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;
}
