Online
<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\"> </td></tr>"; } echo "</table>"; } } echo "</fieldset>"; }
} else
echo "<center><h2>Nėra pranešimų</h2></center>";
echo “<p>”; validation issues </php>