package com.bankaudit.rest.process.dao;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.ResultTransformer;
import org.jboss.logging.Logger;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Repository;

import com.bankaudit.constants.BankAuditConstant;
import com.bankaudit.dao.AbstractDao;
import com.bankaudit.dto.DataTableResponse;
import com.bankaudit.model.User;
import com.bankaudit.process.model.AuditLetterUserInputs;
import com.bankaudit.process.model.AuditLetterUserInputsWrk;
import com.bankaudit.process.model.AuditSchedule;
import com.bankaudit.process.model.AuditScheduleWrk;
import com.bankaudit.process.model.AuditTeam;
import com.bankaudit.process.model.AuditTeamWrk;
import com.bankaudit.util.BankAuditUtil;

@Repository("auditPlanUpdateDao")
public class AuditPlanUpdateDaoImpl extends AbstractDao implements AuditPlanUpdateDao {

	static final Logger logger = Logger.getLogger(AuditPlanUpdateDaoImpl.class);

	@SuppressWarnings({"unchecked", "deprecation"})
	@Override
	public DataTableResponse getAuditsList(int legalEntityCode, String userId, String search, Integer orderColumn, String orderDirection,
			Integer page, Integer size, List<String> unitList,
			LocalDate financialYear, String auditTypeCode, List<LocalDate>  financialYearStrtEndList) { // Added to filter the records based on Financial Year and AuditType) {
		
		logger.info(" AuditPlanUpdateDaoImpl getAuditsList .." + userId + " unitList .." + unitList+ " :: "+financialYear+" :: "+auditTypeCode);
		

		Session session = getSession();
		session.setDefaultReadOnly(true);

		/**********************
		 * This block is to retrieve the User details --> UserId and UserName
		 **************************/
		StringBuilder queryString0 = new StringBuilder("from User u where u.legalEntityCode =:legalEntityCode");
		Query query1 = session.createQuery(queryString0.toString()).setParameter("legalEntityCode", legalEntityCode);

		List<User> lst = query1.list();
		HashMap<String, String> userDtls = new HashMap<>();
		for (User usr : lst) {
			if (usr.getUserId().equalsIgnoreCase(userId))
				userDtls.put(usr.getUserId(), "Me");
			else {
				String name = usr.getMiddleName() != null ? usr.getMiddleName() + " " : "";
				userDtls.put(usr.getUserId(), usr.getFirstName() + " " + name + usr.getLastName());
			}
		}

		DataTableResponse dataTableResponse = new DataTableResponse();
		try {
			StringBuilder queryString = new StringBuilder(
					"SELECT * FROM "
							+ "  (SELECT result.legal_entity_code, result.plan_id, audit_id, result.schedule_start_date, result.schedule_end_date, result.last_audit_date, "
							+ "  result.actual_start_date, result.actual_end_date, result.duration, result.ir_finalisation_date, result.compliance_Due_Date, result.report_Date,"
							+ "  (SELECT CONCAT(result.report_status , '-' , g.description) FROM  general_parameter g WHERE g.legal_entity_code =result.legal_entity_code "
							+ "  AND g.key_1='STATUS' AND g.key_2='REPORT_STATUS' AND g.value=result.report_status) AS report_status,"
							
							+ "  (SELECT CONCAT(audit_type_code , '-' , audit_Type_Desc) FROM maint_audit_type_desc "
							+ "  atm WHERE atm.audit_type_code=result.audit_type_code AND atm.legal_Entity_Code= result.legal_Entity_Code ) AS audit_type_code_desc, "
							
							+ "  (SELECT CONCAT(unit_code , '-' , unit_Name) FROM  maint_entity e WHERE e.unit_code =result.unit_id AND e.legal_Entity_Code =result.legal_Entity_Code ) AS unit_desc, "
							
							+ "  (SELECT g.description FROM  maint_entity e,general_parameter g WHERE e.unit_code =result.unit_id AND e.legal_Entity_Code =result.legal_Entity_Code "
							+ "  AND e.legal_Entity_Code =g.legal_Entity_Code AND g.key_1='UNIT_TYPE' AND g.key_2='UNIT_TYPE' AND g.value=e.unit_Type) AS unit_type,"
							
							+ "  (SELECT c.criticality_Desc FROM  maint_entity e,maint_criticality c WHERE e.unit_code =result.unit_id AND "
							+ "  e.legal_entity_code =result.legal_entity_code AND e.legal_entity_code =c.legal_entity_code AND e.u_Criticality=c.criticality_Code AND "
							+ "  c.criticality_Of_Type='Entity') AS u_Criticality, "
							
							+ "  (CASE WHEN result.team_Status='U'"
							+ "  THEN (SELECT t.user_id FROM  audit_team_wrk t  WHERE t.audit_id=result.audit_id AND t.legal_entity_code =result.legal_entity_code AND t.user_Type='L') "
							+ "  ELSE (SELECT t.user_id FROM  audit_team t WHERE t.audit_id=result.audit_id AND t.legal_entity_code =result.legal_entity_code AND t.user_Type='L') END ) AS team_lead,"
							
							+ "  (SELECT CONCAT(result.audit_Status , '-' , g.description) FROM  general_parameter g WHERE  g.legal_entity_code =result.legal_entity_code "
							+ "  AND g.key_1='STATUS' AND g.key_2='AUDIT_STATUS' AND g.value=result.audit_Status) AS audit_Status_desc, "
							
							+ "  result.team_maker, "
							+ "  (SELECT g.value FROM  general_parameter g WHERE g.legal_entity_code =result.legal_entity_code AND g.key_1='AUDIT_EARLY_START' AND g.key_2='EARLY_START_ALLOWED')  AS early_start_allowed , "
							
							+ "  (SELECT g.value FROM general_parameter g WHERE g.legal_entity_code =result.legal_entity_code AND g.key_1='AUDIT_OFFSET_DAYS'  AND g.key_2='AUDIT_OFFSET_DAYS') AS audit_offset_Days , "
							+ "  (SELECT CONCAT(result.audit_Status , '-' , g.description) FROM  general_parameter g WHERE "
							+ "  g.legal_entity_code =result.legal_entity_code AND g.key_1='STATUS' AND g.key_2='AUDIT_STATUS' AND g.value=result.observation_Status) AS audit_obs_Status, "
							
							+ "  (CASE WHEN result.team_Status='U' THEN (SELECT GROUP_CONCAT(t.user_id) FROM audit_team_wrk t WHERE t.legal_entity_code=result.legal_entity_code "
							+ "       AND t.audit_id=result.audit_id AND t.user_Type='M' ) ELSE (SELECT GROUP_CONCAT(t.user_id) FROM audit_team t WHERE t.legal_entity_code=result.legal_entity_code "
							+ "       AND t.audit_id=result.audit_id AND t.user_Type='M') END ) AS teamMember , "
							
							+ "  (SELECT CONCAT(result.team_Status , '-' , g.description) FROM  general_parameter g "
							+ "       WHERE g.legal_entity_code =result.legal_entity_code AND g.key_1='STATUS' AND g.key_2='STATUS' AND g.value=result.team_Status) AS teamStatus, "
							
							+ "  result.observation_status, result.team_checker, result.team_checker_timestamp, result.unit_id, "
							+ "  result.audit_status , result.maker, result.audit_type_code, "
							+ "  result.checker, result.team_Status, result.status,  "
							
							+ "  (SELECT CONCAT(result.status , '-' , g.description) FROM  general_parameter g "
							+ "  WHERE g.legal_entity_code =result.legal_entity_code AND g.key_1='STATUS' AND g.key_2='STATUS' AND g.value=result.status) AS authStatus" //35
							
							+ "  ,(SELECT unit_name FROM  maint_entity e WHERE e.unit_code =result.unit_id AND e.legal_Entity_Code =result.legal_Entity_Code ) AS unit_name, " // 36
							+ "  financial_year " // 37
							
							+ "  FROM audit_schedule result " 
							
							+ "  UNION ALL "
							
							+ "  SELECT resultWrk.legal_entity_code, resultWrk.plan_id, audit_id, resultWrk.schedule_start_date, resultWrk.schedule_end_date, resultWrk.last_audit_date, "
							+ "  resultWrk.actual_start_date, resultWrk.actual_end_date, resultWrk.duration, resultWrk.ir_finalisation_date, resultWrk.compliance_Due_Date, "
							+ "  resultWrk.report_Date,"
							+ "  (SELECT CONCAT(resultWrk.report_status , '-' , g.description) FROM  general_parameter g WHERE  g.legal_entity_code =resultWrk.legal_entity_code "
							+ "  AND g.key_1='STATUS' AND g.key_2='REPORT_STATUS' AND g.value=resultWrk.report_status)   AS report_status,"
							
							+ "  (SELECT CONCAT(audit_type_code , '-' , audit_Type_Desc) FROM maint_audit_type_desc "
							+ "  atm WHERE atm.audit_type_code=resultWrk.audit_type_code AND atm.legal_Entity_Code= resultWrk.legal_Entity_Code ) AS audit_type_code_desc, "
							
							+ "  (SELECT CONCAT(unit_code , '-' , unit_Name) FROM  maint_entity e WHERE e.unit_code =resultWrk.unit_id AND e.legal_Entity_Code =resultWrk.legal_Entity_Code ) AS unit_desc, "
							
							+ "  (SELECT g.description FROM  maint_entity e,general_parameter g WHERE e.unit_code =resultWrk.unit_id AND e.legal_Entity_Code =resultWrk.legal_Entity_Code "
							+ "  AND e.legal_Entity_Code =g.legal_Entity_Code AND g.key_1='UNIT_TYPE' AND g.key_2='UNIT_TYPE' AND g.value=e.unit_Type) AS unit_type,"
							
							+ "  (SELECT c.criticality_Desc FROM  maint_entity e,maint_criticality c WHERE e.unit_code =resultWrk.unit_id AND "
							+ "  e.legal_entity_code =resultWrk.legal_entity_code AND e.legal_entity_code =c.legal_entity_code AND e.u_Criticality=c.criticality_Code AND "
							+ "  c.criticality_Of_Type='Entity') AS u_Criticality,  "
							
							+ "  ( CASE WHEN resultWrk.team_Status='U' THEN "
							+ "  (SELECT t.user_id FROM  audit_team_wrk t WHERE t.audit_id=resultWrk.audit_id AND t.legal_entity_code =resultWrk.legal_entity_code AND t.user_Type='L') ELSE"
							+ "  (SELECT t.user_id FROM  audit_team_wrk t WHERE t.audit_id=resultWrk.audit_id AND t.legal_entity_code =resultWrk.legal_entity_code AND t.user_Type='L') END ) AS team_lead,"
							
							
							+ "  (SELECT CONCAT(resultWrk.audit_Status , '-' , g.description) FROM  general_parameter g WHERE "
							+ "  g.legal_entity_code =resultWrk.legal_entity_code AND g.key_1='STATUS' AND g.key_2='AUDIT_STATUS' AND g.value=resultWrk.audit_Status)   AS audit_Status_desc,  "
							
							+ "  resultWrk.team_maker,  "
							
							+ " (SELECT g.value FROM  general_parameter g WHERE g.legal_entity_code =resultWrk.legal_entity_code "
							+ "  AND g.key_1='AUDIT_EARLY_START' AND g.key_2='EARLY_START_ALLOWED')  AS early_start_allowed , "
							
							+ "  (SELECT g.value FROM general_parameter g WHERE g.legal_entity_code =resultWrk.legal_entity_code AND g.key_1='AUDIT_OFFSET_DAYS'"
							+ "  AND g.key_2='AUDIT_OFFSET_DAYS') AS audit_offset_Days ,  "
							
							+ "  (SELECT CONCAT(resultWrk.audit_Status , '-' , g.description) FROM  general_parameter g WHERE"
							+ "  g.legal_entity_code =resultWrk.legal_entity_code AND g.key_1='STATUS' AND g.key_2='AUDIT_STATUS' "
							+ "  AND g.value=resultWrk.observation_Status) AS audit_obs_Status, "
							
							+ "  ( CASE WHEN resultWrk.team_Status='U' "
							+ "  THEN (SELECT GROUP_CONCAT(t.user_id) FROM audit_team_wrk t WHERE t.legal_entity_code=resultWrk.legal_entity_code "
							+ "  AND t.audit_id=resultWrk.audit_id AND t.user_Type='M' ) "
							+ "  ELSE (SELECT GROUP_CONCAT(t.user_id) FROM audit_team_wrk t WHERE t.legal_entity_code=resultWrk.legal_entity_code "
							+ "  AND t.audit_id=resultWrk.audit_id AND t.user_Type='M') END ) AS teamMember , "
							
							+ "  (SELECT CONCAT(resultWrk.team_Status , '-' , g.description) FROM  general_parameter g "
							+ "  WHERE g.legal_entity_code =resultWrk.legal_entity_code AND g.key_1='STATUS' AND g.key_2='STATUS' AND g.value=resultWrk.team_Status) AS teamStatus,"
							
							+ "  resultWrk.observation_status,resultWrk.team_checker,"
							+ "  resultWrk.team_checker_timestamp, resultWrk.unit_id,"
							+ "  resultWrk.audit_status, resultWrk.maker, resultWrk.audit_type_code, " //31	
							+ "  resultWrk.checker, resultWrk.team_Status, resultWrk.status,  "
							
							+ "  (SELECT CONCAT(resultWrk.status , '-' , g.description) FROM  general_parameter g "
							+ "  WHERE g.legal_entity_code =resultWrk.legal_entity_code AND g.key_1='STATUS' AND g.key_2='STATUS' AND g.value=resultWrk.status) AS authStatus" //35
							
							+ "  ,(SELECT unit_name FROM  maint_entity e WHERE e.unit_code =resultWrk.unit_id AND e.legal_Entity_Code =resultWrk.legal_Entity_Code ) AS unit_name, " // 36
							+ "  financial_year "  // 37
							
							+ " FROM audit_schedule_wrk resultWrk "
							+ ")  finalResult  WHERE legal_entity_code =:legalEntityCode AND unit_id IN (:unitList) AND audit_Status NOT IN ('AU', 'AN', 'UP')");
			
			// Financial Year and Audit Type filter added for filtering
			if(!BankAuditUtil.isEmptyString(auditTypeCode)) {
				queryString.append(" AND  audit_type_code =:auditTypeCode "); 
			}if(financialYear !=null) {
				LocalDate fYStartDt = financialYearStrtEndList.get(0);
				LocalDate fYearEndDt = financialYearStrtEndList.get(1);
				logger.info("fYStartDt :: "+fYStartDt+" fYEndDt :: "+fYearEndDt);
				queryString.append(" AND (schedule_start_date between '"+fYStartDt+"' AND '"+fYearEndDt+"' )");
				//queryString.append(" And (sch.scheduleStartDate between :fYStartDt and :fYearEndDt )");
			}

			if (!BankAuditUtil.isEmptyString(search)) {
				queryString = queryString.append(
						" AND (audit_type_code IN (SELECT a.audit_type_code FROM maint_audit_type_desc a  WHERE a.legal_entity_code=finalResult.legal_entity_code AND "
						+ "  	(a.audit_type_desc LIKE :search OR a.audit_type_code LIKE :search) ) "
						+ "  OR plan_id LIKE :search OR audit_id LIKE :search  "
						+ "  OR unit_id IN (SELECT m.unit_code FROM maint_entity m WHERE m.legal_entity_code=finalResult.legal_entity_code AND (m.unit_Name LIKE :search OR m.unit_code LIKE :search )) "
						+ "  OR unit_id IN (SELECT m.unit_code FROM maint_entity m,general_parameter g WHERE unit_id=m.unit_code AND m.legal_entity_code=finalResult.legal_entity_code "
						+ "  	AND m.legal_entity_code =g.legal_entity_code AND g.key_1='UNIT_TYPE' AND g.key_2='UNIT_TYPE' AND g.value=m.unit_type AND g.description LIKE :search) "
						+ "  OR unit_id IN (SELECT m.unit_code FROM maint_entity m, maint_criticality c WHERE unit_id=m.unit_code AND m.legal_entity_code=finalResult.legal_entity_code "
						+ "  	AND m.legal_entity_code =c.legal_entity_code AND m.u_criticality=c.criticality_code AND c.criticality_of_type='Entity' AND  c.criticality_desc LIKE :search) "
						+ "  OR unit_id IN (SELECT m.unit_code FROM maint_entity m, maint_audit_parameter p WHERE unit_id=m.unit_code AND m.legal_entity_code=finalResult.legal_entity_code "
						+ "  	AND m.legal_entity_code =p.legal_entity_code AND m.u_criticality=p.u_criticality AND audit_type_code=p.audit_type_code AND  p.duration LIKE :search) "
						+ "  OR audit_id IN (SELECT t.audit_id FROM user u, audit_team t WHERE t.audit_id=audit_id AND t.legal_entity_code =finalResult.legal_entity_code "
						+ "  	AND t.legal_entity_code =u.legal_entity_code AND t.user_id=u.user_id AND (t.user_id LIKE :search OR u.first_name LIKE :search OR u.last_name LIKE :search) ) "
						+ "  OR DATE_FORMAT(schedule_start_date, '%d %b %Y') LIKE :search OR DATE_FORMAT(schedule_end_date, '%d %b %Y') LIKE :search "
						+ "  OR DATE_FORMAT(actual_start_date, '%d %b %Y') LIKE :search OR DATE_FORMAT(actual_end_date, '%d %b %Y') LIKE :search "
						+ "  OR DATE_FORMAT(last_audit_date, '%d %b %Y') LIKE :search "
						+ "  OR maker IN (SELECT u.user_id FROM user u WHERE"
						+ "  	u.legal_entity_code =finalResult.legal_entity_code AND (u.user_id LIKE :search OR u.first_name LIKE :search OR u.last_name LIKE :search) )"
						+ "  OR audit_status IN (SELECT g.value FROM general_parameter g WHERE legal_entity_code =g.legal_entity_code AND g.key_1='STATUS' "
						+ "  	AND g.key_2='AUDIT_STATUS' AND g.value=audit_status AND g.description LIKE :search) "
						+ "  OR audit_id IN (SELECT t.audit_id FROM audit_team t, user u WHERE t.legal_entity_code=finalResult.legal_entity_code AND t.audit_id=audit_id AND t.user_type='M' "
						+ "  	AND t.legal_entity_code=u.legal_entity_code AND t.user_id=u.user_id AND unit_id=u.unit_code AND (u.first_name LIKE :search OR u.middle_name "
						+ "  	LIKE :search OR u.last_name LIKE :search OR u.user_id LIKE :search )) "						
						+ "  OR team_Status IN (SELECT g.value FROM general_parameter g WHERE finalResult.legal_entity_code =g.legal_entity_code AND g.key_1='STATUS' "
						+ " 	 AND g.key_2='STATUS' AND g.value=team_Status AND g.description LIKE :search) "
						+ "  OR status IN (SELECT g.value FROM general_parameter g WHERE legal_entity_code =g.legal_entity_code AND g.key_1='STATUS' "
						+ "  	AND g.key_2='STATUS' AND g.value=status AND g.description LIKE :search)  " 
						+ "  ) ");
			}

			String[] columns = {"audit_type_code", "plan_id", "unit_type", "(unit_id * 1) ", // "unitId",
					"u_criticality", "duration", "last_audit_date", "audit_offset_Days","schedule_start_date", "schedule_end_date",
					"actual_start_date", "actual_end_date", "ir_finalisation_date", "compliance_Due_Date","report_Date", "team_lead", "teamMember","team_maker","team_checker","report_status", "audit_status", "team_Status"
					,"unit_name", "financial_year"};

			logger.info("orderColumn **.... " + orderColumn + "  orderDirection ...." + orderDirection);
			if (orderColumn != null && orderColumn == -1 && !BankAuditUtil.isEmptyString(orderDirection)) {
				queryString = queryString.append(" ORDER BY FIELD(audit_status, 'AI','AP') asc  ");
			} else if (orderColumn != null && !BankAuditUtil.isEmptyString(orderDirection)) {
				if (orderColumn == 0) {
					queryString = queryString.append(" ORDER BY  ").append(columns[orderColumn]).append(" ").append(orderDirection)
							.append(" , FIELD(audit_status, 'AI','AP') asc ,(audit_id *1 ) asc  ");
				} else {
					queryString = queryString.append(" ORDER BY  ").append(columns[orderColumn]).append(" ").append(orderDirection);
					if(orderColumn==21){
						queryString.append(", status ").append(orderDirection);
					}
				}
			} else {
				queryString = queryString.append(" ORDER BY FIELD(audit_status, 'AI','AP') asc  ");
			}
			Query query = (Query) session.createSQLQuery(queryString.toString()).setResultTransformer(new ResultTransformer() {
				@Override
				public Object transformTuple(Object[] tuple, String[] aliases) {
					try {
						AuditSchedule auditSchedule = new AuditSchedule();
						if (tuple[0] != null) {
							auditSchedule.setLegalEntityCode(Integer.parseInt(tuple[0].toString()));
						}
						if (tuple[1] != null) {
							auditSchedule.setPlanId(tuple[1].toString());
						}
						if (tuple[2] != null) {
							auditSchedule.setAuditId(tuple[2].toString());
						}
						DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
						if (tuple[3] != null) {
							auditSchedule.setScheduleStartDate(dateFormat.parse(tuple[3].toString()));
						}
						if (tuple[4] != null) {
							auditSchedule.setScheduleEndDate(dateFormat.parse(tuple[4].toString()));
						}
						if (tuple[5] != null) {
							auditSchedule.setLastAuditDate(dateFormat.parse(tuple[5].toString()));
						}
						if (tuple[6] != null) {
							auditSchedule.setActualStartDate(dateFormat.parse(tuple[6].toString()));
						}
						if (tuple[7] != null) {
							auditSchedule.setActualEndDate(dateFormat.parse(tuple[7].toString()));
						}
						if (tuple[8] != null) {
							auditSchedule.setDuration(tuple[8].toString());
						}
						if (tuple[9] != null) {
							auditSchedule.setIrFinalisationDate(dateFormat.parse(tuple[9].toString()));
						}
						if (tuple[10] != null) {
							auditSchedule.setComplianceDueDate(dateFormat.parse(tuple[10].toString()));
						}
						if (tuple[11] != null) {
							auditSchedule.setReportDate(dateFormat.parse(tuple[11].toString()));
						}
						if (tuple[12] != null) {
							auditSchedule.setReportStatus(tuple[12].toString());
						}
						if (tuple[13] != null) {
							auditSchedule.setAuditTypeCode(tuple[13].toString());
						}
						if (tuple[14] != null) {
							// auditSchedule.setUnitId(tuple[14].toString());
							String unitCodeName=tuple[14].toString();
							auditSchedule.setUnitId(unitCodeName);
							auditSchedule.setUnitName(unitCodeName.substring(unitCodeName.indexOf('-')+1));
						}
						if (tuple[15] != null) {
							auditSchedule.setUnitType(tuple[15].toString());
						}
						if (tuple[16] != null) {
							auditSchedule.setuCriticality(tuple[16].toString());
						}
						if (tuple[17] != null) {
							auditSchedule.setTeamLead(tuple[17].toString() + "-" + userDtls.get(tuple[17].toString()));
						}
						if (tuple[25] != null)
							auditSchedule.setObservationStatus(tuple[25].toString());

						if (tuple[18] != null) {
							if ("P".equalsIgnoreCase(tuple[18].toString()))
								auditSchedule.setAuditStatus("AP");
							else if ("AE".equalsIgnoreCase(auditSchedule.getObservationStatus()) && "AI".equalsIgnoreCase(auditSchedule.getAuditStatus()))
								auditSchedule.setAuditStatus(tuple[25] != null ? tuple[25].toString()+"-Exited" : tuple[18].toString());
							else auditSchedule.setAuditStatus(tuple[18].toString());
						}
						if (tuple[19] != null) {
							auditSchedule.setTeamMaker(tuple[19].toString() + "-" + userDtls.get(tuple[19].toString()));
						}
						if (tuple[20] != null) {
							auditSchedule.setEarlyStartAllowed(tuple[20].toString());
						} else auditSchedule.setEarlyStartAllowed("Y");

						if (tuple[21] != null) {
							auditSchedule.setAuditOffsetDays(Integer.parseInt(tuple[21].toString()));
						} else auditSchedule.setAuditOffsetDays(0);

						if (tuple[23] != null) {
							StringBuilder teamMembers = new StringBuilder();
							String[] members = tuple[23].toString().split(",");
							for (String member : members) {
								teamMembers.append(member + "-" + userDtls.get(member) + ",");
							}
							auditSchedule.setTeamMember(teamMembers.substring(0, teamMembers.length() - 1));
						}
						if (tuple[24] != null)
							auditSchedule.setTeamStatus(tuple[24].toString());
						if (tuple[26] != null)
							auditSchedule.setTeamChecker(tuple[26].toString() + "-" + userDtls.get(tuple[26].toString()));
						if (tuple[27] != null)
							auditSchedule.setTeamCheckerTimestamp((Date)tuple[27]);
						
						if (tuple[30] != null)
							auditSchedule.setMaker(tuple[30].toString() + "-" + userDtls.get(tuple[30].toString()));
						
						if (tuple[32] != null)
							auditSchedule.setChecker(tuple[32].toString() + "-" + userDtls.get(tuple[32].toString()));
						
						if (tuple[35] != null) auditSchedule.setStatus(tuple[35].toString());
						if (tuple[36] != null) auditSchedule.setUnitName(tuple[36].toString());
						if (tuple[37] != null) auditSchedule.setFinancialYear((Date)tuple[37]);
						
						return auditSchedule;
					} catch (NumberFormatException e) {
						e.printStackTrace();
					} catch (ParseException e) {
						e.printStackTrace();
					}
					return null;
				}
				@Override
				public List transformList(List collection) {
					return collection;
				}
			}).setParameter("legalEntityCode", legalEntityCode).setParameter("unitList", unitList);
			if (!BankAuditUtil.isEmptyString(search)) {
				query.setParameter("search", "%" + search + "%");
			}
			if(!BankAuditUtil.isEmptyString(auditTypeCode)) {
				query.setParameter("auditTypeCode", auditTypeCode);
			}

			int totalRecords = 0;
			try {
				totalRecords = query.getResultList().size();
			} catch (Exception e) {
				totalRecords =  0;
			}
			List<AuditSchedule> auditSchedule = query.setFirstResult((page * size)).setMaxResults(size).list();

			dataTableResponse.setData(auditSchedule);
			dataTableResponse.setRecordsTotal((long) totalRecords);
			dataTableResponse.setRecordsFiltered((long) totalRecords);

		} catch (Exception e) {
			logger.debug(e.getMessage());
		}
		return dataTableResponse;

	}

	@Override
	public AuditSchedule getAuditByPlanIdAndAuditId(int legalEntityCode, String auditTypeCode, String planId, String auditId) {
		Session session = getSession();
		session.setDefaultReadOnly(true);

		AuditSchedule auditSchedule = null;
		try {
			StringBuilder queryString2 = new StringBuilder("select sch as auditSchedule, "
					+ "(select concat(auditTypeCode , '-' , auditTypeDesc) from MaintAuditTypeDesc at where at.auditTypeCode=sch.auditTypeCode and at.legalEntityCode= sch.legalEntityCode ) as auditTypeCode,"
					+ " (Select concat(unitCode , '-' , unitName) from  MaintEntity e where e.unitCode =sch.unitId and e.legalEntityCode =sch.legalEntityCode ) as unitId,  "
					+ " (Select concat(sch.auditStatus , '-' , g.description) from  GeneralParameter g where g.legalEntityCode =sch.legalEntityCode and g.key1='STATUS' and g.key2='AUDIT_STATUS' and g.value=sch.auditStatus) as auditStatus, "
					/*+ " usrInput.userInput01, "
					+ " usrInput.userInput02, "
					+ " usrInput.userInput03, "
					+ " usrInput.userInput04, "
					+ " usrInput.userInput05, "
					+ " usrInput.userInput06, "
					+ " usrInput.userInput07, "
					+ " usrInput.userInput08, "
					+ " usrInput.userInput09, "
					+ " usrInput.userInput10, "*/
					+ " usrInput as dirLettInput  " 				
					+ " from AuditSchedule sch "				 
					+ " Left Join AuditLetterUserInputs usrInput on "
					+ " usrInput.legalEntityCode = sch.legalEntityCode "
					+ " and usrInput.auditId = sch.auditId "
					+ " and usrInput.auditTypeCode = sch.auditTypeCode "
					+ " and usrInput.letterCode='Dr'  "					
					+ " where sch.legalEntityCode =:legalEntityCode  and sch.auditId=:auditId ");

			Query query2 = (Query) session.createQuery(queryString2.toString()).setResultTransformer(new ResultTransformer() {
				@Override
				public Object transformTuple(Object[] tuple, String[] aliases) {
					AuditSchedule auditSchedule = null;
					System.out.println("tuple[0] ...." + tuple[0]);
					if (tuple[0] != null) {
						auditSchedule = (AuditSchedule) tuple[0];
						if (tuple[1] != null)
							auditSchedule.setAuditTypeCode(tuple[1].toString());
						if (tuple[2] != null)
							auditSchedule.setUnitName(tuple[2].toString());
						if (tuple[3] != null)
							auditSchedule.setAuditStatusDescription(tuple[3].toString());

						if (tuple[4] != null){
							AuditLetterUserInputs auditLetterUserInputs =   (AuditLetterUserInputs) tuple[4];
							auditSchedule.setUserInput01(auditLetterUserInputs.getUserInput01());
							auditSchedule.setUserInput02(auditLetterUserInputs.getUserInput02());
							auditSchedule.setUserInput03(auditLetterUserInputs.getUserInput03());
							auditSchedule.setUserInput04(auditLetterUserInputs.getUserInput04());
							auditSchedule.setUserInput05(auditLetterUserInputs.getUserInput05());
							auditSchedule.setUserInput06(auditLetterUserInputs.getUserInput06());
							auditSchedule.setUserInput07(auditLetterUserInputs.getUserInput07());
							auditSchedule.setUserInput08(auditLetterUserInputs.getUserInput08());
							auditSchedule.setUserInput09(auditLetterUserInputs.getUserInput09());
							auditSchedule.setUserInput10(auditLetterUserInputs.getUserInput10());
							auditSchedule.setUserInputMaker(auditLetterUserInputs.getMaker()); 
							auditSchedule.setUserInputChecker(auditLetterUserInputs.getChecker());
							auditSchedule.setUserInputAuthRejRemarks(auditLetterUserInputs.getAuthRejRemarks());							   						
							auditSchedule.setUserInputMakerTimeStamp(auditLetterUserInputs.getMakerTimestamp());	
							auditSchedule.setUserInputSysdateTimestamp(auditLetterUserInputs.getSysdateTimestamp());
							auditSchedule.setUserInputBusinessdateTimestamp(auditLetterUserInputs.getBusinessdateTimestamp());
						}
							/*
						//if (tuple[4] != null)
						   auditSchedule.setUserInput01(tuple[4].toString());						
						//if (tuple[5] != null)
							auditSchedule.setUserInput02(tuple[5].toString());
						//if (tuple[6] != null)
							auditSchedule.setUserInput03(tuple[6].toString());
						//if (tuple[7] != null)
							auditSchedule.setUserInput04(tuple[7].toString());
						//if (tuple[8] != null)
							auditSchedule.setUserInput05(tuple[8].toString());
						//if (tuple[9] != null)
							auditSchedule.setUserInput06(tuple[9].toString());
						//if (tuple[10] != null)
							auditSchedule.setUserInput07(tuple[10].toString());
						//if (tuple[11] != null)
							auditSchedule.setUserInput08(tuple[11].toString());
						//if (tuple[12] != null)
							auditSchedule.setUserInput09(tuple[12].toString());
						//if (tuple[13] != null)
							auditSchedule.setUserInput10(tuple[13].toString());*/
					 
					}
					return auditSchedule;
				}
				@Override
				public List transformList(List collection) {
					return collection;
				}
			}).setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId);
			auditSchedule = (AuditSchedule) query2.uniqueResult();

		} catch (Exception e) {
			e.printStackTrace();
		}
		return auditSchedule;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<AuditTeam> getAuditTeamsByAuditId(int legalEntityCode, String auditTypeCode, String planId, String auditId, String status) {

		List<AuditTeam> auditTeams = new ArrayList<>();
		Session session = getSession();
		Class className = null;

		try {
			if(status.equals(BankAuditConstant.STATUS_AUTH))
				className = AuditTeam.class;
			else
				className = AuditTeamWrk.class;

			if (className != null) {
				StringBuilder queryString = new StringBuilder("select  legalEntityCode , planId, auditId, userType,  "
						+ " (select concat(auditTypeCode , '-' , auditTypeDesc) from MaintAuditTypeDesc atd where atd.auditTypeCode=at.auditTypeCode and atd.legalEntityCode= at.legalEntityCode ) as auditTypeCode,"
						+ " (Select concat(unitCode , '-' , unitName) from  MaintEntity e where e.unitCode =at.unitId and e.legalEntityCode =at.legalEntityCode ) as unitId,  "
						+ " userId "
						+ " FROM " + className.getSimpleName() + " at" + " WHERE legalEntityCode =:legalEntityCode AND auditId=:auditId ");

				Query query = (Query) session.createQuery(queryString.toString()).setResultTransformer(new ResultTransformer() {
					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						AuditTeam auditTeam = new AuditTeam();
						if (tuple[0] != null)
							auditTeam.setLegalEntityCode(Integer.parseInt(tuple[0].toString()));
						if (tuple[1] != null)
							auditTeam.setPlanId(tuple[1].toString());
						if (tuple[2] != null)
							auditTeam.setAuditId(tuple[2].toString());
						if (tuple[3] != null)
							auditTeam.setUserType(tuple[3].toString());
						if (tuple[4] != null)
							auditTeam.setAuditTypeCode(tuple[4].toString());
						if (tuple[5] != null)
							auditTeam.setUnitId(tuple[5].toString());
						if (tuple[6] != null)
							auditTeam.setUserId(tuple[6].toString());

						return auditTeam;
					}
					@Override
					public List transformList(List collection) {
						return collection;
					}
				}).setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId);
				logger.info("legalEntityCode :: " + legalEntityCode + "auditId :: " + auditId);
				auditTeams = query.list();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return auditTeams;
	}

	@Override
	public void deleteAuditTeam(Integer legalEntityCode, String auditId, String statusUnauth) {

		Session session = getSession();
		logger.info("deleteAuditTeam .. " + legalEntityCode + " :: " + auditId + " :: " + statusUnauth);
		if (!BankAuditConstant.STATUS_AUTH.equals(statusUnauth)) {
			session.createQuery("delete from AuditTeamWrk  " + " where legalEntityCode =:legalEntityCode " + " and  auditId =:auditId  ")
					.setParameter("auditId", auditId).setParameter("legalEntityCode", legalEntityCode).executeUpdate();
		} else {
			session.createQuery("delete from AuditTeam  " + " where legalEntityCode =:legalEntityCode " + " and  auditId =:auditId ")
					.setParameter("auditId", auditId).setParameter("legalEntityCode", legalEntityCode).executeUpdate();
		}
	}

	@Override
	public List<AuditSchedule> getAuditScheduleUpdateList(int legalEntityCode, String userId, String search, Integer orderColumn,
			String orderDirection, Integer page, Integer size) {
		Query query = getSession().createQuery(
				"SELECT p FROM AuditSchedule p JOIN FETCH p.auditTeams c where p.legalEntityCode =:legalEntityCode AND p.auditStatus in ('AP', 'AI')");
		query.setParameter("legalEntityCode", legalEntityCode);
		return query.setFirstResult((page * size)).setMaxResults(size).list();
	}

	@Override
	public AuditScheduleWrk getAuditScheduleWrk(Integer legalEntityCode, String auditTypeCode, String planId, String auditId) {
		logger.debug("Entered into AuditPlanUpdateDaoImpl getAuditScheduleWrk method");
		Criteria criteria = getSession().createCriteria(AuditScheduleWrk.class);
		criteria.add(Restrictions.eq("legalEntityCode", legalEntityCode));
		criteria.add(Restrictions.eq("auditTypeCode", auditTypeCode));
		criteria.add(Restrictions.eq("planId", planId));
		criteria.add(Restrictions.eq("auditId", auditId));
		logger.debug("Exit into AuditPlanUpdateDaoImpl getAuditScheduleWrk method");
		return (AuditScheduleWrk) criteria.uniqueResult();
	}

	@Override
	public AuditSchedule getAuditSchedule(Integer legalEntityCode, String auditTypeCode, String planId, String auditId) {
		logger.debug("Entered into AuditPlanUpdateDaoImpl getAuditSchedule method");
		Criteria criteria = getSession().createCriteria(AuditSchedule.class);
		criteria.add(Restrictions.eq("legalEntityCode", legalEntityCode));
		criteria.add(Restrictions.eq("auditTypeCode", auditTypeCode));
		criteria.add(Restrictions.eq("planId", planId));
		criteria.add(Restrictions.eq("auditId", auditId));
		logger.debug("Exit into AuditPlanUpdateDaoImpl getAuditSchedule method");
		return (AuditSchedule) criteria.uniqueResult();

	}

	@Override
	public void deleteAuditSchedule(Integer legalEntityCode, String auditTypeCode, String planId, String auditId) {
		try {
			Session session = getSession();
			session.createQuery("delete from  AuditScheduleWrk where  " + " legalEntityCode =:legalEntityCode " + " and auditTypeCode =:auditTypeCode"
					+ " and planId =:planId " + " and auditId =:auditId ").setParameter("legalEntityCode", legalEntityCode)
					.setParameter("auditTypeCode", auditTypeCode).setParameter("planId", planId).setParameter("auditId", auditId).executeUpdate();			
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	@Override
	public AuditSchedule getAuditScheduleWrkByPlanIdAndAuditId(int legalEntityCode, String auditTypeCode, String planId, String auditId) {

		Session session = getSession();
		session.setDefaultReadOnly(true);

		AuditScheduleWrk auditSchedulewrk = null;
		AuditSchedule auditSchedule = new AuditSchedule();
		try {
			StringBuilder queryString2 = new StringBuilder("select sch as auditSchedule, "
					+ "(select concat(auditTypeCode , '-' , auditTypeDesc) from MaintAuditTypeDesc at where at.auditTypeCode=sch.auditTypeCode and at.legalEntityCode= sch.legalEntityCode ) as auditTypeCode,"
					+ " (Select concat(unitCode , '-' , unitName) from  MaintEntity e where e.unitCode =sch.unitId and e.legalEntityCode =sch.legalEntityCode ) as unitId,  "
					+ " (Select concat(sch.auditStatus , '-' , g.description) from  GeneralParameter g where g.legalEntityCode =sch.legalEntityCode and g.key1='STATUS' and g.key2='AUDIT_STATUS' and g.value=sch.auditStatus) as auditStatus, "
					/*+ " usrInput.userInput01, "
					+ " usrInput.userInput02, "
					+ " usrInput.userInput03, "
					+ " usrInput.userInput04, "
					+ " usrInput.userInput05, "
					+ " usrInput.userInput06, "
					+ " usrInput.userInput07, "
					+ " usrInput.userInput08, "
					+ " usrInput.userInput09, "
					+ " usrInput.userInput10  " */
					+ " usrInput as dirLettInput  "
					+ " from AuditScheduleWrk sch "
					+ " Left Join AuditLetterUserInputsWrk usrInput on "
					+ " usrInput.legalEntityCode = sch.legalEntityCode "
					+ " and usrInput.auditId = sch.auditId "
					+ " and usrInput.auditTypeCode = sch.auditTypeCode "
					+ " and usrInput.letterCode='Dr'  "
					+ " where sch.legalEntityCode =:legalEntityCode  and sch.auditId=:auditId ");

			Query query2 = (Query) session.createQuery(queryString2.toString()).setResultTransformer(new ResultTransformer() {
				@Override
				public Object transformTuple(Object[] tuple, String[] aliases) {
					AuditScheduleWrk auditSchedule = null;
					System.out.println("tuple[0] ...." + tuple[0]);
					if (tuple[0] != null) {
						auditSchedule = (AuditScheduleWrk) tuple[0];
						if (tuple[1] != null)
							auditSchedule.setAuditTypeCode(tuple[1].toString());
						if (tuple[2] != null)
							auditSchedule.setUnitName(tuple[2].toString());
						if (tuple[3] != null)
							auditSchedule.setAuditStatusDescription(tuple[3].toString());
						
						if (tuple[4] != null){
							AuditLetterUserInputsWrk auditLetterUserInputs =   (AuditLetterUserInputsWrk) tuple[4];
							auditSchedule.setUserInput01(auditLetterUserInputs.getUserInput01());
							auditSchedule.setUserInput02(auditLetterUserInputs.getUserInput02());
							auditSchedule.setUserInput03(auditLetterUserInputs.getUserInput03());
							auditSchedule.setUserInput04(auditLetterUserInputs.getUserInput04());
							auditSchedule.setUserInput05(auditLetterUserInputs.getUserInput05());
							auditSchedule.setUserInput06(auditLetterUserInputs.getUserInput06());
							auditSchedule.setUserInput07(auditLetterUserInputs.getUserInput07());
							auditSchedule.setUserInput08(auditLetterUserInputs.getUserInput08());
							auditSchedule.setUserInput09(auditLetterUserInputs.getUserInput09());
							auditSchedule.setUserInput10(auditLetterUserInputs.getUserInput10());
							auditSchedule.setUserInputMaker(auditLetterUserInputs.getMaker());  
							auditSchedule.setUserInputAuthRejRemarks(auditLetterUserInputs.getAuthRejRemarks()); 						
							auditSchedule.setUserInputMakerTimeStamp(auditLetterUserInputs.getMakerTimestamp());	
							auditSchedule.setUserInputSysdateTimestamp(auditLetterUserInputs.getSysdateTimestamp());
							auditSchedule.setUserInputBusinessdateTimestamp(auditLetterUserInputs.getBusinessdateTimestamp());
						}
						/*if (tuple[4] != null)
							auditSchedule.setUserInput01(tuple[4].toString());						
						if (tuple[5] != null)
							auditSchedule.setUserInput02(tuple[5].toString());
						if (tuple[6] != null)
							auditSchedule.setUserInput03(tuple[6].toString());
						if (tuple[7] != null)
							auditSchedule.setUserInput04(tuple[7].toString());
						if (tuple[8] != null)
							auditSchedule.setUserInput05(tuple[8].toString());
						if (tuple[9] != null)
							auditSchedule.setUserInput06(tuple[9].toString());
						if (tuple[10] != null)
							auditSchedule.setUserInput07(tuple[10].toString());
						if (tuple[11] != null)
							auditSchedule.setUserInput08(tuple[11].toString());
						if (tuple[12] != null)
							auditSchedule.setUserInput09(tuple[12].toString());
						if (tuple[13] != null)
							auditSchedule.setUserInput10(tuple[13].toString());*/				 
						
					}
					return auditSchedule;
				}
				@Override
				public List transformList(List collection) {
					return collection;
				}
			}).setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId);
			auditSchedulewrk = (AuditScheduleWrk) query2.uniqueResult();
			BeanUtils.copyProperties(auditSchedulewrk, auditSchedule);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return auditSchedule;
	}
	
	@Override
	public AuditLetterUserInputsWrk getAuditLetterUserInputsWrk(Integer legalEntityCode, String auditTypeCode, String planId, String auditId) {
		logger.debug("Entered into AuditPlanUpdateDaoImpl getAuditLetterUserInputsWrk method");
		Criteria criteria = getSession().createCriteria(AuditLetterUserInputsWrk.class);
		criteria.add(Restrictions.eq("legalEntityCode", legalEntityCode));
		criteria.add(Restrictions.eq("auditTypeCode", auditTypeCode));
		criteria.add(Restrictions.eq("planId", planId));
		criteria.add(Restrictions.eq("auditId", auditId));
		criteria.add(Restrictions.eq("letterCode", "Dr"));
		logger.debug("Exit into AuditPlanUpdateDaoImpl getAuditLetterUserInputsWrk method");
		return (AuditLetterUserInputsWrk) criteria.uniqueResult();
	}
	
	@Override
	public AuditLetterUserInputs getAuditLetterUserInputs(Integer legalEntityCode, String auditTypeCode, String planId, String auditId) {
		logger.debug("Entered into AuditPlanUpdateDaoImpl getAuditLetterUserInputs method");
		Criteria criteria = getSession().createCriteria(AuditLetterUserInputs.class);
		criteria.add(Restrictions.eq("legalEntityCode", legalEntityCode));
		criteria.add(Restrictions.eq("auditTypeCode", auditTypeCode));
		criteria.add(Restrictions.eq("planId", planId));
		criteria.add(Restrictions.eq("auditId", auditId));
		criteria.add(Restrictions.eq("letterCode", "Dr"));
		logger.debug("Exit into AuditPlanUpdateDaoImpl getAuditLetterUserInputs method");
		return (AuditLetterUserInputs) criteria.uniqueResult();

	}
	
	@Override
	public void deleteDirectiveLetterUserInput(Integer legalEntityCode, String auditTypeCode, String planId, String auditId){
		try {
			Session session = getSession();
			session.createQuery("delete from AuditLetterUserInputsWrk   where  " + " legalEntityCode =:legalEntityCode " + " and auditTypeCode =:auditTypeCode"
					+ " and planId =:planId " + " and auditId =:auditId and  letterCode='Dr' ").setParameter("legalEntityCode", legalEntityCode)
					.setParameter("auditTypeCode", auditTypeCode).setParameter("planId", planId).setParameter("auditId", auditId).executeUpdate();			 
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}
