package com.bankaudit.report.v3.dao;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import org.hibernate.Session;
import org.hibernate.query.Query;
import org.hibernate.transform.ResultTransformer;
import org.jboss.logging.Logger;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.bankaudit.constants.BankAuditConstant;
import com.bankaudit.dao.AbstractDao;
import com.bankaudit.process.model.AuditReportDynamicTable;
import com.bankaudit.process.model.AuditReportDynamicTableDetails;
import com.bankaudit.process.model.IfcEnsureBaasIRProcessData;
//import com.bankaudit.model.AuditReportCoverPage;
import com.bankaudit.report.v3.dto.ChapterDto;
import com.bankaudit.report.v3.dto.CheckListDto;
import com.bankaudit.report.v3.dto.ProcessDto;
import com.bankaudit.report.v3.dto.ProductDto;
import com.bankaudit.report.v3.dto.SectionDto;
import com.bankaudit.report.v3.dto.StaticDerivedStatementDTO;
import com.bankaudit.report.v3.dto.TableDataColumnNumberDto;
import com.bankaudit.report.v3.dto.TableDataDto;
import com.bankaudit.util.BankAuditUtil;

@Repository("auditBookReportDaoV3")
@Transactional("transactionManager")
public class AuditBookReportDaoImplV3 extends AbstractDao  implements AuditBookReportDaoV3 {
	
	static final Logger logger = Logger.getLogger(AuditBookReportDaoImplV3.class);

	public Object[] getCoverPageDynamicData(Integer legalEntityCode, String auditId){
		 Session session = getSession();		    
		 session.setDefaultReadOnly(true);
		  
		 
		 String sqlQuery = "  select ifnull(audit_serial_no,''),  "
		 		+ "   entity.unit_name,  "
				+ "  address.city_name,  "
				+ "  sch.financial_year ,  "
				+ "  entity.parent_unit_code, " 
				+ "  (select a1.city_name "
						+ "  from maint_entity e1, maint_address a1 "
						+ "	 where e1.city=a1.city_code "
						+ "	 and e1.unit_code=entity.parent_unit_code limit 1) as parent_unit_city_name, "			
				+ "   sch.report_dispatch_date, "  // Changed to dispatched instead of Finalize date
				+ " (SELECT a1.state_name   "
				+ " 		  FROM maint_entity e1, maint_address a1   "
				+ " 			 WHERE e1.state=a1.state_code   "
				+ " 			 AND e1.unit_code=entity.parent_unit_code LIMIT 1) AS parent_unit_state_name,	  "    
				+ "    address.state_name,    "
				+ " (SELECT e1.unit_name   "
				+ " 		  FROM maint_entity e1    "
				+ " 			 WHERE e1.unit_code=entity.parent_unit_code LIMIT 1) AS parent_unit_name, " //9
				+ " (SELECT e2.unit_name  "
				+ "  		  FROM maint_entity e1, maint_entity e2   "
				+ "  			 WHERE e1.unit_code=entity.parent_unit_code "
				+ "  			 AND e1.parent_unit_code = e2.unit_code LIMIT 1) AS parent_of_parent_unit_name," //10
                + " (SELECT a1.state_name  "
				+ "  		  FROM maint_entity e1, maint_entity e2, maint_address a1 "
				+ "  			 WHERE e1.unit_code=entity.parent_unit_code "
                + "              AND e1.parent_unit_code = e2.unit_code "
                + "              AND e2.state=a1.state_code LIMIT 1) AS parent_of_parent_unit_state_name, " // 11                
                + "  sch.schedule_start_date ,  " //12
                + "  sch.schedule_end_date,   " //13
                + "  sch.actual_start_date,   " //14
                + "  sch.actual_end_date,   " //15
                + "  entity.unit_type  "  //16
				+ "  from audit_schedule sch   "
				+ "  INNER JOIN  maint_entity entity on sch.unit_id=entity.unit_code  "
				+ "  INNER JOIN maint_address address on entity.city=address.city_code  "
				+ "  and entity.district = address.district_code   "
				//+ "  and entity.state =address.state_code  "
				+ "  and sch.legal_entity_code=entity.legal_entity_code " 
				+ "  and sch.legal_entity_code=address.legal_entity_code  "				
				+ "  and sch.legal_entity_code= '"+legalEntityCode+"' "
				+ "  and sch.audit_id= '"+auditId+"' ";
		 
		  Query query=session.createSQLQuery(sqlQuery);
		  List<String[]> coverPageData = query.list();
		 
		  if(coverPageData==null || coverPageData.isEmpty()){
			  return null;
		  }
		  Object[] objArray = coverPageData.get(0);  
		 // String[] coverPageDataArray = Arrays.copyOf(objArray, objArray.length, String[].class);
		  return   objArray;
	}
	
	@Override
	public List<ChapterDto> getChapter(Integer legalEntityCode, String auditId) {
		 Session session = getSession();		    
		 session.setDefaultReadOnly(true);
		 //asd.audit_id = 'A0002'
		 
		 String sqlQuery = " select rpt.audit_group_code, rpt.audit_group_name, rpt.audit_group_name_hindi  from  audit_report_stmt rpt  "				 
						+" where rpt.audit_id = '"+auditId+"' "
						+" and rpt.legal_entity_code = '"+legalEntityCode+"'  " 
						+" and (rpt.audit_sub_group_code is null or rpt.audit_sub_group_code='' ) "
						+" and rpt.applicable_for = '"+BankAuditConstant.FOR_CHAPTER+"' "
						+" and (rpt.statement_type  is null or rpt.statement_type='' ) "
						+" and lower(rpt.audit_group_name) != '"+BankAuditConstant.INTRODUCTION_CHAPTER+"' " 
					   // +" group by  rpt.audit_group_code, rpt.audit_group_name  "
						+" order by cast(rpt.sequence  as UNSIGNED)  ASC  ";
		
		  
		 
		 List<ChapterDto> listChapterDto = null;
		 Query query=session.createSQLQuery(sqlQuery)
					.setResultTransformer(new ResultTransformer() {
						
						@Override
						public Object transformTuple(Object[] tuple, String[] aliases) {
							ChapterDto chapterDto=null;
							if(tuple!=null){								
								chapterDto=new ChapterDto();							 
								/*if(tuple[0]!=null){                   
									chapterDto.setSequenceNumber(tuple[0].toString()); 
								}*/
								if(tuple[0]!=null){                   
									chapterDto.setAuditGroupCode(tuple[0].toString()); 
								}
								if(tuple[1]!=null){
									chapterDto.setChapterName(tuple[1].toString());
								}
								if(tuple[2]!=null){
									chapterDto.setChapterNameHindi(tuple[2].toString());
								}
							}
							return chapterDto;
						}
						@Override
						public List transformList(List collection) {
							return collection;
						}
					});
		    
		 listChapterDto = query.list();
		 return listChapterDto;
	}

	@Override
	public List<SectionDto> getSection(Integer legalEntityCode, String auditId, String auditGroupCode) {
		
		 Session session = getSession();		    
		 session.setDefaultReadOnly(true);
		 
		 String sqlQuery = " select rpt.audit_sub_group_code, rpt.audit_sub_group_name,  rpt.audit_sub_group_name_hindi from  audit_report_stmt rpt  "				 
					+" where   rpt.legal_entity_code = '"+legalEntityCode+"'  "
					+" and rpt.audit_id = '"+auditId+"' "
					+" and  rpt.audit_group_code = '"+auditGroupCode+"'"
					+" and rpt.audit_sub_group_code is not null "
					+" and rpt.applicable_for = '"+BankAuditConstant.FOR_SECTION+"' "
					+" and (rpt.statement_type   is null or  rpt.statement_type='' ) "
				   // +" group by  rpt.audit_sub_group_code, rpt.audit_sub_group_name  "
					+" order by cast(rpt.sequence  as UNSIGNED)  ASC  ";
		 		  		 
		 List<SectionDto> listSectionDto = null;
		 Query query=session.createSQLQuery(sqlQuery)
					.setResultTransformer(new ResultTransformer() {
						
						@Override
						public Object transformTuple(Object[] tuple, String[] aliases) {
							SectionDto sectionDto=null;
							if(tuple!=null){								
								sectionDto=new SectionDto();									 
								/*if(tuple[0]!=null){                   
									sectionDto.setSequenceNumber(tuple[0].toString());
								}*/
								if(tuple[0]!=null){                   
									sectionDto.setAuditSubGroupCode(tuple[0].toString());
								}
								if(tuple[1]!=null){
									sectionDto.setSectionName(tuple[1].toString());
								}
								if(tuple[2]!=null){
									sectionDto.setSectionNameHindi(tuple[2].toString());
								}
							}
							return sectionDto;
						}
						@Override
						public List transformList(List collection) {
							return collection;
						}
					});
		    
		 listSectionDto = query.list();
		 return listSectionDto;
	}

	@Override
	public List<ProductDto> getProduct(Integer legalEntityCode, String auditId, String auditGroupCode, String auditSubGroupCode) {
		 
		Session session = getSession();		    
		session.setDefaultReadOnly(true);
		
		String sqlQuery = " select rpt.activity_code, rpt.activity_name, rpt.activity_name_hindi  from  audit_report_stmt rpt  "				 
				+" where   rpt.legal_entity_code = '"+legalEntityCode+"'  "
				+" and rpt.audit_id = '"+auditId+"' "
				+" and  rpt.audit_group_code = '"+auditGroupCode+"'"
				+" and  rpt.audit_sub_group_code = '"+auditSubGroupCode+"'"
				+" and rpt.activity_code is not null "
				+" and rpt.applicable_for = '"+BankAuditConstant.FOR_PRODUCT+"' "
				+" and (rpt.statement_type   is null or  rpt.statement_type='' ) "
			   // +" group by  rpt.activity_code, rpt.activity_name "
				+" order by cast(rpt.sequence  as UNSIGNED)  ASC  ";
		 
		 
		 List<ProductDto> listProductDto = null;
		 Query query=session.createSQLQuery(sqlQuery)
					.setResultTransformer(new ResultTransformer() {
						
						@Override
						public Object transformTuple(Object[] tuple, String[] aliases) {
							ProductDto productDto=null;
							if(tuple!=null){
								
								productDto=new ProductDto();								
								/*if(tuple[0]!=null){                   
									productDto.setSequenceNumber(tuple[0].toString());
								}*/								
								if(tuple[0]!=null){                   
									productDto.setActivityCode(tuple[0].toString());
								}
								if(tuple[1]!=null){
									productDto.setProductName(tuple[1].toString());
								}
								if(tuple[2]!=null){
									productDto.setProductNameHindi(tuple[2].toString());
								}
							}
							return productDto;
						}
						@Override
						public List transformList(List collection) {
							return collection;
						}
					});
		    
		 listProductDto = query.list();
		 return listProductDto;
	}

	@Override
	public List<ProcessDto> getProcess(Integer legalEntityCode, String auditId, String auditGroupCode, String auditSubGroupCode,
			String activityCode) {
		
		Session session = getSession();		    
		session.setDefaultReadOnly(true);
		
		String sqlQuery = " select rpt.process_code, rpt.process_name, rpt.process_name_hindi   from  audit_report_stmt rpt  "				 
				+" where   rpt.legal_entity_code = '"+legalEntityCode+"'  "
				+" and rpt.audit_id = '"+auditId+"' "
				+" and  rpt.audit_group_code = '"+auditGroupCode+"'"
				+" and  rpt.audit_sub_group_code = '"+auditSubGroupCode+"'"
				+" and  rpt.activity_code = '"+activityCode+"'"
				+" and rpt.process_code is not null "
				+" and rpt.applicable_for = '"+BankAuditConstant.FOR_PROCESS+"' "
				+" and (rpt.statement_type  is null or  rpt.statement_type='' ) "
			  //  +" group by  rpt.process_code, rpt.process_name  "
				+" order by cast(rpt.sequence  as UNSIGNED)  ASC  ";
		 
		 
		 List<ProcessDto> listProcessDto = null;
		 Query query=session.createSQLQuery(sqlQuery)
					.setResultTransformer(new ResultTransformer() {
						
						@Override
						public Object transformTuple(Object[] tuple, String[] aliases) {
							ProcessDto processDto=null;
							if(tuple!=null){								
								processDto=new ProcessDto();	
								/*if(tuple[0]!=null){
									processDto.setSequenceNumber(tuple[0].toString()); 
								}*/
								if(tuple[0]!=null){
									processDto.setProcessCode(tuple[0].toString()); 
								}
								if(tuple[1]!=null){                   
									processDto.setProcessName(tuple[1].toString());
								}
								if(tuple[2]!=null){                   
									processDto.setProcessNameHindi(tuple[2].toString());
								}
							}
							return processDto;
						}
						@Override
						public List transformList(List collection) {
							return collection;
						}
					});
		    
		 listProcessDto = query.list();
		 return listProcessDto;
	}

	@Override
	public List<CheckListDto> getChecklist(Integer legalEntityCode, String auditId, String auditGroupCode, String auditSubGroupCode,
			String activityCode, String processCode, String isComplied ) {
		Session session = getSession();		    
		session.setDefaultReadOnly(true);
		List<String> isCompliedList = Arrays.asList(isComplied.split(","));
		String  sqlQuery = " select rpt.finding_code, rpt.finding_name, rpt.compliance_category,  rpt.finding_name_hindi from  audit_report_stmt rpt  "				 
				+" where   rpt.legal_entity_code = '"+legalEntityCode+"'  "
				+" and rpt.audit_id = '"+auditId+"' "
				+" and  rpt.audit_group_code = '"+auditGroupCode+"'"
				+" and  rpt.audit_sub_group_code = '"+auditSubGroupCode+"'"
				+" and  rpt.activity_code = '"+activityCode+"'"
				+" and  rpt.process_code = '"+processCode+"'"
				//+" and  rpt.compliance_category = '"+isComplied+"' "
				+" and  rpt.compliance_category in (:isCompliedList) "
				+" and  rpt.finding_code is not null "
				+" and rpt.applicable_for = '"+BankAuditConstant.FOR_CHECKLIST+"' "
				+" and   rpt.statement_type= '"+BankAuditConstant.FOR_CHECKLIST+"'  "
			  //	+" and (rpt.statement_type  is null or  rpt.statement_type='' ) "
			  //  +" group by  rpt.finding_code, rpt.statement "
				+" order by cast(rpt.sequence  as UNSIGNED)  ASC  ";
	 
		 List<CheckListDto> listCheckListDto = null;
		 Query query=session.createSQLQuery(sqlQuery)
				 .setParameterList("isCompliedList", isCompliedList)
				 .setResultTransformer(new ResultTransformer() {
						
						@Override
						public Object transformTuple(Object[] tuple, String[] aliases) {
							CheckListDto checkListDto=null;
							if(tuple!=null){
								
								checkListDto=new CheckListDto();
								 
							/*	if(tuple[0]!=null){  									
									checkListDto.setChecklist(tuple[0].toString());  
								}*/
								if(tuple[0]!=null){  						 
									checkListDto.setFindingCode(tuple[0].toString());  
								}
								if(tuple[1]!=null){  						 
									checkListDto.setChecklist(tuple[1].toString());  
								}
								if(tuple[2]!=null){  						 
									checkListDto.setIsComplied(tuple[2].toString());  
								}
								if(tuple[3]!=null){  						 
									checkListDto.setChecklistHindi(tuple[3].toString());  
								}
								 								  
							}
							return checkListDto;
						}
						@Override
						public List transformList(List collection) {
							return collection;
						}
					}); 
		    
		 listCheckListDto = query.list();
		 /*if(!isComplied.equalsIgnoreCase("C"))
		 for (CheckListDto checkListDto : listCheckListDto) {
			System.out.println("======================================"+ checkListDto.getChecklist());
		}*/
		 return listCheckListDto;		 
	}


	
	@Override
	public List<String> getDerivedAndStaticStatementForChapter(Integer legalEntityCode, String auditId,  String auditGroupCode) {
		// TODO Auto-generated method stub
		Session session = getSession();		    
		session.setDefaultReadOnly(true);
		 
		 String sqlQuery = " select  rpt.statement  from  audit_report_stmt rpt  "				 
			+" where   rpt.legal_entity_code = '"+legalEntityCode+"'  "
			+" and rpt.audit_id = '"+auditId+"' "
			+" and  rpt.audit_group_code = '"+auditGroupCode+"'"
			+" and  (rpt.audit_sub_group_code is null OR rpt.audit_sub_group_code='') "
			+" and  (rpt.activity_code is null OR rpt.activity_code ='' ) "
			+" and  (rpt.process_code is null  OR rpt.process_code ='') "
			+" and  (rpt.finding_code is null OR finding_code='' ) "
			+" and  (rpt.compliance_category  is null OR rpt.compliance_category='') "
			+" and  rpt.applicable_for = '"+BankAuditConstant.FOR_CHAPTER+"'  "
			+" and  ( rpt.print_flag = 'Yes' OR  rpt.print_flag is null ) "
			+" and rpt.statement_type  in('"+BankAuditConstant.FOR_DERIVED_STMT+"', '"+BankAuditConstant.FOR_STATIC_STMT+"') "
			+" order by cast(rpt.sequence  as UNSIGNED)  ASC  ";
		
		 
		 Query query=session.createSQLQuery(sqlQuery);		 
		 return query.list();
	}

	@Override
	public List<String> getDerivedAndStaticStatementForSection(Integer legalEntityCode, String auditId, String auditGroupCode,
			String auditSubGroupCode) {
		Session session = getSession();		    
		session.setDefaultReadOnly(true); 
		
		String sqlQuery = " select  rpt.statement  from  audit_report_stmt rpt  "				 
				+" where   rpt.legal_entity_code = '"+legalEntityCode+"'  "
				+" and rpt.audit_id = '"+auditId+"' "
				+" and  rpt.audit_group_code = '"+auditGroupCode+"' "
				+" and  rpt.audit_sub_group_code = '"+auditSubGroupCode+"' "
				+" and  (rpt.activity_code is null OR rpt.activity_code ='' ) "
				+" and  (rpt.process_code is null  OR rpt.process_code ='')"
				+" and  (rpt.finding_code is null OR finding_code='' ) "
				+" and  (rpt.compliance_category  is null OR rpt.compliance_category='')"  
				+" and rpt.applicable_for = '"+BankAuditConstant.FOR_SECTION+"' "
				+" and  ( rpt.print_flag = 'Yes' OR  rpt.print_flag is null ) "
				+" and rpt.statement_type  in('"+BankAuditConstant.FOR_DERIVED_STMT+"', '"+BankAuditConstant.FOR_STATIC_STMT+"') "
				+" order by cast(rpt.sequence  as UNSIGNED)  ASC  ";
		 
		 Query query=session.createSQLQuery(sqlQuery);		 
		 return query.list();
	}

	@Override
	public List<String> getDerivedAndStaticStatementForProduct(Integer legalEntityCode, String auditId, String auditGroupCode,
			String auditSubGroupCode, String activityCode) {
		Session session = getSession();		    
		session.setDefaultReadOnly(true);
		 
		String sqlQuery = " select  rpt.statement  from  audit_report_stmt rpt  "				 
				+" where   rpt.legal_entity_code = '"+legalEntityCode+"'  "
				+" and rpt.audit_id = '"+auditId+"' "
				+" and  rpt.audit_group_code = '"+auditGroupCode+"' "
				+" and  rpt.audit_sub_group_code = '"+auditSubGroupCode+"' "
				+" and  (rpt.activity_code  = '"+activityCode+"'  ) "
				+" and  (rpt.process_code is null  OR rpt.process_code ='')"
				+" and  (rpt.finding_code is null OR finding_code='' ) "
				+" and  (rpt.compliance_category  is null OR rpt.compliance_category='') "  
				+" and rpt.applicable_for = '"+BankAuditConstant.FOR_PRODUCT+"' "
				+" and  ( rpt.print_flag = 'Yes' OR  rpt.print_flag is null ) "
				+" and rpt.statement_type  in('"+BankAuditConstant.FOR_DERIVED_STMT+"', '"+BankAuditConstant.FOR_STATIC_STMT+"') "
				+" order by cast(rpt.sequence  as UNSIGNED)  ASC  ";
		 
		 Query query=session.createSQLQuery(sqlQuery);		 
		 return query.list();
	}

	@Override
	public List<String> getDerivedAndStaticStatementForProcess(Integer legalEntityCode, String auditId, String auditGroupCode,
			String auditSubGroupCode, String activityCode, String processCode) {
		Session session = getSession();		    
		session.setDefaultReadOnly(true);
		 
		String sqlQuery = " select  rpt.statement  from  audit_report_stmt rpt  "				 
				+" where   rpt.legal_entity_code = '"+legalEntityCode+"'  "
				+" and rpt.audit_id = '"+auditId+"' "
				+" and  rpt.audit_group_code = '"+auditGroupCode+"' "
				+" and  rpt.audit_sub_group_code = '"+auditSubGroupCode+"' "
				+" and  rpt.activity_code  = '"+activityCode+"'   "
				+" and  rpt.process_code = '"+processCode+"' "
				+" and  (rpt.finding_code is null OR finding_code='' ) "
				+" and  (rpt.compliance_category  is null OR rpt.compliance_category='')" 
				+" and rpt.applicable_for = '"+BankAuditConstant.FOR_PROCESS+"' "
				+" and rpt.statement_type  in('"+BankAuditConstant.FOR_DERIVED_STMT+"', '"+BankAuditConstant.FOR_STATIC_STMT+"') "
				+" order by cast(rpt.sequence  as UNSIGNED)  ASC  ";
		 
		 Query query=session.createSQLQuery(sqlQuery);		 
		 return query.list();
	}

	@Override
	public List<String> getDerivedAndStaticStatementForchecklist(Integer legalEntityCode, String auditId, String auditGroupCode,
			String auditSubGroupCode, String activityCode, String processCode, String findingCode) {
		// TODO Auto-generated method stub
		Session session = getSession();		    
		session.setDefaultReadOnly(true);
		 
		String sqlQuery = " select  rpt.statement  from  audit_report_stmt rpt  "				 
				+" where   rpt.legal_entity_code = '"+legalEntityCode+"'  "
				+" and rpt.audit_id = '"+auditId+"' "
				+" and  rpt.audit_group_code = '"+auditGroupCode+"' "
				+" and  rpt.audit_sub_group_code = '"+auditSubGroupCode+"' "
				+" and  rpt.activity_code  = '"+activityCode+"'   "
				+" and  rpt.process_code = '"+processCode+"' "
				+" and  rpt.finding_code =  '"+findingCode+"'  "
				+" and rpt.applicable_for = '"+BankAuditConstant.FOR_CHECKLIST+"' "
				+" and  ( rpt.print_flag = 'Yes' OR  rpt.print_flag is null ) "
				+" and rpt.statement_type  in('"+BankAuditConstant.FOR_DERIVED_STMT+"', '"+BankAuditConstant.FOR_STATIC_STMT+"') "
				+" and   rpt.statement is not null and  rpt.statement!='' "
			//+" and  (rpt.compliance_category  is null OR rpt.compliance_category='')"  
				+" order by cast(rpt.sequence  as UNSIGNED)  ASC  ";
		 
		 Query query=session.createSQLQuery(sqlQuery);		 
		 return query.list();
	}
	
	@Override
	public List<String> getDerivedAndStaticStatementForIntroduction(Integer legalEntityCode,  String auditId,  String auditTypeCode){		
		List<String> intro = new ArrayList<>();
		Session session = getSession();		    
		session.setDefaultReadOnly(true);
		 
		 String sqlQuery = " select  rpt.statement  from  audit_report_stmt rpt  "				 
			+" where rpt.legal_entity_code = '"+legalEntityCode+"'  "
			+" and rpt.audit_id = '"+auditId+"' "
			+" and rpt.audit_type_code ='"+auditTypeCode+"' "  
			+" and rpt.applicable_for = '"+BankAuditConstant.FOR_CHAPTER+"' "
			+" and rpt.statement_type  in('"+BankAuditConstant.FOR_DERIVED_STMT+"', '"+BankAuditConstant.FOR_STATIC_STMT+"') "
			+" and lower(rpt.audit_group_name) = '"+BankAuditConstant.INTRODUCTION_CHAPTER+"' " 
			+" and ( rpt.print_flag = 'Yes' OR  rpt.print_flag is null ) " // to print only the statements with Print as Yes 
			+" order by cast(rpt.sequence  as UNSIGNED)  ASC  ";		 
		 Query query=session.createSQLQuery(sqlQuery);		 
		 intro = query.list();	    
		 return intro; 
	}
 	
	@Override
	public List<String> getRemarksForchecklist(Integer legalEntityCode, String auditId, String auditGroupCode,
			String auditSubGroupCode, String activityCode, String processCode, String findingCode,  String isComplied) {
		// TODO Auto-generated method stub
		Session session = getSession();		    
		session.setDefaultReadOnly(true);
		List<String> isCompliedList = Arrays.asList(isComplied.split(","));
		String sqlQuery = " select   concat(IFNULL(rpt.statement,' '), '#::#' ,IFNULL(rpt.statement_hindi,' '))  from  audit_report_stmt rpt  "	
				//concat('<style isUnderline=\"true\">',rpt.statement  ,'</style>')
				+" where   rpt.legal_entity_code = '"+legalEntityCode+"'  "
				+" and rpt.audit_id = '"+auditId+"' "
				+" and  rpt.audit_group_code = '"+auditGroupCode+"' "
				+" and  rpt.audit_sub_group_code = '"+auditSubGroupCode+"' "
				+" and  rpt.activity_code  = '"+activityCode+"'   "
				+" and  rpt.process_code = '"+processCode+"' "
				+" and  rpt.finding_code =  '"+findingCode+"'  "
				//+" and  rpt.compliance_category = '"+isComplied+"' "
				+" and  rpt.compliance_category in (:isCompliedList) "
				+" and  rpt.applicable_for = '"+BankAuditConstant.FOR_CHECKLIST+"' "
				+" and  rpt.statement_type = '"+BankAuditConstant.FOR_REMARKS+"' "
				+" and   rpt.statement is not null and  rpt.statement!='' ";
			//+" and  (rpt.compliance_category  is null OR rpt.compliance_category='')"  
			//	+" order by cast(rpt.sequence  as UNSIGNED)  ASC  ";
		 
		 Query query=session.createSQLQuery(sqlQuery)
				 .setParameterList("isCompliedList", isCompliedList);		 
		 return query.list();
	}
	
	@Override
	public List<String> getStandardStatement(Integer legalEntityCode, String auditId, String auditTypeCode, String auditGroupCode, 
			String auditSubGroupCode, String activityCode, String processCode, String findingCode, String isComplied) {
		// TODO Auto-generated method stub
		Session session = getSession();		    
		session.setDefaultReadOnly(true);
		List<String> isCompliedList = Arrays.asList(isComplied.split(","));
		 
		String sqlQuery = " select  concat(IFNULL(rpt.statement,' '), '#::#' ,IFNULL(rpt.statement_hindi,' '))  from  audit_report_stmt rpt  "				 
				+" where   rpt.legal_entity_code = '"+legalEntityCode+"'  "
				+" and rpt.audit_id = '"+auditId+"' "
				+" and  rpt.audit_group_code = '"+auditGroupCode+"' "
				+" and  rpt.audit_sub_group_code = '"+auditSubGroupCode+"' "
				+" and  rpt.activity_code  = '"+activityCode+"'   "
				+" and  rpt.process_code = '"+processCode+"' "
				+" and  rpt.finding_code =  '"+findingCode+"'  "
				//+" and  rpt.compliance_category = '"+isComplied+"' "
				+" and  rpt.compliance_category in (:isCompliedList) "
				+" and rpt.applicable_for = '"+BankAuditConstant.FOR_CHECKLIST+"' "
				+" and rpt.statement_type = '"+BankAuditConstant.FOR_STANDARD_STMT+"' "
				+" and  rpt.statement is not null and  rpt.statement!='' " ;
			//+" and  (rpt.compliance_category  is null OR rpt.compliance_category='')"  
			//	+" order by cast(rpt.sequence  as UNSIGNED)  ASC  ";
		 
		 Query query=session.createSQLQuery(sqlQuery)
				 .setParameterList("isCompliedList", isCompliedList);		 
		 return query.list();
	}
	
	 
	@Override
	public List<String> getBankRemarksForchecklist(Integer legalEntityCode, String auditId, String auditGroupCode,
			String auditSubGroupCode, String activityCode, String processCode, String findingCode,  String isComplied){
		try{
		Session session = getSession();		    
		session.setDefaultReadOnly(true);  
		String strQuery = "   select   rpt.statement  from  audit_report_stmt rpt  "	 
				+" where   rpt.legal_entity_code = '"+legalEntityCode+"'  "
				+" and rpt.audit_id = '"+auditId+"' "
				+" and  rpt.audit_group_code = '"+auditGroupCode+"' "
				+" and  rpt.audit_sub_group_code = '"+auditSubGroupCode+"' "
				+" and  rpt.activity_code  = '"+activityCode+"'   "
				+" and  rpt.process_code = '"+processCode+"' "
				+" and  rpt.finding_code =  '"+findingCode+"'  " 
				+" and  rpt.compliance_category =:isComplied "
				+" and  rpt.applicable_for = '"+BankAuditConstant.FOR_CHECKLIST+"' "
				+" and  rpt.statement_type = '"+BankAuditConstant.FOR_REMARKS_BANK+"' "
				+" and   rpt.statement is not null and  rpt.statement!='' " 
				+" order by cast(rpt.sequence  as UNSIGNED)  ASC " ;
		  
		 
		 Query query=session.createSQLQuery(strQuery) 
				 .setParameter("isComplied", isComplied);	
		 return query.list();
		} catch (Exception e) {
			System.out.println(e);
		}return null;
	}
	
	@Override
	public List<String> getBankRemarksForchecklistHindi(Integer legalEntityCode, String auditId, String auditGroupCode,
			String auditSubGroupCode, String activityCode, String processCode, String findingCode,  String isComplied){
		try{
		Session session = getSession();		    
		session.setDefaultReadOnly(true);  
		String strQuery = "   select   rpt.statement_hindi from  audit_report_stmt rpt  "	 
				+" where   rpt.legal_entity_code = '"+legalEntityCode+"'  "
				+" and rpt.audit_id = '"+auditId+"' "
				+" and  rpt.audit_group_code = '"+auditGroupCode+"' "
				+" and  rpt.audit_sub_group_code = '"+auditSubGroupCode+"' "
				+" and  rpt.activity_code  = '"+activityCode+"'   "
				+" and  rpt.process_code = '"+processCode+"' "
				+" and  rpt.finding_code =  '"+findingCode+"'  " 
				+" and  rpt.compliance_category =:isComplied "
				+" and  rpt.applicable_for = '"+BankAuditConstant.FOR_CHECKLIST+"' "
				+" and  rpt.statement_type = '"+BankAuditConstant.FOR_REMARKS_BANK+"' "
				+" and   rpt.statement is not null and  rpt.statement!='' " 
				+" order by cast(rpt.sequence  as UNSIGNED)  ASC " ;
		  
		 
		 Query query=session.createSQLQuery(strQuery) 
				 .setParameter("isComplied", isComplied);	
		 return query.list();
		} catch (Exception e) {
			System.out.println(e);
		}return null;
	}
	
	@Override
	public List<String> getScrutinyRemarksForchecklist(Integer legalEntityCode, String auditId, String auditGroupCode,
			String auditSubGroupCode, String activityCode, String processCode, String findingCode,  String isComplied){
		Session session = getSession();		    
		session.setDefaultReadOnly(true);  
		String strQuery = "   select  rpt.statement from  audit_report_stmt rpt  "	 
				+" where   rpt.legal_entity_code = '"+legalEntityCode+"'  "
				+" and rpt.audit_id = '"+auditId+"' "
				+" and  rpt.audit_group_code = '"+auditGroupCode+"' "
				+" and  rpt.audit_sub_group_code = '"+auditSubGroupCode+"' "
				+" and  rpt.activity_code  = '"+activityCode+"'   "
				+" and  rpt.process_code = '"+processCode+"' "
				+" and  rpt.finding_code =  '"+findingCode+"'  " 
				+" and  rpt.compliance_category =:isComplied "
				+" and  rpt.applicable_for = '"+BankAuditConstant.FOR_CHECKLIST+"' "
				+" and  rpt.statement_type = '"+BankAuditConstant.FOR_REMARKS_SCRUTINY+"' "
				+" and   rpt.statement is not null and  rpt.statement!='' " 
				+" order by cast(rpt.sequence  as UNSIGNED)  ASC " ;
		 
		 Query query=session.createSQLQuery(strQuery) 
				 .setParameter("isComplied", isComplied);	
		 return query.list();
	}
	
	@Override
	public List<String> getScrutinyRemarksForchecklistHindi(Integer legalEntityCode, String auditId, String auditGroupCode,
			String auditSubGroupCode, String activityCode, String processCode, String findingCode,  String isComplied){
		Session session = getSession();		    
		session.setDefaultReadOnly(true);  
		String strQuery = "   select  rpt.statement_hindi from  audit_report_stmt rpt  "	 
				+" where   rpt.legal_entity_code = '"+legalEntityCode+"'  "
				+" and rpt.audit_id = '"+auditId+"' "
				+" and  rpt.audit_group_code = '"+auditGroupCode+"' "
				+" and  rpt.audit_sub_group_code = '"+auditSubGroupCode+"' "
				+" and  rpt.activity_code  = '"+activityCode+"'   "
				+" and  rpt.process_code = '"+processCode+"' "
				+" and  rpt.finding_code =  '"+findingCode+"'  " 
				+" and  rpt.compliance_category =:isComplied "
				+" and  rpt.applicable_for = '"+BankAuditConstant.FOR_CHECKLIST+"' "
				+" and  rpt.statement_type = '"+BankAuditConstant.FOR_REMARKS_SCRUTINY+"' "
				+" and   rpt.statement is not null and  rpt.statement!='' " 
				+" order by cast(rpt.sequence  as UNSIGNED)  ASC " ;
		 
		 Query query=session.createSQLQuery(strQuery) 
				 .setParameter("isComplied", isComplied);	
		 return query.list();
	}
	 
	@Override
	public List<CheckListDto> getChecklistForPartB(Integer legalEntityCode, String auditId, String auditGroupCode, String auditSubGroupCode,
			String activityCode, String processCode, String isComplied ) {
		Session session = getSession();		    
		session.setDefaultReadOnly(true);
		List<String> isCompliedList = Arrays.asList(isComplied.split(","));
		String  sqlQuery = " select rpt.finding_code, rpt.finding_name, rpt.compliance_category  from  audit_report_stmt rpt  "				 
				+" where   rpt.legal_entity_code = '"+legalEntityCode+"'  "
				+" and rpt.audit_id = '"+auditId+"' "
				+" and  rpt.audit_group_code = '"+auditGroupCode+"'"
				+" and  rpt.audit_sub_group_code = '"+auditSubGroupCode+"'"
				+" and  rpt.activity_code = '"+activityCode+"'"
				+" and  rpt.process_code = '"+processCode+"'"
				//+" and  rpt.compliance_category = '"+isComplied+"' "
				+" and  rpt.compliance_category in (:isCompliedList) "
				+" and  rpt.finding_code is not null "
				+" and rpt.applicable_for = '"+BankAuditConstant.FOR_CHECKLIST+"' "
				+" and   rpt.statement_type= '"+BankAuditConstant.FOR_CHECKLIST+"'  "
			  //	+" and (rpt.statement_type  is null or  rpt.statement_type='' ) "
			  //  +" group by  rpt.finding_code, rpt.statement "
				+" order by cast(rpt.sequence  as UNSIGNED)  ASC  ";
	 
		 List<CheckListDto> listCheckListDto = null;
		 Query query=session.createSQLQuery(sqlQuery)
				 .setParameterList("isCompliedList", isCompliedList)
				 .setResultTransformer(new ResultTransformer() {
						
						@Override
						public Object transformTuple(Object[] tuple, String[] aliases) {
							CheckListDto checkListDto=null;
							if(tuple!=null){
								
								checkListDto=new CheckListDto();
								 
							/*	if(tuple[0]!=null){  									
									checkListDto.setChecklist(tuple[0].toString());  
								}*/
								if(tuple[0]!=null){  						 
									checkListDto.setFindingCode(tuple[0].toString());  
								}
								if(tuple[1]!=null){  						 
									checkListDto.setChecklist(tuple[1].toString());  
								}
								if(tuple[2]!=null){  						 
									checkListDto.setIsComplied(tuple[2].toString());  
								}
								 								  
							}
							return checkListDto;
						}
						@Override
						public List transformList(List collection) {
							return collection;
						}
					}); 
		    
		 listCheckListDto = query.list();
		 /*if(!isComplied.equalsIgnoreCase("C"))
		 for (CheckListDto checkListDto : listCheckListDto) {
			System.out.println("======================================"+ checkListDto.getChecklist());
		}*/
		 return listCheckListDto;		 
	}
	
	/*@Override
	public List<String> getDerivedAndStaticStatementForNonCompliance(Integer legalEntityCode, String auditId, String auditGroupCode, String auditSubGroupCode,
			String activityCode, String processCode, String isComplied ) {
		Session session = getSession();		    
		session.setDefaultReadOnly(true); 
		String  sqlQuery = " select rpt.statement  from  audit_report_stmt rpt  "				 
				+" where   rpt.legal_entity_code = '"+legalEntityCode+"'  "
				+" and rpt.audit_id = '"+auditId+"' "
				+" and  rpt.audit_group_code = '"+auditGroupCode+"'"
				+" and  rpt.audit_sub_group_code = '"+auditSubGroupCode+"'"
				+" and  rpt.activity_code = '"+activityCode+"'"
				+" and  rpt.process_code = '"+processCode+"'" 
				+" and  rpt.compliance_category =:isComplied "
				+" and  rpt.finding_code is not null "
				+" and rpt.applicable_for = '"+BankAuditConstant.FOR_CHECKLIST+"' "
				+" and rpt.statement_type  in('"+BankAuditConstant.FOR_DERIVED_STMT+"', '"+BankAuditConstant.FOR_STATIC_STMT+"') "
				+" and  ( rpt.print_flag = 'Yes' OR  rpt.print_flag is null ) "
				+" and   rpt.statement is not null and  rpt.statement!='' " 
				+" order by cast(rpt.sequence  as UNSIGNED)  ASC  ";	 
	 
		 Query query=session.createSQLQuery(sqlQuery).setParameter("isComplied", isComplied);		 
		 return query.list();		 
	}*/
	
	
	/************* To Store all in Master report Table Start ************************/	
	@Override
	public void deleteExistingBeforeInitiate(Integer legalEntityCode,   String auditId, String auditTypeCode){
		 
		 Session session = getSession();	 
		 String deleteBeforeInitiate = " Delete   from     audit_report_stmt    "
					+" where audit_id =:auditId  "
					+" and  audit_type_code=:auditTypeCode"
					+" and  legal_entity_code =:legalEntityCode  ";
		 
		 session.createSQLQuery(deleteBeforeInitiate)
		 .setParameter("auditId", auditId)
		 .setParameter("auditTypeCode", auditTypeCode)
		 .setParameter("legalEntityCode", legalEntityCode)
		 .executeUpdate();
		 
	}
	
	@Override
	public List<ChapterDto> getChapterForReportTable(Integer legalEntityCode,   String auditId, String auditTypeCode) {
		 Session session = getSession();		  	 
		 session.setDefaultReadOnly(true); 
		 
		 String sqlQuery = "select  seq.ag_sequence,  grp.audit_group_code, grp.audit_group_name, audit_group_name_hindi   "
					+" from  activity_process_finding_mapping mapping "
					+" inner join audit_observation  abr on  mapping.mapping_id=abr.mapping_id  "
					+" inner join maint_report_sequence_process seq on  seq.audit_group_code =mapping.audit_group_code  "
					+" Inner join maint_audit_group grp on grp.audit_group_code = seq.audit_group_code "
					+" and grp.status='"+BankAuditConstant.STATUS_ACTIVE+"' and  grp.entity_status='"+BankAuditConstant.STATUS_AUTH+"' "	
					+" and seq.status='"+BankAuditConstant.STATUS_ACTIVE+"' and  seq.entity_status='"+BankAuditConstant.STATUS_AUTH+"' "
					+" and mapping.status='"+BankAuditConstant.STATUS_ACTIVE+"' and  mapping.entity_status='"+BankAuditConstant.STATUS_AUTH+"' "	
					//+" inner join risk_summary_dtls risk on   mapping.audit_type_code = risk.audit_type_code "
					//+" and mapping.audit_group_code=risk.audit_group_code    "
					//+" and abr.audit_type_code=risk.audit_type_code "
					+" and mapping.audit_type_code= seq.audit_type_code  " 
		           // +" and risk.checked='Y' "
		          /*  +" and  (seq.audit_sub_group_code is null or seq.audit_sub_group_code='' ) " 
		            +" and  (seq.activity_code is null or seq.activity_code='' ) " 
		            +" and  (seq.process_code  is null or  seq.process_code='' ) " 
		            +" and  (seq.finding_code  is null or  seq.finding_code='' ) " */
					+" where abr.audit_id =:auditId  "
					+" and abr.audit_type_code=:auditTypeCode"
					+" and mapping.legal_entity_code =:legalEntityCode  "	
					+" and abr.legal_entity_code =:legalEntityCode  "	
					+" and seq.legal_entity_code =:legalEntityCode  "	
					+" and grp.legal_entity_code =:legalEntityCode  "	
					+" group by seq.ag_sequence,  grp.audit_group_code, grp.audit_group_name, audit_group_name_hindi "
					+" order by cast(seq.ag_sequence  as UNSIGNED)  ASC ";
		 		 
		 List<ChapterDto> listChapterDto = null;
		 Query query=session.createSQLQuery(sqlQuery)
					.setResultTransformer(new ResultTransformer() {
						
						@Override
						public Object transformTuple(Object[] tuple, String[] aliases) {
							ChapterDto chapterDto=null;
							if(tuple!=null){								
								chapterDto=new ChapterDto();							 
								if(tuple[0]!=null){                   
									chapterDto.setSequenceNumber(tuple[0].toString()); 
								}
								if(tuple[1]!=null){                   
									chapterDto.setAuditGroupCode(tuple[1].toString()); 
								}
								if(tuple[2]!=null){
									chapterDto.setChapterName(tuple[2].toString());
								}
								if(tuple[3]!=null){
									chapterDto.setChapterNameHindi(tuple[3].toString());
								}
							}
							return chapterDto;
						}
						@Override
						public List transformList(List collection) {
							return collection;
						}
					})
					.setParameter("auditId", auditId)
					.setParameter("auditTypeCode", auditTypeCode)
					.setParameter("legalEntityCode", legalEntityCode);
		    
		 listChapterDto = query.list();
		 return listChapterDto;
	}

	@Override
	public  List<SectionDto>  getSectionForReportTable(Integer legalEntityCode, String auditId, String auditTypeCode, String auditGroupCode) {
		 
		Session session=getSession();
		session.setDefaultReadOnly(true);
		StringBuffer strQuery = new StringBuffer( "select   seq.asg_sequence, sgrp.audit_sub_group_code, sgrp.audit_sub_group_name,  sgrp.audit_sub_group_name_hindi  "
				+" from  activity_process_finding_mapping mapping   "
				+" inner join audit_observation  abr on  mapping.mapping_id=abr.mapping_id    "
				+" inner join maint_report_sequence_process seq on  seq.audit_sub_group_code = mapping.audit_sub_group_code    "
				+" Inner join maint_audit_subgroup  sgrp on sgrp.audit_sub_group_code = mapping.audit_sub_group_code   "
				+" and sgrp.status='"+BankAuditConstant.STATUS_ACTIVE+"' and  sgrp.entity_status='"+BankAuditConstant.STATUS_AUTH+"' "	
				+" and seq.status='"+BankAuditConstant.STATUS_ACTIVE+"' and  seq.entity_status='"+BankAuditConstant.STATUS_AUTH+"' "
				+" and mapping.status='"+BankAuditConstant.STATUS_ACTIVE+"' and  mapping.entity_status='"+BankAuditConstant.STATUS_AUTH+"' "	
				//+" Inner join risk_summary_dtls risk on   mapping.audit_type_code = risk.audit_type_code   "
				//+" and mapping.audit_group_code=risk.audit_group_code    "
		        //+" and mapping.audit_sub_group_code=risk.audit_sub_group_code   "
				//+" and abr.audit_type_code=risk.audit_type_code   "
				+" and mapping.audit_type_code= seq.audit_type_code  "
				+" and mapping.audit_group_code = seq.audit_group_code  " 
				//+" where risk.checked='Y'  " 
	           /* +" and  (seq.activity_code is null or seq.activity_code='' ) " 
	            +" and  (seq.process_code  is null or  seq.process_code='' ) " 
	            +" and  (seq.finding_code  is null or  seq.finding_code='' ) "*/ 
		        +" and abr.audit_id =:auditId   "
		        +" and abr.audit_type_code =:auditTypeCode  "
				+" and mapping.audit_group_code =:auditGroupCode  "
				+" and mapping.legal_entity_code =:legalEntityCode	  "	
				+" and abr.legal_entity_code =:legalEntityCode	  "	
				+" and seq.legal_entity_code =:legalEntityCode	  "	
				+" and sgrp.legal_entity_code =:legalEntityCode	  "	
				+" group by  seq.asg_sequence, sgrp.audit_sub_group_code, sgrp.audit_sub_group_name, sgrp.audit_sub_group_name_hindi   "
				+" order by cast(seq.asg_sequence  as UNSIGNED)  ASC");	
		  		
		Query query=(Query) session.createSQLQuery(strQuery.toString())
				.setResultTransformer(new ResultTransformer() {					
					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						SectionDto sectionDto=null;
						if(tuple!=null){								
							sectionDto=new SectionDto();							 
							if(tuple[0]!=null){                   
								sectionDto.setSequenceNumber(tuple[0].toString()); 
							}
							if(tuple[1]!=null){                   
								sectionDto.setAuditSubGroupCode(tuple[1].toString()); 
							}
							if(tuple[2]!=null){
								sectionDto.setSectionName(tuple[2].toString());
							}
							if(tuple[3]!=null){
								sectionDto.setSectionNameHindi(tuple[3].toString());
							}
						}
						return sectionDto;
					}
					@Override
					public List transformList(List collection) {
						return collection;
					}
				})
				.setParameter("auditId", auditId)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("auditGroupCode", auditGroupCode)
				.setParameter("legalEntityCode", legalEntityCode);
		 
				
		return query.list();
	}
	
	@Override
	public  List<ProductDto>  getProductForReportTable(Integer legalEntityCode, String auditId, String auditTypeCode, String auditGroupCode, 
			String auditSubGroupCode) {
		 
		Session session=getSession();
		session.setDefaultReadOnly(true);
		StringBuffer strQuery = new StringBuffer( "select   seq.act_sequence,  act.activity_code,  act.activity_name, act.activity_name_hindi  "
						+" from  activity_process_finding_mapping mapping   "
						+" inner join audit_observation  abr on  mapping.mapping_id=abr.mapping_id    "
						+" inner join maint_report_sequence_process seq on  seq.activity_code = mapping.activity_code    "
						+" Inner join maint_audit_activity  act on act.activity_code = mapping.activity_code   "
				       // +" Inner join risk_summary_dtls risk on   mapping.audit_type_code = risk.audit_type_code  "
				       // +" and mapping.audit_group_code=risk.audit_group_code    "
				       // +" and mapping.audit_sub_group_code=risk.audit_sub_group_code   "
				       // +" and mapping.activity_code=risk.activity_code   "
					   // +" and abr.audit_type_code=risk.audit_type_code "
					    +" and act.status='"+BankAuditConstant.STATUS_ACTIVE+"' and  act.entity_status='"+BankAuditConstant.STATUS_AUTH+"' "	
						+" and seq.status='"+BankAuditConstant.STATUS_ACTIVE+"' and  seq.entity_status='"+BankAuditConstant.STATUS_AUTH+"' "
						+" and mapping.status='"+BankAuditConstant.STATUS_ACTIVE+"' and  mapping.entity_status='"+BankAuditConstant.STATUS_AUTH+"' "	
						+" and mapping.audit_type_code= seq.audit_type_code  "
						+" and mapping.audit_group_code = seq.audit_group_code  "
						+" and mapping.audit_sub_group_code = seq.audit_sub_group_code  " 
						//+" and act.activity_id = mapping.activity_code  " //Special case
					  //+" where risk.checked='Y'  " 
			           /* +" and  (seq.process_code  is null or  seq.process_code='' ) " 
			            +" and  (seq.finding_code  is null or  seq.finding_code='' ) " */
				        +" and abr.audit_id =:auditId    "
				        +" and abr.audit_type_code =:auditTypeCode  "
						+" and mapping.audit_group_code =:auditGroupCode  "
				        +" and mapping.audit_sub_group_code =:auditSubGroupCode  "
						+" and mapping.legal_entity_code =:legalEntityCode    "
						+" and abr.legal_entity_code =:legalEntityCode    "
						+" and seq.legal_entity_code =:legalEntityCode    "
						+" and act.legal_entity_code =:legalEntityCode    "
						+" group by   seq.act_sequence,   act.activity_code,  act.activity_name, act.activity_name_hindi    "
						+" order by cast(seq.act_sequence  as UNSIGNED)  ASC");	
						  		
		Query query=(Query) session.createSQLQuery(strQuery.toString())
				.setResultTransformer(new ResultTransformer() {					
					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						ProductDto productDto=null;
						if(tuple!=null){								
							productDto=new ProductDto();							 
							if(tuple[0]!=null){                   
								productDto.setSequenceNumber(tuple[0].toString()); 
							}
							if(tuple[1]!=null){                   
								productDto.setActivityCode(tuple[1].toString()); 
							}
							if(tuple[2]!=null){
								productDto.setProductName(tuple[2].toString());
							}	
							if(tuple[3]!=null){
								productDto.setProductNameHindi(tuple[3].toString());
							}	
						}
						return productDto;
					}
					@Override
					public List transformList(List collection) {
						return collection;
					}
				})
				.setParameter("auditId", auditId)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("auditGroupCode", auditGroupCode)
				.setParameter("auditSubGroupCode", auditSubGroupCode)
				.setParameter("legalEntityCode", legalEntityCode);		 
				
		return query.list();
	}
	
	@Override
	public  List<ProcessDto>  getProcessForReportTable(Integer legalEntityCode, String auditId, String auditTypeCode, String auditGroupCode, 
			String auditSubGroupCode, String activityCode) {
		 
		Session session=getSession();
		session.setDefaultReadOnly(true);
		StringBuffer strQuery = new StringBuffer( "select   seq.prc_sequence,   proc.process_code,  proc.process_name, proc.process_name_hindi  "
				+" from  activity_process_finding_mapping mapping    "
				+" inner join audit_observation  abr on  mapping.mapping_id=abr.mapping_id     "
				+" inner join maint_report_sequence_process seq on mapping.process_code   = seq.process_code " //seq.activity_code = mapping.activity_code     "				
				+" Inner join maint_audit_process  proc on proc.process_code = mapping.process_code  "
				+" and proc.status='"+BankAuditConstant.STATUS_ACTIVE+"' and  proc.entity_status='"+BankAuditConstant.STATUS_AUTH+"' "	
				+" and seq.status='"+BankAuditConstant.STATUS_ACTIVE+"' and  seq.entity_status='"+BankAuditConstant.STATUS_AUTH+"' "
				+" and mapping.status='"+BankAuditConstant.STATUS_ACTIVE+"' and  mapping.entity_status='"+BankAuditConstant.STATUS_AUTH+"' "
				+" and mapping.audit_type_code= seq.audit_type_code  "
				+" and mapping.audit_group_code = seq.audit_group_code  "
				+" and mapping.audit_sub_group_code = seq.audit_sub_group_code  "
				+" and mapping.activity_code = seq.activity_code " 
				//+" and mapping.process_code   = seq.process_code    "  
		        // +" and  (seq.finding_code  is null or  seq.finding_code='' ) " 
				+" where  abr.audit_id =:auditId    "
		        +" and abr.audit_type_code =:auditTypeCode  "
				+" and mapping.audit_group_code =:auditGroupCode "
		        +" and mapping.audit_sub_group_code =:auditSubGroupCode  "
		        +" and mapping.activity_code =:activityCode"
				+" and mapping.legal_entity_code =:legalEntityCode    "
				+" and abr.legal_entity_code =:legalEntityCode    "
				+" and seq.legal_entity_code =:legalEntityCode    "
				+" and proc.legal_entity_code =:legalEntityCode   "				
				+" group by   seq.prc_sequence,   proc.process_code,  proc.process_name, proc.process_name_hindi    "
				+" order by cast(seq.prc_sequence  as UNSIGNED)  ASC");	
						  		
		Query query=(Query) session.createSQLQuery(strQuery.toString())
				.setResultTransformer(new ResultTransformer() {					
					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						ProcessDto processDto=null;
						if(tuple!=null){								
							processDto=new ProcessDto();							 
							if(tuple[0]!=null){                   
								processDto.setSequenceNumber(tuple[0].toString()); 
							}
							if(tuple[1]!=null){                   
								processDto.setProcessCode(tuple[1].toString()); 
							}
							if(tuple[2]!=null){
								processDto.setProcessName(tuple[2].toString());
							}
							if(tuple[3]!=null){
								processDto.setProcessNameHindi(tuple[3].toString());
							}
						}
						return processDto;
					}
					@Override
					public List transformList(List collection) {
						return collection;
					}
				})
				.setParameter("auditId", auditId)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("auditGroupCode", auditGroupCode)
				.setParameter("auditSubGroupCode", auditSubGroupCode)
				.setParameter("activityCode", activityCode)
				.setParameter("legalEntityCode", legalEntityCode);		 
				
		return query.list();
	}
	
	@Override
	public  List<CheckListDto>  getChecklistForReportTable(Integer legalEntityCode, String auditId, String auditTypeCode, String auditGroupCode, 
			String auditSubGroupCode, String activityCode, String processCode, String isComplied) {
		try{
			Session session=getSession();
			session.setDefaultReadOnly(true);
			StringBuffer strQuery = new StringBuffer( "");	
							  		
			// need to modified this query
			strQuery.append(" Select "					
					+" find.finding_code, find.finding_name,   abr.compliance_category,   abr.mapping_id , seq.fnd_sequence, find.finding_name_hindi  "
					+" 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 find.finding_code = mapping.finding_code    " 
					+" inner join maint_report_sequence_process seq on seq.finding_code = mapping.finding_code "
					+" and find.status='"+BankAuditConstant.STATUS_ACTIVE+"' and  find.entity_status='"+BankAuditConstant.STATUS_AUTH+"' "	
					+" and seq.status='"+BankAuditConstant.STATUS_ACTIVE+"' and  seq.entity_status='"+BankAuditConstant.STATUS_AUTH+"' "
					+" and mapping.status='"+BankAuditConstant.STATUS_ACTIVE+"' and  mapping.entity_status='"+BankAuditConstant.STATUS_AUTH+"' "					
					+" and mapping.audit_type_code= seq.audit_type_code  "
					+" and mapping.audit_group_code = seq.audit_group_code  "
					+" and mapping.audit_sub_group_code = seq.audit_sub_group_code  "
					+" and mapping.activity_code = seq.activity_code " 
					+" and mapping.process_code = seq.process_code " 
					+" and abr.audit_id =:auditId    "
					+" and abr.audit_type_code =:auditTypeCode  "
					+" and mapping.audit_group_code =:auditGroupCode "
					+" and mapping.audit_sub_group_code =:auditSubGroupCode  "
					+" and mapping.activity_code =:activityCode "
					+" and mapping.process_code =:processCode "
					+" and mapping.legal_entity_code =:legalEntityCode "
					+" and  abr.legal_entity_code =:legalEntityCode "
					+" and  find.legal_entity_code =:legalEntityCode "
					+" and  seq.legal_entity_code =:legalEntityCode "					
					+" Group by   find.finding_code, find.finding_name, abr.compliance_category, abr.mapping_id, seq.fnd_sequence, find.finding_name_hindi    " 
				    +" order by cast(seq.fnd_sequence  as UNSIGNED)  ASC "
					+ " ");
			
 				
			Query query=(Query) session.createSQLQuery(strQuery.toString())
					.setResultTransformer(new ResultTransformer() {					
						@Override
						public Object transformTuple(Object[] tuple, String[] aliases) {
							CheckListDto checkListDto=null;
							if(tuple!=null){								
								checkListDto=new CheckListDto();	
								
								if(tuple[0]!=null && !BankAuditUtil.isEmptyString(tuple[0].toString())){  									
									
									checkListDto.setFindingCode(tuple[0].toString());  
									
									if(tuple[1]!=null){  						 
										checkListDto.setChecklist(tuple[1].toString());  
									} 
									if(tuple[2]!=null){  						 
										checkListDto.setIsComplied(tuple[2]==null || tuple[2].toString().equals("tuple[2]")?"NA":tuple[2].toString());
									} 
									if(tuple[3]!=null){  						 
										checkListDto.setMappingId(tuple[3].toString());  
									}
									if(tuple[5]!=null){  						 
										checkListDto.setChecklistHindi(tuple[5].toString());  
									}
								}														 								  
							}
							return checkListDto; 
						}
						@Override
						public List transformList(List collection) {
							return collection;
						}
					})
					//.setParameter("isComplied",isComplied)
					.setParameter("auditId", auditId)
					.setParameter("auditTypeCode", auditTypeCode)
					.setParameter("auditGroupCode", auditGroupCode)
					.setParameter("auditSubGroupCode", auditSubGroupCode)
					.setParameter("activityCode", activityCode)
					.setParameter("processCode", processCode)
					.setParameter("legalEntityCode", legalEntityCode);		 
			
			List<CheckListDto> checkListDtoLst =query.list();
			
			for (CheckListDto checkListDto2 : checkListDtoLst) {
				if(checkListDto2.getMappingId()==null || checkListDto2.getMappingId().trim().equals("")){
					System.out.println("");
				}
			}
			
			return query.list();
		}catch (Exception e) {
			System.out.println(e +" ================== ");
		}return null;
	}
		 	 	
	@Override
	public List<StaticDerivedStatementDTO> getDerivedAndStaticStatementForChapterForReportTable(Integer legalEntityCode, String auditId,  String auditTypeCode, String auditGroupCode) { 
		Session session = getSession();		    
		session.setDefaultReadOnly(true); 
		 
		String sqlQuery = "select  statement, statement_type, statement_id, statement_hindi from maint_report_sequence_stmt stmt "
		 		+ " where stmt.legal_entity_code ='"+legalEntityCode+"' "
		 		 + " and stmt.audit_type_code ='"+auditTypeCode+"' "
		 		+ " and stmt.audit_group_code = '"+auditGroupCode+"' "
		 		+ " and stmt.applicable_for = '"+BankAuditConstant.FOR_CHAPTER+"' "		
		 		+ " and stmt.status='"+BankAuditConstant.STATUS_ACTIVE+"' and  stmt.entity_status='"+BankAuditConstant.STATUS_AUTH+"' "		
				+ " and (stmt.audit_sub_group_code  IS NULL OR stmt.audit_sub_group_code='') "
				+ " and (stmt.activity_code  IS NULL OR stmt.activity_code ='' )"
				+ " and (stmt.process_code  IS NULL OR stmt.process_code ='' )"
				+ " and (stmt.finding_code  IS NULL OR  stmt.finding_code ='' ) "						 
				+ " order by cast(stmt.sequence as UNSIGNED)  ASC ";
		
		  	 
		 Query query=session.createSQLQuery(sqlQuery).setResultTransformer(new ResultTransformer() {					
				@Override
				public Object transformTuple(Object[] tuple, String[] aliases) {
					StaticDerivedStatementDTO staticDerivedStatement=null;
					if(tuple!=null){								
						staticDerivedStatement=new StaticDerivedStatementDTO();	
						
						if(tuple[0]!=null && !BankAuditUtil.isEmptyString(tuple[0].toString())){  									
							
							staticDerivedStatement.setStatement(tuple[0].toString());  
							
							if(tuple[1]!=null){  						 
								staticDerivedStatement.setStatementType(tuple[1].toString());  
							} 
							if(tuple[2]!=null){  						 
								staticDerivedStatement.setStatementId(tuple[2].toString());
							}
							if(tuple[3]!=null){  						 
								staticDerivedStatement.setStatementHindi(tuple[3].toString()); 
							}
						}														 								  
					}
					return staticDerivedStatement; 
				}
				@Override
				public List transformList(List collection) {
					return collection;
				}
			});		  
		 
		 return query.list();
	}
 
	
	@Override
	public List<StaticDerivedStatementDTO> getDerivedAndStaticStatementForSectionForReportTable(Integer legalEntityCode, String auditId,  String auditTypeCode, String auditGroupCode,
			String auditSubGroupCode) {
		Session session = getSession();		    
		session.setDefaultReadOnly(true); 		 
		 
		
		String sqlQuery = "select statement, statement_type, statement_id, statement_hindi   from maint_report_sequence_stmt stmt " 
		 		 + " where stmt.legal_entity_code ='"+legalEntityCode+"' "
		 		 + " and stmt.audit_type_code ='"+auditTypeCode+"' "
		 		 + " and stmt.audit_group_code = '"+auditGroupCode+"' "
				 + " and stmt.audit_sub_group_code  = '"+auditSubGroupCode+"' "
				 + " and stmt.applicable_for = '"+BankAuditConstant.FOR_SECTION+"' "
				 + " and stmt.status='"+BankAuditConstant.STATUS_ACTIVE+"' and  stmt.entity_status='"+BankAuditConstant.STATUS_AUTH+"' "		
				 + " and (stmt.activity_code  IS NULL OR stmt.activity_code ='') "
				 + " and (stmt.process_code  IS NULL OR stmt.process_code ='') "
				 + " and (stmt.finding_code  IS NULL OR  stmt.finding_code ='') "				 
				 + " order by cast(stmt.sequence as UNSIGNED)  ASC ";
		 
		Query query=session.createSQLQuery(sqlQuery).setResultTransformer(new ResultTransformer() {					
			@Override
			public Object transformTuple(Object[] tuple, String[] aliases) {
				StaticDerivedStatementDTO staticDerivedStatement=null;
				if(tuple!=null){								
					staticDerivedStatement=new StaticDerivedStatementDTO();	
					
					if(tuple[0]!=null && !BankAuditUtil.isEmptyString(tuple[0].toString())){  									
						
						staticDerivedStatement.setStatement(tuple[0].toString());  
						
						if(tuple[1]!=null){  						 
							staticDerivedStatement.setStatementType(tuple[1].toString());  
						} 
						if(tuple[2]!=null){  						 
							staticDerivedStatement.setStatementId(tuple[2].toString());
						} 
						if(tuple[3]!=null){  						 
							staticDerivedStatement.setStatementHindi(tuple[3].toString()); 
						}
					}														 								  
				}
				return staticDerivedStatement; 
			}
			@Override
			public List transformList(List collection) {
				return collection;
			}
		});	
		
		 return query.list();
	}
 
	@Override
	public List<StaticDerivedStatementDTO> getDerivedAndStaticStatementForProductForReportTable(Integer legalEntityCode, String auditId,  String auditTypeCode, String auditGroupCode,
			String auditSubGroupCode, String activityCode) {
		Session session = getSession();		    
		session.setDefaultReadOnly(true); 
		
		List<String> applicableForList = new ArrayList<>(); 
		applicableForList.add(BankAuditConstant.FOR_PRODUCT);
		applicableForList.add(BankAuditConstant.FOR_PROCESS);
		
		 String sqlQuery = "select  statement, statement_type, statement_id, statement_hindi   from maint_report_sequence_stmt stmt  "
		 		 + " where stmt.legal_entity_code ='"+legalEntityCode+"' "
		 		 + " and stmt.audit_type_code ='"+auditTypeCode+"' "
		 		 + " and stmt.audit_group_code = '"+auditGroupCode+"' "
				 + " and stmt.audit_sub_group_code  = '"+auditSubGroupCode+"' "
				 + " and stmt.activity_code    ='"+activityCode+"' "
				 + " and stmt.status='"+BankAuditConstant.STATUS_ACTIVE+"' and  stmt.entity_status='"+BankAuditConstant.STATUS_AUTH+"' "		
				 + " and stmt.applicable_for in (:applicableForList) "
				// + " and stmt.applicable_for = '"+BankAuditConstant.FOR_PRODUCT+"' "		
				// + " and (stmt.process_code  IS NULL OR stmt.process_code ='') "
				// + " and (stmt.finding_code  IS NULL OR  stmt.finding_code ='') "				 
				 + " order by cast(stmt.sequence as UNSIGNED)  ASC ";
		 
		 Query query=session.createSQLQuery(sqlQuery).setResultTransformer(new ResultTransformer() {					
				@Override
				public Object transformTuple(Object[] tuple, String[] aliases) {
					StaticDerivedStatementDTO staticDerivedStatement=null;
					if(tuple!=null){								
						staticDerivedStatement=new StaticDerivedStatementDTO();	
						
						if(tuple[0]!=null && !BankAuditUtil.isEmptyString(tuple[0].toString())){  									
							
							staticDerivedStatement.setStatement(tuple[0].toString());  
							
							if(tuple[1]!=null){  						 
								staticDerivedStatement.setStatementType(tuple[1].toString());  
							} 
							if(tuple[2]!=null){  						 
								staticDerivedStatement.setStatementId(tuple[2].toString());
							}
							if(tuple[3]!=null){  						 
								staticDerivedStatement.setStatementHindi(tuple[3].toString()); 
							}
						}														 								  
					}
					return staticDerivedStatement; 
				}
				@Override
				public List transformList(List collection) {
					return collection;
				}
			}).setParameterList("applicableForList", applicableForList);	
		 
		 return query.list();
	}
 
	@Override
	public List<StaticDerivedStatementDTO> getDerivedAndStaticStatementForProcessForReportTable(Integer legalEntityCode, String auditId,  String auditTypeCode,String auditGroupCode,
			String auditSubGroupCode, String activityCode, String processCode) {
		Session session = getSession();		    
		session.setDefaultReadOnly(true);		 
		  
		String sqlQuery = "select  statement, statement_type, statement_id, statement_hindi    from maint_report_sequence_stmt stmt   "
		 		 + " where stmt.legal_entity_code ='"+legalEntityCode+"' "
		 		 + " and stmt.audit_type_code ='"+auditTypeCode+"' "
		 		 + " and stmt.audit_group_code = '"+auditGroupCode+"' "
				 + " and stmt.audit_sub_group_code  = '"+auditSubGroupCode+"' "
				 + " and stmt.activity_code    ='"+activityCode+"' "
				 + " and stmt.process_code   ='"+processCode+"' "
				 + " and stmt.applicable_for = '"+BankAuditConstant.FOR_PROCESS+"' "	
				 + " and stmt.status='"+BankAuditConstant.STATUS_ACTIVE+"' and  stmt.entity_status='"+BankAuditConstant.STATUS_AUTH+"' "		
				 + " and (stmt.finding_code  IS NULL OR  stmt.finding_code ='') "				 
				 + " order by cast(stmt.sequence as UNSIGNED)  ASC ";
		 
		Query query=session.createSQLQuery(sqlQuery).setResultTransformer(new ResultTransformer() {					
			@Override
			public Object transformTuple(Object[] tuple, String[] aliases) {
				StaticDerivedStatementDTO staticDerivedStatement=null;
				if(tuple!=null){								
					staticDerivedStatement=new StaticDerivedStatementDTO();	
					
					if(tuple[0]!=null && !BankAuditUtil.isEmptyString(tuple[0].toString())){  									
						
						staticDerivedStatement.setStatement(tuple[0].toString());  
						
						if(tuple[1]!=null){  						 
							staticDerivedStatement.setStatementType(tuple[1].toString());  
						} 
						if(tuple[2]!=null){  						 
							staticDerivedStatement.setStatementId(tuple[2].toString());
						} 
						if(tuple[3]!=null){  						 
							staticDerivedStatement.setStatementHindi(tuple[3].toString()); 
						}
					}														 								  
				}
				return staticDerivedStatement; 
			}
			@Override
			public List transformList(List collection) {
				return collection;
			}
		});	
		
		 return query.list();
	}
	
	@Override
	public List<StaticDerivedStatementDTO> getDerivedAndStaticStatementForChecklistForReportTable(Integer legalEntityCode, String auditId,  String auditTypeCode, String auditGroupCode,
			String auditSubGroupCode, String activityCode, String processCode, String findingCode) {
		// TODO Auto-generated method stub
		Session session = getSession();		    
		session.setDefaultReadOnly(true);		 
		
		String sqlQuery = "select  statement, statement_type, statement_id, statement_hindi  from maint_report_sequence_stmt stmt "
		 		+ " where stmt.legal_entity_code ='"+legalEntityCode+"' "
		 	 	 + " and stmt.audit_type_code ='"+auditTypeCode+"' "
		 		 + " and stmt.audit_group_code = '"+auditGroupCode+"' "
				 + " and stmt.audit_sub_group_code  = '"+auditSubGroupCode+"' "
				 + " and stmt.activity_code    ='"+activityCode+"' "
				 + " and stmt.process_code   ='"+processCode+"' "
				 + " and stmt.finding_code    ='"+findingCode+"' "	
				 + " and stmt.applicable_for = '"+BankAuditConstant.FOR_CHECKLIST+"' "	
				 + " and stmt.status='"+BankAuditConstant.STATUS_ACTIVE+"' and  stmt.entity_status='"+BankAuditConstant.STATUS_AUTH+"' "		
				 + " order by cast(stmt.sequence as UNSIGNED)  ASC  ";
		 
		Query query=session.createSQLQuery(sqlQuery).setResultTransformer(new ResultTransformer() {					
			@Override
			public Object transformTuple(Object[] tuple, String[] aliases) {
				StaticDerivedStatementDTO staticDerivedStatement=null;
				if(tuple!=null){								
					staticDerivedStatement=new StaticDerivedStatementDTO();	
					
					if(tuple[0]!=null && !BankAuditUtil.isEmptyString(tuple[0].toString())){  									
						
						staticDerivedStatement.setStatement(tuple[0].toString());  
						
						if(tuple[1]!=null){  						 
							staticDerivedStatement.setStatementType(tuple[1].toString());  
						} 
						if(tuple[2]!=null){  						 
							staticDerivedStatement.setStatementId(tuple[2].toString());
						} 
						if(tuple[3]!=null){  						 
							staticDerivedStatement.setStatementHindi(tuple[3].toString()); 
						}
					}														 								  
				}
				return staticDerivedStatement; 
			}
			@Override
			public List transformList(List collection) {
				return collection;
			}
		});			 
		 return query.list();
	}
	  
    
    @Override
    public List<CheckListDto>  getStandardStatementForReportTable(Integer legalEntityCode, String auditId, String auditTypeCode, String auditGroupCode, 
			String auditSubGroupCode, String activityCode, String processCode, String findingCode){
    	 
    	try{
	    	Session session=getSession();
			session.setDefaultReadOnly(true);
			StringBuffer strQuery = new StringBuffer( "");	
					
			strQuery.append(" Select ( CASE when abr.compliance_category = 'C' then stmt.standard_stmnt_compliance    "
					+ " ELSE (CASE when abr.compliance_category = 'N' then stmt.standard_stmnt_non_compliance ELSE '' END )  END  )  as stndstmt , " 
					+ " abr.compliance_category,"
					+ " ( CASE when abr.compliance_category = 'C' then stmt.standard_stmnt_compliance_hindi    "
					+ " ELSE (CASE when abr.compliance_category = 'N' then stmt.standard_stmnt_non_compliance_hindi ELSE '' END )  END  )  as stndstmtHindi    "
					+" from  activity_process_finding_mapping mapping      "
					+" inner join audit_observation  abr on  mapping.mapping_id = abr.mapping_id         "
					+" and mapping.status='"+BankAuditConstant.STATUS_ACTIVE+"' and  mapping.entity_status='"+BankAuditConstant.STATUS_AUTH+"' "
					+" Inner join maint_audit_finding find on find.finding_code = mapping.finding_code    "
					+" and find.status='"+BankAuditConstant.STATUS_ACTIVE+"' and  find.entity_status='"+BankAuditConstant.STATUS_AUTH+"' "
					+" left join  maint_report_standard_stmt stmt on  mapping.mapping_id = stmt.mapping_id "
					+" and stmt.status='"+BankAuditConstant.STATUS_ACTIVE+"' and  stmt.entity_status='"+BankAuditConstant.STATUS_AUTH+"' "
					+ " where "					
					//+" where   abr.compliance_category in ('C','N') "
					//+" and stds.standard_stmnt_compliance is not null "
					//+" and stds.standard_stmnt_compliance!='' "	
					+"   abr.audit_id =:auditId    "
					+" and abr.audit_type_code =:auditTypeCode  "
					+" and mapping.audit_group_code =:auditGroupCode "
					+" and mapping.audit_sub_group_code =:auditSubGroupCode  "
					+" and mapping.activity_code =:activityCode "
					+" and mapping.process_code =:processCode "
					+" and mapping.legal_entity_code =:legalEntityCode "
					+" and mapping.finding_code =:findingCode"
					+" and "
				//	+ " ( CASE when abr.compliance_category = 'C' then (standard_stmnt_compliance_print=:printFlag OR standard_stmnt_compliance_print is null OR standard_stmnt_compliance_print='' )"
				//	+ " ELSE (CASE when abr.compliance_category = 'N' then (standard_stmnt_non_compliance_print=:printFlag OR standard_stmnt_non_compliance_print is null OR standard_stmnt_non_compliance_print ='' ) ELSE true END )  END  )  "
					+ " ( CASE when abr.compliance_category = 'C' then (standard_stmnt_compliance_print!=:printFlag )"
					+ " ELSE (CASE when abr.compliance_category = 'N' then (standard_stmnt_non_compliance_print!=:printFlag ) ELSE true END )  END  )  "
					+" Group by stndstmt, abr.compliance_category, stndstmtHindi  "
					//+" Order BY stmt.standard_stmnt_compliance "
					+ " ");
						
			Query query=(Query) session.createSQLQuery(strQuery.toString())
					.setResultTransformer(new ResultTransformer() {					
						@Override
						public Object transformTuple(Object[] tuple, String[] aliases) {
							CheckListDto checkListDto=null;
							if(tuple!=null){								
								checkListDto=new CheckListDto();	
								
								//if(tuple[0]!=null && !BankAuditUtil.isEmptyString(tuple[0].toString())){ 
									
									checkListDto.setStandardStatement(tuple[0]==null?"":tuple[0].toString()); 	
									checkListDto.setStandardStatementHindi(tuple[2]==null?"":tuple[2].toString()); 	
									checkListDto.setIsComplied(tuple[1]==null || tuple[1].toString().equals("")?"NA":tuple[1].toString());
									 	
								//}														 								  
							}
							return checkListDto;
						}
						@Override
						public List transformList(List collection) {
							return collection;
						}
					})
					//.setParameter("isComplied",isComplied)
					.setParameter("auditId", auditId)
					.setParameter("auditTypeCode", auditTypeCode)
					.setParameter("auditGroupCode", auditGroupCode)
					.setParameter("auditSubGroupCode", auditSubGroupCode)
					.setParameter("activityCode", activityCode)
					.setParameter("processCode", processCode)
					.setParameter("legalEntityCode", legalEntityCode)
					.setParameter("findingCode", findingCode)
					//.setParameter("printFlag", BankAuditConstant.YES);
					.setParameter("printFlag", BankAuditConstant.NO);
			
			
			List<CheckListDto> listStndStmt = query.list();
			if(!listStndStmt.isEmpty() && listStndStmt.size()>0){
				return listStndStmt;
			}else{
				listStndStmt = new ArrayList<CheckListDto>();
				CheckListDto checkListDto=new CheckListDto();			
				checkListDto.setStandardStatement(null); 	 	
				listStndStmt.add(checkListDto);
				return listStndStmt;
			}
			
			
		}catch (Exception e) {
			System.out.println(e +" ================== ");
		}
	    return null;
    }
    
    @Override
    public  List<CheckListDto> getRemarksForChecklistForReportTable(Integer legalEntityCode, String auditId, String auditGroupCode,
			String auditSubGroupCode, String activityCode, String processCode, String findingCode, String isComplied){
    
    	try{
			Session session=getSession();
			session.setDefaultReadOnly(true);
			StringBuffer strQuery = new StringBuffer( "");	
			 			
			strQuery.append(" Select 		distinct  abr.finding_remarks "	// distinct abr.remarks     //abr.finding_remarks
					+" from  activity_process_finding_mapping mapping      "
					+" inner join audit_observation  abr on  mapping.mapping_id = abr.mapping_id         "
					+" and mapping.status='"+BankAuditConstant.STATUS_ACTIVE+"' and  mapping.entity_status='"+BankAuditConstant.STATUS_AUTH+"' "
					+" Inner join maint_audit_finding find on find.finding_code = mapping.finding_code    "
					+" and find.status='"+BankAuditConstant.STATUS_ACTIVE+"' and  find.entity_status='"+BankAuditConstant.STATUS_AUTH+"' "
					//+" Inner join  maint_report_standard_stmt stmt on  mapping.mapping_id = stmt.mapping_id  "	 
					+" where   "
					//+ " abr.compliance_category =:isComplied "
					+"   abr.audit_id =:auditId    "
					//+" and abr.audit_type_code =:auditTypeCode  "
					+" and mapping.audit_group_code =:auditGroupCode "
					+" and mapping.audit_sub_group_code =:auditSubGroupCode  "
					+" and mapping.activity_code =:activityCode "
					+" and mapping.process_code =:processCode "
					+" and mapping.legal_entity_code =:legalEntityCode   "
					+" and mapping.finding_code =:findingCode   "
				//	+" Group by stndstmt, mapping.finding_code, abr.compliance_category   "
					//+" Order BY stmt.standard_stmnt_compliance "
					+ " ");
			
			Query query=(Query) session.createSQLQuery(strQuery.toString())
					.setResultTransformer(new ResultTransformer() {					
						@Override
						public Object transformTuple(Object[] tuple, String[] aliases) {
							CheckListDto checkListDto=null;
							if(tuple!=null){								
								checkListDto=new CheckListDto();								 
								checkListDto.setRemarks(tuple[0]==null?"":tuple[0].toString()); 	
								checkListDto.setRemarksHindi(tuple[0]==null?"":tuple[0].toString()); 
									
							}
							return checkListDto;
						}
						@Override
						public List transformList(List collection) {
							return collection;
						}
					}).setParameter("auditId", auditId) 
					.setParameter("auditGroupCode", auditGroupCode)
					.setParameter("auditSubGroupCode", auditSubGroupCode)
					.setParameter("activityCode", activityCode)
					.setParameter("processCode", processCode)
					.setParameter("legalEntityCode", legalEntityCode)
					.setParameter("findingCode", findingCode);
			 
			return query.list();
		}catch (Exception e) {
			System.out.println(e +" ================== ");
		}
    	return null;
    }
    
    @Override
   	public void insertIntoReportTable(String reportQuery){			
   		Session session = getSession();			 
   		session.createSQLQuery(reportQuery).executeUpdate();			 
   	}
    
    
    @Override
	public ChapterDto getIntroductionChapterForReportTable(Integer legalEntityCode,   String auditId, String auditTypeCode) {
		 
    	Session session = getSession();	
		session.setDefaultReadOnly(true); 
		 
		 /*String sqlQuery = "select  grp.audit_group_code   "
					+" from  maint_audit_group grp  "
				    +" where  lower(grp.audit_group_name) = :ChapterName "
				    +" and  grp.audit_type_code = :auditTypeCode  ";
				    				    
			Query query=session.createSQLQuery(sqlQuery)
				 .setParameter("ChapterName", BankAuditConstant.INTRODUCTION_CHAPTER)
				 .setParameter("auditTypeCode", auditTypeCode);		    
		 	listChapter = query.list();		 
		 
		 	if(!listChapter.isEmpty() && listChapter!=null){
				String auditGroupCode = listChapter.get(0);	
				return auditGroupCode;
		 	}	
		 	
		 	return BankAuditConstant.FAILED;
		 */
		 
		 String sqlQuery = "select  grp.audit_group_code, audit_group_name, audit_group_name_hindi  "
					+" from  maint_audit_group grp  "
				    +" where  lower(grp.audit_group_name) = :ChapterName "
				    +" and  grp.audit_type_code = :auditTypeCode "
				    +" and  grp.audit_type_code  <> :auditTypeCodeITIS "
				    +" and grp.status='"+BankAuditConstant.STATUS_ACTIVE+"' and  grp.entity_status='"+BankAuditConstant.STATUS_AUTH+"' ";	 
		 
		 Query query=(Query) session.createSQLQuery(sqlQuery.toString())
					.setResultTransformer(new ResultTransformer() {					
						@Override
						public Object transformTuple(Object[] tuple, String[] aliases) {
							ChapterDto chapterDto=null;
							if(tuple!=null){								
								chapterDto=new ChapterDto();	
								
								chapterDto.setAuditGroupCode(tuple[0]==null?"":tuple[0].toString());
								chapterDto.setChapterName(tuple[1]==null?"":tuple[1].toString());	
								chapterDto.setChapterNameHindi(tuple[2]==null?"":tuple[2].toString());	 
									 	
								 														 								  
							}
							return chapterDto;
						}
						@Override
						public List transformList(List collection) {
							return collection;
						}
					}).setParameter("ChapterName", BankAuditConstant.INTRODUCTION_CHAPTER)
					  .setParameter("auditTypeCode", auditTypeCode)
					  .setParameter("auditTypeCodeITIS", BankAuditConstant.INSPECTION_TYPE_ITISI);
		 
		 List<ChapterDto> listChapter = query.list();		 	  
		 if(!listChapter.isEmpty() && listChapter!=null){ 
			return listChapter.get(0);
		 }
		 
		 return null;
	}
	
	/************* To Store all in Master report Table End ************************/	
	
    /**************** TO update Report related data to master table from UI  (from capture/inspection screen) Start **************/
    
    @Override
    public void updateChecklistForReportTable(Integer legalEntityCode, String mappingId, String auditId, String auditTypeCode, String auditGroupCode, 
			String auditSubGroupCode, String activityCode, String processCode, String findingCode,   String isComplied, String reportLanguage, 
			String nonCompliancePersistence, String criticality){
		//try{
		Session session=getSession();
		
		String updateQry="update audit_report_stmt stmt set " 
				+ " compliance_category='"+isComplied+"', "
				+ " non_compliance_persistence='"+nonCompliancePersistence+"',"
				+ " criticality = '"+criticality+"' "
				+" where   " 
				+" stmt.mapping_id =:mappingId    " 
				+" and  stmt.audit_id =:auditId    " 
				/*+" and stmt.audit_group_code =:auditGroupCode "
				+" and stmt.audit_sub_group_code =:auditSubGroupCode  "
				+" and stmt.activity_code =:activityCode "
				+" and stmt.process_code =:processCode "
				+" and stmt.finding_code =:findingCode "*/
				+" and stmt.legal_entity_code =:legalEntityCode   "
				+" and stmt.applicable_for =:applicableFor   "
				//+" and stmt.statement_type =:statementType  "
				+ " ";			 
		session.createSQLQuery(updateQry)
		.setParameter("mappingId",mappingId)
		.setParameter("auditId", auditId) 		 
		/*.setParameter("auditGroupCode", auditGroupCode)
		.setParameter("auditSubGroupCode", auditSubGroupCode)
		.setParameter("activityCode", activityCode)
		.setParameter("processCode", processCode)
		.setParameter("findingCode", findingCode)*/
		.setParameter("legalEntityCode", legalEntityCode)		
		.setParameter("applicableFor", BankAuditConstant.FOR_CHECKLIST)
		//.setParameter("statementType", BankAuditConstant.FOR_CHECKLIST)
		.executeUpdate();
		/*}catch (Exception e) {
			System.out.println(e+" ========================= ");
		}*/			
	}
    
    @Override
    public void updateStandardStatementForReportTable(Integer legalEntityCode, String mappingId,  String auditId, String auditTypeCode, String auditGroupCode, 
			String auditSubGroupCode, String activityCode, String processCode, String findingCode, String isComplied, String statement,
			String statementHindi, String reportLanguage){
		//try{
		Session session=getSession();
		statement = statement==null?"":statement.replace("'", "''");
		StringBuilder updateQry=new StringBuilder("update audit_report_stmt stmt set ");
							
				updateQry.append(" statement='"+statement+"',   ");
				if(BankAuditConstant.REPORT_LANGUAGE_HI.equalsIgnoreCase(reportLanguage)){
					statementHindi = statementHindi==null?"":statementHindi.replace("'", "''");
					updateQry.append(" statement_hindi='"+statementHindi+"',   ");
				}
				 
			updateQry.append( " compliance_category='"+isComplied+"' "
				+" where   " 
				+" stmt.mapping_id =:mappingId    " 
				+" and stmt.audit_id =:auditId    " 
				/*+" and stmt.audit_group_code =:auditGroupCode "
				+" and stmt.audit_sub_group_code =:auditSubGroupCode  "
				+" and stmt.activity_code =:activityCode "
				+" and stmt.process_code =:processCode "
				+" and stmt.finding_code =:findingCode "*/
				+" and stmt.legal_entity_code =:legalEntityCode   "
				+" and stmt.applicable_for =:applicableFor   "
				+" and stmt.statement_type =:statementType   ");			 
		session.createSQLQuery(updateQry.toString())
		.setParameter("mappingId",mappingId)
		.setParameter("auditId", auditId) 
		/*.setParameter("auditGroupCode", auditGroupCode)
		.setParameter("auditSubGroupCode", auditSubGroupCode)
		.setParameter("activityCode", activityCode)
		.setParameter("processCode", processCode)		
		.setParameter("findingCode", findingCode)*/
		.setParameter("legalEntityCode", legalEntityCode)
		.setParameter("applicableFor", BankAuditConstant.FOR_CHECKLIST)
		.setParameter("statementType", BankAuditConstant.FOR_STANDARD_STMT)
		.executeUpdate();
		/*}catch (Exception e) {
			System.out.println(e+" ========================= ");
		}*/	
	}
	
    @Override
	public void updateRemarksForReportTable(Integer legalEntityCode, String mappingId,  String auditId, String auditTypeCode, String auditGroupCode, 
			String auditSubGroupCode, String activityCode, String processCode, String findingCode, String isComplied, String statement, String reportLanguage){
		
		Session session=getSession();
		
		statement = statement==null?"":statement.replace("'", "''"); 
		
		StringBuilder updateQry= new StringBuilder("update audit_report_stmt stmt set ");
		
				if(BankAuditConstant.REPORT_LANGUAGE_EN.equalsIgnoreCase(reportLanguage)){
					updateQry.append(" statement='"+statement+"',   ");
				}else{
					updateQry.append(" statement_hindi='"+statement+"',   ");
				}		  
				
				updateQry.append(" compliance_category='"+isComplied+"' "
				+" where   " 
				+" stmt.mapping_id =:mappingId    " 
				+" and stmt.audit_id =:auditId    " 
				/*+" and stmt.audit_group_code =:auditGroupCode "
				+" and stmt.audit_sub_group_code =:auditSubGroupCode  "
				+" and stmt.activity_code =:activityCode "
				+" and stmt.process_code =:processCode "
				+" and stmt.finding_code =:findingCode "*/
				+" and stmt.legal_entity_code =:legalEntityCode   "
				+" and stmt.applicable_for =:applicableFor   "
				+" and stmt.statement_type =:statementType   ");
				
		session.createSQLQuery(updateQry.toString())
		.setParameter("mappingId",mappingId)
		.setParameter("auditId", auditId) 
		/*.setParameter("auditGroupCode", auditGroupCode)
		.setParameter("auditSubGroupCode", auditSubGroupCode)
		.setParameter("activityCode", activityCode)
		.setParameter("processCode", processCode)
		.setParameter("findingCode", findingCode)*/
		.setParameter("legalEntityCode", legalEntityCode)
		.setParameter("applicableFor", BankAuditConstant.FOR_CHECKLIST)
		.setParameter("statementType", BankAuditConstant.FOR_REMARKS)
		.executeUpdate();	
	}
    
    @Override
	public  void updateBankRemarksForReportTable(Integer legalEntityCode, String mappingId,  String auditId, String auditTypeCode, String auditGroupCode, 
			String auditSubGroupCode, String activityCode, String processCode, String findingCode, String isComplied, String statement, String reportLanguage){
    	
    	Session session=getSession();		
		statement = statement==null?"":statement.replace("'", "''"); 
		
		StringBuilder updateQry= new StringBuilder("update audit_report_stmt stmt set ");
				
				if(BankAuditConstant.REPORT_LANGUAGE_EN.equalsIgnoreCase(reportLanguage)){
					updateQry.append(" statement='"+statement+"',   ");
				}else{
					updateQry.append(" statement_hindi='"+statement+"',   ");
				}
		 
		updateQry.append( " compliance_category='"+isComplied+"' "
				+" where   " 
				+" stmt.mapping_id =:mappingId    " 
				+" and stmt.audit_id =:auditId    " 
				/*+" and stmt.audit_group_code =:auditGroupCode "
				+" and stmt.audit_sub_group_code =:auditSubGroupCode  "
				+" and stmt.activity_code =:activityCode "
				+" and stmt.process_code =:processCode "
				+" and stmt.finding_code =:findingCode "*/
				+" and stmt.legal_entity_code =:legalEntityCode   "
				+" and stmt.applicable_for =:applicableFor   "
				+" and stmt.statement_type =:statementType   ");
		
		session.createSQLQuery(updateQry.toString())
		.setParameter("mappingId",mappingId)
		.setParameter("auditId", auditId)  
		.setParameter("legalEntityCode", legalEntityCode)
		.setParameter("applicableFor", BankAuditConstant.FOR_CHECKLIST)
		.setParameter("statementType", BankAuditConstant.FOR_REMARKS_BANK)
		.executeUpdate();	
    }
	
    @Override
	public void updateScrutinyRemarksForReportTable(Integer legalEntityCode, String mappingId,  String auditId, String auditTypeCode, String auditGroupCode, 
			String auditSubGroupCode, String activityCode, String processCode, String findingCode, String isComplied, String statement, String reportLanguage){
    	
    	Session session=getSession();		
		statement = statement==null?"":statement.replace("'", "''"); 
		
		StringBuilder updateQry= new StringBuilder("update audit_report_stmt stmt set ");
				if(BankAuditConstant.REPORT_LANGUAGE_EN.equalsIgnoreCase(reportLanguage)){
					updateQry.append(" statement='"+statement+"',   ");
				}else{
					updateQry.append(" statement_hindi='"+statement+"',   ");
				}
				updateQry.append( " compliance_category='"+isComplied+"' "
				+" where   " 
				+" stmt.mapping_id =:mappingId    " 
				+" and stmt.audit_id =:auditId    " 
				/*+" and stmt.audit_group_code =:auditGroupCode "
				+" and stmt.audit_sub_group_code =:auditSubGroupCode  "
				+" and stmt.activity_code =:activityCode "
				+" and stmt.process_code =:processCode "
				+" and stmt.finding_code =:findingCode "*/
				+" and stmt.legal_entity_code =:legalEntityCode   "
				+" and stmt.applicable_for =:applicableFor   "
				+" and stmt.statement_type =:statementType   ");			 
		session.createSQLQuery(updateQry.toString())
		.setParameter("mappingId",mappingId)
		.setParameter("auditId", auditId)  
		.setParameter("legalEntityCode", legalEntityCode)
		.setParameter("applicableFor", BankAuditConstant.FOR_CHECKLIST)
		.setParameter("statementType", BankAuditConstant.FOR_REMARKS_SCRUTINY)
		.executeUpdate();
	}
	
	/**************** TO update Report related data to master table from UI  (from capture/inspection screen) End **************/
	
    /**************** get Finalized Report **************/
    @Override
   	public String getFinalizedReport(Integer legalEntityCode, String auditId, String auditTypeCode, String reportCode){
    	Session session = getSession();
		session.setDefaultReadOnly(true);
		
		String queryString="  Select referencePath from "
				+ " AuditLetterReportDocumentDetails doc  "
				+ " where "
				+ " doc.legalEntityCode =:legalEntityCode"					
				+ " and doc.auditId=:auditId "
				+ " and doc.auditTypeCode=:auditTypeCode "
				+ " and doc.letterCode=:reportCode "
				+ " and doc.status=:status ";
		
		
		Query query= session.createQuery(queryString.toString()) 
		 .setParameter("legalEntityCode", legalEntityCode)
		 .setParameter("auditId", auditId)
		 .setParameter("auditTypeCode", auditTypeCode)
		 .setParameter("reportCode", reportCode)
		 .setParameter("status", BankAuditConstant.STATUS_ACTIVE);
		
		return (String) query.uniqueResult() ;
    }
    
    @Override
   	public void updateFinalizedReportStatus(Integer legalEntityCode, String auditId, String auditTypeCode, String reportCode){    	
    	try{
	    	Session session = getSession(); 			
			String queryString=" Update AuditLetterReportDocumentDetails doc "
					+ "  set doc.status=:status " 
					+ " where "
					+ " doc.legalEntityCode =:legalEntityCode"					
					+ " and doc.auditId=:auditId "
					+ " and doc.auditTypeCode=:auditTypeCode "
					+ " and doc.letterCode=:reportCode " ;
			
			 session.createQuery(queryString.toString()) 
				 .setParameter("legalEntityCode", legalEntityCode)
				 .setParameter("auditId", auditId)
				 .setParameter("auditTypeCode", auditTypeCode)
				 .setParameter("reportCode", reportCode) 
				 .setParameter("status", BankAuditConstant.STATUS_DEL)
				 .executeUpdate();
    	}catch (Exception e) { }
    }
    
    
    /**************** For Cover Page Start **************/
	
	/*public AuditReportCoverPage getCoverPage(Integer legalEntityCode, String auditId, String auditTypeCode){
		
		Session session = getSession();		    
		session.setDefaultReadOnly(true);
		 
		 String sqlQuery = "   from AuditReportCoverPage coverPage "
		 		+ " where coverPage.legalEntityCode ='"+legalEntityCode+"' "
		 		 + " and coverPage.auditId ='"+auditId+"' "		 		 
				 + " and coverPage.auditTypeCode   ='"+auditTypeCode+"'   ";
		 
		 Query query=session.createSQLQuery(sqlQuery);		 
		 List<AuditReportCoverPage> listAuditReportCoverPage = query.list();
		 
		 if(listAuditReportCoverPage.isEmpty() || listAuditReportCoverPage.size()==0){
			 return null;
		 }
		 
		 return  listAuditReportCoverPage.get(0);
	}
	*/
	/**************** For Cover Page End **************/
	
    /**************** Generation of Part-C Start **************/
	
	@Override
  	public List<TableDataColumnNumberDto> getTableDataForPartC(Integer legalEntityCode, String auditId, String auditTypeCode){
  		
  		Session session = getSession();		    
  		session.setDefaultReadOnly(true);
  		List<TableDataColumnNumberDto> listTableDataColumnNumberDto = new ArrayList<>();
  		String tableStr= " from AuditReportDynamicTable  "  
  				+ " where legalEntityCode =:legalEntityCode "
  				+ " and auditTypeCode =:auditTypeCode "
  				+ " and auditId =:auditId "
  				+ " and noOfColumns>0 "
  				+ " and (statementType = :statementType OR  statementType IS NUll)"
  				+ " order by sequence asc ";
  		
  		Query table = session.createQuery(tableStr)
  				.setParameter("legalEntityCode", legalEntityCode)
  				.setParameter("auditTypeCode", auditTypeCode)
  				.setParameter("auditId", auditId)
  				.setParameter("statementType", BankAuditConstant.FOR_ANNEXURE);
  		
  		List<AuditReportDynamicTable> listAuditReportDynamicTable = table.list();  
  		Boolean isHeaderPrinted =true;
  		for (AuditReportDynamicTable auditReportDynamicTable : listAuditReportDynamicTable) {
  			
  			TableDataColumnNumberDto tableDataColumnNumberDto =new TableDataColumnNumberDto();
  			List<TableDataDto> listTableDataDto = new ArrayList<>();
  			
  			Integer tableId = auditReportDynamicTable.getId();//auditReportDynamicTable.getTableId();
  			Integer numberOfColumn = auditReportDynamicTable.getNoOfColumns();
  			Integer sequenceNumber = auditReportDynamicTable.getSequence();
  			
  			tableDataColumnNumberDto.setTableId(tableId);
  			tableDataColumnNumberDto.setNumberOfColumn(numberOfColumn);
  			tableDataColumnNumberDto.setSequenceNumber(sequenceNumber);			
  			tableDataColumnNumberDto.setIsHeaderPrinted(isHeaderPrinted);
  			isHeaderPrinted = false;
  			String tableDetailsStr= " from AuditReportDynamicTableDetails   "  
  	  				+ " where legalEntityCode =:legalEntityCode "
  	  				+ " and auditTypeCode =:auditTypeCode "
  	  				+ " and auditId =:auditId "
  	  				+ " and tableId =:tableId "
  	  				+ " order by sequenceRow asc ";
  	  		
  	  		Query tableDetails  = session.createQuery(tableDetailsStr)
  	  				.setParameter("legalEntityCode", legalEntityCode)
  	  				.setParameter("auditTypeCode", auditTypeCode)
  	  				.setParameter("auditId", auditId)
  	  			    .setParameter("tableId", tableId);
  	  		
  	  		List<AuditReportDynamicTableDetails> listAuditReportDynamicTableDetails = tableDetails.list();
  	  		
  	  		for (AuditReportDynamicTableDetails auditReportDynamicTableDetails : listAuditReportDynamicTableDetails) {
  	  			TableDataDto tableDataDto =new TableDataDto();
  	  			
  	  			tableDataDto.setTableName(auditReportDynamicTable.getTableName());
  	  			
  	  			tableDataDto.setHeader1(auditReportDynamicTable.getHeader01());
  	  			tableDataDto.setHeader2(auditReportDynamicTable.getHeader02());
  	  			tableDataDto.setHeader3(auditReportDynamicTable.getHeader03());
  	  			tableDataDto.setHeader4(auditReportDynamicTable.getHeader04());
  	  			tableDataDto.setHeader5(auditReportDynamicTable.getHeader05());
  	  			tableDataDto.setHeader6(auditReportDynamicTable.getHeader06());
  	  			tableDataDto.setHeader7(auditReportDynamicTable.getHeader07());
  	  			tableDataDto.setHeader8(auditReportDynamicTable.getHeader08());
  	  			tableDataDto.setHeader9(auditReportDynamicTable.getHeader09()); 
  	  			
  	  			tableDataDto.setColumn1(auditReportDynamicTableDetails.getContent01());
  	  			tableDataDto.setColumn2(auditReportDynamicTableDetails.getContent02());
  	  			tableDataDto.setColumn3(auditReportDynamicTableDetails.getContent03());
  	  			tableDataDto.setColumn4(auditReportDynamicTableDetails.getContent04());
  	  			tableDataDto.setColumn5(auditReportDynamicTableDetails.getContent05());
  	  			tableDataDto.setColumn6(auditReportDynamicTableDetails.getContent06());
  	  			tableDataDto.setColumn7(auditReportDynamicTableDetails.getContent07());
  	  			tableDataDto.setColumn8(auditReportDynamicTableDetails.getContent08());
  	  			tableDataDto.setColumn9(auditReportDynamicTableDetails.getContent09());
  	  			
  	  			listTableDataDto.add(tableDataDto);
  	  			
			}
  	  		tableDataColumnNumberDto.setTableData(listTableDataDto);
  	  		listTableDataColumnNumberDto.add(tableDataColumnNumberDto);  	  		
		}  		
  		return listTableDataColumnNumberDto;
  		
  	}
	
	@Override
	public List<String> getPartCPdfFiles(Integer legalEntityCode, String auditId, String auditTypeCode, String reportTypePartC){
		Session session = getSession();		    
  		session.setDefaultReadOnly(true);
  		
  		StringBuilder tableStr= new StringBuilder("Select referencePath from AuditLevelDocumentDetails  "  
  				+ " where legalEntityCode =:legalEntityCode "
  				+ " and auditTypeCode =:auditTypeCode "
  				+ " and auditId =:auditId "
  				+ " and documentType =:documentType " );
  		
  		if(reportTypePartC.equalsIgnoreCase(BankAuditConstant.REPORT_TYPE_PART_C_DETAILED)){		
  			tableStr.append(" and ( documentSubType in ('"+BankAuditConstant.REPORT_TYPE_PART_C_BOTH+"',"
  					+ " '"+BankAuditConstant.REPORT_TYPE_PART_C_DETAILED+"') "
  							+ " OR  documentSubType is null ) " );
  		}else if(reportTypePartC.equalsIgnoreCase(BankAuditConstant.REPORT_TYPE_PART_C_ABRIDGED)){
  			tableStr.append(" and ( documentSubType in ('"+BankAuditConstant.REPORT_TYPE_PART_C_BOTH+"',"
  					+ " '"+BankAuditConstant.REPORT_TYPE_PART_C_ABRIDGED+"') "
  							+ " OR  documentSubType is null ) " );
  		} 
  		
  		tableStr.append(" and uploadStatus =:uploadStatus " 
  				+ " order by CAST(seqId  as int) asc ");
  		
  		Query table = session.createQuery(tableStr.toString())
  				.setParameter("legalEntityCode", legalEntityCode)
  				.setParameter("auditTypeCode", auditTypeCode)
  				.setParameter("auditId", auditId)
  				.setParameter("documentType", BankAuditConstant.DOCUMENT_TYPE_IR_PART_C)
  				//.setParameter("documentSubType", reportTypePartC)
  				.setParameter("uploadStatus", BankAuditConstant.SUCCESS);
  		
  		List<String> listPdf = table.list();    
		return listPdf;
	}
	
	/**************** Generation of Part-C End **************/ 
	
	/**************** Get the IR Parameter Values  **************/ 
	 @Override
	 public Map<String, String[]> getParameterVeluesForProfile(Integer legalEntityCode, String auditId, String auditTypeCode){
		 Session session = getSession();		    
		 session.setDefaultReadOnly(true);
		 
		 String irParameterStr= " from IfcEnsureBaasIRProcessData  "  
	  				+ " where legalEntityCode =:legalEntityCode "
	  				+ " and auditTypeCode =:auditTypeCode "
	  				+ " and auditId =:auditId "  ;
		 
		 Query irParameter = session.createQuery(irParameterStr)
	  				.setParameter("legalEntityCode", legalEntityCode)
	  				.setParameter("auditTypeCode", auditTypeCode)
	  				.setParameter("auditId", auditId);
		 
		 List<IfcEnsureBaasIRProcessData> listIfcEnsureBaasIRProcessData = irParameter.list();
		 Map<String, String[]> parameterMap = new HashMap<>();
		 for (IfcEnsureBaasIRProcessData ifcEnsureBaasIRProcessData : listIfcEnsureBaasIRProcessData) {
			 String [] parameterValues =  {ifcEnsureBaasIRProcessData.getSubmittedValueOldest(), ifcEnsureBaasIRProcessData.getSubmittedValueLatest()};
			 parameterMap.put(ifcEnsureBaasIRProcessData.getParameterNameBaas(),parameterValues);
		 }
		 return parameterMap;
	 }	 
	 
	 @Override
	 public Map<String, String> getProfileParameterMap(Integer legalEntityCode, String auditId, String auditTypeCode){
		 Session session = getSession();		    
		 session.setDefaultReadOnly(true);
		 
		String irParameterStr= " from IfcEnsureBaasIRProcessData  "  
	  				+ " where legalEntityCode =:legalEntityCode "
	  				+ " and auditTypeCode =:auditTypeCode "
	  				+ " and auditId =:auditId "  ;
		 
		 Query irParameter = session.createQuery(irParameterStr)
	  				.setParameter("legalEntityCode", legalEntityCode)
	  				.setParameter("auditTypeCode", auditTypeCode)
	  				.setParameter("auditId", auditId);
		 
		 List<IfcEnsureBaasIRProcessData> ifcEnsureBaasDataLst = irParameter.list();
		 
			Map<String, String> profileMap=new HashMap<String, String>();
			 
			//if(auditTypeCode.equalsIgnoreCase(BankAuditConstant.INSPECTION_TYPE_DCCB)){
			if(auditTypeCode.startsWith(BankAuditConstant.INSPECTION_TYPE_DCCB_CONTAIN)){
				for(IfcEnsureBaasIRProcessData ifc:ifcEnsureBaasDataLst) {
					profileMap.put("1"+ifc.getParameterNameBaas(), ifc.getSubmittedValueOldest());
					profileMap.put("3"+ifc.getParameterNameBaas(), ifc.getSubmittedValueLatest());
					profileMap.put("1"+ifc.getParameterNameNabard(), ifc.getSubmittedValueOldest());
					profileMap.put("3"+ifc.getParameterNameNabard(), ifc.getSubmittedValueLatest());
				}
			}else{
				for(IfcEnsureBaasIRProcessData ifc:ifcEnsureBaasDataLst) {
					profileMap.put("2"+ifc.getParameterNameBaas(), ifc.getSubmittedValueOldest());
					profileMap.put("3"+ifc.getParameterNameBaas(), ifc.getSubmittedValueLatest());
					profileMap.put("2"+ifc.getParameterNameNabard(), ifc.getSubmittedValueOldest());
					profileMap.put("3"+ifc.getParameterNameNabard(), ifc.getSubmittedValueLatest());
				}
			}
			return profileMap;		  
	 } 
	 
	 /******************** Start New Code to Get Chapter/Section/Product/process and All Statements *********************/
	 
	 public Session getIRSession(){
		 Session session = getSession(); 
		 String setMaxGroupConcatQry = "SET Session group_concat_max_len = 10240000";
		 session.createNativeQuery(setMaxGroupConcatQry).executeUpdate();
		 return session;
	 }
	 
	 @Override
	 public List<ChapterDto>  getChapter(String legalEntityCode, String auditId, String auditTypeCode) {
					 			 
		StringBuilder strQuery = new StringBuilder(" SELECT audit_group_code, audit_group_name, audit_group_name_hindi "
				+ " FROM audit_report_stmt "
				+ " WHERE  audit_id = '"+auditId+"'  "
				+ " AND audit_type_code = '"+auditTypeCode+"' "
				+ " AND legal_entity_code = '"+legalEntityCode+"'  "
				+ " AND applicable_for = '"+BankAuditConstant.FOR_CHAPTER+"' "
				+ " AND (audit_sub_group_code IS NULL OR audit_sub_group_code='' ) "
				+ " AND (statement_type  IS NULL OR statement_type='')   "
				+ " AND lower(audit_group_name) != '"+BankAuditConstant.INTRODUCTION_CHAPTER+"' ");				
		strQuery.append(" AND audit_group_code IN (Select distinct mapping.audit_group_code from activity_process_finding_mapping mapping "
				+ " where mapping.legal_entity_code =  '"+legalEntityCode+"'  "
				+ " and mapping.audit_type_code = '"+auditTypeCode+"' "
				+ " AND mapping.status='"+BankAuditConstant.STATUS_ACTIVE+"' AND  mapping.entity_status='"+BankAuditConstant.STATUS_ACTIVE+"')  ");		
		strQuery.append( " ORDER BY CAST(sequence  as UNSIGNED)  ASC   ") ; 		
		
		
		
		Session session = getSession();		    
		session.setDefaultReadOnly(true);
		
		List<ChapterDto> listChapterDto = null;
		Query query=session.createNativeQuery(strQuery.toString())
					.setResultTransformer(new ResultTransformer() {
						
						@Override
						public Object transformTuple(Object[] tuple, String[] aliases) {
							ChapterDto chapterDto=null;
							if(tuple!=null){								
								chapterDto=new ChapterDto(); 
								if(tuple[0]!=null){                   
									chapterDto.setAuditGroupCode(tuple[0].toString()); 
								}
								if(tuple[1]!=null){
									chapterDto.setChapterName(tuple[1].toString());
								}
								if(tuple[2]!=null){
									chapterDto.setChapterNameHindi(tuple[2].toString());
								}							  
							}
							return chapterDto;
						}
						@Override
						public List transformList(List collection) {
							return collection;
						}
					});
		    
		 listChapterDto = query.list();
		 return listChapterDto;
	 }

	 @Override
	 public Map<String, String> getSection(String legalEntityCode, String auditId, String auditTypeCode) {			
			 			  
		String strQuery = " SELECT audit_group_code, " 
				+ " GROUP_CONCAT(DISTINCT sequence,'#::#',audit_sub_group_code,'#::#',audit_sub_group_name,'#::#',IFNULL(audit_sub_group_name_hindi, ' ') SEPARATOR '#@::@#') AS section "
				+ " FROM  audit_report_stmt rpt"
				+ " WHERE  audit_id = '"+auditId+"' "
				+ " AND audit_type_code = '"+auditTypeCode+"' "
				+ " AND legal_entity_code = '"+legalEntityCode+"'  "
				+ " AND applicable_for = '"+BankAuditConstant.FOR_SECTION+"' "
				+ " AND (statement_type   IS NULL OR   statement_type='') "
				+ " AND audit_sub_group_code IS NOT NULL  "
				+ " GROUP BY  audit_group_code";
		
		Session session = getSession();		    
		session.setDefaultReadOnly(true);
		Query query=session.createNativeQuery(strQuery);
		List<Object[]> sectionList = query.list();
		
		Map<String, String> section = new HashMap<>();
		for (Object[] objects : sectionList) {
			section.put(objects[0].toString(), objects[1].toString());
		}
			
			/*List<SectionDto> listSectionDto = null;
			Query query=session.createSQLQuery(strQuery)
						.setResultTransformer(new ResultTransformer() {
							
							@Override
							public Object transformTuple(Object[] tuple, String[] aliases) {
								SectionDto sectionDto=null;
								if(tuple!=null){								
									sectionDto=new SectionDto(); 
									if(tuple[0]!=null){                   
										sectionDto.setAuditSubGroupCode(tuple[0].toString());
									}
									if(tuple[1]!=null){
										sectionDto.setSectionName(tuple[1].toString());
									}								  
								}
								return sectionDto;
							}
							@Override
							public List transformList(List collection) {
								return collection;
							}
						});
			    
			listSectionDto = query.list();			
			Map<String, SectionDto> section = new HashMap<>();
			section = listSectionDto.stream().collect(Collectors.toMap(SectionDto::getAuditSubGroupCode, Function.identity()));*/ 

		return section;
	 }

	 @Override
	 public Map<String, String> getProduct(String legalEntityCode, String auditId, String auditTypeCode) {
			
		String strQuery = "SELECT audit_group_code, audit_sub_group_code,  "
				+ " GROUP_CONCAT( DISTINCT sequence,'#::#',activity_code,'#::#',activity_name,'#::#',IFNULL(activity_name_hindi, ' ') SEPARATOR '#@::@#') AS product "
				+ " FROM  audit_report_stmt rpt "
				+ " WHERE  audit_id = '"+auditId+"' "
				+ " AND audit_type_code = '"+auditTypeCode+"' "
				+ " AND legal_entity_code = '"+legalEntityCode+"'  "
				+ " AND applicable_for = '"+BankAuditConstant.FOR_PRODUCT+"' "					
				+ " AND (statement_type   IS NULL OR   statement_type='')"
				+ " AND activity_code IS NOT NULL"
				+ " GROUP BY  audit_group_code, audit_sub_group_code "; 
		 
		Session session = getSession();		    
		session.setDefaultReadOnly(true);
		Query query=session.createNativeQuery(strQuery);
		List<Object[]> productList = query.list();
		
		Map<String, String> product = new HashMap<>();
		for (Object[] objects : productList) {
			product.put(objects[0].toString()+objects[1].toString(), objects[2].toString());
		}
			
			/*List<ProductDto> listProductDto = null;
			Query query=session.createSQLQuery(strQuery)
						.setResultTransformer(new ResultTransformer() {
							
							@Override
							public Object transformTuple(Object[] tuple, String[] aliases) {
								ProductDto productDto=null;
								if(tuple!=null){
									
									productDto=new ProductDto(); 							
									if(tuple[0]!=null){                   
										productDto.setActivityCode(tuple[0].toString()+tuple[1].toString());
									}
									if(tuple[2]!=null){
										productDto.setProductName(tuple[2].toString());
									}								  
								}
								return productDto;
							}
							@Override
							public List transformList(List collection) {
								return collection;
							}
						});
			    
			listProductDto = query.list();
			 
			Map<String, ProductDto> product = new HashMap<>();
			product = listProductDto.stream().collect(Collectors.toMap(ProductDto::getActivityCode, Function.identity())); */
		
		return product;
	}

	 @Override
	 public Map<String, String> getProcess(String legalEntityCode, String auditId, String auditTypeCode) {
			 
		String strQuery = " SELECT audit_group_code, audit_sub_group_code, activity_code,   "
					+ " GROUP_CONCAT( DISTINCT sequence,'#::#',process_code,'#::#',process_name,'#::#',IFNULL(process_name_hindi, ' ') SEPARATOR '#@::@#') as process "
					+ " FROM  audit_report_stmt rpt "
					+ " WHERE  audit_id = '"+auditId+"' "
					+ " AND audit_type_code = '"+auditTypeCode+"' "
					+ " AND legal_entity_code = '"+legalEntityCode+"'  "
					+ " AND applicable_for = '"+BankAuditConstant.FOR_PROCESS+"' "	 
					+ " AND (statement_type   IS NULL OR   statement_type='') "
					+ " AND process_code IS NOT NULL "
					+ " GROUP BY  audit_group_code, audit_sub_group_code, activity_code ";
			
		Session session = getSession();		    
		session.setDefaultReadOnly(true);			
		Query query=session.createNativeQuery(strQuery);
		List<Object[]> processList = query.list();
		
		Map<String, String> process = new HashMap<>();
		for (Object[] objects : processList) {
			process.put(objects[0].toString()+objects[1].toString()+objects[2].toString(), objects[3].toString());
		}
			
			/*List<ProcessDto> listProcessDto = null;
			Query query=session.createSQLQuery(sqlQuery)
						.setResultTransformer(new ResultTransformer() {
							
							@Override
							public Object transformTuple(Object[] tuple, String[] aliases) {
								ProcessDto processDto=null;
								if(tuple!=null){								
									processDto=new ProcessDto(); 
									if(tuple[0]!=null){ 
										processDto.setProcessCode(tuple[0].toString()+tuple[1].toString()+tuple[2].toString()); 
									}
									if(tuple[3]!=null){                   
										processDto.setProcessName(tuple[3].toString());
									}								 								  
								}
								return processDto;
							}
							@Override
							public List transformList(List collection) {
								return collection;
							}
						});
			    
			listProcessDto = query.list();	 
			 
			Map<String, ProcessDto> process = new HashMap<>();
			process = listProcessDto.stream().collect(Collectors.toMap(ProcessDto::getProcessCode, Function.identity())); */ 

		return process;
	}

	 @Override
	 public  Map<String, String> getChecklist(String legalEntityCode, String auditId, String auditTypeCode, String isComplied) {
			
		 List<String> isCompliedList = Arrays.asList(isComplied.split(","));			 
		 String strQuery = "SELECT audit_group_code, audit_sub_group_code, activity_code, process_code,   "
						+ " GROUP_CONCAT( DISTINCT sequence,'#::#',finding_code,'#::#',finding_name,'#::#',compliance_category, "
						+ " '#::#',IFNULL(finding_name_hindi, ' ') SEPARATOR '#@::@#') as checklist "
						+ " FROM  audit_report_stmt rpt  "
						+ " WHERE  audit_id = '"+auditId+"' "
						+ " AND audit_type_code = '"+auditTypeCode+"' "
						+ " AND legal_entity_code = '"+legalEntityCode+"'  "
						+ " AND applicable_for = '"+BankAuditConstant.FOR_CHECKLIST+"' "							
						+ " AND statement_type = '"+BankAuditConstant.FOR_CHECKLIST+"' "
						+ " AND compliance_category IN (:isCompliedList)  "	  
						+ " AND finding_code IS NOT NULL  "
						+ " GROUP BY  audit_group_code, audit_sub_group_code, activity_code, process_code ";	 
			
		Session session = getSession();		    
		session.setDefaultReadOnly(true);
		Query query=session.createNativeQuery(strQuery)
				.setParameterList("isCompliedList", isCompliedList);
		List<Object[]> checksListList = query.list();
		
		Map<String, String> checklist = new HashMap<>();
		for (Object[] objects : checksListList) {
			checklist.put(objects[0].toString()+objects[1].toString()+objects[2].toString()+objects[3].toString(), objects[4].toString());
		}
			
			/*List<CheckListDto> listCheckListDto = null;
			Query query=session.createSQLQuery(sqlQuery)
					 .setParameterList("isCompliedList", isCompliedList)
					 .setResultTransformer(new ResultTransformer() {
							
							@Override
							public Object transformTuple(Object[] tuple, String[] aliases) {
								CheckListDto checkListDto=null;
								if(tuple!=null){
									
									checkListDto=new CheckListDto(); 
									if(tuple[0]!=null){  						 
										checkListDto.setFindingCode(tuple[0].toString()+tuple[1].toString()+tuple[2].toString()+tuple[3].toString());  
									}
									if(tuple[4]!=null){  						 
										checkListDto.setChecklist(tuple[4].toString());  
									} 
									 								  
								}
								return checkListDto;
							}
							@Override
							public List transformList(List collection) {
								return collection;
							}
						}); 
			    
			listCheckListDto = query.list();
			
			Map<String, CheckListDto> checklist = new HashMap<>();
			checklist = listCheckListDto.stream().collect(Collectors.toMap(CheckListDto::getFindingCode, Function.identity()));*/ 		 

		return checklist;
	 }

	 @Override
	 public  Map<String, String> getStandardStatement(String legalEntityCode, String auditId, String auditTypeCode, String isComplied) { 
 			
		 List<String> isCompliedList = Arrays.asList(isComplied.split(","));	
		 String strQuery = "SELECT audit_group_code, audit_sub_group_code, activity_code, process_code, finding_code, statement, statement_hindi, "
		 				+ " criticality "
						+ " FROM  audit_report_stmt rpt "
						+ " WHERE  audit_id = '"+auditId+"' "
						+ " AND audit_type_code = '"+auditTypeCode+"' "
						+ " AND legal_entity_code = '"+legalEntityCode+"'  "
						+ " AND applicable_for = '"+BankAuditConstant.FOR_CHECKLIST+"' "							
						+ " AND statement_type = '"+BankAuditConstant.FOR_STANDARD_STMT+"' "
						+ " AND compliance_category IN (:isCompliedList) "
						+ " AND finding_code IS NOT NULL"
						+ " AND statement IS  NOT NULL AND statement!='' AND LOWER(statement)!='null' " ; 
			 
		Session session = getSession();		    
		session.setDefaultReadOnly(true);
		Query query=session.createNativeQuery(strQuery)
				.setParameterList("isCompliedList", isCompliedList);
		List<Object[]> checksListList = query.list();
		
		Map<String, String> stndstmt = new HashMap<>();
		for (Object[] objects : checksListList) {
			String stmt = (objects[5]==null?" ":objects[5].toString()+" ")+"#::#"+(objects[6]==null?" ":objects[6].toString()+" ")
					+"#::#"+(objects[7]==null?" ":objects[7].toString()+" ");
			stndstmt.put(objects[0].toString()+objects[1].toString()+objects[2].toString()+objects[3].toString()+objects[4].toString(), stmt);
		}		 

		return stndstmt;
	}
		 
	 @Override
	 public  Map<String, String> getRemarks(String legalEntityCode, String auditId, String auditTypeCode, String isComplied, String remarksFor) {
		 List<String> isCompliedList = Arrays.asList(isComplied.split(","));	
		 String strQuery = "SELECT audit_group_code, audit_sub_group_code, activity_code, process_code, finding_code, statement, statement_hindi,"
		 				+ " criticality "
						+ " FROM  audit_report_stmt rpt "
						+ " WHERE  audit_id = '"+auditId+"' "
						+ " AND audit_type_code = '"+auditTypeCode+"' "
						+ " AND legal_entity_code = '"+legalEntityCode+"'  "
						+ " AND applicable_for = '"+BankAuditConstant.FOR_CHECKLIST+"' "							
						+ " AND statement_type = '"+remarksFor+"' "
						+ " AND compliance_category IN (:isCompliedList) "
						+ " AND finding_code IS NOT NULL"
						+ " AND ("
								+ " (statement IS  NOT NULL AND statement!='' AND LOWER(statement)!='null' ) "						
								+ " OR (statement_hindi IS  NOT NULL AND statement_hindi!='' AND LOWER(statement_hindi)!='null') "
						+ " ) " ; 
 			
		Session session = getSession();		    
		session.setDefaultReadOnly(true);
		Query query=session.createNativeQuery(strQuery)
				.setParameterList("isCompliedList", isCompliedList);
		List<Object[]> checksListList = query.list();
		
		Map<String, String> remark = new HashMap<>();
		for (Object[] objects : checksListList) {
			String remarkStmt = (objects[5]==null?" ":objects[5].toString()+" ")+"#::#"+(objects[6]==null?" ":objects[6].toString()+" ")
					+"#::#"+(objects[7]==null?" ":objects[7].toString()+" ");
			remark.put(objects[0].toString()+objects[1].toString()+objects[2].toString()+objects[3].toString()+objects[4].toString(), remarkStmt);
		}			 

		return remark;
	}
	 
	 @Override
	 public  Map<String, String> getRemarksClosure(String legalEntityCode, String auditId, String auditTypeCode, String isComplied, String remarksFor, String status) {
		 List<String> isCompliedList = Arrays.asList(isComplied.split(","));
		 		  
		 StringBuilder strQuery = new StringBuilder();
		 logger.info("Inside getRemarksClosure .. "+auditId +" :: "+remarksFor);
		 		
		 /*strQuery.append(" Select * from  ( ");
		 if(remarksFor.equalsIgnoreCase(BankAuditConstant.FOR_REMARKS_SCRUTINY)){
			 strQuery.append("  ( SELECT rpt.audit_group_code, rpt.audit_sub_group_code, rpt.activity_code, "
					 		+ " rpt.process_code, rpt.finding_code,  statement, "
					 		+ " statement_hindi,  rpt.id, rpt.mapping_id ,  NOW() , '' "
					 		+ " FROM audit_report_stmt rpt  "
					 		+ " INNER JOIN audit_observation obs "
								+ " ON   obs.audit_id = rpt.audit_id    "
								+ " AND obs.finding_id  = rpt.finding_code "
								+ " AND obs.mapping_id = rpt.mapping_id   "
							+ " INNER JOIN audit_schedule sch "
								+ "  ON sch.audit_id = obs.audit_id   "
								+ "  AND sch.audit_type_code = obs.audit_type_code "   
							+ "  WHERE  rpt.audit_id = '"+auditId+"'  "
								+ "  AND rpt.audit_type_code = '"+auditTypeCode+"' "
								+ "  AND rpt.legal_entity_code = '"+legalEntityCode+"'  "
								+ "  AND rpt.applicable_for =  '"+BankAuditConstant.FOR_CHECKLIST+"'  "			
								+ "  AND rpt.statement_type = '"+remarksFor+"'  "
								+ "  AND rpt.compliance_category IN (:isCompliedList) " 
								
								+ "  AND ( CASE WHEN rpt.statement_type = '"+BankAuditConstant.FOR_REMARKS_SCRUTINY+"' "  
								+ " 	THEN sch.review_status IN ('WS', 'WI') "
								+ " 		AND obs.review_status = '"+BankAuditConstant.STATUS_AUTH+"' "
								+ " 	WHEN rpt.statement_type = '"+BankAuditConstant.FOR_REMARKS_BANK+"'  "
								+ " 	THEN sch.respond_status IN ('RS') "
								+ " 		AND obs.response_status = '"+BankAuditConstant.STATUS_AUTH+"'  "
								+ " 	ELSE FALSE END ) ) ");
			 strQuery.append(" UNION ALL (");
		 }*/
		 strQuery.append( " "
		 				+ " SELECT DISTINCT rpt.audit_group_code, rpt.audit_sub_group_code, rpt.activity_code, "
		 				+ " rpt.process_code, rpt.finding_code, remarkHis.comment as engComm,  "
				 		+ " remarkHis.comment as hindiComm, remarkHis.id, remarkHis.observation_id , "
				 		+ " remarkHis.maker_timestamp, review_respond_status, remarkHis.capture_type,  remarkHis.status "
				 		+ " FROM  audit_observation_respond_review_capture remarkHis      "
				 		+ " INNER JOIN  audit_observation obs  "
		                + "  ON obs.audit_id = remarkHis.audit_id   "
		                + "  AND obs.observation_id = remarkHis.observation_id  "
		                + "  AND obs.group_observation_id = remarkHis.group_observation_id  "
		                + " INNER JOIN audit_report_stmt  rpt   "
		                + "  ON rpt.finding_code = obs.finding_id  "
		                + "  AND rpt.mapping_id = obs.mapping_id "
		                + "  AND rpt.audit_id  = obs.audit_id  "
						+ " WHERE  rpt.audit_id = '"+auditId+"' "
						+ " AND rpt.audit_type_code = '"+auditTypeCode+"' "
						+ " AND rpt.legal_entity_code = '"+legalEntityCode+"'  "
						+ " AND rpt.applicable_for = '"+BankAuditConstant.FOR_CHECKLIST+"' ");		 
						if(BankAuditUtil.isEmptyString(remarksFor)){
							strQuery.append( " AND rpt.statement_type = '"+BankAuditConstant.FOR_CHECKLIST+"'   ");// condition to get only one combination of group, subgroup, prod, act, find
						}else{
							strQuery.append( " AND rpt.statement_type = '"+remarksFor+"' ");
						}
						if(BankAuditConstant.STATUS_AUTH.equalsIgnoreCase(status)){
							strQuery.append( " AND remarkHis.status = :status ");
						}
		 				strQuery.append( " AND remarkHis.capture_type IN (:roles)"
		 						+ " AND rpt.compliance_category IN (:isCompliedList) "
						+ " AND rpt.finding_code IS NOT NULL "						
						//+ " AND (remarkHis.status IS NULL OR  remarkHis.status = '"+BankAuditConstant.STATUS_AUTH+"' ) "
						+ " ORDER BY remarkHis.id  ASC " );
		//strQuery.append( " )) as tmp")  ; 
 	 	 
		Session session = getSession();		    
		session.setDefaultReadOnly(true);
		Query query=session.createNativeQuery(strQuery.toString())
				.setParameterList("isCompliedList", isCompliedList);
		if(BankAuditUtil.isEmptyString(remarksFor)){
			query.setParameterList("roles", Arrays.asList(("G4,G7,G3,G31").split(",")));
		}else if(BankAuditConstant.FOR_REMARKS_SCRUTINY.equalsIgnoreCase(remarksFor)){
			query.setParameterList("roles", Arrays.asList(("G4,G7").split(",")));
		}else if(BankAuditConstant.FOR_REMARKS_BANK.equalsIgnoreCase(remarksFor)){
			query.setParameterList("roles", Arrays.asList(("G3,G31").split(",")));
		}
		if(BankAuditConstant.STATUS_AUTH.equalsIgnoreCase(status)){
			query.setParameter("status", status);
		}
		
		List<Object[]> checksListList = query.list();		
		Map<String, String> remarkMap = new HashMap<>();
		try {
			for (Object[] objects : checksListList) {  
				
				String key = objects[0].toString()+objects[1].toString()+objects[2].toString()+objects[3].toString()+objects[4].toString();			 
				String reviewRespondStatus = objects[10]==null?" ":objects[10].toString()+" ";	
				String role = objects[11]==null?" ":objects[11].toString()+" ";			 
				String statusResult = objects[12]==null?" ":objects[12].toString()+" ";	
				String remarkStmt = (objects[5]==null?" ": objects[5].toString()+" ")+"#::#"
							+(objects[6]==null?" ": objects[6].toString()+" ")+"#::#"
							+(reviewRespondStatus)+"#::#"+role+"#::#"+statusResult;
				
				if(remarkMap.containsKey(key)){
					remarkStmt = remarkMap.get(key) +"#@::@#"+ remarkStmt ; 
				}			
				if(remarkStmt!=null){
					remarkMap.put(objects[0].toString()+objects[1].toString()+objects[2].toString()+objects[3].toString()+objects[4].toString(), remarkStmt);
				}
			}			 
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		return remarkMap;
	} 
 
	@Override
	public Map<String, String> getDerivedStaticStatementAll(String legalEntityCode, String auditId,  String auditTypeCode) {
		 		 
		String strQuery = " SELECT audit_group_code, audit_sub_group_code, activity_code, process_code, finding_code, "
				+ " GROUP_CONCAT(DISTINCT sequence,'#::#', statement,'#::#', IFNULL(statement_hindi,' ')  SEPARATOR '#@::@#') "
				+ " FROM  audit_report_stmt rpt "
				+ " WHERE  audit_id = '"+auditId+"' "
				+ " AND audit_type_code = '"+auditTypeCode+"' "
				//+ " AND applicable_for = '"+BankAuditConstant.FOR_CHAPTER+"' "	 						
				+"  AND statement_type  in('"+BankAuditConstant.FOR_DERIVED_STMT+"', '"+BankAuditConstant.FOR_STATIC_STMT+"') "			
				//+ " AND compliance_category IN (:isCompliedList) "				 
				+ " AND statement IS  NOT NULL AND statement!='' AND LOWER(statement)!='null' " 
				+ " AND ( print_flag = 'Yes' OR  print_flag is null ) "
				+ " GROUP BY audit_group_code, audit_sub_group_code, activity_code, process_code, finding_code "	;			
		
		Session session = getSession();		    
		session.setDefaultReadOnly(true);
		Query query=session.createNativeQuery(strQuery);
		List<Object[]> stmtDerivedStaticList = query.list();
		
		Map<String, String> stmtDerivedStatic = new HashMap<>();
		for (Object[] stmt : stmtDerivedStaticList) {
			String chapter = (stmt[0]==null || stmt[0].toString().equalsIgnoreCase("null") || stmt[0].toString().equals(""))?"":stmt[0].toString();
			String section = (stmt[1]==null || stmt[1].toString().equalsIgnoreCase("null") || stmt[1].toString().equals(""))?"":stmt[1].toString();
			String product = (stmt[2]==null || stmt[2].toString().equalsIgnoreCase("null") || stmt[2].toString().equals(""))?"":stmt[2].toString();
			String process = (stmt[3]==null || stmt[3].toString().equalsIgnoreCase("null") || stmt[3].toString().equals(""))?"":stmt[3].toString();
			String finding = (stmt[4]==null || stmt[4].toString().equalsIgnoreCase("null") || stmt[4].toString().equals(""))?"":stmt[4].toString();
			stmtDerivedStatic.put(chapter+section+product+process+finding, stmt[5].toString());
		}			 

		return stmtDerivedStatic;
	} 	 
	 
	@Override
	public Map<String, String> getDerivedAndStaticStatementForIntroduction(String legalEntityCode,  String auditId,  String auditTypeCode){		
		List<Object[]> intro = new ArrayList<>();
		Session session = getSession();		    
		session.setDefaultReadOnly(true);
		 
		 String sqlQuery = " SELECT audit_group_name, audit_group_name_hindi, "
		 		+ " GROUP_CONCAT(DISTINCT sequence,'#::#', statement,'#::#', IFNULL(statement_hindi,' ')  SEPARATOR '#@::@#')"
			 	+" FROM  audit_report_stmt rpt  "				 
				+" WHERE rpt.legal_entity_code = '"+legalEntityCode+"'  "
				+" AND rpt.audit_id = '"+auditId+"' "
				+" AND rpt.audit_type_code ='"+auditTypeCode+"' "  
				+" AND rpt.applicable_for = '"+BankAuditConstant.FOR_CHAPTER+"' "
				+" AND rpt.statement_type  in('"+BankAuditConstant.FOR_DERIVED_STMT+"', '"+BankAuditConstant.FOR_STATIC_STMT+"') "
				+" AND lower(rpt.audit_group_name) = '"+BankAuditConstant.INTRODUCTION_CHAPTER+"' " 
				+" AND statement IS  NOT NULL AND statement!='' AND LOWER(statement)!='null' " 
				+" AND ( rpt.print_flag = 'Yes' OR  rpt.print_flag is null ) "
				+" GROUP BY audit_group_name, audit_group_name_hindi " ;		 
		 Query query=session.createSQLQuery(sqlQuery);		 
		 intro = query.list();	
		 
		 Map<String, String> stmtDerivedStatic = new HashMap<>();
			for (Object[] stmt : intro) {
				String chapter = (stmt[0]==null || stmt[0].toString().equalsIgnoreCase("null") || stmt[0].toString().equals(""))?"":stmt[0].toString();	
				String chapterHindi = (stmt[1]==null || stmt[1].toString().equalsIgnoreCase("null") || stmt[1].toString().equals(""))?"":stmt[1].toString();	
				stmtDerivedStatic.put(chapter+"#::#"+chapterHindi, stmt[2].toString());
			}	
			
		return stmtDerivedStatic; 
	}
	
	@Override
	public  List<CheckListDto> getAllMajorChecklist(String legalEntityCode, String auditId, String auditTypeCode, String isComplied, String remarksFor) {
		 List<String> isCompliedList = Arrays.asList(isComplied.split(","));	
		 List<String> remarksForList = Arrays.asList(remarksFor.split(","));	 
		 String strQuery = "  Select mapping_id , "
				  	  + "  group_concat(ifnull(statement_type,''), '#::#' , ifnull(statement,'' ), '#::#ep' order by statement_type ASC separator '#@::@#' ) "
					  + "  FROM (Select mapping_id, statement_type, statement from audit_report_stmt   rpt "
					  + "  WHERE rpt.legal_entity_code = :legalEntityCode  "
					  + "  AND rpt.audit_type_code = :auditTypeCode "  
					  + "  AND rpt.audit_id = :auditId "
					  + "  AND rpt.applicable_for = '"+BankAuditConstant.FOR_CHECKLIST+"'  "
					  + "  AND rpt.statement_type in (:remarksForList) "
					  + "  AND rpt.compliance_category IN (:isCompliedList) "
					  + "  AND rpt.criticality = '"+BankAuditConstant.OBSERVATION_CRITICALITY_MAJOR+"' "					 
					  + "  order by CAST(rpt.sequence  as UNSIGNED) ASC ) as tmpRpt "
				  + "  group by mapping_id " ;   
 			
		Session session = getSession();		    
		session.setDefaultReadOnly(true);
		Query query=session.createNativeQuery(strQuery)
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("auditId", auditId)
				.setParameterList("remarksForList", remarksForList)
				.setParameterList("isCompliedList", isCompliedList)
				.setResultTransformer(new ResultTransformer() {					
					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						CheckListDto checkListDto=null;
						if(tuple!=null){								
							checkListDto=new CheckListDto();						 
							if(tuple[0]!=null && tuple[1]!=null){
								String[] observatiosArr = tuple[1].toString().split("#@::@#");
								checkListDto.setRemarks(observatiosArr[0].split("#::#")[1]);
								checkListDto.setStandardStatement(observatiosArr[1].split("#::#")[1]);
							}					 
						}
						return checkListDto;
					}
					@Override
					public List transformList(List collection) {
						return collection;
					}
				});
		
		List<CheckListDto> checkListDtoList = query.list(); 
		return checkListDtoList;
	}
		
	/******************** End New Code to Get Chapter/Section/Product/process and All Statements *********************/	
	
	/******************** START Get dynamic table/remarks/picture details  to store into report table ***********/	
	@Override
	public  List<CheckListDto> getAllGrpSubGrpActProcForReportTable(Integer legalEntityCode,  String auditTypeCode){ 
		logger.info("Inside getAllGrpSubGrpActProcForReportTable .. "+auditTypeCode);
		 
		 
		StringBuilder strQry = new StringBuilder("  Select  applicableFor, auditGroupCode, auditGroupName,  "
			   + "  auditSubGroupCode, auditSubGroupName,  "
			   + "  activityCode, activityName,   "
			   + "  processCode, processName, sequenceAll   "
			   + "  FROM (	  "
					    + "	 (SELECT distinct 'CH' as applicableFor,  "
						+ "	 grp.audit_group_code as auditGroupCode , grp.audit_group_name as auditGroupName, "
					    + "	 NULL as auditSubGroupCode, NULL as auditSubGroupName,   "
						+ "  NULL as activityCode, NULL as activityName,   "
						+ "  NULL as processCode, NULL as processName,"
						+ "  stmts.ag_sequence as sequenceAll"    	 						
						+ "  FROM  maint_report_sequence_process stmts   "  
						+ "	 INNER JOIN maint_audit_group grp   on stmts.audit_group_code = grp.audit_group_code   "
				 		+ "	 AND stmts.audit_type_code = grp.audit_type_code AND stmts.legal_entity_code = grp.legal_entity_code  "
				 		+ "	 AND grp.status='"+BankAuditConstant.STATUS_ACTIVE+"' AND  grp.entity_status='"+BankAuditConstant.STATUS_ACTIVE+"'  "
				 		+ "	 AND stmts.status='"+BankAuditConstant.STATUS_ACTIVE+"' AND  stmts.entity_status='"+BankAuditConstant.STATUS_ACTIVE+"'  "
						+ "	 WHERE stmts.legal_entity_code = :legalEntityCode  AND stmts.audit_type_code = :auditTypeCode     "				 		 
				 		+ "  AND grp.audit_group_code NOT IN (select distinct mapping.audit_group_code from activity_process_finding_mapping mapping  "
				 				+ "	 WHERE mapping.legal_entity_code = :legalEntityCode  AND mapping.audit_type_code = :auditTypeCode "
				 				+ "  AND mapping.status='"+BankAuditConstant.STATUS_ACTIVE+"' AND  mapping.entity_status='"+BankAuditConstant.STATUS_ACTIVE+"' ) " 			 		
						+ "	 group by applicableFor,  stmts.ag_sequence, auditGroupCode ,  auditGroupName,   auditSubGroupCode,  auditSubGroupName,  "
						+ "	 activityCode,  activityName,  processCode,  processName   "
						+ "	  )    "
					+ " UNION ALL   "
						+ "	 (SELECT distinct 'SC' as applicableFor,  "
						+ "	 grp.audit_group_code as auditGroupCode , grp.audit_group_name as auditGroupName,   "
						+ "	 subGrp.audit_sub_group_code as auditSubGroupCode, subGrp.audit_sub_group_name as auditSubGroupName,  " 
						+ "  NULL as activityCode, NULL as activityName,   "
						+ "  NULL as processCode, NULL as processName, "
						+ "  stmts.asg_sequence  as sequenceAll "    	 	
						+ "  FROM maint_report_sequence_process  stmts     "
						+ "	 INNER JOIN maint_audit_group grp   on stmts.audit_group_code = grp.audit_group_code   "
				    	+ "	 AND stmts.audit_type_code = grp.audit_type_code  AND stmts.legal_entity_code = grp.legal_entity_code"
				    	+ "  AND grp.status='"+BankAuditConstant.STATUS_ACTIVE+"' AND  grp.entity_status='"+BankAuditConstant.STATUS_ACTIVE+"'  "
						+ "	 INNER JOIN maint_audit_subgroup subGrp   on stmts.audit_group_code = subGrp.audit_group_code   "
				 		+ "	 AND stmts.audit_sub_group_code = subGrp.audit_sub_group_code AND stmts.audit_type_code = subGrp.audit_type_code  "
				    	+ "	 AND stmts.legal_entity_code = subGrp.legal_entity_code "
				    	+ "	 AND subGrp.status='"+BankAuditConstant.STATUS_ACTIVE+"' AND  subGrp.entity_status='"+BankAuditConstant.STATUS_ACTIVE+"' "  
				    	+ "	 AND stmts.status='"+BankAuditConstant.STATUS_ACTIVE+"' AND  stmts.entity_status='"+BankAuditConstant.STATUS_ACTIVE+"'  "
				    	+ "	 WHERE stmts.legal_entity_code = :legalEntityCode  AND stmts.audit_type_code = :auditTypeCode   "				    	 
				    	+ "  AND grp.audit_group_code NOT IN (select distinct mapping.audit_group_code from activity_process_finding_mapping mapping  "
				 				+ "	 WHERE mapping.legal_entity_code = :legalEntityCode  AND mapping.audit_type_code = :auditTypeCode "
				 				+ "  AND mapping.status='"+BankAuditConstant.STATUS_ACTIVE+"' AND  mapping.entity_status='"+BankAuditConstant.STATUS_ACTIVE+"' ) " 
						+ "	 group by applicableFor, stmts.asg_sequence, auditGroupCode ,  auditGroupName,   auditSubGroupCode,  auditSubGroupName,  "
						+ "	 activityCode,  activityName,  processCode,  processName    "
						+ "	 )   "
					+ " UNION ALL  "
						+ "	 (SELECT distinct 'PD' as applicableFor, "
						+ "	 grp.audit_group_code as auditGroupCode , grp.audit_group_name as auditGroupName,  "
						+ "	 subGrp.audit_sub_group_code as auditSubGroupCode, subGrp.audit_sub_group_name as auditSubGroupName, " 
						+ "	 act.activity_code as activityCode, act.activity_name as activityName,  "
						+ "  NULL as processCode, NULL as processName, "
						+ "  stmts.act_sequence  as sequenceAll " 
						+ "  FROM maint_report_sequence_process  stmts    "
						+ "  INNER JOIN maint_audit_group grp   on stmts.audit_group_code = grp.audit_group_code  "
				    	+ "	 AND stmts.audit_type_code = grp.audit_type_code  AND stmts.legal_entity_code = grp.legal_entity_code "
				    	+ "  AND grp.status='"+BankAuditConstant.STATUS_ACTIVE+"' AND  grp.entity_status='"+BankAuditConstant.STATUS_ACTIVE+"' 	  "
						+ "	 INNER JOIN maint_audit_subgroup subGrp   on stmts.audit_group_code = subGrp.audit_group_code  "
				 		+ "	 AND stmts.audit_sub_group_code = subGrp.audit_sub_group_code AND stmts.audit_type_code = subGrp.audit_type_code   "
				 		+ "	 AND stmts.legal_entity_code = subGrp.legal_entity_code  "
				 		+ "	 AND subGrp.status='"+BankAuditConstant.STATUS_ACTIVE+"' AND  subGrp.entity_status='"+BankAuditConstant.STATUS_ACTIVE+"'   "	 
				    	+ "	 INNER JOIN maint_audit_activity act on  stmts.activity_code = act.activity_code  "
				    	+ "	 AND stmts.legal_entity_code = act.legal_entity_code  "
				    	+ "	 AND act.status='"+BankAuditConstant.STATUS_ACTIVE+"' AND  act.entity_status='"+BankAuditConstant.STATUS_ACTIVE+"'  "
				    	+ "	 AND stmts.status='"+BankAuditConstant.STATUS_ACTIVE+"' AND  stmts.entity_status='"+BankAuditConstant.STATUS_ACTIVE+"'  "
				    	+ "	 WHERE stmts.legal_entity_code = :legalEntityCode  AND stmts.audit_type_code = :auditTypeCode      "				     
				    	+ "  AND grp.audit_group_code NOT IN (select distinct mapping.audit_group_code from activity_process_finding_mapping mapping  "
		 						+ "	 WHERE mapping.legal_entity_code = :legalEntityCode  AND mapping.audit_type_code = :auditTypeCode "
		 						+ "  AND mapping.status='"+BankAuditConstant.STATUS_ACTIVE+"' AND  mapping.entity_status='"+BankAuditConstant.STATUS_ACTIVE+"') " 
				    	+ "	 group by applicableFor,  stmts.act_sequence, auditGroupCode ,  auditGroupName,   auditSubGroupCode,  auditSubGroupName,  "
						+ "	 activityCode,  activityName,  processCode,  processName "
						+ "	 )   "
					+ " UNION ALL  "
						+ " (SELECT distinct 'PC' as applicableFor, "
						+ " grp.audit_group_code as auditGroupCode , grp.audit_group_name as auditGroupName,  "
						+ " subGrp.audit_sub_group_code as auditSubGroupCode, subGrp.audit_sub_group_name as auditSubGroupName,  "
					    + " act.activity_code as activityCode, act.activity_name as activityName,  "
					    + " prc.process_code as processCode, prc.process_name as processName,  "
					    + " stmts.prc_sequence  as sequenceAll " 
					    + " FROM maint_report_sequence_process  stmts    "
						+ " INNER JOIN maint_audit_group grp   on stmts.audit_group_code = grp.audit_group_code  "
			    		+ " AND stmts.audit_type_code = grp.audit_type_code  AND stmts.legal_entity_code = grp.legal_entity_code "
			    		+ " AND grp.status='"+BankAuditConstant.STATUS_ACTIVE+"' AND  grp.entity_status='"+BankAuditConstant.STATUS_ACTIVE+"'  "	
						+ " INNER JOIN maint_audit_subgroup subGrp   on stmts.audit_group_code = subGrp.audit_group_code  "
			 			+ " AND stmts.audit_sub_group_code = subGrp.audit_sub_group_code AND stmts.audit_type_code = subGrp.audit_type_code   "
			    		+ " AND stmts.legal_entity_code = subGrp.legal_entity_code "
			    		+ " AND subGrp.status='"+BankAuditConstant.STATUS_ACTIVE+"'  AND  subGrp.entity_status='"+BankAuditConstant.STATUS_ACTIVE+"' "  
			    		+ " INNER JOIN maint_audit_activity act on  stmts.activity_code = act.activity_code  "
			    		+ " AND stmts.legal_entity_code = act.legal_entity_code  "
			    		+ " AND act.status='"+BankAuditConstant.STATUS_ACTIVE+"' AND  act.entity_status='"+BankAuditConstant.STATUS_ACTIVE+"'  "
			    		+ " AND stmts.status='"+BankAuditConstant.STATUS_ACTIVE+"' AND  stmts.entity_status='"+BankAuditConstant.STATUS_ACTIVE+"'  "
			    		+ " INNER JOIN maint_audit_process prc on stmts.process_code = prc.process_code  "
						+ " AND stmts.legal_entity_code = prc.legal_entity_code "
						+ " AND prc.status='"+BankAuditConstant.STATUS_ACTIVE+"' AND  prc.entity_status='"+BankAuditConstant.STATUS_ACTIVE+"'  "
			    		+ " WHERE stmts.legal_entity_code = :legalEntityCode  AND stmts.audit_type_code = :auditTypeCode  "			    	 
			    		+ " AND grp.audit_group_code NOT IN (select distinct mapping.audit_group_code from activity_process_finding_mapping mapping  "
			    				+ "	 WHERE mapping.legal_entity_code = :legalEntityCode  AND mapping.audit_type_code = :auditTypeCode "
			    				+ "  AND mapping.status='"+BankAuditConstant.STATUS_ACTIVE+"' AND  mapping.entity_status='"+BankAuditConstant.STATUS_ACTIVE+"' ) " 
						+ " group by applicableFor,  stmts.prc_sequence, auditGroupCode ,  auditGroupName,   auditSubGroupCode,  auditSubGroupName,  "
						+ " activityCode,  activityName,  processCode,  processName  "
						+ ")   "
				+ " ) as tmp Where sequenceAll IS NOT NULL and sequenceAll<>'' order by CAST(sequenceAll  as UNSIGNED)  ASC ");				 
		
		Session session = getSession();		    
		session.setDefaultReadOnly(true);
		Query query=session.createSQLQuery(strQry.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode);	 
				
		List<CheckListDto> listData = query.setResultTransformer(new ResultTransformer() {		
			@Override
			public Object transformTuple(Object[] tuple, String[] aliases) { 
				CheckListDto checkListDto =null;
				if(tuple[0]!=null){
					checkListDto =  new CheckListDto(); 
						checkListDto.setApplicableFor(tuple[0].toString()); 					
					if(tuple[1]!=null)
						checkListDto.setAuditGroupCode(tuple[1].toString());
					if(tuple[2]!=null)
						checkListDto.setAuditGroupName(tuple[2].toString());
					if(tuple[3]!=null)
						checkListDto.setAuditSubGroupCode(tuple[3].toString());
					if(tuple[4]!=null)
						checkListDto.setAuditSubGroupName(tuple[4].toString());
					if(tuple[5]!=null)
						checkListDto.setActivityCode(tuple[5].toString());
					if(tuple[6]!=null)
						checkListDto.setActivityName(tuple[6].toString()); 
					if(tuple[7]!=null)
						checkListDto.setProcessCode(tuple[7].toString());
					if(tuple[8]!=null)	
						checkListDto.setProcessName(tuple[8].toString());					 
				}
				return checkListDto;
			}
			
			@Override
			public List transformList(List collection) { 
				return collection;
			}
		}).list(); 			
				
		return listData;		
	}
	
	@Override
	public Map<String, String> getAllGrpSubGrpActProcForReportTableForStatementTablesAndIOInput(Integer legalEntityCode,  String auditTypeCode){
		
		logger.info("Inside getAllGrpSubGrpActProcForReportTableForStatementTablesAndIOInput .. "+auditTypeCode);
		Map<String, String> dataListMap = null;
	 
		try {
			StringBuilder strQry = new StringBuilder(" SELECT " 
							+ " stmts.audit_group_code as auditGroupCode, "
							+ " stmts.audit_sub_group_code as auditSubGroupCode, "
						    + " stmts.activity_code as activityCode, "
						    + " stmts.process_code as processCode,   "
						    + " stmts.applicable_for as applicableFor, "
						    + " group_concat(stmts.statement_id, '#:@@:#', stmts.sequence, '#:@@:#', stmts.statement_type, '#:@@:#',  "
					    	+ " ifnull(stmts.statement,''), '#:@@:#ep'  order by stmts.sequence ASC SEPARATOR '#@::@#'   ) as statementCol " 
						    + " FROM maint_report_sequence_stmt stmts   "
							+ " Where stmts.status='"+BankAuditConstant.STATUS_ACTIVE+"' AND  stmts.entity_status='"+BankAuditConstant.STATUS_ACTIVE+"' "
				    		+ " AND  stmts.legal_entity_code = :legalEntityCode  AND stmts.audit_type_code = :auditTypeCode    "						 
							+ " group by auditGroupCode ,   auditSubGroupCode,  activityCode,   processCode, applicableFor  " ); 
			
			Session session = getSession();		    
			session.setDefaultReadOnly(true);
			Query query= session.createSQLQuery(strQry.toString())
					.setParameter("legalEntityCode", legalEntityCode)
					.setParameter("auditTypeCode", auditTypeCode); 
			
			List<Object[]> dataList=query.list();
					
			dataListMap = new HashMap<>();
			for (Object[] stmt : dataList) {
				String auditGroupCode = (stmt[0]==null || stmt[0].toString().equalsIgnoreCase("null") || stmt[0].toString().equals(""))?"":stmt[0].toString();			
				String auditGroupSubCode = (stmt[1]==null || stmt[1].toString().equalsIgnoreCase("null") || stmt[1].toString().equals(""))?"":stmt[1].toString();	
				String productCode = (stmt[2]==null || stmt[2].toString().equalsIgnoreCase("null") || stmt[2].toString().equals(""))?"":stmt[2].toString();	
				String processCode = (stmt[3]==null || stmt[3].toString().equalsIgnoreCase("null") || stmt[3].toString().equals(""))?"":stmt[3].toString();	
				String stmtData = stmt[5]==null?null:stmt[5].toString().replace("'", "''");
				dataListMap.put(auditGroupCode+auditGroupSubCode+productCode+processCode, stmtData);
			}
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		return dataListMap;
	}  	
	/******************** END Get dynamic table/remarks/picture details to store into report table ***********/
	
	/******************** START Get dynamic table/remarks/picture details for IR ***********/
	
	@Override
	public List<String> getAuditReportDynamicTable(Integer legalEntityCode, String auditId, String auditTypeCode, String auditGroupCode, String auditSubGroupCode,
 			String activityCode,  String processCode, String findingCode, String applicableFor, String statementType, String statementId){
		
		Session session = getSession();		    
		session.setDefaultReadOnly(true);
		
		StringBuilder strQuery= new StringBuilder("Select ardt as dynData,"
				+ " auditlvlDoc.referencePath as referenceDocPath  "
				+ " from AuditReportDynamicTable ardt "
				+ " Left join AuditLevelDocumentDetails auditlvlDoc on ardt.docId = auditlvlDoc.id and ardt.auditTypeCode = auditlvlDoc.auditTypeCode "
				+ " and ardt.legalEntityCode = auditlvlDoc.legalEntityCode  and ardt.auditId = auditlvlDoc.auditId  " 				
				+ " Where  ardt.legalEntityCode = '"+legalEntityCode+"'  "				
				+ " AND ardt.auditTypeCode = '"+auditTypeCode+"' "
				+ " AND ardt.auditId = '"+auditId+"' "	
				+ " AND ardt.statementId = '"+statementId+"' " );	
	 
		List<AuditReportDynamicTable> auditReportDynamicTableList = 
				session.createQuery(strQuery.toString()).setResultTransformer(new ResultTransformer() {			
					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						AuditReportDynamicTable auditReportDynamicTable = new AuditReportDynamicTable();
						if(tuple[0]!=null){
							auditReportDynamicTable =  (AuditReportDynamicTable) tuple[0];
							if(tuple[1]!=null){
								auditReportDynamicTable.setReferencePath(tuple[1].toString());
							}
						}
						return auditReportDynamicTable;
					}
					
					@Override
					public List transformList(List collection) { 
						return collection;
					}
				}).list();
		 
		if(auditReportDynamicTableList!=null && auditReportDynamicTableList.size()>0){
			return auditReportDynamicTableList.stream().map(ardTab -> ardTab.getRemarks()).collect(Collectors.toList());
		}
		return null;
	}
	
	/******************** END Get dynamic table/remarks/picture details for IR ***********/
	
	@Override
	public List<SectionDto> getOutstandingSection(String legalEntityCode, String auditTypeCode, String auditId) {
		
		 Session session = getSession();		    
		 session.setDefaultReadOnly(true);
		 
		 String sqlQuery = " SELECT rpt.audit_group_code, rpt.audit_sub_group_code, rpt.audit_sub_group_name,  rpt.audit_sub_group_name_hindi"
		 		+ "  from  audit_report_stmt rpt  "				 
				+" WHERE rpt.legal_entity_code = :legalEntityCode  "					
				+" AND rpt.audit_type_code = :auditTypeCode "
				+" AND rpt.audit_id = :auditId "
				+" AND rpt.audit_sub_group_code IS NOT NULL "
				+" AND rpt.applicable_for = '"+BankAuditConstant.FOR_SECTION+"' "
				+" AND (rpt.statement_type  IS NULL OR  rpt.statement_type = '' ) " 
				+" ORDER BY cast(rpt.sequence  AS UNSIGNED)  ASC  ";
		 		  		 
		 List<SectionDto> listSectionDto = null;
		 Query query=session.createSQLQuery(sqlQuery)
					.setResultTransformer(new ResultTransformer() {
						
						@Override
						public Object transformTuple(Object[] tuple, String[] aliases) {
							SectionDto sectionDto=null;
							if(tuple!=null){								
								sectionDto=new SectionDto();
								
								if(tuple[0]!=null){                   
									sectionDto.setAuditGroupCode(tuple[0].toString());
								}								
								if(tuple[1]!=null){                   
									sectionDto.setAuditSubGroupCode(tuple[1].toString());
								}
								if(tuple[2]!=null){
									sectionDto.setSectionName(tuple[2].toString());
								}
								if(tuple[3]!=null){
									sectionDto.setSectionNameHindi(tuple[3].toString());
								}
							}
							return sectionDto;
						}
						@Override
						public List transformList(List collection) {
							return collection;
						}
			}).setParameter("legalEntityCode", legalEntityCode)
			.setParameter("auditTypeCode", auditTypeCode)
			.setParameter("auditId", auditId);
		    
		 listSectionDto = query.list();
		 return listSectionDto;
	}
	
	@Override
	public Map<String, String> getOutstandingChecklist(String legalEntityCode, String auditTypeCode, String auditId){
		Session session = getSession();		    
		session.setDefaultReadOnly(true);
	
		StringBuilder strQuery = new StringBuilder(" SELECT rpt.audit_group_code, rpt.audit_sub_group_code,  rpt.activity_code, rpt.process_code, "
				+ " GROUP_CONCAT( DISTINCT rpt.sequence ,'#::#', rpt.finding_code  SEPARATOR '@::@' )  "
				+ " FROM audit_schedule sch   "
				+ " INNER JOIN  audit_observation obs  ON sch.legal_entity_code = obs.legal_entity_code   "
					+ " AND sch.audit_type_code = obs.audit_type_code AND sch.audit_id = obs.audit_id     "
				+ " INNER JOIN  audit_report_stmt rpt  ON sch.legal_entity_code = rpt.legal_entity_code AND sch.audit_type_code = rpt.audit_type_code  "
					+ " AND sch.audit_id = rpt.audit_id AND obs.mapping_id = rpt.mapping_id  "
				+ " WHERE CASE"
					+ "	WHEN sch.report_status  IS NOT NULL AND sch.report_status IN ('RD','RS') AND sch.respond_status IS NULL AND sch.review_status IS NULL "
					+ " 	THEN  obs.compliance_category = :nonCompliance "
					+ " WHEN sch.respond_status IS NOT NULL AND sch.respond_status <> 'RS'  "
					+ " 	THEN  obs.compliance_category = :nonCompliance   "
					+ " WHEN sch.respond_status = 'RS' AND  sch.review_status = 'WS' AND obs.review_status_attribute = :statusFC "
					+ " 	THEN  obs.compliance_category = :nonCompliance   "
					+ " ELSE FALSE END "
				+ " AND sch.legal_entity_code = :legalEntityCode "
				+ " AND sch.audit_type_code = :auditTypeCode "
				+ " AND sch.audit_id = :auditId "
				+ " AND rpt.applicable_for = :applicableFor  "
				+ " AND rpt.statement_type = :statementType  "
				//+ " AND statement IS  NOT NULL AND statement <> '' AND LOWER(statement) <> 'null' " 
				+ " GROUP BY rpt.audit_group_code, rpt.audit_sub_group_code, rpt.activity_code, rpt.process_code     ");
	 
		Query query=session.createNativeQuery(strQuery.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("auditId", auditId)
				.setParameter("nonCompliance", BankAuditConstant.COMPLIANCE_NO)
				.setParameter("statusFC", BankAuditConstant.STATUS_FURTHER_COMPLIANCE)
				.setParameter("applicableFor", BankAuditConstant.FOR_CHECKLIST)
				.setParameter("statementType", BankAuditConstant.FOR_CHECKLIST);
		
		List<Object[]> checklistList = query.list(); 
		
		Map<String, String> checklist = new HashMap<>();
		for (Object[] objects : checklistList) {			 
			checklist.put(objects[0].toString()+objects[1].toString()+objects[2].toString()+objects[3].toString() , objects[4]==null?"":objects[4].toString());
		}
		return checklist;
	}
	
	@Override
	public Map<String, String> getOutstandingStandardStmt(String legalEntityCode, String auditTypeCode, String auditId){
		Session session = getSession();	    
		session.setDefaultReadOnly(true);
	
		StringBuilder strQuery = new StringBuilder(" SELECT audit_group_code, audit_sub_group_code, activity_code, process_code, finding_code,"
				+ " statement, statement_hindi, criticality  "
				+ " FROM audit_schedule sch   "
				+ " INNER JOIN  audit_observation obs  ON sch.legal_entity_code = obs.legal_entity_code   "
					+ " AND sch.audit_type_code = obs.audit_type_code AND sch.audit_id = obs.audit_id     "
				+ " INNER JOIN  audit_report_stmt rpt  ON sch.legal_entity_code = rpt.legal_entity_code AND sch.audit_type_code = rpt.audit_type_code  "
					+ " AND sch.audit_id = rpt.audit_id AND obs.mapping_id = rpt.mapping_id  "
				+ " WHERE CASE"
					+ "	WHEN sch.report_status  IS NOT NULL AND sch.report_status IN ('RD','RS') AND sch.respond_status IS NULL AND sch.review_status IS NULL "
					+ " 	THEN  obs.compliance_category = :nonCompliance "
					+ " WHEN sch.respond_status IS NOT NULL AND sch.respond_status <> 'RS'  "
					+ " 	THEN  obs.compliance_category = :nonCompliance   "
					+ " WHEN sch.respond_status = 'RS' AND  sch.review_status = 'WS' AND obs.review_status_attribute = :statusFC "
					+ " 	THEN  obs.compliance_category = :nonCompliance   "
					+ " ELSE FALSE END "
				+ " AND sch.legal_entity_code = :legalEntityCode "
				+ " AND sch.audit_type_code = :auditTypeCode "
				+ " AND sch.audit_id = :auditId "
				+ " AND rpt.applicable_for = :applicableFor  "
				+ " AND rpt.statement_type = :statementType  "
				+ " AND statement IS  NOT NULL AND statement <> '' AND LOWER(statement) <> 'null'  ");
	 
		Query query=session.createNativeQuery(strQuery.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("auditId", auditId)
				.setParameter("nonCompliance", BankAuditConstant.COMPLIANCE_NO)
				.setParameter("statusFC", BankAuditConstant.STATUS_FURTHER_COMPLIANCE)
				.setParameter("applicableFor", BankAuditConstant.FOR_CHECKLIST)
				.setParameter("statementType", BankAuditConstant.FOR_STANDARD_STMT);
		
		List<Object[]> standardStmtList = query.list(); 
		
		Map<String, String> stndStmt = new HashMap<>();
		for (Object[] objects : standardStmtList) {
			String stmt = (objects[5]==null?" ":objects[5].toString()+" ")+"#::#"+(objects[6]==null?" ":objects[6].toString()+" ")
					+"#::#"+(objects[7]==null?" ":objects[7].toString()+" ");
			stndStmt.put(objects[0].toString()+objects[1].toString()+objects[2].toString()+objects[3].toString()+objects[4].toString(), stmt);
		}
		return stndStmt;
	}
	
	@Override
	public Map<String, String> getOutstandingRemarks(String legalEntityCode, String auditTypeCode, String auditId){
		Session session = getSession();		    
		session.setDefaultReadOnly(true);
	
		StringBuilder strQuery = new StringBuilder(" SELECT audit_group_code, audit_sub_group_code, activity_code, process_code, finding_code,"
				+ " statement, statement_hindi, criticality  "
				+ " FROM audit_schedule sch   "
				+ " INNER JOIN  audit_observation obs  ON sch.legal_entity_code = obs.legal_entity_code   "
					+ " AND sch.audit_type_code = obs.audit_type_code AND sch.audit_id = obs.audit_id     "
				+ " INNER JOIN  audit_report_stmt rpt  ON sch.legal_entity_code = rpt.legal_entity_code AND sch.audit_type_code = rpt.audit_type_code  "
					+ " AND sch.audit_id = rpt.audit_id AND obs.mapping_id = rpt.mapping_id  "
				+ " WHERE CASE"
					+ "	WHEN sch.report_status  IS NOT NULL AND sch.report_status IN ('RD','RS') AND sch.respond_status IS NULL AND sch.review_status IS NULL "
					+ " 	THEN  obs.compliance_category = :nonCompliance "
					+ " WHEN sch.respond_status IS NOT NULL AND sch.respond_status <> 'RS'  "
					+ " 	THEN  obs.compliance_category = :nonCompliance   "
					+ " WHEN sch.respond_status = 'RS' AND  sch.review_status = 'WS' AND obs.review_status_attribute = :statusFC "
					+ " 	THEN  obs.compliance_category = :nonCompliance   "
					+ " ELSE FALSE END "
				+ " AND sch.legal_entity_code = :legalEntityCode "
				+ " AND sch.audit_type_code = :auditTypeCode "
				+ " AND sch.audit_id = :auditId "
				+ " AND rpt.applicable_for = :applicableFor  "
				+ " AND rpt.statement_type = :statementType  "
				+ " AND statement IS  NOT NULL AND statement <> '' AND LOWER(statement) <> 'null'  ");
	 
		Query query=session.createNativeQuery(strQuery.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("auditId", auditId)
				.setParameter("nonCompliance", BankAuditConstant.COMPLIANCE_NO)
				.setParameter("statusFC", BankAuditConstant.STATUS_FURTHER_COMPLIANCE)
				.setParameter("applicableFor", BankAuditConstant.FOR_CHECKLIST)
				.setParameter("statementType", BankAuditConstant.FOR_REMARKS);
		
		List<Object[]> remarksList = query.list(); 
		
		Map<String, String> remarks = new HashMap<>();
		for (Object[] objects : remarksList) {
			String stmt = (objects[5]==null?" ":objects[5].toString()+" ")+"#::#"+(objects[6]==null?" ":objects[6].toString()+" ")
					+"#::#"+(objects[7]==null?" ":objects[7].toString()+" ");
			remarks.put(objects[0].toString()+objects[1].toString()+objects[2].toString()+objects[3].toString()+objects[4].toString(), stmt);
		}
		
		return remarks;
	}
	
 }
