package com.bankaudit.rest.process.dao;

import java.time.LocalDate;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.transform.ResultTransformer;
import org.hibernate.type.StandardBasicTypes;
import org.jboss.logging.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.bankaudit.constants.BankAuditConstant;
import com.bankaudit.dao.AbstractDao;
import com.bankaudit.dto.DataTableResponse;
import com.bankaudit.model.User;
import com.bankaudit.process.model.AuditObservation;
import com.bankaudit.process.model.AuditSchedule;
import com.bankaudit.process.model.AuditScheduleStagesStatusHst;
import com.bankaudit.rest.process.service.AuditScheduleStagesStatusHstService;
import com.bankaudit.service.BusinessDateService;
import com.bankaudit.service.MaintEntityService;
import com.bankaudit.util.BankAuditUtil;

/*MaintEntityDataEffect*/
@Repository("auditReviewDao")
public class AuditReviewDaoImpl extends AbstractDao implements AuditReviewDao {

	@Autowired
	BusinessDateService businessDateService;
	
	@Autowired
	MaintEntityService maintEntityService;
	 	
	static final Logger logger = Logger.getLogger(AuditReviewDaoImpl.class);
	
	@Override
	public DataTableResponse getMyReviewList(int legalEntityCode, String userId, String search, Integer orderColumn, String orderDirection, Integer page, Integer size,
			LocalDate financialYear, String auditTypeCode, List<LocalDate>  financialYearStrtEndList) { // Added to filter the records based on Financial Year and AuditType
		Session session = getSession();
		session.setDefaultReadOnly(true);

		logger.info(" getMyReviewList :: "+ legalEntityCode +" :: "+ userId);
		/********************** 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()==null?"":usr.getFirstName()) 
					+ (usr.getMiddleName()==null?"":(" "+usr.getMiddleName())) 
					+ (usr.getLastName()==null?"":(" "+usr.getLastName())) );		
		}
		logger.info("User details val is .... "+ userDtls);
		/******************** User Retrieval logic, Ends here ********************************/
		
		List<String> maintEntities = null;
		maintEntities=maintEntityService.getSubBranchesGyUserIdOrUnitId(legalEntityCode,"user",userId);
		logger.info("maintEntities .... "+maintEntities);
		
		/****************************** ****************************************/
		
		String pattern = "yyyy-mm-dd hh:mm:ss";
       // SimpleDateFormat simpleDateFormat = new SimpleDateFormat(pattern);
       				
		DataTableResponse dataTableResponse=new DataTableResponse();
		try {
		//StringBuilder queryString=new StringBuilder(" from AuditSchedule where legalEntityCode =:legalEntityCode ");
		StringBuilder queryString=new StringBuilder("select distinct sch as auditSchedule, " 
				+ "(select concat(auditTypeCode , '-' , auditTypeDesc) from MaintAuditTypeDesc at where at.auditTypeCode=sch.auditTypeCode and at.legalEntityCode= sch.legalEntityCode ) as auditTypeCode,"
				
				//+ " (Select concat(unitCode , '-' , unitName) from  MaintEntity e where e.unitCode =sch.unitId and e.legalEntityCode =sch.legalEntityCode ) as unitId,  "
				+ " (Select unitName from  MaintEntity e where e.unitCode =sch.unitId and e.legalEntityCode =sch.legalEntityCode ) as unitName,  "
				//+ " (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 concat(u.firstName , ' ' , u.lastName) from  User u where u.userId =sch.unitHead and u.legalEntityCode =sch.legalEntityCode ) as unitHead, "
				+ " (Select t.userId from  AuditTeam t where t.auditId=sch.auditId and t.legalEntityCode =sch.legalEntityCode and t.userType='L') as teamLead, "
				
				//+ " (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, "
				//+ " (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 tm.auditTypeCode,GROUP_CONCAT(tm.name) from (Select t.auditTypeCode,t.planId, t.auditId, concat(u.firstName , ' ' , u.lastName) as name from  User u,AuditTeam t where u.userId =t.userId and t.auditId=sch.auditId and t.legalEntityCode =u.legalEntityCode and t.legalEntityCode =sch.legalEntityCode and t.userType='T') tm where  GROUP BY tm.auditTypeCode  ) as teamMember "
				//+ " (Select CASE WHEN COUNT(*) > 0 THEN 'Y' ELSE 'N' END as isUnitHeadUser from MaintEntity e where e.unitCode =sch.unitId and e.legalEntityCode =sch.legalEntityCode and e.unitHeadUser =:userId ) as isUnitHeadUser, "
				+ " (Select CASE WHEN COUNT(*) > 0 THEN 'Y' ELSE 'N' END as isUnitHeadUser from UserRoleMapping urm where urm.userRoleId ='LRVWR' and urm.legalEntityCode =sch.legalEntityCode and urm.userId =:userId ) as isUnitHeadUser, "  // To check the user is a Lead Reviewer or not.
				//+ " (Select e.unitHeadUser from MaintEntity e where e.unitCode =sch.unitId and e.legalEntityCode =sch.legalEntityCode  ) as userHead, "
				+ " (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, "
				//+"   sch.auditStatus, "
				+ " (Select concat(sch.reviewStatus , '-' , g.description) from  GeneralParameter g where g.legalEntityCode =sch.legalEntityCode and g.key1='STATUS' and g.key2='AUDIT_STATUS' and g.value=sch.reviewStatus) as reviewStatus "
				+ ", (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 "
				+ ", (Select concat(sch.reviewerId, '-',u.firstName , ' ' , u.lastName) from  User u where u.userId =sch.reviewerId and u.legalEntityCode =sch.legalEntityCode ) as reviewerId "
				+ ", (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 "
				//+ ", CONCAT(sch.assessedRiskPrcntg ,'-', sch.assessedRating) as assessedRating  "
				//+ ", CONCAT(sch.effectiveRiskPrcntg ,'-', sch.effectiveRating) as effectiveRating "
				//+ "  sch.auditStatus "
				+ ", (select group_concat(t.userId) from AuditTeam t where t.legalEntityCode=sch.legalEntityCode AND t.auditId=sch.auditId AND t.userType='M' ) AS teamMember "
				
				+ ", (Select concat(u.userId , '-' , ifnull(u.firstName,''), ' ',ifnull(u.middleName,''),' ',ifnull(u.lastName,'') ) "
				+ " from  User u where u.legalEntityCode =sch.legalEntityCode and u.userId=sch.maker) as maker, "
				
				+ " (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 "
				
				+ " from AuditSchedule sch , UserRoleMapping ur"
				+ "   where ur.legalEntityCode =sch.legalEntityCode and ur.userId=:userId  and " // Condition added to get the Reviewer list to only the Reviewer role
				// + "    and ur.userRoleId in ('RVWR','LRVWR') " // the Roles to change for RCSA Nabard
				/*+ " , User u where u.legalEntityCode =sch.legalEntityCode  "
				+ " and u.unitCode=sch.unitId and sch.legalEntityCode=u.legalEntityCode and  u.userId=:userId " */ // Need to be uncommented 
				+ "    sch.legalEntityCode =:legalEntityCode and IFNULL(sch.auditStatus,'NotNull') in ('AS','AR','AW','AC','AF')"   // 'AI',
				+ "   and ur.userRoleId IN ('G4','G7')"
				+ "   and sch.unitId IN (:maintEntities) "
				//+ "   and sch.unitId IN (SELECT u.unitCode FROM User u WHERE u.unitCode=sch.unitId and  u.legalEntityCode =sch.legalEntityCode and u.userId=:userId) " // To pick the reviewer from the same unit
				// + "   and sch.unitId IN (SELECT e.unitCode FROM User u, MaintEntity e WHERE e.parentUnitCode=u.unitCode and e.unitCode=sch.unitId and  u.legalEntityCode =sch.legalEntityCode and u.legalEntityCode =e.legalEntityCode and u.userId=:userId) "
		        + "   and  sch.auditTypeCode IN (SELECT asgm.auditTypeCode FROM MaintEntityAuditSubgroupMapping as asgm WHERE asgm.legalEntityCode=sch.legalEntityCode and asgm.mappingType='U' and asgm.id =:userId )"  // To show the Assessments to which the Assessor Mapped (Department mapping)
		        // + " and 1 = (select u.unitCode from User u where u.legalEntityCode=sch.legalEntityCode and u.userId=:userId) "); // temporary to check the Reviewer from Head Office
		        + "  " );
		
		// Financial Year and Audit Type filter added for filtering
		if(!BankAuditUtil.isEmptyString(auditTypeCode)) {
			queryString.append(" AND  sch.auditTypeCode =:auditTypeCode "); 
		}if(financialYear !=null) {
			LocalDate fYStartDt = financialYearStrtEndList.get(0);
			LocalDate fYearEndDt = financialYearStrtEndList.get(1);
			logger.info("fYStartDt :: "+fYStartDt+" fYEndDt :: "+fYearEndDt);
			queryString.append(" AND (sch.scheduleStartDate between '"+fYStartDt+"' AND '"+fYearEndDt+"' )");
			//queryString.append(" And (sch.scheduleStartDate between :fYStartDt and :fYearEndDt )");
		}
		
		logger.info("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 a.auditTypeCode LIKE :search) ) "
					+ " or sch.planId like :search "
					+ " or sch.auditId like :search "
					+ " or sch.unitId IN (SELECT m.unitCode FROM MaintEntity m WHERE m.legalEntityCode=sch.legalEntityCode AND m.unitCode LIKE :search) "
					+ " or sch.unitId IN (SELECT m.unitCode FROM MaintEntity m WHERE m.legalEntityCode=sch.legalEntityCode AND m.unitName 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 sch.unitHead IN (SELECT u.userId FROM User u WHERE  sch.legalEntityCode =u.legalEntityCode and sch.unitHead=u.userId and (u.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.assessedRiskPrcntg like :search or sch.assessedRating like :search "
					+ " or sch.effectiveRiskPrcntg like :search or sch.effectiveRating like :search "
					+ " or sch.reviewStatus 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 )) "
					
					+ " 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 g.value LIKE :search)"
				//	+ " and   ( sch.observationStatus  ='AE' and sch.auditStatus='AW'  ) "
					+ " ) "
				
					+ " or sch.reviewStatus 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.reviewStatus "
					+ " and (g.description LIKE :search or g.value LIKE :search)"
					//	+ " and   ( sch.observationStatus  ='AE' and sch.auditStatus='AW'  ) "
					+ " ) "
					
					/*+ " 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 g.value LIKE :search)"
					+ " and   ( sch.observationStatus  ='AE' and sch.auditStatus='AR'  ) "
					+ " ) "
					
					+ " 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 g.value LIKE :search)"
					+ " and   ( sch.observationStatus  ='AE' and sch.auditStatus='AC'  ) "*/
					+ " ) "
					
					+ "  " );
					
					if((search.length()==2 && search.toUpperCase().startsWith("EX") )
					|| (search.length()==3 && search.toUpperCase().startsWith("EXI"))
					|| (search.length()==4 && search.toUpperCase().startsWith("EXIT"))
					|| ((search.length()==5 || search.length()==6) && search.toUpperCase().startsWith("EXITE")) ){
					queryString.append("Or (sch.auditStatus='AI' and sch.observationStatus='AE' )");
					}
					queryString.append(" )" );
					
				/*	+ " ) "
					+ " )"
					);*/
		}
		
	/*	queryString=queryString.append("  ORDER BY FIELD(sch.auditStatus, 'AI','AP','AC','AU') asc ");*/
		 
		String[] columns={
				"sch.auditTypeCode",
				"(sch.auditId *1 ), (sch.planId *1 )", //"CAST(planId AS Integer),CAST(auditId AS Integer) "
				"unitType",
				"(sch.unitId * 1) ",  //"unitId",
				"uCriticality",
				"sch.duration",
				"sch.lastAuditDate",//last audit date actually 
				"sch.scheduleStartDate",
				"sch.scheduleEndDate",
				"sch.actualStartDate",
				"sch.actualEndDate",  // 10
				"teamLead", // Team Lead 
				"sch.maker",
				"sch.reviewStatus",
				"sch.auditStatus",
				"unitName",   // 15
				"financialYear"
		};
		
		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(IFNULL(sch.reviewStatus,'NoStatus'),'WS','WI') desc ,  "
					+ " FIELD( IFNULL(sch.auditStatus,'NoStatus') , 'AI','AS','AR','AC','AU','UP','NoStatus') asc  "); // The code modified to get the records in the order of Status on landing page, the sequence is  Initiated, Submitted, Respond, Reviewed, Closed
		}else if(orderColumn!=null
				&& !BankAuditUtil.isEmptyString(orderDirection)){
			queryString=queryString.append(" ORDER BY  ").append(columns[orderColumn]).append(" ").append(orderDirection);
		}else {
			queryString=queryString.append(" ORDER BY FIELD(sch.auditStatus, 'AI','AS','AR','AC','AU','UP') asc "); // Status AU & UP are the same
		}
		
		

		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.setAuditTypeCode(tuple[1].toString());
							if(tuple[2]!=null) {
								//String unitCodeName=tuple[2].toString();
								//auditSchedule.setUnitId(unitCodeName);
								//auditSchedule.setUnitName(unitCodeName.substring(unitCodeName.indexOf('-')+1));
								auditSchedule.setUnitId(auditSchedule.getUnitId()+"-"+tuple[2].toString());
								auditSchedule.setUnitName(tuple[2].toString());
							}
							
							//if(tuple[3]!=null) auditSchedule.setTeamLead(userDtls.get(tuple[3].toString()));
							if(tuple[3]!=null) { 
								String teamLead = userDtls.get(tuple[3].toString());
								auditSchedule.setTeamLead(teamLead.equalsIgnoreCase("Me")?"Me":tuple[3].toString()+"-"+teamLead);
							}
							
							if(tuple[4]!=null) auditSchedule.setIsUnitHeadUser(tuple[4].toString());
							if(tuple[5]!=null) {
								/*String[] auditSts = tuple[5].toString().split("-");
								if(auditSts !=null && auditSts.length ==2) {
									auditSchedule.setAuditStatus(auditSts[0]);
									auditSchedule.setAuditStatusDescription(auditSts[1]);
								}*/
								auditSchedule.setAuditStatus(tuple[5].toString().split("-")[0]);
								auditSchedule.setAuditStatusDescription(tuple[5].toString().split("-")[1]);
							}
							if(tuple[6]!=null) auditSchedule.setReviewStatus(tuple[6].toString());
							if(tuple[7]!=null && auditSchedule.getRespondedFindings()!= null) {
								auditSchedule.setResponseReject(Integer.parseInt(tuple[7].toString()));
								auditSchedule.setRespondedFindings(auditSchedule.getRespondedFindings()- (Integer.parseInt(tuple[7].toString()))); // Included to find out the Responded Observations
							}
							else auditSchedule.setResponseReject(0);
						
							if(tuple[8]!=null) auditSchedule.setComplianceCount(Integer.parseInt(tuple[8].toString()));
							else auditSchedule.setComplianceCount(0);
						
							if(tuple[9]!=null)	auditSchedule.setNotApplicableCount(Integer.parseInt(tuple[9].toString()));
							else auditSchedule.setNotApplicableCount(0);
							
							if(tuple[10]!=null)	auditSchedule.setReviewerId(tuple[10].toString());

							if(tuple[11]!=null)	auditSchedule.setUnitType(tuple[11].toString());
							
							//if(tuple[10]!=null)	auditSchedule.setAssessedRating(tuple[10].toString());
														
							//if(tuple[11]!=null)	auditSchedule.setEffectiveRiskPrcntg(tuple[11].toString());
							
							
							auditSchedule.setUnitHead(auditSchedule.getUnitHead()+"-"+userDtls.get(auditSchedule.getUnitHead()));				
							
							if(tuple[12]!=null){
								String teamMembers="";
								String[] members = tuple[12].toString().split(",");
								for (String member: members) {
									teamMembers+=member+"-"+userDtls.get(member)+",";
								}
								auditSchedule.setTeamMember(teamMembers.substring(0,teamMembers.length()-1));
							}
							
							if(tuple[13]!=null)	auditSchedule.setMaker(tuple[13].toString());
							if(tuple[14]!=null)	auditSchedule.setuCriticality(tuple[14].toString());
							
							//int tmSize =0;
							/*if(auditSchedule.getAuditTeams() != null ) 
								tmSize= auditSchedule.getAuditTeams().size();
							String teamMember="";
							for( AuditTeam adtTm: auditSchedule.getAuditTeams() ) {
								adtTm.setUserId(adtTm.getUserId()+"-"+userDtls.get(adtTm.getUserId()));
								teamMember +=adtTm.getUserId() + ",";
							}if(tmSize >0 ) auditSchedule.setTeamMember(teamMember.substring(0,teamMember.lastIndexOf(",")));
							*/
							//System.out.println("Team size ...."+auditSchedule.getAuditTeams().size());
						}
						return auditSchedule;
					}
			@Override
			public List transformList(List collection) {
				return collection;
			}
		})		
				.setParameter("legalEntityCode",legalEntityCode).setParameter("userId", userId)
				.setParameter("maintEntities", maintEntities);

		if(!BankAuditUtil.isEmptyString(search)){
			query.setParameter("search", "%"+search+"%");
		}
		if(!BankAuditUtil.isEmptyString(auditTypeCode)) {
			query.setParameter("auditTypeCode", auditTypeCode);
		}
		
		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();
		
		System.out.println("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 AuditSchedule getReviewAuditByPlanIdAndAuditId(int legalEntityCode, String auditTypeCode, String planId,
			String auditId) {
		Session session = getSession();
		session.setDefaultReadOnly(true);
		
		AuditSchedule auditSchedule = null;
		try {
			
			//StringBuilder queryString=new StringBuilder(" from AuditSchedule where legalEntityCode =:legalEntityCode ");
			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 concat(u.firstName , ' ' , u.lastName) from  User u,AuditTeam t where u.userId =t.userId and t.auditId=sch.auditId and t.legalEntityCode =u.legalEntityCode and t.legalEntityCode =sch.legalEntityCode and t.userType='L') as teamLead, "
					
					+ " (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, "
					+ " (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 tm.auditTypeCode,GROUP_CONCAT(tm.name) from (Select t.auditTypeCode,t.planId, t.auditId, concat(u.firstName , ' ' , u.lastName) as name from  User u,AuditTeam t where u.userId =t.userId and t.auditId=sch.auditId and t.legalEntityCode =u.legalEntityCode and t.legalEntityCode =sch.legalEntityCode and t.userType='T') tm where  GROUP BY tm.auditTypeCode  ) as teamMember "
					+ " (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(sch.reviewStatus , '-' , g.description) from  GeneralParameter g where g.legalEntityCode =sch.legalEntityCode and g.key1='STATUS' and g.key2='AUDIT_STATUS' and g.value=sch.reviewStatus) as reviewStatus "
					+ ", (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 "
					+ " from AuditSchedule sch where legalEntityCode =:legalEntityCode and planId=:planId and auditId=:auditId ");
			System.out.println("Before the qry....");
			
			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());
								}
								if(tuple[9]!=null){
									auditSchedule.setDuration(tuple[9].toString());
								}
								if(tuple[10]!=null){
									auditSchedule.setFrequency(tuple[10].toString());
								}
								if(tuple[12]!=null)	auditSchedule.setReviewStatus(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);
								// int tmSize =auditSchedule.getAuditTeams().size();
								
							}
							return auditSchedule;
						}
				@Override
				public List transformList(List collection) {
					return collection;
				}
			})      .setParameter("legalEntityCode",legalEntityCode)
					.setParameter("planId",planId)
					.setParameter("auditId",auditId);
			
			 auditSchedule=(AuditSchedule)query.uniqueResult(); 

		}catch(Exception e) {
			e.printStackTrace();
		}return auditSchedule; 
	}
	
	@Override
	public void updateAuditReviewStatus(AuditSchedule auditSchedule) {
		Session session = getSession(); 
		try {
			String reviewStatus =auditSchedule.getReviewStatus();
			Date reviewDate = auditSchedule.getBusinessdateTimestamp();
			String reviewerId =auditSchedule.getReviewerId(); 
			
			logger.info("auditStatus:: "+ reviewStatus + " reviewDate:: "+reviewDate + " reviewerId:: "+reviewerId );
			
			AuditSchedule audSch1= (AuditSchedule)session.get(AuditSchedule.class,auditSchedule);
			logger.info("audSch1 from Table auditStatus  :: "+ audSch1.getReviewStatus());
			
			audSch1.setReviewStatus(reviewStatus);			 
			if("WI".equalsIgnoreCase(reviewStatus)){
				audSch1.setReviewerId(reviewerId);
				audSch1.setReviewInitiatedDate(businessDateService.getBusinessDateTimestampByLegalEntityCode(audSch1.getLegalEntityCode()));				  
			}else if("WB".equalsIgnoreCase(reviewStatus)) {
				audSch1.setReviewerId("");
				audSch1.setReviewStatus(""); 
			}else if("WS".equalsIgnoreCase(reviewStatus)) {
				audSch1.setReviewedDate(businessDateService.getBusinessDateTimestampByLegalEntityCode(audSch1.getLegalEntityCode()));
				audSch1.setReviewerId(reviewerId);
				//audSch1.setAuditStatus("AC"); // commented foor BaaS, as a separate process included for Closure
				audSch1.setAuditStatus("AW"); // for BaaS				 
			} 
			session.update(audSch1);
		
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
	}	
	
	@Override
	public void updateAuditClosureStatus (AuditSchedule auditSchedule) {
		Session session = getSession();
		try {
			String reviewStatus =auditSchedule.getReviewStatus();
			String closureUserId =auditSchedule.getClosureUserId(); 
			logger.info("updateAuditClosureStatus reviewStatus:: "+ reviewStatus + " closureUserId:: "+closureUserId );
			
			AuditSchedule audSch1= (AuditSchedule)session.get(AuditSchedule.class,auditSchedule);
			logger.info("audSch1 from Table auditStatus  :: "+ audSch1.getAuditStatus());
			
			audSch1.setClosureDate(businessDateService.getBusinessDateTimestampByLegalEntityCode(audSch1.getLegalEntityCode()));
			audSch1.setClosureUserId(closureUserId);
			audSch1.setAuditStatus("AC"); 
			
			session.update(audSch1);
			
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
	}
	
	@Override
	public DataTableResponse getReviewObservationList(int legalEntityCode, String planId, String auditId, String search,
			Integer orderColumn, String orderDirection, Integer page, Integer size) {

		Session session = getSession();
		session.setDefaultReadOnly(true);
		
		List<AuditObservation> auditObservations=null;

		DataTableResponse dataTableResponse=new DataTableResponse();

		try {
		//StringBuilder queryString=new StringBuilder(" from AuditSchedule where legalEntityCode =:legalEntityCode ");
		StringBuilder queryString=new StringBuilder(// "select ols as auditObservation from "
				/* "    from AuditObservationWrk  where legalEntityCode =:legalEntityCode "
				+ " union "
				+ "  from AuditObservation  where legalEntityCode =:legalEntityCode "
				
				+ "   "
				" SELECT legal_entity_code, status, maker_timestamp, maker  FROM audit_observation  "
				+"  WHERE  "
				+"  STATUS !='D' "
				+"  AND legal_entity_code=:legalEntityCode "
				+"  UNION "
				+"   SELECT legal_entity_code, status, maker_timestamp, maker FROM audit_observation_wrk  "
				+"   WHERE legal_entity_code=:legalEntityCode "
				+"   AND ((maker='SYSTEM' AND STATUS='DF') OR (STATUS!='DF') )  "
				+"   ORDER BY FIELD(STATUS, 'AS','M') DESC, maker_timestamp DESC  "
				+" ");*/
				"  SELECT * FROM (  "
				+" SELECT  (select concat(a.audit_group_code,'-',a.audit_group_name) from maint_audit_group a where a.audit_type_code=mp.audit_type_code and a.audit_group_code=mp.audit_group_code and a.legal_entity_code=mp.legal_entity_code ) as audit_group_name, "
				+"   (select concat(a.audit_sub_group_code,'-',a.audit_sub_group_name) from maint_audit_subgroup a where a.audit_type_code=mp.audit_type_code and a.audit_group_code=mp.audit_group_code and a.audit_sub_group_code=mp.audit_sub_group_code and  a.legal_entity_code=mp.legal_entity_code ) as audit_sub_group_name, "
				+"   (select concat(a.activity_code,'-',a.activity_name) from maint_audit_activity a where  a.activity_id=mp.activity_id and a.legal_entity_code=mp.legal_entity_code ) as activity_name, "
				+"   (select concat(a.process_code,'-',a.process_name) from maint_audit_process a where a.process_id=mp.process_id and  a.legal_entity_code=mp.legal_entity_code ) as process_name, "
				+"   (select concat( a.finding_code,'-',a.finding_name) from maint_audit_finding a where a.finding_id=mp.finding_id and  a.legal_entity_code=mp.legal_entity_code ) as finding_name, "
				+"  obs.legal_entity_code, "
				+"   (select concat(a.audit_type_code,'-',a.audit_type_desc) from maint_audit_type_desc a where a.audit_type_code=obs.audit_type_code and  a.legal_entity_code=mp.legal_entity_code ) as audit_type_code, "
				+"   obs.plan_id, obs.audit_id, obs.group_observation_id, obs.observation_id, obs.mapping_id, obs.account_no, "    														// obs.account_no -12
				+"  obs.account_name, obs.account_currency, obs.limit_type, obs.limit_amount, obs.limit_currency, obs.balance, obs.date, obs.finding_id, obs.finding_description,  "  	//  obs.finding_description -21
				+"  obs.finding_remarks, obs.submission_status, "  //  obs.submission_status -23
				//+"  obs.finding_owner"
				+ " (Select concat(u.user_id , '-' , u.first_name, ' ',u.middle_name,' ',u.last_name) from  user u where u.legal_entity_code =obs.legal_entity_code and u.user_id=obs.finding_owner) as finding_owner, "  //finding_owner -24
				+"  obs.finding_criticality, obs.response_id, obs.response_description, obs.response_remarks,"  //  obs.response_remarks -28
				+ " (Select concat(u.user_id , '-' , u.first_name, ' ',u.middle_name,' ',u.last_name) from  user u where u.legal_entity_code =obs.legal_entity_code and u.user_id=obs.response_owner) as response_owner, "  //response_owner -29
				+"  obs.response_status, obs.review_id, obs.review_description, obs.review_remarks, "  //  obs.review_remarks -33
				+ " (Select concat(u.user_id , '-' , u.first_name, ' ',u.middle_name,' ',u.last_name) from  user u where u.legal_entity_code =obs.legal_entity_code and u.user_id=obs.review_owner) as review_owner, "  //review_owner -34
				//+ " obs.review_status,"
				+"  (Select concat(obs.review_status , '-' , g.description) from  general_parameter g where g.legal_entity_code =obs.legal_entity_code and g.key_1='STATUS' and g.key_2='STATUS' and g.value=obs.review_status) as review_status, " //review_status -35
				+ " obs.remarks, obs.status, obs.entity_status, obs.maker, "    //  obs.maker - 39
				+"  obs.maker_timestamp, obs.checker, obs.checker_timestamp, obs.businessdate_timestamp, obs.sysdate_timestamp,obs.capture_way " //  obs.capture_way - 45
				+"  ,obs.capture_type,obs.cust_id, "  //  obs.cust_id - 47
				+ " (Select  g.description from  general_parameter g where g.legal_entity_code =obs.legal_entity_code and g.key_1='OBSERVATION_CAPTURE' and g.key_2='COMPLIANCE_CATEGORY' and g.value=obs.compliance_category) as compliance_category "  // compliance_category -48
				+"   ,(select c.criticality_desc from maint_audit_finding a, maint_criticality c where a.f_criticality=c.criticality_code and c.criticality_of_type='Finding' and a.legal_entity_code=c.legal_entity_code and  a.finding_code=mp.finding_code and  a.legal_entity_code=mp.legal_entity_code ) as finding_criticality_1 "
				+ " from ( "									
				+" SELECT *   FROM audit_observation   "
				//+"  WHERE  IFNULL(STATUS,'NotNull') not in ('ODF','OU','OM','D') AND legal_entity_code=:legalEntityCode AND plan_id=:planId AND audit_id=:auditId  "
				//+"  WHERE  IFNULL(response_status,'NotNull') in ('A','R') "
				+" 	  WHERE  (IFNULL(response_status, 'NotNull') IN ('A' , 'R') OR compliance_category <> 'N')	" // Condition criteria modified to get the Compliance & Not Applicable Records for Reviewer, even before Responded. i.e after the the Auditor raised any Compliance or Not Applicable observations
				+"       AND status <> 'ODF' "  // Condition added to restrict the Draft during observation capture 
				+"		 AND legal_entity_code=:legalEntityCode AND plan_id=:planId AND audit_id=:auditId  "
				+"  UNION  "
				+"   SELECT * FROM audit_observation_wrk   "
				+"   WHERE legal_entity_code=:legalEntityCode AND plan_id=:planId AND audit_id=:auditId  "
				+"   ) as obs, activity_process_finding_mapping mp "
				+"   where mp.mapping_id = obs.mapping_id and mp.legal_entity_code=obs.legal_entity_code "
				+" ) as obsTempTable ");
		
		
		if(!BankAuditUtil.isEmptyString(search)){
			queryString=queryString.append(" where ( observation_id like :search "
					+ " or obsTempTable.audit_group_name like :search or obsTempTable.audit_sub_group_name like :search or activity_name like :search or process_name like :search or finding_name like :search "
					//+ " or cust_id like :search or account_no like :search or account_name like :search or account_currency like :search or limit_type like :search or limit_currency like :search "
					+ " or finding_criticality_1 like :search "  // for Loss event type
					//+ " or limit_amount like :search or balance like :search or DATE_FORMAT(date, '%d %b %Y') LIKE :search "
					+ " or review_status like :search or review_owner like :search"
					//+ " or ('Accounts' like :search AND capture_way = 'A') or ('Findings' like :search AND capture_way = 'F') "
					//+ " or obsTempTable.compliance_category IN (SELECT g.value FROM general_parameter g WHERE obsTempTable.legal_entity_code =g.legal_entity_code and g.key_1='OBSERVATION_CAPTURE' and g.key_2='COMPLIANCE_CATEGORY' and g.value=obsTempTable.compliance_category and g.description LIKE :search) "
					// + " or obsTempTable.compliance_category  like :search "
					//+ " or status like :search ) "
					// + " or (select description from general_parameter g where g.legal_entity_code=obsTempTable.legal_entity_code and obsTempTable.review_status=g.value and g.key_1='STATUS') like :search "
					+ " ) "
					);
		}
		//queryString.append(" ORDER BY FIELD(submission_status, 'ODF','OM','OU','OA') DESC ");

		String columns[]={
				"observation_id",
				"audit_group_name",
				"audit_sub_group_name",
				"activity_name",
				"process_name",
				"finding_name",
				"obsTempTable.finding_criticality_1",
				"review_status",
				"review_owner"	};

		/*if(orderColumn!=null
				&& !BankAuditUtil.isEmptyString(orderDirection)){
			queryString=queryString.append(" order by ").append(columns[orderColumn]).append(" ").append(orderDirection);
		}*/
		
		if(orderColumn!=null && orderColumn == -1) {
			queryString=queryString.append("  ORDER BY FIELD(review_status, 'DF','M','U','A') DESC ");
		}else if(orderColumn!=null && !BankAuditUtil.isEmptyString(orderDirection)){
			queryString=queryString.append("  ORDER BY  ").append(columns[orderColumn]).append(" ").append(orderDirection);
		}
		
		

		Query query=(Query) session.createSQLQuery(queryString.toString())/*.setResultTransformer(Transformers.aliasToBean(AuditObservationWrk.class))*/		
				.setResultTransformer(new ResultTransformer() {
					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						AuditObservation auditObservation=new AuditObservation();
						//System.out.println("tuple[0] is ...."+tuple[0]);
						auditObservation.setLegalEntityCode(Integer.parseInt(tuple[5].toString()));
						auditObservation.setAuditTypeCode(tuple[6]+"");
						auditObservation.setPlanId(tuple[7]+"");
						auditObservation.setAuditId(tuple[8]+"");
						auditObservation.setGroupObservationId(tuple[9]+"");
						auditObservation.setObservationId(tuple[10]+"");
						
						auditObservation.setMappingId(tuple[11]+"");
						if(tuple[12] != null)auditObservation.setAccountNo(tuple[12]+"");
						if(tuple[13] != null)auditObservation.setAccountName(tuple[13]+"");
						if(tuple[14] != null)auditObservation.setAccountCurrency(tuple[14]+"");
						if(tuple[15] != null)auditObservation.setLimitType(tuple[15]+"");
						if(tuple[16] != null)auditObservation.setLimitAmount(tuple[16].toString());
						if(tuple[17] != null)auditObservation.setLimitCurrency(tuple[17]+"");
						if(tuple[18] != null)auditObservation.setBalance(tuple[18].toString());
						if(tuple[19] != null && !"".equalsIgnoreCase(tuple[19].toString())) 
							auditObservation.setDate(BankAuditUtil.parseStringToDate(tuple[19].toString()));
						if(tuple[20] != null)auditObservation.setFindingId(tuple[20]+"");
						if(tuple[21] != null)auditObservation.setFindingDescription(tuple[21]+"");
						if(tuple[22] != null)auditObservation.setFindingRemarks(tuple[22]+"");
						if(tuple[23] != null)auditObservation.setSubmissionStatus(tuple[23]+"");
						if(tuple[24] != null)auditObservation.setFindingOwner(tuple[24]+"");
						
						if(tuple[26] != null)auditObservation.setResponseId(tuple[26]+"");
						if(tuple[27] != null)auditObservation.setResponseDescription(tuple[27]+"");
						if(tuple[28] != null)auditObservation.setResponseRemarks(tuple[28]+"");
						if(tuple[29] != null)auditObservation.setResponseOwner(tuple[29]+"");
						if(tuple[30] != null)auditObservation.setResponseStatus(tuple[30]+"");
						
						if(tuple[31] != null)auditObservation.setReviewId(tuple[31]+"");
						if(tuple[32] != null)auditObservation.setReviewDescription(tuple[32]+"");
						if(tuple[33] != null)auditObservation.setReviewRemarks(tuple[33]+"");
						if(tuple[34] != null)auditObservation.setReviewOwner(tuple[34]+"");
						if(tuple[35] != null)auditObservation.setReviewStatus(tuple[35]+"");
						
						if(tuple[37] != null)auditObservation.setStatus(tuple[37]+"");
						if(tuple[45] != null)auditObservation.setCaptureWay(tuple[45]+"");
						if(tuple[47] != null) auditObservation.setCustId(tuple[47]+"");
						if(tuple[48] != null) auditObservation.setComplianceCategory(tuple[48]+"");
						if(tuple[49] != null) auditObservation.setfCriticality(tuple[49]+"");
						
						auditObservation.setAuditGroupCode(tuple[0]+"");
						auditObservation.setAuditSubGroupCode(tuple[1]+"");
						auditObservation.setActivityCode(tuple[2]+"");
						auditObservation.setProcessCode(tuple[3]+"");
						auditObservation.setFindingCode(tuple[4]+"");
						
						
						return auditObservation;
					}
			@Override
			public List transformList(List collection) {
				return collection;
			}
		})	
				.setParameter("legalEntityCode",legalEntityCode).setParameter("planId",planId).setParameter("auditId",auditId);

		if(!BankAuditUtil.isEmptyString(search)){
			query.setParameter("search", "%"+search+"%");
		}
		
		List<AuditObservation> tmpAuditObservation =query.list(); 
		Long totalRecords=null;
		try {
			totalRecords = (long)tmpAuditObservation.size();
		}catch(Exception e) {
			totalRecords = (long)0;
		}
		System.out.println("totalRecords .... "+ totalRecords + "page :: "+ page +" size:: "+ size);
		List<AuditObservation> auditObservation=query.setFirstResult((page*size)).setMaxResults(size).list();
		
		dataTableResponse.setData(auditObservation);
		dataTableResponse.setRecordsTotal(totalRecords);
		dataTableResponse.setRecordsFiltered(totalRecords);
		
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		return dataTableResponse;
	}
	
	@Override
	public String isReviewObsPendingBfrReviewSubmit(AuditSchedule auditSchedule) {

		Session session=getSession();
		String isReviewObsPendingBfrReviewSubmit ="Y"; int cntUnAuth =0;
		logger.info("LE:: "+ auditSchedule.getLegalEntityCode() + " Audit ID:: "+auditSchedule.getAuditId() + " Bsns Date::" + auditSchedule.getBusinessdateTimestamp() + "Audit Type:: "+ auditSchedule.getAuditTypeCode());
		try {
			StringBuilder queryStr1=new StringBuilder("");
			queryStr1.append(" select count(*) from AuditObservation where legalEntityCode=:legalEntityCode and auditId=:auditId ");
			queryStr1.append(" and auditTypeCode = :auditTypeCode and  ifnull(reviewStatus,'Null') != 'A' ");
			
			cntUnAuth=Integer.parseInt((session.createQuery(queryStr1.toString())
					.setParameter("legalEntityCode", auditSchedule.getLegalEntityCode())
					.setParameter("auditId",auditSchedule.getAuditId())
					.setParameter("auditTypeCode", auditSchedule.getAuditTypeCode())).uniqueResult().toString());
			logger.info("isRespondObsPendingBfrRespondSubmit QRY is :: "+ queryStr1);
	
			logger.info("cntUnAuth is :: "+ cntUnAuth);
			isReviewObsPendingBfrReviewSubmit =(cntUnAuth > 0)?"Y":"N";
			
			logger.info("isReviewObsPendingBfrReviewSubmit QRY is :: "+ isReviewObsPendingBfrReviewSubmit);
			
		}catch(Exception e) {
			e.printStackTrace();
		}
		return isReviewObsPendingBfrReviewSubmit;
	}
	
	@SuppressWarnings({ "unchecked", "serial", "deprecation" })
	public String updateProbSevirtyScoreRiskwise(AuditSchedule auditSchedule){
		logger.info("updateProbSevirtyScoreRiskwise ...."+ auditSchedule.getLegalEntityCode() + "::" + auditSchedule.getAuditTypeCode() +" :: "+ auditSchedule.getAuditId()+" ::"+ auditSchedule.getObservationStatus()+"::"+auditSchedule.getReviewerId()  );
		
		String hql ="";		
		hql+="CALL update_prob_sevirty_score_riskwise(:legalEntityCode,:auditTypeCcode,:auditId,:observationStatus,:maker)";
		Session session=getSession();
		String str = (String) session.createSQLQuery(hql)
				.addScalar("v_status", StandardBasicTypes.STRING)
				.setParameter("legalEntityCode", auditSchedule.getLegalEntityCode())
			    .setParameter("auditTypeCcode", auditSchedule.getAuditTypeCode())
			    .setParameter("auditId", auditSchedule.getAuditId())
			    .setParameter("observationStatus", auditSchedule.getObservationStatus())
			    .setParameter("maker", auditSchedule.getReviewerId()).uniqueResult();
		
		logger.info("str ...."+ str );
		return str;
	}	
	
	@SuppressWarnings({ "unchecked", "serial", "deprecation" })
	public String updateResidualriskAssessmentwiseCallHealthIndex(AuditSchedule auditSchedule){
		logger.info("updateResidualriskAssessmentwiseCallHealthIndex .... :: "+ auditSchedule.getLegalEntityCode() + " :: " + auditSchedule.getAuditTypeCode()); 
		logger.info(" :: "+ auditSchedule.getAuditId()+" ::"+ auditSchedule.getRespondStatus()+"::"+auditSchedule.getReviewerId()  );
		
		String hql ="";		
		//hql+="CALL update_residualrisk_assessmentwise_call_health_index(:legalEntityCode,:auditTypeCcode,:auditId,:respondStatus,:maker)";
		
		hql+="CALL update_residualrisk_assessmentwise_call_health_index("+auditSchedule.getLegalEntityCode()+",'"+auditSchedule.getAuditTypeCode()+"','"+auditSchedule.getAuditId()+"','"+auditSchedule.getRespondStatus()+"','"+auditSchedule.getReviewerId()+"')";
		logger.info("==> CALL update_residualrisk_assessmentwise_call_health_index(  "+auditSchedule.getLegalEntityCode()+",'"+auditSchedule.getAuditTypeCode()+"','"+auditSchedule.getAuditId()+"','"+auditSchedule.getRespondStatus()+"','"+auditSchedule.getReviewerId()+"') ");
		Session session=getSession();
		//String str = (String) 
				session.createSQLQuery(hql)
				.addScalar("v_status", StandardBasicTypes.STRING)
				/*.setParameter("legalEntityCode", auditSchedule.getLegalEntityCode())
			    .setParameter("auditTypeCcode", auditSchedule.getAuditTypeCode())
			    .setParameter("auditId", auditSchedule.getAuditId())
			    .setParameter("respondStatus", auditSchedule.getRespondStatus())
			    .setParameter("maker", auditSchedule.getReviewerId())*/
			    .executeUpdate();  //;  //executeUpdate()  uniqueResult()
		
		//logger.info("str ...."+ str );
		return "";
	}	

	@Override
	public String isRatingsGiven(int legalEntityCode,  String   auditId) { 
		Session session=getSession();
		String isRatingsGiven ="Y"; int cntRating =0;
		logger.info("LE:: "+ legalEntityCode + " Audit ID:: "+auditId);
		 
		/*StringBuilder queryStr1=new StringBuilder(" select count(*) from AuditSchedule sch where ");
		queryStr1.append(" legalEntityCode=:legalEntityCode and auditId=:auditId and deskComplianceScore is not null and deskComplianceRating is not null ");
		
		cntRating=Integer.parseInt((session.createQuery(queryStr1.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditId",auditId)).uniqueResult().toString());
		logger.info("isRatingsGiven QRY is :: "+ queryStr1);

		logger.info("cntRating is :: "+ cntRating);
		isRatingsGiven =(cntRating > 0)?"Y":"N";
		
		logger.info("isRatingsGiven QRY is :: "+ isRatingsGiven);*/
		
		String strQry = " Select sch.deskComplianceRating , "
				+ " (Select  tmp.deskComplianceRating  from AuditScheduleWrkTmp tmp where  tmp.auditId = sch.auditId and "
						+ " tmp.legalEntityCode = tmp.legalEntityCode  ) as tempauditId "
				+ " FROM AuditSchedule sch"
				+ " where legalEntityCode=:legalEntityCode "  
					+ "  and auditId = :auditId " ;	
		Query qry =session.createQuery(strQry) 
				.setParameter("legalEntityCode", legalEntityCode) 
				.setParameter("auditId", auditId);
		
		Object[] data = (Object[]) qry.uniqueResult();  
		if(data != null){
			String onsiteComplianceRating =  data[0]==null?null:data[0].toString();
			String onsiteComplianceRatingTmp = data[1]==null?null:data[1].toString();
			if(!BankAuditUtil.isEmptyString(onsiteComplianceRating)
					&& !BankAuditUtil.isEmptyString(onsiteComplianceRatingTmp)){
				return BankAuditConstant.VALIDATION_FAILED; 
			}else if(!BankAuditUtil.isEmptyString(onsiteComplianceRating)){
				return BankAuditConstant.SUCCESS;
			} 
		} 
		return BankAuditConstant.FAILED;		 
	}
	
	@Override
	public void updateAuditReviewerId(AuditSchedule auditSchedule) {
		Session session = getSession();
		try {  
			logger.info("updateAuditReviewerId reviewStatus:: "+ auditSchedule.getReviewStatus() + " reviewerId:: "+auditSchedule.getReviewerId() );
			  		
			String strQuery = " UPDATE AuditSchedule sch "
					+ " set sch.reviewerId = :reviewerId "
					+ " where sch.auditId = :auditId  "
					+ " and sch.auditTypeCode = :auditTypeCode "
					+ " and sch.legalEntityCode = :legalEntityCode "; 
			
			session.createQuery(strQuery)
					.setParameter("reviewerId", auditSchedule.getReviewerId())
					.setParameter("auditId", auditSchedule.getAuditId())
					.setParameter("auditTypeCode", auditSchedule.getAuditTypeCode())
					.setParameter("legalEntityCode", auditSchedule.getLegalEntityCode())
					.executeUpdate();
			
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
	}
	
	@Override
 	public void deleteDeskComplianceDataFromWrkTmp(int legalEntityCode,String auditTypeCode, String planId,String auditId){
			
		Session session =getSession();  		
		 
		String strQry = " Delete from AuditScheduleWrkTmp sch "
				+ " where legalEntityCode=:legalEntityCode " 		
					+ "  and auditTypeCode=:auditTypeCode " 
					+ "  and auditId=:auditId " ;	
		
		session.createQuery(strQry)
 			.setParameter("legalEntityCode", legalEntityCode)
 			.setParameter("auditTypeCode",auditTypeCode)
 			.setParameter("auditId", auditId)
 			.executeUpdate(); 		 
	}

}
