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