Esempio n. 1
0
        /// <summary>
        /// Create Report Model (Columns)
        /// </summary>
        /// <param name="ctx"></param>
        /// <returns>Report Model</returns>
        public RModel GetRModel(Ctx ctx)
        {
            RModel rm = new RModel("Fact_Acct");
            //  Add Key (Lookups)
            List <String> keys = CreateKeyColumns();
            int           max  = _leadingColumns;

            if (max == 0)
            {
                max = keys.Count;
            }
            for (int i = 0; i < max; i++)
            {
                String column = (String)keys[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.Count; i++)
            {
                String column = (String)keys[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, _ref1));
                    }
                    else
                    {
                        rm.AddColumn(new RColumn(ctx, column, DisplayType.TableDir, null, 0, _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 RColumn(ctx, "PostingType", DisplayType.List,
                                         MFactAcct.POSTINGTYPE_AD_Reference_ID));
            }
            return(rm);
        }
Esempio n. 2
0
        /// <summary>
        /// Create Query and submit
        /// </summary>
        /// <param name="ctx"></param>
        /// <returns>Report Model</returns>
        public RModel Query(Ctx ctx)
        {
            //  Set Where Clause
            StringBuilder whereClause = new StringBuilder();

            //  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();
                IEnumerator <String> it = whereInfo.Values.GetEnumerator();

                while (it.MoveNext())
                {
                    String where = (String)it.Current;
                    if (where != null && where.Length > 0)    //  add only if not empty
                    {
                        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(DataBase.DB.TO_DATE(DateFrom)).Append(" AND ").Append(DataBase.DB.TO_DATE(DateTo));
                    }
                    else if (DateFrom != null)
                    {
                        whereClause.Append("TRUNC(").Append(RModel.TABLE_ALIAS).Append(".DateAcct,'DD') >= ")
                        .Append(DataBase.DB.TO_DATE(DateFrom));
                    }
                    else    //  DateTo != null
                    {
                        whereClause.Append("TRUNC(").Append(RModel.TABLE_ALIAS).Append(".DateAcct,'DD') <= ")
                        .Append(DataBase.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);
                }
            }

            //  Set Order By Clause
            StringBuilder orderClause = new StringBuilder();

            if (sortBy1.Length > 0)
            {
                orderClause.Append(RModel.TABLE_ALIAS).Append(".").Append(sortBy1);
            }
            if (sortBy2.Length > 0)
            {
                if (orderClause.Length > 0)
                {
                    orderClause.Append(",");
                }
                orderClause.Append(RModel.TABLE_ALIAS).Append(".").Append(sortBy2);
            }
            if (sortBy3.Length > 0)
            {
                if (orderClause.Length > 0)
                {
                    orderClause.Append(",");
                }
                orderClause.Append(RModel.TABLE_ALIAS).Append(".").Append(sortBy3);
            }
            if (sortBy4.Length > 0)
            {
                if (orderClause.Length > 0)
                {
                    orderClause.Append(",");
                }
                orderClause.Append(RModel.TABLE_ALIAS).Append(".").Append(sortBy4);
            }
            if (orderClause.Length == 0)
            {
                orderClause.Append(RModel.TABLE_ALIAS).Append(".Fact_Acct_ID");
            }
            //get grid view columns
            RModel rm = GetRModel(ctx);

            //  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(ctx, whereClause.ToString(), orderClause.ToString());

            return(rm);
        }