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_TrackableURLOpen
extends CRM_Mailing_Event_DAO_TrackableURLOpen
{
20 * Track a click-through and return the URL to redirect.
22 * If the numbers don't match up, return the base url.
24 * @param int $queue_id
25 * The Queue Event ID of the clicker.
27 * The ID of the trackable URL.
30 * The redirection url, or base url on failure.
32 public static function track($queue_id, $url_id) {
33 // To find the url, we also join on the queue and job tables. This
34 // prevents foreign key violations.
35 $job = CRM_Utils_Type
::escape(CRM_Mailing_BAO_MailingJob
::getTableName(), 'MysqlColumnNameOrAlias');
36 $eq = CRM_Utils_Type
::escape(CRM_Mailing_Event_BAO_Queue
::getTableName(), 'MysqlColumnNameOrAlias');
37 $turl = CRM_Utils_Type
::escape(CRM_Mailing_BAO_TrackableURL
::getTableName(), 'MysqlColumnNameOrAlias');
40 $search = CRM_Core_DAO
::executeQuery(
45 1 => [$url_id, 'Integer'],
49 if (!$search->fetch()) {
50 return CRM_Utils_System
::baseURL();
56 $search = CRM_Core_DAO
::executeQuery(
57 "SELECT $turl.url as url
59 INNER JOIN $job ON $turl.mailing_id = $job.mailing_id
60 INNER JOIN $eq ON $job.id = $eq.job_id
61 WHERE $eq.id = %1 AND $turl.id = %2",
63 1 => [$queue_id, 'Integer'],
64 2 => [$url_id, 'Integer'],
68 if (!$search->fetch()) {
69 // Can't find either the URL or the queue. If we can find the URL then
70 // return the URL without tracking. Otherwise return the base URL.
71 $search = CRM_Core_DAO
::executeQuery(
72 "SELECT $turl.url as url
76 1 => [$url_id, 'Integer'],
80 if (!$search->fetch()) {
81 return CRM_Utils_System
::baseURL();
87 $open = new CRM_Mailing_Event_BAO_TrackableURLOpen();
88 $open->event_queue_id
= $queue_id;
89 $open->trackable_url_id
= $url_id;
90 $open->time_stamp
= date('YmdHis');
97 * Get row count for the event selector.
99 * @param int $mailing_id
102 * Optional ID of a job to filter on.
103 * @param bool $is_distinct
104 * Group by queue ID?.
106 * Optional ID of a url to filter on.
108 * @param string $toDate
111 * Number of rows in result set
113 public static function getTotalCount(
114 $mailing_id, $job_id = NULL,
115 $is_distinct = FALSE, $url_id = NULL, $toDate = NULL
117 $dao = new CRM_Core_DAO();
119 $click = self
::getTableName();
120 $queue = CRM_Mailing_Event_BAO_Queue
::getTableName();
121 $mailing = CRM_Mailing_BAO_Mailing
::getTableName();
122 $job = CRM_Mailing_BAO_MailingJob
::getTableName();
126 $distinct = 'DISTINCT ';
129 SELECT COUNT($distinct $click.event_queue_id) as opened
132 ON $click.event_queue_id = $queue.id
134 ON $queue.job_id = $job.id
136 ON $job.mailing_id = $mailing.id
138 WHERE $mailing.id = " . CRM_Utils_Type
::escape($mailing_id, 'Integer');
140 if (!empty($toDate)) {
141 $query .= " AND $click.time_stamp <= $toDate";
144 if (!empty($job_id)) {
145 $query .= " AND $job.id = " . CRM_Utils_Type
::escape($job_id, 'Integer');
148 if (!empty($url_id)) {
149 $query .= " AND $click.trackable_url_id = " . CRM_Utils_Type
::escape($url_id, 'Integer');
163 * Get tracked url count for each mailing for a given set of mailing IDs.
165 * @see https://issues.civicrm.org/jira/browse/CRM-12814
167 * @param array $mailingIDs
170 * trackable url count per mailing ID
172 public static function getMailingTotalCount($mailingIDs) {
173 $dao = new CRM_Core_DAO();
176 $click = self
::getTableName();
177 $queue = CRM_Mailing_Event_BAO_Queue
::getTableName();
178 $job = CRM_Mailing_BAO_MailingJob
::getTableName();
179 $mailingIDs = implode(',', $mailingIDs);
182 SELECT $job.mailing_id as mailingID, COUNT($click.id) as opened
185 ON $click.event_queue_id = $queue.id
187 ON $queue.job_id = $job.id
189 WHERE $job.mailing_id IN ({$mailingIDs})
190 GROUP BY civicrm_mailing_job.mailing_id
195 while ($dao->fetch()) {
196 $clickCount[$dao->mailingID
] = $dao->opened
;
202 * Get tracked url count for each mailing for a given set of mailing IDs.
204 * @param int[] $mailingIDs
205 * IDs of the mailing (comma separated).
206 * @param int $contactID
210 * Count per mailing ID
212 public static function getMailingContactCount($mailingIDs, $contactID) {
213 $dao = new CRM_Core_DAO();
216 $click = self
::getTableName();
217 $queue = CRM_Mailing_Event_BAO_Queue
::getTableName();
218 $job = CRM_Mailing_BAO_MailingJob
::getTableName();
219 $mailingIDs = implode(',', $mailingIDs);
222 SELECT $job.mailing_id as mailingID, COUNT($click.id) as opened
225 ON $click.event_queue_id = $queue.id
226 AND $queue.contact_id = $contactID
228 ON $queue.job_id = $job.id
230 WHERE $job.mailing_id IN ({$mailingIDs})
231 GROUP BY civicrm_mailing_job.mailing_id
236 while ($dao->fetch()) {
237 $clickCount[$dao->mailingID
] = $dao->opened
;
244 * Get rows for the event browser.
246 * @param int $mailing_id
249 * Optional ID of the job.
250 * @param bool $is_distinct
251 * Group by queue id?.
253 * Optional ID of a trackable URL to filter on.
256 * @param int $rowCount
260 * @param int $contact_id
261 * Optional contact ID.
266 public static function &getRows(
267 $mailing_id, $job_id,
268 $is_distinct, $url_id,
269 $offset = NULL, $rowCount = NULL, $sort = NULL, $contact_id = NULL
272 $dao = new CRM_Core_DAO();
274 $click = self
::getTableName();
275 $url = CRM_Mailing_BAO_TrackableURL
::getTableName();
276 $queue = CRM_Mailing_Event_BAO_Queue
::getTableName();
277 $mailing = CRM_Mailing_BAO_Mailing
::getTableName();
278 $job = CRM_Mailing_BAO_MailingJob
::getTableName();
279 $contact = CRM_Contact_BAO_Contact
::getTableName();
280 $email = CRM_Core_BAO_Email
::getTableName();
283 SELECT $contact.display_name as display_name,
284 $contact.id as contact_id,
285 $email.email as email,";
288 $query .= "MIN($click.time_stamp) as date,";
291 $query .= "$click.time_stamp as date,";
294 $query .= "$url.url as url
297 ON $queue.contact_id = $contact.id
299 ON $queue.email_id = $email.id
301 ON $click.event_queue_id = $queue.id
303 ON $click.trackable_url_id = $url.id
305 ON $queue.job_id = $job.id
307 ON $job.mailing_id = $mailing.id
309 WHERE $mailing.id = " . CRM_Utils_Type
::escape($mailing_id, 'Integer');
311 if (!empty($contact_id)) {
312 $query .= " AND $contact.id = " . CRM_Utils_Type
::escape($contact_id, 'Integer');
315 if (!empty($job_id)) {
316 $query .= " AND $job.id = " . CRM_Utils_Type
::escape($job_id, 'Integer');
319 if (!empty($url_id)) {
320 $query .= " AND $url.id = " . CRM_Utils_Type
::escape($url_id, 'Integer');
324 $query .= " GROUP BY $queue.id, $url.url ";
327 $orderBy = "sort_name ASC, {$click}.time_stamp DESC";
329 if (is_string($sort)) {
330 $sort = CRM_Utils_Type
::escape($sort, 'String');
334 $orderBy = trim($sort->orderBy());
338 $query .= " ORDER BY {$orderBy} ";
340 if ($offset ||
$rowCount) {
341 //Added "||$rowCount" to avoid displaying all records on first page
342 $query .= ' LIMIT ' . CRM_Utils_Type
::escape($offset, 'Integer') . ', ' . CRM_Utils_Type
::escape($rowCount, 'Integer');
344 CRM_Core_DAO
::disableFullGroupByMode();
346 CRM_Core_DAO
::reenableFullGroupByMode();
349 while ($dao->fetch()) {
350 $url = CRM_Utils_System
::url('civicrm/contact/view',
351 "reset=1&cid={$dao->contact_id}"
354 'name' => "<a href=\"$url\">{$dao->display_name}</a>",
355 'email' => $dao->email
,
357 'date' => CRM_Utils_Date
::customFormat($dao->date
),