/*
 * 
 */
package com.bankaudit.dao;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

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

import com.bankaudit.constants.BankAuditConstant;
import com.bankaudit.dto.ChecklistDto;
import com.bankaudit.dto.DataTableResponse;
import com.bankaudit.process.model.AuditObservation;
import com.bankaudit.process.model.AuditSchedule;
import com.bankaudit.process.model.ChecklistOfflineInspection;
import com.bankaudit.util.BankAuditUtil;

/**
 * The Interface {@link ChecklistDaoImpl} define the methods
 * that can be exposed to controller layer in order to implements rest
 * end-points and define the business logic.
 *
 * @author amit.patel
 * @version 1.0
 */

@Repository("checklistDao")
public class ChecklistDaoImpl extends AbstractDao implements ChecklistDao{
	
	static final Logger logger = Logger.getLogger(ChecklistDaoImpl.class);
	//based on audit id only mandatory and auditTypeCode is optional
	@Override
	public List<String> getChapter(String legalEntityCodeStr, String auditId, String auditTypeCode) {
		Session session=getSession();
		session.setDefaultReadOnly(true);
		StringBuffer strQuery =new StringBuffer("Select distinct concat(grp.audit_group_name,'-',grp.audit_group_code)  "
					+" from  activity_process_finding_mapping a  "
					+"   Inner JOIN risk_summary_dtls r on a.audit_type_code = r.audit_type_code and a.audit_group_code=r.audit_group_code  "
					+" 	 Inner JOIN maint_audit_group grp on grp.audit_group_code = a.audit_group_code "
					+" Where r.checked='Y' and r.audit_id =:auditId "
					+" and a.legal_entity_code =:legalEntityCodeStr "
					+" and a.audit_type_code=:auditTypeCode  ");

					strQuery.append(" order by concat(grp.audit_group_name,'-',  grp.audit_group_code)    ASC  ");
		
		Query query=(Query) session.createSQLQuery(strQuery.toString())
				.setParameter("legalEntityCodeStr", legalEntityCodeStr)
				.setParameter("auditId", auditId)
				.setParameter("auditTypeCode", auditTypeCode);
				 
		
		return query.list();
	}

	@Override
	public List<String> getSection(String legalEntityCodeStr, String auditId, String auditTypeCode, String auditGroupCode) {
		 
		Session session=getSession();
		session.setDefaultReadOnly(true);
		StringBuffer strQuery = new StringBuffer( "Select distinct concat(grp.audit_sub_group_name,'-',grp.audit_sub_group_code)    "
					+"	from activity_process_finding_mapping a "
					+"	    Inner JOIN risk_summary_dtls r On a.audit_type_code = r.audit_type_code and a.audit_group_code=r.audit_group_code  "
					+"					and  a.audit_sub_group_code=r.audit_sub_group_code "
					+"		Inner JOIN maint_audit_subgroup grp on grp.audit_sub_group_code = a.audit_sub_group_code "
					+"	Where r.checked='Y' and r.audit_id =:auditId "
					+"  and a.legal_entity_code =:legalEntityCodeStr "	
					+ "  and a.audit_type_code=:auditTypeCode  "
					+"  and a.audit_group_code=:auditGroupCode " );	
		 
					strQuery.append("	order by concat(grp.audit_sub_group_name,'-',grp.audit_sub_group_code) ASC ");
		
		Query query=(Query) session.createSQLQuery(strQuery.toString())
				.setParameter("legalEntityCodeStr", legalEntityCodeStr)
				.setParameter("auditId", auditId)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("auditGroupCode", auditGroupCode);
		 
				
		return query.list();
	}

	@Override
	public List<String> getProduct(String legalEntityCodeStr, String auditId, String auditTypeCode, String auditGroupCode, String auditSubGroupCode) {

		Session session=getSession();
		session.setDefaultReadOnly(true);
		StringBuffer strQuery = new StringBuffer("Select distinct concat(act.activity_name,'-',act.activity_code)    "
					+"	from activity_process_finding_mapping a   "
					+"	    Inner JOIN risk_summary_dtls r on a.audit_type_code = r.audit_type_code and a.audit_group_code=r.audit_group_code  "
					+"					and  a.audit_sub_group_code=r.audit_sub_group_code and r.activity_code = a.activity_code "
				//	+"	Inner JOIN maint_audit_group grp on grp.audit_group_code = a.audit_group_code "
				//	+"	Inner JOIN maint_audit_subgroup subgrp on subgrp.audit_sub_group_code = a.audit_sub_group_code  "
					+"	Inner join maint_audit_activity act on act.activity_code = a.activity_code "
					+"  Where r.checked='Y' and r.audit_id =:auditId "
					+ " and a.legal_entity_code =:legalEntityCodeStr "
					+"  and a.audit_type_code=:auditTypeCode   "
					+"  and a.audit_group_code=:auditGroupCode "
					+"  and a.audit_sub_group_code=:auditSubGroupCode "
					+"  and act.entity_status='A' "); 
					 
					strQuery.append("  order by concat(act.activity_name,'-',act.activity_code) ASC "); 
		
		Query query=(Query) session.createSQLQuery(strQuery.toString())
				.setParameter("legalEntityCodeStr", legalEntityCodeStr)
				.setParameter("auditId", auditId)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("auditGroupCode", auditGroupCode)
				.setParameter("auditSubGroupCode", auditSubGroupCode); 
		
		return query.list();
	}

	@Override
	public List<String> getProcess(String legalEntityCodeStr, String auditId, String auditTypeCode, String auditGroupCode, String auditSubGroupCode, String activityCode ) {
		Session session=getSession();
		session.setDefaultReadOnly(true);
		StringBuffer strQuery = new StringBuffer(" Select distinct concat( proc.process_name, '-', proc.process_code)  from  "
				+" activity_process_finding_mapping a   "
			//+" Inner JOIN maint_audit_group grp on grp.audit_group_code = a.audit_group_code "
			//+" Inner JOIN maint_audit_subgroup subgrp on subgrp.audit_sub_group_code = a.audit_sub_group_code  "
			//+" Inner join maint_audit_activity act on act.activity_code = a.activity_code "
			+" Inner join maint_audit_process  proc on proc.process_code = a.process_code "
			+"  Where  a.legal_entity_code =:legalEntityCodeStr "
			+ " and a.audit_type_code=:auditTypeCode  "			
			+"  and a.audit_group_code=:auditGroupCode "
			+"  and a.audit_sub_group_code=:auditSubGroupCode "
			+"  and a.activity_code=:activityCode "
			+"	and proc.entity_status='A' ");
		
			strQuery.append("  order by concat( proc.process_name, '-', proc.process_code) ASC"); 
		
		Query query=(Query) session.createSQLQuery(strQuery.toString())
				.setParameter("legalEntityCodeStr", legalEntityCodeStr)	
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("auditGroupCode", auditGroupCode)
				.setParameter("auditSubGroupCode", auditSubGroupCode)
				.setParameter("activityCode", activityCode);	
		 
		
		return query.list();
	}

	@Override
	public DataTableResponse getChecklist(String legalEntityCodeStr, String auditId, String auditTypeCode, String auditGroupCode, String auditSubGroupCode,
			 String activityCode, String processCode, String search, Integer orderColumn, String orderDirection, Integer page, Integer size,
			 String complianceCategory, String submissionStatus, String responseStatus, String reviewStatus, String flag, String criticalityToggle,
				String reportRvwFedbk, String role, String documentType, String documentSubType, String nonCompliancePersistence) {
		 			
		Session session=getSession();
		session.setDefaultReadOnly(true);
		logger.info("getChecklist .. "+ auditId+" :: "+ role);
		
		List<String> rolesListInspector = Arrays.asList(("G2,G8").split(","));  //Inspection
		List<String> rolesListCompliance = Arrays.asList(("G3,G31").split(","));	//Compliance or Response
		List<String> rolesListReviewer = Arrays.asList(("G4,G7").split(","));       //review/Scrutiny
		List<String> rolesList =  new ArrayList<String>();
		if(rolesListInspector.contains(role)){
			rolesList = rolesListInspector;
		}else if(rolesListCompliance.contains(role)){
			rolesList = rolesListCompliance;
		}else if(rolesListReviewer.contains(role)){
			rolesList = rolesListReviewer;
		}else if("G12".equalsIgnoreCase(role)){
			rolesList.add("G12");
		}
		/*String strQuery = " Select distinct concat(finding.finding_name, '-', finding.finding_code),  from  "
				+"  activity_process_finding_mapping a   "
			+"  Inner JOIN maint_audit_group grp on grp.audit_group_code = a.audit_group_code ";*/
			
			/*if(!BankAuditUtil.isEmptyString(auditSubGroupCode)){
				strQuery= strQuery+"  Inner JOIN maint_audit_subgroup subgrp on subgrp.audit_sub_group_code = a.audit_sub_group_code  ";
			}
			if(!BankAuditUtil.isEmptyString(activityCode)){
				strQuery= strQuery+"  Inner join maint_audit_activity act on act.activity_code = a.activity_code ";
			}
			if(!BankAuditUtil.isEmptyString(processCode)){
				strQuery= strQuery+"  Inner join maint_audit_process  proc on proc.process_code = a.process_code 	 ";
			}*/
		AuditSchedule auditSchedule = (AuditSchedule) (session.createQuery("from AuditSchedule sch where sch.auditId=:auditId and sch.legalEntityCode=:legalEntityCode "))
				.setParameter("auditId", auditId).setParameter("legalEntityCode", Integer.parseInt(legalEntityCodeStr)).uniqueResult();
		logger.info("auditSchedule statuses .. .. "+ auditSchedule.getAuditStatus() + " RespondStatus:: "+auditSchedule.getRespondStatus()+" :: "+auditSchedule.getReviewStatus());
	
		StringBuffer strQuery = new StringBuffer(" Select distinct f.finding_code, f.finding_name,"
				+ " obs.observation_id, obs.group_observation_id, obs.mapping_id, "
				+ " obs.compliance_category, obs.observation_criticality, obs.finding_remarks, obs.remarks,"
				+ " stmt.standard_stmnt_compliance, stmt.standard_stmnt_non_compliance, stmt.reference, "
				+ " obs.submission_status, obs.review_note_maker_timestamp, obs.flag,"
				+ " stmt.standard_stmnt_compliance_print, stmt.standard_stmnt_non_compliance_print, "
				+ " (Select count(*) from audit_document_details doc where doc.legal_entity_code = obs.legal_entity_code"
					+ " and doc.observation_id = obs.observation_id and doc.audit_type_code = a.audit_type_code "
					+ " and doc.status != :docStatus");
				if(!rolesList.isEmpty() && rolesList.size()>0){	
					strQuery.append(" and (CASE WHEN doc.role in (:rolesList) "
							+ "   THEN doc.entity_status in ('"+BankAuditConstant.STATUS_AUTH+"', '"+BankAuditConstant.STATUS_PENDING_AUTH+"') "
							+ "   ELSE doc.entity_status = '"+BankAuditConstant.STATUS_AUTH+"' END) ");	
				}else{
					strQuery.append(" and doc.entity_status = '"+BankAuditConstant.STATUS_AUTH+"'  ");	
				}
		
				if(!BankAuditUtil.isEmptyString(documentType)){
						strQuery.append( " and doc.document_type=:documentType ");
				}
				if(!BankAuditUtil.isEmptyString(documentSubType)){
						strQuery.append( " and doc.document_sub_type=:documentSubType  ");	
				}
				strQuery.append( " ) as docCount,  ");
					
				strQuery.append(" f.finding_name_hindi,  "
					+ " stmt.standard_stmnt_compliance_hindi, stmt.standard_stmnt_non_compliance_hindi,"
					+ " stmt.non_comp_edit, stmt.comp_edit,  obs.standard_statement   ");	
				if(rolesListReviewer.contains(role)){
					strQuery.append(", score.desk_assessed_score, desk_effective_score, reviewer_maker_remarks " ); //24,25,26
				}
				
			//+ " 'Y' as standard_stmnt_compliance_print, 'Y' as standard_stmnt_non_compliance_print "		
			strQuery.append(  "	from activity_process_finding_mapping a "   
					+" 	Inner join audit_observation obs on obs.mapping_id = a.mapping_id and obs.legal_entity_code=a.legal_entity_code "
					+" 	Inner join maint_audit_finding f on f.finding_code = a.finding_code and f.legal_entity_code=a.legal_entity_code  " );
			
			if(rolesListReviewer.contains(role)){
				strQuery.append(" left join audit_rating_compliance_obs_score score on obs.audit_id=score.audit_id and obs.legal_entity_code=score.legal_entity_code "
					+ "	AND obs.observation_id = score.observation_id	"	);
			}
			strQuery.append( " 	left join maint_report_standard_stmt stmt on stmt.mapping_id = a.mapping_id  "); 
			
			if(!BankAuditUtil.isEmptyString(role)){
				if("G3".equalsIgnoreCase(role) || "G31".equalsIgnoreCase(role)) {  // G3= Bank Maker && G31=Bank Checker
					strQuery.append("   "							
										+ " and (case when  obs.compliance_category='C' then (stmt.standard_stmnt_compliance_print is null  "
										+ " or stmt.standard_stmnt_compliance_print=''  or stmt.standard_stmnt_compliance_print='Y')  "
										+ " when  obs.compliance_category='N' then (stmt.standard_stmnt_non_compliance_print is null "
										+ " or stmt.standard_stmnt_non_compliance_print='' or stmt.standard_stmnt_non_compliance_print='Y') end) " ); 											 
				}
			}
			 	
			strQuery.append("  "			
				+"  Where  obs.audit_id =:auditId "
				+ " and a.legal_entity_code =:legalEntityCodeStr "
				+ " and a.audit_type_code=:auditTypeCode "
				//+"  and a.audit_group_code=:auditGroupCode "
				//+ " and obs.compliance_category is not null " 
				);
				
			if(!BankAuditUtil.isEmptyString(role)){
				if("G3".equalsIgnoreCase(role) || "G31".equalsIgnoreCase(role)) {  // G3= Bank Maker && G31=Bank Checker
					strQuery.append("  and obs.compliance_category != 'NA' "); 											 
				} 
			}
			
			if(!BankAuditUtil.isEmptyString(auditGroupCode)){		
				strQuery.append("  and a.audit_group_code=:auditGroupCode ");
			} 
			if(!BankAuditUtil.isEmptyString(auditSubGroupCode)){		
				strQuery.append("  and a.audit_sub_group_code=:auditSubGroupCode ");
			}
			if(!BankAuditUtil.isEmptyString(activityCode)){
				strQuery.append("  and a.activity_code=:activityCode ");
			}
			if(!BankAuditUtil.isEmptyString(processCode)){
				strQuery.append("  and a.process_code=:processCode ");
			}
			if(!BankAuditUtil.isEmptyString(complianceCategory)){ // to filter the checklist based on Compliance category N-> Non Compliance, C-> Compliance, NA-> Not Applicable 
				strQuery.append("  and obs.compliance_category=:complianceCategory ");
			}
			if(!BankAuditUtil.isEmptyString(nonCompliancePersistence)){ // to filter the checklist which are Persistence for Non Compliance observation -> Y
				strQuery.append("  and obs.non_compliance_persistence = :nonCompliancePersistence ");
			}
			
			if(!BankAuditUtil.isEmptyString(responseStatus) && "U".equalsIgnoreCase(responseStatus)){ // to show the unattended checklist's in Compliance/Response screen, the param value is U
				if("RS".equalsIgnoreCase(auditSchedule.getRespondStatus()) 
						&& "WI".equalsIgnoreCase(auditSchedule.getReviewStatus())) { // Condition added to get unattended records post Compliance submitted and Scrutiny initiated
					//strQuery.append("  and obs.response_status in ('U','R','PA') ");
					strQuery.append("  and obs.response_status in ('PA') ");
				}else if("RS".equalsIgnoreCase(auditSchedule.getRespondStatus()) 
						&& "WS".equalsIgnoreCase(auditSchedule.getReviewStatus())) { // Condition added to get unattended records post Compliance submitted and Scrutiny submitted
					//strQuery.append("  and obs.response_status in ('U','R','PA') ");
					strQuery.append("  and obs.response_status in ('PA','R' ) ");
				}else strQuery.append("  and obs.response_status in ('U') "); //Compliance initiated // Condition to include R status while getting unattended records
			}else if(!BankAuditUtil.isEmptyString(responseStatus)){
				strQuery.append("  and obs.response_status=:responseStatus ");
			}
			
			if(!BankAuditUtil.isEmptyString(reviewStatus)){ // to show the unattended checklist's in Review screen, the param value is U
				if("WS".equalsIgnoreCase(auditSchedule.getReviewStatus())) { // Condition added to get unattended records post Scrutiny submitted
					strQuery.append("  and obs.review_status_attribute in ('FC')"
							+ " and obs.response_status in ('A') ");
				}else strQuery.append("  and obs.review_status=:reviewStatus ");
			}
			
			if(!BankAuditUtil.isEmptyString(flag)){ // to show the flagged records, the param value is F
				strQuery.append("  and obs.flag=:flag ");
			}
			if(!BankAuditUtil.isEmptyString(criticalityToggle)){
				strQuery.append("  and obs.observation_criticality=:criticalityToggle ");
			}
			if(!BankAuditUtil.isEmptyString(submissionStatus) && BankAuditUtil.isEmptyString(reportRvwFedbk)){ // to show the unattended checklist's in Inspection screen, the param value is U
				strQuery.append("  and obs.submission_status=:submissionStatus ");
			}
			if(!BankAuditUtil.isEmptyString(reportRvwFedbk) && BankAuditUtil.isEmptyString(submissionStatus) ){
				//strQuery.append("  and obs.review_note_maker_timestamp is not null ");	
				if("G2".equalsIgnoreCase(role) || "G8".equalsIgnoreCase(role) || "G12".equalsIgnoreCase(role) ){
					if(reportRvwFedbk.equalsIgnoreCase("Y") && "AC".equalsIgnoreCase(auditSchedule.getAuditStatus())){
						strQuery.append("and (obs.review_status_attribute='FC' OR obs.review_status_attribute='R' )  ");
					}
					else{
						strQuery.append(" and obs.observation_id in  (Select notes.observation_id from audit_observation_report_review_capture notes "
							+ " Where notes.audit_id =:auditId and notes.legal_entity_code=:legalEntityCodeStr "
							+ " and capture_type ='G12'  ) " );
					}
				}else if("G3".equalsIgnoreCase(role) || "G31".equalsIgnoreCase(role)
						|| "G4".equalsIgnoreCase(role) || "G7".equalsIgnoreCase(role)){
					strQuery.append(" and obs.observation_id in   (Select review.observation_id from audit_observation_respond_review_capture review "
							+ " Where review.audit_id =:auditId and review.legal_entity_code=:legalEntityCodeStr "
							+ " and (capture_type ='G4' OR capture_type ='G7' ) )" );
				}
			}else{
				if("G12".equalsIgnoreCase(role) && BankAuditUtil.isEmptyString(reportRvwFedbk) 
						&& !BankAuditUtil.isEmptyString(submissionStatus) ) {  // G12=  Review team
					strQuery.append(" and obs.observation_id not in   (Select notes.observation_id from audit_observation_report_review_capture notes "
							+ " Where notes.audit_id =:auditId and notes.legal_entity_code=:legalEntityCodeStr "
							+ " and capture_type ='G12' ) " );
				} 
			}			
			
			/*if(!BankAuditUtil.isEmptyString(role)){
				if("G3".equalsIgnoreCase(role) || "G31".equalsIgnoreCase(role)) {  // G3= Bank Maker && G31=Bank Checker
					strQuery.append("  and obs.compliance_category != 'NA' "							
										+ " and (case when  obs.compliance_category='C' then (stmt.standard_stmnt_compliance_print is null  "
										+ " or stmt.standard_stmnt_compliance_print=''  or stmt.standard_stmnt_compliance_print='Y')  "
										+ " when  obs.compliance_category='N' then (stmt.standard_stmnt_non_compliance_print is null "
										+ " or stmt.standard_stmnt_non_compliance_print='' or stmt.standard_stmnt_non_compliance_print='Y') end) " ); 											 
				}
			}*/
			 
			
			if(!BankAuditUtil.isEmptyString(search)){
				strQuery.append(" and ( "
						+ " f.finding_name like :search "
						+ " OR f.finding_code like :search"
						+ " OR obs.observation_id like :search"
						+ " OR obs.compliance_category like :search "
						+ " OR obs.observation_criticality like :search " 
						+ "  ) ");
			}
			
			String columns[]={
					"obs.observation_id",
					"f.finding_name, f.finding_code",					
					"obs.compliance_category"
			};

			if(orderColumn != null && orderColumn == -1 && !BankAuditUtil.isEmptyString(reportRvwFedbk)){ // to show the report review feedback records in descending order
				strQuery=strQuery.append(" ORDER BY obs.review_note_maker_timestamp  desc  ");
			}else if(orderColumn != null && orderColumn == -1 && !BankAuditUtil.isEmptyString(orderDirection)){ // -1 send for the first time from the UI during landing page.
				strQuery=strQuery.append(" ORDER BY obs.observation_id  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)){
				strQuery=strQuery.append(" ORDER BY  ").append(columns[orderColumn]).append(" ").append(orderDirection);
			}else {
				strQuery=strQuery.append(" ORDER BY obs.observation_id  asc  ");
			}
			logger.info("Query is ..  .."+ strQuery);
			
			//strQuery.append("  order by  f.finding_name"); 
			@SuppressWarnings("deprecation")
			Query query=(Query) session.createSQLQuery(strQuery.toString()).setResultTransformer(new ResultTransformer() {
				@Override
				public Object transformTuple(Object[] tuple, String[] aliases) {
					ChecklistDto checklistDto=new ChecklistDto();
					try {
					if(tuple[0] !=null) checklistDto.setFindingCode(tuple[0].toString());
					if(tuple[1] !=null) checklistDto.setFindingName(tuple[1].toString());
					if(tuple[2] !=null) checklistDto.setObservationId(tuple[2].toString());
					if(tuple[3] !=null) checklistDto.setGroupObservationId(tuple[3].toString());
					if(tuple[4] !=null) checklistDto.setMappingId(tuple[4].toString());
					if(tuple[5] !=null) checklistDto.setComplianceCategory(tuple[5].toString());
					if(tuple[6] !=null) checklistDto.setObservationCriticality(tuple[6].toString());
					if(tuple[7] !=null) checklistDto.setFindingRemarks(tuple[7].toString());
					if(tuple[8] !=null) checklistDto.setRemarks(tuple[8].toString());
					if(tuple[9] !=null) checklistDto.setStandardStmntCompliance(tuple[9].toString());
					if(tuple[10] !=null) checklistDto.setStandardStmntNonCompliance(tuple[10].toString());
					if(tuple[11] !=null) checklistDto.setReference(tuple[11].toString());
					if(tuple[12] !=null) checklistDto.setSubmissionStatus(tuple[12].toString());
					if(tuple[13] !=null) checklistDto.setReviewNoteMakerTimestamp(tuple[13].toString());
					if(tuple[14] !=null) checklistDto.setFlag(tuple[14].toString());
					if(tuple[15] !=null) checklistDto.setStandardStmntCompliancePrint(tuple[15].toString());
					if(tuple[16] !=null) checklistDto.setStandardStmntNonCompliancePrint(tuple[16].toString());
					
					if(tuple[17] !=null) checklistDto.setObservationDocumentCount(String.valueOf(tuple[17]));
					
					if(tuple[18] !=null) checklistDto.setFindingNameHindi(String.valueOf(tuple[18]));
					if(tuple[19] !=null) checklistDto.setStandardStmntComplianceHindi(String.valueOf(tuple[19]));
					if(tuple[20] !=null) checklistDto.setStandardStmntNonComplianceHindi(String.valueOf(tuple[20]));
					 
					if(tuple[21] !=null) checklistDto.setNonCompEdit(String.valueOf(tuple[21]));
					if(tuple[22] !=null) checklistDto.setCompEdit(String.valueOf(tuple[22]));
					if(tuple[23] !=null) checklistDto.setStandardStatement(String.valueOf(tuple[23]));
					
					if(rolesListReviewer.contains(role)){
						if(tuple[24] !=null) checklistDto.setDeskAssessedScore(tuple[24].toString());  
						if(tuple[25] !=null) checklistDto.setDeskEffectiveScore(tuple[25].toString());   
						if(tuple[26] !=null) checklistDto.setDeskReviewerMakerRemarks(tuple[26].toString());   	
					}
					
					}catch(Exception e) {e.printStackTrace(System.out);}					
					return checklistDto;
				}				
				@Override
				public List transformList(List collection) {
						return collection;
					}
				});
					
				query.setParameter("legalEntityCodeStr", legalEntityCodeStr)
				.setParameter("auditId", auditId)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("docStatus", BankAuditConstant.STATUS_DEL);
				
				if(!rolesList.isEmpty() && rolesList.size()>0){	
					query.setParameterList("rolesList", rolesList);
				}
				
				if(!BankAuditUtil.isEmptyString(auditGroupCode)){
					query.setParameter("auditGroupCode", auditGroupCode);
				}				 
				if(!BankAuditUtil.isEmptyString(auditSubGroupCode)){
					query.setParameter("auditSubGroupCode", auditSubGroupCode);
				}
				if(!BankAuditUtil.isEmptyString(activityCode)){
					query.setParameter("activityCode", activityCode);
				}
				if(!BankAuditUtil.isEmptyString(processCode)){
					query.setParameter("processCode", processCode);		
				}
				if(!BankAuditUtil.isEmptyString(complianceCategory)){
					query.setParameter("complianceCategory", complianceCategory);		
				}
				if(!BankAuditUtil.isEmptyString(nonCompliancePersistence)){ 
					query.setParameter("nonCompliancePersistence", nonCompliancePersistence); 
				}
				
				if(!BankAuditUtil.isEmptyString(submissionStatus) && BankAuditUtil.isEmptyString(reportRvwFedbk)) {
					query.setParameter("submissionStatus", submissionStatus );
				}
				if(!BankAuditUtil.isEmptyString(responseStatus) && "U".equalsIgnoreCase(responseStatus)){ // Condition to include R status while getting unattended records
					//strQuery.append("  and obs.response_status in ('U','R') ");
				}else if(!BankAuditUtil.isEmptyString(responseStatus)){
					query.setParameter("responseStatus",responseStatus );
				}
				if(!BankAuditUtil.isEmptyString(reviewStatus)){ 
					if("WS".equalsIgnoreCase(auditSchedule.getReviewStatus())); // Condition added to get unattended records post Scrutiny submitted
					else query.setParameter("reviewStatus", reviewStatus );
				}
				if(!BankAuditUtil.isEmptyString(flag)){ 
					query.setParameter("flag", flag);
				}
				if(!BankAuditUtil.isEmptyString(criticalityToggle)){
					query.setParameter("criticalityToggle", criticalityToggle);
				}
				
				if(!BankAuditUtil.isEmptyString(search)){
					query.setParameter("search","%"+ search+"%");
				}
				
				if(!BankAuditUtil.isEmptyString(documentType)){
					query.setParameter("documentType", documentType);
				}
				
				if(!BankAuditUtil.isEmptyString(documentSubType)){
					query.setParameter("documentSubType", documentSubType);
				}
				/*if(!BankAuditUtil.isEmptyString(reportRvwFedbk)){
					query.setParameter("captureType", "G12");
				}*/
		
				//query.list();
				logger.info("Bfr qry exe ## .... ");
				List<ChecklistDto> tmpChecklistDto =(List<ChecklistDto>)query.list(); 
				Long totalRecords=null;
				try {
					totalRecords = (long)tmpChecklistDto.size();
				}catch(Exception e) {
					totalRecords = (long)0;
				}
				
				List<ChecklistDto> listhecklistDto = query.setFirstResult((page*size)).setMaxResults(size).list();
				
				DataTableResponse dataTableResponse=new DataTableResponse();
				dataTableResponse.setData(listhecklistDto);
				dataTableResponse.setRecordsTotal(totalRecords);
				dataTableResponse.setRecordsFiltered(totalRecords);
				
		
		return dataTableResponse;
	}
 
	/*@Override
	public  AuditObservation getAuditObservation(String legalEntityCode, String auditTypeCode, String groupObservationId){
		//AuditObservation auditObservation = null;
	 
		Session session =getSession();		
		session.setDefaultReadOnly(true);
		Criteria criteria = session.createCriteria(AuditObservation.class);
		criteria.add(Restrictions.eq("groupObservationId", groupObservationId));
		criteria.add(Restrictions.eq("legalEntityCode", legalEntityCode));
		criteria.add(Restrictions.eq("auditTypeCode", auditTypeCode));
		List<AuditObservation> listAuditObservation = criteria.list();
		if(listAuditObservation.size()>0 && !listAuditObservation.isEmpty()){
			return listAuditObservation.get(0);
		}
		return null;
		
	}*/
	 
	
	/*@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 "
				+ " legalEntityCode =:legalEntityCode"
				+ " and auditTypeCode =:auditTypeCode "
				+ " and planId =:planId "
				+ " and auditId =:auditId "
				+ " and observationId =:observationId "
				+ " and documentType in (:documentType) "
				+ "")
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("planId", planId)
				.setParameter("auditId", auditId)
				.setParameter("observationId", observationId)
				.setParameterList("documentType", documentType.split(","))
				.list();
	}*/
	 
	@Override
	public ChecklistDto getChapterDetailsOfChecklist(String legalEntityCodeStr, String auditTypeCode ,  String auditId, 
			String observationId , String groupObservationId ){
		 
		   Session session =getSession();		
		   session.setDefaultReadOnly(true);
		   String qry=" Select " 		
		   	
		   		+ "  ( Select distinct concat(grp.audit_group_code,'-',grp.audit_group_name) "
		   		+ "  from  maint_audit_group grp  where  grp.audit_group_code = a.audit_group_code "
		   		+ "   and  grp.audit_type_code=a.audit_type_code "
		   		+ "   and grp.legal_entity_code=a.legal_entity_code ) as chapter, "
		   		
				+ "  ( Select distinct concat(subgrp.audit_sub_group_code,'-',subgrp.audit_sub_group_name)  "
				+ "  from maint_audit_subgroup subgrp  where subgrp.audit_sub_group_code = a.audit_sub_group_code"
				+ "  and subgrp.audit_type_code = a.audit_type_code "
				+ "  and subgrp.audit_group_code = a.audit_group_code "
				+ "  and subgrp.legal_entity_code = a.legal_entity_code ) as section, "
				
				+ "  ( Select distinct concat(act.activity_code,'-',act.activity_name)  "
				+ "  from maint_audit_activity act where act.activity_code = a.activity_code "
				+ "  and act.legal_entity_code=a.legal_entity_code and act.entity_status='A' ) as product , "
				
				+ " ( Select  distinct concat(proc.process_code , '-', proc.process_name) "
				+ "  from maint_audit_process proc  where  proc.process_id = a.process_id "
				+ "  and proc.legal_entity_code=a.legal_entity_code and proc.entity_status='A') as process ,  "
				
				+ "  ( Select distinct concat(grp.audit_group_code,'-',grp.audit_group_name_hindi) "
				+ "  from  maint_audit_group grp  where  grp.audit_group_code = a.audit_group_code "
				+ "   and  grp.audit_type_code=a.audit_type_code "
				+ "   and grp.legal_entity_code=a.legal_entity_code ) as chapter_hindi, "
				
				+ "  ( Select distinct concat(subgrp.audit_sub_group_code,'-',subgrp.audit_sub_group_name_hindi)  "
				+ "  from maint_audit_subgroup subgrp  where subgrp.audit_sub_group_code = a.audit_sub_group_code"
				+ "  and subgrp.audit_type_code = a.audit_type_code "
				+ "  and subgrp.audit_group_code = a.audit_group_code "
				+ "  and subgrp.legal_entity_code = a.legal_entity_code ) as section_hindi, "
				
				+ "  ( Select distinct concat(act.activity_code,'-',act.activity_name_hindi)  "
				+ "  from maint_audit_activity act where act.activity_code = a.activity_code "
				+ "  and act.legal_entity_code=a.legal_entity_code and act.entity_status='A' ) as product_hindi , "
				
				+ " ( Select  distinct concat(proc.process_code , '-', proc.process_name_hindi) "
				+ "  from maint_audit_process proc  where  proc.process_id = a.process_id "
				+ "  and proc.legal_entity_code=a.legal_entity_code and proc.entity_status='A') as process_hindi  "
		   		
		   		+ "  from  activity_process_finding_mapping a   Inner Join "
		   		+ "  audit_observation obs on obs.mapping_id = a.mapping_id and obs.legal_entity_code=a.legal_entity_code "
		   		+"   Where   a.legal_entity_code =:legalEntityCodeStr "
				+"   and a.audit_type_code=:auditTypeCode "
				+"   and obs.observation_id =:observationId "
				+"   and obs.group_observation_id =:groupObservationId "
				+"   and obs.audit_id =:auditId ";	
		   		
		   ChecklistDto checklistDtoData = (ChecklistDto) session.createSQLQuery(qry)
				   .setParameter("legalEntityCodeStr", legalEntityCodeStr)
				   .setParameter("auditTypeCode", auditTypeCode)
				   .setParameter("observationId", observationId)
				   .setParameter("groupObservationId", groupObservationId)
				   .setParameter("auditId", auditId)
				   .setResultTransformer(new ResultTransformer() {
						@Override
						public Object transformTuple(Object[] tuple, String[] aliases) {
							
							ChecklistDto checklistDto=new ChecklistDto();
							
							if(tuple[0] !=null) checklistDto.setChapter(tuple[0].toString());
							if(tuple[1] !=null) checklistDto.setSection(tuple[1].toString());
							if(tuple[2] !=null) checklistDto.setProduct(tuple[2].toString());
							if(tuple[3] !=null) checklistDto.setProcess(tuple[3].toString());
							
							if(tuple[4] !=null) checklistDto.setChapterHindi(tuple[4].toString());
							if(tuple[5] !=null) checklistDto.setSectionHindi(tuple[5].toString());
							if(tuple[6] !=null) checklistDto.setProductHindi(tuple[6].toString());
							if(tuple[7] !=null) checklistDto.setProcessHindi(tuple[7].toString());
							
							return checklistDto;
							
						}				
						@Override
						public List transformList(List collection) {
								return collection;
							}
						}).
				   uniqueResult() ; 
	
 
		   return checklistDtoData;
	 }
	
	@Override
	public DataTableResponse getAllChecklistForDownload(String legalEntityCode, String auditId, String auditTypeCode, String auditGroupCode, String auditSubGroupCode,
			 String activityCode, String processCode, String search,String complianceCategory, String submissionStatus, String responseStatus, String reviewStatus, 
			 String flag, String criticalityToggle,	String reportRvwFedbk, String role, String documentType, String documentSubType, String nonCompliancePersistence, 
			 AuditSchedule auditSchedule ) {
		 			
		Session session=getSession();
		session.setDefaultReadOnly(true);
		logger.info("getAllChecklistForDownload .. "+ legalEntityCode + " :: "+auditId+" :: "+auditTypeCode +" :: "+role);
		
		List<String> rolesListInspector = Arrays.asList(("G2,G8").split(","));  //Inspection
		List<String> rolesListCompliance = Arrays.asList(("G3,G31").split(","));	//Compliance or Response
		List<String> rolesListReviewer = Arrays.asList(("G4,G7").split(","));       //review/Scrutiny
		List<String> rolesList =  new ArrayList<String>();
		if(rolesListInspector.contains(role)){
			rolesList = rolesListInspector;
		}else if(rolesListCompliance.contains(role)){
			rolesList = rolesListCompliance;
		}else if(rolesListReviewer.contains(role)){
			rolesList = rolesListReviewer;
		}else if("G12".equalsIgnoreCase(role)){
			rolesList.add("G12");
		}else{
			rolesList.add(role);
		}
		StringBuffer strQuery = new StringBuffer(" Select "
				+ "		obs.audit_id, obs.audit_type_code, sch.unit_id, e.unit_name, " // 3 unit_name
				+ "     obs.observation_id, obs.group_observation_id, obs.mapping_id, m.mapping_code,  " // 7 mapping_code
				+ " 	obs.compliance_category, obs.observation_criticality, obs.finding_remarks, obs.remarks, obs.submission_status, "  // 12 submission_status
				+ " 	m.audit_group_code as chapter_code,  " //13
				+ " 	(select audit_group_name from maint_audit_group g where g.audit_group_code=m.audit_group_code and m.audit_type_code=g.audit_type_code "
				+ "		and   g.legal_entity_code=m.legal_entity_code) as grp_name, " 
				+ " 	audit_sub_group_code as section_code, " //15
				+ " 	(select g.audit_sub_group_name from maint_audit_subgroup g where g.audit_group_code=m.audit_group_code and g.audit_sub_group_code= m.audit_sub_group_code "
				+ " 	and m.audit_type_code=g.audit_type_code and  g.legal_entity_code=m.legal_entity_code) as sgrp_name, " //16
				+ " 	m.activity_code as product_code, " //17
				+ " 	(select activity_name from maint_audit_activity g where g.activity_id=m.activity_id and g.legal_entity_code=m.legal_entity_code) as act_name, " 
				+ " 	process_code, " //19
				+ " 	(select process_name from maint_audit_process g where g.process_id=m.process_id and g.legal_entity_code=m.legal_entity_code) as prc_name, " 
				+ " 	f.finding_code as checklist_code, " //21
				//+ " 	(select finding_name from maint_audit_finding g where g.finding_id=m.finding_id and g.legal_entity_code=m.legal_entity_code) as fnd_name, "//22
				+ " 	f.finding_name  as fnd_name, "//22
				
				+ " 	'' as grp_name_hindi, " //23
				+ " 	'' as sgrp_name_hindi, " //24
				+ " 	'' as act_name_hindi, "//25
				+ " 	'' as prc_name_hindi, " //26				
				+ " 	'' as fnd_name_hindi, "  //27 
				
				+ "		obs.response_status, obs.review_status, "//28,29
				+ "		(Select  concat(gp.value,'-',gp.description) from general_parameter gp where gp.legal_entity_code=m.legal_entity_code  and gp.value=obs.review_status_attribute  "
				+ "		and gp.key_1='STATUS_REVIEW' and gp.key_2='REVIEW_ATTRIBUTE') as reviewStatusAttribute , " //30 
				+ "		latestResRevComm.comment as latestResRevRemark, "//31
				+ "		'' as allResRevRemark," //32
			 	+ "		obs.response_remarks, obs.review_remarks  ");//33,34	
			 	
			if(rolesListReviewer.contains(role)){
				 		strQuery.append( ", score.desk_assessed_score, desk_effective_score, reviewer_maker_remarks "); //35,36,37
			}
		 	strQuery.append( "  	from activity_process_finding_mapping m     " 
			+ " 	Inner join audit_observation obs on obs.mapping_id = m.mapping_id and obs.legal_entity_code=m.legal_entity_code  " 
			+ "		Inner join audit_schedule sch on sch.audit_id=obs.audit_id and sch.legal_entity_code=obs.legal_entity_code "
			+ "		Inner join maint_entity e on e.unit_code=sch.unit_id and e.legal_entity_code=sch.legal_entity_code "
			+ "     Inner join maint_audit_finding f on f.finding_code = m.finding_code and f.legal_entity_code = m.legal_entity_code  ");
		 	if(rolesListReviewer.contains(role)){
		 		strQuery.append( " 	Left join audit_rating_compliance_obs_score score on sch.audit_id=score.audit_id and sch.legal_entity_code=score.legal_entity_code"
		 					+ " 		AND obs.observation_id = score.observation_id	");	
		 	}
		 	strQuery.append( "		Left join (SELECT rccOuter.legal_entity_code, rccOuter.audit_type_code, rccOuter.audit_id, rccOuter.observation_id, rccOuter.comment FROM  "
			+ "						 audit_observation_respond_review_capture as rccOuter , "
			+ "					(Select legal_entity_code, audit_type_code, audit_id, observation_id, max(id) as id   from audit_observation_respond_review_capture  "
			+ "	            		Where  capture_type not in (:rolesList)  and status = :statusAuth "
			+ "				group by legal_entity_code, audit_type_code, audit_id, observation_id) as rrcInner "
			+ "	            where rccOuter.legal_entity_code=rrcInner.legal_entity_code AND rccOuter.audit_id = rrcInner.audit_id AND  "
			+ "	            rccOuter.observation_id = rrcInner.observation_id AND rccOuter.id = rrcInner.id  "
			+ "	        	) as latestResRevComm  ON obs.legal_entity_code=latestResRevComm.legal_entity_code AND obs.audit_id=latestResRevComm.audit_id  "
			+ "	         		AND obs.observation_id=latestResRevComm.observation_id  "
			+ " 	where obs.audit_id=:auditId and obs.legal_entity_code=:legalEntityCode  ");
		//if(!rolesListInspector.contains(role)){
		if(rolesListCompliance.contains(role) || rolesListReviewer.contains(role)){
			strQuery.append(" and obs.compliance_category = :complianceCategory ");
		}
		 		
		if(!BankAuditUtil.isEmptyString(auditGroupCode)){		
			strQuery.append("  and m.audit_group_code=:auditGroupCode ");
		} 
		if(!BankAuditUtil.isEmptyString(auditSubGroupCode)){		
			strQuery.append("  and m.audit_sub_group_code=:auditSubGroupCode ");
		}
		if(!BankAuditUtil.isEmptyString(activityCode)){
			strQuery.append("  and m.activity_code=:activityCode ");
		}
		if(!BankAuditUtil.isEmptyString(processCode)){
			strQuery.append("  and m.process_code=:processCode ");
		}
		if(!BankAuditUtil.isEmptyString(complianceCategory)){ // to filter the checklist based on Compliance category N-> Non Compliance, C-> Compliance, NA-> Not Applicable 
			strQuery.append("  and obs.compliance_category=:complianceCategory ");
		}
		if(!BankAuditUtil.isEmptyString(nonCompliancePersistence)){ // to filter the checklist which are Persistence for Non Compliance observation -> Y
			strQuery.append("  and obs.non_compliance_persistence = :nonCompliancePersistence ");
		}
		
		if(!BankAuditUtil.isEmptyString(responseStatus) && "U".equalsIgnoreCase(responseStatus)){ // to show the unattended checklist's in Compliance/Response screen, the param value is U
			if("RS".equalsIgnoreCase(auditSchedule.getRespondStatus()) 
					&& "WI".equalsIgnoreCase(auditSchedule.getReviewStatus())) { // Condition added to get unattended records post Compliance submitted and Scrutiny initiated
				//strQuery.append("  and obs.response_status in ('U','R','PA') ");
				strQuery.append("  and obs.response_status in ('PA') ");
			}else if("RS".equalsIgnoreCase(auditSchedule.getRespondStatus()) 
					&& "WS".equalsIgnoreCase(auditSchedule.getReviewStatus())) { // Condition added to get unattended records post Compliance submitted and Scrutiny submitted
				//strQuery.append("  and obs.response_status in ('U','R','PA') ");
				strQuery.append("  and obs.response_status in ('PA','R' ) ");
			}else strQuery.append("  and obs.response_status in ('U') "); //Compliance initiated // Condition to include R status while getting unattended records
		}else if(!BankAuditUtil.isEmptyString(responseStatus)){
			strQuery.append("  and obs.response_status=:responseStatus ");
		}
		if(!BankAuditUtil.isEmptyString(reviewStatus)){ // to show the unattended checklist's in Review screen, the param value is U
			if("WS".equalsIgnoreCase(auditSchedule.getReviewStatus())) { // Condition added to get unattended records post Scrutiny submitted
				strQuery.append("  and obs.review_status_attribute in ('FC')"
						+ " and obs.response_status in ('A') ");
			}else strQuery.append("  and obs.review_status=:reviewStatus ");
		}
		if(!BankAuditUtil.isEmptyString(flag)){ // to show the flagged records, the param value is F
			strQuery.append("  and obs.flag=:flag ");
		}
		if(!BankAuditUtil.isEmptyString(criticalityToggle)){
			strQuery.append("  and obs.observation_criticality=:criticalityToggle ");
		}
		if(!BankAuditUtil.isEmptyString(submissionStatus) && BankAuditUtil.isEmptyString(reportRvwFedbk)){ // to show the unattended checklist's in Inspection screen, the param value is U
			strQuery.append("  and obs.submission_status=:submissionStatus ");
		}
		if(!BankAuditUtil.isEmptyString(reportRvwFedbk) && BankAuditUtil.isEmptyString(submissionStatus) ){
			//strQuery.append("  and obs.review_note_maker_timestamp is not null ");	
			if("G2".equalsIgnoreCase(role) || "G8".equalsIgnoreCase(role) || "G12".equalsIgnoreCase(role) ){
				if(reportRvwFedbk.equalsIgnoreCase("Y") && "AC".equalsIgnoreCase(auditSchedule.getAuditStatus())){
					strQuery.append("and (obs.review_status_attribute='FC' OR obs.review_status_attribute='R' )  ");
				}
				else{
					strQuery.append(" and obs.observation_id in  (Select notes.observation_id from audit_observation_report_review_capture notes "
						+ " Where notes.audit_id =:auditId and notes.legal_entity_code=:legalEntityCodeStr "
						+ " and capture_type ='G12'  ) " );
				}
			}else if("G3".equalsIgnoreCase(role) || "G31".equalsIgnoreCase(role)
					|| "G4".equalsIgnoreCase(role) || "G7".equalsIgnoreCase(role)){
				strQuery.append(" and obs.observation_id in   (Select review.observation_id from audit_observation_respond_review_capture review "
						+ " Where review.audit_id =:auditId and review.legal_entity_code=:legalEntityCodeStr "
						+ " and (capture_type ='G4' OR capture_type ='G7' ) )" );
			}
		}else{
			if("G12".equalsIgnoreCase(role) && BankAuditUtil.isEmptyString(reportRvwFedbk) 
					&& !BankAuditUtil.isEmptyString(submissionStatus) ) {  // G12=  Review team
				strQuery.append(" and obs.observation_id not in   (Select notes.observation_id from audit_observation_report_review_capture notes "
						+ " Where notes.audit_id =:auditId and notes.legal_entity_code=:legalEntityCodeStr "
						+ " and capture_type ='G12' ) " );
			} 
		} 		 
		
		if(!BankAuditUtil.isEmptyString(search)){
			strQuery.append(" and ( "
					+ " f.finding_name like :search "
					+ " OR f.finding_code like :search"
					+ " OR obs.observation_id like :search"
					+ " OR obs.compliance_category like :search "
					+ " OR obs.observation_criticality like :search " 
					+ "  ) ");
		}
		
		
		if(rolesListInspector.contains(role) || "G12".equalsIgnoreCase(role) ){
			strQuery.append(" 	ORDER BY FIELD(obs.submission_status, 'U','PA','A'), obs.observation_id asc ");
		}else if(rolesListCompliance.contains(role)){
			strQuery.append(" 	ORDER BY FIELD(obs.response_status, 'U','R','PA','A'), obs.observation_id asc ");
		}else if(rolesListReviewer.contains(role)){
			strQuery.append(" 	ORDER BY FIELD(obs.review_status, 'U','PA','A'), obs.response_status, FIELD(obs.review_status_attribute, 'FC','A','R'), obs.observation_id asc ");
		}
		
		 	
			//strQuery.append("  order by  f.finding_name"); 
			@SuppressWarnings("deprecation")
			Query query=(Query) session.createSQLQuery(strQuery.toString()).setResultTransformer(new ResultTransformer() {
				@Override
				public Object transformTuple(Object[] tuple, String[] aliases) {
					ChecklistDto checklistDto=new ChecklistDto();
					int i=0;
					if(tuple[i] !=null) checklistDto.setAuditId(tuple[i].toString());
					i++; if(tuple[i] !=null) checklistDto.setAuditTypeCode(tuple[i].toString());
					i++; if(tuple[i] !=null) checklistDto.setUnitId(tuple[i].toString());
					i++; if(tuple[i] !=null) checklistDto.setUnitName(tuple[i].toString()); // 3
					i++; if(tuple[i] !=null) checklistDto.setObservationId(tuple[i].toString());
					i++; if(tuple[i] !=null) checklistDto.setGroupObservationId(tuple[i].toString());
					i++; if(tuple[i] !=null) checklistDto.setMappingId(tuple[i].toString());
					i++; if(tuple[i] !=null) checklistDto.setMappingCode(tuple[i].toString()); // 7
					i++; if(tuple[i] !=null) checklistDto.setComplianceCategory(tuple[i].toString());
					i++; if(tuple[i] !=null) checklistDto.setObservationCriticality(tuple[i].toString());
					i++; if(tuple[i] !=null) checklistDto.setFindingRemarks(tuple[i].toString());
					i++; if(tuple[i] !=null) checklistDto.setRemarks(tuple[i].toString());
					i++; if(tuple[i] !=null) checklistDto.setSubmissionStatus(tuple[i].toString());  // 12
					
					i++; if(tuple[i] !=null) checklistDto.setChapterCode(tuple[i].toString());  // 13
					i++; if(tuple[i] !=null) checklistDto.setChapter(tuple[i].toString());
					i++; if(tuple[i] !=null) checklistDto.setSectionCode(tuple[i].toString());  // 15
					i++; if(tuple[i] !=null) checklistDto.setSection(tuple[i].toString()); 
					i++; if(tuple[i] !=null) checklistDto.setProductCode(tuple[i].toString());  // 17
					i++; if(tuple[i] !=null) checklistDto.setProduct(tuple[i].toString());
					i++; if(tuple[i] !=null) checklistDto.setProcessCode(tuple[i].toString());  // 19
					i++; if(tuple[i] !=null) checklistDto.setProcess(tuple[i].toString());
					i++; if(tuple[i] !=null) checklistDto.setFindingCode(tuple[i].toString()); //21
					i++; if(tuple[i] !=null) checklistDto.setFindingName(tuple[i].toString()); 
					
					i++; if(tuple[i] !=null) checklistDto.setChapterHindi(tuple[i].toString());  //23
					i++; if(tuple[i] !=null) checklistDto.setSectionHindi(tuple[i].toString());  //24
					i++; if(tuple[i] !=null) checklistDto.setProductHindi(tuple[i].toString());  //25
					i++; if(tuple[i] !=null) checklistDto.setProcessHindi(tuple[i].toString());  //26
					i++; if(tuple[i] !=null) checklistDto.setFindingNameHindi(tuple[i].toString()); //27
					
					i++; if(tuple[i] !=null) checklistDto.setResponseStatus(tuple[i].toString()); //28
					i++; if(tuple[i] !=null) checklistDto.setReviewStatus(tuple[i].toString()); //29
					i++; if(tuple[i] !=null) checklistDto.setReviewStatusAttribute(tuple[i].toString()); //30
					i++; if(tuple[i] !=null) checklistDto.setLastResponseReviewRemarks(tuple[i].toString()); //31
					i++; if(tuple[i] !=null) checklistDto.setAllResponseReviewRemarks(tuple[i].toString()); //32
					
					i++; if(tuple[i] !=null) checklistDto.setResponseRemarks(tuple[i].toString()); //33
					i++; if(tuple[i] !=null) checklistDto.setReviewRemarks(tuple[i].toString()); //34
					
					if(rolesListReviewer.contains(role)){
						i++; if(tuple[i] !=null) checklistDto.setDeskAssessedScore(tuple[i].toString()); //35
						i++; if(tuple[i] !=null) checklistDto.setDeskEffectiveScore(tuple[i].toString());  //36
						i++; if(tuple[i] !=null) checklistDto.setDeskReviewerMakerRemarks(tuple[i].toString());  //37					
					}										
					return checklistDto;
				}				
				@Override
				public List transformList(List collection) {
						return collection;
					}
				});
					
				query.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditId", auditId)
				.setParameterList("rolesList", rolesList) 
				.setParameter("statusAuth", BankAuditConstant.STATUS_AUTH);
				
				//if(!rolesListInspector.contains(role)){
				if(rolesListCompliance.contains(role) || rolesListReviewer.contains(role)){
					query.setParameter("complianceCategory",BankAuditConstant.COMPLIANCE_NO);
				} 
				
				if(!BankAuditUtil.isEmptyString(auditGroupCode)){
					query.setParameter("auditGroupCode", auditGroupCode);
				}				 
				if(!BankAuditUtil.isEmptyString(auditSubGroupCode)){
					query.setParameter("auditSubGroupCode", auditSubGroupCode);
				}
				if(!BankAuditUtil.isEmptyString(activityCode)){
					query.setParameter("activityCode", activityCode);
				}
				if(!BankAuditUtil.isEmptyString(processCode)){
					query.setParameter("processCode", processCode);		
				}
				if(!BankAuditUtil.isEmptyString(complianceCategory)){
					query.setParameter("complianceCategory", complianceCategory);		
				}
				if(!BankAuditUtil.isEmptyString(nonCompliancePersistence)){ 
					query.setParameter("nonCompliancePersistence", nonCompliancePersistence); 
				}
				
				if(!BankAuditUtil.isEmptyString(submissionStatus)){ 
					query.setParameter("submissionStatus", submissionStatus );
				}
				if(!BankAuditUtil.isEmptyString(responseStatus) && "U".equalsIgnoreCase(responseStatus)){ // Condition to include R status while getting unattended records
					//strQuery.append("  and obs.response_status in ('U','R') ");
				}else if(!BankAuditUtil.isEmptyString(responseStatus)){
					query.setParameter("responseStatus",responseStatus );
				}
				if(!BankAuditUtil.isEmptyString(reviewStatus)){ 
					if("WS".equalsIgnoreCase(auditSchedule.getReviewStatus())); // Condition added to get unattended records post Scrutiny submitted
					else query.setParameter("reviewStatus", reviewStatus );
				}
				if(!BankAuditUtil.isEmptyString(flag)){ 
					query.setParameter("flag", flag);
				}
				if(!BankAuditUtil.isEmptyString(criticalityToggle)){
					query.setParameter("criticalityToggle", criticalityToggle);
				}
				
				if(!BankAuditUtil.isEmptyString(search)){
					query.setParameter("search","%"+ search+"%");
				}
				
				/*if(!BankAuditUtil.isEmptyString(documentType)){
					query.setParameter("documentType", documentType);
				}*/
				
				/*if(!BankAuditUtil.isEmptyString(documentSubType)){
					query.setParameter("documentSubType", documentSubType);
				}*/
									
				List<ChecklistDto> tmpChecklistDto =query.list();  				
				DataTableResponse dataTableResponse=new DataTableResponse();
				dataTableResponse.setData(tmpChecklistDto); 
						
		return dataTableResponse;
	}
	 
	 
	//Update offline inspection  	
	
	public AuditObservation getAuditObservation(ChecklistOfflineInspection  checklistUploadDto){
		try{
			 Session session =getSession();		
			 session.setDefaultReadOnly(true);
			
			 String selectQry =" From  AuditObservation   " 
			 		+ " Where auditId  =:auditId "
					+ " and auditTypeCode =:auditTypeCode "
					+ " and observationId  =:observationId "
					+ " and mappingId  =:mappingId "
					+ " and legalEntityCode =:legalEntityCode ";
			 
			Query qry  = session.createQuery(selectQry)
					.setParameter("auditId", checklistUploadDto.getAuditId())
					.setParameter("auditTypeCode", checklistUploadDto.getAuditTypeCode())
					.setParameter("observationId", checklistUploadDto.getObservationId())
					.setParameter("mappingId", checklistUploadDto.getMappingId())
					.setParameter("legalEntityCode", checklistUploadDto.getLegalEntityCode());
			
			AuditObservation auditObservation = (AuditObservation) qry.uniqueResult();
			 return auditObservation;
		}catch (Exception e) { 
			logger.error("Error .." + e.getMessage()+" :: "+e.getCause());
		}
		return null;
	}
	
	public String updateOfflineInspection(ChecklistOfflineInspection  checklistUploadDto ){
		
		try{			
			Session session =getSession();  		
			String updateQry = "Update AuditObservation "
					+ " set  findingRemarks =:findingRemarks,  "
					+ " submissionStatus =:submissionStatus,  "
					+ " complianceCategory =:complianceCategory, "
					+ " observationCriticality =:observationCriticality "
					+ " Where auditId  =:auditId "
					+ " and auditTypeCode =:auditTypeCode "
					+ " and observationId  =:observationId "
					+ " and mappingId  =:mappingId "
					+ " and legalEntityCode =:legalEntityCode ";
			
			int status = session.createQuery(updateQry)
			.setParameter("findingRemarks", checklistUploadDto.getIoComments())
			.setParameter("submissionStatus", "PA")
			.setParameter("complianceCategory", checklistUploadDto.getComplianceStatus())
			.setParameter("observationCriticality", checklistUploadDto.getCriticality())
			.setParameter("auditId", checklistUploadDto.getAuditId())
			.setParameter("auditTypeCode", checklistUploadDto.getAuditTypeCode())
			.setParameter("observationId", checklistUploadDto.getObservationId())
			.setParameter("mappingId", checklistUploadDto.getMappingId())
			.setParameter("legalEntityCode", checklistUploadDto.getLegalEntityCode())
			.executeUpdate();			
			if(status>0){
				return BankAuditConstant.SUCCESS;
			}			
		}catch (Exception e) { 
			logger.error("Error .." + e.getMessage()+" :: "+e.getCause());
		}
		return BankAuditConstant.FAILED;
	}
	 
	@Override
    public String  getStandardStatementForReportTable(ChecklistOfflineInspection  checklistUploadDto, String reportLanguage){
    	 
    	try{
	    	Session session=getSession();
			session.setDefaultReadOnly(true);
			StringBuffer strQuery = new StringBuffer( "");	
			String printFlag="";			  		 
			
		    if(checklistUploadDto.getComplianceStatus().equalsIgnoreCase(BankAuditConstant.COMPLIANCE_YES)){
	    		 strQuery.append(" select concat(ifnull(stmt.standard_stmnt_compliance, ' '), '#::#', ifnull(stmt.standard_stmnt_compliance_hindi, ' ')  )     as stndstmt ");
	    		 printFlag=" and (standard_stmnt_compliance_print=:printFlag OR standard_stmnt_compliance_print is null)";
	    	 }else if(checklistUploadDto.getComplianceStatus().equalsIgnoreCase(BankAuditConstant.COMPLIANCE_NO)){
	    		 strQuery.append(" select concat(ifnull(stmt.standard_stmnt_non_compliance, ' '), '#::#', ifnull(stmt.standard_stmnt_non_compliance_hindi, ' ')  )    as stndstmt "); 
	    		 printFlag=" and ( standard_stmnt_non_compliance_print=:printFlag   OR standard_stmnt_non_compliance_print is null) ";
	    	 }else{
	    		 return null;
	    	 } 
		    
	    	 strQuery.append("  from  activity_process_finding_mapping mapping      "
				+" inner join audit_observation  abr on  mapping.mapping_id = abr.mapping_id         "
				+" Inner join maint_audit_finding find on mapping.finding_code =  find.finding_code   "
				+" left join  maint_report_standard_stmt stmt on  mapping.mapping_id = stmt.mapping_id "
				+" where "					 
				+" abr.audit_id =:auditId    "
				+" and abr.audit_type_code =:auditTypeCode  " 
				+" and mapping.legal_entity_code =:legalEntityCode "
				+" and mapping.finding_code =:findingCode  "
				+" and find.entity_status=:entityStatus "
				+" and mapping.mapping_id =:mappingId ");
	    	 strQuery.append(printFlag);
						
			Query query=(Query) session.createSQLQuery(strQuery.toString()) 
					.setParameter("auditId", checklistUploadDto.getAuditId())
					.setParameter("auditTypeCode", checklistUploadDto.getAuditTypeCode())  
					.setParameter("legalEntityCode", checklistUploadDto.getLegalEntityCode())
					.setParameter("findingCode", checklistUploadDto.getChecklistCode())
					.setParameter("mappingId", checklistUploadDto.getMappingId())
					.setParameter("entityStatus", BankAuditConstant.STATUS_ACTIVE)
					.setParameter("printFlag", BankAuditConstant.YES);			
			
			return (String) query.uniqueResult();	
			
		}catch (Exception e) { 
			logger.error("Error .." + e.getMessage()+" :: "+e.getCause());
			return BankAuditConstant.FAILED;
		} 
    }
	
}
