package com.bankaudit.rest.process.dao;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.SortedMap;
import java.util.TreeMap;

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.dao.AbstractDao;
import com.bankaudit.dto.process.AuditGradingBasicDtlsDto;
import com.bankaudit.dto.process.AuditGradingDto;
import com.bankaudit.model.MaintGradingParamCompositeRatingDtls;
import com.bankaudit.model.MaintGradingParamScoreMatrix;
import com.bankaudit.model.MaintGradingQsnBasicDtls;
import com.bankaudit.model.MaintGradingQsnDtls;
import com.bankaudit.process.model.AuditGradingParamCompositeRatingDtls;
import com.bankaudit.process.model.AuditGradingParamDtls;
import com.bankaudit.process.model.AuditGradingQsnBasicDtls;
import com.bankaudit.process.model.AuditGradingQsnDtls;
import com.bankaudit.process.model.AuditGradingQsnRemarks;
import com.bankaudit.process.model.AuditGradingQsoDtlsHO;
import com.bankaudit.process.model.AuditGradingQsoDtlsRO;
import com.bankaudit.process.model.AuditRating;
import com.bankaudit.process.model.AuditRatingCompositeCategoryScore;
import com.bankaudit.util.BankAuditUtil;

@Repository("auditGradingDao")
public class AuditGradingDaoImpl  extends AbstractDao implements AuditGradingDao {

	static final Logger logger = Logger.getLogger(AuditGradingDaoImpl.class);
 	
	@Override
	public List<MaintGradingQsnBasicDtls> getMaintGradingQsnBasicDtls(Integer legalEntityCode, String auditTypeCode){ 		
		logger.info("Inside getAuditGradingDetails .. "+legalEntityCode +" :: "+auditTypeCode );	 
		Session session = getSession();
		session.setDefaultReadOnly(true);
		
		StringBuilder gradingQsnQuery = new StringBuilder(" Select qsnDtls as gradingQsnDtls From MaintGradingQsnBasicDtls qsnDtls "
				+ " WHERE legalEntityCode = :legalEntityCode"
				+ " AND  auditTypeCode = :auditTypeCode "
				+ " ORDER BY slNo ASC");		
		List<MaintGradingQsnBasicDtls> maintGradingQsnBasicDtlsList = session.createQuery(gradingQsnQuery.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode).list();
 	 	 
		return maintGradingQsnBasicDtlsList;	
	}
	
	@Override
	public List<MaintGradingQsnDtls> getMaintGradingQsnDetails(Integer legalEntityCode, String auditTypeCode){ 		
		logger.info("Inside getAuditGradingDetails .. "+legalEntityCode +" :: "+auditTypeCode );	 
		Session session = getSession();
		session.setDefaultReadOnly(true);
		
		StringBuilder gradingQsnQuery = new StringBuilder(" Select qsnDtls as gradingQsnDtls From MaintGradingQsnDtls qsnDtls "
				+ " WHERE legalEntityCode = :legalEntityCode"
				+ " AND  auditTypeCode = :auditTypeCode "
				+ " ORDER BY displaySequence ASC");		
		List<MaintGradingQsnDtls> maintGradingQsnDtlsList = session.createQuery(gradingQsnQuery.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode).list();
 	 	 
		return maintGradingQsnDtlsList;	
	}
	
	@Override
	public List<MaintGradingParamCompositeRatingDtls> getMaintGradingParamCompositeRatingDtls(Integer legalEntityCode, String auditTypeCode){ 		
		logger.info("Inside getMaintGradingParamCompositeRatingDtls .. "+legalEntityCode +" :: "+auditTypeCode );	 
		Session session = getSession();
		session.setDefaultReadOnly(true);
		
		StringBuilder gradingQsnQuery = new StringBuilder(" Select paramCompo as gradingParamCompo From MaintGradingParamCompositeRatingDtls paramCompo "
				+ " WHERE legalEntityCode = :legalEntityCode"
				+ " AND auditTypeCode = :auditTypeCode "
				+ " ORDER BY slNo ASC");		
		List<MaintGradingParamCompositeRatingDtls> maintGradingParamCompositeRatingDtlsList = session.createQuery(gradingQsnQuery.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode).list();
 	 	 
		return maintGradingParamCompositeRatingDtlsList;	
	}

	@Override
	public List<Object[]> getAuditGradingDynamicMappingDetails(Integer legalEntityCode, String auditTypeCode, String auditId){
		logger.info("Inside getAuditGradingDynamicMappingDetails.. "+legalEntityCode +" :: "+auditTypeCode+" ::"+ auditId);
		Session session = getSession();
		session.setDefaultReadOnly(true);
		StringBuilder queryString=new StringBuilder("SELECT DISTINCT " 
					+ " r.legal_entity_code, "
					+ " r.audit_type_code, "
					+ " r.audit_id, "					
					+ " d.display_sequence_ref, " 				 
					+ " (CASE WHEN (d.display_type = '"+BankAuditConstant.GRADING_QSN_DISPLAY_TYPE_DYNAMIC+"'  "
									+ " AND (display_ref = '"+BankAuditConstant.GRADING_QSN_DISPLAY_REFERENCE_STATEMENT_ID+"' "
							  		+ " OR display_ref = '"+BankAuditConstant.GRADING_QSN_DISPLAY_REFERENCE_MAPPING_CODE+"' ) ) "
							 		+ " THEN IFNULL(r.statement, '') "							 		
						  + " WHEN ((d.display_type = '"+BankAuditConstant.GRADING_QSN_DISPLAY_TYPE_STATIC+"'"
							 		+ " AND d.display_ref = '"+BankAuditConstant.GRADING_QSN_DISPLAY_REFERENCE_STATIC_CONTENT+"')"
							 		+ " OR d.display_type = '"+BankAuditConstant.GRADING_QSN_DISPLAY_TYPE_IP+"') "
							 		+ " THEN IFNULL(d.display_value, '') END ) as stmts "				 				
					+ "	FROM audit_report_stmt r "
					+ " INNER JOIN  maint_grading_qsn_dynamic_dtls d "					
					+ " ON  d.audit_type_code = r.audit_type_code AND  d.legal_entity_code = r.legal_entity_code " 
					+ " AND (CASE WHEN d.display_type = '"+BankAuditConstant.GRADING_QSN_DISPLAY_TYPE_DYNAMIC+"'  "
									+ " AND display_ref = '"+BankAuditConstant.GRADING_QSN_DISPLAY_REFERENCE_STATEMENT_ID+"' "
							 		+ " THEN d.display_ref_id = r.statement_id "
							 		+ " AND r.statement_type IN ('"+BankAuditConstant.FOR_DERIVED_STMT+"', '"+BankAuditConstant.FOR_STATIC_STMT+"' )"
							  + " WHEN d.display_type = '"+BankAuditConstant.GRADING_QSN_DISPLAY_TYPE_DYNAMIC+"'  "
									+ " AND display_ref = '"+BankAuditConstant.GRADING_QSN_DISPLAY_REFERENCE_MAPPING_CODE+"' "
							 		+ " THEN d.display_ref_id = r.mapping_id "
							 		+ " AND r.statement_type IN ('"+BankAuditConstant.FOR_STANDARD_STMT+"', '"+BankAuditConstant.FOR_REMARKS+"' ) "
							 + "  WHEN ((d.display_type = '"+BankAuditConstant.GRADING_QSN_DISPLAY_TYPE_STATIC+"'"
							 		+ " AND display_ref = '"+BankAuditConstant.GRADING_QSN_DISPLAY_REFERENCE_STATIC_CONTENT+"')"
							 		+ " OR d.display_type = '"+BankAuditConstant.GRADING_QSN_DISPLAY_TYPE_IP+"') "
							 		+ " THEN TRUE END ) "
					 
					+ " WHERE r.legal_entity_code = :legalEntityCode  AND r.audit_type_code = :auditTypeCode AND r.audit_id = :auditId "					
					/*+ " AND r.statement_type IN ('"+BankAuditConstant.FOR_STANDARD_STMT+"', '"+BankAuditConstant.FOR_DERIVED_STMT+"',  "
									+ " '"+BankAuditConstant.FOR_STATIC_STMT+"', '"+BankAuditConstant.FOR_REMARKS+"' )"*/
					/*+ " ORDER BY FIELD(r.statement_type, '"+BankAuditConstant.FOR_STANDARD_STMT+"', '"+BankAuditConstant.FOR_DERIVED_STMT+"',  "
									+ " '"+BankAuditConstant.FOR_STATIC_STMT+"', '"+BankAuditConstant.FOR_REMARKS+"' ) asc "*/
					+ " ");		
		List<Object[]> qsnDynamicValueDtlsList = session.createNativeQuery(queryString.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("auditId", auditId).list();
		
		return qsnDynamicValueDtlsList;
	}
	
	@Override
	public void insertParamDtlsIntoAuditGradingParamDtls(Integer legalEntityCode, String auditTypeCode, String auditId){
		logger.info("Inside insertDataIntoAuditGradingParamDtls.. "+legalEntityCode +" :: "+auditTypeCode+" ::"+ auditId);
		Session session = getSession();
		StringBuilder gradingParamQuery = new StringBuilder(" INSERT INTO audit_grading_param_dtls( "
				+ " legal_entity_code, audit_type_code, audit_id, param_code, sl_no, disp_seq_no, param_name, max_score) "
				+ " SELECT "
				+ " legal_entity_code, audit_type_code, :auditId, param_code, sl_no, disp_seq_no, param_name, max_score "
				+ " FROM maint_grading_param_dtls "
				+ " WHERE legal_entity_code = :legalEntityCode"
				+ " AND  audit_type_code = :auditTypeCode ");	
		
		session.createNativeQuery(gradingParamQuery.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode) 
				.setParameter("auditId", auditId).executeUpdate();			 
	}
	 	
	@Override
	public AuditGradingDto getMyAuditGrading(Integer legalEntityCode, String auditTypeCode, String auditId, String userUnitType){
		logger.info("Inside getMyAuditGrading .. "+legalEntityCode +" :: "+auditTypeCode +" :: "+userUnitType );	 
		Session session = getSession();
		session.setDefaultReadOnly(true);
		
		/**
		 * Get Audit Grading Basic Details
		 */
		StringBuilder gradingBasicQuery = new StringBuilder(" Select basicDtls as gradingBasicDtls From AuditGradingQsnBasicDtls basicDtls "
				+ " WHERE legalEntityCode = :legalEntityCode"
				+ " AND auditTypeCode = :auditTypeCode "
				+ " AND auditId = :auditId "
				+ " ORDER BY slNo ASC ");	 
		List<AuditGradingQsnBasicDtls> auditGradingQsnBasicDtlsList = session.createQuery(gradingBasicQuery.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("auditId", auditId).list();
		
		/*Map<String, AuditGradingQsnBasicDtls> auditGradingQsnBasicDtlsMap = new HashMap<String, AuditGradingQsnBasicDtls>();
		for (AuditGradingQsnBasicDtls auditGradingQsnBasicDtls : auditGradingQsnBasicDtlsList) {			
			auditGradingQsnBasicDtlsMap.put(auditGradingQsnBasicDtls.getDispSeqNo(), auditGradingQsnBasicDtls);
		}*/
		
		SortedMap<String, AuditGradingQsnBasicDtls> auditGradingQsnBasicDtlsMap = new TreeMap<String, AuditGradingQsnBasicDtls>();
		for (AuditGradingQsnBasicDtls auditGradingQsnBasicDtls : auditGradingQsnBasicDtlsList) {			
			auditGradingQsnBasicDtlsMap.put(auditGradingQsnBasicDtls.getDispSeqNo(), auditGradingQsnBasicDtls);
		}
		
		/**
		 * Get Audit Grading QSN Details
		 */
		StringBuilder gradingQsnQuery = new StringBuilder(" Select qsnDtls as gradingQsnDtls From AuditGradingQsnDtls qsnDtls "
				+ " WHERE legalEntityCode = :legalEntityCode"
				+ " AND auditTypeCode = :auditTypeCode "
				+ " AND auditId = :auditId "
				+ " ORDER BY displaySequence ASC ");	 
		List<AuditGradingQsnDtls> auditGradingQsnDtlsList = session.createQuery(gradingQsnQuery.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("auditId", auditId).list();
		
		/*Map<String, AuditGradingQsnDtls> auditGradingQsnDtlsMap = new HashMap<String, AuditGradingQsnDtls>();
		for (AuditGradingQsnDtls auditGradingQsnDtls : auditGradingQsnDtlsList) {			
			auditGradingQsnDtlsMap.put(auditGradingQsnDtls.getDisplaySequenceId(), auditGradingQsnDtls);
		}*/
		SortedMap<String, AuditGradingQsnDtls> auditGradingQsnDtlsMap = new TreeMap<String, AuditGradingQsnDtls>();
		for (AuditGradingQsnDtls auditGradingQsnDtls : auditGradingQsnDtlsList) {			
			auditGradingQsnDtlsMap.put(auditGradingQsnDtls.getDisplaySequenceId(), auditGradingQsnDtls);
		}
		
		/**
		 * Get Audit Grading QSO Details RO
		 */
		StringBuilder gradingQsoQueryRO = new StringBuilder(" Select qsoDtls as gradingQsoDtls From AuditGradingQsoDtlsRO qsoDtls "
				+ " WHERE legalEntityCode = :legalEntityCode"
				+ " AND auditTypeCode = :auditTypeCode "
				+ " AND auditId = :auditId  "
				+ " AND status <> :statusDelete "
				+ " ORDER BY cast(qsoSequenceId  as int)   ASC ");		
		List<AuditGradingQsoDtlsRO> auditGradingQsoDtlsROList = session.createQuery(gradingQsoQueryRO.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("auditId", auditId)
				.setParameter("statusDelete", BankAuditConstant.STATUS_DEL)
				.list();
		
		
		/**
		 * Get Audit Grading QSO Details HO
		 */
		StringBuilder gradingQsoQueryHO = new StringBuilder(" Select qsoDtls as gradingQsoDtls From AuditGradingQsoDtlsHO qsoDtls "
				+ " WHERE legalEntityCode = :legalEntityCode"
				+ " AND auditTypeCode = :auditTypeCode "
				+ " AND auditId = :auditId "
				+ " AND status <> :statusDelete "
				+ " ORDER BY cast(qsoSequenceId  as int)   ASC ");		
		List<AuditGradingQsoDtlsHO> auditGradingQsoDtlsHOList = session.createQuery(gradingQsoQueryHO.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("auditId", auditId)
				.setParameter("statusDelete", BankAuditConstant.STATUS_DEL)
				.list();
		
		
		/**
		 * Get Audit Grading Param Details
		 */
		StringBuilder gradingParamQuery = new StringBuilder(" Select qsoDtls as gradingQsoDtls From AuditGradingParamDtls qsoDtls "
				+ " WHERE legalEntityCode = :legalEntityCode"
				+ " AND auditTypeCode = :auditTypeCode "
				+ " AND auditId = :auditId ");		
		List<AuditGradingParamDtls> auditGradingParamDtlsList = session.createQuery(gradingParamQuery.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("auditId", auditId).list();
		
		/**
		 * Get Audit Grading QSN remarks - All Auth records
		 */
		StringBuilder gradingQsnRemarksAllQuery = new StringBuilder(" Select qsnRemarks as gradingQsnRemarks, "
				+ " concat(IFNULL(u.firstName,''),' ',IFNULL(u.middleName,''),' ',IFNULL(u.lastName,'')) "
				+ " From AuditGradingQsnRemarks qsnRemarks "
				+ " INNER JOIN User u ON qsnRemarks.legalEntityCode = u.legalEntityCode AND qsnRemarks.maker = u.userId "
				+ " WHERE qsnRemarks.legalEntityCode = :legalEntityCode"
				+ " AND qsnRemarks.auditTypeCode = :auditTypeCode "
				+ " AND qsnRemarks.auditId = :auditId "
				+ " AND qsnRemarks.status = :statusAuth "
				+ " AND qsnRemarks.userUnitType = :userUnitType "
				+ " ORDER BY qsnRemarks.id ASC ");		
		
		List<AuditGradingQsnRemarks> auditGradingQsnRemarksList = session.createQuery(gradingQsnRemarksAllQuery.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("auditId", auditId)
				.setParameter("statusAuth", BankAuditConstant.STATUS_AUTH)
				.setParameter("userUnitType", userUnitType)
				.setResultTransformer(new ResultTransformer() {
					
					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						AuditGradingQsnRemarks  auditGradingQsnRemarks = new AuditGradingQsnRemarks();
						if(tuple[0]!=null){
							auditGradingQsnRemarks = (AuditGradingQsnRemarks) tuple[0];
							auditGradingQsnRemarks.setMaker(auditGradingQsnRemarks.getMaker()+"-"+(tuple[1]!=null?tuple[1].toString():""));
						}						
						return auditGradingQsnRemarks;
					}
					
					@Override
					public List transformList(List collection) { 
						return collection;
					}
				}).list(); 		
		
		/**
		 * Get Audit Grading QSN remarks - UnAuth
		 */
		StringBuilder gradingQsnRemarksQueryUnAuth = new StringBuilder(" Select qsnRemarks as gradingQsnRemarks, "
				+ " concat(IFNULL(u.firstName,''),' ',IFNULL(u.middleName,''),' ',IFNULL(u.lastName,'')), "
				+ " u.designation "
				+ " From AuditGradingQsnRemarks qsnRemarks "
				+ " INNER JOIN User u ON qsnRemarks.legalEntityCode = u.legalEntityCode AND qsnRemarks.maker = u.userId "
				+ " WHERE qsnRemarks.legalEntityCode = :legalEntityCode"
				+ " AND qsnRemarks.auditTypeCode = :auditTypeCode "
				+ " AND qsnRemarks.auditId = :auditId "
				+ " AND qsnRemarks.status = :statusUnAuth "
				+ " AND qsnRemarks.userUnitType = :userUnitType  ");		
		List<AuditGradingQsnRemarks> auditGradingQsnRemarksUnAuthList =  session.createQuery(gradingQsnRemarksQueryUnAuth.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("auditId", auditId)
				.setParameter("statusUnAuth", BankAuditConstant.STATUS_UNAUTH)
				.setParameter("userUnitType", userUnitType)
				.setResultTransformer(new ResultTransformer() {					
					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) { 
						AuditGradingQsnRemarks  auditGradingQsnRemarks = new AuditGradingQsnRemarks();
						if(tuple[0]!=null){
							auditGradingQsnRemarks = (AuditGradingQsnRemarks) tuple[0];
							auditGradingQsnRemarks.setMaker(auditGradingQsnRemarks.getMaker()+"-"+(tuple[1]!=null?tuple[1].toString():""));
							auditGradingQsnRemarks.setDesignation(tuple[2]!=null?tuple[2].toString():"");
						}						
						return auditGradingQsnRemarks;
					}
					
					@Override
					public List transformList(List collection) { 
						return collection;
					}
				}).list();		
		AuditGradingQsnRemarks auditGradingQsnRemarks = null;
		if(auditGradingQsnRemarksUnAuthList == null || auditGradingQsnRemarksUnAuthList.size()==0){
			auditGradingQsnRemarks = new AuditGradingQsnRemarks(); 
			auditGradingQsnRemarks.setLegalEntityCode(legalEntityCode);
			auditGradingQsnRemarks.setAuditTypeCode(auditTypeCode);
			auditGradingQsnRemarks.setAuditId(auditId); 
		}else{
			auditGradingQsnRemarks = auditGradingQsnRemarksUnAuthList.get(0);
		}  
		
		/**
		 * Get Audit Grading Param composite rating Details
		 */
		StringBuilder gradingParamCompoQuery = new StringBuilder(" Select paramComp as gradingParamComp From AuditGradingParamCompositeRatingDtls paramComp "
				+ " WHERE legalEntityCode = :legalEntityCode"
				+ " AND auditTypeCode = :auditTypeCode "
				+ " AND auditId = :auditId ");		
		List<AuditGradingParamCompositeRatingDtls> auditGradingParamCompositeRatingDtlsList = session.createQuery(gradingParamCompoQuery.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("auditId", auditId).list();
		
		AuditGradingDto  auditGradingDto = new AuditGradingDto();
		auditGradingDto.setAuditGradingQsnBasicDtlsMap(auditGradingQsnBasicDtlsMap);
		auditGradingDto.setAuditGradingQsnDtlsMap(auditGradingQsnDtlsMap);
		auditGradingDto.setAuditGradingQsoDtlsROList(auditGradingQsoDtlsROList);
		auditGradingDto.setAuditGradingQsoDtlsHOList(auditGradingQsoDtlsHOList);
		
		auditGradingDto.setAuditGradingParamDtlsList(auditGradingParamDtlsList);
		auditGradingDto.setAuditGradingQsnRemarksList(auditGradingQsnRemarksList);
		auditGradingDto.setAuditGradingQsnRemarks(auditGradingQsnRemarks);
		auditGradingDto.setAuditGradingParamCompositeRatingDtlsList(auditGradingParamCompositeRatingDtlsList);
		return auditGradingDto;
	}
	
	@Override
	public void cleanUpBeforeInitiateGrading(Integer legalEntityCode, String auditTypeCode, String auditId){
		logger.info("Inside cleanUpBeforeInitiateGrading .. "+legalEntityCode +" :: "+auditTypeCode +" :: "+auditId);	 
		Session session = getSession(); 
		
		StringBuilder deleteGradingBasic = new StringBuilder(" DELETE FROM AuditGradingQsnBasicDtls "
				+ " WHERE legalEntityCode = :legalEntityCode"
				+ " AND auditTypeCode = :auditTypeCode "
				+ " AND auditId = :auditId  ");		
		session.createQuery(deleteGradingBasic.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("auditId", auditId).executeUpdate();
		
		StringBuilder deleteGradingQsnQuery = new StringBuilder(" DELETE FROM AuditGradingQsnDtls "
				+ " WHERE legalEntityCode = :legalEntityCode"
				+ " AND auditTypeCode = :auditTypeCode "
				+ " AND auditId = :auditId  ");		
		session.createQuery(deleteGradingQsnQuery.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("auditId", auditId).executeUpdate();
		
		StringBuilder deleteGradingQsoQueryRO = new StringBuilder(" DELETE FROM AuditGradingQsoDtlsRO "
				+ " WHERE legalEntityCode = :legalEntityCode"
				+ " AND auditTypeCode = :auditTypeCode "
				+ " AND auditId = :auditId  ");		
		session.createQuery(deleteGradingQsoQueryRO.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("auditId", auditId).executeUpdate();
		
		StringBuilder deleteGradingQsoQueryHO = new StringBuilder(" DELETE FROM AuditGradingQsoDtlsHO "
				+ " WHERE legalEntityCode = :legalEntityCode"
				+ " AND auditTypeCode = :auditTypeCode "
				+ " AND auditId = :auditId  ");		
		session.createQuery(deleteGradingQsoQueryHO.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("auditId", auditId).executeUpdate();
		
		StringBuilder deleteGradingParamQuery = new StringBuilder(" DELETE FROM AuditGradingParamDtls "
				+ " WHERE legalEntityCode = :legalEntityCode"
				+ " AND auditTypeCode = :auditTypeCode "
				+ " AND auditId = :auditId  ");		
		session.createQuery(deleteGradingParamQuery.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("auditId", auditId).executeUpdate();
		
		StringBuilder deleteGradingRemarksQuery = new StringBuilder(" DELETE FROM AuditGradingQsnRemarks "
				+ " WHERE legalEntityCode = :legalEntityCode"
				+ " AND auditTypeCode = :auditTypeCode "
				+ " AND auditId = :auditId  ");		
		session.createQuery(deleteGradingRemarksQuery.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("auditId", auditId).executeUpdate();
		
		StringBuilder deleteGradingParamCompositeRatingDtls = new StringBuilder(" DELETE FROM AuditGradingParamCompositeRatingDtls "
				+ " WHERE legalEntityCode = :legalEntityCode"
				+ " AND auditTypeCode = :auditTypeCode "
				+ " AND auditId = :auditId  ");		
		session.createQuery(deleteGradingParamCompositeRatingDtls.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("auditId", auditId).executeUpdate();
		
	}
	
	@Override
	public void deleteAuditGradingQsoDtlsRO(Integer legalEntityCode, String auditTypeCode, String auditId){
		logger.info("Inside deleteAuditGradingQsoDtlsRO .. "+legalEntityCode +" :: "+auditTypeCode );	 
		Session session = getSession(); 
		
		StringBuilder deleteQsoQuery = new StringBuilder(" DELETE FROM AuditGradingQsoDtlsRO "
				+ " WHERE legalEntityCode = :legalEntityCode"
				+ " AND auditTypeCode = :auditTypeCode "
				+ " AND auditId = :auditId  "
				+ " AND status <> :statusDelete  ");		
		session.createQuery(deleteQsoQuery.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("auditId", auditId)
				.setParameter("statusDelete", BankAuditConstant.STATUS_DEL)
				.executeUpdate();
	}
	
	@Override
	public void deleteAuditGradingQsoDtlsHO(Integer legalEntityCode, String auditTypeCode, String auditId){
		logger.info("Inside deleteAuditGradingQsoDtlsHO .. "+legalEntityCode +" :: "+auditTypeCode );	 
		Session session = getSession(); 
		
		StringBuilder deleteQsoQuery = new StringBuilder(" DELETE FROM AuditGradingQsoDtlsHO "
				+ " WHERE legalEntityCode = :legalEntityCode"
				+ " AND auditTypeCode = :auditTypeCode "
				+ " AND auditId = :auditId "
				+ " AND status <> :statusDelete   ");		
		session.createQuery(deleteQsoQuery.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("auditId", auditId)
				.setParameter("statusDelete", BankAuditConstant.STATUS_DEL)
				.executeUpdate();
	}	
	
	@Override
	public AuditGradingBasicDtlsDto getAuditDetails(Integer legalEntityCode, String auditTypeCode, String auditId){ 
		
		Session session = getSession();		    
		session.setDefaultReadOnly(true);  
		 
		String sqlQuery = "Select "
				+ "  entity.unit_name, "    	//0
				+ "  sch.financial_year as finYear, " //1
				+ "  entity.parent_unit_code, " 	//2
				+ "  sch.report_dispatch_date, "	//3
				+ "  address.state_name, "			//4 				
				+ "  concat(ifnull(u.first_name,''),' ',ifnull(u.middle_name,''),' ',ifnull(u.last_name,'')),  "//5
				+ "  u.designation, "				//6
				+ "  (SELECT e1.unit_name   "
						+ " FROM maint_entity e1    "
						+ " WHERE e1.unit_code = entity.parent_unit_code LIMIT 1) AS parent_unit_name, "  	//7
				+ "  (SELECT e2.unit_name  "
						+ " FROM maint_entity e1, maint_entity e2   "
						+ " WHERE e1.unit_code = entity.parent_unit_code "
						+ " AND e1.parent_unit_code = e2.unit_code LIMIT 1) AS parent_of_parent_unit_name, " //8      
				+ "  sch1.financial_year, " //9	
				+ "  u.user_id " //10	
				+ "  from audit_schedule sch   "			
				+ "  INNER JOIN maint_entity entity on sch.unit_id = entity.unit_code  "
				+ "  INNER JOIN audit_team team on sch.audit_id = team.audit_id and team.user_type = 'L' "
				+ "  INNER JOIN user u on u.user_id = team.user_id   "	
				+ "  LEFT JOIN  audit_schedule sch1 on sch.prev_audit_id = sch1.audit_id AND  sch.legal_entity_code = sch1.legal_entity_code "
				+ "  LEFT JOIN maint_address address on entity.city = address.city_code "
						+ "  and entity.district = address.district_code and sch.legal_entity_code = address.legal_entity_code " 
				+ "  Where sch.legal_entity_code = entity.legal_entity_code "  
				+ "  and sch.legal_entity_code = team.legal_entity_code  "	
				+ "  and sch.legal_entity_code = u.legal_entity_code  "					
				+ "  and sch.legal_entity_code = :legalEntityCode "
				+ "  and sch.audit_type_code = :auditTypeCode  "
				+ "  and sch.audit_id = :auditId ";
		 
		Query query=session.createSQLQuery(sqlQuery);
		List<AuditGradingBasicDtlsDto> auditGradingBasicDtlsDto = query.setResultTransformer(new ResultTransformer() {
			
			@Override
			public Object transformTuple(Object[] tuple, String[] aliases) {
				AuditGradingBasicDtlsDto auditGradingBasicDtlsDto = new AuditGradingBasicDtlsDto(); 
				
				auditGradingBasicDtlsDto.setAuditTypeCode(auditTypeCode);
				if(tuple[0]!=null){auditGradingBasicDtlsDto.setBankName(tuple[0].toString());}
				if(tuple[7]!=null){auditGradingBasicDtlsDto.setRoName(tuple[7].toString());}
				if(tuple[3]!=null){auditGradingBasicDtlsDto.setDispatchDate(tuple[3]!=null?(BankAuditUtil.parseDateToString((Date)tuple[3], "dd-MM-yyyy")):"");}
				if(tuple[5]!=null){auditGradingBasicDtlsDto.setPio(tuple[10].toString()+"-"+tuple[5].toString());}
				if(tuple[6]!=null){auditGradingBasicDtlsDto.setPioDesignation(tuple[6].toString());}				
				if(tuple[1]!=null){auditGradingBasicDtlsDto.setReferenceDatePresentInspection(tuple[1]!=null?(BankAuditUtil.parseDateToString((Date)tuple[1], "dd-MM-yyyy")):"");}
				if(tuple[9]!=null){auditGradingBasicDtlsDto.setReferenceDatePreviousInspection(tuple[9]!=null?(BankAuditUtil.parseDateToString((Date)tuple[9], "dd-MM-yyyy")):"");}				
				return auditGradingBasicDtlsDto;
			}
			
			@Override
			public List transformList(List collection) { 
				return collection;
			}
		}).setParameter("legalEntityCode", legalEntityCode)
		.setParameter("auditTypeCode", auditTypeCode)
		.setParameter("auditId", auditId)
		.list(); 		
		
		return auditGradingBasicDtlsDto.get(0);
	}
	
	@Override
	public MaintGradingParamScoreMatrix getGradingParamScoreMatrixValue(Integer legalEntityCode, String auditTypeCode, Double score){
		Session session = getSession();		    
		session.setDefaultReadOnly(true);  
		
		StringBuilder gradingScore = new StringBuilder(" Select gradeScore as gradeScoreMattrix From MaintGradingParamScoreMatrix gradeScore "
				+ " WHERE legalEntityCode = :legalEntityCode"
				+ " AND auditTypeCode = :auditTypeCode "
				+ " AND :score >= minScore AND :score < maxScore  ");		
		MaintGradingParamScoreMatrix  maintGradingParamScoreMatrix = (MaintGradingParamScoreMatrix) session.createQuery(gradingScore.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("score", score).uniqueResult();
		
		return maintGradingParamScoreMatrix;
	}
	
	@Override
	public List<AuditGradingParamCompositeRatingDtls> getCompositeRatingCategoryScore(Integer legalEntityCode, String auditTypeCode, String auditId){
		Session session = getSession();		    
		session.setDefaultReadOnly(true);  
		 	 
		String queryString = "SELECT rating, paramCompo "
				+ " From  MaintGradingParamCompositeRatingDtls paramCompo  "
				+ " LEFT JOIN AuditRatingCompositeCategoryScore rating  "
				+ " ON paramCompo.legalEntityCode = rating.legalEntityCode AND paramCompo.auditTypeCode = rating.auditTypeCode "
				+ " AND paramCompo.displayValueKeyRO = rating.categoryCode AND rating.auditId = :auditId  "
				+ " where paramCompo.legalEntityCode =:legalEntityCode "
				+ " and paramCompo.auditTypeCode = :auditTypeCode "
				+ "   "; 
		
		List<AuditGradingParamCompositeRatingDtls> auditGradingParamCompositeRatingDtlsList = session.createQuery(queryString)
		.setParameter("legalEntityCode", legalEntityCode)
		.setParameter("auditTypeCode", auditTypeCode)
		.setParameter("auditId", auditId).setResultTransformer(new ResultTransformer() {			
			@Override
			public Object transformTuple(Object[] tuple, String[] aliases) {
				AuditGradingParamCompositeRatingDtls auditGradingParamCompositeRatingDtls = new AuditGradingParamCompositeRatingDtls(); 
				if(tuple[0] != null ){					   
					AuditRatingCompositeCategoryScore auditRatingCompositeCategoryScore = (AuditRatingCompositeCategoryScore) tuple[0];
					MaintGradingParamCompositeRatingDtls maintGradingParamCompositeRatingDtls = (MaintGradingParamCompositeRatingDtls) tuple[1];					
					
					auditGradingParamCompositeRatingDtls.setLegalEntityCode(legalEntityCode);
					auditGradingParamCompositeRatingDtls.setAuditTypeCode(auditTypeCode);
					auditGradingParamCompositeRatingDtls.setAuditId(auditId);					
					auditGradingParamCompositeRatingDtls.setCategoryRo(auditRatingCompositeCategoryScore.getCategoryRatingAftrOverlay());
					auditGradingParamCompositeRatingDtls.setScoreRo(auditRatingCompositeCategoryScore.getCategoryPrcntgAftrOverlay());					
					auditGradingParamCompositeRatingDtls.setParamName(maintGradingParamCompositeRatingDtls.getParamName());
					auditGradingParamCompositeRatingDtls.setDispSeqNo(maintGradingParamCompositeRatingDtls.getDispSeqNo());
					auditGradingParamCompositeRatingDtls.setSlNo(maintGradingParamCompositeRatingDtls.getSlNo());
					auditGradingParamCompositeRatingDtls.setDisplayValueKeyRO(maintGradingParamCompositeRatingDtls.getDisplayValueKeyRO());
				}else  { 
					MaintGradingParamCompositeRatingDtls maintGradingParamCompositeRatingDtls = (MaintGradingParamCompositeRatingDtls) tuple[1];					
					
					auditGradingParamCompositeRatingDtls.setLegalEntityCode(legalEntityCode);
					auditGradingParamCompositeRatingDtls.setAuditTypeCode(auditTypeCode);
					auditGradingParamCompositeRatingDtls.setAuditId(auditId);					
					auditGradingParamCompositeRatingDtls.setCategoryRo("");
					auditGradingParamCompositeRatingDtls.setScoreRo("");					
					auditGradingParamCompositeRatingDtls.setParamName(maintGradingParamCompositeRatingDtls.getParamName());
					auditGradingParamCompositeRatingDtls.setDispSeqNo(maintGradingParamCompositeRatingDtls.getDispSeqNo());
					auditGradingParamCompositeRatingDtls.setSlNo(maintGradingParamCompositeRatingDtls.getSlNo());
					auditGradingParamCompositeRatingDtls.setDisplayValueKeyRO(maintGradingParamCompositeRatingDtls.getDisplayValueKeyRO());
				}
					
				return auditGradingParamCompositeRatingDtls;
			}
			
			@Override
			public List transformList(List collection) { 
				return collection;
			}
		}).list();
		
		return auditGradingParamCompositeRatingDtlsList; 
		
	}
	
	@Override
	public AuditRating getAuditRating(Integer legalEntityCode, String auditTypeCode, String auditId){
		Session session = getSession();		    
		session.setDefaultReadOnly(true);		 	
		//get overall(total)
		String queryString = "From  AuditRating rating "
				+ " where legalEntityCode =:legalEntityCode "
				+ " and auditTypeCode = :auditTypeCode "
				+ " and auditId = :auditId  ";
		
		AuditRating auditRating = (AuditRating) session.createQuery(queryString)
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("auditId", auditId).uniqueResult();
		return auditRating; 		
	}
	
	@Override
	public  AuditGradingDto getMyAuditGradingCompliance(Integer legalEntityCode, String auditTypeCode, String auditId){
		Session session = getSession();		    
		session.setDefaultReadOnly(true);
		
		/**
		 * Get Audit Grading Basic Details
		 */
		StringBuilder gradingBasicQuery = new StringBuilder(" Select basicDtls as gradingBasicDtls From AuditGradingQsnBasicDtls basicDtls "
				+ " WHERE legalEntityCode = :legalEntityCode"
				+ " AND auditTypeCode = :auditTypeCode "
				+ " AND auditId = :auditId "
				+ " ORDER BY slNo ASC ");	 
		List<AuditGradingQsnBasicDtls> auditGradingQsnBasicDtlsList = session.createQuery(gradingBasicQuery.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("auditId", auditId).list();
		
		Map<String, AuditGradingQsnBasicDtls> auditGradingQsnBasicDtlsMap = new HashMap<String, AuditGradingQsnBasicDtls>();
		for (AuditGradingQsnBasicDtls auditGradingQsnBasicDtls : auditGradingQsnBasicDtlsList) {			
			auditGradingQsnBasicDtlsMap.put(auditGradingQsnBasicDtls.getDispSeqNo(), auditGradingQsnBasicDtls);
		}
		
		/**
		 * Get Audit Grading QSO Details RO
		 */
		StringBuilder gradingQsoQueryRO = new StringBuilder(" Select qsoDtls as gradingQsoDtls From AuditGradingQsoDtlsRO qsoDtls "
				+ " WHERE legalEntityCode = :legalEntityCode"
				+ " AND auditTypeCode = :auditTypeCode "
				+ " AND auditId = :auditId "
				+ " AND status <> :statusDelete "
				+ " ORDER BY cast(qsoSequenceId  as int)   ASC ");		
		List<AuditGradingQsoDtlsRO> auditGradingQsoDtlsROList = session.createQuery(gradingQsoQueryRO.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("auditId", auditId)
				.setParameter("statusDelete", BankAuditConstant.STATUS_DEL).list();
				
		/**
		 * Get Audit Grading QSO Details HO
		 */
		StringBuilder gradingQsoQueryHO = new StringBuilder(" Select qsoDtls as gradingQsoDtls From AuditGradingQsoDtlsHO qsoDtls "
				+ " WHERE legalEntityCode = :legalEntityCode"
				+ " AND auditTypeCode = :auditTypeCode "
				+ " AND auditId = :auditId "
				+ " AND status <> :statusDelete "
				+ " ORDER BY cast(qsoSequenceId  as int)   ASC ");		
		List<AuditGradingQsoDtlsHO> auditGradingQsoDtlsHOList = session.createQuery(gradingQsoQueryHO.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("auditId", auditId)
				.setParameter("statusDelete", BankAuditConstant.STATUS_DEL).list();
		
		AuditGradingDto  auditGradingDto = new AuditGradingDto(); 
		auditGradingDto.setAuditGradingQsoDtlsROList(auditGradingQsoDtlsROList);
		auditGradingDto.setAuditGradingQsoDtlsHOList(auditGradingQsoDtlsHOList);
		auditGradingDto.setAuditGradingQsnBasicDtlsMap(auditGradingQsnBasicDtlsMap); 
		
		return auditGradingDto; 
		
	}
	
	
}
