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; }