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