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>
/// Performs the ICH code to generate Stewardship Calculation.
/// Relates to gi3100.p
/// </summary>
/// <param name="ALedgerNumber">ICH Ledger number</param>
/// <param name="APeriodNumber">Period</param>
/// <param name="AICHNumber">ICH Processing Number</param>
/// <param name="ACurrencyType">Currency type: 1 = base, 2 = intl</param>
/// <param name="AFileName">File name to process</param>
/// <param name="AEmail">If true then send email</param>
/// <param name="AVerificationResult">Error messaging</param>
public static void GenerateStewardshipFile(int ALedgerNumber,
int APeriodNumber,
int AICHNumber,
int ACurrencyType,
string AFileName,
bool AEmail,
out TVerificationResultCollection AVerificationResult
)
{
string CostCentre;
decimal IncomeAmount = 0;
decimal ExpenseAmount = 0;
decimal XferAmount = 0;
string Currency;
string LedgerName;
AVerificationResult = new TVerificationResultCollection();
//Begin the transaction
TDBTransaction DBTransaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.Serializable);
try
{
//Find the LedgerRow
ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, DBTransaction);
ALedgerRow LedgerRow = (ALedgerRow)LedgerTable.Rows[0];
//Find the Partner Short Name
PPartnerTable PartnerTable = PPartnerAccess.LoadByPrimaryKey(LedgerRow.PartnerKey, DBTransaction);
PPartnerRow PartnerRow = (PPartnerRow)PartnerTable.Rows[0];
LedgerName = PartnerRow.PartnerShortName;
//Specify currency type
if (ACurrencyType == MFinanceConstants.CURRENCY_BASE_NUM)
{
Currency = LedgerRow.BaseCurrency;
}
else
{
Currency = LedgerRow.IntlCurrency;
}
//Create table for conversion to XML and export to CSV
AAccountingPeriodTable AccountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber, APeriodNumber, DBTransaction);
AAccountingPeriodRow AccountingPeriodRow = (AAccountingPeriodRow)AccountingPeriodTable.Rows[0];
DateTime PeriodEndDate = AccountingPeriodRow.PeriodEndDate;
string StandardCostCentre = TLedgerInfo.GetStandardCostCentre(ALedgerNumber);
DateTime DateToday = DateTime.Today;
//First four fields are constant for each row
DataTable TableForExport = new DataTable();
TableForExport.Columns.Add("PeriodEndDate", typeof(DateTime));
TableForExport.Columns.Add("StandardCostCentre", typeof(string));
TableForExport.Columns.Add("DateToday", typeof(DateTime));
TableForExport.Columns.Add("Currency", typeof(string));
TableForExport.Columns.Add("CostCentre", typeof(string));
TableForExport.Columns.Add("Income", typeof(decimal));
TableForExport.Columns.Add("Expense", typeof(decimal));
TableForExport.Columns.Add("DirectTransfer", typeof(decimal));
CostCentre = string.Empty;
AIchStewardshipTable IchStewTable = new AIchStewardshipTable();
AIchStewardshipRow TemplateRow = (AIchStewardshipRow)IchStewTable.NewRowTyped(false);
TemplateRow.LedgerNumber = ALedgerNumber;
TemplateRow.PeriodNumber = APeriodNumber;
StringCollection operators = StringHelper.InitStrArr(new string[] { "=", "=" });
AIchStewardshipTable IchStewardshipTable = AIchStewardshipAccess.LoadUsingTemplate(TemplateRow, operators, null, DBTransaction);
AIchStewardshipRow IchStewardshipRow = null;
for (int i = 0; i < IchStewardshipTable.Count; i++)
{
IchStewardshipRow = (AIchStewardshipRow)IchStewardshipTable.Rows[i];
if ((AICHNumber == 0)
|| (IchStewardshipRow.IchNumber == AICHNumber))
{
if ((CostCentre != string.Empty)
&& (CostCentre != IchStewardshipRow.CostCentreCode))
{
if ((IncomeAmount != 0) || (ExpenseAmount != 0) || (XferAmount != 0))
{
DataRow DR = (DataRow)TableForExport.NewRow();
DR[0] = PeriodEndDate;
DR[1] = StandardCostCentre;
DR[2] = DateToday;
DR[3] = Currency;
DR[4] = CostCentre;
DR[5] = IncomeAmount;
DR[6] = ExpenseAmount;
DR[7] = XferAmount;
TableForExport.Rows.Add(DR);
TableForExport.AcceptChanges();
}
IncomeAmount = 0;
ExpenseAmount = 0;
XferAmount = 0;
}
if (ACurrencyType == MFinanceConstants.CURRENCY_BASE_NUM)
{
IncomeAmount += IchStewardshipRow.IncomeAmount;
ExpenseAmount += IchStewardshipRow.ExpenseAmount;
XferAmount += IchStewardshipRow.DirectXferAmount;
}
else
{
IncomeAmount += IchStewardshipRow.IncomeAmountIntl;
ExpenseAmount += IchStewardshipRow.ExpenseAmountIntl;
XferAmount += IchStewardshipRow.DirectXferAmountIntl;
}
CostCentre = IchStewardshipRow.CostCentreCode;
}
}
if ((CostCentre != string.Empty) && ((IncomeAmount != 0) || (ExpenseAmount != 0) || (XferAmount != 0)))
{
DataRow DR = (DataRow)TableForExport.NewRow();
DR[0] = PeriodEndDate;
DR[1] = StandardCostCentre;
DR[2] = DateToday;
DR[3] = Currency;
DR[4] = CostCentre;
DR[5] = IncomeAmount;
DR[6] = ExpenseAmount;
DR[7] = XferAmount;
TableForExport.Rows.Add(DR);
TableForExport.AcceptChanges();
}
//Create the XMLDoc ready for export to CSV
XmlDocument doc = TDataBase.DataTableToXml(TableForExport);
TCsv2Xml.Xml2Csv(doc, AFileName);
if (AEmail)
{
string SenderAddress = TAppSettingsManager.GetValue("LocalFieldFinance.EmailAddress");
string EmailSubject = string.Format(Catalog.GetString("Stewardship File from {0}"), LedgerName);
string HTMLText = string.Empty;
string EmailAddress = GetICHEmailAddress(DBTransaction);
if (EmailAddress.Length == 0)
{
throw new Exception("No destination email addresses found!");
}
if (!File.Exists(AFileName))
{
HTMLText = "<html><body>" + String.Format(Catalog.GetString("Cannot find file {0}"), AFileName) + "</body></html>";
}
else
{
HTMLText = "<html><body>" + EmailSubject + ": " + Path.GetFileName(AFileName) + Catalog.GetString(" is attached.") +
"</body></html>";
}
TSmtpSender SendMail = new TSmtpSender();
MailMessage msg = new MailMessage(SenderAddress,
EmailAddress,
EmailSubject,
HTMLText);
msg.Attachments.Add(new Attachment(AFileName));
//msg.Bcc.Add(BCCAddress);
SendMail.SendMessage(msg);
}
}
catch (Exception Exp)
{
TLogging.Log(Exp.Message);
TLogging.Log(Exp.StackTrace);
throw;
}
finally
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
}