/// <summary> /// Run the revaluation and set the flag for the ledger /// </summary> public Boolean RunRevaluation() { try { TLedgerInfo ledger = new TLedgerInfo(F_LedgerNum); F_BaseCurrency = ledger.BaseCurrency; F_BaseCurrencyDigits = new TCurrencyInfo(F_BaseCurrency).digits; F_RevaluationAccCode = ledger.RevaluationAccount; F_FinancialYear = ledger.CurrentFinancialYear; F_AccountingPeriod = ledger.CurrentPeriod; if (!RunRevaluationIntern()) { return(false); } if (F_resultSeverity != TResultSeverity.Resv_Critical) { new TLedgerInitFlagHandler(F_LedgerNum, TLedgerInitFlagEnum.Revaluation).Flag = true; // Set the REVALUATION flag on the Ledger. THIS FLAG IS NOW IGNORED. } } catch (EVerificationException terminate) { FVerificationCollection = terminate.ResultCollection(); } return(F_resultSeverity == TResultSeverity.Resv_Critical); }
/// <summary> /// compare the written result and parameter files with the files approved by a domain expert /// </summary> public static void TestResult(string AResultsFile, int ALedgerNumber = -1) { if (AResultsFile.IndexOf(".Results.html") == -1) { throw new Exception("invalid file name, should end with .Results.html"); } string resultFile = AResultsFile; string resultExpectedFile = AResultsFile.Replace(".Results.html", ".Results.Expected.html"); SortedList <string, string> ToReplace = new SortedList <string, string>(); ToReplace.Add("{ledgernumber}", ALedgerNumber.ToString()); ToReplace.Add("{Today}", DateTime.Today.ToString("yyyy-MM-dd")); int currentYear = DateTime.Today.Year; ToReplace.Add("{ThisYear}", currentYear.ToString()); ToReplace.Add("{PreviousYear}", (currentYear - 1).ToString()); if (ALedgerNumber != -1) { TLedgerInfo ledger = new TLedgerInfo(ALedgerNumber); ToReplace.Add("{CurrentPeriod}", ledger.CurrentPeriod.ToString()); } Assert.True(TTextFile.SameContent(resultFile, resultExpectedFile, true, ToReplace, true), "the file " + resultFile + " should have the same content as " + resultExpectedFile); File.Delete(resultFile); }
/// <summary> /// create a new batch with a consecutive batch number in the ledger /// for call inside a server function /// for performance reasons submitting (save the data in the database) is done later (not here) /// </summary> /// <param name="MainDS"></param> /// <param name="Transaction"></param> /// <param name="LedgerTable"></param> /// <param name="ALedgerNumber"></param> /// <param name="ADateEffective"></param> /// <param name="AForceEffectiveDateToFit"></param> /// <returns>the new gift batch row</returns> public static AGiftBatchRow CreateANewGiftBatchRow(ref GiftBatchTDS MainDS, ref TDBTransaction Transaction, ref ALedgerTable LedgerTable, Int32 ALedgerNumber, DateTime ADateEffective, bool AForceEffectiveDateToFit = true) { AGiftBatchRow NewRow = MainDS.AGiftBatch.NewRowTyped(true); NewRow.LedgerNumber = ALedgerNumber; LedgerTable[0].LastGiftBatchNumber++; NewRow.BatchNumber = LedgerTable[0].LastGiftBatchNumber; Int32 BatchYear, BatchPeriod; // if DateEffective is outside the range of open periods, use the most fitting date TFinancialYear.GetLedgerDatePostingPeriod(ALedgerNumber, ref ADateEffective, out BatchYear, out BatchPeriod, Transaction, AForceEffectiveDateToFit); NewRow.BatchYear = BatchYear; NewRow.BatchPeriod = BatchPeriod; NewRow.GlEffectiveDate = ADateEffective; NewRow.ExchangeRateToBase = 1.0M; NewRow.BatchDescription = "PLEASE ENTER A DESCRIPTION"; NewRow.BankAccountCode = TLedgerInfo.GetDefaultBankAccount(ALedgerNumber); NewRow.BankCostCentre = TLedgerInfo.GetStandardCostCentre(ALedgerNumber); NewRow.CurrencyCode = LedgerTable[0].BaseCurrency; MainDS.AGiftBatch.Rows.Add(NewRow); return(NewRow); }
public void Test_SwitchToNextMonth() { FLedgerNumber = CommonNUnitFunctions.CreateNewLedger(); TLedgerInfo ledgerInfo = new TLedgerInfo(FLedgerNumber); int counter = 0; do { Int32 CurrentPeriod = ledgerInfo.CurrentPeriod; ++counter; Assert.Greater(20, counter, "Too many loops"); // Set revaluation flag ... new TLedgerInitFlagHandler(FLedgerNumber, TLedgerInitFlagEnum.Revaluation).Flag = true; // Run MonthEnd ... TVerificationResultCollection verificationResult; bool blnHasErrors = TPeriodIntervalConnector.PeriodMonthEnd( FLedgerNumber, false, out verificationResult); if (!ledgerInfo.ProvisionalYearEndFlag) { Assert.AreEqual(CurrentPeriod + 1, ledgerInfo.CurrentPeriod, "Period increment"); } Assert.IsFalse(blnHasErrors, "Month end without any error"); System.Diagnostics.Debug.WriteLine("Counter: " + counter.ToString()); } while (!ledgerInfo.ProvisionalYearEndFlag); }
public void Init() { TPetraServerConnector.Connect(); FLedgerNumber = CommonNUnitFunctions.CreateNewLedger(); FledgerInfo = new TLedgerInfo(FLedgerNumber); System.Diagnostics.Debug.WriteLine("Init: " + this.ToString()); }
public static bool PeriodMonthEnd( Int32 ALedgerNumber, bool AInfoMode, out List <Int32> AglBatchNumbers, out Boolean AStewardshipBatch, out TVerificationResultCollection AVerificationResults) { AglBatchNumbers = new List <int>(); AStewardshipBatch = false; try { TLedgerInfo ledgerInfo = new TLedgerInfo(ALedgerNumber); Int32 PeriodClosing = ledgerInfo.CurrentPeriod; bool res = new TMonthEnd(ledgerInfo).RunMonthEnd(AInfoMode, out AglBatchNumbers, out AStewardshipBatch, out AVerificationResults); if (!res && !AInfoMode) { TDBTransaction Transaction = null; AAccountingPeriodTable PeriodTbl = null; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadUncommitted, TEnforceIsolationLevel.eilMinimum, ref Transaction, delegate { PeriodTbl = AAccountingPeriodAccess.LoadByPrimaryKey(ledgerInfo.LedgerNumber, PeriodClosing, Transaction); }); if (PeriodTbl.Rows.Count > 0) { AVerificationResults.Add( new TVerificationResult( Catalog.GetString("Month End"), String.Format(Catalog.GetString("The period {0} - {1} has been closed."), PeriodTbl[0].PeriodStartDate.ToShortDateString(), PeriodTbl[0].PeriodEndDate.ToShortDateString()), TResultSeverity.Resv_Status)); } } return(res); } catch (Exception e) { TLogging.Log("TPeriodIntervallConnector.TPeriodMonthEnd() throws " + e.ToString()); AVerificationResults = new TVerificationResultCollection(); AVerificationResults.Add( new TVerificationResult( Catalog.GetString("Month End"), Catalog.GetString("Uncaught Exception: ") + e.Message, TResultSeverity.Resv_Critical)); return(true); } }
public void Test_09_TAccountPropertyHandler() { TLedgerInfo tHandleLedgerInfo = new TLedgerInfo(FLedgerNumber); TAccountInfo tHandleAccountInfo = new TAccountInfo(tHandleLedgerInfo.LedgerNumber); tHandleAccountInfo.SetSpecialAccountCode(TAccountPropertyEnum.ICH_ACCT); Assert.IsTrue(tHandleAccountInfo.IsValid, "ICH_ACCT shall exist"); Assert.AreEqual("8500", tHandleAccountInfo.AccountCode); }
private void ReallocationLoop(TGlmpInfo AGlmpInfo, TCommonAccountingTool YearEndBatch, String AAccountFrom, String ACostCentreFrom) { string strCostCentreTo = TLedgerInfo.GetStandardCostCentre(FledgerInfo.LedgerNumber); string strAccountTo; FaccountInfo.AccountCode = AAccountFrom; Boolean blnDebitCredit = FaccountInfo.DebitCreditIndicator; String narrativeMessage = FstrNarrativeToMessage; string strCCAccoutCode = FaccountInfo.SetCarryForwardAccount(); // Move FaccountInfo to the Carry Forward Account - if there is one. if (FaccountInfo.IsValid) // The CarryForward account exists.. { strAccountTo = FaccountInfo.AccountCode; if (strCCAccoutCode == "SAMECC") { strCostCentreTo = ACostCentreFrom; narrativeMessage = FstrNarrativeFromToMessage; } } else // If there's no Carry Forward account, use EARNINGS_BF_ACCT { FaccountInfo.SetSpecialAccountCode(TAccountPropertyEnum.EARNINGS_BF_ACCT); strAccountTo = FaccountInfo.AccountCode; } if (FledgerInfo.IltAccountFlag) // In either case, change that if ICH_ACCT is set { FaccountInfo.SetSpecialAccountCode(TAccountPropertyEnum.ICH_ACCT); strAccountTo = FaccountInfo.AccountCode; } if (FledgerInfo.BranchProcessing) // Keep the original Cost Centres - don't roll up { strCostCentreTo = ACostCentreFrom; narrativeMessage = FstrNarrativeFromToMessage; } if (FglmInfo.YtdActualBase < 0) { blnDebitCredit = !blnDebitCredit; } Decimal TransactionAmount = Math.Abs(AGlmpInfo.ActualBase); YearEndBatch.AddBaseCurrencyTransaction( AAccountFrom, ACostCentreFrom, String.Format(narrativeMessage, ACostCentreFrom, AAccountFrom, strCostCentreTo, strAccountTo), FstrYearEnd, !blnDebitCredit, TransactionAmount); YearEndBatch.AddBaseCurrencyTransaction( strAccountTo, strCostCentreTo, String.Format(narrativeMessage, ACostCentreFrom, AAccountFrom, strCostCentreTo, strAccountTo), FstrYearEnd, blnDebitCredit, TransactionAmount); }
public void Test_07_ProcessStatus() { TLedgerInfo ledgerInfo = new TLedgerInfo(LedgerNumber); ledgerInfo.YearEndProcessStatus = (int)TYearEndProcessStatus.ACCOUNT_CLOSED_OUT; Assert.AreEqual((int)TYearEndProcessStatus.ACCOUNT_CLOSED_OUT, ledgerInfo.YearEndProcessStatus, "OK"); ledgerInfo.YearEndProcessStatus = (int)TYearEndProcessStatus.GIFT_CLOSED_OUT; Assert.AreEqual((int)TYearEndProcessStatus.GIFT_CLOSED_OUT, ledgerInfo.YearEndProcessStatus, "OK"); }
/// <summary> /// Populate ledger with gifts and invoices, post batches, close periods and years, according to FNumberOfClosedPeriods /// </summary> /// <param name="datadirectory"></param> public static void PopulateData(string datadirectory) { int periodOverall = 0; int yearCounter = 0; int period = 1; int YearAD = DateTime.Today.Year - (FNumberOfClosedPeriods / 12); SampleDataGiftBatches.LoadBatches(Path.Combine(datadirectory, "donations.csv")); SampleDataAccountsPayable.GenerateInvoices(Path.Combine(datadirectory, "invoices.csv"), YearAD); while (periodOverall <= FNumberOfClosedPeriods) { SampleDataGiftBatches.CreateGiftBatches(period); SampleDataGiftBatches.PostBatches(yearCounter, period, periodOverall == FNumberOfClosedPeriods ? 1 : 0); SampleDataAccountsPayable.PostAndPayInvoices(yearCounter, period, periodOverall == FNumberOfClosedPeriods ? 1 : 0); TLedgerInfo LedgerInfo = new TLedgerInfo(FLedgerNumber); if (periodOverall < FNumberOfClosedPeriods) { TAccountPeriodInfo AccountingPeriodInfo = new TAccountPeriodInfo(FLedgerNumber, period); TLogging.Log("closing period at " + AccountingPeriodInfo.PeriodEndDate.ToShortDateString()); // run month end TMonthEnd MonthEndOperator = new TMonthEnd(LedgerInfo); MonthEndOperator.SetNextPeriod(); if (period == 12) { TYearEnd YearEndOperator = new TYearEnd(LedgerInfo); // run year end TVerificationResultCollection verificationResult = new TVerificationResultCollection(); TReallocation reallocation = new TReallocation(LedgerInfo); reallocation.VerificationResultCollection = verificationResult; reallocation.IsInInfoMode = false; reallocation.RunOperation(); TGlmNewYearInit glmNewYearInit = new TGlmNewYearInit(LedgerInfo, yearCounter, YearEndOperator); glmNewYearInit.VerificationResultCollection = verificationResult; glmNewYearInit.IsInInfoMode = false; glmNewYearInit.RunOperation(); YearAD++; yearCounter++; SampleDataAccountsPayable.GenerateInvoices(Path.Combine(datadirectory, "invoices.csv"), YearAD); period = 0; } } period++; periodOverall++; } }
private DataTable GetAccountListTable(TDBTransaction AReadTransaction, System.Int32 ALedgerNumber, string ATableName) { StringCollection FieldList = new StringCollection(); FieldList.Add(AAccountTable.GetLedgerNumberDBName()); FieldList.Add(AAccountTable.GetAccountCodeDBName()); FieldList.Add(AAccountTable.GetAccountCodeShortDescDBName()); FieldList.Add(AAccountTable.GetAccountActiveFlagDBName()); FieldList.Add(AAccountTable.GetPostingStatusDBName()); FieldList.Add(AAccountTable.GetForeignCurrencyFlagDBName()); FieldList.Add(AAccountTable.GetForeignCurrencyCodeDBName()); GLSetupTDS TempDS = new GLSetupTDS(); AAccountAccess.LoadViaALedger(TempDS, ALedgerNumber, FieldList, AReadTransaction); // load AAccountProperty and set the BankAccountFlag AAccountPropertyAccess.LoadViaALedger(TempDS, ALedgerNumber, AReadTransaction); foreach (AAccountPropertyRow accProp in TempDS.AAccountProperty.Rows) { if ((accProp.PropertyCode == MFinanceConstants.ACCOUNT_PROPERTY_BANK_ACCOUNT) && (accProp.PropertyValue == "true")) { TempDS.AAccount.DefaultView.RowFilter = String.Format("{0}='{1}'", AAccountTable.GetAccountCodeDBName(), accProp.AccountCode); GLSetupTDSAAccountRow acc = (GLSetupTDSAAccountRow)TempDS.AAccount.DefaultView[0].Row; acc.BankAccountFlag = true; TempDS.AAccount.DefaultView.RowFilter = ""; } } // load AAccountHierarchyDetails and check if this account reports to the CASH account AAccountHierarchyDetailAccess.LoadViaAAccountHierarchy(TempDS, ALedgerNumber, MFinanceConstants.ACCOUNT_HIERARCHY_STANDARD, AReadTransaction); TLedgerInfo ledgerInfo = new TLedgerInfo(ALedgerNumber); TGetAccountHierarchyDetailInfo accountHierarchyTools = new TGetAccountHierarchyDetailInfo(ledgerInfo); List <string> children = accountHierarchyTools.GetChildren(MFinanceConstants.CASH_ACCT); foreach (GLSetupTDSAAccountRow account in TempDS.AAccount.Rows) { if (children.Contains(account.AccountCode)) { account.CashAccountFlag = true; } } return(TempDS.AAccount); }
/// <summary> /// create a new batch with a consecutive batch number in the ledger /// for call inside a server function /// for performance reasons submitting (save the data in the database) is done later (not here) /// </summary> /// <param name="AMainDS"></param> /// <param name="ATransaction"></param> /// <param name="ALedgerNumber"></param> /// <returns>the new gift batch row</returns> public static ARecurringGiftBatchRow CreateANewRecurringGiftBatchRow(ref GiftBatchTDS AMainDS, ref TDBTransaction ATransaction, Int32 ALedgerNumber) { #region Validate Arguments if (AMainDS == null) { throw new EFinanceSystemDataObjectNullOrEmptyException(String.Format(Catalog.GetString( "Function:{0} - The Gift Batch dataset is NULL!"), Utilities.GetMethodName(true))); } else if (ATransaction == null) { throw new EFinanceSystemDBTransactionNullException(String.Format(Catalog.GetString( "Function:{0} - Database Transaction must not be 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); } #endregion Validate Arguments TLedgerInfo info = new TLedgerInfo(ALedgerNumber, ATransaction.DataBaseObj); ALedgerAccess.LoadByPrimaryKey(AMainDS, ALedgerNumber, ATransaction); if ((AMainDS.ALedger == null) || (AMainDS.ALedger.Count == 0)) { throw new EFinanceSystemDataObjectNullOrEmptyException(String.Format(Catalog.GetString( "Function:{0} - The Ledger table is NULL or is empty!"), Utilities.GetMethodName(true))); } ARecurringGiftBatchRow NewRow = AMainDS.ARecurringGiftBatch.NewRowTyped(true); NewRow.LedgerNumber = ALedgerNumber; NewRow.BatchNumber = ++AMainDS.ALedger[0].LastRecGiftBatchNumber; NewRow.BatchDescription = Catalog.GetString("Please enter recurring batch description"); NewRow.BankAccountCode = info.GetDefaultBankAccount(); NewRow.BankCostCentre = info.GetStandardCostCentre(); NewRow.CurrencyCode = AMainDS.ALedger[0].BaseCurrency; AMainDS.ARecurringGiftBatch.Rows.Add(NewRow); return(NewRow); }
/// <summary> /// Return the country code for this installation of OpenPetra. /// using the SiteKey to determine the country /// </summary> public static string GetCountryCodeFromSiteLedger(TDBTransaction ATransaction) { string CountryCode = string.Empty; if (DomainManager.GSiteKey > 0) { Int32 ledgerNumber = (Int32)(DomainManager.GSiteKey / 1000000); CountryCode = TLedgerInfo.GetLedgerCountryCode(ledgerNumber); } if (CountryCode.Length == 0) { // Domain Manager doesn't know my site key? CountryCode = "99"; } return(CountryCode); }
/// <summary> /// Run the revaluation and set the flag for the ledger /// Returns true if it seems to be OK. /// </summary> public Boolean RunRevaluation() { try { TLedgerInfo ledger = new TLedgerInfo(F_LedgerNum); F_BaseCurrency = ledger.BaseCurrency; F_BaseCurrencyDigits = new TCurrencyInfo(F_BaseCurrency).digits; F_RevaluationAccCode = ledger.RevaluationAccount; F_FinancialYear = ledger.CurrentFinancialYear; F_AccountingPeriod = ledger.CurrentPeriod; return(RunRevaluationIntern()); } catch (EVerificationException terminate) { FVerificationCollection = terminate.ResultCollection(); return(false); } }
public static bool TPeriodYearEnd( int ALedgerNum, bool AIsInInfoMode, out TVerificationResultCollection AVerificationResult) { bool NewTransaction; DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.Serializable, out NewTransaction); try { TLedgerInfo LedgerInfo = new TLedgerInfo(ALedgerNum); bool res = new TYearEnd(LedgerInfo).RunYearEnd(AIsInInfoMode, out AVerificationResult); if (!res) { String SuccessMsg = AIsInInfoMode ? "YearEnd check: No problems found." : "Success."; AVerificationResult.Add(new TVerificationResult("Year End", SuccessMsg, "Success", TResultSeverity.Resv_Status)); } if (NewTransaction) { DBAccess.GDBAccessObj.CommitTransaction(); } return res; } catch (Exception e) { TLogging.Log("TPeriodIntervalConnector.TPeriodYearEnd() throws " + e.ToString()); AVerificationResult = new TVerificationResultCollection(); AVerificationResult.Add( new TVerificationResult( Catalog.GetString("Year End"), Catalog.GetString("Uncaught Exception: ") + e.Message, TResultSeverity.Resv_Critical)); DBAccess.GDBAccessObj.RollbackTransaction(); return false; } }
/// <summary> /// </summary> public TGlmNewYearInit(TLedgerInfo ALedgerInfo, int AYear, TYearEnd AYearEndOperator) { FOldYearNum = AYear; FNewYearNum = FOldYearNum + 1; FLedgerInfo = ALedgerInfo; FYearEndOperator = AYearEndOperator; FLedgerAccountingPeriods = FLedgerInfo.NumberOfAccountingPeriods; // Don't call these properties in a loop, FLedgerFwdPeriods = FLedgerInfo.NumberFwdPostingPeriods; // as they reload the row from the DB! bool NewTransaction; TDBTransaction Transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, out NewTransaction); try { DataTable GlmTble = LoadTable(FLedgerInfo.LedgerNumber, FOldYearNum, Transaction); FGlmPostingFrom.Merge(GlmTble); GlmTble = LoadTable(FLedgerInfo.LedgerNumber, FNewYearNum, Transaction); GlmTDS.AGeneralLedgerMaster.Merge(GlmTble); GlmTDS.AGeneralLedgerMaster.DefaultView.Sort = AGeneralLedgerMasterTable.GetAccountCodeDBName() + "," + AGeneralLedgerMasterTable.GetCostCentreCodeDBName(); DataTable GlmpTbl = GetGlmpRows(FOldYearNum, Transaction, FLedgerAccountingPeriods); FGlmpFrom.Merge(GlmpTbl); FGlmpFrom.DefaultView.Sort = "a_glm_sequence_i,a_period_number_i"; GlmpTbl = GetGlmpRows(FNewYearNum, Transaction, 0); GlmTDS.AGeneralLedgerMasterPeriod.Merge(GlmpTbl); GlmTDS.AGeneralLedgerMasterPeriod.DefaultView.Sort = "a_glm_sequence_i,a_period_number_i"; } finally { if (NewTransaction) { DBAccess.GDBAccessObj.RollbackTransaction(); } } }
public void Init() { TPetraServerConnector.Connect(); FLedgerNumber = CommonNUnitFunctions.CreateNewLedger(); FledgerInfo = new TLedgerInfo(FLedgerNumber); // add costcentre 7300 for gift batch ACostCentreTable CostCentres = new ACostCentreTable(); ACostCentreRow CCRow = CostCentres.NewRowTyped(); CCRow.LedgerNumber = FLedgerNumber; CCRow.CostCentreCode = "7300"; CCRow.CostCentreName = "7300"; CCRow.CostCentreType = MFinanceConstants.FOREIGN_CC_TYPE; CCRow.CostCentreToReportTo = MFinanceConstants.INTER_LEDGER_HEADING; CCRow.PostingCostCentreFlag = true; CCRow.CostCentreActiveFlag = true; CostCentres.Rows.Add(CCRow); ACostCentreAccess.SubmitChanges(CostCentres, null); System.Diagnostics.Debug.WriteLine("Init: " + this.ToString()); }
public void Test_SwitchToNextMonth() { FLedgerNumber = CommonNUnitFunctions.CreateNewLedger(); TLedgerInfo LedgerInfo = new TLedgerInfo(FLedgerNumber); int Counter = 0; do { Int32 CurrentPeriod = LedgerInfo.CurrentPeriod; ++Counter; Assert.Greater(20, Counter, "Too many loops"); // Set revaluation flag ... new TLedgerInitFlag(FLedgerNumber, "Reval").IsSet = true; // Run MonthEnd ... TVerificationResultCollection verificationResult; List <Int32> glBatchNumbers; Boolean stewardshipBatch; bool blnHasErrors = !TPeriodIntervalConnector.PeriodMonthEnd( FLedgerNumber, false, out glBatchNumbers, out stewardshipBatch, out verificationResult); if (!LedgerInfo.ProvisionalYearEndFlag) { Assert.AreEqual(CurrentPeriod + 1, LedgerInfo.CurrentPeriod, "Period increment"); } Assert.IsFalse(blnHasErrors, "Month end without any error"); System.Diagnostics.Debug.WriteLine("Counter: " + Counter.ToString()); } while (!LedgerInfo.ProvisionalYearEndFlag); }
/// <summary> /// create a new batch with a consecutive batch number in the ledger /// for call inside a server function /// for performance reasons submitting (save the data in the database) is done later (not here) /// </summary> /// <param name="MainDS"></param> /// <param name="Transaction"></param> /// <param name="LedgerTable"></param> /// <param name="ALedgerNumber"></param> /// <returns>the new gift batch row</returns> public static ARecurringGiftBatchRow CreateANewRecurringGiftBatchRow(ref GiftBatchTDS MainDS, ref TDBTransaction Transaction, ref ALedgerTable LedgerTable, Int32 ALedgerNumber) { GiftBatchTDS Temp = new GiftBatchTDS(); ARecurringGiftBatchAccess.LoadViaALedger(Temp, LedgerTable[0].LedgerNumber, Transaction); DataView RecurringGiftBatchDV = new DataView(Temp.ARecurringGiftBatch); RecurringGiftBatchDV.RowFilter = string.Empty; RecurringGiftBatchDV.Sort = string.Format("{0} DESC", ARecurringGiftBatchTable.GetBatchNumberDBName()); //Recurring batch numbers can be reused so check each time for current highest number if (RecurringGiftBatchDV.Count > 0) { LedgerTable[0].LastRecGiftBatchNumber = (int)(RecurringGiftBatchDV[0][ARecurringGiftBatchTable.GetBatchNumberDBName()]); } else { LedgerTable[0].LastRecGiftBatchNumber = 0; } ARecurringGiftBatchRow NewRow = MainDS.ARecurringGiftBatch.NewRowTyped(true); NewRow.LedgerNumber = ALedgerNumber; NewRow.BatchNumber = ++LedgerTable[0].LastRecGiftBatchNumber; NewRow.BatchDescription = Catalog.GetString("Please enter recurring batch description"); NewRow.BankAccountCode = TLedgerInfo.GetDefaultBankAccount(ALedgerNumber); NewRow.BankCostCentre = TLedgerInfo.GetStandardCostCentre(ALedgerNumber); NewRow.CurrencyCode = LedgerTable[0].BaseCurrency; MainDS.ARecurringGiftBatch.Rows.Add(NewRow); return(NewRow); }
/// <summary> /// Run the revaluation and set the flag for the ledger /// </summary> public bool RunRevaluation() { try { TLedgerInfo gli = new TLedgerInfo(F_LedgerNum); F_BaseCurrency = gli.BaseCurrency; F_BaseCurrencyDigits = new TCurrencyInfo(F_BaseCurrency).digits; F_RevaluationAccCode = gli.RevaluationAccount; F_FinancialYear = gli.CurrentFinancialYear; F_AccountingPeriod = gli.CurrentPeriod; RunRevaluationIntern(); if (F_resultSeverity != TResultSeverity.Resv_Critical) { new TLedgerInitFlagHandler(F_LedgerNum, TLedgerInitFlagEnum.Revaluation).Flag = true; } } catch (EVerificationException terminate) { FVerificationCollection = terminate.ResultCollection(); } return(F_resultSeverity == TResultSeverity.Resv_Critical); }
public static bool GenerateHOSAFiles(int ALedgerNumber, int APeriodNumber, int AIchNumber, string ACostCentre, String ACurrencySelect, string AFileName, out TVerificationResultCollection AVerificationResult ) { bool Successful = false; GLBatchTDS MainDS = new GLBatchTDS(); TVerificationResultCollection VerificationResult = new TVerificationResultCollection(); AVerificationResult = VerificationResult; TDBTransaction DBTransaction = null; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref DBTransaction, delegate { //Load tables needed: AccountingPeriod, Ledger, Account, Cost Centre, Transaction, Gift Batch, ICHStewardship ALedgerAccess.LoadByPrimaryKey(MainDS, ALedgerNumber, DBTransaction); /* Retrieve info on the ledger. */ ALedgerRow LedgerRow = (ALedgerRow)MainDS.ALedger.Rows[0]; String Currency = (ACurrencySelect == MFinanceConstants.CURRENCY_BASE) ? LedgerRow.BaseCurrency : LedgerRow.IntlCurrency; /* String StoreNumericFormat = "#" + CultureInfo.CurrentCulture.NumberFormat.NumberGroupSeparator + "##0"; * * if (CultureInfo.CurrentCulture.NumberFormat.NumberDecimalDigits > 0) * { * string DecPls = new String('0', CultureInfo.CurrentCulture.NumberFormat.NumberDecimalDigits); * StoreNumericFormat += CultureInfo.CurrentCulture.NumberFormat.NumberDecimalSeparator + DecPls; * } */ AAccountingPeriodTable AccountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber, APeriodNumber, DBTransaction); AAccountingPeriodRow AccountingPeriodRow = (AAccountingPeriodRow)AccountingPeriodTable.Rows[0]; String MonthName = AccountingPeriodRow.AccountingPeriodDesc; //Create table definitions DataTable TableForExport = new DataTable(); TableForExport.Columns.Add("CostCentre", typeof(string)); TableForExport.Columns.Add("Account", typeof(string)); TableForExport.Columns.Add("LedgerMonth", typeof(string)); TableForExport.Columns.Add("ICHPeriod", typeof(string)); TableForExport.Columns.Add("Date", typeof(DateTime)); TableForExport.Columns.Add("IndividualDebitTotal", typeof(decimal)); TableForExport.Columns.Add("IndividualCreditTotal", typeof(decimal)); string TableForExportHeader = "/** Header **" + "," + APeriodNumber.ToString() + "," + TLedgerInfo.GetStandardCostCentre(ALedgerNumber) + "," + ACostCentre + "," + DateTime.Today.ToShortDateString() + "," + Currency; //See gi3200.p ln: 170 //Select any gift transactions to export string strSql = TDataBase.ReadSqlFile("ICH.HOSAExportGifts.sql"); OdbcParameter parameter; List <OdbcParameter> parameters = new List <OdbcParameter>(); parameter = new OdbcParameter("LedgerNumber", OdbcType.Int); parameter.Value = ALedgerNumber; parameters.Add(parameter); parameter = new OdbcParameter("Year", OdbcType.Int); parameter.Value = LedgerRow.CurrentFinancialYear; parameters.Add(parameter); parameter = new OdbcParameter("CostCentre", OdbcType.VarChar); parameter.Value = ACostCentre; parameters.Add(parameter); DataTable TmpTable = DBAccess.GDBAccessObj.SelectDT(strSql, "table", DBTransaction, parameters.ToArray()); foreach (DataRow untypedTransRow in TmpTable.Rows) { string gLMAcctCode = untypedTransRow[3].ToString(); string gLMCostCCode = untypedTransRow[4].ToString(); string gLMAcctType = untypedTransRow[5].ToString(); if (gLMAcctType == MFinanceConstants.ACCOUNT_TYPE_INCOME) //a_account.a_account_type_c { DateTime PeriodStartDate = AccountingPeriodRow.PeriodStartDate; DateTime PeriodEndDate = AccountingPeriodRow.PeriodEndDate; /*RUN Export_gifts(INPUT pv_ledger_number_i...*/ //gi3200-1.i ExportGifts(ALedgerNumber, ACostCentre, gLMAcctCode, MonthName, APeriodNumber, PeriodStartDate, PeriodEndDate, ACurrencySelect, AIchNumber, TableForExport, VerificationResult); } /* Then see if there are any GL transactions to export */ //gi3200.i ln:33 /* * This scheme with ODBC parameters consistently causes an "input string is the wrong type" eror: * * strSql = TDataBase.ReadSqlFile("ICH.HOSAExportGLTrans.sql"); * OdbcParameter[] SqlParams = new OdbcParameter[] { * new OdbcParameter("LedgerNumber", (Int32)ALedgerNumber), * new OdbcParameter("Account", (String)gLMAcctCode), * new OdbcParameter("CostCentre", (String)gLMCostCCode), * new OdbcParameter("Narrative", (String)MFinanceConstants.NARRATIVE_YEAR_END_REALLOCATION), * new OdbcParameter("ICHNumber", (Int32)AIchNumber), * new OdbcParameter("ICHNumber2", (Int32)AIchNumber), * new OdbcParameter("PeriodNumber", (Int32)APeriodNumber) * }; * DataTable TmpTransTable = DBAccess.GDBAccessObj.SelectDT(strSql, "Transactions", DBTransaction, SqlParams); */ strSql = "SELECT Trans.a_ledger_number_i, Trans.a_batch_number_i, Trans.a_journal_number_i, Trans.a_transaction_number_i, " + "Trans.a_account_code_c, Trans.a_cost_centre_code_c, Trans.a_transaction_date_d, Trans.a_transaction_amount_n, " + "Trans.a_amount_in_base_currency_n, Trans.a_amount_in_intl_currency_n, Trans.a_ich_number_i, Trans.a_system_generated_l, " + "Trans.a_narrative_c, Trans.a_debit_credit_indicator_l FROM public.a_transaction AS Trans, public.a_journal AS Journal " + "WHERE Trans.a_ledger_number_i = Journal.a_ledger_number_i AND Trans.a_batch_number_i = Journal.a_batch_number_i " + "AND Trans.a_journal_number_i = Journal.a_journal_number_i " + String.Format( "AND Trans.a_ledger_number_i = {0} AND Trans.a_account_code_c = '{1}' AND Trans.a_cost_centre_code_c = '{2}' " + "AND Trans.a_transaction_status_l = true AND NOT (Trans.a_narrative_c LIKE '{3}%' AND Trans.a_system_generated_l = true) " + "AND ((Trans.a_ich_number_i + {4}) = Trans.a_ich_number_i OR Trans.a_ich_number_i = {4}) " + "AND Journal.a_journal_period_i = {5};", ALedgerNumber, gLMAcctCode, gLMCostCCode, MFinanceConstants.NARRATIVE_YEAR_END_REALLOCATION, AIchNumber, APeriodNumber ); DataTable TmpTransTable = DBAccess.GDBAccessObj.SelectDT(strSql, "Transactions", DBTransaction); foreach (DataRow untypedTransactRow in TmpTransTable.Rows) { Decimal DebitTotal = 0; Decimal CreditTotal = 0; bool Debit = Convert.ToBoolean(untypedTransactRow[13]); //a_transaction.a_debit_credit_indicator_l bool SystemGenerated = Convert.ToBoolean(untypedTransactRow[11]); //a_transaction.a_system_generated_l //TODO: Calendar vs Financial Date Handling - Check if number of ledger periods needs to be used here and not 12 assumed string Narrative = untypedTransactRow[12].ToString(); //a_transaction.a_narrative_c DateTime TransactionDate = Convert.ToDateTime(untypedTransactRow[6]); //a_transaction.a_transaction_date_d if (ACurrencySelect == MFinanceConstants.CURRENCY_BASE) { decimal AmountInBaseCurrency = Convert.ToDecimal(untypedTransactRow[8]); //a_transaction.a_amount_in_base_currency_n /* find transaction amount and store as debit or credit */ if (Debit) { DebitTotal += AmountInBaseCurrency; } else { CreditTotal += AmountInBaseCurrency; } } else { decimal AmountInIntlCurrency = Convert.ToDecimal(untypedTransactRow[9]); //a_transaction.a_amount_in_intl_currency_n if (Debit) { DebitTotal += AmountInIntlCurrency; } else { CreditTotal += AmountInIntlCurrency; } } TLogging.LogAtLevel(4, "HOSA-Narrative: " + Narrative); //Check for specific narrative strings bool IsNarrativeGBGiftBatch = false; int LenNarrativeGBGiftBatch = MFinanceConstants.NARRATIVE_GB_GIFT_BATCH.Length; bool IsNarrativeGiftsReceivedGiftBatch = false; int LenNarrativeGiftsReceivedGiftBatch = MFinanceConstants.NARRATIVE_GIFTS_RECEIVED_GIFT_BATCH.Length; if (Narrative.Length >= LenNarrativeGiftsReceivedGiftBatch) { IsNarrativeGiftsReceivedGiftBatch = (Narrative.Substring(0, LenNarrativeGiftsReceivedGiftBatch) == MFinanceConstants.NARRATIVE_GIFTS_RECEIVED_GIFT_BATCH); } if (Narrative.Length >= LenNarrativeGBGiftBatch) { IsNarrativeGBGiftBatch = (Narrative.Substring(0, LenNarrativeGBGiftBatch) == MFinanceConstants.NARRATIVE_GB_GIFT_BATCH); } if ((gLMAcctType.ToUpper() != MFinanceConstants.ACCOUNT_TYPE_INCOME.ToUpper()) || !(SystemGenerated && (IsNarrativeGBGiftBatch || IsNarrativeGiftsReceivedGiftBatch))) { // Put transaction information DataRow DR = (DataRow)TableForExport.NewRow(); DR[0] = gLMCostCCode; DR[1] = ConvertAccount(gLMAcctCode); DR[2] = ALedgerNumber.ToString() + MonthName + ":" + Narrative; DR[3] = "ICH-" + APeriodNumber.ToString("00"); DR[4] = TransactionDate; DR[5] = DebitTotal; DR[6] = CreditTotal; TableForExport.Rows.Add(DR); } } } TableForExport.AcceptChanges(); TLogging.LogAtLevel(4, "HOSA-TableForExport: " + TableForExport.Rows.Count.ToString()); //DataTables to XML to CSV XmlDocument doc = TDataBase.DataTableToXml(TableForExport); TCsv2Xml.Xml2Csv(doc, AFileName); //Replace the default CSV header row with OM specific ReplaceHeaderInFile(AFileName, TableForExportHeader, ref VerificationResult); Successful = true; }); // Get NewOrExisting AutoReadTransaction return(Successful); } // Generate HOSA Files
public static Int32 CreateGLBatch(BankImportTDS AMainDS, Int32 ALedgerNumber, Int32 AStatementKey, Int32 AGLBatchNumber, out TVerificationResultCollection AVerificationResult) { AMainDS.AEpTransaction.DefaultView.RowFilter = String.Format("{0}={1}", AEpTransactionTable.GetStatementKeyDBName(), AStatementKey); AMainDS.AEpStatement.DefaultView.RowFilter = String.Format("{0}={1}", AEpStatementTable.GetStatementKeyDBName(), AStatementKey); AEpStatementRow stmt = (AEpStatementRow)AMainDS.AEpStatement.DefaultView[0].Row; AVerificationResult = null; Int32 DateEffectivePeriodNumber, DateEffectiveYearNumber; TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.ReadCommitted); if (!TFinancialYear.IsValidPostingPeriod(ALedgerNumber, stmt.Date, out DateEffectivePeriodNumber, out DateEffectiveYearNumber, Transaction)) { string msg = String.Format(Catalog.GetString("Cannot create a GL batch for date {0} since it is not in an open period of the ledger."), stmt.Date.ToShortDateString()); TLogging.Log(msg); AVerificationResult = new TVerificationResultCollection(); AVerificationResult.Add(new TVerificationResult(Catalog.GetString("Creating GL Batch"), msg, TResultSeverity.Resv_Critical)); DBAccess.GDBAccessObj.RollbackTransaction(); return(-1); } Int32 BatchYear, BatchPeriod; // if DateEffective is outside the range of open periods, use the most fitting date DateTime DateEffective = stmt.Date; TFinancialYear.GetLedgerDatePostingPeriod(ALedgerNumber, ref DateEffective, out BatchYear, out BatchPeriod, Transaction, true); ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction); DBAccess.GDBAccessObj.RollbackTransaction(); GLBatchTDS GLDS = TGLTransactionWebConnector.CreateABatch(ALedgerNumber); ABatchRow glbatchRow = GLDS.ABatch[0]; glbatchRow.BatchPeriod = BatchPeriod; glbatchRow.DateEffective = DateEffective; glbatchRow.BatchDescription = String.Format(Catalog.GetString("bank import for date {0}"), stmt.Date.ToShortDateString()); decimal HashTotal = 0.0M; decimal DebitTotal = 0.0M; decimal CreditTotal = 0.0M; // TODO: support several journals // TODO: support several currencies, support other currencies than the base currency AJournalRow gljournalRow = GLDS.AJournal.NewRowTyped(); gljournalRow.LedgerNumber = glbatchRow.LedgerNumber; gljournalRow.BatchNumber = glbatchRow.BatchNumber; gljournalRow.JournalNumber = glbatchRow.LastJournal + 1; gljournalRow.TransactionCurrency = LedgerTable[0].BaseCurrency; glbatchRow.LastJournal++; gljournalRow.JournalPeriod = glbatchRow.BatchPeriod; gljournalRow.DateEffective = glbatchRow.DateEffective; gljournalRow.JournalDescription = glbatchRow.BatchDescription; gljournalRow.SubSystemCode = CommonAccountingSubSystemsEnum.GL.ToString(); gljournalRow.TransactionTypeCode = CommonAccountingTransactionTypesEnum.STD.ToString(); gljournalRow.ExchangeRateToBase = 1.0m; GLDS.AJournal.Rows.Add(gljournalRow); foreach (DataRowView dv in AMainDS.AEpTransaction.DefaultView) { AEpTransactionRow transactionRow = (AEpTransactionRow)dv.Row; DataView v = AMainDS.AEpMatch.DefaultView; v.RowFilter = AEpMatchTable.GetActionDBName() + " = '" + MFinanceConstants.BANK_STMT_STATUS_MATCHED_GL + "' and " + AEpMatchTable.GetMatchTextDBName() + " = '" + transactionRow.MatchText + "'"; if (v.Count > 0) { AEpMatchRow match = (AEpMatchRow)v[0].Row; ATransactionRow trans = GLDS.ATransaction.NewRowTyped(); trans.LedgerNumber = glbatchRow.LedgerNumber; trans.BatchNumber = glbatchRow.BatchNumber; trans.JournalNumber = gljournalRow.JournalNumber; trans.TransactionNumber = gljournalRow.LastTransactionNumber + 1; trans.AccountCode = match.AccountCode; trans.CostCentreCode = match.CostCentreCode; trans.Reference = match.Reference; trans.Narrative = match.Narrative; trans.TransactionDate = transactionRow.DateEffective; if (transactionRow.TransactionAmount < 0) { trans.AmountInBaseCurrency = -1 * transactionRow.TransactionAmount; trans.TransactionAmount = -1 * transactionRow.TransactionAmount; trans.DebitCreditIndicator = true; DebitTotal += trans.AmountInBaseCurrency; } else { trans.AmountInBaseCurrency = transactionRow.TransactionAmount; trans.TransactionAmount = transactionRow.TransactionAmount; trans.DebitCreditIndicator = false; CreditTotal += trans.AmountInBaseCurrency; } GLDS.ATransaction.Rows.Add(trans); gljournalRow.LastTransactionNumber++; // add one transaction for the bank as well trans = GLDS.ATransaction.NewRowTyped(); trans.LedgerNumber = glbatchRow.LedgerNumber; trans.BatchNumber = glbatchRow.BatchNumber; trans.JournalNumber = gljournalRow.JournalNumber; trans.TransactionNumber = gljournalRow.LastTransactionNumber + 1; trans.AccountCode = stmt.BankAccountCode; trans.CostCentreCode = TLedgerInfo.GetStandardCostCentre(ALedgerNumber); trans.Reference = match.Reference; trans.Narrative = match.Narrative; trans.TransactionDate = transactionRow.DateEffective; if (transactionRow.TransactionAmount < 0) { trans.AmountInBaseCurrency = -1 * transactionRow.TransactionAmount; trans.TransactionAmount = -1 * transactionRow.TransactionAmount; trans.DebitCreditIndicator = false; CreditTotal += trans.AmountInBaseCurrency; } else { trans.AmountInBaseCurrency = transactionRow.TransactionAmount; trans.TransactionAmount = transactionRow.TransactionAmount; trans.DebitCreditIndicator = true; DebitTotal += trans.AmountInBaseCurrency; } GLDS.ATransaction.Rows.Add(trans); gljournalRow.LastTransactionNumber++; } } gljournalRow.JournalDebitTotal = DebitTotal; gljournalRow.JournalCreditTotal = CreditTotal; glbatchRow.BatchDebitTotal = DebitTotal; glbatchRow.BatchCreditTotal = CreditTotal; glbatchRow.BatchControlTotal = HashTotal; TVerificationResultCollection VerificationResult; TSubmitChangesResult result = TGLTransactionWebConnector.SaveGLBatchTDS(ref GLDS, out VerificationResult); if (result == TSubmitChangesResult.scrOK) { return(glbatchRow.BatchNumber); } TLogging.Log("Problems storing GL Batch"); return(-1); }
/// <summary> /// Run the revaluation and set the flag for the ledger /// Returns true if a Reval batch was posted. /// </summary> public Boolean RunRevaluation(out Int32 glBatchNumber) { glBatchNumber = -1; try { TLedgerInfo ledger = new TLedgerInfo(F_LedgerNum); F_BaseCurrency = ledger.BaseCurrency; F_BaseCurrencyDigits = new TCurrencyInfo(F_BaseCurrency).digits; F_RevaluationAccCode = ledger.RevaluationAccount; F_FinancialYear = ledger.CurrentFinancialYear; F_AccountingPeriod = ledger.CurrentPeriod; TDBTransaction Transaction = null; AGeneralLedgerMasterTable GlmTable = new AGeneralLedgerMasterTable(); AGeneralLedgerMasterRow glmTemplate = (AGeneralLedgerMasterRow)GlmTable.NewRowTyped(false); Boolean transactionsWereCreated = false; glmTemplate.LedgerNumber = F_LedgerNum; glmTemplate.Year = F_FinancialYear; for (Int32 i = 0; i < F_ForeignAccount.Length; i++) { glmTemplate.AccountCode = F_ForeignAccount[i]; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, ref Transaction, delegate { GlmTable = AGeneralLedgerMasterAccess.LoadUsingTemplate(glmTemplate, Transaction); }); if (GlmTable.Rows.Count > 0) { transactionsWereCreated |= RevaluateAccount(GlmTable, F_ExchangeRate[i], F_ForeignCurrency[i]); } } Boolean batchPostedOK = true; if (transactionsWereCreated) { batchPostedOK = CloseRevaluationAccountingBatch(out glBatchNumber); } if (batchPostedOK) { if (!transactionsWereCreated) // If no transactions were needed, I'll just advise the user: { FVerificationCollection.Add(new TVerificationResult( "Post Forex Batch", "Exchange rates are unchanged - no revaluation was required.", TResultSeverity.Resv_Status)); } for (Int32 i = 0; i < F_ForeignAccount.Length; i++) { TLedgerInitFlag.RemoveFlagComponent(F_LedgerNum, MFinanceConstants.LEDGER_INIT_FLAG_REVAL, F_ForeignAccount[i]); } } else { FVerificationCollection.Add(new TVerificationResult( "Post Forex Batch", "The Revaluation Batch could not be posted.", TResultSeverity.Resv_Critical)); } return(batchPostedOK); } catch (EVerificationException terminate) { FVerificationCollection = terminate.ResultCollection(); return(false); } } // Run Revaluation
/// <summary> /// create a new batch with a consecutive batch number in the ledger /// for call inside a server function /// for performance reasons submitting (save the data in the database) is done later (not here) /// </summary> /// <param name="AMainDS"></param> /// <param name="ATransaction"></param> /// <param name="ALedgerTbl"></param> /// <param name="ALedgerNumber"></param> /// <param name="ADateEffective"></param> /// <param name="AForceEffectiveDateToFit"></param> /// <returns>the new gift batch row</returns> public static AGiftBatchRow CreateANewGiftBatchRow(ref GiftBatchTDS AMainDS, ref TDBTransaction ATransaction, ref ALedgerTable ALedgerTbl, Int32 ALedgerNumber, DateTime ADateEffective, bool AForceEffectiveDateToFit = true) { #region Validate Arguments if (AMainDS == null) { throw new EFinanceSystemDataObjectNullOrEmptyException(String.Format(Catalog.GetString( "Function:{0} - The Gift Batch dataset is NULL!"), Utilities.GetMethodName(true))); } else if (ATransaction == null) { throw new EFinanceSystemDBTransactionNullException(String.Format(Catalog.GetString( "Function:{0} - Database Transaction must not be NULL!"), Utilities.GetMethodName(true))); } else if ((ALedgerTbl == null) || (ALedgerTbl.Count == 0)) { throw new EFinanceSystemDataObjectNullOrEmptyException(String.Format(Catalog.GetString( "Function:{0} - The Ledger table is NULL or is empty!"), 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); } #endregion Validate Arguments AGiftBatchRow NewRow = null; try { NewRow = AMainDS.AGiftBatch.NewRowTyped(true); NewRow.LedgerNumber = ALedgerNumber; NewRow.BatchNumber = ++ALedgerTbl[0].LastGiftBatchNumber; Int32 BatchYear, BatchPeriod; // if DateEffective is outside the range of open periods, use the most fitting date TFinancialYear.GetLedgerDatePostingPeriod(ALedgerNumber, ref ADateEffective, out BatchYear, out BatchPeriod, ATransaction, AForceEffectiveDateToFit); NewRow.BatchYear = BatchYear; NewRow.BatchPeriod = BatchPeriod; NewRow.GlEffectiveDate = ADateEffective; NewRow.ExchangeRateToBase = 1.0M; NewRow.BatchDescription = "PLEASE ENTER A DESCRIPTION"; NewRow.BankAccountCode = TLedgerInfo.GetDefaultBankAccount(ALedgerNumber); NewRow.BankCostCentre = TLedgerInfo.GetStandardCostCentre(ALedgerNumber); NewRow.CurrencyCode = ALedgerTbl[0].BaseCurrency; AMainDS.AGiftBatch.Rows.Add(NewRow); } catch (Exception ex) { TLogging.LogException(ex, Utilities.GetMethodSignature()); throw; } return(NewRow); }
/// <summary> /// create a new batch with a consecutive batch number in the ledger /// for call inside a server function /// for performance reasons submitting (save the data in the database) is done later (not here) /// </summary> /// <param name="AMainDS"></param> /// <param name="ATransaction"></param> /// <param name="ALedgerTbl"></param> /// <param name="ALedgerNumber"></param> /// <returns>the new gift batch row</returns> public static ARecurringGiftBatchRow CreateANewRecurringGiftBatchRow(ref GiftBatchTDS AMainDS, ref TDBTransaction ATransaction, ref ALedgerTable ALedgerTbl, Int32 ALedgerNumber) { #region Validate Arguments if (AMainDS == null) { throw new EFinanceSystemDataObjectNullOrEmptyException(String.Format(Catalog.GetString( "Function:{0} - The Gift Batch dataset is NULL!"), Utilities.GetMethodName(true))); } else if (ATransaction == null) { throw new EFinanceSystemDBTransactionNullException(String.Format(Catalog.GetString( "Function:{0} - Database Transaction must not be NULL!"), Utilities.GetMethodName(true))); } else if ((ALedgerTbl == null) || (ALedgerTbl.Count == 0)) { throw new EFinanceSystemDataObjectNullOrEmptyException(String.Format(Catalog.GetString( "Function:{0} - The Ledger table is NULL or is empty!"), 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); } #endregion Validate Arguments GiftBatchTDS Temp = new GiftBatchTDS(); ARecurringGiftBatchAccess.LoadViaALedger(Temp, ALedgerNumber, ATransaction); DataView RecurringGiftBatchDV = new DataView(Temp.ARecurringGiftBatch); RecurringGiftBatchDV.RowFilter = string.Empty; RecurringGiftBatchDV.Sort = string.Format("{0} DESC", ARecurringGiftBatchTable.GetBatchNumberDBName()); //Recurring batch numbers can be reused so check each time for current highest number if (RecurringGiftBatchDV.Count > 0) { ALedgerTbl[0].LastRecGiftBatchNumber = (int)(RecurringGiftBatchDV[0][ARecurringGiftBatchTable.GetBatchNumberDBName()]); } else { ALedgerTbl[0].LastRecGiftBatchNumber = 0; } ARecurringGiftBatchRow NewRow = AMainDS.ARecurringGiftBatch.NewRowTyped(true); NewRow.LedgerNumber = ALedgerNumber; NewRow.BatchNumber = ++ALedgerTbl[0].LastRecGiftBatchNumber; NewRow.BatchDescription = Catalog.GetString("Please enter recurring batch description"); NewRow.BankAccountCode = TLedgerInfo.GetDefaultBankAccount(ALedgerNumber); NewRow.BankCostCentre = TLedgerInfo.GetStandardCostCentre(ALedgerNumber); NewRow.CurrencyCode = ALedgerTbl[0].BaseCurrency; AMainDS.ARecurringGiftBatch.Rows.Add(NewRow); return(NewRow); }
public static DataSet APAccountDetail(Dictionary <String, TVariant> AParameters, TReportingDbAdapter DbAdapter) { TDBTransaction Transaction = null; int LedgerNumber = AParameters["param_ledger_number_i"].ToInt32(); TLedgerInfo LedgerInfo = new TLedgerInfo(LedgerNumber); string LedgerCurrency = LedgerInfo.BaseCurrency; string NumFrom = ""; string NumTo = ""; string DateFrom = ""; string DateTo = ""; if (AParameters["param_from_date"].ToString() != String.Empty) { DateFrom = " AND a_transaction.a_transaction_date_d >= '" + AParameters["param_from_date"].ToDate().ToString("yyyy-MM-dd") + "' "; } if (AParameters["param_to_date"].ToString() != String.Empty) { DateTo = " AND a_transaction.a_transaction_date_d <= '" + AParameters["param_to_date"].ToDate().ToString("yyyy-MM-dd") + "' "; } if (AParameters["param_account_from"].ToString() != String.Empty) { NumFrom = " AND a_transaction.a_account_code_c >= '" + AParameters["param_account_from"].ToString() + "' "; } if (AParameters["param_account_to"].ToString() != String.Empty) { NumTo = " AND a_transaction.a_account_code_c <= '" + AParameters["param_account_to"].ToString() + "' "; } DataSet ReturnDataSet = new DataSet(); // create new datatable DataTable Accounts = new DataTable(); DataTable Details = new DataTable(); DbAdapter.FPrivateDatabaseObj.GetNewOrExistingAutoReadTransaction( IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref Transaction, delegate { String Query = @"SELECT DISTINCT a_transaction.a_account_code_c AS AccountCode, a_cost_centre.a_cost_centre_code_c, a_cost_centre_name_c, a_account_code_long_desc_c, a_account_code_short_desc_c, CASE WHEN a_foreign_currency_flag_l THEN a_foreign_currency_code_c ELSE '" + LedgerCurrency + @"' END AS currency FROM a_transaction LEFT JOIN a_cost_centre ON a_cost_centre.a_cost_centre_code_c = a_transaction.a_cost_centre_code_c JOIN a_account ON a_transaction.a_ledger_number_i=a_account.a_ledger_number_i AND a_transaction.a_account_code_c=a_account.a_account_code_c WHERE a_transaction.a_ledger_number_i = " + LedgerNumber + @" AND a_narrative_c LIKE 'AP%' AND a_transaction.a_transaction_status_l = true AND NOT (a_transaction.a_system_generated_l = true AND a_transaction.a_narrative_c LIKE 'Year end re-allocation%') " + DateFrom + DateTo + NumFrom + NumTo; Accounts = DbAdapter.RunQuery(Query, "Accounts", Transaction); Query = @"SELECT a_transaction.a_account_code_c AS AccountCode, a_transaction.a_transaction_date_d AS Date, a_transaction.a_amount_in_base_currency_n AS Amount, a_transaction.a_debit_credit_indicator_l, a_transaction.a_narrative_c AS Detail, a_transaction.a_reference_c AS ReferenceNumber, a_transaction.a_batch_number_i AS BatchNumber, a_transaction.a_cost_centre_code_c AS CostCentreCode FROM a_transaction WHERE a_transaction.a_ledger_number_i = " + LedgerNumber + @" AND a_transaction.a_transaction_status_l = true AND NOT (a_transaction.a_system_generated_l = true AND a_transaction.a_narrative_c LIKE 'Year end re-allocation%') AND a_narrative_c LIKE 'AP%'" + DateFrom + DateTo + NumFrom + NumTo + "ORDER BY a_cost_centre_code_c,a_account_code_c"; Details = DbAdapter.RunQuery(Query, "Details", Transaction); DataView tempView = Details.DefaultView; tempView.RowFilter = "detail LIKE 'AP Payment:%' AND detail LIKE '% AP: %'"; List <string> invoicesList = new List <string>(); foreach (DataRow row in tempView.ToTable().Rows) { invoicesList.AddRange(row["detail"].ToString().Split(new char[] { ' ' }, 4)[3].Split(new char[] { ',', ' ' }, StringSplitOptions.RemoveEmptyEntries)); } if (invoicesList.Count != 0) { DataTable Invoices = DbAdapter.RunQuery( "SELECT a_ap_number_i, a_document_code_c, a_document_code_c || ' (' || a_reference_c || ')' AS newref FROM a_ap_document WHERE a_ap_number_i IN(" + String.Join(",", invoicesList) + ")", "Invoices", Transaction); Invoices.PrimaryKey = new DataColumn[] { Invoices.Columns["a_ap_number_i"] }; for (int i = 0; i < Details.Rows.Count; i++) { if (Details.Rows[i]["detail"].ToString().Split(':')[0].ToString() == "AP Payment") { string detailString = Details.Rows[i]["detail"].ToString().Split(new char[] { ' ' }, 4)[3]; //If it contains a "-" it is a Payment for a supplier if (!detailString.Contains("-")) { string[] InvoiceNumbers = detailString.Split(new char[] { ',', ' ' }, StringSplitOptions.RemoveEmptyEntries); List <string> tempList = new List <string>(); foreach (string num in InvoiceNumbers) { int IntNum; if (int.TryParse(num, out IntNum)) { string Reference = Invoices.Rows.Find(IntNum)["newref"].ToString(); if (Reference == String.Empty) { tempList.Add(Catalog.GetString("AP: ") + Invoices.Rows.Find(IntNum)["a_document_code_c"].ToString()); } else { tempList.Add(Catalog.GetString("AP: ") + Reference); } } } Details.Rows[i]["referencenumber"] = String.Join("; ", tempList); } } } } }); ReturnDataSet.Tables.Add(Accounts); ReturnDataSet.Tables.Add(Details); return(ReturnDataSet); }
public void Test_YearEnd() { intLedgerNumber = CommonNUnitFunctions.CreateNewLedger(); TLedgerInfo LedgerInfo = new TLedgerInfo(intLedgerNumber); Assert.AreEqual(0, LedgerInfo.CurrentFinancialYear, "Before YearEnd, we should be in year 0"); TAccountPeriodInfo periodInfo = new TAccountPeriodInfo(intLedgerNumber, 1); Assert.AreEqual(new DateTime(DateTime.Now.Year, 1, 1), periodInfo.PeriodStartDate, "Calendar from base database should start with January 1st of this year"); CommonNUnitFunctions.LoadTestDataBase("csharp\\ICT\\Testing\\lib\\MFinance\\server\\GL\\test-sql\\gl-test-year-end.sql", intLedgerNumber); TCommonAccountingTool commonAccountingTool = new TCommonAccountingTool(intLedgerNumber, "NUNIT"); commonAccountingTool.AddBaseCurrencyJournal(); commonAccountingTool.JournalDescription = "Test Data accounts"; string strAccountGift = "0200"; string strAccountBank = "6200"; string strAccountExpense = "4100"; // Accounting of some gifts ... commonAccountingTool.AddBaseCurrencyTransaction( strAccountBank, "4301", "Gift Example", "Debit", MFinanceConstants.IS_DEBIT, 100); commonAccountingTool.AddBaseCurrencyTransaction( strAccountBank, "4302", "Gift Example", "Debit", MFinanceConstants.IS_DEBIT, 200); commonAccountingTool.AddBaseCurrencyTransaction( strAccountBank, "4303", "Gift Example", "Debit", MFinanceConstants.IS_DEBIT, 300); commonAccountingTool.AddBaseCurrencyTransaction( strAccountGift, "4301", "Gift Example", "Credit", MFinanceConstants.IS_CREDIT, 100); commonAccountingTool.AddBaseCurrencyTransaction( strAccountGift, "4302", "Gift Example", "Credit", MFinanceConstants.IS_CREDIT, 200); commonAccountingTool.AddBaseCurrencyTransaction( strAccountGift, "4303", "Gift Example", "Credit", MFinanceConstants.IS_CREDIT, 300); // Accounting of some expenses ... commonAccountingTool.AddBaseCurrencyTransaction( strAccountExpense, "4301", "Expense Example", "Debit", MFinanceConstants.IS_DEBIT, 150); commonAccountingTool.AddBaseCurrencyTransaction( strAccountExpense, "4302", "Expense Example", "Debit", MFinanceConstants.IS_DEBIT, 150); commonAccountingTool.AddBaseCurrencyTransaction( strAccountExpense, "4303", "Expense Example", "Debit", MFinanceConstants.IS_DEBIT, 200); commonAccountingTool.AddBaseCurrencyTransaction( strAccountBank, "4301", "Expense Example", "Credit", MFinanceConstants.IS_CREDIT, 150); commonAccountingTool.AddBaseCurrencyTransaction( strAccountBank, "4302", "Expense Example", "Credit", MFinanceConstants.IS_CREDIT, 150); commonAccountingTool.AddBaseCurrencyTransaction( strAccountBank, "4303", "Expense Example", "Credit", MFinanceConstants.IS_CREDIT, 200); commonAccountingTool.CloseSaveAndPost(); // returns true if posting seemed to work TVerificationResultCollection verificationResult = new TVerificationResultCollection(); bool blnLoop = true; while (blnLoop) { if (LedgerInfo.ProvisionalYearEndFlag) { blnLoop = false; } else { TVerificationResultCollection VerificationResult; List <Int32> glBatchNumbers; Boolean stewardshipBatch; TPeriodIntervalConnector.PeriodMonthEnd( intLedgerNumber, false, out glBatchNumbers, out stewardshipBatch, out VerificationResult); CommonNUnitFunctions.EnsureNullOrOnlyNonCriticalVerificationResults(VerificationResult, "Running MonthEnd gave critical error"); } } // check before year end that income and expense accounts are not 0 int intYear = 0; CheckGLMEntry(intLedgerNumber, intYear, strAccountBank, -50, 0, 50, 0, 100, 0); CheckGLMEntry(intLedgerNumber, intYear, strAccountExpense, 150, 0, 150, 0, 200, 0); CheckGLMEntry(intLedgerNumber, intYear, strAccountGift, 100, 0, 200, 0, 300, 0); // test that we cannot post to period 12 anymore, all periods are closed? LedgerInfo = new TLedgerInfo(intLedgerNumber); Assert.AreEqual(true, LedgerInfo.ProvisionalYearEndFlag, "Provisional YearEnd flag should be set"); List <Int32> glBatches = new List <int>(); TDBTransaction transaction = null; bool SubmissionOK = false; DBAccess.GDBAccessObj.GetNewOrExistingAutoTransaction( IsolationLevel.Serializable, TEnforceIsolationLevel.eilMinimum, ref transaction, ref SubmissionOK, delegate { // // Reallocation is never called explicitly like this - it's not really appropriate // because I'm about to call it again as part of YearEnd, below. // But a tweak in the reallocation code means that it should now cope with being called twice. TReallocation reallocation = new TReallocation(LedgerInfo, glBatches, transaction); reallocation.VerificationResultCollection = verificationResult; reallocation.IsInInfoMode = false; reallocation.RunOperation(); SubmissionOK = true; }); // check amounts after reallocation CheckGLMEntry(intLedgerNumber, intYear, strAccountBank, -50, 0, 50, 0, 100, 0); CheckGLMEntry(intLedgerNumber, intYear, strAccountExpense, 0, -150, 0, -150, 0, -200); CheckGLMEntry(intLedgerNumber, intYear, strAccountGift, 0, -100, 0, -200, 0, -300); // first run in info mode TPeriodIntervalConnector.PeriodYearEnd(intLedgerNumber, true, out glBatches, out verificationResult); CommonNUnitFunctions.EnsureNullOrOnlyNonCriticalVerificationResults(verificationResult, "YearEnd test should not have critical errors"); // now run for real TPeriodIntervalConnector.PeriodYearEnd(intLedgerNumber, false, out glBatches, out verificationResult); CommonNUnitFunctions.EnsureNullOrOnlyNonCriticalVerificationResults(verificationResult, "YearEnd should not have critical errors"); ++intYear; // check after year end that income and expense accounts are 0, bank account remains CheckGLMEntry(intLedgerNumber, intYear, strAccountBank, -50, 0, 50, 0, 100, 0); CheckGLMEntry(intLedgerNumber, intYear, strAccountExpense, 0, 0, 0, 0, 0, 0); CheckGLMEntry(intLedgerNumber, intYear, strAccountGift, 0, 0, 0, 0, 0, 0); // also check the glm period records CheckGLMPeriodEntry(intLedgerNumber, intYear, 1, strAccountBank, -50, 50, 100); CheckGLMPeriodEntry(intLedgerNumber, intYear, 1, strAccountExpense, 0, 0, 0); CheckGLMPeriodEntry(intLedgerNumber, intYear, 1, strAccountGift, 0, 0, 0); // 9700 is the account that the expenses and income from last year is moved to TGlmInfo glmInfo = new TGlmInfo(intLedgerNumber, intYear, "9700"); glmInfo.Reset(); Assert.IsTrue(glmInfo.MoveNext(), "9700 account not found"); Assert.AreEqual(100, glmInfo.YtdActualBase); Assert.AreEqual(0, glmInfo.ClosingPeriodActualBase); LedgerInfo = new TLedgerInfo(intLedgerNumber); Assert.AreEqual(1, LedgerInfo.CurrentFinancialYear, "After YearEnd, we are in a new financial year"); Assert.AreEqual(1, LedgerInfo.CurrentPeriod, "After YearEnd, we are in Period 1"); Assert.AreEqual(false, LedgerInfo.ProvisionalYearEndFlag, "After YearEnd, ProvisionalYearEnd flag should not be set"); periodInfo = new TAccountPeriodInfo(intLedgerNumber, 1); Assert.AreEqual(new DateTime(DateTime.Now.Year + 1, 1, 1), periodInfo.PeriodStartDate, "new Calendar should start with January 1st of next year"); }
public void Test_2YearEnds() { intLedgerNumber = CommonNUnitFunctions.CreateNewLedger(); CommonNUnitFunctions.LoadTestDataBase("csharp\\ICT\\Testing\\lib\\MFinance\\server\\GL\\test-sql\\gl-test-year-end.sql", intLedgerNumber); TLedgerInfo LedgerInfo = new TLedgerInfo(intLedgerNumber); for (int countYear = 0; countYear < 2; countYear++) { TLogging.Log("preparing year number " + countYear.ToString()); // accounting one gift string strAccountGift = "0200"; string strAccountBank = "6200"; TCommonAccountingTool commonAccountingTool = new TCommonAccountingTool(intLedgerNumber, "NUNIT"); commonAccountingTool.AddBaseCurrencyJournal(); commonAccountingTool.JournalDescription = "Test Data accounts"; commonAccountingTool.AddBaseCurrencyTransaction( strAccountBank, "4301", "Gift Example", "Debit", MFinanceConstants.IS_DEBIT, 100); commonAccountingTool.AddBaseCurrencyTransaction( strAccountGift, "4301", "Gift Example", "Credit", MFinanceConstants.IS_CREDIT, 100); Boolean PostedOk = commonAccountingTool.CloseSaveAndPost(); // returns true if posting seemed to work Assert.AreEqual(true, PostedOk, "Test batch can't be posted"); bool blnLoop = true; while (blnLoop) { // System.Windows.Forms.MessageBox.Show(LedgerInfo.CurrentPeriod.ToString(), "MonthEnd Period"); if (LedgerInfo.ProvisionalYearEndFlag) { blnLoop = false; } else { List <Int32> glBatchNumbers; Boolean stewardshipBatch; TVerificationResultCollection VerificationResult; TPeriodIntervalConnector.PeriodMonthEnd( intLedgerNumber, false, out glBatchNumbers, out stewardshipBatch, out VerificationResult); CommonNUnitFunctions.EnsureNullOrOnlyNonCriticalVerificationResults(VerificationResult, "MonthEnd gave critical error at Period" + LedgerInfo.CurrentPeriod + ":\r\n"); } } TDBTransaction transaction = null; bool SubmissionOK = false; DBAccess.GDBAccessObj.GetNewOrExistingAutoTransaction( IsolationLevel.Serializable, TEnforceIsolationLevel.eilMinimum, ref transaction, ref SubmissionOK, delegate { TLogging.Log("Closing year number " + countYear.ToString()); List <Int32> glBatches = new List <int>(); TReallocation reallocation = new TReallocation(LedgerInfo, glBatches, transaction); TVerificationResultCollection verificationResult = new TVerificationResultCollection(); reallocation.VerificationResultCollection = verificationResult; reallocation.IsInInfoMode = false; // Assert.AreEqual(1, reallocation.GetJobSize(), "Check 1 reallocation job is required"); // No job size is published by Reallocation reallocation.RunOperation(); TYearEnd YearEndOperator = new TYearEnd(LedgerInfo); TGlmNewYearInit glmNewYearInit = new TGlmNewYearInit(LedgerInfo, countYear, YearEndOperator, transaction); glmNewYearInit.VerificationResultCollection = verificationResult; glmNewYearInit.IsInInfoMode = false; // Assert.Greater(glmNewYearInit.GetJobSize(), 0, "Check that NewYearInit has work to do"); // in this version, GetJobSize returns 0 glmNewYearInit.RunOperation(); YearEndOperator.SetNextPeriod(transaction); SubmissionOK = true; }); } Assert.AreEqual(2, LedgerInfo.CurrentFinancialYear, "After YearEnd, Ledger is in year 2"); TAccountPeriodInfo periodInfo = new TAccountPeriodInfo(intLedgerNumber, 1); Assert.AreEqual(new DateTime(DateTime.Now.Year + 2, 1, 1), periodInfo.PeriodStartDate, "new Calendar should start with January 1st of next year"); } // Test_2YearEnds
public RunMonthEndChecks(TLedgerInfo ALedgerInfo) { FledgerInfo = ALedgerInfo; }
/// <summary> /// </summary> /// <param name="ALedgerInfo"></param> public TMonthEnd(TLedgerInfo ALedgerInfo) { FledgerInfo = ALedgerInfo; }