/// <summary>
/// Get the start and end date of a given period in a given ledger in the given year
/// </summary>
/// <returns>void</returns>
public void GetPeriodDetails(int ledgernr, int period, out DateTime startOfPeriod, out DateTime endOfPeriod, int whichyear, int column)
{
int currentFinancialYear = parameters.Get("param_current_financial_year_i", column).ToInt();
TFinancialPeriod financialPeriod = new TFinancialPeriod(situation.GetDatabaseConnection(), period, whichyear,
situation.GetParameters(), situation.GetColumn());
AAccountingPeriodTable tab = AAccountingPeriodAccess.LoadByPrimaryKey(ledgernr, period, situation.GetDatabaseConnection().Transaction);
if (tab.Rows.Count == 1)
{
AAccountingPeriodRow row = tab[0];
try
{
endOfPeriod = new DateTime(row.PeriodEndDate.Year - (currentFinancialYear - financialPeriod.realYear),
row.PeriodEndDate.Month,
row.PeriodEndDate.Day);
}
catch (Exception)
{
endOfPeriod = new DateTime(row.PeriodEndDate.Year - (currentFinancialYear - financialPeriod.realYear),
row.PeriodEndDate.Month,
row.PeriodEndDate.Day - 1);
}
startOfPeriod = new DateTime(row.PeriodStartDate.Year - (currentFinancialYear - financialPeriod.realYear),
row.PeriodStartDate.Month,
row.PeriodStartDate.Day);
}
else
{
endOfPeriod = DateTime.MinValue;
startOfPeriod = DateTime.MinValue;
}
financialPeriod = null;
}
/// <summary>
/// copy constructor
/// </summary>
/// <param name="APeriod"></param>
public TFinancialPeriod(TFinancialPeriod APeriod)
{
diffPeriod = APeriod.diffPeriod;
realYear = APeriod.realYear;
realPeriod = APeriod.realPeriod;
realGlmSequence = new TGlmSequence(APeriod.realGlmSequence);
exchangeRateToIntl = APeriod.exchangeRateToIntl;
FCurrentFinancialYear = APeriod.FCurrentFinancialYear;
FNumberAccountingPeriods = APeriod.FNumberAccountingPeriods;
FCurrentPeriod = APeriod.FCurrentPeriod;
FNumberForwardingPeriods = APeriod.FNumberForwardingPeriods;
}
/// <summary>
/// returns status CLOSED, FWD PERIOD, or CURRENT for the given period in the given year of the ledger
/// </summary>
/// <returns></returns>
public string GetStatePeriod(int ledgernumber, int whichyear, int period)
{
string ReturnValue;
int currentPeriod;
int currentYear;
TFinancialPeriod financialPeriod;
currentPeriod = parameters.Get("param_current_period_i").ToInt();
currentYear = parameters.Get("param_current_financial_year_i").ToInt();
financialPeriod = new TFinancialPeriod(situation.GetDatabaseConnection(), period, whichyear,
situation.GetParameters(), situation.GetColumn());
if (financialPeriod.realYear < currentYear)
{
ReturnValue = "CLOSED";
}
else if (financialPeriod.realYear > currentYear)
{
ReturnValue = "FWD PERIOD";
}
else
{
if (currentPeriod == financialPeriod.realPeriod)
{
ReturnValue = "CURRENT";
}
else if (currentPeriod > financialPeriod.realPeriod)
{
ReturnValue = "CLOSED";
}
else
{
ReturnValue = "FWD PERIOD";
}
}
return(ReturnValue);
}
private decimal GetBudgetSummary(TFinancialPeriod StartPeriodParent,
TFinancialPeriod EndPeriodParent,
int pv_period_number_i,
int pv_year_i,
String accountHierarchy)
{
// get all the posting accounts that report to this account in the selected account hierarchy
string accountChildren = GetAllAccountDescendants(StartPeriodParent.realGlmSequence.ledger_number,
StartPeriodParent.realGlmSequence.account_code,
accountHierarchy);
decimal ReturnValue = 0;
// precondition: the parent periods must be in the same real year; that is taken care of in CalculateBudget
if (StartPeriodParent.realYear != EndPeriodParent.realYear)
{
return 0;
}
while (accountChildren.Length > 0)
{
string accountChild = StringHelper.GetNextCSV(ref accountChildren);
StringHelper.GetNextCSV(ref accountChildren);
// alias
StringHelper.GetNextCSV(ref accountChildren);
// accountChildAccountDescr
bool childDebitCreditIndicator = (StringHelper.GetNextCSV(ref accountChildren).ToUpper() == "TRUE");
TFinancialPeriod subAccountStartPeriod = new TFinancialPeriod(
situation.GetDatabaseConnection(), pv_period_number_i, pv_year_i, StartPeriodParent.diffPeriod,
StartPeriodParent.FCurrentFinancialYear, StartPeriodParent.FCurrentPeriod, StartPeriodParent.FNumberAccountingPeriods,
StartPeriodParent.FNumberForwardingPeriods,
LedgerStatus.GlmSequencesCache.GetGlmSequenceCurrentYear(situation.GetDatabaseConnection(),
StartPeriodParent.realGlmSequence.ledger_number, StartPeriodParent.realGlmSequence.cost_centre_code, accountChild,
StartPeriodParent.FCurrentFinancialYear));
TFinancialPeriod subAccountEndPeriod = new TFinancialPeriod(subAccountStartPeriod);
subAccountEndPeriod.realPeriod = EndPeriodParent.realPeriod;
decimal subAccountAmount = GetBudget(subAccountStartPeriod, subAccountEndPeriod, pv_period_number_i, pv_year_i);
if (childDebitCreditIndicator != StartPeriodParent.realGlmSequence.DebitCreditIndicator)
{
subAccountAmount = -1 * subAccountAmount;
}
ReturnValue = ReturnValue + subAccountAmount;
}
return ReturnValue;
}
/// <summary>
/// GetActual retrieves the actuals value of the given period, no matter if it is in a forwarding period.
/// GetActual is similar to GetBudget. The main difference is, that forwarding periods are saved in the current year.
/// You want e.g. the actual data of period 13 in year 2, the current financial year is 3.
/// The call would look like: GetActual(sequence_year_2, sequence_year_3, 13, 12, 3, 2, false, "B");
/// That means, the function has to return the difference between year 3 period 1 and the start balance of year 3.
/// </summary>
/// <param name="period"></param>
/// <param name="pv_period_number_i">If 0, then the start balance of the year (pv_this_year_i) is returned,
/// otherwise the actual amount of the given period
/// If it is a forwarding period, pv_this_year_i is checked against the current financial year,
/// if not equal then the data of the next year is used</param>
/// <param name="pv_year_i"></param>
/// <param name="pv_ytd_l"></param>
/// <param name="pv_currency_select_c"></param>
/// <returns></returns>
private decimal GetActual(TFinancialPeriod period, int pv_period_number_i, int pv_year_i, Boolean pv_ytd_l, String pv_currency_select_c)
{
if ((period == null) || (period.realGlmSequence == null))
{
return 0.0M;
}
if (!period.RealPeriodExists())
{
if (pv_ytd_l)
{
// go back to the last valid period
period.realPeriod = period.FCurrentPeriod + period.FNumberForwardingPeriods;
}
else
{
return 0.0M;
}
}
// for summary accounts, need to get the correct summary amount
if (!period.realGlmSequence.postingAccount)
{
string accountHierarchy = parameters.Get("param_account_hierarchy_c").ToString();
return GetActualSummary(period, pv_period_number_i, pv_year_i, pv_ytd_l, pv_currency_select_c, accountHierarchy);
}
decimal lv_currency_amount_n = 0;
if (period.realPeriod == 0)
{
// start balance
string strSql =
"SELECT a_start_balance_base_n, a_start_balance_intl_n, a_start_balance_foreign_n FROM PUB_a_general_ledger_master " +
"WHERE a_glm_sequence_i = " + period.realGlmSequence.glmSequence;
DataTable tab = ActualsCache.GetDataTable(situation.GetDatabaseConnection(), strSql);
if (tab.Rows.Count > 0)
{
if (pv_currency_select_c == "Base")
{
lv_currency_amount_n = Convert.ToDecimal(tab.Rows[0]["a_start_balance_base_n"]);
}
else
{
if (pv_currency_select_c.ToLower().StartsWith("int"))
{
// Curiously the a_start_balance_intl_n field is not just returned here...
lv_currency_amount_n = Convert.ToDecimal(tab.Rows[0]["a_start_balance_base_n"]) * period.exchangeRateToIntl;
}
else
{
if (tab.Rows[0].IsNull("a_start_balance_foreign_n"))
{
// there is no foreign currency, this is an account in base currency
lv_currency_amount_n = Convert.ToDecimal(tab.Rows[0]["a_start_balance_base_n"]);
}
else
{
lv_currency_amount_n = Convert.ToDecimal(tab.Rows[0]["a_start_balance_foreign_n"]);
}
}
}
}
else // No row returned!
{
return 0.0M; // This is mostly so I can put a breakpoint here and catch this eroneous event.
}
}
else // period != 0
{
lv_currency_amount_n = GetActualValue(period, pv_currency_select_c);
if (pv_ytd_l)
{
/*
* is this something about 13 accounting periods?
* it caused bug 833, and the unit tests for forwarding periods now work as in Progress reports
* This code has been there since delphi.net;
* delphi for win32 did not have it, but there was something else
* where the amount from period 12 was subtracted
*
* if ((pv_period_number_i > period.FNumberAccountingPeriods) && (period.realGlmSequence.incExpAccount) && (pv_year_i == period.FCurrentFinancialYear))
* {
* previousPeriod = new TFinancialPeriod(situation.GetDatabaseConnection(), period, -1, situation.GetParameters(), situation.GetColumn());
* lv_currency_amount_n = GetActualValue(previousPeriod, pv_currency_select_c);
* previousPeriod = null;
* }
*/
if ((period.diffPeriod != 0) && (period.realGlmSequence.incExpAccount))
{
decimal lv_prev_year_amount_n = 0;
// start balance starts with 0
TFinancialPeriod firstPeriod = new TFinancialPeriod(situation.GetDatabaseConnection(), 1, pv_year_i, period);
if (firstPeriod.realYear != period.realYear)
{
// add the amount from the last year (real)
TFinancialPeriod lastPeriodOfPrevYear = new TFinancialPeriod(
situation.GetDatabaseConnection(), period.FNumberAccountingPeriods - period.diffPeriod, pv_year_i - 1, period);
if (lastPeriodOfPrevYear.realGlmSequence != null)
{
lv_prev_year_amount_n = GetActualValue(lastPeriodOfPrevYear, pv_currency_select_c);
}
lastPeriodOfPrevYear = null;
lv_currency_amount_n = lv_currency_amount_n + lv_prev_year_amount_n;
}
// subtract the value that is before the first period of the (artificial) year
TFinancialPeriod beforeFirstPeriodOfYear = new TFinancialPeriod(situation.GetDatabaseConnection(), 0, pv_year_i, period);
lv_prev_year_amount_n = 0;
if (beforeFirstPeriodOfYear.realGlmSequence != null)
{
lv_prev_year_amount_n = GetActualValue(beforeFirstPeriodOfYear, pv_currency_select_c);
}
lv_currency_amount_n = lv_currency_amount_n - lv_prev_year_amount_n;
}
}
else
{
// not pv_ytd_l
lv_currency_amount_n = lv_currency_amount_n - GetActual(pv_period_number_i - 1, pv_year_i, true, pv_currency_select_c);
}
}
period = null;
return lv_currency_amount_n;
}
/// <summary>
/// This will get the amount of the given summary account, by going through
/// all the reporting posting accounts, and using the given account hierarchy;
/// This is done even for the STANDARD hierarchy,
/// though that values are stored in the database, they are calculated from the bottom again.
///
/// </summary>
/// <returns>void</returns>
private decimal GetActualSummary(TFinancialPeriod periodParent,
int pv_period_number_i,
int pv_year_i,
Boolean pv_ytd_l,
String pv_currency_select_c,
String accountHierarchy)
{
// get all the posting accounts that report to this account in the selected account hierarchy
string accountChildren = GetAllAccountDescendants(periodParent.realGlmSequence.ledger_number,
periodParent.realGlmSequence.account_code,
accountHierarchy);
decimal ReturnValue = 0;
while (accountChildren.Length > 0)
{
string accountChild = StringHelper.GetNextCSV(ref accountChildren);
StringHelper.GetNextCSV(ref accountChildren);
// alias
StringHelper.GetNextCSV(ref accountChildren);
// accountChildAccountDescr
bool childDebitCreditIndicator = (StringHelper.GetNextCSV(ref accountChildren).ToUpper() == "TRUE");
TFinancialPeriod subAccountPeriod = new TFinancialPeriod(
situation.GetDatabaseConnection(), pv_period_number_i, pv_year_i, periodParent.diffPeriod, periodParent.FCurrentFinancialYear,
periodParent.FCurrentPeriod, periodParent.FNumberAccountingPeriods, periodParent.FNumberForwardingPeriods,
LedgerStatus.GlmSequencesCache.GetGlmSequenceCurrentYear(situation.GetDatabaseConnection(),
periodParent.realGlmSequence.ledger_number, periodParent.realGlmSequence.cost_centre_code, accountChild,
periodParent.FCurrentFinancialYear));
decimal subAccountAmount = GetActual(subAccountPeriod, pv_period_number_i, pv_year_i, pv_ytd_l, pv_currency_select_c);
if (childDebitCreditIndicator != periodParent.realGlmSequence.DebitCreditIndicator)
{
subAccountAmount = (-1) * subAccountAmount;
}
ReturnValue = ReturnValue + subAccountAmount;
}
return ReturnValue;
}
private decimal GetActualValue(TFinancialPeriod period, String pv_currency_select_c)
{
string strSql = "SELECT a_actual_base_n, a_actual_intl_n, a_actual_foreign_n FROM PUB_a_general_ledger_master_period" +
" WHERE a_glm_sequence_i = " + period.realGlmSequence.glmSequence +
" AND a_period_number_i = " + period.realPeriod;
DataTable tab = ActualsCache.GetDataTable(situation.GetDatabaseConnection(), strSql);
if (tab.Rows.Count > 0)
{
pv_currency_select_c = pv_currency_select_c.ToLower();
if (pv_currency_select_c == "base")
{
return Convert.ToDecimal(tab.Rows[0]["a_actual_base_n"]);
}
else if (pv_currency_select_c.StartsWith("int"))
{
return Convert.ToDecimal(tab.Rows[0]["a_actual_base_n"]) * period.exchangeRateToIntl;
}
else if (pv_currency_select_c == "transaction")
{
if (tab.Rows[0].IsNull("a_actual_foreign_n"))
{
// this is not a foreign currency account, so it must be base currency
return Convert.ToDecimal(tab.Rows[0]["a_actual_base_n"]);
}
return Convert.ToDecimal(tab.Rows[0]["a_actual_foreign_n"]);
}
}
return 0.0M;
}
private decimal CalculateBudget(int pv_start_period_i, int pv_end_period_i, int pv_year_i, String pv_currency_select_c)
{
System.Int32 Counter;
decimal ReturnValue = 0;
decimal lastExchangeRate = -1;
TFinancialPeriod startPeriod = new TFinancialPeriod(situation.GetDatabaseConnection(), pv_start_period_i, pv_year_i,
situation.GetParameters(), situation.GetColumn());
if (startPeriod.realPeriod > startPeriod.FNumberAccountingPeriods)
{
// budgets are not stored in the forwarding periods, but in the next year
startPeriod = new TFinancialPeriod(
situation.GetDatabaseConnection(), pv_start_period_i - startPeriod.FNumberAccountingPeriods, pv_year_i + 1,
situation.GetParameters(), situation.GetColumn());
}
TFinancialPeriod endPeriod = new TFinancialPeriod(situation.GetDatabaseConnection(), pv_end_period_i, pv_year_i,
situation.GetParameters(), situation.GetColumn());
if (endPeriod.realPeriod > endPeriod.FNumberAccountingPeriods)
{
// budgets are not stored in the forwarding periods, but in the next year
endPeriod = new TFinancialPeriod(
situation.GetDatabaseConnection(), pv_end_period_i - endPeriod.FNumberAccountingPeriods, pv_year_i + 1,
situation.GetParameters(), situation.GetColumn());
}
// make sure, endperiod is valid, ie. not beyond the existing periods
Counter = pv_end_period_i - 1;
while (((endPeriod == null) || (endPeriod.realGlmSequence == null)) && (Counter >= pv_start_period_i))
{
endPeriod = new TFinancialPeriod(situation.GetDatabaseConnection(), Counter, pv_year_i,
situation.GetParameters(), situation.GetColumn());
Counter = Counter - 1;
}
// GetBudget can only deal with a range of periods in the same year (same glm sequence)
if (startPeriod.realYear != endPeriod.realYear)
{
TFinancialPeriod endOfYearPeriod = new TFinancialPeriod(startPeriod);
endOfYearPeriod.realPeriod = endOfYearPeriod.FNumberAccountingPeriods;
ReturnValue = ReturnValue + GetBudget(startPeriod, endOfYearPeriod, pv_start_period_i, pv_year_i);
TFinancialPeriod beginOfYearPeriod = new TFinancialPeriod(endPeriod);
endOfYearPeriod.realPeriod = 1;
ReturnValue = ReturnValue + GetBudget(beginOfYearPeriod, endPeriod, pv_end_period_i, pv_year_i);
}
else
{
ReturnValue = ReturnValue + GetBudget(startPeriod, endPeriod, pv_start_period_i, pv_year_i);
}
if (pv_currency_select_c.ToLower().StartsWith("int"))
{
if ((endPeriod != null) && (endPeriod.realGlmSequence != null))
{
lastExchangeRate = endPeriod.exchangeRateToIntl;
ReturnValue = ReturnValue * lastExchangeRate;
}
}
return ReturnValue;
}
private decimal GetBudget(TFinancialPeriod startperiod, TFinancialPeriod endperiod, Int32 periodNr, Int32 yearNr)
{
decimal ReturnValue = 0;
if ((startperiod == null) || (startperiod.realGlmSequence == null) || (startperiod.realGlmSequence.glmSequence <= -1))
{
return 0;
}
// precondition: the periods must be in the same real year; that is taken care of in CalculateBudget
if (startperiod.realYear != endperiod.realYear)
{
return 0;
}
// for summary accounts, when using a different than the STANDARD accounting hierarchy,
// need to get the correct summary amount
string accountHierarchy = parameters.Get("param_account_hierarchy_c").ToString();
if ((!startperiod.realGlmSequence.postingAccount) && (!(accountHierarchy.ToUpper().CompareTo("STANDARD") == 0)))
{
return GetBudgetSummary(startperiod, endperiod, periodNr, yearNr, accountHierarchy);
}
string strSql = "SELECT SUM(a_budget_base_n) FROM PUB_a_general_ledger_master_period WHERE a_glm_sequence_i = " +
startperiod.realGlmSequence.glmSequence + " AND a_period_number_i >= " + startperiod.realPeriod +
" AND a_period_number_i <= " + endperiod.realPeriod;
DataTable tab = situation.GetDatabaseConnection().SelectDT(strSql, "GetBudget_TempTable", situation.GetDatabaseConnection().Transaction);
if (tab.Rows.Count > 0)
{
ReturnValue = Convert.ToDecimal(tab.Rows[0][0]);
}
return ReturnValue;
}
/// <summary>
/// returns status CLOSED, FWD PERIOD, or CURRENT for the given period in the given year of the ledger
/// </summary>
/// <returns></returns>
public string GetStatePeriod(int ledgernumber, int whichyear, int period)
{
string ReturnValue;
int currentPeriod;
int currentYear;
TFinancialPeriod financialPeriod;
currentPeriod = parameters.Get("param_current_period_i").ToInt();
currentYear = parameters.Get("param_current_financial_year_i").ToInt();
financialPeriod = new TFinancialPeriod(situation.GetDatabaseConnection(), period, whichyear,
situation.GetParameters(), situation.GetColumn());
if (financialPeriod.realYear < currentYear)
{
ReturnValue = "CLOSED";
}
else if (financialPeriod.realYear > currentYear)
{
ReturnValue = "FWD PERIOD";
}
else
{
if (currentPeriod == financialPeriod.realPeriod)
{
ReturnValue = "CURRENT";
}
else if (currentPeriod > financialPeriod.realPeriod)
{
ReturnValue = "CLOSED";
}
else
{
ReturnValue = "FWD PERIOD";
}
}
return ReturnValue;
}
/// <summary>
/// Get the start and end date of a given period in a given ledger in the given year
/// </summary>
/// <returns>void</returns>
public void GetPeriodDetails(int ledgernr, int period, out DateTime startOfPeriod, out DateTime endOfPeriod, int whichyear, int column)
{
int currentFinancialYear = parameters.Get("param_current_financial_year_i", column).ToInt();
TFinancialPeriod financialPeriod = new TFinancialPeriod(situation.GetDatabaseConnection(), period, whichyear,
situation.GetParameters(), situation.GetColumn());
AAccountingPeriodTable tab = AAccountingPeriodAccess.LoadByPrimaryKey(ledgernr, period, situation.GetDatabaseConnection().Transaction);
if (tab.Rows.Count == 1)
{
AAccountingPeriodRow row = tab[0];
try
{
endOfPeriod = new DateTime(row.PeriodEndDate.Year - (currentFinancialYear - financialPeriod.realYear),
row.PeriodEndDate.Month,
row.PeriodEndDate.Day);
}
catch (Exception)
{
endOfPeriod = new DateTime(row.PeriodEndDate.Year - (currentFinancialYear - financialPeriod.realYear),
row.PeriodEndDate.Month,
row.PeriodEndDate.Day - 1);
}
startOfPeriod = new DateTime(row.PeriodStartDate.Year - (currentFinancialYear - financialPeriod.realYear),
row.PeriodStartDate.Month,
row.PeriodStartDate.Day);
}
else
{
endOfPeriod = DateTime.MinValue;
startOfPeriod = DateTime.MinValue;
}
financialPeriod = null;
}
/// <summary>
/// constructor
/// </summary>
/// <param name="databaseConnection"></param>
/// <param name="period"></param>
/// <param name="year"></param>
/// <param name="AFinancialPeriod"></param>
public TFinancialPeriod(TDataBase databaseConnection, int period, int year, TFinancialPeriod AFinancialPeriod) :
this(databaseConnection, period, year, AFinancialPeriod.diffPeriod, AFinancialPeriod.FCurrentFinancialYear,
AFinancialPeriod.FCurrentPeriod, AFinancialPeriod.FNumberAccountingPeriods, AFinancialPeriod.FNumberForwardingPeriods,
AFinancialPeriod.realGlmSequence)
{
}
/// <summary>
/// creates a period before or after the given period
/// </summary>
public TFinancialPeriod(TDataBase databaseConnection, TFinancialPeriod period, int diff, TParameterList parameters, int column)
: this(databaseConnection, period.realPeriod + diff, period.realYear, parameters, column)
{
}
/// <summary>
/// constructor
/// </summary>
/// <param name="databaseConnection"></param>
/// <param name="period"></param>
/// <param name="year"></param>
/// <param name="AFinancialPeriod"></param>
public TFinancialPeriod(TDataBase databaseConnection, int period, int year, TFinancialPeriod AFinancialPeriod) :
this(databaseConnection, period, year, AFinancialPeriod.diffPeriod, AFinancialPeriod.FCurrentFinancialYear,
AFinancialPeriod.FCurrentPeriod, AFinancialPeriod.FNumberAccountingPeriods, AFinancialPeriod.FNumberForwardingPeriods,
AFinancialPeriod.realGlmSequence)
{
}
/// <summary>
/// copy constructor
/// </summary>
/// <param name="APeriod"></param>
public TFinancialPeriod(TFinancialPeriod APeriod)
{
diffPeriod = APeriod.diffPeriod;
realYear = APeriod.realYear;
realPeriod = APeriod.realPeriod;
realGlmSequence = new TGlmSequence(APeriod.realGlmSequence);
exchangeRateToIntl = APeriod.exchangeRateToIntl;
FCurrentFinancialYear = APeriod.FCurrentFinancialYear;
FNumberAccountingPeriods = APeriod.FNumberAccountingPeriods;
FCurrentPeriod = APeriod.FCurrentPeriod;
FNumberForwardingPeriods = APeriod.FNumberForwardingPeriods;
}
/// <summary>
/// creates a period before or after the given period
/// </summary>
public TFinancialPeriod(TDataBase databaseConnection, TFinancialPeriod period, int diff, TParameterList parameters, int column)
: this(databaseConnection, period.realPeriod + diff, period.realYear, parameters, column)
{
}
