--- a/lib/common.inc.php +++ b/lib/common.inc.php @@ -1,27 +1,31 @@ <?php date_default_timezone_set("Australia/ACT"); -require_once ('jpgraph/jpgraph.php'); -require_once ('jpgraph/jpgraph_line.php'); -require_once ('jpgraph/jpgraph_log.php'); -require_once ('jpgraph/jpgraph_mgraph.php'); -require_once ('jpgraph/jpgraph_pie.php'); -require_once ('jpgraph/jpgraph_bar.php'); -require_once ('jpgraph/jpgraph_date.php'); - error_reporting(E_ALL ^ E_NOTICE); -$link = mysql_connect('localhost', 'root', ''); -if (!$link) { - die('Could not connect: ' . mysql_error()); -} -@mysql_select_db("contractDashboard") or die("Unable to select database"); +$conn = new PDO("pgsql:dbname=contractDashboard;user=postgres;password=snmc;host=localhost"); + +if (!$conn) { + die("A database error occurred.\n"); +} + +// $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); +function databaseError($errMsg) +{ + die($errMsg); +} function ucsmart($str) { - return preg_replace("/(?<=(?<!:|’s)\W) + $shortWords = Array("The","Pty","Ltd","Inc","Red","Oil","A","An","And","At","For","In" + ,"Of","On","Or","The","To","With"); + $strArray = explode(" ",preg_replace("/(?<=(?<!:|’s)\W) (A|An|And|At|For|In|Of|On|Or|The|To|With) - (?=\W)/e", 'strtolower("$1")', ucwords(strtolower($str))); + (?=\W)/e", 'strtolower("$1")', ucwords(strtolower($str)))); + foreach($strArray as &$word) { + if (strlen($word) <= 4 && !in_array($word,$shortWords)) $word = strtoupper($word); + } + return implode(" ",$strArray); } function percent($num_amount, $num_total) { @@ -37,18 +41,23 @@ return $totale; } // magic query modifiers -$agency = mysql_real_escape_string(stripslashes($_REQUEST['agency'])); +$agency = filter_var($_REQUEST['agency'], FILTER_SANITIZE_STRING); if ($agency != "") $agencyQ = "agencyName = '" . $agency . "' AND "; -$supplier = mysql_real_escape_string(stripslashes($_REQUEST['supplier'])); + +$supplier = filter_var($_REQUEST['supplier'], FILTER_SANITIZE_STRING); if ($supplier != "") { $supplierParts = explode("-", $supplier); - if ($supplierParts[0] > 0) $supplierQ = "supplierABN = '" . $supplierParts[0] . "' AND "; - else $supplierQ = "supplierName LIKE '%" . $supplierParts[1] . "%' AND "; -} + $supplierName = "%".$supplierParts[1]."%"; + $supplierABN = $supplierParts[0]; + if ($supplierParts[0] > 0) $supplierQ = ' "supplierABN" = :supplierABN AND '; + else $supplierQ = ' "supplierName" LIKE :supplierName AND '; +} + $startYear = 2007; -$year = mysql_real_escape_string(stripslashes($_REQUEST['year'])); +$year = filter_var($_REQUEST['year'], FILTER_SANITIZE_NUMBER_INT); if ($year != "") $yearQ = "YEAR(publishDate) = " . $year . " AND "; -$standardQ = "childCN = 0 AND YEAR(contractStart) >= 2007 AND YEAR(contractStart) <= 2010"; + +$standardQ = ' "childCN" = 0 '; // AND YEAR(contractStart) >= 2007 AND YEAR(contractStart) <= 2010'; $start =0.0; function include_header($title) { @@ -58,7 +67,7 @@ "http://www.w3.org/TR/html4/strict.dtd"> <html> <head> - <title>Contract Dashboard - <?=$title?></title> + <title>Contract Dashboard - <?php echo $title; ?></title> <link rel="stylesheet" type="text/css" href="http://yui.yahooapis.com/2.8.0r4/build/reset-fonts-grids/reset-fonts-grids.css"> <script type="text/javascript" src="lib/bsn.AutoSuggest_2.1.3_comp.js" charset="utf-8"></script> @@ -82,6 +91,59 @@ </style> <script type="text/javascript" language="javascript" src="media/js/jquery.dataTables.js"></script> <script type="text/javascript" charset="utf-8"> +jQuery.fn.dataTableExt.aTypes.unshift( + function ( sData ) + { + var sValidChars = "0123456789.-,"; + var Char; + + /* Check the numeric part */ + for ( i=1 ; i<sData.length ; i++ ) + { + Char = sData.charAt(i); + if (sValidChars.indexOf(Char) == -1) + { + return null; + } + } + + /* Check prefixed by currency */ + if ( sData.charAt(0) == '$' || sData.charAt(0) == '£' ) + { + return 'currency'; + } + return null; + } +); +jQuery.fn.dataTableExt.oSort['currency-asc'] = function(a,b) { + /* Remove any commas (assumes that if present all strings will have a fixed number of d.p) */ + var x = a == "-" ? 0 : a.replace( /,/g, "" ); + var y = b == "-" ? 0 : b.replace( /,/g, "" ); + + /* Remove the currency sign */ + x = x.substring( 1 ); + y = y.substring( 1 ); + + /* Parse and return */ + x = parseFloat( x ); + y = parseFloat( y ); + return x - y; +}; + +jQuery.fn.dataTableExt.oSort['currency-desc'] = function(a,b) { + /* Remove any commas (assumes that if present all strings will have a fixed number of d.p) */ + var x = a == "-" ? 0 : a.replace( /,/g, "" ); + var y = b == "-" ? 0 : b.replace( /,/g, "" ); + + /* Remove the currency sign */ + x = x.substring( 1 ); + y = y.substring( 1 ); + + /* Parse and return */ + x = parseFloat( x ); + y = parseFloat( y ); + return y - x; +}; $(document).ready(function() { $('table').dataTable(); } ); @@ -145,5 +207,6 @@ echo '<div id="ft"><p>'."Processing time: ". sprintf("%.4f", ($end-$start))." seconds".'</p></div>'; echo '</div> </body> </html>'; } +include ("graphs.inc.php"); ?>