package com.bankaudit.rest.process.dao;

import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

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

import com.bankaudit.constants.BankAuditConstant;
import com.bankaudit.dao.AbstractDao;
import com.bankaudit.dto.process.ComplianceTrackingReportDto;
import com.bankaudit.dto.process.LettersAuditDto;
import com.bankaudit.dto.process.RatingFinancialParameterDto;
import com.bankaudit.dto.process.ReportDto;
import com.bankaudit.dto.process.ReportMISDto;
import com.bankaudit.model.BusinessDate;
import com.bankaudit.process.model.AuditSchedule;
import com.bankaudit.service.BusinessDateService;
import com.bankaudit.util.BankAuditUtil;

@Repository("reportDao")
public class ReportDaoImpl extends AbstractDao implements ReportDao{
	
	static final Logger logger = Logger.getLogger(ReportDaoImpl.class);
	
	/*@Autowired
	private Environment environment;*/
	
	@Autowired
	BusinessDateService businessDateService; 
	
	@SuppressWarnings("unchecked")
	@Override
	public List<ReportDto> getListofRisks(int legalEntityCodeStr, String userId, String role, LocalDate asOfDate, String reportType, List<String> assessmentList){
		
		Session session =getSession();
        session.setDefaultReadOnly(true);
        logger.info("Inside getListofRisks .. .."+ legalEntityCodeStr +" userId.. "+ userId+" role.. "+role +" asOfDate .."+asOfDate +" reportType.. "+reportType);
        logger.info(" assessmentList .. "+assessmentList );
        List<ReportDto> reportsList =null;
        try {
        	if(assessmentList.size() < 1) assessmentList.add(" ");
        	reportsList=  (List<ReportDto>)session.createQuery(" SELECT "
				+ " ao.auditTypeCode ,"		// - 0
        			
				+ "( select concat(f.auditGroupCode , '--' , f.auditGroupName)  "
				+ " from  MaintAuditGroup f where "
				+ "  f.auditGroupCode=apfm.auditGroupCode  "
				+ " and f.auditTypeCode=apfm.auditTypeCode  "
				+ " and f.legalEntityCode=apfm.legalEntityCode ) as auditGroupCode , "  // -1
				
				
				+ "( select  concat(f.auditSubGroupCode, '--' ,  f.auditSubGroupName  )  "
			    + " from  MaintAuditSubgroup f where "
			    + "  f.auditSubGroupCode=apfm.auditSubGroupCode  "
			    + " and f.auditGroupCode=apfm.auditGroupCode  "
			    + " and f.auditTypeCode=apfm.auditTypeCode  "
			    + " and f.legalEntityCode=apfm.legalEntityCode ) as auditSubGroupCode , "  // -2
				
			    
				+ "( select   concat(f.activityId, '--' , f.activityName  )  "
			    + " from  MaintAuditActivity f where "
			    + "  f.activityId=apfm.activityId "
			    + " and f.legalEntityCode=apfm.legalEntityCode ) as activityCode , "  // -3
			    
				+ "( select  concat(f.processId  , '--' , f.processName )  "
				+ " from  MaintAuditProcess f where "
				+ "  f.processId=apfm.processId  "
				+ " and f.legalEntityCode=apfm.legalEntityCode ) as processCode , "  // -4
				
				//+ "( select   concat( f.findingCode , '--' ,   f.findingName , '##' ,f.fCriticality )  "
				+ "( select   concat( f.findingCode , '--' ,   f.findingName , '##' ,"
				+ "		 (select c.criticalityDesc from MaintCriticality c where c.legalEntityCode=f.legalEntityCode "
				+ "			 and c.criticalityCode=f.fCriticality)  ) " 
				+ " from  MaintAuditFinding f where "
				+ "  f.findingId=apfm.findingId  "
				+ " and f.legalEntityCode=apfm.legalEntityCode ) as findingCode , "  // -5
				
				+ " (select concat(gp.value , '--' , gp.description)"
				+ " from GeneralParameter gp where "
				+ " gp.key1='AUDIT_STATUS' and gp.key2='FINDING_STATUS' and gp.value=ao.submissionStatus "
				+ " and gp.legalEntityCode=ao.legalEntityCode ) as findingStatus , "  // -6
				
				+ " (select concat(rr.residualRiskCode , '--' , rr.residualRiskDesc)"
				+ " from MaintResidualRisk rr where "
				+ " rr.residualRiskCode=ao.residulRiskCode  and rr.legalEntityCode=ao.legalEntityCode ) as residualRiskCode , "  // -7

				+ " (select concat(bk.controlClssfcBucketCode , '--' , bk.controlClssfcBucketDesc)"
				+ " from MaintControlClssfcBucket bk where "
				+ " bk.controlClssfcBucketCode=ao.cntrlClsBucketCode  and bk.legalEntityCode=ao.legalEntityCode ) as controlClssfcBucketCode , "  //-8
				
				+ "( select concat(e.unitCode , '--' , e.unitName) from MaintEntity e, AuditSchedule s where e.legalEntityCode=s.legalEntityCode "
				+ "  AND e.legalEntityCode = ao.legalEntityCode AND s.legalEntityCode=ao.legalEntityCode AND e.unitCode = s.unitId  "
				+ "  AND s.auditId = ao.auditId ) as unitCode, "  // -9
				
				+ " ao.legalEntityCode, ao.auditId, ao.observationId "  // observationId -12
				+ " , (select sch.reviewedDate from AuditSchedule as sch WHERE ao.legalEntityCode=sch.legalEntityCode AND ao.auditId=sch.auditId) as reviewedDate"
				+ " from  AuditObservation as ao , ActivityProcessFindingMapping as apfm "
				+ " where  ao.legalEntityCode =:legalEntityCode "
				// + " and ao.auditId =:auditId "
				+ " and ao.legalEntityCode=apfm.legalEntityCode "
				+ " AND ao.mappingId = apfm.mappingId"
				//+ " and ao.auditId in (select sch.auditId from AuditSchedule as sch WHERE ao.legalEntityCode=sch.legalEntityCode "
				//+ "					 AND ao.auditId=sch.auditId AND IFNULL(sch.auditStatus,'NotNull') in ('AW','AC') ) "
				+ " AND ao.auditId in (:assessmentList)"
        		)
		.setParameter("legalEntityCode", legalEntityCodeStr)
		.setParameter("assessmentList", assessmentList)
		//.setParameter("auditId", auditId)
		//.setParameter("observationId", observationId)
		.setResultTransformer(new ResultTransformer() {
			
			@Override
			public Object transformTuple(Object[] tuple, String[] aliases) {
				ReportDto reportDto=null;
				if(tuple[0]!=null){
					logger.info("reportsList .."+ tuple[10].toString() );
					reportDto= new ReportDto();					
					//reportDto.setFinancialYear("2019-20");
					if(tuple[0]!=null) reportDto.setDepartment(tuple[0].toString());
					if(tuple[1]!=null) reportDto.setBusinessLine(tuple[1].toString());
					if(tuple[2]!=null) reportDto.setProduct(tuple[2].toString());
					if(tuple[3]!=null) reportDto.setProcess(tuple[3].toString());
					if(tuple[4]!=null) reportDto.setSubProcess(tuple[4].toString());
					if(tuple[5]!=null) {
						reportDto.setRisk(tuple[5].toString().split("##")[0]);
						reportDto.setLossEventType(tuple[5].toString().split("##")[1]);
					}
					if(tuple[7]!=null) reportDto.setResidualRiskLevel(tuple[7].toString());
					if(tuple[8]!=null) reportDto.setControlRatig(tuple[8].toString());
					if(tuple[9]!=null) reportDto.setRegionalHeadOffice(tuple[9].toString());
					if(tuple[10]!=null) reportDto.setLegalEntityCode(tuple[10].toString());
					if(tuple[11]!=null) reportDto.setAssessmentId(tuple[11].toString());  // AuditId
					if(tuple[12]!=null) reportDto.setRiskId(tuple[12].toString());    // ObservationId
					if(tuple[13]!=null) {
						try {
							String finYear="";
							String year = tuple[13].toString().split("-")[0];
							String month = tuple[13].toString().split("-")[1];
							if(Integer.parseInt(month) > 3) finYear= year + "-"+ String.valueOf((Integer.parseInt(year)+1)).substring(2);
							else finYear = String.valueOf((Integer.parseInt(year)-1)) +"-"+ year.substring(2);
							logger.info("finYear .. "+ finYear);
							reportDto.setFinancialYear(finYear);
						}catch(Exception e) {
							reportDto.setFinancialYear("2019-20");
						}
					} 
				
				
				}
				
				return reportDto;
			}
			
			@Override
			public List transformList(List collection) {
				return collection;
			}
		}).list();
		
        }catch(Exception e) {
        	e.printStackTrace(System.out);
        }
        logger.info("reportsList .."+ reportsList);
        return reportsList;
	}
	
	
	@SuppressWarnings("unchecked")
	@Override
	public List<ReportDto> getHealthIndexforRCSAUnits(int legalEntityCodeStr, String userId, String role, LocalDate asOfDate, String reportType, List<String> assessmentList){
		
		Session session =getSession();
        session.setDefaultReadOnly(true);
        logger.info("Inside getHealthIndexforRCSAUnits .. .."+ legalEntityCodeStr +" userId.. "+ userId+" role.. "+role +" asOfDate .."+asOfDate +" reportType.. "+reportType);
        logger.info(" assessmentList .. "+assessmentList );
        List<ReportDto> healthIdexRCSAUnitsList =null;
        try {
        	if(assessmentList.size() < 1) assessmentList.add(" ");
        	healthIdexRCSAUnitsList=  (List<ReportDto>)session.createQuery(" SELECT "
				+ " sch.auditTypeCode ,"		// - 0
        		+ " sch.healthIndexDsec , sch.healthIndexScore , sch.residulRiskDesc, residulRiskScore , controlProfileDesc, controlProfileScore, "	
				
        		+ " (SELECT CONCAT (sch1.healthIndexDsec, '--' , sch.healthIndexScore, '--' , sch.residulRiskDesc , '--' , residulRiskScore , '--' , "
        		+ "                controlProfileDesc, '--', controlProfileScore) as prevAssmntDtls "
        		+ " FROM AuditSchedule as sch1 WHERE sch.legalEntityCode= sch1.legalEntityCode and sch.prevAuditId =sch1.auditId) as prevAssmntDtls,  "  // -7
        		
        		+ " ( SELECT CONCAT(e.unitCode , '--' , e.unitName) from MaintEntity as e where e.legalEntityCode=sch.legalEntityCode "
				+ "   AND e.unitCode = sch.unitId  ) as unitId, "  // -8
				
				+ " sch.legalEntityCode, sch.auditId, sch.prevAuditId "  // prevAuditId -12
				+ " ,sch.reviewedDate "
				+ " from  AuditSchedule as sch "
				+ " where  sch.legalEntityCode =:legalEntityCode "
				+ " and sch.auditId in (:assessmentList) "
				+ " and IFNULL(sch.auditStatus,'NotNull') in ('AW','AC') "
				)
		.setParameter("legalEntityCode", legalEntityCodeStr)
		.setParameter("assessmentList", assessmentList)   // Need to uncomment
		//.setParameter("auditId", auditId)
		//.setParameter("observationId", observationId)
		.setResultTransformer(new ResultTransformer() {
			
			@Override
			public Object transformTuple(Object[] tuple, String[] aliases) {
				ReportDto reportDto=null;
				if(tuple[0]!=null){
					logger.info("reportsList .."+ tuple[10].toString() );
					reportDto= new ReportDto();					
					if(tuple[0]!=null) reportDto.setDepartment(tuple[0].toString());
					if(tuple[1]!=null) reportDto.setHealthIndexDsec(tuple[1].toString());
					if(tuple[2]!=null) reportDto.setHealthIndexScore(tuple[2].toString());
					if(tuple[3]!=null) reportDto.setResidulRiskDesc(tuple[3].toString());
					if(tuple[4]!=null) reportDto.setResidulRiskScore(tuple[4].toString());
					if(tuple[5]!=null) reportDto.setControlProfileDesc(tuple[5].toString());
					if(tuple[6]!=null) reportDto.setControlProfileScore(tuple[6].toString());
					if(tuple[7]!=null) {
						reportDto.setPrevAssmntHealthIndexDsec(tuple[7].toString().split("--")[0]);
						reportDto.setPrevAssmntHealthIndexScore(tuple[7].toString().split("--")[1]);
						reportDto.setPrevAssmntResidulRiskDesc(tuple[7].toString().split("--")[2]);
						reportDto.setPrevAssmntResidulRiskScore(tuple[7].toString().split("--")[3]);
						reportDto.setPrevAssmntControlProfileDesc(tuple[7].toString().split("--")[4]);
						reportDto.setPrevAssmntControlProfileScore(tuple[7].toString().split("--")[5]);
					}
					if(tuple[8]!=null) reportDto.setRegionalHeadOffice(tuple[8].toString());
					if(tuple[9]!=null) reportDto.setLegalEntityCode(tuple[9].toString());
					if(tuple[10]!=null) reportDto.setAssessmentId(tuple[10].toString());  // AuditId
					if(tuple[11]!=null) reportDto.setPrevAssessmentId(tuple[11].toString()); 
					if(tuple[12]!=null) {
						try {
							String finYear="";
							String year = tuple[12].toString().split("-")[0];
							String month = tuple[12].toString().split("-")[1];
							if(Integer.parseInt(month) > 3) finYear= year + "-"+ String.valueOf((Integer.parseInt(year)+1)).substring(2);
							else finYear = String.valueOf((Integer.parseInt(year)-1)) +"-"+ year.substring(2);
							logger.info("finYear .. "+ finYear);
							reportDto.setFinancialYear(finYear);
						}catch(Exception e) {
							reportDto.setFinancialYear("2019-20");
						}
					} 
					try {
						if(reportDto.getHealthIndexScore() != null && reportDto.getPrevAssmntHealthIndexScore() != null) {
							if(Double.parseDouble(reportDto.getHealthIndexScore()) > Double.parseDouble(reportDto.getPrevAssmntHealthIndexScore()) )
								reportDto.setHealthIndexUP("YES");
							else if(Double.parseDouble(reportDto.getHealthIndexScore()) < Double.parseDouble(reportDto.getPrevAssmntHealthIndexScore()) )
								reportDto.setHealthIndexUP("NO");
							else reportDto.setHealthIndexUP("NA");
						}
					}catch(Exception e) {
						reportDto.setHealthIndexUP("NA");
					}
					try {
						if(reportDto.getResidulRiskScore() != null && reportDto.getPrevAssmntResidulRiskScore() != null) {
							if(Integer.parseInt(reportDto.getResidulRiskScore()) > Integer.parseInt(reportDto.getPrevAssmntResidulRiskScore()) )
								reportDto.setResidulRiskUP("YES");
							else if(Integer.parseInt(reportDto.getResidulRiskScore()) < Integer.parseInt(reportDto.getPrevAssmntResidulRiskScore()) )
								reportDto.setResidulRiskUP("NO");
							else reportDto.setResidulRiskUP("NA");
						}
					}catch(Exception e) {
						reportDto.setResidulRiskUP("NA");
					}
					try {
						if(reportDto.getControlProfileScore() != null && reportDto.getPrevAssmntHealthIndexScore() != null) {
							if(Integer.parseInt(reportDto.getControlProfileScore()) > Integer.parseInt(reportDto.getPrevAssmntControlProfileScore()) )
								reportDto.setControlProfileUP("YES");
							else if(Integer.parseInt(reportDto.getControlProfileScore()) < Integer.parseInt(reportDto.getPrevAssmntControlProfileScore()) )
								reportDto.setControlProfileUP("NO");
							else reportDto.setControlProfileUP("NA");
						}
					}catch(Exception e) {
						reportDto.setControlProfileUP("NA");
					}
				}
					
				
				return reportDto;
			}
			
			@Override
			public List transformList(List collection) {
				return collection;
			}
		}).list();
		
        }catch(Exception e) {
        	e.printStackTrace(System.out);
        }
        logger.info("healthIdexRCSAUnitsList .."+ healthIdexRCSAUnitsList);
        return healthIdexRCSAUnitsList;
	}
	
	
	@SuppressWarnings("unchecked")
	@Override
	public List<ReportDto> getRCSAProgressStatus(int legalEntityCodeStr, String userId, String role, LocalDate asOfDate, String reportType
			, List<String> assessmentList , LocalDate finYrStartDate, LocalDate finYrEndDate){
		
		Session session =getSession();
        session.setDefaultReadOnly(true);
        logger.info("Inside getRCSAProgressStatus .. .."+ legalEntityCodeStr +" userId.. "+ userId+" role.. "+role +" asOfDate .."+asOfDate +" reportType.. "+reportType + " finYrStartDt.. "+finYrStartDate +" finYrEndDt.. "+ finYrEndDate );
        logger.info(" assessmentList .. "+assessmentList );
        List<ReportDto> rCSAprocessStatusList =null;
        try {
        	if(assessmentList.size() < 1) assessmentList.add(" ");
        	rCSAprocessStatusList=  (List<ReportDto>)session.createNativeQuery("  "
        			+ "  SELECT sch.audit_id as ASSESSMENT_ID,sch.unit_id as UNIT_ID,audit_type_code as DEPT_CODE,sch.audit_status, " // 3-audit_status
        			+ "   (select group_concat(concat(u.user_id,'-',first_name,' ',last_name))"
        			+ "		 from maint_entity_audit_subgroup_mapping sm, user_role_mapping rm,user u  "
        			+ "  	 where sm.id=rm.user_id and rm.user_role_id='G8' and sm.audit_type_code=sch.audit_type_code "
        			+ "			and u.legal_entity_code=sm.legal_entity_code and sm.legal_entity_code= sch.legal_entity_code "
        			+ "      	and rm.legal_entity_code=u.legal_entity_code and u.unit_code=sch.unit_id and u.user_id=rm.user_id) as ASSESSOR , " // 4- Assessor
        			+ "   (select concat(t.user_id,'-',u.first_name,' ',u.last_name) from audit_team t, user u where t.audit_id=sch.audit_id and t.legal_entity_code=sch.legal_entity_code and t.user_type='L' and t.legal_entity_code=u.legal_entity_code and t.user_id=u.user_id ) as LEAD, "
        			+ "   (select group_concat(concat(u.user_id,'-',first_name,' ',last_name)) from audit_team t, user u "
        			+ "		 where t.audit_id=sch.audit_id and t.legal_entity_code=sch.legal_entity_code and t.user_type='M' and t.user_id <> 'null' "
        			+ "			and t.legal_entity_code=u.legal_entity_code and t.user_id=u.user_id ) as MEMBER, " // 6- Member
        			+ "   (select count(*) from audit_observation o where o.legal_entity_code=sch.legal_entity_code and o.audit_id=sch.audit_id ) as TOTAL_RISK, " // 7
        			+ "   (select count(*) from audit_observation o where o.legal_entity_code=sch.legal_entity_code and o.audit_id=sch.audit_id and o.submission_status='U' ) as ASSESSMENT_UNAUTH, "  // 8
        			+ "   (select count(*) from audit_observation o where o.legal_entity_code=sch.legal_entity_code and o.audit_id=sch.audit_id and o.submission_status='PA' ) as ASSESSMENT_PENDING_AUTH, " // 9
        			+ "   (select count(*) from audit_observation o where o.legal_entity_code=sch.legal_entity_code and o.audit_id=sch.audit_id and o.submission_status='R' ) as ASSESSMENT_REJECT, " //10 
        			+ "   (select count(*) from audit_observation o where o.legal_entity_code=sch.legal_entity_code and o.audit_id=sch.audit_id and o.submission_status='A' AND ifnull(o.response_status,'O') not in ('A') AND ifnull(o.review_status,'O') not in ('PA','A','R') ) as ASSESSMENT_AUTH, " // 11 -ASSESSMENT_AUTH
        			+ "   (select count(*) from audit_observation o where o.legal_entity_code=sch.legal_entity_code and o.audit_id=sch.audit_id and o.response_status='A' AND ifnull(o.review_status,'O') not in ('PA','A','R')) as AUDIT_AUTH, "  // 12
        			+ "   (select count(*) from audit_observation o where o.legal_entity_code=sch.legal_entity_code and o.audit_id=sch.audit_id and o.review_status='PA' ) as ACTION_PLAN_UNAUTH, "
        			+ "   (select count(*) from audit_observation o where o.legal_entity_code=sch.legal_entity_code and o.audit_id=sch.audit_id and o.review_status='R' ) as ACTION_PLAN_REJECT, "
        			+ "   (select count(*) from audit_observation o where o.legal_entity_code=sch.legal_entity_code and o.audit_id=sch.audit_id and o.review_status='A' ) as ACTION_PLAN_AUTH " // 15 -Action Plan Auth
        			+ "	  , sch.plan_id "
        			+ "   from audit_schedule sch  "
        			+ "   where legal_entity_code=:legalEntityCode and audit_id in (:assessmentList) "
        			+ " 	AND schedule_start_date between :finYrStartDate AND :finYrEndDate "
				)
		.setParameter("legalEntityCode", legalEntityCodeStr)
		.setParameter("assessmentList", assessmentList)   
		.setParameter("finYrStartDate", finYrStartDate)
		.setParameter("finYrEndDate", finYrEndDate)
		.setResultTransformer(new ResultTransformer() {
			int i=0;
			@Override
			public Object transformTuple(Object[] tuple, String[] aliases) {
				ReportDto reportDto=null;
				if(tuple[0]!=null){
					logger.info("reportsList .."+ tuple[10].toString() );
					reportDto= new ReportDto();	i++;				
					reportDto.setSerialNumber(i+"");
					if(tuple[0]!=null) reportDto.setAssessmentId(tuple[0].toString());
					if(tuple[1]!=null) reportDto.setUnitCode(tuple[1].toString());
					if(tuple[2]!=null) reportDto.setDepartment(tuple[2].toString());
					//E2="AP","Planned",IF(E2="AI","Assessment Initiated",IF(E2="AC","Completed",IF(E2="AS","Assessment Submitted",IF(E2="AR","Audit Submitted","No"))))
					if(tuple[3]!=null) reportDto.setLatestStatus( (tuple[3].toString().equals("AP")?"Planned":
						tuple[3].toString().equals("AI")?"Assessment Initiated": tuple[3].toString().equals("AS")?"Assessment Submitted ":
						tuple[3].toString().equals("AR")?"Audit Submitted":tuple[3].toString().equals("AC")?"Completed": ""));
					if(tuple[4]!=null) reportDto.setAssessorsId(tuple[4].toString());
					if(tuple[5]!=null) reportDto.setLeadId(tuple[5].toString());
					if(tuple[6]!=null) reportDto.setMembersId(tuple[6].toString());
					
					if(tuple[7]!=null) reportDto.setTotalRiskRecords(tuple[7].toString());
					if(tuple[8]!=null) reportDto.setAssessmentOpen(tuple[8].toString());
					if(tuple[9]!=null) reportDto.setAssessmentMaker(tuple[9].toString());
					if(tuple[10]!=null) reportDto.setAssessmentRejected(tuple[10].toString());  
					if(tuple[11]!=null) reportDto.setAssessmentChecked(tuple[11].toString()); 
					if(tuple[12]!=null) reportDto.setAuditChecked(tuple[12].toString()); 
					if(tuple[13]!=null) reportDto.setActionPlanMaker(tuple[13].toString());
					if(tuple[14]!=null) reportDto.setActionPlanRejected(tuple[14].toString());
					if(tuple[15]!=null) reportDto.setActionPlanChecked(tuple[15].toString());
					if(tuple[16]!=null) reportDto.setPlanId(tuple[16].toString());
					reportDto.setActionPlanOpen("0");
				}
				return reportDto;
			}
			
			@Override
			public List transformList(List collection) {
				return collection;
			}
		}).list();
		
        }catch(Exception e) {
        	e.printStackTrace(System.out);
        }
        logger.info("rCSAprocessStatusList .."+ rCSAprocessStatusList);
        return rCSAprocessStatusList;
	}
	
	
	
	
	@SuppressWarnings("unchecked")
	@Override
	public boolean isDeptAsRMD(Integer legalEntityCode ,String role,String userId) {
		Session session=getSession();
		boolean isDeptAsRMD = false;
		Integer isDeptAsRMDCnt=0;
		try {
			isDeptAsRMDCnt = Integer.parseInt((session.createQuery("SELECT COUNT(*)  FROM MaintEntityAuditSubgroupMapping WHERE "
					+ " legalEntityCode =:legalEntityCode "
					+ " AND mappingType ='U' AND id =:userId "
					+ " AND status = 'A' AND auditTypeCode='RMD' ")
					.setParameter("legalEntityCode", legalEntityCode)
					.setParameter("userId", userId).uniqueResult()).toString());
			logger.info(" isDeptAsRMD cnt .. "+ isDeptAsRMDCnt);
			if(isDeptAsRMDCnt > 0) isDeptAsRMD = true;
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		return isDeptAsRMD;
						
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<String> getDeptList(Integer legalEntityCode ,String role,String userIdOrUnitId ) {
		Session session=getSession();
		logger.info("getDeptList .." +legalEntityCode + " role .. "+ role +" userIdOrUnitId .. "+userIdOrUnitId);
		
		return  session.createQuery("SELECT distinct auditTypeCode FROM MaintEntityAuditSubgroupMapping  "
				+ " WHERE legalEntityCode =:legalEntityCode "
				+ " AND mappingType ='U' AND id =:userId "
				+ " AND status = 'A' ")
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("userId", userIdOrUnitId)
				.list();
		
		/*return  session.createNativeQuery("SELECT ditsinct audit_type_code FROM maint_entity_audit_subgroup_mapping WHERE "
				+ " legal_entity_code =:legalEntityCode "
				+ " AND mapping_type ='U' AND id =:userId "
				+ " AND status = 'A' ")
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("userId", userIdOrUnitId)
				.list();*/
		
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<String> getAssessmentList(Integer legalEntityCode ,String role,String userId, LocalDate asOfDate,boolean isDeptAsRMD, List<String> deptList, List<String> unitList ){
		
		Session session =getSession();
        session.setDefaultReadOnly(true);
        logger.info("Inside getListofRisks .. .."+ legalEntityCode +" userId.. "+ userId+" role.. "+role +" asOfDate .."+asOfDate +" isDeptAsRMD .. "+isDeptAsRMD);
        logger.info("deptList .. .."+ deptList);
        logger.info("unitList .. .."+ unitList);
        List<String> assessmentList =null;
        try {
        	StringBuilder query= new StringBuilder(	" SELECT "
        			+ "     sch.audit_id "
        			+ " FROM "
        			+ "     audit_schedule AS sch, "
        			+ "     (SELECT  "
        			+ "         unit_id, audit_type_code, MAX(schedule_start_date) AS max_schedule_start_date, legal_entity_code "
        			+ "     FROM "
        			+ "         audit_schedule "
        			+ "     WHERE "
        			+ "         legal_entity_code =:legalEntityCode " 
        			+ "         AND unit_id IN (:unitList) "
        			+ "			AND audit_status = 'AC' ") ;
        	 
        	if(!isDeptAsRMD) query.append(" AND audit_type_code IN (:deptList)  ");
        	if(asOfDate != null && !"".equalsIgnoreCase(asOfDate.toString())) query.append(" AND DATE(reviewed_date) <=:asOfDate  ");
        	 
        	 query.append(" GROUP BY unit_id , audit_type_code) AS sch1 "
        			+ " WHERE "
        			+ "     sch1.audit_type_code = sch.audit_type_code "
        			+ "         AND sch.schedule_start_date = sch1.max_schedule_start_date "
        			+ "         AND sch.unit_id = sch1.unit_id "
        			+ "         AND sch.legal_entity_code = sch1.legal_entity_code ");
        	
        	Query hqlQuery= session.createNativeQuery(query.toString())
        			.setParameter("legalEntityCode", legalEntityCode)
        			.setParameter("unitList", unitList);
        	
        	if(!isDeptAsRMD) {
        		hqlQuery.setParameter("deptList", deptList);
        	}
        	if(asOfDate != null && !"".equalsIgnoreCase(asOfDate.toString())) {
        		hqlQuery.setParameter("asOfDate", asOfDate);
        	}
        		
        	assessmentList=  (List<String>)hqlQuery.list();
			
        }catch(Exception e) {
        	e.printStackTrace(System.out);
        }
        logger.info("assessmentList .."+ assessmentList);
        return assessmentList;
	}
	
	
	
	
	@SuppressWarnings("unchecked")
	@Override
	public List<ReportDto> getUnitNameAddressUserName (int legalEntityCode, String userId, String unitId){
		
		Session session =getSession();
        session.setDefaultReadOnly(true);
        logger.info("Inside getListofRisks .. .."+ legalEntityCode +" userId.. "+ userId +" unitId .."+unitId);
        List<ReportDto> reportDtoLst =null;
        ReportDto reportdto = null;
        try {
        	StringBuilder query= new StringBuilder(	" SELECT "
        			+ "     e.unit_code, e.unit_name, e.address1, e.address2, e.address3, e.city, e.pincode, u.first_name, u.last_name "
        			+ " FROM     maint_entity e, user u "
        			+ " WHERE e.legal_entity_code = u.legal_entity_code AND e.unit_code = u.unit_code "
        			+ "         AND u.user_id =:userId AND e.legal_entity_code =:legalEntityCode " ) ;
        	 
        		Query hqlQuery= session.createNativeQuery(query.toString())
        			.setParameter("legalEntityCode", legalEntityCode)
        			.setParameter("userId", userId);
        	
        		Object[] obj =  (Object[])hqlQuery.uniqueResult();
        		reportDtoLst = new ArrayList<ReportDto>();
        		if(obj != null) {
        			reportdto = new ReportDto();
        			reportdto.setUnitCode(obj[0].toString());
        			reportdto.setUnitName(obj[1].toString());
        			reportdto.setAddress1(obj[2].toString());
        			reportdto.setAddress2(obj[3].toString());
        			reportdto.setAddress3(obj[4].toString());
        			reportdto.setCity(obj[5].toString());
        			reportdto.setPincode(obj[6].toString());
        			reportdto.setFirstName(obj[7].toString());
        			reportdto.setLastName(obj[8].toString());
        			
        		}reportDtoLst.add(reportdto);
        		logger.info("reportDtoLst .. "+reportDtoLst);
        }catch(Exception e) {
        	e.printStackTrace(System.out);
        }
        logger.info("reportDtoLst .."+ reportDtoLst);
        return reportDtoLst;
	}
	
	@Override
	public String updateDynamicStatements(int legalEntityCode, String auditId, String auditTypeCode, String statementId) {
		
		Session session=getSession();
		
		StringBuilder queryString2=new StringBuilder( " select audit_type_code, statement_id, value_a, value_b, value_c,value_d, value_e, derived_stmt " 
				+ " from maint_report_derived_stmt where "
				+ "	statement_id IS NOT NULL and legal_entity_code =:legalEntityCode and audit_type_code =:auditTypeCode " ); 
		Query query2lst=session.createSQLQuery(queryString2.toString())
				.setParameter("legalEntityCode", legalEntityCode).setParameter("auditTypeCode", auditTypeCode);
		
		List ls = query2lst.list();
		
		StringBuilder queryUpdate=new StringBuilder( " update audit_report_stmt set derived_statement=:derivedStatement " 
				+ " where legal_entity_code =:legalEntityCode AND statement_id=:statementId AND "
				+ "	 audit_type_code =:auditTypeCode " ); 
		String dStmnt="";
		String comp="";
		String stmntId=null;
		
		try{
			for(Object obj: ls) {
	 			Object[] obji=(Object[])obj;
	 			logger.info("Obj 0 :: "+ obji[0] +" :: "+ obji[1]);
	 			if(obji[1]!=null && "1".equalsIgnoreCase(obji[1].toString())) {
	 				stmntId=obji[1].toString();
	 				if(obji[2]!=null && obji[3]!=null) {
	 					double a= Double.parseDouble(obji[2].toString());
	 					double b= Double.parseDouble(obji[3].toString());
	 					dStmnt = obji[7].toString();
	 					logger.info("val a:: "+ a + " f:: "+ b);
	 					comp=(a > b ?"decreased":"increased");
	 					dStmnt= dStmnt.replaceAll("##VALUE_COMP##", comp).replaceAll("##VALUE_A##", a+"").replaceAll("##VALUE_B##", b+"");
	 				}
	 			}
	 			
	 			if(obji[1]!=null && "3".equalsIgnoreCase(obji[1].toString())) {
	 				stmntId=obji[1].toString();
	 				if(obji[2]!=null && obji[3]!=null) {
	 					double a= Double.parseDouble(obji[2].toString());
	 					double b= Double.parseDouble(obji[3].toString());
	 					double c= Double.parseDouble(obji[4].toString());
	 					double d= Double.parseDouble(obji[5].toString());
	 					double e= Math.round((a/c)*10000)/100D;
	 					double f= Math.round((b/d)*10000)/100D;
	 					logger.info("val e:: "+ e + " f:: "+ f);
	 					dStmnt = obji[7].toString();
	 					comp=(e > f ?"decreased":"increased");
	 					dStmnt= dStmnt.replaceAll("##VALUE_COMP##", comp).replaceAll("##VALUE_CALC1##", e+"").replaceAll("##VALUE_CALC2##", f+"");
	 				}
	 			}
	 			
	 			logger.info("Comp:: "+ comp);
	 			logger.info("dStmnt:: "+ dStmnt);
	 			
 				session.createSQLQuery(queryUpdate.toString())
 				.setParameter("derivedStatement", dStmnt)
 				.setParameter("legalEntityCode", legalEntityCode)
 				.setParameter("statementId", stmntId)
 				.setParameter("auditTypeCode", auditTypeCode).executeUpdate();
 				
 				session.flush();
				session.clear();
	 			stmntId=""; dStmnt ="";
	 			
			}
		}catch(Exception e) {
			System.out.println("Exception occured ....");
		}
		return "";
		
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<String> getAuditScheduleUnitIdsList(Integer legalEntityCode , List<String> unitList, LocalDate financialYear, String auditTypeCode, List<LocalDate>  financialYearList){
        List<String> assessmentList =null;
        logger.info("Inside getAuditScheduleUnitIdsList .. "+ financialYear+" :: "+auditTypeCode+" :: "+ unitList);
        try {
        	LocalDate financialYearStartDt = financialYearList.get(0);
			LocalDate financialYearEndDt = financialYearList.get(1);
			StringBuilder query= new StringBuilder("SELECT audit_id  FROM audit_schedule WHERE  unit_id IN (:unitList)"  
        			+ " AND legal_entity_code=:legalEntityCode AND audit_status in ('AS', 'AR', 'AW', 'AC')") ;
			query.append(" AND (actual_start_date between '"+financialYearStartDt+"' and '"+financialYearEndDt+"')   ");
			
			if(!BankAuditUtil.isEmptyString(auditTypeCode)) {
				query.append("AND audit_type_code ='"+ auditTypeCode+"'");
			}
        	Query hqlQuery= getSession().createNativeQuery(query.toString())
        			.setParameter("legalEntityCode", legalEntityCode)
        			.setParameter("unitList", unitList);
        			//.setParameter("financialYear", (financialYear.minusYears(1)));
        	assessmentList=  (List<String>)hqlQuery.list();
        }catch(Exception e) {
        	e.printStackTrace(System.out);
        }
        logger.info("assessmentList .."+ assessmentList);
        return assessmentList;
	}


	@SuppressWarnings("deprecation")
	@Override
	public List<ComplianceTrackingReportDto> getAuditScheduleReportList(Integer legalEntityCode, List<String> auditList,
			LocalDate financialYear, String auditTypeCode, List<LocalDate> financialYearList) {
		//String criticality = environment.getRequiredProperty("complianceTrackingCriticality");
		logger.info("Inside getAuditScheduleReportList .. "+ legalEntityCode+ " :: "+  financialYear+" :: "+auditTypeCode+" :: "+auditList);
		 
		List<String> rolesListCompliance = Arrays.asList(("G3,G31").split(","));
		StringBuilder query = new StringBuilder("SELECT ao.legal_entity_code,"
												+ "       ao.audit_type_code,"
												+ "       (SELECT Concat(e.unit_code, '-', e.unit_name)"
												+ "        FROM   maint_entity e,"
												+ "               audit_schedule s"
												+ "        WHERE  e.legal_entity_code = s.legal_entity_code"
												+ "               AND e.legal_entity_code = ao.legal_entity_code"
												+ "               AND s.legal_entity_code = ao.legal_entity_code"
												+ "               AND e.unit_code = s.unit_id"
												+ "               AND s.audit_id = ao.audit_id)                     AS unit_code,"
												+ "       (SELECT Concat(f.audit_group_code, '--', f.audit_group_name)"
												+ "        FROM   maint_audit_group f"
												+ "        WHERE  f.audit_group_code = apfm.audit_group_code"
												+ "               AND f.audit_type_code = apfm.audit_type_code"
												+ "               AND f.legal_entity_code = apfm.legal_entity_code) AS"
												+ "       audit_group_code,"
												+ "       (SELECT Concat(f.audit_sub_group_code, '--', f.audit_sub_group_name)"
												+ "        FROM   maint_audit_subgroup f"
												+ "        WHERE  f.audit_sub_group_code = apfm.audit_sub_group_code"
												+ "               AND f.audit_group_code = apfm.audit_group_code"
												+ "               AND f.audit_type_code = apfm.audit_type_code"
												+ "               AND f.legal_entity_code = apfm.legal_entity_code) AS"
												+ "       audit_sub_group_code,"
												+ "       (SELECT Concat(f.activity_id, '--', f.activity_name)"
												+ "        FROM   maint_audit_activity f"
												+ "        WHERE  f.activity_id = apfm.activity_id"
												+ "               AND f.legal_entity_code = apfm.legal_entity_code) AS activitycode,"
												+ "       (SELECT Concat(f.process_id, '--', f.process_name)"
												+ "        FROM   maint_audit_process f"
												+ "        WHERE  f.process_id = apfm.process_id"
												+ "               AND f.legal_entity_code = apfm.legal_entity_code) AS processcode,"
												+ "       (SELECT Concat(f.finding_code, '--', f.finding_name)"
												+ "        FROM   maint_audit_finding f"
												+ "        WHERE  f.finding_id = apfm.finding_id"
												+ "               AND f.legal_entity_code = apfm.legal_entity_code) AS finding_code,"												
												+ "       (SELECT Concat(g.value, '--', g.description)"
												+ "        FROM   general_parameter g  WHERE key_2='COMPLIANCE_CATEGORY'  and key_1='OBSERVATION_CAPTURE' "
												+ "        and  g.value = ao.compliance_category and g.legal_entity_code =  apfm.legal_entity_code )  as complianceCategory, "												 
												//+ "       ao.compliance_category,"											
												+ "       ao.audit_id, apfm.mapping_id, "												 
														 
												+ "       (SELECT Concat(e1.unit_code, '--', e1.unit_name)"
												+ "        FROM   maint_entity e, "
												+ "              maint_entity e1, "
												+ "               audit_schedule s"
												+ "        WHERE  e.legal_entity_code = s.legal_entity_code"
												+ "               AND e.legal_entity_code = ao.legal_entity_code" 
												+ "               AND e.unit_code = s.unit_id"
												+ "               AND s.audit_id = ao.audit_id"
												+ "				  AND e1.unit_code = e.parent_unit_code )  AS parentUnitCode,"
												+ "		 ao.finding_remarks, 	"
												+ "		 (Select (CASE  WHEN (ao.compliance_category =  '"+BankAuditConstant.COMPLIANCE_YES+"' and ifnull(stmt.standard_stmnt_compliance_print, '') <> '"+BankAuditConstant.NO+"') "
												+ "  						THEN  stmt.standard_stmnt_compliance    "
												+ "		  		 	  	WHEN (ao.compliance_category = '"+BankAuditConstant.COMPLIANCE_NO+"' and ifnull(stmt.standard_stmnt_non_compliance_print, '') <>  '"+BankAuditConstant.NO+"')"
												+ "							THEN  stmt.standard_stmnt_non_compliance "
												+ "						END ) "
												+ "			 	from maint_report_standard_stmt stmt where  stmt.mapping_id=ao.mapping_id and stmt.legal_entity_code = ao.legal_entity_code	"
												+ "				and stmt.audit_type_code=ao.audit_type_code  "	
												+ "				and stmt.status='"+BankAuditConstant.STATUS_ACTIVE+"' and  stmt.entity_status='"+BankAuditConstant.STATUS_AUTH+"' ) as standardStmt, "
												/*+ "		  (Select comStmt.comment   "
												+ "			 	from audit_observation_respond_review_capture comStmt where  comStmt.observation_id=ao.observation_id and comStmt.legal_entity_code = ao.legal_entity_code	"
												+ "				and comStmt.audit_type_code=ao.audit_type_code  and comStmt.status='"+BankAuditConstant.STATUS_ACTIVE+"' and comStmt.capture_type IN (:rolesList)   "
												+ "				order by comStmt.id DESC limit 1	) as complianceRemark " */
												+ "				rrc.comment "
												+ " FROM  activity_process_finding_mapping AS apfm, maint_audit_finding AS mc, maint_criticality as c, "
												+ "       audit_observation AS ao "
												+ "       LEFT JOIN ( "
												+ " 		SELECT c.legal_entity_code, c.audit_id, c.audit_type_code, c.observation_id, c.comment FROM "
												+ "  			audit_observation_respond_review_capture c, "
												+ "  			(SELECT legal_entity_code, audit_id, audit_type_code, observation_id, MAX(id) AS id FROM audit_observation_respond_review_capture comStmt "
												+ "					WHERE capture_type IN (:rolesList) AND audit_id IN (:auditList) AND status='"+BankAuditConstant.STATUS_ACTIVE+"' "
												+ "					GROUP BY legal_entity_code, audit_id, audit_type_code, observation_id) AS rc "
												+ " 			WHERE c.legal_entity_code=rc.legal_entity_code AND c.audit_id = rc.audit_id AND c.observation_id = rc.observation_id AND c.id = rc.id "
												+ "  	   ) AS rrc "
												+ "       ON ao.legal_entity_code=rrc.legal_entity_code AND ao.audit_id=rrc.audit_id AND ao.observation_id=rrc.observation_id "
												+ " WHERE  ao.legal_entity_code = :legalEntityCode"
												+ "       AND ao.legal_entity_code = apfm.legal_entity_code"
												+ "       AND ao.mapping_id = apfm.mapping_id"
												+ " 	  AND mc.finding_id = apfm.finding_id"
												+ "       AND ao.audit_id IN (:auditList) "
												+ "		  AND c.legal_entity_code=mc.legal_entity_code AND c.criticality_of_type='Finding' AND c.criticality_code=mc.f_criticality "
												//+ "		  AND c.criticality_code=ifnull(mc.f_criticality,'Low')	"  // If no criticality maintained for the finding/checklist default to Low
												+ "		  AND c.score >= (SELECT description*1 FROM general_parameter g WHERE g.legal_entity_code=c.legal_entity_code AND key_1='CRITICALITY_CUTOFF' AND key_2='FINDING') ");
		
		
												/*+ " FROM   audit_observation AS ao,"
												+ "       activity_process_finding_mapping AS apfm, maint_audit_finding AS mc "
												+ " WHERE  ao.legal_entity_code = :legalEntityCode"
												+ "       AND ao.legal_entity_code = apfm.legal_entity_code"
												+ "       AND ao.mapping_id = apfm.mapping_id"
												+ " AND mc.finding_id = apfm.finding_id"
												+ "       AND mc.f_criticality in("+criticality+")"
												+ "       AND ao.audit_id IN (:auditList) ");*/
		Query hqlQuery = getSession().createNativeQuery(query.toString()).setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditList", auditList)
				.setParameterList("rolesList", rolesListCompliance)
				.setResultTransformer(new ResultTransformer() {

					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						ComplianceTrackingReportDto complianceReport = new ComplianceTrackingReportDto();
						if (tuple[0] != null) {
							if (tuple[0] != null)
								complianceReport.setLegalEntityCode(tuple[0].toString());
							if (tuple[1] != null)
								complianceReport.setAuditTypeCode(tuple[1].toString());
							if (tuple[2] != null)
								complianceReport.setUnitName(tuple[2].toString());
							if (tuple[3] != null)
								complianceReport.setChapter(tuple[3].toString());
							if (tuple[4] != null)
								complianceReport.setSection(tuple[4].toString());
							if (tuple[5] != null)
								complianceReport.setProduct(tuple[5].toString());
							if (tuple[6] != null)
								complianceReport.setProcess(tuple[6].toString());
							if (tuple[7] != null)
								complianceReport.setCheckList(tuple[7].toString());
							if (tuple[8] != null)
								complianceReport.setComplianceStatus(tuple[8].toString());
							if (tuple[10] != null)
								complianceReport.setMappingId(tuple[10].toString());
							if (tuple[11] != null)
								complianceReport.setParentUnitName(tuple[11].toString());							
							if (tuple[12] != null )	 
								complianceReport.setObservationRemarks(tuple[12].toString().replaceAll("\\<.*?\\>", ""));							 
							if (tuple[13] != null )									
								complianceReport.setStandardStatement(tuple[13].toString()); 
							if (tuple[14] != null)
								complianceReport.setComplianceRemarks(tuple[14].toString().replaceAll("\\<.*?\\>", ""));
							
							complianceReport.setFinancialYear(financialYear.format(DateTimeFormatter.ISO_DATE));
						}
						return complianceReport;
					}

					@Override
					public List transformList(List collection) {
						return collection;
					}
				});
		return hqlQuery.list();
	}
	
	
	@SuppressWarnings({"rawtypes", "unchecked" })
	@Override
	public List<Date> getFinancialYearEndDate(Integer legalEntityCode) {
		Session session=getSession();
	    session.setDefaultReadOnly(true);
		try {
			logger.info("getFinancialYearStartDate .. legalEntityCode:: "+ legalEntityCode);
			StringBuilder str = new StringBuilder("SELECT DISTINCT financial_year FROM audit_schedule "
					+ "WHERE legal_entity_code ="+legalEntityCode+" AND financial_year IS NOT NULL ORDER BY financial_year DESC ");
					
			Query hqlQuery= getSession().createNativeQuery(str.toString());
			
			List<Date> financialYearList =  hqlQuery.list();
			
			BusinessDate businessDate = businessDateService.getBusinessDateByLegalEntityCode(legalEntityCode);				
			Calendar cal = new GregorianCalendar();
			cal.setTime(businessDate.getBusinessDate());
			
			String financiyalYearEnd ="";
			String financiyalYearEndPrevious ="";
			if(cal.get(Calendar.MONTH)>2){ 
			    financiyalYearEnd=(cal.get(Calendar.YEAR)+1)+"-03-31";
			    financiyalYearEndPrevious=(cal.get(Calendar.YEAR))+"-03-31";
			}else{
				financiyalYearEnd=(cal.get(Calendar.YEAR))+"-03-31";
				financiyalYearEndPrevious=(cal.get(Calendar.YEAR)-1)+"-03-31";
			}
			
			Date financialYearDate = new SimpleDateFormat("yyyy-MM-dd").parse(financiyalYearEnd);
			Date financialYearDatePre = new SimpleDateFormat("yyyy-MM-dd").parse(financiyalYearEndPrevious);
			/*if(!financialYearList.contains(financialYearDate)){
				financialYearList.add(financialYearDate);
			}
			if(!financialYearList.contains(financialYearDatePre)){
				financialYearList.add(financialYearDatePre);
			}*/
			
			List<Date> financialYearListSorted =new ArrayList<Date>();
			
			if(!financialYearList.contains(financialYearDatePre)){
				financialYearListSorted.add(financialYearDatePre);
			}
			if(!financialYearList.contains(financialYearDate)){
				financialYearListSorted.add(financialYearDate);
			}
			financialYearListSorted.addAll(financialYearList);
						
			return financialYearList;				
			
		}catch(Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	@SuppressWarnings("deprecation")
	@Override
	public List<RatingFinancialParameterDto> getAuditRatingList(Integer legalEntityCode, List<String> unitList,
			LocalDate financialYear, String auditTypeCode, List<LocalDate>  financialYearList){
		 
		Session session=getSession();
	    session.setDefaultReadOnly(true);	
	    LocalDate financialYearStartDt = financialYearList.get(0);
		LocalDate financialYearEndDt = financialYearList.get(1);
		StringBuilder query = new StringBuilder("Select   "
				+ " (SELECT Concat(e.unitCode, '-', e.unitName)"
				+ " 	FROM   MaintEntity e  " 
				+ " 	WHERE  e.legalEntityCode = sch.legalEntityCode" 
				+ "     AND e.unitCode = sch.unitId )  AS unit_code, " 	 
						 
				+ " (SELECT Concat(e1.unitCode, '--', e1.unitName) "
				+ "     FROM  MaintEntity e INNER JOIN   MaintEntity e1 "
				+ "        on  e.legalEntityCode = sch.legalEntityCode " 
				+ "               AND e.unitCode = sch.unitId " 
				+ "				  AND e1.unitCode = e.parentUnitCode )  AS parentUnitCode, " 
				+ " sch as auditSchedule   "
				+ " FROM  AuditSchedule sch "
				+ " where sch.legalEntityCode = :legalEntityCode "
				+ " and sch.unitId in (:unitList)");
			//	+ " and financialYear='"+(financialYear.getYear()-1)+"-03-31"+"' ");
		query.append(" And (sch.actualStartDate between '"+financialYearStartDt+"' and '"+financialYearEndDt+"' )");
				if(!BankAuditUtil.isEmptyString(auditTypeCode)){
					query.append(" and sch.auditTypeCode = :auditTypeCode ");
				}		
		
			Query hqlQuery = getSession().createQuery(query.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameterList("unitList", unitList);
				//.setParameter("financialYear", financialYear);
				if(!BankAuditUtil.isEmptyString(auditTypeCode)){
					hqlQuery.setParameter("auditTypeCode", auditTypeCode);
				}
				
				hqlQuery.setResultTransformer(new ResultTransformer() {

					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						RatingFinancialParameterDto ratingFinancialParameterDto = new RatingFinancialParameterDto();
						AuditSchedule auditSchedule = new AuditSchedule();
						if (tuple[2] != null) { 
							
							auditSchedule = (AuditSchedule) tuple[2];							
							if (tuple[0] != null)
								ratingFinancialParameterDto.setUnitName(tuple[0].toString());
							if (tuple[1] != null)
								ratingFinancialParameterDto.setParentUnitName(tuple[1].toString());
							
							ratingFinancialParameterDto.setAuditId(auditSchedule.getAuditId());
							ratingFinancialParameterDto.setAuditTypeCode(auditSchedule.getAuditTypeCode());
							ratingFinancialParameterDto.setFinancialYear(financialYear.format(DateTimeFormatter.ISO_DATE));
							ratingFinancialParameterDto.setSupervisoryRating(auditSchedule.getEffectiveRating());
							ratingFinancialParameterDto.setSupervisoryRatingScore(auditSchedule.getEffectiveRiskPrcntg());
							ratingFinancialParameterDto.setDeskComplianceRating(auditSchedule.getDeskComplianceRating());
							ratingFinancialParameterDto.setDeskComplianceRatingScore(auditSchedule.getDeskComplianceScore());
							ratingFinancialParameterDto.setFieldComplianceRating(auditSchedule.getOnsiteComplianceRating());
							ratingFinancialParameterDto.setFieldComplianceRatingScore(auditSchedule.getOnsiteComplianceScore());
							
						}
						return ratingFinancialParameterDto;
					}

					@Override
					public List transformList(List collection) {
						return collection;
					}
				});
		
		return hqlQuery.list();		 
	}

	 
	/*@SuppressWarnings({ "deprecation"})
	@Override
	public List<RatingFinancialParameterDto> getAuditFinancialParameterList(Integer legalEntityCode, List<String> unitList,
			LocalDate financialYear, String auditTypeCode){		 
		
		
		String[] bomIdsArray = new String[]{"BOM- 15","BOM- 44","BOM- 45","BOM- 186","BOMD59","BOMD183","BOMD502","BOMD370","BOMS59","BOMS183","BOMS502","BOMS370"};	
		//bomIdsArray = new String[]{"CRAR","GROSS NPA","Net NPA","RoA"}; 
		if(BankAuditConstant.INSPECTION_TYPE_RRB.equalsIgnoreCase(auditTypeCode)){	
			bomIdsArray = new String[]{"BOM- 15","BOM- 44","BOM- 45","BOM- 186"}; 
		}else if(BankAuditConstant.INSPECTION_TYPE_DCCB.equalsIgnoreCase(auditTypeCode)){
			bomIdsArray = new String[]{"BOMD59","BOMD183","BOMD502","BOMD370"};			
		}else if(BankAuditConstant.INSPECTION_TYPE_StCBI.equalsIgnoreCase(auditTypeCode)){
			bomIdsArray = new String[]{"BOMS59","BOMS183","BOMS502","BOMS370"}; 
		}else if(BankAuditConstant.INSPECTION_TYPE_SCARDBI.equalsIgnoreCase(auditTypeCode)){
			bomIdsArray = new String[]{""}; 
		}
		List<String> bomIds  = new ArrayList<>(Arrays.asList(bomIdsArray));
		
		String[] bomIdsCRAR = new String[]{"BOM- 15","BOMD59", "BOMS59",""};
		String[] bomIdsGrossNPA = new String[]{"BOM- 44","BOMD183", "BOMS183","" };
		String[] bomIdsNetNPA = new String[]{"BOM- 45","BOMD502", "BOMS502","" };
		String[] bomIdsRoA = new String[]{"BOM- 186","BOMD370", "BOMS370",""};
		
		StringBuilder query = new StringBuilder("Select   "
				+ " (SELECT Concat(e.unitCode, '--', e.unitName)"
				+ " 	FROM   MaintEntity e  " 
				+ " 	WHERE  e.legalEntityCode = sch.legalEntityCode" 
				+ "     AND e.unitCode = sch.unitId )  AS unit_code," 	 
						 
				+ " (SELECT Concat(e1.unitCode, '--', e1.unitName)"
				+ "     FROM  MaintEntity e INNER JOIN   MaintEntity e1 "
				+ "        ON  e.legalEntityCode = sch.legalEntityCode " 
				+ "               AND e.unitCode = sch.unitId " 
				+ "				  AND e1.unitCode = e.parentUnitCode )  AS parentUnitCode,"		

				+ " (Select group_concat(ifc.parameterNameNabard ,'#::#',IFNULL(ifc.submittedValueLatest,'NA'),'#::#',"				
							+ " CASE WHEN ifc.parameterNameNabard IN ('BOM- 15','BOMD59', 'BOMS59')    " //CRAR
							+ " 	 THEN (CASE WHEN ifc.submittedValueLatest IS NULL OR ifc.submittedValueLatest='' THEN 'NA'      "
							+ " 	 		WHEN ifc.submittedValueLatest <0 THEN '1'      "
							+ " 			WHEN ifc.submittedValueLatest >=0 AND ifc.submittedValueLatest<4 THEN '2'      "
							+ " 			WHEN ifc.submittedValueLatest >=4 AND ifc.submittedValueLatest<7 THEN '3'      "
							+ " 			WHEN ifc.submittedValueLatest >=7 AND ifc.submittedValueLatest<9 THEN '4'      "
							+ " 			ELSE '5' END)    "
							+ " 	WHEN ifc.parameterNameNabard IN ('BOM- 44','BOMD183', 'BOMS183')    " //Gross NPA
							+ " 	THEN (CASE WHEN ifc.submittedValueLatest IS NULL OR ifc.submittedValueLatest='' THEN 'NA'      "
							+ " 			WHEN ifc.submittedValueLatest <5 THEN '1'      "
							+ " 			WHEN ifc.submittedValueLatest >=5 AND ifc.submittedValueLatest<10 THEN '2'      "
							+ " 			WHEN ifc.submittedValueLatest >=10 AND ifc.submittedValueLatest<15 THEN '3'      "
							+ " 			WHEN ifc.submittedValueLatest >=15 AND ifc.submittedValueLatest<25 THEN '4'      "
							+ " 			ELSE '5' END)   "
							+ " 	WHEN ifc.parameterNameNabard IN ('BOM- 45','BOMD502', 'BOMS502')    "  //Net NPA
							+ " 	THEN (CASE WHEN ifc.submittedValueLatest IS NULL OR ifc.submittedValueLatest='' THEN 'NA'    "  
							+ " 			WHEN ifc.submittedValueLatest <3 THEN '1'      "
							+ " 			WHEN ifc.submittedValueLatest >=3 AND ifc.submittedValueLatest<5 THEN '2'      "
							+ " 			WHEN ifc.submittedValueLatest >=5 AND ifc.submittedValueLatest<10 THEN '3'      "
							+ " 			WHEN ifc.submittedValueLatest >=10 AND ifc.submittedValueLatest<15 THEN '4'      "
							+ " 			ELSE '5' END)   "
							+ " 	WHEN ifc.parameterNameNabard IN ('BOM- 186','BOMD370', 'BOMS370')    "  //RoA
							+ " 	THEN (CASE WHEN ifc.submittedValueLatest IS NULL OR ifc.submittedValueLatest='' THEN 'NA'      "
							+ " 			WHEN ifc.submittedValueLatest <0 THEN '1'      "
							+ " 			WHEN ifc.submittedValueLatest >=0 AND ifc.submittedValueLatest<0.25 THEN '2'      "
							+ " 			WHEN ifc.submittedValueLatest >=0.25 AND ifc.submittedValueLatest<0.50 THEN '3'      "
							+ " 			WHEN ifc.submittedValueLatest >=0.50 AND ifc.submittedValueLatest<1 THEN '4'      "
							+ " 			ELSE '5' END)	  "
							+ " END   "
							+ " )    "  				
						+ " from   IfcEnsureBaasData ifc  "
						+ " where ifc.auditId=sch.auditId "
						+ " and ifc.auditTypeCode=sch.auditTypeCode "
						+ " and ifc.stageOfFile= :stageOfFile "
						+ " and ifc.parameterNameNabard in (:bomIds) "  			  
						+ " and ifc.legalEntityCode= :legalEntityCode) as parameterValues, "	
				
				+ " sch as auditSchedule  " 
				+ " FROM  AuditSchedule sch "
				+ " where sch.legalEntityCode = :legalEntityCode "
				+ " and sch.unitId in (:unitList)"
				+ " and financialYear='"+(financialYear.getYear()-1)+"-03-31"+"' ");
		
				if(!BankAuditUtil.isEmptyString(auditTypeCode)){
					query.append(" and sch.auditTypeCode = :auditTypeCode ");
				}		
		
			Query hqlQuery = getSession().createQuery(query.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameterList("unitList", unitList)
				//.setParameter("financialYear", financialYear)
				.setParameterList("bomIds", bomIds)
				.setParameter("stageOfFile", BankAuditConstant.STAGE_OF_FILE_LATEST);
				if(!BankAuditUtil.isEmptyString(auditTypeCode)){
					hqlQuery.setParameter("auditTypeCode", auditTypeCode);
				}
				
				hqlQuery.setResultTransformer(new ResultTransformer() {

					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						RatingFinancialParameterDto ratingFinancialParameterDto = new RatingFinancialParameterDto();
						AuditSchedule auditSchedule = new AuditSchedule();
						if (tuple[3] != null) { 
							
							auditSchedule = (AuditSchedule) tuple[3];							
							if (tuple[0] != null)
								ratingFinancialParameterDto.setUnitName(tuple[0].toString());
							if (tuple[1] != null)
								ratingFinancialParameterDto.setParentUnitName(tuple[1].toString());
							
							ratingFinancialParameterDto.setAuditId(auditSchedule.getAuditId());
							ratingFinancialParameterDto.setAuditTypeCode(auditSchedule.getAuditTypeCode());
							ratingFinancialParameterDto.setFinancialYear(financialYear.format(DateTimeFormatter.ISO_DATE));
							
							ratingFinancialParameterDto.setSupervisoryRating(auditSchedule.getEffectiveRating());
							ratingFinancialParameterDto.setSupervisoryRatingScore(auditSchedule.getEffectiveRiskPrcntg());
							ratingFinancialParameterDto.setDeskComplianceRating(auditSchedule.getDeskComplianceRating());
							ratingFinancialParameterDto.setDeskComplianceRatingScore(auditSchedule.getDeskComplianceScore());
							ratingFinancialParameterDto.setFieldComplianceRating(auditSchedule.getOnsiteComplianceRating());
							ratingFinancialParameterDto.setFieldComplianceRatingScore(auditSchedule.getOnsiteComplianceScore());
							
							//set financial parameter values 
							if(tuple[2] != null){
								
								String[] parameterValues = tuple[2].toString().split(",");
								for (String bomValues : parameterValues) {
									String[] bomValuesArr = bomValues.split("#::#");
									String bomId = bomValuesArr[0];
									String valueStr = bomValuesArr[1];
									String groupStr = bomValuesArr[2];
									//Double value = (valueStr==null || ((String)valueStr).equals(""))?null: Double.parseDouble((String)valueStr);
									 if(Arrays.stream(bomIdsCRAR).anyMatch(bomId::equalsIgnoreCase)){
										 ratingFinancialParameterDto.setcRAR(valueStr);
										 ratingFinancialParameterDto.setcRARGroup(groupStr);	
									 }else if(Arrays.stream(bomIdsNetNPA).anyMatch(bomId::equalsIgnoreCase)){
										 ratingFinancialParameterDto.setNetNPA(valueStr);
										 ratingFinancialParameterDto.setNetNPAGroup(groupStr);
									 }else if(Arrays.stream(bomIdsGrossNPA).anyMatch(bomId::equalsIgnoreCase)){
										 ratingFinancialParameterDto.setGrossNPA(valueStr);
										 ratingFinancialParameterDto.setGrossNPAGroup(groupStr);
									 }else if(Arrays.stream(bomIdsRoA).anyMatch(bomId::equalsIgnoreCase)){
										 ratingFinancialParameterDto.setRoA(valueStr);
										 ratingFinancialParameterDto.setRoAGroup(groupStr);
									 }								 
								}
							}else{
								ratingFinancialParameterDto.setcRAR("NA");
								ratingFinancialParameterDto.setcRARGroup("NA");								
								ratingFinancialParameterDto.setGrossNPA("NA");
								ratingFinancialParameterDto.setGrossNPAGroup("NA");	
								ratingFinancialParameterDto.setNetNPA("NA");
								ratingFinancialParameterDto.setNetNPAGroup("NA");								
								ratingFinancialParameterDto.setRoA("NA");
								ratingFinancialParameterDto.setRoAGroup("NA");								
							}							 
						}
						return ratingFinancialParameterDto;
					}

					@Override
					public List transformList(List collection) {
						return collection;
					}
				});
		
		return hqlQuery.list();		 
	}*/
	
	@SuppressWarnings({ "deprecation"})
	@Override
	public List<RatingFinancialParameterDto> getAuditFinancialParameterList(Integer legalEntityCode, List<String> unitList,
			LocalDate financialYear, String auditTypeCode, List<LocalDate>  financialYearList){		 
		
		
		String[] bomIdsArray = new String[]{"BOM- 15","BOM- 44","BOM- 45","BOM- 186","BOMD59","BOMD183","BOMD502","BOMD370","BOMS59","BOMS183","BOMS502","BOMS370"};	
		//bomIdsArray = new String[]{"CRAR","GROSS NPA","Net NPA","RoA"}; 
		 
		if(auditTypeCode.startsWith(BankAuditConstant.INSPECTION_TYPE_RRB_CONTAINS)){
			bomIdsArray = new String[]{"BOM- 15","BOM- 44","BOM- 45","BOM- 186"}; 
		}else if(auditTypeCode.startsWith(BankAuditConstant.INSPECTION_TYPE_DCCB_CONTAIN)){
			bomIdsArray = new String[]{"BOMD59","BOMD183","BOMD502","BOMD370"};			
		}else if(auditTypeCode.startsWith(BankAuditConstant.INSPECTION_TYPE_StCB_CONTAIN)){
			bomIdsArray = new String[]{"BOMS59","BOMS183","BOMS502","BOMS370"}; 
		}else if(auditTypeCode.startsWith(BankAuditConstant.INSPECTION_TYPE_SCARDB_CONTAIN)){
			bomIdsArray = new String[]{""}; 
		}
		List<String> bomIds  = new ArrayList<>(Arrays.asList(bomIdsArray));
		
		String[] bomIdsCRAR = new String[]{"BOM- 15","BOMD59", "BOMS59",""};
		String[] bomIdsGrossNPA = new String[]{"BOM- 44","BOMD183", "BOMS183","" };
		String[] bomIdsNetNPA = new String[]{"BOM- 45","BOMD502", "BOMS502","" };
		String[] bomIdsRoA = new String[]{"BOM- 186","BOMD370", "BOMS370",""};
		
		/*String financialYearStartDt =  null;
	    String financialYearEndDt =  null;
	    if(financialYear.getMonthValue()==1 || financialYear.getMonthValue()==2 || financialYear.getMonthValue()==3){
	    	financialYearStartDt =  (financialYear.getYear()-1)+"-04-01";
	    	financialYearEndDt =  (financialYear.getYear())+"-03-31";
	    }else{
	    	financialYearStartDt =  (financialYear.getYear())+"-04-01";
	    	financialYearEndDt =  (financialYear.getYear()+1)+"-03-31";
	    }*/
		LocalDate financialYearStartDt = financialYearList.get(0);
		LocalDate financialYearEndDt = financialYearList.get(1);
		StringBuilder query = new StringBuilder(" "
				+ " Select unitCode,  parentUnitCode, "
				+ " audit_id, audit_type_code,   "
				+ " effective_rating, effective_risk_prcntg, "
				+ " desk_compliance_rating, desk_compliance_score, "
				+ " onsite_compliance_rating, onsite_compliance_score, "
				+ " group_concat(parameterNameNabard ,'#::#', latestValue ) as parameterValues "
				+ "  From(" 
				+ " Select    "
			 	+ " (SELECT concat(e.unit_code, '-', e.unit_name)"
				+ " 	FROM   maint_entity e  " 
				+ " 	WHERE  e.legal_entity_code = sch.legal_entity_code" 
				+ "     AND e.unit_code = sch.unit_id )  AS unitCode," 	 
						 
				+ " (SELECT concat(e1.unit_code, '--', e1.unit_name)"
				+ "     FROM  maint_entity e INNER JOIN   maint_entity e1 "
				+ "        ON  e.legal_entity_code = sch.legal_entity_code " 
				+ "               AND e.unit_code = sch.unit_id " 
				+ "				  AND e1.unit_code = e.parent_unit_code )  AS parentUnitCode, "
				+ " sch.audit_id, sch.audit_type_code, "
				+ " effective_rating, effective_risk_prcntg, "
				+ " desk_compliance_rating, desk_compliance_score, "
				+ " onsite_compliance_rating, onsite_compliance_score, "				
				+ " ifc.parameter_name_nabard as parameterNameNabard, "
				+ " ifc.submitted_value_latest  AS latestValue " 
				+ " FROM  audit_schedule sch "
				+ " LEFT JOIN ifc_ensure_baas_data ifc  "
				+ " ON  ifc.audit_id=sch.audit_id "
				+ " and ifc.audit_type_code=sch.audit_type_code "
				+ " and ifc.stage_of_file= :stageOfFile "
				+ " and ifc.parameter_name_nabard in (:bomIds) "  			  
				+ " and ifc.legal_entity_code= sch.legal_entity_code" 
				+ " WHERE sch.legal_entity_code = :legalEntityCode "
				+ " AND sch.unit_id in (:unitList)" );
			
				//query.append( " and financial_year='"+(financialYear.getYear()-1)+"-03-31"+"' ");
				query.append("  "
							+ " AND (sch.actual_start_date between '"+financialYearStartDt+"' and '"+financialYearEndDt+"' ) ");				 
				if(!BankAuditUtil.isEmptyString(auditTypeCode)){
					query.append(" and sch.audit_type_code = :auditTypeCode ");
				}
				
				query.append(" ) finParam group by unitCode, parentUnitCode ,    "
						+ " audit_id, audit_type_code,   "
						+ " effective_rating, effective_risk_prcntg, "
						+ " desk_compliance_rating, desk_compliance_score, "
						+ " onsite_compliance_rating, onsite_compliance_score  "); 
		
			Query hqlQuery = getSession().createNativeQuery(query.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameterList("unitList", unitList) 
				.setParameterList("bomIds", bomIds)
				.setParameter("stageOfFile", BankAuditConstant.STAGE_OF_FILE_LATEST);
				if(!BankAuditUtil.isEmptyString(auditTypeCode)){
					hqlQuery.setParameter("auditTypeCode", auditTypeCode);
				}
				
				hqlQuery.setResultTransformer(new ResultTransformer() {

					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						RatingFinancialParameterDto ratingFinancialParameterDto = new RatingFinancialParameterDto();
						 					
						if (tuple[0] != null)
							ratingFinancialParameterDto.setUnitName(tuple[0].toString());
						if (tuple[1] != null)
							ratingFinancialParameterDto.setParentUnitName(tuple[1].toString());
						if (tuple[2] != null)
							ratingFinancialParameterDto.setAuditId(tuple[2].toString());
						if (tuple[2] != null)
							ratingFinancialParameterDto.setAuditTypeCode(tuple[3].toString());
						if (tuple[4] != null)
							ratingFinancialParameterDto.setSupervisoryRating(tuple[4].toString());							 
						if (tuple[5] != null)
							ratingFinancialParameterDto.setSupervisoryRatingScore(tuple[5].toString());
						if (tuple[6] != null)
							ratingFinancialParameterDto.setDeskComplianceRating(tuple[6].toString());
						if (tuple[7] != null)
							ratingFinancialParameterDto.setDeskComplianceRatingScore(tuple[7].toString());
						if (tuple[8] != null)
							ratingFinancialParameterDto.setFieldComplianceRating(tuple[8].toString());
						if (tuple[9] != null)
							ratingFinancialParameterDto.setFieldComplianceRatingScore(tuple[9].toString());
												
						ratingFinancialParameterDto.setFinancialYear(financialYear.format(DateTimeFormatter.ISO_DATE));
						//set financial parameter values 
						if(tuple[10] != null){
							String[] parameterValues = tuple[10].toString().split(",");
							for (String bomValues : parameterValues) {
								String[] bomValuesArr = bomValues.split("#::#");
								String groupStr = "NA";
								String bomId = "";
								String valueStr = "";
								if(bomValuesArr.length==2){
									bomId = bomValuesArr[0];								
									valueStr =  bomValuesArr[1]; // latest value
								}							
								 
								if(Arrays.stream(bomIdsCRAR).anyMatch(bomId::equalsIgnoreCase)){
									 if(BankAuditUtil.isEmptyString(valueStr)){
										 ratingFinancialParameterDto.setcRAR(valueStr);
										 ratingFinancialParameterDto.setcRARGroup(groupStr); 
									 }else{
										 Double value = Double.parseDouble(valueStr);
										 if(value<0){
											 groupStr = "1";
										 }else if(value>=0 && value<4){
											 groupStr = "2";
										 }else if(value>=4 && value<7){
											 groupStr = "3";
										 }else if(value>=7 && value<9){
											 groupStr = "4";
										 }else{
											 groupStr = "5";
										 }
										 ratingFinancialParameterDto.setcRAR(value+"");
										 ratingFinancialParameterDto.setcRARGroup(groupStr);
									 }
								 }else if(Arrays.stream(bomIdsNetNPA).anyMatch(bomId::equalsIgnoreCase)){
									 if(BankAuditUtil.isEmptyString(valueStr)){
										 ratingFinancialParameterDto.setNetNPA(valueStr);
										 ratingFinancialParameterDto.setNetNPAGroup(groupStr);
									 }else{
										 Double value = Double.parseDouble(valueStr);
										 if(value<3){
											 groupStr = "1";
										 }else if(value>=3 && value<5){
											 groupStr = "2";
										 }else if(value>=5 && value<10){
											 groupStr = "3";
										 }else if(value>=10 && value<15){
											 groupStr = "4";
										 }else{
											 groupStr = "5";
										 }
										 ratingFinancialParameterDto.setNetNPA(valueStr);
										 ratingFinancialParameterDto.setNetNPAGroup(groupStr);
									 }										
								 }else if(Arrays.stream(bomIdsGrossNPA).anyMatch(bomId::equalsIgnoreCase)){
									 if(BankAuditUtil.isEmptyString(valueStr)){
										 ratingFinancialParameterDto.setGrossNPA(valueStr);
										 ratingFinancialParameterDto.setGrossNPAGroup(groupStr);
									 }else{
										 Double value = Double.parseDouble(valueStr);
										 if(value<5){
											 groupStr = "1";
										 }else if(value>=5 && value<10){
											 groupStr = "2";
										 }else if(value>=10 && value<15){
											 groupStr = "3";
										 }else if(value>=15 && value<25){
											 groupStr = "4";
										 }else{
											 groupStr = "5";
										 }										 
										 ratingFinancialParameterDto.setGrossNPA(valueStr);
										 ratingFinancialParameterDto.setGrossNPAGroup(groupStr);
									 }											 
								 }else if(Arrays.stream(bomIdsRoA).anyMatch(bomId::equalsIgnoreCase)){
									 if(BankAuditUtil.isEmptyString(valueStr)){
										 ratingFinancialParameterDto.setRoA(valueStr);
										 ratingFinancialParameterDto.setRoAGroup(groupStr);
									 }else{
										 Double value = Double.parseDouble(valueStr);
										 if(value<0){
											 groupStr = "1";
										 }else if(value>=0 && value<0.25){
											 groupStr = "2";
										 }else if(value>=0.25 && value<0.50){
											 groupStr = "3";
										 }else if(value>=0.50  && value<1){
											 groupStr = "4";
										 }else{
											 groupStr = "5";
										 }
										 ratingFinancialParameterDto.setRoA(valueStr);
										 ratingFinancialParameterDto.setRoAGroup(groupStr);										 
									 }				
								 }								 
							 }
						}else{
							ratingFinancialParameterDto.setcRAR("NA");
							ratingFinancialParameterDto.setcRARGroup("NA");								
							ratingFinancialParameterDto.setGrossNPA("NA");
							ratingFinancialParameterDto.setGrossNPAGroup("NA");	
							ratingFinancialParameterDto.setNetNPA("NA");
							ratingFinancialParameterDto.setNetNPAGroup("NA");								
							ratingFinancialParameterDto.setRoA("NA");
							ratingFinancialParameterDto.setRoAGroup("NA");								
						}							 
					
						return ratingFinancialParameterDto;
					}

					@Override
					public List transformList(List collection) {
						return collection;
					}
				});
		
		return hqlQuery.list();		 
	}
	
	@Override
	public List<LettersAuditDto> getAuditDetail(Integer legalEntityCode, LocalDate financialYear, String auditTypeCode,
			List<String> unitList, List<LocalDate>  financialYearList ) {
		 
		Session session=getSession();
	    session.setDefaultReadOnly(true);
	    /*String financialYearStartDt =  null;
	    String financialYearEndDt =  null;
	    if(financialYear.getMonthValue()==1 || financialYear.getMonthValue()==2 || financialYear.getMonthValue()==3){
	    	financialYearStartDt =  (financialYear.getYear()-1)+"-04-01";
	    	financialYearEndDt =  (financialYear.getYear())+"-03-31";
	    }else{
	    	financialYearStartDt =  (financialYear.getYear())+"-04-01";
	    	financialYearEndDt =  (financialYear.getYear()+1)+"-03-31";
	    } */
	    /*LocalDate financialYearStartDt = LocalDate.parse((financialYear.getYear()-1)+"-04-01");
		LocalDate financialYearEndDt = LocalDate.parse((financialYear.getYear())+"-03-31");*/
		List<String> auditList = getAuditListAll(legalEntityCode, unitList, financialYear, auditTypeCode, financialYearList);
		if (auditList==null || auditList.size()==0) {
			return new ArrayList<LettersAuditDto>();
		}
		StringBuilder query = new StringBuilder(" Select  sch as auditSchedule, "
				
					+ " (Select concat(sch.auditStatus , '-' , g.description) from  GeneralParameter g where g.legalEntityCode =sch.legalEntityCode and g.key1='STATUS'"
							+ " and g.key2='AUDIT_STATUS' and g.value=sch.auditStatus) as auditStatus, "
					+ " (Select concat(sch.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, "	
					+ " (Select concat(sch.respondStatus , '-' , g.description) from  GeneralParameter g where g.legalEntityCode =sch.legalEntityCode and g.key1='STATUS'"
							+ " and g.key2='AUDIT_STATUS' and g.value=sch.respondStatus) as respondStatus, "
					+ " (Select concat(sch.reviewStatus , '-' , g.description) from  GeneralParameter g where g.legalEntityCode =sch.legalEntityCode and g.key1='STATUS'"
							+ " and g.key2='AUDIT_STATUS' and g.value=sch.reviewStatus) as reviewStatus, "
					
 
					+ " (select 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(e1.unitCode, '--', e1.unitName) FROM  MaintEntity e INNER JOIN   MaintEntity e1   ON  e.legalEntityCode = sch.legalEntityCode " 
							+ " AND e.unitCode = sch.unitId   AND e1.unitCode = e.parentUnitCode )  AS parentUnitCode,"	
					
					+ " (select group_concat(u.userId,'-', ifnull(u.firstName,''), ' ', ifnull(u.middleName,''),' ', ifnull(u.lastName,'')) from AuditTeam t inner join User u on u.userId=t.userId where "
					+ " t.legalEntityCode=sch.legalEntityCode AND t.auditId=sch.auditId AND t.userType='M' ) AS teamMember,"
					
					+ " (select group_concat(u.userId,'-', ifnull(u.firstName,''), ' ', ifnull(u.middleName,''),' ', ifnull(u.lastName,'')) from AuditTeam t inner join User u on u.userId=t.userId where "
					+ " t.legalEntityCode=sch.legalEntityCode AND t.auditId=sch.auditId AND t.userType='L' )  AS teamLead "	
					 				 
					+ " FROM  AuditSchedule sch "
					+ " where sch.legalEntityCode = :legalEntityCode "
					+ " and sch.auditId in (:auditList)"
					//+ " and sch.unitId in (:unitList)"
					//+ " and sch.auditStatus  NOT IN  ('AD','AU','AN') " 
							+ " "); 
			/* query.append("  "
					+ " AND  ( case when ifnull(actualStartDate,0)  then (sch.scheduleStartDate between '"+financialYearStartDt+"' and '"+financialYearEndDt+"' ) "
					+ "     	else (sch.actualStartDate between '"+financialYearStartDt+"' and '"+financialYearEndDt+"' ) end  )  "
					+ "  ");*/		
			if(!BankAuditUtil.isEmptyString(auditTypeCode)){
				query.append(" and sch.auditTypeCode = :auditTypeCode ");
			}
		Query hqlQuery = getSession().createQuery(query.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameterList("auditList", auditList);
				//.setParameterList("unitList", unitList) ;
				if(!BankAuditUtil.isEmptyString(auditTypeCode)){
					hqlQuery.setParameter("auditTypeCode", auditTypeCode);
				}
			
		hqlQuery.setResultTransformer(new ResultTransformer() {
					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						LettersAuditDto lettersAuditDto = new LettersAuditDto();
						AuditSchedule auditSchedule = new AuditSchedule();
						if (tuple[0] != null) {						
							auditSchedule = (AuditSchedule) tuple[0];	
							
							    
							lettersAuditDto.setAuditId(auditSchedule.getAuditId());
							lettersAuditDto.setPlanId(auditSchedule.getPlanId()+"-"+auditSchedule.getAuditId());
							lettersAuditDto.setLegalEntityCode(legalEntityCode+"");
							lettersAuditDto.setFinancialYear(financialYear.format(DateTimeFormatter.ISO_DATE));
							lettersAuditDto.setObservationStatus(auditSchedule.getObservationStatus());
							
							if(auditSchedule.getActualStartDate()!=null)
								lettersAuditDto.setActualStartDate(auditSchedule.getActualStartDate().toString());
							
							if(auditSchedule.getReportDate()!=null)
								lettersAuditDto.setExitDate(auditSchedule.getReportDate().toString());
							
							if(auditSchedule.getReportDispatchDate()!=null)
								lettersAuditDto.setDispatchDate(auditSchedule.getReportDispatchDate().toString());
							
							
							
							if (tuple[1] != null)
								lettersAuditDto.setAuditStatus(tuple[1].toString());
							
							if (tuple[2] != null)
								lettersAuditDto.setReportStatus(tuple[2].toString());
							
							if (tuple[3] != null)
								lettersAuditDto.setComplianceStatus(tuple[3].toString());
							
							if (tuple[4] != null)
								lettersAuditDto.setScrutinyStatus(tuple[4].toString());
							
							if (tuple[5] != null)
								lettersAuditDto.setAuditTypeCode(tuple[5].toString());
							
							if (tuple[6] != null)
								lettersAuditDto.setUnitName(tuple[6].toString());
							
							if (tuple[7] != null)
								lettersAuditDto.setParentUnitName(tuple[7].toString());
														
							if (tuple[8] != null)
								lettersAuditDto.setAio(tuple[8].toString());
							if (tuple[9] != null)
								lettersAuditDto.setPio(tuple[9].toString());
							  							 
						}
						return lettersAuditDto;
					}

					@Override
					public List transformList(List collection) {
						return collection;
					}
				});		
		return hqlQuery.list();	
	}
	
	 
	@Override
	public List<LettersAuditDto> getLettersDetail(Integer legalEntityCode, LocalDate financialYear, String auditTypeCode,
			List<String> unitList, List<LocalDate>  financialYearList ) {
		 
		Session session=getSession();
	    session.setDefaultReadOnly(true);
	    /*String financialYearStartDt =  null;
	    String financialYearEndDt =  null;
	    if(financialYear.getMonthValue()==1 || financialYear.getMonthValue()==2 || financialYear.getMonthValue()==3){
	    	financialYearStartDt =  (financialYear.getYear()-1)+"-04-01";
	    	financialYearEndDt =  (financialYear.getYear())+"-03-31";
	    }else{
	    	financialYearStartDt =  (financialYear.getYear())+"-04-01";
	    	financialYearEndDt =  (financialYear.getYear()+1)+"-03-31";
	    }*/
	    LocalDate financialYearStartDt = financialYearList.get(0);
		LocalDate financialYearEndDt = financialYearList.get(1);
		StringBuilder query = new StringBuilder(" Select  "
					+ "  sch.legal_entity_code, "
				
					+ " (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(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 reportStatus, "	
					+ " (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, "
					+ " (Select concat(sch.review_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.review_status) as reviewStatus, "
					
 
					+ " (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,"
					+ " (Select concat(unit_code , '-' , unit_name) from  maint_entity e where e.unit_code =sch.unit_id and e.legal_entity_code =sch.legal_entity_code ) as unitId,  "
					+ " (SELECT Concat(e1.unit_code, '--', e1.unit_name) FROM  maint_entity e INNER JOIN   maint_entity e1   ON  e.legal_entity_code = sch.legal_entity_code " 
							+ " AND e.unit_code = sch.unit_id   AND e1.unit_code = e.parent_unit_code )  AS parentUnitCode,"	
					
					+ " (select group_concat(u.user_id,'-', ifnull(u.first_name,''), ' ', ifnull(u.middle_name,''),' ', ifnull(u.last_name,'')) from audit_team t inner join user u on u.user_id=t.user_id where "
							+ " t.legal_entity_code=sch.legal_entity_code AND t.audit_id=sch.audit_id AND t.user_type='M' ) AS teamMember,"
					
					+ " (select group_concat(u.user_id,'-', ifnull(u.first_name,''), ' ', ifnull(u.middle_name,''),' ', ifnull(u.last_name,'')) from audit_team t inner join user u on u.user_id=t.user_id where "
							+ " t.legal_entity_code=sch.legal_entity_code AND t.audit_id=sch.audit_id AND t.user_type='L' ) AS teamLead, "		
				 
					+ " (Select concat(letters.letter_code , '-' , g.description) from  general_parameter g where g.legal_entity_code =letters.legal_entity_code and g.key_1='LETTER'"
						+ " and (g.key_2='AUDIT' OR g.key_2='AUDIT_BEFORE') and g.value=letters.letter_code) as lettersName, "
					
					+ " (Select concat(letters.status , '-' , g.description) from  general_parameter g where g.legal_entity_code =letters.legal_entity_code and g.key_1='STATUS'"
						+ " and g.key_2='STATUS' and g.value=letters.status) as lettersStatus, "
					
					+ " sch.actual_start_date, sch.report_date, sch.report_dispatch_date, sch.audit_id, sch.plan_id, "	//#16
					+ " sch.observation_status "				 
					+ " FROM audit_schedule sch " 
										
					+ " LEFT JOIN (select * from audit_letter_user_inputs_wrk "
							+ "	 UNION		"
					+ "  select  * from audit_letter_user_inputs ) letters  "
					+ "  ON letters.audit_id = sch.audit_id  AND letters.legal_entity_code = sch.legal_entity_code " 
					//+ "  AND letters.auditTypeCode = sch.auditTypeCode"
					
					+ " where sch.legal_entity_code = :legalEntityCode "
					+ " and sch.unit_id in (:unitList)"
					+ " and sch.audit_status  NOT IN  ('AD','AU','AN') " 
					//+ " and sch.financial_year='"+(financialYear.getYear()-1)+"-03-31"+"' "
							+ " "); 
				query.append("  "
						+ " AND (case  When actual_start_date IS NOT NULL "
						+ "		THEN (sch.actual_start_date between '"+financialYearStartDt+"' and '"+financialYearEndDt+"' ) "
						+ " 	ELSE (sch.schedule_start_date between '"+financialYearStartDt+"' and '"+financialYearEndDt+"')  END )  "
						+ "  ");	
				if(!BankAuditUtil.isEmptyString(auditTypeCode)){
					query.append("and sch.audit_type_code = :auditTypeCode ");
				}

 	System.out.println(query.toString());
		Query hqlQuery = getSession().createNativeQuery(query.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameterList("unitList", unitList) ;
				if(!BankAuditUtil.isEmptyString(auditTypeCode)){
					hqlQuery.setParameter("auditTypeCode", auditTypeCode);
				}
			
		hqlQuery.setResultTransformer(new ResultTransformer() {
					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						LettersAuditDto lettersAuditDto = new LettersAuditDto();
						 
						if (tuple[0] != null) {							
							    
							lettersAuditDto.setAuditId(tuple[15].toString()); 
							lettersAuditDto.setPlanId(tuple[16].toString()+"-"+tuple[15].toString());
							
							lettersAuditDto.setLegalEntityCode(legalEntityCode+"");
							lettersAuditDto.setFinancialYear(financialYear.format(DateTimeFormatter.ISO_DATE));							
							
							if (tuple[12] != null)
								lettersAuditDto.setActualStartDate(tuple[12].toString());
							
							if (tuple[13] != null)
								lettersAuditDto.setExitDate(tuple[13].toString());
							
							if (tuple[14] != null)
								lettersAuditDto.setDispatchDate(tuple[14].toString());							
							
							if (tuple[1] != null)
								lettersAuditDto.setAuditStatus(tuple[1].toString());
							
							if (tuple[2] != null)
								lettersAuditDto.setReportStatus(tuple[2].toString());
							
							if (tuple[3] != null)
								lettersAuditDto.setComplianceStatus(tuple[3].toString());
							
							if (tuple[4] != null)
								lettersAuditDto.setScrutinyStatus(tuple[4].toString());
							
							if (tuple[5] != null)
								lettersAuditDto.setAuditTypeCode(tuple[5].toString());
							
							if (tuple[6] != null)
								lettersAuditDto.setUnitName(tuple[6].toString());
							
							if (tuple[7] != null)
								lettersAuditDto.setParentUnitName(tuple[7].toString());
														
							if (tuple[8] != null)
								lettersAuditDto.setAio(tuple[8].toString());
							if (tuple[9] != null)
								lettersAuditDto.setPio(tuple[9].toString());
							
							if (tuple[10] != null)
								lettersAuditDto.setLetterName(tuple[10].toString());
							if (tuple[11] != null)
								lettersAuditDto.setLetterStatus(tuple[11].toString());
							
							if (tuple[17] != null)
								lettersAuditDto.setObservationStatus(tuple[17].toString());							
							  							 
						}
						return lettersAuditDto;
					}

					@Override
					public List transformList(List collection) {
						return collection;
					}
				});		
		return hqlQuery.list();	
	}
	
	private List<String> getAuditListAll(Integer legalEntityCode , List<String> unitList, LocalDate financialYear, String auditTypeCode, List<LocalDate>  financialYearList){
        List<String> auditList =null;
        try {
        	LocalDate financialYearStartDt = financialYearList.get(0);
			LocalDate financialYearEndDt = financialYearList.get(1);
			StringBuilder query= new StringBuilder("SELECT audit_id  FROM audit_schedule WHERE  unit_id IN (:unitList)"  
        			+ " AND legal_entity_code=:legalEntityCode AND audit_status  NOT IN  ('AD','AU','AN')  ") ;
			query.append(" AND (case when actual_start_date is not null "
							+ " then (actual_start_date between '"+financialYearStartDt+"' and '"+financialYearEndDt+"')   "
							+ " else (schedule_start_date between '"+financialYearStartDt+"' and '"+financialYearEndDt+"') end )");
			
			if(!BankAuditUtil.isEmptyString(auditTypeCode)) {
				query.append("AND audit_type_code ='"+ auditTypeCode+"'");
			}
        	Query hqlQuery= getSession().createNativeQuery(query.toString())
        			.setParameter("legalEntityCode", legalEntityCode)
        			.setParameter("unitList", unitList);
        			//.setParameter("financialYear", (financialYear.minusYears(1)));
        	auditList=  (List<String>)hqlQuery.list();
        }catch(Exception e) {
        	e.printStackTrace(System.out);
        }
        logger.info("auditList .."+ auditList);
        return auditList;
	}
	
	/** MIS Report Start***/
	@Override
	public List<ReportMISDto> reportStatusFinalisationDateMISReport(Integer legalEntityCode, String auditTypeCode, LocalDate financialYear, List<LocalDate> financialYearList, 
			List<String> unitList){ 
		 
		StringBuilder strQry =  new StringBuilder();
		strQry.append("SELECT sch as auditSch, "
				
				+ " (SELECT concat(auditTypeCode , '-' , auditTypeDesc) FROM MaintAuditTypeDesc at  WHERE at.auditTypeCode = sch.auditTypeCode "
					+ " AND at.legalEntityCode = sch.legalEntityCode ) as auditTypeCodeDesc,"
				+ " (SELECT unitName  FROM  MaintEntity e WHERE e.unitCode = sch.unitId AND e.legalEntityCode =sch.legalEntityCode ) as unitName,  "
				+ " (SELECT Concat(e1.unitCode, '-', e1.unitName) FROM  MaintEntity e INNER JOIN   MaintEntity e1   ON  e.legalEntityCode = sch.legalEntityCode " 
						+ " AND e.unitCode = sch.unitId   AND e1.unitCode = e.parentUnitCode )  AS parentUnitCodeName,"
							
				+ "  '' as zoneCode, "
				+ " (SELECT m.zoneName FROM MaintEntity e, MaintZoneMapping m WHERE  "
						+ " (CASE WHEN e.levelCode IN ('3') THEN e.parentUnitCode ELSE e.unitCode END) = m.unitCode "
						+ " AND e.unitCode=sch.unitId) AS zoneName,"   // zone.zoneCode,  zone.zoneName, //5
								
				+ " rating.reviewedDate,  rating.hoReviewedDate, "  //6, 7
				+ " schStatus.gradingCheckerTimestampRO, schStatus.gradingCheckerTimestampHO, " //8,9
				+ " schStatus.gradingROComplianceCheckerTimestamp, schStatus.gradingHOComplianceCheckerTimestamp, " //10, 11
				
				+ " (SELECT ltr.checkerTimestamp FROM  AuditLetterUserInputs ltr WHERE sch.legalEntityCode  = ltr.legalEntityCode AND sch.auditTypeCode = ltr.auditTypeCode AND sch.auditId = ltr.auditId  "
						+ " AND ltr.letterCode = :letterCodeCompoRating ) as compRatingFinalDate, "
				 
				+ " (SELECT ltr.checkerTimestamp FROM  AuditLetterUserInputs ltr WHERE sch.legalEntityCode  = ltr.legalEntityCode AND sch.auditTypeCode = ltr.auditTypeCode AND sch.auditId = ltr.auditId  "
						+ " AND ltr.letterCode = :letterCodeRN ) as rnFinlDate, "
				
				+ " (SELECT ltr.checkerTimestamp FROM  AuditLetterUserInputs ltr WHERE sch.legalEntityCode  = ltr.legalEntityCode AND sch.auditTypeCode = ltr.auditTypeCode AND sch.auditId = ltr.auditId  "
						+ " AND ltr.letterCode = :letterCodeDirLtr ) as dirLtrFinlDate, "
				
				+ " (Select ltr.checkerTimestamp FROM  AuditLetterUserInputs ltr WHERE sch.legalEntityCode  = ltr.legalEntityCode AND sch.auditTypeCode = ltr.auditTypeCode AND sch.auditId = ltr.auditId  "
						+ " AND ltr.letterCode = :letterCodeFwdLtr ) as fwdLtrFinlDate "
				
				+ " FROM  AuditSchedule sch "
				+ " LEFT JOIN AuditScheduleStatus schStatus  ON  sch.legalEntityCode  = schStatus.legalEntityCode "
				+ " AND sch.auditTypeCode = schStatus.auditTypeCode  AND sch.auditId = schStatus.auditId  "
				+ " LEFT JOIN AuditRating rating  ON  sch.legalEntityCode = rating.legalEntityCode "
				+ " AND sch.auditTypeCode = rating.auditTypeCode  AND sch.auditId = rating.auditId " 				 				
				+ " WHERE sch.legalEntityCode = :legalEntityCode ");
		
		if(!BankAuditUtil.isEmptyString(auditTypeCode)){
			strQry.append(" AND sch.auditTypeCode = :auditTypeCode ");
		}		
		strQry.append(" AND sch.unitId IN (:unitList) ");
		strQry.append(" AND (sch.actualStartDate BETWEEN '"+financialYearList.get(0)+"' AND '"+financialYearList.get(1)+"')  ");
		 
		Session session=getSession();
	    session.setDefaultReadOnly(true);
	    
		Query hqlQuery= session.createQuery(strQry.toString())
    			.setParameter("legalEntityCode", legalEntityCode);
		if(!BankAuditUtil.isEmptyString(auditTypeCode)){
			hqlQuery.setParameter("auditTypeCode", auditTypeCode);
		}
		hqlQuery.setParameter("unitList", unitList)
				.setParameter("letterCodeCompoRating", BankAuditConstant.LETTER_CODE_CompositeRating)
				.setParameter("letterCodeRN", BankAuditConstant.LETTER_CODE_RN)
				.setParameter("letterCodeDirLtr", BankAuditConstant.LETTER_CODE_Directive)
				.setParameter("letterCodeFwdLtr", BankAuditConstant.LETTER_CODE_FwdL);
		
		hqlQuery.setResultTransformer(new ResultTransformer() {
			
			@Override
			public Object transformTuple(Object[] tuple, String[] aliases) {
				ReportMISDto  reportMISDto = new ReportMISDto();
				if(tuple[0] != null){
					AuditSchedule auditSchedule = (AuditSchedule) tuple[0];
					
					reportMISDto.setLegalEntityCode(legalEntityCode);
					reportMISDto.setAuditTypeCode(String.valueOf(tuple[1]));
					reportMISDto.setAuditId(auditSchedule.getAuditId());
					reportMISDto.setPlanId(auditSchedule.getPlanId()+"-"+auditSchedule.getAuditId()); 
					reportMISDto.setActualStartDate(auditSchedule.getActualStartDate());
					reportMISDto.setActualEndDate(auditSchedule.getReportDate()); // instead of actualEndDate, reportDate is Actual end date
					reportMISDto.setRespondedDate(auditSchedule.getRespondedDate());
					reportMISDto.setReviewedDate(auditSchedule.getReviewedDate());
					reportMISDto.setIrFinalisationDate(auditSchedule.getActualEndDate()); //Instead of IrFinalisationDate, actualEndDate is Finalization date
					reportMISDto.setReportDispatchDate(auditSchedule.getReportDispatchDate());
					reportMISDto.setFinancialYear(auditSchedule.getFinancialYear());
					 
					reportMISDto.setUnitCode(auditSchedule.getUnitId()); 
					if(tuple[2] != null){
						reportMISDto.setUnitName(tuple[2].toString()); 
					}
					if(tuple[3] != null){
						reportMISDto.setParentUnitCode(tuple[3].toString().split("-")[0]); 
						reportMISDto.setParentUnitName(tuple[3].toString().split("-")[1]); 
					}
					reportMISDto.setZoneCode(String.valueOf(tuple[4]));
					reportMISDto.setZoneName(String.valueOf(tuple[5]));
					
					if(tuple[6] != null){
						reportMISDto.setRoSupervisoryRatingFinlDate((Date)tuple[6]);
					}
					if(tuple[7] != null){
						reportMISDto.setHoSupervisoryRatingFinlDate((Date)tuple[7]);
					} 
					
					if(tuple[8] != null){
						reportMISDto.setRoGradingFinlDate((Date)tuple[8]);
					} 
					if(tuple[9] != null){
						reportMISDto.setHoGradingFinlDate((Date)tuple[9]);
					} 
					if(tuple[10] != null){
						reportMISDto.setRoGradingComplianceDate((Date)tuple[10]);
					} 
					if(tuple[11] != null){
						reportMISDto.setHoGradingComplianceDate((Date)tuple[11]);
					} 
					
					if(tuple[12] != null){
						reportMISDto.setCompositeRatingFinlDate((Date)tuple[12]);
					} 
					if(tuple[13] != null){
						reportMISDto.setRecommendaryNoteFinlDate((Date)tuple[13]);
					} 
					if(tuple[14] != null){
						reportMISDto.setDirectiveLetterFinlDate((Date)tuple[14]);
					} 
					if(tuple[15] != null){
						reportMISDto.setForwardingLetterFinlDate((Date)tuple[15]);
					} 
				}				
				return reportMISDto;
			}
			
			@Override
			public List transformList(List collection) { 
				return collection;
			}
		});
		
		List<ReportMISDto> listReportMISDto = hqlQuery.list();		
		return ((listReportMISDto==null || listReportMISDto.size()==0)?new ArrayList<>():listReportMISDto);		
	}
	
	@Override
	public List<ReportMISDto> auditStatusMISReport(Integer legalEntityCode, String auditTypeCode, LocalDate financialYear, List<LocalDate> financialYearList, 
			List<String> unitList){
		StringBuilder strQry =  new StringBuilder();
		strQry.append("Select sch as auditSch, "				
				+ " (select concat(auditTypeCode , '-' , auditTypeDesc) FROM MaintAuditTypeDesc at  WHERE at.auditTypeCode = sch.auditTypeCode "
					+ " AND at.legalEntityCode = sch.legalEntityCode ) as auditTypeCodeDesc,"  //1
				+ " (Select unitName  FROM  MaintEntity e WHERE e.unitCode = sch.unitId AND e.legalEntityCode =sch.legalEntityCode ) as unitName,  "
				+ " (SELECT Concat(e1.unitCode, '-', e1.unitName) FROM  MaintEntity e INNER JOIN   MaintEntity e1   ON  e.legalEntityCode = sch.legalEntityCode " 
						+ " AND e.unitCode = sch.unitId   AND e1.unitCode = e.parentUnitCode )  AS parentUnitCodeName,"		//3		

				+ "  '' as zoneCode, "
				+ " (SELECT m.zoneName FROM MaintEntity e, MaintZoneMapping m WHERE  "
						+ " (CASE WHEN e.levelCode IN ('3') THEN e.parentUnitCode ELSE e.unitCode END) = m.unitCode "
						+ " AND e.unitCode=sch.unitId) AS zoneName,"   // zone.zoneCode,  zone.zoneName,	//5

				+ " (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, " //6
				+ " (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, " //7
				
				+ " (select group_concat(u.userId,'-', ifnull(u.firstName,''), ' ', ifnull(u.middleName,''),' ', ifnull(u.lastName,'')) FROM AuditTeam t INNER JOIN User u ON u.userId=t.userId "
						+ " WHERE t.legalEntityCode=sch.legalEntityCode AND t.legalEntityCode=u.legalEntityCode AND t.auditId=sch.auditId AND t.userType='M' ) AS teamMember,"
				
				+ " (select group_concat(u.userId,'-', ifnull(u.firstName,''), ' ', ifnull(u.middleName,''),' ', ifnull(u.lastName,'')) FROM AuditTeam t INNER JOIN User u ON u.userId=t.userId "
						+ " WHERE t.legalEntityCode=sch.legalEntityCode  AND t.legalEntityCode=u.legalEntityCode AND t.auditId=sch.auditId AND t.userType='L' ) AS teamLead,  "	 //9	
 				
				+ " (Select group_concat(u.userId,'-', ifnull(u.firstName,''), ' ', ifnull(u.middleName,''),' ', ifnull(u.lastName,'')) FROM AuditScheduleStagesStatusHst schStatusHst "
						+ " INNER JOIN User u ON u.userId = schStatusHst.makerId  AND u.legalEntityCode = schStatusHst.legalEntityCode  WHERE  sch.legalEntityCode  = schStatusHst.legalEntityCode  "
						+ " AND sch.auditTypeCode = schStatusHst.auditTypeCode  AND sch.auditId = schStatusHst.auditId AND schStatusHst.auditStatus = '"+BankAuditConstant.AUDIT_STATUS_AI+"'  "
						+ " AND schStatusHst.observationStatus = '"+BankAuditConstant.OBSERVATION_STATUS_AE+"'   AND schStatusHst.reportStatus =  '"+BankAuditConstant.REPORT_STATUS_RI+"' "
						+ " AND schStatusHst.code='"+BankAuditConstant.REPORT_STATUS_RI+"' AND (schStatusHst.respondStatus IS NULL OR schStatusHst.respondStatus <> '') ) AS allReportReviewTeam, "  //10

				+ " (Select group_concat(u.userId,'-', ifnull(u.firstName,''), ' ', ifnull(u.middleName,''),' ', ifnull(u.lastName,''), '@#@',  ifnull(schStatusHst.makerDesignation,''), '@#@ep') "
						+ "	FROM AuditScheduleStagesStatusHst schStatusHst INNER JOIN User u ON u.userId = schStatusHst.makerId  AND u.legalEntityCode = schStatusHst.legalEntityCode "
						+ " WHERE  sch.legalEntityCode  = schStatusHst.legalEntityCode  AND sch.auditTypeCode = schStatusHst.auditTypeCode  AND sch.auditId = schStatusHst.auditId "
						+ " AND schStatusHst.auditStatus = '"+BankAuditConstant.AUDIT_STATUS_AS+"' AND schStatusHst.observationStatus = '"+BankAuditConstant.OBSERVATION_STATUS_AE+"' "
						+ " AND schStatusHst.reportStatus =  '"+BankAuditConstant.REPORT_STATUS_RS+"' AND schStatusHst.code='"+BankAuditConstant.REPORT_STATUS_RS+"' "
						+ "  AND (schStatusHst.respondStatus IS NULL OR schStatusHst.respondStatus <> '')  ) AS  finalisingOfficer, " 					//11		
				 
				+ " schStatus.complianceObservations, schStatus.nonComplianceObservations, schStatus.nonComplianceMajorObservations, schStatus.nonComplianceMinorObservations, "
				+ " schStatus.naObservationsExcludeOthers, schStatus.nonCompliancePersistenceObservations   "		
				 				
				/*+ " (Select  count(obs.observationId)  FROM AuditObservation obs  WHERE  sch.legalEntityCode  = obs.legalEntityCode  AND sch.auditTypeCode = obs.auditTypeCode  AND sch.auditId = obs.auditId " 
					+ " AND obs.complianceCategory = :compliance AND obs.submissionStatus = :submissionStatusAuth ) AS compCount, " 	//12
				
				+ " (Select  count(obs.observationId)  FROM AuditObservation obs  WHERE  sch.legalEntityCode  = obs.legalEntityCode  AND sch.auditTypeCode = obs.auditTypeCode  AND sch.auditId = obs.auditId " 
					+ " AND obs.complianceCategory = :nonCompliance AND obs.submissionStatus = :submissionStatusAuth ) AS nonCompCount, " 	
				
				+ " (Select  count(obs.observationId)  FROM AuditObservation obs  WHERE  sch.legalEntityCode  = obs.legalEntityCode  AND sch.auditTypeCode = obs.auditTypeCode  AND sch.auditId = obs.auditId " 
					+ " AND obs.complianceCategory = :nonCompliance AND obs.submissionStatus = :submissionStatusAuth AND obs.observationCriticality = :obsCriticalityMajor ) AS nonCompMjorCount, " 	//14	
				
				+ " (Select  count(obs.observationId)  FROM AuditObservation obs  WHERE  sch.legalEntityCode  = obs.legalEntityCode  AND sch.auditTypeCode = obs.auditTypeCode  AND sch.auditId = obs.auditId " 
					+ " AND obs.complianceCategory = :nonCompliance AND obs.submissionStatus = :submissionStatusAuth AND obs.observationCriticality = :obsCriticalityMinor ) AS nonCompMinorCount, " 		*/
									
				/*+ " (Select  count(obs.observationId)  FROM AuditObservation obs INNER JOIN ActivityProcessFindingMapping mapping"
				+ " ON mapping.mappingId = obs.mappingId AND  mapping.legalEntityCode = obs.legalEntityCode "
				+ " AND mapping.auditTypeCode = obs.auditTypeCode "
				+ " INNER JOIN MaintAuditFinding f ON f.legalEntityCode = mapping.legalEntityCode AND f.findingCode = mapping.findingCode  "
				+ " WHERE  sch.legalEntityCode  = obs.legalEntityCode  AND sch.auditTypeCode = obs.auditTypeCode  AND sch.auditId = obs.auditId " 
				+ " AND obs.complianceCategory = :complianceNA AND obs.submissionStatus = :submissionStatusAuth"
				+ " AND (f.findingName!='Others' AND f.findingName != 'Any other (Specify)' ) ) AS naCount, " 	//16	

				+ " (Select  count(obs.observationId)  FROM AuditObservation obs  WHERE  sch.legalEntityCode  = obs.legalEntityCode  AND sch.auditTypeCode = obs.auditTypeCode  AND sch.auditId = obs.auditId " 
					+ " AND obs.complianceCategory = :nonCompliance AND obs.submissionStatus = :submissionStatusAuth AND obs.nonCompliancePersistence = :persistenceYes ) AS nonCompPersistenceCount " 	*/	
	 			
				+ " FROM  AuditSchedule sch  "
				+ " LEFT JOIN AuditScheduleStatus schStatus ON sch.legalEntityCode  = schStatus.legalEntityCode "
				+ " AND sch.auditTypeCode = schStatus.auditTypeCode  AND sch.auditId = schStatus.auditId  " 				
				+ " WHERE sch.legalEntityCode = :legalEntityCode ");
		
		if(!BankAuditUtil.isEmptyString(auditTypeCode)){
			strQry.append(" AND sch.auditTypeCode = :auditTypeCode ");
		}		
		strQry.append(" AND sch.unitId IN (:unitList) ");
		strQry.append(" AND (sch.actualStartDate BETWEEN '"+financialYearList.get(0)+"' AND '"+financialYearList.get(1)+"')  ");
		 
		Session session=getSession();
	    session.setDefaultReadOnly(true);
	    
	    Query hqlQuery= session.createQuery(strQry.toString())
    			.setParameter("legalEntityCode", legalEntityCode);
		if(!BankAuditUtil.isEmptyString(auditTypeCode)){
			hqlQuery.setParameter("auditTypeCode", auditTypeCode);
		}
		hqlQuery.setParameter("unitList", unitList);
				/*.setParameter("submissionStatusAuth", BankAuditConstant.STATUS_AUTH)
				.setParameter("compliance", BankAuditConstant.COMPLIANCE_YES)
				.setParameter("nonCompliance", BankAuditConstant.COMPLIANCE_NO)
				.setParameter("complianceNA", BankAuditConstant.COMPLIANCE_NA)				
				.setParameter("obsCriticalityMajor", BankAuditConstant.OBSERVATION_CRITICALITY_MAJOR)
				.setParameter("obsCriticalityMinor", BankAuditConstant.OBSERVATION_CRITICALITY_MINOR)
				.setParameter("persistenceYes", BankAuditConstant.YES);*/
		
		hqlQuery.setResultTransformer(new ResultTransformer() {			
			@Override
			public Object transformTuple(Object[] tuple, String[] aliases) {
				ReportMISDto  reportMISDto = new ReportMISDto();
				if(tuple[0] != null){
					AuditSchedule auditSchedule = (AuditSchedule) tuple[0];
					
					reportMISDto.setLegalEntityCode(legalEntityCode);
					reportMISDto.setAuditTypeCode(String.valueOf(tuple[1]));
					reportMISDto.setAuditId(auditSchedule.getAuditId());
					reportMISDto.setPlanId(auditSchedule.getPlanId()+"-"+auditSchedule.getAuditId()); 
					reportMISDto.setActualStartDate(auditSchedule.getActualStartDate());
					reportMISDto.setActualEndDate(auditSchedule.getReportDate()); // instead of actualEndDate, reportDate is Actual end date
					reportMISDto.setRespondedDate(auditSchedule.getRespondedDate());					
					reportMISDto.setReviewedDate(auditSchedule.getReviewedDate());
					reportMISDto.setIrFinalisationDate(auditSchedule.getActualEndDate()); //Instead of IrFinalisationDate, actualEndDate is Finalization date
					reportMISDto.setReportDispatchDate(auditSchedule.getReportDispatchDate());					
					reportMISDto.setIrSubmissionDate(auditSchedule.getIrSubmissionDate());
					reportMISDto.setReportReviewerStartDate(auditSchedule.getReportReviewerStartDate());
					reportMISDto.setFinancialYear(auditSchedule.getFinancialYear());
					 
					reportMISDto.setUnitCode(auditSchedule.getUnitId()); 
					if(tuple[2] != null){
						reportMISDto.setUnitName(tuple[2].toString()); 
					}
					if(tuple[3] != null){
						reportMISDto.setParentUnitCode(tuple[3].toString().split("-")[0]); 
						reportMISDto.setParentUnitName(tuple[3].toString().split("-")[1]); 
					}
					reportMISDto.setZoneCode(String.valueOf(tuple[4]));
					reportMISDto.setZoneName(String.valueOf(tuple[5]));
					 
					
					if(tuple[6] != null){
						reportMISDto.setAuditStatus(tuple[6].toString());
					}
					if(tuple[7] != null){
						reportMISDto.setReportStatus(tuple[7].toString());
					} 
					
					if(tuple[8] != null){
						reportMISDto.setMembersId(tuple[8].toString());
					}
					if(tuple[9] != null){
						reportMISDto.setLeadId(tuple[9].toString());
					}
					if(tuple[10] != null){
						reportMISDto.setReportReviewerAll(tuple[10].toString());
					}
					
					if(tuple[11] != null){						
						reportMISDto.setFinalisingOfficer(tuple[11].toString().split("@#@")[0]);
						if(tuple[11].toString().split("@#@").length>1){
							reportMISDto.setFinalisingOfficerDesignation(tuple[11].toString().split("@#@")[1]);
						}
					} 
					
					if(tuple[12] != null){						
						reportMISDto.setCompliedChecklistCount(tuple[12].toString());
					} 
					
					if(tuple[13] != null){						
						reportMISDto.setNonCompliedChecklistCount(tuple[13].toString());
					}
					
					if(tuple[14] != null){						
						reportMISDto.setNonCompliedMajorChecklistCount(tuple[14].toString());
					}
					
					if(tuple[15] != null){						
						reportMISDto.setNonCompliedMinorChecklistCount(tuple[15].toString());
					}
					
					if(tuple[16] != null){						
						reportMISDto.setNaChecklistCount(tuple[16].toString());
					}
					if(tuple[17] != null){						
						reportMISDto.setNonCompliedPersistenceChecklistCount(tuple[17].toString());
					} 	 					
				}		
				return reportMISDto;			
			}
			
			@Override
			public List transformList(List collection) { 
				return collection;
			}
		});
		
		List<ReportMISDto> listReportMISDto = hqlQuery.list();		
		return ((listReportMISDto==null || listReportMISDto.size()==0)?new ArrayList<>():listReportMISDto);		
	}
	
	@SuppressWarnings("deprecation")
	@Override
	public List<ReportMISDto> complianceStatusMISReport(Integer legalEntityCode, String auditTypeCode, LocalDate financialYear, List<LocalDate> financialYearList, 
			List<String> unitList){
		
		List<AuditSchedule> auditList = getAuditListBy(legalEntityCode, auditTypeCode, unitList, financialYear, financialYearList);
 		if(auditList == null || auditList.size() == 0){			
			return 	new ArrayList<>(); 
		}
 		
		List<String> auditIdList = auditList.stream().map(e -> e.getAuditId()).collect(Collectors.toList());		
		Map<String, AuditSchedule> auditIdMap = auditList.stream().collect(Collectors.toMap(AuditSchedule::getAuditId, value -> value));
		
		StringBuilder strQry =  new StringBuilder();
		strQry.append(" SELECT sch.audit_id,   "				
				+ " (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,"  //1
				+ " (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 Concat(e1.unit_code, '-', e1.unit_name) FROM  maint_entity e INNER JOIN   maint_entity e1   ON  e.legal_entity_code = sch.legal_entity_code " 
						+ " AND e.unit_code = sch.unit_id   AND e1.unit_code = e.parent_unit_code )  AS parentUnitCodeName,"		//3		
				
				+ "  '' as zoneCode, "
				+ " (SELECT m.zone_name FROM maint_entity e, maint_zone_mapping m WHERE  "
						+ " (CASE WHEN e.level_code IN ('3') THEN e.parent_unit_code ELSE e.unit_code END) = m.unit_code "
						+ " AND e.unit_code=sch.unit_id) AS zoneName,"   // zone.zoneCode,  zone.zoneName,	//5

				+ " (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, "  //6
				+ " (Select concat(sch.review_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.review_status) as reviewStatus, "    //7
 	
				+ " (Select  concat(u.user_id,'-', ifnull(u.first_name,''), ' ', ifnull(u.middle_name,''),' ', ifnull(u.last_name,'') ) "
						+ "	FROM  user u WHERE u.user_id = schStatusHstScrutInit.assignee_id  AND u.legal_entity_code = sch.legal_entity_code ) AS  scrutinyOfficers, " //8		
				+ " schStatusHstScrutInit.assignee_designation, sch.review_initiated_date,  " 	//9,10
 	 
				+ " (Select concat(u.user_id,'-', ifnull(u.first_name,''), ' ', ifnull(u.middle_name,''),' ', ifnull(u.last_name,''), '@#@',  ifnull(schStatusHst.maker_designation,''), '@#@ep') "
						+ "	FROM audit_schedule_stages_status_hst schStatusHst INNER JOIN user u ON u.user_id = schStatusHst.maker_id  AND u.legal_entity_code = schStatusHst.legal_entity_code "
						+ " WHERE  sch.legal_entity_code  = schStatusHst.legal_entity_code  AND sch.audit_type_code = schStatusHst.audit_type_code  AND sch.audit_id = schStatusHst.audit_id "						
						+ " AND schStatusHst.audit_status = '"+BankAuditConstant.AUDIT_STATUS_AW+"'"
						+ " AND schStatusHst.report_status = '"+BankAuditConstant.REPORT_STATUS_IR_DISPATCH+"'  "
						+ " AND schStatusHst.respond_status =  '"+BankAuditConstant.RESPOND_STATUS_RS+"' "
						+ " AND schStatusHst.review_status = '"+BankAuditConstant.REVIEW_STATUS_WS+"' "
						+ " AND schStatusHst.action_type  = '"+BankAuditConstant.ACTION_TYPE_SCRUTINY+"'"
						+ " AND schStatusHst.code =  '"+BankAuditConstant.REVIEW_STATUS_WS+"' "
						+ " AND schStatusHst.stages_status =  '"+BankAuditConstant.REVIEW_STATUS_WS+"' ) AS leadScrutiniserDetails,  "	//11
				
				+ " schStatus.accepted_on_scrutiny_submit AS accepted, schStatus.further_compliance_on_scrutiny_submit AS fc, schStatus.carry_forward_on_scrutiny_submit AS carryForward, " // 12 accp, 13Fc, 14 carr fwd,
				+ " schStatus.non_compliance_observations, schStatus.scrutiny_accepted_as_on, schStatus.scrutiny_carry_forward_as_on, schStatus.scrutiny_further_compliance_as_on," //15, 16, 17,18
				+ " schStatus.pending_with_scrutiniser, schStatus.pending_with_bank, " // 19,20
				/*+ " (Select  count(obs.observation_id)  FROM audit_observation obs  WHERE  sch.legal_entity_code  = obs.legal_entity_code  AND sch.audit_type_code = obs.audit_type_code "
						+ " AND sch.audit_id = obs.audit_id  ) AS totalObs ,  "	*/	 //15 No of observation
								
				/*+ " (Select  count(obs.observation_id)  FROM audit_observation obs  WHERE  sch.legal_entity_code  = obs.legal_entity_code  AND sch.audit_type_code = obs.audit_type_code  AND sch.audit_id = obs.audit_id " 
						+ " AND IFNULL(obs.compliance_category, '') = :nonCompliance AND IFNULL(obs.review_status, '') = '"+BankAuditConstant.STATUS_AUTH+"' "
						+ " AND IFNULL(obs.review_status_attribute, '') = '"+BankAuditConstant.REVIEW_STATUS_ATTRIB_ACCEPTED+"' "
						+ " AND IFNULL(sch.review_status, '') = '"+BankAuditConstant.REVIEW_STATUS_WS+"' ) AS scrutinyAcceptedAsOn , " *///16
				
				/*+ " (Select  count(obs.observation_id)  FROM audit_observation obs  WHERE  sch.legal_entity_code  = obs.legal_entity_code  AND sch.audit_type_code = obs.audit_type_code  AND sch.audit_id = obs.audit_id " 
						+ " AND IFNULL(obs.compliance_category, '') = :nonCompliance AND IFNULL(obs.review_status, '') = '"+BankAuditConstant.STATUS_AUTH+"'  "
						+ " AND IFNULL(obs.review_status_attribute, '') = '"+BankAuditConstant.REVIEW_STATUS_ATTRIB_CARRY_FORWARD+"' "
						+ " AND IFNULL(sch.review_status, '') = '"+BankAuditConstant.REVIEW_STATUS_WS+"'  ) AS scrutinyCarryFwdAsOn , " *///17
			 	
				/*+ " (Select  count(obs.observation_id)  FROM audit_observation obs  WHERE  sch.legal_entity_code  = obs.legal_entity_code  AND sch.audit_type_code = obs.audit_type_code  AND sch.audit_id = obs.audit_id " 
						+ " AND IFNULL(obs.compliance_category, '') = :nonCompliance AND IFNULL(obs.review_status, '') = '"+BankAuditConstant.STATUS_AUTH+"'  "
						+ " AND IFNULL(obs.review_status_attribute, '') = '"+BankAuditConstant.REVIEW_STATUS_ATTRIB_FURTHER_COMPLIANCE+"' "
						+ " AND IFNULL(sch.review_status, '') = '"+BankAuditConstant.REVIEW_STATUS_WS+"'  ) AS scrutinyFCAsOn,   "*/ //18 
							 
				/*+ " (SELECT count(obs.observation_id)  FROM audit_observation obs  WHERE  sch.legal_entity_code  = obs.legal_entity_code  AND sch.audit_type_code = obs.audit_type_code  AND sch.audit_id = obs.audit_id " 
						+ " AND IFNULL(obs.compliance_category, '') = :nonCompliance " 
						+ " AND IFNULL(sch.respond_status, '' ) =  '"+BankAuditConstant.RESPOND_STATUS_RS+"'"
						+ " AND  IFNULL(obs.response_status, '') = '"+BankAuditConstant.STATUS_AUTH+"' "
						+ " AND  IFNULL(obs.review_status_attribute, '') = '"+BankAuditConstant.REVIEW_STATUS_ATTRIB_FURTHER_COMPLIANCE+"' "
						+ " AND (obs.review_status = '"+BankAuditConstant.STATUS_PENDING_AUTH+"' OR  obs.review_status = '"+BankAuditConstant.STATUS_AUTH+"'  ) "
						+ " ) AS pendingWithScrutiniser,  "*/ //19						
						 	
				/*+ " (SELECT count(obs.observation_id)  FROM audit_observation obs  WHERE  sch.legal_entity_code  = obs.legal_entity_code  AND sch.audit_type_code = obs.audit_type_code  AND sch.audit_id = obs.audit_id " 
						+ " AND IFNULL(obs.compliance_category, '') = :nonCompliance " 
						+ " AND IFNULL(sch.review_status, '') = '"+BankAuditConstant.REVIEW_STATUS_WS+"' AND IFNULL(obs.response_status, '') <> '"+BankAuditConstant.STATUS_AUTH+"'"
						+ " AND IFNULL(obs.review_status_attribute, '') = '"+BankAuditConstant.REVIEW_STATUS_ATTRIB_FURTHER_COMPLIANCE+"' "
						+ " AND IFNULL(obs.review_status, '') = '"+BankAuditConstant.STATUS_AUTH+"' )   AS pendingWithBank,  " */	//20
						
				+ " scrutReviewerInfo.maker_timestamp as scrutinyCompletedDate "  //21		
				 
				+ " FROM  audit_schedule sch "				
				+ " LEFT JOIN (Select schStatusHstScrutInit.*  "											
						+ " FROM  audit_schedule_stages_status_hst schStatusHstScrutInit "						
						+ " INNER JOIN (SELECT legal_entity_code, audit_id, audit_type_code, MIN(id) AS id FROM audit_schedule_stages_status_hst "
										+ "	WHERE audit_id IN (:auditIdList) AND "
										+ " audit_status IN ('"+BankAuditConstant.AUDIT_STATUS_AR+"', '"+BankAuditConstant.AUDIT_STATUS_AS+"') "
										+ " AND report_status = '"+BankAuditConstant.REPORT_STATUS_IR_DISPATCH+"' "
										+ " AND respond_status =  '"+BankAuditConstant.RESPOND_STATUS_RS+"' "
										+ " AND review_status = '"+BankAuditConstant.REVIEW_STATUS_WI+"' "
										+ " AND action_type = '"+BankAuditConstant.ACTION_TYPE_SCRUTINY+"' "
										+ " AND code =  '"+BankAuditConstant.REVIEW_STATUS_WI+"' "
										+ " AND stages_status =  '"+BankAuditConstant.REVIEW_STATUS_WI+"'"
										+ "	GROUP BY legal_entity_code, audit_id, audit_type_code ) AS firstReviewerInfo  "						
						+ " ON schStatusHstScrutInit.id  = firstReviewerInfo.id  AND schStatusHstScrutInit.audit_id  = firstReviewerInfo.audit_id  AND schStatusHstScrutInit.legal_entity_code  = firstReviewerInfo.legal_entity_code "
						+ " AND schStatusHstScrutInit.audit_type_code  = firstReviewerInfo.audit_type_code "
						+ " ) AS schStatusHstScrutInit ON sch.legal_entity_code  = schStatusHstScrutInit.legal_entity_code AND sch.audit_type_code = schStatusHstScrutInit.audit_type_code  AND sch.audit_id = schStatusHstScrutInit.audit_id   "
				
				+ " LEFT JOIN (Select scrutReviewerInfo.*  "											
						+ " FROM  audit_schedule_stages_status_hst scrutReviewerInfo "						
						+ " INNER JOIN (SELECT legal_entity_code, audit_id, audit_type_code, MAX(id) AS id FROM audit_schedule_stages_status_hst "
										+ "	WHERE audit_id IN (:auditIdList) AND "
										+ " audit_status = '"+BankAuditConstant.AUDIT_STATUS_AR+"' "
										+ " AND report_status = '"+BankAuditConstant.REPORT_STATUS_IR_DISPATCH+"' "
										+ " AND respond_status =  '"+BankAuditConstant.RESPOND_STATUS_RS+"' "
										+ " AND review_status = '"+BankAuditConstant.REVIEW_STATUS_WI+"' "
										+ " AND action_type = '"+BankAuditConstant.ACTION_TYPE_SCRUTINY+"' "
										+ " AND code =  '"+BankAuditConstant.REVIEW_STATUS_WI+"' "
										+ " AND stages_status =  '"+BankAuditConstant.REVIEW_STATUS_WI+"' " 
										+ "	GROUP BY legal_entity_code, audit_id, audit_type_code "
										+ " HAVING COUNT(id)>1 ) AS reviewerInfo  "						
						+ " ON scrutReviewerInfo.id  = reviewerInfo.id  AND scrutReviewerInfo.audit_id  = reviewerInfo.audit_id  AND scrutReviewerInfo.legal_entity_code  = reviewerInfo.legal_entity_code "
						+ " AND scrutReviewerInfo.audit_type_code  = reviewerInfo.audit_type_code "
						+ " ) AS scrutReviewerInfo ON sch.legal_entity_code  = scrutReviewerInfo.legal_entity_code AND sch.audit_type_code = scrutReviewerInfo.audit_type_code  AND sch.audit_id = scrutReviewerInfo.audit_id   "		
						
				+ " LEFT JOIN audit_schedule_status schStatus ON sch.legal_entity_code  = schStatus.legal_entity_code "
				+ " AND sch.audit_type_code = schStatus.audit_type_code  AND sch.audit_id = schStatus.audit_id  "
				+ " WHERE sch.audit_id IN (:auditIdList) ");
		
		Session session=getSession();
	    session.setDefaultReadOnly(true);
		Query sqlQuery= session.createNativeQuery(strQry.toString())
    			.setParameterList("auditIdList", auditIdList);
    			/*.setParameter("nonCompliance", BankAuditConstant.COMPLIANCE_NO);*/
		
		sqlQuery.setResultTransformer(new ResultTransformer() {			
			@Override
			public Object transformTuple(Object[] tuple, String[] aliases) {
				ReportMISDto  reportMISDto = new ReportMISDto();
				if(tuple[0] != null){
					AuditSchedule auditSchedule = auditIdMap.get(tuple[0].toString());
					
					reportMISDto.setLegalEntityCode(legalEntityCode);
					reportMISDto.setAuditTypeCode(String.valueOf(tuple[1]));
					reportMISDto.setAuditId(auditSchedule.getAuditId());
					reportMISDto.setPlanId(auditSchedule.getPlanId()+"-"+auditSchedule.getAuditId()); 
					reportMISDto.setActualStartDate(auditSchedule.getActualStartDate()); 
					reportMISDto.setActualEndDate(auditSchedule.getReportDate()); // instead of actualEndDate, reportDate is Actual end date
					reportMISDto.setRespondedDate(auditSchedule.getRespondedDate());					
					reportMISDto.setReviewedDate(auditSchedule.getReviewedDate()); 
					reportMISDto.setIrFinalisationDate(auditSchedule.getActualEndDate()); //Instead of IrFinalisationDate, actualEndDate is Finalization date
					reportMISDto.setReportDispatchDate(auditSchedule.getReportDispatchDate());		
					reportMISDto.setComplianceDueDate(BankAuditUtil.addDaysToDate(auditSchedule.getReportDispatchDate(), 60));					
					reportMISDto.setIrSubmissionDate(auditSchedule.getIrSubmissionDate());
					reportMISDto.setReportReviewerStartDate(auditSchedule.getReportReviewerStartDate());					
					reportMISDto.setFinancialYear(auditSchedule.getFinancialYear());
					 
					reportMISDto.setUnitCode(auditSchedule.getUnitId()); 
					if(tuple[2] != null){
						reportMISDto.setUnitName(tuple[2].toString()); 
					}
					if(tuple[3] != null){
						reportMISDto.setParentUnitCode(tuple[3].toString().split("-")[0]); 
						reportMISDto.setParentUnitName(tuple[3].toString().split("-")[1]); 
					}
					reportMISDto.setZoneCode(String.valueOf(tuple[4]));
					reportMISDto.setZoneName(String.valueOf(tuple[5]));
					 
					
					if(tuple[6] != null){
						reportMISDto.setRespondStatus(tuple[6].toString());
					}
					if(tuple[7] != null){
						reportMISDto.setReviewStatus(tuple[7].toString());
					} 
					 
					if(tuple[8] != null){
						reportMISDto.setReviewer(tuple[8].toString());
					}
					if(tuple[9] != null){
						reportMISDto.setReviewerDesignation(tuple[9].toString());
					}
					if(tuple[10] != null){
						reportMISDto.setReviewAssignedDate((Date)tuple[10]);
					} 
					if(tuple[11] != null){						
						reportMISDto.setLeadScrutinizer(tuple[11].toString().split("@#@")[0]);
						if(tuple[11].toString().split("@#@").length>1){
							reportMISDto.setLeadScrutinizerDesignation(tuple[11].toString().split("@#@")[1]);
						}
					} 					
					if(tuple[12] != null){
						reportMISDto.setAcceptedCountOnScrutinySubmit(tuple[12].toString());
					}
					if(tuple[13] != null){
						reportMISDto.setFurtherComplianceCountOnScrutinySubmit(tuple[13].toString());
					}					
					if(tuple[14] != null){
						reportMISDto.setCarryForwardCountOnScrutinySubmit(tuple[14].toString());
					}
					if(tuple[15] != null){
						reportMISDto.setObservationCount(tuple[15].toString());
					}	
					 					
					if(tuple[16] != null){
						reportMISDto.setAcceptedCountAsOn(tuple[16].toString());
					}
					if(tuple[17] != null){
						reportMISDto.setCarryForwardCountAsOn(tuple[17].toString());
						reportMISDto.setFurtherComplianceCountAsOn((Integer.parseInt(tuple[17].toString())+Integer.parseInt(tuple[18].toString()))+"");
					}				
					
					if(tuple[19] != null){
						reportMISDto.setPendingWithScrutinizer(tuple[19].toString());
					} 
					if(tuple[20] != null){
						reportMISDto.setPendingWithBank(tuple[20].toString());
					} 
					if(tuple[21] != null){
						reportMISDto.setReviewCompletionDate((Date)tuple[21]);
					} 
				}		
				return reportMISDto;			
			}
			
			@Override
			public List transformList(List collection) { 
				return collection;
			}
		}); 
		 
		List<ReportMISDto> listReportMISDto = sqlQuery.list();		
		return ((listReportMISDto==null || listReportMISDto.size()==0)?new ArrayList<>():listReportMISDto);		
 	}
	
	@Override
	public List<ReportMISDto>  supervisoryRatingMISReport(Integer legalEntityCode, String auditTypeCode, LocalDate financialYear, List<LocalDate> financialYearList, 
			List<String> unitList){
		StringBuilder strQry =  new StringBuilder();
		strQry.append(""
			 	+ " Select auditId, auditTypeCodeDesc, unitName, parentUnitCodeName, zoneCode, zoneName, SuprRatingStatus,"
			 	+ " categoryScore, ratingReviewer, designation, reviewedDate, riskPrcntg, suprRat, riskPrcntgRoHO, suprRatRoHo,"
			 	+ " ratingType, planId, unitId, financialYear, actualStartDate,  ratingParameterStatus   FROM ("				 
				+ " (SELECT sch.audit_id AS auditId,  "				
				+ " (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,"  //1
				+ " (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,  " //2
				+ " (SELECT Concat(e1.unit_code, '-', e1.unit_name) FROM  maint_entity e INNER JOIN   maint_entity e1   ON  e.legal_entity_code = sch.legal_entity_code " 
						+ " AND e.unit_code = sch.unit_id   AND e1.unit_code = e.parent_unit_code )  AS parentUnitCodeName,"		//3		
				
				+ "  'zoneCode' as zoneCode, " //4
				+ " (SELECT m.zone_name FROM maint_entity e, maint_zone_mapping m WHERE  "
						+ " (CASE WHEN e.level_code IN ('3') THEN e.parent_unit_code ELSE e.unit_code END) = m.unit_code "
						+ " AND e.unit_code=sch.unit_id) AS zoneName,"   // zone.zoneCode,  zone.zoneName,	//5 
						
				+ " (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='RATING_PARAM_STATUS' AND g.value = sch.rating_parameter_status) as SuprRatingStatus, " //6
				
				+ " (SELECT GROUP_CONCAT(score.category_code, '@:@', score.score_on_100,'@:@', score.score_on_100_aftr_overlay, '@:@ep') from audit_rating_parameter_category_score score WHERE sch.legal_entity_code  = score.legal_entity_code "
						+ " AND sch.audit_type_code = score.audit_type_code  AND sch.audit_id = score.audit_id ) AS categoryScore, " //7
				
				+ " (select concat(u.user_id,'-', ifnull(u.first_name,''), ' ', ifnull(u.middle_name,''),' ', ifnull(u.last_name,'')) FROM  user u WHERE u.user_id = rating.reviewer_id "
						+ " AND u.legal_entity_code = rating.legal_entity_code AND  sch.rating_parameter_status IN ('RPR','RPHU','RPS') ) AS ratingReviewer,  "	 //8
	 				 
				+ " schStatusHst.maker_designation AS designation,  rating.reviewed_date AS reviewedDate, " // 9, 10			
				+ " rating.supervisory_risk_prcntg AS riskPrcntg, rating.supervisory_rating AS suprRat, " //11, 12 after deflators 
				+ " rating.supervisory_ro_risk_prcntg AS riskPrcntgRoHO, rating.supervisory_ro_rating AS suprRatRoHo, " //13, 14 rating before verlay 
				
				+ " 'RO' AS ratingType, sch.plan_id AS planId, sch.unit_id AS unitId, sch.financial_year AS financialYear,  " //15, 16, 17, 18			
				+ " sch.actual_start_date AS actualStartDate, "
				+ " sch.rating_parameter_status AS ratingParameterStatus  " //20
				  				
				+ " FROM  audit_schedule sch "
				+ " INNER JOIN audit_rating rating  ON  sch.legal_entity_code  = rating.legal_entity_code "
				+ " AND sch.audit_type_code = rating.audit_type_code  AND sch.audit_id = rating.audit_id "
				
				+ " LEFT JOIN audit_schedule_stages_status_hst schStatusHst  ON  sch.legal_entity_code  = schStatusHst.legal_entity_code "
				+ " AND sch.audit_type_code = schStatusHst.audit_type_code  AND sch.audit_id = schStatusHst.audit_id  AND schStatusHst.stages_status = 'RPR' "
				+ " AND schStatusHst.code = 'RPR' AND schStatusHst.action_type = '"+BankAuditConstant.ACTION_TYPE_SUPERVISORY_RATING+"' " 				 	 
				
				+ " WHERE sch.legal_entity_code = :legalEntityCode ");
				if(!BankAuditUtil.isEmptyString(auditTypeCode)){
					strQry.append(" AND sch.audit_type_code = :auditTypeCode ");
				}
			strQry.append(" AND sch.unit_id IN (:unitList) ");
			strQry.append(" AND (sch.actual_start_date BETWEEN '"+financialYearList.get(0)+"' AND '"+financialYearList.get(1)+"')  ");
			strQry.append( ")");
				 
		strQry.append(	" UNION ALL  " );
				
			strQry.append(" (SELECT sch.audit_id AS auditId,  "				
				+ " (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,"  //1
				+ " (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,  " //2
				+ " (SELECT Concat(e1.unit_code, '-', e1.unit_name) FROM  maint_entity e INNER JOIN   maint_entity e1   ON  e.legal_entity_code = sch.legal_entity_code " 
						+ " AND e.unit_code = sch.unit_id   AND e1.unit_code = e.parent_unit_code )  AS parentUnitCodeName,"		//3		
				
				+ "  'zoneCode' as zoneCode, " //4
				+ " (SELECT m.zone_name FROM maint_entity e, maint_zone_mapping m WHERE  "
						+ " (CASE WHEN e.level_code IN ('3') THEN e.parent_unit_code ELSE e.unit_code END) = m.unit_code "
						+ " AND e.unit_code=sch.unit_id) AS zoneName,"   // zone.zoneCode,  zone.zoneName,	//5 						
		
				+ " (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='RATING_PARAM_STATUS' AND g.value = sch.rating_parameter_status) as SuprRatingStatus, " //6
				
				+ " (SELECT GROUP_CONCAT(score.category_code, '@:@', score.score_on_100,'@:@', score.score_on_100_aftr_overlay, '@:@ep') from audit_rating_parameter_category_score score WHERE sch.legal_entity_code  = score.legal_entity_code "
						+ " AND sch.audit_type_code = score.audit_type_code  AND sch.audit_id = score.audit_id ) AS categoryScore, " //7
				
				+ " (select concat(u.user_id,'-', ifnull(u.first_name,''), ' ', ifnull(u.middle_name,''),' ', ifnull(u.last_name,'')) FROM  user u WHERE u.user_id = rating.ho_reviewer_id "
						+ " AND u.legal_entity_code = rating.legal_entity_code AND  sch.rating_parameter_status IN ('RPR','RPHU','RPS') ) AS ratingReviewer,  "	 //8
						 
				+ " schStatusHst.maker_designation AS designation,  rating.ho_reviewed_date AS reviewedDate, " // 9, 10			
				+ " rating.supervisory_risk_prcntg AS riskPrcntg, rating.supervisory_rating AS suprRat, " //11, 12 after deflators 
				+ " rating.supervisory_ho_risk_prcntg  AS riskPrcntgRoHO, rating.supervisory_ho_rating AS suprRatRoHo, " //13, 14 rating before verlay 
				 				
				+ " 'HO' AS ratingType, sch.plan_id AS planId, sch.unit_id AS unitId, sch.financial_year AS financialYear,  " //15, 16, 17, 18			
				+ " sch.actual_start_date AS actualStartDate,"
				+ " sch.rating_parameter_status  AS ratingParameterStatus  " //20
				
				+ " FROM  audit_schedule sch "
				+ " INNER JOIN audit_rating rating  ON  sch.legal_entity_code  = rating.legal_entity_code "
				+ " AND sch.audit_type_code = rating.audit_type_code  AND sch.audit_id = rating.audit_id "
				
				+ " LEFT JOIN audit_schedule_stages_status_hst schStatusHst  ON  sch.legal_entity_code  = schStatusHst.legal_entity_code "
				+ " AND sch.audit_type_code = schStatusHst.audit_type_code  AND sch.audit_id = schStatusHst.audit_id AND schStatusHst.stages_status = 'RPS'  "
				+ " AND schStatusHst.code = 'RPS' AND schStatusHst.action_type = '"+BankAuditConstant.ACTION_TYPE_SUPERVISORY_RATING+"' " 				 	 
				
				+ " WHERE sch.legal_entity_code = :legalEntityCode ");
				if(!BankAuditUtil.isEmptyString(auditTypeCode)){
					strQry.append(" AND sch.audit_type_code = :auditTypeCode ");
				}
				strQry.append(" AND sch.unit_id IN (:unitList) ");
				strQry.append(" AND (sch.actual_start_date BETWEEN '"+financialYearList.get(0)+"' AND '"+financialYearList.get(1)+"')  ");
				strQry.append( ")");				 
		strQry.append( ") as suprRatingTmp ORDER BY actualStartDate, auditId ");
			
		

		Session session=getSession();
	    session.setDefaultReadOnly(true);
	    
	    Query hqlQuery= session.createNativeQuery(strQry.toString())
	    		.setParameter("unitList", unitList)
    			.setParameter("legalEntityCode", legalEntityCode);
		if(!BankAuditUtil.isEmptyString(auditTypeCode)){
			hqlQuery.setParameter("auditTypeCode", auditTypeCode);
		}
		
		hqlQuery.setResultTransformer(new ResultTransformer() {			
			@Override
			public Object transformTuple(Object[] tuple, String[] aliases) {
				ReportMISDto  reportMISDto = new ReportMISDto();
				if(tuple[0] != null){ 
					
					reportMISDto.setLegalEntityCode(legalEntityCode);
					reportMISDto.setAuditTypeCode(String.valueOf(tuple[1]));					
					reportMISDto.setAuditId(tuple[0].toString());  					
					
					if(tuple[2] != null){
						reportMISDto.setUnitName(tuple[2].toString()); 
					}
					if(tuple[3] != null){
						reportMISDto.setParentUnitCode(tuple[3].toString().split("-")[0]); 
						reportMISDto.setParentUnitName(tuple[3].toString().split("-")[1]); 
					}
					if(tuple[4] != null) {
						reportMISDto.setZoneCode(String.valueOf(tuple[4]));
					}
					if(tuple[5] != null) {
						reportMISDto.setZoneName(String.valueOf(tuple[5])); 
					}
					if(tuple[6] != null) {
						reportMISDto.setSuprRatingStatus(String.valueOf(tuple[6])); 
					}
					
					Boolean isAuth = (tuple[15] == null || tuple[20] ==null )?false:
						(("HO".equalsIgnoreCase(tuple[15].toString()) && "RPS".equalsIgnoreCase(tuple[20].toString()))?true:
							("RO".equalsIgnoreCase(tuple[15].toString()))?true:false);
					
					if(tuple[7] != null && isAuth){
						String [] ratingCategoryArr = tuple[7].toString().split(",");
						for (String category : ratingCategoryArr) {
							String [] categoryArr = category.split("@:@");
							if("CT001".equalsIgnoreCase(categoryArr[0])){
								reportMISDto.setSuprRatingCapitalAdequacyScore(String.format("%.2f", Double.parseDouble(categoryArr[1])));
							}else if("CT002".equalsIgnoreCase(categoryArr[0])){
								reportMISDto.setSuprRatingAssetQualityScore(String.format("%.2f", Double.parseDouble(categoryArr[1])));
							}else if("CT003".equalsIgnoreCase(categoryArr[0])){
								reportMISDto.setSuprRatingManagementScore(String.format("%.2f", Double.parseDouble(categoryArr[1])));
								reportMISDto.setSuprRatingManagementScoreOverlay(String.format("%.2f", Double.parseDouble(categoryArr[2])));
							}else if("CT004".equalsIgnoreCase(categoryArr[0])){
								reportMISDto.setSuprRatingEarningsScore(String.format("%.2f", Double.parseDouble(categoryArr[1])));
							}else if("CT005".equalsIgnoreCase(categoryArr[0])){
								reportMISDto.setSuprRatingLiquidityScore(String.format("%.2f", Double.parseDouble(categoryArr[1])));
							}else if("CT006".equalsIgnoreCase(categoryArr[0])){
								reportMISDto.setSuprRatingSystemsScore(String.format("%.2f", Double.parseDouble(categoryArr[1])));
							}else if("CT007".equalsIgnoreCase(categoryArr[0])){
								reportMISDto.setSuprRatingComplianceScore(String.format("%.2f", Double.parseDouble(categoryArr[1])));
							} 
						}						
					} 					 
					if(tuple[8] != null){
						reportMISDto.setSuprRatingReviewer(tuple[8].toString());
						reportMISDto.setSuprRatingFinalisingOfficer(tuple[8].toString());
						if(tuple[10] != null) reportMISDto.setSuprRatingFinalisationDate((Date) tuple[10]);
					}					
					if(tuple[9] != null) {
						reportMISDto.setSuprRatingFinalisingOfficerDesignation(String.valueOf(tuple[9])); 
					}					
					if(tuple[11] != null  && isAuth) {
						reportMISDto.setSuprRatingMarksAfterDeflators(String.valueOf(tuple[11])); 
					}
					if(tuple[12] != null && isAuth) {
						reportMISDto.setSuprRatingCategoryAfterDeflators(String.valueOf(tuple[12])); 
					}					
					if(tuple[13] != null && isAuth) {
						reportMISDto.setSuprRatingMarks(String.valueOf(tuple[13])); 
					}
					if(tuple[14] != null && isAuth) {
						reportMISDto.setSuprRatingCategory(String.valueOf(tuple[14])); 
					}					
					/*if(tuple[14] != null) {
						reportMISDto.setSuprRatingCategory(String.valueOf(tuple[14])); 
					}*/
					if(tuple[15] != null) {
						reportMISDto.setSuprRatingType(String.valueOf(tuple[15])); 
					}
					if(tuple[16] != null && tuple[0] != null) {
						reportMISDto.setPlanId(String.valueOf(tuple[16])+"-"+tuple[0].toString()); 
					}
					if(tuple[17] != null) {
						reportMISDto.setUnitCode(tuple[17].toString()); 
					}	 					
					if(tuple[18] != null){
						reportMISDto.setFinancialYear((Date)tuple[18]);
					}					 
				}		
				return reportMISDto;			
			}
			
			@Override
			public List transformList(List collection) { 
				return collection;
			}
		}); 
		
		List<ReportMISDto> listReportMISDto = hqlQuery.list();
		return ((listReportMISDto==null || listReportMISDto.size()==0)?new ArrayList<>():listReportMISDto);		
	}
	
	@Override
	public List<ReportMISDto> supervisoryRating3YearsMISReport(Integer legalEntityCode, String auditTypeCode, LocalDate financialYear, List<LocalDate> financialYearList, 
			List<String> unitList){
		
		List<AuditSchedule> auditList = getAuditListBy(legalEntityCode, auditTypeCode, unitList, financialYear, financialYearList);
 		if(auditList == null || auditList.size() == 0){	return new ArrayList<>(); } 		
		List<String> auditIdList = auditList.stream().map(e -> e.getAuditId()).collect(Collectors.toList());		 
		
		LocalDate financialYearStartDt = financialYearList.get(0);
		LocalDate financialYearEndDt = financialYearList.get(1);
		
		StringBuilder strQry =  new StringBuilder();
		strQry.append(" SELECT auditId, auditTypeCodeDesc, unitName, parentUnitCodeName, zoneCode, zoneName, referenceYear1, referenceYear2, referenceYear3,"
				+ " supervisoryRiskPrcntg1, supervisoryRoRating1, supervisoryRiskPrcntg2, supervisoryRoRating2, supervisoryRiskPrcntg3, supervisoryRoRating3, ratingType, unitId, auditTypeCodeDesc2, auditTypeCodeDesc3 "
				+ " FROM( "				
						+ " (SELECT sch.audit_id AS auditId,  "				
						+ " (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,"  //1
						+ " (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,  " //2
						+ " (SELECT Concat(e1.unit_code, '-', e1.unit_name) FROM  maint_entity e INNER JOIN   maint_entity e1   ON  e.legal_entity_code = sch.legal_entity_code " 
								+ " AND e.unit_code = sch.unit_id   AND e1.unit_code = e.parent_unit_code )  AS parentUnitCodeName,"		//3		
						
						+ "  'zoneCode' as zoneCode, " //4
						+ " (SELECT m.zone_name FROM maint_entity e, maint_zone_mapping m WHERE  "
								+ " (CASE WHEN e.level_code IN ('3') THEN e.parent_unit_code ELSE e.unit_code END) = m.unit_code "
								+ " AND e.unit_code=sch.unit_id) AS zoneName,"   // zone.zoneCode,  zone.zoneName,	//5
						
						+ " sch.financial_year AS referenceYear1, schPre.financial_year AS referenceYear2, schPrePre.financial_year AS referenceYear3, " 
						+ " rating.supervisory_ro_risk_prcntg AS supervisoryRiskPrcntg1, 		rating.supervisory_ro_rating AS supervisoryRoRating1, "
						+ " ratingPre.supervisory_ro_risk_prcntg AS supervisoryRiskPrcntg2, 		ratingPre.supervisory_ro_rating AS supervisoryRoRating2, "
						+ " ratingPrePre.supervisory_ro_risk_prcntg AS supervisoryRiskPrcntg3, 	ratingPrePre.supervisory_ro_rating AS supervisoryRoRating3, "

						+ " 'RO' as ratingType,  "						
						+ " sch.unit_id AS unitId, "
						
						+ " (SELECT concat(audit_type_code , '-' , audit_type_desc) FROM maint_audit_type_desc at  WHERE at.audit_type_code = schPre.audit_type_code "
								+ " AND at.legal_entity_code = schPre.legal_entity_code ) AS auditTypeCodeDesc2, "   
						+ " (SELECT concat(audit_type_code , '-' , audit_type_desc) FROM maint_audit_type_desc at  WHERE at.audit_type_code = schPrePre.audit_type_code "
								+ " AND at.legal_entity_code = schPrePre.legal_entity_code ) AS auditTypeCodeDesc3 "   
												
						+ " FROM  audit_schedule sch "
						+ " LEFT JOIN audit_rating rating ON sch.legal_entity_code  = rating.legal_entity_code AND sch.audit_type_code = rating.audit_type_code  AND sch.audit_id = rating.audit_id "
						
						+ " LEFT JOIN audit_schedule schPre ON sch.unit_id = schPre.unit_id AND sch.legal_entity_code  = schPre.legal_entity_code "
								+ " AND sch.prev_audit_id = schPre.audit_id "
								+ " AND (schPre.actual_start_date BETWEEN '"+financialYearStartDt.minusYears(1)+"' AND  '"+financialYearEndDt.minusYears(1)+"') "
						+ " LEFT JOIN audit_rating ratingPre ON schPre.legal_entity_code  = ratingPre.legal_entity_code AND schPre.audit_type_code = ratingPre.audit_type_code  AND schPre.audit_id = ratingPre.audit_id "
						
						+ " LEFT JOIN audit_schedule schPrePre ON sch.unit_id = schPrePre.unit_id AND sch.legal_entity_code  = schPrePre.legal_entity_code "
								+ " AND schPre.prev_audit_id = schPrePre.audit_id "
								+ " AND (schPrePre.actual_start_date BETWEEN '"+financialYearStartDt.minusYears(2)+"' AND  '"+financialYearEndDt.minusYears(2)+"') " 					
						+ " LEFT JOIN audit_rating ratingPrePre ON schPrePre.legal_entity_code  = ratingPrePre.legal_entity_code AND schPrePre.audit_type_code = ratingPrePre.audit_type_code  AND schPrePre.audit_id = ratingPrePre.audit_id "
						
						+ " WHERE sch.legal_entity_code=:legalEntityCode AND  sch.audit_id IN (:auditIdList) ) "
						
						+" UNION ALL "
					 
						+ " ( SELECT sch.audit_id AS auditId,  "				
						+ " (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,"  //1
						+ " (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,  " //2
						+ " (SELECT Concat(e1.unit_code, '-', e1.unit_name) FROM  maint_entity e INNER JOIN   maint_entity e1   ON  e.legal_entity_code = sch.legal_entity_code " 
								+ " AND e.unit_code = sch.unit_id   AND e1.unit_code = e.parent_unit_code )  AS parentUnitCodeName,"		//3		
						
						+ "  'zoneCode' as zoneCode, " //4
						+ " (SELECT m.zone_name FROM maint_entity e, maint_zone_mapping m WHERE  "
								+ " (CASE WHEN e.level_code IN ('3') THEN e.parent_unit_code ELSE e.unit_code END) = m.unit_code "
								+ " AND e.unit_code=sch.unit_id) AS zoneName,"   // zone.zoneCode,  zone.zoneName,	//5
						
						+ " sch.financial_year AS referenceYear1, schPre.financial_year AS referenceYear2, schPrePre.financial_year AS referenceYear3, " 
						+ " rating.supervisory_ho_risk_prcntg AS supervisoryRiskPrcntg1, 		rating.supervisory_ho_rating AS supervisoryRoRating1, "
						+ " ratingPre.supervisory_ho_risk_prcntg AS supervisoryRiskPrcntg2, 		ratingPre.supervisory_ho_rating AS supervisoryRoRating2, "
						+ " ratingPrePre.supervisory_ho_risk_prcntg AS supervisoryRiskPrcntg3, 	ratingPrePre.supervisory_ho_rating AS supervisoryRoRating3, "
 						
						+ " 'HO' as ratingType,  "						
						+ " sch.unit_id AS unitId, "
						
						+ " (SELECT concat(audit_type_code , '-' , audit_type_desc) FROM maint_audit_type_desc at  WHERE at.audit_type_code = schPre.audit_type_code "
								+ " AND at.legal_entity_code = schPre.legal_entity_code ) AS auditTypeCodeDesc2, "   
						+ " (SELECT concat(audit_type_code , '-' , audit_type_desc) FROM maint_audit_type_desc at  WHERE at.audit_type_code = schPrePre.audit_type_code "
								+ " AND at.legal_entity_code = schPrePre.legal_entity_code ) AS auditTypeCodeDesc3 "   
												
						+ " FROM  audit_schedule sch "
						+ " LEFT JOIN audit_rating rating ON sch.legal_entity_code  = rating.legal_entity_code AND sch.audit_type_code = rating.audit_type_code  AND sch.audit_id = rating.audit_id "
						
						+ " LEFT JOIN audit_schedule schPre ON sch.unit_id = schPre.unit_id AND sch.legal_entity_code  = schPre.legal_entity_code "
								+ " AND sch.prev_audit_id = schPre.audit_id "
								+ " AND (schPre.actual_start_date BETWEEN '"+financialYearStartDt.minusYears(1)+"' AND  '"+financialYearEndDt.minusYears(1)+"')"
						+ " INNER JOIN audit_rating ratingPre ON schPre.legal_entity_code  = ratingPre.legal_entity_code AND schPre.audit_type_code = ratingPre.audit_type_code  AND schPre.audit_id = ratingPre.audit_id "
						
						+ " LEFT JOIN audit_schedule schPrePre ON sch.unit_id = schPrePre.unit_id AND sch.legal_entity_code  = schPrePre.legal_entity_code "
								+ " AND schPre.prev_audit_id = schPrePre.audit_id "
								+ " AND (schPrePre.actual_start_date BETWEEN '"+financialYearStartDt.minusYears(2)+"' AND  '"+financialYearEndDt.minusYears(2)+"') " 					
						+ " INNER JOIN audit_rating ratingPrePre ON schPrePre.legal_entity_code  = ratingPrePre.legal_entity_code AND schPrePre.audit_type_code = ratingPrePre.audit_type_code  AND schPrePre.audit_id = ratingPrePre.audit_id "
						
						+ " WHERE sch.legal_entity_code=:legalEntityCode AND  sch.audit_id IN (:auditIdList) )"
				+ " ) AS compRating "				
				+ " ORDER BY referenceYear1, unitId  ");
		  
		Session session=getSession();
	    session.setDefaultReadOnly(true);
		Query sqlQuery= session.createNativeQuery(strQry.toString())
				.setParameter("legalEntityCode", legalEntityCode)
    			.setParameterList("auditIdList", auditIdList);
		sqlQuery.setResultTransformer(new ResultTransformer() {			
			@Override
			public Object transformTuple(Object[] tuple, String[] aliases) {
				ReportMISDto  reportMISDto = new ReportMISDto();
				if(tuple[0] != null){  
					
					reportMISDto.setLegalEntityCode(legalEntityCode);
					reportMISDto.setAuditTypeCode(String.valueOf(tuple[1]));  
					  
					if(tuple[2] != null){
						reportMISDto.setUnitName(tuple[2].toString()); 
					}
					if(tuple[6] != null){
						reportMISDto.setUnitCode(tuple[16].toString()); 
					}					
					if(tuple[3] != null){
						reportMISDto.setParentUnitCode(tuple[3].toString().split("-")[0]); 
						reportMISDto.setParentUnitName(tuple[3].toString().split("-")[1]); 
					}
					reportMISDto.setZoneCode(String.valueOf(tuple[4]));
					reportMISDto.setZoneName(String.valueOf(tuple[5]));	
					
					if(tuple[6] != null){
						reportMISDto.setReferenceYear1((Date) tuple[6] );
					}
					if(tuple[7] != null){
						reportMISDto.setReferenceYear2((Date) tuple[7]);
					}
					if(tuple[8] != null){
						reportMISDto.setReferenceYear3((Date)tuple[8]);
					}					
					if(tuple[9] != null && !BankAuditUtil.isEmptyString(tuple[9].toString())){
						reportMISDto.setSuprRatingMarks1(String.format("%.2f", Double.parseDouble(tuple[9].toString())));
					}
					if(tuple[10] != null){
						reportMISDto.setSuprRatingCategory1(tuple[10].toString());
					}					
					if(tuple[11] != null && !BankAuditUtil.isEmptyString(tuple[11].toString())){
						reportMISDto.setSuprRatingMarks2(String.format("%.2f", Double.parseDouble(tuple[11].toString())));
					}
					if(tuple[12] != null){
						reportMISDto.setSuprRatingCategory2(tuple[12].toString());
					}					
					if(tuple[13] != null && !BankAuditUtil.isEmptyString(tuple[13].toString())){
						reportMISDto.setSuprRatingMarks3(String.format("%.2f", Double.parseDouble(tuple[13].toString())));
					}
					if(tuple[14] != null){
						reportMISDto.setSuprRatingCategory3(tuple[14].toString());
					}
					if(tuple[15] != null){
						reportMISDto.setSuprRatingType(tuple[15].toString());
					} 					
					if(tuple[17] != null){
						reportMISDto.setAuditTypeCode2(tuple[17].toString());
					} 
					if(tuple[18] != null){
						reportMISDto.setAuditTypeCode3(tuple[18].toString());
					}  				 					 
				}		
				return reportMISDto;			
			}
			
			@Override
			public List transformList(List collection) { 
				return collection;
			}
		}); 
		
		List<ReportMISDto> listReportMISDto = sqlQuery.list();
		return ((listReportMISDto==null || listReportMISDto.size()==0)?new ArrayList<>():listReportMISDto);		
	}
	
	@Override
	public List<ReportMISDto>  lettersMISReport(Integer legalEntityCode, String auditTypeCode, LocalDate financialYear, List<LocalDate> financialYearList, 
			List<String> unitList){

		List<AuditSchedule> auditList = getAuditListBy(legalEntityCode, auditTypeCode, unitList, financialYear, financialYearList);
 		if(auditList == null || auditList.size() == 0){			
 			return new ArrayList<>(); 
		}
 		
		List<String> auditIdList = auditList.stream().map(e -> e.getAuditId()).collect(Collectors.toList());		
		Map<String, AuditSchedule> auditIdMap = auditList.stream().collect(Collectors.toMap(AuditSchedule::getAuditId, value -> value));
		
		StringBuilder strQry =  new StringBuilder();
		strQry.append(" SELECT sch.audit_id,   "				
				+ " (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,"  //1
				+ " (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 Concat(e1.unit_code, '-', e1.unit_name) FROM  maint_entity e INNER JOIN   maint_entity e1   ON  e.legal_entity_code = sch.legal_entity_code " 
						+ " AND e.unit_code = sch.unit_id   AND e1.unit_code = e.parent_unit_code )  AS parentUnitCodeName,"		//3		
				
				+ "  '' as zoneCode, "
				+ " (SELECT m.zone_name FROM maint_entity e, maint_zone_mapping m WHERE  "
						+ " (CASE WHEN e.level_code IN ('3') THEN e.parent_unit_code ELSE e.unit_code END) = m.unit_code "
						+ " AND e.unit_code=sch.unit_id) AS zoneName,"   // zone.zoneCode,  zone.zoneName,	//5
				
				+ " g.description,  ltrs.ltrReviewDt, "  //6, 7
				
				+ " IFNULL(ltrs.ltrStatus, '') ltrStatus, " //8
				 
				+ " (SELECT  concat(u.user_id,'-', ifnull(u.first_name,''), ' ', ifnull(u.middle_name,''),' ', ifnull(u.last_name,'') ) "
						+ "	FROM  user u WHERE u.user_id = ltrs.ltrReviewer  AND u.legal_entity_code = sch.legal_entity_code ) AS  ltrReviewerName, " //9
				
				+ " (SELECT rating.composite_rating_aftr_overlay from audit_rating rating WHERE  sch.legal_entity_code = rating.legal_entity_code "
						+ " AND  sch.audit_type_code = rating.audit_type_code AND sch.audit_id  = rating.audit_id AND ltrs.ltrStatus IS NOT NULL ) AS compRatingCategory,  " //10		
				
				+ " (SELECT statusHst.maker_designation FROM audit_schedule_stages_status_hst statusHst WHERE  sch.legal_entity_code = statusHst.legal_entity_code "
						+ " AND  sch.audit_type_code = statusHst.audit_type_code AND sch.audit_id  = statusHst.audit_id "
						+ " AND  statusHst.code = g.value AND statusHst.action_type =  '"+BankAuditConstant.ACTION_TYPE_LETTER+"'"
						+ " AND ltrs.ltrReviewer = statusHst.maker_id AND (statusHst.stages_status IS NULL OR statusHst.stages_status = '"+BankAuditConstant.STATUS_AUTH+"')) as designation, "	//11	
				+ " g.value " //12		
				
				+ " FROM  audit_schedule sch "
				+ " LEFT JOIN  general_parameter g  "
					+ " ON g.legal_entity_code = sch.legal_entity_code AND g.key_1 = 'LETTER' AND (g.key_2 like '%AUDIT' or g.key_2 like 'AUDIT_BEFORE')"
					+ " AND g.value NOT IN (:letterCodeNotRequired) "
				+ " LEFT JOIN ("
						+ " SELECT audit_id, audit_type_code, letter_code, null AS ltrStatus, null AS ltrReviewer, null AS ltrReviewDt, legal_entity_code  FROM audit_letter_user_inputs_wrk "
						+ " WHERE legal_entity_code=:legalEntityCode AND audit_id IN (:auditIdList)  "
						+ "	UNION "
						+ " SELECT audit_id, audit_type_code, letter_code, 'Authorized' AS ltrStatus, reviewer AS ltrReviewer, checker_timestamp AS ltrReviewDt, legal_entity_code  FROM audit_letter_user_inputs "
						+ " WHERE legal_entity_code=:legalEntityCode AND audit_id IN (:auditIdList) "
				+ "	) ltrs "
				+ " ON sch.legal_entity_code = ltrs.legal_entity_code "
				+ " AND sch.audit_id  = ltrs.audit_id "
				+ " AND sch.audit_type_code = ltrs.audit_type_code "
				+ " AND g.value = ltrs.letter_code " 
				
				/*+ " INNER JOIN "				
					+ " (SELECT ltrTab.audit_id AS auditId, ltrTab.audit_type_code AS auditTypeCode , ltrTab.legal_entity_code as legalEntityCode,"
					+ " g.value AS lrtCode, g.description AS ltrName, ltrTab.status AS ltrStatus,  ltrTab.reviewer AS ltrReviewer, ltrTab.checker_timestamp AS ltrReviewDt "				 
					+ " FROM general_parameter g LEFT JOIN "
					+ " (SELECT audit_id, audit_type_code, letter_code, null AS status, null AS reviewer, null AS checker_timestamp, legal_entity_code  FROM audit_letter_user_inputs_wrk "
						+ " WHERE legal_entity_code=:legalEntityCode AND audit_id IN (:auditIdList)  "
					+ "	UNION "
					+ " SELECT audit_id, audit_type_code, letter_code, 'Authorized' AS status, reviewer, checker_timestamp, legal_entity_code  FROM audit_letter_user_inputs "
						+ " WHERE legal_entity_code=:legalEntityCode AND audit_id IN (:auditIdList) "
					+ "	) ltrTab  "
					+ " ON ltrTab.letter_code=g.value AND ltrTab.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) ) ltrs ON sch.legal_entity_code  = ltrs.legalEntityCode AND  sch.audit_id  = ltrs.auditId "
					+ " AND sch.audit_type_code  = ltrs.auditTypeCode  "*/
					 		 
				+ " WHERE sch.legal_entity_code=:legalEntityCode AND  sch.audit_id IN (:auditIdList) "
				+ " ORDER BY sch.actual_start_date, sch.audit_id  ");
		
		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});
		
		Session session=getSession();
	    session.setDefaultReadOnly(true);
		Query sqlQuery= session.createNativeQuery(strQry.toString())
				.setParameter("legalEntityCode", legalEntityCode)
    			.setParameterList("auditIdList", auditIdList)
    			.setParameterList("letterCodeNotRequired", letterCodeNotRequired);
		sqlQuery.setResultTransformer(new ResultTransformer() {			
			@Override
			public Object transformTuple(Object[] tuple, String[] aliases) {
				ReportMISDto  reportMISDto = new ReportMISDto();
				if(tuple[0] != null){
					AuditSchedule auditSchedule = auditIdMap.get(tuple[0].toString());
					
					reportMISDto.setLegalEntityCode(legalEntityCode);
					reportMISDto.setAuditTypeCode(String.valueOf(tuple[1]));
					reportMISDto.setAuditId(auditSchedule.getAuditId());
					reportMISDto.setPlanId(auditSchedule.getPlanId()+"-"+auditSchedule.getAuditId());  
					reportMISDto.setFinancialYear(auditSchedule.getFinancialYear());
					 
					reportMISDto.setUnitCode(auditSchedule.getUnitId()); 
					if(tuple[2] != null){
						reportMISDto.setUnitName(tuple[2].toString()); 
					}
					if(tuple[3] != null){
						reportMISDto.setParentUnitCode(tuple[3].toString().split("-")[0]); 
						reportMISDto.setParentUnitName(tuple[3].toString().split("-")[1]); 
					}
					reportMISDto.setZoneCode(String.valueOf(tuple[4]));
					reportMISDto.setZoneName(String.valueOf(tuple[5]));					 
					
					if(tuple[6] != null){
						reportMISDto.setLetterName(tuple[6].toString());
					}
					if(tuple[7] != null){
						reportMISDto.setLetterFinalisationDate((Date) tuple[7]);
					}
					if(tuple[8] != null){
						reportMISDto.setLetterStatus(tuple[8].toString());
					}
					if(tuple[9] != null){
						reportMISDto.setLetterReviewer(tuple[9].toString());
					}
					if(tuple[10] != null && BankAuditConstant.LETTER_CODE_CompositeRating.equalsIgnoreCase(tuple[12].toString())){
						reportMISDto.setLetterCategory(tuple[10].toString());
					}else if(!BankAuditConstant.LETTER_CODE_CompositeRating.equalsIgnoreCase(tuple[12].toString())){
						reportMISDto.setLetterCategory("NA");
					}
					if(tuple[11] != null ){
						reportMISDto.setLetterReviewerDesignation(tuple[11].toString());
					} 					 
				}		
				return reportMISDto;			
			}
			
			@Override
			public List transformList(List collection) { 
				return collection;
			}
		}); 
		
		List<ReportMISDto> listReportMISDto = sqlQuery.list();
		return ((listReportMISDto==null || listReportMISDto.size()==0)?new ArrayList<>():listReportMISDto);	
	}
	
	@Override
	public List<ReportMISDto>  qsoGradingMISReport(Integer legalEntityCode, String auditTypeCode, LocalDate financialYear, List<LocalDate> financialYearList, 
			List<String> unitList){

		List<AuditSchedule> auditList = getAuditListBy(legalEntityCode, auditTypeCode, unitList, financialYear, financialYearList);
 		if(auditList == null || auditList.size() == 0){			
 			return new ArrayList<>(); 
		}
 		
		List<String> auditIdList = auditList.stream().map(e -> e.getAuditId()).collect(Collectors.toList());		
		Map<String, AuditSchedule> auditIdMap = auditList.stream().collect(Collectors.toMap(AuditSchedule::getAuditId, value -> value));
		
		StringBuilder strQry =  new StringBuilder();
		strQry.append(" Select auditId, auditTypeCodeDesc, unitName, parentUnitCodeName, zoneCode, zoneName, "
				+ " gradedBy, gradingStatus, gradingInitDate, gradingMaker, gradingFinlDate, gradingChecker, gradingScore, gradingCategory,  "
				+ " gradingCompFinlDt, gradingCompChecker, gradingMakerDesig, gradingCheckerDesig, gradingCompCheckerDesig ,"
				+ " gradingCompCount, actualStDt   FROM ("
				+ " SELECT sch.audit_id AS auditId,   "				
				+ " (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,"  //1
				+ " (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 Concat(e1.unit_code, '-', e1.unit_name) FROM  maint_entity e INNER JOIN   maint_entity e1   ON  e.legal_entity_code = sch.legal_entity_code " 
						+ " AND e.unit_code = sch.unit_id   AND e1.unit_code = e.parent_unit_code )  AS parentUnitCodeName,"		//3		
				
				+ "  'zoneCode' as zoneCode, "
				+ " (SELECT m.zone_name FROM maint_entity e, maint_zone_mapping m WHERE  "
						+ " (CASE WHEN e.level_code IN ('3') THEN e.parent_unit_code ELSE e.unit_code END) = m.unit_code "
						+ " AND e.unit_code=sch.unit_id) AS zoneName,"   // zone.zoneCode,  zone.zoneName,	//5
				
				+ " grading.grading_checker_ro AS gradedBy, " //6		
				+ " (SELECT g.description from general_parameter g where g.legal_entity_code = sch.legal_entity_code AND g.key_1 = 'GRADING_STATUS'"
						+ " AND g.key_2 IN ('GRADING_STATUS_RO') AND g.value = grading.grading_status_ro) AS gradingStatus, " //7
						
				+ " grading.grading_maker_timestamp_ro AS gradingInitDate, "	//8	
				/*+ " (SELECT  concat(u.user_id,'-', ifnull(u.first_name,''), ' ', ifnull(u.middle_name,''),' ', ifnull(u.last_name,'') ) "
						+ "	FROM  user u WHERE u.user_id = grading.grading_maker_ro  AND u.legal_entity_code = sch.legal_entity_code ) AS  gradingMaker, " //9 */				
				+ " (SELECT GROUP_CONCAT(u.user_id,'-', ifnull(u.first_name,''), ' ', ifnull(u.middle_name,''),' ', ifnull(u.last_name,'') ) FROM audit_schedule_stages_status_hst statusHst INNER JOIN user u "
						+ " ON u.user_id = statusHst.maker_id  AND u.legal_entity_code = statusHst.legal_entity_code "
						+ " WHERE  sch.legal_entity_code = statusHst.legal_entity_code "
						+ " AND sch.audit_type_code = statusHst.audit_type_code AND sch.audit_id  = statusHst.audit_id "
						+ " AND statusHst.code = '"+BankAuditConstant.LETTER_CODE_IR_GRADING+"' AND statusHst.action_type =  '"+BankAuditConstant.ACTION_TYPE_GRADING+"'"
						+ " AND statusHst.stages_status = '"+BankAuditConstant.GRADING_STATUS_RO_UNAUTH+"' "
						+ " ) as gradingMaker, "	//9		
						
				
				+ " grading.grading_checker_timestamp_ro AS gradingFinlDate, " //10				
				+ " (SELECT  concat(u.user_id,'-', ifnull(u.first_name,''), ' ', ifnull(u.middle_name,''),' ', ifnull(u.last_name,'') ) "
						+ "	FROM  user u WHERE u.user_id = grading.grading_checker_ro  AND u.legal_entity_code = sch.legal_entity_code ) AS  gradingChecker, " //11 
				
				+ " grading.grading_score_ro AS gradingScore, grading.grading_category_ro AS gradingCategory, "		//12, 13
				
				+ " grading.grading_ro_compliance_checker_timestamp AS gradingCompFinlDt, " //14				
				+ " (SELECT  concat(u.user_id,'-', ifnull(u.first_name,''), ' ', ifnull(u.middle_name,''),' ', ifnull(u.last_name,'') ) "
						+ "	FROM  user u WHERE u.user_id = grading.grading_ro_compliance_checker  AND u.legal_entity_code = sch.legal_entity_code ) AS  gradingCompChecker, " //15
				
				+ " (SELECT statusHst.maker_designation FROM audit_schedule_stages_status_hst statusHst WHERE  sch.legal_entity_code = statusHst.legal_entity_code "
						+ " AND sch.audit_type_code = statusHst.audit_type_code AND sch.audit_id  = statusHst.audit_id "
						+ " AND statusHst.code = '"+BankAuditConstant.LETTER_CODE_IR_GRADING+"' AND statusHst.action_type =  '"+BankAuditConstant.ACTION_TYPE_GRADING+"'"
						+ " AND statusHst.stages_status = '"+BankAuditConstant.GRADING_STATUS_RO_UNAUTH+"' "
					  //+ " AND statusHst.maker_id = grading.grading_maker_ro " 
						+ " ) as gradingMakerDesig, "	//16	
				
				+ " (SELECT statusHst.maker_designation FROM audit_schedule_stages_status_hst statusHst WHERE  sch.legal_entity_code = statusHst.legal_entity_code "
						+ " AND sch.audit_type_code = statusHst.audit_type_code AND sch.audit_id  = statusHst.audit_id "
						+ " AND statusHst.code = '"+BankAuditConstant.LETTER_CODE_IR_GRADING+"' AND statusHst.action_type =  '"+BankAuditConstant.ACTION_TYPE_GRADING+"'"
						+ " AND statusHst.stages_status = '"+BankAuditConstant.GRADING_STATUS_RO_AUTH+"' "
						+ " AND statusHst.maker_id =  grading.grading_checker_ro) as gradingCheckerDesig, "	//17
										
				+ " (SELECT statusHst.maker_designation FROM audit_schedule_stages_status_hst statusHst WHERE  sch.legal_entity_code = statusHst.legal_entity_code "
						+ " AND sch.audit_type_code = statusHst.audit_type_code AND sch.audit_id  = statusHst.audit_id "
						+ " AND statusHst.code = '"+BankAuditConstant.LETTER_CODE_IR_GRADING_RO_COMPLIANCE+"' AND statusHst.action_type =  '"+BankAuditConstant.ACTION_TYPE_GRADING+"'"
						+ " AND statusHst.stages_status =  '"+BankAuditConstant.GRADING_RO_COMPLIANCE_STATUS_AUTH+"' "
						+ " AND statusHst.maker_id =  grading.grading_ro_compliance_checker) as gradingCompCheckerDesig, "	//18
								
				+ " (Select count(audit_id) from audit_grading_qso_dtls_ro gradingComp WHERE  sch.legal_entity_code = gradingComp.legal_entity_code "
						+ " AND sch.audit_type_code = gradingComp.audit_type_code AND sch.audit_id  = gradingComp.audit_id "
						+ " AND gradingComp.status <> '"+BankAuditConstant.STATUS_DEL+"' ) as gradingCompCount, " //19
								
				+ " sch.actual_start_date as actualStDt "					
						
				+ " FROM  audit_schedule sch "
				+ " INNER JOIN audit_schedule_status grading ON grading.legal_entity_code=:legalEntityCode AND grading.audit_type_code = sch.audit_type_code AND grading.audit_id = sch.audit_id "
						+ " AND grading.grading_status <> '"+BankAuditConstant.GRADING_STATUS_PLANNED +"' AND grading.grading_status_ro IS NOT NULL" 					 	 
				+ " WHERE sch.legal_entity_code=:legalEntityCode AND  sch.audit_id IN (:auditIdList) "
				
				+ " UNION ALL "
				
				+ " SELECT sch.audit_id AS auditId,   "				
				+ " (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,"  //1
				+ " (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 Concat(e1.unit_code, '-', e1.unit_name) FROM  maint_entity e INNER JOIN   maint_entity e1   ON  e.legal_entity_code = sch.legal_entity_code " 
						+ " AND e.unit_code = sch.unit_id   AND e1.unit_code = e.parent_unit_code )  AS parentUnitCodeName,"		//3		
				
				+ "  'zoneCode' as zoneCode, "
				+ " (SELECT m.zone_name FROM maint_entity e, maint_zone_mapping m WHERE  "
						+ " (CASE WHEN e.level_code IN ('3') THEN e.parent_unit_code ELSE e.unit_code END) = m.unit_code "
						+ " AND e.unit_code=sch.unit_id) AS zoneName,"   // zone.zoneCode,  zone.zoneName,	//5
				
				+ " grading.grading_checker_ho AS gradedBy, " //6		
				+ " (SELECT g.description from general_parameter g where g.legal_entity_code = sch.legal_entity_code AND g.key_1 = 'GRADING_STATUS'"
						+ " AND g.key_2 IN ('GRADING_STATUS_HO') AND g.value = grading.grading_status_ho) AS gradingStatus, " //7
						
				+ " grading.grading_maker_timestamp_ho gradingInitDate, "	//8	
				/*+ " (SELECT  concat(u.user_id,'-', ifnull(u.first_name,''), ' ', ifnull(u.middle_name,''),' ', ifnull(u.last_name,'') ) "
						+ "	FROM  user u WHERE u.user_id = grading.grading_maker_ho  AND u.legal_entity_code = sch.legal_entity_code ) AS  gradingMaker, " //9 */
				+ " (SELECT GROUP_CONCAT(u.user_id,'-', ifnull(u.first_name,''), ' ', ifnull(u.middle_name,''),' ', ifnull(u.last_name,'') ) FROM audit_schedule_stages_status_hst statusHst INNER JOIN user u "
						+ " ON u.user_id = statusHst.maker_id  AND u.legal_entity_code = statusHst.legal_entity_code "
						+ " WHERE  sch.legal_entity_code = statusHst.legal_entity_code "
						+ " AND sch.audit_type_code = statusHst.audit_type_code AND sch.audit_id  = statusHst.audit_id "
						+ " AND statusHst.code = '"+BankAuditConstant.LETTER_CODE_IR_GRADING+"' AND statusHst.action_type =  '"+BankAuditConstant.ACTION_TYPE_GRADING+"'"
						+ " AND statusHst.stages_status = '"+BankAuditConstant.GRADING_STATUS_HO_UNAUTH+"' "
						+ " ) as gradingMaker, "	//9		
				
				+ " grading.grading_checker_timestamp_ho AS gradingFinlDate, " //10				
				+ " (SELECT  concat(u.user_id,'-', ifnull(u.first_name,''), ' ', ifnull(u.middle_name,''),' ', ifnull(u.last_name,'') ) "
						+ "	FROM  user u WHERE u.user_id = grading.grading_checker_ho  AND u.legal_entity_code = sch.legal_entity_code ) AS  gradingChecker, " //11 
				
				+ " grading.grading_score_ho AS gradingScore, grading.grading_category_ho AS gradingCategory, "		//12, 13
				
				+ " grading.grading_ho_compliance_checker_timestamp AS gradingCompFinlDt, " //14				
				+ " (SELECT  concat(u.user_id,'-', ifnull(u.first_name,''), ' ', ifnull(u.middle_name,''),' ', ifnull(u.last_name,'') ) "
						+ "	FROM  user u WHERE u.user_id = grading.grading_ho_compliance_checker  AND u.legal_entity_code = sch.legal_entity_code ) AS  gradingCompChecker, " //15
				
				+ " (SELECT statusHst.maker_designation FROM audit_schedule_stages_status_hst statusHst WHERE  sch.legal_entity_code = statusHst.legal_entity_code "
						+ " AND sch.audit_type_code = statusHst.audit_type_code AND sch.audit_id  = statusHst.audit_id "
						+ " AND statusHst.code = '"+BankAuditConstant.LETTER_CODE_IR_GRADING+"' AND statusHst.action_type =  '"+BankAuditConstant.ACTION_TYPE_GRADING+"'"
						+ " AND statusHst.stages_status =  '"+BankAuditConstant.GRADING_STATUS_HO_UNAUTH+"'"
					  //+ " AND statusHst.maker_id = grading.grading_maker_ho"
						+ " ) as gradingMakerDesig, "	//16	
				
				+ " (SELECT statusHst.maker_designation FROM audit_schedule_stages_status_hst statusHst WHERE  sch.legal_entity_code = statusHst.legal_entity_code "
						+ " AND sch.audit_type_code = statusHst.audit_type_code AND sch.audit_id  = statusHst.audit_id "
						+ " AND statusHst.code = '"+BankAuditConstant.LETTER_CODE_IR_GRADING+"' AND statusHst.action_type =  '"+BankAuditConstant.ACTION_TYPE_GRADING+"'"
						+ " AND statusHst.stages_status =  '"+BankAuditConstant.GRADING_STATUS_HO_AUTH+"' "
						+ " AND statusHst.maker_id =  grading.grading_checker_ho) as gradingCheckerDesig, "	//17
										
				+ " (SELECT statusHst.maker_designation FROM audit_schedule_stages_status_hst statusHst WHERE  sch.legal_entity_code = statusHst.legal_entity_code "
						+ " AND sch.audit_type_code = statusHst.audit_type_code AND sch.audit_id  = statusHst.audit_id "
						+ " AND statusHst.code = '"+BankAuditConstant.LETTER_CODE_IR_GRADING_HO_COMPLIANCE+"' AND statusHst.action_type =  '"+BankAuditConstant.ACTION_TYPE_GRADING+"'"
						+ " AND statusHst.stages_status =  '"+BankAuditConstant.GRADING_HO_COMPLIANCE_STATUS_AUTH+"' "
						+ " AND statusHst.maker_id =  grading.grading_ho_compliance_checker) as gradingCompCheckerDesig, "	//18
				
				+ " (Select count(audit_id) from audit_grading_qso_dtls_ho gradingComp WHERE  sch.legal_entity_code = gradingComp.legal_entity_code "
						+ " AND sch.audit_type_code = gradingComp.audit_type_code AND sch.audit_id  = gradingComp.audit_id "
						+ " AND gradingComp.status <> '"+BankAuditConstant.STATUS_DEL+"' ) as gradingCompCount, " //19				
				+ " sch.actual_start_date as actualStDt "				
						
				+ " FROM  audit_schedule sch "
				+ " INNER JOIN audit_schedule_status grading ON grading.legal_entity_code=:legalEntityCode  AND grading.audit_type_code = sch.audit_type_code AND grading.audit_id = sch.audit_id "
						+ " AND grading.grading_status <> '"+BankAuditConstant.GRADING_STATUS_PLANNED +"'  AND grading.grading_status_ho IS NOT NULL" 
					 		 
				+ " WHERE sch.legal_entity_code=:legalEntityCode AND  sch.audit_id IN (:auditIdList) "
				+ " ) as gradingDetails "					
				+ " ORDER BY actualStDt, auditId ");
		 		
		Session session=getSession();
	    session.setDefaultReadOnly(true);
		Query sqlQuery= session.createNativeQuery(strQry.toString())
				.setParameter("legalEntityCode", legalEntityCode)
    			.setParameterList("auditIdList", auditIdList) ;
		sqlQuery.setResultTransformer(new ResultTransformer() {			
			@Override
			public Object transformTuple(Object[] tuple, String[] aliases) {
				ReportMISDto  reportMISDto = new ReportMISDto();
				if(tuple[0] != null){
					AuditSchedule auditSchedule = auditIdMap.get(tuple[0].toString());
					
					reportMISDto.setLegalEntityCode(legalEntityCode);
					reportMISDto.setAuditTypeCode(String.valueOf(tuple[1]));
					reportMISDto.setAuditId(auditSchedule.getAuditId());
					reportMISDto.setPlanId(auditSchedule.getPlanId()+"-"+auditSchedule.getAuditId());  
					reportMISDto.setFinancialYear(auditSchedule.getFinancialYear());
					reportMISDto.setIrFinalisationDate(auditSchedule.getActualEndDate()); //Instead of IrFinalisationDate, actualEndDate is Finalization date
					reportMISDto.setReportDispatchDate(auditSchedule.getReportDispatchDate());	
					 
					reportMISDto.setUnitCode(auditSchedule.getUnitId()); 
					if(tuple[2] != null){
						reportMISDto.setUnitName(tuple[2].toString()); 
					}
					if(tuple[3] != null){
						reportMISDto.setParentUnitCode(tuple[3].toString().split("-")[0]); 
						reportMISDto.setParentUnitName(tuple[3].toString().split("-")[1]); 
					}
					reportMISDto.setZoneCode(String.valueOf(tuple[4]));
					reportMISDto.setZoneName(String.valueOf(tuple[5]));					 
					
					if(tuple[6] != null){
						reportMISDto.setGradedBy(tuple[6].toString());
					}
					if(tuple[7] != null){
						reportMISDto.setGradingStatus(tuple[7].toString());
					}					
					if(tuple[8] != null){
						reportMISDto.setGradingQSNInitDate((Date) tuple[8]);
					}
					if(tuple[9] != null){
						reportMISDto.setGradingQSNMaker(tuple[9].toString());
					}					
					if(tuple[10] != null){
						reportMISDto.setGradingQSNFinalisationDate((Date) tuple[10]);
					}
					if(tuple[11] != null){
						reportMISDto.setGradingQSNChecker(tuple[11].toString());
					}					
					if(tuple[12] != null && !BankAuditUtil.isEmptyString(tuple[12].toString())){
						reportMISDto.setGradingMarks(String.format("%.2f", Double.parseDouble(tuple[12].toString())));
					}
					if(tuple[13] != null){
						reportMISDto.setGradingCategory(tuple[13].toString());
					}
					if(tuple[14] != null){
						reportMISDto.setGradingComplianceFinalisationDate((Date) tuple[14]);
					}
					if(tuple[15] != null){
						reportMISDto.setGradingComplianceChecker(tuple[15].toString());
					}					
					if(tuple[16] != null){
						reportMISDto.setGradingQSNMakerDesignation(tuple[16].toString());
					}
					if(tuple[17] != null){
						reportMISDto.setGradingQSNCheckerDesignation(tuple[17].toString());
					}
					if(tuple[18] != null){
						reportMISDto.setGradingComplianceCheckerDesignation(tuple[18].toString());
					}
					if(tuple[19] != null){
						reportMISDto.setTotalQSO(tuple[19].toString());
					}					 
				}		
				return reportMISDto;			
			}
			
			@Override
			public List transformList(List collection) { 
				return collection;
			}
		}); 
		
		List<ReportMISDto> listReportMISDto = sqlQuery.list();
		return ((listReportMISDto==null || listReportMISDto.size()==0)?new ArrayList<>():listReportMISDto);	
	}
	
	@Override
	public List<ReportMISDto> complianceRatingMISReport(Integer legalEntityCode, String auditTypeCode, LocalDate financialYear, List<LocalDate> financialYearList, 
			List<String> unitList){
		
		List<AuditSchedule> auditList = getAuditListBy(legalEntityCode, auditTypeCode, unitList, financialYear, financialYearList);
 		if(auditList == null || auditList.size() == 0){	return new ArrayList<>();  } 		
		List<String> auditIdList = auditList.stream().map(e -> e.getAuditId()).collect(Collectors.toList());		 
		
		LocalDate financialYearStartDt = financialYearList.get(0);
		LocalDate financialYearEndDt = financialYearList.get(1);
		
		StringBuilder strQry =  new StringBuilder();
		strQry.append(" SELECT auditId, auditTypeCodeDesc, unitName, parentUnitCodeName, zoneCode, zoneName, respondStatus, reviewStatus, totalObs,   "
				+ " score1, rating1, maxMarks, marksObtained, ratingMaker, reviewDate, designation, unitId, ratingType, "
				+ " planId, referenceYear1 "
				+ " FROM( "				
						+ " (SELECT sch.audit_id AS auditId,  "				
						+ " (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,"  //1
						+ " (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,  " //2
						+ " (SELECT Concat(e1.unit_code, '-', e1.unit_name) FROM  maint_entity e INNER JOIN   maint_entity e1   ON  e.legal_entity_code = sch.legal_entity_code " 
								+ " AND e.unit_code = sch.unit_id   AND e1.unit_code = e.parent_unit_code )  AS parentUnitCodeName,"		//3		
						
						+ "  'zoneCode' as zoneCode, " //4
						+ " (SELECT m.zone_name FROM maint_entity e, maint_zone_mapping m WHERE  "
								+ " (CASE WHEN e.level_code IN ('3') THEN e.parent_unit_code ELSE e.unit_code END) = m.unit_code "
								+ " AND e.unit_code=sch.unit_id) AS zoneName,"   // zone.zoneCode,  zone.zoneName,	//5
								
						+ " (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, "  //6
						+ " (Select concat(sch.review_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.review_status) as reviewStatus, "    //7		
					    
						/*+ " (Select  count(obs.observation_id)  FROM audit_observation obs  WHERE  sch.legal_entity_code  = obs.legal_entity_code  AND sch.audit_type_code = obs.audit_type_code "
						+ " AND sch.audit_id = obs.audit_id  ) AS totalObs, "*/		 
						+ " schStatus.non_compliance_observations AS totalObs, "		
						+ " sch.desk_compliance_score as score1 , sch.desk_compliance_rating AS rating1, "
						+ " rating.desk_compliance_maximum_marks AS maxMarks , rating.desk_compliance_marks_obtained AS marksObtained, "	
						
						+ " (SELECT  concat(u.user_id,'-', ifnull(u.first_name,''), ' ', ifnull(u.middle_name,''),' ', ifnull(u.last_name,'') ) "
								+ "	FROM  user u WHERE u.user_id = statusHst.maker_id  AND u.legal_entity_code = statusHst.legal_entity_code ) AS ratingMaker, " //9 
						+ " statusHst.maker_timestamp AS reviewDate , statusHst.maker_designation AS designation, "					
						+ " 'Desk' AS ratingType, sch.unit_id AS unitId, "
						+ " sch.plan_id AS planId, sch.financial_year AS referenceYear1  "  						 
												
						+ " FROM  audit_schedule sch "
					  	+ " LEFT JOIN audit_schedule_status schStatus ON schStatus.legal_entity_code=:legalEntityCode  "
					  		+ " AND schStatus.audit_type_code = sch.audit_type_code AND schStatus.audit_id = sch.audit_id "
					 	+ " LEFT JOIN audit_schedule_stages_status_hst statusHst ON sch.legal_entity_code = statusHst.legal_entity_code "
					 		+ " AND  sch.audit_type_code = statusHst.audit_type_code AND sch.audit_id  = statusHst.audit_id "
							+ " AND  statusHst.code = '"+BankAuditConstant.RATING_DESK+"' AND stages_status = 'RCDS' AND statusHst.action_type =  '"+BankAuditConstant.ACTION_TYPE_COMPLIANCE_RATING+"' " 
						+ " LEFT JOIN audit_rating rating  ON  sch.legal_entity_code  = rating.legal_entity_code "
					 		+ " AND  sch.audit_type_code = rating.audit_type_code AND sch.audit_id = rating.audit_id "
						+ " WHERE sch.legal_entity_code=:legalEntityCode AND  sch.audit_id IN (:auditIdList) ) "
						
						+" UNION ALL "
					 
						+ " (SELECT sch.audit_id AS auditId,  "				
						+ " (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,"  //1
						+ " (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,  " //2
						+ " (SELECT Concat(e1.unit_code, '-', e1.unit_name) FROM  maint_entity e INNER JOIN   maint_entity e1   ON  e.legal_entity_code = sch.legal_entity_code " 
								+ " AND e.unit_code = sch.unit_id   AND e1.unit_code = e.parent_unit_code )  AS parentUnitCodeName,"		//3		
						
						+ "  'zoneCode' as zoneCode, " //4
						+ " (SELECT m.zone_name FROM maint_entity e, maint_zone_mapping m WHERE  "
								+ " (CASE WHEN e.level_code IN ('3') THEN e.parent_unit_code ELSE e.unit_code END) = m.unit_code "
								+ " AND e.unit_code=sch.unit_id) AS zoneName,"   // zone.zoneCode,  zone.zoneName,	//5
								
						+ " (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, "  //6
						+ " (Select concat(sch.review_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.review_status) as reviewStatus, "    //7		
					    
						/*+ " (Select  count(obs.observation_id)  FROM audit_observation obs  WHERE  sch.legal_entity_code  = obs.legal_entity_code  AND sch.audit_type_code = obs.audit_type_code "
						+ " AND sch.audit_id = obs.audit_id  ) AS totalObs, "*/
						+ " schStatus.non_compliance_observations AS totalObs, "
								
						+ " schPre.onsite_compliance_score as score1 , schPre.onsite_compliance_rating AS rating1, "
						+ " rating.onsite_compliance_maximum_marks AS maxMarks , rating.onsite_compliance_marks_obtained AS marksObtained, "	
						
						+ " (SELECT  concat(u.user_id,'-', ifnull(u.first_name,''), ' ', ifnull(u.middle_name,''),' ', ifnull(u.last_name,'') ) "
								+ "	FROM  user u WHERE u.user_id = statusHst.maker_id  AND u.legal_entity_code = statusHst.legal_entity_code ) AS ratingMaker, " //9 
						+ " statusHst.maker_timestamp AS reviewDate, statusHst.maker_designation AS designation , "					
						+ " 'Field' AS ratingType, sch.unit_id AS unitId, "
						+ " sch.plan_id AS planId, sch.financial_year AS referenceYear1  "  						 
												
						+ " FROM  audit_schedule sch "
						+ " INNER JOIN audit_schedule schPre ON  sch.legal_entity_code  = schPre.legal_entity_code "
							+ " AND sch.unit_id = schPre.unit_id AND sch.prev_audit_id = schPre.audit_id " 
						+ " LEFT JOIN audit_schedule_status schStatus ON schStatus.legal_entity_code=:legalEntityCode "
					 		+ " AND schStatus.audit_type_code = sch.audit_type_code AND schStatus.audit_id = sch.audit_id "						 						 	
					 	+ " LEFT JOIN audit_schedule_stages_status_hst statusHst ON schPre.legal_entity_code = statusHst.legal_entity_code "
					 		+ " AND  schPre.audit_type_code = statusHst.audit_type_code AND schPre.audit_id  = statusHst.audit_id "
					 		+ " AND  statusHst.code = '"+BankAuditConstant.RATING_FIELD+"' AND stages_status = 'RCFS' AND statusHst.action_type =  '"+BankAuditConstant.ACTION_TYPE_COMPLIANCE_RATING+"' "	 
					 	+ " LEFT JOIN audit_rating rating  ON  sch.legal_entity_code  = rating.legal_entity_code "
					 		+ " AND  schPre.audit_type_code = rating.audit_type_code AND schPre.audit_id = rating.audit_id "
						+ " WHERE sch.legal_entity_code=:legalEntityCode AND  sch.audit_id IN (:auditIdList) ) "
					 		
						
				+ " ) AS compRating "				
				+ " ORDER BY referenceYear1, unitId  ");
		  
		Session session=getSession();
	    session.setDefaultReadOnly(true);
		Query sqlQuery= session.createNativeQuery(strQry.toString())
				.setParameter("legalEntityCode", legalEntityCode)
    			.setParameterList("auditIdList", auditIdList);
		sqlQuery.setResultTransformer(new ResultTransformer() {			
			@Override
			public Object transformTuple(Object[] tuple, String[] aliases) {
				ReportMISDto  reportMISDto = new ReportMISDto();
				if(tuple[0] != null){  
					
					reportMISDto.setLegalEntityCode(legalEntityCode);
					reportMISDto.setAuditId(tuple[0].toString());					
					reportMISDto.setAuditTypeCode(String.valueOf(tuple[1])); 
					
					if(tuple[18] != null){reportMISDto.setPlanId(tuple[18].toString()+"-"+tuple[0].toString());}  
					if(tuple[2] != null){reportMISDto.setUnitName(tuple[2].toString());}
					if(tuple[16] != null){reportMISDto.setUnitCode(tuple[16].toString());}					
					if(tuple[3] != null){
						reportMISDto.setParentUnitCode(tuple[3].toString().split("-")[0]); 
						reportMISDto.setParentUnitName(tuple[3].toString().split("-")[1]); 
					}
					reportMISDto.setZoneCode(String.valueOf(tuple[4]));
					reportMISDto.setZoneName(String.valueOf(tuple[5]));					
					
					if(tuple[6] != null){reportMISDto.setRespondStatus(tuple[6].toString());}
					if(tuple[7] != null){reportMISDto.setReviewStatus(tuple[7].toString());}
					if(tuple[8] != null){reportMISDto.setObservationCount(tuple[8].toString());}
					 
					if(tuple[9] != null && !BankAuditUtil.isEmptyString(tuple[9].toString())){reportMISDto.setComplianceRatingMarks1(String.format("%.2f", Double.parseDouble(tuple[9].toString())));}
					if(tuple[10] != null){reportMISDto.setComplianceRatingCategory1(tuple[10].toString());}
					
					if(tuple[11] != null && !BankAuditUtil.isEmptyString(tuple[11].toString())){reportMISDto.setComplianceRatingMaximumMarks(String.format("%.2f", Double.parseDouble(tuple[11].toString())));}
					if(tuple[12] != null && !BankAuditUtil.isEmptyString(tuple[12].toString())){reportMISDto.setComplianceRatingMarksObtained(String.format("%.2f", Double.parseDouble(tuple[12].toString())));}
					if(tuple[13] != null){reportMISDto.setComplianceRatingChecker(tuple[13].toString());}
					
					if(tuple[14] != null){reportMISDto.setComplianceRatingFinalisationDate((Date)tuple[14]);}
					if(tuple[15] != null){reportMISDto.setComplianceRatingCheckerDesignation(tuple[15].toString());}
					if(tuple[17] != null){reportMISDto.setComplianceRatingType(tuple[17].toString());}
					if(tuple[19] != null){reportMISDto.setReferenceYear1((Date)tuple[19]);reportMISDto.setFinancialYear((Date)tuple[19]);}
					 
				}		
				return reportMISDto;			
			}
			
			@Override
			public List transformList(List collection) { 
				return collection;
			}
		}); 
		
		List<ReportMISDto> listReportMISDto = sqlQuery.list();
		return ((listReportMISDto==null || listReportMISDto.size()==0)?new ArrayList<>():listReportMISDto);		
	}
	
	@Override
	public List<ReportMISDto> complianceRating3YearsMISReport(Integer legalEntityCode, String auditTypeCode, LocalDate financialYear, List<LocalDate> financialYearList, 
			List<String> unitList){
		
		List<AuditSchedule> auditList = getAuditListBy(legalEntityCode, auditTypeCode, unitList, financialYear, financialYearList);
 		if(auditList == null || auditList.size() == 0){	return new ArrayList<>();  } 		
		List<String> auditIdList = auditList.stream().map(e -> e.getAuditId()).collect(Collectors.toList());		 
		
		LocalDate financialYearStartDt = financialYearList.get(0);
		LocalDate financialYearEndDt = financialYearList.get(1);
		
		StringBuilder strQry =  new StringBuilder();
		strQry.append(" SELECT auditId, auditTypeCodeDesc, unitName, parentUnitCodeName, zoneCode, zoneName, referenceYear1, referenceYear2, referenceYear3,"
				+ "  score1, rating1, score2, rating2, score3, rating3, ratingType, unitId, auditTypeCodeDesc2, auditTypeCodeDesc3 "
				+ " FROM( "				
						+ " (SELECT sch.audit_id AS auditId,  "				
						+ " (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,"  //1
						+ " (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,  " //2
						+ " (SELECT Concat(e1.unit_code, '-', e1.unit_name) FROM  maint_entity e INNER JOIN   maint_entity e1   ON  e.legal_entity_code = sch.legal_entity_code " 
								+ " AND e.unit_code = sch.unit_id   AND e1.unit_code = e.parent_unit_code )  AS parentUnitCodeName,"		//3		
						
						+ "  'zoneCode' as zoneCode, " //4
						+ " (SELECT m.zone_name FROM maint_entity e, maint_zone_mapping m WHERE  "
								+ " (CASE WHEN e.level_code IN ('3') THEN e.parent_unit_code ELSE e.unit_code END) = m.unit_code "
								+ " AND e.unit_code=sch.unit_id) AS zoneName,"   // zone.zoneCode,  zone.zoneName,	//5
						
						+ " sch.financial_year AS referenceYear1, schPre.financial_year AS referenceYear2, schPrePre.financial_year AS referenceYear3, " 
						+ " sch.desk_compliance_score AS score1, 			sch.desk_compliance_rating AS rating1 ,   "
						+ " schPre.desk_compliance_score  AS score2, 	 	schPre.desk_compliance_rating AS rating2,   "
						+ " schPrePre.desk_compliance_score  AS score3,  	schPrePre.desk_compliance_rating AS rating3,   "
						+ " 'Desk' as ratingType,  "						
						+ " sch.unit_id AS unitId, "
						
						+ " (SELECT concat(audit_type_code , '-' , audit_type_desc) FROM maint_audit_type_desc at  WHERE at.audit_type_code = schPre.audit_type_code "
								+ " AND at.legal_entity_code = schPre.legal_entity_code ) AS auditTypeCodeDesc2, "   
						+ " (SELECT concat(audit_type_code , '-' , audit_type_desc) FROM maint_audit_type_desc at  WHERE at.audit_type_code = schPrePre.audit_type_code "
								+ " AND at.legal_entity_code = schPrePre.legal_entity_code ) AS auditTypeCodeDesc3 "   
												
						+ " FROM  audit_schedule sch "
						+ " LEFT JOIN audit_schedule schPre ON sch.unit_id = schPre.unit_id AND sch.legal_entity_code  = schPre.legal_entity_code "
								+ " AND sch.prev_audit_id = schPre.audit_id "
								+ " AND (schPre.actual_start_date BETWEEN '"+financialYearStartDt.minusYears(1)+"' AND  '"+financialYearEndDt.minusYears(1)+"') "
						+ " LEFT JOIN audit_schedule schPrePre ON sch.unit_id = schPrePre.unit_id AND sch.legal_entity_code  = schPrePre.legal_entity_code "
								+ " AND (schPrePre.actual_start_date BETWEEN '"+financialYearStartDt.minusYears(2)+"' AND  '"+financialYearEndDt.minusYears(2)+"') " 
								+ " AND schPre.prev_audit_id = schPrePre.audit_id "
						+ " WHERE sch.legal_entity_code=:legalEntityCode AND  sch.audit_id IN (:auditIdList) ) "
						
						+" UNION ALL "
					 
						+ " (SELECT sch.audit_id AS auditId,   "				
						+ " (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,"  //1
						+ " (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 Concat(e1.unit_code, '-', e1.unit_name) FROM  maint_entity e INNER JOIN   maint_entity e1   ON  e.legal_entity_code = sch.legal_entity_code " 
								+ " AND e.unit_code = sch.unit_id   AND e1.unit_code = e.parent_unit_code )  AS parentUnitCodeName,"		//3		
						
						+ "  'zoneCode' as zoneCode, "
						+ " (SELECT m.zone_name FROM maint_entity e, maint_zone_mapping m WHERE  "
								+ " (CASE WHEN e.level_code IN ('3') THEN e.parent_unit_code ELSE e.unit_code END) = m.unit_code "
								+ " AND e.unit_code=sch.unit_id) AS zoneName,"   // zone.zoneCode,  zone.zoneName,	//5
						
						+ " sch.financial_year AS referenceYear1, schPre.financial_year AS referenceYear2, schPrePre.financial_year AS referenceYear3, " 
						+ " sch.onsite_compliance_score AS score1, 			sch.onsite_compliance_rating AS rating1, "
						+ " schPre.onsite_compliance_score AS score2, 		schPre.onsite_compliance_rating AS rating2, "
						+ " schPrePre.onsite_compliance_score AS score3,  	schPrePre.onsite_compliance_rating AS rating3, "
						+ " 'Field' as ratingType, "
						+ " sch.unit_id AS unitId, "
						
						+ " (SELECT concat(audit_type_code , '-' , audit_type_desc) FROM maint_audit_type_desc at  WHERE at.audit_type_code = schPre.audit_type_code "
								+ " AND at.legal_entity_code = schPre.legal_entity_code ) AS auditTypeCodeDesc2, "   
						+ " (SELECT concat(audit_type_code , '-' , audit_type_desc) FROM maint_audit_type_desc at  WHERE at.audit_type_code = schPrePre.audit_type_code "
								+ " AND at.legal_entity_code = schPrePre.legal_entity_code ) AS auditTypeCodeDesc3 "   
						
						+ " FROM  audit_schedule sch "
						+ " LEFT JOIN audit_schedule schPre ON sch.unit_id = schPre.unit_id  AND sch.legal_entity_code  = schPre.legal_entity_code "
							+ " AND sch.prev_audit_id = schPre.audit_id "
							+ " AND (schPre.actual_start_date BETWEEN '"+financialYearStartDt.minusYears(1)+"' AND  '"+financialYearEndDt.minusYears(1)+"') "
						+ " LEFT JOIN audit_schedule schPrePre ON sch.unit_id = schPrePre.unit_id AND sch.legal_entity_code  = schPrePre.legal_entity_code "
							+ " AND schPre.prev_audit_id = schPrePre.audit_id "
							+ " AND (schPrePre.actual_start_date BETWEEN '"+financialYearStartDt.minusYears(2)+"' AND  '"+financialYearEndDt.minusYears(2)+"') " 						 
						+ " WHERE sch.legal_entity_code=:legalEntityCode AND  sch.audit_id IN (:auditIdList))"
				+ " ) AS compRating "				
				+ " ORDER BY referenceYear1, unitId  ");
		  
		Session session=getSession();
	    session.setDefaultReadOnly(true);
		Query sqlQuery= session.createNativeQuery(strQry.toString())
				.setParameter("legalEntityCode", legalEntityCode)
    			.setParameterList("auditIdList", auditIdList);
		sqlQuery.setResultTransformer(new ResultTransformer() {			
			@Override
			public Object transformTuple(Object[] tuple, String[] aliases) {
				ReportMISDto  reportMISDto = new ReportMISDto();
				if(tuple[0] != null){  
					
					reportMISDto.setLegalEntityCode(legalEntityCode);
					reportMISDto.setAuditTypeCode(String.valueOf(tuple[1]));  
					  
					if(tuple[2] != null){
						reportMISDto.setUnitName(tuple[2].toString()); 
					}
					if(tuple[6] != null){
						reportMISDto.setUnitCode(tuple[16].toString()); 
					}					
					if(tuple[3] != null){
						reportMISDto.setParentUnitCode(tuple[3].toString().split("-")[0]); 
						reportMISDto.setParentUnitName(tuple[3].toString().split("-")[1]); 
					}
					reportMISDto.setZoneCode(String.valueOf(tuple[4]));
					reportMISDto.setZoneName(String.valueOf(tuple[5]));						
					if(tuple[6] != null){
						reportMISDto.setReferenceYear1((Date) tuple[6] );
					}
					if(tuple[7] != null){
						reportMISDto.setReferenceYear2((Date) tuple[7]);
					}
					if(tuple[8] != null){
						reportMISDto.setReferenceYear3((Date)tuple[8]);
					}					
					if(tuple[9] != null && !BankAuditUtil.isEmptyString(tuple[9].toString())){
						reportMISDto.setComplianceRatingMarks1(String.format("%.2f", Double.parseDouble(tuple[9].toString())));
					}
					if(tuple[10] != null){
						reportMISDto.setComplianceRatingCategory1(tuple[10].toString());
					}					
					if(tuple[11] != null && !BankAuditUtil.isEmptyString(tuple[11].toString())){
						reportMISDto.setComplianceRatingMarks2(String.format("%.2f", Double.parseDouble(tuple[11].toString())));
					}
					if(tuple[12] != null){
						reportMISDto.setComplianceRatingCategory2(tuple[12].toString());
					}					
					if(tuple[13] != null && !BankAuditUtil.isEmptyString(tuple[13].toString())){
						reportMISDto.setComplianceRatingMarks3(String.format("%.2f", Double.parseDouble(tuple[13].toString())));
					}
					if(tuple[14] != null){
						reportMISDto.setComplianceRatingCategory3(tuple[14].toString());
					}
					if(tuple[15] != null){
						reportMISDto.setComplianceRatingType(tuple[15].toString());
					} 					
					if(tuple[17] != null){
						reportMISDto.setAuditTypeCode2(tuple[17].toString());
					} 
					if(tuple[18] != null){
						reportMISDto.setAuditTypeCode3(tuple[18].toString());
					}  				
					 					 
				}		
				return reportMISDto;			
			}
			
			@Override
			public List transformList(List collection) { 
				return collection;
			}
		}); 
		
		List<ReportMISDto> listReportMISDto = sqlQuery.list();
		return ((listReportMISDto==null || listReportMISDto.size()==0)?new ArrayList<>():listReportMISDto);		
	}
	
	private List<AuditSchedule> getAuditListBy(Integer legalEntityCode, String auditTypeCode , List<String> unitList, LocalDate financialYear, List<LocalDate>  financialYearList){
        List<AuditSchedule> auditList =null;
        logger.info("Inside getAuditListBy .. "+ financialYear+" :: "+auditTypeCode+" :: "+ unitList);
        try {
        	LocalDate financialYearStartDt = financialYearList.get(0);
			LocalDate financialYearEndDt = financialYearList.get(1);
			StringBuilder query= new StringBuilder("SELECT sch as AuditSch  FROM AuditSchedule sch WHERE  unitId IN (:unitList)"  
        			+ " AND legalEntityCode=:legalEntityCode  ") ;
			query.append(" AND (actualStartDate between '"+financialYearStartDt+"' and '"+financialYearEndDt+"')   ");
			
			if(!BankAuditUtil.isEmptyString(auditTypeCode)) {
				query.append("AND auditTypeCode ='"+ auditTypeCode+"'");
			}
        	
			Query hqlQuery= getSession().createQuery(query.toString())
        			.setParameter("legalEntityCode", legalEntityCode)
        			.setParameter("unitList", unitList); 
			auditList=   hqlQuery.list();
        }catch(Exception e) {
        	e.printStackTrace(System.out);
        } 
        return auditList;
	}
   	
}
