package com.fp.persistence.commondb.db;

import java.sql.Date;
import java.util.List;
import java.util.Map;

import javax.persistence.NoResultException;
import javax.persistence.Query;

import com.fp.common.helper.Constant;
import com.fp.common.properties.CommonProperties;
import com.fp.core.exception.CoreException;
import com.fp.persistence.commondb.HqlStatement;
import com.fp.persistence.commondb.PersistenceHelper;
import com.fp.persistence.commondb.cache.CacheManager;
import com.fp.persistence.commondb.exception.CommondbException;
import com.fp.persistence.pgeneral.date.TgeneAccountingDate;
import com.fp.persistence.pgeneral.date.TgeneAccountingDateBranch;
import com.fp.persistence.pgeneral.date.TgeneAccountingDateBranchKey;
import com.fp.persistence.pgeneral.gene.TgeneCatalogDetail;
import com.fp.persistence.pgeneral.gene.TgeneQueryProcess;
import com.fp.persistence.pgeneral.gene.TgeneTransactionQuery;
import com.fp.persistence.pgeneral.trans.TgeneTransaction;
import com.fp.persistence.pgeneral.trans.TgeneTransactionProcess;
import com.fp.persistence.pgeneral.trans.report.TgeneTransactionReports;


/**
 * Clase que se encarga de manejo de sentencias SQL o HQL con la base de datos, 
 * Existe ciertas tablas con informacion estatica, las cuales se cargan a memoria. <br>
 * Otras tablas se define para que se cargue a cache la informacion de ciertos regitros que esten marcagos con ManageCache.
 * @author Jorge Vaca
 * @version 2.1
 */
public class DataHelper {
	/**Almacena una instancia de DataHelper.*/
	private static DataHelper cache;
	/**Separador del key de maps cuando este se compone de varios atributos.*/
	private static String separator = "^";
	/**
	 * Entrega una instancia de DataHelper.
	 * @return	DataHelper
	 */
	public static DataHelper getInstance(){
		if(cache != null){
			return cache;
		}
		synchronized(DataHelper.class){
			if (cache == null) {
				cache = new DataHelper();
			}		
		}
		return cache;
	}
		
	
	/**
	 * Entrega un objeto con los datos de TgeneAccountingDateBranch. Que contiene la fecha contable de una sucursal.
	 * @param pComany	Codigo de compania a obtener la fecha contable.
	 * @param pBranch	Codigo de sucursal a obtener la fecha contable. 
	 * @return	TgeneAccountingDateBranch
	 * @throws CoreException
	 * @throws Exception
	 */
	public TgeneAccountingDateBranch getTgeneAccountingDateBranch(Integer pComany, Integer pBranch) throws CommondbException,Exception {
		TgeneAccountingDateBranchKey key = new TgeneAccountingDateBranchKey();
		key.setBranchcode(pBranch);
		key.setCompanycode(pComany);
		TgeneAccountingDateBranch obj = null;
		try {
			obj = TgeneAccountingDateBranch.find(PersistenceHelper.getEntityManager(), key);	
		} catch (NoResultException e) {
			throw new CommondbException("COMMONDB-00013","FECHA CONTABLE NO DEFINIDA PARA LA COMPANIA {0}, SUCURSAL {1}",pComany,pBranch);
		}
		return obj;
	}
	
	
	/**Sentencia que devuelve la definicion de una transaccion..*/
	private static final String HQL_TRANSACTION =
	"from TgeneTransaction tt  " +
	" where tt.pk.transactionmodule = :transactionmodule " +
	" and tt.pk.transactioncode = :transactioncode "+
	" and tt.pk.transactionversion = :transactionversion ";
	
	/**
	 * Entrega la definicion de una transaccion definida en tgenetransaction.
	 * @param pTransactionModule	Codigo del modulo al que pertenece la transaccion. 
	 * @param pTransactionCode		Codigo de transaccion.
	 * @param pTransactionVersion	Version de la transaccion. 
	 * @return	TgeneTransaction
	 * @throws Exception
	 */
	public TgeneTransaction getTgeneTransaction(String pTransactionModule,Integer pTransactionCode,Integer pTransactionVersion) throws Exception {
		TgeneTransaction tgeneTransaction = null;
		String key = pTransactionModule+separator+pTransactionCode+separator+pTransactionVersion;
		
        CacheManager cm = CacheManager.getInstance();
        tgeneTransaction = (TgeneTransaction) cm.getData("TgeneTransaction", key);     
        		
		if(tgeneTransaction != null){
			return tgeneTransaction;
		}
		Query qry = PersistenceHelper.getEntityManager().createQuery(HQL_TRANSACTION);
		qry.setParameter("transactionmodule", pTransactionModule);
		qry.setParameter("transactioncode", pTransactionCode);
		qry.setParameter("transactionversion", pTransactionVersion);
		try {
			tgeneTransaction = (TgeneTransaction)qry.getSingleResult();	
			PersistenceHelper.getEntityManager().detach(tgeneTransaction);
		} catch (NoResultException e) {
			//no hacer nada
		}
		
		//if manage cache
		if (tgeneTransaction != null && Constant.ifYes(tgeneTransaction.getManagecache()) ) {
			Map<String, Object> m = cm.getMapDefinition("TgeneTransaction");
			m.put(key, tgeneTransaction);
            cm.putData("TgeneTransaction", (Object) m);		
            
		}
		return tgeneTransaction;
	}
	
	/**Sentencia que devuelve la definicion de procesos de negocio a ejecutar para una transaccion..*/
	private static final String HQL_TRANSACTION_RULE =
	"from TgeneTransactionProcess ttp  " +
	" where ttp.pk.transactionmodule = :transactionmodule " +
	" and ttp.pk.transactioncode = :transactioncode "+
	" and ttp.pk.transactionversion = :transactionversion "+
	" and ttp.status = 'A' "+
	" order by ttp.executionorder ";
	

	/**
	 * Entrega la definicion de procesos de negocio de una transaccion definida.
	 * @param pTransactionModule	Codigo del modulo al que pertenece la transaccion. 
	 * @param pTransactionCode		Codigo de transaccion.
	 * @param pTransactionVersion	Version de la transaccion. 
	 * @return	List<TgeneTransactionProcess>
	 * @throws Exception
	 */
	@SuppressWarnings(Constant.VUNCHECKED)
	public List<TgeneTransactionProcess> getTgeneTransactionProcess(String pTransactionModule,Integer pTransactionCode,Integer pTransactionVersion) throws Exception {
		List<TgeneTransactionProcess> lTgeneTransactionProcess = null;
		String key = pTransactionModule+separator+pTransactionCode+separator+pTransactionVersion;
		        
		CacheManager cm = CacheManager.getInstance();
		lTgeneTransactionProcess = (List<TgeneTransactionProcess>) cm.getData("TgeneTransactionProcess", key);
        
		if(lTgeneTransactionProcess != null){
			return lTgeneTransactionProcess;
		}
		HqlStatement hql = new HqlStatement(HQL_TRANSACTION_RULE);
		hql.setString(Constant.VTRANSACTIONMODULE, pTransactionModule);
		hql.setInteger(Constant.VTRANSACTIONCODE, pTransactionCode);
		hql.setInteger(Constant.VTRANSACTIONVERSION, pTransactionVersion);
		hql.setReadonly(true);
		
		lTgeneTransactionProcess = hql.execute().getResultList();
		
		if(lTgeneTransactionProcess == null || lTgeneTransactionProcess.size() == 0){
			return lTgeneTransactionProcess;
		}
		/*if manage cache  COMMONDB-0006*/
		TgeneTransactionProcess obj = lTgeneTransactionProcess.get(0);
		if (obj != null && Constant.ifYes(obj.getManagecache()) ) {
			Map<String, Object> m = cm.getMapDefinition("TgeneTransactionProcess");
			m.put(key, lTgeneTransactionProcess);
            cm.putData("TgeneTransactionProcess", (Object) m);		            
		}		
		return lTgeneTransactionProcess;
	}
	
	/**Sentencia que devuelve la definicion de procesos a ejecutar el query  para una transaccion..*/
	private static final String HQL_TRANSACTION_QUERY =
	"from TgeneTransactionQuery ttq  " +
	" where ttq.pk.transactionmodule = :transactionmodule " +
	" and ttq.pk.transactioncode = :transactioncode "+
	" and ttq.pk.transactionversion = :transactionversion "+
	" and ttq.status = 'A' "+
	" order by ttq.executionorder ";

	/**
	 * Entrega la definicion de procesos que complementan datos en la consulta para una transaccion.
	 * @param pTransactionModule	Codigo del modulo al que pertenece la transaccion. 
	 * @param pTransactionCode		Codigo de transaccion.
	 * @param pTransactionVersion	Version de la transaccion. 
	 * @return	List<TgeneTransactionQuery>
	 * @throws Exception
	 */
	@SuppressWarnings(Constant.VUNCHECKED)
	public List<TgeneTransactionQuery> getTgeneTransactionQuery(String pTransactionModule,Integer pTransactionCode,Integer pTransactionVersion) throws Exception {
		List<TgeneTransactionQuery> lTgeneTransactionQuery = null;

		HqlStatement hql = new HqlStatement(HQL_TRANSACTION_QUERY);
		hql.setString(Constant.VTRANSACTIONMODULE, pTransactionModule);
		hql.setInteger(Constant.VTRANSACTIONCODE, pTransactionCode);
		hql.setInteger(Constant.VTRANSACTIONVERSION, pTransactionVersion);
		hql.setReadonly(true);
		lTgeneTransactionQuery = hql.execute().getResultList();
		return lTgeneTransactionQuery;
	}
	
	/**Sentencia que devuelve la definicion de procesos a ejecutar en la generacion de reportes..*/
	private static final String HQL_TRANSACTION_REPORT =
	"from TgeneTransactionReports ttr  " +
	" where ttr.pk.transactionmodule = :transactionmodule " +
	" and ttr.pk.transactioncode = :transactioncode "+
	" and ttr.pk.transactionversion = :transactionversion "+
	" and ttr.status = 'A' "+
	" order by ttr.executionorder ";

	/**
	 * Entrega la definicion de procesos que se encargan de generar datos a utilizar en la generacion de reportes.
	 * @param pTransactionModule	Codigo del modulo al que pertenece la transaccion. 
	 * @param pTransactionCode		Codigo de transaccion.
	 * @param pTransactionVersion	Version de la transaccion. 
	 * @return	List<TgeneTransactionReports>
	 * @throws Exception
	 */
	@SuppressWarnings(Constant.VUNCHECKED)
	public List<TgeneTransactionReports> getTgeneTransactionReports(String pTransactionModule,Integer pTransactionCode,Integer pTransactionVersion) throws Exception {
		List<TgeneTransactionReports> lReports = null;
		HqlStatement hql = new HqlStatement(HQL_TRANSACTION_REPORT);
		hql.setString(Constant.VTRANSACTIONMODULE, pTransactionModule);
		hql.setInteger(Constant.VTRANSACTIONCODE, pTransactionCode);
		hql.setInteger(Constant.VTRANSACTIONVERSION, pTransactionVersion);
		hql.setReadonly(true);		
		lReports = hql.execute().getResultList();	
		return lReports;
	}
	
	/**Sentencia que devuelve la definicion de procesos a ejecutar para un codigo de consulta.*/
	private static final String HQL_QUERY_PROCESS =
	"from TgeneQueryProcess tqp  " +
	" where tqp.pk.querycode = :querycode " +
	" and tqp.status = 'A' "+
	" order by executionorder ";

	/**
	 * Entrega la definicion de procesos que complementan datos en la consulta para un codigo de consulta.
	 * @param pQueryCode  Codigo del consulta con el cual se completa datos. 
	 * @return	List<TgeneQueryProcess>
	 * @throws Exception
	 */
	@SuppressWarnings(Constant.VUNCHECKED)
	public List<TgeneQueryProcess> getTgeneQueryProcess(String pQueryCode) throws Exception {
		List<TgeneQueryProcess> lTgeneQueryProcess = null;
		HqlStatement hql = new HqlStatement(HQL_QUERY_PROCESS);
		hql.setString("querycode", pQueryCode);
		hql.setReadonly(true);		
		lTgeneQueryProcess = hql.execute().getResultList();	
		return lTgeneQueryProcess;
	}
	
	/**
	 * Metodo que entrega la fecha contable de liberacion de fondos de cheques locales.
	 * @param pBranch  Codigo de sucursal.
	 * @param pCompany  Codigo de compania.
	 * @param pAccountingDate  Fecha contable en la cual se ejecuta la transaccion.
	 * @return Date
	 * @throws Exception
	 */
	public Date getHoldDate(Integer pBranch,Integer pCompany,Date pAccountingDate) throws Exception {
		Integer days = CommonProperties.getInstance().getIntValue("localhold");
		List<TgeneAccountingDate> lDates = this.getTgeneAccountingDate(pBranch, pCompany, pAccountingDate);
		Date olddate = null;
		Date d = null;
		int i = 0;
		for (TgeneAccountingDate obj : lDates) {			
			if(olddate == null || olddate.compareTo(obj.getAccountingdate()) != 0){
				i++;
			}
			olddate = obj.getAccountingdate();
			if(i >= days){
				d = obj.getAccountingdate();
				break;
			}
		}		
		if(d == null){
			throw new CommondbException("COMMONDB-00014", "FECHAS CONTABLES NO DEFINIDAS EN: {0} PARA LA COMPANIA {1}, SUCURSAL {2}",
					"TGENEACCOUNTINGDATE",pCompany,pBranch);
		}
		return d;
	}
	

	/** Sentencia que devuelve lista de fechas contables por sucursal.*/
	private static final String HQL_HOLD_ACCOUNTING_DATE_BRANCH =
	"from TgeneAccountingDate d  " +
	" where d.pk.branchcode = :branch " +
	" and d.pk.companycode = :company "+
	" and d.accountingdate > :accountingdate "+
	" and d.accountingdate < :accountingdate + :days ";
	
	/**
	 * Metodo que entrega una lista de TgeneAccountingDate, con el cual se obtiene los dias de retencion 
	 * de cheques locales.  
	 * @param pBranch  Codigo de sucursal.
	 * @param pCompany Codigo de compania.
	 * @param pAccountingDate  Fecha contable.
	 * @return  List<TgeneAccountingDate>
	 * @throws Exception
	 */
	@SuppressWarnings(Constant.VUNCHECKED)
	private List<TgeneAccountingDate> getTgeneAccountingDate(Integer pBranch,Integer pCompany,Date pAccountingDate) throws CommondbException,Exception {
		List<TgeneAccountingDate> lDates = null;
		HqlStatement hql = new HqlStatement(HQL_HOLD_ACCOUNTING_DATE_BRANCH);
		hql.setInteger("branch", pBranch);
		hql.setInteger("company", pCompany);
		hql.setDate("accountingdate", pAccountingDate);
		hql.setInteger("days", 15);
		hql.setReadonly(true);
		lDates = hql.execute().getResultList();
		if(lDates == null || lDates.size() == 0){
			throw new CommondbException("COMMONDB-00014", "FECHAS CONTABLES NO DEFINIDAS EN: {0} PARA LA COMPANIA {1}, SUCURSAL {2}",
					"TGENEACCOUNTINGDATE",pCompany,pBranch);			
		}
		return lDates;
	}
	
	/**Sentencia que devuelve un registro del detalle del catalog.*/
	private static final String HQL_CATALOG_DETAIL =
	"from TgeneCatalogDetail tcd  " +
	" where tcd.pk.catalog = :catalog " +
	" and tcd.pk.catalogcode = :catalogcode ";
	
	/**
	 * Metodo que entrega la definicion de detalel del catalogo.
	 * @param pCatalog  Codigo dentro del catalogo.
	 * @param pCatalogcode  Codigo de catalogo. 
	 * @return  TgeneCatalogDetail
	 * @throws Exception
	 */
	public TgeneCatalogDetail getTgeneCatalogDetail(String pCatalog,String pCatalogcode) throws Exception {
		TgeneCatalogDetail tgeneCatalogDetail = null;
		HqlStatement hql = new HqlStatement();
		hql.setSentence(HQL_CATALOG_DETAIL);
		hql.setString("catalog", pCatalog);
		hql.setString("catalogcode", pCatalogcode);
		tgeneCatalogDetail = (TgeneCatalogDetail)hql.getObject();
		return tgeneCatalogDetail;
	}
	
}