package com.bankaudit.rest.process.dao;

import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.ScrollMode;
import org.hibernate.ScrollableResults;
import org.hibernate.Session;
import org.hibernate.transform.ResultTransformer;
import org.jboss.logging.Logger;
import org.springframework.stereotype.Repository;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.bankaudit.constants.BankAuditConstant;
import com.bankaudit.dao.AbstractDao;
import com.bankaudit.dto.DataTableResponse;
import com.bankaudit.model.MaintAuditProcess;
import com.bankaudit.model.User;
import com.bankaudit.process.model.AuditSchedule;
import com.bankaudit.process.model.AuditTeam;
import com.bankaudit.process.model.RiskControlDetails;
import com.bankaudit.process.model.TaskDetails;
import com.bankaudit.util.BankAuditUtil;

import java.util.ArrayList;

@Repository("taskDetailsDao")
public class TaskDetailsDaoImpl extends AbstractDao implements TaskDetailsDao{

static final Logger logger = Logger.getLogger(AuditObservationDaoImpl.class);
	
	@Override
	public List<TaskDetails> getTaskDetails(Integer legalEntityCode, String auditId, String observationId, String recStatus){
		List<TaskDetails> taskDetailsLst = null;
		try {
			Session session =getSession();
	        session.setDefaultReadOnly(true);
	        logger.info("Inside getTaskDetails .."+ legalEntityCode +" auditId.. "+ auditId+" observationId.. "+observationId);
	        taskDetailsLst= session.createQuery(" from TaskDetails "
					+ " WHERE "
					+ " legalEntityCode =:legalEntityCode"
					+ " and auditId =:auditId "
					+ " and observationId =:observationId " )
					.setParameter("legalEntityCode", legalEntityCode)
					.setParameter("auditId", auditId)
					.setParameter("observationId", observationId)
					.list();
			
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		logger.info(" taskDetailsLst List.... "+ taskDetailsLst);
		return taskDetailsLst;
	}
	
	@Override
	public void deleteTaskDetailsById(Integer id) {
		
		try{
			Session session=getSession();
				session.createQuery("delete from TaskDetails where id =:id ")
				.setParameter("id", id).executeUpdate();
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
	}
	
	@Override
	public void deleteTaskDetailsByObservationId(String observationId) {
		
		try{
			Session session=getSession();
				session.createQuery("delete from TaskDetails where observationId =:observationId ")
				.setParameter("observationId", observationId).executeUpdate();
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
	}
	
	@Override
	public void deleteTaskDetailsByIdAndStatus(Integer legalEntityCode, String id, String status) {

		Session session=getSession();
		try {
			if(!BankAuditConstant.STATUS_AUTH.equals(status)){
				
				session.createQuery("delete from TaskDetailsWrk  "
						+ " where legalEntityCode =:legalEntityCode "
						+ " and  id =:id  ").setParameter("id", Integer.parseInt(id))
				.setParameter("legalEntityCode", legalEntityCode).executeUpdate();
				
			}else {
				session.createQuery("delete from TaskDetails  "
						+ " where legalEntityCode =:legalEntityCode "
						+ " and  id =:id ").setParameter("id", Integer.parseInt(id))
				.setParameter("legalEntityCode", legalEntityCode).executeUpdate() ;
			}
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
	}
	
	
	@SuppressWarnings("unchecked")
	@Override
	public DataTableResponse getAssessmentsWithAdviceClosure(int legalEntityCode,String userId, String search, Integer orderColumn,
			String orderDirection, Integer page, Integer size, Boolean isDeptAsRMD, List<String> unitList, List<String> deptList) {

		Session session = getSession();
		session.setDefaultReadOnly(true);
		logger.info(" getAssessmentsWithAdviceClosure .... "+ legalEntityCode+ " isDeptAsRMD:: "+isDeptAsRMD);
		logger.info(" unitList .... "+ unitList+ " deptList:: "+deptList);
		if(deptList == null) {  
			deptList = new ArrayList<String>();
			deptList.add(" ");
		}
		
		/********************** This block is to retrieve the User details --> UserId and UserName **************************/
		StringBuilder queryString0=new StringBuilder(// " Select concat(u.firstName , ' ' , u.lastName) as firstName, u.userId "
				 " from User u  "
				+ " where "
				+ " u.legalEntityCode =:legalEntityCode "
				);
		
		Query query1=session.createQuery(queryString0.toString())
				.setParameter("legalEntityCode", legalEntityCode);
		
		List lst = query1.list();
		HashMap<String, String> userDtls = new HashMap<String, String>();
		for ( Iterator iter = lst.iterator(); iter.hasNext(); ) {
			User usr = (User) iter.next();
			if(usr.getUserId().equalsIgnoreCase(userId))
				userDtls.put(usr.getUserId(), "Me"); // for the same User we are stamping as "You"
			else userDtls.put(usr.getUserId(), usr.getFirstName()+" "+usr.getMiddleName()+" "+usr.getLastName());	
		}
		logger.info("userId is .... "+ userId+" serach:: "+search +" legal entity code:: "+legalEntityCode);
		logger.info("User details val is .... "+ userDtls);
		/******************** User Retrieval logic, Ends here ********************************/
		
		
		DataTableResponse dataTableResponse=new DataTableResponse();
		try {
		StringBuilder queryString=new StringBuilder("select sch as auditSchedule, " 
				+ " legalEntityCode,"
				+ "(select concat(auditTypeCode , '-' , auditTypeDesc) from MaintAuditTypeDesc at where at.auditTypeCode=sch.auditTypeCode and at.legalEntityCode= sch.legalEntityCode ) as auditTypeCode,"
				
				+ "planId,auditId, "
				+ " (Select concat(unitCode , '-' , unitName) from  MaintEntity e where e.unitCode =sch.unitId and e.legalEntityCode =sch.legalEntityCode ) as unitId,  "
				
				+ " (Select g.description from  MaintEntity e,GeneralParameter g where e.unitCode =sch.unitId and e.legalEntityCode =sch.legalEntityCode and e.legalEntityCode =g.legalEntityCode and g.key1='UNIT_TYPE' and g.key2='UNIT_TYPE' and g.value=e.unitType) as unitType, "
				
				+ " (Select c.criticalityDesc from  MaintEntity e,MaintCriticality c where e.unitCode =sch.unitId and e.legalEntityCode =sch.legalEntityCode and e.legalEntityCode =c.legalEntityCode and e.uCriticality=c.criticalityCode and c.criticalityOfType='Entity') as uCriticality, "
				+ " (Select t.userId from  AuditTeam t where t.auditId=sch.auditId and t.legalEntityCode =sch.legalEntityCode and t.userType='L') as teamLead, "
				
				+" duration," //Code to comment //+ " (Select p.duration from  MaintEntity e,MaintAuditParameter p where e.unitCode =sch.unitId and e.unitType=p.unitType and e.uCriticality=p.uCriticality and sch.auditTypeCode=p.auditTypeCode and e.legalEntityCode =sch.legalEntityCode and e.legalEntityCode =p.legalEntityCode ) as duration, "
				+" frequency," //Code to comment //+ " (Select p.frequency from  MaintEntity e,MaintAuditParameter p where e.unitCode =sch.unitId and e.unitType=p.unitType and e.uCriticality=p.uCriticality and sch.auditTypeCode=p.auditTypeCode and e.legalEntityCode =sch.legalEntityCode and e.legalEntityCode =p.legalEntityCode ) as frequency, "
				+ " (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, "
				+ " (Select concat(u.userId , '-' , u.firstName, ' ',u.middleName,' ',u.lastName) from  User u where u.legalEntityCode =sch.legalEntityCode and u.userId=sch.maker) as maker "
				//+ ", (Select count(*) from  AuditObservation o where o.legalEntityCode =sch.legalEntityCode and o.auditId=sch.auditId and o.complianceCategory='N' and o.reviewStatus='R' ) as responseReject "
				//+ ", (Select count(*) from  AuditObservation o where o.legalEntityCode =sch.legalEntityCode and o.auditId=sch.auditId and o.complianceCategory='C' and o.submissionStatus='A') as complianceCount "
				//+ ", (Select count(*) from  AuditObservation o where o.legalEntityCode =sch.legalEntityCode and o.auditId=sch.auditId and o.complianceCategory='NA' and o.submissionStatus='A') as notApplicableCount "
				+ "  ,0,0,0 "
				//+ " , auditStatus "
				+ ", (Select g.value from GeneralParameter g where g.legalEntityCode =sch.legalEntityCode and g.key1='AUDIT_EARLY_START' and g.key2='EARLY_START_ALLOWED') as earlyStartAllowed "
				+ ", (Select g.value from GeneralParameter g where g.legalEntityCode =sch.legalEntityCode and g.key1='AUDIT_OFFSET_DAYS' and g.key2='AUDIT_OFFSET_DAYS') as auditOffsetDays " 
				+ ", (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.observationStatus) as auditStatusTemp " // This result to stamp in Audit status in the Observation status is AE i.e Audit Reported
				
				+ ", (Select count(*) from  TaskDetails t where t.legalEntityCode =sch.legalEntityCode and t.auditId=sch.auditId and t.auditTypeCode=sch.auditTypeCode) as taskTotalCount "
				+ ", (Select count(*) from  TaskDetails t where t.legalEntityCode =sch.legalEntityCode and t.auditId=sch.auditId and t.auditTypeCode=sch.auditTypeCode and t.taskStatus ='Open') as taskOpenCount "
				
				+ ", (select group_concat(t.userId) from AuditTeam t where t.legalEntityCode=sch.legalEntityCode AND t.auditId=sch.auditId AND t.userType='M' ) AS teamMember "
				+ " from AuditSchedule sch where legalEntityCode =:legalEntityCode "
				//+ " and sch.legalEntityCode=t.legalEntityCode and t.auditId=sch.auditId and t.userId=:userId"
				//+ " and   IFNULL(auditStatus,'NotNull') not in ('AD','AU','DRP','UP') "
				//+ " and  sch.auditId IN (SELECT t.auditId FROM AuditTeam t WHERE t.auditId=sch.auditId and t.legalEntityCode =sch.legalEntityCode and t.userId=:userId  ) "
				
				//if(visibleAuditList !=null && visibleAuditList.s)
				+ " and ( auditStatus in ('AC','AW')) " //commented to remove Visibility cond, Condition included to get the Audits as per the Visibility logic maintained in general parameter.
				+ " AND sch.unitId IN (:unitList) "
				);
			if(!isDeptAsRMD)
				queryString.append(" AND sch.auditTypeCode IN (:deptList) ");
				
		System.out.println("Before the qry....");
		
		if(!BankAuditUtil.isEmptyString(search)){
			queryString=queryString.append(" and ( "
					+ " sch.auditTypeCode IN (SELECT a.auditTypeCode FROM MaintAuditTypeDesc a  WHERE a.legalEntityCode=sch.legalEntityCode AND a.auditTypeDesc LIKE :search) "
					+ " or planId like :search "
					+ " or auditId like :search "
					//+ " or unitId like :search "
					+ " or sch.unitId IN (SELECT m.unitCode FROM MaintEntity m WHERE m.legalEntityCode=sch.legalEntityCode AND (m.unitName LIKE :search or m.unitCode LIKE :search )) "
					+ " or sch.unitId IN (SELECT m.unitCode FROM MaintEntity m,GeneralParameter g WHERE sch.unitId=m.unitCode and m.legalEntityCode=sch.legalEntityCode AND m.legalEntityCode =g.legalEntityCode and g.key1='UNIT_TYPE' and g.key2='UNIT_TYPE' and g.value=m.unitType and g.description LIKE :search) "
					+ " or sch.unitId IN (SELECT m.unitCode FROM MaintEntity m,MaintCriticality c WHERE sch.unitId=m.unitCode and m.legalEntityCode=sch.legalEntityCode AND m.legalEntityCode =c.legalEntityCode and m.uCriticality=c.criticalityCode and c.criticalityOfType='Entity' and  c.criticalityDesc LIKE :search) "
					+ " or sch.unitId IN (SELECT m.unitCode FROM MaintEntity m,MaintAuditParameter p WHERE sch.unitId=m.unitCode and m.legalEntityCode=sch.legalEntityCode AND m.legalEntityCode =p.legalEntityCode and m.uCriticality=p.uCriticality and sch.auditTypeCode=p.auditTypeCode and  p.duration LIKE :search) "
					+ " or sch.auditId IN (SELECT t.auditId FROM User u,AuditTeam t WHERE t.auditId=sch.auditId and t.legalEntityCode =sch.legalEntityCode and t.legalEntityCode =u.legalEntityCode and t.userId=u.userId and (t.userId Like :search or u.firstName LIKE :search or u.lastName Like :search) ) "
					+ " or DATE_FORMAT(sch.scheduleStartDate, '%d %b %Y') LIKE :search "
					+ " or DATE_FORMAT(sch.scheduleEndDate, '%d %b %Y') LIKE :search "
					+ " or DATE_FORMAT(sch.actualStartDate, '%d %b %Y') LIKE :search "
					+ " or DATE_FORMAT(sch.actualEndDate, '%d %b %Y') LIKE :search "
					+ " or DATE_FORMAT(sch.lastAuditDate, '%d %b %Y') LIKE :search "
					+ " or sch.maker IN (SELECT u.userId FROM User u WHERE u.legalEntityCode =sch.legalEntityCode and (u.userId Like :search or u.firstName LIKE :search or u.lastName Like :search) ) "
					+ " or sch.auditStatus IN (SELECT g.value FROM GeneralParameter g WHERE sch.legalEntityCode =g.legalEntityCode and g.key1='STATUS' and g.key2='AUDIT_STATUS' and g.value=sch.auditStatus and g.description LIKE :search) "
					+ " or sch.auditId IN (select t.auditId from AuditTeam t, User u where t.legalEntityCode=sch.legalEntityCode AND t.auditId=sch.auditId AND t.userType='M' AND t.legalEntityCode=u.legalEntityCode AND t.userId=u.userId "
					+ "						AND sch.unitId=u.unitCode AND (u.firstName like :search OR u.middleName like :search OR u.lastName like :search OR u.userId like :search )) "
					+ " ) "
					// + " )"
					);
		}
		
		/*queryString=queryString.append("  ORDER BY FIELD(auditStatus, 'AI','P','AP','AS','AC','AU') asc ");*/
		String[] columns={
				"auditTypeCode",
				"(auditId *1 ), (planId *1 )", //"CAST(planId AS Integer),CAST(auditId AS Integer) "
				"unitType",
				"(unitId * 1) ",  //"unitId",
				//"uCriticality",
				//"duration",
				//"lastAuditDate",//last audit date actually 
				"sch.scheduleStartDate",
				"sch.scheduleEndDate",
				//"sch.actualStartDate",
				//"sch.actualEndDate",
				"teamLead", // Team Lead
				"sch.maker", // Team Member
				 "taskTotalCount",
				 "taskOpenCount",
				//"sch.maker",
				"sch.auditStatus"
		};

		/*if(orderColumn!=null
				&& !BankAuditUtil.isEmptyString(orderDirection)){
			queryString=queryString.append(" order by ").append(columns[orderColumn]).append(" ").append(orderDirection);
		}*/
		logger.info("orderColumn **.... "+ orderColumn +"  orderDirection ...."+ orderDirection );
		if(orderColumn != null && orderColumn == -1 && !BankAuditUtil.isEmptyString(orderDirection)){ // -1 send for the first time from the UI during landing page.
			queryString=queryString.append(" ORDER BY FIELD(auditStatus, 'AW','AC') asc  "); // The code modified to get the records in the order of Status on landing page, the sequence is  Initiated, Planned, Submitted, Respond, Reviewed, Closed
		}else if(orderColumn!=null && !BankAuditUtil.isEmptyString(orderDirection)){
			if(orderColumn==0) {
				queryString=queryString.append(" ORDER BY  ").append(columns[orderColumn]).append(" ").append(orderDirection).append(" , FIELD(auditStatus, 'AW','AC') asc ,(auditId *1 ) asc  ");
			}else {
				queryString=queryString.append(" ORDER BY  ").append(columns[orderColumn]).append(" ").append(orderDirection);
			}
		}else {
			queryString=queryString.append(" ORDER BY FIELD(auditStatus, 'AW','AC') asc  ");
		}
		
		logger.info("queryString .... "+ queryString.toString());
		
		Query query=(Query) session.createQuery(queryString.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];
							/* session.evict(maintEntity);*/
							
							if(tuple[1]!=null){
								auditSchedule.setLegalEntityCode(Integer.parseInt(tuple[1].toString()));								
							}
							if(tuple[2]!=null){
								auditSchedule.setAuditTypeCode(tuple[2].toString());
							}
							
							if(tuple[3]!=null){
								auditSchedule.setPlanId(tuple[3].toString());
							}
							
							if(tuple[4]!=null){
								auditSchedule.setAuditId(tuple[4].toString());
							}
							if(tuple[5]!=null){
								auditSchedule.setUnitId(tuple[5].toString());
							}
							if(tuple[6]!=null){
								auditSchedule.setUnitType(tuple[6].toString());
							}
							if(tuple[7]!=null){
								auditSchedule.setuCriticality(tuple[7].toString());
							}
							if(tuple[8]!=null){
								//auditSchedule.setTeamLead(tuple[8].toString()+"-"+userDtls.get(tuple[8].toString()));
								auditSchedule.setTeamLead(userDtls.get(tuple[8].toString()));
							}
							if(tuple[9]!=null){
								auditSchedule.setDuration(tuple[9].toString());
							}
							if(tuple[10]!=null){
								auditSchedule.setFrequency(tuple[10].toString());
							}
							if(tuple[11]!=null){
								if("P".equalsIgnoreCase(tuple[11].toString()))auditSchedule.setAuditStatus("AP"); // temp purpose as in few earlier records the Planned status is reflecting as "P"
								else if("AE".equalsIgnoreCase(auditSchedule.getObservationStatus())) auditSchedule.setAuditStatus(tuple[18] !=null ?tuple[18].toString() :tuple[11].toString() );
								else auditSchedule.setAuditStatus(tuple[11].toString());
							}
							if(tuple[12]!=null){
								auditSchedule.setMaker(tuple[12].toString());
							}
							if(tuple[13]!=null && auditSchedule.getRespondedFindings() != null){
								auditSchedule.setResponseReject(Integer.parseInt(tuple[13].toString()));
								auditSchedule.setRespondedFindings(auditSchedule.getRespondedFindings()- (Integer.parseInt(tuple[13].toString()))); // Included to find out the Responded Observations
							}else auditSchedule.setResponseReject(0);
							
							if(tuple[14]!=null){
								auditSchedule.setComplianceCount(Integer.parseInt(tuple[14].toString()));
							}else auditSchedule.setComplianceCount(0);
							
							if(tuple[15]!=null){
								auditSchedule.setNotApplicableCount(Integer.parseInt(tuple[15].toString()));
							}else auditSchedule.setNotApplicableCount(0);
							
							if(tuple[16]!=null){
								auditSchedule.setEarlyStartAllowed(tuple[16].toString());
							}else auditSchedule.setEarlyStartAllowed("Y");
							
							if(tuple[17]!=null){
								auditSchedule.setAuditOffsetDays(Integer.parseInt(tuple[17].toString()));
							}else auditSchedule.setAuditOffsetDays(0);
							
							if(tuple[19]!=null){
								auditSchedule.setTaskTotalCount(Integer.parseInt(tuple[19].toString()));
							}else auditSchedule.setTaskTotalCount(0);
							
							if(tuple[20]!=null){
								auditSchedule.setTaskOpenCount(Integer.parseInt(tuple[20].toString()));
							}else auditSchedule.setTaskOpenCount(0);
							
							if(tuple[21]!=null){
								String teamMembers="";
								String[] members = tuple[21].toString().split(",");
								for (String member: members) {
									teamMembers+=member+"-"+userDtls.get(member)+",";
								}
								auditSchedule.setTeamMember(teamMembers.substring(0,teamMembers.length()-1));
							}
							
							//int tmSize =auditSchedule.getAuditTeams().size()-1;
							// String teamMember="";
							//logger.info("Audit Id .. .... "+ auditSchedule.getAuditId()+" tmSize .."+tmSize);
							/*for( AuditTeam adtTm: auditSchedule.getAuditTeams() ) {
								adtTm.setUserId(adtTm.getUserId()+"-"+userDtls.get(adtTm.getUserId()));
								if(!"L".equalsIgnoreCase(adtTm.getUserType())) teamMember +=adtTm.getUserId() + ",";
								logger.info("teamMember .... "+ teamMember);
							}if(tmSize >0 ) auditSchedule.setTeamMember(teamMember.substring(0,teamMember.lastIndexOf(",")));*/
							//logger.info("Team member .... "+auditSchedule.getTeamMember());
							
						}
						return auditSchedule;
					}
			@Override
			public List transformList(List collection) {
				return collection;
			}
		}).setParameter("legalEntityCode",legalEntityCode)  //.setParameter("userId",userId).setParameter("visibleAuditList", visibleAuditList);
				.setParameter("unitList", unitList); 

		if(!isDeptAsRMD)
			query.setParameter("deptList", deptList);
		
		if(!BankAuditUtil.isEmptyString(search)){
			query.setParameter("search", "%"+search+"%");
		}
		
		logger.info("page :: "+page +"size :: "+ size);
		List<AuditSchedule> tmpAuditSchedule =query.list(); 
		Long totalRecords=null;
		try {
			totalRecords = (long)tmpAuditSchedule.size();
		}catch(Exception e) {
			totalRecords = (long)0;
		}
		List<AuditSchedule> auditSchedule=query.setFirstResult((page*size)).setMaxResults(size).list();
		
		logger.info("Bfr size tmpAuditSchedule is .... "+tmpAuditSchedule.size()+" totalRecords:: "+ totalRecords);
		dataTableResponse.setData(auditSchedule);
		dataTableResponse.setRecordsTotal(totalRecords);
		dataTableResponse.setRecordsFiltered(totalRecords);
		
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		return dataTableResponse;
	}
	
	@Override
	public DataTableResponse getTaskListAssessmentWise(int legalEntityCode, String planId, String auditId, String userId, String search, Integer orderColumn,
			String orderDirection, Integer page, Integer size) {

		Session session = getSession();
		session.setDefaultReadOnly(true);
		List<TaskDetails> taskDetailsList = null;

		DataTableResponse dataTableResponse=new DataTableResponse();

		StringBuilder queryString=new StringBuilder(
				  " SELECT "
						  +" id, legal_entity_code,"
						  
						  + "(select concat(atd.audit_type_code , '-' , atd.audit_type_desc)"
							+ " from maint_audit_type_desc atd where "
							+ " atd.audit_type_code=aa.audit_type_code "
							+ " and atd.legal_entity_code=aa.legal_entity_code ) as audit_type_code , "
						  
						  +" audit_id, observation_id, action_plan, resp_implementation, resp_monitoring, due_date, "
						  
						  + " (select concat(gp.value , '-' , gp.description) from general_parameter gp where "
							 + " gp.key_1='TASK_STATUS' and gp.key_2='TASK_STATUS' and gp.value=aa.task_status "
							 + " and gp.legal_entity_code=aa.legal_entity_code ) as task_status , "
							 
						  + " (select concat(gp.value , '-' , gp.description) from general_parameter gp where "
							 + " gp.key_1='ENTITY_STATUS' and gp.key_2='ENTITY_STATUS' and gp.value=aa.entity_status "
							 + " and gp.legal_entity_code=aa.legal_entity_code ) as entity_status , " 
							 
						  + " (select concat(gp.value , '-' , gp.description)"
				    		 + " from general_parameter gp where "
				    		 + " gp.key_1='STATUS' and gp.value=aa.status "
				    		 + " and gp.legal_entity_code=aa.legal_entity_code ) as status , "
						  
						 + "(select concat(u.user_id , '-' ,u.first_name,' ',u.last_name )"
						 + " from user u where "
						 + " u.user_id=aa.maker "
						 + " and u.legal_entity_code=aa.legal_entity_code ) as maker ,  "	 
						  
						  +" maker_timestamp, "
						  +" checker, "
						  +" checker_timestamp, "
						  +" businessdate_timestamp, "
						  +" sysdate_timestamp "
						  +" , remarks, auth_rej_remarks , actual_closure_date "
						  +"  FROM "
						  +"  ( SELECT * FROM task_details WHERE legal_entity_code =:legal_entity_code "
						  + "    and entity_status !='E'  and audit_id= :auditId "
						  +" UNION  ALL "
						  +"   SELECT * FROM task_details_wrk WHERE legal_entity_code =:legal_entity_code "
						  + "	 and audit_id= :auditId "
						  +"  ) AS aa "
						  +" WHERE legal_entity_code =:legal_entity_code "
				);

		if(!BankAuditUtil.isEmptyString(search)){
			queryString=queryString.append(" and ( aa.id like :search "
					+ " or  aa.audit_type_code like :search  "
					+ " or  aa.maker like :search  "
					+ " or  aa.audit_id like :search"
					+ " or  aa.action_plan like :search  or aa.resp_implementation like :search or aa.resp_monitoring like :search  "
					+ " or  aa.maker in ( "
					+ " select u.user_id "
					+ " from user u where "
					+ " (concat(u.first_name , ' ' , u.last_name) like :search  ) "
					+ " and u.legal_entity_code=aa.legal_entity_code ) "
					+ " or  aa.task_status IN (SELECT value FROM general_parameter  WHERE key_1='TASK_STATUS' and  description LIKE :search ) "
					+ " or  aa.status IN (SELECT value FROM general_parameter  WHERE key_1='STATUS' and  description LIKE :search ) "
					+ " or  aa.entity_status IN (SELECT value FROM general_parameter  WHERE key_1='ENTITY_STATUS' and key_2='ENTITY_STATUS' and description LIKE :search ) "
					+ " )"
					);
		}



		String[] columns={
				"aa.id",
				"aa.action_plan",
				"aa.resp_implementation","aa.resp_monitoring",
				"aa.due_date", "aa.task_status",
				"aa.entity_status",
		"aa.status"};

		if(orderColumn != null && orderColumn == -1 && !BankAuditUtil.isEmptyString(orderDirection)){ // -1 send for the first time from the UI during landing page.
			queryString=queryString.append(" order by FIELD(STATUS,'R','RM','U','M','A') ASC  "); // The code modified to get the records in the order of Status on landing page, the sequence is  Draft, Reject, Unauth, Modify, Auth
		}else if(orderColumn!=null && !BankAuditUtil.isEmptyString(orderDirection)){
			queryString=queryString.append(" order by FIELD(STATUS,'R','U','M','RM') DESC ,  ").append(columns[orderColumn]).append(" ").append(orderDirection);
		}

		Query query=session.createSQLQuery(queryString.toString())
				.setResultTransformer(new ResultTransformer() {

					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						TaskDetails taskDetails=null;
						if(tuple!=null){
							
							taskDetails=new TaskDetails();
							
							if(tuple[0]!=null)	taskDetails.setId(Integer.parseInt(tuple[0].toString()));
							if(tuple[1]!=null) taskDetails.setLegalEntityCode(Integer.parseInt(tuple[1].toString()));
							if(tuple[2]!=null) taskDetails.setAuditTypeCode(tuple[2].toString());
							if(tuple[3]!=null) taskDetails.setAuditId(tuple[3].toString());
							if(tuple[4]!=null) taskDetails.setObservationId(tuple[4].toString());
							if(tuple[5]!=null) taskDetails.setActionPlan(tuple[5].toString());
							if(tuple[6]!=null) taskDetails.setRespImplementation(tuple[6].toString());
							if(tuple[7]!=null) taskDetails.setRespMonitoring(tuple[7].toString());
							if(tuple[8]!=null) taskDetails.setDueDate((Date)tuple[8]);
							if(tuple[8]!=null) taskDetails.setActualClosureDate((Date)tuple[20]);
							if(tuple[9]!=null) taskDetails.setTaskStatus(tuple[9].toString());
							if(tuple[10]!=null) taskDetails.setEntityStatus(tuple[10].toString());
							if(tuple[11]!=null) taskDetails.setStatus(tuple[11].toString());
							if(tuple[12]!=null) taskDetails.setMaker(tuple[12].toString());
							if(tuple[13]!=null) taskDetails.setMakerTimestamp((Date)tuple[13]);
							if(tuple[14]!=null) taskDetails.setChecker(tuple[14].toString());
							if(tuple[15]!=null) taskDetails.setCheckerTimestamp((Date)tuple[15]);
							if(tuple[16]!=null) taskDetails.setBusinessdateTimestamp((Date)tuple[16]);
							if(tuple[17]!=null) taskDetails.setSysdateTimestamp((Date)tuple[17]);
							if(tuple[18]!=null) taskDetails.setRemarks(tuple[18].toString());
							if(tuple[19]!=null) taskDetails.setAuthRejRemarks(tuple[19].toString());
							
						}
						
						return taskDetails;
					}

					@Override
					public List transformList(List collection) {
						return collection;
					}
				})
				.setParameter("legal_entity_code", legalEntityCode)
				.setParameter("auditId", auditId);



		if(!BankAuditUtil.isEmptyString(search)){
			query.setParameter("search", "%"+search+"%");
		}


		ScrollableResults resultScroll = query.scroll(ScrollMode.SCROLL_INSENSITIVE);
		if(resultScroll.first()&&resultScroll.scroll(page*size)){

			taskDetailsList= new ArrayList<>();
			int i = 0;
			while (size > i++) {
				taskDetailsList.add((TaskDetails) resultScroll.get(0));
				if (!resultScroll.next())
					break;
			}
			dataTableResponse.setData(taskDetailsList);
			resultScroll.last();
			if(size!=0){
				dataTableResponse.setRecordsFiltered((resultScroll.getRowNumber()+1l/size)+1);
				dataTableResponse.setRecordsTotal((resultScroll.getRowNumber()+1l/size)+1);
			}else{
				dataTableResponse.setError("page size zero");
				dataTableResponse.setData(Collections.EMPTY_LIST);
				dataTableResponse.setRecordsTotal(0l);
				dataTableResponse.setRecordsFiltered(0l);
			}

		}else{
			dataTableResponse.setError(null);
			dataTableResponse.setData(Collections.EMPTY_LIST);
			dataTableResponse.setRecordsTotal(0l);
			dataTableResponse.setRecordsFiltered(0l);
		}
		return dataTableResponse;

	}
	
	
}
