package com.bankaudit.report.v1.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.v1.dto.ChapterDto;
import com.bankaudit.report.v1.dto.CheckListDto;
import com.bankaudit.report.v1.dto.ProcessDto;
import com.bankaudit.report.v1.dto.ProductDto;
import com.bankaudit.report.v1.dto.SectionDto;

import java.util.ArrayList;

@Repository("auditBookReportServiceDao")
public class AuditBookReportDaoImpl extends AbstractDao  implements AuditBookReportDao {

	@Override
	public List<ChapterDto> getChapter(Integer legalEntityCode, String auditId) {
		 Session session = getSession();		    
		 session.setDefaultReadOnly(true);
		 
		 String sqlQuery = "Select seq.ag_sequence,seq.audit_group_code,grp.audit_group_name   "
		 		+" from  activity_process_finding_mapping act_map  "
				+" inner join  audit_observation  abr on act_map.mapping_id=abr.mapping_id " 
				+" inner join  audit_schedule asd on asd.audit_id=abr.audit_id "
				+" inner join  maint_report_sequence seq on seq.audit_group_code = act_map.audit_group_code "
				+" Inner join  maint_audit_group grp on grp.audit_group_code = act_map.audit_group_code "
				+" where asd.audit_id = '"+auditId+"' and act_map.legal_entity_code = '"+legalEntityCode+"' "
				+" group by  seq.ag_sequence,seq.audit_group_code,grp.audit_group_name  " 
				+" 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());
								}								  
							}
							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 seq.asg_sequence, seq.audit_sub_group_code, grp.audit_sub_group_name  "
							+" from   activity_process_finding_mapping act_map  "
							+" inner join  maint_report_sequence seq on seq.audit_sub_group_code = act_map.audit_sub_group_code  "
							+" Inner join  maint_audit_subgroup  grp on grp.audit_sub_group_code = act_map.audit_sub_group_code  "
							+" where seq.audit_group_code = '"+auditGroupCode+"' and  seq.legal_entity_code = '"+legalEntityCode+"' "
							+" group by  seq.asg_sequence, seq.audit_sub_group_code, grp.audit_sub_group_name  "   
							+" order by cast(seq.asg_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[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 seq.act_sequence, seq.activity_code, act.activity_name  "
						+"	from  activity_process_finding_mapping act_map   "
						+"	inner join maint_report_sequence seq on seq.activity_code = act_map.activity_code  " 
						+"	Inner join maint_audit_activity  act on act.activity_code = act_map.activity_code   "
						+"	where seq.audit_sub_group_code = '"+auditSubGroupCode+"' and seq.audit_group_code = '"+auditGroupCode+"'  "
						+"  and seq.legal_entity_code = '"+legalEntityCode+"'  "
						+"	group by  seq.act_sequence, seq.activity_code, act.activity_name  "    
						+"	order by cast(seq.act_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[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  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 		 "
							+"	where 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+"'  "
							+"	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.setProcessCode(tuple[0].toString()); 
								}
								if(tuple[1]!=null){                   
									processDto.setProcessName(tuple[1].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) {
		Session session = getSession();		    
		session.setDefaultReadOnly(true);
		  
		/* String sqlQuery = " Select checkList.finding_code,  checkList.finding_name, cast((@idx \\:=(@idx+1)) as UNSIGNED)   "
				+"	from ( Select  finding.finding_code, finding.finding_name  "
				+"	from  activity_process_finding_mapping act_map   "
				+"	inner join maint_audit_finding finding on finding.finding_code = act_map.finding_code  		 " 
				+"	 where 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+"'  "
				+"	group by   finding.finding_code, finding.finding_name order by finding.finding_name ) checkList, "
				+"	(Select @idx \\:=0) rowNum " ; */
		 
		String sqlQuery = " Select checkList.finding_id,  checkList.finding_description, "
				+ " checkList.compliance_category,  checkList.finding_remarks, cast((@idx \\:=(@idx+1)) as UNSIGNED)   "
				+"	from ( select abr.finding_id, abr.finding_description, abr.compliance_category , abr.finding_remarks  " 
				+"	 from  baas_dos.activity_process_finding_mapping  act_map  "
				+"	inner join baas_dos.audit_observation abr on act_map.finding_id =  abr.finding_id 		 " 
				+"	 where 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+"'  "
				+"	group by    abr.finding_id, abr.finding_description, abr.compliance_category , abr.finding_remarks "
				+ "  order by  abr.finding_description ) checkList, "
				+"	(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.setFindingCode(tuple[0].toString()); 
								}
								if(tuple[1]!=null){  									
									checkListDto.setChecklist(tuple[1].toString()); 
									checkListDto.setSequenceNumber(tuple[4]==null?"": (tuple[4].toString())+".");
								}
								if(tuple[2]!=null){  						
									String complianceCategory = tuple[2].toString().equalsIgnoreCase("C")?"Complied":
																tuple[2].toString().equalsIgnoreCase("N")?"Not Complied":"Not Applicable";
									checkListDto.setIsComplied(complianceCategory);  
								}
								 
								if(tuple[3]!=null){  									
									checkListDto.setRemarks(tuple[3].toString());  
								}
								 
								 								  
							}
							return checkListDto;
						}
						@Override
						public List transformList(List collection) {
							return collection;
						}
					}); 
		    
		 listCheckListDto = query.list();
		 return listCheckListDto;
		 
	}

	@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();
	}

	@Override
	public List<String> getDerivedStatementForChapter(Integer legalEntityCode,  String groupSequence) {
		// TODO Auto-generated method stub
		Session session = getSession();		    
		session.setDefaultReadOnly(true);
		 
		 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 groupSequence, String subGroupSequence) {
		Session session = getSession();		    
		session.setDefaultReadOnly(true);
		 
		 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 = '"+subGroupSequence+"'  and stmt.act_sequence IS NULL ";
		 
		 Query query=session.createSQLQuery(sqlQuery);		 
		 return query.list();
	}

	@Override
	public List<String> getDerivedStatementForProduct(Integer legalEntityCode, String groupSequence, String subGroupSequence, String activitySequence) {
		Session session = getSession();		    
		session.setDefaultReadOnly(true);
		 
		 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 = '"+subGroupSequence+"'  and stmt.act_sequence ='"+activitySequence+"' ";
		 
		 Query query=session.createSQLQuery(sqlQuery);		 
		 return query.list();
	}
}
