Examples with MViewColumn org.adempiere.model.MViewColumn used on opensource projects

Example 1 with MViewColumn

use of org.adempiere.model.MViewColumn in project adempiere by adempiere.

the class VBrowserTable method prepareTable.

//  setColumnReadOnly
@Override
public String prepareTable(List<MBrowseField> fields, boolean multiSelection) {
    StringBuffer sql = new StringBuffer("");
    m_multiSelection = multiSelection;
    browserFields = fields;
    int col = 0;
    //  Add columns & sql
    for (MBrowseField field : fields) {
        MViewColumn columnView = field.getAD_View_Column();
        //  create sql
        if (col > 0 && columnView.getColumnSQL().length() > 0)
            sql.append(", ");
        if (field.isKey()) {
            setKey(col);
            field.setName("#");
        }
        sql.append(columnView.getColumnSQL()).append(" ").append("AS").append(" ").append(columnView.getColumnName());
        browserRows.addBrowserField(field, col);
        if (field.isDisplayed()) {
            addColumn(field.get_ValueAsString(I_AD_Browse_Field.COLUMNNAME_Name));
        }
        //	BR [ 257 ]
        col++;
    }
    //	Set Column Class
    col = 0;
    for (MBrowseField field : fields) {
        if (field.isDisplayed()) {
            setColumnClass(col, MBrowseField.createGridFieldVO(field, browser.getWindowNo()), field.getAD_Reference_ID(), field.isReadOnly(), //	#845 Bad translation for table column
            field.get_Translation(I_AD_Browse_Field.COLUMNNAME_Name));
            col++;
        }
    }
    //	
    return sql.toString();
}

Example 2 with MViewColumn

use of org.adempiere.model.MViewColumn in project adempiere by adempiere.

the class Browser method getInfoColumnForAxisField.

/**
	 * Get Info_Column for Axis Field
	 * @param field defined as Axis
	 * @return Info_Column with Axis Field
	 */
public List<MBrowseField> getInfoColumnForAxisField(MBrowseField field) {
    List<MBrowseField> list = new ArrayList<MBrowseField>();
    axisParameters = new ArrayList<>();
    axisParametersValues = new ArrayList<>();
    try {
        I_AD_View_Column xcol, pcol, ycol;
        xcol = field.getAD_View_Column();
        pcol = field.getAxis_Parent_Column();
        ycol = field.getAxis_Column();
        String columnName = xcol.getAD_Column().getColumnName();
        MBrowseField fieldKey = ((MBrowse) field.getAD_Browse()).getFieldKey();
        if (fieldKey == null)
            throw new AdempiereException("@NotFound@ @IsKey@");
        MTable xTable = (MTable) ycol.getAD_View_Definition().getAD_Table();
        String xTableName = xTable.getTableName();
        String keyColumn = MQuery.getZoomColumnName(columnName);
        String tableName = MQuery.getZoomTableName(columnName);
        String whereClause = "";
        if (pcol != null && pcol.getAD_View_Column_ID() > 0) {
            MTable parentTable = MTable.get(field.getCtx(), tableName);
            MColumn parentColumn = getParentColumn(parentTable.getAD_Table_ID());
            if (parentColumn == null)
                throw new AdempiereException("@NotFound@ @IsParent@");
            //	BR [ 242 ]
            if (field.getAD_Val_Rule_ID() > 0)
                whereClause = Env.parseContext(Env.getCtx(), getWindowNo(), field.getAD_Val_Rule().getCode(), false);
        }
        MLookup lookup = MLookupFactory.get(Env.getCtx(), 0, xcol.getAD_Column_ID(), field.getAD_Reference_ID(), m_language, keyColumn, field.getAD_Reference_Value_ID(), false, whereClause);
        int cols = 0;
        StringBuilder axisSql = new StringBuilder("(SELECT ");
        axisSql.append("SUM(").append(ycol.getAD_Column().getColumnName()).append(") FROM  ").append(ycol.getAD_View_Definition().getAD_Table().getTableName()).append(" WHERE ").append(xTableName).append(".").append(fieldKey.getAD_View_Column().getAD_Column().getColumnName()).append("=").append(fieldKey.getAD_View_Column().getColumnSQL());
        for (int id : getAxisRecordIds(tableName, whereClause)) {
            cols++;
            String display = lookup.getDisplay(id).trim();
            display = display.length() > 12 ? display.substring(1, 12) + "_" + cols : display;
            String joinColumn = Msg.translate(m_language, ycol.getAD_Column().getColumnName());
            joinColumn = joinColumn.length() > 15 ? joinColumn.substring(1, 15) : joinColumn;
            String sqlColName = display + "/" + joinColumn;
            String colName = lookup.getDisplay(id).trim() + "/" + Msg.translate(m_language, ycol.getAD_Column().getColumnName());
            StringBuilder axisWhere = new StringBuilder(" ");
            axisWhere.append(getAxisSQLWhere(ycol)).append(" AND ").append(xcol.getAD_View_Definition().getTableAlias()).append(".").append(xcol.getAD_Column().getColumnName());
            StringBuffer select = new StringBuffer();
            select.append(axisSql).append(axisWhere);
            select.append("=").append(id).append(")");
            MViewColumn viewColumn = new MViewColumn(field.getCtx(), 0, field.get_TrxName());
            MViewColumn.copyValues((MViewColumn) ycol, viewColumn);
            viewColumn.setAD_View_Column_ID(ycol.getAD_View_Column_ID());
            viewColumn.setAD_Column_ID(ycol.getAD_Column_ID());
            viewColumn.setColumnSQL(select.toString());
            viewColumn.setColumnName("\"" + sqlColName + "\"");
            MBrowseField browseField = new MBrowseField((MBrowse) field.getAD_Browse(), viewColumn);
            browseField.setAD_Browse_ID(field.getAD_Browse_ID());
            browseField.setAD_Element_ID(field.getAD_Element_ID());
            browseField.setName(colName);
            browseField.setDescription(viewColumn.getDescription());
            browseField.setHelp(viewColumn.getHelp());
            if (viewColumn.get_ID() > 0)
                browseField.setAD_View_Column_ID(viewColumn.getAD_View_Column_ID());
            browseField.setIsActive(true);
            browseField.setIsIdentifier(viewColumn.isIdentifier());
            browseField.setIsRange(false);
            browseField.setIsQueryCriteria(false);
            browseField.setAD_Reference_ID(ycol.getAD_Column().getAD_Reference_ID());
            browseField.setAD_Reference_Value_ID(ycol.getAD_Column().getAD_Reference_Value_ID());
            browseField.setIsKey(false);
            browseField.setIsDisplayed(true);
            browseField.setAxis_Column_ID(field.getAxis_Column_ID());
            browseField.setAxis_Parent_Column_ID(field.getAxis_Parent_Column_ID());
            browseField.setIsReadOnly(field.isReadOnly());
            browseField.setAD_Element_ID(field.getAD_Element_ID());
            list.add(browseField);
            log.finest("Added Column=" + sqlColName + " SQL = " + select);
        }
    } catch (Exception e) {
        throw new AdempiereException(e);
    }
    return list;
}

Example 3 with MViewColumn

use of org.adempiere.model.MViewColumn in project adempiere by adempiere.

the class ViewDefinitionElementHandler method create.

public void create(Properties ctx, TransformerHandler document) throws SAXException {
    PackOut packOut = (PackOut) ctx.get("PackOutProcess");
    int AD_View_Definition_ID = Env.getContextAsInt(ctx, X_AD_View_Definition.COLUMNNAME_AD_View_Definition_ID);
    MViewDefinition m_View_Definition = new MViewDefinition(ctx, AD_View_Definition_ID, getTrxName(ctx));
    AttributesImpl atts = new AttributesImpl();
    createViewDefinitionBinding(atts, m_View_Definition);
    document.startElement("", "", "viewdefinition", atts);
    // View Columns tags.
    StringBuilder whereClause = new StringBuilder(I_AD_View_Definition.COLUMNNAME_AD_View_Definition_ID).append("=?");
    List<MViewColumn> viewColumns = new Query(ctx, I_AD_View_Column.Table_Name, whereClause.toString(), getTrxName(ctx)).setParameters(m_View_Definition.get_ID()).list();
    for (MViewColumn vc : viewColumns) {
        createViewColumn(ctx, document, vc.getAD_View_Column_ID());
    }
    document.endElement("", "", "viewdefinition");
}

Example 4 with MViewColumn

use of org.adempiere.model.MViewColumn in project adempiere by adempiere.

the class CreateViewColumn method doIt.

/**
	 * Process - Generate Export Format
	 * 
	 * @return info
	 */
@SuppressWarnings("unchecked")
protected String doIt() throws Exception {
    MViewDefinition join = new MViewDefinition(getCtx(), p_Record_ID, get_TrxName());
    for (MColumn attr : join.getEntityAttributes()) {
        MViewColumn column = MViewColumn.get(join, attr);
        if (column != null)
            continue;
        column = new MViewColumn(attr);
        column.setAD_View_Definition_ID(join.getAD_View_Definition_ID());
        column.setColumnSQL(join.getTableAlias() + "." + attr.getColumnName());
        column.setColumnName(join.getTableAlias().toUpperCase() + "_" + attr.getColumnName());
        column.setEntityType(join.getAD_View().getEntityType());
        column.setAD_View_ID(join.getAD_View_ID());
        column.saveEx();
        addLog(attr.getColumnName());
    }
    return "@Ok@";
}

Example 5 with MViewColumn

use of org.adempiere.model.MViewColumn 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();
}