public static System.DateTime GetPeriodEndDate(Int32 ALedgerNumber, System.Int32 AYear, System.Int32 ADiffPeriod,
System.Int32 APeriod, TDataBase ADataBase)
{
System.Int32 RealYear = 0;
System.Int32 RealPeriod = 0;
System.Type typeofTable = null;
TCacheable CachePopulator = new TCacheable();
DateTime ReturnValue = DateTime.Now;
GetRealPeriod(ALedgerNumber, ADiffPeriod, AYear, APeriod, out RealPeriod, out RealYear);
DataTable UntypedTable = CachePopulator.GetCacheableTable(TCacheableFinanceTablesEnum.AccountingPeriodList,
"",
false,
ALedgerNumber,
out typeofTable, ADataBase);
AAccountingPeriodTable CachedDataTable = (AAccountingPeriodTable)UntypedTable;
CachedDataTable.DefaultView.RowFilter = AAccountingPeriodTable.GetAccountingPeriodNumberDBName() + " = " + RealPeriod;
if (CachedDataTable.DefaultView.Count > 0)
{
ReturnValue = ((AAccountingPeriodRow)CachedDataTable.DefaultView[0].Row).PeriodEndDate;
ALedgerTable Ledger = (ALedgerTable)CachePopulator.GetCacheableTable(TCacheableFinanceTablesEnum.LedgerDetails,
"",
false,
ALedgerNumber,
out typeofTable, ADataBase);
ReturnValue = ReturnValue.AddYears(RealYear - Ledger[0].CurrentFinancialYear);
}
return(ReturnValue);
}
public static System.DateTime GetPeriodEndDate(Int32 ALedgerNumber, System.Int32 AYear, System.Int32 ADiffPeriod, System.Int32 APeriod)
{
System.Int32 RealYear = 0;
System.Int32 RealPeriod = 0;
System.Type typeofTable = null;
TCacheable CachePopulator = new TCacheable();
DateTime ReturnValue = DateTime.Now;
GetRealPeriod(ALedgerNumber, ADiffPeriod, AYear, APeriod, out RealPeriod, out RealYear);
DataTable CachedDataTable = CachePopulator.GetCacheableTable(TCacheableFinanceTablesEnum.AccountingPeriodList,
"",
false,
ALedgerNumber,
out typeofTable);
string whereClause = AAccountingPeriodTable.GetLedgerNumberDBName() + " = " + ALedgerNumber.ToString() + " and " +
AAccountingPeriodTable.GetAccountingPeriodNumberDBName() + " = " + RealPeriod.ToString();
DataRow[] filteredRows = CachedDataTable.Select(whereClause);
if (filteredRows.Length > 0)
{
ReturnValue = ((AAccountingPeriodRow)filteredRows[0]).PeriodEndDate;
ALedgerTable Ledger = (ALedgerTable)CachePopulator.GetCacheableTable(TCacheableFinanceTablesEnum.LedgerDetails,
"",
false,
ALedgerNumber,
out typeofTable);
ReturnValue = ReturnValue.AddYears(RealYear - Ledger[0].CurrentFinancialYear);
}
return(ReturnValue);
}
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));
}
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);
}
