package com.bankaudit.rest.process.dao;

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

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

import com.bankaudit.constants.BankAuditConstant;
import com.bankaudit.dao.AbstractDao;
import com.bankaudit.dto.DataTableResponse;
import com.bankaudit.dto.process.AuditObservationComplianceDto;
import com.bankaudit.dto.process.NonCompliancePersistenceChecklistDto;
import com.bankaudit.process.model.AuditObservation;
import com.bankaudit.process.model.AuditObservationReportReviewCapture;
import com.bankaudit.process.model.AuditObservationReportReviewCaptureWrk;
import com.bankaudit.process.model.AuditObservationRespondReviewCapture;
import com.bankaudit.process.model.AuditSchedule;
import com.bankaudit.util.BankAuditUtil;

@Repository("auditObservationDao")
public class AuditObservationDaoImpl extends AbstractDao implements AuditObservationDao{
	
	static final Logger logger = Logger.getLogger(AuditObservationDaoImpl.class);
	
	@Override
	public DataTableResponse getAuditObservationList(int legalEntityCode, String planId, String auditId, String search,
			Integer orderColumn, String orderDirection, Integer page, Integer size, String userId) {

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

		DataTableResponse dataTableResponse=new DataTableResponse();
		logger.info("getAuditObservationList .. .. "+ userId);

		try {
		//StringBuilder queryString=new StringBuilder(" from AuditSchedule where legalEntityCode =:legalEntityCode ");
		StringBuilder queryString=new StringBuilder(// "select ols as auditObservation from "
				/* "    from AuditObservationWrk  where legalEntityCode =:legalEntityCode "
				+ " union "
				+ "  from AuditObservation  where legalEntityCode =:legalEntityCode "
				
				+ "   "
				" SELECT legal_entity_code, status, maker_timestamp, maker  FROM audit_observation  "
				+"  WHERE  "
				+"  STATUS !='D' "
				+"  AND legal_entity_code=:legalEntityCode "
				+"  UNION "
				+"   SELECT legal_entity_code, status, maker_timestamp, maker FROM audit_observation_wrk  "
				+"   WHERE legal_entity_code=:legalEntityCode "
				+"   AND ((maker='SYSTEM' AND STATUS='DF') OR (STATUS!='DF') )  "
				+"   ORDER BY FIELD(STATUS, 'AS','M') DESC, maker_timestamp DESC  "
				+" ");*/
				"  SELECT * FROM (  "
				+" SELECT  (select concat(a.audit_group_code,'-',a.audit_group_name) from maint_audit_group a where a.audit_type_code=mp.audit_type_code and a.audit_group_code=mp.audit_group_code and a.legal_entity_code=mp.legal_entity_code ) as audit_group_name, "
				+"   (select concat(a.audit_sub_group_code,'-',a.audit_sub_group_name) from maint_audit_subgroup a where a.audit_type_code=mp.audit_type_code and a.audit_group_code=mp.audit_group_code and a.audit_sub_group_code=mp.audit_sub_group_code and  a.legal_entity_code=mp.legal_entity_code ) as audit_sub_group_name, "
				+"   (select concat(a.activity_code,'-',a.activity_name) from maint_audit_activity a where  a.activity_id=mp.activity_id and a.legal_entity_code=mp.legal_entity_code ) as activity_name, "
				+"   (select concat(a.process_code,'-',a.process_name) from maint_audit_process a where a.process_id=mp.process_id and  a.legal_entity_code=mp.legal_entity_code ) as process_name, "
				+"   (select concat( a.finding_code,'-',a.finding_name) from maint_audit_finding a where a.finding_id=mp.finding_id and  a.legal_entity_code=mp.legal_entity_code ) as finding_name, "
				//+"   (select c.criticality_desc from maint_audit_finding a, maint_criticality c where a.f_criticality=c.criticality_code and c.criticality_of_type='F' and a.legal_entity_code=c.legal_entity_code and  a.finding_code=mp.finding_code and  a.legal_entity_code=mp.legal_entity_code ) as finding_criticality, "
				+"  obs.legal_entity_code, "
				+"  (select concat(a.audit_type_code,'-',a.audit_type_desc) from maint_audit_type_desc a where a.audit_type_code=obs.audit_type_code and  a.legal_entity_code=mp.legal_entity_code ) as audit_type_code, "
				+"  obs.plan_id, obs.audit_id, obs.group_observation_id, obs.observation_id, obs.mapping_id, obs.account_no, "    														// obs.account_no -12
				+"  obs.account_name, obs.account_currency, obs.limit_type, obs.limit_amount, obs.limit_currency, obs.balance, obs.date, obs.finding_id, obs.finding_description,  obs.finding_remarks, "  	//  obs.finding_remarks -22
				+ " (Select concat(obs.submission_status , '-' , g.description) from  general_parameter g where g.legal_entity_code =obs.legal_entity_code and g.key_1='STATUS' and g.key_2='STATUS' and g.value=obs.submission_status) as submission_status_1, "  // submission_status -23
				+ " (Select concat(u.user_id , '-' , u.first_name, ' ',u.middle_name,' ',u.last_name) from  user u where u.legal_entity_code =obs.legal_entity_code and u.user_id=obs.finding_owner) as finding_owner, "  //finding_owner -24
				+ " obs.finding_criticality, obs.response_id, obs.response_description, obs.response_remarks, obs.response_owner, "   //  obs.response_owner -29
				+"  obs.response_status, obs.review_id, obs.review_description, obs.review_remarks, obs.review_owner, obs.review_status, obs.remarks, obs.status, obs.entity_status, obs.maker, "    //  obs.maker - 39
				+"  obs.maker_timestamp, obs.checker, obs.checker_timestamp, obs.businessdate_timestamp, obs.sysdate_timestamp,"
				+ " obs.capture_way,obs.capture_type,obs.cust_id, "				//  obs.cust_id - 47
				+ " (Select  g.description from  general_parameter g where g.legal_entity_code =obs.legal_entity_code and g.key_1='OBSERVATION_CAPTURE' and g.key_2='COMPLIANCE_CATEGORY' and g.value=obs.compliance_category) as compliance_category "  // compliance_category -48
				+ " , obs.submission_status " // obs.submission_status - 49 used for ordering purpose
				+"  , (select c.criticality_desc from maint_audit_finding a, maint_criticality c where a.f_criticality=c.criticality_code and c.criticality_of_type='Finding' and a.legal_entity_code=c.legal_entity_code and  a.finding_code=mp.finding_code and  a.legal_entity_code=mp.legal_entity_code ) as finding_criticality_temp "
				+ " from ( "									
				+" SELECT *   FROM audit_observation   "
				+"  WHERE   legal_entity_code=:legalEntityCode AND plan_id=:planId AND audit_id=:auditId AND IFNULL(STATUS,'NotNull') !='D' "   //IFNULL(STATUS,'NotNull') !='D' AND
				+"			AND (STATUS !='ODF' OR ( STATUS ='ODF' and maker =:userId ) )  "   // condition included to restrict Draft records for other users than Makers
				+"  UNION  "
				+"   SELECT * FROM audit_observation_wrk   "
				+"   WHERE legal_entity_code=:legalEntityCode AND plan_id=:planId AND audit_id=:auditId  "
				+"   ) as obs, activity_process_finding_mapping mp "
				+"   where mp.mapping_id = obs.mapping_id and mp.legal_entity_code=obs.legal_entity_code "
				+" ) as obsTempTable ");
		
		System.out.println("search .... "+search);
		if(!BankAuditUtil.isEmptyString(search)){
			queryString=queryString.append(" where ( observation_id like :search "
					+ " or obsTempTable.audit_group_name like :search or obsTempTable.audit_sub_group_name like :search or activity_name like :search or process_name like :search or finding_name like :search "
					//+ " or cust_id like :search or account_no like :search or account_name like :search or account_currency like :search or limit_type like :search or limit_currency like :search "
					+ " or finding_criticality_temp like :search "  // for Loss event type
					//+ " or limit_amount like :search or balance like :search or DATE_FORMAT(date, '%d %b %Y') LIKE :search "
					+ " or submission_status like :search or finding_owner like :search "
					//+ " or ('Accounts' like :search AND capture_way = 'A') or ('Findings' like :search AND capture_way = 'F') "
					//+ " or obsTempTable.compliance_category IN (SELECT g.value FROM general_parameter g WHERE obsTempTable.legal_entity_code =g.legal_entity_code and g.key_1='OBSERVATION_CAPTURE' and g.key_2='COMPLIANCE_CATEGORY' and g.value=obsTempTable.compliance_category and g.description LIKE :search) "
					//+ " or obsTempTable.compliance_category  like :search "
					//+ " or status like :search ) "
					+ " or (select description from general_parameter g where g.legal_entity_code=obsTempTable.legal_entity_code and obsTempTable.submission_status=g.value and g.key_1='STATUS') like :search ) "
					);
		}
		

		String columns[]={
				"(obsTempTable.observation_id *1)",
				"obsTempTable.audit_group_name",
				"obsTempTable.audit_sub_group_name",
				"obsTempTable.activity_name",
				"obsTempTable.process_name",
				"obsTempTable.finding_name",
				"obsTempTable.finding_criticality_temp",
				"obsTempTable.submission_status",
				"obsTempTable.finding_owner"
		};
		
		System.out.println("orderColumn **.... "+ orderColumn +"  orderDirection ...."+ orderDirection );
		
		if(orderColumn != null && orderColumn == -1 && !BankAuditUtil.isEmptyString(orderDirection)){ // -1 send for the first time from the UI during landing page.
			queryString=queryString.append(" ORDER BY FIELD(submission_status, 'DF','R','PA','M','U','A') ASC  "); // The code modified to get the records in the order of Status on landing page, the sequence is  Initiated, Submitted, Respond, Reviewed, Closed
		}else if(orderColumn!=null && !BankAuditUtil.isEmptyString(orderDirection)){
			//if(orderColumn==0) {
				queryString.append("  ORDER BY ").append(columns[orderColumn]).append(" ").append(orderDirection);
				//queryString=queryString.append(" ORDER BY  ").append(columns[orderColumn]).append(" ").append(orderDirection).append(" , FIELD(submission_status, 'DF','M','U','A') DESC  ");
			//}else {
			//	queryString.append("  ORDER BY ").append(columns[orderColumn]).append(" ").append(orderDirection);
			//}
			
		}else {
			queryString.append(" ORDER BY FIELD(submission_status, 'DF','R','PA','M','U','A') ASC ");
		}

		Query query=(Query) session.createSQLQuery(queryString.toString())/*.setResultTransformer(Transformers.aliasToBean(AuditObservationWrk.class))*/		
				.setResultTransformer(new ResultTransformer() {
					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						AuditObservation auditObservation=new AuditObservation();
						//System.out.println("tuple[0] is ...."+tuple[0]);
						auditObservation.setLegalEntityCode(Integer.parseInt(tuple[5].toString()));
						auditObservation.setAuditTypeCode(tuple[6]+"");
						auditObservation.setPlanId(tuple[7]+"");
						auditObservation.setAuditId(tuple[8]+"");
						auditObservation.setGroupObservationId(tuple[9]+"");
						auditObservation.setObservationId(tuple[10]+"");
						
						auditObservation.setMappingId(tuple[11]+"");
						
						if(tuple[12]!=null)auditObservation.setAccountNo(tuple[12]+"");
						if(tuple[13]!=null)auditObservation.setAccountName(tuple[13]+"");
						if(tuple[14]!=null)auditObservation.setAccountCurrency(tuple[14]+"");
						if(tuple[15]!=null)auditObservation.setLimitType(tuple[15]+"");
						if(tuple[16]!=null)auditObservation.setLimitAmount(tuple[16]+"");
						if(tuple[17]!=null)auditObservation.setLimitCurrency(tuple[17]+"");
						if(tuple[18]!=null)auditObservation.setBalance(tuple[18]+"");
						if(tuple[19] != null && !"".equalsIgnoreCase(tuple[19].toString().trim())) 
							auditObservation.setDate(BankAuditUtil.parseStringToDate(tuple[19].toString()));
						if(tuple[20]!=null)auditObservation.setFindingId(tuple[20]+"");
						if(tuple[21]!=null)auditObservation.setFindingDescription(tuple[21]+"");
						if(tuple[22]!=null)auditObservation.setFindingRemarks(tuple[22]+"");
						if(tuple[23]!=null)auditObservation.setSubmissionStatus(tuple[23]+"");
						if(tuple[24]!=null)auditObservation.setFindingOwner(tuple[24]+"");
						if(tuple[36]!=null)auditObservation.setRemarks(tuple[36]+"");
						if(tuple[37]!=null)auditObservation.setStatus(tuple[37]+"");
						if(tuple[45]!=null)auditObservation.setCaptureWay(tuple[45]+"");
						if(tuple[46]!=null)auditObservation.setCaptureType(tuple[46]+"");
						
						if(tuple[47]!=null)	auditObservation.setCustId(tuple[47]+"");
						if(tuple[48]!=null)	auditObservation.setComplianceCategory(tuple[48]+"");
						
						if(tuple[50]!=null)	auditObservation.setFindingCriticality(tuple[50]+"");
						
						if(tuple[0]!=null)auditObservation.setAuditGroupCode(tuple[0]+"");
						if(tuple[1]!=null)auditObservation.setAuditSubGroupCode(tuple[1]+"");
						if(tuple[2]!=null)auditObservation.setActivityCode(tuple[2]+"");
						if(tuple[3]!=null)auditObservation.setProcessCode(tuple[3]+"");
						if(tuple[4]!=null)auditObservation.setFindingCode(tuple[4]+"");
						auditObservation.setMaker(userId);
						//auditObservation.setfCriticality(tuple[5]+"");
						
						return auditObservation;
					}
			@Override
			public List transformList(List collection) {
				return collection;
			}
		})	
				.setParameter("legalEntityCode",legalEntityCode).setParameter("planId",planId).setParameter("auditId",auditId).setParameter("userId", userId);

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

	@Override
	public void deleteObservationByGrpId(String groupObservationId) {
		
		Session session=getSession();
		
		session.createQuery("delete from AuditObservation where groupObservationId =:groupObservationId ")
		.setParameter("groupObservationId", groupObservationId).executeUpdate();
		
	}
	
	@Override
	public void deleteObservationById(String observationId) {
		
		Session session=getSession();
		
		session.createQuery("delete from AuditObservation where observationId =:observationId ")
		.setParameter("observationId", observationId).executeUpdate();
		
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<AuditObservation> getObservationByObservationGroupId(String observationGroupId , Integer legalEntityCode) {
	
		Session session =getSession();
        session.setDefaultReadOnly(true);
		return session.createQuery(" SELECT "
				+ " ao as auditObservation ,"
				
				+ "( select concat(f.auditGroupName , '-' ,f.auditGroupCode )  "
				+ " from  MaintAuditGroup f where "
				+ "  f.auditGroupCode=apfm.auditGroupCode  "
				+ " and f.auditTypeCode=apfm.auditTypeCode  "
				+ " and f.legalEntityCode=apfm.legalEntityCode ) as auditGroupCode , "
				
				
				+ "( select  concat(  f.auditSubGroupName , '-' ,f.auditSubGroupCode )  "
			    + " 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 , "
				
			    
				+ "( select   concat(f.activityName, '-' , f.activityId  )  "
			    + " from  MaintAuditActivity f where "
			    + "  f.activityId=apfm.activityId "
			    + " and f.legalEntityCode=apfm.legalEntityCode ) as activityCode , "
			    
				+ "( select  concat( f.processName , '-' , f.processId )  "
				+ " from  MaintAuditProcess f where "
				+ "  f.processId=apfm.processId  "
				+ " and f.legalEntityCode=apfm.legalEntityCode ) as processCode , "
				
				+ "( select   concat( f.findingName , '-' , f.findingCode   , '-' ,f.fCriticality )  "
				+ " from  MaintAuditFinding f where "
				+ "  f.findingId=apfm.findingId  "
				+ " and f.legalEntityCode=apfm.legalEntityCode ) as findingCode , "
				
				+ " (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 "
				
				+ " from  AuditObservation as ao , ActivityProcessFindingMapping as apfm "
				+ " where ao.groupObservationId =:observationGroupId "
				+ " and ao.legalEntityCode =:legalEntityCode "
				+ " and ao.legalEntityCode=apfm.legalEntityCode "
				+ " AND ao.mappingId = apfm.mappingId"
				+ " and IFNULL(ao.status,'NotNull') !='D' " 
				)
		.setParameter("legalEntityCode", legalEntityCode)
		.setParameter("observationGroupId", observationGroupId)
		.setResultTransformer(new ResultTransformer() {
			
			@Override
			public Object transformTuple(Object[] tuple, String[] aliases) {
				AuditObservation auditObservation=null;
				if(tuple[0]!=null){
					auditObservation=(AuditObservation)tuple[0];
					
					auditObservation.setAuditGroupCode(tuple[1].toString());
					auditObservation.setAuditSubGroupCode(tuple[2].toString());
					auditObservation.setActivityCode(tuple[3].toString());
					auditObservation.setProcessCode(tuple[4].toString());
					auditObservation.setFindingCode(tuple[5].toString());
					if(tuple[6]!=null){
						auditObservation.setSubmissionStatus(tuple[6].toString());
					}
					
				}
				
				return auditObservation;
			}
			
			@Override
			public List transformList(List collection) {
				return collection;
			}
		}).list();
		
		
		
	}

	@Override
	public AuditSchedule getObservationCounts(Integer legalEntityCode, String auditTypeCode, String planId,
			String auditId) {
		
		/****************************************
        		Total Findings = Number of Non Compliance Observations
       			Responded = Number of Response on Non compliance Observations
       			Closed/Reviewed = Number of Review on Non compliance Response
       			Open Finding = Total Findings  (Responded + Rejected + Closed)
       			-> The logic to derive the Rejected, Non Compliance & Not Applicable done during the Audit service.
		 *****************************************/
		
		Session session = getSession();
		session.setDefaultReadOnly(true);
		
		/*select  distinct
		(select count(*) as sub_sts from audit_observation where submission_status='OA' ) as total ,
		(select count(*) as res_sts from audit_observation where response_status='RA' ) as responded,
		(select count(*) as rew_sts from audit_observation where review_status='WA' ) as closed
		from audit_observation where legal_entity_code=3;*/
		
		System.out.println("Inside getObservationCounts .... leCode::"+ legalEntityCode +" auditType:: "+ auditTypeCode + " planId:: "+planId+" auditId:: "+auditId);
		return (AuditSchedule)session.createQuery(" Select distinct "
				+ " (select count(*)  from AuditObservation where submissionStatus ='A' and legalEntityCode =:legalEntityCode and auditTypeCode =:auditTypeCode and planId =:planId and auditId =:auditId and complianceCategory ='N' ) as total , " // complianceCategory ='N' -> condition added as Total No. of finding= Number of Non Compliance Observations
				+ " (select count(*) from AuditObservation where responseStatus='A' and legalEntityCode =:legalEntityCode and auditTypeCode =:auditTypeCode and planId =:planId and auditId =:auditId and complianceCategory ='N' and ifnull(reviewStatus,'N') !='A' ) as responded , "
				+ " (select count(*) from AuditObservation where reviewStatus='A' and legalEntityCode =:legalEntityCode and auditTypeCode =:auditTypeCode and planId =:planId and auditId =:auditId and complianceCategory ='N') as closed, "
				+ " auditStatus "
				+ " from AuditSchedule "
				+ " where legalEntityCode =:legalEntityCode "
				+ " and auditTypeCode =:auditTypeCode "
				+ " and planId =:planId "
				+ " and auditId =:auditId "
				+ " ")
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("planId", planId)
				.setParameter("auditId", auditId)
				.setResultTransformer(new ResultTransformer() {
					
					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						AuditSchedule auditSchedule=new AuditSchedule();
						System.out.println("total finding tup[0] ...."+tuple[0].toString()+" tpp1.."+ tuple[1] +" tpp2.." + tuple[2]);
						auditSchedule.setTotalFindings(Integer.parseInt(tuple[0].toString()));
						auditSchedule.setClosedFindings(Integer.parseInt(tuple[2].toString()));
						auditSchedule.setRespondedFindings(Integer.parseInt(tuple[1].toString()));
						if("AI".equalsIgnoreCase(tuple[2].toString()))auditSchedule.setRectifiedFindings(auditSchedule.getRespondedFindings()); // The observations responded during the Audit period would be considered as, Observations rectified.
						auditSchedule.setOpenFindings(auditSchedule.getTotalFindings()-(auditSchedule.getClosedFindings()+auditSchedule.getRespondedFindings()));
						System.out.println("Closed finding tup[2] ...."+tuple[2].toString()+" Respond fnds::"+ auditSchedule.getRespondedFindings());
						return auditSchedule;
					}
					
					@Override
					public List transformList(List collection) {
						return collection;
					}
				})
				.uniqueResult();
	}

	/*@SuppressWarnings("unchecked")
	@Override
	public List<AuditDocumentDetails> getAuditDocumentDetails(Integer legalEntityCode, String auditTypeCode,
			String planId, String auditId, String observationId, String documentType) {
		Session session =getSession();
		
		session.setDefaultReadOnly(true);
		
		 
		
		return session.createQuery(""
				+ " from AuditDocumentDetails where "
				+ " status =:statusVal "
				+ " and legalEntityCode =:legalEntityCode"
				+ " and auditTypeCode =:auditTypeCode "
				+ " and planId =:planId "
				+ " and auditId =:auditId "
				+ " and observationId =:observationId "
				+ " and documentType in (:documentType) "
				+ "")
				.setParameter("statusVal", BankAuditConstant.STATUS_ACTIVE)
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("planId", planId)
				.setParameter("auditId", auditId)
				.setParameter("observationId", observationId)
				.setParameterList("documentType", documentType.split(","))
				.list();
	}*/

	@Override
	public AuditObservation getAuditObservation(Integer legalEntityCode, String auditTypeCode, String planId,
			String auditId, String groupObservationId, String observationId) {
		
		Session session = getSession();
		
		return (AuditObservation) session.createQuery(" SELECT "
				+ " ao as auditObservation ,"
				
				+ "( 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 , "
				
				
				+ "( 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 , "
				
			    
				+ "( select   concat(f.activityCode , '-' , f.activityName)  "
			    + " from  MaintAuditActivity f where "
			    + "  f.activityId=apfm.activityId "
			    + " and f.legalEntityCode=apfm.legalEntityCode ) as activityCode , "
			    
				+ "( select  concat(f.processCode , '-' , f.processName)  "
				+ " from  MaintAuditProcess f where "
				+ "  f.processId=apfm.processId  "
				+ " and f.legalEntityCode=apfm.legalEntityCode ) as processCode , "
				
				+ "( select   concat(f.findingCode , '-' , f.findingName , '-' ,f.fCriticality )  "
				+ " from  MaintAuditFinding f where "
				+ "  f.findingId=apfm.findingId  "
				+ " and f.legalEntityCode=apfm.legalEntityCode ) as findingCode , "
				
				+ " (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 , "
				
				+ " (select concat(a.auditTypeCode,'-',a.auditTypeDesc) "
				+ " from MaintAuditTypeDesc a where a.auditTypeCode=ao.auditTypeCode "
				+ " and  a.legalEntityCode=ao.legalEntityCode ) as auditTypeCode "
				
				+ " from  AuditObservation as ao , ActivityProcessFindingMapping as apfm "
				+ " where ao.groupObservationId =:groupObservationId "
				+ " and ao.legalEntityCode =:legalEntityCode "
				+ " and ao.legalEntityCode=apfm.legalEntityCode "
				+ " AND ao.mappingId = apfm.mappingId"
				+ " and ao.status != 'D' "
				
				+ " and ao.auditTypeCode =:auditTypeCode "
				+ " and ao.planId =:planId "
				+ " and ao.auditId =:auditId "
				+ " and ao.observationId =:observationId "
				)
		.setParameter("legalEntityCode", legalEntityCode)
		.setParameter("groupObservationId", groupObservationId)
		.setParameter("auditTypeCode", auditTypeCode)
		.setParameter("planId", planId)
		.setParameter("auditId", auditId)
		.setParameter("observationId", observationId)		
		.setResultTransformer(new ResultTransformer() {
			
			@Override
			public Object transformTuple(Object[] tuple, String[] aliases) {
				AuditObservation auditObservation=null;
				if(tuple[0]!=null){
					auditObservation=(AuditObservation)tuple[0];
					
					auditObservation.setAuditGroupCode(tuple[1].toString());
					auditObservation.setAuditSubGroupCode(tuple[2].toString());
					auditObservation.setActivityCode(tuple[3].toString());
					auditObservation.setProcessCode(tuple[4].toString());
					auditObservation.setFindingCode(tuple[5].toString());
					if(tuple[6]!=null){
						auditObservation.setSubmissionStatus(tuple[6].toString());
					}
					auditObservation.setAuditTypeCode(tuple[7].toString());
					
				}
				
				return auditObservation;
			}
			
			@Override
			public List transformList(List collection) {
				return collection;
			}
		}).uniqueResult();
		
		
	
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<AuditObservationRespondReviewCapture> getAuditObservationRespondReviewCaptureByObservation(
			Integer legalEntityCode, String auditTypeCode, String planId, String auditId, String groupObservationId,
			String observationId) {
	
		
		Session session =getSession();
		
		return session.createQuery( 
				" from AuditObservationRespondReviewCapture  "
				+ " where "
				+ " legalEntityCode =:legalEntityCode "
				+ " and  auditTypeCode =:auditTypeCode "
				+ " and planId =:planId "
				+ " and auditId =:auditId "
				+ " and observationId =:observationId "
				+ " and groupObservationId =:groupObservationId "
				+ " order by id asc "
				+ " ")
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("planId", planId)
				.setParameter("auditId", auditId)
				.setParameter("groupObservationId", groupObservationId)
				.setParameter("observationId", observationId)
				.list();

	}

	@Override
	public void deleteById(AuditObservation auditObservationStored) {
		

		
		Session session = getSession();
		
		 session.createQuery( 
				" delete from  AuditObservation ao "
				+ " where ao.groupObservationId =:groupObservationId "
				+ " and ao.legalEntityCode =:legalEntityCode "
				
				+ " and ao.auditTypeCode =:auditTypeCode "
				+ " and ao.planId =:planId "
				+ " and ao.auditId =:auditId "
				+ " and ao.observationId =:observationId "
				)
		.setParameter("legalEntityCode", auditObservationStored.getLegalEntityCode())
		.setParameter("groupObservationId", auditObservationStored.getGroupObservationId())
		.setParameter("auditTypeCode", auditObservationStored.getAuditTypeCode())
		.setParameter("planId", auditObservationStored.getPlanId())
		.setParameter("auditId", auditObservationStored.getAuditId())
		.setParameter("observationId", auditObservationStored.getObservationId()).executeUpdate();	
			
	}
	
	@Override
	public Map<String, String> validateAccObsMapping(Integer legalEntityCode,String auditTypeCode,String planId,String auditId,String custId,String accountNo,String captureType,String mappingId,String miscType) {
		
		Session session = getSession();
		Map<String, String> map= null;
		try {   
			
			StringBuilder qryString= new StringBuilder(	"FROM  AuditObservation ao "
				+ " WHERE ao.legalEntityCode =:legalEntityCode "
				+ " and ao.auditTypeCode =:auditTypeCode "
				+ " and ao.planId =:planId "
				+ " and ao.auditId =:auditId "
				+ " and ao.custId =:custId and ao.accountNo =:accountNo  and mappingId =:mappingId "
				);
				
			if(!BankAuditUtil.isEmptyString(captureType)){
				qryString =qryString.append(" and captureType =:captureType ");
			}
			if(!BankAuditUtil.isEmptyString(miscType)){
				qryString =qryString.append(" and limitType =:miscType ");
			}
			Query query=(Query) session.createQuery(qryString.toString())
			.setParameter("legalEntityCode", legalEntityCode)
			.setParameter("auditTypeCode", auditTypeCode)
			.setParameter("planId", planId)
			.setParameter("auditId", auditId)
			.setParameter("custId", custId)
			.setParameter("accountNo", accountNo)
			.setParameter("mappingId", mappingId);	
			
			if(!BankAuditUtil.isEmptyString(captureType)){
				query.setParameter("captureType", captureType);
			}
			if(!BankAuditUtil.isEmptyString(miscType)){
				query.setParameter("miscType", miscType);
			}
		
		List lst = query.list();
		logger.info("ls .."+lst);
		for ( Iterator iter = lst.iterator(); iter.hasNext(); ) {
			map = new HashMap();
			AuditObservation adtObs = (AuditObservation) iter.next();
			map.put("ObservationId", adtObs.getObservationId()); 
			map.put("GrpObservationId", adtObs.getGroupObservationId());
			map.put("AlreadyExist", "Yes");
		}
		logger.info("map .."+map);
		}catch(Exception e) {
			
		}
		return map;
	
	}
	
	@Override
	public List<AuditObservation> getAllPossibleRiskFromMapping(int legalEntityCode, String auditTypeCode, String auditId, String planId, String userId){
		
		Session session = getSession();
		List<AuditObservation> auditObservationLst =null;
		logger.info("Inside DaoImpl getAllPossibleRiskFromMapping .. "+ legalEntityCode +" auditTypeCode.. "+ auditTypeCode + " auditId.."+ auditId + " planId.."+ planId + " userId.." + userId);
		try {   
			StringBuilder queryStr1=new StringBuilder(" SELECT e.levelCode from AuditSchedule sch, MaintEntity e  WHERE "
					+" sch.legalEntityCode = e.legalEntityCode AND e.unitCode=sch.unitId AND"
					+" e.legalEntityCode=:legalEntityCode AND sch.auditId=:auditId   ");
			
			String entityLevelCode=(String)session.createQuery(queryStr1.toString())
					.setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId)
					.uniqueResult();
			
			logger.info("getAllPossibleRiskFromMapping entiryLevel for riskBelongs QRY is :: "+ entityLevelCode);
			
			
			StringBuilder qryString= new StringBuilder(	" select m.mapping_id, m.finding_id "
					+ " from activity_process_finding_mapping m, risk_summary_dtls r  " 
					+ " where r.legal_entity_code=m.legal_entity_code and m.audit_type_code=r.audit_type_code " 
					+ " 	and r.checked='Y' and r.audit_id=:auditId and m.audit_type_code =:auditTypeCode  and r.legal_entity_code=:legalEntityCode "
					+ " 	and m.entity_status='A' and m.audit_group_code=r.audit_group_code and m.audit_sub_group_code=r.audit_sub_group_code "
					+ " 	AND ( m.risk_belongs_to= '3' OR m.risk_belongs_to =:entityLevelCode OR ifnull(m.risk_belongs_to,'0') = '0' ) "
				);
				
			Query query=session.createNativeQuery(qryString.toString())
					.setResultTransformer(new ResultTransformer() {
						
						@Override
						public Object transformTuple(Object[] tuple, String[] aliases) {
							AuditObservation auditObservation=new AuditObservation();
							auditObservation.setLegalEntityCode(legalEntityCode);
							auditObservation.setAuditTypeCode(auditTypeCode);
							auditObservation.setPlanId(planId);
							auditObservation.setAuditId(auditId);
							//auditObservation.setGroupObservationId(sequenceAppenderService.getAutoSequenceId());
							//auditObservation.setObservationId(sequenceAppenderService.getAutoSequenceId());
							auditObservation.setMappingId(tuple[0].toString());
							auditObservation.setFindingId(tuple[1].toString());
							auditObservation.setSubmissionStatus("U");
							auditObservation.setStatus("U");
							auditObservation.setMaker(userId);
							
							return auditObservation;
						}
						
						@Override
						public List transformList(List collection) {
							return collection;
						}
					})
					.setParameter("legalEntityCode", legalEntityCode).setParameter("auditTypeCode", auditTypeCode)
					.setParameter("auditId", auditId).setParameter("entityLevelCode", entityLevelCode);
			
			auditObservationLst = query.list();
			logger.info("auditObservationLst .... "+ auditObservationLst);
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		return auditObservationLst;
	
	}
	
	//** Code to get all the Risk of the Previous Assessment
	@Override
	public List<AuditObservation> getAllPossibleRiskFromPrevAssessment(int legalEntityCode, String auditTypeCode, String auditId, String planId, String userId){
		
		Session session = getSession();
		List<AuditObservation> auditObservationLst =null;
		String entityLevelCode =null;
		logger.info("Inside DaoImpl getAllPossibleRiskFromPrevAssessment .. "+ legalEntityCode +" auditTypeCode.. "+ auditTypeCode + " auditId.."+ auditId + " planId.."+ planId + " userId.." + userId);
		
		try {
			StringBuilder queryStr1=new StringBuilder(" SELECT e.levelCode from AuditSchedule sch, MaintEntity e  WHERE "
					+" sch.legalEntityCode = e.legalEntityCode AND e.unitCode=sch.unitId AND"
					+" e.legalEntityCode=:legalEntityCode AND sch.auditId=:auditId   ");
			
			entityLevelCode=(String)session.createQuery(queryStr1.toString())
					.setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId)
					.uniqueResult();
			logger.info("getAllPossibleRiskFromPrevAssessment QRY is :: "+ entityLevelCode);
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}		
		try {   
			
			StringBuilder qryString= new StringBuilder(	"  "
					+ " SELECT  mr.mapping_id, mr.finding_id, o.finding_description,o.finding_criticality," // mapping_id -0,   finding_criticality -3
					+ "      o.prob_code,o.severity_code,o.design_eff_code,o.oprting_eff_code,"  // prob_code -4,   oprting_eff_code -7
					+ "      o.impact_code,o.risk_frequency_code,o.control_frequency_code "  // impact_code-8, control_frequency_code -10
					+ "		 ,o.observation_id, o.audit_id  "  // o.observation_id -11
					+ " FROM "
					+ "     (SELECT m.mapping_id, m.finding_id, m.legal_entity_code "
					+ " 		FROM activity_process_finding_mapping m, risk_summary_dtls r "
					+ " 		WHERE m.legal_entity_code = m.legal_entity_code "
					+ "             AND m.audit_type_code = r.audit_type_code "
					+ "             AND r.checked = 'Y' "
					+ "             AND r.audit_id =:auditId "
					+ "             AND m.audit_type_code =:auditTypeCode "
					+ "             AND r.legal_entity_code =:legalEntityCode "
					+ "             AND m.entity_status = 'A' "
					+ "             AND m.audit_group_code = r.audit_group_code "
					+ "             AND m.audit_sub_group_code = r.audit_sub_group_code"
					+ "				AND ( m.risk_belongs_to= '3' OR m.risk_belongs_to =:entityLevelCode OR ifnull(m.risk_belongs_to,'0') = '0' )"
					+ "		) AS mr "
					+ "         LEFT OUTER JOIN "
					+ "     audit_observation o ON o.mapping_id = mr.mapping_id "
					+ "         AND o.finding_id = mr.finding_id  "
					+ "         AND o.audit_id = (SELECT sch.prev_audit_id FROM audit_schedule sch WHERE legal_entity_code =:legalEntityCode "
					+ "											AND o.legal_entity_code=sch.legal_entity_code AND sch.audit_id =:auditId) "
					+ " WHERE "
					+ "     mr.legal_entity_code =:legalEntityCode "
				);
				
			Query query=session.createNativeQuery(qryString.toString())
					.setResultTransformer(new ResultTransformer() {
						
						@Override
						public Object transformTuple(Object[] tuple, String[] aliases) {
							AuditObservation auditObservation=new AuditObservation();
							auditObservation.setLegalEntityCode(legalEntityCode);
							auditObservation.setAuditTypeCode(auditTypeCode);
							auditObservation.setPlanId(planId);
							auditObservation.setAuditId(auditId);
							//auditObservation.setGroupObservationId(sequenceAppenderService.getAutoSequenceId());
							//auditObservation.setObservationId(sequenceAppenderService.getAutoSequenceId());
							auditObservation.setMappingId(tuple[0].toString());
							auditObservation.setFindingId(tuple[1].toString());
							if(tuple[2] != null)auditObservation.setFindingDescription(tuple[2].toString());
							if(tuple[3] != null)auditObservation.setFindingCriticality(tuple[3].toString());
							//auditObservation.setProbCode(tuple[4].toString());
							//auditObservation.setSeverityCode(tuple[5].toString());
							//auditObservation.setDesignEffCode(tuple[6].toString());
							//auditObservation.setOprtingEffCode(tuple[7].toString());
							if(tuple[8] != null)auditObservation.setImpactCode(tuple[8].toString());
							if(tuple[9] != null)auditObservation.setRiskFrequencyCode(tuple[9].toString());
							if(tuple[10] != null)auditObservation.setControlFrequencyCode(tuple[10].toString());
							if(tuple[11] != null)auditObservation.setTmpObservationId(tuple[11].toString()); // this code added to include the Obs Id for Risk control and Control gap auto insert
							if(tuple[12] != null)auditObservation.setPrevAuditId(tuple[12].toString()); // this code added to include the Prev Audit Id for Risk control and Control gap auto insert
							auditObservation.setSubmissionStatus("U");
							auditObservation.setStatus("U");
							auditObservation.setMaker(userId);
							
							return auditObservation;
						}
						
						@Override
						public List transformList(List collection) {
							return collection;
						}
					})
					.setParameter("legalEntityCode", legalEntityCode).setParameter("auditTypeCode", auditTypeCode)
					.setParameter("auditId", auditId).setParameter("entityLevelCode", entityLevelCode);
			
			auditObservationLst = query.list();
			logger.info("auditObservationLst .... "+ auditObservationLst);
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		return auditObservationLst;
	
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public AuditObservation getRiskDtlsByRiskId(Integer legalEntityCode, String auditId, String observationId, String userId, String status) { 
	
		Session session =getSession();
        session.setDefaultReadOnly(true);
        logger.info("Inside getRiskByAssmntId .. .."+ legalEntityCode +" auditId.. "+ auditId+" observationId.. "+observationId +" userId .."+userId +" status.. "+status);
        AuditObservation auditObservation =null;
        try {
        	auditObservation=  (AuditObservation)session.createQuery(" SELECT "
				+ " ao as auditObservation ,"
				
				+ "( 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 , "
				
				
				+ "( 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 , "
				
			    
				+ "( select   concat(f.activityId , '-' , f.activityName )  "
			    + " from  MaintAuditActivity f where "
			    + "  f.activityId=apfm.activityId "
			    + " and f.legalEntityCode=apfm.legalEntityCode ) as activityCode , "
			    
				+ "( select  concat( f.processId, '-' , f.processName  )  "
				+ " from  MaintAuditProcess f where "
				+ "  f.processId=apfm.processId  "
				+ " and f.legalEntityCode=apfm.legalEntityCode ) as processCode , "
				
				+ "( select   concat(f.findingCode , '--' ,f.findingName , '--' ,f.fCriticality,'-',c.criticalityDesc )  "
				+ " from  MaintAuditFinding f, MaintCriticality c where "
				+ "  f.findingId=apfm.findingId  AND f.fCriticality=c.criticalityCode AND c.criticalityOfType='Finding' "
				+ "  AND f.legalEntityCode=c.legalEntityCode AND f.legalEntityCode=apfm.legalEntityCode ) as findingCode , "
				
				+ " (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 "
				
				+ " ,(select concat(ps.probabltyScaleCode , '--' , ps.probabltyScaleType, '-', ps.probabltyScaleDesc)"
				+ " from MaintProbabilityScale ps where "
				+ " ps.probabltyScaleCode=ao.probCode and ps.legalEntityCode=ao.legalEntityCode ) as probCode, "
				
				+ " (select concat(ss.severityScaleCode , '--' , ss.severityScaleType, '-', ss.severityScaleDesc)"
				+ " from MaintSeverityScale ss where "
				+ " ss.severityScaleCode=ao.severityCode  and ss.legalEntityCode=ao.legalEntityCode ) as severityCode, "
				
				+ " (select concat(eff.controlDesignEffCode , '--' , eff.controlDesignEffType,'-' , eff.controlDesignEffDesc)"
				+ " from MaintControlDesignEffectiveness eff where "
				+ " eff.controlDesignEffCode=ao.designEffCode  and eff.legalEntityCode=ao.legalEntityCode ) as designEffCode, "
				
				+ " (select concat(op.controlOperatingEffCode , '--' , op.controlOperatingEffType,'-' , op.controlOperatingEffDesc)"
				+ " from MaintControlOperatingEffectiveness op where "
				+ " op.controlOperatingEffCode=ao.oprtingEffCode  and op.legalEntityCode=ao.legalEntityCode ) as oprtingEffCode, "
				
				+ " (select concat(gp.value , '-' , gp.description)"
				+ " from GeneralParameter gp where "
				+ " gp.key1='FREQUENCY' and gp.key2='RISK' and gp.value=ao.riskFrequencyCode "
				+ " and gp.legalEntityCode=ao.legalEntityCode ) as riskFrequencyCode, "
				
				+ " (select concat(gp.value , '-' , gp.description)"
				+ " from GeneralParameter gp where "
				+ " gp.key1='FREQUENCY' and gp.key2='CONTROL' and gp.value=ao.controlFrequencyCode "
				+ " and gp.legalEntityCode=ao.legalEntityCode ) as controlFrequencyCode, "
				
				+ " (select concat(gp.value , '-' , gp.description)"
				+ " from GeneralParameter gp where "
				+ " gp.key1='TYPE_OF_IMPACT' and gp.key2='TYPE_OF_IMPACT' and gp.value=ao.impactCode "
				+ " and gp.legalEntityCode=ao.legalEntityCode ) as impactCode "
				
				+ " ,(select concat(ifnull(sch.actualStartDate,' ') , '--' , ifnull(sch.actualEndDate,' ') )"
				+ " from AuditSchedule sch where "
				+ " sch.legalEntityCode=ao.legalEntityCode  and sch.auditId=ao.auditId and sch.auditTypeCode=ao.auditTypeCode ) as actualStartDate "
				
				+ " from  AuditObservation as ao , ActivityProcessFindingMapping as apfm "
				+ " where ao.observationId =:observationId "
				+ " and ao.auditId =:auditId "
				+ " and ao.legalEntityCode =:legalEntityCode "
				+ " and ao.legalEntityCode=apfm.legalEntityCode "
				+ " AND ao.mappingId = apfm.mappingId"
				+ " and IFNULL(ao.status,'NotNull') !='D' "
				)
		.setParameter("legalEntityCode", legalEntityCode)
		.setParameter("auditId", auditId)
		.setParameter("observationId", observationId)
		.setResultTransformer(new ResultTransformer() {
			
			@Override
			public Object transformTuple(Object[] tuple, String[] aliases) {
				AuditObservation auditObservation=null;
				if(tuple[0]!=null){
					auditObservation=(AuditObservation)tuple[0];
					
					auditObservation.setAuditGroupCode(tuple[1].toString());
					auditObservation.setAuditSubGroupCode(tuple[2].toString());
					auditObservation.setActivityCode(tuple[3].toString());
					auditObservation.setProcessCode(tuple[4].toString());
					if(tuple[5]!=null) {
						auditObservation.setFindingCode(tuple[5].toString().split("--")[0]);
						auditObservation.setFindingDescription(tuple[5].toString().split("--")[0]+"_"+tuple[5].toString().split("--")[1]);
						auditObservation.setFindingCriticality(tuple[5].toString().split("--")[2]);
						auditObservation.setfCriticality(tuple[5].toString().split("--")[2]);
					}
					if(tuple[6]!=null){
						auditObservation.setSubmissionStatus(tuple[6].toString());
					}
					if(tuple[7]!=null) {
						auditObservation.setProbCode(tuple[7].toString().split("--")[0]);
						auditObservation.setProbDesc(tuple[7].toString().split("--")[1]);
					}
					if(tuple[8]!=null) {
						auditObservation.setSeverityCode(tuple[8].toString().split("--")[0]);
						auditObservation.setSeverityDesc(tuple[8].toString().split("--")[1]);
					}
					if(tuple[9]!=null) {
						auditObservation.setDesignEffCode(tuple[9].toString().split("--")[0]);
						auditObservation.setDesignEffDesc(tuple[9].toString().split("--")[1]);
					}
					if(tuple[10]!=null) {
						auditObservation.setOprtingEffCode(tuple[10].toString().split("--")[0]);
						auditObservation.setOprtingEffDesc(tuple[10].toString().split("--")[1]);
					}
					if(tuple[11]!=null) {
						auditObservation.setRiskFrequencyCode(tuple[11].toString().split("-")[0]);
						auditObservation.setRiskFrequencyDesc(tuple[11].toString().split("-")[1]);
						
					}
					if(tuple[12]!=null) {
						auditObservation.setControlFrequencyCode(tuple[12].toString().split("-")[0]);
						auditObservation.setControlFrequencyDesc(tuple[12].toString().split("-")[1]);
					}
					if(tuple[13]!=null) {
						auditObservation.setImpactCode(tuple[13].toString().split("-")[0]);
						auditObservation.setImpactDesc(tuple[13].toString().split("-")[1]);
					}
					logger.info(" .... "+tuple[14].toString());
					if(tuple[14]!=null) {
						auditObservation.setActualStartDate(tuple[14].toString().split("--")[0]);
						auditObservation.setActualEndDate(tuple[14].toString().split("--")[1]);
					}
				}
				
				return auditObservation;
			}
			
			@Override
			public List transformList(List collection) {
				return collection;
			}
		}).uniqueResult();
		
        }catch(Exception e) {
        	e.printStackTrace(System.out);
        }
        return auditObservation;		
	}
	
	//************************ BaaS DOS Implementation changes ************************************//
	@Override
	public List<AuditObservation> getAllPossibleCheckListFromMapping(int legalEntityCode, String auditTypeCode, String auditId, String planId, String userId){
		
		Session session = getSession();
		List<AuditObservation> auditObservationLst =null;
		logger.info("Inside DaoImpl getAllPossibleCheckListFromMapping .. "+ legalEntityCode +" auditTypeCode.. "+ auditTypeCode + " auditId.."+ auditId + " planId.."+ planId + " userId.." + userId);
		try {   
			/*StringBuilder queryStr1=new StringBuilder(" SELECT e.levelCode from AuditSchedule sch, MaintEntity e  WHERE "
					+" sch.legalEntityCode = e.legalEntityCode AND e.unitCode=sch.unitId AND"
					+" e.legalEntityCode=:legalEntityCode AND sch.auditId=:auditId   ");
			String entityLevelCode=(String)session.createQuery(queryStr1.toString())
					.setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId)
					.uniqueResult();
			logger.info("getAllPossibleRiskFromMapping entiryLevel for riskBelongs QRY is :: "+ entityLevelCode);*/
			
			
			StringBuilder qryString= new StringBuilder(	" select m.mapping_id, m.finding_id "
					+ " from activity_process_finding_mapping m, risk_summary_dtls r  " 
					+ " where r.legal_entity_code=m.legal_entity_code and m.audit_type_code=r.audit_type_code " 
					+ " 	and r.checked='Y' and r.audit_id=:auditId and m.audit_type_code =:auditTypeCode  and r.legal_entity_code=:legalEntityCode "
					+ " 	and m.entity_status='A' and m.audit_group_code=r.audit_group_code and m.audit_sub_group_code=r.audit_sub_group_code "
					+ "		AND m.activity_code=r.activity_code "
					//+ " 	AND ( m.risk_belongs_to= '3' OR m.risk_belongs_to =:entityLevelCode OR ifnull(m.risk_belongs_to,'0') = '0' ) "
				);
				
			Query query=session.createNativeQuery(qryString.toString())
					.setResultTransformer(new ResultTransformer() {
						
						@Override
						public Object transformTuple(Object[] tuple, String[] aliases) {
							AuditObservation auditObservation=new AuditObservation();
							auditObservation.setLegalEntityCode(legalEntityCode);
							auditObservation.setAuditTypeCode(auditTypeCode);
							auditObservation.setPlanId(planId);
							auditObservation.setAuditId(auditId);
							//auditObservation.setGroupObservationId(sequenceAppenderService.getAutoSequenceId());
							//auditObservation.setObservationId(sequenceAppenderService.getAutoSequenceId());
							auditObservation.setMappingId(tuple[0].toString());
							auditObservation.setFindingId(tuple[1].toString());
							auditObservation.setSubmissionStatus("U");
							auditObservation.setStatus("U");
							auditObservation.setMaker(userId);
							
							return auditObservation;
						}
						
						@Override
						public List transformList(List collection) {
							return collection;
						}
					})
					.setParameter("legalEntityCode", legalEntityCode).setParameter("auditTypeCode", auditTypeCode)
					.setParameter("auditId", auditId);  //.setParameter("entityLevelCode", entityLevelCode)
			
			auditObservationLst = query.list();
			logger.info("auditObservationLst .... "+ auditObservationLst);
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		return auditObservationLst;
	
	}
	
	@Override
	public  AuditObservation getInspectionObservation(Integer legalEntityCode,String auditId, String auditTypeCode, String groupObservationId, String observationId){
		
		logger.info("Inside getInspectionObservation .."+legalEntityCode+" :: "+ auditId+" :: "+ auditTypeCode +" :: "+observationId);
		Session session =getSession();		
		/*session.setDefaultReadOnly(true);
		Criteria criteria = session.createCriteria(AuditObservation.class);
		criteria.add(Restrictions.eq("legalEntityCode", legalEntityCode));
		criteria.add(Restrictions.eq("auditId", auditId));
		criteria.add(Restrictions.eq("auditTypeCode", auditTypeCode));
		criteria.add(Restrictions.eq("observationId", observationId));
		List<AuditObservation> listAuditObservation = criteria.list();
		logger.info("listAuditObservation .. "+ listAuditObservation);
		if(listAuditObservation.size()>0 && !listAuditObservation.isEmpty()){
			return listAuditObservation.get(0);
		}
		return null;*/
		AuditObservation auditObservation= null;
		try {
		auditObservation=(AuditObservation)(session.createQuery(" FROM AuditObservation obs where obs.legalEntityCode=:legalEntityCode "
				+ " AND obs.auditId=:auditId AND obs.auditTypeCode=:auditTypeCode AND obs.observationId=:observationId "))
				.setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId)
				.setParameter("auditTypeCode", auditTypeCode).setParameter("observationId", observationId).uniqueResult();  
		logger.info("getInspectionObservation inside .. "+ auditObservation);
		}catch(Exception e) {
			e.printStackTrace();
		}logger.info("auditObservation .. "+ auditObservation);
		return auditObservation;
		
	}
	
	
	 
		@Override
		public void deleteAuditObservationReportReviewCaptureWrk(AuditObservationReportReviewCaptureWrk auditObservationReportReviewCaptureWrk )//Integer legalEntityCode,String auditId, String auditTypeCode, //String groupObservationId, String observationId, String planId)
		{
			logger.info("Inside deleteAuditObservationReportReviewCaptureWrk ..");
			Session session =getSession();	
			
			session.createQuery("delete from AuditObservationReportReviewCaptureWrk where "
					+ " legalEntityCode =:legalEntityCode "
					+ " and auditId =:auditId "
					+ " and auditTypeCode =:auditTypeCode "
					+ " and groupObservationId =:groupObservationId "
					+ " and observationId =:observationId "
					+ " and planId =:planId ")
			.setParameter("legalEntityCode", auditObservationReportReviewCaptureWrk.getLegalEntityCode())
			.setParameter("auditId", auditObservationReportReviewCaptureWrk.getAuditId())
			.setParameter("auditTypeCode", auditObservationReportReviewCaptureWrk.getAuditTypeCode())
			.setParameter("groupObservationId", auditObservationReportReviewCaptureWrk.getGroupObservationId())
			.setParameter("observationId", auditObservationReportReviewCaptureWrk.getObservationId())
			.setParameter("planId", auditObservationReportReviewCaptureWrk.getPlanId())
			.executeUpdate();
		}
		
		@Override
		public void deleteAllAuditObservationReportReviewCaptureWrk(Integer legalEntityCode,String auditId){
			
			logger.info("Inside deleteAllAuditObservationReportReviewCaptureWrk .."+legalEntityCode+" :: "+auditId);
			Session session =getSession();			
			session.createQuery("delete from AuditObservationReportReviewCaptureWrk where "
					+ " legalEntityCode =:legalEntityCode "
					+ " and auditId =:auditId ")
			.setParameter("legalEntityCode", legalEntityCode)
			.setParameter("auditId", auditId)		 
			.executeUpdate();
		}
		 
		@Override
		public List<AuditObservationReportReviewCaptureWrk> getAuditObservationReportReviewCaptureWrk(Integer legalEntityCode,String auditId){
			
			logger.info("Inside getAuditObservationReportReviewCaptureWrk .."+legalEntityCode+" :: "+auditId);
			Session session =getSession();			
			Query query = session.createQuery("from AuditObservationReportReviewCaptureWrk where "
					+ " legalEntityCode =:legalEntityCode "
					+ " and auditId =:auditId "
					+ " and (comment!='' OR comment is not null)")
			.setParameter("legalEntityCode", legalEntityCode)
			.setParameter("auditId", auditId);
			
			List<AuditObservationReportReviewCaptureWrk> listAuditObservationReportReviewCaptureWrk = query.list();
			return listAuditObservationReportReviewCaptureWrk;
		}
		
		@Override
		public List<AuditObservationReportReviewCapture> getObservationReportReviewCaptureRemark(String legalEntityCode, String observationId ){
			logger.info("Inside getObservationReportReviewCapture .."+legalEntityCode+" :: "+observationId);
			Session session =getSession();			
			Query query = session.createQuery("from AuditObservationReportReviewCapture where "
					+ " legalEntityCode =:legalEntityCode "
					+ " and observationId =:observationId ")
			.setParameter("legalEntityCode", legalEntityCode)
			.setParameter("observationId", observationId);
			
			List<AuditObservationReportReviewCapture> listAuditObservationReportReviewCapture = query.list();
			return listAuditObservationReportReviewCapture;
		}
		
		/*public String deleteIndividualObservationDocument(Integer documentId){
			logger.info("Inside deleteIndividualObservationDocument .." );
			try{
				String deleteQuery="Update AuditDocumentDetails auditDocumentDetails set auditDocumentDetails.status='"+BankAuditConstant.STATUS_DEL+"' where"
				+ " id =:documentId  " ;				
				Session session =getSession();			
				Query query = session.createQuery(deleteQuery)
				.setParameter("documentId", documentId);				 
				query.executeUpdate();
				return BankAuditConstant.SUCCESS;
			}catch (Exception e) {
				logger.error("Error .." + e.getMessage() +" :: "+e.getCause());
				return BankAuditConstant.FAILED;
			}
		}*/
		
		/*@Override
		public String deleteAllObservationDocument(Integer legalEntityCode, String auditTypeCode,
				String planId, String auditId, String observationId, String documentType){
			
			logger.info("Inside deleteAllObservationDocument .." );
			try{
				String deleteQuery="Update AuditDocumentDetails auditDocumentDetails set auditDocumentDetails.status='"+BankAuditConstant.STATUS_DEL+"' where"
				+ " legalEntityCode =:legalEntityCode"
				+ " and auditTypeCode =:auditTypeCode "
				+ " and planId =:planId "
				+ " and auditId =:auditId "
				+ " and observationId =:observationId "
				+ " and documentType in (:documentType) " ;
				
				Session session =getSession();			
				Query query = session.createQuery(deleteQuery)
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("planId", planId)
				.setParameter("auditId", auditId)
				.setParameter("observationId", observationId)
				.setParameterList("documentType", documentType.split(","));
				query.executeUpdate();
				return BankAuditConstant.SUCCESS;
			}catch (Exception e) {
				logger.error("Error .." + e.getMessage() +" :: "+e.getCause());
				return BankAuditConstant.FAILED;
			}
		}*/
		
	//*********************************** End Here **********************************************/

	/***********************For Notes Start Here*********************/
		@Override
		public	Boolean checkIfLatestNoteIsGivenBySameUser(AuditObservation auditObservation){
			 
			logger.info("Inside checkIfLatestNoteIsGivenBySameUser ..");
			Session session =getSession();
			session.flush();			
			
			StringBuilder qryString = new StringBuilder( "Select observationCapture from AuditObservationReportReviewCapture observationCapture where "
					+ " legalEntityCode =:legalEntityCode "					
					+ " and observationId =:observationId "
					+ " and  auditTypeCode=:auditTypeCode "
					+ " and  planId=:planId "
					+ " and  auditId=:auditId " 
					+ " and  (maker!='' OR maker is not null) "
					+ " and  (comment!='' OR comment is not null)"
					+ " order by makerTimestamp DESC "  );		 
			
			Query query = session.createQuery(qryString.toString())		
			.setParameter("legalEntityCode", auditObservation.getLegalEntityCode())
			.setParameter("observationId", auditObservation.getObservationId()) 
			.setParameter("auditTypeCode", auditObservation.getAuditTypeCode())
			.setParameter("planId", auditObservation.getPlanId())
			.setParameter("auditId", auditObservation.getAuditId())
			//.setFirstResult(0)
			.setMaxResults(1);
			
			List<AuditObservationReportReviewCapture> list = query.list();
			 
			
			String maker="";			 
			List<String> rolesListInspector = Arrays.asList(("G2,G8,G12").split(","));  //Inspection
			List<String> rolesListCompliance = Arrays.asList(("G3,G31").split(","));	//Compliance or Response
			List<String> rolesListReviewer = Arrays.asList(("G4,G7").split(","));       //reviewer(scrutiny)			
			String role = auditObservation.getRole();
			 
			if(rolesListInspector.contains(role)){				  		 
				maker = auditObservation.getObservationNoteMaker();
			}
			else if(rolesListCompliance.contains(role)){			  
				maker = auditObservation.getResponseNoteMaker();	
			}
			else if(rolesListReviewer.contains(role)){			  
				maker = auditObservation.getReviewNoteMaker();
			} else {
				return false;
			} 	
			if(!list.isEmpty() && list.size()>0 ){
				AuditObservationReportReviewCapture observationCapture =  list.get(0);
				String isCurrentCycle =  observationCapture.getIsCurrentCycle();
				 if(observationCapture != null  
						 && !BankAuditUtil.isEmptyString(observationCapture.getMaker())
						 && isCurrentCycle != null 
						 && observationCapture.getMaker().equalsIgnoreCase(maker) 
						 && isCurrentCycle.equalsIgnoreCase(BankAuditConstant.YES) ){
					 return true;
				 }
			}
			
			return false;				
					
			/*String maker="";		 
			 StringBuilder qryString = new StringBuilder( "Select legalEntityCode from AuditObservation  where "
			+ " legalEntityCode =:legalEntityCode "
			+ " and observationId =:observationId "); 
					
			 List<String> rolesListInspector = Arrays.asList(("G2,G8,G12").split(","));  //Inspection
			List<String> rolesListCompliance = Arrays.asList(("G3,G31").split(","));	//Compliance or Response
			List<String> rolesListReviewer = Arrays.asList(("G4,G7").split(","));       //reviewer(scrutiny)			
			String role = auditObservation.getRole();
			 
			if(rolesListInspector.contains(role)){				  		
				qryString.append("and observationNoteMaker = :noteMaker");
				maker = auditObservation.getObservationNoteMaker();
			}
			else if(rolesListCompliance.contains(role)){			 
				qryString.append("and responseNoteMaker = :noteMaker");
				maker = auditObservation.getResponseNoteMaker();	
			}
			else if(rolesListReviewer.contains(role)){			 
				qryString.append("and reviewNoteMaker = :noteMaker");
				maker = auditObservation.getReviewNoteMaker();
			} else {
				return false;
			} 	
			
			Query query = session.createQuery(qryString.toString())		
					.setParameter("legalEntityCode", auditObservation.getLegalEntityCode())
					.setParameter("observationId", auditObservation.getObservationId()) 
					.setParameter("noteMaker", maker);
			
			List list = query.list();

			if(list.isEmpty() || list.size()<=0 || list==null){
				return false;
			}else{
				return true;
			}	
				*/
			
				 			
				
			/*if(auditObservation.getObservationNote()!=null && !auditObservation.getObservationNote().equals("")){
				qryString.append("and observationNoteMaker = :noteMaker");
				maker = auditObservation.getObservationNoteMaker();						
			}
			else if(auditObservation.getReviewNote()!=null && !auditObservation.getReviewNote().equals("")){
				qryString.append("and reviewNoteMaker = :noteMaker");
				maker = auditObservation.getReviewNoteMaker();
			}
			else if(auditObservation.getResponseNote()!=null && !auditObservation.getResponseNote().equals("")){
				qryString.append("and responseNoteMaker = :noteMaker");
				maker = auditObservation.getResponseNoteMaker();	
			}
			else {
				return false;
			} 	*/		
			
			
		}	
		
		@Override
		public AuditObservationReportReviewCapture getAuditObservationReportReviewCaptureLatest(AuditObservation auditObservation){
			
			logger.info("Inside getAuditObservationReportReviewCaptureLatest ..");
			Session session =getSession();
			 
			List<String> rolesListInspector = Arrays.asList(("G2,G8,G12").split(","));  //Inspection
			List<String> rolesListCompliance = Arrays.asList(("G3,G31").split(","));	//Compliance or Response
			List<String> rolesListReviewer = Arrays.asList(("G4,G7").split(","));       //reviewer(scrutiny)
			String role = auditObservation.getRole();
			
			String noteMaker="";
			if(rolesListInspector.contains(role)){				 
				noteMaker = auditObservation.getObservationNoteMaker();						
			}
			else if(rolesListCompliance.contains(role)){				 
				noteMaker = auditObservation.getResponseNoteMaker();	
			}
			else if(rolesListReviewer.contains(role)){				 
				noteMaker = auditObservation.getReviewNoteMaker();
			}
			
			/*if(auditObservation.getObservationNote()!=null && !auditObservation.getObservationNote().equals("")){				 
				noteMaker = auditObservation.getObservationNoteMaker();						
			}
			else if(auditObservation.getReviewNote()!=null && !auditObservation.getReviewNote().equals("")){				 
				noteMaker = auditObservation.getReviewNoteMaker();
			}
			else if(auditObservation.getResponseNote()!=null && !auditObservation.getResponseNote().equals("")){				 
				noteMaker = auditObservation.getResponseNoteMaker();	
			}	*/		
			
			StringBuilder qryString = new StringBuilder( "from AuditObservationReportReviewCapture  where "
					+ " legalEntityCode =:legalEntityCode "
					+ " and observationId =:observationId " 					
					//+ " and maker=:noteMaker "
					+ " order by makerTimestamp DESC");								
			 
			Query query = session.createQuery(qryString.toString())		
			.setParameter("legalEntityCode", auditObservation.getLegalEntityCode())
			.setParameter("observationId", auditObservation.getObservationId());
			//.setParameter("noteMaker", noteMaker);
			
			List<AuditObservationReportReviewCapture> list = query.list();
			if(list.isEmpty() || list==null || list.size()==0 ){
				return null;
			}
			else{
				AuditObservationReportReviewCapture auditObservationReportReviewCapture = list.get(0);
				String isCurrentCycle =  auditObservationReportReviewCapture.getIsCurrentCycle();
				if(auditObservationReportReviewCapture !=null 
					 && isCurrentCycle != null	
					 && auditObservationReportReviewCapture.getMaker().equals(noteMaker) 
					 && isCurrentCycle.equalsIgnoreCase(BankAuditConstant.YES)){
					return auditObservationReportReviewCapture;
				}
				return null;
			}			 
		}		
		
		@Override
        public List<AuditObservationReportReviewCapture> getObservationReportReviewCaptureNotes(Integer legalEntityCode, String observationId, String role){
			
			
			logger.info("Inside getObservationReportReviewCaptureNotes ..");
			Session session =getSession();	
			session.setDefaultReadOnly(true);
			List<String> rolesListInspector = Arrays.asList(("G2,G8,G12").split(","));  //Inspection
			List<String> rolesListCompliance = Arrays.asList(("G3,G31").split(","));	//Compliance
			List<String> rolesListReviewer = Arrays.asList(("G4,G7").split(",")); 		//reviewer(scrutiny)
					 
			StringBuffer qry =new StringBuffer(" Select "
					+ " notes as observationNotes, "
					+ " (Select Concat(ugRoleCode,'-',ugRoleName) From MaintUsergroupRoles  rols "
					+ " where rols.ugRoleCode = notes.captureType and rols.legalEntityCode=notes.legalEntityCode ) as userRole, "					
					+ " (Select concat(usr.userId,'-',ifnull(usr.firstName, ''),' ',    ifnull(usr.lastName, '') )"
					+ "  FROM User usr Where  notes.maker = usr.userId and usr.legalEntityCode = notes.legalEntityCode) as maker" 
					+ " From AuditObservationReportReviewCapture notes "					 				 
					+ " Where notes.legalEntityCode =:legalEntityCode "
					+ " and notes.observationId =:observationId"
					+ " and notes.captureType in(:role) "
					+ "");			
			
			qry.append(" ORDER BY notes.makerTimestamp ASC ");	 
			
			Query query = session.createQuery(qry.toString())
			.setParameter("legalEntityCode", legalEntityCode)
			.setParameter("observationId", observationId);
			if(rolesListInspector.contains(role)){
				query.setParameterList("role", rolesListInspector);
			}else if(rolesListCompliance.contains(role)){
				query.setParameterList("role", rolesListCompliance);
			}else if(rolesListReviewer.contains(role)){
				query.setParameterList("role", rolesListReviewer);
			}
			else{
				return null;
			}			
			query.setResultTransformer(new ResultTransformer() {				
				@Override
				public Object transformTuple(Object[] tuple, String[] aliases) {
					AuditObservationReportReviewCapture auditObservationReportReviewCapture= null;
					
					if(tuple[0]!=null){
						auditObservationReportReviewCapture = (AuditObservationReportReviewCapture)(tuple[0]);
						if(tuple[1]!=null) auditObservationReportReviewCapture.setCaptureType(tuple[1].toString());
						if(tuple[2]!=null) auditObservationReportReviewCapture.setMaker(tuple[2].toString()); 
						if(auditObservationReportReviewCapture.getEntityStatus()==null){
							auditObservationReportReviewCapture.setEntityStatus("A");
						}
					} 
					
					return auditObservationReportReviewCapture;
				}
				@Override
				public List transformList(List collection) {
					return collection;
				}
			});
			
			List<AuditObservationReportReviewCapture> listAuditObservationReportReviewCapture = query.list();
			
			return listAuditObservationReportReviewCapture;			 
			
		}
		
		public void markedNotesAsCycleCompleted(Integer legalEntityCode, String auditTypeCode, String planId, String auditId){
			logger.info("Inside markedNotesAsCycleCompleted ..");
			Session session =getSession();	
			
			String updateQuery = "update AuditObservationReportReviewCapture  "
					+ " set isCurrentCycle = '"+ BankAuditConstant.NO+ "' "	
					+ " Where legalEntityCode =:legalEntityCode "	 
					+ " and  auditTypeCode=:auditTypeCode "
					+ " and  planId=:planId "
					+ " and  auditId=:auditId "    ;	
			try{
				
				session.createQuery(updateQuery)
					.setParameter("legalEntityCode", legalEntityCode)
					.setParameter("auditTypeCode", auditTypeCode)
					.setParameter("planId", planId)
					.setParameter("auditId", auditId)
					.executeUpdate();
			
			}catch (Exception e) {
				logger.error("Error .." + e.getMessage() +" :: "+e.getCause()); 
			}			 
		}
		
		@Override
		public String submitObservationNotes(Integer legalEntityCode, String auditTypeCode,String auditId, String planId,  String role ){
			logger.info("Inside submitObservationNotes ..");		 
			
			List<String> rolesListInspector = Arrays.asList(("G2,G8").split(","));  //Inspection
			List<String> reviewTeam = Arrays.asList(("G12").split(","));  //reviewTeam
			List<String> rolesListCompliance = Arrays.asList(("G3,G31").split(","));	//Compliance
			List<String> rolesListReviewer = Arrays.asList(("G4,G7").split(",")); 		//reviewer(scrutiny)
			
			List<String> roleList = new ArrayList<String>();
			if(rolesListInspector.contains(role)){
				roleList.addAll(rolesListInspector);
			}else if(reviewTeam.contains(role)){
				roleList.addAll(reviewTeam);
			}else if(rolesListCompliance.contains(role)){
				roleList.addAll(rolesListCompliance);
			}else if(rolesListReviewer.contains(role)){
				roleList.addAll(rolesListReviewer);
			}
			
			String updateQuery = "update AuditObservationReportReviewCapture  "
					+ " set entityStatus = '"+ BankAuditConstant.STATUS_AUTH+ "' "	
					+ " Where legalEntityCode = :legalEntityCode "	 
					+ " and  auditTypeCode = :auditTypeCode "
					+ " and  planId = :planId "
					+ " and  auditId = :auditId "
					+ " and  captureType in (:role) "
					+ " and entityStatus = '"+ BankAuditConstant.STATUS_PENDING_AUTH+ "'"    ;	
			try{
				
				getSession().createQuery(updateQuery)
					.setParameter("legalEntityCode", legalEntityCode)
					.setParameter("auditTypeCode", auditTypeCode)
					.setParameter("planId", planId)
					.setParameter("auditId", auditId)					
					.setParameterList("role", roleList)					
					.executeUpdate();
			
			}catch (Exception e) {
				logger.error("Error .." + e.getMessage() +" :: "+e.getCause()); 
				return BankAuditConstant.FAILED;
			}	
			return BankAuditConstant.SUCCESS;
		}
		
		/***********************For Notes End*********************/	
		 
		/******************** BaaSDOS Audit Observation records Status update after a particular Process ************************/
		public String updateAuditObservationRecordStatus(Integer legalEntityCode, String auditTypeCode,
				String planId, String auditId, String status){
			
			logger.info("Inside updateAuditObservationRecordStatus .. :: "+auditTypeCode+" :: "+ planId +" :: "+auditId +" :: "+status );
			try{
				StringBuilder updateQuery=new StringBuilder( "Update AuditObservation obs set "); //obs.status='"+BankAuditConstant.STATUS_UNAUTH+"' where"
				
				if("AS".equalsIgnoreCase(status)){
					updateQuery.append(" obs.submissionStatus='"+BankAuditConstant.STATUS_AUTH+"', obs.flag=null ");  				
				}else if("RI".equalsIgnoreCase(status)){
					updateQuery.append(" obs.responseStatus='"+BankAuditConstant.STATUS_UNAUTH+"'");  				
				}else if("RS".equalsIgnoreCase(status)){
					updateQuery.append(" obs.responseStatus='"+BankAuditConstant.STATUS_AUTH+"', obs.flag=null ");  					
				}else if("WI".equalsIgnoreCase(status)){
					updateQuery.append(" obs.reviewStatus='"+BankAuditConstant.STATUS_UNAUTH+"' ");  					
				}else if("WS".equalsIgnoreCase(status)){
					updateQuery.append(" obs.reviewStatus='"+BankAuditConstant.STATUS_AUTH+"', "
							+ " obs.reviewStatusAttributeLastAuth = obs.reviewStatusAttribute, obs.flag=null ");  										
				}
				
				updateQuery.append(" where obs.legalEntityCode =:legalEntityCode"
				+ " and obs.auditTypeCode =:auditTypeCode "
				+ " and obs.planId =:planId "
				+ " and obs.auditId =:auditId ");
				
				Session session =getSession();			
				Query query = session.createQuery(updateQuery.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("planId", planId)
				.setParameter("auditId", auditId);
				query.executeUpdate();
				
				updateComplianceScrutinyRemarkStatus(legalEntityCode, auditTypeCode, planId, auditId, status);
				
				return BankAuditConstant.SUCCESS;
			}catch (Exception e) {
				logger.error("Error .." + e.getMessage() +" :: "+e.getCause());
				return BankAuditConstant.FAILED;
			}
		}
		
		public AuditObservation updateObservationSpecificField(AuditObservation auditObservation, String fieldToUpdate){
			Session session = getSession();
			logger.info("Inside updateObservationSpecificField .. :: "+auditObservation.getAuditId()+" :: "+ auditObservation.getLegalEntityCode() +" :: "+auditObservation.getPlanId()+" :: "+ auditObservation.getAuditTypeCode() +" :: "+ auditObservation.getObservationId() +" :: "+fieldToUpdate );
			AuditObservation audObs=null;
			try{
				String flag= auditObservation.getFlag();
				audObs= (AuditObservation)session.get(AuditObservation.class,auditObservation);
				
				if(("flag").equalsIgnoreCase(fieldToUpdate)){
					audObs.setFlag(flag);  				
				}else {					
				}				
				session.update(audObs);
			}catch (Exception e) {
				logger.error("Error .." + e.getMessage() +" :: "+e.getCause());
			} return audObs;
		}
		
		
		/***********************Remarks related Service -Compliance Scrutiny Start *********************/
		 
		@Override
		public	Boolean checkIfLatestRemarksIsGivenBySameUserGroup(AuditObservation auditObservation, List<String> roles){
			 
			logger.info("Inside checkIfLatestRemarksIsGivenBySameUserGroup ..");
			Session session =getSession();
			session.flush();
			String role="";
			
			StringBuilder qryString = new StringBuilder( "Select captureType from AuditObservationRespondReviewCapture  where "
					+ " legalEntityCode =:legalEntityCode "					
					+ " and observationId =:observationId "
					+ " and  auditTypeCode=:auditTypeCode "
					+ " and  planId=:planId"
					+ " and  auditId=:auditId" 
					+ " order by makerTimestamp DESC "  );		 
			
			Query query = session.createQuery(qryString.toString())		
			.setParameter("legalEntityCode", auditObservation.getLegalEntityCode())
			.setParameter("observationId", auditObservation.getObservationId()) 
			.setParameter("auditTypeCode", auditObservation.getAuditTypeCode())
			.setParameter("planId", auditObservation.getPlanId())
			.setParameter("auditId", auditObservation.getAuditId())
			//.setFirstResult(0)
			.setMaxResults(1);
			
			List<String> list = query.list();

			//if(!list.isEmpty() && list.size()>0 && list.get(0).toString().equalsIgnoreCase(auditObservation.getRole())){
			if(!list.isEmpty() && list.size()>0 && roles.contains(list.get(0).toString())){
				return true;
			}else{
				return false;
			}			
		}	
		
		@Override
		public AuditObservationRespondReviewCapture getAuditObservationRespondReviewCaptureLatest(AuditObservation auditObservation, List<String> roles){
			
			logger.info("Inside getAuditObservationRespondReviewCaptureLatest ..");
			Session session =getSession();
					 
			StringBuilder qryString = new StringBuilder( " from AuditObservationRespondReviewCapture  where "
					+ " legalEntityCode =:legalEntityCode "
					+ " and  observationId =:observationId " 	
					+ " and  auditTypeCode=:auditTypeCode "
					+ " and  planId=:planId"
					+ " and  auditId=:auditId" 
					+ " order by makerTimestamp DESC");								
			 
			Query query = session.createQuery(qryString.toString())		
			.setParameter("legalEntityCode", auditObservation.getLegalEntityCode())
			.setParameter("observationId", auditObservation.getObservationId())
			.setParameter("auditTypeCode", auditObservation.getAuditTypeCode())
			.setParameter("planId", auditObservation.getPlanId())
			.setParameter("auditId", auditObservation.getAuditId())
			//.setFirstResult(0)
			.setMaxResults(1);
			
			List<AuditObservationRespondReviewCapture> list = query.list();
			if(list.isEmpty() || list==null || list.size()==0 ){
				return null;
			}
			else{
				AuditObservationRespondReviewCapture auditObservationRespondReviewCapture = list.get(0);
				//if(auditObservationRespondReviewCapture.getCaptureType().equals(auditObservation.getRole())){
				if(roles.contains(auditObservationRespondReviewCapture.getCaptureType())){
					return auditObservationRespondReviewCapture;
				}
				return null;
			}			
		}		
		
		@Override
		public List<AuditObservationRespondReviewCapture> getAuditObservationRespondReviewCapture(Integer legalEntityCode, 
				String auditTypeCode,String auditId, String planId, String observationId, String role){
			
			try{
			logger.info("Inside getAuditObservationRespondReviewCapture ..");
			Session session =getSession();	
			
			//List<String> rolesListInspector = Arrays.asList(("G2,G8,G12").split(","));
			//List<String> rolesListCompliance = Arrays.asList(("G3,G31").split(","));//Bank User
			//List<String> rolesListReviewer = Arrays.asList(("G4,G7").split(",")); // Scrutiny level
			
			List<String> rolesListComplianceScritony = Arrays.asList(("G4,G7,G3,G31").split(",")); 
					
			/*StringBuffer qry =new StringBuffer(" Select "
					+ "  respondReviewCapture as auditObservationRespondReviewCapture, " 
					+ " (Select Concat(ugRoleCode,'-',ugRoleName) From MaintUsergroupRoles  rols "
							+ " where rols.ugRoleCode = respondReviewCapture.captureType and rols.legalEntityCode=:legalEntityCode) as userRole, "				
					+ " concat(respondReviewCapture.maker,'-',ifnull(usr.firstName, ''),' ',    ifnull(usr.lastName, '') ) as userName" 
					+ " From AuditObservationRespondReviewCapture respondReviewCapture "
						+ " Inner join User usr   on  respondReviewCapture.maker = usr.userId "					 
							+ " Where respondReviewCapture.legalEntityCode =:legalEntityCode "
							+ " and respondReviewCapture.observationId =:observationId"
							+ " and respondReviewCapture.auditTypeCode =:auditTypeCode "
							+ " and respondReviewCapture.planId =:planId "
							+ " and respondReviewCapture.auditId =:auditId "
							+ " and respondReviewCapture.captureType in(:role) "
							+ "");*/
			StringBuffer qry =new StringBuffer(" Select "
					+ " respondReviewCapture.id ,  "
					+ " respondReviewCapture.legalEntityCode,  "
					+ " respondReviewCapture.auditTypeCode, "
					+ " respondReviewCapture.planId, "
					+ " respondReviewCapture.auditId, "
					+ " respondReviewCapture.groupObservationId,  "
					+ " respondReviewCapture.observationId, " 
					+ " (Select Concat(ugRoleCode,'-',ugRoleName) From MaintUsergroupRoles  rols "
					+ " where rols.ugRoleCode = respondReviewCapture.captureType and rols.legalEntityCode=:legalEntityCode) as userRole, "
					+ " concat(respondReviewCapture.maker,'-',ifnull(usr.firstName, ''),' ',    ifnull(usr.lastName, '') ),"
					+ " respondReviewCapture.comment, "
					+ " respondReviewCapture.makerTimestamp"
					+ " From AuditObservationRespondReviewCapture respondReviewCapture "
					+ " Inner join User usr   on  respondReviewCapture.maker = usr.userId "
					+ " and  usr.legalEntityCode = respondReviewCapture.legalEntityCode "					 
					+ " Where respondReviewCapture.legalEntityCode =:legalEntityCode "
					+ " and respondReviewCapture.observationId =:observationId"
					+ " and respondReviewCapture.auditTypeCode =:auditTypeCode "
					+ " and respondReviewCapture.planId =:planId "
					+ " and respondReviewCapture.auditId =:auditId "
					+ " and respondReviewCapture.captureType in(:role) "
					+ "");
				
				 		
			qry.append(" ORDER BY respondReviewCapture.makerTimestamp ASC ");		
					 		
			Query query = session.createQuery(qry.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("observationId", observationId)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("planId", planId)
				.setParameter("auditId", auditId);		
			if(rolesListComplianceScritony.contains(role)){
				query.setParameterList("role", rolesListComplianceScritony);
			}else{
				return null;
			}		 	 
			flushSession();
			query.setResultTransformer(new ResultTransformer() {				
				@Override
				public Object transformTuple(Object[] tuple, String[] aliases) {
					AuditObservationRespondReviewCapture auditObservationRespondReviewCapture= null;
					
					if(tuple[0]!=null){
						
						/*auditObservationRespondReviewCapture =   (AuditObservationRespondReviewCapture) tuple[0];
						 
						if(tuple[1]!=null)	auditObservationRespondReviewCapture.setCaptureType(tuple[1].toString());
						if(tuple[2]!=null)	auditObservationRespondReviewCapture.setMaker(tuple[2].toString()); */
						
						
						auditObservationRespondReviewCapture =  new AuditObservationRespondReviewCapture();
						
						if(tuple[0]!=null)	auditObservationRespondReviewCapture.setId(Integer.parseInt(tuple[0].toString()));
						if(tuple[1]!=null)	auditObservationRespondReviewCapture.setLegalEntityCode(Integer.parseInt(tuple[1].toString()));
						if(tuple[2]!=null)	auditObservationRespondReviewCapture.setAuditTypeCode(tuple[2].toString());
						if(tuple[3]!=null)	auditObservationRespondReviewCapture.setPlanId(tuple[3].toString());
						if(tuple[4]!=null)	auditObservationRespondReviewCapture.setAuditId(tuple[4].toString());
						if(tuple[5]!=null)	auditObservationRespondReviewCapture.setGroupObservationId(tuple[5].toString());
						if(tuple[6]!=null)	auditObservationRespondReviewCapture.setObservationId(tuple[6].toString());
						if(tuple[7]!=null)	auditObservationRespondReviewCapture.setCaptureType(tuple[7].toString());
						if(tuple[8]!=null)	auditObservationRespondReviewCapture.setMaker(tuple[8].toString());
						if(tuple[9]!=null)	auditObservationRespondReviewCapture.setComment(tuple[9].toString());
						if(tuple[10]!=null)	auditObservationRespondReviewCapture.setMakerTimestamp((Date)tuple[10]);
						 
						
					}
					return auditObservationRespondReviewCapture;
				}
				@Override
				public List transformList(List collection) {
					return collection;
				}
			});
			
			List<AuditObservationRespondReviewCapture> listAuditObservationRespondReviewCapture = query.list();
			
			return listAuditObservationRespondReviewCapture;		 
			
		}
			catch(Exception ex){
				System.out.println(ex);
			}return null;
		}
		
		@Override
		public int getCountRespondRejectedRecords(Integer legalEntityCode, String auditId) {

			logger.info("Inside getCountRespondRejectedRecords .. "+legalEntityCode +" auditId.."+auditId );
			Session session=getSession();
			int rejRecs=0;
			try {
				rejRecs= Integer.parseInt(session.createSQLQuery("SELECT COUNT(*) FROM audit_observation "
						+ " WHERE legal_entity_code =:legalEntityCode "
						+ " AND audit_id =:auditId AND response_status='R' ")
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditId", auditId).uniqueResult().toString());
				logger.info("further compliance records  .. "+rejRecs);
			}catch(Exception e) {
				e.printStackTrace();
			}
			return rejRecs;
		}
		
		@Override
		public void updateComplianceScrutinyRemarkStatus(Integer legalEntityCode, String auditTypeCode,
				String planId, String auditId, String status){			 
			Session session=getSession();
			if("RS".equalsIgnoreCase(status) || "WS".equalsIgnoreCase(status) || BankAuditConstant.STATUS_AUTH.equalsIgnoreCase(status)){
				
				String updateQuery = " Update AuditObservationRespondReviewCapture "
						+ " set status = '"+BankAuditConstant.STATUS_AUTH+"' "
						+ " where legalEntityCode =  :legalEntityCode"
						+ " and auditTypeCode = :auditTypeCode "
						+ " and planId = :planId "
						+ " and auditId = :auditId"
						+ " and captureType in (:roles)  " ;
				 			
				Query query = session.createQuery(updateQuery)
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("planId", planId)
				.setParameter("auditId", auditId);
				if("WS".equalsIgnoreCase(status)){
					query.setParameterList("roles", Arrays.asList(("G4,G7").split(",")));
				}else if("RS".equalsIgnoreCase(status) ){
					query.setParameterList("roles", Arrays.asList(("G3,G31").split(",")));
				}else if(BankAuditConstant.STATUS_AUTH.equalsIgnoreCase(status)){
					query.setParameterList("roles", Arrays.asList(("G3,G31,G4,G7").split(",")));
				}			
				query.executeUpdate();
				
			} 		
		}
		
		
		// Method to get the Compliance and Non Compliance count based on the Process code, for Rating calculation
		@Override
		public Map<String, AuditObservationComplianceDto> getCompNonCompCountProcesswise(Integer legalEntityCode, String auditTypeCode, String auditId ){	
			
			Session session=getSession();
			session.setDefaultReadOnly(true);
			logger.info("Inside getCompNonCompCountProcesswise .. "+ auditId);	
			Map<String, AuditObservationComplianceDto> compCntMap =null;
			try{

				StringBuilder strQuery = new StringBuilder(" SELECT process_code,"
						+ " COALESCE(SUM(CASE WHEN compliance_category ='C' THEN cnt END), 0) AS C, "
						+ " COALESCE(SUM(CASE WHEN compliance_category ='N' THEN cnt END), 0) AS N "
						+ " FROM ( "
						+ " 	SELECT c.process_code, c.compliance_category, COUNT(*), audit_id FROM  "
						+ "  		(SELECT m.process_code, o.compliance_category, o.audit_id  "
						+ "     		FROM audit_observation o, activity_process_finding_mapping m   "
						+ "  	    	WHERE o.audit_id=:auditId AND o.legal_entity_code=:legalEntityCode AND o.compliance_category IN ('N','C') "
						+ "			    	AND o.mapping_id=m.mapping_id AND o.legal_entity_code=m.legal_entity_code) c  "  
						+ " 	GROUP BY process_code, compliance_category, audit_id ) AS p "
						+ " GROUP BY process_code ");
				
				Query qry = session.createNativeQuery(strQuery.toString())	 
						.setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId);
				
				compCntMap = new TreeMap<String, AuditObservationComplianceDto>();
				List<Object[]> recordSet=qry.list();
				if(!recordSet.isEmpty() && recordSet.size()>0){
					for (Object[] record : recordSet) {
						AuditObservationComplianceDto aObsDto =new AuditObservationComplianceDto();
						aObsDto.setProcessCode(String.valueOf(record[0]));
						aObsDto.setCompliance(String.valueOf(record[1]));
						aObsDto.setNonCompliance(String.valueOf(record[2]));
						
						compCntMap.put(String.valueOf(record[0]), aObsDto );	
					} 				
				}							
			}
			catch (Exception exception) {
				logger.error("Inside method getAuthorizedLettersCount :: ", exception); 
				exception.printStackTrace(System.out);
			}
			return null;
		}
		
		// Method to get the Compliance and Non Compliance count based on the Process code, for Rating calculation
		@Override
		public String getNonComplianceBehavior(Integer legalEntityCode, String auditId, String auditTypeCode, String unitId, String observationId, String mappingId){	
			
			Session session=getSession();
			session.setDefaultReadOnly(true);
			logger.info("Inside getNonComplianceBehavior .. "+ auditId+" :: "+auditTypeCode+" :: "+ unitId+" :: "+observationId +" :: "+mappingId);	
			String compBhvr ="N";
			try{
				// 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 o.mapping_id, sch_prev.audit_id, o.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 o ON sch_prev.audit_id=o.audit_id AND o.mapping_id=:mappingId "
						+ " 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).setParameter("mappingId", mappingId);
				
				try {
					//Object[] obj = (Object[])query.getSingleResult();
					List<Object[]> results = query.getResultList();
					if (!results.isEmpty()) {
					    Object[] obj = results.get(0);
					    if(obj !=null && obj.length > 2 && obj[2]!=null && "N".equalsIgnoreCase(obj[2].toString())) {
							compBhvr="Y";
					}
				}
				}catch(Exception e) {
					logger.info("Exception while getting comp status from Previous Audit ..");
					e.printStackTrace(System.out);
				}
				
				//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("N".equalsIgnoreCase(compBhvr)) {
					StringBuilder strQueryForAllPrevAudit = new StringBuilder(" SELECT obs.mapping_id, obs.audit_id, obs.compliance_category"
							+ " 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 compliance_category IN ('N','C') AND sch.legal_entity_code=:legalEntityCode AND sch.audit_type_code=:auditTypeCode "
							+ "     AND sch.unit_id=:unitId AND obs.mapping_id=:mappingId  AND sch.audit_id <> :auditId ");  // <> :auditId, not to include the current Audit
					
					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).setParameter("mappingId", mappingId);
					
					List<Object[]> obsCompLst = query2.list();
					int total=0, nonCompCnt=0;
					
					for (Object[] objects : obsCompLst) {
						if(objects[2]!=null && "N".equalsIgnoreCase(objects[2].toString())) nonCompCnt++;
						total++;
					}
					if(total!=0 && nonCompCnt !=0 && nonCompCnt*100/total >=50) {
						compBhvr="Y";
					} 
				}
				
				logger.info("Compliance behavior is .. "+compBhvr);
			}
			catch (Exception exception) {
				logger.error("Inside method getNonComplianceBehavior :: ", exception); 
				exception.printStackTrace(System.out);
				//compBhvr=null;
			}
			return compBhvr;
		}
		
		// Method to Update the Compliance category for "Others", of Cheklists/Observations during Initiation
		@Override
		public String updatingOthersToNA(Integer legalEntityCode, String auditTypeCode,String auditId){
			logger.info("Inside updatingOthersToNA Dao .."+ auditId+ " :: " +auditTypeCode);		 
			
			String updateQuery = "UPDATE audit_observation obs "
					+ " SET compliance_category = '"+ BankAuditConstant.NOT_APPLICABLE+ "', submission_status = '"+ BankAuditConstant.STATUS_PENDING_AUTH+"' " 	
					+ " WHERE legal_entity_code =:legalEntityCode AND  audit_id=:auditId AND audit_type_code =:auditTypeCode "	 
					+ " AND mapping_id in (SELECT m.mapping_id FROM activity_process_finding_mapping m "
					+ "						 JOIN maint_audit_group g ON m.legal_entity_code=g.legal_entity_code AND m.audit_group_code=g.audit_group_code "
					+ "						 JOIN maint_audit_subgroup sg ON m.legal_entity_code=sg.legal_entity_code AND m.audit_group_code=sg.audit_group_code AND m.audit_sub_group_code=sg.audit_sub_group_code "
					+ "						 JOIN maint_audit_activity a ON a.legal_entity_code=m.legal_entity_code AND a.activity_code=m.activity_code "
					+ "						 JOIN maint_audit_process p ON p.legal_entity_code=m.legal_entity_code AND p.process_code=m.process_code "
					+ "						 JOIN maint_audit_finding f ON f.legal_entity_code=m.legal_entity_code AND f.finding_code=m.finding_code "
					+ "					   WHERE m.audit_type_code =:auditTypeCode AND "
					//+ "						 (sg.audit_sub_group_name='Others' OR a.activity_name='Others' OR f.finding_name='Others' OR f.finding_name LIKE 'Any other (Specify)' ) ) ";	
					+ "						 (f.finding_name='Others' OR f.finding_name LIKE 'Any other (Specify)' ) ) ";
			try{				
				int updateCnt=getSession().createNativeQuery(updateQuery)
					.setParameter("legalEntityCode", legalEntityCode).setParameter("auditTypeCode", auditTypeCode).setParameter("auditId", auditId)					
					.executeUpdate();
				logger.info("Others records updated cnt .. "+ updateCnt);
			}catch (Exception e) {
				logger.error("Error .." + e.getMessage() +" :: "+e.getCause()); 
				return BankAuditConstant.FAILED;
			}	
			return BankAuditConstant.SUCCESS;
		}
		
		@Override
		public List<NonCompliancePersistenceChecklistDto> getNonCompliancePersistenceChecklist(Integer legalEntityCode, String auditTypeCode, String auditId){
			logger.info("Inside getNonCompliancePersistenceChecklist .."+ auditId+ " :: " +auditTypeCode);		 
			
			Session session=getSession();
			session.setDefaultReadOnly(true);
			 
			StringBuffer strQuery = new StringBuffer(" SELECT obs.observation_id, obs.group_observation_id, obs.mapping_id,  " //2
					+ " obs.finding_remarks, obs.non_compliance_persistence_remark AS persistenceRemark, r2.statement AS standardStmt, " //5
					+ " r1.audit_group_code, r1.audit_group_name , " 
					+ " r1.audit_sub_group_code, r1.audit_sub_group_name , "
					+ " r1.activity_code, r1.activity_name , "
					+ " r1.process_code, r1.process_name , "
					+ " r1.finding_code, r1.finding_name     "
					+ " FROM   audit_observation obs "
					+ " INNER JOIN audit_report_stmt r1 ON obs.legal_entity_code=r1.legal_entity_code "
					+ " AND obs.audit_id = r1.audit_id AND obs.audit_type_code = r1.audit_type_code "
					+ " AND obs.mapping_id = r1.mapping_id AND obs.compliance_category = r1.compliance_category "
					+ " INNER JOIN audit_report_stmt r2 ON obs.legal_entity_code=r2.legal_entity_code "
					+ " AND obs.audit_id = r2.audit_id AND obs.audit_type_code = r2.audit_type_code "
					+ " AND obs.mapping_id = r2.mapping_id AND obs.compliance_category = r2.compliance_category  " 
					+ " WHERE r1.applicable_for='"+BankAuditConstant.FOR_CHECKLIST+"' AND r1.statement_type='"+BankAuditConstant.FOR_CHECKLIST+"'  "// For checklist 
					+ " AND  r2.applicable_for='"+BankAuditConstant.FOR_CHECKLIST+"' AND r2.statement_type='"+BankAuditConstant.FOR_STANDARD_STMT+"'  " // For standard_stmt					
					+ " AND obs.compliance_category='"+BankAuditConstant.COMPLIANCE_NO+"' AND obs.non_compliance_persistence='"+BankAuditConstant.YES+"' "
					+ " AND obs.legal_entity_code = :legalEntityCode "
					+ " AND obs.audit_id = :auditId "
					+ " AND obs.audit_type_code = :auditTypeCode "
					+ " AND obs.observation_criticality = :criticality ");
			
			Query qry = session.createNativeQuery(strQuery.toString())
					.setParameter("legalEntityCode", legalEntityCode)
					.setParameter("auditId", auditId)
					.setParameter("auditTypeCode", auditTypeCode)
					.setParameter("criticality", BankAuditConstant.OBSERVATION_CRITICALITY);
			
			qry.setResultTransformer(
					new ResultTransformer() {
						int indexNo=1;
						@Override
						public Object transformTuple(Object[] tuple, String[] aliases) {
							NonCompliancePersistenceChecklistDto nonCompliancePersistenceChecklistDto = new NonCompliancePersistenceChecklistDto();
							
							nonCompliancePersistenceChecklistDto.setLegalEntityCode(legalEntityCode);
							nonCompliancePersistenceChecklistDto.setAuditId(auditId);
							nonCompliancePersistenceChecklistDto.setAuditTypeCode(auditTypeCode);
							nonCompliancePersistenceChecklistDto.setSerialNumber(indexNo+++"");
							if(tuple[0]!=null) nonCompliancePersistenceChecklistDto.setObservationId(tuple[0].toString());
							if(tuple[1]!=null) nonCompliancePersistenceChecklistDto.setGroupObservationId(tuple[1].toString());
							if(tuple[2]!=null) nonCompliancePersistenceChecklistDto.setMappingId(tuple[2].toString());
							if(tuple[3]!=null) nonCompliancePersistenceChecklistDto.setFindingRemarks(tuple[3].toString());
							if(tuple[4]!=null) nonCompliancePersistenceChecklistDto.setNonCompliancePersistenceRemark(tuple[4].toString());
							if(tuple[5]!=null) nonCompliancePersistenceChecklistDto.setStandardStatement(tuple[5].toString());
							
							if(tuple[6]!=null) nonCompliancePersistenceChecklistDto.setChapterCode(tuple[6].toString());
							if(tuple[7]!=null) nonCompliancePersistenceChecklistDto.setChapter(tuple[7].toString());
							
							if(tuple[8]!=null) nonCompliancePersistenceChecklistDto.setSectionCode(tuple[8].toString());
							if(tuple[9]!=null) nonCompliancePersistenceChecklistDto.setSection(tuple[9].toString());
							
							if(tuple[10]!=null) nonCompliancePersistenceChecklistDto.setProductCode(tuple[10].toString());
							if(tuple[11]!=null) nonCompliancePersistenceChecklistDto.setProduct(tuple[11].toString());
							
							if(tuple[12]!=null) nonCompliancePersistenceChecklistDto.setProcessCode(tuple[12].toString());
							if(tuple[13]!=null) nonCompliancePersistenceChecklistDto.setProcess(tuple[13].toString());
							
							if(tuple[14]!=null) nonCompliancePersistenceChecklistDto.setChecklistCode(tuple[14].toString());
							if(tuple[15]!=null) nonCompliancePersistenceChecklistDto.setChecklist(tuple[15].toString());
															
							return nonCompliancePersistenceChecklistDto;
						}
						
						@Override
						public List transformList(List collection) { 
							return collection;
						}
					});
			return qry.list();
		} 
		
		public void saveNonCompliancePersistenceRemarks(List<NonCompliancePersistenceChecklistDto> nonCompliancePersistenceChecklistDtoList){
			 
			Session session=getSession();
			for (NonCompliancePersistenceChecklistDto nonCompliancePersistence : nonCompliancePersistenceChecklistDtoList) {				 
				String updateQuery=" Update audit_observation obs set non_compliance_persistence_remark = :persistenceRemark "
						+ " WHere  obs.compliance_category= '"+BankAuditConstant.COMPLIANCE_NO+"' "
						+ " AND obs.non_compliance_persistence= '"+BankAuditConstant.YES+"' "
						+ " AND obs.legal_entity_code =  '"+nonCompliancePersistence.getLegalEntityCode()+"' "
						+ " AND obs.audit_id = '"+nonCompliancePersistence.getAuditId()+"' "
						+ " AND obs.audit_type_code = '"+nonCompliancePersistence.getAuditTypeCode()+"'  "
						+ " AND obs.mapping_id = '"+nonCompliancePersistence.getMappingId()+"' "
						+ " AND obs.observation_id = '"+nonCompliancePersistence.getObservationId()+"' ";
				
				session.createNativeQuery(updateQuery)
				.setParameter("persistenceRemark", nonCompliancePersistence.getNonCompliancePersistenceRemark()).executeUpdate();			
			}
			 
		}		
		
		@Override
		public void updateComplinaceRating(AuditObservation auditObservation){
							
			StringBuilder sqlQuery= new StringBuilder(" update audit_rating_compliance_obs_score score "
					+ "		set  "
					+ " 	score.desk_assessed_score = '"+auditObservation.getDeskEffectiveScore()+"', "
					+ " 	score.desk_effective_score  =  '"+auditObservation.getDeskEffectiveScore()+"', "
					+ " 	score.reviewer_maker_remarks  =  :deskComplianceRemarks , "
					+ " 	score.reviewer_checker_remarks  = :deskComplianceRemarks ,"				
					+ " 	score.maker  = :maker  "				 
					+ "		where score.observation_id = :observationId " 
					+ "		and score.audit_type_code = :auditTypeCode "
					+ "		and score.audit_id = :auditId "
					+ "		and score.legal_entity_code = :legalEntityCode   "  
					+ " ");
			
			getSession().createSQLQuery(sqlQuery.toString())
					.setParameter("observationId", auditObservation.getObservationId())  
			 		.setParameter("auditTypeCode", auditObservation.getAuditTypeCode())
			 		.setParameter("auditId", auditObservation.getAuditId())
			 		.setParameter("legalEntityCode", auditObservation.getLegalEntityCode())	
			 		.setParameter("deskComplianceRemarks", auditObservation.getDeskReviewerMakerRemarks())
			 		.setParameter("maker", auditObservation.getReviewOwner())			 		
			 		.executeUpdate();		 
			
		}
		
		@Override
		public  List<Object[]> getObservationsCountByStatusAttributesOnSubmit(Integer legalEntityCode, String auditTypeCode, String auditId, 
				Boolean onInspectionSubmit, Boolean onComplianceSubmit, Boolean onScrutinySubmit){
			logger.info("Inside getObservationsCountByDifferentParameters .. "+ auditId+" :: "+auditTypeCode+" :: "+ auditId);	
			Session session=getSession();
			session.setDefaultReadOnly(true); 
			
			StringBuilder strQry = new StringBuilder("SELECT COUNT(obs.observationId) AS totalCount ");			
			if(onInspectionSubmit){
				strQry.append(", SUM(CASE WHEN IFNULL(obs.complianceCategory, '') = '"+BankAuditConstant.COMPLIANCE_NO+"'  THEN 1 ELSE 0 END) AS nonCompCount, "
						+ " SUM(CASE WHEN IFNULL(obs.complianceCategory, '') = '"+BankAuditConstant.COMPLIANCE_NO+"' "
								+ "AND IFNULL(obs.observationCriticality, '') = '"+BankAuditConstant.OBSERVATION_CRITICALITY_MAJOR+"'  THEN 1 ELSE 0 END) AS nonCompMjorCount, "
						+ " SUM(CASE WHEN IFNULL(obs.complianceCategory, '') = '"+BankAuditConstant.COMPLIANCE_NO+"' "
								+ " AND IFNULL(obs.observationCriticality, '') = '"+BankAuditConstant.OBSERVATION_CRITICALITY_MINOR+"'  THEN 1 ELSE 0 END) AS nonCompMinorCount, "
						+ " SUM(CASE WHEN IFNULL(obs.complianceCategory, '') = '"+BankAuditConstant.COMPLIANCE_NO+"' "
								+ " AND IFNULL(obs.nonCompliancePersistence, '')  = '"+BankAuditConstant.YES+"'   THEN 1 ELSE 0 END) AS nonCompPersistenceCount, "								
						+ " SUM(CASE WHEN IFNULL(obs.complianceCategory, '') = '"+BankAuditConstant.COMPLIANCE_YES+"'  THEN 1 ELSE 0 END) AS compCount, "
						+ " SUM(CASE WHEN IFNULL(obs.complianceCategory, '') = '"+BankAuditConstant.COMPLIANCE_NA+"' "
								+ " AND obs.mappingId IN(SELECT mapping.mappingId FROM ActivityProcessFindingMapping mapping  "
										+ " INNER JOIN MaintAuditFinding f ON f.legalEntityCode = mapping.legalEntityCode AND f.findingCode = mapping.findingCode  "
										+ " WHERE mapping.legalEntityCode = obs.legalEntityCode AND mapping.auditTypeCode = obs.auditTypeCode"
										+ " AND (f.findingName!='Others' AND f.findingName != 'Any other (Specify)' )  )  THEN 1 ELSE 0 END) AS naCountExcludeOthrs, "
					 	+ " SUM(CASE WHEN IFNULL(obs.complianceCategory, '') = '"+BankAuditConstant.COMPLIANCE_NA+"'  THEN 1 ELSE 0 END) AS naCountIncludeOthrs "
						+ " ");
			}
			if(onComplianceSubmit){
				strQry.append(", SUM(CASE WHEN IFNULL(obs.complianceCategory, '') = '"+BankAuditConstant.COMPLIANCE_NO+"'  THEN 1 ELSE 0 END) AS nonCompCount ");
			}
			if(onScrutinySubmit){
				strQry.append( ", SUM(CASE WHEN IFNULL(obs.reviewStatusAttribute, '') = '"+BankAuditConstant.REVIEW_STATUS_ATTRIB_FURTHER_COMPLIANCE+"'  THEN 1 ELSE 0 END) AS furtherComplianceCount, "
						+ " SUM(CASE WHEN IFNULL(obs.reviewStatusAttribute, '') = '"+BankAuditConstant.REVIEW_STATUS_ATTRIB_CARRY_FORWARD+"'  THEN 1 ELSE 0 END) AS carryForwardCount, "
						+ " SUM(CASE WHEN IFNULL(obs.reviewStatusAttribute, '') = '"+BankAuditConstant.REVIEW_STATUS_ATTRIB_ACCEPTED+"'  THEN 1 ELSE 0 END) AS acceptedCount ");
			}
			strQry.append(", '0' as testCount "
					+ " FROM AuditObservation obs "
				 	+ " WHERE obs.legalEntityCode = :legalEntityCode "
				 	+ " AND obs.auditTypeCode = :auditTypeCode "
				 	+ " AND obs.auditId = :auditId  ");
		
			List<Object[]> statusCount = session.createQuery(strQry.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("auditId", auditId)
				.list(); 
			
			return statusCount;				
		}
}
