/// <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>
/// 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);
}
