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