/**
* Convert segment expression to an action ID or an SQL expression.
*
* This method is used as a sqlFilter-callback for the segments of this plugin.
* Usually, these callbacks only return a value that should be compared to the
* column in the database. In this case, that doesn't work since multiple IDs
* can match an expression (e.g. "pageUrl=@foo").
*/
function getIdActionFromSegment($string, $sqlField, $matchType = '==')
{
// Field is visit_*_idaction_url or visit_*_idaction_name
$actionType = strpos($sqlField, '_name') === false ? Piwik_Tracker_Action::TYPE_ACTION_URL : Piwik_Tracker_Action::TYPE_ACTION_NAME;
// exact matches work by returning the id directly
if ($matchType == Piwik_SegmentExpression::MATCH_EQUAL || $matchType == Piwik_SegmentExpression::MATCH_NOT_EQUAL) {
$sql = Piwik_Tracker_Action::getSqlSelectActionId();
$bind = array($string, $string, $actionType);
$idAction = Piwik_FetchOne($sql, $bind);
// if the action is not found, we hack -100 to ensure it tries to match against an integer
// otherwise binding idaction_name to "false" returns some rows for some reasons (in case &segment=pageTitle==Větrnásssssss)
if (empty($idAction)) {
$idAction = -100;
}
return $idAction;
}
// now, we handle the cases =@ (contains) and !@ (does not contain)
// build the expression based on the match type
$sql = 'SELECT idaction FROM ' . Piwik_Common::prefixTable('log_action') . ' WHERE ';
switch ($matchType) {
case '=@':
// use concat to make sure, no %s occurs because some plugins use %s in their sql
$sql .= '( name LIKE CONCAT("%", ?, "%") AND type = ' . $actionType . ' )';
break;
case '!@':
$sql .= '( name NOT LIKE CONCAT("%", ?, "%") AND type = ' . $actionType . ' )';
break;
default:
throw new Exception("This match type is not available for action-segments.");
break;
}
return array('SQL' => $sql, 'bind' => $string);
}
function recordGoals($visitorInformation)
{
$location_country = isset($visitorInformation['location_country']) ? $visitorInformation['location_country'] : Piwik_Common::getCountry(Piwik_Common::getBrowserLanguage(), $enableLanguageToCountryGuess = Piwik_Tracker_Config::getInstance()->Tracker['enable_language_to_country_guess']);
$location_continent = isset($visitorInformation['location_continent']) ? $visitorInformation['location_continent'] : Piwik_Common::getContinent($location_country);
$goal = array('idvisit' => $visitorInformation['idvisit'], 'idsite' => $visitorInformation['idsite'], 'visitor_idcookie' => $visitorInformation['visitor_idcookie'], 'server_time' => Piwik_Tracker::getDatetimeFromTimestamp($visitorInformation['visit_last_action_time']), 'visit_server_date' => $visitorInformation['visit_server_date'], 'idaction' => $this->action->getIdAction(), 'idlink_va' => $this->action->getIdLinkVisitAction(), 'location_country' => $location_country, 'location_continent' => $location_continent, 'url' => $this->action->getActionUrl(), 'visitor_returning' => $this->cookie->get(Piwik_Tracker::COOKIE_INDEX_VISITOR_RETURNING));
$referer_idvisit = $this->cookie->get(Piwik_Tracker::COOKIE_INDEX_REFERER_ID_VISIT);
if ($referer_idvisit !== false) {
$goal += array('referer_idvisit' => $referer_idvisit, 'referer_visit_server_date' => date("Y-m-d", $this->cookie->get(Piwik_Tracker::COOKIE_INDEX_REFERER_TIMESTAMP)), 'referer_type' => htmlspecialchars_decode($this->cookie->get(Piwik_Tracker::COOKIE_INDEX_REFERER_TYPE)), 'referer_name' => htmlspecialchars_decode($this->cookie->get(Piwik_Tracker::COOKIE_INDEX_REFERER_NAME)), 'referer_keyword' => htmlspecialchars_decode($this->cookie->get(Piwik_Tracker::COOKIE_INDEX_REFERER_KEYWORD)));
}
foreach ($this->matchedGoals as $matchedGoal) {
printDebug("- Goal " . $matchedGoal['idgoal'] . " matched. Recording...");
$newGoal = $goal;
$newGoal['idgoal'] = $matchedGoal['idgoal'];
$newGoal['revenue'] = $matchedGoal['revenue'];
printDebug($newGoal);
$fields = implode(", ", array_keys($newGoal));
$bindFields = substr(str_repeat("?,", count($newGoal)), 0, -1);
try {
Piwik_Tracker::getDatabase()->query("INSERT INTO " . Piwik_Common::prefixTable('log_conversion') . "\t({$fields}) \n\t\t\t\t\tVALUES ({$bindFields}) ", array_values($newGoal));
} catch (Exception $e) {
if (strpos($e->getMessage(), '1062') !== false) {
// integrity violation when same visit converts to the same goal twice
printDebug("--> Goal already recorded for this (idvisit, idgoal)");
} else {
throw $e;
}
}
//$idlog_goal = Piwik_Tracker::getDatabase()->lastInsertId();
}
}
function getIdActionFromString($string, $sqlField)
{
// Field is visit_*_idaction_url or visit_*_idaction_name
$actionType = strpos($sqlField, '_name') === false ? Piwik_Tracker_Action::TYPE_ACTION_URL : Piwik_Tracker_Action::TYPE_ACTION_NAME;
$sql = Piwik_Tracker_Action::getSqlSelectActionId();
$bind = array($string, $string, $actionType);
$idAction = Zend_Registry::get('db')->fetchOne($sql, $bind);
return $idAction;
}
/**
* Testing with some website specific and some global excluded query parameters
* @group Core
* @group Tracker
* @group Tracker_Action
* @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 = Piwik_SitesManager_API::getInstance()->addSite("site1", array('http://example.org'), $ecommerce = 0, $excludedIps = '', $excludedQueryParameters);
Piwik_SitesManager_API::getInstance()->setGlobalExcludedQueryParameters($excludedGlobalParameters);
$this->assertEquals($filteredUrl[1], Piwik_Tracker_Action::excludeQueryParametersFromUrl($url, $idSite));
}
/** Render the area left of the iframe */
public function renderSidebar()
{
$idSite = Piwik_Common::getRequestVar('idSite');
$period = Piwik_Common::getRequestVar('period');
$date = Piwik_Common::getRequestVar('date');
$currentUrl = Piwik_Common::getRequestVar('currentUrl');
$currentUrl = Piwik_Common::unsanitizeInputValue($currentUrl);
$normalizedCurrentUrl = Piwik_Tracker_Action::excludeQueryParametersFromUrl($currentUrl, $idSite);
$normalizedCurrentUrl = Piwik_Common::unsanitizeInputValue($normalizedCurrentUrl);
// load the appropriate row of the page urls report using the label filter
Piwik_Actions_ArchivingHelper::reloadConfig();
$path = Piwik_Actions_ArchivingHelper::getActionExplodedNames($normalizedCurrentUrl, Piwik_Tracker_Action::TYPE_ACTION_URL);
$path = array_map('urlencode', $path);
$label = implode('>', $path);
$request = new Piwik_API_Request('method=Actions.getPageUrls' . '&idSite=' . urlencode($idSite) . '&date=' . urlencode($date) . '&period=' . urlencode($period) . '&label=' . urlencode($label) . '&format=original');
$dataTable = $request->process();
$data = array();
if ($dataTable->getRowsCount() > 0) {
$row = $dataTable->getFirstRow();
$translations = Piwik_API_API::getDefaultMetricTranslations();
$showMetrics = array('nb_hits', 'nb_visits', '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 == 'avg_time_on_page') {
$value = Piwik::getPrettyTimeFromSeconds($value);
}
$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 = Piwik_View::factory('sidebar');
$view->data = $data;
$view->location = $page;
$view->normalizedUrl = $normalizedCurrentUrl;
$view->label = $label;
$view->idSite = $idSite;
$view->period = $period;
$view->date = $date;
echo $view->render();
}
function test_excludeQueryParameters_siteAndGlobalExcluded()
{
// testing also that query parameters are case insensitive
$excludedQueryParameters = 'P2';
$excludedGlobalParameters = 'blabla, P4';
$expectedUrls = array('http:////wrongurl', 'http://*****:*****@hostname:80/path#anchor', 'http://a.com/index?p1=v1', 'http://a.com/index?p1=v1', 'http://a.com/index?p1=v1&p3=v3', 'http://a.com/index?p1=v1&p3=v3', 'http://a.com/index?p1=v1&p3=v3', 'http://a.com/index?p1&p3=v3', 'http://a.com/index?p1=v1&p3=v3');
$this->setUpRootAccess();
$idsite = Piwik_SitesManager_API::getInstance()->addSite("site1", array('http://example.org'), $ecommerce = 0, $excludedIps = '', $excludedQueryParameters);
Piwik_SitesManager_API::getInstance()->setGlobalExcludedQueryParameters($excludedGlobalParameters);
$urls = $this->getTestUrls();
$filteredUrls = array();
foreach ($urls as $url) {
$filteredUrls[] = Piwik_Tracker_Action::excludeQueryParametersFromUrl($url, $idsite);
}
$this->assertEqual($expectedUrls, $filteredUrls);
}
/**
* 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 = Piwik_Tracker_Action::excludeQueryParametersFromUrl($url, $idSite);
// we don't unsanitize $url here. it will be done in the Transitions plugin.
$resultDataTable = new Piwik_DataTable();
try {
$limitBeforeGrouping = Piwik_Config::getInstance()->General['overlay_following_pages_limit'];
$transitionsReport = Piwik_Transitions_API::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;
}
/**
* Get information about the following actions (following pages, outlinks, downloads)
*
* @param $idaction
* @param Piwik_ArchiveProcessing_Day $archiveProcessing
* @return array(followingPages:Piwik_DataTable, outlinks:Piwik_DataTable, downloads:Piwik_DataTable)
*/
public function queryFollowingActions($idaction, Piwik_ArchiveProcessing_Day $archiveProcessing, $limitBeforeGrouping = false)
{
static $types = array(Piwik_Tracker_Action::TYPE_ACTION_URL => 'followingPages', Piwik_Tracker_Action::TYPE_OUTLINK => 'outlinks', Piwik_Tracker_Action::TYPE_DOWNLOAD => 'downloads');
$dimension = 'idaction_url';
$rankingQuery = new Piwik_RankingQuery($limitBeforeGrouping ? $limitBeforeGrouping : $this->limitBeforeGrouping);
$rankingQuery->addLabelColumn(array('name', 'url_prefix'));
$rankingQuery->partitionResultIntoMultipleGroups('type', array_keys($types));
$addSelect = 'log_action.name, log_action.url_prefix, log_action.type';
$where = '
log_link_visit_action.idaction_url_ref = ' . intval($idaction) . ' AND
log_link_visit_action.idaction_url != ' . intval($idaction);
$orderBy = '`' . Piwik_Archive::INDEX_NB_ACTIONS . '` DESC';
$metrics = array(Piwik_Archive::INDEX_NB_ACTIONS);
$data = $archiveProcessing->queryActionsByDimension(array($dimension), $where, $metrics, $orderBy, $rankingQuery, $dimension, $addSelect);
$dataTables = array();
foreach ($types as $type => $recordName) {
$dataTable = new Piwik_DataTable();
if (isset($data[$type])) {
foreach ($data[$type] as &$record) {
$dataTable->addRow(new Piwik_DataTable_Row(array(Piwik_DataTable_Row::COLUMNS => array('label' => $type == Piwik_Tracker_Action::TYPE_ACTION_URL ? Piwik_Tracker_Action::reconstructNormalizedUrl($record['name'], $record['url_prefix']) : $record['name'], Piwik_Archive::INDEX_NB_ACTIONS => intval($record[Piwik_Archive::INDEX_NB_ACTIONS])))));
}
}
$dataTables[$recordName] = $dataTable;
}
return $dataTables;
}
/**
* 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, $searchCurrentLevel = 0 )
{
if($table === false)
{
$table = call_user_func_array(array('Piwik_Archive', 'getDataTableFromArchive'), $callBackParameters);
}
if($searchTree === false)
{
if($actionType == Piwik_Tracker_Action::TYPE_ACTION_NAME)
{
$searchedString = Piwik_Common::unsanitizeInputValue($search);
}
else
{
$searchedString = Piwik_Tracker_Action::excludeQueryParametersFromUrl($search, $idSite = $callBackParameters[1]);
}
$searchTree = Piwik_Actions::getActionExplodedNames($searchedString, $actionType);
}
if(!($table instanceof Piwik_DataTable))
{
throw new Exception("For this API function, date=lastN or date=previousM is not supported");
}
$rows = $table->getRows();
$labelSearch = $searchTree[$searchCurrentLevel];
$isEndSearch = ((count($searchTree)-1) == $searchCurrentLevel);
foreach($rows as $key => $row)
{
$found = false;
// Found a match at this level
$label = $row->getColumn('label');
if($label === $labelSearch)
{
// Is this the end of the search tree? then we found the requested row
if($isEndSearch)
{
// var_dump($label); var_dump($labelSearch); exit;
$table = new Piwik_DataTable();
$table->addRow($row);
return $table;
}
// If we still need to search deeper, call search
$idSubTable = $row->getIdSubDataTable();
// Update the idSubtable in the callback parameter list, to fetch this subtable from the archive
$callBackParameters[6] = $idSubTable;
$subTable = call_user_func_array(array('Piwik_Archive', 'getDataTableFromArchive'), $callBackParameters);
$found = $this->getFilterPageDatatableSearch($callBackParameters, $search, $actionType, $subTable, $searchTree, $searchCurrentLevel+1);
if($found)
{
return $found;
}
}
if(!$found)
{
$table->deleteRow($key);
}
}
// Case the DataTable was searched but nothing was found, @see getFilterPageDatatableSearch()
if($searchCurrentLevel == 0)
{
return new Piwik_DataTable;
}
return false;
}
function detectGoalId($idSite, $idGoal, $request)
{
if (!$this->isGoalPluginEnabled()) {
return false;
}
$goals = $this->getGoalDefinitions($idSite);
if (!isset($goals[$idGoal])) {
return false;
}
$goal = $goals[$idGoal];
$url = Piwik_Common::getRequestVar('url', '', 'string', $request);
$goal['url'] = Piwik_Tracker_Action::excludeQueryParametersFromUrl($url, $idSite);
$goal['revenue'] = Piwik_Common::getRequestVar('revenue', $goal['revenue'], 'float', $request);
$this->convertedGoals[] = $goal;
return true;
}
private function getPageLabel(&$pageRecord, $isTitle)
{
if ($isTitle) {
$label = $pageRecord['name'];
if (empty($label)) {
$label = Piwik_Actions_ArchivingHelper::getUnknownActionName(Piwik_Tracker_Action::TYPE_ACTION_NAME);
}
return $label;
} else {
if ($this->returnNormalizedUrls) {
return $pageRecord['name'];
} else {
return Piwik_Tracker_Action::reconstructNormalizedUrl($pageRecord['name'], $pageRecord['url_prefix']);
}
}
}
/**
* Returns an array containing the following information:
* - referer_type
* - direct -- absence of referer URL OR referer URL has the same host
* - site -- based on the referer URL
* - search_engine -- based on the referer 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 referer types
*
* @param $refererUrl must be URL Encoded
* @param $currentUrl
* @param $idSite
* @return array
*/
public function getRefererInformation($refererUrl, $currentUrl, $idSite)
{
$this->idsite = $idSite;
// default values for the referer_* fields
$refererUrl = Piwik_Common::unsanitizeInputValue($refererUrl);
if (!empty($refererUrl) && !Piwik_Common::isLookLikeUrl($refererUrl)) {
$refererUrl = '';
}
$currentUrl = Piwik_Tracker_Action::cleanupUrl($currentUrl);
$this->refererUrl = $refererUrl;
$this->refererUrlParse = @parse_url($this->refererUrl);
$this->currentUrlParse = @parse_url($currentUrl);
$this->typeRefererAnalyzed = Piwik_Common::REFERER_TYPE_DIRECT_ENTRY;
$this->nameRefererAnalyzed = '';
$this->keywordRefererAnalyzed = '';
$this->refererHost = '';
if (isset($this->refererUrlParse['host'])) {
$this->refererHost = $this->refererUrlParse['host'];
}
$refererDetected = false;
if (!empty($this->currentUrlParse['host']) && $this->detectRefererCampaign()) {
$refererDetected = true;
}
if (!$refererDetected) {
if ($this->detectRefererDirectEntry() || $this->detectRefererSearchEngine()) {
$refererDetected = true;
}
}
if (!empty($this->refererHost) && !$refererDetected) {
$this->typeRefererAnalyzed = Piwik_Common::REFERER_TYPE_WEBSITE;
$this->nameRefererAnalyzed = mb_strtolower($this->refererHost, 'UTF-8');
}
$refererInformation = array('referer_type' => $this->typeRefererAnalyzed, 'referer_name' => $this->nameRefererAnalyzed, 'referer_keyword' => $this->keywordRefererAnalyzed, 'referer_url' => $this->refererUrl);
return $refererInformation;
}
/**
* 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 == Piwik_Tracker_Action::TYPE_ACTION_NAME) {
$searchedString = Piwik_Common::unsanitizeInputValue($search);
} else {
$idSite = $callBackParameters[1];
try {
$searchedString = Piwik_Tracker_Action::excludeQueryParametersFromUrl($search, $idSite);
} catch (Exception $e) {
$searchedString = $search;
}
}
$searchTree = Piwik_Actions::getActionExplodedNames($searchedString, $actionType);
}
if ($table === false) {
// fetch the data table
$table = call_user_func_array(array('Piwik_Archive', 'getDataTableFromArchive'), $callBackParameters);
if ($table instanceof Piwik_DataTable_Array) {
// 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)
$newTableArray = new Piwik_DataTable_Array();
$newTableArray->metadata = $table->metadata;
$newTableArray->setKeyName($table->getKeyName());
foreach ($table->getArray() as $label => $subTable) {
$subTable = $this->doFilterPageDatatableSearch($callBackParameters, $subTable, $searchTree);
$newTableArray->addTable($subTable, $label);
}
return $newTableArray;
}
}
return $this->doFilterPageDatatableSearch($callBackParameters, $table, $searchTree);
}
protected function handleAction($action)
{
$action->setIdSite($this->idsite);
$action->setRequest($this->request);
$action->setTimestamp($this->getCurrentTimestamp());
$action->init();
if($this->detectActionIsOutlinkOnAliasHost($action))
{
printDebug("Info: The outlink URL host is one of the known host for this website. ");
}
if(isset($GLOBALS['PIWIK_TRACKER_DEBUG']) && $GLOBALS['PIWIK_TRACKER_DEBUG'])
{
$type = Piwik_Tracker_Action::getActionTypeName($action->getActionType());
printDebug("Action is a $type,
Action name = ". $action->getActionName() . ",
Action URL = ". $action->getActionUrl() );
}
}
/**
* Explodes action name into an array of elements.
*
* NOTE: before calling this function make sure Piwik_Actions_ArchivingHelper::reloadConfig(); is called
*
* for downloads:
* we explode link http://piwik.org/some/path/piwik.zip into an array( 'piwik.org', '/some/path/piwik.zip' );
*
* for outlinks:
* we explode link http://dev.piwik.org/some/path into an array( 'dev.piwik.org', '/some/path' );
*
* for action urls:
* we explode link http://piwik.org/some/path into an array( 'some', 'path' );
*
* for action names:
* we explode name 'Piwik / Category 1 / Category 2' into an array('Piwik', 'Category 1', 'Category 2');
*
* @param string action name
* @param int action type
* @param int url prefix (only used for TYPE_ACTION_URL)
* @return array of exploded elements from $name
*/
public static function getActionExplodedNames($name, $type, $urlPrefix = null)
{
// Site Search does not split Search keywords
if ($type == Piwik_Tracker_Action::TYPE_SITE_SEARCH) {
return array($name);
}
$matches = array();
$isUrl = false;
$name = str_replace("\n", "", $name);
$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';
}
preg_match($urlRegex, $name, $matches);
if (count($matches)) {
$isUrl = true;
$urlHost = $matches[1];
$urlPath = $matches[2];
$urlFragment = $matches[3];
}
if ($type == Piwik_Tracker_Action::TYPE_DOWNLOAD || $type == Piwik_Tracker_Action::TYPE_OUTLINK) {
if ($isUrl) {
return array(trim($urlHost), '/' . trim($urlPath));
}
}
if ($isUrl) {
$name = $urlPath;
if ($name === '' || substr($name, -1) == '/') {
$name .= self::$defaultActionName;
}
}
if ($type == Piwik_Tracker_Action::TYPE_ACTION_NAME) {
$categoryDelimiter = self::$actionTitleCategoryDelimiter;
} else {
$categoryDelimiter = self::$actionUrlCategoryDelimiter;
}
if ($isUrl) {
$urlFragment = Piwik_Tracker_Action::processUrlFragment($urlFragment);
if (!empty($urlFragment)) {
$name .= '#' . $urlFragment;
}
}
if (empty($categoryDelimiter)) {
return array(trim($name));
}
$split = explode($categoryDelimiter, $name, self::getSubCategoryLevelLimit());
// trim every category and remove empty categories
$split = array_map('trim', $split);
$split = array_filter($split, 'strlen');
// forces array key to start at 0
$split = array_values($split);
if (empty($split)) {
$defaultName = self::getUnknownActionName($type);
return array(trim($defaultName));
}
$lastPageName = end($split);
// we are careful to prefix the page URL / name with some value
// so that if a page has the same name as a category
// we don't merge both entries
if ($type != Piwik_Tracker_Action::TYPE_ACTION_NAME) {
$lastPageName = '/' . $lastPageName;
} else {
$lastPageName = ' ' . $lastPageName;
}
$split[count($split) - 1] = $lastPageName;
return array_values($split);
}
/**
* For an array of visits, query the list of pages for this visit
* as well as make the data human readable
* @param array $visitorDetails
* @param int $idSite
* @return Piwik_DataTable
*/
private function getCleanedVisitorsFromDetails($visitorDetails, $idSite)
{
$table = new Piwik_DataTable();
$site = new Piwik_Site($idSite);
$timezone = $site->getTimezone();
$currencies = Piwik_SitesManager_API::getInstance()->getCurrencySymbols();
foreach ($visitorDetails as $visitorDetail) {
$this->cleanVisitorDetails($visitorDetail, $idSite);
$visitor = new Piwik_Live_Visitor($visitorDetail);
$visitorDetailsArray = $visitor->getAllVisitorDetails();
$visitorDetailsArray['siteCurrency'] = $site->getCurrency();
$visitorDetailsArray['siteCurrencySymbol'] = @$currencies[$site->getCurrency()];
$visitorDetailsArray['serverTimestamp'] = $visitorDetailsArray['lastActionTimestamp'];
$dateTimeVisit = Piwik_Date::factory($visitorDetailsArray['lastActionTimestamp'], $timezone);
$visitorDetailsArray['serverTimePretty'] = $dateTimeVisit->getLocalized('%time%');
$visitorDetailsArray['serverDatePretty'] = $dateTimeVisit->getLocalized(Piwik_Translate('CoreHome_ShortDateFormat'));
$dateTimeVisitFirstAction = Piwik_Date::factory($visitorDetailsArray['firstActionTimestamp'], $timezone);
$visitorDetailsArray['serverDatePrettyFirstAction'] = $dateTimeVisitFirstAction->getLocalized(Piwik_Translate('CoreHome_ShortDateFormat'));
$visitorDetailsArray['serverTimePrettyFirstAction'] = $dateTimeVisitFirstAction->getLocalized('%time%');
$idvisit = $visitorDetailsArray['idVisit'];
$sqlCustomVariables = '';
for ($i = 1; $i <= Piwik_Tracker::MAX_CUSTOM_VARIABLES; $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\tlog_action.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.idlink_va AS pageId,\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\t{$sqlCustomVariables}\n\t\t\t\tFROM " . Piwik_Common::prefixTable('log_link_visit_action') . " AS log_link_visit_action\n\t\t\t\t\tINNER JOIN " . Piwik_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 " . Piwik_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\tWHERE log_link_visit_action.idvisit = ?\n\t\t\t\t ";
$actionDetails = Piwik_FetchAll($sql, array($idvisit));
foreach ($actionDetails as $actionIdx => &$actionDetail) {
$customVariablesPage = array();
for ($i = 1; $i <= Piwik_Tracker::MAX_CUSTOM_VARIABLES; $i++) {
if (!empty($actionDetail['custom_var_k' . $i]) && !empty($actionDetail['custom_var_v' . $i])) {
$customVariablesPage[$i] = array('customVariableName' . $i => $actionDetail['custom_var_k' . $i], 'customVariableValue' . $i => $actionDetail['custom_var_v' . $i]);
}
unset($actionDetail['custom_var_k' . $i]);
unset($actionDetail['custom_var_v' . $i]);
}
if (!empty($customVariablesPage)) {
$actionDetail['customVariables'] = $customVariablesPage;
}
// reconstruct url from prefix
$actionDetail['url'] = Piwik_Tracker_Action::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::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.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 " . Piwik_Common::prefixTable('log_conversion') . " AS log_conversion\n\t\t\t\tLEFT JOIN " . Piwik_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\t\t\t";
$goalDetails = Piwik_FetchAll($sql, array($idvisit));
$sql = "SELECT \n\t\t\t\t\t\tcase idgoal when " . Piwik_Tracker_GoalManager::IDGOAL_CART . " then '" . Piwik_Archive::LABEL_ECOMMERCE_CART . "' else '" . Piwik_Archive::LABEL_ECOMMERCE_ORDER . "' end as type,\n\t\t\t\t\t\tidorder as orderId,\n\t\t\t\t\t\t" . Piwik_ArchiveProcessing_Day::getSqlRevenue('revenue') . " as revenue,\n\t\t\t\t\t\t" . Piwik_ArchiveProcessing_Day::getSqlRevenue('revenue_subtotal') . " as revenueSubTotal,\n\t\t\t\t\t\t" . Piwik_ArchiveProcessing_Day::getSqlRevenue('revenue_tax') . " as revenueTax,\n\t\t\t\t\t\t" . Piwik_ArchiveProcessing_Day::getSqlRevenue('revenue_shipping') . " as revenueShipping,\n\t\t\t\t\t\t" . Piwik_ArchiveProcessing_Day::getSqlRevenue('revenue_discount') . " as revenueDiscount,\n\t\t\t\t\t\titems as items,\n\t\t\t\t\t\t\n\t\t\t\t\t\tlog_conversion.server_time as serverTimePretty\n\t\t\t\t\tFROM " . Piwik_Common::prefixTable('log_conversion') . " AS log_conversion\n\t\t\t\t\tWHERE idvisit = ?\n\t\t\t\t\t\tAND idgoal <= " . Piwik_Tracker_GoalManager::IDGOAL_ORDER;
$ecommerceDetails = Piwik_FetchAll($sql, array($idvisit));
foreach ($ecommerceDetails as &$ecommerceDetail) {
if ($ecommerceDetail['type'] == Piwik_Archive::LABEL_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($this, 'sortByServerTime'));
foreach ($ecommerceDetails as $key => &$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" . Piwik_ArchiveProcessing_Day::getSqlRevenue('price') . " as price,\n\t\t\t\t\t\t\tquantity as quantity\n\t\t\t\t\t\tFROM " . Piwik_Common::prefixTable('log_conversion_item') . "\n\t\t\t\t\t\t\tINNER JOIN " . Piwik_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 " . Piwik_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 " . Piwik_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";
$bind = array($idvisit, isset($ecommerceConversion['orderId']) ? $ecommerceConversion['orderId'] : Piwik_Tracker_GoalManager::ITEM_IDORDER_ABANDONED_CART);
$itemsDetails = Piwik_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($this, 'sortByServerTime'));
$visitorDetailsArray['actionDetails'] = $actions;
// Convert datetimes to the site timezone
foreach ($visitorDetailsArray['actionDetails'] as &$details) {
switch ($details['type']) {
case 'goal':
$details['icon'] = 'themes/default/images/goal.png';
break;
case Piwik_Archive::LABEL_ECOMMERCE_ORDER:
case Piwik_Archive::LABEL_ECOMMERCE_CART:
$details['icon'] = 'themes/default/images/' . $details['type'] . '.gif';
break;
case Piwik_Tracker_Action_Interface::TYPE_DOWNLOAD:
$details['type'] = 'download';
$details['icon'] = 'themes/default/images/download.png';
break;
case Piwik_Tracker_Action_Interface::TYPE_OUTLINK:
$details['type'] = 'outlink';
$details['icon'] = 'themes/default/images/link.gif';
break;
default:
$details['type'] = 'action';
$details['icon'] = null;
break;
}
$dateTimeVisit = Piwik_Date::factory($details['serverTimePretty'], $timezone);
$details['serverTimePretty'] = $dateTimeVisit->getLocalized(Piwik_Translate('CoreHome_ShortDateFormat') . ' %time%');
}
$visitorDetailsArray['goalConversions'] = count($goalDetails);
$table->addRowFromArray(array(Piwik_DataTable_Row::COLUMNS => $visitorDetailsArray));
}
return $table;
}
/**
* Given a page name and type, builds a recursive datatable where
* each level of the tree is a category, based on the page name split by a delimiter (slash / by default)
*
* @param string $actionName
* @param int $actionType
* @param int $urlPrefix
* @return Piwik_DataTable
*/
protected function parseActionNameCategoriesInDataTable($actionName, $actionType, $urlPrefix = null)
{
// we work on the root table of the given TYPE (either ACTION_URL or DOWNLOAD or OUTLINK etc.)
$currentTable =& $this->actionsTablesByType[$actionType];
// go to the level of the subcategory
$actionExplodedNames = $this->getActionExplodedNames($actionName, $actionType, $urlPrefix);
$end = count($actionExplodedNames) - 1;
for ($level = 0; $level < $end; $level++) {
$actionCategory = $actionExplodedNames[$level];
$currentTable =& $currentTable[$actionCategory];
}
$actionShortName = $actionExplodedNames[$end];
// currentTable is now the array element corresponding the the action
// at this point we may be for example at the 4th level of depth in the hierarchy
$currentTable =& $currentTable[$actionShortName];
// add the row to the matching sub category subtable
if (!$currentTable instanceof Piwik_DataTable_Row) {
$defaultColumnsNewRow = array('label' => (string) $actionShortName, Piwik_Archive::INDEX_NB_VISITS => 0, Piwik_Archive::INDEX_NB_UNIQ_VISITORS => 0, Piwik_Archive::INDEX_PAGE_NB_HITS => 0, Piwik_Archive::INDEX_PAGE_SUM_TIME_SPENT => 0);
if ($actionType == Piwik_Tracker_Action::TYPE_ACTION_NAME) {
$currentTable = new Piwik_DataTable_Row(array(Piwik_DataTable_Row::COLUMNS => $defaultColumnsNewRow));
} else {
$currentTable = new Piwik_DataTable_Row(array(Piwik_DataTable_Row::COLUMNS => $defaultColumnsNewRow, Piwik_DataTable_Row::METADATA => array('url' => Piwik_Tracker_Action::reconstructNormalizedUrl((string) $actionName, $urlPrefix))));
}
}
return $currentTable;
}
/**
* Reads items from the request, then looks up the names from the lookup table
* and returns a clean array of items ready for the database.
*
* @param array $items
* @return array $cleanedItems
*/
protected function getCleanedEcommerceItems($items)
{
// Clean up the items array
$cleanedItems = array();
foreach ($items as $item) {
$name = $category = $category2 = $category3 = $category4 = $category5 = false;
$price = 0;
$quantity = 1;
// items are passed in the request as an array: ( $sku, $name, $category, $price, $quantity )
if (empty($item[self::INDEX_ITEM_SKU])) {
continue;
}
$sku = $item[self::INDEX_ITEM_SKU];
if (!empty($item[self::INDEX_ITEM_NAME])) {
$name = $item[self::INDEX_ITEM_NAME];
}
if (!empty($item[self::INDEX_ITEM_CATEGORY])) {
$category = $item[self::INDEX_ITEM_CATEGORY];
}
if (isset($item[self::INDEX_ITEM_PRICE]) && is_numeric($item[self::INDEX_ITEM_PRICE])) {
$price = $this->getRevenue($item[self::INDEX_ITEM_PRICE]);
}
if (!empty($item[self::INDEX_ITEM_QUANTITY]) && is_numeric($item[self::INDEX_ITEM_QUANTITY])) {
$quantity = (int) $item[self::INDEX_ITEM_QUANTITY];
}
// self::INDEX_ITEM_* are in order
$cleanedItems[] = array(self::INTERNAL_ITEM_SKU => $sku, self::INTERNAL_ITEM_NAME => $name, self::INTERNAL_ITEM_CATEGORY => $category, self::INTERNAL_ITEM_CATEGORY2 => $category2, self::INTERNAL_ITEM_CATEGORY3 => $category3, self::INTERNAL_ITEM_CATEGORY4 => $category4, self::INTERNAL_ITEM_CATEGORY5 => $category5, self::INTERNAL_ITEM_PRICE => $price, self::INTERNAL_ITEM_QUANTITY => $quantity);
}
// Lookup Item SKUs, Names & Categories Ids
$actionsToLookupAllItems = array();
// Each item has 7 potential "ids" to lookup in the lookup table
$columnsInEachRow = 1 + 1 + self::MAXIMUM_PRODUCT_CATEGORIES;
foreach ($cleanedItems as $item) {
$actionsToLookup = array();
list($sku, $name, $category, $price, $quantity) = $item;
$actionsToLookup[] = array(trim($sku), Piwik_Tracker_Action::TYPE_ECOMMERCE_ITEM_SKU);
$actionsToLookup[] = array(trim($name), Piwik_Tracker_Action::TYPE_ECOMMERCE_ITEM_NAME);
// Only one category
if (!is_array($category)) {
$actionsToLookup[] = array(trim($category), Piwik_Tracker_Action::TYPE_ECOMMERCE_ITEM_CATEGORY);
} else {
$countCategories = 0;
foreach ($category as $productCategory) {
$productCategory = trim($productCategory);
if (empty($productCategory)) {
continue;
}
$countCategories++;
if ($countCategories > self::MAXIMUM_PRODUCT_CATEGORIES) {
break;
}
$actionsToLookup[] = array($productCategory, Piwik_Tracker_Action::TYPE_ECOMMERCE_ITEM_CATEGORY);
}
}
// Ensure that each row has the same number of columns, fill in the blanks
for ($i = count($actionsToLookup); $i < $columnsInEachRow; $i++) {
$actionsToLookup[] = array(false, Piwik_Tracker_Action::TYPE_ECOMMERCE_ITEM_CATEGORY);
}
$actionsToLookupAllItems = array_merge($actionsToLookupAllItems, $actionsToLookup);
}
$actionsLookedUp = Piwik_Tracker_Action::loadActionId($actionsToLookupAllItems);
// var_dump($actionsLookedUp);
// Replace SKU, name & category by their ID action
foreach ($cleanedItems as $index => &$item) {
list($sku, $name, $category, $price, $quantity) = $item;
// SKU
$item[0] = $actionsLookedUp[$index * $columnsInEachRow + 0][2];
// Name
$item[1] = $actionsLookedUp[$index * $columnsInEachRow + 1][2];
// Categories
$item[2] = $actionsLookedUp[$index * $columnsInEachRow + 2][2];
$item[3] = $actionsLookedUp[$index * $columnsInEachRow + 3][2];
$item[4] = $actionsLookedUp[$index * $columnsInEachRow + 4][2];
$item[5] = $actionsLookedUp[$index * $columnsInEachRow + 5][2];
$item[6] = $actionsLookedUp[$index * $columnsInEachRow + 6][2];
}
return $cleanedItems;
}
/**
* Reads items from the request, then looks up the names from the lookup table
* and returns a clean array of items ready for the database.
*
* @param array $items
* @return array $cleanedItems
*/
protected function getCleanedEcommerceItems($items)
{
// Clean up the items array
$cleanedItems = array();
foreach($items as $item)
{
$name = $category = false;
$price = 0;
$quantity = 1;
// items are passed in the request as an array: ( $sku, $name, $category, $price, $quantity )
if(empty($item[self::INDEX_ITEM_SKU])) {
continue;
}
$sku = $item[self::INDEX_ITEM_SKU];
if(!empty($item[self::INDEX_ITEM_NAME])) {
$name = $item[self::INDEX_ITEM_NAME];
}
if(!empty($item[self::INDEX_ITEM_CATEGORY])) {
$category = $item[self::INDEX_ITEM_CATEGORY];
}
if(!empty($item[self::INDEX_ITEM_PRICE])
&& is_numeric($item[self::INDEX_ITEM_PRICE])) {
$price = $this->getRevenue($item[self::INDEX_ITEM_PRICE]);
}
if(!empty($item[self::INDEX_ITEM_QUANTITY])
&& is_numeric($item[self::INDEX_ITEM_QUANTITY])) {
$quantity = (int)$item[self::INDEX_ITEM_QUANTITY];
}
// self::INDEX_ITEM_* are in order
$cleanedItems[] = array( $sku, $name, $category, $price, $quantity );
}
// Lookup Item SKUs, Names & Categories Ids
$actionsToLookup = array();
foreach($cleanedItems as $item)
{
list($sku, $name, $category, $price, $quantity) = $item;
$actionsToLookup[] = array($sku, Piwik_Tracker_Action::TYPE_ECOMMERCE_ITEM_SKU);
$actionsToLookup[] = array($name, Piwik_Tracker_Action::TYPE_ECOMMERCE_ITEM_NAME);
$actionsToLookup[] = array($category, Piwik_Tracker_Action::TYPE_ECOMMERCE_ITEM_CATEGORY);
}
$actionsLookedUp = Piwik_Tracker_Action::loadActionId($actionsToLookup);
// var_dump($actionsLookedUp);
// Replace SKU, name & category by their ID action
foreach($cleanedItems as $index => &$item)
{
list($sku, $name, $category, $price, $quantity) = $item;
// SKU
$item[0] = $actionsLookedUp[ $index * 3 + self::INDEX_ITEM_SKU][2];
// Name
$item[1] = $actionsLookedUp[ $index * 3 + self::INDEX_ITEM_NAME][2];
// Category
$item[2] = $actionsLookedUp[ $index * 3 + self::INDEX_ITEM_CATEGORY][2];
}
return $cleanedItems;
}
