package com.bankaudit.rest.process.dao;
  
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.function.Function;
import java.util.stream.Collectors;

import javax.persistence.Column;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.ResultTransformer;
import org.jboss.logging.Logger;
import org.springframework.stereotype.Repository;

import com.bankaudit.constants.BankAuditConstant;
import com.bankaudit.dao.AbstractDao;
import com.bankaudit.dto.ChecklistDto;
import com.bankaudit.dto.DataTableResponse;
import com.bankaudit.model.MaintEntity;
import com.bankaudit.process.model.AuditLevelDocumentDetails;
import com.bankaudit.process.model.AuditObservation;
import com.bankaudit.process.model.AuditSchedule;
import com.bankaudit.process.model.ChecklistOfflineInspection;
import com.bankaudit.process.model.ChecklistOfflineInspectionTmp;
import com.bankaudit.process.model.IfcEnsureBaasData;
import com.bankaudit.process.model.IfcEnsureBaasDataTmp;
import com.bankaudit.process.model.IfcEnsureBaasIRProcessData;
import com.bankaudit.process.model.IfcParameterMapping;
import com.bankaudit.util.BankAuditUtil;

@Repository("ifcEnsureBaasDataDao")
public class IfcEnsureBaasDataDaoImpl extends AbstractDao implements IfcEnsureBaasDataDao {


	static final Logger logger = Logger.getLogger(IfcEnsureBaasDataDaoImpl.class);	
	
	@Override
	public Integer getMaxReferenceNumber(){ 
		Session session = getSession();		 
		Integer maxReferenceNo = (Integer) session.createQuery("Select max(referenceNo) from AuditLevelDocumentDetails").getSingleResult();				 
		return maxReferenceNo==null?1:maxReferenceNo+1;		  
	}

	@Override
	public String getBaasParameterName(Integer legalEntityCode,  String auditTypeCode, String nabardParameterName){
		
		Session session = getSession(); 
		
		Criteria crit = session.createCriteria(IfcParameterMapping.class)
			.add( Restrictions.eq("legalEntityCode",  legalEntityCode) )
			.add( Restrictions.eq("auditTypeCode", auditTypeCode) )
			.add( Restrictions.eq("parameterNameNabard", nabardParameterName) );		 
		
		IfcParameterMapping ifcParameterMapping = (IfcParameterMapping)crit.uniqueResult();
		
		return ifcParameterMapping==null?"":ifcParameterMapping.getParameterNameBaas()==null?"":ifcParameterMapping.getParameterNameBaas();
		
	}

	@Override
	public String copyDataToTempFromCSV(IfcEnsureBaasDataTmp ifcEnsureBaasDataTmp){
		
		try{
			Session session = getSession();
		    session.save(ifcEnsureBaasDataTmp);
		}catch (Exception e) {
			logger.error(e.getStackTrace());
			System.out.println(e);
			return BankAuditConstant.FAILED;
		}
		return BankAuditConstant.SUCCESS;
	}
	
	
	@Override
	public String copyDataToMasterFromTemp(AuditLevelDocumentDetails auditLevelDocumentDetails){
		
		try{
			Session session = getSession();
			
			//update master 		
			session.createQuery("  Update "
					+ " IfcEnsureBaasData  "
					+ " set stage_of_file = '"+BankAuditConstant.STAGE_OF_FILE_OLD+"' "  
					+ " where  legalEntityCode =:legalEntityCode"					 
					+ " and auditTypeCode =:auditTypeCode "
					+ " and auditId =:auditId "
					+ " and stage_of_file =:stageOfFile ") 
			
				.setParameter("legalEntityCode", auditLevelDocumentDetails.getLegalEntityCode())				
				.setParameter("auditTypeCode",  auditLevelDocumentDetails.getAuditTypeCode())
				.setParameter("auditId",  auditLevelDocumentDetails.getAuditId())
				.setParameter("stageOfFile",  BankAuditConstant.STAGE_OF_FILE_LATEST)
				.executeUpdate();
			
			
			//Insert into Master
			session.createQuery(" Insert into "
					+ " IfcEnsureBaasData("
						+ " legalEntityCode, auditTypeCode, auditId, "
						+ " referenceNo, stageOfFile,  unitCode, parameterNameBaas, parameterNameNabard, parameterName,  "
						+ " submittedValueLatest, submittedValueMiddest, submittedValueOldest, "
						+ " maker, makerTimestamp) "
					+ " select "
						+ " legalEntityCode, auditTypeCode, auditId, "
						+ " referenceNo, stageOfFile,  unitCode,  parameterNameBaas, parameterNameNabard, parameterName, "
						+ " submittedValueLatest, submittedValueMiddest, submittedValueOldest, "
						+ " maker, makerTimestamp " 
						+ " from IfcEnsureBaasDataTmp "
					+ " where  legalEntityCode =:legalEntityCode"
					+ " and referenceNo =:referenceNo "
					+ " and auditTypeCode =:auditTypeCode "
					+ " and auditId =:auditId ") 
			 
				.setParameter("legalEntityCode", auditLevelDocumentDetails.getLegalEntityCode() )
				.setParameter("referenceNo",  auditLevelDocumentDetails.getReferenceNo())
				.setParameter("auditTypeCode",  auditLevelDocumentDetails.getAuditTypeCode())
				.setParameter("auditId",  auditLevelDocumentDetails.getAuditId())
				.executeUpdate();
			
			//Delete From
			session.createQuery("  Delete From "
					+ " IfcEnsureBaasIRProcessData  " 
					+ " where  legalEntityCode =:legalEntityCode"					 
					+ " and auditTypeCode =:auditTypeCode "
					+ " and auditId =:auditId " ) 
			
				.setParameter("legalEntityCode", auditLevelDocumentDetails.getLegalEntityCode())				
				.setParameter("auditTypeCode",  auditLevelDocumentDetails.getAuditTypeCode())
				.setParameter("auditId",  auditLevelDocumentDetails.getAuditId()) 
				.executeUpdate();
			
			// Insert into IR Process table	
			// IRPrcData.DPI => Latest Values, always Year 3  
			// IRPrcData.DLI => Based on frequency, for RRB It is always Year 2 (Midest value)
			//if(auditLevelDocumentDetails.getAuditTypeCode().equalsIgnoreCase(BankAuditConstant.INSPECTION_TYPE_DCCB)){
			if(auditLevelDocumentDetails.getAuditTypeCode().startsWith(BankAuditConstant.INSPECTION_TYPE_DCCB_CONTAIN)){	
				
				//get Previous year Inspection Id
				/*AuditSchedule preAuditSchedule = getPreviousYearInspection(auditLevelDocumentDetails.getLegalEntityCode(), auditLevelDocumentDetails.getAuditTypeCode(),  auditLevelDocumentDetails.getAuditId() );
				AuditSchedule latestAuditSchedule = getLatestInspection(auditLevelDocumentDetails.getLegalEntityCode(), auditLevelDocumentDetails.getAuditTypeCode(),  auditLevelDocumentDetails.getAuditId()) ;
				*/
				
				String latestValue = getBiAnnualInspectionType(auditLevelDocumentDetails.getLegalEntityCode(), auditLevelDocumentDetails.getAuditTypeCode(), 
						auditLevelDocumentDetails.getAuditId(), "BOMD610");
				logger.info("latestValue .. "+latestValue);
				StringBuilder qry = new StringBuilder();
				qry.append(" Insert into "
						+ " IfcEnsureBaasIRProcessData("
							+ " legalEntityCode, auditTypeCode, auditId, "
							+ " referenceNo,   unitCode, parameterNameBaas, parameterNameNabard, parameterName, "
							+ " submittedValueLatest, submittedValueOldest ) "
						+ " select "
							+ " legalEntityCode, auditTypeCode, auditId, "
							+ " referenceNo,   unitCode, parameterNameBaas, parameterNameNabard, parameterName, ");
				
				if("2".equalsIgnoreCase(latestValue)){  // !BankAuditUtil.isEmptyString(latestValue) || 
					qry.append(" submittedValueLatest,  submittedValueOldest ");
				}else{
					qry.append(" submittedValueLatest,  submittedValueMiddest ");
				}
				/*if(preAuditSchedule!=null){
					//int preFinancialYear = preAuditSchedule.getFinancialYear().getYear();
					Calendar preYear =  new GregorianCalendar();
					preYear.setTime(preAuditSchedule.getFinancialYear()); 
					
					Calendar latestYear =  new GregorianCalendar();
					latestYear.setTime(latestAuditSchedule.getFinancialYear()); 
					
					Integer inspectionDiff = latestYear.get(Calendar.YEAR)  - preYear.get(Calendar.YEAR);
					
					if(inspectionDiff >= 2) {
						qry.append(" submittedValueLatest,  submittedValueOldest ");
					}else{
						qry.append(" submittedValueLatest,  submittedValueMiddest ");
					}
					
				}else{
						qry.append(" submittedValueLatest,  submittedValueMiddest ");
				}*/
				
				qry.append(" from IfcEnsureBaasDataTmp "
					+ " where  legalEntityCode =:legalEntityCode"
					+ " and referenceNo =:referenceNo "
					+ " and auditTypeCode =:auditTypeCode "
					+ " and auditId =:auditId ");
				
				logger.info("Qry is .. "+qry);
				
				session.createQuery(qry.toString())
				.setParameter("legalEntityCode", auditLevelDocumentDetails.getLegalEntityCode() )
				.setParameter("referenceNo",  auditLevelDocumentDetails.getReferenceNo())
				.setParameter("auditTypeCode",  auditLevelDocumentDetails.getAuditTypeCode())
				.setParameter("auditId",  auditLevelDocumentDetails.getAuditId())
				.executeUpdate();
				
			}else{
				session.createQuery(" Insert into "
					+ " IfcEnsureBaasIRProcessData("
						+ " legalEntityCode, auditTypeCode, auditId, "
						+ " referenceNo,   unitCode, parameterNameBaas, parameterNameNabard, parameterName, "
						+ " submittedValueLatest, submittedValueOldest ) "
					+ " select "
						+ " legalEntityCode, auditTypeCode, auditId, "
						+ " referenceNo,   unitCode, parameterNameBaas, parameterNameNabard, parameterName, "
						+ " submittedValueLatest,  submittedValueMiddest " 
						+ " from IfcEnsureBaasDataTmp "
					+ " where  legalEntityCode =:legalEntityCode"
					+ " and referenceNo =:referenceNo "
					+ " and auditTypeCode =:auditTypeCode "
					+ " and auditId =:auditId ") 
			 
				.setParameter("legalEntityCode", auditLevelDocumentDetails.getLegalEntityCode() )
				.setParameter("referenceNo",  auditLevelDocumentDetails.getReferenceNo())
				.setParameter("auditTypeCode",  auditLevelDocumentDetails.getAuditTypeCode())
				.setParameter("auditId",  auditLevelDocumentDetails.getAuditId())
				.executeUpdate();
			}
			
			
			
			
			//Delete from Temp
			session.createQuery("  Delete From "
					+ " IfcEnsureBaasDataTmp  " 
					+ " where  legalEntityCode =:legalEntityCode"					 
					+ " and auditTypeCode =:auditTypeCode "
					+ " and auditId =:auditId " ) 
			
				.setParameter("legalEntityCode", auditLevelDocumentDetails.getLegalEntityCode())				
				.setParameter("auditTypeCode",  auditLevelDocumentDetails.getAuditTypeCode())
				.setParameter("auditId",  auditLevelDocumentDetails.getAuditId()) 
				.executeUpdate();

		}catch (Exception e) {
			logger.error(e.getStackTrace().toString());
			System.out.println(e +" ====================== ");
			return BankAuditConstant.FAILED;
		}
		return BankAuditConstant.SUCCESS;
	}	 
	
	@Override
	public void deleteDataFromTmp(AuditLevelDocumentDetails auditLevelDocumentDetails){
		Session session = getSession();
		session.createQuery("  Delete From "
				+ " IfcEnsureBaasDataTmp  " 
				+ " where  legalEntityCode =:legalEntityCode"					 
				+ " and auditTypeCode =:auditTypeCode "
				+ " and auditId =:auditId " ) 
		
			.setParameter("legalEntityCode", auditLevelDocumentDetails.getLegalEntityCode())				
			.setParameter("auditTypeCode",  auditLevelDocumentDetails.getAuditTypeCode())
			.setParameter("auditId",  auditLevelDocumentDetails.getAuditId()) 
			.executeUpdate();
	}
	
	@Override
	public List<AuditLevelDocumentDetails> getAuditLevelDocuments(Integer legalEntityCode, String auditTypeCode,
			  String auditId, String planId,  String documentType, String role){
		logger.info("legalEntityCode=>"+legalEntityCode+"auditTypeCode"+auditTypeCode+"auditId"+auditId+"planId"+planId+"");
		
		List<String> rolesListInspector = Arrays.asList(("G2,G8").split(","));  	//Inspection
		List<String> rolesListCompliance = Arrays.asList(("G3,G31").split(","));	//Compliance or Response
		List<String> rolesListReviewer = Arrays.asList(("G4,G7").split(","));       //review/Scrutiny	
		
		Session session = getSession();
		session.setDefaultReadOnly(true);
		StringBuilder queryString=new StringBuilder(" Select doc from "
				+ " AuditLevelDocumentDetails doc  "
				+ " where "
				+ " doc.legalEntityCode =:legalEntityCode "					 
				+ " and doc.auditTypeCode =:auditTypeCode "
				+ " and doc.auditId =:auditId "
				+ " and doc.planId =:planId "
				+ " and doc.uploadStatus=:uploadStatus ");
		if(!BankAuditUtil.isEmptyString(role)){
			queryString.append(" and doc.userRoleId in (:roleList)  ");
		}
		if(!BankAuditUtil.isEmptyString(documentType)){
			queryString.append("and doc.documentType=:documentType");
		}
		
		Query query= session.createQuery(queryString.toString())
		 .setParameter("legalEntityCode", legalEntityCode)
		 .setParameter("auditTypeCode", auditTypeCode)
		 .setParameter("auditId", auditId)
		 .setParameter("planId", planId)	
		 .setParameter("uploadStatus", BankAuditConstant.SUCCESS);
		if(!BankAuditUtil.isEmptyString(documentType)){
			query.setParameter("documentType", documentType);
		}
		if(!BankAuditUtil.isEmptyString(role)){
			if(rolesListInspector.contains(role)){
				query.setParameterList("roleList", rolesListInspector);
			}else if(rolesListCompliance.contains(role)){
				query.setParameterList("roleList", rolesListCompliance);
			}else if(rolesListReviewer.contains(role)){ 
				query.setParameterList("roleList", rolesListReviewer);
			} 	
		}
		return query.list();		
	}
	
	@Override
	public List<String>  checkDuplicateAuditLevelDocuments(Integer legalEntityCode, String auditTypeCode,
			  String auditId, String planId,  String documentType, String documentSubType, String documentFetchType, List<String> filesNamesList){
		Session session =getSession();		
		session.setDefaultReadOnly(true);		
		
		StringBuilder queryStr = new StringBuilder(" select   fileName "
				+ " from AuditLevelDocumentDetails where "
				+ " uploadStatus =:uploadStatus "
				+ " and legalEntityCode =:legalEntityCode"
				+ " and auditTypeCode =:auditTypeCode "
				+ " and planId =:planId "
				+ " and auditId =:auditId " 
				+ " and documentType =:documentType ");
		if(!BankAuditUtil.isEmptyString(documentSubType)){
			queryStr.append(" and documentSubType = :documentSubType ");
		} 
		if(!BankAuditUtil.isEmptyString(documentFetchType)){
			queryStr.append(" and documentFetchType = :documentFetchType ");
		} 		
		if(filesNamesList!=null && !filesNamesList.isEmpty() && filesNamesList.size()>0){
			queryStr.append(" and fileName in ( :fileName ) ");
		}
		
		Query query = session.createQuery(queryStr.toString())
				.setParameter("uploadStatus", BankAuditConstant.SUCCESS)
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("planId", planId)
				.setParameter("auditId", auditId) 
				.setParameter("documentType", documentType);
		if(!BankAuditUtil.isEmptyString(documentSubType)){
			query.setParameter("documentSubType", documentSubType);
		}
		if(!BankAuditUtil.isEmptyString(documentFetchType)){
			query.setParameter("documentFetchType", documentFetchType);
		}		
		if(filesNamesList!=null && !filesNamesList.isEmpty() && filesNamesList.size()>0){
			query.setParameterList("fileName", filesNamesList);
		}			
		return query.list();
		
	}
	
	
	@Override
	public String deleteDuplicateDocument(Integer legalEntityCode, String auditTypeCode,
			  String auditId, String planId,  String documentType, String documentSubType, String documentFetchType, List<String> filesNamesList ){
		logger.info("Inside deleteDuplicateDocument .." );
		try{
			System.out.println(filesNamesList.toString());
			Session session =getSession();	 	 
			StringBuilder queryStr =  new StringBuilder("Update AuditLevelDocumentDetails doc "
					+ " set doc.uploadStatus='"+BankAuditConstant.STATUS_DEL+"' " 
					+ " where doc.legalEntityCode =:legalEntityCode "
					+ " and doc.auditTypeCode =:auditTypeCode "
					+ " and doc.planId =:planId "
					+ " and doc.auditId =:auditId "					
					+ " and doc.documentType =:documentType "
				 	+ " and doc.fileName in (:fileName)  ");
			
			if(!BankAuditUtil.isEmptyString(documentSubType)){
				queryStr.append(" and documentSubType = :documentSubType ");
			} 
			if(!BankAuditUtil.isEmptyString(documentFetchType)){
				queryStr.append(" and documentFetchType = :documentFetchType ");
			} 	
			
			Query query = session.createQuery(queryStr.toString())			
			.setParameter("legalEntityCode", legalEntityCode)
			.setParameter("auditTypeCode", auditTypeCode)
			.setParameter("planId", planId)
			.setParameter("auditId", auditId)
			.setParameter("documentType", documentType)  
			.setParameterList("fileName", filesNamesList);
			if(!BankAuditUtil.isEmptyString(documentSubType)){
				query.setParameter("documentSubType", documentSubType);
			}
			if(!BankAuditUtil.isEmptyString(documentFetchType)){
				query.setParameter("documentFetchType", documentFetchType);
			}			
			query.executeUpdate();	
			 
			return BankAuditConstant.SUCCESS;
			
		}catch (Exception e) {
			logger.error("Error .." + e.getMessage() +" :: "+e.getCause());
			return BankAuditConstant.FAILED;
		}
	}
	
	@Override
	public   String deletePdfDocument(Integer legalEntityCode, String auditTypeCode,  String auditId,  String planId, String documentType, Integer id){
		logger.info("Inside deletePdfDocument .." );
		try{ 
			Session session =getSession();	 	 
			
			  session.createQuery(""
					+ " Update AuditLevelDocumentDetails doc "
					+ " set doc.uploadStatus='"+BankAuditConstant.STATUS_DEL+"' " 
					+ " where doc.legalEntityCode =:legalEntityCode "
					+ " and doc.auditTypeCode =:auditTypeCode "
					+ " and doc.planId =:planId "
					+ " and doc.auditId =:auditId "					
					+ " and doc.documentType =:documentType"
					+ " and doc.id=:id " 
					+ " ") 
			.setParameter("legalEntityCode", legalEntityCode)
			.setParameter("auditTypeCode", auditTypeCode)
			.setParameter("planId", planId)
			.setParameter("auditId", auditId)
			.setParameter("documentType", documentType) 
			.setParameter("id", id)
			.executeUpdate();	
			 
			return BankAuditConstant.SUCCESS;
			
		}catch (Exception e) {
			logger.error("Error .." + e.getMessage() +" :: "+e.getCause());
			return BankAuditConstant.FAILED;
		}
	}	
	
	@Override
	public String getDocReferencePath(Integer legalEntityCode, String auditTypeCode,  String auditId, String documentType, Integer id){
		Session session = getSession();
		session.setDefaultReadOnly(true);
		
		String queryString="  Select referencePath from "
				+ " AuditLevelDocumentDetails doc  "
				+ " where "
				+ " doc.legalEntityCode =:legalEntityCode"					 
				+ " and doc.auditTypeCode =:auditTypeCode "
				+ " and doc.auditId =:auditId" 
				+ " and doc.documentType=:documentType "
				+ " and doc.id=:id ";
		
		Query query= session.createQuery(queryString.toString())
		 .setParameter("legalEntityCode", legalEntityCode)
		 .setParameter("auditTypeCode", auditTypeCode)
		 .setParameter("auditId", auditId)
		 .setParameter("documentType", documentType)
		 .setParameter("id", id);
		
		return (String) query.uniqueResult() ;
	}
	
	
	@Override
	 public Map<String, String[]> getParameterMappingVelues(Integer legalEntityCode, String auditId, String auditTypeCode){
		 Session session = getSession();		    
		 session.setDefaultReadOnly(true);
		 
		 String irParameterStr= " from IfcEnsureBaasIRProcessData  "  
	  				+ " where legalEntityCode =:legalEntityCode "
	  				+ " and auditTypeCode =:auditTypeCode "
	  				+ " and auditId =:auditId "  ;
		 
		 Query irParameter = session.createQuery(irParameterStr)
	  				.setParameter("legalEntityCode", legalEntityCode)
	  				.setParameter("auditTypeCode", auditTypeCode)
	  				.setParameter("auditId", auditId);
		 
		 List<IfcEnsureBaasIRProcessData> listIfcEnsureBaasIRProcessData = irParameter.list();
		 Map<String, String[]> parameterMap = new HashMap<String, String[]>();
		 for (IfcEnsureBaasIRProcessData ifcEnsureBaasIRProcessData : listIfcEnsureBaasIRProcessData) {
			 String [] parameterValues =  {ifcEnsureBaasIRProcessData.getSubmittedValueOldest(), ifcEnsureBaasIRProcessData.getSubmittedValueLatest()};
			 parameterMap.put(ifcEnsureBaasIRProcessData.getParameterNameBaas(),parameterValues);
		 }
		 return parameterMap;
	 }
	
	@Override
	public DataTableResponse viewOfflineInspection(Integer id, Integer legalEntityCode, String auditTypeCode,  String auditId, String search,
 			Integer orderColumn, String orderDirection, Integer page, Integer size){
		 
		Session session = getSession();
		session.setDefaultReadOnly(true); 	
		
		DataTableResponse dataTableResponse=new DataTableResponse();
		StringBuilder queryString=new StringBuilder();
		
		queryString=queryString.append(" select checklist From ChecklistOfflineInspection checklist  "
				+ " Inner join AuditLevelDocumentDetails docs on checklist.docId=docs.id "
				+ " and checklist.legalEntityCode = docs.legalEntityCode"
				+ " and checklist.auditId = docs.auditId "
				+ " and checklist.auditTypeCode = docs.auditTypeCode"
				+ " where "
				+ " docs.legalEntityCode =:legalEntityCode"					 
				+ " and docs.auditTypeCode =:auditTypeCode "
				+ " and docs.auditId =:auditId" 
				+ " and docs.id=:id ");
		
		if(!BankAuditUtil.isEmptyString(search)){			
			queryString=queryString.append("");		
		}
		
		String columns[]={
				 "chapter",
				 "section",
				 "product",
				 "process",
				 "checklist"
		};
		
	 
		if(orderColumn != null && orderColumn == -1 && !BankAuditUtil.isEmptyString(orderDirection)){ // -1 send for the first time from the UI during landing page.
			queryString=queryString.append(" ORDER BY checklist.observationId ASC ");
		}else if(orderColumn!=null && !BankAuditUtil.isEmptyString(orderDirection)){
			queryString.append("  ORDER BY ").append(columns[orderColumn]).append(" ").append(orderDirection);		
		}else {
			queryString.append(" ORDER BY checklist.observationId  ASC ");
		}
		
		Query query=(Query) session.createQuery(queryString.toString()) 
				.setParameter("legalEntityCode", legalEntityCode)
				   .setParameter("auditTypeCode", auditTypeCode)
				   .setParameter("auditId", auditId)
				   .setParameter("id", id);
		
		if(!BankAuditUtil.isEmptyString(search)){
			query.setParameter("search", "%"+search+"%");
		}
		
		List<ChecklistOfflineInspection> tmpchecklistOfflineInspection =query.list(); 
		Long totalRecords=null;
		try {
			totalRecords = (long)tmpchecklistOfflineInspection.size();
		}catch(Exception e) {
			totalRecords = (long)0;
		}
		 
		List<ChecklistOfflineInspection> checklistOfflineInspectionList=query.setFirstResult((page*size)).setMaxResults(size).list();
		
		dataTableResponse.setData(checklistOfflineInspectionList);
		dataTableResponse.setRecordsTotal(totalRecords);
		dataTableResponse.setRecordsFiltered(totalRecords);
		
		return dataTableResponse;
		
	}
	

	private AuditSchedule getPreviousYearInspection(int legalEntityCode, String auditTypeCode,   String auditId ){
		
		logger.info("getPreviousYearInspection:: legalEntityCode.."+legalEntityCode +" auditTypeCode.."+auditTypeCode+" auditId.. "+ auditId);
		
		Session session =getSession();
		session.setDefaultReadOnly(true);		
		String strQry =" from AuditSchedule preAudit where "
						+ " preAudit.legalEntityCode=:legalEntityCode " 		
						+ " and preAudit.auditTypeCode=:auditTypeCode " 
						+ " and preAudit.auditId = (Select latestAudit.prevAuditId from AuditSchedule latestAudit where "
										+ " latestAudit.legalEntityCode=:legalEntityCode "
										+ " and latestAudit.auditId= :auditId "
										+ " and latestAudit.auditTypeCode=:auditTypeCode "
										+ "  ) ";
		
		Query qry =session.createQuery(strQry);
		qry.setParameter("legalEntityCode", legalEntityCode);
		qry.setParameter("auditTypeCode",auditTypeCode); 
		qry.setParameter("auditId", auditId);	 
		 
		return (AuditSchedule)qry.uniqueResult(); 
	}
	
	private AuditSchedule getLatestInspection(int legalEntityCode, String auditTypeCode,   String auditId ){
		
		logger.info("getLatestInspection:: legalEntityCode.."+legalEntityCode +" auditTypeCode.."+auditTypeCode+" auditId.. "+ auditId);
		
		Session session =getSession();
		session.setDefaultReadOnly(true);		
		String strQry =" from AuditSchedule   where "
						+ " legalEntityCode =:legalEntityCode " 		
						+ " and auditTypeCode =:auditTypeCode " 
						+ " and auditId =:auditId  ";
		
		Query qry =session.createQuery(strQry);
		qry.setParameter("legalEntityCode", legalEntityCode);
		qry.setParameter("auditTypeCode",auditTypeCode); 
		qry.setParameter("auditId", auditId);	 
		 
		return (AuditSchedule)qry.uniqueResult(); 
	}
	
	private String getBiAnnualInspectionType(int legalEntityCode, String auditTypeCode, String auditId, String bomId ){
		
		logger.info("getBiAnnualInspectionType:: legalEntityCode.."+legalEntityCode +" auditTypeCode.."+auditTypeCode+" auditId.. "+ auditId);
		
		Session session =getSession();
		session.setDefaultReadOnly(true);		
		String strQry =" Select submittedValueLatest from IfcEnsureBaasDataTmp   where "
						+ " legalEntityCode =:legalEntityCode " 		
						+ " and auditTypeCode =:auditTypeCode " 
						+ " and auditId =:auditId "
						+ " and parameterNameNabard = :bomId ";		
		Query qry =session.createQuery(strQry);
		qry.setParameter("legalEntityCode", legalEntityCode)
			.setParameter("auditTypeCode",auditTypeCode)
			.setParameter("auditId", auditId)
			.setParameter("bomId", bomId);	 
		 
		return (String)qry.uniqueResult(); 
		
		 
	}
	
	/*******************/
	
	@Override    
	public  List<AuditLevelDocumentDetails> getAuditLevelPdfDocument(Integer legalEntityCode, String auditTypeCode,
			  String auditId,  String documentType){
		 
		Session session = getSession();
		session.setDefaultReadOnly(true);
		
		String queryString="  Select doc from "
				+ " AuditLevelDocumentDetails doc  "
				+ " where "
				+ " doc.legalEntityCode =:legalEntityCode"					 
				+ " and doc.auditTypeCode =:auditTypeCode "
				+ " and doc.auditId =:auditId" 
				+ " and doc.documentType=:documentType "
				+ " and doc.uploadStatus=:uploadStatus ";
		Query query= session.createQuery(queryString.toString())
		 .setParameter("legalEntityCode", legalEntityCode)
		 .setParameter("auditTypeCode", auditTypeCode)
		 .setParameter("auditId", auditId)
		 .setParameter("documentType", documentType)
		 .setParameter("uploadStatus", BankAuditConstant.SUCCESS);
		
		return query.list();
		
	 }
	
	@Override
	public  List<AuditLevelDocumentDetails> getAuditLevelCSVDocument(Integer legalEntityCode, String auditTypeCode,
			  String auditId,  String documentType){
		
			Session session = getSession();
			session.setDefaultReadOnly(true);
			
			String queryString="  Select doc from "
					+ " AuditLevelDocumentDetails doc  "
					+ " where "
					+ " doc.legalEntityCode =:legalEntityCode"					 
					+ " and doc.auditTypeCode =:auditTypeCode "
					+ " and doc.auditId =:auditId" 
					+ " and doc.documentType=:documentType "
					+ " and doc.uploadStatus=:uploadStatus ";
			Query query= session.createQuery(queryString.toString())
			 .setParameter("legalEntityCode", legalEntityCode)
			 .setParameter("auditTypeCode", auditTypeCode)
			 .setParameter("auditId", auditId)
			 .setParameter("documentType", documentType)
			 .setParameter("uploadStatus", BankAuditConstant.SUCCESS);
			
			return query.list();
	 }
	
	public DataTableResponse viewAuditLevelCSVDocument(Integer legalEntityCode, String auditTypeCode,  String auditId, String stageOfFile, Integer referenceNo,
	    		String search, Integer orderColumn, String orderDirection, Integer page, Integer size){
		
		Session session = getSession();
		session.setDefaultReadOnly(true); 

		DataTableResponse dataTableResponse=new DataTableResponse();
		StringBuilder queryString=new StringBuilder();
		
		queryString=queryString.append("  Select "
				/*+ " parameterName, "
				+ " parameterNameNabard, "
				+ " submittedValueLatest, "
				+ " submittedValueMiddest, "
				+ " submittedValueOldest "*/
				+ " ifcData "
				+ " from IfcEnsureBaasData ifcData "
				+ " where "
				+ " legalEntityCode =:legalEntityCode"					 
				+ " and auditTypeCode =:auditTypeCode "
				+ " and auditId =:auditId"
				+ " and stageOfFile=:stageOfFile "
				+ " and referenceNo=:referenceNo ");
		
		if(!BankAuditUtil.isEmptyString(search)){
			
			queryString=queryString.append(" ( "
					+ " or parameterName like :search "
					+ " or  parameterNameNabard  like :search "
					+ " or  submittedValueLatest  like :search"
					+ " or  submittedValueMiddest  like :search  "
					+ " or  submittedValueOldest  like :search "
					+ " ) ");		
		}
		
		String columns[]={
				 "parameterName",
				 "parameterNameNabard",
				 "submittedValueLatest",
				 "submittedValueMiddest",
				 "submittedValueOldest"
		};
		
	 
		if(orderColumn != null && orderColumn == -1 && !BankAuditUtil.isEmptyString(orderDirection)){ // -1 send for the first time from the UI during landing page.
			queryString=queryString.append(" ORDER BY parameterName ASC ");
		}else if(orderColumn!=null && !BankAuditUtil.isEmptyString(orderDirection)){
			queryString.append("  ORDER BY ").append(columns[orderColumn]).append(" ").append(orderDirection);		
		}else {
			queryString.append(" ORDER BY parameterName  ASC ");
		}
		
		Query query=(Query) session.createQuery(queryString.toString()) 	
				/*.setResultTransformer(new ResultTransformer() {
					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {					
						return null;
					}
					@Override
					public List transformList(List collection) {
						return collection;
					}
				})*/.setParameter("legalEntityCode", legalEntityCode)
				   .setParameter("auditTypeCode", auditTypeCode)
				   .setParameter("auditId", auditId)
				   .setParameter("stageOfFile", stageOfFile)
				   .setParameter("referenceNo", referenceNo);
		
		if(!BankAuditUtil.isEmptyString(search)){
			query.setParameter("search", "%"+search+"%");
		}
		
		List<IfcEnsureBaasData> tmpIfcEnsureBaasData =query.list(); 
		Long totalRecords=null;
		try {
			totalRecords = (long)tmpIfcEnsureBaasData.size();
		}catch(Exception e) {
			totalRecords = (long)0;
		}
		 
		List<IfcEnsureBaasData> IfcEnsureBaasDataList=query.setFirstResult((page*size)).setMaxResults(size).list();
		
		dataTableResponse.setData(IfcEnsureBaasDataList);
		dataTableResponse.setRecordsTotal(totalRecords);
		dataTableResponse.setRecordsFiltered(totalRecords);
		
		return dataTableResponse;
		
	 }
	
	/******** Offline Observation New code Start*******/
	@Override
	public void updateReportStandardStmtFormOfflineUpload(Integer legalEntityCode,  String auditId,  String auditTypeCode, 
			String reportLanguage, Integer docId, List<Integer> idsList){
		
		String sqlQuery=" update audit_report_stmt rpt inner join checklist_offline_inspection_tmp chklist    "
				 +"     on rpt.audit_type_code = chklist.audit_type_code "
				+"		and rpt.audit_id = chklist.audit_id "
				+"		and rpt.legal_entity_code = chklist.legal_entity_code "
				+"		and rpt.mapping_id = chklist.mapping_id "
				+"		and (rpt.compliance_Category is null OR rpt.compliance_Category = '' OR  rpt.compliance_Category = 'null' ) "
				+"		and rpt.applicable_for = :applicableFor "
				+"		and rpt.statement_type = :statementType  "  
				+"		and chklist.audit_type_code = :auditTypeCode "
				+"		and chklist.audit_id = :auditId "
				+"		and chklist.legal_entity_code = :legalEntityCode  "
				+" 		and chklist.doc_id = :docId"
				+" 		and chklist.update_status =  :updateStatus "
				+" 		and rpt.id in  (:idsList) "
				+ "		set  "
				+" 		rpt.compliance_category = chklist.compliance_status, "
				+" 		rpt.statement = chklist.standard_stmt , "
				+"		rpt.statement_hindi = chklist.standard_stmt_hindi,"
				+ "		rpt.non_compliance_persistence = chklist.non_compliance_persistence   ";
		 		 
		getSession().createSQLQuery(sqlQuery)
			.setParameter("auditTypeCode", auditTypeCode)
			.setParameter("auditId", auditId)
			.setParameter("legalEntityCode", legalEntityCode)			
			.setParameter("applicableFor", BankAuditConstant.FOR_CHECKLIST) 
			.setParameter("statementType", BankAuditConstant.FOR_STANDARD_STMT)
			.setParameter("docId", docId) 
			.setParameter("updateStatus", BankAuditConstant.SUCCESS) 
			.setParameterList("idsList", idsList)  
			.executeUpdate();	
		   
	}
	
	@Override
	public void updateReportRemarkFormOfflineUpload(Integer legalEntityCode,  String auditId,  String auditTypeCode, String reportLanguage,
			Integer docId, List<Integer> idsList, String statementType, String role){
		 
		List<String> rolesListInspector = Arrays.asList(("G2,G8").split(","));  	//Inspection
		List<String> rolesListCompliance = Arrays.asList(("G3,G31").split(","));	//Compliance or Response
		List<String> rolesListReviewer = Arrays.asList(("G4,G7").split(","));       //review/Scrutiny	
		
		StringBuilder sqlQuery= new StringBuilder(" update audit_report_stmt rpt inner join checklist_offline_inspection_tmp chklist    "
				+ "		set  "
				+" 		rpt.compliance_category = chklist.compliance_status, "
				+ "		rpt.non_compliance_persistence = chklist.non_compliance_persistence,  ");
		if(rolesListInspector.contains(role)){		
				if(BankAuditConstant.REPORT_LANGUAGE_EN.equalsIgnoreCase(reportLanguage)){
					sqlQuery.append(" statement = chklist.io_comments  ");
				}else{
					sqlQuery.append(" statement_hindi = chklist.io_comments   ");
				}
		}else if(rolesListCompliance.contains(role)){
			sqlQuery.append(" rpt.statement = chklist.response_review_remarks  "); 
		}else if(rolesListReviewer.contains(role)){
			sqlQuery.append( " rpt.statement = concat(SUBSTRING_INDEX(chklist.review_status_attribute, '-', -1)  ,':#@:',chklist.response_review_remarks) ");
		} 
		 
		sqlQuery.append(" where rpt.audit_type_code = chklist.audit_type_code "
				+"		and rpt.audit_id = chklist.audit_id "
				+"		and rpt.legal_entity_code = chklist.legal_entity_code "
				+"		and rpt.mapping_id = chklist.mapping_id " );
		if(rolesListInspector.contains(role)){
			sqlQuery.append(" and (rpt.compliance_Category is null OR rpt.compliance_Category = ''  OR  rpt.compliance_Category = 'null' ) ");
		}
		sqlQuery.append(" and rpt.applicable_for = :applicableFor "
				+"		and rpt.statement_type = :statementType  "  
				+"		and chklist.audit_type_code = :auditTypeCode "
				+"		and chklist.audit_id = :auditId "
				+"		and chklist.legal_entity_code = :legalEntityCode   "
				+" 		and chklist.doc_id = :docId "
				+" 		and chklist.update_status =  :updateStatus "
				+" 		and rpt.id in  (:idsList) " );
		
		getSession().createSQLQuery(sqlQuery.toString())
			 		.setParameter("auditTypeCode", auditTypeCode)
			 		.setParameter("auditId", auditId)
			 		.setParameter("legalEntityCode", legalEntityCode)
			 		.setParameter("applicableFor", BankAuditConstant.FOR_CHECKLIST) 
			 		.setParameter("statementType", statementType)	
			 		.setParameter("docId", docId) 
			 		.setParameter("updateStatus", BankAuditConstant.SUCCESS) 
			 		.setParameterList("idsList", idsList)  
			 		.executeUpdate();		   
	} 
	
	@Override
	public void updateResponseReviewRemarksFormOfflineUpload(Integer legalEntityCode,  String auditId,  String auditTypeCode, 
			Integer docId ,  String role, String maker){
		    
		List<String> rolesListCompliance = Arrays.asList(("G3,G31").split(","));	//Compliance or Response
		List<String> rolesListReviewer = Arrays.asList(("G4,G7").split(","));       //review/Scrutiny	
		
		StringBuilder sqlQuery= new StringBuilder(" update audit_observation_respond_review_capture respReviewCapture inner join checklist_offline_inspection_tmp chklist    "
				+ "	SET respReviewCapture.maker = :maker,"
				+ " respReviewCapture.capture_type  = :role, "
				+ " respReviewCapture.comment  = chklist.response_review_remarks "
				+ "  ");
		if(rolesListReviewer.contains(role)){
			sqlQuery.append( ", respReviewCapture.review_respond_status = SUBSTRING_INDEX(chklist.review_status_attribute, '-', -1) ");
		} 
		 
		sqlQuery.append(" where respReviewCapture.legal_entity_code = chklist.legal_entity_code"
				+ " AND respReviewCapture.audit_type_code = chklist.audit_type_code "
				+ "	AND respReviewCapture.audit_id = chklist.audit_id "
				+ " AND respReviewCapture.observation_id = chklist.observation_id"
				+ " AND respReviewCapture.group_observation_id = chklist.group_observation_id "
				+ " AND respReviewCapture.status = :pendingAuth " );
		 		
		sqlQuery.append(" AND chklist.legal_entity_code = :legalEntityCode   " 
				+"		AND chklist.audit_type_code = :auditTypeCode "
				+"		AND chklist.audit_id = :auditId "				
				+" 		AND chklist.doc_id = :docId "
				+" 		AND chklist.update_status =  :updateStatus "
				+"		AND capture_type in (:roleList)" );
		
	  Query qry = getSession().createSQLQuery(sqlQuery.toString())			 		
			 		.setParameter("legalEntityCode", legalEntityCode)
			 		.setParameter("auditTypeCode", auditTypeCode)
			 		.setParameter("auditId", auditId)
			 		.setParameter("docId", docId) 
			 		.setParameter("updateStatus", BankAuditConstant.SUCCESS) 
			 		.setParameter("pendingAuth", BankAuditConstant.STATUS_PENDING_AUTH)
			 		.setParameter("role", role) 
			 		.setParameter("maker", maker); 
			 		
	 		if(rolesListCompliance.contains(role)){
				qry.setParameterList("roleList", rolesListCompliance);
			}else if(rolesListReviewer.contains(role)){ 
				qry.setParameterList("roleList", rolesListReviewer);
			} 			 		
	 		qry.executeUpdate();		   
	} 
	
	@Override
	public void insertResponseReviewRemarksFormOfflineUpload(Integer legalEntityCode, String auditTypeCode,  String auditId, String planId, 
			Integer docId , String role, String maker, List<String> idsResponseReviewRemarks){ 
		
		StringBuilder sqlQuery= new StringBuilder(" INSERT INTO  audit_observation_respond_review_capture "
				+ " (legal_entity_code, audit_type_code, plan_id, audit_id, group_observation_id, "
						+ " observation_id, comment, "
						+ " capture_type, review_respond_status, status, maker) "
				+ " Select chklist.legal_entity_code, chklist.audit_type_code, '"+planId+"', chklist.audit_id, chklist.group_observation_id, "
						+ " chklist.observation_id, chklist.response_review_remarks, "
						+ " '"+role+"' ,SUBSTRING_INDEX(chklist.review_status_attribute, '-', -1), "
						+ " '"+BankAuditConstant.STATUS_PENDING_AUTH+"', '"+maker+"' "
				+ " From checklist_offline_inspection_tmp chklist "); 
		 		
		sqlQuery.append(" WHERE chklist.legal_entity_code = :legalEntityCode   " 
				+" AND chklist.audit_type_code = :auditTypeCode "
				+" AND chklist.audit_id = :auditId "				
				+" AND chklist.doc_id = :docId "
				+" AND chklist.update_status =  :updateStatus ");
		if(idsResponseReviewRemarks != null && idsResponseReviewRemarks.size()>0){
			sqlQuery.append(" AND concat(chklist.legal_entity_code,chklist.audit_type_code,chklist.audit_id,chklist.group_observation_id,chklist.observation_id)"
					+ "  NOT IN (:idsResponseReviewRemarks) " );
		}
	  Query qry = getSession().createSQLQuery(sqlQuery.toString())			 		
			 		.setParameter("legalEntityCode", legalEntityCode)
			 		.setParameter("auditTypeCode", auditTypeCode)
			 		.setParameter("auditId", auditId)
			 		.setParameter("docId", docId) 
			 		.setParameter("updateStatus", BankAuditConstant.SUCCESS); 
		  if(idsResponseReviewRemarks != null && idsResponseReviewRemarks.size()>0){
			  qry.setParameterList("idsResponseReviewRemarks", idsResponseReviewRemarks);
		  }	
	 	qry.executeUpdate();		   
	} 
	 
	@Override
	public void updateReportChecklistFormOfflineUpload(Integer legalEntityCode,  String auditId,  String auditTypeCode, String reportLanguage, 
			Integer docId, List<Integer> idsList){
		 
		StringBuilder sqlQuery= new StringBuilder(" update audit_report_stmt rpt inner join checklist_offline_inspection_tmp chklist    "
				+ "		set  "
				+" 		rpt.compliance_category = chklist.compliance_status,"
				+" 		rpt.non_compliance_persistence = chklist.non_compliance_persistence " 
				+"		where rpt.audit_type_code = chklist.audit_type_code "
				+"		and rpt.audit_id = chklist.audit_id "
				+"		and rpt.legal_entity_code = chklist.legal_entity_code "
				+"		and rpt.mapping_id = chklist.mapping_id "
				+"		and (rpt.compliance_Category is null OR rpt.compliance_Category = ''  OR  rpt.compliance_Category = 'null'  ) "
				+"		and rpt.applicable_for = :applicableFor "
				//+"		and rpt.statement_type = :statementType  "  
				+"		and chklist.audit_type_code = :auditTypeCode "
				+"		and chklist.audit_id = :auditId "
				+"		and chklist.legal_entity_code = :legalEntityCode   "
				+" 		and chklist.doc_id = :docId "
				+" 		and chklist.update_status =  :updateStatus "
				//+" 		and rpt.id in  (:idsList)"
				+ " ");
		
		getSession().createSQLQuery(sqlQuery.toString())
			 		.setParameter("auditTypeCode", auditTypeCode)
			 		.setParameter("auditId", auditId)
			 		.setParameter("legalEntityCode", legalEntityCode)
			 		.setParameter("applicableFor", BankAuditConstant.FOR_CHECKLIST) 
			 		//.setParameter("statementType", BankAuditConstant.FOR_CHECKLIST)	
			 		.setParameter("docId", docId) 
			 		.setParameter("updateStatus", BankAuditConstant.SUCCESS) 
			 		///.setParameterList("idsList", idsList)  
			 		.executeUpdate();
		   
	}
	
	@Override
	public void updateAuditObservationFormOfflineUpload(Integer legalEntityCode,  String auditId,  String auditTypeCode, String reportLanguage, Integer docId, String role, 
			String maker, String reviewStatus){
		
		List<String> rolesListInspector = Arrays.asList(("G2,G8").split(","));  	//Inspection
		List<String> rolesListCompliance = Arrays.asList(("G3,G31").split(","));	//Compliance or Response
		List<String> rolesListReviewer = Arrays.asList(("G4,G7").split(","));       //review/Scrutiny	 
		
		StringBuilder sqlQuery= new StringBuilder(" update audit_observation abr inner join checklist_offline_inspection_tmp chklist    "
				+ "		set  ");
				
		if(rolesListInspector.contains(role)){		
			sqlQuery.append(" 		abr.compliance_category = chklist.compliance_status, " 
					+" 		abr.observation_criticality = chklist.criticality, " 
					+" 		abr.finding_remarks  = chklist.io_comments,  " 
					+" 		abr.submission_status   = '"+BankAuditConstant.STATUS_PENDING_AUTH+"' ,  " 
					+" 		abr.non_compliance_persistence   = chklist.non_compliance_persistence,  "
					+"      abr.standard_statement = chklist.standard_stmt, "
					+" 		abr.standard_statement_print_flag = chklist.standard_stmt_print_flag " ) ;
		}else if(rolesListCompliance.contains(role)){ 
			sqlQuery.append(" abr.response_status =  '"+BankAuditConstant.STATUS_PENDING_AUTH+"' ,   ");
			sqlQuery.append(" "
					+ " abr.response_remarks = chklist.response_review_remarks, "
					+ " abr.response_owner = '"+maker+"' "); 
		}else if(rolesListReviewer.contains(role)){				
			 
			sqlQuery.append(" abr.review_status =  (CASE WHEN chklist.review_status = '"+BankAuditConstant.STATUS_AUTH+"' "
							+ " AND SUBSTRING_INDEX(chklist.review_status_attribute, '-', 1) <> '"+BankAuditConstant.STATUS_FURTHER_COMPLIANCE+"' "
								+ " THEN  '"+BankAuditConstant.STATUS_AUTH+"' "
						+ " WHEN chklist.review_status = '"+BankAuditConstant.STATUS_PENDING_AUTH+"' "
							+ " AND SUBSTRING_INDEX(chklist.review_status_attribute, '-', 1) <> '"+BankAuditConstant.STATUS_FURTHER_COMPLIANCE+"'"
							+ " AND  '"+reviewStatus+"'='"+BankAuditConstant.STATUS_SCRUTINY_SUBMITTED+"' "
								+ " THEN  '"+BankAuditConstant.STATUS_AUTH+"'  "						
						+ "	ELSE  '"+BankAuditConstant.STATUS_PENDING_AUTH+"' END) ,   ");			
			sqlQuery.append(" abr.response_status = (CASE WHEN SUBSTRING_INDEX(chklist.review_status_attribute, '-', 1) = '"+BankAuditConstant.STATUS_FURTHER_COMPLIANCE+"' "
					+ " AND '"+reviewStatus+"'='"+BankAuditConstant.STATUS_SCRUTINY_INITIATED+"' "
					+ " THEN  '"+BankAuditConstant.STATUS_REJ+"'"
					+ " ELSE  '"+BankAuditConstant.STATUS_AUTH+"' END), ");
			
			sqlQuery.append(" abr.review_status_attribute = SUBSTRING_INDEX(chklist.review_status_attribute, '-', 1),  "
					+ " abr.review_remarks =  chklist.response_review_remarks, "
					+ " abr.review_owner = '"+maker+"' "); 
		}  
				
		sqlQuery.append(" where abr.audit_type_code = chklist.audit_type_code "
				+"		and abr.audit_id = chklist.audit_id "
				+"		and abr.legal_entity_code = chklist.legal_entity_code "
				+"		and abr.mapping_id = chklist.mapping_id "
				+"		and abr.observation_id  = chklist.observation_id ");
		if(rolesListInspector.contains(role)){
			sqlQuery.append(" and (abr.compliance_Category is null OR abr.compliance_Category = ''  OR  abr.compliance_Category = 'null'  ) "	);	
		}
		sqlQuery.append(" and chklist.audit_type_code = :auditTypeCode "
				+"		and chklist.audit_id = :auditId "
				+"		and chklist.legal_entity_code = :legalEntityCode   "
				+" 		and chklist.doc_id = :docId "
				+" 		and chklist.update_status =  :updateStatus ");
		
		getSession().createSQLQuery(sqlQuery.toString())
			 		.setParameter("auditTypeCode", auditTypeCode)
			 		.setParameter("auditId", auditId)
			 		.setParameter("legalEntityCode", legalEntityCode)			 		
			 		.setParameter("docId", docId) 
			 		.setParameter("updateStatus", BankAuditConstant.SUCCESS)
			 		.executeUpdate();		   
	} 
	
	@Override
	public void insertIntoChecklistOfflineInspectionFromTempToMaster(Integer legalEntityCode,  String auditId,  String auditTypeCode, String reportLanguage, Integer docId){
		
		StringBuilder sqlQuery= new StringBuilder(" insert into "
				+ " checklist_offline_inspection("
						+ "	doc_id, legal_entity_code,  observation_id,  "
						+ "	audit_id, audit_type_code, mapping_id, "
					    + " unit_code, chapter,  section, "
					    + " product,  process,  checklist, "
					    + " checklist_code,  compliance_status,  criticality, "
					    + " io_comments, submission_status,  update_status, "
					    + " update_status_message, non_compliance_persistence, "
					    + " response_status, review_status, review_status_attribute, "
					    + " last_response_review_remarks, response_review_remarks,"
					    + " desk_effective_score, desk_reviewer_maker_remarks) " 
				+ " Select "
						+ " chklistTmp.doc_id, chklistTmp.legal_entity_code,  chklistTmp.observation_id,   "
						+ " chklistTmp.audit_id, chklistTmp.audit_type_code, chklistTmp.mapping_id, "
					    + " chklistTmp.unit_code, chklistTmp.chapter,  chklistTmp.section, "
					    + " chklistTmp.product,  chklistTmp.process,  chklistTmp.checklist, "
					    + " chklistTmp.checklist_code,  chklistTmp.compliance_status,  chklistTmp.criticality, "
					    + " chklistTmp.io_comments, chklistTmp.submission_status,  chklistTmp.update_status, "
					    + " chklistTmp.update_status_message, chklistTmp.non_compliance_persistence,"
					    + " chklistTmp.response_status, chklistTmp.review_status, chklistTmp.review_status_attribute, "
					    + " chklistTmp.last_response_review_remarks, chklistTmp.response_review_remarks,"
					    + " desk_effective_score, desk_reviewer_maker_remarks "
				+ " from checklist_offline_inspection_tmp chklistTmp    "
				+ "	Where chklistTmp.doc_id = :docId	"
				+ "	and chklistTmp.audit_type_code = :auditTypeCode "
				+ "	and chklistTmp.audit_id = :auditId "
				+ "	and chklistTmp.legal_entity_code = :legalEntityCode   ");
		
		getSession().createSQLQuery(sqlQuery.toString())
 					.setParameter("docId", docId) 
			 		.setParameter("auditTypeCode", auditTypeCode)
			 		.setParameter("auditId", auditId)
			 		.setParameter("legalEntityCode", legalEntityCode)
			 		.executeUpdate();		   
	} 
	
	@Override
	public void updateDeskComplinaceRatingFromOfflineUpload(Integer legalEntityCode,  String auditId,  String auditTypeCode, String reportLanguage, Integer docId, String role, 
			String maker){
		
		StringBuilder sqlQuery= new StringBuilder(" update audit_rating_compliance_obs_score score inner join checklist_offline_inspection_tmp chklist    "
				+ "		set  "
				+ " 	score.desk_assessed_score = chklist.desk_effective_score,"
				+ " 	score.desk_effective_score  = chklist.desk_effective_score,"
				+ " 	score.reviewer_maker_remarks  = chklist.desk_reviewer_maker_remarks,"
				+ " 	score.reviewer_checker_remarks  = chklist.desk_reviewer_maker_remarks,"				
				+ " 	score.maker  = '"+maker+"'   "				 
				+ "		where score.audit_type_code = chklist.audit_type_code "
				+ "		and score.audit_id = chklist.audit_id "
				+ "		and score.legal_entity_code = chklist.legal_entity_code "
				+ "		and score.observation_id = chklist.observation_id " 
				+ "		and chklist.audit_type_code = :auditTypeCode "
				+ "		and chklist.audit_id = :auditId "
				+ "		and chklist.legal_entity_code = :legalEntityCode   "
				+ " 	and chklist.doc_id = :docId "
				+ " 	and chklist.update_status =  :updateStatus " 
				+ " ");
		
		getSession().createSQLQuery(sqlQuery.toString())
		 		.setParameter("auditTypeCode", auditTypeCode)
		 		.setParameter("auditId", auditId)
		 		.setParameter("legalEntityCode", legalEntityCode)			 		
		 		.setParameter("docId", docId) 
		 		.setParameter("updateStatus", BankAuditConstant.SUCCESS)
		 		.executeUpdate();		 

	}
	
	@Override
	public void updateUploadStatus(Integer legalEntityCode,  String auditId,  String auditTypeCode, String reportLanguage, Integer docId){
		
		StringBuilder sqlQuery = new StringBuilder(" update AuditLevelDocumentDetails doc "
				+"		set uploadStatus = '"+BankAuditConstant.SUCCESS+"'    "
				+"		Where doc.id = :docId	"
				+"		and doc.auditTypeCode = :auditTypeCode "
				+"		and doc.auditId = :auditId "
				+"		and doc.legalEntityCode = :legalEntityCode   ");
		
		getSession().createQuery(sqlQuery.toString())
 					.setParameter("docId", docId) 
			 		.setParameter("auditTypeCode", auditTypeCode)
			 		.setParameter("auditId", auditId)
			 		.setParameter("legalEntityCode", legalEntityCode)
			 		.executeUpdate();		
	} 
	
	@Override
	public void deleteChecklistOfflineInspectionTmp(Integer legalEntityCode,  String auditId,  String auditTypeCode, String reportLanguage, Integer docId){
		
		StringBuilder sqlQueryDelete = new StringBuilder(" Delete from ChecklistOfflineInspectionTmp "
				+"		Where docId = :docId	"
				+"		and auditTypeCode = :auditTypeCode "
				+"		and auditId = :auditId "
				+"		and legalEntityCode = :legalEntityCode   ");
		
		getSession().createQuery(sqlQueryDelete.toString())
						.setParameter("docId", docId) 
			 		.setParameter("auditTypeCode", auditTypeCode)
			 		.setParameter("auditId", auditId)
			 		.setParameter("legalEntityCode", legalEntityCode)
			 		.executeUpdate();	
	} 
	
	
	@Override
	public AuditObservation getAuditObservation(ChecklistOfflineInspectionTmp  checklistUploadDto){
		try{
			 Session session =getSession();		
			 session.setDefaultReadOnly(true);
			
			 String selectQry =" From  AuditObservation   " 
			 		+ " Where auditId  =:auditId "
					+ " and auditTypeCode =:auditTypeCode "
					+ " and observationId  =:observationId "
					+ " and mappingId  =:mappingId "
					+ " and legalEntityCode =:legalEntityCode ";
			 
			Query qry  = session.createQuery(selectQry)
					.setParameter("auditId", checklistUploadDto.getAuditId())
					.setParameter("auditTypeCode", checklistUploadDto.getAuditTypeCode())
					.setParameter("observationId", checklistUploadDto.getObservationId())
					.setParameter("mappingId", checklistUploadDto.getMappingId())
					.setParameter("legalEntityCode", checklistUploadDto.getLegalEntityCode());
			
			AuditObservation auditObservation = (AuditObservation) qry.uniqueResult();
			 return auditObservation;
		}catch (Exception e) { 
			//logger.error("Error .." + e.getMessage()+" :: "+e.getCause());
		}
		return null;
	}
	
	@Override
	public String  getStandardStatementForReportTable(ChecklistOfflineInspectionTmp  checklistUploadDto, String reportLanguage){
    	 
    	try{
	    	Session session=getSession();
			session.setDefaultReadOnly(true);
			StringBuffer strQuery = new StringBuffer( "");	
			String printFlag="";			  		 
			
		    if(checklistUploadDto.getComplianceStatus().equalsIgnoreCase(BankAuditConstant.COMPLIANCE_YES)){
	    		 strQuery.append(" select concat(ifnull(stmt.standard_stmnt_compliance, ' '), '#::#', ifnull(stmt.standard_stmnt_compliance_hindi, ' ') , '#::#','Y' )     as stndstmt ");
	    		 printFlag=" and (standard_stmnt_compliance_print=:printFlag OR standard_stmnt_compliance_print is null)";
	    	 }else if(checklistUploadDto.getComplianceStatus().equalsIgnoreCase(BankAuditConstant.COMPLIANCE_NO)){
	    		 strQuery.append(" select concat(ifnull(stmt.standard_stmnt_non_compliance, ' '), '#::#', ifnull(stmt.standard_stmnt_non_compliance_hindi, ' ') , '#::#','Y' )    as stndstmt "); 
	    		 printFlag=" and ( standard_stmnt_non_compliance_print=:printFlag   OR standard_stmnt_non_compliance_print is null) ";
	    	 }else{
	    		 return null;
	    	 } 
		    
	    	 strQuery.append("  from  activity_process_finding_mapping mapping      "
				+" inner join audit_observation  abr on  mapping.mapping_id = abr.mapping_id         "
				+" Inner join maint_audit_finding find on mapping.finding_code =  find.finding_code   "
				+" Inner join  maint_report_standard_stmt stmt on  mapping.mapping_id = stmt.mapping_id "
				+" where "					 
				+" abr.audit_id =:auditId    "
				+" and abr.audit_type_code =:auditTypeCode  " 
				+" and mapping.legal_entity_code =:legalEntityCode "
				+" and mapping.finding_code =:findingCode  "
				+" and find.entity_status=:entityStatus "
				+" and mapping.mapping_id =:mappingId ");
	    	 strQuery.append(printFlag);
						
			Query query=(Query) session.createSQLQuery(strQuery.toString()) 
					.setParameter("auditId", checklistUploadDto.getAuditId())
					.setParameter("auditTypeCode", checklistUploadDto.getAuditTypeCode())  
					.setParameter("legalEntityCode", checklistUploadDto.getLegalEntityCode())
					.setParameter("findingCode", checklistUploadDto.getChecklistCode())
					.setParameter("mappingId", checklistUploadDto.getMappingId())
					.setParameter("entityStatus", BankAuditConstant.STATUS_ACTIVE)
					.setParameter("printFlag", BankAuditConstant.YES);			
			
			return (String) query.uniqueResult();	
			
		}catch (Exception e) { 
			//logger.error("Error .." + e.getMessage()+" :: "+e.getCause());
			return BankAuditConstant.FAILED;
		} 
	 }
	
	@Override
	public List<Integer> getListofIdsFormReportTable(Integer legalEntityCode,  String auditId,  String auditTypeCode, 
			String applicableFor, String statementType, String complianceCategory){
		try{
			 Session session =getSession();		
			 session.setDefaultReadOnly(true);
			
			 StringBuilder selectQry = new StringBuilder(" select  rpt1.id from audit_report_stmt rpt1  " );
			
			 if(!BankAuditUtil.isEmptyString(complianceCategory) && BankAuditConstant.COMPLIANCE_NO.equalsIgnoreCase(complianceCategory)){
				 //selectQry.append(" AND compliance_category = :complianceCategory ) ");
				 selectQry.append("INNER JOIN audit_report_stmt rpt2 on rpt1.audit_id = rpt2.audit_id AND rpt1.audit_type_code = rpt2.audit_type_code "
				 		+ " AND rpt1.legal_entity_code = rpt2.legal_entity_code AND rpt1.mapping_id = rpt2.mapping_id"
				 		+ " AND rpt2.applicable_for = :applicableFor AND rpt2.statement_type = :statementType_rpt2  "
				 		+ " AND rpt2.compliance_category = :complianceCategory ");
			 }		 
			 selectQry.append( " Where rpt1.audit_id  =:auditId "
						+ " and rpt1.audit_type_code =:auditTypeCode " 
						+ " and rpt1.legal_entity_code =:legalEntityCode"
						+"	and rpt1.applicable_for = :applicableFor "
						+"	and rpt1.statement_type = :statementType   "); 
			Query qry  = session.createSQLQuery(selectQry.toString())
					.setParameter("auditId", auditId)
					.setParameter("auditTypeCode", auditTypeCode) 
					.setParameter("legalEntityCode", legalEntityCode)
					.setParameter("applicableFor", applicableFor) 
			 		.setParameter("statementType", statementType);
			if(!BankAuditUtil.isEmptyString(complianceCategory) && BankAuditConstant.COMPLIANCE_NO.equalsIgnoreCase(complianceCategory)){
				qry.setParameter("complianceCategory", complianceCategory);
				qry.setParameter("statementType_rpt2", BankAuditConstant.FOR_CHECKLIST);
			 } 
			
			return qry.list();
			
		}catch (Exception e) { 
			System.out.println(e);
			//logger.error("Error .." + e.getMessage()+" :: "+e.getCause());
		}
		return null;
	}
	
	@Override
	public Map<String, AuditObservation> getAllAuditObservation(Integer legalEntityCode, String auditTypeCode, String auditId){
		try{
			 Session session =getSession();		
			 session.setDefaultReadOnly(true);
			
			 String selectQry =" From  AuditObservation   " 
			 		+ " Where auditId  =:auditId "
					+ " and auditTypeCode =:auditTypeCode " 
					+ " and legalEntityCode =:legalEntityCode ";
			 
			Query qry  = session.createQuery(selectQry)
					.setParameter("auditId", auditId)
					.setParameter("auditTypeCode", auditTypeCode)					 
					.setParameter("legalEntityCode", legalEntityCode);
			
			List<AuditObservation> auditObservationList =  qry.list();
			
			Map<String, AuditObservation>  auditObservationMap = auditObservationList.stream().collect(Collectors 
					.toMap(observation -> (observation.getAuditId()+observation.getObservationId()+observation.getMappingId()), observation -> observation));					 
			System.out.println(auditObservationMap);
			return auditObservationMap;
		}catch (Exception e) { 
			logger.error("Error .." + e.getMessage()+" :: "+e.getCause());
		}
		return null;
	}
	
	@Override
	public Map<String, ChecklistDto>  getAllStandardStmtMap(Integer legalEntityCode, String auditId, String auditTypeCode) {
		 
		Session session=getSession();
		session.setDefaultReadOnly(true);
		StringBuffer strQuery = new StringBuffer( "");	
						  		 
		strQuery.append(" Select  stmt.mapping_id,  " 
				+ " IFNULL(stmt.standard_stmnt_compliance,''),  IFNULL(stmt.standard_stmnt_compliance_hindi,''),  IFNULL(standard_stmnt_compliance_print,''),  "
				+ " IFNULL(stmt.standard_stmnt_non_compliance,''), IFNULL(stmt.standard_stmnt_non_compliance_hindi,''), IFNULL(standard_stmnt_non_compliance_print,'') "				  
				+ " from  activity_process_finding_mapping mapping      "
				+ " inner join audit_observation  abr on  mapping.mapping_id = abr.mapping_id   and abr.audit_type_code = mapping.audit_type_code " 				
				+ " INNER JOIN maint_report_standard_stmt stmt on stmt.mapping_id = mapping.mapping_id  and stmt.audit_type_code = mapping.audit_type_code "
				
				+ " and mapping.status='"+BankAuditConstant.STATUS_ACTIVE+"' and  mapping.entity_status='"+BankAuditConstant.STATUS_AUTH+"' "				
				+ " and stmt.status='"+BankAuditConstant.STATUS_ACTIVE+"' and stmt.entity_status='"+BankAuditConstant.STATUS_AUTH+"' "				
				 
				+ " and abr.audit_id =:auditId    "
				+ " and abr.audit_type_code =:auditTypeCode  " 
				+ " and mapping.legal_entity_code =:legalEntityCode "
				+ " and abr.legal_entity_code =:legalEntityCode "
				+ " and stmt.legal_entity_code =:legalEntityCode  " 				 
				+ " ");
		 
		Query query=(Query) session.createSQLQuery(strQuery.toString()) 
				.setParameter("auditId", auditId)
				.setParameter("auditTypeCode", auditTypeCode) 
				.setParameter("legalEntityCode", legalEntityCode);		 
		
		List<Object[]> checkListLst =query.list();
		
		Map<String, ChecklistDto> checkListMap = new HashMap<>();
		for (Object[] stmt : checkListLst) {
			String mappingId = (stmt[0]==null || stmt[0].toString().equalsIgnoreCase("null") || stmt[0].toString().equals(""))?"":stmt[0].toString();	
			
			ChecklistDto checklistDto =  new ChecklistDto();
			checklistDto.setStandardStmntCompliance((stmt[1]==null || stmt[1].toString().equalsIgnoreCase("null") || stmt[1].toString().equals(""))?"":stmt[1].toString());
			checklistDto.setStandardStmntComplianceHindi((stmt[2]==null || stmt[2].toString().equalsIgnoreCase("null") || stmt[2].toString().equals(""))?"":stmt[2].toString());
			checklistDto.setStandardStmntCompliancePrint((stmt[3]==null || stmt[3].toString().equalsIgnoreCase("null") || stmt[3].toString().equals(""))?"":stmt[3].toString());
			
			checklistDto.setStandardStmntNonCompliance((stmt[4]==null || stmt[4].toString().equalsIgnoreCase("null") || stmt[4].toString().equals(""))?"":stmt[4].toString());
			checklistDto.setStandardStmntNonComplianceHindi((stmt[5]==null || stmt[5].toString().equalsIgnoreCase("null") || stmt[5].toString().equals(""))?"":stmt[5].toString());
			checklistDto.setStandardStmntNonCompliancePrint((stmt[6]==null || stmt[6].toString().equalsIgnoreCase("null") || stmt[6].toString().equals(""))?"":stmt[6].toString());
			
			checkListMap.put(mappingId, checklistDto);
		}		
		return checkListMap;  
	}
	
	@Override
	public List<String> getAllNonComplianceBehavior(Integer legalEntityCode, String auditId, String auditTypeCode, String unitId, 
			List<String> mappingIds){
		
		Session session=getSession();
		session.setDefaultReadOnly(true);
		logger.info("Inside getAllNonComplianceBehavior .. "+ auditId+" :: "+auditTypeCode+" :: "+ unitId);	
		 
		List<String> persistenceMappingIds = new ArrayList<>(); 
		// STEP 1: Find out the Compliance category from the Previous Audit and If it is Non compliance return as Persistence Or else STEP 2
		StringBuilder strQuery = new StringBuilder(" SELECT obs.mapping_id, sch_prev.audit_id, obs.compliance_category FROM audit_schedule sch_prev "
				+ " JOIN audit_schedule sch_curr ON sch_prev.audit_id=sch_curr.prev_audit_id "
				+ " AND sch_prev.legal_entity_code = sch_curr.legal_entity_code "
				+ " AND sch_curr.audit_id = :auditId "
				+ " JOIN audit_observation obs ON sch_prev.audit_id = obs.audit_id"
				+ " AND obs.mapping_id IN (:mappingIds) "
				+ " AND obs.compliance_category = '"+BankAuditConstant.COMPLIANCE_NO+"' " 
				+ " WHERE sch_prev.legal_entity_code = :legalEntityCode  ");
		 	
		logger.info("Before the qry...."+ strQuery.toString());
		Query query=session.createNativeQuery(strQuery.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditId", auditId)
				.setParameterList("mappingIds", mappingIds);
		
		List<Object[]> list =  query.list();
		for (Object[] obj : list) {
			if(obj !=null && obj[0]!=null && obj[1]!=null ) {
				persistenceMappingIds.add(obj[0].toString());		 
			}
		}
		
		
		//STEP 2: Find out the Compliance category of all previous Audits for the same Finding/Checklist/Mapping
		//        If the Non compliance count is more than of 50% of Total, then it is called as Persistence behavior
		//        The Audits consider is, whose Compliance category is either N or C.
		if(persistenceMappingIds.size() < mappingIds.size()) {			
			mappingIds.removeAll(persistenceMappingIds);				
			StringBuilder strQueryForAllPrevAudit = new StringBuilder(" SELECT mappingId, "
					+ " IFNULL(SUM(nonCompliance),0) AS nonComplianceSUM, "
					+ " IFNULL(SUM(compliance),0) AS complianceSum   "
					+ " FROM ( "
						+ " SELECT obs.mapping_id AS mappingId, "
						+ " CASE WHEN obs.compliance_category = '"+BankAuditConstant.COMPLIANCE_NO+"' THEN  1 END AS nonCompliance ,"
						+ " CASE WHEN obs.compliance_category = '"+BankAuditConstant.COMPLIANCE_YES+"' THEN 1  END AS compliance "
						+ " FROM audit_observation obs, audit_schedule sch "
						+ " WHERE sch.legal_entity_code = obs.legal_entity_code "
						+ " AND sch.audit_id = obs.audit_id "
						+ " AND sch.audit_type_code = obs.audit_type_code "
						+ " AND obs.compliance_category IN ('"+BankAuditConstant.COMPLIANCE_NO+"','"+BankAuditConstant.COMPLIANCE_YES+"') "
						+ " AND sch.legal_entity_code = :legalEntityCode AND sch.audit_type_code = :auditTypeCode "
						+ " AND sch.unit_id = :unitId AND obs.mapping_id IN (:mappingIds)  AND sch.audit_id <> :auditId "						 
					+ " ) AS tmp GROUP BY mappingId  ");  
			
			logger.info("Before the strQueryForAllPrevAudit ...."+ strQueryForAllPrevAudit.toString());
			Query query2=session.createNativeQuery(strQueryForAllPrevAudit.toString())
					.setParameter("legalEntityCode", legalEntityCode)
					.setParameter("auditId", auditId)
					.setParameter("unitId", unitId)
					.setParameter("auditTypeCode", auditTypeCode)
					.setParameterList("mappingIds", mappingIds);
			
			List<Object[]> obsCompLst = query2.list(); 			 
			persistenceMappingIds.addAll(obsCompLst.stream()
							.filter( e -> (Integer.parseInt(e[1].toString())*100/ (Integer.parseInt(e[1].toString())+Integer.parseInt(e[2].toString())))>=50)
							.collect(Collectors.toList())
							.stream().map(e1 -> e1[0].toString()).collect(Collectors.toList()));		 
 		}
		
		return persistenceMappingIds;
	}
	
	@Override
	public List<String> getAllResponseReviewRemarks(Integer legalEntityCode, String auditTypeCode, String auditId,   String role){
		 
		List<String> rolesListCompliance = Arrays.asList(("G3,G31").split(","));	//Compliance or Response
		List<String> rolesListReviewer = Arrays.asList(("G4,G7").split(","));       //review/Scrutiny	
		
		StringBuilder sqlQuery =  new StringBuilder("Select concat(legal_entity_code,audit_type_code,audit_id,group_observation_id,observation_id) "
				+ " from audit_observation_respond_review_capture where ");
		
		sqlQuery.append(" legal_entity_code = :legalEntityCode "
				+ " AND audit_type_code = :auditTypeCode "
				+ " AND audit_id = :auditId  "
				+ " AND capture_type IN (:roleList) "
				+ " AND status = :pendingAuth ");
		
		Query qry = getSession().createSQLQuery(sqlQuery.toString())	 		
	 		.setParameter("legalEntityCode", legalEntityCode)
	 		.setParameter("auditTypeCode", auditTypeCode)
	 		.setParameter("auditId", auditId)
	 		.setParameter("pendingAuth", BankAuditConstant.STATUS_PENDING_AUTH);
		
		if(rolesListCompliance.contains(role)){
			qry.setParameterList("roleList", rolesListCompliance);
		}else if(rolesListReviewer.contains(role)){ 
			qry.setParameterList("roleList", rolesListReviewer);
		} 
		
		return qry.list(); 
	}
	
	@Override
	public void updateAllNonComplianceBehaviorToTemp(Integer legalEntityCode, String auditId, String auditTypeCode, List<String> persistenceMappingIds, Integer docId) { 
		
		StringBuilder sqlQuery= new StringBuilder(" update checklist_offline_inspection_tmp chklisttmp    "
				+ "		set  "
				+" 		chklisttmp.non_compliance_persistence = '"+BankAuditConstant.YES+"' " 
				+"		where  chklisttmp.audit_type_code = :auditTypeCode "
				+"		and chklisttmp.audit_id = :auditId "
				+"		and chklisttmp.legal_entity_code = :legalEntityCode   "
				+" 		and chklisttmp.doc_id = :docId "
				+" 		and chklisttmp.update_status =  :updateStatus "
				+" 		and chklisttmp.mapping_id in  (:persistenceMappingIds) ");
		
		getSession().createSQLQuery(sqlQuery.toString())
			 		.setParameter("auditTypeCode", auditTypeCode)
			 		.setParameter("auditId", auditId)
			 		.setParameter("legalEntityCode", legalEntityCode) 
			 		.setParameter("docId", docId) 
			 		.setParameter("updateStatus", BankAuditConstant.SUCCESS) 
			 		.setParameterList("persistenceMappingIds", persistenceMappingIds)  
			 		.executeUpdate();
	}
	 
	/*@Override
	public Integer[] getMinMaxReportTableId(Integer legalEntityCode,  String auditId,  String auditTypeCode){
		try{
			 Session session =getSession();		
			 session.setDefaultReadOnly(true);
			
			 String selectQry =" select  min(id), max(id) from audit_report_stmt   " 
			 		+ " Where audit_id  =:auditId "
					+ " and audit_type_code =:auditTypeCode " 
					+ " and legal_entity_code =:legalEntityCode ";
			 
			Query qry  = session.createSQLQuery(selectQry)
					.setParameter("auditId", auditId)
					.setParameter("auditTypeCode", auditTypeCode) 
					.setParameter("legalEntityCode", legalEntityCode);
			
			Object[] minMaxObj = (Object[]) qry.uniqueResult(); 
			
			if(minMaxObj!=null && minMaxObj.length>1){
				return new Integer[]{(Integer) minMaxObj[0], (Integer) minMaxObj[1]};
			}
			
		}catch (Exception e) { 
			System.out.println(e);
			//logger.error("Error .." + e.getMessage()+" :: "+e.getCause());
		}
		return null;
	}*/
	 
	 /******** Offline Observation New code End*******/
	
	@Override
	public void deleteRatingDataFromTmp(AuditLevelDocumentDetails auditLevelDocumentDetails){
		Session session = getSession();
		session.createQuery("  Delete From "
				+ " IfcRatingDataTmp  " 
				+ " where  legalEntityCode =:legalEntityCode"					 
				+ " and auditTypeCode =:auditTypeCode "
				+ " and auditId =:auditId " ) 
		
			.setParameter("legalEntityCode", auditLevelDocumentDetails.getLegalEntityCode())				
			.setParameter("auditTypeCode",  auditLevelDocumentDetails.getAuditTypeCode())
			.setParameter("auditId",  auditLevelDocumentDetails.getAuditId()) 
			.executeUpdate();
	}
	
	@Override
	public String copyRatingDataToMasterFromTemp(AuditLevelDocumentDetails auditLevelDocumentDetails){
		
		try{
			Session session = getSession();
			
			//Delete from master 		
			session.createQuery("  Delete from  "
					+ " IfcRatingData  "					 
					+ " where  legalEntityCode =:legalEntityCode"					 
					+ " and auditTypeCode =:auditTypeCode "
					+ " and auditId =:auditId  ") 
			
				.setParameter("legalEntityCode", auditLevelDocumentDetails.getLegalEntityCode())				
				.setParameter("auditTypeCode",  auditLevelDocumentDetails.getAuditTypeCode())
				.setParameter("auditId",  auditLevelDocumentDetails.getAuditId()) 
				.executeUpdate();
						
			//Insert into Master
			session.createQuery(" Insert into "
					+ " IfcRatingData("
						+ " legalEntityCode, auditTypeCode, auditId, "
						+ "  inputExt, inputBaas, inputValue) "
					+ " select "
					+ " legalEntityCode, auditTypeCode, auditId, "
					+ "  inputExt, inputBaas, inputValue " 
						+ " from IfcRatingDataTmp "
					+ " where  legalEntityCode =:legalEntityCode" 
					+ " and auditTypeCode =:auditTypeCode "
					+ " and auditId =:auditId ") 
			 
				.setParameter("legalEntityCode", auditLevelDocumentDetails.getLegalEntityCode() ) 
				.setParameter("auditTypeCode",  auditLevelDocumentDetails.getAuditTypeCode())
				.setParameter("auditId",  auditLevelDocumentDetails.getAuditId())
				.executeUpdate();		
			
			//Delete from Temp
			session.createQuery("  Delete From "
					+ " IfcRatingDataTmp  " 
					+ " where  legalEntityCode =:legalEntityCode"					 
					+ " and auditTypeCode =:auditTypeCode "
					+ " and auditId =:auditId " ) 
			
				.setParameter("legalEntityCode", auditLevelDocumentDetails.getLegalEntityCode())				
				.setParameter("auditTypeCode",  auditLevelDocumentDetails.getAuditTypeCode())
				.setParameter("auditId",  auditLevelDocumentDetails.getAuditId()) 
				.executeUpdate();

		}catch (Exception e) {
			logger.error(e.getStackTrace().toString()); 
			return BankAuditConstant.FAILED;
		}
		return BankAuditConstant.SUCCESS;
	}	
	
	@Override
	public void updateRatingParameterScoresToMaster(Integer legalEntityCode, String auditTypeCode, String auditId, Integer docId, 
			String rmCode,   String role, String userType){
		
		logger.info("updateRatingParameterScoresToMaster .. "+ auditId + " :: "+ auditTypeCode);
		List<String> rolesListInspector = Arrays.asList(("G2,G8").split(","));  //Inspection
		List<String> rolesListHO_RO = Arrays.asList(("G12").split(","));	 	
		StringBuilder sqlQuery= new StringBuilder(" update  audit_rating_parameter_score score inner join audit_rating_parameter_score_tmp scoreTmp    "
				 + " on score.audit_type_code = scoreTmp.audit_type_code "
				 + " and score.audit_id = scoreTmp.audit_id "
				 + " and score.legal_entity_code = scoreTmp.legal_entity_code "
				 + " and score.rm_code = scoreTmp.rm_code "
				 + " and score.param_code = scoreTmp.param_code "
				 + " and scoreTmp.legal_entity_code = :legalEntityCode "
				 + " and scoreTmp.audit_type_code = :auditTypeCode "
				 + " and scoreTmp.audit_id = :auditId "
				 + " and scoreTmp.rm_code = :rmCode " 
				 + " and scoreTmp.doc_id = :docId " 	  
				 +" set  ");
		if(rolesListInspector.contains(role)){	
			sqlQuery.append(" score.score_assessed = scoreTmp.score_assessed ");
			sqlQuery.append(", score.score_effective = scoreTmp.score_effective  ");
			sqlQuery.append(", score.maker_remarks = IFNULL(scoreTmp.maker_remarks, score.maker_remarks)  ");
		}else if(rolesListHO_RO.contains(role)){
			if(userType.equalsIgnoreCase("RO")) {
				sqlQuery.append(" score.score_effective = scoreTmp.score_effective  ");
				sqlQuery.append(", score.score_effective_ro = scoreTmp.score_effective_ro  ");
				sqlQuery.append(", score.reviewer_remarks = IFNULL(scoreTmp.reviewer_remarks, score.reviewer_remarks)  ");
				sqlQuery.append(", score.reviewer_remarks_id = IFNULL(scoreTmp.reviewer_remarks_id, score.reviewer_remarks_id)  ");
			}else {
				sqlQuery.append(" score.score_effective_ho = scoreTmp.score_effective_ho  ");
				sqlQuery.append(", score.ho_remarks = IFNULL(scoreTmp.ho_remarks, score.ho_remarks)  ");
				sqlQuery.append(", score.ho_remarks_id = IFNULL(scoreTmp.ho_remarks_id, score.ho_remarks_id)  ");
			}
		}   		
    		 
		getSession().createNativeQuery(sqlQuery.toString())
			.setParameter("auditTypeCode", auditTypeCode)
			.setParameter("auditId", auditId)
			.setParameter("legalEntityCode", legalEntityCode)	
			.setParameter("rmCode", rmCode)	 	
			.setParameter("docId", docId)  
			.executeUpdate();	
		   
	}
	
	@Override
	public void deleteAuditRatingParameterScoreTmp(Integer legalEntityCode, String auditTypeCode, String auditId, Integer docId ){
		 	 	
		StringBuilder sqlQuery= new StringBuilder("Delete from audit_rating_parameter_score_tmp scoreTmp    " 
				 + " Where scoreTmp.legal_entity_code = :legalEntityCode "
				 + " and scoreTmp.audit_type_code = :auditTypeCode "
				 + " and scoreTmp.audit_id = :auditId " 
				 + " and scoreTmp.doc_id = :docId " 	  
				 +"   "); 
    		 
		getSession().createNativeQuery(sqlQuery.toString())
			.setParameter("auditTypeCode", auditTypeCode)
			.setParameter("auditId", auditId)
			.setParameter("legalEntityCode", legalEntityCode) 
			.setParameter("docId", docId)  
			.executeUpdate();	
		   
	} 
	
	@Override
	public void updateAuditRatingComplianceObsScoreToMaster(Integer legalEntityCode, String auditTypeCode, String auditId, Integer docId, 
			   String role, String ratingComplianceStatus){
		
		logger.info("updateAuditRatingComplianceObsScoreToMaster .. "+ auditId + " :: "+ auditTypeCode);
		List<String> rolesListInspector = Arrays.asList(("G2,G8").split(","));  //Inspection
		List<String> rolesListReviewer = Arrays.asList(("G8,G12").split(",")); //Reviewer
		List<String> rolesListScrutinizer = Arrays.asList(("G4,G7").split(",")); // Scrutinizer/review team 
		
		StringBuilder sqlQuery= new StringBuilder(" update  audit_rating_compliance_obs_score score inner join audit_rating_compliance_obs_score_tmp scoreTmp    "
				// + " on score.audit_type_code = scoreTmp.audit_type_code "
				 + " on score.audit_id = scoreTmp.audit_id "
				 + " and score.legal_entity_code = scoreTmp.legal_entity_code " 
				 + " and score.observation_id = scoreTmp.observation_id "				 
				 + " and scoreTmp.legal_entity_code = :legalEntityCode "
				// + " and scoreTmp.audit_type_code = :auditTypeCode "
				 + " and scoreTmp.audit_id = :auditId " 
				 + " and scoreTmp.doc_id = :docId " 	  
				 +" set  ");
		if(rolesListScrutinizer.contains(role)
				&& (BankAuditUtil.isEmptyString(ratingComplianceStatus) || ratingComplianceStatus.equalsIgnoreCase("RCDDF"))){
			sqlQuery.append(" score.desk_assessed_score = scoreTmp.desk_assessed_score ");
			sqlQuery.append(", score.desk_effective_score = scoreTmp.desk_effective_score  ");
			sqlQuery.append(", score.reviewer_maker_remarks = scoreTmp.reviewer_maker_remarks  ");
			sqlQuery.append(", score.reviewer_checker_remarks = scoreTmp.reviewer_checker_remarks  ");
		}else if(rolesListScrutinizer.contains(role)
				&& (ratingComplianceStatus.equalsIgnoreCase("RCDU") || ratingComplianceStatus.equalsIgnoreCase("RCDM"))){
			sqlQuery.append(" score.desk_effective_score = scoreTmp.desk_effective_score  ");
			sqlQuery.append(", score.reviewer_checker_remarks = scoreTmp.reviewer_checker_remarks  ");							
		}else if(rolesListInspector.contains(role)  
				&& (BankAuditUtil.isEmptyString(ratingComplianceStatus) || ratingComplianceStatus.equalsIgnoreCase("RCDS") || ratingComplianceStatus.equalsIgnoreCase("RCFDF"))){
			sqlQuery.append(" score.field_assessed_score = scoreTmp.field_assessed_score ");
			sqlQuery.append(", score.field_effective_score = scoreTmp.field_effective_score  ");
			sqlQuery.append(", score.auditor_maker_remarks = scoreTmp.auditor_maker_remarks  ");
			sqlQuery.append(", score.auditor_checker_remarks = scoreTmp.auditor_checker_remarks  ");							 
		}else if(rolesListReviewer.contains(role) 
				&& (ratingComplianceStatus.equalsIgnoreCase("RCFU") ||ratingComplianceStatus.equalsIgnoreCase("RCFM"))){
			sqlQuery.append(" score.field_effective_score = scoreTmp.field_effective_score  ");
			sqlQuery.append(", score.auditor_checker_remarks = scoreTmp.auditor_checker_remarks  ");	
		}  
		
		getSession().createNativeQuery(sqlQuery.toString())
			//.setParameter("auditTypeCode", auditTypeCode)
			.setParameter("auditId", auditId)
			.setParameter("legalEntityCode", legalEntityCode)	 
			.setParameter("docId", docId)  
			.executeUpdate();		   
	}
	
	@Override
	public void deleteAuditRatingComplianceObsScoreTmp(Integer legalEntityCode, String auditTypeCode, String auditId, Integer docId ){
		 	 	
		StringBuilder sqlQuery= new StringBuilder("Delete from audit_rating_compliance_obs_score_tmp scoreTmp    " 
				 + " Where scoreTmp.legal_entity_code = :legalEntityCode "
				 + " and scoreTmp.audit_type_code = :auditTypeCode "
				 + " and scoreTmp.audit_id = :auditId " 
				 + " and scoreTmp.doc_id = :docId " 	  
				 +"   "); 
    		 
		getSession().createNativeQuery(sqlQuery.toString())
			.setParameter("auditTypeCode", auditTypeCode)
			.setParameter("auditId", auditId)
			.setParameter("legalEntityCode", legalEntityCode) 
			.setParameter("docId", docId)  
			.executeUpdate();	
		   
	} 
	
	// To verify whether Audit is Biannual or not  // BOMD610 used for Biannual  -> 1=Annual , 2= Biannual
	//@Override
	public boolean isAuditBiAnnual(Integer legalEntityCode, String auditId , String auditTypeCode){
		Session session =getSession();
		logger.info("Inside isAuditBiAnnual .. "+ auditId +" :: "+auditTypeCode);
		boolean isAuditBiAnnual=false;
		try{					  		
			String biAnnualParam="BOMD610"; // for DCCBI and DCCBEI
			StringBuilder query= new StringBuilder("SELECT ifc FROM IfcEnsureBaasIRProcessData ifc "
					+ " WHERE ifc.legalEntityCode=:legalEntityCode AND ifc.auditId=:auditId AND parameterName=:biAnnualParam ");
			Query hqlQuery= session.createQuery(query.toString()).setParameter("legalEntityCode", legalEntityCode)
					.setParameter("auditId", auditId).setParameter("biAnnualParam", biAnnualParam);
			
			IfcEnsureBaasIRProcessData ifcIrData=(IfcEnsureBaasIRProcessData)hqlQuery.uniqueResult();
			if(ifcIrData !=null && ifcIrData.getSubmittedValueLatest() !=null) {
				String submittedValue=ifcIrData.getSubmittedValueLatest();
				logger.info("submittedValue .."+submittedValue);
				if("2".equalsIgnoreCase(submittedValue.trim())) isAuditBiAnnual=true;
			}							
			
		}catch (Exception e) { 
			logger.error("Error .." + e.getMessage()+" :: "+e.getCause());
			e.printStackTrace(System.out);
		}
		return isAuditBiAnnual;
	}
	
	// To verify whether Scheduled or Non-Scheduled StCB  // BOMS602 used Scheduled/Non-Scheduled  -> 1 OR Yes =Scheduled , 2 OR No= Non-Scheduled
		//@Override
		public boolean isScheduledStcb(Integer legalEntityCode, String auditId , String auditTypeCode){
			Session session =getSession();
			logger.info("Inside isScheduledStcb .. "+ auditId +" :: "+auditTypeCode);
			boolean isScheduledStcb=false;
			try{					  		
				String schParam="BOMS602"; // for StCBI and StCBEI
				StringBuilder query= new StringBuilder("SELECT ifc FROM IfcEnsureBaasIRProcessData ifc "
						+ " WHERE ifc.legalEntityCode=:legalEntityCode AND ifc.auditId=:auditId AND parameterName=:schParam ");
				Query hqlQuery= session.createQuery(query.toString()).setParameter("legalEntityCode", legalEntityCode)
						.setParameter("auditId", auditId).setParameter("schParam", schParam);
				
				IfcEnsureBaasIRProcessData ifcIrData=(IfcEnsureBaasIRProcessData)hqlQuery.uniqueResult();
				if(ifcIrData !=null && ifcIrData.getSubmittedValueLatest() !=null) {
					String submittedValue=ifcIrData.getSubmittedValueLatest();
					logger.info("submittedValue .."+submittedValue);
					if("1".equalsIgnoreCase(submittedValue.trim()) || "Yes".equalsIgnoreCase(submittedValue.trim())) isScheduledStcb=true;
				}							
				
			}catch (Exception e) { 
				logger.error("Error .." + e.getMessage()+" :: "+e.getCause());
				e.printStackTrace(System.out);
			}
			return isScheduledStcb;
		}
	
}

/*public Integer updateReportStandardStmtFormOfflineUpload(Integer legalEntityCode,  String auditId,  String auditTypeCode){

String sqlQuery=" update audit_report_stmt rpt, checklist_offline_inspection chklist    "
		+ "		set  "
		+" 		rpt.compliance_category = chklist.compliance_status, "
		+" 		statement = "
		+ "			(Case when chklist.compliance_status = 'C'  "
		+" 				Then  (Select ifnull(stmt.standard_stmnt_compliance, ' ')    "
		+" 					FROM maint_report_standard_stmt stmt  "
		+" 					where stmt.mapping_id = rpt.mapping_id "
		+" 					and stmt.audit_type_code = rpt.audit_type_code "
		+" 					and stmt.status = :status "
		+" 					and stmt.entity_status = :status  "
		+" 					and (stmt.standard_stmnt_compliance_print = :printFlag "
		+" 					OR stmt.standard_stmnt_compliance_print is null))   " 
		+" 				when  chklist.compliance_status = 'N'  "
		+" 				Then  (Select ifnull(stmt.standard_stmnt_non_compliance, ' ')   " 
		+" 					FROM maint_report_standard_stmt stmt  "
		+" 					where stmt.mapping_id = rpt.mapping_id "
		+" 					and stmt.audit_type_code = rpt.audit_type_code "
		+" 					and stmt.status = :status  "
		+" 					and stmt.entity_status = :status  "
		+" 					and (stmt.standard_stmnt_non_compliance_print = :printFlag    "
		+" 					OR stmt.standard_stmnt_non_compliance_print is null) )   "             
		+"			END ) , "
		+"		statement_hindi =  "
		+"			(Case when chklist.compliance_status = 'C'  "
		+"				Then  (Select ifnull(stmt.standard_stmnt_compliance_hindi, ' ')  "  
		+"					FROM maint_report_standard_stmt stmt  "
		+"					where stmt.mapping_id = rpt.mapping_id "
		+"					and stmt.audit_type_code = rpt.audit_type_code "
		+"					and stmt.status = :status  "
		+"					and stmt.entity_status = :status  "
		+"					and (stmt.standard_stmnt_compliance_print = :printFlag "
		+"						OR stmt.standard_stmnt_compliance_print is null))  " 
		+"				when chklist.compliance_status='N'  "
		+"				Then  (Select ifnull(stmt.standard_stmnt_non_compliance_hindi, ' ') "   
		+"					FROM maint_report_standard_stmt stmt  "
		+"					where stmt.mapping_id=rpt.mapping_id "
		+"					and stmt.audit_type_code = rpt.audit_type_code "
		+"					and stmt.status = :status  "
		+"					and stmt.entity_status = :status  "
		+"					and (stmt.standard_stmnt_non_compliance_print = :printFlag   " 
		+"					 OR stmt.standard_stmnt_non_compliance_print is null) )   "                             
		+"			END )    "
        +"     	where rpt.audit_type_code = chklist.audit_type_code "
		+"		and rpt.audit_id = chklist.audit_id "
		+"		and rpt.legal_entity_code = chklist.legal_entity_code "
		+"		and rpt.mapping_id = chklist.mapping_id "
		+"		and (rpt.compliance_Category is null OR rpt.compliance_Category = '' ) "
		+"		and rpt.statement_type = :statementType  "  
		+"		and chklist.audit_type_code = :auditTypeCode "
		+"		and chklist.audit_id = :auditId "
		+"		and chklist.legal_entity_code = :legalEntityCode   ";

Session session = getSession();		 
try{
	return  session.createSQLQuery(sqlQuery)
	 		.setParameter("auditTypeCode", auditTypeCode)
	 		.setParameter("auditId", auditId)
	 		.setParameter("legalEntityCode", legalEntityCode)
	 		.setParameter("statementType", BankAuditConstant.FOR_STANDARD_STMT)
	 		.setParameter("status", BankAuditConstant.STATUS_ACTIVE)
	 		.setParameter("printFlag", BankAuditConstant.YES)			 		
	 		.executeUpdate();	
}catch (Exception e) { 
	System.out.println(e+" ============ ");
}
return 0; 
}*/

