/**
* Returns an array containing the following information:
* - referer_type
* - direct -- absence of referrer URL OR referrer URL has the same host
* - site -- based on the referrer URL
* - search_engine -- based on the referrer URL
* - campaign -- based on campaign URL parameter
*
* - referer_name
* - ()
* - piwik.net -- site host name
* - google.fr -- search engine host name
* - adwords-search -- campaign name
*
* - referer_keyword
* - ()
* - ()
* - my keyword
* - my paid keyword
* - ()
* - ()
*
* - referer_url : the same for all the referrer types
*
* @param string $referrerUrl must be URL Encoded
* @param string $currentUrl
* @param int $idSite
* @return array
*/
public function getReferrerInformation($referrerUrl, $currentUrl, $idSite)
{
$this->idsite = $idSite;
// default values for the referer_* fields
$referrerUrl = Common::unsanitizeInputValue($referrerUrl);
if (!empty($referrerUrl) && !UrlHelper::isLookLikeUrl($referrerUrl)) {
$referrerUrl = '';
}
$currentUrl = PageUrl::cleanupUrl($currentUrl);
$this->referrerUrl = $referrerUrl;
$this->referrerUrlParse = @parse_url($this->referrerUrl);
$this->currentUrlParse = @parse_url($currentUrl);
$this->typeReferrerAnalyzed = Common::REFERRER_TYPE_DIRECT_ENTRY;
$this->nameReferrerAnalyzed = '';
$this->keywordReferrerAnalyzed = '';
$this->referrerHost = '';
if (isset($this->referrerUrlParse['host'])) {
$this->referrerHost = $this->referrerUrlParse['host'];
}
$referrerDetected = $this->detectReferrerCampaign();
if (!$referrerDetected) {
if ($this->detectReferrerDirectEntry() || $this->detectReferrerSearchEngine()) {
$referrerDetected = true;
}
}
if (!empty($this->referrerHost) && !$referrerDetected) {
$this->typeReferrerAnalyzed = Common::REFERRER_TYPE_WEBSITE;
$this->nameReferrerAnalyzed = Common::mb_strtolower($this->referrerHost);
}
$referrerInformation = array('referer_type' => $this->typeReferrerAnalyzed, 'referer_name' => $this->nameReferrerAnalyzed, 'referer_keyword' => $this->keywordReferrerAnalyzed, 'referer_url' => $this->referrerUrl);
return $referrerInformation;
}
/** Render the area left of the iframe */
public function renderSidebar()
{
$idSite = Common::getRequestVar('idSite');
$period = Common::getRequestVar('period');
$date = Common::getRequestVar('date');
$currentUrl = Common::getRequestVar('currentUrl');
$currentUrl = Common::unsanitizeInputValue($currentUrl);
$normalizedCurrentUrl = PageUrl::excludeQueryParametersFromUrl($currentUrl, $idSite);
$normalizedCurrentUrl = Common::unsanitizeInputValue($normalizedCurrentUrl);
// load the appropriate row of the page urls report using the label filter
ArchivingHelper::reloadConfig();
$path = ArchivingHelper::getActionExplodedNames($normalizedCurrentUrl, Action::TYPE_PAGE_URL);
$path = array_map('urlencode', $path);
$label = implode('>', $path);
$request = new Request('method=Actions.getPageUrls' . '&idSite=' . urlencode($idSite) . '&date=' . urlencode($date) . '&period=' . urlencode($period) . '&label=' . urlencode($label) . '&format=original' . '&format_metrics=0');
$dataTable = $request->process();
$formatter = new Metrics\Formatter\Html();
$data = array();
if ($dataTable->getRowsCount() > 0) {
$row = $dataTable->getFirstRow();
$translations = Metrics::getDefaultMetricTranslations();
$showMetrics = array('nb_hits', 'nb_visits', 'nb_users', 'nb_uniq_visitors', 'bounce_rate', 'exit_rate', 'avg_time_on_page');
foreach ($showMetrics as $metric) {
$value = $row->getColumn($metric);
if ($value === false) {
// skip unique visitors for period != day
continue;
}
if ($metric == 'bounce_rate' || $metric == 'exit_rate') {
$value = $formatter->getPrettyPercentFromQuotient($value);
} else {
if ($metric == 'avg_time_on_page') {
$value = $formatter->getPrettyTimeFromSeconds($value, $displayAsSentence = true);
}
}
$data[] = array('name' => $translations[$metric], 'value' => $value);
}
}
// generate page url string
foreach ($path as &$part) {
$part = preg_replace(';^/;', '', urldecode($part));
}
$page = '/' . implode('/', $path);
$page = preg_replace(';/index$;', '/', $page);
if ($page == '/') {
$page = '/index';
}
// render template
$view = new View('@Overlay/renderSidebar');
$view->data = $data;
$view->location = $page;
$view->normalizedUrl = $normalizedCurrentUrl;
$view->label = $label;
$view->idSite = $idSite;
$view->period = $period;
$view->date = $date;
$this->outputCORSHeaders();
return $view->render();
}
protected function getActionsToLookup()
{
$actionUrl = false;
$url = $this->getActionUrl();
if (!empty($url)) {
// normalize urls by stripping protocol and www
$url = Tracker\PageUrl::normalizeUrl($url);
$actionUrl = array($url['url'], $this->getActionType(), $url['prefixId']);
}
return array('idaction_url' => $actionUrl);
}
/**
* Returns the extracted utm details from the url
* @param string $currentUrl
* @param int $idSite
* @param Request $request
* @return array
*/
private function getCampaignInformation($currentUrl, $idSite, Request $request)
{
$cacheKey = $currentUrl . $idSite;
if (isset(self::$cachedReferrer[$cacheKey])) {
return self::$cachedReferrer[$cacheKey];
}
$currentUrl = PageUrl::cleanupUrl($currentUrl);
$this->currentUrlParse = @parse_url($currentUrl);
$utmInformation = $this->extractUtmDetailsFromUrl();
self::$cachedReferrer[$cacheKey] = $utmInformation;
return $utmInformation;
}
/**
* Returns an array containing the following information:
* - referer_type
* - direct -- absence of referrer URL OR referrer URL has the same host
* - site -- based on the referrer URL
* - search_engine -- based on the referrer URL
* - campaign -- based on campaign URL parameter
*
* - referer_name
* - ()
* - piwik.net -- site host name
* - google.fr -- search engine host name
* - adwords-search -- campaign name
*
* - referer_keyword
* - ()
* - ()
* - my keyword
* - my paid keyword
* - ()
* - ()
*
* - referer_url : the same for all the referrer types
*
* @param string $referrerUrl must be URL Encoded
* @param string $currentUrl
* @param int $idSite
* @return array
*/
protected function getReferrerInformation($referrerUrl, $currentUrl, $idSite, Request $request)
{
$cacheKey = $referrerUrl . $currentUrl . $idSite;
if (isset(self::$cachedReferrer[$cacheKey])) {
return self::$cachedReferrer[$cacheKey];
}
$this->idsite = $idSite;
// default values for the referer_* fields
$referrerUrl = Common::unsanitizeInputValue($referrerUrl);
if (!empty($referrerUrl) && !UrlHelper::isLookLikeUrl($referrerUrl)) {
$referrerUrl = '';
}
$currentUrl = PageUrl::cleanupUrl($currentUrl);
$this->referrerUrl = $referrerUrl;
$this->referrerUrlParse = @parse_url($this->referrerUrl);
$this->currentUrlParse = @parse_url($currentUrl);
$this->typeReferrerAnalyzed = Common::REFERRER_TYPE_DIRECT_ENTRY;
$this->nameReferrerAnalyzed = '';
$this->keywordReferrerAnalyzed = '';
$this->referrerHost = '';
if (isset($this->referrerUrlParse['host'])) {
$this->referrerHost = $this->referrerUrlParse['host'];
}
$referrerDetected = $this->detectReferrerCampaign($request);
if (!$referrerDetected) {
if ($this->detectReferrerDirectEntry() || $this->detectReferrerSearchEngine()) {
$referrerDetected = true;
}
}
if (!$referrerDetected && !empty($this->referrerHost)) {
$this->typeReferrerAnalyzed = Common::REFERRER_TYPE_WEBSITE;
$this->nameReferrerAnalyzed = Common::mb_strtolower($this->referrerHost);
$urlsByHost = $this->getCachedUrlsByHostAndIdSite();
$directEntry = new SiteUrls();
$path = $directEntry->getPathMatchingUrl($this->referrerUrlParse, $urlsByHost);
if (!empty($path) && $path !== '/') {
$this->nameReferrerAnalyzed .= rtrim($path, '/');
}
}
$referrerInformation = array('referer_type' => $this->typeReferrerAnalyzed, 'referer_name' => $this->nameReferrerAnalyzed, 'referer_keyword' => $this->keywordReferrerAnalyzed, 'referer_url' => $this->referrerUrl);
self::$cachedReferrer[$cacheKey] = $referrerInformation;
return $referrerInformation;
}
/**
* Get following pages of a url.
* This is done on the logs - not the archives!
*
* Note: if you use this method via the regular API, the number of results will be limited.
* Make sure, you set filter_limit=-1 in the request.
*/
public function getFollowingPages($url, $idSite, $period, $date, $segment = false)
{
$this->authenticate($idSite);
$url = PageUrl::excludeQueryParametersFromUrl($url, $idSite);
// we don't unsanitize $url here. it will be done in the Transitions plugin.
$resultDataTable = new DataTable();
try {
$limitBeforeGrouping = Config::getInstance()->General['overlay_following_pages_limit'];
$transitionsReport = APITransitions::getInstance()->getTransitionsForAction($url, $type = 'url', $idSite, $period, $date, $segment, $limitBeforeGrouping, $part = 'followingActions', $returnNormalizedUrls = true);
} catch (Exception $e) {
return $resultDataTable;
}
$reports = array('followingPages', 'outlinks', 'downloads');
foreach ($reports as $reportName) {
if (!isset($transitionsReport[$reportName])) {
continue;
}
foreach ($transitionsReport[$reportName]->getRows() as $row) {
// don't touch the row at all for performance reasons
$resultDataTable->addRow($row);
}
}
return $resultDataTable;
}
private function getPageLabel(&$pageRecord, $type)
{
if ($type == Action::TYPE_PAGE_TITLE) {
$label = $pageRecord['name'];
if (empty($label)) {
$label = ArchivingHelper::getUnknownActionName(Action::TYPE_PAGE_TITLE);
}
return $label;
}
if ($type == Action::TYPE_OUTLINK || $type == Action::TYPE_DOWNLOAD) {
return PageUrl::reconstructNormalizedUrl($pageRecord['name'], $pageRecord['url_prefix']);
}
return $pageRecord['name'];
}
/**
* Will search in the DataTable for a Label matching the searched string
* and return only the matching row, or an empty datatable
*/
protected function getFilterPageDatatableSearch($callBackParameters, $search, $actionType, $table = false, $searchTree = false)
{
if ($searchTree === false) {
// build the query parts that are searched inside the tree
if ($actionType == Action::TYPE_PAGE_TITLE) {
$searchedString = Common::unsanitizeInputValue($search);
} else {
$idSite = $callBackParameters[1];
try {
$searchedString = PageUrl::excludeQueryParametersFromUrl($search, $idSite);
} catch (Exception $e) {
$searchedString = $search;
}
}
ArchivingHelper::reloadConfig();
$searchTree = ArchivingHelper::getActionExplodedNames($searchedString, $actionType);
}
if ($table === false) {
// fetch the data table
$table = call_user_func_array(array($this, 'getDataTableFromArchive'), $callBackParameters);
if ($table instanceof DataTable\Map) {
// search an array of tables, e.g. when using date=last30
// note that if the root is an array, we filter all children
// if an array occurs inside the nested table, we only look for the first match (see below)
$dataTableMap = $table->getEmptyClone();
foreach ($table->getDataTables() as $label => $subTable) {
$newSubTable = $this->doFilterPageDatatableSearch($callBackParameters, $subTable, $searchTree);
$dataTableMap->addTable($newSubTable, $label);
}
return $dataTableMap;
}
}
return $this->doFilterPageDatatableSearch($callBackParameters, $table, $searchTree);
}
public function detectGoalId($idSite)
{
if (!Common::isGoalPluginEnabled()) {
return false;
}
$goals = $this->getGoalDefinitions($idSite);
if (!isset($goals[$this->idGoal])) {
return false;
}
$goal = $goals[$this->idGoal];
$url = $this->request->getParam('url');
$goal['url'] = PageUrl::excludeQueryParametersFromUrl($url, $idSite);
$this->convertedGoals[] = $goal;
return true;
}
public static function getUrlIfLookValid($url)
{
$url = PageUrl::cleanupString($url);
if (!UrlHelper::isLookLikeUrl($url)) {
Common::printDebug("WARNING: URL looks invalid and is discarded");
return false;
}
return $url;
}
protected function detectSiteSearch($originalUrl)
{
$website = Cache::getCacheWebsiteAttributes($this->request->getIdSite());
if (empty($website['sitesearch'])) {
Common::printDebug("Internal 'Site Search' tracking is not enabled for this site. ");
return false;
}
$actionName = $url = $categoryName = $count = false;
$originalUrl = PageUrl::cleanupUrl($originalUrl);
// Detect Site search from Tracking API parameters rather than URL
$searchKwd = $this->request->getParam('search');
if (!empty($searchKwd)) {
$actionName = $searchKwd;
$isCategoryName = $this->request->getParam('search_cat');
if (!empty($isCategoryName)) {
$categoryName = $isCategoryName;
}
$isCount = $this->request->getParam('search_count');
if ($this->isValidSearchCount($isCount)) {
$count = $isCount;
}
}
if (empty($actionName)) {
$parsedUrl = @parse_url($originalUrl);
// Detect Site Search from URL query parameters
if (!empty($parsedUrl['query']) || !empty($parsedUrl['fragment'])) {
// array($url, $actionName, $categoryName, $count);
$searchInfo = $this->detectSiteSearchFromUrl($website, $parsedUrl);
if (!empty($searchInfo)) {
list($url, $actionName, $categoryName, $count) = $searchInfo;
}
}
}
$actionName = trim($actionName);
$categoryName = trim($categoryName);
if (empty($actionName)) {
Common::printDebug("(this is not a Site Search request)");
return false;
}
Common::printDebug("Detected Site Search keyword '{$actionName}'. ");
if (!empty($categoryName)) {
Common::printDebug("- Detected Site Search Category '{$categoryName}'. ");
}
if ($count !== false) {
Common::printDebug("- Search Results Count was '{$count}'. ");
}
if ($url != $originalUrl) {
Common::printDebug("NOTE: The Page URL was changed / removed, during the Site Search detection, was '{$originalUrl}', now is '{$url}'");
}
return array($actionName, $url, $categoryName, $count);
}
/**
* @param $visitorDetailsArray
* @param $actionsLimit
* @param $timezone
* @return array
*/
public static function enrichVisitorArrayWithActions($visitorDetailsArray, $actionsLimit, $timezone)
{
$idVisit = $visitorDetailsArray['idVisit'];
$model = new Model();
$actionDetails = $model->queryActionsForVisit($idVisit, $actionsLimit);
$formatter = new Formatter();
$maxCustomVariables = CustomVariables::getNumUsableCustomVariables();
foreach ($actionDetails as $actionIdx => &$actionDetail) {
$actionDetail =& $actionDetails[$actionIdx];
$customVariablesPage = array();
for ($i = 1; $i <= $maxCustomVariables; $i++) {
if (!empty($actionDetail['custom_var_k' . $i])) {
$cvarKey = $actionDetail['custom_var_k' . $i];
$cvarKey = static::getCustomVariablePrettyKey($cvarKey);
$customVariablesPage[$i] = array('customVariablePageName' . $i => $cvarKey, 'customVariablePageValue' . $i => $actionDetail['custom_var_v' . $i]);
}
unset($actionDetail['custom_var_k' . $i]);
unset($actionDetail['custom_var_v' . $i]);
}
if (!empty($customVariablesPage)) {
$actionDetail['customVariables'] = $customVariablesPage;
}
if ($actionDetail['type'] == Action::TYPE_CONTENT) {
unset($actionDetails[$actionIdx]);
continue;
} elseif ($actionDetail['type'] == Action::TYPE_EVENT) {
// Handle Event
if (strlen($actionDetail['pageTitle']) > 0) {
$actionDetail['eventName'] = $actionDetail['pageTitle'];
}
unset($actionDetail['pageTitle']);
} else {
if ($actionDetail['type'] == Action::TYPE_SITE_SEARCH) {
// Handle Site Search
$actionDetail['siteSearchKeyword'] = $actionDetail['pageTitle'];
unset($actionDetail['pageTitle']);
}
}
// Event value / Generation time
if ($actionDetail['type'] == Action::TYPE_EVENT) {
if (strlen($actionDetail['custom_float']) > 0) {
$actionDetail['eventValue'] = round($actionDetail['custom_float'], self::EVENT_VALUE_PRECISION);
}
} elseif ($actionDetail['custom_float'] > 0) {
$actionDetail['generationTime'] = $formatter->getPrettyTimeFromSeconds($actionDetail['custom_float'] / 1000, true);
}
unset($actionDetail['custom_float']);
if ($actionDetail['type'] != Action::TYPE_EVENT) {
unset($actionDetail['eventCategory']);
unset($actionDetail['eventAction']);
}
// Reconstruct url from prefix
$url = Tracker\PageUrl::reconstructNormalizedUrl($actionDetail['url'], $actionDetail['url_prefix']);
$url = Common::unsanitizeInputValue($url);
$actionDetail['url'] = $url;
unset($actionDetail['url_prefix']);
}
// If the visitor converted a goal, we shall select all Goals
$goalDetails = $model->queryGoalConversionsForVisit($idVisit, $actionsLimit);
$ecommerceDetails = $model->queryEcommerceConversionsForVisit($idVisit, $actionsLimit);
foreach ($ecommerceDetails as &$ecommerceDetail) {
if ($ecommerceDetail['type'] == Piwik::LABEL_ID_GOAL_IS_ECOMMERCE_CART) {
unset($ecommerceDetail['orderId']);
unset($ecommerceDetail['revenueSubTotal']);
unset($ecommerceDetail['revenueTax']);
unset($ecommerceDetail['revenueShipping']);
unset($ecommerceDetail['revenueDiscount']);
}
// 25.00 => 25
foreach ($ecommerceDetail as $column => $value) {
if (strpos($column, 'revenue') !== false) {
if ($value == round($value)) {
$ecommerceDetail[$column] = round($value);
}
}
}
}
// Enrich ecommerce carts/orders with the list of products
usort($ecommerceDetails, array('static', 'sortByServerTime'));
foreach ($ecommerceDetails as &$ecommerceConversion) {
$idOrder = isset($ecommerceConversion['orderId']) ? $ecommerceConversion['orderId'] : GoalManager::ITEM_IDORDER_ABANDONED_CART;
$itemsDetails = $model->queryEcommerceItemsForOrder($idVisit, $idOrder, $actionsLimit);
foreach ($itemsDetails as &$detail) {
if ($detail['price'] == round($detail['price'])) {
$detail['price'] = round($detail['price']);
}
}
$ecommerceConversion['itemDetails'] = $itemsDetails;
}
// Enrich with time spent per action
foreach ($actionDetails as $actionIdx => &$actionDetail) {
// Set the time spent for this action (which is the timeSpentRef of the next action)
$nextActionFound = isset($actionDetails[$actionIdx + 1]);
if ($nextActionFound) {
$actionDetail['timeSpent'] = $actionDetails[$actionIdx + 1]['timeSpentRef'];
} else {
// Last action of a visit.
// By default, Piwik does not know how long the user stayed on the page
// If enableHeartBeatTimer() is used in piwik.js then we can find the accurate time on page for the last pageview
$timeOfLastActionOrPingInVisitRow = $visitorDetailsArray['lastActionTimestamp'];
$timeOfLastAction = Date::factory($actionDetail['serverTimePretty'])->getTimestamp();
$timeSpentOnPage = $timeOfLastActionOrPingInVisitRow - $timeOfLastAction;
// Safe net, we assume the time is correct when it's more than 10 seconds
if ($timeSpentOnPage > 10) {
$actionDetail['timeSpent'] = $timeSpentOnPage;
}
}
if (isset($actionDetail['timeSpent'])) {
$actionDetail['timeSpentPretty'] = $formatter->getPrettyTimeFromSeconds($actionDetail['timeSpent'], true);
}
unset($actionDetails[$actionIdx]['timeSpentRef']);
// not needed after timeSpent is added
}
$actions = array_merge($actionDetails, $goalDetails, $ecommerceDetails);
usort($actions, array('static', 'sortByServerTime'));
foreach ($actions as &$action) {
unset($action['idlink_va']);
}
$visitorDetailsArray['goalConversions'] = count($goalDetails);
$visitorDetailsArray['actionDetails'] = $actions;
foreach ($visitorDetailsArray['actionDetails'] as &$details) {
switch ($details['type']) {
case 'goal':
$details['icon'] = 'plugins/Morpheus/images/goal.png';
break;
case Piwik::LABEL_ID_GOAL_IS_ECOMMERCE_ORDER:
case Piwik::LABEL_ID_GOAL_IS_ECOMMERCE_CART:
$details['icon'] = 'plugins/Morpheus/images/' . $details['type'] . '.gif';
break;
case Action::TYPE_DOWNLOAD:
$details['type'] = 'download';
$details['icon'] = 'plugins/Morpheus/images/download.png';
break;
case Action::TYPE_OUTLINK:
$details['type'] = 'outlink';
$details['icon'] = 'plugins/Morpheus/images/link.gif';
break;
case Action::TYPE_SITE_SEARCH:
$details['type'] = 'search';
$details['icon'] = 'plugins/Morpheus/images/search_ico.png';
break;
case Action::TYPE_EVENT:
$details['type'] = 'event';
$details['icon'] = 'plugins/Morpheus/images/event.png';
break;
default:
$details['type'] = 'action';
$details['icon'] = null;
break;
}
// Convert datetimes to the site timezone
$dateTimeVisit = Date::factory($details['serverTimePretty'], $timezone);
$details['serverTimePretty'] = $dateTimeVisit->getLocalized(Date::DATETIME_FORMAT_SHORT);
$details['timestamp'] = $dateTimeVisit->getTimestamp();
}
return $visitorDetailsArray;
}
public function detectGoalId($idSite, Request $request)
{
if (!Common::isGoalPluginEnabled()) {
return null;
}
$idGoal = $request->getParam('idgoal');
$goals = $this->getGoalDefinitions($idSite);
if (!isset($goals[$idGoal])) {
return null;
}
$goal = $goals[$idGoal];
$url = $request->getParam('url');
$goal['url'] = PageUrl::excludeQueryParametersFromUrl($url, $idSite);
return $goal;
}
private static function parseNameFromPageUrl($name, $type, $urlPrefix)
{
$urlRegexAfterDomain = '([^/]+)[/]?([^#]*)[#]?(.*)';
if ($urlPrefix === null) {
// match url with protocol (used for outlinks / downloads)
$urlRegex = '@^http[s]?://' . $urlRegexAfterDomain . '$@i';
} else {
// the name is a url that does not contain protocol and www anymore
// we know that normalization has been done on db level because $urlPrefix is set
$urlRegex = '@^' . $urlRegexAfterDomain . '$@i';
}
$matches = array();
preg_match($urlRegex, $name, $matches);
if (!count($matches)) {
return $name;
}
$urlHost = $matches[1];
$urlPath = $matches[2];
$urlFragment = $matches[3];
if (in_array($type, array(Action::TYPE_DOWNLOAD, Action::TYPE_OUTLINK))) {
$path = '/' . trim($urlPath);
if (!empty($urlFragment)) {
$path .= '#' . $urlFragment;
}
return array(trim($urlHost), $path);
}
$name = $urlPath;
if ($name === '' || substr($name, -1) == '/') {
$name .= self::$defaultActionName;
}
$urlFragment = PageUrl::processUrlFragment($urlFragment);
if (!empty($urlFragment)) {
$name .= '#' . $urlFragment;
}
return $name;
}
protected function aggregateFromActions($valueField)
{
$resultSet = $this->queryCustomDimensionActions($this->dataArray, $valueField);
while ($row = $resultSet->fetch()) {
$label = $row[$valueField];
$label = $this->cleanCustomDimensionValue($label);
$this->dataArray->sumMetricsActions($label, $row);
// make sure we always work with normalized URL no matter how the individual action stores it
$normalized = Tracker\PageUrl::normalizeUrl($row['url']);
$row['url'] = $normalized['url'];
$subLabel = $row['url'];
if (empty($subLabel)) {
continue;
}
$this->dataArray->sumMetricsActionCustomDimensionsPivot($label, $subLabel, $row);
}
}
protected function getUrlAndType()
{
$url = $this->getActionUrl();
if (!empty($url)) {
// normalize urls by stripping protocol and www
$url = PageUrl::normalizeUrl($url);
return array($url['url'], self::TYPE_PAGE_URL, $url['prefixId']);
}
return false;
}
/**
* @param $visitorDetailsArray
* @param $actionsLimit
* @param $timezone
* @return array
*/
public static function enrichVisitorArrayWithActions($visitorDetailsArray, $actionsLimit, $timezone)
{
$idVisit = $visitorDetailsArray['idVisit'];
$maxCustomVariables = CustomVariables::getMaxCustomVariables();
$sqlCustomVariables = '';
for ($i = 1; $i <= $maxCustomVariables; $i++) {
$sqlCustomVariables .= ', custom_var_k' . $i . ', custom_var_v' . $i;
}
// The second join is a LEFT join to allow returning records that don't have a matching page title
// eg. Downloads, Outlinks. For these, idaction_name is set to 0
$sql = "\n\t\t\t\tSELECT\n\t\t\t\t\tCOALESCE(log_action_event_category.type, log_action.type, log_action_title.type) AS type,\n\t\t\t\t\tlog_action.name AS url,\n\t\t\t\t\tlog_action.url_prefix,\n\t\t\t\t\tlog_action_title.name AS pageTitle,\n\t\t\t\t\tlog_action.idaction AS pageIdAction,\n\t\t\t\t\tlog_link_visit_action.server_time as serverTimePretty,\n\t\t\t\t\tlog_link_visit_action.time_spent_ref_action as timeSpentRef,\n\t\t\t\t\tlog_link_visit_action.idlink_va AS pageId,\n\t\t\t\t\tlog_link_visit_action.custom_float\n\t\t\t\t\t" . $sqlCustomVariables . ",\n\t\t\t\t\tlog_action_event_category.name AS eventCategory,\n\t\t\t\t\tlog_action_event_action.name as eventAction\n\t\t\t\tFROM " . Common::prefixTable('log_link_visit_action') . " AS log_link_visit_action\n\t\t\t\t\tLEFT JOIN " . Common::prefixTable('log_action') . " AS log_action\n\t\t\t\t\tON log_link_visit_action.idaction_url = log_action.idaction\n\t\t\t\t\tLEFT JOIN " . Common::prefixTable('log_action') . " AS log_action_title\n\t\t\t\t\tON log_link_visit_action.idaction_name = log_action_title.idaction\n\t\t\t\t\tLEFT JOIN " . Common::prefixTable('log_action') . " AS log_action_event_category\n\t\t\t\t\tON log_link_visit_action.idaction_event_category = log_action_event_category.idaction\n\t\t\t\t\tLEFT JOIN " . Common::prefixTable('log_action') . " AS log_action_event_action\n\t\t\t\t\tON log_link_visit_action.idaction_event_action = log_action_event_action.idaction\n\t\t\t\tWHERE log_link_visit_action.idvisit = ?\n\t\t\t\tORDER BY server_time ASC\n\t\t\t\tLIMIT 0, {$actionsLimit}\n\t\t\t\t ";
$actionDetails = Db::fetchAll($sql, array($idVisit));
foreach ($actionDetails as $actionIdx => &$actionDetail) {
$actionDetail =& $actionDetails[$actionIdx];
$customVariablesPage = array();
for ($i = 1; $i <= $maxCustomVariables; $i++) {
if (!empty($actionDetail['custom_var_k' . $i])) {
$cvarKey = $actionDetail['custom_var_k' . $i];
$cvarKey = static::getCustomVariablePrettyKey($cvarKey);
$customVariablesPage[$i] = array('customVariablePageName' . $i => $cvarKey, 'customVariablePageValue' . $i => $actionDetail['custom_var_v' . $i]);
}
unset($actionDetail['custom_var_k' . $i]);
unset($actionDetail['custom_var_v' . $i]);
}
if (!empty($customVariablesPage)) {
$actionDetail['customVariables'] = $customVariablesPage;
}
if ($actionDetail['type'] == Action::TYPE_CONTENT) {
unset($actionDetails[$actionIdx]);
continue;
} elseif ($actionDetail['type'] == Action::TYPE_EVENT_CATEGORY) {
// Handle Event
if (strlen($actionDetail['pageTitle']) > 0) {
$actionDetail['eventName'] = $actionDetail['pageTitle'];
}
unset($actionDetail['pageTitle']);
} else {
if ($actionDetail['type'] == Action::TYPE_SITE_SEARCH) {
// Handle Site Search
$actionDetail['siteSearchKeyword'] = $actionDetail['pageTitle'];
unset($actionDetail['pageTitle']);
}
}
// Event value / Generation time
if ($actionDetail['type'] == Action::TYPE_EVENT_CATEGORY) {
if (strlen($actionDetail['custom_float']) > 0) {
$actionDetail['eventValue'] = round($actionDetail['custom_float'], self::EVENT_VALUE_PRECISION);
}
} elseif ($actionDetail['custom_float'] > 0) {
$actionDetail['generationTime'] = \Piwik\MetricsFormatter::getPrettyTimeFromSeconds($actionDetail['custom_float'] / 1000);
}
unset($actionDetail['custom_float']);
if ($actionDetail['type'] != Action::TYPE_EVENT_CATEGORY) {
unset($actionDetail['eventCategory']);
unset($actionDetail['eventAction']);
}
// Reconstruct url from prefix
$actionDetail['url'] = Tracker\PageUrl::reconstructNormalizedUrl($actionDetail['url'], $actionDetail['url_prefix']);
unset($actionDetail['url_prefix']);
// Set the time spent for this action (which is the timeSpentRef of the next action)
if (isset($actionDetails[$actionIdx + 1])) {
$actionDetail['timeSpent'] = $actionDetails[$actionIdx + 1]['timeSpentRef'];
$actionDetail['timeSpentPretty'] = \Piwik\MetricsFormatter::getPrettyTimeFromSeconds($actionDetail['timeSpent']);
}
unset($actionDetails[$actionIdx]['timeSpentRef']);
// not needed after timeSpent is added
}
// If the visitor converted a goal, we shall select all Goals
$sql = "\n\t\t\t\tSELECT\n\t\t\t\t\t\t'goal' as type,\n\t\t\t\t\t\tgoal.name as goalName,\n\t\t\t\t\t\tgoal.idgoal as goalId,\n\t\t\t\t\t\tgoal.revenue as revenue,\n\t\t\t\t\t\tlog_conversion.idlink_va as goalPageId,\n\t\t\t\t\t\tlog_conversion.server_time as serverTimePretty,\n\t\t\t\t\t\tlog_conversion.url as url\n\t\t\t\tFROM " . Common::prefixTable('log_conversion') . " AS log_conversion\n\t\t\t\tLEFT JOIN " . Common::prefixTable('goal') . " AS goal\n\t\t\t\t\tON (goal.idsite = log_conversion.idsite\n\t\t\t\t\t\tAND\n\t\t\t\t\t\tgoal.idgoal = log_conversion.idgoal)\n\t\t\t\t\tAND goal.deleted = 0\n\t\t\t\tWHERE log_conversion.idvisit = ?\n\t\t\t\t\tAND log_conversion.idgoal > 0\n ORDER BY server_time ASC\n\t\t\t\tLIMIT 0, {$actionsLimit}\n\t\t\t";
$goalDetails = Db::fetchAll($sql, array($idVisit));
$sql = "SELECT\n\t\t\t\t\t\tcase idgoal when " . GoalManager::IDGOAL_CART . " then '" . Piwik::LABEL_ID_GOAL_IS_ECOMMERCE_CART . "' else '" . Piwik::LABEL_ID_GOAL_IS_ECOMMERCE_ORDER . "' end as type,\n\t\t\t\t\t\tidorder as orderId,\n\t\t\t\t\t\t" . LogAggregator::getSqlRevenue('revenue') . " as revenue,\n\t\t\t\t\t\t" . LogAggregator::getSqlRevenue('revenue_subtotal') . " as revenueSubTotal,\n\t\t\t\t\t\t" . LogAggregator::getSqlRevenue('revenue_tax') . " as revenueTax,\n\t\t\t\t\t\t" . LogAggregator::getSqlRevenue('revenue_shipping') . " as revenueShipping,\n\t\t\t\t\t\t" . LogAggregator::getSqlRevenue('revenue_discount') . " as revenueDiscount,\n\t\t\t\t\t\titems as items,\n\n\t\t\t\t\t\tlog_conversion.server_time as serverTimePretty\n\t\t\t\t\tFROM " . Common::prefixTable('log_conversion') . " AS log_conversion\n\t\t\t\t\tWHERE idvisit = ?\n\t\t\t\t\t\tAND idgoal <= " . GoalManager::IDGOAL_ORDER . "\n\t\t\t\t\tORDER BY server_time ASC\n\t\t\t\t\tLIMIT 0, {$actionsLimit}";
$ecommerceDetails = Db::fetchAll($sql, array($idVisit));
foreach ($ecommerceDetails as &$ecommerceDetail) {
if ($ecommerceDetail['type'] == Piwik::LABEL_ID_GOAL_IS_ECOMMERCE_CART) {
unset($ecommerceDetail['orderId']);
unset($ecommerceDetail['revenueSubTotal']);
unset($ecommerceDetail['revenueTax']);
unset($ecommerceDetail['revenueShipping']);
unset($ecommerceDetail['revenueDiscount']);
}
// 25.00 => 25
foreach ($ecommerceDetail as $column => $value) {
if (strpos($column, 'revenue') !== false) {
if ($value == round($value)) {
$ecommerceDetail[$column] = round($value);
}
}
}
}
// Enrich ecommerce carts/orders with the list of products
usort($ecommerceDetails, array('static', 'sortByServerTime'));
foreach ($ecommerceDetails as &$ecommerceConversion) {
$sql = "SELECT\n\t\t\t\t\t\t\tlog_action_sku.name as itemSKU,\n\t\t\t\t\t\t\tlog_action_name.name as itemName,\n\t\t\t\t\t\t\tlog_action_category.name as itemCategory,\n\t\t\t\t\t\t\t" . LogAggregator::getSqlRevenue('price') . " as price,\n\t\t\t\t\t\t\tquantity as quantity\n\t\t\t\t\t\tFROM " . Common::prefixTable('log_conversion_item') . "\n\t\t\t\t\t\t\tINNER JOIN " . Common::prefixTable('log_action') . " AS log_action_sku\n\t\t\t\t\t\t\tON idaction_sku = log_action_sku.idaction\n\t\t\t\t\t\t\tLEFT JOIN " . Common::prefixTable('log_action') . " AS log_action_name\n\t\t\t\t\t\t\tON idaction_name = log_action_name.idaction\n\t\t\t\t\t\t\tLEFT JOIN " . Common::prefixTable('log_action') . " AS log_action_category\n\t\t\t\t\t\t\tON idaction_category = log_action_category.idaction\n\t\t\t\t\t\tWHERE idvisit = ?\n\t\t\t\t\t\t\tAND idorder = ?\n\t\t\t\t\t\t\tAND deleted = 0\n\t\t\t\t\t\tLIMIT 0, {$actionsLimit}\n\t\t\t\t";
$bind = array($idVisit, isset($ecommerceConversion['orderId']) ? $ecommerceConversion['orderId'] : GoalManager::ITEM_IDORDER_ABANDONED_CART);
$itemsDetails = Db::fetchAll($sql, $bind);
foreach ($itemsDetails as &$detail) {
if ($detail['price'] == round($detail['price'])) {
$detail['price'] = round($detail['price']);
}
}
$ecommerceConversion['itemDetails'] = $itemsDetails;
}
$actions = array_merge($actionDetails, $goalDetails, $ecommerceDetails);
usort($actions, array('static', 'sortByServerTime'));
$visitorDetailsArray['actionDetails'] = $actions;
foreach ($visitorDetailsArray['actionDetails'] as &$details) {
switch ($details['type']) {
case 'goal':
$details['icon'] = 'plugins/Morpheus/images/goal.png';
break;
case Piwik::LABEL_ID_GOAL_IS_ECOMMERCE_ORDER:
case Piwik::LABEL_ID_GOAL_IS_ECOMMERCE_CART:
$details['icon'] = 'plugins/Morpheus/images/' . $details['type'] . '.gif';
break;
case Action::TYPE_DOWNLOAD:
$details['type'] = 'download';
$details['icon'] = 'plugins/Morpheus/images/download.png';
break;
case Action::TYPE_OUTLINK:
$details['type'] = 'outlink';
$details['icon'] = 'plugins/Morpheus/images/link.gif';
break;
case Action::TYPE_SITE_SEARCH:
$details['type'] = 'search';
$details['icon'] = 'plugins/Morpheus/images/search_ico.png';
break;
case Action::TYPE_EVENT_CATEGORY:
$details['type'] = 'event';
$details['icon'] = 'plugins/Morpheus/images/event.png';
break;
default:
$details['type'] = 'action';
$details['icon'] = null;
break;
}
// Convert datetimes to the site timezone
$dateTimeVisit = Date::factory($details['serverTimePretty'], $timezone);
$details['serverTimePretty'] = $dateTimeVisit->getLocalized(Piwik::translate('CoreHome_ShortDateFormat') . ' %time%');
}
$visitorDetailsArray['goalConversions'] = count($goalDetails);
return $visitorDetailsArray;
}
/**
* Testing with some website specific and some global excluded query parameters
* @group Core
* @dataProvider getTestUrls
*/
public function testExcludeQueryParametersSiteAndGlobalExcluded($url, $filteredUrl)
{
// testing also that query parameters are case insensitive
$excludedQueryParameters = 'P2,var[value][date]';
$excludedGlobalParameters = 'blabla, P4';
$this->setUpRootAccess();
$idSite = API::getInstance()->addSite("site1", array('http://example.org'), $ecommerce = 0, $siteSearch = 1, $searchKeywordParameters = null, $searchCategoryParameters = null, $excludedIps = '', $excludedQueryParameters, $timezone = null, $currency = null, $group = null, $startDate = null, $excludedUserAgents = null, $keepURLFragments = 1);
API::getInstance()->setGlobalExcludedQueryParameters($excludedGlobalParameters);
$this->assertEquals($filteredUrl[1], PageUrl::excludeQueryParametersFromUrl($url, $idSite));
}
