/// <summary>
/// creates the rows for the whole current year in AGeneralLedgerMaster and AGeneralLedgerMasterPeriod for an Account/CostCentre combination
/// </summary>
/// <param name="AMainDS"></param>
/// <param name="ALedgerNumber"></param>
/// <param name="AAccountCode"></param>
/// <param name="ACostCentreCode"></param>
/// <returns>The new glm sequence, which is negative until SubmitChanges</returns>
private static Int32 CreateGLMYear(
ref GLPostingTDS AMainDS,
Int32 ALedgerNumber,
string AAccountCode,
string ACostCentreCode)
{
ALedgerRow Ledger = AMainDS.ALedger[0];
AGeneralLedgerMasterRow GLMRow = AMainDS.AGeneralLedgerMaster.NewRowTyped();
// row.GlmSequence will be set by SubmitChanges
GLMRow.GlmSequence = (AMainDS.AGeneralLedgerMaster.Rows.Count * -1) - 1;
GLMRow.LedgerNumber = ALedgerNumber;
GLMRow.Year = Ledger.CurrentFinancialYear;
GLMRow.AccountCode = AAccountCode;
GLMRow.CostCentreCode = ACostCentreCode;
AMainDS.AGeneralLedgerMaster.Rows.Add(GLMRow);
for (int PeriodCount = 1; PeriodCount < Ledger.NumberOfAccountingPeriods + Ledger.NumberFwdPostingPeriods + 1; PeriodCount++)
{
AGeneralLedgerMasterPeriodRow PeriodRow = AMainDS.AGeneralLedgerMasterPeriod.NewRowTyped();
PeriodRow.GlmSequence = GLMRow.GlmSequence;
PeriodRow.PeriodNumber = PeriodCount;
AMainDS.AGeneralLedgerMasterPeriod.Rows.Add(PeriodRow);
}
return GLMRow.GlmSequence;
}
/// <summary>
/// Write all changes to the database; on failure the whole transaction will be rolled back
/// </summary>
/// <param name="AMainDS"></param>
private static void SubmitChanges(GLPostingTDS AMainDS)
{
TLogging.LogAtLevel(POSTING_LOGLEVEL, "Posting: SubmitChanges...");
GLPostingTDSAccess.SubmitChanges(AMainDS.GetChangesTyped(true));
TLogging.LogAtLevel(POSTING_LOGLEVEL, "Posting: Finished...");
}
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);
}
public static bool GenerateICHStewardshipBatch(int ALedgerNumber,
int APeriodNumber,
ref TVerificationResultCollection AVerificationResult)
{
string StandardCostCentre = TLedgerInfo.GetStandardCostCentre(ALedgerNumber);
bool IsSuccessful = false;
bool DrCrIndicator = true;
bool IncomeDrCrIndicator;
bool ExpenseDrCrIndicator;
bool AccountDrCrIndicator;
string IncomeAccounts = string.Empty;
string ExpenseAccounts = string.Empty;
//Error handling
string ErrorContext = String.Empty;
string ErrorMessage = String.Empty;
//Set default type as non-critical
TResultSeverity ErrorType = TResultSeverity.Resv_Noncritical;
bool NewTransaction = false;
TDBTransaction DBTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.Serializable, out NewTransaction);
//Generating the ICH batch...
try
{
DateTime PeriodStartDate;
DateTime PeriodEndDate;
TFinancialYear.GetStartAndEndDateOfPeriod(ALedgerNumber, APeriodNumber, out PeriodStartDate, out PeriodEndDate, DBTransaction);
String strPeriodStartDate = "#" + PeriodStartDate.ToString("yyyy-MM-dd") + "#";
String strPeriodEndDate = "#" + PeriodEndDate.ToString("yyyy-MM-dd") + "#";
AGiftBatchTable GiftBatchTable = new AGiftBatchTable();
String GiftQuery = "SELECT * FROM a_gift_batch WHERE " +
AGiftBatchTable.GetLedgerNumberDBName() + " = " + ALedgerNumber +
" AND " + AGiftBatchTable.GetBatchStatusDBName() + " = '" + MFinanceConstants.BATCH_POSTED + "'" +
" AND " + AGiftBatchTable.GetGlEffectiveDateDBName() + " >= " + strPeriodStartDate +
" AND " + AGiftBatchTable.GetGlEffectiveDateDBName() + " <= " + strPeriodEndDate +
" ORDER BY " + AGiftBatchTable.GetBatchNumberDBName();
DBAccess.GDBAccessObj.SelectDT(GiftBatchTable, GiftQuery, DBTransaction);
//Create a new batch. If it turns out I don't need one, I can delete it later.
GLBatchTDS MainDS = TGLPosting.CreateABatch(ALedgerNumber, Catalog.GetString("ICH Stewardship"), 0, PeriodEndDate);
ABatchRow NewBatchRow = MainDS.ABatch[0];
int GLBatchNumber = NewBatchRow.BatchNumber;
//Load tables needed: AccountingPeriod, Ledger, Account, Cost Centre, Transaction, Gift Batch, ICHStewardship
GLPostingTDS PostingDS = new GLPostingTDS();
ALedgerAccess.LoadByPrimaryKey(PostingDS, ALedgerNumber, DBTransaction);
AAccountAccess.LoadViaALedger(PostingDS, ALedgerNumber, DBTransaction);
AIchStewardshipAccess.LoadViaALedger(PostingDS, ALedgerNumber, DBTransaction);
AAccountHierarchyAccess.LoadViaALedger(PostingDS, ALedgerNumber, DBTransaction);
ABatchTable BatchTable = new ABatchTable();
ABatchRow BatchTemplateRow = (ABatchRow)BatchTable.NewRowTyped(false);
BatchTemplateRow.LedgerNumber = ALedgerNumber;
BatchTemplateRow.BatchPeriod = APeriodNumber;
StringCollection Operators0 = StringHelper.InitStrArr(new string[] { "=", "=" });
StringCollection OrderList0 = new StringCollection();
OrderList0.Add("ORDER BY");
OrderList0.Add(ABatchTable.GetBatchNumberDBName() + " DESC");
ABatchTable BatchesInAPeriod = ABatchAccess.LoadUsingTemplate(BatchTemplateRow,
Operators0,
null,
DBTransaction,
OrderList0,
0,
0);
if (BatchesInAPeriod != null)
{
int BatchNumber = 0;
for (int i = 0; i < BatchesInAPeriod.Count; i++)
{
ABatchRow batchRow = (ABatchRow)BatchesInAPeriod.Rows[i];
BatchNumber = batchRow.BatchNumber;
AJournalAccess.LoadViaABatch(MainDS, ALedgerNumber, BatchNumber, DBTransaction);
ATransactionAccess.LoadViaABatch(MainDS, ALedgerNumber, BatchNumber, DBTransaction);
}
}
else
{
ErrorContext = Catalog.GetString("Generating the ICH batch");
ErrorMessage =
String.Format(Catalog.GetString("No Batches found to process in Ledger: {0}"),
ALedgerNumber);
ErrorType = TResultSeverity.Resv_Noncritical;
throw new System.InvalidOperationException(ErrorMessage);
}
ALedgerRow LedgerRow = (ALedgerRow)PostingDS.ALedger.Rows[0];
//Create a new journal in the Batch
//Run gl1120o.p
AJournalRow NewJournalRow = MainDS.AJournal.NewRowTyped();
NewJournalRow.LedgerNumber = ALedgerNumber;
NewJournalRow.BatchNumber = GLBatchNumber;
NewJournalRow.JournalNumber = ++NewBatchRow.LastJournal;
NewJournalRow.JournalDescription = NewBatchRow.BatchDescription;
NewJournalRow.SubSystemCode = MFinanceConstants.SUB_SYSTEM_GL;
NewJournalRow.TransactionTypeCode = CommonAccountingTransactionTypesEnum.STD.ToString();
NewJournalRow.TransactionCurrency = LedgerRow.BaseCurrency;
NewJournalRow.ExchangeRateToBase = 1;
NewJournalRow.DateEffective = PeriodEndDate;
NewJournalRow.JournalPeriod = APeriodNumber;
MainDS.AJournal.Rows.Add(NewJournalRow);
int GLJournalNumber = NewJournalRow.JournalNumber;
int GLTransactionNumber = NewJournalRow.LastTransactionNumber + 1;
// ***************************
// Generate the transactions
// ***************************
AAccountRow AccountRow = (AAccountRow)PostingDS.AAccount.Rows.Find(new object[] { ALedgerNumber, MFinanceConstants.INCOME_HEADING });
//Process income accounts
if (AccountRow != null)
{
IncomeDrCrIndicator = AccountRow.DebitCreditIndicator;
}
else
{
ErrorContext = Catalog.GetString("Generating the ICH batch");
ErrorMessage =
String.Format(Catalog.GetString("Income Account header: '{1}' not found in the accounts table for Ledger: {0}."),
ALedgerNumber,
MFinanceConstants.INCOME_HEADING);
ErrorType = TResultSeverity.Resv_Noncritical;
throw new System.InvalidOperationException(ErrorMessage);
}
BuildChildAccountList(ALedgerNumber,
AccountRow,
DBTransaction,
ref IncomeAccounts,
ref AVerificationResult);
//Process expense accounts
AccountRow = (AAccountRow)PostingDS.AAccount.Rows.Find(new object[] { ALedgerNumber, MFinanceConstants.EXPENSE_HEADING });
if (AccountRow != null)
{
ExpenseDrCrIndicator = AccountRow.DebitCreditIndicator;
}
else
{
ErrorContext = Catalog.GetString("Generating the ICH batch");
ErrorMessage =
String.Format(Catalog.GetString("Expense Account header: '{1}' not found in the accounts table for Ledger: {0}."),
ALedgerNumber,
MFinanceConstants.EXPENSE_HEADING);
ErrorType = TResultSeverity.Resv_Noncritical;
throw new System.InvalidOperationException(ErrorMessage);
}
BuildChildAccountList(ALedgerNumber,
AccountRow,
DBTransaction,
ref ExpenseAccounts,
ref AVerificationResult);
//Process P&L accounts
AccountRow = (AAccountRow)PostingDS.AAccount.Rows.Find(new object[] { ALedgerNumber, MFinanceConstants.PROFIT_AND_LOSS_HEADING });
if (AccountRow != null)
{
AccountDrCrIndicator = AccountRow.DebitCreditIndicator;
}
else
{
ErrorContext = Catalog.GetString("Generating the ICH batch");
ErrorMessage =
String.Format(Catalog.GetString("Profit & Loss Account header: '{1}' not found in the accounts table for Ledger: {0}."),
ALedgerNumber,
MFinanceConstants.PROFIT_AND_LOSS_HEADING);
ErrorType = TResultSeverity.Resv_Noncritical;
throw new System.InvalidOperationException(ErrorMessage);
}
// find out the stewardship number - Ln 275
// Increment the Last ICH No.
int ICHProcessing = ++LedgerRow.LastIchNumber;
decimal ICHTotal = 0;
bool PostICHBatch = false;
ACostCentreRow CCTemplateRow = PostingDS.ACostCentre.NewRowTyped(false);
CCTemplateRow.LedgerNumber = ALedgerNumber;
CCTemplateRow.PostingCostCentreFlag = true;
CCTemplateRow.CostCentreType = MFinanceConstants.FOREIGN_CC_TYPE;
ACostCentreAccess.LoadUsingTemplate(PostingDS, CCTemplateRow, DBTransaction);
//Iterate through the cost centres
// string OrderBy = ACostCentreTable.GetCostCentreCodeDBName();
StringDictionary DestinationAccount = GetDestinationAccountCodes(ALedgerNumber, PostingDS.ACostCentre, DBTransaction);
AIchStewardshipTable ICHStewardshipTable = new AIchStewardshipTable();
Boolean NonIchTransactionsIncluded = false;
String JournalRowOrder = "a_journal_number_i";
String TransRowOrder = "a_batch_number_i,a_journal_number_i,a_transaction_number_i";
foreach (ACostCentreRow CostCentreRow in PostingDS.ACostCentre.Rows)
{
string CostCentre = CostCentreRow.CostCentreCode;
//Initialise values for each Cost Centre
decimal SettlementAmount = 0;
decimal IncomeAmount = 0;
decimal ExpenseAmount = 0;
decimal XferAmount = 0;
decimal IncomeAmountIntl = 0;
decimal ExpenseAmountIntl = 0;
decimal XferAmountIntl = 0;
Boolean TransferFound = false;
/* 0008 Go through all of the transactions. Ln:301 */
String WhereClause = "a_cost_centre_code_c = '" + CostCentreRow.CostCentreCode +
"' AND a_transaction_status_l=true AND a_ich_number_i = 0";
DataRow[] FoundTransRows = MainDS.ATransaction.Select(WhereClause, TransRowOrder);
foreach (DataRow UntypedTransRow in FoundTransRows)
{
ATransactionRow TransRow = (ATransactionRow)UntypedTransRow;
DataRow[] FoundJournalRows = MainDS.AJournal.Select(
"a_batch_number_i = " + TransRow.BatchNumber + " AND a_journal_number_i = " + TransRow.JournalNumber,
JournalRowOrder);
if (FoundJournalRows != null)
{
TransferFound = true;
PostICHBatch = true;
TransRow.IchNumber = ICHProcessing;
if (TransRow.DebitCreditIndicator == AccountDrCrIndicator)
{
SettlementAmount -= TransRow.AmountInBaseCurrency;
}
else
{
SettlementAmount += TransRow.AmountInBaseCurrency;
}
//Process Income (ln:333)
if (IncomeAccounts.Contains(TransRow.AccountCode))
{
if (TransRow.DebitCreditIndicator == IncomeDrCrIndicator)
{
IncomeAmount += TransRow.AmountInBaseCurrency;
IncomeAmountIntl += TransRow.AmountInIntlCurrency;
}
else
{
IncomeAmount -= TransRow.AmountInBaseCurrency;
IncomeAmountIntl -= TransRow.AmountInIntlCurrency;
}
}
//process expenses
if (ExpenseAccounts.Contains(TransRow.AccountCode)
&& (TransRow.AccountCode != MFinanceConstants.DIRECT_XFER_ACCT)
&& (TransRow.AccountCode != MFinanceConstants.ICH_ACCT_SETTLEMENT))
{
if (TransRow.DebitCreditIndicator = ExpenseDrCrIndicator)
{
ExpenseAmount += TransRow.AmountInBaseCurrency;
ExpenseAmountIntl += TransRow.AmountInIntlCurrency;
}
else
{
ExpenseAmount -= TransRow.AmountInBaseCurrency;
ExpenseAmountIntl -= TransRow.AmountInIntlCurrency;
}
}
//Process Direct Transfers
if (TransRow.AccountCode == MFinanceConstants.DIRECT_XFER_ACCT)
{
if (TransRow.DebitCreditIndicator == ExpenseDrCrIndicator)
{
XferAmount += TransRow.AmountInBaseCurrency;
XferAmountIntl += TransRow.AmountInIntlCurrency;
}
else
{
XferAmount -= TransRow.AmountInBaseCurrency;
XferAmountIntl -= TransRow.AmountInIntlCurrency;
}
}
}
} //end of foreach transaction
/* now mark all the gifts as processed */
if (TransferFound)
{
AGiftDetailTable GiftDetailTable = new AGiftDetailTable();
AGiftDetailRow GiftDetailTemplateRow = (AGiftDetailRow)GiftDetailTable.NewRowTyped(false);
GiftDetailTemplateRow.LedgerNumber = ALedgerNumber;
GiftDetailTemplateRow.IchNumber = 0;
GiftDetailTemplateRow.CostCentreCode = CostCentreRow.CostCentreCode;
foreach (AGiftBatchRow GiftBatchRow in GiftBatchTable.Rows)
{
GiftDetailTemplateRow.BatchNumber = GiftBatchRow.BatchNumber;
GiftDetailTable = AGiftDetailAccess.LoadUsingTemplate(GiftDetailTemplateRow, DBTransaction);
foreach (AGiftDetailRow GiftDetailRow in GiftDetailTable.Rows)
{
GiftDetailRow.IchNumber = ICHProcessing;
}
}
} // if TransferFound
if ((SettlementAmount == 0) // If there's no activity in this CC,
&& (IncomeAmount == 0) // bail to the next one.
&& (ExpenseAmount == 0)
&& (XferAmount == 0))
{
continue;
}
/* Balance the cost centre by entering an opposite transaction
* to ICH settlement. Use positive amounts only.
*/
/* Increment or decrement the ICH total to be transferred after this loop.
* NOTE - if this is a "non-ICH fund", I need to balance it separately, and I'll do that right here.
*/
DrCrIndicator = AccountRow.DebitCreditIndicator;
if (DestinationAccount[CostCentreRow.CostCentreCode] == MFinanceConstants.ICH_ACCT_ICH)
{
if (DrCrIndicator == MFinanceConstants.IS_DEBIT)
{
ICHTotal += SettlementAmount;
}
else
{
ICHTotal -= SettlementAmount;
}
}
DrCrIndicator = AccountDrCrIndicator;
if (SettlementAmount < 0)
{
DrCrIndicator = !AccountDrCrIndicator;
SettlementAmount = 0 - SettlementAmount;
}
if ((DestinationAccount[CostCentreRow.CostCentreCode] != MFinanceConstants.ICH_ACCT_ICH) && (SettlementAmount != 0))
{
// I'm creating a transaction right here for this "non-ICH" CostCentre.
// This potentially means that there will be multiple transactions to the "non-ICH" account,
// whereas the ICH account has only a single transaction, but that's not big deal:
if (!TGLPosting.CreateATransaction(MainDS, ALedgerNumber, GLBatchNumber, GLJournalNumber,
Catalog.GetString("Non-ICH foreign fund Clearing"),
DestinationAccount[CostCentreRow.CostCentreCode],
StandardCostCentre, SettlementAmount, PeriodEndDate, !DrCrIndicator, Catalog.GetString("Non-ICH"),
true, SettlementAmount,
out GLTransactionNumber))
{
ErrorContext = Catalog.GetString("Generating the ICH batch");
ErrorMessage =
String.Format(Catalog.GetString("Unable to create a new transaction for Ledger {0}, Batch {1} and Journal {2}."),
ALedgerNumber,
GLBatchNumber,
GLJournalNumber);
ErrorType = TResultSeverity.Resv_Noncritical;
throw new System.InvalidOperationException(ErrorMessage);
}
NonIchTransactionsIncluded = true;
}
/* Generate the transaction to 'balance' the foreign fund -
* in the ICH settlement account.
*/
//RUN gl1130o.p ("new":U,
//Create a transaction
if (SettlementAmount > 0)
{
if (!TGLPosting.CreateATransaction(MainDS, ALedgerNumber, GLBatchNumber, GLJournalNumber,
Catalog.GetString("ICH Monthly Clearing"),
MFinanceConstants.ICH_ACCT_SETTLEMENT, // DestinationAccount[CostCentreRow.CostCentreCode],
CostCentreRow.CostCentreCode, SettlementAmount, PeriodEndDate, DrCrIndicator,
Catalog.GetString("ICH Process"), true, SettlementAmount,
out GLTransactionNumber))
{
ErrorContext = Catalog.GetString("Generating the ICH batch");
ErrorMessage =
String.Format(Catalog.GetString("Unable to create a new transaction for Ledger {0}, Batch {1} and Journal {2}."),
ALedgerNumber,
GLBatchNumber,
GLJournalNumber);
ErrorType = TResultSeverity.Resv_Noncritical;
throw new System.InvalidOperationException(ErrorMessage);
}
//Mark as processed
ATransactionRow TransRow =
(ATransactionRow)MainDS.ATransaction.Rows.Find(new object[] { ALedgerNumber, GLBatchNumber, GLJournalNumber,
GLTransactionNumber });
TransRow.IchNumber = ICHProcessing;
}
/* Now create corresponding report row on stewardship table,
* Only for Cost Centres that cleared to ICH
*/
if ((DestinationAccount[CostCentreRow.CostCentreCode] == MFinanceConstants.ICH_ACCT_ICH)
&& ((IncomeAmount != 0)
|| (ExpenseAmount != 0)
|| (XferAmount != 0)))
{
AIchStewardshipRow ICHStewardshipRow = ICHStewardshipTable.NewRowTyped(true);
//MainDS.Tables.Add(IchStewardshipTable);
ICHStewardshipRow.LedgerNumber = ALedgerNumber;
ICHStewardshipRow.PeriodNumber = APeriodNumber;
ICHStewardshipRow.IchNumber = ICHProcessing;
// ICHStewardshipRow.DateProcessed = DateTime.Today; // This would be strictly correct, but the Stewardship Reporting looks for
ICHStewardshipRow.DateProcessed = PeriodEndDate; // rows using a date filter.
ICHStewardshipRow.CostCentreCode = CostCentreRow.CostCentreCode;
ICHStewardshipRow.IncomeAmount = IncomeAmount;
ICHStewardshipRow.ExpenseAmount = ExpenseAmount;
ICHStewardshipRow.DirectXferAmount = XferAmount;
ICHStewardshipRow.IncomeAmountIntl = IncomeAmountIntl;
ICHStewardshipRow.ExpenseAmountIntl = ExpenseAmountIntl;
ICHStewardshipRow.DirectXferAmountIntl = XferAmountIntl;
ICHStewardshipTable.Rows.Add(ICHStewardshipRow);
}
} // for each cost centre
/* Update the balance of the ICH account (like a bank account).
* If the total is negative, it means the ICH batch has a
* credit total so far. Thus, we now balance it with the opposite
* transaction. */
if (ICHTotal < 0)
{
DrCrIndicator = MFinanceConstants.IS_DEBIT;
ICHTotal = -ICHTotal;
}
else if (ICHTotal > 0)
{
DrCrIndicator = MFinanceConstants.IS_CREDIT;
}
/* 0006 - If the balance is 0 then this is ok
* (eg last minute change of a gift from one field to another)
*/
if ((ICHTotal == 0) && !NonIchTransactionsIncluded)
{
AVerificationResult.Add(new TVerificationResult(Catalog.GetString("Generating the ICH batch"),
Catalog.GetString("No ICH batch was required."), TResultSeverity.Resv_Status));
// An empty GL Batch now exists, which I need to delete.
//
TVerificationResultCollection BatchCancelResult = new TVerificationResultCollection();
TGLPosting.DeleteGLBatch(
ALedgerNumber,
GLBatchNumber,
out BatchCancelResult);
AVerificationResult.AddCollection(BatchCancelResult);
IsSuccessful = true;
}
else
{
if (ICHTotal != 0)
{
//Create a transaction
if (!TGLPosting.CreateATransaction(MainDS, ALedgerNumber, GLBatchNumber, GLJournalNumber,
Catalog.GetString("ICH Monthly Clearing"),
MFinanceConstants.ICH_ACCT_ICH, StandardCostCentre, ICHTotal, PeriodEndDate, DrCrIndicator, Catalog.GetString("ICH"),
true, ICHTotal,
out GLTransactionNumber))
{
ErrorContext = Catalog.GetString("Generating the ICH batch");
ErrorMessage =
String.Format(Catalog.GetString("Unable to create a new transaction for Ledger {0}, Batch {1} and Journal {2}."),
ALedgerNumber,
GLBatchNumber,
GLJournalNumber);
ErrorType = TResultSeverity.Resv_Noncritical;
throw new System.InvalidOperationException(ErrorMessage);
}
}
//Post the batch
if (PostICHBatch)
{
AIchStewardshipAccess.SubmitChanges(ICHStewardshipTable, DBTransaction);
MainDS.ThrowAwayAfterSubmitChanges = true; // SubmitChanges will not return to me any changes made in MainDS.
GLBatchTDSAccess.SubmitChanges(MainDS);
ALedgerAccess.SubmitChanges(PostingDS.ALedger, DBTransaction); // LastIchNumber has changed.
IsSuccessful = TGLPosting.PostGLBatch(ALedgerNumber, GLBatchNumber, out AVerificationResult);
}
else
{
AVerificationResult.Add(new TVerificationResult(ErrorContext,
Catalog.GetString("No Stewardship batch is required."),
TResultSeverity.Resv_Status));
// An empty GL Batch now exists, which I need to delete.
//
TVerificationResultCollection BatchCancelResult = new TVerificationResultCollection();
TGLPosting.DeleteGLBatch(
ALedgerNumber,
GLBatchNumber,
out BatchCancelResult);
AVerificationResult.AddCollection(BatchCancelResult);
} // else
} // else
} // try
catch (ArgumentException Exc)
{
TLogging.Log("An ArgumentException occured during the generation of the Stewardship Batch:" + Environment.NewLine + Exc.ToString());
if (AVerificationResult == null)
{
AVerificationResult = new TVerificationResultCollection();
}
AVerificationResult.Add(new TVerificationResult(ErrorContext, Exc.Message, ErrorType));
throw;
}
catch (InvalidOperationException Exc)
{
TLogging.Log(
"An InvalidOperationException occured during the generation of the Stewardship Batch:" + Environment.NewLine + Exc.ToString());
if (AVerificationResult == null)
{
AVerificationResult = new TVerificationResultCollection();
}
AVerificationResult.Add(new TVerificationResult(ErrorContext, Exc.Message, ErrorType));
throw;
}
catch (Exception Exc)
{
TLogging.Log("An Exception occured during the generation of the Stewardship Batch:" + Environment.NewLine + Exc.ToString());
ErrorContext = Catalog.GetString("Calculate Admin Fee");
ErrorMessage = String.Format(Catalog.GetString("Unknown error while generating the ICH batch for Ledger: {0} and Period: {1}" +
Environment.NewLine + Environment.NewLine + Exc.ToString()),
ALedgerNumber,
APeriodNumber);
ErrorType = TResultSeverity.Resv_Critical;
if (AVerificationResult == null)
{
AVerificationResult = new TVerificationResultCollection();
}
AVerificationResult.Add(new TVerificationResult(ErrorContext, ErrorMessage, ErrorType));
throw;
}
finally
{
if (IsSuccessful && NewTransaction)
{
DBAccess.GDBAccessObj.CommitTransaction();
}
else if (!IsSuccessful && NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
}
return IsSuccessful;
}
/// <summary>
/// for each posting level, propagate the value upwards through both the account and the cost centre hierarchy in glm master;
/// also propagate the value from the posting period through the following periods;
/// </summary>
private static bool SummarizeData(
GLPostingTDS APostingDS,
Int32 AFromPeriod,
ref SortedList <string, TAmount>APostingLevel,
ref SortedList <string, TAccountTreeElement>AAccountTree,
ref SortedList <string, string>ACostCentreTree)
{
if (APostingDS.ALedger[0].ProvisionalYearEndFlag)
{
// If the year end close is running, then we are posting the year end
// reallocations. These appear as part of the final period, but
// should only be written to the forward periods.
// In year end, a_current_period_i = a_number_of_accounting_periods_i = a_batch_period_i.
AFromPeriod++;
}
DataView GLMMasterView = APostingDS.AGeneralLedgerMaster.DefaultView;
GLMMasterView.Sort = AGeneralLedgerMasterTable.GetAccountCodeDBName() + "," + AGeneralLedgerMasterTable.GetCostCentreCodeDBName();
DataView GLMPeriodView = APostingDS.AGeneralLedgerMasterPeriod.DefaultView;
GLMPeriodView.Sort = AGeneralLedgerMasterPeriodTable.GetGlmSequenceDBName() + "," + AGeneralLedgerMasterPeriodTable.GetPeriodNumberDBName();
// Loop through the posting data collected earlier. Summarize it to a
// temporary table, which is much faster than finding and updating records
// in the glm tables multiple times. WriteData will write it to the real
// tables in a single pass.
foreach (string PostingLevelKey in APostingLevel.Keys)
{
string AccountCode = TAmount.GetAccountCode(PostingLevelKey);
string CostCentreCode = TAmount.GetCostCentreCode(PostingLevelKey);
TAmount PostingLevelElement = APostingLevel[PostingLevelKey];
// Combine the summarization trees for both the account and the cost centre.
foreach (string AccountTreeKey in AAccountTree.Keys)
{
if (TAccountTreeElement.GetReportingAccountCode(AccountTreeKey) == AccountCode)
{
string AccountCodeToReportTo = TAccountTreeElement.GetAccountReportToCode(AccountTreeKey);
TAccountTreeElement AccountTreeElement = AAccountTree[AccountTreeKey];
foreach (string CostCentreKey in ACostCentreTree.Keys)
{
if (CostCentreKey.StartsWith(CostCentreCode + ":"))
{
string CostCentreCodeToReportTo = CostCentreKey.Split(':')[1];
decimal SignBaseAmount = PostingLevelElement.baseAmount;
decimal SignTransAmount = PostingLevelElement.transAmount;
// Set the sign of the amounts according to the debit/credit indicator
if (AccountTreeElement.Invert)
{
SignBaseAmount *= -1;
SignTransAmount *= -1;
}
// Find the summary level, creating it if it does not already exist.
int GLMMasterIndex = GLMMasterView.Find(new object[] { AccountCodeToReportTo, CostCentreCodeToReportTo });
if (GLMMasterIndex == -1)
{
CreateGLMYear(
ref APostingDS,
APostingDS.ALedger[0].LedgerNumber,
AccountCodeToReportTo,
CostCentreCodeToReportTo);
GLMMasterIndex = GLMMasterView.Find(new object[] { AccountCodeToReportTo, CostCentreCodeToReportTo });
}
AGeneralLedgerMasterRow GlmRow = (AGeneralLedgerMasterRow)GLMMasterView[GLMMasterIndex].Row;
GlmRow.YtdActualBase += SignBaseAmount;
if (AccountTreeElement.Foreign)
{
if (GlmRow.IsYtdActualForeignNull())
{
GlmRow.YtdActualForeign = SignTransAmount;
}
else
{
GlmRow.YtdActualForeign += SignTransAmount;
}
}
if (APostingDS.ALedger[0].ProvisionalYearEndFlag)
{
GlmRow.ClosingPeriodActualBase += SignBaseAmount;
}
// Add the period data from the posting level to the summary levels
for (Int32 PeriodCount = AFromPeriod;
PeriodCount <= APostingDS.ALedger[0].NumberOfAccountingPeriods + APostingDS.ALedger[0].NumberFwdPostingPeriods;
PeriodCount++)
{
int GLMPeriodIndex = GLMPeriodView.Find(new object[] { GlmRow.GlmSequence, PeriodCount });
AGeneralLedgerMasterPeriodRow GlmPeriodRow;
if (GLMPeriodIndex == -1)
{
GlmPeriodRow = APostingDS.AGeneralLedgerMasterPeriod.NewRowTyped();
GlmPeriodRow.GlmSequence = GlmRow.GlmSequence;
GlmPeriodRow.PeriodNumber = PeriodCount;
APostingDS.AGeneralLedgerMasterPeriod.Rows.Add(GlmPeriodRow);
}
else
{
GlmPeriodRow = (AGeneralLedgerMasterPeriodRow)GLMPeriodView[GLMPeriodIndex].Row;
}
GlmPeriodRow.ActualBase += SignBaseAmount;
if (AccountTreeElement.Foreign)
{
if (GlmPeriodRow.IsActualForeignNull())
{
GlmPeriodRow.ActualForeign = SignTransAmount;
}
else
{
GlmPeriodRow.ActualForeign += SignTransAmount;
}
}
}
}
}
}
}
}
return true;
}
/// <summary>
/// Load all GLM and GLMPeriod records for the batch period and the following periods, since that will avoid loading them one by one during submitchanges.
/// this is called after ValidateBatchAndTransactions, because the BatchYear and BatchPeriod are validated and recalculated there
/// </summary>
private static void LoadGLMData(ref GLPostingTDS AGLPostingDS, Int32 ALedgerNumber, ABatchRow ABatchToPost)
{
TDBTransaction Transaction = null;
GLPostingTDS GLPostingDS = AGLPostingDS;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
AGeneralLedgerMasterRow GLMTemplateRow = GLPostingDS.AGeneralLedgerMaster.NewRowTyped(false);
GLMTemplateRow.LedgerNumber = ALedgerNumber;
GLMTemplateRow.Year = ABatchToPost.BatchYear;
AGeneralLedgerMasterAccess.LoadUsingTemplate(GLPostingDS, GLMTemplateRow, Transaction);
string query = "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_ledger_number_i = ? " +
"AND PUB_a_general_ledger_master.a_year_i = ? " +
"AND PUB_a_general_ledger_master_period.a_glm_sequence_i = PUB_a_general_ledger_master.a_glm_sequence_i " +
"AND PUB_a_general_ledger_master_period.a_period_number_i >= ?";
List <OdbcParameter>parameters = new List <OdbcParameter>();
OdbcParameter parameter = new OdbcParameter("ledgernumber", OdbcType.Int);
parameter.Value = ALedgerNumber;
parameters.Add(parameter);
parameter = new OdbcParameter("year", OdbcType.Int);
parameter.Value = ABatchToPost.BatchYear;
parameters.Add(parameter);
parameter = new OdbcParameter("period", OdbcType.Int);
parameter.Value = ABatchToPost.BatchPeriod;
parameters.Add(parameter);
DBAccess.GDBAccessObj.Select(GLPostingDS,
query,
GLPostingDS.AGeneralLedgerMasterPeriod.TableName, Transaction, parameters.ToArray());
});
}
/// <summary>
/// validate the attributes of the transactions
/// some things are even modified, eg. batch period etc from date effective
/// </summary>
private static bool ValidateAnalysisAttributes(ref GLBatchTDS ADataSet,
GLPostingTDS APostingDS,
Int32 ALedgerNumber,
Int32 ABatchNumber,
out TVerificationResultCollection AVerifications)
{
AVerifications = new TVerificationResultCollection();
DataView TransactionsOfJournalView = new DataView(ADataSet.ATransaction);
foreach (AJournalRow journal in ADataSet.AJournal.Rows)
{
if (journal.BatchNumber != ABatchNumber)
{
continue;
}
TransactionsOfJournalView.RowFilter = ATransactionTable.GetJournalNumberDBName() + " = " + journal.JournalNumber.ToString();
foreach (DataRowView transRowView in TransactionsOfJournalView)
{
ATransactionRow transRow = (ATransactionRow)transRowView.Row;
// Check that all atransanalattrib records are there for all analattributes entries
DataView ANView = APostingDS.AAnalysisAttribute.DefaultView;
ANView.RowFilter = String.Format("{0} = '{1}' AND {2} = true",
AAnalysisAttributeTable.GetAccountCodeDBName(),
transRow.AccountCode, AAnalysisAttributeTable.GetActiveDBName());
int i = 0;
while (i < ANView.Count)
{
AAnalysisAttributeRow attributeRow = (AAnalysisAttributeRow)ANView[i].Row;
ATransAnalAttribRow aTransAttribRow =
(ATransAnalAttribRow)ADataSet.ATransAnalAttrib.Rows.Find(new object[] { ALedgerNumber, ABatchNumber,
transRow.JournalNumber,
transRow.TransactionNumber,
attributeRow.AnalysisTypeCode });
if (aTransAttribRow == null)
{
AVerifications.Add(new TVerificationResult(
String.Format(Catalog.GetString("Cannot post Batch {0} in Ledger {1}"), ABatchNumber, ALedgerNumber),
String.Format(Catalog.GetString(
"Missing attributes record for journal #{0} transaction #{1} and TypeCode {2}"),
transRow.JournalNumber,
transRow.TransactionNumber, attributeRow.AnalysisTypeCode),
TResultSeverity.Resv_Critical));
}
else
{
String v = aTransAttribRow.AnalysisAttributeValue;
if ((v == null) || (v.Length == 0))
{
AVerifications.Add(new TVerificationResult(
String.Format(Catalog.GetString("Cannot post Batch {0} in Ledger {1}"), ABatchNumber, ALedgerNumber),
String.Format(Catalog.GetString("Analysis Type {0} is missing values in journal #{1}, transaction #{2}"),
attributeRow.AnalysisTypeCode, transRow.JournalNumber, transRow.TransactionNumber),
TResultSeverity.Resv_Critical));
}
else
{
AFreeformAnalysisRow afaRow = (AFreeformAnalysisRow)APostingDS.AFreeformAnalysis.Rows.Find(
new Object[] { ALedgerNumber, attributeRow.AnalysisTypeCode, v });
if (afaRow == null)
{
// this would cause a constraint error and is only possible in a development/sqlite environment
AVerifications.Add(new TVerificationResult(
String.Format(Catalog.GetString("Cannot post Batch {0} in Ledger {1}"), ABatchNumber, ALedgerNumber),
String.Format(Catalog.GetString("Invalid values at journal #{0} transaction #{1} and TypeCode {2}"),
transRow.JournalNumber, transRow.TransactionNumber, attributeRow.AnalysisTypeCode),
TResultSeverity.Resv_Critical));
}
else
{
if (!afaRow.Active)
{
AVerifications.Add(new TVerificationResult(
String.Format(Catalog.GetString("Cannot post Batch {0} in Ledger {1}"), ABatchNumber,
ALedgerNumber),
String.Format(Catalog.GetString(
"Value {0} not active at journal #{1} transaction #{2} and TypeCode {3}"), v,
transRow.JournalNumber, transRow.TransactionNumber, attributeRow.AnalysisTypeCode),
TResultSeverity.Resv_Critical));
} // if
} // else
} // else
} // else
i++;
} // while i
} // foreach transRowView
} // foreach journal
return TVerificationHelper.IsNullOrOnlyNonCritical(AVerifications);
}
//
//
// April 2015, Tim Ingham:
//
// NOTE that the full summarization that includes all the summary levels has been discontinued,
// since the Open Petra reports only use the posting levels and calculate the summaries on the fly.
// This makes Ledger posting MUCH faster.
//
// The full SummarizeData method, and its supporting CalculateTrees method, is still present,
// and we could return to it if it became necessary.
private static void SummarizeInternal(Int32 ALedgerNumber,
GLPostingTDS APostingDS,
SortedList <string, TAmount>APostingLevel,
Int32 AFromPeriod,
bool ACalculatePostingTree)
{
#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 (APostingDS == null)
{
throw new EFinanceSystemDataObjectNullOrEmptyException(String.Format(Catalog.GetString(
"Function:{0} - The GL Posting dataset is null!"),
Utilities.GetMethodName(true)));
}
#endregion Validate Arguments
if (ACalculatePostingTree)
{
TLogging.LogAtLevel(POSTING_LOGLEVEL, "Posting: CalculateTrees...");
// key is PostingAccount, the value TAccountTreeElement describes the parent account and other details of the relation
SortedList <string, TAccountTreeElement>AccountTree;
// key is the PostingCostCentre, the value is the parent Cost Centre
SortedList <string, string>CostCentreTree;
// this was in Petra 2.x; takes a lot of time, which the reports could do better
// TODO: can we just calculate the cost centre tree, since that is needed for Balance Sheet,
// but avoid calculating the whole account tree?
CalculateTrees(ALedgerNumber, ref APostingLevel, out AccountTree, out CostCentreTree, APostingDS);
TLogging.LogAtLevel(POSTING_LOGLEVEL, "Posting: SummarizeData...");
SummarizeData(APostingDS, AFromPeriod, ref APostingLevel, ref AccountTree, ref CostCentreTree);
}
else
{
TLogging.LogAtLevel(POSTING_LOGLEVEL, "Posting: SummarizeDataSimple...");
SummarizeDataSimple(ALedgerNumber, APostingDS, AFromPeriod, ref APostingLevel);
}
}
/// <summary>
/// Only used for precalculating the new balances before the user actually posts the batch
/// </summary>
/// <param name="ALedgerNumber"></param>
/// <param name="ABatchNumber"></param>
/// <param name="ATransaction"></param>
/// <param name="AVerifications"></param>
/// <param name="APostingDS"></param>
/// <param name="ABatchPeriod"></param>
/// <returns></returns>
public static bool TestPostGLBatch(Int32 ALedgerNumber,
Int32 ABatchNumber,
TDBTransaction ATransaction,
out TVerificationResultCollection AVerifications,
out GLPostingTDS APostingDS,
ref Int32 ABatchPeriod)
{
#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 (ABatchNumber <= 0)
{
throw new EFinanceSystemInvalidBatchNumberException(String.Format(Catalog.GetString(
"Function:{0} - The Batch number must be greater than 0!"),
Utilities.GetMethodName(true)), ALedgerNumber, ABatchNumber);
}
else if (ATransaction == null)
{
throw new EFinanceSystemDBTransactionNullException(String.Format(Catalog.GetString(
"Function:{0} - Database Transaction must not be NULL!"),
Utilities.GetMethodName(true)));
}
#endregion Validate Arguments
bool RetVal = false;
GLBatchTDS MainDS = new GLBatchTDS();
SortedList <string, TAmount>PostingLevel = new SortedList <string, TGLPosting.TAmount>();
try
{
APostingDS = PrepareGLBatchForPosting(out MainDS,
ALedgerNumber,
ABatchNumber,
ref ATransaction,
out AVerifications,
PostingLevel,
ref ABatchPeriod);
if ((MainDS != null) && (APostingDS != null))
{
SummarizeInternal(ALedgerNumber, APostingDS, PostingLevel, ABatchPeriod, false);
RetVal = true;
}
}
catch (Exception ex)
{
TLogging.Log(String.Format("Method:{0} - Unexpected error!{1}{1}{2}",
Utilities.GetMethodSignature(),
Environment.NewLine,
ex.Message));
throw ex;
}
return RetVal;
}
/// <summary>
/// For each posting level, propagate the value upwards through both the account and the cost centre hierarchy in glm master;
/// also propagate the value from the posting period through the following periods;
/// </summary>
private static bool SummarizeData(
GLPostingTDS APostingDS,
Int32 AFromPeriod,
ref SortedList <string, TAmount>APostingLevel,
ref SortedList <string, TAccountTreeElement>AAccountTree,
ref SortedList <string, string>ACostCentreTree)
{
#region Validate Arguments
if (APostingDS == null)
{
throw new EFinanceSystemDataObjectNullOrEmptyException(String.Format(Catalog.GetString(
"Function:{0} - The GL Posting dataset is null!"),
Utilities.GetMethodName(true)));
}
else if ((APostingDS.ALedger == null) || (APostingDS.ALedger.Count == 0))
{
throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString(
"Function:{0} - Ledger data in the GL Posting dataset does not exist or could not be accessed!"),
Utilities.GetMethodName(true)));
}
#endregion Validate Arguments
if (APostingDS.ALedger[0].ProvisionalYearEndFlag)
{
// If the year end close is running, then we are posting the year end
// reallocations. These appear as part of the final period, but
// should only be written to the forward periods.
// In year end, a_current_period_i = a_number_of_accounting_periods_i = a_batch_period_i.
AFromPeriod++;
}
DataView GLMMasterView = APostingDS.AGeneralLedgerMaster.DefaultView;
GLMMasterView.Sort = AGeneralLedgerMasterTable.GetAccountCodeDBName() + "," + AGeneralLedgerMasterTable.GetCostCentreCodeDBName();
DataView GLMPeriodView = APostingDS.AGeneralLedgerMasterPeriod.DefaultView;
GLMPeriodView.Sort = AGeneralLedgerMasterPeriodTable.GetGlmSequenceDBName() + "," + AGeneralLedgerMasterPeriodTable.GetPeriodNumberDBName();
// Loop through the posting data collected earlier.
foreach (string postingLevelKey in APostingLevel.Keys)
{
String[] keyParts = postingLevelKey.Split(':');
string accountCode = keyParts[0];
string costCentreCode = keyParts[1];
TAmount postingLevelElement = APostingLevel[postingLevelKey];
// Combine the summarization trees for both the account and the cost centre.
foreach (string accountTreeKey in AAccountTree.Keys)
{
String[] accountKeyParts = accountTreeKey.Split(':');
if (accountKeyParts[0] == accountCode)
{
string accountCodeToReportTo = accountKeyParts[1];
TAccountTreeElement accountTreeElement = AAccountTree[accountTreeKey];
foreach (string costCentreKey in ACostCentreTree.Keys)
{
String[] cCKeyParts = costCentreKey.Split(':');
if (cCKeyParts[0] == costCentreCode)
{
string costCentreCodeToReportTo = cCKeyParts[1];
decimal signBaseAmount = postingLevelElement.BaseAmount;
decimal signIntlAmount = postingLevelElement.IntlAmount;
decimal signTransAmount = postingLevelElement.TransAmount;
// Set the sign of the amounts according to the debit/credit indicator
if (accountTreeElement.Invert)
{
signBaseAmount *= -1;
signIntlAmount *= -1;
signTransAmount *= -1;
}
// Find the summary level, creating it if it does not already exist.
int gLMMasterIndex = GLMMasterView.Find(new object[] { accountCodeToReportTo, costCentreCodeToReportTo });
if (gLMMasterIndex == -1)
{
CreateGLMYear(
ref APostingDS,
APostingDS.ALedger[0].LedgerNumber,
accountCodeToReportTo,
costCentreCodeToReportTo);
gLMMasterIndex = GLMMasterView.Find(new object[] { accountCodeToReportTo, costCentreCodeToReportTo });
}
AGeneralLedgerMasterRow gLMRow = (AGeneralLedgerMasterRow)GLMMasterView[gLMMasterIndex].Row;
#region Validate Data
if (gLMRow == null)
{
throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString(
"Function:{0} - GLM row data does not exist or could not be accessed!"),
Utilities.GetMethodName(true)));
}
#endregion Validate Data
gLMRow.YtdActualBase += signBaseAmount;
gLMRow.YtdActualIntl += signIntlAmount;
if (accountTreeElement.Foreign)
{
if (gLMRow.IsYtdActualForeignNull())
{
gLMRow.YtdActualForeign = signTransAmount;
}
else
{
gLMRow.YtdActualForeign += signTransAmount;
}
}
if (APostingDS.ALedger[0].ProvisionalYearEndFlag)
{
gLMRow.ClosingPeriodActualBase += signBaseAmount;
gLMRow.ClosingPeriodActualIntl += signIntlAmount;
}
// Add the data to forward periods, to the end of the GLMP list
for (Int32 PeriodCount = AFromPeriod;
PeriodCount <= APostingDS.ALedger[0].NumberOfAccountingPeriods + APostingDS.ALedger[0].NumberFwdPostingPeriods;
PeriodCount++)
{
int gLMPeriodIndex = GLMPeriodView.Find(new object[] { gLMRow.GlmSequence, PeriodCount });
AGeneralLedgerMasterPeriodRow gLMPeriodRow;
if (gLMPeriodIndex == -1)
{
gLMPeriodRow = APostingDS.AGeneralLedgerMasterPeriod.NewRowTyped();
gLMPeriodRow.GlmSequence = gLMRow.GlmSequence;
gLMPeriodRow.PeriodNumber = PeriodCount;
APostingDS.AGeneralLedgerMasterPeriod.Rows.Add(gLMPeriodRow);
}
else
{
gLMPeriodRow = (AGeneralLedgerMasterPeriodRow)GLMPeriodView[gLMPeriodIndex].Row;
}
gLMPeriodRow.ActualBase += signBaseAmount;
gLMPeriodRow.ActualIntl += signIntlAmount;
if (accountTreeElement.Foreign)
{
if (gLMPeriodRow.IsActualForeignNull())
{
gLMPeriodRow.ActualForeign = signTransAmount;
}
else
{
gLMPeriodRow.ActualForeign += signTransAmount;
}
}
}
}
}
}
}
}
return true;
}
/// <summary>
/// on the posting level propagate the value from the posting period through the following periods;
/// in this version of SummarizeData, there is no calculation of summary accounts/cost centres, since that can be done by the reports
/// </summary>
private static bool SummarizeDataSimple(
Int32 ALedgerNumber,
GLPostingTDS APostingDS,
Int32 AFromPeriod,
ref SortedList <string, TAmount>APostingLevel)
{
#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 (APostingDS == null)
{
throw new EFinanceSystemDataObjectNullOrEmptyException(String.Format(Catalog.GetString(
"Function:{0} - The GL Posting dataset is null!"),
Utilities.GetMethodName(true)));
}
else if ((APostingDS.ALedger == null) || (APostingDS.ALedger.Count == 0))
{
throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString(
"Function:{0} - Ledger data in the GL Posting dataset does not exist or could not be accessed!"),
Utilities.GetMethodName(true)));
}
#endregion Validate Arguments
if (APostingDS.ALedger[0].ProvisionalYearEndFlag)
{
// If the year end close is running, then we are posting the year end
// reallocations. These appear as part of the final period, but
// should only be written to the forward periods.
// In year end, a_current_period_i = a_number_of_accounting_periods_i = a_batch_period_i.
AFromPeriod++;
}
DataView GLMMasterView = APostingDS.AGeneralLedgerMaster.DefaultView;
GLMMasterView.Sort = AGeneralLedgerMasterTable.GetAccountCodeDBName() + "," + AGeneralLedgerMasterTable.GetCostCentreCodeDBName();
DataView GLMPeriodView = APostingDS.AGeneralLedgerMasterPeriod.DefaultView;
GLMPeriodView.Sort = AGeneralLedgerMasterPeriodTable.GetGlmSequenceDBName() + "," + AGeneralLedgerMasterPeriodTable.GetPeriodNumberDBName();
foreach (string postingLevelKey in APostingLevel.Keys)
{
String[] keyParts = postingLevelKey.Split(':');
string accountCode = keyParts[0];
string costCentreCode = keyParts[1];
TAmount postingLevelElement = APostingLevel[postingLevelKey];
// Find the posting level, creating it if it does not already exist.
int gLMMasterIndex = GLMMasterView.Find(new object[] { accountCode, costCentreCode });
AGeneralLedgerMasterRow gLMRow;
if (gLMMasterIndex == -1)
{
CreateGLMYear(
ref APostingDS,
ALedgerNumber,
accountCode,
costCentreCode);
gLMMasterIndex = GLMMasterView.Find(new object[] { accountCode, costCentreCode });
}
gLMRow = (AGeneralLedgerMasterRow)GLMMasterView[gLMMasterIndex].Row;
gLMRow.YtdActualBase += postingLevelElement.BaseAmount;
gLMRow.YtdActualIntl += postingLevelElement.IntlAmount;
AAccountRow accountRow = (AAccountRow)APostingDS.AAccount.Rows.Find(new object[] { ALedgerNumber, accountCode });
#region Validate Data
if (accountRow == null)
{
throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString(
"Function:{0} - Account row data for Account code {1} in Ledger number {2} does not exist or could not be accessed!"),
Utilities.GetMethodName(true),
accountCode,
ALedgerNumber));
}
#endregion Validate Data
if (accountRow.ForeignCurrencyFlag)
{
if (gLMRow.IsYtdActualForeignNull())
{
gLMRow.YtdActualForeign = postingLevelElement.TransAmount;
}
else
{
gLMRow.YtdActualForeign += postingLevelElement.TransAmount;
}
}
if (APostingDS.ALedger[0].ProvisionalYearEndFlag)
{
gLMRow.ClosingPeriodActualBase += postingLevelElement.BaseAmount;
gLMRow.ClosingPeriodActualIntl += postingLevelElement.IntlAmount;
} // Last use of GlmRow in this routine ...
// propagate the data through the following periods
for (Int32 periodCount = AFromPeriod;
periodCount <= APostingDS.ALedger[0].NumberOfAccountingPeriods + APostingDS.ALedger[0].NumberFwdPostingPeriods;
periodCount++)
{
int gLMPeriodIndex = GLMPeriodView.Find(new object[] { gLMRow.GlmSequence, periodCount });
AGeneralLedgerMasterPeriodRow gLMPeriodRow;
if (gLMPeriodIndex == -1)
{
gLMPeriodRow = APostingDS.AGeneralLedgerMasterPeriod.NewRowTyped();
gLMPeriodRow.GlmSequence = gLMRow.GlmSequence;
gLMPeriodRow.PeriodNumber = periodCount;
}
else
{
gLMPeriodRow = (AGeneralLedgerMasterPeriodRow)GLMPeriodView[gLMPeriodIndex].Row;
}
gLMPeriodRow.ActualBase += postingLevelElement.BaseAmount;
gLMPeriodRow.ActualIntl += postingLevelElement.IntlAmount;
if (accountRow.ForeignCurrencyFlag)
{
if (gLMPeriodRow.IsActualForeignNull())
{
gLMPeriodRow.ActualForeign = postingLevelElement.TransAmount;
}
else
{
gLMPeriodRow.ActualForeign += postingLevelElement.TransAmount;
}
}
}
}
GLMMasterView.Sort = "";
GLMPeriodView.Sort = "";
return true;
}
/// <summary>
/// Calculate the summarization trees for each posting account and each
/// posting cost centre. The result of the union of these trees,
/// excluding the base posting/posting combination, is the set of
/// accounts that receive the summary data.
/// </summary>
private static bool CalculateTrees(
Int32 ALedgerNumber,
ref SortedList <string, TAmount>APostingLevel,
out SortedList <string, TAccountTreeElement>AAccountTree,
out SortedList <string, string>ACostCentreTree,
GLPostingTDS APostingDS)
{
#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 (APostingDS == null)
{
throw new EFinanceSystemDataObjectNullOrEmptyException(String.Format(Catalog.GetString(
"Function:{0} - The GL Posting dataset is null!"),
Utilities.GetMethodName(true)));
}
#endregion Validate Arguments
// get all accounts that each posting level account is directly or indirectly posting to
AAccountTree = new SortedList <string, TAccountTreeElement>();
foreach (string postingLevelKey in APostingLevel.Keys)
{
string accountCode = TAmount.GetAccountCode(postingLevelKey);
// only once for each account, even though there might be several entries for one account in APostingLevel because of different costcentres
if (AAccountTree.ContainsKey(TAccountTreeElement.MakeKey(accountCode, accountCode)))
{
continue;
}
AAccountRow accountRow = (AAccountRow)APostingDS.AAccount.Rows.Find(new object[] { ALedgerNumber, accountCode });
#region Validate Data
if (accountRow == null)
{
throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString(
"Function:{0} - Account row data for Account code {1} in Ledger number {2} does not exist or could not be accessed!"),
Utilities.GetMethodName(true),
accountCode,
ALedgerNumber));
}
#endregion Validate Data
bool DebitCreditIndicator = accountRow.DebitCreditIndicator;
AAccountTree.Add(TAccountTreeElement.MakeKey(accountCode, accountCode),
new TAccountTreeElement(false, accountRow.ForeignCurrencyFlag));
AAccountHierarchyDetailRow HierarchyDetail =
(AAccountHierarchyDetailRow)APostingDS.AAccountHierarchyDetail.Rows.Find(
new object[] { ALedgerNumber, MFinanceConstants.ACCOUNT_HIERARCHY_STANDARD, accountCode });
while (HierarchyDetail != null)
{
accountRow = (AAccountRow)APostingDS.AAccount.Rows.Find(new object[] { ALedgerNumber, HierarchyDetail.AccountCodeToReportTo });
if (accountRow == null)
{
// current account is BAL SHT, and it reports nowhere (account with name = ledgernumber does not exist)
break;
}
AAccountTree.Add(TAccountTreeElement.MakeKey(accountCode, HierarchyDetail.AccountCodeToReportTo),
new TAccountTreeElement(DebitCreditIndicator != accountRow.DebitCreditIndicator, accountRow.ForeignCurrencyFlag));
HierarchyDetail = (AAccountHierarchyDetailRow)APostingDS.AAccountHierarchyDetail.Rows.Find(
new object[] { ALedgerNumber, MFinanceConstants.ACCOUNT_HIERARCHY_STANDARD, HierarchyDetail.AccountCodeToReportTo });
}
}
ACostCentreTree = new SortedList <string, string>();
foreach (string postingLevelKey in APostingLevel.Keys)
{
string costCentreCode = TAmount.GetCostCentreCode(postingLevelKey);
// only once for each cost centre
if (ACostCentreTree.ContainsKey(costCentreCode + ":" + costCentreCode))
{
continue;
}
ACostCentreTree.Add(costCentreCode + ":" + costCentreCode,
costCentreCode + ":" + costCentreCode);
ACostCentreRow costCentre = (ACostCentreRow)APostingDS.ACostCentre.Rows.Find(new object[] { ALedgerNumber, costCentreCode });
while (costCentre != null && !costCentre.IsCostCentreToReportToNull())
{
ACostCentreTree.Add(costCentreCode + ":" + costCentre.CostCentreToReportTo,
costCentreCode + ":" + costCentre.CostCentreToReportTo);
costCentre = (ACostCentreRow)APostingDS.ACostCentre.Rows.Find(new object[] { ALedgerNumber, costCentre.CostCentreToReportTo });
}
}
return true;
}
/// <summary>
/// mark each journal, each transaction as being posted;
/// add sums for costcentre/account combinations
/// </summary>
/// <param name="AMainDS">can contain several batches and journals and transactions</param>
/// <param name="APostingDS"></param>
/// <param name="APostingLevel">the balance changes at the posting level</param>
/// <param name="ABatchToPost">the batch to post</param>
/// <returns>a list with the sums for each costcentre/account combination</returns>
private static SortedList <string, TAmount>MarkAsPostedAndCollectData(GLBatchTDS AMainDS,
GLPostingTDS APostingDS,
SortedList <string, TAmount>APostingLevel, ABatchRow ABatchToPost)
{
#region Validate Arguments
if (AMainDS == null)
{
throw new EFinanceSystemDataObjectNullOrEmptyException(String.Format(Catalog.GetString("Function:{0} - The GL Batch dataset is null!"),
Utilities.GetMethodName(true)));
}
else if (APostingDS == null)
{
throw new EFinanceSystemDataObjectNullOrEmptyException(String.Format(Catalog.GetString(
"Function:{0} - The GL Posting dataset is null!"),
Utilities.GetMethodName(true)));
}
else if (ABatchToPost == null)
{
throw new EFinanceSystemDataObjectNullOrEmptyException(String.Format(Catalog.GetString(
"Function:{0} - The GL Batch to post data row is null!"),
Utilities.GetMethodName(true)));
}
#endregion Validate Arguments
DataView TransactionsDV = new DataView(AMainDS.ATransaction);
TransactionsDV.Sort = ATransactionTable.GetJournalNumberDBName();
foreach (AJournalRow journal in AMainDS.AJournal.Rows)
{
if (journal.BatchNumber != ABatchToPost.BatchNumber)
{
continue;
}
foreach (DataRowView transactionview in TransactionsDV.FindRows(journal.JournalNumber))
{
ATransactionRow transaction = (ATransactionRow)transactionview.Row;
if (transaction.BatchNumber != ABatchToPost.BatchNumber)
{
continue;
}
transaction.TransactionStatus = true;
// get the account that this transaction is writing to
AAccountRow accountRow = (AAccountRow)APostingDS.AAccount.Rows.Find(new object[] { transaction.LedgerNumber,
transaction.AccountCode });
#region Validate Data
if (accountRow == null)
{
throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString(
"Function:{0} - Account row data for Account code {1} in Ledger number {2} does not exist or could not be accessed!"),
Utilities.GetMethodName(true),
transaction.AccountCode,
transaction.LedgerNumber));
}
#endregion Validate Data
// Set the sign of the amounts according to the debit/credit indicator
decimal SignBaseAmount = transaction.AmountInBaseCurrency;
decimal SignIntlAmount = transaction.AmountInIntlCurrency;
decimal SignTransAmount = transaction.TransactionAmount;
if (accountRow.DebitCreditIndicator != transaction.DebitCreditIndicator)
{
SignBaseAmount *= -1.0M;
SignIntlAmount *= -1.0M;
SignTransAmount *= -1.0M;
}
// TODO: do we need to check for base currency corrections?
// or do we get rid of these problems by not having international currency?
string key = TAmount.MakeKey(transaction.AccountCode, transaction.CostCentreCode);
if (!APostingLevel.ContainsKey(key))
{
APostingLevel.Add(key, new TAmount());
}
APostingLevel[key].BaseAmount += SignBaseAmount;
APostingLevel[key].IntlAmount += SignIntlAmount;
// Only foreign currency accounts store a value in the transaction currency,
// if the transaction was actually in the foreign currency.
if (accountRow.ForeignCurrencyFlag && (journal.TransactionCurrency == accountRow.ForeignCurrencyCode))
{
APostingLevel[key].TransAmount += SignTransAmount;
}
}
journal.JournalStatus = MFinanceConstants.BATCH_POSTED;
}
ABatchToPost.BatchStatus = MFinanceConstants.BATCH_POSTED;
return APostingLevel;
}
/// <summary>
/// validate the attributes of the transactions
/// some things are even modified, eg. batch period etc from date effective
/// </summary>
private static bool ValidateAnalysisAttributes(ref GLBatchTDS AGLBatchDS,
GLPostingTDS APostingDS,
Int32 ALedgerNumber,
Int32 ABatchNumber,
out TVerificationResultCollection AVerifications)
{
#region Validate Arguments
if (AGLBatchDS == null)
{
throw new EFinanceSystemDataObjectNullOrEmptyException(String.Format(Catalog.GetString("Function:{0} - The GL Batch dataset is null!"),
Utilities.GetMethodName(true)));
}
else if (APostingDS == null)
{
throw new EFinanceSystemDataObjectNullOrEmptyException(String.Format(Catalog.GetString(
"Function:{0} - The GL Posting dataset is null!"),
Utilities.GetMethodName(true)));
}
else 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 (ABatchNumber <= 0)
{
throw new EFinanceSystemInvalidBatchNumberException(String.Format(Catalog.GetString(
"Function:{0} - The Batch number in Ledger {1} must be greater than 0!"),
Utilities.GetMethodName(true), ALedgerNumber), ALedgerNumber, ABatchNumber);
}
#endregion Validate Arguments
bool CriticalError = false;
AVerifications = new TVerificationResultCollection();
DataView TransactionsOfJournalView = new DataView(AGLBatchDS.ATransaction);
foreach (AJournalRow journal in AGLBatchDS.AJournal.Rows)
{
if (journal.BatchNumber != ABatchNumber)
{
continue;
}
TransactionsOfJournalView.RowFilter = ATransactionTable.GetJournalNumberDBName() + " = " + journal.JournalNumber.ToString();
foreach (DataRowView transRowView in TransactionsOfJournalView)
{
ATransactionRow transRow = (ATransactionRow)transRowView.Row;
// Check that all atransanalattrib records are there for all analattributes entries
DataView ANView = APostingDS.AAnalysisAttribute.DefaultView;
ANView.RowFilter = String.Format("{0} = '{1}' AND {2} = true",
AAnalysisAttributeTable.GetAccountCodeDBName(),
transRow.AccountCode, AAnalysisAttributeTable.GetActiveDBName());
int counter = 0;
while (counter < ANView.Count)
{
AAnalysisAttributeRow attributeRow = (AAnalysisAttributeRow)ANView[counter].Row;
ATransAnalAttribRow aTransAttribRow =
(ATransAnalAttribRow)AGLBatchDS.ATransAnalAttrib.Rows.Find(new object[] { ALedgerNumber, ABatchNumber,
transRow.JournalNumber,
transRow.TransactionNumber,
attributeRow.AnalysisTypeCode });
if (aTransAttribRow == null)
{
AVerifications.Add(new TVerificationResult(
String.Format(Catalog.GetString("Cannot post Batch {0} in Ledger {1}"), ABatchNumber, ALedgerNumber),
String.Format(Catalog.GetString(
"Analysis Type {0} is missing values in journal {1}, transaction {2}"),
attributeRow.AnalysisTypeCode, transRow.JournalNumber, transRow.TransactionNumber),
TResultSeverity.Resv_Critical));
CriticalError = true;
break;
}
else
{
String analAttrValue = aTransAttribRow.AnalysisAttributeValue;
if ((analAttrValue == null) || (analAttrValue.Length == 0))
{
AVerifications.Add(new TVerificationResult(
String.Format(Catalog.GetString("Cannot post Batch {0} in Ledger {1}"), ABatchNumber, ALedgerNumber),
String.Format(Catalog.GetString("Analysis Type {0} is missing values in journal {1}, transaction {2}"),
attributeRow.AnalysisTypeCode, transRow.JournalNumber, transRow.TransactionNumber),
TResultSeverity.Resv_Critical));
CriticalError = true;
break;
}
else
{
AFreeformAnalysisRow afaRow = (AFreeformAnalysisRow)APostingDS.AFreeformAnalysis.Rows.Find(
new Object[] { ALedgerNumber, attributeRow.AnalysisTypeCode, analAttrValue });
if (afaRow == null)
{
// this would cause a constraint error and is only possible in a development/sqlite environment
AVerifications.Add(new TVerificationResult(
String.Format(Catalog.GetString("Cannot post Batch {0} in Ledger {1}"), ABatchNumber, ALedgerNumber),
String.Format(Catalog.GetString("Analysis Type {0} has invalid value in journal {1}, transaction {2}"),
attributeRow.AnalysisTypeCode, transRow.JournalNumber, transRow.TransactionNumber),
TResultSeverity.Resv_Critical));
CriticalError = true;
break;
}
else
{
if (!afaRow.Active)
{
AVerifications.Add(new TVerificationResult(
String.Format(Catalog.GetString("Cannot post Batch {0} in Ledger {1}"), ABatchNumber,
ALedgerNumber),
String.Format(Catalog.GetString(
"Analysis Type {0} has inactive value: '{1}' in journal {2}, transaction {3}"),
attributeRow.AnalysisTypeCode,
analAttrValue, transRow.JournalNumber, transRow.TransactionNumber),
TResultSeverity.Resv_Critical));
CriticalError = true;
break;
} // if
} // else
} // else
} // else
counter++;
} // while i
if (CriticalError)
{
break;
}
} // foreach transRowView
if (CriticalError)
{
break;
}
} // foreach journal
return TVerificationHelper.IsNullOrOnlyNonCritical(AVerifications);
}
/// <summary>
/// Only used for precalculating the new balances before the user actually posts the batch
/// </summary>
/// <param name="ALedgerNumber"></param>
/// <param name="ABatchNumber"></param>
/// <param name="ATransaction"></param>
/// <param name="AVerifications"></param>
/// <param name="APostingDS"></param>
/// <param name="ABatchPeriod"></param>
/// <returns></returns>
public static bool TestPostGLBatch(Int32 ALedgerNumber,
Int32 ABatchNumber,
TDBTransaction ATransaction,
out TVerificationResultCollection AVerifications,
out GLPostingTDS APostingDS,
ref Int32 ABatchPeriod)
{
GLBatchTDS MainDS = new GLBatchTDS();
SortedList <string, TAmount>PostingLevel = new SortedList <string, TGLPosting.TAmount>();
APostingDS = PrepareGLBatchForPosting(out MainDS,
ALedgerNumber,
ABatchNumber,
ref ATransaction,
out AVerifications,
PostingLevel,
ref ABatchPeriod);
if ((MainDS != null) && (APostingDS != null))
{
SummarizeInternal(ALedgerNumber, APostingDS, PostingLevel, ABatchPeriod, false);
return true;
}
return false;
}
/// <summary>
/// load the tables that are needed for posting
/// </summary>
/// <param name="ALedgerNumber"></param>
/// <returns></returns>
private static GLPostingTDS LoadGLDataForPosting(Int32 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
GLPostingTDS PostingDS = new GLPostingTDS();
TDBTransaction Transaction = null;
try
{
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
ALedgerAccess.LoadByPrimaryKey(PostingDS, ALedgerNumber, Transaction);
// load all accounts of ledger, because we need them later for the account hierarchy tree for summarisation
AAccountAccess.LoadViaALedger(PostingDS, ALedgerNumber, Transaction);
// TODO: use cached table?
AAccountHierarchyDetailAccess.LoadViaAAccountHierarchy(PostingDS,
ALedgerNumber,
MFinanceConstants.ACCOUNT_HIERARCHY_STANDARD,
Transaction);
// TODO: use cached table?
ACostCentreAccess.LoadViaALedger(PostingDS, ALedgerNumber, Transaction);
AAnalysisTypeAccess.LoadViaALedger(PostingDS, ALedgerNumber, Transaction);
AFreeformAnalysisAccess.LoadViaALedger(PostingDS, ALedgerNumber, Transaction);
AAnalysisAttributeAccess.LoadViaALedger(PostingDS, ALedgerNumber, Transaction);
});
#region Validate Data
//Only the following tables should not be empty when posting.
if ((PostingDS.ALedger == null) || (PostingDS.ALedger.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));
}
else if ((PostingDS.AAccount == null) || (PostingDS.AAccount.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));
}
else if ((PostingDS.ACostCentre == null) || (PostingDS.ACostCentre.Count == 0))
{
throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString(
"Function:{0} - Cost Centre data for Ledger number {1} does not exist or could not be accessed!"),
Utilities.GetMethodName(true),
ALedgerNumber));
}
else if ((PostingDS.AAccountHierarchyDetail == null) || (PostingDS.AAccountHierarchyDetail.Count == 0))
{
throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString(
"Function:{0} - AAccount Hierarchy Detail data for Ledger number {1} does not exist or could not be accessed!"),
Utilities.GetMethodName(true),
ALedgerNumber));
}
#endregion Validate Data
}
catch (Exception ex)
{
TLogging.Log(String.Format("Method:{0} - Unexpected error!{1}{1}{2}",
Utilities.GetMethodSignature(),
Environment.NewLine,
ex.Message));
throw ex;
}
return PostingDS;
}
/// <summary>
/// load the tables that are needed for posting
/// </summary>
/// <param name="ALedgerNumber"></param>
/// <returns></returns>
private static GLPostingTDS LoadDataForPosting(Int32 ALedgerNumber)
{
GLPostingTDS PostingDS = new GLPostingTDS();
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
ALedgerAccess.LoadByPrimaryKey(PostingDS, ALedgerNumber, Transaction);
// load all accounts of ledger, because we need them later for the account hierarchy tree for summarisation
AAccountAccess.LoadViaALedger(PostingDS, ALedgerNumber, Transaction);
// TODO: use cached table?
AAccountHierarchyDetailAccess.LoadViaAAccountHierarchy(PostingDS,
ALedgerNumber,
MFinanceConstants.ACCOUNT_HIERARCHY_STANDARD,
Transaction);
// TODO: use cached table?
ACostCentreAccess.LoadViaALedger(PostingDS, ALedgerNumber, Transaction);
AAnalysisTypeAccess.LoadAll(PostingDS, Transaction);
AFreeformAnalysisAccess.LoadViaALedger(PostingDS, ALedgerNumber, Transaction);
AAnalysisAttributeAccess.LoadViaALedger(PostingDS, ALedgerNumber, Transaction);
});
return PostingDS;
}
/// <summary>
/// Load all GLM and GLMPeriod records for the batch period and the following periods, since that will avoid loading them one by one during submitchanges.
/// this is called after ValidateBatchAndTransactions, because the BatchYear and BatchPeriod are validated and recalculated there
///
/// This should probably be changed, in the new, skinny summarization, only a few rows need to be accessed.
/// </summary>
private static void LoadGLMData(ref GLPostingTDS AGLPostingDS, Int32 ALedgerNumber, ABatchRow ABatchToPost)
{
#region Validate Arguments
if (AGLPostingDS == null)
{
throw new EFinanceSystemDataObjectNullOrEmptyException(String.Format(Catalog.GetString(
"Function:{0} - The GL Posting dataset is null!"),
Utilities.GetMethodName(true)));
}
else 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 (ABatchToPost == null)
{
throw new EFinanceSystemDataObjectNullOrEmptyException(String.Format(Catalog.GetString(
"Function:{0} - The GL Batch to post data row is null!"),
Utilities.GetMethodName(true)));
}
#endregion Validate Arguments
GLPostingTDS GLPostingDS = AGLPostingDS;
TDBTransaction Transaction = null;
try
{
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
AGeneralLedgerMasterRow GLMTemplateRow = GLPostingDS.AGeneralLedgerMaster.NewRowTyped(false);
GLMTemplateRow.LedgerNumber = ALedgerNumber;
GLMTemplateRow.Year = ABatchToPost.BatchYear;
AGeneralLedgerMasterAccess.LoadUsingTemplate(GLPostingDS, GLMTemplateRow, Transaction);
string query = "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_ledger_number_i = ? " +
"AND PUB_a_general_ledger_master.a_year_i = ? " +
"AND PUB_a_general_ledger_master_period.a_glm_sequence_i = PUB_a_general_ledger_master.a_glm_sequence_i " +
"AND PUB_a_general_ledger_master_period.a_period_number_i >= ?";
List <OdbcParameter>parameters = new List <OdbcParameter>();
OdbcParameter parameter = new OdbcParameter("ledgernumber", OdbcType.Int);
parameter.Value = ALedgerNumber;
parameters.Add(parameter);
parameter = new OdbcParameter("year", OdbcType.Int);
parameter.Value = ABatchToPost.BatchYear;
parameters.Add(parameter);
parameter = new OdbcParameter("period", OdbcType.Int);
parameter.Value = ABatchToPost.BatchPeriod;
parameters.Add(parameter);
DBAccess.GDBAccessObj.Select(GLPostingDS,
query,
GLPostingDS.AGeneralLedgerMasterPeriod.TableName, Transaction, parameters.ToArray());
});
}
catch (Exception ex)
{
TLogging.Log(String.Format("Method:{0} - Unexpected error!{1}{1}{2}",
Utilities.GetMethodSignature(),
Environment.NewLine,
ex.Message));
throw ex;
}
}
/// <summary>
/// runs validations on batch, journals and transactions
/// some things are even modified, eg. batch period etc from date effective
/// </summary>
private static bool ValidateBatchAndTransactions(ref GLBatchTDS AGLBatchDS,
GLPostingTDS APostingDS,
Int32 ALedgerNumber,
ABatchRow ABatchToPost,
out TVerificationResultCollection AVerifications)
{
AVerifications = new TVerificationResultCollection();
TVerificationResultCollection Verifications = AVerifications;
if ((ABatchToPost.BatchStatus == MFinanceConstants.BATCH_CANCELLED) || (ABatchToPost.BatchStatus == MFinanceConstants.BATCH_POSTED))
{
AVerifications.Add(new TVerificationResult(
String.Format(Catalog.GetString("Cannot post Batch {0} in Ledger {1}"), ABatchToPost.BatchNumber, ALedgerNumber),
String.Format(Catalog.GetString("It has status {0}"), ABatchToPost.BatchStatus),
TResultSeverity.Resv_Critical));
}
// Calculate the base currency amounts for each transaction, using the exchange rate from the journals.
// erm - this is done already? I don't want to do it here, since my journal may contain forex-reval elements.
// Calculate the credit and debit totals
GLRoutines.UpdateTotalsOfBatch(ref AGLBatchDS, ABatchToPost);
if (ABatchToPost.BatchCreditTotal != ABatchToPost.BatchDebitTotal)
{
AVerifications.Add(new TVerificationResult(
String.Format(Catalog.GetString("Cannot post Batch {0} in Ledger {1}"), ABatchToPost.BatchNumber, ALedgerNumber),
String.Format(Catalog.GetString("It does not balance: Debit is {0:N2}, Credit is {1:N2}"), ABatchToPost.BatchDebitTotal,
ABatchToPost.BatchCreditTotal),
TResultSeverity.Resv_Critical));
}
else if ((ABatchToPost.BatchCreditTotal == 0) && ((AGLBatchDS.AJournal.Rows.Count == 0) || (AGLBatchDS.ATransaction.Rows.Count == 0)))
{
AVerifications.Add(new TVerificationResult(
String.Format(Catalog.GetString("Cannot post Batch {0} in Ledger {1}"), ABatchToPost.BatchNumber, ALedgerNumber),
Catalog.GetString("The batch has no monetary value. Please cancel it or add transactions."),
TResultSeverity.Resv_Critical));
}
else if ((ABatchToPost.BatchControlTotal != 0)
&& (ABatchToPost.BatchControlTotal != ABatchToPost.BatchCreditTotal))
{
AVerifications.Add(new TVerificationResult(
String.Format(Catalog.GetString("Cannot post Batch {0} in Ledger {1}"), ABatchToPost.BatchNumber, ALedgerNumber),
String.Format(Catalog.GetString("The control total {0:n2} does not fit the Credit/Debit Total {1:n2}."),
ABatchToPost.BatchControlTotal,
ABatchToPost.BatchCreditTotal),
TResultSeverity.Resv_Critical));
}
Int32 DateEffectivePeriodNumber, DateEffectiveYearNumber;
TDBTransaction Transaction = null;
GLBatchTDS GLBatchDS = AGLBatchDS;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
if (!TFinancialYear.IsValidPostingPeriod(ABatchToPost.LedgerNumber, ABatchToPost.DateEffective, out DateEffectivePeriodNumber,
out DateEffectiveYearNumber,
Transaction))
{
Verifications.Add(new TVerificationResult(
String.Format(Catalog.GetString("Cannot post Batch {0} in Ledger {1}"), ABatchToPost.BatchNumber, ALedgerNumber),
String.Format(Catalog.GetString("The Date Effective {0:d-MMM-yyyy} does not fit any open accounting period."),
ABatchToPost.DateEffective),
TResultSeverity.Resv_Critical));
}
else
{
// just make sure that the correct BatchPeriod is used
ABatchToPost.BatchPeriod = DateEffectivePeriodNumber;
ABatchToPost.BatchYear = DateEffectiveYearNumber;
}
// check that all transactions are inside the same period as the GL date effective of the batch
DateTime PostingPeriodStartDate, PostingPeriodEndDate;
TFinancialYear.GetStartAndEndDateOfPeriod(ABatchToPost.LedgerNumber,
DateEffectivePeriodNumber,
out PostingPeriodStartDate,
out PostingPeriodEndDate,
Transaction);
foreach (ATransactionRow transRow in GLBatchDS.ATransaction.Rows)
{
if ((transRow.BatchNumber == ABatchToPost.BatchNumber)
&& (transRow.TransactionDate < PostingPeriodStartDate) || (transRow.TransactionDate > PostingPeriodEndDate))
{
Verifications.Add(new TVerificationResult(
String.Format(Catalog.GetString("Cannot post Batch {0} in Ledger {1}"), ABatchToPost.BatchNumber, ALedgerNumber),
String.Format(
"invalid transaction date for transaction {0} in Batch {1} Journal {2}: {3:d-MMM-yyyy} must be inside period {4} ({5:d-MMM-yyyy} till {6:d-MMM-yyyy})",
transRow.TransactionNumber, transRow.BatchNumber, transRow.JournalNumber,
transRow.TransactionDate,
DateEffectivePeriodNumber,
PostingPeriodStartDate,
PostingPeriodEndDate),
TResultSeverity.Resv_Critical));
}
}
});
AVerifications = Verifications;
DataView TransactionsOfJournalView = new DataView(AGLBatchDS.ATransaction);
foreach (AJournalRow journal in AGLBatchDS.AJournal.Rows)
{
journal.DateEffective = ABatchToPost.DateEffective;
journal.JournalPeriod = ABatchToPost.BatchPeriod;
if (journal.JournalCreditTotal != journal.JournalDebitTotal)
{
AVerifications.Add(new TVerificationResult(
String.Format(Catalog.GetString("Cannot post Batch {0} in Ledger {1}"), ABatchToPost.BatchNumber, ALedgerNumber),
String.Format(Catalog.GetString("The journal {0} does not balance: Debit is {1:N2}, Credit is {2:N2}"),
journal.JournalNumber,
journal.JournalDebitTotal, journal.JournalCreditTotal),
TResultSeverity.Resv_Critical));
}
TransactionsOfJournalView.RowFilter = ATransactionTable.GetJournalNumberDBName() + " = " + journal.JournalNumber.ToString();
foreach (DataRowView TransactionViewRow in TransactionsOfJournalView)
{
ATransactionRow transaction = (ATransactionRow)TransactionViewRow.Row;
// check that transactions on foreign currency accounts are using the correct currency
// (fx reval transactions are an exception because they are posted in base currency)
if (!((transaction.Reference == CommonAccountingTransactionTypesEnum.REVAL.ToString())
&& (journal.TransactionTypeCode == CommonAccountingTransactionTypesEnum.REVAL.ToString())))
{
// get the account that this transaction is writing to
AAccountRow Account = (AAccountRow)APostingDS.AAccount.Rows.Find(new object[] { ALedgerNumber, transaction.AccountCode });
if (Account == null)
{
// should not get here
throw new Exception("ValidateBatchAndTransactions: Cannot find account " + transaction.AccountCode);
}
if (Account.ForeignCurrencyFlag && (journal.TransactionCurrency != Account.ForeignCurrencyCode))
{
AVerifications.Add(new TVerificationResult(
String.Format(Catalog.GetString("Cannot post Batch {0} in Ledger {1}"), ABatchToPost.BatchNumber, ALedgerNumber),
String.Format(Catalog.GetString(
"Transaction {0} in Journal {1} with currency {2} does not fit the foreign currency {3} of account {4}."),
transaction.TransactionNumber, transaction.JournalNumber, journal.TransactionCurrency,
Account.ForeignCurrencyCode,
transaction.AccountCode),
TResultSeverity.Resv_Critical));
}
}
if ((transaction.AmountInBaseCurrency == 0) && (transaction.TransactionAmount != 0))
{
AVerifications.Add(new TVerificationResult(
String.Format(Catalog.GetString("Cannot post Batch {0} in Ledger {1}"), ABatchToPost.BatchNumber, ALedgerNumber),
String.Format(Catalog.GetString("Transaction {0} in Journal {1} has invalid base transaction amount of 0."),
transaction.TransactionNumber, transaction.JournalNumber),
TResultSeverity.Resv_Critical));
}
}
}
return TVerificationHelper.IsNullOrOnlyNonCritical(AVerifications);
}
/// <summary>
/// creates the rows for the whole current year in AGeneralLedgerMaster and AGeneralLedgerMasterPeriod for an Account/CostCentre combination
/// </summary>
/// <param name="AMainDS"></param>
/// <param name="ALedgerNumber"></param>
/// <param name="AAccountCode"></param>
/// <param name="ACostCentreCode"></param>
/// <returns>The new glm sequence, which is negative until SubmitChanges</returns>
private static Int32 CreateGLMYear(
ref GLPostingTDS AMainDS,
Int32 ALedgerNumber,
string AAccountCode,
string ACostCentreCode)
{
#region Validate Arguments
if (AMainDS == null)
{
throw new EFinanceSystemDataObjectNullOrEmptyException(String.Format(Catalog.GetString(
"Function:{0} - The GL Posting dataset is null!"),
Utilities.GetMethodName(true)));
}
else 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 ((AMainDS.ALedger == null) || (AMainDS.ALedger.Count == 0))
{
throw new EFinanceSystemDataObjectNullOrEmptyException(String.Format(Catalog.GetString(
"Function:{0} - The Ledger table does not exist or is empty!"),
Utilities.GetMethodName(true)));
}
else if (AAccountCode.Length == 0)
{
throw new ArgumentException(String.Format(Catalog.GetString("Function:{0} - The Account code is empty!"),
Utilities.GetMethodName(true)));
}
else if (ACostCentreCode.Length == 0)
{
throw new ArgumentException(String.Format(Catalog.GetString("Function:{0} - The Cost Centre code is empty!"),
Utilities.GetMethodName(true)));
}
#endregion Validate Arguments
int RetVal = 0;
try
{
ALedgerRow LedgerRow = AMainDS.ALedger[0];
#region Validate Data
if (LedgerRow == null)
{
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
AGeneralLedgerMasterRow GLMRow = AMainDS.AGeneralLedgerMaster.NewRowTyped();
// row.GlmSequence will be set by SubmitChanges
GLMRow.GlmSequence = (AMainDS.AGeneralLedgerMaster.Count * -1) - 1;
GLMRow.LedgerNumber = ALedgerNumber;
GLMRow.Year = LedgerRow.CurrentFinancialYear;
GLMRow.AccountCode = AAccountCode;
GLMRow.CostCentreCode = ACostCentreCode;
AMainDS.AGeneralLedgerMaster.Rows.Add(GLMRow);
for (int PeriodCount = 1; PeriodCount < LedgerRow.NumberOfAccountingPeriods + LedgerRow.NumberFwdPostingPeriods + 1; PeriodCount++)
{
AGeneralLedgerMasterPeriodRow PeriodRow = AMainDS.AGeneralLedgerMasterPeriod.NewRowTyped();
PeriodRow.GlmSequence = GLMRow.GlmSequence;
PeriodRow.PeriodNumber = PeriodCount;
AMainDS.AGeneralLedgerMasterPeriod.Rows.Add(PeriodRow);
}
RetVal = GLMRow.GlmSequence;
}
catch (Exception ex)
{
TLogging.Log(String.Format("Method:{0} - Unexpected error!{1}{1}{2}",
Utilities.GetMethodSignature(),
Environment.NewLine,
ex.Message));
throw ex;
}
return RetVal;
}
/// <summary>
/// mark each journal, each transaction as being posted;
/// add sums for costcentre/account combinations
/// </summary>
/// <param name="MainDS">can contain several batches and journals and transactions</param>
/// <param name="APostingDS"></param>
/// <param name="APostingLevel">the balance changes at the posting level</param>
/// <param name="ABatchToPost">the batch to post</param>
/// <returns>a list with the sums for each costcentre/account combination</returns>
private static SortedList <string, TAmount>MarkAsPostedAndCollectData(GLBatchTDS MainDS,
GLPostingTDS APostingDS,
SortedList <string, TAmount>APostingLevel, ABatchRow ABatchToPost)
{
DataView myView = new DataView(MainDS.ATransaction);
myView.Sort = ATransactionTable.GetJournalNumberDBName();
foreach (AJournalRow journal in MainDS.AJournal.Rows)
{
if (journal.BatchNumber != ABatchToPost.BatchNumber)
{
continue;
}
foreach (DataRowView transactionview in myView.FindRows(journal.JournalNumber))
{
ATransactionRow transaction = (ATransactionRow)transactionview.Row;
if (transaction.BatchNumber != ABatchToPost.BatchNumber)
{
continue;
}
transaction.TransactionStatus = true;
// get the account that this transaction is writing to
AAccountRow Account = (AAccountRow)APostingDS.AAccount.Rows.Find(new object[] { transaction.LedgerNumber, transaction.AccountCode });
// Set the sign of the amounts according to the debit/credit indicator
decimal SignBaseAmount = transaction.AmountInBaseCurrency;
decimal SignTransAmount = transaction.TransactionAmount;
if (Account.DebitCreditIndicator != transaction.DebitCreditIndicator)
{
SignBaseAmount *= -1.0M;
SignTransAmount *= -1.0M;
}
// TODO: do we need to check for base currency corrections?
// or do we get rid of these problems by not having international currency?
string key = TAmount.MakeKey(transaction.AccountCode, transaction.CostCentreCode);
if (!APostingLevel.ContainsKey(key))
{
APostingLevel.Add(key, new TAmount());
}
APostingLevel[key].baseAmount += SignBaseAmount;
// Only foreign currency accounts store a value in the transaction currency,
// if the transaction was actually in the foreign currency.
if (Account.ForeignCurrencyFlag && (journal.TransactionCurrency == Account.ForeignCurrencyCode))
{
APostingLevel[key].transAmount += SignTransAmount;
}
}
journal.JournalStatus = MFinanceConstants.BATCH_POSTED;
}
ABatchToPost.BatchStatus = MFinanceConstants.BATCH_POSTED;
return APostingLevel;
}
public static TSubmitChangesResult SaveGLBatchTDS(ref GLBatchTDS AInspectDS,
out TVerificationResultCollection AVerificationResult)
{
AVerificationResult = new TVerificationResultCollection();
TVerificationResultCollection VerificationResult = AVerificationResult;
// make sure that empty tables are removed. This can return NULL!!
AInspectDS = AInspectDS.GetChangesTyped(true);
if (AInspectDS == null)
{
AVerificationResult.Add(new TVerificationResult(
Catalog.GetString("Save GL Batch"),
Catalog.GetString("No changes - nothing to do"),
TResultSeverity.Resv_Info));
return TSubmitChangesResult.scrNothingToBeSaved;
}
bool AllValidationsOK = true;
bool GLBatchTableInDataSet = (AInspectDS.ABatch != null && AInspectDS.ABatch.Count > 0);
bool GLJournalTableInDataSet = (AInspectDS.AJournal != null && AInspectDS.AJournal.Count > 0);
bool GLTransTableInDataSet = (AInspectDS.ATransaction != null && AInspectDS.ATransaction.Count > 0);
bool GLTransAttrTableInDataSet = (AInspectDS.ATransAnalAttrib != null && AInspectDS.ATransAnalAttrib.Count > 0);
bool RecurrGLBatchTableInDataSet = (AInspectDS.ARecurringBatch != null && AInspectDS.ARecurringBatch.Count > 0);
bool RecurrGLJournalTableInDataSet = (AInspectDS.ARecurringJournal != null && AInspectDS.ARecurringJournal.Count > 0);
bool RecurrGLTransTableInDataSet = (AInspectDS.ARecurringTransaction != null && AInspectDS.ARecurringTransaction.Count > 0);
bool RecurrGLAttrTableInDataSet = (AInspectDS.ARecurringTransAnalAttrib != null && AInspectDS.ARecurringTransAnalAttrib.Count > 0);
//Check if saving recurring tables
if (RecurrGLBatchTableInDataSet || RecurrGLJournalTableInDataSet || RecurrGLTransTableInDataSet || RecurrGLAttrTableInDataSet)
{
if (GLBatchTableInDataSet || GLJournalTableInDataSet || GLTransTableInDataSet || GLTransAttrTableInDataSet)
{
throw new Exception(String.Format("Function:{0} - Recurring and normal GL data found in same changes batch!",
Utilities.GetMethodName(true)));
}
return SaveRecurringGLBatchTDS(ref AInspectDS,
ref AVerificationResult,
RecurrGLBatchTableInDataSet,
RecurrGLJournalTableInDataSet,
RecurrGLTransTableInDataSet,
RecurrGLAttrTableInDataSet);
}
else
{
if (!(GLBatchTableInDataSet || GLJournalTableInDataSet || GLTransTableInDataSet || GLTransAttrTableInDataSet))
{
throw new Exception(String.Format("Function:{0} - No GL data changes to save!", Utilities.GetMethodName(true)));
}
}
GLBatchTDS InspectDS = AInspectDS;
List <Int32>ListAllGLBatchesToProcess = new List <int>();
Int32 LedgerNumber = -1;
TDBTransaction Transaction = null;
try
{
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.Serializable,
ref Transaction,
delegate
{
if (GLBatchTableInDataSet)
{
DataView AllBatchesToProcess = new DataView(InspectDS.ABatch);
AllBatchesToProcess.RowStateFilter = DataViewRowState.OriginalRows | DataViewRowState.Added;
foreach (DataRowView drv in AllBatchesToProcess)
{
ABatchRow glbr = (ABatchRow)drv.Row;
int batchNumber;
if (glbr.RowState != DataRowState.Deleted)
{
LedgerNumber = glbr.LedgerNumber;
batchNumber = glbr.BatchNumber;
}
else
{
LedgerNumber = (Int32)glbr[ABatchTable.ColumnLedgerNumberId, DataRowVersion.Original];
batchNumber = (Int32)glbr[ABatchTable.ColumnBatchNumberId, DataRowVersion.Original];
}
if (!ListAllGLBatchesToProcess.Contains(batchNumber))
{
ListAllGLBatchesToProcess.Add(batchNumber);
}
int periodNumber, yearNumber;
if (TFinancialYear.IsValidPostingPeriod(LedgerNumber,
glbr.DateEffective,
out periodNumber,
out yearNumber,
Transaction))
{
glbr.BatchYear = yearNumber;
glbr.BatchPeriod = periodNumber;
}
}
//TODO add validation as with gift
//ValidateGiftDetail(ref AVerificationResult, AInspectDS.AGiftDetail);
//ValidateGiftDetailManual(ref AVerificationResult, AInspectDS.AGiftDetail);
if (!TVerificationHelper.IsNullOrOnlyNonCritical(VerificationResult))
{
AllValidationsOK = false;
}
}
if (GLJournalTableInDataSet)
{
DataView AllBatchesToProcess = new DataView(InspectDS.AJournal);
AllBatchesToProcess.RowStateFilter = DataViewRowState.OriginalRows | DataViewRowState.Added;
foreach (DataRowView drv in AllBatchesToProcess)
{
GLBatchTDSAJournalRow gljr = (GLBatchTDSAJournalRow)drv.Row;
int batchNumber;
if (gljr.RowState != DataRowState.Deleted)
{
if (LedgerNumber == -1)
{
LedgerNumber = gljr.LedgerNumber;
}
batchNumber = gljr.BatchNumber;
}
else
{
if (LedgerNumber == -1)
{
LedgerNumber = (Int32)gljr[AJournalTable.ColumnLedgerNumberId, DataRowVersion.Original];
}
batchNumber = (Int32)gljr[AJournalTable.ColumnBatchNumberId, DataRowVersion.Original];
}
if (!ListAllGLBatchesToProcess.Contains(batchNumber))
{
ListAllGLBatchesToProcess.Add(batchNumber);
}
}
//TODO add validation as with gift
//ValidateGiftDetail(ref AVerificationResult, AInspectDS.AGiftDetail);
//ValidateGiftDetailManual(ref AVerificationResult, AInspectDS.AGiftDetail);
if (!TVerificationHelper.IsNullOrOnlyNonCritical(VerificationResult))
{
AllValidationsOK = false;
}
}
if (GLTransTableInDataSet)
{
DataView AllBatchesToProcess = new DataView(InspectDS.ATransaction);
AllBatchesToProcess.RowStateFilter = DataViewRowState.OriginalRows | DataViewRowState.Added;
GLPostingTDS accountsAndCostCentresDS = new GLPostingTDS();
foreach (DataRowView drv in AllBatchesToProcess)
{
ATransactionRow gltr = (ATransactionRow)drv.Row;
int batchNumber;
if (gltr.RowState != DataRowState.Deleted)
{
if (LedgerNumber == -1)
{
LedgerNumber = gltr.LedgerNumber;
}
batchNumber = gltr.BatchNumber;
//Prepare to test for valid account and cost centre code
if ((accountsAndCostCentresDS.AAccount == null) || (accountsAndCostCentresDS.AAccount.Count == 0))
{
AAccountAccess.LoadViaALedger(accountsAndCostCentresDS, LedgerNumber, Transaction);
ACostCentreAccess.LoadViaALedger(accountsAndCostCentresDS, LedgerNumber, Transaction);
#region Validate Data
if ((accountsAndCostCentresDS.AAccount == null) || (accountsAndCostCentresDS.AAccount.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),
LedgerNumber));
}
else if ((accountsAndCostCentresDS.ACostCentre == null) || (accountsAndCostCentresDS.ACostCentre.Count == 0))
{
throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString(
"Function:{0} - Cost Centre data for Ledger number {1} does not exist or could not be accessed!"),
Utilities.GetMethodName(true),
LedgerNumber));
}
#endregion Validate Data
}
CheckTransactionAccountAndCostCentre(LedgerNumber, ref accountsAndCostCentresDS, ref gltr, ref VerificationResult);
}
else
{
if (LedgerNumber == -1)
{
LedgerNumber = (Int32)gltr[ATransactionTable.ColumnLedgerNumberId, DataRowVersion.Original];
}
batchNumber = (Int32)gltr[ATransactionTable.ColumnBatchNumberId, DataRowVersion.Original];
}
if (!ListAllGLBatchesToProcess.Contains(batchNumber))
{
ListAllGLBatchesToProcess.Add(batchNumber);
}
}
//TODO add validation as with gift
//ValidateGiftDetail(ref AVerificationResult, AInspectDS.AGiftDetail);
//ValidateGiftDetailManual(ref AVerificationResult, AInspectDS.AGiftDetail);
if (!TVerificationHelper.IsNullOrOnlyNonCritical(VerificationResult))
{
AllValidationsOK = false;
}
}
if (GLTransAttrTableInDataSet)
{
DataView AllBatchesToProcess = new DataView(InspectDS.ATransAnalAttrib);
AllBatchesToProcess.RowStateFilter = DataViewRowState.OriginalRows | DataViewRowState.Added;
foreach (DataRowView drv in AllBatchesToProcess)
{
ATransAnalAttribRow glta = (ATransAnalAttribRow)drv.Row;
int batchNumber;
if (glta.RowState != DataRowState.Deleted)
{
if (LedgerNumber == -1)
{
LedgerNumber = glta.LedgerNumber;
}
batchNumber = glta.BatchNumber;
}
else
{
if (LedgerNumber == -1)
{
LedgerNumber = (Int32)glta[ATransAnalAttribTable.ColumnLedgerNumberId, DataRowVersion.Original];
}
batchNumber = (Int32)glta[ATransAnalAttribTable.ColumnBatchNumberId, DataRowVersion.Original];
}
if (!ListAllGLBatchesToProcess.Contains(batchNumber))
{
ListAllGLBatchesToProcess.Add(batchNumber);
}
}
//TODO add validation as with gift
//ValidateGiftDetail(ref AVerificationResult, AInspectDS.AGiftDetail);
//ValidateGiftDetailManual(ref AVerificationResult, AInspectDS.AGiftDetail);
if (!TVerificationHelper.IsNullOrOnlyNonCritical(VerificationResult))
{
AllValidationsOK = false;
}
}
// load previously stored batches and check for posted status
if (ListAllGLBatchesToProcess.Count == 0)
{
VerificationResult.Add(new TVerificationResult(Catalog.GetString("Saving Batch"),
Catalog.GetString("Cannot save an empty Batch!"),
TResultSeverity.Resv_Critical));
}
else
{
string listOfBatchNumbers = string.Empty;
foreach (Int32 batchNumber in ListAllGLBatchesToProcess)
{
listOfBatchNumbers = StringHelper.AddCSV(listOfBatchNumbers, batchNumber.ToString());
}
string SQLStatement = "SELECT * " +
" FROM PUB_" + ABatchTable.GetTableDBName() + " WHERE " + ABatchTable.GetLedgerNumberDBName() +
" = " +
LedgerNumber.ToString() +
" AND " + ABatchTable.GetBatchNumberDBName() + " IN (" + listOfBatchNumbers + ")";
GLBatchTDS BatchDS = new GLBatchTDS();
DBAccess.GDBAccessObj.Select(BatchDS, SQLStatement, BatchDS.ABatch.TableName, Transaction);
foreach (ABatchRow batch in BatchDS.ABatch.Rows)
{
if ((batch.BatchStatus == MFinanceConstants.BATCH_POSTED)
|| (batch.BatchStatus == MFinanceConstants.BATCH_CANCELLED))
{
VerificationResult.Add(new TVerificationResult(Catalog.GetString("Saving Batch"),
String.Format(Catalog.GetString("Cannot modify Batch {0} because it is {1}"),
batch.BatchNumber, batch.BatchStatus),
TResultSeverity.Resv_Critical));
}
}
}
});
}
catch (Exception ex)
{
TLogging.Log(String.Format("Method:{0} - Unexpected error!{1}{1}{2}",
Utilities.GetMethodSignature(),
Environment.NewLine,
ex.Message));
throw ex;
}
if (AVerificationResult.Count > 0)
{
// Downgrade TScreenVerificationResults to TVerificationResults in order to allow
// Serialisation (needed for .NET Remoting).
TVerificationResultCollection.DowngradeScreenVerificationResults(AVerificationResult);
}
if (!TVerificationHelper.IsNullOrOnlyNonCritical(AVerificationResult))
{
return TSubmitChangesResult.scrError;
}
TSubmitChangesResult SubmissionResult;
if (AllValidationsOK)
{
//Need to save changes before deleting any transactions
GLBatchTDSAccess.SubmitChanges(AInspectDS);
SubmissionResult = TSubmitChangesResult.scrOK;
}
else
{
SubmissionResult = TSubmitChangesResult.scrError;
}
return SubmissionResult;
}
/// <summary>
/// Calculate the summarization trees for each posting account and each
/// posting cost centre. The result of the union of these trees,
/// excluding the base posting/posting combination, is the set of
/// accounts that receive the summary data.
/// </summary>
private static bool CalculateTrees(
Int32 ALedgerNumber,
ref SortedList <string, TAmount>APostingLevel,
out SortedList <string, TAccountTreeElement>AAccountTree,
out SortedList <string, string>ACostCentreTree,
GLPostingTDS APostingDS)
{
// get all accounts that each posting level account is directly or indirectly posting to
AAccountTree = new SortedList <string, TAccountTreeElement>();
foreach (string PostingLevelKey in APostingLevel.Keys)
{
string AccountCode = TAmount.GetAccountCode(PostingLevelKey);
// only once for each account, even though there might be several entries for one account in APostingLevel because of different costcentres
if (AAccountTree.ContainsKey(TAccountTreeElement.MakeKey(AccountCode, AccountCode)))
{
continue;
}
AAccountRow Account = (AAccountRow)APostingDS.AAccount.Rows.Find(new object[] { ALedgerNumber, AccountCode });
bool DebitCreditIndicator = Account.DebitCreditIndicator;
AAccountTree.Add(TAccountTreeElement.MakeKey(AccountCode, AccountCode),
new TAccountTreeElement(false, Account.ForeignCurrencyFlag));
AAccountHierarchyDetailRow HierarchyDetail =
(AAccountHierarchyDetailRow)APostingDS.AAccountHierarchyDetail.Rows.Find(
new object[] { ALedgerNumber, MFinanceConstants.ACCOUNT_HIERARCHY_STANDARD, AccountCode });
while (HierarchyDetail != null)
{
Account = (AAccountRow)APostingDS.AAccount.Rows.Find(new object[] { ALedgerNumber, HierarchyDetail.AccountCodeToReportTo });
if (Account == null)
{
// current account is BAL SHT, and it reports nowhere (account with name = ledgernumber does not exist)
break;
}
AAccountTree.Add(TAccountTreeElement.MakeKey(AccountCode, HierarchyDetail.AccountCodeToReportTo),
new TAccountTreeElement(DebitCreditIndicator != Account.DebitCreditIndicator, Account.ForeignCurrencyFlag));
HierarchyDetail = (AAccountHierarchyDetailRow)APostingDS.AAccountHierarchyDetail.Rows.Find(
new object[] { ALedgerNumber, MFinanceConstants.ACCOUNT_HIERARCHY_STANDARD, HierarchyDetail.AccountCodeToReportTo });
}
}
ACostCentreTree = new SortedList <string, string>();
foreach (string PostingLevelKey in APostingLevel.Keys)
{
string CostCentreCode = TAmount.GetCostCentreCode(PostingLevelKey);
// only once for each cost centre
if (ACostCentreTree.ContainsKey(CostCentreCode + ":" + CostCentreCode))
{
continue;
}
ACostCentreTree.Add(CostCentreCode + ":" + CostCentreCode,
CostCentreCode + ":" + CostCentreCode);
ACostCentreRow CostCentre = (ACostCentreRow)APostingDS.ACostCentre.Rows.Find(new object[] { ALedgerNumber, CostCentreCode });
while (!CostCentre.IsCostCentreToReportToNull())
{
ACostCentreTree.Add(CostCentreCode + ":" + CostCentre.CostCentreToReportTo,
CostCentreCode + ":" + CostCentre.CostCentreToReportTo);
CostCentre = (ACostCentreRow)APostingDS.ACostCentre.Rows.Find(new object[] { ALedgerNumber, CostCentre.CostCentreToReportTo });
}
}
return true;
}
private static void CheckTransactionAccountAndCostCentre(Int32 ALedgerNumber,
ref GLPostingTDS AAccountsAndCostCentresDS,
ref ATransactionRow ATransRow,
ref TVerificationResultCollection AVerificationResult)
{
// check for valid accounts and cost centres
if (AAccountsAndCostCentresDS.AAccount.Rows.Find(new object[] { ALedgerNumber, ATransRow.AccountCode }) == null)
{
AVerificationResult.Add(new TVerificationResult(
Catalog.GetString("Cannot save transaction"),
String.Format(Catalog.GetString("Invalid account code {0} in batch {1}, journal {2}, transaction {3}"),
ATransRow.AccountCode,
ATransRow.BatchNumber,
ATransRow.JournalNumber,
ATransRow.TransactionNumber),
TResultSeverity.Resv_Critical));
}
if (AAccountsAndCostCentresDS.ACostCentre.Rows.Find(new object[] { ALedgerNumber, ATransRow.CostCentreCode }) == null)
{
AVerificationResult.Add(new TVerificationResult(
Catalog.GetString("Cannot save transaction"),
String.Format(Catalog.GetString("Invalid cost centre code {0} in batch {1}, journal {2}, transaction {3}"),
ATransRow.CostCentreCode,
ATransRow.BatchNumber,
ATransRow.JournalNumber,
ATransRow.TransactionNumber),
TResultSeverity.Resv_Critical));
}
// AmountInBaseCurrency must be greater than 0.
// Transaction amount can be 0 if ForexGain.
if (ATransRow.AmountInBaseCurrency <= 0)
{
AVerificationResult.Add(new TVerificationResult(
Catalog.GetString("Cannot save transaction"),
String.Format(Catalog.GetString("Invalid amount in batch {0}, journal {1}, transaction {2}. " +
"Either the debit amount or the credit amount needs to be greater than 0."),
ATransRow.BatchNumber,
ATransRow.JournalNumber,
ATransRow.TransactionNumber),
TResultSeverity.Resv_Critical));
}
}
/// <summary>
/// on the posting level propagate the value from the posting period through the following periods;
/// in this version of SummarizeData, there is no calculation of summary accounts/cost centres, since that can be done by the reports
/// </summary>
private static bool SummarizeDataSimple(
Int32 ALedgerNumber,
GLPostingTDS AMainDS,
Int32 AFromPeriod,
ref SortedList <string, TAmount>APostingLevel)
{
if (AMainDS.ALedger[0].ProvisionalYearEndFlag)
{
// If the year end close is running, then we are posting the year end
// reallocations. These appear as part of the final period, but
// should only be written to the forward periods.
// In year end, a_current_period_i = a_number_of_accounting_periods_i = a_batch_period_i.
AFromPeriod++;
}
DataView GLMMasterView = AMainDS.AGeneralLedgerMaster.DefaultView;
GLMMasterView.Sort = AGeneralLedgerMasterTable.GetAccountCodeDBName() + "," + AGeneralLedgerMasterTable.GetCostCentreCodeDBName();
DataView GLMPeriodView = AMainDS.AGeneralLedgerMasterPeriod.DefaultView;
GLMPeriodView.Sort = AGeneralLedgerMasterPeriodTable.GetGlmSequenceDBName() + "," + AGeneralLedgerMasterPeriodTable.GetPeriodNumberDBName();
// Loop through the posting data collected earlier. Summarize it to a
// temporary table, which is much faster than finding and updating records
// in the glm tables multiple times. WriteData will write it to the real
// tables in a single pass.
foreach (string PostingLevelKey in APostingLevel.Keys)
{
string AccountCode = TAmount.GetAccountCode(PostingLevelKey);
string CostCentreCode = TAmount.GetCostCentreCode(PostingLevelKey);
TAmount PostingLevelElement = APostingLevel[PostingLevelKey];
// Find the posting level, creating it if it does not already exist.
int GLMMasterIndex = GLMMasterView.Find(new object[] { AccountCode, CostCentreCode });
AGeneralLedgerMasterRow GlmRow;
if (GLMMasterIndex == -1)
{
CreateGLMYear(
ref AMainDS,
ALedgerNumber,
AccountCode,
CostCentreCode);
GLMMasterIndex = GLMMasterView.Find(new object[] { AccountCode, CostCentreCode });
}
GlmRow = (AGeneralLedgerMasterRow)GLMMasterView[GLMMasterIndex].Row;
GlmRow.YtdActualBase += PostingLevelElement.baseAmount;
AAccountRow account = (AAccountRow)AMainDS.AAccount.Rows.Find(new object[] { ALedgerNumber, AccountCode });
if (account.ForeignCurrencyFlag)
{
if (GlmRow.IsYtdActualForeignNull())
{
GlmRow.YtdActualForeign = PostingLevelElement.transAmount;
}
else
{
GlmRow.YtdActualForeign += PostingLevelElement.transAmount;
}
}
if (AMainDS.ALedger[0].ProvisionalYearEndFlag)
{
GlmRow.ClosingPeriodActualBase += PostingLevelElement.baseAmount;
} // Last use of GlmRow in this routine ...
// propagate the data through the following periods
for (Int32 PeriodCount = AFromPeriod;
PeriodCount <= AMainDS.ALedger[0].NumberOfAccountingPeriods + AMainDS.ALedger[0].NumberFwdPostingPeriods;
PeriodCount++)
{
int GLMPeriodIndex = GLMPeriodView.Find(new object[] { GlmRow.GlmSequence, PeriodCount });
AGeneralLedgerMasterPeriodRow GlmPeriodRow;
if (GLMPeriodIndex == -1)
{
GlmPeriodRow = AMainDS.AGeneralLedgerMasterPeriod.NewRowTyped();
GlmPeriodRow.GlmSequence = GlmRow.GlmSequence;
GlmPeriodRow.PeriodNumber = PeriodCount;
}
else
{
GlmPeriodRow = (AGeneralLedgerMasterPeriodRow)GLMPeriodView[GLMPeriodIndex].Row;
}
GlmPeriodRow.ActualBase += PostingLevelElement.baseAmount;
if (account.ForeignCurrencyFlag)
{
if (GlmPeriodRow.IsActualForeignNull())
{
GlmPeriodRow.ActualForeign = PostingLevelElement.transAmount;
}
else
{
GlmPeriodRow.ActualForeign += PostingLevelElement.transAmount;
}
}
}
}
GLMMasterView.Sort = "";
GLMPeriodView.Sort = "";
return true;
}
private static void SummarizeInternal(Int32 ALedgerNumber,
GLPostingTDS APostingDS,
SortedList <string, TAmount>APostingLevel,
Int32 AFromPeriod,
bool ACalculatePostingTree)
{
// we need the tree, because of the cost centre tree, which is not calculated by the balance sheet and other reports.
// for testing the balances, we don't need to calculate the whole tree
if (ACalculatePostingTree)
{
TLogging.LogAtLevel(POSTING_LOGLEVEL, "Posting: CalculateTrees...");
// key is PostingAccount, the value TAccountTreeElement describes the parent account and other details of the relation
SortedList <string, TAccountTreeElement>AccountTree;
// key is the PostingCostCentre, the value is the parent Cost Centre
SortedList <string, string>CostCentreTree;
// TODO Can anything of this be done in StoredProcedures? Only SQLite here?
// this was in Petra 2.x; takes a lot of time, which the reports could do better
// TODO: can we just calculate the cost centre tree, since that is needed for Balance Sheet,
// but avoid calculating the whole account tree?
CalculateTrees(ALedgerNumber, ref APostingLevel, out AccountTree, out CostCentreTree, APostingDS);
TLogging.LogAtLevel(POSTING_LOGLEVEL, "Posting: SummarizeData...");
SummarizeData(APostingDS, AFromPeriod, ref APostingLevel, ref AccountTree, ref CostCentreTree);
}
else
{
TLogging.LogAtLevel(POSTING_LOGLEVEL, "Posting: SummarizeDataSimple...");
SummarizeDataSimple(ALedgerNumber, APostingDS, AFromPeriod, ref APostingLevel);
}
}
public static TSubmitChangesResult SaveGLBatchTDS(ref GLBatchTDS AInspectDS,
out TVerificationResultCollection AVerificationResult)
{
AVerificationResult = new TVerificationResultCollection();
TVerificationResultCollection VerificationResult = AVerificationResult;
// make sure that empty tables are removed
AInspectDS = AInspectDS.GetChangesTyped(true);
bool batchTableInDataSet = (AInspectDS.ABatch != null);
bool journalTableInDataSet = (AInspectDS.AJournal != null);
bool transTableInDataSet = (AInspectDS.ATransaction != null);
bool attrTableInDataSet = (AInspectDS.ATransAnalAttrib != null);
bool recurrBatchTableInDataSet = (AInspectDS.ARecurringBatch != null);
bool recurrJournalTableInDataSet = (AInspectDS.ARecurringJournal != null);
bool recurrTransTableInDataSet = (AInspectDS.ARecurringTransaction != null);
//bool newTransaction = false;
TDBTransaction Transaction = null;
GLBatchTDS InspectDS = AInspectDS;
// calculate debit and credit sums for journal and batch? but careful: we only have the changed parts!
// no, we calculate the debit and credit sums before the posting, with GLRoutines.UpdateTotalsOfBatch
// check added and modified and deleted rows: are they related to a posted or cancelled batch? we must not save adjusted posted batches!
List <Int32>BatchNumbersInvolved = new List <int>();
Int32 LedgerNumber = -1;
//Check if saving recurring tables
if (recurrBatchTableInDataSet
|| recurrJournalTableInDataSet
|| recurrTransTableInDataSet)
{
if (batchTableInDataSet || journalTableInDataSet || transTableInDataSet || attrTableInDataSet)
{
throw new Exception(
"SaveGLBatchTDS: need to call GetChangesTyped before saving, otherwise confusion about recurring or normal gl batch");
}
return SaveRecurringGLBatchTDS(ref AInspectDS);
}
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.Serializable,
ref Transaction,
delegate
{
if (batchTableInDataSet)
{
LedgerNumber = ((ABatchRow)InspectDS.ABatch.Rows[0]).LedgerNumber;
foreach (ABatchRow batch in InspectDS.ABatch.Rows)
{
if (batch.RowState != DataRowState.Added)
{
Int32 BatchNumber;
try
{
BatchNumber = batch.BatchNumber;
}
catch (Exception)
{
// for deleted batches
BatchNumber = (Int32)batch[ABatchTable.ColumnBatchNumberId, DataRowVersion.Original];
}
if (!BatchNumbersInvolved.Contains(BatchNumber))
{
BatchNumbersInvolved.Add(BatchNumber);
}
}
int PeriodNumber, YearNr;
if (TFinancialYear.IsValidPostingPeriod(LedgerNumber,
batch.DateEffective,
out PeriodNumber,
out YearNr,
Transaction))
{
batch.BatchYear = YearNr;
batch.BatchPeriod = PeriodNumber;
}
}
}
if (journalTableInDataSet)
{
if (LedgerNumber == -1)
{
LedgerNumber = ((AJournalRow)InspectDS.AJournal.Rows[0]).LedgerNumber;
}
foreach (GLBatchTDSAJournalRow journal in InspectDS.AJournal.Rows)
{
Int32 BatchNumber;
try
{
BatchNumber = journal.BatchNumber;
LedgerNumber = journal.LedgerNumber;
}
catch (Exception)
{
// for deleted journals
BatchNumber = (Int32)journal[AJournalTable.ColumnBatchNumberId, DataRowVersion.Original];
LedgerNumber = (Int32)journal[AJournalTable.ColumnLedgerNumberId, DataRowVersion.Original];
}
if (!BatchNumbersInvolved.Contains(BatchNumber))
{
BatchNumbersInvolved.Add(BatchNumber);
}
}
}
if (transTableInDataSet)
{
if (LedgerNumber == -1)
{
LedgerNumber = ((ATransactionRow)InspectDS.ATransaction.Rows[0]).LedgerNumber;
}
GLPostingTDS TestAccountsAndCostCentres = new GLPostingTDS();
foreach (ATransactionRow transaction in InspectDS.ATransaction.Rows)
{
Int32 BatchNumber;
try
{
BatchNumber = transaction.BatchNumber;
LedgerNumber = transaction.LedgerNumber;
if (TestAccountsAndCostCentres.AAccount.Count == 0)
{
AAccountAccess.LoadViaALedger(TestAccountsAndCostCentres, LedgerNumber, Transaction);
ACostCentreAccess.LoadViaALedger(TestAccountsAndCostCentres, LedgerNumber, Transaction);
}
// TODO could check for active accounts and cost centres?
// check for valid accounts and cost centres
if (TestAccountsAndCostCentres.AAccount.Rows.Find(new object[] { LedgerNumber, transaction.AccountCode }) == null)
{
VerificationResult.Add(new TVerificationResult(
Catalog.GetString("Cannot save transaction"),
String.Format(Catalog.GetString("Invalid account code {0} in batch {1}, journal {2}, transaction {3}"),
transaction.AccountCode,
transaction.BatchNumber,
transaction.JournalNumber,
transaction.TransactionNumber),
TResultSeverity.Resv_Critical));
}
if (TestAccountsAndCostCentres.ACostCentre.Rows.Find(new object[] { LedgerNumber,
transaction.CostCentreCode }) == null)
{
VerificationResult.Add(new TVerificationResult(
Catalog.GetString("Cannot save transaction"),
String.Format(Catalog.GetString("Invalid cost centre code {0} in batch {1}, journal {2}, transaction {3}"),
transaction.CostCentreCode,
transaction.BatchNumber,
transaction.JournalNumber,
transaction.TransactionNumber),
TResultSeverity.Resv_Critical));
}
// AmountInBaseCurrency must be greater than 0.
// Transaction amount can be 0 if ForexGain.
if (transaction.AmountInBaseCurrency <= 0)
{
VerificationResult.Add(new TVerificationResult(
Catalog.GetString("Cannot save transaction"),
String.Format(Catalog.GetString("Invalid amount in batch {0}, journal {1}, transaction {2}. " +
"Either the debit amount or the credit amount needs to be greater than 0."),
transaction.BatchNumber,
transaction.JournalNumber,
transaction.TransactionNumber),
TResultSeverity.Resv_Critical));
}
}
catch (Exception)
{
// for deleted transactions
BatchNumber = (Int32)transaction[ATransactionTable.ColumnBatchNumberId, DataRowVersion.Original];
LedgerNumber = (Int32)transaction[ATransactionTable.ColumnLedgerNumberId, DataRowVersion.Original];
}
if (!BatchNumbersInvolved.Contains(BatchNumber))
{
BatchNumbersInvolved.Add(BatchNumber);
}
}
}
if (attrTableInDataSet)
{
foreach (ATransAnalAttribRow transAnalAttrib in InspectDS.ATransAnalAttrib.Rows)
{
Int32 BatchNumber;
if (transAnalAttrib.RowState != DataRowState.Deleted)
{
BatchNumber = transAnalAttrib.BatchNumber;
if (!BatchNumbersInvolved.Contains(BatchNumber))
{
BatchNumbersInvolved.Add(BatchNumber);
}
}
}
}
// load previously stored batches and check for posted status
if (BatchNumbersInvolved.Count == 0)
{
VerificationResult.Add(new TVerificationResult(Catalog.GetString("Saving Batch"),
Catalog.GetString("Cannot save an empty Batch!"),
TResultSeverity.Resv_Critical));
}
else
{
string ListOfBatchNumbers = string.Empty;
foreach (Int32 BatchNumber in BatchNumbersInvolved)
{
ListOfBatchNumbers = StringHelper.AddCSV(ListOfBatchNumbers, BatchNumber.ToString());
}
string SQLStatement = "SELECT * " +
" FROM PUB_" + ABatchTable.GetTableDBName() + " WHERE " + ABatchTable.GetLedgerNumberDBName() + " = " +
LedgerNumber.ToString() +
" AND " + ABatchTable.GetBatchNumberDBName() + " IN (" + ListOfBatchNumbers + ")";
GLBatchTDS BatchDS = new GLBatchTDS();
DBAccess.GDBAccessObj.Select(BatchDS, SQLStatement, BatchDS.ABatch.TableName, Transaction);
foreach (ABatchRow batch in BatchDS.ABatch.Rows)
{
if ((batch.BatchStatus == MFinanceConstants.BATCH_POSTED)
|| (batch.BatchStatus == MFinanceConstants.BATCH_CANCELLED))
{
VerificationResult.Add(new TVerificationResult(Catalog.GetString("Saving Batch"),
String.Format(Catalog.GetString("Cannot modify Batch {0} because it is {1}"),
batch.BatchNumber, batch.BatchStatus),
TResultSeverity.Resv_Critical));
}
}
}
});
AVerificationResult = VerificationResult;
if (!TVerificationHelper.IsNullOrOnlyNonCritical(AVerificationResult))
{
return TSubmitChangesResult.scrError;
}
//Need to save changes before deleting any transactions
GLBatchTDSAccess.SubmitChanges(AInspectDS);
TSubmitChangesResult SubmissionResult = TSubmitChangesResult.scrOK;
if ((transTableInDataSet) && (AInspectDS.ATransaction.Rows.Count > 0))
{
//Accept deletion of Attributes to allow deletion of transactions
if (attrTableInDataSet)
{
AInspectDS.ATransAnalAttrib.AcceptChanges();
}
AInspectDS.ATransaction.AcceptChanges();
if (AInspectDS.ATransaction.Count > 0)
{
ATransactionRow tranR = (ATransactionRow)AInspectDS.ATransaction.Rows[0];
Int32 currentLedger = tranR.LedgerNumber;
Int32 currentBatch = tranR.BatchNumber;
Int32 currentJournal = tranR.JournalNumber;
Int32 transToDelete = 0;
try
{
//Check if any records have been marked for deletion
DataRow[] foundTransactionForDeletion = AInspectDS.ATransaction.Select(String.Format("{0} = '{1}'",
ATransactionTable.GetSubTypeDBName(),
MFinanceConstants.MARKED_FOR_DELETION));
if (foundTransactionForDeletion.Length > 0)
{
ATransactionRow transRowClient = null;
for (int i = 0; i < foundTransactionForDeletion.Length; i++)
{
transRowClient = (ATransactionRow)foundTransactionForDeletion[i];
transToDelete = transRowClient.TransactionNumber;
TLogging.Log(String.Format("Transaction to Delete: {0} from Journal: {1} in Batch: {2}",
transToDelete,
currentJournal,
currentBatch));
transRowClient.Delete();
}
//Submit all changes
GLBatchTDSAccess.SubmitChanges(AInspectDS);
SubmissionResult = TSubmitChangesResult.scrOK;
}
}
catch (Exception ex)
{
TLogging.Log("Saving DataSet: " + ex.Message);
TLogging.Log(String.Format("Error trying to save transaction: {0} in Journal: {1}, Batch: {2}",
transToDelete,
currentJournal,
currentBatch
));
SubmissionResult = TSubmitChangesResult.scrError;
}
}
}
return SubmissionResult;
}
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;
}
