package com.bankaudit.rest.process.dao;

import java.time.LocalDate;
import java.util.List;

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

import com.bankaudit.constants.BankAuditConstant;
import com.bankaudit.dao.AbstractDao;
import com.bankaudit.dto.DataTableResponse;
import com.bankaudit.dto.process.AuditReportDynamicTableDto;
import com.bankaudit.dto.process.AuditReportDynamicTablePredefinedDataListDto;
import com.bankaudit.process.model.AuditLevelDocumentDetails;
import com.bankaudit.process.model.AuditReportDynamicTable;
import com.bankaudit.process.model.AuditReportStmt;
import com.bankaudit.process.model.AuditSchedule;
import com.bankaudit.util.BankAuditUtil;

@Repository
public class AuditReportDynamicTableDaoImpl extends AbstractDao implements AuditReportDynamicTableDao{

	static final Logger logger = Logger.getLogger(AuditReportDynamicTableDaoImpl.class);

	@SuppressWarnings("unchecked")
	@Override
	public DataTableResponse getDynamicTables(int legalEntityCode,String userId, String role, String search, Integer orderColumn,String orderDirection, Integer page, Integer size,
			LocalDate financialYear, String auditTypeCode, List<LocalDate>  financialYearStrtEndList) { // Added to filter the records based on Financial Year and AuditType) {

		Session session = getSession();
		session.setDefaultReadOnly(true);

		logger.info("Inside getDynamicTables ..## "+ legalEntityCode +" :: "+ userId +" :: "+role);
		DataTableResponse dataTableResponse=new DataTableResponse();
		try {
		StringBuilder queryString=new StringBuilder("select ard as ardTable, "// 0
				 + " sch as auditSchedule, "
				 + "   (select concat(auditTypeCode , '-' , auditTypeDesc) from MaintAuditTypeDesc at where at.auditTypeCode=ard.auditTypeCode and at.legalEntityCode= ard.legalEntityCode ) as auditTypeCodeTmp, "  // 2
				 //+ "   (Select concat(unitCode , '--' , unitName) from  MaintEntity e where e.unitCode =ard.unitId and e.legalEntityCode =ard.legalEntityCode ) as unitIdTmp,  "  // 3
				 + "   (Select unitName from  MaintEntity e where e.unitCode =ard.unitId and e.legalEntityCode =ard.legalEntityCode ) as unitName,  "  // 3
				 + "   (Select concat(sch.auditStatus , '-' , g.description) from  GeneralParameter g where g.legalEntityCode =sch.legalEntityCode and g.key1='STATUS' and g.key2='AUDIT_STATUS' and g.value=sch.auditStatus) as auditStatusDescTmp, " // 4
				 + "   (Select concat(u.userId , '-' , ifnull(u.firstName,''), ' ',ifnull(u.middleName,''),' ',ifnull(u.lastName,'') )  from  User u where u.legalEntityCode =ard.legalEntityCode and u.userId=ard.maker) as makerTmp, "  // 5
				 + "   (Select concat(g.value , '-' , ifnull(g.description,''))  from  GeneralParameter g where g.legalEntityCode =ard.legalEntityCode and "
				 + "   g.value=ard.statementType and g.key1 = 'AUDIT_DYNAMIC_DATA' and g.key2 = 'ENRICHMENT_TYPE_OR_OPTIONS' ) as stmtType, "  // 6

				 + "   ( Select distinct concat(grp.auditGroupCode,'-',grp.auditGroupName) "
			   		+ "  from  MaintAuditGroup grp  where  grp.auditGroupCode = ard.auditGroupCode "
			   		+ "  and grp.auditTypeCode=ard.auditTypeCode  and grp.legalEntityCode=ard.legalEntityCode"
			   		+ "  and grp.status = 'A' and grp.entityStatus = 'A') as chapter, " //7

			   	 + "  ( Select distinct concat(subgrp.auditSubGroupCode,'-',subgrp.auditSubGroupName)  "
					+ "  from MaintAuditSubgroup subgrp  where subgrp.auditSubGroupCode = ard.auditSubGroupCode"
					+ "  and subgrp.auditTypeCode = ard.auditTypeCode and subgrp.auditGroupCode = ard.auditGroupCode "
					+ "  and subgrp.legalEntityCode = ard.legalEntityCode "
					+ "  and subgrp.status = 'A' and subgrp.entityStatus = 'A' ) as section, "				//8

				 + "  ( Select distinct concat(act.activityCode,'-',act.activityName)  "
					+ "  from MaintAuditActivity act where act.activityCode = ard.activityCode "
					+ "  and act.legalEntityCode = ard.legalEntityCode "
					+ "  and act.status = 'A' and act.entityStatus = 'A' ) as product , "		//9

				 + " ( Select  distinct concat(proc.processCode , '-', proc.processName) "
					+ "  from MaintAuditProcess proc  where  proc.processCode = ard.processCode "
					+ "  and proc.legalEntityCode = ard.legalEntityCode"
					+ "  and proc.status = 'A' and proc.entityStatus = 'A') as process,  " //10
					
				 + " ( Select  distinct  doc as docDetails  "
					+ "  from AuditLevelDocumentDetails doc  where  doc.auditId = ard.auditId  and doc.legalEntityCode = ard.legalEntityCode "
					+ "  and doc.auditTypeCode = ard.auditTypeCode  and doc.id = ard.docId  and doc.uploadStatus = 'S'  ) as docDetails  "  //11
					
				 + " from AuditReportDynamicTable ard , AuditSchedule sch , AuditTeam at " 
				 + " where ard.legalEntityCode=sch.legalEntityCode and sch.legalEntityCode=at.legalEntityCode  "
				 + "     and sch.auditId=ard.auditId and sch.auditId=at.auditId and ard.auditId=at.auditId  "
				 + " 	 and IFNULL(sch.auditStatus,'NotNull') not in ('AD','AU','DRP','UP','AP') "
				 + "     and ard.legalEntityCode =:legalEntityCode and at.userId=:userId ");

		// Financial Year and Audit Type filter added for filtering
		if(!BankAuditUtil.isEmptyString(auditTypeCode)) {
			queryString.append(" AND  sch.auditTypeCode =:auditTypeCode "); 
		}if(financialYear !=null) {
			LocalDate fYStartDt = financialYearStrtEndList.get(0);
			LocalDate fYearEndDt = financialYearStrtEndList.get(1);
			logger.info("fYStartDt :: "+fYStartDt+" fYEndDt :: "+fYearEndDt);
			queryString.append(" AND (sch.scheduleStartDate between '"+fYStartDt+"' AND '"+fYearEndDt+"' )");
			//queryString.append(" And (sch.scheduleStartDate between :fYStartDt and :fYearEndDt )");
		}		
		
		logger.info("Before the qry....");

		if(!BankAuditUtil.isEmptyString(search)){
			queryString=queryString.append(" and ( "
					+ " ard.auditTypeCode IN (SELECT a.auditTypeCode FROM MaintAuditTypeDesc a  WHERE a.legalEntityCode=ard.legalEntityCode AND (a.auditTypeDesc LIKE :search OR a.auditTypeCode LIKE :search)) "
					+ " or ard.planId like :search "
					+ " or ard.auditId like :search "
					+ " or ard.unitId like :search "
					+ " or ard.unitId IN (SELECT m.unitCode FROM MaintEntity m WHERE m.legalEntityCode=ard.legalEntityCode AND (m.unitName LIKE :search or m.unitCode LIKE :search )) "
					+ " or ard.maker IN (SELECT u.userId FROM User u WHERE u.legalEntityCode =ard.legalEntityCode and (u.userId Like :search or u.firstName LIKE :search or u.lastName Like :search) ) "
					//+ " or sch.auditStatus IN (SELECT g.value FROM GeneralParameter g WHERE sch.legalEntityCode =g.legalEntityCode and g.key1='STATUS' and g.key2='AUDIT_STATUS' and g.value=sch.auditStatus and g.description LIKE :search) "
					//+ " or sch.auditId IN (select t.auditId from AuditTeam t, User u where t.legalEntityCode=sch.legalEntityCode AND t.auditId=sch.auditId AND t.userType='M' AND t.legalEntityCode=u.legalEntityCode AND t.userId=u.userId "
					//+ "						AND sch.unitId=u.unitCode AND (u.firstName like :search OR u.middleName like :search OR u.lastName like :search OR u.userId like :search )) "


//					+ " or ard.tableId like :search "
					+ " or ard.statementType like :search "
//					+ " or ard.tableName like :search "
					+ " or ard.sequence like :search "
					+ " or sch.auditStatus IN (SELECT g.value FROM GeneralParameter g WHERE sch.legalEntityCode =g.legalEntityCode and g.key1='STATUS' and g.key2='AUDIT_STATUS' and g.value=sch.auditStatus and g.description LIKE :search) "

					+ " or sch.auditStatus IN (SELECT g.value FROM GeneralParameter g WHERE sch.legalEntityCode =g.legalEntityCode and g.key1='STATUS' "
					+ " and g.key2='AUDIT_STATUS' and g.value=sch.auditStatus "
					+ " and (g.description LIKE :search or g.value LIKE :search)"
					+ " and   ( sch.observationStatus !='AE'  ) "
					+ " ) "

					+ " or sch.auditStatus IN (SELECT g.value FROM GeneralParameter g WHERE sch.legalEntityCode =g.legalEntityCode and g.key1='STATUS' "
					+ " and g.key2='AUDIT_STATUS' and g.value=sch.auditStatus "
					+ " and (g.description LIKE :search or g.value LIKE :search)"
					+ " and   ( sch.observationStatus  ='AE' and sch.auditStatus='AW'  ) "
					+ " ) "

					//+ " ) "
					);
					if((search.length()==2 && search.toUpperCase().startsWith("EX") )
						|| (search.length()==3 && search.toUpperCase().startsWith("EXI"))
						|| (search.length()==4 && search.toUpperCase().startsWith("EXIT"))
						|| ((search.length()==5 || search.length()==6) && search.toUpperCase().startsWith("EXITE")) ){
							queryString.append("Or (sch.auditStatus='AI' and sch.observationStatus='AE' ) )");
					}	else{
						queryString.append(" )" );
						if(search.toUpperCase().startsWith("INI")){
							queryString.append("and  ( sch.auditStatus='AI' and (sch.observationStatus is null or sch.observationStatus='' ) )");
						}
					}
		}

		/*queryString=queryString.append("  ORDER BY FIELD(auditStatus, 'AI','P','AP','AS','AC','AU') asc ");*/
			String[] columns={
					"ard.auditTypeCode",
					"(ard.auditId *1 ), (ard.planId *1 )", //"CAST(planId AS Integer),CAST(auditId AS Integer) "
					"(ard.unitId * 1) ",  //"unitId",
					"ard.statementType",
					"ard.sequence",
					"sch.auditStatus",  // 5
					"ard.maker",
					"unitName",  
					"financialYear"
			};

			logger.info("orderColumn **.... "+ orderColumn +"  orderDirection ...."+ orderDirection );
			if(orderColumn != null && orderColumn == -1 && !BankAuditUtil.isEmptyString(orderDirection)){ // -1 send for the first time from the UI during landing page.
				queryString=queryString.append(" ORDER BY FIELD(sch.auditStatus, 'AI','AP','AS','AR','AC') asc  "); // The code modified to get the records in the order of Status on landing page, the sequence is  Initiated, Planned, Submitted, Respond, Reviewed, Closed
			}else if(orderColumn!=null && !BankAuditUtil.isEmptyString(orderDirection)){
				queryString=queryString.append(" ORDER BY  ").append(columns[orderColumn]).append(" ").append(orderDirection);
			}else {
				queryString=queryString.append(" ORDER BY FIELD(sch.auditStatus, 'AI','AP','AS','AR','AC') asc  ");
			}

			logger.info("queryString ..##.. "+ queryString.toString());

		Query query=(Query) session.createQuery(queryString.toString())
				.setResultTransformer(new ResultTransformer() {
					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						AuditReportDynamicTable ardTable=null;
						if(tuple[0]!=null){
							ardTable=(AuditReportDynamicTable)tuple[0];
							AuditSchedule auditSchedule = (AuditSchedule)tuple[1];

							ardTable.setObservationStatus(auditSchedule.getObservationStatus());
							ardTable.setFinancialYear(auditSchedule.getFinancialYear());
							ardTable.setUnitId(auditSchedule.getUnitId());
							if(tuple[2]!=null) ardTable.setAuditTypeCode(tuple[2].toString());
							if(tuple[3]!=null){
								//ardTable.setUnitId(tuple[3].toString().split("--")[0]);
								//ardTable.setUnitName(tuple[3].toString().split("--")[1]);
								ardTable.setUnitName(tuple[3].toString());								
							}
							if(tuple[4]!=null){
								ardTable.setAuditStatus(tuple[4].toString().split("-")[0]);
								ardTable.setAuditStatusDesc(tuple[4].toString().split("-")[1]);
							}
							if(tuple[5]!=null){
								ardTable.setMaker(tuple[5].toString());
							}
							if(tuple[6]!=null){
								ardTable.setStatementType(tuple[6].toString());
							}else{
								ardTable.setStatementType(BankAuditConstant.FOR_ANNEXURE+"-"+BankAuditConstant.FOR_ANNEXURE);
							}
							
							if(tuple[7]!=null)
								ardTable.setAuditGroupCode(tuple[7].toString());
							if(tuple[8]!=null)
								ardTable.setAuditSubGroupCode(tuple[8].toString());
							if(tuple[9]!=null)
								ardTable.setActivityCode(tuple[9].toString());
							if(tuple[10]!=null)
								ardTable.setProcessCode(tuple[10].toString());
							if(tuple[11]!=null){
								AuditLevelDocumentDetails auditLevelDocumentDetails = (AuditLevelDocumentDetails) tuple[11];
								ardTable.setFileName(auditLevelDocumentDetails.getFileName());
								ardTable.setReferencePath(auditLevelDocumentDetails.getReferencePath());
							}
						}
						return ardTable;
					}
			@Override
			public List transformList(List collection) {
				return collection;
			}
		})
		.setParameter("legalEntityCode",legalEntityCode).setParameter("userId",userId);
		if(!BankAuditUtil.isEmptyString(search)){
			query.setParameter("search", "%"+search+"%");
		}		
		if(!BankAuditUtil.isEmptyString(auditTypeCode)) {
			query.setParameter("auditTypeCode", auditTypeCode);
		}
		
		logger.info("page :: "+page +"size :: "+ size);
		List<AuditReportDynamicTable> tmpAuditReportDynamicTable =query.list();
		Long totalRecords=null;
		try {
			totalRecords = (long)tmpAuditReportDynamicTable.size();
		}catch(Exception e) {
			totalRecords = (long)0;
		}
		List<AuditReportDynamicTable> auditReportDynamicTable=query.setFirstResult((page*size)).setMaxResults(size).list();

		logger.info("Bfr size tmpAuditReportDynamicTable is .... "+tmpAuditReportDynamicTable.size()+" totalRecords:: "+ totalRecords);

		dataTableResponse.setData(auditReportDynamicTable);
		dataTableResponse.setRecordsTotal(totalRecords);
		dataTableResponse.setRecordsFiltered(totalRecords);

		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		return dataTableResponse;
	}

	@Override
	public AuditReportDynamicTableDto getDynamicTablesByAuditId(int legalEntityCode, String auditTypeCode, String auditId) {

		logger.info("Inside getDynamicTablesByAuditId ..## "+ legalEntityCode +" :: "+ auditTypeCode +" :: "+auditId);
		AuditReportDynamicTableDto auditReportDynamicTableDto=new AuditReportDynamicTableDto();
		try {
			StringBuilder queryString=new StringBuilder("select ard as ardTable, "   // 0
					 + "   (select concat(auditTypeCode , '-' , auditTypeDesc) from MaintAuditTypeDesc at where at.auditTypeCode=ard.auditTypeCode and at.legalEntityCode= ard.legalEntityCode ) as auditTypeCodeTmp, "  // 1
					 + "   (Select concat(unitCode , '--' , unitName) from  MaintEntity e where e.unitCode = ard.unitId and e.legalEntityCode =ard.legalEntityCode ) as unitIdTmp,  "  // 2
					 + "   (Select concat(u.userId , '-' , ifnull(u.firstName,''), ' ',ifnull(u.middleName,''),' ',ifnull(u.lastName,'') )  from  User u where u.legalEntityCode =ard.legalEntityCode and u.userId=ard.maker) as makerTmp, "  // 3
					 + "   (Select concat(g.value , '-' , ifnull(g.description,''))  from  GeneralParameter g where g.legalEntityCode =ard.legalEntityCode and "
					 + "   g.value=ard.statementType and g.key1 = 'AUDIT_DYNAMIC_DATA' and g.key2 = 'ENRICHMENT_TYPE_OR_OPTIONS' ) as stmtType, "  // 4

					 + "   ( Select distinct concat(grp.auditGroupCode,'-',grp.auditGroupName) "
				   		+ "  from  MaintAuditGroup grp  where  grp.auditGroupCode = ard.auditGroupCode "
				   		+ "  and grp.auditTypeCode=ard.auditTypeCode  and grp.legalEntityCode=ard.legalEntityCode"
				   		+ "  and grp.status = 'A' and grp.entityStatus = 'A') as chapter, " //5

				   	 + "  ( Select distinct concat(subgrp.auditSubGroupCode,'-',subgrp.auditSubGroupName)  "
						+ "  from MaintAuditSubgroup subgrp  where subgrp.auditSubGroupCode = ard.auditSubGroupCode"
						+ "  and subgrp.auditTypeCode = ard.auditTypeCode and subgrp.auditGroupCode = ard.auditGroupCode "
						+ "  and subgrp.legalEntityCode = ard.legalEntityCode "
						+ "  and subgrp.status = 'A' and subgrp.entityStatus = 'A' ) as section, "				//6

					 + "  ( Select distinct concat(act.activityCode,'-',act.activityName)  "
						+ "  from MaintAuditActivity act where act.activityCode = ard.activityCode "
						+ "  and act.legalEntityCode = ard.legalEntityCode "
						+ "  and act.status = 'A' and act.entityStatus = 'A' ) as product , "		//7

					 + " ( Select  distinct concat(proc.processCode , '-', proc.processName) "
						+ "  from MaintAuditProcess proc  where  proc.processCode = ard.processCode "
						+ "  and proc.legalEntityCode = ard.legalEntityCode"
						+ "  and proc.status = 'A' and proc.entityStatus = 'A') as process,  " //8
						
					 + " ( Select  distinct  doc as docDetails  "
							+ "  from AuditLevelDocumentDetails doc  where  doc.auditId = ard.auditId  and doc.legalEntityCode = ard.legalEntityCode "
							+ "  and doc.auditTypeCode = ard.auditTypeCode  and doc.id = ard.docId  and doc.uploadStatus = 'S'  ) as docDetails  "  //9	

					 + " from AuditReportDynamicTable ard   "
					 + " where ard.legalEntityCode =:legalEntityCode"
					 + " and ard.auditTypeCode = :auditTypeCode "
					 + " and ard.auditId= :auditId   ");

			logger.info("queryString ..##.. "+ queryString.toString());

			Session session = getSession();
			session.setDefaultReadOnly(true);
			Query query=(Query) session.createQuery(queryString.toString())
					.setResultTransformer(new ResultTransformer() {
						@Override
						public Object transformTuple(Object[] tuple, String[] aliases) {
							AuditReportDynamicTable ardTable=null;
							if(tuple[0]!=null){
								ardTable=(AuditReportDynamicTable)tuple[0];

								if(tuple[1]!=null) ardTable.setAuditTypeCode(tuple[1].toString());
								if(tuple[2]!=null){
									ardTable.setUnitId(tuple[2].toString().split("--")[0]);
									ardTable.setUnitName(tuple[2].toString().split("--")[1]);
								}
								if(tuple[3]!=null){
									ardTable.setMaker(tuple[3].toString());
								}
								if(tuple[4]!=null){
									ardTable.setStatementType(tuple[4].toString());
								}else{
									ardTable.setStatementType(BankAuditConstant.FOR_ANNEXURE+"-"+BankAuditConstant.FOR_ANNEXURE);
								}
								
								if(tuple[5]!=null)
									ardTable.setAuditGroupCode(tuple[5].toString());
								if(tuple[6]!=null)
									ardTable.setAuditSubGroupCode(tuple[6].toString());
								if(tuple[7]!=null)
									ardTable.setActivityCode(tuple[7].toString());
								if(tuple[8]!=null)
									ardTable.setProcessCode(tuple[8].toString());

								if(tuple[9]!=null){
									AuditLevelDocumentDetails auditLevelDocumentDetails = (AuditLevelDocumentDetails) tuple[9];
									ardTable.setFileName(auditLevelDocumentDetails.getFileName());
									ardTable.setReferencePath(auditLevelDocumentDetails.getReferencePath());
								}
							}
							return ardTable;
						}
				@Override
				public List transformList(List collection) {
					return collection;
				}
			})
			.setParameter("legalEntityCode", legalEntityCode)
			.setParameter("auditTypeCode", auditTypeCode)
			.setParameter("auditId", auditId);
			List<AuditReportDynamicTable> auditReportDynamicTable = query.list();

			auditReportDynamicTableDto.setArdTableSequenceList(auditReportDynamicTable);
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		return auditReportDynamicTableDto;
	}

	@Override
	public List<AuditReportDynamicTable>  getDynamicTablesByAuditIdForAnnexure(int legalEntityCode, String auditTypeCode, String auditId) {

		logger.info("Inside getDynamicTablesByAuditId ..## "+ legalEntityCode +" :: "+ auditTypeCode +" :: "+auditId);

		try {
			StringBuilder queryString=new StringBuilder("select ard as ardTable  "
					 + " from AuditReportDynamicTable ard   "
					 + " where ard.legalEntityCode =:legalEntityCode"
					 + " and ard.auditTypeCode = :auditTypeCode "
					 + " and ard.auditId= :auditId"
					 + " and ard.statementType= :statementType "
					 + " order by sequence ASC  ");

			logger.info("queryString ..##.. "+ queryString.toString());

			Session session = getSession();
			session.setDefaultReadOnly(true);
			Query query=(Query) session.createQuery(queryString.toString())
			.setParameter("legalEntityCode", legalEntityCode)
			.setParameter("auditTypeCode", auditTypeCode)
			.setParameter("auditId", auditId)
			.setParameter("statementType", BankAuditConstant.FOR_ANNEXURE);
			List<AuditReportDynamicTable> auditReportDynamicTable = query.list();

			return auditReportDynamicTable;
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		return null;
	}


	@Override
	public AuditReportDynamicTable getDynamicTableById(Integer legalEntityCode, String auditTypeCode, String auditId, Integer id ) {
		logger.info("Inside getDynamicTablesById of InterfaceParameterDaoImpl .... ");
		Session session = getSession();
		session.setDefaultReadOnly(true);

		StringBuilder strQry = new StringBuilder(" Select ard as dyTable, ");
		strQry.append(" ( Select distinct concat(grp.auditGroupCode,'-',grp.auditGroupName) "
					   		+ "  from  MaintAuditGroup grp  where  grp.auditGroupCode = ard.auditGroupCode "
					   		+ "  and grp.auditTypeCode=ard.auditTypeCode  and grp.legalEntityCode=ard.legalEntityCode"
					   		+ "  and grp.status = 'A' and grp.entityStatus = 'A') as chapter, "

					   	 + "  ( Select distinct concat(subgrp.auditSubGroupCode,'-',subgrp.auditSubGroupName)  "
							+ "  from MaintAuditSubgroup subgrp  where subgrp.auditSubGroupCode = ard.auditSubGroupCode"
							+ "  and subgrp.auditTypeCode = ard.auditTypeCode and subgrp.auditGroupCode = ard.auditGroupCode "
							+ "  and subgrp.legalEntityCode = ard.legalEntityCode "
							+ "  and subgrp.status = 'A' and subgrp.entityStatus = 'A' ) as section, "

						 + "  ( Select distinct concat(act.activityCode,'-',act.activityName)  "
							+ "  from MaintAuditActivity act where act.activityCode = ard.activityCode "
							+ "  and act.legalEntityCode = ard.legalEntityCode "
							+ "  and act.status = 'A' and act.entityStatus = 'A' ) as product , "

						 + " ( Select  distinct concat(proc.processCode , '-', proc.processName) "
							+ "  from MaintAuditProcess proc  where  proc.processCode = ard.processCode "
							+ "  and proc.legalEntityCode = ard.legalEntityCode"
							+ "  and proc.status = 'A' and proc.entityStatus = 'A') as process,"
						
						 + " ( Select  distinct  doc as docDetails  "
							+ "  from AuditLevelDocumentDetails doc  where  doc.auditId = ard.auditId  and doc.legalEntityCode = ard.legalEntityCode "
							+ "  and doc.auditTypeCode = ard.auditTypeCode  and doc.id = ard.docId  and doc.uploadStatus = 'S'  ) as docDetails  "  //5
							
					+ " FROM AuditReportDynamicTable ard "
					+ " where ard.id = :id "
					+ " AND ard.legalEntityCode = :legalEntityCode "
					+ " AND ard.auditTypeCode = :auditTypeCode "
					+ " AND ard.auditId = :auditId  " );
		List<AuditReportDynamicTable>  auditReportDynamicTableList = session
				.createQuery(strQry.toString())
				.setParameter("id", id)
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("auditId", auditId)
				.setResultTransformer(new ResultTransformer() {

					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						AuditReportDynamicTable ardTable=null;
						if(tuple[0]!=null){
							ardTable=(AuditReportDynamicTable)tuple[0];
							if(tuple[1]!=null)
								ardTable.setAuditGroupCode(tuple[1].toString());
							if(tuple[2]!=null)
								ardTable.setAuditSubGroupCode(tuple[2].toString());
							if(tuple[3]!=null)
								ardTable.setActivityCode(tuple[3].toString());
							if(tuple[4]!=null)
								ardTable.setProcessCode(tuple[4].toString());
							if(tuple[5]!=null){
								AuditLevelDocumentDetails auditLevelDocumentDetails = (AuditLevelDocumentDetails) tuple[5];
								ardTable.setFileName(auditLevelDocumentDetails.getFileName());
								ardTable.setReferencePath(auditLevelDocumentDetails.getReferencePath());
							}
							if(BankAuditUtil.isEmptyString(ardTable.getStatementType())){
								ardTable.setStatementType(BankAuditConstant.FOR_ANNEXURE);
							}
						}
						return ardTable;
					}

					@Override
					public List transformList(List collection) {
						return collection;
					}
				}).list();
		return auditReportDynamicTableList!=null && auditReportDynamicTableList.size()>0?auditReportDynamicTableList.get(0):null;
	}

	@Override
	public void deleteAuditReportDynamicTableDetailsByTableId(int legalEntityCode, int tableId, String auditTypeCode, String auditId, List<Integer> dynamicTableDetailIds){
		logger.info("Inside deleteAuditReportDynamicTableDetailsByTableId .. "+legalEntityCode +" auditId.."+auditId + " tableId.. "+tableId);
		Session session=getSession();
		session.createQuery("delete from AuditReportDynamicTableDetails  "
				+ " where legalEntityCode = :legalEntityCode "
				+ " and tableId = :tableId  "
				+ " and auditTypeCode = :auditTypeCode "
				+ " and auditId = :auditId "
				+ " and id not in (:dynamicTableDetailIds) "
				+ " ")
		.setParameter("legalEntityCode", legalEntityCode)
		.setParameter("tableId", tableId)
		.setParameter("auditTypeCode", auditTypeCode)
		.setParameter("auditId", auditId)
		.setParameterList("dynamicTableDetailIds", dynamicTableDetailIds)
		.executeUpdate() ;
	}

	@SuppressWarnings("unchecked")
	@Override
	public void deleteDynamicTablesById(Integer legalEntityCode, String auditTypeCode, String auditId, Integer id)  {
		logger.info("Inside deleteDynamicTablesById .. "+legalEntityCode + " id.."+id +" auditId.."+auditId );
		Session session=getSession();
		delete(session.createQuery("from AuditReportDynamicTable  "
				+ " where legalEntityCode = :legalEntityCode "
				+ " and id = :id "
				+ " and auditTypeCode = :auditTypeCode "
				+ " and auditId = :auditId " )
		.setParameter("legalEntityCode", legalEntityCode)
		.setParameter("id", id)
		.setParameter("auditTypeCode", auditTypeCode)
		.setParameter("auditId", auditId).uniqueResult());
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<AuditSchedule> getInprogressAuditbyUserId(int legalEntityCode, String userId, String roleId)  {
		logger.info("Inside getInprogressAuditbyUserId .. "+legalEntityCode +" userId.."+userId + " roleId.. "+roleId);
		Session session=getSession();
		return session.createQuery(" select sch FROM AuditSchedule sch, AuditTeam at "
					+ " where  sch.legalEntityCode = at.legalEntityCode "
					+ " and sch.auditId = at.auditId "
					+ " and sch.auditStatus in ('AI') "
					+ " and sch.legalEntityCode = :legalEntityCode "
					+ " and at.userId = :userId  ")
				.setParameter("legalEntityCode", legalEntityCode).setParameter("userId", userId).list();
	}

	@Override
	public void executeHQLQuery(String strQuery){
		logger.info("Inside executeHQLQuery .. "+strQuery);
		Session session=getSession();
		session.createQuery(strQuery).executeUpdate() ;
	}

	@Override
	public List<AuditReportDynamicTablePredefinedDataListDto>  getPredefinedStatementDetail(int legalEntityCode, String auditTypeCode, String auditId, String auditGroupCode,
			String auditSubGroupCode,  String activityCode,  String processCode, String statementType, String all){

		StringBuilder qryStr = new StringBuilder(" Select rpt as auditReportStmt,  ardTable.id "
				+ " from  AuditReportStmt rpt"
				+ " left join AuditReportDynamicTable ardTable ON  ardTable.legalEntityCode =  rpt.legalEntityCode "
				+ " and ardTable.auditTypeCode = rpt.auditTypeCode and ardTable.auditId = rpt.auditId "
				+ " and ardTable.statementType = rpt.statementType ");
		if(!statementType.equalsIgnoreCase(BankAuditConstant.FOR_ANNEXURE)){
			qryStr.append(" and  ardTable.statementId = rpt.statementId " );
		}
		if(!BankAuditUtil.isEmptyString(all) && BankAuditConstant.NO.equalsIgnoreCase(all)){
			qryStr.append(" and  ardTable.applicableFor = rpt.applicableFor " );
		}
		qryStr.append(" where rpt.legalEntityCode = :legalEntityCode  "
				+ " and rpt.auditTypeCode = :auditTypeCode  "
				+ " and rpt.auditId = :auditId  ");

		String applicableFor = "";
		if(!BankAuditUtil.isEmptyString(auditGroupCode)){
			qryStr.append(" and rpt.auditGroupCode = :auditGroupCode");
			applicableFor =  BankAuditConstant.FOR_CHAPTER;
		}
		if(!BankAuditUtil.isEmptyString(auditSubGroupCode)){
			qryStr.append(" and rpt.auditSubGroupCode = :auditSubGroupCode");
			applicableFor =  BankAuditConstant.FOR_SECTION;
		}
		if(!BankAuditUtil.isEmptyString(activityCode)){
			qryStr.append(" and rpt.activityCode = :activityCode");
			applicableFor =  BankAuditConstant.FOR_PRODUCT;
		}
		if(!BankAuditUtil.isEmptyString(processCode)){
			qryStr.append(" and rpt.processCode = :processCode");
			applicableFor =  BankAuditConstant.FOR_PROCESS;
		}
		if(!BankAuditUtil.isEmptyString(all) && BankAuditConstant.NO.equalsIgnoreCase(all)){
			qryStr.append(" and  rpt.applicableFor = :applicableFor " );
		}
		qryStr.append(" and rpt.statementType = :statementType  "
					+ " order by rpt.sequence ASC ");

		Session session = getSession();
		session.setDefaultReadOnly(true);
		Query query = session.createQuery(qryStr.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("auditId", auditId)
				.setParameter("statementType", statementType);
		if(!BankAuditUtil.isEmptyString(auditGroupCode)){
			query.setParameter("auditGroupCode", auditGroupCode);
		}
		if(!BankAuditUtil.isEmptyString(auditSubGroupCode)){
			query.setParameter("auditSubGroupCode", auditSubGroupCode);
		}
		if(!BankAuditUtil.isEmptyString(activityCode)){
			query.setParameter("activityCode", activityCode);
		}
		if(!BankAuditUtil.isEmptyString(processCode)){
			query.setParameter("processCode", processCode);
		}

		if(!BankAuditUtil.isEmptyString(all) && BankAuditConstant.NO.equalsIgnoreCase(all)){
			query.setParameter("applicableFor", applicableFor);
		}

		query.setResultTransformer(new ResultTransformer() {
					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						AuditReportDynamicTablePredefinedDataListDto ardtPredefinedDataListDto = null;
						if(tuple[0]!=null){
							ardtPredefinedDataListDto = new AuditReportDynamicTablePredefinedDataListDto();
							AuditReportStmt rpt =  (AuditReportStmt) tuple[0];

							ardtPredefinedDataListDto.setLegalEntityCode(legalEntityCode);
							ardtPredefinedDataListDto.setAuditTypeCode(auditTypeCode);
							ardtPredefinedDataListDto.setAuditId(auditId);

							ardtPredefinedDataListDto.setAuditGroupCode(rpt.getAuditGroupCode()==null?null:(rpt.getAuditGroupCode()+"-"+rpt.getAuditGroupName()));
							ardtPredefinedDataListDto.setAuditSubGroupCode(rpt.getAuditSubGroupCode()==null?null:(rpt.getAuditSubGroupCode()+"-"+rpt.getAuditSubGroupName()));
							ardtPredefinedDataListDto.setActivityCode(rpt.getActivityCode()==null?null:(rpt.getActivityCode()+"-"+rpt.getActivityName()));
							ardtPredefinedDataListDto.setProcessCode(rpt.getProcessCode()==null?null:(rpt.getProcessCode()+"-"+rpt.getProcessName()));
							ardtPredefinedDataListDto.setFindingCode(rpt.getFindingCode()==null?null:(rpt.getFindingCode()+"-"+rpt.getFindingName()));

							ardtPredefinedDataListDto.setApplicableFor(rpt.getApplicableFor());
							ardtPredefinedDataListDto.setStatementType(rpt.getStatementType());
							ardtPredefinedDataListDto.setStatementId(rpt.getStatementId());
							ardtPredefinedDataListDto.setStatement(rpt.getStatement());
							ardtPredefinedDataListDto.setReportSequence(Integer.parseInt(rpt.getSequence()));
							ardtPredefinedDataListDto.setMaintReportSequence(rpt.getMaintReportSequence());

							ardtPredefinedDataListDto.setRecordStatus(tuple[1]!=null?"Y":"N");

						}
						return ardtPredefinedDataListDto;
					}

					@Override
					public List transformList(List collection) {
						return collection;
					}
				});

		List<AuditReportDynamicTablePredefinedDataListDto> ardtPredefinedDataListDtoList =
				query.list();

		return ardtPredefinedDataListDtoList;
	}

	@Override
	public AuditReportDynamicTable  getSavedStatementDetail(int legalEntityCode, String auditTypeCode, String auditId, String auditGroupCode,
			String auditSubGroupCode,  String activityCode,  String processCode, String statementType, String statementId){

		StringBuilder qryStr = new StringBuilder(" Select  ardTable as dynaminTable ,"
				 + " ( Select  distinct  doc as docDetails  "
					+ "  from AuditLevelDocumentDetails doc  where  doc.auditId = ardTable.auditId  and doc.legalEntityCode = ardTable.legalEntityCode "
					+ "  and doc.auditTypeCode = ardTable.auditTypeCode  and doc.id = ardTable.docId  and doc.uploadStatus = 'S'  ) as docDetails  "  //2
				+ " From AuditReportDynamicTable ardTable   ");

		qryStr.append(" where ardTable.legalEntityCode = :legalEntityCode  "
				+ " and ardTable.auditTypeCode = :auditTypeCode  "
				+ " and ardTable.auditId = :auditId  ");

		String applicableFor = "";
		if(!BankAuditUtil.isEmptyString(statementId) ){
			qryStr.append(" and  ardTable.statementId = :statementId " );
		}
		if(!BankAuditUtil.isEmptyString(auditGroupCode)){
			qryStr.append(" and ardTable.auditGroupCode = :auditGroupCode");
			applicableFor =  BankAuditConstant.FOR_CHAPTER;
		}
		if(!BankAuditUtil.isEmptyString(auditSubGroupCode)){
			qryStr.append(" and ardTable.auditSubGroupCode = :auditSubGroupCode");
			applicableFor =  BankAuditConstant.FOR_SECTION;
		}
		if(!BankAuditUtil.isEmptyString(activityCode)){
			qryStr.append(" and ardTable.activityCode = :activityCode");
			applicableFor =  BankAuditConstant.FOR_PRODUCT;
		}
		if(!BankAuditUtil.isEmptyString(processCode)){
			qryStr.append(" and ardTable.processCode = :processCode");
			applicableFor =  BankAuditConstant.FOR_PROCESS;
		}
		if(!BankAuditUtil.isEmptyString(applicableFor)){
			qryStr.append(" and  ardTable.applicableFor = :applicableFor " );
		}
		qryStr.append(" and ardTable.statementType = :statementType ");

		Session session = getSession();
		session.setDefaultReadOnly(true);
		Query query = session.createQuery(qryStr.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("auditId", auditId)
				.setParameter("statementType", statementType);
		if(!BankAuditUtil.isEmptyString(statementId)){
			query.setParameter("statementId", statementId);
		}
		if(!BankAuditUtil.isEmptyString(auditGroupCode)){
			query.setParameter("auditGroupCode", auditGroupCode);
		}
		if(!BankAuditUtil.isEmptyString(auditSubGroupCode)){
			query.setParameter("auditSubGroupCode", auditSubGroupCode);
		}
		if(!BankAuditUtil.isEmptyString(activityCode)){
			query.setParameter("activityCode", activityCode);
		}
		if(!BankAuditUtil.isEmptyString(processCode)){
			query.setParameter("processCode", processCode);
		}
		if(!BankAuditUtil.isEmptyString(applicableFor)){
			query.setParameter("applicableFor", applicableFor);
		}

		//AuditReportDynamicTable auditReportDynamicTable = (AuditReportDynamicTable) query.uniqueResult();
		query.setResultTransformer(new ResultTransformer() {
			@Override
			public Object transformTuple(Object[] tuple, String[] aliases) {
				AuditReportDynamicTable ardTable =null;
				
				if(tuple[0]!=null){ 
					ardTable  = (AuditReportDynamicTable) tuple[0]; 
					if(tuple[1]!=null){
						AuditLevelDocumentDetails auditLevelDocumentDetails = (AuditLevelDocumentDetails) tuple[1];
						ardTable.setFileName(auditLevelDocumentDetails.getFileName());
						ardTable.setReferencePath(auditLevelDocumentDetails.getReferencePath());	
					}
				}
				return ardTable;
			}

			@Override
			public List transformList(List collection) {
				return collection;
			}
		});

		List<AuditReportDynamicTable> ardTableList = query.list();
		if(ardTableList!=null && ardTableList.size()>0){
			return ardTableList.get(0);
		}
		return null;
	}

}
