/** * Is the request for a known VisitorId, based on 1st party, 3rd party (optional) cookies or Tracking API forced Visitor ID * @throws Exception */ protected function assignVisitorIdFromRequest() { $found = false; // Was a Visitor ID "forced" (@see Tracking API setVisitorId()) for this request? $idVisitor = $this->forcedVisitorId; if (!empty($idVisitor)) { if (strlen($idVisitor) != Piwik_Tracker::LENGTH_HEX_ID_STRING) { throw new Exception("Visitor ID (cid) must be " . Piwik_Tracker::LENGTH_HEX_ID_STRING . " characters long"); } printDebug("Request will be recorded for this idvisitor = " . $idVisitor); $found = true; } // - If set to use 3rd party cookies for Visit ID, read the cookie if (!$found) { // - By default, reads the first party cookie ID $useThirdPartyCookie = $this->shouldUseThirdPartyCookie(); if ($useThirdPartyCookie) { $idVisitor = $this->cookie->get(0); if ($idVisitor !== false && strlen($idVisitor) == Piwik_Tracker::LENGTH_HEX_ID_STRING) { $found = true; } } } // If a third party cookie was not found, we default to the first party cookie if (!$found) { $idVisitor = Piwik_Common::getRequestVar('_id', '', 'string', $this->request); $found = strlen($idVisitor) >= Piwik_Tracker::LENGTH_HEX_ID_STRING; } if ($found) { $truncated = substr($idVisitor, 0, Piwik_Tracker::LENGTH_HEX_ID_STRING); $binVisitorId = @Piwik_Common::hex2bin($truncated); if (!empty($binVisitorId)) { $this->visitorInfo['idvisitor'] = $binVisitorId; } } }
private function loadLastVisitorDetailsFromDatabase($idSite, $period = false, $date = false, $segment = false, $filter_limit = false, $maxIdVisit = false, $visitorId = false, $minTimestamp = false) { // var_dump($period); var_dump($date); var_dump($filter_limit); var_dump($maxIdVisit); var_dump($visitorId); //var_dump($minTimestamp); if(empty($filter_limit)) { $filter_limit = 100; } $where = $whereBind = array(); $where[] = "log_visit.idsite = ? "; $whereBind[] = $idSite; $orderBy = "idsite, visit_last_action_time DESC"; $orderByParent = "sub.visit_last_action_time DESC"; if(!empty($visitorId)) { $where[] = "log_visit.idvisitor = ? "; $whereBind[] = Piwik_Common::hex2bin($visitorId); } if(!empty($maxIdVisit)) { $where[] = "log_visit.idvisit < ? "; $whereBind[] = $maxIdVisit; $orderBy = "idvisit DESC"; $orderByParent = "sub.idvisit DESC"; } if(!empty($minTimestamp)) { $where[] = "log_visit.visit_last_action_time > ? "; $whereBind[] = date("Y-m-d H:i:s", $minTimestamp); } // If no other filter, only look at the last 24 hours of stats if(empty($visitorId) && empty($maxIdVisit) && empty($period) && empty($date)) { $period = 'day'; $date = 'yesterdaySameTime'; } // SQL Filter with provided period if (!empty($period) && !empty($date)) { $currentSite = new Piwik_Site($idSite); $currentTimezone = $currentSite->getTimezone(); $dateString = $date; if($period == 'range') { $processedPeriod = new Piwik_Period_Range('range', $date); if($parsedDate = Piwik_Period_Range::parseDateRange($date)) { $dateString = $parsedDate[2]; } } else { $processedDate = Piwik_Date::factory($date); if($date == 'today' || $date == 'now' || $processedDate->toString() == Piwik_Date::factory('now', $currentTimezone)->toString()) { $processedDate = $processedDate->subDay(1); } $processedPeriod = Piwik_Period::factory($period, $processedDate); } $dateStart = $processedPeriod->getDateStart()->setTimezone($currentTimezone); $where[] = "log_visit.visit_last_action_time >= ?"; $whereBind[] = $dateStart->toString('Y-m-d H:i:s'); if(!in_array($date, array('now', 'today', 'yesterdaySameTime')) && strpos($date, 'last') === false && strpos($date, 'previous') === false && Piwik_Date::factory($dateString)->toString('Y-m-d') != Piwik_Date::factory('now', $currentTimezone)->toString()) { $dateEnd = $processedPeriod->getDateEnd()->setTimezone($currentTimezone); $where[] = " log_visit.visit_last_action_time <= ?"; $dateEndString = $dateEnd->addDay(1)->toString('Y-m-d H:i:s'); $whereBind[] = $dateEndString; } } $sqlWhere = ""; if(count($where) > 0) { $sqlWhere = " WHERE " . join(" AND ", $where); } $segment = new Piwik_Segment($segment, $idSite); $segmentSql = $segment->getSql(); $sqlSegment = $segmentSql['sql']; if(!empty($sqlSegment)) $sqlSegment = ' AND '.$sqlSegment; $whereBind = array_merge ( $whereBind, $segmentSql['bind'] ); // Subquery to use the indexes for ORDER BY // Group by idvisit so that a visitor converting 2 goals only appears twice $sql = " SELECT sub.* FROM ( SELECT * FROM " . Piwik_Common::prefixTable('log_visit') . " AS log_visit $sqlWhere $sqlSegment ORDER BY $orderBy LIMIT ".(int)$filter_limit." ) AS sub GROUP BY sub.idvisit ORDER BY $orderByParent "; try { $data = Piwik_FetchAll($sql, $whereBind); } catch(Exception $e) { echo $e->getMessage();exit; } //var_dump($whereBind); echo($sql); //var_dump($data); return $data; }
/** * This methods tries to see if the visitor has visited the website before. * * We have to split the visitor into one of the category * - Known visitor * - New visitor * * A known visitor is a visitor that has already visited the website in the current month. * We define a known visitor using the algorithm: * * 1) Checking if a cookie contains * // a unique id for the visitor * - id_visitor * * // the timestamp of the last action in the most recent visit * - timestamp_last_action * * // the timestamp of the first action in the most recent visit * - timestamp_first_action * * // the ID of the most recent visit (which could be in the past or the current visit) * - id_visit * * // the ID of the most recent action * - id_last_action * * 2) If the visitor doesn't have a cookie, we try to look for a similar visitor configuration. * We search for a visitor with the same plugins/OS/Browser/Resolution for today for this website. */ protected function recognizeTheVisitor() { $this->visitorKnown = false; $this->setCookie(new Piwik_Cookie($this->getCookieName(), $this->getCookieExpire(), $this->getCookiePath())); $this->printCookie(); $found = $forcedVisitorId = false; // Was a Visitor ID "forced" (@see Tracking API setVisitorId()) for this request? $idVisitor = $this->forcedVisitorId; if (!empty($idVisitor)) { if (strlen($idVisitor) != Piwik_Tracker::LENGTH_HEX_ID_STRING) { throw new Exception("Visitor ID (cid) must be " . Piwik_Tracker::LENGTH_HEX_ID_STRING . " characters long"); } printDebug("Request will be forced to record for this idvisitor = " . $idVisitor); $forcedVisitorId = true; $found = true; } if (!$found) { // - If set to use 3rd party cookies for Visit ID, read the cookies // - By default, reads the first party cookie ID $useThirdPartyCookie = $this->shouldUseThirdPartyCookie(); if ($useThirdPartyCookie) { $idVisitor = $this->cookie->get(0); if ($idVisitor !== false && strlen($idVisitor) == Piwik_Tracker::LENGTH_HEX_ID_STRING) { $found = true; } } } // If a third party cookie was not found, we default to the first party cookie if (!$found) { $idVisitor = Piwik_Common::getRequestVar('_id', '', 'string', $this->request); $found = strlen($idVisitor) >= Piwik_Tracker::LENGTH_HEX_ID_STRING; } // Does the cookie contain a Visitor ID? if ($found) { $this->visitorInfo['idvisitor'] = Piwik_Common::hex2bin($idVisitor); printDebug("The visitor has the piwik cookie (idvisitor = " . $idVisitor . ") "); } else { printDebug("Visitor doesn't have the piwik cookie."); } $userInfo = $this->getUserSettingsInformation(); $configId = $userInfo['config_id']; $timeLookBack = date('Y-m-d H:i:s', $this->getCurrentTimestamp() - self::TIME_IN_PAST_TO_SEARCH_FOR_VISITOR); $where = "visit_last_action_time >= ?\n\t\t\t\t\tAND idsite = ?"; $bindSql = array($timeLookBack, $this->idsite); // we always match on the config_id, except if the current request forces the visitor id if (!$forcedVisitorId) { $where .= ' AND config_id = ? '; $bindSql[] = $configId; } // We force to match a visitor ID // 1) If the visitor cookies should be trusted (ie. intranet) - config file setting // 2) or if the Visitor ID was forced via the Tracking API setVisitorId() if (!empty($this->visitorInfo['idvisitor']) && (Piwik_Tracker_Config::getInstance()->Tracker['trust_visitors_cookies'] || $forcedVisitorId)) { printDebug("Matching the visitor based on his idcookie: " . bin2hex($this->visitorInfo['idvisitor']) . "..."); $where .= ' AND idvisitor = ?'; $bindSql[] = $this->visitorInfo['idvisitor']; } $sql = " SELECT \tidvisitor,\n\t\t\t\t\t\t\tvisit_last_action_time,\n\t\t\t\t\t\t\tvisit_first_action_time,\n\t\t\t\t\t\t\tidvisit,\n\t\t\t\t\t\t\tvisit_exit_idaction_url,\n\t\t\t\t\t\t\tvisit_exit_idaction_name,\n\t\t\t\t\t\t\tvisitor_returning,\n\t\t\t\t\t\t\tvisitor_days_since_first,\n\t\t\t\t\t\t\treferer_name,\n\t\t\t\t\t\t\treferer_keyword,\n\t\t\t\t\t\t\treferer_type,\n\t\t\t\t\t\t\tvisitor_count_visits\n\t\t\t\tFROM " . Piwik_Common::prefixTable('log_visit') . " WHERE " . $where . "\n\t\t\t\tORDER BY visit_last_action_time DESC\n\t\t\t\tLIMIT 1"; $visitRow = Piwik_Tracker::getDatabase()->fetch($sql, $bindSql); if (!Piwik_Tracker_Config::getInstance()->Debug['tracker_always_new_visitor'] && $visitRow && count($visitRow) > 0) { // These values will be used throughout the request $this->visitorInfo['visit_last_action_time'] = strtotime($visitRow['visit_last_action_time']); $this->visitorInfo['visit_first_action_time'] = strtotime($visitRow['visit_first_action_time']); $this->visitorInfo['idvisitor'] = $visitRow['idvisitor']; $this->visitorInfo['idvisit'] = $visitRow['idvisit']; $this->visitorInfo['visit_exit_idaction_url'] = $visitRow['visit_exit_idaction_url']; $this->visitorInfo['visit_exit_idaction_name'] = $visitRow['visit_exit_idaction_name']; $this->visitorInfo['visitor_returning'] = $visitRow['visitor_returning']; $this->visitorInfo['visitor_days_since_first'] = $visitRow['visitor_days_since_first']; $this->visitorInfo['visitor_count_visits'] = $visitRow['visitor_count_visits']; // Referer information will be potentially used for Goal Conversion attribution $this->visitorInfo['referer_name'] = $visitRow['referer_name']; $this->visitorInfo['referer_keyword'] = $visitRow['referer_keyword']; $this->visitorInfo['referer_type'] = $visitRow['referer_type']; $this->visitorKnown = true; printDebug("The visitor is known (idvisitor = " . bin2hex($this->visitorInfo['idvisitor']) . ",\n\t\t\t\t\t\tconfig_id = " . bin2hex($configId) . ", \n\t\t\t\t\t\tidvisit = {$this->visitorInfo['idvisit']}, \n\t\t\t\t\t\tlast action = " . date("r", $this->visitorInfo['visit_last_action_time']) . ", \n\t\t\t\t\t\tfirst action = " . date("r", $this->visitorInfo['visit_first_action_time']) . ")"); } else { printDebug("The visitor was not matched with an existing visitor..."); } }