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

Example 66 with MLookup

use of org.compiere.model.MLookup in project lar_361 by comitsrl.

the class VLookup method getDirectAccessSQL.

/**
 * 	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) {
    StringBuffer sql = new StringBuffer();
    m_tableName = MQuery.getZoomTableName(m_columnName);
    m_keyColumnName = MQuery.getZoomColumnName(m_columnName);
    // 
    if (m_columnName.equals("M_Product_ID")) {
        // Reset
        resetTabInfo();
        // 
        sql.append("SELECT M_Product_ID FROM M_Product WHERE (UPPER(Value) LIKE ").append(DB.TO_STRING(text)).append(" OR UPPER(Name) LIKE ").append(DB.TO_STRING(text)).append(" OR SKU LIKE ").append(DB.TO_STRING(text)).append(" OR UPC LIKE ").append(DB.TO_STRING(text)).append(")");
    } else if (m_columnName.equals("C_BPartner_ID")) {
        sql.append("SELECT C_BPartner_ID FROM C_BPartner WHERE (UPPER(Value) LIKE ").append(DB.TO_STRING(text)).append(" OR UPPER(Name) LIKE ").append(DB.TO_STRING(text)).append(")");
    } else if (m_columnName.equals("C_Order_ID")) {
        sql.append("SELECT C_Order_ID FROM C_Order WHERE UPPER(DocumentNo) LIKE ").append(DB.TO_STRING(text));
    } else if (m_columnName.equals("C_Invoice_ID")) {
        sql.append("SELECT C_Invoice_ID 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 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 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'");
        // ***
        log.finest(m_columnName + " (predefined) " + sql.toString());
        return MRole.getDefault().addAccessSQL(sql.toString(), m_tableName, MRole.SQL_NOTQUALIFIED, MRole.SQL_RO);
    }
    // Check if it is a Table Reference
    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;
            ResultSet rs = null;
            try {
                pstmt = DB.prepareStatement(query, null);
                pstmt.setInt(1, AD_Reference_ID);
                rs = pstmt.executeQuery();
                if (rs.next()) {
                    m_keyColumnName = rs.getString(1);
                    displayColumnName = rs.getString(2);
                    m_tableName = rs.getString(3);
                }
            } catch (Exception e) {
                log.log(Level.SEVERE, query, e);
            } finally {
                DB.close(rs, pstmt);
                rs = null;
                pstmt = null;
            }
            if (displayColumnName != null) {
                sql = new StringBuffer();
                sql.append("SELECT ").append(m_keyColumnName).append(" FROM ").append(m_tableName).append(" WHERE UPPER(").append(displayColumnName).append(") LIKE ").append(DB.TO_STRING(text)).append(" AND IsActive='Y'");
                String wc = getWhereClause();
                if (wc != null && wc.length() > 0)
                    sql.append(" AND ").append(wc);
                // ***
                log.finest(m_columnName + " (Table) " + sql.toString());
                return MRole.getDefault().addAccessSQL(sql.toString(), m_tableName, MRole.SQL_NOTQUALIFIED, MRole.SQL_RO);
            }
        }
    // 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=?)";
    sql = new StringBuffer();
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        pstmt = DB.prepareStatement(query, null);
        pstmt.setString(1, m_keyColumnName);
        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));
        }
    } catch (SQLException ex) {
        log.log(Level.SEVERE, query, ex);
    } finally {
        DB.close(rs, pstmt);
        rs = null;
        pstmt = null;
    }
    // 
    if (sql.length() == 0) {
        log.log(Level.SEVERE, m_columnName + " (TableDir) - no standard/identifier columns");
        return "";
    }
    // 
    StringBuffer retValue = new StringBuffer("SELECT ").append(m_columnName).append(" FROM ").append(m_tableName).append(" WHERE (").append(sql).append(")").append(" AND IsActive='Y'");
    String wc = getWhereClause();
    if (wc != null && wc.length() > 0)
        retValue.append(" AND ").append(wc);
    // ***
    log.finest(m_columnName + " (TableDir) " + sql.toString());
    return MRole.getDefault().addAccessSQL(retValue.toString(), m_tableName, MRole.SQL_NOTQUALIFIED, MRole.SQL_RO);
}

Example 67 with MLookup

use of org.compiere.model.MLookup in project lar_361 by comitsrl.

the class VLookup method actionZoom.

// actionBPartner
/**
 *	Action - Zoom
 *	@param selectedItem item
 */
private void actionZoom(Object selectedItem) {
    if (m_lookup == null)
        return;
    // 
    MQuery zoomQuery = m_lookup.getZoomQuery();
    Object value = getValue();
    if (value == null)
        value = selectedItem;
    // If not already exist or exact value
    if (zoomQuery == null || value != null) {
        // ColumnName might be changed in MTab.validateQuery
        zoomQuery = new MQuery();
        String keyTableName = null;
        String keyColumnName = null;
        // Check if it is a Table Reference
        if (m_lookup != null && m_lookup instanceof MLookup) {
            int AD_Reference_ID = ((MLookup) m_lookup).getAD_Reference_Value_ID();
            if (DisplayType.List == m_lookup.getDisplayType()) {
                keyColumnName = "AD_Ref_List_ID";
                keyTableName = "AD_Ref_List";
                value = DB.getSQLValue(null, "SELECT AD_Ref_List_ID FROM AD_Ref_List WHERE AD_Reference_ID=? AND Value=?", AD_Reference_ID, value);
            } else {
                if (AD_Reference_ID != 0) {
                    String query = "SELECT kc.ColumnName, kt.TableName" + " FROM AD_Ref_Table rt" + " INNER JOIN AD_Column kc ON (rt.AD_Key=kc.AD_Column_ID)" + " INNER JOIN AD_Table kt ON (rt.AD_Table_ID=kt.AD_Table_ID)" + " WHERE rt.AD_Reference_ID=?";
                    PreparedStatement pstmt = null;
                    ResultSet rs = null;
                    try {
                        pstmt = DB.prepareStatement(query, null);
                        pstmt.setInt(1, AD_Reference_ID);
                        rs = pstmt.executeQuery();
                        if (rs.next()) {
                            keyColumnName = rs.getString(1);
                            keyTableName = rs.getString(2);
                        }
                    } catch (Exception e) {
                        log.log(Level.SEVERE, query, e);
                    } finally {
                        DB.close(rs, pstmt);
                        rs = null;
                        pstmt = null;
                    }
                }
            // Table Reference
            }
        }
        if (keyColumnName != null && keyColumnName.length() != 0) {
            zoomQuery.addRestriction(keyColumnName, MQuery.EQUAL, value);
            zoomQuery.setZoomColumnName(keyColumnName);
            zoomQuery.setZoomTableName(keyTableName);
        } else {
            zoomQuery.addRestriction(m_columnName, MQuery.EQUAL, value);
            if (m_columnName.indexOf(".") > 0) {
                zoomQuery.setZoomColumnName(m_columnName.substring(m_columnName.indexOf(".") + 1));
                zoomQuery.setZoomTableName(m_columnName.substring(0, m_columnName.indexOf(".")));
            } else {
                zoomQuery.setZoomColumnName(m_columnName);
                // remove _ID to get table name
                zoomQuery.setZoomTableName(m_columnName.substring(0, m_columnName.length() - 3));
            }
        }
        zoomQuery.setZoomValue(value);
        // guess
        zoomQuery.setRecordCount(1);
    }
    int AD_Window_ID = m_lookup.getZoom(zoomQuery);
    // 
    log.info(m_columnName + " - AD_Window_ID=" + AD_Window_ID + " - Query=" + zoomQuery + " - Value=" + value);
    // 
    setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR));
    // 
    AWindow frame = new AWindow();
    if (!frame.initWindow(AD_Window_ID, zoomQuery)) {
        setCursor(Cursor.getDefaultCursor());
        ValueNamePair pp = CLogger.retrieveError();
        String msg = pp == null ? "AccessTableNoView" : pp.getValue();
        ADialog.error(m_lookup.getWindowNo(), this, msg, pp == null ? "" : pp.getName());
    } else {
        AEnv.addToWindowManager(frame);
        if (Ini.isPropertyBool(Ini.P_OPEN_WINDOW_MAXIMIZED)) {
            AEnv.showMaximized(frame);
        } else {
            AEnv.showCenterScreen(frame);
        }
    }
    // async window - not able to get feedback
    frame = null;
    // 
    setCursor(Cursor.getDefaultCursor());
}

Example 68 with MLookup

use of org.compiere.model.MLookup in project lar_361 by comitsrl.

the class PosPayment method init.

private void init() {
    Font font = AdempierePLAF.getFont_Field().deriveFont(18f);
    // North
    CPanel mainPanel = new CPanel(new MigLayout("hidemode 3", "[100:100:300, trailing]20[200:200:300,grow, trailing]"));
    getContentPane().add(mainPanel);
    mainPanel.setBorder(new TitledBorder(Msg.translate(p_ctx, "Payment")));
    CLabel gtLabel = new CLabel(Msg.translate(p_ctx, "GrandTotal"));
    mainPanel.add(gtLabel, "growx");
    mainPanel.add(fTotal, "wrap, growx");
    fTotal.setEditable(false);
    fTotal.setFont(font);
    fTotal.setHorizontalAlignment(JTextField.TRAILING);
    mainPanel.add(new CLabel(Msg.translate(p_ctx, "Balance")), "growx");
    mainPanel.add(fBalance, "wrap, growx");
    fBalance.setEditable(false);
    fBalance.setFont(font);
    fBalance.setHorizontalAlignment(JTextField.TRAILING);
    mainPanel.add(new CLabel(Msg.translate(p_ctx, "TenderType"), "growx"));
    // 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);
    DefaultComboBoxModel typeModel = new DefaultComboBoxModel(types.toArray());
    tenderTypePick.setModel(typeModel);
    // default to cash payment
    for (Object obj : types) {
        if (obj instanceof ValueNamePair) {
            ValueNamePair key = (ValueNamePair) obj;
            if (key.getID().equals("X")) {
                // Cash
                tenderTypePick.setSelectedItem(key);
            }
            if (!"CDKXT".contains(key.getID())) {
                tenderTypePick.removeItem(key);
            }
            if (key.getID().equals("T")) {
                // Account
                accountKey = key;
            }
        }
    }
    tenderTypePick.setFont(font);
    tenderTypePick.addActionListener(this);
    // red1 for ID purpuse during testing
    tenderTypePick.setName("tenderTypePick");
    tenderTypePick.setRenderer(new ListCellRenderer() {

        protected DefaultListCellRenderer defaultRenderer = new DefaultListCellRenderer();

        public Component getListCellRendererComponent(JList list, Object value, int index, boolean isSelected, boolean cellHasFocus) {
            JLabel renderer = (JLabel) defaultRenderer.getListCellRendererComponent(list, value, index, isSelected, cellHasFocus);
            renderer.setPreferredSize(new Dimension(50, 50));
            renderer.setHorizontalAlignment(JLabel.CENTER);
            return renderer;
        }
    });
    mainPanel.add(tenderTypePick, "wrap, h 50!, growx");
    /**
     *  Load Payment Terms
     */
    String sql = "SELECT C_PaymentTerm_ID, Name " + "  FROM C_PaymentTerm " + " WHERE IsActive='Y' AND AD_Client_ID=? ORDER BY Name";
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        pstmt = DB.prepareStatement(sql, null);
        pstmt.setInt(1, Env.getAD_Client_ID(p_ctx));
        rs = pstmt.executeQuery();
        while (rs.next()) {
            int key = rs.getInt(1);
            String name = rs.getString(2);
            KeyNamePair pp = new KeyNamePair(key, name);
            fCPaymentTerm.addItem(pp);
        }
    } catch (SQLException e) {
        log.log(Level.SEVERE, sql, e);
    } finally {
        DB.close(rs, pstmt);
        rs = null;
        pstmt = null;
    }
    fCPaymentTerm.setFont(font);
    lCPaymentTerm = new CLabel(Msg.translate(p_ctx, "PaymentTerms"));
    mainPanel.add(lCPaymentTerm, "growx");
    mainPanel.add(fCPaymentTerm, "wrap, h 50!, growx");
    fPayAmt = new PosTextField(Msg.translate(p_ctx, "PayAmt"), p_posPanel, p_pos.getOSNP_KeyLayout_ID(), DisplayType.getNumberFormat(DisplayType.Amount));
    lPayAmt = new CLabel(Msg.translate(p_ctx, "PayAmt"));
    mainPanel.add(lPayAmt, "growx");
    fPayAmt.setFont(font);
    fPayAmt.setEditable(false);
    fPayAmt.setHorizontalAlignment(JTextField.TRAILING);
    fPayAmt.addActionListener(this);
    mainPanel.add(fPayAmt, "wrap, growx");
    fTenderAmt = new PosTextField(Msg.translate(p_ctx, "AmountTendered"), p_posPanel, p_pos.getOSNP_KeyLayout_ID(), DisplayType.getNumberFormat(DisplayType.Amount));
    lTenderAmt = new CLabel(Msg.translate(p_ctx, "AmountTendered"));
    mainPanel.add(lTenderAmt, "growx");
    fTenderAmt.addActionListener(this);
    fTenderAmt.setFont(font);
    fTenderAmt.setHorizontalAlignment(JTextField.TRAILING);
    mainPanel.add(fTenderAmt, "wrap, growx");
    fReturnAmt = new PosTextField(Msg.translate(p_ctx, "AmountReturned"), p_posPanel, p_pos.getOSNP_KeyLayout_ID(), DisplayType.getNumberFormat(DisplayType.Amount));
    lReturnAmt = new CLabel(Msg.translate(p_ctx, "AmountReturned"));
    mainPanel.add(lReturnAmt, "growx");
    fReturnAmt.setFont(font);
    fReturnAmt.setHorizontalAlignment(JTextField.TRAILING);
    mainPanel.add(fReturnAmt, "wrap, growx");
    fReturnAmt.setEditable(false);
    fCheckRouteNo = new PosTextField(Msg.translate(p_ctx, "RoutingNo"), p_posPanel, p_pos.getOSNP_KeyLayout_ID());
    lCheckRouteNo = new CLabel(Msg.translate(p_ctx, "RoutingNo"));
    mainPanel.add(lCheckRouteNo, "growx");
    mainPanel.add(fCheckRouteNo, "wrap, growx");
    fCheckRouteNo.setFont(font);
    fCheckRouteNo.setHorizontalAlignment(JTextField.TRAILING);
    fCheckAccountNo = new PosTextField(Msg.translate(p_ctx, "AccountNo"), p_posPanel, p_pos.getOSNP_KeyLayout_ID(), new DecimalFormat("#"));
    lCheckAccountNo = new CLabel(Msg.translate(p_ctx, "AccountNo"));
    mainPanel.add(lCheckAccountNo, "growx");
    mainPanel.add(fCheckAccountNo, "wrap, growx");
    fCheckAccountNo.setFont(font);
    fCheckAccountNo.setHorizontalAlignment(JTextField.TRAILING);
    fCheckNo = new PosTextField(Msg.translate(p_ctx, "CheckNo"), p_posPanel, p_pos.getOSNP_KeyLayout_ID(), new DecimalFormat("#"));
    lCheckNo = new CLabel(Msg.translate(p_ctx, "CheckNo"));
    mainPanel.add(lCheckNo, "growx");
    mainPanel.add(fCheckNo, "wrap, growx");
    fCheckNo.setFont(font);
    fCheckNo.setHorizontalAlignment(JTextField.TRAILING);
    fCheckAccountName = new PosTextField(Msg.translate(p_ctx, "A_Name"), p_posPanel, p_pos.getOSNP_KeyLayout_ID());
    lCheckAccountName = new CLabel(Msg.translate(p_ctx, "A_Name"));
    mainPanel.add(lCheckAccountName, "growx");
    mainPanel.add(fCheckAccountName, "wrap, growx");
    fCheckAccountName.setFont(font);
    fCheckAccountName.setHorizontalAlignment(JTextField.TRAILING);
    /**
     *	Load Credit Cards
     */
    ValueNamePair[] ccs = p_order.getCreditCards((BigDecimal) fPayAmt.getValue());
    // Set Selection
    fCCardType = new CComboBox(ccs);
    // red1 Id for testing
    fCCardType.setName("cardType");
    fCCardType.setRenderer(new ListCellRenderer() {

        protected DefaultListCellRenderer defaultRenderer = new DefaultListCellRenderer();

        public Component getListCellRendererComponent(JList list, Object value, int index, boolean isSelected, boolean cellHasFocus) {
            JLabel renderer = (JLabel) defaultRenderer.getListCellRendererComponent(list, value, index, isSelected, cellHasFocus);
            renderer.setPreferredSize(new Dimension(50, 50));
            renderer.setHorizontalAlignment(JLabel.CENTER);
            return renderer;
        }
    });
    lCCardType = new CLabel(Msg.translate(p_ctx, "CreditCardType"));
    mainPanel.add(lCCardType, "growx");
    mainPanel.add(fCCardType, "h 50, wrap, growx");
    fCCardType.setFont(font);
    fCCardNo = new PosTextField(Msg.translate(p_ctx, "CreditCardNumber"), p_posPanel, p_pos.getOSNP_KeyLayout_ID(), new DecimalFormat("#"));
    lCCardNo = new CLabel(Msg.translate(p_ctx, "CreditCardNumber"));
    fCCardNo.setName("cardNo");
    mainPanel.add(lCCardNo, "growx");
    mainPanel.add(fCCardNo, "wrap, growx");
    fCCardNo.setFont(font);
    fCCardNo.setHorizontalAlignment(JTextField.TRAILING);
    fCCardName = new PosTextField(Msg.translate(p_ctx, "Name"), p_posPanel, p_pos.getOSK_KeyLayout_ID());
    lCCardName = new CLabel(Msg.translate(p_ctx, "Name"));
    mainPanel.add(lCCardName, "growx");
    mainPanel.add(fCCardName, "wrap, growx");
    fCCardName.setFont(font);
    fCCardName.setHorizontalAlignment(JTextField.TRAILING);
    fCCardMonth = new PosTextField(Msg.translate(p_ctx, "Expires"), p_posPanel, p_pos.getOSNP_KeyLayout_ID());
    lCCardMonth = new CLabel(Msg.translate(p_ctx, "Expires"));
    fCCardMonth.setName("expiry");
    mainPanel.add(lCCardMonth, "growx");
    mainPanel.add(fCCardMonth, "wrap, w 75!");
    fCCardMonth.setFont(font);
    fCCardMonth.setHorizontalAlignment(JTextField.TRAILING);
    fCCardVC = new PosTextField(Msg.translate(p_ctx, "CVC"), p_posPanel, p_pos.getOSNP_KeyLayout_ID(), new DecimalFormat("#"));
    lCCardVC = new CLabel(Msg.translate(p_ctx, "CVC"));
    mainPanel.add(lCCardVC, "growx");
    mainPanel.add(fCCardVC, "wrap, w 75!");
    fCCardVC.setFont(font);
    fCCardVC.setHorizontalAlignment(JTextField.TRAILING);
    fPrintShipment.setText("Imprimir Remito");
    fPrintShipment.setSelected(true);
    mainPanel.add(fPrintShipment, "skip, wrap, growx");
    AppsAction actCancel = new AppsAction("Cancel", KeyStroke.getKeyStroke(KeyEvent.VK_ENTER, 0), false);
    actCancel.setDelegate(this);
    f_bCancel = (CButton) actCancel.getButton();
    f_bCancel.setFocusable(false);
    mainPanel.add(f_bCancel, "h 50!, w 50!, skip, split 2, trailing");
    AppsAction act = new AppsAction("Ok", KeyStroke.getKeyStroke(KeyEvent.VK_ENTER, 0), false);
    act.setDelegate(this);
    f_bProcess = (CButton) act.getButton();
    f_bProcess.setFocusable(false);
    mainPanel.add(f_bProcess, "h 50!, w 50!");
    pack();
    setTotals();
}