/// <summary> /// retrieve parameters from client sent in AParameters and build up AParameterList to run SQL query /// </summary> /// <param name="AParameters"></param> /// <param name="ASqlStmt"></param> /// <param name="ASQLParameterList"></param> protected override void RetrieveParameters(TParameterList AParameters, ref string ASqlStmt, ref List <OdbcParameter>ASQLParameterList) { ICollection <String>param_explicit_specialtypes; // prepare list of special types param_explicit_specialtypes = AParameters.Get("param_explicit_specialtypes").ToString().Split(new Char[] { ',', }); if (param_explicit_specialtypes.Count == 0) { throw new NoNullAllowedException("At least one option must be checked."); } // now add parameters to sql parameter list ASQLParameterList.Add(TDbListParameterValue.OdbcListParameterValue("specialtype", OdbcType.VarChar, param_explicit_specialtypes)); ASQLParameterList.Add(new OdbcParameter("param_dateFieldsIncluded", OdbcType.Bit) { Value = !AParameters.Get("param_date_set").IsZeroOrNull() }); ASQLParameterList.Add(new OdbcParameter("Date", OdbcType.Date) { Value = AParameters.Get("param_date_set").ToDate() }); ASQLParameterList.Add(new OdbcParameter("param_active", OdbcType.Bit) { Value = AParameters.Get("param_active").ToBool() }); ASQLParameterList.Add(new OdbcParameter("param_families_only", OdbcType.Bit) { Value = AParameters.Get("param_families_only").ToBool() }); ASQLParameterList.Add(new OdbcParameter("param_exclude_no_solicitations", OdbcType.Bit) { Value = AParameters.Get("param_exclude_no_solicitations").ToBool() }); }
public void TestGeneralParametersProcessing() { TParameterList parameters = new TParameterList(); TVariant value = new TVariant(); value.ApplyFormatString("Currency"); Assert.AreEqual("0", value.ToFormattedString(), "null value for currency should be 0"); value = new TVariant(value.ToFormattedString()); parameters.Add("amountdue", value, -1, 2, null, null, ReportingConsts.CALCULATIONPARAMETERS); parameters.Save("testDebug.csv", true); Assert.AreEqual(true, parameters.Exists("amountdue", -1, 1, eParameterFit.eBestFitEvenLowerLevel), "can find added parameter"); Assert.AreEqual("0", parameters.Get("amountdue", -1, 2, eParameterFit.eBestFit).ToFormattedString(), "currency parameter is stored not correctly"); //Assert.AreEqual("0", parameters.Get("amountdue", -1, 1, eParameterFit.eBestFit).ToFormattedString(), "currency parameter is stored not correctly"); Assert.AreEqual("0", parameters.Get("amountdue", -1, 1, eParameterFit.eBestFitEvenLowerLevel).ToFormattedString(), "currency parameter cannot be accessed from level up"); parameters.Add("IntegerList", "300,400"); parameters.Save("test.csv", false); parameters.Load(Path.GetFullPath("test.csv")); Assert.AreEqual("eString:300,400", parameters.Get( "IntegerList").EncodeToString(), "integers separated by comma should be treated as string"); parameters.Save("test2.csv", true); }
/// <summary> /// Sets the selected values in the controls, using the parameters loaded from a file /// </summary> /// <param name="AParameters"></param> public void SetControls(TParameterList AParameters) { chkActivePartners.Checked = AParameters.Get("param_active").ToBool(); chkMailingAddressesOnly.Checked = AParameters.Get("param_mailing_addresses_only").ToBool(); chkFamiliesOnly.Checked = AParameters.Get("param_families_only").ToBool(); chkExcludeNoSolicitations.Checked = AParameters.Get("param_exclude_no_solicitations").ToBool(); }
private void SetControlsManual(TParameterList AParameters) { // param_reportday defines if the report is run on arrival or departuer days. if (AParameters.Get("param_reportday").ToString() == "Arrival") { rbtArrival.Checked = true; } else if (AParameters.Get("param_reportday").ToString() == "Departure") { rbtDeparture.Checked = true; } }
/// <summary> /// retrieve parameters from client sent in AParameters and build up AParameterList to run SQL query /// </summary> /// <param name="AParameters"></param> /// <param name="ASqlStmt"></param> /// <param name="ASQLParameterList"></param> protected override void RetrieveParameters(TParameterList AParameters, ref string ASqlStmt, ref List <OdbcParameter>ASQLParameterList) { // prepare list of selected events List <String>param_events = new List <String>(); foreach (TVariant choice in AParameters.Get("param_events").ToComposite()) { param_events.Add(choice.ToString()); } if (param_events.Count == 0) { throw new NoNullAllowedException("At least one event must be checked."); } // prepare list of selected event roles (comes all in one comma separated string) List <String>param_event_roles = new List <String>(); if (AParameters.Exists("param_event_roles")) { param_event_roles = new List <String>(AParameters.Get("param_event_roles").ToString().Split(',')); } if (param_event_roles.Count == 0) { throw new NoNullAllowedException("At least one event role must be checked."); } // now add parameters to sql parameter list ASQLParameterList.Add(TDbListParameterValue.OdbcListParameterValue("events", OdbcType.BigInt, param_events)); ASQLParameterList.Add(TDbListParameterValue.OdbcListParameterValue("event_roles", OdbcType.VarChar, param_event_roles)); ASQLParameterList.Add(new OdbcParameter("Accepted", OdbcType.Bit) { Value = AParameters.Get("param_status_accepted").ToBool() }); ASQLParameterList.Add(new OdbcParameter("Hold", OdbcType.Bit) { Value = AParameters.Get("param_status_hold").ToBool() }); ASQLParameterList.Add(new OdbcParameter("Enquiry", OdbcType.Bit) { Value = AParameters.Get("param_status_enquiry").ToBool() }); ASQLParameterList.Add(new OdbcParameter("Cancelled", OdbcType.Bit) { Value = AParameters.Get("param_status_cancelled").ToBool() }); ASQLParameterList.Add(new OdbcParameter("Rejected", OdbcType.Bit) { Value = AParameters.Get("param_status_rejected").ToBool() }); }
/// <summary> /// retrieve parameters from client sent in AParameters and build up AParameterList to run SQL query /// </summary> /// <param name="AParameters"></param> /// <param name="ASqlStmt"></param> /// <param name="ASQLParameterList"></param> protected override void RetrieveParameters(TParameterList AParameters, ref string ASqlStmt, ref List <OdbcParameter>ASQLParameterList) { // prepare list of selected publications List <String>param_explicit_publication = new List <String>(); foreach (TVariant choice in AParameters.Get("param_explicit_publication").ToComposite()) { param_explicit_publication.Add(choice.ToString()); } // now add parameters to sql parameter list ASQLParameterList.Add(TDbListParameterValue.OdbcListParameterValue("param_explicit_publication", OdbcType.VarChar, param_explicit_publication)); ASQLParameterList.Add(new OdbcParameter("param_free_subscriptions_only", OdbcType.Bit) { Value = AParameters.Get("param_free_subscriptions_only").ToBool() }); ASQLParameterList.Add(new OdbcParameter("param_include_active_subscriptions_only", OdbcType.Bit) { Value = AParameters.Get("param_include_active_subscriptions_only").ToBool() }); ASQLParameterList.Add(new OdbcParameter("param_include_active_subscriptions_only", OdbcType.Bit) { Value = AParameters.Get("param_include_active_subscriptions_only").ToBool() }); ASQLParameterList.Add(new OdbcParameter("param_subscription_status", OdbcType.VarChar) { Value = AParameters.Get("param_subscription_status").ToString() }); ASQLParameterList.Add(new OdbcParameter("param_active", OdbcType.Bit) { Value = AParameters.Get("param_active").ToBool() }); ASQLParameterList.Add(new OdbcParameter("param_families_only", OdbcType.Bit) { Value = AParameters.Get("param_families_only").ToBool() }); ASQLParameterList.Add(new OdbcParameter("param_exclude_no_solicitations", OdbcType.Bit) { Value = AParameters.Get("param_exclude_no_solicitations").ToBool() }); }
/// <summary> /// retrieve parameters from client sent in AParameters and build up AParameterList to run SQL query /// </summary> /// <param name="AParameters"></param> /// <param name="ASqlStmt"></param> /// <param name="ASQLParameterList"></param> protected override void RetrieveParameters(TParameterList AParameters, ref string ASqlStmt, ref List <OdbcParameter>ASQLParameterList) { // now add parameters to sql parameter list ASQLParameterList.Add(new OdbcParameter("city", OdbcType.VarChar) { Value = AParameters.Get("param_city").ToString() }); ASQLParameterList.Add(new OdbcParameter("Date", OdbcType.Date) { Value = AParameters.Get("param_today").ToDate() }); ASQLParameterList.Add(new OdbcParameter("Date", OdbcType.Date) { Value = AParameters.Get("param_today").ToDate() }); }
/// <summary> /// retrieve parameters from client sent in AParameters and build up AParameterList to run SQL query /// </summary> /// <param name="AParameters"></param> /// <param name="ASqlStmt"></param> /// <param name="ASQLParameterList"></param> protected override void RetrieveParameters(TParameterList AParameters, ref string ASqlStmt, ref List <OdbcParameter>ASQLParameterList) { // now add parameters to sql parameter list ASQLParameterList.Add(new OdbcParameter("base_extract", OdbcType.Int) { Value = AParameters.Get("param_base_extract").ToString() }); }
/// <summary> /// This procedure will switch the two columns /// </summary> /// <param name="AColumnParameters">List with the current columns</param> /// <param name="AFrom">Index of the column to move</param> /// <param name="ATo">Index of the new position of the column to move</param> /// <returns>void</returns> public static void SwitchColumn(ref TParameterList AColumnParameters, int AFrom, int ATo) { System.Int32 MaxDisplayColumns; System.Int32 Counter; System.Int32 ReferencedColumn; AColumnParameters.SwitchColumn(AFrom, ATo); /* switch the referenced columns in calculation */ MaxDisplayColumns = AColumnParameters.Get("MaxDisplayColumns").ToInt(); for (Counter = 0; Counter <= MaxDisplayColumns - 1; Counter += 1) { if (AColumnParameters.Exists("FirstColumn", Counter)) { ReferencedColumn = AColumnParameters.Get("FirstColumn", Counter).ToInt(); if (ReferencedColumn == AFrom) { ReferencedColumn = ATo; } else if (ReferencedColumn == ATo) { ReferencedColumn = AFrom; } AColumnParameters.Add("FirstColumn", new TVariant(ReferencedColumn), Counter); } if (AColumnParameters.Exists("SecondColumn", Counter)) { ReferencedColumn = AColumnParameters.Get("SecondColumn", Counter).ToInt(); if (ReferencedColumn == AFrom) { ReferencedColumn = ATo; } else if (ReferencedColumn == ATo) { ReferencedColumn = AFrom; } AColumnParameters.Add("SecondColumn", new TVariant(ReferencedColumn), Counter); } } }
/// <summary> /// retrieve parameters from client sent in AParameters and build up AParameterList to run SQL query /// </summary> /// <param name="AParameters"></param> /// <param name="ASqlStmt"></param> /// <param name="ASQLParameterList"></param> protected override void RetrieveParameters(TParameterList AParameters, ref string ASqlStmt, ref List <OdbcParameter>ASQLParameterList) { // prepare list of selected events List <String>param_events = new List <String>(); foreach (TVariant choice in AParameters.Get("param_events").ToComposite()) { param_events.Add(choice.ToString()); } if (param_events.Count == 0) { throw new NoNullAllowedException("At least one option must be checked."); } // now add parameters to sql parameter list ASQLParameterList.Add(TDbListParameterValue.OdbcListParameterValue("events", OdbcType.BigInt, param_events)); ASQLParameterList.Add(new OdbcParameter("Accepted", OdbcType.Bit) { Value = AParameters.Get("param_status_accepted").ToBool() }); ASQLParameterList.Add(new OdbcParameter("Hold", OdbcType.Bit) { Value = AParameters.Get("param_status_hold").ToBool() }); ASQLParameterList.Add(new OdbcParameter("Enquiry", OdbcType.Bit) { Value = AParameters.Get("param_status_enquiry").ToBool() }); ASQLParameterList.Add(new OdbcParameter("Cancelled", OdbcType.Bit) { Value = AParameters.Get("param_status_cancelled").ToBool() }); ASQLParameterList.Add(new OdbcParameter("Rejected", OdbcType.Bit) { Value = AParameters.Get("param_status_rejected").ToBool() }); ASQLParameterList.Add(new OdbcParameter("Active", OdbcType.Bit) { Value = AParameters.Get("param_active").ToBool() }); ASQLParameterList.Add(new OdbcParameter("Exclude_no_soliciations", OdbcType.Bit) { Value = AParameters.Get("param_exclude_no_solicitations").ToBool() }); }
/// <summary> /// This stores the resultlist into a datatable. /// Mainly used for sending the resultlist over a remote connection /// </summary> /// <returns>the datatable that contains a collection of results /// </returns> public System.Data.DataTable ToDataTable(TParameterList parameters) { int maxColumn = 0; for (int i = 0; i < MaxDisplayColumns; i++) { if ((!parameters.Get("ColumnWidth", i, -1, eParameterFit.eBestFit).IsNil())) { maxColumn = i + 1; } } DataTable ReturnValue = new System.Data.DataTable(); ReturnValue.Columns.Add(new System.Data.DataColumn("masterRow", typeof(System.Int32))); ReturnValue.Columns.Add(new System.Data.DataColumn("childRow", typeof(System.Int32))); ReturnValue.Columns.Add(new System.Data.DataColumn("display", typeof(bool))); ReturnValue.Columns.Add(new System.Data.DataColumn("depth", typeof(System.Int32))); ReturnValue.Columns.Add(new System.Data.DataColumn("code", typeof(String))); ReturnValue.Columns.Add(new System.Data.DataColumn("condition", typeof(String))); ReturnValue.Columns.Add(new System.Data.DataColumn("debit_credit_indicator", typeof(bool))); ReturnValue.Columns.Add(new System.Data.DataColumn("header1", typeof(String))); ReturnValue.Columns.Add(new System.Data.DataColumn("header2", typeof(String))); ReturnValue.Columns.Add(new System.Data.DataColumn("descr1", typeof(String))); ReturnValue.Columns.Add(new System.Data.DataColumn("descr2", typeof(String))); ReturnValue.Columns.Add(new System.Data.DataColumn("maxcolumn", typeof(System.Int32))); for (int i = 0; i < maxColumn; i++) { ReturnValue.Columns.Add(new System.Data.DataColumn("column" + i.ToString(), typeof(String))); } foreach (TResult element in results) { DataRow row = ReturnValue.NewRow(); row["maxcolumn"] = (System.Object)maxColumn; row["masterRow"] = (System.Object)element.masterRow; row["childRow"] = (System.Object)element.childRow; row["display"] = (System.Object)element.display; row["depth"] = (System.Object)element.depth; row["code"] = element.code; row["condition"] = element.condition; row["debit_credit_indicator"] = (System.Object)element.debit_credit_indicator; row["header1"] = element.header[0].EncodeToString(); row["header2"] = element.header[1].EncodeToString(); row["descr1"] = element.descr[0].EncodeToString(); row["descr2"] = element.descr[1].EncodeToString(); for (int i = 0; i < maxColumn; i++) { row["column" + i.ToString()] = element.column[i].EncodeToString(); } ReturnValue.Rows.InsertAt(row, ReturnValue.Rows.Count); } return(ReturnValue); }
/// <summary> /// find the next row on the same level /// </summary> /// <param name="currentRow"></param> /// <returns></returns> protected TResult FindNextSibling(TResult currentRow) { TResult ReturnValue; Int32 i; Int32 masterRow; Int32 childRow; // if currentRow is nil assume the root (needed to find first printable element) if (currentRow != null) { masterRow = currentRow.masterRow; childRow = currentRow.childRow; } else { masterRow = 0; childRow = 0; } ReturnValue = null; foreach (TResult row in FResults) { if (row.masterRow == masterRow) { if ((row.childRow > childRow) && ((ReturnValue == null) || (row.childRow < ReturnValue.childRow))) { if ((row.depth == 1) && (FLowestLevel != 1) && (FParameters.GetOrDefault("HasSubReports", -1, new TVariant(false)).ToBool() == true)) { // reset the FLowestLevel, because this is basically a new report (several lowerlevelreports in main level) // todo: be careful: some reports have several rows in the main level, I just assumed one total for the finance reports // it works now for reports with just one row depth, for others this still needs to be sorted properly. another parameter? FLowestLevel = FResultList.GetDeepestVisibleLevel(row.childRow); FPrinter.LineSpaceFeed(eFont.eDefaultFont); FPrinter.DrawLine(FPrinter.LeftMargin, FPrinter.RightMargin, eLinePosition.eAbove, eFont.eDefaultBoldFont); FPrinter.LineSpaceFeed(eFont.eDefaultFont); FParameters.Add("CurrentSubReport", FParameters.Get("CurrentSubReport").ToInt() + 1); for (i = 0; i <= FLowestLevel; i += 1) { FNextElementLineToPrint.Add(eStageElementPrinting.eHeader); } FNextElementLineToPrint[FLowestLevel] = eStageElementPrinting.eDetails; } ReturnValue = row; FNextElementLineToPrint[ReturnValue.depth] = eStageElementPrinting.eHeader; } } } return(ReturnValue); }
private void SetControlsManual(TParameterList AParameters) { String CountryCode = AParameters.Get("param_country_code").ToString(); if ((CountryCode.Length > 0) && (CountryCode != "*")) { cmbCountry.SetSelectedString(CountryCode); } }
/// <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> /// 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> /// 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> /// retrieve parameters from client sent in AParameters and build up AParameterList to run SQL query /// </summary> /// <param name="AParameters"></param> /// <param name="ASqlStmt"></param> /// <param name="ASQLParameterList"></param> protected override void RetrieveParameters(TParameterList AParameters, ref string ASqlStmt, ref List <OdbcParameter>ASQLParameterList) { bool AllLedgers; ICollection <String>param_ledgers; AllLedgers = AParameters.Get("param_all_ledgers").ToBool(); // now add parameters to sql parameter list ASQLParameterList.Add(new OdbcParameter("param_all_ledgers", OdbcType.Bit) { Value = AllLedgers }); if (AllLedgers) { // Add dummy value in case of an empty list so sql query does not fail. // This value is irrelevant in this case. ASQLParameterList.Add(new OdbcParameter("ledgers", OdbcType.BigInt) { Value = 0 }); } else { // prepare list of ledgers param_ledgers = AParameters.Get("param_ledgers").ToString().Split(new Char[] { ',', }); ASQLParameterList.Add(TDbListParameterValue.OdbcListParameterValue("ledgers", OdbcType.BigInt, param_ledgers)); } ASQLParameterList.Add(new OdbcParameter("param_date_from_unset", OdbcType.Bit) { Value = AParameters.Get("param_date_from").IsZeroOrNull() }); ASQLParameterList.Add(new OdbcParameter("param_date_from", OdbcType.Date) { Value = AParameters.Get("param_date_from").ToDate() }); ASQLParameterList.Add(new OdbcParameter("param_date_to_unset", OdbcType.Bit) { Value = AParameters.Get("param_date_to").IsZeroOrNull() }); ASQLParameterList.Add(new OdbcParameter("param_date_to", OdbcType.Date) { Value = AParameters.Get("param_date_to").ToDate() }); }
/// <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 method needs to be implemented by extracts that can't follow the default processing with just /// one query. /// </summary> /// <param name="AParameters"></param> /// <param name="ATransaction"></param> /// <param name="AExtractId"></param> protected override bool RunSpecialTreatment(TParameterList AParameters, TDBTransaction ATransaction, out int AExtractId) { AExtractId = -1; if (AParameters.Get("param_sending_receiving").ToString() == "ReceivingField") { return ProcessReceivingFields(AParameters, ATransaction, out AExtractId); } else if (AParameters.Get("param_sending_receiving").ToString() == "SendingField") { return ProcessSendingFields(AParameters, ATransaction, out AExtractId); } else { return false; } }
/// <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> /// This method needs to be implemented by extracts that can't follow the default processing with just one query. /// </summary> /// <param name="AParameters"></param> /// <param name="ATransaction"></param> /// <param name="AExtractId"></param> protected override bool RunSpecialTreatment(TParameterList AParameters, TDBTransaction ATransaction, out int AExtractId) { Boolean ReturnValue = false; Int32 ExtractId = -1; TDBTransaction Transaction = null; DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.Serializable, ref Transaction, delegate { DataTable giftdetails; string SqlStmt = TDataBase.ReadSqlFile("Gift.Queries.ExtractDonorByAmount.sql"); List <OdbcParameter>SqlParameterList = new List <OdbcParameter>(); bool AddressFilterAdded; DataTable partnerkeys = new DataTable(); // call to derived class to retrieve parameters specific for extract RetrieveParameters(AParameters, ref SqlStmt, ref SqlParameterList); // add address filter information to sql statement and parameter list AddressFilterAdded = AddAddressFilter(AParameters, ref SqlStmt, ref SqlParameterList); // now run the database query TLogging.Log("Getting the data from the database...", TLoggingType.ToStatusBar); giftdetails = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "partners", Transaction, SqlParameterList.ToArray()); // 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; } TLogging.Log("Preparing the extract...", TLoggingType.ToStatusBar); // With the result of the original query process the data and identify the partner keys for // the extract. partnerkeys.Columns.Add("0", typeof(Int64)); partnerkeys.Columns.Add("1", typeof(string)); partnerkeys.Columns.Add("p_site_key_n", typeof(Int64)); partnerkeys.Columns.Add("p_location_key_i", typeof(Int32)); ProcessGiftDetailRecords(giftdetails, AddressFilterAdded, AParameters, ref partnerkeys); // filter data by postcode (if applicable) ExtractQueryBase.PostcodeFilter(ref partnerkeys, ref AddressFilterAdded, AParameters, Transaction); // 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); }); AExtractId = ExtractId; return ReturnValue; } // Run Special Treatment
/// <summary> /// Post processing of db query that retrieved gift detail records to create a list of partner keys /// </summary> /// <param name="AGiftDetails"></param> /// <param name="AAddressFilterAdded"></param> /// <param name="AParameters"></param> /// <param name="APartnerKeys"></param> private void ProcessGiftDetailRecords(DataTable AGiftDetails, bool AAddressFilterAdded, TParameterList AParameters, ref DataTable APartnerKeys) { int PartnerKeyColumn = 0; int LedgerNumberColumn = 2; int BatchNumberColumn = 3; int GiftTransactionNumberColumn = 4; int FirstTimeGiftColumn = 5; int GiftDetailNumberColumn = 6; int GiftAmountColumn = 7; int GiftAmountInternationalColumn = 8; int SiteKeyColumn = -1; int LocationKeyColumn = -1; Int32 LedgerNumber = -1; Int32 BatchNumber = -1; Int32 GiftTransactionNumber = -1; Int32 GiftDetailNumber = -1; Int64 PartnerKey = -1; Int64 SiteKey = 0; Int32 LocationKey = 0; Int32 PreviousLedgerNumber = -1; Int32 PreviousBatchNumber = -1; Int32 PreviousGiftTransactionNumber = -1; Int32 PreviousGiftDetailNumber = -1; Int64 PreviousPartnerKey = -1; Int64 PreviousSiteKey = 0; Int32 PreviousLocationKey = 0; int CountGifts = 0; decimal GiftAmountTotal = 0; decimal GiftAmount; decimal GiftAmountInternationalTotal = 0; decimal GiftAmountInternational; bool ReversedGift = false; Decimal MinAmount = 0; Decimal MaxAmount = 999999999.99M; bool AmountPerSingleGift = false; Int32 MinNumberOfGifts = 0; Int32 MaxNumberOfGifts = 999999; bool BaseCurrency = true; bool NewDonor = false; bool NewDonorsOnly = false; bool NextGift = false; bool NextPartner = false; // initialize parameters needed from parameter list if (!AParameters.Get("param_new_donors_only").IsNil()) { NewDonorsOnly = AParameters.Get("param_new_donors_only").ToBool(); } if (!AParameters.Get("param_min_gift_amount").IsNil()) { MinAmount = AParameters.Get("param_min_gift_amount").ToDecimal(); } if (!AParameters.Get("param_max_gift_amount").IsNil()) { MaxAmount = AParameters.Get("param_max_gift_amount").ToDecimal(); } if (!AParameters.Get("param_amount_per_single_gift").IsNil()) { AmountPerSingleGift = AParameters.Get("param_amount_per_single_gift").ToBool(); } if (!AParameters.Get("param_min_number_gifts").IsNil()) { MinNumberOfGifts = AParameters.Get("param_min_number_gifts").ToInt32(); } if (!AParameters.Get("param_max_number_gifts").IsNil()) { MaxNumberOfGifts = AParameters.Get("param_max_number_gifts").ToInt32(); } if (!AParameters.Get("param_currency").IsNil() && (AParameters.Get("param_currency").ToString() == "InternationalCurrency")) { BaseCurrency = false; } // only set columns for address related information if address filter was added if (AAddressFilterAdded) { SiteKeyColumn = 9; LocationKeyColumn = 10; } // now start processing rows retrieved from database to filter according to criteria foreach (DataRow giftDetailRow in AGiftDetails.Rows) { LedgerNumber = Convert.ToInt32(giftDetailRow[LedgerNumberColumn]); BatchNumber = Convert.ToInt32(giftDetailRow[BatchNumberColumn]); GiftTransactionNumber = Convert.ToInt32(giftDetailRow[GiftTransactionNumberColumn]); GiftDetailNumber = Convert.ToInt32(giftDetailRow[GiftDetailNumberColumn]); PartnerKey = Convert.ToInt64(giftDetailRow[PartnerKeyColumn]); if (AAddressFilterAdded) { SiteKey = Convert.ToInt64(giftDetailRow[SiteKeyColumn]); LocationKey = Convert.ToInt32(giftDetailRow[LocationKeyColumn]); // if key field values have not changed then this record is the same with just // a different location key --> skip this record as otherwise gift amounts and // numbers of gifts would be wrongly multiplied. if ((LedgerNumber == PreviousLedgerNumber) && (BatchNumber == PreviousBatchNumber) && (GiftTransactionNumber == PreviousGiftTransactionNumber) && (GiftDetailNumber == PreviousGiftDetailNumber) && (PartnerKey == PreviousPartnerKey)) { continue; } } // check for new partner record (unless this is the first record) if (PreviousPartnerKey != -1) { if (PartnerKey != PreviousPartnerKey) { NextPartner = true; } } // check for new gift record (unless this is the first record) if (PreviousGiftTransactionNumber != -1) { if (!((LedgerNumber == PreviousLedgerNumber) && (BatchNumber == PreviousBatchNumber) && (GiftTransactionNumber == PreviousGiftTransactionNumber))) { NextGift = true; } } // new gift: check for criteria and possibly increase gift counter if (NextGift) { if (AmountPerSingleGift) { if ((BaseCurrency && (Math.Abs(GiftAmountTotal) >= MinAmount) && (Math.Abs(GiftAmountTotal) <= MaxAmount)) || (!BaseCurrency && (Math.Abs(GiftAmountInternationalTotal) >= MinAmount) && (Math.Abs(GiftAmountInternationalTotal) <= MaxAmount))) { if (ReversedGift) { CountGifts = CountGifts - 1; } else { CountGifts = CountGifts + 1; } } GiftAmountTotal = 0; GiftAmountInternationalTotal = 0; } else { if (ReversedGift) { CountGifts = CountGifts - 1; } else { CountGifts = CountGifts + 1; } } // reset variable for reversed gift since a new gift record is starting ReversedGift = false; } if (NextPartner) { // different partner than the record before: check if partner meets criteria for extract // (in case of single gift amounts don't check amounts again) if ((!AmountPerSingleGift && ((BaseCurrency && ((Math.Abs(GiftAmountTotal) < MinAmount) || (Math.Abs(GiftAmountTotal) > MaxAmount))) || (!BaseCurrency && ((Math.Abs(GiftAmountInternationalTotal) < MinAmount) || (Math.Abs(GiftAmountInternationalTotal) > MaxAmount))))) || (CountGifts < MinNumberOfGifts) || (CountGifts > MaxNumberOfGifts) || !NewDonor) { // skip partner as criteria are not fulfilled } else { // add partner to extract APartnerKeys.Rows.Add(PreviousPartnerKey, "", PreviousSiteKey, PreviousLocationKey); } } if (NextPartner) { // reset variables needed for calculation NewDonor = false; CountGifts = 0; GiftAmountTotal = 0; GiftAmountInternationalTotal = 0; } if (Convert.ToBoolean(giftDetailRow[FirstTimeGiftColumn]) || !NewDonorsOnly) { NewDonor = true; } GiftAmount = Convert.ToInt32(giftDetailRow[GiftAmountColumn]); GiftAmountTotal = GiftAmountTotal + GiftAmount; GiftAmountInternational = Convert.ToInt32(giftDetailRow[GiftAmountInternationalColumn]); GiftAmountInternationalTotal = GiftAmountInternationalTotal + GiftAmountInternational; if (GiftAmount < 0) { ReversedGift = true; } // prepare for next round of loop PreviousLedgerNumber = LedgerNumber; PreviousBatchNumber = BatchNumber; PreviousGiftTransactionNumber = GiftTransactionNumber; PreviousGiftDetailNumber = GiftDetailNumber; PreviousPartnerKey = PartnerKey; PreviousSiteKey = SiteKey; PreviousLocationKey = LocationKey; NextPartner = false; NextGift = false; } // process last record after loop through all records has finished // (in case of single gift amounts don't check amounts again) if ((!AmountPerSingleGift && ((BaseCurrency && ((Math.Abs(GiftAmountTotal) < MinAmount) || (Math.Abs(GiftAmountTotal) > MaxAmount))) || (!BaseCurrency && ((Math.Abs(GiftAmountInternationalTotal) < MinAmount) || (Math.Abs(GiftAmountInternationalTotal) > MaxAmount))))) || (CountGifts < MinNumberOfGifts) || (CountGifts > MaxNumberOfGifts) || !NewDonor) { // skip partner as criteria are not fulfilled } else { // add partner to extract APartnerKeys.Rows.Add(PreviousPartnerKey, "", PreviousSiteKey, PreviousLocationKey); } }
} // Run Special Treatment /// <summary> /// Retrieve parameters from client sent in AParameters and build up AParameterList to run SQL query /// </summary> /// <param name="AParameters"></param> /// <param name="ASqlStmt"></param> /// <param name="ASQLParameterList"></param> protected override void RetrieveParameters(TParameterList AParameters, ref string ASqlStmt, ref List <OdbcParameter>ASQLParameterList) { ASQLParameterList.Add(new OdbcParameter("param_date_from_unset", OdbcType.Bit) { Value = AParameters.Get("param_date_from").IsZeroOrNull() }); ASQLParameterList.Add(new OdbcParameter("param_date_from", OdbcType.Date) { Value = AParameters.Get("param_date_from").ToDate() }); ASQLParameterList.Add(new OdbcParameter("param_date_to_unset", OdbcType.Bit) { Value = AParameters.Get("param_date_to").IsZeroOrNull() }); ASQLParameterList.Add(new OdbcParameter("param_date_to", OdbcType.Date) { Value = AParameters.Get("param_date_to").ToDate() }); ASQLParameterList.Add(new OdbcParameter("param_new_donors_only", OdbcType.Bit) { Value = AParameters.Get("param_new_donors_only").ToBool() }); ASQLParameterList.Add(new OdbcParameter("param_active", OdbcType.Bit) { Value = AParameters.Get("param_active").ToBool() }); ASQLParameterList.Add(new OdbcParameter("param_families_only", OdbcType.Bit) { Value = AParameters.Get("param_families_only").ToBool() }); ASQLParameterList.Add(new OdbcParameter("param_exclude_no_solicitations", OdbcType.Bit) { Value = AParameters.Get("param_exclude_no_solicitations").ToBool() }); }
/// <summary> /// This will check, if the set of settings with the given name is already existing /// and if it is a system settings; ie. it is provided by the organisation/OpenPetra.org and should not be overwritten. /// all settings in the user directory are non system settings, all in the {app}/reports30/Settings are system settings /// </summary> /// <returns>void</returns> public bool IsSystemSettings(String ASettingsName) { bool ReturnValue = false; // need to switch back to the application directory, because the path names might be relative to the application Environment.CurrentDirectory = FApplicationDirectory; String Filename = FSettingsDirectory + FReportName + System.IO.Path.DirectorySeparatorChar + ASettingsName + ".xml"; if (System.IO.File.Exists(Filename)) { TParameterList Parameters = new TParameterList(); try { Parameters.Load(Filename); if (Parameters.Get("systemsettings").ToBool()) { ReturnValue = true; } } finally { } } 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> /// <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; } }
private void SetControlsManual(TParameterList AParameters) { txtRecipient.Text = AParameters.Get("param_recipient_key").ToString(); txtExtract.Text = AParameters.Get("param_extract_name").ToString(); }
private void SetControlsManual(TParameterList AParameters) { clbDocuments.SetCheckedStringList(AParameters.Get("param_doctype").ToString()); }
private void SetControlsManual(TParameterList AParameters) { txtDonor.Text = AParameters.Get("param_donorkey").ToString(); txtExtract.Text = AParameters.Get("param_extract_name").ToString(); }
/// <summary> /// Sets the selected values in the controls, using the parameters loaded from a file /// /// </summary> /// <param name="AParameters"></param> /// <returns>void</returns> public void SetControls(TParameterList AParameters) { System.Int32 MaxDisplayColumns; MaxDisplayColumns = TUC_ColumnHelper.SetControls(ref FColumnParameters, ref AParameters); /* copy values for columns to the current set of parameters */ FPetraUtilsObject.FMaxDisplayColumns = MaxDisplayColumns; chkYTD.Checked = AParameters.Get("param_ytd").ToBool(); FillColumnGrid(); }
/// <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; }
private void grdSelection_SetControls(TParameterList AParameters) { FSelectionTable.Rows.Clear(); int NumAttributes = AParameters.Get("param_number_of_contact_attributes").ToInt32(); for (int Counter = 0; Counter < NumAttributes; ++Counter) { PContactAttributeDetailRow Row = FSelectionTable.NewRowTyped(true); Row.ContactAttributeCode = AParameters.Get("param_contact_attribute_attribute_" + Counter.ToString()).ToString(); Row.ContactAttrDetailCode = AParameters.Get("param_contact_attribute_detail_" + Counter.ToString()).ToString(); Row.ContactAttrDetailDescr = AParameters.Get("param_contact_attribute_description_" + Counter.ToString()).ToString(); FSelectionTable.Rows.Add(Row); } }
/// <summary> /// Filter data by postcode (if applicable) /// </summary> /// <param name="APartnerkeys"></param> /// <param name="AAddressFilterAdded"></param> /// <param name="AParameters"></param> /// <param name="ATransaction"></param> public static void PostcodeFilter(ref DataTable APartnerkeys, ref bool AAddressFilterAdded, TParameterList AParameters, TDBTransaction ATransaction) { // if filter exists if ((AParameters.Exists("param_region") && !string.IsNullOrEmpty(AParameters.Get("param_region").ToString())) || (AParameters.Exists("param_postcode_from") && !string.IsNullOrEmpty(AParameters.Get("param_postcode_from").ToString())) || (AParameters.Exists("param_postcode_to") && !string.IsNullOrEmpty(AParameters.Get("param_postcode_to").ToString()))) { DataTable partnerkeysCopy = APartnerkeys.Copy(); int i = 0; foreach (DataRow Row in partnerkeysCopy.Rows) { // get postcode for current partner's location PLocationRow LocationRow = (PLocationRow)PLocationAccess.LoadByPrimaryKey( Convert.ToInt64(Row["p_site_key_n"]), Convert.ToInt32(Row["p_location_key_i"]), ATransaction)[0]; if (!AddressMeetsPostCodeCriteriaOrEmpty(LocationRow.PostalCode, AParameters.Get("param_region").ToString(), AParameters.Get("param_postcode_from").ToString(), AParameters.Get("param_postcode_to").ToString())) { // remove record if it is excluded by the filter APartnerkeys.Rows.RemoveAt(i); } else { i++; } } AAddressFilterAdded = true; } }
/// <summary> /// extend query statement and query parameter list by address filter information given in extract parameters /// </summary> /// <param name="AParameters"></param> /// <param name="ASqlStmt"></param> /// <param name="AOdbcParameterList"></param> /// <returns>true if address tables and fields were added</returns> protected static bool AddAddressFilter(TParameterList AParameters, ref string ASqlStmt, ref List <OdbcParameter>AOdbcParameterList) { string WhereClause = ""; string TableNames = ""; string FieldNames = ""; string OrderByClause = ""; string StringValue; DateTime DateValue; bool LocationTableNeeded = false; bool PartnerLocationTableNeeded = false; bool AddressFilterAdded = false; // add check for mailing addresses only if (AParameters.Exists("param_mailing_addresses_only")) { if (AParameters.Get("param_mailing_addresses_only").ToBool()) { WhereClause = WhereClause + " AND pub_p_partner_location.p_send_mail_l"; PartnerLocationTableNeeded = true; } } // add city statement (allow any city that begins with search string) if (AParameters.Exists("param_city")) { StringValue = AParameters.Get("param_city").ToString(); if ((StringValue.Trim().Length > 0) && (StringValue != "*")) { AOdbcParameterList.Add(new OdbcParameter("param_city", OdbcType.VarChar) { Value = StringValue + "%" }); WhereClause = WhereClause + " AND pub_p_location.p_city_c LIKE ?"; LocationTableNeeded = true; } } // add county statement (allow any county that begins with search string) if (AParameters.Exists("param_county")) { StringValue = AParameters.Get("param_county").ToString(); if ((StringValue.Trim().Length > 0) && (StringValue != "*")) { AOdbcParameterList.Add(new OdbcParameter("param_county", OdbcType.VarChar) { Value = StringValue + "%" }); WhereClause = WhereClause + " AND pub_p_location.p_county_c LIKE ?"; LocationTableNeeded = true; } } // add statement for country if (AParameters.Exists("param_country")) { StringValue = AParameters.Get("param_country").ToString(); if (StringValue.Trim().Length > 0) { AOdbcParameterList.Add(new OdbcParameter("param_country", OdbcType.VarChar) { Value = StringValue }); WhereClause = WhereClause + " AND pub_p_location.p_country_code_c = ?"; LocationTableNeeded = true; } } // postcode filter will be applied after the data is obtained if (AParameters.Exists("param_region") || AParameters.Exists("param_postcode_from") || AParameters.Exists("param_postcode_to")) { LocationTableNeeded = true; } // add date clause if address should only be valid at a certain date if (AParameters.Exists("param_only_addresses_valid_on") && (AParameters.Get("param_only_addresses_valid_on").ToBool())) { if (AParameters.Exists("param_address_date_valid_on") && !AParameters.Get("param_address_date_valid_on").IsZeroOrNull()) { DateValue = AParameters.Get("param_address_date_valid_on").ToDate(); } else { // if date not given then use "Today" DateValue = DateTime.Today; } AOdbcParameterList.Add(new OdbcParameter("param_address_date_valid_on_1", OdbcType.Date) { Value = DateValue }); AOdbcParameterList.Add(new OdbcParameter("param_address_date_valid_on_2", OdbcType.Date) { Value = DateValue }); AOdbcParameterList.Add(new OdbcParameter("param_address_date_valid_on_3", OdbcType.Date) { Value = DateValue }); WhereClause = WhereClause + " AND ( ( pub_p_partner_location.p_date_effective_d <= ?" + " AND pub_p_partner_location.p_date_good_until_d IS NULL)" + " OR ( pub_p_partner_location.p_date_effective_d <= ?" + " AND pub_p_partner_location.p_date_good_until_d >= ?))"; LocationTableNeeded = true; } else { // if not valid on certain date then check if date range is filled if (AParameters.Exists("param_address_start_from") && !AParameters.Get("param_address_start_from").IsZeroOrNull()) { AOdbcParameterList.Add(new OdbcParameter("param_address_start_from", OdbcType.Date) { Value = AParameters.Get("param_address_start_from").ToDate() }); WhereClause = WhereClause + " AND pub_p_partner_location.p_date_effective_d >= ?"; LocationTableNeeded = true; } if (AParameters.Exists("param_address_start_to") && !AParameters.Get("param_address_start_to").IsZeroOrNull()) { AOdbcParameterList.Add(new OdbcParameter("param_address_start_to", OdbcType.Date) { Value = AParameters.Get("param_address_start_to").ToDate() }); WhereClause = WhereClause + " AND pub_p_partner_location.p_date_effective_d <= ?"; LocationTableNeeded = true; } if (AParameters.Exists("param_address_end_from") && !AParameters.Get("param_address_end_from").IsZeroOrNull()) { AOdbcParameterList.Add(new OdbcParameter("param_address_end_from", OdbcType.Date) { Value = AParameters.Get("param_address_end_from").ToDate() }); WhereClause = WhereClause + " AND ( pub_p_partner_location.p_date_good_until_d IS NOT NULL" + " AND pub_p_partner_location.p_date_good_until_d >= ?)"; LocationTableNeeded = true; } if (AParameters.Exists("param_address_end_to") && !AParameters.Get("param_address_end_to").IsZeroOrNull()) { AOdbcParameterList.Add(new OdbcParameter("param_address_end_to", OdbcType.Date) { Value = AParameters.Get("param_address_end_to").ToDate() }); WhereClause = WhereClause + " AND ( pub_p_partner_location.p_date_good_until_d IS NOT NULL" + " AND pub_p_partner_location.p_date_good_until_d <= ?)"; LocationTableNeeded = true; } } // add statement for location type if (AParameters.Exists("param_location_type")) { StringValue = AParameters.Get("param_location_type").ToString(); if (StringValue.Trim().Length > 0) { List <String>param_location_type = new List <String>(); foreach (TVariant choice in AParameters.Get("param_location_type").ToComposite()) { param_location_type.Add(choice.ToString()); } AOdbcParameterList.Add(TDbListParameterValue.OdbcListParameterValue("param_location_type", OdbcType.VarChar, param_location_type)); WhereClause = WhereClause + " AND pub_p_partner_location.p_location_type_c IN (?)"; PartnerLocationTableNeeded = true; } } // if location table is needed then automatically partner location table is needed as well if (LocationTableNeeded) { FieldNames = ", pub_p_partner_location.p_site_key_n, pub_p_partner_location.p_location_key_i "; TableNames = TableNames + ", pub_p_location, pub_p_partner_location"; WhereClause = " AND pub_p_partner_location.p_partner_key_n = pub_p_partner.p_partner_key_n" + " AND pub_p_location.p_site_key_n = pub_p_partner_location.p_site_key_n" + " AND pub_p_location.p_location_key_i = pub_p_partner_location.p_location_key_i" + WhereClause; OrderByClause = ", pub_p_partner.p_partner_key_n"; } else if (PartnerLocationTableNeeded) { FieldNames = ", pub_p_partner_location.p_site_key_n, pub_p_partner_location.p_location_key_i "; TableNames = TableNames + ", pub_p_partner_location"; WhereClause = " AND pub_p_partner_location.p_partner_key_n = pub_p_partner.p_partner_key_n" + WhereClause; OrderByClause = ", pub_p_partner.p_partner_key_n"; } // Set information if address filter was set. It is not enough to just check if extra fields or // clauses were built but filter fields to be replaced also need to exist. if ((ASqlStmt.Contains("##address_filter_fields##") || ASqlStmt.Contains("##address_filter_tables##") || ASqlStmt.Contains("##address_filter_where_clause##") || ASqlStmt.Contains("##address_filter_order_by_clause##")) && ((TableNames.Length > 0) || (WhereClause.Length > 0))) { AddressFilterAdded = true; } else { AddressFilterAdded = false; } ASqlStmt = ASqlStmt.Replace("##address_filter_fields##", FieldNames); ASqlStmt = ASqlStmt.Replace("##address_filter_tables##", TableNames); ASqlStmt = ASqlStmt.Replace("##address_filter_where_clause##", WhereClause); ASqlStmt = ASqlStmt.Replace("##address_filter_order_by_clause##", OrderByClause); return AddressFilterAdded; }
private void SetControlsManual(TParameterList AParameters) { FFromAccountCode = AParameters.Get("param_account_from").ToString(); FToAccountCode = AParameters.Get("param_account_to").ToString(); }
/// <summary> /// This returns the resultlist as lines for a CSV file /// </summary> /// <param name="AParameters"></param> /// <param name="separator">if this has the value FIND_BEST_SEPARATOR, /// then first the parameters will be checked for CSV_separator, and if that parameter does not exist, /// then the CurrentCulture is checked, for the local language settings</param> /// <param name="ADebugging">if true, thent the currency and date values are written encoded, not localized</param> /// <param name="AExportOnlyLowestLevel">if true, only the lowest level of AParameters are exported (level with higest depth) /// otherwise all levels in AParameter are exported</param> /// <returns>the lines to be written to the CSV file</returns> public List <string> WriteCSVInternal(TParameterList AParameters, string separator = "FIND_BEST_SEPARATOR", Boolean ADebugging = false, Boolean AExportOnlyLowestLevel = false) { List <string> lines = new List <string>(); int i; string strLine; ArrayList sortedList; bool display; bool useIndented; TParameterList FormattedParameters; TResultList FormattedResult; // myEncoding: Encoding; // bytes: array of byte; if (separator == "FIND_BEST_SEPARATOR") { if (AParameters.Exists("CSV_separator")) { separator = AParameters.Get("CSV_separator").ToString(); if (separator.ToUpper() == "TAB") { separator = new String((char)9, 1); } else if (separator.ToUpper() == "SPACE") { separator = " "; } } else { separator = CultureInfo.CurrentCulture.TextInfo.ListSeparator; } } if (ADebugging == false) { FormattedParameters = AParameters.ConvertToFormattedStrings("CSV"); FormattedResult = ConvertToFormattedStrings(FormattedParameters, "CSV"); } else { FormattedParameters = AParameters; FormattedResult = this; } // write headings strLine = ""; // for debugging: // strLine = StringHelper.AddCSV(strLine, "masterRow", separator); // strLine = StringHelper.AddCSV(strLine, "childRow", separator); // strLine = StringHelper.AddCSV(strLine, "depth", separator); strLine = StringHelper.AddCSV(strLine, "id", separator); if (FormattedParameters.Exists("ControlSource", ReportingConsts.HEADERPAGELEFT1, -1, eParameterFit.eBestFit)) { strLine = StringHelper.AddCSV(strLine, FormattedParameters.Get("ControlSource", ReportingConsts.HEADERPAGELEFT1, -1, eParameterFit.eBestFit).ToString(), separator); } if (FormattedParameters.Exists("ControlSource", ReportingConsts.HEADERPAGELEFT2, -1, eParameterFit.eBestFit)) { strLine = StringHelper.AddCSV(strLine, FormattedParameters.Get("ControlSource", ReportingConsts.HEADERPAGELEFT2, -1, eParameterFit.eBestFit).ToString(), separator); } if (FormattedParameters.Exists("ControlSource", ReportingConsts.HEADERCOLUMN, -1, eParameterFit.eBestFit)) { strLine = StringHelper.AddCSV(strLine, "header 1", separator); strLine = StringHelper.AddCSV(strLine, "header 0", separator); } useIndented = false; for (i = 0; i <= FormattedParameters.Get("lowestLevel").ToInt(); i++) { if (FormattedParameters.Exists("indented", ReportingConsts.ALLCOLUMNS, i, eParameterFit.eBestFit)) { useIndented = true; } } MaxDisplayColumns = AParameters.Get("MaxDisplayColumns").ToInt32(); for (i = 0; i < MaxDisplayColumns; i++) { if ((!FormattedParameters.Get("ColumnCaption", i, -1, eParameterFit.eBestFit).IsNil())) { strLine = StringHelper.AddCSV(strLine, (FormattedParameters.Get("ColumnCaption", i, -1, eParameterFit.eBestFit).ToString() + ' ' + FormattedParameters.Get("ColumnCaption2", i, -1, eParameterFit.eBestFit).ToString(false) + ' ' + FormattedParameters.Get("ColumnCaption3", i, -1, eParameterFit.eBestFit).ToString( false)).Trim(), separator); if (useIndented) { strLine = StringHelper.AddCSV(strLine, "", separator); } } } lines.Add(strLine); FormattedResult.SortChildren(); sortedList = new ArrayList(); FormattedResult.CreateSortedListByMaster(sortedList, 0); int LowestLevel = -1; if (AExportOnlyLowestLevel) { // find the highest level foreach (TResult element in sortedList) { if (element.depth > LowestLevel) { LowestLevel = element.depth; } } } // write each row to CSV file foreach (TResult element in sortedList) { if (AExportOnlyLowestLevel && (element.depth < LowestLevel)) { continue; } if (element.display) { strLine = ""; // for debugging // strLine = StringHelper.AddCSV(strLine, element.masterRow.ToString(), separator); // strLine = StringHelper.AddCSV(strLine, element.childRow.ToString(), separator); // strLine = StringHelper.AddCSV(strLine, element.depth.ToString(), separator); strLine = StringHelper.AddCSV(strLine, element.code, separator); if (FormattedParameters.Exists("ControlSource", ReportingConsts.HEADERPAGELEFT1, -1, eParameterFit.eBestFit)) { if (ADebugging) { strLine = StringHelper.AddCSV(strLine, element.descr[0].EncodeToString(), separator); } else { strLine = StringHelper.AddCSV(strLine, element.descr[0].ToString(), separator); } } if (FormattedParameters.Exists("ControlSource", ReportingConsts.HEADERPAGELEFT2, -1, eParameterFit.eBestFit)) { if (ADebugging) { strLine = StringHelper.AddCSV(strLine, element.descr[1].EncodeToString(), separator); } else { strLine = StringHelper.AddCSV(strLine, element.descr[1].ToString(), separator); } } if (FormattedParameters.Exists("ControlSource", ReportingConsts.HEADERCOLUMN, -1, eParameterFit.eBestFit)) { if (ADebugging) { strLine = StringHelper.AddCSV(strLine, element.header[1].EncodeToString(), separator); strLine = StringHelper.AddCSV(strLine, element.header[0].EncodeToString(), separator); } else { strLine = StringHelper.AddCSV(strLine, element.header[1].ToString(), separator); strLine = StringHelper.AddCSV(strLine, element.header[0].ToString(), separator); } } /* TODO: try to export in the right codepage, to print umlaut and other special characters correctly * if element.childRow = 7 then * begin * myEncoding := System.Text.Encoding.get_ASCII; * TLogging.Log(Encoding.Default.EncodingName); * TLogging.Log(element.column[0].ToString()); * SetLength(bytes, Encoding.Default.GetByteCount(element.column[0].ToString())); * bytes := Encoding.Default.GetBytes(element.column[0].ToString()); * TLogging.Log(myEncoding.GetChars(bytes)); * // this will still not help with Excel * end; */ display = false; for (i = 0; i <= MaxDisplayColumns - 1; i += 1) { if (FormattedParameters.Get("indented", i, element.depth, eParameterFit.eAllColumnFit).ToBool() == true) { strLine = StringHelper.AddCSV(strLine, "", separator); } if (((element.column[i] != null) && (!element.column[i].IsNil())) || (ADebugging)) { display = true; if (ADebugging) { strLine = StringHelper.AddCSV(strLine, element.column[i].EncodeToString(), separator); } else { strLine = StringHelper.AddCSV(strLine, element.column[i].ToString().Trim(), separator); } } else { strLine = StringHelper.AddCSV(strLine, "", separator); } if ((FormattedParameters.Get("indented", i, element.depth, eParameterFit.eAllColumnFit).ToBool() != true) && useIndented) { strLine = StringHelper.AddCSV(strLine, "", separator); } } if (display) { lines.Add(strLine); } } } sortedList = null; return(lines); }