/// <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 all GLMP rows for this Cost Centre in this period
/// </summary>
/// <returns></returns>
public Boolean LoadByCostCentreAccountPeriod(String ACostCentreCode, String AAccountCode, Int32 AYear, Int32 APeriod)
{
Boolean LoadedOk = false;
TDBTransaction transaction = null;
FGLMpTable = new AGeneralLedgerMasterPeriodTable();
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum, ref transaction,
delegate
{
DBAccess.GDBAccessObj.SelectDT(
FGLMpTable,
"SELECT a_general_ledger_master_period.* FROM" +
" a_general_ledger_master_period, a_general_ledger_master" +
" WHERE" +
" a_general_ledger_master_period.a_glm_sequence_i=a_general_ledger_master.a_glm_sequence_i" +
" AND a_general_ledger_master.a_ledger_number_i = " + FLedgerNumber +
" AND a_general_ledger_master.a_year_i = " + AYear +
" AND a_general_ledger_master.a_account_code_c = '" + AAccountCode + "'" +
" AND a_general_ledger_master.a_cost_centre_code_c = '" + ACostCentreCode + "'" +
" AND a_general_ledger_master_period.a_period_number_i=" + APeriod,
transaction);
LoadedOk = (FGLMpTable.Rows.Count > 0);
}); // Get NewOrExisting AutoReadTransaction
FGLMpRow = (LoadedOk) ? FGLMpTable[0] : null;
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>
/// 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 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>
/// Load all GLMP rows for this Cost Centre in this period
/// </summary>
/// <returns></returns>
public Boolean LoadByCostCentreAccountPeriod(String ACostCentreCode, String AAccountCode, Int32 AYear, Int32 APeriod)
{
Boolean LoadedOk = false;
TDBTransaction transaction = new TDBTransaction();
TDataBase db = DBAccess.Connect("LoadByCostCentreAccountPeriod", FDataBase);
FGLMpTable = new AGeneralLedgerMasterPeriodTable();
db.ReadTransaction(ref transaction,
delegate
{
db.SelectDT(
FGLMpTable,
"SELECT a_general_ledger_master_period.* FROM" +
" a_general_ledger_master_period, a_general_ledger_master" +
" WHERE" +
" a_general_ledger_master_period.a_glm_sequence_i=a_general_ledger_master.a_glm_sequence_i" +
" AND a_general_ledger_master.a_ledger_number_i = " + FLedgerNumber +
" AND a_general_ledger_master.a_year_i = " + AYear +
" AND a_general_ledger_master.a_account_code_c = '" + AAccountCode + "'" +
" AND a_general_ledger_master.a_cost_centre_code_c = '" + ACostCentreCode + "'" +
" AND a_general_ledger_master_period.a_period_number_i=" + APeriod,
transaction);
LoadedOk = (FGLMpTable.Rows.Count > 0);
}); // AutoReadTransaction
if (FDataBase == null)
{
db.CloseDBConnection();
}
FGLMpRow = (LoadedOk) ? FGLMpTable[0] : null;
return(LoadedOk);
}
/// <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>
/// 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>
///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);
}
private DataTable GetGlmpRows(Int32 AYear, TDBTransaction ATransaction, Int32 APeriodGreaterThan)
{
AGeneralLedgerMasterPeriodTable typedTable = new AGeneralLedgerMasterPeriodTable();
string strSQL = "SELECT PUB_a_general_ledger_master_period.* " +
" FROM PUB_a_general_ledger_master, PUB_a_general_ledger_master_period" +
" WHERE PUB_a_general_ledger_master.a_year_i = " + AYear +
" AND PUB_a_general_ledger_master.a_glm_sequence_i = PUB_a_general_ledger_master_period.a_glm_sequence_i" +
" AND PUB_a_general_ledger_master_period.a_period_number_i > " + APeriodGreaterThan +
" ORDER BY PUB_a_general_ledger_master_period.a_period_number_i;";
DBAccess.GDBAccessObj.SelectDT(typedTable, strSQL, ATransaction);
return typedTable;
}
/// <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);
}
} // 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);
}
/// <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);
}
private void LoadAll(Int32 ALedgerNumber, 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 = ALedgerNumber;
GLMTemplateRow.Year = AYear;
FGLMpTable = AGeneralLedgerMasterPeriodAccess.LoadViaAGeneralLedgerMasterTemplate(GLMTemplateRow, transaction);
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
}
}
public void T0_Consolidation()
{
// reset the database, so that there is no consolidated budget
CommonNUnitFunctions.ResetDatabase();
string budgetTestFile = TAppSettingsManager.GetValue("GiftBatch.file",
CommonNUnitFunctions.rootPath + "/csharp/ICT/Testing/lib/MFinance/SampleData/BudgetImport-All.csv");
int NumBudgetsUpdated;
int NumFailedRows;
TVerificationResultCollection VerificationResult;
BudgetTDS ImportDS = new BudgetTDS();
string ImportString = File.ReadAllText(budgetTestFile);
// import budget from CSV
decimal RowsImported = TBudgetMaintainWebConnector.ImportBudgets(
FLedgerNumber,
0,
ImportString,
budgetTestFile,
new string[] { ",", "dmy", "American" },
ref ImportDS,
out NumBudgetsUpdated,
out NumFailedRows,
out VerificationResult);
Assert.AreNotEqual(0, RowsImported, "expect to import several rows");
CommonNUnitFunctions.EnsureNullOrOnlyNonCriticalVerificationResults(VerificationResult,
"ImportBudgets has critical errors:");
BudgetTDSAccess.SubmitChanges(ImportDS);
// check for value in budget table
string sqlQueryBudget =
String.Format(
"SELECT {0} FROM PUB_{1}, PUB_{2} WHERE {1}.a_budget_sequence_i = {2}.a_budget_sequence_i AND a_period_number_i = 1 AND " +
"a_ledger_number_i = {3} AND a_revision_i = 0 AND a_year_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300'",
ABudgetPeriodTable.GetBudgetBaseDBName(),
ABudgetTable.GetTableDBName(),
ABudgetPeriodTable.GetTableDBName(),
FLedgerNumber);
decimal budgetValue = Convert.ToDecimal(DBAccess.GDBAccessObj.ExecuteScalar(sqlQueryBudget, IsolationLevel.ReadCommitted));
Assert.AreEqual(250m, budgetValue, "problem with importing budget from CSV");
// check for zero in glmperiod budget: that row does not even exist yet, so check that it does not exist
string sqlQueryCheckEmptyConsolidatedBudget =
String.Format(
"SELECT COUNT(*) FROM PUB_{0}, PUB_{1} WHERE {0}.a_glm_sequence_i = {1}.a_glm_sequence_i AND a_period_number_i = 1 AND " +
"a_ledger_number_i = {2} AND a_year_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300'",
AGeneralLedgerMasterPeriodTable.GetTableDBName(),
AGeneralLedgerMasterTable.GetTableDBName(),
FLedgerNumber);
Assert.AreEqual(0, DBAccess.GDBAccessObj.ExecuteScalar(sqlQueryCheckEmptyConsolidatedBudget,
IsolationLevel.ReadCommitted), "budget should not be consolidated yet");
// consolidate the budget
TBudgetConsolidateWebConnector.LoadBudgetForConsolidate(FLedgerNumber);
TBudgetConsolidateWebConnector.ConsolidateBudgets(FLedgerNumber, true);
// check for correct value in glmperiod budget
string sqlQueryConsolidatedBudget =
String.Format(
"SELECT {0} FROM PUB_{1}, PUB_{2} WHERE {1}.a_glm_sequence_i = {2}.a_glm_sequence_i AND a_period_number_i = 1 AND " +
"a_ledger_number_i = {3} AND a_year_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300'",
AGeneralLedgerMasterPeriodTable.GetBudgetBaseDBName(),
AGeneralLedgerMasterPeriodTable.GetTableDBName(),
AGeneralLedgerMasterTable.GetTableDBName(),
FLedgerNumber);
decimal consolidatedBudgetValue =
Convert.ToDecimal(DBAccess.GDBAccessObj.ExecuteScalar(sqlQueryConsolidatedBudget, IsolationLevel.ReadCommitted));
Assert.AreEqual(250m, consolidatedBudgetValue, "budget should now be consolidated");
// TODO: also check some summary account and cost centre for summed up budget values
// check how reposting a budget works
string sqlChangeBudget = String.Format("UPDATE PUB_{0} SET {1} = 44 WHERE a_period_number_i = 1 AND " +
"EXISTS (SELECT * FROM PUB_{2} WHERE {0}.a_budget_sequence_i = {2}.a_budget_sequence_i AND a_ledger_number_i = {3} " +
"AND a_year_i = 0 AND a_revision_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300')",
ABudgetPeriodTable.GetTableDBName(),
ABudgetPeriodTable.GetBudgetBaseDBName(),
ABudgetTable.GetTableDBName(),
FLedgerNumber);
bool SubmissionOK = true;
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.BeginAutoTransaction(IsolationLevel.Serializable, ref Transaction, ref SubmissionOK,
delegate
{
DBAccess.GDBAccessObj.ExecuteNonQuery(sqlChangeBudget, Transaction);
});
// post all budgets again
TBudgetConsolidateWebConnector.LoadBudgetForConsolidate(FLedgerNumber);
TBudgetConsolidateWebConnector.ConsolidateBudgets(FLedgerNumber, true);
consolidatedBudgetValue =
Convert.ToDecimal(DBAccess.GDBAccessObj.ExecuteScalar(sqlQueryConsolidatedBudget, IsolationLevel.ReadCommitted));
Assert.AreEqual(44.0m, consolidatedBudgetValue, "budget should be consolidated with the new value");
// post only a modified budget (testing UnPostBudget)
sqlChangeBudget = String.Format("UPDATE PUB_{0} SET {1} = 65 WHERE a_period_number_i = 1 AND " +
"EXISTS (SELECT * FROM PUB_{2} WHERE {0}.a_budget_sequence_i = {2}.a_budget_sequence_i AND a_ledger_number_i = {3} " +
"AND a_year_i = 0 AND a_revision_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300')",
ABudgetPeriodTable.GetTableDBName(),
ABudgetPeriodTable.GetBudgetBaseDBName(),
ABudgetTable.GetTableDBName(),
FLedgerNumber);
string sqlMarkBudgetForConsolidation = String.Format("UPDATE PUB_{0} SET {1} = false WHERE " +
"a_ledger_number_i = {2} " +
"AND a_year_i = 0 AND a_revision_i = 0 AND a_account_code_c = '0300' AND a_cost_centre_code_c = '4300'",
ABudgetTable.GetTableDBName(),
ABudgetTable.GetBudgetStatusDBName(),
FLedgerNumber);
SubmissionOK = true;
Transaction = null;
DBAccess.GDBAccessObj.BeginAutoTransaction(IsolationLevel.Serializable, ref Transaction, ref SubmissionOK,
delegate
{
DBAccess.GDBAccessObj.ExecuteNonQuery(sqlChangeBudget, Transaction);
DBAccess.GDBAccessObj.ExecuteNonQuery(sqlMarkBudgetForConsolidation, Transaction);
});
// post only modified budget again
TBudgetConsolidateWebConnector.LoadBudgetForConsolidate(FLedgerNumber);
TBudgetConsolidateWebConnector.ConsolidateBudgets(FLedgerNumber, false);
consolidatedBudgetValue =
Convert.ToDecimal(DBAccess.GDBAccessObj.ExecuteScalar(sqlQueryConsolidatedBudget, IsolationLevel.ReadCommitted));
Assert.AreEqual(65.0m, consolidatedBudgetValue, "budget should be consolidated with the new value, after UnPostBudget");
// TODO: test forwarding periods. what happens to next year values, when there is no next year glm record yet?
}
public static TSubmitChangesResult SaveLedgerSettings(
Int32 ALedgerNumber,
DateTime ACalendarStartDate,
ref GLSetupTDS AInspectDS)
{
#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);
}
else if (AInspectDS == null)
{
return TSubmitChangesResult.scrNothingToBeSaved;
}
#endregion Validate Arguments
ALedgerTable LedgerTable;
ALedgerRow LedgerRow;
AAccountingPeriodTable AccountingPeriodTable;
AAccountingPeriodRow AccountingPeriodRow;
AAccountingPeriodTable NewAccountingPeriodTable;
AAccountingPeriodRow NewAccountingPeriodRow;
AGeneralLedgerMasterTable GLMTable;
AGeneralLedgerMasterRow GLMRow;
AGeneralLedgerMasterPeriodTable GLMPeriodTable;
AGeneralLedgerMasterPeriodTable TempGLMPeriodTable;
AGeneralLedgerMasterPeriodTable NewGLMPeriodTable;
AGeneralLedgerMasterPeriodRow GLMPeriodRow;
AGeneralLedgerMasterPeriodRow TempGLMPeriodRow;
AGeneralLedgerMasterPeriodRow NewGLMPeriodRow;
int CurrentNumberPeriods;
int NewNumberPeriods;
int CurrentNumberFwdPostingPeriods;
int NewNumberFwdPostingPeriods;
int CurrentLastFwdPeriod;
int NewLastFwdPeriod;
int Period;
Boolean ExtendFwdPeriods = false;
DateTime PeriodStartDate;
DateTime CurrentCalendarStartDate;
Boolean CreateCalendar = false;
TDBTransaction Transaction = null;
bool SubmissionOK = false;
GLSetupTDS InspectDS = AInspectDS;
try
{
DBAccess.GDBAccessObj.GetNewOrExistingAutoTransaction(IsolationLevel.Serializable,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
ref SubmissionOK,
delegate
{
// load ledger row currently saved in database so it can be used for comparison with modified data
LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction);
#region Validate Data
if ((LedgerTable == null) || (LedgerTable.Count == 0))
{
throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString(
"Function:{0} - Ledger data for Ledger number {1} does not exist or could not be accessed!"),
Utilities.GetMethodName(true),
ALedgerNumber));
}
#endregion Validate Data
LedgerRow = (ALedgerRow)LedgerTable.Rows[0];
if (InspectDS.ALedger != null)
{
// initialize variables for accounting periods and forward periods
CurrentNumberPeriods = LedgerRow.NumberOfAccountingPeriods;
NewNumberPeriods = ((ALedgerRow)(InspectDS.ALedger.Rows[0])).NumberOfAccountingPeriods;
CurrentNumberFwdPostingPeriods = LedgerRow.NumberFwdPostingPeriods;
NewNumberFwdPostingPeriods = ((ALedgerRow)(InspectDS.ALedger.Rows[0])).NumberFwdPostingPeriods;
// retrieve currently saved calendar start date (start date of financial year)
AAccountingPeriodTable CalendarTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber, 1, Transaction);
CurrentCalendarStartDate = DateTime.MinValue;
if (CalendarTable.Count > 0)
{
CurrentCalendarStartDate = ((AAccountingPeriodRow)CalendarTable.Rows[0]).PeriodStartDate;
}
// update accounting periods (calendar):
// this only needs to be done if the calendar mode is changed
// or if calendar mode is monthly and the start date has changed
// or if not monthly and number of periods has changed
if (((ALedgerRow)(InspectDS.ALedger.Rows[0])).CalendarMode != LedgerRow.CalendarMode)
{
CreateCalendar = true;
}
else if (((ALedgerRow)(InspectDS.ALedger.Rows[0])).CalendarMode
&& (ACalendarStartDate != CurrentCalendarStartDate))
{
CreateCalendar = true;
}
else if (!((ALedgerRow)(InspectDS.ALedger.Rows[0])).CalendarMode
&& (NewNumberPeriods != CurrentNumberPeriods))
{
CreateCalendar = true;
}
if (!CreateCalendar
&& (NewNumberFwdPostingPeriods < CurrentNumberFwdPostingPeriods))
{
CreateCalendar = true;
}
if (!CreateCalendar
&& (NewNumberFwdPostingPeriods > CurrentNumberFwdPostingPeriods))
{
// in this case only extend the periods (as there may already be existing transactions)
ExtendFwdPeriods = true;
}
// now perform the actual update of accounting periods (calendar)
if (CreateCalendar)
{
// first make sure all accounting period records are deleted
if (AAccountingPeriodAccess.CountViaALedger(ALedgerNumber, Transaction) > 0)
{
AAccountingPeriodTable TemplateTable = new AAccountingPeriodTable();
AAccountingPeriodRow TemplateRow = TemplateTable.NewRowTyped(false);
TemplateRow.LedgerNumber = ALedgerNumber;
AAccountingPeriodAccess.DeleteUsingTemplate(TemplateRow, null, Transaction);
}
// now create all accounting period records according to monthly calendar mode
// (at the same time create forwarding periods. If number of forwarding periods also
// changes with this saving method then this will be dealt with further down in the code)
NewAccountingPeriodTable = new AAccountingPeriodTable();
PeriodStartDate = ACalendarStartDate;
for (Period = 1; Period <= NewNumberPeriods; Period++)
{
NewAccountingPeriodRow = NewAccountingPeriodTable.NewRowTyped();
NewAccountingPeriodRow.LedgerNumber = ALedgerNumber;
NewAccountingPeriodRow.AccountingPeriodNumber = Period;
NewAccountingPeriodRow.PeriodStartDate = PeriodStartDate;
//TODO: Calendar vs Financial Date Handling - Check for current ledger number of periods
if ((((ALedgerRow)(InspectDS.ALedger.Rows[0])).NumberOfAccountingPeriods == 13)
&& (Period == 12))
{
// in case of 12 periods the second last period represents the last month except for the very last day
NewAccountingPeriodRow.PeriodEndDate = PeriodStartDate.AddMonths(1).AddDays(-2);
}
else if ((((ALedgerRow)(InspectDS.ALedger.Rows[0])).NumberOfAccountingPeriods == 13)
&& (Period == 13))
{
// in case of 13 periods the last period just represents the very last day of the financial year
NewAccountingPeriodRow.PeriodEndDate = PeriodStartDate;
}
else
{
NewAccountingPeriodRow.PeriodEndDate = PeriodStartDate.AddMonths(1).AddDays(-1);
}
NewAccountingPeriodRow.AccountingPeriodDesc = PeriodStartDate.ToString("MMMM");
NewAccountingPeriodTable.Rows.Add(NewAccountingPeriodRow);
PeriodStartDate = NewAccountingPeriodRow.PeriodEndDate.AddDays(1);
}
AAccountingPeriodAccess.SubmitChanges(NewAccountingPeriodTable, Transaction);
TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
TCacheableFinanceTablesEnum.AccountingPeriodList.ToString());
CurrentNumberPeriods = NewNumberPeriods;
}
// check if any new forwarding periods need to be created
if (CreateCalendar || ExtendFwdPeriods)
{
// now create new forwarding posting periods (if at all needed)
NewAccountingPeriodTable = new AAccountingPeriodTable();
// if calendar was created then there are no forward periods yet
if (CreateCalendar)
{
Period = CurrentNumberPeriods + 1;
}
else
{
Period = CurrentNumberPeriods + CurrentNumberFwdPostingPeriods + 1;
}
while (Period <= NewNumberPeriods + NewNumberFwdPostingPeriods)
{
AccountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber,
Period - CurrentNumberPeriods,
Transaction);
AccountingPeriodRow = (AAccountingPeriodRow)AccountingPeriodTable.Rows[0];
NewAccountingPeriodRow = NewAccountingPeriodTable.NewRowTyped();
NewAccountingPeriodRow.LedgerNumber = ALedgerNumber;
NewAccountingPeriodRow.AccountingPeriodNumber = Period;
NewAccountingPeriodRow.AccountingPeriodDesc = AccountingPeriodRow.AccountingPeriodDesc;
NewAccountingPeriodRow.PeriodStartDate = AccountingPeriodRow.PeriodStartDate.AddYears(1);
NewAccountingPeriodRow.PeriodEndDate = AccountingPeriodRow.PeriodEndDate.AddYears(1);
NewAccountingPeriodTable.Rows.Add(NewAccountingPeriodRow);
Period++;
}
AAccountingPeriodAccess.SubmitChanges(NewAccountingPeriodTable, Transaction);
TCacheableTablesManager.GCacheableTablesManager.MarkCachedTableNeedsRefreshing(
TCacheableFinanceTablesEnum.AccountingPeriodList.ToString());
// also create new general ledger master periods with balances
CurrentLastFwdPeriod = LedgerRow.NumberOfAccountingPeriods + CurrentNumberFwdPostingPeriods;
NewLastFwdPeriod = LedgerRow.NumberOfAccountingPeriods + NewNumberFwdPostingPeriods;
// TODO: the following 2 lines would need to replace the 2 lines above if not all possible forward periods are created initially
//CurrentLastFwdPeriod = LedgerRow.CurrentPeriod + CurrentNumberFwdPostingPeriods;
//NewLastFwdPeriod = LedgerRow.CurrentPeriod + NewNumberFwdPostingPeriods;
GLMTable = new AGeneralLedgerMasterTable();
AGeneralLedgerMasterRow template = GLMTable.NewRowTyped(false);
template.LedgerNumber = ALedgerNumber;
template.Year = LedgerRow.CurrentFinancialYear;
// find all general ledger master records of the current financial year for given ledger
GLMTable = AGeneralLedgerMasterAccess.LoadUsingTemplate(template, Transaction);
NewGLMPeriodTable = new AGeneralLedgerMasterPeriodTable();
foreach (DataRow Row in GLMTable.Rows)
{
// for each of the general ledger master records of the current financial year set the
// new, extended forwarding glm period records (most likely they will not exist yet
// but if they do then update values)
GLMRow = (AGeneralLedgerMasterRow)Row;
GLMPeriodTable =
AGeneralLedgerMasterPeriodAccess.LoadByPrimaryKey(GLMRow.GlmSequence, CurrentLastFwdPeriod, Transaction);
if (GLMPeriodTable.Count > 0)
{
GLMPeriodRow = (AGeneralLedgerMasterPeriodRow)GLMPeriodTable.Rows[0];
for (Period = CurrentLastFwdPeriod + 1; Period <= NewLastFwdPeriod; Period++)
{
if (AGeneralLedgerMasterPeriodAccess.Exists(GLMPeriodRow.GlmSequence, Period, Transaction))
{
// if the record already exists then just change values
TempGLMPeriodTable = AGeneralLedgerMasterPeriodAccess.LoadByPrimaryKey(GLMPeriodRow.GlmSequence,
Period,
Transaction);
TempGLMPeriodRow = (AGeneralLedgerMasterPeriodRow)TempGLMPeriodTable.Rows[0];
TempGLMPeriodRow.ActualBase = GLMPeriodRow.ActualBase;
TempGLMPeriodRow.ActualIntl = GLMPeriodRow.ActualIntl;
if (!GLMPeriodRow.IsActualForeignNull())
{
TempGLMPeriodRow.ActualForeign = GLMPeriodRow.ActualForeign;
}
else
{
TempGLMPeriodRow.SetActualForeignNull();
}
NewGLMPeriodTable.Merge(TempGLMPeriodTable, true);
}
else
{
// add new row since it does not exist yet
NewGLMPeriodRow = NewGLMPeriodTable.NewRowTyped();
NewGLMPeriodRow.GlmSequence = GLMPeriodRow.GlmSequence;
NewGLMPeriodRow.PeriodNumber = Period;
NewGLMPeriodRow.ActualBase = GLMPeriodRow.ActualBase;
NewGLMPeriodRow.ActualIntl = GLMPeriodRow.ActualIntl;
if (!GLMPeriodRow.IsActualForeignNull())
{
NewGLMPeriodRow.ActualForeign = GLMPeriodRow.ActualForeign;
}
else
{
NewGLMPeriodRow.SetActualForeignNull();
}
NewGLMPeriodTable.Rows.Add(NewGLMPeriodRow);
}
}
// remove periods if the number of periods + forwarding periods has been reduced
int NumberOfExistingPeriods = LedgerRow.NumberOfAccountingPeriods + LedgerRow.NumberFwdPostingPeriods;
while ((NewNumberPeriods + NewNumberFwdPostingPeriods) < NumberOfExistingPeriods)
{
AGeneralLedgerMasterPeriodAccess.DeleteByPrimaryKey(GLMPeriodRow.GlmSequence,
NumberOfExistingPeriods,
Transaction);
NumberOfExistingPeriods--;
}
}
}
// just one SubmitChanges for all records needed
AGeneralLedgerMasterPeriodAccess.SubmitChanges(NewGLMPeriodTable, Transaction);
}
}
// update a_ledger_init_flag records for:
// suspense account flag: "SUSP-ACCT"
// budget flag: "BUDGET"
// base currency: "CURRENCY"
// international currency: "INTL-CURRENCY" (this is a new flag for OpenPetra)
// current period (start of ledger date): CURRENT-PERIOD
// calendar settings: CAL
AddOrRemoveLedgerInitFlag(ALedgerNumber, MFinanceConstants.LEDGER_INIT_FLAG_SUSP_ACC, LedgerRow.SuspenseAccountFlag,
Transaction);
AddOrRemoveLedgerInitFlag(ALedgerNumber, MFinanceConstants.LEDGER_INIT_FLAG_BUDGET, LedgerRow.BudgetControlFlag, Transaction);
AddOrRemoveLedgerInitFlag(ALedgerNumber, MFinanceConstants.LEDGER_INIT_FLAG_CURRENCY, !LedgerRow.IsBaseCurrencyNull(),
Transaction);
AddOrRemoveLedgerInitFlag(ALedgerNumber, MFinanceConstants.LEDGER_INIT_FLAG_INTL_CURRENCY, !LedgerRow.IsIntlCurrencyNull(),
Transaction);
AddOrRemoveLedgerInitFlag(ALedgerNumber, MFinanceConstants.LEDGER_INIT_FLAG_CURRENT_PERIOD, !LedgerRow.IsCurrentPeriodNull(),
Transaction);
AddOrRemoveLedgerInitFlag(ALedgerNumber, MFinanceConstants.LEDGER_INIT_FLAG_CAL, !LedgerRow.IsNumberOfAccountingPeriodsNull(),
Transaction);
GLSetupTDSAccess.SubmitChanges(InspectDS);
SubmissionOK = true;
});
}
catch (Exception ex)
{
TLogging.Log(String.Format("Method:{0} - Unexpected error!{1}{1}{2}",
Utilities.GetMethodSignature(),
Environment.NewLine,
ex.Message));
throw ex;
}
return TSubmitChangesResult.scrOK;
}
/// <summary>
/// Load all GLMP rows for this Cost Centre in this period
/// </summary>
/// <returns></returns>
public Boolean LoadByCostCentreAccountPeriod(String ACostCentreCode, String AAccountCode, Int32 AYear, Int32 APeriod)
{
Boolean LoadedOk = false;
TDBTransaction transaction = null;
FGLMpTable = new AGeneralLedgerMasterPeriodTable();
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum, ref transaction,
delegate
{
DBAccess.GDBAccessObj.SelectDT(
FGLMpTable,
"SELECT a_general_ledger_master_period.* FROM" +
" a_general_ledger_master_period, a_general_ledger_master" +
" WHERE" +
" a_general_ledger_master_period.a_glm_sequence_i=a_general_ledger_master.a_glm_sequence_i" +
" AND a_general_ledger_master.a_ledger_number_i = " + FLedgerNumber +
" AND a_general_ledger_master.a_year_i = " + AYear +
" AND a_general_ledger_master.a_account_code_c = '" + AAccountCode + "'" +
" AND a_general_ledger_master.a_cost_centre_code_c = '" + ACostCentreCode + "'" +
" AND a_general_ledger_master_period.a_period_number_i=" + APeriod,
transaction);
LoadedOk = (FGLMpTable.Rows.Count > 0);
}); // Get NewOrExisting AutoReadTransaction
FGLMpRow = (LoadedOk) ? FGLMpTable[0] : null;
return LoadedOk;
}
public static bool LoadBudgetForConsolidate(Int32 ALedgerNumber)
{
FBudgetTDS = new BudgetTDS();
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
ALedgerAccess.LoadByPrimaryKey(FBudgetTDS, ALedgerNumber, Transaction);
string sqlLoadBudgetForThisAndNextYear =
string.Format("SELECT * FROM PUB_{0} WHERE {1}=? AND ({2} = ? OR {2} = ?)",
ABudgetTable.GetTableDBName(),
ABudgetTable.GetLedgerNumberDBName(),
ABudgetTable.GetYearDBName());
List <OdbcParameter> parameters = new List <OdbcParameter>();
OdbcParameter param = new OdbcParameter("ledgernumber", OdbcType.Int);
param.Value = ALedgerNumber;
parameters.Add(param);
param = new OdbcParameter("thisyear", OdbcType.Int);
param.Value = FBudgetTDS.ALedger[0].CurrentFinancialYear;
parameters.Add(param);
param = new OdbcParameter("nextyear", OdbcType.Int);
param.Value = FBudgetTDS.ALedger[0].CurrentFinancialYear + 1;
parameters.Add(param);
DBAccess.GDBAccessObj.Select(FBudgetTDS, sqlLoadBudgetForThisAndNextYear, FBudgetTDS.ABudget.TableName, Transaction,
parameters.ToArray());
string sqlLoadBudgetPeriodForThisAndNextYear =
string.Format("SELECT {0}.* FROM PUB_{0}, PUB_{1} WHERE {0}.a_budget_sequence_i = {1}.a_budget_sequence_i AND " +
"{2}=? AND ({3} = ? OR {3} = ?)",
ABudgetPeriodTable.GetTableDBName(),
ABudgetTable.GetTableDBName(),
ABudgetTable.GetLedgerNumberDBName(),
ABudgetTable.GetYearDBName());
DBAccess.GDBAccessObj.Select(FBudgetTDS,
sqlLoadBudgetPeriodForThisAndNextYear,
FBudgetTDS.ABudgetPeriod.TableName,
Transaction,
parameters.ToArray());
// Accept row changes here so that the Client gets 'unmodified' rows
FBudgetTDS.AcceptChanges();
GLPostingDS = new GLPostingTDS();
AAccountAccess.LoadViaALedger(GLPostingDS, ALedgerNumber, Transaction);
AAccountHierarchyDetailAccess.LoadViaALedger(GLPostingDS, ALedgerNumber, Transaction);
ACostCentreAccess.LoadViaALedger(GLPostingDS, ALedgerNumber, Transaction);
ALedgerAccess.LoadByPrimaryKey(GLPostingDS, ALedgerNumber, Transaction);
// get the glm sequences for this year and next year
for (int i = 0; i <= 1; i++)
{
int Year = GLPostingDS.ALedger[0].CurrentFinancialYear + i;
AGeneralLedgerMasterRow TemplateRow = (AGeneralLedgerMasterRow)GLPostingDS.AGeneralLedgerMaster.NewRowTyped(false);
TemplateRow.LedgerNumber = ALedgerNumber;
TemplateRow.Year = Year;
GLPostingDS.AGeneralLedgerMaster.Merge(AGeneralLedgerMasterAccess.LoadUsingTemplate(TemplateRow, Transaction));
}
string sqlLoadGlmperiodForThisAndNextYear =
string.Format("SELECT {0}.* FROM PUB_{0}, PUB_{1} WHERE {0}.a_glm_sequence_i = {1}.a_glm_sequence_i AND " +
"{2}=? AND ({3} = ? OR {3} = ?)",
AGeneralLedgerMasterPeriodTable.GetTableDBName(),
AGeneralLedgerMasterTable.GetTableDBName(),
AGeneralLedgerMasterTable.GetLedgerNumberDBName(),
AGeneralLedgerMasterTable.GetYearDBName());
DBAccess.GDBAccessObj.Select(GLPostingDS,
sqlLoadGlmperiodForThisAndNextYear,
GLPostingDS.AGeneralLedgerMasterPeriod.TableName,
Transaction,
parameters.ToArray());
});
GLPostingDS.AcceptChanges();
return(true);
}
/// <summary>
/// export all GL Balances in the given year, towards the specified cost centres
/// </summary>
public static void ExportGLBalances(string AOutputPath,
char ACSVSeparator,
string ANewLine,
Int32 ALedgerNumber,
Int32 AFinancialYear,
string ACostCentres,
string AIgnoreAccounts)
{
string filename = Path.GetFullPath(Path.Combine(AOutputPath, "balance.csv"));
Console.WriteLine("Writing file: " + filename);
string sql =
String.Format("SELECT GLM.{1} AS CostCentre, GLM.{0} AS Account, {2} AS StartBalance, GLMP.{3} AS EndBalance " +
"FROM PUB_{4} AS GLM, PUB_{10} AS A, PUB_{13} AS GLMP " +
"WHERE GLM.{5} = {6} AND {7} = {8} AND GLM.{1} IN ({9}) " +
"AND GLMP.{14} = GLM.{14} AND GLMP.{15} = 12 " +
"AND A.{5} = GLM.{5} AND A.{0} = GLM.{0} AND " +
"A.{11}=true AND NOT GLM.{0} IN ({12})" +
"ORDER BY GLM.{1}, GLM.{0}",
AGeneralLedgerMasterTable.GetAccountCodeDBName(),
AGeneralLedgerMasterTable.GetCostCentreCodeDBName(),
AGeneralLedgerMasterTable.GetStartBalanceBaseDBName(),
AGeneralLedgerMasterPeriodTable.GetActualBaseDBName(),
AGeneralLedgerMasterTable.GetTableDBName(),
AGeneralLedgerMasterTable.GetLedgerNumberDBName(),
ALedgerNumber,
AGeneralLedgerMasterTable.GetYearDBName(),
AFinancialYear,
"'" + ACostCentres.Replace(",", "','") + "'",
AAccountTable.GetTableDBName(),
AAccountTable.GetPostingStatusDBName(),
"'" + AIgnoreAccounts.Replace(",", "','") + "'",
AGeneralLedgerMasterPeriodTable.GetTableDBName(),
AGeneralLedgerMasterPeriodTable.GetGlmSequenceDBName(),
AGeneralLedgerMasterPeriodTable.GetPeriodNumberDBName());
TDBTransaction Transaction = new TDBTransaction();
DataTable balances = null;
DBAccess.ReadTransaction(ref Transaction,
delegate
{
balances = Transaction.DataBaseObj.SelectDT(sql, "balances", Transaction);
});
StringBuilder sb = new StringBuilder();
if (balances != null)
{
foreach (DataRow row in balances.Rows)
{
sb.Append(StringHelper.StrMerge(
new string[] {
row["CostCentre"].ToString(),
row["Account"].ToString(),
String.Format("{0:N}", Convert.ToDecimal(row["StartBalance"])),
String.Format("{0:N}", Convert.ToDecimal(row["EndBalance"]))
}, ACSVSeparator));
sb.Append(ANewLine);
}
}
StreamWriter sw = new StreamWriter(filename, false, Encoding.GetEncoding(1252));
sw.Write(sb.ToString());
sw.Close();
}
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));
}
}
}
