package com.bankaudit.rest.process.dao;

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

import org.hibernate.ScrollMode;
import org.hibernate.ScrollableResults;
import org.hibernate.Session;
import org.hibernate.query.Query;
import org.hibernate.transform.ResultTransformer;
import org.jboss.logging.Logger;
import org.springframework.stereotype.Repository;
import com.bankaudit.dao.AbstractDao;
import com.bankaudit.dto.DataTableResponse;
import com.bankaudit.dto.process.AlertsDto;
import com.bankaudit.dto.process.AlertsResponseDto;
import com.bankaudit.model.ActivityProcessFindingMapping;
import com.bankaudit.model.MaintEntity;
import com.bankaudit.process.model.AlertMst;
import com.bankaudit.process.model.AuditSchedule;
import com.bankaudit.process.model.AuditTeam;
import com.bankaudit.process.model.TaskDetails;
import com.bankaudit.util.BankAuditUtil;

import java.util.ArrayList;

@Repository
public class AlertToDoDaoImpl extends AbstractDao implements AlertToDoDao{
	
	static final Logger logger = Logger.getLogger(AlertToDoDaoImpl.class);
	
	@SuppressWarnings("unchecked")
	@Override
	public Map<String, Integer> getAlertsCount(Integer legalEntityCode, String userId, String role) {
		Session session=getSession();
		Map<String, Integer> alertDtls =null;
		try {
			logger.info("getAlertsCount legalEntityCode.."+legalEntityCode+" userId.."+userId+" role.."+role);
			Query hql= session.createQuery(" select alertsCode,userId,count(userId) from AlertMst "
					+ " where legalEntityCode=:legalEntityCode AND userId =:userId "
					+ "group by alerts_code,user_id")
					.setParameter("legalEntityCode", legalEntityCode)
					.setParameter("userId", userId);
			List lst = hql.list();
			
			alertDtls= new HashMap();int totalAlerts=0;
			for ( Iterator iter = lst.iterator(); iter.hasNext(); ) {
				Object[] alrtObj= (Object[]) iter.next();
				alertDtls.put(alrtObj[0].toString(), Integer.parseInt(alrtObj[2].toString())); 
				totalAlerts += Integer.parseInt(alrtObj[2].toString()); // Adding up all the Alerts for the given User
			}alertDtls.put("TOTAL_ALERTS", totalAlerts);
			
			logger.info("getAlertsCount alertDtls .."+alertDtls);
		}catch(Exception e) {
			e.printStackTrace();
		}
		return alertDtls;
		
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public DataTableResponse getAlertsByUserIdAlertIdDataTable(int legalEntityCode,String userId,String alertId,String role, String search,Integer orderColumn,
			String orderDirection, Integer page, Integer size) {
		Session session=getSession();
		DataTableResponse dataTableResponse=null;
		List<AlertsResponseDto> alertsResponseDtoLst = null;
		try {
			logger.info("getAlertsByUserIdAlertIdDataTable legalEntityCode.."+legalEntityCode+" userId.."+userId+" role.."+role+" alertId.."+alertId);
			StringBuilder qryString=new StringBuilder(" select alertsCode,userId,colHeader,colValue from AlertMst "
					+ " where legalEntityCode=:legalEntityCode AND userId =:userId ");
			
			if(!BankAuditUtil.isEmptyString(search)){
				qryString.append(" and ( alertsCode like :search "
				+ " or  colValue like :search )  ");
			}
						
			Query query= session.createQuery(qryString.toString())
					.setResultTransformer(new ResultTransformer() {
				@Override
				public Object transformTuple(Object[] tuple, String[] aliases) {
					AlertsResponseDto alertResponseDto=null; 
					Map<String, String> colHdrValMap = null;
					if(tuple!=null){
						alertResponseDto=new AlertsResponseDto();
						if(tuple[0]!=null){
							alertResponseDto.setAlertCode(tuple[0].toString());
						}
						if(tuple[1]!=null){
							alertResponseDto.setUserName(tuple[1].toString());						
			            }
						if(tuple[2]!=null && tuple[3]!=null){
							colHdrValMap = new HashMap();
							String[] colHdr = tuple[2].toString().split(",");
							String[] colVal = tuple[3].toString().split(",");
							for (int i = 0; i < colHdr.length; i++) {
								colHdrValMap.put(colHdr[i], colVal[i]);
							}
							alertResponseDto.setColHeaderValues(colHdrValMap);
			            }						
					}
					
					return alertResponseDto;
				}
				
				@Override
				public List transformList(List collection) {
					return collection;
				}
			}).setParameter("legalEntityCode", legalEntityCode)
			.setParameter("userId", userId);
			
			if(!BankAuditUtil.isEmptyString(search)){
				query.setParameter("search", "%"+search+"%");
			}
			
			dataTableResponse=new DataTableResponse();
			
			ScrollableResults resultScroll = query.scroll(ScrollMode.SCROLL_INSENSITIVE);
			if(resultScroll.first()&&resultScroll.scroll(page*size)){
				
				alertsResponseDtoLst= new ArrayList<>();
				int i = 0;
				while (size > i++) {
					alertsResponseDtoLst.add((AlertsResponseDto) resultScroll.get(0));
				    if (!resultScroll.next())
				        break;
				}
				dataTableResponse.setData(alertsResponseDtoLst);
				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.emptyList());
					dataTableResponse.setRecordsTotal(0l);
					dataTableResponse.setRecordsFiltered(0l);
				}
				
			}else{
				dataTableResponse.setError(null);
				dataTableResponse.setData(Collections.emptyList());
				dataTableResponse.setRecordsTotal(0l);
				dataTableResponse.setRecordsFiltered(0l);
			}
						
		}catch(Exception e) {
			e.printStackTrace();
		}
		return dataTableResponse;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<AlertsResponseDto> getListofAlerts(Integer legalEntityCode, String userId, String role, String alertId) {
		Session session=getSession();
		List<AlertsResponseDto> lst = null;
		try {
			logger.info("getListofAlerts legalEntityCode.."+legalEntityCode+" userId.."+userId+" role.."+role+" alertId.."+alertId);
			Query hql= session.createQuery(" select alertsCode,userId,colHeader,colValue from AlertMst "
					+ " where legalEntityCode=:legalEntityCode AND userId =:userId ")
					.setParameter("legalEntityCode", legalEntityCode)
					.setParameter("userId", userId);
			
			lst= hql.setResultTransformer(new ResultTransformer() {
				@Override
				public Object transformTuple(Object[] tuple, String[] aliases) {
					AlertsResponseDto alertResponseDto=null; 
					Map<String, String> colHdrValMap = null;
					if(tuple!=null){
						alertResponseDto=new AlertsResponseDto();
						if(tuple[0]!=null){
							alertResponseDto.setAlertCode(tuple[0].toString());
						}
						if(tuple[1]!=null){
							alertResponseDto.setUserName(tuple[1].toString());						
			            }
						if(tuple[2]!=null && tuple[3]!=null){
							colHdrValMap = new HashMap();
							String[] colHdr = tuple[2].toString().split(",");
							String[] colVal = tuple[3].toString().split(",");
							for (int i = 0; i < colHdr.length; i++) {
								colHdrValMap.put(colHdr[i], colVal[i]);
							}
							alertResponseDto.setColHeaderValues(colHdrValMap);
			            }						
					}
					
					return alertResponseDto;
				}
				
				@Override
				public List transformList(List collection) {
					return collection;
				}
			}).list();
			logger.info("getAlertsCount alertDtls .."+lst);
		}catch(Exception e) {
			e.printStackTrace();
		}
		return lst;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<AuditSchedule> getPlanAuth(Integer legalEntityCode, String userId, String role, String unitId) {
		Session session=getSession(); 
		List<AuditSchedule> planAuthCountLst =null;
		logger.info("inside getPlanAuthCount .."+legalEntityCode +" :: "+ userId+" :: "+ role+" :: "+unitId);
		try {
		planAuthCountLst = (List<AuditSchedule>)session.createQuery(" FROM AuditSchedule as sch WHERE "
				+ " sch.legalEntityCode =:legalEntityCode AND sch.auditStatus ='AP'  "
				+ " AND sch.auditId IN (SELECT t.auditId from AuditTeam as t where t.legalEntityCode=sch.legalEntityCode AND t.auditId=sch.auditId "
				+ "						AND t.userId =:userId ) ")
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("userId", userId)
				.list();
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		return planAuthCountLst;
	}
	
	@Override
	public DataTableResponse getPlanAuth(Integer legalEntityCode, String search, Integer orderColumn, String orderDirection,
			 Integer page, Integer size, String userId, String role, String unitId, String alertName ) {
	    Session session = getSession();
	    session.setDefaultReadOnly(true);
		List<AuditSchedule> auditScheduleLst = null;
		
		DataTableResponse dataTableResponse=new DataTableResponse();
		
		StringBuilder queryString=new StringBuilder(" FROM AuditSchedule as sch WHERE "
				+ " sch.legalEntityCode =:legalEntityCode AND sch.auditStatus ='AP'  "
				+ " AND sch.auditId IN (SELECT t.auditId from AuditTeam as t where t.legalEntityCode=sch.legalEntityCode AND t.auditId=sch.auditId "
				+ "						AND t.userId =:userId ) ");
		
		if(!BankAuditUtil.isEmptyString(search)){
			queryString=queryString.append(" and (sch.auditId LIKE :search "
					+ " 	or sch.auditTypeCode IN (select atd.auditTypeCode from MaintAuditTypeDesc atd where"
				    + "  								atd.auditTypeDesc LIKE :search and atd.legalEntityCode=sch.legalEntityCode )"
				    + "     or  sch.scheduleStartDate LIKE :search or sch.scheduleEndDate LIKE :search "
					+ " ) ");
		}
	
		String[] columns={
				"sch.auditId",
				"sch.auditTypeCode",
				"sch.scheduleStartDate",
				"sch.scheduleEndDate"};
		
		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 sch.scheduleStartDate 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 ").append(columns[orderColumn]).append(" ").append(orderDirection);
		}
		
		Query query=session.createQuery(queryString.toString())
				.setResultTransformer(new ResultTransformer() {
					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						AuditSchedule auditSchedule=null;
						if(tuple!=null){
							auditSchedule=new AuditSchedule();
							if(tuple[0]!=null) auditSchedule =(AuditSchedule)tuple[0];
						}
						return auditSchedule;
					}
					@Override
					public List transformList(List collection) {
						return collection;
					}
				})
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("userId", userId);
		
		if(!BankAuditUtil.isEmptyString(search)){
			query.setParameter("search", "%"+search+"%");
		}
		
		ScrollableResults resultScroll = query.scroll(ScrollMode.SCROLL_INSENSITIVE);
		if(resultScroll.first()&&resultScroll.scroll(page*size)){
			
			auditScheduleLst= new ArrayList<>();
			int i = 0;
			while (size > i++) {
				auditScheduleLst.add((AuditSchedule) resultScroll.get(0));
			    if (!resultScroll.next())
			        break;
			}
			dataTableResponse.setData(auditScheduleLst);
			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;
	} 
	
	@Override
	public DataTableResponse getRiskAssessedAlerts(Integer legalEntityCode, String search, Integer orderColumn, String orderDirection,
			 Integer page, Integer size, String userId, String role, String unitId, String alertName ) {
	    Session session = getSession();
	    session.setDefaultReadOnly(true);
		List<AlertsDto> alertsDtoLst = null;
		
		logger.info("getRiskAssessedAlerts .. "+legalEntityCode +" :: "+ userId +" :: "+ role +" :: "+ unitId);
		DataTableResponse dataTableResponse=new DataTableResponse();
		
		StringBuilder queryString=new StringBuilder(" ");
		if("G2".equalsIgnoreCase(role) || "G8".equalsIgnoreCase(role)) {
			queryString.append(" select   audit_id, audit_type_code, total, auth, pendAuth, unAuth   "
					+ " from ( "
					+ " 	select sch.audit_id, sch.audit_type_code, "
					+ " 		(select count(*) from audit_observation o where o.audit_id=sch.audit_id AND o.legal_entity_code=sch.legal_entity_code ) as total, "
					+ " 		(select count(*) from audit_observation o where o.audit_id=sch.audit_id AND o.legal_entity_code=sch.legal_entity_code AND o.submission_status='A') as auth, "
					+ " 		(select count(*) from audit_observation o where o.audit_id=sch.audit_id AND o.legal_entity_code=sch.legal_entity_code AND o.submission_status='PA') as pendAuth, "
					+ " 		(select count(*) from audit_observation o where o.audit_id=sch.audit_id AND o.legal_entity_code=sch.legal_entity_code AND ifnull(o.submission_status,'U') not in ('A','PA')) as unAuth "
					+ "   	from audit_schedule sch, audit_team t where  t.legal_entity_code=sch.legal_entity_code  "
					+ "			AND sch.observation_status='AI' "
					+ " 	  	AND t.audit_id= sch.audit_id AND sch.legal_entity_code=:legalEntityCode AND t.user_id=:userId   "
					+ " 	  	AND sch.audit_id IN (select o.audit_id from audit_observation o where o.audit_id=sch.audit_id AND o.legal_entity_code=sch.legal_entity_code "
					+ "		  		AND  o.submission_status in ('PA','A') )"
					+ "  ) as a " );
		}else if("G3".equalsIgnoreCase(role)) {
			queryString.append(" select audit_id, audit_type_code, total, auth, pendAuth, unAuth  "
					+ "  from ( " 
					+ " 	select sch.audit_id, sch.audit_type_code, " 
					+ " 		(select count(*) from audit_observation o where o.audit_id=sch.audit_id AND o.legal_entity_code=sch.legal_entity_code ) as total, " 
					+ " 		(select count(*) from audit_observation o where o.audit_id=sch.audit_id AND o.legal_entity_code=sch.legal_entity_code AND o.submission_status='A') as auth, " 
					+ " 		(select count(*) from audit_observation o where o.audit_id=sch.audit_id AND o.legal_entity_code=sch.legal_entity_code AND o.submission_status='PA') as pendAuth, " 
					+ " 		(select count(*) from audit_observation o where o.audit_id=sch.audit_id AND o.legal_entity_code=sch.legal_entity_code AND ifnull(o.submission_status,'U') not in ('A','PA')) as unAuth " 
					+ "     from audit_schedule sch, user u where  u.legal_entity_code=sch.legal_entity_code  " 
					+ "			AND Ifnull(respond_status,'RI') = 'RI' "
					+ " 		AND u.unit_code= sch.unit_id AND sch.legal_entity_code=:legalEntityCode AND u.user_id=:userId   " 
					+ " 	    AND sch.audit_id IN (select o.audit_id from audit_observation o where o.audit_id=sch.audit_id AND o.legal_entity_code=sch.legal_entity_code "
					+ "				AND  o.submission_status in ('A') )"
					+ "  ) as a " );
		}
		
		if(!BankAuditUtil.isEmptyString(search)){
			queryString=queryString.append("where  (  "
				+ " 	audit_id LIKE :search or audit_type_code LIKE :search  "
				+ " 	or total LIKE :search or auth LIKE :search  or pendAuth LIKE :search or unAuth LIKE :search  "
			    + " ) ");
		}
		String[] columns={
				"a.audit_id",
				"a.audit_type_code",
				"a.total", "a.auth", "a.pendAuth", "a.unAuth"};
		
		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 a.audit_id, a.audit_type_code 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 ").append(columns[orderColumn]).append(" ").append(orderDirection);
		}
		
		Query query=session.createSQLQuery(queryString.toString())
				.setResultTransformer(new ResultTransformer() {
					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						AlertsDto alertsDto=null;
						if(tuple!=null){
							alertsDto=new AlertsDto();
							if(tuple[0]!=null) alertsDto.setAuditId(tuple[0].toString());
							if(tuple[1]!=null) alertsDto.setRCSAUnnit(tuple[1].toString());
							//if(tuple[2]!=null) alertsDto.setBusinessLine(tuple[2].toString());
							//if(tuple[3]!=null) alertsDto.setProductName(tuple[3].toString());
							//if(tuple[3]!=null) alertsDto.setProductName(tuple[2].toString()+"->"+tuple[3].toString()); // To show the Product along with Business Line
							
							alertsDto.setTotal(tuple[2].toString() != null ? tuple[2].toString().toString() : "0");
							alertsDto.setAuthorised(tuple[3].toString() != null ? tuple[3].toString().toString() : "0");
							alertsDto.setPendingAuthorised(tuple[4].toString() != null ? tuple[4].toString().toString() : "0");
							alertsDto.setUnauthorised(tuple[5].toString() != null ? tuple[5].toString().toString() : "0");
						}
						return alertsDto;
					}
					@Override
					public List transformList(List collection) {
						return collection;
					}
				})
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("userId", userId);
		
		if(!BankAuditUtil.isEmptyString(search)){
			query.setParameter("search", "%"+search+"%");
		}
		
		ScrollableResults resultScroll = query.scroll(ScrollMode.SCROLL_INSENSITIVE);
		if(resultScroll.first()&&resultScroll.scroll(page*size)){
			
			alertsDtoLst= new ArrayList<>();
			int i = 0;
			while (size > i++) {
				alertsDtoLst.add((AlertsDto) resultScroll.get(0));
			    if (!resultScroll.next())
			        break;
			}
			dataTableResponse.setData(alertsDtoLst);
			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;
	} 
	
	@SuppressWarnings("unchecked")
	@Override
	public Integer getRiskAssessedCnt(Integer legalEntityCode, String userId, String role, String unitId) {
		Session session=getSession(); 
		int riskAssessedCnt=0;
		logger.info("inside getRiskAssessedCnt .."+legalEntityCode +" :: "+ userId+" :: "+ role+" :: "+unitId);
		try {
			StringBuilder query= new StringBuilder(" FROM AuditSchedule as sch WHERE "
				+ " sch.legalEntityCode =:legalEntityCode "
				+ " AND sch.auditId IN (SELECT o.auditId from AuditObservation as o where o.legalEntityCode=sch.legalEntityCode AND o.auditId=sch.auditId " );
			if("G2".equalsIgnoreCase(role) || "G8".equalsIgnoreCase(role)) {
				query.append("			AND o.submissionStatus IN ('PA','A')) ");
				query.append("	AND sch.observationStatus = 'AI' ");
				query.append("  AND sch.auditId IN (SELECT t.auditId from AuditTeam as t where t.legalEntityCode=sch.legalEntityCode AND t.auditId=sch.auditId AND t.userId =:userId )" );
				
			}else if("G3".equalsIgnoreCase(role)) {
				query.append("			AND o.submissionStatus IN ('A')) ");
				query.append("	AND Ifnull(sch.respondStatus,'RI') = 'RI' ");
				query.append("  AND unitId = (SELECT unitCode FROM User u where u.legalEntityCode=sch.legalEntityCode and u.userId =:userId ) ");
			}
			logger.info("query .."+ query.toString());
			List lst = session.createQuery(query.toString())	
					.setParameter("legalEntityCode", legalEntityCode)
					.setParameter("userId", userId).list();
			riskAssessedCnt=  lst.size();
			logger.info("size of list .... "+riskAssessedCnt);
		
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		return riskAssessedCnt;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public Integer getAuditInitiationCnt(Integer legalEntityCode, String userId, String role, String unitId, Boolean isDeptAsRMD, List<String> deptList) {
		Session session=getSession(); 
		int auditInitiationCnt=0;
		logger.info("inside getAuditInitiationCnt .. .."+legalEntityCode +" :: "+ userId+" :: "+ role+" :: "+unitId);
		logger.info("isDeptAsRMD .. "+isDeptAsRMD +" deptList .. "+deptList);
		try {
			StringBuilder query= new StringBuilder(" "
				+ " FROM AuditSchedule as sch WHERE respondStatus='RI' and legalEntityCode=:legalEntityCode AND "
				+ "	         unitId = (SELECT unitCode FROM User u where u.legalEntityCode=sch.legalEntityCode and u.userId =:userId ) ");
			if("G2".equalsIgnoreCase(role) || "G8".equalsIgnoreCase(role)) { // for Assessor, only after Assessment submitted.(because before that they got the info from Assessed Risks Alerts)
				query.append(" AND observationStatus= 'AS' ");
			}else if("G4".equalsIgnoreCase(role) || "G7".equalsIgnoreCase(role)) { // for Advisor, till ActionPlan initiated.(because post ActionPlan Initiate diff Alert is there)
				query.append(" AND reviewStatus IS NULL ");
				if(!isDeptAsRMD )
					query.append(" AND sch.auditTypeCode IN (:deptList) ");
			}
						
			Query qry = session.createQuery(query.toString())	
					.setParameter("legalEntityCode", legalEntityCode)
					.setParameter("userId", userId);
			if(!isDeptAsRMD )
				qry.setParameter("deptList", deptList);
			
			List lst= qry.list();
			
			auditInitiationCnt=  lst.size();
			logger.info("size of list .... "+auditInitiationCnt);
		
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		return auditInitiationCnt;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public Integer getAuditSubmitCnt(Integer legalEntityCode, String userId, String role, String unitId, Boolean isDeptAsRMD, List<String> deptList) {
		Session session=getSession(); 
		int auditSubmitCnt=0;
		logger.info("inside getAuditSubmitCnt .."+legalEntityCode +" :: "+ userId+" :: "+ role+" :: "+unitId);
		logger.info("isDeptAsRMD .. "+isDeptAsRMD +" deptList .. "+deptList);
		try {
			StringBuilder query= new StringBuilder(" "
				+ " FROM AuditSchedule as sch WHERE respondStatus='RS' AND ifnull(reviewStatus,'NA') in ('NA') AND legalEntityCode=:legalEntityCode AND "
				+ "	         unitId = (SELECT unitCode FROM User u where u.legalEntityCode=sch.legalEntityCode and u.userId =:userId ) ");
			if(!isDeptAsRMD)
				query.append(" AND sch.auditTypeCode IN (:deptList) ");
			
			Query qry = session.createQuery(query.toString())	
					.setParameter("legalEntityCode", legalEntityCode)
					.setParameter("userId", userId);
			if(!isDeptAsRMD)
				qry.setParameter("deptList", deptList);
			
			List lst= qry.list();
			
			auditSubmitCnt=  lst.size();
			logger.info("size of AuditSubmit list .... "+auditSubmitCnt);
		
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		return auditSubmitCnt;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public Integer getActionPlanInitiateCnt(Integer legalEntityCode, String userId, String role, String unitId, List<String> unitList, Boolean isDeptAsRMD, List<String> deptList) {
		Session session=getSession(); 
		int actionPlanInitiateCnt=0;
		logger.info("inside getActionPlanInitiateCnt .."+legalEntityCode +" :: "+ userId+" :: "+ role+" :: "+unitId+" :: "+ unitList);
		logger.info("isDeptAsRMD .. "+isDeptAsRMD +" deptList .. "+deptList);
		try {
			StringBuilder query= new StringBuilder(" "
				+ " FROM AuditSchedule as sch WHERE respondStatus IN ('RI','RS') AND reviewStatus='WI' AND legalEntityCode=:legalEntityCode AND "
				+ "  unitId IN (:unitList) ");
				//+ "	         unitId = (SELECT unitCode FROM User u where u.legalEntityCode=sch.legalEntityCode and u.userId =:userId ) ");
			if(!isDeptAsRMD)
				query.append(" AND sch.auditTypeCode IN (:deptList) ");
			
			Query qry = session.createQuery(query.toString())	
					.setParameter("legalEntityCode", legalEntityCode)
					.setParameter("unitList", unitList);
			
			if(!isDeptAsRMD)
				qry.setParameter("deptList", deptList);
			
			List lst= qry.list();
			
			actionPlanInitiateCnt=  lst.size();
			logger.info("size of Action Plan Initiate .... "+actionPlanInitiateCnt);
		
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		return actionPlanInitiateCnt;
	}	
	
	@SuppressWarnings("unchecked")
	@Override
	public Integer getRCSAClosureCnt(Integer legalEntityCode, String userId, String role, String unitId, Date businessDate, List<String> unitList, Boolean isDeptAsRMD, List<String> deptList) {
		Session session=getSession(); 
		int RCSAClosureCnt=0;
		logger.info("inside getRCSAClosureCnt .."+legalEntityCode +" :: "+ userId+" :: "+ role+" :: "+unitId + " :: "+businessDate +" :: "+ unitList);
		logger.info("isDeptAsRMD .. "+isDeptAsRMD +" deptList .. "+deptList);
		try {
			StringBuilder query= new StringBuilder(" "
				/*+ " FROM AuditSchedule as sch WHERE reviewStatus IN ('WS','AC') AND legalEntityCode=:legalEntityCode  "
				+ "	         AND unitId = (SELECT unitCode FROM User u where u.legalEntityCode=sch.legalEntityCode and u.userId =:userId )  "
				+ " 		 AND DATE_ADD(reviewedDate, INTERVAL (SELECT value FROM GeneralParameter WHERE key1 ='DAYS_TO_TRIGGER' AND key2='RCSA_CLOSURE' "
				+ "											AND legalEntityCode=:legalEntityCode) DAY) >=:businessDate"
				+ " ");*/
				+ " SELECT sch.audit_id FROM audit_schedule sch WHERE review_status in ('WS','AC')  AND legal_entity_code=:legalEntityCode AND "  
				//+ "	         unit_id = (select unit_code from user u where u.legal_entity_code=sch.legal_entity_code and u.user_id=:userId) AND "
				+ "	   unit_id IN (:unitList) AND "
				+ "    DATE_ADD(reviewed_date, INTERVAL (SELECT value from general_parameter where key_1 ='DAYS_TO_TRIGGER' AND key_2='RCSA_CLOSURE' "
				+ "         AND legal_entity_code=:legalEntityCode) DAY) >= :businessDate ");
			
			if(!isDeptAsRMD && !"G3".equalsIgnoreCase(role)) // Auditor can see across the Dept.
				query.append(" AND sch.audit_type_code IN (:deptList) ");
			
			logger.info("query .... "+ query.toString());
			Query qry = session.createSQLQuery(query.toString())	
					.setParameter("legalEntityCode", legalEntityCode)
					.setParameter("unitList", unitList)
					.setParameter("businessDate", businessDate);
			
			if(!isDeptAsRMD && !"G3".equalsIgnoreCase(role)) qry.setParameter("deptList", deptList);
			
			List lst = qry.list();
			
			RCSAClosureCnt=  lst.size();
			logger.info("size of list .... "+RCSAClosureCnt);
		
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		return RCSAClosureCnt;
	}	
	
	@SuppressWarnings("unchecked")
	@Override
	public Integer getTaskClosureCnt(Integer legalEntityCode, String userId, String role, String unitId, Date businessDate, List<String> unitList, Boolean isDeptAsRMD, List<String> deptList) {
		Session session=getSession(); 
		int taskClosureCnt=0;
		// G4 -Jr. Advsr, G7- Advsr, G6- Mgmnt
		logger.info("inside getTaskClosureCnt .."+legalEntityCode +" :: "+ userId+" :: "+ role+" :: "+unitId + " :: "+businessDate +" :: "+unitList);
		logger.info("isDeptAsRMD .. "+isDeptAsRMD +" deptList .. "+deptList);
		try {
			StringBuilder query= new StringBuilder(" "
				+ " SELECT id,audit_id FROM task_details t WHERE legal_entity_code=:legalEntityCode AND task_status in ('Closed','Dropped') AND " 
				+ "           audit_id in ( SELECT sch.audit_id FROM audit_schedule sch, user u WHERE t.legal_entity_code=sch.legal_entity_code "
				+ "									AND sch.legal_entity_code=u.legal_entity_code AND t.legal_entity_code=u.legal_entity_code"  
				+ "									AND sch.unit_id=u.unit_code AND u.user_id=:userId ");
			if("G6".equalsIgnoreCase(role)) {
				query.append("					    AND sch.unit_id IN (:unitList) ");
				if(!isDeptAsRMD) query.append(" 	AND sch.audit_type_code IN (:deptList) ");
			}
			query.append("			   )  AND "
				+ "	           DATE_ADD(Date(checker_timestamp), INTERVAL (SELECT value from general_parameter where "
				+ "                          key_1 ='DAYS_TO_TRIGGER' AND key_2='TASK_CLOSURE' AND legal_entity_code=:legalEntityCode) DAY) >=:businessDate "  
				+ "");
			 Query qry= session.createSQLQuery(query.toString())	
					.setParameter("legalEntityCode", legalEntityCode)
					.setParameter("userId", userId)
					.setParameter("businessDate", businessDate);
			
			if("G6".equalsIgnoreCase(role)) {
				qry.setParameter("unitList",unitList);
				if(!isDeptAsRMD) qry.setParameter("deptList",deptList);
			}
			List lst = qry.list();
			taskClosureCnt=  lst.size();
			logger.info("size of Task Closure  .... "+ taskClosureCnt);
		
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		return taskClosureCnt;
	}	
	
	@SuppressWarnings("unchecked")
	@Override
	public Map<String, Integer> getTaskOpenOverdueCount(Integer legalEntityCode, String userId, String role, String unitId, Date businessDate, List<String> unitList, Boolean isDeptAsRMD, List<String> deptList) {
		Session session=getSession(); 
		Map<String, Integer> taskOpenOverdueCnt =null;
		logger.info("inside getTaskOpenOverdueCount .."+legalEntityCode +" :: "+ userId+" :: "+ role+" :: "+unitId);
		logger.info("isDeptAsRMD .. "+isDeptAsRMD +" deptList .. "+deptList);
		
		StringBuilder queryString=new StringBuilder( " " 
				+ "select g.value from general_parameter g where key_1='DAYS_TO_TRIGGER' AND key_2='TASK_DUE_TO_CLOSE' AND g.legal_entity_code=:legalEntityCode " 
				); 
		//(select g.value from GeneralParameter g where g.key1='AUDITOR_VISIBILITY' AND g.key2='DAYS' AND g.legalEntityCode=:legalEntityCode) 
		
		Query query=session.createSQLQuery(queryString.toString())
				.setParameter("legalEntityCode", legalEntityCode);
		
		Object objTsk = query.uniqueResult();
		int openTskDays = 0;
		try{
			logger.info(" objTsk .."+objTsk);
			if(objTsk != null  ) {
				openTskDays  =Integer.parseInt(objTsk.toString());		
			}
		}catch(Exception e) {
			System.out.println("Exception occured ....");
		}logger.info("openTskDays .. "+openTskDays);
		
		try {
			StringBuilder str= new StringBuilder(" "
			 	+ "SELECT CASE WHEN :businessDate BETWEEN DATE_SUB(Date(due_date), INTERVAL :openTskDays DAY) AND due_date THEN 'Open' " 
				+ " 		   WHEN due_date < :businessDate THEN 'OverDue'  " 
			 	+ "			   ELSE 'NA' "
				+ "       END as sts, count(*) " 
				+ " FROM task_details t, audit_schedule sch, user u "
				+ "	WHERE t.legal_entity_code=:legalEntityCode  AND t.legal_entity_code=sch.legal_entity_code AND t.legal_entity_code=u.legal_entity_code" 
				+ " 		AND sch.legal_entity_code=u.legal_entity_code " 
				+ "         AND sch.unit_id =u.unit_code  AND t.audit_id=sch.audit_id  AND task_status ='Open' AND user_id= :userId " 
				+ " 		AND sch.unit_id IN (:unitList)"); 
			if(!isDeptAsRMD) str.append(" 	AND sch.audit_type_code IN (:deptList) ");
			str.append("   GROUP BY CASE WHEN :businessDate BETWEEN DATE_SUB(Date(due_date), INTERVAL :openTskDays DAY) AND due_date THEN 'Open' " 
				+ " 					 	 WHEN due_date < :businessDate THEN 'OverDue'  " 
				+ "							 ELSE 'NA' "
				+ "                 END	 " );
				
				Query qry= session.createSQLQuery(str.toString())	
						.setParameter("legalEntityCode", legalEntityCode)
						.setParameter("userId", userId).setParameter("unitList",unitList)
						.setParameter("businessDate", businessDate).setParameter("openTskDays", openTskDays);
				
				if(!isDeptAsRMD) {
					qry.setParameter("deptList",deptList);
				}
			 try{
				 List lst = qry.list();
				 
				 taskOpenOverdueCnt = new HashMap<>();
				 logger.info("obj .. "+lst);
				 for(Object obj: lst)
					{
					 if(((Object[])obj)[0] != null && "Open".equalsIgnoreCase(((Object[])obj)[0].toString()))
						 taskOpenOverdueCnt.put("OPEN_TASK_COUNT", Integer.parseInt( ((Object[])obj)[1].toString()));
					 else if(((Object[])obj)[0] != null && "OverDue".equalsIgnoreCase(((Object[])obj)[0].toString()))
						 taskOpenOverdueCnt.put("OVERDUE_TASK_COUNT", Integer.parseInt( ((Object[])obj)[1].toString()));
					
						logger.info("taskOpenOverdueCnt .... "+ taskOpenOverdueCnt );
						//taskOpenOverdueCnt.put("OPEN_TASK_COUNT", taskOpenCnt);
						//taskOpenOverdueCnt.put("OVERDUE_TASK_COUNT", taskOverdueCnt);
					}
				}catch(Exception e) {
					e.printStackTrace(System.out);
				}
			 	logger.info("taskOpenOverdueCnt .."+taskOpenOverdueCnt);
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}logger.info("taskOpenOverdueCnt .... "+ taskOpenOverdueCnt);
		return taskOpenOverdueCnt;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<AlertsDto> getRiskAssessedAlerts(Integer legalEntityCode, String userId, String role, String unitId) {
		Session session=getSession(); 
		List<AlertsDto> lst =null;
		logger.info("inside getRiskAssessedAlerts .."+legalEntityCode +" :: "+ userId+" :: "+ role+" :: "+unitId);
		try {
			lst = (List<AlertsDto>)session.createQuery(" FROM AuditSchedule as sch WHERE "
				+ " sch.legalEntityCode =:legalEntityCode AND sch.auditStatus ='AP'  "
				+ " AND sch.auditId IN (SELECT t.auditId from AuditTeam as t where t.legalEntityCode=sch.legalEntityCode AND t.auditId=sch.auditId "
				+ "						AND t.userId =:userId ) ")
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("userId", userId)
				.list();
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		return lst;
	}
	
		
		
	@SuppressWarnings("unchecked")
	@Override
	public List<TaskDetails> getTaskClosureAlerts(Integer legalEntityCode, String userId, String role, String unitId) {
		Session session=getSession(); 
		List<TaskDetails> lst =null;
		logger.info("inside getTaskClosureAlerts .."+legalEntityCode +" :: "+ userId+" :: "+ role+" :: "+unitId);
		try {
			lst = (List<TaskDetails>)session.createQuery(" FROM AuditSchedule as sch WHERE "
				+ " sch.legalEntityCode =:legalEntityCode AND sch.auditStatus ='AP'  "
				+ " AND sch.auditId IN (SELECT t.auditId from AuditTeam as t where t.legalEntityCode=sch.legalEntityCode AND t.auditId=sch.auditId "
				+ "						AND t.userId =:userId ) ")
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("userId", userId)
				.list();
			logger.info(" lst .. "+ lst);
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		return lst;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<TaskDetails> getOpenTaskAlerts(Integer legalEntityCode, String userId, String role, String unitId) {
		Session session=getSession(); 
		List<TaskDetails> lst =null;
		logger.info("inside getOpenTaskAlerts .."+legalEntityCode +" :: "+ userId+" :: "+ role+" :: "+unitId);
		try {
			lst = (List<TaskDetails>)session.createQuery(" FROM AuditSchedule as sch WHERE "
				+ " sch.legalEntityCode =:legalEntityCode AND sch.auditStatus ='AP'  "
				+ " AND sch.auditId IN (SELECT t.auditId from AuditTeam as t where t.legalEntityCode=sch.legalEntityCode AND t.auditId=sch.auditId "
				+ "						AND t.userId =:userId ) ")
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("userId", userId)
				.list();
			logger.info(" lst .. "+ lst);
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		return lst;
	}
	
	@Override
	public DataTableResponse getAuditInitiatedAlerts(Integer legalEntityCode, String search, Integer orderColumn, String orderDirection,
			 Integer page, Integer size, String userId, String role, String unitId, String alertName, Boolean isDeptAsRMD, List<String> deptList ) {
	    Session session = getSession();
	    session.setDefaultReadOnly(true);
		List<AlertsDto> validationIntLst = null;
		
		logger.info("getAuditInitiatedAlerts .. "+legalEntityCode +" :: "+ userId +" :: "+ role +" :: "+ unitId);
		logger.info(" isDeptAsRMD:: "+ isDeptAsRMD +" deptList:: "+deptList);
		DataTableResponse dataTableResponse=new DataTableResponse();
		
		StringBuilder queryString=new StringBuilder(" ");
		
		if("G2".equalsIgnoreCase(role) || "G8".equalsIgnoreCase(role)) {
			queryString.append(" select   audit_id, audit_type_code, total, auth, pendAuth, unAuth   "
					+ " from ( "
					+ " 	select sch.audit_id, sch.audit_type_code, "
					+ " 		(select count(*) from audit_observation o where o.audit_id=sch.audit_id AND o.legal_entity_code=sch.legal_entity_code ) as total, "
					+ " 		(select count(*) from audit_observation o where o.audit_id=sch.audit_id AND o.legal_entity_code=sch.legal_entity_code AND o.submission_status='A') as auth, "
					+ " 		(select count(*) from audit_observation o where o.audit_id=sch.audit_id AND o.legal_entity_code=sch.legal_entity_code AND o.submission_status='PA') as pendAuth, "
					+ " 		(select count(*) from audit_observation o where o.audit_id=sch.audit_id AND o.legal_entity_code=sch.legal_entity_code AND ifnull(o.submission_status,'U') not in ('A','PA')) as unAuth "
					+ "   	from audit_schedule sch, audit_team t where  t.legal_entity_code=sch.legal_entity_code  "
					+ "			AND sch.observation_status='AS' AND sch.respond_status='RI' " 
					+ " 	  	AND t.audit_id= sch.audit_id AND sch.legal_entity_code=:legalEntityCode AND t.user_id=:userId   "
					+ "  ) as a " );
		}else if("G3".equalsIgnoreCase(role) || "G4".equalsIgnoreCase(role) || "G7".equalsIgnoreCase(role)) {
			queryString.append(" select audit_id, audit_type_code, total, auth, pendAuth, unAuth  "
					+ "  from ( " 
					+ " 	select sch.audit_id, sch.audit_type_code, " 
					+ " 		(select count(*) from audit_observation o where o.audit_id=sch.audit_id AND o.legal_entity_code=sch.legal_entity_code ) as total, " 
					+ " 		(select count(*) from audit_observation o where o.audit_id=sch.audit_id AND o.legal_entity_code=sch.legal_entity_code AND o.submission_status='A') as auth, " 
					+ " 		(select count(*) from audit_observation o where o.audit_id=sch.audit_id AND o.legal_entity_code=sch.legal_entity_code AND o.submission_status='PA') as pendAuth, " 
					+ " 		(select count(*) from audit_observation o where o.audit_id=sch.audit_id AND o.legal_entity_code=sch.legal_entity_code AND ifnull(o.submission_status,'U') not in ('A','PA')) as unAuth " 
					+ "     from audit_schedule sch, user u where  u.legal_entity_code=sch.legal_entity_code  " 
					+ "			AND respond_status = 'RI' "); // + advsrCond
			if("G4".equalsIgnoreCase(role) || "G7".equalsIgnoreCase(role) ) {
				queryString.append(" AND review_status is null ");	
				if(!isDeptAsRMD) queryString.append(" AND sch.audit_type_code IN (:deptList) ");
			}
			queryString.append(" AND u.unit_code= sch.unit_id AND sch.legal_entity_code=:legalEntityCode AND u.user_id=:userId   " 
					+ "  ) as a " );
		}	
			
			if(!BankAuditUtil.isEmptyString(search)){
				queryString=queryString.append("where  (  "
					+ " 	audit_id LIKE :search or audit_type_code LIKE :search  "
					+ " 	or total LIKE :search or auth LIKE :search  or pendAuth LIKE :search or unAuth LIKE :search  "
				    + " ) ");
			}
			String[] columns={
					"a.audit_id",
					"a.audit_type_code",
					"a.total", "a.auth", "a.pendAuth", "a.unAuth"};
			
		
		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 a.audit_id, a.audit_type_code 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 ").append(columns[orderColumn]).append(" ").append(orderDirection);
		}
		
		Query query=session.createSQLQuery(queryString.toString())
				.setResultTransformer(new ResultTransformer() {
					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						AlertsDto alertsDto=null;
						if(tuple!=null){
							alertsDto=new AlertsDto();
							if(tuple[0]!=null) alertsDto.setAuditId(tuple[0].toString());
							if(tuple[1]!=null) alertsDto.setRCSAUnnit(tuple[1].toString());
							//if(tuple[2]!=null) alertsDto.setBusinessLine(tuple[2].toString());
							//if(tuple[3]!=null) alertsDto.setProductName(tuple[3].toString());
							alertsDto.setTotal(tuple[2].toString() != null ? tuple[2].toString().toString() : "0");
							alertsDto.setAuthorised(tuple[3].toString() != null ? tuple[3].toString().toString() : "0");
							alertsDto.setPendingAuthorised(tuple[4].toString() != null ? tuple[4].toString().toString() : "0");
							alertsDto.setUnauthorised(tuple[5].toString() != null ? tuple[5].toString().toString() : "0");
						}
						return alertsDto;
					}
					@Override
					public List transformList(List collection) {
						return collection;
					}
				})
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("userId", userId);
		
		if("G4".equalsIgnoreCase(role) || "G7".equalsIgnoreCase(role) && !isDeptAsRMD )
			query.setParameter("deptList",deptList);
		
		if(!BankAuditUtil.isEmptyString(search)){
			query.setParameter("search", "%"+search+"%");
		}
		
		ScrollableResults resultScroll = query.scroll(ScrollMode.SCROLL_INSENSITIVE);
		if(resultScroll.first()&&resultScroll.scroll(page*size)){
			
			validationIntLst= new ArrayList<>();
			int i = 0;
			while (size > i++) {
				validationIntLst.add((AlertsDto) resultScroll.get(0));
			    if (!resultScroll.next())
			        break;
			}
			dataTableResponse.setData(validationIntLst);
			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;
	} 
	
	@Override
	public DataTableResponse getAuditSubmittedAlerts(Integer legalEntityCode, String search, Integer orderColumn, String orderDirection,
			 Integer page, Integer size, String userId, String role, String unitId, String alertName, Boolean isDeptAsRMD, List<String> deptList ) {
	    Session session = getSession();
	    session.setDefaultReadOnly(true);
		List<AlertsDto> validationSbmtLst = null;
		
		logger.info("getAuditSubmittedAlerts .. "+legalEntityCode +" :: "+ userId +" :: "+ role +" :: "+ unitId);
		logger.info(" isDeptAsRMD:: "+ isDeptAsRMD +" deptList:: "+deptList);
		DataTableResponse dataTableResponse=new DataTableResponse();
		
		StringBuilder queryString=new StringBuilder(" ");
		
		queryString.append(" select audit_id, audit_type_code, responded_date, schedule_end_date, total, auth, pendAuth, unAuth, audited  "
				+ "  from ( " 
				+ " 	select sch.audit_id, sch.audit_type_code, sch.responded_date, sch.schedule_end_date, " 
				+ " 		(select count(*) from audit_observation o where o.audit_id=sch.audit_id AND o.legal_entity_code=sch.legal_entity_code ) as total, " 
				+ " 		(select count(*) from audit_observation o where o.audit_id=sch.audit_id AND o.legal_entity_code=sch.legal_entity_code AND o.submission_status='A') as auth, " 
				+ " 		(select count(*) from audit_observation o where o.audit_id=sch.audit_id AND o.legal_entity_code=sch.legal_entity_code AND o.submission_status='PA') as pendAuth, " 
				+ " 		(select count(*) from audit_observation o where o.audit_id=sch.audit_id AND o.legal_entity_code=sch.legal_entity_code AND ifnull(o.submission_status,'U') not in ('A','PA')) as unAuth, " 
				+ " 		(select count(*) from audit_observation o where o.audit_id=sch.audit_id AND o.legal_entity_code=sch.legal_entity_code AND ifnull(o.response_status,'U') = 'A') as audited " 
				+ "     from audit_schedule sch, user u where  u.legal_entity_code=sch.legal_entity_code  " 
				+ "			AND respond_status = 'RS' AND review_status IS NULL " 
				+ " 		AND u.unit_code= sch.unit_id AND sch.legal_entity_code=:legalEntityCode AND u.user_id=:userId   " );
		if(!isDeptAsRMD) queryString.append(" AND sch.audit_type_code IN (:deptList) ");
		queryString.append("	  ) as a " );
		
		if(!BankAuditUtil.isEmptyString(search)){
			queryString=queryString.append("where  (  "
				+ " 	audit_id LIKE :search or audit_type_code LIKE :search  or responded_date LIKE :search or schedule_end_date LIKE :search "
				+ " 	or total LIKE :search or auth LIKE :search  or pendAuth LIKE :search or unAuth LIKE :search or audited LIKE :search  "
			    + " ) ");
		}
		String[] columns={
				"a.audit_id",
				"a.audit_type_code", "a.responded_date" , "a.schedule_end_date",
				"a.total", "a.auth", "a.pendAuth", "a.unAuth", "a.audited"};
		
		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 a.audit_id, a.audit_type_code 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 ").append(columns[orderColumn]).append(" ").append(orderDirection);
		}
		
		Query query=session.createSQLQuery(queryString.toString())
				.setResultTransformer(new ResultTransformer() {
					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						AlertsDto alertsDto=null;
						if(tuple!=null){
							alertsDto=new AlertsDto();
							if(tuple[0]!=null) alertsDto.setAuditId(tuple[0].toString());
							if(tuple[1]!=null) alertsDto.setRCSAUnnit(tuple[1].toString());
							//if(tuple[2]!=null) alertsDto.setBusinessLine(tuple[2].toString());
							//if(tuple[3]!=null) alertsDto.setProductName(tuple[3].toString());
							if(tuple[2]!=null) alertsDto.setAuditedDate(tuple[2].toString());
							if(tuple[3]!=null) alertsDto.setScheduleEndDate(tuple[3].toString());
							alertsDto.setTotal(tuple[4].toString() != null ? tuple[4].toString().toString() : "0");
							alertsDto.setAuthorised(tuple[5].toString() != null ? tuple[5].toString().toString() : "0");
							alertsDto.setPendingAuthorised(tuple[6].toString() != null ? tuple[6].toString().toString() : "0");
							alertsDto.setUnauthorised(tuple[7].toString() != null ? tuple[7].toString().toString() : "0");
							alertsDto.setAudited(tuple[8].toString() != null ? tuple[8].toString().toString() : "0");
						}
						return alertsDto;
					}
					@Override
					public List transformList(List collection) {
						return collection;
					}
				})
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("userId", userId);
		
		if(!isDeptAsRMD) query.setParameter("deptList",deptList);
		
		if(!BankAuditUtil.isEmptyString(search)){
			query.setParameter("search", "%"+search+"%");
		}
		
		ScrollableResults resultScroll = query.scroll(ScrollMode.SCROLL_INSENSITIVE);
		if(resultScroll.first()&&resultScroll.scroll(page*size)){
			
			validationSbmtLst= new ArrayList<>();
			int i = 0;
			while (size > i++) {
				validationSbmtLst.add((AlertsDto) resultScroll.get(0));
			    if (!resultScroll.next())
			        break;
			}
			dataTableResponse.setData(validationSbmtLst);
			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;
	} 	
	
	@Override
	public DataTableResponse getActionPlanInitiatedAlerts(Integer legalEntityCode, String search, Integer orderColumn, String orderDirection,
			 Integer page, Integer size, String userId, String role, String unitId, String alertName, List<String> unitList, Boolean isDeptAsRMD, List<String> deptList) {
	    Session session = getSession();
	    session.setDefaultReadOnly(true);
		List<AlertsDto> actionPlanInitLst = null;
		
		logger.info("getActionPlanInitiatedAlerts .. "+legalEntityCode +" :: "+ userId +" :: "+ role +" :: "+ unitId);
		logger.info(" isDeptAsRMD:: "+ isDeptAsRMD +" deptList:: "+deptList);
		DataTableResponse dataTableResponse=new DataTableResponse();
		
		StringBuilder queryString=new StringBuilder(" ");
		
		if("G4".equalsIgnoreCase(role) || "G7".equalsIgnoreCase(role)) {
			queryString.append(" select audit_id, audit_type_code, responded_date, schedule_end_date, total, auth, pendAuth, unAuth, audited  "
				+ "  from ( " 
				+ " 	select sch.audit_id, sch.audit_type_code, sch.responded_date, sch.schedule_end_date," 
				+ " 		(select count(*) from audit_observation o where o.audit_id=sch.audit_id AND o.legal_entity_code=sch.legal_entity_code ) as total, " 
				+ " 		(select count(*) from audit_observation o where o.audit_id=sch.audit_id AND o.legal_entity_code=sch.legal_entity_code AND o.submission_status='A') as auth, " 
				+ " 		(select count(*) from audit_observation o where o.audit_id=sch.audit_id AND o.legal_entity_code=sch.legal_entity_code AND o.submission_status='PA') as pendAuth, " 
				+ " 		(select count(*) from audit_observation o where o.audit_id=sch.audit_id AND o.legal_entity_code=sch.legal_entity_code AND ifnull(o.submission_status,'U') not in ('A','PA')) as unAuth, " 
				+ " 		(select count(*) from audit_observation o where o.audit_id=sch.audit_id AND o.legal_entity_code=sch.legal_entity_code AND ifnull(o.response_status,'U') = 'A') as audited " 
				+ "     from audit_schedule sch, user u where  u.legal_entity_code=sch.legal_entity_code  " 
				+ "			AND review_status ='WI' " 
				+ " 		AND u.unit_code= sch.unit_id AND sch.legal_entity_code=:legalEntityCode AND u.user_id=:userId   " );
			if(!isDeptAsRMD) queryString.append(" AND sch.audit_type_code IN (:deptList) ");
			queryString.append(" ) as a " );
		}
		else if("G6".equalsIgnoreCase(role) ) {
			queryString.append(" select audit_id, audit_type_code, responded_date, schedule_end_date, total, auth, pendAuth, unAuth, audited  "
				+ "  from ( " 
				+ " 	select sch.audit_id, sch.audit_type_code, sch.responded_date, sch.schedule_end_date, " 
				+ " 		(select count(*) from audit_observation o where o.audit_id=sch.audit_id AND o.legal_entity_code=sch.legal_entity_code ) as total, " 
				+ " 		(select count(*) from audit_observation o where o.audit_id=sch.audit_id AND o.legal_entity_code=sch.legal_entity_code AND o.submission_status='A') as auth, " 
				+ " 		(select count(*) from audit_observation o where o.audit_id=sch.audit_id AND o.legal_entity_code=sch.legal_entity_code AND o.submission_status='PA') as pendAuth, " 
				+ " 		(select count(*) from audit_observation o where o.audit_id=sch.audit_id AND o.legal_entity_code=sch.legal_entity_code AND ifnull(o.submission_status,'U') not in ('A','PA')) as unAuth, " 
				+ " 		(select count(*) from audit_observation o where o.audit_id=sch.audit_id AND o.legal_entity_code=sch.legal_entity_code AND ifnull(o.response_status,'U') = 'A') as audited " 
				+ "     from audit_schedule sch  " 
				+ "			AND review_status ='WI' "
				+ " 		AND sch.unit_code IN (:unitList) AND sch.legal_entity_code=:legalEntityCode " );
			if(!isDeptAsRMD) queryString.append(" AND sch.audit_type_code IN (:deptList) ");
			queryString.append("  ) as a " );
		}	
		
		
		if(!BankAuditUtil.isEmptyString(search)){
			queryString=queryString.append("where  (  "
				+ " 	audit_id LIKE :search or audit_type_code LIKE :search  or responded_date LIKE :search or schedule_end_date LIKE :search "
				+ " 	or total LIKE :search or auth LIKE :search  or pendAuth LIKE :search or unAuth LIKE :search or audited LIKE :search  "
			    + " ) ");
		}
		String[] columns={
				"a.audit_id",
				"a.audit_type_code", "a.responded_date" , "a.schedule_end_date",
				"a.total", "a.auth", "a.pendAuth", "a.unAuth", "a.audited"};
		
		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 a.audit_id, a.audit_type_code 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 ").append(columns[orderColumn]).append(" ").append(orderDirection);
		}
		
		Query query=session.createSQLQuery(queryString.toString())
				.setResultTransformer(new ResultTransformer() {
					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						AlertsDto alertsDto=null;
						if(tuple!=null){
							alertsDto=new AlertsDto();
							if(tuple[0]!=null) alertsDto.setAuditId(tuple[0].toString());
							if(tuple[1]!=null) alertsDto.setRCSAUnnit(tuple[1].toString());
							//if(tuple[2]!=null) alertsDto.setBusinessLine(tuple[2].toString());
							//if(tuple[3]!=null) alertsDto.setProductName(tuple[3].toString());
							if(tuple[2]!=null) alertsDto.setAuditedDate(tuple[2].toString());
							if(tuple[3]!=null) alertsDto.setScheduleEndDate(tuple[3].toString());
							alertsDto.setTotal(tuple[4].toString() != null ? tuple[4].toString().toString() : "0");
							alertsDto.setAuthorised(tuple[5].toString() != null ? tuple[5].toString().toString() : "0");
							alertsDto.setPendingAuthorised(tuple[6].toString() != null ? tuple[6].toString().toString() : "0");
							alertsDto.setUnauthorised(tuple[7].toString() != null ? tuple[7].toString().toString() : "0");
							alertsDto.setAudited(tuple[8].toString() != null ? tuple[8].toString().toString() : "0");
						}
						return alertsDto;
					}
					@Override
					public List transformList(List collection) {
						return collection;
					}
				})
				.setParameter("legalEntityCode", legalEntityCode)
				;
		
		if("G4".equalsIgnoreCase(role) || "G7".equalsIgnoreCase(role)){
			query.setParameter("userId", userId);
		}else if("G6".equalsIgnoreCase(role)){
			query.setParameter("unitList", unitList);
		}
		if(!isDeptAsRMD) query.setParameter("deptList", deptList);
			
		if(!BankAuditUtil.isEmptyString(search)){
			query.setParameter("search", "%"+search+"%");
		}
		
		ScrollableResults resultScroll = query.scroll(ScrollMode.SCROLL_INSENSITIVE);
		if(resultScroll.first()&&resultScroll.scroll(page*size)){
			
			actionPlanInitLst= new ArrayList<>();
			int i = 0;
			while (size > i++) {
				actionPlanInitLst.add((AlertsDto) resultScroll.get(0));
			    if (!resultScroll.next())
			        break;
			}
			dataTableResponse.setData(actionPlanInitLst);
			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;
	} 	
	
	@Override
	public DataTableResponse getRCSAClosureAlerts(Integer legalEntityCode, String search, Integer orderColumn, String orderDirection,
			 Integer page, Integer size, String userId, String role, String unitId, String alertName , Date businessDate, List<String> unitList, Boolean isDeptAsRMD, List<String> deptList) {
	    Session session = getSession();
	    session.setDefaultReadOnly(true);
		List<AlertsDto> rcsaClsrLst = null;
		// for G1-Ops, G6-Mgmnt, G4-Jr. Advsr, G7-Advsr and G3-Adtr
		logger.info("getRCSAClosureAlerts .. "+legalEntityCode +" :: "+ userId +" :: "+ role +" :: "+ unitId+" :: "+unitList);
		logger.info(" isDeptAsRMD:: "+ isDeptAsRMD +" deptList:: "+deptList);
		DataTableResponse dataTableResponse=new DataTableResponse();
		
		StringBuilder queryString=new StringBuilder(" ");
		
		queryString.append(" select audit_id, audit_type_code, schedule_start_date,schedule_end_date,actual_start_date,actual_end_date, total  "
				+ "  from ( " 
				+ " 	select sch.audit_id, sch.audit_type_code, sch.schedule_start_date, sch.schedule_end_date, sch.actual_start_date, sch.actual_end_date," 
				+ " 		(select count(*) from audit_observation o where o.audit_id=sch.audit_id AND o.legal_entity_code=sch.legal_entity_code ) as total " 
				//+ " 		,(select count(*) from audit_observation o where o.audit_id=sch.audit_id AND o.legal_entity_code=sch.legal_entity_code AND o.submission_status='A') as auth, " 
				+ "     from audit_schedule sch, user u where  u.legal_entity_code=sch.legal_entity_code  " 
				+ "			AND review_status IN ('WS','AC') " 
				+ " 		AND sch.unit_id IN (:unitList) AND sch.legal_entity_code=:legalEntityCode AND u.user_id= :userId  " 
				+ "			AND DATE_ADD(sch.reviewed_date, INTERVAL (SELECT value from general_parameter where key_1 ='DAYS_TO_TRIGGER'  "
				+ "         			AND key_2='RCSA_CLOSURE' AND legal_entity_code=:legalEntityCode) DAY) >= :businessDate " );
		
		if(!isDeptAsRMD && !"G3".equalsIgnoreCase(role)) // Auditor can see across the Dept.
			queryString.append(" AND sch.audit_type_code IN (:deptList) ");
		
		queryString.append("  ) as a " );
				
		if(!BankAuditUtil.isEmptyString(search)){
			queryString=queryString.append("where  (  "
				+ " 	audit_id LIKE :search or audit_type_code LIKE :search  or schedule_start_date LIKE :search or schedule_end_date LIKE :search "
				+ " 	actual_start_date LIKE :search or actual_end_date LIKE :search  "
				+ " 	or total LIKE :search "
			    + " ) ");
		}
		String[] columns={
				"a.audit_id",
				"a.audit_type_code", "a.total",
				 "a.schedule_start_date" , "a.schedule_end_date", "a.actual_start_date" , "a.actual_end_date" };
		
		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 a.audit_id, a.audit_type_code 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 ").append(columns[orderColumn]).append(" ").append(orderDirection);
		}
		
		Query query=session.createSQLQuery(queryString.toString())
				.setResultTransformer(new ResultTransformer() {
					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						AlertsDto alertsDto=null;
						if(tuple!=null){
							alertsDto=new AlertsDto();
							if(tuple[0]!=null) alertsDto.setAuditId(tuple[0].toString());
							if(tuple[1]!=null) alertsDto.setRCSAUnnit(tuple[1].toString());
							//if(tuple[2]!=null) alertsDto.setBusinessLine(tuple[2].toString());
							//if(tuple[3]!=null) alertsDto.setProductName(tuple[3].toString());
							if(tuple[2]!=null) alertsDto.setScheduleStartDate(tuple[2].toString());
							if(tuple[3]!=null) alertsDto.setScheduleEndDate(tuple[3].toString());
							if(tuple[4]!=null) alertsDto.setActualStartDate(tuple[4].toString());
							if(tuple[5]!=null) alertsDto.setActualEndDate(tuple[5].toString());
							if(tuple[6]!=null) alertsDto.setTotal(tuple[6].toString() != null ? tuple[6].toString().toString() : "0");
							if(tuple[6]!=null) alertsDto.setCountOfRecords(tuple[6].toString() != null ? tuple[6].toString().toString() : "0");
						}
						return alertsDto;
					}
					@Override
					public List transformList(List collection) {
						return collection;
					}
				})
				.setParameter("legalEntityCode", legalEntityCode).setParameter("userId", userId)
				.setParameter("unitList", unitList).setParameter("businessDate", businessDate);
		
		if(!isDeptAsRMD && !"G3".equalsIgnoreCase(role)) query.setParameter("deptList",deptList);
		
		if(!BankAuditUtil.isEmptyString(search)){
			query.setParameter("search", "%"+search+"%");
		}
		
		ScrollableResults resultScroll = query.scroll(ScrollMode.SCROLL_INSENSITIVE);
		if(resultScroll.first()&&resultScroll.scroll(page*size)){
			
			rcsaClsrLst= new ArrayList<>();
			int i = 0;
			while (size > i++) {
				rcsaClsrLst.add((AlertsDto) resultScroll.get(0));
			    if (!resultScroll.next())
			        break;
			}
			dataTableResponse.setData(rcsaClsrLst);
			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;
	} 	
	
	@Override
	public DataTableResponse getTaskOpenCloseOverdueAlerts(Integer legalEntityCode, String search, Integer orderColumn, String orderDirection,
			 Integer page, Integer size, String userId, String role, String unitId, String alertName, Date businessDate, List<String> unitList, Boolean isDeptAsRMD, List<String> deptList ) {
	    Session session = getSession();
	    session.setDefaultReadOnly(true);
		List<TaskDetails> taskDetailsLst = null;
		
		logger.info("getTaskOpenCloseOverdueAlerts .. "+legalEntityCode +" :: "+ userId +" :: "+ role +" :: "+ unitId+" :: "+unitList);
		logger.info(" isDeptAsRMD:: "+ isDeptAsRMD +" deptList:: "+deptList);
		
		DataTableResponse dataTableResponse=new DataTableResponse();
		
		StringBuilder queryString=new StringBuilder(" SELECT t.legal_entity_code, "
					+ "		(select concat(atd.audit_type_code , '-' , atd.audit_type_desc)"
				    + " 		from maint_audit_type_desc atd where "
				    + " 		atd.audit_type_code=m.audit_type_code "
				    + " 		and atd.legal_entity_code=m.legal_entity_code ) as audit_type_code , "
					+ " 	(select concat(ag.audit_group_code , '-' , ag.audit_group_name) " 
					+ " 		 from maint_audit_group ag where  " 
					+ " 		 ag.audit_group_code=m.audit_group_code  " 
					+ " 		 and ag.audit_type_code=m.audit_type_code  " 
					+ " 		 and ag.legal_entity_code=m.legal_entity_code ) as audit_group_code ,  " 
					+ " 	(select concat(asg.audit_sub_group_code , '-' , asg.audit_sub_group_name) " 
					+ " 		 from maint_audit_subgroup asg where  " 
					+ " 		 asg.audit_sub_group_code=m.audit_sub_group_code  " 
					+ " 		 and asg.audit_group_code=m.audit_group_code  " 
					+ " 		 and asg.audit_type_code=m.audit_type_code  " 
					+ " 		 and asg.legal_entity_code=m.legal_entity_code ) as audit_sub_group_code ,  " 
					+ " 	(select concat(aa.activity_id , '-' , aa.activity_name) " 
					+ " 		 from maint_audit_activity aa where  " 
					+ " 		 aa.activity_id=m.activity_id  " 
					+ " 		 and aa.legal_entity_code=m.legal_entity_code ) as activity_id ,  " 
					+ " 	(select concat(ap.process_id , '-' , ap.process_name) " 
					+ " 		 from maint_audit_process ap where  " 
					+ " 		 ap.process_id=m.process_id  " 
					+ " 		 and ap.legal_entity_code=m.legal_entity_code ) as process_id ,  " 
					+ " 				 " 
					+ " 	(select concat(af.finding_id , '-' , af.finding_name , '-' , af.f_criticality)    " 
					+ " 		 from maint_audit_finding af where  " 
					+ " 		 af.finding_id=m.finding_id  " 
					+ " 		 and af.legal_entity_code=m.legal_entity_code ) as finding_id , 	 " 
					+ " 	t.audit_id,t.id,t.action_plan,t.due_date,t.resp_implementation,t.resp_monitoring,t.task_status,m.mapping_id  " 
					+ " 	from task_details t, audit_observation o, activity_process_finding_mapping m  " 
					+ " 	where t.legal_entity_code=o.legal_entity_code and t.observation_id=o.observation_id " 
					+ " 		and t.legal_entity_code=m.legal_entity_code " 
					+ " 	    and o.legal_entity_code=m.legal_entity_code and o.audit_type_code=m.audit_type_code and o.mapping_id=m.mapping_id " 
					+ " 	    and t.legal_entity_code=:legalEntityCode " 
					+ "         and t.audit_id in (select s.audit_id from audit_schedule s, user u, maint_entity_audit_subgroup_mapping sm  " 
					+ "						where s.legal_entity_code=sm.legal_entity_code and u.legal_entity_code=sm.legal_entity_code " 
					+ "								AND s.legal_entity_code=u.legal_entity_code AND u.user_id=sm.id AND sm.mapping_type='U' "  
					+ "                             AND s.audit_type_code=sm.audit_type_code AND s.unit_id=u.unit_code AND s.audit_id=t.audit_id "  
					+ "                             AND u.user_id=:userId "				
					+ "								AND s.unit_id IN (:unitList)"
				);  // and due_date > '2020-01-20' and task_status='Open' 
		
		if(!isDeptAsRMD) {
			queryString.append(" 				   AND s.audit_type_code IN (:deptList) ");
		}
		queryString.append(" 					  )");
		
		if("OVERDUE".equalsIgnoreCase(alertName))
			queryString.append(" AND task_status='Open' AND due_date < :businessDate ");
		
		else if("OPEN".equalsIgnoreCase(alertName))
			queryString.append(" AND task_status in ('Open')  "
					+ " AND :businessDate BETWEEN DATE_SUB(Date(due_date), INTERVAL (SELECT value FROM general_parameter WHERE key_1 ='DAYS_TO_TRIGGER' "
					+ "					  AND key_2='TASK_DUE_TO_CLOSE' AND legal_entity_code=:legalEntityCode) DAY) AND due_date");
		else if("CLOSURE".equalsIgnoreCase(alertName))
			queryString.append(" AND task_status IN ('Closed','Dropped')  "
					+ " AND :businessDate BETWEEN t.checker_timestamp AND DATE_ADD(Date(t.checker_timestamp), INTERVAL (SELECT value FROM general_parameter  "
					+ "					              WHERE key_1 ='DAYS_TO_TRIGGER' AND key_2='TASK_CLOSURE' AND legal_entity_code=:legalEntityCode) DAY) ");
		
		if(!BankAuditUtil.isEmptyString(search)){
			queryString=queryString.append(" and ( m.audit_type_code IN ("
					+ " select atd.audit_type_code "
				    + " from maint_audit_type_desc atd where "
				    + " atd.audit_type_desc LIKE :search "
				    + " and atd.legal_entity_code=m.legal_entity_code  "
					+ " ) "
					+ " or  m.audit_type_code LIKE :search "
					+ " or  m.audit_group_code IN ( "
					+ "select ag.audit_group_code "
				    + " from maint_audit_group ag where "
				    + " ag.audit_group_name LIKE :search "
				    + " and ag.legal_entity_code=m.legal_entity_code "
					+ " ) "
					+ " or  m.audit_group_code LIKE :search "
					+ " or  m.audit_sub_group_code IN ( "
					+ " select asg.audit_sub_group_code"
					+ " from maint_audit_subgroup asg where "
					+ " asg.audit_sub_group_name LIKE :search "
					+ " and asg.legal_entity_code=m.legal_entity_code "
					+ " ) "
					+ " or  m.audit_sub_group_code LIKE :search "
					+ " or  m.activity_id IN ( "
					+ " select aa.activity_id "
					+ " from maint_audit_activity aa where "
					+ " aa.activity_name LIKE :search "
					+ " and aa.legal_entity_code=m.legal_entity_code  "
					+ " )"
					+ " or  m.activity_code LIKE :search "
					+ " or  m.process_id IN ( "
					+ " select ap.process_id"
					+ " from maint_audit_process ap where "
					+ " ap.process_name LIKE :search "
					+ " and ap.legal_entity_code=m.legal_entity_code "
					+ " )"
					+ " or  m.process_code LIKE :search "
					+ " or  m.maker like :search"
                    + " or  m.maker in ( "
					+ " select u.user_id "
					+ " from user u where "
					+ " (concat(u.first_name , ' ' , u.last_name) like :search ) "
					+ " and u.legal_entity_code=m.legal_entity_code ) "
					+ " or  m.status IN (SELECT value FROM general_parameter  WHERE key_1='STATUS' and  description LIKE :search ) "
					+ " or  m.finding_id IN ( "
					+ " select af.finding_id "
					+ " from maint_audit_finding af where "
					+ " af.finding_name LIKE :search "
					+ " and af.legal_entity_code=m.legal_entity_code "
					+ " ) "
					+ " or  m.finding_code LIKE :search "
					+ " or  m.entity_status IN (SELECT value FROM general_parameter  WHERE key_1='ENTITY_STATUS' and key_2='ENTITY_STATUS' and description LIKE :search ) "
					+ " or  t.audit_id LIKE :search or t.id LIKE :search or t.action_plan like :search "
					+ " or  t.due_date LIKE :search or t.resp_implementation LIKE :search or t.resp_monitoring LIKE :search or t.task_status LIKE :search"
					+ " ) ");
		}
	
		String[] columns={
				"audit_type_code",
				"t.audit_id","t.id",
				//"m.audit_group_code",
				"audit_sub_group_code",
				/*"apfm.activity_code",*/
				"activity_code",
				"process_code",
				"finding_code",
				"t.action_plan",
				"t.due_date","t.resp_implementation","t.resp_monitoring"};
		
		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 t.id 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 ").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.setLegalEntityCode(Integer.parseInt(tuple[0].toString()));
							if(tuple[1]!=null)taskDetails.setAuditTypeCode(tuple[1].toString());
							if(tuple[2]!=null)taskDetails.setAuditGroupCode(tuple[2].toString());
							//if(tuple[3]!=null)taskDetails.setAuditSubGroupCode(tuple[3].toString());
							if(tuple[3]!=null)taskDetails.setAuditSubGroupCode(tuple[2].toString()+"->"+tuple[3].toString()); // To show the AuditGrpCode along with AuditSubGrpCode
							if(tuple[4]!=null)taskDetails.setActivityCode(tuple[4].toString());
							if(tuple[5]!=null)taskDetails.setProcessCode(tuple[5].toString());
							if(tuple[6]!=null)taskDetails.setFindingCode(tuple[6].toString());
							if(tuple[7]!=null)taskDetails.setAuditId(tuple[7].toString());
							if(tuple[8]!=null)taskDetails.setId(Integer.parseInt(tuple[8].toString()));
							if(tuple[9]!=null)taskDetails.setActionPlan(tuple[9].toString());
							if(tuple[10]!=null)taskDetails.setDueDate((Date)tuple[10]);
							if(tuple[11]!=null)taskDetails.setRespImplementation(tuple[11].toString());
							if(tuple[12]!=null)taskDetails.setRespMonitoring(tuple[12].toString());
							if(tuple[13]!=null)taskDetails.setStatus(tuple[13].toString());
							if(tuple[14]!=null)taskDetails.setMappingId(tuple[14].toString());
							
						}
						return taskDetails;
					}
					@Override
					public List transformList(List collection) {
						return collection;
					}
				})
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("businessDate", businessDate).setParameter("unitList",unitList)
				.setParameter("userId", userId);
		
		if(!isDeptAsRMD) query.setParameter("deptList",deptList);
		
		if(!BankAuditUtil.isEmptyString(search)){
			query.setParameter("search", "%"+search+"%");
		}
		
		ScrollableResults resultScroll = query.scroll(ScrollMode.SCROLL_INSENSITIVE);
		if(resultScroll.first()&&resultScroll.scroll(page*size)){
			
			taskDetailsLst= new ArrayList<>();
			int i = 0;
			while (size > i++) {
				taskDetailsLst.add((TaskDetails) resultScroll.get(0));
			    if (!resultScroll.next())
			        break;
			}
			dataTableResponse.setData(taskDetailsLst);
			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;
	} 
	
}
