CRM/Mailing/Event/BAO/Opened.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.7                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2017                                |
   7  +--------------------------------------------------------------------+
   8  | This file is a part of CiviCRM.                                    |
   9  |                                                                    |
  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.   |
  13  |                                                                    |
  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.        |
  18  |                                                                    |
  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  +--------------------------------------------------------------------+
  26  */
  27
  28 /**
  29  *
  30  * @package CRM
  31  * @copyright CiviCRM LLC (c) 2004-2017
  32  */
  33 class CRM_Mailing_Event_BAO_Opened extends CRM_Mailing_Event_DAO_Opened {
  34
  35   /**
  36    * Class constructor.
  37    */
  38   public function __construct() {
  39     parent::__construct();
  40   }
  41
  42   /**
  43    * Register an open event.
  44    *
  45    * @param int $queue_id
  46    *   The Queue Event ID of the recipient.
  47    *
  48    * @return bool
  49    */
  50   public static function open($queue_id) {
  51     // First make sure there's a matching queue event.
  52
  53     $success = FALSE;
  54
  55     $q = new CRM_Mailing_Event_BAO_Queue();
  56     $q->id = $queue_id;
  57     if ($q->find(TRUE)) {
  58       $oe = new CRM_Mailing_Event_BAO_Opened();
  59       $oe->event_queue_id = $queue_id;
  60       $oe->time_stamp = date('YmdHis');
  61       $oe->save();
  62       $success = TRUE;
  63     }
  64
  65     return $success;
  66   }
  67
  68   /**
  69    * Get row count for the event selector.
  70    *
  71    * @param int $mailing_id
  72    *   ID of the mailing.
  73    * @param int $job_id
  74    *   Optional ID of a job to filter on.
  75    * @param bool $is_distinct
  76    *   Group by queue ID?.
  77    *
  78    * @param string $toDate
  79    *
  80    * @return int
  81    *   Number of rows in result set
  82    */
  83   public static function getTotalCount(
  84     $mailing_id,
  85     $job_id = NULL,
  86     $is_distinct = FALSE,
  87     $toDate = NULL
  88   ) {
  89     $dao = new CRM_Core_DAO();
  90
  91     $open = self::getTableName();
  92     $queue = CRM_Mailing_Event_BAO_Queue::getTableName();
  93     $mailing = CRM_Mailing_BAO_Mailing::getTableName();
  94     $job = CRM_Mailing_BAO_MailingJob::getTableName();
  95
  96     $query = "
  97             SELECT      COUNT($open.id) as opened
  98             FROM        $open
  99             INNER JOIN  $queue
 100                     ON  $open.event_queue_id = $queue.id
 101             INNER JOIN  $job
 102                     ON  $queue.job_id = $job.id
 103             INNER JOIN  $mailing
 104                     ON  $job.mailing_id = $mailing.id
 105                     AND $job.is_test = 0
 106             WHERE       $mailing.id = " . CRM_Utils_Type::escape($mailing_id, 'Integer');
 107
 108     if (!empty($toDate)) {
 109       $query .= " AND $open.time_stamp <= $toDate";
 110     }
 111
 112     if (!empty($job_id)) {
 113       $query .= " AND $job.id = " . CRM_Utils_Type::escape($job_id, 'Integer');
 114     }
 115
 116     if ($is_distinct) {
 117       $query .= " GROUP BY $queue.id ";
 118     }
 119
 120     $dao->query($query);
 121     $dao->fetch();
 122     if ($is_distinct) {
 123       return $dao->N;
 124     }
 125     else {
 126       return $dao->opened ? $dao->opened : 0;
 127     }
 128   }
 129
 130   /**
 131    * CRM-12814
 132    * Get opened count for each mailing for a given set of mailing IDs
 133    *
 134    * @param $mailingIDs
 135    *
 136    * @return array
 137    *   Opened count per mailing ID
 138    */
 139   public static function getMailingTotalCount($mailingIDs) {
 140     $dao = new CRM_Core_DAO();
 141     $openedCount = array();
 142
 143     $open = self::getTableName();
 144     $queue = CRM_Mailing_Event_BAO_Queue::getTableName();
 145     $job = CRM_Mailing_BAO_MailingJob::getTableName();
 146     $mailingIDs = implode(',', $mailingIDs);
 147
 148     $query = "
 149       SELECT $job.mailing_id as mailingID, COUNT($open.id) as opened
 150       FROM $open
 151       INNER JOIN $queue
 152         ON  $open.event_queue_id = $queue.id
 153       INNER JOIN $job
 154         ON  $queue.job_id = $job.id
 155         AND $job.is_test = 0
 156       WHERE $job.mailing_id IN ({$mailingIDs})
 157       GROUP BY civicrm_mailing_job.mailing_id
 158     ";
 159
 160     $dao->query($query);
 161
 162     while ($dao->fetch()) {
 163       $openedCount[$dao->mailingID] = $dao->opened;
 164     }
 165     return $openedCount;
 166   }
 167
 168   /**
 169    * Get opened count for each mailing for a given set of mailing IDs and a specific contact.
 170    *
 171    * @param int $mailingIDs
 172    *   IDs of the mailing (comma separated).
 173    * @param int $contactID
 174    *   ID of the contact.
 175    *
 176    * @return array
 177    *   Count per mailing ID
 178    */
 179   public static function getMailingContactCount($mailingIDs, $contactID) {
 180     $dao = new CRM_Core_DAO();
 181     $openedCount = array();
 182
 183     $open = self::getTableName();
 184     $queue = CRM_Mailing_Event_BAO_Queue::getTableName();
 185     $job = CRM_Mailing_BAO_MailingJob::getTableName();
 186     $mailingIDs = implode(',', $mailingIDs);
 187
 188     $query = "
 189       SELECT $job.mailing_id as mailingID, COUNT($open.id) as opened
 190       FROM $open
 191       INNER JOIN $queue
 192         ON  $open.event_queue_id = $queue.id
 193         AND $queue.contact_id = $contactID
 194       INNER JOIN $job
 195         ON  $queue.job_id = $job.id
 196         AND $job.is_test = 0
 197       WHERE $job.mailing_id IN ({$mailingIDs})
 198       GROUP BY civicrm_mailing_job.mailing_id
 199     ";
 200
 201     $dao->query($query);
 202
 203     while ($dao->fetch()) {
 204       $openedCount[$dao->mailingID] = $dao->opened;
 205     }
 206
 207     return $openedCount;
 208   }
 209
 210   /**
 211    * Get rows for the event browser.
 212    *
 213    * @param int $mailing_id
 214    *   ID of the mailing.
 215    * @param int $job_id
 216    *   Optional ID of the job.
 217    * @param bool $is_distinct
 218    *   Group by queue id?.
 219    * @param int $offset
 220    *   Offset.
 221    * @param int $rowCount
 222    *   Number of rows.
 223    * @param array $sort
 224    *   Sort array.
 225    *
 226    * @param int $contact_id
 227    *
 228    * @return array
 229    *   Result set
 230    */
 231   public static function &getRows(
 232     $mailing_id, $job_id = NULL,
 233     $is_distinct = FALSE, $offset = NULL, $rowCount = NULL, $sort = NULL, $contact_id = NULL
 234   ) {
 235     $dao = new CRM_Core_Dao();
 236
 237     $open = self::getTableName();
 238     $queue = CRM_Mailing_Event_BAO_Queue::getTableName();
 239     $mailing = CRM_Mailing_BAO_Mailing::getTableName();
 240     $job = CRM_Mailing_BAO_MailingJob::getTableName();
 241     $contact = CRM_Contact_BAO_Contact::getTableName();
 242     $email = CRM_Core_BAO_Email::getTableName();
 243
 244     $selectClauses = array(
 245       "$contact.display_name as display_name",
 246       "$contact.id as contact_id",
 247       "$email.email as email",
 248       ($is_distinct) ? "MIN({$open}.time_stamp) as date" : "{$open}.time_stamp as date",
 249     );
 250
 251     if ($is_distinct) {
 252       $groupBy = " GROUP BY $queue.id ";
 253       $select = CRM_Contact_BAO_Query::appendAnyValueToSelect($selectClauses, "$queue.id");
 254     }
 255     else {
 256       $groupBy = '';
 257       $select = " SELECT " . implode(', ', $selectClauses);
 258     }
 259
 260     $query = "
 261             $select
 262             FROM        $contact
 263             INNER JOIN  $queue
 264                     ON  $queue.contact_id = $contact.id
 265             INNER JOIN  $email
 266                     ON  $queue.email_id = $email.id
 267             INNER JOIN  $open
 268                     ON  $open.event_queue_id = $queue.id
 269             INNER JOIN  $job
 270                     ON  $queue.job_id = $job.id
 271             INNER JOIN  $mailing
 272                     ON  $job.mailing_id = $mailing.id
 273                     AND $job.is_test = 0
 274             WHERE       $mailing.id = " . CRM_Utils_Type::escape($mailing_id, 'Integer');
 275
 276     if (!empty($job_id)) {
 277       $query .= " AND $job.id = " . CRM_Utils_Type::escape($job_id, 'Integer');
 278     }
 279
 280     if (!empty($contact_id)) {
 281       $query .= " AND $contact.id = " . CRM_Utils_Type::escape($contact_id, 'Integer');
 282     }
 283
 284     $query .= $groupBy;
 285
 286     $orderBy = "sort_name ASC";
 287     if (!$is_distinct) {
 288       $orderBy .= ", {$open}.time_stamp DESC";
 289     }
 290     if ($sort) {
 291       if (is_string($sort)) {
 292         $sort = CRM_Utils_Type::escape($sort, 'String');
 293         $orderBy = $sort;
 294       }
 295       else {
 296         $orderBy = trim($sort->orderBy());
 297       }
 298     }
 299
 300     $query .= " ORDER BY {$orderBy} ";
 301
 302     if ($offset || $rowCount) {
 303       //Added "||$rowCount" to avoid displaying all records on first page
 304       $query .= ' LIMIT ' . CRM_Utils_Type::escape($offset, 'Integer') . ', ' . CRM_Utils_Type::escape($rowCount, 'Integer');
 305     }
 306
 307     $dao->query($query);
 308
 309     $results = array();
 310
 311     while ($dao->fetch()) {
 312       $url = CRM_Utils_System::url('civicrm/contact/view',
 313         "reset=1&cid={$dao->contact_id}"
 314       );
 315       $results[] = array(
 316         'name' => "<a href=\"$url\">{$dao->display_name}</a>",
 317         'email' => $dao->email,
 318         'date' => CRM_Utils_Date::customFormat($dao->date),
 319       );
 320     }
 321     return $results;
 322   }
 323
 324 }