public static function update($idvisitor, $name = false, $email = false, $phone = false, $comments = false)
{
if ($name == false && $email == false && $phone == false && $comments == false) {
return false;
}
$buildQuery = "";
$argSet = array();
$argOnUpdate = array();
$argSet[] = @Common::hex2bin($idvisitor);
if ($name) {
$argSet[] = $name;
$argOnUpdate[] = $name;
$buildQuery .= " name = ?,";
}
if ($email) {
$argSet[] = $email;
$argOnUpdate[] = $email;
$buildQuery .= " email = ?,";
}
if ($phone) {
$argSet[] = $phone;
$argOnUpdate[] = $phone;
$buildQuery .= " phone = ?,";
}
if ($comments) {
$argSet[] = $comments;
$argOnUpdate[] = $comments;
$buildQuery .= " comments = ?,";
}
$buildQuery = trim(substr_replace($buildQuery, "", -1));
$arguments = array_merge($argSet, $argOnUpdate);
Db::query("INSERT INTO " . Common::prefixTable('chat_personnal_informations') . " SET idvisitor = ?, {$buildQuery} ON DUPLICATE KEY UPDATE {$buildQuery}", $arguments);
return true;
}
public static function setLastSent($idsite, $idvisitor, $microtime)
{
foreach (ChatCommon::getUsersBySite($idsite) as $user) {
$arguments = array($user['login'], @Common::hex2bin($idvisitor), $microtime, $microtime);
Db::query("INSERT INTO " . Common::prefixTable('chat_history_admin') . " SET login = ?, idvisitor = ?, lastsent = ? ON DUPLICATE KEY UPDATE lastsent = ?", $arguments);
}
return true;
}
public function test_makeLogVisitsQueryString_whenSegment()
{
$model = new Model();
list($sql, $bind) = $model->makeLogVisitsQueryString($idSite = 1, $period = 'month', $date = '2010-01-01', $segment = 'customVariablePageName1==Test', $offset = 0, $limit = 100, $visitorId = 'abc', $minTimestamp = false, $filterSortOrder = false);
$expectedSql = ' SELECT sub.* FROM
(
SELECT log_inner.*
FROM (
SELECT log_visit.*
FROM ' . Common::prefixTable('log_visit') . ' AS log_visit
LEFT JOIN ' . Common::prefixTable('log_link_visit_action') . ' AS log_link_visit_action
ON log_link_visit_action.idvisit = log_visit.idvisit
WHERE ( log_visit.idsite in (?)
AND log_visit.idvisitor = ?
AND log_visit.visit_last_action_time >= ?
AND log_visit.visit_last_action_time <= ? )
AND ( log_link_visit_action.custom_var_k1 = ? )
ORDER BY idsite, visit_last_action_time DESC
LIMIT 100
) AS log_inner
ORDER BY idsite, visit_last_action_time DESC
LIMIT 100
) AS sub
GROUP BY sub.idvisit
ORDER BY sub.visit_last_action_time DESC
';
$expectedBind = array('1', Common::hex2bin('abc'), '2010-01-01 00:00:00', '2010-02-01 00:00:00', 'Test');
$this->assertEquals(SegmentTest::removeExtraWhiteSpaces($expectedSql), SegmentTest::removeExtraWhiteSpaces($sql));
$this->assertEquals(SegmentTest::removeExtraWhiteSpaces($expectedBind), SegmentTest::removeExtraWhiteSpaces($bind));
}
public function getAutomaticMessageReceivedById($idAutoMsg)
{
$arguments = array($this->idsite, @Common::hex2bin($this->idvisitor), $idAutoMsg);
$rows = Db::fetchAll("SELECT * FROM " . Common::prefixTable('chat') . " WHERE idsite = ? AND idvisitor = ? AND idautomsg = ? ORDER BY microtime DESC", $arguments);
$rows = ChatCommon::formatRows($rows);
return $rows;
}
/**
* Is the request for a known VisitorId, based on 1st party, 3rd party (optional) cookies or Tracking API forced Visitor ID
* @throws Exception
*/
public function getVisitorId()
{
$found = false;
// Was a Visitor ID "forced" (@see Tracking API setVisitorId()) for this request?
$idVisitor = $this->getForcedVisitorId();
if (!empty($idVisitor)) {
if (strlen($idVisitor) != Tracker::LENGTH_HEX_ID_STRING) {
throw new Exception("Visitor ID (cid) {$idVisitor} must be " . Tracker::LENGTH_HEX_ID_STRING . " characters long");
}
Common::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) {
$cookie = $this->makeThirdPartyCookie();
$idVisitor = $cookie->get(0);
if ($idVisitor !== false && strlen($idVisitor) == 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 = Common::getRequestVar('_id', '', 'string', $this->params);
$found = strlen($idVisitor) >= Tracker::LENGTH_HEX_ID_STRING;
}
if ($found) {
$truncated = substr($idVisitor, 0, Tracker::LENGTH_HEX_ID_STRING);
$binVisitorId = @Common::hex2bin($truncated);
if (!empty($binVisitorId)) {
return $binVisitorId;
}
}
return false;
}
/**
* @param $visitor
* @param $valuesToUpdate
* @return mixed
*/
private function setIdVisitorForExistingVisit($valuesToUpdate)
{
// Might update the idvisitor when it was forced or overwritten for this visit
if (strlen($this->visitProperties->getProperty('idvisitor')) == Tracker::LENGTH_BINARY_ID) {
$binIdVisitor = $this->visitProperties->getProperty('idvisitor');
$valuesToUpdate['idvisitor'] = $binIdVisitor;
}
// User ID takes precedence and overwrites idvisitor value
$userId = $this->request->getForcedUserId();
if ($userId) {
$userIdHash = $this->request->getUserIdHashed($userId);
$binIdVisitor = Common::hex2bin($userIdHash);
$this->visitProperties->setProperty('idvisitor', $binIdVisitor);
$valuesToUpdate['idvisitor'] = $binIdVisitor;
}
return $valuesToUpdate;
}
public function getAdjacentVisitorId($idSite, $visitorId, $visitLastActionTime, $segment, $getNext)
{
$visitorId = $this->adjacentVisitorId($idSite, @Common::hex2bin($visitorId), $visitLastActionTime, $segment, $getNext);
if (!empty($visitorId)) {
$visitorId = bin2hex($visitorId);
}
return $visitorId;
}
private function loadLastVisitorDetailsFromDatabase($idSite, $period, $date, $segment = false, $countVisitorsToFetch = 100, $visitorId = false, $minTimestamp = false, $filterSortOrder = false)
{
$where = $whereBind = array();
list($whereClause, $idSites) = $this->getIdSitesWhereClause($idSite);
$where[] = $whereClause;
$whereBind = $idSites;
if (strtolower($filterSortOrder) !== 'asc') {
$filterSortOrder = 'DESC';
}
$orderBy = "idsite, visit_last_action_time " . $filterSortOrder;
$orderByParent = "sub.visit_last_action_time " . $filterSortOrder;
if (!empty($visitorId)) {
$where[] = "log_visit.idvisitor = ? ";
$whereBind[] = @Common::hex2bin($visitorId);
}
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($countVisitorsToFetch) && empty($period) && empty($date)) {
$period = 'day';
$date = 'yesterdaySameTime';
}
// SQL Filter with provided period
if (!empty($period) && !empty($date)) {
$currentSite = new Site($idSite);
$currentTimezone = $currentSite->getTimezone();
$dateString = $date;
if ($period == 'range') {
$processedPeriod = new Range('range', $date);
if ($parsedDate = Range::parseDateRange($date)) {
$dateString = $parsedDate[2];
}
} else {
$processedDate = Date::factory($date);
if ($date == 'today' || $date == 'now' || $processedDate->toString() == Date::factory('now', $currentTimezone)->toString()) {
$processedDate = $processedDate->subDay(1);
}
$processedPeriod = Period\Factory::build($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 && Date::factory($dateString)->toString('Y-m-d') != 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;
}
}
if (count($where) > 0) {
$where = join("\n\t\t\t\tAND ", $where);
} else {
$where = false;
}
$segment = new Segment($segment, $idSite);
// Subquery to use the indexes for ORDER BY
$select = "log_visit.*";
$from = "log_visit";
$subQuery = $segment->getSelectQuery($select, $from, $where, $whereBind, $orderBy);
$sqlLimit = $countVisitorsToFetch >= 1 ? " LIMIT 0, " . (int) $countVisitorsToFetch : "";
// Group by idvisit so that a visitor converting 2 goals only appears once
$sql = "\n\t\t\tSELECT sub.* FROM (\n\t\t\t\t" . $subQuery['sql'] . "\n\t\t\t\t{$sqlLimit}\n\t\t\t) AS sub\n\t\t\tGROUP BY sub.idvisit\n\t\t\tORDER BY {$orderByParent}\n\t\t";
try {
$data = Db::fetchAll($sql, $subQuery['bind']);
} catch (Exception $e) {
echo $e->getMessage();
exit;
}
$dataTable = new DataTable();
$dataTable->addRowsFromSimpleArray($data);
// $dataTable->disableFilter('Truncate');
if (!empty($data[0])) {
$columnsToNotAggregate = array_map(function () {
return 'skip';
}, $data[0]);
$dataTable->setMetadata(DataTable::COLUMN_AGGREGATION_OPS_METADATA_NAME, $columnsToNotAggregate);
}
return $dataTable;
}
/**
* Returns visitor cookie
*
* @return string binary
*/
protected function getVisitorIdcookie()
{
if ($this->isVisitorKnown()) {
return $this->visitorInfo['idvisitor'];
}
// If the visitor had a first party ID cookie, then we use this value
if (!empty($this->visitorInfo['idvisitor']) && strlen($this->visitorInfo['idvisitor']) == Tracker::LENGTH_BINARY_ID) {
return $this->visitorInfo['idvisitor'];
}
return Common::hex2bin($this->generateUniqueVisitorId());
}
/**
* @param string $whereClause
* @param array $bindIdSites
* @param $idSite
* @param $period
* @param $date
* @param $visitorId
* @param $minTimestamp
* @return array
* @throws Exception
*/
private function getWhereClauseAndBind($whereClause, $bindIdSites, $idSite, $period, $date, $visitorId, $minTimestamp)
{
$where = array();
$where[] = $whereClause;
$whereBind = $bindIdSites;
if (!empty($visitorId)) {
$where[] = "log_visit.idvisitor = ? ";
$whereBind[] = @Common::hex2bin($visitorId);
}
if (!empty($minTimestamp)) {
$where[] = "log_visit.visit_last_action_time > ? ";
$whereBind[] = date("Y-m-d H:i:s", $minTimestamp);
}
// SQL Filter with provided period
if (!empty($period) && !empty($date)) {
$currentSite = $this->makeSite($idSite);
$currentTimezone = $currentSite->getTimezone();
$dateString = $date;
if ($period == 'range') {
$processedPeriod = new Range('range', $date);
if ($parsedDate = Range::parseDateRange($date)) {
$dateString = $parsedDate[2];
}
} else {
$processedDate = Date::factory($date);
$processedPeriod = Period\Factory::build($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 && Date::factory($dateString)->toString('Y-m-d') != 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;
}
}
if (count($where) > 0) {
$where = join("\n\t\t\t\tAND ", $where);
} else {
$where = false;
}
return array($whereBind, $where);
}
private function getDummyVisitorId()
{
return Common::hex2bin('ea95f303f2165aa0');
}
