List of usage examples for org.hibernate SQLQuery setBigDecimal
@Deprecated @SuppressWarnings("unchecked") default Query<R> setBigDecimal(int position, BigDecimal val)
From source file:com.aw.core.db.support.WhereBuilder2.java
License:Open Source License
public void setParams(SQLQuery sqlQuery) { for (int i = 0; i < params.size(); i++) { Object param = params.get(i); if (param instanceof Long) sqlQuery.setLong(i, (Long) param); else if (param instanceof Integer) sqlQuery.setInteger(i, (Integer) param); else if (param instanceof Date) sqlQuery.setDate(i, (Date) param); else if (param instanceof String) sqlQuery.setString(i, (String) param); else if (param instanceof BigDecimal) sqlQuery.setBigDecimal(i, (BigDecimal) param); else if (param == null) sqlQuery.setParameter(i, null); else// w w w . j a v a 2 s .c o m throw new IllegalArgumentException("Implementar codigo param:" + param.getClass()); } //To change body of created methods use File | Settings | File Templates. }
From source file:com.lp.server.system.automatikjob.AutomatikjobPaternoster.java
License:Open Source License
private void speicherePaternostermenge(org.hibernate.Session session, Integer paternosterIId, String cNr, BigDecimal menge, Timestamp ts) { String sql = "UPDATE WW_ARTIKELLAGERPLAETZE SET N_LAGERSTANDPATERNOSTER = ?, T_AENDERN = ? " + "WHERE I_ID = (SELECT I_ID FROM WW_ARTIKELLAGERPLAETZE " + "WHERE LAGERPLATZ_I_ID = (SELECT I_ID FROM WW_LAGERPLATZ " + "WHERE PATERNOSTER_I_ID = ? AND ARTIKEL_I_ID = " + "(SELECT I_ID FROM WW_ARTIKEL WHERE C_NR = ?)))"; org.hibernate.SQLQuery sq = session.createSQLQuery(sql); sq.setBigDecimal(0, menge); sq.setTimestamp(1, ts);//from ww w .j a va2s .c o m sq.setInteger(2, paternosterIId); sq.setString(3, cNr); int anzahl = sq.executeUpdate(); if (anzahl == 0) { if (menge.doubleValue() > 0) { myLogger.info("Artikel " + cNr + " ist im Paternoster ohne Paternosterlagerplatz mit Menge " + menge.doubleValue()); } } }
From source file:lt.emasina.resthub.server.handler.Handler.java
License:Open Source License
public void applyParameters(SQLQuery query) throws SQLException { for (Map.Entry<QueryParameter, Object> e : parameters.entrySet()) { QueryParameter p = e.getKey();/*from w w w . j a va2s. c om*/ Object value = e.getValue(); String name = p.getSqlName(); if (value != null && p.getArray()) { switch (p.getType()) { case DATE: query.setParameterList(name, (Object[]) value, new DateType()); break; case NUMBER: query.setParameterList(name, (Object[]) value, new BigDecimalType()); break; case STRING: query.setParameterList(name, (Object[]) value, new StringType()); break; case CLOB: case BLOB: throw new ClientErrorException(Status.CLIENT_ERROR_BAD_REQUEST, String.format("LOBs are not supported as parameters: %s", name)); } } else { switch (p.getType()) { case DATE: query.setDate(name, (Date) value); break; case NUMBER: query.setBigDecimal(name, (BigDecimal) value); break; case STRING: query.setString(name, (String) value); break; case CLOB: case BLOB: throw new ClientErrorException(Status.CLIENT_ERROR_BAD_REQUEST, String.format("LOBs are not supported as parameters: %s", name)); } } } }
From source file:org.egov.egf.web.actions.brs.AutoReconcileHelper.java
License:Open Source License
/** * @return/*w w w . ja va 2 s.c o m*/ */ @Transactional public String schedule() { // Step1: mark which are all we are going to process count = 0; // persistenceService.getSession().getTransaction().setTimeout(900); if (LOGGER.isDebugEnabled()) LOGGER.debug("Started at " + new Date()); markForProcessing(BRS_TRANSACTION_TYPE_CHEQUE); persistenceService.getSession().flush(); // step2 :find duplicate and mark to be processed manually findandUpdateDuplicates(); final List<AutoReconcileBean> detailList = getStatmentsForProcessing(BRS_TRANSACTION_TYPE_CHEQUE); final String statusQury = "select id from EgwStatus where upper(moduletype)=upper('instrument') and upper(description)=upper('" + FinancialConstants.INSTRUMENT_RECONCILED_STATUS + "')"; statusId = (Integer) persistenceService.find(statusQury); final Long instrumentTypeId = getInstrumentType(FinancialConstants.INSTRUMENT_TYPE_CHEQUE); final Long instrumentTypeDDId = getInstrumentType(FinancialConstants.INSTRUMENT_TYPE_DD); // where instrumentheaderid= (select id.....) is used to fetch only one record may be double submit or two instrument // entries // let the user decide final String recociliationQuery = "update EGF_InstrumentHeader set id_status=:statusId, lastmodifiedby=:userId,lastmodifieddate=CURRENT_DATE" + " where id= (select id from egf_instrumentheader where instrumentNumber=:instrumentNo and " + " instrumentAmount=:amount and bankaccountid=:accountId and ispaycheque=:ispaycheque and instrumentType in (" + instrumentTypeId + "," + instrumentTypeDDId + ")" + " and id_status=(select id from Egw_Status where upper(moduletype)=upper('instrument') and upper(description)=upper(:instrumentStatus)))"; final String recociliationAmountQuery = "update egf_instrumentOtherdetails set reconciledamount=:amount,instrumentstatusdate=:txDate " + " ,lastmodifiedby=:userId,lastmodifieddate=CURRENT_DATE,reconciledOn=:reconciliationDate " + " where instrumentheaderid= (select id from egf_instrumentheader where instrumentNumber=:instrumentNo and " + " instrumentAmount=:amount and bankaccountid=:accountId and ispaycheque=:ispaycheque and instrumentType in (" + instrumentTypeId + "," + instrumentTypeDDId + ")" + " and id_status=(select id from Egw_Status where upper(moduletype)=upper('instrument') and upper(description)=upper(:instrumentStatus)))"; final SQLQuery updateQuery = persistenceService.getSession().createSQLQuery(recociliationQuery); final SQLQuery updateQuery2 = persistenceService.getSession().createSQLQuery(recociliationAmountQuery); final String backUpdateBankStmtquery = "update " + TABLENAME + " set action='" + BRS_ACTION_PROCESSED + "' ,reconciliationDate=:reconciliationDate where id=:id"; final String backUpdateFailureBRSquery = "update " + TABLENAME + " set action='" + BRS_ACTION_TO_BE_PROCESSED_MANUALLY + "',errormessage=:e where id=:id"; final SQLQuery backupdateQuery = persistenceService.getSession().createSQLQuery(backUpdateBankStmtquery); final SQLQuery backupdateFailureQuery = persistenceService.getSession() .createSQLQuery(backUpdateFailureBRSquery); rowCount = 0; for (final AutoReconcileBean bean : detailList) { int updated = -1; try { updateQuery.setLong("statusId", statusId); updateQuery.setLong("accountId", accountId); updateQuery.setString("instrumentNo", bean.getInstrumentNo()); updateQuery.setInteger("userId", ApplicationThreadLocals.getUserId().intValue()); updateQuery2.setDate("txDate", bean.getTxDate()); updateQuery2.setDate("reconciliationDate", reconciliationDate); updateQuery2.setLong("accountId", accountId); updateQuery2.setString("instrumentNo", bean.getInstrumentNo()); updateQuery2.setInteger("userId", ApplicationThreadLocals.getUserId().intValue()); if (bean.getDebit() != null && bean.getDebit().compareTo(BigDecimal.ZERO) != 0) { updateQuery.setBigDecimal("amount", bean.getDebit()); updateQuery.setCharacter("ispaycheque", '1'); updateQuery.setString("instrumentStatus", FinancialConstants.INSTRUMENT_CREATED_STATUS); updated = updateQuery.executeUpdate(); if (updated != 0) { updateQuery2.setBigDecimal("amount", bean.getDebit()); updateQuery2.setCharacter("ispaycheque", '1'); updateQuery2.setString("instrumentStatus", FinancialConstants.INSTRUMENT_RECONCILED_STATUS); updated = updateQuery2.executeUpdate(); } } else { updateQuery.setBigDecimal("amount", bean.getCredit()); updateQuery.setCharacter("ispaycheque", '0'); updateQuery.setString("instrumentStatus", FinancialConstants.INSTRUMENT_DEPOSITED_STATUS); updated = updateQuery.executeUpdate(); if (updated != 0) { updateQuery2.setBigDecimal("amount", bean.getCredit()); updateQuery2.setCharacter("ispaycheque", '0'); updateQuery2.setString("instrumentStatus", FinancialConstants.INSTRUMENT_RECONCILED_STATUS); updated = updateQuery2.executeUpdate(); } } // if updated is 0 means nothing got updated means could not find matching row in instrumentheader if (updated == 0) { backupdateFailureQuery.setLong("id", bean.getId()); backupdateFailureQuery.setString("e", DID_NOT_FIND_MATCH_IN_BANKBOOK); backupdateFailureQuery.executeUpdate(); } else { backupdateQuery.setLong("id", bean.getId()); backupdateQuery.setDate("reconciliationDate", reconciliationDate); backupdateQuery.executeUpdate(); count++; // if(LOGGER.isDebugEnabled()) LOGGER.debug(count); } rowCount++; if (LOGGER.isDebugEnabled()) LOGGER.debug("out of " + rowCount + "==>succesfull " + count); if (rowCount % 20 == 0) persistenceService.getSession().flush(); // These exception might be because the other entires in instrument which is not in egf_brs_bankstatements // so any issues leave it for manual update } catch (final HibernateException e) { if (e.getCause().getMessage().contains("single-row subquery returns more")) backupdateFailureQuery.setString("e", BRS_MESSAGE_MORE_THAN_ONE_MATCH); else backupdateFailureQuery.setString("e", e.getMessage()); backupdateFailureQuery.setLong("id", bean.getId()); backupdateFailureQuery.executeUpdate(); } catch (final Exception e) { backupdateFailureQuery.setLong("id", bean.getId()); backupdateFailureQuery.setString("e", e.getMessage()); backupdateFailureQuery.executeUpdate(); } } processCSL(); return "result"; }
From source file:org.egov.egf.web.actions.brs.AutoReconcileHelper.java
License:Open Source License
private void processCSL() { markForProcessing(BRS_TRANSACTION_TYPE_BANK); final List<AutoReconcileBean> CSLList = getStatmentsForProcessing(BRS_TRANSACTION_TYPE_BANK); final Long instrumentTypeId = getInstrumentType(FinancialConstants.INSTRUMENT_TYPE_BANK_TO_BANK); final String recociliationQuery = "update EGF_InstrumentHeader set id_status=:statusId, lastmodifiedby=:userId,lastmodifieddate=CURRENT_DATE" + " where id = (select ih.id from egf_instrumentheader ih,egf_instrumentvoucher iv,voucherheader vh where " + " instrumentAmount=:amount and bankaccountid=:accountId and ispaycheque=:ispaycheque and instrumentType in (" + instrumentTypeId + ")" + " and id_status=(select id from Egw_Status where upper(moduletype)=upper('instrument') and upper(description)=" + " upper(:instrumentStatus)) and iv.instrumentheaderid=ih.id and iv.voucherheaderid=ih.id and vh.vouchernumber=:cslNo ) "; final String recociliationAmountQuery = "update egf_instrumentOtherdetails set reconciledamount=:amount,instrumentstatusdate=:txDate " + " ,lastmodifiedby=:userId,lastmodifieddate=CURRENT_DATE,reconciledOn=:reconciliationDate " + " where instrumentheaderid = (select ih.id from egf_instrumentheader ih,egf_instrumentvoucher iv,voucherheader vh where " + " instrumentAmount=:amount and bankaccountid=:accountId and ispaycheque=:ispaycheque and instrumentType in (" + instrumentTypeId + ")" + " and id_status=(select id from Egw_Status where upper(moduletype)=upper('instrument') and upper(description)=" + " upper(:instrumentStatus)) and iv.instrumentheaderid=ih.id and iv.voucherheaderid=ih.id and vh.vouchernumber=:cslNo ) "; final SQLQuery updateQuery = persistenceService.getSession().createSQLQuery(recociliationQuery); final SQLQuery updateQuery2 = persistenceService.getSession().createSQLQuery(recociliationAmountQuery); final String backUpdateBankStmtquery = "update " + TABLENAME + " set action='" + BRS_ACTION_PROCESSED + "' ,reconciliationDate=:reconciliationDate where id=:id"; final String backUpdateFailureBRSquery = "update " + TABLENAME + " set action='" + BRS_ACTION_TO_BE_PROCESSED_MANUALLY + "',errormessage=:e where id=:id"; final SQLQuery backupdateQuery = persistenceService.getSession().createSQLQuery(backUpdateBankStmtquery); final SQLQuery backupdateFailureQuery = persistenceService.getSession() .createSQLQuery(backUpdateFailureBRSquery); for (final AutoReconcileBean bean : CSLList) { int updated = -1; try {// www . j ava 2 s .com updateQuery.setLong("statusId", statusId); updateQuery.setLong("accountId", accountId); updateQuery.setString("cslNo", bean.getCSLno()); updateQuery.setInteger("userId", ApplicationThreadLocals.getUserId().intValue()); updateQuery2.setDate("txDate", bean.getTxDate()); updateQuery2.setDate("reconciliationDate", reconciliationDate); updateQuery2.setLong("accountId", accountId); updateQuery2.setString("cslNo", bean.getCSLno()); updateQuery2.setInteger("userId", ApplicationThreadLocals.getUserId().intValue()); if (bean.getDebit() != null && bean.getDebit().compareTo(BigDecimal.ZERO) != 0) { updateQuery.setBigDecimal("amount", bean.getDebit()); updateQuery.setCharacter("ispaycheque", '1'); updateQuery.setString("instrumentStatus", FinancialConstants.INSTRUMENT_CREATED_STATUS); updated = updateQuery.executeUpdate(); if (updated != 0) { updateQuery2.setBigDecimal("amount", bean.getDebit()); updateQuery2.setCharacter("ispaycheque", '1'); updateQuery2.setString("instrumentStatus", FinancialConstants.INSTRUMENT_RECONCILED_STATUS); updated = updateQuery2.executeUpdate(); } } else { updateQuery.setBigDecimal("amount", bean.getCredit()); updateQuery.setCharacter("ispaycheque", '1'); updateQuery.setString("instrumentStatus", FinancialConstants.INSTRUMENT_CREATED_STATUS); updated = updateQuery.executeUpdate(); if (updated != 0) { updateQuery2.setBigDecimal("amount", bean.getCredit()); updateQuery2.setCharacter("ispaycheque", '1'); updateQuery2.setString("instrumentStatus", FinancialConstants.INSTRUMENT_RECONCILED_STATUS); updated = updateQuery2.executeUpdate(); } if (updated == 0) { } } // if updated is 0 means nothing got updated means could not find matching row in instrumentheader if (updated == 0) { backupdateFailureQuery.setLong("id", bean.getId()); backupdateFailureQuery.setString("e", DID_NOT_FIND_MATCH_IN_BANKBOOK); backupdateFailureQuery.executeUpdate(); } else if (updated == -1) { backupdateFailureQuery.setLong("id", bean.getId()); backupdateFailureQuery.setString("e", DID_NOT_FIND_MATCH_IN_BANKBOOK); backupdateFailureQuery.executeUpdate(); // if(LOGGER.isDebugEnabled()) LOGGER.debug(count); } else { backupdateQuery.setLong("id", bean.getId()); backupdateQuery.setDate("reconciliationDate", reconciliationDate); backupdateQuery.executeUpdate(); count++; // if(LOGGER.isDebugEnabled()) LOGGER.debug(count); } rowCount++; if (LOGGER.isDebugEnabled()) LOGGER.debug("out of " + rowCount + "==>succesfull " + count); if (rowCount % 20 == 0) persistenceService.getSession().flush(); // These exception might be because the other entires in instrument which is not in egf_brs_bankstatements // so any issues leave it for manual update } catch (final HibernateException e) { if (e.getCause().getMessage().contains("single-row subquery returns more")) backupdateFailureQuery.setString("e", BRS_MESSAGE_MORE_THAN_ONE_MATCH); else backupdateFailureQuery.setString("e", e.getMessage()); backupdateFailureQuery.setLong("id", bean.getId()); backupdateFailureQuery.executeUpdate(); } catch (final Exception e) { backupdateFailureQuery.setLong("id", bean.getId()); backupdateFailureQuery.setString("e", e.getMessage()); backupdateFailureQuery.executeUpdate(); } } }
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 {/* w ww . ja va 2 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 "); } }