Examples with RModel org.compiere.report.core.RModel used on opensource projects

Example 6 with RModel

use of org.compiere.report.core.RModel 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;
}

Example 7 with RModel

use of org.compiere.report.core.RModel 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;
}