Search in sources :

Example 1 with RColumn

use of org.compiere.report.core.RColumn in project adempiere by adempiere.

the class WAcctViewerData method getRModel.

// query
/**
	 *  Create Report Model (Columns)
	 *  @return Report Model
	 */
private RModel getRModel() {
    Properties ctx = Env.getCtx();
    RModel rm = new RModel("Fact_Acct");
    //  Add Key (Lookups)
    ArrayList<String> keys = createKeyColumns();
    int max = m_leadingColumns;
    if (max == 0)
        max = keys.size();
    for (int i = 0; i < max; i++) {
        String column = keys.get(i);
        if (column != null && column.startsWith("Date"))
            rm.addColumn(new RColumn(ctx, column, DisplayType.Date));
        else if (column != null && column.endsWith("_ID"))
            rm.addColumn(new RColumn(ctx, column, DisplayType.TableDir));
    }
    //  Main Info
    rm.addColumn(new RColumn(ctx, "AmtAcctDr", DisplayType.Amount));
    rm.addColumn(new RColumn(ctx, "AmtAcctCr", DisplayType.Amount));
    if (displaySourceAmt) {
        if (!keys.contains("DateTrx"))
            rm.addColumn(new RColumn(ctx, "DateTrx", DisplayType.Date));
        rm.addColumn(new RColumn(ctx, "C_Currency_ID", DisplayType.TableDir));
        rm.addColumn(new RColumn(ctx, "AmtSourceDr", DisplayType.Amount));
        rm.addColumn(new RColumn(ctx, "AmtSourceCr", DisplayType.Amount));
        rm.addColumn(new RColumn(ctx, "Rate", DisplayType.Amount, "CASE WHEN (AmtSourceDr + AmtSourceCr) = 0 THEN 0" + " ELSE (AmtAcctDr + AmtAcctCr) / (AmtSourceDr + AmtSourceCr) END"));
    }
    for (int i = max; i < keys.size(); i++) {
        String column = keys.get(i);
        if (column != null && column.startsWith("Date"))
            rm.addColumn(new RColumn(ctx, column, DisplayType.Date));
        else if (column.startsWith("UserElement")) {
            if (column.indexOf('1') != -1)
                rm.addColumn(new RColumn(ctx, column, DisplayType.TableDir, null, 0, m_ref1));
            else
                rm.addColumn(new RColumn(ctx, column, DisplayType.TableDir, null, 0, m_ref2));
        } else if (column != null && column.endsWith("_ID"))
            rm.addColumn(new RColumn(ctx, column, DisplayType.TableDir));
    }
    if (!keys.contains("DateAcct"))
        rm.addColumn(new RColumn(ctx, "DateAcct", DisplayType.Date));
    if (!keys.contains("C_Period_ID"))
        rm.addColumn(new RColumn(ctx, "C_Period_ID", DisplayType.TableDir));
    if (displayQty) {
        rm.addColumn(new RColumn(ctx, "C_UOM_ID", DisplayType.TableDir));
        rm.addColumn(new RColumn(ctx, "Qty", DisplayType.Quantity));
    }
    if (displayDocumentInfo) {
        rm.addColumn(new RColumn(ctx, "AD_Table_ID", DisplayType.TableDir));
        rm.addColumn(new RColumn(ctx, "Record_ID", DisplayType.ID));
        rm.addColumn(new RColumn(ctx, "Description", DisplayType.String));
    }
    if (PostingType == null || PostingType.length() == 0)
        rm.addColumn(new // teo_sarca, [ 1664208 ]
        RColumn(// teo_sarca, [ 1664208 ]
        ctx, // teo_sarca, [ 1664208 ]
        "PostingType", // teo_sarca, [ 1664208 ]
        DisplayType.List, RModel.TABLE_ALIAS + ".PostingType", MFactAcct.POSTINGTYPE_AD_Reference_ID, null));
    return rm;
}
Also used : RModel(org.compiere.report.core.RModel) RColumn(org.compiere.report.core.RColumn) Properties(java.util.Properties)

Example 2 with RColumn

use of org.compiere.report.core.RColumn in project adempiere by adempiere.

the class AcctViewerData method query.

//  getButtonRecordID
/**************************************************************************

	/**
	 *  Create Query and submit
	 *  @return Report Model
	 */
protected RModel query() {
    //  Set Where Clause
    StringBuffer whereClause = new StringBuffer();
    //  Add Organization
    if (C_AcctSchema_ID != 0)
        whereClause.append(RModel.TABLE_ALIAS).append(".C_AcctSchema_ID=").append(C_AcctSchema_ID);
    //	Posting Type Selected
    if (PostingType != null && PostingType.length() > 0) {
        if (whereClause.length() > 0)
            whereClause.append(" AND ");
        whereClause.append(RModel.TABLE_ALIAS).append(".PostingType='").append(PostingType).append("'");
    }
    //
    if (documentQuery) {
        if (whereClause.length() > 0)
            whereClause.append(" AND ");
        whereClause.append(RModel.TABLE_ALIAS).append(".AD_Table_ID=").append(AD_Table_ID).append(" AND ").append(RModel.TABLE_ALIAS).append(".Record_ID=").append(Record_ID);
    } else {
        //  get values (Queries)
        Iterator<String> it = whereInfo.values().iterator();
        while (it.hasNext()) {
            String where = (String) it.next();
            if (//  add only if not empty
            where != null && where.length() > 0) {
                if (whereClause.length() > 0)
                    whereClause.append(" AND ");
                whereClause.append(RModel.TABLE_ALIAS).append(".").append(where);
            }
        }
        if (DateFrom != null || DateTo != null) {
            if (whereClause.length() > 0)
                whereClause.append(" AND ");
            if (DateFrom != null && DateTo != null)
                whereClause.append("TRUNC(").append(RModel.TABLE_ALIAS).append(".DateAcct, 'DD') BETWEEN ").append(DB.TO_DATE(DateFrom)).append(" AND ").append(DB.TO_DATE(DateTo));
            else if (DateFrom != null)
                whereClause.append("TRUNC(").append(RModel.TABLE_ALIAS).append(".DateAcct, 'DD') >= ").append(DB.TO_DATE(DateFrom));
            else
                //  DateTo != null
                whereClause.append("TRUNC(").append(RModel.TABLE_ALIAS).append(".DateAcct, 'DD') <= ").append(DB.TO_DATE(DateTo));
        }
        //  Add Organization
        if (AD_Org_ID != 0) {
            if (whereClause.length() > 0)
                whereClause.append(" AND ");
            whereClause.append(RModel.TABLE_ALIAS).append(".AD_Org_ID=").append(AD_Org_ID);
        }
    }
    RModel rm = getRModel();
    //  Set Order By Clause
    StringBuffer orderClause = new StringBuffer();
    if (sortBy1.length() > 0) {
        RColumn col = rm.getRColumn(sortBy1);
        if (col != null)
            orderClause.append(col.getDisplaySQL());
        else
            orderClause.append(RModel.TABLE_ALIAS).append(".").append(sortBy1);
    }
    if (sortBy2.length() > 0) {
        if (orderClause.length() > 0)
            orderClause.append(",");
        RColumn col = rm.getRColumn(sortBy2);
        if (col != null)
            orderClause.append(col.getDisplaySQL());
        else
            orderClause.append(RModel.TABLE_ALIAS).append(".").append(sortBy2);
    }
    if (sortBy3.length() > 0) {
        if (orderClause.length() > 0)
            orderClause.append(",");
        RColumn col = rm.getRColumn(sortBy3);
        if (col != null)
            orderClause.append(col.getDisplaySQL());
        else
            orderClause.append(RModel.TABLE_ALIAS).append(".").append(sortBy3);
    }
    if (sortBy4.length() > 0) {
        if (orderClause.length() > 0)
            orderClause.append(",");
        RColumn col = rm.getRColumn(sortBy4);
        if (col != null)
            orderClause.append(col.getDisplaySQL());
        else
            orderClause.append(RModel.TABLE_ALIAS).append(".").append(sortBy4);
    }
    if (orderClause.length() == 0)
        orderClause.append(RModel.TABLE_ALIAS).append(".Fact_Acct_ID");
    //  Groups
    if (group1 && sortBy1.length() > 0)
        rm.setGroup(sortBy1);
    if (group2 && sortBy2.length() > 0)
        rm.setGroup(sortBy2);
    if (group3 && sortBy3.length() > 0)
        rm.setGroup(sortBy3);
    if (group4 && sortBy4.length() > 0)
        rm.setGroup(sortBy4);
    //  Totals
    rm.setFunction("AmtAcctDr", RModel.FUNCTION_SUM);
    rm.setFunction("AmtAcctCr", RModel.FUNCTION_SUM);
    rm.query(Env.getCtx(), whereClause.toString(), orderClause.toString());
    return rm;
}
Also used : RModel(org.compiere.report.core.RModel) RColumn(org.compiere.report.core.RColumn)

Example 3 with RColumn

use of org.compiere.report.core.RColumn in project adempiere by adempiere.

the class AcctViewerData method getRModel.

//  query
/**
	 *  Create Report Model (Columns)
	 *  @return Report Model
	 */
private RModel getRModel() {
    Properties ctx = Env.getCtx();
    RModel rm = new RModel("Fact_Acct");
    //  Add Key (Lookups)
    ArrayList<String> keys = createKeyColumns();
    int max = m_leadingColumns;
    if (max == 0)
        max = keys.size();
    for (int i = 0; i < max; i++) {
        String column = (String) keys.get(i);
        if (column != null && column.startsWith("Date"))
            rm.addColumn(new RColumn(ctx, column, DisplayType.Date));
        else if (column != null && column.endsWith("_ID"))
            rm.addColumn(new RColumn(ctx, column, DisplayType.TableDir));
    }
    //  Main Info
    rm.addColumn(new RColumn(ctx, "AmtAcctDr", DisplayType.Amount));
    rm.addColumn(new RColumn(ctx, "AmtAcctCr", DisplayType.Amount));
    if (displaySourceAmt) {
        if (!keys.contains("DateTrx"))
            rm.addColumn(new RColumn(ctx, "DateTrx", DisplayType.Date));
        rm.addColumn(new RColumn(ctx, "C_Currency_ID", DisplayType.TableDir));
        rm.addColumn(new RColumn(ctx, "AmtSourceDr", DisplayType.Amount));
        rm.addColumn(new RColumn(ctx, "AmtSourceCr", DisplayType.Amount));
        rm.addColumn(new RColumn(ctx, "Rate", DisplayType.Amount, "CASE WHEN (AmtSourceDr + AmtSourceCr) = 0 THEN 0" + " ELSE (AmtAcctDr + AmtAcctCr) / (AmtSourceDr + AmtSourceCr) END"));
    }
    //	Remaining Keys
    for (int i = max; i < keys.size(); i++) {
        String column = (String) keys.get(i);
        if (column != null && column.startsWith("Date"))
            rm.addColumn(new RColumn(ctx, column, DisplayType.Date));
        else if (column.startsWith("UserElement")) {
            if (column.indexOf('1') != -1)
                rm.addColumn(new RColumn(ctx, column, DisplayType.TableDir, null, 0, m_ref1));
            else
                rm.addColumn(new RColumn(ctx, column, DisplayType.TableDir, null, 0, m_ref2));
        } else if (column != null && column.endsWith("_ID"))
            rm.addColumn(new RColumn(ctx, column, DisplayType.TableDir));
    }
    //	Info
    if (!keys.contains("DateAcct"))
        rm.addColumn(new RColumn(ctx, "DateAcct", DisplayType.Date));
    if (!keys.contains("C_Period_ID"))
        rm.addColumn(new RColumn(ctx, "C_Period_ID", DisplayType.TableDir));
    if (displayQty) {
        rm.addColumn(new RColumn(ctx, "C_UOM_ID", DisplayType.TableDir));
        rm.addColumn(new RColumn(ctx, "Qty", DisplayType.Quantity));
    }
    if (displayDocumentInfo) {
        rm.addColumn(new RColumn(ctx, "AD_Table_ID", DisplayType.TableDir));
        rm.addColumn(new RColumn(ctx, "Record_ID", DisplayType.ID));
        rm.addColumn(new RColumn(ctx, "Description", DisplayType.String));
    }
    if (PostingType == null || PostingType.length() == 0)
        rm.addColumn(new // teo_sarca, [ 1664208 ]
        RColumn(// teo_sarca, [ 1664208 ]
        ctx, // teo_sarca, [ 1664208 ]
        "PostingType", // teo_sarca, [ 1664208 ]
        DisplayType.List, RModel.TABLE_ALIAS + ".PostingType", MFactAcct.POSTINGTYPE_AD_Reference_ID, null));
    return rm;
}
Also used : RModel(org.compiere.report.core.RModel) RColumn(org.compiere.report.core.RColumn) Properties(java.util.Properties)

Example 4 with RColumn

use of org.compiere.report.core.RColumn in project adempiere by adempiere.

the class WAcctViewer method actionQuery.

// sortAddItem
/**
	 *  Query
	 */
private void actionQuery() {
    //  Parameter Info
    StringBuffer para = new StringBuffer();
    //  Reset Selection Data
    m_data.C_AcctSchema_ID = 0;
    m_data.AD_Org_ID = 0;
    //  Save Selection Choices
    Listitem listitem = selAcctSchema.getSelectedItem();
    KeyNamePair kp = null;
    if (listitem != null)
        kp = (KeyNamePair) listitem.getValue();
    if (kp != null)
        m_data.C_AcctSchema_ID = kp.getKey();
    para.append("C_AcctSchema_ID=").append(m_data.C_AcctSchema_ID);
    listitem = selPostingType.getSelectedItem();
    ValueNamePair vp = null;
    if (listitem != null)
        vp = (ValueNamePair) listitem.getValue();
    else
        return;
    m_data.PostingType = vp.getValue();
    para.append(", PostingType=").append(m_data.PostingType);
    //  Document
    m_data.documentQuery = selDocument.isChecked();
    para.append(", DocumentQuery=").append(m_data.documentQuery);
    if (selDocument.isChecked()) {
        if (m_data.AD_Table_ID == 0 || m_data.Record_ID == 0)
            return;
        para.append(", AD_Table_ID=").append(m_data.AD_Table_ID).append(", Record_ID=").append(m_data.Record_ID);
    } else {
        m_data.DateFrom = selDateFrom.getValue() != null ? new Timestamp(selDateFrom.getValue().getTime()) : null;
        para.append(", DateFrom=").append(m_data.DateFrom);
        m_data.DateTo = selDateTo.getValue() != null ? new Timestamp(selDateTo.getValue().getTime()) : null;
        para.append(", DateTo=").append(m_data.DateTo);
        listitem = selOrg.getSelectedItem();
        if (listitem != null)
            kp = (KeyNamePair) listitem.getValue();
        else
            kp = null;
        if (kp != null)
            m_data.AD_Org_ID = kp.getKey();
        para.append(", AD_Org_ID=").append(m_data.AD_Org_ID);
        //
        Iterator<String> it = m_data.whereInfo.values().iterator();
        while (it.hasNext()) para.append(", ").append(it.next());
    }
    //  Save Display Choices
    m_data.displayQty = displayQty.isChecked();
    para.append(" - Display Qty=").append(m_data.displayQty);
    m_data.displaySourceAmt = displaySourceAmt.isChecked();
    para.append(", Source=").append(m_data.displaySourceAmt);
    m_data.displayDocumentInfo = displayDocumentInfo.isChecked();
    para.append(", Doc=").append(m_data.displayDocumentInfo);
    listitem = sortBy1.getSelectedItem();
    vp = null;
    if (listitem != null) {
        vp = (ValueNamePair) listitem.getValue();
        if (vp.getName() != null && vp.getName().trim().length() > 0) {
            //vp.getName();
            m_data.sortBy1 = vp.getValue();
            m_data.group1 = group1.isChecked();
            para.append(" - Sorting: ").append(m_data.sortBy1).append("/").append(m_data.group1);
        }
    }
    listitem = sortBy2.getSelectedItem();
    vp = null;
    if (listitem != null) {
        vp = (ValueNamePair) listitem.getValue();
        if (vp.getName() != null && vp.getName().trim().length() > 0) {
            //vp.getName();
            m_data.sortBy2 = vp.getValue();
            m_data.group2 = group2.isChecked();
            para.append(", ").append(m_data.sortBy2).append("/").append(m_data.group2);
        }
    }
    listitem = sortBy3.getSelectedItem();
    vp = null;
    if (listitem != null) {
        vp = (ValueNamePair) listitem.getValue();
        if (vp.getName() != null && vp.getName().trim().length() > 0) {
            //vp.getName();
            m_data.sortBy3 = vp.getValue();
            m_data.group3 = group3.isChecked();
            para.append(", ").append(m_data.sortBy3).append("/").append(m_data.group3);
        }
    }
    listitem = sortBy4.getSelectedItem();
    vp = null;
    if (listitem != null) {
        vp = (ValueNamePair) listitem.getValue();
        if (vp.getName() != null && vp.getName().trim().length() > 0) {
            //vp.getName();
            m_data.sortBy4 = vp.getValue();
            m_data.group4 = group4.isChecked();
            para.append(", ").append(m_data.sortBy4).append("/").append(m_data.group4);
        }
    }
    bQuery.setEnabled(false);
    statusLine.setValue(" " + Msg.getMsg(Env.getCtx(), "Processing"));
    log.config(para.toString());
    //  Switch to Result pane
    tabbedPane.setSelectedIndex(1);
    //  Set TableModel with Query
    RModel rmodel = m_data.query();
    m_queryData = rmodel.getRows();
    List<ArrayList<Object>> list = null;
    paging.setPageSize(PAGE_SIZE);
    if (m_queryData.size() > PAGE_SIZE) {
        list = m_queryData.subList(0, PAGE_SIZE);
        paging.setTotalSize(m_queryData.size());
        pagingPanel.setVisible(true);
    } else {
        list = m_queryData;
        paging.setTotalSize(m_queryData.size());
        pagingPanel.setVisible(false);
    }
    paging.setActivePage(0);
    ListModelTable listmodeltable = new ListModelTable(list);
    if (table.getListhead() == null) {
        Listhead listhead = new Listhead();
        listhead.setSizable(true);
        for (int i = 0; i < rmodel.getColumnCount(); i++) {
            // Replace user columns with the user selected names
            String displayColumnName = rmodel.getColumnName(i);
            ;
            String columnName;
            RColumn col = rmodel.getColumn(i);
            columnName = col.getColumnName();
            MAcctSchema as = MAcctSchema.get(Env.getCtx(), m_data.C_AcctSchema_ID);
            if (columnName.equals("User1_ID")) {
                MAcctSchemaElement ase = as.getAcctSchemaElement(MAcctSchemaElement.ELEMENTTYPE_UserList1);
                if (ase != null)
                    displayColumnName = Msg.translate(Env.getCtx(), ase.getName());
            } else if (columnName.equals("User2_ID")) {
                MAcctSchemaElement ase = as.getAcctSchemaElement(MAcctSchemaElement.ELEMENTTYPE_UserList2);
                if (ase != null)
                    displayColumnName = Msg.translate(Env.getCtx(), ase.getName());
            } else if (columnName.equals("User3_ID")) {
                MAcctSchemaElement ase = as.getAcctSchemaElement(MAcctSchemaElement.ELEMENTTYPE_UserList3);
                if (ase != null)
                    displayColumnName = Msg.translate(Env.getCtx(), ase.getName());
            } else if (columnName.equals("User4_ID")) {
                MAcctSchemaElement ase = as.getAcctSchemaElement(MAcctSchemaElement.ELEMENTTYPE_UserList4);
                if (ase != null)
                    displayColumnName = Msg.translate(Env.getCtx(), ase.getName());
            }
            Listheader listheader = new Listheader(displayColumnName);
            listheader.setTooltiptext(rmodel.getColumnName(i));
            listhead.appendChild(listheader);
        }
        table.appendChild(listhead);
    } else // Elaine 2008/07/28
    {
        Listhead listhead = table.getListhead();
        // remove existing column header
        listhead.getChildren().clear();
        // add in new column header
        for (int i = 0; i < rmodel.getColumnCount(); i++) {
            Listheader listheader = new Listheader(rmodel.getColumnName(i));
            listhead.appendChild(listheader);
        }
    }
    //
    table.getItems().clear();
    table.setItemRenderer(new WListItemRenderer());
    table.setModel(listmodeltable);
    resultPanel.invalidate();
    bQuery.setEnabled(true);
    statusLine.setValue(" " + Msg.getMsg(Env.getCtx(), "ViewerOptions"));
}
Also used : WListItemRenderer(org.adempiere.webui.component.WListItemRenderer) Listheader(org.zkoss.zul.Listheader) Listhead(org.zkoss.zul.Listhead) Listitem(org.zkoss.zul.Listitem) ArrayList(java.util.ArrayList) RModel(org.compiere.report.core.RModel) ListModelTable(org.adempiere.webui.component.ListModelTable) RColumn(org.compiere.report.core.RColumn) Timestamp(java.sql.Timestamp) MAcctSchema(org.compiere.model.MAcctSchema) KeyNamePair(org.compiere.util.KeyNamePair) ValueNamePair(org.compiere.util.ValueNamePair) MAcctSchemaElement(org.compiere.model.MAcctSchemaElement)

Aggregations

RColumn (org.compiere.report.core.RColumn)4 RModel (org.compiere.report.core.RModel)4 Properties (java.util.Properties)2 Timestamp (java.sql.Timestamp)1 ArrayList (java.util.ArrayList)1 ListModelTable (org.adempiere.webui.component.ListModelTable)1 WListItemRenderer (org.adempiere.webui.component.WListItemRenderer)1 MAcctSchema (org.compiere.model.MAcctSchema)1 MAcctSchemaElement (org.compiere.model.MAcctSchemaElement)1 KeyNamePair (org.compiere.util.KeyNamePair)1 ValueNamePair (org.compiere.util.ValueNamePair)1 Listhead (org.zkoss.zul.Listhead)1 Listheader (org.zkoss.zul.Listheader)1 Listitem (org.zkoss.zul.Listitem)1