package com.bankaudit.rest.process.service;

import java.io.ByteArrayOutputStream;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;
import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jboss.logging.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.bankaudit.constants.BankAuditConstant;
import com.bankaudit.dto.DataTableResponse;
import com.bankaudit.dto.process.AuditRatingParameterScoreDto;
import com.bankaudit.dto.process.AuditRatingScoreDto;
import com.bankaudit.dto.process.AuditRatingSummaryDto;
import com.bankaudit.dto.process.RatingParameterCategoryDto;
import com.bankaudit.dto.process.RatingParameterReportDto;
import com.bankaudit.model.MaintRatingModelOverlay;
import com.bankaudit.process.model.AuditRatingModelOverlay;
import com.bankaudit.process.model.AuditRatingParameterScore;
import com.bankaudit.rest.process.dao.AuditRatingParameterScoreDao;
import com.bankaudit.rest.process.dao.MyAuditsDao;
import com.bankaudit.service.BusinessDateService;
import com.bankaudit.util.BankAuditUtil;

@Service("auditRatingParameterScoreService")
@Transactional("transactionManager")
public class AuditRatingParameterScoreServiceImpl implements AuditRatingParameterScoreService{

	@Autowired(required=true)
	MyAuditsDao myAuditsDao;
	
	@Autowired(required=true)
	AuditRatingParameterScoreDao auditRatingParameterScoreDao;
	
	@Autowired
	BusinessDateService businessDateService;
		
	static final Logger logger = Logger.getLogger(AuditRatingParameterScoreServiceImpl.class);

	@Override
	public DataTableResponse getMuAuditsList(int legalEntityCode, String userId, String search, Integer orderColumn,
			String orderDirection, Integer page, Integer size, String role, String userLevel, LocalDate financialYear, String auditTypeCode) {
		List<LocalDate>  financialYearStrtEndList=null;
		if(financialYear!=null) financialYearStrtEndList= BankAuditUtil.getFinancialStartEndDatesNxtFrom(financialYear);
		DataTableResponse ds=auditRatingParameterScoreDao.getMuAuditsList(legalEntityCode,userId,search,orderColumn,orderDirection,page,size, role, userLevel,
				financialYear, auditTypeCode, financialYearStrtEndList);
		return ds;
	}
	
	@Override
	public DataTableResponse getAuditsForView(int legalEntityCode, String userId, String unitCode, String roleId, String search, Integer orderColumn,
			String orderDirection, Integer page, Integer size) {
		DataTableResponse ds=myAuditsDao.getAuditsForView(legalEntityCode,userId,unitCode,roleId,search,orderColumn,
				orderDirection,page,size, null, null, null);
		return ds;
	}
	
	@Override
	public DataTableResponse getAuditsForLetter(int legalEntityCode,String userId, String search,Integer orderColumn,
			String orderDirection, Integer page, Integer size, String role) {
		DataTableResponse ds=myAuditsDao.getAuditsForLetter(legalEntityCode,userId,search,orderColumn,orderDirection,page,size, role, null, null, null);
		return ds;
	}
	
	@Override
	public boolean verifyCSVUpload(int legalEntityCode,String auditId) {
		boolean isDataPresent= auditRatingParameterScoreDao.verifyDataPresentForParamScore(legalEntityCode, auditId); 
		if(!isDataPresent) isDataPresent=auditRatingParameterScoreDao.verifyCSVUpload(legalEntityCode, auditId); 
		return isDataPresent;
	}
	
	@Override
	public List<AuditRatingParameterScore> insertAuditParameter(int legalEntityCode,String auditId, String unitId, String auditTypeCode, String userId) {
		List<AuditRatingParameterScore> paramScrLst=null;
		//boolean isDataPresent= auditRatingParameterScoreDao.verifyDataPresentForParamScore(legalEntityCode, auditId); 
		//if(!isDataPresent) {
			boolean success=auditRatingParameterScoreDao.createAndGetAuditRatingParamScore(legalEntityCode, auditId, unitId, auditTypeCode, userId);
		    if(success) paramScrLst= auditRatingParameterScoreDao.getParametersByAuditId(legalEntityCode,auditId,auditTypeCode,"PIO");
		//}
		return paramScrLst;
	}
	
	@Override
	public List<AuditRatingParameterScore> getParametersByAuditId(int legalEntityCode,String auditId, String auditTypeCode, String userType) {
		List<AuditRatingParameterScore> auditParamsLst=auditRatingParameterScoreDao.getParametersByAuditId(legalEntityCode,auditId,auditTypeCode, userType); 
		 return auditParamsLst; 
	}
	
	@Override
	public void updateAuditParamScores(List<AuditRatingParameterScore> auditRatingParameterScores, int legalEntityCode,String auditId, String userType) {
		auditRatingParameterScoreDao.updateAuditParamScores(auditRatingParameterScores, legalEntityCode, auditId, userType); 
	}
	
	@Override
	public boolean executeAnUpdateWeightedScore(int legalEntityCode, String auditId, String userType) {
		return auditRatingParameterScoreDao.executeAnUpdateWeightedScore(legalEntityCode, auditId, userType);
	}

	@Override
	public List<AuditRatingSummaryDto> getAuditRatingSummary(int legalEntityCode, String auditId, String rmCode, String userType) {
		if("RM04".equalsIgnoreCase(rmCode) || "RM03".equalsIgnoreCase(rmCode)) {
			return auditRatingParameterScoreDao.getAuditRatingSummaryNoWeightage(legalEntityCode,auditId,rmCode, userType,"No"); // No -> no overlay 
		}else{
			return auditRatingParameterScoreDao.getAuditRatingSummary(legalEntityCode,auditId,rmCode, userType);  
		}
	}
	
	// Method added to calculate for Widget 3, as applying the Overlay while calling the method getAuditRatingSummaryNoWeightage() with additional param as overlay
	public List<AuditRatingSummaryDto> getAuditRatingSummaryOverlay(int legalEntityCode, String auditId, String rmCode, String userType) {
		if("RM04".equalsIgnoreCase(rmCode) || "RM03".equalsIgnoreCase(rmCode)) {
			return auditRatingParameterScoreDao.getAuditRatingSummaryNoWeightage(legalEntityCode,auditId,rmCode, userType, "Yes"); // Yes -> include overlay 
		}else{
			return auditRatingParameterScoreDao.getAuditRatingSummary(legalEntityCode,auditId,rmCode, userType);  
		}
	}
	
	@Override
	public boolean isModelOverlayApplied(int legalEntityCode,String auditId) {
		logger.info("Inside isModelOverlayApplied .."+legalEntityCode+" .."+ auditId);
		return auditRatingParameterScoreDao.isModelOverlayApplied(legalEntityCode, auditId);
	}
	
	@Override
	public List<AuditRatingModelOverlay> getModelOverlayByAuditId(int legalEntityCode, String auditId, String rmCode, String userId ) {
		List<MaintRatingModelOverlay> isMainModelOverlaysExist=auditRatingParameterScoreDao.isModelOverlayExistForRatingModel(legalEntityCode, rmCode);
		if(!isMainModelOverlaysExist.isEmpty()) {
			List<AuditRatingModelOverlay> auditModelOverlaysLst=auditRatingParameterScoreDao.getModelOverlayByAuditId(legalEntityCode, auditId);
			logger.info("Inside getModelOverlayByAuditId auditModelOverlaysLst .."+auditModelOverlaysLst);
			if(auditModelOverlaysLst.isEmpty()) {
				auditModelOverlaysLst=auditRatingParameterScoreDao.createAndGetAuditRatingModelOverlays(legalEntityCode, rmCode, auditId, userId);
			}
			return auditModelOverlaysLst; 
		}else {
			return new ArrayList<AuditRatingModelOverlay>();
		}		
	}
	
	@Override
	public void updateAuditRatingModelOverlays(List<AuditRatingModelOverlay> auditRatingModelOverlays) {
		auditRatingParameterScoreDao.updateAuditRatingModelOverlays(auditRatingModelOverlays); 
	}
	
	@Override
	public void updateAuditRatingParameterCategoryScore(List<AuditRatingSummaryDto> auditRatingParameterCategoryLst, int legalEntityCode, String auditId, String auditTypeCode) {
		auditRatingParameterScoreDao.updateAuditRatingParameterCategoryScore(auditRatingParameterCategoryLst, legalEntityCode, auditId, auditTypeCode); 
	}
	
	@Override
	public AuditRatingScoreDto getAuditRatingScores(int legalEntityCode, String auditId, String rmCode, String userType) {
		logger.info("Inside getAuditRatingScores .. "+ legalEntityCode+" :: "+ auditId+" :: "+rmCode+" :: "+userType);
		if("RM04".equalsIgnoreCase(rmCode) || "RM03".equalsIgnoreCase(rmCode)) {
			//List<AuditRatingSummaryDto> sumDto= auditRatingParameterScoreDao.getAuditRatingSummaryNoWeightage(legalEntityCode, auditId, rmCode, userType);
			//return auditRatingParameterScoreDao.getAuditRatingScoresNoWeightage(legalEntityCode, auditId, rmCode, sumDto);
		}
		if("HO".equalsIgnoreCase(userType)) // To make separate call for HO
			return auditRatingParameterScoreDao.getAuditRatingScoresHO(legalEntityCode, auditId, rmCode);
		else return auditRatingParameterScoreDao.getAuditRatingScores(legalEntityCode, auditId, rmCode); 
	}
	@Override
	public ByteArrayOutputStream downloadRatingParameterScoresByAuditId(Integer legalEntityCode, String auditId, String auditTypeCode, String role, String userType, String ratingParameterStatus){
		
		logger.info("Inside downloadParameterScoresByAuditId .."+legalEntityCode+" .."+ auditId+" .."+ auditTypeCode+" .."+ role+" .."+ratingParameterStatus);
		List<String> rolesListInspector = Arrays.asList(("G2,G8").split(","));  //Inspection
		List<String> rolesListHO_RO = Arrays.asList(("G12").split(","));
		if(!rolesListInspector.contains(role) && !rolesListHO_RO.contains(role) ){
			return null;
		}
		try(XSSFWorkbook workbook = new XSSFWorkbook(); ByteArrayOutputStream out = new ByteArrayOutputStream();) { 
			
			List<AuditRatingParameterScore>  auditRatingParameterScoreList = 
					auditRatingParameterScoreDao.downloadRatingParameterScoresByAuditId(legalEntityCode, auditId, auditTypeCode, "IP");
					
			
			int rowNumber=0;
			XSSFSheet sheet = workbook.createSheet(legalEntityCode+"_"+auditId+"_"+auditTypeCode);  

			sheet.protectSheet(System.nanoTime()+"legalEntityauditIdauditType_"+auditId+"_"+auditTypeCode+"_"+legalEntityCode); 
			
			CellStyle cellStyleEditable = workbook.createCellStyle();
			cellStyleEditable.setLocked(false);	 
			cellStyleEditable.setWrapText(true);
			cellStyleEditable.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP);
			
			CellStyle cellStyleReadOnly = workbook.createCellStyle(); 
			cellStyleReadOnly.setLocked(true);	 
			cellStyleReadOnly.setWrapText(true);
			cellStyleReadOnly.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP);  
			
			CellStyle cellStyleReadOnlyCenter = workbook.createCellStyle(); 
			cellStyleReadOnly.setLocked(true);	 
			cellStyleReadOnly.setWrapText(true);
			cellStyleReadOnly.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP);
			cellStyleReadOnlyCenter.setAlignment(CellStyle.ALIGN_CENTER);
			
			Font font = workbook.createFont();
			font.setBold(true);
			CellStyle cellStyleHeader = workbook.createCellStyle();  
			cellStyleHeader.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP);
			cellStyleHeader.setAlignment(CellStyle.ALIGN_CENTER);
			cellStyleHeader.setFont(font); 
			cellStyleHeader.setWrapText(true);
			cellStyleHeader.setLocked(true);
			
			//Create information Header row
			XSSFRow rowheadInfoHead = sheet.createRow(rowNumber++);			
			BankAuditUtil.createCell(sheet, rowheadInfoHead, 0, "Inspection Id", cellStyleHeader, 15*256);
			BankAuditUtil.createCell(sheet, rowheadInfoHead, 1, "Inspection Type", cellStyleHeader, 45*256);
			BankAuditUtil.createCell(sheet, rowheadInfoHead, 2, "RM Code", cellStyleHeader, 10*256);
			BankAuditUtil.createCell(sheet, rowheadInfoHead, 3, "User Detail", cellStyleHeader, 10*256);
		
			XSSFRow rowheadInfoValues = sheet.createRow(rowNumber++);			
			BankAuditUtil.createCell(sheet, rowheadInfoValues, 0, auditId, cellStyleReadOnlyCenter, 0);
			BankAuditUtil.createCell(sheet, rowheadInfoValues, 1, auditTypeCode, cellStyleReadOnlyCenter, 0);
			BankAuditUtil.createCell(sheet, rowheadInfoValues, 2, auditRatingParameterScoreList.get(0).getRmCode(), cellStyleReadOnlyCenter, 0);
			BankAuditUtil.createCell(sheet, rowheadInfoValues, 3, userType+"__"+role, cellStyleReadOnlyCenter, 0);
			  			
			//Create Header of Excel			
			XSSFRow rowhead = sheet.createRow(rowNumber++); 
			BankAuditUtil.createCell(sheet, rowhead, 0, "Param Code", cellStyleHeader, 0);
			
			//sheet.addMergedRegion(CellRangeAddress.valueOf("B"+rowNumber+":C"+rowNumber));			
			BankAuditUtil.createCell(sheet, rowhead, 1, "Parameter Name", cellStyleHeader, 0);
			
			BankAuditUtil.createCell(sheet, rowhead, 2, "Minimum Score", cellStyleHeader, 0);
			BankAuditUtil.createCell(sheet, rowhead, 3, "Maximum Score", cellStyleHeader, 0);
			
			BankAuditUtil.createCell(sheet, rowhead, 4, "Effective (Reference) Score", cellStyleHeader,12*256);
			BankAuditUtil.createCell(sheet, rowhead, 5, "Updated Score", cellStyleHeader, 10*256);
			BankAuditUtil.createCell(sheet, rowhead, 6, "Remarks", cellStyleHeader, 40*256);
			BankAuditUtil.createCell(sheet, rowhead, 7, "Updated Remarks", cellStyleHeader, 40*256);
		    	          		
 			for (AuditRatingParameterScore auditRatingParameterScore : auditRatingParameterScoreList) {	 				
	 			XSSFRow rowParam = sheet.createRow(rowNumber);	
 				BankAuditUtil.createCell(sheet, rowParam, 0, auditRatingParameterScore.getParamCode(), cellStyleReadOnlyCenter, 0);
 				
 				//sheet.addMergedRegion(CellRangeAddress.valueOf("B"+rowNumber+":C"+rowNumber));				
 				BankAuditUtil.createCell(sheet, rowParam, 1, auditRatingParameterScore.getParamName(), cellStyleReadOnly, 0);
 				
 				BankAuditUtil.createCell(sheet, rowParam, 2, auditRatingParameterScore.getMinScore(), cellStyleReadOnlyCenter, 0);
 				BankAuditUtil.createCell(sheet, rowParam, 3, auditRatingParameterScore.getMaxScore(), cellStyleReadOnlyCenter, 0);
 				
				if(rolesListInspector.contains(role)){					
					BankAuditUtil.createCell(sheet, rowParam, 4, auditRatingParameterScore.getScoreAssessed(), cellStyleReadOnlyCenter, 0);					
				}else if(userType.equalsIgnoreCase("HO")){
					BankAuditUtil.createCell(sheet, rowParam, 4, auditRatingParameterScore.getScoreEffectiveHO(), cellStyleReadOnlyCenter, 0); 
				} else BankAuditUtil.createCell(sheet, rowParam, 4, auditRatingParameterScore.getScoreEffective(), cellStyleReadOnlyCenter, 0); 
				
				BankAuditUtil.createCell(sheet, rowParam, 5, "", cellStyleEditable, 10*256);
				
				if(rolesListInspector.contains(role)){	// for AIO and PIO Maker Remarks				
					BankAuditUtil.createCell(sheet, rowParam, 6, auditRatingParameterScore.getMakerRemarks(), cellStyleReadOnly, 0);				
				}else if(userType.equalsIgnoreCase("HO")){
					if("RPR".equalsIgnoreCase(ratingParameterStatus) && auditRatingParameterScore.getHoRemarks()==null)
						BankAuditUtil.createCell(sheet, rowParam, 6, auditRatingParameterScore.getReviewerRemarks(), cellStyleReadOnly, 0);
					else BankAuditUtil.createCell(sheet, rowParam, 6, auditRatingParameterScore.getHoRemarks(), cellStyleReadOnly, 0);
				}else { // For RO
					if("RPP".equalsIgnoreCase(ratingParameterStatus) && auditRatingParameterScore.getReviewerRemarks()==null)
						BankAuditUtil.createCell(sheet, rowParam, 6, auditRatingParameterScore.getMakerRemarks(), cellStyleReadOnly, 0);
					else BankAuditUtil.createCell(sheet, rowParam, 6, auditRatingParameterScore.getReviewerRemarks(), cellStyleReadOnly, 0);					
				}							
				BankAuditUtil.createCell(sheet, rowParam, 7, "", cellStyleEditable, 40*256); 	
				
				XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
				XSSFDataValidationConstraint dvConstraint = 
				    (XSSFDataValidationConstraint)
				    dvHelper.createNumericConstraint(XSSFDataValidationConstraint.ValidationType.DECIMAL, XSSFDataValidationConstraint.OperatorType.BETWEEN,
				        String.valueOf(auditRatingParameterScore.getMinScore()), String.valueOf(auditRatingParameterScore.getMaxScore()));
 
				CellRangeAddressList addressList = new CellRangeAddressList(rowNumber, rowNumber, 5, 5); 
				XSSFDataValidation validation =(XSSFDataValidation)dvHelper.createValidation(dvConstraint, addressList);
				validation.setSuppressDropDownArrow(false);
				validation.setShowErrorBox(true);
				validation.createErrorBox("Error", "Please enter score between "+auditRatingParameterScore.getMinScore()+" and "+auditRatingParameterScore.getMaxScore());
				sheet.addValidationData(validation);
				rowNumber++; 
			}
 			
 			rowNumber++;
	       /* DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper(); 
            DataValidationConstraint constraint = dataValidationHelper.createExplicitListConstraint(new String[] { "1", "2", "3", "4", "5" }) ;             
            XSSFDataValidation validation = (XSSFDataValidation) dataValidationHelper.createValidation(constraint,
            		new CellRangeAddressList(3, rowNumber, 5, 5));	 // CellRangeAddressList(int firstRow, int lastRow, int firstCol, int lastCol)          
            validation.createErrorBox("Error", "Please enter score between 1 and 5");
            validation.setSuppressDropDownArrow(true);
            validation.setShowErrorBox(true);
            sheet.addValidationData(validation);*/
            
 			workbook.write(out);
            out.toByteArray();
            return out; 
		}catch ( Exception ex ) {
			logger.error(ex);
			ex.printStackTrace(System.out);
        }		
		return null;
	}
	
	@Override
	public List<AuditRatingSummaryDto> getAuditRatingSummaryWithOverlay(int legalEntityCode, String auditId, String rmCode, String userType) {
		logger.info("Inside ServiceImpl getAuditRatingSummaryWithOverlay .."+ auditId+" :: "+ rmCode);
		if("RM04".equalsIgnoreCase(rmCode) || "RM03".equalsIgnoreCase(rmCode)) {
			//List<AuditRatingSummaryDto> ratSummry= getAuditRatingSummary( legalEntityCode,  auditId,  rmCode,  userType);
			List<AuditRatingSummaryDto> ratSummry= getAuditRatingSummaryOverlay( legalEntityCode,  auditId,  rmCode,  userType); // method updated to segregate Widget1 and Widget3
			return getAuditRatingSummaryForReportExisting(ratSummry, legalEntityCode, auditId, rmCode, userType);	
		}else return auditRatingParameterScoreDao.getAuditRatingSummaryForReport(legalEntityCode, auditId, rmCode, userType);		
	}
	
	@Override
	public RatingParameterReportDto getRatingReportDataByAuditId(int legalEntityCode,String auditId, String auditTypeCode, String unitId, String rmCode, String userType) {
		RatingParameterReportDto reportDto= new RatingParameterReportDto();
		
		reportDto.setCategoryWiseRatingLst(auditRatingParameterScoreDao.getCategoryWiseRatingsByAuditId(legalEntityCode,auditId,auditTypeCode)); // Parameter scores Category + Measurement Type(Quantitative/Qualitative)
		
		// D. Deflators
		List<AuditRatingModelOverlay> auditModelOverlaysLst=auditRatingParameterScoreDao.getModelOverlayByAuditId(legalEntityCode, auditId);
		reportDto.setManagementOverlayLst((List<AuditRatingModelOverlay>) auditModelOverlaysLst.stream().filter(p -> p.getLevel().equals("MODEL")).collect(Collectors.toList())); // Model overlay deatils
		reportDto.setDeflatorLst((List<AuditRatingModelOverlay>) auditModelOverlaysLst.stream().filter(p -> p.getLevel().equals("PARAMETER")).collect(Collectors.toList()));  // Deflator details
		
		if("RM03".equalsIgnoreCase(rmCode) || "RM04".equalsIgnoreCase(rmCode)) {// Report for Existing Ratings
			List<AuditRatingSummaryDto> summaryDtoLst=getAuditRatingSummaryWithOverlay(legalEntityCode, auditId, rmCode, userType);
			reportDto.setCategoryWiseRatingSummaryLst(summaryDtoLst);
			
			// Report Section E. Supervisory Rating 
			AuditRatingScoreDto scrDto= new AuditRatingScoreDto();
			scrDto.setLegalEntityCode(legalEntityCode);
			scrDto.setAuditId(auditId);
			scrDto.setRmCode(rmCode);
			for(AuditRatingSummaryDto sDto:summaryDtoLst) {
				if("CTALL".equalsIgnoreCase(sDto.getCategoryCode())) scrDto.setNormalisedAfterOverlayScore(sDto.getTotalweightedScore());
				//if("FINAL_RATING".equalsIgnoreCase(sDto.getCategoryCode())) scrDto.setFinalRating(sDto.getFinalRating());FINAL_RATING_AFTR_DEFLATOR
				if("FINAL_RATING_AFTR_DEFLATOR".equalsIgnoreCase(sDto.getCategoryCode())) scrDto.setFinalRating(sDto.getFinalRating());
			}
			reportDto.setFinalRatingScoreGrade(scrDto);
			//End here, Report Section E
			
		}else { // Report for Enhanced Ratings
			// Code commented (2 lines) on 08Sep24, to use the same 3rd widget service for the 4th widget 
			//reportDto.setCategoryWiseRatingSummaryLst(auditRatingParameterScoreDao.getAuditRatingSummaryForReport(legalEntityCode, auditId, rmCode, userType));  // Scores Categorywise
			//reportDto.setFinalRatingScoreGrade(auditRatingParameterScoreDao.getAuditRatingScores(legalEntityCode, auditId, rmCode)); // final rating
			
			List<AuditRatingSummaryDto> summaryDtoLst=auditRatingParameterScoreDao.getAuditRatingSummaryForReport(legalEntityCode, auditId, rmCode, userType);
			reportDto.setCategoryWiseRatingSummaryLst(summaryDtoLst);
			
			// Report Section E. Supervisory Rating 
			AuditRatingScoreDto scrDto= new AuditRatingScoreDto();
			scrDto.setLegalEntityCode(legalEntityCode);
			scrDto.setAuditId(auditId);
			scrDto.setRmCode(rmCode);
			for(AuditRatingSummaryDto sDto:summaryDtoLst) {
				if("CTALL".equalsIgnoreCase(sDto.getCategoryCode())) scrDto.setNormalisedAfterOverlayScore(sDto.getTotalweightedScore());
				if("FINAL_RATING_AFTR_DEFLATOR".equalsIgnoreCase(sDto.getCategoryCode())) scrDto.setFinalRating(sDto.getFinalRating());
			}
			reportDto.setFinalRatingScoreGrade(scrDto);
			//End here, Report Section E			
			
		}
		return reportDto; 
	}
	
	@Override
	public RatingParameterReportDto getRatingReportDataByAuditIdHORO(int legalEntityCode,String auditId, String auditTypeCode, String unitId, String rmCode, String userType) {
		RatingParameterReportDto reportDto= new RatingParameterReportDto();
		
		reportDto.setCategoryWiseRatingLst(auditRatingParameterScoreDao.getCategoryWiseRatingsByAuditIdHORO(legalEntityCode,auditId,auditTypeCode)); // Parameter scores Category + Measurement Type(Quantitative/Qualitative)
		
		List<AuditRatingSummaryDto> roSummary=auditRatingParameterScoreDao.getAuditRatingSummaryForReport(legalEntityCode, auditId, rmCode, "RO");
		List<AuditRatingSummaryDto> hoSummary=auditRatingParameterScoreDao.getAuditRatingSummaryForReport(legalEntityCode, auditId, rmCode, "HO");
		List<AuditRatingSummaryDto> finalSummary=new ArrayList<>();
		finalSummary.addAll(roSummary);
		logger.info(" roSummary size .. "+roSummary!=null?roSummary.size():"NULL");
		logger.info(" hoSummary size .. "+hoSummary!=null?hoSummary.size():"NULL");
		//for(AuditRatingSummaryDto dto: roSummary) {
		for(int i=0; i<roSummary.size(); i++) {
			if(hoSummary!=null && !hoSummary.isEmpty() && hoSummary.size() >=i && hoSummary.get(i) !=null) {
				finalSummary.get(i).setQualitativeScoreHO(hoSummary.get(i).getQualitativeScore());
				finalSummary.get(i).setQuantitativeSoreHO(hoSummary.get(i).getQuantitativeSore());
				finalSummary.get(i).setTotalScoreHO(hoSummary.get(i).getTotalScore());
				finalSummary.get(i).setTotalweightedScoreHO(hoSummary.get(i).getTotalweightedScore());
				finalSummary.get(i).setTotalweightedScoreDirectHO(hoSummary.get(i).getTotalweightedScoreDirect());
				finalSummary.get(i).setTotalScoreNormalizedHO(hoSummary.get(i).getTotalScoreNormalized());
				finalSummary.get(i).setTotalweightedScoreNormalizedHO(hoSummary.get(i).getTotalweightedScoreNormalized());
				finalSummary.get(i).setTotalweightedScoreDirectNormalizedHO(hoSummary.get(i).getTotalweightedScoreDirectNormalized());
				finalSummary.get(i).setFinalRatingHO(hoSummary.get(i).getFinalRating());
			}
		}
		
		reportDto.setCategoryWiseRatingSummaryLst(finalSummary);  // Scores Categorywise
		//reportDto.setCategoryWiseRatingSummaryLst(auditRatingParameterScoreDao.getAuditRatingSummaryForReport(legalEntityCode, auditId, rmCode, userType));  // Scores Categorywise
		
		List<AuditRatingModelOverlay> auditModelOverlaysLst=auditRatingParameterScoreDao.getModelOverlayByAuditId(legalEntityCode, auditId);
		reportDto.setManagementOverlayLst((List<AuditRatingModelOverlay>) auditModelOverlaysLst.stream().filter(p -> p.getLevel().equals("MODEL")).collect(Collectors.toList())); // Model overlay deatils
		reportDto.setDeflatorLst((List<AuditRatingModelOverlay>) auditModelOverlaysLst.stream().filter(p -> p.getLevel().equals("PARAMETER")).collect(Collectors.toList()));  // Deflator details
		
		// Code commented (2 lines) on 08Sep24, to use the same 3rd widget service for the 4th widget 
		//AuditRatingScoreDto ratingsRo=auditRatingParameterScoreDao.getAuditRatingScores(legalEntityCode, auditId, rmCode);
		//AuditRatingScoreDto ratingsHo=auditRatingParameterScoreDao.getAuditRatingScoresHO(legalEntityCode, auditId, rmCode);
		
		// Code added on 08Sep24, to use the same 3rd widget service for the 4th widget
		AuditRatingScoreDto ratingsRo= new AuditRatingScoreDto();
		ratingsRo.setLegalEntityCode(legalEntityCode);	
		ratingsRo.setAuditId(auditId);
		ratingsRo.setRmCode(rmCode);
		for(AuditRatingSummaryDto sDto:roSummary) {
			if("CTALL".equalsIgnoreCase(sDto.getCategoryCode())) ratingsRo.setNormalisedAfterOverlayScore(sDto.getTotalweightedScore());
			if("FINAL_RATING_AFTR_DEFLATOR".equalsIgnoreCase(sDto.getCategoryCode())) ratingsRo.setFinalRating(sDto.getFinalRating());
		}
		
		AuditRatingScoreDto ratingsHo= new AuditRatingScoreDto();
		ratingsHo.setLegalEntityCode(legalEntityCode);	
		ratingsHo.setAuditId(auditId);
		ratingsHo.setRmCode(rmCode);
		for(AuditRatingSummaryDto sDto:hoSummary) {
			if("CTALL".equalsIgnoreCase(sDto.getCategoryCode())) ratingsHo.setNormalisedAfterOverlayScore(sDto.getTotalweightedScore());
			if("FINAL_RATING_AFTR_DEFLATOR".equalsIgnoreCase(sDto.getCategoryCode())) ratingsHo.setFinalRating(sDto.getFinalRating());
		}
		// end here, 08Sep24		
		
		if(ratingsRo !=null) {
			ratingsRo.setNormalisedScoreHO(ratingsHo.getNormalisedScore());
			ratingsRo.setNormalisedAfterOverlayScoreHO(ratingsHo.getNormalisedAfterOverlayScore());
			ratingsRo.setFinalRatingHO(ratingsHo.getFinalRating());
		}
		reportDto.setFinalRatingScoreGrade(ratingsRo);
		//reportDto.setFinalRatingScoreGrade(auditRatingParameterScoreDao.getAuditRatingScores(legalEntityCode, auditId, rmCode)); // final rating
		
		return reportDto; 
	}
	
	@Override
	public RatingParameterReportDto getRatingReportDataByAuditIdHOROExisting(int legalEntityCode,String auditId, String auditTypeCode, String unitId, String rmCode, String userType) {
		RatingParameterReportDto reportDto= new RatingParameterReportDto();
		reportDto.setCategoryWiseRatingLst(auditRatingParameterScoreDao.getCategoryWiseRatingsByAuditIdHORO(legalEntityCode,auditId,auditTypeCode)); // Parameter scores Category + Measurement Type(Quantitative/Qualitative)
		
		List<AuditRatingSummaryDto> roSummary=getAuditRatingSummaryWithOverlay(legalEntityCode, auditId, rmCode, "RO");
		List<AuditRatingSummaryDto> hoSummary=getAuditRatingSummaryWithOverlay(legalEntityCode, auditId, rmCode, "HO");
		List<AuditRatingSummaryDto> finalSummary=new ArrayList<>();
		finalSummary.addAll(roSummary);
		//for(AuditRatingSummaryDto dto: roSummary) {
		for(int i=0; i<roSummary.size(); i++) {
			if(hoSummary!=null && hoSummary.size() >=i && hoSummary.get(i) !=null) {
				finalSummary.get(i).setQualitativeScoreHO(hoSummary.get(i).getQualitativeScore());
				finalSummary.get(i).setQuantitativeSoreHO(hoSummary.get(i).getQuantitativeSore());
				finalSummary.get(i).setTotalScoreHO(hoSummary.get(i).getTotalScore());
				finalSummary.get(i).setTotalweightedScoreHO(hoSummary.get(i).getTotalweightedScore());
				finalSummary.get(i).setTotalweightedScoreDirectHO(hoSummary.get(i).getTotalweightedScoreDirect());
				finalSummary.get(i).setTotalScoreNormalizedHO(hoSummary.get(i).getTotalScoreNormalized());
				finalSummary.get(i).setTotalweightedScoreNormalizedHO(hoSummary.get(i).getTotalweightedScoreNormalized());
				finalSummary.get(i).setTotalweightedScoreDirectNormalizedHO(hoSummary.get(i).getTotalweightedScoreDirectNormalized());
				finalSummary.get(i).setFinalRatingHO(hoSummary.get(i).getFinalRating());
			}
		}
		
		reportDto.setCategoryWiseRatingSummaryLst(finalSummary);
		
		// D. Deflators
		List<AuditRatingModelOverlay> auditModelOverlaysLst=auditRatingParameterScoreDao.getModelOverlayByAuditId(legalEntityCode, auditId);
		reportDto.setManagementOverlayLst((List<AuditRatingModelOverlay>) auditModelOverlaysLst.stream().filter(p -> p.getLevel().equals("MODEL")).collect(Collectors.toList())); // Model overlay deatils
		reportDto.setDeflatorLst((List<AuditRatingModelOverlay>) auditModelOverlaysLst.stream().filter(p -> p.getLevel().equals("PARAMETER")).collect(Collectors.toList()));  // Deflator details
					
		// Report Section E. Supervisory Rating 
		AuditRatingScoreDto scrDto= new AuditRatingScoreDto();
		scrDto.setLegalEntityCode(legalEntityCode);
		scrDto.setAuditId(auditId);
		scrDto.setRmCode(rmCode);
		for(AuditRatingSummaryDto sDto:finalSummary) {
			if("CTALL".equalsIgnoreCase(sDto.getCategoryCode())) {
				scrDto.setNormalisedAfterOverlayScore(sDto.getTotalweightedScore());
				scrDto.setNormalisedAfterOverlayScoreHO(sDto.getTotalweightedScoreHO());
			}if("FINAL_RATING_AFTR_DEFLATOR".equalsIgnoreCase(sDto.getCategoryCode())) {  // FINAL_RATING
				scrDto.setFinalRating(sDto.getFinalRating());
				scrDto.setFinalRatingHO(sDto.getFinalRatingHO());
			}
		}
		reportDto.setFinalRatingScoreGrade(scrDto);
		//End here, Report Section E
		
		return reportDto; 
	}
	
	public List<AuditRatingSummaryDto> getAuditRatingSummaryForReportExisting(List<AuditRatingSummaryDto> ratSummary,int legalEntityCode, String auditId, String rmCode, String userType) {
		String ratingScr=null;
		logger.info("getAuditRatingSummaryForReportExisting .. "+ auditId+" :: "+rmCode+" :: "+ userType);
		for(AuditRatingSummaryDto sumDto: ratSummary) {
			sumDto.setTotalScoreNormalized(sumDto.getTotalScore());
			sumDto.setTotalweightedScoreNormalized(sumDto.getTotalweightedScore());
			sumDto.setTotalScore(""); // Make the scores blank for 1 to 5 column
			if("CTALL".equalsIgnoreCase(sumDto.getCategoryCode())) {
				logger.info("CTALL ==> "+sumDto.getCategoryCode() + " :: "+sumDto.getTotalweightedScore());
				ratingScr=sumDto.getTotalweightedScore();
			}
		}
		// To get the Rating
		AuditRatingSummaryDto auditSummaryDto = new AuditRatingSummaryDto();
		auditSummaryDto.setLegalEntityCode(legalEntityCode);
		auditSummaryDto.setAuditId(auditId);
		auditSummaryDto.setRmCode(rmCode);
		auditSummaryDto.setCategoryCode("FINAL_RATING");
		auditSummaryDto.setCategoryName("Composite Supervisory Rating");
		auditSummaryDto.setFinalRating(auditRatingParameterScoreDao.getRatingFromScore(legalEntityCode, auditId, rmCode, ratingScr));
		String finalRating= auditSummaryDto.getFinalRating();
		ratSummary.add(auditSummaryDto);
		
		//To get the Rating after Deflator
		auditSummaryDto = new AuditRatingSummaryDto();
		auditSummaryDto.setLegalEntityCode(legalEntityCode);
		auditSummaryDto.setAuditId(auditId);
		auditSummaryDto.setRmCode(rmCode);
		auditSummaryDto.setCategoryCode("FINAL_RATING_AFTR_DEFLATOR");
		auditSummaryDto.setCategoryName("Final Rating After Deflator");
		
		Map defScore=auditRatingParameterScoreDao.getRatingFromDeflatorScore(legalEntityCode, auditId, rmCode, ratingScr, userType);
		String ratingDfltrScr=null, deflatorApplied="Y";
		try {
			logger.info("finalRating .. "+finalRating);
			if(!defScore.isEmpty()) {
				deflatorApplied=defScore.get("DEFLATOR_APPLIED").toString();
				if("Y".equalsIgnoreCase(deflatorApplied)) {
					ratingDfltrScr=defScore.containsKey("DEFLATOR_MAX_PER_SCORE")?defScore.get("DEFLATOR_MAX_PER_SCORE").toString():null;
					logger.info(" ratingScr :: "+ratingScr +" ratingDfltrScr :: "+ratingDfltrScr);
					if(ratingScr !=null && ratingDfltrScr !=null && Double.parseDouble(ratingDfltrScr) < Double.parseDouble(ratingScr))
						finalRating=defScore.containsKey("DEFLATOR_RATING")?defScore.get("DEFLATOR_RATING").toString():null;
				}else finalRating="";
				logger.info("finalRating Aftr .. "+finalRating);	
			}
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		auditSummaryDto.setFinalRating(finalRating);
		ratSummary.add(auditSummaryDto);
		//End, Rating after Deflator
		
		return ratSummary;
	}
	
	/*@Override
	public List getRatingAndObsCntGrpSGrpwiseByAuditId(int legalEntityCode, String auditTypeCode, String planId,String auditId) {
		List ratingObscntlst= null;
		ratingObscntlst=myAuditsDao.getRatingAndObsCntGrpSGrpwiseByAuditId(legalEntityCode,auditTypeCode,planId,auditId); 
		Map<String, String> processCntMap = myAuditsDao.getObsRespRvwCnt(legalEntityCode, auditTypeCode, planId, auditId);
		ratingObscntlst.add(processCntMap);
		return ratingObscntlst;
	}*/
		
}
