3 +--------------------------------------------------------------------+
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2018 |
7 +--------------------------------------------------------------------+
8 | This file is a part of CiviCRM. |
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. |
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. |
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 +--------------------------------------------------------------------+
31 * @copyright CiviCRM LLC (c) 2004-2018
33 class CRM_Mailing_Event_BAO_TrackableURLOpen
extends CRM_Mailing_Event_DAO_TrackableURLOpen
{
38 public function __construct() {
39 parent
::__construct();
43 * Track a click-through and return the URL to redirect.
45 * If the numbers don't match up, return the base url.
47 * @param int $queue_id
48 * The Queue Event ID of the clicker.
50 * The ID of the trackable URL.
53 * The redirection url, or base url on failure.
55 public static function track($queue_id, $url_id) {
56 // To find the url, we also join on the queue and job tables. This
57 // prevents foreign key violations.
58 $job = CRM_Utils_Type
::escape(CRM_Mailing_BAO_MailingJob
::getTableName(), 'MysqlColumnNameOrAlias');
59 $eq = CRM_Utils_Type
::escape(CRM_Mailing_Event_BAO_Queue
::getTableName(), 'MysqlColumnNameOrAlias');
60 $turl = CRM_Utils_Type
::escape(CRM_Mailing_BAO_TrackableURL
::getTableName(), 'MysqlColumnNameOrAlias');
63 $search = CRM_Core_DAO
::executeQuery(
68 1 => array($url_id, 'Integer'),
72 if (!$search->fetch()) {
73 return CRM_Utils_System
::baseURL();
79 $search = CRM_Core_DAO
::executeQuery(
80 "SELECT $turl.url as url
82 INNER JOIN $job ON $turl.mailing_id = $job.mailing_id
83 INNER JOIN $eq ON $job.id = $eq.job_id
84 WHERE $eq.id = %1 AND $turl.id = %2",
86 1 => array($queue_id, 'Integer'),
87 2 => array($url_id, 'Integer'),
91 if (!$search->fetch()) {
92 // Can't find either the URL or the queue. If we can find the URL then
93 // return the URL without tracking. Otherwise return the base URL.
94 $search = CRM_Core_DAO
::executeQuery(
95 "SELECT $turl.url as url
99 1 => array($url_id, 'Integer'),
103 if (!$search->fetch()) {
104 return CRM_Utils_System
::baseURL();
110 $open = new CRM_Mailing_Event_BAO_TrackableURLOpen();
111 $open->event_queue_id
= $queue_id;
112 $open->trackable_url_id
= $url_id;
113 $open->time_stamp
= date('YmdHis');
120 * Get row count for the event selector.
122 * @param int $mailing_id
125 * Optional ID of a job to filter on.
126 * @param bool $is_distinct
127 * Group by queue ID?.
129 * Optional ID of a url to filter on.
131 * @param string $toDate
134 * Number of rows in result set
136 public static function getTotalCount(
137 $mailing_id, $job_id = NULL,
138 $is_distinct = FALSE, $url_id = NULL, $toDate = NULL
140 $dao = new CRM_Core_DAO();
142 $click = self
::getTableName();
143 $queue = CRM_Mailing_Event_BAO_Queue
::getTableName();
144 $mailing = CRM_Mailing_BAO_Mailing
::getTableName();
145 $job = CRM_Mailing_BAO_MailingJob
::getTableName();
149 $distinct = 'DISTINCT ';
152 SELECT COUNT($distinct $click.event_queue_id) as opened
155 ON $click.event_queue_id = $queue.id
157 ON $queue.job_id = $job.id
159 ON $job.mailing_id = $mailing.id
161 WHERE $mailing.id = " . CRM_Utils_Type
::escape($mailing_id, 'Integer');
163 if (!empty($toDate)) {
164 $query .= " AND $click.time_stamp <= $toDate";
167 if (!empty($job_id)) {
168 $query .= " AND $job.id = " . CRM_Utils_Type
::escape($job_id, 'Integer');
171 if (!empty($url_id)) {
172 $query .= " AND $click.trackable_url_id = " . CRM_Utils_Type
::escape($url_id, 'Integer');
186 * Get tracked url count for each mailing for a given set of mailing IDs.
190 * @param array $mailingIDs
193 * trackable url count per mailing ID
195 public static function getMailingTotalCount($mailingIDs) {
196 $dao = new CRM_Core_DAO();
197 $clickCount = array();
199 $click = self
::getTableName();
200 $queue = CRM_Mailing_Event_BAO_Queue
::getTableName();
201 $job = CRM_Mailing_BAO_MailingJob
::getTableName();
202 $mailingIDs = implode(',', $mailingIDs);
205 SELECT $job.mailing_id as mailingID, COUNT($click.id) as opened
208 ON $click.event_queue_id = $queue.id
210 ON $queue.job_id = $job.id
212 WHERE $job.mailing_id IN ({$mailingIDs})
213 GROUP BY civicrm_mailing_job.mailing_id
218 while ($dao->fetch()) {
219 $clickCount[$dao->mailingID
] = $dao->opened
;
225 * Get tracked url count for each mailing for a given set of mailing IDs.
227 * @param int $mailingIDs
228 * IDs of the mailing (comma separated).
229 * @param int $contactID
233 * Count per mailing ID
235 public static function getMailingContactCount($mailingIDs, $contactID) {
236 $dao = new CRM_Core_DAO();
237 $clickCount = array();
239 $click = self
::getTableName();
240 $queue = CRM_Mailing_Event_BAO_Queue
::getTableName();
241 $job = CRM_Mailing_BAO_MailingJob
::getTableName();
242 $mailingIDs = implode(',', $mailingIDs);
245 SELECT $job.mailing_id as mailingID, COUNT($click.id) as opened
248 ON $click.event_queue_id = $queue.id
249 AND $queue.contact_id = $contactID
251 ON $queue.job_id = $job.id
253 WHERE $job.mailing_id IN ({$mailingIDs})
254 GROUP BY civicrm_mailing_job.mailing_id
259 while ($dao->fetch()) {
260 $clickCount[$dao->mailingID
] = $dao->opened
;
267 * Get rows for the event browser.
269 * @param int $mailing_id
272 * Optional ID of the job.
273 * @param bool $is_distinct
274 * Group by queue id?.
276 * Optional ID of a trackable URL to filter on.
279 * @param int $rowCount
283 * @param int $contact_id
284 * Optional contact ID.
289 public static function &getRows(
290 $mailing_id, $job_id = NULL,
291 $is_distinct = FALSE, $url_id,
292 $offset = NULL, $rowCount = NULL, $sort = NULL, $contact_id = NULL
295 $dao = new CRM_Core_Dao();
297 $click = self
::getTableName();
298 $url = CRM_Mailing_BAO_TrackableURL
::getTableName();
299 $queue = CRM_Mailing_Event_BAO_Queue
::getTableName();
300 $mailing = CRM_Mailing_BAO_Mailing
::getTableName();
301 $job = CRM_Mailing_BAO_MailingJob
::getTableName();
302 $contact = CRM_Contact_BAO_Contact
::getTableName();
303 $email = CRM_Core_BAO_Email
::getTableName();
306 SELECT $contact.display_name as display_name,
307 $contact.id as contact_id,
308 $email.email as email,";
311 $query .= "MIN($click.time_stamp) as date,";
314 $query .= "$click.time_stamp as date,";
317 $query .= "$url.url as url
320 ON $queue.contact_id = $contact.id
322 ON $queue.email_id = $email.id
324 ON $click.event_queue_id = $queue.id
326 ON $click.trackable_url_id = $url.id
328 ON $queue.job_id = $job.id
330 ON $job.mailing_id = $mailing.id
332 WHERE $mailing.id = " . CRM_Utils_Type
::escape($mailing_id, 'Integer');
334 if (!empty($contact_id)) {
335 $query .= " AND $contact.id = " . CRM_Utils_Type
::escape($contact_id, 'Integer');
338 if (!empty($job_id)) {
339 $query .= " AND $job.id = " . CRM_Utils_Type
::escape($job_id, 'Integer');
342 if (!empty($url_id)) {
343 $query .= " AND $url.id = " . CRM_Utils_Type
::escape($url_id, 'Integer');
347 $query .= " GROUP BY $queue.id, $url.url ";
350 $orderBy = "sort_name ASC, {$click}.time_stamp DESC";
352 if (is_string($sort)) {
353 $sort = CRM_Utils_Type
::escape($sort, 'String');
357 $orderBy = trim($sort->orderBy());
361 $query .= " ORDER BY {$orderBy} ";
363 if ($offset ||
$rowCount) {
364 //Added "||$rowCount" to avoid displaying all records on first page
365 $query .= ' LIMIT ' . CRM_Utils_Type
::escape($offset, 'Integer') . ', ' . CRM_Utils_Type
::escape($rowCount, 'Integer');
367 CRM_Core_DAO
::disableFullGroupByMode();
369 CRM_Core_DAO
::reenableFullGroupByMode();
372 while ($dao->fetch()) {
373 $url = CRM_Utils_System
::url('civicrm/contact/view',
374 "reset=1&cid={$dao->contact_id}"
377 'name' => "<a href=\"$url\">{$dao->display_name}</a>",
378 'email' => $dao->email
,
380 'date' => CRM_Utils_Date
::customFormat($dao->date
),