package com.bankaudit.report.v2.dao;
 
import java.util.List; 
import org.hibernate.Session;
import org.hibernate.query.Query;
import org.hibernate.transform.ResultTransformer;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import com.bankaudit.dao.AbstractDao;
import com.bankaudit.report.v2.dto.ChapterDto;
import com.bankaudit.report.v2.dto.CheckListDto;
import com.bankaudit.report.v2.dto.ProcessDto;
import com.bankaudit.report.v2.dto.ProductDto;
import com.bankaudit.report.v2.dto.SectionDto;

import java.util.ArrayList;

@Repository("auditBookReportServiceDaoV2")
public class AuditBookReportDaoImplV2 extends AbstractDao  implements AuditBookReportDaoV2 {

	@Override
	public List<ChapterDto> getChapter(Integer legalEntityCode, String auditId) {
		 Session session = getSession();		    
		 session.setDefaultReadOnly(true);
		 //asd.audit_id = 'A0002'
		 
		 String sqlQuery = " select  stds.ag_sequence, grp.audit_group_code, grp.audit_group_name  from  audit_schedule asd  "
				+"	 inner join audit_observation  abr on  asd.audit_id=abr.audit_id "
				+"	 inner join maint_report_standard_stmt stds on  stds.mapping_id =abr.mapping_id  "
				+"	 Inner join maint_audit_group grp on grp.audit_group_code = stds.audit_group_code "
				+"   where asd.audit_id = '"+auditId+"' and stds.legal_entity_code = '"+legalEntityCode+"' and "
				+"   asd.legal_entity_code = '"+legalEntityCode+"'  "
				+"	 group by stds.ag_sequence, grp.audit_group_code,grp.audit_group_name  "
				+"	 order by cast(stds.ag_sequence  as UNSIGNED)  ASC  ";
		
		 /*String sqlQuery = "select  grp.audit_group_code,grp.audit_group_name  from  audit_schedule asd  "
				+"	inner join audit_observation  abr on  asd.audit_id=abr.audit_id "
				+"	inner join activity_process_finding_mapping act_map on act_map.mapping_id=abr.mapping_id " 
				+"	Inner join maint_audit_group grp on grp.audit_group_code = act_map.audit_group_code "
				+"	inner join  maint_report_standard_stmt stds on  stds.audit_group_code=act_map.audit_group_code " 
				+"  where asd.audit_id = '"+auditId+"' and act_map.legal_entity_code = '"+legalEntityCode+"' and "
				+"  asd.legal_entity_code = '"+legalEntityCode+"'  "
				+"  group by   grp.audit_group_code,grp.audit_group_name ";*/
		 
		 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());
								}								  
							}
							return chapterDto;
						}
						@Override
						public List transformList(List collection) {
							return collection;
						}
					});
		    
		 listChapterDto = query.list();
		 return listChapterDto;
	}

	@Override
	public List<SectionDto> getSection(Integer legalEntityCode, String auditGroupCode) {
		
		 Session session = getSession();		    
		 session.setDefaultReadOnly(true);
		 
		 String sqlQuery = "  Select stds.asg_sequence, sgrp.audit_sub_group_code, sgrp.audit_sub_group_name from  audit_schedule asd   "
				 +"  inner join audit_observation  abr on  asd.audit_id=abr.audit_id   "
				 +"  Inner join  maint_report_standard_stmt stds on   stds.mapping_id =abr.mapping_id   "
				 //+"  Inner join maint_audit_group grp on grp.audit_group_code = stds.audit_group_code  "
				 +"  Inner join  maint_audit_subgroup  sgrp on sgrp.audit_sub_group_code = stds.audit_sub_group_code      "
				 +"  where stds.audit_group_code = '"+auditGroupCode+"' and  stds.legal_entity_code = '"+legalEntityCode+"' "
				 +"  group by  stds.asg_sequence,sgrp.audit_sub_group_code, sgrp.audit_sub_group_name    "  
				 +"  order by cast(stds.asg_sequence  as UNSIGNED)  ASC ";
		 
		 
		 /*String sqlQuery = " Select  grp.audit_sub_group_code, grp.audit_sub_group_name  "
				 +"  from   activity_process_finding_mapping act_map     "
				 +"  Inner join  maint_audit_subgroup  grp on grp.audit_sub_group_code = act_map.audit_sub_group_code   "
				 +"  Inner join  maint_report_standard_stmt stds on  stds.audit_sub_group_code=act_map.audit_sub_group_code   "
				 +" where act_map.audit_group_code = '"+auditGroupCode+"' and  act_map.legal_entity_code = '"+legalEntityCode+"' "
				 +" group by  grp.audit_sub_group_code, grp.audit_sub_group_name"   ; */
		 
		 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[1]!=null){                   
									sectionDto.setAuditSubGroupCode(tuple[1].toString());
								}
								if(tuple[2]!=null){
									sectionDto.setSectionName(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 auditGroupCode, String auditSubGroupCode) {
		 
		Session session = getSession();		    
		session.setDefaultReadOnly(true);
		
		String sqlQuery = "  Select stds.act_sequence, act.activity_code, act.activity_name  from  audit_schedule asd   "
				+" inner join audit_observation  abr on  asd.audit_id=abr.audit_id   "
				+" Inner join  maint_report_standard_stmt stds on   stds.mapping_id =abr.mapping_id    "
				+" Inner join maint_audit_activity  act on act.activity_code = stds.activity_code  "
				//+" Inner join maint_audit_group grp on grp.audit_group_code = stds.audit_group_code  "
				//+" Inner join  maint_audit_subgroup  sgrp on sgrp.audit_sub_group_code = stds.audit_sub_group_code   "
				+"	where stds.audit_sub_group_code = '"+auditSubGroupCode+"' and stds.audit_group_code = '"+auditGroupCode+"'  "
				+"  and stds.legal_entity_code = '"+legalEntityCode+"'  "
				+" group by  stds.act_sequence, act.activity_code, act.activity_name  "
				+" order by cast(stds.act_sequence  as UNSIGNED)  ASC ";
		
		 /*String sqlQuery = " Select  act.activity_code, act.activity_name    "
						 +" from  activity_process_finding_mapping act_map 	  "
						 +"	Inner join maint_audit_activity  act on act.activity_code = act_map.activity_code "
						 +"	Inner join  maint_report_standard_stmt stds on  stds.activity_code=act_map.activity_code  " 						 
						+"	where stds.audit_sub_group_code = '"+auditSubGroupCode+"' and stds.audit_group_code = '"+auditGroupCode+"'  "
						+"  and stds.legal_entity_code = '"+legalEntityCode+"'  "
						+"	group by  act.activity_code, act.activity_name   " ;*/
		 
		 
		 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[1]!=null){                   
									productDto.setActivityCode(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();
		 return listProductDto;
	}

	@Override
	public List<ProcessDto> getProcess(Integer legalEntityCode, String auditGroupCode, String auditSubGroupCode,
			String activityCode) {
		
		Session session = getSession();		    
		session.setDefaultReadOnly(true);
		
		String sqlQuery = "Select  stds.prc_sequence, proc.process_code, proc.process_name from  audit_schedule asd	 " 
				+"	 inner join audit_observation  abr on  asd.audit_id=abr.audit_id 	 "
		 +"	 Inner join  maint_report_standard_stmt stds on   stds.mapping_id =abr.mapping_id  	 "
		 +"	 inner join maint_audit_process proc on proc.process_code = stds.process_code	 "
		// +"	 Inner join maint_audit_activity  act on act.activity_code = stds.activity_code	 "
		// +"	 Inner join maint_audit_group grp on grp.audit_group_code = stds.audit_group_code	 "
		// +"	 Inner join  maint_audit_subgroup  sgrp on sgrp.audit_sub_group_code = stds.audit_sub_group_code 	 "
		+"	where stds.activity_code = '"+activityCode+"' and stds.audit_sub_group_code = '"+auditSubGroupCode+"' "
		+"  and stds.audit_group_code = '"+auditGroupCode+"'    and stds.legal_entity_code = '"+legalEntityCode+"'  "
		 +"	 group by   stds.prc_sequence ,proc.process_code, proc.process_name 	 "
		 +"	 order by cast(stds.prc_sequence   as UNSIGNED)  ASC	 ";
		 
		 /*String sqlQuery = "Select  proc.process_code, proc.process_name	 " 
							 +"	from  activity_process_finding_mapping act_map 	 "
							 +"	inner join maint_audit_process proc on proc.process_code = act_map.process_code	 "
							 +"	Inner join  maint_report_standard_stmt stds on  stds.process_code=act_map.process_code 	 "
							+"	where stds.activity_code = '"+activityCode+"' and stds.audit_sub_group_code = '"+auditSubGroupCode+"' "
							+"  and stds.audit_group_code = '"+auditGroupCode+"'    and stds.legal_entity_code = '"+legalEntityCode+"'  "
							+"	group by  proc.process_code, proc.process_name "
							+"  order by proc.process_name ";*/
		 
		 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[1]!=null){
									processDto.setProcessCode(tuple[1].toString()); 
								}
								if(tuple[2]!=null){                   
									processDto.setProcessName(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 auditGroupCode, String auditSubGroupCode,
			String activityCode, String processCode, String isComplied ) {
		Session session = getSession();		    
		session.setDefaultReadOnly(true);
		String sqlQuery ="";
		
		if(isComplied.equalsIgnoreCase("C")){
				 sqlQuery  = " Select    stndstmt,  fndCode, cast((@idx \\:=(@idx+1)) as UNSIGNED) as seqNum from "
							+ " (select   stds.fnd_sequence as  fndseq ,  stds.standard_statement   as stndstmt ,stds.process_code, "
						+ " stds.activity_code, stds.audit_sub_group_code, stds.audit_group_code, stds.finding_code as fndCode from   audit_schedule asd  "
						+ " inner join audit_observation  abr on  asd.audit_id=abr.audit_id  "
						+ " Inner join  maint_report_standard_stmt stds on   stds.mapping_id =abr.mapping_id   "
						+ " where    abr.compliance_category = '"+isComplied+"'  and stds.process_code = '"+processCode+"'  and stds.activity_code = '"+activityCode+"' "
						+ " and stds.audit_sub_group_code = '"+auditSubGroupCode+"'  and stds.audit_group_code = '"+auditGroupCode+"'   "
						+"  and stds.legal_entity_code = '"+legalEntityCode+"' and stds.standard_statement is not null "
						+ " and stds.standard_statement!='' "				
						+ " group by stds.fnd_sequence   ,  stds.standard_statement,stds.process_code, "
						+ " stds.activity_code, stds.audit_sub_group_code, stds.audit_group_code, stds.finding_code "
						+ " order by  cast(stds.fnd_sequence as UNSIGNED)  ASC ) StandardSTMT, "
						+ " (Select @idx \\:=0) rowNum  ";
		}else{
				sqlQuery  = " Select    stndstmt,  fndCode, cast((@idx \\:=(@idx+1)) as UNSIGNED) as seqNum from "
						+ " (select   stds.fnd_sequence as  fndseq ,  abr.finding_remarks    as stndstmt ,stds.process_code, "  //  abr.remarks
					+ " stds.activity_code, stds.audit_sub_group_code, stds.audit_group_code, stds.finding_code as fndCode from   audit_schedule asd  "
					+ " inner join audit_observation  abr on  asd.audit_id=abr.audit_id  "
					+ " Inner join  maint_report_standard_stmt stds on   stds.mapping_id =abr.mapping_id   "
					+ " where    abr.compliance_category = '"+isComplied+"'  and stds.process_code = '"+processCode+"'  and stds.activity_code = '"+activityCode+"' "
					+ " and stds.audit_sub_group_code = '"+auditSubGroupCode+"'  and stds.audit_group_code = '"+auditGroupCode+"'   "
					+"  and stds.legal_entity_code = '"+legalEntityCode+"' and abr.finding_remarks is not null "  // abr.remarks
					+ " and abr.finding_remarks !='' "	  //abr.remarks 			
					+ " group by stds.fnd_sequence   , abr.finding_remarks , stds.process_code, "  //abr.remarks
					+ " stds.activity_code, stds.audit_sub_group_code, stds.audit_group_code, stds.finding_code "
					+ " order by  cast(stds.fnd_sequence as UNSIGNED)  ASC ) StandardSTMT, "
					+ " (Select @idx \\:=0) rowNum  ";		 
		}
		/*String sqlQuery ="";if(isComplied.equalsIgnoreCase("C")){
		sqlQuery = "  Select    stndstmt, cast((@idx \\:=(@idx+1)) as UNSIGNED) as seqNum "
				+ "		from (select   cast(stds.over_all_sequence as UNSIGNED) AS Seq,  stds.standard_statement   as stndstmt  "
				+ "		from    audit_observation  abr   "
				+ " 	inner join activity_process_finding_mapping act_map on act_map.mapping_id=abr.mapping_id   "
				+ " 	inner join maint_report_standard_stmt stds on  stds.audit_group_code=act_map.audit_group_code "
				+ " 	and stds.finding_code=act_map.finding_code "
				+ " 	where    abr.compliance_category = '"+isComplied+"'  and act_map.process_code = '"+processCode+"'  and act_map.activity_code = '"+activityCode+"' "
				+ " 	and act_map.audit_sub_group_code = '"+auditSubGroupCode+"'  and act_map.audit_group_code = '"+auditGroupCode+"'   "
				+"  	and act_map.legal_entity_code = '"+legalEntityCode+"' and stds.standard_statement is not null "
				+ " 	and stds.standard_statement!='' "				
				+ " 	order by  cast(stds.over_all_sequence as UNSIGNED)  ASC ) StandardSTMT, "
				+ "     (Select @idx \\:=0) rowNum  ";
		} else {
			sqlQuery = "  Select    stndstmt, cast((@idx \\:=(@idx+1)) as UNSIGNED) as seqNum "
					+ "		from (select   cast(stds.over_all_sequence as UNSIGNED) AS Seq,  abr.remarks   as stndstmt "
					+ "		from    audit_observation  abr   "
					+ " 	inner join activity_process_finding_mapping act_map on act_map.mapping_id=abr.mapping_id   "
					+ " 	inner join maint_report_standard_stmt stds on  stds.audit_group_code=act_map.audit_group_code "
					+ " 	and stds.finding_code=act_map.finding_code "
					+ " 	where    abr.compliance_category = '"+isComplied+"'  and act_map.process_code = '"+processCode+"'  and act_map.activity_code = '"+activityCode+"' "
					+ " 	and act_map.audit_sub_group_code = '"+auditSubGroupCode+"' and act_map.audit_group_code = '"+auditGroupCode+"' "
					+"  	  and act_map.legal_entity_code = '"+legalEntityCode+"' and abr.remarks is not null "
					+ " 	and abr.remarks!='' "				
					+ " 	order by  cast(stds.over_all_sequence as UNSIGNED)  ASC ) StandardSTMT, "
					+ "     (Select @idx \\:=0) rowNum  ";
		}*/
		
		 

		 List<CheckListDto> listCheckListDto = null;
		 Query query=session.createSQLQuery(sqlQuery)
				 .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[1]!=null){  						 
									checkListDto.setFindingCode(tuple[1].toString());  
								}
								if(tuple[2]!=null){  						 
									checkListDto.setSequenceNumber(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;
		 
	}

	///*********Not User start********///
	@Override
	public List<String> getStaticContentForChapter(Integer legalEntityCode, String auditGroupCode, String reportLanguage) {

		Session session = getSession();		    
		session.setDefaultReadOnly(true);
		 
		 String sqlQuery="";
		  if(reportLanguage.equalsIgnoreCase("hi")){
			 sqlQuery = " select stmt.static_statement_hindi from  maint_report_static_stmt stmt where "
		 		+ " stmt.audit_group_code= '"+auditGroupCode+"' and stmt.applicable_for='C' "
		 				+ " and stmt.legal_entity_code = '"+legalEntityCode+"' " ;	
		  }else{
			  sqlQuery = " select stmt.static_statement from  maint_report_static_stmt stmt where "
				 		+ " stmt.audit_group_code= '"+auditGroupCode+"' and stmt.applicable_for='C' "
				 				+ " and stmt.legal_entity_code = '"+legalEntityCode+"' " ;	
		  }
		 Query query=session.createSQLQuery(sqlQuery);
		 
		 return query.list();
		 
	}

	@Override
	public List<String> getStaticContentForSection(Integer legalEntityCode, String auditGroupCode,
			String auditSubGroupCode,  String reportLanguage) {
		Session session = getSession();		    
		session.setDefaultReadOnly(true);
		 String sqlQuery="";
		  if(reportLanguage.equalsIgnoreCase("hi")){
			  sqlQuery = " select stmt.static_statement_hindi from  maint_report_static_stmt stmt where "
			 		+ " stmt.audit_group_code= '"+auditGroupCode+"' and stmt.audit_sub_group_code = '"+auditSubGroupCode+"'"
			 				+ " and stmt.applicable_for='S' and stmt.legal_entity_code = '"+legalEntityCode+"' " ;	
		  }
		  else{
			  sqlQuery = " select stmt.static_statement from  maint_report_static_stmt stmt where "
				 		+ " stmt.audit_group_code= '"+auditGroupCode+"' and stmt.audit_sub_group_code = '"+auditSubGroupCode+"'"
				 				+ " and stmt.applicable_for='S' and stmt.legal_entity_code = '"+legalEntityCode+"' " ;	
		  }
		 
		 Query query=session.createSQLQuery(sqlQuery);
		 
		 return query.list();
	}

	@Override
	public List<String> getStaticContentForProduct(Integer legalEntityCode, String auditGroupCode,
			String auditSubGroupCode, String activityCode, String reportLanguage) {
		Session session = getSession();		    
		session.setDefaultReadOnly(true);

		 String sqlQuery="";
		  if(reportLanguage.equalsIgnoreCase("hi")){ 
			  sqlQuery = " select stmt.static_statement_hindi from  maint_report_static_stmt stmt where "		 
		 		+ " stmt.audit_group_code= '"+auditGroupCode+"' and stmt.audit_sub_group_code = '"+auditSubGroupCode+"' "
		 		+ " and stmt.activity_code = '"+activityCode+"' and stmt.applicable_for='P' and stmt.legal_entity_code = '"+legalEntityCode+"' " ;	
		  } else{
			  sqlQuery = " select stmt.static_statement from  maint_report_static_stmt stmt where "		 
			 		+ " stmt.audit_group_code= '"+auditGroupCode+"' and stmt.audit_sub_group_code = '"+auditSubGroupCode+"' "
			 		+ " and stmt.activity_code = '"+activityCode+"' and stmt.applicable_for='P' and stmt.legal_entity_code = '"+legalEntityCode+"' " ;					
		  }
		 Query query=session.createSQLQuery(sqlQuery);
		 
		 return query.list();
	}
	//*********Not User end********///
	
	@Override
	public List<String> getDerivedStatementForChapter(Integer legalEntityCode,  String auditGroupCode) {
		// TODO Auto-generated method stub
		Session session = getSession();		    
		session.setDefaultReadOnly(true);
		 
		 String sqlQuery = "select (case when stmt.derived_statement is not null "
		 		+ " then stmt.derived_statement else stmt.static_statement end) as stmts "
		 		+ "  from audit_report_stmts stmt  where stmt.audit_group_code = '"+auditGroupCode+"' "
				 + " 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 ";
		 
		 
		/* String sqlQuery = "select stmt.derived_stmt from maint_report_derived_stmt stmt where  "
				 			+" stmt.ag_sequence= '"+groupSequence+"' and   stmt.legal_entity_code = '"+legalEntityCode+"' "
				 			+" and stmt.asg_sequence IS NULL  and stmt.act_sequence IS NULL ";*/
		 
		 Query query=session.createSQLQuery(sqlQuery);		 
		 return query.list();
	}

	@Override
	public List<String> getDerivedStatementForSection(Integer legalEntityCode, String auditGroupCode,
			String auditSubGroupCode) {
		Session session = getSession();		    
		session.setDefaultReadOnly(true); 
		
		 String sqlQuery = "select (case when stmt.derived_statement is not null "		 		
				 + " then  (Case WHen  stmt.id in (2,3,4)  then "
		 		+ "  concat('<style isUnderline=\"true\">',stmt.derived_statement,'</style>') "
		 		+ "  else   stmt.derived_statement end ) "		 		
		 		+ " else stmt.static_statement end) as stmts "
		 		+ "  from audit_report_stmts stmt  where stmt.audit_group_code = '"+auditGroupCode+"' "
				 + " and stmt.audit_sub_group_code  = '"+auditSubGroupCode+"' "
				 + " 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);		 
		 return query.list();
	}

	@Override
	public List<String> getDerivedStatementForProduct(Integer legalEntityCode, String auditGroupCode,
			String auditSubGroupCode, String activityCode) {
		Session session = getSession();		    
		session.setDefaultReadOnly(true);
		 
		 String sqlQuery = "select (case when stmt.derived_statement is not null "
		 		+ " then   stmt.derived_statement else stmt.static_statement end) as stmts  "
		 		+ " from audit_report_stmts stmt  where stmt.audit_group_code = '"+auditGroupCode+"' "
				 + " and stmt.audit_sub_group_code  = '"+auditSubGroupCode+"' "
				 + " and stmt.activity_code    ='"+activityCode+"' "
				 + " 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);		 
		 return query.list();
	}

	@Override
	public List<String> getDerivedStatementForProcess(Integer legalEntityCode, String auditGroupCode,
			String auditSubGroupCode, String activityCode, String processCode) {
		Session session = getSession();		    
		session.setDefaultReadOnly(true);
		 
		 String sqlQuery = "select (case when stmt.derived_statement is not null "
		 		+ " then   stmt.derived_statement else stmt.static_statement end) as stmts  "
		 		+ "  from audit_report_stmts stmt  where 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  IS NULL OR  stmt.finding_code ='') "				 
				 + " order by cast(stmt.sequence as UNSIGNED)  ASC ";
		 
		 Query query=session.createSQLQuery(sqlQuery);		 
		 return query.list();
	}

	@Override
	public List<String> getDerivedStatementForchecklist(Integer legalEntityCode, String auditGroupCode,
			String auditSubGroupCode, String activityCode, String processCode, String findingCode) {
		// TODO Auto-generated method stub
		Session session = getSession();		    
		session.setDefaultReadOnly(true);
		 
		 String sqlQuery = "select (case when stmt.derived_statement is not null "
		 		+ " then   stmt.derived_statement else stmt.static_statement end) as stmts  "
		 		+ " from audit_report_stmts stmt  where 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+"' "				 
				 + " order by cast(stmt.sequence as UNSIGNED)  ASC  ";
		 
		 Query query=session.createSQLQuery(sqlQuery);		 
		 return query.list();
	}
}
