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

Example 66 with Query

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

the class DefaultRoutingServiceImplTest method getC_UOM_ID.

public int getC_UOM_ID(String x12de355) {
    String whereClause = X_C_UOM.COLUMNNAME_X12DE355 + "=? AND AD_Client_ID IN (0,?)";
    int[] ids = new Query(getCtx(), MUOM.Table_Name, whereClause, null).setParameters(new Object[] { x12de355, getAD_Client_ID() }).setOrderBy("AD_Client_ID DESC").getIDs();
    return ids.length > 0 ? ids[0] : -1;
}

Example 67 with Query

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

the class MRPUpdate method doIt.

//	prepare
/**
	 *  doIT - run process
	 */
protected String doIt() throws Exception {
    String result = null;
    ArrayList<Object> parameters = new ArrayList<Object>();
    StringBuffer whereClause = new StringBuffer(MResource.COLUMNNAME_ManufacturingResourceType + "=? AND AD_Client_ID=?");
    parameters.add(MResource.MANUFACTURINGRESOURCETYPE_Plant);
    parameters.add(m_AD_Client_ID);
    if (p_S_Resource_ID > 0) {
        whereClause.append(" AND S_Resource_ID=?");
        parameters.add(p_S_Resource_ID);
    }
    List<MResource> plants = new Query(getCtx(), MResource.Table_Name, whereClause.toString(), get_TrxName()).setParameters(parameters).list();
    if (plants.size() == 0) {
        // TODO: translate
        throw new AdempiereException("No plants found");
    }
    for (MResource plant : plants) {
        log.info("Run MRP to Plant: " + plant.getName());
        parameters = new ArrayList<Object>();
        whereClause = new StringBuffer("AD_Client_ID=?");
        parameters.add(m_AD_Client_ID);
        if (p_AD_Org_ID > 0) {
            whereClause.append(" AND AD_Org_ID=?");
            parameters.add(p_AD_Org_ID);
        }
        List<MOrg> organizations = new Query(getCtx(), MOrg.Table_Name, whereClause.toString(), get_TrxName()).setParameters(parameters).list();
        for (MOrg organization : organizations) {
            log.info("Run MRP to Organization: " + organization.getName());
            if (p_M_Warehouse_ID == 0) {
                MWarehouse[] ws = MWarehouse.getForOrg(getCtx(), organization.getAD_Org_ID());
                for (MWarehouse w : ws) {
                    log.info("Run MRP to Wharehouse: " + w.getName());
                    deleteRecords(m_AD_Client_ID, organization.getAD_Org_ID(), plant.getS_Resource_ID(), w.getM_Warehouse_ID());
                    createRecords(m_AD_Client_ID, organization.getAD_Org_ID(), plant.getS_Resource_ID(), w.getM_Warehouse_ID());
                    result = result + "<br>finish MRP to Warehouse " + w.getName();
                }
            } else {
                log.info("Run MRP to Wharehouse: " + p_M_Warehouse_ID);
                deleteRecords(m_AD_Client_ID, organization.getAD_Org_ID(), plant.getS_Resource_ID(), p_M_Warehouse_ID);
                createRecords(m_AD_Client_ID, organization.getAD_Org_ID(), plant.getS_Resource_ID(), p_M_Warehouse_ID);
            }
            result = result + "<br>finish MRP to Organization " + organization.getName();
        }
        result = result + "<br>finish MRP to Plant " + plant.getName();
    }
    if (Util.isEmpty(result, true)) {
        // TODO: translate
        return "No records found";
    }
    return Msg.getMsg(getCtx(), "ProcessOK");
}

Example 68 with Query

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

the class DynamicDashBoard method prepareSelectQuery.

/**
	 * 
	 * @return String<br>
	 * 
	 *         This method builds the sql query to generate Result set. It creates Columns and labels as well.
	 * 
	 * 
	 */
public String prepareSelectQuery() {
    // Getting isDisplayed fields based on the Browse ID
    StringBuffer whereClause = new StringBuffer(X_AD_Browse.COLUMNNAME_AD_Browse_ID + " = " + browseId + " AND isDisplayed = 'Y'");
    List<MBrowseField> fieldList = new Query(Env.getCtx(), I_AD_Browse_Field.Table_Name, whereClause.toString(), null).setOnlyActiveRecords(true).setOrderBy(X_AD_Browse_Field.COLUMNNAME_SeqNo).list();
    if (fieldList != null && !fieldList.isEmpty()) {
        lable = new Label[fieldList.size()];
        column = new Column[fieldList.size()];
        sqlQuery = sqlQuery.append(" SELECT ");
        if (zoomWindowId > 0 && zoomTabId > 0) {
            MBrowseField zoomField = new MBrowseField(Env.getCtx(), zoomFieldId, null);
            if (!fieldList.contains(zoomField)) {
                StringBuffer columnWhereClause = new StringBuffer("AD_View_Column_ID = " + zoomField.getAD_View_Column_ID());
                MViewColumn viewColumn = new Query(Env.getCtx(), I_AD_View_Column.Table_Name, columnWhereClause.toString(), null).setOnlyActiveRecords(true).first();
                sqlQuery = sqlQuery.append(viewColumn.getColumnSQL() + ", ");
            } else {
                displayZoomCol = Boolean.TRUE;
            }
            whereClause = new StringBuffer("AD_Tab_ID= " + zoomTabId);
            zoomTab = new Query(Env.getCtx(), I_AD_Tab.Table_Name, whereClause.toString(), null).setOnlyActiveRecords(true).first();
            if (zoomTab != null) {
                // Getting Zoom table based on the zoom tab
                whereClause = new StringBuffer("AD_Table_ID= " + zoomTab.getAD_Table_ID());
                zoomTable = new Query(Env.getCtx(), I_AD_Table.Table_Name, whereClause.toString(), null).setOnlyActiveRecords(true).first();
                if (zoomTable != null) {
                    // table or view? based on suffix "_v". It assumed that
                    // created view is having this suffix
                    //@The View Name must be Actual Table Name and It must ends with one of above suffixes @
                    //If this view combination of multiple tables, the view name must be prefix with any one of Joining Tables
                    //And That table should have primary key::: Eg: AD_USer_v, here AD_User_ID is Primary key of AD_User
                    zoomTableName = zoomTable.getTableName();
                    if (zoomTable.isView()) {
                        if (zoomTableName.endsWith("_vt")) {
                            zoomTableColumnName = zoomTableName.replace("_vt", "_ID");
                            sqlQuery = sqlQuery.append(zoomTableColumnName + ", ");
                        } else // if view ends with _v only
                        if (zoomTableName.endsWith("_v")) {
                            zoomTableColumnName = zoomTableName.replace("_v", "_ID");
                            sqlQuery = sqlQuery.append(zoomTableColumnName + ", ");
                        }
                    } else {
                        zoomTableColumnName = zoomTable.getTableName() + "_ID";
                    }
                }
            }
        }
        int i = 0;
        for (MBrowseField field : fieldList) {
            if (i != 0) {
                sqlQuery = sqlQuery.append(", ");
            }
            // creating column and preparing sql query with db column names
            column[i] = new Column(field.getName(), null, null);
            StringBuffer columnWhereClause = new StringBuffer("AD_View_Column_ID = " + field.getAD_View_Column_ID());
            MViewColumn viewColumn = new Query(Env.getCtx(), I_AD_View_Column.Table_Name, columnWhereClause.toString(), null).setOnlyActiveRecords(true).first();
            sqlQuery = sqlQuery.append(viewColumn.getColumnName().replaceFirst("_", "."));
            i = i + 1;
        }
    }
    whereClause = new StringBuffer("AD_Browse_ID= " + browseId);
    MBrowse browse = new Query(Env.getCtx(), I_AD_Browse.Table_Name, whereClause.toString(), null).setOnlyActiveRecords(true).first();
    if (browse != null) {
        whereClause = new StringBuffer("AD_View_ID= " + browse.getAD_View_ID());
        List<MViewDefinition> list = new Query(Env.getCtx(), I_AD_View_Definition.Table_Name, whereClause.toString(), null).setOnlyActiveRecords(true).list();
        whereClause = new StringBuffer(X_AD_View_Definition.COLUMNNAME_AD_Table_ID + " = " + list.get(0).getAD_Table_ID());
        table = new Query(Env.getCtx(), I_AD_Table.Table_Name, whereClause.toString(), null).setOnlyActiveRecords(true).first();
        StringBuffer joinClause = new StringBuffer();
        for (MViewDefinition viewDefinition : list) {
            if (viewDefinition.getJoinClause() == null)
                joinClause.append(" " + viewDefinition.getTableAlias());
            else
                joinClause.append(" " + viewDefinition.getJoinClause());
        }
        // Associating sql query with table name 
        sqlQuery.append(" FROM " + table.getTableName()).append(joinClause.toString());
        //get where clause of this Smart Browse
        String where = browse.getWhereClause();
        if (where != null) {
            boolean success = true;
            do {
                int index = where.indexOf("@");
                int index2 = where.indexOf("@", index + 1);
                boolean integer = false;
                int replacedValue = 0;
                String replacedString = null;
                if (index > 0 || index2 > 0) {
                    String subString1 = where.substring(index + 1, index2);
                    if (subString1.contains("#")) {
                        if (subString1.endsWith("_ID")) {
                            replacedValue = Env.getContextAsInt(ctx, subString1);
                            integer = true;
                        } else {
                            replacedString = Env.getContext(ctx, subString1);
                        }
                        if (integer) {
                            where = where.replaceAll(where.substring(index, index2 + 1), String.valueOf(replacedValue));
                        } else {
                            where = where.replaceAll(where.substring(index, index2 + 1), "'" + replacedString + "'");
                        }
                    } else {
                        where = where.replaceAll(where.substring(index, index2 + 1), where.substring(index + 1, index2));
                    }
                    success = true;
                } else {
                    success = false;
                }
            } while (success);
            sqlQuery.append(" Where " + where);
        }
    }
    return sqlQuery.toString();
}

Example 69 with Query

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

the class MessageElementHandler method getMessages.

private List<X_AD_Message> getMessages(Properties ctx) {
    int AD_Message_ID = Env.getContextAsInt(ctx, X_AD_Package_Exp_Detail.COLUMNNAME_AD_Message_ID);
    int AD_EntityType_ID = Env.getContextAsInt(ctx, X_AD_Package_Exp_Detail.COLUMNNAME_AD_EntityType_ID);
    String whereClause;
    Object[] params;
    if (AD_Message_ID > 0) {
        whereClause = X_AD_Message.COLUMNNAME_AD_Message_ID + "=?";
        params = new Object[] { AD_Message_ID };
    } else if (AD_EntityType_ID > 0) {
        whereClause = " EXISTS (SELECT 1 FROM AD_EntityType et" + " WHERE et.AD_EntityType_ID=? AND et.EntityType=AD_Message.EntityType)";
        params = new Object[] { AD_EntityType_ID };
    } else {
        throw new IllegalArgumentException("AD_Message_ID and AD_EntityType_ID not found");
    }
    List<X_AD_Message> list = new Query(ctx, X_AD_Message.Table_Name, whereClause, null).setParameters(params).setOrderBy(X_AD_Message.COLUMNNAME_AD_Message_ID).list();
    return list;
}

Example 70 with Query

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

the class RoleElementHandler method create.

public void create(Properties ctx, TransformerHandler document) throws SAXException {
    int Role_id = Env.getContextAsInt(ctx, X_AD_Package_Exp_Detail.COLUMNNAME_AD_Role_ID);
    if (roles.contains(Role_id))
        return;
    roles.add(Role_id);
    X_AD_Role m_Role = new X_AD_Role(ctx, Role_id, null);
    AttributesImpl atts = new AttributesImpl();
    createRoleBinding(atts, m_Role);
    document.startElement("", "", "role", atts);
    // Process org access
    String sql = "SELECT * FROM AD_Role_OrgAccess WHERE AD_Role_ID= " + Role_id;
    PreparedStatement pstmt = null;
    pstmt = DB.prepareStatement(sql, getTrxName(ctx));
    try {
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
            createOrgAccess(ctx, document, rs.getInt("AD_Org_ID"), rs.getInt("AD_Role_ID"));
        }
        rs.close();
        pstmt.close();
        pstmt = null;
    } catch (Exception e) {
        log.log(Level.SEVERE, "AD_Role_OrgAccess", e);
        throw new DatabaseAccessException("Failed to export organization role access.");
    }
    // Process user assignment access
    sql = "SELECT * FROM AD_User_Roles WHERE AD_Role_ID= " + Role_id;
    pstmt = null;
    pstmt = DB.prepareStatement(sql, getTrxName(ctx));
    try {
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
            createUserRole(ctx, document, rs.getInt("AD_User_ID"), rs.getInt("AD_Role_ID"), rs.getInt("AD_Org_ID"));
        }
        rs.close();
        pstmt.close();
        pstmt = null;
    } catch (Exception e) {
        log.log(Level.SEVERE, "AD_User_Roles", e);
        throw new DatabaseAccessException("Failed to export user role assignment.");
    }
    // Process AD_Window_Access Values
    sql = "SELECT * FROM AD_Window_Access WHERE AD_Role_ID= " + Role_id;
    pstmt = null;
    pstmt = DB.prepareStatement(sql, getTrxName(ctx));
    try {
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
            createWindowAccess(ctx, document, rs.getInt("AD_Window_ID"), rs.getInt("AD_Role_ID"));
        }
        rs.close();
        pstmt.close();
        pstmt = null;
    } catch (Exception e) {
        log.log(Level.SEVERE, "AD_Window_Access", e);
        throw new DatabaseAccessException("Failed to export window access.");
    }
    // Process AD_Process_Access Values
    sql = "SELECT * FROM AD_Process_Access WHERE AD_Role_ID= " + Role_id;
    pstmt = null;
    pstmt = DB.prepareStatement(sql, getTrxName(ctx));
    try {
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
            createProcessAccess(ctx, document, rs.getInt("AD_Process_ID"), rs.getInt("AD_Role_ID"));
        }
        rs.close();
        pstmt.close();
        pstmt = null;
    } catch (Exception e) {
        log.log(Level.SEVERE, "AD_Process_Access", e);
        throw new DatabaseAccessException("Failed to export process access.");
    }
    // Process AD_Form_Access Values
    sql = "SELECT * FROM AD_Form_Access WHERE AD_Role_ID= " + Role_id;
    pstmt = null;
    pstmt = DB.prepareStatement(sql, getTrxName(ctx));
    try {
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
            createFormAccess(ctx, document, rs.getInt("AD_Form_ID"), rs.getInt("AD_Role_ID"));
        }
        rs.close();
        pstmt.close();
        pstmt = null;
    } catch (Exception e) {
        log.log(Level.SEVERE, "AD_Form_Access", e);
        throw new DatabaseAccessException("Failed to export form access.");
    }
    // Process AD_Browse_Access
    String whereClause = I_AD_Browse_Access.COLUMNNAME_AD_Role_ID + "=?";
    List<X_AD_Browse_Access> browseaccess = new Query(ctx, I_AD_Browse_Access.Table_Name, whereClause, getTrxName(ctx)).setParameters(Role_id).list();
    for (X_AD_Browse_Access ba : browseaccess) {
        createBrowseAccess(ctx, document, ba.getAD_Browse_ID(), ba.getAD_Role_ID());
    }
    // Process AD_Workflow_Access Values
    sql = "SELECT * FROM AD_Workflow_Access WHERE AD_Role_ID= " + Role_id;
    pstmt = null;
    pstmt = DB.prepareStatement(sql, getTrxName(ctx));
    try {
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
            createWorkflowAccess(ctx, document, rs.getInt("AD_Workflow_ID"), rs.getInt("AD_Role_ID"));
        }
        rs.close();
        pstmt.close();
        pstmt = null;
    } catch (Exception e) {
        log.log(Level.SEVERE, "AD_Workflow_Access", e);
        throw new DatabaseAccessException("Failed to export workflow access.");
    }
    // Process AD_Task_Access Values
    sql = "SELECT * FROM AD_Task_Access WHERE AD_Role_ID= " + Role_id;
    pstmt = null;
    pstmt = DB.prepareStatement(sql, getTrxName(ctx));
    try {
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
            createTaskAccess(ctx, document, rs.getInt("AD_Task_ID"), rs.getInt("AD_Role_ID"));
        }
        rs.close();
        pstmt.close();
        pstmt = null;
    } catch (Exception e) {
        log.log(Level.SEVERE, "AD_Task_Access", e);
        throw new DatabaseAccessException("Failed to export task access.");
    } finally {
        try {
            if (pstmt != null)
                pstmt.close();
        } catch (Exception e) {
        }
        pstmt = null;
    }
    document.endElement("", "", "role");
}