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> /// 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); }
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
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); }
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; } }
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
/// <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(); }
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 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); }
public static DataTable GetAvailableGLYearEnds(Int32 ALedgerNumber, System.Int32 ADiffPeriod, bool AIncludeNextYear, out String ADisplayMember, out String AValueMember) { //Create the table to populate the combobox DataTable ReturnTable = null; ADisplayMember = "YearEndDate"; AValueMember = "YearNumber"; string YearEnd = "YearEndDateLong"; DateTime YearEndDate; int YearNumber; bool NewTransaction = false; TDBTransaction Transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTransaction); ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction); AAccountingPeriodTable AccountingPeriods = AAccountingPeriodAccess.LoadViaALedger(ALedgerNumber, Transaction); if (LedgerTable.Count < 1) { throw new Exception("Ledger " + ALedgerNumber + " not found"); } 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 ReturnTable = new DataTable(); ReturnTable.Columns.Add(AValueMember, typeof(System.Int32)); ReturnTable.Columns.Add(ADisplayMember, typeof(String)); ReturnTable.Columns.Add(YearEnd, typeof(String)); ReturnTable.PrimaryKey = new DataColumn[] { ReturnTable.Columns[0] }; //Add the current year to the table YearNumber = LedgerRow.CurrentFinancialYear; YearEndDate = periodRow.PeriodEndDate; DataRow ResultRow = ReturnTable.NewRow(); ResultRow[0] = YearNumber; ResultRow[1] = YearEndDate.ToShortDateString(); 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"); try { 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.ToShortDateString(); ReturnTable.Rows.Add(ResultRow2); } } catch (Exception ex) { TLogging.Log(ex.ToString()); //Do nothing } if (NewTransaction) { DBAccess.GDBAccessObj.RollbackTransaction(); } return(ReturnTable); }
private static Int32 FindFinancialYearByDate(Int32 ALedgerNumber, DateTime ADate) { Int32 yearDateBelongsTo; DateTime yearStartDate = DateTime.Today; bool newTransaction = false; TDBTransaction Transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.Serializable, out newTransaction); ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, Transaction); if ((LedgerTable == null) || (LedgerTable.Count == 0)) { if (newTransaction) { DBAccess.GDBAccessObj.RollbackTransaction(); } return(99); } ALedgerRow LedgerRow = (ALedgerRow)LedgerTable.Rows[0]; yearDateBelongsTo = LedgerRow.CurrentFinancialYear; AAccountingPeriodTable AccPeriodTable = AAccountingPeriodAccess.LoadViaALedger(ALedgerNumber, Transaction); if ((AccPeriodTable == null) || (AccPeriodTable.Count == 0)) { if (newTransaction) { DBAccess.GDBAccessObj.RollbackTransaction(); } return(99); } //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--; } if (newTransaction) { DBAccess.GDBAccessObj.RollbackTransaction(); } //Set the year to return return(yearDateBelongsTo); }
/// <summary> /// using the calendar of the ledger to determine the financial period at the given date. /// </summary> /// <param name="ALedgerNumber"></param> /// <param name="ADateToTest"></param> /// <param name="AFinancialYear"></param> /// <param name="AFinancialPeriod"></param> /// <param name="ATransaction"></param> /// <param name="ADoFixDate">do you want to fix the date if it is outside the open periods; /// if the date is outside of the open periods, the date will be changed to the most appropriate date: /// if the original date is before the posting period, the first available date in the posting period will be returned, /// otherwise the last possible date</param> /// <returns>false if date needed to change</returns> public static bool GetLedgerDatePostingPeriod(Int32 ALedgerNumber, ref DateTime ADateToTest, out Int32 AFinancialYear, out Int32 AFinancialPeriod, TDBTransaction ATransaction, bool ADoFixDate) { #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); } 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 bool RetVal = false; AFinancialPeriod = -1; AFinancialYear = -1; AAccountingPeriodRow currentPeriodRow = null; AAccountingPeriodRow lastAllowedPeriodRow = null; ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, ATransaction); AAccountingPeriodTable AccountingPeriodTable = AAccountingPeriodAccess.LoadViaALedger(ALedgerNumber, ATransaction); #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 ((AccountingPeriodTable == null) || (AccountingPeriodTable.Count == 0)) { throw new EFinanceSystemDataTableReturnedNoDataException(String.Format(Catalog.GetString( "Function:{0} - Accounting Period data for Ledger number {1} does not exist or could not be accessed!"), Utilities.GetMethodName(true), ALedgerNumber)); } #endregion Validate Data try { int aCurrentPeriod = LedgerTable[0].CurrentPeriod; int anAllowedForwardPeriod = aCurrentPeriod + LedgerTable[0].NumberFwdPostingPeriods; foreach (AAccountingPeriodRow row in AccountingPeriodTable.Rows) { if (row.AccountingPeriodNumber == aCurrentPeriod) { currentPeriodRow = row; } if (row.AccountingPeriodNumber == anAllowedForwardPeriod) { lastAllowedPeriodRow = row; } if ((row.PeriodStartDate <= ADateToTest) && (ADateToTest <= row.PeriodEndDate)) { // check if this period is either the current period or one of the forward posting periods if (LedgerTable.Count == 1) { AFinancialPeriod = row.AccountingPeriodNumber; //This is the number of the period to which the "DateToTest" belongs //This can be // 1.) before the current period or in the last financial year // => FIX Date to be the first day of the current period // 2.) greater oder eqal currentperiod but within AllowedForwardperiod = no FIX required // 3.) after the allowed Forward period or even in a future financial year: = FIX Date to be the last day of the last allowed forward period if ((AFinancialPeriod >= aCurrentPeriod) && (AFinancialPeriod <= anAllowedForwardPeriod)) { AFinancialYear = LedgerTable[0].CurrentFinancialYear; RetVal = true; break; } } } } if (ADoFixDate && !RetVal) { if (ADateToTest < currentPeriodRow.PeriodStartDate) { ADateToTest = currentPeriodRow.PeriodStartDate; AFinancialYear = LedgerTable[0].CurrentFinancialYear; AFinancialPeriod = currentPeriodRow.AccountingPeriodNumber; } else { if (lastAllowedPeriodRow == null) { lastAllowedPeriodRow = AccountingPeriodTable[AccountingPeriodTable.Rows.Count - 1]; } if (ADateToTest > lastAllowedPeriodRow.PeriodEndDate) { ADateToTest = lastAllowedPeriodRow.PeriodEndDate; AFinancialYear = LedgerTable[0].CurrentFinancialYear; AFinancialPeriod = lastAllowedPeriodRow.AccountingPeriodNumber; } } } } catch (Exception ex) { TLogging.Log(String.Format("Method:{0} - Unexpected error!{1}{1}{2}", Utilities.GetMethodSignature(), Environment.NewLine, ex.Message)); throw ex; } return(RetVal); }
/// <summary> /// using the calendar of the ledger to determine the financial period at the given date. /// </summary> /// <param name="ALedgerNumber"></param> /// <param name="ADateToTest"></param> /// <param name="AFinancialYear"></param> /// <param name="AFinancialPeriod"></param> /// <param name="ATransaction"></param> /// <param name="ADoFixDate">do you want to fix the date if it is outside the open periods; /// if the date is outside of the open periods, the date will be changed to the most appropriate date: /// if the original date is before the posting period, the first available date in the posting period will be returned, /// otherwise the last possible date</param> /// <returns>false if date needed to change</returns> public static bool GetLedgerDatePostingPeriod(Int32 ALedgerNumber, ref DateTime ADateToTest, out Int32 AFinancialYear, out Int32 AFinancialPeriod, TDBTransaction ATransaction, bool ADoFixDate) { bool RetVal = false; AFinancialPeriod = -1; AFinancialYear = -1; AAccountingPeriodRow currentPeriodRow = null; AAccountingPeriodRow lastAllowedPeriodRow = null; AAccountingPeriodTable table = AAccountingPeriodAccess.LoadViaALedger(ALedgerNumber, ATransaction); ALedgerTable LedgerTable = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, ATransaction); try { if (LedgerTable.Count < 1) { throw new Exception("Ledger " + ALedgerNumber + " not found"); } int ACurrentPeriod = LedgerTable[0].CurrentPeriod; int AAllowedForwardPeriod = ACurrentPeriod + LedgerTable[0].NumberFwdPostingPeriods; foreach (AAccountingPeriodRow row in table.Rows) { if (row.AccountingPeriodNumber == ACurrentPeriod) { currentPeriodRow = row; } if (row.AccountingPeriodNumber == AAllowedForwardPeriod) { lastAllowedPeriodRow = row; } if ((row.PeriodStartDate <= ADateToTest) && (ADateToTest <= row.PeriodEndDate)) { // check if this period is either the current period or one of the forward posting periods if (LedgerTable.Count == 1) { AFinancialPeriod = row.AccountingPeriodNumber; //This is the number of the period to which the "DateToTest" belongs //This can be // 1.) before the current period or in the last financial year // => FIX Date to be the first day of the current period // 2.) greater oder eqal currentperiod but within AllowedForwardperiod = no FIX required // 3.) after the allowed Forward period or even in a future financial year: = FIX Date to be the last day of the last allowed forward period if ((AFinancialPeriod >= ACurrentPeriod) && (AFinancialPeriod <= AAllowedForwardPeriod)) { AFinancialYear = LedgerTable[0].CurrentFinancialYear; RetVal = true; break; } } } } if (ADoFixDate && !RetVal) { if (ADateToTest < currentPeriodRow.PeriodStartDate) { ADateToTest = currentPeriodRow.PeriodStartDate; AFinancialYear = LedgerTable[0].CurrentFinancialYear; AFinancialPeriod = currentPeriodRow.AccountingPeriodNumber; } else { if (lastAllowedPeriodRow == null) { lastAllowedPeriodRow = table[table.Rows.Count - 1]; } if (ADateToTest > lastAllowedPeriodRow.PeriodEndDate) { ADateToTest = lastAllowedPeriodRow.PeriodEndDate; AFinancialYear = LedgerTable[0].CurrentFinancialYear; AFinancialPeriod = lastAllowedPeriodRow.AccountingPeriodNumber; } } } } catch (Exception Ex) { TLogging.Log("Error in GetLedgerDatePostingPeriod: " + Ex.Message); throw; } return(RetVal); }