private BigDecimal getSaldoAtual(final Integer codprod) throws Exception { BigDecimal saldoAtual = new BigDecimal("0.00"); StringBuilder sql = new StringBuilder(); sql.append("SELECT NSALDOAX FROM EQPRODUTOSP01(?,?,?,?,?,?, cast('now' as date))"); PreparedStatement ps = con.prepareStatement(sql.toString()); int param = 1; ps.setInt(param++, Aplicativo.iCodEmp); ps.setInt(param++, ListaCampos.getMasterFilial("EQPRODUTO")); ps.setInt(param++, codprod); ps.setInt(param++, Aplicativo.iCodEmp); ps.setInt(param++, ListaCampos.getMasterFilial("EQALMOX")); ps.setInt(param++, getAlmoxarifado(codprod)); ResultSet rs = ps.executeQuery(); if (rs.next()) { saldoAtual = rs.getBigDecimal("NSALDOAX") != null ? rs.getBigDecimal("NSALDOAX") : saldoAtual; } rs.close(); ps.close(); con.commit(); return saldoAtual.setScale(Aplicativo.casasDec, BigDecimal.ROUND_HALF_UP); }
/** * Get Labor Cost from Expense Report * * @param as Account Schema * @return Unit Labor Cost */ private BigDecimal getLaborCost(MAcctSchema as) { // Todor Lulov 30.01.2008 BigDecimal retValue = Env.ZERO; BigDecimal qty = Env.ZERO; String sql = "SELECT ConvertedAmt, Qty FROM S_TimeExpenseLine " + " WHERE S_TimeExpenseLine.S_TimeExpenseLine_ID = ?"; PreparedStatement pstmt = null; ResultSet rs = null; try { pstmt = DB.prepareStatement(sql.toString(), getTrxName()); pstmt.setInt(1, m_issue.getS_TimeExpenseLine_ID()); rs = pstmt.executeQuery(); if (rs.next()) { retValue = rs.getBigDecimal(1); qty = rs.getBigDecimal(2); retValue = retValue.multiply(qty); log.fine("ExpLineCost = " + retValue); } else log.warning("Not found for S_TimeExpenseLine_ID=" + m_issue.getS_TimeExpenseLine_ID()); } catch (Exception e) { log.log(Level.SEVERE, sql.toString(), e); } finally { DB.close(rs, pstmt); pstmt = null; rs = null; } return retValue; } // getLaborCost
@Override public BizLoanTransferApply mapRow(ResultSet rs, int index) throws SQLException { BizLoanTransferApply apply = new BizLoanTransferApply(); apply.setId(rs.getInt("id")); apply.setOriginalLoanId(rs.getInt("original_loan_id")); apply.setInitTransferLoanRate(rs.getBigDecimal("init_transfer_loan_rate")); apply.setTransferLoanRate(rs.getBigDecimal("transfer_loan_rate")); apply.setTransferDirection(TransferDirection.valueOf(rs.getString("transfer_direction"))); if (StringUtils.isNotBlank(rs.getString("lender_user_id"))) { apply.setLenderUserId(rs.getInt("lender_user_id")); } if (StringUtils.isNotBlank(rs.getString("lender_hold_id"))) { apply.setLenderHoldId(rs.getInt("lender_hold_id")); } if (StringUtils.isNotBlank(rs.getString("middle_man_id"))) { apply.setMiddleManId(rs.getInt("middle_man_id")); } if (StringUtils.isNotBlank(rs.getString("middle_hold_id"))) { apply.setMiddleHoldId(rs.getInt("middle_hold_id")); } apply.setTransferCurrentAmount(rs.getBigDecimal("transfer_current_amount")); apply.setCreateDatetime(rs.getTimestamp("create_datetime")); apply.setLastUpdateDatetime(rs.getTimestamp("last_update_datetime")); apply.setTransferStatus(TransferApplyStatus.valueOf(rs.getString("transfer_status"))); apply.setVersion(rs.getInt("version")); return apply; }
public String priceBeco( Ctx ctx, int WindowNo, GridTab mTab, GridField mField, Object value, Object oldValue) { System.out.println("pricebeco ... "); try { BigDecimal precio = (BigDecimal) mField.getValue(); String priceRuleSQL = "select xx_lowrank,xx_highrank,xx_termination,xx_increase,xx_infinitevalue from xx_vme_pricerule order by (xx_lowrank)"; PreparedStatement priceRulePstmt = DB.prepareStatement(priceRuleSQL, null); ResultSet priceRuleRs = priceRulePstmt.executeQuery(); Integer precioInt = precio.intValue(); BigDecimal precioBig = new BigDecimal(precioInt); while (priceRuleRs.next()) { if (precioBig.compareTo(priceRuleRs.getBigDecimal("xx_lowrank")) >= 0 && precioBig.compareTo(priceRuleRs.getBigDecimal("xx_highrank")) <= 0) { Integer incremento = priceRuleRs.getInt("xx_increase"); for (Integer i = priceRuleRs.getInt("xx_lowrank") - 1; i <= priceRuleRs.getInt("xx_highrank"); i = i + incremento) { BigDecimal var = new BigDecimal(i); if (precioBig.compareTo(var) <= 0) { BigDecimal beco = var; BigDecimal terminacion = priceRuleRs.getBigDecimal("xx_termination"); if (terminacion.intValue() == 0) { beco = var.add(terminacion); } else { var = var.divide(new BigDecimal(10)); Integer aux = var.intValue() * 10; beco = new BigDecimal(aux).add(terminacion); } // mTab.setValue("PriceList", beco); priceRuleRs.close(); priceRulePstmt.close(); if (beco.compareTo(precio) == 0) { return priceBandBeco(ctx, WindowNo, mTab, mField, value, oldValue); // return""; } else { mTab.setValue("PriceList", beco); return ""; // return "Precio Beco Sugerido "+beco.toString(); } } } } } priceRuleRs.close(); priceRulePstmt.close(); return priceBandBeco(ctx, WindowNo, mTab, mField, value, oldValue); // return ""; } catch (Exception e) { return e.getMessage(); } }
public Object[] loadReceber( Integer codemprc, Integer codfilialrc, Integer codempcl, Integer codfilialcl, Integer codcli, Integer codemppd, Integer codfilialpd, Integer codprod, Date dtini, Date dtfim) throws Exception { Object[] result = new Object[RESULT_RECEBER.values().length]; try { StringBuilder sql = new StringBuilder(); sql.append("select sum(ir.vlrapagitrec) total_aberto "); sql.append( ", sum(case when ir.dtvencitrec<cast('now' as date) then ir.vlrapagitrec else 0 end) total_atraso "); sql.append(", avg(case when ir.dtvencitrec<coalesce(ir.dtliqitrec,cast('now' as date)) "); sql.append( "then coalesce(ir.dtliqitrec,cast('now' as date)) - ir.dtvencitrec else 0 end) atraso_medio "); // sql.append( ", min(datarec), max(datarec) " ); sql.append("from fnreceber rc, fnitreceber ir "); sql.append( "where rc.codemp=ir.codemp and rc.codfilial=ir.codfilial and rc.codrec=ir.codrec and "); sql.append( "ir.codemp=? and ir.codfilial=? and rc.codempcl=? and rc.codfilialcl=? and codcli=? "); sql.append("and ir.dtvencitrec between ? and ? "); PreparedStatement ps = getConn().prepareStatement(sql.toString()); int param = 1; ps.setInt(param++, codemprc); ps.setInt(param++, codfilialrc); ps.setInt(param++, codempcl); ps.setInt(param++, codfilialcl); ps.setInt(param++, codcli); ps.setDate(param++, Funcoes.dateToSQLDate(dtini)); ps.setDate(param++, Funcoes.dateToSQLDate(dtfim)); ResultSet rs = ps.executeQuery(); if (rs.next()) { // result[RESULT_RECEBER.TOTAL_VENDAS.ordinal()] = rs.getBigDecimal( // RESULT_RECEBER.TOTAL_VENDAS.name() ); result[RESULT_RECEBER.TOTAL_ABERTO.ordinal()] = rs.getBigDecimal(RESULT_RECEBER.TOTAL_ABERTO.name()); result[RESULT_RECEBER.TOTAL_ATRASO.ordinal()] = rs.getBigDecimal(RESULT_RECEBER.TOTAL_ATRASO.name()); result[RESULT_RECEBER.ATRASO_MEDIO.ordinal()] = rs.getBigDecimal(RESULT_RECEBER.ATRASO_MEDIO.name()); } rs.close(); ps.close(); getConn().commit(); } catch (SQLException e) { getConn().rollback(); throw new Exception(e.getMessage()); } return result; }
public CierreCaja getCierre() { // se crear un referencia al pool de conexiones // DataSource ds = DBCPDataSourceFactory.getDataSource("mysql"); Connection con = null; String sql = "select * from v_cierre_caja;"; // Statement stmt = null; CierreCaja unaCierre = new CierreCaja(); ResultSet res = null; boolean existe = false; try { con = Conexion.getPoolConexion().getConnection(); seleccionarCierre = con.prepareStatement(sql); res = seleccionarCierre.executeQuery(); while (res.next()) { existe = true; unaCierre.setNoFacturaInicio(res.getInt("factura_inicio")); unaCierre.setNoFacturaFinal(res.getInt("factura_ultima")); unaCierre.setEfectivo(res.getBigDecimal("total_efectivo")); unaCierre.setCredito(res.getBigDecimal("total_credito")); unaCierre.setTarjeta(res.getBigDecimal("total_tarjeta")); unaCierre.setIsv15(res.getBigDecimal("total_isv15")); unaCierre.setIsv18(res.getBigDecimal("total_isv18")); unaCierre.setTotal(res.getBigDecimal("total")); unaCierre.setUsuario(res.getString("usuario")); // .setTotal(res.getBigDecimal("total")); } } catch (SQLException e) { e.printStackTrace(); } finally { try { if (res != null) res.close(); if (seleccionarCierre != null) seleccionarCierre.close(); if (con != null) con.close(); } // fin de try catch (SQLException excepcionSql) { excepcionSql.printStackTrace(); // conexion.desconectar(); } // fin de catch } // fin de finally if (existe) { return unaCierre; } else return null; }
@Override public void parseResultQuery(ResultSet rs) throws SQLException { while (rs.next()) { cold = rs.getBigDecimal("cold"); hot = rs.getBigDecimal("hot"); light = rs.getBigDecimal("light"); sanitation = rs.getBigDecimal("sanitation"); door = rs.getBigDecimal("door"); } }
@Override public InvoiceData mapRow(final ResultSet rs, @SuppressWarnings("unused") final int rowNum) throws SQLException { Long id = rs.getLong("id"); Date invoiceDate = rs.getDate("invoiceDate"); BigDecimal invoiceAmount = rs.getBigDecimal("invoiceAmount"); BigDecimal dueAmount = rs.getBigDecimal("dueAmount"); Long billId = rs.getLong("billId"); return new InvoiceData(id, invoiceAmount, dueAmount, invoiceDate, billId); }
public static void loadIdentitySpecifier( Connection connection, IdentitySpecifier identitySpecifier, Column column) throws SQLException { final Table table = column.getTable(); final Schema schema = table.getSchema(); final Database database = ModelHelper.getDatabase(schema); final DatabaseDefinition databaseDefinition = RDBCorePlugin.getDefault().getDatabaseDefinitionRegistry().getDefinition(database); final DataModelElementFactory factory = databaseDefinition.getDataModelElementFactory(); String query = "Select INCREMENT,START,MAXVALUE,MINVALUE,CYCLE, CACHE " + //$NON-NLS-1$ " FROM SYSCAT.COLIDENTATTRIBUTES" + //$NON-NLS-1$ " WHERE TABSCHEMA='" + LUWUtil.getIdentifier(schema.getName()) + "'" + //$NON-NLS-1$ //$NON-NLS-2$ " AND TABNAME= '" + LUWUtil.getIdentifier(table.getName()) + "'" + //$NON-NLS-1$ //$NON-NLS-2$ " AND COLNAME ='" + LUWUtil.getIdentifier(column.getName()) + "'"; //$NON-NLS-1$ //$NON-NLS-2$ Statement s = connection.createStatement(); ResultSet r = s.executeQuery(query); try { while (r.next()) { DB2IdentitySpecifier identity = (DB2IdentitySpecifier) identitySpecifier; identity.setIncrement(r.getBigDecimal("INCREMENT").toBigInteger()); // $NON-NLS-1$ identity.setStartValue(r.getBigDecimal("START").toBigInteger()); // $NON-NLS-1$ identity.setMinimum(r.getBigDecimal("MINVALUE").toBigInteger()); // $NON-NLS-1$ identity.setMaximum(r.getBigDecimal("MAXVALUE").toBigInteger()); // $NON-NLS-1$ identity.setCache(r.getInt("CACHE")); // $NON-NLS-1$ if (r.getString("CYCLE").trim().equals("Y")) { // $NON-NLS-1$ //$NON-NLS-2$ identitySpecifier.setCycleOption(true); } else { identitySpecifier.setCycleOption(false); } } } catch (Exception e) { e.printStackTrace(); } r.close(); s.close(); }
@Override public InterestRatePeriodData mapRow( final ResultSet rs, @SuppressWarnings("unused") final int rowNum) throws SQLException { final Date fromDate = rs.getDate("linkedrateperiods_from_date"); final BigDecimal interestRate = rs.getBigDecimal("linkedrateperiods_interest_rate"); final boolean isDifferentialToBLR = rs.getBoolean("linkedrateperiods_is_differential_to_base_lending_rate"); final Date blrFromDate = rs.getDate("baserate_from_date"); final BigDecimal blrInterestRate = rs.getBigDecimal("baserate_interest_rate"); return new InterestRatePeriodData( fromDate, interestRate, isDifferentialToBLR, blrFromDate, blrInterestRate); }
public static BigDecimal getSupplierPaidAmountForGivenPeriod( Delegator delegator, String customTimePeriodId, String organizationPartyId) throws GenericEntityException, SQLException { BigDecimal supplierPaidAmount = BigDecimal.ZERO; String TIME_CONDITION = getTimeQueryCondition(delegator, customTimePeriodId); SUPPLIER_PAID_AMOUNT_QUERY = SUPPLIER_PAID_AMOUNT_QUERY + TIME_CONDITION; ResultSet resultSet = execute(SUPPLIER_PAID_AMOUNT_QUERY, delegator); while (resultSet.next()) { supplierPaidAmount = resultSet.getBigDecimal("amount") != null ? resultSet.getBigDecimal("amount") : BigDecimal.ZERO; } return supplierPaidAmount; }
public static BigDecimal getCustomerReceiptAmountForGivenPeriod( Delegator delegator, String customTimePeriodId, String organizationPartyId) throws GenericEntityException, SQLException { BigDecimal customerReceiptAmount = BigDecimal.ZERO; String TIME_CONDITION = getTimeQueryCondition(delegator, customTimePeriodId); RECEIPTS_FROM_CUSTOMER_QUERY = RECEIPTS_FROM_CUSTOMER_QUERY + TIME_CONDITION; ResultSet resultSet = execute(RECEIPTS_FROM_CUSTOMER_QUERY, delegator); while (resultSet.next()) { customerReceiptAmount = resultSet.getBigDecimal("totalAmount") != null ? resultSet.getBigDecimal("totalAmount") : BigDecimal.ZERO; } return customerReceiptAmount; }
public static BigDecimal getOutflowInAGivenPeriod( Delegator delegator, String customTimePeriodId, String organizationPartyId) throws GenericEntityException, SQLException { BigDecimal cashPaidAmount = BigDecimal.ZERO; String TIME_CONDITION = getTimeQueryCondition(delegator, customTimePeriodId); CASH_OR_BANK_PAID_AMOUNT_QUERY = CASH_OR_BANK_PAID_AMOUNT_QUERY + TIME_CONDITION; ResultSet resultSet = execute(CASH_OR_BANK_PAID_AMOUNT_QUERY, delegator); while (resultSet.next()) { cashPaidAmount = resultSet.getBigDecimal("amount") != null ? resultSet.getBigDecimal("amount") : BigDecimal.ZERO; } return cashPaidAmount; }
@Override public List<MaterialBean> buscarMateriales(int codTipoMaterial, String descripcion) throws Exception { Connection cn = MySqlDbConn.obtenerConexion(); CallableStatement st = cn.prepareCall("{ call pr_buscarMateriales(?, ?) }"); st.setInt(1, codTipoMaterial); st.setString(2, descripcion); ResultSet rs = st.executeQuery(); ArrayList<MaterialBean> arr = new ArrayList<MaterialBean>(); while (rs.next()) { MaterialBean material = new MaterialBean(); material.setStrCodMaterial(rs.getString("cod_mat")); material.setIntCodTipoMaterial(rs.getInt("cod_tip_mat")); material.setStrTipoMaterial(rs.getString("tip_mat")); material.setStrDescripMaterial(rs.getString("desc_mat")); material.setStrUnidadMedida(rs.getString("und_med_mat")); material.setBdPrecioUnitario(rs.getBigDecimal("prec_uni_mat")); arr.add(material); } rs.close(); cn.close(); return arr; }
/** * BankStmt - Payment. Update Transaction Amount when payment is selected * * @param ctx context * @param WindowNo window no * @param mTab tab * @param mField field * @param value value * @return null or error message */ public String payment( Properties ctx, int WindowNo, GridTab mTab, GridField mField, Object value) { Integer C_Payment_ID = (Integer) value; if (C_Payment_ID == null || C_Payment_ID.intValue() == 0) return ""; // BigDecimal stmt = (BigDecimal) mTab.getValue("StmtAmt"); if (stmt == null) stmt = Env.ZERO; String sql = "SELECT PayAmt FROM C_Payment_v WHERE C_Payment_ID=?"; // 1 PreparedStatement pstmt = null; ResultSet rs = null; try { pstmt = DB.prepareStatement(sql, null); pstmt.setInt(1, C_Payment_ID.intValue()); rs = pstmt.executeQuery(); if (rs.next()) { BigDecimal bd = rs.getBigDecimal(1); mTab.setValue("TrxAmt", bd); if (stmt.compareTo(Env.ZERO) == 0) mTab.setValue("StmtAmt", bd); } } catch (SQLException e) { log.log(Level.SEVERE, "BankStmt_Payment", e); return e.getLocalizedMessage(); } finally { DB.close(rs, pstmt); rs = null; pstmt = null; } // Recalculate Amounts amount(ctx, WindowNo, mTab, mField, value); return ""; } // payment
@Ignore("until DECIMAL is supported (no longer maps to double) (DRILL-????)") @Test public void test_getBigDecimal_handles_DECIMAL_2() throws SQLException { assertThat( testDataRow.getBigDecimal("C_DECIMAL_10.10"), equalTo(new BigDecimal("10.10"))); // ??????resolve }
public List<Product> getAllProducts() { List<Product> products = new ArrayList<Product>(); try { Statement stmt = con.createStatement(); String SQL = "select * from APP.PRODUCTS"; ResultSet rs = stmt.executeQuery(SQL); while (rs.next()) { int number = rs.getInt("Number"); String name = rs.getString("Name"); BigDecimal price = rs.getBigDecimal("Price").setScale(2); int quantity = rs.getInt("Quantity"); String category = rs.getString("Category"); String car = rs.getString("Car"); String image = rs.getString("Image"); Product p = new Product(number, name, price, quantity, category, car, image); products.add(p); } } catch (SQLException err) { System.out.println(err.getMessage()); } return products; }
@Override public AcademicKPI mapRow(ResultSet rs, int rowNum) throws SQLException { AcademicKPI domain = new AcademicKPI(); domain.setAcademicKPIId(rs.getLong("academic_kpi_id")); domain.setName(rs.getString("name")); domain.setCode(rs.getString("code")); domain.setWorkTypeCode(rs.getString("work_type_code")); domain.setDescription(rs.getString("description")); domain.setStatus(rs.getString("status")); domain.setAcademicYear(rs.getString("academic_year")); domain.setMark(rs.getBigDecimal("mark")); domain.setUnitCode(rs.getString("unit_code")); domain.setMultiplyValue(rs.getString("rule_code")); domain.setOrderNo(rs.getString("order_no")); domain.setSpecialP1(rs.getString("special_p1")); domain.setSpecialP2(rs.getString("special_p2")); domain.setSpecialP3(rs.getString("special_p3")); domain.setSpecialP4(rs.getString("special_p4")); domain.setTotalStudentFrom(rs.getString("total_student_from")); domain.setTotalStudentTo(rs.getString("total_student_to")); domain.setFromRegis(rs.getString("from_reg")); // logger.info(" ###### Multiply Value:"+rs.getString("rule_code")); try { domain.setUnitDesc( schoolUtil.getUnitDescMyCode(rs.getString("unit_code"), rs.getString("academic_year"))); } catch (org.springframework.dao.EmptyResultDataAccessException ex) { ex.printStackTrace(); } return domain; }
@Override public SavingsDueData mapRow(ResultSet rs, @SuppressWarnings("unused") int rowNum) throws SQLException { final Long savingsId = rs.getLong("savingsId"); final String accountId = rs.getString("accountId"); final Integer accountStatusId = JdbcSupport.getInteger(rs, "accountStatusId"); final String productName = rs.getString("productShortName"); final Long productId = rs.getLong("productId"); final BigDecimal dueAmount = rs.getBigDecimal("dueAmount"); final String currencyCode = rs.getString("currencyCode"); final String currencyName = rs.getString("currencyName"); final String currencyNameCode = rs.getString("currencyNameCode"); final String currencyDisplaySymbol = rs.getString("currencyDisplaySymbol"); final Integer currencyDigits = JdbcSupport.getInteger(rs, "currencyDigits"); final Integer inMultiplesOf = JdbcSupport.getInteger(rs, "inMultiplesOf"); // currency final CurrencyData currency = new CurrencyData( currencyCode, currencyName, currencyDigits, inMultiplesOf, currencyDisplaySymbol, currencyNameCode); return SavingsDueData.instance( savingsId, accountId, accountStatusId, productName, productId, currency, dueAmount); }
/** * Method to fetch a single instance of a given element. * * @param personId of the user * @param entryId of the element @ if this element can not be located @ if there are any fetch * problems. */ public ElementDataObject findElementInstance(String personId, BigDecimal entryId) { Committee committee = new Committee(); Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; try { conn = getDataSource().getConnection(); ps = conn.prepareStatement(SELECT_QUERY_SINGLE); ps.setString(1, personId); ps.setBigDecimal(2, entryId); rs = ps.executeQuery(); if (rs.next()) { committee.setPersonId(rs.getString("PERSON_ID")); committee.setEntryId(rs.getBigDecimal("ENTRY_ID")); committee.setDateCreated(rs.getTimestamp("DATE_CREATED")); committee.setModifiedDate(rs.getTimestamp("MODIFIED_DATE")); committee.setEntryName(rs.getString("ENTRY_NAME")); committee.setFromDate(rs.getTimestamp("FROM_DATE")); committee.setToDate(rs.getTimestamp("TO_DATE")); committee.setDescription(rs.getString("DESCRIPTION")); } else { return null; } } catch (SQLException sqle) { logService.error("Exception caught when attempting to query name table.", sqle); throw new DataAccessException(sqle); } finally { close(conn, ps, rs); } return committee; }
private List getReverseGlavList(GlAccvouchObj glavInput) throws Exception { List pingZhangGlavList = new ArrayList(); Connection conn = null; try { conn = MySqlUtil.getConnection(SystemConstant.DEFAULT_DB); ResultSet rst = conn.createStatement() .executeQuery( "select zzAccSubReverse ccode,SUM(md) md,SUM(mc) mc from gl_accvouch group by zzAccSubReverse"); while (rst.next()) { GlAccvouchObj o = createGlAccvouchObj(glavInput); o.setCcode(rst.getString("ccode")); o.setMd(rst.getBigDecimal("mc")); // 注意这个地方mc,md要反过来,因为要平账 o.setMc(rst.getBigDecimal("md")); pingZhangGlavList.add(o); } } catch (Exception e) { throw e; } finally { MySqlUtil.closeConnection(conn); } return pingZhangGlavList; }
public void testQueryForObjectWithBigDecimal() throws Exception { String sql = "SELECT AGE FROM CUSTMR WHERE ID = 3"; mockResultSetMetaData.getColumnCount(); ctrlResultSetMetaData.setReturnValue(1); mockResultSet.getMetaData(); ctrlResultSet.setReturnValue(mockResultSetMetaData); mockResultSet.next(); ctrlResultSet.setReturnValue(true); mockResultSet.getBigDecimal(1); ctrlResultSet.setReturnValue(new BigDecimal(22.5)); mockResultSet.next(); ctrlResultSet.setReturnValue(false); mockResultSet.close(); ctrlResultSet.setVoidCallable(); mockStatement.executeQuery(sql); ctrlStatement.setReturnValue(mockResultSet); mockStatement.getWarnings(); ctrlStatement.setReturnValue(null); mockStatement.close(); ctrlStatement.setVoidCallable(); mockConnection.createStatement(); ctrlConnection.setReturnValue(mockStatement); replay(); JdbcTemplate template = new JdbcTemplate(mockDataSource); assertEquals(new BigDecimal(22.5), template.queryForObject(sql, BigDecimal.class)); }
/*---------------------------------- @Author: Jorge Vidal - Disytel @Fecha: 05/09/2006 @Comentario: Actualiza el total que corresponde a las lineas @Parametros: -------------------------------------------*/ public void checkLines() { StringBuffer sql = new StringBuffer( "SELECT SUM(PAYMENT_AMT) FROM M_BOLETADEPOSITOLINE WHERE M_BOLETADEPOSITO_ID=? "); PreparedStatement pstmt = null; try { pstmt = DB.prepareStatement(sql.toString(), get_TrxName()); pstmt.setInt(1, getM_BoletaDeposito_ID()); ResultSet rs = pstmt.executeQuery(); if (rs.next()) { BigDecimal total = rs.getBigDecimal(1); if (!(total.equals(getGrandTotal()))) { setGrandTotal(total); save(); } } rs.close(); pstmt.close(); pstmt = null; } catch (Exception e) { log.log(Level.SEVERE, "CheckLines - " + sql, e); } finally { try { if (pstmt != null) pstmt.close(); } catch (Exception e) { } pstmt = null; } }
public BigDecimal getItemStockAvailable(int itemid, int unitid) { PreparedStatement stmt; ResultSet rs; BigDecimal bg = null; String str = "SELECT (a.total_qty/u.unit_relative_quantity) FROM department_store_stock a,item_unit u WHERE a.item_id = ? AND u.unit_id = ? "; try { initConnection(); stmt = conn.prepareStatement(str); stmt.setInt(1, itemid); // System.out.println(unitid); stmt.setInt(2, unitid); rs = stmt.executeQuery(); while (rs.next()) { bg = rs.getBigDecimal(1).setScale(2, RoundingMode.HALF_UP); } } catch (SQLException se) { DisplayMessages.displayError( null, se.getMessage() + "from getItemStockAvailable " + getClass().getName(), "SQL Error"); } finally { closeConnection(); } return bg; }
/** @throws Exception If failed. */ public void testBigDecimal() throws Exception { ResultSet rs = stmt.executeQuery(SQL); int cnt = 0; while (rs.next()) { if (cnt == 0) { assert rs.getBigDecimal("bigVal").intValue() == 1; assert rs.getBigDecimal(9).intValue() == 1; } cnt++; } assert cnt == 1; }
/** * Get PO Costs in Currency of AcctSchema * * @param as Account Schema * @return Unit PO Cost */ private BigDecimal getPOCost(MAcctSchema as) { BigDecimal retValue = null; // Uses PO Date String sql = "SELECT currencyConvert(ol.PriceActual, o.C_Currency_ID, ?, o.DateOrdered, o.C_ConversionType_ID, ?, ?) " + "FROM C_OrderLine ol" + " INNER JOIN M_InOutLine iol ON (iol.C_OrderLine_ID=ol.C_OrderLine_ID)" + " INNER JOIN C_Order o ON (o.C_Order_ID=ol.C_Order_ID) " + "WHERE iol.M_InOutLine_ID=?"; PreparedStatement pstmt = null; ResultSet rs = null; try { pstmt = DB.prepareStatement(sql, getTrxName()); pstmt.setInt(1, as.getC_Currency_ID()); pstmt.setInt(2, getAD_Client_ID()); pstmt.setInt(3, getAD_Org_ID()); pstmt.setInt(4, m_issue.getM_InOutLine_ID()); rs = pstmt.executeQuery(); if (rs.next()) { retValue = rs.getBigDecimal(1); log.fine("POCost = " + retValue); } else log.warning("Not found for M_InOutLine_ID=" + m_issue.getM_InOutLine_ID()); } catch (Exception e) { log.log(Level.SEVERE, sql, e); } finally { DB.close(rs, pstmt); pstmt = null; rs = null; } return retValue; } // getPOCost();
// in this implementation, there is only one order per customer // the data model however allows for multiple orders per customer public CustomerOrder findByCustomer(int customerID) { Customer customer = customerFacade.find(customerID); CustomerOrder order = new CustomerOrder(); Connection con = util.getConnection(); String sql = "select * from customer_order where customer_id=?"; PreparedStatement statement = null; try { statement = con.prepareStatement(sql); statement.setInt(1, customerID); ResultSet rs = statement.executeQuery(); while (rs.next()) { order.setId(rs.getInt(1)); order.setAmount(rs.getBigDecimal(2)); order.setDateCreated(rs.getDate(3)); order.setConfirmationNumber(rs.getInt(4)); order.setCustomer(customer); } } catch (SQLException e) { e.printStackTrace(); } finally { if (statement != null) { try { statement.close(); } catch (SQLException e) { e.printStackTrace(); } } } return order; // return (CustomerOrder) // em.createNamedQuery("CustomerOrder.findByCustomer").setParameter("customer", // customer).getSingleResult(); }
/** PaySelect changed - load Bank */ public void loadPaySelectInfo(int C_PaySelection_ID) { // load Banks from PaySelectLine m_C_BankAccount_ID = -1; String sql = "SELECT ps.C_BankAccount_ID, b.Name || ' ' || ba.AccountNo," // 1..2 + " c.ISO_Code, CurrentBalance, ba.PaymentExportClass " // 3..5 + "FROM C_PaySelection ps" + " INNER JOIN C_BankAccount ba ON (ps.C_BankAccount_ID=ba.C_BankAccount_ID)" + " INNER JOIN C_Bank b ON (ba.C_Bank_ID=b.C_Bank_ID)" + " INNER JOIN C_Currency c ON (ba.C_Currency_ID=c.C_Currency_ID) " + "WHERE ps.C_PaySelection_ID=? AND ps.Processed='Y' AND ba.IsActive='Y'"; try { PreparedStatement pstmt = DB.prepareStatement(sql, null); pstmt.setInt(1, C_PaySelection_ID); ResultSet rs = pstmt.executeQuery(); if (rs.next()) { m_C_BankAccount_ID = rs.getInt(1); bank = rs.getString(2); currency = rs.getString(3); balance = rs.getBigDecimal(4); m_PaymentExportClass = rs.getString(5); } else { m_C_BankAccount_ID = -1; bank = ""; currency = ""; balance = Env.ZERO; m_PaymentExportClass = null; log.log(Level.SEVERE, "No active BankAccount for C_PaySelection_ID=" + C_PaySelection_ID); } rs.close(); pstmt.close(); } catch (SQLException e) { log.log(Level.SEVERE, sql, e); } } // loadPaySelectInfo
private ServicePrice constructPriceEntity(ResultSet rs) throws SQLException { ServicePrice servicePrice = new ServicePrice(); servicePrice.setPrice(rs.getBigDecimal("price")); servicePrice.setServiceId(rs.getInt("service_id")); servicePrice.setDate(rs.getDate("date").toLocalDate()); return servicePrice; }
/** * CSV出力用モデルを生成 * * @param rs ResultSet * @return created TcdCsvModel * @throws SQLException SQL実行例外 */ private TcdCsvModel __getTcdCsvFromRs(ResultSet rs) throws SQLException { TcdCsvModel bean = new TcdCsvModel(); bean.setUsrSid(rs.getInt("USR_SID")); bean.setUserName(rs.getString("USI_SEI") + " " + rs.getString("USI_MEI")); bean.setTcdDate(UDate.getInstanceTimestamp(rs.getTimestamp("TCD_DATE"))); bean.setTcdIntime(rs.getTime("TCD_INTIME")); bean.setTcdOuttime(rs.getTime("TCD_OUTTIME")); bean.setTcdStrikeIntime(rs.getTime("TCD_STRIKE_INTIME")); bean.setTcdStrikeOuttime(rs.getTime("TCD_STRIKE_OUTTIME")); bean.setTcdBiko(rs.getString("TCD_BIKO")); bean.setTcdStatus(rs.getInt("TCD_STATUS")); bean.setTcdHolkbn(rs.getInt("TCD_HOLKBN")); bean.setTcdHolother(rs.getString("TCD_HOLOTHER")); bean.setTcdHolcnt(rs.getBigDecimal("TCD_HOLCNT")); bean.setTcdChkkbn(rs.getInt("TCD_CHKKBN")); bean.setTcdSoukbn(rs.getInt("TCD_SOUKBN")); bean.setTcdLockFlg(rs.getInt("TCD_LOCK_FLG")); bean.setTcdAuid(rs.getInt("TCD_AUID")); bean.setTcdAdate(UDate.getInstanceTimestamp(rs.getTimestamp("TCD_ADATE"))); bean.setTcdEuid(rs.getInt("TCD_EUID")); bean.setTcdEdate(UDate.getInstanceTimestamp(rs.getTimestamp("TCD_EDATE"))); return bean; }