Search in sources :

Example 1 with MViewDefinition

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

the class Browser method getAxisSQLWhere.

/**
	 * Get SQL where for axis
	 * @param viewColumn
	 * @return
	 */
public String getAxisSQLWhere(I_AD_View_Column viewColumn) {
    MViewDefinition viewDefinition = (MViewDefinition) viewColumn.getAD_View_Definition();
    MTable tableBaseName = (MTable) viewDefinition.getAD_Table();
    StringBuilder whereAxis = new StringBuilder();
    boolean onRange = false;
    setParameters();
    for (int i = 0; i < m_parameters_field.size(); i++) {
        String fieldName = "";
        MColumn column = tableBaseName.getColumn(m_parameters_field.get(i).ColumnName);
        if (column != null)
            fieldName = tableBaseName.getTableName() + "." + column.getColumnName();
        else
            continue;
        if (!onRange) {
            if (parametersValues.get(i) != null && !parametersValues.get(i).toString().isEmpty() && !m_parameters_field.get(i).IsRange) {
                whereAxis.append(" AND ");
                whereAxis.append(fieldName).append("=").append(parametersValues.get(i).toString());
            } else if (parametersValues.get(i) != null && !parametersValues.get(i).toString().isEmpty() && m_parameters_field.get(i).IsRange) {
                whereAxis.append(" AND ");
                whereAxis.append(fieldName).append(" >= ? ");
                axisParameters.add(m_parameters_field.get(i));
                axisParametersValues.add(parametersValues.get(i));
                onRange = true;
            } else
                continue;
        } else if (parametersValues.get(i) != null && !parametersValues.get(i).toString().isEmpty()) {
            whereAxis.append(" AND ");
            whereAxis.append(fieldName).append(" <= ? ");
            axisParameters.add(m_parameters_field.get(i));
            axisParametersValues.add(parametersValues.get(i));
            onRange = false;
        }
    }
    return whereAxis.toString();
}
Also used : MColumn(org.compiere.model.MColumn) MTable(org.compiere.model.MTable) MViewDefinition(org.adempiere.model.MViewDefinition)

Example 2 with MViewDefinition

use of org.adempiere.model.MViewDefinition 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");
}
Also used : AttributesImpl(org.xml.sax.helpers.AttributesImpl) Query(org.compiere.model.Query) PackOut(org.adempiere.pipo.PackOut) MViewDefinition(org.adempiere.model.MViewDefinition) MViewColumn(org.adempiere.model.MViewColumn)

Example 3 with MViewDefinition

use of org.adempiere.model.MViewDefinition 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@";
}
Also used : MColumn(org.compiere.model.MColumn) MViewDefinition(org.adempiere.model.MViewDefinition) MViewColumn(org.adempiere.model.MViewColumn)

Example 4 with MViewDefinition

use of org.adempiere.model.MViewDefinition 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();
}
Also used : MBrowseField(org.adempiere.model.MBrowseField) MQuery(org.compiere.model.MQuery) Query(org.compiere.model.Query) I_AD_View_Column(org.adempiere.model.I_AD_View_Column) MViewColumn(org.adempiere.model.MViewColumn) Column(org.zkoss.zul.Column) MViewDefinition(org.adempiere.model.MViewDefinition) MBrowse(org.adempiere.model.MBrowse) MViewColumn(org.adempiere.model.MViewColumn)

Example 5 with MViewDefinition

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

the class ViewDefinitionElementHandler method startElement.

public void startElement(Properties ctx, Element element) throws SAXException {
    String elementValue = element.getElementValue();
    Attributes atts = element.attributes;
    log.info(elementValue + " " + atts.getValue("ADViewDefinitionNameID"));
    String entitytype = atts.getValue("EntityType");
    if (isProcessElement(ctx, entitytype)) {
        if (element.parent != null && element.parent.getElementValue().equals("view") && element.parent.defer) {
            element.defer = true;
            return;
        }
        String tableAlias = atts.getValue("ADViewDefinitionNameID");
        int tableid = get_IDWithColumn(ctx, "AD_Table", "TableName", atts.getValue("ADTableNameID"));
        if (tableid <= 0) {
            element.defer = true;
            return;
        }
        int viewid = 0;
        if (element.parent != null && element.parent.getElementValue().equals("view") && element.parent.recordId > 0) {
            viewid = element.parent.recordId;
        } else {
            viewid = get_ID(ctx, "AD_View", atts.getValue("ADViewNameID"));
            if (element.parent != null && element.parent.getElementValue().equals("view") && viewid > 0) {
                element.parent.recordId = viewid;
            }
        }
        if (viewid <= 0) {
            element.defer = true;
            return;
        }
        StringBuffer sqlB = new StringBuffer("SELECT AD_View_Definition_ID FROM AD_View_Definition WHERE AD_View_ID = " + viewid + " and TableAlias = '" + tableAlias + "'" + " and AD_Table_ID = ?");
        int id = DB.getSQLValue(getTrxName(ctx), sqlB.toString(), tableid);
        MViewDefinition m_View_Definition = new MViewDefinition(ctx, id, getTrxName(ctx));
        if (id <= 0 && atts.getValue("AD_View_Definition_ID") != null && Integer.parseInt(atts.getValue("AD_View_Definition_ID")) <= PackOut.MAX_OFFICIAL_ID)
            m_View_Definition.setAD_View_Definition_ID(Integer.parseInt(atts.getValue("AD_View_Definition_ID")));
        int AD_Backup_ID = -1;
        String Object_Status = null;
        if (id > 0) {
            AD_Backup_ID = copyRecord(ctx, "AD_View_Definition", m_View_Definition);
            Object_Status = "Update";
        } else {
            Object_Status = "New";
            AD_Backup_ID = 0;
        }
        sqlB = null;
        m_View_Definition.setTableAlias(tableAlias);
        String name;
        if (getStringValue(atts, "ADTableNameID") != null) {
            name = atts.getValue("ADTableNameID");
            id = get_IDWithColumn(ctx, "AD_Table", "TableName", name);
            m_View_Definition.setAD_Table_ID(id);
        }
        m_View_Definition.setAD_View_ID(viewid);
        m_View_Definition.setIsActive(atts.getValue("isActive") != null ? Boolean.valueOf(atts.getValue("isActive")).booleanValue() : true);
        m_View_Definition.setProcessing(false);
        m_View_Definition.setSeqNo(Integer.parseInt(atts.getValue("SeqNo")));
        if (getStringValue(atts, "JoinClause") != null) {
            m_View_Definition.setJoinClause(atts.getValue("JoinClause"));
        }
        if (m_View_Definition.save(getTrxName(ctx)) == true) {
            record_log(ctx, 1, m_View_Definition.getTableAlias(), "ViewDefinition", m_View_Definition.get_ID(), AD_Backup_ID, Object_Status, "AD_View_Definition", get_IDWithColumn(ctx, "AD_Table", "TableName", "AD_View_Definition"));
            element.recordId = m_View_Definition.getAD_View_Definition_ID();
        } else {
            record_log(ctx, 0, m_View_Definition.getTableAlias(), "ViewDefinition", m_View_Definition.get_ID(), AD_Backup_ID, Object_Status, "AD_View_Definition", get_IDWithColumn(ctx, "AD_Table", "TableName", "AD_View_Definition"));
            throw new POSaveFailedException("ViewDefinition");
        }
    } else {
        element.skip = true;
    }
}
Also used : Attributes(org.xml.sax.Attributes) POSaveFailedException(org.adempiere.pipo.exception.POSaveFailedException) MViewDefinition(org.adempiere.model.MViewDefinition)

Aggregations

MViewDefinition (org.adempiere.model.MViewDefinition)9 MViewColumn (org.adempiere.model.MViewColumn)4 Query (org.compiere.model.Query)3 PackOut (org.adempiere.pipo.PackOut)2 ProcessCtl (org.compiere.apps.ProcessCtl)2 MColumn (org.compiere.model.MColumn)2 ProcessInfo (org.compiere.process.ProcessInfo)2 AttributesImpl (org.xml.sax.helpers.AttributesImpl)2 I_AD_View_Column (org.adempiere.model.I_AD_View_Column)1 MBrowse (org.adempiere.model.MBrowse)1 MBrowseField (org.adempiere.model.MBrowseField)1 MView (org.adempiere.model.MView)1 X_AD_View (org.adempiere.model.X_AD_View)1 POSaveFailedException (org.adempiere.pipo.exception.POSaveFailedException)1 Waiting (org.compiere.apps.Waiting)1 MQuery (org.compiere.model.MQuery)1 MTable (org.compiere.model.MTable)1 Attributes (org.xml.sax.Attributes)1 Column (org.zkoss.zul.Column)1