<html>
<head>
<title>test raggruppa anni</title>
</head>
<body class="no-skin">
<?php
testQuery();
?>
</body>
</html>
<?php
function testQuery(){
$dsn = 'mysql:dbname=test; host=localhost';
$user = 'root';
$password = '';
try {
$db = new PDO($dsn, $user, $password);
}
catch(PDOException $e) {
echo 'Connessione fallita: '.$e->getMessage();
}
$time_start = microtime_float();
$sql="
select * from (
SELECT gruppo, 9999 as anno, sum(importo) as importo from tabpivot group by gruppo
union
SELECT gruppo, year(data) as anno, sum(importo) as importo from tabpivot group by gruppo, year(data)
union
SELECT distinct t1.gruppo, year(t2.data)as anno, 0 as importo FROM tabpivot t1, tabpivot t2
where not exists (select 1 FROM tabpivot where gruppo=t1.gruppo and year(data)=year(t2.data))
) x order by x.gruppo, x.anno desc
";
$nrighe = 0;
$tabella = "";
$titolo = "<tr><td>GRUPPO</td>"."<td>TOTALE</td>";
$sth = $db->query($sql);
$sth->setFetchMode( PDO::FETCH_ASSOC );
while( $row = $sth->fetch() )
{
if ( $row['anno'] == 9999 )
{
$nrighe += 1;
if ( $nrighe > 1 )
{
$tabella .= "</tr>";
$tr = false;
}
$tabella .= "<tr><td>".$row['gruppo']."</td>";
$tr = true;
$TotAnno = $row['importo'];
}
else
{
if ( $nrighe == 1 ) $titolo .= "<td>".$row['anno']."</td>";
}
$importo = ( $row['importo'] == 0 ? ' ' : number_format($row['importo'], 2, ",", ".") );
$tabella .= "<td>".$importo."</td>";
}
$titolo .= "</tr>";
if ( $tr === true ) $tabella .= "</tr>";
$time_end = microtime_float();
echo "
<style type='text/css'>
* { padding:2px 3px 2px 3px; }
td { BORDER:#000 1px solid; text-align:center; font-size: 16px; }
</style>
<h1>soluzione query</h1> <br />
<table border=2 cellpadding=4>
".$titolo.$tabella."
</table>
<p> </p>
";
$time = $time_end - $time_start;
echo "time_start : ".$time_start."<br />time_end : ".$time_end."<br />Did it, in ".$time." seconds<br />";
}
function testArray(){
$dsn = 'mysql:dbname=test; host=localhost';
$user = 'root';
$password = '';
try {
$db = new PDO($dsn, $user, $password);
}
catch(PDOException $e) {
echo 'Connessione fallita: '.$e->getMessage();
}
$time_start = microtime_float();
$ANNI['TOTALE'] = 0;
$sql ="select distinct year(data) as anno from tabpivot order by year(data) desc";
$sth = $db->query($sql); $sth->setFetchMode( PDO::FETCH_ASSOC );
while( $row = $sth->fetch() ) $ANNI[$row['anno']] = 0;
$IMPORTI = array();
$sql ="select distinct gruppo from tabpivot order by gruppo";
$sth = $db->query($sql); $sth->setFetchMode( PDO::FETCH_ASSOC );
while( $row = $sth->fetch() ) $IMPORTI[$row['gruppo']] = $ANNI;
$sql ="select gruppo, year(data) as anno, importo from tabpivot";
$sth = $db->query($sql); $sth->setFetchMode( PDO::FETCH_ASSOC );
while( $row = $sth->fetch() ) $IMPORTI[$row['gruppo']][$row['anno']] += $row['importo'];
foreach( $IMPORTI as $key => $VALORI ) $IMPORTI[$key]['TOTALE'] = array_sum($VALORI);
$titolo = "<tr>"."<td>GRUPPO</td>";
foreach( $ANNI as $key => $value ) $titolo .= "<td>".$key."</td>";
$titolo .= "</tr>";
$tabella = "";
foreach( $IMPORTI as $key => $VALORI ) {
$tabella .= "<tr>"."<td>".$key."</td>";
foreach( $VALORI as $key => $value ) {
$importo = ( $value == 0 ? ' ' : number_format($value, 2, ",", ".") );
$tabella .= "<td>".$importo."</td>";
}
$tabella .= "</tr>";
}
$time_end = microtime_float();
echo "
<style type='text/css'>
* { padding:2px 3px 2px 3px; }
td { BORDER:#000 1px solid; text-align:center; font-size: 16px; }
</style>
<h1>soluzione con array</h1> <br />
<table border=2 cellpadding=4>
".$titolo.$tabella."
</table>
<p> </p>
";
$time = $time_end - $time_start;
echo "time_start : ".$time_start."<br />time_end : ".$time_end."<br />Did it, in ".$time." seconds<br />";
}
function microtime_float()
{
list($usec, $sec) = explode(" ", microtime());
return ((float)$usec + (float)$sec);
}
?>