package com.bankaudit.rest.process.dao;


import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
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.process.AuditRatingComplianceScoreDto;
import com.bankaudit.dto.process.RatingParameterCategoryDto;
import com.bankaudit.process.model.AuditRatingComplianceObsScore;
import com.bankaudit.service.BusinessDateService;
import com.bankaudit.service.MaintEntityService;

/*MaintEntityDataEffect */
@Repository("auditRatingComplianceScoreDao")
public class AuditRatingComplianceScoreDaoImpl extends AbstractDao implements AuditRatingComplianceScoreDao{
	
	@Autowired
	BusinessDateService businessDateService;
		
	@Autowired
	AuditScheduleDao auditScheduleDao;
	
	@Autowired
	MaintEntityService maintEntityService;
	
	@Lazy
	@Autowired
	AuditRatingComplianceScoreDao auditRatingComplianceScoreDao; 
	
		
	static final Logger logger = Logger.getLogger(AuditRatingComplianceScoreDaoImpl.class);
	
	//Verify data present in Compliance Observation Score table
	@Override
	public boolean verifyDataPresentForComplianceObsScore(int legalEntityCode, String auditId) {
		Session session = getSession();		
		boolean isCompObsDataPresent=false;
		logger.info("Inside verifyDataPresentForComplianceObsScore .. "+ legalEntityCode +" auditId .. "+auditId);
		try{
			List<AuditRatingComplianceObsScore> auditPScore= session.createQuery(" FROM AuditRatingComplianceObsScore aps WHERE auditId=:auditId AND legalEntityCode=:legalEntityCode ")
					.setParameter("auditId", auditId).setParameter("legalEntityCode", legalEntityCode).list();		
			if(!auditPScore.isEmpty()) isCompObsDataPresent=true;
			logger.info("isCompObsDataPresent .. "+ isCompObsDataPresent);
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}return isCompObsDataPresent;
	}
	
	
	// Inserting data into "audit_rating_compliance_obs_score"
	@Override
	public boolean createAndGetAuditRatingComplianceScore(int legalEntityCode,String auditId, String auditTypeCode, String unitId, String userId) {
		Session session = getSession();
		logger.info("Inside createAndGetAuditRatingComplianceScore:: "+legalEntityCode+" :: "+auditId+ " :: "+unitId+" :: "+auditTypeCode+" :: "+userId);
		boolean created=false;
		try {
			StringBuilder qryParamr=new StringBuilder(" "
					+" SELECT o.observation_id, o.observation_criticality, o.finding_remarks, s.standard_stmnt_non_compliance,  " // 3
					+"		(SELECT c.comment FROM audit_observation_respond_review_capture c WHERE c.audit_id=o.audit_id AND capture_type in ('G3','G31') "   // Comments from Bank Maker(G3) and Checker(G31)
					+" 			AND c.observation_id=o.observation_id ORDER BY maker_timestamp DESC LIMIT 1) AS compliance_comment " // 4
					+" FROM audit_observation o  "
					+"  LEFT JOIN maint_report_standard_stmt s ON o.audit_type_code=s.audit_type_code AND o.mapping_id=s.mapping_id "
					+" WHERE o.legal_entity_code=:legalEntityCode AND o.audit_id=:auditId AND "
					//+"  	o.audit_type_code=:auditTypeCode AND "
					+"		o.compliance_category='N'");
			
			List qrylst=session.createNativeQuery(qryParamr.toString()).setParameter("auditId", auditId)
					.setParameter("legalEntityCode", legalEntityCode).list();	// .setParameter("auditTypeCode", auditTypeCode)		
			
			int i=0;
			for ( Iterator iter = qrylst.iterator(); iter.hasNext(); ) {
				String obs_remarks="";
				Object[] obj = (Object[]) iter.next(); i++;				
				AuditRatingComplianceObsScore acs= new AuditRatingComplianceObsScore();
				acs.setLegalEntityCode(legalEntityCode);
				acs.setAuditId(auditId);
				acs.setUnitId(unitId);
				acs.setAuditTypeCode(auditTypeCode);
				acs.setMaker(userId);
				acs.setMakerTimestamp(new Timestamp(System.currentTimeMillis()));
				if(obj[0] !=null) acs.setObservationId(obj[0].toString());
				if(obj[1] !=null) acs.setObservationCriticality(obj[1].toString());
				if(obj[3] !=null) obs_remarks=obj[3].toString()+"\n";  // Non Compliance Standard statement
				if(obj[2] !=null) obs_remarks+= obj[2].toString();	// IO Remarks
				if(obj[4] !=null) acs.setCompliance(obj[4].toString());
				acs.setObservation(obs_remarks);
				acs.setEntityStatus("A");
				acs.setStatus("U");					
				auditRatingComplianceScoreDao.saveOrUpdate(acs);		
				auditRatingComplianceScoreDao.flushSession();
			}logger.info(" Compliance scores Inserted .. "+i +" times");	
			created=true;
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}return created;
	}
	
	@Override
	public List<AuditRatingComplianceObsScore> getComplianceObsScoreByAuditId(int legalEntityCode, String auditId, String auditTypeCode, String userType) {
		Session session = getSession();
		session.setDefaultReadOnly(true);
		
		List<AuditRatingComplianceObsScore> auditRatingCompObsScoreLst = null;
		logger.info("Inside getComplianceObsScoreByAuditId .. "+ legalEntityCode +" auditId .. "+auditId +" auditTypeCode .."+auditTypeCode+" userType .."+userType);
		try {
			StringBuilder queryString=new StringBuilder(" SELECT arcs.observation_id, arcs.unit_id, arcs.observation, arcs.compliance, " // 3
					+ "  arcs.desk_assessed_score, arcs.desk_effective_score, arcs.field_assessed_score, arcs.field_effective_score, "   // 7
					+ "  arcs.reviewer_maker_remarks, arcs.reviewer_checker_remarks, arcs.auditor_maker_remarks, arcs.auditor_checker_remarks, "  // 11
					+ "  arcs.entity_status, arcs.status, arcs.maker, arcs.checker, " // 15
					+ "  arcs.maker_timestamp, arcs.checker_timestamp, arcs.businessdate_timestamp, arcs.sysdate_timestamp, " // 19
					+ "  o.review_status_attribute ,  " // 20
					+ "  (SELECT description FROM general_parameter g WHERE g.key_1 = 'STATUS_REVIEW' AND g.key_2='REVIEW_ATTRIBUTE'  "
					+ " 	AND g.value=o.review_status_attribute AND g.legal_entity_code=o.legal_entity_code) AS review_status_desc,  " // 21
					+ " 	(SELECT c.comment FROM audit_observation_respond_review_capture c WHERE c.audit_id=o.audit_id AND capture_type IN ('G4','G7','G71')  "
					+ " 			AND c.observation_id=o.observation_id ORDER BY maker_timestamp DESC LIMIT 1) AS reviewer_comment  "  // 22
					+ "  FROM audit_rating_compliance_obs_score arcs, audit_observation o   "
					+ "  WHERE arcs.legal_entity_code =o.legal_entity_code AND arcs.audit_id=o.audit_id AND arcs.observation_id=o.observation_id  "
					+ "  AND o.compliance_category='N' AND arcs.audit_id=:auditId AND arcs.legal_entity_code=:legalEntityCode ORDER BY o.observation_id ");
					
			logger.info("Before the qry....");
			
			List qrylst=session.createNativeQuery(queryString.toString()).setParameter("auditId", auditId)
					.setParameter("legalEntityCode", legalEntityCode).list();
			
			int i=0;
			auditRatingCompObsScoreLst = new ArrayList<AuditRatingComplianceObsScore>();
			for ( Iterator iter = qrylst.iterator(); iter.hasNext(); ) {
				Object[] obj = (Object[]) iter.next(); i++;				
				AuditRatingComplianceObsScore acs= new AuditRatingComplianceObsScore();
				acs.setLegalEntityCode(legalEntityCode);
				acs.setAuditId(auditId);
				acs.setAuditTypeCode(auditTypeCode);
				
				if(obj[0] !=null) acs.setObservationId(obj[0].toString());
				if(obj[1] !=null) acs.setUnitId(obj[1].toString());
				if(obj[2] !=null) acs.setObservation(obj[2].toString());
				if(obj[3] !=null) acs.setCompliance(obj[3].toString());
				if(obj[4] !=null) acs.setDeskAssessedScore(obj[4].toString());
				if(obj[5] !=null) acs.setDeskEffectiveScore(obj[5].toString());
				if(obj[6] !=null) acs.setFieldAssessedScore(obj[6].toString());
				if(obj[7] !=null) acs.setFieldEffectiveScore(obj[7].toString());
				if(obj[8] !=null) acs.setReviewerMakerRemarks(obj[8].toString());
				if(obj[9] !=null) acs.setReviewerCheckerRemarks(obj[9].toString());
				if(obj[10] !=null) acs.setAuditorMakerRemarks(obj[10].toString());
				if(obj[11] !=null) acs.setAuditorCheckerRemarks(obj[11].toString());
				if(obj[12] !=null) acs.setEntityStatus(obj[12].toString());
				if(obj[13] !=null) acs.setStatus(obj[13].toString());
				if(obj[14] !=null) acs.setMaker(obj[14].toString());
				if(obj[15] !=null) acs.setChecker(obj[15].toString());
				if(obj[16] !=null) acs.setMakerTimestamp((Date)obj[16]);
				if(obj[20] !=null) acs.setReviewStatus(obj[20].toString());
				if(obj[21] !=null) acs.setReviewStatusDesc(obj[21].toString());
				if(obj[22] !=null) acs.setReviewComments(obj[22].toString());				
				auditRatingCompObsScoreLst.add(acs);
				
			}logger.info(" Comp records .. "+i +" Lst:: " + auditRatingCompObsScoreLst);	
			
			/*StringBuilder queryString=new StringBuilder("SELECT arcs, o.reviewStatusAttribute, "   	// 1
					+ " (SELECT description FROM GeneralParameter g WHERE g.key1 = 'STATUS_REVIEW' AND g.key2='REVIEW_ATTRIBUTE' AND g.value=o.reviewStatusAttribute AND g.legalEntityCode=o.legalEntityCode) as review_status_desc, "  // 2
					+ "	(SELECT c.comment FROM AuditObservationRespondReviewCapture c WHERE c.auditId=o.auditId AND captureType in ('G4','G7','G71') "   // Comments from Scrutinizer(G4),  Lead Scrutinizer(G7) and  Approver(G71)
					+ " 			AND c.observationId=o.observationId ORDER BY makerTimestamp DESC LIMIT 1) AS reviewer_comment " // 3
					+ " from AuditRatingComplianceObsScore arcs, AuditObservation o  "
					+ " where arcs.legalEntityCode =o.legalEntityCode AND arcs.auditId=o.auditId AND arcs.observationId=o.observationId "
					+ "  AND arcs.auditId=:auditId AND arcs.legalEntityCode=:legalEntityCode ORDER BY o.observationId "); 
			logger.info("Before the qry....");
			
			Query query=(Query) session.createQuery(queryString.toString())				
					.setResultTransformer(new ResultTransformer() {
						@Override
						public Object transformTuple(Object[] tuple, String[] aliases) {
							AuditRatingComplianceObsScore auditRatingComplianceObsScore=null;
							if(tuple[0]!=null){
								auditRatingComplianceObsScore=(AuditRatingComplianceObsScore)tuple[0];
								if(tuple[1]!=null) auditRatingComplianceObsScore.setReviewStatus(tuple[1].toString());
								if(tuple[2]!=null) auditRatingComplianceObsScore.setReviewStatusDesc(tuple[2].toString());
								if(tuple[3]!=null) auditRatingComplianceObsScore.setReviewComments(tuple[3].toString());
							}
							return auditRatingComplianceObsScore;
						}
				@Override
				public List transformList(List collection) {
					return collection;
				}
			}).setParameter("legalEntityCode",legalEntityCode)
			  .setParameter("auditId",auditId);
			
			auditRatingCompObsScoreLst=query.list();*/

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

	// Updating the modified Compliance obs score
	@Override
	public void updateAuditComplianceObsScores(List<AuditRatingComplianceObsScore> auditRatingComplianceObsScores, int legalEntityCode, String auditId, String userType) {
		Session session = getSession();
		
		logger.info("Inside updateAuditComplianceObsScores .. .. "+legalEntityCode +" :: "+ auditId+ " :: " +userType);
		for(AuditRatingComplianceObsScore auditRatingComplianceObsScore : auditRatingComplianceObsScores) {
			if("M".equalsIgnoreCase(auditRatingComplianceObsScore.getStatus())) { 
				auditRatingComplianceObsScore.setStatus(BankAuditConstant.STATUS_UNAUTH);
				auditRatingComplianceScoreDao.flushSession();
				auditRatingComplianceScoreDao.saveOrUpdate(auditRatingComplianceObsScore);
			}
		}
	}
	
	@Override
	public AuditRatingComplianceScoreDto getAuditRatingComplianceScores(int legalEntityCode, String auditId, String ratingType, String userType) {
		logger.info("Inside getAuditRatingComplianceScores "+ auditId +" :: "+ratingType+" :: "+ userType);
		Session session = getSession();
		session.setDefaultReadOnly(true);
		
		AuditRatingComplianceScoreDto scoreDto = null;
		String scoreType="desk_effective_score";
		if("F".equalsIgnoreCase(ratingType)) scoreType="field_effective_score";
		logger.info("Score Type as .."+ scoreType);
		
		// To check if Non compliance records present for the Inspection/Audit, If no NC records set the highest Mark and Rating
		Long ncRecords=(Long)session.createQuery(" SELECT count(*) FROM AuditRatingComplianceObsScore "
				+ " WHERE legalEntityCode =:legalEntityCode AND audit_id=:auditId ")
		.setParameter("legalEntityCode", legalEntityCode)
		.setParameter("auditId", auditId).uniqueResult();
		logger.info("No os Non Compliance records .."+ncRecords);
		if(ncRecords==0) { // If no Non compliance records set the mark as 100 and highest rating
			scoreDto= new AuditRatingComplianceScoreDto();
			String maxGrade= getRatingFromScore(legalEntityCode, auditId, "COMPLIANCE", "ALL", String.valueOf(100) );
			logger.info("maxGrade .. "+maxGrade);
			scoreDto.setAuditId(auditId);
			scoreDto.setFieldScore(String.valueOf(100));
			scoreDto.setFieldRating(maxGrade);
			scoreDto.setDeskScore(String.valueOf(100));
			scoreDto.setDeskRating(maxGrade);
			return scoreDto;
		}// if No NC records, end here
		
		
		// Applicable Number of observations = Total number of observations  - No. of observations marked as NA  
		StringBuilder queryString=new StringBuilder(" SELECT observation_criticality, COUNT(*) as total_obs, CAST(SUM("+scoreType+")as signed) as total_score "  // --0-2
				+ "   FROM audit_rating_compliance_obs_score  "
				+ "   WHERE legal_entity_code=:legalEntityCode AND audit_id=:auditId AND "+scoreType+" <> 'NA' GROUP BY observation_criticality ");
			
		Query query=(Query) session.createNativeQuery(queryString.toString()).setParameter("legalEntityCode", legalEntityCode)
		  .setParameter("auditId", auditId);
		
		List lst=query.list();
		try {
			String obsCrtly=null, ratingGrade=null;
			int totalCnt=0, mjrCnt=0, mnrCnt=0, nCnt=0;  // here nCnt=> Normal count is which is not Major and Minor 
			double totalScore=0, totalMjrScore=0, totalMnrScore=0, totalNScore=0;  // totalNScore=> total Normal Score
			double finalTotal=0;
			
			RatingParameterCategoryDto cDto=null;
			for ( Iterator iter = lst.iterator(); iter.hasNext(); ) {
				Object[] obj =(Object[])iter.next();
				obsCrtly= obj[0]!=null?obj[0].toString():"" ;
				logger.info("obsCrtly:::::::::::::::::::::::::::::::::::::::"+obsCrtly);
				if("Major".equalsIgnoreCase(obsCrtly)) {
					if(obj[1]!=null) mjrCnt= Integer.parseInt(obj[1].toString());
					if(obj[2]!=null) totalMjrScore+= Double.parseDouble(obj[2].toString());
				}else if("Minor".equalsIgnoreCase(obsCrtly)) {
					if(obj[1]!=null) mnrCnt= Integer.parseInt(obj[1].toString());
					if(obj[2]!=null) totalMnrScore+= Double.parseDouble(obj[2].toString());
				}else {
					if(obj[1]!=null) nCnt= Integer.parseInt(obj[1].toString());
					if(obj[2]!=null) totalNScore+= Double.parseDouble(obj[2].toString());
				}
			}
			logger.info("Cnt :: "+mjrCnt+" :: "+mnrCnt +" :: "+nCnt +" :: "+totalMjrScore+" :: "+ totalMnrScore+" :: "+totalNScore);
			totalCnt =mjrCnt+mnrCnt+nCnt;
			totalScore= totalMjrScore+ totalMnrScore + totalNScore;
			if(totalCnt>0) finalTotal=(double)Math.round((totalScore/(totalCnt*5)*100)*100)/100.00;
			ratingGrade= getRatingFromScore(legalEntityCode, auditId, "COMPLIANCE", "ALL", String.valueOf(finalTotal) );
			
			logger.info("totalCnt ::"+totalCnt+" :: "+totalScore+" :: "+finalTotal+" :: "+ratingGrade);
				
			scoreDto= new AuditRatingComplianceScoreDto();
			scoreDto.setAuditId(auditId);
			if("F".equalsIgnoreCase(ratingType)) {
				scoreDto.setFieldScore(String.valueOf(finalTotal));
				scoreDto.setFieldRating(ratingGrade);
				scoreDto.setFieldTotalObs(String.valueOf(totalCnt));
				scoreDto.setFieldTotalMarks(String.valueOf(totalScore));
				scoreDto.setFieldMaxMarks(String.valueOf(totalCnt*5));
			}else {
				scoreDto.setDeskScore(String.valueOf(finalTotal));
				scoreDto.setDeskRating(ratingGrade);
				scoreDto.setDeskTotalObs(String.valueOf(totalCnt));
				scoreDto.setDeskTotalMarks(String.valueOf(totalScore));
				scoreDto.setDeskMaxMarks(String.valueOf(totalCnt*5));
			}	
			
				
			logger.info("ComplianceScoreDto .... "+scoreDto);
			}catch(Exception e) {
				e.printStackTrace(System.out);
			}
		return scoreDto;
	}
	
	@Override
	public String getRatingFromScore(Integer legalEntityCode, String auditId, String ratingName, String ratingSubType, String ratingScr ){
		Session session =getSession();
		logger.info("Inside getRatingFromScore AuditRatingCompliancescoreDaoImpl .. "+ auditId +" :: "+ratingName+" :: "+ratingSubType +" :: "+ratingScr);
		String rating=null;
		try{					  		
			StringBuilder queryRating=new StringBuilder(" SELECT g.grade_code, g.max_per FROM maint_rating_score_grade g "						
					+ " WHERE g.legal_entity_code=:legalEntityCode AND g.rating_name =:ratingName AND g.rating_sub_type =:ratingSubType "
					+ "    AND :ratingScr  >= g.min_per AND :ratingScr < g.max_per ");
					logger.info("Before the qry of Rating ...."+ queryRating.toString());
					Query qry2=session.createNativeQuery(queryRating.toString())
						.setParameter("legalEntityCode", legalEntityCode).setParameter("ratingScr", ratingScr).setParameter("ratingName", ratingName)
						.setParameter("ratingSubType", ratingSubType);
					Object[] obj1 = (Object[])qry2.getSingleResult();
					
					if(obj1 !=null) {
						if(obj1[0] !=null) { 
							rating= obj1[0].toString();
							logger.info("ratingGrade .."+rating);
						}
					}		
		}catch (Exception e) { 
			logger.error("Error .." + e.getMessage()+" :: "+e.getCause());
			e.printStackTrace(System.out);
		}
		return rating;
	}	
	
	@Override
	public List<AuditRatingComplianceObsScore> getComplianceObsScoreReportByAuditId(int legalEntityCode,String auditId, String userType, String compRatingStatus) {
		Session session = getSession();
		session.setDefaultReadOnly(true);
		
		List<AuditRatingComplianceObsScore> auditRatingCompObsScoreLst = null;
		logger.info("Inside getComplianceObsScoreReportByAuditId .. "+ legalEntityCode +" auditId .. "+auditId +" userType .."+userType+" compRatingStatus .."+compRatingStatus);
		try {
			StringBuilder queryString=new StringBuilder(" SELECT arcs.observation_id, arcs.unit_id, arcs.observation, arcs.compliance, " // 3
					+ "  arcs.desk_assessed_score, arcs.desk_effective_score, arcs.field_assessed_score, arcs.field_effective_score, "   // 7
					+ "  arcs.reviewer_maker_remarks, arcs.reviewer_checker_remarks, arcs.auditor_maker_remarks, arcs.auditor_checker_remarks, "  // 11
					+ "  arcs.entity_status, arcs.status, arcs.maker, arcs.checker, " // 15
					+ "  arcs.maker_timestamp, arcs.checker_timestamp, arcs.businessdate_timestamp, arcs.sysdate_timestamp, " // 19
					+ "  o.review_status_attribute ,  " // 20
					//+ "  (SELECT description FROM general_parameter g WHERE g.key_1 = 'STATUS_REVIEW' AND g.key_2='REVIEW_ATTRIBUTE'  "
					//+ " 	AND g.value=o.review_status_attribute AND g.legal_entity_code=o.legal_entity_code) AS review_status_desc,  " // 21
					//+ " 	(SELECT c.comment FROM audit_observation_respond_review_capture c WHERE c.audit_id=o.audit_id AND capture_type IN ('G4','G7','G71')  "
					//+ " 			AND c.observation_id=o.observation_id ORDER BY maker_timestamp DESC LIMIT 1) AS reviewer_comment  "  // 22
					+ "  '' as review_status_desc, '' as reviewer_comment, " // 21, 22
					+ "	 s.standard_stmnt_non_compliance "  // 23
					+ "  FROM audit_rating_compliance_obs_score arcs, audit_observation o "
					+ "     LEFT JOIN maint_report_standard_stmt s ON o.mapping_id=s.mapping_id AND o.legal_entity_code=s.legal_entity_code AND o.audit_type_code=s.audit_type_code "
					+ "  WHERE arcs.legal_entity_code =o.legal_entity_code AND arcs.audit_id=o.audit_id AND arcs.observation_id=o.observation_id  "
					+ "  AND o.compliance_category='N' AND arcs.audit_id=:auditId AND arcs.legal_entity_code=:legalEntityCode ORDER BY o.observation_id ");
					
			logger.info("Before the qry....");
			
			List qrylst=session.createNativeQuery(queryString.toString()).setParameter("auditId", auditId)
					.setParameter("legalEntityCode", legalEntityCode).list();
			
			int i=0;
			auditRatingCompObsScoreLst = new ArrayList<AuditRatingComplianceObsScore>();
			for ( Iterator iter = qrylst.iterator(); iter.hasNext(); ) {
				Object[] obj = (Object[]) iter.next(); i++;				
				AuditRatingComplianceObsScore acs= new AuditRatingComplianceObsScore();
				acs.setLegalEntityCode(legalEntityCode);
				acs.setAuditId(auditId);
				
				if(obj[0] !=null) acs.setObservationId(obj[0].toString());
				if(obj[1] !=null) acs.setUnitId(obj[1].toString());
				if(obj[2] !=null) acs.setObservation(obj[2].toString());
				if(obj[3] !=null) acs.setCompliance(obj[3].toString());
				if(obj[4] !=null) acs.setDeskAssessedScore(obj[4].toString());
				if(obj[5] !=null) acs.setDeskEffectiveScore(obj[5].toString());
				if(obj[6] !=null) acs.setFieldAssessedScore(obj[6].toString());
				if(obj[7] !=null) acs.setFieldEffectiveScore(obj[7].toString());
				if(obj[8] !=null) acs.setReviewerMakerRemarks(obj[8].toString());
				if(obj[9] !=null) acs.setReviewerCheckerRemarks(obj[9].toString());
				if(obj[10] !=null) acs.setAuditorMakerRemarks(obj[10].toString());
				if(obj[11] !=null) acs.setAuditorCheckerRemarks(obj[11].toString());
				if(obj[12] !=null) acs.setEntityStatus(obj[12].toString());
				if(obj[13] !=null) acs.setStatus(obj[13].toString());
				if(obj[14] !=null) acs.setMaker(obj[14].toString());
				if(obj[15] !=null) acs.setChecker(obj[15].toString());
				if(obj[16] !=null) acs.setMakerTimestamp((Date)obj[16]);
				if(obj[20] !=null) acs.setReviewStatus(obj[20].toString());
				if(obj[21] !=null) acs.setReviewStatusDesc(obj[21].toString());
				if(obj[22] !=null) acs.setReviewComments(obj[22].toString());	
				//if(obj[23] !=null) acs.setStandardStatementNonCompliance(obj[23].toString()); // as the Standard statement is part of Observation
				
				auditRatingCompObsScoreLst.add(acs);
				
			}logger.info("Obs Comp records in Report .. "+i +" Lst:: " + auditRatingCompObsScoreLst);				
			
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}return auditRatingCompObsScoreLst; 
	}
	
	@Override
	public List<AuditRatingComplianceObsScore> downloadAuditRatingComplianceObsScore(Integer legalEntityCode, String auditTypeCode, String auditId ){
		
		Session session = getSession();
		session.setDefaultReadOnly(true);
		
		StringBuilder queryString=new StringBuilder(" From AuditRatingComplianceObsScore compScore"
				+ " where compScore.legalEntityCode = :legalEntityCode "
				//+ " and auditTypeCode = :auditTypeCode "
				+ " and auditId = :auditId ");
		List<AuditRatingComplianceObsScore> auditRatingComplianceObsScoreList =  session.createQuery(queryString.toString())
		  .setParameter("legalEntityCode", legalEntityCode)
		  //.setParameter("auditTypeCode", auditTypeCode)
		  .setParameter("auditId", auditId)
		  .list();
		
		return auditRatingComplianceObsScoreList;
		
	}
	
}
