/// <summary>
/// The constructor creates a base batch and defines the batch parameters. There is only
/// one batch to account. Use a new object to post another batch.
/// </summary>
/// <param name="ALedgerNumber">the ledger number</param>
/// <param name="ABatchDescription">a batch description text</param>
/// <param name="ADataBase"></param>
public TCommonAccountingTool(int ALedgerNumber,
string ABatchDescription, TDataBase ADataBase = null)
{
FLedgerInfo = new TLedgerInfo(ALedgerNumber);
FDataBase = ADataBase;
TCommonAccountingTool_(ABatchDescription);
}
public static bool PeriodMonthEnd(
Int32 ALedgerNumber,
bool AInfoMode,
out TVerificationResultCollection AVerificationResults)
{
try
{
TLedgerInfo ledgerInfo = new TLedgerInfo(ALedgerNumber);
Int32 PeriodClosing = ledgerInfo.CurrentPeriod;
bool res = new TMonthEnd(ledgerInfo).RunMonthEnd(AInfoMode,
out AVerificationResults);
if (!res && !AInfoMode)
{
TDBTransaction Transaction = null;
AAccountingPeriodTable PeriodTbl = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadUncommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
PeriodTbl = AAccountingPeriodAccess.LoadByPrimaryKey(ledgerInfo.LedgerNumber, PeriodClosing, Transaction);
});
if (PeriodTbl.Rows.Count > 0)
{
AVerificationResults.Add(
new TVerificationResult(
Catalog.GetString("Month End"),
String.Format(Catalog.GetString("The period {0} - {1} has been closed."),
PeriodTbl[0].PeriodStartDate.ToShortDateString(), PeriodTbl[0].PeriodEndDate.ToShortDateString()),
TResultSeverity.Resv_Status));
}
}
return res;
}
catch (Exception e)
{
TLogging.Log("TPeriodIntervallConnector.TPeriodMonthEnd() throws " + e.ToString());
AVerificationResults = new TVerificationResultCollection();
AVerificationResults.Add(
new TVerificationResult(
Catalog.GetString("Month End"),
Catalog.GetString("Uncaught Exception: ") + e.Message,
TResultSeverity.Resv_Critical));
return true;
}
}
/// <summary>
/// ...
/// </summary>
/// <param name="ALedgerInfo"></param>
public TGetAccountHierarchyDetailInfo(TLedgerInfo ALedgerInfo)
{
FledgerInfo = ALedgerInfo;
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
FAccountTable = AAccountHierarchyDetailAccess.LoadViaALedger(
FledgerInfo.LedgerNumber, Transaction);
});
}
/// <summary>
/// ...
/// </summary>
/// <param name="ALedgerInfo"></param>
public TGetAccountHierarchyDetailInfo(TLedgerInfo ALedgerInfo)
{
FledgerInfo = ALedgerInfo;
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
FAccountTable = AAccountHierarchyDetailAccess.LoadViaALedger(
FledgerInfo.LedgerNumber, Transaction);
});
}
/// <summary>
/// The constructor needs a ledgerinfo (for the ledger number)
/// </summary>
/// <param name="ledgerInfo"></param>
public THandleAccountPropertyInfo(TLedgerInfo ledgerInfo)
{
bool NewTransaction = false;
try
{
TDBTransaction transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
propertyCodeTable = AAccountPropertyAccess.LoadViaALedger(ledgerInfo.LedgerNumber, transaction);
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
}
}
}
public static bool TPeriodYearEnd(
int ALedgerNum,
bool AIsInInfoMode,
out TVerificationResultCollection AVerificationResult)
{
bool NewTransaction;
DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.Serializable, out NewTransaction);
try
{
TLedgerInfo LedgerInfo = new TLedgerInfo(ALedgerNum);
bool res = new TYearEnd(LedgerInfo).RunYearEnd(AIsInInfoMode, out AVerificationResult);
if (!res)
{
String SuccessMsg = AIsInInfoMode ? "YearEnd check: No problems found." : "Success.";
AVerificationResult.Add(new TVerificationResult("Year End", SuccessMsg, "Success", TResultSeverity.Resv_Status));
}
if (NewTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
}
return res;
}
catch (Exception e)
{
TLogging.Log("TPeriodIntervalConnector.TPeriodYearEnd() throws " + e.ToString());
AVerificationResult = new TVerificationResultCollection();
AVerificationResult.Add(
new TVerificationResult(
Catalog.GetString("Year End"),
Catalog.GetString("Uncaught Exception: ") + e.Message,
TResultSeverity.Resv_Critical));
DBAccess.GDBAccessObj.RollbackTransaction();
return false;
}
}
/// <summary>
/// ...
/// </summary>
/// <param name="ALedgerInfo"></param>
public TGetAccountHierarchyDetailInfo(TLedgerInfo ALedgerInfo)
{
FledgerInfo = ALedgerInfo;
bool NewTransaction = false;
TDBTransaction transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTransaction);
try
{
FAccountTable = AAccountHierarchyDetailAccess.LoadViaALedger(
FledgerInfo.LedgerNumber, transaction);
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
}
}
/// <summary>
/// </summary>
/// <param name="ALedgerInfo"></param>
public TMonthEnd(TLedgerInfo ALedgerInfo)
{
FledgerInfo = ALedgerInfo;
}
/// <summary>
/// The Constructor defines a first value of a specific accounting code too.
/// </summary>
/// <param name="ALedgerInfo"></param>
/// <param name="AAccountCode"></param>
public TAccountInfo(TLedgerInfo ALedgerInfo, string AAccountCode)
{
FLedgerInfo = ALedgerInfo;
LoadData();
AccountCode = AAccountCode;
}
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
/// <summary>
/// A TLedgerInfo-Oject is used internally.
/// If you already have one, use this constructor to reduce the number of database requests.
/// </summary>
/// <param name="ALedgerInfo">The ledger-info object</param>
/// <param name="ABatchDescription">the description text ...</param>
public TCommonAccountingTool(TLedgerInfo ALedgerInfo, string ABatchDescription)
{
FLedgerInfo = ALedgerInfo;
TCommonAccountingTool_(ABatchDescription);
}
/// <summary></summary>
/// <param name="ALedgerInfo"></param>
public TArchive(TLedgerInfo ALedgerInfo)
{
FledgerInfo = ALedgerInfo;
}
public void Test_07_ProcessStatus()
{
TLedgerInfo ledgerInfo = new TLedgerInfo(LedgerNumber);
ledgerInfo.YearEndProcessStatus = (int)TYearEndProcessStatus.ACCOUNT_CLOSED_OUT;
Assert.AreEqual((int)TYearEndProcessStatus.ACCOUNT_CLOSED_OUT, ledgerInfo.YearEndProcessStatus,
"OK");
ledgerInfo.YearEndProcessStatus = (int)TYearEndProcessStatus.GIFT_CLOSED_OUT;
Assert.AreEqual((int)TYearEndProcessStatus.GIFT_CLOSED_OUT, ledgerInfo.YearEndProcessStatus,
"OK");
}
/// <summary>
/// Run the revaluation and set the flag for the ledger
/// </summary>
public Boolean RunRevaluation()
{
try
{
TLedgerInfo ledger = new TLedgerInfo(F_LedgerNum);
F_BaseCurrency = ledger.BaseCurrency;
F_BaseCurrencyDigits = new TCurrencyInfo(F_BaseCurrency).digits;
F_RevaluationAccCode = ledger.RevaluationAccount;
F_FinancialYear = ledger.CurrentFinancialYear;
F_AccountingPeriod = ledger.CurrentPeriod;
if (!RunRevaluationIntern())
{
return false;
}
if (F_resultSeverity != TResultSeverity.Resv_Critical)
{
new TLedgerInitFlagHandler(F_LedgerNum,
TLedgerInitFlagEnum.Revaluation).Flag = true; // Set the REVALUATION flag on the Ledger. THIS FLAG IS NOW IGNORED.
}
}
catch (EVerificationException terminate)
{
FVerificationCollection = terminate.ResultCollection();
}
return F_resultSeverity == TResultSeverity.Resv_Critical;
}
/// <summary>
/// This mininmal constructor defines the result collection for the error messages and
/// Ledger Info to select the ledger ...
/// </summary>
/// <param name="ALedgerInfo"></param>
public TAccountInfo(TLedgerInfo ALedgerInfo)
{
FLedgerInfo = ALedgerInfo;
LoadData();
}
/// <summary>
/// The Constructor defines a first value of a specific accounting code too.
/// </summary>
/// <param name="ALedgerInfo"></param>
/// <param name="AAccountCode"></param>
public TAccountInfo(TLedgerInfo ALedgerInfo, string AAccountCode)
{
FLedgerInfo = ALedgerInfo;
LoadData();
AccountCode = AAccountCode;
}
/// <summary>
/// get the default bank account for this ledger
/// </summary>
/// <param name="ALedgerNumber"></param>
public static string GetDefaultBankAccount(int ALedgerNumber)
{
string BankAccountCode = TSystemDefaultsCache.GSystemDefaultsCache.GetStringDefault(
SharedConstants.SYSDEFAULT_GIFTBANKACCOUNT + ALedgerNumber.ToString());
if (BankAccountCode.Length == 0)
{
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
// use the first bank account
AAccountPropertyTable accountProperties = AAccountPropertyAccess.LoadViaALedger(ALedgerNumber, Transaction);
accountProperties.DefaultView.RowFilter = AAccountPropertyTable.GetPropertyCodeDBName() + " = '" +
MFinanceConstants.ACCOUNT_PROPERTY_BANK_ACCOUNT + "' and " +
AAccountPropertyTable.GetPropertyValueDBName() + " = 'true'";
if (accountProperties.DefaultView.Count > 0)
{
BankAccountCode = ((AAccountPropertyRow)accountProperties.DefaultView[0].Row).AccountCode;
}
else
{
string SQLQuery = "SELECT a_gift_batch.a_bank_account_code_c " +
"FROM a_gift_batch " +
"WHERE a_gift_batch.a_ledger_number_i = " + ALedgerNumber +
" AND a_gift_batch.a_batch_number_i = (" +
"SELECT max(a_gift_batch.a_batch_number_i) " +
"FROM a_gift_batch " +
"WHERE a_gift_batch.a_ledger_number_i = " + ALedgerNumber +
" AND a_gift_batch.a_gift_type_c = '" + MFinanceConstants.GIFT_TYPE_GIFT + "')";
DataTable LatestAccountCode = DBAccess.GDBAccessObj.SelectDT(SQLQuery, "LatestAccountCode", Transaction);
// use the Bank Account of the previous Gift Batch
if ((LatestAccountCode != null) && (LatestAccountCode.Rows.Count > 0))
{
BankAccountCode = LatestAccountCode.Rows[0]["a_bank_account_code_c"].ToString();
}
// if this is the first ever gift batch (this should happen only once!) then use the first appropriate Account Code in the database
else
{
AAccountTable AccountTable = AAccountAccess.LoadViaALedger(ALedgerNumber, Transaction);
DataView dv = AccountTable.DefaultView;
dv.Sort = "a_account_code_c asc";
dv.RowFilter = "a_account_active_flag_l = true AND a_posting_status_l = true";
DataTable sortedDT = dv.ToTable();
TLedgerInfo ledgerInfo = new TLedgerInfo(ALedgerNumber);
TGetAccountHierarchyDetailInfo accountHierarchyTools = new TGetAccountHierarchyDetailInfo(ledgerInfo);
List <string>children = accountHierarchyTools.GetChildren(MFinanceConstants.CASH_ACCT);
foreach (DataRow account in sortedDT.Rows)
{
// check if this account reports to the CASH account
if (children.Contains(account["a_account_code_c"].ToString()))
{
BankAccountCode = account["a_account_code_c"].ToString();
break;
}
}
}
}
});
}
return BankAccountCode;
}
/// <summary>
/// The constructor needs a ledgerinfo (for the ledger number)
/// </summary>
/// <param name="ledgerInfo"></param>
public THandleAccountPropertyInfo(TLedgerInfo ledgerInfo)
{
bool NewTransaction = false;
try
{
TDBTransaction transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
propertyCodeTable = AAccountPropertyAccess.LoadViaALedger(ledgerInfo.LedgerNumber, transaction);
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
}
}
}
/// <summary>
/// </summary>
public TResetForwardPeriodBatches(TLedgerInfo ALedgerInfo, Int32 AOldYearNum)
{
FLedgerInfo = ALedgerInfo;
FOldYearNum = AOldYearNum;
}
/// <summary>
/// get the default bank account for this ledger
/// </summary>
/// <param name="ALedgerNumber"></param>
public static string GetDefaultBankAccount(int ALedgerNumber)
{
#region Validate Arguments
if (ALedgerNumber <= 0)
{
throw new EFinanceSystemInvalidLedgerNumberException(String.Format(Catalog.GetString(
"Function:{0} - The Ledger number must be greater than 0!"),
Utilities.GetMethodName(true)), ALedgerNumber);
}
#endregion Validate Arguments
string BankAccountCode = TSystemDefaultsCache.GSystemDefaultsCache.GetStringDefault(
SharedConstants.SYSDEFAULT_GIFTBANKACCOUNT + ALedgerNumber.ToString());
if (BankAccountCode.Length == 0)
{
TDBTransaction readTransaction = null;
try
{
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum, ref readTransaction,
delegate
{
// use the first bank account
AAccountPropertyTable accountProperties = AAccountPropertyAccess.LoadViaALedger(ALedgerNumber, readTransaction);
accountProperties.DefaultView.RowFilter = AAccountPropertyTable.GetPropertyCodeDBName() + " = '" +
MFinanceConstants.ACCOUNT_PROPERTY_BANK_ACCOUNT + "' and " +
AAccountPropertyTable.GetPropertyValueDBName() + " = 'true'";
if (accountProperties.DefaultView.Count > 0)
{
BankAccountCode = ((AAccountPropertyRow)accountProperties.DefaultView[0].Row).AccountCode;
}
else
{
string SQLQuery = "SELECT a_gift_batch.a_bank_account_code_c " +
"FROM a_gift_batch " +
"WHERE a_gift_batch.a_ledger_number_i = " + ALedgerNumber +
" AND a_gift_batch.a_batch_number_i = (" +
"SELECT max(a_gift_batch.a_batch_number_i) " +
"FROM a_gift_batch " +
"WHERE a_gift_batch.a_ledger_number_i = " + ALedgerNumber +
" AND a_gift_batch.a_gift_type_c = '" + MFinanceConstants.GIFT_TYPE_GIFT + "')";
DataTable LatestAccountCode = DBAccess.GDBAccessObj.SelectDT(SQLQuery, "LatestAccountCode", readTransaction);
// use the Bank Account of the previous Gift Batch
if ((LatestAccountCode != null) && (LatestAccountCode.Rows.Count > 0))
{
BankAccountCode = LatestAccountCode.Rows[0][AGiftBatchTable.GetBankAccountCodeDBName()].ToString(); //"a_bank_account_code_c"
}
// if this is the first ever gift batch (this should happen only once!) then use the first appropriate Account Code in the database
else
{
AAccountTable AccountTable = AAccountAccess.LoadViaALedger(ALedgerNumber, readTransaction);
#region Validate Data
if ((AccountTable == null) || (AccountTable.Count == 0))
{
throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString(
"Function:{0} - Account data for Ledger number {1} does not exist or could not be accessed!"),
Utilities.GetMethodName(true),
ALedgerNumber));
}
#endregion Validate Data
DataView dv = AccountTable.DefaultView;
dv.Sort = AAccountTable.GetAccountCodeDBName() + " ASC"; //a_account_code_c
dv.RowFilter = String.Format("{0} = true AND {1} = true",
AAccountTable.GetAccountActiveFlagDBName(),
AAccountTable.GetPostingStatusDBName()); // "a_account_active_flag_l = true AND a_posting_status_l = true";
DataTable sortedDT = dv.ToTable();
TLedgerInfo ledgerInfo = new TLedgerInfo(ALedgerNumber);
TGetAccountHierarchyDetailInfo accountHierarchyTools = new TGetAccountHierarchyDetailInfo(ledgerInfo);
List <string> children = accountHierarchyTools.GetChildren(MFinanceConstants.CASH_ACCT);
foreach (DataRow account in sortedDT.Rows)
{
// check if this account reports to the CASH account
if (children.Contains(account["a_account_code_c"].ToString()))
{
BankAccountCode = account["a_account_code_c"].ToString();
break;
}
}
}
}
});
}
catch (Exception ex)
{
TLogging.Log(String.Format("Method:{0} - Unexpected error!{1}{1}{2}",
Utilities.GetMethodSignature(),
Environment.NewLine,
ex.Message));
throw ex;
}
}
return(BankAccountCode);
}
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 RunMonthEndChecks(TLedgerInfo ALedgerInfo)
{
FledgerInfo = ALedgerInfo;
}
/// <summary>
/// </summary>
public TGlmNewYearInit(TLedgerInfo ALedgerInfo, int AYear, TYearEnd AYearEndOperator)
{
FOldYearNum = AYear;
FNewYearNum = FOldYearNum + 1;
FLedgerInfo = ALedgerInfo;
FYearEndOperator = AYearEndOperator;
FLedgerAccountingPeriods = FLedgerInfo.NumberOfAccountingPeriods; // Don't call these properties in a loop,
FLedgerFwdPeriods = FLedgerInfo.NumberFwdPostingPeriods; // as they reload the row from the DB!
bool NewTransaction;
TDBTransaction Transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
try
{
DataTable GlmTble = LoadTable(FLedgerInfo.LedgerNumber, FOldYearNum, Transaction);
FGlmPostingFrom.Merge(GlmTble);
GlmTble = LoadTable(FLedgerInfo.LedgerNumber, FNewYearNum, Transaction);
GlmTDS.AGeneralLedgerMaster.Merge(GlmTble);
GlmTDS.AGeneralLedgerMaster.DefaultView.Sort =
AGeneralLedgerMasterTable.GetAccountCodeDBName() + "," +
AGeneralLedgerMasterTable.GetCostCentreCodeDBName();
DataTable GlmpTbl = GetGlmpRows(FOldYearNum, Transaction, FLedgerAccountingPeriods);
FGlmpFrom.Merge(GlmpTbl);
FGlmpFrom.DefaultView.Sort = "a_glm_sequence_i,a_period_number_i";
GlmpTbl = GetGlmpRows(FNewYearNum, Transaction, 0);
GlmTDS.AGeneralLedgerMasterPeriod.Merge(GlmpTbl);
GlmTDS.AGeneralLedgerMasterPeriod.DefaultView.Sort = "a_glm_sequence_i,a_period_number_i";
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
}
}
/// <summary>
/// get the default bank account for this ledger
/// </summary>
/// <param name="ALedgerNumber"></param>
public static string GetDefaultBankAccount(int ALedgerNumber)
{
string BankAccountCode = TSystemDefaultsCache.GSystemDefaultsCache.GetStringDefault(
SharedConstants.SYSDEFAULT_GIFTBANKACCOUNT + ALedgerNumber.ToString());
if (BankAccountCode.Length == 0)
{
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
// use the first bank account
AAccountPropertyTable accountProperties = AAccountPropertyAccess.LoadViaALedger(ALedgerNumber, Transaction);
accountProperties.DefaultView.RowFilter = AAccountPropertyTable.GetPropertyCodeDBName() + " = '" +
MFinanceConstants.ACCOUNT_PROPERTY_BANK_ACCOUNT + "' and " +
AAccountPropertyTable.GetPropertyValueDBName() + " = 'true'";
if (accountProperties.DefaultView.Count > 0)
{
BankAccountCode = ((AAccountPropertyRow)accountProperties.DefaultView[0].Row).AccountCode;
}
else
{
string SQLQuery = "SELECT a_gift_batch.a_bank_account_code_c " +
"FROM a_gift_batch " +
"WHERE a_gift_batch.a_ledger_number_i = " + ALedgerNumber +
" AND a_gift_batch.a_batch_number_i = (" +
"SELECT max(a_gift_batch.a_batch_number_i) " +
"FROM a_gift_batch " +
"WHERE a_gift_batch.a_ledger_number_i = " + ALedgerNumber +
" AND a_gift_batch.a_gift_type_c = '" + MFinanceConstants.GIFT_TYPE_GIFT + "')";
DataTable LatestAccountCode = DBAccess.GDBAccessObj.SelectDT(SQLQuery, "LatestAccountCode", Transaction);
// use the Bank Account of the previous Gift Batch
if ((LatestAccountCode != null) && (LatestAccountCode.Rows.Count > 0))
{
BankAccountCode = LatestAccountCode.Rows[0]["a_bank_account_code_c"].ToString();
}
// if this is the first ever gift batch (this should happen only once!) then use the first appropriate Account Code in the database
else
{
AAccountTable AccountTable = AAccountAccess.LoadViaALedger(ALedgerNumber, Transaction);
DataView dv = AccountTable.DefaultView;
dv.Sort = "a_account_code_c asc";
dv.RowFilter = "a_account_active_flag_l = true AND a_posting_status_l = true";
DataTable sortedDT = dv.ToTable();
TLedgerInfo ledgerInfo = new TLedgerInfo(ALedgerNumber);
TGetAccountHierarchyDetailInfo accountHierarchyTools = new TGetAccountHierarchyDetailInfo(ledgerInfo);
List <string> children = accountHierarchyTools.GetChildren(MFinanceConstants.CASH_ACCT);
foreach (DataRow account in sortedDT.Rows)
{
// check if this account reports to the CASH account
if (children.Contains(account["a_account_code_c"].ToString()))
{
BankAccountCode = account["a_account_code_c"].ToString();
break;
}
}
}
}
});
}
return(BankAccountCode);
}
public void Test_2YearEnds()
{
intLedgerNumber = CommonNUnitFunctions.CreateNewLedger();
CommonNUnitFunctions.LoadTestDataBase("csharp\\ICT\\Testing\\lib\\MFinance\\GL\\test-sql\\gl-test-year-end.sql", intLedgerNumber);
TLedgerInfo LedgerInfo = new TLedgerInfo(intLedgerNumber);
for (int countYear = 0; countYear < 2; countYear++)
{
TLogging.Log("preparing year number " + countYear.ToString());
// accounting one gift
string strAccountGift = "0200";
string strAccountBank = "6200";
TCommonAccountingTool commonAccountingTool =
new TCommonAccountingTool(intLedgerNumber, "NUNIT");
commonAccountingTool.AddBaseCurrencyJournal();
commonAccountingTool.JournalDescription = "Test Data accounts";
commonAccountingTool.AddBaseCurrencyTransaction(
strAccountBank, "4301", "Gift Example", "Debit", MFinanceConstants.IS_DEBIT, 100);
commonAccountingTool.AddBaseCurrencyTransaction(
strAccountGift, "4301", "Gift Example", "Credit", MFinanceConstants.IS_CREDIT, 100);
Boolean PostedOk = commonAccountingTool.CloseSaveAndPost(); // returns true if posting seemed to work
Assert.AreEqual(true, PostedOk, "Test batch can't be posted");
bool blnLoop = true;
while (blnLoop)
{
// System.Windows.Forms.MessageBox.Show(LedgerInfo.CurrentPeriod.ToString(), "MonthEnd Period");
if (LedgerInfo.ProvisionalYearEndFlag)
{
blnLoop = false;
}
else
{
TVerificationResultCollection VerificationResult;
TPeriodIntervalConnector.PeriodMonthEnd(intLedgerNumber, false, out VerificationResult);
CommonNUnitFunctions.EnsureNullOrOnlyNonCriticalVerificationResults(VerificationResult,
"MonthEnd gave critical error at Period" + LedgerInfo.CurrentPeriod + ":\r\n");
}
}
TLogging.Log("Closing year number " + countYear.ToString());
TReallocation reallocation = new TReallocation(LedgerInfo);
TVerificationResultCollection verificationResult = new TVerificationResultCollection();
reallocation.VerificationResultCollection = verificationResult;
reallocation.IsInInfoMode = false;
// Assert.AreEqual(1, reallocation.GetJobSize(), "Check 1 reallocation job is required"); // No job size is published by Reallocation
reallocation.RunOperation();
TYearEnd YearEndOperator = new TYearEnd(LedgerInfo);
TGlmNewYearInit glmNewYearInit = new TGlmNewYearInit(LedgerInfo, countYear, YearEndOperator);
glmNewYearInit.VerificationResultCollection = verificationResult;
glmNewYearInit.IsInInfoMode = false;
// Assert.Greater(glmNewYearInit.GetJobSize(), 0, "Check that NewYearInit has work to do"); // in this version, GetJobSize returns 0
glmNewYearInit.RunOperation();
}
Assert.AreEqual(2, LedgerInfo.CurrentFinancialYear, "After YearEnd, Ledger is in year 2");
TAccountPeriodInfo periodInfo = new TAccountPeriodInfo(intLedgerNumber, 1);
Assert.AreEqual(new DateTime(DateTime.Now.Year + 2,
1,
1), periodInfo.PeriodStartDate, "new Calendar should start with January 1st of next year");
}
public void Test_09_TAccountPropertyHandler()
{
TLedgerInfo tHandleLedgerInfo = new TLedgerInfo(LedgerNumber);
TAccountInfo tHandleAccountInfo = new TAccountInfo(tHandleLedgerInfo);
tHandleAccountInfo.SetSpecialAccountCode(TAccountPropertyEnum.ICH_ACCT);
Assert.IsTrue(tHandleAccountInfo.IsValid, "ICH_ACCT shall exist");
Assert.AreEqual("8500", tHandleAccountInfo.AccountCode);
}
public void Test_SwitchToNextMonth()
{
FLedgerNumber = CommonNUnitFunctions.CreateNewLedger();
TLedgerInfo ledgerInfo = new TLedgerInfo(FLedgerNumber);
int counter = 0;
do
{
Int32 CurrentPeriod = ledgerInfo.CurrentPeriod;
++counter;
Assert.Greater(20, counter, "Too many loops");
// Set revaluation flag ...
new TLedgerInitFlagHandler(FLedgerNumber,
TLedgerInitFlagEnum.Revaluation).Flag = true;
// Run MonthEnd ...
TVerificationResultCollection verificationResult;
bool blnHasErrors = TPeriodIntervalConnector.PeriodMonthEnd(
FLedgerNumber, false, out verificationResult);
if (!ledgerInfo.ProvisionalYearEndFlag)
{
Assert.AreEqual(CurrentPeriod + 1,
ledgerInfo.CurrentPeriod, "Period increment");
}
Assert.IsFalse(blnHasErrors, "Month end without any error");
System.Diagnostics.Debug.WriteLine("Counter: " + counter.ToString());
} while (!ledgerInfo.ProvisionalYearEndFlag);
}
/// <summary>
/// The constructor creates a base batch and defines the batch parameters. There is only
/// one batch to account. Use a new object to post another batch.
/// </summary>
/// <param name="ALedgerNumber">the ledger number</param>
/// <param name="ABatchDescription">a batch description text</param>
public TCommonAccountingTool(int ALedgerNumber,
string ABatchDescription)
{
FLedgerInfo = new TLedgerInfo(ALedgerNumber);
TCommonAccountingTool_(ABatchDescription);
}
/// <summary>
/// </summary>
/// <param name="ALedgerInfo"></param>
public TYearEnd(TLedgerInfo ALedgerInfo)
{
FledgerInfo = ALedgerInfo;
}
/// <summary>
/// Populate ledger with gifts and invoices, post batches, close periods and years, according to FNumberOfClosedPeriods
/// </summary>
public static void PopulateData(string datadirectory, bool smallNumber = false)
{
int periodOverall = 0;
int yearCounter = 0;
int period = 1;
int YearAD = DateTime.Today.Year - (FNumberOfClosedPeriods / 12);
SampleDataGiftBatches.FLedgerNumber = FLedgerNumber;
SampleDataAccountsPayable.FLedgerNumber = FLedgerNumber;
SampleDataGiftBatches.LoadBatches(Path.Combine(datadirectory, "donations.csv"), smallNumber);
SampleDataAccountsPayable.GenerateInvoices(Path.Combine(datadirectory, "invoices.csv"), YearAD, smallNumber);
while (periodOverall <= FNumberOfClosedPeriods)
{
TLogging.LogAtLevel(1, "working on year " + yearCounter.ToString() + " / period " + period.ToString());
SampleDataGiftBatches.CreateGiftBatches(period);
if (!SampleDataGiftBatches.PostBatches(yearCounter, period, (periodOverall == FNumberOfClosedPeriods) ? 1 : 0))
{
throw new Exception("could not post gift batches");
}
if (!SampleDataAccountsPayable.PostAndPayInvoices(yearCounter, period, (periodOverall == FNumberOfClosedPeriods) ? 1 : 0))
{
throw new Exception("could not post invoices");
}
TLedgerInfo LedgerInfo = new TLedgerInfo(FLedgerNumber);
if (periodOverall < FNumberOfClosedPeriods)
{
TAccountPeriodInfo AccountingPeriodInfo =
new TAccountPeriodInfo(FLedgerNumber, period);
TLogging.Log("closing period at " + AccountingPeriodInfo.PeriodEndDate.ToShortDateString());
// run month end
TMonthEnd MonthEndOperator = new TMonthEnd(LedgerInfo);
MonthEndOperator.SetNextPeriod();
if (period == 12)
{
TYearEnd YearEndOperator = new TYearEnd(LedgerInfo);
// run year end
TVerificationResultCollection verificationResult = new TVerificationResultCollection();
TReallocation reallocation = new TReallocation(LedgerInfo);
reallocation.VerificationResultCollection = verificationResult;
reallocation.IsInInfoMode = false;
reallocation.RunOperation();
TGlmNewYearInit glmNewYearInit = new TGlmNewYearInit(LedgerInfo, yearCounter, YearEndOperator);
glmNewYearInit.VerificationResultCollection = verificationResult;
glmNewYearInit.IsInInfoMode = false;
glmNewYearInit.RunOperation();
YearAD++;
yearCounter++;
SampleDataAccountsPayable.GenerateInvoices(Path.Combine(datadirectory, "invoices.csv"), YearAD, smallNumber);
period = 0;
}
}
period++;
periodOverall++;
}
}
/// <summary>
/// A TLedgerInfo-Oject is used internally.
/// If you already have one, use this constructor to reduce the number of database requests.
/// </summary>
/// <param name="ALedgerInfo">The ledger-info object</param>
/// <param name="ABatchDescription">the description text ...</param>
public TCommonAccountingTool(TLedgerInfo ALedgerInfo, string ABatchDescription)
{
FLedgerInfo = ALedgerInfo;
TCommonAccountingTool_(ABatchDescription);
}
/// <summary>
/// This mininmal constructor defines the result collection for the error messages and
/// Ledger Info to select the ledger ...
/// </summary>
/// <param name="ALedgerInfo"></param>
public TAccountInfo(TLedgerInfo ALedgerInfo)
{
FLedgerInfo = ALedgerInfo;
LoadData();
}
/// <summary>
///
/// </summary>
public TReallocation(TLedgerInfo ALedgerInfo)
{
FledgerInfo = ALedgerInfo;
}
/// <summary>
/// A TLedgerInfo-Oject is used internally.
/// If you already have one, use this constructor to reduce the number of database requests.
/// </summary>
/// <param name="ALedgerInfo">The ledger-info object</param>
/// <param name="ABatchDescription">the description text ...</param>
/// <param name="ADataBase"></param>
public TCommonAccountingTool(TLedgerInfo ALedgerInfo, string ABatchDescription, TDataBase ADataBase = null)
{
FLedgerInfo = ALedgerInfo;
FDataBase = ADataBase;
TCommonAccountingTool_(ABatchDescription);
}
/// <summary>
/// get the default bank account for this ledger
/// </summary>
/// <param name="ALedgerNumber"></param>
public static string GetDefaultBankAccount(int ALedgerNumber)
{
#region Validate Arguments
if (ALedgerNumber <= 0)
{
throw new EFinanceSystemInvalidLedgerNumberException(String.Format(Catalog.GetString(
"Function:{0} - The Ledger number must be greater than 0!"),
Utilities.GetMethodName(true)), ALedgerNumber);
}
#endregion Validate Arguments
string BankAccountCode = TSystemDefaultsCache.GSystemDefaultsCache.GetStringDefault(
SharedConstants.SYSDEFAULT_GIFTBANKACCOUNT + ALedgerNumber.ToString());
if (BankAccountCode.Length == 0)
{
TDBTransaction readTransaction = null;
try
{
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum, ref readTransaction,
delegate
{
// use the first bank account
AAccountPropertyTable accountProperties = AAccountPropertyAccess.LoadViaALedger(ALedgerNumber, readTransaction);
accountProperties.DefaultView.RowFilter = AAccountPropertyTable.GetPropertyCodeDBName() + " = '" +
MFinanceConstants.ACCOUNT_PROPERTY_BANK_ACCOUNT + "' and " +
AAccountPropertyTable.GetPropertyValueDBName() + " = 'true'";
if (accountProperties.DefaultView.Count > 0)
{
BankAccountCode = ((AAccountPropertyRow)accountProperties.DefaultView[0].Row).AccountCode;
}
else
{
string SQLQuery = "SELECT a_gift_batch.a_bank_account_code_c " +
"FROM a_gift_batch " +
"WHERE a_gift_batch.a_ledger_number_i = " + ALedgerNumber +
" AND a_gift_batch.a_batch_number_i = (" +
"SELECT max(a_gift_batch.a_batch_number_i) " +
"FROM a_gift_batch " +
"WHERE a_gift_batch.a_ledger_number_i = " + ALedgerNumber +
" AND a_gift_batch.a_gift_type_c = '" + MFinanceConstants.GIFT_TYPE_GIFT + "')";
DataTable LatestAccountCode = DBAccess.GDBAccessObj.SelectDT(SQLQuery, "LatestAccountCode", readTransaction);
// use the Bank Account of the previous Gift Batch
if ((LatestAccountCode != null) && (LatestAccountCode.Rows.Count > 0))
{
BankAccountCode = LatestAccountCode.Rows[0][AGiftBatchTable.GetBankAccountCodeDBName()].ToString(); //"a_bank_account_code_c"
}
// if this is the first ever gift batch (this should happen only once!) then use the first appropriate Account Code in the database
else
{
AAccountTable AccountTable = AAccountAccess.LoadViaALedger(ALedgerNumber, readTransaction);
#region Validate Data
if ((AccountTable == null) || (AccountTable.Count == 0))
{
throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString(
"Function:{0} - Account data for Ledger number {1} does not exist or could not be accessed!"),
Utilities.GetMethodName(true),
ALedgerNumber));
}
#endregion Validate Data
DataView dv = AccountTable.DefaultView;
dv.Sort = AAccountTable.GetAccountCodeDBName() + " ASC"; //a_account_code_c
dv.RowFilter = String.Format("{0} = true AND {1} = true",
AAccountTable.GetAccountActiveFlagDBName(),
AAccountTable.GetPostingStatusDBName()); // "a_account_active_flag_l = true AND a_posting_status_l = true";
DataTable sortedDT = dv.ToTable();
TLedgerInfo ledgerInfo = new TLedgerInfo(ALedgerNumber);
TGetAccountHierarchyDetailInfo accountHierarchyTools = new TGetAccountHierarchyDetailInfo(ledgerInfo);
List <string>children = accountHierarchyTools.GetChildren(MFinanceConstants.CASH_ACCT);
foreach (DataRow account in sortedDT.Rows)
{
// check if this account reports to the CASH account
if (children.Contains(account["a_account_code_c"].ToString()))
{
BankAccountCode = account["a_account_code_c"].ToString();
break;
}
}
}
}
});
}
catch (Exception ex)
{
TLogging.Log(String.Format("Method:{0} - Unexpected error!{1}{1}{2}",
Utilities.GetMethodSignature(),
Environment.NewLine,
ex.Message));
throw ex;
}
}
return BankAccountCode;
}
public void Test_YearEnd()
{
intLedgerNumber = CommonNUnitFunctions.CreateNewLedger();
TLedgerInfo LedgerInfo = new TLedgerInfo(intLedgerNumber);
Assert.AreEqual(0, LedgerInfo.CurrentFinancialYear, "Before YearEnd, we should be in year 0");
TAccountPeriodInfo periodInfo = new TAccountPeriodInfo(intLedgerNumber, 1);
Assert.AreEqual(new DateTime(DateTime.Now.Year,
1,
1), periodInfo.PeriodStartDate, "Calendar from base database should start with January 1st of this year");
CommonNUnitFunctions.LoadTestDataBase("csharp\\ICT\\Testing\\lib\\MFinance\\GL\\test-sql\\gl-test-year-end.sql", intLedgerNumber);
TCommonAccountingTool commonAccountingTool =
new TCommonAccountingTool(intLedgerNumber, "NUNIT");
commonAccountingTool.AddBaseCurrencyJournal();
commonAccountingTool.JournalDescription = "Test Data accounts";
string strAccountGift = "0200";
string strAccountBank = "6200";
string strAccountExpense = "4100";
// Accounting of some gifts ...
commonAccountingTool.AddBaseCurrencyTransaction(
strAccountBank, "4301", "Gift Example", "Debit", MFinanceConstants.IS_DEBIT, 100);
commonAccountingTool.AddBaseCurrencyTransaction(
strAccountBank, "4302", "Gift Example", "Debit", MFinanceConstants.IS_DEBIT, 200);
commonAccountingTool.AddBaseCurrencyTransaction(
strAccountBank, "4303", "Gift Example", "Debit", MFinanceConstants.IS_DEBIT, 300);
commonAccountingTool.AddBaseCurrencyTransaction(
strAccountGift, "4301", "Gift Example", "Credit", MFinanceConstants.IS_CREDIT, 100);
commonAccountingTool.AddBaseCurrencyTransaction(
strAccountGift, "4302", "Gift Example", "Credit", MFinanceConstants.IS_CREDIT, 200);
commonAccountingTool.AddBaseCurrencyTransaction(
strAccountGift, "4303", "Gift Example", "Credit", MFinanceConstants.IS_CREDIT, 300);
// Accounting of some expenses ...
commonAccountingTool.AddBaseCurrencyTransaction(
strAccountExpense, "4301", "Expense Example", "Debit", MFinanceConstants.IS_DEBIT, 150);
commonAccountingTool.AddBaseCurrencyTransaction(
strAccountExpense, "4302", "Expense Example", "Debit", MFinanceConstants.IS_DEBIT, 150);
commonAccountingTool.AddBaseCurrencyTransaction(
strAccountExpense, "4303", "Expense Example", "Debit", MFinanceConstants.IS_DEBIT, 200);
commonAccountingTool.AddBaseCurrencyTransaction(
strAccountBank, "4301", "Expense Example", "Credit", MFinanceConstants.IS_CREDIT, 150);
commonAccountingTool.AddBaseCurrencyTransaction(
strAccountBank, "4302", "Expense Example", "Credit", MFinanceConstants.IS_CREDIT, 150);
commonAccountingTool.AddBaseCurrencyTransaction(
strAccountBank, "4303", "Expense Example", "Credit", MFinanceConstants.IS_CREDIT, 200);
commonAccountingTool.CloseSaveAndPost(); // returns true if posting seemed to work
TVerificationResultCollection verificationResult = new TVerificationResultCollection();
bool blnLoop = true;
while (blnLoop)
{
if (LedgerInfo.ProvisionalYearEndFlag)
{
blnLoop = false;
}
else
{
TVerificationResultCollection VerificationResult;
TPeriodIntervalConnector.PeriodMonthEnd(intLedgerNumber, false, out VerificationResult);
CommonNUnitFunctions.EnsureNullOrOnlyNonCriticalVerificationResults(VerificationResult,
"Running MonthEnd gave critical error");
}
}
// check before year end that income and expense accounts are not 0
int intYear = 0;
CheckGLMEntry(intLedgerNumber, intYear, strAccountBank,
-50, 0, 50, 0, 100, 0);
CheckGLMEntry(intLedgerNumber, intYear, strAccountExpense,
150, 0, 150, 0, 200, 0);
CheckGLMEntry(intLedgerNumber, intYear, strAccountGift,
100, 0, 200, 0, 300, 0);
// test that we cannot post to period 12 anymore, all periods are closed?
LedgerInfo = new TLedgerInfo(intLedgerNumber);
Assert.AreEqual(true, LedgerInfo.ProvisionalYearEndFlag, "Provisional YearEnd flag should be set");
Assert.AreEqual(TYearEndProcessStatus.RESET_STATUS,
(TYearEndProcessStatus)LedgerInfo.YearEndProcessStatus,
"YearEnd process status should be still on RESET");
//
// Reallocation is never called explicitly like this - it's not really appropriate
// because I'm about to call it again as part of YearEnd, below.
// But a tweak in the reallocation code means that it should now cope with being called twice.
TReallocation reallocation = new TReallocation(LedgerInfo);
reallocation.VerificationResultCollection = verificationResult;
reallocation.IsInInfoMode = false;
reallocation.RunOperation();
// check amounts after reallocation
CheckGLMEntry(intLedgerNumber, intYear, strAccountBank,
-50, 0, 50, 0, 100, 0);
CheckGLMEntry(intLedgerNumber, intYear, strAccountExpense,
0, -150, 0, -150, 0, -200);
CheckGLMEntry(intLedgerNumber, intYear, strAccountGift,
0, -100, 0, -200, 0, -300);
// first run in info mode
TPeriodIntervalConnector.PeriodYearEnd(intLedgerNumber, true, out verificationResult);
CommonNUnitFunctions.EnsureNullOrOnlyNonCriticalVerificationResults(verificationResult,
"YearEnd test should not have critical errors");
// now run for real
TPeriodIntervalConnector.PeriodYearEnd(intLedgerNumber, false, out verificationResult);
CommonNUnitFunctions.EnsureNullOrOnlyNonCriticalVerificationResults(verificationResult,
"YearEnd should not have critical errors");
++intYear;
// check after year end that income and expense accounts are 0, bank account remains
CheckGLMEntry(intLedgerNumber, intYear, strAccountBank,
-50, 0, 50, 0, 100, 0);
CheckGLMEntry(intLedgerNumber, intYear, strAccountExpense,
0, 0, 0, 0, 0, 0);
CheckGLMEntry(intLedgerNumber, intYear, strAccountGift,
0, 0, 0, 0, 0, 0);
// also check the glm period records
CheckGLMPeriodEntry(intLedgerNumber, intYear, 1, strAccountBank,
-50, 50, 100);
CheckGLMPeriodEntry(intLedgerNumber, intYear, 1, strAccountExpense,
0, 0, 0);
CheckGLMPeriodEntry(intLedgerNumber, intYear, 1, strAccountGift,
0, 0, 0);
// 9700 is the account that the expenses and income from last year is moved to
TGlmInfo glmInfo = new TGlmInfo(intLedgerNumber, intYear, "9700");
glmInfo.Reset();
Assert.IsTrue(glmInfo.MoveNext(), "9700 account not found");
Assert.AreEqual(100, glmInfo.YtdActualBase);
Assert.AreEqual(0, glmInfo.ClosingPeriodActualBase);
LedgerInfo = new TLedgerInfo(intLedgerNumber);
Assert.AreEqual(1, LedgerInfo.CurrentFinancialYear, "After YearEnd, we are in a new financial year");
Assert.AreEqual(1, LedgerInfo.CurrentPeriod, "After YearEnd, we are in Period 1");
Assert.AreEqual(false, LedgerInfo.ProvisionalYearEndFlag, "After YearEnd, ProvisionalYearEnd flag should not be set");
Assert.AreEqual(TYearEndProcessStatus.RESET_STATUS,
(TYearEndProcessStatus)LedgerInfo.YearEndProcessStatus,
"after year end, year end process status should be RESET");
periodInfo = new TAccountPeriodInfo(intLedgerNumber, 1);
Assert.AreEqual(new DateTime(DateTime.Now.Year + 1,
1,
1), periodInfo.PeriodStartDate, "new Calendar should start with January 1st of next year");
}
/// <summary>
/// The constructor creates a base batch and defines the batch parameters. There is only
/// one batch to account. Use a new object to post another batch.
/// </summary>
/// <param name="ALedgerNumber">the ledger number</param>
/// <param name="ABatchDescription">a batch description text</param>
public TCommonAccountingTool(int ALedgerNumber,
string ABatchDescription)
{
FLedgerInfo = new TLedgerInfo(ALedgerNumber);
TCommonAccountingTool_(ABatchDescription);
}
/// <summary>
///
/// </summary>
public TestGLPeriodicEndMonth()
{
FLedgerNumber = 43;
FledgerInfo = new TLedgerInfo(FLedgerNumber);
}
/// <summary>
/// </summary>
/// <param name="ALedgerInfo"></param>
public TResetForwardPeriodICH(TLedgerInfo ALedgerInfo)
{
FLedgerInfo = ALedgerInfo;
}
