gitphp 0.2.9.1 :: contractdashboard.git/blobdiff

<?php

include_once("./lib/common.inc.php");

include_header("Months and Years");

if ($_REQUEST['category']) {

echo "<center><h1>".$_REQUEST['category']."</h1></center>";

if ($_REQUEST['month']) {

$query = "SELECT CNID, description, value, agencyName, category, contractStart, supplierName

$monthParts = explode("-", str_replace(Array("'","/","\\"), "",$_REQUEST['month']));

FROM `contractnotice`

$year = str_replace('-', '',$monthParts[1]);

WHERE childCN = 0

$month = str_replace('-', '',$monthParts[0]);

AND category = '" . $_REQUEST['category'] . "'

$monthName = date("F Y", mktime(0,0,0,$month,1,$year));

ORDER BY value DESC";

$result = mysql_query($query);

include_header($monthName);

echo "<table>";

echo "<center><h1>" . $monthName . "</h1></center>";

while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {

setlocale(LC_MONETARY, 'en_US');

$query = 'SELECT "CNID", "description", "value", "agencyName", "category", "contractStart", "supplierName"

$value = number_format(doubleval($row['value']) , 2);

FROM contractnotice

echo ("<tr>

WHERE "childCN" is null

AND extract(year from "contractStart") = :year

<td><b>{$row['description']}</b></a></td>

AND extract(month from "contractStart") = :month

<td>\$$value</td><td>{$row['agencyName']}</td>

ORDER BY value DESC';

<td>{$row['contractStart']}</td>

$query = $conn->prepare($query);

<td>{$row['supplierName']}</td>

$query->bindParam(":month", $monthParts[0]);

</tr>");

}

$query->bindParam(":year", $monthParts[1]);

echo "</table>";

$query->execute();

mysql_free_result($result);

databaseError($conn->errorInfo());

} else {

/*

//MethodCountGraph($supplier);

split by year

//CnCGraph($supplier);

todo:

Year/Month drilldown - largest contracts, agencies, suppliers

echo "<table> <thead>

big picture graphs?*/

<tr>

<th>Contract Notice Number</th>

<th>Contract Description</th>

$query = "SELECT YEAR(contractStart), MONTH(contractStart),

<th>Total Contract Value</th>

value FROM `contractnotice` WHERE childCN = 0 GROUP BY MONTH(contractStart), YEAR(contractStart) ORDER BY YEAR(contractStart), MONTH(contractStart) ";

<th>Agency</th>

<th>Contract Start Date</th>

$result = mysql_query($query);

<th>Supplier</th>

echo "<table>";

</tr>

while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {

</thead>";

setlocale(LC_MONETARY, 'en_US');

foreach ($query->fetchAll() as $row) {

$value = number_format(doubleval($row["value"]),2);

setlocale(LC_MONETARY, 'en_US');

$month_name = date( 'F', mktime(0, 0, 0, $row[1]) );

$value = number_format(doubleval($row['value']), 2);

echo ("<tr><td><b>$month_name {$row[0]}</b></td><td>\$$value</td></tr>");

echo ("<tr>

}

echo "</table>";

<td><b>{$row['description']}</b></a></td>

mysql_free_result($result);

<td>\$$value</td><td>{$row['agencyName']}</td>

}

<td>{$row['contractStart']}</td>

include_footer();

<td>{$row['supplierName']}</td>

</tr>");

}

echo "</table>";

} else {

/*

split by year

todo:

Year/Month drilldown - largest contracts, agencies, suppliers

count per month

big picture graphs? */

include_header("Months and Years");

ContractStartingGraph();

ContractPublishedGraph();

$query = 'SELECT extract(year from "contractStart"), extract(month from "contractStart"),

SUM(value) as val, count(1) as count FROM contractnotice WHERE "childCN" is null GROUP BY extract(year from "contractStart"), extract(month from "contractStart") ORDER BY extract(year from "contractStart"), extract(month from "contractStart") ';

$query = $conn->prepare($query);

$query->execute();

databaseError($conn->errorInfo());

echo "<table> <thead>

<tr>

<th>Month/Year</th>

<th>Total Contracts Value</th>

<th>Number of Contracts</th>

</tr>

</thead>";

foreach ($query->fetchAll() as $row) {

setlocale(LC_MONETARY, 'en_US');

$value = number_format(doubleval($row["val"]), 2);

$month_name = date('F', mktime(0, 0, 0, $row[1]));

echo ('<tr><td><b><a href="?month='.$row[1].'-'.$row[0].'">'.$month_name.' '.$row[0].'</a></b></td><td>$'.$value.'</td><td>(' .$row['count'].' contracts)</td></tr>');

}

echo "</table>";

}

include_footer();