/// <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);
}
/// <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);
}
