package com.fitbank.common.helper; import java.sql.Date; import java.util.HashMap; import java.util.Map; import org.hibernate.SQLQuery; import com.fitbank.common.ApplicationDates; import com.fitbank.common.BeanManager; import com.fitbank.common.Helper; import com.fitbank.common.exception.FitbankException; import com.fitbank.common.hb.UtilHB; import com.fitbank.common.logger.FitbankLogger; import com.fitbank.hb.persistence.fin.Tbalancegroupid; import com.fitbank.hb.persistence.fin.Tcategories; import com.fitbank.hb.persistence.gene.Tcurrencyid; import com.fitbank.hb.persistence.loc.Taccountingdatebranch; import com.fitbank.hb.persistence.loc.Tnotdateaccounting; import com.fitbank.hb.persistence.trans.Transaction; import com.fitbank.hb.persistence.trans.Transactionid; public class SqlHelper { private static final String CIA = "cia"; private static final String V_TIMESTAMP = "v_timestamp"; /** * Almacena un objeto FinancialGlobal con datos utiles para ejecutar una * transaccion financiera. */ private static final SqlHelper INSTANCE = new SqlHelper(); /** * Separador para construir el key de maps. */ private static String separator = "^"; /** * Sentencia que devuelve datos de una tsubsistematransacciones. */ private static final String HQL_TRANSACTION = "select tran from com.fitbank.hb.persistence.trans.Transaction tran " + " where tran.pk.cidioma = :language " + " and tran.pk.csubsistema = :subsystem " + " and tran.pk.ctransaccion = :transaction " + " and tran.pk.versiontransaccion = :version " + " and tran.pk.fhasta = :v_timestamp "; /** * Sentencia que devuelve datos de una tcategorias. */ private static final String HQL_CATEGORY = " from com.fitbank.hb.persistence.fin.Tcategories cat " + " where cat.pk.categoria = :category " + " and cat.pk.cgrupobalance = :balancegroup " + " and cat.pk.cpersona_compania = :company " + " and cat.pk.cidioma = :language " + " and cat.pk.fhasta = :v_timestamp "; /** * Sentencia que devuelve las fechas contables por sucursal. */ private static final String HQL_ACCOUNTING_DATE = "from com.fitbank.hb.persistence.loc.Taccountingdatebranch fcont " + " where fcont.pk.cpersona_compania = :cia " + " and fcont.pk.csucursal = :branch " + " and fcont.pk.fhasta = :v_timestamp "; /** * Sentencia que devuelve la anterior fecha contable . */ private static final String HQL_PREVIOUS_ACCOUNTING_DATE = " select max(fcont.fcontable) from com.fitbank.hb.persistence.loc.Tdateaccounting fcont " + " where fcont.pk.cpersona_compania = :cia " + " and fcont.pk.csucursal = :branch " + " and fcont.pk.fhasta = :v_timestamp " + " and fcont.fcontable < :accountingdate"; /** * Sentencia que entrega la proxima fecha contable. */ private static final String SQL_NEXT_VALIDATE_ACCOUNTING_DATE = "select max(fcontable) from (select fcontable " + "from tfechascontables where csucursal =:branch and cpersona_compania =:cia " + "and fcontable > :accountingdate and fhasta =:v_timestamp " + "order by fcontable asc) where rownum <= :days"; /** * Sentencia que devuelve datos de tmonedasid. */ private static final String HQL_CURRENCY = " from com.fitbank.hb.persistence.gene.Tcurrencyid c " + " where c.pk = :currecy "; /** * Sentencia que devuelve las fechas contables por sucursal. */ private static final String HQL_REPORT_DATE = "from com.fitbank.hb.persistence.loc.Tnotdateaccounting frep " + " where frep.pk.cpersona_compania = :cia and frep.pk.fhasta = :v_timestamp " + " and :accountingDate between frep.pk.frealdesde and frep.pk.frealhasta"; /** * Sentencia que devuelve la fecha real hasta para una fecha de la tabla * TFECHASNOCONTABLES. */ private static final String HQL_REPORT_DATE_HASTA = "select frep.pk.frealhasta " + " from com.fitbank.hb.persistence.loc.Tnotdateaccounting frep " + " where frep.pk.cpersona_compania = :cia and frep.pk.fhasta = :v_timestamp " + " and frep.fbalance = :accountingDate"; /** * Entrega una instancia de SqlHelper. * * @return SqlHelper * @throws Exception */ public static SqlHelper getInstance() { return INSTANCE; } /** * Almacena datos de Tmonedasid. */ private Map mTcurrencyid; /** * Almacena datos tsubsistematransaccionesid. */ private Map mTransactionid; /** * Sentencia que devuelve datos de una tsubsistematransaccionesid. */ private static final String HQL_TRANSACTIONID = "select tran from com.fitbank.hb.persistence.trans.Transactionid tran " + " where tran.pk.csubsistema = :subsystem " + " and tran.pk.ctransaccion = :transaction " + " and tran.pk.versiontransaccion = :version "; /** * Entrega la fecha contable para una sucursal. * * @return command * @throws Exception */ public Taccountingdatebranch getAccountingdate(final Integer pCompany, final Integer pBranch) throws Exception { final UtilHB utilHB = new UtilHB(HQL_ACCOUNTING_DATE); utilHB.setInteger(CIA, pCompany); utilHB.setInteger("branch", pBranch); utilHB.setTimestamp(V_TIMESTAMP, ApplicationDates.DEFAULT_EXPIRY_TIMESTAMP); utilHB.setReadonly(true); final Taccountingdatebranch accountingdate = (Taccountingdatebranch) utilHB .getObject(); if (accountingdate == null) { FitbankLogger.getLogger().debug( "FECHA CONTABLE NO DEFINIDA PARA LA SUCURSAL " + pBranch); throw new FitbankException("FIN000", "FECHA CONTABLE NO DEFINIDA PARA LA SUCURSAL {0}", pBranch); } return accountingdate; } /** * Entrega la fecha contable para una sucursal. * * @return command * @throws Exception */ public Tnotdateaccounting getTnotdateaccounting(final Integer pCompany, final Date accountingDate) { final UtilHB utilHB = new UtilHB(HQL_REPORT_DATE); utilHB.setInteger(CIA, pCompany); utilHB.setDate("accountingDate", accountingDate); utilHB.setTimestamp(V_TIMESTAMP, ApplicationDates.DEFAULT_EXPIRY_TIMESTAMP); utilHB.setReadonly(true); return (Tnotdateaccounting) utilHB.getObject(); } /** * Entrega la fecha contable para una sucursal. * * @return command * @throws Exception */ public Date getReportDate(final Integer pCompany, final Date accountingDate) { final UtilHB utilHB = new UtilHB(HQL_REPORT_DATE_HASTA); utilHB.setInteger(CIA, pCompany); utilHB.setDate("accountingDate", accountingDate); utilHB.setTimestamp(V_TIMESTAMP, ApplicationDates.DEFAULT_EXPIRY_TIMESTAMP); utilHB.setReadonly(true); return (Date) utilHB.getObject(); } /** * Entrega la proxima fecha contable segun el dia ingresado. * * @param pCompany * Codigo de compania a obtener la fecha. * @param pBranch * Codigo de sucursal a obtener la fecha contable. * @param pAccountingdate * Fecha contable a obtener la proxima fecha contable. * @param pDay * Numero de dias que se agregan a la fecha contable. * @return * @throws Exception */ public Date getNextTrueAccountingDate(final Integer pCompany, final Integer pBranch, final Date pAccountingdate, final Long pDay) throws Exception { final SQLQuery sql = Helper .createSQLQuery(SQL_NEXT_VALIDATE_ACCOUNTING_DATE); sql.setInteger(CIA, pCompany); sql.setInteger("branch", pBranch); sql.setTimestamp(V_TIMESTAMP, ApplicationDates.DEFAULT_EXPIRY_TIMESTAMP); sql.setDate("accountingdate", pAccountingdate); sql.setLong("days", pDay + 1); final Date accountingdate = (Date) BeanManager.convertObject( sql.uniqueResult(), Date.class); if (accountingdate == null) { throw new FitbankException( "FIN000", "FECHA CONTABLE PROXIMA NO DEFINIDA PARA LA SUCURSAL {0} FCONTABLE {1}", pBranch, pAccountingdate); } return accountingdate; } /** * Entrega la fecha contable anterior. * * @param pCompany * Codigo de compania a obtener la fecha. * @param pBranch * Codigo de sucursal a obtener la fecha contable. * @param pAccountingdate * Fecha contable a obtener la proxima fecha contable. * @return Date * @throws Exception */ public Date getPreviousAccountingdate(final Integer pCompany, final Integer pBranch, final Date pAccountingdate) { final UtilHB utilHB = new UtilHB(HQL_PREVIOUS_ACCOUNTING_DATE); utilHB.setInteger(CIA, pCompany); utilHB.setInteger("branch", pBranch); utilHB.setDate("accountingdate", pAccountingdate); utilHB.setTimestamp(V_TIMESTAMP, ApplicationDates.DEFAULT_EXPIRY_TIMESTAMP); utilHB.setReadonly(true); final Date accountingdate = (Date) utilHB.getObject(); if (accountingdate == null) { throw new FitbankException( "FIN000", "FECHA CONTABLE ANTERIOR NO DEFINIDA PARA LA SUCURSAL {0} FCONTABLE {1}", pBranch, pAccountingdate); } return accountingdate; } /** * Entrega datos de tcategorias. * * @param pCategory * Codigo de categoria. * @param pBalancegroup * Codigo de grupo de balance. * @param pCompany * Codigo de compania. * @param pLanguage * Codigo de idioma del usuario. * @return Tcategories * @throws Exception */ public Tcategories getTcategories(final String pCategory, final String pBalancegroup, final Integer pCompany, final String pLanguage) throws Exception { final UtilHB utilHB = new UtilHB(HQL_CATEGORY); utilHB.setString("category", pCategory); utilHB.setString("balancegroup", pBalancegroup); utilHB.setInteger("company", pCompany); utilHB.setString("language", pLanguage); utilHB.setTimestamp(V_TIMESTAMP, ApplicationDates.DEFAULT_EXPIRY_TIMESTAMP); utilHB.setReadonly(true); return (Tcategories) utilHB.getObject(); } /** * Entrega datos de tmonedasid. * * @return Tcurrencyid * @throws Exception */ public Tcurrencyid getTcurrencyid(final String pCurrnecy) throws Exception { final UtilHB utilHB = new UtilHB(); Tcurrencyid tcurrencyid = null; if (this.mTcurrencyid == null) { this.mTcurrencyid = new HashMap(); } else { tcurrencyid = this.mTcurrencyid.get(pCurrnecy); } if (tcurrencyid == null) { utilHB.setSentence(HQL_CURRENCY); utilHB.setString("currecy", pCurrnecy); utilHB.setReadonly(true); tcurrencyid = (Tcurrencyid) utilHB.getObject(); if (tcurrencyid == null) { throw new FitbankException("GEN023", "MONEDA {0} NO DEFINIDA EN TMONEDASID", pCurrnecy); } synchronized (this.mTcurrencyid) { if (!this.mTcurrencyid.containsKey(pCurrnecy)) { this.mTcurrencyid.put(pCurrnecy, tcurrencyid); } } } return tcurrencyid; } /** * Entrega datos de una tsubsistematransaccionesid. * * @param pSubsystem * Codigo del subsistema la que pertenece la transaccion. * @param pTransaction * Codigo de version a la que pertenece la transaccion. * @param pVersion * Version de la transaccion. * @return Transactionid * @throws Exception */ public Transaction getTransaction(final String pSubsystem, final String pTransaction, final String pVersion, final String pLanguage) throws Exception { final UtilHB utilHB = new UtilHB(HQL_TRANSACTION); utilHB.setString("language", pLanguage); utilHB.setString("subsystem", pSubsystem); utilHB.setString("transaction", pTransaction); utilHB.setString("version", pVersion); utilHB.setTimestamp(V_TIMESTAMP, ApplicationDates.DEFAULT_EXPIRY_TIMESTAMP); utilHB.setReadonly(true); return (Transaction) utilHB.getObject(); } /** * Entrega datos de una tsubsistematransaccionesid. * * @param pSubsystem * Codigo del subsistema la que pertenece la transaccion. * @param pTransaction * Codigo de version a la que pertenece la transaccion. * @param pVersion * Version de la transaccion. * @return Transactionid * @throws Exception */ public Transactionid getTransactionid(final String pSubsystem, final String pTransaction, final String pVersion) throws Exception { final UtilHB utilHB = new UtilHB(); Transactionid transactionid = null; final String key = pSubsystem + separator + pTransaction + separator + pVersion; if (this.mTransactionid == null) { this.mTransactionid = new HashMap(); } else { transactionid = this.mTransactionid.get(key); } if (transactionid == null) { utilHB.setSentence(HQL_TRANSACTIONID); utilHB.setString("subsystem", pSubsystem); utilHB.setString("transaction", pTransaction); utilHB.setString("version", pVersion); utilHB.setReadonly(true); transactionid = (Transactionid) utilHB.getObject(); // if define cache if ((transactionid != null) && (transactionid.getUsacache() != null) && (transactionid.getUsacache().compareTo("1") == 0)) { synchronized (this.mTransactionid) { if (!this.mTransactionid.containsKey(key)) { this.mTransactionid.put(key, transactionid); } } } } return transactionid; } /** * Almacena datos de grupos de balance. */ private Map mbalancegroupid; /** * Sentencia que devuelve el grupo de balance. */ private static final String HQL_BALANCE_GROUP = "from com.fitbank.hb.persistence.fin.Tbalancegroupid bal " + " where bal.pk = :balancegroup "; /** * Entrega el grupo de balance. * * @return balgroup * @throws Exception */ public Tbalancegroupid getBalancegroup(final String pBalancegroup) throws Exception { final UtilHB utilHB = new UtilHB(); Tbalancegroupid balgroup = null; if (this.mbalancegroupid == null) { this.mbalancegroupid = new HashMap(); } else { balgroup = this.mbalancegroupid.get(pBalancegroup); } if (balgroup == null) { utilHB.setSentence(HQL_BALANCE_GROUP); utilHB.setString("balancegroup", pBalancegroup); utilHB.setReadonly(true); balgroup = (Tbalancegroupid) utilHB.getObject(); if (balgroup == null) { throw new FitbankException("FIN004", "GRUPO DE BALANCE {0} NO DEFINIDO", pBalancegroup); } // if define cache if (balgroup != null && balgroup.getUsacache() != null && balgroup.getUsacache().compareTo("1") == 0) { synchronized (this.mbalancegroupid) { if (!this.mbalancegroupid.containsKey(pBalancegroup)) { this.mbalancegroupid.put(pBalancegroup, balgroup); } } } } return balgroup; } }