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

Example 46 with MLookup

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

the class ADLookup method getDirectAccessSQL.

//	actionText	
/**
	 * 	Generate Access SQL for Search.
	 * 	The SQL returns the ID of the value entered
	 * 	Also sets m_tableName and m_keyColumnName
	 *	@param text uppercase text for LIKE comparison
	 *	@return sql or ""
	 *  Example
	 *	SELECT C_Payment_ID FROM C_Payment WHERE UPPER(DocumentNo) LIKE x OR ...
	 */
private String getDirectAccessSQL(String text) {
    //Tutaj trzeba doda� dodatkowe pole dla odpowiednich typ�w wyszukiwania w selectach
    StringBuffer sql = new StringBuffer();
    // without _ID
    m_tableName = m_columnName.substring(0, m_columnName.length() - 3);
    m_keyColumnName = m_columnName;
    if (m_columnName.equals("C_Invoice_ID")) {
        sql.append("SELECT C_Invoice_ID, DocumentNo FROM C_Invoice WHERE UPPER(DocumentNo) LIKE ").append(DB.TO_STRING(text));
    } else if (m_columnName.equals("M_InOut_ID")) {
        sql.append("SELECT M_InOut_ID, '' FROM M_InOut WHERE UPPER(DocumentNo) LIKE ").append(DB.TO_STRING(text));
    } else if (m_columnName.equals("C_Payment_ID")) {
        sql.append("SELECT C_Payment_ID, DocumentNo FROM C_Payment WHERE UPPER(DocumentNo) LIKE ").append(DB.TO_STRING(text));
    } else if (m_columnName.equals("GL_JournalBatch_ID")) {
        sql.append("SELECT GL_JournalBatch_ID, '' FROM GL_JournalBatch WHERE UPPER(DocumentNo) LIKE ").append(DB.TO_STRING(text));
    } else if (m_columnName.equals("SalesRep_ID")) {
        sql.append("SELECT AD_User_ID, NAME FROM AD_User WHERE UPPER(Name) LIKE ").append(DB.TO_STRING(text));
        m_tableName = "AD_User";
        m_keyColumnName = "AD_User_ID";
    }
    //	Predefined
    if (sql.length() > 0) {
        String wc = getWhereClause();
        if (wc != null && wc.length() > 0)
            sql.append(" AND ").append(wc);
        sql.append(" AND IsActive='Y'");
        if (DB.isOracle())
            sql.append(" AND ROWNUM < " + MAX_PRODUCT_ROWS + " ");
        //	***
        //log.finest("(predefined) " + sql.toString());
        String sqlret = MRole.getDefault().addAccessSQL(sql.toString(), m_tableName, MRole.SQL_NOTQUALIFIED, MRole.SQL_RO);
        if (DB.isPostgreSQL())
            sqlret = sqlret + " LIMIT " + MAX_PRODUCT_ROWS;
        return sqlret;
    }
    // TODO  dorobic obsluge where z ad_ref_table !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
    if (m_lookup != null && m_lookup instanceof MLookup) {
        int AD_Reference_ID = ((MLookup) m_lookup).getAD_Reference_Value_ID();
        if (AD_Reference_ID != 0) {
            String query = "SELECT kc.ColumnName, dc.ColumnName, t.TableName " + "FROM AD_Ref_Table rt" + " INNER JOIN AD_Column kc ON (rt.AD_Key=kc.AD_Column_ID)" + " INNER JOIN AD_Column dc ON (rt.AD_Display=dc.AD_Column_ID)" + " INNER JOIN AD_Table t ON (rt.AD_Table_ID=t.AD_Table_ID) " + "WHERE rt.AD_Reference_ID=?";
            String displayColumnName = null;
            PreparedStatement pstmt = null;
            try {
                pstmt = DB.prepareStatement(query);
                pstmt.setInt(1, AD_Reference_ID);
                ResultSet rs = pstmt.executeQuery();
                if (rs.next()) {
                    m_keyColumnName = rs.getString(1);
                    displayColumnName = rs.getString(2);
                    m_tableName = rs.getString(3);
                }
                rs.close();
                pstmt.close();
                pstmt = null;
            } catch (Exception e) {
            //log.log(Level.SEVERE, "getDirectAccessSQL", e);
            }
            try {
                if (pstmt != null)
                    pstmt.close();
                pstmt = null;
            } catch (Exception e) {
                pstmt = null;
            }
            if (displayColumnName != null) {
                sql = new StringBuffer();
                // kolec  - bylo m_displayColumnName
                sql.append("SELECT ").append(m_keyColumnName).append(", " + displayColumnName).append(" FROM ").append(m_tableName).append(" WHERE UPPER(").append(displayColumnName).append(") LIKE ").append(DB.TO_STRING(text)).append(" AND IsActive='Y'");
                if (DB.isOracle())
                    sql.append(" AND ROWNUM < " + MAX_PRODUCT_ROWS + " ");
                String wc = getWhereClause();
                if (wc != null && wc.length() > 0)
                    sql.append(" AND ").append(wc);
                //	***
                //log.finest("(Table) " + sql.toString());
                String sqlret = MRole.getDefault().addAccessSQL(sql.toString(), m_tableName, MRole.SQL_NOTQUALIFIED, MRole.SQL_RO);
                if (DB.isPostgreSQL())
                    sqlret = sqlret + " LIMIT " + MAX_PRODUCT_ROWS;
                return sqlret;
            }
        }
    //	Table Reference
    }
    //	MLookup
    /** Check Well Known Columns of Table - assumes TableDir	**/
    String query = "SELECT t.TableName, c.ColumnName " + "FROM AD_Column c " + " INNER JOIN AD_Table t ON (c.AD_Table_ID=t.AD_Table_ID AND t.IsView='N') " + "WHERE (c.ColumnName IN ('DocumentNo', 'Value', 'Name') OR c.IsIdentifier='Y')" + " AND c.AD_Reference_ID IN (10,14)" + " AND EXISTS (SELECT * FROM AD_Column cc WHERE cc.AD_Table_ID=t.AD_Table_ID" + " AND cc.IsKey='Y' AND cc.ColumnName=?)";
    m_keyColumnName = m_columnName;
    sql = new StringBuffer();
    PreparedStatement pstmt = null;
    try {
        pstmt = DB.prepareStatement(query);
        pstmt.setString(1, m_keyColumnName);
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
            if (sql.length() != 0)
                sql.append(" OR ");
            m_tableName = rs.getString(1);
            sql.append("UPPER(").append(rs.getString(2)).append(") LIKE ").append(DB.TO_STRING(text));
        }
        rs.close();
        pstmt.close();
        pstmt = null;
    } catch (SQLException ex) {
        log.log(Level.SEVERE, "getDirectAccessSQL", ex);
    }
    try {
        if (pstmt != null)
            pstmt.close();
    } catch (SQLException ex1) {
    }
    pstmt = null;
    //
    if (sql.length() == 0) {
        log.log(Level.SEVERE, "(TableDir) - no standard/identifier columns");
        return "";
    }
    //
    StringBuffer retValue = new StringBuffer("SELECT ").append(m_columnName).append(" , NAME").append(" FROM ").append(m_tableName).append(" WHERE ").append(sql).append(" AND IsActive='Y' ");
    if (DB.isOracle())
        retValue.append(" AND ROWNUM < " + MAX_PRODUCT_ROWS + " ");
    String wc = getWhereClause();
    if (wc != null && wc.length() > 0)
        retValue.append(" AND ").append(wc);
    //	***
    log.finest("(TableDir) " + sql.toString());
    String sqlret = MRole.getDefault().addAccessSQL(retValue.toString(), m_tableName, MRole.SQL_NOTQUALIFIED, MRole.SQL_RO);
    if (DB.isPostgreSQL())
        sqlret = sqlret + " LIMIT " + MAX_PRODUCT_ROWS;
    return sqlret;
}

Example 47 with MLookup

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

the class WCollectDetail method loadStandardPanel.

/**
	 * Load standard Panel
	 * @return void
	 */
public void loadStandardPanel() {
    v_StandarPanel = GridFactory.newGridLayout();
    v_StandarPanel.setWidth("100%");
    v_StandarPanel.setHeight("75px");
    groupPanel.appendChild(v_StandarPanel);
    Rows rows = null;
    Row row = null;
    rows = v_StandarPanel.newRows();
    row = rows.newRow();
    // Payment type selection
    //C_Payment_v.TenderType
    int AD_Column_ID = 8416;
    MLookup lookup = MLookupFactory.get(Env.getCtx(), 0, 0, AD_Column_ID, DisplayType.List);
    ArrayList<Object> types = lookup.getData(true, false, true, true);
    bMinus = v_Parent.createButtonAction("Minus", KeyStroke.getKeyStroke(KeyEvent.VK_F3, Event.F3));
    bMinus.addActionListener(this);
    row.setHeight("55px");
    fTenderType = ListboxFactory.newDropdownListbox();
    fTenderType.addActionListener(this);
    int pos = 0;
    // default to cash payment
    for (Object obj : types) {
        if (obj instanceof ValueNamePair) {
            ValueNamePair key = (ValueNamePair) obj;
            fTenderType.appendItem(key.getName(), key);
            if (key.getID().equals(getTenderType())) {
                fTenderType.setSelectedIndex(pos);
            }
            pos++;
        }
    }
    fTenderType.setStyle(HEIGHT + WIDTH + FONT_SIZE);
    row.appendChild(fTenderType);
    Label lPayAmt = new Label(Msg.translate(p_ctx, "PayAmt"));
    lPayAmt.setWidth("225px");
    fPayAmt = new POSNumberBox(false);
    row.appendChild(fPayAmt);
    row.appendChild(bMinus);
    fPayAmt.setValue(new BigDecimal("0.0"));
    fPayAmt.setStyle("text-align:right;" + HEIGHT + WIDTH + FONT_SIZE);
    fPayAmt.addEventListener("onBlur", this);
    fPayAmt.addEventListener(Events.ON_CHANGING, this);
    fPayAmt.addEventListener(Events.ON_CHANGE, this);
}

Example 48 with MLookup

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

the class WCollectDetail method loadCreditMemoPanel.

/**
	 * Load for Credit Memo
	 * @return void
	 */
private void loadCreditMemoPanel() {
    v_CreditMemoPanel = GridFactory.newGridLayout();
    v_CreditMemoPanel.setWidth("100%");
    v_CreditMemoPanel.setHeight("95px");
    Rows rows = v_CreditMemoPanel.newRows();
    Row row = rows.newRow();
    row.setSpans("1,2");
    //	Add label credit note
    lCreditMemo = new Label(Msg.translate(Env.getCtx(), "CreditMemo") + ":");
    lCreditMemo.setStyle(HEIGHT + WIDTH + FONT_SIZE);
    row.appendChild(lCreditMemo);
    MLookup lookup = getCreditMemoLockup(v_Parent.getC_BPartner_ID());
    ArrayList<Object> types = lookup.getData(false, false, true, true);
    row = rows.newRow();
    fCreditMemo = ListboxFactory.newDropdownListbox();
    row.appendChild(fCreditMemo);
    fCreditMemo.setStyle(HEIGHT + WIDTH + FONT_SIZE);
    fCreditMemo.setValue(Msg.translate(p_ctx, "CreditMemoType"));
    fCreditMemo.addActionListener(this);
    /**
		 *	Load Credit Notes
		 */
    for (Object obj : types) {
        if (obj instanceof KeyNamePair) {
            KeyNamePair key = (KeyNamePair) obj;
            fCreditMemo.appendItem(key.getName(), key.getID());
        }
    }
}

Example 49 with MLookup

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

the class WFactReconcile method initComponents.

/**
	 *  Static Init
	 *  @throws Exception
	 */
private void initComponents() throws Exception {
    //  Buttons
    bCancel.addActionListener(this);
    //
    bGenerate.setLabel(Msg.getMsg(Env.getCtx(), "Process"));
    bGenerate.addActionListener(this);
    bGenerate.setEnabled(false);
    //	
    bReset.setLabel(Msg.getMsg(Env.getCtx(), "Reset"));
    bReset.addActionListener(this);
    bReset.setEnabled(false);
    //
    bZoom.setLabel(Msg.translate(Env.getCtx(), "Fact_Acct_ID"));
    bZoom.setEnabled(false);
    bZoom.addActionListener(this);
    //
    bRefresh.addActionListener(this);
    //  Labels
    labelBlank.setValue(" ");
    labelAcctSchema.setText(Msg.translate(Env.getCtx(), "C_AcctSchema_ID"));
    labelAccount.setText(Msg.translate(Env.getCtx(), "Account_ID"));
    labelBPartner.setText(Msg.translate(Env.getCtx(), "C_BPartner_ID"));
    labelDateAcct.setText(Msg.translate(Env.getCtx(), "DateAcct"));
    labelDateAcct2.setText("-");
    labelProduct.setText(Msg.translate(Env.getCtx(), "M_Product_ID"));
    //
    labelOrg.setText(Msg.translate(Env.getCtx(), "AD_Org_ID"));
    isReconciled.setText(Msg.translate(Env.getCtx(), "IsReconciled"));
    //
    statusBar.setEastVisibility(false);
    statusBar.setAttribute("zk_component_ID", "info_statusBar");
    setStatusLine(Msg.getMsg(Env.getCtx(), "SearchRows_EnterQuery"), false);
    setStatusDB("0");
    //
    differenceLabel.setText(Msg.getMsg(Env.getCtx(), "Difference"));
    differenceField.setReadonly(true);
    differenceField.setValue("0");
    differenceField.setAttribute("zk_component_ID", "ConfirmPanel_differenceField");
    //  Find context and client
    Properties ctx = Env.getCtx();
    m_AD_Client_ID = Env.getAD_Client_ID(Env.getCtx());
    // Organization filter selection
    //  Fact_Acct.C_AcctSchema_ID AD_Column_ID = 2513
    fieldAcctSchema = new WTableDirEditor("C_AcctSchema_ID", false, false, true, MLookupFactory.get(Env.getCtx(), m_WindowNo, 0, MColumn.getColumn_ID(MFactAcct.Table_Name, MFactAcct.COLUMNNAME_C_AcctSchema_ID), DisplayType.TableDir));
    fieldAcctSchema.getComponent().setAttribute("zk_component_ID", "Lookup_Criteria_C_AcctSchema_ID");
    fieldAcctSchema.getComponent().setAttribute("zk_component_prefix", "Lookup_");
    fieldAcctSchema.getComponent().setAttribute("IsDynamic", "False");
    fieldAcctSchema.getComponent().setAttribute("fieldName", "fieldAcctSchema");
    fieldAcctSchema.setValue(MClient.get(Env.getCtx()).getAcctSchema().getC_AcctSchema_ID());
    //
    //Fact_Acct.AD_Org_ID (needed to allow org 0) AD_Column_ID = 839; 
    fieldOrg = new WTableDirEditor("AD_Org_ID", false, false, true, MLookupFactory.get(Env.getCtx(), m_WindowNo, 0, MColumn.getColumn_ID(MFactAcct.Table_Name, MFactAcct.COLUMNNAME_AD_Org_ID), DisplayType.TableDir));
    fieldOrg.getComponent().setAttribute("zk_component_ID", "Lookup_Criteria_AD_Org_ID");
    fieldOrg.getComponent().setAttribute("zk_component_prefix", "Lookup_");
    fieldOrg.getComponent().setAttribute("IsDynamic", "False");
    fieldOrg.getComponent().setAttribute("fieldName", "fieldOrg");
    // Attempt to select "*" and fall back to context           
    fieldOrg.setValue(0);
    if (fieldOrg.getValue() == null || ((Integer) fieldOrg.getValue()).intValue() != 0)
        fieldOrg.setValue(Env.getAD_Org_ID(Env.getCtx()));
    //
    //  BPartner C_Invoice.C_BPartner_ID AD_Column_ID = 3499; 
    fieldBPartner.getComponent().setAttribute("zk_component_ID", "Lookup_Criteria_C_BPartner_ID");
    fieldBPartner.getComponent().setAttribute("zk_component_prefix", "Lookup_");
    fieldBPartner.getComponent().setAttribute("IsDynamic", "False");
    fieldBPartner.getComponent().setAttribute("fieldName", "fieldBPartner");
    fieldBPartner.getComponent().setWidth("200px");
    //
    // Product Fact_Acct.M_Product_ID AD_Column_ID = 2527;        
    fieldProduct.getComponent().setAttribute("zk_component_ID", "Lookup_Criteria_M_Product_ID");
    fieldProduct.getComponent().setAttribute("zk_component_prefix", "Lookup_");
    fieldProduct.getComponent().setAttribute("IsDynamic", "False");
    fieldProduct.getComponent().setAttribute("fieldName", "fieldProduct");
    fieldProduct.getComponent().setWidth("200px");
    //
    // The Account combo.  A bit more involved if we try to filter out the summary accounts.
    MLookup lookup;
    try {
        lookup = MLookupFactory.get(Env.getCtx(), m_WindowNo, MColumn.getColumn_ID(MFactAcct.Table_Name, MFactAcct.COLUMNNAME_Account_ID), DisplayType.TableDir, Env.getLanguage(Env.getCtx()), MFactAcct.COLUMNNAME_Account_ID, 0, false, "C_ElementValue.IsSummary = 'N'");
    } catch (Exception e) {
        // Jut alors!  Drop the validation and try again.
        lookup = MLookupFactory.get(Env.getCtx(), m_WindowNo, 0, MColumn.getColumn_ID(MFactAcct.Table_Name, MFactAcct.COLUMNNAME_Account_ID), DisplayType.TableDir);
    }
    fieldAccount = new WTableDirEditor("AD_Org_ID", true, false, true, lookup);
    fieldAccount.getComponent().setAttribute("zk_component_ID", "Lookup_Criteria_Account_ID");
    fieldAccount.getComponent().setAttribute("zk_component_prefix", "Lookup_");
    fieldAccount.getComponent().setAttribute("IsDynamic", "False");
    fieldAccount.getComponent().setAttribute("fieldName", "fieldAccount");
    if (fieldAccount.getComponent().getItemCount() > 2)
        fieldAccount.getComponent().setSelectedIndex(1);
    //  Define the table
    m_sql = miniTable.prepareTable(new ColumnInfo[] { new ColumnInfo(" ", "fa.Fact_Acct_ID", IDColumn.class, false, false, null), new ColumnInfo(Msg.translate(ctx, "AmtAcct"), "(fa.amtacctdr-fa.amtacctcr)", BigDecimal.class, true, true, null), new ColumnInfo("DR/CR", "(CASE WHEN (fa.amtacctdr-fa.amtacctcr) < 0 THEN 'CR' ELSE 'DR' END)", String.class), new ColumnInfo(Msg.translate(ctx, "C_BPartner_ID"), "bp.Name", String.class), new ColumnInfo(Msg.translate(ctx, "DateAcct"), "fa.DateAcct", Timestamp.class), new ColumnInfo(Msg.translate(ctx, "GL_Category_ID"), "glc.Name", String.class), new ColumnInfo(Msg.translate(ctx, "M_Product_ID"), "p.Value", String.class), new ColumnInfo(Msg.translate(ctx, "Qty"), "Qty", BigDecimal.class), new ColumnInfo(Msg.translate(ctx, "Description"), "fa.Description", String.class), new ColumnInfo(Msg.translate(ctx, "MatchCode"), "r.MatchCode", String.class), new ColumnInfo(Msg.translate(ctx, "DateTrx"), "fa.DateTrx", Timestamp.class), new ColumnInfo(Msg.translate(ctx, "AD_Org_ID"), "o.Value", String.class), new ColumnInfo(Msg.translate(ctx, "Amt"), "abs(fa.amtacctdr-fa.amtacctcr)", BigDecimal.class, 0, true, false, null, false) }, //	FROM
    "Fact_Acct fa" + " LEFT OUTER JOIN Fact_Reconciliation r ON (fa.Fact_Acct_ID=r.Fact_Acct_ID)" + " LEFT OUTER JOIN C_BPartner bp ON (fa.C_BPartner_ID=bp.C_BPartner_ID)" + " LEFT OUTER JOIN AD_Org o ON (o.AD_Org_ID=fa.AD_Org_ID)" + " LEFT OUTER JOIN M_Product p ON (p.M_Product_ID=fa.M_Product_ID)" + " LEFT OUTER JOIN GL_Category glc ON (fa.GL_Category_ID=glc.GL_Category_ID)", //	additional where & order in loadTableInfo()
    " fa.AD_Client_ID=?", true, "fa");
    //
    miniTable.addActionListener(new EventListener() {

        public void onEvent(Event event) throws Exception {
            if (event.getName().equals("onSelect")) {
                calculateSelection();
                boolean enable = (miniTable.getSelectedCount() == 1);
                bZoom.setEnabled(enable);
            }
        }
    });
}

Example 50 with MLookup

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

the class WCreateFromStatementUI method dynInit.

/**
	 *  Dynamic Init
	 *  @throws Exception if Lookups cannot be initialized
	 *  @return true if initialized
	 */
public boolean dynInit() throws Exception {
    log.config("");
    //Refresh button
    Button refreshButton = v_CreateFromPanel.getConfirmPanel().createButton(ConfirmPanel.A_REFRESH);
    refreshButton.addEventListener(Events.ON_CLICK, this);
    v_CreateFromPanel.getConfirmPanel().addButton(refreshButton);
    //  C_BankStatement.C_BankAccount_ID
    int AD_Column_ID = 4917;
    MLookup lookup = MLookupFactory.get(Env.getCtx(), p_WindowNo, 0, AD_Column_ID, DisplayType.TableDir);
    bankAccountField = new WTableDirEditor("C_BankAccount_ID", true, true, true, lookup);
    //  Set Default
    bankAccountField.setValue(getC_BankAccount_ID());
    //  initial Loading
    authorizationField = new WStringEditor("authorization", false, false, true, 10, 30, null, null);
    authorizationField.getComponent().addEventListener(Events.ON_CHANGE, this);
    lookup = MLookupFactory.get(Env.getCtx(), p_WindowNo, 0, MColumn.getColumn_ID(MPayment.Table_Name, MPayment.COLUMNNAME_C_DocType_ID), DisplayType.TableDir);
    documentTypeField = new WTableDirEditor(MPayment.COLUMNNAME_C_DocType_ID, false, false, true, lookup);
    documentTypeField.getComponent().addEventListener(Events.ON_CHANGE, this);
    lookup = MLookupFactory.get(Env.getCtx(), p_WindowNo, 0, MColumn.getColumn_ID(MPayment.Table_Name, MPayment.COLUMNNAME_TenderType), DisplayType.List);
    tenderTypeField = new WTableDirEditor(MPayment.COLUMNNAME_TenderType, false, false, true, lookup);
    tenderTypeField.getComponent().addEventListener(Events.ON_CHANGE, this);
    lookup = MLookupFactory.get(Env.getCtx(), p_WindowNo, 0, 3499, DisplayType.Search);
    bPartnerLookup = new WSearchEditor("C_BPartner_ID", false, false, true, lookup);
    Timestamp date = Env.getContextAsDate(Env.getCtx(), p_WindowNo, MBankStatement.COLUMNNAME_StatementDate);
    dateToField.setValue(date);
    loadBankAccount();
    return true;
}