/// <summary>
/// Main calculation of the publication statistical report.
/// </summary>
/// <param name="ACountryCode"></param>
/// <param name="APublicationCodes"></param>
private void CalculatePublicationStatisticalReport(String ACountryCode, string APublicationCodes)
{
DataTable PartnerTable = new DataTable();;
PLocationTable LocationTable;
PLocationRow LocationRow;
// codes should be surrounded by single quotes
APublicationCodes = APublicationCodes.Replace("\"", "'");
// load all active partners who have subscriptions for publications in the list, are a donor, are a church, are an applicant or are an ex-worker
string Query = "SELECT DISTINCT p_partner.*," +
// is partner a donor?
" CASE WHEN EXISTS (SELECT * FROM PUB_a_gift" +
" WHERE a_gift.p_donor_key_n = p_partner.p_partner_key_n) THEN 'yes'" +
" ELSE '' END AS " + COLUMN_DONOR + "," +
// is partner an ex-worker?
" CASE WHEN EXISTS (SELECT * FROM p_partner_type " +
" WHERE p_partner_type.p_partner_key_n = p_partner.p_partner_key_n" +
" AND p_partner_type.p_type_code_c LIKE 'EX-WORKER%') THEN 'yes'" +
" ELSE 'no' END AS " + COLUMN_EXPARTICIPANTS + "," +
// is partner an applicant?
" CASE WHEN EXISTS (SELECT * FROM p_partner_type " +
" WHERE p_partner_type.p_partner_key_n = p_partner.p_partner_key_n" +
" AND p_partner_type.p_type_code_c LIKE 'APPLIED%') THEN 'yes'" +
" ELSE 'no' END AS " + COLUMN_APPLICANTS +
" FROM p_partner" +
" WHERE p_partner.p_status_code_c = 'ACTIVE'" +
// if have subscriptions for publications in the list
" AND (EXISTS (SELECT * FROM p_subscription" +
" WHERE p_subscription.p_partner_key_n = p_partner.p_partner_key_n" +
" AND p_subscription.p_publication_code_c IN (" + APublicationCodes + "))" +
// if a donor
" OR EXISTS (SELECT * FROM PUB_a_gift" +
" WHERE a_gift.p_donor_key_n = p_partner.p_partner_key_n)" +
// if a church
" OR p_partner.p_partner_class_c = 'CHURCH'" +
// if an applicant or an ex-worker
" OR EXISTS (SELECT * FROM p_partner_type " +
" WHERE p_partner_type.p_partner_key_n = p_partner.p_partner_key_n" +
" AND (p_partner_type.p_type_code_c LIKE 'EX-WORKER%'" +
" OR p_partner_type.p_type_code_c LIKE 'APPLIED%')))";
PartnerTable = DBAccess.GDBAccessObj.SelectDT(PartnerTable, Query, situation.GetDatabaseConnection().Transaction);
// get total number of active partners
FNumberOfActivePartner = PPartnerAccess.CountViaPPartnerStatus("ACTIVE", situation.GetDatabaseConnection().Transaction);
PSubscriptionTable SubscriptionTable = new PSubscriptionTable();
// load all subscriptions for publications in the list
Query = "SELECT * FROM p_subscription" +
" WHERE p_subscription.p_publication_code_c IN (" + APublicationCodes + ")";
SubscriptionTable = (PSubscriptionTable)DBAccess.GDBAccessObj.SelectDT(SubscriptionTable, Query,
situation.GetDatabaseConnection().Transaction);
PPartnerLocationRow PartnerLocationRow;
Dictionary <String, int>SubscriptionCounter = new Dictionary <String, int>();
InitSubscriptionCounter(ref SubscriptionCounter);
Dictionary <String, int>CountyRowList = InitStatisticalReportTable();
// foreach partner that satisfied the above criteria
foreach (DataRow PartnerRow in PartnerTable.Rows)
{
Int64 PartnerKey = Convert.ToInt64(PartnerRow[PPartnerTable.GetPartnerKeyDBName()]);
if (GetPartnerBestAddressRow(PartnerKey, situation, out PartnerLocationRow))
{
if (PartnerLocationRow == null)
{
continue;
}
if ((!PartnerLocationRow.IsDateGoodUntilNull())
&& (PartnerLocationRow.DateGoodUntil < System.DateTime.Today))
{
// Best address is no longer valid - don't use it
continue;
}
LocationTable = PLocationAccess.LoadByPrimaryKey(PartnerLocationRow.SiteKey,
PartnerLocationRow.LocationKey, situation.GetDatabaseConnection().Transaction);
if (LocationTable.Rows.Count < 1)
{
continue;
}
LocationRow = LocationTable[0];
// TODO what do we do with partners that have 0 as Location key?
if (LocationRow.LocationKey == 0)
{
continue;
}
String RowName = ROW_FOREIGN;
// Use county if in this country, else foreign
if ((!LocationRow.IsCountryCodeNull())
&& (LocationRow.CountryCode == ACountryCode))
{
// partner is OK
if (LocationRow.County.Length > 0)
{
// County
RowName = LocationRow.County.ToLower().Trim();
}
else
{
// *NONE*
RowName = ROW_NONE;
}
}
// get the subscriptions that this partner receives (if any)
DataRow[] Subscriptions = SubscriptionTable.Select(PPartnerTable.GetPartnerKeyDBName() + " = " + PartnerKey);
foreach (DataRow Row in Subscriptions)
{
PSubscriptionRow SubscriptionRow = (PSubscriptionRow)Row;
// if there is a cancelled date set, then don't use this subscription in the report
if (SubscriptionRow.IsDateCancelledNull()
&& ((SubscriptionRow.SubscriptionStatus == "PROVISIONAL")
|| (SubscriptionRow.SubscriptionStatus == "PERMANENT")
|| (SubscriptionRow.SubscriptionStatus == "GIFT")))
{
// Add Value to Table
AddToStatisticalReportTable(CountyRowList[RowName], SubscriptionRow.PublicationCode, 1);
// Add number of copies to overall "Count:" column
AddToStatisticalReportTable(CountyRowList[ROW_COUNT], SubscriptionRow.PublicationCode, SubscriptionRow.PublicationCopies);
}
}
// partner is a donor
if (PartnerRow[COLUMN_DONOR].ToString() == "yes")
{
AddToStatisticalReportTable(CountyRowList[RowName], COLUMN_DONOR, 1);
}
// partner is church
if (PartnerRow[PPartnerTable.GetPartnerClassDBName()].ToString() == "CHURCH")
{
AddToStatisticalReportTable(CountyRowList[RowName], COLUMN_CHURCH, 1);
}
// partner is an applicant
if (PartnerRow[COLUMN_APPLICANTS].ToString() == "yes")
{
AddToStatisticalReportTable(CountyRowList[RowName], COLUMN_APPLICANTS, 1);
}
// partner is an ex-worker
if (PartnerRow[COLUMN_EXPARTICIPANTS].ToString() == "yes")
{
AddToStatisticalReportTable(CountyRowList[RowName], COLUMN_EXPARTICIPANTS, 1);
}
}
} // end for each
CalculateTotals();
}