private DataTable GetCostCentreListTable(TDBTransaction AReadTransaction, System.Int32 ALedgerNumber, string ATableName)
{
StringCollection FieldList = new StringCollection();
FieldList.Add(ACostCentreTable.GetLedgerNumberDBName());
FieldList.Add(ACostCentreTable.GetCostCentreCodeDBName());
FieldList.Add(ACostCentreTable.GetCostCentreNameDBName());
FieldList.Add(ACostCentreTable.GetCostCentreToReportToDBName());
FieldList.Add(ACostCentreTable.GetPostingCostCentreFlagDBName());
FieldList.Add(ACostCentreTable.GetCostCentreActiveFlagDBName());
FieldList.Add(ACostCentreTable.GetCostCentreTypeDBName());
return(ACostCentreAccess.LoadViaALedger(ALedgerNumber, FieldList, AReadTransaction));
}
public static void GenerateHOSAReports(int ALedgerNumber,
int APeriodNumber,
int AIchNumber,
string ACurrencySelect,
out TVerificationResultCollection AVerificationResult
)
{
AVerificationResult = new TVerificationResultCollection();
//Begin the transaction
bool NewTransaction = false;
TDBTransaction DBTransaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTransaction);
ALedgerTable ALedger = ALedgerAccess.LoadByPrimaryKey(ALedgerNumber, DBTransaction);
AJournalTable AJournal = new AJournalTable();
ATransactionTable ATransaction = new ATransactionTable();
ACostCentreTable ACostCentre = new ACostCentreTable();
try
{
#if TODO
ALedgerRow LedgerRow = PostingDS.ALedger[0];
//Find the Ledger Name = Partner Short Name
PPartnerTable PartnerTable = PPartnerAccess.LoadByPrimaryKey(LedgerRow.PartnerKey, DBTransaction);
PPartnerRow PartnerRow = (PPartnerRow)PartnerTable.Rows[0];
string LedgerName = PartnerRow.PartnerShortName;
#endif
//
// Load the Journals, and Transactions for this period:
String JournalQuery = "SELECT PUB_a_journal.* FROM PUB_a_batch, PUB_a_journal WHERE " +
"PUB_a_batch.a_ledger_number_i = " + ALedgerNumber +
" AND PUB_a_batch.a_batch_year_i = " + ALedger[0].CurrentFinancialYear +
" AND PUB_a_batch.a_batch_period_i = " + APeriodNumber +
" AND PUB_a_batch.a_batch_status_c = 'Posted'" +
" AND PUB_a_batch.a_ledger_number_i = PUB_a_journal.a_ledger_number_i" +
" AND PUB_a_batch.a_batch_number_i = PUB_a_journal.a_batch_number_i";
DBAccess.GDBAccessObj.SelectDT(AJournal, JournalQuery, DBTransaction);
String TransactionQuery = "SELECT PUB_a_transaction.* FROM PUB_a_batch, PUB_a_transaction WHERE " +
"PUB_a_batch.a_ledger_number_i = " + ALedgerNumber +
" AND PUB_a_batch.a_batch_year_i = " + ALedger[0].CurrentFinancialYear +
" AND PUB_a_batch.a_batch_period_i = " + APeriodNumber +
" AND PUB_a_batch.a_batch_status_c = 'Posted'" +
" AND PUB_a_batch.a_ledger_number_i = PUB_a_transaction.a_ledger_number_i" +
" AND PUB_a_batch.a_batch_number_i = PUB_a_transaction.a_batch_number_i";
DBAccess.GDBAccessObj.SelectDT(ATransaction, TransactionQuery, DBTransaction);
String CostCentreQuery = "SELECT * FROM a_cost_centre WHERE " +
ACostCentreTable.GetLedgerNumberDBName() + " = " + ALedgerNumber +
" AND " + ACostCentreTable.GetPostingCostCentreFlagDBName() + " = True" +
" AND " + ACostCentreTable.GetCostCentreTypeDBName() + " LIKE '" + MFinanceConstants.FOREIGN_CC_TYPE + "'" +
" ORDER BY " + ACostCentreTable.GetCostCentreCodeDBName();
DBAccess.GDBAccessObj.SelectDT(ACostCentre, CostCentreQuery, DBTransaction);
//Iterate through the cost centres
foreach (ACostCentreRow CostCentreRow in ACostCentre.Rows)
{
bool TransactionExists = false;
//Iterate through the journals
foreach (AJournalRow JournalRow in AJournal.Rows)
{
int BatchNumber = JournalRow.BatchNumber;
int JournalNumber = JournalRow.JournalNumber;
#if TODO
String TransFilter =
ATransactionTable.GetBatchNumberDBName() + " = " + BatchNumber.ToString() +
" AND " + ATransactionTable.GetJournalNumberDBName() + " = " + JournalNumber.ToString() +
" AND " + ATransactionTable.GetCostCentreCodeDBName() + " = '" + CostCentreRow.CostCentreCode + "'" +
" AND (" + ATransactionTable.GetIchNumberDBName() + " = 0" +
" OR " + ATransactionTable.GetIchNumberDBName() + " = " + AIchNumber.ToString() +
")";
DataRow[] FoundTransRows = BatchDS.ATransaction.Select(TransFilter);
foreach (DataRow untypedTransRow in FoundTransRows)
{
ATransactionRow TransactionRow = (ATransactionRow)untypedTransRow;
TransactionExists = true;
string DefaultData = ALedgerNumber.ToString() + "," +
LedgerName + "," +
APeriodNumber.ToString() + "," +
APeriodNumber.ToString() + "," +
CostCentreRow.CostCentreCode + "," +
"" + "," +
"" + "," +
"" + "," +
"A" + "," +
LedgerRow.CurrentFinancialYear.ToString() + "," +
LedgerRow.CurrentPeriod.ToString() + "," +
MFinanceConstants.MAX_PERIODS.ToString() + "," +
"h" + "," +
ACurrency + "," +
AIchNumber.ToString();
string ReportTitle = "Home Office Stmt of Acct: " + CostCentreRow.CostCentreName;
//call code for gl2120p.p Produces Account Detail, Analysis Attribute and HOSA Reprint reports.
/* RUN sm9000.w ("gl2120p.p",
* lv_report_title_c,
* lv_default_data_c).*/
//TODO: call code to produce reports
break;
}
#endif
if (TransactionExists)
{
//only need to run above code once for 1 transaction per cost centre code
break; //goto next cost centre else try next journal
}
}
}
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
}
catch (Exception Exp)
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
TLogging.Log(Exp.Message);
TLogging.Log(Exp.StackTrace);
throw;
}
}