3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.6 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2014 |
7 +--------------------------------------------------------------------+
8 | This file is a part of CiviCRM. |
10 | CiviCRM is free software; you can copy, modify, and distribute it |
11 | under the terms of the GNU Affero General Public License |
12 | Version 3, 19 November 2007 and the CiviCRM Licensing Exception. |
14 | CiviCRM is distributed in the hope that it will be useful, but |
15 | WITHOUT ANY WARRANTY; without even the implied warranty of |
16 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. |
17 | See the GNU Affero General Public License for more details. |
19 | You should have received a copy of the GNU Affero General Public |
20 | License and the CiviCRM Licensing Exception along |
21 | with this program; if not, contact CiviCRM LLC |
22 | at info[AT]civicrm[DOT]org. If you have questions about the |
23 | GNU Affero General Public License or the licensing of CiviCRM, |
24 | see the CiviCRM license FAQ at http://civicrm.org/licensing |
25 +--------------------------------------------------------------------+
31 * @copyright CiviCRM LLC (c) 2004-2014
35 class CRM_Report_Form_Mailing_Detail
extends CRM_Report_Form
{
37 protected $_exposeContactID = FALSE;
43 public function __construct() {
44 $this->_columns
= array();
46 $this->_columns
['civicrm_contact'] = array(
47 'dao' => 'CRM_Contact_DAO_Contact',
51 'title' => ts('Contact ID'),
56 'title' => ts('Contact Name'),
62 'title' => ts('Contact Name'),
65 'title' => ts('Contact ID'),
71 'title' => ts('Contact Name'),
73 'default_order' => 'ASC',
76 'grouping' => 'contact-fields',
79 $this->_columns
['civicrm_mailing'] = array(
80 'dao' => 'CRM_Mailing_DAO_Mailing',
82 'mailing_name' => array(
84 'title' => ts('Mailing'),
89 'mailing_id' => array(
91 'title' => ts('Mailing'),
92 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
93 'type' => CRM_Utils_Type
::T_INT
,
94 'options' => CRM_Mailing_BAO_Mailing
::getMailingsList(),
98 'mailing_name' => array(
100 'title' => ts('Mailing'),
103 'grouping' => 'mailing-fields',
106 // adding dao just to have alias
107 $this->_columns
['civicrm_mailing_event_bounce'] = array(
108 'dao' => 'CRM_Mailing_Event_DAO_Bounce',
111 $this->_columns
['civicrm_mailing_event_delivered'] = array(
112 'dao' => 'CRM_Mailing_Event_DAO_Delivered',
114 'delivery_id' => array(
116 'title' => ts('Delivery Status'),
121 'delivery_status' => array(
122 'name' => 'delivery_status',
123 'title' => ts('Delivery Status'),
124 'operatorType' => CRM_Report_Form
::OP_SELECT
,
125 'type' => CRM_Utils_Type
::T_STRING
,
128 'successful' => 'Successful',
129 'bounced' => 'Bounced',
133 'grouping' => 'mailing-fields',
136 $this->_columns
['civicrm_mailing_event_unsubscribe'] = array(
137 'dao' => 'CRM_Mailing_Event_DAO_Unsubscribe',
139 'unsubscribe_id' => array(
141 'title' => ts('Unsubscribe'),
144 'optout_id' => array(
146 'title' => ts('Opt-out'),
148 'alias' => 'mailing_event_unsubscribe_civireport2',
152 'is_unsubscribed' => array(
154 'title' => ts('Unsubscribed'),
155 'type' => CRM_Utils_Type
::T_INT
,
156 'operatorType' => CRM_Report_Form
::OP_SELECT
,
162 'clause' => 'mailing_event_unsubscribe_civireport.id IS NULL',
164 'is_optout' => array(
165 'title' => ts('Opted-out'),
166 'type' => CRM_Utils_Type
::T_INT
,
167 'operatorType' => CRM_Report_Form
::OP_SELECT
,
173 'clause' => 'mailing_event_unsubscribe_civireport2.id IS NULL',
176 'grouping' => 'mailing-fields',
179 $this->_columns
['civicrm_mailing_event_reply'] = array(
180 'dao' => 'CRM_Mailing_Event_DAO_Reply',
184 'title' => ts('Reply'),
188 'is_replied' => array(
190 'title' => ts('Replied'),
191 'type' => CRM_Utils_Type
::T_INT
,
192 'operatorType' => CRM_Report_Form
::OP_SELECT
,
198 'clause' => 'mailing_event_reply_civireport.id IS NULL',
201 'grouping' => 'mailing-fields',
204 $this->_columns
['civicrm_mailing_event_forward'] = array(
205 'dao' => 'CRM_Mailing_Event_DAO_Forward',
207 'forward_id' => array(
209 'title' => ts('Forwarded to Email'),
213 'is_forwarded' => array(
215 'title' => ts('Forwarded'),
216 'type' => CRM_Utils_Type
::T_INT
,
217 'operatorType' => CRM_Report_Form
::OP_SELECT
,
223 'clause' => 'mailing_event_forward_civireport.id IS NULL',
226 'grouping' => 'mailing-fields',
229 $this->_columns
['civicrm_email'] = array(
230 'dao' => 'CRM_Core_DAO_Email',
233 'title' => ts('Email'),
237 'grouping' => 'contact-fields',
240 $this->_columns
['civicrm_phone'] = array(
241 'dao' => 'CRM_Core_DAO_Phone',
242 'fields' => array('phone' => NULL),
243 'grouping' => 'contact-fields',
246 $this->_groupFilter
= TRUE;
247 $this->_tagFilter
= TRUE;
249 parent
::__construct();
252 public function select() {
253 $select = $columns = array();
254 foreach ($this->_columns
as $tableName => $table) {
255 if (array_key_exists('fields', $table)) {
256 foreach ($table['fields'] as $fieldName => $field) {
257 if (!empty($field['required']) ||
258 !empty($this->_params
['fields'][$fieldName])
260 if (in_array($fieldName, array(
266 $select[] = "IF({$field['dbAlias']} IS NULL, 'No', 'Yes') as {$tableName}_{$fieldName}";
267 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array
::value('type', $field);
268 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['no_display'] = CRM_Utils_Array
::value('no_display', $field);
269 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array
::value('title', $field);
270 unset($this->_columns
[$tableName]['fields'][$fieldName]);
271 $columns[$tableName][$fieldName] = $field;
273 elseif ($fieldName == 'delivery_id') {
274 $select[] = "IF(mailing_event_delivered_civireport.id IS NOT NULL, 'Successful', IF(mailing_event_bounce_civireport.id IS NOT NULL, 'Bounced ', 'Unknown')) as {$tableName}_{$fieldName}";
275 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array
::value('type', $field);
276 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['no_display'] = CRM_Utils_Array
::value('no_display', $field);
277 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array
::value('title', $field);
278 unset($this->_columns
[$tableName]['fields'][$fieldName]);
279 $columns[$tableName][$fieldName] = $field;
287 if (!empty($select)) {
288 $this->_select
.= ', ' . implode(', ', $select) . " ";
291 // put the fields that were unset, back in place
292 foreach ($columns as $tableName => $table) {
293 foreach ($table as $fieldName => $fields) {
294 $this->_columns
[$tableName]['fields'][$fieldName] = $fields;
299 ksort($this->_columnHeaders
);
302 public function from() {
304 FROM civicrm_contact {$this->_aliases['civicrm_contact']}";
307 INNER JOIN civicrm_mailing_event_queue
308 ON civicrm_mailing_event_queue.contact_id = {$this->_aliases['civicrm_contact']}.id
309 INNER JOIN civicrm_email {$this->_aliases['civicrm_email']}
310 ON civicrm_mailing_event_queue.email_id = {$this->_aliases['civicrm_email']}.id";
312 if (array_key_exists('delivery_id', $this->_params
['fields'])) {
314 LEFT JOIN civicrm_mailing_event_delivered {$this->_aliases['civicrm_mailing_event_delivered']}
315 ON {$this->_aliases['civicrm_mailing_event_delivered']}.event_queue_id = civicrm_mailing_event_queue.id
316 LEFT JOIN civicrm_mailing_event_bounce {$this->_aliases['civicrm_mailing_event_bounce']}
317 ON {$this->_aliases['civicrm_mailing_event_bounce']}.event_queue_id = civicrm_mailing_event_queue.id";
318 if (CRM_Utils_Array
::value('delivery_status_value', $this->_params
) ==
321 $this->_columns
['civicrm_mailing_event_delivered']['filters']['delivery_status']['clause'] = "{$this->_aliases['civicrm_mailing_event_bounce']}.id IS NOT NULL";
323 elseif (CRM_Utils_Array
::value('delivery_status_value', $this->_params
) ==
326 $this->_columns
['civicrm_mailing_event_delivered']['filters']['delivery_status']['clause'] = "{$this->_aliases['civicrm_mailing_event_delivered']}.id IS NOT NULL";
330 unset($this->_columns
['civicrm_mailing_event_delivered']['filters']['delivery_status']);
333 if (array_key_exists('reply_id', $this->_params
['fields']) ||
334 is_numeric(CRM_Utils_Array
::value('is_replied_value', $this->_params
))
336 if (CRM_Utils_Array
::value('is_replied_value', $this->_params
) == 1) {
338 $this->_columns
['civicrm_mailing_event_reply']['filters']['is_replied']['clause'] = '(1)';
344 {$joinType} JOIN civicrm_mailing_event_reply {$this->_aliases['civicrm_mailing_event_reply']}
345 ON {$this->_aliases['civicrm_mailing_event_reply']}.event_queue_id = civicrm_mailing_event_queue.id";
348 unset($this->_columns
['civicrm_mailing_event_reply']['filters']['is_replied']);
351 if (array_key_exists('unsubscribe_id', $this->_params
['fields']) ||
352 is_numeric(CRM_Utils_Array
::value('is_unsubscribed_value', $this->_params
))
354 if (CRM_Utils_Array
::value('is_unsubscribed_value', $this->_params
) == 1
357 $this->_columns
['civicrm_mailing_event_unsubscribe']['filters']['is_unsubscribed']['clause'] = '(1)';
363 {$joinType} JOIN civicrm_mailing_event_unsubscribe {$this->_aliases['civicrm_mailing_event_unsubscribe']}
364 ON {$this->_aliases['civicrm_mailing_event_unsubscribe']}.event_queue_id = civicrm_mailing_event_queue.id
365 AND {$this->_aliases['civicrm_mailing_event_unsubscribe']}.org_unsubscribe = 0";
368 unset($this->_columns
['civicrm_mailing_event_unsubscribe']['filters']['is_unsubscribed']);
371 if (array_key_exists('optout_id', $this->_params
['fields']) ||
372 is_numeric(CRM_Utils_Array
::value('is_optout_value', $this->_params
))
374 if (CRM_Utils_Array
::value('is_optout_value', $this->_params
) == 1) {
376 $this->_columns
['civicrm_mailing_event_unsubscribe']['filters']['is_optout']['clause'] = '(1)';
382 {$joinType} JOIN civicrm_mailing_event_unsubscribe {$this->_aliases['civicrm_mailing_event_unsubscribe']}2
383 ON {$this->_aliases['civicrm_mailing_event_unsubscribe']}2.event_queue_id = civicrm_mailing_event_queue.id
384 AND {$this->_aliases['civicrm_mailing_event_unsubscribe']}2.org_unsubscribe = 1";
387 unset($this->_columns
['civicrm_mailing_event_unsubscribe']['filters']['is_optout']);
390 if (array_key_exists('forward_id', $this->_params
['fields']) ||
391 is_numeric(CRM_Utils_Array
::value('is_forwarded_value', $this->_params
))
393 if (CRM_Utils_Array
::value('is_forwarded_value', $this->_params
) == 1) {
395 $this->_columns
['civicrm_mailing_event_forward']['filters']['is_forwarded']['clause'] = '(1)';
401 {$joinType} JOIN civicrm_mailing_event_forward {$this->_aliases['civicrm_mailing_event_forward']}
402 ON {$this->_aliases['civicrm_mailing_event_forward']}.event_queue_id = civicrm_mailing_event_queue.id";
405 unset($this->_columns
['civicrm_mailing_event_forward']['filters']['is_forwarded']);
409 INNER JOIN civicrm_mailing_job
410 ON civicrm_mailing_event_queue.job_id = civicrm_mailing_job.id
411 INNER JOIN civicrm_mailing {$this->_aliases['civicrm_mailing']}
412 ON civicrm_mailing_job.mailing_id = {$this->_aliases['civicrm_mailing']}.id
413 AND civicrm_mailing_job.is_test = 0";
415 if ($this->_phoneField
) {
417 LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
418 ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND
419 {$this->_aliases['civicrm_phone']}.is_primary = 1 ";
423 public function where() {
425 $this->_where
.= " AND {$this->_aliases['civicrm_mailing']}.sms_provider_id IS NULL";
431 public function mailingList() {
434 $mailing = new CRM_Mailing_BAO_Mailing();
435 $query = "SELECT name FROM civicrm_mailing ";
436 $mailing->query($query);
438 while ($mailing->fetch()) {
439 $data[mysql_real_escape_string($mailing->name
)] = $mailing->name
;
446 * Alter display of rows.
448 * Iterate through the rows retrieved via SQL and make changes for display purposes,
449 * such as rendering contacts as links.
452 * Rows generated by SQL, with an array for each row.
454 public function alterDisplay(&$rows) {
456 foreach ($rows as $rowNum => $row) {
457 // make count columns point to detail report
458 // convert display name to links
459 if (array_key_exists('civicrm_contact_sort_name', $row) &&
460 array_key_exists('civicrm_contact_id', $row)
462 $url = CRM_Utils_System
::url('civicrm/contact/view',
463 'reset=1&cid=' . $row['civicrm_contact_id'],
466 $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
467 $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contact details for this contact.");
471 // skip looking further in rows, if first row itself doesn't
472 // have the column we need