public static DataTable RecipientGiftStatementDonorAddressesTable(Int64 ADonorKey, TReportingDbAdapter DbAdapter)
{
TDBTransaction Transaction = null;
// create new datatable
DataTable Results = new DataTable();
Results.Columns.Add("DonorKey", typeof(Int64));
DBAccess.GDBAccessObj.BeginAutoReadTransaction(
ref Transaction,
delegate
{
// get best address for the partner
PPartnerLocationTable PartnerLocationDT = PPartnerLocationAccess.LoadViaPPartner(ADonorKey, Transaction);
TLocationPK BestAddress = Calculations.DetermineBestAddress(PartnerLocationDT);
string QueryLocation = "SELECT" +
" PUB_p_location.p_locality_c AS Locality," +
" PUB_p_location.p_street_name_c," +
" PUB_p_location.p_address_3_c," +
" PUB_p_location.p_postal_code_c," +
" PUB_p_location.p_city_c," +
" PUB_p_location.p_county_c," +
" PUB_p_location.p_country_code_c," +
" PUB_p_country.p_address_order_i" +
" FROM" +
" PUB_p_location" +
" LEFT JOIN PUB_p_country" +
" ON PUB_p_country.p_country_code_c = PUB_p_location.p_country_code_c" +
" WHERE" +
" PUB_p_location.p_site_key_n = " + BestAddress.SiteKey +
" AND PUB_p_location.p_location_key_i = " + BestAddress.LocationKey;
Results.Merge(DbAdapter.RunQuery(QueryLocation, "DonorAddresses", Transaction));
if (Results.Rows.Count == 0)
{
DataRow NewRow = Results.NewRow();
NewRow["Locality"] = "UNKNOWN";
Results.Rows.Add(NewRow);
}
Results.Rows[0]["DonorKey"] = ADonorKey;
});
return Results;
}
public static GLReportingTDS GetReportingDataSet(String ADataSetFilterCsv)
{
TDBTransaction Transaction = null;
GLReportingTDS MainDs = new GLReportingTDS();
try
{
Transaction = DBAccess.GDBAccessObj.BeginTransaction();
FDbAdapter = new TReportingDbAdapter();
while (!FDbAdapter.IsCancelled && ADataSetFilterCsv != "")
{
String Tbl = StringHelper.GetNextCSV(ref ADataSetFilterCsv, ",", "");
String[] part = Tbl.Split('/');
DataTable NewTbl = FDbAdapter.RunQuery(part[1], part[0], Transaction);
MainDs.Merge(NewTbl);
}
if (FDbAdapter.IsCancelled)
{
return null;
}
}
catch (Exception)
{
MainDs = null;
throw;
}
finally
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
return MainDs;
}
public static DataSet GetOneYearMonthGivingDataSet(Dictionary <String, TVariant>AParameters)
{
FDbAdapter = new TReportingDbAdapter();
TLogging.SetStatusBarProcedure(WriteToStatusBar);
DataSet ReturnDataSet = new DataSet();
// get recipients
DataTable Recipients = TFinanceReportingWebConnector.RecipientGiftStatementRecipientTable(AParameters, FDbAdapter);
if (FDbAdapter.IsCancelled || (Recipients == null))
{
return null;
}
DataTable Donors = new DataTable("Donors");
foreach (DataRow Row in Recipients.Rows)
{
// get donor information for each recipient
Donors.Merge(TFinanceReportingWebConnector.OneYearMonthGivingDonorTable(AParameters, (Int64)Row["RecipientKey"], FDbAdapter));
if (FDbAdapter.IsCancelled)
{
return null;
}
}
ReturnDataSet.Tables.Add(Recipients);
ReturnDataSet.Tables.Add(Donors);
return (FDbAdapter.IsCancelled) ? null : ReturnDataSet;
}
public static DataTable TrialBalanceTable(Dictionary <String, TVariant>AParameters, TReportingDbAdapter DbAdapter)
{
/* Required columns:
* CostCentreCode
* CostCentreName
* AccountCode
* AccountName
* Debit
* Credit
*/
/*
* Trial balance is simply a list of all the account / cost centre balances, at the end of the period specified.
* (If the period is open, it still works.)
*
* Trial balance works on Posting accounts and cost centres, so there's no chasing up the hierarchy tree.
*/
Int32 LedgerNumber = AParameters["param_ledger_number_i"].ToInt32();
Int32 AccountingYear = AParameters["param_year_i"].ToInt32();
Int32 ReportPeriodEnd = AParameters["param_end_period_i"].ToInt32();
bool YTDBalance = AParameters["param_chkYTD"].ToBool();
//
// 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);
TDBTransaction ReadTrans = null;
DataTable resultTable = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref ReadTrans,
delegate
{
String OrderBy = " ORDER BY a_cost_centre.a_cost_centre_type_c DESC, glm.a_cost_centre_code_c, glm.a_account_code_c";
if (AParameters["param_sortby"].ToString() == "Account")
{
OrderBy = " ORDER BY glm.a_account_code_c, a_cost_centre.a_cost_centre_type_c DESC, glm.a_cost_centre_code_c";
}
String Query = "SELECT DISTINCT" +
" 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," +
" a_account.a_debit_credit_indicator_l AS IsDebit,";
if (YTDBalance)
{
Query += " glmp." + ActualFieldName + " AS Balance,";
}
else
{
Query += " (glmp." + ActualFieldName + " - glmpPrevious." + ActualFieldName + ") AS Balance,";
}
Query += " 0.0 as Debit, " +
" 0.0 as Credit, " +
" glm.a_account_code_c AS AccountCode," +
" a_account.a_account_code_short_desc_c AS AccountName" +
" FROM a_general_ledger_master AS glm, a_general_ledger_master_period AS glmp, a_account, a_cost_centre";
if (!YTDBalance)
{
Query += ", a_general_ledger_master_period AS glmpPrevious";
}
Query += " 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." + ActualFieldName + " <> 0";
if (!YTDBalance)
{
Query += " AND glm.a_glm_sequence_i = glmpPrevious.a_glm_sequence_i" +
" AND glmpPrevious.a_period_number_i = " + (ReportPeriodEnd - 1);
}
Query += " 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 +
OrderBy;
TLogging.Log(Catalog.GetString("Loading data.."), TLoggingType.ToStatusBar);
resultTable = DbAdapter.RunQuery(Query, "TrialBalance", ReadTrans);
foreach (DataRow Row in resultTable.Rows)
{
Decimal Amount = Convert.ToDecimal(Row["Balance"]);
Boolean IsDebit = Convert.ToBoolean(Row["IsDebit"]);
if (Amount < 0)
{
IsDebit = !IsDebit;
Amount = 0 - Amount;
}
String ToField = IsDebit ? "Debit" : "Credit";
Row[ToField] = Amount;
}
TLogging.Log("", TLoggingType.ToStatusBar);
}); // Get NewOrExisting AutoReadTransaction
return resultTable;
} // Trial Balance Table
public static DataTable GetPeriodBalances(String ALedgerFilter,
String AAccountCodeFilter,
String ACostCentreFilter,
Int32 AFinancialYear,
String ASortBy,
DataTable ATransactionsTbl,
Int32 AStartPeriod,
Int32 AEndPeriod,
String ASelectedCurrency)
{
DataTable Results = new DataTable();
Results.Columns.Add(new DataColumn("a_cost_centre_code_c", typeof(string)));
Results.Columns.Add(new DataColumn("a_account_code_c", typeof(string)));
Results.Columns.Add(new DataColumn("OpeningBalance", typeof(Decimal)));
Results.Columns.Add(new DataColumn("ClosingBalance", typeof(Decimal)));
Results.Columns.Add(new DataColumn("Currency", typeof(string)));
Boolean FromStartOfYear = (AStartPeriod == 1);
TReportingDbAdapter DbAdapter = new TReportingDbAdapter();
if (!FromStartOfYear)
{
AStartPeriod -= 1; // I want the closing balance of the previous period.
}
String CurrencyCodeField = ASelectedCurrency.StartsWith("Int") ? "a_ledger.a_intl_currency_c" :
ASelectedCurrency == "Base" ? "a_ledger.a_base_currency_c" :
"CASE WHEN a_account.a_foreign_currency_flag_l=TRUE THEN a_account.a_foreign_currency_code_c ELSE a_ledger.a_base_currency_c END";
String GroupField = "";
if (ASortBy == "Account")
{
GroupField = " ORDER BY glm.a_account_code_c, glm.a_cost_centre_code_c";
}
if (ASortBy == "Cost Centre")
{
GroupField = " ORDER BY glm.a_cost_centre_code_c, glm.a_account_code_c";
}
//
// This can only be used posting Accounts and Cost Centres;
// it does no summarisation, so summary Accounts and Cost Centres won't work here:
String Query = "SELECT glm.a_cost_centre_code_c, glm.a_account_code_c, glmp.a_period_number_i, " +
"a_account.a_debit_credit_indicator_l AS Debit, " +
"glm.a_start_balance_base_n AS StartBalanceBase, " +
"glm.a_start_balance_foreign_n AS StartBalanceForeign, " +
"glm.a_start_balance_intl_n AS StartBalanceIntl, " +
"glmp.a_actual_base_n AS BalanceBase, " +
"glmp.a_actual_foreign_n AS BalanceForeign, " +
"glmp.a_actual_intl_n AS BalanceIntl," +
CurrencyCodeField + " AS Currency, " +
" a_ledger.a_base_currency_c AS BaseCurrency " +
" FROM a_general_ledger_master AS glm, a_general_ledger_master_period AS glmp, a_account, a_cost_centre, a_ledger" +
" WHERE glm." + ALedgerFilter +
" AND a_account." + ALedgerFilter +
" AND a_cost_centre." + ALedgerFilter +
" AND a_ledger." + ALedgerFilter +
" AND a_account.a_posting_status_l = TRUE" +
" AND a_cost_centre.a_posting_cost_centre_flag_l = TRUE" +
" AND glm.a_year_i = " + AFinancialYear +
" AND glm.a_account_code_c = a_account.a_account_code_c " +
" AND glm.a_cost_centre_code_c = a_cost_centre.a_cost_centre_code_c " +
AAccountCodeFilter +
ACostCentreFilter +
" AND glm.a_glm_sequence_i = glmp.a_glm_sequence_i" +
" AND glmp.a_period_number_i BETWEEN " + AStartPeriod + " AND " + AEndPeriod +
GroupField;
DataTable GlmTbl = null;
TDBTransaction ReadTrans = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref ReadTrans,
delegate
{
GlmTbl = DbAdapter.RunQuery(Query, "balances", ReadTrans);
});
String CostCentre = "";
String AccountCode = "";
Int32 MaxPeriod = -1;
Int32 MinPeriod = 99;
DataRow NewRow = null;
Decimal MakeItDebit = 1;
// For each CostCentre / Account combination I want just a single row, with the opening and closing balances,
// so I need to pre-process the stuff I've got in this table, and generate rows in the Results table.
foreach (DataRow row in GlmTbl.Rows)
{
if (DbAdapter.IsCancelled)
{
return Results;
}
MakeItDebit = (Convert.ToBoolean(row["Debit"])) ? -1 : 1;
if ((row["a_cost_centre_code_c"].ToString() != CostCentre) || (row["a_account_code_c"].ToString() != AccountCode)) // a new CC/AC combination
{
NewRow = Results.NewRow();
NewRow["Currency"] = row["Currency"].ToString();
CostCentre = row["a_cost_centre_code_c"].ToString();
NewRow["a_cost_centre_code_c"] = CostCentre;
AccountCode = row["a_account_code_c"].ToString();
MakeItDebit = (Convert.ToBoolean(row["Debit"])) ? -1 : 1;
NewRow["a_account_code_c"] = AccountCode;
Results.Rows.Add(NewRow);
MaxPeriod = -1;
MinPeriod = 99;
}
Int32 ThisPeriod = Convert.ToInt32(row["a_period_number_i"]);
String BalanceField = "BalanceBase";
if (ASelectedCurrency.StartsWith("Int"))
{
BalanceField = "BalanceIntl";
}
else
{
if (ASelectedCurrency.StartsWith("Trans") && (row["Currency"].ToString() != row["BaseCurrency"].ToString()))
{
BalanceField = "BalanceForeign";
}
}
if (ThisPeriod < MinPeriod)
{
MinPeriod = ThisPeriod;
Decimal OpeningBalance = 0;
if (FromStartOfYear)
{
String StartBalanceField = "StartBalanceBase";
if (ASelectedCurrency.StartsWith("Int"))
{
StartBalanceField = "StartBalanceIntl";
}
else
{
if (ASelectedCurrency.StartsWith("Trans") && (row["Currency"].ToString() != row["BaseCurrency"].ToString()))
{
StartBalanceField = "StartBalanceForeign";
}
}
if (row[StartBalanceField].GetType() != typeof(DBNull))
{
OpeningBalance = Convert.ToDecimal(row[StartBalanceField]);
}
}
else
{
if (row[BalanceField].GetType() != typeof(DBNull))
{
OpeningBalance = Convert.ToDecimal(row[BalanceField]);
}
}
NewRow["OpeningBalance"] = MakeItDebit * OpeningBalance;
}
if (ThisPeriod > MaxPeriod)
{
MaxPeriod = ThisPeriod;
Decimal ClosingBalance = 0;
if (row[BalanceField].GetType() != typeof(DBNull))
{
ClosingBalance = Convert.ToDecimal(row[BalanceField]);
}
NewRow["ClosingBalance"] = MakeItDebit * ClosingBalance;
}
} // foreach
for (Int32 Idx = Results.Rows.Count - 1; Idx >= 0; Idx--)
{
DataRow ResultsRow = Results.Rows[Idx];
//
// Since a revision in October 2014, this balances table can be the master table for the Account Detail report
// (That is, "for each opening and closing balance, list any applicable transactions",
// rather than, "for each Account/Cost Centre combination where we see transactions, show opening and closing balance".)
// The effect of this is I need to remove rows where opening and closing balances both are zero,
// AND there were no transactions in the selected period.
if ((Convert.ToDecimal(ResultsRow["OpeningBalance"]) == 0) && (Convert.ToDecimal(ResultsRow["ClosingBalance"]) == 0))
{
ATransactionsTbl.DefaultView.RowFilter = String.Format("AccountCode='{0}' AND CostCentreCode ='{1}'",
ResultsRow["a_account_code_c"],
ResultsRow["a_cost_centre_code_c"]);
if (ATransactionsTbl.DefaultView.Count == 0)
{
ResultsRow.Delete();
}
}
}
Results.AcceptChanges();
return Results;
}
public static DataTable ExecutiveSummaryTable(Dictionary <String, TVariant>AParameters, TReportingDbAdapter DbAdapter)
{
TDBTransaction Transaction = null;
int LedgerNumber = AParameters["param_ledger_number_i"].ToInt32();
int EndPeriod = AParameters["param_end_period_i"].ToInt32();
int Year = AParameters["param_year_i"].ToInt32();
DateTime EndPeriodEndDate = AParameters["param_end_date"].ToDate();
DateTime EndPeriodStartDate = new DateTime(EndPeriodEndDate.Year, EndPeriodEndDate.Month, 1);
DateTime NextPeriodStartDate = new DateTime();
DateTime NextPeriodEndDate = new DateTime();
decimal[] ActualsAndBudget;
Int64 LedgerPartnerKey;
string StandardSummaryCostCentre;
// create new datatable
DataTable Results = new DataTable();
string[] Columns =
{
"ThisMonth", "ActualYTD", "Budget", "PriorYTD"
};
foreach (string Column in Columns)
{
Results.Columns.Add(new DataColumn("Income" + Column, typeof(Decimal)));
Results.Columns.Add(new DataColumn("Expenses" + Column, typeof(Decimal)));
Results.Columns.Add(new DataColumn("PersonnelCosts" + Column, typeof(Decimal)));
Results.Columns.Add(new DataColumn("SupportIncome" + Column, typeof(Decimal)));
Results.Columns.Add(new DataColumn("CashAndBank" + Column, typeof(Decimal)));
Results.Columns.Add(new DataColumn("FromToICH" + Column, typeof(Decimal)));
Results.Columns.Add(new DataColumn("PaymentsDue" + Column, typeof(Decimal)));
Results.Columns.Add(new DataColumn("GiftsForOtherFields" + Column, typeof(Decimal)));
Results.Columns.Add(new DataColumn("Personnel" + Column, typeof(Int32)));
Results.Columns.Add(new DataColumn("PersonnelOtherFields" + Column, typeof(Int32)));
}
DataRow ResultRow = Results.NewRow();
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
ALedgerRow LedgerRow = (ALedgerRow)ALedgerAccess.LoadByPrimaryKey(LedgerNumber, Transaction).Rows[0];
LedgerPartnerKey = LedgerRow.PartnerKey;
StandardSummaryCostCentre = LedgerRow.LedgerNumber.ToString("00") + "00S";
/* Income and Expenses */
string[, ] IncomeExpense = { { "Income", MFinanceConstants.INCOME_HEADING }, // "INC"
{ "Expenses", MFinanceConstants.EXPENSE_HEADING } }; // "EXP"
for (int i = 0; i < 2; i++)
{
ActualsAndBudget = GetActualsAndBudget(DbAdapter, LedgerRow, IncomeExpense[i, 1], StandardSummaryCostCentre, EndPeriod, Year);
ResultRow[IncomeExpense[i, 0] + "ThisMonth"] = ActualsAndBudget[0];
ResultRow[IncomeExpense[i, 0] + "PriorYTD"] = ActualsAndBudget[1];
ResultRow[IncomeExpense[i, 0] + "ActualYTD"] = ActualsAndBudget[2];
ResultRow[IncomeExpense[i, 0] + "Budget"] = ActualsAndBudget[3];
}
/* Personnel Costs */
// Calculate how many team members
int PersonnelCount = 0;
PmStaffDataTable StaffDataTable = PmStaffDataAccess.LoadViaPUnitReceivingField(LedgerPartnerKey, Transaction);
// this month - includes people who are only there for part of the month
foreach (PmStaffDataRow Row in StaffDataTable.Rows)
{
if (((Row.EndOfCommitment == null) || (Row.EndOfCommitment >= EndPeriodStartDate))
&& (Row.StartOfCommitment <= EndPeriodEndDate))
{
PersonnelCount++;
}
}
ResultRow["PersonnelThisMonth"] = PersonnelCount;
// this year
PersonnelCount = 0;
foreach (PmStaffDataRow Row in StaffDataTable.Rows)
{
if (((Row.EndOfCommitment == null) || (Row.EndOfCommitment >= new DateTime(EndPeriodStartDate.Year, 1, 1)))
&& (Row.StartOfCommitment <= EndPeriodEndDate))
{
PersonnelCount++;
}
}
ResultRow["PersonnelActualYTD"] = PersonnelCount;
// last year
PersonnelCount = 0;
foreach (PmStaffDataRow Row in StaffDataTable.Rows)
{
if (((Row.EndOfCommitment == null) || (Row.EndOfCommitment >= new DateTime(EndPeriodStartDate.Year - 1, 1, 1)))
&& (Row.StartOfCommitment <= EndPeriodEndDate.AddYears(-1)))
{
PersonnelCount++;
}
}
ResultRow["PersonnelPriorYTD"] = PersonnelCount;
/* calculate Personnel Costs per Team Member */
ActualsAndBudget =
GetActualsAndBudget(DbAdapter, LedgerRow, MFinanceConstants.PERSONNEL_EXPENSES, StandardSummaryCostCentre, EndPeriod, Year); //4300S
decimal TotalPersonnelCostsThisMonth = ActualsAndBudget[0];
decimal TotalPersonnelCostsPriorYTD = ActualsAndBudget[1];
decimal TotalPersonnelCostsActualYTD = ActualsAndBudget[2];
decimal TotalPersonnelCostsBudget = ActualsAndBudget[3];
if (Convert.ToInt32(ResultRow["PersonnelThisMonth"]) != 0)
{
ResultRow["PersonnelCostsThisMonth"] = TotalPersonnelCostsThisMonth / Convert.ToInt32(ResultRow["PersonnelThisMonth"]);
}
if (Convert.ToInt32(ResultRow["PersonnelActualYTD"]) != 0)
{
ResultRow["PersonnelCostsActualYTD"] = TotalPersonnelCostsActualYTD / Convert.ToInt32(ResultRow["PersonnelActualYTD"]);
ResultRow["PersonnelCostsBudget"] = TotalPersonnelCostsBudget / Convert.ToInt32(ResultRow["PersonnelActualYTD"]);
}
if (Convert.ToInt32(ResultRow["PersonnelPriorYTD"]) != 0)
{
ResultRow["PersonnelCostsPriorYTD"] = TotalPersonnelCostsPriorYTD / Convert.ToInt32(ResultRow["PersonnelPriorYTD"]);
}
/* SUPPORT INCOME % */
ActualsAndBudget = GetActualsAndBudget(DbAdapter,
LedgerRow, MFinanceConstants.SUPPORT_GIFTS_LOCAL, StandardSummaryCostCentre, EndPeriod, Year); // 0100S
decimal TotalSupportIncomeThisMonth = ActualsAndBudget[0];
decimal TotalSupportIncomePriorYTD = ActualsAndBudget[1];
decimal TotalSupportIncomeActualYTD = ActualsAndBudget[2];
decimal TotalSupportIncomeBudget = ActualsAndBudget[3];
ActualsAndBudget = GetActualsAndBudget(DbAdapter,
LedgerRow, MFinanceConstants.SUPPORT_GIFTS_FOREIGN, StandardSummaryCostCentre, EndPeriod, Year); // 1100S
TotalSupportIncomeThisMonth += ActualsAndBudget[0];
TotalSupportIncomePriorYTD += ActualsAndBudget[1];
TotalSupportIncomeActualYTD += ActualsAndBudget[2];
TotalSupportIncomeBudget += ActualsAndBudget[3];
ResultRow["SupportIncomeThisMonth"] = TotalPersonnelCostsThisMonth != 0 ?
(TotalSupportIncomeThisMonth / TotalPersonnelCostsThisMonth) : 0;
ResultRow["SupportIncomePriorYTD"] = TotalPersonnelCostsPriorYTD != 0 ?
(TotalSupportIncomePriorYTD / TotalPersonnelCostsPriorYTD) : 0;
ResultRow["SupportIncomeActualYTD"] = TotalPersonnelCostsActualYTD != 0 ?
(TotalSupportIncomeActualYTD / TotalPersonnelCostsActualYTD) : 0;
ResultRow["SupportIncomeBudget"] = TotalPersonnelCostsBudget != 0 ?
(TotalSupportIncomeBudget / TotalPersonnelCostsBudget) : 0;
/* Bank */
string[, ] Bank =
{
{ "CashAndBank", MFinanceConstants.BANK_HEADING }, { "FromToICH", MFinanceConstants.ICH_ACCT_ICH + "S" }
}; // "CASH", 8500S
for (int i = 0; i < 2; i++)
{
int Multiplier = 1;
// find out if ICH balance is credit or debit
AAccountRow AccountRow = (AAccountRow)AAccountAccess.LoadByPrimaryKey(LedgerNumber, Bank[i, 1], Transaction).Rows[0];
if ((AccountRow != null) && !AccountRow.DebitCreditIndicator)
{
Multiplier = -1;
}
ActualsAndBudget = GetActualsAndBudget(DbAdapter, LedgerRow, Bank[i, 1], StandardSummaryCostCentre, EndPeriod, Year);
// balance sheet item so will be the same as the ytd figure
ResultRow[Bank[i, 0] + "ThisMonth"] = ActualsAndBudget[2] * Multiplier;
ResultRow[Bank[i, 0] + "PriorYTD"] = ActualsAndBudget[1] * Multiplier;
ResultRow[Bank[i, 0] + "ActualYTD"] = ActualsAndBudget[2] * Multiplier;
ResultRow[Bank[i, 0] + "Budget"] = ActualsAndBudget[3] * Multiplier;
}
/* ACCOUNTS PAYABLE */
// find the dates of the next period
AAccountingPeriodTable AccountingPeriodTable = AAccountingPeriodAccess.LoadViaALedger(LedgerNumber, Transaction);
AAccountingPeriodRow AccountingPeriodRow =
(AAccountingPeriodRow)AccountingPeriodTable.Rows.Find(new object[] { LedgerNumber, EndPeriod + 1 });
if (AccountingPeriodRow == null)
{
AccountingPeriodRow = (AAccountingPeriodRow)AccountingPeriodTable.Rows.Find(new object[] { LedgerNumber, EndPeriod });
if (AccountingPeriodRow == null)
{
throw new Exception("TFinanceReportingWebConnector.ExecutiveSummaryTable: Accounting Period not available");
}
NextPeriodStartDate = AccountingPeriodRow.PeriodEndDate.AddDays(1);
// approximate the end date
NextPeriodEndDate = AccountingPeriodRow.PeriodEndDate.AddDays(30);
}
else
{
NextPeriodStartDate = AccountingPeriodRow.PeriodStartDate;
NextPeriodEndDate = AccountingPeriodRow.PeriodEndDate;
}
if (NextPeriodStartDate == NextPeriodEndDate)
{
// If 13th period then add 32 days to the end date to go into end of January
NextPeriodEndDate = NextPeriodEndDate.AddDays(31);
}
// this month
ResultRow["PaymentsDueThisMonth"] = 0;
AApDocumentTable ApDocumentTable = AApDocumentAccess.LoadViaALedger(LedgerNumber, Transaction);
foreach (AApDocumentRow ApDocumentRow in ApDocumentTable.Rows)
{
if ((ApDocumentRow.DateIssued.AddDays(ApDocumentRow.CreditTerms) >= NextPeriodStartDate)
&& (ApDocumentRow.DateIssued.AddDays(ApDocumentRow.CreditTerms) <= NextPeriodEndDate)
&& (!ApDocumentRow.DocumentStatus.Equals(MFinanceConstants.BATCH_UNPOSTED, StringComparison.InvariantCultureIgnoreCase))
&& (!ApDocumentRow.DocumentStatus.Equals(MFinanceConstants.BATCH_CANCELLED, StringComparison.InvariantCultureIgnoreCase)))
{
ResultRow["PaymentsDueThisMonth"] = Convert.ToDecimal(ResultRow["PaymentsDueThisMonth"]) + ApDocumentRow.TotalAmount;
}
}
/* FOREIGN GIFTS */
ActualsAndBudget = GetActualsAndBudget(DbAdapter,
LedgerRow, MFinanceConstants.GIFT_HEADING, MFinanceConstants.INTER_LEDGER_HEADING, EndPeriod, Year); // "GIFT", "ILT"
ResultRow["GiftsForOtherFieldsThisMonth"] = ActualsAndBudget[0];
ResultRow["GiftsForOtherFieldsPriorYTD"] = ActualsAndBudget[1];
ResultRow["GiftsForOtherFieldsActualYTD"] = ActualsAndBudget[2];
ResultRow["GiftsForOtherFieldsBudget"] = ActualsAndBudget[3];
/* NUMBER OF PERSONNEL ON OTHER FIELDS */
PersonnelCount = 0;
StaffDataTable = PmStaffDataAccess.LoadViaPUnitHomeOffice(LedgerPartnerKey, Transaction);
// this month - includes people who are only there for part of the month
foreach (PmStaffDataRow Row in StaffDataTable.Rows)
{
if (((Row.EndOfCommitment == null) || (Row.EndOfCommitment >= EndPeriodStartDate))
&& (Row.StartOfCommitment <= EndPeriodEndDate)
&& (Row.ReceivingField != LedgerPartnerKey))
{
PersonnelCount++;
}
}
ResultRow["PersonnelOtherFieldsThisMonth"] = PersonnelCount;
// this year
PersonnelCount = 0;
foreach (PmStaffDataRow Row in StaffDataTable.Rows)
{
if (((Row.EndOfCommitment == null) || (Row.EndOfCommitment >= new DateTime(EndPeriodStartDate.Year, 1, 1)))
&& (Row.StartOfCommitment <= EndPeriodEndDate)
&& (Row.ReceivingField != LedgerPartnerKey))
{
PersonnelCount++;
}
}
ResultRow["PersonnelOtherFieldsActualYTD"] = PersonnelCount;
// last year
PersonnelCount = 0;
foreach (PmStaffDataRow Row in StaffDataTable.Rows)
{
if (((Row.EndOfCommitment == null) || (Row.EndOfCommitment >= new DateTime(EndPeriodStartDate.Year - 1, 1, 1)))
&& (Row.StartOfCommitment <= EndPeriodEndDate.AddYears(-1))
&& (Row.ReceivingField != LedgerPartnerKey))
{
PersonnelCount++;
}
}
ResultRow["PersonnelOtherFieldsPriorYTD"] = PersonnelCount;
}); // Get NewOrExisting AutoReadTransaction
Results.Rows.Add(ResultRow);
return Results;
}
public static DataTable TotalGiftsThroughField(Dictionary <String, TVariant>AParameters, TReportingDbAdapter DbAdapter)
{
Int32 LedgerNum = AParameters["param_ledger_number_i"].ToInt32();
DateTime startDate = AParameters["param_StartDate"].ToDate();
string strStartDate = startDate.ToString("#yyyy-MM-dd#");
DateTime endDate = AParameters["param_EndDate"].ToDate();
string strEndDate = endDate.ToString("#yyyy-MM-dd#");
bool TaxDeductiblePercentageEnabled = Convert.ToBoolean(
TSystemDefaults.GetSystemDefault(SharedConstants.SYSDEFAULT_TAXDEDUCTIBLEPERCENTAGE, "FALSE"));
string SqlQuery = "SELECT batch.a_gl_effective_date_d as Date, motive.a_report_column_c AS ReportColumn, ";
if (AParameters["param_currency"].ToString() == "Base")
{
SqlQuery += "detail.a_gift_amount_n AS Amount";
if (TaxDeductiblePercentageEnabled)
{
SqlQuery += ", detail.a_tax_deductible_amount_base_n AS TaxDeductAmount";
}
}
else
{
SqlQuery += "detail.a_gift_amount_intl_n AS Amount";
if (TaxDeductiblePercentageEnabled)
{
SqlQuery += ", detail.a_tax_deductible_amount_intl_n AS TaxDeductAmount";
}
}
SqlQuery += (" FROM PUB_a_gift as gift, PUB_a_gift_detail as detail, PUB_a_gift_batch as batch, PUB_a_motivation_detail AS motive"
+ " WHERE detail.a_ledger_number_i = " + LedgerNum +
" AND batch.a_batch_status_c = 'Posted'" +
" AND batch.a_batch_number_i = gift.a_batch_number_i" +
" AND batch.a_ledger_number_i = " + LedgerNum +
" AND batch.a_gl_effective_date_d >= " + strStartDate +
" AND batch.a_gl_effective_date_d <= " + strEndDate
+ " AND gift.a_ledger_number_i = " + LedgerNum +
" AND detail.a_batch_number_i = gift.a_batch_number_i" +
" AND detail.a_gift_transaction_number_i = gift.a_gift_transaction_number_i"
+ " AND motive.a_ledger_number_i = " + LedgerNum +
" AND motive.a_motivation_group_code_c = detail.a_motivation_group_code_c" +
" AND motive.a_motivation_detail_code_c = detail.a_motivation_detail_code_c" +
" AND motive.a_receipt_l=true"
+ " ORDER BY batch.a_gl_effective_date_d"
);
DataTable tempTbl = null;
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
tempTbl = DbAdapter.RunQuery(SqlQuery, "AllGifts", Transaction);
});
DataTable resultTable = new DataTable();
resultTable.Columns.Add("Year", typeof(Int32));
resultTable.Columns.Add("Month", typeof(Int32));
resultTable.Columns.Add("MonthName", typeof(String));
resultTable.Columns.Add("MonthWorker", typeof(Decimal));
resultTable.Columns.Add("MonthWorkerCount", typeof(Int32));
resultTable.Columns.Add("MonthField", typeof(Decimal));
resultTable.Columns.Add("MonthFieldCount", typeof(Int32));
resultTable.Columns.Add("MonthTotal", typeof(Decimal));
resultTable.Columns.Add("MonthTotalCount", typeof(Int32));
resultTable.Columns.Add("MonthWorkerTaxDeduct", typeof(Decimal));
resultTable.Columns.Add("MonthFieldTaxDeduct", typeof(Decimal));
resultTable.Columns.Add("MonthTotalTaxDeduct", typeof(Decimal));
for (Int32 Year = endDate.Year; Year >= startDate.Year; Year--)
{
//TODO: Calendar vs Financial Date Handling - Check if this should use financial num periods and not assume 12
for (Int32 Month = 1; Month <= 12; Month++)
{
string monthStart = String.Format("#{0:0000}-{1:00}-01#", Year, Month);
string nextMonthStart = String.Format("#{0:0000}-{1:00}-01#", Year, Month + 1);
if (Month == 12)
{
nextMonthStart = String.Format("#{0:0000}-01-01#", Year + 1);
}
tempTbl.DefaultView.RowFilter = "Date >= " + monthStart + " AND Date < " + nextMonthStart;
DataRow resultRow = resultTable.NewRow();
Decimal WorkerTotal = 0;
Decimal FieldTotal = 0;
Int32 WorkerCount = 0;
Int32 FieldCount = 0;
Int32 TotalCount = tempTbl.DefaultView.Count;
Decimal WorkerTotalTaxDeduct = 0;
Decimal FieldTotalTaxDeduct = 0;
foreach (DataRowView rv in tempTbl.DefaultView)
{
DataRow Row = rv.Row;
if (Row["ReportColumn"].ToString() == "Worker")
{
WorkerCount++;
WorkerTotal += Convert.ToDecimal(Row["Amount"]);
if (TaxDeductiblePercentageEnabled)
{
WorkerTotalTaxDeduct += Convert.ToDecimal(Row["TaxDeductAmount"]);
}
}
else
{
FieldCount++;
FieldTotal += Convert.ToDecimal(Row["Amount"]);
if (TaxDeductiblePercentageEnabled)
{
FieldTotalTaxDeduct += Convert.ToDecimal(Row["TaxDeductAmount"]);
}
}
}
resultRow["Year"] = Year;
resultRow["Month"] = Month;
resultRow["MonthName"] = StringHelper.GetLongMonthName(Month);
resultRow["MonthWorker"] = WorkerTotal;
resultRow["MonthWorkerCount"] = WorkerCount;
resultRow["MonthField"] = FieldTotal;
resultRow["MonthFieldCount"] = FieldCount;
resultRow["MonthTotal"] = WorkerTotal + FieldTotal;
resultRow["MonthTotalCount"] = TotalCount;
resultRow["MonthWorkerTaxDeduct"] = WorkerTotalTaxDeduct;
resultRow["MonthFieldTaxDeduct"] = FieldTotalTaxDeduct;
resultRow["MonthTotalTaxDeduct"] = WorkerTotalTaxDeduct + FieldTotalTaxDeduct;
resultTable.Rows.Add(resultRow);
} // For Month
} // For Year
return resultTable;
} // Total Gifts Through Field
public static DataTable GiftBatchDetailTable(Dictionary <String, TVariant>AParameters, TReportingDbAdapter DbAdapter)
{
TDBTransaction Transaction = null;
int LedgerNumber = AParameters["param_ledger_number_i"].ToInt32();
int BatchNumber = AParameters["param_batch_number_i"].ToInt32();
// create new datatable
DataTable Results = new DataTable();
DBAccess.GDBAccessObj.BeginAutoReadTransaction(
ref Transaction,
delegate
{
DateTime CurrentDate = DateTime.Today;
string Query =
"SELECT DISTINCT a_gift_batch.a_batch_description_c, a_gift_batch.a_batch_status_c, a_gift_batch.a_gift_type_c, a_gift_batch.a_gl_effective_date_d, "
+
"a_gift_batch.a_bank_cost_centre_c, a_gift_batch.a_bank_account_code_c, a_gift_batch.a_currency_code_c, a_gift_batch.a_hash_total_n, a_gift_batch.a_batch_total_n, "
+
"a_gift_detail.a_gift_transaction_number_i, a_gift_detail.a_detail_number_i, a_gift_detail.a_confidential_gift_flag_l, "
+
"a_gift_detail.p_recipient_key_n, a_gift_detail.a_gift_amount_n, a_gift_detail.a_gift_amount_intl_n, a_gift_detail.a_gift_transaction_amount_n, "
+
"a_gift_detail.a_motivation_group_code_c, a_gift_detail.a_motivation_detail_code_c, a_gift_detail.a_recipient_ledger_number_n, "
+
"a_gift_detail.a_gift_comment_one_c, a_gift_detail.a_gift_comment_two_c, a_gift_detail.a_gift_comment_three_c, a_gift_detail.a_tax_deductible_pct_n, "
+
"a_gift.p_donor_key_n, a_gift.a_reference_c, a_gift.a_method_of_giving_code_c, a_gift.a_method_of_payment_code_c, "
+
"a_gift.a_receipt_letter_code_c, a_gift.a_date_entered_d, a_gift.a_first_time_gift_l, a_gift.a_receipt_number_i, "
+
"Donor.p_partner_class_c, Donor.p_partner_short_name_c, Donor.p_receipt_letter_frequency_c, Donor.p_receipt_each_gift_l, "
+
"Recipient.p_partner_class_c, Recipient.p_partner_short_name_c, " +
// true if donor has a valid Ex-Worker special type
"CASE WHEN EXISTS (SELECT p_partner_type.* FROM p_partner_type WHERE " +
"p_partner_type.p_partner_key_n = a_gift.p_donor_key_n" +
" AND (p_partner_type.p_valid_from_d IS null OR p_partner_type.p_valid_from_d <= '" + CurrentDate + "')" +
" AND (p_partner_type.p_valid_until_d IS null OR p_partner_type.p_valid_until_d >= '" + CurrentDate + "')" +
" AND p_partner_type.p_type_code_c LIKE '" +
TSystemDefaults.GetSystemDefault(SharedConstants.SYSDEFAULT_EXWORKERSPECIALTYPE, "EX-WORKER") + "%'" +
") THEN True ELSE False END AS EXWORKER, " +
// true if the gift is restricted for the user
"CASE WHEN EXISTS (SELECT s_user_group.* FROM s_user_group " +
"WHERE a_gift.a_restricted_l IS true" +
" AND NOT EXISTS (SELECT s_group_gift.s_read_access_l FROM s_group_gift, s_user_group " +
"WHERE s_group_gift.s_read_access_l" +
" AND s_group_gift.a_ledger_number_i = " + LedgerNumber +
" AND s_group_gift.a_batch_number_i = " + BatchNumber +
" AND s_group_gift.a_gift_transaction_number_i = a_gift_detail.a_gift_transaction_number_i" +
" AND s_user_group.s_user_id_c = '" + UserInfo.GUserInfo.UserID + "'" +
" AND s_user_group.s_group_id_c = s_group_gift.s_group_id_c" +
" AND s_user_group.s_unit_key_n = s_group_gift.s_group_unit_key_n)" +
") THEN False ELSE True END AS ReadAccess " +
"FROM a_gift_batch, a_gift_detail, a_gift, p_partner AS Donor, p_partner AS Recipient " +
"WHERE a_gift_batch.a_ledger_number_i = " + LedgerNumber + " AND a_gift_batch.a_batch_number_i = " + BatchNumber +
" AND a_gift.a_ledger_number_i = " + LedgerNumber + " AND a_gift.a_batch_number_i = " + BatchNumber +
" AND a_gift_detail.a_ledger_number_i = " + LedgerNumber + " AND a_gift_detail.a_batch_number_i = " +
BatchNumber +
" AND a_gift.a_gift_transaction_number_i = a_gift_detail.a_gift_transaction_number_i " +
" AND Donor.p_partner_key_n = a_gift.p_donor_key_n" +
" AND Recipient.p_partner_key_n = a_gift_detail.p_recipient_key_n";
Results = DBAccess.GDBAccessObj.SelectDT(Query, "Results", Transaction);
});
return Results;
}
public static DataTable RecipientTaxDeductPctTable(Dictionary <String, TVariant>AParameters, TReportingDbAdapter DbAdapter)
{
TDBTransaction Transaction = null;
// create new datatable
DataTable Results = new DataTable();
DBAccess.GDBAccessObj.BeginAutoReadTransaction(ref Transaction,
delegate
{
DateTime CurrentDate = DateTime.Today;
string RecipientSelection = AParameters["param_recipient_selection"].ToString();
string Query =
"SELECT DISTINCT p_partner_tax_deductible_pct.p_partner_key_n, p_partner_tax_deductible_pct.p_date_valid_from_d, " +
"p_partner_tax_deductible_pct.p_percentage_tax_deductible_n, p_partner.p_partner_short_name_c, " +
"p_partner_gift_destination.p_field_key_n, um_unit_structure.um_parent_unit_key_n " +
"FROM p_partner_tax_deductible_pct " +
"LEFT JOIN p_partner " +
"ON p_partner.p_partner_key_n = p_partner_tax_deductible_pct.p_partner_key_n " +
"LEFT JOIN p_partner_gift_destination " +
"ON CASE WHEN p_partner.p_partner_class_c = 'FAMILY' " +
"THEN p_partner.p_partner_key_n = p_partner_gift_destination.p_partner_key_n " +
"AND p_partner_gift_destination.p_date_effective_d <= '" + CurrentDate + "' " +
"AND (p_partner_gift_destination.p_date_expires_d IS NULL " +
"OR (p_partner_gift_destination.p_date_expires_d >= '" + CurrentDate + "' " +
"AND p_partner_gift_destination.p_date_effective_d <> p_partner_gift_destination.p_date_expires_d)) END " +
"LEFT JOIN um_unit_structure " +
"ON CASE WHEN p_partner.p_partner_class_c = 'UNIT' " +
"THEN NOT EXISTS (SELECT * FROM p_partner_type " +
"WHERE p_partner_type.p_partner_key_n = p_partner_tax_deductible_pct.p_partner_key_n " +
"AND p_partner_type.p_type_code_c = 'LEDGER') " +
"AND um_unit_structure.um_child_unit_key_n = p_partner_tax_deductible_pct.p_partner_key_n " +
"AND um_unit_structure.um_child_unit_key_n <> um_unit_structure.um_parent_unit_key_n END";
if (RecipientSelection == "one_partner")
{
Query += " WHERE p_partner_tax_deductible_pct.p_partner_key_n = " + AParameters["param_recipient_key"].ToInt64();
}
else if (RecipientSelection == "Extract")
{
// recipient must be part of extract
Query += " WHERE EXISTS(SELECT * FROM m_extract, m_extract_master";
if (!AParameters["param_chkPrintAllExtract"].ToBool())
{
Query += ", a_gift_detail, a_gift_batch";
}
Query += " WHERE p_partner_tax_deductible_pct.p_partner_key_n = m_extract.p_partner_key_n " +
"AND m_extract.m_extract_id_i = m_extract_master.m_extract_id_i " +
"AND m_extract_master.m_extract_name_c = '" + AParameters["param_extract_name"] + "'";
if (!AParameters["param_chkPrintAllExtract"].ToBool())
{
// recipient must have a posted gift
Query += " AND a_gift_detail.a_ledger_number_i = " + AParameters["param_ledger_number_i"] +
" AND a_gift_detail.p_recipient_key_n = m_extract.p_partner_key_n " +
"AND a_gift_batch.a_ledger_number_i = " + AParameters["param_ledger_number_i"] +
" AND a_gift_batch.a_batch_number_i = a_gift_detail.a_batch_number_i " +
"AND a_gift_batch.a_batch_status_c = 'Posted'";
}
Query += ")";
}
Results = DBAccess.GDBAccessObj.SelectDT(Query, "Results", Transaction);
});
return Results;
}
public static DataTable StewardshipTable(Dictionary <String, TVariant>AParameters, TReportingDbAdapter DbAdapter)
{
try
{
TLogging.Log(Catalog.GetString("Loading data.."), TLoggingType.ToStatusBar);
Int32 LedgerNumber = AParameters["param_ledger_number_i"].ToInt32();
Int32 IchNumber = AParameters["param_cmbICHNumber"].ToInt32();
Int32 period = AParameters["param_cmbReportPeriod"].ToInt32();
String StewardshipFilter = "PUB_a_ich_stewardship.a_ledger_number_i = " + LedgerNumber;
if (IchNumber == 0)
{
StewardshipFilter += " AND PUB_a_ich_stewardship.a_period_number_i = " + period;
}
else
{
StewardshipFilter += " AND PUB_a_ich_stewardship.a_ich_number_i = " + IchNumber;
}
String Query = "SELECT PUB_a_ich_stewardship.*, PUB_a_cost_centre.a_cost_centre_name_c" +
" FROM PUB_a_ich_stewardship, PUB_a_cost_centre WHERE " +
StewardshipFilter +
" AND PUB_a_cost_centre.a_ledger_number_i = PUB_a_ich_stewardship.a_ledger_number_i" +
" AND PUB_a_cost_centre.a_cost_centre_code_c = PUB_a_ich_stewardship.a_cost_centre_code_c ";
TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction();
TLogging.Log(Catalog.GetString(""), TLoggingType.ToStatusBar);
return DbAdapter.RunQuery(Query, "Stewardship", Transaction);
}
finally
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
} // StewardshipTable
public static DataTable AFOTable(Dictionary <String, TVariant>AParameters, TReportingDbAdapter DbAdapte)
{
TDBTransaction Transaction = null;
AAccountTable AccountTable = new AAccountTable();
int LedgerNumber = AParameters["param_ledger_number_i"].ToInt32();
string StandardSummaryCostCentre = LedgerNumber.ToString("00") + "00S";
string AccountHierarchyCode = AParameters["param_account_hierarchy_c"].ToString();
// create new datatable
DataTable Results = new DataTable();
Results.Columns.Add(new DataColumn("a_account_code_c", typeof(string)));
Results.Columns.Add(new DataColumn("a_account_code_short_desc_c", typeof(string)));
Results.Columns.Add(new DataColumn("DebitCreditIndicator", typeof(bool)));
Results.Columns.Add(new DataColumn("ActualDebitBase", typeof(Decimal)));
Results.Columns.Add(new DataColumn("ActualCreditBase", typeof(Decimal)));
Results.Columns.Add(new DataColumn("ActualDebitIntl", typeof(Decimal)));
Results.Columns.Add(new DataColumn("ActualCreditIntl", typeof(Decimal)));
DBAccess.GDBAccessObj.BeginAutoReadTransaction(ref Transaction,
delegate
{
ALedgerRow LedgerRow = (ALedgerRow)ALedgerAccess.LoadByPrimaryKey(LedgerNumber, Transaction).Rows[0];
AAccountHierarchyRow HierarchyRow = (AAccountHierarchyRow)AAccountHierarchyAccess.LoadByPrimaryKey(
LedgerNumber, AccountHierarchyCode, Transaction).Rows[0];
List <String>list = new List <string>();
ScanHierarchy(ref list, LedgerNumber, HierarchyRow.RootAccountCode, AccountHierarchyCode, Transaction);
// get AAccountRows for each account number in list
foreach (string AccountCode in list)
{
AAccountRow AddRow = (AAccountRow)AAccountAccess.LoadByPrimaryKey(LedgerNumber, AccountCode, Transaction).Rows[0];
if (AddRow != null)
{
AccountTable.Rows.Add((object[])AddRow.ItemArray.Clone());
}
}
// Populate the Results Dataset
foreach (AAccountRow Account in AccountTable.Rows)
{
DataRow NewRow = Results.NewRow();
NewRow["a_account_code_c"] = Account.AccountCode;
NewRow["a_account_code_short_desc_c"] = Account.AccountCodeShortDesc;
int GLMSequence = TCommonBudgetMaintain.GetGLMSequenceForBudget(
LedgerNumber, Account.AccountCode, StandardSummaryCostCentre, AParameters["param_year_i"].ToInt32());
// get balances
if (Account.DebitCreditIndicator)
{
NewRow["DebitCreditIndicator"] = true;
NewRow["ActualDebitBase"] = TCommonBudgetMaintain.GetActual(LedgerNumber,
GLMSequence,
-1,
AParameters["param_end_period_i"].ToInt32(),
LedgerRow.NumberOfAccountingPeriods,
LedgerRow.CurrentFinancialYear,
AParameters["param_year_i"].ToInt32(),
true,
MFinanceConstants.CURRENCY_BASE);
NewRow["ActualDebitIntl"] = TCommonBudgetMaintain.GetActual(LedgerNumber,
GLMSequence,
-1,
AParameters["param_end_period_i"].ToInt32(),
LedgerRow.NumberOfAccountingPeriods,
LedgerRow.CurrentFinancialYear,
AParameters["param_year_i"].ToInt32(),
true,
MFinanceConstants.CURRENCY_INTERNATIONAL);
}
else
{
NewRow["DebitCreditIndicator"] = false;
NewRow["ActualCreditBase"] = TCommonBudgetMaintain.GetActual(LedgerNumber,
GLMSequence,
-1,
AParameters["param_end_period_i"].ToInt32(),
LedgerRow.NumberOfAccountingPeriods,
LedgerRow.CurrentFinancialYear,
AParameters["param_year_i"].ToInt32(),
true,
MFinanceConstants.CURRENCY_BASE);
NewRow["ActualCreditIntl"] = TCommonBudgetMaintain.GetActual(LedgerNumber,
GLMSequence,
-1,
AParameters["param_end_period_i"].ToInt32(),
LedgerRow.NumberOfAccountingPeriods,
LedgerRow.CurrentFinancialYear,
AParameters["param_year_i"].ToInt32(),
true,
MFinanceConstants.CURRENCY_INTERNATIONAL);
}
Results.Rows.Add(NewRow);
}
});
return Results;
}
public static DataTable HosaGiftsTable(Dictionary <String, TVariant>AParameters, TReportingDbAdapter DbAdapter)
{
try
{
Boolean PersonalHosa = (AParameters["param_filter_cost_centres"].ToString() == "PersonalCostcentres");
Int32 LedgerNumber = AParameters["param_ledger_number_i"].ToInt32();
String CostCentreCodes = AParameters["param_cost_centre_codes"].ToString();
Int32 IchNumber = AParameters["param_ich_number"].ToInt32();
String DateFilter = "";
if (AParameters["param_period"].ToBool() == true)
{
Int32 periodYear = AParameters["param_year_i"].ToInt32();
Int32 periodStart = AParameters["param_start_period_i"].ToInt32();
Int32 periodEnd = AParameters["param_end_period_i"].ToInt32();
DateFilter = "AND GiftBatch.a_batch_year_i = " + periodYear;
if (periodStart == periodEnd)
{
DateFilter += (" AND GiftBatch.a_batch_period_i = " + periodStart + " ");
}
else
{
DateFilter += (" AND GiftBatch.a_batch_period_i >= " + periodStart +
" AND GiftBatch.a_batch_period_i <= " + periodEnd + " ");
}
}
else
{
DateTime dateStart = AParameters["param_start_date"].ToDate();
DateTime dateEnd = AParameters["param_end_date"].ToDate();
DateFilter = "AND GiftBatch.a_gl_effective_date_d >= '" + dateStart.ToString("yyyy-MM-dd") + "'" +
" AND GiftBatch.a_gl_effective_date_d <= '" + dateEnd.ToString("yyyy-MM-dd") + "' ";
}
String Query = "SELECT ";
if (PersonalHosa)
{
Query += "LinkedCostCentre.a_cost_centre_code_c AS CostCentre, ";
}
else
{
Query += "GiftDetail.a_cost_centre_code_c AS CostCentre, ";
}
Query +=
"MotivationDetail.a_account_code_c AS AccountCode, SUM(GiftDetail.a_gift_amount_n) AS GiftBaseAmount, SUM(GiftDetail.a_gift_amount_intl_n) AS GiftIntlAmount, SUM(a_gift_transaction_amount_n) AS GiftTransactionAmount, "
+
"GiftDetail.p_recipient_key_n AS RecipientKey, Partner.p_partner_short_name_c AS RecipientShortname, " +
"Partner.p_partner_short_name_c AS Narrative " +
"FROM a_gift_detail AS GiftDetail, a_gift_batch AS GiftBatch, " +
"a_motivation_detail AS MotivationDetail, a_gift AS Gift, p_partner AS Partner";
if (PersonalHosa)
{
Query += ",PUB_a_valid_ledger_number AS LinkedCostCentre";
}
Query += " WHERE GiftDetail.a_ledger_number_i = GiftBatch.a_ledger_number_i " +
"AND GiftDetail.a_batch_number_i = GiftBatch.a_batch_number_i " +
"AND GiftDetail.a_ledger_number_i = MotivationDetail.a_ledger_number_i " +
"AND GiftDetail.a_motivation_group_code_c = MotivationDetail.a_motivation_group_code_c " +
"AND GiftDetail.a_motivation_detail_code_c = MotivationDetail.a_motivation_detail_code_c " +
"AND GiftDetail.a_ledger_number_i = Gift.a_ledger_number_i " +
"AND GiftDetail.a_batch_number_i = Gift.a_batch_number_i " +
"AND GiftDetail.a_gift_transaction_number_i = Gift.a_gift_transaction_number_i " +
"AND Partner.p_partner_key_n = GiftDetail.p_recipient_key_n " +
"AND GiftDetail.a_ledger_number_i = " + LedgerNumber + " " +
"AND GiftBatch.a_batch_status_c = '" + MFinanceConstants.BATCH_POSTED + "' " +
DateFilter;
if (PersonalHosa)
{
Query += "AND LinkedCostCentre.a_ledger_number_i = GiftDetail.a_ledger_number_i " +
"AND LinkedCostCentre.a_cost_centre_code_c IN (" + CostCentreCodes + ") " +
"AND GiftDetail.p_recipient_key_n = LinkedCostCentre.p_partner_key_n ";
}
else
{
Query += "AND GiftDetail.a_cost_centre_code_c IN (" + CostCentreCodes + ") ";
}
if (IchNumber != 0)
{
Query += "AND GiftDetail.a_ich_number_i = " + IchNumber + " ";
}
Query += "GROUP BY CostCentre, AccountCode, GiftDetail.p_recipient_key_n, Partner.p_partner_short_name_c " +
"ORDER BY Partner.p_partner_short_name_c ASC";
TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction();
TLogging.Log(Catalog.GetString("Loading data.."), TLoggingType.ToStatusBar);
DataTable resultTable = DbAdapter.RunQuery(Query, "Gifts", Transaction);
resultTable.Columns.Add("Reference", typeof(string));
foreach (DataRow r in resultTable.Rows)
{
if (DbAdapter.IsCancelled)
{
return resultTable;
}
r["Reference"] = StringHelper.PartnerKeyToStr(Convert.ToInt64(r["RecipientKey"]));
}
TLogging.Log("", TLoggingType.ToStatusBar);
return resultTable;
} // try
catch (Exception e)
{
TLogging.Log("Problem gift rows for HOSA report: " + e.ToString());
return null;
}
finally
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
} // HosaGiftsTable
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
public static DataTable OneYearMonthGivingDonorTable(Dictionary <String, TVariant>AParameters,
Int64 ARecipientKey,
TReportingDbAdapter DbAdapter)
{
TDBTransaction Transaction = null;
int LedgerNumber = AParameters["param_ledger_number_i"].ToInt32();
string Currency = AParameters["param_currency"].ToString().ToUpper() == "BASE" ? "a_gift_amount_n" : "a_gift_amount_intl_n";
// create new datatable
DataTable Results = new DataTable();
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(
IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
//TODO: Calendar vs Financial Date Handling - Check if this should use financial year start/end in all places below
string Query = "SELECT DISTINCT" +
" gift.p_donor_key_n AS DonorKey," +
" DonorPartner.p_partner_short_name_c AS DonorName," +
" DonorPartner.p_partner_class_c AS DonorClass," +
" detail.p_recipient_key_n AS RecipientKey," +
" SUM (detail." + Currency + ") AS GiftAmountTotal," +
" COUNT (detail." + Currency + ") AS TotalCount," +
" PUB_a_gift_batch.a_currency_code_c AS GiftCurrency," +
" SUM (CASE WHEN UPPER(DonorPartner.p_partner_class_c) = 'CHURCH' THEN detail." + Currency +
" ELSE 0 END) AS TotalChurches," +
" SUM (CASE WHEN UPPER(DonorPartner.p_partner_class_c) = 'PERSON' OR " +
" UPPER(DonorPartner.p_partner_class_c) = 'FAMILY' THEN detail." + Currency +
" ELSE 0 END) AS TotalIndividuals," +
" SUM (CASE WHEN gift.a_date_entered_d BETWEEN '" + AParameters["param_year"] + "-01-01'" +
" AND '" + AParameters["param_year"] + "-01-31'" +
" THEN detail." + Currency +
" ELSE 0 END) AS GiftJanuary," +
" SUM (CASE WHEN gift.a_date_entered_d >= '" + AParameters["param_year"] + "-02-01'" +
" AND gift.a_date_entered_d < '" + AParameters["param_year"] + "-03-01'" +
" THEN detail." + Currency +
" ELSE 0 END) AS GiftFebruary," +
" SUM (CASE WHEN gift.a_date_entered_d BETWEEN '" + AParameters["param_year"] + "-03-01'" +
" AND '" + AParameters["param_year"] + "-03-31'" +
" THEN detail." + Currency +
" ELSE 0 END) AS GiftMarch," +
" SUM (CASE WHEN gift.a_date_entered_d BETWEEN '" + AParameters["param_year"] + "-04-01'" +
" AND '" + AParameters["param_year"] + "-04-30'" +
" THEN detail." + Currency +
" ELSE 0 END) AS GiftApril," +
" SUM (CASE WHEN gift.a_date_entered_d BETWEEN '" + AParameters["param_year"] + "-05-01'" +
" AND '" + AParameters["param_year"] + "-05-31'" +
" THEN detail." + Currency +
" ELSE 0 END) AS GiftMay," +
" SUM (CASE WHEN gift.a_date_entered_d BETWEEN '" + AParameters["param_year"] + "-06-01'" +
" AND '" + AParameters["param_year"] + "-06-30'" +
" THEN detail." + Currency +
" ELSE 0 END) AS GiftJune," +
" SUM (CASE WHEN gift.a_date_entered_d BETWEEN '" + AParameters["param_year"] + "-07-01'" +
" AND '" + AParameters["param_year"] + "-07-31'" +
" THEN detail." + Currency +
" ELSE 0 END) AS GiftJuly," +
" SUM (CASE WHEN gift.a_date_entered_d BETWEEN '" + AParameters["param_year"] + "-08-01'" +
" AND '" + AParameters["param_year"] + "-08-31'" +
" THEN detail." + Currency +
" ELSE 0 END) AS GiftAugust," +
" SUM (CASE WHEN gift.a_date_entered_d BETWEEN '" + AParameters["param_year"] + "-09-01'" +
" AND '" + AParameters["param_year"] + "-09-30'" +
" THEN detail." + Currency +
" ELSE 0 END) AS GiftSeptember," +
" SUM (CASE WHEN gift.a_date_entered_d BETWEEN '" + AParameters["param_year"] + "-10-01'" +
" AND '" + AParameters["param_year"] + "-10-31'" +
" THEN detail." + Currency +
" ELSE 0 END) AS GiftOctober," +
" SUM (CASE WHEN gift.a_date_entered_d BETWEEN '" + AParameters["param_year"] + "-11-01'" +
" AND '" + AParameters["param_year"] + "-11-30'" +
" THEN detail." + Currency +
" ELSE 0 END) AS GiftNovember," +
" SUM (CASE WHEN gift.a_date_entered_d BETWEEN '" + AParameters["param_year"] + "-12-01'" +
" AND '" + AParameters["param_year"] + "-12-31'" +
" THEN detail." + Currency +
" ELSE 0 END) AS GiftDecember" +
" FROM" +
" PUB_a_gift as gift," +
" PUB_a_gift_detail as detail," +
" PUB_a_gift_batch," +
" PUB_p_partner AS DonorPartner" +
" WHERE" +
" detail.a_ledger_number_i = gift.a_ledger_number_i" +
" AND detail.p_recipient_key_n = " + ARecipientKey +
" AND PUB_a_gift_batch.a_batch_status_c = 'Posted'" +
" AND PUB_a_gift_batch.a_batch_number_i = gift.a_batch_number_i" +
" AND PUB_a_gift_batch.a_ledger_number_i = " + LedgerNumber +
" AND gift.a_date_entered_d BETWEEN '" + AParameters["param_from_date"].ToDate().ToString("yyyy-MM-dd") +
"' AND '" + AParameters["param_to_date"].ToDate().ToString(
"yyyy-MM-dd") + "'" +
" AND DonorPartner.p_partner_key_n = gift.p_donor_key_n" +
" AND gift.a_ledger_number_i = " + LedgerNumber +
" AND detail.a_batch_number_i = gift.a_batch_number_i" +
" AND detail.a_gift_transaction_number_i = gift.a_gift_transaction_number_i" +
" AND detail.a_modified_detail_l = false" +
" GROUP BY DonorPartner.p_partner_key_n, gift.p_donor_key_n, detail.p_recipient_key_n, DonorPartner.p_partner_short_name_c, DonorPartner.p_partner_class_c, PUB_a_gift_batch.a_currency_code_c"
+
" ORDER BY gift.p_donor_key_n";
Results = DbAdapter.RunQuery(Query, "Donors", Transaction);
});
return Results;
}
public static DataTable FeesTable(Dictionary <String, TVariant>AParameters, TReportingDbAdapter DbAdapter)
{
DataTable resultsTable = null;
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
TLogging.Log(Catalog.GetString("Loading data.."), TLoggingType.ToStatusBar);
Int32 LedgerNumber = AParameters["param_ledger_number_i"].ToInt32();
Int32 period = AParameters["param_cmbReportPeriod"].ToInt32();
Int32 YearNumber = AParameters["param_cmbYearEnding"].ToInt32();
String[] SelectedFees = AParameters["param_fee_codes"].ToString().Split(',');
Int32 FeeCols = SelectedFees.Length;
// Full report lists every gift transaction for all cost centres
// Summary report groups and summarises gifts with a common Foreign cost centre
bool FullReport = AParameters["param_rgrFees"].ToString() == "ByGiftDetail";
//
// This constant is copied from the client - it represents a reasonable maximum
// number of columns in the report, and hopefully it is as many as any field needs.
Int32 MAX_FEE_COUNT = 11;
String Query = "SELECT ";
if (!FullReport)
{
Query += "a_cost_centre.a_cost_centre_code_c AS CostCentreCode, " +
"a_cost_centre.a_cost_centre_name_c AS CostCentreName," +
"0 AS BatchNumber, " +
"0 AS TransactionNumber, " +
"0 AS DetailNumber, " +
"0 AS Field, " +
"(SELECT SUM(GiftDetail2.a_gift_amount_n) FROM a_gift_detail AS GiftDetail2, a_gift_batch AS GiftBatch2" +
" WHERE GiftDetail2.a_ledger_number_i = " + LedgerNumber +
" AND GiftDetail2.a_cost_centre_code_c = a_cost_centre.a_cost_centre_code_c" +
" AND GiftBatch2.a_ledger_number_i = GiftDetail2.a_ledger_number_i" +
" AND GiftBatch2.a_batch_number_i = GiftDetail2.a_batch_number_i" +
" AND GiftBatch2.a_batch_year_i = " + YearNumber + " AND GiftBatch2.a_batch_period_i = " + period +
") AS GiftAmount";
}
else
{
Query += "a_gift_detail.a_gift_amount_n AS GiftAmount, " +
"a_gift_detail.a_batch_number_i AS BatchNumber, " +
"a_gift_detail.a_gift_transaction_number_i AS TransactionNumber, " +
"a_gift_detail.a_detail_number_i AS DetailNumber, " +
"a_gift_detail.a_recipient_ledger_number_n AS Field, " +
"0 AS CostCentreCode, " +
"0 AS CostCentreName";
}
for (Int32 Idx = 0; Idx < MAX_FEE_COUNT; Idx++)
{
if (Idx < FeeCols)
{
Query += ", SUM(CASE WHEN (a_processed_fee.a_fee_code_c = '" + SelectedFees[Idx] +
"') THEN a_processed_fee.a_periodic_amount_n ELSE 0 END)as F" + Idx;
}
else // I'm always providing {MAX_FEE_COUNT} columns - some may be blank at RHS.
{
Query += ", 0 as F" + Idx;
}
}
Query += " FROM a_gift_batch, a_gift_detail " +
"LEFT JOIN a_processed_fee " +
"ON a_processed_fee.a_ledger_number_i = " + LedgerNumber +
" AND a_processed_fee.a_period_number_i = " + period +
" AND a_processed_fee.a_batch_number_i = a_gift_detail.a_batch_number_i" +
" AND a_processed_fee.a_gift_transaction_number_i = a_gift_detail.a_gift_transaction_number_i" +
" AND a_processed_fee.a_detail_number_i = a_gift_detail.a_detail_number_i ";
if (!FullReport)
{
Query += "JOIN a_cost_centre " +
"ON a_cost_centre.a_ledger_number_i = a_processed_fee.a_ledger_number_i " +
"AND a_cost_centre.a_cost_centre_code_c = a_processed_fee.a_cost_centre_code_c " +
"AND a_cost_centre.a_cost_centre_type_c = 'Foreign' ";
}
Query += "WHERE " +
"a_gift_batch.a_ledger_number_i = " + LedgerNumber +
" AND a_gift_batch.a_batch_year_i = " + YearNumber + " AND a_gift_batch.a_batch_period_i = " + period +
" AND a_gift_detail.a_ledger_number_i = " + LedgerNumber +
" AND a_gift_batch.a_batch_number_i = a_gift_detail.a_batch_number_i ";
if (!FullReport)
{
Query += "GROUP BY CostCentreCode, CostCentreName " +
"ORDER BY CostCentreCode";
}
else
{
Query += "GROUP BY GiftAmount, BatchNumber, TransactionNumber, DetailNumber, Field " +
"ORDER BY BatchNumber, TransactionNumber, DetailNumber";
}
TLogging.Log(Catalog.GetString(""), TLoggingType.ToStatusBar);
resultsTable = DbAdapter.RunQuery(Query, "Fees", Transaction);
}); // Get NewOrExisting AutoReadTransaction
return resultsTable;
} // Fees Table
public static DataTable TrialBalanceTable(Dictionary <String, TVariant>AParameters, TReportingDbAdapter DbAdapter)
{
/* Required columns:
* CostCentreCode
* CostCentreName
* AccountCode
* AccountName
* Debit
* Credit
*/
/*
* Trial balance is simply a list of all the account / cost centre balanaces, at the end of the period specified.
* (If the period is open, it still works.)
*
* Trial balance works on Posting accounts and cost centres, so there's no chasing up the hierarchy tree.
*/
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 CostCentreOptions = AParameters["param_rgrCostCentres"].ToString();
if (CostCentreOptions == "CostCentreList")
{
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";
}
String AccountCodeFilter = "";
String AccountCodeOptions = AParameters["param_rgrAccounts"].ToString();
if (AccountCodeOptions == "AccountList")
{
String AccountCodeList = AParameters["param_account_codes"].ToString();
AccountCodeList = AccountCodeList.Replace(",", "','"); // SQL IN List items in single quotes
AccountCodeFilter = " AND glm.a_account_code_c in ('" + AccountCodeList + "')";
}
if (AccountCodeOptions == "AccountRange")
{
AccountCodeFilter = " AND glm.a_account_code_c >='" + AParameters["param_account_code_start"].ToString() +
"' AND glm.a_account_code_c <='" + AParameters["param_account_code_end"].ToString() + "'";
}
if (AccountCodeOptions == "AllActiveAccounts") // THIS IS NOT SET AT ALL
{
AccountCodeFilter = " AND a_account.a_account_active_flag_l=true";
}
TDBTransaction ReadTrans = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadCommitted);
DataTable resultTable = null;
String OrderBy = " ORDER BY a_cost_centre.a_cost_centre_type_c DESC, glm.a_cost_centre_code_c, glm.a_account_code_c";
if (AParameters["param_sortby"].ToString() == "Account")
{
OrderBy = " ORDER BY glm.a_account_code_c, a_cost_centre.a_cost_centre_type_c DESC, glm.a_cost_centre_code_c";
}
try
{
String Query = "SELECT DISTINCT" +
" 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," +
" a_account.a_debit_credit_indicator_l AS IsDebit," +
" glmp." + ActualFieldName + " AS Balance," +
" 0.0 as Debit, " +
" 0.0 as Credit, " +
" glm.a_account_code_c AS AccountCode," +
" a_account.a_account_code_short_desc_c AS AccountName" +
" 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." + ActualFieldName + " <> 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 +
OrderBy;
TLogging.Log(Catalog.GetString("Loading data.."), TLoggingType.ToStatusBar);
resultTable = DbAdapter.RunQuery(Query, "TrialBalance", ReadTrans);
foreach (DataRow Row in resultTable.Rows)
{
Decimal Amount = Convert.ToDecimal(Row["Balance"]);
Boolean IsDebit = Convert.ToBoolean(Row["IsDebit"]);
if (Amount < 0)
{
IsDebit = !IsDebit;
Amount = 0 - Amount;
}
String ToField = IsDebit ? "Debit" : "Credit";
Row[ToField] = Amount;
}
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 resultTable;
} // TrialBalanceTable
public static DataTable AFOTable(Dictionary <String, TVariant>AParameters, TReportingDbAdapter DbAdapter)
{
AAccountTable AccountTable = new AAccountTable();
int LedgerNumber = AParameters["param_ledger_number_i"].ToInt32();
string StandardSummaryCostCentre = LedgerNumber.ToString("00") + "00S";
Int32 Year = AParameters["param_year_i"].ToInt32();
Int32 Period = AParameters["param_end_period_i"].ToInt32();
string AccountHierarchyCode = AParameters["param_account_hierarchy_c"].ToString();
String CostCentreFilter = GetReportingCostCentres(LedgerNumber, StandardSummaryCostCentre, "");
CostCentreFilter = " AND glm.a_cost_centre_code_c in ('" + CostCentreFilter.Replace(",", "','") + "') ";
// create new datatable
DataTable Results = new DataTable();
Results.Columns.Add(new DataColumn("a_account_code_c", typeof(string)));
Results.Columns.Add(new DataColumn("a_account_code_short_desc_c", typeof(string)));
Results.Columns.Add(new DataColumn("DebitCreditIndicator", typeof(bool)));
Results.Columns.Add(new DataColumn("ActualDebitBase", typeof(Decimal)));
Results.Columns.Add(new DataColumn("ActualCreditBase", typeof(Decimal)));
Results.Columns.Add(new DataColumn("ActualDebitIntl", typeof(Decimal)));
Results.Columns.Add(new DataColumn("ActualCreditIntl", typeof(Decimal)));
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
ALedgerRow LedgerRow = (ALedgerRow)ALedgerAccess.LoadByPrimaryKey(LedgerNumber, Transaction).Rows[0];
AAccountHierarchyRow HierarchyRow = (AAccountHierarchyRow)AAccountHierarchyAccess.LoadByPrimaryKey(
LedgerNumber, AccountHierarchyCode, Transaction).Rows[0];
List <String>list = new List <string>();
ScanHierarchy(ref list, LedgerNumber, HierarchyRow.RootAccountCode, AccountHierarchyCode, Transaction);
// get AAccountRows for each account number in list
foreach (string AccountCode in list)
{
AAccountRow AddRow = (AAccountRow)AAccountAccess.LoadByPrimaryKey(LedgerNumber, AccountCode, Transaction).Rows[0];
if (AddRow != null)
{
AccountTable.Rows.Add((object[])AddRow.ItemArray.Clone());
}
}
// Populate the Results Dataset
foreach (AAccountRow Account in AccountTable.Rows)
{
String ActualField = Account.DebitCreditIndicator ? "ActualDebitBase" : "ActualCreditBase";
String IntlField = Account.DebitCreditIndicator ? "ActualDebitIntl" : "ActualCreditIntl";
String AccountFilter = GetReportingAccounts(LedgerNumber, Account.AccountCode, "");
AccountFilter = " AND glm.a_account_code_c IN ('" + AccountFilter.Replace(",",
"','") + "')";
String subtractOrAddBase = (Account.DebitCreditIndicator) ?
"CASE WHEN debit=TRUE THEN Base ELSE 0-Base END"
:
"CASE WHEN debit=TRUE THEN 0-Base ELSE Base END";
String subtractOrAddIntl = (Account.DebitCreditIndicator) ?
"CASE WHEN debit=TRUE THEN Intl ELSE 0-Intl END"
:
"CASE WHEN debit=TRUE THEN 0-Intl ELSE Intl END";
String Query =
"SELECT sum(" + subtractOrAddBase + ") AS Actual," +
" sum(" + subtractOrAddIntl + ") AS ActualIntl" +
" FROM" +
" (SELECT DISTINCT a_account.a_account_code_c AS AccountCode, glm.a_cost_centre_code_c AS CostCentreCode, a_account.a_debit_credit_indicator_l AS debit,"
+
" glmp.a_actual_base_n AS Base, glmp.a_actual_intl_n AS Intl" +
" FROM a_general_ledger_master AS glm, a_general_ledger_master_period AS glmp, a_account" +
" WHERE glm.a_glm_sequence_i=glmp.a_glm_sequence_i" +
" AND glm.a_account_code_c=a_account.a_account_code_c" +
" AND a_account.a_ledger_number_i=" + LedgerNumber +
" AND glm.a_ledger_number_i=" + LedgerNumber +
" AND glm.a_year_i=" + Year +
" AND glmp.a_period_number_i=" + Period +
AccountFilter +
CostCentreFilter +
") AS AllGlm";
DataTable tempTable = DbAdapter.RunQuery(Query, "AFO", Transaction);
DataRow NewRow = Results.NewRow();
NewRow["a_account_code_c"] = Account.AccountCode;
NewRow["a_account_code_short_desc_c"] = Account.AccountCodeShortDesc;
NewRow["DebitCreditIndicator"] = Account.DebitCreditIndicator;
NewRow[ActualField] = tempTable.Rows[0]["Actual"];
NewRow[IntlField] = tempTable.Rows[0]["ActualIntl"];
Results.Rows.Add(NewRow);
}
}); // Get NewOrExisting AutoReadTransaction
return Results;
} // AFO Table
public static DataTable GetPeriodBalances(String ALedgerFilter,
String AAccountCodeFilter,
String ACostCentreFilter,
Int32 AFinancialYear,
String ASortBy,
DataTable ATransactionsTbl,
Int32 AStartPeriod,
Int32 AEndPeriod,
Boolean AInternational)
{
DataTable Results = new DataTable();
Results.Columns.Add(new DataColumn("a_cost_centre_code_c", typeof(string)));
Results.Columns.Add(new DataColumn("a_account_code_c", typeof(string)));
Results.Columns.Add(new DataColumn("OpeningBalance", typeof(Decimal)));
Results.Columns.Add(new DataColumn("ClosingBalance", typeof(Decimal)));
try
{
Boolean FromStartOfYear = (AStartPeriod == 1);
TReportingDbAdapter DbAdapter = new TReportingDbAdapter();
if (!FromStartOfYear)
{
AStartPeriod -= 1; // I want the closing balance of the previous period.
}
TDBTransaction ReadTrans = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadCommitted);
String BalanceField = (AInternational) ? "glmp.a_actual_intl_n" : "glmp.a_actual_base_n";
String StartBalanceField = (AInternational) ? "glm.a_start_balance_intl_n" : "glm.a_start_balance_base_n";
String GroupField = "";
if (ASortBy == "Account")
{
GroupField = " ORDER BY glm.a_account_code_c, glm.a_cost_centre_code_c";
}
if (ASortBy == "Cost Centre")
{
GroupField = " ORDER BY glm.a_cost_centre_code_c, glm.a_account_code_c";
}
String Query = "SELECT glm.a_cost_centre_code_c, glm.a_account_code_c, glmp.a_period_number_i, " +
"a_account.a_debit_credit_indicator_l AS Debit, " +
StartBalanceField + " AS start_balance, " +
BalanceField + " AS balance " +
" FROM a_general_ledger_master AS glm, a_general_ledger_master_period AS glmp, a_account, a_cost_centre" +
" WHERE glm." + ALedgerFilter +
" AND a_account." + ALedgerFilter +
" AND a_cost_centre." + ALedgerFilter +
" AND a_account.a_posting_status_l = TRUE" +
" AND a_cost_centre.a_posting_cost_centre_flag_l = TRUE" +
" AND glm.a_year_i = " + AFinancialYear +
" AND glm.a_account_code_c = a_account.a_account_code_c " +
" AND glm.a_cost_centre_code_c = a_cost_centre.a_cost_centre_code_c " +
AAccountCodeFilter +
ACostCentreFilter +
" AND glm.a_glm_sequence_i = glmp.a_glm_sequence_i" +
" AND glmp.a_period_number_i BETWEEN " + AStartPeriod + " AND " + AEndPeriod +
GroupField;
DataTable GlmTbl = DbAdapter.RunQuery(Query, "balances", ReadTrans);
String CostCentre = "";
String AccountCode = "";
Int32 MaxPeriod = -1;
Int32 MinPeriod = 99;
DataRow NewRow = null;
Decimal MakeItDebit = 1;
// For each CostCentre / Account combination I want just a single row, with the opening and closing balances,
// so I need to pre-process the stuff I've got in this table, and generate rows in the Results table.
foreach (DataRow row in GlmTbl.Rows)
{
if (DbAdapter.IsCancelled)
{
return Results;
}
MakeItDebit = (Convert.ToBoolean(row["Debit"])) ? -1 : 1;
if ((row["a_cost_centre_code_c"].ToString() != CostCentre) || (row["a_account_code_c"].ToString() != AccountCode)) // a new CC/AC combination
{
NewRow = Results.NewRow();
CostCentre = row["a_cost_centre_code_c"].ToString();
NewRow["a_cost_centre_code_c"] = CostCentre;
AccountCode = row["a_account_code_c"].ToString();
MakeItDebit = (Convert.ToBoolean(row["Debit"])) ? -1 : 1;
NewRow["a_account_code_c"] = AccountCode;
Results.Rows.Add(NewRow);
MaxPeriod = -1;
MinPeriod = 99;
}
Int32 ThisPeriod = Convert.ToInt32(row["a_period_number_i"]);
if (ThisPeriod < MinPeriod)
{
MinPeriod = ThisPeriod;
Decimal OpeningBalance = (FromStartOfYear) ? Convert.ToDecimal(row["start_balance"]) : Convert.ToDecimal(row["balance"]);
NewRow["OpeningBalance"] = MakeItDebit * OpeningBalance;
}
if (ThisPeriod > MaxPeriod)
{
MaxPeriod = ThisPeriod;
Decimal ClosingBalance = Convert.ToDecimal(row["balance"]);
NewRow["ClosingBalance"] = MakeItDebit * ClosingBalance;
}
} // foreach
for (Int32 Idx = Results.Rows.Count - 1; Idx >= 0; Idx--)
{
DataRow ResultsRow = Results.Rows[Idx];
//
// Since a revision in October 2014, this balances table can be the master table for the Account Detail report
// (That is, "for each opening and closing balance, list any applicable transactions",
// rather than, "for each Account/Cost Centre combination where we say transactions, show opening and closing balance".)
// The effect of this is I need to remove opening and closing balances both are zero,
// AND there were no transactions in the selected period.
if ((Convert.ToDecimal(ResultsRow["OpeningBalance"]) == 0) && (Convert.ToDecimal(ResultsRow["ClosingBalance"]) == 0))
{
ATransactionsTbl.DefaultView.RowFilter = String.Format("AccountCode='{0}' AND CostCentreCode ='{1}'",
ResultsRow["a_account_code_c"],
ResultsRow["a_cost_centre_code_c"]);
if (ATransactionsTbl.DefaultView.Count == 0)
{
ResultsRow.Delete();
}
}
}
Results.AcceptChanges();
} // 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();
}
return Results;
}
public static DataTable FieldLeaderGiftSummary(Dictionary <String, TVariant>AParameters, TReportingDbAdapter DbAdapter)
{
String selectedFieldList = AParameters["param_clbFields"].ToString();
selectedFieldList = selectedFieldList.Replace('\'', ' ');
String FieldFilter = " AND partnerfield.p_partner_key_n IN (" + selectedFieldList + ") ";
Int32 LedgerNum = AParameters["param_ledger_number_i"].ToInt32();
DateTime PeriodStart = AParameters["param_from_date"].ToDate();
DateTime PeriodEnd = AParameters["param_to_date"].ToDate();
String PeriodRange = "BETWEEN '" + PeriodStart.ToString("yyyy-MM-dd") + "' AND '" + PeriodEnd.ToString("yyyy-MM-dd") + "'";
Int32 PeriodYear = PeriodEnd.Year;
String amountField = (AParameters["param_currency"].ToString().StartsWith("Int")) ? "a_gift_amount_intl_n" : "a_gift_amount_n";
DateTime Year = new DateTime(AParameters["param_year0"].ToInt32(), 1, 1);
String Year1Range = "BETWEEN '" + Year.ToString("yyyy-MM-dd") + "' AND '" + Year.AddYears(1).AddDays(-1).ToString("yyyy-MM-dd") + "'";
Year = new DateTime(AParameters["param_year1"].ToInt32(), 1, 1);
String Year2Range = "BETWEEN '" + Year.ToString("yyyy-MM-dd") + "' AND '" + Year.AddYears(1).AddDays(-1).ToString("yyyy-MM-dd") + "'";
Year = new DateTime(AParameters["param_year2"].ToInt32(), 1, 1);
String Year3Range = "BETWEEN '" + Year.ToString("yyyy-MM-dd") + "' AND '" + Year.AddYears(1).AddDays(-1).ToString("yyyy-MM-dd") + "'";
Year = new DateTime(AParameters["param_year3"].ToInt32(), 1, 1);
String Year4Range = "BETWEEN '" + Year.ToString("yyyy-MM-dd") + "' AND '" + Year.AddYears(1).AddDays(-1).ToString("yyyy-MM-dd") + "'";
DateTime FirstDate = Year;
if (PeriodStart < FirstDate)
{
FirstDate = PeriodStart;
}
//TODO: Calendar vs Financial Date Handling - Check if this should use financial year start/end and not assume calendar
String TotalDateRange = "BETWEEN '" + FirstDate.ToString("yyyy-MM-dd") + "' AND '" + new DateTime(DateTime.Today.Year, 12, 31).ToString(
"yyyy-MM-dd") + "'";
String Query =
" SELECT * FROM (" +
" SELECT " +
" partnerfield.p_partner_key_n AS FieldKey," +
" partnerfield.p_partner_short_name_c AS FieldName,"
+ " partnerrecipient.p_partner_key_n AS RecipientKey," +
" partnerrecipient.p_partner_short_name_c AS RecipientName," +
" partnerrecipient.p_partner_class_c AS RecipientClass," +
" SUM(CASE WHEN gift.a_date_entered_d " + PeriodRange + " THEN detail." + amountField + " ELSE 0 END) AS AmountPeriod," +
" SUM(CASE WHEN gift.a_date_entered_d " + Year1Range + " THEN detail." + amountField + " ELSE 0 END) AS AmountYear1," +
" SUM(CASE WHEN gift.a_date_entered_d " + Year2Range + " THEN detail." + amountField + " ELSE 0 END) AS AmountYear2," +
" SUM(CASE WHEN gift.a_date_entered_d " + Year3Range + " THEN detail." + amountField + " ELSE 0 END) AS AmountYear3," +
" SUM(CASE WHEN gift.a_date_entered_d " + Year4Range + " THEN detail." + amountField + " ELSE 0 END) AS AmountYear4"
+ " FROM" +
" PUB_p_partner as partnerfield," +
" PUB_p_partner as partnerrecipient," +
" PUB_a_gift as gift," +
" PUB_a_gift_detail as detail," +
" PUB_a_gift_batch as giftbatch"
+ " WHERE" +
" detail.a_batch_number_i = gift.a_batch_number_i" +
" AND detail.a_gift_transaction_number_i = gift.a_gift_transaction_number_i" +
" AND detail.a_recipient_ledger_number_n = partnerfield.p_partner_key_n" +
" AND detail.a_ledger_number_i = " + LedgerNum +
" AND gift.a_ledger_number_i = " + LedgerNum +
" AND gift.a_date_entered_d " + TotalDateRange +
" AND giftbatch.a_batch_status_c = 'Posted'" +
" AND giftbatch.a_batch_number_i = gift.a_batch_number_i" +
" AND giftbatch.a_ledger_number_i = " + LedgerNum
+ " AND partnerrecipient.p_partner_key_n = detail.p_recipient_key_n"
+ FieldFilter +
" GROUP BY partnerfield.p_partner_short_name_c, partnerfield.p_partner_key_n, partnerrecipient.p_partner_key_n, partnerrecipient.p_partner_short_name_c, partnerrecipient.p_partner_class_c "
+
") AllData WHERE AmountPeriod > 0 ORDER BY FieldKey, RecipientName"
;
DataTable resultTable = new DataTable();
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
resultTable = DbAdapter.RunQuery(Query, "FieldLeaderGiftSummary", Transaction);
});
return resultTable;
} // Field Leader Gift Summary
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
} // Total Gifts Through Field
// get Actuals for this month, YTD and Prior YTD and Budget YTD
private static Decimal[] GetActualsAndBudget(TReportingDbAdapter DbAdapter,
ALedgerRow ALedger, string AAccountCode, string ACostCentreCode, int APeriodNumber, int AYear)
{
decimal[] Results = new decimal[4];
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
String YearFilter = String.Format(" AND glm.a_year_i in({0},{1})", AYear - 1, AYear);
String PeriodFilter = (APeriodNumber > 1) ?
String.Format(" AND glmp.a_period_number_i IN ({0},{1})", APeriodNumber - 1, APeriodNumber)
:
" AND glmp.a_period_number_i=1";
AAccountRow AccountRow = (AAccountRow)AAccountAccess.LoadByPrimaryKey(ALedger.LedgerNumber, AAccountCode, Transaction).Rows[0];
String AccountFilter = GetReportingAccounts(ALedger.LedgerNumber, AAccountCode, "");
AccountFilter = " AND glm.a_account_code_c IN ('" + AccountFilter.Replace(",", "','") + "')";
String CostCentreFilter = GetReportingCostCentres(ALedger.LedgerNumber, ACostCentreCode, "");
CostCentreFilter = " AND glm.a_cost_centre_code_c in ('" + CostCentreFilter.Replace(",",
"','") + "') ";
String subtractOrAddBase = (AccountRow.DebitCreditIndicator) ?
"CASE WHEN debit=TRUE THEN Base ELSE 0-Base END"
:
"CASE WHEN debit=TRUE THEN 0-Base ELSE Base END";
String subtractOrAddLastMonth = (APeriodNumber == 1) ?
"CASE WHEN Year=" + AYear + " THEN " + (
(AccountRow.DebitCreditIndicator) ?
"CASE WHEN debit=TRUE THEN YearStart ELSE 0-YearStart END"
:
"CASE WHEN debit=TRUE THEN 0-YearStart ELSE YearStart END") +
" END"
:
"CASE WHEN Year=" + AYear + " AND Period=" +
(APeriodNumber - 1) + " THEN " + subtractOrAddBase + " END";
String subtractOrAddThisYear = "CASE WHEN Year=" + AYear + " AND Period=" + APeriodNumber + " THEN " + subtractOrAddBase + " END";
String subtractOrAddLastYear = "CASE WHEN Year=" +
(AYear - 1) + " AND Period=" + APeriodNumber + " THEN " + subtractOrAddBase + " END";
String subtractOrAddBudget = "CASE WHEN Year=" + AYear + " AND Period=" + APeriodNumber + " THEN " + (
(AccountRow.DebitCreditIndicator) ?
"CASE WHEN debit=TRUE THEN Budget ELSE 0-Budget END"
:
"CASE WHEN debit=TRUE THEN 0-Budget ELSE Budget END") +
" END";
String Query =
"SELECT sum(" + subtractOrAddLastMonth + ") AS SumLastMonthYtd," +
" sum(" + subtractOrAddThisYear + ") AS SumYtd," +
" sum(" + subtractOrAddLastYear + ") AS SumLastYear," +
" sum(" + subtractOrAddBudget + ") AS SumBudget" +
" FROM" +
" (SELECT DISTINCT a_account.a_account_code_c AS AccountCode, glm.a_cost_centre_code_c AS CostCentreCode, a_account.a_debit_credit_indicator_l AS debit,"
+
" glm.a_year_i AS Year," +
" glm.a_start_balance_base_n AS YearStart," +
" glmp.a_period_number_i AS Period," +
" glmp.a_actual_base_n AS Base, 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_glm_sequence_i=glmp.a_glm_sequence_i" +
" AND glm.a_account_code_c=a_account.a_account_code_c" +
" AND a_account.a_ledger_number_i=" + ALedger.LedgerNumber +
" AND glm.a_ledger_number_i=" + ALedger.LedgerNumber +
YearFilter +
PeriodFilter +
AccountFilter +
CostCentreFilter +
") AS AllGlm";
DataTable tempTable = DbAdapter.RunQuery(Query, "ExecSummary", Transaction);
if (tempTable.Rows.Count > 0)
{
Results[0] = Convert.ToDecimal(tempTable.Rows[0]["SumYtd"]) - Convert.ToDecimal(tempTable.Rows[0]["SumLastMonthYtd"]);
Results[1] = Convert.ToDecimal(tempTable.Rows[0]["SumLastYear"]);
Results[2] = Convert.ToDecimal(tempTable.Rows[0]["SumYtd"]);
Results[3] = Convert.ToDecimal(tempTable.Rows[0]["SumBudget"]);
}
/*
* int GLMSeqLastYear = TCommonBudgetMaintain.GetGLMSequenceForBudget(
* ALedger.LedgerNumber, AAccountCode, ACostCentreCode, AYear - 1);
* int GLMSeqThisYear = TCommonBudgetMaintain.GetGLMSequenceForBudget(
* ALedger.LedgerNumber, AAccountCode, ACostCentreCode, AYear);
* int GLMSeqNextYear = TCommonBudgetMaintain.GetGLMSequenceForBudget(
* ALedger.LedgerNumber, AAccountCode, ACostCentreCode, AYear + 1);
*
* Results[0] = TCommonBudgetMaintain.GetActual(ALedger.LedgerNumber,
* GLMSeqThisYear,
* -1,
* APeriodNumber,
* ALedger.NumberOfAccountingPeriods,
* ALedger.CurrentFinancialYear,
* AYear,
* false,
* MFinanceConstants.CURRENCY_BASE);
* Results[1] = TCommonBudgetMaintain.GetActual(ALedger.LedgerNumber,
* GLMSeqLastYear,
* GLMSeqThisYear,
* APeriodNumber,
* ALedger.NumberOfAccountingPeriods,
* ALedger.CurrentFinancialYear,
* AYear - 1,
* true,
* MFinanceConstants.CURRENCY_BASE);
* Results[2] = TCommonBudgetMaintain.GetActual(ALedger.LedgerNumber,
* GLMSeqThisYear,
* -1,
* APeriodNumber,
* ALedger.NumberOfAccountingPeriods,
* ALedger.CurrentFinancialYear,
* AYear,
* true,
* MFinanceConstants.CURRENCY_BASE);
* Results[3] = TCommonBudgetMaintain.GetBudget(GLMSeqThisYear,
* GLMSeqNextYear,
* APeriodNumber,
* ALedger.NumberOfAccountingPeriods,
* true,
* MFinanceConstants.CURRENCY_BASE);
*/
});
return Results;
}
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 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 HosaGiftsTable(Dictionary <String, TVariant>AParameters, TReportingDbAdapter DbAdapter)
{
DataTable resultTable = null;
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
Boolean PersonalHosa = (AParameters["param_filter_cost_centres"].ToString() == "PersonalCostcentres");
Int32 LedgerNumber = AParameters["param_ledger_number_i"].ToInt32();
String CostCentreCodes = AParameters["param_cost_centre_codes"].ToString();
String LinkedCC_CCFilter = "";
String GiftDetail_CCfilter = "";
if (CostCentreCodes != "ALL")
{
LinkedCC_CCFilter = " AND LinkedCostCentre.a_cost_centre_code_c IN (" + CostCentreCodes + ") ";
GiftDetail_CCfilter = " AND GiftDetail.a_cost_centre_code_c IN (" + CostCentreCodes + ") ";
}
Int32 IchNumber = AParameters["param_ich_number"].ToInt32();
String DateFilter = "";
if (AParameters["param_period"].ToBool() == true)
{
Int32 periodYear = AParameters["param_year_i"].ToInt32();
Int32 periodStart = AParameters["param_start_period_i"].ToInt32();
Int32 periodEnd = AParameters["param_end_period_i"].ToInt32();
DateFilter = "AND GiftBatch.a_batch_year_i = " + periodYear;
if (periodStart == periodEnd)
{
DateFilter += (" AND GiftBatch.a_batch_period_i = " + periodStart + " ");
}
else
{
DateFilter += (" AND GiftBatch.a_batch_period_i >= " + periodStart +
" AND GiftBatch.a_batch_period_i <= " + periodEnd + " ");
}
}
else
{
DateTime dateStart = AParameters["param_start_date"].ToDate();
DateTime dateEnd = AParameters["param_end_date"].ToDate();
DateFilter = "AND GiftBatch.a_gl_effective_date_d >= '" + dateStart.ToString("yyyy-MM-dd") + "'" +
" AND GiftBatch.a_gl_effective_date_d <= '" + dateEnd.ToString("yyyy-MM-dd") + "' ";
}
bool TaxDeductiblePercentageEnabled = Convert.ToBoolean(
TSystemDefaults.GetSystemDefault(SharedConstants.SYSDEFAULT_TAXDEDUCTIBLEPERCENTAGE, "FALSE"));
String Query = string.Empty;
// If tax deductibility % is enabled then we need to use two querys - one for tax deduct and one for non-tax deduct.
// The results from these queries are then merged together.
if (TaxDeductiblePercentageEnabled)
{
Query = "SELECT ";
if (PersonalHosa)
{
Query += "UnionTable.CostCentre AS CostCentre, ";
}
else
{
Query += "UnionTable.CostCentre AS CostCentre, ";
}
Query +=
"UnionTable.AccountCode AS AccountCode, " +
"SUM(UnionTable.GiftBaseAmount) AS GiftBaseAmount, " +
"SUM(UnionTable.GiftIntlAmount) AS GiftIntlAmount, " +
"SUM(UnionTable.GiftTransactionAmount) AS GiftTransactionAmount, " +
"UnionTable.RecipientKey AS RecipientKey, " +
"UnionTable.RecipientShortname AS RecipientShortname, " +
"UnionTable.Narrative AS Narrative " +
"FROM (" +
GetHOSASQLQuery(true, false, PersonalHosa, LedgerNumber, DateFilter, IchNumber, LinkedCC_CCFilter, GiftDetail_CCfilter) +
" UNION ALL " +
GetHOSASQLQuery(true,
true,
PersonalHosa,
LedgerNumber,
DateFilter,
IchNumber,
LinkedCC_CCFilter,
GiftDetail_CCfilter) +
") AS UnionTable " +
"GROUP BY Uniontable.Narrative, UnionTable.CostCentre, UnionTable.AccountCode, UnionTable.RecipientKey, UnionTable.RecipientShortname ";
}
else
{
Query =
GetHOSASQLQuery(false, false, PersonalHosa, LedgerNumber, DateFilter, IchNumber, LinkedCC_CCFilter, GiftDetail_CCfilter);
}
Query += " ORDER BY CostCentre, AccountCode, RecipientShortname ASC";
TLogging.Log(Catalog.GetString("Loading data.."), TLoggingType.ToStatusBar);
resultTable = DbAdapter.RunQuery(Query, "Gifts", Transaction);
resultTable.Columns.Add("Reference", typeof(string));
foreach (DataRow r in resultTable.Rows)
{
if (DbAdapter.IsCancelled)
{
return;
}
r["Reference"] = StringHelper.PartnerKeyToStr(Convert.ToInt64(r["RecipientKey"]));
}
TLogging.Log("", TLoggingType.ToStatusBar);
return;
}); // Get NewOrExisting AutoReadTransaction
return resultTable;
} // Hosa Gifts Table
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 KeyMinGiftsTable(Dictionary <String, TVariant>AParameters, TReportingDbAdapter DbAdapter)
{
DataTable resultTable = null;
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
Int32 LedgerNumber = AParameters["param_ledger_number_i"].ToInt32();
String CostCentreCodes = AParameters["param_cost_centre_codes"].ToString();
String CCfilter = "";
if (CostCentreCodes == "ALL")
{
CCfilter = "AND CostCentre.a_cost_centre_code_c=GiftDetail.a_cost_centre_code_c " +
"AND CostCentre.a_cost_centre_type_c='" + MFinanceConstants.FOREIGN_CC_TYPE + "' " +
"AND CostCentre.a_ledger_number_i=" + LedgerNumber + " ";
}
else
{
CCfilter = " AND GiftDetail.a_cost_centre_code_c IN (" + CostCentreCodes + ") ";
}
Int32 IchNumber = AParameters["param_ich_number"].ToInt32();
String DateFilter = "";
if (AParameters["param_period"].ToBool() == true)
{
Int32 periodYear = AParameters["param_year_i"].ToInt32();
Int32 periodStart = AParameters["param_start_period_i"].ToInt32();
Int32 periodEnd = AParameters["param_end_period_i"].ToInt32();
DateFilter = "AND GiftBatch.a_batch_year_i = " + periodYear;
if (periodStart == periodEnd)
{
DateFilter += (" AND GiftBatch.a_batch_period_i = " + periodStart + " ");
}
else
{
DateFilter += (" AND GiftBatch.a_batch_period_i >= " + periodStart +
" AND GiftBatch.a_batch_period_i <= " + periodEnd + " ");
}
}
else
{
DateTime dateStart = AParameters["param_start_date"].ToDate();
DateTime dateEnd = AParameters["param_end_date"].ToDate();
DateFilter = "AND GiftBatch.a_gl_effective_date_d >= '" + dateStart.ToString("yyyy-MM-dd") + "'" +
" AND GiftBatch.a_gl_effective_date_d <= '" + dateEnd.ToString(
"yyyy-MM-dd") + "' ";
}
String Query = "SELECT " +
"GiftBatch.a_gl_effective_date_d AS date, " +
"GiftDetail.a_cost_centre_code_c AS CostCentreCode, " +
"GiftDetail.a_gift_amount_n AS GiftBaseAmount, " +
"GiftDetail.a_gift_amount_intl_n AS GiftIntlAmount, " +
"CASE WHEN GiftDetail.a_recipient_ledger_number_n=GiftDetail.p_recipient_key_n THEN 'FIELD' ELSE 'KEYMIN' END AS RecipientType, "
+
"GiftDetail.p_recipient_key_n AS RecipientKey, " +
"Recipient.p_partner_short_name_c AS RecipientShortname, " +
"Donor.p_partner_key_n AS DonorKey, " +
"Donor.p_partner_short_name_c AS DonorShortname " +
"FROM a_gift_detail AS GiftDetail, a_gift AS Gift, a_gift_batch AS GiftBatch, ";
if (CostCentreCodes == "ALL")
{
Query += "a_cost_centre AS CostCentre, ";
}
Query += "p_partner AS Donor, p_partner AS Recipient " +
"WHERE GiftBatch.a_ledger_number_i = " + LedgerNumber + " " +
"AND GiftDetail.a_batch_number_i = GiftBatch.a_batch_number_i " +
"AND (GiftDetail.a_recipient_ledger_number_n = GiftDetail.p_recipient_key_n " + // Field Gifts
"OR (SELECT COUNT(p_partner_key_n) FROM p_unit WHERE p_unit.p_partner_key_n=GiftDetail.p_recipient_key_n AND p_unit.u_unit_type_code_c='KEY-MIN') > 0) "
+
"AND Gift.a_ledger_number_i = " + LedgerNumber + " " +
"AND Gift.a_batch_number_i = GiftDetail.a_batch_number_i " +
"AND Gift.a_gift_transaction_number_i = GiftDetail.a_gift_transaction_number_i " +
CCfilter +
"AND Donor.p_partner_key_n = Gift.p_donor_key_n " +
"AND Recipient.p_partner_key_n = GiftDetail.p_recipient_key_n " +
"AND GiftDetail.a_ledger_number_i = " + LedgerNumber + " " +
"AND GiftBatch.a_batch_status_c = '" + MFinanceConstants.BATCH_POSTED + "' " +
DateFilter;
if (IchNumber != 0)
{
Query += "AND GiftDetail.a_ich_number_i = " + IchNumber + " ";
}
Query += "ORDER BY CostCentreCode, RecipientType Desc, RecipientKey, DonorKey";
TLogging.Log(Catalog.GetString("Loading data.."), TLoggingType.ToStatusBar);
resultTable = DbAdapter.RunQuery(Query, "Recipient", Transaction);
TLogging.Log("", TLoggingType.ToStatusBar);
return;
}); // Get NewOrExisting AutoReadTransaction
return resultTable;
} // KeyMin Gifts Table
public static DataSet GetRecipientGiftStatementDataSet(Dictionary <String, TVariant>AParameters)
{
string ReportType = AParameters["param_report_type"].ToString();
FDbAdapter = new TReportingDbAdapter();
TLogging.SetStatusBarProcedure(WriteToStatusBar);
DataSet ReturnDataSet = new DataSet();
// get recipients
DataTable Recipients = TFinanceReportingWebConnector.RecipientGiftStatementRecipientTable(AParameters, FDbAdapter);
if (FDbAdapter.IsCancelled || (Recipients == null))
{
return null;
}
DataTable RecipientTotals = new DataTable("RecipientTotals");
RecipientTotals.Columns.Add("PreviousYearTotal", typeof(decimal));
RecipientTotals.Columns.Add("CurrentYearTotal", typeof(decimal));
DataTable Donors = new DataTable("Donors");
foreach (DataRow Row in Recipients.Rows)
{
if (ReportType == "Complete")
{
// get year totals for recipient
RecipientTotals.Merge(TFinanceReportingWebConnector.RecipientGiftStatementTotalsTable(AParameters, (Int64)Row["RecipientKey"],
FDbAdapter));
}
// get donor information for each recipient
Donors.Merge(TFinanceReportingWebConnector.RecipientGiftStatementDonorTable(AParameters, (Int64)Row["RecipientKey"], FDbAdapter));
if (FDbAdapter.IsCancelled)
{
return null;
}
}
DataView View = new DataView(Donors);
DataTable DistinctDonors = new DataTable();
if (View.Count > 0)
{
DistinctDonors = View.ToTable(true, "DonorKey");
}
DataTable DonorAddresses = new DataTable("DonorAddresses");
if ((ReportType == "Complete") || (ReportType == "Donors Only"))
{
foreach (DataRow Row in DistinctDonors.Rows)
{
// get best address for each distinct donor
DonorAddresses.Merge(TFinanceReportingWebConnector.RecipientGiftStatementDonorAddressesTable(Convert.ToInt64(Row["DonorKey"]),
FDbAdapter));
if (FDbAdapter.IsCancelled)
{
return null;
}
}
}
else
{
DonorAddresses.Merge(DistinctDonors);
}
// We only want distinct donors for this report (i.e. no more than one per recipient)
if (ReportType == "Donors Only")
{
if (View.Count > 0)
{
DistinctDonors = View.ToTable(true, "DonorKey", "DonorName", "RecipientKey");
Donors.Clear();
Donors.Merge(DistinctDonors);
}
else // I should return an empty table with just columns, to keep the client happy:
{
DistinctDonors = new DataTable();
DistinctDonors.Columns.Add("DonorKey", typeof(Int64));
DistinctDonors.Columns.Add("DonorName", typeof(String));
DistinctDonors.Columns.Add("RecipientKey", typeof(Int64));
}
}
ReturnDataSet.Tables.Add(Recipients);
ReturnDataSet.Tables.Add(RecipientTotals);
ReturnDataSet.Tables.Add(Donors);
ReturnDataSet.Tables.Add(DonorAddresses);
return (FDbAdapter.IsCancelled) ? null : ReturnDataSet;
}
public static DataTable StewardshipTable(Dictionary <String, TVariant>AParameters, TReportingDbAdapter DbAdapter)
{
DataTable resultsTable = null;
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
TLogging.Log(Catalog.GetString("Loading data.."), TLoggingType.ToStatusBar);
Int32 LedgerNumber = AParameters["param_ledger_number_i"].ToInt32();
Int32 IchNumber = AParameters["param_cmbICHNumber"].ToInt32();
Int32 period = AParameters["param_cmbReportPeriod"].ToInt32();
Int32 Year = AParameters["param_cmbYearEnding"].ToInt32();
Int32 CurrentFinancialYear = ALedgerAccess.LoadByPrimaryKey(LedgerNumber, Transaction)[0].CurrentFinancialYear;
string AccountHierarchyCode = MFinanceConstants.ACCOUNT_HIERARCHY_STANDARD;
bool BaseCurrency = AParameters["param_currency"].ToString() == "Base";
String Query = string.Empty;
if (CurrentFinancialYear == Year) // if current year
{
string IncomeAmount = string.Empty;
string ExpenseAmount = string.Empty;
string XferAmount = string.Empty;
if (BaseCurrency)
{
IncomeAmount = "a_income_amount_n";
ExpenseAmount = "a_expense_amount_n";
XferAmount = "a_direct_xfer_amount_n";
}
else
{
IncomeAmount = "a_income_amount_intl_n";
ExpenseAmount = "a_expense_amount_intl_n";
XferAmount = "a_direct_xfer_amount_intl_n";
}
String StewardshipFilter = "a_ich_stewardship.a_ledger_number_i = " + LedgerNumber;
if (IchNumber == 0)
{
StewardshipFilter += " AND a_ich_stewardship.a_period_number_i = " + period;
}
else
{
StewardshipFilter += " AND a_ich_stewardship.a_ich_number_i = " + IchNumber;
}
Query = "SELECT" +
" a_ich_stewardship.a_cost_centre_code_c AS CostCentreCode, " +
" a_cost_centre.a_cost_centre_name_c AS CostCentreName, " +
" sum(a_ich_stewardship." + IncomeAmount + ") AS Income, " +
" sum(a_ich_stewardship." + ExpenseAmount + ") AS Expense, " +
" sum(a_ich_stewardship." + XferAmount + ") AS Xfer" +
" FROM a_ich_stewardship, a_cost_centre WHERE " +
StewardshipFilter +
" AND a_cost_centre.a_ledger_number_i = a_ich_stewardship.a_ledger_number_i" +
" AND a_cost_centre.a_cost_centre_code_c = a_ich_stewardship.a_cost_centre_code_c " +
" AND a_cost_centre.a_cost_centre_type_c = '" + MFinanceConstants.FOREIGN_CC_TYPE + "'" +
" AND a_cost_centre.a_clearing_account_c = '" + MFinanceConstants.ICH_ACCT_ICH + "'" +
" GROUP BY CostCentreCode, CostCentreName " +
" ORDER BY CostCentreCode";
}
else // if past year
{
string ActualCurrency = string.Empty;
if (BaseCurrency)
{
ActualCurrency = "a_actual_base_n";
}
else
{
ActualCurrency = "a_actual_intl_n";
}
string Actual = "GLMP1." + ActualCurrency;
// if period is not 1 then we need to subract the actual for the previous period from the actual for the current period
if (period > 1)
{
Actual = "(" + Actual + " - GLMP2." + ActualCurrency + ")";
}
// obtain accounts that report to account INC
string IncomeAccountsString =
GetFormattedReportingAccounts(LedgerNumber, MFinanceConstants.INCOME_HEADING, AccountHierarchyCode);
// obtain accounts that report to account EXP
string ExpenseAccountsString =
GetFormattedReportingAccounts(LedgerNumber, MFinanceConstants.EXPENSE_HEADING, AccountHierarchyCode);
Query = "SELECT" +
" a_cost_centre.a_cost_centre_code_c AS CostCentreCode, " +
" a_cost_centre.a_cost_centre_name_c AS CostCentreName, " +
/* Revenue: Income received for the foreign ledger. */
" (SUM(CASE WHEN GLM.a_account_code_c IN (" + IncomeAccountsString + ")" +
" THEN " + Actual + " ELSE 0 END)) AS Income, " +
/* Expenses: Fees & other expenses charged to the foreign ledger. */
// Get "Direct Transfer" information. Set up for money that is not sent
// through the clearing house but directly to a field. Really an expense account.
" (SUM(CASE WHEN GLM.a_account_code_c = '" + MFinanceConstants.DIRECT_XFER_ACCT + "'" +
" THEN " + Actual + " ELSE 0 END)) AS Xfer, " +
// Get other expense information. Lookup in the gl master file the
// summary heading of total EXPENSES for the entire cost centre.
" ((SUM(CASE WHEN GLM.a_account_code_c IN (" + ExpenseAccountsString + ")" +
" THEN " + Actual + " ELSE 0 END)) - " +
// Subtract "Direct Transfer" information.
" (SUM(CASE WHEN GLM.a_account_code_c = '" + MFinanceConstants.DIRECT_XFER_ACCT + "'" +
" THEN " + Actual + " ELSE 0 END)) - " +
// Subtract "ICH Settlement" information. The account used to balance out each foreign cost centre at the period end.
// Set up as an expense account and thus must be removed from the total expenses.
" (SUM(CASE WHEN GLM.a_account_code_c = '" + MFinanceConstants.ICH_ACCT_SETTLEMENT + "'" +
" THEN " + Actual + " ELSE 0 END))) " + " AS Expense " +
" FROM a_cost_centre" +
" INNER JOIN a_general_ledger_master AS GLM" +
" ON GLM.a_cost_centre_code_c = a_cost_centre.a_cost_centre_code_c" +
" AND GLM.a_account_code_c IN (" + IncomeAccountsString + ", " + ExpenseAccountsString + ", '" +
MFinanceConstants.DIRECT_XFER_ACCT + "', '" + MFinanceConstants.ICH_ACCT_SETTLEMENT + "')" +
" AND GLM.a_year_i = " + Year +
" INNER JOIN a_general_ledger_master_period AS GLMP1" +
" ON GLMP1.a_glm_sequence_i = GLM.a_glm_sequence_i" +
" AND GLMP1.a_period_number_i = " + period;
// if needed also get the GLMP record for the previous period
if (period > 1)
{
Query += " INNER JOIN a_general_ledger_master_period AS GLMP2" +
" ON GLMP2.a_glm_sequence_i = GLM.a_glm_sequence_i" +
" AND GLMP2.a_period_number_i = " + (period - 1);
}
Query += " WHERE " +
" a_cost_centre.a_ledger_number_i = " + LedgerNumber +
" AND a_cost_centre.a_cost_centre_type_c = '" + MFinanceConstants.FOREIGN_CC_TYPE + "'" +
" AND a_cost_centre.a_clearing_account_c = '" + MFinanceConstants.ICH_ACCT_ICH + "'" +
" GROUP BY CostCentreCode, CostCentreName " +
" ORDER BY CostCentreCode";
}
TLogging.Log(Catalog.GetString(""), TLoggingType.ToStatusBar);
resultsTable = DbAdapter.RunQuery(Query, "Stewardship", Transaction);
}); // Get NewOrExisting AutoReadTransaction
return resultsTable;
} // StewardshipTable
public static DataTable GetReportDataTable(String AReportType, Dictionary <String, TVariant>AParameters)
{
FDbAdapter = new TReportingDbAdapter();
TLogging.SetStatusBarProcedure(WriteToStatusBar);
DataTable ResultTbl = null;
switch (AReportType)
{
/* GL Reports */
case "BalanceSheet":
ResultTbl = TFinanceReportingWebConnector.BalanceSheetTable(AParameters, FDbAdapter);
break;
case "FieldGifts":
ResultTbl = TFinanceReportingWebConnector.KeyMinGiftsTable(AParameters, FDbAdapter);
break;
case "HOSA":
ResultTbl = TFinanceReportingWebConnector.HosaGiftsTable(AParameters, FDbAdapter);
break;
case "Stewardship":
ResultTbl = TFinanceReportingWebConnector.StewardshipTable(AParameters, FDbAdapter);
break;
case "Fees":
ResultTbl = TFinanceReportingWebConnector.FeesTable(AParameters, FDbAdapter);
break;
case "IncomeExpense":
ResultTbl = TFinanceReportingWebConnector.IncomeExpenseTable(AParameters, FDbAdapter);
break;
case "AFO":
ResultTbl = TFinanceReportingWebConnector.AFOTable(AParameters, FDbAdapter);
break;
case "Executive Summary":
ResultTbl = TFinanceReportingWebConnector.ExecutiveSummaryTable(AParameters, FDbAdapter);
break;
case "TrialBalance":
ResultTbl = TFinanceReportingWebConnector.TrialBalanceTable(AParameters, FDbAdapter);
break;
case "SurplusDeficit":
ResultTbl = TFinanceReportingWebConnector.SurplusDeficitTable(AParameters, FDbAdapter);
break;
/* Gift Reports */
case "GiftBatchDetail":
ResultTbl = TFinanceReportingWebConnector.GiftBatchDetailTable(AParameters, FDbAdapter);
break;
case "RecipientTaxDeductPct":
ResultTbl = TFinanceReportingWebConnector.RecipientTaxDeductPctTable(AParameters, FDbAdapter);
break;
case "FieldLeaderGiftSummary":
ResultTbl = TFinanceReportingWebConnector.FieldLeaderGiftSummary(AParameters, FDbAdapter);
break;
case "TotalGiftsThroughField":
ResultTbl = TFinanceReportingWebConnector.TotalGiftsThroughField(AParameters, FDbAdapter);
break;
default:
TLogging.Log("GetDatatableThread unknown ReportType: " + AReportType);
break;
}
FDbAdapter.CloseConnection();
return (FDbAdapter.IsCancelled) ? null : ResultTbl;
}
public static DataTable RecipientGiftStatementDonorTable(Dictionary <String, TVariant>AParameters,
Int64 ARecipientKey,
TReportingDbAdapter DbAdapter)
{
TDBTransaction Transaction = null;
int LedgerNumber = AParameters["param_ledger_number_i"].ToInt32();
string ReportType = AParameters["param_report_type"].ToString();
string Currency = "";
if ((ReportType == "List") || (ReportType == "Email"))
{
Currency = "a_gift_transaction_amount_n";
}
else
{
Currency = AParameters["param_currency"].ToString().ToUpper() == "BASE" ? "a_gift_amount_n" : "a_gift_amount_intl_n";
}
// create new datatable
DataTable Results = new DataTable();
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(
IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
string Query = "SELECT" +
" gift.a_date_entered_d AS GiftDate," +
" gift.p_donor_key_n AS DonorKey," +
" CASE WHEN DonorPartner.p_partner_short_name_c NOT LIKE ''" +
" THEN DonorPartner.p_partner_short_name_c" +
" ELSE '" + Catalog.GetString("Unknown Donor") + "' END AS DonorName," +
" DonorPartner.p_partner_class_c AS DonorClass," +
" detail.p_recipient_key_n AS RecipientKey," +
" detail.a_motivation_detail_code_c AS MotivationCode," +
" detail.a_confidential_gift_flag_l AS Confidential," +
" detail." + Currency + " AS GiftAmount," +
" gift.a_receipt_number_i AS Receipt," +
" PUB_a_gift_batch.a_currency_code_c AS GiftCurrency," +
" RecipientLedgerPartner.p_partner_short_name_c AS GiftField," +
" CASE WHEN" +
" (UPPER(detail.a_comment_one_type_c) = 'RECIPIENT' OR UPPER(detail.a_comment_one_type_c) = 'BOTH')" +
" AND '" + ReportType + "' = 'Complete'" +
" THEN detail.a_gift_comment_one_c" +
" ELSE ''" +
" END AS CommentOne," +
" CASE WHEN" +
" UPPER(detail.a_comment_one_type_c) = 'RECIPIENT' OR UPPER(detail.a_comment_one_type_c) = 'BOTH'" +
" AND '" + ReportType + "' = 'Complete'" +
" THEN detail.a_gift_comment_two_c" +
" ELSE ''" +
" END AS CommentTwo," +
" CASE WHEN" +
" UPPER(detail.a_comment_one_type_c) = 'RECIPIENT' OR UPPER(detail.a_comment_one_type_c) = 'BOTH'" +
" AND '" + ReportType + "' = 'Complete'" +
" THEN detail.a_gift_comment_three_c" +
" ELSE ''" +
" END AS CommentThree" +
" FROM" +
" PUB_a_gift as gift," +
" PUB_a_gift_detail as detail," +
" PUB_a_gift_batch," +
" PUB_p_partner AS DonorPartner," +
" PUB_p_partner AS RecipientLedgerPartner" +
" WHERE" +
" detail.a_ledger_number_i = gift.a_ledger_number_i" +
" AND detail.p_recipient_key_n = " + ARecipientKey +
" AND PUB_a_gift_batch.a_batch_status_c = 'Posted'" +
" AND PUB_a_gift_batch.a_batch_number_i = gift.a_batch_number_i" +
" AND PUB_a_gift_batch.a_ledger_number_i = " + LedgerNumber +
" AND gift.a_date_entered_d BETWEEN '" + AParameters["param_from_date"].ToDate().ToString("yyyy-MM-dd") +
"' AND '" + AParameters["param_to_date"].ToDate().ToString("yyyy-MM-dd") + "'" +
" AND DonorPartner.p_partner_key_n = gift.p_donor_key_n" +
" AND RecipientLedgerPartner.p_partner_key_n = detail.a_recipient_ledger_number_n" +
" AND gift.a_ledger_number_i = " + LedgerNumber +
" AND detail.a_batch_number_i = gift.a_batch_number_i" +
" AND detail.a_gift_transaction_number_i = gift.a_gift_transaction_number_i";
if ((ReportType == "Complete") || (ReportType == "Gifts Only"))
{
Query += " ORDER BY gift.a_date_entered_d";
}
else if (ReportType == "Donors Only")
{
Query += " ORDER BY DonorPartner.p_partner_short_name_c";
}
Results = DbAdapter.RunQuery(Query, "Donors", Transaction);
});
return Results;
}
