Example #1
0
 /** 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);
 }
Example #2
0
    /**
     * 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);
    }
Example #3
0
    /** 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);
    }