package com.bankaudit.rest.process.dao;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;

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

import com.bankaudit.constants.BankAuditConstant;
import com.bankaudit.dao.AbstractDao;
import com.bankaudit.dto.DataTableResponse;
import com.bankaudit.model.User;
import com.bankaudit.model.UserWrk;
import com.bankaudit.process.model.AuditPlan;
import com.bankaudit.process.model.AuditSchedule;
import com.bankaudit.process.model.AuditTeam;
import com.bankaudit.process.model.AuditTeamWrk;
import com.bankaudit.process.model.ChecklistOfflineInspection;
import com.bankaudit.util.BankAuditUtil;

@Repository("auditTeamDao")
public class AuditTeamDaoImpl extends AbstractDao implements AuditTeamDao {
	
	static final Logger logger = Logger.getLogger(AuditTeamDaoImpl.class);
	
	@SuppressWarnings("unchecked")
	@Override
	public DataTableResponse getAuditsList(int legalEntityCode,String userId, String search, Integer orderColumn,
			String orderDirection, Integer page, Integer size, List<String> unitList ) 
	{
		logger.info(" getAuditsList .."+ userId+ " unitList .."+unitList);
		Session session = getSession();
		session.setDefaultReadOnly(true);

		/********************** This block is to retrieve the User details --> UserId and UserName **************************/
		StringBuilder queryString0=new StringBuilder(// " Select concat(u.firstName , ' ' , u.lastName) as firstName, u.userId "
				 " from User u  "
				+ " where "
				+ " u.legalEntityCode =:legalEntityCode "
				);
		
		Query query1=session.createQuery(queryString0.toString())
				.setParameter("legalEntityCode", legalEntityCode);
		
		List lst = query1.list();
		HashMap<String, String> userDtls = new HashMap<String, String>();
		for ( Iterator iter = lst.iterator(); iter.hasNext(); ) {
			User usr = (User) iter.next();
			if(usr.getUserId().equalsIgnoreCase(userId))
				userDtls.put(usr.getUserId(), "Me"); // for the same User we are stamping as "You"
			else userDtls.put(usr.getUserId(), usr.getFirstName()+" "+usr.getMiddleName()+" "+usr.getLastName());	
		}
		System.out.println("userId is .... "+ userId+" serach:: "+search +" legal entity code:: "+legalEntityCode);
		System.out.println("User details val is .... "+ userDtls);
		/******************** User Retrieval logic, Ends here ********************************/
		
		DataTableResponse dataTableResponse=new DataTableResponse();
		try {
		//StringBuilder queryString=new StringBuilder(" from AuditSchedule where legalEntityCode =:legalEntityCode ");
		StringBuilder queryString=new StringBuilder("select sch as auditSchedule, " 
				+ " legalEntityCode,"
				+ "(select concat(auditTypeCode , '-' , auditTypeDesc) from MaintAuditTypeDesc at where at.auditTypeCode=sch.auditTypeCode and at.legalEntityCode= sch.legalEntityCode ) as auditTypeCode,"
				+ "planId,auditId, "
				+ " (Select concat(unitCode , '-' , unitName) from  MaintEntity e where e.unitCode =sch.unitId and e.legalEntityCode =sch.legalEntityCode ) as unitId,  "
				+ " (Select g.description from  MaintEntity e,GeneralParameter g where e.unitCode =sch.unitId and e.legalEntityCode =sch.legalEntityCode and e.legalEntityCode =g.legalEntityCode and g.key1='UNIT_TYPE' and g.key2='UNIT_TYPE' and g.value=e.unitType) as unitType, "
				+ " (Select c.criticalityDesc from  MaintEntity e,MaintCriticality c where e.unitCode =sch.unitId and e.legalEntityCode =sch.legalEntityCode and e.legalEntityCode =c.legalEntityCode and e.uCriticality=c.criticalityCode and c.criticalityOfType='Entity') as uCriticality, "
				//+ " (Select concat(u.firstName , ' ' , u.lastName) from  User u,AuditTeam t where u.userId =t.userId and t.auditId=sch.auditId and t.legalEntityCode =u.legalEntityCode and t.legalEntityCode =sch.legalEntityCode and t.userType='L') as teamLead, "
				+ " ( case when sch.teamStatus='U' "
				+ "        then (Select t.userId from  AuditTeamWrk t where t.auditId=sch.auditId and t.legalEntityCode =sch.legalEntityCode and t.userType='L') "
				+ "		   else (Select t.userId from  AuditTeam t where t.auditId=sch.auditId and t.legalEntityCode =sch.legalEntityCode and t.userType='L') end ) as teamLead, "
				//+ " if(sch.teamStatus='U',(Select t.userId from  AuditTeamWrk t where t.auditId=sch.auditId and t.legalEntityCode =sch.legalEntityCode and t.userType='L') , "
				//+ "						  (Select t.userId from  AuditTeam t where t.auditId=sch.auditId and t.legalEntityCode =sch.legalEntityCode and t.userType='L') ) as teamLead,"
				+" duration," //Code to comment //+ " (Select p.duration from  MaintEntity e,MaintAuditParameter p where e.unitCode =sch.unitId and e.unitType=p.unitType and e.uCriticality=p.uCriticality and sch.auditTypeCode=p.auditTypeCode and e.legalEntityCode =sch.legalEntityCode and e.legalEntityCode =p.legalEntityCode ) as duration, "
				+" frequency," //Code to comment //+ " (Select p.frequency from  MaintEntity e,MaintAuditParameter p where e.unitCode =sch.unitId and e.unitType=p.unitType and e.uCriticality=p.uCriticality and sch.auditTypeCode=p.auditTypeCode and e.legalEntityCode =sch.legalEntityCode and e.legalEntityCode =p.legalEntityCode ) as frequency, "
				//+ " (select tm.auditTypeCode,GROUP_CONCAT(tm.name) from (Select t.auditTypeCode,t.planId, t.auditId, concat(u.firstName , ' ' , u.lastName) as name from  User u,AuditTeam t where u.userId =t.userId and t.auditId=sch.auditId and t.legalEntityCode =u.legalEntityCode and t.legalEntityCode =sch.legalEntityCode and t.userType='T') tm where  GROUP BY tm.auditTypeCode  ) as teamMember "
				+ " (Select concat(sch.auditStatus , '-' , g.description) from  GeneralParameter g where g.legalEntityCode =sch.legalEntityCode and g.key1='STATUS' and g.key2='AUDIT_STATUS' and g.value=sch.auditStatus) as auditStatus, "
				+ " (Select concat(u.userId , '-' , u.firstName, ' ',u.middleName,' ',u.lastName) from  User u where u.legalEntityCode =sch.legalEntityCode and u.userId=sch.teamMaker) as teamMaker "
				//+ ", (Select count(*) from  AuditObservation o where o.legalEntityCode =sch.legalEntityCode and o.auditId=sch.auditId and o.complianceCategory='N' and o.reviewStatus='R' ) as responseReject "
				//+ ", (Select count(*) from  AuditObservation o where o.legalEntityCode =sch.legalEntityCode and o.auditId=sch.auditId and o.complianceCategory='C' and o.submissionStatus='A') as complianceCount "
				//+ ", (Select count(*) from  AuditObservation o where o.legalEntityCode =sch.legalEntityCode and o.auditId=sch.auditId and o.complianceCategory='NA' and o.submissionStatus='A') as notApplicableCount "
				+" ,0,0,0 "  // No need to calculate responseReject, Compliance, NA
				//+ " , auditStatus "
				+ ", (Select g.value from GeneralParameter g where g.legalEntityCode =sch.legalEntityCode and g.key1='AUDIT_EARLY_START' and g.key2='EARLY_START_ALLOWED') as earlyStartAllowed "
				+ ", (Select g.value from GeneralParameter g where g.legalEntityCode =sch.legalEntityCode and g.key1='AUDIT_OFFSET_DAYS' and g.key2='AUDIT_OFFSET_DAYS') as auditOffsetDays " 
				+ ", (Select concat(sch.auditStatus , '-' , g.description) from  GeneralParameter g where g.legalEntityCode =sch.legalEntityCode and g.key1='STATUS' and g.key2='AUDIT_STATUS' and g.value=sch.observationStatus) as auditStatusTemp " // This result to stamp in Audit status in the Observation status is AE i.e Audit Reported
				//+ ", if(sch.teamStatus='U', (select group_concat(t.userId) from AuditTeamWrk t where t.legalEntityCode=sch.legalEntityCode AND t.auditId=sch.auditId AND t.userType='M' ) ,"
				//+ "             (select group_concat(t.userId) from AuditTeam t where t.legalEntityCode=sch.legalEntityCode AND t.auditId=sch.auditId AND t.userType='M') ) AS teamMember "  // 19 Team member
				+ " , ( case when sch.teamStatus='U' "
				+ "        then (select group_concat(t.userId) from AuditTeamWrk t where t.legalEntityCode=sch.legalEntityCode AND t.auditId=sch.auditId AND t.userType='M' ) "
				+ "		   else (select group_concat(t.userId) from AuditTeam t where t.legalEntityCode=sch.legalEntityCode AND t.auditId=sch.auditId AND t.userType='M') end ) as teamMember "
				
				
				+ ", (Select concat(sch.teamStatus , '-' , g.description) from  GeneralParameter g where g.legalEntityCode =sch.legalEntityCode and g.key1='STATUS' and g.key2='STATUS' and g.value=sch.teamStatus) as teamStatus " 
				
				+ " from AuditSchedule sch where legalEntityCode =:legalEntityCode "
				+ " AND sch.unitId IN (:unitList) AND  IFNULL(auditStatus,'NotNull') IN ('AP','AI') ");
						
				
		System.out.println("Before the qry....");
		
		if(!BankAuditUtil.isEmptyString(search)){
			queryString=queryString.append(" and ( "
					+ " sch.auditTypeCode IN (SELECT a.auditTypeCode FROM MaintAuditTypeDesc a  WHERE a.legalEntityCode=sch.legalEntityCode AND (a.auditTypeDesc LIKE :search OR a.auditTypeCode LIKE :search)) "
					+ " or planId like :search "
					+ " or auditId like :search "
					//+ " or unitId like :search "
					+ " or sch.unitId IN (SELECT m.unitCode FROM MaintEntity m WHERE m.legalEntityCode=sch.legalEntityCode AND (m.unitName LIKE :search or m.unitCode LIKE :search )) "
					+ " or sch.unitId IN (SELECT m.unitCode FROM MaintEntity m,GeneralParameter g WHERE sch.unitId=m.unitCode and m.legalEntityCode=sch.legalEntityCode AND m.legalEntityCode =g.legalEntityCode and g.key1='UNIT_TYPE' and g.key2='UNIT_TYPE' and g.value=m.unitType and g.description LIKE :search) "
					+ " or sch.unitId IN (SELECT m.unitCode FROM MaintEntity m,MaintCriticality c WHERE sch.unitId=m.unitCode and m.legalEntityCode=sch.legalEntityCode AND m.legalEntityCode =c.legalEntityCode and m.uCriticality=c.criticalityCode and c.criticalityOfType='Entity' and  c.criticalityDesc LIKE :search) "
					+ " or sch.unitId IN (SELECT m.unitCode FROM MaintEntity m,MaintAuditParameter p WHERE sch.unitId=m.unitCode and m.legalEntityCode=sch.legalEntityCode AND m.legalEntityCode =p.legalEntityCode and m.uCriticality=p.uCriticality and sch.auditTypeCode=p.auditTypeCode and  p.duration LIKE :search) "
					+ " or sch.auditId IN (SELECT t.auditId FROM User u,AuditTeam t WHERE t.auditId=sch.auditId and t.legalEntityCode =sch.legalEntityCode and t.legalEntityCode =u.legalEntityCode and t.userId=u.userId and (t.userId Like :search or u.firstName LIKE :search or u.lastName Like :search) ) "
					+ " or DATE_FORMAT(sch.scheduleStartDate, '%d %b %Y') LIKE :search "
					+ " or DATE_FORMAT(sch.scheduleEndDate, '%d %b %Y') LIKE :search "
					+ " or DATE_FORMAT(sch.actualStartDate, '%d %b %Y') LIKE :search "
					+ " or DATE_FORMAT(sch.actualEndDate, '%d %b %Y') LIKE :search "
					+ " or DATE_FORMAT(sch.lastAuditDate, '%d %b %Y') LIKE :search "
					+ " or sch.maker IN (SELECT u.userId FROM User u WHERE u.legalEntityCode =sch.legalEntityCode and (u.userId Like :search or u.firstName LIKE :search or u.lastName Like :search) ) "
					+ " or sch.auditStatus IN (SELECT g.value FROM GeneralParameter g WHERE sch.legalEntityCode =g.legalEntityCode and g.key1='STATUS' and g.key2='AUDIT_STATUS' and g.value=sch.auditStatus and g.description LIKE :search) "
					// + " or teamMember like :search "
					+ " or sch.auditId IN (select t.auditId from AuditTeam t, User u where t.legalEntityCode=sch.legalEntityCode AND t.auditId=sch.auditId AND t.userType='M' AND t.legalEntityCode=u.legalEntityCode AND t.userId=u.userId "
					+ "						AND sch.unitId=u.unitCode AND (u.firstName like :search OR u.middleName like :search OR u.lastName like :search OR u.userId like :search )) "
					+ " ) "
					// + " )"
					);
		}
		
		/*queryString=queryString.append("  ORDER BY FIELD(auditStatus, 'AI','P','AP','AS','AC','AU') asc ");*/
		String[] columns={
				"auditTypeCode",
				"(auditId *1 ), (planId *1 )", //"CAST(planId AS Integer),CAST(auditId AS Integer) "
				"unitType",
				"(unitId * 1) ",  //"unitId",
				"uCriticality",
				"duration",
				"lastAuditDate",//last audit date actually 
				"sch.scheduleStartDate",
				"sch.scheduleEndDate",
				"sch.actualStartDate",
				"sch.actualEndDate",
				"teamLead", // Team Lead
				// "sch.maker", // Team Member
				"sch.maker",
				"sch.auditStatus"
		};

		logger.info("orderColumn **.... "+ orderColumn +"  orderDirection ...."+ orderDirection );
		if(orderColumn != null && orderColumn == -1 && !BankAuditUtil.isEmptyString(orderDirection)){ // -1 send for the first time from the UI during landing page.
			queryString=queryString.append(" ORDER BY FIELD(auditStatus, 'AI','AP') asc  "); // The code modified to get the records in the order of Status on landing page, the sequence is  Initiated, Planned, Submitted, Respond, Reviewed, Closed
		}else if(orderColumn!=null && !BankAuditUtil.isEmptyString(orderDirection)){
			if(orderColumn==0) {
				queryString=queryString.append(" ORDER BY  ").append(columns[orderColumn]).append(" ").append(orderDirection).append(" , FIELD(auditStatus, 'AI','AP') asc ,(auditId *1 ) asc  ");
			}else {
				queryString=queryString.append(" ORDER BY  ").append(columns[orderColumn]).append(" ").append(orderDirection);
			}
		}else {
			queryString=queryString.append(" ORDER BY FIELD(auditStatus, 'AI','AP') asc  ");
		}
		
		System.out.println("queryString .... "+ queryString.toString());
		
		Query query=(Query) session.createQuery(queryString.toString())				
				.setResultTransformer(new ResultTransformer() {
					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						AuditSchedule auditSchedule=null;
						//System.out.println("tuple[0] ...."+tuple[0]);
						if(tuple[0]!=null){
							auditSchedule=(AuditSchedule)tuple[0];
							/* session.evict(maintEntity);*/
							
							if(tuple[1]!=null){
								auditSchedule.setLegalEntityCode(Integer.parseInt(tuple[1].toString()));								
							}
							if(tuple[2]!=null){
								auditSchedule.setAuditTypeCode(tuple[2].toString());
							}
							
							if(tuple[3]!=null){
								auditSchedule.setPlanId(tuple[3].toString());
							}
							
							if(tuple[4]!=null){
								auditSchedule.setAuditId(tuple[4].toString());
							}
							if(tuple[5]!=null){
								auditSchedule.setUnitId(tuple[5].toString());
							}
							if(tuple[6]!=null){
								auditSchedule.setUnitType(tuple[6].toString());
							}
							if(tuple[7]!=null){
								auditSchedule.setuCriticality(tuple[7].toString());
							}
							if(tuple[8]!=null){
								//auditSchedule.setTeamLead(tuple[8].toString()+"-"+userDtls.get(tuple[8].toString()));
								auditSchedule.setTeamLead(userDtls.get(tuple[8].toString()));
							}
							if(tuple[9]!=null){
								auditSchedule.setDuration(tuple[9].toString());
							}
							if(tuple[10]!=null){
								auditSchedule.setFrequency(tuple[10].toString());
							}
							if(tuple[11]!=null){
								if("P".equalsIgnoreCase(tuple[11].toString()))auditSchedule.setAuditStatus("AP"); // temp purpose as in few earlier records the Planned status is reflecting as "P"
								else if("AE".equalsIgnoreCase(auditSchedule.getObservationStatus())) auditSchedule.setAuditStatus(tuple[18] !=null ?tuple[18].toString() :tuple[11].toString() );
								else auditSchedule.setAuditStatus(tuple[11].toString());
							}
							if(tuple[12]!=null){
								auditSchedule.setTeamMaker(tuple[12].toString());
							}
							if(tuple[13]!=null && auditSchedule.getRespondedFindings() != null){
								auditSchedule.setResponseReject(Integer.parseInt(tuple[13].toString()));
								auditSchedule.setRespondedFindings(auditSchedule.getRespondedFindings()- (Integer.parseInt(tuple[13].toString()))); // Included to find out the Responded Observations
							}else auditSchedule.setResponseReject(0);
							
							if(tuple[14]!=null){
								auditSchedule.setComplianceCount(Integer.parseInt(tuple[14].toString()));
							}else auditSchedule.setComplianceCount(0);
							
							if(tuple[15]!=null){
								auditSchedule.setNotApplicableCount(Integer.parseInt(tuple[15].toString()));
							}else auditSchedule.setNotApplicableCount(0);
							
							if(tuple[16]!=null){
								auditSchedule.setEarlyStartAllowed(tuple[16].toString());
							}else auditSchedule.setEarlyStartAllowed("Y");
							
							if(tuple[17]!=null){
								auditSchedule.setAuditOffsetDays(Integer.parseInt(tuple[17].toString()));
							}else auditSchedule.setAuditOffsetDays(0);
							
							if(tuple[19]!=null){
								String teamMembers="";
								String[] members = tuple[19].toString().split(",");
								for (String member: members) {
									teamMembers+=member+"-"+userDtls.get(member)+",";
								}
								auditSchedule.setTeamMember(teamMembers.substring(0,teamMembers.length()-1));
							}
							
							/*int tmSize =auditSchedule.getAuditTeams().size()-1;
							String teamMember="";
							System.out.println("Audit Id .... "+ auditSchedule.getAuditId()+" tmSize .."+tmSize);
							for( AuditTeam adtTm: auditSchedule.getAuditTeams() ) {
								
								adtTm.setUserId(adtTm.getUserId()+"-"+userDtls.get(adtTm.getUserId()));
								
								if(!"L".equalsIgnoreCase(adtTm.getUserType())) teamMember +=adtTm.getUserId() + ",";
								System.out.println("teamMember .... "+ teamMember);
							}if(tmSize >0 ) auditSchedule.setTeamMember(teamMember.substring(0,teamMember.lastIndexOf(",")));*/
							//System.out.println("Team member .... "+auditSchedule.getTeamMember());
							//System.out.println("Team size ...."+auditSchedule.getAuditTeams().size());
							
							if(tuple[20]!=null) auditSchedule.setTeamStatus(tuple[20].toString());
						}
						return auditSchedule;
					}
			@Override
			public List transformList(List collection) {
				return collection;
			}
		})		
		.setParameter("legalEntityCode",legalEntityCode).setParameter("unitList", unitList);
		if(!BankAuditUtil.isEmptyString(search)){
			query.setParameter("search", "%"+search+"%");
		}
		
		System.out.println("page :: "+page +"size :: "+ size);
		List<AuditSchedule> tmpAuditSchedule =query.list(); 
		Long totalRecords=null;
		try {
			totalRecords = (long)tmpAuditSchedule.size();
		}catch(Exception e) {
			totalRecords = (long)0;
		}
		List<AuditSchedule> auditSchedule=query.setFirstResult((page*size)).setMaxResults(size).list();
		
		System.out.println("Bfr size tmpAuditSchedule is .... "+tmpAuditSchedule.size()+" totalRecords:: "+ totalRecords);
		
		/*int i=0;
		for(AuditSchedule adSch:auditSchedule){
			System.out.println("Inside adSch ...");
			if("A2".equalsIgnoreCase(adSch.getAuditId()));
			else 	tmpAuditSchedule.add(adSch);
			System.out.println("after the call");
			
		}
		System.out.println("size tmpAuditSchedule .... "+tmpAuditSchedule);*/
		
		
		dataTableResponse.setData(auditSchedule);
		dataTableResponse.setRecordsTotal(totalRecords);
		dataTableResponse.setRecordsFiltered(totalRecords);
		
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		return dataTableResponse;
	
	}
	
	
	@Override
	public AuditSchedule getAuditByPlanIdAndAuditId(int legalEntityCode, String auditTypeCode, String planId,
			String auditId) {
		Session session = getSession();
		session.setDefaultReadOnly(true);
		
		AuditSchedule auditSchedule = null;
		try {
			//StringBuilder queryString=new StringBuilder(" from AuditSchedule where legalEntityCode =:legalEntityCode ");
			StringBuilder queryString=new StringBuilder("select sch as auditSchedule, " 
					+ "(select concat(auditTypeCode , '-' , auditTypeDesc) from MaintAuditTypeDesc at where at.auditTypeCode=sch.auditTypeCode and at.legalEntityCode= sch.legalEntityCode ) as auditTypeCode,"
					+ " (Select concat(unitCode , '-' , unitName) from  MaintEntity e where e.unitCode =sch.unitId and e.legalEntityCode =sch.legalEntityCode ) as unitId,  "
					+ " (Select concat(sch.auditStatus , '-' , g.description) from  GeneralParameter g where g.legalEntityCode =sch.legalEntityCode and g.key1='STATUS' and g.key2='AUDIT_STATUS' and g.value=sch.auditStatus) as auditStatus "
					+ " from AuditSchedule sch where legalEntityCode =:legalEntityCode  and auditId=:auditId "); /* and planId=:planId */
			System.out.println("Before the qry....");
			
			Query query=(Query) session.createQuery(queryString.toString())				
					.setResultTransformer(new ResultTransformer() {
						@Override
						public Object transformTuple(Object[] tuple, String[] aliases) {
							AuditSchedule auditSchedule=null;
							System.out.println("tuple[0] ...."+tuple[0]);
							if(tuple[0]!=null){
								auditSchedule=(AuditSchedule)tuple[0];
								if(tuple[1]!=null)	auditSchedule.setAuditTypeCode(tuple[1].toString());								
								if(tuple[2]!=null)	auditSchedule.setUnitName(tuple[2].toString());
								if(tuple[3]!=null)	auditSchedule.setAuditStatusDescription(tuple[3].toString());
							}
							return auditSchedule;
						}
				@Override
				public List transformList(List collection) {
					return collection;
				}
			})      .setParameter("legalEntityCode",legalEntityCode)
					.setParameter("auditId",auditId);
			auditSchedule=(AuditSchedule)query.uniqueResult(); 
		}catch(Exception e) {
			e.printStackTrace();
		}return auditSchedule; 
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<AuditTeam> getAuditTeamsByAuditId(int legalEntityCode,String auditTypeCode, String planId,String auditId){
		
		List<AuditTeam> auditTeams = new ArrayList<AuditTeam>();
		Session session=getSession();
		Class className=null;
		
		try {
			Long count1=(Long)session.createQuery(" select count(*) from AuditTeamWrk  where legalEntityCode =:legalEntityCode AND auditId =:auditId ")
			.setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId).uniqueResult();
			
			if(count1 >0 )className=AuditTeamWrk.class;
			else { 
				Long count2=(Long)session.createQuery(" select count(*) from AuditTeam  where legalEntityCode =:legalEntityCode AND auditId =:auditId ")
					.setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId).uniqueResult();
				if(count2 >0 )className=AuditTeam.class;
			}
			
			if(className != null) {
				StringBuilder queryString=new StringBuilder("select  legalEntityCode , planId, auditId, userType,  "  // at as auditTeam,
						+ " (select concat(auditTypeCode , '-' , auditTypeDesc) from MaintAuditTypeDesc atd where atd.auditTypeCode=at.auditTypeCode and atd.legalEntityCode= at.legalEntityCode ) as auditTypeCode,"
						+ " (Select concat(unitCode , '-' , unitName) from  MaintEntity e where e.unitCode =at.unitId and e.legalEntityCode =at.legalEntityCode ) as unitId,  "
						+ " (Select concat(u.userId , '-' , u.firstName, ' ',u.middleName,' ',u.lastName) from  User u where u.legalEntityCode =at.legalEntityCode and u.userId=at.userId) as userId " 
						+ " FROM " + className.getSimpleName() +" at"
						+ " WHERE legalEntityCode =:legalEntityCode AND auditId=:auditId ");
				
				Query query=(Query) session.createQuery(queryString.toString())				
					.setResultTransformer(new ResultTransformer() {
						@Override
						public Object transformTuple(Object[] tuple, String[] aliases) {
							AuditTeam auditTeam=new AuditTeam();
							if(tuple[0]!=null) auditTeam.setLegalEntityCode(Integer.parseInt(tuple[0].toString()));
							if(tuple[1]!=null) auditTeam.setPlanId(tuple[1].toString());
							if(tuple[2]!=null) auditTeam.setAuditId(tuple[2].toString());
							if(tuple[3]!=null) auditTeam.setUserType(tuple[3].toString());
							if(tuple[4]!=null) auditTeam.setAuditTypeCode(tuple[4].toString());
							if(tuple[5]!=null) auditTeam.setUnitId(tuple[5].toString());
							if(tuple[6]!=null) auditTeam.setUserId(tuple[6].toString());
								
							return auditTeam;
						}
					@Override
					public List transformList(List collection) {
						return collection;
					}
				}).setParameter("legalEntityCode",legalEntityCode).setParameter("auditId",auditId); 
				logger.info("legalEntityCode :: "+legalEntityCode +"auditId :: "+ auditId);
				auditTeams =query.list(); 
			}
		}catch(Exception e) {
			e.printStackTrace();
		}
		return auditTeams;
	}

	
	@Override
	public void deleteAuditTeam(Integer legalEntityCode, String auditId, String statusUnauth) {

		Session session=getSession();
		logger.info("deleteAuditTeam .. "+ legalEntityCode +" :: "+ auditId +" :: "+ statusUnauth);
		if(!BankAuditConstant.STATUS_AUTH.equals(statusUnauth)){
			session.createQuery("delete from AuditTeamWrk  "
					+ " where legalEntityCode =:legalEntityCode "
					+ " and  auditId =:auditId  ").setParameter("auditId", auditId)
			.setParameter("legalEntityCode", legalEntityCode).executeUpdate();
		}else {
			session.createQuery("delete from AuditTeam  "
					+ " where legalEntityCode =:legalEntityCode "
					+ " and  auditId =:auditId ").setParameter("auditId", auditId)
			.setParameter("legalEntityCode", legalEntityCode).executeUpdate() ;
		}
	}
	
	@Override
	public void deleteAuditTeamByIdAndType(Integer legalEntityCode, String auditId, String auditTypeCode, String statusUnauth) {
		Session session=getSession();
		logger.info("deleteAuditTeamByIdAndType .. "+ legalEntityCode +" :: "+ auditId+ " :: "+ auditTypeCode +" :: "+ statusUnauth);
		if(!BankAuditConstant.STATUS_AUTH.equals(statusUnauth)){
			session.createQuery(" DELETE FROM AuditTeamWrk WHERE legalEntityCode=:legalEntityCode AND auditId=:auditId AND auditTypeCode=:auditTypeCode ")
				.setParameter("auditId", auditId).setParameter("auditTypeCode", auditTypeCode).setParameter("legalEntityCode", legalEntityCode).executeUpdate();
		}else {
			session.createQuery("DELETE from AuditTeam WHERE legalEntityCode=:legalEntityCode AND auditId=:auditId AND auditTypeCode=:auditTypeCode ")
				.setParameter("auditId", auditId).setParameter("auditTypeCode", auditTypeCode).setParameter("legalEntityCode", legalEntityCode).executeUpdate();
		}
		session.flush();
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<AuditTeam> getAuditTeamsByAuditIdAndType(int legalEntityCode,String auditTypeCode,String auditId){
		Session session = getSession();
		logger.info("Inside getAuditTeamsByAuditIdAndType .. "+ legalEntityCode +" :: "+auditTypeCode+" :: "+ auditId);
		return (List<AuditTeam>)session.createQuery(" From AuditTeam at WHERE at.legalEntityCode=:legalEntityCode AND at.auditTypeCode=:auditTypeCode "
					+ " AND at.auditId=:auditId ").setParameter("legalEntityCode", legalEntityCode).setParameter("auditTypeCode", auditTypeCode)
					.setParameter("auditId", auditId).list();
		
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public AuditTeam getAuditTeamLeadByAuditIdAndType(int legalEntityCode,String auditTypeCode,String auditId){
		Session session = getSession();
		logger.info("Inside getAuditTeamLeadByAuditIdAndType .. "+ legalEntityCode +" :: "+auditTypeCode+" :: "+ auditId);
		return (AuditTeam)session.createQuery(" From AuditTeam at WHERE at.legalEntityCode=:legalEntityCode AND at.auditTypeCode=:auditTypeCode "
					+ " AND at.auditId=:auditId AND at.userType='L' ").setParameter("legalEntityCode", legalEntityCode).setParameter("auditTypeCode", auditTypeCode)
					.setParameter("auditId", auditId).uniqueResult();		
	}
	
	public void updateAuditTeamLead(int legalEntityCode, String auditTypeCode, String auditId, String leadId){
		logger.info("Inside updateAuditTeamLead .. "+ legalEntityCode +" :: "+auditTypeCode+" :: "+ auditId+" :: "+leadId);
		try{			
			Session session =getSession();  		
			String updateQry = "UPDATE AuditTeam SET userId =:leadId "
					+ " WHERE auditId =:auditId AND auditTypeCode =:auditTypeCode AND legalEntityCode =:legalEntityCode AND userType ='L' ";
			
			int updateCnt = session.createQuery(updateQry).setParameter("leadId", leadId).setParameter("auditId", auditId)
					.setParameter("auditTypeCode", auditTypeCode).setParameter("legalEntityCode", legalEntityCode).executeUpdate();			
			logger.info("Updated the Lead count .."+ updateCnt);			
		}catch (Exception e) { 
			logger.error("Error .." + e.getMessage()+" :: "+e.getCause());
		}
	}

}
