3 +--------------------------------------------------------------------+
4 | Copyright CiviCRM LLC. All rights reserved. |
6 | This work is published under the GNU AGPLv3 license with some |
7 | permitted exceptions and without any warranty. For full license |
8 | and copyright information, see https://civicrm.org/licensing |
9 +--------------------------------------------------------------------+
15 * @copyright CiviCRM LLC https://civicrm.org/licensing
17 class CRM_Mailing_Event_BAO_Opened
extends CRM_Mailing_Event_DAO_Opened
{
20 * Register an open event.
22 * @param int $queue_id
23 * The Queue Event ID of the recipient.
27 public static function open($queue_id) {
28 // First make sure there's a matching queue event.
32 $q = new CRM_Mailing_Event_BAO_Queue();
35 $oe = new CRM_Mailing_Event_BAO_Opened();
36 $oe->event_queue_id
= $queue_id;
37 $oe->time_stamp
= date('YmdHis');
46 * Get row count for the event selector.
48 * @param int $mailing_id
51 * Optional ID of a job to filter on.
52 * @param bool $is_distinct
55 * @param string $toDate
58 * Number of rows in result set
60 public static function getTotalCount(
66 $dao = new CRM_Core_DAO();
68 $open = self
::getTableName();
69 $queue = CRM_Mailing_Event_BAO_Queue
::getTableName();
70 $mailing = CRM_Mailing_BAO_Mailing
::getTableName();
71 $job = CRM_Mailing_BAO_MailingJob
::getTableName();
74 SELECT COUNT($open.id) as opened
77 ON $open.event_queue_id = $queue.id
79 ON $queue.job_id = $job.id
81 ON $job.mailing_id = $mailing.id
83 WHERE $mailing.id = " . CRM_Utils_Type
::escape($mailing_id, 'Integer');
85 if (!empty($toDate)) {
86 $query .= " AND $open.time_stamp <= $toDate";
89 if (!empty($job_id)) {
90 $query .= " AND $job.id = " . CRM_Utils_Type
::escape($job_id, 'Integer');
94 $query .= " GROUP BY $queue.id ";
103 return $dao->opened ?
$dao->opened
: 0;
108 * @see https://issues.civicrm.org/jira/browse/CRM-12814
109 * Get opened count for each mailing for a given set of mailing IDs
114 * Opened count per mailing ID
116 public static function getMailingTotalCount($mailingIDs) {
117 $dao = new CRM_Core_DAO();
120 $open = self
::getTableName();
121 $queue = CRM_Mailing_Event_BAO_Queue
::getTableName();
122 $job = CRM_Mailing_BAO_MailingJob
::getTableName();
123 $mailingIDs = implode(',', $mailingIDs);
126 SELECT $job.mailing_id as mailingID, COUNT($open.id) as opened
129 ON $open.event_queue_id = $queue.id
131 ON $queue.job_id = $job.id
133 WHERE $job.mailing_id IN ({$mailingIDs})
134 GROUP BY civicrm_mailing_job.mailing_id
139 while ($dao->fetch()) {
140 $openedCount[$dao->mailingID
] = $dao->opened
;
146 * Get opened count for each mailing for a given set of mailing IDs and a specific contact.
148 * @param int[] $mailingIDs
149 * IDs of the mailing (comma separated).
150 * @param int $contactID
154 * Count per mailing ID
156 public static function getMailingContactCount($mailingIDs, $contactID) {
157 $dao = new CRM_Core_DAO();
160 $open = self
::getTableName();
161 $queue = CRM_Mailing_Event_BAO_Queue
::getTableName();
162 $job = CRM_Mailing_BAO_MailingJob
::getTableName();
163 $mailingIDs = implode(',', $mailingIDs);
166 SELECT $job.mailing_id as mailingID, COUNT($open.id) as opened
169 ON $open.event_queue_id = $queue.id
170 AND $queue.contact_id = $contactID
172 ON $queue.job_id = $job.id
174 WHERE $job.mailing_id IN ({$mailingIDs})
175 GROUP BY civicrm_mailing_job.mailing_id
180 while ($dao->fetch()) {
181 $openedCount[$dao->mailingID
] = $dao->opened
;
188 * Get rows for the event browser.
190 * @param int $mailing_id
193 * Optional ID of the job.
194 * @param bool $is_distinct
195 * Group by queue id?.
198 * @param int $rowCount
203 * @param int $contact_id
208 public static function &getRows(
209 $mailing_id, $job_id = NULL,
210 $is_distinct = FALSE, $offset = NULL, $rowCount = NULL, $sort = NULL, $contact_id = NULL
212 $dao = new CRM_Core_DAO();
214 $open = self
::getTableName();
215 $queue = CRM_Mailing_Event_BAO_Queue
::getTableName();
216 $mailing = CRM_Mailing_BAO_Mailing
::getTableName();
217 $job = CRM_Mailing_BAO_MailingJob
::getTableName();
218 $contact = CRM_Contact_BAO_Contact
::getTableName();
219 $email = CRM_Core_BAO_Email
::getTableName();
222 "$contact.display_name as display_name",
223 "$contact.id as contact_id",
224 "$email.email as email",
225 ($is_distinct) ?
"MIN({$open}.time_stamp) as date" : "{$open}.time_stamp as date",
229 $groupBy = " GROUP BY $queue.id ";
230 $select = CRM_Contact_BAO_Query
::appendAnyValueToSelect($selectClauses, "$queue.id");
234 $select = " SELECT " . implode(', ', $selectClauses);
241 ON $queue.contact_id = $contact.id
243 ON $queue.email_id = $email.id
245 ON $open.event_queue_id = $queue.id
247 ON $queue.job_id = $job.id
249 ON $job.mailing_id = $mailing.id
251 WHERE $mailing.id = " . CRM_Utils_Type
::escape($mailing_id, 'Integer');
253 if (!empty($job_id)) {
254 $query .= " AND $job.id = " . CRM_Utils_Type
::escape($job_id, 'Integer');
257 if (!empty($contact_id)) {
258 $query .= " AND $contact.id = " . CRM_Utils_Type
::escape($contact_id, 'Integer');
263 $orderBy = "sort_name ASC";
265 $orderBy .= ", {$open}.time_stamp DESC";
268 if (is_string($sort)) {
269 $sort = CRM_Utils_Type
::escape($sort, 'String');
273 $orderBy = trim($sort->orderBy());
277 $query .= " ORDER BY {$orderBy} ";
279 if ($offset ||
$rowCount) {
280 //Added "||$rowCount" to avoid displaying all records on first page
281 $query .= ' LIMIT ' . CRM_Utils_Type
::escape($offset, 'Integer') . ', ' . CRM_Utils_Type
::escape($rowCount, 'Integer');
288 while ($dao->fetch()) {
289 $url = CRM_Utils_System
::url('civicrm/contact/view',
290 "reset=1&cid={$dao->contact_id}"
293 'name' => "<a href=\"$url\">{$dao->display_name}</a>",
294 'email' => $dao->email
,
295 'date' => CRM_Utils_Date
::customFormat($dao->date
),