CRM/Mailing/Event/BAO/Opened.php

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