package com.bankaudit.dao;

import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Set;

import org.hibernate.ScrollMode;
import org.hibernate.ScrollableResults;
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 com.bankaudit.constants.BankAuditConstant;
import com.bankaudit.dto.DataTableResponse;
import com.bankaudit.dto.SequenceDto;
import com.bankaudit.model.MaintReportSequenceProcess;
import com.bankaudit.model.MaintReportSequenceProcessWrk;
import com.bankaudit.util.BankAuditUtil;

import java.util.ArrayList;

@Repository("maintReportSequenceProcessDao")
public class MaintReportSequenceProcessDaoImpl extends AbstractDao implements MaintReportSequenceProcessDao {

	/** The Constant logger is used to specify the . */
	static final Logger logger = Logger.getLogger(MaintReportSequenceProcessDaoImpl.class);

	@Override
	public List<SequenceDto> getMaintReportSequenceProcessMappingValues(String legalEntityCode, String auditTypeCode, String auditGroupCode,
			String auditSubGroupCode, String activityCode, String processCode, String findingCode, String status) {

		Session session = getSession();
		StringBuilder queryStr;
		Query query = null;
		if (!BankAuditUtil.isEmptyString(processCode)) {
			queryStr = new StringBuilder("SELECT  DISTINCT maa.finding_id, maa.finding_name, apfm.fnd_sequence"
					+ " FROM maint_audit_finding maa INNER JOIN maint_report_sequence_process apfm ON "
					+ " maa.legal_entity_code = apfm.legal_entity_code AND maa.finding_id = apfm.finding_code "
					+ " WHERE maa.legal_entity_code=:legalEntityCode AND apfm.audit_type_code=:auditTypeCode AND "
					+ "apfm.audit_group_code=:auditGroupCode AND apfm.audit_sub_group_code=:auditSubGroupCode AND "
					+ "apfm.activity_code=:activityId AND apfm.process_code=:processId");
			query = session.createNativeQuery(queryStr.toString()).setParameter("legalEntityCode", Integer.parseInt(legalEntityCode))
					.setParameter("auditTypeCode", auditTypeCode).setParameter("auditGroupCode", auditGroupCode)
					.setParameter("activityId", activityCode).setParameter("auditSubGroupCode", auditSubGroupCode)
					.setParameter("processId", processCode);
		} else if (!BankAuditUtil.isEmptyString(activityCode)) {
			queryStr = new StringBuilder("SELECT  DISTINCT maa.process_code,  maa.process_name, apfm.prc_sequence "
					+ "FROM maint_audit_process maa INNER JOIN maint_report_sequence_process apfm ON  "
					+ "maa.legal_entity_code = apfm.legal_entity_code AND maa.process_code = apfm.process_code WHERE "
					+ "maa.legal_entity_code=:legalEntityCode AND apfm.audit_type_code=:auditTypeCode AND "
					+ "apfm.audit_group_code=:auditGroupCode AND apfm.audit_sub_group_code=:auditSubGroupCode AND apfm.activity_code=:activityId");
			query = session.createNativeQuery(queryStr.toString()).setParameter("legalEntityCode", Integer.parseInt(legalEntityCode))
					.setParameter("auditTypeCode", auditTypeCode).setParameter("auditGroupCode", auditGroupCode)
					.setParameter("activityId", activityCode).setParameter("auditSubGroupCode", auditSubGroupCode);
		} else if (!BankAuditUtil.isEmptyString(auditSubGroupCode)) {
			queryStr = new StringBuilder("SELECT  DISTINCT maa.activity_id,  maa.activity_name, apfm.act_sequence  FROM maint_audit_activity maa  "
					+ "INNER JOIN maint_report_sequence_process apfm ON maa.activity_id=apfm.activity_code AND "
					+ "maa.legal_entity_code = apfm.legal_entity_code WHERE maa.legal_entity_code=:legalEntityCode AND "
					+ "apfm.audit_type_code=:auditTypeCode AND apfm.audit_group_code=:auditGroupCode AND apfm.audit_sub_group_code=:auditSubGroupCode");
			query = session.createNativeQuery(queryStr.toString()).setParameter("legalEntityCode", Integer.parseInt(legalEntityCode))
					.setParameter("auditTypeCode", auditTypeCode).setParameter("auditGroupCode", auditGroupCode)
					.setParameter("auditSubGroupCode", auditSubGroupCode);
		} else if (!BankAuditUtil.isEmptyString(auditGroupCode)) {
			queryStr = new StringBuilder("SELECT DISTINCT f.audit_sub_group_code, f.audit_sub_group_name, map.asg_sequence "
					+ "FROM maint_audit_subgroup f INNER JOIN maint_report_sequence_process map ON "
					+ "f.audit_group_code=map.audit_group_code AND f.audit_sub_group_code=map.audit_sub_group_code "
					+ "AND f.audit_type_code=map.audit_type_code AND f.legal_entity_code=map.legal_entity_code "
					+ " WHERE f.legal_entity_code =:legalEntityCode AND f.audit_type_code=:auditTypeCode AND "
					+ "f.audit_group_code=:auditGroupCode");
			query = session.createNativeQuery(queryStr.toString()).setParameter("legalEntityCode", Integer.parseInt(legalEntityCode))
					.setParameter("auditTypeCode", auditTypeCode).setParameter("auditGroupCode", auditGroupCode);
		} else {
			queryStr = new StringBuilder("SELECT DISTINCT f.audit_group_code,f.audit_group_name, map.ag_sequence FROM maint_audit_group f "
					+ "INNER JOIN maint_report_sequence_process map ON f.audit_Group_Code=map.audit_Group_Code  "
					+ "AND f.audit_Type_Code=map.audit_Type_Code AND f.legal_Entity_Code=map.legal_Entity_Code WHERE f.legal_Entity_Code =:legalEntityCode "
					+ "AND f.audit_Type_Code =:auditTypeCode");

			query = session.createNativeQuery(queryStr.toString()).setParameter("legalEntityCode", Integer.parseInt(legalEntityCode))
					.setParameter("auditTypeCode", auditTypeCode);
		}

		query.setResultTransformer(new ResultTransformer() {
			@Override
			public Object transformTuple(Object[] tuple, String[] aliases) {
				SequenceDto sequenceDto = null;
				if (tuple != null) {
					sequenceDto = new SequenceDto();
					if (tuple[1] != null) {
						sequenceDto.setCode((String) tuple[0]);
					}
					if (tuple[1] != null) {
						sequenceDto.setDiscription((String) tuple[1]);
					}
					if (tuple[2] != null) {
						sequenceDto.setSequenceNo((String)tuple[2]);
					}
				}
				return sequenceDto;
			}
			@Override
			public List transformList(List collection) {
				return collection;
			}
		});
		
		List<SequenceDto> list1 = query.list();
		List<SequenceDto> list2 = getActivityProcessFindingMappings(legalEntityCode, auditTypeCode, auditGroupCode, auditSubGroupCode, activityCode, processCode, findingCode, status);
		 Set<SequenceDto> result = new LinkedHashSet<SequenceDto>();
		 if(list1 != null)
			 result.addAll(list1);
		 if(list2 != null)
			 result.addAll(list2);
		return new ArrayList<SequenceDto>(result);
	}

	@SuppressWarnings("deprecation")
	@Override
	public DataTableResponse getMaintReportSequenceProcess(Integer legalEntityCode, String search, Integer orderColumn, String orderDirection,
			Integer page, Integer size, String auditTypeCode) {
		Session session = getSession();
		session.setDefaultReadOnly(true);
		List<MaintReportSequenceProcess> activityProcessFindingMappings = null;
		DataTableResponse dataTableResponse = new DataTableResponse();
		StringBuilder queryString = new StringBuilder("  SELECT legal_entity_code,mapping_id, "
				+ "(select concat(atd.audit_type_code , '-' , atd.audit_type_desc) from maint_audit_type_desc atd where "
				+ " atd.audit_type_code=apfm.audit_type_code and atd.legal_entity_code=apfm.legal_entity_code ) as audit_type_code , "
				+ "(select concat(ag.audit_group_code , '-' , ag.audit_group_name) from maint_audit_group ag where "
				+ " ag.audit_group_code=apfm.audit_group_code  and ag.audit_type_code=apfm.audit_type_code "
				+ " and ag.legal_entity_code=apfm.legal_entity_code ) as audit_group_code , "
				+ "(select concat(asg.audit_sub_group_code , '-' , asg.audit_sub_group_name) from maint_audit_subgroup asg where "
				+ " asg.audit_sub_group_code=apfm.audit_sub_group_code and asg.audit_group_code=apfm.audit_group_code "
				+ " and asg.audit_type_code=apfm.audit_type_code  and asg.legal_entity_code=apfm.legal_entity_code ) as audit_sub_group_code , "
				+ "(select concat(aa.activity_id , '-' , aa.activity_name) from maint_audit_activity aa where "
				+ " aa.activity_id=apfm.activity_code  and aa.legal_entity_code=apfm.legal_entity_code ) as activity_code , "
				+ "(select concat(ap.process_id , '-' , ap.process_name) from maint_audit_process ap where "
				+ " ap.process_id=apfm.process_code  and ap.legal_entity_code=apfm.legal_entity_code ) as process_code , "
				+ "(select concat(af.finding_id , '-' , af.finding_name , '-' , af.f_criticality)  from maint_audit_finding af where "
				+ " af.finding_id=apfm.finding_code  and af.legal_entity_code=apfm.legal_entity_code ) as finding_code , "
				+ "  auth_rej_remarks,  (select concat(gp.value , '-' , gp.description) from general_parameter gp where "
				+ " gp.key_1='ENTITY_STATUS' and gp.key_2='ENTITY_STATUS' and gp.value=apfm.entity_status "
				+ " and gp.legal_entity_code=apfm.legal_entity_code ) as entity_status , "
				+ " (select concat(gp.value , '-' , gp.description) from general_parameter gp where "
				+ " gp.key_1='STATUS' and gp.value=apfm.status  and gp.legal_entity_code=apfm.legal_entity_code ) as status , "
				+ " maker, maker_timestamp,   checker,   checker_timestamp,   businessdate_timestamp, "
				+ "  sysdate_timestamp,ag_sequence,asg_sequence,act_sequence,prc_sequence,fnd_sequence FROM  ( SELECT * FROM "
				+ "  maint_report_sequence_process UNION ALL SELECT *  FROM " + "  maint_report_sequence_process_wrk) AS apfm   WHERE "
				+ " ( apfm.legal_entity_code =:legal_entity_code AND apfm.audit_type_code =:auditTypeCode AND apfm.status != 'DF' AND apfm.fnd_sequence IS NOT NULL )"
				+ " OR (apfm.legal_entity_code =:legal_entity_code AND apfm.status ='DF' AND apfm.audit_type_code =:auditTypeCode AND "
				+ "( prc_sequence =1 OR act_sequence =1 OR asg_sequence =1 OR ag_sequence =1))"
				);

		if (!BankAuditUtil.isEmptyString(search)) {
			queryString = queryString.append(" HAVING audit_group_code LIKE :search OR audit_sub_group_code LIKE :search "
					+ " OR activity_code LIKE :search OR process_code LIKE :search OR  finding_code LIKE :search "
					+ " OR maker LIKE :search OR checker LIKE :search ");
		}
		String[] columns = {"apfm.audit_type_code", "apfm.audit_group_code", "apfm.audit_sub_group_code",
				" CAST(apfm.activity_code AS UNSIGNED), apfm.activity_code", "apfm.process_code", "apfm.finding_code", "apfm.maker",
				"apfm.entity_status", "apfm.status"};

		if (orderColumn != null && orderColumn == -1 && !BankAuditUtil.isEmptyString(orderDirection)) {
			queryString = queryString.append(" order by FIELD(STATUS,'DF','R','RM','U','M','A') ASC  ");
		} else if (orderColumn != null && !BankAuditUtil.isEmptyString(orderDirection)) {
			queryString = queryString.append(" order by ").append(columns[orderColumn]).append(" ").append(orderDirection);
		}

		Query query = session.createNativeQuery(queryString.toString()).setResultTransformer(new ResultTransformer() {
			@Override
			public Object transformTuple(Object[] tuple, String[] aliases) {
				MaintReportSequenceProcess activityProcessFindingMapping = null;
				if (tuple != null) {

					activityProcessFindingMapping = new MaintReportSequenceProcess();

					if (tuple[0] != null) {
						activityProcessFindingMapping.setLegalEntityCode(Integer.parseInt(tuple[0].toString()));
					}
					if (tuple[1] != null) {
						activityProcessFindingMapping.setMappingId(tuple[1].toString());
					}
					if (tuple[2] != null) {
						activityProcessFindingMapping.setAuditTypeCode(tuple[2].toString());
					}
					if (tuple[3] != null) {
						activityProcessFindingMapping.setAuditGroupCode(tuple[3].toString());
					}
					if (tuple[4] != null) {
						activityProcessFindingMapping.setAuditSubGroupCode(tuple[4].toString());
					}
					if (tuple[5] != null) {
						activityProcessFindingMapping.setActivityCode(tuple[5].toString());
					}
					if (tuple[6] != null) {
						activityProcessFindingMapping.setProcessCode(tuple[6].toString());
					}
					if (tuple[7] != null) {
						activityProcessFindingMapping.setFindingCode(tuple[7].toString());
					}
					if (tuple[8] != null) {
						activityProcessFindingMapping.setAuthRejRemarks(tuple[8].toString());
					}
					if (tuple[9] != null) {
						activityProcessFindingMapping.setEntityStatus(tuple[9].toString());
					}
					if (tuple[10] != null) {
						activityProcessFindingMapping.setStatus(tuple[10].toString());
					}
					if (tuple[11] != null) {
						activityProcessFindingMapping.setMaker(tuple[11].toString());
					}
					if (tuple[12] != null) {
						activityProcessFindingMapping.setMakerTimestamp((Date) tuple[12]);
					}
					if (tuple[13] != null) {
						activityProcessFindingMapping.setChecker(tuple[13].toString());
					}
					if (tuple[14] != null) {
						activityProcessFindingMapping.setCheckerTimestamp((Date) tuple[14]);
					}
					if (tuple[15] != null) {
						activityProcessFindingMapping.setBusinessdateTimestamp((Date) tuple[15]);
					}
					if (tuple[16] != null) {
						activityProcessFindingMapping.setSysdateTimestamp((Date) tuple[16]);
					}
					if (tuple[17] != null) {
						activityProcessFindingMapping.setAgSequence(tuple[17].toString());
					}
					if (tuple[18] != null) {
						activityProcessFindingMapping.setAsgSequence(tuple[18].toString());
					}
					if (tuple[19] != null) {
						activityProcessFindingMapping.setActSequence(tuple[19].toString());
					}
					if (tuple[20] != null) {
						activityProcessFindingMapping.setPrcSequence(tuple[20].toString());
					}
					if (tuple[21] != null) {
						activityProcessFindingMapping.setFndSequence(tuple[21].toString());
					}
				}
				return activityProcessFindingMapping;
			}
			@Override
			public List transformList(List collection) {
				return collection;
			}
		}).setParameter("legal_entity_code", legalEntityCode).setParameter("auditTypeCode", auditTypeCode);

		if(!BankAuditUtil.isEmptyString(search)){
			query.setParameter("search", "%"+search+"%");
		}
		
		ScrollableResults resultScroll = query.scroll(ScrollMode.SCROLL_INSENSITIVE);
		if (resultScroll.first() && resultScroll.scroll(page * size)) {
			activityProcessFindingMappings = new ArrayList<>();
			int i = 0;
			while (size > i++) {
				activityProcessFindingMappings.add((MaintReportSequenceProcess) resultScroll.get(0));
				if (!resultScroll.next())
					break;
			}
			dataTableResponse.setData(activityProcessFindingMappings);
			resultScroll.last();
			if (size != 0) {
				dataTableResponse.setRecordsFiltered((resultScroll.getRowNumber() + 1l / size) + 1);
				dataTableResponse.setRecordsTotal((resultScroll.getRowNumber() + 1l / size) + 1);
			} else {
				dataTableResponse.setError("page size zero");
				dataTableResponse.setData(Collections.EMPTY_LIST);
				dataTableResponse.setRecordsTotal(0l);
				dataTableResponse.setRecordsFiltered(0l);
			}
		} else {
			dataTableResponse.setError(null);
			dataTableResponse.setData(Collections.EMPTY_LIST);
			dataTableResponse.setRecordsTotal(0l);
			dataTableResponse.setRecordsFiltered(0l);
		}
		return dataTableResponse;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MaintReportSequenceProcess> getMaintReportSequenceProcessChapaterList(Integer legalEntityCode, String auditTypeCode) {
		Session session = getSession();
		return session
				.createQuery(" FROM MaintReportSequenceProcess u where u.legalEntityCode=:legalEntityCode AND u.auditTypeCode=:auditTypeCode"
						+ " And u.auditGroupCode IS NOT NULL AND u.auditSubGroupCode IS NULL  AND u.activityCode IS NULL "
						+ "AND u.processCode IS NULL AND u.findingCode IS NULL")
				.setParameter("legalEntityCode", legalEntityCode).setParameter("auditTypeCode", auditTypeCode).list();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MaintReportSequenceProcessWrk> getMaintReportSequenceProcessWrkChapaterList(Integer legalEntityCode, String auditTypeCode) {
		Session session = getSession();
		return session
				.createQuery(" FROM MaintReportSequenceProcessWrk u where u.legalEntityCode=:legalEntityCode AND u.auditTypeCode=:auditTypeCode"
						+ " And u.auditGroupCode IS NOT NULL AND u.auditSubGroupCode IS NULL  AND u.activityCode IS NULL "
						+ "AND u.processCode IS NULL AND u.findingCode IS NULL")
				.setParameter("legalEntityCode", legalEntityCode).setParameter("auditTypeCode", auditTypeCode).list();

	}

	@Override
	public List<MaintReportSequenceProcess> getMaintReportSequenceProcessSectionList(Integer legalEntityCode, String auditTypeCode,
			String auditGroupCode) {
		Session session = getSession();
		List<String> sectionList = getActivityProcessFindingMappingValues(legalEntityCode.toString(), auditTypeCode, null, null, null, null, null,
				"C");
		
		return session
				.createQuery(" FROM MaintReportSequenceProcess u where u.legalEntityCode=:legalEntityCode AND u.auditTypeCode=:auditTypeCode"
						+ " And u.auditGroupCode =:auditGroupCode AND u.auditSubGroupCode IS NOT NULL  AND u.activityCode IS NULL "
						+ "AND u.processCode IS NULL AND u.findingCode IS NULL")
				.setParameter("legalEntityCode", legalEntityCode).setParameter("auditTypeCode", auditTypeCode).setParameter("auditGroupCode", auditGroupCode).list();
	}

	@Override
	public List<MaintReportSequenceProcessWrk> getMaintReportSequenceProcessWrkSectionList(Integer legalEntityCode, String auditTypeCode,
			String auditGroupCode) {
		Session session = getSession();
		
		return session
				.createQuery(" FROM MaintReportSequenceProcessWrk u where u.legalEntityCode=:legalEntityCode AND u.auditTypeCode=:auditTypeCode"
						+ "  And u.auditGroupCode =:auditGroupCode AND u.auditSubGroupCode IS NOT NULL  AND u.activityCode IS NULL "
						+ "AND u.processCode IS NULL AND u.findingCode IS NULL")
				.setParameter("legalEntityCode", legalEntityCode).setParameter("auditTypeCode", auditTypeCode).setParameter("auditGroupCode", auditGroupCode).list();
	}

	@Override
	public List<MaintReportSequenceProcess> getMaintReportSequenceProcessProductList(Integer legalEntityCode, String auditTypeCode,
			String auditGroupCode, String auditSubGroupCode) {
		Session session = getSession();
		
		return session
				.createQuery(" FROM MaintReportSequenceProcess u where u.legalEntityCode=:legalEntityCode AND u.auditTypeCode=:auditTypeCode"
						+ "  And u.auditGroupCode =:auditGroupCode  AND u.auditSubGroupCode =:auditSubGroupCode  AND u.activityCode IS NOT NULL "
						+ "AND u.processCode IS NULL AND u.findingCode IS NULL")
				.setParameter("legalEntityCode", legalEntityCode).setParameter("auditTypeCode", auditTypeCode)
				.setParameter("auditGroupCode", auditGroupCode).setParameter("auditSubGroupCode", auditSubGroupCode).list();
	}

	@Override
	public List<MaintReportSequenceProcessWrk> getMaintReportSequenceProcessWrkProductList(Integer legalEntityCode, String auditTypeCode,
			String auditGroupCode, String auditSubGroupCode) {
		Session session = getSession();
				return session
				.createQuery(" FROM MaintReportSequenceProcessWrk u where u.legalEntityCode=:legalEntityCode AND u.auditTypeCode=:auditTypeCode"
						+ "  And u.auditGroupCode =:auditGroupCode  AND u.auditSubGroupCode =:auditSubGroupCode AND u.activityCode IS NOT NULL "
						+ "AND u.processCode IS NULL AND u.findingCode IS NULL")
				.setParameter("legalEntityCode", legalEntityCode).setParameter("auditTypeCode", auditTypeCode)
				.setParameter("auditGroupCode", auditGroupCode).setParameter("auditSubGroupCode", auditSubGroupCode).list();
	}

	@Override
	public List<MaintReportSequenceProcess> getMaintReportSequenceProcessList(Integer legalEntityCode, String auditTypeCode, String auditGroupCode,
			String auditSubGroupCode, String activityCode) {
		Session session = getSession();
		
		return session
				.createQuery(" FROM MaintReportSequenceProcess u where u.legalEntityCode=:legalEntityCode AND u.auditTypeCode=:auditTypeCode"
						+ "  And u.auditGroupCode =:auditGroupCode  AND u.auditSubGroupCode =:auditSubGroupCode  AND u.activityCode =:activityCode "
						+ "AND u.processCode IS NOT NULL AND u.findingCode IS NULL")
				.setParameter("legalEntityCode", legalEntityCode).setParameter("auditTypeCode", auditTypeCode)
				.setParameter("auditGroupCode", auditGroupCode).setParameter("auditSubGroupCode", auditSubGroupCode).setParameter("activityCode", activityCode).list();
	}

	@Override
	public List<MaintReportSequenceProcessWrk> getMaintReportSequenceProcessWrkList(Integer legalEntityCode, String auditTypeCode,
			String auditGroupCode, String auditSubGroupCode, String activityCode) {
		Session session = getSession();
		
		return session
				.createQuery(" FROM MaintReportSequenceProcessWrk u where u.legalEntityCode=:legalEntityCode AND u.auditTypeCode=:auditTypeCode"
						+ "  And u.auditGroupCode =:auditGroupCode  AND u.auditSubGroupCode =:auditSubGroupCode  AND u.activityCode =:activityCode"
						+ " AND u.processCode IS NOT NULL AND u.findingCode IS NULL")
				.setParameter("legalEntityCode", legalEntityCode).setParameter("auditTypeCode", auditTypeCode)
				.setParameter("auditGroupCode", auditGroupCode).setParameter("auditSubGroupCode", auditSubGroupCode).setParameter("activityCode", activityCode).list();
	}

	@Override
	public List<MaintReportSequenceProcess> getMaintReportSequenceProcessChekListList(Integer legalEntityCode, String auditTypeCode,
			String auditGroupCode, String auditSubGroupCode, String activityCode, String processCode) {
		Session session = getSession();
	
		return session
				.createQuery(" FROM MaintReportSequenceProcess u where u.legalEntityCode=:legalEntityCode AND u.auditTypeCode=:auditTypeCode"
						+ "  And u.auditGroupCode =:auditGroupCode  AND u.auditSubGroupCode =:auditSubGroupCode  AND u.activityCode =:activityCode "
						+ "AND u.processCode =:processCode AND u.findingCode IS NOT NULL")
				.setParameter("legalEntityCode", legalEntityCode).setParameter("auditTypeCode", auditTypeCode)
				.setParameter("auditGroupCode", auditGroupCode).setParameter("auditSubGroupCode", auditSubGroupCode)
				.setParameter("activityCode", activityCode).setParameter("processCode", processCode).list();
	}

	@Override
	public List<MaintReportSequenceProcessWrk> getMaintReportSequenceProcessWrkChekListList(Integer legalEntityCode, String auditTypeCode,
			String auditGroupCode, String auditSubGroupCode, String activityCode, String processCode) {
		Session session = getSession();
	
		return session
				.createQuery(" FROM MaintReportSequenceProcessWrk u where u.legalEntityCode=:legalEntityCode AND u.auditTypeCode=:auditTypeCode"
						+ "  And u.auditGroupCode =:auditGroupCode  AND u.auditSubGroupCode =:auditSubGroupCode  AND u.activityCode =:activityCode "
						+ "AND u.processCode =:processCode AND u.findingCode IS NOT NULL")
				.setParameter("legalEntityCode", legalEntityCode).setParameter("auditTypeCode", auditTypeCode)
				.setParameter("auditGroupCode", auditGroupCode).setParameter("auditSubGroupCode", auditSubGroupCode)
				.setParameter("activityCode", activityCode).setParameter("processCode", processCode).list();
	}

	@Override
	public List<SequenceDto> getMaintReportSequenceProcessWrkMappingValues(String legalEntityCode, String auditTypeCode, String auditGroupCode,
			String auditSubGroupCode, String activityCode, String processCode, String findingCode, String status) {

		Session session = getSession();
		StringBuilder queryStr;
		Query query = null;
		if (!BankAuditUtil.isEmptyString(processCode)) {
			queryStr = new StringBuilder("SELECT  DISTINCT maa.finding_id, maa.finding_name, apfm.fnd_sequence"
					+ " FROM maint_audit_finding maa INNER JOIN maint_report_sequence_process_wrk apfm ON "
					+ " maa.legal_entity_code = apfm.legal_entity_code AND maa.finding_id = apfm.finding_code "
					+ " WHERE maa.legal_entity_code=:legalEntityCode AND apfm.audit_type_code=:auditTypeCode AND "
					+ "apfm.audit_group_code=:auditGroupCode AND apfm.audit_sub_group_code=:auditSubGroupCode AND "
					+ "apfm.activity_code=:activityId AND apfm.process_code=:processId");
			query = session.createNativeQuery(queryStr.toString()).setParameter("legalEntityCode", Integer.parseInt(legalEntityCode))
					.setParameter("auditTypeCode", auditTypeCode).setParameter("auditGroupCode", auditGroupCode)
					.setParameter("activityId", activityCode).setParameter("auditSubGroupCode", auditSubGroupCode)
					.setParameter("processId", processCode);
		} else if (!BankAuditUtil.isEmptyString(activityCode)) {
			queryStr = new StringBuilder("SELECT  DISTINCT maa.process_code,  maa.process_name, apfm.prc_sequence "
					+ "FROM maint_audit_process maa INNER JOIN maint_report_sequence_process_wrk apfm ON  "
					+ "maa.legal_entity_code = apfm.legal_entity_code AND maa.process_code = apfm.process_code WHERE "
					+ "maa.legal_entity_code=:legalEntityCode AND apfm.audit_type_code=:auditTypeCode AND "
					+ "apfm.audit_group_code=:auditGroupCode AND apfm.audit_sub_group_code=:auditSubGroupCode AND apfm.activity_code=:activityId");
			query = session.createNativeQuery(queryStr.toString()).setParameter("legalEntityCode", Integer.parseInt(legalEntityCode))
					.setParameter("auditTypeCode", auditTypeCode).setParameter("auditGroupCode", auditGroupCode)
					.setParameter("activityId", activityCode).setParameter("auditSubGroupCode", auditSubGroupCode);
		} else if (!BankAuditUtil.isEmptyString(auditSubGroupCode)) {
			queryStr = new StringBuilder("SELECT  DISTINCT maa.activity_id,  maa.activity_name, apfm.act_sequence  FROM maint_audit_activity maa  "
					+ "INNER JOIN maint_report_sequence_process_wrk apfm ON maa.activity_id=apfm.activity_code AND "
					+ "maa.legal_entity_code = apfm.legal_entity_code WHERE maa.legal_entity_code=:legalEntityCode AND "
					+ "apfm.audit_type_code=:auditTypeCode AND apfm.audit_group_code=:auditGroupCode AND apfm.audit_sub_group_code=:auditSubGroupCode");
			query = session.createNativeQuery(queryStr.toString()).setParameter("legalEntityCode", Integer.parseInt(legalEntityCode))
					.setParameter("auditTypeCode", auditTypeCode).setParameter("auditGroupCode", auditGroupCode)
					.setParameter("auditSubGroupCode", auditSubGroupCode);
		} else if (!BankAuditUtil.isEmptyString(auditGroupCode)) {
			queryStr = new StringBuilder("SELECT DISTINCT f.audit_sub_group_code, f.audit_sub_group_name, map.asg_sequence "
					+ "FROM maint_audit_subgroup f INNER JOIN maint_report_sequence_process_wrk map ON "
					+ "f.audit_group_code=map.audit_group_code AND f.audit_sub_group_code=map.audit_sub_group_code "
					+ "AND f.audit_type_code=map.audit_type_code AND f.legal_entity_code=map.legal_entity_code "
					+ " WHERE f.legal_entity_code =:legalEntityCode AND f.audit_type_code=:auditTypeCode AND "
					+ "f.audit_group_code=:auditGroupCode");
			query = session.createNativeQuery(queryStr.toString()).setParameter("legalEntityCode", Integer.parseInt(legalEntityCode))
					.setParameter("auditTypeCode", auditTypeCode).setParameter("auditGroupCode", auditGroupCode);
		} else {
			queryStr = new StringBuilder("SELECT DISTINCT f.audit_group_code,f.audit_group_name, map.ag_sequence FROM maint_audit_group f "
					+ "INNER JOIN maint_report_sequence_process_wrk map ON f.audit_Group_Code=map.audit_Group_Code  "
					+ "AND f.audit_Type_Code=map.audit_Type_Code AND f.legal_Entity_Code=map.legal_Entity_Code WHERE f.legal_Entity_Code =:legalEntityCode "
					+ "AND f.audit_Type_Code =:auditTypeCode");

			query = session.createNativeQuery(queryStr.toString()).setParameter("legalEntityCode", Integer.parseInt(legalEntityCode))
					.setParameter("auditTypeCode", auditTypeCode);
		}

		query.setResultTransformer(new ResultTransformer() {
			@Override
			public Object transformTuple(Object[] tuple, String[] aliases) {
				SequenceDto sequenceDto = null;
				if (tuple != null) {
					sequenceDto = new SequenceDto();
					if (tuple[1] != null) {
						sequenceDto.setCode((String) tuple[0]);
					}
					if (tuple[1] != null) {
						sequenceDto.setDiscription((String) tuple[1]);
					}
					if (tuple[2] != null) {
						sequenceDto.setSequenceNo((String)tuple[2]);
					}
				}
				return sequenceDto;
			}
			@Override
			public List transformList(List collection) {
				return collection;
			}
		});
		return query.list();
		
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MaintReportSequenceProcessWrk> getRejectedMaintReportSequenceProcessByAuditGroupCode(Integer legalEntityCode, String auditTypeCode,
			String auditGroupCode) {
		Session session = getSession();
		logger.info("Inside MaintReportSequenceProcess .. " + legalEntityCode + ":::" + auditTypeCode + "" + auditGroupCode);
		return session
				.createQuery(" from MaintReportSequenceProcessWrk m " + " where m.legalEntityCode =:legalEntityCode "
						+ " and m.auditTypeCode=:auditTypeCode and m.status='" + BankAuditConstant.STATUS_REJ + "'")
				.setParameter("legalEntityCode", legalEntityCode).setParameter("auditTypeCode", auditTypeCode).list();
	}

	@Override
	public void deleteRejectedMaintReportSequenceProcessByAuditGroupCode(Integer legalEntityCode, String auditTypeCode, String auditGroupCode) {
		Session session = getSession();
		logger.info("Inside MaintReportSequenceProcess .. " + legalEntityCode + ":::" + auditTypeCode + "" + auditGroupCode);
		session.createQuery("delete from MaintReportSequenceProcessWrk m " + " where m.legalEntityCode =:legalEntityCode "
				+ " and m.auditTypeCode=:auditTypeCode and m.status='" + BankAuditConstant.STATUS_REJ + "'")
				.setParameter("legalEntityCode", legalEntityCode).setParameter("auditTypeCode", auditTypeCode).executeUpdate();
	}

	@Override
	public List<String> getActivityProcessFindingMappingValues(String legalEntityCode, String auditTypeCode, String auditGroupCode,
			String auditSubGroupCode, String activityId, String processId, String findingId, String createOrUpdate) {

		Session session = getSession();

		StringBuilder queryStr = new StringBuilder();
		queryStr.append(" From ActivityProcessFindingMapping map  " + " where map.legalEntityCode =:legalEntityCode "
				+ " and   map.auditTypeCode =:auditTypeCode ");

		if (createOrUpdate.equalsIgnoreCase("C")) {
			queryStr.append(" and  map.entityStatus ='" + BankAuditConstant.STATUS_ACTIVE + "'  ");
		}

		if (!BankAuditUtil.isEmptyString(findingId)) {
			queryStr.insert(0, " select map.mappingId  ");
			queryStr.append(" and map.findingId =:findingId " + " and map.processId =:processId " + " and map.activityId =:activityId "
					+ " and map.auditSubGroupCode =:auditSubGroupCode " + " and map.auditGroupCode =:auditGroupCode " + " ");

		} else if (!BankAuditUtil.isEmptyString(processId)) {

			queryStr.insert(0, " select distinct  " + "( select distinct  f.findingId" + " from  MaintAuditFinding f where "
			// + " f.entityStatus= '"+BankAuditConstant.STATUS_ACTIVE+"' and"
					+ " f.findingId=map.findingId  " + " and f.legalEntityCode=map.legalEntityCode ) as findingId ");

			queryStr.append(" " + " and map.processId =:processId " + " and map.activityId =:activityId "
					+ " and map.auditSubGroupCode =:auditSubGroupCode " + " and map.auditGroupCode =:auditGroupCode " + " ");

		} else if (!BankAuditUtil.isEmptyString(activityId)) {

			queryStr.insert(0, " select distinct " + "( select distinct f.processId   " + " from  MaintAuditProcess f where "
			// + " f.entityStatus= '"+BankAuditConstant.STATUS_ACTIVE+"' and"
					+ "  f.processId=map.processId  " + " and f.legalEntityCode=map.legalEntityCode ) as processId ");

			queryStr.append(" " + " and map.activityId =:activityId " + " and map.auditSubGroupCode =:auditSubGroupCode "
					+ " and map.auditGroupCode =:auditGroupCode " + " ");

		} else if (!BankAuditUtil.isEmptyString(auditSubGroupCode)) {

			queryStr.insert(0, " select distinct " + "( select distinct   f.activityId " + " from  MaintAuditActivity f where "
			// + " f.entityStatus= '"+BankAuditConstant.STATUS_ACTIVE+"' and "
					+ " f.activityId=map.activityId " + " and f.legalEntityCode=map.legalEntityCode ) as activityId ");

			queryStr.append(" " + " and map.auditSubGroupCode =:auditSubGroupCode " + " and map.auditGroupCode =:auditGroupCode " + " ");
		} else if (!BankAuditUtil.isEmptyString(auditGroupCode)) {

			queryStr.insert(0, " select distinct  " + "( select distinct f.auditSubGroupCode" + " from  MaintAuditSubgroup f where "
			// + " f.entityStatus= '"+BankAuditConstant.STATUS_ACTIVE+"' and "
					+ " f.auditSubGroupCode=map.auditSubGroupCode  " + " and f.auditGroupCode=map.auditGroupCode  "
					+ " and f.auditTypeCode=map.auditTypeCode  " + " and f.legalEntityCode=map.legalEntityCode ) as auditSubGroupCode ");

			queryStr.append(" " + " and map.auditGroupCode =:auditGroupCode " + " ");
		} else {
			queryStr.insert(0,
					" select distinct  " + "( select distinct  f.auditGroupCode" + " from  MaintAuditGroup f where "
							+ "  f.auditGroupCode=map.auditGroupCode  " + " and f.auditTypeCode=map.auditTypeCode  "
							+ " and f.legalEntityCode=map.legalEntityCode ) as auditGroupCode ");

		}

		Query query = session.createQuery(queryStr.toString()).setParameter("legalEntityCode", Integer.parseInt(legalEntityCode))
				.setParameter("auditTypeCode", auditTypeCode);

		if (!BankAuditUtil.isEmptyString(auditGroupCode)) {
			query.setParameter("auditGroupCode", auditGroupCode);
		}

		if (!BankAuditUtil.isEmptyString(auditSubGroupCode)) {
			query.setParameter("auditSubGroupCode", auditSubGroupCode);
		}

		if (!BankAuditUtil.isEmptyString(activityId)) {
			query.setParameter("activityId", activityId);
		}

		if (!BankAuditUtil.isEmptyString(processId)) {
			query.setParameter("processId", processId);
		}

		if (!BankAuditUtil.isEmptyString(findingId)) {
			query.setParameter("findingId", findingId);
		}
		return query.list();
	}

	@Override
	public List<MaintReportSequenceProcessWrk> getDraftMaintReportSequenceProcess(Integer legalEntityCode, String auditTypeCode, String status) {
		Session session = getSession();
		return session
				.createQuery(" FROM MaintReportSequenceProcessWrk u where u.legalEntityCode=:legalEntityCode AND u.auditTypeCode=:auditTypeCode"
						+ "  And u.status = '" + status + "'")
				.setParameter("legalEntityCode", legalEntityCode).setParameter("auditTypeCode", auditTypeCode).list();
	}

	@Override
	public List<SequenceDto> getActivityProcessFindingMappings(String legalEntityCode, String auditTypeCode, String auditGroupCode,
			String auditSubGroupCode, String activityCode, String processCode, String findingCode, String status) {
		
		Session session=getSession();
		
		StringBuilder queryStr=new StringBuilder();
		queryStr.append(" From ActivityProcessFindingMapping map  "
				+ " where map.legalEntityCode =:legalEntityCode "
				+ " and   map.auditTypeCode =:auditTypeCode ");
		
			queryStr.append(" and  map.entityStatus ='"+BankAuditConstant.STATUS_ACTIVE+"'  ");
		
		if(!BankAuditUtil.isEmptyString(findingCode)){
			queryStr.insert( 0," select map.mappingId  ");
			queryStr.append(" and map.findingId =:findingId "
					+ " and map.processId =:processId "
					+ " and map.activityId =:activityId "
					+ " and map.auditSubGroupCode =:auditSubGroupCode "
					+ " and map.auditGroupCode =:auditGroupCode "
					+ " ");
			
		}else if (!BankAuditUtil.isEmptyString(processCode)) {
			
			queryStr.insert(0," select distinct  "
					+ "( select distinct  concat(f.findingName , '-' , f.findingId  , '-' ,f.fCriticality )  "
            	    + " from  MaintAuditFinding f where "
            	   // + " f.entityStatus= '"+BankAuditConstant.STATUS_ACTIVE+"' and"
            	    + " f.findingId=map.findingId  "
	                + " and f.legalEntityCode=map.legalEntityCode ) as findingId ");
			
			queryStr.append(" "
					+ " and map.processId =:processId "
					+ " and map.activityId =:activityId "
					+ " and map.auditSubGroupCode =:auditSubGroupCode "
					+ " and map.auditGroupCode =:auditGroupCode "
					+ " ");
			
		}else if (!BankAuditUtil.isEmptyString(activityCode)) {
			
			queryStr.insert(0," select distinct "
					+ "( select distinct  concat( f.processName , '-' , f.processId )  "
            	    + " from  MaintAuditProcess f where "
            	   // + " f.entityStatus= '"+BankAuditConstant.STATUS_ACTIVE+"' and"
            	    + "  f.processId=map.processId  "
            	    + " and f.legalEntityCode=map.legalEntityCode ) as processId ");
			
			queryStr.append(" "
					+ " and map.activityId =:activityId "
					+ " and map.auditSubGroupCode =:auditSubGroupCode "
					+ " and map.auditGroupCode =:auditGroupCode "
					+ " ");
			
		}else if (!BankAuditUtil.isEmptyString(auditSubGroupCode)) {
		
			queryStr.insert(0," select distinct "
					+ "( select distinct  concat( f.activityName , '-' , f.activityId )  "
            	    + " from  MaintAuditActivity f where "
            	  //  + " f.entityStatus= '"+BankAuditConstant.STATUS_ACTIVE+"' and "
            	    + " f.activityId=map.activityId "
            	    + " and f.legalEntityCode=map.legalEntityCode ) as activityId ");
			
			queryStr.append(" "
					+ " and map.auditSubGroupCode =:auditSubGroupCode "
					+ " and map.auditGroupCode =:auditGroupCode "
					+ " ");
		}
		else if (!BankAuditUtil.isEmptyString(auditGroupCode)) {
		
			queryStr.insert(0," select distinct  "
					+ "( select distinct  concat( f.auditSubGroupName , '-' , f.auditSubGroupCode ) "
            	    + " from  MaintAuditSubgroup f where "
            	   // + " f.entityStatus= '"+BankAuditConstant.STATUS_ACTIVE+"' and "
            	    + " f.auditSubGroupCode=map.auditSubGroupCode  "
            	    + " and f.auditGroupCode=map.auditGroupCode  "
            	    + " and f.auditTypeCode=map.auditTypeCode  "
            	    + " and f.legalEntityCode=map.legalEntityCode ) as auditSubGroupCode ");
			
			queryStr.append(" "
					+ " and map.auditGroupCode =:auditGroupCode "
					+ " ");
		}
		else {
			queryStr.insert(0," select distinct  "
					+ "( select distinct  concat( f.auditGroupName   ,'-' , f.auditGroupCode ) "
            	    + " from  MaintAuditGroup f where "
            	    + "  f.auditGroupCode=map.auditGroupCode  "
            	    + " and f.auditTypeCode=map.auditTypeCode  "
            	    + " and f.legalEntityCode=map.legalEntityCode ) as auditGroupCode ");
			
		}
		
		Query query = session.createQuery(queryStr.toString())
				.setParameter("legalEntityCode", Integer.parseInt(legalEntityCode))
				.setParameter("auditTypeCode", auditTypeCode);
		
		if(!BankAuditUtil.isEmptyString( auditGroupCode)){
			query.setParameter("auditGroupCode", auditGroupCode);
		}
		
		if(!BankAuditUtil.isEmptyString( auditSubGroupCode)){
			query.setParameter("auditSubGroupCode", auditSubGroupCode);
		}
		
		if(!BankAuditUtil.isEmptyString( activityCode)){
			query.setParameter("activityId", activityCode);
		}
		
		if(!BankAuditUtil.isEmptyString( processCode)){
			query.setParameter("processId", processCode);
		}
		
		if(!BankAuditUtil.isEmptyString( findingCode)){
			query.setParameter("findingId", findingCode);
		}
		
		List<String> list = query.list();
		List<SequenceDto> result = new ArrayList();
		int sequ = 1;
		for (String str : list) {
			SequenceDto s = new SequenceDto();
			s.setDiscription(str.substring(0, str.lastIndexOf('-')));
			s.setCode(str.substring(str.lastIndexOf('-') + 1));
			s.setSequenceNo(sequ++ +"");
			result.add(s);
		}
		return result;
	}
	
	@Override
	public List<MaintReportSequenceProcessWrk> getMaintReportSequenceProcessWrkList(Integer legalEntityCode, String auditTypeCode) {
		Session session = getSession();
		return session
				.createQuery(" FROM MaintReportSequenceProcessWrk u where u.legalEntityCode=:legalEntityCode AND u.auditTypeCode=:auditTypeCode")
				.setParameter("legalEntityCode", legalEntityCode).setParameter("auditTypeCode", auditTypeCode).list();
	}
	
	@Override
	public List<MaintReportSequenceProcess> getMaintReportSequenceProcessList(Integer legalEntityCode, String auditTypeCode) {
		Session session = getSession();
		return session
				.createQuery(" FROM MaintReportSequenceProcess u where u.legalEntityCode=:legalEntityCode AND u.auditTypeCode=:auditTypeCode")
				.setParameter("legalEntityCode", legalEntityCode).setParameter("auditTypeCode", auditTypeCode).list();
	}
	
	
	@Override
	public void deleteMaintReportSequenceProcess(Integer legalEntityCode, String auditTypeCode) {
		Session session = getSession();
		logger.info("Inside MaintReportSequenceProcess .. " + legalEntityCode + ":::" + auditTypeCode );
		session.createQuery("delete from MaintReportSequenceProcess m " + " where m.legalEntityCode =:legalEntityCode "
				+ " and m.auditTypeCode=:auditTypeCode ")
				.setParameter("legalEntityCode", legalEntityCode).setParameter("auditTypeCode", auditTypeCode).executeUpdate();
	}
	
	@Override
	public void deleteMaintReportSequenceProcessWrk(Integer legalEntityCode, String auditTypeCode) {
		Session session = getSession();
		logger.info("Inside MaintReportSequenceProcess .. " + legalEntityCode + ":::" + auditTypeCode );
		session.createQuery("delete from MaintReportSequenceProcessWrk m " + " where m.legalEntityCode =:legalEntityCode "
				+ " and m.auditTypeCode=:auditTypeCode ")
				.setParameter("legalEntityCode", legalEntityCode).setParameter("auditTypeCode", auditTypeCode).executeUpdate();
	}
	
}
