List of usage examples for org.hibernate SQLQuery setLong
@Deprecated @SuppressWarnings("unchecked") default Query<R> setLong(int position, long val)
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(); }