Examples with DBException org.adempiere.exceptions.DBException used on opensource projects

Example 71 with DBException

use of org.adempiere.exceptions.DBException in project adempiere by adempiere.

the class OrderReceiptIssue method lotes.

/**
	 * Adds Attribute Set Instances Quantities to table. Extension to
	 * {@link #executeQuery()}
	 * 
	 * @return how many lines were added
	 */
private int lotes(int row, IDColumn id, int Warehouse_ID, int M_Product_ID, BigDecimal qtyRequired, BigDecimal qtyToDelivery, IMiniTable issue) {
    int linesNo = 0;
    BigDecimal qtyRequiredActual = qtyRequired;
    final String sql = "SELECT " + "s.M_Product_ID , s.QtyOnHand, s.M_AttributeSetInstance_ID" + ", p.Name, masi.Description, l.M_Locator_ID , l.Value, w.Value, w.M_warehouse_ID,p.Value" + "  FROM M_Storage s " + " INNER JOIN M_Product p ON (s.M_Product_ID = p.M_Product_ID) " + " INNER JOIN C_UOM u ON (u.C_UOM_ID = p.C_UOM_ID) " + " INNER JOIN M_AttributeSetInstance masi ON (masi.M_AttributeSetInstance_ID = s.M_AttributeSetInstance_ID) " + " INNER JOIN M_Warehouse w ON (w.M_Warehouse_ID = ?) " + " INNER JOIN M_Locator l ON(l.M_Warehouse_ID=w.M_Warehouse_ID and s.M_Locator_ID=l.M_Locator_ID) " + " WHERE s.M_Product_ID = ? and s.QtyOnHand > 0 " + " and s.M_AttributeSetInstance_ID <> 0 " + " ORDER BY s.Created ";
    // Execute
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        pstmt = DB.prepareStatement(sql, null);
        pstmt.setInt(1, Warehouse_ID);
        pstmt.setInt(2, M_Product_ID);
        rs = pstmt.executeQuery();
        while (rs.next()) {
            issue.setRowCount(row + 1);
            // Qty On Hand
            final BigDecimal qtyOnHand = rs.getBigDecimal(2);
            // ID/M_AttributeSetInstance_ID
            IDColumn id1 = new IDColumn(rs.getInt(3));
            id1.setSelected(false);
            // issue.setRowSelectionAllowed(true);
            issue.setValueAt(id1, row, 0);
            // Product
            KeyNamePair productkey = new KeyNamePair(rs.getInt(1), rs.getString(4));
            issue.setValueAt(productkey, row, 3);
            // QtyOnHand
            issue.setValueAt(qtyOnHand, row, 10);
            // ASI
            issue.setValueAt(rs.getString(5), row, 5);
            // Locator
            KeyNamePair locatorKey = new KeyNamePair(rs.getInt(6), rs.getString(7));
            issue.setValueAt(locatorKey, row, 13);
            // Warehouse
            KeyNamePair m_warehousekey = new KeyNamePair(rs.getInt(9), rs.getString(8));
            issue.setValueAt(m_warehousekey, row, 14);
            // QtyRequired
            issue.setValueAt(Env.ZERO, row, 6);
            // QtyToDelivery
            issue.setValueAt(Env.ZERO, row, 8);
            // Srcap
            issue.setValueAt(Env.ZERO, row, 9);
            // Qty Required:
            if (qtyRequiredActual.compareTo(qtyOnHand) < 0) {
                issue.setValueAt(qtyRequiredActual.signum() > 0 ? qtyRequiredActual : Env.ZERO, row, 6);
            } else {
                issue.setValueAt(qtyOnHand, row, 6);
            }
            qtyRequiredActual = qtyRequiredActual.subtract(qtyOnHand);
            linesNo++;
            row++;
        }
    } catch (SQLException e) {
        throw new DBException(e);
    } finally {
        DB.close(rs, pstmt);
        rs = null;
        pstmt = null;
    }
    return linesNo;
}

Example 72 with DBException

use of org.adempiere.exceptions.DBException in project adempiere by adempiere.

the class MInvoice method getAllocatedAmt.

//	setM_PriceList_ID
/**
	 * 	Get Allocated Amt in Invoice Currency
	 *	@return pos/neg amount or null
	 */
public BigDecimal getAllocatedAmt() {
    BigDecimal retValue = null;
    String sql = "SELECT SUM(currencyConvert(al.Amount+al.DiscountAmt+al.WriteOffAmt," + "ah.C_Currency_ID, i.C_Currency_ID,ah.DateTrx,COALESCE(i.C_ConversionType_ID,0), al.AD_Client_ID,al.AD_Org_ID)) " + "FROM C_AllocationLine al" + " INNER JOIN C_AllocationHdr ah ON (al.C_AllocationHdr_ID=ah.C_AllocationHdr_ID)" + " INNER JOIN C_Invoice i ON (al.C_Invoice_ID=i.C_Invoice_ID) " + "WHERE al.C_Invoice_ID=?" + " AND ah.IsActive='Y' AND al.IsActive='Y'";
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        pstmt = DB.prepareStatement(sql, get_TrxName());
        pstmt.setInt(1, getC_Invoice_ID());
        rs = pstmt.executeQuery();
        if (rs.next()) {
            retValue = rs.getBigDecimal(1);
        }
        rs.close();
        pstmt.close();
        pstmt = null;
    } catch (SQLException e) {
        throw new DBException(e, sql);
    } finally {
        DB.close(rs, pstmt);
        rs = null;
        pstmt = null;
    }
    //	? ROUND(NVL(v_AllocatedAmt,0), 2);
    return retValue;
}

Example 73 with DBException

use of org.adempiere.exceptions.DBException in project adempiere by adempiere.

the class MLanguage method addTable.

//	deleteTable
/**
	 * 	Add Translation to table
	 *	@param tableName table name
	 *	@return number of records inserted
	 */
private int addTable(String tableName) {
    String baseTable = tableName.substring(0, tableName.length() - 4);
    String sql = "SELECT c.ColumnName " + "FROM AD_Column c" + " INNER JOIN AD_Table t ON (c.AD_Table_ID=t.AD_Table_ID) " + "WHERE t.TableName=?" + "  AND c.IsTranslated='Y' AND c.IsActive='Y' " + "ORDER BY c.ColumnName";
    ArrayList<String> columns = new ArrayList<String>(5);
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        pstmt = DB.prepareStatement(sql, null);
        pstmt.setString(1, baseTable);
        rs = pstmt.executeQuery();
        while (rs.next()) {
            columns.add(rs.getString(1));
        }
    } catch (SQLException e) {
        throw new DBException(e, sql);
    } finally {
        DB.close(rs, pstmt);
        rs = null;
        pstmt = null;
    }
    //	Columns
    if (columns.size() == 0) {
        log.log(Level.SEVERE, "No Columns found for " + baseTable);
        return 0;
    }
    StringBuffer cols = new StringBuffer();
    for (int i = 0; i < columns.size(); i++) cols.append(",").append(columns.get(i));
    //	Insert Statement
    int AD_User_ID = Env.getAD_User_ID(getCtx());
    String keyColumn = baseTable + "_ID";
    String insert = "INSERT INTO " + tableName + "(AD_Language,IsTranslated, AD_Client_ID,AD_Org_ID, " + "Created,Updated, " + "Createdby,UpdatedBy, " + keyColumn + cols + ") " + "SELECT '" + getAD_Language() + "','N', AD_Client_ID,AD_Org_ID, SYSDATE, SYSDATE ," + AD_User_ID + "," + AD_User_ID + ", " + keyColumn + cols + " FROM " + baseTable + " WHERE " + keyColumn + " NOT IN (SELECT " + keyColumn + " FROM " + tableName + " WHERE AD_Language='" + getAD_Language() + "')";
    //	+ " WHERE (" + keyColumn + ",'" + getAD_Language()+ "') NOT IN (SELECT " 
    //		+ keyColumn + ",AD_Language FROM " + tableName + ")";
    int no = DB.executeUpdateEx(insert, null, get_TrxName());
    log.fine(tableName + " #" + no);
    return no;
}

Example 74 with DBException

use of org.adempiere.exceptions.DBException in project adempiere by adempiere.

the class MRoleIncluded method hasLoop.

/**
	 * Check if there is a loop in the tree defined in given table
	 * @param tableName
	 * @param idColumnName Node_ID column name
	 * @param parentIdColumnName Parent_ID column name
	 * @param nodeId current Node_ID
	 * @param trace current tree path (optional)
	 * @param trxName transaction name
	 * @return true if loop detected. If you specified not null trace, you will have in that list the IDs from the loop
	 */
// TODO: refactor this method and move into org.compiere.util.DB class because it's general and usefull of others too
private static boolean hasLoop(String tableName, String idColumnName, String parentIdColumnName, int nodeId, List<Integer> trace, String trxName) {
    final List<Integer> trace2;
    if (trace == null) {
        trace2 = new ArrayList<Integer>(10);
    } else {
        trace2 = new ArrayList<Integer>(trace);
    }
    trace2.add(nodeId);
    //
    final String sql = "SELECT " + idColumnName + "," + parentIdColumnName + " FROM " + tableName + " WHERE " + parentIdColumnName + "=?";
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        pstmt = DB.prepareStatement(sql, trxName);
        pstmt.setInt(1, nodeId);
        rs = pstmt.executeQuery();
        while (rs.next()) {
            final int childId = rs.getInt(1);
            if (trace2.contains(childId)) {
                trace.clear();
                trace.addAll(trace2);
                trace.add(childId);
                return true;
            }
            if (hasLoop(tableName, idColumnName, parentIdColumnName, childId, trace2, trxName)) {
                trace.clear();
                trace.addAll(trace2);
                return true;
            }
        }
    } catch (SQLException e) {
        throw new DBException(e, sql);
    } finally {
        DB.close(rs, pstmt);
        rs = null;
        pstmt = null;
    }
    //
    return false;
}

Example 75 with DBException

use of org.adempiere.exceptions.DBException in project adempiere by adempiere.

the class InventoryTestException method assertStorage.

private void assertStorage(MMDocument doc, String trxName) {
    MLocator locator = InventoryUtil.getCreateLocator(-1, doc.LocatorValue, doc.LocatorValue);
    MProduct product = InventoryUtil.getCreateProduct(doc.ProductValue, null);
    int M_ASI_ID = -1;
    if (!Util.isEmpty(doc.ASI, true)) {
        M_ASI_ID = doc.scenario.getM_ASI_ID(doc.ASI);
    }
    ArrayList<Object> params = new ArrayList<Object>();
    String sql = "SELECT" + " COALESCE(SUM(QtyOnHand),0)" + ",COALESCE(SUM(QtyReserved),0)" + ",COALESCE(SUM(QtyOrdered),0)" + " FROM M_Storage" + " WHERE M_Locator_ID=? AND M_Product_ID=?";
    params.add(locator.get_ID());
    params.add(product.get_ID());
    if (M_ASI_ID >= 0) {
        sql += " AND " + MStorage.COLUMNNAME_M_AttributeSetInstance_ID + "=?";
        params.add(M_ASI_ID);
    }
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    BigDecimal qtyOnHand = Env.ZERO;
    BigDecimal qtyOrdered = Env.ZERO;
    BigDecimal qtyReserved = Env.ZERO;
    try {
        pstmt = DB.prepareStatement(sql, trxName);
        DB.setParameters(pstmt, params);
        rs = pstmt.executeQuery();
        if (rs.next()) {
            qtyOnHand = rs.getBigDecimal(1);
            qtyReserved = rs.getBigDecimal(2);
            qtyOrdered = rs.getBigDecimal(3);
        }
    } catch (SQLException e) {
        throw new DBException(e, sql);
    } finally {
        DB.close(rs, pstmt);
        rs = null;
        pstmt = null;
    }
    //
    //
    assertEquals("QtyOnHand not match " + doc, doc.Qty, qtyOnHand);
    assertEquals("QtyReserved not match " + doc, doc.QtyReserved, qtyReserved);
    assertEquals("QtyOrdered not match " + doc, doc.QtyOrdered, qtyOrdered);
}