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