package com.bankaudit.rest.process.dao;


import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.transform.ResultTransformer;
import org.jboss.logging.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.bankaudit.constants.BankAuditConstant;
import com.bankaudit.dao.AbstractDao;
import com.bankaudit.dto.process.AuditRatingComplianceScoreDto;
import com.bankaudit.dto.process.AuditRatingCompositeScoreDto;
import com.bankaudit.dto.process.AuditRatingScoreDto;
import com.bankaudit.dto.process.RatingParameterCategoryDto;
import com.bankaudit.model.MaintRatingCompositeOverlay;
import com.bankaudit.model.MaintRatingEscalationMatrix;
import com.bankaudit.process.model.AuditRatingComplianceObsScore;
import com.bankaudit.process.model.AuditRatingCompositeCategoryScore;
import com.bankaudit.process.model.AuditRatingCompositeOverlay;
import com.bankaudit.process.model.AuditRatingCompositeScore;
import com.bankaudit.process.model.AuditRatingModelOverlay;
import com.bankaudit.process.model.AuditSchedule;
import com.bankaudit.process.model.IfcRatingData;
import com.bankaudit.service.BusinessDateService;
import com.bankaudit.service.MaintEntityService;

@Repository("auditRatingCompositeDao")
public class AuditRatingCompositeDaoImpl extends AbstractDao implements AuditRatingCompositeDao{
	
	@Autowired
	BusinessDateService businessDateService;
		
	@Autowired
	AuditScheduleDao auditScheduleDao;
	
	@Autowired
	MaintEntityService maintEntityService;
	
	@Autowired
	AuditRatingComplianceScoreDao auditRatingComplianceScoreDao; 
	
		
	static final Logger logger = Logger.getLogger(AuditRatingCompositeDaoImpl.class);
	
	//Verify data present in Compliance Observation Score table
	//@Override
	public boolean verifyDataPresentForCompositeScore(int legalEntityCode, String auditId) {
		Session session = getSession();		
		boolean isCompRatingDataPresent=false;
		logger.info("Inside verifyDataPresentForCompositeScore .. "+ legalEntityCode +" auditId .. "+auditId);
		try{
			List<AuditRatingCompositeScore> auditPScore= session.createQuery(" FROM AuditRatingCompositeScore aps WHERE auditId=:auditId AND legalEntityCode=:legalEntityCode ")
					.setParameter("auditId", auditId).setParameter("legalEntityCode", legalEntityCode).list();		
			if(!auditPScore.isEmpty()) isCompRatingDataPresent=true;
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}return isCompRatingDataPresent;
	}
	
	// Verify the IR CSV file uploaded or not
	@Override
	public boolean verifyCSVUpload(int legalEntityCode, String auditId) {
		Session session = getSession();		
		boolean isCSVUpload=false;
		logger.info("Inside verifyCSVUpload .. "+ legalEntityCode +" auditId .. "+auditId);
		try{
			List<IfcRatingData> ifcRatings= session.createQuery(" FROM IfcEnsureBaasIRProcessData ird WHERE auditId=:auditId AND legalEntityCode=:legalEntityCode")
					.setParameter("auditId", auditId).setParameter("legalEntityCode", legalEntityCode).list();		
			if(!ifcRatings.isEmpty()) isCSVUpload=true;
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}return isCSVUpload;
	}
	
	// Inserting data into "audit_rating_composite_score"
	//@Override
	public boolean createAndGetAuditRatingCompositeScore(int legalEntityCode,String auditId, String auditTypeCode, String unitId, String userId) {
		Session session = getSession();
		logger.info("Inside createAndGetAuditRatingCompositeScore:: "+legalEntityCode+" :: "+auditId+ " :: "+unitId+" :: "+auditTypeCode+" :: "+userId);
		boolean created=false;
		try {
			StringBuilder qryParamr=new StringBuilder(" "
					+"  SELECT c.param_code, c.category_code, c.param_input_mapping, c.param_input_mapping_ii, c.param_basis, i.submitted_value_latest, i.submitted_value_middest, i.submitted_value_oldest "  // 7
					+"  FROM maint_rating_composite_dtls c  "
					//+"     LEFT JOIN ifc_ensure_baas_ir_process_data p ON p.audit_id=:auditId AND c.legal_entity_code=p.legal_entity_code " // To get the score from CSV  //Commented as we need 3 years data, so using ifc_ensure_baas_data table
					+"     LEFT JOIN ifc_ensure_baas_data i ON i.audit_id=:auditId AND c.legal_entity_code=i.legal_entity_code " // To get the score from CSV
					+" 			AND c.param_input_mapping=i.parameter_name_nabard AND i.stage_of_file='L' "
					+" WHERE c.legal_entity_code=:legalEntityCode AND c.audit_type_code=:auditTypeCode AND c.entity_status='A'");
			
			List qrylst=session.createNativeQuery(qryParamr.toString()).setParameter("legalEntityCode", legalEntityCode)
					.setParameter("auditId", auditId).setParameter("auditTypeCode", auditTypeCode).list();			
			
			int i=0;
			for ( Iterator iter = qrylst.iterator(); iter.hasNext(); ) {
				String obs_remarks="";
				Object[] obj = (Object[]) iter.next(); i++;				
				AuditRatingCompositeScore acs= new AuditRatingCompositeScore();
				acs.setLegalEntityCode(legalEntityCode);
				acs.setAuditId(auditId);
				acs.setAuditTypeCode(auditTypeCode);
				acs.setMaker(userId);
				acs.setMakerTimestamp(new Timestamp(System.currentTimeMillis()));
				if(obj[0] !=null) acs.setParamCode(obj[0].toString());
				if(obj[1] !=null) acs.setCategoryCode(obj[1].toString());
				if(obj[2] !=null) acs.setParamInputMapping(obj[2].toString());
				if(obj[3] !=null) acs.setParamInputMappingII(obj[3].toString());  // This parameter for exception scenario, like DDCB bi-annual. getting the BOMID and keeping it to update in updateExceptionAuditRatingCompositeScores() 
				if(obj[4] !=null) acs.setParamBasis(obj[4].toString());
				if(obj[5] !=null) acs.setParamScore(obj[5].toString());
				if(obj[6] !=null) acs.setParamScoreII(obj[6].toString());
				if(obj[7] !=null) acs.setParamScoreIII(obj[7].toString());
				
				// If input param mapping is "NA", then the score is NA
				if("NA".equalsIgnoreCase(acs.getParamInputMapping())) { 
					acs.setParamScore("NA");
				}
				
				acs.setEntityStatus("A");
				acs.setStatus("U");					
				auditRatingComplianceScoreDao.flushSession();
				auditRatingComplianceScoreDao.saveOrUpdate(acs);				
			}logger.info(" Composite Parameters Inserted .. "+i +" times");	
			created=true;
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}return created;
	}
	
	// Inserting data into "audit_rating_composite_category_score"
	//@Override
	public boolean createAuditRatingCompositeCategoryScore(int legalEntityCode,String auditId, String auditTypeCode, String unitId, String userId) {
		Session session = getSession();
		logger.info("Inside createAuditRatingCompositeScore:: "+legalEntityCode+" :: "+auditId+ " :: "+unitId+" :: "+auditTypeCode+" :: "+userId);
		boolean created=false;
		try {
			StringBuilder qryParamr=new StringBuilder(" "
					+"  SELECT distinct category_code,category_name FROM maint_rating_category " 
					+"   WHERE legal_entity_code=:legalEntityCode AND IFNULL(audit_type_code,:auditTypeCode)=:auditTypeCode "
					+"    AND category_for='COMPOSITE' AND entity_status='A' ");
			
			List qrylst=session.createNativeQuery(qryParamr.toString()).setParameter("legalEntityCode", legalEntityCode)
					.setParameter("auditTypeCode", auditTypeCode).list();			
			
			int i=0;
			for ( Iterator iter = qrylst.iterator(); iter.hasNext(); ) {
				Object[] obj = (Object[]) iter.next(); i++;				
				AuditRatingCompositeCategoryScore acs= new AuditRatingCompositeCategoryScore();
				acs.setLegalEntityCode(legalEntityCode);
				acs.setAuditId(auditId);
				acs.setAuditTypeCode(auditTypeCode);
				acs.setMaker(userId);
				acs.setMakerTimestamp(new Timestamp(System.currentTimeMillis()));
				if(obj[0] !=null) acs.setCategoryCode(obj[0].toString());
				acs.setStatus("U");					
				auditRatingComplianceScoreDao.flushSession();
				auditRatingComplianceScoreDao.saveOrUpdate(acs);				
			}logger.info(" Composite Category Inserted .. "+i +" times");	
			created=true;
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}return created;
	}
	
	// Inserting data into "audit_rating_composite_overlay"
		//@Override
		public boolean createAuditRatingCompositeOverlay(int legalEntityCode,String auditId,  String auditTypeCode, String userId) {
			Session session = getSession();
			logger.info("Inside createAuditRatingCompositeOverlay:: "+legalEntityCode+" :: "+auditId+" :: "+auditTypeCode+" :: "+userId);
			boolean created=false;
			try {
				/*List<MaintRatingCompositeOverlay> ovrlyLst=session.createQuery("FROM MaintRatingCompositeOverlay m WHERE m.auditTypeCode=:auditTypeCode "
						+ " AND m.legalEntityCode=:legalEntityCode AND entityStatus='A'").setParameter("legalEntityCode", legalEntityCode)
						.setParameter("auditTypeCode", auditTypeCode).list();	*/		
				
				StringBuilder qryParamr=new StringBuilder(" SELECT c.audit_type_code, c.overlay_code, c.overlay_name, c.sl_no, " // 3
						+ " c.input_overlay_mapping, p.submitted_value_latest "  // 5
						+"  FROM maint_rating_composite_overlay c  "
						+"     LEFT JOIN ifc_ensure_baas_ir_process_data p ON p.audit_id=:auditId AND c.legal_entity_code=p.legal_entity_code " // To get the score from CSV 
						+" 			AND c.input_overlay_mapping=p.parameter_name_nabard "
						+" WHERE c.legal_entity_code=:legalEntityCode AND c.audit_type_code=:auditTypeCode AND c.entity_status='A' ");
				
				List qrylst=session.createNativeQuery(qryParamr.toString()).setParameter("legalEntityCode", legalEntityCode)
						.setParameter("auditId", auditId).setParameter("auditTypeCode", auditTypeCode).list();	
								
				int i=0;
				for ( Iterator iter = qrylst.iterator(); iter.hasNext(); ) {
					Object[] obj = (Object[]) iter.next(); i++;		
					AuditRatingCompositeOverlay aco= new AuditRatingCompositeOverlay();
					aco.setLegalEntityCode(legalEntityCode);
					aco.setAuditId(auditId);
					aco.setAuditTypeCode(auditTypeCode);
					if(obj[1] !=null) aco.setOverlayCode(obj[1].toString());
					if(obj[2] !=null) aco.setOverlayName(obj[2].toString());
					if(obj[3] !=null) aco.setSlNo(obj[3].toString());
					if(obj[4] !=null) aco.setInputOverlayMapping(obj[4].toString());
					if(obj[5] !=null) aco.setOverlayValue(obj[5].toString());
					aco.setEntityStatus("A");
					aco.setStatus("A");
					aco.setMaker(userId);
					aco.setMakerTimestamp(new Timestamp(System.currentTimeMillis()));
									
					auditRatingComplianceScoreDao.flushSession();
					auditRatingComplianceScoreDao.saveOrUpdate(aco);				
				}logger.info(" Overlay created for  .. "+ auditId);	
				created=true;
			}catch(Exception e) {
				e.printStackTrace(System.out);
			}return created;
		}
		
	// To get the Composite score grouped under Category, but this is done in ServiceImpl class
	//@Override
	/*public AuditRatingCompositeScoreDto getAuditRatingCompositeScoreGroupedOnCategoryByAuditId(int legalEntityCode,String auditId, String auditTypeCode, String userType) {
		Session session = getSession();
		logger.info("Inside getAuditRatingCompositeCategoryScoreByAuditId .. "+auditId+" :: "+ auditTypeCode);
		
		List<AuditRatingCompositeScore> compScrLst=getAuditRatingCompositeScoreByAuditId(legalEntityCode,auditId,auditTypeCode,userType);
		
		List<AuditRatingCompositeCategoryScore> catScoreLst =new ArrayList<>();
		StringBuilder queryString=new StringBuilder("SELECT c as catScr, r.categoryName FROM AuditRatingCompositeCategoryScore c, MaintRatingCategory r  "
				+ " WHERE c.auditId=:auditId AND c.legalEntityCode=:legalEntityCode AND r.categoryCode=c.categoryCode "
				+ " AND IFNULL(r.auditTypeCode,c.auditTypeCode)=c.auditTypeCode  AND r.categoryFor='COMPOSITE' ");
		
		logger.info(" Before the qry....");
		
		Query query=session.createQuery(queryString.toString())
				.setResultTransformer(new ResultTransformer() {					
					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						AuditRatingCompositeCategoryScore arccs=new AuditRatingCompositeCategoryScore();
						if(tuple[0]!=null) arccs= (AuditRatingCompositeCategoryScore)tuple[0];
						if(tuple[1]!=null) arccs.setCategoryName(tuple[1].toString());
						return arccs;
					}						
					@Override
					public List transformList(List collection) {
						return collection;
					}
				}).setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId);
		
		catScoreLst = query.list();		
		
		for(AuditRatingCompositeCategoryScore catScore: catScoreLst) {
			// reportDto.setManagementOverlayLst((List<AuditRatingModelOverlay>) auditModelOverlaysLst.stream().filter(p -> p.getLevel().equals("MODEL")).collect(Collectors.toList()));
			catScore.setAuditRatingCompositeScoreLst((List<AuditRatingCompositeScore>)compScrLst.stream().filter(p -> p.getCategoryCode().equals(catScore.getCategoryCode())).collect(Collectors.toList()));
		}
		
		return compScoreDto;
	}*/	
		
	
	//@Override
	public List<AuditRatingCompositeScore> getAuditRatingCompositeScoreByAuditId(int legalEntityCode,String auditId, String auditTypeCode, String userType) {
		Session session = getSession();
		session.setDefaultReadOnly(true);
		
		List<AuditRatingCompositeScore> auditRatingCompositeScoreLst = null;
		logger.info("Inside getAuditRatingCompositeScoreByAuditId .. "+ legalEntityCode +" auditId .. "+auditId +" auditTypeCode .."+auditTypeCode+" userType .."+userType);
		try {
			
			StringBuilder queryString=new StringBuilder("SELECT s as cmpScr, d.paramName, c.categoryName "
					+ "  FROM MaintRatingCompositeDtls d,AuditRatingCompositeScore s  "
					+ "     LEFT JOIN MaintRatingCategory c ON c.categoryCode=s.categoryCode AND c.categoryFor='COMPOSITE'  " 
					+ "          AND s.legalEntityCode=c.legalEntityCode AND IFNULL(c.auditTypeCode,s.auditTypeCode)=s.auditTypeCode " 
					+ "  WHERE s.auditId=:auditId AND s.legalEntityCode=:legalEntityCode AND s.auditTypeCode=d.auditTypeCode AND s.legalEntityCode=d.legalEntityCode AND s.paramCode = d.paramCode " 
					+ "   ORDER BY s.categoryCode, s.paramCode  ");
			
			logger.info(" Before the qry....");
			
			Query query=session.createQuery(queryString.toString())
					.setResultTransformer(new ResultTransformer() {					
						@Override
						public Object transformTuple(Object[] tuple, String[] aliases) {
							AuditRatingCompositeScore arccs=new AuditRatingCompositeScore();
							if(tuple[0]!=null) arccs= (AuditRatingCompositeScore)tuple[0];
							if(tuple[1]!=null) arccs.setParamName(tuple[1].toString());
							if(tuple[2]!=null) arccs.setCategoryName(tuple[2].toString());
							return arccs;
						}						
						@Override
						public List transformList(List collection) {
							return collection;
						}
					}).setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId);
			
			auditRatingCompositeScoreLst = query.list();	
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}return auditRatingCompositeScoreLst; 
		
		/*	StringBuilder queryString=new StringBuilder(" SELECT s.legal_entity_code, s.audit_id, s.audit_type_code, s.param_code,s.category_code, s.param_input_mapping, " // 5
					+ "    s.param_basis, s.param_score, s.param_score_ii, d.param_name, c.category_name "   // 10  //p.submitted_value_latest, p.submitted_value_oldest,
					+ "  FROM  maint_rating_composite_dtls d, audit_rating_composite_score s " 
					+ "     LEFT JOIN maint_rating_category c ON c.category_code=s.category_code AND c.category_for='COMPOSITE'  " 
					+ "          AND s.legal_entity_code=c.legal_entity_code AND IFNULL(c.audit_type_code,s.audit_type_code)=s.audit_type_code " 
					//+ "     LEFT JOIN ifc_ensure_baas_ir_process_data p ON s.audit_type_code=p.audit_type_code AND s.param_input_mapping=p.parameter_name_nabard AND s.audit_id=p.audit_id " // As the data already pushed after CSV upload no need to fetch
					+ "  WHERE s.audit_id=:auditId AND s.legal_entity_code=:legalEntityCode AND s.audit_type_code=d.audit_type_code AND s.legal_entity_code=d.legal_entity_code AND s.param_code = d.param_code " 
					+ "   ORDER BY s.category_code, s.param_code  ");
					
			logger.info("Before the qry....");
			
			List qrylst=session.createNativeQuery(queryString.toString()).setParameter("auditId", auditId)
					.setParameter("legalEntityCode", legalEntityCode).list();
			
			int i=0;
			auditRatingCompositeScoreLst = new ArrayList<AuditRatingCompositeScore>();
			for ( Iterator iter = qrylst.iterator(); iter.hasNext(); ) {
				Object[] obj = (Object[]) iter.next(); i++;				
				AuditRatingCompositeScore acs= new AuditRatingCompositeScore();
				acs.setLegalEntityCode(legalEntityCode);
				acs.setAuditId(auditId);
				acs.setAuditTypeCode(auditTypeCode);
				
				if(obj[3] !=null) acs.setParamCode(obj[3].toString());
				if(obj[4] !=null) acs.setCategoryCode(obj[4].toString());
				if(obj[5] !=null) acs.setParamInputMapping(obj[5].toString());
				if(obj[6] !=null) acs.setParamBasis(obj[6].toString());
				if(obj[7] !=null) acs.setParamScore(obj[7].toString());
				if(obj[8] !=null) acs.setParamScoreII(obj[8].toString());
				if(obj[9] !=null) acs.setParamName(obj[9].toString());
				if(obj[10] !=null) acs.setCategoryName(obj[10].toString());
				// if(obj[11] !=null) acs.setParamScore(obj[11].toString()); // Score will be replaced from ifc_ensure_baas_ir_process_data, which updated on CSV file upload
				// if(obj[12] !=null && "P0010".equalsIgnoreCase(acs.getParamCode())) 
				//	acs.setParamScoreII(obj[12].toString()); // Score will be replaced from ifc_ensure_baas_ir_process_data, which updated on CSV file upload
			
				auditRatingCompositeScoreLst.add(acs);				
			}logger.info(" Composite records .. "+i +" Lst:: " + auditRatingCompositeScoreLst);	
			*/
	}
	
	//@Override
	public List<AuditRatingCompositeScore> getAuditRatingCompositeScoreOnlyByAuditId(int legalEntityCode,String auditId, String auditTypeCode, String userType) {
		Session session = getSession();
		return session.createQuery(" FROM AuditRatingCompositeScore c where c.auditId=:auditId AND c.legalEntityCode=:legalEntityCode")
				.setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId).list();
	}
	
	//@Override
	public Map<String, String> getParamScoreMapByAuditId(int legalEntityCode,String auditId, String auditTypeCode, String userType) {
		Session session = getSession();
		logger.info("getParamScoreMapByAuditId .. "+auditId+" :: "+auditTypeCode);
		Map<String, String> paramMap= new HashMap<String, String>();
		List<AuditRatingCompositeScore> cmpScrLst=getAuditRatingCompositeScoreOnlyByAuditId(legalEntityCode,auditId,auditTypeCode,userType);
		for(AuditRatingCompositeScore cmpScr: cmpScrLst) {
			paramMap.put(cmpScr.getParamCode(), cmpScr.getParamScore());
			if("P0011".equalsIgnoreCase(cmpScr.getParamCode())) {
				paramMap.put((cmpScr.getParamCode()+"_II"), cmpScr.getParamScoreII()); // To store the 2nd year data to the Map
			}else if("P0114".equals(cmpScr.getParamCode()) || "P1114".equals(cmpScr.getParamCode()) || "P0214".equals(cmpScr.getParamCode()) || "P1214".equals(cmpScr.getParamCode())) {
				paramMap.put((cmpScr.getParamCode()+"_II"), cmpScr.getParamScoreII()); // To store the 2nd year data to the Map
				paramMap.put((cmpScr.getParamCode()+"_III"), cmpScr.getParamScoreIII()); // To store the 3rd year data to the Map
			}
			
		}
		logger.info(" paramMap .. "+paramMap);
		return paramMap;
	}

	//@Override
	public List<AuditRatingCompositeCategoryScore> getAuditRatingCompositeCategoryScoreByAuditId(int legalEntityCode,String auditId, String auditTypeCode, String userType) {
		Session session = getSession();
		logger.info("Inside getAuditRatingCompositeCategoryScoreByAuditId .. "+auditId+" :: "+ auditTypeCode);
		List<AuditRatingCompositeCategoryScore> catScoreLst =new ArrayList<>();
		StringBuilder queryString=new StringBuilder("SELECT c as catScr, r.categoryName FROM AuditRatingCompositeCategoryScore c, MaintRatingCategory r  "
				+ " WHERE c.auditId=:auditId AND c.legalEntityCode=:legalEntityCode AND r.categoryCode=c.categoryCode "
				+ " AND IFNULL(r.auditTypeCode,c.auditTypeCode)=c.auditTypeCode  AND r.categoryFor='COMPOSITE' ");
		
		logger.info(" Before the qry....");
		
		Query query=session.createQuery(queryString.toString())
				.setResultTransformer(new ResultTransformer() {					
					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						AuditRatingCompositeCategoryScore arccs=new AuditRatingCompositeCategoryScore();
						if(tuple[0]!=null) arccs= (AuditRatingCompositeCategoryScore)tuple[0];
						if(tuple[1]!=null) arccs.setCategoryName(tuple[1].toString());
						return arccs;
					}						
					@Override
					public List transformList(List collection) {
						return collection;
					}
				}).setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId);
		
		catScoreLst = query.list();		
		return catScoreLst;
	}
	
	//@Override
	public List<AuditRatingCompositeOverlay> getAuditRatingCompositeOverlayByAuditId(int legalEntityCode,String auditId, String auditTypeCode, String userType) {
		Session session = getSession();
		logger.info("Inside getAuditRatingCompositeOverlayByAuditId .. "+auditId+" :: "+ auditTypeCode);
		return session.createQuery(" FROM AuditRatingCompositeOverlay o WHERE o.auditId=:auditId AND o.legalEntityCode=:legalEntityCode ")
				.setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId).list();		
	}
	
	// Updating the modified Composite score after IR CSV file Upload
	//@Override
	public void executeAndUpdateAuditCompositeScoresFromIRCSV(int legalEntityCode, String auditId, String userType) {
		Session session = getSession();
		logger.info("Inside executeAndUpdateAuditCompositeScoresFromIRCSV .. "+legalEntityCode +" :: "+ auditId+ " :: " +userType);
		try{
			StringBuilder updateQry =new StringBuilder(" UPDATE audit_rating_composite_score c, ifc_ensure_baas_data i "  // ifc_ensure_baas_ir_process_data
					+ " SET c.param_score=i.submitted_value_latest, c.param_score_ii=i.submitted_value_middest, c.param_score_iii=i.submitted_value_oldest  "
					+ " WHERE c.legal_entity_code=i.legal_entity_code AND c.audit_id=i.audit_id AND c.param_input_mapping=i.parameter_name_nabard "
					+ "  AND c.audit_id=:auditId AND c.legal_entity_code=:legalEntityCode AND i.stage_of_file='L' ");
			int totalUpdates=session.createNativeQuery(updateQry.toString()).setParameter("legalEntityCode", legalEntityCode)	.setParameter("auditId", auditId).executeUpdate();
			
			/*StringBuilder updateQry =new StringBuilder("UPDATE AuditRatingCompositeScore c, IfcEnsureBaasIRProcessData i SET   "
						+ " c.paramScore =i.submittedValueLatest , c.paramScoreII=i.submittedValueOldest "
						+ "  WHERE c.legalEntityCode =i.legalEntityCode AND c.paramInputMapping=i.parameterNameNabard AND c.auditId=i.auditId "
						+ "  AND c.auditId=:auditId AND c.legalEntityCode=:legalEntityCode ");
			int totalUpdates=session.createQuery(updateQry.toString()).setParameter("legalEntityCode", legalEntityCode)	.setParameter("auditId", auditId).executeUpdate();*/ 	
			logger.info("No of composite score updates .."+totalUpdates);
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
	}
	
	// Updating the modified Composite score for exceptional scenario (for BiAnnual )
	/**** For Net Profit/Loss as per IO    -> Annual    -> for all 3, use BOMD500
	 *                                     -> Bi-Annual -> for all 3, use BOMD331
	 *    For Accumulated Losses as per IO -> Annual    -> for all 3, use BOMD340
	 *                                     -> Bi-Annual -> for all 3, use BOMD333                               
	 *    Use the param_input_mapping_ii for updating the score ***/
	//@Override
	public void updateExceptionAuditRatingCompositeScores(int legalEntityCode, String auditId, String auditTypeCode, String userType) {
			Session session = getSession();
			logger.info("Inside updateExceptionAuditRatingCompositeScores .. "+legalEntityCode +" :: "+ auditId+ " :: "+ auditTypeCode+ " :: " +userType);
			try{
				StringBuilder updateQry =new StringBuilder(" UPDATE audit_rating_composite_score c, ifc_ensure_baas_data i "  // ifc_ensure_baas_ir_process_data
					//+ " SET c.param_score=i.submitted_value_latest, c.param_score_ii=i.submitted_value_middest, c.param_score_iii=i.submitted_value_oldest  "
					+ " SET c.param_score_ii=i.submitted_value_middest " // 
					+ " WHERE c.legal_entity_code=i.legal_entity_code AND c.param_input_mapping_ii=i.parameter_name_nabard AND c.audit_id=i.audit_id "
					+ "  AND c.audit_id=:auditId AND c.legal_entity_code=:legalEntityCode AND i.stage_of_file='L' ");
				int totalUpdates=session.createNativeQuery(updateQry.toString()).setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId).executeUpdate();
				
				logger.info("No. of composite excp score updates .."+totalUpdates);
			}catch(Exception e) {
				e.printStackTrace(System.out);
			}
		}
	
	// Updating the modified Composite Overlay after IR CSV file Upload
	//@Override
	public void executeAndUpdateAuditCompositeOverlayFromIRCSV(int legalEntityCode, String auditId, String userType) {
		Session session = getSession();
		logger.info("Inside executeAndUpdateAuditCompositeOverlayFromIRCSV .. .. "+legalEntityCode +" :: "+ auditId+ " :: " +userType);
		try{
			StringBuilder updateQry =new StringBuilder("UPDATE audit_rating_composite_overlay c, ifc_ensure_baas_ir_process_data i "
					+ " SET c.overlay_value =i.submitted_value_latest "
					+ "  WHERE c.legal_entity_code =i.legal_entity_code AND c.input_overlay_mapping=i.parameter_name_nabard AND c.audit_id=i.audit_id "
					+ "  AND c.audit_id=:auditId AND c.legal_entity_code=:legalEntityCode ");
			
		int totalUpdates=session.createNativeQuery(updateQry.toString()).setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId).executeUpdate();
		
			/*StringBuilder updateQry =new StringBuilder("UPDATE AuditRatingCompositeOverlay c, IfcEnsureBaasIRProcessData i SET   "
						+ " c.overlayScore =i.submittedValueLatest "
						+ "  WHERE c.legalEntityCode =i.legalEntityCode AND c.inputOverlayMapping=c.parameterNameNabard AND c.auditId=i.auditId "
						+ "  AND c.auditId=:auditId AND c.legalEntityCode=:legalEntityCode ");
				
			int totalUpdates=session.createQuery(updateQry.toString()).setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId).executeUpdate(); */	
			logger.info("No of overlay updates .."+totalUpdates);
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
	}
	
	// Updating the Composite score after Supervisory rating done
	//@Override
	public void fetchAndUpdateAuditCompositeScoresFromSupervisoryRating(int legalEntityCode, String auditId, String userType) {
		Session session = getSession();
		logger.info("Inside fetchAndUpdateAuditCompositeScoresFromSupervisoryRating .. .. "+legalEntityCode +" :: "+ auditId+ " :: " +userType);
		try{
			StringBuilder updateQry =new StringBuilder("UPDATE audit_rating_composite_score c,audit_rating_parameter_category_score p "
					+ " SET c.param_score=p.score_on_100_aftr_overlay "  //  SET c.param_score=p.category_prcntg_aftr_overlay
					+ "  WHERE c.legal_entity_code =p.legal_entity_code AND c.param_input_mapping=p.category_code AND c.audit_id=p.audit_id "
					+ "  AND c.param_basis='FETCH' AND c.audit_id=:auditId AND c.legal_entity_code=:legalEntityCode ");
			
			int totalUpdates=session.createNativeQuery(updateQry.toString()).setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId).executeUpdate();
		
			/*StringBuilder updateQry =new StringBuilder("UPDATE AuditRatingCompositeScore c, AuditRatingParameterCategoryScore p SET   "
						+ " c.paramScore =p.categoryPrcntgAftrOverlay "
						+ "  WHERE c.legalEntityCode =p.legalEntityCode AND c.paramInputMapping=p.categoryCode AND c.auditId=p.auditId "
						+ "  AND c.paramBasis='FETCH' AND c.auditId=:auditId AND c.legalEntityCode=:legalEntityCode ");
				
			int totalUpdates=session.createQuery(updateQry.toString()).setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId).executeUpdate();*/ 	
			logger.info("No of composite scores fetched from Supervisory Rating .."+totalUpdates);
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
	}
	
	

	// Updating the modified Composite score
	//@Override
	public void updateAuditRatingCompositeScores(List<AuditRatingCompositeScore> auditRatingCompositeScores, int legalEntityCode, String auditId, String userType) {
		Session session = getSession();
		
		logger.info("Inside updateAuditRatingCompositeScores .. "+legalEntityCode +" :: "+ auditId+ " :: " +userType);
		for(AuditRatingCompositeScore auditRatingCompositeScore : auditRatingCompositeScores) {
			if("M".equalsIgnoreCase(auditRatingCompositeScore.getStatus())) {
				auditRatingCompositeScore.setStatus(BankAuditConstant.STATUS_UNAUTH);
				auditRatingComplianceScoreDao.flushSession();
				auditRatingComplianceScoreDao.saveOrUpdate(auditRatingCompositeScore);
			}
		}
	}
	
	// Updating the modified Composite Category score
	//@Override
	public void updateAuditRatingCompositeCategoryScores(List<AuditRatingCompositeCategoryScore> auditRatingCompositeCategoryScores, int legalEntityCode, String auditId, String userType) {
		Session session = getSession();
		
		logger.info("Inside updateAuditRatingCompositeCategoryScores .. "+legalEntityCode +" :: "+ auditId+ " :: " +userType);
		for(AuditRatingCompositeCategoryScore auditCategoryScore : auditRatingCompositeCategoryScores) {
			if("M".equalsIgnoreCase(auditCategoryScore.getStatus())) {
				auditCategoryScore.setStatus(BankAuditConstant.STATUS_UNAUTH);
				auditRatingComplianceScoreDao.flushSession();
				auditRatingComplianceScoreDao.saveOrUpdate(auditCategoryScore);
			}
		}
	}
	
	// Updating the modified Composite Overlay Remarks
	//@Override
	public void updateAuditRatingCompositeOverlay(List<AuditRatingCompositeOverlay> auditRatingCompositeOverlay, int legalEntityCode, String auditId, String userId, String userType) {
		Session session = getSession();
		
		logger.info("Inside updateAuditRatingCompositeOverlay .. "+legalEntityCode +" :: "+ auditId+ " :: " +userId+ " :: " +userType);
		for(AuditRatingCompositeOverlay compOvrly : auditRatingCompositeOverlay) {
			//if("M".equalsIgnoreCase(compOvrly.getStatus())) {
				compOvrly.setStatus(BankAuditConstant.STATUS_UNAUTH);
				// compOvrly.setMaker(userId);
				compOvrly.setMakerTimestamp(new Timestamp(System.currentTimeMillis()));
				auditRatingComplianceScoreDao.flushSession();
				auditRatingComplianceScoreDao.saveOrUpdate(compOvrly);
			//}
		}
	}
	
	//@Override
	public AuditRatingComplianceScoreDto getAuditRatingComplianceScores(int legalEntityCode, String auditId, String ratingType, String userType) {
		logger.info("Inside getAuditRatingComplianceScores "+ auditId +" :: "+ratingType+" :: "+ userType);
		Session session = getSession();
		session.setDefaultReadOnly(true);
		
		AuditRatingComplianceScoreDto scoreDto = null;
		String scoreType="desk_effective_score";
		if("F".equalsIgnoreCase(ratingType)) scoreType="field_effective_score";
		logger.info("Score Type as .."+ scoreType);
		
		
		// Applicable Number of observations = Total number of observations  - No. of observations marked as NA  
		StringBuilder queryString=new StringBuilder(" SELECT observation_criticality, COUNT(*) as total_obs, CAST(SUM("+scoreType+")as signed) as total_score "  // --0-2
				+ "   FROM audit_rating_compliance_obs_score  "
				+ "   WHERE legal_entity_code=:legalEntityCode AND audit_id=:auditId AND "+scoreType+" <> 'NA' GROUP BY observation_criticality ");
			
		Query query=(Query) session.createNativeQuery(queryString.toString()).setParameter("legalEntityCode", legalEntityCode)
		  .setParameter("auditId", auditId);
		
		List lst=query.list();
		try {
			String obsCrtly=null, ratingGrade=null;
			int totalCnt=0, mjrCnt=0, mnrCnt=0, nCnt=0;  // here nCnt=> Normal count is which is not Major and Minor 
			double totalScore=0, totalMjrScore=0, totalMnrScore=0, totalNScore=0;  // totalNScore=> total Normal Score
			double finalTotal=0;
			
			RatingParameterCategoryDto cDto=null;
			for ( Iterator iter = lst.iterator(); iter.hasNext(); ) {
				Object[] obj =(Object[])iter.next();
				obsCrtly= obj[0]!=null?obj[0].toString():"" ;
				logger.info("obsCrtly:::::::::::::::::::::::::::::::::::::::"+obsCrtly);
				if("Major".equalsIgnoreCase(obsCrtly)) {
					if(obj[1]!=null) mjrCnt= Integer.parseInt(obj[1].toString());
					if(obj[2]!=null) totalMjrScore+= Double.parseDouble(obj[2].toString());
				}else if("Minor".equalsIgnoreCase(obsCrtly)) {
					if(obj[1]!=null) mnrCnt= Integer.parseInt(obj[1].toString());
					if(obj[2]!=null) totalMnrScore+= Double.parseDouble(obj[2].toString());
				}else {
					if(obj[1]!=null) nCnt= Integer.parseInt(obj[1].toString());
					if(obj[2]!=null) totalNScore+= Double.parseDouble(obj[2].toString());
				}
			}
			logger.info("Cnt :: "+mjrCnt+" :: "+mnrCnt +" :: "+nCnt +" :: "+totalMjrScore+" :: "+ totalMnrScore+" :: "+totalNScore);
			totalCnt =mjrCnt+mnrCnt+nCnt;
			totalScore= totalMjrScore+ totalMnrScore + totalNScore;
			if(totalCnt>0) finalTotal=(double)Math.round((totalScore/(totalCnt*5)*100)*100)/100.00;
			ratingGrade= getRatingFromScore(legalEntityCode, auditId, "COMPLIANCE", "ALL", String.valueOf(finalTotal) );
			
			logger.info("totalCnt ::"+totalCnt+" :: "+totalScore+" :: "+finalTotal+" :: "+ratingGrade);
				
			scoreDto= new AuditRatingComplianceScoreDto();
			scoreDto.setAuditId(auditId);
			if("F".equalsIgnoreCase(ratingType)) {
				scoreDto.setFieldScore(String.valueOf(finalTotal));
				scoreDto.setFieldRating(ratingGrade);
				scoreDto.setFieldTotalObs(String.valueOf(totalCnt));
				scoreDto.setFieldTotalMarks(String.valueOf(totalScore));
				scoreDto.setFieldMaxMarks(String.valueOf(totalCnt*5));
			}else {
				scoreDto.setDeskScore(String.valueOf(finalTotal));
				scoreDto.setDeskRating(ratingGrade);
				scoreDto.setDeskTotalObs(String.valueOf(totalCnt));
				scoreDto.setDeskTotalMarks(String.valueOf(totalScore));
				scoreDto.setDeskMaxMarks(String.valueOf(totalCnt*5));
			}	
			
				
			logger.info("ComplianceScoreDto .... "+scoreDto);
			}catch(Exception e) {
				e.printStackTrace(System.out);
			}
		return scoreDto;
	}
	
	//@Override
	public String getRatingFromScore(Integer legalEntityCode, String auditId, String ratingName, String ratingSubType, String ratingScr ){
		Session session =getSession();
		logger.info("Inside getRatingFromScore AuditRatingCompliancescoreDaoImpl .. "+ auditId +" :: "+ratingName+" :: "+ratingSubType +" :: "+ratingScr);
		String rating=null;
		try{					  		
			StringBuilder queryRating=new StringBuilder(" SELECT g.grade_code, g.max_per FROM maint_rating_score_grade g "						
					+ " WHERE g.legal_entity_code=:legalEntityCode AND g.rating_name =:ratingName AND g.rating_sub_type =:ratingSubType "
					+ "    AND :ratingScr  >= g.min_per AND :ratingScr < g.max_per ");
					logger.info("Before the qry of Rating ...."+ queryRating.toString());
					Query qry2=session.createNativeQuery(queryRating.toString())
						.setParameter("legalEntityCode", legalEntityCode).setParameter("ratingScr", ratingScr).setParameter("ratingName", ratingName)
						.setParameter("ratingSubType", ratingSubType);
					Object[] obj1 = (Object[])qry2.getSingleResult();
					
					if(obj1 !=null) {
						if(obj1[0] !=null) { 
							rating= obj1[0].toString();
							logger.info("ratingGrade .."+rating);
						}
					}		
		}catch (Exception e) { 
			logger.error("Error .." + e.getMessage()+" :: "+e.getCause());
			e.printStackTrace(System.out);
		}
		return rating;
	}	
	
	//@Override
	public List<AuditRatingComplianceObsScore> getComplianceObsScoreReportByAuditId(int legalEntityCode,String auditId, String userType, String compRatingStatus) {
		Session session = getSession();
		session.setDefaultReadOnly(true);
		
		List<AuditRatingComplianceObsScore> auditRatingCompObsScoreLst = null;
		logger.info("Inside getComplianceObsScoreReportByAuditId .. "+ legalEntityCode +" auditId .. "+auditId +" userType .."+userType+" compRatingStatus .."+compRatingStatus);
		try {
			StringBuilder queryString=new StringBuilder(" SELECT arcs.observation_id, arcs.unit_id, arcs.observation, arcs.compliance, " // 3
					+ "  arcs.desk_assessed_score, arcs.desk_effective_score, arcs.field_assessed_score, arcs.field_effective_score, "   // 7
					+ "  arcs.reviewer_maker_remarks, arcs.reviewer_checker_remarks, arcs.auditor_maker_remarks, arcs.auditor_checker_remarks, "  // 11
					+ "  arcs.entity_status, arcs.status, arcs.maker, arcs.checker, " // 15
					+ "  arcs.maker_timestamp, arcs.checker_timestamp, arcs.businessdate_timestamp, arcs.sysdate_timestamp, " // 19
					+ "  o.review_status_attribute ,  " // 20
					//+ "  (SELECT description FROM general_parameter g WHERE g.key_1 = 'STATUS_REVIEW' AND g.key_2='REVIEW_ATTRIBUTE'  "
					//+ " 	AND g.value=o.review_status_attribute AND g.legal_entity_code=o.legal_entity_code) AS review_status_desc,  " // 21
					//+ " 	(SELECT c.comment FROM audit_observation_respond_review_capture c WHERE c.audit_id=o.audit_id AND capture_type IN ('G4','G7','G71')  "
					//+ " 			AND c.observation_id=o.observation_id ORDER BY maker_timestamp DESC LIMIT 1) AS reviewer_comment  "  // 22
					+ "  '' as review_status_desc, '' as reviewer_comment, " // 21, 22
					+ "	 s.standard_stmnt_non_compliance "  // 23
					+ "  FROM audit_rating_compliance_obs_score arcs, audit_observation o "
					+ "     LEFT JOIN maint_report_standard_stmt s ON o.mapping_id=s.mapping_id AND o.legal_entity_code=s.legal_entity_code AND o.audit_type_code=s.audit_type_code "
					+ "  WHERE arcs.legal_entity_code =o.legal_entity_code AND arcs.audit_id=o.audit_id AND arcs.observation_id=o.observation_id  "
					+ "  AND o.compliance_category='N' AND arcs.audit_id=:auditId AND arcs.legal_entity_code=:legalEntityCode ORDER BY o.observation_id ");
					
			logger.info("Before the qry....");
			
			List qrylst=session.createNativeQuery(queryString.toString()).setParameter("auditId", auditId)
					.setParameter("legalEntityCode", legalEntityCode).list();
			
			int i=0;
			auditRatingCompObsScoreLst = new ArrayList<AuditRatingComplianceObsScore>();
			for ( Iterator iter = qrylst.iterator(); iter.hasNext(); ) {
				Object[] obj = (Object[]) iter.next(); i++;				
				AuditRatingComplianceObsScore acs= new AuditRatingComplianceObsScore();
				acs.setLegalEntityCode(legalEntityCode);
				acs.setAuditId(auditId);
				
				if(obj[0] !=null) acs.setObservationId(obj[0].toString());
				if(obj[1] !=null) acs.setUnitId(obj[1].toString());
				if(obj[2] !=null) acs.setObservation(obj[2].toString());
				if(obj[3] !=null) acs.setCompliance(obj[3].toString());
				if(obj[4] !=null) acs.setDeskAssessedScore(obj[4].toString());
				if(obj[5] !=null) acs.setDeskEffectiveScore(obj[5].toString());
				if(obj[6] !=null) acs.setFieldAssessedScore(obj[6].toString());
				if(obj[7] !=null) acs.setFieldEffectiveScore(obj[7].toString());
				if(obj[8] !=null) acs.setReviewerMakerRemarks(obj[8].toString());
				if(obj[9] !=null) acs.setReviewerCheckerRemarks(obj[9].toString());
				if(obj[10] !=null) acs.setAuditorMakerRemarks(obj[10].toString());
				if(obj[11] !=null) acs.setAuditorCheckerRemarks(obj[11].toString());
				if(obj[12] !=null) acs.setEntityStatus(obj[12].toString());
				if(obj[13] !=null) acs.setStatus(obj[13].toString());
				if(obj[14] !=null) acs.setMaker(obj[14].toString());
				if(obj[15] !=null) acs.setChecker(obj[15].toString());
				if(obj[16] !=null) acs.setMakerTimestamp((Date)obj[16]);
				if(obj[20] !=null) acs.setReviewStatus(obj[20].toString());
				if(obj[21] !=null) acs.setReviewStatusDesc(obj[21].toString());
				if(obj[22] !=null) acs.setReviewComments(obj[22].toString());	
				//if(obj[23] !=null) acs.setStandardStatementNonCompliance(obj[23].toString()); // as the Standard statement is part of Observation
				
				auditRatingCompObsScoreLst.add(acs);
				
			}logger.info("Obs Comp records in Report .. "+i +" Lst:: " + auditRatingCompObsScoreLst);				
			
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}return auditRatingCompObsScoreLst; 
	}
	
	// To verify whether Overlay have value Yes
	//@Override
	public boolean isRatingCompositeOverlayApplied(Integer legalEntityCode, String auditId ){
		Session session =getSession();
		logger.info("Inside isRatingCompositeOverlay .. "+ auditId );
		boolean overLayYes=false;
		try{					  		
			StringBuilder query= new StringBuilder("SELECT COUNT(*) FROM audit_rating_composite_overlay o "
					+ " WHERE o.legal_entity_code=:legalEntityCode AND o.audit_id=:auditId AND UPPER(o.overlay_value)='Yes' ");
			Query hqlQuery= session.createNativeQuery(query.toString()).setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId);
			int overLayYesCnt=overLayYesCnt = Integer.parseInt((hqlQuery).uniqueResult().toString());
			if(overLayYesCnt >0) overLayYes=true;
			
		}catch (Exception e) { 
			logger.error("Error .." + e.getMessage()+" :: "+e.getCause());
			e.printStackTrace(System.out);
		}
		return overLayYes;
	}
	
	//@Override
	public MaintRatingEscalationMatrix getRatingEscalationMatrixByAuditId(int legalEntityCode,String auditId, String auditTypeCode, String categoryValue) {
		Session session = getSession();
		logger.info("Inside getRatingEscalationMatrixByAuditId .. "+ auditId+" :: "+ auditTypeCode+" :: "+ categoryValue);
		return (MaintRatingEscalationMatrix)session.createQuery(" FROM MaintRatingEscalationMatrix c WHERE c.categoryCode=:categoryValue "
				+ " AND c.legalEntityCode=:legalEntityCode AND c.auditTypeCode=:auditTypeCode AND c.categoryFor='COMPOSITE' ")
				.setParameter("legalEntityCode", legalEntityCode).setParameter("categoryValue", categoryValue)
				.setParameter("auditTypeCode", auditTypeCode).uniqueResult();
	}
		
	
	// Updating the Composite score and Status in Audit Rating 
	//@Override
	public void updateAuditRatingScores(AuditRatingScoreDto auditRatingScoreDto, int legalEntityCode, String auditId, String userId, String userType){
		Session session = getSession();
		logger.info("Inside updateAuditRatingScores .. "+legalEntityCode +" :: "+ auditId+ " :: " +userType+" :: "+userId);
		try{
			StringBuilder updateQry =new StringBuilder("UPDATE AuditRating r SET   "
						+ " r.compositePrcntgAftrOverlay =:cPrcnt , r.compositeRatingAftrOverlay=:cRating, "
						+ " r.compositeRatingStatus=:cStatus, r.compositeRatingChecker=:cChecker "
						+ "  WHERE r.auditId=:auditId AND r.legalEntityCode=:legalEntityCode ");
				
			int updates=session.createQuery(updateQry.toString()).setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId)
					.setParameter("cPrcnt", auditRatingScoreDto.getCompositePrcntgAftrOverlay()).setParameter("cRating", auditRatingScoreDto.getCompositeRatingAftrOverlay())
					.setParameter("cStatus", auditRatingScoreDto.getCompositeRatingStatus()).setParameter("cChecker", userId).executeUpdate(); 	
			logger.info("Audit Ratings updated .."+updates);
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
	}
	
	
}
