/// <summary>
/// Load a single row by sequence and period
/// </summary>
/// <returns>True if it seemed to work</returns>
public Boolean LoadBySequence(Int32 ASequence, Int32 APeriod)
{
Boolean LoadedOk = false;
if (ASequence != -1)
{
TDBTransaction transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("LoadBySequence", FDataBase);
db.ReadTransaction(ref transaction,
delegate
{
FGLMpTable = AGeneralLedgerMasterPeriodAccess.LoadByPrimaryKey(ASequence, APeriod, transaction);
LoadedOk = FGLMpTable.Rows.Count > 0;
FGLMpRow = (LoadedOk) ? FGLMpTable[0] : null;
});
if (FDataBase == null)
{
db.CloseDBConnection();
}
}
return(LoadedOk);
}
/// <summary>
/// Load a single row by sequence and period
/// </summary>
/// <returns>True if it seemed to work</returns>
public Boolean LoadBySequence(Int32 ASequence, Int32 APeriod)
{
Boolean LoadedOk = false;
if (ASequence != -1)
{
bool NewTransaction = false;
try
{
TDBTransaction transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
FGLMpTable = AGeneralLedgerMasterPeriodAccess.LoadByPrimaryKey(ASequence, APeriod, transaction);
LoadedOk = FGLMpTable.Rows.Count > 0;
FGLMpRow = (LoadedOk) ? FGLMpTable[0] : null;
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
}
}
return(LoadedOk);
}
/// <summary>
/// Loads <b>all</b> GLMP data for the selected year
/// </summary>
public void LoadByYear(Int32 AYear)
{
bool NewTransaction = false;
try
{
TDBTransaction transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
AGeneralLedgerMasterTable GLMTemplateTbl = new AGeneralLedgerMasterTable();
AGeneralLedgerMasterRow GLMTemplateRow = GLMTemplateTbl.NewRowTyped(false);
GLMTemplateRow.LedgerNumber = FLedgerNumber;
GLMTemplateRow.Year = AYear;
FGLMpTable = AGeneralLedgerMasterPeriodAccess.LoadViaAGeneralLedgerMasterTemplate(GLMTemplateRow, transaction);
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
}
}
/// <summary>
/// Loads <b>all</b> GLMP data for the selected year
/// </summary>
public TGlmpInfo(Int32 ALedgerNumber, Int32 AYear, Int32 ASequence, Int32 APeriod)
{
if (ASequence != -1)
{
bool NewTransaction = false;
try
{
TDBTransaction transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
out NewTransaction);
FGLMpTable = AGeneralLedgerMasterPeriodAccess.LoadByPrimaryKey(ASequence, APeriod, transaction);
FGLMpRow = (FGLMpTable.Rows.Count > 0) ? FGLMpTable[0] : null;
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
}
}
else
{
LoadAll(ALedgerNumber, AYear);
FGLMpRow = null;
}
}
/// <summary>
///Description: CalculateBudget is only used internally as a helper function for GetBudget.
///Returns the budget for the given period of time,
/// if ytd is set, this period is from start_period to end_period,
/// otherwise it is only the value of the end_period.
/// currency_select is either "B" for base or "I" for international currency
/// </summary>
/// <param name="AGLMSeq"></param>
/// <param name="AStartPeriod"></param>
/// <param name="AEndPeriod"></param>
/// <param name="AYTD"></param>
/// <param name="ACurrencySelect"></param>
/// <returns>Budget Amount</returns>
private static decimal CalculateBudget(int AGLMSeq, int AStartPeriod, int AEndPeriod, bool AYTD, string ACurrencySelect)
{
decimal retVal = 0;
decimal lv_currency_amount_n = 0;
int lv_ytd_period_i;
if (AGLMSeq == -1)
{
return(retVal);
}
AGeneralLedgerMasterPeriodTable GeneralLedgerMasterPeriodTable = null;
AGeneralLedgerMasterPeriodRow GeneralLedgerMasterPeriodRow = null;
if (!AYTD)
{
AStartPeriod = AEndPeriod;
}
TDBTransaction transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("CalculateBudget");
db.ReadTransaction(
ref transaction,
delegate
{
for (lv_ytd_period_i = AStartPeriod; lv_ytd_period_i <= AEndPeriod; lv_ytd_period_i++)
{
GeneralLedgerMasterPeriodTable = AGeneralLedgerMasterPeriodAccess.LoadByPrimaryKey(AGLMSeq, lv_ytd_period_i, transaction);
GeneralLedgerMasterPeriodRow = (AGeneralLedgerMasterPeriodRow)GeneralLedgerMasterPeriodTable.Rows[0];
if (GeneralLedgerMasterPeriodRow != null)
{
if (ACurrencySelect == MFinanceConstants.CURRENCY_BASE)
{
lv_currency_amount_n += GeneralLedgerMasterPeriodRow.BudgetBase;
}
else if (ACurrencySelect == MFinanceConstants.CURRENCY_INTERNATIONAL)
{
lv_currency_amount_n += GeneralLedgerMasterPeriodRow.BudgetIntl;
}
}
}
});
retVal = lv_currency_amount_n;
return(retVal);
}
/// <summary>
///Description: CalculateBudget is only used internally as a helper function for GetBudget.
///Returns the budget for the given period of time,
/// if ytd is set, this period is from start_period to end_period,
/// otherwise it is only the value of the end_period.
/// currency_select is either "B" for base or "I" for international currency
/// </summary>
/// <param name="AGLMSeq"></param>
/// <param name="AStartPeriod"></param>
/// <param name="AEndPeriod"></param>
/// <param name="AYTD"></param>
/// <param name="ACurrencySelect"></param>
/// <returns></returns>
private static decimal CalculateBudget(int AGLMSeq, int AStartPeriod, int AEndPeriod, bool AYTD, string ACurrencySelect)
{
decimal retVal = 0;
decimal lv_currency_amount_n = 0;
int lv_ytd_period_i;
if (AGLMSeq == -1)
{
return(retVal);
}
bool NewTransaction = false;
TDBTransaction transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTransaction);
AGeneralLedgerMasterPeriodTable GeneralLedgerMasterPeriodTable = null;
AGeneralLedgerMasterPeriodRow GeneralLedgerMasterPeriodRow = null;
if (!AYTD)
{
AStartPeriod = AEndPeriod;
}
for (lv_ytd_period_i = AStartPeriod; lv_ytd_period_i <= AEndPeriod; lv_ytd_period_i++)
{
GeneralLedgerMasterPeriodTable = AGeneralLedgerMasterPeriodAccess.LoadByPrimaryKey(AGLMSeq, lv_ytd_period_i, transaction);
GeneralLedgerMasterPeriodRow = (AGeneralLedgerMasterPeriodRow)GeneralLedgerMasterPeriodTable.Rows[0];
if (GeneralLedgerMasterPeriodRow != null)
{
if (ACurrencySelect == MFinanceConstants.CURRENCY_BASE)
{
lv_currency_amount_n += GeneralLedgerMasterPeriodRow.BudgetBase;
}
else if (ACurrencySelect == MFinanceConstants.CURRENCY_INTERNATIONAL)
{
lv_currency_amount_n += GeneralLedgerMasterPeriodRow.BudgetIntl;
}
}
}
retVal = lv_currency_amount_n;
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
return(retVal);
}
/// <summary>
/// Loads <b>all</b> GLMP data for the selected year
/// </summary>
public void LoadByYear(Int32 AYear)
{
TDBTransaction transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("LoadByYear", FDataBase);
db.ReadTransaction(ref transaction,
delegate
{
AGeneralLedgerMasterTable GLMTemplateTbl = new AGeneralLedgerMasterTable();
AGeneralLedgerMasterRow GLMTemplateRow = GLMTemplateTbl.NewRowTyped(false);
GLMTemplateRow.LedgerNumber = FLedgerNumber;
GLMTemplateRow.Year = AYear;
FGLMpTable = AGeneralLedgerMasterPeriodAccess.LoadViaAGeneralLedgerMasterTemplate(GLMTemplateRow, transaction);
});
if (FDataBase == null)
{
db.CloseDBConnection();
}
}
public static decimal GetBudgetValue(ref DataTable APeriodDataTable, int AGLMSequence, int APeriodNumber)
{
decimal GetBudgetValue = 0;
DataRow TempRow = (DataRow)APeriodDataTable.Rows.Find(new object[] { AGLMSequence, APeriodNumber });
if (TempRow == null)
{
AGeneralLedgerMasterPeriodTable GeneralLedgerMasterPeriodTable = null;
AGeneralLedgerMasterPeriodRow GeneralLedgerMasterPeriodRow = null;
TDBTransaction transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref transaction,
delegate
{
GeneralLedgerMasterPeriodTable = AGeneralLedgerMasterPeriodAccess.LoadByPrimaryKey(AGLMSequence, APeriodNumber, transaction);
});
if (GeneralLedgerMasterPeriodTable.Count > 0)
{
GeneralLedgerMasterPeriodRow = (AGeneralLedgerMasterPeriodRow)GeneralLedgerMasterPeriodTable.Rows[0];
DataRow DR = (DataRow)APeriodDataTable.NewRow();
DR["GLMSequence"] = AGLMSequence;
DR["PeriodNumber"] = APeriodNumber;
DR["BudgetBase"] = GeneralLedgerMasterPeriodRow.BudgetBase;
APeriodDataTable.Rows.Add(DR);
}
}
else
{
//Set to budget base
GetBudgetValue = Convert.ToDecimal(TempRow["BudgetBase"]);
}
return(GetBudgetValue);
}
public static decimal GetBudgetValue(ref DataTable APeriodDataTable, int AGLMSequence, int APeriodNumber)
{
decimal GetBudgetValue = 0;
DataRow TempRow = (DataRow)APeriodDataTable.Rows.Find(new object[] { AGLMSequence, APeriodNumber });
if (TempRow == null)
{
AGeneralLedgerMasterPeriodTable GeneralLedgerMasterPeriodTable = null;
AGeneralLedgerMasterPeriodRow GeneralLedgerMasterPeriodRow = null;
TDataBase db = DBAccess.Connect("Budget");
TDBTransaction transaction = new TDBTransaction();
db.ReadTransaction(
ref transaction,
delegate
{
GeneralLedgerMasterPeriodTable = AGeneralLedgerMasterPeriodAccess.LoadByPrimaryKey(AGLMSequence, APeriodNumber, transaction);
});
if (GeneralLedgerMasterPeriodTable.Count > 0)
{
GeneralLedgerMasterPeriodRow = (AGeneralLedgerMasterPeriodRow)GeneralLedgerMasterPeriodTable.Rows[0];
DataRow DR = (DataRow)APeriodDataTable.NewRow();
DR["GLMSequence"] = AGLMSequence;
DR["PeriodNumber"] = APeriodNumber;
DR["BudgetBase"] = GeneralLedgerMasterPeriodRow.BudgetBase;
APeriodDataTable.Rows.Add(DR);
}
}
else
{
//Set to budget base
GetBudgetValue = Convert.ToDecimal(TempRow["BudgetBase"]);
}
return(GetBudgetValue);
}
/// <summary>
/// Get the actual amount
/// </summary>
/// <param name="ALedgerNumber"></param>
/// <param name="AGLMSeqThisYear"></param>
/// <param name="AGLMSeqNextYear"></param>
/// <param name="APeriodNumber"></param>
/// <param name="ANumberAccountingPeriods"></param>
/// <param name="ACurrentFinancialYear"></param>
/// <param name="AThisYear"></param>
/// <param name="AYTD"></param>
/// <param name="ABalSheetForwardPeriods"></param>
/// <param name="ACurrencySelect"></param>
/// <returns></returns>
private static decimal GetActualInternal(int ALedgerNumber,
int AGLMSeqThisYear,
int AGLMSeqNextYear,
int APeriodNumber,
int ANumberAccountingPeriods,
int ACurrentFinancialYear,
int AThisYear,
bool AYTD,
bool ABalSheetForwardPeriods,
string ACurrencySelect)
{
decimal retVal = 0;
decimal currencyAmount = 0;
bool incExpAccountFwdPeriod = false;
//DEFINE BUFFER a_glm_period FOR a_general_ledger_master_period.
//DEFINE BUFFER a_glm FOR a_general_ledger_master.
//DEFINE BUFFER buf_account FOR a_account.
if (AGLMSeqThisYear == -1)
{
return(retVal);
}
bool newTransaction = false;
TDataBase db = DBAccess.Connect("GetActualInternal");
TDBTransaction dBTransaction = db.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out newTransaction);
AGeneralLedgerMasterTable generalLedgerMasterTable = null;
AGeneralLedgerMasterRow generalLedgerMasterRow = null;
AGeneralLedgerMasterPeriodTable generalLedgerMasterPeriodTable = null;
AGeneralLedgerMasterPeriodRow generalLedgerMasterPeriodRow = null;
AAccountTable AccountTable = null;
AAccountRow AccountRow = null;
try
{
if (APeriodNumber == 0) /* start balance */
{
generalLedgerMasterTable = AGeneralLedgerMasterAccess.LoadByPrimaryKey(AGLMSeqThisYear, dBTransaction);
generalLedgerMasterRow = (AGeneralLedgerMasterRow)generalLedgerMasterTable.Rows[0];
switch (ACurrencySelect)
{
case MFinanceConstants.CURRENCY_BASE:
currencyAmount = generalLedgerMasterRow.StartBalanceBase;
break;
case MFinanceConstants.CURRENCY_INTERNATIONAL:
currencyAmount = generalLedgerMasterRow.StartBalanceIntl;
break;
default:
currencyAmount = generalLedgerMasterRow.StartBalanceForeign;
break;
}
}
else if (APeriodNumber > ANumberAccountingPeriods) /* forwarding periods only exist for the current financial year */
{
if (ACurrentFinancialYear == AThisYear)
{
generalLedgerMasterPeriodTable = AGeneralLedgerMasterPeriodAccess.LoadByPrimaryKey(AGLMSeqThisYear,
APeriodNumber,
dBTransaction);
generalLedgerMasterPeriodRow = (AGeneralLedgerMasterPeriodRow)generalLedgerMasterPeriodTable.Rows[0];
}
else
{
generalLedgerMasterPeriodTable =
AGeneralLedgerMasterPeriodAccess.LoadByPrimaryKey(AGLMSeqNextYear,
(APeriodNumber - ANumberAccountingPeriods),
dBTransaction);
generalLedgerMasterPeriodRow = (AGeneralLedgerMasterPeriodRow)generalLedgerMasterPeriodTable.Rows[0];
}
}
else /* normal period */
{
generalLedgerMasterPeriodTable = AGeneralLedgerMasterPeriodAccess.LoadByPrimaryKey(AGLMSeqThisYear, APeriodNumber, dBTransaction);
generalLedgerMasterPeriodRow = (AGeneralLedgerMasterPeriodRow)generalLedgerMasterPeriodTable.Rows[0];
}
if (generalLedgerMasterPeriodRow != null)
{
switch (ACurrencySelect)
{
case MFinanceConstants.CURRENCY_BASE:
currencyAmount = generalLedgerMasterPeriodRow.ActualBase;
break;
case MFinanceConstants.CURRENCY_INTERNATIONAL:
currencyAmount = generalLedgerMasterPeriodRow.ActualIntl;
break;
default:
currencyAmount = generalLedgerMasterPeriodRow.ActualForeign;
break;
}
}
if ((APeriodNumber > ANumberAccountingPeriods) && (ACurrentFinancialYear == AThisYear))
{
generalLedgerMasterTable = AGeneralLedgerMasterAccess.LoadByPrimaryKey(AGLMSeqThisYear, dBTransaction);
generalLedgerMasterRow = (AGeneralLedgerMasterRow)generalLedgerMasterTable.Rows[0];
AccountTable = AAccountAccess.LoadByPrimaryKey(ALedgerNumber, generalLedgerMasterRow.AccountCode, dBTransaction);
AccountRow = (AAccountRow)AccountTable.Rows[0];
if ((AccountRow.AccountCode.ToUpper() == MFinanceConstants.ACCOUNT_TYPE_INCOME.ToUpper()) ||
(AccountRow.AccountCode.ToUpper() == MFinanceConstants.ACCOUNT_TYPE_EXPENSE.ToUpper()) &&
!ABalSheetForwardPeriods)
{
incExpAccountFwdPeriod = true;
currencyAmount -= GetActualInternal(ALedgerNumber,
AGLMSeqThisYear,
AGLMSeqNextYear,
ANumberAccountingPeriods,
ANumberAccountingPeriods,
ACurrentFinancialYear,
AThisYear,
true,
ABalSheetForwardPeriods,
ACurrencySelect);
}
}
if (!AYTD)
{
if (!((APeriodNumber == (ANumberAccountingPeriods + 1)) && incExpAccountFwdPeriod) &&
!((APeriodNumber == (ANumberAccountingPeriods + 1)) && (ACurrentFinancialYear > AThisYear)))
{
/* if it is an income expense acount, and we are in period 13, nothing needs to be subtracted,
* because that was done in correcting the amount in the block above;
* if we are in a previous year, in period 13, don't worry about subtracting.
*
* THIS IS CLEARLY INCORRECT - THE CONDITION ABOVE APPLIES *ONLY* IN THE FIRST FORWARDING PERIOD, NOT IN EVERY FORWARDING PERIOD.
* IF THE METHOD IS ONLY CALLED FROM AUTOGENERATE BUDGETS, THIS IS PROBABLY INCONSEQUENTIAL.
*/
currencyAmount -= GetActualInternal(ALedgerNumber,
AGLMSeqThisYear,
AGLMSeqNextYear,
(APeriodNumber - 1),
ANumberAccountingPeriods,
ACurrentFinancialYear,
AThisYear,
true,
ABalSheetForwardPeriods,
ACurrencySelect);
}
}
retVal = currencyAmount;
}
finally
{
if (newTransaction)
{
dBTransaction.Rollback();
}
}
return(retVal);
}
} // Run Revaluation
private Boolean RevaluateAccount(AGeneralLedgerMasterTable AGlmTbl, decimal AExchangeRate, string ACurrencyCode)
{
Boolean transactionsWereCreated = false;
foreach (AGeneralLedgerMasterRow glmRow in AGlmTbl.Rows)
{
AGeneralLedgerMasterPeriodTable glmpTbl = null;
TDBTransaction transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref transaction,
delegate
{
glmpTbl = AGeneralLedgerMasterPeriodAccess.LoadByPrimaryKey(glmRow.GlmSequence, F_AccountingPeriod, transaction);
});
try
{
if (glmpTbl.Rows.Count == 0)
{
continue; // I really don't expect this, but if it does happen, this will prevent a crash!
}
AGeneralLedgerMasterPeriodRow glmpRow = glmpTbl[0];
//
// If ActualForeign has not been set, I can't allow the ORM to even attempt to access them:
// (If ActualForeign is NULL, that's probably a fault, but this has occured in historical data.)
if (glmpRow.IsActualBaseNull() || glmpRow.IsActualForeignNull())
{
continue;
}
decimal delta = AccountDelta(glmpRow.ActualBase,
glmpRow.ActualForeign,
AExchangeRate,
F_BaseCurrencyDigits);
if (delta != 0)
{
// Now we have the relevant Cost Centre ...
RevaluateCostCentre(glmRow.AccountCode, glmRow.CostCentreCode, delta, AExchangeRate, ACurrencyCode);
transactionsWereCreated = true;
}
else
{
string strMessage = String.Format(
Catalog.GetString("The account {1}:{0} does not require revaluation."),
glmRow.AccountCode,
glmRow.CostCentreCode,
AExchangeRate);
FVerificationCollection.Add(new TVerificationResult(
strStatusContent, strMessage, TResultSeverity.Resv_Status));
TLedgerInitFlag.RemoveFlagComponent(F_LedgerNum, MFinanceConstants.LEDGER_INIT_FLAG_REVAL, glmRow.AccountCode);
}
}
catch (EVerificationException terminate)
{
FVerificationCollection = terminate.ResultCollection();
}
} // foreach
return(transactionsWereCreated);
}
private void RevaluateAccount(DataView GLMView, decimal AExchangeRate)
{
foreach (DataRowView RowView in GLMView)
{
AGeneralLedgerMasterRow glmRow = (AGeneralLedgerMasterRow)RowView.Row;
ACostCentreTable tempTbl = null;
AGeneralLedgerMasterPeriodTable glmpTbl = null;
TDBTransaction transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref transaction,
delegate
{
tempTbl = ACostCentreAccess.LoadByPrimaryKey(F_LedgerNum, glmRow.CostCentreCode, transaction);
glmpTbl = AGeneralLedgerMasterPeriodAccess.LoadByPrimaryKey(glmRow.GlmSequence, F_AccountingPeriod, transaction);
});
if (tempTbl.Rows.Count == 0)
{
continue; // I really don't expect this, but if it does happen, this will prevent a crash!
}
ACostCentreRow tempRow = tempTbl[0];
if (!tempRow.PostingCostCentreFlag)
{
continue; // I do expect this - many rows are not "posting" cost centres.
}
try
{
if (glmpTbl.Rows.Count == 0)
{
continue; // I really don't expect this, but if it does happen, this will prevent a crash!
}
AGeneralLedgerMasterPeriodRow glmpRow = glmpTbl[0];
//
// If ActualForeign has not been set, I can't allow the ORM to even attempt to access them:
// (If ActualForeign is NULL, that's probably a fault, but this has occured in historical data.)
if (glmpRow.IsActualBaseNull() || glmpRow.IsActualForeignNull())
{
continue;
}
decimal delta = AccountDelta(glmpRow.ActualBase,
glmpRow.ActualForeign,
AExchangeRate,
F_BaseCurrencyDigits);
if (delta != 0)
{
// Now we have the relevant Cost Centre ...
RevaluateCostCentre(glmRow.AccountCode, glmRow.CostCentreCode, delta);
}
else
{
string strMessage = String.Format(
Catalog.GetString("The account {1}:{0} does not require revaluation."),
glmRow.AccountCode,
glmRow.CostCentreCode,
AExchangeRate);
FVerificationCollection.Add(new TVerificationResult(
strStatusContent, strMessage, TResultSeverity.Resv_Noncritical));
}
}
catch (EVerificationException terminate)
{
FVerificationCollection = terminate.ResultCollection();
}
catch (DivideByZeroException)
{
FVerificationCollection.Add(new TVerificationResult(
strStatusContent, Catalog.GetString("DivideByZeroException"), TResultSeverity.Resv_Noncritical));
}
catch (OverflowException)
{
FVerificationCollection.Add(new TVerificationResult(
strStatusContent, Catalog.GetString("OverflowException"), TResultSeverity.Resv_Noncritical));
}
}
}
