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>
/// 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 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>
/// 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>
/// 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);
}
/// <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>
/// get all partners that we want to display on the current birthday report
/// </summary>
public static DataTable CalculateBirthdays(TParameterList AParameters, TResultList AResults)
{
SortedList <string, string>Defines = new SortedList <string, string>();
List <OdbcParameter>SqlParameterList = new List <OdbcParameter>();
try
{
// prepare the sql statement parameters
if (AParameters.Exists("FamilyKey"))
{
SqlParameterList.Add(new OdbcParameter("FamilyKey", OdbcType.Decimal)
{
Value = AParameters.Get("FamilyKey").ToDecimal()
});
Defines.Add("BYFAMILYKEY", string.Empty);
}
else
{
AddPartnerSelectionParametersToSqlQuery(AParameters, Defines, SqlParameterList);
}
if (AParameters.Get("param_chkSelectTypes").ToBool() == true)
{
string[] types = AParameters.Get("param_typecode").ToString().Split(new char[] { ',' });
string FilterForTypes = string.Empty;
foreach (string type in types)
{
if (FilterForTypes.Length > 0)
{
FilterForTypes += " OR ";
}
FilterForTypes += "pptype.p_type_code_c = ?";
SqlParameterList.Add(new OdbcParameter("typecode" + FilterForTypes.Length, OdbcType.VarChar)
{
Value = type
});
}
Defines.Add("SELECTTYPES", "(" + FilterForTypes + ")");
}
if (AParameters.Get("param_chkUseDate").ToBool() == true)
{
DateTime FromDate = AParameters.Get("param_dtpFromDate").ToDate();
DateTime ToDate = AParameters.Get("param_dtpToDate").ToDate();
if (FromDate.DayOfYear < ToDate.DayOfYear)
{
Defines.Add("WITHDATERANGE", string.Empty);
SqlParameterList.Add(new OdbcParameter("startdate", OdbcType.Date)
{
Value = FromDate
});
SqlParameterList.Add(new OdbcParameter("enddate", OdbcType.Date)
{
Value = ToDate
});
}
else
{
Defines.Add("WITHOUTDATERANGE", string.Empty);
SqlParameterList.Add(new OdbcParameter("enddate", OdbcType.Date)
{
Value = ToDate
});
SqlParameterList.Add(new OdbcParameter("startdate", OdbcType.Date)
{
Value = FromDate
});
}
}
}
catch (Exception e)
{
TLogging.Log("problem while preparing sql statement for birthday report: " + e.ToString());
return null;
}
string SqlStmt = TDataBase.ReadSqlFile("Personnel.Reports.Birthday.sql", Defines);
Boolean NewTransaction;
TDBTransaction Transaction = DBAccess.GDBAccessObj.GetNewOrExistingTransaction(IsolationLevel.ReadCommitted, out NewTransaction);
try
{
// now run the database query
TLogging.Log("Getting the data from the database...", TLoggingType.ToStatusBar);
DataTable resultTable = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "result", Transaction,
SqlParameterList.ToArray());
// if this is taking a long time, every now and again update the TLogging statusbar, and check for the cancel button
if (AParameters.Get("CancelReportCalculation").ToBool() == true)
{
return null;
}
// if end date is not set, use the end of this year
DateTime AgeDay = DateTime.Now;
if (AParameters.Get("param_chkUseDate").ToBool() == true)
{
AgeDay = AParameters.Get("param_dtpToDate").ToDate();
}
else
{
AgeDay = new DateTime(AgeDay.Year, 12, 31);
}
// Calculate the age, in new column
resultTable.Columns.Add(new DataColumn("age", typeof(Int32)));
foreach (DataRow r in resultTable.Rows)
{
int age = 0;
if (r["DOB"] != DBNull.Value)
{
DateTime BDay = Convert.ToDateTime(r["DOB"]);
age = AgeDay.Year - BDay.Year;
}
r["Age"] = age;
}
// filter by anniversaries?
if ((AParameters.Get("param_chkAnniversaries").ToBool() == true)
&& !AParameters.Get("param_txtAnniversaries").IsZeroOrNull())
{
List <string>anniversaries = new List <string>(AParameters.Get("param_txtAnniversaries").ToString().Split(new char[] { ',' }));
List <DataRow>RowsToDelete = new List <DataRow>();
foreach (DataRow r in resultTable.Rows)
{
if (!anniversaries.Contains(r["Age"].ToString()))
{
RowsToDelete.Add(r);
}
}
foreach (DataRow r in RowsToDelete)
{
resultTable.Rows.Remove(r);
}
}
return resultTable;
}
catch (Exception e)
{
TLogging.Log(e.ToString());
return null;
}
finally
{
if (NewTransaction)
{
DBAccess.GDBAccessObj.RollbackTransaction();
}
}
}
/// <summary>
/// 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)
{
Int32 StoredVal = 0;
if (FLedgerNumber == -1)
{
// we will wait until the ledger number has been set
return;
}
//TODO: Calendar vs Financial Date Handling - Check if below has assumed a 12 period financial year
// TODO
// int DiffPeriod = 0;//(System.Int32)CbB_YearEndsOn.SelectedItem;
// DiffPeriod = DiffPeriod - 12;
// ACalculator.AddParameter("param_diff_period_i", DiffPeriod);
cmbAccountHierarchy.SetSelectedString(AParameters.Get("param_account_hierarchy_c").ToString());
cmbCurrency.SetSelectedString(AParameters.Get("param_currency").ToString());
cmbPeriodYear.SetSelectedInt32(AParameters.Get("param_year_i").ToInt());
cmbQuarterYear.SetSelectedInt32(AParameters.Get("param_year_i").ToInt());
cmbBreakdownYear.SetSelectedInt32(AParameters.Get("param_year_i").ToInt());
rbtQuarter.Checked = AParameters.Get("param_quarter").ToBool();
rbtDate.Checked = AParameters.Get("param_date_checked").ToBool();
rbtPeriod.Checked = AParameters.Get("param_period").ToBool();
rbtBreakdown.Checked = AParameters.Get("param_period_breakdown").ToBool();
if (!rbtPeriod.Checked && !rbtDate.Checked && !rbtQuarter.Checked && !rbtBreakdown.Checked)
{
rbtPeriod.Checked = true;
}
StoredVal = Math.Max((AParameters.Get("param_end_period_i").ToInt() / 3), 1);
txtQuarter.Text = StoredVal.ToString();
if (AParameters.Get("param_start_period_i").ToString().Length == 0)
{
txtStartPeriod.Text = FLedgerRow.CurrentPeriod.ToString();
}
else
{
StoredVal = Math.Max(AParameters.Get("param_start_period_i").ToInt(), 1);
txtStartPeriod.Text = StoredVal.ToString();
}
if (AParameters.Get("param_end_period_i").ToString().Length == 0)
{
txtEndPeriod.Text = FLedgerRow.CurrentPeriod.ToString();
}
else
{
StoredVal = Math.Max(AParameters.Get("param_end_period_i").ToInt(), 1);
txtEndPeriod.Text = StoredVal.ToString();
}
if (cmbPeriodYear.SelectedIndex == -1)
{
cmbPeriodYear.SetSelectedInt32(FLedgerRow.CurrentFinancialYear);
}
if (cmbQuarterYear.SelectedIndex == -1)
{
cmbQuarterYear.SetSelectedInt32(FLedgerRow.CurrentFinancialYear);
}
if (cmbBreakdownYear.SelectedIndex == -1)
{
cmbBreakdownYear.SetSelectedInt32(FLedgerRow.CurrentFinancialYear);
}
if (AParameters.Exists("param_start_date"))
{
dtpStartDate.Date = AParameters.Get("param_start_date").ToDate();
dtpEndDate.Date = AParameters.Get("param_end_date").ToDate();
}
}
/// <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>
/// 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)
{
string WhereClause = "";
string TableNames = "";
string StringValue;
// add parameters to sql parameter list
// Partner Class
ASQLParameterList.Add(new OdbcParameter("param_partner_class_unset", OdbcType.Bit)
{
Value = AParameters.Get("param_partner_class").IsZeroOrNull()
});
ASQLParameterList.Add(new OdbcParameter("param_partner_class", OdbcType.VarChar)
{
Value = AParameters.Get("param_partner_class").ToString()
});
// Language Code
ASQLParameterList.Add(new OdbcParameter("param_language_unset", OdbcType.Bit)
{
Value = AParameters.Get("param_language").IsZeroOrNull()
});
ASQLParameterList.Add(new OdbcParameter("param_language", OdbcType.VarChar)
{
Value = AParameters.Get("param_language").ToString()
});
// Active Partners and No Solicitations
ASQLParameterList.Add(new OdbcParameter("param_active", OdbcType.Bit)
{
Value = AParameters.Get("param_active").ToBool()
});
ASQLParameterList.Add(new OdbcParameter("param_exclude_no_solicitations", OdbcType.Bit)
{
Value = AParameters.Get("param_exclude_no_solicitations").ToBool()
});
// User that created/modified the record
ASQLParameterList.Add(new OdbcParameter("param_user_created_unset", OdbcType.Bit)
{
Value = AParameters.Get("param_user_created").IsZeroOrNull()
});
ASQLParameterList.Add(new OdbcParameter("param_user_created", OdbcType.VarChar)
{
Value = AParameters.Get("param_user_created").ToString()
});
ASQLParameterList.Add(new OdbcParameter("param_user_modified_unset", OdbcType.Bit)
{
Value = AParameters.Get("param_user_modified").IsZeroOrNull()
});
ASQLParameterList.Add(new OdbcParameter("param_user_modified", OdbcType.VarChar)
{
Value = AParameters.Get("param_user_modified").ToString()
});
// Date range for creation and modification of record
ASQLParameterList.Add(new OdbcParameter("param_date_created_from_unset", OdbcType.Bit)
{
Value = AParameters.Get("param_date_created_from").IsZeroOrNull()
});
ASQLParameterList.Add(new OdbcParameter("param_date_created_from", OdbcType.Date)
{
Value = AParameters.Get("param_date_created_from").ToDate()
});
ASQLParameterList.Add(new OdbcParameter("param_date_created_to_unset", OdbcType.Bit)
{
Value = AParameters.Get("param_date_created_to").IsZeroOrNull()
});
ASQLParameterList.Add(new OdbcParameter("param_date_created_to", OdbcType.Date)
{
Value = AParameters.Get("param_date_created_to").ToDate()
});
ASQLParameterList.Add(new OdbcParameter("param_date_modified_from_unset", OdbcType.Bit)
{
Value = AParameters.Get("param_date_modified_from").IsZeroOrNull()
});
ASQLParameterList.Add(new OdbcParameter("param_date_modified_from", OdbcType.Date)
{
Value = AParameters.Get("param_date_modified_from").ToDate()
});
ASQLParameterList.Add(new OdbcParameter("param_date_modified_to_unset", OdbcType.Bit)
{
Value = AParameters.Get("param_date_modified_to").IsZeroOrNull()
});
ASQLParameterList.Add(new OdbcParameter("param_date_modified_to", OdbcType.Date)
{
Value = AParameters.Get("param_date_modified_to").ToDate()
});
// add statement for church denomination
if (AParameters.Exists("param_denomination"))
{
StringValue = AParameters.Get("param_denomination").ToString();
if (StringValue.Trim().Length > 0)
{
ASQLParameterList.Add(new OdbcParameter("param_denomination", OdbcType.VarChar)
{
Value = StringValue
});
TableNames = ", pub_p_church";
WhereClause = " AND pub_p_church.p_partner_key_n = pub_p_partner.p_partner_key_n" +
" AND pub_p_church.p_denomination_c = ?";
}
}
// add statement for organisation business code
if (AParameters.Exists("param_business"))
{
StringValue = AParameters.Get("param_business").ToString();
if (StringValue.Trim().Length > 0)
{
ASQLParameterList.Add(new OdbcParameter("param_business", OdbcType.VarChar)
{
Value = StringValue
});
TableNames = ", pub_p_organisation";
WhereClause = " AND pub_p_organisation.p_partner_key_n = pub_p_partner.p_partner_key_n" +
" AND pub_p_organisation.p_business_code_c = ?";
}
}
ASqlStmt = ASqlStmt.Replace("##partner_specific_tables##", TableNames);
ASqlStmt = ASqlStmt.Replace("##partner_specific_where_clause##", WhereClause);
}
/// <summary>
/// calculate the report and save the result and returned parameters to file
/// </summary>
public static void CalculateReport(string AReportParameterXmlFile, TParameterList ASpecificParameters, int ALedgerNumber = -1)
{
// important: otherwise month names are in different language, etc
Thread.CurrentThread.CurrentCulture = new CultureInfo("en-GB", false);
TReportGeneratorUIConnector ReportGenerator = new TReportGeneratorUIConnector();
TParameterList Parameters = new TParameterList();
string resultFile = AReportParameterXmlFile.Replace(".xml", ".Results.xml");
string parameterFile = AReportParameterXmlFile.Replace(".xml", ".Parameters.xml");
Parameters.Load(AReportParameterXmlFile);
if (ALedgerNumber != -1)
{
Parameters.Add("param_ledger_number_i", ALedgerNumber);
}
Parameters.Add(ASpecificParameters);
ReportGenerator.Start(Parameters.ToDataTable());
while (!ReportGenerator.Progress.JobFinished)
{
Thread.Sleep(500);
}
Assert.IsTrue(ReportGenerator.GetSuccess(), "Report did not run successfully");
TResultList Results = new TResultList();
Results.LoadFromDataTable(ReportGenerator.GetResult());
Parameters.LoadFromDataTable(ReportGenerator.GetParameter());
if (!Parameters.Exists("ControlSource", ReportingConsts.HEADERPAGELEFT1, -1, eParameterFit.eBestFit))
{
Parameters.Add("ControlSource", new TVariant("Left1"), ReportingConsts.HEADERPAGELEFT1);
}
if (!Parameters.Exists("ControlSource", ReportingConsts.HEADERPAGELEFT2, -1, eParameterFit.eBestFit))
{
Parameters.Add("ControlSource", new TVariant("Left2"), ReportingConsts.HEADERPAGELEFT2);
}
Parameters.Save(parameterFile, false);
Results.WriteCSV(Parameters, resultFile, ",", false, false);
}
/// <summary>
/// Sets the selected values in the controls, using the parameters loaded from a file
///
/// </summary>
/// <param name="AColumnParameters">List with the current columns</param>
/// <param name="AParameters"></param>
/// <returns>the MaxDisplayColumns number</returns>
public static System.Int32 SetControls(ref TParameterList AColumnParameters, ref TParameterList AParameters)
{
System.Int32 MaxDisplayColumns = 0;
/* copy values for columns to the current set of parameters */
AColumnParameters.Clear();
if (AParameters.Exists("MaxDisplayColumns"))
{
MaxDisplayColumns = AParameters.Get("MaxDisplayColumns").ToInt();
}
AColumnParameters.Add("MaxDisplayColumns", MaxDisplayColumns);
for (int Counter = 0; Counter <= MaxDisplayColumns - 1; Counter += 1)
{
AColumnParameters.Copy(AParameters, Counter, -1, eParameterFit.eExact, Counter);
}
return MaxDisplayColumns;
}
/// <summary>
/// initialise the controls using the parameters
/// </summary>
/// <param name="AParameters"></param>
public void SetControls(TParameterList AParameters)
{
rbtPartner.Checked = (AParameters.Get("param_selection").ToString() == "one partner");
rbtExtract.Checked = (AParameters.Get("param_selection").ToString() == "an extract");
rbtCurrentStaff.Checked = (AParameters.Get("param_selection").ToString() == "all current staff");
rbtAllStaff.Checked = (AParameters.Get("param_selection").ToString() == "all staff");
txtPartnerKey.Text = AParameters.Get("param_partnerkey").ToString();
txtExtract.Text = AParameters.Get("param_extract").ToString();
if (AParameters.Exists("param_currenstaffdate"))
{
dtpCurrentStaff.Date = AParameters.Get("param_currentstaffdate").ToDate();
}
else
{
dtpCurrentStaff.Date = DateTime.Today;
}
}
/// <summary>
/// initialise the controls using the parameters
/// </summary>
/// <param name="AParameters"></param>
public void SetControls(TParameterList AParameters)
{
if (AParameters.Get("param_conferenceselection").ToString() == "one conference")
{
rbtConference.Checked = true;
}
else if (AParameters.Get("param_conferenceselection").ToString() == "all conferences")
{
rbtAllConferences.Checked = true;
}
String AttendeeSelection = AParameters.Get("param_attendeeselection").ToString();
if (AttendeeSelection == "all attendees")
{
rbtAllAttendees.Checked = true;
}
else if (AttendeeSelection == "from extract")
{
rbtExtract.Checked = true;
}
else if (AttendeeSelection == "one attendee")
{
rbtOneAttendee.Checked = true;
}
txtOneAttendee.Text = AParameters.Get("param_partnerkey").ToString();
if (AParameters.Exists("param_conferencekey") && (txtConference.Text == ""))
{
txtConference.Text = AParameters.Get("param_conferencekey").ToString();
}
txtExtract.Text = AParameters.Get("param_extractname").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;
}
/// <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;
}
/// <summary>
/// initialise the controls using the parameters
/// </summary>
/// <param name="AParameters"></param>
public void SetControls(TParameterList AParameters)
{
for (Int32 counter = 0; counter < NUMBER_SORTBY; counter += 1)
{
if (AParameters.Exists("orderby" + counter.ToString()))
{
FSortByComboboxes[counter].SetSelectedString(AParameters.Get("orderby" + counter.ToString()).ToString());
}
}
}
/// <summary>
/// if withExchangeRatesGLM is false, it
/// will only set the correct values for realPeriod and realYear,
/// </summary>
/// <returns></returns>
public TFinancialPeriod(TDataBase databaseConnection, int period, int year, TParameterList parameters, int column)
{
diffPeriod = 0;
FCurrentFinancialYear = parameters.Get("param_current_financial_year_i", column).ToInt();
FNumberAccountingPeriods = parameters.Get("param_number_of_accounting_periods_i", column).ToInt();
FCurrentPeriod = parameters.Get("param_current_period_i", column).ToInt();
FNumberForwardingPeriods = parameters.Get("param_number_fwd_posting_periods_i", column).ToInt();
Int32 glmSequenceNumber = parameters.Get("glm_sequence_i", column).ToInt();
realGlmSequence = LedgerStatus.GlmSequencesCache.GetGlmSequence(glmSequenceNumber);
if ((realGlmSequence != null) && (realGlmSequence.year != year))
{
realGlmSequence = LedgerStatus.GlmSequencesCache.GetOtherYearGlmSequence(databaseConnection, realGlmSequence, year);
}
if (parameters.Exists("param_diff_period_i", column, -1))
{
diffPeriod = parameters.Get("param_diff_period_i", column).ToInt();
}
MainConstructor(databaseConnection,
period,
year,
diffPeriod,
FCurrentFinancialYear,
FCurrentPeriod,
FNumberAccountingPeriods,
FNumberForwardingPeriods,
realGlmSequence);
}
