package com.bankaudit.rest.process.dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import javax.transaction.Transactional;

import org.apache.log4j.Level;
import org.hibernate.Criteria;
import org.hibernate.FlushMode;
import org.hibernate.Query;
import org.hibernate.ScrollMode;
import org.hibernate.ScrollableResults;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.criterion.Restrictions;
import org.hibernate.jdbc.Work;
import org.hibernate.transform.ResultTransformer;
import org.hibernate.type.StandardBasicTypes;
import org.jboss.logging.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.bankaudit.constants.BankAuditConstant;
import com.bankaudit.dao.AbstractDao;
import com.bankaudit.dao.GeneralParameterDao;
import com.bankaudit.dao.MaintEntityDao;
import com.bankaudit.dto.DataTableResponse;
import com.bankaudit.dto.process.AssessorTeamDto;
import com.bankaudit.model.GeneralParameter;
import com.bankaudit.model.MaintEntity;
import com.bankaudit.model.User;
import com.bankaudit.model.UserRoleMapping;
import com.bankaudit.process.model.AdhocPlanFinalV;
import com.bankaudit.process.model.AuditPlan;
import com.bankaudit.process.model.AuditPlanV;
import com.bankaudit.process.model.AuditPlanWrk;
import com.bankaudit.process.model.AuditProcessTeam;
import com.bankaudit.process.model.AuditProcessV;
import com.bankaudit.process.model.AuditProcessValidate;
import com.bankaudit.process.model.AuditSchedule;
import com.bankaudit.process.model.AuditScheduleWrk;
import com.bankaudit.process.model.AuditTeam;
import com.bankaudit.process.model.AuditTeamWrk;
import com.bankaudit.process.model.AuditschedulesV;
import com.bankaudit.rest.process.controller.MyAuditsController;
import com.bankaudit.service.BusinessDateService;
import com.bankaudit.service.UserService;
import com.bankaudit.util.BankAuditUtil;

import java.util.ArrayList;
@Repository("auditProcessVDao")
public class AuditProcessVDaoImpl extends AbstractDao implements AuditProcessVDao{
	
	@Autowired
	BusinessDateService businessDateService;

	@Autowired
	MaintEntityDao maintEntityDao;
	
	@Autowired
	GeneralParameterDao generalParameterDao;
	
	@Autowired
	UserService userService;

	@SuppressWarnings("unchecked")
	/**
	 * This method is to fetch all branches which are fall under schedule plan start and end dates.
	 * @param legalEntityCode
	 * @param auditTypecode
	 * @param planStartDate
	 * @param planEndDate
	 * @param maker
	 * @return AuditPlansList
	 */
	static final Logger logger = Logger.getLogger(AuditProcessVDaoImpl.class);	
	public List<AuditProcessV> getAuditProcessByAuditPlan(int legalEntityCode,String auditType,String psDate,String peDate,String maker) {
		Session session=getSession();
				
		String hql =" CALL auto_audit_process(:legalEntityCode,:auditTypeCcode,:planStartDate,:planEndDate,:maker) ";		
				
		logger.info(" CALL auto_audit_process("+legalEntityCode+",'"+auditType+"','"+psDate+"','"+peDate+"','"+maker+"')");
		
		List<Object[]> rows = session.createSQLQuery(hql)

				.addScalar("row_num", StandardBasicTypes.LONG)
				.addScalar("audit_status", StandardBasicTypes.STRING)
				.addScalar("audit_type_code", StandardBasicTypes.STRING)
				.addScalar("legal_entity_code", StandardBasicTypes.STRING)
				.addScalar("unit_type", StandardBasicTypes.STRING)

				.addScalar("unit_name", StandardBasicTypes.STRING)
				.addScalar("unit_id", StandardBasicTypes.STRING)
				.addScalar("frequency", StandardBasicTypes.STRING)
				.addScalar("man_days", StandardBasicTypes.STRING)

				.addScalar("duration", StandardBasicTypes.INTEGER)
				.addScalar("last_audit_end_date", StandardBasicTypes.DATE)

				.addScalar("start_date", StandardBasicTypes.DATE)
				.addScalar("end_date", StandardBasicTypes.DATE)
				.addScalar("criticality_desc", StandardBasicTypes.STRING)				
				.addScalar("audit_id", StandardBasicTypes.STRING)
				.addScalar("plan_comments", StandardBasicTypes.STRING)				
				.addScalar("plan_audit_remarks", StandardBasicTypes.STRING)
				
				.setParameter("legalEntityCode", legalEntityCode)
			    .setParameter("auditTypeCcode", auditType)
			    .setParameter("planStartDate", psDate)
			    .setParameter("planEndDate", peDate)
	            .setParameter("maker", maker)
		
				.list();

		AuditProcessV apt = null;
		List<AuditProcessV> ls = new ArrayList<>();
		List<String> unitIdsList = maintEntityDao.getSubBranchesGyUserIdOrUnitId(legalEntityCode, "user", maker);

		try {
			for (Object[] col : rows) {
				if (unitIdsList.contains(col[6].toString())) {
					apt = new AuditProcessV();
					apt.setRowNum(Long.valueOf(col[0].toString()).longValue());
					apt.setAuditStatus(col[1].toString());
					apt.setAuditTypeCode(col[2].toString());
					apt.setLegalEntityCode(col[3].toString());
					apt.setUnitType((col[4] != null) ? col[4].toString() : "");

				apt.setUnitName(col[5].toString());
				apt.setUnitId(col[6].toString());
				apt.setFrequency(col[7].toString());
				apt.setManDays(col[8].toString());

				//apt.setDuration(Integer.valueOf(col[9].toString()).intValue());
				apt.setDuration(Integer.valueOf(col[9].toString())); //spotbugs
				apt.setLastAuditEndDate(col[10].toString());
				apt.setStartDate(col[11].toString());
				apt.setEndDate(col[12].toString());

					apt.setCriticalityDesc(col[13].toString());
					apt.setAuditId(null);

					apt.setPlanComments((col[14] != null) ? col[14].toString() : "");
					apt.setPlanAuditRemarks((col[15] != null) ? col[15].toString() : "");

					ls.add(apt);
				}
			}

		}catch (Exception e) {
			logger.error(e.getMessage(),e);
		}

		return ls;		//.setParameter("planStartDate", planStartDate)
	}

	
	/*@SuppressWarnings("unchecked")
	public List<AuditProcessV> getAuditProcessByAuditPlan(int legalEntityCode,String auditType,String psDate,String peDate,String maker) {
		
		List<AuditProcessV> auditProcess=null;
		
		     Session session=getSession();
		     
		     Query query = session.createSQLQuery("  CALL auto_audit_process(:legalEntityCode,:auditTypeCcode,:planStartDate,:planEndDate,:maker) ")
				.addEntity(AuditProcessV.class)
				.setParameter("legalEntityCode", legalEntityCode)
			    .setParameter("auditTypeCcode", auditType)
			    .setParameter("planStartDate", psDate)
			    .setParameter("planEndDate", peDate)
	            .setParameter("maker", maker);		
				  
		     return auditProcess = query.list();  
		}
	*/

	/*@SuppressWarnings("rawtypes")
	public List<AuditProcessV> checkAndsetStartDateforAuditProcess(List<AuditProcessV> apl,String planStartDate,String planEndDate){
		List modified = new ArrayList<>();
		Session session=getSession();
		Object[] obj;
		String frequency="0",currStartDate=null,currEndDate=null;
		boolean isValidedStartDate=false;
		for (AuditProcessV auditProcessV : apl) {
			isValidedStartDate=false;

			if(auditProcessV.getAuditStatus().equals("AU")) {
				//auditProcessV.setStartDate(BankAuditUtil.parseDate(planStartDate));	
				isValidedStartDate = true;
			}else if(auditProcessV.getStartDate()!=null && BankAuditUtil.parseStringToDate(planStartDate).compareTo(BankAuditUtil.parseStringToDate(auditProcessV.getStartDate()))>0) {

				obj=isValiedStartDate(session,auditProcessV,planStartDate,frequency,planStartDate,planEndDate);
				if(obj!=null && obj[0]!=null && (Integer)obj[0]>0 && obj[1]!=null && obj[2]!=null)  
				{
					currStartDate=(String)obj[1];
					currEndDate=(String)obj[2];

					auditProcessV.setStartDate(currStartDate);
					auditProcessV.setEndDate(currEndDate);
					isValidedStartDate = true;
				}
			}else {
				if(auditProcessV.getStartDate()!=null) {
					auditProcessV.setStartDate(auditProcessV.getStartDate());
					auditProcessV.setEndDate(auditProcessV.getEndDate());
					isValidedStartDate = true;
				}
			} 

			if(isValidedStartDate) {
				modified.add(auditProcessV);
			}
		}
		return modified;
	}*/

/*
	@Override
	public List getAuditProcessAsPerFrequnacy(String planStartDate, String planEndDate,List<AuditProcessV> apv) {

		Session session=getSession();

		List  finalList=new  ArrayList<>();
		boolean flag=false;
		String currStartDate=null,currEndDate=null,frequency=null;
		Object[] obj;
        
		AuditProcessV a =null;
		int count=0;
		for (AuditProcessV auditProcessV : apv) {
			auditProcessV.setRowNum(++count);
			finalList.add(auditProcessV);
			
			if(!auditProcessV.getAuditStatus().equals("AU")) {
				flag=false;			
				currStartDate="";
				frequency=auditProcessV.getFrequency();
				do {

					if("".equals(currStartDate)) {
						currStartDate=auditProcessV.getStartDate();
					}
					obj=isValiedStartDate(session,auditProcessV,currStartDate,frequency,planStartDate,planEndDate);
					if(obj!=null && obj[0]!=null && (Integer)obj[0]>0 && obj[1]!=null && obj[2]!=null)  
					{
						currStartDate=(String)obj[1];
						currEndDate=(String)obj[2];
						a=setBeanValuesToNewObject(auditProcessV,currStartDate,currEndDate,++count);
						finalList.add(a);
						flag=true;
					}else {
						flag=false;
					}
				}while(flag);	
			}
		}//for	
		
		return finalList;
	}
*/
	
/*	public AuditProcessV setBeanValuesToNewObject(AuditProcessV actualObj,String currStartDate,String currEndDate,int count) {
		AuditProcessV copyIntoObject = null;
		if(actualObj!=null) {
			
			copyIntoObject=new AuditProcessV();
			
			copyIntoObject.setRowNum(count);
			copyIntoObject.setAuditStatus(actualObj.getAuditStatus());
			copyIntoObject.setLegalEntityCode(actualObj.getLegalEntityCode());
			copyIntoObject.setUnitType(actualObj.getUnitType());
			copyIntoObject.setUnitName(actualObj.getUnitName());
			
			copyIntoObject.setUnitId(actualObj.getUnitId());
			copyIntoObject.setCriticalityDesc(actualObj.getCriticalityDesc());
			copyIntoObject.setFrequency(actualObj.getFrequency());
			copyIntoObject.setManDays(actualObj.getManDays());
			copyIntoObject.setDuration(actualObj.getDuration());
			copyIntoObject.setLastAuditEndDate(actualObj.getLastAuditEndDate());
			
			copyIntoObject.setStartDate(currStartDate);
			copyIntoObject.setEndDate(currEndDate);
			copyIntoObject.setAuditTypeCode(actualObj.getAuditTypeCode());
		}
		
		return copyIntoObject;
	}
*/	
/**
 * this method is to check gien start date is Valid Business Date 
 * @param session
 * @param apv
 * @param currStartDate
 * @param frequency
 * @param plnStDate
 * @param plnEnDate
 * @return
 */
	public Object[] isValiedStartDate(Session session,AuditProcessV apv,String currStartDate,String frequency,String plnStDate,String plnEnDate) {

		String hql=" SELECT COUNT(d.currStartDate) as cnt,d.currStartDate,d.currEndDate FROM " +

				" (SELECT DATE(Schedule_date_f("+apv.getLegalEntityCode()+",'"+apv.getUnitId()+"','"+currStartDate+"',"+frequency+",1)) AS currStartDate ,"

				+ " DATE(Schedule_date_f("+apv.getLegalEntityCode()+",'"+apv.getUnitId()+"','"+currStartDate+"',"+frequency+","+apv.getDuration()+")) AS currEndDate "

				+ " FROM DUAL) d " +
				" WHERE 1=1 " +
				" AND d.currStartDate >= CAST('"+plnStDate+"' AS DATE) " ;


		if(plnEnDate!=null && !"".equals(plnEnDate)) {		
			hql+=	" AND d.currStartDate <= CAST('"+plnEnDate+"' AS DATE) " +
					" group by  d.currStartDate, d.currEndDate ";
		}

		Object[] obj=(Object[]) session.createSQLQuery(hql)
				.addScalar("cnt", StandardBasicTypes.INTEGER)
				.addScalar("currStartDate", StandardBasicTypes.STRING)
				.addScalar("currEndDate", StandardBasicTypes.STRING)
				.uniqueResult();

		return obj;

	}

	
	public Long numberOfBusinessDaysBtweenStartEndDates(String legalEntityCode,String unitCode,String StDate,String EnDate)  {
		Long numOfDays = null;
		Session session= null;
		String hql=null;
	try {
		session=getSession();
		hql=" SELECT get_duration_dates("+legalEntityCode+",'"+unitCode+"', " + 
				"'"+StDate+"','"+EnDate+"')  AS num_of_days FROM DUAL " ;
		numOfDays=(Long) session.createSQLQuery(hql)
				.addScalar("num_of_days", StandardBasicTypes.LONG)
				.uniqueResult();
	}catch(Exception e) {
		logger.error(e.getMessage(),e);
		throw e;
	}
		return numOfDays;
	}

	
	
	/**
	 * this method is to check given end date is Valid Business Date 
	 * @param session
	 * @param apv
	 * @param currStartDate
	 * @param currEndDate
	 * @param frequency
	 * @param plnStDate
	 * @param plnEnDate
	 * @return
	 */
	public Map<String, String> isValiedBusinessDate(Session session,AuditProcessV apv,String currStartDate,String currEndDate,String frequency,String plnStDate,String plnEnDate) {
		
		
		Map<String,String> map1=null;
		String hql=" select d.currStartDate from (SELECT DATE(Schedule_date_f("+apv.getLegalEntityCode()+",'"+apv.getUnitId()+"',";
		
		if(currStartDate!=null && !"".equals(currStartDate)) 
				hql+= "'"+currStartDate+"'";
				
				if(currEndDate!=null && !"".equals(currEndDate)) 
				hql+= "'"+currEndDate+"'";	
			
		        hql+= ",0,1)) AS currStartDate "
				+ " FROM DUAL) d " +
				" WHERE 1=1 " ;
		     
		      
	/*	if(currStartDate!=null && plnStDate!=null && plnEnDate!=null ) {
			 hql+= " AND d.currStartDate = CAST('"+currStartDate+"' AS DATE) " ;
		     hql+= " AND d.currStartDate >= CAST('"+plnStDate+"' AS DATE) " ;
		     hql+= " AND d.currStartDate <= CAST('"+plnEnDate+"' AS DATE) " ;
	    }
		
		if(currEndDate!=null && plnStDate!=null) {		
			 hql+= " AND d.currStartDate = CAST('"+currEndDate+"' AS DATE) " ;
			hql+= " AND d.currStartDate >= CAST('"+plnStDate+"' AS DATE) " ;				
	    }  */
		
		
		if(currStartDate!=null && !"".equals(currStartDate)) {
			 hql+= " AND d.currStartDate = CAST('"+currStartDate+"' AS DATE) "; 
		} 
		
		if(currEndDate!=null && !"".equals(currEndDate)) {		
			 hql+= " AND d.currStartDate = CAST('"+currEndDate+"' AS DATE) " ;							
	    }
		
		if((plnStDate!=null && !"".equals(plnStDate))) {
			 hql+= " AND d.currStartDate >= CAST('"+plnStDate+"' AS DATE) "; 
		}
		
		if((plnEnDate!=null && !"".equals(plnEnDate))) {			
		    hql+= " AND d.currStartDate <= CAST('"+plnEnDate+"' AS DATE) " ;
	    }
		
		String str= (String) session.createSQLQuery(hql)	
				.addScalar("currStartDate", StandardBasicTypes.STRING)				
				.uniqueResult();
		if(str!=null) {
			map1=new HashMap<String,String>();
			map1.put("validDate","1");
		}else {
			map1=new HashMap<String,String>();			
			map1.put("validDate","0");
		}
		
		return map1;

	}
	

		
	public String previousAuditDetails(int legalEntityCode,String auditType,String unitCode,String auditId) {
		
		String hql=" SELECT " + 
				" concat(ash.audit_id,',',ash.plan_id,',',ash.audit_type_code) as auditId " + 
				" FROM audit_schedule ash, " + 
				" (SELECT legal_entity_code,audit_type_code,unit_id, audit_id, schedule_start_date " + 
				" FROM audit_schedule " + 
				" where 1=1 " + 
				" and audit_id = '"+auditId+"' " + 
				" and legal_entity_code =  " + legalEntityCode +
				" and unit_id =  '" + unitCode +"' "+
				" and audit_type_code = '"+auditType+"' " + 
				" ) as ash1 " + 
				" where ash.legal_entity_code = ash1.legal_entity_code " + 
				" and ash.audit_type_code = ash1.audit_type_code " + 
				" and ash.unit_id = ash1.unit_id " + 
				" and IFNULL(ash.actual_end_date,ash.schedule_start_date) <  ash1.schedule_start_date " + 
				" order by ash.actual_end_date desc " +
				" LIMIT 1 ";
		
		Session session=getSession();
		      		
		Object obj= session.createSQLQuery(hql)	
				.addScalar("auditId", StandardBasicTypes.STRING)				
				.uniqueResult();
		
		return obj!=null ? (String)obj:null;

	}
	
	
	/**
	 * this method is to give legend information after plan validation happened     
	 * @param legalEntityCode
	 * @param auditType
	 * @param maker
	 * @return
	 */
public Object[] isValiedPlan(int legalEntityCode,String auditType,String maker) {
	   Session session=getSession(); 
	
	//	String hql="select * from audit_process_validate_legend ";
		
		
		List<AuditProcessValidate> auditSchedules=null;
		logger.info(" call audit_process_validate_legend("+legalEntityCode + ",'"+ auditType+"','"+maker+"')");
		  
		     Query query = session.createSQLQuery(" CALL audit_process_validate_legend(:legalEntityCode,:auditTypeCcode,:maker) ")	
		    		    .addScalar("ValidData", StandardBasicTypes.STRING)
						.addScalar("ErrorExist", StandardBasicTypes.STRING)						
						.addScalar("ErrorCountZero", StandardBasicTypes.STRING)
						.addScalar("ErrorCountOne", StandardBasicTypes.STRING)
						.addScalar("ErrorCountTwo", StandardBasicTypes.STRING)
						.addScalar("ErrorCountThree", StandardBasicTypes.STRING)
						.addScalar("ErrorCountFour", StandardBasicTypes.STRING)
						.addScalar("ErrorCountFive", StandardBasicTypes.STRING)
						
						.addScalar("noneValidtedRecords", StandardBasicTypes.STRING)
						.addScalar("totalRecords", StandardBasicTypes.STRING)
						
						.addScalar("errorCountZeroMessage", StandardBasicTypes.STRING)
						.addScalar("errorCountOneMessage", StandardBasicTypes.STRING)
						.addScalar("errorCountTwoMessage", StandardBasicTypes.STRING)
						.addScalar("errorCountThreeMessage", StandardBasicTypes.STRING)
						.addScalar("errorCountFourMessage", StandardBasicTypes.STRING)
						.addScalar("errorCountFiveMessage", StandardBasicTypes.STRING)
						
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCcode", auditType)
		        .setParameter("maker", maker);
		     
		     Object[] obj=(Object[])query.uniqueResult();
		     
		    
		
		//Object[] obj=(Object[]) session.createSQLQuery(query)
				/*.addScalar("ValidData", StandardBasicTypes.STRING)
				.addScalar("ErrorExist", StandardBasicTypes.STRING)
				
				.addScalar("ErrorCountZero", StandardBasicTypes.STRING)
				.addScalar("ErrorCountOne", StandardBasicTypes.STRING)
				.addScalar("ErrorCountTwo", StandardBasicTypes.STRING)
				.addScalar("ErrorCountThree", StandardBasicTypes.STRING)
				.addScalar("ErrorCountFour", StandardBasicTypes.STRING)
				.addScalar("ErrorCountFive", StandardBasicTypes.STRING)
				
				.addScalar("noneValidtedRecords", StandardBasicTypes.STRING)
				.addScalar("totalRecords", StandardBasicTypes.STRING)
				
				.addScalar("errorCountZeroMessage", StandardBasicTypes.STRING)
				.addScalar("errorCountOneMessage", StandardBasicTypes.STRING)
				.addScalar("errorCountTwoMessage", StandardBasicTypes.STRING)
				.addScalar("errorCountThreeMessage", StandardBasicTypes.STRING)
				.addScalar("errorCountFourMessage", StandardBasicTypes.STRING)
				.addScalar("errorCountFiveMessage", StandardBasicTypes.STRING)
								
				.uniqueResult();*/
		
		return obj;

	}
	
	
/**
 * 
 * @param legalEntityCode
 * @param unitId
 * @param currentStartDate
 * @param planStartDate
 * @param planEndDate
 * @return
 */
	public Map<String, String> isValiedBusinessDate(String legalEntityCode,String unitId,String frequency,int duration,String currStartDate,String currEndDate,String plnStDate,String plnEnDate) {

		Session session=getSession();    	
		AuditProcessV apv=new AuditProcessV();
		apv.setLegalEntityCode(legalEntityCode);
		apv.setDuration(duration);
		apv.setUnitId(unitId);

		return this.isValiedBusinessDate(session,apv,currStartDate,currEndDate,frequency,plnStDate,plnEnDate);

	}
	
/**
 * this method is to get Next Plan Start Date.
 */
	public  List<AuditPlanV> getAuditProcessNextPlan(int legalEntityCode,String auditTypeCode) {

		logger.info("Inside getAuditProcessNextPlan .... ");
		List<AuditPlanV> ls=null;

		Session session=getSession();
		String hql=" FROM AuditPlanV where legalEntityCode =:legalEntityCode ";
				
		if(!BankAuditUtil.isEmptyString(auditTypeCode)) {
			 hql+="and  auditTypeCode=:auditTypeCode ";		
			 hql+=" order by auditTypeDesc asc,planId*1 asc   ";
			 ls=session.createQuery(hql).setParameter("legalEntityCode",legalEntityCode).setParameter("auditTypeCode",auditTypeCode).list();
		}else {
			hql+=" order by auditTypeDesc asc,planId*1 asc ";
			ls=session.createQuery(hql).setParameter("legalEntityCode",legalEntityCode).list();
		}
		
		session.setFlushMode(FlushMode.MANUAL);
		session.clear();
		
		logger.info("ls .."+ls);
		List<AuditPlanV> ls1= new ArrayList<>();
		Session session1=getSession();
		SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
		try {
			for(AuditPlanV av: ls) {
				 if(av.getPlanStartDate().compareTo(av.getPlanEndDate())>0){
					 String hql1="SELECT get_financial_year_endDate_forDate("+legalEntityCode+", '"+sf.format(av.getPlanStartDate())+"') FROM DUAL;"; //Need to comment, financial year end date
					 Date dt=(Date)session.createNativeQuery(hql1).getSingleResult();
					 av.setPlanEndDate(dt);
				 }
				 ls1.add(av);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}
		logger.info("ls .."+ls1);		
		return ls;
	}


	/**
	 * this method is to get Next Plan Start Date.
	 */
		public  List<AuditPlanV> getAuditProcessNextPlan(int legalEntityCode,String auditTypeCode,String plannerUnitCode) {

			logger.info("Inside getAuditProcessNextPlan .... :: "+legalEntityCode+ " :: "+auditTypeCode +" :: "+plannerUnitCode);
			List<AuditPlanV> ls=null;
			AuditPlanV apv=null;

			Session session=getSession();
			String hql=" SELECT (DATE_ADD(IFNULL(MAX(plan_end_date),CURRENT_DATE), INTERVAL 1 DAY)) AS plan_start_date,  "
					+ " (SELECT plan_end_date FROM  audit_process_plan_end_date_v v WHERE legal_entity_code=:legalEntityCode) as plan_end_date "
					+ "	FROM audit_plan "
					+ "	WHERE planner_unit_code=:plannerUnitCode AND audit_type_code=:auditTypeCode AND "
					+ "		legal_entity_code=:legalEntityCode";
			Object obj= session.createSQLQuery(hql).setParameter("legalEntityCode",legalEntityCode).setParameter("plannerUnitCode",plannerUnitCode)
					.setParameter("auditTypeCode",auditTypeCode).uniqueResult();
			
			Date planNxtStartDate=null, planNxtEndDate=null; 
			
			apv = new AuditPlanV();
			if(obj != null  && ((Object[])obj).length > 0){
				planNxtStartDate= (Date)((Object[])obj)[0];
				planNxtEndDate = (Date)((Object[])obj)[1];
				logger.info("planNxtStartDate.. "+ planNxtStartDate +" planNxtEndDate.. "+planNxtEndDate);
			}			
			apv.setPlanStartDate(planNxtStartDate);
			apv.setPlanEndDate(planNxtEndDate);
			
			session.setFlushMode(FlushMode.MANUAL);
			session.clear();
			
			Session session1=getSession();
			SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
			try {
				if(planNxtStartDate.compareTo(planNxtEndDate)>0){
						 String hql1="SELECT get_financial_year_endDate_forDate("+legalEntityCode+", '"+sf.format(planNxtStartDate)+"') FROM DUAL;"; //Need to comment, financial year end date
						 Date dt=(Date)session.createNativeQuery(hql1).getSingleResult();
						 apv.setPlanEndDate(dt);
				}				
			}catch(Exception e) {
				e.printStackTrace(System.out);
			}
			if(apv !=null ) {
				ls= new ArrayList<>();
				ls.add(apv);
			}
			return ls;
		}

	
	@SuppressWarnings({ "unchecked", "serial", "deprecation" })
	public String getAdhocUnitSheduleDetails(int legalEntityCode,String auditTypeCode,String unitCode,String currBusinessDate){
		System.out.println("getAdhocUnitSheduleDetails ...."+ legalEntityCode + "" + auditTypeCode +" :: "+ unitCode+" ::"+ currBusinessDate  );
		
		String hql ="";		
		hql+="CALL audit_sheduled_details(:legalEntityCode,:auditTypeCcode,:unitCode,:currBusinessDate)";
		Session session=getSession();
		String str = (String) session.createSQLQuery(hql)
				.addScalar("v_temp", StandardBasicTypes.STRING)
				.setParameter("legalEntityCode", legalEntityCode)
			    .setParameter("auditTypeCcode", auditTypeCode)
			    .setParameter("unitCode", unitCode)
			    .setParameter("currBusinessDate", currBusinessDate).uniqueResult();
		
		System.out.println("str ...."+ str );
		return str;
		
	}	
	
	@SuppressWarnings({ "unchecked", "serial", "deprecation" })
	public List<AdhocPlanFinalV> getAdhocPlan(int legalEntityCode,String auditTypeCode,String unitCode,String currBusinessDate){
		List<AdhocPlanFinalV> ls=null;

		Session session=getSession();
		
		logger.info("getAdhocPlan .."+legalEntityCode +" auditTypeCode.."+auditTypeCode+" unitCode.."+unitCode+" currBsnsDt.."+currBusinessDate);
		StringBuilder queryString=new StringBuilder();
		 queryString.append("  SELECT                                                                                                                 ");
		 queryString.append("       t1.legal_entity_code AS legal_entity_code,                                                                        ");
		 queryString.append("       t1.audit_type_code AS audit_type_code,                                                                            ");
		 queryString.append("       t1.unit_code AS unit_code,                                                                                        ");
		 queryString.append("       t1.unit_name AS unit_name,                                                                                        ");
		 queryString.append("       t1.unit_type AS unit_type,                                                                                        ");
		 
		 queryString.append("       t1.controlling_office AS controlling_office,                                                                      ");
		 queryString.append("       t1.unit_head_user AS unit_head_user,                                                                              ");
		 queryString.append("       t1.u_criticality AS u_criticality,                                                                                ");
		 queryString.append("       t1.classification AS classification,                                                                              ");
		 queryString.append("       t1.frequency AS frequency,                                                                                        ");
		 
		 queryString.append("       t1.duration AS duration,                                                                                          ");
		 queryString.append("       t1.man_days AS man_days,                                                                                          ");
		 
		 queryString.append("       t2.audit_id AS audit_id,                                                                                          ");
		 queryString.append("       t2.plan_id AS plan_id,                                                                                            ");
		 queryString.append("       t2.start_date AS start_date,                                                                                      ");
		 
		 queryString.append("       t2.end_date AS end_date,                                                                                          ");
		 queryString.append("       t2.report_submitted_date AS report_submitted_date,                                                                ");
		 queryString.append("       t2.auditor AS auditor,                                                                                            ");
		 queryString.append("       t2.assessed_rating AS assessed_rating,                                                                            ");
		 queryString.append("       t2.effective_rating AS effective_rating,                                                                          ");
		 
		 //queryString.append("       t2.audit_status AS audit_status,                                                                                  "); 
		 
		 queryString.append(" (select concat(g.value,'-',g.description) from general_parameter g where g.key_1='STATUS' AND g.key_2='AUDIT_STATUS' " + 
		 		            " and g.value =  t2.audit_status  and g.legal_entity_code = t2.legal_entity_code )  AS audit_status, ");
		 
		 
		 queryString.append("       t2.total_observations AS total_observations,                                                                      ");
		 queryString.append("       t2.respond_status + t2.review_status AS open_status,                                                              ");
		 queryString.append("       t2.respond_status AS respond_status,                                                                              ");
		 queryString.append("       t2.review_status AS review_status,                                                                                ");
		 queryString.append("       t2.response_reject AS response_reject,                                                                            ");
		 queryString.append("       t2.plan_comments AS plan_comments                                                                                 ");
		 queryString.append("   FROM                                                                                                                  ");
		 queryString.append("       adhoc_plan_unit_details_v as t1                                                                                   ");
		 queryString.append("       LEFT JOIN adhoc_plan_schedule_details_v as t2 ON (t1.legal_entity_code = t2.legal_entity_code                     ");
		 queryString.append("           AND t1.audit_type_code = t2.audit_type_code                       									          ");
		 queryString.append("           AND t1.unit_code = t2.unit_id                                    										      ");
		 queryString.append("           AND t2.plan_id IS NOT NULL                                                                                    ");
		 queryString.append("           AND t2.audit_id IS NOT NULL                                                       							  ");
		 queryString.append("           and t2.audit_id = adhoc_audit_shedule_date(t2.legal_entity_code,t2.audit_type_code,t2.unit_id,:currBusinessDate) ");
		 
		/* queryString.append("      AND (t1.response_time = IFNULL(t2.response_time,t1.response_time))                   " + 
		 		"            AND ( t1.rating = IFNULL(t2.rating_id,(SELECT" + 
		 		"                    risk_score_percentage.risk_category_code              " + 
		 		"                FROM" + 
		 		"                    risk_score_percentage " + 
		 		"                WHERE" + 
		 		"                    (risk_score_percentage.maximum_per , risk_score_percentage.legal_entity_code,risk_score_percentage.audit_type_code) IN " + 
		 		"                 (SELECT" + 
		 		"                        MAX(risk_score_percentage.maximum_per)," + 
		 		"                        risk_score_percentage.legal_entity_code," + 
		 		"                        risk_score_percentage.audit_type_code                            " + 
		 		"                    FROM" + 
		 		"                        risk_score_percentage                               " + 
		 		"                    WHERE" + 
		 		"                        ((risk_score_percentage.legal_entity_code =:legalEntityCode)                    " + 
		 		"                        AND (risk_score_percentage.audit_type_code=:auditTypeCode))                      " + 
		 		"                    GROUP BY" + 
		 		"                        risk_score_percentage.legal_entity_code ," + 
		 		"                        risk_score_percentage.audit_type_code))))             " + 
		 		"            AND ( t1.revenue_leakage = IFNULL(t2.revenue_leakage, 0)  )    													");*/
		 		
		 queryString.append("           ) where  t1.legal_entity_code=:legalEntityCode AND t1.audit_type_code=:auditTypeCode AND t1.unit_code=:unitCode ");
		 queryString.append("         ORDER BY t1.legal_entity_code, t1.audit_type_code, t1.unit_code  LIMIT 1  		                    ");
		
		 logger.info("Query String : "+queryString.toString());
		 
		 SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
			Query query=(Query) session.createNativeQuery(queryString.toString())				
					.setResultTransformer(new ResultTransformer() {
					   
						@Override
						public Object transformTuple(Object[] tuple, String[] aliases) {
							AdhocPlanFinalV apfv=null;
							if(tuple[0]!=null) {
								try {
								apfv= new AdhocPlanFinalV();
								if(tuple[0]!=null) apfv.setLegalEntityCode(Integer.valueOf(tuple[0].toString()).intValue());
								if(tuple[1]!=null) apfv.setAuditTypeCode(tuple[1].toString());
								if(tuple[2]!=null) apfv.setUnitCode(tuple[2].toString());
								if(tuple[3]!=null) apfv.setUnitName(tuple[3].toString());
								if(tuple[4]!=null) apfv.setUnitType(tuple[4].toString());
								
								if(tuple[5]!=null) apfv.setControllingOffice(tuple[5].toString());
								if(tuple[6]!=null) apfv.setUnitHeadUser(tuple[6].toString());
								if(tuple[7]!=null) apfv.setuCriticality(tuple[7].toString());
								if(tuple[8]!=null) apfv.setClassification(tuple[8].toString());
								if(tuple[9]!=null) apfv.setFrequency(tuple[9].toString());
								
								if(tuple[10]!=null) apfv.setDuration(tuple[10].toString());
								if(tuple[11]!=null) apfv.setManDays(tuple[11].toString());
								if(tuple[12]!=null) apfv.setAuditId(tuple[12].toString());
								if(tuple[13]!=null) apfv.setPlanId(tuple[13].toString());
								if(tuple[14]!=null) apfv.setStartDate(formatter.parse(tuple[14].toString()));
								
								if(tuple[15]!=null) apfv.setEndDate(formatter.parse(tuple[15].toString()));
								if(tuple[16]!=null) apfv.setReportSubmittedDate(formatter.parse(tuple[16].toString()));
								if(tuple[17]!=null) apfv.setAuditor(tuple[17].toString());
								if(tuple[18]!=null) apfv.setAssessedRating(tuple[18].toString());
								if(tuple[19]!=null) apfv.setEffectiveRating(tuple[19].toString());
								
								if(tuple[20]!=null) apfv.setAuditStatus(tuple[20].toString());
								if(tuple[21]!=null) apfv.setTotalObservations(Integer.valueOf(tuple[21].toString()).intValue());
								if(tuple[22]!=null) apfv.setOpen(Long.valueOf(tuple[22].toString()).longValue());
								if(tuple[23]!=null) apfv.setResponded(Long.valueOf(tuple[23].toString()).longValue());
								if(tuple[24]!=null) apfv.setReviewed(Long.valueOf(tuple[24].toString()).longValue());
								if(tuple[25]!=null) apfv.setRejected(Long.valueOf(tuple[25].toString()).longValue());
								if(tuple[26]!=null) apfv.setPlanComments(tuple[26].toString());
								
								}catch (Exception e) {
									logger.error(e.getMessage(),e);
								}
							}
							return apfv;
						}

						@Override
						public List transformList(List collection) {
							return collection;
						}
					}).setParameter("legalEntityCode",legalEntityCode)
					 .setParameter("auditTypeCode",auditTypeCode)
					 .setParameter("unitCode",unitCode)					 
					 .setParameter("currBusinessDate",currBusinessDate);
					ls=query.list();
							
						
		session.setFlushMode(FlushMode.MANUAL);
		session.clear();
		return ls;
	}
	
	@SuppressWarnings({ "unchecked", "serial", "deprecation" })
	/*
	 public List<AdhocPlanFinalV> getAdhocPlan(int legalEntityCode,String auditTypeCode,String planId){
		List<AdhocPlanFinalV> ls=null;

		Session session=getSession();
				
		StringBuilder queryString=new StringBuilder();
		 queryString.append("  SELECT                                                                                                                 ");
		 queryString.append("       t1.legal_entity_code AS legal_entity_code,                                                                        ");
		 queryString.append("       t1.audit_type_code AS audit_type_code,                                                                            ");
		 queryString.append("       t1.unit_code AS unit_code,                                                                                        ");
		 queryString.append("       t1.unit_name AS unit_name,                                                                                        ");
		 queryString.append("       t1.unit_type AS unit_type,                                                                                        ");
		 
		 queryString.append("       t1.controlling_office AS controlling_office,                                                                      ");
		 queryString.append("       t1.unit_head_user AS unit_head_user,                                                                              ");
		 queryString.append("       t1.u_criticality AS u_criticality,                                                                                ");
		 queryString.append("       t1.classification AS classification,                                                                              ");
		 queryString.append("       t1.frequency AS frequency,                                                                                        ");
		 
		 queryString.append("       t1.duration AS duration,                                                                                          ");
		 queryString.append("       t1.man_days AS man_days,                                                                                          ");
		 
		 queryString.append("       t2.audit_id AS audit_id,                                                                                          ");
		 queryString.append("       t2.plan_id AS plan_id,                                                                                            ");
		 queryString.append("       t2.start_date AS start_date,                                                                                      ");
		 
		 queryString.append("       t2.end_date AS end_date,                                                                                          ");
		 queryString.append("       t2.report_submitted_date AS report_submitted_date,                                                                ");
		 queryString.append("       t2.auditor AS auditor,                                                                                            ");
		 queryString.append("       t2.assessed_rating AS assessed_rating,                                                                            ");
		 queryString.append("       t2.effective_rating AS effective_rating,                                                                          ");
		 
		// queryString.append("       t2.audit_status AS audit_status,                                                                                  ");
		 
		 queryString.append(" (select concat(g.value,'-',g.description) from general_parameter g where g.key_1='STATUS' AND g.key_2='AUDIT_STATUS' " + 
		 		            " and g.value =  t2.audit_status  and g.legal_entity_code = t2.legal_entity_code )  AS audit_status, ");
		 
		 queryString.append("       t2.total_observations AS total_observations,                                                                      ");
		 queryString.append("       t2.respond_status + t2.review_status AS open_status,                                                              ");
		 queryString.append("       t2.respond_status AS respond_status,                                                                              ");
		 queryString.append("       t2.review_status AS review_status,                                                                                ");
		 
		 queryString.append("       t2.response_reject AS response_reject,                                                                             ");
		 queryString.append("       t2.plan_comments AS plan_comments,                                                                             ");
		 queryString.append("   FROM                                                                                                                  ");
		 queryString.append("       adhoc_plan_unit_details_v as t1                                                                                   ");
		 queryString.append("       LEFT JOIN adhoc_plan_schedule_v as t2 ON (t1.legal_entity_code = t2.legal_entity_code                     ");
		 queryString.append("           AND t1.audit_type_code = t2.audit_type_code                       									          ");
		 queryString.append("           AND t1.unit_code = t2.unit_id                                    										      ");
		 queryString.append("           AND t2.plan_id IS NOT NULL                                                                                    ");
		 queryString.append("           AND t2.audit_id IS NOT NULL                                                       							  ");
		// queryString.append("           and t2.audit_id = adhoc_audit_shedule_date(t2.legal_entity_code,t2.audit_type_code,t2.unit_id,:currBusinessDate) ");
		 queryString.append("           ) where 1=1  AND t1.legal_entity_code=:legalEntityCode AND t1.audit_type_code=:auditTypeCode and t2.plan_id=:planId");
		 
		 
		 
		 
		 
		 queryString.append("   ORDER BY t1.legal_entity_code * 1 , t1.audit_type_code * 1                                            "); 
		
		 System.out.println("Query String : "+queryString.toString());
		 
		 SimpleDateFormat formatter = new SimpleDateFormat("yyyy-mm-dd");
			Query query=(Query) session.createNativeQuery(queryString.toString())				
					.setResultTransformer(new ResultTransformer() {
					   
						@Override
						public Object transformTuple(Object[] tuple, String[] aliases) {
							AdhocPlanFinalV apfv=null;
							if(tuple[0]!=null) {
								try {
								apfv= new AdhocPlanFinalV();
								if(tuple[0]!=null) apfv.setLegalEntityCode(Integer.valueOf(tuple[0].toString()).intValue());
								if(tuple[1]!=null) apfv.setAuditTypeCode(tuple[1].toString());
								if(tuple[2]!=null) apfv.setUnitCode(tuple[2].toString());
								if(tuple[3]!=null) apfv.setUnitName(tuple[3].toString());
								if(tuple[4]!=null) apfv.setUnitType(tuple[4].toString());
								
								if(tuple[5]!=null) apfv.setControllingOffice(tuple[5].toString());
								if(tuple[6]!=null) apfv.setUnitHeadUser(tuple[6].toString());
								if(tuple[7]!=null) apfv.setuCriticality(tuple[7].toString());
								if(tuple[8]!=null) apfv.setClassification(tuple[8].toString());
								if(tuple[9]!=null) apfv.setFrequency(tuple[9].toString());
								
								if(tuple[10]!=null) apfv.setDuration(tuple[10].toString());
								if(tuple[11]!=null) apfv.setManDays(tuple[11].toString());
								if(tuple[12]!=null) apfv.setAuditId(tuple[12].toString());
								if(tuple[13]!=null) apfv.setPlanId(tuple[13].toString());
								if(tuple[14]!=null) apfv.setStartDate(formatter.parse(tuple[14].toString()));
								
								if(tuple[15]!=null) apfv.setEndDate(formatter.parse(tuple[15].toString()));
								if(tuple[16]!=null) apfv.setReportSubmittedDate(formatter.parse(tuple[16].toString()));
								if(tuple[17]!=null) apfv.setAuditor(tuple[17].toString());
								if(tuple[18]!=null) apfv.setAssessedRating(tuple[18].toString());
								if(tuple[19]!=null) apfv.setEffectiveRating(tuple[19].toString());
								
								if(tuple[20]!=null) apfv.setAuditStatus(tuple[20].toString());
								if(tuple[21]!=null) apfv.setTotalObservations(Integer.valueOf(tuple[21].toString()).intValue());
								if(tuple[22]!=null) apfv.setOpen(Long.valueOf(tuple[22].toString()).longValue());
								if(tuple[23]!=null) apfv.setResponded(Long.valueOf(tuple[23].toString()).longValue());
								if(tuple[24]!=null) apfv.setReviewed(Long.valueOf(tuple[24].toString()).longValue());
								if(tuple[25]!=null) apfv.setRejected(Long.valueOf(tuple[25].toString()).longValue());
								if(tuple[26]!=null) apfv.setPlanComments(tuple[26].toString());
								}catch (Exception e) {
									e.printStackTrace();
								}
							}
							return apfv;
						}

						@Override
						public List transformList(List collection) {
							return collection;
						}
					}).setParameter("legalEntityCode",legalEntityCode)
					 .setParameter("auditTypeCode",auditTypeCode)
					 .setParameter("planId",planId)	;				 
					// .setParameter("currBusinessDate",currBusinessDate);
					ls=query.list();
						
		session.setFlushMode(FlushMode.MANUAL);
		session.clear();
		return ls;
	}
	*/
	
	/**
	 * this method is user to check auditors are configured for the selected audit type 
	 */
	public boolean isAuditorsExist(int legalEntityCode) throws Exception {
		Session session=getSession();
		Object obj = null;
		boolean flag=false;
		logger.info("isAuditorsExist .. legalEntityCode "+ legalEntityCode);
		try {
			
			String hql = " SELECT CASE WHEN COUNT(*) > 0 THEN 'Y' ELSE 'N' END AS userexist " + 
					" FROM ( " + 
					" SELECT " + 
					" usr.user_id, " + 
					" CONCAT(usr.first_name,'',usr.last_name) user_name, " + 
					" usr.state, " + 
					" ' ' audit_type_code	" + 
					" FROM user usr,user_role_mapping urm " + 
					" WHERE usr.legal_entity_code =  " + legalEntityCode + 
					" AND urm.legal_entity_code=usr.legal_entity_code " + 
					" AND urm.status='A' " + 
					" AND usr.status='A' " + 
					" AND urm.user_role_id LIKE '%G2%' " + 
					" AND urm.user_id=usr.user_id " + 
					" GROUP BY usr.user_id,user_name,usr.state,audit_type_code " + 
					" ORDER BY user_name ASC) AS t ";
			
			obj = session.createSQLQuery(hql)
					.addScalar("userexist", StandardBasicTypes.STRING)
					.uniqueResult();
			if(obj!=null) {
				logger.info("Object Value :"+ obj );
				flag=((String)obj).equals("Y")?true:false;
			}
		}catch (Exception e) {
			e.getStackTrace();
			throw e;
		}
		return  flag;
	}

	
	
	
	public Map financialYearDates(int legalEntityCode) {
		Session session=getSession();
		Map mp = null;
		try {
			
			String hql = " select DATE_ADD(DATE_SUB(plan_end_date, INTERVAL 12 MONTH),INTERVAL 1 DAY) as plan_start_date,plan_end_date " + 
					"from audit_process_plan_end_date_v where legal_entity_code= "+legalEntityCode;
			
			List ls = session.createSQLQuery(hql)
					.addScalar("plan_start_date", StandardBasicTypes.STRING)
					.addScalar("plan_end_date", StandardBasicTypes.STRING)
					//.setParameter("legalEntityCode", legalEntityCode)
					.list();
			Iterator iterator = ls.iterator();
			while(iterator.hasNext()){  
	            Object[] objects = (Object[]) iterator.next();  
	            logger.info("plan_start_date"+objects[0]);  
	            logger.info("plan_start_date="+objects[1]);  
	            logger.info("----------------------------");  
	            
	            mp= new HashMap<>();
	            mp.put("startDate", objects[0].toString());
	            mp.put("endDate", objects[1].toString());
	            
	            logger.info(mp.toString());
	        }  
			
		}catch (Exception e) {
			e.getStackTrace();
			throw e;
		}
		return  mp;
	}

	
	public String getWatchListForAllAudits(int legalEntityCode,String auditTypeCode) {
		Session session=getSession();
		Long watchListCount = null;
		try { 
			
			String hql = " select get_watch_list_for_all_audits("+legalEntityCode+",'"+auditTypeCode+"') as watch_list_count from DUAL ";
			
			watchListCount=(Long) session.createSQLQuery(hql)
					.addScalar("watch_list_count", StandardBasicTypes.LONG)
					.uniqueResult();
								
			logger.info("watchListCount "+ watchListCount);
			
		}catch (Exception e) {
			e.getStackTrace();
			throw e;
		}
		return  watchListCount+"";
	}
	
	
	public Long getTargetAudits(int legalEntityCode,String units,String auditTypeCode,String maker) {
		Session session=getSession();
		Long noOfUnits = null;
		try {
			
			StringBuilder hql = new StringBuilder(" SELECT count(unit_id) as cnt from audit_process_temp2 where 1=1 " + 
					" and  unit_id in ("+units+") and legal_entity_code ="+legalEntityCode+" and maker = '"+maker+"' " ); 
			
			 if(!BankAuditUtil.isEmptyString(auditTypeCode)){
				 hql.append(" and audit_type_code='"+auditTypeCode+"' "); // Included to get the data Audit wise.
			 }
			 
			 
			 noOfUnits = (Long)session.createSQLQuery(hql.toString())
						.addScalar("cnt", StandardBasicTypes.LONG)
						.uniqueResult(); 
			
		}catch (Exception e) {
			e.getStackTrace();
			throw e;
		}
		return  (noOfUnits!=null && noOfUnits>0)? noOfUnits :0;
	}
	
	/* This method rewritten by changing the Query
	public Map jurisdictionByShedule(int legalEntityCode,String units, String auditTypeCode,String plnStDate,String plnEnDate) {
		Session session=getSession();
		Map mp = null;
		try {
			
			StringBuilder hql = new StringBuilder(" SELECT " + 
					"sum(v.scheduled) as planed, " + 
					"sum(v.closed) as closed, " + 
					"sum(v.initiated+v.responded+v.reviewed+v.submitted) as inProcess " + 
					"FROM audit_schdules_mst_v as v " + 
					"where unit_ids in ("+units+") and legal_entity_code =:legalEntityCode " +
					 " and plan_start_date between '"+plnStDate+"' and '"+plnEnDate+"' ");
			
			 if(!BankAuditUtil.isEmptyString(auditTypeCode)){
				 hql.append(" and audit_type_code=:auditTypeCode "); // Included to get the data Audit wise.
			 }
			
			Query hqlQuery = session.createSQLQuery(hql.toString())
					.addScalar("planed", StandardBasicTypes.STRING)
					.addScalar("closed", StandardBasicTypes.STRING)
					.addScalar("InProcess", StandardBasicTypes.STRING)					
					.setParameter("legalEntityCode", legalEntityCode);
			
			if(!BankAuditUtil.isEmptyString(auditTypeCode)){
				hqlQuery.setParameter("auditTypeCode", auditTypeCode);
			 }
			
			List ls = hqlQuery.list();
			Iterator iterator = ls.iterator();
			while(iterator.hasNext()){  
	            Object[] objects = (Object[]) iterator.next();  
	            logger.info("planed="+objects[0]);  
	            logger.info("closed="+objects[1]);
	            logger.info("inProcess="+objects[2]);  
	            
	            mp= new HashMap<>();
	            mp.put("planed", objects[0].toString());
	            mp.put("closed", objects[1].toString());
	            mp.put("inProcess", objects[2].toString());
	            
	            logger.info(mp.toString());
	        }  
			
		}catch (Exception e) {
			e.getStackTrace();
			throw e;
		}
		return  mp;
	}*/
	
	public Map jurisdictionByShedule(int legalEntityCode,String units, String auditTypeCode,String plnStDate,String plnEnDate) {
		Session session=getSession();
		Map map = null;
		try {
			logger.info("legalEntityCode.. "+legalEntityCode + " units.. "+ units + "auditTypeCode.."+auditTypeCode+" plnStDate.."+plnStDate+" plnEnDate.."+plnEnDate);
			StringBuilder query= new StringBuilder(
					" SELECT  " 
							 + "   (CASE WHEN aus.audit_status = 'AP' THEN 'planed' " 
							 + "         WHEN aus.audit_status IN ('AI') THEN 'inProcess' "
							// + "       WHEN aus.audit_status = 'AI' THEN 'initiated' " 
							// + "		 WHEN aus.audit_status = 'AS' THEN 'submitted' " 
							// + "       WHEN aus.audit_status = 'AR' THEN 'responded' " 
							 + "            ELSE 'closed' " 
							 + "    END)  AS audit_status "		
							 + " 	,COUNT(aus.audit_status) AS audit_count "
	        				 + "   FROM audit_schedule AS aus ,maint_entity AS me " 
							 + "   WHERE  " 
							 + "   aus.legal_entity_code=me.legal_entity_code " 
							 + "   AND aus.unit_id=me.unit_code   " 
							 + "   AND aus.legal_entity_code =:legalEntityCode " 
							 + "   AND aus.audit_status IN ('AI','AP' ,'AS','AR','AW','AC','AF')  " 
							 + "   AND DATE(aus.schedule_start_date) BETWEEN :plnStDate AND :plnEnDate "
					)  ;
			
			query.append(" AND aus.unit_id in ("+units+") ");  //unit_ids in ("+units+")  aus.unit_id in (:units)
						
			if(!BankAuditUtil.isEmptyString(auditTypeCode)){
				query.append(" AND aus.audit_type_code =:auditTypeCode ");
			}
			query.append(
					   "   GROUP BY   "
							   + "   (CASE WHEN aus.audit_status = 'AP' THEN 'planed' " 
							   + "         WHEN aus.audit_status IN ('AI') THEN 'inProcess' "
							// + "       WHEN aus.audit_status = 'AI' THEN 'initiated' " 
							// + "		 WHEN aus.audit_status = 'AS' THEN 'submitted' " 
							// + "       WHEN aus.audit_status = 'AR' THEN 'responded' "  
								 + "            ELSE 'closed' " 
								 + "    END)   ");
	        
			
			Query hqlQuery= session.createNativeQuery(query.toString())
			.setParameter("plnStDate", plnStDate)
			.setParameter("plnEnDate", plnEnDate)
			.setParameter("legalEntityCode", legalEntityCode)
			//.setParameter("units",units)
			;
			if(!BankAuditUtil.isEmptyString(auditTypeCode)){
				hqlQuery.setParameter("auditTypeCode", auditTypeCode);
			}
						
			List ls = hqlQuery.list();
			logger.info("Size lst .."+ls.size());
			Iterator iterator = ls.iterator();
			map= new HashMap<>();
			while(iterator.hasNext()){  
	            Object[] objects = (Object[]) iterator.next();  
	            map.put(objects[0].toString(),objects[1].toString());
	            logger.info(objects[0]+ "= "+objects[1]);
	          }  
			if(!map.containsKey("planed")) map.put("planed","0");
			if(!map.containsKey("inProcess")) map.put("inProcess","0");
			if(!map.containsKey("closed")) map.put("closed","0");
			int total= Integer.parseInt(map.get("planed").toString()) + Integer.parseInt(map.get("inProcess").toString()) +Integer.parseInt(map.get("closed").toString());
			map.put("total",total+"");			
			logger.info("map ...."+map);
		}catch (Exception e) {
			e.getStackTrace();
			throw e;
		}
		return  map;
	}
	
	public List getAllUnits(int legalEntityCode,String search){
		Session session=getSession();
		String hql=" SELECT CONCAT(me.unit_code,'-',me.unit_name) AS unit " + 
				" FROM maint_entity me " + 
				" WHERE me.unit_name LIKE :search " + 
				" and me.legal_entity_code =:legalEntityCode " +
				" GROUP BY me.unit_name,me.unit_code " ;
		return session.createSQLQuery(hql)
				.addScalar("unit", StandardBasicTypes.STRING)
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("search", "%"+search+"%").list();
	}
	
	
	/**
	 * this method is to check Audit Parameters are configured for that audit.
	 */
	public boolean isAuditParametersExist(int legalEntityCode,String auditTypeCode) throws Exception {
		Session session=getSession();
		boolean flag=false;
		logger.info("AuditProcessVDaoImpl isAuditParametersExist .. "+ legalEntityCode +" :: "+ auditTypeCode);
		try {
			
			String hql = " SELECT COUNT(*) " + 
					" FROM MaintEntity med,MaintAuditParameterDerived map " + 
					" WHERE map.legalEntityCode =:legalEntityCode "+ 
					" AND map.auditTypeCode =:auditTypeCode " + 
					" AND med.legalEntityCode = map.legalEntityCode " + 
					" AND med.unitType = map.unitType " + 
					" AND med.uCriticality = map.uCriticality ";
			
			Long count = (Long)session.createQuery(hql)				
					.setParameter("legalEntityCode", legalEntityCode)
					.setParameter("auditTypeCode", auditTypeCode)
					.uniqueResult();
			
			if(count!=null&& count >0){
				flag = true;
			}
			logger.info(" isAuditParametersExist .."+flag);
		}catch (Exception e) {
			e.getStackTrace();
			throw e;
		}
		return  flag;
	}
	
	/**
	 * this method is to fetch audit team members  
	 */
	public List getauditProcessTeam(int legalEntityCode,String auditType,String userId) {
		logger.info("getauditProcessTeam .. "+ legalEntityCode +" :: " +auditType +" :: "+userId);
		Session session=getSession();
		String hql = " SELECT usr.user_id, " + 
					 " CONCAT(usr.first_name,' ',ifnull(usr.last_name,'')) user_name, " + 
					 " usr.state " +					
					 " FROM user usr,user_role_mapping urm  " + 
					 " WHERE usr.legal_entity_code = " + legalEntityCode + 
					 " AND urm.legal_entity_code=usr.legal_entity_code " + 
					 " AND urm.status='A' " + 
					 " AND usr.status='A' " + 
					 " AND urm.entity_status='A' " + 
					 " AND usr.entity_status IN ('A','L') " +  // Users Active and Locked be picked & Users Inactive and Closed not to pick
					 " AND urm.user_role_id LIKE '%G2%' " +   //  Using this ROLE for retrieving the Assessors
					 " AND urm.user_id=usr.user_id " + 
					 " GROUP BY usr.user_id,user_name,usr.state " + 
					 " ORDER BY user_name ASC ";

		List<Object[]> rows = session.createSQLQuery(hql)
				.addScalar("user_id", StandardBasicTypes.STRING)
				//.addScalar("unit_code", StandardBasicTypes.STRING)
				.addScalar("user_name", StandardBasicTypes.STRING)
				.addScalar("state", StandardBasicTypes.STRING)
				//.addScalar("audit_type_code", StandardBasicTypes.STRING) 
				.list();
		
		logger.info("size .. lst .."+ rows.size());
		AuditProcessTeam apt=null;

		List<AuditProcessTeam> ls=new ArrayList<>(); 

		for(Object[] col : rows){
			if(col[0]!=null && col[1]!=null && col[2]!=null /*&& col[3]!=null*/) {
				apt = new AuditProcessTeam();
				apt.setUserId(col[0].toString());
				apt.setUserName(col[1].toString());
				apt.setState(col[2].toString());
				//apt.setAuditTypeCode(col[3].toString());
				ls.add(apt);
			}
		}
		logger.info("getauditProcessTeam .. lst .."+ ls);
		return  ls;
	}

	//@Override
	public List getauditProcessPIOTeam(int legalEntityCode,String auditType,String userId) {
		logger.info("getauditProcessTeam .. "+ legalEntityCode +" :: " +auditType +" :: "+userId);
		Session session=getSession();
		String hql = " SELECT usr.user_id, " + 
					 " CONCAT(usr.first_name,' ',ifnull(usr.last_name,'')) user_name, " + 
					 " usr.state " +					
					 " FROM user usr,user_role_mapping urm  " + 
					 " WHERE usr.legal_entity_code = " + legalEntityCode + 
					 " AND urm.legal_entity_code=usr.legal_entity_code " + 
					 " AND urm.status='A' " + 
					 " AND usr.status='A' " + 
					 " AND urm.entity_status='A' " + 
					 " AND usr.entity_status IN ('A','L') " +  // Users Active and Locked be picked & Users Inactive and Closed not to pick
					 " AND urm.user_role_id LIKE '%G8%' " +   //  Using this ROLE for retrieving the Assessors
					 " AND urm.user_id=usr.user_id " + 
					 " GROUP BY usr.user_id,user_name,usr.state " + 
					 " ORDER BY user_name ASC ";

		List<Object[]> rows = session.createSQLQuery(hql)
				.addScalar("user_id", StandardBasicTypes.STRING)
				//.addScalar("unit_code", StandardBasicTypes.STRING)
				.addScalar("user_name", StandardBasicTypes.STRING)
				.addScalar("state", StandardBasicTypes.STRING)
				//.addScalar("audit_type_code", StandardBasicTypes.STRING) 
				.list();
		
		logger.info("size .. lst .."+ rows.size());
		AuditProcessTeam apt=null;

		List<AuditProcessTeam> ls=new ArrayList<>(); 

		for(Object[] col : rows){
			if(col[0]!=null && col[1]!=null && col[2]!=null /*&& col[3]!=null*/) {
				apt = new AuditProcessTeam();
				apt.setUserId(col[0].toString());
				apt.setUserName(col[1].toString());
				apt.setState(col[2].toString());
				//apt.setAuditTypeCode(col[3].toString());
				ls.add(apt);
			}
		}
		logger.info("getauditProcessTeam .. lst .."+ ls);
		return  ls;
	}
	
	/**
	 * this method is to fetch audit team members  
	 */
	public Long getAllOperationAuditUnits(int legalEntityCode,String auditType,String unitIds)  throws Exception {
		
		Session session=getSession();
		Long noOfUnits = null;
		try {
			
		String hql = " SELECT count( id ) as cnt from audit_subgroup_mapping_v  " +  
					 " WHERE mapping_type='E' and legal_entity_code = " + legalEntityCode ;
		
				if(!BankAuditUtil.isEmptyString(auditType)) {
					hql+= " AND audit_type_code='"+auditType+"' " ;
				}
		
				if(!BankAuditUtil.isEmptyString(unitIds)) {
					hql+= " AND id in ("+unitIds+") " ;
				}
		
				noOfUnits = (Long)session.createSQLQuery(hql)
						.addScalar("cnt", StandardBasicTypes.LONG)
						.uniqueResult();

		}catch (Exception e) {
			e.getStackTrace();
			throw e;
		}
		return  (noOfUnits!=null && noOfUnits>0)? noOfUnits :0;
	}
	
	
	/**
	 * this method is to fetch audit team members  
	 */
	public Long getOperationAuditUnits(int legalEntityCode,String auditType,String unitIds)  throws Exception {
		
		Session session=getSession();
		Long noOfUnits = null;
		try {
		logger.info("getOperationAuditUnits .. auditType::"+auditType +" legalEntityCode::"+ legalEntityCode+" unitIds::"+unitIds);
		String hql = " SELECT count( distinct id ) as cnt from audit_subgroup_mapping_v  " +  
					 " WHERE mapping_type='E' and legal_entity_code = " + legalEntityCode ;
		
				if(!BankAuditUtil.isEmptyString(auditType)) {
					hql+= " AND audit_type_code='"+auditType+"' " ;
				}
		
				if(!BankAuditUtil.isEmptyString(unitIds)) {
					hql+= " AND id in ("+unitIds+") " ;
				}
		
				noOfUnits = (Long)session.createSQLQuery(hql)
						.addScalar("cnt", StandardBasicTypes.LONG)
						.uniqueResult();

		}catch (Exception e) {
			e.getStackTrace();
			throw e;
		}
		return  (noOfUnits!=null && noOfUnits>0)? noOfUnits :0;
	}
	
	
	/**
	 * this method is used to get All Schedule Plan information in landing page. 
	 */
	public DataTableResponse getAuditSchedules(int legalEntityCode,String search,Integer orderColumn,
			String orderDirection, Integer page, Integer size,String maker,String planType) { 

		Session session = getSession();
		session.setDefaultReadOnly(true); 
		logger.info("Inside getAuditSchedules for getting Adhoc Plan legalEntityCode .. "+ legalEntityCode+" maker.. "+maker +" planType.. "+ planType);
		
		
		List<AuditschedulesV> auditSchedules=null;

		DataTableResponse dataTableResponse=new DataTableResponse();
		
		StringBuilder queryString=new StringBuilder(" from AuditschedulesV as asv where asv.legalEntityCode =:legalEntityCode ");
		if(!BankAuditUtil.isEmptyString(planType))
			queryString.append(" and asv.planType =:planType");
		
		/*if(!BankAuditUtil.isEmptyString(maker)) {
			List<String> unitIdsList = maintEntityDao.getSubBranchesGyUserIdOrUnitId(legalEntityCode, "user", maker);
			String sectionStr = "'" + String.join("','", unitIdsList).concat("'");
			queryString.append(" and asv.unitIds in ("+sectionStr +") ");
		}*/
		if(!BankAuditUtil.isEmptyString(maker)) {
			User usr = userService.getUserByLegalEntityCodeAndUserId(legalEntityCode, maker);
			if(usr!=null )queryString.append(" and (asv.plannerUnitCode='"+usr.getUnitCode()+"' OR 'HO'='"+usr.getUnitCode()+"' )"  );
		}
		
		if(!BankAuditUtil.isEmptyString(maker))
			queryString.append(" and ifnull(asv.maker,'') like case status when 'DF' then :maker else '%' end ");
		
		//Code to get only Plan for that Regional office
		// queryString.append(" AND  ifnull(asv.plannerUnitCode,'HO')= 'HO' ");
		
		if(!BankAuditUtil.isEmptyString(search)){
			queryString=queryString.append(" and ( asv.auditTypeDesc like :search "
					+ " or asv.planId like :search "
					+ " or DATE_FORMAT(asv.planStartDate, '%d %b %Y') LIKE :search "
					+ " or DATE_FORMAT(asv.planEndDate, '%d %b %Y') LIKE :search "
					+ " or asv.unitIds like :search "
					+ " or asv.dropped like :search "
					+ " or asv.unplanned like :search "
					+ " or asv.scheduled like :search "
					+ " or asv.initiated like :search "
					+ " or asv.submitted like :search "
					+ " or asv.responded like :search "
				//	+ " or reviewed like :search "
					+ " or asv.closed like :search "					
					+ " or asv.maker like :search "
					+ " or asv.description like :search "					
					+ " ) "
					);
		}

		String columns[]={
				"asv.auditTypeDesc",
				"asv.planId",
				"asv.planStartDate",
				"asv.planEndDate",
				"asv.unitIds*1",
				"asv.dropped*1",
				"asv.unplanned*1",
				"asv.scheduled*1",
				"asv.initiated*1",
				"asv.submitted*1",
				"asv.responded*1",
				//"reviewed*1",
				"asv.closed*1",
				"asv.maker",
				"asv.description"
		};

		if(orderColumn!=null && !BankAuditUtil.isEmptyString(orderDirection)){
			if(orderColumn == -1) {
			    queryString=queryString.append(" order by " 
					+ " case "  
					+ " when asv.status = 'DF' then 0 "  
					+ " else 1 "  
					+ " end asc ," 
					+ " asv.makerTimestamp desc ");
			}else {		
				queryString=queryString.append("  order by "+columns[orderColumn]).append(" ").append(orderDirection);
			}
		}
		logger.info(" Query is :: "+ queryString);
		logger.info("legalEntityCode .. "+ legalEntityCode+" maker.. "+maker +" planType.. "+ planType+" search.."+search+"..");
		//Query query=null;

		//try {

		 Query query=session.createQuery(queryString.toString()).setParameter("legalEntityCode",legalEntityCode);
		
		if(!BankAuditUtil.isEmptyString(planType))
		query.setParameter("planType", planType);
		
		if(!BankAuditUtil.isEmptyString(maker))
		query.setParameter("maker", "%"+maker.trim()+"%");	
		
		if(!BankAuditUtil.isEmptyString(search))
		query.setParameter("search", "%"+search+"%"); 
		
		
		/*}catch (Exception e) {
			logger.error(e.getMessage(),e);
		}*/


		ScrollableResults resultScroll = query.scroll(ScrollMode.SCROLL_INSENSITIVE);
		if(resultScroll.first()&&resultScroll.scroll(page*size)){

			auditSchedules= new ArrayList<>();
			AuditschedulesV asv=null;
			int i = 0;
			//int j = 0;
			while (size > i++) {
				
				asv=(AuditschedulesV) resultScroll.get(0);
				
				// ------------- IF(v.maker='SYSTEM','DF',!'DF') ---------------
				
				/*System.out.println("Maker :"+maker);
				System.out.println("Maker :"+asv.getMaker());
				System.out.println(" DF :"+asv.getStatus());*/
				
				/*if(asv.getMaker()!=null && !BankAuditUtil.isEmptyString(maker) && asv.getMaker().trim().contains((maker.trim())) && "DF".equals(asv.getStatus().trim())) {
					auditSchedules.add((asv));
				}else {
					if(!"DF".equals(asv.getStatus())) {
					    auditSchedules.add((asv));
					}
				}*/
				
				auditSchedules.add(asv);
				if (!resultScroll.next())
					break;
			}
			dataTableResponse.setData(auditSchedules);
			resultScroll.last();
			if(size!=0 && auditSchedules!=null && auditSchedules.size()>0){
				
				//dataTableResponse.setRecordsFiltered(new Long(auditSchedules.size()));
				//dataTableResponse.setRecordsTotal(new Long(auditSchedules.size()));
				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;
	}

	
	
	
	
/**
 * this method is to delete old plan data from temporary table.
 */
	@Override
	
	public void deleteAuditPlanTemp(int legalEntityCode,String maker) {
		Session session=getSession();
		session.createQuery("DELETE FROM AuditProcessValidate where legalEntityCode=:legalEntityCode and maker=:maker").setParameter("maker", maker).setParameter("legalEntityCode", legalEntityCode).executeUpdate();	
		session.flush();
		session.clear();
	}
	
	/**
	 * this method is to validate current plan
	 */
	public List<AuditProcessValidate> validateAuditProcess(int legalEntityCode,String auditType,Date psDate,Date peDate,String planId,String maker) {
	List<AuditProcessValidate> auditSchedules=null;
	
	 
		 logger.info("  -->  call check_dates_are_repeats("+legalEntityCode + ",'"+ auditType+"','"+planId+"','"+maker+"')");
	     Session session=getSession();
	     
	     Query query = session.createSQLQuery("  CALL check_dates_are_repeats(:legalEntityCode,:auditTypeCcode,:planId,:maker) ")
			.addEntity(AuditProcessValidate.class)
			.setParameter("legalEntityCode", legalEntityCode)
			.setParameter("auditTypeCcode", auditType)
			.setParameter("planId", planId)
	        .setParameter("maker", maker);	   
	     return auditSchedules = query.list();  
	}
	
	
	public List<AuditProcessValidate> getValidateAuditProcess(int legalEntityCode,String auditTypeCode){
		Session session=getSession();
		String hql=" from AuditProcessValidate apv where apv.auditTypeCode =:auditTypeCode "
				+ " and apv.legalEntityCode =:legalEntityCode ";
		return session.createQuery(hql).setParameter("auditTypeCode", auditTypeCode).setParameter("legalEntityCode", legalEntityCode).list();
	}
	 
	
/** 
 * 
 */
	@Transactional
	public void createAuditPlanTemp(List<AuditProcessValidate> auditProcessValidateList) {
		AuditProcessValidate vl=null;
		Session session=getSession();
		try {
			List<AuditProcessValidate> auditProcessList = new ArrayList<>();
			List<Long> rowNumberLst = new ArrayList<>();
			
			//To Avoid duplicate rownums for plan
			for (AuditProcessValidate auditProcessValidate : auditProcessValidateList) {
				if(!rowNumberLst.contains(auditProcessValidate.getRowNum())) {
					rowNumberLst.add(auditProcessValidate.getRowNum());
					auditProcessList.add(auditProcessValidate);
				}
			}
			
			
			for (AuditProcessValidate auditProcessValidate : auditProcessList) {
				
				logger.info("createAuditPlanTemp .."+auditProcessValidate);
				
				vl=auditProcessValidate;
				vl.setRemarkAuditCodes(null);
				vl.setRemarkAuditIds(null);
				vl.setRemarkDesc(null);
				vl.setRemarkErrorCodes(null);
				vl.setRemarkPlanIds(null);
				vl.setRemarkRowNum(null);
				vl.setRemarkUnitIds(null);	
				vl.setPlanAuditRemarks(null);
				
				session.save(vl);	
								
			}
			
			session.flush();
			session.clear();
			
			
		}catch (RuntimeException e) {
			logger.error(e.getMessage(),e);
			throw e;
		}
	}
	
	/**
	 * this method is to  skill set validation 
	 * 
	 * @param legalEntityCode
	 * @param userId
	 * @param unitCode
	 * @param auditTypeCode
	 * @return
	 */
	public String skillSetValidation(String legalEntityCode,String userId,String unitCode,String auditTypeCode) {
		
		Session session=getSession();
		String skillSetValidation="Passed";
		
		StringBuilder queryString=new StringBuilder(" select e.audit_group_code as entAdGrpcd,e.audit_sub_group_code,m.audit_group_code as usrAdGrpcd,m.id from "
				+ " (select m.audit_group_code,m.audit_sub_group_code,m.legal_entity_code from maint_entity_audit_subgroup_mapping m, maint_audit_subgroup s "
				+ "	where m.audit_group_code=s.audit_group_code and m.audit_sub_group_code=s.audit_sub_group_code and s.audit_type_code=:auditTypeCode and "
				+ "	m.legal_entity_code=s.legal_entity_code and m.id=:unitCode and m.mapping_type='E' and m.legal_entity_code=:legalEntityCode "
				+ "	order by m.audit_group_code,m.audit_sub_group_code) as e "
				+ " left outer join(select * from  maint_entity_audit_subgroup_mapping m where "
				//+ "  m.id in (:userId)"
				+ " FIND_IN_SET(m.id,:userId) "
				+ " ) m "
				+ " on e.audit_group_code = m.audit_group_code and e.audit_sub_group_code=m.audit_sub_group_code and e.legal_entity_code=m.legal_entity_code and m.mapping_type='U' ");

		Query query2lst=session.createSQLQuery(queryString.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("userId", userId)
				.setParameter("unitCode", unitCode)
				.setParameter("auditTypeCode", auditTypeCode);

		List lst = query2lst.list();
	//	HashMap<String, String> userDtls = new HashMap<String, String>();
		for ( Iterator iter = lst.iterator(); iter.hasNext(); ) {
			Object[] obj = (Object[]) iter.next();
			try{
				String eAuditGrp=obj[0].toString();
				String uAuditGrp=obj[2].toString();
				logger.info("eAuditGrp is .... "+ eAuditGrp +" uAuditGrp ...." +uAuditGrp);
			}catch(Exception e) {
				logger.error(e.getMessage());
			}
			if(obj[0] != null && obj[2] == null ){
				logger.info("Entity .... "+obj[0].toString());
				skillSetValidation="Failed";
				break;
			}
		}return skillSetValidation;
	}
	
	@Override
	//public List<AuditSchedule> getAuditByPlanId(int legalEntityCode, String auditTypeCode, String planId) {
	/**
	 * this method is for get plan data by plan Id.
	 * 
	 * @param legalEntityCode
	 * @param auditTypeCode
	 * @param planId
	 * @return
	 */
	public AuditPlan getAuditByPlanId(int legalEntityCode, String auditTypeCode, String planId) {
		Session session = getSession();
		session.setDefaultReadOnly(true);
		
		AuditPlan adtPlan=null;
		String planStatus=null;
		
		try {
			/********************** This block is to retrieve Plan details **************************/

			StringBuilder queryString0=new StringBuilder( " SELECT audit_type_code,plan_id,plan_start_date,plan_end_date,status,"
					+ " (SELECT DISTINCT CONCAT(apw.maker,'-',first_name,last_name) FROM user usr WHERE usr.user_id = apw.maker and usr.legal_entity_code=apw.legal_entity_code ) AS maker,"		
					//+ " maker,"
					+ " maker_timestamp,"
					+ " (SELECT DISTINCT CONCAT(apw.checker,'-',first_name,last_name) FROM user usr WHERE usr.user_id = apw.checker and usr.legal_entity_code=apw.legal_entity_code) AS checker,"
					//+ " checker,"	      		
					+ " checker_timestamp,businessdate_timestamp,sysdate_timestamp,auth_rej_remarks, planner_unit_code, "
					+ " (SELECT CONCAT(e.unit_name,'--',e.level_code,'--',l.level_desc) FROM maint_entity e, entity_level_code_desc l "
					+ "		WHERE apw.legal_entity_code=e.legal_entity_code AND e.unit_code=apw.planner_unit_code "
					+ "			  AND apw.legal_entity_code=l.legal_entity_code AND l.level_code=e.level_code) as planner_level "
					+ " FROM audit_plan_wrk  apw "  
					+ " WHERE  legal_entity_code=:legalEntityCode AND plan_id=:planId AND audit_type_code =:auditTypeCode" 
					+ " UNION  " 
					+ " SELECT audit_type_code,plan_id,plan_start_date,plan_end_date,status,"
					+ " (SELECT DISTINCT CONCAT(ap.maker,'-',first_name,last_name) FROM user usr WHERE usr.user_id = ap.maker and usr.legal_entity_code=ap.legal_entity_code ) AS maker,"
					//+ " maker,"
					+ " maker_timestamp,"				
					+ " (SELECT DISTINCT CONCAT(ap.checker,'-',first_name,last_name) FROM user usr WHERE usr.user_id = ap.checker and usr.legal_entity_code=ap.legal_entity_code) AS checker,"
					//+ " checker,"
					+ " checker_timestamp,businessdate_timestamp,sysdate_timestamp,auth_rej_remarks, planner_unit_code, "
					+ " (SELECT CONCAT(e.unit_name,'--',e.level_code,'--',l.level_desc) FROM maint_entity e, entity_level_code_desc l "
					+ "		WHERE ap.legal_entity_code=e.legal_entity_code AND e.unit_code=ap.planner_unit_code "
					+ "			  AND ap.legal_entity_code=l.legal_entity_code AND l.level_code=e.level_code) as planner_level "
					+ " FROM audit_plan  ap " 
					+ " WHERE legal_entity_code=:legalEntityCode AND plan_id=:planId AND audit_type_code=:auditTypeCode"
					);

			Query  query1  =  session.createSQLQuery(queryString0.toString())
					.setParameter("legalEntityCode", legalEntityCode).
					setParameter("auditTypeCode", auditTypeCode).
					setParameter("planId", planId);


			Object obj = query1.uniqueResult();

			SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

			if(obj != null && ((Object[])obj).length > 0){

				adtPlan= new AuditPlan();

				adtPlan.setAuditTypeCode(((Object[])obj)[0].toString());
				adtPlan.setPlanId(((Object[])obj)[1].toString());

				//adtPlan.setPlanStartDate(formatter.parse(((Object[])obj)[2].toString()));
				//adtPlan.setPlanEndDate(formatter.parse(((Object[])obj)[3].toString()));

				adtPlan.setPlanStartDate(BankAuditUtil.parseStringToDate(((Object[])obj)[2].toString()));
				adtPlan.setPlanEndDate(BankAuditUtil.parseStringToDate(((Object[])obj)[3].toString()));

				logger.info("stdt..."+((Object[])obj)[3].toString());
				logger.info("stdt 11..."+(BankAuditUtil.parseStringToDate(((Object[])obj)[3].toString())));

				//System.out.println(""+ new Date());
				adtPlan.setStatus(((Object[])obj)[4].toString());
				if(((Object[])obj)[5] != null)adtPlan.setMaker(((Object[])obj)[5].toString());
				if(((Object[])obj)[6] != null)adtPlan.setMakerTimestamp(formatter.parse(((Object[])obj)[6].toString()));	
				if(((Object[])obj)[7] != null)adtPlan.setChecker(((Object[])obj)[7].toString());
				if(((Object[])obj)[8] != null)adtPlan.setCheckerTimestamp(formatter.parse(((Object[])obj)[8].toString()));
				if(((Object[])obj)[9] != null)adtPlan.setBusinessdateTimestamp(formatter.parse(((Object[])obj)[9].toString()));
				if(((Object[])obj)[10] != null)adtPlan.setSysdateTimestamp(formatter.parse(((Object[])obj)[10].toString()));
				if(((Object[])obj)[11] != null)adtPlan.setAuthRejRemarks(((Object[])obj)[11].toString());
				if(((Object[])obj)[12] != null)adtPlan.setPlannerUnitCode(((Object[])obj)[12].toString());
				if(((Object[])obj)[13] != null) {adtPlan.setPlannerUnitName(((Object[])obj)[13].toString().split("--")[0]);
												 adtPlan.setPlannerLevelCode(((Object[])obj)[13].toString().split("--")[1]);
												 adtPlan.setPlannerLevelDesc(((Object[])obj)[13].toString().split("--")[2]);
												}

			}

			if(adtPlan!=null) {
				planStatus=adtPlan.getStatus();
			}  

		}catch(Exception e) {
			logger.error(e.getMessage(),e);
		}
		
		List<AuditSchedule> auditSchedulelst = null;
		String schTable="AuditSchedule";
		String teamTable="AuditTeam";
		
		if(!"A".equalsIgnoreCase(planStatus)) {
			schTable="AuditScheduleWrk";
			teamTable="AuditTeamWrk";
		}
		
		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 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 t.userId from "+teamTable +" t where  t.auditId=sch.auditId and t.legalEntityCode =sch.legalEntityCode and t.userType='L') as teamLead, "
					
					+ " sch.duration as duration, "
					+ " sch.frequency as frequency, "
					+ " sch.manDays as manDays, "
					//commented by narendar, as we are saving below columns into schedule table 
					
					/*+ " (Select MIN(p.duration) from  MaintEntity e,MaintAuditParameterDerived 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 "
					+ " and p.responseTime = COALESCE( sch.responseTime,  (SELECT g.value FROM GeneralParameter g WHERE g.legalEntityCode = sch.legalEntityCode AND g.key1='DEFAULT' AND g.key2='RESPONSE_TIME')) "
					+ " and p.rating = COALESCE( sch.ratingId,(SELECT g.value FROM GeneralParameter g WHERE g.legalEntityCode = sch.legalEntityCode AND g.key1='DEFAULT' AND g.key2='RATING')) "
					+ " and p.revenueLeakage = COALESCE( sch.revenueLeakage, (SELECT g.value FROM GeneralParameter g WHERE g.legalEntityCode = sch.legalEntityCode AND g.key1='DEFAULT' AND g.key2='REVENUE_LEAKAGE')) "
					+ " ) as duration, " 
					
					+ " (Select MIN(p.frequency) from  MaintEntity e,MaintAuditParameterDerived 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 "
					+ " and p.responseTime = COALESCE( sch.responseTime, (SELECT g.value FROM GeneralParameter g WHERE g.legalEntityCode = sch.legalEntityCode AND g.key1='DEFAULT' AND g.key2='RESPONSE_TIME')) "  
					+ " and p.rating = COALESCE( sch.ratingId,(SELECT g.value FROM GeneralParameter g WHERE g.legalEntityCode = sch.legalEntityCode AND g.key1='DEFAULT' AND g.key2='RATING')) "  
					+ " and p.revenueLeakage = COALESCE( sch.revenueLeakage, (SELECT g.value FROM GeneralParameter g WHERE g.legalEntityCode = sch.legalEntityCode AND g.key1='DEFAULT' AND g.key2='REVENUE_LEAKAGE')) "  
					+ "	) as  frequency, "
					
					+ " (Select MIN(p.manDays) from  MaintEntity e,MaintAuditParameterDerived 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 "
					+ "  and p.responseTime = COALESCE( sch.responseTime,(SELECT g.value FROM GeneralParameter g WHERE g.legalEntityCode = sch.legalEntityCode AND g.key1='DEFAULT' AND g.key2='RESPONSE_TIME')) " 
					+ "	 and p.rating = COALESCE( sch.ratingId,(SELECT g.value FROM GeneralParameter g WHERE g.legalEntityCode = sch.legalEntityCode AND g.key1='DEFAULT' AND g.key2='RATING')) " 
					+ "	 and p.revenueLeakage = COALESCE( sch.revenueLeakage,(SELECT g.value FROM GeneralParameter g WHERE g.legalEntityCode = sch.legalEntityCode AND g.key1='DEFAULT' AND g.key2='REVENUE_LEAKAGE')) " 
					+ "	) as  manDays, "
					*/
					
					//+ " (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 "					
					//added by Narendar to show description ,on 1/31/2018
					
					+ " (SELECT g.description FROM GeneralParameter g WHERE g.value = sch.auditStatus AND g.legalEntityCode = sch.legalEntityCode AND g.key1='STATUS' AND g.key2='AUDIT_STATUS') as auditStatusDescription, "
					+" sch.auditConsiderForPlanning as auditConsiderForPlanning,sch.plannedAuditContinueDrop as plannedAuditContinueDrop, "
					+" sch.planValidationMismatch as planValidationMismatch,"


					/*+ " (select group_concat(concat(u.userId,'-',u.firstName,' ',ifNull(u.middleName,''), ' ',ifNull(u.lastName,''))) from "+teamTable +" t, User u where t.userId = u.userId and t.legalEntityCode=sch.legalEntityCode "
					+ " AND t.auditId=sch.auditId  AND t.userType='M' ) AS teamMember "*/
					+ " (select group_concat(u.userId) from "+teamTable +" t, User u where t.userId = u.userId and t.legalEntityCode=sch.legalEntityCode "
					+ " AND t.auditId=sch.auditId  AND t.userType='M' ) AS teamMember "
					
					+ " from "+ schTable+" sch where legalEntityCode =:legalEntityCode and planId=:planId "); 
			
		//	and asv.maker like case status when 'DF' then :maker else '%' end 
			
			logger.info("Before the qry.............."+queryString);
			
			@SuppressWarnings("deprecation")
			Query query=(Query) session.createQuery(queryString.toString())				
					.setResultTransformer(new ResultTransformer() {
						int i=0;
						@Override
						public Object transformTuple(Object[] tuple, String[] aliases) {
							AuditSchedule auditSchedule=null;
							AuditScheduleWrk auditSchedule1=null;
							
							logger.info("tuple[0] ....  "+tuple[0].getClass());
							if(tuple[0]!=null){
								logger.info(tuple[0].getClass());
								String str="";
								
								try {
								auditSchedule=(AuditSchedule)tuple[0];
								if(tuple[2]!=null) auditSchedule.setAuditTypeCode(tuple[2].toString());
								if(tuple[5]!=null) 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) auditSchedule.setTeamLead(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) auditSchedule.setManDays(tuple[11].toString());
								if(tuple[12]!=null) auditSchedule.setAuditStatusDescription(tuple[12].toString());
								if(tuple[13]!=null) auditSchedule.setAuditConsiderForPlanning(tuple[13].toString());
								if(tuple[14]!=null) auditSchedule.setAuditStatusDescription(tuple[14].toString());
								str = (tuple[15]!=null && tuple[15].toString().equals("4"))?"Skill Set mismatch.":str;
								str = (tuple[15]!=null && tuple[15].toString().equals("5"))?str+"Effort mismatch..":str;
								
								if(tuple[16]!=null) auditSchedule.setTeamMember(tuple[16].toString());
								if(!"".equals(str)) auditSchedule.setPlanAuditRemarks(str);
								
								/*int tmSize =(auditSchedule.getAuditTeams()!=null)?auditSchedule.getAuditTeams().size():0;
								String teamMember="";
								for( AuditTeam adtTm: auditSchedule.getAuditTeams() ) {
									adtTm.setUserId(adtTm.getUserId());
									if("L".equalsIgnoreCase(adtTm.getUserType()))
										teamMember +=adtTm.getUserId() + ",";
								}
								if(tmSize >0 ) { 
									if(teamMember.contains(","))
									auditSchedule.setTeamMember(teamMember.substring(0,teamMember.lastIndexOf(",")));
								}*/
								
								//auditSchedule.setMember(auditSchedule.getTeamMember());
								auditSchedule.setCriticalityDesc(auditSchedule.getuCriticality());
								//auditSchedule.setStartDate(auditSchedule.getScheduleStartDate());
								//auditSchedule.setEndDate(auditSchedule.getScheduleEndDate());
								//logger.info("member .."+auditSchedule.getAuditTeams() + " TeamMember .." +auditSchedule.getTeamMember());
								
								auditSchedule.setRowNum(++i);
								logger.info("Inside schMst ....4");
								}catch(Exception e){
									logger.info("Inside Exc AuditScheduleWrk .... "+e);
									//e.printStackTrace(System.out);
									auditSchedule1=(AuditScheduleWrk)tuple[0];
									if(tuple[2]!=null) auditSchedule1.setAuditTypeCode(tuple[2].toString());
									if(tuple[5]!=null) auditSchedule1.setUnitName(tuple[5].toString());
									if(tuple[6]!=null) auditSchedule1.setUnitType(tuple[6].toString());
									if(tuple[7]!=null) auditSchedule1.setuCriticality(tuple[7].toString());
									if(tuple[8]!=null) auditSchedule1.setTeamLead(tuple[8].toString());
									if(tuple[9]!=null) auditSchedule1.setDuration(tuple[9].toString());
									if(tuple[10]!=null)	auditSchedule1.setFrequency(tuple[10].toString());
									if(tuple[11]!=null) auditSchedule1.setManDays(tuple[11].toString());
									if(tuple[12]!=null) auditSchedule1.setAuditStatusDescription(tuple[12].toString());
									if(tuple[13]!=null) auditSchedule1.setAuditConsiderForPlanning(tuple[13].toString());
									if(tuple[14]!=null) auditSchedule1.setAuditStatusDescription(tuple[14].toString());
									
									str = (tuple[15]!=null && tuple[15].toString().equals("4"))?"Skill Set mismatch.":str;
									str = (tuple[15]!=null && tuple[15].toString().equals("5"))?str+"Effort mismatch..":str;
									
									// if(tuple[16]!=null) auditSchedule1.setTeamMember(tuple[16].toString());
									
									if(!"".equals(str))
										auditSchedule1.setPlanAuditRemarks(str);
									
									int tmSize =auditSchedule1.getAuditTeamWrks().size();
									String teamMember="";
									 
									for( AuditTeamWrk adtTm: auditSchedule1.getAuditTeamWrks() ) {
										adtTm.setUserId(adtTm.getUserId());
										if("T".equalsIgnoreCase(adtTm.getUserType()))
											teamMember +=adtTm.getUserId() + ",";
									}if(tmSize >0 ) { 
										if(teamMember.contains(","))
										auditSchedule1.setTeamMember(teamMember.substring(0,teamMember.lastIndexOf(",")));
									}								
									auditSchedule1.setMember(auditSchedule1.getTeamMember());
									auditSchedule1.setCriticalityDesc(auditSchedule1.getuCriticality());
									//auditSchedule.setStartDate(auditSchedule.getScheduleStartDate());
									//auditSchedule.setEndDate(auditSchedule.getScheduleEndDate());
									
									auditSchedule1.setRowNum(++i);
								}    /* session.evict(maintEntity);*/
							}
							logger.info(" auditSchedule.."+auditSchedule+" auditSchedule1.."+auditSchedule1);
							if(auditSchedule != null)
							return auditSchedule; 
							else return auditSchedule1;
						}
				@Override
				public List transformList(List collection) {
					return collection;
				}
			})      .setParameter("legalEntityCode",legalEntityCode)
					.setParameter("planId",planId);
			
			 auditSchedulelst =query.list(); 
			 
			 if(auditSchedulelst!=null)
			 adtPlan.setAuditSchedules(auditSchedulelst);

		}catch(Exception e) {
			logger.error(e.getMessage(),e);
		}//return auditSchedulelst;
		return adtPlan;
	}
	
	
	/********************** This Method to get the Next Plan Start and End date **********************************/
	public  List<AuditPlanV> getAuditProcessNextPlanStEndDt(int legalEntityCode,String auditTypeCode) {

		logger.info("Inside getAuditProcessNextPlan .... ");
		List<AuditPlanV> ls=null;

		Session session=getSession();
		String hql=" FROM AuditPlanV where legalEntityCode =:legalEntityCode ";
				
		if(!BankAuditUtil.isEmptyString(auditTypeCode)) {
			 hql+="and  auditTypeCode=:auditTypeCode ";		
			 hql+=" order by auditTypeDesc asc,planId*1 asc   ";
			 ls=session.createQuery(hql).setParameter("legalEntityCode",legalEntityCode).setParameter("auditTypeCode",auditTypeCode).list();
		}else {
			hql+=" order by auditTypeDesc asc,planId*1 asc ";
			ls=session.createQuery(hql).setParameter("legalEntityCode",legalEntityCode).list();
		}
		
		session.setFlushMode(FlushMode.MANUAL);
		session.clear();
		
		logger.info("ls .."+ls);
		List<AuditPlanV> ls1= new ArrayList<>();
		Session session1=getSession();
		String hql1="select Date('2021-03-31') from dual "; //Need to comment, financial year end date
		Date dt=(Date)session.createNativeQuery(hql1).getSingleResult();
		try {
			for(AuditPlanV av: ls) {
				 if(av.getPlanStartDate().compareTo(av.getPlanEndDate())>0){
					 av.setPlanEndDate(dt);
				 }
				 ls1.add(av);
			}
		}catch(Exception e) {
			e.printStackTrace();
		}
		logger.info("ls .."+ls1);		
		return ls;
	}

	
	/******************* This Method to fetch the Assesments based on Audit/ Dept ****************/
	@SuppressWarnings({ "unchecked", "serial", "deprecation" })
	public List<AuditProcessV> getAssessmentsByAuditType(int legalEntityCode,String auditType,String psDate,String peDate,String maker){
		List<AuditProcessV> ls=null;
		int daysForStartDate =0; String assmntEndDtGP=null;

		try {
			Date businessDate=businessDateService.getBusinessDateTimestampByLegalEntityCode(legalEntityCode);
			Session session=getSession();
			
			StringBuilder queryStringGP=new StringBuilder( " select " 
					+ " (select value from general_parameter where legal_entity_code=:legalEntityCode and key_1='PLAN_START_END_DATE' and key_2='DAYS_FROM' ) as days," 
					+ " (select value from general_parameter where legal_entity_code=:legalEntityCode and key_1='PLAN_START_END_DATE' and key_2='END_DATE' ) as end_date "  
					+ " from dual "  
					); 
			Query queryGP=session.createSQLQuery(queryStringGP.toString()).setParameter("legalEntityCode", legalEntityCode);
			Object obj = queryGP.uniqueResult();
			try{
				if(obj != null  && ((Object[])obj).length > 0)
				{
					daysForStartDate  =Integer.parseInt( ((Object[])obj)[0].toString());
					assmntEndDtGP= ((Object[])obj)[1].toString();
					logger.info("daysForStartDate .... "+ daysForStartDate +" assmntEndDtGP.... "+assmntEndDtGP);
				}
			}catch(Exception e) {
				e.printStackTrace(System.out);
			}
			logger.info("businessDate .... "+ businessDate);
					
			logger.info("getAssessmentsByAuditType .."+legalEntityCode +" auditTypeCode.."+auditType+" psDate.."+psDate+" peDate.."+peDate +" maker .. "+maker);

         /***********Code commented to implement the logic of considering Rating and Revenue leakage from the previous Audit  ***************/
 		 /*	StringBuilder queryString=new StringBuilder(" "
			+ " SELECT  "
			+ "    m.unit_code,m.unit_name,m.unit_type,sch.audit_type_code,pd.frequency,pd.duration,pd.man_days, "
//			/ * Code commented to get the Start_date and End_date based on below Logic * / 
//			/ *+ "		DATE_ADD(sch.assessment_date, INTERVAL pd.frequency MONTH) > :businessDate then "  
//			+ "			DATE_ADD(sch.assessment_date, INTERVAL pd.frequency MONTH)"  
//			+ "		ELSE :businessDate end start_date, "  
//			+ "      CASE WHEN " 
//			+ "		DATE_ADD(sch.assessment_date, INTERVAL pd.frequency MONTH) > :businessDate then " 
//			+ "			DATE_ADD(DATE_ADD(sch.assessment_date, INTERVAL pd.frequency MONTH), INTERVAL pd.duration DAY) " 
//			+ "		ELSE DATE_ADD(:businessDate, INTERVAL pd.duration DAY) end last_date,  "
//			 / * Code commented * /
//			/ * Code included to get the Start_date and End_date based on below Logic 
//			 *  Start_date = Business_date + 7   and End_date= GenParam * / 
			+ " 	DATE_ADD(:businessDate, INTERVAL :daysForStartDate DAY) start_date,"
			+ "		:assmntEndDtGP end_date, "
//			/ *+ "		DATE_ADD(sch.assessment_date, INTERVAL pd.frequency MONTH) start_date, "
//			+ "		DATE_ADD(DATE_ADD(sch.assessment_date, INTERVAL pd.frequency MONTH), INTERVAL pd.duration DAY) last_date, "* /
			+ "    (select actual_end_date from audit_schedule s1 where s1.legal_entity_code=sch.legal_entity_code and s1.audit_type_code=sch.audit_type_code and  "
			+ "					s1.unit_id=sch.unit_id and s1.schedule_start_date=sch.assessment_date) as last_audit_end_date "
			+ " FROM  "
			+ "   ( maint_entity m, "
			+ "    maint_entity_audit_subgroup_mapping asmp, maint_audit_parameter_derived pd ) "
			+ "    left join ( "
			+ "    SELECT MAX(schedule_start_date) AS assessment_date,  "
			+ "                legal_entity_code AS legal_entity_code, "
			+ "                audit_type_code AS audit_type_code, "
			+ "                unit_id AS unit_id "
			+ "        FROM audit_schedule sch where legal_entity_code=:legalEntityCode "
			+ "             GROUP BY legal_entity_code , audit_type_code , unit_id) sch "
			+ "    on m.legal_entity_code=sch.legal_entity_code and m.unit_code=sch.unit_id and asmp.audit_type_code=sch.audit_type_code "
			+ " WHERE "
			+ "    m.legal_entity_code = asmp.legal_entity_code AND m.unit_code = asmp.id "
			+ "    AND m.legal_entity_code= pd.legal_entity_code AND asmp.legal_entity_code=pd.legal_entity_code  "
			+ "    AND pd.audit_type_code=asmp.audit_type_code "
			+ "		AND m.legal_entity_code =:legalEntityCode AND m.status = 'A'     "
			+ "        AND asmp.mapping_type='E' AND asmp.audit_type_code =:auditTypeCode ");
			 logger.info("Query String : "+queryString.toString());
             // END HERE   */			 
			
			StringBuilder queryString=new StringBuilder(" "
					+ " SELECT  "
					+ "    m.unit_code,m.unit_name,m.unit_type,sch.audit_type_code,pd.frequency,pd.duration,pd.man_days, "
					/* Code commented to get the Start_date and End_date based on below Logic */ 
					/*+ "		DATE_ADD(sch.assessment_date, INTERVAL pd.frequency MONTH) > :businessDate then "  
					+ "			DATE_ADD(sch.assessment_date, INTERVAL pd.frequency MONTH)"  
					+ "		ELSE :businessDate end start_date, "  
					+ "      CASE WHEN " 
					+ "		DATE_ADD(sch.assessment_date, INTERVAL pd.frequency MONTH) > :businessDate then " 
					+ "			DATE_ADD(DATE_ADD(sch.assessment_date, INTERVAL pd.frequency MONTH), INTERVAL pd.duration DAY) " 
					+ "		ELSE DATE_ADD(:businessDate, INTERVAL pd.duration DAY) end last_date,  "
					 /* Code commented */
					/* Code included to get the Start_date and End_date based on below Logic 
					 *  Start_date = Business_date + 7   and End_date= GenParam */ 
					+ " 	DATE_ADD(:businessDate, INTERVAL :daysForStartDate DAY) start_date,"
					+ "		:assmntEndDtGP end_date, "
					/*+ "		DATE_ADD(sch.assessment_date, INTERVAL pd.frequency MONTH) start_date, "
					+ "		DATE_ADD(DATE_ADD(sch.assessment_date, INTERVAL pd.frequency MONTH), INTERVAL pd.duration DAY) last_date, "*/
					+ "    (select actual_end_date from audit_schedule s1 where s1.legal_entity_code=sch.legal_entity_code and s1.audit_type_code=sch.audit_type_code and  "
					+ "					s1.unit_id=sch.unit_id and s1.schedule_start_date=sch.assessment_date) as last_audit_end_date "
					+ " FROM  "
					+ "   ( maint_entity m, "
					+ "    maint_entity_audit_subgroup_mapping asmp, maint_audit_parameter_derived pd ) "
					
				// Code added to get the Previous Rating and Revenue leakage	
					+ " LEFT JOIN "
					+ "		(select sch1.audit_id, sch1.unit_id, sch1.audit_type_code, sch1.assessed_rating, 0 as rev_leak, sch1.legal_entity_code "
					+ "				from audit_schedule sch1, (SELECT  MAX(actual_end_date) AS actual_end_date, "
					+ "								legal_entity_code AS legal_entity_code, audit_type_code AS audit_type_code, unit_id AS unit_id "
					+ "								FROM audit_schedule sch WHERE legal_entity_code =:legalEntityCode "
					+ "								GROUP BY legal_entity_code , audit_type_code , unit_id) sch2 "
					+ "          where  sch1.actual_end_date=sch2.actual_end_date  and sch1.legal_entity_code=sch2.legal_entity_code "
					+ "				 and sch1.audit_type_code=sch2.audit_type_code and sch1.unit_id= sch2.unit_id   "
					+ "		)  sch3 "
					+ "		on m.legal_entity_code = sch3.legal_entity_code and m.unit_code=sch3.unit_id and pd.audit_type_code=sch3.audit_type_code "
				// end here	
					+ "    left join ( "
					+ "    SELECT MAX(schedule_start_date) AS assessment_date,  "
					+ "                legal_entity_code AS legal_entity_code, "
					+ "                audit_type_code AS audit_type_code, "
					+ "                unit_id AS unit_id "
					+ "        FROM audit_schedule sch where legal_entity_code=:legalEntityCode "
					+ "             GROUP BY legal_entity_code , audit_type_code , unit_id) sch "
					+ "    on m.legal_entity_code=sch.legal_entity_code and m.unit_code=sch.unit_id and asmp.audit_type_code=sch.audit_type_code "
					+ " WHERE "
					+ "    m.legal_entity_code = asmp.legal_entity_code AND m.unit_code = asmp.id "
					+ "    AND m.legal_entity_code= pd.legal_entity_code AND asmp.legal_entity_code=pd.legal_entity_code  "
					+ "    AND pd.audit_type_code=asmp.audit_type_code "
				  // Condition added to match the Unittype, criticality, classification, rev_leak, rating
					+ "	   AND pd.unit_type = m.unit_type AND pd.u_criticality=m.u_criticality AND pd.classification= m.classification "
			        + "	   AND pd.revenue_leakage=ifnull(sch3.rev_leak,0)  "
			        + "    AND pd.rating= ifnull(sch3.assessed_rating,(select risk_category_code from risk_score_percentage r "
			        + "							where r.audit_type_code=asmp.audit_type_code and r.rating_internal=1)) "
			      // condition, end here 
					+ "		AND m.legal_entity_code =:legalEntityCode AND m.status = 'A'     "
					+ "        AND asmp.mapping_type='E' AND asmp.audit_type_code =:auditTypeCode ");
					 logger.info("Query String : "+queryString.toString());
			
			 int rowNum =0;
			 SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
				Query query=(Query) session.createNativeQuery(queryString.toString())				
						.setResultTransformer(new ResultTransformer() {
			// m.unit_code,m.unit_name,m.unit_type,sch.audit_type_code, pd.frequency,pd.duration, start_date, last_date, actual_end_date			   
							@Override
							public Object transformTuple(Object[] tuple, String[] aliases) {
								AuditProcessV apv=null;
								if(tuple[0]!=null) {
									try {
										apv= new AuditProcessV(); 
										//apv.setRowNum(rowNum++);
										apv.setLegalEntityCode(legalEntityCode+"");
										apv.setAuditTypeCode(auditType);
										if(tuple[0]!=null) apv.setUnitId(tuple[0].toString());
										if(tuple[1]!=null) apv.setUnitName(tuple[1].toString());
										if(tuple[2]!=null) apv.setUnitType(tuple[2].toString());
										//if(tuple[3]!=null) apv.setAuditTypeCode(tuple[3].toString());
										if(tuple[4]!=null) apv.setFrequency(tuple[4].toString());
										if(tuple[5]!=null) apv.setDuration(Integer.parseInt(tuple[5].toString()));
										if(tuple[6]!=null) apv.setManDays(tuple[6].toString());
										if(tuple[7]!=null) {
											// logger.info("tuple[7] .. "+tuple[7]);
											apv.setStartDate(tuple[7].toString().substring(0, 10));
										}
											// logger.info("tuple[8] .. "+tuple[8]);
										if(tuple[8]!=null) { 
											//apv.setEndDate(tuple[8].toString());
											String assmntEndDt= psDate.substring(0,4)+"-"+tuple[8];
											apv.setEndDate(assmntEndDt);
										}
										if(tuple[9]!=null) apv.setLastAuditEndDate(tuple[9].toString());
										
										apv.setAuditId(null);
	
									}catch (Exception e) {
										logger.error(e.getMessage(),e);
									}
								}
								return apv;
							}
	
							@Override
							public List transformList(List collection) {
								return collection;
							}
						}).setParameter("legalEntityCode",legalEntityCode)
						 .setParameter("auditTypeCode",auditType)
						 /*.setParameter("unitCode",unitCode)*/					 
						 .setParameter("businessDate",businessDate)
						 .setParameter("daysForStartDate", daysForStartDate)
						 .setParameter("assmntEndDtGP", assmntEndDtGP);
						ls=query.list();
								
							
			session.setFlushMode(FlushMode.MANUAL);
			session.clear();
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		return ls;
	}
	
	
	/**
	 * this method is to fetch audit team members  
	 */
	public List getRiskAssessorsTeam(int legalEntityCode,String auditType,String unitCode, String userId) {
		
		List riskAssessorTeam = null;
		Session session=getSession();
		StringBuilder hql =new StringBuilder( " SELECT usr.user_id, " + 
					 " CONCAT(usr.first_name,' ',usr.last_name) user_name, " + 
					 " usr.state,usr.unit_code " +					
					 " FROM user usr,user_role_mapping urm,  " + 
					 " maint_entity_audit_subgroup_mapping asmp "+
					 " WHERE usr.legal_entity_code = :legalEntityCode " + 
					 " AND urm.legal_entity_code=usr.legal_entity_code " + 
					 " AND urm.status='A' " + 
					 " AND usr.status='A' " + 
					 " AND urm.user_role_id LIKE '%G2%' " + 
					 " AND urm.user_id=usr.user_id " + 
					 //" AND usr.unit_code = '"+unitCode +"'"+
					 " AND asmp.legal_entity_code=usr.legal_entity_code " +
					 " AND asmp.audit_type_code= :auditType " +
					 " AND asmp.id= usr.user_id ");
		if(unitCode != null && !"".equalsIgnoreCase(unitCode)) {
				hql.append(" AND usr.unit_code = :unitCode ");}
		hql.append(" GROUP BY usr.user_id,user_name,usr.unit_code, usr.state " + 
					 " ORDER BY usr.unit_code,user_name ASC ");

		Query hQry	= session.createSQLQuery(hql.toString())
				.addScalar("user_id", StandardBasicTypes.STRING)
				.addScalar("user_name", StandardBasicTypes.STRING)
				.addScalar("state", StandardBasicTypes.STRING)
				.addScalar("unit_code", StandardBasicTypes.STRING)
				//.addScalar("audit_type_code", StandardBasicTypes.STRING 
				.setParameter("legalEntityCode", legalEntityCode).setParameter("auditType", auditType);
				if(unitCode != null && !"".equalsIgnoreCase(unitCode)) {
					hQry.setParameter("unitCode", unitCode);
				}
				
		List<Object[]> rows = hQry.list();

		AuditProcessTeam apt=null;

		//List<AuditProcessTeam> ls=new ArrayList<>();
		AssessorTeamDto assTeam= null;
		List ls=new ArrayList<>();
		String tmpUnit =null; int iterateCnt = 0; HashMap auditTeam =new HashMap(); 
		HashMap temp = new HashMap();  List tmpls=new ArrayList(); 
		logger.info("No of recs .."+ rows.size());
		
		
		for(Object[] col : rows){
			if(col[0]!=null && col[1]!=null && col[2]!=null && col[3]!=null) {
				assTeam = new AssessorTeamDto();
				if(iterateCnt == 0) {
					assTeam.setLabel("select");
					assTeam.setValue("null");
					ls.add(assTeam);
					assTeam = new AssessorTeamDto();
					assTeam.setLabel(col[1].toString());
					assTeam.setValue(col[0].toString());
					ls.add(assTeam);					
	 			}else{
		 			if(col[3].toString().equalsIgnoreCase(tmpUnit)) {
		 				assTeam.setLabel(col[1].toString());
						assTeam.setValue(col[0].toString());
						ls.add(assTeam);
						assTeam = new AssessorTeamDto();
		 			}else {
		 				auditTeam.put(tmpUnit,ls);
		 				ls=new ArrayList<>(); 
		 				assTeam.setLabel("select");
						assTeam.setValue("null");
						ls.add(assTeam);
						assTeam = new AssessorTeamDto();
		 				assTeam.setLabel(col[1].toString());
						assTeam.setValue(col[0].toString());
						ls.add(assTeam);
						assTeam = new AssessorTeamDto();
		 			}
	 			}
	 			iterateCnt++; 
	 			if(iterateCnt == rows.size()) { 
	 				auditTeam.put(col[3].toString(),ls);
	 			}tmpUnit =col[3].toString();
			}
		}
		
		/*for(Object[] col : rows){
			if(col[0]!=null && col[1]!=null && col[2]!=null && col[3]!=null) {
				if(iterateCnt == 0) {
					tmpls = new ArrayList(); 
					temp = new HashMap();
					temp.put("label", col[1].toString());
					tmpls.add(temp);
					temp = new HashMap();
					temp.put("value", col[0].toString());
					tmpls.add(temp);
					ls.add(tmpls);
					tmpls=null;
	 			}else{
		 			if(col[3].toString().equalsIgnoreCase(tmpUnit)) {
		 				tmpls = new ArrayList(); 
		 				temp = new HashMap();
						temp.put("label", col[1].toString());
						tmpls.add(temp);
						temp = new HashMap();
						temp.put("value", col[0].toString());
						tmpls.add(temp);
						ls.add(tmpls);
						tmpls=null;
		 			}else {
		 				auditTeam.put(tmpUnit,ls);
		 				ls=new ArrayList<>(); 
		 				tmpls = new ArrayList(); 
		 				temp = new HashMap();
						temp.put("label", col[1].toString());
						tmpls.add(temp);
						temp = new HashMap();
						temp.put("value", col[0].toString());
						tmpls.add(temp);
						ls.add(tmpls);
						tmpls=null;
		 			}
	 			}
	 			iterateCnt++; 
	 			if(iterateCnt == ls.size()) { 
	 				auditTeam.put(col[3].toString(),ls);
	 			}tmpUnit =col[3].toString();
			}
		}*/
		
		/* for(Object[] col : rows){
			if(col[0]!=null && col[1]!=null && col[2]!=null && col[3]!=null) {
				apt = new AuditProcessTeam();
				if(iterateCnt == 0) {
					apt.setUserId(col[0].toString());
					apt.setUserName(col[1].toString());
					apt.setState(col[2].toString());
					//apt.setAuditTypeCode(col[3].toString());
					ls.add(apt);
	 			}else{
		 			if(col[3].toString().equalsIgnoreCase(tmpUnit)) {
		 				apt.setUserId(col[0].toString());
						apt.setUserName(col[1].toString());
						apt.setState(col[2].toString());
						//apt.setAuditTypeCode(col[3].toString());
						ls.add(apt);
		 			}else {
		 				auditTeam.put(tmpUnit,ls);
		 				ls=new ArrayList<>(); 
		 				apt.setUserId(col[0].toString());
						apt.setUserName(col[1].toString());
						apt.setState(col[2].toString());
						//apt.setAuditTypeCode(col[3].toString());
						ls.add(apt);
		 			}
	 			}
	 			iterateCnt++; 
	 			if(iterateCnt == ls.size()) { 
	 				auditTeam.put(col[3].toString(),ls);
	 			}tmpUnit =col[3].toString();
			}
		} */
		riskAssessorTeam = new ArrayList<>();
		riskAssessorTeam.add(auditTeam);
		logger.info("users List .."+ auditTeam);
		return  riskAssessorTeam;
	}
	
	public List getRiskAssessorsTeamChekcer(int legalEntityCode,String auditType,String unitCode, String userId) {
		
		List riskAssessorTeam = null;
		Session session=getSession();
		StringBuilder hql =new StringBuilder( " SELECT usr.user_id, " + 
					 " CONCAT(usr.first_name,' ',usr.last_name) user_name, " + 
					 " usr.state,usr.unit_code " +					
					 " FROM user usr,user_role_mapping urm,  " + 
					 " maint_entity_audit_subgroup_mapping asmp "+
					 " WHERE usr.legal_entity_code = :legalEntityCode " + 
					 " AND urm.legal_entity_code=usr.legal_entity_code " + 
					 " AND urm.status='A' " + 
					 " AND usr.status='A' " + 
					 " AND urm.user_role_id LIKE '%G8%' " + 
					 " AND urm.user_id=usr.user_id " + 
					 //" AND usr.unit_code = '"+unitCode +"'"+
					 " AND asmp.legal_entity_code=usr.legal_entity_code " +
					 " AND asmp.audit_type_code= :auditType " +
					 " AND asmp.id= usr.user_id ");
		if(unitCode != null && !"".equalsIgnoreCase(unitCode)) {
				hql.append(" AND usr.unit_code = :unitCode ");}
		hql.append(" GROUP BY usr.user_id,user_name,usr.unit_code, usr.state " + 
					 " ORDER BY usr.unit_code,user_name ASC ");

		Query hQry	= session.createSQLQuery(hql.toString())
				.addScalar("user_id", StandardBasicTypes.STRING)
				.addScalar("user_name", StandardBasicTypes.STRING)
				.addScalar("state", StandardBasicTypes.STRING)
				.addScalar("unit_code", StandardBasicTypes.STRING)
				//.addScalar("audit_type_code", StandardBasicTypes.STRING 
				.setParameter("legalEntityCode", legalEntityCode).setParameter("auditType", auditType);
				if(unitCode != null && !"".equalsIgnoreCase(unitCode)) {
					hQry.setParameter("unitCode", unitCode);
				}
				
		List<Object[]> rows = hQry.list();

		AuditProcessTeam apt=null;

		//List<AuditProcessTeam> ls=new ArrayList<>();
		AssessorTeamDto assTeam= null;
		List ls=new ArrayList<>();
		String tmpUnit =null; int iterateCnt = 0; HashMap auditTeam =new HashMap(); 
		HashMap temp = new HashMap();  List tmpls=new ArrayList(); 
		logger.info("No of recs .."+ rows.size());
		
		
		for(Object[] col : rows){
			if(col[0]!=null && col[1]!=null && col[2]!=null && col[3]!=null) {
				assTeam = new AssessorTeamDto();
				if(iterateCnt == 0) {
					assTeam.setLabel("select");
					assTeam.setValue("null");
					ls.add(assTeam);
					assTeam = new AssessorTeamDto();
					assTeam.setLabel(col[1].toString());
					assTeam.setValue(col[0].toString());
					ls.add(assTeam);					
	 			}else{
		 			if(col[3].toString().equalsIgnoreCase(tmpUnit)) {
		 				assTeam.setLabel(col[1].toString());
						assTeam.setValue(col[0].toString());
						ls.add(assTeam);
						assTeam = new AssessorTeamDto();
		 			}else {
		 				auditTeam.put(tmpUnit,ls);
		 				ls=new ArrayList<>(); 
		 				assTeam.setLabel("select");
						assTeam.setValue("null");
						ls.add(assTeam);
						assTeam = new AssessorTeamDto();
		 				assTeam.setLabel(col[1].toString());
						assTeam.setValue(col[0].toString());
						ls.add(assTeam);
						assTeam = new AssessorTeamDto();
		 			}
	 			}
	 			iterateCnt++; 
	 			if(iterateCnt == rows.size()) { 
	 				auditTeam.put(col[3].toString(),ls);
	 			}tmpUnit =col[3].toString();
			}
		}
		riskAssessorTeam = new ArrayList<>();
		riskAssessorTeam.add(auditTeam);
		logger.info("users List .."+ auditTeam);
		return  riskAssessorTeam;
	}
		@Override
	public String validateAuditInspectionProcessPreviosDate(int legalEntityCode, String userIds, Date planStartDate) {
		String[] users = userIds.split(",");
		logger.debug("Entered into AuditProcessVDaoImpl validateAuditInspectionProcessPreviosDate method");
		Session session = getSession();
		for (String str : users) {
			StringBuilder hql = new StringBuilder(
					"SELECT * FROM audit_schedule WHERE audit_id IN " + "(SELECT audit_id FROM audit_team  WHERE user_id IN ('" + str
							+ "'))  AND observation_status = 'AE' AND report_date IS NOT NULL ORDER BY report_date DESC LIMIT 1");
			List<AuditSchedule> adslist =  session.createNativeQuery(hql.toString(), AuditSchedule.class).getResultList();
			if (adslist != null && !adslist.isEmpty()) {
				LocalDateTime sld = LocalDateTime.ofInstant(adslist.get(0).getReportDate().toInstant(), ZoneId.systemDefault());
				DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
				LocalDate ld =  LocalDate.parse(planStartDate.toString(), formatter);
				LocalDateTime planEnds = ld.atStartOfDay(); 
				sld = sld.plusDays(30);
				if (sld.isAfter(planEnds))
					return str;
			}
		}
		return "";
	}
	@Override
	public List<AuditTeam> immediatePrecedinginSpection(int legalEntityCode, String auditTypeCode, String planId, String maker, String unitId,
			String userIds) {
		logger.debug("Entered into AuditProcessVDaoImpl immediatePrecedinginSpection method");
		Session session = getSession();
		String[] users = userIds.split(",");
		String sectionStr = "'" + String.join("','", users).concat("'");
		StringBuilder hql = new StringBuilder("SELECT * FROM audit_team WHERE legal_entity_code = " + legalEntityCode + " AND user_id IN ("
				+ sectionStr + ")" + " AND audit_id = (SELECT audit_id FROM audit_schedule WHERE legal_entity_code=" + legalEntityCode + " AND "
				+ "  audit_type_code ='" + auditTypeCode + "' AND unit_id = '" + unitId
				+ "' AND report_date IS NOT NULL ORDER BY report_date DESC LIMIT 1)");
		List<AuditTeam> list = session.createSQLQuery(hql.toString()).list();
		return list;
	}
	@Override
	public String userIsFromOtherRegion(int legalEntityCode, String auditTypeCode, String unitId, String userIds) {
		if (unitId != null && unitId.equalsIgnoreCase("1"))
			return "";
		MaintEntity region = getMaintEntity(legalEntityCode, unitId);
		String[] users = userIds.split(",");
		for (String s : users) {
			MaintEntity userRegn = getUserMaintEntity(legalEntityCode, s);
			if (userRegn != null && region != null && !userRegn.getUnitCode().equalsIgnoreCase(region.getUnitCode()))
				return s;
		}
		return "";
	}
	private MaintEntity getMaintEntity(int legalEntityCode, String unitId) {
		Criteria criteria = getSession().createCriteria(MaintEntity.class);
		criteria.add(Restrictions.eq("legalEntityCode", legalEntityCode));
		criteria.add(Restrictions.eq("unitCode", unitId));
		MaintEntity entity = (MaintEntity) criteria.uniqueResult();
		if (entity != null && Integer.parseInt(entity.getLevelCode()) > 2) {
			entity = getMaintEntity(legalEntityCode, entity.getParentUnitCode());
		}
		return entity;
	}
	private MaintEntity getUserMaintEntity(int legalEntityCode, String userId) {
		Criteria criteria = getSession().createCriteria(User.class);
		criteria.add(Restrictions.eq("legalEntityCode", legalEntityCode));
		criteria.add(Restrictions.eq("userId", userId));
		User user = (User) criteria.uniqueResult();
		return getMaintEntity(legalEntityCode, user.getUnitCode());
	}
	/*@Override
	public String compareUserGrades(int legalEntityCode, String teamLead, String members) {
		List<GeneralParameter> genParams = generalParameterDao.getGeneralParameter(legalEntityCode, null, null, "GRADE", "USER", null, null);
		Map<String, String> paramKeyValue = genParams.stream()
				.collect(Collectors.toMap(GeneralParameter::getValue, GeneralParameter::getDescription));
		
		Criteria criteria = getSession().createCriteria(UserRoleMapping.class);
		criteria.add(Restrictions.eq("legalEntityCode", legalEntityCode));
		criteria.add(Restrictions.eq("userId", teamLead));
		List<UserRoleMapping> teamLeadRoleList = criteria.list();
		if (teamLeadRoleList != null && !teamLeadRoleList.isEmpty()) {
			UserRoleMapping teamLeadRole = teamLeadRoleList.get(teamLeadRoleList.size() - 1);
			String[] users = members.split(",");
			for (String str : users) {
				criteria.add(Restrictions.eq("userId", str));
				List<UserRoleMapping> memberRoleList = criteria.list();
				if(memberRoleList != null && !memberRoleList.isEmpty()) {
					UserRoleMapping memberRole = memberRoleList.get(teamLeadRoleList.size() - 1);
					if (paramKeyValue.get(teamLeadRole.getUserRoleId()).compareTo(paramKeyValue.get(memberRole.getUserRoleId())) < 0) {
						return str;
					}
				}
			}
		}
		return "";
	}*/

	/*************************************Error Code ****************************************/
	
	@Override
	public Map<String, String> isValiedBusinessDate(Session session,AuditProcessV apv,Date currStartDate,Date currEndDate,String frequency,Date plnStDate,Date plnEnDate) {
		
		
		Map<String,String> map1=null;
		String hql=" select d.currStartDate from (SELECT DATE(Schedule_date_f("+apv.getLegalEntityCode()+",'"+apv.getUnitId()+"',";
		
		if(currStartDate!=null && !"".equals(currStartDate)) 
				hql+= "'"+currStartDate+"'";
				
				if(currEndDate!=null && !"".equals(currEndDate)) 
				hql+= "'"+currEndDate+"'";	
			
		        hql+= ",0,1)) AS currStartDate "
				+ " FROM DUAL) d " +
				" WHERE 1=1 " ;
		     
		      
	/*	if(currStartDate!=null && plnStDate!=null && plnEnDate!=null ) {
			 hql+= " AND d.currStartDate = CAST('"+currStartDate+"' AS DATE) " ;
		     hql+= " AND d.currStartDate >= CAST('"+plnStDate+"' AS DATE) " ;
		     hql+= " AND d.currStartDate <= CAST('"+plnEnDate+"' AS DATE) " ;
	    }
		
		if(currEndDate!=null && plnStDate!=null) {		
			 hql+= " AND d.currStartDate = CAST('"+currEndDate+"' AS DATE) " ;
			hql+= " AND d.currStartDate >= CAST('"+plnStDate+"' AS DATE) " ;				
	    }  */
		
		
		/*if(currStartDate!=null && !"".equals(currStartDate)) {
			 hql+= " AND d.currStartDate = CAST('"+currStartDate+"' AS DATE) "; 
		} 
		
		if(currEndDate!=null && !"".equals(currEndDate)) {		
			 hql+= " AND d.currStartDate = CAST('"+currEndDate+"' AS DATE) " ;							
	    }*/
		
		if((plnStDate!=null && !"".equals(plnStDate))) {
			 hql+= " AND d.currStartDate >= '"+plnStDate+"' "; 
		}
		
		if((plnEnDate!=null && !"".equals(plnEnDate))) {			
		    hql+= " AND d.currStartDate <= '"+plnEnDate+"' " ;
	    }
		
		String str= (String) session.createSQLQuery(hql)	
				.addScalar("currStartDate", StandardBasicTypes.STRING)				
				.uniqueResult();
		if(str!=null) {
			map1=new HashMap<String,String>();
			map1.put("validDate","1");
		}else {
			map1=new HashMap<String,String>();			
			map1.put("validDate","0");
		}
		
		return map1;

	}
	
	@Override
	public Map<String, String> getUserGradeMap(Integer legalEntityCode){
		Session session = getSession();
		StringBuilder queryString0 = new StringBuilder("from User u where u.legalEntityCode =:legalEntityCode");
		Query query1 = session.createQuery(queryString0.toString()).setParameter("legalEntityCode", legalEntityCode);
		List<User> lst = query1.list();
		HashMap<String, String> userDtls = new HashMap<>();
		for (User usr : lst) {
			userDtls.put(usr.getUserId(), usr.getGrade());
		}
		return userDtls;
	}
	
	@Override
	public boolean isAuditorsConflict(int legalEntityCode, String auditId, Date schStartDate, Date schEndDate, List<String> aioPios){
		logger.info("Inside isAuditorsConflict :: "+legalEntityCode + " :: "+ auditId);
		Session session = getSession();
		boolean aioPioConflict=false;
		try {
		StringBuilder queryString0 = new StringBuilder("SELECT sch FROM AuditSchedule sch, AuditTeam t "
				+ "	WHERE sch.legalEntityCode=t.legalEntityCode  AND sch.auditId=t.auditId "
				+ " AND sch.legalEntityCode=:legalEntityCode AND sch.auditId !=:auditId AND t.userId IN (:aioPios) "
				+ " AND ((ifnull(sch.actualStartDate, sch.scheduleStartDate) between :schStartDate AND :schEndDate "
				+ "     OR ifnull(sch.actualEndDate, sch.scheduleEndDate) between :schStartDate AND :schEndDate ) "
				+ "	OR ( ifnull(sch.actualStartDate, sch.scheduleStartDate) < :schStartDate AND ifnull(sch.actualEndDate, sch.scheduleEndDate) > :schEndDate )) ");
		Query query1 = session.createQuery(queryString0.toString()).setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditId", auditId).setParameter("aioPios", aioPios)
				.setParameter("schStartDate", schStartDate).setParameter("schEndDate", schEndDate);
		List<AuditSchedule> lst = query1.list();
		if(!lst.isEmpty() && lst.size() >0) aioPioConflict=true;
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		return aioPioConflict;
	}
	
	@Override
	public boolean isUnitInspectingSametime(int legalEntityCode, String auditId, String unitId, String auditTypeCode, Date schStartDate, Date schEndDate){
		logger.info("Inside isUnitInspectingSametime :: "+legalEntityCode + " :: "+ auditId+ " :: " +unitId +" :: "+ auditTypeCode+" :: "+schStartDate+" :: "+schEndDate);
		Session session = getSession();
		boolean isUnitInspectingSametime=false;
		try {
			StringBuilder queryString0 = new StringBuilder("SELECT sch FROM AuditSchedule sch  "
				+ "	WHERE sch.legalEntityCode=:legalEntityCode AND sch.auditId !=:auditId AND sch.unitId =:unitId "
				+ " AND ((ifnull(sch.actualStartDate, sch.scheduleStartDate) between :schStartDate AND :schEndDate "
				+ "     OR ifnull(sch.actualEndDate, sch.scheduleEndDate) between :schStartDate AND :schEndDate ) "
				+ "	OR ( ifnull(sch.actualStartDate, sch.scheduleStartDate) < :schStartDate AND ifnull(sch.actualEndDate, sch.scheduleEndDate) > :schEndDate )) ");
			
				/*+ " AND ( ifnull(sch.actualStartDate, sch.scheduleStartDate) between :schStartDate AND :schEndDate "
				+ "     OR ifnull(sch.actualEndDate, sch.scheduleEndDate) between :schStartDate AND :schEndDate ) ");*/
		
			Query query1 = session.createQuery(queryString0.toString()).setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditId", auditId).setParameter("unitId", unitId)
				.setParameter("schStartDate", schStartDate).setParameter("schEndDate", schEndDate);
			List<AuditSchedule> lst = query1.list();
			if(!lst.isEmpty() && lst.size() >0) isUnitInspectingSametime=true;
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		return isUnitInspectingSametime;
	}
	
	@Override
	public boolean isEffortMismatch(int legalEntityCode, String auditId, Date schStartDate, Date schEndDate, List<String> aioPios) {
		logger.info("Inside isEffortMismatch :: "+legalEntityCode + " :: "+ auditId+ " :: " +schStartDate +" :: "+ schEndDate+" :: "+aioPios);
		Session session = getSession();
		boolean isEffortMismatch=false; 
		int dateDiff=1, manDays=0, duration=0, noAioPios=aioPios.size();
		String auditStatus=null;
		try {
			StringBuilder queryStringGP=new StringBuilder( " SELECT DATEDIFF(:schEndDate, :schStartDate) as diffdate, duration, man_days, audit_status " 
					+ " FROM audit_schedule "
					+ " WHERE legal_entity_code=:legalEntityCode AND audit_id=:auditId " ); 
			Query querySch=session.createSQLQuery(queryStringGP.toString()).setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId)
					.setParameter("schEndDate", schEndDate).setParameter("schStartDate", schStartDate);
			Object obj = querySch.uniqueResult();
			if(obj != null  && ((Object[])obj).length > 0)
				{
					dateDiff  =Integer.parseInt( ((Object[])obj)[0].toString());
					duration  =Integer.parseInt( ((Object[])obj)[1].toString());
					manDays  =Integer.parseInt( ((Object[])obj)[2].toString());
					auditStatus=((Object[])obj)[3].toString();
					logger.info("dateDiff.. "+ dateDiff +" duration.. "+duration+" manDays.. "+manDays+ " auditStatus.. "+auditStatus);
				}
			if(noAioPios*dateDiff != manDays*duration && "AP".equalsIgnoreCase(auditStatus)) isEffortMismatch=true;
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}return isEffortMismatch;
	}
	
	// If one of the Inspectors are also not part of the same office, this warning will appear
	@Override
	public boolean isAuditorsBelongstoSameOffice(int legalEntityCode, String unitId, List<String> aioPios){
		logger.info("Inside isAuditorsBelongstoSameOffice :: "+legalEntityCode + " :: "+ unitId);
		Session session = getSession();
		boolean isAIOPIOBelongstoSameOffice=false;
		try {
		StringBuilder queryString0 = new StringBuilder(" SELECT u FROM User u  "
				+ "	WHERE u.legalEntityCode=:legalEntityCode AND u.userId IN (:aioPios) "
				+ "   AND u.unitCode =(SELECT parentUnitCode FROM MaintEntity e "
				+ "						WHERE u.legalEntityCode=e.legalEntityCode AND e.unitCode=:unitId ) ");
		Query query1 = session.createQuery(queryString0.toString()).setParameter("legalEntityCode", legalEntityCode)
				.setParameter("unitId", unitId).setParameter("aioPios", aioPios);
		List<User> lst = query1.list();
		//if(!lst.isEmpty() && lst.size() >0) isAIOPIOBelongstoSameOffice=true; // Checking of at least one of the Inspectors belongs to the same office
		if(!lst.isEmpty() && lst.size() >=aioPios.size() ) isAIOPIOBelongstoSameOffice=true; // Checking all the Inspectors belongs to the same office
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		return isAIOPIOBelongstoSameOffice;
	}
	
	// Is the Auditors/Inspectors picked, already part of the previous inspection of the same Unit
	// Only consider the Inspections in Exit stage, (Inspections which are in initiation and Planned state are not to be considered)
	@Override
	public boolean isAuditorsBelongstoPrevAudit(int legalEntityCode, String auditId, String auditTypeCode, String unitId,  List<String> aioPios){
		logger.info("Inside isAuditorsBelongstoPrevAudit :: "+legalEntityCode + " :: "+auditId+" :: "+auditTypeCode+" :: "+ unitId);
		Session session = getSession();
		boolean isAuditorsBelongstoPrevAudit=false;
		try {
		StringBuilder queryString0 = new StringBuilder(" SELECT t FROM AuditTeam t  "
			+ "	WHERE t.legalEntityCode=:legalEntityCode AND t.userId IN (:aioPios) "
			+ "   AND t.auditId IN (SELECT auditId FROM AuditSchedule  "
			+ "					     WHERE reportDate IN (SELECT MAX(reportDate) FROM AuditSchedule "    //+ "  WHERE scheduleStartDate IN (SELECT MAX(scheduleStartDate) FROM AuditSchedule "
			+ "							WHERE auditTypeCode=:auditTypeCode AND unitId=:unitId GROUP BY auditTypeCode, unitId )"
			+ "							AND auditTypeCode=:auditTypeCode AND unitId=:unitId ) ");
		Query query1 = session.createQuery(queryString0.toString()).setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode).setParameter("unitId", unitId).setParameter("aioPios", aioPios);
		List<User> lst = query1.list();
		if(!lst.isEmpty() && lst.size() >0) isAuditorsBelongstoPrevAudit=true;
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		return isAuditorsBelongstoPrevAudit;
	}

	
	// Is the Auditors/Inspectors available due to his previous inspection
	// Only consider the Inspections in Exit stage, (Inspections which are in initiation and Planned state are not to be considered)
	@Override
	public boolean isAuditorsAvailableDuetoPrevAudit(int legalEntityCode, String auditId, String auditTypeCode, String unitId,  List<String> aioPios){
		logger.info("Inside isAuditorsAvailableDuetoPrevAudit :: "+legalEntityCode + " :: "+auditId+" :: "+auditTypeCode+" :: "+ unitId);
		Session session = getSession();
		boolean isAuditorsAvailableDuetoPrevAudit=true;
		try {
		/*StringBuilder queryString0 = new StringBuilder(" SELECT t FROM AuditTeam t  "
			+ "	WHERE t.legalEntityCode=:legalEntityCode AND t.userId IN (:aioPios) AND t.auditId <> :auditId "
			+ "   AND t.auditId IN (SELECT auditId FROM AuditSchedule sch "
			+ "					     WHERE sch.reportDate between DATE_SUB(ifnull(sch.actualStartDate, sch.scheduleStartDate), INTERVAL 30 DAY) AND ifnull(sch.actualStartDate, sch.scheduleStartDate)  "
			+ "						 AND sch.reportStatus <> 'RD' AND sch.legalEntityCode=t.legalEntityCode ) ");*/
		
		StringBuilder queryString0 = new StringBuilder(" SELECT t.* FROM audit_team t  "
				+ "	WHERE t.legal_entity_code=:legalEntityCode AND t.user_id IN (:aioPios) AND t.audit_id <> :auditId "
				+ "   AND t.audit_id IN (SELECT audit_id FROM audit_schedule sch "
				+ "					     WHERE sch.report_date between DATE_SUB(ifnull(sch.actual_start_date, sch.schedule_start_date), INTERVAL 30 DAY) AND ifnull(sch.actual_start_date, sch.schedule_start_date)  "
				+ "						 AND sch.report_status <> 'RD' AND sch.legal_entity_code=t.legal_entity_code ) ");
		
		Query query1 = session.createNativeQuery(queryString0.toString()).setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditId", auditId).setParameter("aioPios", aioPios);
		List<User> lst = query1.list();
		if(!lst.isEmpty() && lst.size() >0) isAuditorsAvailableDuetoPrevAudit=false;
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		return isAuditorsAvailableDuetoPrevAudit;
	}
	
	/**
	 * this method is to check Audit Parameters are configured for that audit.
	 */
	public List<AuditPlanWrk> isPlanAlreadyExist(int legalEntityCode,String userId,String parentUnitCode,String auditTypeCode){
		Session session=getSession();
		logger.info("AuditProcessVDaoImpl isPlanAlreadyExist .. "+ legalEntityCode +" :: "+userId+" :: "+parentUnitCode+" :: "+ auditTypeCode);
		List<AuditPlanWrk> ls=null;
		try { 
			ls=session.createQuery("from AuditPlanWrk pw "
				+ " where pw.legalEntityCode =:legalEntityCode "
				+ " and pw.plannerUnitCode=:plannerUnitCode and pw.auditTypeCode=:auditTypeCode ")
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode)
				.setParameter("plannerUnitCode", parentUnitCode).list();
			logger.info("ls .. "+ls);
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}return ls;
	}
	
	public List<AuditProcessV> getAuditProcessByAuditPlanAtParent(int legalEntityCode,String auditType,String parentUnitCode,String psDate,String peDate,String maker) {
		Session session=getSession();
		logger.info("Inside getAuditProcessByAuditPlanAtParent ");		
		String hql =" CALL auto_audit_process_at_parent(:legalEntityCode,:auditTypeCcode,:parentUnitCode,:planStartDate,:planEndDate,:maker) ";		
				
		logger.info(" CALL auto_audit_process_at_parent("+legalEntityCode+",'"+auditType+"','"+parentUnitCode+"','"+psDate+"','"+peDate+"','"+maker+"')");
		
		List<Object[]> rows = session.createSQLQuery(hql)

				.addScalar("row_num", StandardBasicTypes.LONG)
				.addScalar("audit_status", StandardBasicTypes.STRING)
				.addScalar("audit_type_code", StandardBasicTypes.STRING)
				.addScalar("legal_entity_code", StandardBasicTypes.STRING)
				.addScalar("parent_unit_code", StandardBasicTypes.STRING)
				.addScalar("unit_type", StandardBasicTypes.STRING)  // 6

				.addScalar("unit_name", StandardBasicTypes.STRING)
				.addScalar("unit_id", StandardBasicTypes.STRING)
				.addScalar("frequency", StandardBasicTypes.STRING)
				.addScalar("man_days", StandardBasicTypes.STRING)   // 10

				.addScalar("duration", StandardBasicTypes.INTEGER)
				.addScalar("response_time", StandardBasicTypes.STRING)
				.addScalar("closure_time", StandardBasicTypes.STRING)
				.addScalar("last_audit_end_date", StandardBasicTypes.DATE)  // 14

				.addScalar("start_date", StandardBasicTypes.DATE)
				.addScalar("end_date", StandardBasicTypes.DATE)
				.addScalar("criticality_desc", StandardBasicTypes.STRING)				
				.addScalar("audit_id", StandardBasicTypes.STRING)
				.addScalar("plan_comments", StandardBasicTypes.STRING)				
				.addScalar("plan_audit_remarks", StandardBasicTypes.STRING)  // 20
				
				.setParameter("legalEntityCode", legalEntityCode)
			    .setParameter("auditTypeCcode", auditType)
			    .setParameter("parentUnitCode", parentUnitCode)
			    .setParameter("planStartDate", psDate)
			    .setParameter("planEndDate", peDate)
	            .setParameter("maker", maker)
		
				.list();
		
		logger.info("Units for planning .. "+rows!=null? rows.size():"No records.");

		AuditProcessV apt = null;
		List<AuditProcessV> ls = new ArrayList<>();
		List<String> unitIdsList = maintEntityDao.getSubBranchesGyUserIdOrUnitId(legalEntityCode, "user", maker);

		try {
			for (Object[] col : rows) {
				//if (unitIdsList.contains(col[6].toString())) {
				apt = new AuditProcessV();
				apt.setRowNum(Long.valueOf(col[0].toString()).longValue());
				apt.setAuditStatus(col[1].toString());
				apt.setAuditTypeCode(col[2].toString());
				apt.setLegalEntityCode(col[3].toString());
				apt.setParentUnitCode((col[4] != null) ? col[4].toString() : "");
				apt.setUnitType((col[5] != null) ? col[5].toString() : "");

				apt.setUnitName(col[6].toString());
				apt.setUnitId(col[7].toString());
				apt.setFrequency(col[8].toString());
				apt.setManDays(col[9].toString());

				//apt.setDuration(Integer.valueOf(col[9].toString()).intValue());
				apt.setDuration(Integer.valueOf(col[10].toString())); //spotbugs
				apt.setResponseTime((col[11] != null) ? col[11].toString():"");
				apt.setClosureTime((col[12] != null) ? col[12].toString():"");
				apt.setLastAuditEndDate((col[13] != null) ? col[13].toString():"");
				apt.setStartDate((col[14] != null) ? col[14].toString():"");
				apt.setEndDate((col[15] != null) ? col[15].toString():"");

				apt.setCriticalityDesc((col[16] !=null)?col[16].toString():"");
				apt.setAuditId(null);

				apt.setPlanComments((col[17] != null) ? col[17].toString() : "");
				apt.setPlanAuditRemarks((col[18] != null) ? col[18].toString() : "");

				ls.add(apt);
				}
			//}

		}catch (Exception e) {
			//logger.error(e.getMessage(),e);
			e.printStackTrace(System.out);
		}

		return ls;		//.setParameter("planStartDate", planStartDate)
	}
	
	public Map<String, String> isDateFallOnFortnight(String legalEntityCode,String unitId,String currDate) {
				
		Map<String,String> map1=null;
		int day=0, fort_param=0;
		String day_name=null, dayOfWeek=null, fortNightDayOfUnit=null, validDate="1";
		logger.info("Inside isDateFallOnFortnight .. "+ legalEntityCode +" unitId .. "+unitId+ " currDate.. "+currDate);
		try {
			Session session=getSession();
			String hql=" SELECT DAY(:currDate) AS days, WEEKDAY(:currDate) AS daysOfWeek, DAYNAME(:currDate) AS day_Name, "
					+ " (SELECT value FROM general_parameter WHERE key_1='HOLIDAY' AND key_2 ='FORTNIGHT' and legal_entity_code=:legalEntityCode) AS fort_night_param, "
					+ " (SELECT g.description FROM maint_entity e, general_parameter g WHERE e.unit_code=:unitId AND key_1='WEEK_DAY' AND key_2='WEEK_DAY' AND g.value=IFNULL(e.fortnightly_holiday,7)) AS unit_fort_night "  // IFNULL(e.fortnightly_holiday,7)) added
					//+ " (SELECT IF(ISNULL(e.fortnightly_holiday)=1, NULL,(e.fortnightly_holiday-1)) AS fort_night_day FROM maint_entity e WHERE e.unit_code=:unitId) as unit_fort_night "
					+ "	FROM dual ";
			Object obj= session.createSQLQuery(hql).setParameter("legalEntityCode",legalEntityCode)
					.setParameter("currDate",currDate).setParameter("unitId", unitId).uniqueResult();
			
			if(obj != null  && ((Object[])obj).length > 0){
				day= Integer.parseInt(((Object[])obj)[0].toString());
				dayOfWeek = ((Object[])obj)[1].toString();
				day_name = ((Object[])obj)[2].toString();
				fort_param = Integer.parseInt(((Object[])obj)[3].toString());
				fortNightDayOfUnit = ((Object[])obj)[4].toString();
				logger.info("day.. "+ day + "dayOfWeek.."+dayOfWeek +" day_name.. "+day_name+" fort_param.. "+fort_param+" fortNightDayOfUnit.."+fortNightDayOfUnit);
			}			
			
			//if("Saturday".equalsIgnoreCase(day_name)) {
			//if(dayOfWeek.equalsIgnoreCase(fortNightDayOfUnit)) {
			if(day_name.equalsIgnoreCase(fortNightDayOfUnit)) {
				if(fort_param==1 && (day >=8 && day <=14) ) { // fort_param==1, 2nd Saturday(or any day define) is a Holiday
					validDate="0";
				}else if(fort_param==2 && ((day >=8 && day <=14) || (day >=22 && day <=28) ) ){ // fort_param==2, 2nd & 4th Saturday(or any day define) is a Holiday
					validDate="0";
				}else validDate="1";
			}
				map1=new HashMap<String,String>();
				map1.put("validDate",validDate);
			logger.info("map1 .. "+map1);	
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		return map1;
	}

	public Map<String, String> isDateFallOnHoliday(String legalEntityCode,String unitId,String currDate) {
		
		Map<String,String> map1=null;
		String day_name=null, validDate="1";
		logger.info("Inside isDateFallOnHoliday .. "+ legalEntityCode +" unitId .. "+unitId+ " currDate.. "+currDate);
		Session session=getSession();
		try {
			MaintEntity maintEntity=(MaintEntity)session.createQuery(" from MaintEntity me "
					+ " where me.legalEntityCode =:legalEntityCode "
					+ " and me.unitCode =:unitCode")
			.setParameter("legalEntityCode", Integer.parseInt(legalEntityCode))
			.setParameter("unitCode", unitId).uniqueResult();
			
			String stateCode=(maintEntity!=null?maintEntity.getState():"");
			stateCode=","+stateCode+",";
			logger.info("stateCode .. "+stateCode);	
			
			String hql=" SELECT id FROM maint_holiday h, maint_entity e  "
					+ "	WHERE holiday_date=:currDate AND CONCAT(',',h.state_code,',') LIKE '%:"+stateCode+"%' AND e.unit_code=:unitId "
					+ " AND h.legal_entity_code=e.legal_entity_code AND h.legal_entity_code=:legalEntityCode ";
			List lst= session.createSQLQuery(hql).setParameter("legalEntityCode",legalEntityCode)
					.setParameter("unitId", unitId).setParameter("currDate", currDate).list();
			logger.info("lst .. "+lst);	
			
			if(!lst.isEmpty()) {
				validDate="0";
			}
				map1=new HashMap<String,String>();
				map1.put("validDate",validDate);
			logger.info("map1 .. "+map1);	
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		return map1;
	}

	
}
