4 +--------------------------------------------------------------------+
5 | CiviCRM version 4.4 |
6 +--------------------------------------------------------------------+
7 | Copyright CiviCRM LLC (c) 2004-2013 |
8 +--------------------------------------------------------------------+
9 | This file is a part of CiviCRM. |
11 | CiviCRM is free software; you can copy, modify, and distribute it |
12 | under the terms of the GNU Affero General Public License |
13 | Version 3, 19 November 2007 and the CiviCRM Licensing Exception. |
15 | CiviCRM is distributed in the hope that it will be useful, but |
16 | WITHOUT ANY WARRANTY; without even the implied warranty of |
17 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. |
18 | See the GNU Affero General Public License for more details. |
20 | You should have received a copy of the GNU Affero General Public |
21 | License and the CiviCRM Licensing Exception along |
22 | with this program; if not, contact CiviCRM LLC |
23 | at info[AT]civicrm[DOT]org. If you have questions about the |
24 | GNU Affero General Public License or the licensing of CiviCRM, |
25 | see the CiviCRM license FAQ at http://civicrm.org/licensing |
26 +--------------------------------------------------------------------+
32 * @copyright CiviCRM LLC (c) 2004-2013
36 class CRM_Report_Form_Mailing_Detail
extends CRM_Report_Form
{
38 protected $_exposeContactID = FALSE;
40 function __construct() {
41 $this->_columns
= array();
43 $this->_columns
['civicrm_contact'] = array(
44 'dao' => 'CRM_Contact_DAO_Contact',
50 'title' => ts('Contact ID'),
56 'title' => ts('Contact Name'),
64 'title' => ts('Contact Name'),
68 'title' => ts('Contact ID'),
75 array('title' => ts('Contact Name'),
77 'default_order' => 'ASC',
80 'grouping' => 'contact-fields',
83 $this->_columns
['civicrm_mailing'] = array(
84 'dao' => 'CRM_Mailing_DAO_Mailing',
90 'title' => ts('Mailing'),
99 'title' => ts('Mailing'),
100 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
101 'type' => CRM_Utils_Type
::T_INT
,
102 'options' => CRM_Mailing_BAO_Mailing
::getMailingsList(),
110 'title' => ts('Mailing'),
113 'grouping' => 'mailing-fields',
116 // adding dao just to have alias
117 $this->_columns
['civicrm_mailing_event_bounce'] = array(
118 'dao' => 'CRM_Mailing_Event_DAO_Bounce',
121 $this->_columns
['civicrm_mailing_event_delivered'] = array(
122 'dao' => 'CRM_Mailing_Event_DAO_Delivered',
128 'title' => ts('Delivery Status'),
136 'name' => 'delivery_status',
137 'title' => ts('Delivery Status'),
138 'operatorType' => CRM_Report_Form
::OP_SELECT
,
139 'type' => CRM_Utils_Type
::T_STRING
,
142 'successful' => 'Successful',
143 'bounced' => 'Bounced',
147 'grouping' => 'mailing-fields',
150 $this->_columns
['civicrm_mailing_event_unsubscribe'] = array(
151 'dao' => 'CRM_Mailing_Event_DAO_Unsubscribe',
157 'title' => ts('Unsubscribe'),
163 'title' => ts('Opt-out'),
165 'alias' => 'mailing_event_unsubscribe_civireport2',
173 'title' => ts('Unsubscribed'),
174 'type' => CRM_Utils_Type
::T_INT
,
175 'operatorType' => CRM_Report_Form
::OP_SELECT
,
176 'options' => array('' => ts('Any'), '0' => ts('No'), '1' => ts('Yes')),
177 'clause' => 'mailing_event_unsubscribe_civireport.id IS NULL',
181 'title' => ts('Opted-out'),
182 'type' => CRM_Utils_Type
::T_INT
,
183 'operatorType' => CRM_Report_Form
::OP_SELECT
,
184 'options' => array('' => ts('Any'), '0' => ts('No'), '1' => ts('Yes')),
185 'clause' => 'mailing_event_unsubscribe_civireport2.id IS NULL',
188 'grouping' => 'mailing-fields',
191 $this->_columns
['civicrm_mailing_event_reply'] = array(
192 'dao' => 'CRM_Mailing_Event_DAO_Reply',
198 'title' => ts('Reply'),
206 'title' => ts('Replied'),
207 'type' => CRM_Utils_Type
::T_INT
,
208 'operatorType' => CRM_Report_Form
::OP_SELECT
,
209 'options' => array('' => ts('Any'), '0' => ts('No'), '1' => ts('Yes')),
210 'clause' => 'mailing_event_reply_civireport.id IS NULL',
213 'grouping' => 'mailing-fields',
216 $this->_columns
['civicrm_mailing_event_forward'] = array(
217 'dao' => 'CRM_Mailing_Event_DAO_Forward',
223 'title' => ts('Forwarded to Email'),
231 'title' => ts('Forwarded'),
232 'type' => CRM_Utils_Type
::T_INT
,
233 'operatorType' => CRM_Report_Form
::OP_SELECT
,
234 'options' => array('' => ts('Any'), '0' => ts('No'), '1' => ts('Yes')),
235 'clause' => 'mailing_event_forward_civireport.id IS NULL',
238 'grouping' => 'mailing-fields',
241 $this->_columns
['civicrm_email'] = array(
242 'dao' => 'CRM_Core_DAO_Email',
247 'title' => ts('Email'),
251 'grouping' => 'contact-fields',
254 $this->_columns
['civicrm_phone'] = array(
255 'dao' => 'CRM_Core_DAO_Phone',
256 'fields' => array('phone' => NULL),
257 'grouping' => 'contact-fields',
260 $this->_columns
['civicrm_group'] = array(
261 'dao' => 'CRM_Contact_DAO_Group',
267 'name' => 'group_id',
268 'title' => ts('Group'),
269 'operatorType' => CRM_Report_Form
::OP_MULTISELECT
,
271 'options' => CRM_Core_PseudoConstant
::group(),
276 parent
::__construct();
280 $select = $columns = array();
281 foreach ($this->_columns
as $tableName => $table) {
282 if (array_key_exists('fields', $table)) {
283 foreach ($table['fields'] as $fieldName => $field) {
284 if (CRM_Utils_Array
::value('required', $field) ||
285 CRM_Utils_Array
::value($fieldName, $this->_params
['fields'])
287 if (in_array($fieldName, array(
288 'unsubscribe_id', 'optout_id', 'forward_id', 'reply_id'))) {
289 $select[] = "IF({$field['dbAlias']} IS NULL, 'No', 'Yes') as {$tableName}_{$fieldName}";
290 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array
::value('type', $field);
291 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['no_display'] = CRM_Utils_Array
::value('no_display', $field);
292 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array
::value('title', $field);
293 unset($this->_columns
[$tableName]['fields'][$fieldName]);
294 $columns[$tableName][$fieldName] = $field;
296 elseif ($fieldName == 'delivery_id') {
297 $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}";
298 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['type'] = CRM_Utils_Array
::value('type', $field);
299 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['no_display'] = CRM_Utils_Array
::value('no_display', $field);
300 $this->_columnHeaders
["{$tableName}_{$fieldName}"]['title'] = CRM_Utils_Array
::value('title', $field);
301 unset($this->_columns
[$tableName]['fields'][$fieldName]);
302 $columns[$tableName][$fieldName] = $field;
310 if (!empty($select)) {
311 $this->_select
.= ', ' . implode(', ', $select) . " ";
314 // put the fields that were unset, back in place
315 foreach ($columns as $tableName => $table) {
316 foreach ($table as $fieldName => $fields) {
317 $this->_columns
[$tableName]['fields'][$fieldName] = $fields;
322 ksort($this->_columnHeaders
);
327 FROM civicrm_contact {$this->_aliases['civicrm_contact']}";
330 INNER JOIN civicrm_mailing_event_queue
331 ON civicrm_mailing_event_queue.contact_id = {$this->_aliases['civicrm_contact']}.id
332 INNER JOIN civicrm_email {$this->_aliases['civicrm_email']}
333 ON civicrm_mailing_event_queue.email_id = {$this->_aliases['civicrm_email']}.id";
335 if (array_key_exists('delivery_id', $this->_params
['fields'])) {
337 LEFT JOIN civicrm_mailing_event_delivered {$this->_aliases['civicrm_mailing_event_delivered']}
338 ON {$this->_aliases['civicrm_mailing_event_delivered']}.event_queue_id = civicrm_mailing_event_queue.id
339 LEFT JOIN civicrm_mailing_event_bounce {$this->_aliases['civicrm_mailing_event_bounce']}
340 ON {$this->_aliases['civicrm_mailing_event_bounce']}.event_queue_id = civicrm_mailing_event_queue.id";
341 if (CRM_Utils_Array
::value('delivery_status_value', $this->_params
) == 'bounced') {
342 $this->_columns
['civicrm_mailing_event_delivered']['filters']['delivery_status']['clause'] = "{$this->_aliases['civicrm_mailing_event_bounce']}.id IS NOT NULL";
344 elseif (CRM_Utils_Array
::value('delivery_status_value', $this->_params
) == 'successful') {
345 $this->_columns
['civicrm_mailing_event_delivered']['filters']['delivery_status']['clause'] = "{$this->_aliases['civicrm_mailing_event_delivered']}.id IS NOT NULL";
349 unset($this->_columns
['civicrm_mailing_event_delivered']['filters']['delivery_status']);
352 if (array_key_exists('reply_id', $this->_params
['fields']) ||
353 is_numeric(CRM_Utils_Array
::value('is_replied_value', $this->_params
))
355 if (CRM_Utils_Array
::value('is_replied_value', $this->_params
) == 1) {
357 $this->_columns
['civicrm_mailing_event_reply']['filters']['is_replied']['clause'] = '(1)';
363 {$joinType} JOIN civicrm_mailing_event_reply {$this->_aliases['civicrm_mailing_event_reply']}
364 ON {$this->_aliases['civicrm_mailing_event_reply']}.event_queue_id = civicrm_mailing_event_queue.id";
367 unset($this->_columns
['civicrm_mailing_event_reply']['filters']['is_replied']);
370 if (array_key_exists('unsubscribe_id', $this->_params
['fields']) ||
371 is_numeric(CRM_Utils_Array
::value('is_unsubscribed_value', $this->_params
))
373 if (CRM_Utils_Array
::value('is_unsubscribed_value', $this->_params
) == 1) {
375 $this->_columns
['civicrm_mailing_event_unsubscribe']['filters']['is_unsubscribed']['clause'] = '(1)';
381 {$joinType} JOIN civicrm_mailing_event_unsubscribe {$this->_aliases['civicrm_mailing_event_unsubscribe']}
382 ON {$this->_aliases['civicrm_mailing_event_unsubscribe']}.event_queue_id = civicrm_mailing_event_queue.id
383 AND {$this->_aliases['civicrm_mailing_event_unsubscribe']}.org_unsubscribe = 0";
386 unset($this->_columns
['civicrm_mailing_event_unsubscribe']['filters']['is_unsubscribed']);
389 if (array_key_exists('optout_id', $this->_params
['fields']) ||
390 is_numeric(CRM_Utils_Array
::value('is_optout_value', $this->_params
))
392 if (CRM_Utils_Array
::value('is_optout_value', $this->_params
) == 1) {
394 $this->_columns
['civicrm_mailing_event_unsubscribe']['filters']['is_optout']['clause'] = '(1)';
400 {$joinType} JOIN civicrm_mailing_event_unsubscribe {$this->_aliases['civicrm_mailing_event_unsubscribe']}2
401 ON {$this->_aliases['civicrm_mailing_event_unsubscribe']}2.event_queue_id = civicrm_mailing_event_queue.id
402 AND {$this->_aliases['civicrm_mailing_event_unsubscribe']}2.org_unsubscribe = 1";
405 unset($this->_columns
['civicrm_mailing_event_unsubscribe']['filters']['is_optout']);
408 if (array_key_exists('forward_id', $this->_params
['fields']) ||
409 is_numeric(CRM_Utils_Array
::value('is_forwarded_value', $this->_params
))
411 if (CRM_Utils_Array
::value('is_forwarded_value', $this->_params
) == 1) {
413 $this->_columns
['civicrm_mailing_event_forward']['filters']['is_forwarded']['clause'] = '(1)';
419 {$joinType} JOIN civicrm_mailing_event_forward {$this->_aliases['civicrm_mailing_event_forward']}
420 ON {$this->_aliases['civicrm_mailing_event_forward']}.event_queue_id = civicrm_mailing_event_queue.id";
423 unset($this->_columns
['civicrm_mailing_event_forward']['filters']['is_forwarded']);
427 INNER JOIN civicrm_mailing_job
428 ON civicrm_mailing_event_queue.job_id = civicrm_mailing_job.id
429 INNER JOIN civicrm_mailing {$this->_aliases['civicrm_mailing']}
430 ON civicrm_mailing_job.mailing_id = {$this->_aliases['civicrm_mailing']}.id
431 AND civicrm_mailing_job.is_test = 0";
433 if ($this->_phoneField
) {
435 LEFT JOIN civicrm_phone {$this->_aliases['civicrm_phone']}
436 ON {$this->_aliases['civicrm_contact']}.id = {$this->_aliases['civicrm_phone']}.contact_id AND
437 {$this->_aliases['civicrm_phone']}.is_primary = 1 ";
443 $this->_where
.= " AND {$this->_aliases['civicrm_mailing']}.sms_provider_id IS NULL";
446 function mailingList() {
449 $mailing = new CRM_Mailing_BAO_Mailing();
450 $query = "SELECT name FROM civicrm_mailing ";
451 $mailing->query($query);
453 while ($mailing->fetch()) {
454 $data[mysql_real_escape_string($mailing->name
)] = $mailing->name
;
460 function alterDisplay(&$rows) {
461 // custom code to alter rows
463 foreach ($rows as $rowNum => $row) {
464 // make count columns point to detail report
465 // convert display name to links
466 if (array_key_exists('civicrm_contact_sort_name', $row) &&
467 array_key_exists('civicrm_contact_id', $row)
469 $url = CRM_Utils_System
::url('civicrm/contact/view',
470 'reset=1&cid=' . $row['civicrm_contact_id'],
473 $rows[$rowNum]['civicrm_contact_sort_name_link'] = $url;
474 $rows[$rowNum]['civicrm_contact_sort_name_hover'] = ts("View Contact details for this contact.");
478 // skip looking further in rows, if first row itself doesn't
479 // have the column we need