package com.bankaudit.dao;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.Date;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.ScrollMode;
import org.hibernate.ScrollableResults;
import org.hibernate.Session;
import org.hibernate.transform.ResultTransformer;
import org.jboss.logging.Logger;
import org.springframework.stereotype.Repository;

import com.bankaudit.constants.BankAuditConstant;
import com.bankaudit.dto.DataTableResponse;
import com.bankaudit.model.RiskScoreAuditGrp;
import com.bankaudit.model.RiskScoreAuditSubgrp;
import com.bankaudit.util.BankAuditUtil;
import com.bankaudit.util.RiskScoreAuditGrpList;
import com.bankaudit.util.RiskScoreAuditGrpSublistMap;

import java.util.ArrayList;

@Repository("riskScoreAuditGrpDao")
public class RiskScoreAuditGrpDaoImpl extends AbstractDao implements RiskScoreAuditGrpDao{

	static final Logger logger = Logger.getLogger(RiskScoreAuditGrpDaoImpl.class);
	
	@Override
	public void deleteByLegalEntityCodeAndAuditTypeCode(Integer legalEntityCode, String auditTypeCode) {

		Session session=getSession();
		
		session.createQuery("delete from RiskScoreAuditGrp where "
				+ " legalEntityCode =:legalEntityCode and auditTypeCode =:auditTypeCode")
		.setParameter("legalEntityCode", legalEntityCode)
		.setParameter("auditTypeCode", auditTypeCode).executeUpdate();
		
		session.createQuery("delete from RiskScoreAuditSubgrp where "
				+ " legalEntityCode =:legalEntityCode "
				+ " and auditTypeCode =:auditTypeCode ")
		.setParameter("legalEntityCode", legalEntityCode)
		.setParameter("auditTypeCode",auditTypeCode).executeUpdate();
	
		
	}

	@Override
	public DataTableResponse getRiskScoreAuditGrps(Integer legalEntityCode, String search, Integer orderColumn,
			String orderDirection, Integer page, Integer size) {
		
		
	    Session session = getSession();
	    session.setDefaultReadOnly(true);
		List<RiskScoreAuditSubgrp> riskScoreAuditSubgrps = null;
		
		DataTableResponse dataTableResponse=new DataTableResponse();
	
		StringBuilder queryString=new StringBuilder(
				
				
				           "  select "
						 + "  sub_group_weightage , "
						
						 + "(select concat(ag.audit_sub_group_code , '-' , ag.audit_sub_group_name)"
						 + " from maint_audit_subgroup ag where "
						 + " ag.audit_group_code=rsag.audit_group_code "
						 + " and ag.audit_type_code=rsag.audit_type_code "
						 + " and ag.audit_sub_group_code=rsag.audit_sub_group_code "
						 + " and ag.legal_entity_code=rsag.legal_entity_code ) as audit_sub_group_code , "
						 
						 + "  legal_entity_code , "
						
						 + "(select concat(atd.audit_type_code , '-' , atd.audit_type_desc)"
						 + " from maint_audit_type_desc atd where "
						 + " atd.audit_type_code=rsag.audit_type_code "
						 + " and atd.legal_entity_code=rsag.legal_entity_code ) as audit_type_code , "
						 
						 + "(select concat(ag.audit_group_code , '-' , ag.audit_group_name)"
						 + " from maint_audit_group ag where "
						 + " ag.audit_group_code=rsag.audit_group_code "
						 + " and ag.audit_type_code=rsag.audit_type_code "
						 + " and ag.legal_entity_code=rsag.legal_entity_code ) as audit_group_code , "
						 
						 + "  weightage , "
						 + "  auth_rej_remarks , "
				        
						 + " (select concat(gp.value , '-' , gp.description) from general_parameter gp where"
				         + " gp.key_1='ENTITY_STATUS' and gp.key_2='ENTITY_STATUS' and gp.value=rsag.entity_status "
				         + " and gp.legal_entity_code=rsag.legal_entity_code ) as entity_status ,  "
						
						 + " (select concat(gp.value , '-' , gp.description)"
						 + " from general_parameter gp where "
						 + " gp.key_1='STATUS' and gp.value=rsag.status "
						 + " and gp.legal_entity_code=rsag.legal_entity_code ) as status, "
						 
						 + "(select concat(u.user_id , '-' ,u.first_name,' ',u.last_name )"
						 + " from user u where "
						 + " u.user_id=rsag.maker "
						 + " and u.legal_entity_code=rsag.legal_entity_code ) as maker ,  "
						 
						 + "  maker_timestamp , "
						 + "  checker , "
						 + "  checker_timestamp , "
						 + "  businessdate_timestamp , "
						 + "  sysdate_timestamp "
						 
						 + "  from ( "
						 + "  SELECT  "
						 + "  asg.weightage as sub_group_weightage , "
						 + "  asg.audit_sub_group_code , "
						 + "  ag.legal_entity_code , "
						 + "  ag.audit_type_code , "
						 + "  ag.audit_group_code , "
						 + "  ag.weightage , "
						 + "  ag.auth_rej_remarks , "
						 + "  ag.entity_status  , "
						 + "  ag.status  , "
						 + "  ag.maker  , "
						 + "  ag.maker_timestamp , "
						 + "  ag.checker , "
						 + "  ag.checker_timestamp , "
						 + "  ag.businessdate_timestamp , "
						 + "  ag.sysdate_timestamp "
						 + "  FROM  risk_score_audit_grp AS ag , risk_score_audit_subgrp AS asg "
						 + "   WHERE asg.legal_entity_code=:legal_entity_code "
						 + "   AND asg.legal_entity_code=ag.legal_entity_code "
						 + "   AND asg.audit_group_code=ag.audit_group_code "
						 + "   AND asg.audit_type_code=ag.audit_type_code "
						 + "   union ALL "
						 + "  SELECT "
						 + "  asg.weightage as sub_group_weightage , "
						 + "  asg.audit_sub_group_code , "
						 + "  ag.legal_entity_code , "
						 + "  ag.audit_type_code , "
						 + "  ag.audit_group_code , "
						 + "  ag.weightage , "
						 + "  ag.auth_rej_remarks , "
						 + "  ag.entity_status  , "
						 + "  ag.status  , "
						 + "  ag.maker  , "
						 + "  ag.maker_timestamp , "
						 + "  ag.checker , "
						 + "  ag.checker_timestamp , "
						 + "  ag.businessdate_timestamp , "
						 + "  ag.sysdate_timestamp "
						 + "  FROM  risk_score_audit_grp_wrk AS ag , risk_score_audit_subgrp_wrk AS asg "
						 + "   WHERE asg.legal_entity_code=:legal_entity_code "
						 + "   AND asg.legal_entity_code=ag.legal_entity_code "
						 + "   AND asg.audit_group_code=ag.audit_group_code "
						 + "   AND asg.audit_type_code=ag.audit_type_code "
						 + "  ) as rsag where legal_entity_code=:legal_entity_code "
			
				
				);
		
		
		if(!BankAuditUtil.isEmptyString(search)){
			queryString=queryString.append(" and ( "
					+ " rsag.audit_type_code like :search  "
					+ " or  rsag.audit_type_code IN ( select atd.audit_type_code from maint_audit_type_desc atd where "
					+ "  						 atd.legal_entity_code=rsag.legal_entity_code and atd.audit_type_desc LIKE :search ) "
			        			        
			        + " or  rsag.audit_group_code like :search  "
			        + " or  rsag.audit_group_code IN ( select  ag.audit_group_code from maint_audit_group ag where ag.legal_entity_code=rsag.legal_entity_code "
					+ "  						and ag.audit_type_code=rsag.audit_type_code and ag.audit_group_name like :search )"
				    + " or  rsag.weightage like :search  "
					
				    + " or  rsag.audit_sub_group_code like :search  "
					+ " or  rsag.audit_sub_group_code In ( select ag.audit_sub_group_code from maint_audit_subgroup ag where ag.audit_group_code=rsag.audit_group_code "
					+ " 						and ag.audit_type_code=rsag.audit_type_code and ag.legal_entity_code=rsag.legal_entity_code "
					+ " 						and ag.audit_sub_group_name like :search )"
					+ " or  rsag.sub_group_weightage like :search  "
					
					+ " or  rsag.maker like :search  "
                    + " or  rsag.maker IN ( select u.user_id from user u where (concat(u.first_name,' ', u.last_name) like :search ) and u.legal_entity_code =:legal_entity_code ) "
	                + " or  rsag.status IN (SELECT value FROM general_parameter  WHERE key_1='STATUS' and  description LIKE :search ) "
                    //+ " or  rsag.entity_status IN (SELECT value FROM general_parameter  WHERE key_1='ENTITY_STATUS' and key_2='ENTITY_STATUS' and description LIKE :search ) "
					+ " ) ");
		}
		/*		legal_entity_code  
		audit_type_code    
		audit_group_code   
		weightage          
		status             
		maker                              */
		String[] columns={
				"rsag.audit_type_code",
				"rsag.audit_group_code",
				"rsag.weightage",
				"rsag.audit_sub_group_code",
				"rsag.sub_group_weightage",
				"rsag.maker",
				"rsag.entity_status",
				"rsag.status"};
		
		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(STATUS,'DF','R','RM','U','M','A') ASC  "); // The code modified to get the records in the order of Status on landing page, the sequence is  Draft, Reject, Unauth, Modify, Auth
		}else if(orderColumn!=null && !BankAuditUtil.isEmptyString(orderDirection)){
			queryString=queryString.append(" order by ").append(columns[orderColumn]).append(" ").append(orderDirection);
		}
		
		Query query=session.createSQLQuery(queryString.toString())
		
				.setResultTransformer(new ResultTransformer() {
					
					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						
						RiskScoreAuditSubgrp riskScoreAuditSubgrp=null;
						RiskScoreAuditGrp riskScoreAuditGrp=null;
						
						if(tuple!=null){
							
							riskScoreAuditSubgrp=new RiskScoreAuditSubgrp();
							riskScoreAuditGrp =new RiskScoreAuditGrp();
							
			if(tuple[0]!=null){riskScoreAuditSubgrp.setWeightage(Integer.parseInt(tuple[0].toString()));}
			if(tuple[1]!=null){riskScoreAuditSubgrp.setAuditSubGroupCode(tuple[1].toString());}
			if(tuple[2]!=null){riskScoreAuditGrp.setLegalEntityCode(Integer.parseInt(tuple[2].toString()));}
			if(tuple[3]!=null){riskScoreAuditGrp.setAuditTypeCode(tuple[3].toString());}
			if(tuple[4]!=null){riskScoreAuditGrp.setAuditGroupCode(tuple[4].toString());}
			if(tuple[5]!=null){riskScoreAuditGrp.setWeightage(Integer.parseInt(tuple[5].toString()));}
			if(tuple[6]!=null){riskScoreAuditGrp.setAuthRejRemarks(tuple[6].toString());}
			if(tuple[7]!=null){riskScoreAuditGrp.setEntityStatus(tuple[7].toString());}
			if(tuple[8]!=null){riskScoreAuditGrp.setStatus(tuple[8].toString());}
			if(tuple[9]!=null){riskScoreAuditGrp.setMaker(tuple[9].toString());}
			if(tuple[10]!=null){riskScoreAuditGrp.setMakerTimestamp((Date)tuple[10]);}
			if(tuple[11]!=null){riskScoreAuditGrp.setChecker(tuple[11].toString());}
			if(tuple[12]!=null){riskScoreAuditGrp.setCheckerTimestamp((Date)tuple[12]);}
			if(tuple[13]!=null){riskScoreAuditGrp.setBusinessdateTimestamp((Date)tuple[13]);}
			if(tuple[14]!=null){riskScoreAuditGrp.setSysdateTimestamp((Date)tuple[14]);}
		    
			riskScoreAuditSubgrp.setRiskScoreAuditGrp(riskScoreAuditGrp);	
						}					
						return riskScoreAuditSubgrp;
					}
					
					@Override
					public List transformList(List collection) {
						return collection;
					}
				})
				.setParameter("legal_entity_code", legalEntityCode);
		
	
		
		if(!BankAuditUtil.isEmptyString(search)){
			query.setParameter("search", "%"+search+"%");
		}
		
		
		ScrollableResults resultScroll = query.scroll(ScrollMode.SCROLL_INSENSITIVE);
		if(resultScroll.first()&&resultScroll.scroll(page*size)){
			
			riskScoreAuditSubgrps= new ArrayList<>();
			int i = 0;
			while (size > i++) {
				riskScoreAuditSubgrps.add((RiskScoreAuditSubgrp) resultScroll.get(0));
			    if (!resultScroll.next())
			        break;
			}
			dataTableResponse.setData(riskScoreAuditSubgrps);
			resultScroll.last();
			if(size!=0){
				dataTableResponse.setRecordsFiltered((resultScroll.getRowNumber()+1l/size)+1);
				dataTableResponse.setRecordsTotal((resultScroll.getRowNumber()+1l/size)+1);
			}else{
				dataTableResponse.setError("page size zero");
				dataTableResponse.setData(Collections.EMPTY_LIST);
				dataTableResponse.setRecordsTotal(0l);
				dataTableResponse.setRecordsFiltered(0l);
			}
			
		}else{
			dataTableResponse.setError(null);
			dataTableResponse.setData(Collections.EMPTY_LIST);
			dataTableResponse.setRecordsTotal(0l);
			dataTableResponse.setRecordsFiltered(0l);
		}
		return dataTableResponse;

	
		
	}

	@Override
	public List<RiskScoreAuditGrp> getRiskScoreAuditGrp(Integer legalEntityCode, String auditGroupCode,
			String auditTypeCode) {
		
		Session session=getSession();
		
		
		
		StringBuilder queryString=new StringBuilder(
				" select "
						+ " rsag as riskScoreAuditGrp , "
			    + "(select concat(atd.auditTypeCode , '-' , atd.auditTypeDesc)"
			    + " from MaintAuditTypeDesc atd where "
			    + " atd.auditTypeCode=rsag.auditTypeCode "
			    + " and atd.legalEntityCode=rsag.legalEntityCode ) as auditTypeCode , "
				
				+ "(select concat(ag.auditGroupCode , '-' , ag.auditGroupName)"
				+ " from MaintAuditGroup ag where "
				+ " ag.auditGroupCode=rsag.auditGroupCode "
				+ " and ag.legalEntityCode=rsag.legalEntityCode ) as auditGroupCode "
				

			    + " from RiskScoreAuditGrp as rsag   "
				+ " where rsag.legalEntityCode =:legalEntityCode "
				+ " and rsag.auditTypeCode =:auditTypeCode "
				
				);
		
		if(!BankAuditUtil.isEmptyString(auditGroupCode)){
			queryString=queryString.append(" and rsag.auditGroupCode =:auditGroupCode ");
		}

		
		Query query=session.createQuery(queryString.toString());
		
		query.setParameter("legalEntityCode", legalEntityCode);
		query.setParameter("auditTypeCode", auditTypeCode);
		
		if(!BankAuditUtil.isEmptyString(auditGroupCode)){
			query.setParameter("auditGroupCode", auditGroupCode);
		}
		query.setResultTransformer(
		 new ResultTransformer() {
		        @Override
		        public Object transformTuple(
		            Object[] tuple, 
		            String[] aliases) {
		        	RiskScoreAuditGrp riskScoreAuditGrp=null;
		        	if(tuple!=null){
		        		riskScoreAuditGrp=(RiskScoreAuditGrp)tuple[0];
			        	if(tuple[1]!=null){
			        		riskScoreAuditGrp.setAuditTypeCode((String)tuple[1]);
			        	}
			        	if(tuple[2]!=null){
				        	riskScoreAuditGrp.setAuditGroupCode((String)tuple[2]);			        		
			        	}
			        	
		        	}
		           return riskScoreAuditGrp; 
		        }
		 
		        @Override
		        public List transformList(List collection) {
		            return collection;
		        }
		    } 
		);
		
		return query.list();
	}

	@Override
	public List<RiskScoreAuditGrp> getRiskScoreAuditGrpByLegalEntityCodeAndAuditTypeCode(int legalEntityCode,
			String auditTypeCode,String status) {
		
		Session session=getSession();
		
		String authQuery=
				
				
		        "SELECT " 
				+ " " 
				+ "maint.legal_entity_code as legalEntityCode , " 
				+ "maint.audit_type_desc as auditTypeCode , " 
				+ "maint.audit_group_name as auditGroupCode , " 
				+ "risk.group_weightage as groupWeightage , " 
				+ "risk.status as status, " 
				+ "risk.maker as maker, " 
				+ "risk.maker_timestamp as makerTimestamp, " 
				+ "risk.checker as checker, " 
				+ "risk.checker_timestamp as checkerTimestamp, " 
				+ "risk.businessdate_timestamp as businessdateTimestamp, " 
				+ "risk.sysdate_timestamp as sysdateTimestamp, " 
				
                + "maint.audit_sub_group_name as auditSubGroupCode , " 
				+ "risk.sub_group_weightage as subGroupWeightage , " 
				+ "risk.entity_status as entity_status  "
				
				
				
				+ " " 
				+ " FROM  " 
				+ " " 
				+ "(SELECT mag.legal_entity_code,mag.audit_type_code, " 
				+ "mag.audit_group_code,mag.status,masg.audit_sub_group_code, " 
				+ "CONCAT(mag.audit_type_code,'-',matd.audit_type_desc) audit_type_desc, " 
				+ "CONCAT(mag.audit_group_code,'-',mag.audit_group_name)  audit_group_name, " 
				+ "CONCAT(masg.audit_sub_group_code,'-',masg.audit_sub_group_name)  audit_sub_group_name " 
				+ " " 
				+ "FROM maint_audit_group mag,maint_audit_subgroup masg,maint_audit_type_desc matd " 
				+ "WHERE 1=1  " 
				+ "AND mag.legal_entity_code=masg.legal_entity_code " 
				+ "AND mag.audit_type_code=masg.audit_type_code " 
				+ "AND mag.audit_group_code=masg.audit_group_code " 
				+ " " 
				+ "AND mag.legal_entity_code=matd.legal_entity_code " 
				+ "AND mag.audit_type_code=matd.audit_type_code " 
				
				+ " AND mag.entity_status='A'  "
				+ " AND masg.entity_status='A' "
				
				+ "ORDER BY mag.audit_type_code ASC,mag.audit_group_code ASC,masg.audit_sub_group_code ASC " 
				+ ") maint " 
				+ " " 
				+ "LEFT JOIN  " 
				+ " " 
				+ "(SELECT  " 
				+ "rsag.legal_entity_code, " 
				+ "rsag.audit_type_code, " 
				+ "rsag.audit_group_code, " 
				+ "rsasg.audit_sub_group_code, " 
				+ " " 
				+ "rsag.weightage AS group_weightage, " 
				+ "rsasg.weightage AS sub_group_weightage, " 
				+ "rsag.maker, " 
				+ "rsag.maker_timestamp, " 
				+ "rsag.checker, " 
				+ "rsag.checker_timestamp, " 
				+ "rsag.businessdate_timestamp, " 
				+ "rsag.sysdate_timestamp , "
				+ "rsag.status ,"
				+ "rsag.entity_status "
				+ "FROM risk_score_audit_grp rsag,risk_score_audit_subgrp rsasg " 
				+ "WHERE 1=1 " 
				+ "AND rsag.legal_entity_code=rsasg.legal_entity_code " 
				+ "AND rsag.audit_type_code=rsasg.audit_type_code " 
				+ "AND rsag.audit_group_code=rsasg.audit_group_code " 
				+ "ORDER BY rsag.audit_type_code ASC,rsag.audit_group_code ASC,rsasg.audit_sub_group_code ASC) risk " 
				+ " " 
				+ "ON (maint.legal_entity_code = risk.legal_entity_code " 
				+ "AND maint.audit_type_code = risk.audit_type_code  " 
				+ "AND maint.audit_group_code = risk.audit_group_code " 
				+ "AND maint.audit_sub_group_code=risk.audit_sub_group_code " 
				+ ") " 
				+ "WHERE maint.audit_type_code =:auditTypeCode " 
				+ "AND maint.legal_entity_code =:legalEntityCode     " 
		
		;
		
		
		String unauthQuery=
				
				
		        "SELECT " 
				+ " " 
				+ "maint.legal_entity_code as legalEntityCode , " 
				+ "maint.audit_type_desc as auditTypeCode , " 
				+ "maint.audit_group_name as auditGroupCode , " 
				+ "risk.group_weightage as groupWeightage , " 
				+ "risk.status as status, " 
				+ "risk.maker as maker, " 
				+ "risk.maker_timestamp as makerTimestamp, " 
				+ "risk.checker as checker, " 
				+ "risk.checker_timestamp as checkerTimestamp, " 
				+ "risk.businessdate_timestamp as businessdateTimestamp, " 
				+ "risk.sysdate_timestamp as sysdateTimestamp, " 
				
                + "maint.audit_sub_group_name as auditSubGroupCode , " 
				+ "risk.sub_group_weightage as subGroupWeightage , "
				+ "risk.entity_status as entity_status  "
                
				
				
				
				+ " " 
				+ " FROM  " 
				+ " " 
				+ "(SELECT mag.legal_entity_code,mag.audit_type_code, " 
				+ "mag.audit_group_code,mag.status,masg.audit_sub_group_code, " 
				+ "CONCAT(mag.audit_type_code,'-',matd.audit_type_desc) audit_type_desc, " 
				+ "CONCAT(mag.audit_group_code,'-',mag.audit_group_name)  audit_group_name, " 
				+ "CONCAT(masg.audit_sub_group_code,'-',masg.audit_sub_group_name)  audit_sub_group_name " 
				+ " " 
				+ "FROM maint_audit_group mag,maint_audit_subgroup masg,maint_audit_type_desc matd " 
				+ "WHERE 1=1  " 
				+ "AND mag.legal_entity_code=masg.legal_entity_code " 
				+ "AND mag.audit_type_code=masg.audit_type_code " 
				+ "AND mag.audit_group_code=masg.audit_group_code " 
				+ " " 
				+ "AND mag.legal_entity_code=matd.legal_entity_code " 
				+ "AND mag.audit_type_code=matd.audit_type_code " 
								
				+ " AND mag.entity_status='A'  "
				+ " AND masg.entity_status='A' "

				+ "ORDER BY mag.audit_type_code ASC,mag.audit_group_code ASC,masg.audit_sub_group_code ASC " 
				+ ") maint " 
				+ " " 
				+ "LEFT JOIN  " 
				+ " " 
				+ "(SELECT  " 
				+ "rsag.legal_entity_code, " 
				+ "rsag.audit_type_code, " 
				+ "rsag.audit_group_code, " 
				+ "rsasg.audit_sub_group_code, " 
				+ " " 
				+ "rsag.weightage AS group_weightage, " 
				+ "rsasg.weightage AS sub_group_weightage, " 
				+ "rsag.maker, " 
				+ "rsag.maker_timestamp, " 
				+ "rsag.checker, " 
				+ "rsag.checker_timestamp, " 
				+ "rsag.businessdate_timestamp, " 
				+ "rsag.sysdate_timestamp, "
				+ "rsag.status, "
				+ "rsag.entity_status "
				+ "FROM risk_score_audit_grp_wrk rsag,risk_score_audit_subgrp_wrk rsasg " 
				+ "WHERE 1=1 " 
				+ "AND rsag.legal_entity_code=rsasg.legal_entity_code " 
				+ "AND rsag.audit_type_code=rsasg.audit_type_code " 
				+ "AND rsag.audit_group_code=rsasg.audit_group_code " 
				+ "ORDER BY rsag.audit_type_code ASC,rsag.audit_group_code ASC,rsasg.audit_sub_group_code ASC) risk " 
				+ " " 
				+ "ON (maint.legal_entity_code = risk.legal_entity_code " 
				+ "AND maint.audit_type_code = risk.audit_type_code  " 
				+ "AND maint.audit_group_code = risk.audit_group_code " 
				+ "AND maint.audit_sub_group_code=risk.audit_sub_group_code " 
				+ ") " 
				+ "WHERE maint.audit_type_code =:auditTypeCode " 
				+ "AND maint.legal_entity_code =:legalEntityCode     " 
		
		;
		
		
		
		String queryString="";
		
		if(BankAuditConstant.STATUS_AUTH.equalsIgnoreCase(status)){
			queryString=authQuery;
		}else {
			queryString=unauthQuery;
		}
	

		
		Query query=session.createSQLQuery(queryString);
		
		query.setParameter("legalEntityCode", legalEntityCode);
		query.setParameter("auditTypeCode", auditTypeCode);
		
	
		query.setResultTransformer(
		 new ResultTransformer() {
		        @Override
		        public Object transformTuple(
		            Object[] tuple, 
		            String[] aliases) {
		        	RiskScoreAuditGrp riskScoreAuditGrp=new RiskScoreAuditGrp(tuple);
		        	riskScoreAuditGrp.setRiskScoreAuditSubgrps(Arrays.asList(new RiskScoreAuditSubgrp(tuple)));
		           return riskScoreAuditGrp; 
		        }
		 
		        @Override
		        public List transformList(List collection) {
		        	
		        	RiskScoreAuditGrpList list = new RiskScoreAuditGrpList(collection); 
	        	         
		        	RiskScoreAuditGrpSublistMap map = new RiskScoreAuditGrpSublistMap();
	        	    for (RiskScoreAuditGrp item : list) { 
	        	    	RiskScoreAuditGrpList subList = map.get(item); 
	        	        if (subList == null) { 
	        	            subList = new RiskScoreAuditGrpList();
	        	            map.put(item, subList); 
	        	        }
	        	        subList.add(item); 
	        	    }
	        	     
	        	    List<RiskScoreAuditGrp> mainList=new ArrayList<>();   
	        	    for (RiskScoreAuditGrpList subList : map.values()) { 
	        	       
	        	    	List<RiskScoreAuditSubgrp> scoreAuditSubgrps=new ArrayList<>();
	        	    	RiskScoreAuditGrp main=subList.get(0);
	        	    	for (RiskScoreAuditGrp riskScoreAuditGrp : subList) {
	        	    		scoreAuditSubgrps.addAll(riskScoreAuditGrp.getRiskScoreAuditSubgrps());
	        	    		main.setRiskScoreAuditSubgrps(scoreAuditSubgrps);
						}
	        	    	mainList.add(main);
	        	    }
		        	
		            return mainList;
		        }
		    } 
		);
		
		return query.list();
	}

	@Override
	public Boolean isRiskScoreAuditGrp(Integer legalEntityCode, String auditTypeCode) {
		
		logger.info("Inside isRiskScoreAuditGrp .. legalEntityCode .."+legalEntityCode +" auditTypeCode.."+auditTypeCode);
		Boolean flag=false;
		try {
			/* " select "
						+ " "
						+ " (select count(*) "
						+ " from MaintEntity   "
						+ " where "
						+ " legalEntityCode =:legalEntityCode "
						+ " and unitHeadUser =:userId "
						+ " and entityStatus='A' ) + "
						+ " (select count(*) "
						+ " from MaintEntityWrk   "
						+ " where "
						+ " legalEntityCode =:legalEntityCode "
						+ " and unitHeadUser =:userId "
						+ " and entityStatus='A' ) from MaintEntity  "
						+ "  "
						*/
			
			Session session=getSession();
			
			Long count=(Long)session.createQuery(
		
					
					" select count(*) "
					+ " from RiskScoreAuditGrp   "
					+ " where "
					+ " legalEntityCode =:legalEntityCode "
					+ " and auditTypeCode =:auditTypeCode  ")
			.setParameter("legalEntityCode", legalEntityCode)
			.setParameter("auditTypeCode", auditTypeCode).uniqueResult();
			
			Long count1=(Long)session.createQuery(
			
					
					" select count(*) "
					+ " from RiskScoreAuditGrpWrk   "
					+ " where "
					+ " legalEntityCode =:legalEntityCode "
					+ " and auditTypeCode =:auditTypeCode  ")
			.setParameter("legalEntityCode", legalEntityCode)
			.setParameter("auditTypeCode", auditTypeCode).uniqueResult();
			
			logger.info("Mst count .."+count+" Wrk count .."+count1);
			
			if((count1!=null && count1 >0)||(count!=null && count >0)){
				flag= true;
			}else {
				flag= false;
			}
			logger.info("flag .."+flag);
		}catch(Exception e) {
			
		}
		return flag;
	}

	@Override
	public void deleteRiskScoreAuditGrp(Integer legalEntityCode, String auditTypeCode, String statusUnauth) {
		
		Session session=getSession();
		if(!BankAuditConstant.STATUS_AUTH.equals(statusUnauth)){
			session.createQuery("delete from RiskScoreAuditGrpWrk where legalEntityCode =:legalEntityCode   "
			
					+ " and auditTypeCode =:auditTypeCode  ")
			.setParameter("auditTypeCode", auditTypeCode)
			.setParameter("legalEntityCode", legalEntityCode).executeUpdate();
			session.createQuery("delete from RiskScoreAuditSubgrpWrk where legalEntityCode =:legalEntityCode   "
					+ " and auditTypeCode =:auditTypeCode  ")
			.setParameter("auditTypeCode", auditTypeCode)
			.setParameter("legalEntityCode", legalEntityCode).executeUpdate();
			
		}else {
			session.createQuery("delete from RiskScoreAuditGrp where legalEntityCode =:legalEntityCode   "
					+ " and auditTypeCode =:auditTypeCode  ")
			.setParameter("auditTypeCode", auditTypeCode)
			.setParameter("legalEntityCode", legalEntityCode).executeUpdate();
			

			session.createQuery("delete from RiskScoreAuditSubgrp where legalEntityCode =:legalEntityCode   "
					
					+ " and auditTypeCode =:auditTypeCode  ")
			.setParameter("auditTypeCode", auditTypeCode)
			.setParameter("legalEntityCode", legalEntityCode).executeUpdate();
		
		}
		
	
		
	}
	
}
