void BtnPrintReportPDFClick(object sender, EventArgs e) { OpenFileDialog DialogOpen = new OpenFileDialog(); // see file in sub directory test data. // create file with method TResultList.WriteBinaryFile DialogOpen.Filter = "Binary Report File (*.bin)|*.bin"; DialogOpen.RestoreDirectory = true; DialogOpen.Title = "Open Binary Report File"; if (DialogOpen.ShowDialog() == DialogResult.OK) { TResultList Results = new TResultList(); TParameterList Parameters; Results.ReadBinaryFile(DialogOpen.FileName, out Parameters); TPetraIdentity PetraIdentity = new TPetraIdentity( "TESTUSER", "", "", "", "", DateTime.MinValue, DateTime.MinValue, DateTime.MinValue, 0, -1, -1, false, false, false); UserInfo.GUserInfo = new TPetraPrincipal(PetraIdentity, null); PrintDocument doc = new PrintDocument(); TPdfPrinter pdfPrinter = new TPdfPrinter(doc, TGfxPrinter.ePrinterBehaviour.eReport); TReportPrinterLayout layout = new TReportPrinterLayout(Results, Parameters, pdfPrinter, true); pdfPrinter.Init(eOrientation.ePortrait, layout, eMarginType.ePrintableArea); pdfPrinter.SavePDF("test.pdf"); System.Diagnostics.Process.Start(Path.GetFullPath("test.pdf")); } }
} // 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 must not be static. DataTable TotalGiftsPerRecipient = null; TDBTransaction Transaction = new TDBTransaction(); TDataBase db = DBAccess.Connect("SelectGiftDonors"); string SqlQuery = TSession.GetVariable("QueryFinanceReport_SelectGiftRecipients").ToString(); SqlQuery = SqlQuery.Replace("GROUP by ", "AND recipient.p_partner_key_n = " + recipientKey + " GROUP by "); db.ReadTransaction( ref Transaction, delegate { TotalGiftsPerRecipient = db.SelectDT(SqlQuery, "result", Transaction); }); TotalGiftsPerRecipient.DefaultView.RowFilter = "RecipientKey = " + recipientKey.ToString(); DataTable resultTable = TotalGiftsPerRecipient.DefaultView.ToTable(true, new String[] { "DonorKey", "DonorName", "DonorClass", "YearTotal0", "YearTotal1", "YearTotal2", "YearTotal3" }); return(resultTable); }
/// <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 = new TDBTransaction(); DataTable tempTbl = new DataTable(); DBAccess.ReadTransaction( ref Transaction, delegate { tempTbl = Transaction.DataBaseObj.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> /// 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)); }
void TbbImportReportBinaryFileClick(object sender, EventArgs e) { OpenFileDialog DialogOpen = new OpenFileDialog(); // see file in sub directory test data. // create file with method TResultList.WriteBinaryFile DialogOpen.Filter = "Binary Report File (*.bin)|*.bin"; DialogOpen.RestoreDirectory = true; DialogOpen.Title = "Open Binary Report File"; if (DialogOpen.ShowDialog() == DialogResult.OK) { TResultList Results = new TResultList(); TParameterList Parameters; Results.ReadBinaryFile(DialogOpen.FileName, out Parameters); PrintDocument doc = new PrintDocument(); TPetraIdentity PetraIdentity = new TPetraIdentity( "TESTUSER", "", "", "", "", DateTime.MinValue, DateTime.MinValue, DateTime.MinValue, 0, -1, -1, false, false, false); UserInfo.GUserInfo = new TPetraPrincipal(PetraIdentity, null); FGfxPrinter = new TGfxPrinter(doc, TGfxPrinter.ePrinterBehaviour.eReport); new TReportPrinterLayout(Results, Parameters, FGfxPrinter, true); printPreviewControl1.Document = doc; doc.EndPrint += new PrintEventHandler(this.PrintDocument_EndPrint); printPreviewControl1.InvalidatePreview(); } }
/// <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 an extract from a report: all partners of a given type (or selection of multiple types) /// </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.ExtractPartnerByGeneralCriteria.sql"); // create a new object of this class and control extract calculation from base class QueryPartnerByGeneralCriteria ExtractQuery = new QueryPartnerByGeneralCriteria(); 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 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) { string SqlStmt = TDataBase.ReadSqlFile("Gift.Queries.ExtractDonorByField.sql"); // create a new object of this class and control extract calculation from base class QueryDonorByField ExtractQuery = new QueryDonorByField(); return(ExtractQuery.CalculateExtractInternal(AParameters, SqlStmt, AResults)); }
/// <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> /// calculate an extract from a report: all partners working on a given field /// </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 QueryPartnerByField ExtractQuery = new QueryPartnerByField(); return(ExtractQuery.CalculateExtractInternal(AParameters, SqlStmt, AResults)); }
} // 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> /// 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> /// 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> /// <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> 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> /// 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; TDataBase db = DBAccess.Connect("GetFamilyKeys"); TDBTransaction Transaction = db.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTransaction); try { // now run the database query DataTable resultTable = db.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) { Transaction.Rollback(); } } }
/// <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.AsyncExecProgressServerSide.ProgressState == TAsyncExecProgressState.Aeps_Executing) { 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> /// 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(); if (AReportParameterXmlFile.IndexOf(".Test.xml") == -1) { throw new Exception("invalid report name, should end with .Test.xml"); } string resultFile = AReportParameterXmlFile.Replace(".Test.xml", ".Results.csv"); string parameterFile = AReportParameterXmlFile.Replace(".Test.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()); Parameters.Sort(); Parameters.Save(parameterFile, false); Results.WriteCSV(Parameters, resultFile, ","); }
/// <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> /// 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)); }
/// <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; TDataBase db = DBAccess.Connect("CalculateBirthdays"); TDBTransaction Transaction = db.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTransaction); try { // now run the database query TLogging.Log("Getting the data from the database...", TLoggingType.ToStatusBar); DataTable resultTable = db.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) { Transaction.Rollback(); } } }
/// <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
} // 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 = TSystemDefaults.GetBooleanDefault(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> /// 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> /// 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> /// get all gifts for the current costcentre and account /// </summary> public static DataTable HosaCalculateGifts(TParameterList AParameters, TResultList AResults) { SortedList <string, string> Defines = new SortedList <string, string>(); List <OdbcParameter> SqlParameterList = new List <OdbcParameter>(); try { if (AParameters.Get("param_filter_cost_centres").ToString() == "PersonalCostcentres") { Defines.Add("PERSONALHOSA", "true"); } SqlParameterList.Add(new OdbcParameter("ledgernumber", OdbcType.Decimal) { Value = AParameters.Get("param_ledger_number_i").ToDecimal() }); SqlParameterList.Add(new OdbcParameter("costcentre", OdbcType.VarChar) { Value = AParameters.Get("line_a_cost_centre_code_c") }); if (AParameters.Get("param_ich_number").ToInt32() == 0) { Defines.Add("NOT_LIMITED_TO_ICHNUMBER", "true"); } else { SqlParameterList.Add(new OdbcParameter("ichnumber", OdbcType.Int) { Value = AParameters.Get("param_ich_number").ToInt32() }); } SqlParameterList.Add(new OdbcParameter("batchstatus", OdbcType.VarChar) { Value = MFinanceConstants.BATCH_POSTED }); if (AParameters.Get("param_period").ToBool() == true) { Defines.Add("BYPERIOD", "true"); SqlParameterList.Add(new OdbcParameter("batchyear", OdbcType.Int) { Value = AParameters.Get("param_year_i").ToInt32() }); SqlParameterList.Add(new OdbcParameter("batchperiod_start", OdbcType.Int) { Value = AParameters.Get("param_start_period_i").ToInt32() }); SqlParameterList.Add(new OdbcParameter("batchperiod_end", OdbcType.Int) { Value = AParameters.Get("param_end_period_i").ToInt32() }); } else { SqlParameterList.Add(new OdbcParameter("param_start_date", OdbcType.Int) { Value = AParameters.Get("param_start_date").ToInt32() }); SqlParameterList.Add(new OdbcParameter("param_end_date", OdbcType.Int) { Value = AParameters.Get("param_end_date").ToInt32() }); } SqlParameterList.Add(new OdbcParameter("accountcode", OdbcType.VarChar) { Value = AParameters.Get("line_a_account_code_c") }); } catch (Exception e) { TLogging.Log("problem while preparing sql statement for HOSA report: " + e.ToString()); return(null); } string SqlStmt = TDataBase.ReadSqlFile("ICH.HOSAReportGiftSummary.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); } resultTable.Columns.Add("a_transaction_amount_n", typeof(Decimal)); resultTable.Columns.Add("a_amount_in_base_currency_n", typeof(Decimal)); resultTable.Columns.Add("a_amount_in_intl_currency_n", typeof(Decimal)); resultTable.Columns.Add("a_reference_c", typeof(string)); resultTable.Columns.Add("a_narrative_c", typeof(string)); Boolean InternationalCurrency = AParameters.Get("param_currency").ToString() == "International"; Double ExchangeRate = 1.00; // TODO Get exchange rate! foreach (DataRow r in resultTable.Rows) { r["a_transaction_amount_n"] = Convert.ToDecimal(r["GiftTransactionAmount"]); r["a_amount_in_base_currency_n"] = Convert.ToDecimal(r["GiftBaseAmount"]); if (InternationalCurrency) { r["a_amount_in_intl_currency_n"] = (Decimal)(Convert.ToDouble(r["GiftBaseAmount"]) * ExchangeRate); } r["a_reference_c"] = StringHelper.PartnerKeyToStr(Convert.ToInt64(r["RecipientKey"])); r["a_narrative_c"] = r["RecipientShortname"].ToString(); } return(resultTable); } catch (Exception e) { TLogging.Log(e.ToString()); return(null); } finally { if (NewTransaction) { DBAccess.GDBAccessObj.RollbackTransaction(); } } }
/// <summary> /// Find all the gifts for a specific month, returning "worker", "field" and "total" results. /// </summary> public static DataTable TotalGiftsThroughFieldMonth(TParameterList AParameters, TResultList AResults) { Int32 LedgerNum = AParameters.Get("param_ledger_number_i").ToInt32(); Int32 Year = AParameters.Get("param_YearBlock").ToInt32(); string YearStart = String.Format("#{0:0000}-01-01#", Year); string YearEnd = String.Format("#{0:0000}-12-31#", Year); bool TaxDeductiblePercentageEnabled = Convert.ToBoolean( TSystemDefaults.GetSystemDefault(SharedConstants.SYSDEFAULT_TAXDEDUCTIBLEPERCENTAGE, "FALSE")); 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 batch.a_gl_effective_date_d >= " + YearStart + " AND batch.a_gl_effective_date_d <= " + YearEnd + " 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"); Boolean newTransaction; TDBTransaction Transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out newTransaction); DataTable tempTbl = DBAccess.GDBAccessObj.SelectDT(SqlQuery, "result", Transaction); if (newTransaction) { DBAccess.GDBAccessObj.RollbackTransaction(); } DataTable resultTable = new DataTable(); resultTable.Columns.Add("MonthName", typeof(String)); // resultTable.Columns.Add("MonthWorker", typeof(Decimal)); // These are the names of the variables resultTable.Columns.Add("MonthWorkerCount", typeof(Int32)); // returned by this calculation. resultTable.Columns.Add("MonthField", typeof(Decimal)); // resultTable.Columns.Add("MonthFieldCount", typeof(Int32)); // resultTable.Columns.Add("MonthTotal", typeof(Decimal)); // resultTable.Columns.Add("MonthTotalCount", typeof(Int32)); // resultTable.Columns.Add("MonthWorkerTaxDeduct", typeof(Decimal)); resultTable.Columns.Add("MonthFieldTaxDeduct", typeof(Decimal)); resultTable.Columns.Add("MonthTotalTaxDeduct", typeof(Decimal)); for (int mnth = 1; mnth <= 12; mnth++) { string monthStart = String.Format("#{0:0000}-{1:00}-01#", Year, mnth); string nextMonthStart = String.Format("#{0:0000}-{1:00}-01#", Year, mnth + 1); if (mnth == 12) { nextMonthStart = String.Format("#{0:0000}-01-01#", Year + 1); } tempTbl.DefaultView.RowFilter = "Date >= " + monthStart + " AND Date < " + nextMonthStart; 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["MonthName"] = StringHelper.GetLongMonthName(mnth); resultRow["MonthWorker"] = WorkerTotal; resultRow["MonthWorkerCount"] = WorkerCount; resultRow["MonthField"] = FieldTotal; resultRow["MonthFieldCount"] = FieldCount; resultRow["MonthTotal"] = WorkerTotal + FieldTotal; resultRow["MonthTotalCount"] = TotalCount; resultRow["MonthWorkerTaxDeduct"] = WorkerTotalTaxDeduct; resultRow["MonthFieldTaxDeduct"] = FieldTotalTaxDeduct; resultRow["MonthTotalTaxDeduct"] = WorkerTotalTaxDeduct + FieldTotalTaxDeduct; resultTable.Rows.Add(resultRow); } return(resultTable); }
/// <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.tbtGenerateChart.Text = Catalog.GetString("Generate Chart"); this.tbtGenerateChart.ToolTipText = Catalog.GetString( "Generates a chart in Excel (only ava" + "ilable yet for few reports at the moment)"); 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> /// 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() && (Parameters.Get("CancelReportCalculation").ToBool() != true)) { 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); }