~~NOCACHE~~
====== Online ======
{{.:icon.png?nolink&40 |}}\\
\\
<php>
echo "</p>"; //validation issues

include("./spot-in/core/web_db_conf.php");

try{ $DBH = new PDO("mysql:host=$dbServer;dbname=$dbName", $dbUser, $dbPass); }
catch(PDOException $e){ echo $e->getMessage(); }
$DBH->query("SET NAMES 'utf8'");
$DBH->query("SET lc_time_names = 'lt_LT'");
$sql = " SELECT 
  rep.id AS id
  ,sta.station AS station
  ,dis.district AS district
  ,city.city AS city
  ,rep.comments AS com
  ,CONCAT(DATE_FORMAT(rep.time, '%W, '), UCASE(MID(DATE_FORMAT(rep.time, '%M'),1,1)), MID(DATE_FORMAT(rep.time, '%M '),2), DATE_FORMAT(rep.time, '%d') ) AS date
  ,TIME_FORMAT(rep.time, '%H:%i') AS time
  ,COALESCE(votes.negVote, 0) as neg
  ,COALESCE(votes.posVote, 0) as pos
  ,rep.mobile as cType
FROM reports AS rep
	JOIN stations as sta 
		ON rep.stationId = sta.id
	JOIN districts AS dis
		ON sta.districtId = dis.id
	JOIN cities AS city
		ON dis.cityId = city.id
		LEFT JOIN (
			select 
			reportId,
			SUM( CASE WHEN vote=1 THEN 1 ELSE 0 END ) as posVote,
			SUM( CASE WHEN vote=0 THEN 1 ELSE 0 END ) as negVote
			FROM verification
			GROUP BY reportid
		) AS votes
			ON votes.reportId = rep.id
LEFT JOIN devices AS dev ON rep.deviceId = dev.id

WHERE rep.time >= DATE_ADD(NOW(), INTERVAL -3 DAY) and deleted = 'No'
GROUP BY rep.id
ORDER BY rep.time DESC;
";
$STH = $DBH->prepare($sql);
$STH->execute();
$curDay = "";

while($row = $STH->fetch(PDO::FETCH_ASSOC) ){
	$rep = array(
		"id"=>		intval($row["id"])
		,"type"=>	intval($row["cType"])
		,"name"=>	$row["station"]
		,"district"=>	$row["district"]." sen., ".$row["city"]
		,"comment"=>	$row["com"]
		,"time"=>	$row["time"]
		,"vote"=>	array("pos"=>$row["pos"], "neg"=>$row["neg"])
//		,"trust"=>	$row["trust"]
	);

	if($curDay != $row["date"]){														//kita diena
		if(!empty($reports))
				$days[] = array("day"=>$curDay, "reports"=>$reports);
		$curDay = $row["date"];
		unset($reports);
	}
	$reports[]=$rep;
}
if(!empty($reports))
	$days[] = array("day"=>$curDay, "reports"=>$reports);

//------------------------------------------------------------------------------
if(is_array($days)){
	foreach($days as $k1 => $v1){
		echo "<fieldset><legend>".$v1['day']."</legend>";
		foreach($v1 as $k2 => $v2){
			if(is_array($v2)){
	//var_dump($v2);
	echo "<table style='border: 0px solid #DDD; width:100%'>";
	//$reps = $v2['reports'];
	foreach ($v2 as $k3 => $rep){
	if($rep['type'] == 1) $ico = "<img src='/_media/project:spot-in:group.png' alt='Group' />";
	else $ico = "<img src='/_media/project:spot-in:bus.png?w=40' alt='Bus' />";

		echo "<tr><td rowspan=\"3\">$ico</td><td><b>". $rep['name'] ."</b></td><td>". $rep['time'] ."</td></tr><tr><td><small>".$rep['district']."</small></td><td><span style=\"color:red\">". $rep['vote']['pos'] ."</span> / <span style=\"color:green\">".$rep['vote']['neg']."</span></td></tr><tr><td colspan=\"2\"><i>".$rep['comment']."</i></td></tr><tr><td colspan=\"3\" style=\"height:2px; bgcolor:gray\">&nbsp;</td></tr>";
	}
	echo "</table>";
	}
		}
		echo "</fieldset>";
	}
}
else 
	echo "<center><h2>Nėra pranešimų</h2></center>";
echo "<p>"; //validation issues
</php>