<?php

$sqlitedb = '/tmp/places.sqlite.copy';
# moved to make sure permissions are not the problem

$top_request = 'SELECT url,title,visit_count FROM moz_places ORDER BY visit_count DESC LIMIT 20';

$getvisited = 'SELECT url,title,datetime(visit_date/1000000, "unixepoch","localtime") as moment FROM moz_historyvisits, moz_places WHERE moz_historyvisits.place_id = moz_places.id';

$countedvisited = 'SELECT count (*) as counted FROM moz_historyvisits, moz_places WHERE moz_historyvisits.place_id = moz_places.id';

$filterentertainment = ' AND (url like "%quicksilverscreen.im%" OR url like "%watchseries-online.com%" OR url like "%youtube.com%" OR url like "otakucenter")'
$filterwork = ' AND (url like "%fabien.benetou.fr%" OR url like "%127.0.0.1%" OR url like "%seedea.org%" OR url like "%agi-wiki.org%" OR url like "%ourp.im%")';
$filtersocial = ' AND (url like "%twitter%" OR url like "%facebook.com%" OR url like "%mail.google%")';

$req_total = $getvisited;
$req_entertainment = $getvisited.$filterentertainment;
$req_work = $getvisited.$filterwork;
$req_social = $getvisited.$filtersocial;

$req_count_total = $countedvisited;
$req_count_entertainment = $countedvisited.$filterentertainment;
$req_count_work = $countedvisited.$filterwork;
$req_count_social = $countedvisited.$filtersocial;

try {
	/*** connect to SQLite database ***/
	$dbh = new PDO("sqlite:".$sqlitedb);

	foreach ($dbh->query($req_count_total) as $res)
		{ $total = $res['counted']; }

	foreach ($dbh->query($req_count_entertainment) as $res)
		{ $entertainment = $res['counted']; }
	foreach ($dbh->query($req_count_work) as $res)
		{ $work = $res['counted']; }
	foreach ($dbh->query($req_count_social) as $res)
		{ $social = $res['counted']; }

	$first_request = "[date unkown]";
	# to define by getting the date of the first request
	print "<h1>Browsing since $first_request</h1>\n";

	$rest = $total - $entertainment + $social + $work;
	print "<hr/><h2>Distribution</h2> $total = entertainment ($entertainment) + social ($social) + work ($work) + the rest.\n";
	print "<img src=\"http://sparklines.bitworking.info/spark.cgi?type=impulse&d=$total,$entertainment,$social,$work&limits=0,$total&height=50&upper=0&above-color=green&below-color=red&width=10\" /><br/>";

	if ( $rest > ($total / 4) )
		print "Warning: more than a quarter of the total are <font color=\"red\">not</font> classified, patterns should be improved.<br/>\n";

	print "<hr/><h2>Top 20 websites</h2>\n";

	print "<table>\n";
	print "<tr><th>Visits</th><th>Title (if missing, URL)<th/></tr>\n";
	foreach ($dbh->query($top_request) as $row) {
		if ($row['title']=="")
			print "<tr><td>".$row['visit_count']."</td><td><a href=\"".$row['url']."\">".$row['url']."</a><td/></tr>\n";
		else
			print "<tr><td>".$row['visit_count']."</td><td><a href=\"".$row['url']."\">".$row['title']."</a><td/></tr>\n";
	}
	print "</table>\n";

	/*** close the database connection ***/
	$dbh = null;
}
catch(PDOException $e) {
    echo $e->getMessage();
}

?>