public static Boolean GetCurrentPeriodDates(Int32 ALedgerNumber, out DateTime AStartDateCurrentPeriod, out DateTime AEndDateCurrentPeriod) { DateTime startDate = new DateTime(); DateTime endDate = new DateTime(); TDBTransaction Transaction = null; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref Transaction, delegate { ALedgerTable ledgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction); AAccountingPeriodTable accountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber, ledgerTable[0].CurrentPeriod, Transaction); startDate = accountingPeriodTable[0].PeriodStartDate; endDate = accountingPeriodTable[0].PeriodEndDate; }); AStartDateCurrentPeriod = startDate; AEndDateCurrentPeriod = endDate; return(true); }
public static Boolean GetCurrentPeriodDates(Int32 ALedgerNumber, out DateTime AStartDateCurrentPeriod, out DateTime AEndDateCurrentPeriod) { DateTime startDate = new DateTime(); DateTime endDate = new DateTime(); TDBTransaction Transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("GetCurrentPeriodDates"); db.ReadTransaction( ref Transaction, delegate { ALedgerTable ledgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction); AAccountingPeriodTable accountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber, ledgerTable[0].CurrentPeriod, Transaction); startDate = accountingPeriodTable[0].PeriodStartDate; endDate = accountingPeriodTable[0].PeriodEndDate; }); db.CloseDBConnection(); AStartDateCurrentPeriod = startDate; AEndDateCurrentPeriod = endDate; return(true); }
/// <summary> /// Get the start and end date of a given period in a given ledger in the given year /// </summary> /// <returns>void</returns> public void GetPeriodDetails(int ledgernr, int period, out DateTime startOfPeriod, out DateTime endOfPeriod, int whichyear, int column) { int currentFinancialYear = parameters.Get("param_current_financial_year_i", column).ToInt(); TFinancialPeriod financialPeriod = new TFinancialPeriod(situation.GetDatabaseConnection(), period, whichyear, situation.GetParameters(), situation.GetColumn()); AAccountingPeriodTable tab = AAccountingPeriodAccess.LoadByPrimaryKey(ledgernr, period, situation.GetDatabaseConnection().Transaction); if (tab.Rows.Count == 1) { AAccountingPeriodRow row = tab[0]; try { endOfPeriod = new DateTime(row.PeriodEndDate.Year - (currentFinancialYear - financialPeriod.realYear), row.PeriodEndDate.Month, row.PeriodEndDate.Day); } catch (Exception) { endOfPeriod = new DateTime(row.PeriodEndDate.Year - (currentFinancialYear - financialPeriod.realYear), row.PeriodEndDate.Month, row.PeriodEndDate.Day - 1); } startOfPeriod = new DateTime(row.PeriodStartDate.Year - (currentFinancialYear - financialPeriod.realYear), row.PeriodStartDate.Month, row.PeriodStartDate.Day); } else { endOfPeriod = DateTime.MinValue; startOfPeriod = DateTime.MinValue; } financialPeriod = null; }
/// <summary> /// initialize the calender of the ledger for a specific year, so that we can have the specified number of open periods, /// and the current year is open. /// </summary> public static void InitCalendar() { int YearDifference = (FNumberOfClosedPeriods / 12); if (FNumberOfClosedPeriods % 12 >= DateTime.Today.Month) { YearDifference++; } AAccountingPeriodTable periodTable = AAccountingPeriodAccess.LoadViaALedger(FLedgerNumber, null); foreach (AAccountingPeriodRow row in periodTable.Rows) { row.PeriodStartDate = new DateTime(row.PeriodStartDate.Year - YearDifference, row.PeriodStartDate.Month, row.PeriodStartDate.Day); int LastDay = row.PeriodEndDate.Day; if (row.PeriodEndDate.Month == 2) { LastDay = DateTime.IsLeapYear(row.PeriodEndDate.Year - YearDifference) ? 29 : 28; } row.PeriodEndDate = new DateTime(row.PeriodEndDate.Year - YearDifference, row.PeriodEndDate.Month, LastDay); } AAccountingPeriodAccess.SubmitChanges(periodTable, null); }
public static Boolean GetCurrentPostingRangeDates(Int32 ALedgerNumber, out DateTime AStartDateCurrentPeriod, out DateTime AEndDateLastForwardingPeriod) { DateTime StartDateCurrentPeriod = new DateTime(); DateTime EndDateLastForwardingPeriod = new DateTime(); TDBTransaction Transaction = null; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref Transaction, delegate { ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction); AAccountingPeriodTable AccountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber, LedgerTable[0].CurrentPeriod, Transaction); StartDateCurrentPeriod = AccountingPeriodTable[0].PeriodStartDate; AccountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber, LedgerTable[0].CurrentPeriod + LedgerTable[0].NumberFwdPostingPeriods, Transaction); EndDateLastForwardingPeriod = AccountingPeriodTable[0].PeriodEndDate; }); AStartDateCurrentPeriod = StartDateCurrentPeriod; AEndDateLastForwardingPeriod = EndDateLastForwardingPeriod; return(true); }
public static bool GetFirstDayOfAccountingPeriod(Int32 ALedgerNumber, DateTime ADateInAPeriod, out DateTime AFirstDayOfPeriod, TDataBase ADataBase = null) { TDBTransaction Transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("GetFirstDayOfAccountingPeriod", ADataBase); DateTime Result = DateTime.MinValue; db.ReadTransaction(ref Transaction, delegate { // Get the accounting periods for this ledger. The table will contain more than 12 rows. // The start dates will be the correct day and month but may have been inserted for an arbitrary year when the table was first created. // We are really only interested in the Day anyway AAccountingPeriodTable periods = AAccountingPeriodAccess.LoadViaALedger(ALedgerNumber, Transaction); DataView periodsView = new DataView(periods, "", String.Format("{0} ASC", AAccountingPeriodTable.GetPeriodStartDateDBName()), DataViewRowState.CurrentRows); AAccountingPeriodRow row = (AAccountingPeriodRow)periodsView[0].Row; Result = new DateTime(ADateInAPeriod.Year, ADateInAPeriod.Month, row.PeriodStartDate.Day); if (ADateInAPeriod.Day < row.PeriodStartDate.Day) { Result = Result.AddMonths(-1); } }); if (ADataBase == null) { db.CloseDBConnection(); } AFirstDayOfPeriod = Result; return(Result != DateTime.MinValue); }
public static bool GetAccountingYearPeriodByDate(Int32 ALedgerNumber, DateTime ADate, out Int32 AYearNumber, out Int32 APeriodNumber) { bool newTransaction; Int32 CurrentFinancialYear; //Set the year to return AYearNumber = FindFinancialYearByDate(ALedgerNumber, ADate); if (AYearNumber == 99) { AYearNumber = 0; APeriodNumber = 0; return(false); } TDBTransaction Transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.Serializable, out newTransaction); ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction); CurrentFinancialYear = ((ALedgerRow)LedgerTable.Rows[0]).CurrentFinancialYear; AAccountingPeriodTable AccPeriodTableTmp = new AAccountingPeriodTable(); AAccountingPeriodRow TemplateRow = AccPeriodTableTmp.NewRowTyped(false); TemplateRow.LedgerNumber = ALedgerNumber; TemplateRow.PeriodStartDate = ADate.AddYears(CurrentFinancialYear - AYearNumber); TemplateRow.PeriodEndDate = ADate.AddYears(CurrentFinancialYear - AYearNumber); StringCollection operators = StringHelper.InitStrArr(new string[] { "=", "<=", ">=" }); AAccountingPeriodTable AccountingPeriodTable = AAccountingPeriodAccess.LoadUsingTemplate(TemplateRow, operators, null, Transaction); if ((AccountingPeriodTable == null) || (AccountingPeriodTable.Count == 0)) { if (newTransaction) { DBAccess.GDBAccessObj.RollbackTransaction(); } APeriodNumber = 0; return(false); } AAccountingPeriodRow AccountingPeriodRow = (AAccountingPeriodRow)AccountingPeriodTable.Rows[0]; APeriodNumber = AccountingPeriodRow.AccountingPeriodNumber; if (newTransaction) { DBAccess.GDBAccessObj.RollbackTransaction(); } return(true); }
public static bool GetCurrentPostingRangeDates(Int32 ALedgerNumber, out DateTime AStartDateCurrentPeriod, out DateTime AEndDateLastForwardingPeriod) { bool newTransaction = false; TDBTransaction Transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.Serializable, out newTransaction); ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction); AAccountingPeriodTable AccountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber, LedgerTable[0].CurrentPeriod, Transaction); AStartDateCurrentPeriod = AccountingPeriodTable[0].PeriodStartDate; AccountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber, LedgerTable[0].CurrentPeriod + LedgerTable[0].NumberFwdPostingPeriods, Transaction); AEndDateLastForwardingPeriod = AccountingPeriodTable[0].PeriodEndDate; if (newTransaction) { DBAccess.GDBAccessObj.RollbackTransaction(); } return(true); }
private static Int32 FindFinancialYearByDate(Int32 ALedgerNumber, DateTime ADate) { Int32 yearDateBelongsTo = 99; DateTime yearStartDate = DateTime.Today; TDBTransaction transaction = null; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.Serializable, ref transaction, delegate { ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, transaction); if (LedgerTable.Count == 0) { return; } ALedgerRow LedgerRow = (ALedgerRow)LedgerTable.Rows[0]; yearDateBelongsTo = LedgerRow.CurrentFinancialYear; AAccountingPeriodTable AccPeriodTable = AAccountingPeriodAccess.LoadViaALedger(ALedgerNumber, transaction); if (AccPeriodTable.Count == 0) { return; } //Find earliest start date (don't assume PK order) AAccountingPeriodRow AccPeriodRow = null; for (int i = 0; i < AccPeriodTable.Count; i++) { DateTime currentStartDate; AccPeriodRow = (AAccountingPeriodRow)AccPeriodTable.Rows[i]; currentStartDate = AccPeriodRow.PeriodStartDate; if (i > 0) { if (yearStartDate > currentStartDate) { yearStartDate = currentStartDate; } } else { yearStartDate = currentStartDate; } } //Find the correct year while (ADate < yearStartDate) { ADate = ADate.AddYears(1); yearDateBelongsTo--; } }); // Get NewOrExisting AutoReadTransaction //Set the year to return return(yearDateBelongsTo); } // Find FinancialYear ByDate
private void LoadData() { TDBTransaction Transaction = null; try { DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref Transaction, delegate { FPeriodTable = AAccountingPeriodAccess.LoadViaALedger(FLedgerNumber, Transaction); #region Validate Data if ((FPeriodTable == null) || (FPeriodTable.Count == 0)) { throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString( "Function:{0} - Accounting Period data for Ledger {1} does not exist or could not be accessed!"), Utilities.GetMethodName(true), FLedgerNumber)); } #endregion Validate Data }); } catch (Exception ex) { TLogging.LogException(ex, Utilities.GetMethodSignature()); throw; } }
public static bool GetFirstDayOfAccountingPeriod(Int32 ALedgerNumber, DateTime ADateInAPeriod, out DateTime AFirstDayOfPeriod) { TDBTransaction Transaction = null; DateTime Result = DateTime.MinValue; // Used by importing so the isolation level is serializable DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.Serializable, ref Transaction, delegate { // Get the accounting periods for this ledger. The table will contain more than 12 rows. // The start dates will be the correct day and month but may have been inserted for an arbitrary year when the table was first created. // We are really only interested in the Day anyway AAccountingPeriodTable periods = AAccountingPeriodAccess.LoadViaALedger(ALedgerNumber, Transaction); DataView periodsView = new DataView(periods, "", String.Format("{0} ASC", AAccountingPeriodTable.GetPeriodStartDateDBName()), DataViewRowState.CurrentRows); AAccountingPeriodRow row = (AAccountingPeriodRow)periodsView[0].Row; Result = new DateTime(ADateInAPeriod.Year, ADateInAPeriod.Month, row.PeriodStartDate.Day); if (ADateInAPeriod.Day < row.PeriodStartDate.Day) { Result = Result.AddMonths(-1); } }); AFirstDayOfPeriod = Result; return(Result != DateTime.MinValue); }
/// <summary> /// Get the start and end date of the given period in the given year /// </summary> public static bool GetStartAndEndDateOfPeriod(Int32 ALedgerNumber, Int32 AYear, Int32 APeriodNumber, out DateTime APeriodStartDate, out DateTime APeriodEndDate, TDBTransaction ATransaction) { #region Validate Arguments if (ALedgerNumber <= 0) { throw new EFinanceSystemInvalidLedgerNumberException(String.Format(Catalog.GetString( "Function:{0} - The Ledger number must be greater than 0!"), Utilities.GetMethodName(true)), ALedgerNumber); } // ATransaction can be null //else if (ATransaction == null) //{ // throw new EFinanceSystemDBTransactionNullException(String.Format(Catalog.GetString( // "Function:{0} - Database Transaction must not be NULL!"), // Utilities.GetMethodName(true))); //} #endregion Validate Arguments // invalid period if (APeriodNumber == -1) { APeriodStartDate = DateTime.MinValue; APeriodEndDate = DateTime.MaxValue; return(false); } AAccountingPeriodTable AccPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber, APeriodNumber, ATransaction); #region Validate Data if ((AccPeriodTable == null) || (AccPeriodTable.Count == 0)) { throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString( "Function:{0} - Accounting Period data for period {1} in Ledger number {2} does not exist or could not be accessed!"), Utilities.GetMethodName(true), APeriodNumber, ALedgerNumber)); } #endregion Validate Data AAccountingPeriodRow AccPeriodRow = (AAccountingPeriodRow)AccPeriodTable.Rows[0]; ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, ATransaction); Int32 currentYear = LedgerTable[0].CurrentFinancialYear; Int32 yearsAgo = currentYear - AYear; APeriodStartDate = AccPeriodRow.PeriodStartDate.AddYears(0 - yearsAgo); APeriodEndDate = AccPeriodRow.PeriodEndDate.AddYears(0 - yearsAgo); return(true); }
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 static bool GetAccountingYearPeriodByDate(Int32 ALedgerNumber, DateTime ADate, out Int32 AYearNumber, out Int32 APeriodNumber) { Int32 CurrentFinancialYear; //Set the year to return Int32 YearNumber = FindFinancialYearByDate(ALedgerNumber, ADate); AYearNumber = YearNumber; if (AYearNumber == 99) { AYearNumber = 0; APeriodNumber = 0; return(false); } Int32 PeriodNumber = 0; TDBTransaction transaction = null; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.Serializable, ref transaction, delegate { ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, transaction); CurrentFinancialYear = ((ALedgerRow)LedgerTable.Rows[0]).CurrentFinancialYear; AAccountingPeriodTable AccPeriodTableTmp = new AAccountingPeriodTable(); AAccountingPeriodRow TemplateRow = AccPeriodTableTmp.NewRowTyped(false); TemplateRow.LedgerNumber = ALedgerNumber; TemplateRow.PeriodStartDate = ADate.AddYears(CurrentFinancialYear - YearNumber); TemplateRow.PeriodEndDate = ADate.AddYears(CurrentFinancialYear - YearNumber); StringCollection operators = StringHelper.InitStrArr(new string[] { "=", "<=", ">=" }); AAccountingPeriodTable AccountingPeriodTable = AAccountingPeriodAccess.LoadUsingTemplate(TemplateRow, operators, null, transaction); if (AccountingPeriodTable.Count == 0) { return; } AAccountingPeriodRow AccountingPeriodRow = (AAccountingPeriodRow)AccountingPeriodTable.Rows[0]; PeriodNumber = AccountingPeriodRow.AccountingPeriodNumber; }); APeriodNumber = PeriodNumber; return(true); } // Get AccountingYear Period ByDate
private DataTable GetAccountingPeriodListTable(TDBTransaction AReadTransaction, System.Int32 ALedgerNumber, string ATableName) { StringCollection FieldList = new StringCollection(); FieldList.Add(AAccountingPeriodTable.GetLedgerNumberDBName()); FieldList.Add(AAccountingPeriodTable.GetAccountingPeriodNumberDBName()); FieldList.Add(AAccountingPeriodTable.GetAccountingPeriodDescDBName()); FieldList.Add(AAccountingPeriodTable.GetPeriodStartDateDBName()); FieldList.Add(AAccountingPeriodTable.GetPeriodEndDateDBName()); return(AAccountingPeriodAccess.LoadViaALedger(ALedgerNumber, FieldList, AReadTransaction)); }
/// <summary> /// The AccountingPeriod Rows are updated ... /// </summary> override public Int32 RunOperation() { bool NewTransaction; Int32 JobSize = 0; Boolean ShouldCommit = false; TDBTransaction Transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.Serializable, out NewTransaction); AAccountingPeriodTable AccountingPeriodTbl = null; try { AccountingPeriodTbl = AAccountingPeriodAccess.LoadViaALedger(FLedgerNumber, Transaction); foreach (AAccountingPeriodRow accountingPeriodRow in AccountingPeriodTbl.Rows) { accountingPeriodRow.PeriodStartDate = accountingPeriodRow.PeriodStartDate.AddDays(1).AddYears(1).AddDays(-1); accountingPeriodRow.PeriodEndDate = accountingPeriodRow.PeriodEndDate.AddDays(1).AddYears(1).AddDays(-1); JobSize++; } if (DoExecuteableCode) { AAccountingPeriodAccess.SubmitChanges(AccountingPeriodTbl, Transaction); ShouldCommit = true; } } catch (Exception Exc) { TLogging.Log("Exception during running the AccountPeriod To New Year operation:" + Environment.NewLine + Exc.ToString()); throw; } finally { if (NewTransaction) { if (ShouldCommit) { DBAccess.GDBAccessObj.CommitTransaction(); } else { DBAccess.GDBAccessObj.RollbackTransaction(); } } } return JobSize; } // RunOperation
public static bool GetCurrentPeriodDates(Int32 ALedgerNumber, out DateTime AStartDate, out DateTime AEndDate) { TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(IsolationLevel.Serializable); ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction); AAccountingPeriodTable AccountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber, LedgerTable[0].CurrentPeriod, Transaction); AStartDate = AccountingPeriodTable[0].PeriodStartDate; AEndDate = AccountingPeriodTable[0].PeriodEndDate; DBAccess.GDBAccessObj.RollbackTransaction(); return(true); }
private decimal GetCorporateExchangeRateFromDB(TDataBase databaseConnection, int pv_ledger_number_i, int pv_year_i, int pv_period_i, int currentFinancialYear) { ALedgerTable ledgerTable = ALedgerAccess.LoadByPrimaryKey(pv_ledger_number_i, databaseConnection.Transaction); AAccountingPeriodTable AccountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(pv_ledger_number_i, pv_period_i, databaseConnection.Transaction); if (AccountingPeriodTable.Rows.Count < 1) { return(-1); // This is poor (because the caller can blindly use it!) // I wonder whether an exception would be better. (Tim Ingham, Oct 2013) } if (currentFinancialYear < 0) { currentFinancialYear = ledgerTable[0].CurrentFinancialYear; } DateTime startOfPeriod = AccountingPeriodTable[0].PeriodStartDate; DateTime endOfPeriod = AccountingPeriodTable[0].PeriodEndDate; startOfPeriod = new DateTime(startOfPeriod.Year - (currentFinancialYear - pv_year_i), startOfPeriod.Month, startOfPeriod.Day); if ((endOfPeriod.Month == 2) && (endOfPeriod.Day == 29) && (((currentFinancialYear - pv_year_i)) % 4 != 0)) { endOfPeriod = endOfPeriod.AddDays(-1); } endOfPeriod = new DateTime(endOfPeriod.Year - (currentFinancialYear - pv_year_i), endOfPeriod.Month, endOfPeriod.Day); // get the corporate exchange rate between base and intl currency for the period decimal IntlToBaseExchRate; TExchangeRateTools.GetCorporateExchangeRate(ledgerTable[0].IntlCurrency, ledgerTable[0].BaseCurrency, startOfPeriod, endOfPeriod, out IntlToBaseExchRate, databaseConnection); return(IntlToBaseExchRate); }
public static bool GetCurrentPeriodDates(Int32 ALedgerNumber, out DateTime AStartDate, out DateTime AEndDate) { TDBTransaction transaction = null; AAccountingPeriodTable AccountingPeriodTable = null; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.Serializable, TEnforceIsolationLevel.eilMinimum, ref transaction, delegate { ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, transaction); AccountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber, LedgerTable[0].CurrentPeriod, transaction); }); AStartDate = AccountingPeriodTable[0].PeriodStartDate; AEndDate = AccountingPeriodTable[0].PeriodEndDate; return(true); }
public static bool GetPeriodDates(Int32 ALedgerNumber, Int32 AYearNumber, Int32 ADiffPeriod, Int32 APeriodNumber, out DateTime AStartDatePeriod, out DateTime AEndDatePeriod) { if ((AYearNumber < 0) || (APeriodNumber < 0)) { AStartDatePeriod = DateTime.MinValue; AEndDatePeriod = DateTime.MinValue; return(false); } DateTime StartDatePeriod = new DateTime(); DateTime EndDatePeriod = new DateTime(); TDBTransaction Transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("GetPeriodDates"); db.ReadTransaction( ref Transaction, delegate { AAccountingPeriodTable AccountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber, APeriodNumber, Transaction); // TODO: ADiffPeriod for support of different financial years StartDatePeriod = AccountingPeriodTable[0].PeriodStartDate; EndDatePeriod = AccountingPeriodTable[0].PeriodEndDate; ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction); //TODO: Calendar vs Financial Date Handling - Should this be number of accounting periods rather than 12 StartDatePeriod = StartDatePeriod.AddMonths(-12 * (LedgerTable[0].CurrentFinancialYear - AYearNumber)); EndDatePeriod = EndDatePeriod.AddMonths(-12 * (LedgerTable[0].CurrentFinancialYear - AYearNumber)); }); db.CloseDBConnection(); AStartDatePeriod = StartDatePeriod; AEndDatePeriod = EndDatePeriod; return(true); }
public static bool GetCurrentPeriodDates(Int32 ALedgerNumber, out DateTime AStartDate, out DateTime AEndDate) { TDBTransaction transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("GetCurrentPeriodDates"); AAccountingPeriodTable AccountingPeriodTable = null; db.ReadTransaction(ref transaction, delegate { ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, transaction); AccountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber, LedgerTable[0].CurrentPeriod, transaction); }); db.CloseDBConnection(); AStartDate = AccountingPeriodTable[0].PeriodStartDate; AEndDate = AccountingPeriodTable[0].PeriodEndDate; return(true); }
public static bool GetPeriodDates(Int32 ALedgerNumber, Int32 AYearNumber, Int32 ADiffPeriod, Int32 APeriodNumber, out DateTime AStartDatePeriod, out DateTime AEndDatePeriod) { if ((AYearNumber < 0) || (APeriodNumber < 0)) { AStartDatePeriod = DateTime.MinValue; AEndDatePeriod = DateTime.MinValue; return(false); } DateTime StartDatePeriod = new DateTime(); DateTime EndDatePeriod = new DateTime(); TDBTransaction Transaction = null; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref Transaction, delegate { AAccountingPeriodTable AccountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber, APeriodNumber, Transaction); // TODO: ADiffPeriod for support of different financial years StartDatePeriod = AccountingPeriodTable[0].PeriodStartDate; EndDatePeriod = AccountingPeriodTable[0].PeriodEndDate; ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction); //TODO: Calendar vs Financial Date Handling - Should this be number of accounting periods rather than 12 StartDatePeriod = StartDatePeriod.AddMonths(-12 * (LedgerTable[0].CurrentFinancialYear - AYearNumber)); EndDatePeriod = EndDatePeriod.AddMonths(-12 * (LedgerTable[0].CurrentFinancialYear - AYearNumber)); }); AStartDatePeriod = StartDatePeriod; AEndDatePeriod = EndDatePeriod; return(true); }
/// <summary> /// initialize the calender of the ledger for a specific year, so that we can have the specified number of open periods, /// and the current year is open. /// </summary> public static void InitCalendar() { TDataBase db = DBAccess.Connect("InitCalendar"); TDBTransaction Transaction = new TDBTransaction(); bool SubmitOK = false; db.WriteTransaction(ref Transaction, ref SubmitOK, delegate { int YearDifference = (FNumberOfClosedPeriods / 12); if (FNumberOfClosedPeriods % 12 >= DateTime.Today.Month) { YearDifference++; } AAccountingPeriodTable periodTable = AAccountingPeriodAccess.LoadViaALedger(FLedgerNumber, Transaction); foreach (AAccountingPeriodRow row in periodTable.Rows) { row.PeriodStartDate = new DateTime(row.PeriodStartDate.Year - YearDifference, row.PeriodStartDate.Month, row.PeriodStartDate.Day); int LastDay = row.PeriodEndDate.Day; if (row.PeriodEndDate.Month == 2) { LastDay = DateTime.IsLeapYear(row.PeriodEndDate.Year - YearDifference) ? 29 : 28; } row.PeriodEndDate = new DateTime(row.PeriodEndDate.Year - YearDifference, row.PeriodEndDate.Month, LastDay); } AAccountingPeriodAccess.SubmitChanges(periodTable, Transaction); SubmitOK = true; }); db.CloseDBConnection(); }
/// <summary> /// get the start and end date of the given period in the current year /// </summary> public static bool GetStartAndEndDateOfPeriod(Int32 ALedgerNumber, Int32 APeriodNumber, out DateTime APeriodStartDate, out DateTime APeriodEndDate, TDBTransaction ATransaction) { // invalid period if (APeriodNumber == -1) { APeriodStartDate = DateTime.MinValue; APeriodEndDate = DateTime.MaxValue; return(false); } AAccountingPeriodTable AccPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber, APeriodNumber, ATransaction); AAccountingPeriodRow AccPeriodRow = (AAccountingPeriodRow)AccPeriodTable.Rows[0]; APeriodStartDate = AccPeriodRow.PeriodStartDate; APeriodEndDate = AccPeriodRow.PeriodEndDate; return(true); }
private void LoadData() { bool NewTransaction; TDBTransaction transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, out NewTransaction); try { periodTable = AAccountingPeriodAccess.LoadViaALedger(intLedgerNumber, transaction); if (NewTransaction) { DBAccess.GDBAccessObj.CommitTransaction(); } } catch (Exception) { DBAccess.GDBAccessObj.RollbackTransaction(); throw; } }
private void LoadData() { TDBTransaction Transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("TAccountPeriodInfo.LoadData", FDataBase); try { db.ReadTransaction( ref Transaction, delegate { FPeriodTable = AAccountingPeriodAccess.LoadViaALedger(FLedgerNumber, Transaction); #region Validate Data if ((FPeriodTable == null) || (FPeriodTable.Count == 0)) { throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString( "Function:{0} - Accounting Period data for Ledger {1} does not exist or could not be accessed!"), Utilities.GetMethodName(true), FLedgerNumber)); } #endregion Validate Data }); } catch (Exception ex) { TLogging.LogException(ex, Utilities.GetMethodSignature()); throw; } if (FDataBase == null) { db.CloseDBConnection(); } }
public static bool GetPeriodDates(Int32 ALedgerNumber, Int32 AYearNumber, Int32 ADiffPeriod, Int32 APeriodNumber, out DateTime AStartDatePeriod, out DateTime AEndDatePeriod) { if ((AYearNumber < 0) || (APeriodNumber < 0)) { AStartDatePeriod = DateTime.MinValue; AEndDatePeriod = DateTime.MinValue; return(false); } bool NewTransaction; TDBTransaction Transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.Serializable, out NewTransaction); AAccountingPeriodTable AccountingPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber, APeriodNumber, Transaction); // TODO: ADiffPeriod for support of different financial years AStartDatePeriod = AccountingPeriodTable[0].PeriodStartDate; AEndDatePeriod = AccountingPeriodTable[0].PeriodEndDate; ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction); AStartDatePeriod = AStartDatePeriod.AddMonths(-12 * (LedgerTable[0].CurrentFinancialYear - AYearNumber)); AEndDatePeriod = AEndDatePeriod.AddMonths(-12 * (LedgerTable[0].CurrentFinancialYear - AYearNumber)); if (NewTransaction) { DBAccess.GDBAccessObj.RollbackTransaction(); } return(true); }
private static string BudgetRevisionYearName(int ALedgerNumber, int ABudgetRevisionYear) { int budgetYear = 0; ALedgerTable LedgerTable = null; AAccountingPeriodTable accPeriodTable = null; TDBTransaction Transaction = null; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref Transaction, delegate { LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction); accPeriodTable = AAccountingPeriodAccess.LoadByPrimaryKey(ALedgerNumber, 1, Transaction); }); ALedgerRow ledgerRow = (ALedgerRow)LedgerTable.Rows[0]; AAccountingPeriodRow accPeriodRow = (AAccountingPeriodRow)accPeriodTable.Rows[0]; DateTime CurrentYearEnd = TAccountingPeriodsWebConnector.GetPeriodEndDate(ALedgerNumber, ledgerRow.CurrentFinancialYear, 0, ledgerRow.NumberOfAccountingPeriods); budgetYear = ABudgetRevisionYear + CurrentYearEnd.Year - ledgerRow.CurrentFinancialYear; if (budgetYear == accPeriodRow.PeriodStartDate.Year) { return("This"); } else { return("Next"); } }
public static DataTable GetAvailableGLYearsHOSA(Int32 ALedgerNumber, out String ADisplayMember, out String AValueMember, out String ADescriptionMember) { DateTime YearEndDate; int YearNumber; ADisplayMember = "YearEndDate"; AValueMember = "YearNumber"; ADescriptionMember = "YearEndDateLong"; DataTable BatchTable = null; TDBTransaction transaction = null; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref transaction, delegate { ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, transaction); AAccountingPeriodTable AccountingPeriods = AAccountingPeriodAccess.LoadViaALedger(ALedgerNumber, transaction); if (LedgerTable.Rows.Count < 1) { return; } ALedgerRow LedgerRow = (ALedgerRow)LedgerTable[0]; AccountingPeriods.DefaultView.RowFilter = String.Format("{0}={1}", AAccountingPeriodTable.GetAccountingPeriodNumberDBName(), LedgerRow.NumberOfAccountingPeriods); //Get last period row AAccountingPeriodRow periodRow = (AAccountingPeriodRow)AccountingPeriods.DefaultView[0].Row; //Create the table to populate the combobox BatchTable = new DataTable(); BatchTable.Columns.Add("YearNumber", typeof(System.Int32)); BatchTable.Columns.Add("YearEndDate", typeof(String)); BatchTable.Columns.Add("YearEndDateLong", typeof(String)); BatchTable.PrimaryKey = new DataColumn[] { BatchTable.Columns[0] }; //Add the current year to the table YearNumber = LedgerRow.CurrentFinancialYear; YearEndDate = periodRow.PeriodEndDate; DataRow ResultRow = BatchTable.NewRow(); ResultRow[0] = YearNumber; ResultRow[1] = YearEndDate.ToShortDateString(); ResultRow[2] = YearEndDate.ToLongDateString(); BatchTable.Rows.Add(ResultRow); //Retrieve all previous years string sql = String.Format("SELECT DISTINCT {0} AS batchYear" + " FROM PUB_{1}" + " WHERE {2} = {3} And {0} < {4}" + " ORDER BY 1 DESC", ABatchTable.GetBatchYearDBName(), ABatchTable.GetTableDBName(), ABatchTable.GetLedgerNumberDBName(), ALedgerNumber, YearNumber); DataTable BatchYearTable = DBAccess.GDBAccessObj.SelectDT(sql, "BatchYearTable", transaction); BatchYearTable.DefaultView.Sort = String.Format("batchYear DESC"); foreach (DataRowView row in BatchYearTable.DefaultView) { DataRow currentBatchYearRow = row.Row; Int32 currentBatchYear = (Int32)currentBatchYearRow[0]; if (YearNumber != currentBatchYear) { YearNumber -= 1; YearEndDate = DecrementYear(YearEndDate); if (YearNumber != currentBatchYear) { //Gap in year numbers throw new Exception(String.Format(Catalog.GetString("Year {0} not found for Ledger {1}"), YearNumber, ALedgerNumber)); } } DataRow ResultRow2 = BatchTable.NewRow(); ResultRow2[0] = YearNumber; ResultRow2[1] = YearEndDate.ToShortDateString(); ResultRow2[2] = YearEndDate.ToLongDateString(); BatchTable.Rows.Add(ResultRow2); } // foreach }); // Get NewOrExisting AutoReadTransaction return(BatchTable); } // Get Available GLYears HOSA
public static DataTable GetAvailableGLYearEnds(Int32 ALedgerNumber, Int32 ADiffPeriod, bool AIncludeNextYear, out String ADisplayMember, out String AValueMember) { const string INTL_DATE_FORMAT = "yyyy-MM-dd"; //Create the table to populate the combobox DataTable ReturnTable = null; AValueMember = "YearNumber"; ADisplayMember = "YearEndDate"; TDBTransaction Transaction = null; try { DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref Transaction, delegate { DateTime yearEndDate; DateTime currentYearEndDate; int yearNumber; int currentFinancialYear; ALedgerTable LedgerTable = null; AAccountingPeriodTable AccountingPeriods = null; LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction); AccountingPeriods = AAccountingPeriodAccess.LoadViaALedger(ALedgerNumber, Transaction); #region Validate Data if ((LedgerTable == null) || (LedgerTable.Count == 0)) { throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString( "Function:{0} - Ledger data for Ledger number {1} does not exist or could not be accessed!"), Utilities.GetMethodName(true), ALedgerNumber)); } else if ((AccountingPeriods == null) || (AccountingPeriods.Count == 0)) { throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString( "Function:{0} - AAccount Period data for Ledger number {1} does not exist or could not be accessed!"), Utilities.GetMethodName(true), ALedgerNumber)); } #endregion Validate Data ALedgerRow ledgerRow = (ALedgerRow)LedgerTable[0]; currentYearEndDate = GetPeriodEndDate(ALedgerNumber, ledgerRow.CurrentFinancialYear, ADiffPeriod, ledgerRow.NumberOfAccountingPeriods); currentFinancialYear = ledgerRow.CurrentFinancialYear; //Filter to highest period number AccountingPeriods.DefaultView.RowFilter = String.Format("{0}={1}", AAccountingPeriodTable.GetAccountingPeriodNumberDBName(), ledgerRow.NumberOfAccountingPeriods); //Get last period row AAccountingPeriodRow periodRow = (AAccountingPeriodRow)AccountingPeriods.DefaultView[0].Row; //Create the table to populate the combobox ReturnTable = new DataTable(); ReturnTable.Columns.Add("YearNumber", typeof(System.Int32)); ReturnTable.Columns.Add("YearEndDate", typeof(String)); ReturnTable.Columns.Add("YearEndDateLong", typeof(String)); ReturnTable.PrimaryKey = new DataColumn[] { ReturnTable.Columns[0] }; //Add the current year to the table yearNumber = currentFinancialYear; yearEndDate = periodRow.PeriodEndDate; DataRow ResultRow = ReturnTable.NewRow(); ResultRow[0] = yearNumber; ResultRow[1] = yearEndDate.ToString(INTL_DATE_FORMAT); ResultRow[2] = yearEndDate.ToLongDateString(); ReturnTable.Rows.Add(ResultRow); //Retrieve all previous years string sql = String.Format("SELECT DISTINCT {0} AS batchYear" + " FROM PUB_{1}" + " WHERE {2} = {3} And {0} < {4}" + " ORDER BY 1 DESC", ABatchTable.GetBatchYearDBName(), ABatchTable.GetTableDBName(), ABatchTable.GetLedgerNumberDBName(), ALedgerNumber, yearNumber); DataTable BatchYearTable = DBAccess.GDBAccessObj.SelectDT(sql, "BatchYearTable", Transaction); BatchYearTable.DefaultView.Sort = String.Format("batchYear DESC"); foreach (DataRowView row in BatchYearTable.DefaultView) { DataRow currentBatchYearRow = row.Row; Int32 currentBatchYear = Convert.ToInt32(currentBatchYearRow[0]); if (yearNumber != currentBatchYear) { yearNumber -= 1; yearEndDate = DecrementYear(yearEndDate); if (yearNumber != currentBatchYear) { //Gap in year numbers throw new Exception(String.Format(Catalog.GetString("Year {0} not found for Ledger {1}"), yearNumber, ALedgerNumber)); } } DataRow ResultRow2 = ReturnTable.NewRow(); ResultRow2[0] = yearNumber; ResultRow2[1] = yearEndDate.ToString(INTL_DATE_FORMAT); ReturnTable.Rows.Add(ResultRow2); } if (AIncludeNextYear && (ReturnTable.Rows.Find(currentFinancialYear + 1) == null)) { DataRow resultRow = ReturnTable.NewRow(); resultRow[0] = currentFinancialYear + 1; resultRow[1] = currentYearEndDate.AddYears(1).ToString(INTL_DATE_FORMAT); //resultRow[2] = currentYearEndDate.ToString("dd-MMM-yyyy"); ReturnTable.Rows.InsertAt(resultRow, 0); } }); // Get NewOrExisting AutoReadTransaction } catch (Exception ex) { TLogging.LogException(ex, Utilities.GetMethodSignature()); throw; } return(ReturnTable); }