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