Created
May 17, 2015 03:10
-
-
Save anonymous/2ba8de7c37dd37c04bc7 to your computer and use it in GitHub Desktop.
trackableURLOpen.php
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php | |
/* | |
+--------------------------------------------------------------------+ | |
| CiviCRM version 4.6 | | |
+--------------------------------------------------------------------+ | |
| Copyright CiviCRM LLC (c) 2004-2015 | | |
+--------------------------------------------------------------------+ | |
| This file is a part of CiviCRM. | | |
| | | |
| CiviCRM is free software; you can copy, modify, and distribute it | | |
| under the terms of the GNU Affero General Public License | | |
| Version 3, 19 November 2007 and the CiviCRM Licensing Exception. | | |
| | | |
| CiviCRM is distributed in the hope that it will be useful, but | | |
| WITHOUT ANY WARRANTY; without even the implied warranty of | | |
| MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. | | |
| See the GNU Affero General Public License for more details. | | |
| | | |
| You should have received a copy of the GNU Affero General Public | | |
| License and the CiviCRM Licensing Exception along | | |
| with this program; if not, contact CiviCRM LLC | | |
| at info[AT]civicrm[DOT]org. If you have questions about the | | |
| GNU Affero General Public License or the licensing of CiviCRM, | | |
| see the CiviCRM license FAQ at http://civicrm.org/licensing | | |
+--------------------------------------------------------------------+ | |
*/ | |
/** | |
* | |
* @package CRM | |
* @copyright CiviCRM LLC (c) 2004-2015 | |
* $Id$ | |
* | |
*/ | |
class CRM_Mailing_Event_BAO_TrackableURLOpen extends CRM_Mailing_Event_DAO_TrackableURLOpen { | |
/** | |
* Class constructor. | |
*/ | |
public function __construct() { | |
parent::__construct(); | |
} | |
/** | |
* Track a click-through and return the URL to redirect. If the numbers | |
* don't match up, return the base url. | |
* | |
* @param int $queue_id | |
* The Queue Event ID of the clicker. | |
* @param int $url_id | |
* The ID of the trackable URL. | |
* | |
* @return string | |
* The redirection url, or base url on failure. | |
*/ | |
public static function track($queue_id, $url_id) { | |
$search = new CRM_Mailing_BAO_TrackableURL(); | |
/* To find the url, we also join on the queue and job tables. This | |
* prevents foreign key violations. */ | |
$job = CRM_Mailing_BAO_MailingJob::getTableName(); | |
$eq = CRM_Mailing_Event_BAO_Queue::getTableName(); | |
$turl = CRM_Mailing_BAO_TrackableURL::getTableName(); | |
if (!$queue_id) { | |
$search->query("SELECT $turl.url as url from $turl | |
WHERE $turl.id = " . CRM_Utils_Type::escape($url_id, 'Integer') | |
); | |
if (!$search->fetch()) { | |
return CRM_Utils_System::baseURL(); | |
} | |
return $search->url; | |
} | |
$search->query("SELECT $turl.url as url from $turl | |
INNER JOIN $job ON $turl.mailing_id = $job.mailing_id | |
INNER JOIN $eq ON $job.id = $eq.job_id | |
WHERE $eq.id = " . CRM_Utils_Type::escape($queue_id, 'Integer') . " AND $turl.id = " . CRM_Utils_Type::escape($url_id, 'Integer') | |
); | |
if (!$search->fetch()) { | |
/* Whoops, error, don't track it. Return the base url. */ | |
return CRM_Utils_System::baseURL(); | |
} | |
$open = new CRM_Mailing_Event_BAO_TrackableURLOpen(); | |
$open->event_queue_id = $queue_id; | |
$open->trackable_url_id = $url_id; | |
$open->time_stamp = date('YmdHis'); | |
$open->save(); | |
return $search->url; | |
} | |
/** | |
* Get row count for the event selector. | |
* | |
* @param int $mailing_id | |
* ID of the mailing. | |
* @param int $job_id | |
* Optional ID of a job to filter on. | |
* @param bool $is_distinct | |
* Group by queue ID?. | |
* @param int $url_id | |
* Optional ID of a url to filter on. | |
* | |
* @return int | |
* Number of rows in result set | |
*/ | |
public static function getTotalCount( | |
$mailing_id, $job_id = NULL, | |
$is_distinct = FALSE, $url_id = NULL, $toDate = NULL | |
) { | |
$dao = new CRM_Core_DAO(); | |
$click = self::getTableName(); | |
$queue = CRM_Mailing_Event_BAO_Queue::getTableName(); | |
$mailing = CRM_Mailing_BAO_Mailing::getTableName(); | |
$job = CRM_Mailing_BAO_MailingJob::getTableName(); | |
$query = " | |
SELECT COUNT($click.id) as opened | |
FROM $click | |
INNER JOIN $queue | |
ON $click.event_queue_id = $queue.id | |
INNER JOIN $job | |
ON $queue.job_id = $job.id | |
INNER JOIN $mailing | |
ON $job.mailing_id = $mailing.id | |
AND $job.is_test = 0 | |
WHERE $mailing.id = " . CRM_Utils_Type::escape($mailing_id, 'Integer'); | |
if (!empty($toDate)) { | |
$query .= " AND $click.time_stamp <= $toDate"; | |
} | |
if (!empty($job_id)) { | |
$query .= " AND $job.id = " . CRM_Utils_Type::escape($job_id, 'Integer'); | |
} | |
if (!empty($url_id)) { | |
$query .= " AND $click.trackable_url_id = " . CRM_Utils_Type::escape($url_id, 'Integer'); | |
} | |
if ($is_distinct) { | |
$query .= " GROUP BY $queue.id "; | |
} | |
// query was missing | |
$dao->query($query); | |
if ($dao->fetch()) { | |
return $dao->opened; | |
} | |
return NULL; | |
} | |
/** | |
* CRM-12814 | |
* Get tracked url count for each mailing for a given set of mailing IDs | |
* | |
* @param $mailingIDs | |
* | |
* @return array | |
* trackable url count per mailing ID | |
*/ | |
public static function getMailingTotalCount($mailingIDs) { | |
$dao = new CRM_Core_DAO(); | |
$clickCount = array(); | |
$click = self::getTableName(); | |
$queue = CRM_Mailing_Event_BAO_Queue::getTableName(); | |
$job = CRM_Mailing_BAO_MailingJob::getTableName(); | |
$mailingIDs = implode(',', $mailingIDs); | |
$query = " | |
SELECT $job.mailing_id as mailingID, COUNT($click.id) as opened | |
FROM $click | |
INNER JOIN $queue | |
ON $click.event_queue_id = $queue.id | |
INNER JOIN $job | |
ON $queue.job_id = $job.id | |
AND $job.is_test = 0 | |
WHERE $job.mailing_id IN ({$mailingIDs}) | |
GROUP BY civicrm_mailing_job.mailing_id | |
"; | |
$dao->query($query); | |
while ($dao->fetch()) { | |
$clickCount[$dao->mailingID] = $dao->opened; | |
} | |
return $clickCount; | |
} | |
/** | |
* Get tracked url count for each mailing for a given set of mailing IDs. | |
* | |
* @param int $mailingIDs | |
* IDs of the mailing (comma separated). | |
* @param int $contactID | |
* ID of the contact. | |
* | |
* @return array | |
* Count per mailing ID | |
*/ | |
public static function getMailingContactCount($mailingIDs, $contactID) { | |
$dao = new CRM_Core_DAO(); | |
$clickCount = array(); | |
$click = self::getTableName(); | |
$queue = CRM_Mailing_Event_BAO_Queue::getTableName(); | |
$job = CRM_Mailing_BAO_MailingJob::getTableName(); | |
$mailingIDs = implode(',', $mailingIDs); | |
$query = " | |
SELECT $job.mailing_id as mailingID, COUNT($click.id) as opened | |
FROM $click | |
INNER JOIN $queue | |
ON $click.event_queue_id = $queue.id | |
AND $queue.contact_id = $contactID | |
INNER JOIN $job | |
ON $queue.job_id = $job.id | |
AND $job.is_test = 0 | |
WHERE $job.mailing_id IN ({$mailingIDs}) | |
GROUP BY civicrm_mailing_job.mailing_id | |
"; | |
$dao->query($query); | |
while ($dao->fetch()) { | |
$clickCount[$dao->mailingID] = $dao->opened; | |
} | |
return $clickCount; | |
} | |
/** | |
* Get rows for the event browser. | |
* | |
* @param int $mailing_id | |
* ID of the mailing. | |
* @param int $job_id | |
* Optional ID of the job. | |
* @param bool $is_distinct | |
* Group by queue id?. | |
* @param int $url_id | |
* Optional ID of a trackable URL to filter on. | |
* @param int $offset | |
* Offset. | |
* @param int $rowCount | |
* Number of rows. | |
* @param array $sort | |
* Sort array. | |
* @param int $contact_id | |
* Optional contact ID. | |
* | |
* @return array | |
* Result set | |
*/ | |
public static function &getRows( | |
$mailing_id, $job_id = NULL, | |
$is_distinct = FALSE, $url_id, | |
$offset = NULL, $rowCount = NULL, $sort = NULL, $contact_id = NULL | |
) { | |
$dao = new CRM_Core_Dao(); | |
$click = self::getTableName(); | |
$url = CRM_Mailing_BAO_TrackableURL::getTableName(); | |
$queue = CRM_Mailing_Event_BAO_Queue::getTableName(); | |
$mailing = CRM_Mailing_BAO_Mailing::getTableName(); | |
$job = CRM_Mailing_BAO_MailingJob::getTableName(); | |
$contact = CRM_Contact_BAO_Contact::getTableName(); | |
$email = CRM_Core_BAO_Email::getTableName(); | |
$query = " | |
SELECT $contact.display_name as display_name, | |
$contact.id as contact_id, | |
$email.email as email, | |
$click.time_stamp as date, | |
$url.url as url | |
FROM $contact | |
INNER JOIN $queue | |
ON $queue.contact_id = $contact.id | |
INNER JOIN $email | |
ON $queue.email_id = $email.id | |
INNER JOIN $click | |
ON $click.event_queue_id = $queue.id | |
INNER JOIN $url | |
ON $click.trackable_url_id = $url.id | |
INNER JOIN $job | |
ON $queue.job_id = $job.id | |
INNER JOIN $mailing | |
ON $job.mailing_id = $mailing.id | |
AND $job.is_test = 0 | |
WHERE $mailing.id = " . CRM_Utils_Type::escape($mailing_id, 'Integer'); | |
if (!empty($contact_id)) { | |
$query .= " AND $contact.id = " . CRM_Utils_Type::escape($contact_id, 'Integer'); | |
} | |
if (!empty($job_id)) { | |
$query .= " AND $job.id = " . CRM_Utils_Type::escape($job_id, 'Integer'); | |
} | |
if (!empty($url_id)) { | |
$query .= " AND $url.id = " . CRM_Utils_Type::escape($url_id, 'Integer'); | |
} | |
if ($is_distinct) { | |
$query .= " GROUP BY $queue.id "; | |
} | |
$orderBy = "sort_name ASC, {$click}.time_stamp DESC"; | |
if ($sort) { | |
if (is_string($sort)) { | |
$sort = CRM_Utils_Type::escape($sort, 'String'); | |
$orderBy = $sort; | |
} | |
else { | |
$orderBy = trim($sort->orderBy()); | |
} | |
} | |
$query .= " ORDER BY {$orderBy} "; | |
if ($offset || $rowCount) { | |
//Added "||$rowCount" to avoid displaying all records on first page | |
$query .= ' LIMIT ' . CRM_Utils_Type::escape($offset, 'Integer') . ', ' . CRM_Utils_Type::escape($rowCount, 'Integer'); | |
} | |
$dao->query($query); | |
$results = array(); | |
while ($dao->fetch()) { | |
$url = CRM_Utils_System::url('civicrm/contact/view', | |
"reset=1&cid={$dao->contact_id}" | |
); | |
$results[] = array( | |
'name' => "<a href=\"$url\">{$dao->display_name}</a>", | |
'email' => $dao->email, | |
'url' => $dao->url, | |
'date' => CRM_Utils_Date::customFormat($dao->date), | |
); | |
} | |
return $results; | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment