package com.bankaudit.dao;

import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.ScrollMode;
import org.hibernate.ScrollableResults;
import org.hibernate.Session;
import org.hibernate.transform.ResultTransformer;
import org.hibernate.transform.Transformers;
import org.hibernate.type.StandardBasicTypes;
import org.jboss.logging.Logger;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Repository;

import com.bankaudit.constants.BankAuditConstant;
import com.bankaudit.dto.DataTableResponse;
import com.bankaudit.model.MaintEntity;
import com.bankaudit.model.MaintEntityWrk;
import com.bankaudit.model.User;
import com.bankaudit.process.model.IfcEnsureBaasData;
import com.bankaudit.util.BankAuditUtil;

import java.util.ArrayList;

@Repository("maintEntityDao")
public class MaintEntityDaoImpl  extends AbstractDao implements MaintEntityDao{
	
	static final Logger logger = Logger.getLogger(MaintEntityDaoImpl.class);

	@Override
	public MaintEntity getUnique(Integer legalEntityCode, String unitCode,String status) {
		
		Session session=getSession();
		MaintEntity maintEntity=null;
	
		if(!status.equalsIgnoreCase(BankAuditConstant.STATUS_AUTH)){
			MaintEntityWrk maintEntityWrk=(MaintEntityWrk)session.createQuery("from MaintEntityWrk me "
					+ " where me.legalEntityCode =:legalEntityCode "
					+ " and me.unitCode =:unitCode")
			.setParameter("legalEntityCode", legalEntityCode)
			.setParameter("unitCode", unitCode).uniqueResult();
			
			if(maintEntityWrk!=null){
				maintEntity=new MaintEntity();
				BeanUtils.copyProperties(maintEntityWrk, maintEntity);
			}
			return maintEntity;
			
		}else {
			return (MaintEntity)session.createQuery("from MaintEntity me " 
					+ " where me.legalEntityCode =:legalEntityCode "
					+ " and me.unitCode =:unitCode")
			.setParameter("legalEntityCode", legalEntityCode)
			.setParameter("unitCode", unitCode).uniqueResult();
		}
		
	}

	@SuppressWarnings("unchecked")
	@Override
	public DataTableResponse getMaintEntity(Integer legalEntityCode, String levelCodeStr,String userId,String parentUnitCode, String search,
			Integer orderColumn, String orderDirection, Integer page, Integer size) {
		
		
	    Session session = getSession();
	    
		session.setDefaultReadOnly(true);
	    
		List<MaintEntity> maintEntitys = null;
		
		DataTableResponse dataTableResponse=new DataTableResponse();

		StringBuilder queryString2=new StringBuilder("select  "
				
                  + "unit_name, "
  
			      + "  (SELECT concat(unit_code , '-' , unit_name) FROM maint_entity WHERE "
			      + " unit_code= me.parent_unit_code AND legal_entity_code =:legal_entity_code)as parentUnitCode , "
			    
    			  + "(select concat(gp.value , '-' , gp.description)"
    			  + " from general_parameter gp where "
    			  + " gp.key_1='STATUS' and gp.value=me.status "
    			  + " and gp.legal_entity_code=me.legal_entity_code ) as status , "
			    
				  + "(select concat(gp.value , '-' , gp.description)"
				  + " from general_parameter gp where "
				  + " gp.key_1='ENTITY_STATUS' and gp.value=me.entity_status "
				  + " and gp.legal_entity_code=me.legal_entity_code ) as entityStatus , "
				  
				  + "(select concat(gp.value , '-' , gp.description)"
				  + " from general_parameter gp where "
				  + " gp.key_1='UNIT_TYPE' and gp.value=me.unit_type "
				  + " and gp.legal_entity_code=me.legal_entity_code ) as unit_type, "
	              
			      + "(select concat(u.user_id , '-' ,u.first_name,' ',u.last_name )"
			      + " from user u where "
			      + " u.user_id=me.contact_person "
			      + " and u.legal_entity_code=me.legal_entity_code ) as contactPerson , "
			      
			      + "(select distinct concat(u.country_alpha3_code , '-' ,u.country_name )"
			      + " from maint_address u where "
			      + " u.country_alpha3_code=me.country "
			      + " and u.legal_entity_code=me.legal_entity_code ) as country , "
                  
			      + "(select distinct concat(u.state_code , '-' ,u.state_name )"
			      + " from maint_address u where "
			      + " u.state_code=me.state "
			      + " and u.country_alpha3_code=me.country "
			      + " and u.legal_entity_code=me.legal_entity_code ) as state , "
                  
                  
			      + "(select distinct concat(u.district_code , '-' ,u.district_name )"
			      + " from maint_address u where "
			      + " u.district_code=me.district "
			      + " and u.country_alpha3_code=me.country "
			      + " and u.state_code=me.state "
			      + " and u.legal_entity_code=me.legal_entity_code ) as district , "
			      	
			      + "(select distinct concat(u.city_code , '-' ,u.city_name )"
			      + " from maint_address u where "
			      + " u.city_code=me.city "
			      + " and u.district_code=me.district "
			      + " and u.country_alpha3_code=me.country "
			      + " and u.state_code=me.state "
			      + " and u.legal_entity_code=me.legal_entity_code ) as city ,  "
			     
			      + "(select concat(u.user_id , '-' ,u.first_name,' ',u.last_name )"
			      + " from user u where "
			      + " u.user_id=me.maker "
			      + " and u.legal_entity_code=me.legal_entity_code ) as maker ,  "
			      
			      + "(select concat(u.user_id , '-' ,u.first_name,' ',u.last_name )"
			      + " from user u where "
			      + " u.user_id=me.checker "
			      + " and u.legal_entity_code=me.legal_entity_code ) as checker ,  "
			      
				   + "  (SELECT concat(level_code , '-' , level_desc) FROM entity_level_code_desc WHERE "
				   + " level_code= me.level_code AND legal_entity_code =:legal_entity_code) as level_code , "

			      
			
                  
                  + "legal_entity_code, "
                  + "address1, "
                  + "address2, "
                  + "address3, "
                  + "pincode, "
                  + "landline1, "
                  + "landline2, "
                  + "mobile1, "
                  + "mobile2, "
                  + "mailid, "
                  + "u_criticality, "
                  + "weekly_holiday, "
                  + "fortnightly_holiday, "
                  + "unit_open_date, "
                  + "unit_head_inception_date, "
                  + "auth_rej_remarks, "
                  + "maker_timestamp, "
                  + "checker_timestamp, "
                  + "businessdate_timestamp, "
                  + "sysdate_timestamp, "
                 /* + "unit_head_user , "*/
              	+ "(select concat(u.user_id , '-' ,u.first_name,' ',u.last_name )"
				+ " from user u where "
				+ " u.user_id=me.unit_head_user "
				+ " and u.legal_entity_code=me.legal_entity_code ) as unit_head_user ,  "
                  + "unit_code "
                  
			      + " from ( "
			      
			      + " select * from maint_entity "
			      + " where legal_entity_code =:legal_entity_code "
			      
			      + " union ALL "
			      
			      + " select * from maint_entity_wrk "
			      + " where legal_entity_code =:legal_entity_code "
			      + " and (status !='DF' OR ( status ='DF' and maker =:user_id ) )  "
			      
			      + " ) as me  where legal_entity_code =:legal_entity_code ");
		
		
		
		String[] columns={
			/*	"LENGTH( me.unit_code),  me.unit_code",*/
				
				" CAST( me.unit_code AS UNSIGNED),  me.unit_code",
				"me.unit_name",
				"me.unit_type",
                "me.parent_unit_code",
                "me.level_code",
                "me.unit_head_user",
            	"me.contact_person",
            	/*"LENGTH(),  me.mailid",*/
            	" CAST( me.mailid AS UNSIGNED),  me.mailid",
    			"me.u_criticality",
    			"me.mobile1",
    			"me.maker",
    			"me.entity_status",
				"me.status",
				};
			
		if(!BankAuditUtil.isEmptyString(parentUnitCode)){
			queryString2=queryString2.append("and me.parent_unit_code =:parent_unit_code");
		}else {
			queryString2=queryString2.append("and me.level_code =:level_code");
		}
		
		
		if(!BankAuditUtil.isEmptyString(search)){
			queryString2=queryString2.append(" and (   "
					//	+ " or  me.address1 like :search "
					/*+ " or  me.entityLevelCodeDesc.levelDesc like :search "*/
					//+ " or  me.city like :search  "
					
					+ "     me.unit_code like :search  "
					+ " or  me.unit_name like :search  "
					+ " or  me.unit_type IN (SELECT value FROM general_parameter  WHERE key_1='UNIT_TYPE' AND description LIKE :search ) "
					+ " or  me.parent_unit_code like :search  "
					+ " or  me.parent_unit_code IN (SELECT e.unit_code FROM maint_entity e WHERE e.unit_code= me.parent_unit_code AND e.legal_entity_code =:legal_entity_code and e.unit_name LIKE :search ) "
					+ " or  me.level_code IN (SELECT level_code FROM entity_level_code_desc WHERE level_desc like :search AND legal_entity_code =:legal_entity_code) "
					+ " or  me.unit_head_user IN (SELECT user_id FROM user  WHERE legal_entity_code=me.legal_entity_code AND (concat(first_name , ' ' , last_name) like :search  ) ) "
					+ " or  me.contact_person like :search  "
					+ " or  me.contact_person IN (SELECT user_id FROM user  WHERE legal_entity_code=me.legal_entity_code AND (concat(first_name , ' ' , last_name) like :search  ) ) "
					+ " or  me.mailid like :search"
					+ " or  me.u_criticality like :search  "
					+ " or  me.mobile1 like :search  "
					+ " or  me.maker like :search  "
					+ " or  me.maker IN (SELECT user_id FROM user  WHERE legal_entity_code=me.legal_entity_code AND (concat(first_name , ' ' , last_name) like :search  ) ) "
					+ " or  me.entity_status IN (SELECT value FROM general_parameter  WHERE key_1='ENTITY_STATUS' AND description LIKE :search ) "
					+ " or  me.status IN (SELECT value FROM general_parameter  WHERE key_1='STATUS' and  description LIKE :search ) "
					
					
					/*+ " or  me.country IN (SELECT country_alpha3_code FROM maint_address "
                    + " WHERE legal_entity_code=me.legal_entity_code "
                    + " AND country_name LIKE :search ) "

					+ " or  me.state IN (SELECT state_code FROM maint_address "
					+ " WHERE legal_entity_code=me.legal_entity_code "
					+ " AND country_alpha3_code =me.country"
					+ " AND state_name LIKE :search )"
					
					+ " or  me.district IN (SELECT district_code FROM maint_address "
					+ " WHERE legal_entity_code=me.legal_entity_code "
					+ " AND country_alpha3_code =me.country"
					+ " AND state_code =me.state"
					+ " AND district_name LIKE :search )"
					
					+ " or  me.city IN (SELECT city_code FROM maint_address "
					+ " WHERE legal_entity_code=me.legal_entity_code "
					+ " AND country_alpha3_code =me.country"
					+ " AND state_code =me.state"
					+ " AND district_code =me.district"
					+ " AND city_name LIKE :search )"		*/ // Remove the search criteria on Country, State, District & City, as this values are not appearing on the Landing page			
					
					+ "  )   ");
		}


		if(orderColumn != null && orderColumn == -1 && !BankAuditUtil.isEmptyString(orderDirection)){
			queryString2=queryString2.append(" order by FIELD(STATUS,'DF','R','RM','U','M','A') ASC  "); // The code modified to get the records in the order of Status on landing page, the sequence is  Draft, Reject, Unauth, Modify, Auth
		}else if(orderColumn!=null && !BankAuditUtil.isEmptyString(orderDirection)){
			queryString2=queryString2.append(" order by ").append(columns[orderColumn]).append(" ").append(orderDirection);
		}
		Query query = session.createSQLQuery(queryString2.toString())
				.setResultTransformer(new ResultTransformer() {
					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						MaintEntity maintEntity=new MaintEntity();
						if(tuple[0]!=null){
							maintEntity.setUnitName(tuple[0].toString());	
						}
						
						if(tuple[1]!=null){
							maintEntity.setParentUnitCode(tuple[1].toString());								
						}
						if(tuple[2]!=null){
							maintEntity.setStatus(tuple[2].toString());
						}
						
						if(tuple[3]!=null){
							maintEntity.setEntityStatus(tuple[3].toString());
						}
						
						if(tuple[4]!=null){
							maintEntity.setUnitType(tuple[4].toString());
						}
						if(tuple[5]!=null){
							maintEntity.setContactPerson(tuple[5].toString());
						}
						if(tuple[6]!=null){
							maintEntity.setCountry(tuple[6].toString());
						}
						if(tuple[7]!=null){
							maintEntity.setState(tuple[7].toString());
						}
						if(tuple[8]!=null){
							maintEntity.setDistrict(tuple[8].toString());
						}
						if(tuple[9]!=null){
							maintEntity.setCity(tuple[9].toString());
						}
						if(tuple[10]!=null){
							maintEntity.setMaker(tuple[10].toString());
						}
		
						if(tuple[11]!=null){
							maintEntity.setChecker(tuple[11].toString());								
						}
						if(tuple[12]!=null){
							maintEntity.setLevelCode(tuple[12].toString());
						}
						
						if(tuple[13]!=null){
							maintEntity.setLegalEntityCode(Integer.parseInt(tuple[13].toString()));
						}
						
						if(tuple[14]!=null){
							maintEntity.setAddress1(tuple[14].toString());
						}
						if(tuple[15]!=null){
							maintEntity.setAddress2(tuple[15].toString());
						}
						if(tuple[16]!=null){
							maintEntity.setAddress3(tuple[16].toString());
						}
						if(tuple[17]!=null){
							maintEntity.setPincode(Integer.parseInt(tuple[17].toString()));
						}
						if(tuple[18]!=null){
							maintEntity.setLandline1(tuple[18].toString());
						}
						if(tuple[19]!=null){
							maintEntity.setLandline2(tuple[19].toString());
						}
						if(tuple[20]!=null){
							maintEntity.setMobile1(tuple[20].toString());
						}
						if(tuple[21]!=null){
							maintEntity.setMobile2(tuple[21].toString());
						}
						
						if(tuple[22]!=null){
							maintEntity.setMailid(tuple[22].toString());
						}
						
						if(tuple[23]!=null){
							maintEntity.setuCriticality(tuple[23].toString());
						}
						
						if(tuple[24]!=null){
							maintEntity.setWeeklyHoliday(tuple[24].toString());
						}
						if(tuple[25]!=null){
							maintEntity.setFortnightlyHoliday(tuple[25].toString());
						}
						if(tuple[26]!=null){
							maintEntity.setUnitOpenDate((Date)tuple[26]);
						}
						if(tuple[27]!=null){
							maintEntity.setUnitHeadInceptionDate((Date)tuple[27]);
						}
						if(tuple[28]!=null){
							maintEntity.setAuthRejRemarks(tuple[28].toString());
						}
						if(tuple[29]!=null){
							maintEntity.setMakerTimestamp((Date)tuple[29]);
						}
						if(tuple[30]!=null){
							maintEntity.setCheckerTimestamp((Date)tuple[30]);
						}
						if(tuple[31]!=null){
							maintEntity.setBusinessdateTimestamp((Date)tuple[31]);
						}
						if(tuple[32]!=null){
							maintEntity.setSysdateTimestamp((Date)tuple[32]);
						}
						if(tuple[33]!=null){
							maintEntity.setUnitHeadUser(tuple[33].toString());
						}
						if(tuple[34]!=null){
							maintEntity.setUnitCode(tuple[34].toString());
						}
						return maintEntity;
					}
						
					@Override
					public List transformList(List collection) {
						return collection;
					}
				})
				.setParameter("legal_entity_code", legalEntityCode)
				.setParameter("user_id", userId);

		
		if(!BankAuditUtil.isEmptyString(parentUnitCode)){
			query.setParameter("parent_unit_code", parentUnitCode);
		}else {
			query.setParameter("level_code", levelCodeStr);
		}
		
		if(!BankAuditUtil.isEmptyString(search)){
			query.setParameter("search", "%"+search+"%");
		}
		
		
		ScrollableResults resultScroll = query.scroll(ScrollMode.SCROLL_INSENSITIVE);
		if(resultScroll.first()&&resultScroll.scroll(page*size)){
			
			maintEntitys= new ArrayList<>();
			int i = 0;
			while (size > i++) {
				maintEntitys.add((MaintEntity) resultScroll.get(0));
			    if (!resultScroll.next())
			        break;
			}
			dataTableResponse.setData(maintEntitys);
			resultScroll.last();
			if(size!=0){
				dataTableResponse.setRecordsFiltered((resultScroll.getRowNumber()+1l/size)+1);
				dataTableResponse.setRecordsTotal((resultScroll.getRowNumber()+1l/size)+1);
			}else{
				dataTableResponse.setError("page size zero");
				dataTableResponse.setData(Collections.emptyList());
				dataTableResponse.setRecordsTotal(0l);
				dataTableResponse.setRecordsFiltered(0l);
			}
			
		}else{
			dataTableResponse.setError(null);
			dataTableResponse.setData(Collections.emptyList());
			dataTableResponse.setRecordsTotal(0l);
			dataTableResponse.setRecordsFiltered(0l);
		}
		return dataTableResponse;

	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MaintEntity> getMaintEntityByLegalEntityCodeAndLevelCode(Integer legalEntityCode, String levelCode) {
		
		Session session=getSession();
		
		
		return session.createQuery(" select m.unitCode as unitCode , m.unitName as unitName from MaintEntity m "
				+ " where m.legalEntityCode =:legalEntityCode and m.levelCode =:levelCode and entityStatus='"+BankAuditConstant.STATUS_ACTIVE+"' "
						+ " order by  m.unitName asc")
				.setResultTransformer(Transformers.aliasToBean(MaintEntity.class))
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("levelCode", levelCode).list();
	}

	@Override
	public Boolean isHeadUser(Integer legalEntityCode, String userId, String unitCode) {
		Session session=getSession();
			
		/*
		SELECT 
		(SELECT COUNT(*) FROM USER)+(SELECT COUNT(*) FROM USER)
		FROM `maint_entity`*/
			
		StringBuilder queryString=new StringBuilder( " select "
				+ " "
				+ " (select count(*) "
				+ " from MaintEntity   "
				+ " where "
				+ " legalEntityCode =:legalEntityCode "
				+ " and unitHeadUser =:userId ");
		if(!BankAuditUtil.isEmptyString(unitCode)){
			queryString.append(" and unitCode <>:unitCode ");
		}
		queryString.append(" and entityStatus='A' ) + "
				+ " (select count(*) "
				+ " from MaintEntityWrk   "
				+ " where "
				+ " legalEntityCode =:legalEntityCode "
				+ " and unitHeadUser =:userId " );
		if(!BankAuditUtil.isEmptyString(unitCode)){
			queryString.append( " and unitCode <>:unitCode ");
		}
		queryString.append(" and entityStatus='A' ) from MaintEntity  "	);
	
		Query query=session.createQuery(queryString.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("userId", userId);
		
		if(!BankAuditUtil.isEmptyString(unitCode)){
			query.setParameter("unitCode", unitCode);
		}
	
		Long count=(Long)query.uniqueResult();
		Boolean flag=false;
		if(count!=null && count > 0 ){
			flag= true;
		}else {
			flag= false;
		}
		return flag;
	}

	@Override
	public void deleteMaintEntityWrk(Integer legalEntityCode, String unitCode) {
		
		getSession().createQuery(" delete from MaintEntityWrk "
				+ " where legalEntityCode =:legalEntityCode "
				+ " and unitCode =:unitCode")
		.setParameter("unitCode", unitCode)
		.setParameter("legalEntityCode", legalEntityCode).executeUpdate();
		
	}
	
	@Override
	public void deleteMaintEntity(Integer legalEntityCode, String unitCode) {
		
		getSession().createQuery(" delete from MaintEntity "
				+ " where legalEntityCode =:legalEntityCode "
				+ " and unitCode =:unitCode")
		.setParameter("unitCode", unitCode)
		.setParameter("legalEntityCode", legalEntityCode).executeUpdate();
		
	}

	@Override
	public Boolean isMaintEntity(Integer legalEntityCode, String unitCode) {
		Session session=getSession();
		Long count=(Long)session.createQuery(
				" select count(*) "
				+ " from MaintEntity   "
				+ " where "
				+ " legalEntityCode =:legalEntityCode "
				+ " and unitCode =:unitCode  ")
		.setParameter("legalEntityCode", legalEntityCode)
		.setParameter("unitCode", unitCode).uniqueResult();
		
		Long count1=(Long)session.createQuery(
		
				
				" select count(*) "
				+ " from MaintEntityWrk   "
				+ " where "
				+ " legalEntityCode =:legalEntityCode "
				+ " and unitCode =:unitCode ")
		.setParameter("legalEntityCode", legalEntityCode)
		.setParameter("unitCode", unitCode).uniqueResult();
		Boolean flag=false;
		if((count1!=null && count1 >0)||(count!=null && count >0)){
			flag= true;
		}else {
			flag= false;
		}
		return flag;
	}

	@Override
	public Boolean isChildUnitActivated(Integer legalEntityCode, String unitCode) {

		Session session=getSession();
		Long count =(Long)session.createQuery("select count(*) from MaintEntity "
				+ " where "
				+ " legalEntityCode =:legalEntityCode "
				+ " and parentUnitCode =:parentUnitCode "
				+ " and entityStatus =:entityStatus ")
		.setParameter("legalEntityCode", legalEntityCode)
		.setParameter("parentUnitCode", unitCode)
		.setParameter("entityStatus", BankAuditConstant.STATUS_ACTIVE).uniqueResult();
		Boolean flag=false;
		if(count!=null && count >0){
			flag= true;
		}else {
			flag= false;
		}
		
		return flag;
	}
	
	
	//This method use provide the details till 8 levels of hierarchy, use this method instead the below method which takes lots of iteration for getting the list.
	public List<String> getSubBranchesGyUserIdOrUnitId(Integer legalEntityCode,String type,String userIdOrUnitId) {
		List<String> list=null;
		try {
			
			Session session=getSession();
			String unitCode="";
			String sql="";
			if(type.equalsIgnoreCase("user")) {
				sql="SELECT u.unitCode as unitCode FROM User u WHERE u.userId=:userIdOrUnitId AND u.legalEntityCode=:legalEntityCode ";
				User usr= (User) session.createQuery(sql)
						.setResultTransformer(Transformers.aliasToBean(User.class))
						.setParameter("legalEntityCode", legalEntityCode)
						.setParameter("userIdOrUnitId", userIdOrUnitId).uniqueResult();
				unitCode=usr.getUnitCode();
			}else {
				unitCode=userIdOrUnitId;
			}
			if(unitCode!=null) {
				sql = " select distinct unit_code from maint_entity where unit_code in (" + 
						"		select distinct unit_code from maint_entity" + 
						"		join ( " + 
						"		    select A.unit_code Aid," + 
						"		    B.unit_code Bid," + 
						"		    C.unit_code Cid," + 
						"		    D.unit_code Did," + 
						"		    E.unit_code Eid," + 
						"		    F.unit_code Fid," + 
						"		    G.unit_code Gid," + 
						"		    H.unit_code Hid " + 
						"		    FROM maint_entity A" + 
						"		    left join maint_entity B on B.parent_unit_code=A.unit_code and B.legal_entity_code=A.legal_entity_code" + 
						"		    left join maint_entity C on C.parent_unit_code=B.unit_code and C.legal_entity_code=B.legal_entity_code" + 
						"		    left join maint_entity D on D.parent_unit_code=C.unit_code and D.legal_entity_code=C.legal_entity_code" + 
						"		    left join maint_entity E on E.parent_unit_code=D.unit_code and E.legal_entity_code=D.legal_entity_code" + 
						"		    left join maint_entity F on F.parent_unit_code=E.unit_code and F.legal_entity_code=E.legal_entity_code" + 
						"		    left join maint_entity G on G.parent_unit_code=F.unit_code and G.legal_entity_code=F.legal_entity_code" + 
						"		    left join maint_entity H on H.parent_unit_code=G.unit_code and H.legal_entity_code=G.legal_entity_code" + 
						"		    Where A.unit_code='"+unitCode+"' and A.legal_entity_code=" +legalEntityCode+  " AND A.status='A' AND A.entity_status='A' "+
						"		) X" + 
						"		where unit_code in (Aid,Bid,Cid,Did,Eid,Fid,Gid,Hid) AND status='A' AND entity_status='A' ) ";
				list = session.createSQLQuery(sql).list(); 
			}
			
		}catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace(); 
		}
		logger.info("getSubBranchesGyUserIdOrUnitId .. list<String>::"+ list.size()+" ::"+list);
		return list;
	}
	
	//Use the above method instead of this method which takes lots of iteration for getting the list.
	public List<MaintEntity> getSubBranchesGyUserIdOrUnitId(Integer legalEntityCode,String role,String type,String userIdOrUnitId) {

		List<MaintEntity> list=null;
		Session session=getSession();
		String unitCode="";
		
		if(type.equals("user")) {
			String sql="SELECT u.unitCode as unitCode FROM User u WHERE u.userId=:userIdOrUnitId AND u.legalEntityCode=:legalEntityCode ";
			User usr= (User) session.createQuery(sql)
					.setResultTransformer(Transformers.aliasToBean(User.class))
					.setParameter("legalEntityCode", legalEntityCode)
					.setParameter("userIdOrUnitId", userIdOrUnitId).uniqueResult();
			unitCode=usr.getUnitCode();
		}else {
			unitCode=userIdOrUnitId;
		}

		System.out.println(unitCode);

		if(unitCode!=null) {
			list=listOfSubBranchesGyUnitIds(session,legalEntityCode,unitCode,new ArrayList<MaintEntity>());
			if(list==null) {
				MaintEntity me=getUnitInfo(session,legalEntityCode,unitCode);
				if(me!=null && me.getLevelCode().equals("1")) {
					list = new ArrayList<MaintEntity>();
					list.add(me);
				}
			}else {	
				MaintEntity me=getUnitInfo(session,legalEntityCode,unitCode);
				if(me!=null && me.getLevelCode().equals("1")) {
					list.add(me);
				}
			}
			/*}else {
			       list=listOfSubBranchesGyUnitIds(session,legalEntityCode,userIdOrUnitId,list);
		          }*/

			if(list!=null  && list.size()>1) {
				Collections.sort(list);
			}
		}
		System.out.println("getSubBranchesGyUserIdOrUnitId .. list<MaintEntity>::"+ list.size()+" ::"+list);
		return list;
	}
	
	
		
public List<MaintEntity> listOfSubBranchesGyUnitIds(Session session,Integer legalEntityCode,String UnitId,List<MaintEntity> ls) {
		
	   // Session session=getSession();
	    List<MaintEntity> list=null;
	    
	    list = session.createQuery(" select m.unitCode as unitCode , m.unitName as unitName , m.levelCode as levelCode from MaintEntity m "
				+ " where m.legalEntityCode =:legalEntityCode and m.parentUnitCode = '"+UnitId+"' and entityStatus='"+BankAuditConstant.STATUS_ACTIVE+"' "
						+ " order by  m.unitName asc ")
				.setResultTransformer(Transformers.aliasToBean(MaintEntity.class))
				.setParameter("legalEntityCode", legalEntityCode)
				//.setParameter("UnitId", UnitId) 
				.list();
	    if(list!=null) {
	    	ls.addAll(list) ;
	    	for (MaintEntity maintEntity : list) {
	    		listOfSubBranchesGyUnitIds(session,legalEntityCode,maintEntity.getUnitCode(),ls);	    		
	    	}
	    }
	    
	  /*  if(ls!=null) 
	   Collections.sort(ls);*/
	    
	   return ls;
	}

@SuppressWarnings({ "unchecked", "deprecation" })
public MaintEntity getUnitInfo(Session session,Integer legalEntityCode,String UnitId) {
		
	   // Session session=getSession();
    
	MaintEntity   me =  (MaintEntity) session.createQuery(" select m.unitCode as unitCode , m.unitName as unitName , m.levelCode as levelCode from MaintEntity m "
				+ " where m.legalEntityCode =:legalEntityCode and m.unitCode ='"+UnitId+"' and entityStatus='"+BankAuditConstant.STATUS_ACTIVE+"' "
						+ " order by  m.unitName asc ")
				.setResultTransformer(Transformers.aliasToBean(MaintEntity.class))
				.setParameter("legalEntityCode", legalEntityCode)
				//.setParameter("UnitId", UnitId)
				.uniqueResult();
	   
	   return  me;
	}

@SuppressWarnings({ "unchecked", "deprecation" })
@Override
public List<MaintEntity> getImmediateChild(Integer legalEntityCode, String userId) {
	Session session=getSession();
	return session.createQuery(" select m.unitCode as unitCode , m.unitName as unitName , m.levelCode as levelCode from MaintEntity m "
			+ " where m.legalEntityCode =:legalEntityCode "
			+ " and m.parentUnitCode =(select unitCode from User where legalEntityCode=m.legalEntityCode and userId =:userId ) "
			+ " and m.entityStatus='"+BankAuditConstant.STATUS_ACTIVE+"' "
					+ " order by  m.unitName asc ")
			.setResultTransformer(Transformers.aliasToBean(MaintEntity.class))
			.setParameter("legalEntityCode", legalEntityCode)
			.setParameter("userId", userId).list();
}

	@SuppressWarnings({ "unchecked", "deprecation" })
	@Override
	public List<MaintEntity> getMaintEntityByLEAndUser(Integer legalEntityCode,String role,List<String> unitLst) {
		Session session=getSession();
		logger.info("Inside getMaintEntityByLEAndUser .. "+ legalEntityCode + " role ..");
		logger.info(" unitLst.. "+ unitLst);
		return session.createQuery(" from MaintEntity m "
				+ " where m.legalEntityCode =:legalEntityCode "
				+ " and m.unitCode in (:unitLst) "
				+ " and m.entityStatus='"+BankAuditConstant.STATUS_ACTIVE+"' "
						+ " order by  m.unitName asc ")
				//.setResultTransformer(Transformers.aliasToBean(MaintEntity.class))
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("unitLst", unitLst).list();
	}
	
	public Map<String, String> getRiskManagerDetails(Integer legalEntityCode, String userId, String role, String unitCode){
		Session session = getSession();
		session.setDefaultReadOnly(true);
		
		logger.info("Inside getRiskManagerDetails .... "+ legalEntityCode +" " + userId+" "+ role+" "+ unitCode);
		Map<String, String> riskManagerMap =new HashMap();
		StringBuilder queryString2=new StringBuilder( " select concat(u.first_name,' ', u.last_name),u.email_id, u.mobile1,u.landline1 " 
				+ " from user u, maint_entity e where u.legal_entity_code=e.legal_entity_code and e.unit_code='HO' " 
				+ "	AND u.user_id= e.contact_person AND e.legal_entity_code=:legalEntityCode " 
				); 
		
		Query query2lst=session.createSQLQuery(queryString2.toString())
				.setParameter("legalEntityCode", legalEntityCode);
				//.setParameter("userId", userId).setParameter("businessDate", businessDate);
		
		Object obj = query2lst.uniqueResult();
		try{
			logger.info("obj .. "+obj);
			if(obj != null  && ((Object[])obj).length > 0)
			{
				logger.info("obj .. "+((Object[])obj)[0]);
				riskManagerMap.put("NAME",((Object[])obj)[0]+"");
				riskManagerMap.put("EMAIL",((Object[])obj)[1]+"");
				riskManagerMap.put("MOBILE",((Object[])obj)[2]+"");
				riskManagerMap.put("LANDLINE",((Object[])obj)[3]+"");
			}logger.info("riskManagerMap .... "+ riskManagerMap);
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		return riskManagerMap;
	}
	
	@Override
	public List<MaintEntity> getEntityByLevelAndNotInGrpEntityMapping (Integer legalEntityCode, String levelCode, String status) {
		logger.info("Inside getEntityByLevelAndNotInGrpEntityMapping .. "+legalEntityCode +" levelCode.."+levelCode +" status.."+status );
		Session session=getSession();
		return session.createQuery("from MaintEntity e"
					+ " where  e.legalEntityCode =:legalEntityCode AND e.levelCode=:levelCode "
					+ " AND e.status ='A' AND e.entityStatus='A'  "
					+ " AND e.unitCode not in (select id from MaintEntityAuditSubgroupMapping m where m.legalEntityCode=e.legalEntityCode and m.mappingType='E' ) "
					+ " AND e.unitCode not in (select id from MaintEntityAuditSubgroupMappingWrk m where m.legalEntityCode=e.legalEntityCode and m.mappingType='E' ) "
					+ " ORDER BY e.unitName ")
				.setParameter("legalEntityCode", legalEntityCode).setParameter("levelCode", levelCode).list();
	}

	private Map<String, String> Hashmap() {
		// TODO Auto-generated method stub
		return null;
	}
	
	@Override
	public List<MaintEntity> getMaintEntityByLegalEntityCodeAndAuditTypeCode(Integer legalEntityCode, String auditTypeCode) {
		Session session=getSession();
		return session.createQuery("from MaintEntity m "
				+ " where m.legalEntityCode =:legalEntityCode "
				+ " and m.unitCode in (select id from MaintEntityAuditSubgroupMapping where mappingType ='E' and auditTypeCode=:auditTypeCode) "
				+ " and m.entityStatus='"+BankAuditConstant.STATUS_ACTIVE+"' "
						+ " order by  m.unitName asc ")
				//.setResultTransformer(Transformers.aliasToBean(MaintEntity.class))
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode).list();
	}
	
	@Override
	public MaintEntity getUnitDetails(int legalEntityCode, String unitId) {
		logger.info("Inside getUnitDetails .. "+legalEntityCode + " unitId.. "+unitId);
		Session session = getSession();
		session.setDefaultReadOnly(true);
		MaintEntity maintEntity=null;
		try {
			StringBuilder queryString=new StringBuilder("select e as maintEntity, "   // 0
					+ "   (select distinct stateName from MaintAddress a where e.legalEntityCode=a.legalEntityCode and e.state=a.stateCode and e.legalEntityCode=:legalEntityCode) as state "  // 1
					+ " from MaintEntity e " 
					+ " where e.legalEntityCode=:legalEntityCode and e.unitCode=:unitId and e.entityStatus='A' and e.status='A' ");
				
			Query query=(Query) session.createQuery(queryString.toString())				
					.setResultTransformer(new ResultTransformer() {
						@Override
						public Object transformTuple(Object[] tuple, String[] aliases) {
							MaintEntity maintEntity=null;
							if(tuple[0]!=null){
								maintEntity=(MaintEntity)tuple[0];
								if(tuple[1]!=null) maintEntity.setState(tuple[1].toString());
							}
							return maintEntity;
						}
				@Override
				public List transformList(List collection) {
					return collection;
				}
			}).setParameter("legalEntityCode",legalEntityCode).setParameter("unitId",unitId);
			maintEntity= (MaintEntity)query.uniqueResult();
			
			logger.info("maintEntity .. "+ maintEntity);
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}
		return maintEntity;
	}
	
	@Override
	public List<MaintEntity> getUnitCodes(Integer legalEntityCode, String userId, String role) {
		
		Session session=getSession();
		MaintEntity maintEntity=null;
		return session.createQuery(" from MaintEntity e " 
					+ " where e.legalEntityCode =:legalEntityCode "
					+ " and e.status ='A' AND e.entityStatus='A' ")
			.setParameter("legalEntityCode", legalEntityCode).list();
	}
	
	@Override
	public List<MaintEntity> getEntityForPlanLevel(Integer legalEntityCode, String userId, String levelCode) {
		logger.info("Inside getEntityForPlanLevel .. "+legalEntityCode +" levelCode.."+levelCode +" userId.."+userId );
		Session session=getSession();
		List<MaintEntity> entityLst=null;
		
		String levelCodeUser=null; String unitCode=null;
		Query qryUser=session.createSQLQuery( "SELECT u.unit_code, e.level_code FROM user u, maint_entity e " 
				+ " WHERE u.unit_code=e.unit_code AND u.legal_entity_code=e.legal_entity_code " 
				+ "	AND u.user_id=:userId AND u.legal_entity_code=:legalEntityCode " )				
				.setParameter("legalEntityCode", legalEntityCode).setParameter("userId", userId);
		
		Object obj = qryUser.uniqueResult();
		try{
			if(obj != null  && ((Object[])obj).length > 0)
			{
				logger.info("obj .. "+((Object[])obj)[0]);
				unitCode=((Object[])obj)[0].toString();
				levelCodeUser=((Object[])obj)[1].toString();				
			}logger.info("unitCode.."+unitCode+" levelCodeUser.."+levelCodeUser);
			
			String param1="";
			StringBuilder query2=new StringBuilder("");
			if(Integer.parseInt(levelCode) == Integer.parseInt(levelCodeUser)) {
				param1=unitCode;
				query2.append(" from MaintEntity e where e.legalEntityCode=:legalEntityCode AND e.unitCode=:param1 ");
			}else {
				query2.append(" from MaintEntity e where e.legalEntityCode=:legalEntityCode AND e.levelCode=:param1 ");
				param1=levelCode;
			}
			
			entityLst=session.createQuery(query2.toString())
					.setParameter("legalEntityCode", legalEntityCode).setParameter("param1", param1).list();
			
		}catch(Exception e) {
			e.printStackTrace(System.out);
		}return entityLst;
		
	}
	@Override
	public MaintEntity getUnitDetailsByUnitId(int legalEntityCode, String auditId) {
		logger.info("Inside getUnitDetailsByUnitId .. "+legalEntityCode + " auditId.. "+auditId);
		Session session = getSession();
		session.setDefaultReadOnly(true);
		return (MaintEntity)session.createQuery("SELECT m FROM MaintEntity m, AuditSchedule sch "
				+ " WHERE m.legalEntityCode=sch.legalEntityCode AND m.unitCode=sch.unitId "
				+ " AND  m.legalEntityCode =:legalEntityCode AND sch.auditId =:auditId ")
				.setParameter("legalEntityCode", legalEntityCode).setParameter("auditId", auditId).uniqueResult();		
	}
	
	@Override
	public void saveMaintEntity(Integer legalEntityCode, String userId, Integer uploadDocId) {
		try {

			Session session = getSession();

			/*StringBuilder createTempMaintEntity = new StringBuilder("CREATE TEMPORARY TABLE temp_maint_entity ("
					+ "legal_entity_code INT NOT NULL,"
					+ "level_code INT NOT NULL,"
					+ "unit_code VARCHAR(20) NOT NULL UNIQUE,"
					+ "parent_unit_code VARCHAR(20),"
					+ "unit_name VARCHAR(100) NOT NULL,"
					+ "address1 VARCHAR(255) NOT NULL,"
					+ "address2 VARCHAR(100),"
					+ "address3 VARCHAR(100),"
					+ "city_name VARCHAR(50) NOT NULL,"
					+ "city_code VARCHAR(10),"
					+ "pincode INT NOT NULL,"
					+ "district_name VARCHAR(50) NOT NULL,"
					+ "district_code VARCHAR(10),"
					+ "state_name VARCHAR(50) NOT NULL,"
					+ "state_code VARCHAR(10),"
					+ "country_name VARCHAR(50) NOT NULL,"
					+ "country_code VARCHAR(10),"
					+ "contact_person VARCHAR(100),"
					+ "landline1 VARCHAR(10) NOT NULL,"
					+ "landline2 VARCHAR(10),"
					+ "mobile1 VARCHAR(10) NOT NULL,"
					+ "mobile2 VARCHAR(10),"
					+ "mailid VARCHAR(30) CHECK (mailid LIKE '%@%.%'),"
					+ "u_criticality VARCHAR(20),"
					+ "unit_type_name VARCHAR(50),"
					+ "unit_type_ofc VARCHAR(10),"
					+ "classification_name VARCHAR(20),"
					+ "classification_code VARCHAR(10),"
					+ "weekly_holiday_name VARCHAR(25),"
					+ "weekly_holiday_code VARCHAR(10),"
					+ "fortnightly_holiday_name VARCHAR(25),"
					+ "fortnightly_holiday_code VARCHAR(10),"
					+ "unit_open_date DATE NOT NULL,"
					+ "unit_head_user VARCHAR(20),"
					+ "unit_head_inception_date DATE,"
					+ "type_of_entity_name VARCHAR(10),"
					+ "type_of_entity_code VARCHAR(10),"
					+ "entity_status VARCHAR(20),"
					+ "entity_status_code VARCHAR(10),"
					+ "auth_rej_remarks VARCHAR(255),"
					+ "status VARCHAR(20) DEFAULT 'Authorised',"
					+ "status_code VARCHAR(10),"
					+ "maker_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,"
					+ "maker VARCHAR(50) DEFAULT 'SYSTEM',"
					+ "checker_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,"
					+ "checker VARCHAR(50) DEFAULT 'SYSTEM',"
					+ "businessdate_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,"
					+ "sysdate_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP"
					+ ")");*/

//			session.createSQLQuery(createTempMaintEntity.toString()).executeUpdate();
			System.out.println("insert data to temp_maint_entity table ");

			String insertDataSQL = "INSERT INTO temp_maint_entity ("// add documentId
					+ "uploaded_id, legal_entity_code, level_code, unit_code, parent_unit_code, unit_name, "
					+ "address1, address2, address3, city_name, pincode, "
					+ "district_name, state_name, country_name, "
					+ "contact_person, landline1, landline2, mobile1, "
					+ "mobile2, mailid, u_criticality, unit_type_name, "
					+ "classification_name, weekly_holiday_name, "
					+ "fortnightly_holiday_name, "
					+ "unit_open_date, unit_head_user, unit_head_inception_date, "
					+ "type_of_entity_name, entity_status, maker)"
					+ "SELECT '"+uploadDocId+"', "
					+ "legal_entity_code, level_code, unit_code, parent_unit_code, unit_name, "
					+ "address1, address2, address3, city, pincode, "
					+ "district, state, country, "
					+ "contact_person, landline1, landline2, mobile1, "
					+ "mobile2, mailid, u_criticality, unit_type, "
					+ "classification, weekly_holiday, "
					+ "fortnightly_holiday, "
					+ "unit_open_date, unit_head_user, unit_head_inception_date, "
					+ "type_of_entity, entity_status, '"+userId+"' "
					+ "FROM excel_maint_entity";

			session.createNativeQuery(insertDataSQL).executeUpdate();
			session.flush();
			System.out.println("Data inserted into the temporary table successfully.");

		} catch (Exception e) {
			e.printStackTrace(System.out);
		}
	}
	
	@Override
	public String getLicenseeCodeForUnitCode(Integer legalEntityCode, String unitCode){
		Session session = getSession(); 
		session.setDefaultReadOnly(true);
		String licenseeCode=null;
		logger.info("Inside getLicenseeCodeForUnitCode .. "+ unitCode);
		String qryStr = "SELECT licensee_code FROM maint_entity_licensee_code_mapping li "
				+ " WHERE li.legal_entity_code= :legalEntityCode AND li.unit_code = :unitCode ";
     
		licenseeCode=(String)session.createSQLQuery(qryStr).setParameter("legalEntityCode", legalEntityCode).setParameter("unitCode", unitCode).getSingleResult();
		logger.info("licenseeCode .."+licenseeCode);
		
		return licenseeCode;
		
	}
	


}