private static void GetAdminFees(Int32 ALedgerNumber,
Int32 ABatchNumber,
Int32 ATransactionNumber,
Int32 ADetailNumber,
out decimal GIFFee,
out decimal ICTFee,
out decimal OtherFee)
{
GIFFee = 0;
ICTFee = 0;
OtherFee = 0;
AProcessedFeeTable ProcessedFeeTbl = AProcessedFeeAccess.LoadViaAGiftDetail(ALedgerNumber,
ABatchNumber,
ATransactionNumber,
ADetailNumber,
FTransaction);
foreach (AProcessedFeeRow Row in ProcessedFeeTbl.Rows)
{
switch (Row.FeeCode)
{
case "ICT":
{
ICTFee += Row.PeriodicAmount;
break;
}
case "GIF":
{
GIFFee += Row.PeriodicAmount;
break;
}
default:
{
OtherFee += Row.PeriodicAmount;
break;
}
}
}
}
/// <summary>
/// Reads from the table holding all the fees charged for this month and generates a GL batch from it.
/// Relates to gl2150.p
/// </summary>
/// <param name="ALedgerNumber"></param>
/// <param name="APeriodNumber"></param>
/// <param name="APrintReport"></param>
/// <param name="ADBTransaction"></param>
/// <param name="AVerificationResult"></param>
/// <returns></returns>
private static bool GenerateAdminFeeBatch(int ALedgerNumber,
int APeriodNumber,
bool APrintReport,
TDBTransaction ADBTransaction,
ref TVerificationResultCollection AVerificationResult
)
{
bool IsSuccessful = false;
bool CreatedSuccessfully = false;
decimal TransactionAmount;
string DrAccountCode;
string DestCostCentreCode = string.Empty;
string DestAccountCode = string.Empty;
string FeeDescription = string.Empty;
decimal DrFeeTotal = 0;
bool DrCrIndicator = true;
//Error handling
string ErrorContext = String.Empty;
string ErrorMessage = String.Empty;
//Set default type as non-critical
TResultSeverity ErrorType = TResultSeverity.Resv_Noncritical;
/* Make a temporary table to hold totals for gifts going to
* each account. */
GLStewardshipCalculationTDSCreditFeeTotalTable CreditFeeTotalDT = new GLStewardshipCalculationTDSCreditFeeTotalTable();
//int x = CreditFeeTotalDT.Count;
/* Retrieve info on the ledger. */
ALedgerTable AvailableLedgers = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, ADBTransaction);
ALedgerRow LedgerRow = (ALedgerRow)AvailableLedgers.Rows[0];
try
{
/* Check that we have not closed all periods for the year yet.
* (Not at the provisional year end point) */
if (LedgerRow.ProvisionalYearEndFlag)
{
//Petra ErrorCode = GL0071
ErrorContext = Catalog.GetString("Generate Admin Fee Batch");
ErrorMessage = String.Format(Catalog.GetString(
"Cannot progress as Ledger {0} is at the provisional year-end point"), ALedgerNumber);
ErrorType = TResultSeverity.Resv_Critical;
throw new System.InvalidOperationException(ErrorMessage);
}
/* 0003 Finds for ledger base currency format, for report currency format */
ACurrencyTable CurrencyInfo = ACurrencyAccess.LoadByPrimaryKey(LedgerRow.BaseCurrency, ADBTransaction);
ACurrencyRow CurrencyRow = (ACurrencyRow)CurrencyInfo.Rows[0];
/* 0001 Extract number of decimal places */
string NumericFormat = CurrencyRow.DisplayFormat;
int NumDecPlaces = THelperNumeric.CalcNumericFormatDecimalPlaces(NumericFormat);
/* Create the journal to create the fee transactions in, if there are
* fees to charge.
* NOTE: if the date in the processed fee table is ? then that fee
* hasn't been processed. */
AProcessedFeeTable ProcessedFeeDataTable = new AProcessedFeeTable();
string sqlStmt = String.Format("SELECT * FROM {0} WHERE {1} = ? AND {2} = ? AND {3} IS NULL AND {4} <> 0 ORDER BY {5}, {6}",
AProcessedFeeTable.GetTableDBName(),
AProcessedFeeTable.GetLedgerNumberDBName(),
AProcessedFeeTable.GetPeriodNumberDBName(),
AProcessedFeeTable.GetProcessedDateDBName(),
AProcessedFeeTable.GetPeriodicAmountDBName(),
AProcessedFeeTable.GetFeeCodeDBName(),
AProcessedFeeTable.GetCostCentreCodeDBName()
);
OdbcParameter[] parameters = new OdbcParameter[2];
parameters[0] = new OdbcParameter("LedgerNumber", OdbcType.Int);
parameters[0].Value = ALedgerNumber;
parameters[1] = new OdbcParameter("PeriodNumber", OdbcType.Int);
parameters[1].Value = APeriodNumber;
DBAccess.GDBAccessObj.SelectDT(ProcessedFeeDataTable, sqlStmt, ADBTransaction, parameters, -1, -1);
if (ProcessedFeeDataTable.Count == 0)
{
if (TLogging.DebugLevel > 0)
{
TLogging.Log("No fees to charge were found");
AVerificationResult.Add(new TVerificationResult(Catalog.GetString("Admin Fee Batch"),
String.Format(Catalog.GetString("No admin fees charged in period {0}."), APeriodNumber),
TResultSeverity.Resv_Status));
}
IsSuccessful = true;
}
else
{
//Post to Ledger - Ln 132
//****************4GL Transaction Starts Here********************
AAccountingPeriodTable AccountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber,
APeriodNumber,
ADBTransaction);
AAccountingPeriodRow AccountingPeriodRow = (AAccountingPeriodRow)AccountingPeriodTable.Rows[0];
// Create a Batch. If no fees are to be charged, I'll delete this batch later.
GLBatchTDS AdminFeeDS = TGLPosting.CreateABatch(ALedgerNumber, Catalog.GetString(
"Admin Fees & Grants"), 0, AccountingPeriodRow.PeriodEndDate);
ABatchRow BatchRow = AdminFeeDS.ABatch[0];
AJournalRow JournalRow = AdminFeeDS.AJournal.NewRowTyped();
JournalRow.LedgerNumber = ALedgerNumber;
JournalRow.BatchNumber = BatchRow.BatchNumber;
JournalRow.JournalNumber = ++BatchRow.LastJournal;
JournalRow.JournalDescription = BatchRow.BatchDescription;
JournalRow.SubSystemCode = MFinanceConstants.SUB_SYSTEM_GL;
JournalRow.TransactionTypeCode = CommonAccountingTransactionTypesEnum.STD.ToString();
JournalRow.TransactionCurrency = LedgerRow.BaseCurrency;
JournalRow.ExchangeRateToBase = 1;
JournalRow.DateEffective = AccountingPeriodRow.PeriodEndDate;
JournalRow.JournalPeriod = APeriodNumber;
AdminFeeDS.AJournal.Rows.Add(JournalRow);
//
// Generate the transactions
//
/* M009 Changed the following loop for Petra 2.1 design changes. a_processed_fee
* now has a record for each gift detail so it is necessary to sum up all the
* totals for each fee code/cost centre so that only one transaction is posted
* for each */
int GLJournalNumber = JournalRow.JournalNumber;
int GLTransactionNumber = 0;
string CurrentFeeCode = string.Empty;
string CostCentreCode = string.Empty;
string CostCentreCodeDBName = AProcessedFeeTable.GetCostCentreCodeDBName();
for (int i = 0; i < ProcessedFeeDataTable.Count; i++)
{
AProcessedFeeRow pFR = (AProcessedFeeRow)ProcessedFeeDataTable.Rows[i];
if (CurrentFeeCode != pFR.FeeCode)
{
CurrentFeeCode = pFR.FeeCode;
// Find first
AFeesPayableTable FeesPayableTable = AFeesPayableAccess.LoadByPrimaryKey(ALedgerNumber, CurrentFeeCode, ADBTransaction);
if (FeesPayableTable.Count > 0) //if null try receivables instead
{
AFeesPayableRow FeesPayableRow = (AFeesPayableRow)FeesPayableTable.Rows[0];
DrAccountCode = FeesPayableRow.DrAccountCode;
DestCostCentreCode = FeesPayableRow.CostCentreCode;
DestAccountCode = FeesPayableRow.AccountCode;
FeeDescription = FeesPayableRow.FeeDescription;
}
else
{
AFeesReceivableTable FeesReceivableTable = AFeesReceivableAccess.LoadByPrimaryKey(ALedgerNumber,
CurrentFeeCode,
ADBTransaction);
if (FeesReceivableTable.Count > 0)
{
AFeesReceivableRow FeesReceivableRow = (AFeesReceivableRow)FeesReceivableTable.Rows[0];
DrAccountCode = FeesReceivableRow.DrAccountCode;
DestCostCentreCode = FeesReceivableRow.CostCentreCode;
DestAccountCode = FeesReceivableRow.AccountCode;
FeeDescription = FeesReceivableRow.FeeDescription;
}
else
{
//Petra error: X_0007
ErrorContext = Catalog.GetString("Generate Transactions");
ErrorMessage =
String.Format(Catalog.GetString(
"Unable to access information for Fee Code '{1}' in either the Fees Payable & Receivable Tables for Ledger {0}"),
ALedgerNumber, CurrentFeeCode);
ErrorType = TResultSeverity.Resv_Critical;
throw new System.InvalidOperationException(ErrorMessage);
}
}
DrFeeTotal = 0;
//Get all the distinct CostCentres
DataView CostCentreView = ProcessedFeeDataTable.DefaultView;
CostCentreView.Sort = CostCentreCodeDBName;
CostCentreView.RowFilter = string.Format("{0} = '{1}'", AProcessedFeeTable.GetFeeCodeDBName(), CurrentFeeCode);
DataTable ProcessedFeeCostCentresTable = CostCentreView.ToTable(true, CostCentreCodeDBName);
foreach (DataRow r in ProcessedFeeCostCentresTable.Rows)
{
CostCentreCode = r[0].ToString();
DataView view = ProcessedFeeDataTable.DefaultView;
view.Sort = CostCentreCodeDBName;
view.RowFilter = string.Format("{0} = '{1}' AND {2} = '{3}'",
AProcessedFeeTable.GetFeeCodeDBName(),
CurrentFeeCode,
CostCentreCodeDBName,
CostCentreCode);
//ProcessedFeeDataTable2 = ProcessedFeeDataTable2.Clone();
DataTable ProcessedFeeDataTable2 = view.ToTable();
Int32 FeeCodeRowCount = ProcessedFeeDataTable2.Rows.Count;
for (int j = 0; j < FeeCodeRowCount; j++)
{
DataRow pFR2 = ProcessedFeeDataTable2.Rows[j];
DrFeeTotal = DrFeeTotal + Math.Round(Convert.ToDecimal(
pFR2[AProcessedFeeTable.GetPeriodicAmountDBName()]), NumDecPlaces); //pFR2.PeriodicAmount; //ROUND(pFR2.PeriodicAmount,
// lv_dp)
if (j == (FeeCodeRowCount - 1)) //implies last of the CostCentre rows for this feecode
{
if (DrFeeTotal != 0)
{
if (DrFeeTotal < 0)
{
DrCrIndicator = false; //Credit
DrFeeTotal = -DrFeeTotal;
}
else
{
DrCrIndicator = true; //Debit
//lv_dr_fee_total remains unchanged
}
/*
* Generate the transaction to deduct the fee amount from the source cost centre. (Expense leg)
*/
//RUN gl1130o.p -> gl1130.i
//Create a transaction
if (!TGLPosting.CreateATransaction(AdminFeeDS,
ALedgerNumber,
BatchRow.BatchNumber,
GLJournalNumber,
"Fee: " + FeeDescription + " (" + CurrentFeeCode + ")",
DrAccountCode,
CostCentreCode,
DrFeeTotal,
AccountingPeriodRow.PeriodEndDate,
DrCrIndicator,
"AG",
true,
DrFeeTotal,
out GLTransactionNumber))
{
ErrorContext = Catalog.GetString("Generating the Admin Fee batch");
ErrorMessage =
String.Format(Catalog.GetString(
"Unable to create a new transaction for Ledger {0}, Batch {1} and Journal {2}."),
ALedgerNumber,
BatchRow.BatchNumber,
GLJournalNumber);
ErrorType = TResultSeverity.Resv_Noncritical;
throw new System.InvalidOperationException(ErrorMessage);
}
DrFeeTotal = 0;
}
}
}
}
}
/* Mark each fee entry as processed. */
pFR.ProcessedDate = DateTime.Today.Date;
pFR.Timestamp =
(DateTime.Today.TimeOfDay.Hours * 3600 + DateTime.Today.TimeOfDay.Minutes * 60 + DateTime.Today.TimeOfDay.Seconds);
/* Add the charges on this account to the fee total,
* creating an entry if necessary. (This is for the income total)
*/
GLStewardshipCalculationTDSCreditFeeTotalRow CreditFeeTotalRow = (GLStewardshipCalculationTDSCreditFeeTotalRow)
CreditFeeTotalDT.Rows.Find(new object[] { DestCostCentreCode,
DestAccountCode });
if (CreditFeeTotalRow != null)
{
CreditFeeTotalRow.TransactionAmount += Math.Round(pFR.PeriodicAmount, NumDecPlaces);
}
else
{
CreditFeeTotalRow = CreditFeeTotalDT.NewRowTyped();
CreditFeeTotalRow.CostCentreCode = DestCostCentreCode;
CreditFeeTotalRow.AccountCode = DestAccountCode;
CreditFeeTotalRow.TransactionAmount = Math.Round(pFR.PeriodicAmount, NumDecPlaces);
CreditFeeTotalDT.Rows.Add(CreditFeeTotalRow);
}
}
/* Generate the transaction to credit the fee amounts to
* the destination accounts. (Income leg)
*/
for (int k = 0; k < CreditFeeTotalDT.Count; k++)
{
GLStewardshipCalculationTDSCreditFeeTotalRow cFT = (GLStewardshipCalculationTDSCreditFeeTotalRow)
CreditFeeTotalDT.Rows[k];
if (cFT.TransactionAmount < 0)
{
/* The case of a negative gift total should be very rare.
* It would only happen if, for instance, the was only
* a reversal but no new gifts for a certain ledger.
*/
DrCrIndicator = true; //Debit
TransactionAmount = -cFT.TransactionAmount;
}
else
{
DrCrIndicator = false; //Credit
TransactionAmount = cFT.TransactionAmount;
}
/* 0002 - Ok for it to be 0 as just a correction */
if (cFT.TransactionAmount != 0)
{
GLTransactionNumber = 0;
CreatedSuccessfully = TGLPosting.CreateATransaction(AdminFeeDS,
ALedgerNumber,
BatchRow.BatchNumber,
JournalRow.JournalNumber,
"Collected admin charges",
cFT.AccountCode,
cFT.CostCentreCode,
TransactionAmount,
AccountingPeriodRow.PeriodEndDate,
DrCrIndicator,
"AG",
true,
TransactionAmount,
out GLTransactionNumber);
}
}
TVerificationResultCollection Verification = null;
/* check that something has been posted - we know this if the IsSuccessful flag is still false */
if (!CreatedSuccessfully)
{
IsSuccessful = true;
AVerificationResult.Add(new TVerificationResult(Catalog.GetString("Admin Fee Batch"),
String.Format(Catalog.GetString("No admin fees charged in period ({0})."), APeriodNumber),
TResultSeverity.Resv_Status));
// An empty GL Batch now exists, which I need to delete.
//
TVerificationResultCollection BatchCancelResult = new TVerificationResultCollection();
TGLPosting.DeleteGLBatch(
ALedgerNumber,
BatchRow.BatchNumber,
out BatchCancelResult);
AVerificationResult.AddCollection(BatchCancelResult);
}
else
{
//Post the batch just created
GLBatchTDSAccess.SubmitChanges(AdminFeeDS);
IsSuccessful = TGLPosting.PostGLBatch(ALedgerNumber, BatchRow.BatchNumber, out Verification);
if (IsSuccessful)
{
AProcessedFeeAccess.SubmitChanges(ProcessedFeeDataTable, ADBTransaction);
}
}
if (!TVerificationHelper.IsNullOrOnlyNonCritical(Verification))
{
//Petra error: GL0067
ErrorContext = Catalog.GetString("Posting Admin Fee Batch");
ErrorMessage = String.Format(Catalog.GetString("The posting of the admin fee batch failed."));
ErrorType = TResultSeverity.Resv_Noncritical;
throw new System.InvalidOperationException(ErrorMessage);
}
//End of Transaction block in 4GL
/* Print the Admin Fee Calculations report, if requested */
if (APrintReport && IsSuccessful)
{
//TODO
}
}
}
catch (InvalidOperationException ex)
{
AVerificationResult.Add(new TVerificationResult(ErrorContext, ex.Message, ErrorType));
IsSuccessful = false;
}
catch (Exception ex)
{
ErrorContext = Catalog.GetString("Generate Admin Fee Batch");
ErrorMessage = String.Format(Catalog.GetString("Error while generating admin fee batch for Ledger {0}:" +
Environment.NewLine + Environment.NewLine + ex.ToString()),
ALedgerNumber
);
ErrorType = TResultSeverity.Resv_Critical;
AVerificationResult.Add(new TVerificationResult(ErrorContext, ErrorMessage, ErrorType));
IsSuccessful = false;
}
return IsSuccessful;
}
