/** Change query bindings from named bindings to ? bindings */ private static function rebindQuery($function, &$sql, &$bindings) { self::$bindings = $bindings; self::$newBindings = array(); $sql = preg_replace_callback('/:[a-z0-9]*/i', array('Piwik_SiteSearch_Db', 'replaceBinding'), $sql); return call_user_func($function, $sql, self::$newBindings); }
/** * 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); }