/* * private void GenerateReports(Object Sender, EventArgs e) * { * String CurrencySelect = (this.rbtBase.Checked ? MFinanceConstants.CURRENCY_BASE : MFinanceConstants.CURRENCY_INTERNATIONAL); * bool DoGenerateHOSAReports = chkHOSAReport.Checked; * bool DoEmailHOSAReports = chkEmailHOSAReport.Checked; * bool DoGenerateHOSAFiles = chkHOSAFile.Checked; * bool DoEmailHOSAFiles = chkEmailHOSAFile.Checked; * * TVerificationResultCollection VerificationResults; * * string msg = string.Empty; * string SuccessfullCostCentres = string.Empty; * string FailedCostCentres = string.Empty; * * int SelectedReportPeriod = cmbReportPeriod.GetSelectedInt32(); * int SelectedICHNumber = cmbICHNumber.GetSelectedInt32(); * * if (!ValidReportPeriod()) * { * return; * } * * String HOSAFilePrefix = txtHOSAPrefix.Text; * * if (HOSAFilePrefix.Length == 0) * { * HOSAFilePrefix = Catalog.GetString("HOSAFilesExportFor"); * } * else * { * Int32 IndexOfInvalidFilenameCharacter = HOSAFilePrefix.IndexOfAny(Path.GetInvalidFileNameChars()); * * if (IndexOfInvalidFilenameCharacter >= 0) * { * msg = String.Format("The HOSA File Prefix: '{0}', contains characters not valid in a filename: {1}{2}{2}Please remove and retry.", * HOSAFilePrefix, * String.Join(", ", Path.GetInvalidFileNameChars()), * Environment.NewLine); * * MessageBox.Show(msg, "Generate HOSA Reports and Files", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); * * txtHOSAPrefix.Focus(); * txtHOSAPrefix.Select(IndexOfInvalidFilenameCharacter, 1); * return; * } * } * * try * { * Cursor = Cursors.WaitCursor; * * DataTable ICHNumbers = TDataCache.TMFinance.GetCacheableFinanceTable(TCacheableFinanceTablesEnum.ICHStewardshipList, FLedgerNumber); * * //Filter for current period * if (SelectedICHNumber != 0) * { * ICHNumbers.DefaultView.RowFilter = String.Format("{0}={1} And {2}={3}", * AIchStewardshipTable.GetPeriodNumberDBName(), * SelectedReportPeriod, * AIchStewardshipTable.GetIchNumberDBName(), * SelectedICHNumber); * } * else * { * ICHNumbers.DefaultView.RowFilter = String.Format("{0}={1}", * AIchStewardshipTable.GetPeriodNumberDBName(), * SelectedReportPeriod); * } * * ICHNumbers.DefaultView.Sort = AIchStewardshipTable.GetCostCentreCodeDBName(); * * foreach (DataRowView tmpRow in ICHNumbers.DefaultView) * { * AIchStewardshipRow ichRow = (AIchStewardshipRow)tmpRow.Row; * bool HOSASuccess = false; * * String CostCentreCode = ichRow.CostCentreCode; * * if (DoGenerateHOSAReports) * { * //TODO code to generate the HOSA reports * TRemote.MFinance.ICH.WebConnectors.GenerateHOSAReports(FLedgerNumber, * cmbReportPeriod.GetSelectedInt32(), * cmbICHNumber.GetSelectedInt32(), * CurrencySelect, * out VerificationResults); * HOSASuccess = !VerificationResults.HasCriticalErrors; * } * else if (DoGenerateHOSAFiles) * { * String FileName = TClientSettings.PathTemp + Path.DirectorySeparatorChar + HOSAFilePrefix + CostCentreCode + ".csv"; * HOSASuccess = TRemote.MFinance.ICH.WebConnectors.GenerateHOSAFiles(FLedgerNumber, cmbReportPeriod.GetSelectedInt32(), * cmbICHNumber.GetSelectedInt32(), CostCentreCode, CurrencySelect, FileName, out VerificationResults); * } * * if (HOSASuccess) * { * if (SuccessfullCostCentres.Length == 0) * { * SuccessfullCostCentres = CostCentreCode; * } * else * { * SuccessfullCostCentres += ", " + CostCentreCode; * } * } * else * { * if (FailedCostCentres.Length == 0) * { * FailedCostCentres = CostCentreCode; * } * else * { * FailedCostCentres += ", " + CostCentreCode; * } * } * } * * Cursor = Cursors.Default; * * if (SuccessfullCostCentres.Length > 0) * { * msg = String.Format(Catalog.GetString("HOSA file generated successfully for Cost Centre(s):{0}{0}{1}{0}{0}"), * Environment.NewLine, * SuccessfullCostCentres); * } * * if (FailedCostCentres.Length > 0) * { * msg += String.Format(Catalog.GetString("HOSA generation FAILED for Cost Centre(s):{0}{0}{1}"), * Environment.NewLine, * FailedCostCentres); * } * * if (msg.Length == 0) * { * msg = Catalog.GetString("Stewardship Calculations haven't been run or no transactions to process."); * } * * MessageBox.Show(msg, Catalog.GetString("Generate Reports")); * } * finally * { * Cursor = Cursors.Default; * } * } * private bool ValidReportPeriod() * { * if (cmbReportPeriod.SelectedIndex > -1) * { * return true; * } * else if (cmbReportPeriod.Count > 0) * { * MessageBox.Show(Catalog.GetString("Please select a valid reporting period first.")); * cmbReportPeriod.Focus(); * } * * return false; * } */ private void ViewReportTemplate(TRptCalculator ACalc) { String ReportName = ""; if (chkRecipient.Checked) { ReportName = "Recipient Gift Statement"; } if (chkFees.Checked) { ReportName = "Fees"; } if (chkStewardship.Checked) { ReportName = "Stewardship"; } if (chkHOSA.Checked) { ReportName = "HOSA"; } if (ReportName == "") { return; } MyFastReportsPlugin = new FastReportsWrapper(ReportName); MyFastReportsPlugin.DesignReport(ACalc); }
// // New methods using the Fast-reports DLL: // This form generates a clutch of different reports. // In this method, a new FastReportsWrapper is used for each selected report type. private void GenerateAllSelectedReports(TRptCalculator ACalc) { // "Stewardship"; MyFastReportsPlugin = new FastReportsWrapper("Stewardship"); MyFastReportsPlugin.SetDataGetter(LoadStewardshipReportData); MyFastReportsPlugin.GenerateReport(ACalc); }
/// <summary> /// Print or reprint the posting report for this batch. /// </summary> public static void PrintPostingRegister(Int32 ALedgerNumber, Int32 ABatchNumber, Boolean AEditTemplate = false) { FastReportsWrapper ReportingEngine = new FastReportsWrapper("Batch Posting Register"); if (!ReportingEngine.LoadedOK) { ReportingEngine.ShowErrorPopup(); return; } GLBatchTDS BatchTDS = TRemote.MFinance.GL.WebConnectors.LoadABatchAndContent(ALedgerNumber, ABatchNumber); TRptCalculator Calc = new TRptCalculator(); ALedgerRow LedgerRow = BatchTDS.ALedger[0]; //Call RegisterData to give the data to the template ReportingEngine.RegisterData(BatchTDS.ABatch, "ABatch"); ReportingEngine.RegisterData(BatchTDS.AJournal, "AJournal"); ReportingEngine.RegisterData(BatchTDS.ATransaction, "ATransaction"); Calc.AddParameter("param_batch_number_i", ABatchNumber); Calc.AddParameter("param_ledger_number_i", ALedgerNumber); String LedgerName = TRemote.MFinance.Reporting.WebConnectors.GetLedgerName(ALedgerNumber); Calc.AddStringParameter("param_ledger_name", LedgerName); if (AEditTemplate) { ReportingEngine.DesignReport(Calc); } else { ReportingEngine.GenerateReport(Calc); } }
/// <summary> /// Print out the Motivation Details using FastReports template. /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void FilePrint(object sender, EventArgs e) { FastReportsWrapper ReportingEngine = new FastReportsWrapper("Motivation Details"); if (!ReportingEngine.LoadedOK) { ReportingEngine.ShowErrorPopup(); return; } ReportingEngine.RegisterData(FMainDS.AMotivationDetail, "MotivationDetail"); TRptCalculator Calc = new TRptCalculator(); ALedgerRow LedgerRow = FMainDS.ALedger[0]; Calc.AddParameter("param_ledger_number_i", LedgerRow.LedgerNumber); Calc.AddStringParameter("param_ledger_name", LedgerRow.LedgerName); if (ModifierKeys.HasFlag(Keys.Control)) { ReportingEngine.DesignReport(Calc); } else { ReportingEngine.GenerateReport(Calc); } }
// // New methods using the Fast-reports DLL: // This form generates a clutch of different reports. // In this method, a new FastReportsWrapper is used for each selected report type. private void GenerateAllSelectedReports(TRptCalculator ACalc) { FStatusMsg = ""; if (chkHOSA.Enabled && chkHOSA.Checked) { FStatusMsg += Catalog.GetString("\r\nAll HOSAs:"); MyFastReportsPlugin = new FastReportsWrapper("HOSA"); MyFastReportsPlugin.SetDataGetter(LoadHosaReportData); MyFastReportsPlugin.GenerateReport(ACalc); } if (chkStewardship.Enabled && chkStewardship.Checked) { FStatusMsg += Catalog.GetString("\r\nStewardship Report:"); MyFastReportsPlugin = new FastReportsWrapper("Stewardship"); MyFastReportsPlugin.SetDataGetter(LoadStewardshipReportData); MyFastReportsPlugin.GenerateReport(ACalc); } if (chkFees.Enabled && chkFees.Checked) { FStatusMsg += Catalog.GetString("\r\nFees Report:"); MyFastReportsPlugin = new FastReportsWrapper("Fees"); MyFastReportsPlugin.SetDataGetter(LoadFeesReportData); MyFastReportsPlugin.GenerateReport(ACalc); } // complex way of stepping around the Windows non-thread-safe controls problem! FStatusMsg += Catalog.GetString("\r\n\r\nReport generation complete."); this.Invoke(new CrossThreadUpdate(ShowReportStatus)); }
} // Initialise Dictionary private Boolean LoadHosaReportData(TRptCalculator ACalc) { InitialiseDictionary(ACalc); ACalc.AddStringParameter("param_cost_centre_codes", "ALL"); ACalc.AddStringParameter("param_filter_cost_centres", ""); ACalc.AddStringParameter("param_linked_partner_cc", ""); // Used for auto-emailing HOSAs, this is usually blank. ACalc.AddParameter("param_include_rgs", !chkRecipient.Enabled || chkRecipient.Checked); Boolean DataOk = TFrmHOSA.LoadReportDataStaticInner(this, FPetraUtilsObject, MyFastReportsPlugin, ACalc); if ((!ACalc.GetParameters().Get("param_design_template").ToBool()) && (rbtEmailHosa.Checked)) { ACalc.AddStringParameter("param_currency", "Base"); // Always email HOSAs in Base Currency FStatusMsg += FastReportsWrapper.AutoEmailReports(FPetraUtilsObject, MyFastReportsPlugin, ACalc, FLedgerNumber, "Foreign"); return(false); } return(DataOk); } // Load Hosa Report Data
private void ViewReportTemplate(TRptCalculator ACalc) { String ReportName = ""; if (chkFees.Enabled && chkFees.Checked) { ReportName = "Fees"; } if (chkStewardship.Enabled && chkStewardship.Checked) { ReportName = "Stewardship"; } if (chkHOSA.Enabled && chkHOSA.Checked) { ReportName = "HOSA"; } if (ReportName == "") { return; } MyFastReportsPlugin = new FastReportsWrapper(ReportName); if (chkFees.Enabled && chkFees.Checked) { MyFastReportsPlugin.SetDataGetter(LoadFeesReportData); } if (chkStewardship.Enabled && chkStewardship.Checked) { MyFastReportsPlugin.SetDataGetter(LoadStewardshipReportData); } if (chkHOSA.Enabled && chkHOSA.Checked) { MyFastReportsPlugin.SetDataGetter(LoadHosaReportData); } MyFastReportsPlugin.DesignReport(ACalc); }
/// <summary> /// Print out the Hierarchy using FastReports template. /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void FilePrint(object sender, EventArgs e) { FastReportsWrapper ReportingEngine = new FastReportsWrapper("Duplicate Address Check"); if (!ReportingEngine.LoadedOK) { ReportingEngine.ShowErrorPopup(); return; } ReportingEngine.RegisterData(FDuplicateLocations, "DuplicateLocations"); TRptCalculator Calc = new TRptCalculator(); if (ModifierKeys.HasFlag(Keys.Control)) { ReportingEngine.DesignReport(Calc); } else { ReportingEngine.GenerateReport(Calc); } }
/// <summary> /// Print out the Hierarchy using FastReports template. /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void FilePrint(object sender, EventArgs e) { FastReportsWrapper ReportingEngine = new FastReportsWrapper("Account Hierarchy"); if (!ReportingEngine.LoadedOK) { ReportingEngine.ShowErrorPopup(); return; } if (!FMainDS.AAccount.Columns.Contains("AccountPath")) { FMainDS.AAccount.Columns.Add("AccountPath", typeof(String)); FMainDS.AAccount.Columns.Add("AccountLevel", typeof(Int32)); } DataView PathView = new DataView(FMainDS.AAccountHierarchyDetail); PathView.Sort = "a_reporting_account_code_c"; DataView AccountView = new DataView(FMainDS.AAccount); AccountView.Sort = "a_account_code_c"; // I need to make the "AccountPath" field that will be used to sort the table for printout: foreach (DataRowView rv in PathView) { DataRow Row = rv.Row; String AccountCode = Row["a_reporting_account_code_c"].ToString(); String Path = Row["a_report_order_i"] + "-" + AccountCode + '~'; Int32 Level = 0; String ReportsTo = Row["a_account_code_to_report_to_c"].ToString(); while (ReportsTo != "") { Int32 ParentIdx = PathView.Find(ReportsTo); if (ParentIdx >= 0) { DataRow ParentRow = PathView[ParentIdx].Row; ReportsTo = ParentRow["a_account_code_to_report_to_c"].ToString(); Path = ParentRow["a_report_order_i"] + "-" + ParentRow["a_reporting_account_code_c"].ToString() + "~" + Path; Level++; if (Level > 100) // Surely this is a fault. If I just break here, { break; // the report will print and I should be able to see what the fault is. } } else { ReportsTo = ""; } } Int32 AccountIdx = AccountView.Find(AccountCode); DataRow AccountRow = AccountView[AccountIdx].Row; AccountRow["AccountPath"] = Path; AccountRow["AccountLevel"] = Level; } AccountView.Sort = "AccountPath"; DataTable SortedByPath = AccountView.ToTable(); ReportingEngine.RegisterData(SortedByPath, "AccountHierarchy"); ReportingEngine.RegisterData(FMainDS.AAnalysisAttribute, "AnalysisAttribute"); TRptCalculator Calc = new TRptCalculator(); ALedgerRow LedgerRow = FMainDS.ALedger[0]; Calc.AddParameter("param_ledger_number_i", LedgerRow.LedgerNumber); Calc.AddStringParameter("param_ledger_name", LedgerRow.LedgerName); if (ModifierKeys.HasFlag(Keys.Control)) { ReportingEngine.DesignReport(Calc); } else { ReportingEngine.GenerateReport(Calc); } }
/// <summary> /// Print out the Hierarchy using FastReports template. /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void FilePrint(object sender, EventArgs e) { TLogging.Log("CostCentreHierarchy.File Print.."); FastReportsWrapper ReportingEngine = new FastReportsWrapper("Cost Centre Hierarchy"); if (!ReportingEngine.LoadedOK) { ReportingEngine.ShowErrorPopup(); return; } if (!FMainDS.ACostCentre.Columns.Contains("CostCentrePath")) { FMainDS.ACostCentre.Columns.Add("CostCentrePath", typeof(String)); FMainDS.ACostCentre.Columns.Add("CostCentreLevel", typeof(Int32)); } DataView PathView = new DataView(FMainDS.ACostCentre); PathView.Sort = "a_cost_centre_code_c"; TLogging.Log("CostCentreHierarchy.File Print calculating paths.."); // I need to make the "CostCentrePath" field that will be used to sort the table for printout: foreach (DataRowView rv in PathView) { DataRow Row = rv.Row; String Path = Row["a_cost_centre_code_c"].ToString() + '~'; Int32 Level = 0; String ReportsTo = Row["a_cost_centre_to_report_to_c"].ToString(); while (ReportsTo != "") { Int32 ParentIdx = PathView.Find(ReportsTo); if (ParentIdx >= 0) { DataRow ParentRow = PathView[ParentIdx].Row; ReportsTo = ParentRow["a_cost_centre_to_report_to_c"].ToString(); Path = ParentRow["a_cost_centre_code_c"].ToString() + "~" + Path; Level++; if (Level > 100) // Surely this is a fault. If I just break here, { break; // the report will print and I should be able to see what the fault is. } } else { ReportsTo = ""; } } Row["CostCentrePath"] = Path; Row["CostCentreLevel"] = Level; } PathView.Sort = "CostCentrePath"; DataTable SortedByPath = PathView.ToTable(); TLogging.Log("CostCentreHierarchy.File Print paths all done."); ReportingEngine.RegisterData(SortedByPath, "CostCentreHierarchy"); TRptCalculator Calc = new TRptCalculator(); ALedgerRow LedgerRow = FMainDS.ALedger[0]; Calc.AddParameter("param_ledger_nunmber", LedgerRow.LedgerNumber); Calc.AddStringParameter("param_ledger_name", LedgerRow.LedgerName); TLogging.Log("CostCentreHierarchy.File Print calling FastReport..."); if (ModifierKeys.HasFlag(Keys.Control)) { ReportingEngine.DesignReport(Calc); } else { ReportingEngine.GenerateReport(Calc); } }
/// <summary> /// Print out the Motivation Details using FastReports template. /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void FilePrint(object sender, EventArgs e) { FastReportsWrapper ReportingEngine = new FastReportsWrapper("Motivation Details"); if (!ReportingEngine.LoadedOK) { ReportingEngine.ShowErrorPopup(); return; } // Add in the Fees applicable for each row: if (!FMainDS.AMotivationDetail.Columns.Contains("Fees")) { FMainDS.AMotivationDetail.Columns.Add("Fees", typeof(String)); FMainDS.AMotivationDetail.Columns.Add("KeyMin", typeof(String)); } foreach (AMotivationDetailRow Row in FMainDS.AMotivationDetail.Rows) { FMainDS.AMotivationDetailFee.DefaultView.RowFilter = String.Format( "a_motivation_group_code_c='{0}' AND a_motivation_detail_code_c='{1}'", Row.MotivationGroupCode, Row.MotivationDetailCode); String Fees = ""; foreach (DataRowView rv in FMainDS.AMotivationDetailFee.DefaultView) { AMotivationDetailFeeRow FeeRow = (AMotivationDetailFeeRow)rv.Row; if (Fees != "") { Fees += ", "; } Fees += FeeRow.FeeCode; } Row["Fees"] = Fees; if (Row.RecipientKey != 0) { String mPartnerShortName; TPartnerClass mPartnerClass; if (TServerLookup.TMPartner.GetPartnerShortName(Row.RecipientKey, out mPartnerShortName, out mPartnerClass, true)) { Row["KeyMin"] = mPartnerShortName; } } } // foreach // // Ensure the proper sorting for the printout: FMainDS.AMotivationDetail.DefaultView.Sort = "a_motivation_group_code_c, a_motivation_detail_code_c"; ReportingEngine.RegisterData(FMainDS.AMotivationDetail.DefaultView.ToTable(), "MotivationDetail"); TRptCalculator Calc = new TRptCalculator(); ALedgerRow LedgerRow = FMainDS.ALedger[0]; Calc.AddParameter("param_ledger_number_i", LedgerRow.LedgerNumber); Calc.AddStringParameter("param_ledger_name", LedgerRow.LedgerName); Calc.AddParameter("param_TD", FTaxDeductiblePercentageEnabled); if (ModifierKeys.HasFlag(Keys.Control)) { ReportingEngine.DesignReport(Calc); } else { ReportingEngine.GenerateReport(Calc); } }
// // 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); }
// // 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. // (Returns FALSE if the user has selected "auto-email" and this method drives the email sending process itself, leaving nothing for the default process to do.) 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 TransAccountCodeFilter = ""; 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 + ")"; TransAccountCodeFilter = "AND a_transaction.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") + "'"; TransAccountCodeFilter = "AND a_transaction.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 TransCostCentreFilter = ""; 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 + ")"; TransCostCentreFilter = " AND a_transaction.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") + "'"; TransCostCentreFilter = " AND a_transaction.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 OrderByClause = "AccountCode, a_cost_centre_code_c, TransactionDate"; String Sortby = parameters.Get("param_sortby").ToString(); if (Sortby == "Cost Centre") { OrderByClause = "a_cost_centre_code_c, AccountCode, TransactionDate"; } if (Sortby == "Reference") { OrderByClause = "a_reference_c, a_cost_centre_code_c, AccountCode, TransactionDate"; 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") { OrderByClause = "AnalysisTypeCode, AnalysisValue, TransactionDate"; String FilterItem = parameters.Get("param_analyis_type_start").ToString(); if (FilterItem != "") { AnalysisTypeFilter = " AND a_analysis_type.a_analysis_type_code_c >='" + FilterItem + "' "; } FilterItem = parameters.Get("param_analyis_type_end").ToString(); if (FilterItem != "") { AnalysisTypeFilter += " AND a_analysis_type.a_analysis_type_code_c <='" + FilterItem + "' "; } } parameters.Add("param_groupfield", OrderByClause); 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"); String CurrencySelected = parameters.Get("param_currency").ToString(); // // FX Reval transactions should not be shown if "Transaction" currency was selected, // since these transactions only take place in Base currency; they're 0 in the account currency. String RevalFilter = ""; if (CurrencySelected == "Transaction") { RevalFilter = " AND ((a_account.a_foreign_currency_flag_l=FALSE) OR (a_account.a_foreign_currency_code_c = a_journal.a_transaction_currency_c))"; } String AmountField = CurrencySelected.StartsWith("Int") ? "a_amount_in_intl_currency_n" : CurrencySelected.StartsWith("Trans") ? "a_transaction_amount_n" : "a_amount_in_base_currency_n"; String CurrencyField = CurrencySelected.StartsWith("Int") ? "a_ledger.a_intl_currency_c" : CurrencySelected.StartsWith("Trans") ? "a_journal.a_transaction_currency_c" : "a_ledger.a_base_currency_c"; 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," + CurrencyField + " 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," + "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_journal, a_trans_anal_attrib, a_analysis_type, a_ledger, a_account" + " WHERE a_transaction." + LedgerFilter + " AND a_transaction.a_ledger_number_i = a_account.a_ledger_number_i " + " AND a_transaction.a_account_code_c = a_account.a_account_code_c " + " AND a_transaction.a_ledger_number_i = a_ledger.a_ledger_number_i " + " 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 " + RevalFilter + " 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 + TransAccountCodeFilter + TransCostCentreFilter + " 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 " + OrderByClause + ", 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," + CurrencyField + " 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, a_ledger, a_account WHERE " + " a_transaction." + LedgerFilter + " AND a_transaction.a_ledger_number_i = a_account.a_ledger_number_i " + " AND a_transaction.a_account_code_c = a_account.a_account_code_c " + " AND a_transaction.a_ledger_number_i = a_ledger.a_ledger_number_i " + " 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 " + RevalFilter + TransAccountCodeFilter + TransCostCentreFilter + " 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 " + OrderByClause); } 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(), CurrencySelected ); 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.. 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_currency_formatter", "0,0.000"); ACalc.AddStringParameter("param_base_currency_name", uco_GeneralSettings.GetBaseCurrency()); 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() ) { String Status = FastReportsWrapper.AutoEmailReports(FPetraUtilsObject, FPetraUtilsObject.FFastReportsPlugin, ACalc, FLedgerNumber, CostCentreFilter); MessageBox.Show(Status, Catalog.GetString("Account Detail Report")); return(false); } return(true); }