public queryAdjacentVisitorId ( integer $idSite, string $visitorId, string $visitLastActionTime, string $segment, boolean $getNext ) : string | ||
$idSite | integer | The ID of the site whose visits should be looked at. |
$visitorId | string | The ID of the visitor to get an adjacent visitor for. |
$visitLastActionTime | string | The last action time of the latest visit for $visitorId. |
$segment | string | |
$getNext | boolean | Whether to retrieve the next visitor or the previous visitor. The next visitor will be the visitor that appears chronologically later in the log_visit table. The previous visitor will be the visitor that appears earlier. |
return | string | The hex visitor ID. |
public function queryAdjacentVisitorId($idSite, $visitorId, $visitLastActionTime, $segment, $getNext)
{
if ($getNext) {
$visitLastActionTimeCondition = "sub.visit_last_action_time <= ?";
$orderByDir = "DESC";
} else {
$visitLastActionTimeCondition = "sub.visit_last_action_time >= ?";
$orderByDir = "ASC";
}
$visitLastActionDate = Date::factory($visitLastActionTime);
$dateOneDayAgo = $visitLastActionDate->subDay(1);
$dateOneDayInFuture = $visitLastActionDate->addDay(1);
$select = "log_visit.idvisitor, MAX(log_visit.visit_last_action_time) as visit_last_action_time";
$from = "log_visit";
$where = "log_visit.idsite = ? AND log_visit.idvisitor <> ? AND visit_last_action_time >= ? and visit_last_action_time <= ?";
$whereBind = array($idSite, @Common::hex2bin($visitorId), $dateOneDayAgo->toString('Y-m-d H:i:s'), $dateOneDayInFuture->toString('Y-m-d H:i:s'));
$orderBy = "MAX(log_visit.visit_last_action_time) {$orderByDir}";
$groupBy = "log_visit.idvisitor";
$segment = new Segment($segment, $idSite);
$queryInfo = $segment->getSelectQuery($select, $from, $where, $whereBind, $orderBy, $groupBy);
$sql = "SELECT sub.idvisitor, sub.visit_last_action_time FROM ({$queryInfo['sql']}) as sub\n WHERE {$visitLastActionTimeCondition}\n LIMIT 1";
$bind = array_merge($queryInfo['bind'], array($visitLastActionTime));
$visitorId = Db::fetchOne($sql, $bind);
if (!empty($visitorId)) {
$visitorId = bin2hex($visitorId);
}
return $visitorId;
}
/** * @param DataTable $visits * @param $visitorId * @param $segment */ private function handleAdjacentVisitorIds(DataTable $visits, $visitorId, $segment) { // get visitor IDs that are adjacent to this one in log_visit // TODO: make sure order of visitor ids is not changed if a returning visitor visits while the user is // looking at the popup. $rows = $visits->getRows(); $latestVisitTime = reset($rows)->getColumn('lastActionDateTime'); $model = new Model(); $this->profile['nextVisitorId'] = $model->queryAdjacentVisitorId($this->idSite, $visitorId, $latestVisitTime, $segment, $getNext = true); $this->profile['previousVisitorId'] = $model->queryAdjacentVisitorId($this->idSite, $visitorId, $latestVisitTime, $segment, $getNext = false); }