/// <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()
});
}
/// <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();
}
/// <summary>
/// this function should be overwritten; sets the environment for running the function
/// </summary>
public virtual Boolean FunctionSelector(TRptSituation ASituation, String f, TVariant[] ops, out TVariant AValue)
{
this.situation = ASituation;
parameters = situation.GetParameters();
AValue = null;
return false;
}
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>
/// calculate an extract from a report: all partners who have a Contact Log meeting selected criteria
/// </summary>
/// <param name="AParameters"></param>
/// <param name="AResults"></param>
/// <returns></returns>
public static bool CalculateExtract(TParameterList AParameters, TResultList AResults)
{
string SqlStmt = TDataBase.ReadSqlFile("Partner.Queries.ExtractPartnerByContactLog.sql");
QueryPartnerByContactLog ExtractQuery = new QueryPartnerByContactLog();
return ExtractQuery.CalculateExtractInternal(AParameters, SqlStmt, AResults);
}
/// <summary>
/// Initialisation
/// </summary>
public void InitialiseData(TFrmPetraReportingUtils APetraUtilsObject)
{
FUpdatingRelationColumns = false;
FPetraUtilsObject = APetraUtilsObject;
FColumnParameters = new TParameterList();
FColumnParameters.Add("MaxDisplayColumns", 0);
FPetraUtilsObject.FMaxDisplayColumns = 0;
FDuringApplyOrCancel = false;
FSelectedColumn = -1;
grdColumns.SortableHeaders = false;
grdColumns.SelectionMode = SourceGrid.GridSelectionMode.Column;
grdColumns.AutoStretchColumnsToFitWidth = false;
/* Hook up event that fires when a different row is selected */
grdColumns.Selection.FocusColumnEntered += new ColumnEventHandler(this.GrdColumns_FocusColumnEntered);
grdColumns.Selection.CellLostFocus += new ChangeActivePositionEventHandler(this.GrdColumns_CellLostFocus);
grdColumns.Selection.CellGotFocus += new ChangeActivePositionEventHandler(this.GrdColumns_CellGotFocus);
btnApply.Enabled = false;
btnCancel.Enabled = false;
rbtFromGL.Checked = true;
rbtFromGL.Enabled = false;
rbtCalculateExisting.Enabled = false;
cmbYearSelection.Enabled = false;
chkYTD.Enabled = false;
clbLedger.Enabled = false;
}
/// <summary>
/// add the parameters from another list, overwriting existing values, but not deleting parameters as Copy does
/// </summary>
/// <param name="AOtherList"></param>
public void Add(TParameterList AOtherList)
{
foreach (TParameter element in AOtherList.Fparameters)
{
Add(element.name, element.value, element.column, element.level);
}
}
/// <summary>
/// This stores the resultlist into a CSV file.
/// </summary>
/// <param name="AParameters"></param>
/// <param name="csvfilename"></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>true for success</returns>
public bool WriteCSV(TParameterList AParameters,
string csvfilename,
string separator = "FIND_BEST_SEPARATOR",
Boolean ADebugging = false,
Boolean AExportOnlyLowestLevel = false)
{
StreamWriter csvStream;
try
{
// don't append; use the local encoding, e.g. to support Umlauts
csvStream = new StreamWriter(csvfilename, false, System.Text.Encoding.Default);
}
catch (System.Exception ex)
{
ErrorStatus = ex.Message;
return(false);
}
List <string> lines = WriteCSVInternal(AParameters, separator, ADebugging, AExportOnlyLowestLevel);
foreach (string line in lines)
{
csvStream.WriteLine(line);
}
csvStream.Close();
return(true);
}
/// <summary>
/// Used for passing a Partner's partner key to the screen before the screen is actually shown.
/// </summary>
/// <param name="APartnerKey">PartnerKey of the Partner to print.</param>
public void SetParameters(Int64 APartnerKey)
{
TParameterList Parameters = new TParameterList();
Parameters.Add("param_selection", "one partner");
Parameters.Add("param_partnerkey", APartnerKey);
this.ucoPartnerSelection.SetControls(Parameters);
}
/// <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 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>
/// Copy Constructor
/// creates a copy of another ParameterList;
/// this is required to be able to print and export to CSV with the correctly formatted dates
///
/// </summary>
/// <returns>void</returns>
public TParameterList(TParameterList copy)
{
Fparameters = new ArrayList();
foreach (TParameter p in copy.Fparameters)
{
Fparameters.Add(new TParameter(p));
}
}
/// <summary>
/// calculate an extract from a report: all partners living in a given city
/// </summary>
/// <param name="AParameters"></param>
/// <param name="AResults"></param>
/// <param name="AExtractId"></param>
/// <returns></returns>
public static bool CalculateExtract(TParameterList AParameters, TResultList AResults, out int AExtractId)
{
string SqlStmt = TDataBase.ReadSqlFile("Partner.Queries.ExtractFamilyMembers.sql");
// create a new object of this class and control extract calculation from base class
QueryFamilyMembersExtract ExtractQuery = new QueryFamilyMembersExtract();
return ExtractQuery.CalculateExtractInternal(AParameters, SqlStmt, AResults, out AExtractId);
}
/// <summary>
/// calculate an extract from a report: all partners living in a given city
/// </summary>
/// <param name="AParameters"></param>
/// <param name="AResults"></param>
/// <returns></returns>
public static bool CalculateExtract(TParameterList AParameters, TResultList AResults)
{
string SqlStmt = TDataBase.ReadSqlFile("Partner.Queries.ExtractByPartnerCity.sql");
// create a new object of this class and control extract calculation from base class
QueryPartnerByCity ExtractQuery = new QueryPartnerByCity();
return ExtractQuery.CalculateExtractInternal(AParameters, SqlStmt, AResults);
}
/// <summary>
/// calculate an extract from a report: all recipient that have given to particular fields (ledgers)
/// </summary>
/// <param name="AParameters"></param>
/// <param name="AResults"></param>
/// <returns></returns>
public static bool CalculateExtract(TParameterList AParameters, TResultList AResults)
{
string SqlStmt = TDataBase.ReadSqlFile("Gift.Queries.ExtractRecipientByField.sql");
// create a new object of this class and control extract calculation from base class
QueryRecipientByField ExtractQuery = new QueryRecipientByField();
return ExtractQuery.CalculateExtractInternal(AParameters, SqlStmt, AResults);
}
private void SetControlsManual(TParameterList AParameters)
{
String CountryCode = AParameters.Get("param_country_code").ToString();
if ((CountryCode.Length > 0)
&& (CountryCode != "*"))
{
cmbCountry.SetSelectedString(CountryCode);
}
}
/// <summary>
/// calculate an extract from a report: all donors that have given to particular fields (ledgers)
/// </summary>
/// <param name="AParameters"></param>
/// <param name="AResults"></param>
/// <returns></returns>
public static bool CalculateExtract(TParameterList AParameters, TResultList AResults)
{
// Sql statements will be initialized later on in special treatment
string SqlStmt = "";
// create a new object of this class and control extract calculation from base class
QueryDonorByAmount ExtractQuery = new QueryDonorByAmount();
return ExtractQuery.CalculateExtractInternal(AParameters, SqlStmt, AResults);
}
/// <summary>
/// constructor
/// </summary>
/// <param name="AResult"></param>
/// <param name="AParameters"></param>
/// <param name="APrinter"></param>
public TReportPrinterCommon(TResultList AResult, TParameterList AParameters, TPrinter APrinter)
{
// go through all results and parameters and replace the unformatted and encoded date
// the whole point is to format the dates differently, depending on the output (printer vs. CSV)
FParameters = AParameters.ConvertToFormattedStrings("Localized");
FResultList = AResult.ConvertToFormattedStrings(FParameters, "Localized");
FResults = FResultList.GetResults();
FLowestLevel = FParameters.Get("lowestLevel").ToInt();
FTimePrinted = DateTime.Now;
FPrinter = APrinter;
}
/// <summary>
/// constructor
/// </summary>
/// <param name="parameters"></param>
/// <param name="results"></param>
/// <param name="reportStore"></param>
/// <param name="report"></param>
/// <param name="dataDB"></param>
/// <param name="depth"></param>
/// <param name="column"></param>
/// <param name="lineId"></param>
/// <param name="parentRowId"></param>
public TRptEvaluator(TParameterList parameters,
TResultList results,
TReportStore reportStore,
TRptReport report,
TDataBase dataDB,
int depth,
int column,
int lineId,
int parentRowId)
: base(parameters, results, reportStore, report, dataDB, depth, column, lineId, parentRowId)
{
}
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>
/// This stores the resultlist and parameterlist into a binary file (using the Datatable conversion);
/// This can be used for debugging the printing, and saving time on calculating the report by reusing previous results
///
/// </summary>
/// <returns>void</returns>
public void WriteBinaryFile(TParameterList AParameters, String AFilename)
{
DataTable dt = ToDataTable(AParameters);
FileStream fs = new FileStream(AFilename, FileMode.Create);
BinaryFormatter bf = new BinaryFormatter();
bf.Serialize(fs, dt);
dt = AParameters.ToDataTable();
bf.Serialize(fs, dt);
fs.Close();
}
private void RunOnceOnActivationManual()
{
if (CalledFromExtracts)
{
tabReportSettings.Controls.Remove(tpgColumns);
}
var addressSettings = new TParameterList();
addressSettings.Add("param_active", true);
addressSettings.Add("param_mailing_addresses_only", true);
addressSettings.Add("param_families_only", false);
addressSettings.Add("param_exclude_no_solicitations", true);
ucoChkFilter.SetControls(addressSettings);
}
/// <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>
public TGridPreview(Form APreviewForm,
TFrmPetraUtils APetraUtilsObject,
TPreviewDelegate APreviewDelegate,
TResultList AResultList,
TParameterList AParameters)
{
results = AResultList.ConvertToFormattedStrings(AParameters);
FOrigParameters = AParameters;
parameters = AParameters.ConvertToFormattedStrings();
FPreviewForm = APreviewForm;
FPetraUtilsObject = APetraUtilsObject;
FPreviewDelegate = APreviewDelegate;
FGenerateReportThread = null;
}
public void TestPartnerBySpecialTypes()
{
CommonNUnitFunctions.ResetDatabase();
string testFile = "../../csharp/ICT/Testing/lib/MPartner/server/Reporting/TestData/PartnerBySpecialTypes.xml";
TParameterList SpecificParameters = new TParameterList();
SpecificParameters.Add("param_address_date_valid_on", new TVariant(new DateTime(DateTime.Today.Year, 1, 1)));
SpecificParameters.Add("param_explicit_specialtypes", new TVariant("LEDGER"));
TReportTestingTools.CalculateReport(testFile, SpecificParameters);
TReportTestingTools.TestResult(testFile);
}
/// <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>
/// This loads the resultlist and parameterlist from a binary file (using the Datatable conversion);
/// This can be used for debugging the printing, and saving time on calculating the report by reusing previous results
///
/// </summary>
/// <returns>void</returns>
public void ReadBinaryFile(String AFilename, out TParameterList AParameters)
{
FileStream fs = new FileStream(AFilename, FileMode.Open);
BinaryFormatter bf = new BinaryFormatter();
DataTable dt = (DataTable)bf.Deserialize(fs);
results = new ArrayList();
LoadFromDataTable(dt);
dt = (DataTable)bf.Deserialize(fs);
AParameters = new TParameterList();
AParameters.LoadFromDataTable(dt);
fs.Close();
}
/// <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>
/// 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>
/// This formats the dates for different output, for example printing
/// </summary>
/// <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 parameters, with the correct formatting
/// </returns>
public TParameterList ConvertToFormattedStrings(String AOutputType)
{
TParameterList ReturnValue;
ReturnValue = new TParameterList(this);
foreach (TParameter p in ReturnValue.Fparameters)
{
if (p.value.TypeVariant != eVariantTypes.eString)
{
p.value = new TVariant(p.value.ToFormattedString("", AOutputType));
}
}
return(ReturnValue);
}
/// <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>
/// 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>
/// 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 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>
/// 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>
/// add all parameters that do not have an equivalent in this parameter list
/// </summary>
public void CopyMissing(TParameterList AOtherList)
{
foreach (TParameter param in AOtherList.Fparameters)
{
/*
* Do not use ParameterList.Exists() because that
* function should be renamed to
* ParameterList.ExistsOrEmpty(). We only actually
* want to check ParamterList.Exists() here. Instead,
* just check if GetParamter() returns null.
*/
if (GetParameter(param.name) == null)
{
Add(param.name, param.value, param.column);
}
}
}
/// <summary>
/// This formats the dates for different output, for example printing
/// </summary>
/// <param name="AParameters">the current parameters, environmnent variables, for formatting</param>
/// <param name="AOutputType">if this is 'Localized' then the dates are formatted in the format DD-MMM-YYYY</param>
/// <returns>s a new copy of the result, with the correct formatting
/// </returns>
public TResultList ConvertToFormattedStrings(TParameterList AParameters, String AOutputType)
{
TResultList ReturnValue = new TResultList(this);
Int32 i;
foreach (TResult r in ReturnValue.results)
{
for (i = 0; i <= 1; i++)
{
r.header[i] = new TVariant(r.header[i].ToFormattedString("", AOutputType));
}
for (i = 0; i <= 1; i++)
{
r.descr[i] = new TVariant(r.descr[i].ToFormattedString("", AOutputType));
}
for (i = 0; i < r.column.Length; i++)
{
if (r.column[i].TypeVariant == eVariantTypes.eString)
{
r.column[i] = new TVariant(r.column[i].ToString(), true);
}
else
{
// format thousands only or without decimals
if (StringHelper.IsCurrencyFormatString(r.column[i].FormatString) && AParameters.Exists("param_currency_format"))
{
r.column[i] = new TVariant(r.column[i].ToFormattedString(AParameters.Get(
"param_currency_format").ToString(), AOutputType), true);
}
else
{
r.column[i] = new TVariant(r.column[i].ToFormattedString("", AOutputType), true);
}
}
}
}
return(ReturnValue);
}
/// <summary>
/// calculate an extract from a report: all partners in selected relationships with selected partner
/// </summary>
/// <param name="AParameters"></param>
/// <param name="AResults"></param>
/// <returns></returns>
public static bool CalculateExtract(TParameterList AParameters, TResultList AResults)
{
string SqlStmt = "";
if (AParameters.Get("param_selection").ToString() == "an extract")
{
SqlStmt = TDataBase.ReadSqlFile("Partner.Queries.ExtractFromExtractByPartnerRelationship.sql");
}
else if (AParameters.Get("param_selection").ToString() == "one partner")
{
SqlStmt = TDataBase.ReadSqlFile("Partner.Queries.ExtractByPartnerRelationship.sql");
}
else
{
throw new ArgumentException("Must supply an extract or partner key.");
}
// create a new object of this class and control extract calculation from base class
QueryPartnerByRelationship ExtractQuery = new QueryPartnerByRelationship();
return ExtractQuery.CalculateExtractInternal(AParameters, SqlStmt, AResults);
}
/// <summary>
/// Procedure to copy all parameters of one column from another parameter list;
/// The column is first emptied in this parameter list, before the copying takes place.
///
/// </summary>
/// <returns>void</returns>
public void Copy(TParameterList AOtherList, int column, int depth, eParameterFit exact, int ADestColumn)
{
TParameter element;
System.Int32 Counter;
if (ADestColumn == -1)
{
ADestColumn = column;
}
// remove all parameters in column
Counter = 0;
while (Counter < Fparameters.Count)
{
element = (TParameter)Fparameters[Counter];
if (element.column == ADestColumn)
{
Fparameters.RemoveAt(Counter);
}
else
{
Counter = Counter + 1;
}
}
// copy parameters from other list
foreach (TParameter element2 in AOtherList.Fparameters)
{
if (element2.column == column)
{
Add(element2.name, element2.value, ADestColumn, element2.level, element2.subreport);
}
}
}
private void SetControlsManual(TParameterList AParameters)
{
FFromAccountCode = AParameters.Get("param_account_from").ToString();
FToAccountCode = AParameters.Get("param_account_to").ToString();
}
/// <summary>
/// constructor
/// </summary>
/// <param name="parameters"></param>
/// <param name="column"></param>
/// <param name="depth"></param>
public TRptFormatQuery(TParameterList parameters, int column, int depth)
{
this.parameters = parameters;
this.column = column;
this.depth = depth;
}
/// <summary>
/// copy the whole list
/// </summary>
/// <param name="AOtherList"></param>
public void Copy(TParameterList AOtherList)
{
Copy(AOtherList, -1, -1, eParameterFit.eBestFit, -1);
}
/// <summary>
/// overload; no specific separator, find the best for the current localisation
/// </summary>
/// <param name="AParameters"></param>
/// <param name="csvfilename"></param>
/// <param name="AExportOnlyLowestLevel"></param>
/// <returns></returns>
public bool WriteCSV(TParameterList AParameters, string csvfilename, Boolean AExportOnlyLowestLevel)
{
return(WriteCSV(AParameters, csvfilename, "FIND_BEST_SEPARATOR", false, AExportOnlyLowestLevel));
}
/// <summary>
/// This stores the resultlist into a XmlDocument (to be saved as Excel file)
/// </summary>
/// <param name="AParameters"></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 XmlDocument</returns>
public XmlDocument WriteXmlDocument(TParameterList AParameters, Boolean AExportOnlyLowestLevel = false)
{
List <string> lines = WriteCSVInternal(AParameters, ";", false, AExportOnlyLowestLevel);
return(TCsv2Xml.ParseCSV2Xml(lines, ";"));
}
/// <summary>
/// overload, use Localized formatting
/// </summary>
/// <param name="AParameters"></param>
/// <returns></returns>
public TResultList ConvertToFormattedStrings(TParameterList AParameters)
{
return(ConvertToFormattedStrings(AParameters, "Localized"));
}
/// <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);
}
