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);
}
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
protected override void Execute(CodeActivityContext context)
{
DateTime DueDate = YearEndDate.Get <DateTime>(context);
OptionSetValue valCalculationMethod = CalculationMethod.Get <OptionSetValue>(context);
int valMonthsLaterDue = MonthsLaterDue.Get <int>(context);
int valext1MonthsLaterDue = ext1MonthsLaterDue.Get <int>(context);
int valext2MonthsLaterDue = ext2MonthsLaterDue.Get <int>(context);
int valDayDue = DayDue.Get <int>(context);
int valext1DayDue = ext1DayDue.Get <int>(context);
int valext2DayDue = ext2DayDue.Get <int>(context);
int valDaysLaterDue = DaysLaterDue.Get <int>(context);
int valext1DaysLaterDue = ext1DaysLaterDue.Get <int>(context);
int valext2DaysLaterDue = ext2DaysLaterDue.Get <int>(context);
OptionSetValue valTimesExtended = TimesExtended.Get <OptionSetValue>(context);
int valMonth;
int valYear;
string calcDate = "";
DueDate = DueDate.ToLocalTime();
if (valTimesExtended.Value == 100000000) //if (valTimesExtended.Equals(100000000))
{
//Calculation Method = Months Later - Specific Day of Month for Extension 0
if (valCalculationMethod.Value == 100000001) //if (valCalculationMethod.Equals(100000001))
{
if (valDayDue <= 0)
{
valDayDue = 1;
}
DateTime DueDateAddMonths = DueDate.AddMonths(valMonthsLaterDue);
valMonth = DueDateAddMonths.Month;
valYear = DueDateAddMonths.Year;
calcDate = DueDateAddMonths.Month + "/" + valDayDue + "/" + DueDateAddMonths.Year;
while (DateTime.TryParse(calcDate, out DueDateAddMonths) == false)
{
valDayDue = valDayDue - 1;
calcDate = valMonth + "/" + valDayDue + "/" + valYear;
}
DueDate = DueDateAddMonths;
}
//Calculation Method = Months Later for Extension 0
else if (valCalculationMethod.Value == 100000002)
{
DueDate = DueDate.AddMonths(valMonthsLaterDue);
}
//Calculation Method = Month Later - Last Day of Month Extension 0
else if (valCalculationMethod.Value == 100000003)
{
DateTime DueDateMonthplusone = DueDate.AddMonths(valMonthsLaterDue + 1);
DueDateMonthplusone = new DateTime(DueDateMonthplusone.Year, DueDateMonthplusone.Month, 1);
DateTime DueDateDayminusone = DueDateMonthplusone.AddDays(-1);
DueDate = new DateTime(DueDateDayminusone.Year, DueDateDayminusone.Month, DueDateDayminusone.Day);
}
//Calculation Method = Days Later for Extension 0
else
{
DueDate = DueDate.AddDays(valDaysLaterDue);
}
}
else if (valTimesExtended.Value == 100000001) //if (valTimesExtended.Equals(100000001))
{
//Calculation Method = Months Later - Specific Day of Month Extension 1
if (valCalculationMethod.Value == 100000001) //if (valCalculationMethod.Equals(100000001))
{
if (valext1DayDue <= 0)
{
valext1DayDue = 1;
}
DateTime DueDateAddMonths = DueDate.AddMonths(valext1MonthsLaterDue);
valMonth = DueDateAddMonths.Month;
valYear = DueDateAddMonths.Year;
calcDate = DueDateAddMonths.Month + "/" + valext1DayDue + "/" + DueDateAddMonths.Year;
while (DateTime.TryParse(calcDate, out DueDateAddMonths) == false)
{
valext1DayDue = valext1DayDue - 1;
calcDate = valMonth + "/" + valext1DayDue + "/" + valYear;
}
DueDate = DueDateAddMonths;
}
//Calculation Method = Months Later for Extension 1
else if (valCalculationMethod.Value == 100000002)
{
DueDate = DueDate.AddMonths(valext1MonthsLaterDue);
}
//Calculation Method = Month Later - Last Day of Month Extension 1
else if (valCalculationMethod.Value == 100000003)
{
DateTime DueDateMonthplusone = DueDate.AddMonths(valext1MonthsLaterDue + 1);
DueDateMonthplusone = new DateTime(DueDateMonthplusone.Year, DueDateMonthplusone.Month, 1);
DateTime DueDateDayminusone = DueDateMonthplusone.AddDays(-1);
DueDate = new DateTime(DueDateDayminusone.Year, DueDateDayminusone.Month, DueDateDayminusone.Day);
}
//Calculation Method = Days Later for Extension 1
else
{
DueDate = DueDate.AddDays(valext1DaysLaterDue);
}
}
else if (valTimesExtended.Value == 100000002) //if (valTimesExtended.Equals(100000002))
{
//Calculation Method = Months Later - Specific Day of Month Extension 2
if (valCalculationMethod.Value == 100000001) //if (valCalculationMethod.Equals(100000001))
{
if (valext2DayDue <= 0)
{
valext2DayDue = 1;
}
DateTime DueDateAddMonths = DueDate.AddMonths(valext2MonthsLaterDue);
valMonth = DueDateAddMonths.Month;
valYear = DueDateAddMonths.Year;
calcDate = DueDateAddMonths.Month + "/" + valext2DayDue + "/" + DueDateAddMonths.Year;
while (DateTime.TryParse(calcDate, out DueDateAddMonths) == false)
{
valext2DayDue = valext2DayDue - 1;
calcDate = valMonth + "/" + valext2DayDue + "/" + valYear;
}
DueDate = DueDateAddMonths;
}
//Calculation Method = Months Later for Extension 2
else if (valCalculationMethod.Value == 100000002)
{
DueDate = DueDate.AddMonths(valext2MonthsLaterDue);
}
//Calculation Method = Month Later - Last Day of Month Extension 2
else if (valCalculationMethod.Value == 100000003)
{
DateTime DueDateMonthplusone = DueDate.AddMonths(valext2MonthsLaterDue + 1);
DueDateMonthplusone = new DateTime(DueDateMonthplusone.Year, DueDateMonthplusone.Month, 1);
DateTime DueDateDayminusone = DueDateMonthplusone.AddDays(-1);
DueDate = new DateTime(DueDateDayminusone.Year, DueDateDayminusone.Month, DueDateDayminusone.Day);
}
//Calculation Method = Days Later for Extension 2
else
{
DueDate = DueDate.AddDays(valext2DaysLaterDue);
}
}
result.Set(context, DueDate);
}
