/// <summary> /// calculate an extract from a report: all partners who have a Contact Log meeting selected criteria /// </summary> /// <param name="AParameters"></param> /// <param name="AResults"></param> /// <returns></returns> public static bool CalculateExtract(TParameterList AParameters, TResultList AResults) { string SqlStmt = TDataBase.ReadSqlFile("Partner.Queries.ExtractPartnerByContactLog.sql"); QueryPartnerByContactLog ExtractQuery = new QueryPartnerByContactLog(); return ExtractQuery.CalculateExtractInternal(AParameters, SqlStmt, AResults); }
/// <summary> /// calculate an extract from a report: all partners living in a given city /// </summary> /// <param name="AParameters"></param> /// <param name="AResults"></param> /// <param name="AExtractId"></param> /// <returns></returns> public static bool CalculateExtract(TParameterList AParameters, TResultList AResults, out int AExtractId) { string SqlStmt = TDataBase.ReadSqlFile("Partner.Queries.ExtractFamilyMembers.sql"); // create a new object of this class and control extract calculation from base class QueryFamilyMembersExtract ExtractQuery = new QueryFamilyMembersExtract(); return ExtractQuery.CalculateExtractInternal(AParameters, SqlStmt, AResults, out AExtractId); }
/// <summary> /// calculate an extract from a report: all recipient that have given to particular fields (ledgers) /// </summary> /// <param name="AParameters"></param> /// <param name="AResults"></param> /// <returns></returns> public static bool CalculateExtract(TParameterList AParameters, TResultList AResults) { string SqlStmt = TDataBase.ReadSqlFile("Gift.Queries.ExtractRecipientByField.sql"); // create a new object of this class and control extract calculation from base class QueryRecipientByField ExtractQuery = new QueryRecipientByField(); return ExtractQuery.CalculateExtractInternal(AParameters, SqlStmt, AResults); }
/// <summary> /// calculate an extract from a report: all partners living in a given city /// </summary> /// <param name="AParameters"></param> /// <param name="AResults"></param> /// <returns></returns> public static bool CalculateExtract(TParameterList AParameters, TResultList AResults) { string SqlStmt = TDataBase.ReadSqlFile("Partner.Queries.ExtractByPartnerCity.sql"); // create a new object of this class and control extract calculation from base class QueryPartnerByCity ExtractQuery = new QueryPartnerByCity(); return ExtractQuery.CalculateExtractInternal(AParameters, SqlStmt, AResults); }
/// <summary> /// Copy Constructor /// creates a copy of another Resultlist; /// this is required to be able to print and export to CSV with the correctly formatted dates /// /// </summary> /// <returns>void</returns> public TResultList(TResultList copy) { MaxDisplayColumns = copy.MaxDisplayColumns; results = new ArrayList(); foreach (TResult r in copy.results) { results.Add(new TResult(r)); } }
/// <summary> /// calculate an extract from a report: all donors that have given to particular fields (ledgers) /// </summary> /// <param name="AParameters"></param> /// <param name="AResults"></param> /// <returns></returns> public static bool CalculateExtract(TParameterList AParameters, TResultList AResults) { // Sql statements will be initialized later on in special treatment string SqlStmt = ""; // create a new object of this class and control extract calculation from base class QueryDonorByAmount ExtractQuery = new QueryDonorByAmount(); return ExtractQuery.CalculateExtractInternal(AParameters, SqlStmt, AResults); }
/// <summary> /// constructor /// </summary> /// <param name="AResult"></param> /// <param name="AParameters"></param> /// <param name="APrinter"></param> public TReportPrinterCommon(TResultList AResult, TParameterList AParameters, TPrinter APrinter) { // go through all results and parameters and replace the unformatted and encoded date // the whole point is to format the dates differently, depending on the output (printer vs. CSV) FParameters = AParameters.ConvertToFormattedStrings("Localized"); FResultList = AResult.ConvertToFormattedStrings(FParameters, "Localized"); FResults = FResultList.GetResults(); FLowestLevel = FParameters.Get("lowestLevel").ToInt(); FTimePrinted = DateTime.Now; FPrinter = APrinter; }
/// <summary> /// constructor /// </summary> /// <param name="parameters"></param> /// <param name="results"></param> /// <param name="reportStore"></param> /// <param name="report"></param> /// <param name="dataDB"></param> /// <param name="depth"></param> /// <param name="column"></param> /// <param name="lineId"></param> /// <param name="parentRowId"></param> public TRptEvaluator(TParameterList parameters, TResultList results, TReportStore reportStore, TRptReport report, TDataBase dataDB, int depth, int column, int lineId, int parentRowId) : base(parameters, results, reportStore, report, dataDB, depth, column, lineId, parentRowId) { }
/// <summary> /// constructor /// </summary> public TGridPreview(Form APreviewForm, TFrmPetraUtils APetraUtilsObject, TPreviewDelegate APreviewDelegate, TResultList AResultList, TParameterList AParameters) { results = AResultList.ConvertToFormattedStrings(AParameters); FOrigParameters = AParameters; parameters = AParameters.ConvertToFormattedStrings(); FPreviewForm = APreviewForm; FPetraUtilsObject = APetraUtilsObject; FPreviewDelegate = APreviewDelegate; FGenerateReportThread = null; }
/// <summary> /// constructor /// </summary> /// <param name="AResult"></param> /// <param name="AParameters"></param> /// <param name="APrinter"></param> /// <param name="AWrapColumn">True: Wrap text in the column if it is to long. Otherwise cut it</param> public TReportPrinterLayout(TResultList AResult, TParameterList AParameters, TPrinter APrinter, bool AWrapColumn) : base(AResult, AParameters, APrinter) { FWrapColumn = AWrapColumn; if (AParameters.Get("ReportWidth").ToDouble() > 20) { APrinter.Init(eOrientation.eLandscape, this, eMarginType.eDefaultMargins); } else { APrinter.Init(eOrientation.ePortrait, this, eMarginType.eDefaultMargins); } }
/// <summary> /// get the family keys of the specified persons /// </summary> /// <param name="AParameters"></param> /// <param name="AResults"></param> public static DataTable GetFamilyKeys(TParameterList AParameters, TResultList AResults) { SortedList <string, string>Defines = new SortedList <string, string>(); List <OdbcParameter>SqlParameterList = new List <OdbcParameter>(); try { // prepare the sql statement parameters AddPartnerSelectionParametersToSqlQuery(AParameters, Defines, SqlParameterList); } catch (Exception e) { TLogging.Log("problem while preparing sql statement for birthday report: " + e.ToString()); return null; } string SqlStmt = TDataBase.ReadSqlFile("Personnel.Reports.GetFamilyKeyOfPerson.sql", Defines); Boolean NewTransaction; TDBTransaction Transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTransaction); try { // now run the database query DataTable resultTable = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "result", Transaction, SqlParameterList.ToArray()); // if this is taking a long time, every now and again update the TLogging statusbar, and check for the cancel button if (AParameters.Get("CancelReportCalculation").ToBool() == true) { return null; } return resultTable; } catch (Exception e) { TLogging.Log(e.ToString()); return null; } finally { if (NewTransaction) { DBAccess.GDBAccessObj.RollbackTransaction(); } } }
/// <summary> /// This formats the dates for different output, for example printing /// </summary> /// <param name="AParameters">the current parameters, environmnent variables, for formatting</param> /// <param name="AOutputType">if this is 'Localized' then the dates are formatted in the format DD-MMM-YYYY</param> /// <returns>s a new copy of the result, with the correct formatting /// </returns> public TResultList ConvertToFormattedStrings(TParameterList AParameters, String AOutputType) { TResultList ReturnValue = new TResultList(this); Int32 i; foreach (TResult r in ReturnValue.results) { for (i = 0; i <= 1; i++) { r.header[i] = new TVariant(r.header[i].ToFormattedString("", AOutputType)); } for (i = 0; i <= 1; i++) { r.descr[i] = new TVariant(r.descr[i].ToFormattedString("", AOutputType)); } for (i = 0; i < r.column.Length; i++) { if (r.column[i].TypeVariant == eVariantTypes.eString) { r.column[i] = new TVariant(r.column[i].ToString(), true); } else { // format thousands only or without decimals if (StringHelper.IsCurrencyFormatString(r.column[i].FormatString) && AParameters.Exists("param_currency_format")) { r.column[i] = new TVariant(r.column[i].ToFormattedString(AParameters.Get( "param_currency_format").ToString(), AOutputType), true); } else { r.column[i] = new TVariant(r.column[i].ToFormattedString("", AOutputType), true); } } } } return(ReturnValue); }
/// <summary> /// calculate an extract from a report: all partners in selected relationships with selected partner /// </summary> /// <param name="AParameters"></param> /// <param name="AResults"></param> /// <returns></returns> public static bool CalculateExtract(TParameterList AParameters, TResultList AResults) { string SqlStmt = ""; if (AParameters.Get("param_selection").ToString() == "an extract") { SqlStmt = TDataBase.ReadSqlFile("Partner.Queries.ExtractFromExtractByPartnerRelationship.sql"); } else if (AParameters.Get("param_selection").ToString() == "one partner") { SqlStmt = TDataBase.ReadSqlFile("Partner.Queries.ExtractByPartnerRelationship.sql"); } else { throw new ArgumentException("Must supply an extract or partner key."); } // create a new object of this class and control extract calculation from base class QueryPartnerByRelationship ExtractQuery = new QueryPartnerByRelationship(); return ExtractQuery.CalculateExtractInternal(AParameters, SqlStmt, AResults); }
} // Total Gifts Through Field Month /// <summary> /// Find all the gifts for a year, returning "worker", "field" and "total" results. /// </summary> public static DataTable TotalGiftsThroughFieldYear(TParameterList AParameters, TResultList AResults) { bool TaxDeductiblePercentageEnabled = Convert.ToBoolean( TSystemDefaults.GetSystemDefault(SharedConstants.SYSDEFAULT_TAXDEDUCTIBLEPERCENTAGE, "FALSE")); Int32 LedgerNum = AParameters.Get("param_ledger_number_i").ToInt32(); Int32 NumberOfYears = AParameters.Get("param_NumberOfYears").ToInt32(); string SqlQuery = "SELECT batch.a_gl_effective_date_d as Date, motive.a_report_column_c AS ReportColumn, "; if (AParameters.Get("param_currency").ToString() == "Base") { SqlQuery += "detail.a_gift_amount_n AS Amount"; if (TaxDeductiblePercentageEnabled) { SqlQuery += ", detail.a_tax_deductible_amount_base_n AS TaxDeductAmount"; } } else { SqlQuery += "detail.a_gift_amount_intl_n AS Amount"; if (TaxDeductiblePercentageEnabled) { SqlQuery += ", detail.a_tax_deductible_amount_intl_n AS TaxDeductAmount"; } } SqlQuery += (" FROM PUB_a_gift as gift, PUB_a_gift_detail as detail, PUB_a_gift_batch as batch, PUB_a_motivation_detail AS motive" + " WHERE detail.a_ledger_number_i = " + LedgerNum + " AND batch.a_batch_status_c = 'Posted'" + " AND batch.a_batch_number_i = gift.a_batch_number_i" + " AND batch.a_ledger_number_i = " + LedgerNum + " AND gift.a_ledger_number_i = " + LedgerNum + " AND detail.a_batch_number_i = gift.a_batch_number_i" + " AND detail.a_gift_transaction_number_i = gift.a_gift_transaction_number_i" + " AND motive.a_ledger_number_i = " + LedgerNum + " AND motive.a_motivation_group_code_c = detail.a_motivation_group_code_c" + " AND motive.a_motivation_detail_code_c = detail.a_motivation_detail_code_c" + " AND motive.a_receipt_l=true"); DataTable tempTbl = null; TDBTransaction Transaction = null; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref Transaction, delegate { tempTbl = DBAccess.GDBAccessObj.SelectDT(SqlQuery, "result", Transaction); }); DataTable resultTable = new DataTable(); resultTable.Columns.Add("SummaryYear", typeof(Int32)); // resultTable.Columns.Add("YearWorker", typeof(Decimal)); // These are the names of the variables resultTable.Columns.Add("YearWorkerCount", typeof(Int32)); // returned by this calculation. resultTable.Columns.Add("YearField", typeof(Decimal)); // resultTable.Columns.Add("YearFieldCount", typeof(Int32)); // resultTable.Columns.Add("YearTotal", typeof(Decimal)); // resultTable.Columns.Add("YearTotalCount", typeof(Int32)); // resultTable.Columns.Add("YearWorkerTaxDeduct", typeof(Decimal)); resultTable.Columns.Add("YearFieldTaxDeduct", typeof(Decimal)); resultTable.Columns.Add("YearTotalTaxDeduct", typeof(Decimal)); Int32 Year = DateTime.Now.Year; for (Int32 YearIdx = 0; YearIdx < NumberOfYears; YearIdx++) { //TODO: Calendar vs Financial Date Handling - Check if financial year start/end should be used not calendar string yearStart = String.Format("#{0:0000}-01-01#", Year - YearIdx); string yearEnd = String.Format("#{0:0000}-12-31#", Year - YearIdx); tempTbl.DefaultView.RowFilter = "Date >= " + yearStart + " AND Date < " + yearEnd; Decimal WorkerTotal = 0; Decimal FieldTotal = 0; Int32 WorkerCount = 0; Int32 FieldCount = 0; Int32 TotalCount = tempTbl.DefaultView.Count; Decimal WorkerTotalTaxDeduct = 0; Decimal FieldTotalTaxDeduct = 0; for (int i = 0; i < TotalCount; i++) { DataRow Row = tempTbl.DefaultView[i].Row; if (Row["ReportColumn"].ToString() == "Worker") { WorkerCount++; WorkerTotal += Convert.ToDecimal(Row["Amount"]); if (TaxDeductiblePercentageEnabled) { WorkerTotalTaxDeduct += Convert.ToDecimal(Row["TaxDeductAmount"]); } } else { FieldCount++; FieldTotal += Convert.ToDecimal(Row["Amount"]); if (TaxDeductiblePercentageEnabled) { FieldTotalTaxDeduct += Convert.ToDecimal(Row["TaxDeductAmount"]); } } } DataRow resultRow = resultTable.NewRow(); resultRow["SummaryYear"] = Year - YearIdx; resultRow["YearWorker"] = WorkerTotal; resultRow["YearWorkerCount"] = WorkerCount; resultRow["YearField"] = FieldTotal; resultRow["YearFieldCount"] = FieldCount; resultRow["YearTotal"] = WorkerTotal + FieldTotal; resultRow["YearTotalCount"] = TotalCount; resultRow["YearWorkerTaxDeduct"] = WorkerTotalTaxDeduct; resultRow["YearFieldTaxDeduct"] = FieldTotalTaxDeduct; resultRow["YearTotalTaxDeduct"] = WorkerTotalTaxDeduct + FieldTotalTaxDeduct; resultTable.Rows.Add(resultRow); } return resultTable; }
/// <summary> /// this is where all the calculations take place /// </summary> /// <returns>true if the report was successfully generated /// </returns> public Boolean GenerateResult(ref TParameterList parameterlist, ref TResultList resultlist, ref String AErrorMessage) { Boolean ReturnValue; if (TLogging.DebugLevel >= TLogging.DEBUGLEVEL_REPORTING) { // for timing of reports TLogging.Log("start calculating", TLoggingType.ToLogfile); } ReturnValue = false; AErrorMessage = ""; try { this.Parameters = parameterlist; if (!Parameters.Exists("calculateFromMethod")) { LoadReportDefinitionFiles(Parameters.Get("xmlfiles").ToString()); this.CurrentReport = this.ReportStore.Get(Parameters.Get("currentReport").ToString()); if (this.CurrentReport == null) { TLogging.Log("report \"" + Parameters.Get("currentReport").ToString() + "\" could not be found. XML file missing?"); return false; } InitColumns(); InitColumnsFormat(); } InitParameterLedgers(); if (Parameters.Get("param_multiperiod").ToBool()) { InitMultiPeriodColumns(); } Results.SetMaxDisplayColumns(Parameters.Get("MaxDisplayColumns").ToInt()); if (TLogging.DebugLevel >= TLogging.DEBUGLEVEL_REPORTING) { Parameters.Save(Path.GetDirectoryName(TSrvSetting.ServerLogFile) + Path.DirectorySeparatorChar + "LogParamAfterPreproc.xml", true); } // to avoid still having in the status line: loading common.xml, although he is already working on the report TLogging.Log("Preparing data for the report... ", TLoggingType.ToStatusBar); if (Calculate()) { if (Parameters.Get("CancelReportCalculation").ToBool() == true) { AErrorMessage = "Report calculation was cancelled"; return false; } resultlist = this.Results; if (TLogging.DebugLevel >= TLogging.DEBUGLEVEL_REPORTING) { string FilePath = Path.GetDirectoryName(TSrvSetting.ServerLogFile) + Path.DirectorySeparatorChar; Parameters.Save(FilePath + "LogParamAfterCalculation.xml", true); Results.WriteCSV(Parameters, FilePath + Path.DirectorySeparatorChar + "ReportResults.csv", ",", true, false); } ReturnValue = true; } } catch (Exception E) { TLogging.Log(E.StackTrace); TLogging.Log(E.Message); if (TLogging.DebugLevel >= TLogging.DEBUGLEVEL_REPORTING) { Parameters.Save(Path.GetDirectoryName(TSrvSetting.ServerLogFile) + Path.DirectorySeparatorChar + "LogAfterException.xml", true); } System.Console.WriteLine(E.StackTrace); AErrorMessage = E.Message; } if (TLogging.DebugLevel >= TLogging.DEBUGLEVEL_REPORTING) { // for timing of reports TLogging.Log("finished calculating", TLoggingType.ToLogfile); } return ReturnValue; }
public static Boolean CreateFamilyExtractForPersons(int ABaseExtractId, ref int AExtractId, String AExtractName, String AExtractDescription) { Boolean ResultValue = false; TParameterList ParameterList = new TParameterList(); TResultList ResultList = new TResultList(); ParameterList.Add("param_base_extract", ABaseExtractId); ParameterList.Add("param_extract_name", AExtractName); ParameterList.Add("param_extract_description", AExtractDescription); ResultValue = Ict.Petra.Server.MPartner.queries.QueryFamilyExtractForPersons.CalculateExtract (ParameterList, ResultList, out AExtractId); return ResultValue; }
/// <summary> /// calculate an extract from a report: all partners of a given type (or selection of multiple types) /// </summary> /// <param name="AParameters"></param> /// <param name="ASqlStmt"></param> /// <param name="AResults"></param> /// <param name="AExtractId"></param> /// <returns></returns> protected bool CalculateExtractInternal(TParameterList AParameters, string ASqlStmt, TResultList AResults, out int AExtractId) { Boolean ReturnValue = false; Boolean NewTransaction; TDBTransaction Transaction; List <OdbcParameter>SqlParameterList = new List <OdbcParameter>(); bool AddressFilterAdded; AExtractId = -1; Transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.Serializable, out NewTransaction); // get the partner keys from the database try { if (FSpecialTreatment) { ReturnValue = RunSpecialTreatment(AParameters, Transaction, out AExtractId); } else { // call to derived class to retrieve parameters specific for extract RetrieveParameters(AParameters, ref ASqlStmt, ref SqlParameterList); // add address filter information to sql statement and parameter list AddressFilterAdded = AddAddressFilter(AParameters, ref ASqlStmt, ref SqlParameterList); // now run the database query TLogging.Log("getting the data from the database", TLoggingType.ToStatusBar); DataTable partnerkeys = DBAccess.GDBAccessObj.SelectDT(ASqlStmt, "partners", Transaction, SqlParameterList.ToArray()); // filter data by postcode (if applicable) PostcodeFilter(ref partnerkeys, ref AddressFilterAdded, AParameters, Transaction); if (NewTransaction) { DBAccess.GDBAccessObj.RollbackTransaction(); } // if this is taking a long time, every now and again update the TLogging statusbar, and check for the cancel button // TODO: we might need to add this functionality to TExtractsHandling.CreateExtractFromListOfPartnerKeys as well??? if (AParameters.Get("CancelReportCalculation").ToBool() == true) { return false; } TLogging.Log("preparing the extract", TLoggingType.ToStatusBar); // create an extract with the given name in the parameters ReturnValue = TExtractsHandling.CreateExtractFromListOfPartnerKeys( AParameters.Get("param_extract_name").ToString(), AParameters.Get("param_extract_description").ToString(), out AExtractId, partnerkeys, 0, AddressFilterAdded, true); } if (ReturnValue) { DBAccess.GDBAccessObj.CommitTransaction(); } else { DBAccess.GDBAccessObj.RollbackTransaction(); } return ReturnValue; } catch (Exception Exc) { TLogging.Log("An Exception occured in CalculateExtractInternal:" + Environment.NewLine + Exc.ToString()); if (NewTransaction) { DBAccess.GDBAccessObj.RollbackTransaction(); } throw; } }
/// <summary> /// constructor /// </summary> /// <param name="ACallerForm"></param> /// <param name="caption">caption of the dialog</param> /// <param name="duration"></param> /// <param name="results"></param> /// <param name="parameters"></param> /// <param name="AWrapColumn">True: Wrap the text in the column. False: Cut the text that is too long for the column</param> /// <param name="ACalculator"></param> public TFrmPrintPreview(Form ACallerForm, String caption, TimeSpan duration, TResultList results, TParameterList parameters, bool AWrapColumn, TRptCalculator ACalculator) : base() { FPetraUtilsObject = new Ict.Petra.Client.CommonForms.TFrmPetraUtils(ACallerForm, this, stbMain); FCalculator = ACalculator; // // Required for Windows Form Designer support // InitializeComponent(); #region CATALOGI18N // this code has been inserted by GenerateI18N, all changes in this region will be overwritten by GenerateI18N this.mniFile.Text = Catalog.GetString("&File"); this.mniFileClose.Text = Catalog.GetString("&Close"); this.mniFileClose.ToolTipText = Catalog.GetString("Close the preview"); this.mniHelp.Text = Catalog.GetString("&Help"); this.mniHelpPetraHelp.Text = Catalog.GetString("&Petra Help"); this.mniHelpBugReport.Text = Catalog.GetString("Bug &Report"); this.mniHelpAboutPetra.Text = Catalog.GetString("&About OpenPetra..."); this.mniHelpDevelopmentTeam.Text = Catalog.GetString("&The Development Team..."); this.stbMain.Text = Catalog.GetString("Status Bar"); this.stpInfo.Text = Catalog.GetString("Ready"); this.tbbClose.Text = Catalog.GetString("Close"); this.tbbClose.ToolTipText = Catalog.GetString("Closes this window"); this.Text = Catalog.GetString("Print Preview"); this.tbpText.Text = Catalog.GetString("Text Preview"); this.txtOutput.Text = Catalog.GetString("Text Output"); this.tbpPreview.Text = Catalog.GetString("Print Preview"); this.lblNoPrinter.Text = Catalog.GetString("Unfortunately this function is disabled. Please install a printer to use this page."); this.CbB_Zoom.Text = Catalog.GetString("Select Zoom"); this.Btn_PreviousPage.Text = Catalog.GetString("Previous Page"); this.Btn_NextPage.Text = Catalog.GetString("Next Page"); this.tbpGridView.Text = Catalog.GetString("Detail Reports"); this.tbtPrint.Text = Catalog.GetString("Print"); this.tbtPrint.ToolTipText = Catalog.GetString("Print the report"); this.tbtExportCSV.Text = Catalog.GetString("Export to CSV"); this.tbtExportCSV.ToolTipText = Catalog.GetString("Export to CSV text file"); this.tbtExportExcelFile.Text = Catalog.GetString("Export to Excel"); this.tbtExportExcelFile.ToolTipText = Catalog.GetString("Export to Excel xlsx file or directly into Excel, if it is available"); this.tbtExportText.Text = Catalog.GetString("Save as Text file"); this.tbtExportText.ToolTipText = Catalog.GetString("Save as a text file (e.g. for email)"); this.tbtSendEmail.Text = Catalog.GetString("Send Email"); this.tbtSendEmail.ToolTipText = Catalog.GetString("Send the Report as an Email with Excel attachment"); this.tbtCreateExtract.Text = Catalog.GetString("Create Extract"); this.tbtCreateExtract.ToolTipText = Catalog.GetString("Create an Extract from this result set"); this.tbtGenerateChart.Text = Catalog.GetString("Generate Chart"); this.tbtGenerateChart.ToolTipText = Catalog.GetString( "Generates a chart in Excel (not available for all reports)"); this.Text = Catalog.GetString("Print Preview"); #endregion System.Windows.Forms.TabPage SelectedTab; this.Text = this.Text + ": " + caption; this.ReportName = caption; this.Results = results; this.Parameters = parameters; FTxtPrinter = new TTxtPrinter(); this.ReportTxtPrinter = new TReportPrinterLayout(Results, Parameters, FTxtPrinter, AWrapColumn); ReportTxtPrinter.PrintReport(); this.txtOutput.Lines = FTxtPrinter.GetArrayOfString(); FPrinterInstalled = this.PrintDocument.PrinterSettings.IsValid; FWrapColumn = AWrapColumn; if (FPrinterInstalled) { this.tabPreview.SelectedTab = tbpPreview; FGfxPrinter = new TGfxPrinter(this.PrintDocument, TGfxPrinter.ePrinterBehaviour.eReport); // TReportPrinterLayout ReportGfxPrinter new TReportPrinterLayout(Results, Parameters, FGfxPrinter, AWrapColumn); this.PrintPreviewControl.Document = FGfxPrinter.Document; this.PrintPreviewControl.Zoom = 1; // show 100% by default this.PrintPreviewControl.UseAntiAlias = true; this.lblNoPrinter.Visible = false; } else { // PrintPreviewControl.CalculatePageInfo will throw InvalidPrinterException this.tabPreview.SelectedTab = tbpText; this.PrintPreviewControl.Visible = false; this.CbB_Zoom.Enabled = false; this.Btn_PreviousPage.Enabled = false; this.Btn_NextPage.Enabled = false; } this.PrintChartProcedure = null; this.PrintChartProcedureValid = false; sgGridView.SortableHeaders = false; sgGridView.SelectionMode = SourceGrid.GridSelectionMode.Cell; this.sgGridView.DoubleClickCell += new TDoubleClickCellEventHandler(this.SgGridView_DoubleClickCell); FGridPreview = new TGridPreview(this, FPetraUtilsObject, @PreviewDetailReport, Results, Parameters); if (!FGridPreview.PopulateResultGrid(sgGridView)) { SelectedTab = tabPreview.SelectedTab; this.tabPreview.TabPages.Clear(); this.tabPreview.TabPages.AddRange(new TabPage[] { this.tbpText, this.tbpPreview }); tabPreview.SelectedTab = SelectedTab; } else { FGridPreview.PopulateGridContextMenu(ContextMenu1); } stpInfo.Text = string.Empty; stbMain.ShowMessage(String.Format(Catalog.GetString("It took {0} to calculate the report"), FormatDuration(duration))); }
/// <summary> /// calculate an extract from a report: all partners of a given type (or selection of multiple types) /// </summary> /// <param name="AParameters"></param> /// <param name="ASqlStmt"></param> /// <param name="AResults"></param> /// <param name="AExtractId"></param> /// <returns></returns> protected bool CalculateExtractInternal(TParameterList AParameters, string ASqlStmt, TResultList AResults, out int AExtractId) { Boolean ReturnValue = false; TDBTransaction Transaction = null; bool SubmissionOK = false; List <OdbcParameter>SqlParameterList = new List <OdbcParameter>(); bool AddressFilterAdded; int ExtractId = -1; DBAccess.GDBAccessObj.GetNewOrExistingAutoTransaction(IsolationLevel.Serializable, ref Transaction, ref SubmissionOK, delegate { // get the partner keys from the database if (FSpecialTreatment) { ReturnValue = RunSpecialTreatment(AParameters, Transaction, out ExtractId); } else { // call to derived class to retrieve parameters specific for extract RetrieveParameters(AParameters, ref ASqlStmt, ref SqlParameterList); // add address filter information to sql statement and parameter list AddressFilterAdded = AddAddressFilter(AParameters, ref ASqlStmt, ref SqlParameterList); // now run the database query TLogging.Log("Getting the data from the database...", TLoggingType.ToStatusBar); DataTable partnerkeys = DBAccess.GDBAccessObj.SelectDT(ASqlStmt, "partners", Transaction, SqlParameterList.ToArray()); // filter data by postcode (if applicable) PostcodeFilter(ref partnerkeys, ref AddressFilterAdded, AParameters, Transaction); // if this is taking a long time, every now and again update the TLogging statusbar, and check for the cancel button // TODO: we might need to add this functionality to TExtractsHandling.CreateExtractFromListOfPartnerKeys as well??? if (AParameters.Get("CancelReportCalculation").ToBool() == true) { ReturnValue = false; } else { TLogging.Log("Preparing the extract...", TLoggingType.ToStatusBar); // create an extract with the given name in the parameters ReturnValue = TExtractsHandling.CreateExtractFromListOfPartnerKeys( AParameters.Get("param_extract_name").ToString(), AParameters.Get("param_extract_description").ToString(), out ExtractId, partnerkeys, 0, AddressFilterAdded); } } if (ReturnValue) { SubmissionOK = true; } }); AExtractId = ExtractId; return ReturnValue; }
/// <summary> /// constructor /// </summary> /// <param name="parameters"></param> /// <param name="results"></param> /// <param name="reportStore"></param> /// <param name="report"></param> /// <param name="dataDB"></param> /// <param name="depth"></param> /// <param name="column"></param> /// <param name="lineId"></param> /// <param name="parentRowId"></param> public TRptSituation(TParameterList parameters, TResultList results, TReportStore reportStore, TRptReport report, TDataBase dataDB, int depth, int column, int lineId, int parentRowId) { TRptSituation.RunningCode = 0; this.Parameters = parameters; this.Results = results; this.ReportStore = reportStore; this.CurrentReport = report; this.DatabaseConnection = dataDB; this.Depth = depth; this.column = column; this.LineId = lineId; this.ParentRowId = parentRowId; }
/// <summary> /// get all partners that we want to display on the current birthday report /// </summary> public static DataTable CalculateBirthdays(TParameterList AParameters, TResultList AResults) { SortedList <string, string>Defines = new SortedList <string, string>(); List <OdbcParameter>SqlParameterList = new List <OdbcParameter>(); try { // prepare the sql statement parameters if (AParameters.Exists("FamilyKey")) { SqlParameterList.Add(new OdbcParameter("FamilyKey", OdbcType.Decimal) { Value = AParameters.Get("FamilyKey").ToDecimal() }); Defines.Add("BYFAMILYKEY", string.Empty); } else { AddPartnerSelectionParametersToSqlQuery(AParameters, Defines, SqlParameterList); } if (AParameters.Get("param_chkSelectTypes").ToBool() == true) { string[] types = AParameters.Get("param_typecode").ToString().Split(new char[] { ',' }); string FilterForTypes = string.Empty; foreach (string type in types) { if (FilterForTypes.Length > 0) { FilterForTypes += " OR "; } FilterForTypes += "pptype.p_type_code_c = ?"; SqlParameterList.Add(new OdbcParameter("typecode" + FilterForTypes.Length, OdbcType.VarChar) { Value = type }); } Defines.Add("SELECTTYPES", "(" + FilterForTypes + ")"); } if (AParameters.Get("param_chkUseDate").ToBool() == true) { DateTime FromDate = AParameters.Get("param_dtpFromDate").ToDate(); DateTime ToDate = AParameters.Get("param_dtpToDate").ToDate(); if (FromDate.DayOfYear < ToDate.DayOfYear) { Defines.Add("WITHDATERANGE", string.Empty); SqlParameterList.Add(new OdbcParameter("startdate", OdbcType.Date) { Value = FromDate }); SqlParameterList.Add(new OdbcParameter("enddate", OdbcType.Date) { Value = ToDate }); } else { Defines.Add("WITHOUTDATERANGE", string.Empty); SqlParameterList.Add(new OdbcParameter("enddate", OdbcType.Date) { Value = ToDate }); SqlParameterList.Add(new OdbcParameter("startdate", OdbcType.Date) { Value = FromDate }); } } } catch (Exception e) { TLogging.Log("problem while preparing sql statement for birthday report: " + e.ToString()); return null; } string SqlStmt = TDataBase.ReadSqlFile("Personnel.Reports.Birthday.sql", Defines); Boolean NewTransaction; TDBTransaction Transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTransaction); try { // now run the database query TLogging.Log("Getting the data from the database...", TLoggingType.ToStatusBar); DataTable resultTable = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "result", Transaction, SqlParameterList.ToArray()); // if this is taking a long time, every now and again update the TLogging statusbar, and check for the cancel button if (AParameters.Get("CancelReportCalculation").ToBool() == true) { return null; } // if end date is not set, use the end of this year DateTime AgeDay = DateTime.Now; if (AParameters.Get("param_chkUseDate").ToBool() == true) { AgeDay = AParameters.Get("param_dtpToDate").ToDate(); } else { AgeDay = new DateTime(AgeDay.Year, 12, 31); } // Calculate the age, in new column resultTable.Columns.Add(new DataColumn("age", typeof(Int32))); foreach (DataRow r in resultTable.Rows) { int age = 0; if (r["DOB"] != DBNull.Value) { DateTime BDay = Convert.ToDateTime(r["DOB"]); age = AgeDay.Year - BDay.Year; } r["Age"] = age; } // filter by anniversaries? if ((AParameters.Get("param_chkAnniversaries").ToBool() == true) && !AParameters.Get("param_txtAnniversaries").IsZeroOrNull()) { List <string>anniversaries = new List <string>(AParameters.Get("param_txtAnniversaries").ToString().Split(new char[] { ',' })); List <DataRow>RowsToDelete = new List <DataRow>(); foreach (DataRow r in resultTable.Rows) { if (!anniversaries.Contains(r["Age"].ToString())) { RowsToDelete.Add(r); } } foreach (DataRow r in RowsToDelete) { resultTable.Rows.Remove(r); } } return resultTable; } catch (Exception e) { TLogging.Log(e.ToString()); return null; } finally { if (NewTransaction) { DBAccess.GDBAccessObj.RollbackTransaction(); } } }
/// <summary> /// constructor /// </summary> /// <param name="situation"></param> /// <param name="depth"></param> /// <param name="column"></param> /// <param name="lineId"></param> /// <param name="parentRowId"></param> public TRptSituation(TRptSituation situation, int depth, int column, int lineId, int parentRowId) { this.Parameters = situation.Parameters; this.Results = situation.Results; this.ReportStore = situation.ReportStore; this.CurrentReport = situation.CurrentReport; this.DatabaseConnection = situation.DatabaseConnection; this.Depth = depth; this.column = column; this.LineId = lineId; this.ParentRowId = parentRowId; }
/// <summary> /// constructor /// </summary> /// <param name="situation"></param> public TRptSituation(TRptSituation situation) { this.Parameters = situation.Parameters; this.Results = situation.Results; this.ReportStore = situation.ReportStore; this.CurrentReport = situation.CurrentReport; this.DatabaseConnection = situation.DatabaseConnection; this.Depth = situation.Depth; this.column = situation.column; this.LineId = situation.LineId; this.ParentRowId = situation.ParentRowId; }
/// <summary> /// this is where all the calculations take place /// </summary> /// <returns> /// true if the report was successfully generated /// </returns> public Boolean GenerateResultRemoteClient() { Boolean ReturnValue; Thread ProgressCheckThread; ReturnValue = false; FReportingGenerator = TRemote.MReporting.UIConnectors.ReportGenerator(); FKeepUpProgressCheck = true; try { this.Results = new TResultList(); FReportingGenerator.Start(this.Parameters.ToDataTable()); ProgressCheckThread = new Thread(new ThreadStart(AsyncProgressCheckThread)); ProgressCheckThread.Start(); } catch (Exception e) { TLogging.Log(e.Message); // Release the server object FReportingGenerator = null; return false; } // todo: allow canceling of the calculation of a report while (FKeepUpProgressCheck) { Thread.Sleep(500); } ReturnValue = FReportingGenerator.GetSuccess(); // Do not release the server object, we still might want to send an email... // FReportingGenerator = null; if (ReturnValue) { if (FCalculatesExtract) { TLogging.Log("Extract calculation finished. Look for extract '" + Parameters.Get("param_extract_name").ToString() + "' in Extract Master List.", TLoggingType.ToStatusBar); TFormsMessage BroadcastMessage = new TFormsMessage(TFormsMessageClassEnum.mcExtractCreated); BroadcastMessage.SetMessageDataName(Parameters.Get("param_extract_name").ToString()); TFormsList.GFormsList.BroadcastFormMessage(BroadcastMessage); } else { TLogging.Log("Report calculation finished.", TLoggingType.ToStatusBar); } } return ReturnValue; }
/// <summary> /// calculate the report and save the result and returned parameters to file /// </summary> public static void CalculateReport(string AReportParameterXmlFile, TParameterList ASpecificParameters, int ALedgerNumber = -1) { // important: otherwise month names are in different language, etc Thread.CurrentThread.CurrentCulture = new CultureInfo("en-GB", false); TReportGeneratorUIConnector ReportGenerator = new TReportGeneratorUIConnector(); TParameterList Parameters = new TParameterList(); string resultFile = AReportParameterXmlFile.Replace(".xml", ".Results.xml"); string parameterFile = AReportParameterXmlFile.Replace(".xml", ".Parameters.xml"); Parameters.Load(AReportParameterXmlFile); if (ALedgerNumber != -1) { Parameters.Add("param_ledger_number_i", ALedgerNumber); } Parameters.Add(ASpecificParameters); ReportGenerator.Start(Parameters.ToDataTable()); while (!ReportGenerator.Progress.JobFinished) { Thread.Sleep(500); } Assert.IsTrue(ReportGenerator.GetSuccess(), "Report did not run successfully"); TResultList Results = new TResultList(); Results.LoadFromDataTable(ReportGenerator.GetResult()); Parameters.LoadFromDataTable(ReportGenerator.GetParameter()); if (!Parameters.Exists("ControlSource", ReportingConsts.HEADERPAGELEFT1, -1, eParameterFit.eBestFit)) { Parameters.Add("ControlSource", new TVariant("Left1"), ReportingConsts.HEADERPAGELEFT1); } if (!Parameters.Exists("ControlSource", ReportingConsts.HEADERPAGELEFT2, -1, eParameterFit.eBestFit)) { Parameters.Add("ControlSource", new TVariant("Left2"), ReportingConsts.HEADERPAGELEFT2); } Parameters.Save(parameterFile, false); Results.WriteCSV(Parameters, resultFile, ",", false, false); }
/// <summary> /// this is where all the calculations take place /// </summary> /// <returns>true if the report was successfully generated /// </returns> public Boolean GenerateResultRemoteClient() { Boolean ReturnValue; Thread ProgressCheckThread; ReturnValue = false; FReportingGenerator = TRemote.MReporting.UIConnectors.ReportGenerator(); FKeepUpProgressCheck = true; // Register Object with the TEnsureKeepAlive Class so that it doesn't get GC'd TEnsureKeepAlive.Register(FReportingGenerator); try { this.Results = new TResultList(); FReportingGenerator.Start(this.Parameters.ToDataTable()); ProgressCheckThread = new Thread(new ThreadStart(AsyncProgressCheckThread)); ProgressCheckThread.Start(); } catch (Exception e) { TLogging.Log(e.Message); // UnRegister Object from the TEnsureKeepAlive Class so that the Object can get GC'd on the PetraServer TEnsureKeepAlive.UnRegister(FReportingGenerator); return false; } // todo: allow canceling of the calculation of a report while (FKeepUpProgressCheck) { Thread.Sleep(500); } ReturnValue = FReportingGenerator.GetSuccess(); // UnRegister Object from the TEnsureKeepAlive Class so that the Object can get GC'd on the PetraServer TEnsureKeepAlive.UnRegister(FReportingGenerator); if (ReturnValue) { if (FCalculatesExtract) { TLogging.Log("Extract calculation finished. Look for extract '" + Parameters.Get("param_extract_name").ToString() + "' in Extract Master List.", TLoggingType.ToStatusBar); TFormsMessage BroadcastMessage = new TFormsMessage(TFormsMessageClassEnum.mcExtractCreated); BroadcastMessage.SetMessageDataName(Parameters.Get("param_extract_name").ToString()); TFormsList.GFormsList.BroadcastFormMessage(BroadcastMessage); } else { TLogging.Log("Report calculation finished.", TLoggingType.ToStatusBar); } } return ReturnValue; }
} // Select Gift Recipients /// <summary> /// Find all the gifts for a worker, presenting the results in four year columns. /// NOTE - the user can select the field of the donor. /// /// All the DB work was previously done by the Select Gift Recipients function above. /// I only need to filter the table by recipientKey. /// </summary> /// <returns></returns> public static DataTable SelectGiftDonors(TParameterList AParameters, TResultList AResults) { Int64 recipientKey = AParameters.Get("RecipientKey").ToInt64(); TotalGiftsPerRecipient.DefaultView.RowFilter = "RecipientKey = " + recipientKey.ToString(); DataTable resultTable = TotalGiftsPerRecipient.DefaultView.ToTable(true, new String[] { "DonorKey", "DonorName", "DonorClass", "YearTotal0", "YearTotal1", "YearTotal2", "YearTotal3" }); return resultTable; }
/// <summary> /// ... /// </summary> public static void PrintTxt(TResultList results, TParameterList parameters, string output) { TReportPrinterLayout reportTxtPrinter; TTxtPrinter txtPrinter; txtPrinter = new TTxtPrinter(); reportTxtPrinter = new TReportPrinterLayout(results, parameters, txtPrinter, true); reportTxtPrinter.PrintReport(); txtPrinter.WriteToFile(output); }
/// <summary> /// constructor /// </summary> /// <param name="AResultList"></param> /// <param name="AParameters"></param> public TReportExcel(TResultList AResultList, TParameterList AParameters) { results = AResultList.ConvertToFormattedStrings(AParameters, "CSV"); parameters = AParameters.ConvertToFormattedStrings("CSV"); }
/// <summary> /// calculate an extract from a report: all partners of a given type (or selection of multiple types) /// </summary> /// <param name="AParameters"></param> /// <param name="ASqlStmt"></param> /// <param name="AResults"></param> /// <returns></returns> protected bool CalculateExtractInternal(TParameterList AParameters, string ASqlStmt, TResultList AResults) { int ExtractId; return CalculateExtractInternal(AParameters, ASqlStmt, AResults, out ExtractId); }
/// <summary> /// Find the latest gift that was given by a donor /// </summary> /// <param name="AParameters">report parameter list</param> /// <param name="AResults">result list</param> /// <returns>DataTable with row for last gift by donor</returns> public static DataTable SelectLatestGiftRow(TParameterList AParameters, TResultList AResults) { Int64 DonorKey = AParameters.Get("PartnerKey").ToInt64(); String StrSql = "SELECT DISTINCT " + AGiftTable.GetTableDBName() + "." + AGiftTable.GetLedgerNumberDBName() + ", " + AGiftTable.GetTableDBName() + "." + AGiftTable.GetBatchNumberDBName() + ", " + AGiftTable.GetTableDBName() + "." + AGiftTable.GetGiftTransactionNumberDBName() + ", " + AGiftTable.GetTableDBName() + "." + AGiftTable.GetDateEnteredDBName(); StrSql = StrSql + " FROM " + AGiftTable.GetTableDBName() + " WHERE " + AGiftTable.GetTableDBName() + "." + AGiftTable.GetDonorKeyDBName() + " = " + DonorKey.ToString() + " ORDER BY " + AGiftTable.GetTableDBName() + "." + AGiftTable.GetDateEnteredDBName() + " DESC"; TDBTransaction Transaction = null; DataTable tempTbl = new DataTable(); DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref Transaction, delegate { tempTbl = DBAccess.GDBAccessObj.SelectDT(StrSql, "result", Transaction); }); DataTable resultTbl = tempTbl.Clone(); resultTbl.Clear(); if (tempTbl.Rows.Count > 0) { resultTbl.Rows.Add((object[])tempTbl.Rows[0].ItemArray.Clone()); } return resultTbl; }
/// <summary> /// Find recipient Partner Key and name for all partners who received gifts in the timeframe. /// NOTE - the user can select the PartnerType of the recipient. /// /// With only a little more load on the DB, I can get all the data that the report will need, /// and store it in a DataTable local to this class, so that when more detailed data is requested below, /// I don't need another DB query. /// </summary> /// <returns>RecipientKey, RecipientName</returns> public static DataTable SelectGiftRecipients(TParameterList AParameters, TResultList AResults) { Int32 LedgerNum = AParameters.Get("param_ledger_number_i").ToInt32(); Boolean onlySelectedTypes = AParameters.Get("param_type_selection").ToString() == "selected_types"; Boolean onlySelectedFields = AParameters.Get("param_field_selection").ToString() == "selected_fields"; Boolean fromExtract = AParameters.Get("param_recipient").ToString() == "Extract"; Boolean oneRecipient = AParameters.Get("param_recipient").ToString() == "One Recipient"; String period0Start = AParameters.Get("param_from_date_0").ToDate().ToString("yyyy-MM-dd"); String period0End = AParameters.Get("param_to_date_0").ToDate().ToString("yyyy-MM-dd"); String period1Start = AParameters.Get("param_from_date_1").ToDate().ToString("yyyy-MM-dd"); String period1End = AParameters.Get("param_to_date_1").ToDate().ToString("yyyy-MM-dd"); String period2Start = AParameters.Get("param_from_date_2").ToDate().ToString("yyyy-MM-dd"); String period2End = AParameters.Get("param_to_date_2").ToDate().ToString("yyyy-MM-dd"); String period3Start = AParameters.Get("param_from_date_3").ToDate().ToString("yyyy-MM-dd"); String period3End = AParameters.Get("param_to_date_3").ToDate().ToString("yyyy-MM-dd"); String amountFieldName = (AParameters.Get("param_currency").ToString() == "International") ? "detail.a_gift_amount_intl_n" : "detail.a_gift_amount_n"; string SqlQuery = "SELECT DISTINCT " + "gift.p_donor_key_n AS DonorKey, " + "donor.p_partner_short_name_c AS DonorName, donor.p_partner_class_c AS DonorClass, " + "recipient.p_partner_key_n AS RecipientKey, " + "recipient.p_partner_short_name_c AS RecipientName, " + "SUM(CASE WHEN gift.a_date_entered_d BETWEEN '" + period0Start + "' AND '" + period0End + "' " + "THEN " + amountFieldName + " ELSE 0 END )as YearTotal0, " + "SUM(CASE WHEN gift.a_date_entered_d BETWEEN '" + period1Start + "' AND '" + period1End + "' " + "THEN " + amountFieldName + " ELSE 0 END )as YearTotal1, " + "SUM(CASE WHEN gift.a_date_entered_d BETWEEN '" + period2Start + "' AND '" + period2End + "' " + "THEN " + amountFieldName + " ELSE 0 END )as YearTotal2, " + "SUM(CASE WHEN gift.a_date_entered_d BETWEEN '" + period3Start + "' AND '" + period3End + "' " + "THEN " + amountFieldName + " ELSE 0 END )as YearTotal3 " + "FROM PUB_a_gift as gift, PUB_a_gift_detail as detail, PUB_a_gift_batch AS GiftBatch, PUB_p_partner AS donor, PUB_p_partner AS recipient "; if (onlySelectedTypes) { SqlQuery += ", PUB_p_partner_type AS RecipientType "; } if (fromExtract) { String extractName = AParameters.Get("param_extract_name").ToString(); SqlQuery += (", PUB_m_extract AS Extract, PUB_m_extract_master AS ExtractMaster " + "WHERE " + "recipient.p_partner_key_n = Extract.p_partner_key_n " + "AND Extract.m_extract_id_i = ExtractMaster.m_extract_id_i " + "AND ExtractMaster.m_extract_name_c = '" + extractName + "' " + "AND " ); } else { SqlQuery += "WHERE "; } SqlQuery += ("detail.a_ledger_number_i = " + LedgerNum + " " + "AND detail.p_recipient_key_n = recipient.p_partner_key_n " + "AND gift.p_donor_key_n = donor.p_partner_key_n " + "AND detail.a_batch_number_i = gift.a_batch_number_i " + "AND detail.a_gift_transaction_number_i = gift.a_gift_transaction_number_i " + "AND gift.a_date_entered_d BETWEEN '" + period3Start + "' AND '" + period0End + "' " + "AND gift.a_ledger_number_i = " + LedgerNum + " " + "AND GiftBatch.a_batch_status_c = 'Posted' " + "AND GiftBatch.a_batch_number_i = gift.a_batch_number_i " + "AND GiftBatch.a_ledger_number_i = " + LedgerNum + " " ); if (oneRecipient) { String recipientKey = AParameters.Get("param_recipient_key").ToString(); SqlQuery += ("AND recipient.p_partner_key_n = " + recipientKey + " "); } if (onlySelectedFields) { String selectedFieldList = AParameters.Get("param_clbFields").ToString(); selectedFieldList = selectedFieldList.Replace('\'', ' '); SqlQuery += ("AND detail.a_recipient_ledger_number_n IN (" + selectedFieldList + ") "); } if (onlySelectedTypes) { String selectedTypeList = "'" + AParameters.Get("param_clbTypes").ToString() + "'"; selectedTypeList = selectedTypeList.Replace(",", "','"); SqlQuery += ("AND RecipientType.p_partner_key_n = detail.p_recipient_key_n " + "AND RecipientType.p_type_code_c IN (" + selectedTypeList + ") "); } SqlQuery += ( "GROUP by gift.p_donor_key_n, donor.p_partner_short_name_c, donor.p_partner_class_c, recipient.p_partner_key_n, recipient.p_partner_short_name_c " + "ORDER BY recipient.p_partner_short_name_c"); TDBTransaction Transaction = null; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted, TEnforceIsolationLevel.eilMinimum, ref Transaction, delegate { TotalGiftsPerRecipient = DBAccess.GDBAccessObj.SelectDT(SqlQuery, "result", Transaction); }); // // Ok, I've got a DataTable with ALL THE DATA I need for the report, // but for this calculation I only want a list of partner keys and names... // DataTable resultTable = new DataTable(); resultTable.Columns.Add("RecipientKey", typeof(String)); // These are the names of the variables resultTable.Columns.Add("RecipientName", typeof(String)); // returned by this calculation. Int64 previousPartner = -1; foreach (DataRow Row in TotalGiftsPerRecipient.Rows) { Int64 partnerKey = Convert.ToInt64(Row["RecipientKey"]); if (partnerKey != previousPartner) { previousPartner = partnerKey; DataRow NewRow = resultTable.NewRow(); NewRow["RecipientKey"] = Row["RecipientKey"]; NewRow["RecipientName"] = Row["RecipientName"]; resultTable.Rows.Add(NewRow); } } return resultTable; } // Select Gift Recipients