package com.bankaudit.rest.process.dao;

import java.time.LocalDate;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.transform.ResultTransformer;
import org.jboss.logging.Logger;
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.AuditLettersDto;
import com.bankaudit.dto.process.AuditObservationLetterDto;
import com.bankaudit.dto.process.LettersDto;
import com.bankaudit.dto.process.LettersSystemDataDto;
import com.bankaudit.model.MaintLetter;
import com.bankaudit.process.model.AuditLetterBiLingual;
import com.bankaudit.process.model.AuditLetterBiLingualWrk;
import com.bankaudit.process.model.AuditLetterUserInputs;
import com.bankaudit.process.model.AuditLetterUserInputsWrk;
import com.bankaudit.process.model.AuditRatingCompositeCategoryScore;
import com.bankaudit.process.model.AuditSchedule;
import com.bankaudit.util.BankAuditUtil;

@Repository("lettersDao")
public class LettersDaoImpl extends AbstractDao implements LettersDao{
	
	static final Logger logger = Logger.getLogger(LettersDaoImpl.class);

	@Override
	public List<LettersDto> getLettersByAuditId(int legalEntityCode, String auditTypeCode,String auditId,String role) {
		Session session = getSession();
		session.setDefaultReadOnly(true);
		
		logger.info("Inside getLettersByAuditId .... ");
		List<String> letterCodeNotRequired = Arrays.asList(new String[]{BankAuditConstant.LETTER_CODE_IR_GRADING,
				BankAuditConstant.LETTER_CODE_IR_GRADING_HO_COMPLIANCE,BankAuditConstant.LETTER_CODE_IR_GRADING_RO_COMPLIANCE});
		List<LettersDto> lettersLst= null; 
		StringBuilder queryString=new StringBuilder(" SELECT g.value, g.description, a.status, a.maker, a.reviewer, "
				+ " (Select concat(IFNULL(u.first_name,''), ' ',IFNULL(u.middle_name,''),' ',IFNULL(u.last_name,'')) from  user u where u.user_id =a.maker and u.legal_entity_code =:legalEntityCode ) as makerName, "
				+ " (Select concat(IFNULL(u.first_name,''), ' ',IFNULL(u.middle_name,''),' ',IFNULL(u.last_name,'')) from  user u where u.user_id =a.reviewer and u.legal_entity_code =:legalEntityCode ) as reviewerName "
				+ " FROM general_parameter g LEFT JOIN "
				+ "  (SELECT audit_id, letter_code, status, maker,reviewer, legal_entity_code  FROM audit_letter_user_inputs_wrk WHERE audit_id=:auditId AND legal_entity_code=:legalEntityCode "
				+ "	 UNION "
				+ "  SELECT audit_id, letter_code, status, maker,reviewer, legal_entity_code  FROM audit_letter_user_inputs WHERE audit_id=:auditId AND legal_entity_code=:legalEntityCode"
				+ "	 ) a  "
				+ "  ON a.letter_code=g.value AND a.legal_entity_code=g.legal_entity_code "
				+ " WHERE key_1 = 'LETTER' and (key_2 like '%AUDIT' or key_2 like 'AUDIT_BEFORE') and g.legal_entity_code=:legalEntityCode "
				+ " AND g.value NOT IN (:letterCodeNotRequired) "
				+ " ORDER BY FIELD(g.value,'Dr','FwdL','RN','HoR','RoR','HoLoSC','RoLoSC','RN_Annexure_V' ) ASC ");
		
 			logger.info("Before the qry...."+queryString);
			
			Query query=session.createSQLQuery(queryString.toString())
					.setParameter("legalEntityCode", legalEntityCode)
					.setParameter("auditId", auditId)
					.setParameterList("letterCodeNotRequired", letterCodeNotRequired);
			
			List lst = query.list();
			logger.info("after executing obj.... "+ lst.size());
			try{
				lettersLst = new ArrayList<>();
				for ( Iterator iter = lst.iterator(); iter.hasNext(); ) {
					Object[] obj = (Object[]) iter.next();
					logger.info("Code::"+ obj[0] +" Desc::" +obj[1]+" Status::"+ obj[2]+" Maker::"+obj[3]);
					
					String letterCode = obj[0].toString() ;
					
					if(auditTypeCode.contains(BankAuditConstant.INSPECTION_TYPE_RRB_CONTAINS) 
							&& (BankAuditConstant.LETTER_CODE_FwdL_RoLoSC.equalsIgnoreCase(letterCode) || BankAuditConstant.LETTER_CODE_FwdL_RoR.equalsIgnoreCase(letterCode) )){
						continue;
					}else{
						LettersDto ltDto = new LettersDto();
						ltDto.setLegalEntityCode(legalEntityCode);
						ltDto.setAuditId(auditId);
						ltDto.setAuditTypeCode(auditTypeCode);
						if(obj[0] !=null)ltDto.setLetterCode(letterCode);
						if(obj[1] !=null)ltDto.setLetterDesc(obj[1].toString());
						if(obj[2] !=null)ltDto.setStatus(obj[2].toString());
						if(obj[3] !=null)ltDto.setMaker(obj[3].toString());
						if(obj[4] !=null)ltDto.setReviewer(obj[4].toString()); 	
						if(obj[5] !=null)ltDto.setMakerName(obj[5].toString());  
						if(obj[6] !=null)ltDto.setReviewerName(obj[6].toString());  // Reviewer name as part of UI/UX change
 						lettersLst.add(ltDto);
					}
				}
			}catch(Exception e) {
				e.printStackTrace(System.out);
			}
			return lettersLst; 
	}
	
	
	@Override
	public AuditLettersDto getLetterByAuditIdAndLetterCode(int legalEntityCode, String auditId,String letterCode, String status, String auditTypeCode) {
		
		Session session=getSession();
		session.setDefaultReadOnly(true);
		logger.info("Inside getLetterByAuditIdAndLetterCode .. "+legalEntityCode +" :: "+auditId+" ::"+ letterCode+" ::"+status);
		
		AuditLettersDto adtLtrDto=new AuditLettersDto(); 
		if(BankAuditConstant.STATUS_AUTH.equalsIgnoreCase(status)){
			AuditLetterUserInputs adtLtr= (AuditLetterUserInputs)session.createQuery(" FROM AuditLetterUserInputs l "
					+ " WHERE l.legalEntityCode=:legalEntityCode AND l.auditId=:auditId AND l.letterCode=:letterCode ")
					.setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId).setParameter("letterCode", letterCode)
					.uniqueResult();
			adtLtrDto.setAdtLtr(adtLtr);
			//adtLtr = new AuditLetterUserInputs();
			//BeanUtils.copyProperties(adtLtrWrk, adtLtr);
		}else{
			AuditLetterUserInputsWrk adtLtrWrk= (AuditLetterUserInputsWrk)session.createQuery(" FROM AuditLetterUserInputsWrk l "
					+ " WHERE l.legalEntityCode=:legalEntityCode AND l.auditId=:auditId AND l.letterCode=:letterCode ")
					.setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId).setParameter("letterCode", letterCode)
					.uniqueResult();
			adtLtrDto.setAdtLtrWrk(adtLtrWrk);
		}		
		return adtLtrDto; 
	}
	
	@Override
	public AuditLettersDto getBiLingualLetterByAuditIdAndLetterCode(int legalEntityCode, String auditTypeCode,  String auditId, String letterCode, String status){
		Session session=getSession();
		session.setDefaultReadOnly(true);
		logger.info("Inside getBiLingualLetterByAuditIdAndLetterCode .. "+legalEntityCode +" :: "+auditId+" ::"+ letterCode+" ::"+status);		 
		AuditLettersDto adtLtrDto=new AuditLettersDto();
		if(BankAuditUtil.isEmptyString(status) || "null".equalsIgnoreCase(status)){
			List<String> auditTypeCodeList = new ArrayList<>();
			if(auditTypeCode.startsWith(BankAuditConstant.INSPECTION_TYPE_RRB_CONTAINS)){	 
				auditTypeCodeList.add("RRBI");auditTypeCodeList.add("RRBEI");
			}else if(auditTypeCode.startsWith(BankAuditConstant.INSPECTION_TYPE_DCCB_CONTAIN)){	 
				auditTypeCodeList.add("DCCBI");auditTypeCodeList.add("DCCBEI");
			}else if(auditTypeCode.startsWith(BankAuditConstant.INSPECTION_TYPE_StCB_CONTAIN)){	 
				auditTypeCodeList.add("StCBI");auditTypeCodeList.add("StCBEI");
			}
			
			Query qry =  session.createQuery("Select l as ltr FROM  MaintLetter l  "
					+ " WHERE l.legalEntityCode = :legalEntityCode"
					+ " AND l.letterCode = :letterCode "
					+ " AND l.auditTypeCode in (:auditTypeCode) "
					+ " AND l.status = :status"
					+ " AND l.entityStatus = :entityStatus "
					+ " order by sequence ASC ")
					.setParameter("legalEntityCode", legalEntityCode)
					.setParameterList("auditTypeCode", auditTypeCodeList)
					.setParameter("letterCode", letterCode)
					.setParameter("status", BankAuditConstant.STATUS_AUTH)
					.setParameter("entityStatus", BankAuditConstant.STATUS_ACTIVE);
			qry.setResultTransformer(new ResultTransformer() {
				
				@Override
				public Object transformTuple(Object[] tuple, String[] aliases) { 
					
					AuditLetterBiLingualWrk auditLetterBiLingualWrk = new AuditLetterBiLingualWrk();
					if(tuple[0]!=null){
						MaintLetter maintLetter = (MaintLetter)tuple[0];						
						auditLetterBiLingualWrk.setAuditId(auditId);
						auditLetterBiLingualWrk.setAuditTypeCode(auditTypeCode);
						auditLetterBiLingualWrk.setLegalEntityCode(legalEntityCode);
						auditLetterBiLingualWrk.setLetterCode(maintLetter.getLetterCode());
						auditLetterBiLingualWrk.setSequence(maintLetter.getSequence());
						auditLetterBiLingualWrk.setContentEnglish(maintLetter.getContentEnglish());
						auditLetterBiLingualWrk.setContentHindi(maintLetter.getContentHindi());
						auditLetterBiLingualWrk.setMergeFlag(maintLetter.getMergeFlag());
						auditLetterBiLingualWrk.setRichTextFlag(maintLetter.getRichTextFlag());
						auditLetterBiLingualWrk.setBoldFlag(maintLetter.getBoldFlag());
						auditLetterBiLingualWrk.setPageBreakFlag(maintLetter.getPageBreakFlag());
					}
					return auditLetterBiLingualWrk;
					
				}
				
				@Override
				public List transformList(List collection) { 
					return collection;
				}
			});
			
			List<AuditLetterBiLingualWrk> auditLetterBiLingualWrkList = qry.list();
			adtLtrDto.setAuditLetterBiLingualWrkList(auditLetterBiLingualWrkList);
			
		}else{			 
			if(BankAuditConstant.STATUS_AUTH.equalsIgnoreCase(status)){
				List<AuditLetterBiLingual> auditLetterBiLingualList =  session.createQuery(" FROM AuditLetterBiLingual l "
						+ " WHERE l.legalEntityCode=:legalEntityCode AND l.auditId=:auditId AND l.letterCode=:letterCode "
						+ " order by sequence ASC ")
						.setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId).setParameter("letterCode", letterCode)
						.list(); 
				adtLtrDto.setAuditLetterBiLingualList(auditLetterBiLingualList);
			}else{
				List<AuditLetterBiLingualWrk> auditLetterBiLingualWrkList =  session.createQuery(" FROM AuditLetterBiLingualWrk l "
						+ " WHERE l.legalEntityCode=:legalEntityCode AND l.auditId=:auditId AND l.letterCode=:letterCode "
						+ " order by sequence ASC ")
						.setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId).setParameter("letterCode", letterCode)
						.list();
				adtLtrDto.setAuditLetterBiLingualWrkList(auditLetterBiLingualWrkList);
			}		
		}
		
		return adtLtrDto;
	}
	
	@Override
	public AuditLetterUserInputsWrk getAuditLetterUserInputsWrk(int legalEntityCode, String auditId,String letterCode, String status) {
		
		Session session=getSession();
		session.setDefaultReadOnly(true);
		logger.info("Inside getLetterByAuditIdAndLetterCode .. "+legalEntityCode +" :: "+auditId+" ::"+ letterCode+" ::"+status);
		
		return (AuditLetterUserInputsWrk) session.createQuery(" FROM AuditLetterUserInputsWrk l "
				+ " WHERE l.legalEntityCode=:legalEntityCode AND l.auditId=:auditId AND l.letterCode=:letterCode ")
				.setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId).setParameter("letterCode", letterCode)
				.uniqueResult();		
	}
	
	@Override
	public void deleteAuditLetterInput(Integer legalEntityCode, String auditId,String letterCode, String status) {
		
		Session session=getSession();	
		
		if(!BankAuditConstant.STATUS_AUTH.equalsIgnoreCase(status)){			
			session.createQuery("delete from AuditLetterUserInputsWrk where "
					+ " legalEntityCode =:legalEntityCode and auditId =:auditId and letterCode=:letterCode ")
			.setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId).setParameter("letterCode", letterCode)
			.executeUpdate();	
			
			session.createQuery("delete from AuditLetterBiLingualWrk where "
					+ " legalEntityCode =:legalEntityCode and auditId =:auditId and letterCode=:letterCode ")
			.setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId).setParameter("letterCode", letterCode)
			.executeUpdate();	
			
		}else {
			session.createQuery("delete from AuditLetterUserInputs where "
					+ " legalEntityCode =:legalEntityCode and auditId =:auditId and letterCode=:letterCode ")
			.setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId).setParameter("letterCode", letterCode)
			.executeUpdate();
			
			session.createQuery("delete from AuditLetterBiLingual where "
					+ " legalEntityCode =:legalEntityCode and auditId =:auditId and letterCode=:letterCode ")
			.setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId).setParameter("letterCode", letterCode)
			.executeUpdate();
		}
		  
	}
	
	@Override
	public LettersSystemDataDto getSystemDataByAuditIdAndLetterCode(int legalEntityCode, String auditId,String letterCode, String status) {
		
		Session session=getSession();
		session.setDefaultReadOnly(true);
		logger.info("Inside getSystemDataByAuditIdAndLetterCode .. "+legalEntityCode +" :: "+auditId+" ::"+ letterCode+" ::"+status);
		LettersSystemDataDto ltDto=null;
		
		try {
			StringBuilder queryString=new StringBuilder("select  " 
					+ "  sch.legal_entity_code, sch.audit_id, sch.actual_start_date, sch.actual_end_date, sch.financial_year, sch.audit_serial_no, "  // 5 => audit_serail_no
					+ "  sch.unit_id, e.unit_name, e.address1, e.address2, e.address3, e.pincode, a.state_name, a.district_name, a.city_name,  " // 14 => city
					+ "	 t.user_id, u.first_name, u.last_name, u.designation, " // 18 => designation
					+ "  sch.report_dispatch_date, sch.report_status, " // 20=> report_status
					+ "	 e.parent_unit_code, p.unit_name as p_unit_name, p.address1 as p_address1, p.address2 as p_address2, p.address3 as p_address3, p.pincode as p_pincode, " //26=> p_pincode
					+ "  pa.state_name as p_state_name, pa.district_name as p_district_name, pa.city_name as p_city_name, " //29=> p_city_name
					+ "	 gt.name_in_language, " // 30
					+ "  sch.schedule_start_date, sch.schedule_end_date, sch.audit_status " // 32
					+ " from audit_schedule sch " 
					+ "  join audit_team t on sch.audit_id=t.audit_id and sch.legal_entity_code=t.legal_entity_code and t.user_type='L' "
					+ "  join user u on u.user_id=t.user_id and u.legal_entity_code=t.legal_entity_code "
					+ "  join maint_entity e on sch.legal_entity_code=e.legal_entity_code and sch.unit_id=e.unit_code "
					+ "  join maint_entity p on p.legal_entity_code=e.legal_entity_code and p.unit_code=e.parent_unit_code "
					+ "	 left join maint_address a on e.legal_entity_code=a.legal_entity_code and e.state=a.state_code and e.district=a.district_code and e.city=a.city_code "
					+ "	 left join maint_address pa on p.legal_entity_code=pa.legal_entity_code and p.state=pa.state_code and p.district=pa.district_code and p.city=pa.city_code	"
					+ "  left join general_translator gt on p.legal_entity_code=gt.legal_entity_code and p.state=gt.code and gt.key_01='STATE' and gt.key_02='NAME'	"
					+ " where sch.legal_entity_code=:legalEntityCode and sch.audit_id=:auditId ");
			
			Query query=(Query) session.createNativeQuery(queryString.toString())				
					.setResultTransformer(new ResultTransformer() {
						@Override
						public Object transformTuple(Object[] tuple, String[] aliases) {
							LettersSystemDataDto lsDto=new LettersSystemDataDto();
							
						if(tuple[0]!=null) lsDto.setLegalEntityCode(legalEntityCode);
						if(tuple[1]!=null) lsDto.setAuditId(auditId);
						if(tuple[2]!=null) lsDto.setAuditStartDate((Date)tuple[2]);
						if(tuple[3]!=null) lsDto.setAuditEndDate((Date)tuple[3]);
						if(tuple[4]!=null) lsDto.setFinYrDate((Date)tuple[4]);
						if(tuple[5]!=null) {
							lsDto.setSerialNo(tuple[5].toString());
							try{
								lsDto.setSerialNoRoman(BankAuditUtil.integerToRomanNumeral(Integer.parseInt(tuple[5].toString())));
							}catch(Exception e) {}
						}
						
						if(tuple[6]!=null) lsDto.setUnitCode(tuple[6].toString());
						if(tuple[7]!=null) lsDto.setUnitName(tuple[7].toString());
						if(tuple[8]!=null) lsDto.setAddress1(tuple[8].toString());
						if(tuple[9]!=null) lsDto.setAddress2(tuple[9].toString());
						if(tuple[10]!=null) lsDto.setAddress3(tuple[10].toString());
						if(tuple[11]!=null) lsDto.setPincode(tuple[11].toString());
						if(tuple[12]!=null) lsDto.setState(tuple[12].toString());
						if(tuple[13]!=null) lsDto.setDistrict(tuple[13].toString());
						if(tuple[14]!=null) lsDto.setCity(tuple[14].toString());
						if(tuple[15]!=null) lsDto.setLeadId(tuple[15].toString());
						if(tuple[16]!=null) lsDto.setLeadName((tuple[16].toString()+" "+(tuple[17]!=null?tuple[17].toString():"")));
						if(tuple[18]!=null) lsDto.setLeadDesignation(tuple[18].toString());
						if(tuple[19]!=null) lsDto.setDispatchDate((Date)tuple[19]);
						if(tuple[20]!=null) lsDto.setReportStatus(tuple[20].toString());
						
						if(tuple[21]!=null) lsDto.setParentUnitCode(tuple[21].toString());
						if(tuple[22]!=null) lsDto.setParentUnitName(tuple[22].toString());
						if(tuple[23]!=null) lsDto.setParentAddress1(tuple[23].toString());
						if(tuple[24]!=null) lsDto.setParentAddress2(tuple[24].toString());
						if(tuple[25]!=null) lsDto.setParentAddress3(tuple[25].toString());
						if(tuple[26]!=null) lsDto.setParentPincode(tuple[26].toString());
						if(tuple[27]!=null) lsDto.setParentState(tuple[27].toString());
						if(tuple[28]!=null) lsDto.setParentDistrict(tuple[28].toString());
						if(tuple[29]!=null) lsDto.setParentCity(tuple[29].toString());
						if(tuple[30]!=null) lsDto.setParentUnitNameHindi(tuple[30].toString());
						
						if(tuple[31]!=null) lsDto.setScheduleStartDate((Date)tuple[31]);
						if(tuple[32]!=null) lsDto.setScheduleEndDate((Date)tuple[32]);
						if(tuple[33]!=null) lsDto.setAuditStatus(tuple[33].toString());
						
						return lsDto;
					}
				@Override
				public List transformList(List collection) {
					return collection;
				}
			}).setParameter("legalEntityCode",legalEntityCode).setParameter("auditId",auditId);
			ltDto=(LettersSystemDataDto)query.uniqueResult();
			
		}catch(Exception e) {}
		return ltDto; 
	}
	
	@Override
	public Boolean isAuthAllowed(Integer legalEntityCode, String auditId,String letterCode, String userId) {		
		Session session=getSession();
		
		// The letters to be authorized in the sequence of 'FwdL','RN','HoR'/'RoR','HoLoSC'/'RoLoSC' 
		List<String> letterCodeLst =new ArrayList<>();
		if("RN".equalsIgnoreCase(letterCode)) letterCodeLst.add("FwdL");
		else if("HoR".equalsIgnoreCase(letterCode) || "RoR".equalsIgnoreCase(letterCode)) letterCodeLst.add("RN");
		else if("HoLoSC".equalsIgnoreCase(letterCode) || "RoLoSC".equalsIgnoreCase(letterCode)) { letterCodeLst.add("HoR"); letterCodeLst.add("RoR"); }
		
		Long authCount=(Long)session.createQuery(
				" SELECT COUNT(*) FROM AuditLetterUserInputs WHERE"
				+ " legalEntityCode =:legalEntityCode AND auditId =:auditId AND letterCode =:letterCodeLst ")
		.setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId)
		.setParameter("letterCodeLst", letterCodeLst).uniqueResult();
		
		Boolean flag;
		if(authCount!=null && authCount >0){
			flag= true;
		}else {
			flag= false;
		}
	return flag;
	}
	
	@Override
	public Map<String, AuditObservationLetterDto> getChklstStmnts(int legalEntityCode, String auditId,String letterCode, String status, String auditTypeCode) {
		
		Session session=getSession();
		session.setDefaultReadOnly(true);
		Map<String, AuditObservationLetterDto> chkLstMap= new HashMap<>();
		logger.info("Inside getChklstStmnts .. "+legalEntityCode +" :: "+auditId+" ::"+ letterCode+" ::"+status);
		StringBuilder queryString=new StringBuilder("select  " 
					+ "  o.legal_entity_code,o.audit_id,o.audit_type_code,d.letter_code,o.mapping_id,o.observation_id, "  // 5->observation_id
					+ "  o.finding_id,o.submission_status,o.compliance_category,o.finding_remarks,  " // 9->finding_remarks
					+ "  s.standard_stmnt_compliance, s.standard_stmnt_compliance_print,s.standard_stmnt_non_compliance,s.reference, " // 13->reference
					+ "	 d.display_sequence_ref, d.display_sequence_id, d.display_sequence, " //16->display_sequence
					+ "  o.standard_statement, o.standard_statement_print_flag " //18 standard stmt print flag
					+ "	from audit_observation o "
					+ " join audit_letter_dynamic_value_mapping d on d.display_ref_id=o.mapping_id and d.letter_code=:letterCode and d.display_type='CK' and d.audit_type_code=:auditTypeCode " 
					+ " left join maint_report_standard_stmt s on s.legal_entity_code=o.legal_entity_code and s.mapping_id=o.mapping_id " 
					+ " WHERE o.legal_entity_code=:legalEntityCode and o.audit_id=:auditId ");
			
			logger.info("Before the qry...."+queryString);			
			List lst=session.createSQLQuery(queryString.toString()).setParameter("legalEntityCode", legalEntityCode).setParameter("auditTypeCode", auditTypeCode).
					setParameter("auditId", auditId).setParameter("letterCode", letterCode).list();
			logger.info("after executing obj.... "+ lst.size());
			try{
				
				for ( Iterator iter = lst.iterator(); iter.hasNext(); ) {
					Object[] obj = (Object[]) iter.next();
					logger.info("observation_id::"+ obj[5] +" finding_remarks::" +obj[9]+" reference::"+ obj[13]+" Maker::"+obj[3]);					
					AuditObservationLetterDto ltDto = new AuditObservationLetterDto();
					ltDto.setLegalEntityCode(legalEntityCode);
					ltDto.setAuditId(auditId);
					if(obj[2] !=null)ltDto.setAuditTypeCode(obj[2].toString());
					if(obj[3] !=null)ltDto.setLetterCode(obj[3].toString());
					if(obj[4] !=null)ltDto.setMappingId(obj[4].toString());
					if(obj[5] !=null)ltDto.setObservationId(obj[5].toString());
					if(obj[6] !=null)ltDto.setFindingId(obj[6].toString());
					if(obj[7] !=null)ltDto.setSubmissionStatus(obj[7].toString());
					if(obj[8] !=null)ltDto.setComplianceCategory(obj[8].toString());
					if(obj[9] !=null)ltDto.setFindingRemarks(obj[9].toString());
					
					/*if(obj[10] !=null)ltDto.setStandardStmntCompliance(obj[10].toString());
					if(obj[11] !=null)ltDto.setStandardStmntCompliancePrint(obj[11].toString());
					if(obj[12] !=null)ltDto.setStandardStmntNonCompliance(obj[12].toString());*/
					
					if(obj[17] !=null)ltDto.setStandardStmntCompliance(obj[17].toString());
					if(obj[18] !=null)ltDto.setStandardStmntCompliancePrint(obj[18].toString());
					if(obj[17] !=null)ltDto.setStandardStmntNonCompliance(obj[17].toString());
					if(obj[18] !=null)ltDto.setStandardStmntNonCompliancePrint(obj[18].toString());
					
					if(obj[13] !=null)ltDto.setReference(obj[13].toString());
					if(obj[14] !=null)ltDto.setDisplaySequenceRef(obj[14].toString());
					if(obj[15] !=null)ltDto.setDisplaySequenceId(obj[15].toString());
					if(obj[16] !=null)ltDto.setDisplaySequence(obj[16].toString());
										
					chkLstMap.put(ltDto.getDisplaySequenceRef(), ltDto);
				}
			}catch(Exception e) {
				e.printStackTrace(System.out);
			}logger.info("Map is .."+ chkLstMap);
		return chkLstMap; 
	}

	@Override
	public Map<String, AuditObservationLetterDto> getDeriveStmnts(int legalEntityCode, String auditId,String letterCode, String status, String auditTypeCode) {
		
		Session session=getSession();
		session.setDefaultReadOnly(true);
		Map<String, AuditObservationLetterDto> drvStmntMap= new HashMap<>();
		logger.info("Inside getDeriveStmnts .. "+legalEntityCode +" :: "+auditId+" ::"+ letterCode+" ::"+status);
		StringBuilder queryString=new StringBuilder("select  " 
					+ "  r.legal_entity_code, r.audit_id, r.audit_type_code, r.statement_id, r.statement as derived_statement, r.derive_statement_op_stmnt, "  // 5->op_statement
					+ "  d.letter_code, d.display_sequence_ref, d.display_sequence_id, d.display_sequence " // 9->display_sequence
					+ "	from audit_report_stmt r "
					+ " join audit_letter_dynamic_value_mapping d on d.display_ref_id=r.statement_id and d.letter_code=:letterCode and d.display_type='DR' and d.audit_type_code=:auditTypeCode " 
					+ " WHERE r.legal_entity_code=:legalEntityCode and r.audit_id=:auditId and r.statement_type='DR' ");
			
		logger.info("Before the qry...."+queryString);			
		List lst=session.createSQLQuery(queryString.toString()).setParameter("legalEntityCode", legalEntityCode).setParameter("auditTypeCode", auditTypeCode).
					setParameter("auditId", auditId).setParameter("letterCode", letterCode).list();
		logger.info("after executing obj.... "+ lst.size());
		try{
			for ( Iterator iter = lst.iterator(); iter.hasNext(); ) {
				Object[] obj = (Object[]) iter.next();
				logger.info("letter_code::"+ obj[4] +" disp_seq_ref::" +obj[5]+" disp_seq_id::"+ obj[6]);					
				AuditObservationLetterDto ltDto = new AuditObservationLetterDto();
				ltDto.setLegalEntityCode(legalEntityCode);
				ltDto.setAuditId(auditId);
				if(obj[2] !=null)ltDto.setAuditTypeCode(obj[2].toString());
				if(obj[3] !=null)ltDto.setStatementId(obj[3].toString());
				if(obj[4] !=null)ltDto.setDerivedStatement(obj[4].toString());
				if(obj[5] !=null)ltDto.setDeriveStatementOPStmnt(obj[5].toString());
				if(obj[6] !=null)ltDto.setLetterCode(obj[6].toString());
				if(obj[7] !=null)ltDto.setDisplaySequenceRef(obj[7].toString());
				if(obj[8] !=null)ltDto.setDisplaySequenceId(obj[8].toString());
				if(obj[9] !=null)ltDto.setDisplaySequence(obj[9].toString());
										
				drvStmntMap.put(ltDto.getDisplaySequenceRef(), ltDto);
				}
			}catch(Exception e) {
				e.printStackTrace(System.out);
			}logger.info("Map is .."+ drvStmntMap);
		return drvStmntMap; 
	}
	
	@Override
	public void deleteAuditLetterByIdCode(int legalEntityCode, String auditId,String letterCode, String status) {
		Session session = getSession();
		logger.info("deleteAuditLetterByIdCode .. "+ legalEntityCode+" ::"+auditId+" ::"+letterCode+" ::"+status);
		if (BankAuditConstant.STATUS_AUTH.equalsIgnoreCase(status)) {
			session.createQuery("delete from AuditLetterUserInputs where legalEntityCode=:legalEntityCode and auditId=:auditId and letterCode=:letterCode ")
				.setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId).setParameter("letterCode", letterCode).executeUpdate();
			
			session.createQuery("delete from AuditLetterBiLingual where legalEntityCode =:legalEntityCode and auditId =:auditId and letterCode=:letterCode ")
			.setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId).setParameter("letterCode", letterCode)
			.executeUpdate();	
		} else {
			session.createQuery("delete from AuditLetterUserInputsWrk where legalEntityCode=:legalEntityCode and auditId=:auditId and letterCode=:letterCode ")
			    .setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId).setParameter("letterCode", letterCode).executeUpdate();
			
			session.createQuery("delete from AuditLetterBiLingualWrk where legalEntityCode =:legalEntityCode and auditId =:auditId and letterCode=:letterCode ")
			.setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId).setParameter("letterCode", letterCode)
			.executeUpdate();	
		}
	}		
	
	@Override
	public String getDocReferencePath(int legalEntityCode, String auditId, String letterCode, String auditTypeCode ){
		Session session = getSession();
		session.setDefaultReadOnly(true);
		
		String queryString="  Select referencePath from "
				+ " AuditLetterReportDocumentDetails doc  "
				+ " where "
				+ " doc.legalEntityCode =:legalEntityCode"					
				+ " and doc.auditId=:auditId "
				+ " and doc.letterCode=:letterCode"
				+ " and auditTypeCode=:auditTypeCode ";
		
		
		Query query= session.createQuery(queryString.toString()) 
		 .setParameter("legalEntityCode", legalEntityCode)
		 .setParameter("auditId", auditId)
		 .setParameter("letterCode", letterCode)
		 .setParameter("auditTypeCode", auditTypeCode);
		
		return (String) query.uniqueResult() ;
	}
	
	@Override
	public List<String> getLettersPdfFiles(Integer legalEntityCode, String auditId, String auditTypeCode, String letterDocumentTypeCode){
		Session session = getSession();		    
  		session.setDefaultReadOnly(true);
  		 
  		String tableStr= "Select referencePath from AuditLevelDocumentDetails  "  
  				+ " where legalEntityCode =:legalEntityCode "
  				+ " and auditTypeCode =:auditTypeCode "
  				+ " and auditId =:auditId "
  				+ " and documentType =:documentType "
  				+ " and uploadStatus =:uploadStatus " 
  				+ " order by CAST(seqId  as int) asc ";
  		 
  		Query table = session.createQuery(tableStr)
  				.setParameter("legalEntityCode", legalEntityCode)
  				.setParameter("auditTypeCode", auditTypeCode)
  				.setParameter("auditId", auditId)
  				.setParameter("documentType", letterDocumentTypeCode)
  				.setParameter("uploadStatus", BankAuditConstant.SUCCESS);
  		
  		List<String> listPdf = table.list();    
		return listPdf;
	}
	
	@Override
	public DataTableResponse getLetterForReviewer(int legalEntityCode, String userId, String search,Integer orderColumn,
			String orderDirection, Integer page, Integer size, String role, String userUnitType) {
		Session session = getSession();
		session.setDefaultReadOnly(true);
		
		logger.info("Inside getLetterForReviewer .... ");
		
		StringBuilder queryString=new StringBuilder(" SELECT sch as auditSchedule, "  //0
				+ "   wrk.letterCode, wrk.status , wrk.reviewer ,  "  // 3
				+ "   (Select g.description from  GeneralParameter g  Where g.legalEntityCode = sch.legalEntityCode AND  wrk.letterCode=g.value "
				+ "         AND g.key1 LIKE '%LETTER%' AND (g.key2 LIKE '%AUDIT' OR g.key2 LIKE 'AUDIT_BEFORE'))  as letterDescription,  "  // 4
				+ "   (select concat(auditTypeCode , '-' , auditTypeDesc) from MaintAuditTypeDesc at where at.auditTypeCode=sch.auditTypeCode and at.legalEntityCode= sch.legalEntityCode ) as auditTypeCode, " // 5 
				//+ "   (SELECT CONCAT(unitCode , '-' , unitName) FROM  MaintEntity e WHERE e.unitCode =sch.unitId AND e.legalEntityCode =sch.legalEntityCode ) AS unitName,   "  // 6
				+ "   (SELECT unitName FROM  MaintEntity e WHERE e.unitCode =sch.unitId AND e.legalEntityCode =sch.legalEntityCode ) AS unitName,   "  // 6
				+ "   (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,  "   // 7
				+ "   (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,   "   // 8
				+ "   (SELECT CONCAT(u.userId , '-' , IFNULL(u.firstName,''), ' ',IFNULL(u.middleName,''),' ',IFNULL(u.lastName,'') )  " 
				+ " 		FROM  User u WHERE u.legalEntityCode =sch.legalEntityCode AND u.userId=wrk.maker) AS letterMaker, "   // 9
				+ "   (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 reportStatus,"  // 10
				+ "   (Select concat(e1.unitName , '-' , e1.unitCode) from  MaintEntity e, MaintEntity e1  where e.unitCode =sch.unitId "
				+ "         AND e.legalEntityCode =sch.legalEntityCode and e1.unitCode = e.parentUnitCode and  e1.legalEntityCode =sch.legalEntityCode) as parentUnitCode  " // 11
			 	+ "   , sch.unitId " // 12
				+ "  FROM  AuditSchedule sch " 
				+ "  INNER JOIN AuditLetterUserInputsWrk wrk " 
				+ "  ON  sch.auditId=wrk.auditId AND sch.auditTypeCode=wrk.auditTypeCode AND wrk.legalEntityCode = sch.legalEntityCode"				 
				+ "  Where sch.legalEntityCode=:legalEntityCode  "				
				+ "  AND wrk.status = '"+BankAuditConstant.STATUS_UNAUTH+"' AND wrk.reviewer = :reviewerId  ");			
				
			if(!BankAuditUtil.isEmptyString(search)){
				queryString=queryString.append(" and ( "
					+ " sch.auditTypeCode IN (SELECT a.auditTypeCode FROM MaintAuditTypeDesc a  WHERE a.legalEntityCode=sch.legalEntityCode AND (a.auditTypeDesc LIKE :search OR a.auditTypeCode LIKE :search)) "
					+ " or sch.planId like :search "
					+ " or sch.auditId like :search " 
					+ " or sch.unitId IN (SELECT m.unitCode FROM MaintEntity m WHERE m.legalEntityCode=sch.legalEntityCode AND (m.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 wrk.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 sch.auditStatus IN (SELECT g.value FROM GeneralParameter g WHERE sch.legalEntityCode =g.legalEntityCode and g.key1='STATUS' "
						+ " and g.key2='AUDIT_STATUS' and g.value=sch.auditStatus "
						+ " and (g.description LIKE :search or g.value LIKE :search)"
						+ " and   ( sch.observationStatus !='AE'  )   ) "							
					+ " or sch.auditStatus IN (SELECT g.value FROM GeneralParameter g WHERE sch.legalEntityCode =g.legalEntityCode and g.key1='STATUS' "
					+ " and g.key2='AUDIT_STATUS' and g.value=sch.auditStatus "
					+ " and (g.description LIKE :search or g.value LIKE :search)"
					+ " and   ( sch.observationStatus  ='AE' and sch.auditStatus='AW'  )  )  " );
						
					if((search.length()==2 && search.toUpperCase().startsWith("EX") )
					|| (search.length()==3 && search.toUpperCase().startsWith("EXI"))
					|| (search.length()==4 && search.toUpperCase().startsWith("EXIT"))
					|| ((search.length()==5 || search.length()==6) && search.toUpperCase().startsWith("EXITE")) ){
						queryString.append("Or (sch.auditStatus='AI' and sch.observationStatus='AE' ) ");
					}	
					else{
						//queryString.append(" )" );
						if(search.toUpperCase().startsWith("INI")){
							queryString.append("OR  ( sch.auditStatus='AI' and (sch.observationStatus is null or sch.observationStatus='' ) ) ");
						}
					} 
					queryString.append(" )" );
			}
							
			queryString.append("  ORDER BY FIELD(wrk.letterCode,'Dr','FwdL','RN','HoR','RoR','HoLoSC','RoLoSC' ) ASC  ");
		
			logger.info("Before the qry...."+queryString);
			
			Query query=session.createQuery(queryString.toString())
					.setResultTransformer(new ResultTransformer() {
						@Override
						public Object transformTuple(Object[] tuple, String[] aliases) {
							AuditSchedule auditSchedule=null; 
							LettersDto ltDto = null;
							if(tuple[0]!=null){
								ltDto = new LettersDto();
								auditSchedule=(AuditSchedule)tuple[0];
								
								ltDto.setAuditId(auditSchedule.getAuditId());
								ltDto.setLegalEntityCode(legalEntityCode);
								ltDto.setPlanId(auditSchedule.getPlanId());
								ltDto.setObservationStatus(auditSchedule.getObservationStatus());
								
								ltDto.setLetterCode(String.valueOf(tuple[1]));
								ltDto.setStatus(String.valueOf(tuple[2]));								
								ltDto.setReviewer(String.valueOf(tuple[3]));								
								ltDto.setLetterDesc(String.valueOf(tuple[4]));
								ltDto.setAuditTypeCode(String.valueOf(tuple[5]));
								//ltDto.setUnitId(String.valueOf(tuple[6]));
								/*if(tuple[12] != null){
									ltDto.setUnitName(tuple[6].toString().split("-")[1]);
								}*/
								ltDto.setUnitId(tuple[12].toString()+"-"+tuple[6].toString());
								ltDto.setUnitName(tuple[6].toString());
								
								ltDto.setUnitType(String.valueOf(tuple[7]));
								ltDto.setAuditStatus(String.valueOf(tuple[8]));
								ltDto.setMaker(String.valueOf(tuple[9]));
								ltDto.setReportStatus(String.valueOf(tuple[10])); 
								ltDto.setParentUnitCodeName(String.valueOf(tuple[11]));
								ltDto.setFinancialYear(auditSchedule.getFinancialYear());
							}
							return ltDto;
						}
				@Override
				public List transformList(List collection) {
					return collection;
				}
			})
			.setParameter("legalEntityCode", legalEntityCode)
			.setParameter("reviewerId", userId)
			.setParameter("letterCodeGrading", BankAuditConstant.LETTER_CODE_IR_GRADING);
			if(!BankAuditUtil.isEmptyString(search)){
				query.setParameter("search", "%"+search+"%");
			}
			
			
		List<LettersDto> lettersLstCount   = query.list();		  
		Long totalRecords=null;
		try {
			totalRecords = (long)lettersLstCount.size();
		}catch(Exception e) {
			totalRecords = (long)0;
		}
		List<LettersDto> lettersLst=query.setFirstResult((page*size)).setMaxResults(size).list();
		
		
		DataTableResponse dataTableResponse=new DataTableResponse();
		dataTableResponse.setData(lettersLst);
		dataTableResponse.setRecordsTotal(totalRecords);
		dataTableResponse.setRecordsFiltered(totalRecords);
		
		return dataTableResponse; 		
	}
	
	@Override
	public Integer isRatingCompleted(Integer legalEntityCode, String auditTypeCode, String auditId) {
		Session session = getSession();
		session.setDefaultReadOnly(true);
		
		String queryString = "Select count(auditId) From AuditSchedule sch"
				+ " where legalEntityCode =:legalEntityCode "
				+ " and auditTypeCode = :auditTypeCode "
				+ " and auditId = :auditId "
				+ " and ratingParameterStatus IN ('RPR','RPHU','RPS') ";
		
		return ((Long) session.createQuery(queryString)
		.setParameter("legalEntityCode", legalEntityCode)
		.setParameter("auditTypeCode", auditTypeCode)
		.setParameter("auditId", auditId)
		.uniqueResult()).intValue();		
	}
	
	@Override
	public Integer isLetterAuthorised(Integer legalEntityCode, String auditTypeCode, String auditId, String letterCode) {
		Session session = getSession();
		session.setDefaultReadOnly(true);
		logger.info("Inside isLetterAuthorised .. "+legalEntityCode +" :: "+auditTypeCode+" ::"+ auditId+" :: "+ letterCode);
		String queryString = "Select count(auditId) From AuditLetterUserInputs l "
				+ " WHERE legalEntityCode =:legalEntityCode AND auditTypeCode =:auditTypeCode AND auditId =:auditId  "
				+ "   AND letterCode=:letterCode AND status='A' " ;
		
		return ((Long) session.createQuery(queryString)
		.setParameter("legalEntityCode", legalEntityCode).setParameter("auditTypeCode", auditTypeCode)
		.setParameter("auditId", auditId).setParameter("letterCode", letterCode)
		.uniqueResult()).intValue();		
	}
	
	@Override
	public Map<String, String> getAuditRatingCompositeCategoryScoreByAuditId(Integer legalEntityCode, String auditTypeCode, String auditId){
		
		Session session = getSession();
		session.setDefaultReadOnly(true);
		
		String queryString = "From  AuditRatingCompositeCategoryScore rating "
				+ " where legalEntityCode =:legalEntityCode "
				+ " and auditTypeCode = :auditTypeCode "
				+ " and auditId = :auditId  ";
		
		List<AuditRatingCompositeCategoryScore> auditRatingCompositeCategoryScoreList = session.createQuery(queryString)
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("auditId", auditId).list();
		Map<String, String> compositeRatingMap = new HashMap<>();
		if(auditRatingCompositeCategoryScoreList!=null && auditRatingCompositeCategoryScoreList.size()>0){
			for (AuditRatingCompositeCategoryScore auditRatingCompositeCategoryScore : auditRatingCompositeCategoryScoreList) {
				//compositeRatingMap.put(auditRatingCompositeCategoryScore.getCategoryCode(), auditRatingCompositeCategoryScore.getCategoryRating()); // Commented 
				compositeRatingMap.put(auditRatingCompositeCategoryScore.getCategoryCode(), auditRatingCompositeCategoryScore.getCategoryRatingAftrOverlay());  // To display the afterOverlay Rating in RN
			}
		}
		
		return compositeRatingMap;
	}
	
	@Override
	public DataTableResponse getAllLetterForReviewer(int legalEntityCode, String userId, String search,Integer orderColumn,
			String orderDirection, Integer page, Integer size, String role, String userUnitType, List<String> unitList,
			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("Inside getLetterForReviewer .... ");
		LocalDate fYStartDt=null,fYearEndDt=null; 
		if(financialYear !=null) {
			fYStartDt = financialYearStrtEndList.get(0);
			fYearEndDt = financialYearStrtEndList.get(1);
			logger.info("fYStartDt :: "+fYStartDt+" fYEndDt :: "+fYearEndDt);			
		}
		
		List<String> ratingParamStatusList = Arrays.asList(BankAuditConstant.RATING_STATUS_SUPERVISORY_AUTH_RO_HO_ARRAY);
		StringBuilder queryString=new StringBuilder(" Select legalEntityCode, auditTypeCode, auditId, planId, financialYear, unitId, auditStatus, reportStatus, observationStatus, "
				+ " letterCode, status, reviewer, letterMakerId, letterDescription, auditTypeCodeDesc, unitName, unitType, auditStatusDesc, letterMaker, reportStatusDesc, "
				+ " parentUnitCode, gradingStatus, gradingStatusRO, gradingStatusHO, aioPio, reviewerName  FROM ");		
		queryString.append( " (SELECT sch.legal_entity_code as legalEntityCode, sch.audit_type_code as auditTypeCode, sch.audit_id as auditId,   "   
				+ " sch.plan_id  as planId, "
			 	+ " sch.financial_year as financialYear, "  
			 	+ " sch.unit_id as unitId, "  		
			 	+ " sch.audit_status as auditStatus, "  
			 	+ " sch.report_status as reportStatus, "  	
			 	+ " sch.observation_status  as observationStatus,  "  
				+ " wrk.letter_code as letterCode , "
				+ " wrk.status as status ,"
				+ " wrk.reviewer as reviewer,  "
				+ " wrk.maker as letterMakerId, "   				
				+ " (Select g.description from  general_parameter g  Where g.legal_entity_code = sch.legal_entity_code AND  wrk.letter_code=g.value "
				+ "         AND g.key_1 LIKE '%LETTER%' AND (g.key_2 LIKE '%AUDIT' OR g.key_2 LIKE 'AUDIT_BEFORE'))  as letterDescription,  "  			
				+ " (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 auditTypeCodeDesc, "  			 
				+ " (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 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 auditStatusDesc,   "   			
				+ " (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=wrk.maker) AS letterMaker, "   			
				+ " (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,"   			
				+ " (Select concat(e1.unit_name , '-' , e1.unit_code) from  maint_entity e, maint_entity e1  where e.unit_code =sch.unit_id "
				+ "         AND e.legal_entity_code =sch.legal_entity_code and e1.unit_code = e.parent_unit_code and  e1.legal_entity_code =sch.legal_entity_code) as parentUnitCode,"
				+ " wrk.grading_status as gradingStatus,   "  
				+ " schStatus.grading_status_ro as gradingStatusRO, "
				+ " schStatus.grading_status_ho as gradingStatusHO, "
				+ " (SELECT group_concat(user_id separator '##') FROM  audit_team team WHERE sch.legal_entity_code = team.legal_entity_code AND sch.audit_type_code = team.audit_type_code "
				+ " 		AND sch.audit_id = team.audit_id) AS aioPio "  
				+ " ,(SELECT CONCAT( 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=wrk.reviewer) AS reviewerName "  
				+ " FROM  audit_schedule sch " 
				+ " INNER JOIN audit_letter_user_inputs_wrk wrk " 
				+ " ON  sch.audit_id=wrk.audit_id AND sch.audit_type_code=wrk.audit_type_code AND sch.legal_entity_code = wrk.legal_entity_code"  
				+ " LEFT JOIN audit_schedule_status schStatus "
				+ " ON sch.audit_id=schStatus.audit_id AND sch.audit_type_code=schStatus.audit_type_code AND sch.legal_entity_code = schStatus.legal_entity_code " 
				+ " WHERE sch.legal_entity_code = :legalEntityCode  " 			
				+ " AND wrk.status IN ('"+BankAuditConstant.STATUS_UNAUTH+"','"+BankAuditConstant.GRADING_STATUS_PLANNED+"','"+BankAuditConstant.GRADING_STATUS_IN_PROGRESS+"')"
				+ " AND ( CASE WHEN wrk.letter_code <> :letterCodeGrading THEN  wrk.reviewer = :reviewerId  "
						+ " ELSE ( wrk.letter_code = :letterCodeGrading  "  
										+ " AND sch.report_status = :reportStatus"
										+ " AND sch.rating_parameter_status in (:supervisoryRatingParameterStatus) " 
										+ " AND (select count(ltr.audit_id) from audit_letter_user_inputs ltr WHERE ltr.letter_code = :letterCodeRN "
											+ "  AND ltr.audit_id = sch.audit_id AND ltr.legal_entity_code = sch.legal_entity_code  ) > 0 "); 
		 
				if(BankAuditConstant.UNIT_TYPE_RO.equalsIgnoreCase(userUnitType)){		
					queryString.append( " AND (sch.audit_id IN ( Select rating.audit_id from audit_rating rating WHERE rating.composite_rating_aftr_overlay in ('A','B','I','II') "
													//	+ " AND rating.audit_type_code NOT IN ('"+BankAuditConstant.INSPECTION_TYPE_RRB+"', '"+BankAuditConstant.INSPECTION_TYPE_RRBEI+"') "
														+ " AND sch.legal_entity_code = rating.legal_entity_code "
														+ " AND rating.composite_rating_status = :compositeRatingStatus) ) "
														+ " AND (sch.unit_id IN (:unitList) OR wrk.reviewer = :reviewerId ) "
														+ " ) END " );	
					 
				}else{
					queryString.append( " AND (sch.audit_id IN ( Select rating.audit_id from audit_rating rating WHERE rating.composite_rating_aftr_overlay in ('C','D','III','IV') "
														+ " AND sch.legal_entity_code = rating.legal_entity_code "
														+ " AND rating.composite_rating_status = :compositeRatingStatus) "
														+ " OR "
														+ " wrk.grading_status IN ('"+BankAuditConstant.GRADING_STATUS_RO_AUTH+"', '"+BankAuditConstant.GRADING_STATUS_HO_UNAUTH+"',"
																+ " '"+BankAuditConstant.GRADING_STATUS_HO_SUBMITTED+"', '"+BankAuditConstant.GRADING_STATUS_HO_AUTH+"' ) ) )END "   );	
				}
				queryString.append( " ) ");
				
				// 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 sch.legal_entity_code as legalEntityCode, sch.audit_type_code as auditTypeCode, sch.audit_id as auditId,   "   
				+ " sch.plan_id  as planId, "
			 	+ " sch.financial_year as financialYear, "  
			 	+ " sch.unit_id as unitId, "  		
			 	+ " sch.audit_status as auditStatus, "  
			 	+ " sch.report_status as reportStatus, "  	
			 	+ " sch.observation_status  as observationStatus,  "  
				+ " ltr.letter_code as letterCode , "
				+ " ltr.status as status ,"
				+ " ltr.reviewer as reviewer,  "
				+ " ltr.maker as letterMakerId, "   				
				+ " (Select g.description from  general_parameter g  Where g.legal_entity_code = sch.legal_entity_code AND  ltr.letter_code=g.value "
				+ "         AND g.key_1 LIKE '%LETTER%' AND (g.key_2 LIKE '%AUDIT' OR g.key_2 LIKE 'AUDIT_BEFORE'))  as letterDescription,  "  			
				+ " (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 auditTypeCodeDesc, "  			 
				+ " (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 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 auditStatusDesc,   "   			
				+ " (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=ltr.maker) AS letterMaker, "   			
				+ " (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,"   			
				+ " (Select concat(e1.unit_name , '-' , e1.unit_code) from  maint_entity e, maint_entity e1  where e.unit_code =sch.unit_id "
				+ "         AND e.legal_entity_code =sch.legal_entity_code and e1.unit_code = e.parent_unit_code and  e1.legal_entity_code =sch.legal_entity_code) as parentUnitCode,   "  
				+ " ltr.grading_status as gradingStatus,"
				+ " schStatus.grading_status_ro as gradingStatusRO , "
				+ " schStatus.grading_status_ho as gradingStatusHO, "
				+ " (SELECT group_concat(user_id separator '##') FROM  audit_team team WHERE sch.legal_entity_code = team.legal_entity_code AND sch.audit_type_code = team.audit_type_code "
				+ " 		AND sch.audit_id = team.audit_id) AS aioPio "  
				+ " ,(SELECT CONCAT( 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=ltr.reviewer) AS reviewerName " 
				+ " FROM  audit_schedule sch " 
				+ " INNER JOIN audit_letter_user_inputs ltr " 
				+ " ON  sch.audit_id=ltr.audit_id AND sch.audit_type_code=ltr.audit_type_code AND sch.legal_entity_code = ltr.legal_entity_code "
				+ " LEFT JOIN audit_schedule_status schStatus "
				+ " ON sch.audit_id=schStatus.audit_id AND sch.audit_type_code=schStatus.audit_type_code AND sch.legal_entity_code = schStatus.legal_entity_code "  
				+ " WHERE sch.legal_entity_code = :legalEntityCode  " 			
				+ " AND ltr.status = '"+BankAuditConstant.STATUS_AUTH+"'  and ltr.letter_code = :letterCodeGrading "	);
				
				if(BankAuditConstant.UNIT_TYPE_RO.equalsIgnoreCase(userUnitType)){		
					queryString.append( " AND sch.audit_id IN ( Select rating.audit_id from audit_rating rating WHERE rating.composite_rating_aftr_overlay in ('A','B','I','II') "
														//+ " AND rating.audit_type_code NOT IN ('"+BankAuditConstant.INSPECTION_TYPE_RRB+"', '"+BankAuditConstant.INSPECTION_TYPE_RRBEI+"') "
														+ " AND sch.legal_entity_code = rating.legal_entity_code ) " );
					queryString.append(" AND sch.unit_id IN (:unitList) ");
				}else{
					/*queryString.append( " AND (sch.audit_id IN ( Select rating.audit_id from audit_rating rating WHERE rating.composite_rating_aftr_overlay in ('C','D','III','IV') "
														+ " AND sch.legal_entity_code = rating.legal_entity_code ) )"   );*/	
				}
				
				// 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( " ) tmpTable " );	 
 				
			if(!BankAuditUtil.isEmptyString(search)){
				queryString=queryString.append(" Where ( "
						+ " tmpTable.auditTypeCode IN (SELECT a.audit_type_code FROM maint_audit_type_desc a  WHERE a.legal_entity_code=tmpTable.legalEntityCode AND (a.audit_type_desc LIKE :search OR a.audit_type_code LIKE :search)) "
						+ " or tmpTable.planId like :search "
						+ " or tmpTable.auditId like :search " 
						+ " or tmpTable.unitId IN (SELECT m.unit_code FROM maint_entity m WHERE m.legal_entity_code=tmpTable.legalEntityCode AND (m.unit_name LIKE :search or m.unit_code LIKE :search )) "
						+ " or tmpTable.unitId IN (SELECT m.unit_code FROM maint_entity m, general_parameter g WHERE tmpTable.unitId=m.unit_code and m.legal_entity_code=tmpTable.legalEntityCode 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 tmpTable.unitId IN (SELECT m.unit_code FROM maint_entity m, maint_criticality c WHERE tmpTable.unitId=m.unit_code and m.legal_entity_code=tmpTable.legalEntityCode 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 tmpTable.unitId IN (SELECT m.unit_code FROM maint_entity m, maint_audit_parameter p WHERE tmpTable.unitId=m.unit_code and m.legal_entity_code=tmpTable.legalEntityCode AND m.legal_entity_code =p.legal_entity_code and m.u_criticality=p.u_criticality and tmpTable.auditTypeCode=p.audit_type_code and  p.duration LIKE :search) "
						+ " or tmpTable.auditId IN (SELECT t.audit_id FROM user u, audit_team t WHERE t.audit_id=tmpTable.auditId and t.legal_entity_code =tmpTable.legalEntityCode 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 tmpTable.letterMakerId IN (SELECT u.user_id FROM user u WHERE u.legal_entity_code =tmpTable.legalEntityCode and (u.user_id Like :search or u.first_name LIKE :search or u.last_name Like :search) ) "
						+ " or tmpTable.auditStatus IN (SELECT g.value FROM general_parameter g WHERE tmpTable.legalEntityCode =g.legal_entity_code and g.key_1='STATUS' and g.key_2='AUDIT_STATUS' and g.value=tmpTable.auditStatus and g.description LIKE :search) "
						+ " or tmpTable.reportStatus IN (SELECT g.value FROM general_parameter g WHERE tmpTable.legalEntityCode =g.legal_entity_code and g.key_1='STATUS' and g.key_2='REPORT_STATUS' and g.value=tmpTable.reportStatus and g.description LIKE :search) "
						+ " or tmpTable.auditStatus IN (SELECT g.value FROM general_parameter g WHERE tmpTable.legalEntityCode =g.legal_entity_code and g.key_1='STATUS' and g.key_2='AUDIT_STATUS' and g.value=tmpTable.auditStatus and (g.description LIKE :search or g.value LIKE :search) and   ( tmpTable.observationStatus !='AE'  )   ) "							
						+ " or tmpTable.auditStatus IN (SELECT g.value FROM general_parameter g WHERE tmpTable.legalEntityCode =g.legal_entity_code and g.key_1='STATUS' and g.key_2='AUDIT_STATUS' and g.value=tmpTable.auditStatus  and (g.description LIKE :search or g.value LIKE :search) and   ( tmpTable.observationStatus  ='AE' and tmpTable.auditStatus='AW'  )  )  " );
							
						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 (tmpTable.auditStatus='AI' and tmpTable.observationStatus='AE' ) ");
						}	
						else{ 
							if(search.toUpperCase().startsWith("INI")){
								queryString.append("OR  ( tmpTable.auditStatus='AI' and (tmpTable.observationStatus is null or tmpTable.observationStatus='' ) ) ");
							}
						} 
						queryString.append(" )" );
			}
							
			queryString.append("  ORDER BY FIELD(tmpTable.letterCode,'Dr','FwdL','RN','HoR','RoR','HoLoSC','RoLoSC','IR_GRADING' ) ASC  ");
		
			logger.info("Before the qry...."+queryString);
			
			Query query=session.createNativeQuery(queryString.toString())
					.setResultTransformer(new ResultTransformer() {
						@Override
						public Object transformTuple(Object[] tuple, String[] aliases) { 
							LettersDto ltDto = null;
							if(tuple[0]!=null){
								ltDto = new LettersDto();								 								
								ltDto.setLegalEntityCode(legalEntityCode);
								ltDto.setAuditId(String.valueOf(tuple[2]));								
								ltDto.setPlanId(String.valueOf(tuple[3]));
								ltDto.setFinancialYear(tuple[4]!=null?(Date)tuple[4]:null);
								ltDto.setObservationStatus(String.valueOf(tuple[8]));								
								ltDto.setLetterCode(String.valueOf(tuple[9]));								
								ltDto.setStatus(String.valueOf(tuple[10]));								
								ltDto.setReviewer(String.valueOf(tuple[11]));								
								ltDto.setLetterDesc(String.valueOf(tuple[13]));								
								ltDto.setAuditTypeCode(String.valueOf(tuple[14]));								 
								ltDto.setUnitId(tuple[5].toString()+"-"+tuple[15].toString());
								ltDto.setUnitName(tuple[15].toString());								
								ltDto.setUnitType(String.valueOf(tuple[16]));
								ltDto.setAuditStatus(String.valueOf(tuple[17]));
								ltDto.setMaker(tuple[18]!=null?tuple[18].toString():"");
								ltDto.setReportStatus(String.valueOf(tuple[19])); 
								ltDto.setParentUnitCodeName(String.valueOf(tuple[20]));
								ltDto.setGradingStatus(String.valueOf(tuple[21]));
								ltDto.setGradingStatusRO(tuple[22]!=null?tuple[22].toString():"");
								ltDto.setGradingStatusHO(tuple[23]!=null?tuple[23].toString():"");
								ltDto.setTeam(tuple[24]!=null?tuple[24].toString().split("##"):null);
								ltDto.setReviewerName(tuple[25]!=null?tuple[25].toString():""); // Reviewer name as part of UI/UX change
							}
							return ltDto;
						}
				@Override
				public List transformList(List collection) {
					return collection;
				}
			})
			.setParameter("legalEntityCode", legalEntityCode)
			.setParameter("reviewerId", userId)			 
			.setParameter("letterCodeGrading", BankAuditConstant.LETTER_CODE_IR_GRADING)
			.setParameter("reportStatus", BankAuditConstant.REPORT_STATUS_IR_DISPATCH)
			.setParameter("supervisoryRatingParameterStatus", ratingParamStatusList)
			.setParameter("compositeRatingStatus", BankAuditConstant.RATING_STATUS_COMPOSITE_AUTH)
			.setParameter("letterCodeRN", BankAuditConstant.LETTER_CODE_RN);
			if(BankAuditConstant.UNIT_TYPE_RO.equalsIgnoreCase(userUnitType)){
				query.setParameterList("unitList", unitList);
			}
			
			if(!BankAuditUtil.isEmptyString(search)){
				query.setParameter("search", "%"+search+"%");
			}
			
			if(!BankAuditUtil.isEmptyString(auditTypeCode)) {
				query.setParameter("auditTypeCode", auditTypeCode);
			}
			
		List<LettersDto> lettersLstCount   = query.list();		  
		Long totalRecords=null;
		try {
			totalRecords = (long)lettersLstCount.size();
		}catch(Exception e) {
			totalRecords = (long)0;
		}
		List<LettersDto> lettersLst=query.setFirstResult((page*size)).setMaxResults(size).list();
		
		
		DataTableResponse dataTableResponse=new DataTableResponse();
		dataTableResponse.setData(lettersLst);
		dataTableResponse.setRecordsTotal(totalRecords);
		dataTableResponse.setRecordsFiltered(totalRecords);
		
		return dataTableResponse; 		
	} 
		
	@Override
	public DataTableResponse getLetterForReviewerGradingCompliance(int legalEntityCode, String userId, String search,Integer orderColumn,
			String orderDirection, Integer page, Integer size, String role, String userUnitType,
			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("Inside getAllLetterForReviewerGradingCompliance .... ");
		LocalDate fYStartDt=null,fYearEndDt=null; 
		if(financialYear !=null) {
			fYStartDt = financialYearStrtEndList.get(0);
			fYearEndDt = financialYearStrtEndList.get(1);
			logger.info("fYStartDt :: "+fYStartDt+" fYEndDt :: "+fYearEndDt);			
		}
		 
		StringBuilder queryString=new StringBuilder(" Select legalEntityCode, auditTypeCode, auditId, planId, financialYear, unitId, auditStatus, reportStatus, observationStatus, "
				+ " letterCode, status, reviewer, letterMakerId, letterDescription, auditTypeCodeDesc, unitName, unitType, auditStatusDesc, letterMaker, reportStatusDesc, "
				+ " parentUnitCode, gradingStatus, gradingStatusRO, gradingStatusHO, aioPio , gradingComplianceStatus, gradingROComplianceStatus, gradingHOComplianceStatus FROM ");		
		queryString.append( " (SELECT sch.legal_entity_code as legalEntityCode, sch.audit_type_code as auditTypeCode, sch.audit_id as auditId,   "   
				+ " sch.plan_id  as planId, "
			 	+ " sch.financial_year as financialYear, "  
			 	+ " sch.unit_id as unitId, "  		
			 	+ " sch.audit_status as auditStatus, "  
			 	+ " sch.report_status as reportStatus, "  	
			 	+ " sch.observation_status  as observationStatus,  "  
				+ " wrk.letter_code as letterCode , "
				+ " wrk.status as status ,"
				+ " wrk.reviewer as reviewer,  "
				+ " wrk.maker as letterMakerId, "   				
				+ " (Select g.description from  general_parameter g  Where g.legal_entity_code = sch.legal_entity_code AND  wrk.letter_code=g.value "
				+ "         AND g.key_1 LIKE '%LETTER%' AND (g.key_2 LIKE '%AUDIT' OR g.key_2 LIKE 'AUDIT_BEFORE'))  as letterDescription,  "  			
				+ " (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 auditTypeCodeDesc, "  			 
				+ " (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 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 auditStatusDesc,   "   			
				+ " (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=wrk.maker) AS letterMaker, "   			
				+ " (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,"   			
				+ " (Select concat(e1.unit_name , '-' , e1.unit_code) from  maint_entity e, maint_entity e1  where e.unit_code =sch.unit_id "
				+ "         AND e.legal_entity_code =sch.legal_entity_code and e1.unit_code = e.parent_unit_code and  e1.legal_entity_code =sch.legal_entity_code) as parentUnitCode,"
				+ " wrk.grading_status as gradingStatus,   "  
				+ " schStatus.grading_status_ro as gradingStatusRO, "
				+ " schStatus.grading_status_ho as gradingStatusHO, "			
				+ " (SELECT group_concat(user_id separator '##') FROM  audit_team team WHERE sch.legal_entity_code = team.legal_entity_code AND sch.audit_type_code = team.audit_type_code "
				+ " 		AND sch.audit_id = team.audit_id) AS aioPio, " 
				+ " wrk.grading_compliance_status as gradingComplianceStatus, "				
				+ " schStatus.grading_ro_compliance_status as gradingROComplianceStatus, "
				+ " schStatus.grading_ho_compliance_status as gradingHOComplianceStatus  "				
				+ " FROM  audit_schedule sch " 
				+ " INNER JOIN audit_letter_user_inputs_wrk wrk " 
				+ " ON  sch.audit_id=wrk.audit_id AND sch.audit_type_code=wrk.audit_type_code AND sch.legal_entity_code = wrk.legal_entity_code"  
				+ " LEFT JOIN audit_schedule_status schStatus "
				+ " ON sch.audit_id=schStatus.audit_id AND sch.audit_type_code=schStatus.audit_type_code AND sch.legal_entity_code = schStatus.legal_entity_code " 
				+ " WHERE sch.legal_entity_code = :legalEntityCode  " 			
				+ " AND wrk.status IN ('"+BankAuditConstant.STATUS_UNAUTH+"', '"+BankAuditConstant.GRADING_COMPLIANCE_STATUS_PLANNED+"', '"+BankAuditConstant.GRADING_COMPLIANCE_STATUS_IN_PROGRESS+"' ) "
				+ " AND (wrk.letter_code = :letterCodeGradingROCompliance OR wrk.letter_code = :letterCodeGradingHOCompliance)"
				//+ " AND (wrk.reviewer = :reviewerId  OR wrk.reviewer IS NULL )"
				+ " ");		
			
				// 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( " ");				
				queryString.append(" UNION ALL " );
				
				queryString.append( " SELECT sch.legal_entity_code as legalEntityCode, sch.audit_type_code as auditTypeCode, sch.audit_id as auditId,   "   
				+ " sch.plan_id  as planId, "
			 	+ " sch.financial_year as financialYear, "  
			 	+ " sch.unit_id as unitId, "  		
			 	+ " sch.audit_status as auditStatus, "  
			 	+ " sch.report_status as reportStatus, "  	
			 	+ " sch.observation_status  as observationStatus,  "  
				+ " ltr.letter_code as letterCode , "
				+ " ltr.status as status ,"
				+ " ltr.reviewer as reviewer,  "
				+ " ltr.maker as letterMakerId, "   				
				+ " (Select g.description from  general_parameter g  Where g.legal_entity_code = sch.legal_entity_code AND  ltr.letter_code=g.value "
				+ "         AND g.key_1 LIKE '%LETTER%' AND (g.key_2 LIKE '%AUDIT' OR g.key_2 LIKE 'AUDIT_BEFORE'))  as letterDescription,  "  			
				+ " (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 auditTypeCodeDesc, "  			 
				+ " (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 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 auditStatusDesc,   "   			
				+ " (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=ltr.maker) AS letterMaker, "   			
				+ " (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,"   			
				+ " (Select concat(e1.unit_name , '-' , e1.unit_code) from  maint_entity e, maint_entity e1  where e.unit_code =sch.unit_id "
				+ "         AND e.legal_entity_code =sch.legal_entity_code and e1.unit_code = e.parent_unit_code and  e1.legal_entity_code =sch.legal_entity_code) as parentUnitCode,   "  
				+ " ltr.grading_status as gradingStatus,"
				+ " schStatus.grading_status_ro as gradingStatusRO , "
				+ " schStatus.grading_status_ho as gradingStatusHO, "
				+ " (SELECT group_concat(user_id separator '##') FROM  audit_team team WHERE sch.legal_entity_code = team.legal_entity_code AND sch.audit_type_code = team.audit_type_code "
				+ " 		AND sch.audit_id = team.audit_id) AS aioPio, "  
				+ " ltr.grading_compliance_status as gradingComplianceStatus, "
				+ " schStatus.grading_ro_compliance_status as gradingROComplianceStatus, "
				+ " schStatus.grading_ho_compliance_status as gradingHOComplianceStatus "	
				+ " FROM  audit_schedule sch " 
				+ " INNER JOIN audit_letter_user_inputs ltr " 
				+ " ON  sch.audit_id=ltr.audit_id AND sch.audit_type_code=ltr.audit_type_code AND sch.legal_entity_code = ltr.legal_entity_code "
				+ " LEFT JOIN audit_schedule_status schStatus "
				+ " ON sch.audit_id=schStatus.audit_id AND sch.audit_type_code=schStatus.audit_type_code AND sch.legal_entity_code = schStatus.legal_entity_code "  
				+ " WHERE sch.legal_entity_code = :legalEntityCode  " 			
				+ " AND ltr.status = '"+BankAuditConstant.STATUS_AUTH+"'  "
				+ " AND (ltr.letter_code = :letterCodeGradingROCompliance OR ltr.letter_code = :letterCodeGradingHOCompliance)"
				+ "  "	);
				
				// 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( " ) tmpTable " );	 
 				
			if(!BankAuditUtil.isEmptyString(search)){
				queryString=queryString.append(" Where ( "
						+ " tmpTable.auditTypeCode IN (SELECT a.audit_type_code FROM maint_audit_type_desc a  WHERE a.legal_entity_code=tmpTable.legalEntityCode AND (a.audit_type_desc LIKE :search OR a.audit_type_code LIKE :search)) "
						+ " or tmpTable.planId like :search "
						+ " or tmpTable.auditId like :search " 
						+ " or tmpTable.unitId IN (SELECT m.unit_code FROM maint_entity m WHERE m.legal_entity_code=tmpTable.legalEntityCode AND (m.unit_name LIKE :search or m.unit_code LIKE :search )) "
						+ " or tmpTable.unitId IN (SELECT m.unit_code FROM maint_entity m, general_parameter g WHERE tmpTable.unitId=m.unit_code and m.legal_entity_code=tmpTable.legalEntityCode 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 tmpTable.unitId IN (SELECT m.unit_code FROM maint_entity m, maint_criticality c WHERE tmpTable.unitId=m.unit_code and m.legal_entity_code=tmpTable.legalEntityCode 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 tmpTable.unitId IN (SELECT m.unit_code FROM maint_entity m, maint_audit_parameter p WHERE tmpTable.unitId=m.unit_code and m.legal_entity_code=tmpTable.legalEntityCode AND m.legal_entity_code =p.legal_entity_code and m.u_criticality=p.u_criticality and tmpTable.auditTypeCode=p.audit_type_code and  p.duration LIKE :search) "
						+ " or tmpTable.auditId IN (SELECT t.audit_id FROM user u, audit_team t WHERE t.audit_id=tmpTable.auditId and t.legal_entity_code =tmpTable.legalEntityCode 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 tmpTable.letterMakerId IN (SELECT u.user_id FROM user u WHERE u.legal_entity_code =tmpTable.legalEntityCode and (u.user_id Like :search or u.first_name LIKE :search or u.last_name Like :search) ) "
						+ " or tmpTable.auditStatus IN (SELECT g.value FROM general_parameter g WHERE tmpTable.legalEntityCode =g.legal_entity_code and g.key_1='STATUS' and g.key_2='AUDIT_STATUS' and g.value=tmpTable.auditStatus and g.description LIKE :search) "
						+ " or tmpTable.reportStatus IN (SELECT g.value FROM general_parameter g WHERE tmpTable.legalEntityCode =g.legal_entity_code and g.key_1='STATUS' and g.key_2='REPORT_STATUS' and g.value=tmpTable.reportStatus and g.description LIKE :search) "
						+ " or tmpTable.auditStatus IN (SELECT g.value FROM general_parameter g WHERE tmpTable.legalEntityCode =g.legal_entity_code and g.key_1='STATUS' and g.key_2='AUDIT_STATUS' and g.value=tmpTable.auditStatus and (g.description LIKE :search or g.value LIKE :search) and   ( tmpTable.observationStatus !='AE'  )   ) "							
						+ " or tmpTable.auditStatus IN (SELECT g.value FROM general_parameter g WHERE tmpTable.legalEntityCode =g.legal_entity_code and g.key_1='STATUS' and g.key_2='AUDIT_STATUS' and g.value=tmpTable.auditStatus  and (g.description LIKE :search or g.value LIKE :search) and   ( tmpTable.observationStatus  ='AE' and tmpTable.auditStatus='AW'  )  )  " );
							
						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 (tmpTable.auditStatus='AI' and tmpTable.observationStatus='AE' ) ");
						}	
						else{ 
							if(search.toUpperCase().startsWith("INI")){
								queryString.append("OR  ( tmpTable.auditStatus='AI' and (tmpTable.observationStatus is null or tmpTable.observationStatus='' ) ) ");
							}
						} 
						queryString.append(" )" );
			}
							
			queryString.append("  ORDER BY FIELD(tmpTable.letterCode,'Dr','FwdL','RN','HoR','RoR','HoLoSC','RoLoSC','IR_GRADING' ) ASC  ");
		
			logger.info("Before the qry...."+queryString);
			
			Query query=session.createNativeQuery(queryString.toString())
					.setResultTransformer(new ResultTransformer() {
						@Override
						public Object transformTuple(Object[] tuple, String[] aliases) { 
							LettersDto ltDto = null;
							if(tuple[0]!=null){
								ltDto = new LettersDto();								 								
								ltDto.setLegalEntityCode(legalEntityCode);
								ltDto.setAuditId(String.valueOf(tuple[2]));								
								ltDto.setPlanId(String.valueOf(tuple[3]));
								ltDto.setFinancialYear(tuple[4]!=null?(Date)tuple[4]:null);
								ltDto.setObservationStatus(String.valueOf(tuple[8]));								
								ltDto.setLetterCode(String.valueOf(tuple[9]));								
								ltDto.setStatus(String.valueOf(tuple[10]));								
								ltDto.setReviewer(String.valueOf(tuple[11]));								
								ltDto.setLetterDesc(String.valueOf(tuple[13]));								
								ltDto.setAuditTypeCode(String.valueOf(tuple[14]));								 
								ltDto.setUnitId(tuple[5].toString()+"-"+tuple[15].toString());
								ltDto.setUnitName(tuple[15].toString());								
								ltDto.setUnitType(String.valueOf(tuple[16]));
								ltDto.setAuditStatus(String.valueOf(tuple[17]));
								ltDto.setMaker(tuple[18]!=null?tuple[18].toString():"");
								ltDto.setReportStatus(String.valueOf(tuple[19])); 
								ltDto.setParentUnitCodeName(String.valueOf(tuple[20]));
								ltDto.setGradingStatus(String.valueOf(tuple[21]));
								ltDto.setGradingStatusRO(tuple[22]!=null?tuple[22].toString():"");
								ltDto.setGradingStatusHO(tuple[23]!=null?tuple[23].toString():"");
								ltDto.setTeam(tuple[24]!=null?tuple[24].toString().split("##"):null);
								ltDto.setGradingComplianceStatus(tuple[25]!=null?tuple[25].toString():null);								
								ltDto.setGradingROComplianceStatus(tuple[26]!=null?tuple[26].toString():null);
								ltDto.setGradingHOComplianceStatus(tuple[27]!=null?tuple[27].toString():null);
							}
							return ltDto;
						}
				@Override
				public List transformList(List collection) {
					return collection;
				}
			})
			.setParameter("legalEntityCode", legalEntityCode)
			//.setParameter("reviewerId", userId)			 
			.setParameter("letterCodeGradingROCompliance", BankAuditConstant.LETTER_CODE_IR_GRADING_RO_COMPLIANCE)
			.setParameter("letterCodeGradingHOCompliance", BankAuditConstant.LETTER_CODE_IR_GRADING_HO_COMPLIANCE);	 
			if(!BankAuditUtil.isEmptyString(search)){
				query.setParameter("search", "%"+search+"%");
			}
			
			if(!BankAuditUtil.isEmptyString(auditTypeCode)) {
				query.setParameter("auditTypeCode", auditTypeCode);
			}			
			
		List<LettersDto> lettersLstCount   = query.list();		  
		Long totalRecords=null;
		try {
			totalRecords = (long)lettersLstCount.size();
		}catch(Exception e) {
			totalRecords = (long)0;
		}
		List<LettersDto> lettersLst=query.setFirstResult((page*size)).setMaxResults(size).list();
		
		
		DataTableResponse dataTableResponse=new DataTableResponse();
		dataTableResponse.setData(lettersLst);
		dataTableResponse.setRecordsTotal(totalRecords);
		dataTableResponse.setRecordsFiltered(totalRecords);
		
		return dataTableResponse; 		
	} 
	
}
