public static PartnerEditTDS GetPartnerDetails(Int64 APartnerKey, bool AWithSubscriptions, bool AWithRelationships,
out string APrimaryPhoneNumber, out string APrimaryEmailAddress)
{
// Call the standard method including address details
List <string> Dummy1, Dummy2;
string Dummy3, Dummy4, Dummy5;
PartnerEditTDS MainDS = GetPartnerDetails(APartnerKey, out Dummy1, out Dummy2, out Dummy3, out Dummy4, out Dummy5);
TDBTransaction ReadTransaction = new TDBTransaction();
string PrimaryPhoneNumber = String.Empty;
string PrimaryEmailAddress = String.Empty;
DBAccess.ReadTransaction(ref ReadTransaction,
delegate
{
// Now get the primary email and phone
PPartnerAttributeTable attributeTable = TContactDetailsAggregate.GetPartnersContactDetailAttributes(APartnerKey);
Calculations.GetPrimaryEmailAndPrimaryPhone(ReadTransaction, attributeTable, out PrimaryPhoneNumber, out PrimaryEmailAddress);
});
APrimaryPhoneNumber = PrimaryPhoneNumber;
APrimaryEmailAddress = PrimaryEmailAddress;
return(MainDS);
}
/// <summary>
/// Gets the 'Primary Email Address'.
/// </summary>
/// <returns>The 'Primary Email Address', or <see cref="String.Empty" /> in case the Partner hasn't got one.</returns>
public static string GetBestEmailAddress(Int64 APartnerKey)
{
string EmailAddress = String.Empty;
if (!TContactDetailsAggregate.GetPrimaryEmailAddress(APartnerKey, out EmailAddress))
{
EmailAddress = String.Empty;
}
return(EmailAddress);
}
public static PartnerEditTDS GetPartnerDetails(Int64 APartnerKey, bool AWithSubscriptions, bool AWithRelationships,
out string APrimaryPhoneNumber, out string APrimaryEmailAddress)
{
// Call the standard method including address details
PartnerEditTDS MainDS = GetPartnerDetails(APartnerKey, true, AWithSubscriptions, AWithRelationships);
// Now get the primary email and phone
PPartnerAttributeTable attributeTable = TContactDetailsAggregate.GetPartnersContactDetailAttributes(APartnerKey);
Calculations.GetPrimaryEmailAndPrimaryPhone(attributeTable, out APrimaryPhoneNumber, out APrimaryEmailAddress);
return MainDS;
}
/// <summary>
/// Gets the 'Primary Email Address'.
/// </summary>
/// <returns>The 'Primary Email Address', or <see cref="String.Empty" /> in case the Partner hasn't got one.</returns>
public static string GetBestEmailAddress(Int64 APartnerKey)
{
string EmailAddress = String.Empty;
TDBTransaction Transaction = new TDBTransaction();
DBAccess.ReadTransaction(
ref Transaction,
delegate
{
if (!TContactDetailsAggregate.GetPrimaryEmailAddress(Transaction, APartnerKey, out EmailAddress))
{
EmailAddress = String.Empty;
}
});
return(EmailAddress);
}
/// <summary>
/// Add the address details of the supporting church of a partner to the results
/// </summary>
/// <param name="APartnerKey">The partner key of whom the supporting church details should be added</param>
/// <returns></returns>
private bool GetChurch(Int64 APartnerKey)
{
PPartnerRelationshipTable RelationshipTable;
PPartnerTable ChurchTable;
string PhoneNumber;
string EmailAddress;
Dictionary <String, String> GatheredResults = new Dictionary <String, String>();
PPartnerRelationshipRow TemplateRow = new PPartnerRelationshipTable().NewRowTyped(false);
TemplateRow.RelationKey = APartnerKey;
TemplateRow.RelationName = "SUPPCHURCH";
RelationshipTable = PPartnerRelationshipAccess.LoadUsingTemplate(TemplateRow, situation.GetDatabaseConnection().Transaction);
bool IsFirstAddress = true;
foreach (PPartnerRelationshipRow Row in RelationshipTable.Rows)
{
ChurchTable = PPartnerAccess.LoadByPrimaryKey(Row.PartnerKey, situation.GetDatabaseConnection().Transaction);
if (ChurchTable.Rows.Count < 1)
{
continue;
}
PPartnerLocationRow PartnerLocationRow;
PLocationTable LocationTable;
if (!TRptUserFunctionsPartner.GetPartnerBestAddressRow(Row.PartnerKey, situation, out PartnerLocationRow))
{
continue;
}
LocationTable = PLocationAccess.LoadByPrimaryKey(PartnerLocationRow.SiteKey,
PartnerLocationRow.LocationKey, situation.GetDatabaseConnection().Transaction);
if (LocationTable.Rows.Count < 1)
{
continue;
}
if (IsFirstAddress)
{
GatheredResults.Add("Church-Name", ((PPartnerRow)ChurchTable.Rows[0]).PartnerShortName);
}
else
{
GatheredResults["Church-Name"] += ", " + ((PPartnerRow)ChurchTable.Rows[0]).PartnerShortName + " ";
}
// Add this church address to the results
// the variables will be something like Church-PostalCode, Church-StreetName
// get the location details into the parameters
foreach (DataColumn col in LocationTable.Columns)
{
if (IsFirstAddress)
{
GatheredResults.Add("Church-" + StringHelper.UpperCamelCase(col.ColumnName, true, true),
LocationTable.Rows[0][col.ColumnName].ToString());
}
else
{
GatheredResults["Church-" + StringHelper.UpperCamelCase(col.ColumnName, true, true)] +=
", " + LocationTable.Rows[0][col.ColumnName].ToString();
}
}
if (IsFirstAddress)
{
// also put the phone number and email etc into the parameters
TContactDetailsAggregate.GetPrimaryEmailAndPrimaryPhone(Row.PartnerKey,
out PhoneNumber, out EmailAddress);
// Add Calculation Parameter for 'Primary Email Address' (String.Empty is supplied if the Partner hasn't got one)
situation.GetParameters().AddCalculationParameter("Church-EmailAddress",
new TVariant(EmailAddress ?? String.Empty));
// Add Calculation Parameter for 'Primary Phone Number' (String.Empty is supplied if the Partner hasn't got one)
situation.GetParameters().AddCalculationParameter("Church-Telephone",
new TVariant(PhoneNumber ?? String.Empty));
// At present we no longer support the reporting of the following, so we set those Calculation Parameters to String.Empty
GatheredResults.Add("Church-FaxNumber", String.Empty);
GatheredResults.Add("Church-MobileNumber", String.Empty);
GatheredResults.Add("Church-AlternateTelephone", String.Empty);
}
IsFirstAddress = false;
}
if (IsFirstAddress)
{
situation.GetParameters().RemoveVariable("Church-Telephone");
situation.GetParameters().RemoveVariable("Church-FaxNumber");
situation.GetParameters().RemoveVariable("Church-EmailAddress");
situation.GetParameters().RemoveVariable("Church-MobileNumber");
situation.GetParameters().RemoveVariable("Church-AlternateTelephone");
situation.GetParameters().RemoveVariable("Church-Name");
situation.GetParameters().RemoveVariable("Church-Locality");
situation.GetParameters().RemoveVariable("Church-Address3");
situation.GetParameters().RemoveVariable("Church-City");
situation.GetParameters().RemoveVariable("Church-CountryCode");
situation.GetParameters().RemoveVariable("Church-County");
situation.GetParameters().RemoveVariable("Church-PostalCode");
situation.GetParameters().RemoveVariable("Church-StreetName");
}
else
{
foreach (KeyValuePair <String, String> kvp in GatheredResults)
{
situation.GetParameters().Add(kvp.Key, new TVariant(kvp.Value));
}
}
return(true);
}
/// <summary>
/// Retrieves data that will be shown on the 'Overview' UserControl and adds it to <paramref name="AIndividualDataDS" />.
/// </summary>
/// <param name="APartnerKey">PartnerKey of the Person to load data for.</param>
/// <param name="AIndividualDataDS">Typed DataSet of Type <see cref="IndividualDataTDS" />. Needs to be instantiated already!</param>
/// <param name="AReadTransaction">Open Database transaction.</param>
/// <returns>void</returns>
private static void BuildSummaryData(Int64 APartnerKey, ref IndividualDataTDS AIndividualDataDS, TDBTransaction AReadTransaction)
{
string StrNotAvailable = Catalog.GetString("Not Available");
IndividualDataTDSSummaryDataTable SummaryDT;
IndividualDataTDSSummaryDataRow SummaryDR;
IndividualDataTDSMiscellaneousDataRow MiscellaneousDataDR = AIndividualDataDS.MiscellaneousData[0];
PPersonTable PPersonDT;
PPersonRow PersonDR = null;
PmPassportDetailsTable PassportDetailsDT;
PmStaffDataTable PmStaffDataDT;
PmStaffDataRow PmStaffDataDR = null;
PmJobAssignmentTable PmJobAssignmentDT = null;
PUnitTable PUnitDT = null;
PmJobAssignmentRow PmJobAssignmentDR;
IndividualDataTDSJobAssignmentStaffDataCombinedRow JobAssiStaffDataCombDR;
int JobAssiStaffDataCombKey = 0;
TCacheable CommonCacheable = new TCacheable();
TPartnerCacheable PartnerCacheable = new TPartnerCacheable();
string MaritalStatusDescr;
StringCollection PassportColumns;
string Nationalities;
PPartnerRelationshipTable PartnerRelationshipDT;
PPartnerTable PartnerDT;
PPartnerRow PartnerDR = null;
PLocationRow LocationDR;
PPartnerLocationRow PartnerLocationDR;
string PhoneNumber;
string EmailAddress;
Int64 ChurchPartnerKey;
SummaryDT = new IndividualDataTDSSummaryDataTable();
SummaryDR = SummaryDT.NewRowTyped(false);
SummaryDR.PartnerKey = APartnerKey;
#region Person Info
PPersonDT = PPersonAccess.LoadByPrimaryKey(APartnerKey, AReadTransaction);
if (PPersonDT.Rows.Count == 1)
{
PersonDR = (PPersonRow)PPersonDT.Rows[0];
}
if (PersonDR != null)
{
SummaryDR.DateOfBirth = PersonDR.DateOfBirth;
SummaryDR.Gender = PersonDR.Gender;
MaritalStatusDescr = PartnerCodeHelper.GetMaritalStatusDescription(
@PartnerCacheable.GetCacheableTable, PersonDR.MaritalStatus);
if (MaritalStatusDescr != String.Empty)
{
MaritalStatusDescr = " - " + MaritalStatusDescr;
}
SummaryDR.MaritalStatus = PersonDR.MaritalStatus + MaritalStatusDescr;
}
else
{
SummaryDR.SetDateOfBirthNull();
SummaryDR.Gender = StrNotAvailable;
SummaryDR.MaritalStatus = StrNotAvailable;
}
#region Nationalities
PassportColumns = StringHelper.StrSplit(
PmPassportDetailsTable.GetDateOfIssueDBName() + "," +
PmPassportDetailsTable.GetDateOfExpirationDBName() + "," +
PmPassportDetailsTable.GetPassportNationalityCodeDBName() + "," +
PmPassportDetailsTable.GetMainPassportDBName(), ",");
PassportDetailsDT = PmPassportDetailsAccess.LoadViaPPerson(APartnerKey,
PassportColumns, AReadTransaction, null, 0, 0);
Nationalities = Ict.Petra.Shared.MPersonnel.Calculations.DeterminePersonsNationalities(
@CommonCacheable.GetCacheableTable, PassportDetailsDT);
if (Nationalities != String.Empty)
{
SummaryDR.Nationalities = Nationalities;
}
else
{
SummaryDR.Nationalities = StrNotAvailable;
}
#endregion
#region Phone and Email (from 'Best Address')
if (TContactDetailsAggregate.GetPrimaryEmailAndPrimaryPhone(APartnerKey, out PhoneNumber, out EmailAddress))
{
SummaryDR.PrimaryPhoneNumber = PhoneNumber ?? StrNotAvailable;
SummaryDR.PrimaryEmailAddress = EmailAddress ?? StrNotAvailable;
}
else
{
SummaryDR.PrimaryPhoneNumber = StrNotAvailable;
SummaryDR.PrimaryEmailAddress = StrNotAvailable;
}
#endregion
#endregion
#region Commitments/Jobs
PmStaffDataDT = PmStaffDataAccess.LoadViaPPerson(APartnerKey, AReadTransaction);
MiscellaneousDataDR.ItemsCountCommitmentPeriods = PmStaffDataDT.Rows.Count;
// First check if the PERSON has got any Commitments
if (PmStaffDataDT.Rows.Count > 0)
{
foreach (DataRow DR in PmStaffDataDT.Rows)
{
JobAssiStaffDataCombDR = AIndividualDataDS.JobAssignmentStaffDataCombined.NewRowTyped(false);
JobAssiStaffDataCombDR.Key = JobAssiStaffDataCombKey++;
JobAssiStaffDataCombDR.PartnerKey = APartnerKey;
PmStaffDataDR = (PmStaffDataRow)DR;
if (!(PmStaffDataDR.IsReceivingFieldNull()) &&
(PmStaffDataDR.ReceivingField != 0))
{
PUnitDT = PUnitAccess.LoadByPrimaryKey(PmStaffDataDR.ReceivingField, AReadTransaction);
JobAssiStaffDataCombDR.FieldKey = PmStaffDataDR.ReceivingField;
JobAssiStaffDataCombDR.FieldName = PUnitDT[0].UnitName;
}
else
{
JobAssiStaffDataCombDR.FieldKey = 0;
JobAssiStaffDataCombDR.FieldName = "[None]";
}
JobAssiStaffDataCombDR.Position = PmStaffDataDR.JobTitle;
JobAssiStaffDataCombDR.FromDate = PmStaffDataDR.StartOfCommitment;
JobAssiStaffDataCombDR.ToDate = PmStaffDataDR.EndOfCommitment;
AIndividualDataDS.JobAssignmentStaffDataCombined.Rows.Add(JobAssiStaffDataCombDR);
}
}
else
{
// The PERSON hasn't got any Commitments, therefore check if the PERSON has any Job Assignments
PmJobAssignmentDT = PmJobAssignmentAccess.LoadViaPPartner(APartnerKey, AReadTransaction);
if (PmJobAssignmentDT.Rows.Count > 0)
{
foreach (DataRow DR in PmJobAssignmentDT.Rows)
{
JobAssiStaffDataCombDR = AIndividualDataDS.JobAssignmentStaffDataCombined.NewRowTyped(false);
JobAssiStaffDataCombDR.Key = JobAssiStaffDataCombKey++;
JobAssiStaffDataCombDR.PartnerKey = APartnerKey;
PmJobAssignmentDR = (PmJobAssignmentRow)DR;
if (PmJobAssignmentDR.UnitKey != 0)
{
PUnitDT = PUnitAccess.LoadByPrimaryKey(PmJobAssignmentDR.UnitKey, AReadTransaction);
JobAssiStaffDataCombDR.FieldKey = PmJobAssignmentDR.UnitKey;
JobAssiStaffDataCombDR.FieldName = PUnitDT[0].UnitName;
}
else
{
JobAssiStaffDataCombDR.FieldKey = 0;
JobAssiStaffDataCombDR.FieldName = "[None]";
}
JobAssiStaffDataCombDR.Position = PmJobAssignmentDR.PositionName;
JobAssiStaffDataCombDR.FromDate = PmJobAssignmentDR.FromDate;
JobAssiStaffDataCombDR.ToDate = PmJobAssignmentDR.ToDate;
AIndividualDataDS.JobAssignmentStaffDataCombined.Rows.Add(JobAssiStaffDataCombDR);
}
}
}
#endregion
#region Church Info
SummaryDR.ChurchName = StrNotAvailable;
SummaryDR.ChurchAddress = StrNotAvailable;
SummaryDR.ChurchPrimaryPhoneNumber = StrNotAvailable;
SummaryDR.ChurchPastor = StrNotAvailable;
SummaryDR.ChurchPastorsPrimaryPhoneNumber = StrNotAvailable;
SummaryDR.NumberOfShownSupportingChurchPastors = 0;
// Find SUPPCHURCH Relationship
PartnerRelationshipDT = PPartnerRelationshipAccess.LoadUsingTemplate(new TSearchCriteria[] {
new TSearchCriteria(PPartnerRelationshipTable.GetRelationKeyDBName(), APartnerKey),
new TSearchCriteria(PPartnerRelationshipTable.GetRelationNameDBName(), "SUPPCHURCH")
},
AReadTransaction);
SummaryDR.NumberOfShownSupportingChurches = PartnerRelationshipDT.Rows.Count;
if (PartnerRelationshipDT.Rows.Count > 0)
{
ChurchPartnerKey = PartnerRelationshipDT[0].PartnerKey;
// Load Church Partner
PartnerDT = PPartnerAccess.LoadByPrimaryKey(ChurchPartnerKey, AReadTransaction);
if (PartnerDT.Rows.Count > 0)
{
PartnerDR = PartnerDT[0];
// Church Name
if (PartnerDR.PartnerShortName != String.Empty)
{
SummaryDR.ChurchName = PartnerDR.PartnerShortName;
}
#region Church Address and Phone
ServerCalculations.DetermineBestAddress(PartnerRelationshipDT[0].PartnerKey, out PartnerLocationDR, out LocationDR);
if (LocationDR != null)
{
SummaryDR.ChurchAddress = Calculations.DetermineLocationString(LocationDR,
Calculations.TPartnerLocationFormatEnum.plfCommaSeparated);
}
if (TContactDetailsAggregate.GetPrimaryPhoneNumber(PartnerRelationshipDT[0].PartnerKey,
out PhoneNumber))
{
SummaryDR.ChurchPrimaryPhoneNumber = PhoneNumber ?? StrNotAvailable;
}
else
{
SummaryDR.ChurchPrimaryPhoneNumber = StrNotAvailable;
}
#endregion
#region Pastor
// Find PASTOR Relationship
PartnerRelationshipDT.Rows.Clear();
PartnerRelationshipDT = PPartnerRelationshipAccess.LoadUsingTemplate(new TSearchCriteria[] {
new TSearchCriteria(PPartnerRelationshipTable.GetPartnerKeyDBName(), ChurchPartnerKey),
new TSearchCriteria(PPartnerRelationshipTable.GetRelationNameDBName(), "PASTOR")
},
AReadTransaction);
SummaryDR.NumberOfShownSupportingChurchPastors = PartnerRelationshipDT.Rows.Count;
if (PartnerRelationshipDT.Rows.Count > 0)
{
// Load PASTOR Partner
PartnerDT = PPartnerAccess.LoadByPrimaryKey(PartnerRelationshipDT[0].RelationKey, AReadTransaction);
if (PartnerDT.Rows.Count > 0)
{
PartnerDR = PartnerDT[0];
// Pastor's Name
if (PartnerDR.PartnerShortName != String.Empty)
{
SummaryDR.ChurchPastor = PartnerDR.PartnerShortName;
}
#region Pastor's Phone
if (TContactDetailsAggregate.GetPrimaryPhoneNumber(PartnerRelationshipDT[0].RelationKey,
out PhoneNumber))
{
SummaryDR.ChurchPastorsPrimaryPhoneNumber = PhoneNumber ?? StrNotAvailable;
}
else
{
SummaryDR.ChurchPastorsPrimaryPhoneNumber = StrNotAvailable;
}
#endregion
}
}
#endregion
}
}
#endregion
// Add Summary DataRow to Summary DataTable
SummaryDT.Rows.Add(SummaryDR);
// Add Row to 'SummaryData' DataTable in Typed DataSet 'IndividualDataTDS'
AIndividualDataDS.Merge(SummaryDT);
}
/// <summary>
/// Gets the 'Primary Email Address' and some location details of the 'Best Address'.
/// </summary>
/// <returns>The 'Primary Email Address', or <see cref="String.Empty" /> in case the Partner hasn't got one.</returns>
public static string GetBestEmailAddressWithDetails(Int64 APartnerKey, out PLocationTable AAddress, out string ACountryNameLocal)
{
string EmailAddress = String.Empty;
PLocationTable Address = null;
string CountryNameLocal = "";
TDBTransaction Transaction = null;
bool FoundBestAddress = false;
DBAccess.GDBAccessObj.GetNewOrExistingAutoReadTransaction(IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
DataSet PartnerLocationsDS = new DataSet();
PartnerLocationsDS.Tables.Add(new PPartnerLocationTable());
PartnerLocationsDS.Tables.Add(new PCountryTable());
DataTable PartnerLocationTable = PartnerLocationsDS.Tables[PPartnerLocationTable.GetTableName()];
PCountryTable CountryTable = (PCountryTable)PartnerLocationsDS.Tables[PCountryTable.GetTableName()];
CountryTable.DefaultView.Sort = PCountryTable.GetCountryCodeDBName();
// add special column BestAddress and Icon
PartnerLocationTable.Columns.Add(new System.Data.DataColumn("BestAddress", typeof(Boolean)));
PartnerLocationTable.Columns.Add(new System.Data.DataColumn("Icon", typeof(Int32)));
// find all locations of the partner, put it into a dataset
PPartnerLocationAccess.LoadViaPPartner(PartnerLocationsDS, APartnerKey, Transaction);
Ict.Petra.Shared.MPartner.Calculations.DeterminePartnerLocationsDateStatus(PartnerLocationsDS);
Ict.Petra.Shared.MPartner.Calculations.DetermineBestAddress(PartnerLocationsDS);
foreach (PPartnerLocationRow row in PartnerLocationTable.Rows)
{
// find the row with BestAddress = 1
if (Convert.ToInt32(row["BestAddress"]) == 1)
{
FoundBestAddress = true;
// we also want the post address, need to load the p_location table:
Address = PLocationAccess.LoadByPrimaryKey(row.SiteKey, row.LocationKey, Transaction);
if (CountryTable.DefaultView.Find(Address[0].CountryCode) == -1)
{
CountryTable.Merge(PCountryAccess.LoadByPrimaryKey(Address[0].CountryCode, Transaction));
}
CountryNameLocal = CountryTable[CountryTable.DefaultView.Find(Address[0].CountryCode)].CountryNameLocal;
}
}
if (FoundBestAddress)
{
if (!TContactDetailsAggregate.GetPrimaryEmailAddress(APartnerKey, out EmailAddress))
{
EmailAddress = String.Empty;
}
}
});
AAddress = Address;
ACountryNameLocal = CountryNameLocal;
return(EmailAddress);
}
public static DataSet GiftsOverMinimum(Dictionary <String, TVariant> AParameters, TReportingDbAdapter DbAdapter)
{
int LedgerNumber = AParameters["param_ledger_number_i"].ToInt32();
String StartDate = AParameters["param_start_date"].ToDate().ToString("yyyy-MM-dd");
String EndDate = AParameters["param_end_date"].ToDate().ToString("yyyy-MM-dd");
String MinimumAmount = AParameters["param_minimum_amount"].ToString();
String CurrencyField = (AParameters["param_currency"].ToString().ToUpper() == "BASE" ? "a_gift_amount_n" : "a_gift_amount_intl_n");
String DonorExclude = "";
String MotivationQuery = "";
TDBTransaction Transaction = null;
DataTable Gifts = new DataTable();
DataTable Donors = new DataTable();
DataTable Contacts = new DataTable();
DataSet Results = new DataSet();
#if DEBUG
foreach (String key in AParameters.Keys)
{
TLogging.Log(key + " => " + AParameters[key].ToString());
}
#endif
if (AParameters["param_exclude_anonymous_donors"].ToBool())
{
DonorExclude += "AND Donor.p_anonymous_donor_l = 0 ";
}
if (AParameters["param_exclude_no_solicitations"].ToBool())
{
DonorExclude += "AND Donor.p_no_solicitations_l = 0 ";
}
if (!AParameters["param_all_motivation_groups"].ToBool())
{
MotivationQuery += String.Format("AND a_gift_detail.a_motivation_group_code_c IN ({0}) ",
AParameters["param_motivation_group_quotes"]);
}
if (!AParameters["param_all_motivation_details"].ToBool())
{
MotivationQuery += String.Format("AND (a_gift_detail.a_motivation_group_code_c, a_gift_detail.a_motivation_detail_code_c) IN ({0}) ",
AParameters["param_motivation_group_detail_pairs"]);
}
DbAdapter.FPrivateDatabaseObj.GetNewOrExistingAutoReadTransaction(
IsolationLevel.ReadCommitted,
TEnforceIsolationLevel.eilMinimum,
ref Transaction,
delegate
{
String Query =
@"
WITH Details AS (
SELECT
a_gift.p_donor_key_n AS DonorKey
-- We need to join to Donor to check p_anonymous_donor_l and p_no_solicitations_l for the query, so we may as well pull Name etc.
-- at the same time, to avoid doing a second Donor query later. But we'll consolidate this duplicated data into another DataTable
-- to return it to the client.
, Donor.p_partner_short_name_c AS DonorName
, Donor.p_partner_class_c AS DonorClass
, Donor.p_receipt_letter_frequency_c AS ReceiptFrequency
, a_gift.a_date_entered_d AS GiftDate
, a_gift_detail.a_confidential_gift_flag_l AS Confidential
, a_gift_detail.a_motivation_group_code_c AS MotivationGroup
, a_gift_detail.a_motivation_detail_code_c AS MotivationDetail
, a_motivation_group.a_motivation_group_description_c AS MotivationGroupDescription
, a_motivation_detail.a_motivation_detail_desc_c AS MotivationDetailDescription
, a_gift_detail.p_recipient_key_n AS RecipientKey
, a_gift_detail."
+
CurrencyField + @" AS GiftAmount
, sum(a_gift_detail." + CurrencyField +
@") OVER (PARTITION BY a_gift.p_donor_key_n) AS TotalAmount
FROM
a_gift
INNER JOIN
p_partner AS Donor
ON
(Donor.p_partner_key_n = a_gift.p_donor_key_n)
INNER JOIN
a_gift_detail
USING
(a_ledger_number_i, a_batch_number_i, a_gift_transaction_number_i)
INNER JOIN
a_motivation_group
USING
(a_ledger_number_i, a_motivation_group_code_c)
INNER JOIN
a_motivation_detail
USING
(a_ledger_number_i, a_motivation_group_code_c, a_motivation_detail_code_c)
WHERE
a_gift.a_ledger_number_i = "
+
LedgerNumber + @"
AND a_gift.a_date_entered_d BETWEEN '" + StartDate + "' AND '" +
EndDate +
@"'
-- I hope a_dont_report_l gets converted to a_report_l to avoid this horrible double negative:
AND a_motivation_detail.a_dont_report_l = False
"
+
MotivationQuery + DonorExclude +
@"
-- For OM Germany, exclude donors 99000000 and 27002909 (SHKI and anonymous UNBEKANNT)
AND NOT ((a_gift.a_ledger_number_i = 27 OR a_gift.a_ledger_number_i = 90 OR a_gift.a_ledger_number_i = 99)
AND (a_gift.p_donor_key_n = 99000000 OR a_gift.p_donor_key_n = 27002909))
)
SELECT
Details.*
, Recipient.p_partner_short_name_c AS RecipientName
FROM
Details
INNER JOIN
p_partner AS Recipient
ON
(Recipient.p_partner_key_n = Details.RecipientKey)
WHERE
TotalAmount >= "
+
MinimumAmount +
@"
ORDER BY
Details.DonorName
;
";
#if DEBUG
TLogging.Log(Query);
#endif
Gifts = DbAdapter.RunQuery(Query, "GiftsOverMinimum", Transaction);
#if DEBUG
TLogging.Log("Query finished");
#endif
// Get the donors' addresses
const int DONOR_KEY = 0;
const int DONOR_ADDR = 5;
const int DONOR_POSTCODE = 6;
const int DONOR_PHONE = 7;
const int DONOR_EMAIL = 8;
Donors = Gifts.DefaultView.ToTable("Donors", true, "DonorKey", "DonorName", "DonorClass", "ReceiptFrequency", "TotalAmount");
Donors.Columns.Add("Address", typeof(String));
Donors.Columns.Add("PostalCode", typeof(String));
Donors.Columns.Add("Phone", typeof(String));
Donors.Columns.Add("Email", typeof(String));
// Having copied the distinct names and totals from Gifts to Donors, we no longer need to pass their duplicated data back to the client
foreach (String col in new String[] { "DonorName", "DonorClass", "ReceiptFrequency", "TotalAmount" })
{
Gifts.Columns.Remove(col);
}
PLocationTable Address;
String Country, EmailAddress, PhoneNumber, FaxNumber;
List <String> DonorList = new List <string>();
#if DEBUG
TLogging.Log("Processing addresses");
#endif
foreach (DataRow Donor in Donors.Rows)
{
DonorList.Add(Donor[DONOR_KEY].ToString());
if (TAddressTools.GetBestAddress((Int64)Donor[DONOR_KEY], out Address, out Country, Transaction))
{
Donor[DONOR_ADDR] =
Calculations.DetermineLocationString(Address[0], Calculations.TPartnerLocationFormatEnum.plfCommaSeparated);
Donor[DONOR_POSTCODE] = Address[0]["p_postal_code_c"];
}
else
{
Donor[DONOR_ADDR] = "";
Donor[DONOR_POSTCODE] = "";
}
TContactDetailsAggregate.GetPrimaryEmailAndPrimaryPhoneAndFax((Int64)Donor[DONOR_KEY], out PhoneNumber, out EmailAddress,
out FaxNumber);
Donor[DONOR_PHONE] = PhoneNumber;
Donor[DONOR_EMAIL] = EmailAddress;
}
if (DonorList.Count == 0)
{
DonorList.Add("null");
}
#if DEBUG
TLogging.Log(
"Addresses finished");
#endif
// Get the most recent contacts with each donor
Query =
@"
WITH Contacts AS (
SELECT
row_number() OVER (PARTITION BY p_partner_contact.p_partner_key_n ORDER BY s_contact_date_d DESC, s_contact_time_i DESC) AS RowID
, p_partner_contact.p_partner_key_n AS DonorKey
, p_contact_log.p_contactor_c AS Contactor
, p_contact_log.s_contact_date_d AS ContactDate
, p_contact_log.s_contact_time_i AS ContactTime
, p_contact_log.s_contact_time_i * '1 second'::interval AS Time
, p_contact_log.p_contact_code_c AS ContactCode
, p_contact_log.p_contact_comment_c AS Comment
FROM
p_partner_contact
INNER JOIN
p_contact_log
USING
(p_contact_log_id_i)
WHERE
p_partner_key_n in ("
+
String.Join(",",
DonorList) +
@")
ORDER BY
DonorKey,
RowID
)
SELECT
*
FROM
Contacts
WHERE
Contacts.RowID <= "
+
AParameters["param_max_contacts"] + @";
";
#if DEBUG
TLogging.Log(Query);
#endif
Contacts = DbAdapter.RunQuery(Query, "Contacts", Transaction);
#if DEBUG
TLogging.Log("Query finished");
#endif
if (DbAdapter.IsCancelled)
{
Results = null;
return;
}
}); // GetNewOrExistingAutoReadTransaction
Results.Tables.Add(Gifts);
Results.Tables.Add(Donors);
Results.Tables.Add(Contacts);
return(Results);
}
