/// <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(); }