package com.bankaudit.rest.process.dao;

import java.math.BigDecimal;
import java.sql.Timestamp;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Comparator;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import org.apache.commons.beanutils.BeanUtils;
import org.hibernate.HibernateException;
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.context.annotation.Lazy;
import org.springframework.stereotype.Repository;

import com.bankaudit.constants.BankAuditConstant;
import com.bankaudit.dao.AbstractDao;
import com.bankaudit.dto.DataTableResponse;
import com.bankaudit.dto.process.AuditRatingParameterScoreDto;
import com.bankaudit.dto.process.AuditRatingScoreDto;
import com.bankaudit.dto.process.AuditRatingSummaryDto;
import com.bankaudit.dto.process.BranchPerformanceBUResponse;
import com.bankaudit.dto.process.RatingParameterCategoryDto;
import com.bankaudit.dto.process.RatingParameterDtlsDto;
import com.bankaudit.dto.process.RatingParameterQnQLDto;
import com.bankaudit.dto.process.RatingParameterReportDto;
import com.bankaudit.model.MaintRatingModelOverlay;
import com.bankaudit.model.MaintRatingParameterDtls;
import com.bankaudit.model.User;
import com.bankaudit.process.model.AuditRatingModelOverlay;
import com.bankaudit.process.model.AuditRatingParameterCategoryScore;
import com.bankaudit.process.model.AuditRatingParameterScore;
import com.bankaudit.process.model.AuditSchedule;
import com.bankaudit.process.model.ChecklistOfflineInspection;
import com.bankaudit.process.model.IfcRatingData;
import com.bankaudit.service.BusinessDateService;
import com.bankaudit.service.MaintEntityService;
import com.bankaudit.util.BankAuditUtil;

/*MaintEntityDataEffect */
@Repository("auditRatingParameterScoreDao")
public class AuditRatingParameterScoreDaoImpl extends AbstractDao implements AuditRatingParameterScoreDao{
	
	@Autowired
	BusinessDateService businessDateService;
		
	@Autowired
	AuditScheduleDao auditScheduleDao;
	
	@Autowired
	MaintEntityService maintEntityService;
	
	@Lazy
	@Autowired
	AuditRatingParameterScoreDao auditRatingParameterScoreDao; 
	
		
	static final Logger logger = Logger.getLogger(AuditRatingParameterScoreDaoImpl.class);
	
	@SuppressWarnings("unchecked")
	@Override
	public DataTableResponse getMuAuditsList(int legalEntityCode,String userId, String search, Integer orderColumn,
			String orderDirection, Integer page, Integer size, String roleId, String userLevel,
			LocalDate financialYear, String auditTypeCode, List<LocalDate>  financialYearStrtEndList) { // Added to filter the records based on Financial Year and AuditType

		Session session = getSession();
		session.setDefaultReadOnly(true);
        
		logger.info("Inside AuditRatingParameterScoreDaoImpl getMuAuditsList .."+userId + " :: "+roleId +" :: "+ userLevel);
		/********************** This block is to retrieve the User details --> UserId and UserName **************************/
		StringBuilder queryString0=new StringBuilder(// " Select concat(u.firstName , ' ' , u.lastName) as firstName, u.userId "
				 " FROM User u  WHERE u.legalEntityCode =:legalEntityCode ");
		
		Query query1=session.createQuery(queryString0.toString())
				.setParameter("legalEntityCode", legalEntityCode);
		
		List lst = query1.list();
		HashMap<String, String> userDtls = new HashMap<String, String>();
		for ( Iterator iter = lst.iterator(); iter.hasNext(); ) {
			User usr = (User) iter.next();
			if(usr.getUserId().equalsIgnoreCase(userId))
				userDtls.put(usr.getUserId(), "Me"); // for the same User we are stamping as "You"
			else userDtls.put(usr.getUserId(), (usr.getFirstName()==null?"":usr.getFirstName()) 
											+ (usr.getMiddleName()==null?"":(" "+usr.getMiddleName())) 
											+ (usr.getLastName()==null?"":(" "+usr.getLastName())) );	
		}
		logger.info("userId is .... "+ userId+" serach:: "+search +" legal entity code:: "+legalEntityCode);
		logger.info("User details val is .... "+ userDtls);
		/******************** User Retrieval logic, Ends here ********************************/
		
		/***************** Logic to retrieve duration and No. of Audits from general parameter table and  ****
		 ***************** 	comparing both to find out the minimum, Starts here 	      ********************/
		Date businessDate =businessDateService.getBusinessDateTimestampByLegalEntityCode(legalEntityCode);
		logger.info("businessDate .... "+ businessDate);
		StringBuilder queryString2=new StringBuilder( " select nd.auditsInInterval,nads.noAudits from " 
				+ " ( select count(*) as auditsInInterval from audit_schedule ash, audit_team atm " 
				+ "	where ash.plan_id=atm.plan_id and ash.audit_id = atm.audit_id and ash.legal_entity_code=atm.legal_entity_code and atm.user_id=:userId " 
				+ "          and (ash.schedule_start_date between :businessDate and " 
				+ "				ADDDATE(:businessDate, INTERVAL (select g.value from general_parameter g where key_1='AUDITOR_VISIBILITY' AND key_2='DAYS' AND g.legal_entity_code=ash.legal_entity_code ) DAY))  ) as nd, " 
				+ " ( select g.value as noAudits from general_parameter g where g.key_1='AUDITOR_VISIBILITY' AND g.key_2='NO_OF_AUDITS' AND g.legal_entity_code=:legalEntityCode) as nads "  
				); 
		//(select g.value from GeneralParameter g where g.key1='AUDITOR_VISIBILITY' AND g.key2='DAYS' AND g.legalEntityCode=:legalEntityCode) 
		
		Query query2lst=session.createSQLQuery(queryString2.toString())
				.setParameter("legalEntityCode", legalEntityCode).setParameter("userId", userId)
				.setParameter("businessDate", businessDate);
		
		Object obj = query2lst.uniqueResult();
		int auidtsDisplay = 100;
		try{
			if(obj != null  && ((Object[])obj).length > 0)
			{
				int auditsInInterval  =Integer.parseInt( ((Object[])obj)[0].toString());
				int  noOfAudit=Integer.parseInt( ((Object[])obj)[1].toString());
				logger.info("auditsInInterval .... "+ auditsInInterval +" noOfAudit.... "+noOfAudit);
				if(auditsInInterval > noOfAudit) auidtsDisplay=noOfAudit;
				else auidtsDisplay = auditsInInterval;
			}
		}catch(Exception e) {
			logger.info("Exception occured ....");
		}
		logger.info("auidtsDisplay .... "+auidtsDisplay);
		/******************** Logic for no of Audits, Ends here ********************************/
			
		/***************** Logic to retrieve the Visible Audits, which are in Planned state. Based on the above Output ****/
		logger.info("businessDate .... "+ businessDate);
		StringBuilder queryString3=new StringBuilder( " select audit_id from audit_schedule s "
				+ "	where legal_entity_code=:legalEntityCode and audit_status in ('AP') " 
				+ "		and audit_id in(select t.audit_id from audit_team t where t.legal_entity_code=s.legal_entity_code and t.user_id=:userId) " 
				+ "	order by schedule_start_date asc limit :auidtsDisplay "); 
		
		List visibleAuditList=session.createNativeQuery(queryString3.toString())
				.setParameter("legalEntityCode", legalEntityCode).setParameter("userId", userId)
				.setParameter("auidtsDisplay", auidtsDisplay).list();
		logger.info("visibleAuditList .... "+visibleAuditList);
		if(visibleAuditList != null && visibleAuditList.size() < 1) visibleAuditList.add("NoAudit"); // If the Audit visibility is 0, then we are passing NoAudit
		/******************** Logic for Visible Audits, Ends here ********************************/
		
		/************************** ********************************************/
		/*StringBuilder queryString4=new StringBuilder( " select user_role_id from user_role_mapping where legal_entity_code=:legalEntityCode and user_id=:userId limit 1"); 
		String roleId=(String)session.createNativeQuery(queryString4.toString())
				.setParameter("legalEntityCode", legalEntityCode).setParameter("userId", userId)
				.uniqueResult();*/
		logger.info("roleId .... "+roleId);
		List<String> maintEntities = null;
		if(roleId != null && (roleId.equalsIgnoreCase("G1") || roleId.equalsIgnoreCase("G6") || roleId.equalsIgnoreCase("G12") )) {
			maintEntities=maintEntityService.getSubBranchesGyUserIdOrUnitId(legalEntityCode,"user",userId);
			logger.info("maintEntities .... "+maintEntities);
		}		
		/****************************** ****************************************/
		
		
		boolean isDeptAsRMD =false;
		//isDeptAsRMD = reportDao.isDeptAsRMD(legalEntityCode, "", userId);
		
		DataTableResponse dataTableResponse=new DataTableResponse();
		try {
		//StringBuilder queryString=new StringBuilder(" from AuditSchedule where legalEntityCode =:legalEntityCode ");
		StringBuilder queryString=new StringBuilder("select sch as auditSchedule, " 
				+ " legalEntityCode,"
				+ "(select concat(auditTypeCode , '-' , auditTypeDesc) from MaintAuditTypeDesc at where at.auditTypeCode=sch.auditTypeCode and at.legalEntityCode= sch.legalEntityCode ) as auditTypeCode,"
				
				+ "planId,auditId, "
				//+ " (Select concat(unitCode , '-' , unitName) from  MaintEntity e where e.unitCode =sch.unitId and e.legalEntityCode =sch.legalEntityCode ) as unitId,  "
				+ " (Select unitName from  MaintEntity e where e.unitCode =sch.unitId and e.legalEntityCode =sch.legalEntityCode ) as unitName,  "
				
				+ " (Select g.description from  MaintEntity e,GeneralParameter g where e.unitCode =sch.unitId and e.legalEntityCode =sch.legalEntityCode and e.legalEntityCode =g.legalEntityCode and g.key1='UNIT_TYPE' and g.key2='UNIT_TYPE' and g.value=e.unitType) as unitType, "
				
				+ " (Select c.criticalityDesc from  MaintEntity e,MaintCriticality c where e.unitCode =sch.unitId and e.legalEntityCode =sch.legalEntityCode and e.legalEntityCode =c.legalEntityCode and e.uCriticality=c.criticalityCode and c.criticalityOfType='Entity') as uCriticality, "
				
				//+ " (Select concat(u.firstName , ' ' , u.lastName) from  User u,AuditTeam t where u.userId =t.userId and t.auditId=sch.auditId and t.legalEntityCode =u.legalEntityCode and t.legalEntityCode =sch.legalEntityCode and t.userType='L') as teamLead, "
				+ " (Select t.userId from  AuditTeam t where t.auditId=sch.auditId and t.legalEntityCode =sch.legalEntityCode and t.userType='L') as teamLead, "
				
				+" duration," //Code to comment //+ " (Select p.duration from  MaintEntity e,MaintAuditParameter p where e.unitCode =sch.unitId and e.unitType=p.unitType and e.uCriticality=p.uCriticality and sch.auditTypeCode=p.auditTypeCode and e.legalEntityCode =sch.legalEntityCode and e.legalEntityCode =p.legalEntityCode ) as duration, "
				+" frequency," //Code to comment //+ " (Select p.frequency from  MaintEntity e,MaintAuditParameter p where e.unitCode =sch.unitId and e.unitType=p.unitType and e.uCriticality=p.uCriticality and sch.auditTypeCode=p.auditTypeCode and e.legalEntityCode =sch.legalEntityCode and e.legalEntityCode =p.legalEntityCode ) as frequency, "
				//+ " (select tm.auditTypeCode,GROUP_CONCAT(tm.name) from (Select t.auditTypeCode,t.planId, t.auditId, concat(u.firstName , ' ' , u.lastName) as name from  User u,AuditTeam t where u.userId =t.userId and t.auditId=sch.auditId and t.legalEntityCode =u.legalEntityCode and t.legalEntityCode =sch.legalEntityCode and t.userType='T') tm where  GROUP BY tm.auditTypeCode  ) as teamMember "
				+ " (Select concat(sch.auditStatus , '-' , g.description) from  GeneralParameter g where g.legalEntityCode =sch.legalEntityCode and g.key1='STATUS' and g.key2='AUDIT_STATUS' and g.value=sch.auditStatus) as auditStatus, "
				
				//+ " (Select concat(u.userId , '-' , u.firstName, ' ',u.middleName,' ',u.lastName) from  User u where u.legalEntityCode =sch.legalEntityCode and u.userId=sch.maker) as maker "
				+ " (Select concat(u.userId , '-' , ifnull(u.firstName,''), ' ',ifnull(u.middleName,''),' ',ifnull(u.lastName,'') ) "
				+ " from  User u where u.legalEntityCode =sch.legalEntityCode and u.userId=sch.maker) as maker "
								
				//+ ", (Select count(*) from  AuditObservation o where o.legalEntityCode =sch.legalEntityCode and o.auditId=sch.auditId and o.complianceCategory='N' and o.reviewStatus='R' ) as responseReject "
				//+ ", (Select count(*) from  AuditObservation o where o.legalEntityCode =sch.legalEntityCode and o.auditId=sch.auditId and o.complianceCategory='C' and o.submissionStatus='A') as complianceCount "
				//+ ", (Select count(*) from  AuditObservation o where o.legalEntityCode =sch.legalEntityCode and o.auditId=sch.auditId and o.complianceCategory='NA' and o.submissionStatus='A') as notApplicableCount "
				+" ,0,0,0 "  // No need to calculate responseReject, Compliance, NA
				//+ " , auditStatus "
				+ ", (Select g.value from GeneralParameter g where g.legalEntityCode =sch.legalEntityCode and g.key1='AUDIT_EARLY_START' and g.key2='EARLY_START_ALLOWED') as earlyStartAllowed "
				+ ", (Select g.value from GeneralParameter g where g.legalEntityCode =sch.legalEntityCode and g.key1='AUDIT_OFFSET_DAYS' and g.key2='AUDIT_OFFSET_DAYS') as auditOffsetDays " 
				+ ", (Select concat(sch.auditStatus , '-' , g.description) from  GeneralParameter g where g.legalEntityCode =sch.legalEntityCode and g.key1='STATUS' and g.key2='AUDIT_STATUS' and g.value=sch.observationStatus) as auditStatusTemp " // This result to stamp in Audit status in the Observation status is AE i.e Audit Reported
				+ ", (select group_concat(t.userId) from AuditTeam t where t.legalEntityCode=sch.legalEntityCode AND t.auditId=sch.auditId AND t.userType='M' ) AS teamMember "
				+" ,   sch.reportStatus " // Report review status added for BaaSDOS implementation 
				+ ",  (Select concat(sch.reportStatus , '-' , g.description) from  GeneralParameter g where g.legalEntityCode =sch.legalEntityCode and g.key1='STATUS' and g.key2='REPORT_STATUS' and g.value=sch.reportStatus) as reportStatusDesc " // Report review status desc added for BaaSDOS implementation 
								
			 	+ " , (Select concat(sch.status , '-' , g.description) from  GeneralParameter g where g.legalEntityCode =sch.legalEntityCode "
				+ " and g.key1='STATUS' and g.key2='STATUS' and g.value=sch.status) as authStatus "  
				
				+ " , (SELECT concat(e2.unitName,'-',e2.unitCode) FROM MaintEntity e1, MaintEntity e2 WHERE e1.parentUnitCode=e2.unitCode AND e1.unitCode=sch.unitId and e1.legalEntityCode =sch.legalEntityCode and e1.legalEntityCode=e2.legalEntityCode ) as parentUnitName "  // 23
				+ " , (SELECT unitName from  MaintEntity e where e.unitCode =sch.unitId and e.legalEntityCode =sch.legalEntityCode ) as unitName  " // 24
				+ " , ratingParameterReviewerAssigned, ratingParameterHoReviewer "  // 26 - Added as part of UI/UX
				+ " from AuditSchedule sch where legalEntityCode =:legalEntityCode ");
				//+ " and sch.legalEntityCode=t.legalEntityCode and t.auditId=sch.auditId and t.userId=:userId"
				// + " and  IFNULL(auditStatus,'NotNull') not in ('AD','AU','DRP','UP') "
				// Condition added to get the Assessments, For processing check with AuitTeam table till AC audit status , for View check with User table from AC audit status
				if(roleId != null && (roleId.equalsIgnoreCase("G2") || roleId.equalsIgnoreCase("G8"))) {
					queryString.append(" and   (sch.auditId IN (SELECT t.auditId FROM AuditTeam t WHERE t.auditId=sch.auditId and t.legalEntityCode =sch.legalEntityCode and t.userId=:userId  ) "
					+ "           AND IFNULL(auditStatus,'NotNull') IN ('AI','AS','AR','AW','AC') )" );   // 'AW','AC' included to show the Inspections after Closure as well to the Inspectors
				}/*  Reviewer condition removed to get all the Inspections for all the Reviewer
				else if(roleId != null && (roleId.equalsIgnoreCase("G12"))) {
					queryString.append(" and  ( sch.ratingParameterReviewerAssigned=:userId " );   //  Review team assigned for Rating
				}	*/	
				//+ "        OR ( (sch.unitId IN  (SELECT u.unitCode FROM User u WHERE u.unitCode=sch.unitId and  u.legalEntityCode =sch.legalEntityCode and u.userId=:userId) ) "
				//+ "			  AND  IFNULL(auditStatus,'NotNull') IN ('AW','AC')  )   " );
				else if(roleId != null && (roleId.equalsIgnoreCase("G1") || roleId.equalsIgnoreCase("G6"))) {
					queryString.append(" AND (sch.unitId IN (:maintEntities) AND  IFNULL(auditStatus,'NotNull') IN ('AW','AC')) ");
				}else if(roleId != null && (roleId.equalsIgnoreCase("G12") )) {
					queryString.append(" AND (sch.unitId IN (:maintEntities) OR sch.reportReviewer =:userId ) ");
				}/*else if(roleId != null && !(roleId.equalsIgnoreCase("G2") || roleId.equalsIgnoreCase("G8"))){
					queryString.append(" OR ( (sch.unitId IN  (SELECT u.unitCode FROM User u WHERE u.unitCode=sch.unitId and  u.legalEntityCode =sch.legalEntityCode and u.userId=:userId) ) "
							+ " AND  IFNULL(auditStatus,'NotNull') IN ('AW','AC')  ) ");
				}*/
				if(userLevel != null && (userLevel.equalsIgnoreCase("RO"))) { // RO user can see the records from Pending Auth onwards
					queryString.append(" AND sch.ratingParameterStatus NOT IN ('RPDF','RPU') "); 
				}else if(userLevel != null && (userLevel.equalsIgnoreCase("HO"))) { // HO user can see the records from Rating Reviewed onwards
					queryString.append(" AND sch.ratingParameterStatus IN ('RPR','RPHU','RPS') "); // Rating Reviewed, Rating HO Updated, Rating Submitted
				}
				//queryString.append(" ) ");
		
				// Condition end here
				/*if(!isDeptAsRMD) {
					queryString.append(" and  sch.auditTypeCode IN (SELECT asgm.auditTypeCode FROM MaintEntityAuditSubgroupMapping as asgm WHERE asgm.legalEntityCode=sch.legalEntityCode and asgm.mappingType='U' and asgm.id =:userId )"); // To show the Assessments to which the Assessor Mapped (Department mapping)
				}*/
				//if(visibleAuditList !=null && visibleAuditList.s)
				//+ " and (sch.auditId  IN :visibleAuditList  OR auditStatus in ('AI','AS','AR','AC','AU','UP','AD','AW')) " //commented to remove Visibility cond, Condition included to get the Audits as per the Visibility logic maintained in general parameter.
				
				// Financial Year and Audit Type filter added for filtering
				if(!BankAuditUtil.isEmptyString(auditTypeCode)) {
					queryString.append(" AND  sch.auditTypeCode =:auditTypeCode "); 
				}if(financialYear !=null) {
					LocalDate fYStartDt = financialYearStrtEndList.get(0);
					LocalDate fYearEndDt = financialYearStrtEndList.get(1);
					logger.info("fYStartDt :: "+fYStartDt+" fYEndDt :: "+fYearEndDt);
					queryString.append(" AND (sch.scheduleStartDate between '"+fYStartDt+"' AND '"+fYearEndDt+"' )");
					//queryString.append(" And (sch.scheduleStartDate between :fYStartDt and :fYearEndDt )");
				}
				
				logger.info("Before the qry....");
		
		if(!BankAuditUtil.isEmptyString(search)){
			queryString=queryString.append(" and ( "
					+ " sch.auditTypeCode IN (SELECT a.auditTypeCode FROM MaintAuditTypeDesc a  WHERE a.legalEntityCode=sch.legalEntityCode AND (a.auditTypeDesc LIKE :search OR a.auditTypeCode LIKE :search)) "
					+ " or planId like :search "
					+ " or auditId like :search "
					//+ " or unitId like :search "
					+ " or sch.unitId IN (SELECT m.unitCode FROM MaintEntity m WHERE m.legalEntityCode=sch.legalEntityCode AND (m.unitName LIKE :search or m.unitCode LIKE :search )) "
					+ " or sch.unitId IN (SELECT m.unitCode FROM MaintEntity m,GeneralParameter g WHERE sch.unitId=m.unitCode and m.legalEntityCode=sch.legalEntityCode AND m.legalEntityCode =g.legalEntityCode and g.key1='UNIT_TYPE' and g.key2='UNIT_TYPE' and g.value=m.unitType and g.description LIKE :search) "
					+ " or sch.unitId IN (SELECT m.unitCode FROM MaintEntity m,MaintCriticality c WHERE sch.unitId=m.unitCode and m.legalEntityCode=sch.legalEntityCode AND m.legalEntityCode =c.legalEntityCode and m.uCriticality=c.criticalityCode and c.criticalityOfType='Entity' and  c.criticalityDesc LIKE :search) "
					+ " or sch.unitId IN (SELECT m.unitCode FROM MaintEntity m,MaintAuditParameter p WHERE sch.unitId=m.unitCode and m.legalEntityCode=sch.legalEntityCode AND m.legalEntityCode =p.legalEntityCode and m.uCriticality=p.uCriticality and sch.auditTypeCode=p.auditTypeCode and  p.duration LIKE :search) "
					+ " or sch.auditId IN (SELECT t.auditId FROM User u,AuditTeam t WHERE t.auditId=sch.auditId and t.legalEntityCode =sch.legalEntityCode and t.legalEntityCode =u.legalEntityCode and t.userId=u.userId and (t.userId Like :search or u.firstName LIKE :search or u.lastName Like :search) ) "
					+ " or DATE_FORMAT(sch.scheduleStartDate, '%d %b %Y') LIKE :search "
					+ " or DATE_FORMAT(sch.scheduleEndDate, '%d %b %Y') LIKE :search "
					+ " or DATE_FORMAT(sch.actualStartDate, '%d %b %Y') LIKE :search "
					+ " or DATE_FORMAT(sch.actualEndDate, '%d %b %Y') LIKE :search "
					+ " or DATE_FORMAT(sch.lastAuditDate, '%d %b %Y') LIKE :search "
					+ " or sch.maker IN (SELECT u.userId FROM User u WHERE u.legalEntityCode =sch.legalEntityCode and (u.userId Like :search or u.firstName LIKE :search or u.lastName Like :search) ) "
					+ " or sch.ratingParameterReviewerAssigned IN (SELECT u.userId FROM User u WHERE u.legalEntityCode =sch.legalEntityCode and (u.userId Like :search or u.firstName LIKE :search or u.lastName Like :search) ) "
					+ " or sch.ratingParameterHoReviewer IN (SELECT u.userId FROM User u WHERE u.legalEntityCode =sch.legalEntityCode and (u.userId Like :search or u.firstName LIKE :search or u.lastName Like :search) ) "
					+ " or sch.auditStatus IN (SELECT g.value FROM GeneralParameter g WHERE sch.legalEntityCode =g.legalEntityCode and g.key1='STATUS' and g.key2='AUDIT_STATUS' and g.value=sch.auditStatus and g.description LIKE :search) "
					+ " or sch.reportStatus IN (SELECT g.value FROM GeneralParameter g WHERE sch.legalEntityCode =g.legalEntityCode and g.key1='STATUS' and g.key2='REPORT_STATUS' and g.value=sch.reportStatus and g.description LIKE :search) "
					// + " or teamMember like :search "
					+ " or sch.auditId IN (select t.auditId from AuditTeam t, User u where t.legalEntityCode=sch.legalEntityCode AND t.auditId=sch.auditId AND t.userType='M' AND t.legalEntityCode=u.legalEntityCode AND t.userId=u.userId "
					+ "						AND sch.unitId=u.unitCode AND (u.firstName like :search OR u.middleName like :search OR u.lastName like :search OR u.userId like :search )) "
					
					//+ " ) "
					
						+ " or sch.auditStatus IN (SELECT g.value FROM GeneralParameter g WHERE sch.legalEntityCode =g.legalEntityCode and g.key1='STATUS' "
						+ " and g.key2='AUDIT_STATUS' and g.value=sch.auditStatus "
						+ " and (g.description LIKE :search or g.value LIKE :search)"
						+ " and   ( sch.observationStatus !='AE'  ) "
						+ " ) "
						
						+ " or sch.auditStatus IN (SELECT g.value FROM GeneralParameter g WHERE sch.legalEntityCode =g.legalEntityCode and g.key1='STATUS' "
						+ " and g.key2='AUDIT_STATUS' and g.value=sch.auditStatus "
						+ " and (g.description LIKE :search or g.value LIKE :search)"
						+ " and   ( sch.observationStatus  ='AE' and sch.auditStatus='AW'  ) "
						+ " ) " 
						
						+ "  " );
						
						if((search.length()==2 && search.toUpperCase().startsWith("EX") )
						|| (search.length()==3 && search.toUpperCase().startsWith("EXI"))
						|| (search.length()==4 && search.toUpperCase().startsWith("EXIT"))
						|| ((search.length()==5 || search.length()==6) && search.toUpperCase().startsWith("EXITE")) ){
							queryString.append("Or (sch.auditStatus='AI' and sch.observationStatus='AE' ) )");
						}	
						else{
							queryString.append(" )" );
							if(search.toUpperCase().startsWith("INI")){
								queryString.append("and  ( sch.auditStatus='AI' and (sch.observationStatus is null or sch.observationStatus='' ) )");
							}
						}
					/*queryString.append(" )" );*/
		}
		
		/*queryString=queryString.append("  ORDER BY FIELD(auditStatus, 'AI','P','AP','AS','AC','AU') asc ");*/
		String[] columns={
				"auditTypeCode",
				"(auditId *1 ), (planId *1 )", //"CAST(planId AS Integer),CAST(auditId AS Integer) "
				"unitType",
				"(unitId * 1) ",  //"unitId",
				"uCriticality",
				"duration",
				"lastAuditDate",//last audit date actually 
				"sch.scheduleStartDate",
				"sch.scheduleEndDate",
				"sch.actualStartDate",
				"sch.actualEndDate",   // 10
				"teamLead", // Team Lead
				// "sch.maker", // Team Member
				"sch.maker",
				"sch.reportStatus",
				"sch.auditStatus",
				"unitName",     // 15
				"financialYear",
				"ratingParameterReviewerAssigned", 
				"ratingParameterHoReviewer"  // 18
		};

		/*if(orderColumn!=null
				&& !BankAuditUtil.isEmptyString(orderDirection)){
			queryString=queryString.append(" order by ").append(columns[orderColumn]).append(" ").append(orderDirection);
		}*/
		logger.info(" orderColumn **.... "+ orderColumn +"  orderDirection ...."+ orderDirection );
		if(orderColumn != null && orderColumn == -1 && !BankAuditUtil.isEmptyString(orderDirection)){ // -1 send for the first time from the UI during landing page.
			if(userLevel != null && (userLevel.equalsIgnoreCase("RO"))) { // RO user 
				queryString.append(" ORDER BY FIELD(ratingParameterReviewerAssigned, '"+userId+"') desc, FIELD(ratingParameterStatus,'RPP','RPRU') DESC,FIELD(auditStatus, 'AI','AP','AS','AR','AC') asc "); 
			}else if(userLevel != null && (userLevel.equalsIgnoreCase("HO"))) { 
				queryString.append(" ORDER BY FIELD(ratingParameterStatus,'RPR','RPHU') desc ,FIELD(auditStatus, 'AI','AP','AS','AR','AC') asc ");
			}else
				queryString=queryString.append(" ORDER BY FIELD(auditStatus, 'AI','AP','AS','AR','AC') asc  "); // The code modified to get the records in the order of Status on landing page, the sequence is  Initiated, Planned, Submitted, Respond, Reviewed, Closed
		}else if(orderColumn!=null && !BankAuditUtil.isEmptyString(orderDirection)){
			if(orderColumn==0) {
				queryString=queryString.append(" ORDER BY  ").append(columns[orderColumn]).append(" ").append(orderDirection).append(" , FIELD(auditStatus, 'AI','AP','AS','AR','AC') asc ,(auditId *1 ) asc  ");
			}else {
				queryString=queryString.append(" ORDER BY  ").append(columns[orderColumn]).append(" ").append(orderDirection);
			}
		}else {
			queryString=queryString.append(" ORDER BY FIELD(auditStatus, 'AI','AP','AS','AR','AC') asc  ");
		}
		
		logger.info("queryString .... "+ queryString.toString());
		
		Query query=(Query) session.createQuery(queryString.toString())				
				.setResultTransformer(new ResultTransformer() {
					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						AuditSchedule auditSchedule=null;
						//System.out.println("tuple[0] ...."+tuple[0]);
						if(tuple[0]!=null){
							auditSchedule=(AuditSchedule)tuple[0];
							/* session.evict(maintEntity);*/
							
							if(tuple[1]!=null){
								auditSchedule.setLegalEntityCode(Integer.parseInt(tuple[1].toString()));								
							}
							if(tuple[2]!=null){
								auditSchedule.setAuditTypeCode(tuple[2].toString());
							}
							
							if(tuple[3]!=null){
								auditSchedule.setPlanId(tuple[3].toString());
							}
							
							if(tuple[4]!=null){
								auditSchedule.setAuditId(tuple[4].toString());
							}
							if(tuple[5]!=null){
								//String unitCodeName=tuple[5].toString();
								//auditSchedule.setUnitId(unitCodeName);
								//auditSchedule.setUnitName(unitCodeName.substring(unitCodeName.indexOf('-')+1));
								auditSchedule.setUnitId(auditSchedule.getUnitId()+"-"+tuple[5].toString());
								auditSchedule.setUnitName(tuple[5].toString());
							}
							if(tuple[6]!=null){
								auditSchedule.setUnitType(tuple[6].toString());
							}
							if(tuple[7]!=null){
								auditSchedule.setuCriticality(tuple[7].toString());
							}
							if(tuple[8]!=null){ 
									String teamLead = userDtls.get(tuple[8].toString());
									auditSchedule.setTeamLead(teamLead.equalsIgnoreCase("Me")?"Me":tuple[8].toString()+"-"+teamLead);
								 
								//auditSchedule.setTeamLead(tuple[8].toString()+"-"+userDtls.get(tuple[8].toString()));
								//auditSchedule.setTeamLead(userDtls.get(tuple[8].toString()));
							}
							if(tuple[9]!=null){
								auditSchedule.setDuration(tuple[9].toString());
							}
							if(tuple[10]!=null){
								auditSchedule.setFrequency(tuple[10].toString());
							}
							if(tuple[11]!=null){
								if("P".equalsIgnoreCase(tuple[11].toString()))auditSchedule.setAuditStatus("AP"); // temp purpose as in few earlier records the Planned status is reflecting as "P"
								else if("AE".equalsIgnoreCase(auditSchedule.getObservationStatus())) auditSchedule.setAuditStatus(tuple[18] !=null ?tuple[18].toString() :tuple[11].toString() );
								else auditSchedule.setAuditStatus(tuple[11].toString());
							}
							if(tuple[12]!=null){
								auditSchedule.setMaker(tuple[12].toString());
							}
							if(tuple[13]!=null && auditSchedule.getRespondedFindings() != null){
								auditSchedule.setResponseReject(Integer.parseInt(tuple[13].toString()));
								auditSchedule.setRespondedFindings(auditSchedule.getRespondedFindings()- (Integer.parseInt(tuple[13].toString()))); // Included to find out the Responded Observations
							}else auditSchedule.setResponseReject(0);
							
							if(tuple[14]!=null){
								auditSchedule.setComplianceCount(Integer.parseInt(tuple[14].toString()));
							}else auditSchedule.setComplianceCount(0);
							
							if(tuple[15]!=null){
								auditSchedule.setNotApplicableCount(Integer.parseInt(tuple[15].toString()));
							}else auditSchedule.setNotApplicableCount(0);
							
							if(tuple[16]!=null){
								auditSchedule.setEarlyStartAllowed(tuple[16].toString());
							}else auditSchedule.setEarlyStartAllowed("Y");
							
							if(tuple[17]!=null){
								auditSchedule.setAuditOffsetDays(Integer.parseInt(tuple[17].toString()));
							}else auditSchedule.setAuditOffsetDays(0);
							
							if(tuple[19]!=null){
								String teamMembers="";
								String[] members = tuple[19].toString().split(",");
								for (String member: members) {
									teamMembers+=member+"-"+userDtls.get(member)+",";
								}
								auditSchedule.setTeamMember(teamMembers.substring(0,teamMembers.length()-1));
							}
							
							if(tuple[20]!=null) auditSchedule.setReportStatus(tuple[20].toString());
							if(tuple[21]!=null) auditSchedule.setReportStatusDesc(tuple[21].toString());
							
							if(tuple[22]!=null) auditSchedule.setStatus(tuple[22].toString());
							
							if(tuple[23]!=null) auditSchedule.setParentUnitCodeName(tuple[23].toString());
							if(tuple[24]!=null) auditSchedule.setUnitName(tuple[24].toString());
							if(tuple[25]!=null) auditSchedule.setRatingParameterReviewerAssignedName(userDtls.get(tuple[25].toString()));							
							if(tuple[26]!=null) auditSchedule.setRatingParameterHoReviewerName(userDtls.get(tuple[26].toString()));
							
							/*int tmSize =auditSchedule.getAuditTeams().size()-1;
							String teamMember="";
							System.out.println("Audit Id .... "+ auditSchedule.getAuditId()+" tmSize .."+tmSize);
							for( AuditTeam adtTm: auditSchedule.getAuditTeams() ) {
								
								adtTm.setUserId(adtTm.getUserId()+"-"+userDtls.get(adtTm.getUserId()));
								
								if(!"L".equalsIgnoreCase(adtTm.getUserType())) teamMember +=adtTm.getUserId() + ",";
								System.out.println("teamMember .... "+ teamMember);
							}if(tmSize >0 ) auditSchedule.setTeamMember(teamMember.substring(0,teamMember.lastIndexOf(",")));*/
							//System.out.println("Team member .... "+auditSchedule.getTeamMember());
							//System.out.println("Team size ...."+auditSchedule.getAuditTeams().size());
							
						}
						return auditSchedule;
					}
			@Override
			public List transformList(List collection) {
				return collection;
			}
		})		
				.setParameter("legalEntityCode",legalEntityCode); //.setParameter("userId",userId); //.setParameter("visibleAuditList", visibleAuditList);
		if(roleId != null && (roleId.equalsIgnoreCase("G2") || roleId.equalsIgnoreCase("G8"))) 
			query.setParameter("userId",userId);
		else if(roleId != null && (roleId.equalsIgnoreCase("G1") || roleId.equalsIgnoreCase("G6"))) 
			query.setParameter("maintEntities", maintEntities);
		if(!BankAuditUtil.isEmptyString(search)){
			query.setParameter("search", "%"+search+"%");
		}
		if(!BankAuditUtil.isEmptyString(auditTypeCode)) {
			query.setParameter("auditTypeCode", auditTypeCode);
		}
		
		logger.info("page :: "+page +"size :: "+ size);
		List<AuditSchedule> tmpAuditSchedule =query.list(); 
		Long totalRecords=null;
		try {
			totalRecords = (long)tmpAuditSchedule.size();
		}catch(Exception e) {
			totalRecords = (long)0;
		}
		List<AuditSchedule> auditSchedule=query.setFirstResult((page*size)).setMaxResults(size).list();
		
		System.out.println("Bfr size tmpAuditSchedule is .... "+tmpAuditSchedule.size()+" totalRecords:: "+ totalRecords);
		
		/*int i=0;
		for(AuditSchedule adSch:auditSchedule){
			System.out.println("Inside adSch ...");
			if("A2".equalsIgnoreCase(adSch.getAuditId()));
			else 	tmpAuditSchedule.add(adSch);
			System.out.println("after the call");
			
		}
		System.out.println("size tmpAuditSchedule .... "+tmpAuditSchedule);*/
		
		
		dataTableResponse.setData(auditSchedule);
		dataTableResponse.setRecordsTotal(totalRecords);
		dataTableResponse.setRecordsFiltered(totalRecords);
		
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		return dataTableResponse;
	
	}

		
	@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 IfcRatingData 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;
	}
	
	@Override
	public boolean verifyDataPresentForParamScore(int legalEntityCode, String auditId) {
		Session session = getSession();		
		boolean isParamDataPresent=false;
		logger.info("Inside verifyDataPresentForParamScore .. "+ legalEntityCode +" auditId .. "+auditId);
		try{
			List<AuditRatingParameterScore> auditPScore= session.createQuery(" FROM AuditRatingParameterScore aps WHERE auditId=:auditId AND legalEntityCode=:legalEntityCode")
					.setParameter("auditId", auditId).setParameter("legalEntityCode", legalEntityCode).list();		
			if(!auditPScore.isEmpty()) isParamDataPresent=true;
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}return isParamDataPresent;
	}
	
	
	// Inserting data into "audit_rating_parameter_score", without uploading the CSV file. (Through Rating->Edit for the first time)
	@Override
	public boolean createAndGetAuditRatingParamScore(int legalEntityCode,String auditId, String unitId, String auditTypeCode, String userId) {
		Session session = getSession();
		logger.info("Inside createAuditRatingModelOverlays:: "+legalEntityCode+" :: "+auditId+ " :: "+unitId+" :: "+auditTypeCode+" :: "+userId);
		boolean created=false;
		try {
			StringBuilder qryParamr=new StringBuilder(" "
					+" SELECT am.rm_code, m.param_code,m.param_mesaurement_type,m.param_basis,m.param_weightage, p.category_code, p.sub_category_code, " //6
					+"  m.min_score, m.max_score, am.audit_type_code " // 9
					+"   FROM maint_rating_parameter_dtls m , maint_rating_parameter_mapping p , maint_rating_audit_type_mapping am "
					+" WHERE m.param_code=p.param_code AND m.rm_code=p.rm_code AND m.rm_code=am.rm_code AND p.rm_code=am.rm_code "
					+" AND m.legal_entity_code =p.legal_entity_code AND m.legal_entity_code=am.legal_entity_code "
					+" AND m.legal_entity_code=:legalEntityCode AND am.audit_type_code=:auditTypeCode ");
			
			List qrylst=session.createNativeQuery(qryParamr.toString())
					.setParameter("auditTypeCode", auditTypeCode).setParameter("legalEntityCode", legalEntityCode).list();			
			
			int i=0;
			for ( Iterator iter = qrylst.iterator(); iter.hasNext(); ) {
				Object[] obj = (Object[]) iter.next(); i++;				
				AuditRatingParameterScore aps= new AuditRatingParameterScore();
				aps.setLegalEntityCode(legalEntityCode);
				aps.setAuditId(auditId);
				aps.setUnitId(unitId);
				aps.setAuditTypeCode(auditTypeCode);
				aps.setMaker(userId);
				aps.setMakerTimestamp(new Timestamp(System.currentTimeMillis()));
				if(obj[0] !=null) aps.setRmCode(obj[0].toString());
				if(obj[1] !=null) aps.setParamCode(obj[1].toString());
				if(obj[2] !=null) aps.setParamMesaurementType(obj[2].toString());
				if(obj[3] !=null) aps.setParamBasis(obj[3].toString());
				if(obj[4] !=null) aps.setParamWeightage(obj[4].toString());
				if(obj[5] !=null) aps.setCategoryCode(obj[5].toString());
				if(obj[6] !=null) aps.setSubCategoryCode(obj[6].toString());
				if(obj[7] !=null) aps.setMinScore(obj[7].toString());
				if(obj[8] !=null) aps.setMaxScore(obj[8].toString());
				aps.setEntityStatus("A");
				aps.setStatus("U");					
				auditRatingParameterScoreDao.flushSession();
				auditRatingParameterScoreDao.saveOrUpdate(aps);				
			}logger.info(" Parameters Inserted .. "+i +" times");	
			created=true;
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}return created;
	}	
	
	// Inserting data into "audit_rating_parameter_score", when CSV uploaded (Inserting both Quantitative and Qualitative records)
	@Override
	public void createAuditParameterScore(AuditRatingParameterScoreDto scoreDto) {
		Session session = getSession();
		logger.info("Inside createAuditParameterScore .."+scoreDto.getLegalEntityCode()+" :: "+scoreDto.getAuditId()+" :: "+scoreDto.getRmCode()+" :: "+ scoreDto.getParamCode()+
				" Score=> "+scoreDto.getProcessedScore() +" :: "+scoreDto.getScore());
		try {
			
			AuditRatingParameterScore aps= new AuditRatingParameterScore();
			aps.setLegalEntityCode(scoreDto.getLegalEntityCode());
			aps.setAuditId(scoreDto.getAuditId());
			aps.setUnitId(scoreDto.getUnitId());
			aps.setAuditTypeCode(scoreDto.getAuditTypeCode());
			aps.setRmCode(scoreDto.getRmCode());
			aps.setParamCode(scoreDto.getParamCode());
			aps.setParamMesaurementType(scoreDto.getParamMesaurementType());
			aps.setParamBasis(scoreDto.getParamBasis());
			aps.setParamWeightage(scoreDto.getParamWeightage());
			aps.setCategoryCode(scoreDto.getCategoryCode());
			aps.setSubCategoryCode(scoreDto.getSubCategoryCode());
			aps.setProcessedScore(scoreDto.getProcessedScore());
			aps.setScoreEffective(scoreDto.getScore());
			aps.setScoreAssessed(scoreDto.getScore());
			aps.setMinScore(scoreDto.getMinScore());
			aps.setMaxScore(scoreDto.getMaxScore());
			aps.setMaker(scoreDto.getMaker());
			aps.setMakerTimestamp(new Timestamp(System.currentTimeMillis()));
			aps.setEntityStatus("A");
			aps.setStatus("U");					
			auditRatingParameterScoreDao.flushSession();
			auditRatingParameterScoreDao.saveOrUpdate(aps);	
			logger.info("Record is getting Saved/Updated");
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
	}	
	
	// Updating "audit_rating_parameter_score", when CSV uploaded subsequently (basically updating the Quantitative with basistype->CALC records)
	@Override
	public void updateAuditParameterScore(AuditRatingParameterScoreDto scoreDto){
		logger.info("Inside updateAuditParameterScore .."+scoreDto.getLegalEntityCode()+" :: "+scoreDto.getAuditId()+" :: "+scoreDto.getRmCode()+" :: "+ scoreDto.getParamCode()+
				" Score=> "+scoreDto.getProcessedScore() +" :: "+scoreDto.getScore());
		try{			
			Session session =getSession();  
			StringBuilder sqlQuery = new StringBuilder(" UPDATE AuditRatingParameterScore aps "
				+"		SET processedScore =:processedScore, scoreAssessed=:concreteScore, scoreEffective=:concreteScore "
				+"		WHERE aps.legalEntityCode= :legalEntityCode AND aps.auditId = :auditId	"
				+"		and aps.rmCode= :rmCode AND aps.paramCode=:paramCode ");
		
			int status = session.createQuery(sqlQuery.toString())
					.setParameter("processedScore", scoreDto.getProcessedScore()).setParameter("concreteScore", scoreDto.getScore())
 					.setParameter("legalEntityCode", scoreDto.getLegalEntityCode()).setParameter("auditId", scoreDto.getAuditId())
			 		.setParameter("rmCode", scoreDto.getRmCode()).setParameter("paramCode", scoreDto.getParamCode())
			 		.executeUpdate();		
		}catch (Exception e) { 
			logger.error("Error .." + e.getMessage()+" :: "+e.getCause());
		}
	} 	
	
	@Override
	public List<AuditRatingParameterScoreDto> getRatingParamDataFromMaint(Integer legalEntityCode, String auditId, String unitId, String auditTypeCode, String userId) {
		Session session = getSession();
		logger.info("Inside getRatingParamDataFromMaint "+legalEntityCode+" :: "+auditId+ " :: "+unitId+" :: "+auditTypeCode+" :: "+userId);
		List<AuditRatingParameterScoreDto> paramDtoLst=null;
		try {
			StringBuilder qryParamr=new StringBuilder(" "
					+" SELECT am.rm_code, m.param_code,m.param_mesaurement_type,m.param_basis,m.param_calc_type ,m.param_weightage, p.category_code,p.sub_category_code, "// 7
					+"    ip.input_01, ip.input_02, ip.input_03, ip.input_04, ip.input_05, ip.input_06, ip.formula , " // 14
					+"    m.min_score, m.max_score, am.audit_type_code  " // 17
					+"  FROM maint_rating_parameter_mapping p , maint_rating_audit_type_mapping am, "
					+"    maint_rating_parameter_dtls m LEFT JOIN maint_rating_parameter_inputs ip "
					+"		ON m.legal_entity_code=ip.legal_entity_code AND m.rm_code=ip.rm_code AND m.param_code=ip.param_code "
					+"  WHERE m.param_code=p.param_code AND m.rm_code=p.rm_code AND m.rm_code=am.rm_code AND p.rm_code=am.rm_code "
					+"    AND m.legal_entity_code =p.legal_entity_code AND m.legal_entity_code=am.legal_entity_code  "
					+"    AND m.legal_entity_code=:legalEntityCode AND am.audit_type_code=:auditTypeCode ");
					// +" AND m.param_code in ('P0001','P0002','P0003','P0106','P0107') ");
			
			List qrylst=session.createNativeQuery(qryParamr.toString())
					.setParameter("auditTypeCode", auditTypeCode).setParameter("legalEntityCode", legalEntityCode).list();			
			
			paramDtoLst =new ArrayList<AuditRatingParameterScoreDto>();
			for ( Iterator iter = qrylst.iterator(); iter.hasNext(); ) {
				Object[] obj = (Object[]) iter.next();			
				AuditRatingParameterScoreDto apsd= new AuditRatingParameterScoreDto();
				apsd.setLegalEntityCode(legalEntityCode);
				apsd.setAuditId(auditId);
				apsd.setUnitId(unitId);
				apsd.setAuditTypeCode(auditTypeCode);
				apsd.setMaker(userId);
				if(obj[0] !=null) apsd.setRmCode(obj[0].toString());
				if(obj[1] !=null) apsd.setParamCode(obj[1].toString());
				if(obj[2] !=null) apsd.setParamMesaurementType(obj[2].toString());
				if(obj[3] !=null) apsd.setParamBasis(obj[3].toString());
				if(obj[4] !=null) apsd.setParamCalcType(obj[4].toString());
				if(obj[5] !=null) apsd.setParamWeightage(obj[5].toString());
				if(obj[6] !=null) apsd.setCategoryCode(obj[6].toString());
				if(obj[7] !=null) apsd.setSubCategoryCode(obj[7].toString());
				if(obj[8] !=null) apsd.setInput01(obj[8].toString());
				if(obj[9] !=null) apsd.setInput02(obj[9].toString());
				if(obj[10] !=null) apsd.setInput03(obj[10].toString());
				if(obj[11] !=null) apsd.setInput04(obj[11].toString());
				if(obj[12] !=null) apsd.setInput05(obj[12].toString());
				if(obj[13] !=null) apsd.setInput06(obj[13].toString());
				if(obj[14] !=null) apsd.setFormula(obj[14].toString());
				if(obj[15] !=null) apsd.setMinScore(obj[15].toString());
				if(obj[16] !=null) apsd.setMaxScore(obj[16].toString());
				
				paramDtoLst.add(apsd);							
			}logger.info(" Parameters Inserted .. "+paramDtoLst);	
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}return paramDtoLst;
	}	
	
	@Override
	public List<AuditRatingParameterScore> getParametersByAuditId(int legalEntityCode, String auditId, String auditTypeCode, String userType) {
		Session session = getSession();
		session.setDefaultReadOnly(true);
		
		List<AuditRatingParameterScore> auditRatingScoreLst = null;
		logger.info("Inside getParametersByAuditId .. "+ legalEntityCode +" auditId .. "+auditId +" auditTypeCode .."+auditTypeCode+" userType .."+userType);
		try {
			//StringBuilder queryString=new StringBuilder(" from AuditSchedule where legalEntityCode =:legalEntityCode ");
			StringBuilder queryString=new StringBuilder("SELECT arps as auditRatingParameterScore, "   	// 0
					+ " arps.legalEntityCode, arps.auditId, mpd.paramName, mpr.scoreReference, "							// 4
					//+ " (select concat(auditTypeCode , '-' , auditTypeDesc) from MaintAuditTypeDesc at where at.auditTypeCode=arps.auditTypeCode and at.legalEntityCode= arps.legalEntityCode ) as auditTypeCode, " //4
					//+ " (Select concat(unitCode , '-' , unitName) from  MaintEntity e where e.unitCode =arps.unitId and e.legalEntityCode =arps.legalEntityCode ) as unitId,  "  // 
					+ " (Select categoryName from  MaintRatingCategory c where c.categoryCode =arps.categoryCode and c.legalEntityCode =arps.legalEntityCode ) as categoryName,  "  // 5
					+ " (Select subCategoryName from  MaintRatingSubCategory c where c.categoryCode =arps.categoryCode and c.subCategoryCode =arps.subCategoryCode and c.legalEntityCode =arps.legalEntityCode ) as subCategoryName,  "  // 6
					+ " ROUND(arps.scoreAssessed * arps.paramWeightage/100.00 ,2) as weighted_score, " // 7
					+ " (SELECT description FROM GeneralParameter g WHERE g.key1 = 'RATING_MODEL' AND g.key2='MEASUREMENT' AND g.value=arps.paramMesaurementType AND g.legalEntityCode=arps.legalEntityCode) as measurement_name, "  // 8
					+ " (SELECT r.inputValue FROM MaintRatingParameterInputs i, IfcRatingData r WHERE i.input01=r.inputExt AND r.auditId=arps.auditId AND arps.rmCode=i.rmCode AND arps.paramCode=i.paramCode ) as csv_inputs " // 9 -- getting the first input of parameter from CSV file
					//+ " , arps.weightedScoreEffective, arps.weightedScoreHo "  // 11 
					+ " from AuditRatingParameterScore arps, MaintRatingParameterDtls mpd  "
					+ "  LEFT JOIN MaintRatingParameterScoreReference mpr ON mpr.rmCode=mpd.rmCode AND mpr.paramCode=mpd.paramCode "
					+ " where arps.legalEntityCode =mpd.legalEntityCode and arps.paramCode=mpd.paramCode and "
					+ " arps.legalEntityCode =:legalEntityCode and arps.auditId=:auditId "
					+ " ORDER BY arps.categoryCode, arps.paramMesaurementType desc, arps.paramCode"); 
			logger.info("Before the qry....");
			
			Query query=(Query) session.createQuery(queryString.toString())				
					.setResultTransformer(new ResultTransformer() {
						@Override
						public Object transformTuple(Object[] tuple, String[] aliases) {
							AuditRatingParameterScore auditRatingScore=null;
							if(tuple[0]!=null){
								auditRatingScore=(AuditRatingParameterScore)tuple[0];
								if(tuple[1]!=null) auditRatingScore.setLegalEntityCode(Integer.parseInt(tuple[1].toString()));		
								if(tuple[2]!=null) auditRatingScore.setAuditId(tuple[2].toString());
								if(tuple[3]!=null) auditRatingScore.setParamName(tuple[3].toString());
								if(tuple[4]!=null) auditRatingScore.setScoreReference(tuple[4].toString());
								//if(tuple[4]!=null) auditRatingScore.setAuditTypeName(tuple[4].toString());
								if(tuple[5]!=null) auditRatingScore.setCategoryName(tuple[5].toString());
								if(tuple[6]!=null) auditRatingScore.setSubCategoryName(tuple[6].toString());
								if(tuple[7]!=null) auditRatingScore.setWeightedScore(tuple[7].toString());
								if(tuple[8]!=null) auditRatingScore.setParamMesaurementName(tuple[8].toString());
								if(tuple[9]!=null) auditRatingScore.setInputValue(tuple[9].toString());
								
								if(auditRatingScore.getWeightedScoreEffective()!=null) auditRatingScore.setWeightedScore(auditRatingScore.getWeightedScoreEffective().toString());
								if("HO".equalsIgnoreCase(userType) && auditRatingScore.getWeightedScoreHO() !=null) {
									auditRatingScore.setWeightedScore(auditRatingScore.getWeightedScoreHO().toString());
								}
								if(auditRatingScore.getMinScore()==null)auditRatingScore.setMinScore("1");
								if(auditRatingScore.getMaxScore()==null)auditRatingScore.setMaxScore("5");																						 
							}
							return auditRatingScore;
						}
				@Override
				public List transformList(List collection) {
					return collection;
				}
			}).setParameter("legalEntityCode",legalEntityCode)
			  .setParameter("auditId",auditId);
			
			auditRatingScoreLst=query.list();

		}catch(Exception e) {
			e.printStackTrace();
		}return auditRatingScoreLst; 
	}

	@Override
	public void updateAuditParamScores(List<AuditRatingParameterScore> auditRatingParameterScores,int legalEntityCode, String auditId, String userType) {
		Session session = getSession();
		
		logger.info("Inside updateAuditParamScores .. "+legalEntityCode +" :: "+ auditId+ " :: " +userType);
		for(AuditRatingParameterScore auditRatingParameterScore: auditRatingParameterScores) {
			if("M".equalsIgnoreCase(auditRatingParameterScore.getStatus())) {
				if(auditRatingParameterScore.getScoreEffective()==null)
					auditRatingParameterScore.setScoreEffective(auditRatingParameterScore.getScoreAssessed());
				auditRatingParameterScore.setStatus(BankAuditConstant.STATUS_UNAUTH);
				auditRatingParameterScoreDao.flushSession();
				auditRatingParameterScoreDao.saveOrUpdate(auditRatingParameterScore);
			}
		}		
		/*************************** Call Error of Approximation logic ********************************/
		//executeAnUpdateErrorOfApproximation(legalEntityCode,  auditId,  userType);				
	}
	
	@Override
	public boolean executeAnUpdateWeightedScore(int legalEntityCode, String auditId, String userType) {
		logger.info("Inside executeAnUpdateWeightedScore .. "+ legalEntityCode +" :: "+ auditId +" :: "+ userType);
		return executeAnUpdateErrorOfApproximation(legalEntityCode,  auditId,  userType);		
	}
	
	//Applied Approximation 
	@Override
	public List<AuditRatingSummaryDto> getAuditRatingSummary(int legalEntityCode, String auditId, String rmCode, String userType) {
		logger.info("Inside getAuditRatingSummary "+ auditId+" :: "+ rmCode+" :: "+userType);
		Session session = getSession();
		session.setDefaultReadOnly(true);
		String scoreEffective="weighted_score_effective";
		if("HO".equalsIgnoreCase(userType)) scoreEffective="weighted_score_ho";
		logger.info("Score effective .."+ scoreEffective);
		
		AuditRatingSummaryDto auditSummaryDto = null;
		List<AuditRatingSummaryDto> lstSummary= null; 
		StringBuilder queryString=new StringBuilder(" SELECT c.legal_entity_code, w.rm_code, c.category_code,c.category_name, w.weightage, "  //4
			//+"  ROUND((SELECT SUM("+scoreEffective+"*param_weightage/100) FROM audit_rating_parameter_score s WHERE s.rm_code=w.rm_code AND s.category_code=w.category_code AND param_mesaurement_type='QN' AND audit_id=:auditId ),2) AS qn_score, " // 5
			//+"  ROUND((SELECT SUM("+scoreEffective+"*param_weightage/100) FROM audit_rating_parameter_score s WHERE s.rm_code=w.rm_code AND s.category_code=w.category_code AND param_mesaurement_type='QL' AND audit_id=:auditId ),2) AS ql_score, " // 6
			//+"  ROUND((SELECT SUM("+scoreEffective+"*param_weightage/100)* w.weightage / 100 FROM audit_rating_parameter_score s WHERE s.rm_code=w.rm_code AND s.category_code=w.category_code AND audit_id=:auditId ),2) AS cat_score  "  // 7
			+"  (SELECT SUM("+scoreEffective+") FROM audit_rating_parameter_score s WHERE s.rm_code=w.rm_code AND s.category_code=w.category_code AND param_mesaurement_type='QN' AND audit_id=:auditId ) AS qn_score, " // 5
			+"  (SELECT SUM("+scoreEffective+") FROM audit_rating_parameter_score s WHERE s.rm_code=w.rm_code AND s.category_code=w.category_code AND param_mesaurement_type='QL' AND audit_id=:auditId ) AS ql_score, " // 6
			+"  (SELECT SUM("+scoreEffective+") * w.weightage / 100 FROM audit_rating_parameter_score s WHERE s.rm_code=w.rm_code AND s.category_code=w.category_code AND audit_id=:auditId ) AS cat_score  "  // 7
			+" FROM  maint_rating_weightage_category w, maint_rating_category c "
			+"  WHERE w.rm_code=:rmCode AND w.legal_entity_code=:legalEntityCode AND "
			+ " w.category_code=c.category_code AND w.legal_entity_code=c.legal_entity_code AND "
			+ " IF(category_for='','SUPERVISORY',category_for)='SUPERVISORY' "  // category cond added
			+" order by c.category_code ");
				
			logger.info("Before the qry...."+ queryString.toString());
			
			Query query=session.createNativeQuery(queryString.toString())
					.setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId).setParameter("rmCode", rmCode);
			
			List qrylst = query.list();	
			try {
				lstSummary = new ArrayList<>();
				double finalTotal=0.0;
				for ( Iterator iter = qrylst.iterator(); iter.hasNext(); ) {
					Object[] obj = (Object[]) iter.next();
					double weightage=0.0, qn=0.0, ql=0.0, weightedScore=0.0;
					auditSummaryDto = new AuditRatingSummaryDto();
					auditSummaryDto.setLegalEntityCode(legalEntityCode);
					auditSummaryDto.setAuditId(auditId);
					auditSummaryDto.setRmCode(rmCode);
					auditSummaryDto.setCategoryCode(obj[2].toString());
					auditSummaryDto.setCategoryName(obj[3].toString());
					if(obj[4] !=null ) {
						auditSummaryDto.setWeightage(obj[4].toString());
						weightage=Double.parseDouble(obj[4].toString());
					}if(obj[5] !=null ) {
						auditSummaryDto.setQuantitativeSore(obj[5].toString());
						qn=Double.parseDouble(obj[5].toString());
					}if(obj[6] !=null ) {
						auditSummaryDto.setQualitativeScore(obj[6].toString());
						ql=Double.parseDouble(obj[6].toString());
					}if(obj[7] !=null ) {
						auditSummaryDto.setTotalweightedScore(obj[7].toString());
						finalTotal+=Double.parseDouble(auditSummaryDto.getTotalweightedScore());
					}
					auditSummaryDto.setTotalScore(String.valueOf((double)Math.round((qn+ql)*100)/100.00));
					weightedScore=(double)Math.round((qn+ql)*weightage)/100.00;
					auditSummaryDto.setTotalweightedScoreDirect(String.valueOf(weightedScore));
					
					if("RM01".equalsIgnoreCase(rmCode) || "RM02".equalsIgnoreCase(rmCode)) {
						auditSummaryDto.setTotalScoreNormalized( String.valueOf(Math.round(Double.parseDouble(auditSummaryDto.getTotalScore()) * 20 * 100)/100.00 ));
						auditSummaryDto.setTotalweightedScoreNormalized(String.valueOf(Math.round(Double.parseDouble(auditSummaryDto.getTotalweightedScore()) * 20 *100)/100.00));
						auditSummaryDto.setTotalweightedScoreDirectNormalized(String.valueOf(Math.round(Double.parseDouble(auditSummaryDto.getTotalweightedScoreDirect()) * 20 *100)/100.00));
					} 			
					
					//finalTotal+=weightedScore;
					lstSummary.add(auditSummaryDto);					
				}			
				// Adding the Final score
				auditSummaryDto = new AuditRatingSummaryDto();
				auditSummaryDto.setLegalEntityCode(legalEntityCode);
				auditSummaryDto.setAuditId(auditId);
				auditSummaryDto.setRmCode(rmCode);
				auditSummaryDto.setCategoryName("Final Score");
				auditSummaryDto.setTotalweightedScore(String.valueOf(Math.round((finalTotal)*100)/100.00));
				lstSummary.add(auditSummaryDto);
				// End here
				logger.info("lstSummary .... "+lstSummary);
			}catch(Exception e) {
				e.printStackTrace(System.out);
			}
		return lstSummary;
	}
	
	/** To get the Rating summary for Parameters with no weightage and uneven max score **/ // for Existing Rating models // New param overLay added 
	@Override
	public List<AuditRatingSummaryDto> getAuditRatingSummaryNoWeightage(int legalEntityCode, String auditId, String rmCode, String userType, String overLay) {
		logger.info("Inside getAuditRatingSummaryNoWeightage "+ auditId+" RMCode:: "+ rmCode+ " OvrLy:: "+overLay);
		Session session = getSession();
		session.setDefaultReadOnly(true);
		
		String scoreEffective="score_effective";
		if("HO".equalsIgnoreCase(userType)) scoreEffective="score_effective_ho";
		logger.info("Score effective .."+ scoreEffective);
		
		AuditRatingSummaryDto auditSummaryDto = null;
		List<AuditRatingSummaryDto> lstSummary= null; 
		StringBuilder queryString=new StringBuilder(" SELECT c.legal_entity_code, w.rm_code, c.category_code,c.category_name, weightage, "  //4
			+"  ROUND((SELECT IFNULL(SUM(IFNULL("+scoreEffective+",0)),0) FROM audit_rating_parameter_score s WHERE s.rm_code=w.rm_code AND s.category_code=w.category_code AND param_mesaurement_type='QN' AND audit_id=:auditId ),2) AS qn_score, " // 5
			+"  ROUND((SELECT IFNULL(SUM(IFNULL("+scoreEffective+",0)),0) FROM audit_rating_parameter_score s WHERE s.rm_code=w.rm_code AND s.category_code=w.category_code AND param_mesaurement_type='QL' AND audit_id=:auditId ),2) AS ql_score, " // 6
			+"  ROUND((SELECT IFNULL(SUM(IFNULL(max_score,0)),0) FROM audit_rating_parameter_score s WHERE s.rm_code=w.rm_code AND s.category_code=w.category_code AND param_mesaurement_type='QN' AND audit_id=:auditId ),2) AS qn_max_score, " // 7
			+"  ROUND((SELECT IFNULL(SUM(IFNULL(max_score,0)),0) FROM audit_rating_parameter_score s WHERE s.rm_code=w.rm_code AND s.category_code=w.category_code AND param_mesaurement_type='QL' AND audit_id=:auditId ),2) AS ql_max_score " // 8
			+" FROM  maint_rating_weightage_category w, maint_rating_category c "
			+"  WHERE w.rm_code=:rmCode AND w.legal_entity_code=:legalEntityCode AND "
			+ " w.category_code=c.category_code AND w.legal_entity_code=c.legal_entity_code AND "
			+ " IF(category_for='','SUPERVISORY',category_for)='SUPERVISORY' "  // category cond added
			+" order by c.category_code ");
				
			logger.info("Before the qry...."+ queryString.toString());
			
			Query query=session.createNativeQuery(queryString.toString())
					.setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId).setParameter("rmCode", rmCode);
			
			List qrylst = query.list();	
			try {
				lstSummary = new ArrayList<>();
				// String modelAttendedFlag="Y"; // after deflator
				double finalTotal=0.0;
				for ( Iterator iter = qrylst.iterator(); iter.hasNext(); ) {
					Object[] obj = (Object[]) iter.next();
					Map dflatedMap=null;
					double weightage=0.0, qn=0.0, ql=0.0, qn_max_total=0.0, ql_max_total=0.0,qn_avg=0.0, ql_avg=0.0, qn_prc_scr=0.0, ql_prc_scr=0.0, 
							qn_max_prc=0.0, ql_max_prc=0.0, cat_avg=0.0, weightedScore=0.0,  deflaterScore=1.0;
					auditSummaryDto = new AuditRatingSummaryDto();
					auditSummaryDto.setLegalEntityCode(legalEntityCode);
					auditSummaryDto.setAuditId(auditId);
					auditSummaryDto.setRmCode(rmCode);
					auditSummaryDto.setCategoryCode(obj[2].toString());
					auditSummaryDto.setCategoryName(obj[3].toString());
					if(obj[4] !=null ) {
						auditSummaryDto.setWeightage(obj[4].toString());
						weightage=Double.parseDouble(obj[4].toString());
					}if(obj[5] !=null ) {
						//auditSummaryDto.setQuantitativeSore(obj[5].toString());
						qn=Double.parseDouble(obj[5].toString());
					}if(obj[6] !=null ) {
						//auditSummaryDto.setQualitativeScore(obj[6].toString());
						ql=Double.parseDouble(obj[6].toString());
					}/*if(obj[7] !=null ) {
						auditSummaryDto.setTotalweightedScore(obj[7].toString());
						finalTotal+=Double.parseDouble(auditSummaryDto.getTotalweightedScore());
					}*/
					if(obj[7] !=null ) qn_max_total=Double.parseDouble(obj[7].toString());
					if(obj[8] !=null ) ql_max_total=Double.parseDouble(obj[8].toString());
					
					qn_avg=(double)Math.round((qn/qn_max_total*100)*100)/100.00;
					ql_avg=(double)Math.round((ql/ql_max_total*100)*100)/100.00;
					cat_avg=(double)Math.round(( (ql+qn)/(ql_max_total+qn_max_total)*100)*100)/100.00;
					
					qn_max_prc= (double)Math.round((qn_max_total/(qn_max_total+ql_max_total)*100)*100)/100.00;
					ql_max_prc= (double)Math.round((ql_max_total/(qn_max_total+ql_max_total)*100)*100)/100.00;
					
					qn_prc_scr= (double)Math.round((qn_avg*qn_max_prc/100)*100)/100.00;
					ql_prc_scr= (double)Math.round((ql_avg*ql_max_prc/100)*100)/100.00;
					
					logger.info("Category .."+auditSummaryDto.getCategoryName()+" :: "+qn_max_total +" :: "+ql_max_total+" :: "+qn_avg+" :: "+ql_avg+" :: "+cat_avg);
					logger.info(" QN_MAX_PRC :: "+ qn_max_prc+" :: "+ql_max_prc +" QN_PRC_SCR :: "+ qn_prc_scr+ " :: "+ ql_prc_scr);
					//auditSummaryDto.setQuantitativeSore(String.valueOf(qn_avg));
					//auditSummaryDto.setQualitativeScore(String.valueOf(ql_avg));
					
					auditSummaryDto.setQuantitativeSore(String.valueOf(qn_prc_scr));
					auditSummaryDto.setQualitativeScore(String.valueOf(ql_prc_scr));
					
					//auditSummaryDto.setTotalScore(String.valueOf((double)Math.round((qn+ql)*100)/100.00));
					
					// For Widget 3, Calculation with Overlay
					if("Yes".equalsIgnoreCase(overLay)) {
						//Code after inclusion of deflator
						logger.info("Cat Name .."+obj[2].toString());
						if("CT003".equalsIgnoreCase(obj[2].toString())) { // for Management score need to call deflator and subtract the score
							logger.info("Inside Category Management ..");
							dflatedMap= getDeflatedScoreForAuditId(legalEntityCode, auditId, rmCode, userType);
							if(dflatedMap !=null && "Y".equalsIgnoreCase(dflatedMap.get("MODEL_ATTENDED").toString())) {
								deflaterScore= Double.parseDouble(dflatedMap.get("DEFLATED_SCORE").toString());
								double mgFinalScore= cat_avg * deflaterScore;
								logger.info("deflaterScore .."+deflaterScore+" cat_avg .."+cat_avg +" mgFinalScore .. "+ mgFinalScore);
								//auditSummaryDto.setTotalweightedScore(String.valueOf(Math.round(mgFinalScore*100)/100.00));
								//auditSummaryDto.setTotalweightedScore(String.valueOf(mgFinalScore));
								cat_avg=mgFinalScore;
							}/*else { auditSummaryDto.setTotalweightedScore("0.0"); // If Model is unattended, then Management Score is blank
									modelAttendedFlag="N";
							}*/
						}//end here		
					}// Overlay calc end here
					
					auditSummaryDto.setTotalScore(String.valueOf(cat_avg));
					weightedScore=(double)Math.round((cat_avg)*weightage)/100.00;
					auditSummaryDto.setTotalweightedScore(String.valueOf(weightedScore));
					auditSummaryDto.setTotalweightedScoreDirect(String.valueOf(weightedScore));
					
					finalTotal+=weightedScore;
					lstSummary.add(auditSummaryDto);					
				}			
				// Adding the Final score
				auditSummaryDto = new AuditRatingSummaryDto();
				auditSummaryDto.setLegalEntityCode(legalEntityCode);
				auditSummaryDto.setAuditId(auditId);
				auditSummaryDto.setRmCode(rmCode);
				auditSummaryDto.setCategoryCode("CTALL");
				auditSummaryDto.setCategoryName("Final Score");
				auditSummaryDto.setTotalweightedScore(String.valueOf(Math.round((finalTotal)*100)/100.00));
				lstSummary.add(auditSummaryDto);
				// End here
				logger.info("lstSummary .... "+lstSummary);
			}catch(Exception e) {
				e.printStackTrace(System.out);
			}
		return lstSummary;
	}
	
	
	
	/** To get the Final score, after applying the Model overlay and Parameter overlay**/
	@Override
	public AuditRatingScoreDto getAuditRatingScores(int legalEntityCode, String auditId, String rmCode) {
		logger.info("Inside getAuditRatingScores "+ auditId +" :: "+rmCode);
		Session session = getSession();
		session.setDefaultReadOnly(true);
		
		AuditRatingScoreDto scoreDto = null;
		/*StringBuilder queryString=new StringBuilder(" "
				+" SELECT integrated_score, best_rating AS model_overlay_grade, total_penalty, " //2
				+ "	 ROUND((integrated_score -total_penalty),2) AS score_after_penalty , scr.max_per as model_grade_max_per, " //4
				+"   (SELECT g.grade_code FROM maint_rating_grade g WHERE scr.integrated_score >= g.min_per AND scr.integrated_score < g.max_per ) AS integrate_grade, "  // 5
				+"   (SELECT g.grade_code FROM maint_rating_grade g WHERE (scr.integrated_score-total_penalty) >= g.min_per AND (scr.integrated_score-total_penalty) < g.max_per ) AS param_grade, " // 6
				+"   (SELECT ROUND( (SUM(a.score_effective * param_weightage/100) * w.weightage/100),2)  "
				+"	 	FROM  maint_rating_weightage_category w, audit_rating_parameter_score a, maint_rating_category c "
				+"   	WHERE w.rm_code=:rmCode AND w.legal_entity_code=:legalEntityCode AND a.audit_id=:auditId AND c.category_name LIKE '%MANAGEMENT%' "
				+"	 	AND c.legal_entity_code=w.legal_entity_code AND c.category_code=w.category_code AND w.legal_entity_code=a.legal_entity_code"
				+"	 	AND w.category_code=a.category_code GROUP BY w.category_code, w.weightage) AS management_score " // 7
				+"  FROM  "
				+"  (SELECT integrated_score,best_rating,total_penalty ,gr.max_per FROM "
				+"    (SELECT ROUND(SUM(integrated_score),2) AS integrated_score  FROM  "
				+" 	(SELECT ROUND( (SUM(a.score_effective *param_weightage/100) * w.weightage/100),2) AS integrated_score "
				+" 	   FROM  maint_rating_weightage_category w, audit_rating_parameter_score a "
				+" 	   WHERE w.rm_code=:rmCode AND w.legal_entity_code=:legalEntityCode AND w.category_code=a.category_code AND w.legal_entity_code=a.legal_entity_code AND a.audit_id=:auditId "
				+" 	   GROUP BY w.category_code, w.weightage)AS a1) AS s, "
				+"    (SELECT m.best_rating,m.audit_id,g.max_per, m.legal_entity_code FROM audit_rating_model_overlay m, maint_rating_grade g  "
				+" 	   WHERE m.rm_code=g.rm_code AND m.best_rating=g.grade_code AND m.applicability='Y' AND m.audit_id=:auditId ORDER BY rating_internal LIMIT 1) AS gr, "
				+"    (SELECT IFNULL(SUM(o.score_penalty),0) AS total_penalty FROM audit_rating_model_overlay o WHERE o.applicability='Y' AND o.audit_id=:auditId ) AS sp "
				+"  WHERE gr.audit_id=:auditId AND gr.legal_entity_code=:legalEntityCode) AS scr ");*/
		
			StringBuilder queryString=new StringBuilder(" "
				+" SELECT integrated_score, best_rating AS model_overlay_grade, total_penalty, " //2
				+ "	 ROUND((integrated_score -total_penalty),2) AS score_after_penalty , scr.max_per as model_grade_max_per, " //4
				+"   (SELECT g.grade_code FROM maint_rating_grade g WHERE g.rm_code=:rmCode AND scr.integrated_score *20 >= g.min_per AND scr.integrated_score * 20 < g.max_per ) AS integrate_grade, "  // 5
				+"   (SELECT g.grade_code FROM maint_rating_grade g WHERE g.rm_code=:rmCode AND (scr.integrated_score-total_penalty) >= g.min_per AND (scr.integrated_score-total_penalty) < g.max_per ) AS param_grade, " // 6
				//+"   (SELECT ROUND( (SUM(a.score_effective * param_weightage/100) * w.weightage/100),2)  "
				+"   (SELECT CAST((SUM(a.weighted_score_effective) * w.weightage/100) AS DECIMAL(8,2) )  " // // used weighted_score_effective
				+"	 	FROM  maint_rating_weightage_category w, audit_rating_parameter_score a, maint_rating_category c "
				+"   	WHERE w.rm_code=:rmCode AND w.legal_entity_code=:legalEntityCode AND a.audit_id=:auditId AND c.category_name LIKE '%MANAGEMENT%' "
				+"	 	AND c.legal_entity_code=w.legal_entity_code AND c.category_code=w.category_code AND w.legal_entity_code=a.legal_entity_code "
				+ " 	AND IF(category_for='','SUPERVISORY',category_for)='SUPERVISORY' "  // category cond added
				+"	 	AND w.category_code=a.category_code GROUP BY w.category_code, w.weightage) AS management_score, " // 7
				+"   (SELECT GROUP_CONCAT(deflator_score) FROM audit_rating_model_overlay o WHERE o.applicability='Y' AND o.level='Parameter' AND o.audit_id=:auditId) AS deflator_score_list, "  // 8 list of all deflator score
				+"   (SELECT g.value FROM general_parameter g where key_1='MODEL_OVERLAY' AND key_2='DEFLATOR_SCORE' AND g.legal_entity_code=:legalEntityCode) as gen_param_deflator_score, " // 9 deflator score from gen_param
				+"   (SELECT count(*) FROM audit_rating_model_overlay o WHERE o.applicability IS NOT NULL AND o.audit_id=:auditId) AS model_attended "  // 10 model overlay attended or not
				+"  FROM  "
				+"  (SELECT integrated_score,best_rating,total_penalty ,gr.max_per FROM "
				//+"    (SELECT ROUND(SUM(integrated_score),2) AS integrated_score  FROM  "
				//+" 	(SELECT ROUND( (SUM(a.score_effective *param_weightage/100) * w.weightage/100),2) AS integrated_score "
				+"  (SELECT CAST(SUM(integrated_score) AS DECIMAL(8,2)) AS integrated_score  FROM  "  // used weighted_score_effective
				+" 	 (SELECT CAST((SUM(a.weighted_score_effective) * w.weightage/100) AS DECIMAL(8,2)) AS integrated_score " // used weighted_score_effective
				+" 	   FROM  maint_rating_weightage_category w, audit_rating_parameter_score a "
				+" 	   WHERE w.rm_code=:rmCode AND w.legal_entity_code=:legalEntityCode AND w.category_code=a.category_code AND w.legal_entity_code=a.legal_entity_code AND a.audit_id=:auditId "
				+" 	   GROUP BY w.category_code, w.weightage)AS a1) AS s, "
				+"    (SELECT IFNULL(SUM(o.score_penalty),0) AS total_penalty FROM audit_rating_model_overlay o WHERE o.applicability='Y' AND o.audit_id=:auditId ) AS sp "
				+"    LEFT JOIN (SELECT m.best_rating,m.audit_id,g.max_per, m.legal_entity_code FROM audit_rating_model_overlay m, maint_rating_grade g  "
				+" 	      WHERE m.rm_code=g.rm_code AND m.best_rating=g.grade_code AND m.applicability='Y' AND m.audit_id=:auditId ORDER BY rating_internal LIMIT 1) AS gr "
				+"        ON 1=1  "				
				+"  WHERE 1=1 ) AS scr ");
				
			logger.info("Before the qry...."+ queryString.toString());
			Query query=session.createNativeQuery(queryString.toString())
					.setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId).setParameter("rmCode", rmCode);
			
			try {
				Object[] obj = (Object[])query.getSingleResult();
			
				if(obj !=null) {
					scoreDto = new AuditRatingScoreDto();
					int modelAttended=0;
					double integratedScore=0.0, managementScore=0.0, afterParamOverlayScore=0.0, modelMaxGradeScore=0.0, 
							deflatorScore=0.0, deflatorScoreGenParam=0.0, deflatorScoreFinal=0.0, normalizedScore=0.0, normalizedAftrOverlayScore=0.0;
					if(obj[0]!=null) { scoreDto.setIntegratedScore(obj[0].toString());
						integratedScore=Double.parseDouble(obj[0].toString());
					}
					if(obj[1]!=null) scoreDto.setAfterModelOverlayRating(obj[1].toString());  // The worst rating of selected Model overlays
					if(obj[2]!=null) scoreDto.setParamOverlayScore(obj[2].toString());  // total score of level as parameter
					if(obj[3]!=null) { scoreDto.setAfterOverlayScore(obj[3].toString());  // Total score - Total Param overlay penalty
						afterParamOverlayScore=Double.parseDouble(obj[3].toString());
					}
					if(obj[4]!=null) modelMaxGradeScore= Double.parseDouble(obj[4].toString()); // Max score of worst model overlay grade
					if(obj[5]!=null) scoreDto.setIntegratedRating(obj[5].toString());   // Grade/Rating based on the total score
					if(obj[6]!=null) scoreDto.setAfterParameterOverlayRating(obj[6].toString()); // Rating after removing the Penalty score 
					if(obj[7]!=null) { managementScore= Double.parseDouble(obj[7].toString());
						scoreDto.setManagementScore(obj[7].toString()); // Management score					
					}
					
					if(obj[10]!=null) modelAttended= Integer.parseInt(obj[10].toString());
					if(modelAttended >= 1) {
						if(obj[8]!=null) {
							String[] dfScores=obj[8].toString().split(",");
							deflatorScore=1;
							for(String dfScore: dfScores) {
								deflatorScore=deflatorScore * (1 - Double.parseDouble(dfScore));
							}
							if(obj[9]!=null) deflatorScoreGenParam= Double.parseDouble(obj[9].toString());
							if( deflatorScoreGenParam > 0 &&  deflatorScoreGenParam >= deflatorScore) 
								deflatorScoreFinal = managementScore * deflatorScoreGenParam;
							else deflatorScoreFinal=  managementScore * deflatorScore;					
							
							afterParamOverlayScore = integratedScore - managementScore + deflatorScoreFinal;							
						}else afterParamOverlayScore=integratedScore;
						scoreDto.setAfterOverlayScore(String.valueOf((double)Math.round((afterParamOverlayScore)*100)/100.00));
						normalizedAftrOverlayScore = Double.parseDouble(scoreDto.getAfterOverlayScore()) /5 * 100;
						scoreDto.setNormalisedAfterOverlayScore(String.valueOf((double)Math.round((normalizedAftrOverlayScore)*100)/100.00));
						
						logger.info("deflatorScore .. "+deflatorScore +" deflatorScoreFinal.. "+deflatorScoreFinal+ " integratedScore.."+ integratedScore+" managementScore .. "+managementScore+" afterParamOverlayScore .. "+afterParamOverlayScore+" normalizedAftrOverlayScore.. "+normalizedAftrOverlayScore); 
						// afterParamOverlayScore = Total Score - Management score + (Management score * Deflated score)
												
						normalizedScore= afterParamOverlayScore/5 * 100;
						logger.info("normalizedScore .. "+normalizedScore);
						scoreDto.setNormalisedScore(String.valueOf((double)Math.round((normalizedScore)*100)/100.00));
						logger.info("iScore:: "+ integratedScore+" ovrlyScore:: "+afterParamOverlayScore+" modMaxScore::"+ modelMaxGradeScore);
						
						/* To find the After param overlay score */
						try{
							StringBuilder queryAftrOvrRating=new StringBuilder(" SELECT g.grade_code, g.max_per FROM maint_rating_grade g "						
							+ " WHERE g.legal_entity_code=:legalEntityCode AND  g.rm_code =:rmCode AND :normalizedScore  >= g.min_per AND :normalizedScore < g.max_per ");
							logger.info("Before the qry...."+ queryAftrOvrRating.toString());
							Query qry2=session.createNativeQuery(queryAftrOvrRating.toString())
								.setParameter("legalEntityCode", legalEntityCode).setParameter("normalizedScore", normalizedScore).setParameter("rmCode", rmCode);
							
							/*Object[] obj1 = (Object[])qry2.getSingleResult();							
							if(obj1 !=null) {
								if(obj1[0] !=null) scoreDto.setAfterParameterOverlayRating(obj1[0].toString());
							}*/
							
							// Code modified for CPU
							List<Object[]> results = query.getResultList();
							if (!results.isEmpty()) {
							    Object[] obj1 = results.get(0);
							    if(obj1 !=null && obj1.length > 0 && obj1[0]!=null) 
							    	scoreDto.setAfterParameterOverlayRating(obj1[0].toString());
							}		
						}catch(Exception e) {
							e.printStackTrace(System.out);
						}
						/* end here*/
						
						
						if(scoreDto.getAfterModelOverlayRating()==null) scoreDto.setAfterModelOverlayRating(scoreDto.getIntegratedRating()); // If Model overlay is NO for all model levels
						
						/*if(integratedScore <= afterParamOverlayScore ) {
							if(integratedScore <= modelMaxGradeScore )
								scoreDto.setFinalRating(scoreDto.getIntegratedRating());
							else scoreDto.setFinalRating(scoreDto.getAfterModelOverlayRating());
						}else if(afterParamOverlayScore < modelMaxGradeScore) {
							scoreDto.setFinalRating(scoreDto.getAfterParameterOverlayRating());
						}else scoreDto.setFinalRating(scoreDto.getAfterModelOverlayRating());	*/		
						logger.info("integratedScore .."+integratedScore+" modelMaxGradeScore .."+modelMaxGradeScore+" afterParamOverlayScore .."+afterParamOverlayScore);
						logger.info("intRating ::"+scoreDto.getIntegratedRating()+" afterModel:: "+scoreDto.getAfterModelOverlayRating()+" afterParam:: "+scoreDto.getAfterParameterOverlayRating());
						if(afterParamOverlayScore > 0 && afterParamOverlayScore <= integratedScore ) {
							if(modelMaxGradeScore > 0 && modelMaxGradeScore <= (afterParamOverlayScore*20) )
								scoreDto.setFinalRating(scoreDto.getAfterModelOverlayRating());
							else scoreDto.setFinalRating(scoreDto.getAfterParameterOverlayRating());
						}else if(modelMaxGradeScore > 0 & modelMaxGradeScore <= (integratedScore*20) ) {
							scoreDto.setFinalRating(scoreDto.getAfterModelOverlayRating());
						}else scoreDto.setFinalRating(scoreDto.getIntegratedRating());
					}else { 
						scoreDto.setFinalRating(null);   // scoreDto.getIntegratedRating()
						scoreDto.setIntegratedRating(null);
						scoreDto.setAfterParameterOverlayRating(null);  // If no model overlay yet, set Parameter overlay as NULL
						scoreDto.setAfterModelOverlayRating(null); // If no model overlay yet, set Model overlay as NULL
					}
				}
				scoreDto.setRmCode(rmCode);
				scoreDto.setAuditId(auditId);
				scoreDto.setLegalEntityCode(legalEntityCode);
				logger.info("scoreDto .... "+scoreDto);
			}catch(Exception e) {
				e.printStackTrace(System.out);
			}
		return scoreDto;
	}
	
	/** To get the Final score for HO, after applying the Model overlay and Parameter overlay**/
	@Override
	public AuditRatingScoreDto getAuditRatingScoresHO(int legalEntityCode, String auditId, String rmCode) {
		logger.info("Inside getAuditRatingScoresHO .. "+ auditId);
		Session session = getSession();
		session.setDefaultReadOnly(true);
		
		AuditRatingScoreDto scoreDto = null;
		// replaced "score_effective" with "score_effective_ho" and "applicability" with "applicability_ho"
		StringBuilder queryString=new StringBuilder(" "
				+" SELECT integrated_score, best_rating AS model_overlay_grade, total_penalty, " //2
				+ "	 ROUND((integrated_score -total_penalty),2) AS score_after_penalty , scr.max_per as model_grade_max_per, " //4
				+"   (SELECT g.grade_code FROM maint_rating_grade g WHERE g.rm_code=:rmCode AND scr.integrated_score *20 >= g.min_per AND scr.integrated_score * 20 < g.max_per ) AS integrate_grade, "  // 5
				+"   (SELECT g.grade_code FROM maint_rating_grade g WHERE g.rm_code=:rmCode AND (scr.integrated_score-total_penalty) >= g.min_per AND (scr.integrated_score-total_penalty) < g.max_per ) AS param_grade, " // 6
				//+" (SELECT ROUND( (SUM(a.score_effective_ho * param_weightage/100) * w.weightage/100),2)  "  // commented to use the Weighted_score
				+"   (SELECT CAST((SUM(a.weighted_score_ho) * w.weightage/100) AS DECIMAL(8,2) )  " // used weighted_score_ho
				+"	 	FROM  maint_rating_weightage_category w, audit_rating_parameter_score a, maint_rating_category c "
				+"   	WHERE w.rm_code=:rmCode AND w.legal_entity_code=:legalEntityCode AND a.audit_id=:auditId AND c.category_name LIKE '%MANAGEMENT%' "
				+"	 	AND c.legal_entity_code=w.legal_entity_code AND c.category_code=w.category_code AND w.legal_entity_code=a.legal_entity_code "
				+" 	AND IF(category_for='','SUPERVISORY',category_for)='SUPERVISORY' "  // category cond added
				+"	 	AND w.category_code=a.category_code GROUP BY w.category_code, w.weightage) AS management_score, " // 7
				+"   (SELECT GROUP_CONCAT(deflator_score) FROM audit_rating_model_overlay o WHERE o.applicability_ho='Y' AND o.level='Parameter' AND o.audit_id=:auditId) AS deflator_score_list, "  // 8 list of all deflator score
				+"   (SELECT g.value FROM general_parameter g where key_1='MODEL_OVERLAY' AND key_2='DEFLATOR_SCORE' AND g.legal_entity_code=:legalEntityCode) as gen_param_deflator_score, " // 9 deflator score from gen_param
				+"   (SELECT count(*) FROM audit_rating_model_overlay o WHERE o.applicability_ho IS NOT NULL AND o.audit_id=:auditId) AS model_attended "  // 10 model overlay attended or not
				+"  FROM  "
				+"  (SELECT integrated_score,best_rating,total_penalty ,gr.max_per FROM "
				//+"    (SELECT ROUND(SUM(integrated_score),2) AS integrated_score  FROM  "
				//+" 	   (SELECT ROUND( (SUM(a.score_effective_ho *param_weightage/100) * w.weightage/100),2) AS integrated_score "
				+"      (SELECT CAST(SUM(integrated_score) AS DECIMAL(8,2)) AS integrated_score  FROM  "  // used weighted_score_ho
				+" 	     (SELECT CAST((SUM(a.weighted_score_ho) * w.weightage/100) AS DECIMAL(8,2)) AS integrated_score " // used weighted_score_ho
				+" 	      FROM  maint_rating_weightage_category w, audit_rating_parameter_score a "
				+" 	       WHERE w.rm_code=:rmCode AND w.legal_entity_code=:legalEntityCode AND w.category_code=a.category_code AND w.legal_entity_code=a.legal_entity_code AND a.audit_id=:auditId "
				+" 	        GROUP BY w.category_code, w.weightage)AS a1) AS s, "
				+"    (SELECT IFNULL(SUM(o.score_penalty),0) AS total_penalty FROM audit_rating_model_overlay o WHERE o.applicability_ho='Y' AND o.audit_id=:auditId ) AS sp "
				+"    LEFT JOIN (SELECT m.best_rating,m.audit_id,g.max_per, m.legal_entity_code FROM audit_rating_model_overlay m, maint_rating_grade g  "
				+" 	      WHERE m.rm_code=g.rm_code AND m.best_rating=g.grade_code AND m.applicability_ho='Y' AND m.audit_id=:auditId ORDER BY rating_internal LIMIT 1) AS gr "
				+"        ON 1=1  "				
				+"  WHERE 1=1 ) AS scr ");
				
			logger.info("Before the qry...."+ queryString.toString());
			Query query=session.createNativeQuery(queryString.toString())
					.setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId).setParameter("rmCode", rmCode);
			
			try {
				Object[] obj = (Object[])query.getSingleResult();
			
				if(obj !=null) {
					scoreDto = new AuditRatingScoreDto();
					int modelAttended=0;
					double integratedScore=0.0, managementScore=0.0, afterParamOverlayScore=0.0, modelMaxGradeScore=0.0, 
							deflatorScore=0.0, deflatorScoreGenParam=0.0, deflatorScoreFinal=0.0, normalizedScore=0.0, normalizedAftrOverlayScore=0.0;
					if(obj[0]!=null) { scoreDto.setIntegratedScore(obj[0].toString());
						integratedScore=Double.parseDouble(obj[0].toString());
					}
					if(obj[1]!=null) scoreDto.setAfterModelOverlayRating(obj[1].toString());  // The worst rating of selected Model overlays
					if(obj[2]!=null) scoreDto.setParamOverlayScore(obj[2].toString());  // total score of level as parameter
					if(obj[3]!=null) { scoreDto.setAfterOverlayScore(obj[3].toString());  // Total score - Total Param overlay penalty
						afterParamOverlayScore=Double.parseDouble(obj[3].toString());
					}
					if(obj[4]!=null) modelMaxGradeScore= Double.parseDouble(obj[4].toString()); // Max score of worst model overlay grade
					if(obj[5]!=null) scoreDto.setIntegratedRating(obj[5].toString());   // Grade/Rating based on the total score
					if(obj[6]!=null) scoreDto.setAfterParameterOverlayRating(obj[6].toString()); // Rating after removing the Penalty score 
					if(obj[7]!=null) { managementScore= Double.parseDouble(obj[7].toString());
						scoreDto.setManagementScore(obj[7].toString()); // Management score					
					}
					
					if(obj[10]!=null) modelAttended= Integer.parseInt(obj[10].toString());
					if(modelAttended >= 1) {
						if(obj[8]!=null) {
							String[] dfScores=obj[8].toString().split(",");
							deflatorScore=1;
							for(String dfScore: dfScores) {
								deflatorScore=deflatorScore * (1 - Double.parseDouble(dfScore));
							}
							if(obj[9]!=null) deflatorScoreGenParam= Double.parseDouble(obj[9].toString());
							if( deflatorScoreGenParam > 0 &&  deflatorScoreGenParam >= deflatorScore) 
								deflatorScoreFinal = managementScore * deflatorScoreGenParam;
							else deflatorScoreFinal=  managementScore * deflatorScore;					
							
							afterParamOverlayScore = integratedScore - managementScore + deflatorScoreFinal;							
						}else afterParamOverlayScore=integratedScore;
						scoreDto.setAfterOverlayScore(String.valueOf((double)Math.round((afterParamOverlayScore)*100)/100.00));
						normalizedAftrOverlayScore = Double.parseDouble(scoreDto.getAfterOverlayScore()) /5 * 100;
						scoreDto.setNormalisedAfterOverlayScore(String.valueOf((double)Math.round((normalizedAftrOverlayScore)*100)/100.00));
						
						logger.info("deflatorScore .. "+deflatorScore +" deflatorScoreFinal.. "+deflatorScoreFinal+ " integratedScore.."+ integratedScore+" managementScore .. "+managementScore+" afterParamOverlayScore .. "+afterParamOverlayScore+" normalizedAftrOverlayScore.. "+normalizedAftrOverlayScore); 
						// afterParamOverlayScore = Total Score - Management score + (Management score * Deflated score)
												
						normalizedScore= afterParamOverlayScore/5 * 100;
						logger.info("normalizedScore .. "+normalizedScore);
						scoreDto.setNormalisedScore(String.valueOf((double)Math.round((normalizedScore)*100)/100.00));
						logger.info("iScore:: "+ integratedScore+" ovrlyScore:: "+afterParamOverlayScore+" modMaxScore::"+ modelMaxGradeScore);
						
						/* To find the After param overlay score */
						try{
							StringBuilder queryAftrOvrRating=new StringBuilder(" SELECT g.grade_code, g.max_per FROM maint_rating_grade g "						
							+ " WHERE g.legal_entity_code=:legalEntityCode AND  g.rm_code =:rmCode AND :normalizedScore  >= g.min_per AND :normalizedScore < g.max_per ");
							logger.info("Before the qry...."+ queryAftrOvrRating.toString());
							Query qry2=session.createNativeQuery(queryAftrOvrRating.toString())
								.setParameter("legalEntityCode", legalEntityCode).setParameter("normalizedScore", normalizedScore).setParameter("rmCode", rmCode);
							Object[] obj1 = (Object[])qry2.getSingleResult();
							
							if(obj1 !=null) {
								if(obj1[0] !=null) scoreDto.setAfterParameterOverlayRating(obj1[0].toString());
							}
						}catch(Exception e) {
							e.printStackTrace(System.out);
						}
						/* end here*/
						
						
						if(scoreDto.getAfterModelOverlayRating()==null) scoreDto.setAfterModelOverlayRating(scoreDto.getIntegratedRating()); // If Model overlay is NO for all model levels
						
						/*if(integratedScore <= afterParamOverlayScore ) {
							if(integratedScore <= modelMaxGradeScore )
								scoreDto.setFinalRating(scoreDto.getIntegratedRating());
							else scoreDto.setFinalRating(scoreDto.getAfterModelOverlayRating());
						}else if(afterParamOverlayScore < modelMaxGradeScore) {
							scoreDto.setFinalRating(scoreDto.getAfterParameterOverlayRating());
						}else scoreDto.setFinalRating(scoreDto.getAfterModelOverlayRating());	*/		
						logger.info("integratedScore .."+integratedScore+" modelMaxGradeScore .."+modelMaxGradeScore+" afterParamOverlayScore .."+afterParamOverlayScore);
						logger.info("intRating ::"+scoreDto.getIntegratedRating()+" afterModel:: "+scoreDto.getAfterModelOverlayRating()+" afterParam:: "+scoreDto.getAfterParameterOverlayRating());
						if(afterParamOverlayScore > 0 && afterParamOverlayScore <= integratedScore ) {
							if(modelMaxGradeScore > 0 && modelMaxGradeScore <= (afterParamOverlayScore*20) )
								scoreDto.setFinalRating(scoreDto.getAfterModelOverlayRating());
							else scoreDto.setFinalRating(scoreDto.getAfterParameterOverlayRating());
						}else if(modelMaxGradeScore > 0 & modelMaxGradeScore <= (integratedScore*20) ) {
							scoreDto.setFinalRating(scoreDto.getAfterModelOverlayRating());
						}else scoreDto.setFinalRating(scoreDto.getIntegratedRating());
					}else { 
						scoreDto.setFinalRating(null);   // scoreDto.getIntegratedRating()
						scoreDto.setIntegratedRating(null);
						scoreDto.setAfterParameterOverlayRating(null);  // If no model overlay yet, set Parameter overlay as NULL
						scoreDto.setAfterModelOverlayRating(null); // If no model overlay yet, set Model overlay as NULL
					}
				}
				scoreDto.setRmCode(rmCode);
				scoreDto.setAuditId(auditId);
				scoreDto.setLegalEntityCode(legalEntityCode);
				logger.info("scoreDto .... "+scoreDto);
			}catch(Exception e) {
				e.printStackTrace(System.out);
			}
		return scoreDto;
	}
	
	/** To get the Final score for Existing Audit Types, No Model overlay and Parameter overlay**/
	public AuditRatingScoreDto getAuditRatingScoresNoWeightage(int legalEntityCode, String auditId, String rmCode, List<AuditRatingSummaryDto> sumDtos) {
		logger.info("Inside getAuditRatingScoresNoWeightage .. "+ legalEntityCode + " :: "+auditId +" :: "+rmCode);
		Session session = getSession();
		session.setDefaultReadOnly(true);
		AuditRatingScoreDto scoreDto = null;
		try {
			scoreDto= new AuditRatingScoreDto();
			for(AuditRatingSummaryDto sumDto: sumDtos) {
				if("CT003".equalsIgnoreCase(sumDto.getCategoryCode())) scoreDto.setManagementScore(sumDto.getTotalweightedScore());
				else if("CTALL".equalsIgnoreCase(sumDto.getCategoryCode())) scoreDto.setIntegratedScore(sumDto.getTotalweightedScore());
			}
			scoreDto.setNormalisedScore(scoreDto.getIntegratedScore());
			
			logger.info("Normalized score ...."+scoreDto.getNormalisedScore() +" ManagementScore .. "+scoreDto.getManagementScore());
			/* To get the Rating */
			try{
				StringBuilder queryAftrOvrRating=new StringBuilder(" SELECT g.grade_code, g.max_per FROM maint_rating_grade g "						
				+ " WHERE g.legal_entity_code=:legalEntityCode AND  g.rm_code =:rmCode AND :normalizedScore  >= g.min_per AND :normalizedScore < g.max_per ");
				logger.info("Before the qry...."+ queryAftrOvrRating.toString());
				Query qry2=session.createNativeQuery(queryAftrOvrRating.toString())
					.setParameter("legalEntityCode", legalEntityCode).setParameter("normalizedScore", scoreDto.getNormalisedScore()).setParameter("rmCode", rmCode);
				Object[] obj1 = (Object[])qry2.getSingleResult();
				
				if(obj1 !=null) {
					if(obj1[0] !=null) scoreDto.setIntegratedRating(obj1[0].toString());
				}
			}catch(Exception e) {
				e.printStackTrace(System.out);
			}
			/* end here*/
			scoreDto.setFinalRating(scoreDto.getIntegratedRating());
			scoreDto.setRmCode(rmCode);
			scoreDto.setAuditId(auditId);
			scoreDto.setLegalEntityCode(legalEntityCode);
			
			logger.info("scoreDto .... "+scoreDto);
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}		
		return scoreDto;
	}
	
	@Override
	public boolean isModelOverlayApplied(int legalEntityCode,String auditId) {
		Session session = getSession();		
		boolean isModelOverlayApplied=false;
		logger.info("Inside isModelOverlayApplied .. "+ legalEntityCode +" auditId .. "+auditId);
		long cntModelOverLayNull=(long)session.createQuery("SELECT COUNT(*) FROM AuditRatingModelOverlay ao WHERE auditId=:auditId AND legalEntityCode=:legalEntityCode AND applicability IS NOT NULL")
				.setParameter("auditId", auditId).setParameter("legalEntityCode", legalEntityCode).getSingleResult();
		if(cntModelOverLayNull >= 1) isModelOverlayApplied=true;
		return isModelOverlayApplied;		
	}
	
	@Override
	public List<MaintRatingModelOverlay> isModelOverlayExistForRatingModel(int legalEntityCode, String rmCode) {
		Session session = getSession();		
		logger.info("Inside isModelOverlayExistForRatingModel .. "+ legalEntityCode +" rmCode .. "+rmCode);
		return session.createQuery(" FROM MaintRatingModelOverlay ao WHERE rmCode=:rmCode AND legalEntityCode=:legalEntityCode")
				.setParameter("rmCode", rmCode).setParameter("legalEntityCode", legalEntityCode).list();		
	}
		
	@Override
	public List<AuditRatingModelOverlay> getModelOverlayByAuditId(int legalEntityCode, String auditId) {
		Session session = getSession();		
		logger.info("Inside getModelOverlayByAuditId .. "+ legalEntityCode +" auditId .. "+auditId);
		return session.createQuery(" FROM AuditRatingModelOverlay ao WHERE auditId=:auditId AND legalEntityCode=:legalEntityCode")
				.setParameter("auditId", auditId).setParameter("legalEntityCode", legalEntityCode).list();		
	}	
	
	@Override
	public List<AuditRatingModelOverlay> createAndGetAuditRatingModelOverlays(int legalEntityCode, String rmCode, String auditId, String userId ) {
		Session session = getSession();
		logger.info("Inside createAuditRatingModelOverlays ");
		List<AuditRatingModelOverlay> auditOverLays =null;
		try {
			List<MaintRatingModelOverlay> maintOverLays =null;
			maintOverLays=session.createQuery(" FROM MaintRatingModelOverlay mo WHERE rmCode=:rmCode AND legalEntityCode=:legalEntityCode")
			.setParameter("rmCode", rmCode).setParameter("legalEntityCode", legalEntityCode).list();
			
			if(!maintOverLays.isEmpty())
				auditOverLays= new ArrayList<AuditRatingModelOverlay>();
			for(MaintRatingModelOverlay modelOverlay: maintOverLays) {
				if("A".equalsIgnoreCase(modelOverlay.getStatus())) {
					AuditRatingModelOverlay aol= new AuditRatingModelOverlay();
					aol.setLegalEntityCode(legalEntityCode);
					aol.setAuditId(auditId);
					aol.setRmCode(modelOverlay.getRmCode());
					aol.setLevel(modelOverlay.getLevel());
					aol.setTheme(modelOverlay.getTheme());
					aol.setCriteria(modelOverlay.getCriteria());
					aol.setCriteriaCode(modelOverlay.getCriteriaCode());
					aol.setOverlays(modelOverlay.getOverlays());
					aol.setBestRating(modelOverlay.getBestRating());
					aol.setDeflatorScore(modelOverlay.getDeflatorScore());
					aol.setMaker(userId);	
					aol.setMakerTimestamp(new Timestamp(System.currentTimeMillis()));
					aol.setStatus(BankAuditConstant.STATUS_UNAUTH);
					auditOverLays.add(aol);
					auditRatingParameterScoreDao.flushSession();
					auditRatingParameterScoreDao.saveOrUpdate(aol);
				}
			}logger.info("auditOverLays .. "+auditOverLays);	
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}return auditOverLays;
	}	
	
	@Override
	public void updateAuditRatingModelOverlays(List<AuditRatingModelOverlay> auditRatingModelOverlays) {
		logger.info("Inside updateAuditRatingModelOverlays ");
		for(AuditRatingModelOverlay modelOverlay: auditRatingModelOverlays) {
			if("M".equalsIgnoreCase(modelOverlay.getStatus())) {
				modelOverlay.setStatus(BankAuditConstant.STATUS_UNAUTH);
				auditRatingParameterScoreDao.flushSession();
				auditRatingParameterScoreDao.saveOrUpdate(modelOverlay);
			}
		}		
	}
	
	@Override
	public void updateAuditRatingParameterCategoryScore(List<AuditRatingSummaryDto> auditRatingParameterCategoryLst, int legalEntityCode, String auditId, String auditTypeCode) {
		logger.info("Inside updateAuditRatingParameterCategoryScore "+ auditId+" :: "+ auditTypeCode);
		for(AuditRatingSummaryDto smrDto: auditRatingParameterCategoryLst) {
			if(smrDto.getCategoryCode() !=null) {
				AuditRatingParameterCategoryScore paramCatScr= new AuditRatingParameterCategoryScore();
				paramCatScr.setLegalEntityCode(legalEntityCode);
				paramCatScr.setAuditId(auditId);
				paramCatScr.setCategoryCode(smrDto.getCategoryCode());
				paramCatScr.setAuditTypeCode(auditTypeCode);
				paramCatScr.setRmCode(smrDto.getRmCode());
				paramCatScr.setCategoryPrcntg(smrDto.getTotalweightedScoreNormalized());
				paramCatScr.setCategoryPrcntgAftrOverlay(smrDto.getTotalweightedScoreNormalized());
				paramCatScr.setScoreOn100(smrDto.getTotalScoreNormalized());
				paramCatScr.setScoreOn100AftrOverlay(smrDto.getTotalScoreNormalized());
				auditRatingParameterScoreDao.flushSession();
				auditRatingParameterScoreDao.saveOrUpdate(paramCatScr);	
			}
		}		
	}
	
	
	@Override
	public String getContinuousScoreForParamCode(AuditRatingParameterScoreDto scoreDto) {
		Session session = getSession();
		logger.info("Inside getConcreteScore:: "+scoreDto.getLegalEntityCode()+" :: "+scoreDto.getAuditId()+ " :: "+scoreDto.getParamCode()+" :: "+scoreDto.getProcessedScore()+" :: "+scoreDto.getParamCalcType());
		String concreteScore=null;
		String paramCalcType=scoreDto.getParamCalcType();
		double ipScore=Double.parseDouble(scoreDto.getProcessedScore());
		try {
			StringBuilder qryParamr=new StringBuilder(" SELECT p.param_code,p.legal_entity_code, p.min_range, p.max_range, p.score, "  // 4
					+"   r2.min_range AS p_min_range,r2.max_range AS p_max_range, r2.score AS p_score  "	  // 7	
					+"  FROM (SELECT score,min_range,max_range, param_code,legal_entity_code FROM  "
					+"         (SELECT IFNULL(min_range,-99999999) AS min_r, IFNULL(max_range,99999999) AS max_r, score,min_range,max_range,param_code,legal_entity_code "
					+"		    FROM maint_rating_parameter_range WHERE param_code=:paramCode )r "
					+"	     WHERE param_code=:paramCode  AND ( :tmpScore > r.min_r AND :tmpScore <= r.max_r) ) AS p "
					+" LEFT JOIN maint_rating_parameter_range r2 ON r2.param_code=:paramCode AND r2.legal_entity_code=:legalEntityCode "
					+"		AND r2.legal_entity_code=p.legal_entity_code AND p.min_range=IFNULL(r2.max_range,99999999) limit 1 ");
			
			Query qry=session.createNativeQuery(qryParamr.toString())
					.setParameter("legalEntityCode", scoreDto.getLegalEntityCode()).setParameter("paramCode", scoreDto.getParamCode())
					.setParameter("tmpScore", scoreDto.getProcessedScore());			
			
			Object[] obj = (Object[])qry.getSingleResult();
			if("P0803".equals(scoreDto.getParamCode())) {
				logger.info("Inside ...."+scoreDto.getParamCode());
			}
			
			if(obj !=null) {
				double min=0.0, max=0.0, score=0.0, p_min=0.0, p_max=0.0, p_score=0.0, discreteScore=0.0, concScore=0.0;
				if(obj[2]!=null) min=Double.parseDouble(obj[2].toString());	
				if(obj[3]!=null) max=Double.parseDouble(obj[3].toString());		
				if(obj[4]!=null) {score=Double.parseDouble(obj[4].toString());	
								  discreteScore=Double.parseDouble(obj[4].toString());}
				if(obj[5]!=null) p_min=Double.parseDouble(obj[5].toString());	
				if(obj[6]!=null) p_max=Double.parseDouble(obj[6].toString());		
				if(obj[7]!=null) p_score=Double.parseDouble(obj[7].toString());	
				logger.info("ds::"+discreteScore +" max::"+max+" is::"+ipScore+" Pmax::"+p_max+" sc::"+score+" psc::"+p_score);
				/**************************  ContineousScore ******************************/
				// ContineousScore = DiscreteScore+ [(MaxRange -InputScore)/(MaxRange-PreviousMaxRange) * (PreviousRangeScore-CurrentRangeScore)]
				/*************END HERE *******************/
				if(obj[2]==null || obj[3]==null )  // The Discrete score is already out side of the range
					concScore = discreteScore;
				else concScore = discreteScore + ((max- ipScore)/ (max-p_max) * (p_score - score));
				logger.info(" Score for .. "+scoreDto.getParamCode()+" ::"+ concScore );	
				concreteScore =String.valueOf((Math.round(concScore*100)/100.00));
			}
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}return concreteScore;
	}	
	
	@Override
	public String getDescreteScoreForParamCode(AuditRatingParameterScoreDto scoreDto) {
		Session session = getSession();
		logger.info("Inside getDescreteScoreForParamCode:: "+scoreDto.getLegalEntityCode()+" :: "+scoreDto.getAuditId()+ " :: "+scoreDto.getParamCode()+" :: "+scoreDto.getProcessedScore()+" :: "+scoreDto.getParamCalcType());
		String concreteScore=null;
		String paramCalcType=scoreDto.getParamCalcType();
		double ipScore=Double.parseDouble(scoreDto.getProcessedScore());
		try {
			StringBuilder qryParamr=new StringBuilder(" SELECT p.param_code,p.legal_entity_code, p.min_range, p.max_range, p.score, "  // 4
					+"   r2.min_range AS p_min_range,r2.max_range AS p_max_range, r2.score AS p_score  "	  // 7	
					+"  FROM (SELECT score,min_range,max_range, param_code,legal_entity_code FROM  "
					+"         (SELECT IFNULL(min_range,-99999999) AS min_r, IFNULL(max_range,99999999) AS max_r, score,min_range,max_range,param_code,legal_entity_code "
					+"		    FROM maint_rating_parameter_range WHERE param_code=:paramCode )r "
					+"	     WHERE param_code=:paramCode  AND ( :tmpScore > r.min_r AND :tmpScore <= r.max_r) ) AS p "
					+" LEFT JOIN maint_rating_parameter_range r2 ON r2.param_code=:paramCode AND r2.legal_entity_code=:legalEntityCode "
					+"		AND r2.legal_entity_code=p.legal_entity_code AND p.min_range=IFNULL(r2.max_range,99999999) limit 1 ");
			
			Query qry=session.createNativeQuery(qryParamr.toString())
					.setParameter("legalEntityCode", scoreDto.getLegalEntityCode()).setParameter("paramCode", scoreDto.getParamCode())
					.setParameter("tmpScore", scoreDto.getProcessedScore());			
			
			Object[] obj = (Object[])qry.getSingleResult();
			if("P0803".equals(scoreDto.getParamCode())) {
				logger.info("Inside ...."+scoreDto.getParamCode());
			}
			
			if(obj !=null) {
				double min=0.0, max=0.0, score=0.0, p_min=0.0, p_max=0.0, p_score=0.0, discreteScore=0.0, concScore=0.0;
				if(obj[2]!=null) min=Double.parseDouble(obj[2].toString());	
				if(obj[3]!=null) max=Double.parseDouble(obj[3].toString());		
				if(obj[4]!=null) {score=Double.parseDouble(obj[4].toString());	
								  discreteScore=Double.parseDouble(obj[4].toString());}
				if(obj[5]!=null) p_min=Double.parseDouble(obj[5].toString());	
				if(obj[6]!=null) p_max=Double.parseDouble(obj[6].toString());		
				if(obj[7]!=null) p_score=Double.parseDouble(obj[7].toString());	
				logger.info("ds::"+discreteScore +" max::"+max+" is::"+ipScore+" Pmax::"+p_max+" sc::"+score+" psc::"+p_score);
				/**************************  ContineousScore ******************************/
				// ContineousScore = DiscreteScore+ [(MaxRange -InputScore)/(MaxRange-PreviousMaxRange) * (PreviousRangeScore-CurrentRangeScore)]
				/*************END HERE *******************/
				if(obj[2]==null || obj[3]==null || "DSC".equalsIgnoreCase(paramCalcType) )  // The Discrete score is already out side of the range
					concScore = discreteScore;
				else concScore = discreteScore + ((max- ipScore)/ (max-p_max) * (p_score - score));
				logger.info(" Score for .. "+scoreDto.getParamCode()+" ::"+ concScore );	
				concreteScore =String.valueOf((Math.round(concScore*100)/100.00));
			}
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}return concreteScore;
	}	
	
		
	/** Method to get the Inspections as View, for Operations and Management users **/
	@SuppressWarnings("unchecked")
	@Override
	public DataTableResponse getAuditsForView(int legalEntityCode,String userId, String unitCode, String roleId, String search,Integer orderColumn,
			String orderDirection, Integer page, Integer size) {

		Session session = getSession();
		session.setDefaultReadOnly(true);
		
		logger.info("Inside getAuditsForView :: "+ legalEntityCode+" :: "+ userId +" :: "+ unitCode+" :: "+roleId+" :: "+search);
		/********************** This block is to retrieve the User details --> UserId and UserName **************************/
		StringBuilder queryString0=new StringBuilder(" from User u where  u.legalEntityCode =:legalEntityCode ");
		
		Query query1=session.createQuery("from User u where  u.legalEntityCode =:legalEntityCode ").setParameter("legalEntityCode", legalEntityCode);
		
		List lst = query1.list();
		HashMap<String, String> userDtls = new HashMap<String, String>();
		for ( Iterator iter = lst.iterator(); iter.hasNext(); ) {
			User usr = (User) iter.next();
			if(usr.getUserId().equalsIgnoreCase(userId))
				userDtls.put(usr.getUserId(), "Me"); // for the same User we are stamping as "You"
			else userDtls.put(usr.getUserId(), (usr.getFirstName()==null?"":usr.getFirstName()) 
											+ (usr.getMiddleName()==null?"":(" "+usr.getMiddleName())) 
											+ (usr.getLastName()==null?"":(" "+usr.getLastName())) );	
		}
		logger.info("User details val is .... "+ userDtls);
		/******************** User Retrieval logic, Ends here ********************************/
		
		Date businessDate =businessDateService.getBusinessDateTimestampByLegalEntityCode(legalEntityCode);
		logger.info("businessDate .... "+ businessDate);
		List<String> maintEntities = null;
		maintEntities=maintEntityService.getSubBranchesGyUserIdOrUnitId(legalEntityCode,"user",userId);
			logger.info("maintEntities .... "+maintEntities);
		
		boolean isDeptAsRMD =false;
		//isDeptAsRMD = reportDao.isDeptAsRMD(legalEntityCode, "", userId);
		
		DataTableResponse dataTableResponse=new DataTableResponse();
		try {
			StringBuilder queryString=new StringBuilder("select sch as auditSchedule, " 
				+ " legalEntityCode,"
				+ "(select concat(auditTypeCode , '-' , auditTypeDesc) from MaintAuditTypeDesc at where at.auditTypeCode=sch.auditTypeCode and at.legalEntityCode= sch.legalEntityCode ) as auditTypeCode,"
				
				+ "planId,auditId, "
				+ " (Select concat(unitCode , '-' , unitName) from  MaintEntity e where e.unitCode =sch.unitId and e.legalEntityCode =sch.legalEntityCode ) as unitId,  "
				+ " (Select g.description from  MaintEntity e,GeneralParameter g where e.unitCode =sch.unitId and e.legalEntityCode =sch.legalEntityCode and e.legalEntityCode =g.legalEntityCode and g.key1='UNIT_TYPE' and g.key2='UNIT_TYPE' and g.value=e.unitType) as unitType, "
				+ " (Select c.criticalityDesc from  MaintEntity e,MaintCriticality c where e.unitCode =sch.unitId and e.legalEntityCode =sch.legalEntityCode and e.legalEntityCode =c.legalEntityCode and e.uCriticality=c.criticalityCode and c.criticalityOfType='Entity') as uCriticality, "
				+ " (Select t.userId from  AuditTeam t where t.auditId=sch.auditId and t.legalEntityCode =sch.legalEntityCode and t.userType='L') as teamLead, "
				+" duration," //Code to comment //+ " (Select p.duration from  MaintEntity e,MaintAuditParameter p where e.unitCode =sch.unitId and e.unitType=p.unitType and e.uCriticality=p.uCriticality and sch.auditTypeCode=p.auditTypeCode and e.legalEntityCode =sch.legalEntityCode and e.legalEntityCode =p.legalEntityCode ) as duration, "
				+" frequency," //Code to comment //+ " (Select p.frequency from  MaintEntity e,MaintAuditParameter p where e.unitCode =sch.unitId and e.unitType=p.unitType and e.uCriticality=p.uCriticality and sch.auditTypeCode=p.auditTypeCode and e.legalEntityCode =sch.legalEntityCode and e.legalEntityCode =p.legalEntityCode ) as frequency, "
				+ " (Select concat(sch.auditStatus , '-' , g.description) from  GeneralParameter g where g.legalEntityCode =sch.legalEntityCode and g.key1='STATUS' and g.key2='AUDIT_STATUS' and g.value=sch.auditStatus) as auditStatus, "
				+ " (Select concat(u.userId , '-' , ifnull(u.firstName,''), ' ',ifnull(u.middleName,''),' ',ifnull(u.lastName,'') ) "
				+ " from  User u where u.legalEntityCode =sch.legalEntityCode and u.userId=sch.maker) as maker "
				+" ,0,0,0 "  // No need to calculate responseReject, Compliance, NA
				//+ " , auditStatus "
				+ ", (Select g.value from GeneralParameter g where g.legalEntityCode =sch.legalEntityCode and g.key1='AUDIT_EARLY_START' and g.key2='EARLY_START_ALLOWED') as earlyStartAllowed "
				+ ", (Select g.value from GeneralParameter g where g.legalEntityCode =sch.legalEntityCode and g.key1='AUDIT_OFFSET_DAYS' and g.key2='AUDIT_OFFSET_DAYS') as auditOffsetDays " 
				+ ", (Select concat(sch.auditStatus , '-' , g.description) from  GeneralParameter g where g.legalEntityCode =sch.legalEntityCode and g.key1='STATUS' and g.key2='AUDIT_STATUS' and g.value=sch.observationStatus) as auditStatusTemp " // This result to stamp in Audit status in the Observation status is AE i.e Audit Reported
				+ ", (select group_concat(t.userId) from AuditTeam t where t.legalEntityCode=sch.legalEntityCode AND t.auditId=sch.auditId AND t.userType='M' ) AS teamMember "
				+" ,   sch.reportStatus " // Report review status added for BaaSDOS implementation 
				+ ",  (Select concat(sch.reportStatus , '-' , g.description) from  GeneralParameter g where g.legalEntityCode =sch.legalEntityCode and g.key1='STATUS' and g.key2='REPORT_STATUS' and g.value=sch.reportStatus) as reportStatusDesc " // Report review status desc added for BaaSDOS implementation 
				+ " , (Select concat(sch.status , '-' , g.description) from  GeneralParameter g where g.legalEntityCode =sch.legalEntityCode "
				+ " and g.key1='STATUS' and g.key2='STATUS' and g.value=sch.status) as authStatus "  
				 + " from AuditSchedule sch where legalEntityCode =:legalEntityCode "
				+ " and  IFNULL(auditStatus,'NotNull') not in ('AD','AU','DRP','UP','AN')   "
				);
				
				//if(!isDeptAsRMD) {
				//	queryString.append(" and  sch.auditTypeCode IN (SELECT asgm.auditTypeCode FROM MaintEntityAuditSubgroupMapping as asgm WHERE asgm.legalEntityCode=sch.legalEntityCode and asgm.mappingType='U' and asgm.id =:userId )"); // To show the Assessments to which the Assessor Mapped (Department mapping)
				//}
				//if(visibleAuditList !=null && visibleAuditList.s)
				//+ " and (sch.auditId  IN :visibleAuditList  OR auditStatus in ('AI','AS','AR','AC','AU','UP','AD','AW')) " //commented to remove Visibility cond, Condition included to get the Audits as per the Visibility logic maintained in general parameter.
					
			    if(("G2").equalsIgnoreCase(roleId) || ("G8").equalsIgnoreCase(roleId)) {
			    	queryString.append(" and sch.auditId IN (SELECT t.auditId FROM AuditTeam t WHERE t.auditId=sch.auditId and t.legalEntityCode =sch.legalEntityCode and t.userId=:userId  )");
			    }else if (("G1").equalsIgnoreCase(roleId) || ("G6").equalsIgnoreCase(roleId)) {
			    	queryString.append(" and sch.unitId IN (:maintEntities)  ");
			    }else {
			    	queryString.append(" and sch.unitId IN (:maintEntities)  ");
			    }
			    
				
			    
		logger.info("Before the qry....");
		
		if(!BankAuditUtil.isEmptyString(search)){
			queryString=queryString.append(" and ( "
					+ " sch.auditTypeCode IN (SELECT a.auditTypeCode FROM MaintAuditTypeDesc a  WHERE a.legalEntityCode=sch.legalEntityCode AND (a.auditTypeDesc LIKE :search OR a.auditTypeCode LIKE :search)) "
					+ " or planId like :search "
					+ " or auditId like :search "
					//+ " or unitId like :search "
					+ " or sch.unitId IN (SELECT m.unitCode FROM MaintEntity m WHERE m.legalEntityCode=sch.legalEntityCode AND (m.unitName LIKE :search or m.unitCode LIKE :search )) "
					+ " or sch.unitId IN (SELECT m.unitCode FROM MaintEntity m,GeneralParameter g WHERE sch.unitId=m.unitCode and m.legalEntityCode=sch.legalEntityCode AND m.legalEntityCode =g.legalEntityCode and g.key1='UNIT_TYPE' and g.key2='UNIT_TYPE' and g.value=m.unitType and g.description LIKE :search) "
					+ " or sch.unitId IN (SELECT m.unitCode FROM MaintEntity m,MaintCriticality c WHERE sch.unitId=m.unitCode and m.legalEntityCode=sch.legalEntityCode AND m.legalEntityCode =c.legalEntityCode and m.uCriticality=c.criticalityCode and c.criticalityOfType='Entity' and  c.criticalityDesc LIKE :search) "
					+ " or sch.unitId IN (SELECT m.unitCode FROM MaintEntity m,MaintAuditParameter p WHERE sch.unitId=m.unitCode and m.legalEntityCode=sch.legalEntityCode AND m.legalEntityCode =p.legalEntityCode and m.uCriticality=p.uCriticality and sch.auditTypeCode=p.auditTypeCode and  p.duration LIKE :search) "
					+ " or sch.auditId IN (SELECT t.auditId FROM User u,AuditTeam t WHERE t.auditId=sch.auditId and t.legalEntityCode =sch.legalEntityCode and t.legalEntityCode =u.legalEntityCode and t.userId=u.userId and (t.userId Like :search or u.firstName LIKE :search or u.lastName Like :search) ) "
					+ " or DATE_FORMAT(sch.scheduleStartDate, '%d %b %Y') LIKE :search "
					+ " or DATE_FORMAT(sch.scheduleEndDate, '%d %b %Y') LIKE :search "
					+ " or DATE_FORMAT(sch.actualStartDate, '%d %b %Y') LIKE :search "
					+ " or DATE_FORMAT(sch.actualEndDate, '%d %b %Y') LIKE :search "
					+ " or DATE_FORMAT(sch.lastAuditDate, '%d %b %Y') LIKE :search "
					+ " or sch.maker IN (SELECT u.userId FROM User u WHERE u.legalEntityCode =sch.legalEntityCode and (u.userId Like :search or u.firstName LIKE :search or u.lastName Like :search) ) "
					+ " or sch.auditStatus IN (SELECT g.value FROM GeneralParameter g WHERE sch.legalEntityCode =g.legalEntityCode and g.key1='STATUS' and g.key2='AUDIT_STATUS' and g.value=sch.auditStatus and g.description LIKE :search) "
					+ " or sch.reportStatus IN (SELECT g.value FROM GeneralParameter g WHERE sch.legalEntityCode =g.legalEntityCode and g.key1='STATUS' and g.key2='REPORT_STATUS' and g.value=sch.reportStatus and g.description LIKE :search) "
					// + " or teamMember like :search "
					+ " or sch.auditId IN (select t.auditId from AuditTeam t, User u where t.legalEntityCode=sch.legalEntityCode AND t.auditId=sch.auditId AND t.userType='M' AND t.legalEntityCode=u.legalEntityCode AND t.userId=u.userId "
					+ "						AND sch.unitId=u.unitCode AND (u.firstName like :search OR u.middleName like :search OR u.lastName like :search OR u.userId like :search )) "
					
					//+ " ) "
					
						+ " or sch.auditStatus IN (SELECT g.value FROM GeneralParameter g WHERE sch.legalEntityCode =g.legalEntityCode and g.key1='STATUS' "
						+ " and g.key2='AUDIT_STATUS' and g.value=sch.auditStatus "
						+ " and (g.description LIKE :search or g.value LIKE :search)"
						+ " and   ( sch.observationStatus !='AE'  ) "
						+ " ) "
						
						+ " or sch.auditStatus IN (SELECT g.value FROM GeneralParameter g WHERE sch.legalEntityCode =g.legalEntityCode and g.key1='STATUS' "
						+ " and g.key2='AUDIT_STATUS' and g.value=sch.auditStatus "
						+ " and (g.description LIKE :search or g.value LIKE :search)"
						+ " and   ( sch.observationStatus  ='AE' and sch.auditStatus='AW'  ) "
						+ " ) " 
						
						+ "  " );
						
						if((search.length()==2 && search.toUpperCase().startsWith("EX") )
						|| (search.length()==3 && search.toUpperCase().startsWith("EXI"))
						|| (search.length()==4 && search.toUpperCase().startsWith("EXIT"))
						|| ((search.length()==5 || search.length()==6) && search.toUpperCase().startsWith("EXITE")) ){
							queryString.append("Or (sch.auditStatus='AI' and sch.observationStatus='AE' ) )");
						}	
						else{
							queryString.append(" )" );
							if(search.toUpperCase().startsWith("INI")){
								queryString.append("and  ( sch.auditStatus='AI' and (sch.observationStatus is null or sch.observationStatus='' ) )");
							}
						}
					/*queryString.append(" )" );*/
		}
		
		/*queryString=queryString.append("  ORDER BY FIELD(auditStatus, 'AI','P','AP','AS','AC','AU') asc ");*/
		String[] columns={
				"auditTypeCode",
				"(auditId *1 ), (planId *1 )", //"CAST(planId AS Integer),CAST(auditId AS Integer) "
				"unitType",
				"(unitId * 1) ",  //"unitId",
				"uCriticality",
				"duration",
				"lastAuditDate",//last audit date actually 
				"sch.scheduleStartDate",
				"sch.scheduleEndDate",
				"sch.actualStartDate",
				"sch.actualEndDate",
				"teamLead", // Team Lead
				// "sch.maker", // Team Member
				"sch.maker",
				"sch.reportStatus",
				"sch.auditStatus"
		};

		/*if(orderColumn!=null
				&& !BankAuditUtil.isEmptyString(orderDirection)){
			queryString=queryString.append(" order by ").append(columns[orderColumn]).append(" ").append(orderDirection);
		}*/
		logger.info(" orderColumn **.... "+ orderColumn +"  orderDirection ...."+ orderDirection );
		if(orderColumn != null && orderColumn == -1 && !BankAuditUtil.isEmptyString(orderDirection)){ // -1 send for the first time from the UI during landing page.
			queryString=queryString.append(" ORDER BY FIELD(auditStatus, 'AI','AS','AR','AC') asc  "); // The code modified to get the records in the order of Status on landing page, the sequence is  Initiated, Planned, Submitted, Respond, Reviewed, Closed
		}else if(orderColumn!=null && !BankAuditUtil.isEmptyString(orderDirection)){
			if(orderColumn==0) {
				queryString=queryString.append(" ORDER BY  ").append(columns[orderColumn]).append(" ").append(orderDirection).append(" , FIELD(auditStatus, 'AI','AP','AS','AR','AC') asc ,(auditId *1 ) asc  ");
			}else {
				queryString=queryString.append(" ORDER BY  ").append(columns[orderColumn]).append(" ").append(orderDirection);
			}
		}else {
			queryString=queryString.append(" ORDER BY FIELD(auditStatus, 'AI','AS','AR','AC') asc  ");
		}
		
		logger.info("queryString .... "+ queryString.toString());
		
		Query query=(Query) session.createQuery(queryString.toString())				
				.setResultTransformer(new ResultTransformer() {
					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						AuditSchedule auditSchedule=null;
						//System.out.println("tuple[0] ...."+tuple[0]);
						if(tuple[0]!=null){
							auditSchedule=(AuditSchedule)tuple[0];
							/* session.evict(maintEntity);*/
							
							if(tuple[1]!=null){
								auditSchedule.setLegalEntityCode(Integer.parseInt(tuple[1].toString()));								
							}
							if(tuple[2]!=null){
								auditSchedule.setAuditTypeCode(tuple[2].toString());
							}
							
							if(tuple[3]!=null){
								auditSchedule.setPlanId(tuple[3].toString());
							}
							
							if(tuple[4]!=null){
								auditSchedule.setAuditId(tuple[4].toString());
							}
							if(tuple[5]!=null){
								auditSchedule.setUnitId(tuple[5].toString());
							}
							if(tuple[6]!=null){
								auditSchedule.setUnitType(tuple[6].toString());
							}
							if(tuple[7]!=null){
								auditSchedule.setuCriticality(tuple[7].toString());
							}
							if(tuple[8]!=null){ 
									String teamLead = userDtls.get(tuple[8].toString());
									auditSchedule.setTeamLead(teamLead.equalsIgnoreCase("Me")?"Me":tuple[8].toString()+"-"+teamLead);
								 
								//auditSchedule.setTeamLead(tuple[8].toString()+"-"+userDtls.get(tuple[8].toString()));
								//auditSchedule.setTeamLead(userDtls.get(tuple[8].toString()));
							}
							if(tuple[9]!=null){
								auditSchedule.setDuration(tuple[9].toString());
							}
							if(tuple[10]!=null){
								auditSchedule.setFrequency(tuple[10].toString());
							}
							if(tuple[11]!=null){
								if("P".equalsIgnoreCase(tuple[11].toString()))auditSchedule.setAuditStatus("AP"); // temp purpose as in few earlier records the Planned status is reflecting as "P"
								else if("AE".equalsIgnoreCase(auditSchedule.getObservationStatus())) auditSchedule.setAuditStatus(tuple[18] !=null ?tuple[18].toString() :tuple[11].toString() );
								else auditSchedule.setAuditStatus(tuple[11].toString());
							}
							if(tuple[12]!=null){
								auditSchedule.setMaker(tuple[12].toString());
							}
							if(tuple[13]!=null && auditSchedule.getRespondedFindings() != null){
								auditSchedule.setResponseReject(Integer.parseInt(tuple[13].toString()));
								auditSchedule.setRespondedFindings(auditSchedule.getRespondedFindings()- (Integer.parseInt(tuple[13].toString()))); // Included to find out the Responded Observations
							}else auditSchedule.setResponseReject(0);
							
							if(tuple[14]!=null){
								auditSchedule.setComplianceCount(Integer.parseInt(tuple[14].toString()));
							}else auditSchedule.setComplianceCount(0);
							
							if(tuple[15]!=null){
								auditSchedule.setNotApplicableCount(Integer.parseInt(tuple[15].toString()));
							}else auditSchedule.setNotApplicableCount(0);
							
							if(tuple[16]!=null){
								auditSchedule.setEarlyStartAllowed(tuple[16].toString());
							}else auditSchedule.setEarlyStartAllowed("Y");
							
							if(tuple[17]!=null){
								auditSchedule.setAuditOffsetDays(Integer.parseInt(tuple[17].toString()));
							}else auditSchedule.setAuditOffsetDays(0);
							
							if(tuple[19]!=null){
								String teamMembers="";
								String[] members = tuple[19].toString().split(",");
								for (String member: members) {
									teamMembers+=member+"-"+userDtls.get(member)+",";
								}
								auditSchedule.setTeamMember(teamMembers.substring(0,teamMembers.length()-1));
							}
							
							if(tuple[20]!=null) auditSchedule.setReportStatus(tuple[20].toString());
							if(tuple[21]!=null) auditSchedule.setReportStatusDesc(tuple[21].toString());
							
							if(tuple[22]!=null) auditSchedule.setStatus(tuple[22].toString());
							
							/*int tmSize =auditSchedule.getAuditTeams().size()-1;
							String teamMember="";
							System.out.println("Audit Id .... "+ auditSchedule.getAuditId()+" tmSize .."+tmSize);
							for( AuditTeam adtTm: auditSchedule.getAuditTeams() ) {
								
								adtTm.setUserId(adtTm.getUserId()+"-"+userDtls.get(adtTm.getUserId()));
								
								if(!"L".equalsIgnoreCase(adtTm.getUserType())) teamMember +=adtTm.getUserId() + ",";
								System.out.println("teamMember .... "+ teamMember);
							}if(tmSize >0 ) auditSchedule.setTeamMember(teamMember.substring(0,teamMember.lastIndexOf(",")));*/
							//System.out.println("Team member .... "+auditSchedule.getTeamMember());
							//System.out.println("Team size ...."+auditSchedule.getAuditTeams().size());
							
						}
						return auditSchedule;
					}
			@Override
			public List transformList(List collection) {
				return collection;
			}
		}).setParameter("legalEntityCode",legalEntityCode); //.setParameter("visibleAuditList", visibleAuditList);
		
		if(("G2").equalsIgnoreCase(roleId) || ("G8").equalsIgnoreCase(roleId)) {
	    	query.setParameter("userId",userId  );
	    }else if (("G1").equalsIgnoreCase(roleId) || ("G6").equalsIgnoreCase(roleId)) {
	    	query.setParameter("maintEntities", maintEntities);
	    }else {
	    	query.setParameter("maintEntities", maintEntities);
	    }
		
		if(!BankAuditUtil.isEmptyString(search)){
			query.setParameter("search", "%"+search+"%");
		}
		
		logger.info("page :: "+page +"size :: "+ size);
		List<AuditSchedule> tmpAuditSchedule =query.list(); 
		Long totalRecords=null;
		try {
			totalRecords = (long)tmpAuditSchedule.size();
		}catch(Exception e) {
			totalRecords = (long)0;
		}
		List<AuditSchedule> auditSchedule=query.setFirstResult((page*size)).setMaxResults(size).list();
		
		logger.info("Bfr size tmpAuditSchedule is .... "+tmpAuditSchedule.size()+" totalRecords:: "+ totalRecords);
		
		/*int i=0;
		for(AuditSchedule adSch:auditSchedule){
			System.out.println("Inside adSch ...");
			if("A2".equalsIgnoreCase(adSch.getAuditId()));
			else 	tmpAuditSchedule.add(adSch);
			System.out.println("after the call");
			
		}
		System.out.println("size tmpAuditSchedule .... "+tmpAuditSchedule);*/
		
		
		dataTableResponse.setData(auditSchedule);
		dataTableResponse.setRecordsTotal(totalRecords);
		dataTableResponse.setRecordsFiltered(totalRecords);
		
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		return dataTableResponse;
	
	}
		
	@Override
	public List<AuditRatingParameterScore> downloadRatingParameterScoresByAuditId(int legalEntityCode, String auditId, String auditTypeCode,
			 String paramBasis) {
		Session session = getSession();
		session.setDefaultReadOnly(true);
		
		List<AuditRatingParameterScore> auditRatingScoreLst = null;
		logger.info("Inside downloadRatingParameterScoresByAuditId .. "+ legalEntityCode +" auditId .. "+auditId +" auditTypeCode .."+auditTypeCode +" paramBasis .."+paramBasis);
		  
		StringBuilder queryString=new StringBuilder("SELECT arps as auditRatingParameterScore,  "
				+ " mpd.paramName  "							// 1  
				+ " from AuditRatingParameterScore arps INNER JOIN MaintRatingParameterDtls mpd  "					 
				+ " ON arps.legalEntityCode = mpd.legalEntityCode "
				+ " and arps.paramCode = mpd.paramCode   "
				+ " WHERE arps.legalEntityCode = :legalEntityCode"
				+ " and arps.auditId = :auditId "
				+ " and arps.auditTypeCode = :auditTypeCode ");
			if(!BankAuditUtil.isEmptyString(paramBasis)){
				queryString.append(" and arps.paramBasis = :paramBasis  ");  
			}
		Query query=(Query) session.createQuery(queryString.toString())				
				.setResultTransformer(new ResultTransformer() {
					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						AuditRatingParameterScore auditRatingScore=null;
						if(tuple[0]!=null){
							auditRatingScore=(AuditRatingParameterScore)tuple[0]; 
							if(tuple[1]!=null) auditRatingScore.setParamName(tuple[1].toString()); 								 
						}
						return auditRatingScore;
					}
			@Override
			public List transformList(List collection) {
				return collection;
			}
		}).setParameter("legalEntityCode", legalEntityCode)
		  .setParameter("auditId", auditId)
		  .setParameter("auditTypeCode", auditTypeCode);
		if(!BankAuditUtil.isEmptyString(paramBasis)){
			query.setParameter("paramBasis", paramBasis);
		}
		auditRatingScoreLst=query.list();

		 return auditRatingScoreLst; 
	}
	
	/** Rating PDf Report **/
	@Override
	public List<RatingParameterCategoryDto> getCategoryWiseRatingsByAuditId(int legalEntityCode,String auditId, String auditTypeCode) {
		Session session = getSession();
		session.setDefaultReadOnly(true);
		
		RatingParameterReportDto rptDto=new RatingParameterReportDto();
		List<RatingParameterCategoryDto> catLst = null;
		logger.info("Inside getCategoryWiseRatingsByAuditId .. "+ legalEntityCode +" auditId .. "+auditId +" auditTypeCode .."+auditTypeCode );
		  
		StringBuilder queryString=new StringBuilder(" SELECT audit_id, r.param_code, m.param_name, "  // --0-2
				+ "   r.param_mesaurement_type, r.param_weightage, processed_score, score_assessed, score_effective, "  // --7
				+ "   maker_remarks, reviewer_remarks, ho_remarks, category_code, sub_category_code, unit_id, "  // --13
				+ "   (SELECT cat.category_name FROM maint_rating_category cat  "
				+ " 	WHERE cat.category_code=r.category_code AND cat.legal_entity_code=r.legal_entity_code) AS cat_name, " // -14
				+ "   (SELECT scat.sub_category_name FROM maint_rating_sub_category scat  "
				+ " 	WHERE scat.category_code=r.category_code AND scat.sub_category_code=r.sub_category_code AND scat.legal_entity_code=r.legal_entity_code) AS sub_cat_name, " // -15
				//+ "   ROUND(score_effective*r.param_weightage/100.00,2) AS calc_score " // -- 16  Commented as we are deriving and storing the Weighted score
				+ "   weighted_score_effective AS calc_score " // -- 16
				+ " FROM audit_rating_parameter_score r, maint_rating_parameter_dtls m "
				+ " WHERE r.legal_entity_code=m.legal_entity_code AND r.param_code=m.param_code  "
				+ "     AND m.legal_entity_code=:legalEntityCode AND audit_id=:auditId AND r.audit_type_code = :auditTypeCode "
				+ " ORDER BY category_code, param_mesaurement_type DESC, param_code ");
			
		Query query=(Query) session.createNativeQuery(queryString.toString()).setParameter("legalEntityCode", legalEntityCode)
		  .setParameter("auditId", auditId)
		  .setParameter("auditTypeCode", auditTypeCode);
		
		List lst=query.list();
		try {
			String catCode=null; String catCodeTemp=null; String qnQl=null; 
			double pScr=0, catScr=0, qnScr=0, qlScr=0, pWtg=0;
			int cnt=1, ctCnt=1, qnCnt=1, qlCnt=1; 
			List<RatingParameterDtlsDto> qnDtls = null;
			List<RatingParameterDtlsDto> qlDtls = null;
			
			RatingParameterCategoryDto cDto=null;
			catLst = new ArrayList<>();
			for ( Iterator iter = lst.iterator(); iter.hasNext(); ) {
				Object[] obj =(Object[])iter.next();
				catCode= obj[11]!=null?obj[11].toString():"" ;
				logger.info("recNo:::::::::::::::::::::::::::::::::::::::"+cnt +"catCode .."+catCode);
				
				RatingParameterDtlsDto pDtls= new RatingParameterDtlsDto();
				if(obj[1]!=null) pDtls.setParamCode(obj[1].toString());
				if(obj[2]!=null) pDtls.setParamName(obj[2].toString());
				if(obj[4]!=null) { 
					pWtg=Double.parseDouble(obj[4].toString());
					pDtls.setParamWeightage(String.valueOf((double)Math.round(pWtg *100)/100.00));
					}
				if(obj[5]!=null) pDtls.setProcessedScore(obj[5].toString());
				if(obj[7]!=null) {
					pDtls.setScoreEffective(obj[7].toString());
					pScr = Double.parseDouble(obj[7].toString()) * pWtg/100;	// Used till Error of approximation not calculated
					// pDtls.setScoreFinal(String.valueOf((double)Math.round(pScr *100/100.00)));
				}
				if(obj[16]!=null) {
					pDtls.setScoreFinal(obj[16].toString());
					pScr = Double.parseDouble(obj[16].toString());  // To pick the score from weighted_score_effective if present
				}
				if(obj[8]!=null) pDtls.setMakerRemarks(obj[8].toString());
				if(obj[9]!=null) pDtls.setReviewerRemarks(obj[9].toString());
				if(obj[10]!=null) pDtls.setHoRemarks(obj[10].toString());
				qnQl= obj[3]!=null?obj[3].toString():"";
				pDtls.setParamMesaurementType(qnQl);
				if("QL".equalsIgnoreCase(qnQl)) pDtls.setProcessedScore(obj[7]!=null?obj[7].toString():null);
				
				if(cnt==1) {
					cDto=new RatingParameterCategoryDto();
					cDto.setCategorySlNo(BankAuditUtil.integerToRomanNumeral(ctCnt));
					cDto.setCategoryCode(catCode);
					if(obj[14]!=null)cDto.setCategoryName(obj[14].toString());
					
					qnDtls = new ArrayList<>();
					qlDtls = new ArrayList<>();
					if("QN".equalsIgnoreCase(qnQl)) {
						pDtls.setSlNo(String.valueOf(qnCnt));qnCnt++;
						qnScr+=pScr;
						qnDtls.add(pDtls);						
					}else if("QL".equalsIgnoreCase(qnQl)) {
						pDtls.setSlNo(String.valueOf(qlCnt));qlCnt++;
						qlScr+=pScr;
						qlDtls.add(pDtls);								
					}
					//catCodeTemp = catCode;
				}
				
				if(catCode.equalsIgnoreCase(catCodeTemp)){
					logger.info("Inside If");
					if("QN".equalsIgnoreCase(qnQl)) {
						pDtls.setSlNo(String.valueOf(qnCnt));qnCnt++;
						qnScr+=pScr;
						qnDtls.add(pDtls);						
					}else if("QL".equalsIgnoreCase(qnQl)) {
						pDtls.setSlNo(String.valueOf(qlCnt));qlCnt++;
						qlScr+=pScr;
						qlDtls.add(pDtls);								
					}					
				}else {
					logger.info("Inside else");
					if(cnt != 1) {
						RatingParameterQnQLDto qnDto= new RatingParameterQnQLDto();
						qnDto.setQnQlName("Quantitative");
						qnDto.setQnQlToatlScore(String.valueOf((double)Math.round(qnScr*100)/100.00));
						qnDto.setParamDtls(qnDtls);
						
						RatingParameterQnQLDto qlDto= new RatingParameterQnQLDto();
						qlDto.setQnQlName("Qualitative");
						qlDto.setQnQlToatlScore(String.valueOf((double)Math.round(qlScr*100)/100.00));
						qlDto.setParamDtls(qlDtls);
						
						cDto.setCategoryToatlScore(String.valueOf((double)Math.round((qnScr+ qlScr)*100)/100.00));
						cDto.setQuantitativeDtls(qnDto);
						cDto.setQualitativeDtls(qlDto);
						
						catLst.add(cDto);  // Adding Category to the list
						
						ctCnt++;
						
						// Resting all the temp values
						qnDtls = new ArrayList<>(); qlDtls = new ArrayList<>();
						qnScr=0; qlScr=0; qnCnt=1; qlCnt=1;
						//catCodeTemp = catCode;
						
						cDto=new RatingParameterCategoryDto();
						cDto.setCategorySlNo(BankAuditUtil.integerToRomanNumeral(ctCnt));
						cDto.setCategoryCode(catCode);
						if(obj[14]!=null)cDto.setCategoryName(obj[14].toString());
						
						qnDtls = new ArrayList<>();
						qlDtls = new ArrayList<>();
						if("QN".equalsIgnoreCase(qnQl)) {
							pDtls.setSlNo(String.valueOf(qnCnt));qnCnt++;
							qnScr+=pScr;
							qnDtls.add(pDtls);						
						}else if("QL".equalsIgnoreCase(qnQl)) {
							pDtls.setSlNo(String.valueOf(qlCnt));qlCnt++;
							qlScr+=pScr;
							qlDtls.add(pDtls);								
						}
					}	
				}
				
				if(cnt == lst.size()) {
					RatingParameterQnQLDto qnDto= new RatingParameterQnQLDto();
					qnDto.setQnQlName("Quantitative");
					qnDto.setQnQlToatlScore(String.valueOf((double)Math.round((qnScr)*100)/100.00));
					qnDto.setParamDtls(qnDtls);
					
					RatingParameterQnQLDto qlDto= new RatingParameterQnQLDto();
					qlDto.setQnQlName("Qualitative");
					qlDto.setQnQlToatlScore(String.valueOf((double)Math.round((qlScr)*100)/100.00));
					qlDto.setParamDtls(qlDtls);
					
					cDto.setCategoryToatlScore(String.valueOf((double)Math.round((qnScr+ qlScr)*100)/100.00));
					cDto.setQuantitativeDtls(qnDto);
					cDto.setQualitativeDtls(qlDto);
					
					catLst.add(cDto);  // Adding Category to the list
				}
				catCodeTemp = catCode;cnt++;
			}
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		return catLst; 
	}
	
	@Override
	public List<AuditRatingSummaryDto> getAuditRatingSummaryForReport(int legalEntityCode, String auditId, String rmCode, String userType) {
		logger.info("Inside getAuditRatingSummaryForReport .. "+ auditId+" :: "+ userType);
		Session session = getSession();
		session.setDefaultReadOnly(true);
		String scoreEffective="weighted_score_effective";
		if("HO".equalsIgnoreCase(userType)) scoreEffective="weighted_score_ho";
		logger.info("Weighted_score as Score effective after User Type .."+ scoreEffective);
		
		AuditRatingSummaryDto auditSummaryDto = null;
		List<AuditRatingSummaryDto> lstSummary= null; 
		StringBuilder queryString=new StringBuilder(" SELECT c.legal_entity_code, w.rm_code, c.category_code,c.category_name, w.weightage, "  //4
			//+"  ROUND((SELECT SUM("+scoreEffective+"*param_weightage/100) FROM audit_rating_parameter_score s WHERE s.rm_code=w.rm_code AND s.category_code=w.category_code AND param_mesaurement_type='QN' AND audit_id=:auditId ),2) AS qn_score, " // 5
			//+"  ROUND((SELECT SUM("+scoreEffective+"*param_weightage/100) FROM audit_rating_parameter_score s WHERE s.rm_code=w.rm_code AND s.category_code=w.category_code AND param_mesaurement_type='QL' AND audit_id=:auditId ),2) AS ql_score, " // 6
			//+"  ROUND((SELECT SUM("+scoreEffective+"*param_weightage/100)* w.weightage / 100 FROM audit_rating_parameter_score s WHERE s.rm_code=w.rm_code AND s.category_code=w.category_code AND audit_id=:auditId ),2) AS cat_score  "  // 7
			
			+"  (SELECT SUM("+scoreEffective+") FROM audit_rating_parameter_score s WHERE s.rm_code=w.rm_code AND s.category_code=w.category_code AND param_mesaurement_type='QN' AND audit_id=:auditId ) AS qn_score, " // 5
			+"  (SELECT SUM("+scoreEffective+") FROM audit_rating_parameter_score s WHERE s.rm_code=w.rm_code AND s.category_code=w.category_code AND param_mesaurement_type='QL' AND audit_id=:auditId ) AS ql_score, " // 6
			+"  (SELECT SUM("+scoreEffective+") * w.weightage / 100 FROM audit_rating_parameter_score s WHERE s.rm_code=w.rm_code AND s.category_code=w.category_code AND audit_id=:auditId ) AS cat_score  "  // 7
			+" FROM  maint_rating_weightage_category w, maint_rating_category c " 
			+"  WHERE w.rm_code=:rmCode AND w.legal_entity_code=:legalEntityCode AND "
			+ " w.category_code=c.category_code AND w.legal_entity_code=c.legal_entity_code AND"
			+ " IF(category_for='','SUPERVISORY',category_for)='SUPERVISORY' "  // category cond added
			+" order by c.category_code ");
				
			logger.info("Before the qry...."+ queryString.toString());
			
			Query query=session.createNativeQuery(queryString.toString())
					.setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId).setParameter("rmCode", rmCode);
			
			List qrylst = query.list();	
			try {
				lstSummary = new ArrayList<>();
				String modelAttendedFlag="Y"; 
				double finalTotal=0.0, finalWeightedTotalNormalized=0.0;
				for ( Iterator iter = qrylst.iterator(); iter.hasNext(); ) {
					Map dflatedMap=null;
					Object[] obj = (Object[]) iter.next();
					logger.info("=============================================================== "+obj[2].toString()+" ==> "+ obj[3].toString());
					double weightage=0.0, qn=0.0, ql=0.0, weightedScore=0.0, deflaterScore=1.0;
					auditSummaryDto = new AuditRatingSummaryDto();
					auditSummaryDto.setLegalEntityCode(legalEntityCode);
					auditSummaryDto.setAuditId(auditId);
					auditSummaryDto.setRmCode(rmCode);
					auditSummaryDto.setCategoryCode(obj[2].toString());
					auditSummaryDto.setCategoryName(obj[3].toString());
					if(obj[4] !=null ) {
						auditSummaryDto.setWeightage(obj[4].toString());
						weightage=Double.parseDouble(obj[4].toString());
					}if(obj[5] !=null ) {
						auditSummaryDto.setQuantitativeSore(obj[5].toString());
						qn=Double.parseDouble(obj[5].toString());
					}if(obj[6] !=null ) {
						auditSummaryDto.setQualitativeScore(obj[6].toString());
						ql=Double.parseDouble(obj[6].toString());
					}if(obj[7] !=null ) {  // Weighted score @Categoy level => Sum(EffectiveScore@CategoryCode) X CategoryWeight
						logger.info("Cat Name .."+obj[2].toString());
						if("CT003".equalsIgnoreCase(obj[2].toString())) { // for Management score need to call deflator and subtract the score
							dflatedMap= getDeflatedScoreForAuditId(legalEntityCode, auditId, rmCode, userType);
							if(dflatedMap !=null && "Y".equalsIgnoreCase(dflatedMap.get("MODEL_ATTENDED").toString())) {
								deflaterScore= Double.parseDouble(dflatedMap.get("DEFLATED_SCORE").toString());
								double mgFinalScore= Double.parseDouble(obj[7].toString()) * deflaterScore;
								logger.info("mgScore .."+obj[7].toString()+ "deflaterScore .."+deflaterScore +" mgFinalScore .. "+ mgFinalScore);
								//auditSummaryDto.setTotalweightedScore(String.valueOf(Math.round(mgFinalScore*100)/100.00));
								auditSummaryDto.setTotalweightedScore(String.valueOf(mgFinalScore));
							}else { auditSummaryDto.setTotalweightedScore("0.0"); // If Model is unattended, then Management Score is blank
									modelAttendedFlag="N";
							}
						}else {
							auditSummaryDto.setTotalweightedScore(obj[7].toString());								
						}
						finalTotal+=Double.parseDouble(auditSummaryDto.getTotalweightedScore());
					}
					logger.info("final Rating after ::"+obj[3].toString()+" :: "+finalTotal);
					
					if("CT003".equalsIgnoreCase(obj[2].toString()) && dflatedMap !=null && "Y".equalsIgnoreCase(dflatedMap.get("MODEL_ATTENDED").toString())) { // for Management score need to call deflator and subtract the score
						auditSummaryDto.setTotalScore(String.valueOf((double)Math.round((qn+ql)*deflaterScore *100)/100.00));
						weightedScore=(double)Math.round((qn+ql)*deflaterScore*weightage)/100.00;
						auditSummaryDto.setTotalweightedScoreDirect(String.valueOf(weightedScore));
					}else {
						auditSummaryDto.setTotalScore(String.valueOf((double)Math.round((qn+ql)*100)/100.00));
						weightedScore=(double)Math.round((qn+ql)*weightage)/100.00;
						auditSummaryDto.setTotalweightedScoreDirect(String.valueOf(weightedScore));
					}
					logger.info("weightedScore for ::"+obj[3].toString()+" :: "+weightage+" QnQl=>"+ qn +" :: "+ql +" WieghtScore:: "+weightedScore);
					
					if("RM01".equalsIgnoreCase(rmCode) || "RM02".equalsIgnoreCase(rmCode)) {
						auditSummaryDto.setTotalScoreNormalized( String.valueOf(Math.round(Double.parseDouble(auditSummaryDto.getTotalScore()) * 20 * 100)/100.00 ));
						finalWeightedTotalNormalized +=Math.round(Double.parseDouble(auditSummaryDto.getTotalweightedScore()) * 20 *100)/100.00;
						auditSummaryDto.setTotalweightedScoreNormalized(String.valueOf(Math.round(Double.parseDouble(auditSummaryDto.getTotalweightedScore()) * 20 *100)/100.00));
						auditSummaryDto.setTotalweightedScoreDirectNormalized(String.valueOf(Math.round(Double.parseDouble(auditSummaryDto.getTotalweightedScoreDirect()) * 20 *100)/100.00));
					} 
					logger.info("finalWeightedTotalNormalized for ::"+obj[3].toString()+" :: "+finalWeightedTotalNormalized);
					if("CT003".equalsIgnoreCase(obj[2].toString()) && "N".equalsIgnoreCase(modelAttendedFlag)) { // If Model is unattended, then Management score is blank
						auditSummaryDto.setTotalScore("");
						auditSummaryDto.setTotalScoreNormalized("");
						auditSummaryDto.setTotalweightedScoreNormalized("");
						auditSummaryDto.setTotalweightedScoreDirectNormalized("");
					}
					
					//finalTotal+=weightedScore;
					lstSummary.add(auditSummaryDto);					
				}			
				
				logger.info("************************************* finalTotal ...."+finalTotal + " finalWeightedTotalNormalized .."+finalWeightedTotalNormalized);
				
				// Adding the Final score =======> Total Marks
				auditSummaryDto = new AuditRatingSummaryDto();
				auditSummaryDto.setLegalEntityCode(legalEntityCode);
				auditSummaryDto.setAuditId(auditId);
				auditSummaryDto.setRmCode(rmCode);
				auditSummaryDto.setRmCode("CTALL"); // Added on 08Sep24, as 3rd widget Rating used in 4th Widget
				auditSummaryDto.setCategoryName("Total Marks ");
				auditSummaryDto.setTotalweightedScore(String.valueOf(Math.round((finalTotal)*100)/100.00));
				auditSummaryDto.setTotalweightedScoreNormalized(String.valueOf(Math.round((finalWeightedTotalNormalized)*100)/100.00));
				if("N".equalsIgnoreCase(modelAttendedFlag)){  // If Model is unattended, then Total Mark is blank
					auditSummaryDto.setTotalweightedScore("");
					auditSummaryDto.setTotalweightedScoreNormalized("");
				}
				lstSummary.add(auditSummaryDto);
				// End here
				
				// Adding to get the Rating based on Score ========> Composite Supervisory Rating
				String finalRating=null;
				try{
					auditSummaryDto = new AuditRatingSummaryDto();
					auditSummaryDto.setLegalEntityCode(legalEntityCode);
					auditSummaryDto.setAuditId(auditId);
					auditSummaryDto.setRmCode(rmCode);
					auditSummaryDto.setCategoryName("Composite Supervisory Rating");
					logger.info("finalWeightedTotalNormalized .. "+finalWeightedTotalNormalized);	
					
					StringBuilder queryAftrOvrRating=new StringBuilder(" SELECT g.grade_code, g.max_per FROM maint_rating_grade g "						
					+ " WHERE g.legal_entity_code=:legalEntityCode AND  g.rm_code =:rmCode AND :finalWeightedTotalNormalized  >= g.min_per AND :finalWeightedTotalNormalized < g.max_per ");
					logger.info("Before the qry of Rating ...."+ queryAftrOvrRating.toString());
					Query qry2=session.createNativeQuery(queryAftrOvrRating.toString())
						.setParameter("legalEntityCode", legalEntityCode).setParameter("finalWeightedTotalNormalized", finalWeightedTotalNormalized).setParameter("rmCode", rmCode);
					Object[] obj1 = (Object[])qry2.getSingleResult();
					
					if(obj1 !=null) {
						if(obj1[0] !=null) { // auditSummaryDto.setTotalweightedScore(obj1[0].toString());
							// auditSummaryDto.setTotalweightedScoreNormalized(obj1[0].toString());
							auditSummaryDto.setFinalRating(obj1[0].toString());
							finalRating=obj1[0].toString();
						}
					}		
					if("N".equalsIgnoreCase(modelAttendedFlag)){ //// If Model is unattended, then Rating is blank 
						auditSummaryDto.setFinalRating("");
					}
					lstSummary.add(auditSummaryDto);
				}catch(Exception e) {
					e.printStackTrace(System.out);
				}logger.info("Final Rating .."+finalRating);
				// End here				
				
				
				// To get the Final Rating of Widget 4 ===========> 
				logger.info("============== FINAL_RATING_AFTR_DEFLATOR ============ WIDGET 4===> Final Rating ");
				logger.info("finalWeightedTotalNormalized .. .. "+finalWeightedTotalNormalized);
				auditSummaryDto = new AuditRatingSummaryDto();
				auditSummaryDto.setLegalEntityCode(legalEntityCode);
				auditSummaryDto.setAuditId(auditId);
				auditSummaryDto.setRmCode(rmCode);
				auditSummaryDto.setCategoryCode("FINAL_RATING_AFTR_DEFLATOR");
				auditSummaryDto.setCategoryName("Final Rating After Deflator");
				String ratingScr =String.valueOf(finalWeightedTotalNormalized);
				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);
				lstSummary.add(auditSummaryDto);
				//End, Rating after Deflator
								
				logger.info("lstSummary .... "+lstSummary);
			}catch(Exception e) {
				e.printStackTrace(System.out);
			}
		return lstSummary;
	}
	
	/******************************* for HO with RO *******************************************/
	@Override
	public List<RatingParameterCategoryDto> getCategoryWiseRatingsByAuditIdHORO(int legalEntityCode,String auditId, String auditTypeCode) {
		Session session = getSession();
		session.setDefaultReadOnly(true);
		
		RatingParameterReportDto rptDto=new RatingParameterReportDto();
		List<RatingParameterCategoryDto> catLst = null;
		logger.info("Inside getCategoryWiseRatingsByAuditId .. "+ legalEntityCode +" auditId .. "+auditId +" auditTypeCode .."+auditTypeCode );
		  
		StringBuilder queryString=new StringBuilder(" SELECT audit_id, r.param_code, m.param_name, "  // --0-2
				+ "   r.param_mesaurement_type, r.param_weightage, processed_score, score_assessed, score_effective, "  // --7
				+ "   maker_remarks, reviewer_remarks, ho_remarks, category_code, sub_category_code, unit_id, "  // --13
				+ "   (SELECT cat.category_name FROM maint_rating_category cat  "
				+ " 	WHERE cat.category_code=r.category_code AND cat.legal_entity_code=r.legal_entity_code) AS cat_name, " // -14
				+ "   (SELECT scat.sub_category_name FROM maint_rating_sub_category scat  "
				+ " 	WHERE scat.category_code=r.category_code AND scat.sub_category_code=r.sub_category_code AND scat.legal_entity_code=r.legal_entity_code) AS sub_cat_name, " // -15
				//+ "   ROUND(score_effective*r.param_weightage/100.00,2) AS calc_score " // -- 16
				//+ "   , score_effective_ho , ROUND(score_effective_ho * r.param_weightage/100.00,2) AS calc_score_ho "  // -- 18
				+ "     weighted_score_effective, score_effective_ho , weighted_score_ho " // 16, 17, 18
				+ " FROM audit_rating_parameter_score r, maint_rating_parameter_dtls m "
				+ " WHERE r.legal_entity_code=m.legal_entity_code AND r.param_code=m.param_code  "
				+ "     AND m.legal_entity_code=:legalEntityCode AND audit_id=:auditId AND r.audit_type_code = :auditTypeCode "
				+ " ORDER BY category_code, param_mesaurement_type DESC, param_code ");
			
		Query query=(Query) session.createNativeQuery(queryString.toString()).setParameter("legalEntityCode", legalEntityCode)
		  .setParameter("auditId", auditId)
		  .setParameter("auditTypeCode", auditTypeCode);
		
		List lst=query.list();
		try {
			String catCode=null; String catCodeTemp=null; String qnQl=null; 
			double pScr=0, catScr=0, qnScr=0, qlScr=0, pWtg=0;
			double pScrHo=0, catScrHo=0, qnScrHo=0, qlScrHo=0;
			int cnt=1, ctCnt=1, qnCnt=1, qlCnt=1; 
			List<RatingParameterDtlsDto> qnDtls = null;
			List<RatingParameterDtlsDto> qlDtls = null;
			
			RatingParameterCategoryDto cDto=null;
			catLst = new ArrayList<>();
			for ( Iterator iter = lst.iterator(); iter.hasNext(); ) {
				Object[] obj =(Object[])iter.next();
				catCode= obj[11]!=null?obj[11].toString():"" ;
				logger.info("recNo:::::::::::::::::::::::::::::::::::::::"+cnt +"catCode .."+catCode);
				
				RatingParameterDtlsDto pDtls= new RatingParameterDtlsDto();
				if(obj[1]!=null) pDtls.setParamCode(obj[1].toString());
				if(obj[2]!=null) pDtls.setParamName(obj[2].toString());
				if(obj[4]!=null) { 
					pWtg=Double.parseDouble(obj[4].toString());
					pDtls.setParamWeightage(String.valueOf((double)Math.round(pWtg *100)/100.00));
				}
				if(obj[5]!=null) pDtls.setProcessedScore(obj[5].toString());
				
				// If weighted_score_effective is present => Override Score = weighted_score_effective(obj[16])
				//	 									else pick score_effective(obj[7]) * wtg(obj[4])
				if(obj[7]!=null) {
					pDtls.setScoreEffective(obj[7].toString());
					pScr = Double.parseDouble(obj[7].toString()) * pWtg/100;	
					// pDtls.setScoreFinal(String.valueOf((double)Math.round(pScr *100/100.00)));
				}
				if(obj[16]!=null) { // weighted score RO
					pDtls.setScoreFinal(obj[16].toString());
					pScr= Double.parseDouble(obj[16].toString()); // If weighted_score_effective present then Override the pScr -> obj[7](Scr_eff) *obj[4](Prm_wtg)
				}// Logic ends here
				
				if(obj[17]!=null) { // Score HO
					pDtls.setScoreEffectiveHO(obj[17].toString());
					pScrHo = Double.parseDouble(obj[17].toString()) * pWtg/100;	
				}
				
				if(obj[18]!=null) {
					pDtls.setScoreFinalHO(obj[18].toString());  // weighted Score HO
					pScrHo= Double.parseDouble(obj[18].toString()); // If weighted_score_ho present then Override the pScr -> weighted_score_effective
				}
				if(obj[8]!=null) pDtls.setMakerRemarks(obj[8].toString());
				if(obj[9]!=null) pDtls.setReviewerRemarks(obj[9].toString());
				if(obj[10]!=null) pDtls.setHoRemarks(obj[10].toString());
				qnQl= obj[3]!=null?obj[3].toString():"";
				pDtls.setParamMesaurementType(qnQl);
				if("QL".equalsIgnoreCase(qnQl)) pDtls.setProcessedScore(obj[7]!=null?obj[7].toString():null);
				
				if(cnt==1) {
					cDto=new RatingParameterCategoryDto();
					cDto.setCategorySlNo(BankAuditUtil.integerToRomanNumeral(ctCnt));
					cDto.setCategoryCode(catCode);
					if(obj[14]!=null)cDto.setCategoryName(obj[14].toString());
					
					qnDtls = new ArrayList<>();
					qlDtls = new ArrayList<>();
					if("QN".equalsIgnoreCase(qnQl)) {
						pDtls.setSlNo(String.valueOf(qnCnt));qnCnt++;
						qnScr+=pScr;  qnScrHo+=pScrHo;  // HO Score
						qnDtls.add(pDtls);						
					}else if("QL".equalsIgnoreCase(qnQl)) {
						pDtls.setSlNo(String.valueOf(qlCnt));qlCnt++;
						qlScr+=pScr; qlScrHo+=pScrHo;  // HO Score
						qlDtls.add(pDtls);								
					}
					//catCodeTemp = catCode;
				}
				
				if(catCode.equalsIgnoreCase(catCodeTemp)){
					logger.info("Inside If");
					if("QN".equalsIgnoreCase(qnQl)) {
						pDtls.setSlNo(String.valueOf(qnCnt));qnCnt++;
						qnScr+=pScr;  qnScrHo+=pScrHo;  // Qn HO Score
						qnDtls.add(pDtls);						
					}else if("QL".equalsIgnoreCase(qnQl)) {
						pDtls.setSlNo(String.valueOf(qlCnt));qlCnt++;
						qlScr+=pScr;  qlScrHo+=pScrHo;  // Ql HO Score
						qlDtls.add(pDtls);								
					}					
				}else {
					logger.info("Inside else");
					if(cnt != 1) {
						RatingParameterQnQLDto qnDto= new RatingParameterQnQLDto();
						qnDto.setQnQlName("Quantitative");
						qnDto.setQnQlToatlScore(String.valueOf((double)Math.round(qnScr*100)/100.00));
						qnDto.setQnQlToatlScoreHO(String.valueOf((double)Math.round(qnScrHo*100)/100.00)); // HO Score
						qnDto.setParamDtls(qnDtls);
						
						RatingParameterQnQLDto qlDto= new RatingParameterQnQLDto();
						qlDto.setQnQlName("Qualitative");
						qlDto.setQnQlToatlScore(String.valueOf((double)Math.round(qlScr*100)/100.00));
						qlDto.setQnQlToatlScoreHO(String.valueOf((double)Math.round(qlScrHo*100)/100.00)); // HO Score
						qlDto.setParamDtls(qlDtls);
						
						cDto.setCategoryToatlScore(String.valueOf((double)Math.round((qnScr+ qlScr)*100)/100.00));
						cDto.setCategoryToatlScoreHO(String.valueOf((double)Math.round((qnScrHo+ qlScrHo)*100)/100.00)); // HO Score
						cDto.setQuantitativeDtls(qnDto);
						cDto.setQualitativeDtls(qlDto);
						
						catLst.add(cDto);  // Adding Category to the list
						
						ctCnt++;
						
						// Resetting all the temp values
						qnDtls = new ArrayList<>(); qlDtls = new ArrayList<>();
						qnScr=0; qlScr=0; qnCnt=1; qlCnt=1; qnScrHo=0; qlScrHo=0;
						//catCodeTemp = catCode;
						
						cDto=new RatingParameterCategoryDto();
						cDto.setCategorySlNo(BankAuditUtil.integerToRomanNumeral(ctCnt));
						cDto.setCategoryCode(catCode);
						if(obj[14]!=null)cDto.setCategoryName(obj[14].toString());
						
						qnDtls = new ArrayList<>();
						qlDtls = new ArrayList<>();
						if("QN".equalsIgnoreCase(qnQl)) {
							pDtls.setSlNo(String.valueOf(qnCnt));qnCnt++;
							qnScr+=pScr;	qnScrHo+=pScrHo;  // Qn HO Score
							qnDtls.add(pDtls);						
						}else if("QL".equalsIgnoreCase(qnQl)) {
							pDtls.setSlNo(String.valueOf(qlCnt));qlCnt++;
							qlScr+=pScr;	qlScrHo+=pScrHo;  // Ql HO Score
							qlDtls.add(pDtls);								
						}
					}	
				}
				
				if(cnt == lst.size()) {
					RatingParameterQnQLDto qnDto= new RatingParameterQnQLDto();
					qnDto.setQnQlName("Quantitative");
					qnDto.setQnQlToatlScore(String.valueOf((double)Math.round((qnScr)*100)/100.00));
					qnDto.setQnQlToatlScoreHO(String.valueOf((double)Math.round((qnScrHo)*100)/100.00));
					qnDto.setParamDtls(qnDtls);
					
					RatingParameterQnQLDto qlDto= new RatingParameterQnQLDto();
					qlDto.setQnQlName("Qualitative");
					qlDto.setQnQlToatlScore(String.valueOf((double)Math.round((qlScr)*100)/100.00));
					qlDto.setQnQlToatlScoreHO(String.valueOf((double)Math.round((qlScrHo)*100)/100.00));
					qlDto.setParamDtls(qlDtls);
					
					cDto.setCategoryToatlScore(String.valueOf((double)Math.round((qnScr+ qlScr)*100)/100.00));
					cDto.setCategoryToatlScoreHO(String.valueOf((double)Math.round((qnScrHo+ qlScrHo)*100)/100.00));
					cDto.setQuantitativeDtls(qnDto);
					cDto.setQualitativeDtls(qlDto);
					
					catLst.add(cDto);  // Adding Category to the list
				}
				catCodeTemp = catCode;cnt++;
			}
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		return catLst; 
	}
	
	/******************************* end, for HO with RO **************************************/
	
	/** To get the Deflated score, after comparing the value in general parameter**/
	public Map getDeflatedScoreForAuditId(int legalEntityCode, String auditId, String rmCode, String userType) {
		logger.info("Inside getDeflatedScoreForAuditId "+ legalEntityCode+" :: "+auditId+" :: "+ rmCode+ " :: "+userType);
		Session session = getSession();
		session.setDefaultReadOnly(true);
		Map<String, String> dflatedMap = null;
		String modelAttendedFlag="N";
		
		String applicability="applicability";
		if("HO".equalsIgnoreCase(userType)) applicability="applicability_ho";
		logger.info("applicability after UserType .."+ applicability);
		
		StringBuilder queryString=new StringBuilder(" "
				+" SELECT DISTINCT "
				+"   (SELECT GROUP_CONCAT(deflator_score) FROM audit_rating_model_overlay o WHERE o."+applicability+"='Y' AND o.level='Parameter' AND o.audit_id=:auditId) AS deflator_score_list, "  // 0 list of all deflator score
				+"   (SELECT g.value FROM general_parameter g where key_1='MODEL_OVERLAY' AND key_2='DEFLATOR_SCORE' AND g.legal_entity_code=:legalEntityCode) as gen_param_deflator_score, " // 1 deflator score from gen_param
				+"   (SELECT count(*) FROM audit_rating_model_overlay o WHERE o."+applicability+" IS NOT NULL AND o.audit_id=:auditId) AS model_attended "  // 2 model overlay attended or not
				+"  FROM audit_rating_parameter_score a "
				+"  WHERE a.audit_id=:auditId AND a.legal_entity_code=:legalEntityCode ");
				
		logger.info("Before the qry...."+ queryString.toString());
		Query query=session.createNativeQuery(queryString.toString())
					.setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId);
		
		dflatedMap =new HashMap<>();
		double deflatorScore=0.0, deflatorScoreGenParam=0.0, deflatorScoreFinal=1.0;
		try {
			Object[] obj = (Object[])query.getSingleResult();
		
			if(obj !=null) {
				int modelAttended=0;
					if(obj[2]!=null) modelAttended= Integer.parseInt(obj[2].toString());
					if(modelAttended >= 1) {
						modelAttendedFlag="Y";
						if(obj[0]!=null) {
							String[] dfScores=obj[0].toString().split(",");
							deflatorScore=1;
							for(String dfScore: dfScores) {
								deflatorScore=deflatorScore * (1 - Double.parseDouble(dfScore));
							}
							if(obj[1]!=null) deflatorScoreGenParam= Double.parseDouble(obj[1].toString());
							if( deflatorScoreGenParam > 0 &&  deflatorScoreGenParam >= deflatorScore) 
								deflatorScoreFinal = deflatorScoreGenParam;
							else deflatorScoreFinal=  deflatorScore;	
						}
					logger.info("deflatorScore .. "+deflatorScore +" deflatorScoreFinal.. "+deflatorScoreFinal); 
				}				
			}
			dflatedMap.put("MODEL_ATTENDED", modelAttendedFlag);
			dflatedMap.put("DEFLATED_SCORE", String.valueOf(deflatorScoreFinal));
			logger.info("dflatedMap .."+dflatedMap);
		}catch(Exception e) {
			e.printStackTrace(System.out);
			dflatedMap.put("MODEL_ATTENDED", "N");
			dflatedMap.put("DEFLATED_SCORE", "1");
		}
		return dflatedMap;
	}
	
	public String updateAuditRatingParamEffectiveScores(Integer legalEntityCode, String auditId, String auditRatingParamStatus){
		Session session =getSession();
		logger.info("Inside updateAuditRatingParamEffectiveScores .. "+ auditId +" :: "+auditRatingParamStatus);
		try{					  		
			String updateQry = "Update AuditRatingParameterScore set scoreEffectiveRO =scoreEffective, scoreEffectiveHO =scoreEffective "
					+ " WHERE auditId=:auditId and legalEntityCode=:legalEntityCode";
			
			int status = session.createQuery(updateQry.toString())	.setParameter("auditId", auditId).setParameter("legalEntityCode", legalEntityCode).executeUpdate();			
			if(status>0){
				return BankAuditConstant.SUCCESS;
			}			
		}catch (Exception e) { 
			logger.error("Error .." + e.getMessage()+" :: "+e.getCause());
		}
		return BankAuditConstant.FAILED;
	}
	
	public String updateAuditRatingParamRemarks(Integer legalEntityCode, String auditId, String auditRatingParamStatus){
		Session session =getSession();
		logger.info("Inside updateAuditRatingParamEffectiveScores .. "+ auditId +" :: "+auditRatingParamStatus);
		try{					  		
			 // "RPRU=>Rating Review RO UnAuth"-> Submitted by RO ||  "RPHU=>Rating Review HO UnAuth"-> Submitted by HO 
			 StringBuilder updateQry =new StringBuilder("Update AuditRatingParameterScore set  ");
			 if("RPRU".equalsIgnoreCase(auditRatingParamStatus))	{
				 updateQry.append(" reviewerRemarks= IFNULL(reviewerRemarks, makerRemarks) ");
			 }else if("RPHU".equalsIgnoreCase(auditRatingParamStatus)){
				 updateQry.append(" hoRemarks = IFNULL(hoRemarks, reviewerRemarks) ");
			 }
			 updateQry.append(" WHERE auditId=:auditId and legalEntityCode=:legalEntityCode");
			
			int status = session.createQuery(updateQry.toString())	.setParameter("auditId", auditId).setParameter("legalEntityCode", legalEntityCode).executeUpdate();			
			if(status>0){
				return BankAuditConstant.SUCCESS;
			}			
		}catch (Exception e) { 
			logger.error("Error .." + e.getMessage()+" :: "+e.getCause());
			e.printStackTrace(System.out);
		}
		return BankAuditConstant.FAILED;
	}
	
	public boolean executeAnUpdateErrorOfApproximation(int legalEntityCode, String auditId, String userType) {
		Session session = getSession();
		boolean execution=true;
		logger.info("Inside ExecuteAnUpdateErrorOfApproximation .. "+legalEntityCode +" :: "+ auditId+ " :: " +userType);
		try {
			/*************************** update weighted Score raw ********************************/
			String scoreEff="score_effective",  weightedScoreRaw="weighted_score_raw_pio",  weightedScore="weighted_score_pio";
			if(userType.equalsIgnoreCase("RO")) {
				scoreEff="score_effective";
				weightedScoreRaw="weighted_score_raw_ro";   // Raw value Param_Score * Param_Weighate
				weightedScore="weighted_score_ro"; 
			}else if(userType.equalsIgnoreCase("HO")) {
				scoreEff="score_effective_ho";
				weightedScoreRaw="weighted_score_raw_ho";
				weightedScore="weighted_score_ho";
			}else {
				scoreEff="score_effective";
				weightedScoreRaw="weighted_score_raw_pio";
				weightedScore="weighted_score_pio";
			}
			logger.info(" score_effective .. "+ scoreEff +" weightedScoreRaw .. "+weightedScoreRaw +" weightedScore .. "+weightedScore  );
			
			StringBuilder sqlQuery= new StringBuilder(" update  audit_rating_parameter_score set "
					+ weightedScoreRaw+" = "+scoreEff+" * param_weightage/100 , " 
					+ weightedScore+" = CAST("+scoreEff+" * param_weightage/100 AS DECIMAL(8,2) )"  +   // + weightedScore+" = ROUND("+scoreEff+" * param_weightage , 2 )"  +
					" where audit_id =:auditId and legal_entity_code=:legalEntityCode ");  
			
			/*if(userType.equalsIgnoreCase("RO")) {
				sqlQuery.append(" weighted_score_raw_ro = score_effective * param_weightage  ");
			}else if(userType.equalsIgnoreCase("HO")) {
				sqlQuery.append(" weighted_score_raw_ho = score_effective_ho * param_weightage  ");
			}else {
				sqlQuery.append(" weighted_score_raw_pio = score_effective * param_weightage  ");
			}
			sqlQuery.append(" where audit_id =:auditId and legal_entity_code=:legalEntityCode ");*/
	   		 
			logger.info("Updated sqlQuery .."+ sqlQuery );
			session.createNativeQuery(sqlQuery.toString()).setParameter("auditId", auditId).setParameter("legalEntityCode", legalEntityCode)	
				.executeUpdate();
			/******************** End of update weighted Score raw  **************************/
			
			/******************* Logic to rectify Errors of Approximation  **************************/
			/* To find the After param overlay score */
			logger.info(" To get the diff between SUM(Raw Weighted Parametsers scores) and SUM(Rounded Weighted Parameter Scores) .. "); 
			double diffChk=0l, diffChkTmp=0l, diffChkTmp0=0l; int cnt=0; int mul=100;
			try{
				StringBuilder diffRatingScore=new StringBuilder(" SELECT "
				//+ "(CAST(SUM("+scoreEff+"*param_weightage) AS DECIMAL(8,2)) - SUM(CAST("+scoreEff+"*param_weightage AS DECIMAL(8,2))) ) AS diff "
				+ " CAST((CAST(SUM("+weightedScoreRaw+") AS DECIMAL(8,2)) - SUM(CAST("+weightedScore+" AS DECIMAL(8,2))) ) AS DECIMAL (8,2) ) AS diff "
				+ " FROM audit_rating_parameter_score s WHERE audit_id=:auditId AND legal_entity_code=:legalEntityCode ");
				logger.info("Before the qry...."+ diffRatingScore.toString());
				Query qry2=session.createNativeQuery(diffRatingScore.toString())
					.setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId);
				BigDecimal diffChkBD = (BigDecimal)qry2.getSingleResult();
				
				if(diffChkBD!=null) { diffChkTmp0 = diffChkBD.doubleValue()*mul;
									  diffChk = (double)Math.round(diffChkTmp0*100)/100.00;
				}
				
				logger.info("ROUND of SUM(of All Parameter)- SUM of ROUND(of Individual Parameter)  ==> "+diffChkTmp0 + " :: "+ diffChk);
			}catch(Exception e) {
				e.printStackTrace(System.out);
			}
			/* end here*/
			/******************* **************************/
			
			/****************** Get all the parameter records and Sort them *********************/
			logger.info(" Getting all parameters ");
			List<AuditRatingParameterScore> parmLst = session.createQuery(" FROM AuditRatingParameterScore arps "
					+ " WHERE auditId=:auditId AND legalEntityCode=:legalEntityCode")
					.setParameter("auditId", auditId).setParameter("legalEntityCode", legalEntityCode).list();	
			
			List<AuditRatingParameterScore> parmLstUpdate= null;
			// If diffChk > 0, STEP1: organize weighted scores in ascending order and STEP2: Add a unit to the lowest value sequentially till diff becomes zero
			// If diffChk < 0, STEP1: organize weighted scores in descending order and STEP2: Deduct a unit from the highest value sequentially till diff becomes zero
			
			parmLstUpdate= new ArrayList<>();
			HashMap< String, String> map= new HashMap<>();
			List<String> paramLst = new ArrayList<String>();
			diffChkTmp = diffChk;
			
			if(userType.equalsIgnoreCase("RO")) {
				if(diffChk > 0) {				
					parmLstUpdate=parmLst.stream().sorted(Comparator.comparing(AuditRatingParameterScore::getWeightedScoreRO)).collect(Collectors.toList());  // STEP 1
					for(AuditRatingParameterScore scr: parmLstUpdate) { // STEP 2
						String decimalCnt= (String.valueOf(scr.getWeightedScoreRawRO())).split("\\.")[1];  // Logic to Check which Parameters were rounded.
						if(decimalCnt !=null && decimalCnt.length() >2 ) {  // After decimal if more than 2 numbers there, then it can be used for Approximation
							paramLst.add(scr.getParamCode());  // Collecting parameters for Approximation
							diffChk-=0.01*mul;
						}
						if(diffChk == 0) break;
					}
				}else if(diffChk < 0) {				
					parmLstUpdate=parmLst.stream().sorted(Comparator.comparing(AuditRatingParameterScore::getWeightedScoreRO).reversed()).collect(Collectors.toList());  // STEP 1
					for(AuditRatingParameterScore scr: parmLstUpdate) { // STEP 2
						String decimalCnt= (String.valueOf(scr.getWeightedScoreRawRO())).split("\\.")[1];  // Logic to Check which Parameters were rounded.
						if(decimalCnt !=null && decimalCnt.length() >2 ) {  // After decimal if more than 2 numbers there, then it can be used for Approximation
							paramLst.add(scr.getParamCode());  // Collecting parameters for Approximation
							diffChk+=0.01*mul;
						}
						if(diffChk == 0) break;
					}
				}
			}else if(userType.equalsIgnoreCase("HO")) {
				if(diffChk > 0) {				
					parmLstUpdate=parmLst.stream().sorted(Comparator.comparing(AuditRatingParameterScore::getWeightedScoreHO)).collect(Collectors.toList());  // STEP 1
					for(AuditRatingParameterScore scr: parmLstUpdate) { // STEP 2
						String decimalCnt= (String.valueOf(scr.getWeightedScoreRawHO())).split("\\.")[1];  // Logic to Check which Parameters were rounded.
						if(decimalCnt !=null && decimalCnt.length() >2 ) {  // After decimal if more than 2 numbers there, then it can be used for Approximation
							paramLst.add(scr.getParamCode());  // Collecting parameters for Approximation
							diffChk-=0.01*mul;
						}
						if(diffChk == 0) break;
					}
				}else if(diffChk < 0) {				
					parmLstUpdate=parmLst.stream().sorted(Comparator.comparing(AuditRatingParameterScore::getWeightedScoreHO).reversed()).collect(Collectors.toList());  // STEP 1
					for(AuditRatingParameterScore scr: parmLstUpdate) { // STEP 2
						String decimalCnt= (String.valueOf(scr.getWeightedScoreRawHO())).split("\\.")[1];  // Logic to Check which Parameters were rounded.
						if(decimalCnt !=null && decimalCnt.length() >2 ) {  // After decimal if more than 2 numbers there, then it can be used for Approximation
							paramLst.add(scr.getParamCode());  // Collecting parameters for Approximation
							diffChk+=0.01*mul;
						}
						if(diffChk == 0) break;
					}
				}
			}else {
				if(diffChk > 0) {				
					parmLstUpdate=parmLst.stream().sorted(Comparator.comparing(AuditRatingParameterScore::getWeightedScorePio)).collect(Collectors.toList());  // STEP 1
					for(AuditRatingParameterScore scr: parmLstUpdate) { // STEP 2
						String decimalCnt= (String.valueOf(scr.getWeightedScoreRawPio())).split("\\.")[1];  // Logic to Check which Parameters were rounded.
						if(decimalCnt !=null && decimalCnt.length() >2 ) {  // After decimal if more than 2 numbers there, then it can be used for Approximation
							paramLst.add(scr.getParamCode());  // Collecting parameters for Approximation
							diffChk-=0.01*mul;
						}
						if(diffChk == 0) break;
					}
				}else if(diffChk < 0) {				
					parmLstUpdate=parmLst.stream().sorted(Comparator.comparing(AuditRatingParameterScore::getWeightedScorePio).reversed()).collect(Collectors.toList());  // STEP 1
					for(AuditRatingParameterScore scr: parmLstUpdate) { // STEP 2
						String decimalCnt= (String.valueOf(scr.getWeightedScoreRawPio())).split("\\.")[1];  // Logic to Check which Parameters were rounded.
						if(decimalCnt !=null && decimalCnt.length() >2 ) {  // After decimal if more than 2 numbers there, then it can be used for Approximation
							paramLst.add(scr.getParamCode());  // Collecting parameters for Approximation
							diffChk+=0.01*mul;
						}
						if(diffChk == 0) break;
					}
				}
			}
			logger.info("paramLst Size .. "+ paramLst.size());
			/***************** *****************/
			/************************ Updating the Weighted score ******************************/
			if(diffChkTmp != 0 && paramLst.size() > 0) {
				logger.info("Inside diffChkTmp != 0 :: "+ diffChkTmp );
				StringBuilder sqlUpdateQuery= new StringBuilder(" UPDATE audit_rating_parameter_score SET ");
				if(diffChkTmp > 0) sqlUpdateQuery.append(" "+ weightedScore+" = "+weightedScore+" +  0.01 ");
				else if(diffChkTmp < 0) sqlUpdateQuery.append(" "+ weightedScore+" = "+weightedScore+" -  0.01 ");
				sqlUpdateQuery.append(	" WHERE audit_id =:auditId AND legal_entity_code=:legalEntityCode AND param_code in (:paramLst) ");  
				
				logger.info("sqlUpdateQuery sqlQuery .."+ sqlUpdateQuery );
				int update=session.createNativeQuery(sqlUpdateQuery.toString()).setParameter("auditId", auditId)
					.setParameter("legalEntityCode", legalEntityCode).setParameterList("paramLst", paramLst)
					.executeUpdate();
				logger.info("update Approx Params.."+ update);
			}
			
			// Updating the weighted_score_effective after PIO and RO, so same field can use for displaying the Widget 1,3 and 4 values.
			if(!userType.equalsIgnoreCase("HO")) {
				int update=session.createNativeQuery("UPDATE audit_rating_parameter_score SET weighted_score_effective= "+weightedScore +" "
						 +" WHERE audit_id =:auditId AND legal_entity_code=:legalEntityCode ")
						.setParameter("auditId", auditId).setParameter("legalEntityCode", legalEntityCode).executeUpdate();
				logger.info("update weighted_score_effective .."+ update);
			}
		}catch(Exception e) {
			e.printStackTrace(System.out);
			execution=false;
		}return execution;
		
	}
	
	@Override
	public String getRatingFromScore(Integer legalEntityCode, String auditId, String rmCode, String ratingScr ){
		Session session =getSession();
		logger.info("Inside getRatingFromScore .. "+ auditId +" :: "+rmCode+" :: "+ratingScr);
		String rating=null;
		try{					  		
			StringBuilder queryAftrOvrRating=new StringBuilder(" SELECT g.grade_code, g.max_per FROM maint_rating_grade g "						
					+ " WHERE g.legal_entity_code=:legalEntityCode AND  g.rm_code =:rmCode AND :finalWeightedTotalNormalized  >= g.min_per AND :finalWeightedTotalNormalized < g.max_per ");
					logger.info("Before the qry of Rating ...."+ queryAftrOvrRating.toString());
					Query qry2=session.createNativeQuery(queryAftrOvrRating.toString())
						.setParameter("legalEntityCode", legalEntityCode).setParameter("finalWeightedTotalNormalized", ratingScr).setParameter("rmCode", rmCode);
					Object[] obj1 = (Object[])qry2.getSingleResult();
					
					if(obj1 !=null) {
						if(obj1[0] !=null) { // auditSummaryDto.setTotalweightedScore(obj1[0].toString());
							rating= obj1[0].toString();
							logger.info("rating .."+rating);
						}
					}		
		}catch (Exception e) { 
			logger.error("Error .." + e.getMessage()+" :: "+e.getCause());
			e.printStackTrace(System.out);
		}
		return rating;
	}

	@Override
	public Map<String, String> getRatingFromDeflatorScore(Integer legalEntityCode, String auditId, String rmCode, String ratingScr, String userType ){
		Session session =getSession();
		logger.info("Inside getRatingFromDeflatorScore .. "+ auditId +" :: "+rmCode+" :: "+ratingScr+" :: "+userType);
		String applicability="applicability";
		if("HO".equalsIgnoreCase(userType)) applicability= "applicability_ho";
		Map map=null;
		try{					  		
			StringBuilder queryDefRatingScore=new StringBuilder(" SELECT d.audit_id, d.def_applied, m.grade_code, m.max_per FROM "
					+ "  (SELECT COUNT(*) AS def_applied, audit_id FROM audit_rating_model_overlay WHERE LEVEL='MODEL' AND audit_id=:auditId AND "+applicability+" IS NOT NULL ) d "
					+ "  LEFT JOIN "
					+ "  (SELECT g.grade_code, g.max_per,o.audit_id FROM audit_rating_model_overlay o, maint_rating_grade g "						
					+ "     WHERE o.level='MODEL' AND o."+applicability+"='Y' AND g.rm_code=o.rm_code AND o.best_rating=g.grade_code AND "
					+ "  	  g.legal_entity_code=:legalEntityCode AND g.rm_code =:rmCode AND o.audit_id=:auditId ORDER BY max_per LIMIT 1) m "
					+ "  ON m.audit_id=d.audit_id ");
					logger.info("Before the qry...."+ queryDefRatingScore.toString());
					Query qry2=session.createNativeQuery(queryDefRatingScore.toString())
						.setParameter("legalEntityCode", legalEntityCode).setParameter("rmCode", rmCode).setParameter("auditId", auditId);
					Object[] obj = (Object[])qry2.getSingleResult();
					
					map = new HashMap<>();
					map.put("DEFLATOR_APPLIED", "N");
					if(obj !=null) {
						if(obj[1] !=null && Integer.parseInt(obj[1].toString()) > 0) map.put("DEFLATOR_APPLIED", "Y");
						if(obj[2] !=null) map.put("DEFLATOR_RATING", obj[2].toString());
						if(obj[3] !=null) map.put("DEFLATOR_MAX_PER_SCORE", obj[3].toString());
					}	
					logger.info("Def score .."+ map);
		}catch (Exception e) { 
			logger.error("Error .." + e.getMessage()+" :: "+e.getCause());
			e.printStackTrace(System.out);
		}
		return map;
	}
}
