package com.bankaudit.dao;

import java.lang.reflect.InvocationTargetException;
import java.util.List;

import org.apache.commons.beanutils.BeanUtils;
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.dto.DataTableResponse;
import com.bankaudit.dto.MaintActionItemDto;
import com.bankaudit.model.MaintActionItem;
import com.bankaudit.model.MaintActionItemWrk;
import com.bankaudit.service.MaintActionItemsService;
import com.bankaudit.util.BankAuditUtil;

@Repository("maintActionItemsDao")
public class MaintActionItemsDaoImpl  extends AbstractDao implements MaintActionItemsDao {
	
	static final Logger logger = Logger.getLogger(MaintActionItemsService.class);
	
	@Override
	public DataTableResponse getAllActionItemList(Integer legalEntityCode, String search,Integer orderColumn, String orderDirection, Integer page, Integer size){
		
		Session session = getSession();
		session.setDefaultReadOnly(true);
		StringBuilder strQury = new StringBuilder(" Select at.legal_entity_code, at.audit_type_code, at.audit_type_desc, "
				+ " actionItems.legal_entity_code as legalEntityCode, actionItems.audit_type_code as auditTypeCode,  actionItems.entity_type, "
				+ " (Select concat(g.value , '-' , g.description) from  general_parameter g where g.legal_entity_code = actionItems.legal_entity_code "
				+ " and g.key_1='STATUS' and g.key_2='STATUS' and g.value=actionItems.statusRow) as statusCol , "
				+ " (Select concat(g.value , '-' , g.description) from  general_parameter g where g.legal_entity_code = actionItems.legal_entity_code "
				+ " and g.key_1='STATUS' and g.key_2='STATUS' and g.value=actionItems.entitystatus) as entitystatus , "		
				+ " actionCount, informationCount, "
				//+ " actionItems.maker  "
				+ " (Select concat(u.user_id , '-' , ifnull(u.first_name,''), ' ',ifnull(u.middle_name,''),' ',ifnull(u.last_name,'') ) "
						+ " from  user u where u.legal_entity_code =actionItems.legal_entity_code and u.user_id=actionItems.maker) as maker "
				+ " from (" 				
				+ " Select  legal_entity_code, audit_type_code, entity_type, "
				+ " '"+BankAuditConstant.STATUS_AUTH+"' as statusRow, '"+BankAuditConstant.STATUS_AUTH+"' as entitystatus, " 					
				+ " SUM(CASE WHEN action_item_type = '"+BankAuditConstant.ACTION_TYPE_ACTION+"' THEN 1 ELSE 0 END ) as actionCount, "
				+ " SUM(CASE WHEN action_item_type = '"+BankAuditConstant.ACTION_TYPE_INFORMATION+"' THEN 1 ELSE 0 END ) as informationCount, "
				/*+ " (Select concat(u.user_id , '-' , ifnull(u.first_name,''), ' ',ifnull(u.middle_name,''),' ',ifnull(u.last_name,'') ) "
						+ " from  user u where u.legal_entity_code =actItem.legal_entity_code and u.user_id=actItem.maker) as maker "  */
				+ " maker "	
     			+ " from maint_action_item  actItem "
				+ " where actItem.legal_entity_code = :legalEntityCode"
				+ " and  entity_status <> '"+BankAuditConstant.STATUS_DEL+"' "
				+ " group by legal_entity_code, audit_type_code, entity_type, statusRow, entitystatus ,maker "
				+ " UNION ALL "
				+ " Select  legal_entity_code, audit_type_code, entity_type,"				
				+ " '"+BankAuditConstant.STATUS_UNAUTH+"' as statusRow, '"+BankAuditConstant.STATUS_UNAUTH+"' as entitystatus, " 				
				+ " SUM( CASE WHEN action_item_type = '"+BankAuditConstant.ACTION_TYPE_ACTION+"' THEN 1 ELSE 0 END ) as actionCount, "
				+ " SUM( CASE WHEN action_item_type = '"+BankAuditConstant.ACTION_TYPE_INFORMATION+"' THEN 1 ELSE 0 END ) as informationCount, "
				+ " maker "	
				/*+ " (Select concat(u.user_id , '-' , ifnull(u.first_name,''), ' ',ifnull(u.middle_name,''),' ',ifnull(u.last_name,'') ) "
						+ " from  user u where u.legal_entity_code =actItemWrk.legal_entity_code and u.user_id=actItemWrk.maker) as maker "  */
				+ " from maint_action_item_wrk  actItemWrk "
				+ " where actItemWrk.legal_entity_code = :legalEntityCode "
				+ " group by legal_entity_code, audit_type_code, entity_type, statusRow, entitystatus, maker "
				+ " ) As actionItems "
				+ " right join  maint_audit_type_desc at "
				+ " on at.audit_type_code = actionItems.audit_type_code "
				+ " and at.legal_entity_code = actionItems.legal_entity_code "
				+ " Where  at.status = '"+BankAuditConstant.STATUS_AUTH+"' "
				+ " AND at.entity_status = '"+BankAuditConstant.STATUS_ACTIVE+"' ");
		
		if(!BankAuditUtil.isEmptyString(search)){
			strQury.append(" AND ("
					+ " at.audit_type_code like  :search  "
					+ " OR at.audit_type_desc  like  :search  "
					//+ " OR actionItems.actionItems like :search "
					+ " OR statusRow like :search "
					+ " OR actionItems.maker IN (SELECT u.user_id FROM user u WHERE u.legal_entity_code = at.legal_entity_code and (u.user_id Like :search or u.first_name LIKE :search or u.last_name Like :search) ) "
					+ " OR statusRow IN (SELECT g.value FROM general_parameter g WHERE at.legal_entity_code =g.legal_entity_code and g.key_1='STATUS' and g.key_2='STATUS' and g.value=status and g.description LIKE :search) "
					+ " OR actionCount like :search  "
					+ " OR informationCount like :search  "
					+ " OR (actionCount+informationCount)  like :search ) ");
		}
		
		//strQury.append("GROUP BY ");
		
		String[] columns={
				" at.audit_type_code",
				" actionCount",
				" informationCount",
				" (actionCount+informationCount)",
				" actionItems.maker",
				" statusRow",
		};
		if(orderColumn!=null && orderColumn!=-1 && !BankAuditUtil.isEmptyString(orderDirection)){
			strQury.append(" ORDER BY  ").append(columns[orderColumn]).append(" ").append(orderDirection);
		}else {
			strQury.append(" ORDER BY FIELD(status, 'A','U','M'), at.audit_type_code asc  ");
		}
		DataTableResponse dataTableResponse=new DataTableResponse();
		try{
			Query qry = session.createNativeQuery(strQury.toString())
			.setResultTransformer(new ResultTransformer() {
				
				@Override
				public Object transformTuple(Object[] tuple, String[] aliases) {
					
					MaintActionItemDto maintActionItemDto = new MaintActionItemDto();
					maintActionItemDto.setLegalEntityCode(legalEntityCode);
					 
					if(tuple[1]!=null && tuple[2]!=null)
						maintActionItemDto.setAuditTypeCode(tuple[1].toString()+"-"+tuple[2].toString());				
					if(tuple[5]!=null)
						maintActionItemDto.setEntityType(tuple[5].toString());					 
					if(tuple[6]!=null)
						maintActionItemDto.setStatus(tuple[6].toString());
					if(tuple[7]!=null)
						maintActionItemDto.setEntityStatus(tuple[7].toString());
					if(tuple[8]!=null)
						maintActionItemDto.setActionItemsCount(Integer.parseInt(tuple[8].toString()));
					if(tuple[9]!=null)
						maintActionItemDto.setInformationItemsCount(Integer.parseInt(tuple[9].toString()));  
					if(tuple[10]!=null)
						maintActionItemDto.setMaker(tuple[10].toString());
					
					maintActionItemDto.setTotalCount((maintActionItemDto.getActionItemsCount()==null?0:maintActionItemDto.getActionItemsCount())
							+ (maintActionItemDto.getInformationItemsCount()==null?0:maintActionItemDto.getInformationItemsCount()));
					
					return maintActionItemDto;
				}
				
				@Override
				public List transformList(List collection) { 
					return collection;
				}
			})
			.setParameter("legalEntityCode", legalEntityCode);
			if(!BankAuditUtil.isEmptyString(search)){
				qry.setParameter("search", "%"+search+"%");
			}
			
			List<MaintActionItemDto> actionItemCount = qry.list(); 
			Long totalRecords=null;
			try {
				totalRecords = (long)actionItemCount.size();
			}catch(Exception e) {
				totalRecords = (long)0;
			}
			List<MaintActionItemDto> maintActionItemDtoList=qry.setFirstResult((page*size)).setMaxResults(size).list();
			
			dataTableResponse.setData(maintActionItemDtoList);
			dataTableResponse.setRecordsTotal(totalRecords);
			dataTableResponse.setRecordsFiltered(totalRecords);
		}catch (Exception e) { 
			logger.error(e.getStackTrace());
		}
		return dataTableResponse;
	}
	
	@Override
	public DataTableResponse getActionItemListByauditTypeCode(Integer legalEntityCode, String auditTypeCode, String status, String search, Integer orderColumn, String orderDirection, 
			Integer page, Integer size ){
		
		Session session = getSession();
		session.setDefaultReadOnly(true);
		DataTableResponse dataTableResponse=new DataTableResponse();
		StringBuilder strQry = new StringBuilder(" Select actItem as actionItem , "
				+ " (select concat(auditTypeCode , '-' , auditTypeDesc) from MaintAuditTypeDesc at where at.auditTypeCode=actItem.auditTypeCode and at.legalEntityCode= actItem.legalEntityCode ) as auditTypeCode, "   // 1
				+ " (Select concat(u.userId , '-' , ifnull(u.firstName,''), ' ',ifnull(u.middleName,''),' ',ifnull(u.lastName,'') ) from  User u where u.legalEntityCode =actItem.legalEntityCode and u.userId=actItem.maker) as maker, "  // 2
				+ " (select concat(actItem.status , '-' , gp.description) from GeneralParameter gp where gp.key1='STATUS' and gp.key2='STATUS' and gp.value=actItem.status and gp.legalEntityCode=actItem.legalEntityCode ) as status, " // 3
				+ " (select concat(actItem.entityStatus , '-' , gp.description) from GeneralParameter gp where gp.key1='ENTITY_STATUS' and gp.key2='ENTITY_STATUS' and gp.value=actItem.entityStatus and gp.legalEntityCode=actItem.legalEntityCode ) as entityStatus " // 4
   				+ " from ");		
		if(BankAuditConstant.STATUS_AUTH.equalsIgnoreCase(status)){
			strQry.append("  MaintActionItem as actItem "
					+ " Where actItem.legalEntityCode = :legalEntityCode "
					+ " AND actItem.entityStatus <> '"+BankAuditConstant.STATUS_DEL+"' ");
		}else{
			strQry.append("  MaintActionItemWrk as actItem "
					+ " Where actItem.legalEntityCode = :legalEntityCode ");
		} 		
		strQry.append("  "
				+ " and actItem.auditTypeCode = :auditTypeCode  ");
		
		if(!BankAuditUtil.isEmptyString(search)){
			strQry.append(" AND ("
					+ " auditTypeCode IN (SELECT a.auditTypeCode FROM MaintAuditTypeDesc a  WHERE a.legalEntityCode=actItem.legalEntityCode AND (a.auditTypeDesc LIKE :search OR a.auditTypeCode LIKE :search)) "
					+ " OR actionItem like :search "
					+ " OR actionItemType like :search "
					+ " OR status like :search "
					+ " OR maker IN (SELECT u.userId FROM User u WHERE u.legalEntityCode =actItem.legalEntityCode and (u.userId Like :search or u.firstName LIKE :search or u.lastName Like :search) ) "
					+ " OR status IN (SELECT g.value FROM GeneralParameter g WHERE actItem.legalEntityCode =g.legalEntityCode and g.key1='STATUS' and g.key2='STATUS' and g.value=actItem.status and g.description LIKE :search) "
					+ " OR authRejRemarks  like :search "
					+ " OR entityStatus IN (SELECT g.value FROM GeneralParameter g WHERE actItem.legalEntityCode =g.legalEntityCode and g.key1='ENTITY_STATUS' and g.key2='ENTITY_STATUS' and g.value=actItem.entityStatus and g.description LIKE :search) "
					+ "  )");
		}
		
		String[] columns={
				"audit_type_code",
				"actionItem",
				"actionItemType",
				"maker",
				"status",
				"authRejRemarks",
				"entityStatus"
		};
		if(orderColumn!=null && orderColumn!=-1 && !BankAuditUtil.isEmptyString(orderDirection)){
			strQry.append(" ORDER BY  ").append(columns[orderColumn]).append(" ").append(orderDirection);
		}else{
			strQry.append(" ORDER BY actionItemType DESC ");
		}
		try{
			Query qry =	session.createQuery(strQry.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode);
			if(!BankAuditUtil.isEmptyString(search)){
				qry.setParameter("search", "%"+search+"%");
			}
				qry.setResultTransformer(new ResultTransformer() {
					
					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						
						MaintActionItemWrk maintActionItemWrk = new MaintActionItemWrk();
						
						if(BankAuditConstant.STATUS_AUTH.equalsIgnoreCase(status)){
							MaintActionItem maintActionItem = new MaintActionItem();	
							if(tuple[0] != null){
									maintActionItem = (MaintActionItem) tuple[0];	
									if(tuple[1] != null) maintActionItem.setAuditTypeCode(tuple[1].toString());
									if(tuple[2] != null) maintActionItem.setMaker(tuple[2].toString());
									if(tuple[3] != null) maintActionItem.setStatus(tuple[3].toString());
									if(tuple[4] != null) maintActionItem.setEntityStatus(tuple[4].toString());
								try {
									BeanUtils.copyProperties(maintActionItemWrk, maintActionItem);
								} catch (IllegalAccessException|InvocationTargetException e) { 
									e.printStackTrace();
								} 
							}					 
						}else{ 	
							if(tuple[0] != null){
									maintActionItemWrk = (MaintActionItemWrk) tuple[0];
									if(tuple[1] != null) maintActionItemWrk.setAuditTypeCode(tuple[1].toString());
									if(tuple[2] != null) maintActionItemWrk.setMaker(tuple[2].toString());
									if(tuple[3] != null) maintActionItemWrk.setStatus(tuple[3].toString());
									if(tuple[4] != null) maintActionItemWrk.setEntityStatus(tuple[4].toString());
							}
						}
						return maintActionItemWrk;				
					}
					@Override
					public List transformList(List collection) { 
						return collection;
					}
				});
		
			List<MaintActionItemWrk> maintActionItemList = qry.list();	
			 
			Long totalRecords=null;
			try {
				totalRecords = (long)maintActionItemList.size();
			}catch(Exception e) {
				totalRecords = (long)0;
			}
			List<MaintActionItemWrk> maintActionItemWrkList=qry.setFirstResult((page*size)).setMaxResults(size).list();
			
			dataTableResponse.setData(maintActionItemWrkList);
			dataTableResponse.setRecordsTotal(totalRecords);
			dataTableResponse.setRecordsFiltered(totalRecords);
			 
		}catch (Exception e) {
			logger.error(e.getStackTrace());
		}
		return dataTableResponse;
	}
	
	@Override
	public List<MaintActionItemWrk> getAllActionItemListByauditTypeCode(Integer legalEntityCode, String auditTypeCode,  String status){
		Session session = getSession();
		session.setDefaultReadOnly(true); 
		StringBuilder strQry = new StringBuilder(" Select actItem as actionItem "				
				+ " from ");		
		if(BankAuditConstant.STATUS_AUTH.equalsIgnoreCase(status)){
			strQry.append("  MaintActionItem as actItem ");
			strQry.append("  Where  actItem.legalEntityCode = :legalEntityCode "
				+ " and actItem.auditTypeCode = :auditTypeCode "
				+ " and actItem.entityStatus = :entityStatus ");
		}else{
			strQry.append("  MaintActionItemWrk as actItem  ");
			strQry.append("  Where actItem.legalEntityCode = :legalEntityCode "
				+ " and actItem.auditTypeCode = :auditTypeCode  ");
		}  
		
		try{
			Query qry =	session.createQuery(strQry.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode);
			if(BankAuditConstant.STATUS_AUTH.equalsIgnoreCase(status)){
				qry.setParameter("entityStatus", BankAuditConstant.STATUS_ACTIVE);
			}
			qry.setResultTransformer(new ResultTransformer() {
					
					@Override
					public Object transformTuple(Object[] tuple, String[] aliases) {
						
						MaintActionItemWrk maintActionItemWrk = new MaintActionItemWrk();
						
						if(BankAuditConstant.STATUS_AUTH.equalsIgnoreCase(status)){
							MaintActionItem maintActionItem = new MaintActionItem();	
							if(tuple[0] != null){
									maintActionItem = (MaintActionItem) tuple[0];								 
								try {
									BeanUtils.copyProperties(maintActionItemWrk, maintActionItem);
								} catch (IllegalAccessException|InvocationTargetException e) { 
									e.printStackTrace();
								} 
							}					 
						}else{ 	
							if(tuple[0] != null){
									maintActionItemWrk = (MaintActionItemWrk) tuple[0];								 
							}
						}
						return maintActionItemWrk;				
					}
					@Override
					public List transformList(List collection) { 
						return collection;
					}
				});
		
			List<MaintActionItemWrk> maintActionItemList = qry.list();	
			return maintActionItemList;
			 
		}catch (Exception e) {
			logger.error(e.getStackTrace());
		}
		return null;
	}
	
	@Override
	public void copyDataFromMasterToWrk(Integer legalEntityCode, String auditTypeCode, String userId){		
		Session session = getSession();
		String qry = " INSERT INTO maint_action_item_wrk("
					+ " master_id, legal_entity_code, audit_type_code, entity_type, action_item, action_item_type, "
					+ " status, entity_status, auth_rej_remarks, maker, maker_timestamp, checker, "
					+ " checker_timestamp, businessdate_timestamp, sysdate_timestamp)  "
					+ " select  "
					+ " id, legal_entity_code, audit_type_code, entity_type, action_item, action_item_type, "
					+ " '"+BankAuditConstant.STATUS_UNAUTH+"', entity_status, auth_rej_remarks, '"+userId+"', maker_timestamp, checker, "
					+ " checker_timestamp, businessdate_timestamp, sysdate_timestamp "
					+ " from maint_action_item "
					+ " Where legal_entity_code = '"+legalEntityCode+"'"
					+ " and audit_type_code = '"+auditTypeCode+"' ";
		session.createNativeQuery(qry).executeUpdate();
	}
	
	@Override
	public void updateMaker(Integer legalEntityCode, String auditTypeCode, String userId, String status){		
		Session session = getSession();
		StringBuilder qry = new StringBuilder();
		if(BankAuditConstant.STATUS_AUTH.equalsIgnoreCase(status)){
			qry.append("UPDATE   maint_action_item ");
		}else{
			qry.append("UPDATE   maint_action_item_wrk ");
		}
		qry.append(" SET maker = '"+userId+"'  " 
					+ " Where legal_entity_code = '"+legalEntityCode+"'"
					+ " and audit_type_code = '"+auditTypeCode+"' ");
		session.createNativeQuery(qry.toString()).executeUpdate();
	}
 	 
	@Override
	public void deleteFromMaintActionItem(Integer legalEntityCode, String auditTypeCode,  Integer id){
		Session session = getSession();
		 
		String deleteQueryMaster = " UPDATE  MaintActionItem "
				+ " SET entityStatus = '"+BankAuditConstant.STATUS_DEL+"' "
					+ " Where legalEntityCode = '"+legalEntityCode+"' "
					+ " AND auditTypeCode = '"+auditTypeCode+"'" 
					+ " AND id = '"+id+"' " ;		
		session.createQuery(deleteQueryMaster).executeUpdate();	 
	}
	 
	@Override
	public void deleteFromMaintActionItemWrk(Integer legalEntityCode, String auditTypeCode,  Integer id){
		Session session = getSession();
		String deleteQuery = "Delete from  MaintActionItemWrk "
					+ " Where legalEntityCode = '"+legalEntityCode+"' "
					+ " AND auditTypeCode = '"+auditTypeCode+"' " 
					+ " AND id = "+id ;
		session.createQuery(deleteQuery).executeUpdate();
	}
	
	@Override
	public MaintActionItemWrk getModifiedDataFromWrk(Integer legalEntityCode, String auditTypeCode,  Integer masterId){
		Session session = getSession();
		session.setDefaultReadOnly(true); 
		StringBuilder strQry = new StringBuilder(" Select actItem as actionItem   from ");			 
			strQry.append("  MaintActionItemWrk as actItem "
					+ " Where actItem.legalEntityCode = :legalEntityCode "
					+ " and actItem.auditTypeCode = :auditTypeCode  ");			 
				strQry.append( " AND actItem.masterId = "+masterId);	 
			
		try{
			Query qry =	session.createQuery(strQry.toString())
				.setParameter("legalEntityCode", legalEntityCode)
				.setParameter("auditTypeCode", auditTypeCode);

			MaintActionItemWrk maintActionItemWrk = (MaintActionItemWrk) qry.uniqueResult();			  
			return maintActionItemWrk;			
		}catch (Exception e) {
			logger.error(e.getStackTrace());
		}
		return null;
	}
	
	/*@Override
	public void deleteActionItemById(Integer legalEntityCode, String auditTypeCode, Integer id, String status){
		Session session = getSession();
		
		if(BankAuditConstant.STATUS_AUTH.equalsIgnoreCase(status)){ 		 
			
			String deleteQueryMater = " UPDATE  MaintActionItemWrk "
					+ " SET status = '"+BankAuditConstant.STATUS_DEL+"' "
						+ " Where legalEntityCode = '"+legalEntityCode+"' "
						+ " AND auditTypeCode = '"+auditTypeCode+"'" 
						+ " AND id = '"+id+"' " ;		
			session.createQuery(deleteQueryMater).executeUpdate();	 
			
		}else if(!BankAuditConstant.STATUS_AUTH.equalsIgnoreCase(status)){
			String deleteQuery = "Delete from  MaintActionItemWrk "
					+ " Where legalEntityCode = '"+legalEntityCode+"' "
					+ " AND auditTypeCode = '"+auditTypeCode+"' "
					+ " AND id = '"+id+"' "
					+ " AND status = '"+BankAuditConstant.STATUS_UNAUTH+"' "
					+ " AND entityStatus = '"+BankAuditConstant.STATUS_UNAUTH+"' " ;
			session.createQuery(deleteQuery).executeUpdate();
		}
	}*/
	
}
