// save browser id
$bname = db_select(TBL_PREFIX . TBL_BROWSERS, "id", "name='" . $browser->getBrowser() . "'");
if (!$bname) {
$browserid = db_insert(TBL_PREFIX . TBL_BROWSERS, "name", "'" . $browser->getBrowser() . "'");
} else {
$browserid = $bname['id'];
}
// save OS id
$osname = db_select(TBL_PREFIX . TBL_OS, "id", "name='" . $browser->getPlatform() . "'");
if (!$osname) {
$osid = db_insert(TBL_PREFIX . TBL_OS, "name", "'" . $browser->getPlatform() . "'");
} else {
$osid = $osname['id'];
}
// save domain id
$domain = url_get_domain($URL);
$d = db_select(TBL_PREFIX . TBL_DOMAINS, "id", "domain='" . $domain . "'");
if (!$d) {
$did = db_insert(TBL_PREFIX . TBL_DOMAINS, "domain", "'" . $domain . "'");
} else {
$did = $d['id'];
}
/* create database entry ---------------------------------------------------- */
$fields = "client_id,cache_id,domain_id,os_id,browser_id,browser_ver,user_agent,";
$fields .= "ftu,ip,scr_width,scr_height,vp_width,vp_height,";
$fields .= "sess_date,sess_time,fps,coords_x,coords_y,clicks,hovered,clicked";
$values = "'" . $_POST['client'] . "',";
$values .= "'" . $cache_id . "',";
$values .= "'" . $did . "',";
$values .= "'" . $osid . "',";
$values .= "'" . $browserid . "',";
// domain name
$sql .= 'PRIMARY KEY (`id`) ';
$sql .= ') DEFAULT CHARSET utf8';
db_query($sql);
$UPGRADED = true;
}
// check if domains should be updated
$res = db_query("SHOW COLUMNS FROM " . TBL_PREFIX . TBL_RECORDS . " LIKE 'domain_id'");
if (!mysql_num_rows($res)) {
// create new column
$sql = "ALTER TABLE `" . TBL_PREFIX . TBL_RECORDS . "` ADD `domain_id` SMALLINT unsigned NOT NULL AFTER `cache_id`";
db_query($sql);
// and update old DB records with the new values
$pages = db_select_all(TBL_PREFIX . TBL_CACHE, "id,url", "1");
foreach ($pages as $page) {
$domain = url_get_domain($page['url']);
$d = db_select(TBL_PREFIX . TBL_DOMAINS, "id", "domain='" . $domain . "'");
if (!$d) {
$did = db_insert(TBL_PREFIX . TBL_DOMAINS, "domain", "'" . $domain . "'");
} else {
$did = $d['id'];
}
db_update(TBL_PREFIX . TBL_RECORDS, "domain_id='" . $did . "'", "cache_id='" . $page['id'] . "'");
}
$UPGRADED = true;
}
// define helper function
function update_cms($table, $fields, $values, $condition)
{
global $UPGRADED;
// was upgraded before?
// display a start on first time visitors
$ftu = $r['ftu'] ? ' class="ftu"' : null;
$abbrDate = date('Y/m/d', strtotime($r['sess_date']));
// use pretty date?
$displayDate = $usePrettyDate ? '<abbr title="' . prettyDate::getStringResolved($r['sess_date']) . '">' . $abbrDate . '</abbr>' : $abbrDate;
$browsingTime = $r['sess_time'];
//$locationId = mask_client(md5($r['ip']));
$lang = $browser->getLanguage();
if ($lang != $browser::LANGUAGE_UNKNOWN) {
$locationId = '<img src="styles/blank.gif" class="flag flag-' . $lang . '" alt="' . $lang . '" title="' . $lang . '" />';
} else {
$locationId = "?";
}
$displayId = 'id=' . $r['id'];
$pageId = $r['cache_id'];
$clientId = mask_client($r['client_id']);
$interactionTime = round(count(explode(",", $r['coords_x'])) / $r['fps'], 2);
$numClicks = count_clicks($r['clicks']);
$notes = db_select(TBL_PREFIX . TBL_HYPERNOTES, "count(*) as num", "record_id='" . $r['id'] . "'");
$numNotes = $notes['num'] > 0 ? '<a href="./hypernotes/list.php?id=' . $r['id'] . '">' . $notes['num'] . '</a>' : $notes['num'];
}
$cache = db_select(TBL_PREFIX . TBL_CACHE, "url", "id='" . $pageId . "'");
$domain = url_get_domain($cache['url']);
$replayUrl = TRACKING_SERVER . "admin/ext/logs/track.php?" . $displayId;
$deleteUrl = TRACKING_SERVER . "admin/ext/logs/delete.php?" . $displayId;
$item = ["ftu" => $ftu, "clientId" => $clientId, "locationId" => $locationId, "pageId" => $pageId, "domain" => $domain, "domain_id" => $r['domain_id'], "cache" => $cache, "recordData" => $r, "displayDate" => $displayDate, "interactionTime" => $interactionTime, "numClicks" => $numClicks, "numNotes" => $numNotes, "replayUrl" => $replayUrl, "deleteUrl" => $deleteUrl];
$items[] = $item;
}
}
header('Content-Type: application/json');
echo json_encode(array('rows' => $items));
