3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.3 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2013 |
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-2013
35 class CRM_Mailing_Event_BAO_TrackableURLOpen
extends CRM_Mailing_Event_DAO_TrackableURLOpen
{
40 function __construct() {
41 parent
::__construct();
45 * Track a click-through and return the URL to redirect. If the numbers
46 * don't match up, return the base url.
48 * @param int $queue_id The Queue Event ID of the clicker
49 * @param int $url_id The ID of the trackable URL
51 * @return string $url The redirection url, or base url on failure.
55 public static function track($queue_id, $url_id) {
57 $search = new CRM_Mailing_BAO_TrackableURL();
59 /* To find the url, we also join on the queue and job tables. This
60 * prevents foreign key violations. */
63 $job = CRM_Mailing_BAO_Job
::getTableName();
64 $eq = CRM_Mailing_Event_BAO_Queue
::getTableName();
65 $turl = CRM_Mailing_BAO_TrackableURL
::getTableName();
68 $search->query("SELECT $turl.url as url from $turl
69 WHERE $turl.id = " . CRM_Utils_Type
::escape($url_id, 'Integer')
71 if (!$search->fetch()) {
72 return CRM_Utils_System
::baseURL();
77 $search->query("SELECT $turl.url as url from $turl
78 INNER JOIN $job ON $turl.mailing_id = $job.mailing_id
79 INNER JOIN $eq ON $job.id = $eq.job_id
80 WHERE $eq.id = " . CRM_Utils_Type
::escape($queue_id, 'Integer') . " AND $turl.id = " . CRM_Utils_Type
::escape($url_id, 'Integer')
83 if (!$search->fetch()) {
84 /* Whoops, error, don't track it. Return the base url. */
86 return CRM_Utils_System
::baseURL();
89 $open = new CRM_Mailing_Event_BAO_TrackableURLOpen();
90 $open->event_queue_id
= $queue_id;
91 $open->trackable_url_id
= $url_id;
92 $open->time_stamp
= date('YmdHis');
99 * Get row count for the event selector
101 * @param int $mailing_id ID of the mailing
102 * @param int $job_id Optional ID of a job to filter on
103 * @param boolean $is_distinct Group by queue ID?
104 * @param int $url_id Optional ID of a url to filter on
106 * @return int Number of rows in result set
110 public static function getTotalCount($mailing_id, $job_id = NULL,
111 $is_distinct = FALSE, $url_id = NULL
113 $dao = new CRM_Core_DAO();
115 $click = self
::getTableName();
116 $queue = CRM_Mailing_Event_BAO_Queue
::getTableName();
117 $mailing = CRM_Mailing_BAO_Mailing
::getTableName();
118 $job = CRM_Mailing_BAO_Job
::getTableName();
121 SELECT COUNT($click.id) as opened
124 ON $click.event_queue_id = $queue.id
126 ON $queue.job_id = $job.id
128 ON $job.mailing_id = $mailing.id
130 WHERE $mailing.id = " . CRM_Utils_Type
::escape($mailing_id, 'Integer');
132 if (!empty($job_id)) {
133 $query .= " AND $job.id = " . CRM_Utils_Type
::escape($job_id, 'Integer');
136 if (!empty($url_id)) {
137 $query .= " AND $click.trackable_url_id = " . CRM_Utils_Type
::escape($url_id, 'Integer');
141 $query .= " GROUP BY $queue.id ";
155 * Get rows for the event browser
157 * @param int $mailing_id ID of the mailing
158 * @param int $job_id optional ID of the job
159 * @param boolean $is_distinct Group by queue id?
160 * @param int $url_id optional ID of a trackable URL to filter on
161 * @param int $offset Offset
162 * @param int $rowCount Number of rows
163 * @param array $sort sort array
164 * @param int $contact_id optional contact ID
166 * @return array Result set
170 public static function &getRows($mailing_id, $job_id = NULL,
171 $is_distinct = FALSE, $url_id,
172 $offset = NULL, $rowCount = NULL, $sort = NULL, $contact_id = NULL
175 $dao = new CRM_Core_Dao();
177 $click = self
::getTableName();
178 $url = CRM_Mailing_BAO_TrackableURL
::getTableName();
179 $queue = CRM_Mailing_Event_BAO_Queue
::getTableName();
180 $mailing = CRM_Mailing_BAO_Mailing
::getTableName();
181 $job = CRM_Mailing_BAO_Job
::getTableName();
182 $contact = CRM_Contact_BAO_Contact
::getTableName();
183 $email = CRM_Core_BAO_Email
::getTableName();
186 SELECT $contact.display_name as display_name,
187 $contact.id as contact_id,
188 $email.email as email,
189 $click.time_stamp as date,
193 ON $queue.contact_id = $contact.id
195 ON $queue.email_id = $email.id
197 ON $click.event_queue_id = $queue.id
199 ON $click.trackable_url_id = $url.id
201 ON $queue.job_id = $job.id
203 ON $job.mailing_id = $mailing.id
205 WHERE $mailing.id = " . CRM_Utils_Type
::escape($mailing_id, 'Integer');
207 if (!empty($contact_id)) {
208 $query .= " AND $contact.id = " . CRM_Utils_Type
::escape($contact_id, 'Integer');
211 if (!empty($job_id)) {
212 $query .= " AND $job.id = " . CRM_Utils_Type
::escape($job_id, 'Integer');
215 if (!empty($url_id)) {
216 $query .= " AND $url.id = " . CRM_Utils_Type
::escape($url_id, 'Integer');
220 $query .= " GROUP BY $queue.id ";
223 $orderBy = "sort_name ASC, {$click}.time_stamp DESC";
225 if (is_string($sort)) {
226 $sort = CRM_Utils_Type
::escape($sort, 'String');
230 $orderBy = trim($sort->orderBy());
234 $query .= " ORDER BY {$orderBy} ";
236 if ($offset ||
$rowCount) {
237 //Added "||$rowCount" to avoid displaying all records on first page
238 $query .= ' LIMIT ' . CRM_Utils_Type
::escape($offset, 'Integer') . ', ' . CRM_Utils_Type
::escape($rowCount, 'Integer');
245 while ($dao->fetch()) {
246 $url = CRM_Utils_System
::url('civicrm/contact/view',
247 "reset=1&cid={$dao->contact_id}"
250 'name' => "<a href=\"$url\">{$dao->display_name}</a>",
251 'email' => $dao->email
,
253 'date' => CRM_Utils_Date
::customFormat($dao->date
),