package com.bankaudit.dao;

import java.lang.reflect.InvocationTargetException;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.stream.Collectors;

import org.apache.commons.beanutils.BeanUtils;
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.process.MaintRatingModelDto;
import com.bankaudit.model.MaintFormula;
import com.bankaudit.model.MaintRatingCategory;
import com.bankaudit.model.MaintRatingGrade;
import com.bankaudit.model.MaintRatingModel;
import com.bankaudit.model.MaintRatingModelOverlay;
import com.bankaudit.model.MaintRatingModelOverlayWrk;
import com.bankaudit.model.MaintRatingModelWrk;
import com.bankaudit.model.MaintRatingParameterInputs;
import com.bankaudit.model.MaintRatingParameterInputsWrk;
import com.bankaudit.model.MaintRatingParameterMapping;
import com.bankaudit.model.MaintRatingParameterMappingWrk;
import com.bankaudit.model.MaintRatingParameterRange;
import com.bankaudit.model.MaintRatingParameterRangeWrk;
import com.bankaudit.model.MaintRatingParameterDtls;
import com.bankaudit.model.MaintRatingParameterDtlsWrk;
import com.bankaudit.model.MaintRatingSubCategory;
import com.bankaudit.process.model.AuditSchedule;
import com.bankaudit.process.model.IfcRatingParameterMapping;
import com.bankaudit.util.BankAuditUtil; 

@Repository("maintRatingModelDao")
public class MaintRatingModelDaoImpl extends AbstractDao  implements MaintRatingModelDao {

	static final Logger logger = Logger.getLogger(MaintRatingModelDaoImpl.class);
	
	@Override
	public Long countRatingModel(Integer legalEntityCode){
		
		Session session=getSession();
		StringBuilder selectQry = new StringBuilder();
		  				
		selectQry.append("Select (	"
				+ " ( Select count(rm.rm_code) From maint_rating_model rm where  rm.legal_entity_code = :legalEntityCode ) +  "
				+ " ( Select count(rmWrk.rm_code) From maint_rating_model_wrk rmWrk where  rmWrk.legal_entity_code = :legalEntityCode ) "
			+ " )  as countVal  ");		
		return  ((BigInteger) session.createNativeQuery(selectQry.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.uniqueResult()).longValue() ;
		
	}
	
	@Override
	public Long countRatingModelParameter(Integer legalEntityCode){
		Session session=getSession();
		StringBuilder selectQry = new StringBuilder();
		 
		selectQry.append("Select ("
								+ " (Select count(param.param_code) From maint_rating_parameter_dtls param  where  param.legal_entity_code = :legalEntityCode) + "
								+ " (Select count(paramWrk.param_code) From maint_rating_parameter_dtls_wrk paramWrk  where  paramWrk.legal_entity_code = :legalEntityCode)"
							+ " ) AS countVal");			 
				
		return   ((BigInteger) session.createNativeQuery(selectQry.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.uniqueResult()).longValue() ;
	}
	
	@Override
	public List<IfcRatingParameterMapping> getInputValues(Integer legalEntityCode, String auditTypeCode){
		
		Session session=getSession();
		StringBuilder selectQry = new StringBuilder();
		selectQry.append(" Select DISTINCT input From IfcRatingParameterMapping input "
				+ " where "
				+ " input.legalEntityCode = :legalEntityCode ");
		if(!BankAuditUtil.isEmptyString(auditTypeCode))  {
			selectQry.append(" and input.auditTypeCode = :auditTypeCode ");	
		}		 
		 	
			
	    Query  query = session.createQuery(selectQry.toString())
			.setParameter("legalEntityCode", legalEntityCode);
	    if(!BankAuditUtil.isEmptyString(auditTypeCode)){
	    	query.setParameter("auditTypeCode", auditTypeCode);	    
	    }
	    
		List<IfcRatingParameterMapping> inputList = query.list();
		return inputList;
	}
	
	@Override
	public List<MaintFormula> getFactor(Integer legalEntityCode, Integer noOfInputs ){
		
		Session session=getSession();
		StringBuilder selectQry = new StringBuilder();
		selectQry.append(" From MaintFormula factr "
				+ " where "
				+ " factr.legalEntityCode = :legalEntityCode ");
		if( noOfInputs !=null && noOfInputs !=0 && "".equals(noOfInputs+""))  {
			selectQry.append(" and factr.noOfInputs = :noOfInputs ");	
		}			 		 
			
	    Query  query = session.createQuery(selectQry.toString())
			.setParameter("legalEntityCode", legalEntityCode);
	    if( noOfInputs !=null && noOfInputs !=0 && "".equals(noOfInputs+"")){
	    	query.setParameter("noOfInputs", noOfInputs);	    
	    }
		List<MaintFormula> factor = query.list();
		return factor;
	}
		
	@Override
	public List<MaintRatingGrade> getGrades(Integer legalEntityCode, String rmCode){
		//Get constraints
		Session session=getSession();
		StringBuilder selectQry = new StringBuilder();
		selectQry.append("   Select legal_entity_code, rm_code, min_per, max_per,"
				+ "  rating_internal, auth_rej_remarks, "
				+ " entity_status, status, maker, maker_timestamp, checker, "
				+ " checker_timestamp, businessdate_timestamp, sysdate_timestamp, grade_code, grade_desc"
				+ " From maint_rating_grade grade "
				 +" where "
				 +" grade.legal_entity_code = :legalEntityCode "
				 +" and "
				 +" (CASE "
				 +" 	 WHEN  :rmCode NOT IN (Select gradeRm.rm_code FROM maint_rating_grade gradeRm "
				 +" 					 where gradeRm.legal_entity_code = :legalEntityCode) "
				 +" 	 THEN grade.rm_code = 'RM'  "
				 +" ELSE grade.rm_code= :rmCode END)  ");			 
			
	    Query  query = session.createNativeQuery(selectQry.toString())
			.setParameter("legalEntityCode", legalEntityCode)
			.setParameter("rmCode", rmCode)
			.setResultTransformer(new ResultTransformer() {
				
				@Override
				public Object transformTuple(Object[] tuple, String[] aliases) {
					 							
					MaintRatingGrade maintRatingGrade = new MaintRatingGrade();
					if(tuple[0] != null) maintRatingGrade.setLegalEntityCode(legalEntityCode);
					if(tuple[1] != null) maintRatingGrade.setRmCode(tuple[1].toString());					
					if(tuple[2] != null) maintRatingGrade.setMinPer(Integer.parseInt(tuple[2].toString()));				
					if(tuple[3] != null) maintRatingGrade.setMaxPer(Integer.parseInt(tuple[3].toString()));
				
					if(tuple[4] != null) maintRatingGrade.setRatingInternal(Integer.parseInt(tuple[4].toString()));
	    
					if(tuple[5] != null) maintRatingGrade.setAuthRejRemarks(tuple[5].toString());
					if(tuple[6] != null) maintRatingGrade.setEntityStatus(tuple[6].toString());
					if(tuple[7] != null) maintRatingGrade.setStatus(tuple[7].toString());
				
					if(tuple[8] != null) maintRatingGrade.setMaker(tuple[8].toString());
					if(tuple[9] != null) maintRatingGrade.setMakerTimestamp((Date) tuple[9]);
					
					if(tuple[10] != null) maintRatingGrade.setChecker(tuple[10].toString());
					if(tuple[11] != null) maintRatingGrade.setCheckerTimestamp((Date)tuple[11]);
					if(tuple[12] != null) maintRatingGrade.setBusinessdateTimestamp((Date)tuple[11]);
					if(tuple[13] != null) maintRatingGrade.setSysdateTimestamp((Date)tuple[11]);
					
					if(tuple[14] != null) maintRatingGrade.setGradeCode(tuple[14].toString());
					if(tuple[15] != null) maintRatingGrade.setGradeDesc(tuple[15].toString());
					
					return maintRatingGrade;
				}
				
				@Override
				public List transformList(List collection) {
					// TODO Auto-generated method stub
					return collection;
				}
			}); ;	    
		
		List<MaintRatingGrade> grades = query.list();
		return grades;
	}
	
	@Override
	public List<MaintRatingCategory> getRatingCategory(Integer legalEntityCode){
		Session session=getSession();
		StringBuilder selectQry = new StringBuilder();
		selectQry.append(" From MaintRatingCategory cat "
				+ " where "
				+ " cat.legalEntityCode = :legalEntityCode "
				+ " and cat.status = :status "
				+ " and cat.entityStatus = :entityStatus   ");	
		
		 
			
	    Query  query = session.createQuery(selectQry.toString())
			.setParameter("legalEntityCode", legalEntityCode)
			.setParameter("status", BankAuditConstant.STATUS_AUTH)
			.setParameter("entityStatus", BankAuditConstant.STATUS_ACTIVE);
	    
		
		List<MaintRatingCategory> category = query.list();
		return category;
	}
	
	@Override
	public List<MaintRatingSubCategory> getRatingSubCategory(Integer legalEntityCode, String categoryCode){
		Session session=getSession();
		StringBuilder selectQry = new StringBuilder();
		selectQry.append(" From MaintRatingSubCategory catSub "
				+ " where "
				+ " catSub.legalEntityCode = :legalEntityCode "
				+ " and catSub.status = :status "
				+ " and catSub.entityStatus = :entityStatus "	);
		if(!BankAuditUtil.isEmptyString(categoryCode)){
			selectQry.append( " and catSub.categoryCode = :categoryCode ");	
		}
		
	    Query  query = session.createQuery(selectQry.toString())
			.setParameter("legalEntityCode", legalEntityCode)
			.setParameter("status", BankAuditConstant.STATUS_AUTH)
			.setParameter("entityStatus", BankAuditConstant.STATUS_ACTIVE);
	    if(!BankAuditUtil.isEmptyString(categoryCode)){
	    	query.setParameter("categoryCode", categoryCode);
	    }
		List<MaintRatingSubCategory> categorySub = query.list();
		return categorySub;
	}
	
	@Override
	public DataTableResponse getAllRatingModel(Integer legalEntityCode, String search,Integer orderColumn, String orderDirection, Integer page, Integer size){
		
		Session session=getSession();
		StringBuilder selectQry = new StringBuilder();
		 
		selectQry.append(" Select  legal_entity_code, rm_code, rm_name, rm_desc, model_status, auth_rej_remarks,"
				+ " entity_status, status, "
				+ " (Select concat(u.user_id , '-' ,ifnull(u.first_name,''), ' ',ifnull(u.middle_name,''),' ',ifnull(u.last_name,'')) "
						+ "	from  user u where u.legal_entity_code =ratingMod.legal_entity_code "
						+ " and u.user_id=ratingMod.maker) as makers , " 
				+ " maker_timestamp, "
				+ " (Select concat(u.user_id , '-' ,ifnull(u.first_name,''), ' ',ifnull(u.middle_name,''),' ',ifnull(u.last_name,'')) "
						+ "	from  user u where u.legal_entity_code =ratingMod.legal_entity_code "
						+ " and u.user_id=ratingMod.checker) as checkers , " 
				+ " checker_timestamp, businessdate_timestamp, sysdate_timestamp "
				+ " FROM "				
						+ " ( Select legal_entity_code, rm_code, rm_name, rm_desc, model_status, auth_rej_remarks,"
						+ " entity_status, status, maker, maker_timestamp, checker,"
						+ " checker_timestamp, businessdate_timestamp, sysdate_timestamp From maint_rating_model rm "
						+ " where "
						+ " rm.legal_entity_code = :legalEntityCode  "
					+ " UNION ALL "
						+ " Select legal_entity_code, rm_code, rm_name, rm_desc, model_status, auth_rej_remarks,"
						+ " entity_status, status, maker, maker_timestamp, checker,"
						+ " checker_timestamp, businessdate_timestamp, sysdate_timestamp  From maint_rating_model_wrk rm "
						+ " where "
						+ " rm.legal_entity_code = :legalEntityCode   )"
				+ " ratingMod ");			 
				
			if(!BankAuditUtil.isEmptyString(search)){
				selectQry.append("");
			}
	    Query  query = session.createNativeQuery(selectQry.toString())
			.setParameter("legalEntityCode", legalEntityCode)
			.setResultTransformer(new ResultTransformer() {
				
				@Override
				public Object transformTuple(Object[] tuple, String[] aliases) {
					
					MaintRatingModelWrk MaintRatingModelWrk = new MaintRatingModelWrk();
					if(tuple[0] != null) MaintRatingModelWrk.setLegalEntityCode(legalEntityCode);
					if(tuple[1] != null) MaintRatingModelWrk.setRmCode(tuple[1].toString());
					if(tuple[2] != null) MaintRatingModelWrk.setRmName(tuple[2].toString());					
					if(tuple[3] != null) MaintRatingModelWrk.setRmDesc(tuple[3].toString());
				
					if(tuple[4] != null) MaintRatingModelWrk.setModelStatus(tuple[4].toString());
	    
					if(tuple[5] != null) MaintRatingModelWrk.setAuthRejRemarks(tuple[5].toString());
					if(tuple[6] != null) MaintRatingModelWrk.setEntityStatus(tuple[6].toString());
					if(tuple[7] != null) MaintRatingModelWrk.setStatus(tuple[7].toString());
				
					if(tuple[8] != null) MaintRatingModelWrk.setMaker(tuple[8].toString());
					if(tuple[9] != null) MaintRatingModelWrk.setMakerTimestamp((Date) tuple[9]);
					
					if(tuple[10] != null) MaintRatingModelWrk.setChecker(tuple[10].toString());
					if(tuple[11] != null) MaintRatingModelWrk.setCheckerTimestamp((Date)tuple[11]);
					if(tuple[12] != null) MaintRatingModelWrk.setBusinessdateTimestamp((Date)tuple[11]);
					if(tuple[13] != null) MaintRatingModelWrk.setSysdateTimestamp((Date)tuple[11]);
					
					return MaintRatingModelWrk;
				}
				
				@Override
				public List transformList(List collection) {
					// TODO Auto-generated method stub
					return collection;
				}
			}); 
	   
	    
	    
	    List<AuditSchedule> tmpAuditSchedule =query.list(); 
		Long totalRecords=null;
		try {
			totalRecords = (long)tmpAuditSchedule.size();
		}catch(Exception e) {
			totalRecords = (long)0;
		}
		
		List<MaintRatingModelWrk> ratingModel=query.setFirstResult((page*size)).setMaxResults(size).list();
		
		DataTableResponse dataTableResponse=new DataTableResponse();
		dataTableResponse.setData(ratingModel);
		dataTableResponse.setRecordsTotal(totalRecords);
		dataTableResponse.setRecordsFiltered(totalRecords);
		
		return dataTableResponse;
	}
	
	/*@Override
	public MaintRatingModelDto getRatingModelParameterDetails(Integer legalEntityCode, String rmCode, String status){		
		
		MaintRatingModelDto maintRatingModelDto =  new MaintRatingModelDto();
		
		//get Rating Model details
		List<MaintRatingModelWrk> ratingModelList = getRatingModelBy(legalEntityCode, rmCode, status);
		if(ratingModelList!=null && ratingModelList.size()>0){
			maintRatingModelDto.setMaintRatingModelWrk(ratingModelList.get(0));
			
			List<MaintRatingParameterDtlsWrk> parametersDetailsWrkList = getRatingParameterDetailsBy(legalEntityCode, rmCode, status);
			List<String> paramCode = parametersDetailsWrkList.stream().map(MaintRatingParameterDtlsWrk::getParamCode) .collect(Collectors.toList());
			maintRatingModelDto.setMaintRatingPrameterDtlsWrkList(parametersDetailsWrkList);	
			
			maintRatingModelDto.setMaintRatingParameterRangeWrkList(getRatingParameterRangeBy(legalEntityCode, rmCode, paramCode, status));
			maintRatingModelDto.setMaintRatingParameterCategoryMappingWrkList(getRatingParameterCategoryMappingBy(legalEntityCode, rmCode, paramCode, status));
			
			maintRatingModelDto.setMaintRatingModelOverlayWrkList(getRatingModelOverlayBy(legalEntityCode, rmCode, status)); 
		}
		
		return maintRatingModelDto;
	}*/
	
	@Override
	public List<MaintRatingModelWrk>  getRatingModelBy(Integer legalEntityCode, String rmCode, String status){	
		
		Session session=getSession();
		StringBuilder selectQry = new StringBuilder();
		if(!status.equalsIgnoreCase(BankAuditConstant.STATUS_AUTH)){
			selectQry.append("From MaintRatingModelWrk rm ");
		}else{
			selectQry.append("From MaintRatingModel rm ");
		}
		selectQry.append("  where rm.rmCode = :rmCode "
				+ " and rm.legalEntityCode = :legalEntityCode ");		 
		
		@SuppressWarnings("deprecation")
		Query  qry = session.createQuery(selectQry.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("rmCode", rmCode)
				.setResultTransformer(new ResultTransformer() {
					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						MaintRatingModelWrk maintRatingModelWrk=null; 
						if(tuple[0]!=null){
							if(status.equalsIgnoreCase(BankAuditConstant.STATUS_AUTH)){
								MaintRatingModel maintRatingModel = (MaintRatingModel)tuple[0]; 
								try {
									maintRatingModelWrk =  new MaintRatingModelWrk();								
									BeanUtils.copyProperties(maintRatingModelWrk, maintRatingModel);
								} catch (IllegalAccessException | InvocationTargetException e) {  
								}
							}else{
								maintRatingModelWrk=(MaintRatingModelWrk)tuple[0]; 
							}
						}
						return maintRatingModelWrk;
					}
					@Override
					public List transformList(List collection) {
						return collection;
					}
				});			
		return qry.list(); 		
	}
	
	@Override
	public List<MaintRatingParameterDtlsWrk>  getRatingParameterDetailsBy(Integer legalEntityCode, String rmCode, String status){	
		
		Session session=getSession();
		StringBuilder selectQry = new StringBuilder();
		if(!status.equalsIgnoreCase(BankAuditConstant.STATUS_AUTH)){
			selectQry.append("From MaintRatingParameterDtlsWrk paramDtls ");
		}else{
			selectQry.append("From MaintRatingParameterDtls paramDtls ");
		}
		selectQry.append("  where paramDtls.rmCode = :rmCode "
				+ " and paramDtls.legalEntityCode = :legalEntityCode ");		 
		
		@SuppressWarnings("deprecation")
		Query  qry = session.createQuery(selectQry.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("rmCode", rmCode)
				.setResultTransformer(new ResultTransformer() {
					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						MaintRatingParameterDtlsWrk maintRatingParameterDtlsWrk=null; 
						if(tuple[0]!=null){
							if(status.equalsIgnoreCase(BankAuditConstant.STATUS_AUTH)){
								MaintRatingParameterDtls maintRatingParameterDtls = (MaintRatingParameterDtls)tuple[0]; 
							    
								MaintRatingParameterInputsWrk  maintRatingParameterInputsWrk  = 
														getRatingParameterInputsBy(legalEntityCode, rmCode, maintRatingParameterDtls.getParamCode(), status);
								try {
									maintRatingParameterDtlsWrk =  new MaintRatingParameterDtlsWrk();								
									BeanUtils.copyProperties(maintRatingParameterDtlsWrk, maintRatingParameterDtls);
									
									if(maintRatingParameterInputsWrk!=null && !BankAuditUtil.isEmptyString(maintRatingParameterDtlsWrk.getParamCode())) {
										List<String> ratingInputs = new ArrayList<>();
										for (int len=1; len<=15; len++) {			    
											String input = "input"+String.format("%02d", len );				 
											Object inputObject = org.apache.commons.beanutils.PropertyUtils.getSimpleProperty(maintRatingParameterInputsWrk, input);
											if(inputObject!=null && !"".equals(inputObject.toString())){										 
												ratingInputs.add((String) inputObject.toString());
											}   					
										}
										maintRatingParameterDtlsWrk.setMaintRatingParameterInputsWrkList(ratingInputs);
									}
									
								} catch (IllegalAccessException | InvocationTargetException | NoSuchMethodException e) { 
									throw new RuntimeException(e);
								}  catch (SecurityException e) { 
									throw new RuntimeException(e);
								}
							}else{
								maintRatingParameterDtlsWrk=(MaintRatingParameterDtlsWrk)tuple[0]; 
								
								MaintRatingParameterInputsWrk  maintRatingParameterInputsWrk  = 
												getRatingParameterInputsBy(legalEntityCode, rmCode, maintRatingParameterDtlsWrk.getParamCode(), status);
								if(maintRatingParameterInputsWrk!=null && !BankAuditUtil.isEmptyString(maintRatingParameterDtlsWrk.getParamCode())) {
									List<String> ratingInputs = new ArrayList<>();
									for (int len=1; len<=15; len++) {	
										try{
											String input = "input"+String.format("%02d", len );				 
											Object inputObject = org.apache.commons.beanutils.PropertyUtils.getSimpleProperty(maintRatingParameterInputsWrk, input);
											if(inputObject!=null && !"".equals(inputObject.toString())){ 
												ratingInputs.add((String)inputObject.toString());
											} 
										} catch (IllegalAccessException | InvocationTargetException | NoSuchMethodException |   SecurityException e) {  
											throw new RuntimeException(e);
										}
									}
									maintRatingParameterDtlsWrk.setMaintRatingParameterInputsWrkList(ratingInputs);
								}
							}
						}
						return maintRatingParameterDtlsWrk;
					}
					@Override
					public List transformList(List collection) {
						return collection;
					}
				});			
		return qry.list(); 		
	}
	
	@Override
	public MaintRatingParameterInputsWrk getRatingParameterInputsBy(Integer legalEntityCode, String rmCode, String paramCode, String status){	
		 
		Session session=getSession();
		StringBuilder selectQry = new StringBuilder();
		if(!status.equalsIgnoreCase(BankAuditConstant.STATUS_AUTH)){
			selectQry.append("From MaintRatingParameterInputsWrk paramInputs ");
		}else{
			selectQry.append("From MaintRatingParameterInputs paramInputs ");
		}
		selectQry.append("  where paramInputs.legalEntityCode = :legalEntityCode "
				+ " and  paramInputs.rmCode = :rmCode "
				+ " and  paramInputs.paramCode = :paramCode ");		 
		
		@SuppressWarnings("deprecation")
		Query  qry = session.createQuery(selectQry.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("rmCode", rmCode)
				.setParameter("paramCode", paramCode)
				.setResultTransformer(new ResultTransformer() {
					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						MaintRatingParameterInputsWrk MaintRatingParameterInputsWrk=null; 
						if(tuple[0]!=null){
							if(status.equalsIgnoreCase(BankAuditConstant.STATUS_AUTH)){
								MaintRatingParameterInputs maintRatingParameterInputs = (MaintRatingParameterInputs)tuple[0]; 
								try {
									MaintRatingParameterInputsWrk =  new MaintRatingParameterInputsWrk();								
									BeanUtils.copyProperties(MaintRatingParameterInputsWrk, maintRatingParameterInputs);
								} catch (IllegalAccessException | InvocationTargetException e) {  
								}
							}else{
								MaintRatingParameterInputsWrk=(MaintRatingParameterInputsWrk)tuple[0]; 
							}
						}
						return MaintRatingParameterInputsWrk;
					}
					@Override
					public List transformList(List collection) {
						return collection;
					}
				});			
		return (MaintRatingParameterInputsWrk) qry.uniqueResult(); 		
	}
	
	@Override
	public List<MaintRatingParameterRangeWrk> getRatingParameterRangeBy(Integer legalEntityCode, String rmCode, List<String> paramCode, String status){	
		 
		Session session=getSession();
		StringBuilder selectQry = new StringBuilder();
		if(!status.equalsIgnoreCase(BankAuditConstant.STATUS_AUTH)){
			selectQry.append("From MaintRatingParameterRangeWrk paramRange ");
		}else{
			selectQry.append("From MaintRatingParameterRange paramRange ");
		}
		selectQry.append("  where paramRange.legalEntityCode = :legalEntityCode "
				+ " and  paramRange.rmCode = :rmCode "
				+ " and  paramRange.paramCode in (:paramCode) ");		 
		
		@SuppressWarnings("deprecation")
		Query  qry = session.createQuery(selectQry.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("rmCode", rmCode)
				.setParameterList("paramCode", paramCode)
				.setResultTransformer(new ResultTransformer() {
					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						MaintRatingParameterRangeWrk maintRatingParameterRangeWrk=null; 
						if(tuple[0]!=null){
							if(status.equalsIgnoreCase(BankAuditConstant.STATUS_AUTH)){
								MaintRatingParameterRange maintRatingParameterRange = (MaintRatingParameterRange)tuple[0]; 
								try {
									maintRatingParameterRangeWrk =  new MaintRatingParameterRangeWrk();								
									BeanUtils.copyProperties(maintRatingParameterRangeWrk, maintRatingParameterRange);
								} catch (IllegalAccessException | InvocationTargetException e) {  
								}
							}else{
								maintRatingParameterRangeWrk=(MaintRatingParameterRangeWrk)tuple[0]; 
							}
						}
						return maintRatingParameterRangeWrk;
					}
					@Override
					public List transformList(List collection) {
						return collection;
					}
				});			
		return qry.list(); 		
	}
	
	@Override
	public List<MaintRatingParameterMappingWrk> getRatingParameterCategoryMappingBy(Integer legalEntityCode, String rmCode, List<String> paramCode, String status){	
		 
		Session session=getSession();
		StringBuilder selectQry = new StringBuilder();
		if(!status.equalsIgnoreCase(BankAuditConstant.STATUS_AUTH)){
			selectQry.append("From MaintRatingParameterMappingWrk paramCat ");
		}else{
			selectQry.append("From MaintRatingParameterMapping paramCat ");
		}
		selectQry.append("  where paramCat.legalEntityCode = :legalEntityCode "
				+ " and  paramCat.rmCode = :rmCode "
				+ " and  paramCat.paramCode in (:paramCode) ");		 
		
		@SuppressWarnings("deprecation")
		Query  qry = session.createQuery(selectQry.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("rmCode", rmCode)
				.setParameterList("paramCode", paramCode)
				.setResultTransformer(new ResultTransformer() {
					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						MaintRatingParameterMappingWrk maintRatingParameterCategoryMappingWrk=null; 
						if(tuple[0]!=null){
							if(status.equalsIgnoreCase(BankAuditConstant.STATUS_AUTH)){
								MaintRatingParameterMapping maintRatingParameterCategoryMapping = (MaintRatingParameterMapping)tuple[0]; 
								try {
									maintRatingParameterCategoryMappingWrk =  new MaintRatingParameterMappingWrk();								
									BeanUtils.copyProperties(maintRatingParameterCategoryMappingWrk, maintRatingParameterCategoryMapping);
								} catch (IllegalAccessException | InvocationTargetException e) {  
								}
							}else{
								maintRatingParameterCategoryMappingWrk=(MaintRatingParameterMappingWrk)tuple[0]; 
							}
						}
						return maintRatingParameterCategoryMappingWrk;
					}
					@Override
					public List transformList(List collection) {
						return collection;
					}
				});			
		return qry.list(); 		
	}
 
	@Override
	public List<MaintRatingModelOverlayWrk> getRatingModelOverlayBy(Integer legalEntityCode, String rmCode,   String status){	
		 
		Session session=getSession();
		StringBuilder selectQry = new StringBuilder();
		if(!status.equalsIgnoreCase(BankAuditConstant.STATUS_AUTH)){
			selectQry.append("From MaintRatingModelOverlayWrk overlay ");
		}else{
			selectQry.append("From MaintRatingModelOverlay overlay ");
		}
		selectQry.append("  where overlay.legalEntityCode = :legalEntityCode "
				+ "  and  overlay.rmCode = :rmCode  ");		 
		
		@SuppressWarnings("deprecation")
		Query  qry = session.createQuery(selectQry.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("rmCode", rmCode) 
				.setResultTransformer(new ResultTransformer() {
					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						MaintRatingModelOverlayWrk maintRatingModelOverlayWrk = null; 
						if(tuple[0]!=null){
							if(status.equalsIgnoreCase(BankAuditConstant.STATUS_AUTH)){
								MaintRatingModelOverlay maintRatingModelOverlay = (MaintRatingModelOverlay)tuple[0]; 
								try {
									maintRatingModelOverlayWrk =  new MaintRatingModelOverlayWrk();								
									BeanUtils.copyProperties(maintRatingModelOverlayWrk, maintRatingModelOverlay);
								} catch (IllegalAccessException | InvocationTargetException e) {  
								}
							}else{
								maintRatingModelOverlayWrk=(MaintRatingModelOverlayWrk)tuple[0]; 
							}
						}
						return maintRatingModelOverlayWrk;
					}
					@Override
					public List transformList(List collection) {
						return collection;
					}
				});			
		return qry.list(); 		
	}
 
	
	@Override
	public void deleteRatingModelBy(Integer legalEntityCode, String rmCode, String status){
		
		logger.info("called deleteRatingModelBy :: delete  rating model");
		
		Session session=getSession();
		StringBuilder deleteQry = new StringBuilder(); 
		deleteQry.append("Delete from  MaintRatingModelWrk rm ");
				/*+ " set rm.status= :status"
				+ " set rm.entityStatus = :status ");		 */
		deleteQry.append(" Where "
				+ " rm.legalEntityCode = :legalEntityCode "
				+ " and rm.rmCode = :rmCode  ");
		session.createQuery(deleteQry.toString())
		.setParameter("legalEntityCode", legalEntityCode)
		.setParameter("rmCode", rmCode) 
		//.setParameter("status", status)
		.executeUpdate();
		
	}
	
	@Override
	public void deleteRatingParameterDetailsBy(Integer legalEntityCode, String rmCode, String paramCode, String status){
		
		logger.info("called deleteRatingParameterDetailsBy :: delete  parameter details");
		
		Session session=getSession();
		StringBuilder deleteQry = new StringBuilder();

		deleteQry.append("Delete from MaintRatingParameterDtlsWrk paramtr");
				/*+ " set paramtr.status= :status "
				+ " set paramtr.entityStatus = :status ");*/
		 
		deleteQry.append(" Where "
				+ " paramtr.legalEntityCode = :legalEntityCode "
				+ " and paramtr.rmCode = :rmCode "
				+ " and paramtr.paramCode = :paramCode ");
		session.createQuery(deleteQry.toString())
		.setParameter("legalEntityCode", legalEntityCode)
		.setParameter("rmCode", rmCode)
		.setParameter("paramCode", paramCode) 
		.executeUpdate();		
		
	}
	
	@Override
	public void deleteRatingParameterInputsBy(Integer legalEntityCode, String rmCode, String paramCode, String status){
		
		logger.info("called deleteRatingParameterInputsBy :: delete  parameter inputs");
		
		Session session=getSession();
		StringBuilder deleteQry = new StringBuilder(); 
		deleteQry.append("Delete From MaintRatingParameterInputsWrk paramtrInput ");
				/*+ " set paramtrInput.status= :status"
				+ " set paramtrInput.entityStatus = :status  ");*/
		 
		deleteQry.append(" Where "
				+ " paramtrInput.legalEntityCode = :legalEntityCode "
				+ " and paramtrInput.rmCode = :rmCode "
				+ " and paramtrInput.paramCode = :paramCode " );
		session.createQuery(deleteQry.toString())
		.setParameter("legalEntityCode", legalEntityCode)
		.setParameter("rmCode", rmCode)
		.setParameter("paramCode", paramCode) 
		.executeUpdate();
	}
	
	@Override
	public void deleteRatingParameterInputsFromWrkBy(Integer legalEntityCode, String rmCode, String paramCode){
		
		logger.info("called deleteRatingParameterInputsFromWrkBy :: delete  parameter inputs before insert");
		
		Session session=getSession();
		StringBuilder deleteQryParamInput = new StringBuilder(); 
		deleteQryParamInput.append("Delete from MaintRatingParameterInputsWrk paramtr");
		 
		deleteQryParamInput.append(" Where "
				+ " paramtr.legalEntityCode = :legalEntityCode "
				+ " and paramtr.rmCode = :rmCode "
				+ " and paramtr.paramCode = :paramCode ");
		session.createQuery(deleteQryParamInput.toString())
		.setParameter("legalEntityCode", legalEntityCode)
		.setParameter("rmCode", rmCode)
		.setParameter("paramCode", paramCode)
		.executeUpdate();
	}
	
	@Override
	public void deleteRatingParameterRangeBy(Integer legalEntityCode, String rmCode, String paramCode, String id, String status){

		logger.info("called deleteRatingParameterRangeBy :: delete parameter score/range");
		
		Session session=getSession();
		StringBuilder deleteQry = new StringBuilder(); 
			deleteQry.append("Delete from  MaintRatingParameterRangeWrk score");
					/*+ " set score.status= :status"
					+ " set score.entityStatus = :status  ");*/
		    
		deleteQry.append(" Where "
				+ " score.legalEntityCode = :legalEntityCode "
				+ " and score.rmCode = :rmCode "
				+ " and score.paramCode = :paramCode ");
		if(!BankAuditUtil.isEmptyString(id)){
			deleteQry.append(" and id = :id ");
		}	
		Query qry = session.createQuery(deleteQry.toString())
		.setParameter("legalEntityCode", legalEntityCode)
		.setParameter("rmCode", rmCode)
		.setParameter("paramCode", paramCode);
		if(!BankAuditUtil.isEmptyString(id)){
			qry.setParameter("id", id);
		}
		qry.executeUpdate();
		
	}
	
	@Override
	public void deleteRatingParameterCategoryMappingBy(Integer legalEntityCode, String rmCode, String paramCode, String categoryCode, String subCategoryCode, String status){
		
		logger.info("called deleteRatingParameterCategoryMappingBy :: detete parameter category details");
		
		Session session=getSession();
		StringBuilder deleteQry = new StringBuilder();   
		deleteQry.append("Delete from MaintRatingParameterMappingWrk catg ");
				/*+ " catg score.status= :status"
				+ " catg score.entityStatus = :status  ");*/
		 
		deleteQry.append(" Where "
				+ " catg.legalEntityCode = :legalEntityCode "
				+ " and catg.rmCode = :rmCode "
				+ " and catg.paramCode = :paramCode ");
		if(!BankAuditUtil.isEmptyString(categoryCode)){
			deleteQry.append(" and catg.categoryCode = :categoryCode  ");
		}
		if(!BankAuditUtil.isEmptyString(subCategoryCode)){
			deleteQry.append("  and catg.subCategoryCode = :subCategoryCode  ");
		}
		Query qry = session.createQuery(deleteQry.toString())
		.setParameter("legalEntityCode", legalEntityCode)
		.setParameter("rmCode", rmCode)
		.setParameter("paramCode", paramCode);
		if(!BankAuditUtil.isEmptyString(categoryCode)){
			qry.setParameter("categoryCode", categoryCode);	  	
		}
		if(!BankAuditUtil.isEmptyString(subCategoryCode)){ 
			qry.setParameter("subCategoryCode", subCategoryCode);	
		}
		qry.executeUpdate();		
	}
		
	@Override
	public  void deleteRatingParameterModelOverlayBy(Integer legalEntityCode, String rmCode, String level, String categoryCode,  String criteriaCode, String status){
		
		logger.info("called deleteRatingParameterModelOverLayBy :: detete model overlay details");
		
		Session session = getSession();
		StringBuilder deleteQry = new StringBuilder();   
		deleteQry.append("Delete from MaintRatingModelOverlayWrk overlay "); 
		 
		deleteQry.append(" Where "
				+ " overlay.legalEntityCode = :legalEntityCode "
				+ " and overlay.rmCode = :rmCode "				
				+ " and overlay.level = :level "
				+ " and overlay.categoryCode = :categoryCode "
				+ " and overlay.criteriaCode = :criteriaCode ");
		 
		 session.createQuery(deleteQry.toString())
		.setParameter("legalEntityCode", legalEntityCode)
		.setParameter("rmCode", rmCode)
		.setParameter("level", level)
		.setParameter("categoryCode", categoryCode)
		.setParameter("criteriaCode", criteriaCode)
		.executeUpdate();		
	}
	 
	@Override
	public void authorizeRatingModelParameterDetails(MaintRatingModelWrk maintRatingModelWrk){
		
		Integer legalEntityCode = maintRatingModelWrk.getLegalEntityCode();
		String rmCode = maintRatingModelWrk.getRmCode();
		String checker = maintRatingModelWrk.getChecker();
		String authRemarks = maintRatingModelWrk.getAuthRejRemarks();
		
		List<MaintRatingParameterDtlsWrk> parametersDetailsWrkList = getRatingParameterDetailsBy(legalEntityCode, rmCode, BankAuditConstant.STATUS_UNAUTH);
		List<String> paramCode = parametersDetailsWrkList.stream().map(MaintRatingParameterDtlsWrk::getParamCode) .collect(Collectors.toList());
		
		Session session=getSession();
		
		StringBuilder insertIntoRatingModel = new StringBuilder();
		insertIntoRatingModel.append(" insert into maint_rating_model(  "
				+ " legal_entity_code, rm_code, rm_name , rm_desc, auth_rej_remarks, entity_status, status,  " 
				+ " maker, maker_timestamp, checker, checker_timestamp,  "
				+ " businessdate_timestamp, sysdate_timestamp ) Select "
				+ " legal_entity_code, rm_code, rm_name , rm_desc, '"+authRemarks+"', '"+BankAuditConstant.STATUS_AUTH+"', '"+BankAuditConstant.STATUS_AUTH+"',  "
				+ " maker, maker_timestamp, '"+checker+"', :checkerDateTime ,  "
				+ " businessdate_timestamp, sysdate_timestamp from maint_rating_model_wrk wrk "
				+ " where "
				+ " wrk.legal_entity_code = :legalEntityCode "
				+ " and wrk.rm_code = :rmCode "
				+ " and wrk.status != :statusU " );
		session.flush();
		session.createNativeQuery(insertIntoRatingModel.toString())
			   .setParameter("checkerDateTime", new Date())
			   .setParameter("legalEntityCode", legalEntityCode)
			   .setParameter("rmCode", rmCode)
			   .setParameter("statusU", BankAuditConstant.STATUS_DEL)
			   .executeUpdate();
	 		
		StringBuilder insertIntoRatingParameterDetails = new StringBuilder();
		insertIntoRatingParameterDetails.append(" Insert into maint_rating_parameter_dtls(  " 
				+ " legal_entity_code, rm_code, param_code, param_name, " 
				+ " param_desc, param_dimension, param_mesaurement_type, param_basis, "  
				+ " param_calc_type, param_weightage, entity_status, status,"
				+ " remarks, guidance_note ) Select  " 			
				+ " legal_entity_code, rm_code, param_code, param_name, " 
				+ " param_desc, param_dimension, param_mesaurement_type, param_basis, "  
				+ " param_calc_type, param_weightage,  '"+BankAuditConstant.STATUS_AUTH+"', '"+BankAuditConstant.STATUS_AUTH+"',"
				+ " remarks, guidance_note " 
				+ " from maint_rating_parameter_dtls_wrk wrk " 
				+ " where "
				+ " wrk.legal_entity_code = :legalEntityCode "
				+ " and wrk.rm_code = :rmCode "
				+ " and wrk.status != :statusU  ");		
		session.flush();
		session.createNativeQuery(insertIntoRatingParameterDetails.toString())
			   .setParameter("legalEntityCode", legalEntityCode)
			   .setParameter("rmCode", rmCode)
			   .setParameter("statusU", BankAuditConstant.STATUS_DEL)
			   .executeUpdate();
		 
		StringBuilder insertIntoRatingParameterInputs = new StringBuilder();
		insertIntoRatingParameterInputs.append(" INSERT INTO  maint_rating_parameter_inputs "
				+ " (legal_entity_code, rm_code, param_code, input_01, input_02, input_03, "
				+ " input_04, input_05, input_06, input_07, input_08, input_09, input_10, input_11, "
				+ " input_12, input_13, input_14, input_15, calculation, formula, formula_desc, entity_status,status) "
				+ " SELECT "
				+ " legal_entity_code, rm_code, param_code, input_01, input_02, input_03, "
				+ " input_04, input_05, input_06, input_07, input_08, input_09, input_10, input_11, "
				+ " input_12, input_13, input_14, input_15, calculation, formula, formula_desc, "
				+ " '"+BankAuditConstant.STATUS_AUTH+"', '"+BankAuditConstant.STATUS_AUTH+"' "
				+ " FROM maint_rating_parameter_inputs_wrk wrk "
				+ " where wrk.legal_entity_code = :legalEntityCode "
				+ " and wrk.rm_code = :rmCode "
				+ " and wrk.param_code in (:paramCode) "
				+ " and wrk.status != :statusU  ");
		session.flush();
		session.createNativeQuery(insertIntoRatingParameterInputs.toString())
			   .setParameter("legalEntityCode", legalEntityCode)
			   .setParameter("rmCode", rmCode)
			   .setParameterList("paramCode", paramCode)
			   .setParameter("statusU", BankAuditConstant.STATUS_DEL)
			   .executeUpdate(); 
		
		StringBuilder insertIntoRatingParameterRange = new StringBuilder();
		insertIntoRatingParameterRange.append("insert into maint_rating_parameter_range(  "
				+ " legal_entity_code, rm_code, param_code,  "
				+ " min_range, max_range,  score,  "
				+ " status, entity_status) Select  "
				+ " legal_entity_code, rm_code, param_code, " 
				+ " min_range, max_range,  score,  "
				+ " '"+BankAuditConstant.STATUS_AUTH+"', '"+BankAuditConstant.STATUS_AUTH+"' "
				+ " from maint_rating_parameter_range_wrk wrk " 
				+ " where "
				+ " wrk.legal_entity_code = :legalEntityCode "
				+ " and wrk.rm_code = :rmCode "
				+ " and wrk.param_code in (:paramCode) "
				+ " and wrk.status != :statusU ");
		session.flush();
		session.createNativeQuery(insertIntoRatingParameterRange.toString())
			   .setParameter("legalEntityCode", legalEntityCode)
			   .setParameter("rmCode", rmCode)
			   .setParameterList("paramCode", paramCode)
			   .setParameter("statusU", BankAuditConstant.STATUS_DEL)
			   .executeUpdate();
		
		
		StringBuilder insertIntoRatingParameterCategory = new StringBuilder();		
		insertIntoRatingParameterCategory.append("insert into maint_rating_parameter_mapping(  "
				+ " legal_entity_code, rm_code, param_code,   "
				+ " category_code, sub_category_code, risk, "
				+ " status, entity_status) Select  "
				+ " legal_entity_code, rm_code, param_code, "  
				+ " category_code, sub_category_code, risk, "
				+ " '"+BankAuditConstant.STATUS_AUTH+"', '"+BankAuditConstant.STATUS_AUTH+"' "
				+ " from maint_rating_parameter_mapping_wrk  wrk "
				+ " where"
				+ " wrk.legal_entity_code = :legalEntityCode "
				+ " and wrk.rm_code = :rmCode"
				+ " and wrk.param_code in (:paramCode) "
				+ " and wrk.status != :statusU " );
		
		session.flush();
		session.createNativeQuery(insertIntoRatingParameterCategory.toString())
			   .setParameter("legalEntityCode", legalEntityCode)
			   .setParameter("rmCode", rmCode)
			   .setParameterList("paramCode", paramCode)
			   .setParameter("statusU", BankAuditConstant.STATUS_DEL)
			   .executeUpdate();
		
		   
		   StringBuilder insertIntoRatingModelOverlay = new StringBuilder();	
		   insertIntoRatingModelOverlay.append("INSERT INTO  maint_rating_model_overlay"
		   		+ "(legal_entity_code, rm_code, level, theme, criteria_code, criteria, overlays, "
		   		+ " category_code, sub_category_code, best_rating, auth_rej_remarks, "
		   		+ " entity_status, status, "
		   		+ " maker, maker_timestamp, checker, "
		   		+ " checker_timestamp, businessdate_timestamp, sysdate_timestamp) "
		   		+ " Select "
		   		+ " legal_entity_code, rm_code, level, theme, criteria_code, criteria, overlays, "
		   		+ " category_code, sub_category_code, best_rating, '"+authRemarks+"' , "
		   		+ " '"+BankAuditConstant.STATUS_AUTH+"', '"+BankAuditConstant.STATUS_AUTH+"',"
		   		+ " maker, maker_timestamp, '"+checker+"' , :checkerDateTime , businessdate_timestamp, sysdate_timestamp "
		   		+ " From maint_rating_model_overlay_wrk wrk "
		   		+ " WHERE  wrk.legal_entity_code = :legalEntityCode "
				+ " and wrk.rm_code = :rmCode" 
				+ " and wrk.status != :statusU  ");
		   session.flush();
		   session.createNativeQuery(insertIntoRatingModelOverlay.toString())
		   		  .setParameter("checkerDateTime", new Date())
				  .setParameter("legalEntityCode", legalEntityCode)
				  .setParameter("rmCode", rmCode) 
				  .setParameter("statusU", BankAuditConstant.STATUS_DEL)
				  .executeUpdate();
		   
	}	
	
 	@Override
	public  void deleteRatingModelDetailsWhileAuthBy(Integer legalEntityCode, String rmCode, List<String> paramCode, String status){
		
 		Session session=getSession();	
			
		logger.info("Start deleteRatingModelDetailsWhileAuthBy :: delete  parameter details input");		 
		StringBuilder deleteQryParamInput = new StringBuilder();
		if(!status.equalsIgnoreCase(BankAuditConstant.STATUS_AUTH)){
			deleteQryParamInput.append("Delete from MaintRatingParameterInputsWrk paramtr");
		}else{
			deleteQryParamInput.append("Delete from MaintRatingParameterInputs paramtr");
		}
		deleteQryParamInput.append(" Where "
				+ " paramtr.legalEntityCode = :legalEntityCode "
				+ " and paramtr.rmCode = :rmCode "
				+ " and paramtr.paramCode in (:paramCode) ");
		session.createQuery(deleteQryParamInput.toString())
		.setParameter("legalEntityCode", legalEntityCode)
		.setParameter("rmCode", rmCode)
		.setParameterList("paramCode", paramCode)
		.executeUpdate();
		session.flush();
		
		logger.info("call deleteRatingModelDetailsWhileAuthBy :: delete parameter score/range");		 
		StringBuilder deleteQryScore = new StringBuilder();
		if(!status.equalsIgnoreCase(BankAuditConstant.STATUS_AUTH)){
			deleteQryScore.append("Delete from MaintRatingParameterRangeWrk score");
		}else{
			deleteQryScore.append("Delete from MaintRatingParameterRange score");
		}
		deleteQryScore.append(" Where "
				+ " score.legalEntityCode = :legalEntityCode "
				+ " and score.rmCode = :rmCode "
				+ " and score.paramCode in (:paramCode)  ");
		session.createQuery(deleteQryScore.toString())
		.setParameter("legalEntityCode", legalEntityCode)
		.setParameter("rmCode", rmCode)
		.setParameterList("paramCode", paramCode)
		.executeUpdate();
		session.flush();
		
		logger.info("call deleteRatingModelDetailsWhileAuthBy :: delete parameter category details");		 
		StringBuilder deleteQryCat = new StringBuilder();
		if(!status.equalsIgnoreCase(BankAuditConstant.STATUS_AUTH)){
			deleteQryCat.append("Delete from MaintRatingParameterMappingWrk catg");
		}else{
			deleteQryCat.append("Delete from MaintRatingParameterMapping catg");
		}
		deleteQryCat.append(" Where "
				+ " catg.legalEntityCode = :legalEntityCode "
				+ " and catg.rmCode = :rmCode "
				+ " and catg.paramCode in (:paramCode)  ");
		session.createQuery(deleteQryCat.toString())
		.setParameter("legalEntityCode", legalEntityCode)
		.setParameter("rmCode", rmCode)
		.setParameterList("paramCode", paramCode)
		.executeUpdate();	
		session.flush();
		
		logger.info("start deleteRatingModelDetailsWhileAuthBy  :: delete  model overlay");			
		StringBuilder deleteQryOverLay = new StringBuilder();
		if(!status.equalsIgnoreCase(BankAuditConstant.STATUS_AUTH)){
			deleteQryOverLay.append("Delete from MaintRatingModelOverlayWrk overlay");
		}else{
			deleteQryOverLay.append("Delete from MaintRatingModelOverlay overlay");
		}
		deleteQryOverLay.append(" Where "
				+ " overlay.legalEntityCode = :legalEntityCode "
				+ " and overlay.rmCode = :rmCode  ");
		session.createQuery(deleteQryOverLay.toString())
		.setParameter("legalEntityCode", legalEntityCode)
		.setParameter("rmCode", rmCode)  
		.executeUpdate();
		session.flush();
		
		logger.info("call deleteRatingModelDetailsWhileAuthBy :: delete  parameter details");		 
		StringBuilder deleteQryParam = new StringBuilder();
		if(!status.equalsIgnoreCase(BankAuditConstant.STATUS_AUTH)){
			deleteQryParam.append("Delete from MaintRatingParameterDtlsWrk paramtr");
		}else{
			deleteQryParam.append("Delete from MaintRatingParameterDtls paramtr");
		}
		deleteQryParam.append(" Where "
				+ " paramtr.legalEntityCode = :legalEntityCode "
				+ " and paramtr.rmCode = :rmCode "
				+ " and paramtr.paramCode in (:paramCode) ");
		session.createQuery(deleteQryParam.toString())
		.setParameter("legalEntityCode", legalEntityCode)
		.setParameter("rmCode", rmCode)
		.setParameterList("paramCode", paramCode)
		.executeUpdate();
		session.flush();
		
		logger.info("call deleteRatingModelDetailsWhileAuthBy :: delete  rating model");			
		StringBuilder deleteQryRM = new StringBuilder();
		if(!status.equalsIgnoreCase(BankAuditConstant.STATUS_AUTH)){
			deleteQryRM.append("Delete from MaintRatingModelWrk rm");
		}else{
			deleteQryRM.append("Delete from MaintRatingModel rm");
		}
		deleteQryRM.append(" Where "
				+ " rm.legalEntityCode = :legalEntityCode "
				+ " and rm.rmCode = :rmCode  ");
		session.createQuery(deleteQryRM.toString())
		.setParameter("legalEntityCode", legalEntityCode)
		.setParameter("rmCode", rmCode) 
		.executeUpdate();
		session.flush();
		
		
	}	
	 
}
