/// <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>
/// retrieve parameters from client sent in AParameters and build up AParameterList to run SQL query
/// </summary>
/// <param name="AParameters"></param>
/// <param name="ASqlStmt"></param>
/// <param name="ASQLParameterList"></param>
protected override void RetrieveParameters(TParameterList AParameters, ref string ASqlStmt, ref List <OdbcParameter> ASQLParameterList)
{
// prepare list of selected events
List <String> param_events = new List <String>();
foreach (TVariant choice in AParameters.Get("param_events").ToComposite())
{
param_events.Add(choice.ToString());
}
if (param_events.Count == 0)
{
throw new NoNullAllowedException("At least one event must be checked.");
}
// prepare list of selected event roles (comes all in one comma separated string)
List <String> param_event_roles = new List <String>();
if (AParameters.Exists("param_event_roles"))
{
param_event_roles = new List <String>(AParameters.Get("param_event_roles").ToString().Split(','));
}
if (param_event_roles.Count == 0)
{
throw new NoNullAllowedException("At least one event role must be checked.");
}
// now add parameters to sql parameter list
ASQLParameterList.Add(TDbListParameterValue.OdbcListParameterValue("events", OdbcType.BigInt, param_events));
ASQLParameterList.Add(TDbListParameterValue.OdbcListParameterValue("event_roles", OdbcType.VarChar, param_event_roles));
ASQLParameterList.Add(new OdbcParameter("Accepted", OdbcType.Bit)
{
Value = AParameters.Get("param_status_accepted").ToBool()
});
ASQLParameterList.Add(new OdbcParameter("Hold", OdbcType.Bit)
{
Value = AParameters.Get("param_status_hold").ToBool()
});
ASQLParameterList.Add(new OdbcParameter("Enquiry", OdbcType.Bit)
{
Value = AParameters.Get("param_status_enquiry").ToBool()
});
ASQLParameterList.Add(new OdbcParameter("Cancelled", OdbcType.Bit)
{
Value = AParameters.Get("param_status_cancelled").ToBool()
});
ASQLParameterList.Add(new OdbcParameter("Rejected", OdbcType.Bit)
{
Value = AParameters.Get("param_status_rejected").ToBool()
});
}
/// <summary>
/// retrieve parameters from client sent in AParameters and build up AParameterList to run SQL query
/// </summary>
/// <param name="AParameters"></param>
/// <param name="ASqlStmt"></param>
/// <param name="ASQLParameterList"></param>
protected override void RetrieveParameters(TParameterList AParameters, ref string ASqlStmt, ref List <OdbcParameter> ASQLParameterList)
{
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>
/// 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>
/// 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
// when "mailing addresses only" is selected no expired or future addresses should be used
if ((AParameters.Exists("param_only_addresses_valid_on") &&
(AParameters.Get("param_only_addresses_valid_on").ToBool())) ||
(AParameters.Exists("param_mailing_addresses_only") &&
(AParameters.Get("param_mailing_addresses_only").ToBool())))
{
if ((AParameters.Exists("param_only_addresses_valid_on") &&
(AParameters.Get("param_only_addresses_valid_on").ToBool())) &&
(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 or "mailing addresses only" 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>
/// 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 commitment types
List <String>param_commitment_status_choices = new List <String>();
foreach (TVariant choice in AParameters.Get("param_commitment_status_choices").ToComposite())
{
param_commitment_status_choices.Add(choice.ToString());
}
// now add parameters to sql parameter list
ASQLParameterList.Add(new OdbcParameter("param_start_date_from_unset", OdbcType.Bit)
{
Value = AParameters.Get("param_start_date_from").IsZeroOrNull()
});
ASQLParameterList.Add(new OdbcParameter("param_start_date_from", OdbcType.Date)
{
Value = AParameters.Get("param_start_date_from").ToDate()
});
ASQLParameterList.Add(new OdbcParameter("param_start_date_to_unset", OdbcType.Bit)
{
Value = AParameters.Get("param_start_date_to").IsZeroOrNull()
});
ASQLParameterList.Add(new OdbcParameter("param_start_date_to", OdbcType.Date)
{
Value = AParameters.Get("param_start_date_to").ToDate()
});
ASQLParameterList.Add(new OdbcParameter("param_end_date_from_unset", OdbcType.Bit)
{
Value = AParameters.Get("param_end_date_from").IsZeroOrNull()
});
ASQLParameterList.Add(new OdbcParameter("param_end_date_from", OdbcType.Date)
{
Value = AParameters.Get("param_end_date_from").ToDate()
});
ASQLParameterList.Add(new OdbcParameter("param_end_date_to_unset", OdbcType.Bit)
{
Value = AParameters.Get("param_end_date_to").IsZeroOrNull()
});
ASQLParameterList.Add(new OdbcParameter("param_end_date_to", OdbcType.Date)
{
Value = AParameters.Get("param_end_date_to").ToDate()
});
ASQLParameterList.Add(new OdbcParameter("param_date_valid_on_unset", OdbcType.Bit)
{
Value = AParameters.Get("param_date_valid_on").IsZeroOrNull()
});
ASQLParameterList.Add(new OdbcParameter("param_date_valid_on_a", OdbcType.Date)
{
Value = AParameters.Get("param_date_valid_on").ToDate()
});
ASQLParameterList.Add(new OdbcParameter("param_date_valid_on_b", OdbcType.Date)
{
Value = AParameters.Get("param_date_valid_on").ToDate()
});
ASQLParameterList.Add(new OdbcParameter("param_field_sending_unset", OdbcType.Bit)
{
Value = AParameters.Get("param_field_sending").IsZeroOrNull()
});
ASQLParameterList.Add(new OdbcParameter("param_field_sending", OdbcType.Int)
{
Value = AParameters.Get("param_field_sending").ToInt32()
});
ASQLParameterList.Add(new OdbcParameter("param_field_receiving_unset", OdbcType.Bit)
{
Value = AParameters.Get("param_field_receiving").IsZeroOrNull()
});
ASQLParameterList.Add(new OdbcParameter("param_field_receiving", OdbcType.Int)
{
Value = AParameters.Get("param_field_receiving").ToInt32()
});
ASQLParameterList.Add(new OdbcParameter("param_consider_commitment_status_not", OdbcType.Bit)
{
Value = AParameters.Get("param_all_partners").ToBool()
});
ASQLParameterList.Add(TDbListParameterValue.OdbcListParameterValue("param_commitment_status_choices",
OdbcType.VarChar,
param_commitment_status_choices));
ASQLParameterList.Add(new OdbcParameter("param_include_no_commitment_status", OdbcType.Bit)
{
Value = AParameters.Get("param_include_no_commitment_status").ToBool()
});
ASQLParameterList.Add(new OdbcParameter("param_active_not", OdbcType.Bit)
{
Value = !AParameters.Get("param_active").ToBool()
});
ASQLParameterList.Add(new OdbcParameter("param_exclude_no_solicitations_not", OdbcType.Bit)
{
Value = !AParameters.Get("param_exclude_no_solicitations").ToBool()
});
}
/// <summary>
/// retrieve parameters from client sent in AParameters and build up AParameterList to run SQL query
/// </summary>
/// <param name="AParameters"></param>
/// <param name="ASqlStmt"></param>
/// <param name="ASQLParameterList"></param>
protected override void RetrieveParameters(TParameterList AParameters, ref string ASqlStmt, ref List <OdbcParameter> ASQLParameterList)
{
bool AllLedgers;
bool ReceiptLetterFrequencySet;
string ReceiptLetterFrequency;
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()
});
ASQLParameterList.Add(new OdbcParameter("param_new_donors_only", OdbcType.Bit)
{
Value = AParameters.Get("param_new_donors_only").ToBool()
});
ASQLParameterList.Add(new OdbcParameter("param_receipt_each_gift_only", OdbcType.Bit)
{
Value = AParameters.Get("param_receipt_each_gift_only").ToBool()
});
ASQLParameterList.Add(new OdbcParameter("param_active", OdbcType.Bit)
{
Value = AParameters.Get("param_active").ToBool()
});
ASQLParameterList.Add(new OdbcParameter("param_families_only", OdbcType.Bit)
{
Value = AParameters.Get("param_families_only").ToBool()
});
ASQLParameterList.Add(new OdbcParameter("param_exclude_no_solicitations", OdbcType.Bit)
{
Value = AParameters.Get("param_exclude_no_solicitations").ToBool()
});
if (AParameters.Get("param_receipt_letter_frequency").IsZeroOrNull() ||
(AParameters.Get("param_receipt_letter_frequency").ToString() == ""))
{
ReceiptLetterFrequencySet = false;
ReceiptLetterFrequency = "";
}
else
{
ReceiptLetterFrequencySet = true;
ReceiptLetterFrequency = AParameters.Get("param_receipt_letter_frequency").ToString();
}
ASQLParameterList.Add(new OdbcParameter("param_receipt_letter_frequency_set", OdbcType.Bit)
{
Value = ReceiptLetterFrequencySet
});
ASQLParameterList.Add(new OdbcParameter("param_receipt_letter_frequency", OdbcType.VarChar)
{
Value = ReceiptLetterFrequency
});
}
/// <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 (var choice in AParameters.Get("param_explicit_publication").ToString().Split(','))
{
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_persons_only", OdbcType.Bit)
{
Value = AParameters.Get("param_persons_only").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()
});
if (AParameters.Get("param_rgrCopies").ToString() == "Single")
{
ASQLParameterList.Add(new OdbcParameter("param_min_copies", OdbcType.Int)
{
Value = 1
});
ASQLParameterList.Add(new OdbcParameter("param_max_copies", OdbcType.Int)
{
Value = 1
});
}
else if (AParameters.Get("param_rgrCopies").ToString() == "Bulk")
{
ASQLParameterList.Add(new OdbcParameter("param_min_copies", OdbcType.Int)
{
Value = 2
});
ASQLParameterList.Add(new OdbcParameter("param_max_copies", OdbcType.Int)
{
Value = 999999
});
}
else
{
ASQLParameterList.Add(new OdbcParameter("param_min_copies", OdbcType.Int)
{
Value = 1
});
ASQLParameterList.Add(new OdbcParameter("param_max_copies", OdbcType.Int)
{
Value = 999999
});
}
ASQLParameterList.Add(new OdbcParameter("param_start_date_from_notnull", OdbcType.Bit)
{
Value = AParameters.Get("param_start_date_from_notnull").ToBool()
});
ASQLParameterList.Add(new OdbcParameter("param_start_date_from", OdbcType.Date)
{
Value = AParameters.Get("param_start_date_from").ToDate()
});
ASQLParameterList.Add(new OdbcParameter("param_start_date_to_notnull", OdbcType.Bit)
{
Value = AParameters.Get("param_start_date_to_notnull").ToBool()
});
ASQLParameterList.Add(new OdbcParameter("param_start_date_to", OdbcType.Date)
{
Value = AParameters.Get("param_start_date_to").ToDate()
});
ASQLParameterList.Add(new OdbcParameter("param_expiry_date_from_notnull", OdbcType.Bit)
{
Value = AParameters.Get("param_expiry_date_from_notnull").ToBool()
});
ASQLParameterList.Add(new OdbcParameter("param_expiry_date_from", OdbcType.Date)
{
Value = AParameters.Get("param_expiry_date_from").ToDate()
});
ASQLParameterList.Add(new OdbcParameter("param_expiry_date_to_notnull", OdbcType.Bit)
{
Value = AParameters.Get("param_expiry_date_to_notnull").ToBool()
});
ASQLParameterList.Add(new OdbcParameter("param_expiry_date_to", OdbcType.Date)
{
Value = AParameters.Get("param_expiry_date_to").ToDate()
});
}
/// <summary>
/// Add persons or families to extract that have commitments to specified receiving/sending fields
/// </summary>
/// <param name="AProcessReceivingFields"></param>
/// <param name="AParameters"></param>
/// <param name="ATransaction"></param>
/// <param name="AExtractId"></param>
private bool ProcessCommitments(bool AProcessReceivingFields, TParameterList AParameters,
TDBTransaction ATransaction, out int AExtractId)
{
bool ReturnValue = false;
bool AddressFilterAdded;
string SqlStmt = TDataBase.ReadSqlFile("Partner.Queries.ExtractPartnerByField.Commitment.sql");
List <OdbcParameter> SqlParameterList = new List <OdbcParameter>();
// need to set initial value here in case method needs to return before value is set
AExtractId = -1;
// prepare list of selected fields
List <String> param_fields = new List <String>();
foreach (TVariant choice in AParameters.Get("param_fields").ToComposite())
{
param_fields.Add(choice.ToString());
}
if (param_fields.Count == 0)
{
throw new NoNullAllowedException("At least one option must be checked.");
}
// now add parameters to sql parameter list
SqlParameterList.Add(TDbListParameterValue.OdbcListParameterValue("fields", OdbcType.BigInt, param_fields));
SqlParameterList.Add(new OdbcParameter("param_from_date_unset", OdbcType.Bit)
{
Value = AParameters.Get("param_from_date").IsZeroOrNull()
});
SqlParameterList.Add(new OdbcParameter("param_from_date", OdbcType.Date)
{
Value = AParameters.Get("param_from_date").ToDate()
});
SqlParameterList.Add(new OdbcParameter("param_until_date_unset", OdbcType.Bit)
{
Value = AParameters.Get("param_until_date").IsZeroOrNull()
});
SqlParameterList.Add(new OdbcParameter("param_until_date", OdbcType.Date)
{
Value = AParameters.Get("param_until_date").ToDate()
});
SqlParameterList.Add(new OdbcParameter("param_active", OdbcType.Bit)
{
Value = AParameters.Get("param_active").ToBool()
});
SqlParameterList.Add(new OdbcParameter("param_exclude_no_solicitations", OdbcType.Bit)
{
Value = AParameters.Get("param_exclude_no_solicitations").ToBool()
});
if (AProcessReceivingFields)
{
// for receiving fields target field table field needs to be used
SqlStmt = SqlStmt.Replace("##sending_or_receiving_field##", "pm_receiving_field_n");
}
else
{
// for sending fields home office table field needs to be used
SqlStmt = SqlStmt.Replace("##sending_or_receiving_field##", "pm_home_office_n");
}
if (AParameters.Get("param_families_only").ToBool())
{
/* In case that only family records are wanted a join via family key of a person is needed
* to find families of persons. */
SqlStmt = SqlStmt.Replace("##person_table##", ", pub_p_person");
SqlStmt = SqlStmt.Replace("##join_for_person_or_family##",
" AND pub_p_person.p_partner_key_n = pub_pm_staff_data.p_partner_key_n" +
" AND pub_p_partner.p_partner_key_n = pub_p_person.p_family_key_n ");
}
else
{
// in this case there will be person records in the extract
SqlStmt = SqlStmt.Replace("##person_table##", "");
SqlStmt = SqlStmt.Replace("##join_for_person_or_family##",
" AND pub_p_partner.p_partner_key_n = pub_pm_staff_data.p_partner_key_n");
}
// add address filter information to sql statement and parameter list
AddressFilterAdded = AddAddressFilter(AParameters, ref SqlStmt, ref SqlParameterList);
// now run the database query
TLogging.Log("Getting the data from the database...", TLoggingType.ToStatusBar);
DataTable partnerkeys = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "partners", ATransaction,
SqlParameterList.ToArray());
// filter data by postcode (if applicable)
ExtractQueryBase.PostcodeFilter(ref partnerkeys, ref AddressFilterAdded, AParameters, ATransaction);
// if this is taking a long time, every now and again update the TLogging statusbar, and check for the cancel button
// TODO: we might need to add this functionality to TExtractsHandling.CreateExtractFromListOfPartnerKeys as well???
if (AParameters.Get("CancelReportCalculation").ToBool() == true)
{
return(false);
}
TLogging.Log("Preparing the extract...", TLoggingType.ToStatusBar);
// create an extract with the given name in the parameters
ReturnValue = TExtractsHandling.CreateExtractFromListOfPartnerKeys(
AParameters.Get("param_extract_name").ToString(),
AParameters.Get("param_extract_description").ToString(),
out AExtractId,
partnerkeys,
0,
AddressFilterAdded);
return(ReturnValue);
}
/// <summary>
/// Run extract in case the user wants to analyze receiving fields.
/// </summary>
/// <param name="AParameters"></param>
/// <param name="ATransaction"></param>
/// <param name="AExtractId"></param>
private bool ProcessReceivingFields(TParameterList AParameters, TDBTransaction ATransaction, out int AExtractId)
{
/*Approach:
* In case of a specified "Period" only find persons
* that have a commitment record.
* In case of "Now" or "Ever" also find partners
* (persons & families) without such a commitment
* record, that match the specified criteria.
* In case of "Now" only find partners with a "Worker" type.
* (This check is dropped in case of "Ever")
* When interested in families only, also find families
* for which a member matches the specified criteria.*/
bool ReturnValue = false;
// for receiving fields first look at commitments
ReturnValue = ProcessCommitments(true, AParameters, ATransaction, out AExtractId);
if (ReturnValue == false)
{
return(ReturnValue);
}
// if only commitments need to be considered then no need to continue here
if (AParameters.Get("param_commitments_and_worker_field").IsZeroOrNull() ||
(AParameters.Get("param_commitments_and_worker_field").ToString() == "CommitmentsOnly"))
{
return(ReturnValue);
}
bool AddressFilterAdded;
string SqlStmtWorkerFieldOriginal = TDataBase.ReadSqlFile("Partner.Queries.ExtractPartnerByField.WorkerField.sql");
string SqlStmt;
List <OdbcParameter> SqlParameterList = new List <OdbcParameter>();
string TypeCodeParameter;
// If date range was specified then only look at staff data. Otherwise look for persons and families seperately.
if (AParameters.Get("param_field_dates").ToString() == "DateRange")
{
return(ReturnValue);
}
// prepare parameter field for partner type code.
if (AParameters.Get("param_field_dates").ToString() == "DateEver")
{
TypeCodeParameter = "";
}
else
{
TypeCodeParameter = "OMER%";
}
// prepare list of selected fields
List <String> param_fields = new List <String>();
foreach (TVariant choice in AParameters.Get("param_fields").ToComposite())
{
param_fields.Add(choice.ToString());
}
if (param_fields.Count == 0)
{
throw new NoNullAllowedException("At least one option must be checked.");
}
// now add parameters to sql parameter list
SqlParameterList.Add(TDbListParameterValue.OdbcListParameterValue("fields", OdbcType.BigInt, param_fields));
SqlParameterList.Add(new OdbcParameter("param_active", OdbcType.Bit)
{
Value = AParameters.Get("param_active").ToBool()
});
SqlParameterList.Add(new OdbcParameter("param_exclude_no_solicitations", OdbcType.Bit)
{
Value = AParameters.Get("param_exclude_no_solicitations").ToBool()
});
// ----------------------------------------------------------------------------------------
// now start retrieving either families or persons whose worker field is set to given value
// ----------------------------------------------------------------------------------------
SqlStmt = SqlStmtWorkerFieldOriginal;
SqlStmt = SqlStmt.Replace("##person_or_family_table##", ", pub_p_person");
SqlStmt = SqlStmt.Replace("##person_or_family_table_name##", "pub_p_person");
SqlStmt = SqlStmt.Replace("##exclude_familiy_members_existing_in_extract##", "");
SqlStmt = SqlStmt.Replace("##worker_type##", TypeCodeParameter);
if (AParameters.Get("param_families_only").ToBool())
{
/* In case that only family records are wanted a join via family key of a person is needed
* to find families of persons. */
SqlStmt = SqlStmt.Replace("##join_for_person_or_family##",
" AND pub_p_partner.p_partner_key_n = pub_p_person.p_family_key_n");
}
else
{
// in this case there will be person records in the extract
SqlStmt = SqlStmt.Replace("##join_for_person_or_family##",
" AND pub_p_partner.p_partner_key_n = pub_p_person.p_partner_key_n");
}
// add address filter information to sql statement and parameter list
AddressFilterAdded = AddAddressFilter(AParameters, ref SqlStmt, ref SqlParameterList);
// now run the database query
TLogging.Log("Getting the data from the database...", TLoggingType.ToStatusBar);
DataTable partnerkeys = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "partners", ATransaction,
SqlParameterList.ToArray());
// filter data by postcode (if applicable)
ExtractQueryBase.PostcodeFilter(ref partnerkeys, ref AddressFilterAdded, AParameters, ATransaction);
// if this is taking a long time, every now and again update the TLogging statusbar, and check for the cancel button
// TODO: we might need to add this functionality to TExtractsHandling.CreateExtractFromListOfPartnerKeys as well???
if (AParameters.Get("CancelReportCalculation").ToBool() == true)
{
return(false);
}
TLogging.Log("Preparing the extract...", TLoggingType.ToStatusBar);
// create an extract with the given name in the parameters
TExtractsHandling.ExtendExtractFromListOfPartnerKeys(
AExtractId,
partnerkeys,
0,
AddressFilterAdded,
false);
// ----------------------------------------------------------------------------------------
// Now start retrieving families whose worker field is set to given value and that are not
// already contained in the created extract.
// ----------------------------------------------------------------------------------------
SqlStmt = SqlStmtWorkerFieldOriginal;
// need to rebuild parameter list as statement is also loaded again and filled
SqlParameterList.Clear();
SqlParameterList.Add(TDbListParameterValue.OdbcListParameterValue("fields", OdbcType.BigInt, param_fields));
SqlParameterList.Add(new OdbcParameter("param_active", OdbcType.Bit)
{
Value = AParameters.Get("param_active").ToBool()
});
SqlParameterList.Add(new OdbcParameter("param_exclude_no_solicitations", OdbcType.Bit)
{
Value = AParameters.Get("param_exclude_no_solicitations").ToBool()
});
SqlStmt = SqlStmt.Replace("##person_or_family_table##", ", pub_p_family");
SqlStmt = SqlStmt.Replace("##person_or_family_table_name##", "pub_p_family");
SqlStmt = SqlStmt.Replace("##worker_type##", TypeCodeParameter);
SqlStmt = SqlStmt.Replace("##join_for_person_or_family##",
" AND pub_p_partner.p_partner_key_n = pub_p_family.p_partner_key_n");
SqlStmt = SqlStmt.Replace("##exclude_familiy_members_existing_in_extract##",
"AND NOT EXISTS (SELECT pub_p_family.p_partner_key_n " +
" FROM pub_p_family, pub_p_person, pub_m_extract " +
" WHERE pub_p_person.p_family_key_n = pub_p_family.p_partner_key_n " +
" AND pub_m_extract.m_extract_id_i = " + AExtractId.ToString() +
" AND pub_m_extract.p_partner_key_n = pub_p_person.p_partner_key_n)");
// add address filter information to sql statement and parameter list
AddressFilterAdded = AddAddressFilter(AParameters, ref SqlStmt, ref SqlParameterList);
// now run the database query
TLogging.Log("Getting the data from the database...", TLoggingType.ToStatusBar);
partnerkeys.Clear();
partnerkeys = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "partners", ATransaction,
SqlParameterList.ToArray());
// filter data by postcode (if applicable)
ExtractQueryBase.PostcodeFilter(ref partnerkeys, ref AddressFilterAdded, AParameters, ATransaction);
// if this is taking a long time, every now and again update the TLogging statusbar, and check for the cancel button
// TODO: we might need to add this functionality to TExtractsHandling.CreateExtractFromListOfPartnerKeys as well???
if (AParameters.Get("CancelReportCalculation").ToBool() == true)
{
return(false);
}
TLogging.Log("Preparing the extract...", TLoggingType.ToStatusBar);
// create an extract with the given name in the parameters
TExtractsHandling.ExtendExtractFromListOfPartnerKeys(
AExtractId,
partnerkeys,
0,
AddressFilterAdded,
false);
ReturnValue = 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>S
protected override void RetrieveParameters(TParameterList AParameters, ref string ASqlStmt, ref List <OdbcParameter> ASQLParameterList)
{
List <String> param_relationships = new List <String>();
List <String> param_reciprocal_relationships = new List <String>();
foreach (var choice in AParameters.Get("param_explicit_relationships").ToString().Split(','))
{
param_relationships.Add(choice);
}
foreach (var choice in AParameters.Get("param_reciprocal_relationships").ToString().Split(','))
{
param_reciprocal_relationships.Add(choice);
}
if ((param_relationships.Count == 0) && (param_reciprocal_relationships.Count == 0))
{
throw new NoNullAllowedException("At least one option must be checked.");
}
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()
});
string paramName;
if (AParameters.Get("param_selection").ToString() == "an extract")
{
paramName = "param_extract";
ASQLParameterList.Add(new OdbcParameter(paramName, OdbcType.VarChar)
{
Value = AParameters.Get(paramName).ToString()
});
ASQLParameterList.Add(TDbListParameterValue.OdbcListParameterValue("param_relationships", OdbcType.VarChar, param_relationships));
ASQLParameterList.Add(new OdbcParameter(paramName, OdbcType.VarChar)
{
Value = AParameters.Get(paramName)
});
ASQLParameterList.Add(TDbListParameterValue.OdbcListParameterValue("param_reciprocal_relationships", OdbcType.VarChar,
param_reciprocal_relationships));
}
else
{
paramName = "param_partnerkey";
ASQLParameterList.Add(new OdbcParameter(paramName, OdbcType.Int)
{
Value = AParameters.Get(paramName).ToInt32()
});
ASQLParameterList.Add(TDbListParameterValue.OdbcListParameterValue("param_relationships", OdbcType.VarChar, param_relationships));
ASQLParameterList.Add(new OdbcParameter(paramName, OdbcType.Int)
{
Value = AParameters.Get(paramName).ToInt32()
});
ASQLParameterList.Add(TDbListParameterValue.OdbcListParameterValue("param_reciprocal_relationships", OdbcType.VarChar,
param_reciprocal_relationships));
}
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()
});
}
/// <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>S
protected override void RetrieveParameters(TParameterList AParameters, ref string ASqlStmt, ref List <OdbcParameter> ASQLParameterList)
{
ASQLParameterList.Add(new OdbcParameter("param_has_contactor", OdbcType.Bit)
{
Value = !string.IsNullOrWhiteSpace(AParameters.Get("param_contactor").ToString())
});
ASQLParameterList.Add(new OdbcParameter("param_contactor", OdbcType.VarChar)
{
Value = AParameters.Get("param_contactor").ToString()
});
ASQLParameterList.Add(new OdbcParameter("param_has_contact_code", OdbcType.Bit)
{
Value = !string.IsNullOrWhiteSpace(AParameters.Get("param_contact_code").ToString())
});
ASQLParameterList.Add(new OdbcParameter("param_contact_code", OdbcType.VarChar)
{
Value = AParameters.Get("param_contact_code").ToString()
});
ASQLParameterList.Add(new OdbcParameter("param_has_mailing_code", OdbcType.Bit)
{
Value = !string.IsNullOrWhiteSpace(AParameters.Get("param_mailing_code").ToString())
});
ASQLParameterList.Add(new OdbcParameter("param_mailing_code", OdbcType.VarChar)
{
Value = AParameters.Get("param_mailing_code").ToString()
});
ASQLParameterList.Add(new OdbcParameter("param_has_contact_attributes", OdbcType.Bit)
{
Value = !string.IsNullOrEmpty(AParameters.Get("param_contact_attributes").ToString())
});
ICollection <String> param_contact_attributes;
param_contact_attributes = AParameters.Get("param_contact_attributes").ToString().Split(new Char[] { ',', });
ASQLParameterList.Add(TDbListParameterValue.OdbcListParameterValue("param_contact_attributes", OdbcType.VarChar, param_contact_attributes));
ASQLParameterList.Add(new OdbcParameter("param_has_date_from", OdbcType.Bit)
{
Value = AParameters.Get("param_dtpDateFrom") != null && AParameters.Get("param_dtpDateFrom").ToDate() > DateTime.MinValue
});
ASQLParameterList.Add(new OdbcParameter("param_dtpDateFrom", OdbcType.Date)
{
Value = AParameters.Get("param_dtpDateFrom").ToDate()
});
ASQLParameterList.Add(new OdbcParameter("param_has_date_to", OdbcType.Bit)
{
Value = AParameters.Get("param_dtpDateTo") != null && AParameters.Get("param_dtpDateTo").ToDate() > DateTime.MinValue
});
ASQLParameterList.Add(new OdbcParameter("param_dtpDateTo", OdbcType.Date)
{
Value = AParameters.Get("param_dtpDateTo").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>
/// 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>S
protected override void RetrieveParameters(TParameterList AParameters, ref string ASqlStmt, ref List <OdbcParameter> ASQLParameterList)
{
ASQLParameterList.Add(new OdbcParameter("param_has_contactor", OdbcType.Bit)
{
Value = !string.IsNullOrWhiteSpace(AParameters.Get("param_contactor").ToString())
});
ASQLParameterList.Add(new OdbcParameter("param_contactor", OdbcType.VarChar)
{
Value = AParameters.Get("param_contactor").ToString()
});
ASQLParameterList.Add(new OdbcParameter("param_has_contact_code", OdbcType.Bit)
{
Value = !string.IsNullOrWhiteSpace(AParameters.Get("param_contact_code").ToString())
});
ASQLParameterList.Add(new OdbcParameter("param_contact_code", OdbcType.VarChar)
{
Value = AParameters.Get("param_contact_code").ToString()
});
ASQLParameterList.Add(new OdbcParameter("param_has_mailing_code", OdbcType.Bit)
{
Value = !string.IsNullOrWhiteSpace(AParameters.Get("param_mailing_code").ToString())
});
ASQLParameterList.Add(new OdbcParameter("param_mailing_code", OdbcType.VarChar)
{
Value = AParameters.Get("param_mailing_code").ToString()
});
List <String> param_contact_attributes = new List <String>();
foreach (TVariant choice in AParameters.Get("param_contact_attributes").ToComposite())
{
if (choice.ToString().Length > 0)
{
param_contact_attributes.Add(choice.ToString());
}
}
ASQLParameterList.Add(new OdbcParameter("param_has_contact_attributes", OdbcType.Bit)
{
Value = param_contact_attributes.Any()
});
ASQLParameterList.Add(TDbListParameterValue.OdbcListParameterValue("param_explicit_publication",
OdbcType.VarChar,
param_contact_attributes));
ASQLParameterList.Add(new OdbcParameter("param_has_date_from", OdbcType.Bit)
{
Value = AParameters.Get("param_date_from") != null && AParameters.Get("param_date_from").ToDate() > DateTime.MinValue
});
ASQLParameterList.Add(new OdbcParameter("param_date_from", OdbcType.Date)
{
Value = AParameters.Get("param_date_from").ToDate()
});
ASQLParameterList.Add(new OdbcParameter("param_has_date_to", OdbcType.Bit)
{
Value = AParameters.Get("param_date_to") != null && AParameters.Get("param_date_to").ToDate() > DateTime.MinValue
});
ASQLParameterList.Add(new OdbcParameter("param_date_to", OdbcType.Date)
{
Value = AParameters.Get("param_date_to").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()
});
}
