/**
* Analyze pages associated with the search and stores them in a single
* DataTable for all keywords.
*/
private function dayAnalyzeAssociatedPages($following)
{
if ($following) {
// pages following a search
$getAction = 'idaction_url';
$setAction = 'idaction_url_ref';
} else {
// pages before a search
$getAction = 'idaction_url_ref';
$setAction = 'idaction_url';
}
$bind = $this->getSqlBindings();
$bind[':url'] = $this->getSiteUrlBase();
// version class might not be available in archiving or tracker
require_once PIWIK_INCLUDE_PATH . '/core/Version.php';
// check whether version is prior to 1.2, adjust sql query
$version = explode('.', Piwik_Version::VERSION);
$pre12 = $version[0] < 1 || $version[1] < 2;
if ($pre12) {
$dateRange = '(visit.visit_server_date BETWEEN :startDate AND :endDate)';
} else {
$dateRange = '(visit_action.server_time BETWEEN :startDate AND :endDate)';
}
$sql = '
SELECT
CONCAT(search.id, "_", action_get.idaction) AS `' . self::LABEL . '`,
search.id AS `' . self::SEARCH_TERM_ID . '`,
REPLACE(action_get.name, :url, "") AS `' . self::PAGE . '`,
action_get.name AS `' . self::URL . '`,
COUNT(action_get.idaction) AS `' . self::HITS . '`
FROM
' . Piwik_Common::prefixTable('log_action') . ' AS action_set
LEFT JOIN
' . Piwik_Common::prefixTable('log_link_visit_action') . ' AS visit_action
ON action_set.idaction = visit_action.' . $setAction . '
LEFT JOIN
' . Piwik_Common::prefixTable('log_action') . ' AS action_get
ON action_get.idaction = visit_action.' . $getAction . '
LEFT JOIN
' . Piwik_Common::prefixTable('log_visit') . ' AS visit
ON visit.idvisit = visit_action.idvisit
LEFT JOIN
' . Piwik_Common::prefixTable('log_sitesearch') . ' AS search
ON action_set.search_term = search.id
WHERE
visit.idsite = :idsite AND
visit_action.idaction_url_ref != 0 AND
action_set.search_term IS NOT NULL AND
action_get.search_term IS NULL AND
' . $dateRange . '
GROUP BY
search.id,
action_get.idaction
';
$data = Piwik_SiteSearch_Db::fetchAll($sql, $bind);
$name = ($following ? 'following' : 'previous') . 'Pages';
return $this->archiveDataArray($name, $data, false, true);
}
/** Analyze site for serach URLs */
private function analyzeSite($idSite)
{
// remove all searchterms from actions
Piwik_Query('
UPDATE ' . Piwik_Common::prefixTable('log_action') . ' AS action
SET search_term = NULL
WHERE search_term IS NOT NULL AND EXISTS (
SELECT
search.idsite
FROM
' . Piwik_Common::prefixTable('log_sitesearch') . ' AS search
WHERE
search.idsite = ' . intval($idSite) . ' AND
search.id = action.search_term
)
');
// rescan
$site = Piwik_SitesManager_API::getInstance()->getSiteFromId($idSite);
if (empty($site['sitesearch_url']) || empty($site['sitesearch_parameter'])) {
return;
}
// this is only a rough filter, Piwik_SiteSearch_Archive::logAction
// will do a more precise check
$url = '%' . $site['sitesearch_url'] . '%' . $site['sitesearch_parameter'] . '=%';
$sql = '
SELECT
action.idaction,
action.name
FROM
' . Piwik_Common::prefixTable('log_action') . ' AS action
LEFT JOIN
' . Piwik_Common::prefixTable('log_link_visit_action') . ' AS link
ON action.idaction = link.idaction_url
LEFT JOIN
' . Piwik_Common::prefixTable('log_visit') . ' AS visit
ON link.idvisit = visit.idvisit
WHERE
action.type = 1 AND
action.name LIKE :name AND
visit.idsite = :idSite
GROUP BY
action.idaction
';
$bind = array(':name' => $url, ':idSite' => intval($idSite));
$result = Piwik_SiteSearch_Db::fetchAll($sql, $bind);
foreach ($result as $action) {
Piwik_SiteSearch_Archive::logAction($action, $idSite, $site);
}
// remove unneccessary sitesearch entries
$sql = '
DELETE FROM ' . Piwik_Common::prefixTable('log_sitesearch') . '
WHERE idsite = ' . intval($idSite) . ' AND NOT EXISTS (
SELECT action.idaction
FROM ' . Piwik_Common::prefixTable('log_action') . ' AS action
WHERE action.search_term = id
)
';
Piwik_Query($sql);
$this->clearArchive($idSite);
}