Example usage for org.hibernate SQLQuery setLong

List of usage examples for org.hibernate SQLQuery setLong

Introduction

In this page you can find the example usage for org.hibernate SQLQuery setLong.

Prototype

@Deprecated
@SuppressWarnings("unchecked")
default Query<R> setLong(int position, long val) 

Source Link

Document

Bind a positional long-valued parameter.

Usage

From source file:org.egov.egf.web.actions.brs.AutoReconcileHelper.java

License:Open Source License

private List<AutoReconcileBean> getStatmentsForProcessing(final String type) {
    final SQLQuery detailQuery = persistenceService.getSession()
            .createSQLQuery("select id,txDate,instrumentNo,debit,credit,CSLno  from " + TABLENAME
                    + " where accountId=:accountId  and type='" + type + "' and action='"
                    + BRS_ACTION_TO_BE_PROCESSED + "'");
    detailQuery.setLong("accountId", accountId);
    detailQuery.addScalar("id", LongType.INSTANCE).addScalar("txDate").addScalar("instrumentNo")
            .addScalar("debit").addScalar("credit").addScalar("CSLno")
            .setResultTransformer(Transformers.aliasToBean(AutoReconcileBean.class));
    final List<AutoReconcileBean> detailList = detailQuery.list();
    return detailList;
}

From source file:org.egov.egf.web.actions.brs.AutoReconcileHelper.java

License:Open Source License

private void findandUpdateDuplicates() {
    // for payment cheques instrumentNo,debit,accountId combination should be unique else mark it duplicate
    try {//from w ww .  j a  v a2 s.  c om
        String duplicates = "select instrumentNo,debit,accountId from " + TABLENAME
                + " where accountId=:accountId" + " and debit>0 and action='" + BRS_ACTION_TO_BE_PROCESSED
                + "'  group by  instrumentNo,debit,accountId having count(*)>1";
        final SQLQuery paymentDuplicateChequesQuery = persistenceService.getSession()
                .createSQLQuery(duplicates);
        paymentDuplicateChequesQuery.addScalar("instrumentNo").addScalar("debit")
                .addScalar("accountId", LongType.INSTANCE)
                .setResultTransformer(Transformers.aliasToBean(AutoReconcileBean.class));
        // paymentDuplicateChequesQuery.setParameter("accountId", Long.class);
        paymentDuplicateChequesQuery.setLong("accountId", accountId);
        final List<AutoReconcileBean> duplicatePaymentCheques = paymentDuplicateChequesQuery.list();

        final String backUpdateDuplicatePaymentquery = "update " + TABLENAME + " set action='"
                + BRS_ACTION_TO_BE_PROCESSED_MANUALLY + "'," + " errorMessage='"
                + BRS_MESSAGE_DUPPLICATE_IN_BANKSTATEMENT
                + "' where debit=:debit and accountid=:accountId and instrumentNo=:instrumentNo "
                + " and action='" + BRS_ACTION_TO_BE_PROCESSED + "'";

        final SQLQuery paymentDuplicateUpdate = persistenceService.getSession()
                .createSQLQuery(backUpdateDuplicatePaymentquery);
        for (final AutoReconcileBean bean : duplicatePaymentCheques) {

            paymentDuplicateUpdate.setLong("accountId", bean.getAccountId());
            paymentDuplicateUpdate.setBigDecimal("debit", bean.getDebit());
            paymentDuplicateUpdate.setString("instrumentNo", bean.getInstrumentNo());
            paymentDuplicateUpdate.executeUpdate();

        }
        // this portion is for receipts instrumentNo,credit,accountId combination should be unique else mark it duplicate
        duplicates = "select instrumentNo,credit,accountId from " + TABLENAME + " where accountid=:accountId"
                + " and  credit>0 and action='" + BRS_ACTION_TO_BE_PROCESSED
                + "' group by  instrumentNo,credit,accountId having count(*)>1";
        final SQLQuery receiptsDuplicateChequesQuery = persistenceService.getSession()
                .createSQLQuery(duplicates);
        receiptsDuplicateChequesQuery.addScalar("instrumentNo").addScalar("credit")
                .addScalar("accountId", LongType.INSTANCE)
                .setResultTransformer(Transformers.aliasToBean(AutoReconcileBean.class));
        receiptsDuplicateChequesQuery.setLong("accountId", accountId);
        final List<AutoReconcileBean> duplicateReceiptsCheques = receiptsDuplicateChequesQuery.list();

        final String backUpdateDuplicateReceiptsQuery = "update " + TABLENAME + " set action='"
                + BRS_ACTION_TO_BE_PROCESSED_MANUALLY + "'" + " ,errorMessage='"
                + BRS_MESSAGE_DUPPLICATE_IN_BANKSTATEMENT
                + "' where credit=:credit and accountid=:accountId and instrumentNo=:instrumentNo "
                + " and action='" + BRS_ACTION_TO_BE_PROCESSED + "'";
        final SQLQuery receiptDuplicateUpdate = persistenceService.getSession()
                .createSQLQuery(backUpdateDuplicateReceiptsQuery);

        for (final AutoReconcileBean bean : duplicateReceiptsCheques) {
            receiptDuplicateUpdate.setLong("accountId", bean.getAccountId());
            receiptDuplicateUpdate.setBigDecimal("credit", bean.getCredit());
            receiptDuplicateUpdate.setString("instrumentNo", bean.getInstrumentNo());
            receiptDuplicateUpdate.executeUpdate();
        }
    } catch (final HibernateException e) {
        throw new ApplicationRuntimeException("Failed while processing autoreconciliation ");
    }

}

From source file:org.egov.egf.web.actions.brs.ManualReconcileHelper.java

License:Open Source License

public Map<String, String> getUnReconciledDrCr(Long bankAccId, Date fromDate, Date toDate) {
    Map<String, String> unreconMap = new LinkedHashMap<String, String>();
    //String  ="decode(iv.voucherHeaderId,null,0,ih.instrumentAmount)";
    String instrumentsForBrsEntryTotal = "case when br.voucherHeaderId is null then ih.instrumentAmount else 0 end";
    //String instrumentsForOtherTotal="decode(br.voucherHeaderId,null,ih.instrumentAmount,0)";
    String voucherExcludeStatuses = getExcludeStatuses();

    String totalQuery = "SELECT (sum(CASE WHEN ih.ispaycheque='1' then ih.instrumentAmount else 0 end ))  AS \"brs_creditTotal\", "
            + " (sum(CASE WHEN ih.ispaycheque = '0' then  ih.instrumentAmount else 0 end)) AS \"brs_debitTotal\" "
            + " FROM egf_instrumentheader ih    WHERE   ih.bankAccountId =:bankAccountId "
            + " AND IH.INSTRUMENTDATE >= :fromDate" + " AND IH.INSTRUMENTDATE <= :toDate"
            + " AND  ( (ih.ispaycheque='0' and  ih.id_status=(select id from egw_status where moduletype='Instrument' "
            + " and description='Deposited'))or (ih.ispaycheque='1' and  ih.id_status=(select id from egw_status where "
            + " moduletype='Instrument'  and description='New'))) " + " and ih.instrumentnumber is not null";
    //see u might need to exclude brs entries here 

    String otherTotalQuery = " SELECT (sum(case when ih.ispaycheque='1' then ih.instrumentAmount else 0 end))  AS \"brs_creditTotalOthers\", "
            + " (sum(case when ih.ispaycheque= '0' then ih.instrumentAmount else 0 end))  AS \"brs_debitTotalOthers\" "
            + " FROM  egf_instrumentheader ih   WHERE   ih.bankAccountId =:bankAccountId"
            + " AND IH.transactiondate >= :fromDate" + " AND IH.transactiondate <= :toDate  "
            + " AND ( (ih.ispaycheque='0' and ih.id_status=(select id from egw_status where moduletype='Instrument'"
            + "  and description='Deposited'))or (ih.ispaycheque='1' and  ih.id_status=(select id from egw_status where"
            + " moduletype='Instrument'  and description='New'))) " + " AND ih.transactionnumber is not null";

    String brsEntryQuery = " SELECT (sum(case when ih.ispaycheque= '1' then " + instrumentsForBrsEntryTotal
            + " else 0 end ))  AS \"brs_creditTotalBrsEntry\", " + " (sum(case when ih.ispaycheque= '0' then "
            + instrumentsForBrsEntryTotal + " else 0 end))  AS \"brs_debitTotalBrsEntry\" "
            + " FROM egf_instrumentheader ih, bankentries br   WHERE   ih.bankAccountId = :bankAccountId"
            + " AND IH.transactiondate >= :fromDate  " + " AND IH.transactiondate <= :toDate "
            + " AND ( (ih.ispaycheque='0' and ih.id_status=(select id from egw_status where moduletype='Instrument' "
            + " and description='Deposited')) or (ih.ispaycheque='1' and  ih.id_status=(select id from egw_status where moduletype='Instrument'  and description='New'))) "
            + " AND br.instrumentHeaderid=ih.id and ih.transactionnumber is not null";

    if (LOGGER.isInfoEnabled())
        LOGGER.info("  query  for  total : " + totalQuery);

    String unReconciledDrCr = "";

    String creditTotal = null;/* ww w  . j a v  a2s  . co m*/
    String creditOthertotal = null;
    String debitTotal = null;
    String debitOtherTotal = null;
    String creditTotalBrsEntry = null;
    String debitTotalBrsEntry = null;

    try {
        SQLQuery totalSQLQuery = persistenceService.getSession().createSQLQuery(totalQuery);
        totalSQLQuery.setLong("bankAccountId", bankAccId);
        totalSQLQuery.setDate("fromDate", fromDate);
        totalSQLQuery.setDate("toDate", toDate);

        List list = totalSQLQuery.list();
        if (list.size() > 0) {
            if (LOGGER.isDebugEnabled())
                LOGGER.debug(list.get(0));
            Object[] my = (Object[]) list.get(0);
            creditTotal = my[0] != null ? my[0].toString() : null;
            debitTotal = my[1] != null ? my[1].toString() : null;
        }

        if (LOGGER.isInfoEnabled())
            LOGGER.info("  query  for other than cheque/DD: " + otherTotalQuery);
        totalSQLQuery = persistenceService.getSession().createSQLQuery(otherTotalQuery);
        totalSQLQuery.setLong("bankAccountId", bankAccId);
        totalSQLQuery.setDate("fromDate", fromDate);
        totalSQLQuery.setDate("toDate", toDate);
        list = totalSQLQuery.list();
        if (list.size() > 0) {
            if (LOGGER.isDebugEnabled())
                LOGGER.debug(list.get(0));
            Object[] my = (Object[]) list.get(0);
            creditOthertotal = my[0] != null ? my[0].toString() : null;
            debitOtherTotal = my[1] != null ? my[1].toString() : null;
        }
        if (LOGGER.isInfoEnabled())
            LOGGER.info("  query  for bankEntries: " + brsEntryQuery);

        totalSQLQuery = persistenceService.getSession().createSQLQuery(brsEntryQuery);
        totalSQLQuery.setLong("bankAccountId", bankAccId);
        totalSQLQuery.setDate("fromDate", fromDate);
        totalSQLQuery.setDate("toDate", toDate);
        list = totalSQLQuery.list();
        if (list.size() > 0) {
            if (LOGGER.isDebugEnabled())
                LOGGER.debug(list.get(0));
            Object[] my = (Object[]) list.get(0);
            creditTotalBrsEntry = my[0] != null ? my[0].toString() : null;
            debitTotalBrsEntry = my[1] != null ? my[1].toString() : null;
        }

        /* ReconcileBean reconBean=new ReconcileBean();
         reconBean.setCreditAmount(BigDecimal.valueOf(creditTotal));
         reconBean.setDebitAmount(debitTotal);
         */
        creditTotal = creditTotal == null ? "0" : creditTotal;
        debitTotal = debitTotal == null ? "0" : debitTotal;
        creditOthertotal = creditOthertotal == null ? "0" : creditOthertotal;
        debitOtherTotal = debitOtherTotal == null ? "0" : debitOtherTotal;
        debitTotalBrsEntry = debitTotalBrsEntry == null ? "0" : debitTotalBrsEntry;

        unreconMap.put("Cheque/DD/Cash Payments", creditTotal);
        unreconMap.put("Cheque/DD/Cash Receipts", debitTotal);
        unreconMap.put("RTGS Payments", creditOthertotal);
        unreconMap.put("Other Receipts", debitOtherTotal);
        unreconMap.put("BRS Entry", debitTotalBrsEntry);

        /*//unReconciledDrCr="Cheque/DD/Cash Payments:"+(creditTotal != null ? creditTotal : "0" )+",RTGS Payments:"+(creditOthertotal!= null ? creditOthertotal : "0")
        +",Cheque/DD/Cash Receipts:"+(debitTotal!= null ? debitTotal : "0") +",Other Receipts:"+( debitOtherTotal!= null ? debitOtherTotal : "0")+""+
        "/"+(creditTotalBrsEntry!= null ? creditTotalBrsEntry : "0") +",Net:"+( debitTotalBrsEntry!= null ? debitTotalBrsEntry : "0")+"";*/
    } catch (Exception e) {
        LOGGER.error("Exp in getUnReconciledDrCr" + e.getMessage());

    }
    return unreconMap;
}

From source file:org.egov.egf.web.actions.brs.ManualReconcileHelper.java

License:Open Source License

public List<ReconcileBean> getUnReconciledCheques(ReconcileBean reconBean) {
    List<ReconcileBean> list = new ArrayList<ReconcileBean>();
    String instrumentCondition = "";
    if (reconBean.getInstrumentNo() != null && !reconBean.getInstrumentNo().isEmpty()) {
        instrumentCondition = "and (ih.instrumentNumber='" + reconBean.getInstrumentNo()
                + "' or ih.transactionnumber='" + reconBean.getInstrumentNo() + "' )";
    }// w  ww.  j av a2s.  com
    try {
        String voucherExcludeStatuses = getExcludeStatuses();
        StringBuffer query = new StringBuffer().append(
                " select string_agg(distinct v.vouchernumber, ',') as \"voucherNumber\" ,ih.id as \"ihId\", case when ih.instrumentNumber is null then 'Direct' else ih.instrumentNumber  end as \"chequeNumber\", "
                        + " to_char(ih.instrumentdate,'dd/mm/yyyy') as \"chequeDate\" ,ih.instrumentAmount as \"chequeAmount\",rec.transactiontype as \"txnType\" , "
                        + " case when rec.transactionType='Cr' then  'Payment' else 'Receipt' end as \"type\" "
                        + " FROM BANKRECONCILIATION rec, BANKACCOUNT BANK,"
                        + " VOUCHERHEADER v ,egf_instrumentheader ih, egf_instrumentotherdetails io, egf_instrumentVoucher iv   WHERE "
                        + "  ih.bankAccountId = BANK.ID AND bank.id =:bankAccId   AND IH.INSTRUMENTDATE <= :toDate  "
                        + " AND v.ID= iv.voucherheaderid  and v.STATUS not in  (" + voucherExcludeStatuses
                        + ")  " + instrumentCondition
                        + " AND ((ih.id_status=(select id from egw_status where moduletype='Instrument'  and description='Deposited') and ih.ispaycheque='0') or (ih.ispaycheque='1' and  ih.id_status=(select id from egw_status where moduletype='Instrument'  and description='New'))) "
                        + " AND rec.instrumentHeaderId=ih.id    and iv.instrumentHeaderid=ih.id and io.instrumentheaderid=ih.id and ih.instrumentNumber is not null"
                        + " group by ih.id,rec.transactiontype "

                        + " union  "

                        + " select string_agg(distinct v.vouchernumber, ',') as \"voucherNumber\" , ih.id as \"ihId\", case when ih.transactionnumber is null then 'Direct' else ih.transactionnumber end as \"chequeNumber\", "
                        + " to_char(ih.transactiondate,'dd/mm/yyyy') as \"chequedate\" ,ih.instrumentAmount as \"chequeamount\",rec.transactiontype as \"txnType\", case when rec.transactionType= 'Cr' then 'Payment' else 'Receipt' end    as \"type\" "
                        + " FROM BANKRECONCILIATION rec, BANKACCOUNT BANK,"
                        + " VOUCHERHEADER v ,egf_instrumentheader ih, egf_instrumentotherdetails io, egf_instrumentVoucher iv   WHERE   ih.bankAccountId = BANK.ID AND bank.id = :bankAccId "
                        + "   AND IH.INSTRUMENTDATE <= :toDate " + instrumentCondition
                        + " AND v.ID= iv.voucherheaderid and v.STATUS not in  (" + voucherExcludeStatuses
                        + ") AND ((ih.id_status=(select id from egw_status where moduletype='Instrument'  and description='Deposited') and ih.ispaycheque='0')or (ih.ispaycheque='1' and  ih.id_status=(select id from egw_status where moduletype='Instrument'  and description='New'))) "
                        + " AND rec.instrumentHeaderId=ih.id    and iv.instrumentHeaderid=ih.id and io.instrumentheaderid=ih.id and ih.transactionnumber is not null"
                        + "   group by ih.id,rec.transactiontype order by 4 ");

        if (reconBean.getLimit() != null & reconBean.getLimit() != 0) {
            query.append(" limit " + reconBean.getLimit());
        }

        // if(LOGGER.isInfoEnabled())    
        LOGGER.info("  query  for getUnReconciledCheques: " + query);
        /*String query=" SELECT decode(rec.chequeNumber, null, 'Direct', rec.chequeNumber) as \"chequeNumber\",rec.chequedate as \"chequedate\" ,amount as \"chequeamount\",transactiontype as \"txnType\" ,rec.type as \"type\" from bankreconciliation rec, bankAccount bank, voucherheader vh "
           +" where  rec.bankAccountId = bank.id AND bank.id ="+bankAccId+" and  rec.isReversed = 0 AND (rec.reconciliationDate > to_date('"+recDate+"'  || ' 23:59:59','DD-MON-YYYY HH24:MI:SS') "
           +" OR (rec.isReconciled = 0)) AND vh.VOUCHERDATE <= to_date('"+recDate+"'  || ' 23:59:59','DD-MON-YYYY HH24:MI:SS') and vh.id=rec.VOUCHERHEADERID and vh.STATUS<>4"
           +" union "
           +" select refno as \"chequeNumber\", txndate as \"chequedate\", txnamount as \"chequeamount\", decode(type,'R','Dr','Cr') as \"txnType\", "
           +" type as \"type\" from bankentries be,bankAccount bank where  be.bankAccountId = bank.id and bank.id ="+bankAccId+"  "
           +" and txndate<= to_date('"+recDate+"'  || ' 23:59:59','DD-MON-YYYY HH24:MI:SS') and voucherheaderid is null ";
        */

        SQLQuery createSQLQuery = persistenceService.getSession().createSQLQuery(query.toString());
        createSQLQuery.setLong("bankAccId", reconBean.getAccountId());
        createSQLQuery.setDate("toDate", reconBean.getReconciliationDate());
        createSQLQuery.addScalar("voucherNumber", StringType.INSTANCE);
        createSQLQuery.addScalar("ihId", LongType.INSTANCE);
        createSQLQuery.addScalar("chequeDate", StringType.INSTANCE);
        createSQLQuery.addScalar("chequeNumber", StringType.INSTANCE);
        createSQLQuery.addScalar("chequeAmount", BigDecimalType.INSTANCE);
        createSQLQuery.addScalar("txnType", StringType.INSTANCE);
        createSQLQuery.addScalar("type", StringType.INSTANCE);
        createSQLQuery.setResultTransformer(Transformers.aliasToBean(ReconcileBean.class));
        list = (List<ReconcileBean>) createSQLQuery.list();

    } catch (Exception e) {
        LOGGER.error("Exp in getUnReconciledCheques:" + e.getMessage());
        throw new ApplicationRuntimeException(e.getMessage());
    }

    return list;
}

From source file:org.egov.egf.web.actions.voucher.CancelBillAction.java

License:Open Source License

@Action(value = "/voucher/cancelBill-cancelBill")
public String cancelBill() {
    new Date();
    EgBillregister billRegister;/*w w w . j a  v  a2 s  .  c o m*/

    final Long[] idList = new Long[billListDisplay.size()];
    int i = 0, idListLength = 0;
    String idString = "";
    final StringBuilder statusQuery = new StringBuilder("from EgwStatus where ");
    final StringBuilder cancelQuery = new StringBuilder("Update eg_billregister set ");
    for (final BillRegisterBean billRgistrBean : billListDisplay)
        if (billRgistrBean.getIsSelected()) {
            idList[i++] = Long.parseLong(billRgistrBean.getId());
            idListLength++;
        }
    if (expType == null || expType.equalsIgnoreCase("")) {
        statusQuery.append("moduletype='" + FinancialConstants.CONTINGENCYBILL_FIN + "' and description='"
                + FinancialConstants.CONTINGENCYBILL_CANCELLED_STATUS + "'");
        cancelQuery.append(" billstatus='" + FinancialConstants.CONTINGENCYBILL_CANCELLED_STATUS
                + "' , statusid=:statusId ");
    } else if (FinancialConstants.STANDARD_EXPENDITURETYPE_SALARY.equalsIgnoreCase(expType)) {
        statusQuery.append("moduletype='" + FinancialConstants.SALARYBILL + "' and description='"
                + FinancialConstants.SALARYBILL_CANCELLED_STATUS + "'");
        cancelQuery.append(
                " billstatus='" + FinancialConstants.SALARYBILL_CANCELLED_STATUS + "' , statusid=:statusId ");

    } else if (FinancialConstants.STANDARD_EXPENDITURETYPE_CONTINGENT.equalsIgnoreCase(expType)) {
        statusQuery.append("moduletype='" + FinancialConstants.CONTINGENCYBILL_FIN + "' and description='"
                + FinancialConstants.CONTINGENCYBILL_CANCELLED_STATUS + "'");
        cancelQuery.append(" billstatus='" + FinancialConstants.CONTINGENCYBILL_CANCELLED_STATUS
                + "' , statusid=:statusId ");
    } else if (FinancialConstants.STANDARD_EXPENDITURETYPE_PURCHASE.equalsIgnoreCase(expType)) {
        statusQuery.append("moduletype='" + FinancialConstants.SUPPLIERBILL + "' and description='"
                + FinancialConstants.SUPPLIERBILL_CANCELLED_STATUS + "'");
        cancelQuery.append(
                " billstatus='" + FinancialConstants.SUPPLIERBILL_CANCELLED_STATUS + "' , statusid=:statusId ");
    } else if (FinancialConstants.STANDARD_EXPENDITURETYPE_WORKS.equalsIgnoreCase(expType)) {
        statusQuery.append("moduletype='" + FinancialConstants.CONTRACTORBILL + "' and description='"
                + FinancialConstants.CONTRACTORBILL_CANCELLED_STATUS + "'");
        cancelQuery.append(" billstatus='" + FinancialConstants.CONTRACTORBILL_CANCELLED_STATUS
                + "' , statusid=:statusId ");
    }
    if (LOGGER.isDebugEnabled())
        LOGGER.debug(" Status Query - " + statusQuery.toString());
    final EgwStatus status = (EgwStatus) persistenceService.find(statusQuery.toString());
    persistenceService.getSession();
    if (idListLength != 0) {
        for (i = 0; i < idListLength; i++) {
            billRegister = billsService.getBillRegisterById(idList[i].intValue());
            final boolean value = cancelBillAndVoucher.canCancelBill(billRegister);
            if (!value) {
                addActionMessage(getText("cancel.bill.failure", new String[] { billRegister.getBillnumber() }));
                continue;
            }
            idString += idList[i] + (i == idListLength - 1 ? "" : ",");
        }
        if (isNotBlank(idString)) {
            if (idString.charAt(idString.length() - 1) == ',')
                idString = idString.replace(",", "");
        }
        cancelQuery.append(" where id in (" + idString + ")");
        if (LOGGER.isDebugEnabled())
            LOGGER.debug(" Cancel Query - " + cancelQuery.toString());
        final SQLQuery totalSQLQuery = persistenceService.getSession().createSQLQuery(cancelQuery.toString());
        totalSQLQuery.setLong("statusId", status.getId());
        if (isNotBlank(idString))
            totalSQLQuery.executeUpdate();
    }

    if (isNotBlank(idString))
        addActionMessage(getText("cancel.bill.success"));

    prepareBeforeSearch();
    return "search";
}

From source file:org.egov.services.contra.ContraService.java

License:Open Source License

private void updateInstrumentAndPayinSql(final Map instrumentDetailsMap) {
    final String ioSql = "update EGF_INSTRUMENTOTHERDETAILS set PAYINSLIPID=:payinId,INSTRUMENTSTATUSDATE=:ihStatusDate,"
            + " LASTMODIFIEDBY=:modifiedBy, LASTMODIFIEDDATE =:modifiedDate where INSTRUMENTHEADERID=:ihId";

    final SQLQuery ioSQLQuery = getSession().createSQLQuery(ioSql);
    ioSQLQuery.setLong("payinId", (Long) instrumentDetailsMap.get("payinid"))
            .setLong("ihId", (Long) instrumentDetailsMap.get("instrumentheader"))
            .setDate("ihStatusDate", (Date) instrumentDetailsMap.get("depositdate"))
            .setDate("modifiedDate", new Date())
            .setLong("modifiedBy", (Long) instrumentDetailsMap.get("createdby"));
    ioSQLQuery.executeUpdate();/*from   www.  j  a  v a 2 s. co m*/

    final String ihSql = "update EGF_instrumentheader  set ID_STATUS=:statusId,BANKACCOUNTID=:bankAccId,LASTMODIFIEDBY=:modifiedBy,"
            + " LASTMODIFIEDDATE =:modifiedDate where id=:ihId";

    final SQLQuery ihSQLQuery = getSession().createSQLQuery(ihSql);
    if (instrumentDetailsMap.get("instrumenttype").equals(FinancialConstants.INSTRUMENT_TYPE_DD)
            || instrumentDetailsMap.get("instrumenttype").equals(FinancialConstants.INSTRUMENT_TYPE_CHEQUE))
        ihSQLQuery.setLong("statusId", (Long) instrumentDetailsMap.get("instrumentDepositedStatus"));
    else if (instrumentDetailsMap.get("instrumenttype").equals(FinancialConstants.INSTRUMENT_TYPE_CASH))
        ihSQLQuery.setLong("statusId", (Long) instrumentDetailsMap.get("instrumentReconciledStatus"));

    ihSQLQuery.setLong("ihId", (Long) instrumentDetailsMap.get("instrumentheader"))
            .setLong("bankAccId", (Long) instrumentDetailsMap.get("bankaccountid"))
            .setDate("modifiedDate", new Date())
            .setLong("modifiedBy", (Long) instrumentDetailsMap.get("createdby"));
    ihSQLQuery.executeUpdate();

}

From source file:org.egov.services.contra.ContraService.java

License:Open Source License

/**
 *
 * @param instrumentDetailsMap//from   w  ww  .j a v a 2 s .  co  m
 * @throws ApplicationRuntimeException
 *
 * Will update bank reconcilation and set isreconciled to true for the type 1. cash 2.ECS 3. bank challan 4. bank
 */
public void addToBankReconcilationSQL(final Map instrumentDetailsMap) throws ApplicationRuntimeException {
    final String brsSql = "Insert into bankreconciliation (ID,BANKACCOUNTID,AMOUNT,TRANSACTIONTYPE,INSTRUMENTHEADERID) values "
            + " (nextVal('seq_bankreconciliation'),:bankAccId,:amount,:trType,:ihId)";
    final SQLQuery brsSQLQuery = getSession().createSQLQuery(brsSql);

    brsSQLQuery.setLong("bankAccId", (Long) instrumentDetailsMap.get("bankaccountid"))
            .setBigDecimal("amount", (BigDecimal) instrumentDetailsMap.get("instrumentamount"))
            .setString("trType",
                    "1".equalsIgnoreCase((String) instrumentDetailsMap.get("ispaycheque")) ? "Cr" : "Dr")
            .setLong("ihId", (Long) instrumentDetailsMap.get("instrumentheader"));
    brsSQLQuery.executeUpdate();

    if (FinancialConstants.INSTRUMENT_TYPE_CASH
            .equalsIgnoreCase((String) instrumentDetailsMap.get("instrumenttype"))
            || FinancialConstants.INSTRUMENT_TYPE_BANK
                    .equalsIgnoreCase((String) instrumentDetailsMap.get("instrumenttype"))
            || FinancialConstants.INSTRUMENT_TYPE_BANK_TO_BANK
                    .equalsIgnoreCase((String) instrumentDetailsMap.get("instrumenttype"))
            || FinancialConstants.INSTRUMENT_TYPE_ECS
                    .equalsIgnoreCase((String) instrumentDetailsMap.get("instrumenttype"))) {
        final String ioSql = "update EGF_instrumentOtherdetails set reconciledamount=:reconciledAmt,INSTRUMENTSTATUSDATE=:ihStatusDate,LASTMODIFIEDBY=:modifiedBy,"
                + " LASTMODIFIEDDATE =:modifiedDate where INSTRUMENTHEADERID=:ihId";

        final SQLQuery ioSQLQuery = getSession().createSQLQuery(ioSql);
        ioSQLQuery.setLong("ihId", (Long) instrumentDetailsMap.get("instrumentheader"))
                .setBigDecimal("reconciledAmt", (BigDecimal) instrumentDetailsMap.get("instrumentamount"))
                .setDate("ihStatusDate", (Date) instrumentDetailsMap.get("depositdate"))
                .setDate("modifiedDate", new Date())
                .setLong("modifiedBy", (Long) instrumentDetailsMap.get("createdby"));
        ioSQLQuery.executeUpdate();

        final String ihSql = "update EGF_instrumentheader  set ID_STATUS=:statusId,LASTMODIFIEDBY=:modifiedBy,"
                + " LASTMODIFIEDDATE =:modifiedDate where id=:ihId";
        final SQLQuery ihSQLQuery = getSession().createSQLQuery(ihSql);
        ihSQLQuery.setLong("statusId", (Long) instrumentDetailsMap.get("instrumentReconciledStatus"))
                .setLong("ihId", (Long) instrumentDetailsMap.get("instrumentheader"))
                .setDate("modifiedDate", new Date())
                .setLong("modifiedBy", (Long) instrumentDetailsMap.get("createdby"));
        ihSQLQuery.executeUpdate();

    }

}

From source file:org.egov.services.contra.ContraService.java

License:Open Source License

private void addToContraSql(final Map instrumentDetailsMap) {

    final String ioSql = "Insert into contrajournalvoucher (ID,VOUCHERHEADERID,FROMBANKACCOUNTID,TOBANKACCOUNTID,INSTRUMENTHEADERID"
            + " ,STATE_ID,CREATEDBY,LASTMODIFIEDBY) values "
            + " (nextVal('seq_contrajournalvoucher'),:vhId,null,:depositedBankId,:ihId,null,:createdBy,:createdBy)";
    final SQLQuery ioSQLQuery = getSession().createSQLQuery(ioSql);
    ioSQLQuery.setLong("vhId", (Long) instrumentDetailsMap.get("payinid"))
            .setLong("ihId", (Long) instrumentDetailsMap.get("instrumentheader"))
            .setLong("depositedBankId", (Long) instrumentDetailsMap.get("bankaccountid"))
            .setLong("createdBy", (Long) instrumentDetailsMap.get("createdby"));
    ioSQLQuery.executeUpdate();/*w w w .j  av a 2 s. c  o  m*/

}

From source file:org.egov.services.report.IncomeExpenditureService.java

License:Open Source License

private List<StatementResultObject> getBudgetForMajorCodes(final Statement incomeExpenditureStatement) {

    final StringBuilder queryStr = new StringBuilder(1000);

    queryStr.append(" select coa.majorCode as glcode, sum(bd.approvedamount) as amount ").append(
            " from egf_budgetdetail bd , egf_budgetgroup bg,egf_budget b, chartofaccounts coa, eg_wf_states wfs ")
            .append("where ((bg.maxcode<=coa.id and bg.mincode>=coa.id) or bg.majorcode=coa.id ) ")
            .append("and bd.budgetgroup= bg.id and bd.budget=b.id and  bd.state_id=wfs.id  and wfs.value='END'")
            .append("and b.isbere=:isBeRe and b.financialyearid=:finYearId  ");
    if (incomeExpenditureStatement.getFund() != null && incomeExpenditureStatement.getFund().getId() != null
            && incomeExpenditureStatement.getFund().getId() != 0)
        queryStr.append(" and bd.fund=" + incomeExpenditureStatement.getFund().getId());
    if (incomeExpenditureStatement.getDepartment() != null
            && incomeExpenditureStatement.getDepartment().getId() != 0)
        queryStr.append(" and bd.executing_department=" + incomeExpenditureStatement.getDepartment().getId());
    if (incomeExpenditureStatement.getFunction() != null
            && incomeExpenditureStatement.getFunction().getId() != null
            && incomeExpenditureStatement.getFunction().getId() != 0)
        queryStr.append("  and bd.function= " + incomeExpenditureStatement.getFunction().getId());

    queryStr.append(" and coa.majorcode is not null  group by coa.majorCode ");

    queryStr.append(" order by 1");
    final SQLQuery budgteQuery = persistenceService.getSession().createSQLQuery(queryStr.toString());
    budgteQuery.addScalar("glCode").addScalar("amount")
            .setResultTransformer(Transformers.aliasToBean(StatementResultObject.class));
    budgteQuery.setLong("finYearId", incomeExpenditureStatement.getFinancialYear().getId()).setString("isBeRe",
            "RE");
    return budgteQuery.list();

}

From source file:org.egov.services.report.IncomeExpenditureService.java

License:Open Source License

private List<StatementResultObject> getBudgetReappMinorCodes(final Statement incomeExpenditureStatement) {
    final StringBuilder queryStr = new StringBuilder(1000);

    queryStr.append(//  w  ww .  ja v a 2s.  c o m
            " select coa.majorcode as glCode, sum(bdr.addition_amount- bdr.deduction_amount) as amount ")
            .append(" from egf_budgetdetail bd , egf_budgetgroup bg,egf_budget b, chartofaccounts coa,eg_wf_states wfs,")
            .append("egf_budget_reappropriation bdr where ((bg.maxcode<=coa.id and bg.mincode>=coa.id) or bg.majorcode=coa.id ) ")
            .append("and bd.budgetgroup= bg.id and bdr.budgetdetail=bd.id and bd.budget=b.id and bdr.state_id=wfs.id ")
            .append("and wfs.value='END' and b.isbere=:isBeRe and b.financialyearid=:finYearId  ");

    if (incomeExpenditureStatement.getFund() != null && incomeExpenditureStatement.getFund().getId() != null
            && incomeExpenditureStatement.getFund().getId() != 0)
        queryStr.append(" and bd.fund=" + incomeExpenditureStatement.getFund().getId());
    if (incomeExpenditureStatement.getDepartment() != null
            && incomeExpenditureStatement.getDepartment().getId() != 0)
        queryStr.append(" and bd.executing_department=" + incomeExpenditureStatement.getDepartment().getId());
    if (incomeExpenditureStatement.getFunction() != null
            && incomeExpenditureStatement.getFunction().getId() != null
            && incomeExpenditureStatement.getFunction().getId() != 0)
        queryStr.append("  and bd.function= " + incomeExpenditureStatement.getFunction().getId());
    queryStr.append("  group by coa.majorCode ");

    queryStr.append(" order by 1 asc");
    final SQLQuery budgteReappQuery = persistenceService.getSession().createSQLQuery(queryStr.toString());
    budgteReappQuery.addScalar("glCode").addScalar("amount")
            .setResultTransformer(Transformers.aliasToBean(StatementResultObject.class));
    budgteReappQuery.setLong("finYearId", incomeExpenditureStatement.getFinancialYear().getId())
            .setString("isBeRe", "RE");
    return budgteReappQuery.list();
}