public static DataTable BalanceSheetTable(Dictionary <String, TVariant>AParameters, TReportingDbAdapter DbAdapter)
{
DataTable FilteredResults = null;
/* Required columns:
* Actual
* LastYearActual
* LastYearEnd
*/
Int32 LedgerNumber = AParameters["param_ledger_number_i"].ToInt32();
Int32 NumberOfAccountingPeriods = new TLedgerInfo(LedgerNumber).NumberOfAccountingPeriods;
Int32 AccountingYear = AParameters["param_year_i"].ToInt32();
Int32 ReportPeriodEnd = AParameters["param_end_period_i"].ToInt32();
String HierarchyName = AParameters["param_account_hierarchy_c"].ToString();
String RootCostCentre = AParameters["param_cost_centre_code"].ToString();
Boolean International = AParameters["param_currency"].ToString().StartsWith("Int");
Decimal EffectiveExchangeRate = 1;
Decimal LastYearExchangeRate = 1;
if (International)
{
TCorporateExchangeRateCache ExchangeRateCache = new TCorporateExchangeRateCache();
EffectiveExchangeRate = ExchangeRateCache.GetCorporateExchangeRate(DBAccess.GDBAccessObj,
LedgerNumber,
AccountingYear,
ReportPeriodEnd,
-1);
LastYearExchangeRate = ExchangeRateCache.GetCorporateExchangeRate(DBAccess.GDBAccessObj,
LedgerNumber,
AccountingYear - 1,
ReportPeriodEnd,
-1);
}
TLogging.Log(Catalog.GetString("Loading data.."), TLoggingType.ToStatusBar);
DataTable resultTable = null;
TDBTransaction ReadTrans = null;
DbAdapter.FPrivateDatabaseObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref ReadTrans,
delegate
{
ACostCentreTable AllCostCentres = ACostCentreAccess.LoadViaALedger(LedgerNumber, ReadTrans);
AllCostCentres.DefaultView.Sort = ACostCentreTable.GetCostCentreToReportToDBName();
List <string>ReportingCostCentres = new List <string>();
GetReportingCostCentres(AllCostCentres, ReportingCostCentres, RootCostCentre);
String CostCentreList = StringHelper.StrMerge(ReportingCostCentres.ToArray(), ',');
CostCentreList = "'" + CostCentreList.Replace(",", "','") + "'";
String Summarised = // This query gets the totals I need
"(SELECT " +
" glm.a_year_i AS Year," +
" glmp.a_period_number_i AS Period," +
" a_account.a_account_type_c AS AccountType," +
" a_account.a_debit_credit_indicator_l AS DebitCredit," +
" glm.a_account_code_c AS AccountCode," +
" a_account.a_account_code_short_desc_c AS AccountName," +
" sum(glmp.a_actual_base_n) AS Actual" +
" FROM a_general_ledger_master AS glm, a_general_ledger_master_period AS glmp, a_account" +
" WHERE glm.a_ledger_number_i=" + LedgerNumber +
" AND glm.a_cost_centre_code_c in (" + CostCentreList + ") " +
" AND glm.a_year_i IN (" + (AccountingYear - 1) + ", " + AccountingYear + ")" +
" AND glm.a_glm_sequence_i = glmp.a_glm_sequence_i" +
" AND glmp.a_period_number_i=" + ReportPeriodEnd +
" AND a_account.a_account_code_c = glm.a_account_code_c" +
" AND a_account.a_ledger_number_i = glm.a_ledger_number_i" +
" AND a_account.a_posting_status_l = true" +
" GROUP BY glm.a_year_i, glmp.a_period_number_i, a_account.a_account_type_c," +
" a_account.a_debit_credit_indicator_l, glm.a_account_code_c," +
" a_account.a_account_code_short_desc_c" +
" ORDER BY glm.a_account_code_c) AS summarised"
;
String Query =
"SELECT " +
"summarised.*," +
" 1 AS AccountLevel," +
" false AS HasChildren," +
" false AS ParentFooter," +
" 0 AS AccountTypeOrder," +
" false AS AccountIsSummary," +
" 'Path' AS AccountPath," +
" 0.0 AS ActualYTD," +
" 0.0 AS LastYearActual" +
" FROM " + Summarised;
resultTable = DbAdapter.RunQuery(Query, "BalanceSheet", ReadTrans);
DataView LastYear = new DataView(resultTable);
LastYear.Sort = "AccountCode";
LastYear.RowFilter = String.Format("Year={0}", AccountingYear - 1);
DataView ThisYear = new DataView(resultTable);
ThisYear.RowFilter = String.Format("Year={0}", AccountingYear);
//
// Some of these rows are from a year ago. I'll copy those into the current period "LastYear" fields.
TLogging.Log(Catalog.GetString("Get last year data.."), TLoggingType.ToStatusBar);
foreach (DataRowView rv in ThisYear)
{
if (DbAdapter.IsCancelled)
{
return;
}
DataRow Row = rv.Row;
Int32 RowIdx = LastYear.Find(
new Object[] {
Row["AccountCode"]
}
);
if (RowIdx >= 0)
{
DataRow LastYearRow = LastYear[RowIdx].Row;
Row["LastYearActual"] = LastYearExchangeRate * Convert.ToDecimal(LastYearRow["Actual"]);
LastYearRow.Delete();
}
Row["Actual"] = EffectiveExchangeRate * Convert.ToDecimal(Row["Actual"]);
}
//
// At the end of that process, there may be some remaining LastYearRows that don't have an equivalent entry in this year
// (because there's been no activity this year.)
// I'll tweak these so the Row appears as this year, but the amount appears as LastYear:
foreach (DataRowView rv in LastYear)
{
DataRow Row = rv.Row;
Row["Year"] = Convert.ToInt32(Row["Year"]) + 1;
Row["LastYearActual"] = LastYearExchangeRate * Convert.ToDecimal(Row["Actual"]);
Row["Actual"] = 0.0;
}
//
// So now I don't have to look at last year's rows:
FilteredResults = ThisYear.ToTable("BalanceSheet");
//
// I only have "posting accounts" - I need to add the summary accounts.
AAccountHierarchyDetailTable HierarchyTbl = AAccountHierarchyDetailAccess.LoadViaAAccountHierarchy(LedgerNumber,
HierarchyName,
ReadTrans);
HierarchyTbl.DefaultView.Sort = "a_reporting_account_code_c"; // These two sort orders
FilteredResults.DefaultView.Sort = "AccountCode"; // Are required by AddTotalsToParentAccountRow, below.
Int32 PostingAccountRecords = FilteredResults.Rows.Count;
TLogging.Log(Catalog.GetString("Summarise to parent accounts.."), TLoggingType.ToStatusBar);
for (Int32 Idx = 0; Idx < PostingAccountRecords; Idx++)
{
if (DbAdapter.IsCancelled)
{
return;
}
DataRow Row = FilteredResults.Rows[Idx];
String ParentAccountPath;
Int32 ParentAccountTypeOrder;
Int32 AccountLevel = AddTotalsToParentAccountRow(
FilteredResults,
HierarchyTbl,
LedgerNumber,
"", // No Cost Centres on Balance Sheet
Row["AccountCode"].ToString(),
Row,
false,
0,
out ParentAccountPath,
out ParentAccountTypeOrder,
ReadTrans);
Row["AccountLevel"] = AccountLevel;
Row["AccountPath"] = ParentAccountPath + Row["AccountCode"];
}
}); // Get NewOrExisting AutoReadTransaction
//
// Now if I re-order the result by AccountPath, hide all the empty rows,
// and rows that are too detailed, it should be what I need!
Int32 DetailLevel = AParameters["param_nesting_depth"].ToInt32();
String DepthFilter = " AND AccountLevel<=" + DetailLevel.ToString();
FilteredResults.DefaultView.Sort = "AccountTypeOrder, AccountPath";
FilteredResults.DefaultView.RowFilter = "(Actual <> 0 OR LastYearActual <> 0 )" + // Only non-zero rows
DepthFilter; // Nothing too detailed
FilteredResults = FilteredResults.DefaultView.ToTable("BalanceSheet");
//
// The income and expense accounts have been used to produce the balance of 'PL',
// but now I don't want to see those details - only the total.
FilteredResults.DefaultView.RowFilter = "AccountPath NOT LIKE '%-PL~%' OR AccountLevel < 3"; // Don't include Children of PL account
FilteredResults = FilteredResults.DefaultView.ToTable("BalanceSheet");
//
// Finally, to make the hierarchical report possible,
// I want to include a note to show whether a row has child rows,
// and if it does, I'll copy this row to a new row, below the children, marking the new row as "footer".
TLogging.Log(Catalog.GetString("Format data for reporting.."), TLoggingType.ToStatusBar);
for (Int32 RowIdx = 0; RowIdx < FilteredResults.Rows.Count - 1; RowIdx++)
{
if (DbAdapter.IsCancelled)
{
return FilteredResults;
}
Int32 ParentAccountLevel = Convert.ToInt32(FilteredResults.Rows[RowIdx]["AccountLevel"]);
Boolean HasChildren = (Convert.ToInt32(FilteredResults.Rows[RowIdx + 1]["AccountLevel"]) > ParentAccountLevel);
FilteredResults.Rows[RowIdx]["HasChildren"] = HasChildren;
if (HasChildren)
{
Int32 NextSiblingPos = -1;
for (Int32 ChildIdx = RowIdx + 2; ChildIdx < FilteredResults.Rows.Count; ChildIdx++)
{
if (Convert.ToInt32(FilteredResults.Rows[ChildIdx]["AccountLevel"]) <= ParentAccountLevel) // This row is not a child of mine
{ // so I insert my footer before here.
NextSiblingPos = ChildIdx;
break;
}
}
DataRow FooterRow = FilteredResults.NewRow();
DataUtilities.CopyAllColumnValues(FilteredResults.Rows[RowIdx], FooterRow);
FooterRow["ParentFooter"] = true;
FooterRow["HasChildren"] = false;
if (NextSiblingPos > 0)
{
FilteredResults.Rows.InsertAt(FooterRow, NextSiblingPos);
}
else
{
FilteredResults.Rows.Add(FooterRow);
}
}
} // for
TLogging.Log("", TLoggingType.ToStatusBar);
return FilteredResults;
} // Balance Sheet Table
public static DataTable SurplusDeficitTable(Dictionary <String, TVariant>AParameters, TReportingDbAdapter DbAdapter)
{
Int32 LedgerNumber = AParameters["param_ledger_number_i"].ToInt32();
Int32 AccountingYear = AParameters["param_year_i"].ToInt32();
Int32 ReportPeriodEnd = AParameters["param_end_period_i"].ToInt32();
// Read different DB fields according to currency setting
//String ActualFieldName = AParameters["param_currency"].ToString().StartsWith("Int") ? "a_actual_intl_n" : "a_actual_base_n";
String CostCentreFilter;
String AccountCodeFilter;
// create filters from parameters
AccountAndCostCentreFilters(AParameters, out CostCentreFilter, out AccountCodeFilter);
DataTable resultTable = null;
TDBTransaction ReadTrans = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref ReadTrans,
delegate
{
TCorporateExchangeRateCache ExchangeRateCache = new TCorporateExchangeRateCache();
decimal ExchangeRate = 1;
if (AParameters["param_currency"].ToString().StartsWith("Int"))
{
ExchangeRate = ExchangeRateCache.GetCorporateExchangeRate(DBAccess.GDBAccessObj,
LedgerNumber,
AccountingYear,
ReportPeriodEnd,
-1);
}
String OrderBy = " ORDER BY glm.a_cost_centre_code_c";
String Query = "SELECT " +
" glm.a_year_i AS Year," +
" glmp.a_period_number_i AS Period," +
" glm.a_cost_centre_code_c AS CostCentreCode," +
" a_cost_centre.a_cost_centre_name_c AS CostCentreName," +
" a_cost_centre.a_cost_centre_type_c AS CostCentreType," +
" SUM (CASE" +
" WHEN (a_account.a_debit_credit_indicator_l = false AND glmp.a_actual_base_n > 0)" +
" OR (a_account.a_debit_credit_indicator_l = true AND glmp.a_actual_base_n < 0)" +
" THEN ABS(glmp.a_actual_base_n) * " + ExchangeRate +
" ELSE 0 END) AS Credit," +
" SUM (CASE" +
" WHEN (a_account.a_debit_credit_indicator_l = true AND glmp.a_actual_base_n > 0)" +
" OR (a_account.a_debit_credit_indicator_l = false AND glmp.a_actual_base_n < 0)" +
" THEN ABS(glmp.a_actual_base_n) * " + ExchangeRate +
" ELSE 0 END) AS Debit" +
" FROM a_general_ledger_master AS glm, a_general_ledger_master_period AS glmp, a_account, a_cost_centre" +
" WHERE glm.a_ledger_number_i=" + LedgerNumber +
" AND glm.a_year_i=" + AccountingYear +
" AND glm.a_glm_sequence_i = glmp.a_glm_sequence_i" +
" AND glmp.a_period_number_i=" + ReportPeriodEnd +
" AND glmp.a_actual_base_n <> 0" +
" AND a_account.a_account_code_c = glm.a_account_code_c" +
" AND a_account.a_ledger_number_i = glm.a_ledger_number_i" +
" AND a_account.a_posting_status_l = true" +
" AND a_cost_centre.a_ledger_number_i = glm.a_ledger_number_i" +
" AND a_cost_centre.a_cost_centre_code_c = glm.a_cost_centre_code_c" +
" AND a_cost_centre.a_posting_cost_centre_flag_l = true" +
CostCentreFilter +
AccountCodeFilter +
" GROUP BY Year, Period, CostCentreCode, CostCentreName, CostCentreType " +
OrderBy;
TLogging.Log(Catalog.GetString("Loading data.."), TLoggingType.ToStatusBar);
resultTable = DbAdapter.RunQuery(Query, "SurplusDeficitTable", ReadTrans);
TLogging.Log("", TLoggingType.ToStatusBar);
}); // Get NewOrExisting AutoReadTransaction
return resultTable;
} // Surplus Deficit Table
public static DataTable BalanceSheetTable(Dictionary <String, TVariant>AParameters, TReportingDbAdapter DbAdapter)
{
DataTable FilteredResults = null;
/* Required columns:
* Actual
* ActualLastYear
* ActualLastYearComplete
*/
Int32 LedgerNumber = AParameters["param_ledger_number_i"].ToInt32();
Int32 AccountingYear = AParameters["param_year_i"].ToInt32();
Int32 ReportPeriodEnd = AParameters["param_end_period_i"].ToInt32();
String HierarchyName = AParameters["param_account_hierarchy_c"].ToString();
String RootCostCentre = AParameters["param_cost_centre_code"].ToString();
Boolean International = AParameters["param_currency"].ToString().StartsWith("Int");
Decimal EffectiveExchangeRate = 1;
Decimal LastYearExchangeRate = 1;
String ActualFieldName = "a_actual_base_n";
String StartBalanceFieldName = "a_start_balance_base_n";
if (International)
{
TCorporateExchangeRateCache ExchangeRateCache = new TCorporateExchangeRateCache();
EffectiveExchangeRate = ExchangeRateCache.GetCorporateExchangeRate(DBAccess.GDBAccessObj,
LedgerNumber,
AccountingYear,
ReportPeriodEnd,
-1);
LastYearExchangeRate = ExchangeRateCache.GetCorporateExchangeRate(DBAccess.GDBAccessObj,
LedgerNumber,
AccountingYear - 1,
ReportPeriodEnd,
-1);
}
String PlAccountCode = "PL"; // I could get this from the Ledger record, but in fact it's never set there!
TDBTransaction ReadTrans = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
String Query = "SELECT DISTINCT" +
" 1 AS AccountLevel," +
" false AS HasChildren," +
" false AS ParentFooter," +
" glm.a_glm_sequence_i AS Seq," +
" glm.a_year_i AS Year," +
" glmp.a_period_number_i AS Period," +
" a_account.a_account_type_c AS AccountType," +
" 0 AS AccountTypeOrder," +
" a_account.a_debit_credit_indicator_l AS DebitCredit," +
" glm.a_account_code_c AS AccountCode," +
" glm.a_cost_centre_code_c AS CostCentreCode," +
" false AS AccountIsSummary," +
" 'Path' AS AccountPath," +
" a_account.a_account_code_short_desc_c AS AccountName," +
" glm." + StartBalanceFieldName + " AS YearStart," +
" 0.0 AS ActualYTD," +
" glmp." + ActualFieldName + " AS Actual," +
" 0.0 AS ActualLastYear" +
" FROM a_general_ledger_master AS glm, a_general_ledger_master_period AS glmp, a_account, a_cost_centre" +
" WHERE glm.a_ledger_number_i=" + LedgerNumber +
" AND glm.a_cost_centre_code_c = '" + RootCostCentre + "' " +
" AND glm.a_year_i IN (" + (AccountingYear - 1) + ", " + AccountingYear + ")" +
" AND glm.a_glm_sequence_i = glmp.a_glm_sequence_i" +
" AND glmp.a_period_number_i=" + ReportPeriodEnd +
" AND a_account.a_account_code_c = glm.a_account_code_c" +
" AND (a_account.a_account_type_c IN ('Asset','Liability','Equity') OR a_account.a_account_code_c = '" +
PlAccountCode + "')" +
" AND a_account.a_ledger_number_i = glm.a_ledger_number_i" +
" AND (a_account.a_posting_status_l = true OR a_account.a_account_code_c = '" + PlAccountCode + "')" +
" ORDER BY glm.a_account_code_c"
;
TLogging.Log(Catalog.GetString("Loading data.."), TLoggingType.ToStatusBar);
DataTable resultTable = DbAdapter.RunQuery(Query, "BalanceSheet", ReadTrans);
DataView OldPeriod = new DataView(resultTable);
DataView ThisMonth = new DataView(resultTable);
OldPeriod.Sort = "AccountCode";
OldPeriod.RowFilter = String.Format("Year={0}", AccountingYear - 1);
ThisMonth.RowFilter = String.Format("Year={0}", AccountingYear);
//
// Some of these rows are from a year ago. I'll copy those into the current period "LastYear" fields.
TLogging.Log(Catalog.GetString("Get last year data.."), TLoggingType.ToStatusBar);
foreach (DataRowView rv in ThisMonth)
{
if (DbAdapter.IsCancelled)
{
return FilteredResults;
}
DataRow Row = rv.Row;
Int32 RowIdx = OldPeriod.Find(
new Object[] {
Row["AccountCode"]
}
);
if (RowIdx >= 0)
{
DataRow LastYearRow = OldPeriod[RowIdx].Row;
Row["ActualLastYear"] = LastYearExchangeRate * Convert.ToDecimal(LastYearRow["Actual"]);
}
if (Row["AccountCode"].ToString() == PlAccountCode) // Tweak the PL account and pretend it's an Equity.
{ // (It was probably previously an income account.)
Row["AccountType"] = "Equity";
Row["AccountTypeOrder"] = 3;
}
Row["YearStart"] = EffectiveExchangeRate * Convert.ToDecimal(Row["YearStart"]);
Row["Actual"] = EffectiveExchangeRate * Convert.ToDecimal(Row["Actual"]);
}
//
// So now I don't have to look at last year's rows:
FilteredResults = ThisMonth.ToTable("BalanceSheet");
//
// I only have "posting accounts" - I need to add the summary accounts.
AAccountHierarchyDetailTable HierarchyTbl = AAccountHierarchyDetailAccess.LoadViaAAccountHierarchy(LedgerNumber,
HierarchyName,
ReadTrans);
HierarchyTbl.DefaultView.Sort = "a_reporting_account_code_c"; // These two sort orders
FilteredResults.DefaultView.Sort = "AccountCode"; // Are required by AddTotalsToParentAccountRow, below.
Int32 PostingAccountRecords = FilteredResults.Rows.Count;
TLogging.Log(Catalog.GetString("Summarise to parent accounts.."), TLoggingType.ToStatusBar);
for (Int32 Idx = 0; Idx < PostingAccountRecords; Idx++)
{
if (DbAdapter.IsCancelled)
{
return FilteredResults;
}
DataRow Row = FilteredResults.Rows[Idx];
String ParentAccountPath;
Int32 ParentAccountTypeOrder;
Int32 AccountLevel = AddTotalsToParentAccountRow(
FilteredResults,
HierarchyTbl,
LedgerNumber,
"", // No Cost Centres on Balance Sheet
Row["AccountCode"].ToString(),
Row,
false,
false,
out ParentAccountPath,
out ParentAccountTypeOrder,
ReadTrans);
Row["AccountLevel"] = AccountLevel;
Row["AccountPath"] = ParentAccountPath + Row["AccountCode"];
}
//
// Now if I re-order the result by AccountPath, hide all the old data and empty rows, and rows that are too detailed, it should be what I need!
Int32 DetailLevel = AParameters["param_nesting_depth"].ToInt32();
String DepthFilter = " AND AccountLevel<=" + DetailLevel.ToString();
FilteredResults.DefaultView.Sort = "AccountTypeOrder, AccountPath";
FilteredResults.DefaultView.RowFilter = "(Actual <> 0 OR ActualLastYear <> 0 )" + // Only non-zero rows
DepthFilter; // Nothing too detailed
FilteredResults = FilteredResults.DefaultView.ToTable("BalanceSheet");
//
// Finally, to make the hierarchical report possible,
// I want to include a note to show whether a row has child rows,
// and if it does, I'll copy this row to a new row, below the children, marking the new row as "footer".
TLogging.Log(Catalog.GetString("Format data for reporting.."), TLoggingType.ToStatusBar);
for (Int32 RowIdx = 0; RowIdx < FilteredResults.Rows.Count - 1; RowIdx++)
{
if (DbAdapter.IsCancelled)
{
return FilteredResults;
}
Int32 ParentAccountLevel = Convert.ToInt32(FilteredResults.Rows[RowIdx]["AccountLevel"]);
Boolean HasChildren = (Convert.ToInt32(FilteredResults.Rows[RowIdx + 1]["AccountLevel"]) > ParentAccountLevel);
FilteredResults.Rows[RowIdx]["HasChildren"] = HasChildren;
if (HasChildren)
{
Int32 NextSiblingPos = -1;
for (Int32 ChildIdx = RowIdx + 2; ChildIdx < FilteredResults.Rows.Count; ChildIdx++)
{
if (Convert.ToInt32(FilteredResults.Rows[ChildIdx]["AccountLevel"]) <= ParentAccountLevel) // This row is not a child of mine
{ // so I insert my footer before here.
NextSiblingPos = ChildIdx;
break;
}
}
DataRow FooterRow = FilteredResults.NewRow();
DataUtilities.CopyAllColumnValues(FilteredResults.Rows[RowIdx], FooterRow);
FooterRow["ParentFooter"] = true;
FooterRow["HasChildren"] = false;
if (NextSiblingPos > 0)
{
FilteredResults.Rows.InsertAt(FooterRow, NextSiblingPos);
}
else
{
FilteredResults.Rows.Add(FooterRow);
}
}
} // for
} // try
catch (Exception ex) // if the report was cancelled, DB calls with the same transaction will raise exceptions.
{
TLogging.Log(ex.Message);
}
finally // Whatever happens, I need to do this:
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
TLogging.Log("", TLoggingType.ToStatusBar);
return FilteredResults;
} // Balance Sheet Table
public static DataTable IncomeExpenseTable(Dictionary <String, TVariant>AParameters, TReportingDbAdapter DbAdapter)
{
/* Required columns:
* CostCentreCode
* CostCentreName
* AccountType
* AccountLevel
* HasChildren
* Breakdown
* ParentFooter
* AccountPath
* AccountCode
* AccountName
* AccountIsSummary
* YearStart
* Actual
* ActualYTD
* LastYearActual
* LastYearActualYtd
* LastYearEnd
* Budget
* BudgetYTD
* LastYearBudget
* BudgetWholeYear
*/
/*
* Cost Centre Breakdown process, in English:
*
* Find all the transactions for this period (and last month, last year) in glmp, sorting by Account, CostCentre
* For each account, re-calculate the summary accounts, generating parent records and AccountPath, using the given hierarchy
* Summarise to the required detail level by copying into new "breakdown" records:
* Headers and footers at a lower level are just copied,
* Accounts at the highest level must be made into header/footer pairs. The totals should be correct.
* all transactions at the required detail level or higher must be combined by CostCentreCode and listed within the appropriate level account.
*
* The initial query and calculation of previous periods and budget figures is all the same; only the summarisation is different.
*/
/*
* "Whole year breakdown by period" process, in English:
*
* Find all the transactions for the whole year (to period 12) in glmp, sorting by CostCentre, Account
* For each account, summarise into 12 fields of summary accounts, generating parent records and AccountPath, using the given hierarchy
* Summarise to the required level of detail
* For each remaining posting account, create a "breakdown" record with 12 fields for the summation
* Remove all records that are not a summary or a breakdown
*/
Int32 LedgerNumber = AParameters["param_ledger_number_i"].ToInt32();
Int32 NumberOfAccountingPeriods = new TLedgerInfo(LedgerNumber).NumberOfAccountingPeriods;
Int32 AccountingYear = AParameters["param_year_i"].ToInt32();
Int32 ReportPeriodStart = AParameters["param_start_period_i"].ToInt32();
Int32 ReportPeriodEnd = AParameters["param_end_period_i"].ToInt32();
String HierarchyName = AParameters["param_account_hierarchy_c"].ToString();
Boolean International = AParameters["param_currency"].ToString().StartsWith("Int");
Decimal EffectiveExchangeRate = 1;
Decimal LastYearExchangeRate = 1;
if (International)
{
TCorporateExchangeRateCache ExchangeRateCache = new TCorporateExchangeRateCache();
EffectiveExchangeRate = ExchangeRateCache.GetCorporateExchangeRate(DBAccess.GDBAccessObj,
LedgerNumber,
AccountingYear,
ReportPeriodEnd,
-1);
LastYearExchangeRate = ExchangeRateCache.GetCorporateExchangeRate(DBAccess.GDBAccessObj,
LedgerNumber,
AccountingYear - 1,
ReportPeriodEnd,
-1);
}
//
// Read different DB fields according to currency setting
Boolean CostCentreBreakdown = AParameters["param_cost_centre_breakdown"].ToBool();
Boolean WholeYearPeriodsBreakdown = AParameters["param_period_breakdown"].ToBool();
List <String>SelectedCostCentres = GetCostCentreList(AParameters);
DataTable FilteredResults = new DataTable();
FilteredResults.TableName = "IncomeExpense";
TDBTransaction ReadTrans = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(
IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref ReadTrans,
delegate
{
String YearFilter = // Get last year's values too.
" AND glm.a_year_i>=" + (AccountingYear - 1) +
" AND glm.a_year_i<=" + AccountingYear;
String isThisYear = " Year=" + AccountingYear;
String isLastYear = " Year=" + (AccountingYear - 1);
String PeriodFilter = " AND glmp.a_period_number_i<=" + NumberOfAccountingPeriods; // I need the whole year to see "whole year budget".
String isEndPeriod = "Period=" + ReportPeriodEnd;
String isPrevPeriod = "Period=" + (ReportPeriodStart - 1);
String ActualYtdQuery = "SUM (CASE WHEN " + isThisYear + " AND " + isEndPeriod + " THEN ActualYTD ELSE 0 END) AS ActualYtd, ";
String PrevPeriodQuery = (ReportPeriodStart == 1) ?
"SUM (CASE WHEN " + isThisYear + " THEN StartBalance ELSE 0 END) AS LastMonthYtd, "
: "SUM (CASE WHEN " + isThisYear + " AND " + isPrevPeriod +
" THEN ActualYTD ELSE 0 END) AS LastMonthYtd, ";
String LastYearActualYtdQuery = "SUM (CASE WHEN " + isLastYear + " AND " + isEndPeriod +
" THEN ActualYTD ELSE 0 END) AS LastYearActualYtd, ";
String LastYearPrevPeriodQuery = (ReportPeriodStart == 1) ?
"SUM (CASE WHEN " + isLastYear + " THEN StartBalance ELSE 0 END) AS LastYearLastMonthYtd, "
: "SUM (CASE WHEN " + isLastYear + " AND " + isPrevPeriod +
" THEN ActualYTD ELSE 0 END) AS LastYearLastMonthYtd, ";
String LastYearEndQuery = "SUM (CASE WHEN " + isLastYear + " THEN EndBalance ELSE 0 END) AS LastYearEnd, ";
String BudgetQuery = "SUM (CASE WHEN " + isThisYear + " AND Period>=" + ReportPeriodStart + " AND Period <= " + ReportPeriodEnd +
" THEN Budget ELSE 0 END) AS Budget, ";
String BudgetYtdQuery = "SUM (CASE WHEN " + isThisYear + " AND Period<=" + ReportPeriodEnd +
" THEN Budget ELSE 0 END) AS BudgetYTD, ";
String BudgetWholeYearQuery = "SUM (CASE WHEN " + isThisYear + " THEN Budget ELSE 0 END) AS WholeYearBudget, ";
String BudgetLastYearQuery = "SUM (CASE WHEN " + isLastYear + " THEN Budget ELSE 0 END) AS LastYearBudget, ";
String MonthlyBreakdownQuery =
"0.0 AS P1, 0.0 AS P2, 0.0 AS P3, 0.0 AS P4, 0.0 AS P5, 0.0 AS P6 , 0.0 AS P7, 0.0 AS P8, 0.0 AS P9, 0.0 AS P10, 0.0 AS P11, 0.0 AS P12 ";
String NoZeroesFilter =
"WHERE (LastMonthYtd != 0 OR ActualYtd != 0 OR Budget != 0 OR BudgetYTD != 0 OR WholeYearBudget != 0 OR LastYearBudget != 0 OR LastYearLastMonthYtd != 0 OR LastYearActualYtd != 0)";
if (WholeYearPeriodsBreakdown)
{
//TODO: Calendar vs Financial Date Handling - Check if this should use financial num periods and not assume 12
CostCentreBreakdown = false; // Hopefully the client will have ensured this is false anyway - I'm just asserting it!
MonthlyBreakdownQuery =
"SUM (CASE WHEN Period=1 THEN ActualYTD ELSE 0 END) AS P1, " +
"SUM (CASE WHEN Period=2 THEN ActualYTD ELSE 0 END) AS P2, " +
"SUM (CASE WHEN Period=3 THEN ActualYTD ELSE 0 END) AS P3, " +
"SUM (CASE WHEN Period=4 THEN ActualYTD ELSE 0 END) AS P4, " +
"SUM (CASE WHEN Period=5 THEN ActualYTD ELSE 0 END) AS P5, " +
"SUM (CASE WHEN Period=6 THEN ActualYTD ELSE 0 END) AS P6, " +
"SUM (CASE WHEN Period=7 THEN ActualYTD ELSE 0 END) AS P7, " +
"SUM (CASE WHEN Period=8 THEN ActualYTD ELSE 0 END) AS P8, " +
"SUM (CASE WHEN Period=9 THEN ActualYTD ELSE 0 END) AS P9, " +
"SUM (CASE WHEN Period=10 THEN ActualYTD ELSE 0 END) AS P10, " +
"SUM (CASE WHEN Period=11 THEN ActualYTD ELSE 0 END) AS P11, " +
"SUM (CASE WHEN Period=12 THEN ActualYTD ELSE 0 END) AS P12 "; // No comma because this is the last field!
ActualYtdQuery = "0 AS ActualYtd, ";
PrevPeriodQuery = "0.0 AS LastMonthYtd, ";
LastYearActualYtdQuery = "0 AS LastYearActualYtd, ";
LastYearPrevPeriodQuery = "0.0 AS LastYearLastMonthYtd, ";
LastYearEndQuery = "0.0 AS LastYearEnd, ";
BudgetQuery = "0.0 AS Budget,";
BudgetYtdQuery = "0.0 AS BudgetYTD,";
BudgetWholeYearQuery = "0.0 AS WholeYearBudget, ";
BudgetLastYearQuery = "0.0 AS LastYearBudget, ";
YearFilter = " AND glm.a_year_i=" + AccountingYear;
PeriodFilter = " AND glmp.a_period_number_i<=12";
NoZeroesFilter = "WHERE (P1<>0 OR P2<>0 OR P3<>0 OR P4<>0 OR P5<>0 OR P6<>0 " +
"OR P7<>0 OR P8<>0 OR P9<>0 OR P10<>0 OR P11<>0 OR P12<>0) "; // No blank rows
}
TLogging.Log(Catalog.GetString("Loading data.."), TLoggingType.ToStatusBar);
//
// I can't use summary rows in GLM. Each Summary Cost Centre must be expressed as the sum of all the posting Cost Centres it represents.
// Accordingly, the query below is called for each Cost Centre, and the results appended into one table.
foreach (String ParentCC in SelectedCostCentres)
{
String[] Parts = ParentCC.Split(',');
String CostCentreFilter = GetReportingCostCentres(LedgerNumber, Parts[0], "");
CostCentreFilter = CostCentreFilter.Replace(",",
"','"); // SQL IN List items in single quotes
CostCentreFilter = " AND glm.a_cost_centre_code_c in ('" + CostCentreFilter + "') ";
String AllGlmp = // This query fetches all the data I need from GLM and GLMP
"(SELECT a_account.a_account_code_c AS AccountCode, a_account.a_account_type_c AS AccountType, " +
"a_account.a_account_code_short_desc_c AS AccountName, " +
"CASE a_account.a_account_type_c WHEN 'Income' THEN 1 WHEN 'Expense' THEN 2 END AS AccountTypeOrder, " +
"a_account.a_debit_credit_indicator_l AS DebitCredit, " +
"glm.a_year_i AS Year, " +
"glm.a_start_balance_base_n AS StartBalance, " +
"glm.a_closing_period_actual_base_n AS EndBalance, " +
"glmp.a_period_number_i AS Period, " +
"glmp.a_actual_base_n AS ActualYTD, " +
"glmp.a_budget_base_n AS Budget " +
"FROM a_general_ledger_master AS glm, a_general_ledger_master_period AS glmp, a_account " +
"WHERE " +
"glm.a_ledger_number_i=" + LedgerNumber + " " +
YearFilter +
PeriodFilter +
" AND glm.a_glm_sequence_i = glmp.a_glm_sequence_i" +
" AND a_account.a_account_code_c = glm.a_account_code_c" +
" AND (a_account.a_account_type_c = 'Income' OR a_account.a_account_type_c = 'Expense') AND a_account.a_ledger_number_i = glm.a_ledger_number_i "
+
"AND a_account.a_posting_status_l = true " +
CostCentreFilter +
"AND (glmp.a_actual_base_n != 0 OR glmp.a_budget_base_n != 0) " +
") AS AllGlmp ";
String Summarised = // This query reduces the result set from AllGlmp
"(SELECT " +
"AccountCode, AccountType, AccountName, DebitCredit, " +
"SUM (CASE WHEN " + isThisYear + " THEN StartBalance ELSE 0 END) AS YearStart, " +
PrevPeriodQuery +
ActualYtdQuery +
LastYearActualYtdQuery +
LastYearPrevPeriodQuery +
LastYearEndQuery +
BudgetQuery +
BudgetYtdQuery +
BudgetWholeYearQuery +
BudgetLastYearQuery +
"AccountTypeOrder, " +
MonthlyBreakdownQuery +
"FROM " +
AllGlmp +
"GROUP BY AccountType, AccountCode, AccountName, DebitCredit, AccountTypeOrder " +
") AS Summarised ";
String Query = "SELECT " + // This query adds extra columns to Summarised
" '" + Parts[0].Replace("'", "''") + "' AS CostCentreCode," +
" '" + Parts[1].Replace("'",
"''") + "' AS CostCentreName," +
"Summarised.*, " +
"ActualYtd - LastMonthYtd AS Actual, " +
"LastYearActualYtd - LastYearLastMonthYtd AS LastYearActual, " +
"1 AS AccountLevel, false AS HasChildren, false AS ParentFooter, false AS AccountIsSummary, false AS Breakdown,'Path' AS AccountPath "
+
"FROM " +
Summarised +
NoZeroesFilter +
"ORDER BY AccountTypeOrder, AccountCode ";
FilteredResults.Merge(DbAdapter.RunQuery(Query, "IncomeExpense", ReadTrans));
if (DbAdapter.IsCancelled)
{
return;
}
} // foreach ParentCC
if (CostCentreBreakdown) // I need to re-order the resulting table by Account:
{
FilteredResults.DefaultView.Sort = "AccountCode";
FilteredResults = FilteredResults.DefaultView.ToTable();
}
//
// I only have "posting accounts" - I need to add the summary accounts.
TLogging.Log(Catalog.GetString("Summarise to parent accounts.."), TLoggingType.ToStatusBar);
AAccountHierarchyDetailTable HierarchyTbl = AAccountHierarchyDetailAccess.LoadViaAAccountHierarchy(LedgerNumber,
HierarchyName,
ReadTrans);
HierarchyTbl.DefaultView.Sort = "a_reporting_account_code_c"; // These two sort orders
if (CostCentreBreakdown) // Are required by AddTotalsToParentAccountRow, below.
{
FilteredResults.DefaultView.Sort = "AccountCode";
}
else
{
FilteredResults.DefaultView.Sort = "CostCentreCode, AccountCode";
}
Int32 PostingAccountRecords = FilteredResults.Rows.Count;
for (Int32 Idx = 0; Idx < PostingAccountRecords; Idx++)
{
if (DbAdapter.IsCancelled)
{
return;
}
DataRow Row = FilteredResults.Rows[Idx];
if (WholeYearPeriodsBreakdown) // The query gave me YTD values; I need monthly actuals.
{
for (Int32 i = NumberOfAccountingPeriods; i > 1; i--)
{
Row["P" + i] = Convert.ToDecimal(Row["P" + i]) - Convert.ToDecimal(Row["P" + (i - 1)]);
}
Row["P1"] = Convert.ToDecimal(Row["P1"]) - Convert.ToDecimal(Row["YearStart"]);
}
String CostCentreParam = (CostCentreBreakdown) ? "" : Row["CostCentreCode"].ToString();
String ParentAccountPath;
Int32 ParentAccountTypeOrder;
Int32 AccountLevel = AddTotalsToParentAccountRow(
FilteredResults,
HierarchyTbl,
LedgerNumber,
CostCentreParam,
Row["AccountCode"].ToString(),
Row,
CostCentreBreakdown,
(WholeYearPeriodsBreakdown) ? NumberOfAccountingPeriods : 0,
out ParentAccountPath,
out ParentAccountTypeOrder,
ReadTrans);
Row["AccountLevel"] = AccountLevel;
Row["AccountPath"] = ParentAccountPath + "~" + Row["AccountCode"];
}
//
// Now if I re-order the result, and hide any rows that are empty or too detailed, it should be what I need!
Int32 DetailLevel = AParameters["param_nesting_depth"].ToInt32();
if (CostCentreBreakdown)
{
TLogging.Log(Catalog.GetString("Get Cost Centre Breakdown.."), TLoggingType.ToStatusBar);
// I'm creating additional "breakdown" records for the per-CostCentre breakdown, and potentially removing
// some records that were summed into those "breakdown" records.
FilteredResults.DefaultView.Sort = "AccountType DESC, AccountPath ASC, CostCentreCode";
FilteredResults.DefaultView.RowFilter = "Breakdown=false";
// At this point I need to add together any transactions in more detailed levels, summarising them by Cost Centre,
// and listing them under the account to which they relate:
DataView SummaryView = new DataView(FilteredResults);
SummaryView.Sort = "AccountTypeOrder, AccountPath ASC, CostCentreCode";
SummaryView.RowFilter = "Breakdown=true";
DataRow AccumulatingRow = FilteredResults.NewRow(); // This temporary row is not part of the result set - it's just a line of temporary vars.
for (Int32 RowIdx = 0; RowIdx < FilteredResults.DefaultView.Count; RowIdx++)
{
if (DbAdapter.IsCancelled)
{
return;
}
DataRow DetailRow = FilteredResults.DefaultView[RowIdx].Row;
AddToCostCentreBreakdownSummary(SummaryView, DetailLevel, DetailRow);
//
// For posting accounts in "details" view, the cost centre breakdown rows will be presented after one or more rows with the same account.
// The last account row will become a header, below, and any other rows with the same account will be removed.
// So I need the values in those rows to accumulate into the last row.
AccumulateTotalsPerCostCentre(DetailRow, AccumulatingRow);
}
FilteredResults.DefaultView.Sort = "AccountTypeOrder, AccountPath ASC, Breakdown, CostCentreCode";
} // if (CostCentreBreakdown)
else
{
FilteredResults.DefaultView.Sort = "CostCentreCode, AccountTypeOrder, AccountPath ASC";
}
FilteredResults.DefaultView.RowFilter = "AccountLevel<=" + DetailLevel.ToString(); // Nothing too detailed
FilteredResults = FilteredResults.DefaultView.ToTable("IncomeExpense");
//
// Finally, to make the hierarchical report possible,
// I want to include a note to show whether a row has child rows,
// and if it does, I'll copy this row to a new row, below the children, marking the new row as "footer".
TLogging.Log(Catalog.GetString("Format data for reporting.."), TLoggingType.ToStatusBar);
for (Int32 RowIdx = 0; RowIdx < FilteredResults.Rows.Count - 1; RowIdx++)
{
if (DbAdapter.IsCancelled)
{
return;
}
Int32 ParentAccountLevel = Convert.ToInt32(FilteredResults.Rows[RowIdx]["AccountLevel"]);
Boolean HasChildren = (Convert.ToInt32(FilteredResults.Rows[RowIdx + 1]["AccountLevel"]) > ParentAccountLevel)
|| (Convert.ToBoolean(FilteredResults.Rows[RowIdx]["Breakdown"]) == false
&& Convert.ToBoolean(FilteredResults.Rows[RowIdx + 1]["Breakdown"]) == true);
FilteredResults.Rows[RowIdx]["HasChildren"] = HasChildren;
if (HasChildren)
{
if (CostCentreBreakdown)
{
//
// Header and footer rows do not have Cost Centres -
// The Cost Centre fields were used for sorting, but they're misleading so I'll remove them here:
FilteredResults.Rows[RowIdx]["CostCentreCode"] = "";
FilteredResults.Rows[RowIdx]["CostCentreName"] = "";
}
Int32 NextSiblingPos = -1;
for (Int32 ChildIdx = RowIdx + 2; ChildIdx < FilteredResults.Rows.Count; ChildIdx++)
{
if ((Convert.ToInt32(FilteredResults.Rows[ChildIdx]["AccountLevel"]) <= ParentAccountLevel)
&& (Convert.ToBoolean(FilteredResults.Rows[ChildIdx]["Breakdown"]) == false)) // This row is not a child of mine
{ // so I insert my footer before here.
NextSiblingPos = ChildIdx;
break;
}
}
DataRow FooterRow = FilteredResults.NewRow();
DataUtilities.CopyAllColumnValues(FilteredResults.Rows[RowIdx], FooterRow);
FooterRow["ParentFooter"] = true;
FooterRow["HasChildren"] = false;
if (NextSiblingPos > 0)
{
FilteredResults.Rows.InsertAt(FooterRow, NextSiblingPos);
}
else
{
FilteredResults.Rows.Add(FooterRow);
}
}
}
// For "Cost Centre Breakdown", the only transactions I want to see are the "breakdown" rows I've added.
// Everything else is removed unless it's a header or footer:
if (CostCentreBreakdown)
{
FilteredResults.DefaultView.RowFilter = "Breakdown=true OR HasChildren=true OR ParentFooter=true";
FilteredResults = FilteredResults.DefaultView.ToTable("IncomeExpense");
}
if (EffectiveExchangeRate != 1)
{
if (WholeYearPeriodsBreakdown)
{
foreach (DataRow Row in FilteredResults.Rows)
{
Row["p1"] = Convert.ToDecimal(Row["p1"]) * EffectiveExchangeRate;
Row["p2"] = Convert.ToDecimal(Row["p2"]) * EffectiveExchangeRate;
Row["p3"] = Convert.ToDecimal(Row["p3"]) * EffectiveExchangeRate;
Row["p4"] = Convert.ToDecimal(Row["p4"]) * EffectiveExchangeRate;
Row["p5"] = Convert.ToDecimal(Row["p5"]) * EffectiveExchangeRate;
Row["p6"] = Convert.ToDecimal(Row["p6"]) * EffectiveExchangeRate;
Row["p7"] = Convert.ToDecimal(Row["p7"]) * EffectiveExchangeRate;
Row["p8"] = Convert.ToDecimal(Row["p8"]) * EffectiveExchangeRate;
Row["p9"] = Convert.ToDecimal(Row["p9"]) * EffectiveExchangeRate;
Row["p10"] = Convert.ToDecimal(Row["p10"]) * EffectiveExchangeRate;
Row["p11"] = Convert.ToDecimal(Row["p11"]) * EffectiveExchangeRate;
Row["p12"] = Convert.ToDecimal(Row["p12"]) * EffectiveExchangeRate;
}
}
else
{
foreach (DataRow Row in FilteredResults.Rows)
{
Row["yearstart"] = Convert.ToDecimal(Row["yearstart"]) * EffectiveExchangeRate;
Row["lastmonthytd"] = Convert.ToDecimal(Row["lastmonthytd"]) * EffectiveExchangeRate;
Row["actualytd"] = Convert.ToDecimal(Row["actualytd"]) * EffectiveExchangeRate;
Row["LastYearlastmonthytd"] = Convert.ToDecimal(Row["LastYearlastmonthytd"]) * EffectiveExchangeRate;
Row["LastYearactualytd"] = Convert.ToDecimal(Row["LastYearactualytd"]) * EffectiveExchangeRate;
Row["LastYearEnd"] = Convert.ToDecimal(Row["LastYearEnd"]) * LastYearExchangeRate;
Row["budget"] = Convert.ToDecimal(Row["budget"]) * EffectiveExchangeRate;
Row["budgetytd"] = Convert.ToDecimal(Row["budgetytd"]) * EffectiveExchangeRate;
Row["wholeyearbudget"] = Convert.ToDecimal(Row["wholeyearbudget"]) * EffectiveExchangeRate;
Row["LastYearBudget"] = Convert.ToDecimal(Row["LastYearBudget"]) * LastYearExchangeRate;
Row["actual"] = Convert.ToDecimal(Row["actual"]) * EffectiveExchangeRate;
Row["LastYearactual"] = Convert.ToDecimal(Row["LastYearactual"]) * EffectiveExchangeRate;
}
}
}
TLogging.Log("", TLoggingType.ToStatusBar);
}); // Get NewOrExisting AutoReadTransaction
return FilteredResults;
} // Income Expense Table
public static DataTable IncomeExpenseTable(Dictionary <String, TVariant>AParameters, TReportingDbAdapter DbAdapter)
{
/* Required columns:
* CostCentreCode
* CostCentreName
* AccountType
* AccountLevel
* HasChildren
* Breakdown
* ParentFooter
* AccountPath
* AccountCode
* AccountName
* AccountIsSummary
* YearStart
* Actual
* ActualYTD
* ActualLastYear
* ActualLastYearComplete // not currently supported
* Budget
* BudgetYTD
* BudgetLastYear
* BudgetWholeYear
*/
/*
* Cost Centre Breakdown process, in English:
*
* Find all the transactions for this period (and last month, last year) in glmp, sorting by Account, CostCentre
* For each account, re-calculate the summary accounts, generating parent records and AccountPath, using the given hierarchy
* Summarise to the required detail level by copying into new "breakdown" records:
* Headers and footers at a lower level are just copied,
* Accounts at the highest level must be made into header/footer pairs. The totals should be correct.
* all transactions at the required detail level or higher must be combined by CostCentreCode and listed within the appropriate level account.
*
* The initial query and calculation of previous periods and budget figures is all the same; only the summarisation is different.
*/
/*
* "Whole year breakdown by period" process, in English:
*
* Find all the transactions for the whole year (to period 12) in glmp, sorting by CostCentre, Account
* For each account, summarise into 12 fields of summary accounts, generating parent records and AccountPath, using the given hierarchy
* Summarise to the required level of detail
* For each remaining posting account, create a "breakdown" record with 12 fields for the summation
* Remove all records that are not a summary or a breakdown
*/
Int32 LedgerNumber = AParameters["param_ledger_number_i"].ToInt32();
Int32 AccountingYear = AParameters["param_year_i"].ToInt32();
Int32 ReportPeriodStart = AParameters["param_start_period_i"].ToInt32();
Int32 ReportPeriodEnd = AParameters["param_end_period_i"].ToInt32();
Int32 PeriodMonths = 1 + (ReportPeriodEnd - ReportPeriodStart);
String HierarchyName = AParameters["param_account_hierarchy_c"].ToString();
Boolean International = AParameters["param_currency"].ToString().StartsWith("Int");
Decimal EffectiveExchangeRate = 1;
// Decimal LastYearExchangeRate = 1;
if (International)
{
TCorporateExchangeRateCache ExchangeRateCache = new TCorporateExchangeRateCache();
EffectiveExchangeRate = ExchangeRateCache.GetCorporateExchangeRate(DBAccess.GDBAccessObj,
LedgerNumber,
AccountingYear,
ReportPeriodEnd,
-1);
/*
* LastYearExchangeRate = ExchangeRateCache.GetCorporateExchangeRate(DBAccess.GDBAccessObj,
* LedgerNumber,
* AccountingYear - 1,
* ReportPeriodEnd,
* -1);
*/
}
//
// Read different DB fields according to currency setting
String ActualFieldName = /* International ? "a_actual_intl_n" : */ "a_actual_base_n";
String StartBalanceFieldName = /* International ? "a_start_balance_intl_n" : */ "a_start_balance_base_n";
String BudgetFieldName = /* International ? "a_budget_intl_n" : */ "a_budget_base_n";
Boolean CostCentreBreakdown = AParameters["param_cost_centre_breakdown"].ToBool();
Boolean WholeYearPeriodsBreakdown = AParameters["param_period_breakdown"].ToBool();
String CostCentreFilter = "";
String CostCentreOptions = AParameters["param_costcentreoptions"].ToString();
if (CostCentreOptions == "SelectedCostCentres")
{
String CostCentreList = AParameters["param_cost_centre_codes"].ToString();
CostCentreList = CostCentreList.Replace(",", "','"); // SQL IN List items in single quotes
CostCentreFilter = " AND glm.a_cost_centre_code_c in ('" + CostCentreList + "')";
}
if (CostCentreOptions == "CostCentreRange")
{
CostCentreFilter = " AND glm.a_cost_centre_code_c >='" + AParameters["param_cost_centre_code_start"].ToString() +
"' AND glm.a_cost_centre_code_c >='" + AParameters["param_cost_centre_code_end"].ToString() + "'";
}
if (CostCentreOptions == "AllActiveCostCentres") // THIS IS NOT SET AT ALL!
{
CostCentreFilter = " AND a_cost_centre.a_cost_centre_active_flag_l=true";
}
TDBTransaction ReadTrans = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadCommitted);
DataTable FilteredResults = null;
try
{
// To find the Budget YTD, I need to sum all the budget fields from the start of the year.
String BudgetQuery = (PeriodMonths == 1) ? " glmp." + BudgetFieldName + // For one month, the Budget is read directly from the record;
" AS Budget," :
" (CASE WHEN glm.a_year_i=" + AccountingYear + // for multiple months, I need to do a sum.
" AND a_period_number_i=" + ReportPeriodEnd +
" THEN (SELECT SUM(" + BudgetFieldName + ") FROM a_general_ledger_master_period" +
" WHERE a_glm_sequence_i= glm.a_glm_sequence_i " +
" AND a_period_number_i >= " + ReportPeriodStart +
" AND a_period_number_i <= " + ReportPeriodEnd +
" ) ELSE 0.0 END) AS Budget,";
String BudgetYtdQuery = " (CASE WHEN glm.a_year_i=" + AccountingYear +
" AND a_period_number_i=" + ReportPeriodEnd +
" THEN (SELECT SUM(" + BudgetFieldName + ") FROM a_general_ledger_master_period" +
" WHERE a_glm_sequence_i= glm.a_glm_sequence_i AND a_period_number_i <= " + ReportPeriodEnd +
" ) ELSE 0.0 END) AS BudgetYTD,";
String YearFilter =
" AND glm.a_year_i>=" + (AccountingYear - 1) +
" AND glm.a_year_i<=" + AccountingYear;
String PeriodFilter =
" AND glmp.a_period_number_i>=" + (ReportPeriodStart - PeriodMonths) +
" AND glmp.a_period_number_i<=" + ReportPeriodEnd;
String WholeYearBreakdownFields =
", 0.0 AS P1, 0.0 AS P2, 0.0 AS P3, 0.0 AS P4, 0.0 AS P5, 0.0 AS P6 " +
", 0.0 AS P7, 0.0 AS P8, 0.0 AS P9, 0.0 AS P10, 0.0 AS P11, 0.0 AS P12 ";
if (WholeYearPeriodsBreakdown)
{
CostCentreBreakdown = false; // Hopefully the client will have ensured this is false anyway - I'm just asserting it!
BudgetQuery = "0.0 AS Budget,";
BudgetYtdQuery = "0.0 AS BudgetYTD,";
YearFilter = " AND glm.a_year_i=" + AccountingYear;
PeriodFilter = " AND glmp.a_period_number_i<=12";
PeriodMonths = 12;
}
String Query = "SELECT DISTINCT" +
" 1 AS AccountLevel," +
" false AS HasChildren," +
" false AS ParentFooter," +
" false AS AccountIsSummary," +
" false AS Breakdown," +
" glm.a_glm_sequence_i AS Seq," +
" glm.a_year_i AS Year," +
" glmp.a_period_number_i AS Period," +
" glm.a_cost_centre_code_c AS CostCentreCode," +
" a_cost_centre.a_cost_centre_name_c AS CostCentreName," +
" a_account.a_account_type_c AS AccountType," +
" CASE a_account.a_account_type_c WHEN 'Income' THEN 1 WHEN 'Expense' THEN 2 END AS AccountTypeOrder," +
" a_account.a_debit_credit_indicator_l AS DebitCredit," +
" glm.a_account_code_c AS AccountCode," +
" 'Path' AS AccountPath," +
" a_account.a_account_code_short_desc_c AS AccountName," +
" glm." + StartBalanceFieldName + " AS YearStart," +
" 0.0 AS Actual," +
" glmp." + ActualFieldName + " AS ActualYTD," +
" 0.0 AS ActualLastYear," +
BudgetQuery +
BudgetYtdQuery +
" 0.0 AS BudgetLastYear," +
" 0.0 AS WholeYearBudget" +
WholeYearBreakdownFields +
" FROM a_general_ledger_master AS glm, a_general_ledger_master_period AS glmp, a_account, a_cost_centre" +
" WHERE glm.a_ledger_number_i=" + LedgerNumber +
YearFilter +
" AND glm.a_glm_sequence_i = glmp.a_glm_sequence_i" +
PeriodFilter +
" AND a_account.a_account_code_c = glm.a_account_code_c" +
" AND (a_account.a_account_type_c = 'Income' OR a_account.a_account_type_c = 'Expense')" +
" AND a_account.a_ledger_number_i = glm.a_ledger_number_i" +
" AND a_account.a_posting_status_l = true" +
" AND a_cost_centre.a_ledger_number_i = glm.a_ledger_number_i" +
" AND a_cost_centre.a_cost_centre_code_c = glm.a_cost_centre_code_c" +
CostCentreFilter;
if (CostCentreBreakdown)
{
Query += " ORDER BY glm.a_account_code_c, glm.a_cost_centre_code_c";
}
else
{
Query += " ORDER BY glm.a_cost_centre_code_c, glm.a_account_code_c";
}
TLogging.Log(Catalog.GetString("Loading data.."), TLoggingType.ToStatusBar);
DataTable resultTable = DbAdapter.RunQuery(Query, "IncomeExpense", ReadTrans);
if (WholeYearPeriodsBreakdown)
{
FilteredResults = resultTable;
}
else
{
//
// The table includes YTD balances, but I need the balance for the specified period.
TLogging.Log(Catalog.GetString("Calculate period transactions.."), TLoggingType.ToStatusBar);
DataView OldPeriod = new DataView(resultTable);
DataView ThisMonth = new DataView(resultTable);
ThisMonth.RowFilter = "Period=" + ReportPeriodEnd;
OldPeriod.Sort = "Year,Period,CostCentreCode,AccountCode";
//
// If I have rows for the previous month too, I can subtract the previous month's YTD balance to get my "Actual".
if (ReportPeriodEnd > PeriodMonths)
{
Int32 PrevPeriod = ReportPeriodEnd - PeriodMonths;
foreach (DataRowView rv in ThisMonth)
{
if (DbAdapter.IsCancelled)
{
return FilteredResults;
}
DataRow Row = rv.Row;
Int32 RowIdx = OldPeriod.Find(
new Object[] {
Row["Year"],
PrevPeriod,
Row["CostCentreCode"],
Row["AccountCode"]
}
);
DataRow PreviousPeriodRow = OldPeriod[RowIdx].Row;
Row["Actual"] =
(Convert.ToDecimal(Row["ActualYTD"]) - Convert.ToDecimal(PreviousPeriodRow["ActualYTD"])) * EffectiveExchangeRate;
}
}
else
{
//
// For the first period of the year, I can just subtract the YearStart balance, which I already have just here...
foreach (DataRowView rv in ThisMonth)
{
if (DbAdapter.IsCancelled)
{
return FilteredResults;
}
DataRow Row = rv.Row;
Row["Actual"] = (Convert.ToDecimal(Row["ActualYTD"]) - Convert.ToDecimal(Row["YearStart"])) * EffectiveExchangeRate;
}
}
//
// Some of these rows are from a year ago. I've updated their "Actual" values;
// now I'll copy those into the current period "LastYear" fields.
TLogging.Log(Catalog.GetString("Get Last year actuals.."), TLoggingType.ToStatusBar);
foreach (DataRowView rv in ThisMonth)
{
if (DbAdapter.IsCancelled)
{
return FilteredResults;
}
DataRow Row = rv.Row;
Int32 RowIdx = OldPeriod.Find(
new Object[] {
AccountingYear - 1,
ReportPeriodEnd,
Row["CostCentreCode"],
Row["AccountCode"]
}
);
if (RowIdx >= 0)
{
DataRow LastYearRow = OldPeriod[RowIdx].Row;
Row["ActualLastYear"] = Convert.ToDecimal(LastYearRow["Actual"]);
Row["BudgetLastYear"] = Convert.ToDecimal(LastYearRow["Budget"]) * EffectiveExchangeRate;
}
Row["Budget"] = Convert.ToDecimal(Row["Budget"]) * EffectiveExchangeRate;
}
//
// So now I don't have to look at last year's rows or last month's rows:
ThisMonth.RowFilter = "Year=" + AccountingYear + " AND Period=" + ReportPeriodEnd; // Only current period
FilteredResults = ThisMonth.ToTable("IncomeExpense");
//
// I need to add in the "whole year budget" field:
TLogging.Log(Catalog.GetString("Get Budgets.."), TLoggingType.ToStatusBar);
foreach (DataRow Row in FilteredResults.Rows)
{
if (DbAdapter.IsCancelled)
{
return FilteredResults;
}
Query = "SELECT SUM(" + BudgetFieldName +
") AS WholeYearBudget FROM a_general_ledger_master_period WHERE a_glm_sequence_i=" +
Convert.ToInt32(Row["Seq"]);
DataTable YearBudgetTbl = DbAdapter.RunQuery(Query, "YearBudget", ReadTrans);
if (YearBudgetTbl.Rows.Count > 0)
{
Row["WholeYearBudget"] = Convert.ToDecimal(YearBudgetTbl.Rows[0]["WholeYearBudget"]) * EffectiveExchangeRate;
}
}
} // If not Whole Year Periods Breakdown
//
// I only have "posting accounts" - I need to add the summary accounts.
TLogging.Log(Catalog.GetString("Summarise to parent accounts.."), TLoggingType.ToStatusBar);
AAccountHierarchyDetailTable HierarchyTbl = AAccountHierarchyDetailAccess.LoadViaAAccountHierarchy(LedgerNumber,
HierarchyName,
ReadTrans);
HierarchyTbl.DefaultView.Sort = "a_reporting_account_code_c"; // These two sort orders
if (CostCentreBreakdown) // Are required by AddTotalsToParentAccountRow, below.
{
FilteredResults.DefaultView.Sort = "AccountCode";
}
else
{
FilteredResults.DefaultView.Sort = "CostCentreCode, AccountCode";
}
Int32 PostingAccountRecords = FilteredResults.Rows.Count;
Decimal PreviousActualYTD = 0;
String ActualAccountCode = "";
for (Int32 Idx = 0; Idx < PostingAccountRecords; Idx++)
{
if (DbAdapter.IsCancelled)
{
return FilteredResults;
}
DataRow Row = FilteredResults.Rows[Idx];
if (WholeYearPeriodsBreakdown) // Because I missed out a chunk of code above, these rows have no "Actual" - only "ActualYTD".
{ // I need to calculate those Actuals before I go further.
if (Row["AccountCode"].ToString() != ActualAccountCode)
{
ActualAccountCode = Row["AccountCode"].ToString();
PreviousActualYTD = 0;
}
Row["Actual"] = Convert.ToDecimal(Row["ActualYTD"]) - PreviousActualYTD;
PreviousActualYTD = Convert.ToDecimal(Row["ActualYTD"]);
}
String CostCentreParam = (CostCentreBreakdown) ? "" : Row["CostCentreCode"].ToString();
String ParentAccountPath;
Int32 ParentAccountTypeOrder;
Int32 AccountLevel = AddTotalsToParentAccountRow(
FilteredResults,
HierarchyTbl,
LedgerNumber,
CostCentreParam,
Row["AccountCode"].ToString(),
Row,
CostCentreBreakdown,
WholeYearPeriodsBreakdown,
out ParentAccountPath,
out ParentAccountTypeOrder,
ReadTrans);
Row["AccountLevel"] = AccountLevel;
Row["AccountPath"] = ParentAccountPath + "~" + Row["AccountCode"];
}
//
// Now if I re-order the result, and hide any rows that are empty or too detailed, it should be what I need!
Int32 DetailLevel = AParameters["param_nesting_depth"].ToInt32();
String DepthFilter = " AccountLevel<=" + DetailLevel.ToString();
if (CostCentreBreakdown)
{
TLogging.Log(Catalog.GetString("Get Cost Centre Breakdown.."), TLoggingType.ToStatusBar);
// I'm creating additional "breakdown" records for the per-CostCentre breakdown, and potentially removing
// some records that were summed into those "breakdown" records.
FilteredResults.DefaultView.Sort = "AccountType DESC, AccountPath ASC, CostCentreCode";
FilteredResults.DefaultView.RowFilter = "Breakdown=false";
// At this point I need to add together any transactions in more detailed levels, summarising them by Cost Centre,
// and listing them under the account to which they relate:
DataView SummaryView = new DataView(FilteredResults);
SummaryView.Sort = "AccountTypeOrder, AccountPath ASC, CostCentreCode";
SummaryView.RowFilter = "Breakdown=true";
DataRow AccumulatingRow = FilteredResults.NewRow(); // This temporary row is not part of the result set - it's just a line of temporary vars.
for (Int32 RowIdx = 0; RowIdx < FilteredResults.DefaultView.Count - 1; RowIdx++)
{
if (DbAdapter.IsCancelled)
{
return FilteredResults;
}
DataRow DetailRow = FilteredResults.DefaultView[RowIdx].Row;
AddToCostCentreBreakdownSummary(SummaryView, DetailLevel, DetailRow);
//
// For posting accounts in "details" view, the cost centre breakdown rows will be presented after one or more rows with the same account.
// The last account row will become a header, below, and any other rows with the same account will be removed.
// So I need the values in those rows to accumulate into the last row.
AccumulateTotalsPerCostCentre(DetailRow, AccumulatingRow);
}
FilteredResults.DefaultView.Sort = "AccountTypeOrder, AccountPath ASC, Breakdown, CostCentreCode";
}
else
{
FilteredResults.DefaultView.Sort = "CostCentreCode, AccountTypeOrder, AccountPath ASC";
}
if (WholeYearPeriodsBreakdown)
{
FilteredResults.DefaultView.RowFilter =
DepthFilter; // Nothing too detailed
}
else
{
FilteredResults.DefaultView.RowFilter =
"(Actual <> 0 OR ActualYTD <> 0 OR Budget <> 0 OR BudgetYTD <> 0)" + // Only non-zero rows
" AND " + DepthFilter; // Nothing too detailed
}
FilteredResults = FilteredResults.DefaultView.ToTable("IncomeExpense");
if (WholeYearPeriodsBreakdown)
{
TLogging.Log(Catalog.GetString("Get whole year breakdown.."), TLoggingType.ToStatusBar);
//
// If there are any unsummarised rows left after applying the Depth Filter,
// I need to summarise them into new "per period" rows (with 12 "Actual" fields), and throw the original rows away.
FilteredResults.DefaultView.RowFilter = "AccountIsSummary=false AND Breakdown=false";
DataView SummaryView = new DataView(FilteredResults);
SummaryView.Sort = "CostCentreCode, AccountTypeOrder, AccountPath ASC";
SummaryView.RowFilter = "Breakdown=true";
foreach (DataRowView rv in FilteredResults.DefaultView)
{
if (DbAdapter.IsCancelled)
{
return FilteredResults;
}
DataRow Row = rv.Row;
AddToPeriodBreakdownSummary(SummaryView, Row);
}
FilteredResults.DefaultView.RowFilter = "(Breakdown=true OR AccountIsSummary=true) " + // Only the new rows with the calculated summaries
"AND (P1<>0 OR P2<>0 OR P3<>0 OR P4<>0 OR P5<>0 OR P6<>0 " +
"OR P7<>0 OR P8<>0 OR P9<>0 OR P10<>0 OR P11<>0 OR P12<>0) "; // No blank rows
FilteredResults.DefaultView.Sort = "CostCentreCode, AccountType DESC, AccountPath ASC";
FilteredResults = FilteredResults.DefaultView.ToTable("IncomeExpense");
}
//
// Finally, to make the hierarchical report possible,
// I want to include a note to show whether a row has child rows,
// and if it does, I'll copy this row to a new row, below the children, marking the new row as "footer".
TLogging.Log(Catalog.GetString("Format data for reporting.."), TLoggingType.ToStatusBar);
for (Int32 RowIdx = 0; RowIdx < FilteredResults.Rows.Count - 1; RowIdx++)
{
if (DbAdapter.IsCancelled)
{
return FilteredResults;
}
Int32 ParentAccountLevel = Convert.ToInt32(FilteredResults.Rows[RowIdx]["AccountLevel"]);
Boolean HasChildren = (Convert.ToInt32(FilteredResults.Rows[RowIdx + 1]["AccountLevel"]) > ParentAccountLevel)
|| (Convert.ToBoolean(FilteredResults.Rows[RowIdx]["Breakdown"]) == false
&& Convert.ToBoolean(FilteredResults.Rows[RowIdx + 1]["Breakdown"]) == true);
FilteredResults.Rows[RowIdx]["HasChildren"] = HasChildren;
if (HasChildren)
{
if (CostCentreBreakdown)
{
//
// Header and footer rows do not have Cost Centres -
// The Cost Centre fields were used for sorting, but they're misleading so I'll remove them here:
FilteredResults.Rows[RowIdx]["CostCentreCode"] = "";
FilteredResults.Rows[RowIdx]["CostCentreName"] = "";
}
Int32 NextSiblingPos = -1;
for (Int32 ChildIdx = RowIdx + 2; ChildIdx < FilteredResults.Rows.Count; ChildIdx++)
{
if ((Convert.ToInt32(FilteredResults.Rows[ChildIdx]["AccountLevel"]) <= ParentAccountLevel)
&& (Convert.ToBoolean(FilteredResults.Rows[ChildIdx]["Breakdown"]) == false)) // This row is not a child of mine
{ // so I insert my footer before here.
NextSiblingPos = ChildIdx;
break;
}
}
DataRow FooterRow = FilteredResults.NewRow();
DataUtilities.CopyAllColumnValues(FilteredResults.Rows[RowIdx], FooterRow);
FooterRow["ParentFooter"] = true;
FooterRow["HasChildren"] = false;
if (NextSiblingPos > 0)
{
FilteredResults.Rows.InsertAt(FooterRow, NextSiblingPos);
}
else
{
FilteredResults.Rows.Add(FooterRow);
}
}
}
// For "Cost Centre Breakdown", the only transactions I want to see are the "breakdown" rows I've added.
// Everything else is removed unless it's a header or footer:
if (CostCentreBreakdown)
{
FilteredResults.DefaultView.RowFilter = "Breakdown=true OR HasChildren=true OR ParentFooter=true";
FilteredResults = FilteredResults.DefaultView.ToTable("IncomeExpense");
}
TLogging.Log("", TLoggingType.ToStatusBar);
}
catch (Exception ex) // if the report was cancelled, DB calls with the same transaction will raise exceptions.
{
TLogging.Log(ex.Message);
}
finally
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
return FilteredResults;
} // IncomeExpenseTable
