/// <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>
/// 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>
/// This method needs to be implemented by extracts that can't follow the default processing with just one query.
/// </summary>
/// <param name="AParameters"></param>
/// <param name="ATransaction"></param>
/// <param name="AExtractId"></param>
protected override bool RunSpecialTreatment(TParameterList AParameters, TDBTransaction ATransaction, out int AExtractId)
{
Boolean ReturnValue = false;
Int32 ExtractId = -1;
TDBTransaction Transaction = null;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.Serializable, ref Transaction,
delegate
{
DataTable giftdetails;
string SqlStmt = TDataBase.ReadSqlFile("Gift.Queries.ExtractDonorByAmount.sql");
List <OdbcParameter> SqlParameterList = new List <OdbcParameter>();
bool AddressFilterAdded;
DataTable partnerkeys = new DataTable();
// call to derived class to retrieve parameters specific for extract
RetrieveParameters(AParameters, ref SqlStmt, ref SqlParameterList);
// add address filter information to sql statement and parameter list
AddressFilterAdded = AddAddressFilter(AParameters, ref SqlStmt, ref SqlParameterList);
// now run the database query
TLogging.Log("Getting the data from the database...", TLoggingType.ToStatusBar);
giftdetails = DBAccess.GDBAccessObj.SelectDT(SqlStmt, "partners", Transaction,
SqlParameterList.ToArray());
// if this is taking a long time, every now and again update the TLogging statusbar, and check for the cancel button
// TODO: we might need to add this functionality to TExtractsHandling.CreateExtractFromListOfPartnerKeys as well???
if (AParameters.Get("CancelReportCalculation").ToBool() == true)
{
return;
}
TLogging.Log("Preparing the extract...", TLoggingType.ToStatusBar);
// With the result of the original query process the data and identify the partner keys for
// the extract.
partnerkeys.Columns.Add("0", typeof(Int64));
partnerkeys.Columns.Add("1", typeof(string));
partnerkeys.Columns.Add("p_site_key_n", typeof(Int64));
partnerkeys.Columns.Add("p_location_key_i", typeof(Int32));
ProcessGiftDetailRecords(giftdetails, AddressFilterAdded, AParameters, ref partnerkeys);
// filter data by postcode (if applicable)
ExtractQueryBase.PostcodeFilter(ref partnerkeys, ref AddressFilterAdded, AParameters, Transaction);
// create an extract with the given name in the parameters
ReturnValue = TExtractsHandling.CreateExtractFromListOfPartnerKeys(
AParameters.Get("param_extract_name").ToString(),
AParameters.Get("param_extract_description").ToString(),
out ExtractId,
partnerkeys,
0,
AddressFilterAdded);
});
AExtractId = ExtractId;
return(ReturnValue);
} // Run Special Treatment