public static GLReportingTDS GetReportingDataSet(String ADataSetFilterCsv) { GLReportingTDS MainDs = new GLReportingTDS(); TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(); FDbAdapter = new TReportingDbAdapter(); while (!FDbAdapter.IsCancelled && ADataSetFilterCsv != "") { String Tbl = StringHelper.GetNextCSV(ref ADataSetFilterCsv, ",", ""); String[] part = Tbl.Split('/'); // MainDs.Tables[part[0]].Merge(FDbAdapter.RunQuery(part[1], part[0], Transaction)); MainDs.Merge(FDbAdapter.RunQuery(part[1], part[0], Transaction)); } DBAccess.GDBAccessObj.RollbackTransaction(); if (FDbAdapter.IsCancelled) { return(null); } return(MainDs); }
public static GLReportingTDS GetReportingDataSet(String ADataSetFilterCsv) { TDBTransaction Transaction = null; GLReportingTDS MainDs = new GLReportingTDS(); try { FDbAdapter = new TReportingDbAdapter(false); Transaction = FDbAdapter.FPrivateDatabaseObj.BeginTransaction( ATransactionName: "FastReports Report GetReportingDataSet DB Transaction"); while (!FDbAdapter.IsCancelled && ADataSetFilterCsv != "") { String Tbl = StringHelper.GetNextCSV(ref ADataSetFilterCsv, ",", ""); String[] part = Tbl.Split('/'); DataTable NewTbl = FDbAdapter.RunQuery(part[1], part[0], Transaction); MainDs.Merge(NewTbl); } if (FDbAdapter.IsCancelled) { return(null); } } catch (Exception Exc) { MainDs = null; TLogging.Log("TReportingWebConnector.GetReportingDataSet encountered an Exception: " + Exc.ToString()); throw; } finally { FDbAdapter.FPrivateDatabaseObj.RollbackTransaction(); FDbAdapter.CloseConnection(); } return(MainDs); }
public static GLReportingTDS GetReportingDataSet(String ADataSetFilterCsv) { TDBTransaction Transaction = null; GLReportingTDS MainDs = new GLReportingTDS(); try { Transaction = DBAccess.GDBAccessObj.BeginTransaction(); FDbAdapter = new TReportingDbAdapter(); while (!FDbAdapter.IsCancelled && ADataSetFilterCsv != "") { String Tbl = StringHelper.GetNextCSV(ref ADataSetFilterCsv, ",", ""); String[] part = Tbl.Split('/'); DataTable NewTbl = FDbAdapter.RunQuery(part[1], part[0], Transaction); MainDs.Merge(NewTbl); } if (FDbAdapter.IsCancelled) { return(null); } } catch (Exception) { MainDs = null; throw; } finally { DBAccess.GDBAccessObj.RollbackTransaction(); } return(MainDs); }
public static GLReportingTDS GetReportingDataSet(String ADataSetFilterCsv) { TDBTransaction Transaction = null; GLReportingTDS MainDs = new GLReportingTDS(); try { Transaction = DBAccess.GDBAccessObj.BeginTransaction(); FDbAdapter = new TReportingDbAdapter(); while (!FDbAdapter.IsCancelled && ADataSetFilterCsv != "") { String Tbl = StringHelper.GetNextCSV(ref ADataSetFilterCsv, ",", ""); String[] part = Tbl.Split('/'); DataTable NewTbl = FDbAdapter.RunQuery(part[1], part[0], Transaction); MainDs.Merge(NewTbl); } if (FDbAdapter.IsCancelled) { return null; } } catch (Exception) { MainDs = null; throw; } finally { DBAccess.GDBAccessObj.RollbackTransaction(); } return MainDs; }
// // This will be called if the Fast Reports Wrapper loaded OK. // Returns True if the data apparently loaded OK and the report should be printed. private bool LoadReportData(TRptCalculator ACalc) { Shared.MReporting.TParameterList parameters = ACalc.GetParameters(); String LedgerFilter = "a_ledger_number_i=" + parameters.Get("param_ledger_number_i").ToInt32(); String AccountCodeFilter = ""; // Account Filter, as range or list: String GlmAccountCodeFilter = ""; DataTable Balances = new DataTable(); ACalc.AddStringParameter("param_linked_partner_cc", ""); // I may want to use this later, for auto_email, but usually it's unused. if (parameters.Get("param_rgrAccounts").ToString() == "AccountList") { String Filter = "'" + parameters.Get("param_account_codes") + "'"; Filter = Filter.Replace(",", "','"); AccountCodeFilter = "AND a_account_code_c in (" + Filter + ")"; GlmAccountCodeFilter = " AND glm.a_account_code_c in (" + Filter + ")"; } if (parameters.Get("param_rgrAccounts").ToString() == "AccountRange") { AccountCodeFilter = "AND a_account_code_c BETWEEN '" + parameters.Get("param_account_code_start") + "' AND '" + parameters.Get("param_account_code_end") + "'"; GlmAccountCodeFilter = " AND glm.a_account_code_c BETWEEN '" + parameters.Get("param_account_code_start") + "' AND '" + parameters.Get("param_account_code_end") + "'"; } String CostCentreFilter = ""; // Cost Centre Filter, as range or list: String GlmCostCentreFilter = ""; if (parameters.Get("param_rgrCostCentres").ToString() == "CostCentreList") { String Filter = "'" + parameters.Get("param_cost_centre_codes") + "'"; Filter = Filter.Replace(",", "','"); CostCentreFilter = " AND a_cost_centre_code_c in (" + Filter + ")"; GlmCostCentreFilter = " AND glm.a_cost_centre_code_c in (" + Filter + ")"; } if (parameters.Get("param_rgrCostCentres").ToString() == "CostCentreRange") { CostCentreFilter = " AND a_cost_centre_code_c BETWEEN '" + parameters.Get("param_cost_centre_code_start") + "' AND '" + parameters.Get("param_cost_centre_code_end") + "'"; GlmCostCentreFilter = " AND glm.a_cost_centre_code_c BETWEEN '" + parameters.Get("param_cost_centre_code_start") + "' AND '" + parameters.Get("param_cost_centre_code_end") + "'"; } String TranctDateFilter = "a_transaction_date_d BETWEEN '" + parameters.Get("param_start_date").DateToString("yyyy-MM-dd") + "' AND '" + parameters.Get("param_end_date").DateToString("yyyy-MM-dd") + "'"; String ReferenceFilter = ""; String AnalysisTypeFilter = ""; String GroupField = "a_account_code_c, a_cost_centre_code_c"; String Sortby = parameters.Get("param_sortby").ToString(); if (Sortby == "Cost Centre") { GroupField = "a_cost_centre_code_c, a_account_code_c"; } if (Sortby == "Reference") { GroupField = "a_reference_c"; String FilterItem = parameters.Get("param_reference_start").ToString(); if (FilterItem != "") { ReferenceFilter = " AND a_reference_c >='" + FilterItem + "'"; } FilterItem = parameters.Get("param_reference_end").ToString(); if (FilterItem != "") { ReferenceFilter += " AND a_reference_c <='" + FilterItem + "'"; } } if (Sortby == "Analysis Type") { GroupField = "a_analysis_type_code_c"; String FilterItem = parameters.Get("param_analyis_type_start").ToString(); if (FilterItem != "") { AnalysisTypeFilter = " AND a_analysis_type_code_c >='" + FilterItem + "'"; } FilterItem = parameters.Get("param_analyis_type_end").ToString(); if (FilterItem != "") { AnalysisTypeFilter += " AND a_analysis_type_code_c <='" + FilterItem + "'"; } } parameters.Add("param_groupfield", GroupField); String Csv = ""; Csv = StringHelper.AddCSV(Csv, "ALedger/SELECT * FROM a_ledger WHERE " + LedgerFilter); Csv = StringHelper.AddCSV( Csv, "AAccount/SELECT * FROM a_account WHERE " + LedgerFilter + AccountCodeFilter + " AND a_posting_status_l=true AND a_account_active_flag_l=true"); Csv = StringHelper.AddCSV( Csv, "ACostCentre/SELECT * FROM a_cost_centre WHERE " + LedgerFilter + CostCentreFilter + " AND a_posting_cost_centre_flag_l=true AND a_cost_centre_active_flag_l=true"); Boolean InternationalCurrencySelected = parameters.Get("param_currency").ToString().StartsWith("Int"); String AmountField = InternationalCurrencySelected ? "a_amount_in_intl_currency_n" : "a_amount_in_base_currency_n"; if (Sortby == "Analysis Type") // To sort by analysis type, I need a different (and more horible) query: { Csv = StringHelper.AddCSV( Csv, "Transactions/" + "SELECT a_transaction.a_account_code_c AS AccountCode," + "a_transaction.a_cost_centre_code_c AS CostCentreCode," + "a_transaction.a_transaction_date_d AS TransactionDate," + "a_transaction." + AmountField + " AS Amount," + "a_transaction.a_debit_credit_indicator_l AS Debit," + "a_transaction.a_narrative_c AS Narrative," + "a_transaction.a_reference_c AS Reference," + "a_trans_anal_attrib.a_analysis_type_code_c AS AnalysisTypeCode," + "a_analysis_type.a_analysis_type_description_c AS AnalysisTypeDescr," + "a_trans_anal_attrib.a_analysis_attribute_value_c AS AnalysisValue" + " FROM a_transaction, a_trans_anal_attrib, a_analysis_type" + " WHERE a_transaction." + LedgerFilter + " AND a_trans_anal_attrib.a_ledger_number_i = a_transaction.a_ledger_number_i " + " AND a_trans_anal_attrib.a_batch_number_i = a_transaction.a_batch_number_i" + " AND a_trans_anal_attrib.a_journal_number_i = a_transaction.a_journal_number_i" + " AND a_trans_anal_attrib.a_transaction_number_i = a_transaction.a_transaction_number_i" + " AND a_trans_anal_attrib.a_analysis_type_code_c = a_analysis_type.a_analysis_type_code_c" + AnalysisTypeFilter + AccountCodeFilter + CostCentreFilter + " AND " + TranctDateFilter + " AND a_transaction_status_l=true AND NOT (a_system_generated_l=true AND a_narrative_c LIKE 'Year end re-allocation%')" + " ORDER BY " + GroupField + ", a_transaction_date_d"); } else { Csv = StringHelper.AddCSV(Csv, "Transactions/" + "SELECT a_transaction.a_account_code_c AS AccountCode," + "a_transaction.a_cost_centre_code_c AS CostCentreCode," + "a_transaction.a_transaction_date_d AS TransactionDate," + "a_transaction." + AmountField + " AS Amount," + "a_journal.a_transaction_currency_c AS Currency," + "a_transaction.a_debit_credit_indicator_l AS Debit," + "a_transaction.a_narrative_c AS Narrative," + "a_transaction.a_reference_c AS Reference," + "'' AS AnalysisTypeCode," + "'' AS AnalysisTypeDescr," + "'' AS AnalysisValue" + " FROM a_transaction, a_journal WHERE " + " a_transaction." + LedgerFilter + " AND a_transaction.a_ledger_number_i = a_journal.a_ledger_number_i " + " AND a_transaction.a_batch_number_i = a_journal.a_batch_number_i " + " AND a_transaction.a_journal_number_i = a_journal.a_journal_number_i " + AccountCodeFilter + CostCentreFilter + " AND " + TranctDateFilter + ReferenceFilter + " AND a_transaction_status_l=true AND NOT (a_system_generated_l=true AND a_narrative_c LIKE 'Year end re-allocation%')" + " ORDER BY " + GroupField + ", a_transaction_date_d"); } GLReportingTDS ReportDs = TRemote.MReporting.WebConnectors.GetReportingDataSet(Csv); if ((this.IsDisposed) || // If the user has pressed Esc the form is closed! (TRemote.MReporting.WebConnectors.DataTableGenerationWasCancelled())) { return(false); } // // I want to include opening and closing balances for each Cost Centre / Account, in the selected currency. // Following a revision in Oct 2014, this table is the master table, and the transactions are the slave. Int32 Year = parameters.Get("param_year_i").ToInt32(); Balances = TRemote.MFinance.Reporting.WebConnectors.GetPeriodBalances( LedgerFilter, GlmAccountCodeFilter, GlmCostCentreFilter, Year, Sortby, ReportDs.Tables["Transactions"], parameters.Get("param_start_period_i").ToInt32(), parameters.Get("param_end_period_i").ToInt32(), InternationalCurrencySelected ); if ((this.IsDisposed) || (Balances == null)) { return(false); } // My report doesn't need a ledger row - only the name of the ledger. And I need the currency formatter.. { ALedgerRow Row = ReportDs.ALedger[0]; ACalc.AddStringParameter("param_ledger_name", Row.LedgerName); ACalc.AddStringParameter("param_currency_formatter", "0,0.000"); } if (TRemote.MReporting.WebConnectors.DataTableGenerationWasCancelled()) { return(false); } // // If I need to show Analysis Attributes, I need to get the rows that pertain to the Transactions I've selected above. if (ReportDs.ATransaction.Rows.Count > 0) { DataView BatchSorted = new DataView(ReportDs.ATransaction); BatchSorted.Sort = "a_batch_number_i"; ATransactionRow Row = (ATransactionRow)BatchSorted[0].Row; Int32 FirstBatch = Row.BatchNumber; Row = (ATransactionRow)BatchSorted[BatchSorted.Count - 1].Row; Int32 LastBatch = Row.BatchNumber; Csv = ""; Csv = StringHelper.AddCSV( Csv, "ATransAnalAttrib/SELECT * FROM a_trans_anal_attrib WHERE " + LedgerFilter + " AND a_batch_number_i >= " + FirstBatch + " AND a_batch_number_i <= " + LastBatch); Csv = StringHelper.AddCSV(Csv, "AAnalysisType/SELECT * FROM a_analysis_type"); ReportDs.Merge(TRemote.MReporting.WebConnectors.GetReportingDataSet(Csv)); } if (TRemote.MReporting.WebConnectors.DataTableGenerationWasCancelled()) { return(false); } FPetraUtilsObject.FFastReportsPlugin.RegisterData(ReportDs.ATransAnalAttrib, "a_trans_anal_attrib"); FPetraUtilsObject.FFastReportsPlugin.RegisterData(ReportDs.AAnalysisType, "a_analysis_type"); FPetraUtilsObject.FFastReportsPlugin.RegisterData(ReportDs.AAccount, "a_account"); FPetraUtilsObject.FFastReportsPlugin.RegisterData(ReportDs.ACostCentre, "a_costCentre"); FPetraUtilsObject.FFastReportsPlugin.RegisterData(ReportDs.Tables["Transactions"], "Transactions"); FPetraUtilsObject.FFastReportsPlugin.RegisterData(Balances, "balances"); // // For Account Detail reports that must be sent on email, one page at a time, // I'm calling the FastReports plugin multiple times, // and then I'm going to return false, which will prevent the default action using this dataset. if ((parameters.Get("param_sortby").ToString() == "Cost Centre") && (parameters.Get("param_auto_email").ToBool()) && !parameters.Get("param_design_template").ToBool() ) { FPetraUtilsObject.FFastReportsPlugin.AutoEmailReports(ACalc, FLedgerNumber, CostCentreFilter); return(false); } return(true); }
// // New methods using the Fast-reports DLL: private Boolean LoadReportData(TRptCalculator ACalc) { Shared.MReporting.TParameterList pm = ACalc.GetParameters(); pm.Add("param_current_period", uco_GeneralSettings.GetCurrentPeiod()); // 0 = Full Report. Currently the only option for this report. pm.Add("param_run_number", 0); String Csv = ""; // // My "a_transaction" table forms the lower half of the HOSA: // All transactions for all the "Expense" accounts for the selected Cost Centre within the selected dates or periods. String LedgerFilter = "a_ledger_number_i=" + pm.Get("param_ledger_number_i").ToInt32(); String TranctDateFilter = ""; // Optional Date Filter, as periods or dates String CostCentreCodes = pm.Get("param_cost_centre_codes").ToString(); if (CostCentreCodes == String.Empty) { MessageBox.Show(Catalog.GetString("Please select one or more Cost Centres."), "HOSA"); return(false); } CostCentreCodes = CostCentreCodes.Replace('"', '\''); ACalc.AddStringParameter("param_cost_centre_codes", CostCentreCodes); if (pm.Get("param_period").ToBool() == true) { DataTable AccountingPeriodTbl = (AAccountingPeriodTable)TDataCache.TMFinance.GetCacheableFinanceTable(TCacheableFinanceTablesEnum.AccountingPeriodList, pm.Get("param_ledger_number_i").ToInt32()); Int32 PeriodStart = pm.Get("param_start_period_i").ToInt32(); Int32 PeriodEnd = pm.Get("param_end_period_i").ToInt32(); AccountingPeriodTbl.DefaultView.RowFilter = LedgerFilter + " AND a_accounting_period_number_i=" + PeriodStart; DateTime DateStart = Convert.ToDateTime(AccountingPeriodTbl.DefaultView[0].Row["a_period_start_date_d"]); pm.Add("param_start_date", DateStart); AccountingPeriodTbl.DefaultView.RowFilter = LedgerFilter + " AND a_accounting_period_number_i=" + PeriodEnd; DateTime DateEnd = Convert.ToDateTime(AccountingPeriodTbl.DefaultView[0].Row["a_period_end_date_d"]); pm.Add("param_end_date", DateEnd); String PeriodTitle = " (" + DateStart.ToString("dd-MMM-yyyy") + " - " + DateEnd.ToString("dd-MMM-yyyy") + ")"; if (PeriodEnd > PeriodStart) { PeriodTitle = String.Format("{0} - {1}", PeriodStart, PeriodEnd) + PeriodTitle; } else { PeriodTitle = String.Format("{0}", PeriodStart) + PeriodTitle; } pm.Add("param_date_title", PeriodTitle); } else { String PeriodTitle = " " + pm.Get("param_start_date").DateToString("yyyy-MM-dd") + " - " + pm.Get("param_end_date").DateToString("yyyy-MM-dd"); pm.Add("param_date_title", PeriodTitle); } TranctDateFilter = "a_transaction_date_d>='" + pm.Get("param_start_date").DateToString("yyyy-MM-dd") + "' AND a_transaction_date_d<='" + pm.Get("param_end_date").DateToString("yyyy-MM-dd") + "'"; String TranctCostCentreFilter = " AND a_cost_centre_code_c IN (" + CostCentreCodes + ") "; Csv = StringHelper.AddCSV(Csv, "AAccount/SELECT * FROM a_account WHERE " + LedgerFilter + " AND a_posting_status_l=true AND a_account_active_flag_l=true"); Csv = StringHelper.AddCSV(Csv, "ACostCentre/SELECT * FROM a_cost_centre WHERE " + LedgerFilter + " AND a_cost_centre_code_c IN (" + CostCentreCodes + ") AND a_posting_cost_centre_flag_l=true AND a_cost_centre_active_flag_l=true"); Csv = StringHelper.AddCSV( Csv, "ATransaction/SELECT * FROM a_transaction WHERE " + LedgerFilter + TranctCostCentreFilter + " AND " + TranctDateFilter + " AND NOT (a_system_generated_l = true AND (a_narrative_c LIKE 'Gifts received - Gift Batch%' OR a_narrative_c LIKE 'GB - Gift Batch%' OR a_narrative_c LIKE 'Year end re-allocation%'))" + " ORDER BY a_account_code_c, a_transaction_date_d"); GLReportingTDS ReportDs = TRemote.MReporting.WebConnectors.GetReportingDataSet(Csv); ArrayList reportParam = ACalc.GetParameters().Elems; Dictionary <String, TVariant> paramsDictionary = new Dictionary <string, TVariant>(); foreach (Shared.MReporting.TParameter p in reportParam) { if (p.name.StartsWith("param") && (p.name != "param_calculation")) { paramsDictionary.Add(p.name, p.value); } } DataTable ReportTable = TRemote.MReporting.WebConnectors.GetReportDataTable("HOSA", paramsDictionary); if (this.IsDisposed) { return(false); } if (ReportTable == null) { FPetraUtilsObject.WriteToStatusBar("Report Cancelled."); return(false); } FPetraUtilsObject.FFastReportsPlugin.RegisterData(ReportTable, "Gifts"); FPetraUtilsObject.FFastReportsPlugin.RegisterData(ReportDs.AAccount, "a_account"); FPetraUtilsObject.FFastReportsPlugin.RegisterData(ReportDs.ACostCentre, "a_costCentre"); FPetraUtilsObject.FFastReportsPlugin.RegisterData(ReportDs.ATransaction, "a_transaction"); // // My report doesn't need a ledger row - only the name of the ledger. And I need the currency formatter.. String LedgerName = TRemote.MFinance.Reporting.WebConnectors.GetLedgerName(FLedgerNumber); ACalc.AddStringParameter("param_ledger_name", LedgerName); ACalc.AddStringParameter("param_currency_formatter", "0,0.000"); Boolean HasData = (ReportDs.ATransaction.Rows.Count > 0) || (ReportTable.Rows.Count > 0); if (!HasData) { MessageBox.Show(Catalog.GetString("No Transactions found for selected Cost Centres."), "HOSA"); } return(HasData); }
/// <summary> /// Data loader for HOSA data, /// Made static so it can be called from Stewardship Reports /// </summary> public static Boolean LoadReportDataStaticInner(Form ParentForm, TFrmPetraReportingUtils UtilsObject, FastReportsWrapper ReportingEngine, TRptCalculator ACalc) { Shared.MReporting.TParameterList pm = ACalc.GetParameters(); String Csv = ""; // // My "a_transaction" table forms the lower half of the HOSA: // All transactions for all the "Expense" accounts for the selected Cost Centre within the selected dates or periods. String LedgerFilter = "a_ledger_number_i=" + pm.Get("param_ledger_number_i").ToInt32(); String TranctDateFilter = ""; // Optional Date Filter, as periods or dates String CostCentreCodes = pm.Get("param_cost_centre_codes").ToString(); if (CostCentreCodes == String.Empty) { MessageBox.Show(Catalog.GetString("Please select one or more Cost Centres."), "HOSA"); return(false); } String CostCentreFilter = ""; if (CostCentreCodes == "ALL") { CostCentreFilter = " AND a_cost_centre.a_cost_centre_type_c='Foreign' "; } else { CostCentreCodes = CostCentreCodes.Replace('"', '\''); ACalc.AddStringParameter("param_cost_centre_codes", CostCentreCodes); CostCentreFilter = " AND a_cost_centre.a_cost_centre_code_c IN (" + CostCentreCodes + ") "; } if (pm.Get("param_period").ToBool() == true) { Int32 PeriodStart = pm.Get("param_start_period_i").ToInt32(); Int32 PeriodEnd = pm.Get("param_end_period_i").ToInt32(); DateTime DateStart = pm.Get("param_start_date").ToDate(); DateTime DateEnd = pm.Get("param_end_date").ToDate(); ALedgerTable LedgerDetailsTable = (ALedgerTable)TDataCache.TMFinance.GetCacheableFinanceTable( TCacheableFinanceTablesEnum.LedgerDetails); ALedgerRow LedgerRow = LedgerDetailsTable[0]; Boolean IsClosed = (!pm.Get("param_current_financial_year").ToBool() || (PeriodEnd < LedgerRow.CurrentPeriod)); ACalc.AddParameter("param_period_closed", IsClosed); Boolean IsCurrent = (pm.Get("param_current_financial_year").ToBool() && (PeriodEnd == LedgerRow.CurrentPeriod)); ACalc.AddParameter("param_period_current", IsCurrent); String PeriodTitle = " (" + DateStart.ToString("dd-MMM-yyyy") + " - " + DateEnd.ToString("dd-MMM-yyyy") + ")"; if (PeriodEnd > PeriodStart) { PeriodTitle = String.Format("{0} - {1}", PeriodStart, PeriodEnd) + PeriodTitle; } else { PeriodTitle = String.Format("{0}", PeriodStart) + PeriodTitle; } pm.Add("param_date_title", PeriodTitle); } else { String PeriodTitle = " " + pm.Get("param_start_date").DateToString("yyyy-MM-dd") + " - " + pm.Get("param_end_date").DateToString("yyyy-MM-dd"); pm.Add("param_date_title", PeriodTitle); } TranctDateFilter = "a_transaction_date_d>='" + pm.Get("param_start_date").DateToString("yyyy-MM-dd") + "' AND a_transaction_date_d<='" + pm.Get("param_end_date").DateToString("yyyy-MM-dd") + "'"; Csv = StringHelper.AddCSV(Csv, "AAccount/SELECT * FROM a_account WHERE " + LedgerFilter + " AND a_posting_status_l=true AND a_account_active_flag_l=true"); Csv = StringHelper.AddCSV(Csv, "ACostCentre/SELECT * FROM a_cost_centre WHERE " + LedgerFilter + CostCentreFilter + " AND a_posting_cost_centre_flag_l=true AND a_cost_centre_active_flag_l=true"); Csv = StringHelper.AddCSV( Csv, "ATransaction/SELECT a_transaction.* FROM a_transaction, a_cost_centre WHERE a_transaction." + LedgerFilter + " AND " + TranctDateFilter + " AND NOT (a_system_generated_l = true AND (a_narrative_c LIKE 'Gifts received - Gift Batch%' OR a_narrative_c LIKE 'GB - Gift Batch%' OR a_narrative_c LIKE 'Year end re-allocation%'))" + " AND a_transaction.a_ledger_number_i = a_cost_centre.a_ledger_number_i " + " AND a_transaction.a_cost_centre_code_c = a_cost_centre.a_cost_centre_code_c " + CostCentreFilter + " ORDER BY a_account_code_c, a_transaction_date_d"); GLReportingTDS ReportDs = TRemote.MReporting.WebConnectors.GetReportingDataSet(Csv); ArrayList reportParam = ACalc.GetParameters().Elems; Dictionary <String, TVariant> paramsDictionary = new Dictionary <string, TVariant>(); foreach (Shared.MReporting.TParameter p in reportParam) { if (p.name.StartsWith("param") && (p.name != "param_calculation")) { paramsDictionary.Add(p.name, p.value); } } DataTable GiftsTable = TRemote.MReporting.WebConnectors.GetReportDataTable("HOSA", paramsDictionary); DataTable KeyMinGiftsTable = TRemote.MReporting.WebConnectors.GetReportDataTable("FieldGifts", paramsDictionary); // // I'm going to get rid of any Cost Centres that saw no activity in the requested period: for (Int32 Idx = ReportDs.ACostCentre.Rows.Count - 1; Idx >= 0; Idx--) { ACostCentreRow Row = ReportDs.ACostCentre[Idx]; ReportDs.ATransaction.DefaultView.RowFilter = String.Format("a_cost_centre_code_c='{0}'", Row.CostCentreCode); GiftsTable.DefaultView.RowFilter = String.Format("CostCentre='{0}'", Row.CostCentreCode); if ((ReportDs.ATransaction.DefaultView.Count == 0) && (GiftsTable.DefaultView.Count == 0)) { ReportDs.ACostCentre.Rows.Remove(Row); } } if (ParentForm.IsDisposed) { return(false); } if (GiftsTable == null) { UtilsObject.WriteToStatusBar("Report Cancelled."); return(false); } ReportingEngine.RegisterData(GiftsTable, "Gifts"); ReportingEngine.RegisterData(KeyMinGiftsTable, "FieldGifts"); ReportingEngine.RegisterData(ReportDs.AAccount, "a_account"); ReportingEngine.RegisterData(ReportDs.ACostCentre, "a_costCentre"); ReportingEngine.RegisterData(ReportDs.ATransaction, "a_transaction"); Boolean HasData = (ReportDs.ATransaction.Rows.Count > 0) || (GiftsTable.Rows.Count > 0); if (!HasData) { MessageBox.Show(Catalog.GetString("No Transactions found for selected Cost Centres."), "HOSA"); } return(HasData); }
public static GLReportingTDS GetReportingDataSet(String ADataSetFilterCsv) { GLReportingTDS MainDs = new GLReportingTDS(); TDBTransaction Transaction = DBAccess.GDBAccessObj.BeginTransaction(); FDbAdapter = new TReportingDbAdapter(); while (!FDbAdapter.IsCancelled && ADataSetFilterCsv != "") { String Tbl = StringHelper.GetNextCSV(ref ADataSetFilterCsv, ",", ""); String[] part = Tbl.Split('/'); // MainDs.Tables[part[0]].Merge(FDbAdapter.RunQuery(part[1], part[0], Transaction)); MainDs.Merge(FDbAdapter.RunQuery(part[1], part[0], Transaction)); } DBAccess.GDBAccessObj.RollbackTransaction(); if (FDbAdapter.IsCancelled) { return null; } return MainDs; }