From 335038eb35852988d4622e9e1cf254d6b8f6c002 Mon Sep 17 00:00:00 2001 From: Pratik Joshi Date: Tue, 27 Aug 2013 19:02:29 +0530 Subject: [PATCH] CRM-13026-handling-of-showing-scheduled-recipients-as-well, also added a new filter for mailing job status --- CRM/Core/TableHierarchy.php | 17 ++-- CRM/Mailing/BAO/Query.php | 81 ++++++++++++++------ CRM/Mailing/Form/Task.php | 4 +- CRM/Mailing/Selector/Search.php | 6 +- templates/CRM/Mailing/Form/Search/Common.tpl | 8 +- 5 files changed, 78 insertions(+), 38 deletions(-) diff --git a/CRM/Core/TableHierarchy.php b/CRM/Core/TableHierarchy.php index c1048e58fb..36e5608b43 100644 --- a/CRM/Core/TableHierarchy.php +++ b/CRM/Core/TableHierarchy.php @@ -58,7 +58,7 @@ class CRM_Core_TableHierarchy { 'civicrm_entity_tag' => '22', 'civicrm_note' => '23', 'civicrm_contribution' => '24', - 'civicrm_financial_type' => '25', + 'civicrm_financial_type' => '25', 'civicrm_participant' => '26', 'civicrm_event' => '27', 'civicrm_worldregion' => '28', @@ -68,13 +68,14 @@ class CRM_Core_TableHierarchy { 'case_relation_type' => '32', 'civicrm_activity' => '33', 'civicrm_mailing_summary' => '34', - 'civicrm_mailing_event_queue' => '35', - 'civicrm_mailing_job' => '36', - 'civicrm_mailing' => '37', - 'civicrm_mailing_event_bounce' => '38', - 'civicrm_mailing_event_opened' => '39', - 'civicrm_mailing_event_reply' => '40', - 'civicrm_mailing_event_trackable_url_open' => '41', + 'civicrm_mailing_recipients' => '35', + 'civicrm_mailing' => '36', + 'civicrm_mailing_job' => '37', + 'civicrm_mailing_event_queue' => '38', + 'civicrm_mailing_event_bounce' => '39', + 'civicrm_mailing_event_opened' => '40', + 'civicrm_mailing_event_reply' => '41', + 'civicrm_mailing_event_trackable_url_open' => '42', ); static function &info() { diff --git a/CRM/Mailing/BAO/Query.php b/CRM/Mailing/BAO/Query.php index 9495096e8b..e81910aaea 100644 --- a/CRM/Mailing/BAO/Query.php +++ b/CRM/Mailing/BAO/Query.php @@ -59,14 +59,11 @@ class CRM_Mailing_BAO_Query { $query->_select['mailing_id'] = "civicrm_mailing.id as mailing_id"; $query->_element['mailing_id'] = 1; - $query->_tables['civicrm_mailing'] = 1; - $query->_whereTables['civicrm_mailing'] = 1; + // base table is contact, so join recipients to it + $query->_tables['civicrm_mailing_recipients'] = $query->_whereTables['civicrm_mailing_recipients'] = + " INNER JOIN civicrm_mailing_recipients ON civicrm_mailing_recipients.contact_id = contact_a.id "; - // during display search strictly display contacts inside mailing - $query->_tables['civicrm_mailing_event_queue'] = $query->_whereTables['civicrm_mailing_event_queue'] = - " INNER JOIN civicrm_mailing_event_queue ON civicrm_mailing_event_queue.contact_id = contact_a.id "; - - $query->_tables['civicrm_mailing_job'] = $query->_whereTables['civicrm_mailing_job'] = 1; + $query->_tables['civicrm_mailing'] = $query->_whereTables['civicrm_mailing'] = 1; // get mailing name if (CRM_Utils_Array::value('mailing_name', $query->_returnProperties)) { @@ -82,6 +79,8 @@ class CRM_Mailing_BAO_Query { // get mailing status if (CRM_Utils_Array::value('mailing_job_status', $query->_returnProperties)) { + $query->_tables['civicrm_mailing_job'] = $query->_whereTables['civicrm_mailing_job'] = + " LEFT JOIN civicrm_mailing_job ON civicrm_mailing_job.mailing_id = civicrm_mailing.id AND civicrm_mailing_job.parent_id IS NULL "; $query->_select['mailing_job_status'] = "civicrm_mailing_job.status as mailing_job_status"; $query->_element['mailing_job_status'] = 1; } @@ -108,13 +107,15 @@ class CRM_Mailing_BAO_Query { // mailing job end date / completed date if (CRM_Utils_Array::value('mailing_job_end_date', $query->_returnProperties)) { + $query->_tables['civicrm_mailing_job'] = $query->_whereTables['civicrm_mailing_job'] = + " LEFT JOIN civicrm_mailing_job ON civicrm_mailing_job.mailing_id = civicrm_mailing.id AND civicrm_mailing_job.parent_id IS NULL"; $query->_select['mailing_job_end_date'] = "civicrm_mailing_job.end_date as mailing_job_end_date"; $query->_element['mailing_job_end_date'] = 1; } - if (CRM_Utils_Array::value('mailing_event_queue_id', $query->_returnProperties)) { - $query->_select['mailing_event_queue_id'] = " civicrm_mailing_event_queue.id as mailing_event_queue_id"; - $query->_element['mailing_event_queue_id'] = 1; + if (CRM_Utils_Array::value('mailing_recipients_id', $query->_returnProperties)) { + $query->_select['mailing_recipients_id'] = " civicrm_mailing_recipients.id as mailing_recipients_id"; + $query->_element['mailing_recipients_id'] = 1; } } } @@ -137,17 +138,25 @@ class CRM_Mailing_BAO_Query { static function from($name, $mode, $side) { $from = NULL; + switch ($name) { - case 'civicrm_mailing_event_queue': - $from = " $side JOIN civicrm_mailing_event_queue ON civicrm_mailing_event_queue.contact_id = contact_a.id"; + case 'civicrm_mailing_recipients': + $from = " $side JOIN civicrm_mailing_recipients ON civicrm_mailing_recipients.contact_id = contact_a.id"; break; - case 'civicrm_mailing_job': - $from = " $side JOIN civicrm_mailing_job ON civicrm_mailing_job.id = civicrm_mailing_event_queue.job_id"; + case 'civicrm_mailing_event_queue': + // this is tightly binded so as to do a check WRT actual job recipients ('child' type jobs) + $from = " INNER JOIN civicrm_mailing_event_queue ON + civicrm_mailing_event_queue.contact_id = civicrm_mailing_recipients.contact_id + AND civicrm_mailing_event_queue.job_id = civicrm_mailing_job.id AND civicrm_mailing_job.job_type = 'child'"; break; case 'civicrm_mailing': - $from = " $side JOIN civicrm_mailing on civicrm_mailing.id = civicrm_mailing_job.mailing_id"; + $from = " $side JOIN civicrm_mailing ON civicrm_mailing.id = civicrm_mailing_recipients.mailing_id "; + break; + + case 'civicrm_mailing_job': + $from = " $side JOIN civicrm_mailing_job ON civicrm_mailing_job.mailing_id = civicrm_mailing.id "; break; case 'civicrm_mailing_event_bounce': @@ -161,7 +170,8 @@ class CRM_Mailing_BAO_Query { break; case 'recipient_email': - $from = " $side JOIN civicrm_email recipient_email ON recipient_email.id = civicrm_mailing_event_queue.email_id"; + $from = " $side JOIN civicrm_email recipient_email ON recipient_email.id = civicrm_mailing_recipients.email_id"; + break; } return $from; @@ -185,7 +195,7 @@ class CRM_Mailing_BAO_Query { 'mailing_job_end_date' => 1, 'contact_type' => 1, 'contact_sub_type' => 1, - 'mailing_event_queue_id' => 1 + 'mailing_recipients_id' => 1 ); } return $properties; @@ -196,10 +206,10 @@ class CRM_Mailing_BAO_Query { $fields = array(); $fields = self::getFields(); + switch ($name) { case 'mailing_id': $selectedMailings = array_flip($value); - $value = "(" . implode(',', $value) . ")"; $op = 'IN'; $query->_where[$grouping][] = "civicrm_mailing.id $op $value"; @@ -211,9 +221,8 @@ class CRM_Mailing_BAO_Query { $selectedMailings = implode(' or ', $selectedMailings); $query->_qill[$grouping][] = "Mailing Name $op \"$selectedMailings\""; - $query->_tables['civicrm_mailing_event_queue'] = $query->_whereTables['civicrm_mailing_event_queue'] = 1; - $query->_tables['civicrm_mailing_job'] = $query->_whereTables['civicrm_mailing_job'] = 1; $query->_tables['civicrm_mailing'] = $query->_whereTables['civicrm_mailing'] = 1; + $query->_tables['civicrm_mailing_recipients'] = $query->_whereTables['civicrm_mailing_recipients'] = 1; return; case 'mailing_name': @@ -223,18 +232,19 @@ class CRM_Mailing_BAO_Query { $op = 'LIKE'; } $query->_where[$grouping][] = "LOWER(civicrm_mailing.name) $op '$value'"; - $query->_qill[$grouping][] = "Mailing Name $op \"$value\""; - $query->_tables['civicrm_mailing_event_queue'] = $query->_whereTables['civicrm_mailing_event_queue'] = 1; - $query->_tables['civicrm_mailing_job'] = $query->_whereTables['civicrm_mailing_job'] = 1; + $query->_qill[$grouping][] = "Mailing Namename $op \"$value\""; $query->_tables['civicrm_mailing'] = $query->_whereTables['civicrm_mailing'] = 1; + $query->_tables['civicrm_mailing_recipients'] = $query->_whereTables['civicrm_mailing_recipients'] = 1; return; case 'mailing_date': case 'mailing_date_low': case 'mailing_date_high': // process to / from date + $query->_tables['civicrm_mailing'] = $query->_whereTables['civicrm_mailing'] = 1; $query->_tables['civicrm_mailing_event_queue'] = $query->_whereTables['civicrm_mailing_event_queue'] = 1; $query->_tables['civicrm_mailing_job'] = $query->_whereTables['civicrm_mailing_job'] = 1; + $query->_tables['civicrm_mailing_recipients'] = $query->_whereTables['civicrm_mailing_recipients'] = 1; $query->dateQueryBuilder($values, 'civicrm_mailing_job', 'mailing_date', 'start_date', 'Mailing Delivery Date' ); @@ -311,6 +321,20 @@ class CRM_Mailing_BAO_Query { ts('Mailing: '), $valueTitle ); return; + + case 'mailing_job_status': + if (!empty($value)) { + if ($value != 'Scheduled' && $value != 'Canceled') { + $query->_tables['civicrm_mailing_event_queue'] = $query->_whereTables['civicrm_mailing_event_queue'] = 1; + } + $query->_tables['civicrm_mailing'] = $query->_whereTables['civicrm_mailing'] = 1; + $query->_tables['civicrm_mailing_job'] = $query->_whereTables['civicrm_mailing_job'] = 1; + $query->_tables['civicrm_mailing_recipients'] = $query->_whereTables['civicrm_mailing_recipients'] = 1; + + $query->_where[$grouping][] = " civicrm_mailing_job.status = '{$value}' "; + $query->_qill[$grouping][] = "Mailing Job Status IS \"$value\""; + } + return; } } @@ -334,6 +358,15 @@ class CRM_Mailing_BAO_Query { CRM_Core_Form_Date::buildDateRange($form, 'mailing_date', 1, '_low', '_high', ts('From'), FALSE); + $mailingJobStatuses = array( + '' => ts('- select -'), + 'Complete' => 'Complete', + 'Scheduled' => 'Scheduled', + 'Running' => 'Running', + 'Canceled' => 'Canceled' + ); + $form->addElement('select', 'mailing_job_status', ts('Mailing Job Status'), $mailingJobStatuses, FALSE); + // event filters $form->addRadio('mailing_delivery_status', ts('Delivery Status'), CRM_Mailing_PseudoConstant::yesNoOptions('delivered')); $form->addRadio('mailing_open_status', ts('Trackable Opens'), CRM_Mailing_PseudoConstant::yesNoOptions('open')); @@ -418,7 +451,9 @@ class CRM_Mailing_BAO_Query { } $query->_qill[$grouping][] = $fieldTitle . ' - ' . $valueTitles[$value]; + $query->_tables['civicrm_mailing_job'] = $query->_whereTables['civicrm_mailing_job'] = 1; $query->_tables['civicrm_mailing_event_queue'] = $query->_whereTables['civicrm_mailing_event_queue'] = 1; + $query->_tables['civicrm_mailing_recipients'] = $query->_whereTables['civicrm_mailing_recipients'] = 1; $query->_tables[$tableName] = $query->_whereTables[$tableName] = 1; } } diff --git a/CRM/Mailing/Form/Task.php b/CRM/Mailing/Form/Task.php index 2c726498bb..055985eed8 100644 --- a/CRM/Mailing/Form/Task.php +++ b/CRM/Mailing/Form/Task.php @@ -101,13 +101,13 @@ class CRM_Mailing_Form_Task extends CRM_Core_Form { $result = $query->searchQuery(0, 0, $sortOrder); while ($result->fetch()) { - $ids[] = $result->mailing_event_queue_id; + $ids[] = $result->mailing_recipients_id; } $form->assign('totalSelectedMailingRecipients', $form->get('rowCount')); } if (!empty($ids)) { - $form->_componentClause = ' civicrm_mailing_event_queue.id IN ( ' . implode(',', $ids) . ' ) '; + $form->_componentClause = ' civicrm_mailing_recipients.id IN ( ' . implode(',', $ids) . ' ) '; } //set the context for redirection for any task actions diff --git a/CRM/Mailing/Selector/Search.php b/CRM/Mailing/Selector/Search.php index 5707e7ec97..7a6c9de10f 100644 --- a/CRM/Mailing/Selector/Search.php +++ b/CRM/Mailing/Selector/Search.php @@ -170,7 +170,6 @@ class CRM_Mailing_Selector_Search extends CRM_Core_Selector_Base implements CRM_ // type of selector $this->_action = $action; - $this->_query = new CRM_Contact_BAO_Query($this->_queryParams, CRM_Mailing_BAO_Query::defaultReturnProperties(CRM_Contact_BAO_Query::MODE_MAILING, FALSE @@ -178,7 +177,8 @@ class CRM_Mailing_Selector_Search extends CRM_Core_Selector_Base implements CRM_ NULL, FALSE, FALSE, CRM_Contact_BAO_Query::MODE_MAILING ); - $this->_query->_distinctComponentClause = " civicrm_mailing_event_queue.id "; + + $this->_query->_distinctComponentClause = " civicrm_mailing_recipients.id "; } //end of constructor @@ -299,7 +299,7 @@ class CRM_Mailing_Selector_Search extends CRM_Core_Selector_Base implements CRM_ } } - $row['checkbox'] = CRM_Core_Form::CB_PREFIX . $result->mailing_event_queue_id; + $row['checkbox'] = CRM_Core_Form::CB_PREFIX . $result->mailing_recipients_id; $actions = array( 'cid' => $result->contact_id, diff --git a/templates/CRM/Mailing/Form/Search/Common.tpl b/templates/CRM/Mailing/Form/Search/Common.tpl index bc6c1312a6..93cc471dba 100644 --- a/templates/CRM/Mailing/Form/Search/Common.tpl +++ b/templates/CRM/Mailing/Form/Search/Common.tpl @@ -1,5 +1,5 @@ - + {$form.mailing_id.label}
{$form.mailing_id.html} @@ -14,8 +14,12 @@ {/literal} + + {$form.mailing_job_status.label} +
+ {$form.mailing_job_status.html} + - {include file="CRM/Core/DateRange.tpl" fieldName="mailing_date" from='_low' to='_high'} -- 2.25.1