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

Example 31 with DBException

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

the class DB method getKeyNamePairs.

/**
	 * Get Array of KeyNamePair items.
	 * <pre> Example:
	 * String sql = "SELECT C_City_ID, Name FROM C_City WHERE C_City_ID=?";
	 * KeyNamePair[] list = DB.getKeyNamePairs(sql, false, params);
	 * </pre>
	 * @param sql SELECT ID_Column, Name_Column FROM ...
	 * @param optional if {@link ValueNamePair#EMPTY} is added
	 * @param params query parameters
	 * @return array of {@link KeyNamePair} or empty array
     * @throws DBException if there is any SQLException
	 */
public static KeyNamePair[] getKeyNamePairs(String sql, boolean optional, List<Object> params) {
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    ArrayList<KeyNamePair> list = new ArrayList<KeyNamePair>();
    if (optional) {
        list.add(KeyNamePair.EMPTY);
    }
    try {
        pstmt = DB.prepareStatement(sql, null);
        setParameters(pstmt, params);
        rs = pstmt.executeQuery();
        while (rs.next()) {
            list.add(new KeyNamePair(rs.getInt(1), rs.getString(2)));
        }
    } catch (SQLException e) {
        throw new DBException(e, sql);
    } finally {
        close(rs, pstmt);
        rs = null;
        pstmt = null;
    }
    return list.toArray(new KeyNamePair[list.size()]);
}

Example 32 with DBException

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

the class WSearchEditorAutoComplete method updateListData.

@Override
protected boolean updateListData() {
    // clearing list
    removeAllItems();
    setDict(null);
    setDescription(null);
    //
    final String search = getSearchText();
    Object userObject = getUserOject();
    if (userObject != null && !isMatching(userObject, search)) {
        setUserObject(null);
    }
    //
    final ArrayList<Object> list = new ArrayList<Object>();
    boolean truncated = false;
    //
    // Load list from database
    final ArrayList<Object> params = new ArrayList<Object>();
    final String sql = getSelectSQL(search, params);
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        pstmt = DB.prepareStatement(sql, null);
        DB.setParameters(pstmt, params);
        rs = pstmt.executeQuery();
        int i = 0;
        while (rs.next()) {
            if (i > 0 && i > m_maxItems) {
                list.add(ITEM_More);
                truncated = true;
                break;
            }
            Object o = fetchUserObject(rs);
            list.add(o);
            i++;
        }
    } catch (SQLException e) {
        throw new DBException(e, sql.toString());
    } finally {
        DB.close(rs, pstmt);
        rs = null;
        pstmt = null;
    }
    // if there is no items on the list return false, to not show the pop-up
    if (list.isEmpty()) {
        setStyle("background:red");
        return false;
    }
    // If the list has only one item, but that item is not equals with
    // return false to not show any popup
    userObject = getUserOject();
    if (!truncated && list.size() == 1 && userObject != null && list.get(0).equals(userObject)) {
        log.finest("nothing to do 1");
        return false;
    }
    // if first list item matched then select it
    if (isMatching(list.get(0), search)) {
        setUserObject(list.get(0));
        return true;
    }
    // List updated, show we need to show the pop-up
    setStyle(defaultStyle);
    return true;
}

Example 33 with DBException

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

the class MChartDatasource method addData.

public void addData(MChart parent) {
    String value = getValueColumn();
    String category;
    String unit = "D";
    if (!parent.isTimeSeries())
        category = getCategoryColumn();
    else {
        if (parent.getTimeUnit().equals(MChart.TIMEUNIT_Week)) {
            unit = "W";
        } else if (parent.getTimeUnit().equals(MChart.TIMEUNIT_Month)) {
            unit = "MM";
        } else if (parent.getTimeUnit().equals(MChart.TIMEUNIT_Quarter)) {
            unit = "Q";
        } else if (parent.getTimeUnit().equals(MChart.TIMEUNIT_Year)) {
            unit = "Y";
        }
        category = " TRUNC(" + getDateColumn() + ", '" + unit + "') ";
    }
    String series = DB.TO_STRING(getName());
    boolean hasSeries = false;
    if (getSeriesColumn() != null) {
        series = getSeriesColumn();
        hasSeries = true;
    }
    String where = getWhereClause();
    if (!Util.isEmpty(where)) {
        where = Env.parseContext(getCtx(), parent.getWindowNo(), where, true);
    }
    boolean hasWhere = false;
    String sql = "SELECT " + value + ", " + category + ", " + series + " FROM " + getFromClause();
    if (!Util.isEmpty(where)) {
        sql += " WHERE " + where;
        hasWhere = true;
    }
    Date currentDate = Env.getContextAsDate(getCtx(), "#Date");
    Date startDate = null;
    Date endDate = null;
    int scope = parent.getTimeScope();
    int offset = getTimeOffset();
    if (parent.isTimeSeries() && scope != 0) {
        offset += -scope;
        startDate = increment(currentDate, parent.getTimeUnit(), offset);
        endDate = increment(startDate, parent.getTimeUnit(), scope);
    }
    if (startDate != null && endDate != null) {
        sql += hasWhere ? " AND " : " WHERE ";
        sql += category + ">=TRUNC(" + DB.TO_DATE(new Timestamp(startDate.getTime())) + ", '" + unit + "') AND ";
        sql += category + "<=TRUNC(" + DB.TO_DATE(new Timestamp(endDate.getTime())) + ", '" + unit + "') ";
    }
    MRole role = MRole.getDefault(getCtx(), false);
    sql = role.addAccessSQL(sql, null, true, false);
    if (hasSeries)
        sql += " GROUP BY " + series + ", " + category + " ORDER BY " + series + ", " + category;
    else
        sql += " GROUP BY " + category + " ORDER BY " + category;
    log.log(Level.FINE, sql);
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    TimeSeries tseries = null;
    Dataset dataset = parent.getDataset();
    try {
        pstmt = DB.prepareStatement(sql, null);
        rs = pstmt.executeQuery();
        while (rs.next()) {
            String key = rs.getString(2);
            String seriesName = rs.getString(3);
            if (seriesName == null)
                seriesName = getName();
            String queryWhere = "";
            if (hasWhere)
                queryWhere += where + " AND ";
            queryWhere += series + " = " + DB.TO_STRING(seriesName) + " AND " + category + " = ";
            if (parent.isTimeSeries() && dataset instanceof TimeSeriesCollection) {
                if (tseries == null || !tseries.getKey().equals(seriesName)) {
                    if (tseries != null)
                        ((TimeSeriesCollection) dataset).addSeries(tseries);
                    tseries = new TimeSeries(seriesName);
                }
                Date date = rs.getDate(2);
                RegularTimePeriod period = null;
                if (parent.getTimeUnit().equals(MChart.TIMEUNIT_Day))
                    period = new Day(date);
                else if (parent.getTimeUnit().equals(MChart.TIMEUNIT_Week))
                    period = new Week(date);
                else if (parent.getTimeUnit().equals(MChart.TIMEUNIT_Month))
                    period = new Month(date);
                else if (parent.getTimeUnit().equals(MChart.TIMEUNIT_Quarter))
                    period = new Quarter(date);
                else if (parent.getTimeUnit().equals(MChart.TIMEUNIT_Year))
                    period = new Year(date);
                tseries.add(period, rs.getBigDecimal(1));
                key = period.toString();
                queryWhere += DB.TO_DATE(new Timestamp(date.getTime()));
            } else {
                queryWhere += DB.TO_STRING(key);
            }
            MQuery query = new MQuery(getAD_Table_ID());
            String keyCol = MTable.get(getCtx(), getAD_Table_ID()).getKeyColumns()[0];
            String whereClause = keyCol + " IN (SELECT " + getKeyColumn() + " FROM " + getFromClause() + " WHERE " + queryWhere + " )";
            query.addRestriction(whereClause.toString());
            query.setRecordCount(1);
            HashMap<String, MQuery> map = parent.getQueries();
            if (dataset instanceof DefaultPieDataset) {
                ((DefaultPieDataset) dataset).setValue(key, rs.getBigDecimal(1));
                map.put(key, query);
            } else if (dataset instanceof DefaultCategoryDataset) {
                ((DefaultCategoryDataset) dataset).addValue(rs.getBigDecimal(1), seriesName, key);
                map.put(seriesName + "__" + key, query);
            } else if (dataset instanceof TimeSeriesCollection) {
                map.put(seriesName + "__" + key, query);
            }
        }
    } catch (SQLException e) {
        throw new DBException(e, sql);
    } finally {
        DB.close(rs, pstmt);
        rs = null;
        pstmt = null;
    }
    if (tseries != null)
        ((TimeSeriesCollection) dataset).addSeries(tseries);
}

Example 34 with DBException

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

the class MChartDatasource method getZoomQuery.

public MQuery getZoomQuery(MChart parent, String value, String category2) {
    MQuery query = new MQuery(getAD_Table_ID());
    String category;
    if (!parent.isTimeSeries())
        category = getCategoryColumn();
    else {
        String unit = "D";
        if (parent.getTimeUnit().equals(MChart.TIMEUNIT_Week))
            unit = "W";
        else if (parent.getTimeUnit().equals(MChart.TIMEUNIT_Month))
            unit = "MM";
        else if (parent.getTimeUnit().equals(MChart.TIMEUNIT_Quarter))
            unit = "Q";
        else if (parent.getTimeUnit().equals(MChart.TIMEUNIT_Year))
            unit = "Y";
        category = " TRUNC(" + getDateColumn() + ", '" + unit + "') ";
    }
    String where = getWhereClause();
    if (!Util.isEmpty(where)) {
        where = Env.parseContext(Env.getCtx(), parent.getWindowNo(), where, true);
    }
    String series = DB.TO_STRING(getName());
    if (getSeriesColumn() != null)
        series = getSeriesColumn();
    String sql = "SELECT " + getKeyColumn() + "," + category + "," + series + " FROM " + getFromClause();
    if (!Util.isEmpty(where)) {
        sql += " WHERE " + where;
    }
    sql += " GROUP BY " + series + ", " + category + "," + getKeyColumn() + " ORDER BY " + series + ", " + category + "," + getKeyColumn();
    StringBuffer includedIds = new StringBuffer();
    //	Execute
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        pstmt = DB.prepareStatement(sql, null);
        rs = pstmt.executeQuery();
        while (rs.next()) {
            int id = rs.getInt(1);
            String key = rs.getString(2);
            if (parent.isTimeSeries()) {
                Date date = rs.getDate(2);
                String unit = "yyyy-MM-dd";
                if (parent.getTimeUnit().equals(MChart.TIMEUNIT_Week))
                    unit = "yyyy-w";
                else if (parent.getTimeUnit().equals(MChart.TIMEUNIT_Month))
                    unit = "yyyy-MMM";
                else if (parent.getTimeUnit().equals(MChart.TIMEUNIT_Quarter))
                    unit = "yyyy-MM";
                else if (parent.getTimeUnit().equals(MChart.TIMEUNIT_Year))
                    unit = "yyyy";
                SimpleDateFormat format = new SimpleDateFormat(unit);
                key = format.format(date);
                if (parent.getTimeUnit().equals(MChart.TIMEUNIT_Quarter))
                    key = convertToQuarter(format.format(date));
            }
            if (value.equals(key)) {
                if (includedIds.length() > 0)
                    includedIds.append(",");
                includedIds.append(id);
            }
        }
    } catch (SQLException e) {
        throw new DBException(e, sql);
    } finally {
        DB.close(rs, pstmt);
        rs = null;
        pstmt = null;
    }
    if (includedIds.length() == 0)
        return MQuery.getNoRecordQuery(query.getTableName(), false);
    return query;
}

Example 35 with DBException

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

the class MUOMConversion method convert.

//	getConversion
/**
	 *  Get Converted Qty from Server (no cache)
	 *  @param  qty             The quantity to be converted
	 *  @param  C_UOM_From_ID   The C_UOM_ID of the qty
	 *  @param  C_UOM_To_ID     The targeted UOM
	 *  @param  StdPrecision    if true, standard precision, if false costing precision
	 *  @return amount
	 *  @depreciated should not be used
	 */
public static BigDecimal convert(int C_UOM_From_ID, int C_UOM_To_ID, BigDecimal qty, boolean StdPrecision) {
    //  Nothing to do
    if (qty == null || qty.compareTo(Env.ZERO) == 0 || C_UOM_From_ID == C_UOM_To_ID)
        return qty;
    //
    BigDecimal retValue = null;
    int precision = 2;
    String sql = "SELECT c.MultiplyRate, uomTo.StdPrecision, uomTo.CostingPrecision " + "FROM	C_UOM_Conversion c" + " INNER JOIN C_UOM uomTo ON (c.C_UOM_TO_ID=uomTo.C_UOM_ID) " + //	#1/2
    "WHERE c.IsActive='Y' AND c.C_UOM_ID=? AND c.C_UOM_TO_ID=? " + " AND c.M_Product_ID IS NULL" + " ORDER BY c.AD_Client_ID DESC, c.AD_Org_ID DESC";
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        pstmt = DB.prepareStatement(sql, null);
        pstmt.setInt(1, C_UOM_From_ID);
        pstmt.setInt(2, C_UOM_To_ID);
        rs = pstmt.executeQuery();
        if (rs.next()) {
            retValue = rs.getBigDecimal(1);
            precision = rs.getInt(StdPrecision ? 2 : 3);
        }
    } catch (SQLException e) {
        throw new DBException(e, sql);
    } finally {
        DB.close(rs, pstmt);
        rs = null;
        pstmt = null;
    }
    if (retValue == null) {
        s_log.info("NOT found - FromUOM=" + C_UOM_From_ID + ", ToUOM=" + C_UOM_To_ID);
        return null;
    }
    //	Just get Rate
    if (GETRATE.equals(qty))
        return retValue;
    //	Calculate & Scale
    retValue = retValue.multiply(qty);
    if (retValue.scale() > precision)
        retValue = retValue.setScale(precision, BigDecimal.ROUND_HALF_UP);
    return retValue;
}