Statistics All

<?php
// statistics.php

$client_list = "";

$clients = $db->getAll("SELECT * FROM `".CLIENT_TABLE."` ORDER BY ?n DESC", 'clientID');

$years = array("2010", "2011", "2012", "2013", "2014");

foreach($clients as $row) {
    $clientname = $row->clientname;
    $clientID   = $row->clientID;
    $status     = $row->status;
    if($status == 1) {$status = "tick.gif"; }
    else { $status = "cross.gif"; }

    $client_list .= "
    <tr>
        <td>".$clientID."</td>
        <td><img src=\"images/".$status."\" alt=\"".$status."\" /></td>
        <td><a href=\"index.php?page=Project&amp;do=View&amp;sortby=$clientID\">".$clientname."</a></td>";

    foreach($years as $year) {
        $client_list .= "<td>";

        // collect statistics by project type, e.g. translation, proofreading, interpreting etc.
        $stat_by_type[$year] = $db->getAll("SELECT type,
                        COUNT(projectID) as count
                        FROM `".PROJECT_TABLE."`
                        WHERE clientID = ?i AND duedate LIKE '%$year%'
                        GROUP BY type", $clientID);

        foreach($stat_by_type[$year] as $prow) {
            $client_list .= $prow->count." ".$prow->type."<br />";
        }


        // collect statistics by project units, e.g. words, pages, hours
        $stat_by_units[$year] = $db->getAll("SELECT *,
                        SUM(units) AS units_sum
                        FROM `".PROJECT_TABLE."`
                        WHERE clientID = ?i AND duedate LIKE '%$year%'
                        GROUP BY unit", $clientID);

        foreach($stat_by_units[$year] as $urow) {
            $client_list .= $urow->units_sum." ".$urow->unit."<br />";
        }

        // collect statistics by income per client
        $stat_by_income[$year] = $db->getAll("SELECT *,
                        SUM(totalprice) AS client_sum
                        FROM `".PROJECT_TABLE."`
                        WHERE clientID = ?i AND duedate LIKE '%$year%'
                        GROUP BY currency", $clientID);

        foreach($stat_by_income[$year] as $irow) {
            $client_list .= $irow->client_sum." ".$irow->currency."<br />";
        }

        $client_list .= "</td>";
    }
    $client_list .= "</tr>";
}
?>
<div id="list">
<h3><?php echo $label['projectstats']; ?></h3>
<table>
<tr><td colspan="8" class="subheader"></td></tr>
<tr>
    <th>ID</th>
    <th>Status</th>
    <th>Client</th>
    <th>2010</th>
    <th>2011</th>
    <th>2012</th>
    <th>2013</th>
    <th>2014</th>
</tr>
<?php echo $client_list; ?>
</table>
</div>