private Boolean LoadReportData(TRptCalculator ACalc)
{
Shared.MReporting.TParameterList pm = ACalc.GetParameters();
pm.Add("param_start_period_i", 1);
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("Executive Summary", paramsDictionary);
if (ReportTable == null)
{
FPetraUtilsObject.WriteToStatusBar("Report Cancelled.");
return(false);
}
FPetraUtilsObject.FFastReportsPlugin.RegisterData(ReportTable, "Accounts");
//
// My report doesn't need a ledger row - only the name of the ledger.
DataTable LedgerNameTable = TDataCache.TMFinance.GetCacheableFinanceTable(TCacheableFinanceTablesEnum.LedgerNameList);
DataView LedgerView = new DataView(LedgerNameTable);
LedgerView.RowFilter = "LedgerNumber=" + FLedgerNumber;
String LedgerName = "";
if (LedgerView.Count > 0)
{
LedgerName = LedgerView[0].Row["LedgerName"].ToString();
}
ACalc.AddStringParameter("param_ledger_name", LedgerName);
ALedgerRow Ledger =
((ALedgerTable)TDataCache.TMFinance.GetCacheableFinanceTable(TCacheableFinanceTablesEnum.LedgerDetails, FLedgerNumber))[0];
ACalc.AddStringParameter("param_currency_name", Ledger.BaseCurrency);
Boolean HasData = ReportTable.Rows.Count > 0;
if (!HasData)
{
MessageBox.Show(Catalog.GetString("No Executive Summary data found for current Ledger."), "Executive Summary");
}
return(HasData);
}
private Dictionary <String, TVariant> InitialiseDictionary(TRptCalculator ACalc)
{
Shared.MReporting.TParameterList pm = ACalc.GetParameters();
pm.Add("param_ledger_number_i", FLedgerNumber);
String LedgerName = TRemote.MFinance.Reporting.WebConnectors.GetLedgerName(FLedgerNumber);
ACalc.AddStringParameter("param_ledger_name", LedgerName);
ACalc.AddParameter("param_currency_name", FLedgerRow.BaseCurrency); // Stewardship reports are always in Base Currency.
ACalc.AddStringParameter("param_currency_formatter", "0,0.000");
ACalc.AddParameter("param_ich_number", pm.Get("param_cmbICHNumber").ToInt32());
ACalc.AddParameter("param_period", true);
Int32 period = pm.Get("param_cmbReportPeriod").ToInt32();
Int32 PeriodStart = Math.Max(1, period);
Int32 PeriodEnd = period;
if (PeriodEnd == 0)
{
PeriodEnd = TFinanceControls.GetLedgerNumPeriods(FLedgerNumber);
}
Int32 Year = pm.Get("param_cmbYearEnding").ToInt32();
ACalc.AddParameter("param_start_period_i", PeriodStart);
ACalc.AddParameter("param_end_period_i", PeriodEnd);
DateTime StartDate = TRemote.MFinance.GL.WebConnectors.GetPeriodStartDate(FLedgerNumber, Year, 0, PeriodStart);
DateTime EndDate = TRemote.MFinance.GL.WebConnectors.GetPeriodEndDate(FLedgerNumber, Year, 0, PeriodEnd);
ACalc.AddParameter("param_real_year", StartDate.Year);
ACalc.AddParameter("param_start_date", StartDate);
ACalc.AddParameter("param_end_date", EndDate);
ACalc.AddParameter("param_current_financial_year", FLedgerRow.CurrentFinancialYear == Year);
Boolean IsClosed = (Year < FLedgerRow.CurrentFinancialYear) || (PeriodEnd < FLedgerRow.CurrentPeriod);
ACalc.AddParameter("param_period_closed", IsClosed);
Boolean IsCurrent = (Year == FLedgerRow.CurrentFinancialYear) && (PeriodEnd == FLedgerRow.CurrentPeriod);
ACalc.AddParameter("param_period_current", IsCurrent);
ACalc.AddParameter("param_year_i", Year);
ArrayList reportParam = pm.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);
}
}
return(paramsDictionary);
} // Initialise Dictionary
private Boolean LoadStewardshipReportData(TRptCalculator ACalc)
{
Shared.MReporting.TParameterList pm = ACalc.GetParameters();
pm.Add("param_ledger_number_i", FLedgerNumber);
ArrayList reportParam = pm.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);
}
}
// pm.Add("param_current_period", uco_GeneralSettings.GetCurrentPeiod());
DataTable ReportTable = TRemote.MReporting.WebConnectors.GetReportDataTable("Stewardship", paramsDictionary);
if (this.IsDisposed)
{
return(false);
}
if (ReportTable == null)
{
FPetraUtilsObject.WriteToStatusBar("Report Cancelled.");
return(false);
}
MyFastReportsPlugin.RegisterData(ReportTable, "Stewardship");
//
// 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 = (ReportTable.Rows.Count > 0);
if (!HasData)
{
MessageBox.Show(Catalog.GetString("No Stewardship entries found for selected Run Number."), "Stewardship");
}
return(HasData);
}
//
// New methods using the Fast-reports DLL:
private Boolean LoadReportData(TRptCalculator ACalc)
{
Shared.MReporting.TParameterList pm = ACalc.GetParameters();
//
// 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");
ACalc.AddParameter("param_period", true);
ACalc.AddStringParameter("param_linked_partner_cc", ""); // Used for auto-emailing HOSAs, this is usually blank.
// 0 = Full Report. Currently the only option for this report.
pm.Add("param_ich_number", 0);
return(LoadReportDataStaticInner(this, FPetraUtilsObject, FPetraUtilsObject.FFastReportsPlugin, ACalc));
}
//
// New methods using the Fast-reports DLL:
private Boolean LoadReportData(TRptCalculator ACalc)
{
Shared.MReporting.TParameterList pm = ACalc.GetParameters();
pm.RemoveVariable("param_start_period_i");
pm.Add("param_start_period_i", 1);
pm.Add("param_current_period", uco_GeneralSettings.GetCurrentPeiod());
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("AFO", paramsDictionary);
if (ReportTable == null)
{
FPetraUtilsObject.WriteToStatusBar("Report Cancelled.");
return(false);
}
FPetraUtilsObject.FFastReportsPlugin.RegisterData(ReportTable, "Accounts");
//
// 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");
ACalc.AddStringParameter("param_base_currency", uco_GeneralSettings.GetBaseCurrency());
ACalc.AddStringParameter("param_intl_currency", uco_GeneralSettings.GetInternationalCurrency());
Boolean HasData = ReportTable.Rows.Count > 0;
if (!HasData)
{
MessageBox.Show(Catalog.GetString("No Summary Accounts found for current Ledger."), "AFO");
}
return(HasData);
}
//
// 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 pm = ACalc.GetParameters();
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.ContainsKey(p.name)
)
{
paramsDictionary.Add(p.name, p.value);
}
}
DataTable ReportTable = TRemote.MReporting.WebConnectors.GetReportDataTable("DonorGiftsToField", paramsDictionary);
if (ReportTable == null)
{
return(false);
}
FPetraUtilsObject.FFastReportsPlugin.RegisterData(ReportTable, "DonorGiftsToField");
//
// 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);
ALedgerTable LedgerDetailsTable = (ALedgerTable)TDataCache.TMFinance.GetCacheableFinanceTable(TCacheableFinanceTablesEnum.LedgerDetails);
ALedgerRow Row = LedgerDetailsTable[0];
ACalc.AddStringParameter("param_ledger_name", LedgerName);
String CurrencyName = (cmbCurrency.SelectedItem.ToString() == "Base") ? Row.BaseCurrency : Row.IntlCurrency;
ACalc.AddStringParameter("param_currency_name", CurrencyName);
ACalc.AddStringParameter("param_currency_formatter", "0,0.000");
return(true);
}
//
// 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);
}
//
// 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)
{
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.ContainsKey(p.name)))
{
paramsDictionary.Add(p.name, p.value);
}
}
Int32 ParamNestingDepth = 99;
String DepthOption = paramsDictionary["param_depth"].ToString();
if (DepthOption == "Summary")
{
ParamNestingDepth = 2;
}
if (DepthOption == "Standard")
{
ParamNestingDepth = 3;
}
paramsDictionary.Add("param_nesting_depth", new TVariant(ParamNestingDepth));
//
// The table contains Actual and Budget figures, both this period and YTD, also last year and budget last year.
// It does not contain any variance (actual / budget) figures - these are calculated in the report.
DataTable ReportTable = TRemote.MReporting.WebConnectors.GetReportDataTable("IncomeExpense", paramsDictionary);
if (this.IsDisposed) // There's no cancel function as such - if the user has pressed Esc the form is closed!
{
return(false);
}
if (ReportTable == null)
{
FPetraUtilsObject.WriteToStatusBar("Report Cancelled.");
return(false);
}
FPetraUtilsObject.FFastReportsPlugin.RegisterData(ReportTable, "IncomeExpense");
//
// I need to get the name of the current ledger..
DataTable LedgerNameTable = TDataCache.TMFinance.GetCacheableFinanceTable(TCacheableFinanceTablesEnum.LedgerNameList);
DataView LedgerView = new DataView(LedgerNameTable);
LedgerView.RowFilter = "LedgerNumber=" + FLedgerNumber;
String LedgerName = "";
if (LedgerView.Count > 0)
{
LedgerName = LedgerView[0].Row["LedgerName"].ToString();
}
ACalc.AddStringParameter("param_ledger_name", LedgerName);
ACalc.AddStringParameter("param_linked_partner_cc", ""); // I may want to use this for auto_email, but usually it's unused.
//
// For reports that must be sent on email, one page at a time,
// I'm using the AutoEmailReports method which calls the FastReports plugin multiple times,
// and then I'm going to return false, which will prevent the default action using this dataset.
Shared.MReporting.TParameterList pm = ACalc.GetParameters();
if ((pm.Get("param_auto_email").ToBool()) &&
!pm.Get("param_design_template").ToBool()
)
{
String CostCentreFilter = "";
String CostCentreOptions = pm.Get("param_costcentreoptions").ToString();
if (CostCentreOptions == "SelectedCostCentres")
{
String CostCentreList = pm.Get("param_cost_centre_codes").ToString();
CostCentreList = CostCentreList.Replace(",", "','"); // SQL IN List items in single quotes
CostCentreFilter = " AND a_cost_centre_code_c in ('" + CostCentreList + "')";
}
if (CostCentreOptions == "CostCentreRange")
{
CostCentreFilter = " AND a_cost_centre_code_c >='" + pm.Get("param_cost_centre_code_start").ToString() +
"' AND a_cost_centre_code_c >='" + pm.Get("param_cost_centre_code_end").ToString() + "'";
}
List <String> Status = FastReportsWrapper.AutoEmailReports(FPetraUtilsObject,
FPetraUtilsObject.FFastReportsPlugin,
ACalc,
FLedgerNumber,
CostCentreFilter);
MessageBox.Show(String.Join("\n", Status), Catalog.GetString("Auto Email") + " " + Catalog.GetString("Income Expense Report"));
return(false);
}
return(true);
}
/// <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);
}
