CRM/Mailing/Event/BAO/Opened.php

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