Examples with MLookupInfo org.compiere.model.MLookupInfo used on opensource projects

Example 6 with MLookupInfo

use of org.compiere.model.MLookupInfo in project adempiere by adempiere.

the class PaySelect method getPaymentRuleData.

//  dynInit
/**
	 *  Load Bank Info - Load Info from Bank Account and valid Documents (PaymentRule)
	 */
public ArrayList<ValueNamePair> getPaymentRuleData(BankInfo bi) {
    if (bi == null)
        return null;
    m_bankBalance = bi.Balance;
    ArrayList<ValueNamePair> data = new ArrayList<ValueNamePair>();
    //  MLookupInfo.getAD_Reference_ID("All_Payment Rule");
    int AD_Reference_ID = 195;
    Language language = Env.getLanguage(Env.getCtx());
    MLookupInfo info = MLookupFactory.getLookup_List(language, AD_Reference_ID);
    String sql = info.Query.substring(0, info.Query.indexOf(" ORDER BY")) + " AND " + info.KeyColumn + " IN (SELECT PaymentRule FROM C_BankAccountDoc WHERE C_BankAccount_ID=?) " + info.Query.substring(info.Query.indexOf(" ORDER BY"));
    try {
        PreparedStatement pstmt = DB.prepareStatement(sql, null);
        pstmt.setInt(1, bi.C_BankAccount_ID);
        ResultSet rs = pstmt.executeQuery();
        ValueNamePair vp = null;
        while (rs.next()) {
            //  returns also not active
            vp = new ValueNamePair(rs.getString(2), rs.getString(3));
            data.add(vp);
        }
        rs.close();
        pstmt.close();
    } catch (SQLException e) {
        log.log(Level.SEVERE, sql, e);
    }
    return data;
}

Example 7 with MLookupInfo

use of org.compiere.model.MLookupInfo in project adempiere by adempiere.

the class WSearchEditorAutoComplete method getSelectSQL.

@Override
protected String getSelectSQL(String search, List<Object> params) {
    String searchSQL = Util.stripDiacritics(search);
    if (!searchSQL.startsWith("%")) {
        searchSQL = "%" + searchSQL;
    }
    if (!searchSQL.endsWith("%")) {
        searchSQL += "%";
    }
    final StringBuffer sql_select = new StringBuffer();
    final StringBuffer sql_where = new StringBuffer();
    sql_where.append(lookup.getTableName()).append(".IsActive=?");
    params.add(true);
    if (searchColumns.length > 0) {
        sql_where.append(" AND (");
        for (int i = 0; i < searchColumns.length; i++) {
            sql_select.append(",");
            if (i > 0) {
                sql_where.append(" OR ");
            }
            sql_where.append("UPPER(" + FUNC_unaccent_string + "(");
            if (searchColumns[i].equals(searchColumnsSQL[i])) {
                sql_select.append(lookup.getTableName()).append(".").append(searchColumnsSQL[i]).append(" AS ").append(searchColumns[i]);
                sql_where.append(lookup.getTableName()).append(".").append(searchColumns[i]);
            } else {
                sql_select.append(searchColumnsSQL[i]).append(" AS ").append(searchColumns[i]);
                sql_where.append(lookup.getTableName()).append(".").append(searchColumns[i]);
            }
            sql_where.append(", 1)) LIKE UPPER(" + FUNC_unaccent_string + "(?, 1)) ");
            // .append(") LIKE "+DB.TO_STRING(searchSQL));
            params.add(searchSQL);
        }
        // Full generated identifier search
        sql_where.append(" OR UPPER(" + FUNC_unaccent_string + "(ZZ_DisplayName, 1)) LIKE UPPER(" + FUNC_unaccent_string + "(?, 1))");
        params.add(searchSQL);
        //
        sql_where.append(") ");
    } else {
        log.warning("No search columns found");
        sql_where.append(" AND 1=2 ");
    }
    final MLookupInfo info = lookup.getLookupInfo();
    String sql = info.Query;
    // Dynamic validation:
    if (!info.IsValidated) {
        String validation = Env.parseContext(info.ctx, info.WindowNo, info.ValidationCode, false);
        info.parsedValidationCode = validation;
        if (validation.length() == 0 && info.ValidationCode.length() > 0) {
            log.fine(info.KeyColumn + ": Loader NOT Validated: " + info.ValidationCode);
        } else {
            log.fine(info.KeyColumn + ": Loader Validated: " + validation);
            int posFrom = sql.lastIndexOf(" FROM ");
            boolean hasWhere = sql.indexOf(" WHERE ", posFrom) != -1;
            //
            int posOrder = sql.lastIndexOf(" ORDER BY ");
            if (posOrder != -1)
                sql = sql.substring(0, posOrder) + (hasWhere ? " AND " : " WHERE ") + validation + sql.substring(posOrder);
            else
                sql += (hasWhere ? " AND " : " WHERE ") + validation;
            if (CLogMgt.isLevelFinest())
                log.fine(info.KeyColumn + ": Validation=" + validation);
        }
    }
    //
    // Autocomplete filter:
    final StringBuffer sqlBuf = new StringBuffer();
    sqlBuf.append(sql.replace("," + lookup.getTableName() + ".IsActive FROM " + lookup.getTableName() + " WHERE ", " AS ZZ_DisplayName" + "," + lookup.getTableName() + ".IsActive " + sql_select + " FROM " + lookup.getTableName() + " WHERE "));
    int i = sqlBuf.lastIndexOf(" ORDER BY ");
    if (i > 0) {
        sqlBuf.insert(i, ") " + lookup.getTableName() + " WHERE " + sql_where);
    } else {
        sqlBuf.append(") " + lookup.getTableName() + " WHERE " + sql_where);
    }
    sqlBuf.insert(0, "SELECT * FROM (");
    sql = sqlBuf.toString();
    //
    return sql;
}

Example 8 with MLookupInfo

use of org.compiere.model.MLookupInfo in project adempiere by adempiere.

the class VPayPrint method dynInit.

//  VPayPrint
/**
	 *  Dynamic Init
	 */
private void dynInit() {
    //  C_PaySelection_ID
    //  C_PaySelectionCheck.C_PaySelection_ID
    int AD_Column_ID = 7670;
    //	FR [ 297 ]
    //	Add DocStatus for validation
    MLookupInfo info = MLookupFactory.getLookupInfo(Env.getCtx(), windowNo, AD_Column_ID, DisplayType.Search);
    info.ValidationCode = "C_PaySelection.DocStatus IN('CO', 'CL') AND C_PaySelection.C_BankAccount_ID IS NOT NULL";
    MLookup lookupPS = new MLookup(info, 0);
    paySelectSearch = new VLookup("C_PaySelection_ID", true, false, true, lookupPS);
    paySelectSearch.addVetoableChangeListener(this);
}

Example 9 with MLookupInfo

use of org.compiere.model.MLookupInfo in project adempiere by adempiere.

the class VLookupAutoCompleter method getSelectSQL.

@Override
protected String getSelectSQL(final String searchInput, final int caretPosition, final List<Object> params) {
    final String search;
    if (caretPosition > 0 && caretPosition < searchInput.length()) {
        search = new StringBuilder(searchInput).insert(caretPosition, "%").toString();
    } else {
        search = searchInput;
    }
    String searchSQL = search;
    if (!searchSQL.startsWith("%")) {
        searchSQL = "%" + searchSQL;
    }
    if (!searchSQL.endsWith("%")) {
        searchSQL += "%";
    }
    StringBuffer sql_select = new StringBuffer();
    StringBuffer sql_where = new StringBuffer();
    sql_where.append(lookup.getTableName()).append(".IsActive=?");
    params.add(true);
    if (searchColumns.length > 0) {
        sql_where.append(" AND (");
        for (int i = 0; i < searchColumns.length; i++) {
            sql_select.append(",");
            if (i > 0) {
                sql_where.append(" OR ");
            }
            sql_where.append("UPPER(" + FUNC_unaccent_string + "(");
            if (searchColumns[i].equals(searchColumnsSQL[i])) {
                sql_select.append(lookup.getTableName()).append(".").append(searchColumnsSQL[i]).append(" AS ").append(searchColumns[i]);
                sql_where.append(lookup.getTableName()).append(".").append(searchColumns[i]);
            } else {
                sql_select.append(searchColumnsSQL[i]).append(" AS ").append(searchColumns[i]);
                sql_where.append(lookup.getTableName()).append(".").append(searchColumns[i]);
            }
            sql_where.append(", 1)) LIKE UPPER(" + FUNC_unaccent_string + "(?, 1)) ");
            // .append(") LIKE "+DB.TO_STRING(searchSQL));
            params.add(searchSQL);
        }
        // Full generated identifier search
        sql_where.append(" OR UPPER(" + FUNC_unaccent_string + "(ZZ_DisplayName, 1)) LIKE UPPER(" + FUNC_unaccent_string + "(?, 1))");
        params.add(searchSQL);
        //
        sql_where.append(") ");
    } else {
        log.warning("No search columns found");
        sql_where.append(" AND 1=2 ");
    }
    final MLookupInfo info = lookup.getLookupInfo();
    String sql = info.Query;
    // Dynamic validation:
    if (!info.IsValidated) {
        String validation = Env.parseContext(info.ctx, info.WindowNo, info.ValidationCode, false);
        info.parsedValidationCode = validation;
        if (validation.length() == 0 && info.ValidationCode.length() > 0) {
            log.fine(info.KeyColumn + ": Loader NOT Validated: " + info.ValidationCode);
        } else {
            log.fine(info.KeyColumn + ": Loader Validated: " + validation);
            int posFrom = sql.lastIndexOf(" FROM ");
            boolean hasWhere = sql.indexOf(" WHERE ", posFrom) != -1;
            //
            int posOrder = sql.lastIndexOf(" ORDER BY ");
            if (posOrder != -1)
                sql = sql.substring(0, posOrder) + (hasWhere ? " AND " : " WHERE ") + validation + sql.substring(posOrder);
            else
                sql += (hasWhere ? " AND " : " WHERE ") + validation;
            if (CLogMgt.isLevelFinest())
                log.fine(info.KeyColumn + ": Validation=" + validation);
        }
    }
    //
    // Autocomplete filter:
    final StringBuffer sqlBuf = new StringBuffer();
    sqlBuf.append(sql.replace("," + lookup.getTableName() + ".IsActive FROM " + lookup.getTableName() + " WHERE ", " AS ZZ_DisplayName" + "," + lookup.getTableName() + ".IsActive " + sql_select + " FROM " + lookup.getTableName() + " WHERE "));
    int i = sqlBuf.lastIndexOf(" ORDER BY ");
    if (i > 0) {
        sqlBuf.insert(i, ") " + lookup.getTableName() + " WHERE " + sql_where);
    } else {
        sqlBuf.append(") " + lookup.getTableName() + " WHERE " + sql_where);
    }
    sqlBuf.insert(0, "SELECT * FROM (");
    sql = sqlBuf.toString();
    //
    return sql;
}

Example 10 with MLookupInfo

use of org.compiere.model.MLookupInfo in project adempiere by adempiere.

the class HRActionNotice method getConceptReference.

/**
	 * Get Reference Concept
	 * @param referenceId
	 * @return
	 * @return ArrayList<ValueNamePair>
	 */
public ArrayList<ValueNamePair> getConceptReference(int referenceId) {
    ArrayList<ValueNamePair> data = new ArrayList<ValueNamePair>();
    //	Get Lookup Info
    MLookupInfo infoLookup = MLookupFactory.getLookup_List(Env.getLanguage(Env.getCtx()), referenceId);
    //	Valid Lookup
    if (infoLookup == null)
        return data;
    //	Get SQL
    String sql = infoLookup.Query;
    //	
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        pstmt = DB.prepareStatement(sql, null);
        rs = pstmt.executeQuery();
        ValueNamePair vp = null;
        data.add(new ValueNamePair("", ""));
        while (rs.next()) {
            vp = new ValueNamePair(rs.getString(2), rs.getString(3));
            data.add(vp);
        }
    } catch (SQLException e) {
        log.log(Level.SEVERE, sql, e);
    } finally {
        DB.close(rs);
        DB.close(pstmt);
    }
    return data;
}