package com.bankaudit.rest.process.dao;

import java.sql.Timestamp;
import java.time.LocalDate;
import java.util.ArrayList;
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.jboss.logging.Logger;
import org.springframework.beans.BeanUtils;
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.dto.process.AuditRatingDto;
import com.bankaudit.model.User;
import com.bankaudit.process.model.AuditSchedule;
import com.bankaudit.process.model.AuditScheduleWrkTmp;
import com.bankaudit.service.BusinessDateService;
import com.bankaudit.service.MaintEntityService;
import com.bankaudit.service.UserService;
import com.bankaudit.util.BankAuditUtil;

@Repository("auditRatingDao")
public class AuditRatingDaoImpl extends AbstractDao implements AuditRatingDao{
	
	@Autowired
	BusinessDateService businessDateService;
	
	@Autowired
    MaintEntityService maintEntityService;
	
	@Autowired
    UserService userService;
	
	
	static final Logger logger = Logger.getLogger(AuditRatingDaoImpl.class);
	
	@SuppressWarnings("unchecked")
	@Override
	public DataTableResponse getAuditRatingList(int legalEntityCode,String userId, String unitCode, String role, String search, Integer orderColumn,
			String orderDirection, Integer page, Integer size, String documentType, String documentSubType,
			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("getAuditRatingList .. " +legalEntityCode+" :: "+userId+" :: "+role);

		/********************** 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(); ) {
		Iterator iter = lst.iterator();
		while(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("userId is .... "+ userId+" serach:: "+search +" legal entity code:: "+legalEntityCode);
		logger.info("User details val is .... "+ userDtls);
		/******************** User Retrieval logic, Ends here ********************************/
		
		/***************** Logic to retrieve duration and No. of Audits from general parameter table and  ****
		 ***************** 	comparing both to find out the minimum, Starts here 	      ********************/
		Date businessDate =businessDateService.getBusinessDateTimestampByLegalEntityCode(legalEntityCode);
		logger.info("businessDate .... "+ businessDate);
		/* 
		StringBuilder queryString2=new StringBuilder( " select nd.auditsInInterval,nads.noAudits from " 
				+ " ( select count(*) as auditsInInterval from audit_schedule ash, audit_team atm " 
				+ "	where ash.plan_id=atm.plan_id and ash.audit_id = atm.audit_id and ash.legal_entity_code=atm.legal_entity_code and atm.user_id=:userId " 
				+ "          and (ash.schedule_start_date between :businessDate and " 
				+ "				ADDDATE(:businessDate, INTERVAL (select g.value from general_parameter g where key_1='AUDITOR_VISIBILITY' AND key_2='DAYS' AND g.legal_entity_code=ash.legal_entity_code ) DAY))  ) as nd, " 
				+ " ( select g.value as noAudits from general_parameter g where g.key_1='AUDITOR_VISIBILITY' AND g.key_2='NO_OF_AUDITS' AND g.legal_entity_code=:legalEntityCode) as nads "  
				); 
		//(select g.value from GeneralParameter g where g.key1='AUDITOR_VISIBILITY' AND g.key2='DAYS' AND g.legalEntityCode=:legalEntityCode) 
		
		Query query2lst=session.createSQLQuery(queryString2.toString())
				.setParameter("legalEntityCode", legalEntityCode).setParameter("userId", userId)
				.setParameter("businessDate", businessDate);
		
		Object obj = query2lst.uniqueResult();
		int auidtsDisplay = 100;
		try{
			if(obj != null  && ((Object[])obj).length > 0)
			{
				int auditsInInterval  =Integer.parseInt( ((Object[])obj)[0].toString());
				int  noOfAudit=Integer.parseInt( ((Object[])obj)[1].toString());
				System.out.println("auditsInInterval .... "+ auditsInInterval +" noOfAudit.... "+noOfAudit);
				if(auditsInInterval > noOfAudit) auidtsDisplay=noOfAudit;
				else auidtsDisplay = auditsInInterval;
			}
		}catch(Exception e) {
			System.out.println("Exception occured ....");
		}
		System.out.println("auidtsDisplay .... "+auidtsDisplay); */
		/******************** Logic for no of Audits, Ends here ********************************/
			
		/***************** Logic to retrieve the Visible Audits, which are in Planned state. Based on the above Output ****/
		/*System.out.println("businessDate .... "+ businessDate);
		StringBuilder queryString3=new StringBuilder( " select audit_id from audit_schedule s "
				+ "	where legal_entity_code=:legalEntityCode and audit_status in ('AP') " 
				+ "		and audit_id in(select t.audit_id from audit_team t where t.legal_entity_code=s.legal_entity_code and t.user_id=:userId) " 
				+ "	order by schedule_start_date asc limit :auidtsDisplay "); 
		
		List visibleAuditList=session.createNativeQuery(queryString3.toString())
				.setParameter("legalEntityCode", legalEntityCode).setParameter("userId", userId)
				.setParameter("auidtsDisplay", auidtsDisplay).list();
		logger.info("visibleAuditList .... "+visibleAuditList);
		if(visibleAuditList != null && visibleAuditList.size() < 1) visibleAuditList.add("NoAudit"); // If the Audit visibility is 0, then we are passing NoAudit
		*/
		/******************** Logic for Visible Audits, Ends here ********************************/
		
		/************************** ********************************************/
		/*StringBuilder queryString4=new StringBuilder( " select role from user_role_mapping where legal_entity_code=:legalEntityCode and user_id=:userId limit 1"); 
		String roleId=(String)session.createNativeQuery(queryString4.toString())
				.setParameter("legalEntityCode", legalEntityCode).setParameter("userId", userId)
				.uniqueResult();
		logger.info("roleId .... "+roleId);*/
		String roleId=role;
		
		List<String> maintEntities = null;
		List<User> users =null;
		if(roleId != null && (roleId.equalsIgnoreCase("G1") || roleId.equalsIgnoreCase("G6") 
					|| roleId.equalsIgnoreCase("G4") || roleId.equalsIgnoreCase("G7")  
					|| roleId.equalsIgnoreCase("G2") || roleId.equalsIgnoreCase("G8") )) {
			maintEntities=maintEntityService.getSubBranchesGyUserIdOrUnitId(legalEntityCode,"user",userId);
			logger.info("maintEntities .... "+maintEntities);
		}
		
		/****************************** ****************************************/
		LocalDate fYStartDt=null, fYearEndDt=null;
		if(financialYear !=null) {
			fYStartDt = financialYearStrtEndList.get(0);
			fYearEndDt = financialYearStrtEndList.get(1);
			logger.info("fYStartDt :: "+fYStartDt+" fYEndDt :: "+fYearEndDt);			
		}
		
		boolean isDeptAsRMD =true;
		//isDeptAsRMD = reportDao.isDeptAsRMD(legalEntityCode, "", userId);
		
		DataTableResponse dataTableResponse=new DataTableResponse();
		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 t.userId from  AuditTeam t where t.auditId=sch.auditId and t.legalEntityCode =sch.legalEntityCode and t.userType='L') as teamLead, "
				
				+" duration," //Code to comment //+ " (Select p.duration from  MaintEntity e,MaintAuditParameter p where e.unitCode =sch.unitId and e.unitType=p.unitType and e.uCriticality=p.uCriticality and sch.auditTypeCode=p.auditTypeCode and e.legalEntityCode =sch.legalEntityCode and e.legalEntityCode =p.legalEntityCode ) as duration, "
				+" frequency," //Code to comment //+ " (Select p.frequency from  MaintEntity e,MaintAuditParameter p where e.unitCode =sch.unitId and e.unitType=p.unitType and e.uCriticality=p.uCriticality and sch.auditTypeCode=p.auditTypeCode and e.legalEntityCode =sch.legalEntityCode and e.legalEntityCode =p.legalEntityCode ) as frequency, "
				//+ " (select 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(u.userId , '-' , u.firstName, ' ',u.middleName,' ',u.lastName) from  User u where u.legalEntityCode =sch.legalEntityCode and u.userId=sch.maker) as maker "
				//+ ", (Select count(*) from  AuditObservation o where o.legalEntityCode =sch.legalEntityCode and o.auditId=sch.auditId and o.complianceCategory='N' and o.reviewStatus='R' ) as responseReject "
				//+ ", (Select count(*) from  AuditObservation o where o.legalEntityCode =sch.legalEntityCode and o.auditId=sch.auditId and o.complianceCategory='C' and o.submissionStatus='A') as complianceCount "
				//+ ", (Select count(*) from  AuditObservation o where o.legalEntityCode =sch.legalEntityCode and o.auditId=sch.auditId and o.complianceCategory='NA' and o.submissionStatus='A') as notApplicableCount "
				+" ,0,0,0 "  // No need to calculate responseReject, Compliance, NA
				//+ " , auditStatus "
				+ ", (Select g.value from GeneralParameter g where g.legalEntityCode =sch.legalEntityCode and g.key1='AUDIT_EARLY_START' and g.key2='EARLY_START_ALLOWED') as earlyStartAllowed "
				+ ", (Select g.value from GeneralParameter g where g.legalEntityCode =sch.legalEntityCode and g.key1='AUDIT_OFFSET_DAYS' and g.key2='AUDIT_OFFSET_DAYS') as auditOffsetDays " 
				+ ", (Select concat(sch.auditStatus , '-' , g.description) from  GeneralParameter g where g.legalEntityCode =sch.legalEntityCode and g.key1='STATUS' and g.key2='AUDIT_STATUS' and g.value=sch.observationStatus) as auditStatusTemp " // This result to stamp in Audit status in the Observation status is AE i.e Audit Reported
				+ ", (select group_concat(t.userId) from AuditTeam t where t.legalEntityCode=sch.legalEntityCode AND t.auditId=sch.auditId AND t.userType='M' ) AS teamMember "
				+" ,   sch.reportStatus " // Report review status added for BaaSDOS implementation 
				+ ",  (Select concat(sch.reportStatus , '-' , g.description) from  GeneralParameter g where g.legalEntityCode =sch.legalEntityCode and g.key1='STATUS' and g.key2='REPORT_STATUS' and g.value=sch.reportStatus) as reportStatusDesc " // Report review status desc added for BaaSDOS implementation 
								
				+ " from AuditSchedule sch where legalEntityCode =:legalEntityCode "
				//+ " and sch.legalEntityCode=t.legalEntityCode and t.auditId=sch.auditId and t.userId=:userId"
				// + " and  IFNULL(auditStatus,'NotNull') not in ('AD','AU','DRP','UP') "
				// Condition added to get the Assessments, For processing check with AuitTeam table till AC audit status , for View check with User table from AC audit status
				+ " and  ( (sch.auditId IN (SELECT t.auditId FROM AuditTeam t WHERE t.auditId=sch.auditId and t.legalEntityCode =sch.legalEntityCode and t.userId=:userId  ) "
				+ "           AND IFNULL(auditStatus,'NotNull') IN ('AP','AI','AS','AR') )" );
				//+ "        OR ( (sch.unitId IN  (SELECT u.unitCode FROM User u WHERE u.unitCode=sch.unitId and  u.legalEntityCode =sch.legalEntityCode and u.userId=:userId) ) "
				//+ "			  AND  IFNULL(auditStatus,'NotNull') IN ('AW','AC')  )   " );
				if(roleId != null && (roleId.equalsIgnoreCase("G1") || roleId.equalsIgnoreCase("G6"))) {
					queryString.append(" OR (sch.unitId IN (:maintEntities) AND  IFNULL(auditStatus,'NotNull') IN ('AW','AC')) ");
				}else {
					queryString.append(" OR ( (sch.unitId IN  (SELECT u.unitCode FROM User u WHERE u.unitCode=sch.unitId and  u.legalEntityCode =sch.legalEntityCode and u.userId=:userId) ) "
							+ " AND  IFNULL(auditStatus,'NotNull') IN ('AW','AC')  ) ");
				}
				queryString.append(" ) ");
				// Condition end here
				if(!isDeptAsRMD) {
					queryString.append(" 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)
				}
				//if(visibleAuditList !=null && visibleAuditList.s)
				//+ " and (sch.auditId  IN :visibleAuditList  OR auditStatus in ('AI','AS','AR','AC','AU','UP','AD','AW')) " //commented to remove Visibility cond, Condition included to get the Audits as per the Visibility logic maintained in general parameter.
						
				
		System.out.println("Before the qry....");
		
		if(!BankAuditUtil.isEmptyString(search)){
			queryString=queryString.append(" and ( "
					+ " sch.auditTypeCode IN (SELECT a.auditTypeCode FROM MaintAuditTypeDesc a  WHERE a.legalEntityCode=sch.legalEntityCode AND (a.auditTypeDesc LIKE :search OR a.auditTypeCode LIKE :search)) "
					+ " or planId like :search "
					+ " or auditId like :search "
					//+ " or unitId like :search "
					+ " or sch.unitId IN (SELECT m.unitCode FROM MaintEntity m WHERE m.legalEntityCode=sch.legalEntityCode AND (m.unitName LIKE :search or m.unitCode LIKE :search )) "
					+ " or sch.unitId IN (SELECT m.unitCode FROM MaintEntity m,GeneralParameter g WHERE sch.unitId=m.unitCode and m.legalEntityCode=sch.legalEntityCode AND m.legalEntityCode =g.legalEntityCode and g.key1='UNIT_TYPE' and g.key2='UNIT_TYPE' and g.value=m.unitType and g.description LIKE :search) "
					+ " or sch.unitId IN (SELECT m.unitCode FROM MaintEntity m,MaintCriticality c WHERE sch.unitId=m.unitCode and m.legalEntityCode=sch.legalEntityCode AND m.legalEntityCode =c.legalEntityCode and m.uCriticality=c.criticalityCode and c.criticalityOfType='Entity' and  c.criticalityDesc LIKE :search) "
					+ " or sch.unitId IN (SELECT m.unitCode FROM MaintEntity m,MaintAuditParameter p WHERE sch.unitId=m.unitCode and m.legalEntityCode=sch.legalEntityCode AND m.legalEntityCode =p.legalEntityCode and m.uCriticality=p.uCriticality and sch.auditTypeCode=p.auditTypeCode and  p.duration LIKE :search) "
					+ " or sch.auditId IN (SELECT t.auditId FROM User u,AuditTeam t WHERE t.auditId=sch.auditId and t.legalEntityCode =sch.legalEntityCode and t.legalEntityCode =u.legalEntityCode and t.userId=u.userId and (t.userId Like :search or u.firstName LIKE :search or u.lastName Like :search) ) "
					+ " or DATE_FORMAT(sch.scheduleStartDate, '%d %b %Y') LIKE :search "
					+ " or DATE_FORMAT(sch.scheduleEndDate, '%d %b %Y') LIKE :search "
					+ " or DATE_FORMAT(sch.actualStartDate, '%d %b %Y') LIKE :search "
					+ " or DATE_FORMAT(sch.actualEndDate, '%d %b %Y') LIKE :search "
					+ " or DATE_FORMAT(sch.lastAuditDate, '%d %b %Y') LIKE :search "
					+ " or sch.maker IN (SELECT u.userId FROM User u WHERE u.legalEntityCode =sch.legalEntityCode and (u.userId Like :search or u.firstName LIKE :search or u.lastName Like :search) ) "
					+ " or sch.auditStatus IN (SELECT g.value FROM GeneralParameter g WHERE sch.legalEntityCode =g.legalEntityCode and g.key1='STATUS' and g.key2='AUDIT_STATUS' and g.value=sch.auditStatus and g.description LIKE :search) "
					+ " or sch.reportStatus IN (SELECT g.value FROM GeneralParameter g WHERE sch.legalEntityCode =g.legalEntityCode and g.key1='STATUS' and g.key2='REPORT_STATUS' and g.value=sch.reportStatus and g.description LIKE :search) "
					// + " or teamMember 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 )) "
					
					+ " ) "
					// + " )"
					);
		}*/
		
			
			StringBuilder queryString=new StringBuilder(" "  
					+ " 	select audit_type_code, legal_entity_code,    auditTypeCode,  "   // 2
					+ " 	plan_id , audit_id,  unitId , unitType, uCriticality, teamLead, "  // 8
					+ " 	duration, frequency, auditStatus,  makers, z1, z2, z3, earlyStartAllowed, auditOffsetDays,  "  // 17
					+ " 	auditStatusTemp, teamMember ,   reportStatus, reportStatusDesc, ratingStatus, "   // 22
					+ " 	actualStartDate, actualEndDate, scheduleStartDate, scheduleEndDate, lastAuditDate,"  // 27
					+ " 	respondStatus,  isForRatingWorkTmp, reviewStatusDesc,"  // 30
					+ " 	observationStatusCode, auditStatusCode, auditStatusDesc, unitIdSearch, "  // 34
					+ "     ratingComplianceStatus, ratingComplianceStatusPrev, ratingComplianceAuditorAssignedPrevAuditId, prev_audit_id " // 38
					+ "		,unitName "  // 39
					+ " FROM  ( ");
			
			queryString.append( " select legal_entity_code, " 
					+ " audit_type_code," 
					
					+ "(select concat(audit_type_code , '-' , audit_type_desc) from maint_audit_type_desc at where at.audit_type_code=sch.audit_type_code"
					+ "  and at.legal_entity_code= sch.legal_entity_code ) as auditTypeCode,"
					
					+ "plan_id ,audit_id, unit_id as unitId, "
					+ " (Select  unit_name from  maint_entity e where e.unit_code =sch.unit_id and e.legal_entity_code =sch.legal_entity_code ) as unitName,  "
					
					+ " (Select g.description from  maint_entity e, general_parameter g where e.unit_code =sch.unit_id and e.legal_entity_code =sch.legal_entity_code "
					+ " and e.legal_entity_code =g.legal_entity_code and g.key_1='UNIT_TYPE' and g.key_2='UNIT_TYPE' and g.value=e.unit_type) as unitType, "
					
					+ " (Select c.criticality_desc from  maint_entity e,maint_criticality c where e.unit_code =sch.unit_id and e.legal_entity_code =sch.legal_entity_code "
					+ " and e.legal_entity_code =c.legal_entity_code and e.u_criticality=c.criticality_code and c.criticality_of_type='Entity') as uCriticality, "
					
					+ " (Select t.user_id from  audit_team t where t.audit_id=sch.audit_id and t.legal_entity_code =sch.legal_entity_code and t.user_type='L') as teamLead, "
					
					+" duration,"  
					+" frequency,"  
					
					+ " (Select concat(sch.audit_status , '-' , g.description) from  general_parameter g where g.legal_entity_code =sch.legal_entity_code and g.key_1='STATUS' "
					+ " and g.key_2='AUDIT_STATUS' and g.value=sch.audit_status) as auditStatus, "
					
					+ " (Select concat(u.user_id , '-' ,ifnull(u.first_name,''), ' ',ifnull(u.middle_name,''),' ',ifnull(u.last_name,'')) from  user u where u.legal_entity_code =sch.legal_entity_code "
					+ " and u.user_id=sch.maker) as makers, "
					
					+" 0 as z1, 0 as z2 , 0 as z3 , "  // No need to calculate responseReject, Compliance, NA
					
					+ " (Select g.value from general_parameter g where g.legal_entity_code =sch.legal_entity_code and g.key_1='AUDIT_EARLY_START' "
					+ " and g.key_2='EARLY_START_ALLOWED') as earlyStartAllowed,"
					
					+ " (Select g.value from general_parameter g where g.legal_entity_code =sch.legal_entity_code and"
					+ "  g.key_1='AUDIT_OFFSET_DAYS' and g.key_2='AUDIT_OFFSET_DAYS') as auditOffsetDays, " 
					
					+ " (Select concat(sch.audit_status , '-' , g.description) from  general_parameter g where g.legal_entity_code =sch.legal_entity_code "
					+ " and g.key_1='STATUS' and g.key_2='AUDIT_STATUS' and g.value=sch.observation_status) as auditStatusTemp, " // This result to stamp in Audit status in the Observation status is AE i.e Audit Reported
					
					+ " (select group_concat(t.user_id) from audit_team t where t.legal_entity_code=sch.legal_entity_code "
					+ " AND  t.audit_id=sch.audit_id AND t.user_type='M' ) AS teamMember, "
					
					+"    sch.report_status  as reportStatus, " // Report review status added for BaaSDOS implementation 
					
					+ "  (Select concat(sch.report_status , '-' , g.description) from  general_parameter g where g.legal_entity_code =sch.legal_entity_code "
					+ " and g.key_1='STATUS' and g.key_2='REPORT_STATUS' and g.value=sch.report_status) as reportStatusDesc, " // Report review status desc added for BaaSDOS implementation 
									
				//	+ " (Select concat(sch.status , '-' , g.description) from  general_parameter g where g.legal_entity_code =sch.legal_entity_code "
				//	+ " and g.key_1='STATUS' and g.key_2='STATUS' and g.value=sch.status) as authStatus, "  
				
					+ "  concat('A' , '-' , 'Authorised')   as ratingStatus, " // Default all will be auth in AuditSchedule wile compliance rating
					
				
					+ " actual_start_date as actualStartDate, "
					+ " actual_end_date as actualEndDate, "
					+ " schedule_start_date as scheduleStartDate, "
					+ " schedule_end_date as scheduleEndDate, "
					+ " last_audit_date as lastAuditDate, "
					
					+ "  (Select concat(sch.respond_status , '-' , g.description) from  general_parameter g where g.legal_entity_code =sch.legal_entity_code "
					+ " and g.key_1='STATUS' and g.key_2='AUDIT_STATUS' and g.value=sch.respond_status) as respondStatus,  "  // 28
					
					+ " 'master' as isForRatingWorkTmp, "		// 29
					+ "  (Select g.description from  general_parameter g where g.legal_entity_code =sch.legal_entity_code "
					+ "  	and g.key_1='STATUS' and g.key_2='AUDIT_STATUS' and g.value=sch.review_status) as reviewStatusDesc,  "  // 30
					
					+ " sch.observation_status as observationStatusCode, " // 31
					+ " sch.audit_status as auditStatusCode, "  // 32
					+ " (Select  g.description from  general_parameter g where g.legal_entity_code =sch.legal_entity_code and g.key_1='STATUS' "
					+ " 	and g.key_2='AUDIT_STATUS' and g.value=sch.audit_status) as auditStatusDesc, "  // 33
					+ " sch.unit_id   as unitIdSearch,  " // 34
					
					+ " (Select concat(sch.rating_compliance_status, '-' , g.description) from  general_parameter g where g.legal_entity_code =sch.legal_entity_code "
					+ "   and g.key_1='STATUS' and g.key_2='RATING_COMPLIANCE_STATUS' and g.value=sch.rating_compliance_status) as ratingComplianceStatus ,  " // 35
					
					+ " (Select concat(schPrev.rating_compliance_status, '-' , g.description) from  general_parameter g, audit_schedule schPrev where g.legal_entity_code =schPrev.legal_entity_code "
					+ "   and g.key_1='STATUS' and g.key_2='RATING_COMPLIANCE_STATUS' and g.value=schPrev.rating_compliance_status and schPrev.audit_id=sch.prev_audit_id) as ratingComplianceStatusPrev ,  " // 36
					
					+ " (Select schPrev.rating_compliance_auditor_assigned from audit_schedule schPrev where sch.legal_entity_code =schPrev.legal_entity_code "
					+ "   and schPrev.audit_id=sch.prev_audit_id) as ratingComplianceAuditorAssignedPrevAuditId ,  " // 37
					
					+ " sch.prev_audit_id " // 38
					
					+ " from audit_schedule sch where legal_entity_code =:legalEntityCode ");
					/*if(role.equalsIgnoreCase("G2") || role.equalsIgnoreCase("G8")){
						queryString.append( " and sch.prev_audit_id is not null " );
					}*/
					queryString.append( " AND ( IFNULL(audit_status,'NotNull') IN ('AI','AS','AR','AW','AC') ");
					if(role.equalsIgnoreCase("G2") || role.equalsIgnoreCase("G8")){
						queryString.append( " and  (sch.audit_id IN (SELECT t.audit_id FROM audit_team t WHERE t.audit_id=sch.audit_id  "
						+ "  and t.legal_entity_code =sch.legal_entity_code and t.user_id=:userId  ) )");  // Condition for Inspector to view
					}else if(roleId != null && (roleId.equalsIgnoreCase("G1") || roleId.equalsIgnoreCase("G6") 
							// || roleId.equalsIgnoreCase("G2") || roleId.equalsIgnoreCase("G8") 
							)) {
						queryString.append(" OR (sch.unit_id IN (:maintEntities) AND  IFNULL(audit_status,'NotNull') IN ('AW','AC')) "); // Condition for Operations and Management user
					}else if(roleId != null && (roleId.equalsIgnoreCase("G4") || roleId.equalsIgnoreCase("G7") )) {
						//queryString.append(" OR ( IFNULL(review_status,'NotNull') IN ('WI') AND reviewer_id=:userId ) "); // Condition of the Reviewer mapped to the inspection
						queryString.append(" AND ( IFNULL(review_status,'NotNull') IN ('WI','WS') AND sch.unit_id IN (:maintEntities) ) "); // Condition of the Inspector mapped to the inspection
					}else if(roleId != null && (roleId.equalsIgnoreCase("G12"))) { // RO user can see the records from RCFU, RCFM, RCFS
						queryString.append(" AND sch.prev_audit_id IN (SELECT audit_id FROM audit_schedule WHERE rating_compliance_status IN ('RCFU','RCFM','RCFS')) ");
						//queryString.append(" AND sch.rating_compliance_status IN ('RCFU','RCFM','RCFS') "); 
					}else if(roleId != null && !(roleId.equalsIgnoreCase("G2") || roleId.equalsIgnoreCase("G8"))){ // Exclude the AIO and PIO for the units
						queryString.append(" OR ( (sch.unit_id IN  (SELECT u.unit_code FROM user u WHERE u.unit_code=sch.unit_id and  u.legal_entity_code =sch.legal_entity_code"
								+ "  				and u.user_id=:userId) ) "
								+ " 		 AND  IFNULL(audit_status,'NotNull') IN ('AW','AC')  ) ");
					} 
					queryString.append(" ) "); 
					if(!isDeptAsRMD) {
						queryString.append(" and  sch.audit_type_code IN (SELECT asgm.audit_type_code FROM maint_entity_audit_subgroup_mapping as asgm "
								+ " WHERE asgm.legal_entity_code=sch.legal_entity_code and asgm.mapping_type='U' and asgm.id =:userId )"); // To show the Assessments to which the Assessor Mapped (Department mapping)
					} 	
					// Financial Year and Audit Type filter added for filtering
					if(!BankAuditUtil.isEmptyString(auditTypeCode)) {
						queryString.append(" AND  sch.audit_type_code =:auditTypeCode ");
					}if(financialYear !=null) {
						queryString.append(" AND (sch.schedule_start_date between '"+fYStartDt+"' AND '"+fYearEndDt+"' )");
					}
					
					queryString.append(" UNION ALL");
					
					queryString.append(" "
							+ " select legal_entity_code, " 
							+ " audit_type_code," 
							
							+ "(select concat(audit_type_code , '-' , audit_type_desc) from maint_audit_type_desc at where at.audit_type_code=sch.audit_type_code"
							+ "  and at.legal_entity_code= sch.legal_entity_code ) as auditTypeCode,"
							
							+ "plan_id ,audit_id, unit_id as unitId, "
							+ " (Select unit_name from  maint_entity e where e.unit_code =sch.unit_id and e.legal_entity_code =sch.legal_entity_code ) as unitName,  "
							
							+ " (Select g.description from  maint_entity e, general_parameter g where e.unit_code =sch.unit_id and e.legal_entity_code =sch.legal_entity_code "
							+ " and e.legal_entity_code =g.legal_entity_code and g.key_1='UNIT_TYPE' and g.key_2='UNIT_TYPE' and g.value=e.unit_type) as unitType, "
							
							+ " (Select c.criticality_desc from  maint_entity e,maint_criticality c where e.unit_code =sch.unit_id and e.legal_entity_code =sch.legal_entity_code "
							+ " and e.legal_entity_code =c.legal_entity_code and e.u_criticality=c.criticality_code and c.criticality_of_type='Entity') as uCriticality, "
							
							+ " (Select t.user_id from  audit_team t where t.audit_id=sch.audit_id and t.legal_entity_code =sch.legal_entity_code and t.user_type='L') as teamLead, "
							
							+" duration,"  
							+" frequency,"  
							
							+ " (Select concat(sch.audit_status , '-' , g.description) from  general_parameter g where g.legal_entity_code =sch.legal_entity_code and g.key_1='STATUS' "
							+ " and g.key_2='AUDIT_STATUS' and g.value=sch.audit_status) as auditStatus, "
							
							+ " (Select concat(u.user_id , '-' ,ifnull(u.first_name,''), ' ',ifnull(u.middle_name,''),' ',ifnull(u.last_name,'')) "
							+ " from  user u where u.legal_entity_code =sch.legal_entity_code  and u.user_id=sch.rating_maker) as makers, "
							
							+" 0 as z1, 0 as z2 , 0 as z3 , "  // No need to calculate responseReject, Compliance, NA
							
							+ " (Select g.value from general_parameter g where g.legal_entity_code =sch.legal_entity_code and g.key_1='AUDIT_EARLY_START' "
							+ " and g.key_2='EARLY_START_ALLOWED') as earlyStartAllowed, "
							
							+ " (Select g.value from general_parameter g where g.legal_entity_code =sch.legal_entity_code and"
							+ "  g.key_1='AUDIT_OFFSET_DAYS' and g.key_2='AUDIT_OFFSET_DAYS') as auditOffsetDays, " 
							
							+ " (Select concat(sch.audit_status , '-' , g.description) from  general_parameter g where g.legal_entity_code =sch.legal_entity_code "
							+ " and g.key_1='STATUS' and g.key_2='AUDIT_STATUS' and g.value=sch.observation_status) as auditStatusTemp, " // This result to stamp in Audit status in the Observation status is AE i.e Audit Reported
							
							+ " (select group_concat(t.user_id) from audit_team t where t.legal_entity_code=sch.legal_entity_code "
							+ " AND  t.audit_id=sch.audit_id AND t.user_type='M' ) AS teamMember, "
							
							+"    sch.report_status  as reportStatus, " // Report review status added for BaaSDOS implementation 
							
							+ "  (Select concat(sch.report_status , '-' , g.description) from  general_parameter g where g.legal_entity_code =sch.legal_entity_code "
							+ " and g.key_1='STATUS' and g.key_2='REPORT_STATUS' and g.value=sch.report_status) as reportStatusDesc, " // Report review status desc added for BaaSDOS implementation 
											
							/*+ " (Select concat(sch.rating_status , '-' , g.description) from  general_parameter g where g.legal_entity_code =sch.legal_entity_code "
							+ " and g.key_1='STATUS' and g.key_2='STATUS' and g.value=sch.status) as ratingStatus, "  */
							+ " concat('M' , '-' , 'Modified')   as ratingStatus,  "  
														
							+ " sch.actual_start_date as actualStartDate, "
							+ " sch.actual_end_date as actualEndDate, "
							+ " sch.schedule_start_date as scheduleStartDate, "
							+ " sch.schedule_end_date as scheduleEndDate, "
							+ " sch.last_audit_date as lastAuditDate, "
							
							+ " (Select concat(sch.respond_status , '-' , g.description) from  general_parameter g where g.legal_entity_code =sch.legal_entity_code "
							+ "   and g.key_1='STATUS' and g.key_2='AUDIT_STATUS' and g.value=sch.respond_status) as respondStatus,  "

							+ " 'wrk_tmp' as isForRatingWorkTmp, "
							+ " (Select g.description from  general_parameter g where g.legal_entity_code =sch.legal_entity_code "
							+ "   and g.key_1='STATUS' and g.key_2='AUDIT_STATUS' and g.value=sch.review_status) as reviewStatusDesc,  "
							
							+" sch.observation_status as observationStatusCode, "
							+" sch.audit_status as auditStatusCode, "
							+ " (Select  g.description from  general_parameter g where g.legal_entity_code =sch.legal_entity_code and g.key_1='STATUS' "
							+ " and g.key_2='AUDIT_STATUS' and g.value=sch.audit_status) as auditStatusDesc, "
							+ " sch.unit_id   as unitIdSearch, "
							
							+ " (Select concat(sch.rating_compliance_status , '-' , g.description) from  general_parameter g where g.legal_entity_code =sch.legal_entity_code "
							+ " and g.key_1='STATUS' and g.key_2='RATING_COMPLIANCE_STATUS' and g.value=sch.rating_compliance_status) as ratingComplianceStatus ,  " // 35
							
							+ " (Select concat(schPrev.rating_compliance_status, '-' , g.description) from  general_parameter g, audit_schedule schPrev where g.legal_entity_code =schPrev.legal_entity_code "
							+ "   and g.key_1='STATUS' and g.key_2='RATING_COMPLIANCE_STATUS' and g.value=schPrev.rating_compliance_status and schPrev.audit_id=sch.prev_audit_id) as ratingComplianceStatusPrev , " // 36
							
							+ " (Select schPrev.rating_compliance_auditor_assigned from audit_schedule schPrev where sch.legal_entity_code =schPrev.legal_entity_code "
							+ "   and schPrev.audit_id=sch.prev_audit_id) as ratingComplianceAuditorAssignedPrevAuditId ,  " // 37
							
							+ " sch.prev_audit_id " // 38

							+ " from audit_schedule_wrk_tmp sch where legal_entity_code =:legalEntityCode " );
							/*if(role.equalsIgnoreCase("G2") || role.equalsIgnoreCase("G8")){
								queryString.append( " and sch.prev_audit_id is not null " );
							}*/
							queryString.append( " and  ( (sch.audit_id IN (SELECT t.audit_id FROM audit_team t WHERE t.audit_id=sch.audit_id and t.legal_entity_code =sch.legal_entity_code and t.user_id=:userId  ) "
							+ "         AND IFNULL(audit_status,'NotNull') IN ('AP','AI','AS','AR') )" );  // Condition for Inspector to view
							if(roleId != null && (roleId.equalsIgnoreCase("G1") || roleId.equalsIgnoreCase("G6") 
									|| roleId.equalsIgnoreCase("G2") || roleId.equalsIgnoreCase("G8"))) {
								queryString.append(" OR (sch.unit_id IN (:maintEntities) AND  IFNULL(audit_status,'NotNull') IN ('AW','AC')) "); // Condition for Operations and Management user
							}else if(roleId != null && (roleId.equalsIgnoreCase("G4") || roleId.equalsIgnoreCase("G7"))) {
								//queryString.append(" OR ( IFNULL(review_status,'NotNull') IN ('WI') AND reviewer_id=:userId ) "); // Condition of the Reviewer mapped to the inspection
								queryString.append(" OR ( IFNULL(review_status,'NotNull') IN ('WI') AND sch.unit_id IN (:maintEntities) ) "); // Condition of the Reviewer mapped to the inspection
							}else if(roleId != null && (roleId.equalsIgnoreCase("G12"))) { // RO user can see the records from RCFU, RCFM, RCFS
								queryString.append(" AND sch.rating_compliance_status IN ('RCFU','RCFM','RCFS') "); 
							}else {
								queryString.append(" OR ( (sch.unit_id IN  (SELECT u.unit_code FROM user u WHERE u.unit_code=sch.unit_id and  u.legal_entity_code =sch.legal_entity_code"
										+ "  				and u.user_id=:userId) ) "
										+ " 		 AND  IFNULL(audit_status,'NotNull') IN ('AW','AC')  ) ");
							}
							queryString.append(" ) "); 
							if(!isDeptAsRMD) {
								queryString.append(" and  sch.audit_type_code IN (SELECT asgm.audit_type_code FROM maint_entity_audit_subgroup_mapping as asgm "
										+ " WHERE asgm.legal_entity_code=sch.legal_entity_code and asgm.mapping_type='U' and asgm.id =:userId )"); // To show the Assessments to which the Assessor Mapped (Department mapping)
							} 
							
							//Get unAuth documnet
							/*queryString.append(" UNION ALL");
							queryString.append( " select schDoc.legal_entity_code, " 
									+ " schDoc.audit_type_code," 
									
									+ "(select concat(audit_type_code , '-' , audit_type_desc) from maint_audit_type_desc at where at.audit_type_code=schDoc.audit_type_code"
									+ "  and at.legal_entity_code= schDoc.legal_entity_code ) as auditTypeCode,"
									
									+ " schDoc.plan_id , schDoc.audit_id, "
									+ " (Select concat(unit_code , '-' , unit_name) from  maint_entity e where e.unit_code =schDoc.unit_id and e.legal_entity_code =schDoc.legal_entity_code ) as unitId,  "
									
									+ " (Select g.description from  maint_entity e, general_parameter g where e.unit_code =schDoc.unit_id and e.legal_entity_code =schDoc.legal_entity_code "
									+ " and e.legal_entity_code =g.legal_entity_code and g.key_1='UNIT_TYPE' and g.key_2='UNIT_TYPE' and g.value=e.unit_type) as unitType, "
									
									+ " (Select c.criticality_desc from  maint_entity e,maint_criticality c where e.unit_code =schDoc.unit_id and e.legal_entity_code =schDoc.legal_entity_code "
									+ " and e.legal_entity_code =c.legal_entity_code and e.u_criticality=c.criticality_code and c.criticality_of_type='Entity') as uCriticality, "
									
									+ " (Select t.user_id from  audit_team t where t.audit_id=schDoc.audit_id and t.legal_entity_code =schDoc.legal_entity_code and t.user_type='L') as teamLead, "
									
									+" duration,"  
									+" frequency,"  
									
									+ " (Select concat(schDoc.audit_status , '-' , g.description) from  general_parameter g where g.legal_entity_code =schDoc.legal_entity_code and g.key_1='STATUS' "
									+ " and g.key_2='AUDIT_STATUS' and g.value=schDoc.audit_status) as auditStatus, "
									
									+ " (Select concat(u.user_id , '-' ,ifnull(u.first_name,''), ' ',ifnull(u.middle_name,''),' ',ifnull(u.last_name,'')) from  user u where u.legal_entity_code =schDoc.legal_entity_code "
									+ " and u.user_id=schDoc.maker) as makers, "
									
									+" 0 as z1, 0 as z2 , 0 as z3 , "  // No need to calculate responseReject, Compliance, NA
									
									+ " (Select g.value from general_parameter g where g.legal_entity_code =schDoc.legal_entity_code and g.key_1='AUDIT_EARLY_START' "
									+ " and g.key_2='EARLY_START_ALLOWED') as earlyStartAllowed,"
									
									+ " (Select g.value from general_parameter g where g.legal_entity_code =schDoc.legal_entity_code and"
									+ "  g.key_1='AUDIT_OFFSET_DAYS' and g.key_2='AUDIT_OFFSET_DAYS') as auditOffsetDays, " 
									
									+ " (Select concat(schDoc.audit_status , '-' , g.description) from  general_parameter g where g.legal_entity_code =schDoc.legal_entity_code "
									+ " and g.key_1='STATUS' and g.key_2='AUDIT_STATUS' and g.value=schDoc.observation_status) as auditStatusTemp, " // This result to stamp in Audit status in the Observation status is AE i.e Audit Reported
									
									+ " (select group_concat(t.user_id) from audit_team t where t.legal_entity_code=schDoc.legal_entity_code "
									+ " AND  t.audit_id=schDoc.audit_id AND t.user_type='M' ) AS teamMember, "
									
									+"  schDoc.report_status  as reportStatus, " // Report review status added for BaaSDOS implementation 
									
									+ "  (Select concat(schDoc.report_status , '-' , g.description) from  general_parameter g where g.legal_entity_code =schDoc.legal_entity_code "
									+ " and g.key_1='STATUS' and g.key_2='REPORT_STATUS' and g.value=schDoc.report_status) as reportStatusDesc, " // Report review status desc added for BaaSDOS implementation 
 
									+ "  concat('M' , '-' , 'Modified')   as ratingStatus, " // Default all will be auth in AuditSchedule wile compliance rating
									
								
									+ " actual_start_date as actualStartDate, "
									+ " actual_end_date as actualEndDate, "
									+ " schedule_start_date as scheduleStartDate, "
									+ " schedule_end_date as scheduleEndDate, "
									+ " last_audit_date as lastAuditDate, "
									
									+ "  (Select concat(schDoc.respond_status , '-' , g.description) from  general_parameter g where g.legal_entity_code =schDoc.legal_entity_code "
									+ " and g.key_1='STATUS' and g.key_2='AUDIT_STATUS' and g.value=schDoc.respond_status) as respondStatus,  "
									
									+ " 'master' as isForRatingWorkTmp, "				
									+ "  (Select g.description from  general_parameter g where g.legal_entity_code =schDoc.legal_entity_code "
									+ "  	and g.key_1='STATUS' and g.key_2='AUDIT_STATUS' and g.value=schDoc.review_status) as reviewStatusDesc,  "
									
									+" schDoc.observation_status as observationStatusCode, "
									+" schDoc.audit_status as auditStatusCode, "
									+ " (Select  g.description from  general_parameter g where g.legal_entity_code =schDoc.legal_entity_code and g.key_1='STATUS' "
									+ " and g.key_2='AUDIT_STATUS' and g.value=schDoc.audit_status) as auditStatusDesc, "
									+ " schDoc.unit_id   as unitIdSearch  "
									
									+ " from audit_schedule schDoc  ");
									 
									queryString.append(" Where  schDoc.legal_entity_code =:legalEntityCode  and "
											+ " concat(audit_type_code, audit_id, plan_id)   in  "
											+ " ( Select concat(audit_type_code,audit_id,plan_id) from audit_document_details_wrk docWrk  "
													+ "	Where schDoc.legal_entity_code = docWrk.legal_entity_code "											
											+ " and docWrk.document_type =:documentType "
											+ " and docWrk.document_sub_type =:documentSubType "
											+ " and concat(docWrk.audit_type_code,docWrk.audit_id,docWrk.plan_id) not in  "
											+ " ( Select concat(audit_type_code,audit_id,plan_id) from audit_schedule_wrk_tmp schtTempDoc "
													+ "  Where schDoc.legal_entity_code=schtTempDoc.legal_entity_code ) )  "
											 
											+ " ");
							
									queryString.append( " and  ( (schDoc.audit_id IN (SELECT t.audit_id FROM audit_team t WHERE t.audit_id=schDoc.audit_id and t.legal_entity_code =schDoc.legal_entity_code and t.user_id=:userId  ) "
									+ "         AND IFNULL(audit_status,'NotNull') IN ('AP','AI','AS','AR') )" );  // Condition for Inspector to view
									if(roleId != null && (roleId.equalsIgnoreCase("G1") || roleId.equalsIgnoreCase("G6") 
											|| roleId.equalsIgnoreCase("G2") || roleId.equalsIgnoreCase("G8") )) {
										queryString.append(" OR (schDoc.unit_id IN (:maintEntities) AND  IFNULL(audit_status,'NotNull') IN ('AW','AC')) "); // Condition for Operations and Management user
									}else if(roleId != null && (roleId.equalsIgnoreCase("G4") || roleId.equalsIgnoreCase("G7") )) {
										//queryString.append(" OR ( IFNULL(review_status,'NotNull') IN ('WI') AND reviewer_id=:userId ) "); // Condition of the Reviewer mapped to the inspection
										queryString.append(" OR ( IFNULL(review_status,'NotNull') IN ('WI') AND schDoc.unit_id IN (:maintEntities) ) "); // Condition of the Inspector mapped to the inspection
									} 
									else {
										queryString.append(" OR ( (schDoc.unit_id IN  (SELECT u.unit_code FROM user u WHERE u.unit_code=schDoc.unit_id and  u.legal_entity_code =schDoc.legal_entity_code"
												+ "  				and u.user_id=:userId) ) "
												+ " 		 AND  IFNULL(audit_status,'NotNull') IN ('AW','AC')  ) ");
									}
									queryString.append(" ) "); 
									if(!isDeptAsRMD) {
										queryString.append(" and  schDoc.audit_type_code IN (SELECT asgm.audit_type_code FROM maint_entity_audit_subgroup_mapping as asgm "
												+ " WHERE asgm.legal_entity_code=schDoc.legal_entity_code and asgm.mapping_type='U' and asgm.id =:userId )"); // To show the Assessments to which the Assessor Mapped (Department mapping)
									} 	*/
							
							// Financial Year and Audit Type filter added for filtering
							if(!BankAuditUtil.isEmptyString(auditTypeCode)) {
								queryString.append(" AND  sch.audit_type_code =:auditTypeCode ");
							}if(financialYear !=null) {
								queryString.append(" AND (sch.schedule_start_date between '"+fYStartDt+"' AND '"+fYearEndDt+"' )");
							}
							
							queryString.append( " ) schUnion ");
							
					
			logger.info("Before the qry....");
			
			if(!BankAuditUtil.isEmptyString(search)){
				 
				queryString=queryString.append(" where ( "
						+ " schUnion.audit_type_code IN (SELECT a.audit_type_code FROM maint_audit_type_desc a  WHERE a.legal_entity_code=schUnion.legal_entity_code AND"
						+ "  (a.audit_type_desc LIKE :search OR a.audit_type_code LIKE :search)) "
						+ " or plan_id like :search "
						+ " or audit_id like :search " 
						
						/*+ " or schUnion.unitId IN (SELECT m.unit_code FROM maint_entity m WHERE m.legal_entity_code=schUnion.legal_entity_code "
						+ " AND (m.unit_name LIKE :search or m.unit_code LIKE :search )) "*/
						+ " or schUnion.unitIdSearch IN (SELECT m.unit_code FROM maint_entity m, general_parameter g WHERE schUnion.unitIdSearch=m.unit_code "
						+ " and m.legal_entity_code=schUnion.legal_entity_code "						 
						+ " AND m.legal_entity_code =g.legal_entity_code and g.key_1='UNIT_TYPE' and g.key_2='UNIT_TYPE'"
						+ " and g.value=m.unit_type and (g.description LIKE :search or m.unit_name LIKE :search or m.unit_code LIKE :search) ) "
						
						+ " or schUnion.ratingStatus IN (Select concat(g.value , '-' , g.description) from  general_parameter g"
						+ " where g.legal_entity_code =schUnion.legal_entity_code   and g.key_1='STATUS' and g.key_2='STATUS' "
						+ " and ( g.value  LIKE :search or  g.description  LIKE :search )) "
						
						+ " or schUnion.unitId IN (SELECT m.unit_code FROM maint_entity m, maint_criticality c WHERE schUnion.unitId=m.unit_Code and m.legal_entity_code=schUnion.legal_entity_code "
						+ " AND m.legal_entity_code =c.legal_entity_code and m.u_criticality=c.criticality_Code and c.criticality_of_type='Entity' and  c.criticality_Desc LIKE :search) "
						+ " or schUnion.unitId IN (SELECT m.unit_Code FROM maint_entity m, maint_audit_parameter p WHERE schUnion.unitId=m.unit_Code "
						+ " and m.legal_entity_code=schUnion.legal_entity_code AND m.legal_entity_code =p.legal_entity_code and m.u_Criticality=p.u_Criticality "
						+ " and schUnion.auditTypeCode=p.audit_Type_Code and  p.duration LIKE :search) "
						+ " or schUnion.audit_id IN (SELECT t.audit_Id FROM user u,audit_team t WHERE t.audit_Id= schUnion.audit_Id and t.legal_entity_code =schUnion.legal_entity_code "
						+ " and t.legal_Entity_Code =u.legal_entity_code and t.user_Id=u.user_Id and (t.user_Id Like :search or u.first_Name LIKE :search or u.last_Name Like :search) ) "
						+ " or DATE_FORMAT(schUnion.scheduleStartDate, '%d %b %Y') LIKE :search "
						+ " or DATE_FORMAT(schUnion.scheduleEndDate, '%d %b %Y') LIKE :search "
						+ " or DATE_FORMAT(schUnion.actualStartDate, '%d %b %Y') LIKE :search "
						+ " or DATE_FORMAT(schUnion.actualEndDate, '%d %b %Y') LIKE :search "
						+ " or DATE_FORMAT(schUnion.lastAuditDate, '%d %b %Y') LIKE :search "
						+ " or schUnion.makers IN (SELECT u.user_Id FROM user u WHERE u.legal_entity_code =schUnion.legal_entity_code and (u.user_Id Like :search or "
						+ " u.first_Name LIKE :search or u.last_Name Like :search) ) "
						+ " or schUnion.auditStatus IN (SELECT g.value FROM general_parameter g WHERE schUnion.legal_entity_code =g.legal_entity_code and g.key_1='STATUS' "
						+ " and g.key_2='AUDIT_STATUS' and g.value=schUnion.auditStatus and g.description LIKE :search) "
						+ " or schUnion.reportStatus IN (SELECT g.value FROM general_parameter g WHERE schUnion.legal_entity_code =g.legal_entity_code and g.key_1='STATUS' "
						+ " and g.key_2='REPORT_STATUS' and g.value=schUnion.reportStatus and g.description LIKE :search) "
						// + " or teamMember like :search "
						+ " or schUnion.audit_Id IN (select t.audit_Id from audit_team t, user u where t.legal_entity_code=schUnion.legal_entity_code AND t.audit_Id=schUnion.audit_Id "
						+ " AND t.user_Type='M' AND t.legal_entity_code=u.legal_entity_code AND t.user_Id=u.user_Id "
						+ "						AND schUnion.unitId=u.unit_Code AND (u.first_Name like :search OR u.middle_Name like :search OR u.last_Name like :search "
						+ " OR u.user_Id like :search )) "
						
						+ " or schUnion.auditStatusCode IN (SELECT g.value FROM general_parameter g WHERE schUnion.legal_entity_code =g.legal_entity_code and g.key_1='STATUS' "
						+ " and g.key_2='AUDIT_STATUS' and g.value=schUnion.auditStatusCode "
						+ " and (g.description LIKE :search or g.value LIKE :search)"
						+ " and   ( schUnion.observationStatusCode  ='AE' and schUnion.auditStatusCode='AW'  ) "
						+ " ) "
						
						+ " or schUnion.auditStatusCode IN (SELECT g.value FROM general_parameter g WHERE schUnion.legal_entity_code =g.legal_entity_code and g.key_1='STATUS' "
						+ " and g.key_2='AUDIT_STATUS' and g.value=schUnion.auditStatusCode "
						+ " and (g.description LIKE :search or g.value LIKE :search)"
						+ " and   ( schUnion.observationStatusCode  ='AE' and schUnion.auditStatusCode='AR'  ) "
						+ " ) "	
						
						+ " or schUnion.auditStatusCode IN (SELECT g.value FROM general_parameter g WHERE schUnion.legal_entity_code =g.legal_entity_code and g.key_1='STATUS' "
						+ " and g.key_2='AUDIT_STATUS' and g.value=schUnion.auditStatusCode "
						+ " and (g.description LIKE :search or g.value LIKE :search)"
						+ " and   ( (schUnion.observationStatusCode  ='AE' OR schUnion.observationStatusCode  ='AS' ) and schUnion.auditStatusCode='AS'  ) "
						+ " ) "	
						
						+ " or schUnion.auditStatusCode IN (SELECT g.value FROM general_parameter g WHERE schUnion.legal_entity_code =g.legal_entity_code and g.key_1='STATUS' "
						+ " and g.key_2='AUDIT_STATUS' and g.value=schUnion.auditStatusCode "
						+ " and (g.description LIKE :search or g.value LIKE :search)"
						+ " and   ( schUnion.observationStatusCode !='AE'  or schUnion.observationStatusCode is null)  "
						+ " ) "
						
						);
				
				if(roleId.equalsIgnoreCase("G2") || roleId.equalsIgnoreCase("G8")  || roleId.equalsIgnoreCase("G12")) {
					queryString.append(" Or ( schUnion.ratingComplianceStatusPrev IN (SELECT g.value FROM general_parameter g WHERE schUnion.legal_entity_code =g.legal_entity_code and g.key_1='STATUS' "
						+ " and g.key_2='RATING_COMPLIANCE_STATUS' and g.value=schUnion.reportStatus and g.description LIKE :search) )");
				}else {
					queryString.append(" Or ( schUnion.ratingComplianceStatus IN (SELECT g.value FROM general_parameter g WHERE schUnion.legal_entity_code =g.legal_entity_code and g.key_1='STATUS' "
								+ " and g.key_2='RATING_COMPLIANCE_STATUS' and g.value=schUnion.reportStatus and g.description LIKE :search) )");
				}
						
				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 ( schUnion.auditStatusCode='AI' and schUnion.observationStatusCode='AE' )");
				}
				
				queryString.append(" )" );
						 	 
			}
		//queryString=queryString.append("  ORDER BY FIELD(auditStatus, 'AI','P','AP','AS','AC','AU') asc "); 
		String[] columns={
				"auditTypeCode",
				"(audit_id *1 ), (plan_id *1 )", //"CAST(planId AS Integer),CAST(auditId AS Integer) "
				"unitType",
				"(unitId * 1) ",  //"unitId",
				"uCriticality",
				"duration", 						
				"lastAuditDate", //last audit date actually 
				//"schUnion.scheduleStartDate",
				//"schUnion.scheduleEndDate",
				"schUnion.actualStartDate",
				"schUnion.actualEndDate",
				"schUnion.teamLead", // Team Lead 
				"schUnion.makers",
				"schUnion.reportStatus",
				"schUnion.auditStatus",
				"schUnion.ratingComplianceStatus",
				"unitName",  
				"financialYear"
		};

		//if(orderColumn!=null
		//		&& !BankAuditUtil.isEmptyString(orderDirection)){
		//	queryString=queryString.append(" order by ").append(columns[orderColumn]).append(" ").append(orderDirection);
		//}
		System.out.println("orderColumn **.... "+ orderColumn +"  orderDirection ...."+ orderDirection );
		if(orderColumn != null && orderColumn == -1 && !BankAuditUtil.isEmptyString(orderDirection)){ // -1 send for the first time from the UI during landing page.
			//queryString=queryString.append(" ORDER BY FIELD(auditStatus, 'AI','AP','AS','AR','AC') asc  ");
			queryString=queryString.append(" ORDER BY schUnion.ratingComplianceStatus Desc "); // get the record based on modified record on top
		}else if(orderColumn!=null && !BankAuditUtil.isEmptyString(orderDirection)){
			if(orderColumn==0) {
				queryString=queryString.append(" ORDER BY  ").append(columns[orderColumn]).append(" ").append(orderDirection).append(" , FIELD(auditStatus, 'AI','AP','AS','AR','AC') asc ,(audit_id *1 ) asc  ");
			}else {
				queryString=queryString.append(" ORDER BY  ").append(columns[orderColumn]).append(" ").append(orderDirection);
			}
		}else {
			queryString=queryString.append(" ORDER BY schUnion.ratingComplianceStatus Desc  ");
		}
		
		logger.info("queryString .... "+ queryString.toString());
		
		Query query=(Query) session.createSQLQuery(queryString.toString())				
				.setResultTransformer(new ResultTransformer() {
					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						AuditSchedule auditSchedule=null; 						
						
						if(tuple[0]!=null){
							//auditSchedule=(AuditSchedule)tuple[0]; 
							auditSchedule = new AuditSchedule();
							
							auditSchedule.setAuditTypeCode(tuple[0].toString());
							
							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());
								//String unitCodeName=tuple[5].toString();
								//auditSchedule.setUnitId(unitCodeName);
								//auditSchedule.setUnitName(unitCodeName.substring(unitCodeName.indexOf('-')+1));
															
								auditSchedule.setUnitId(tuple[5].toString()+"-"+tuple[39].toString());
								auditSchedule.setUnitName(tuple[39].toString());
							}
							if(tuple[6]!=null){
								auditSchedule.setUnitType(tuple[6].toString());
							}
							if(tuple[7]!=null){
								auditSchedule.setuCriticality(tuple[7].toString());
							}
							if(tuple[8]!=null){
								//auditSchedule.setTeamLead(tuple[8].toString()+"-"+userDtls.get(tuple[8].toString()));
								//auditSchedule.setTeamLead(userDtls.get(tuple[8].toString()));
								String teamLead = userDtls.get(tuple[8].toString());
								auditSchedule.setTeamLead(teamLead.equalsIgnoreCase("Me")?"Me":tuple[8].toString()+"-"+teamLead);
							}
							if(tuple[9]!=null){
								auditSchedule.setDuration(tuple[9].toString());
							}
							if(tuple[10]!=null){
								auditSchedule.setFrequency(tuple[10].toString());
							}
							if(tuple[11]!=null){
								if("P".equalsIgnoreCase(tuple[11].toString()))auditSchedule.setAuditStatus("AP"); // temp purpose as in few earlier records the Planned status is reflecting as "P"
								else if("AE".equalsIgnoreCase(auditSchedule.getObservationStatus())) auditSchedule.setAuditStatus(tuple[18] !=null ?tuple[18].toString() :tuple[11].toString() );
								else auditSchedule.setAuditStatus(tuple[11].toString());
							}
							if(tuple[12]!=null){
								auditSchedule.setMaker(tuple[12].toString());
							}
							if(tuple[13]!=null && auditSchedule.getRespondedFindings() != null){
								auditSchedule.setResponseReject(Integer.parseInt(tuple[13].toString()));
								auditSchedule.setRespondedFindings(auditSchedule.getRespondedFindings()- (Integer.parseInt(tuple[13].toString()))); // Included to find out the Responded Observations
							}else auditSchedule.setResponseReject(0);
							
							if(tuple[14]!=null){
								auditSchedule.setComplianceCount(Integer.parseInt(tuple[14].toString()));
							}else auditSchedule.setComplianceCount(0);
						 
							if(tuple[15]!=null){
								auditSchedule.setNotApplicableCount(Integer.parseInt(tuple[15].toString()));
							}else auditSchedule.setNotApplicableCount(0);
							
							if(tuple[16]!=null){
								auditSchedule.setEarlyStartAllowed(tuple[16].toString());
							}else auditSchedule.setEarlyStartAllowed("Y");
							
							if(tuple[17]!=null){
								auditSchedule.setAuditOffsetDays(Integer.parseInt(tuple[17].toString()));
							}else auditSchedule.setAuditOffsetDays(0);
							
							if(tuple[19]!=null){
								String teamMembers="";
								String[] members = tuple[19].toString().split(",");
								for (String member: members) {
									teamMembers+=member+"-"+userDtls.get(member)+",";
								}
								auditSchedule.setTeamMember(teamMembers.substring(0,teamMembers.length()-1));
							}
							
							if(tuple[20]!=null) auditSchedule.setReportStatus(tuple[20].toString());
							if(tuple[21]!=null) auditSchedule.setReportStatusDesc(tuple[21].toString());
							
							if(tuple[22]!=null) {
								auditSchedule.setStatus(tuple[22].toString());
								auditSchedule.setRatingStatus(tuple[22].toString());
							}
							
							if(tuple[28]!=null) auditSchedule.setRespondStatus(tuple[28].toString());
							if(tuple[29]!=null) auditSchedule.setIsForRatingWorkTmp(tuple[29].toString());
							if(tuple[30]!=null) auditSchedule.setReviewStatusDesc(tuple[30].toString());
							if(tuple[35]!=null) { 
								auditSchedule.setRatingComplianceStatus(tuple[35].toString().split("-")[0]);
								auditSchedule.setRatingComplianceStatusDesc(tuple[35].toString().split("-").length>1?tuple[35].toString().split("-")[1]:"");								
							}
							if(tuple[36]!=null) { 
								auditSchedule.setRatingComplianceStatusPrevAuditId(tuple[36].toString().split("-")[0]);
								auditSchedule.setRatingComplianceStatusDescPrevAuditId(tuple[36].toString().split("-").length>1?tuple[36].toString().split("-")[1]:"");
							
							}
							if(tuple[37]!=null) auditSchedule.setRatingComplianceAuditorAssignedPrevAuditId(tuple[37].toString());
							if(tuple[39]!=null) {
								auditSchedule.setUnitName(tuple[39].toString()); 
							}
						}
						return auditSchedule;
					}
			@Override
			public List transformList(List collection) {
				return collection;
			}
		})		
		.setParameter("legalEntityCode",legalEntityCode)
		.setParameter("userId",userId);
		/*.setParameter("documentType", documentType)
		.setParameter("documentSubType", documentSubType)*/
		 //.setParameter("visibleAuditList", visibleAuditList);
 
		if(roleId != null && (roleId.equalsIgnoreCase("G1") || roleId.equalsIgnoreCase("G6") 
				|| roleId.equalsIgnoreCase("G4") || roleId.equalsIgnoreCase("G7") 
				|| roleId.equalsIgnoreCase("G2") || roleId.equalsIgnoreCase("G8")) )
			query.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> auditScheduleList=query.setFirstResult((page*size)).setMaxResults(size).list();
		
		List<AuditSchedule> auditScheduleMappedList= new ArrayList<AuditSchedule>();
		for (AuditSchedule auditSchedule2 : auditScheduleList) {
			
			AuditSchedule auditScheduleToMap = new AuditSchedule();
			auditScheduleToMap = getAuditByPlanIdAndAuditId(legalEntityCode, auditSchedule2.getAuditTypeCode().split("-")[0], 
					auditSchedule2.getPlanId(), auditSchedule2.getAuditId(), auditSchedule2.getIsForRatingWorkTmp());
			 
			if(auditScheduleToMap!=null){
				auditScheduleToMap.setLegalEntityCode(auditSchedule2.getLegalEntityCode());				
				auditScheduleToMap.setAuditTypeCode(auditSchedule2.getAuditTypeCode());			 
				auditScheduleToMap.setPlanId(auditSchedule2.getPlanId());			 
				auditScheduleToMap.setAuditId(auditSchedule2.getAuditId());			 
				auditScheduleToMap.setUnitId(auditSchedule2.getUnitId());		
				auditScheduleToMap.setUnitName(auditSchedule2.getUnitName());	
				auditScheduleToMap.setUnitType(auditSchedule2.getUnitType());			 
				auditScheduleToMap.setuCriticality(auditSchedule2.getuCriticality());			 
				auditScheduleToMap.setTeamLead(auditSchedule2.getTeamLead());			 
				auditScheduleToMap.setDuration(auditSchedule2.getDuration());			 
				auditScheduleToMap.setFrequency(auditSchedule2.getFrequency());			 
				auditScheduleToMap.setAuditStatus(auditSchedule2.getAuditStatus()); 		 
				auditScheduleToMap.setMaker(auditSchedule2.getMaker());
				
				auditScheduleToMap.setResponseReject(auditSchedule2.getResponseReject());
				auditScheduleToMap.setRespondedFindings(auditSchedule2.getRespondedFindings());
				/*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);*/
				
				 
				auditScheduleToMap.setComplianceCount(auditSchedule2.getComplianceCount());			 
				auditScheduleToMap.setNotApplicableCount(auditSchedule2.getNotApplicableCount());		
				auditScheduleToMap.setEarlyStartAllowed(auditSchedule2.getEarlyStartAllowed());			 
				auditScheduleToMap.setAuditOffsetDays(auditSchedule2.getAuditOffsetDays());	 				 
				auditScheduleToMap.setTeamMember(auditSchedule2.getTeamMember());
				 
				auditScheduleToMap.setReportStatus(auditSchedule2.getReportStatus());
				auditScheduleToMap.setReportStatusDesc(auditSchedule2.getReportStatusDesc());
				auditScheduleToMap.setStatus(auditSchedule2.getStatus());
				
				//auditScheduleToMap.setRatingStatus(auditSchedule2.getRatingStatus());
				auditScheduleToMap.setRatingStatus(BankAuditUtil.isEmptyString(auditSchedule2.getRatingStatus())?"U-UnAuthorised":auditSchedule2.getRatingStatus()) ;
				
				auditScheduleToMap.setRespondStatus(auditSchedule2.getRespondStatus());
				if(roleId.equalsIgnoreCase("G2") || roleId.equalsIgnoreCase("G8")  || roleId.equalsIgnoreCase("G12")) {
					auditScheduleToMap.setRatingComplianceStatus(auditSchedule2.getRatingComplianceStatusPrevAuditId());
					auditScheduleToMap.setRatingComplianceStatusDesc(auditSchedule2.getRatingComplianceStatusDescPrevAuditId());
					auditScheduleToMap.setRatingComplianceAuditorAssigned(auditSchedule2.getRatingComplianceAuditorAssignedPrevAuditId());
				}else {
					//auditScheduleToMap.setRatingComplianceStatus(auditSchedule2.getRatingComplianceStatusPrevAuditId());
					//auditScheduleToMap.setRatingComplianceAuditorAssigned(auditSchedule2.getRatingComplianceAuditorAssignedPrevAuditId());
					auditScheduleToMap.setRatingComplianceStatusDesc(auditSchedule2.getRatingComplianceStatusDesc());					
				}
				
				auditScheduleMappedList.add(auditScheduleToMap);
			}
		}
		
		System.out.println("Bfr size tmpAuditSchedule is .... "+tmpAuditSchedule.size()+" totalRecords:: "+ totalRecords);
 
		
		dataTableResponse.setData(auditScheduleMappedList);
		dataTableResponse.setRecordsTotal(totalRecords);
		dataTableResponse.setRecordsFiltered(totalRecords);
		
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		return dataTableResponse;
	
	}
	
	@Override
	public AuditRatingDto getAuditByPlanIdAndAuditIdAndStatus(int legalEntityCode, String auditTypeCode, String planId,
			String auditId, String status, String role) {
		Session session = getSession();
		session.setDefaultReadOnly(true);
		AuditRatingDto auditRatingDto =  new AuditRatingDto();
		auditRatingDto.setAuditId(auditId);
		//auditRatingDto.setPlanId(planId);
		auditRatingDto.setRole(role);
		AuditSchedule auditSchedule = null; 
		AuditScheduleWrkTmp auditScheduleWrkTmp=null;
		try { 
			String preAuditId=null;
			// G12=Review team -> Get previous Inspection Details,G8
			//if(status.equalsIgnoreCase(BankAuditConstant.STATUS_AUTH)){
			if(role.equalsIgnoreCase("G2") || role.equalsIgnoreCase("G8") ){ // G12=Review team
				preAuditId = getPreviousYearInspection(legalEntityCode, auditTypeCode,planId, auditId);
				auditRatingDto.setPreAuditId(preAuditId);
				auditId = preAuditId;
				if(BankAuditUtil.isEmptyString(auditId)){
					return null;
				}
				auditScheduleWrkTmp = getAuditScheduleWrkTmp(legalEntityCode,   auditRatingDto.getAuditId() );
			//	}
			}
			
			StringBuilder queryString=new StringBuilder(""
					+ "    "
					+ "select sch as auditSchedule, tmp as auditScheduleTmp, " 
					+ "(select concat(auditTypeCode , '-' , auditTypeDesc) from MaintAuditTypeDesc at where at.auditTypeCode=sch.auditTypeCode and at.legalEntityCode= sch.legalEntityCode ) as auditTypeCode,"
					+ " (Select concat(unitCode , '-' , unitName) from  MaintEntity e where e.unitCode =sch.unitId and e.legalEntityCode =sch.legalEntityCode ) as unitId,  "
					+ " (Select concat(sch.auditStatus , '-' , g.description) from  GeneralParameter g where g.legalEntityCode =sch.legalEntityCode and g.key1='STATUS' and g.key2='AUDIT_STATUS' and g.value=sch.auditStatus) as auditStatus, "
					+ " (Select concat(tmp.ratingStatus , '-' , g.description) from  GeneralParameter g where g.legalEntityCode =tmp.legalEntityCode and g.key1='STATUS' and g.key2='STATUS' and g.value=tmp.ratingStatus) as ratingStatusTmp "
					+ " from AuditSchedule sch "
					+ " left join AuditScheduleWrkTmp tmp on tmp.auditId = sch.auditId "
					+ " and tmp.planId= sch.planId "
					+ " and tmp.auditTypeCode= sch.auditTypeCode "
					+ " and tmp.legalEntityCode= sch.legalEntityCode "
					+ " where sch.legalEntityCode =:legalEntityCode "
					+ " and sch.auditId=:auditId "
					//+ " and  sch.auditTypeCode=:auditTypeCode "  // need to uncomment, when one unit map to multiple Audit types
					+ " ");
					if(role.equalsIgnoreCase("G12")){
						queryString.append("  and sch.planId=:planId ");
					}
					 
			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;
							AuditScheduleWrkTmp auditScheduleWrkTmp=null;
							
							System.out.println("tuple[0] ...."+tuple[0]);
							if(tuple[1]!=null ){
								auditScheduleWrkTmp=(AuditScheduleWrkTmp)tuple[1];								
								if(tuple[2]!=null)	auditScheduleWrkTmp.setAuditTypeCode(tuple[2].toString());								
								if(tuple[3]!=null)	auditScheduleWrkTmp.setUnitName(tuple[3].toString());
								if(tuple[4]!=null)	auditScheduleWrkTmp.setAuditStatusDescription(tuple[4].toString());
								if(tuple[5]!=null)	auditScheduleWrkTmp.setRatingStatus(tuple[5].toString());
								
								  auditSchedule = new AuditSchedule();
								  BeanUtils.copyProperties(auditScheduleWrkTmp, auditSchedule);			
							}
							else if(tuple[0]!=null ){
								auditSchedule=(AuditSchedule)tuple[0];								
								if(tuple[2]!=null)	auditSchedule.setAuditTypeCode(tuple[2].toString());								
								if(tuple[3]!=null)	auditSchedule.setUnitName(tuple[3].toString());
								if(tuple[4]!=null)	auditSchedule.setAuditStatusDescription(tuple[4].toString());
								auditSchedule.setRatingStatus("A-Authorised");
							}
							return auditSchedule;
						}
				@Override
				public List transformList(List collection) {
					return collection;
				}
			})     
			.setParameter("legalEntityCode",legalEntityCode)
			.setParameter("auditId",auditId);
			//.setParameter("auditTypeCode",auditTypeCode);  // need to uncomment, when one unit map to multiple Audit types
			if(role.equalsIgnoreCase("G12")){
				query.setParameter("planId",planId);
			}
			
			auditSchedule=(AuditSchedule)query.uniqueResult();	
			if(auditScheduleWrkTmp!=null && !BankAuditUtil.isEmptyString(auditScheduleWrkTmp.getAuditId())){
				auditSchedule.setOnsiteComplianceRating(auditScheduleWrkTmp.getOnsiteComplianceRating());
				auditSchedule.setOnsiteComplianceScore(auditScheduleWrkTmp.getOnsiteComplianceScore());
				auditSchedule.setRatingMakerTimestamp(auditScheduleWrkTmp.getRatingMakerTimestamp());
				auditSchedule.setRatingMaker(auditScheduleWrkTmp.getRatingMaker());
			}
			auditRatingDto.setAuditSchedule(auditSchedule);
			/*if(status.equalsIgnoreCase(BankAuditConstant.STATUS_AUTH)) {
				StringBuilder queryString=new StringBuilder(""
						+ "    "
						+ "select sch as auditSchedule, tmp as auditScheduleTmp" 
						+ "(select concat(auditTypeCode , '-' , auditTypeDesc) from MaintAuditTypeDesc at where at.auditTypeCode=sch.auditTypeCode and at.legalEntityCode= sch.legalEntityCode ) as auditTypeCode,"
						+ " (Select concat(unitCode , '-' , unitName) from  MaintEntity e where e.unitCode =sch.unitId and e.legalEntityCode =sch.legalEntityCode ) as unitId,  "
						+ " (Select concat(sch.auditStatus , '-' , g.description) from  GeneralParameter g where g.legalEntityCode =sch.legalEntityCode and g.key1='STATUS' and g.key2='AUDIT_STATUS' and g.value=sch.auditStatus) as auditStatus "
						+ " from AuditSchedule sch "
						+ " left join AuditScheduleWrkTmp tmp on tmp.auditId = sch.auditId "
						+ " and tmp.planId= sch.planId "
						+ " and tmp.auditTypeCode= sch.auditTypeCode "
						+ " where sch.legalEntityCode =:legalEntityCode  and sch.auditId=:auditId "
						+ " and  sch.auditTypeCode=:auditTypeCode "
						+ "  and sch.planId=:planId "
						+ "  ");  
				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];
									
									if(tuple[1]!=null)	auditSchedule.setAuditTypeCode(tuple[1].toString());								
									if(tuple[2]!=null)	auditSchedule.setUnitName(tuple[2].toString());
									if(tuple[3]!=null)	auditSchedule.setAuditStatusDescription(tuple[3].toString());
								}
								return auditSchedule;
							}
					@Override
					public List transformList(List collection) {
						return collection;
					}
				})      .setParameter("legalEntityCode",legalEntityCode)
						.setParameter("auditId",auditId);
				auditSchedule=(AuditSchedule)query.uniqueResult();
			}else{
				StringBuilder queryString=new StringBuilder("select sch as auditSchedule, " 
						+ "(select concat(auditTypeCode , '-' , auditTypeDesc) from MaintAuditTypeDesc at where at.auditTypeCode=sch.auditTypeCode and at.legalEntityCode= sch.legalEntityCode ) as auditTypeCode,"
						+ " (Select concat(unitCode , '-' , unitName) from  MaintEntity e where e.unitCode =sch.unitId and e.legalEntityCode =sch.legalEntityCode ) as unitId,  "
						+ " (Select concat(sch.auditStatus , '-' , g.description) from  GeneralParameter g where g.legalEntityCode =sch.legalEntityCode and g.key1='STATUS' and g.key2='AUDIT_STATUS' and g.value=sch.auditStatus) as auditStatus "
						+ " from AuditScheduleWrkTmp sch where legalEntityCode =:legalEntityCode  and auditId=:auditId ");  and planId=:planId 
				System.out.println("Before the qry....");
				
				Query query=(Query) session.createQuery(queryString.toString())				
						.setResultTransformer(new ResultTransformer() {
							@Override
							public Object transformTuple(Object[] tuple, String[] aliases) {
								AuditScheduleWrkTmp auditSchedule=null;
								System.out.println("tuple[0] ...."+tuple[0]);
								if(tuple[0]!=null){
									auditSchedule=(AuditScheduleWrkTmp)tuple[0];
									if(tuple[1]!=null)	auditSchedule.setAuditTypeCode(tuple[1].toString());								
									if(tuple[2]!=null)	auditSchedule.setUnitName(tuple[2].toString());
									if(tuple[3]!=null)	auditSchedule.setAuditStatusDescription(tuple[3].toString());
								}
								return auditSchedule;
							}
					@Override
					public List transformList(List collection) {
						return collection;
					}
				})      .setParameter("legalEntityCode",legalEntityCode)
						.setParameter("auditId",auditId);
				AuditScheduleWrkTmp auditScheduleWrkTmp=(AuditScheduleWrkTmp)query.uniqueResult();
				auditSchedule =  new AuditSchedule();
				BeanUtils.copyProperties(auditScheduleWrkTmp, auditSchedule);			
			}*/
			 
		}catch(Exception e) {
			e.printStackTrace();
			return null;
		}//return auditSchedule; 
		return auditRatingDto;
	}
	
	@Override
	public String updateAuditScheduleRatingIfAuthorized(AuditRatingDto auditRatingDto){
		try{
			AuditSchedule  auditSchedule =  auditRatingDto.getAuditSchedule();
			auditSchedule.setRatingCheckerTimestamp(businessDateService.getBusinessDateTimestampByLegalEntityCode(auditSchedule.getLegalEntityCode()));
			
			String role = auditRatingDto.getRole();
			if(role.equalsIgnoreCase("G2") || role.equalsIgnoreCase("G8") ){				
				Session session = getSession();
				String updateQry = "Update AuditSchedule  set" 
						+ " onsiteComplianceScore=:onsiteComplianceScore,  "
						+ " onsiteComplianceRating=:onsiteComplianceRating,  "
						+ " ratingMaker=:ratingMaker,  "
						+ " ratingMakerTimestamp=:ratingMakerTimestamp,  "
						+ " ratingChecker=:ratingChecker,  "
						+ " ratingCheckerTimestamp=:ratingCheckerTimestamp,  "
						+ " ratingStatus=:ratingStatus,  "
						+ " ratingAuthRejRemarks=:ratingAuthRejRemarks "
						+ " where  legalEntityCode=:legalEntityCode "
						+ " and auditTypeCode=:auditTypeCode " 
						+ " and auditId=:auditId";
				
				 session.createQuery(updateQry) 
					.setParameter("onsiteComplianceScore", auditSchedule.getOnsiteComplianceScore())
					.setParameter("onsiteComplianceRating", auditSchedule.getOnsiteComplianceRating())
					.setParameter("ratingMaker", auditSchedule.getRatingMaker())
					.setParameter("ratingMakerTimestamp", auditSchedule.getRatingMakerTimestamp())
					.setParameter("ratingChecker", auditSchedule.getRatingChecker())
					.setParameter("ratingCheckerTimestamp", auditSchedule.getRatingCheckerTimestamp())
					.setParameter("ratingStatus", auditSchedule.getRatingStatus())
					.setParameter("ratingAuthRejRemarks", auditSchedule.getRatingAuthRejRemarks())
					.setParameter("legalEntityCode", auditSchedule.getLegalEntityCode())
					.setParameter("auditTypeCode", auditSchedule.getAuditTypeCode()) 
					.setParameter("auditId", auditRatingDto.getPreAuditId())
					.executeUpdate();
			}else{
				Session session = getSession();
				String updateQry = "Update AuditSchedule  set"
						+ " deskComplianceScore=:deskComplianceScore,"
						+ " deskComplianceRating=:deskComplianceRating,  " 
						+ " ratingMaker=:ratingMaker,  "
						+ " ratingMakerTimestamp=:ratingMakerTimestamp,  "
						+ " ratingChecker=:ratingChecker,  "
						+ " ratingCheckerTimestamp=:ratingCheckerTimestamp,  "
						+ " ratingStatus=:ratingStatus,  "
						+ " ratingAuthRejRemarks=:ratingAuthRejRemarks "
						+ " where  legalEntityCode=:legalEntityCode "
						+ " and auditTypeCode=:auditTypeCode "
						+ " and planId=:planId "
						+ " and auditId=:auditId";
				
				 session.createQuery(updateQry)
					.setParameter("deskComplianceScore", auditSchedule.getDeskComplianceScore())
					.setParameter("deskComplianceRating", auditSchedule.getDeskComplianceRating()) 
					.setParameter("ratingMaker", auditSchedule.getRatingMaker())
					.setParameter("ratingMakerTimestamp", auditSchedule.getRatingMakerTimestamp())
					.setParameter("ratingChecker", auditSchedule.getRatingChecker())
					.setParameter("ratingCheckerTimestamp", auditSchedule.getRatingCheckerTimestamp())
					.setParameter("ratingStatus", auditSchedule.getRatingStatus())
					.setParameter("ratingAuthRejRemarks", auditSchedule.getRatingAuthRejRemarks())
					.setParameter("legalEntityCode", auditSchedule.getLegalEntityCode())
					.setParameter("auditTypeCode", auditSchedule.getAuditTypeCode())
					.setParameter("planId", auditSchedule.getPlanId())
					.setParameter("auditId", auditSchedule.getAuditId())
					.executeUpdate();
			}
			
			
			 
		}catch (Exception ex) {
			logger.error("Error .." + ex.getMessage()+" :: "+ex.getCause());
			return BankAuditConstant.FAILED;
		}
		return BankAuditConstant.SUCCESS;		
	}
	
	@Override
	public String DeleteAuditScheduleWrkTmpRatingIfAuthorized(AuditScheduleWrkTmp auditSchedule){
		try{
			
			Session session = getSession();
			String updateQry = "Delete from  AuditScheduleWrkTmp   "
					+ " where  legalEntityCode=:legalEntityCode "
					/*+ " and auditTypeCode=:auditTypeCode "
					+ " and planId=:planId "*/
					+ " and auditId=:auditId";
			
			 session.createQuery(updateQry) 
				.setParameter("legalEntityCode", auditSchedule.getLegalEntityCode())
				/*.setParameter("auditTypeCode", auditSchedule.getAuditTypeCode())
				.setParameter("planId", auditSchedule.getPlanId())*/
				.setParameter("auditId", auditSchedule.getAuditId())
				.executeUpdate();
			 
		}catch (Exception ex) {
			logger.error("Error .." + ex.getMessage()+" :: "+ex.getCause());
			return BankAuditConstant.FAILED;
		}
		return BankAuditConstant.SUCCESS;		
	}
	
	@Override
	public AuditSchedule getAuditByPlanIdAndAuditId(int legalEntityCode, String auditTypeCode, String planId,	
			 String auditId, String complianceRatingFor ) {
		Session session = getSession();
		session.setDefaultReadOnly(true);
		
		AuditSchedule auditSchedule = null;
		try { 
			String  queryString= "select sch as auditSchedule "
					+ " from AuditSchedule sch "
					+ " where legalEntityCode =:legalEntityCode "
					+ " and auditId=:auditId "
					+ " and planId=:planId "
					+ " and auditTypeCode=:auditTypeCode  " ;
			if(complianceRatingFor.equalsIgnoreCase("wrk_tmp")){
				queryString= "select sch as auditSchedule "
						+ " from AuditScheduleWrkTmp sch "
						+ " where legalEntityCode =:legalEntityCode "
						+ " and auditId=:auditId "
						+ " and planId=:planId "
						+ " and auditTypeCode=:auditTypeCode  " ;
			} 		 
			
			Query query=(Query) session.createQuery(queryString)
					.setParameter("legalEntityCode",legalEntityCode)
					.setParameter("auditId",auditId)
					.setParameter("planId",planId)
					.setParameter("auditTypeCode",auditTypeCode);
			if(complianceRatingFor.equalsIgnoreCase("wrk_tmp")){
				AuditScheduleWrkTmp auditScheduleWrkTmp=(AuditScheduleWrkTmp)query.uniqueResult(); 
				auditSchedule =  new AuditSchedule();
				//if(auditScheduleWrkTmp!=null){
					BeanUtils.copyProperties(auditScheduleWrkTmp, auditSchedule);	
				//}			
			}else{
				
				auditSchedule=(AuditSchedule)query.uniqueResult(); 
			}
			
		}catch(Exception e) {
			e.printStackTrace();
		}return auditSchedule; 
	}
	
	@Override
	public AuditSchedule getAuditByAuditId(int legalEntityCode,    String auditId  ) {
		Session session = getSession();
		session.setDefaultReadOnly(true);
		
		AuditSchedule auditSchedule = null;
		try { 
			String  queryString= "select sch as auditSchedule "
					+ " from AuditSchedule sch "
					+ " where legalEntityCode =:legalEntityCode "
					+ " and auditId=:auditId   " ;
			 
			
			Query query=(Query) session.createQuery(queryString)
					.setParameter("legalEntityCode",legalEntityCode)
					.setParameter("auditId",auditId) ;		 
				
				auditSchedule=(AuditSchedule)query.uniqueResult(); 		 
			
		}catch(Exception e) {
			e.printStackTrace();
		}return auditSchedule; 
	}
	
	/*********** Document upload Download Start **********************/
	/*@Override
	public String getReferencePathById(Integer legalEntityCode, Integer id,  String status){
	
		Session session =getSession();
		session.setDefaultReadOnly(true);
	 	StringBuilder str =new StringBuilder("  ");
		
		if("A".equalsIgnoreCase(status) ) str.append( "Select referencePath from AuditRatingDocumentDetails where legalEntityCode=:legalEntityCode AND id=:id ");
		else  str.append( "Select referencePath from AuditRatingDocumentDetailsWrk where legalEntityCode=:legalEntityCode AND id=:id ");		
	 	if("A".equalsIgnoreCase(status) ) str.append( "Select referencePath from AuditDocumentDetails where legalEntityCode=:legalEntityCode AND id=:id ");
		else  str.append( "Select referencePath from AuditDocumentDetailsWrk where legalEntityCode=:legalEntityCode AND id=:id ");

		return (String)(session.createQuery(str.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("id", id).uniqueResult());
	}*/
	
	@Override 
	public void updateMakerToWrkAndMaster(Integer legalEntityCode, String auditTypeCode, String auditId, String planId,  String maker,
			String documentType, String documentSubType){
		
		try{		
			Session session =getSession();
			
			//insert all from master to work to authorize 
			String insertToWrk = "insert into audit_document_details_wrk(legal_entity_code,audit_type_code,"
					+ " plan_id, audit_id, document_type,"
					+ " system_id,file_name,reference_number,reference_path,status,role, maker,"
					+ " maker_timestamp,checker,checker_timestamp,businessdate_timestamp, "
					+ " sysdate_timestamp,entity_status, auth_rej_remarks, document_name, document_sub_type) "
							+ " Select  legal_entity_code,audit_type_code,"
							+ " plan_id, audit_id, document_type,"
							//+ " system_id,file_name,reference_number,reference_path,status,role, maker,"
							+ " system_id,file_name,reference_number,reference_path,'"+BankAuditConstant.STATUS_UNAUTH+"',role, maker,"
							+ " maker_timestamp,checker,checker_timestamp,businessdate_timestamp, "
							+ " sysdate_timestamp,entity_status, auth_rej_remarks, document_name, document_sub_type "
							+ " from audit_document_details "
							+ " Where  legal_entity_code =:legalEntityCode "
							+ " and audit_type_code=:auditTypeCode "
							+ " and  document_type=:documentType "
							+ " and  document_sub_type=:documentSubType "
							+ "  and plan_id=:planId "
							+ "  and audit_id=:auditId  "
							+ " and  status='"+BankAuditConstant.STATUS_AUTH+"' ";	
			
			session.flush();
		  	session.createSQLQuery(insertToWrk)
			  	.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("documentType", documentType)
				.setParameter("documentSubType", documentSubType)
				.setParameter("planId", planId)
				.setParameter("auditId", auditId)
				.executeUpdate();
		  			  	
		  //UPdate all from master  	
			String deleteQuery = " update audit_document_details  set  status='"+BankAuditConstant.STATUS_UNAUTH+"', "
					+ "  entity_status='"+BankAuditConstant.STATUS_ACTIVE+"' "
					+ "  where legal_entity_code =:legalEntityCode "
					+ "  and audit_type_code=:auditTypeCode "
					+ " and  document_type=:documentType "
					+ " and  document_sub_type=:documentSubType "
					+ "  and plan_id=:planId "
					+ "  and audit_id=:auditId  ";	
			
			session.flush();
			session.createSQLQuery(deleteQuery)
				.setParameter("legalEntityCode", legalEntityCode)			
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("documentType", documentType)
				.setParameter("documentSubType", documentSubType)
				.setParameter("planId", planId)
				.setParameter("auditId", auditId)
				.executeUpdate();	  			 	
		  	
		    /// Update maker
			 /*String updateStatus = "Update AuditDocumentDetailsWrk auditDocumentDetails set "						 
					+ " auditDocumentDetails.maker='"+maker+"',"
					+ " auditDocumentDetails.status= '"+BankAuditConstant.STATUS_UNAUTH+"' "
					+ " where legalEntityCode =:legalEntityCode"
					+ " and auditTypeCode =:auditTypeCode " 
					+ " and  documentType=:documentType "
					+ " and  documentSubType=:documentSubType "
					+ "  and planId=:planId "
					+ "  and auditId=:auditId " ;  
											
			 session.createQuery(updateStatus)
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("documentType", documentType)
				.setParameter("documentSubType", documentSubType)
				.setParameter("planId", planId)
				.setParameter("auditId", auditId)
				.executeUpdate();			*/
			
		}catch (Exception e) {
			logger.error("Error .." + e.getMessage() +" :: "+e.getCause()); 
		}		
	}
	
	@Override
	/*public String  authorizeAuditRatingDocuments(Integer legalEntityCode, String auditTypeCode, String auditId, String planId,  String checker,
			String status, String authRejRemarks, String documentType,  String documentSubType, String role)*/
	public String  authorizeAuditRatingDocuments(AuditRatingDto auditRatingDto){
		try{
			AuditSchedule auditScheduleWrkTmp = auditRatingDto.getAuditSchedule();
			
			Integer legalEntityCode = auditScheduleWrkTmp.getLegalEntityCode();
			String auditTypeCode = 	auditScheduleWrkTmp.getAuditTypeCode();
			String auditId = auditScheduleWrkTmp.getAuditId();
			String planId = auditScheduleWrkTmp.getPlanId();
			String documentType = auditScheduleWrkTmp.getDocumentType();
			String documentSubType = auditScheduleWrkTmp.getDocumentSubType();
			String checker = auditScheduleWrkTmp.getRatingChecker();
			String authRejRemarks = auditScheduleWrkTmp.getRatingAuthRejRemarks();
			String status = auditScheduleWrkTmp.getRatingStatus();
			Session session =getSession();  			
			if(status.equalsIgnoreCase(BankAuditConstant.STATUS_AUTH)){
			
				//Delete all from master
				deleteAllAuditRatingDocFromMaster(legalEntityCode, auditTypeCode, auditId, planId, documentType,    documentSubType);
				session.flush();
			
				//Update ALL to Authorize in work
				String updateQry = "Update AuditDocumentDetailsWrk auditDocumentDetails set "						 
						+ " auditDocumentDetails.status='"+BankAuditConstant.STATUS_AUTH+"', "
								+ "auditDocumentDetails.businessdateTimestamp=  '"+ new java.sql.Timestamp(businessDateService.getBusinessDateTimestampByLegalEntityCode(legalEntityCode).getTime())+"' , "
								+ "auditDocumentDetails.sysdateTimestamp= '"+new java.sql.Timestamp(new Timestamp(System.currentTimeMillis()).getTime())+"' , " 
								+ "auditDocumentDetails.checkerTimestamp= '"+new java.sql.Timestamp(businessDateService.getBusinessDateTimestampByLegalEntityCode(legalEntityCode).getTime())+"'  , " 
								+ "auditDocumentDetails.checker= '"+checker+"', "
								+ "auditDocumentDetails.authRejRemarks= '"+authRejRemarks+"' "
						+ " where legalEntityCode =:legalEntityCode"
						+ " and auditTypeCode =:auditTypeCode "
						+ " and documentType =:documentType "
						+ " and documentSubType =:documentSubType "
						+ " and planId=:planId "
						+ " and auditId=:auditId " ;
												
				 session.createQuery(updateQry)
					.setParameter("legalEntityCode", legalEntityCode)
					.setParameter("auditTypeCode", auditTypeCode) 
					.setParameter("documentType", documentType)
					.setParameter("documentSubType", documentSubType)
					.setParameter("planId", planId)
					.setParameter("auditId", auditId)
				    .executeUpdate();
								
				//Insert all authorized to master
				String insertToMaster = "insert into audit_document_details(legal_entity_code,audit_type_code, "
						+ " plan_id, audit_id, document_type,"
						+ " system_id,file_name,reference_number,reference_path,status,role, maker,"
						+ " maker_timestamp,checker,checker_timestamp,businessdate_timestamp, "
						+ " sysdate_timestamp,entity_status, auth_rej_remarks, document_name, document_sub_type) "
								+ " Select  legal_entity_code, audit_type_code, "
								+ "  plan_id, audit_id, document_type,"
								+ " system_id,file_name,reference_number,reference_path,status,role, maker,"
								+ " maker_timestamp,checker,checker_timestamp,businessdate_timestamp, "
								//+ " sysdate_timestamp,entity_status, auth_rej_remarks, document_name, document_sub_type "
								+ " sysdate_timestamp,'"+BankAuditConstant.STATUS_ACTIVE+"', auth_rej_remarks, document_name, document_sub_type "
								+ " from audit_document_details_wrk "
										+ " Where  legal_entity_code =:legalEntityCode "
										+ " and audit_type_code=:auditTypeCode "
										+ " and plan_id=:planId "
										+ " and audit_id=:auditId  "
										+ " and  document_type=:documentType "
										+ " and  document_sub_type=:documentSubType "
										+ " and entity_status !=:entityStatus ";
				
				session.flush();
			  	session.createSQLQuery(insertToMaster)
					.setParameter("legalEntityCode", legalEntityCode)
					.setParameter("auditTypeCode", auditTypeCode)
					.setParameter("planId", planId)
					.setParameter("auditId", auditId)
					.setParameter("documentType", documentType)
					.setParameter("documentSubType", documentSubType)
					.setParameter("entityStatus", BankAuditConstant.STATUS_DEL)
					.executeUpdate();		
			 
			  	//Delete from Work
			  	session.flush();
			  	deleteAllAuditRatingDocFromWork( legalEntityCode, auditTypeCode, auditId, planId, documentType,    documentSubType);
			
			}else{
				 
				String updateQry = "Update AuditDocumentDetailsWrk auditDocumentDetails set "						 
						+ " auditDocumentDetails.status='"+BankAuditConstant.STATUS_REJ+"',"
								+ "auditDocumentDetails.businessdateTimestamp=  '"+ new java.sql.Timestamp(businessDateService.getBusinessDateTimestampByLegalEntityCode(legalEntityCode).getTime())+"' , " 
								+ "auditDocumentDetails.sysdateTimestamp='"+new java.sql.Timestamp(new Timestamp(System.currentTimeMillis()).getTime())+"' , " 
								+ "auditDocumentDetails.checkerTimestamp= '"+ new java.sql.Timestamp(businessDateService.getBusinessDateTimestampByLegalEntityCode(legalEntityCode).getTime())+"' , " 										 
								+ "auditDocumentDetails.checker= '"+checker+"', "
								+ "auditDocumentDetails.authRejRemarks= '"+authRejRemarks+"' "
						+ " where legalEntityCode =:legalEntityCode"
						+ " and auditTypeCode =:auditTypeCode " 
						+ " and planId=:planId "
						+ " and auditId=:auditId "
						+ " and documentType =:documentType "
						+ " and documentSubType =:documentSubType "; 
												
				session.createQuery(updateQry)
					.setParameter("legalEntityCode", legalEntityCode)
					.setParameter("auditTypeCode", auditTypeCode)
					.setParameter("planId", planId)
					.setParameter("auditId", auditId) 
					.setParameter("documentType", documentType)
					.setParameter("documentSubType", documentSubType)
				 	.executeUpdate();
			}
			
			return BankAuditConstant.SUCCESS;
		}catch (Exception e) {
			logger.error("Error .." + e.getMessage() +" :: "+e.getCause());
			return BankAuditConstant.FAILED;
		}
	}
	
	public String deleteAllAuditRatingDocFromMaster(Integer legalEntityCode, String auditTypeCode, String auditId, String planId,
			 String documentType,  String documentSubType){
		try{
			Session session =getSession();
			session.createQuery("delete from  AuditDocumentDetails  "
					+ " where legalEntityCode =:legalEntityCode "
					+ " and auditTypeCode=:auditTypeCode "
					+ " and planId=:planId "
					+ " and auditId=:auditId "
					+ " and documentType =:documentType "
					+ " and documentSubType =:documentSubType ")
					.setParameter("auditTypeCode", auditTypeCode)
					.setParameter("legalEntityCode", legalEntityCode)
					.setParameter("planId", planId)
					.setParameter("auditId", auditId) 
					.setParameter("documentType", documentType)
					.setParameter("documentSubType", documentSubType)
					.executeUpdate();
				
			return BankAuditConstant.SUCCESS;
		}catch (Exception e) {
			logger.error("Error .." + e.getMessage() +" :: "+e.getCause());
			return BankAuditConstant.FAILED;
		}		
	}
	
	
	public String deleteAllAuditRatingDocFromWork(Integer legalEntityCode, String auditTypeCode, String auditId, String planId,
			 String documentType,  String documentSubType){
		try{
			Session session =getSession();
			session.createQuery("delete from AuditDocumentDetailsWrk  "
					+ " where legalEntityCode =:legalEntityCode "
					+ " and auditTypeCode=:auditTypeCode "
					+ " and planId=:planId "
					+ " and auditId=:auditId "
					+ " and documentType =:documentType "
					+ " and documentSubType =:documentSubType ")
					.setParameter("auditTypeCode", auditTypeCode)
					.setParameter("legalEntityCode", legalEntityCode)
					.setParameter("planId", planId)
					.setParameter("auditId", auditId)
					.setParameter("documentType", documentType)
					.setParameter("documentSubType", documentSubType)
					.executeUpdate();
				
			return BankAuditConstant.SUCCESS;
		}catch (Exception e) {
			logger.error("Error .." + e.getMessage() +" :: "+e.getCause());
			return BankAuditConstant.FAILED;
		}		
	}
	
	@Override
	public String deleteAuditRatingDocumentsFromUI(Integer legalEntityCode,Integer id, String auditTypeCode, String auditId, String planId, String  status, String entityStatus,
			 String documentType,  String documentSubType){
		logger.info("Inside deleteAuditTypeDocument .." );
		
		try{
			Session session=getSession();
			String deleteQry = " ";
			String insertToWrk ="";
			if(BankAuditConstant.STATUS_AUTH.equals(status)){
				//Update to Master
				deleteQry = " update AuditDocumentDetails  set   " //status='"+BankAuditConstant.STATUS_UNAUTH+"',
						+ " status='"+BankAuditConstant.STATUS_UNAUTH+"', "
						+ " entityStatus='"+BankAuditConstant.STATUS_DEL+"' "
						+ "  where legalEntityCode =:legalEntityCode "
						+ " and auditTypeCode=:auditTypeCode "
						+ " and planId=:planId "
						+ " and auditId=:auditId"
						+ " and id=:id ";
				
				session.createQuery(deleteQry)
					.setParameter("legalEntityCode", legalEntityCode)
					.setParameter("auditTypeCode", auditTypeCode)
					.setParameter("planId", planId)
					.setParameter("auditId", auditId)
					.setParameter("id", id)
					.executeUpdate(); 				
				
				/*insertToWrk = "insert into audit_document_details_wrk(legal_entity_code,audit_type_code, "
						+ " plan_id, audit_id,  document_type,"
						+ " system_id,file_name,reference_number,reference_path,status,role, maker,"
						+ " maker_timestamp,checker,checker_timestamp,businessdate_timestamp, "
						+ " sysdate_timestamp,entity_status, auth_rej_remarks, document_name, document_sub_type) "
							+ " Select  legal_entity_code,audit_type_code, "
							+ " plan_id, audit_id, document_type,"
							+ " system_id,file_name,reference_number,reference_path,'"+BankAuditConstant.STATUS_UNAUTH+"',role, maker,"
							+ " maker_timestamp,checker,checker_timestamp,businessdate_timestamp, "
							+ " sysdate_timestamp,entity_status, auth_rej_remarks, document_name, document_sub_type"
							+ " from audit_document_details "
							+ " Where  legal_entity_code =:legalEntityCode "
							+ " and audit_type_code=:auditTypeCode"
							+ " and plan_id=:planId"
							+ " and audit_id=:auditId "
							+ " and  document_type=:documentType "
							+ " and  document_sub_type=:documentSubType ";  
				
				session.flush();
			  	session.createSQLQuery(insertToWrk)
				  	.setParameter("auditTypeCode", auditTypeCode)
					.setParameter("legalEntityCode", legalEntityCode)
					.setParameter("planId", planId)
					.setParameter("auditId", auditId)
					.setParameter("documentType", documentType)
					.setParameter("documentSubType", documentSubType)
					.executeUpdate();	*/
					
			} else if (BankAuditConstant.STATUS_UNAUTH.equals(status) && !BankAuditConstant.STATUS_NEW.equals(entityStatus)) { 				
			 
				/*deleteQry = " Update AuditRatingDocumentDetailsWrk   set   entity_status='"+BankAuditConstant.STATUS_DEL+"' "
						+ "   where   legal_entity_code =:legalEntityCode "
						+ " and audit_type_code=:auditTypeCode "
						+ " and  id =:id ";	*/
			     deleteQry = " Update AuditDocumentDetailsWrk  "
			     		+ " set   entityStatus='"+BankAuditConstant.STATUS_DEL+"' "
			     		+ "  where legalEntityCode =:legalEntityCode "
						+ " and auditTypeCode=:auditTypeCode "
						+ " and planId=:planId "
						+ " and auditId=:auditId"
						+ " and id=:id "; 
			
				session.createQuery(deleteQry)
					.setParameter("legalEntityCode", legalEntityCode)
					.setParameter("auditTypeCode", auditTypeCode)
					.setParameter("planId", planId)
					.setParameter("auditId", auditId)
					.setParameter("id", id)
					.executeUpdate(); 	
				
			}else{
				/* deleteQry = "delete from AuditRatingDocumentDetailsWrk   "
							+ "   where   legal_entity_code =:legalEntityCode "
							+ " and audit_type_code=:auditTypeCode "
							+ " and  id =:id ";	*/
				
				 deleteQry = " Delete from AuditDocumentDetailsWrk  " 
				     		+ "  where legalEntityCode =:legalEntityCode "
							+ " and auditTypeCode=:auditTypeCode "
							+ " and planId=:planId "
							+ " and auditId=:auditId"
							+ " and id=:id "; 
				
					session.createQuery(deleteQry)
						.setParameter("legalEntityCode", legalEntityCode)
						.setParameter("auditTypeCode", auditTypeCode)
						.setParameter("planId", planId)
						.setParameter("auditId", auditId)
						.setParameter("id", id)
						.executeUpdate(); 				 
			}
			 
		  	return BankAuditConstant.SUCCESS;
		}catch (Exception e) {
			logger.error("Error .." + e.getMessage() +" :: "+e.getCause());
			return BankAuditConstant.FAILED;
		}		 
	}
		
	public String deleteAllAuditRatingDocumentsFromUI(Integer legalEntityCode, String auditTypeCode,  String auditId, String planId,  String status, 
			String documentType, String documentSubType){
		logger.info("Inside deleteAllAuditRatingDocumentsFromUI .." );		
	
		try{
			Session session =getSession();				
			if(BankAuditConstant.STATUS_AUTH.equals(status)){ 
				
				//Update into master
				String deleteQuery = " update AuditDocumentDetails  set  status='"+BankAuditConstant.STATUS_UNAUTH+"', "
						+ "  entityStatus='"+BankAuditConstant.STATUS_DEL+"' "
						+ "  where legalEntityCode =:legalEntityCode "
						+ " and auditTypeCode=:auditTypeCode "
						+ " and planId=:planId "
						+ " and auditId=:auditId "
						+ " and documentType =:documentType "
						+ " and documentSubType =:documentSubType ";
				
				session.createQuery(deleteQuery)
					.setParameter("legalEntityCode", legalEntityCode)
					.setParameter("auditTypeCode", auditTypeCode)
					.setParameter("planId", planId)
					.setParameter("auditId", auditId)
					.setParameter("documentType", documentType)
					.setParameter("documentSubType", documentSubType)
					.executeUpdate();
				
				//Insert into work to authorize from master
				String insertToWrk = "insert into audit_document_details_wrk(legal_entity_code,audit_type_code,"
						+ " plan_id, audit_id, document_type,"
						+ " system_id,file_name,reference_number,reference_path,status,role, maker,"
						+ " maker_timestamp,checker,checker_timestamp,businessdate_timestamp, "
						+ " sysdate_timestamp,entity_status, auth_rej_remarks, document_name, document_sub_type) "
							+ " Select  legal_entity_code,audit_type_code, "
							+ " plan_id, audit_id, document_type, "
							+ " system_id,file_name,reference_number,reference_path,status,role, maker,"
							+ " maker_timestamp,checker,checker_timestamp,businessdate_timestamp, "
							+ " sysdate_timestamp,entity_status, auth_rej_remarks, document_name, document_sub_type"
							+ " from audit_document_details "
								+ " Where  legal_entity_code =:legalEntityCode "
								+ " and audit_type_code=:auditTypeCode"
								+ " and plan_id=:planId"
								+ " and audit_id=:auditId "
								+ " and  document_type=:documentType "
								+ " and  document_sub_type=:documentSubType ";
				
				session.flush();
			  	session.createSQLQuery(insertToWrk)
				  	.setParameter("auditTypeCode", auditTypeCode)
					.setParameter("legalEntityCode", legalEntityCode)
					.setParameter("planId", planId)
					.setParameter("auditId", auditId)
					.setParameter("documentType", documentType)
					.setParameter("documentSubType", documentSubType)
					.executeUpdate();				
				
			}else{				 
				//Update which already authorized but wants to delete from wrok
				String updateQuery = " update AuditDocumentDetailsWrk  set  status='"+BankAuditConstant.STATUS_UNAUTH+"', " 
						+ "  entityStatus='"+BankAuditConstant.STATUS_DEL+"' "
						+ "  where legalEntityCode =:legalEntityCode "
						+ " and auditTypeCode=:auditTypeCode "
						+ " and planId=:planId "
						+ " and auditId=:auditId  "
						+ " and documentType =:documentType "
						+ " and documentSubType =:documentSubType "
						+ "  and entityStatus !=:entityStatus ";	
				
				session.flush();
				session.createQuery(updateQuery)
					.setParameter("auditTypeCode", auditTypeCode)
					.setParameter("legalEntityCode", legalEntityCode)
					.setParameter("planId", planId)
					.setParameter("auditId", auditId)
					.setParameter("documentType", documentType)
					.setParameter("documentSubType", documentSubType)
					.setParameter("entityStatus", BankAuditConstant.STATUS_NEW)	
					.executeUpdate();
				
				//Delete newly added 
				String deleteQuery = " delete from AuditDocumentDetailsWrk    "
						+ "  where legalEntityCode =:legalEntityCode "
						+ "  and auditTypeCode=:auditTypeCode "
						+ "  and planId=:planId "
						+ "  and auditId=:auditId "
						+ " and documentType =:documentType "
						+ " and documentSubType =:documentSubType "
						+ "  and entityStatus =:entityStatus ";	
				
				session.flush();
				session.createQuery(deleteQuery)
					.setParameter("auditTypeCode", auditTypeCode)
					.setParameter("legalEntityCode", legalEntityCode)
					.setParameter("planId", planId)
					.setParameter("auditId", auditId)
					.setParameter("documentType", documentType)
					.setParameter("documentSubType", documentSubType)
					.setParameter("entityStatus", BankAuditConstant.STATUS_NEW)
					.executeUpdate();				
				
			} 
				
			return BankAuditConstant.SUCCESS;
		}catch (Exception e) {
			logger.error("Error .." + e.getMessage() +" :: "+e.getCause());
			return BankAuditConstant.FAILED;
		}
	}
	
	/*@Override
	public List<Object> getAuditRatingDocumentDetails(Integer legalEntityCode, String auditTypeCode, String auditId, String planId, 
				String documentType, String userId, String userRoleId, String status,    String documentSubType) {
		Session session =getSession();
		session.setDefaultReadOnly(true);
		 
		logger.info("legalEntityCode.. "+legalEntityCode +" auditTypeCode.."+auditTypeCode+" documentType.. "+ documentType);
		try {
			
			StringBuffer str = new StringBuffer("   ");			
			 
			if("A".equalsIgnoreCase(status) ) str.append( " from AuditDocumentDetails where legalEntityCode=:legalEntityCode ");
			else  str.append( " from AuditDocumentDetailsWrk where legalEntityCode=:legalEntityCode ");
			
			str.append( "   and auditTypeCode=:auditTypeCode "
			+ "  and planId=:planId "
			+ "  and auditId=:auditId " );
			if(!BankAuditUtil.isEmptyString(documentType)){
				str.append(" and  documentType =:documentType ");
			}
			if(!BankAuditUtil.isEmptyString(documentSubType)){
				str.append(" and documentSubType =:documentSubType ");
			}
			logger.info("str .. "+ str);
			
			Query qry =session.createQuery(str.toString());
			qry.setParameter("legalEntityCode", legalEntityCode);
			qry.setParameter("auditTypeCode",auditTypeCode);
			qry.setParameter("planId", planId);
			qry.setParameter("auditId", auditId);
			if(!BankAuditUtil.isEmptyString(documentType)){
				qry.setParameter("documentType",documentType);
			}
			if(!BankAuditUtil.isEmptyString(documentSubType)){
				qry.setParameter("documentSubType", documentSubType);
			} 
			logger.info(qry.toString());
			List<Object> lst = qry.list();			 
			
			 return lst;
			
			
		}catch(Exception e) {
			e.printStackTrace();
			return null;
		} 
	}*/
	
	private String getPreviousYearInspection(int legalEntityCode, String auditTypeCode, String planId, String auditId ){
		
		logger.info("getPreviousYearInspection():: legalEntityCode.."+legalEntityCode +" auditTypeCode.."+auditTypeCode+" auditId.. "+ auditId);
		
		Session session =getSession();
		session.setDefaultReadOnly(true);		
		String strQry =" from AuditSchedule where legalEntityCode=:legalEntityCode " 		
					//	+ "  and auditTypeCode=:auditTypeCode "  // need to uncomment, when one unit map to multiple Audit types
						+ "  and auditId=:auditId " ;						  
		
		Query qry =session.createQuery(strQry);
		qry.setParameter("legalEntityCode", legalEntityCode);
		// qry.setParameter("auditTypeCode",auditTypeCode);  // need to uncomment, when one unit map to multiple Audit types
		qry.setParameter("auditId", auditId);	 
		 
		AuditSchedule  auditSchedule = (AuditSchedule)qry.uniqueResult();;		
		return auditSchedule.getPrevAuditId();
	}
	
 	public AuditScheduleWrkTmp getAuditScheduleWrkTmp(int legalEntityCode,   String auditId ) {
		Session session = getSession();
		session.setDefaultReadOnly(true);
		 
		try {  
			String	queryString= "select sch as auditSchedule "
						+ " from AuditScheduleWrkTmp sch "
						+ " where legalEntityCode =:legalEntityCode "
						+ " and auditId=:auditId ";
						/*+ " and planId=:planId "
						+ " and auditTypeCode=:auditTypeCode  "*/  
			 		 
			
			Query query=(Query) session.createQuery(queryString)
					.setParameter("legalEntityCode",legalEntityCode)
					.setParameter("auditId",auditId);/*
					.setParameter("planId",planId)
					.setParameter("auditTypeCode",auditTypeCode);*/
			AuditScheduleWrkTmp auditScheduleWrkTmp=(AuditScheduleWrkTmp)query.uniqueResult(); 
			return auditScheduleWrkTmp; 
		}catch(Exception e) {
			e.printStackTrace();
			return null;
		}
	}
	 
	/*********** Document upload Download End **********************/
 	
 	@Override
 	public String getOnsiteComplianceStatus(int legalEntityCode,String auditTypeCode, String planId,String auditId){
		logger.info("getOnsiteComplianceStatus ...."+auditId +" :: "+auditTypeCode );
 		String preAuditId = getPreviousYearInspection(legalEntityCode, auditTypeCode, planId, auditId);
 		if(!BankAuditUtil.isEmptyString(preAuditId)){ 
 			
 			Session session =getSession();
 			session.setDefaultReadOnly(true);		
 			/*String strQry =" from AuditSchedule where legalEntityCode=:legalEntityCode " 		
 							+ "  and auditTypeCode=:auditTypeCode " 
 							+ "  and auditId=:auditId " ;	*/		
 			 
 			String strQry = " Select sch.onsiteComplianceRating , "
 					+ " (Select  tmp.onsiteComplianceRating  from AuditScheduleWrkTmp tmp where  tmp.auditId = :auditId and "
 							+ " tmp.legalEntityCode = tmp.legalEntityCode  ) as tempauditId "
 					+ " , sch.ratingComplianceStatus " // added to get Compliance status
 					+ " FROM AuditSchedule sch"
 					+ " where legalEntityCode=:legalEntityCode " 		
						//+ "  and auditTypeCode=:auditTypeCode " 
						+ "  and auditId = :PreAuditId " ;	
 			
 			Query qry =session.createQuery(strQry) 
 					.setParameter("legalEntityCode", legalEntityCode)
 					//.setParameter("auditTypeCode",auditTypeCode)
 					.setParameter("PreAuditId", preAuditId)
 					.setParameter("auditId", auditId);
 			
 			Object[] data = (Object[]) qry.uniqueResult();  
 			logger.info("data is .."+ data);
 			if(data != null){
 				String onsiteComplianceRating =  data[0]==null?null:data[0].toString();
 				String onsiteComplianceRatingTmp = data[1]==null?null:data[1].toString();
 				String onsiteComplianceRatingStatus = data[2]==null?null:data[2].toString();
 				if(!BankAuditUtil.isEmptyString(onsiteComplianceRating)
 						&& !BankAuditUtil.isEmptyString(onsiteComplianceRatingTmp)){
 					return BankAuditConstant.VALIDATION_FAILED; 
 				}else if(!"RCFS".equalsIgnoreCase(onsiteComplianceRatingStatus)){ // Verifying if Compliance on-site rating Authorized or not
 					return BankAuditConstant.FAILED;  
 				}else if(!BankAuditUtil.isEmptyString(onsiteComplianceRating)){
 					return BankAuditConstant.SUCCESS;
 				} 
 			} 
 			return BankAuditConstant.FAILED;
 			
 			/*AuditSchedule  auditSchedule = (AuditSchedule)qry.uniqueResult();		
 			String onsiteComplianceRating = auditSchedule.getOnsiteComplianceRating();
 			if(!BankAuditUtil.isEmptyString(onsiteComplianceRating)){
 				return BankAuditConstant.SUCCESS;
 			}else{
 				return BankAuditConstant.FAILED;
 			}*/
 		} 
 		return BankAuditConstant.SUCCESS;
 		 
	}

 	@Override
 	public void deleteOnsiteComplianceDataFromWrkTmp(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();		 
	}
}
