public static function WriteStats($p_type, $p_specifier)
{
// The main input info is object_type (e.g. article) and object_id (unique specifier)
// as for now, it shall be artycle_type and object_id of an article
// Note that the p_specifier shall be unique within all the type domains,
// while the current state is that the object_ids are taken unique just within articles.
// We shall think on it if trying to put other types (e.g. sections, issues) into stats.
$object_type_id = 0 + $p_type;
$object_id = 0 + $p_specifier;
// taking db access info
global $Campsite;
if (empty($Campsite)) {
$Campsite = array('db' => array());
}
$newscoop_path = dirname(dirname(__FILE__));
require_once $newscoop_path . '/conf/database_conf.php';
$dbAccess = $Campsite['db'];
$db_host = $dbAccess['host'];
$db_port = $dbAccess['port'];
$db_user = $dbAccess['user'];
$db_pwd = $dbAccess['pass'];
$db_name = $dbAccess['name'];
$session_name = session_name();
if (session_id() == '') {
session_start($session_name);
$session_id = session_id();
} else {
$session_id = session_id();
}
// sql commands used along the workflow
$sqlSessionSel = 'SELECT user_id FROM Sessions WHERE id = :session_id';
$sqlSessionIns = 'INSERT INTO Sessions (id, start_time) VALUES (:session_id, :start_time)';
$sqlRequestObjectSel = 'SELECT object_id FROM RequestObjects WHERE object_id = :object_id';
$sqlRequestObjectIns = 'INSERT INTO RequestObjects (object_id, object_type_id) VALUES (:object_id, :object_type_id)';
$sqlRequestObjectUpd = 'UPDATE RequestObjects SET request_count = (request_count + 1) WHERE object_id = :object_id';
$sqlRequestSel = 'SELECT last_stats_update FROM Requests WHERE (session_id, object_id) = (:session_id, :object_id) LIMIT 1';
$sqlRequestIns = 'INSERT INTO Requests (session_id, object_id, last_stats_update) VALUES (:session_id, :object_id, :last_stats_update)';
// it looks that this table was used just for having daily/hourly statistics,
// but can not see where it is used now; was it finally abandoned?
$sqlRequestStatSel = 'SELECT request_count FROM RequestStats WHERE (object_id, date, hour) = (:object_id, :date, :hour)';
$sqlRequestStatIns = 'INSERT INTO RequestStats (object_id, date, hour, request_count) VALUES (:object_id, :date, :hour, 1)';
$sqlRequestStatUpd = 'UPDATE RequestStats SET request_count = (request_count + 1) WHERE (object_id, date, hour) = (:object_id, :date, :hour)';
// openning the db connection
$dbh = null;
$sth = null;
try {
$dbh = new PDO("mysql:host={$db_host};port={$db_port};dbname={$db_name}", "{$db_user}", "{$db_pwd}", array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'));
} catch (Exception $exc) {
return false;
}
// the old processing read/created session info first
// it looks to me that we do not need this here, may be to abandon this at a refactoring
try {
$user_id = null;
$session_exists = false;
$sth = $dbh->prepare($sqlSessionSel);
$sth->bindValue(':session_id', (string) $session_id, PDO::PARAM_STR);
$res = $sth->execute();
if (!$res) {
return false;
}
while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
$user_id = $row['user_id'];
$session_exists = true;
}
$sth = null;
if (!$session_exists) {
$sth = $dbh->prepare($sqlSessionIns);
$sth->bindValue(':session_id', (string) $session_id, PDO::PARAM_STR);
$sth->bindValue(':start_time', (string) strftime("%Y-%m-%d %T"), PDO::PARAM_STR);
$res = $sth->execute();
if (!$res) {
return false;
}
$sth = null;
}
} catch (Exception $exc) {
return false;
}
// we read the stats info from RequestObjects and create empty one, if none there
// note that RequestObjects and RequestStats contain apparently duplicated info,
// it shall be rationalized at some refactoring
try {
$request_object_exists = false;
$sth = $dbh->prepare($sqlRequestObjectSel);
$sth->bindValue(':object_id', (string) $object_id, PDO::PARAM_INT);
$res = $sth->execute();
if (!$res) {
return false;
}
while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
$request_object_exists = true;
}
$sth = null;
if (!$request_object_exists) {
$sth = $dbh->prepare($sqlRequestObjectIns);
$sth->bindValue(':object_id', (string) $object_id, PDO::PARAM_INT);
$sth->bindValue(':object_type_id', (string) $object_type_id, PDO::PARAM_INT);
$res = $sth->execute();
if (!$res) {
return false;
}
$sth = null;
}
} catch (Exception $exc) {
return false;
}
// does the user already read this article
// in a distant past the page-reads were taken per user (i.e. session_id) and hour,
// for the current (and past) workflow a user (i.e. seesion_id) can be taken just once,
// i.e. either the row (on session_id and object_id) does exists or not at all
$last_stats_update = null;
try {
$sth = $dbh->prepare($sqlRequestSel);
$sth->bindValue(':session_id', (string) $session_id, PDO::PARAM_STR);
$sth->bindValue(':object_id', (string) $object_id, PDO::PARAM_INT);
$res = $sth->execute();
if (!$res) {
return false;
}
while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
$last_stats_update = $row['last_stats_update'];
}
$sth = null;
} catch (Exception $exc) {
return false;
}
// stop here if already read that
if (!empty($last_stats_update)) {
return true;
}
$last_stats_update = date('Y-m-d G:i:s');
$current_date = date('Y-m-d');
$current_hour = 0 + date('H');
// save that the user has read the article
try {
$sth = $dbh->prepare($sqlRequestIns);
$sth->bindValue(':session_id', (string) $session_id, PDO::PARAM_STR);
$sth->bindValue(':object_id', (string) $object_id, PDO::PARAM_INT);
$sth->bindValue(':last_stats_update', (string) $last_stats_update, PDO::PARAM_STR);
$res = $sth->execute();
if (!$res) {
return false;
}
$sth = null;
} catch (Exception $exc) {
return false;
}
$request_count_update = false;
// how many has read this article this hour
try {
$sth = $dbh->prepare($sqlRequestStatSel);
$sth->bindValue(':object_id', (string) $object_id, PDO::PARAM_INT);
$sth->bindValue(':date', (string) $current_date, PDO::PARAM_STR);
$sth->bindValue(':hour', (string) $current_hour, PDO::PARAM_INT);
$res = $sth->execute();
if (!$res) {
return false;
}
while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
$request_count_update = true;
}
$sth = null;
} catch (Exception $exc) {
return false;
}
// save the (increased) count of read access
// both into RequestStats and RequestObjects
try {
if ($request_count_update) {
$sth = $dbh->prepare($sqlRequestStatUpd);
} else {
$sth = $dbh->prepare($sqlRequestStatIns);
}
$sth->bindValue(':object_id', (string) $object_id, PDO::PARAM_INT);
$sth->bindValue(':date', (string) $current_date, PDO::PARAM_STR);
$sth->bindValue(':hour', (string) $current_hour, PDO::PARAM_INT);
$res = $sth->execute();
if (!$res) {
return false;
}
$sth = null;
$sth = $dbh->prepare($sqlRequestObjectUpd);
$sth->bindValue(':object_id', (string) $object_id, PDO::PARAM_INT);
$res = $sth->execute();
if (!$res) {
return false;
}
$sth = null;
} catch (Exception $exc) {
return false;
}
return true;
}
