tweak year/agency modifer for queries
[contractdashboard.git] / lib / common.inc.php
blob:a/lib/common.inc.php -> blob:b/lib/common.inc.php
--- a/lib/common.inc.php
+++ b/lib/common.inc.php
@@ -1,138 +1,317 @@
-<?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");

-

-function ucsmart($str) {

-  return preg_replace("/(?<=(?<!:|’s)\W)

-            (A|An|And|At|For|In|Of|On|Or|The|To|With)

-            (?=\W)/e", 'strtolower("$1")', ucwords(strtolower($str)));

-}

-

-function percent($num_amount, $num_total) {

-  $count1 = $num_amount / $num_total;

-  $count2 = $count1 * 100;

-  $count = number_format($count2, 2);

-  return $count;

-}

- function array_sum_all($a) {

-  if(!is_array($a)) return $a;

-  foreach($a as $key=>$value)

-     $totale += array_sum_all($value);

-  return $totale; 

- }

-// magic query modifiers

-$agency = mysql_real_escape_string(stripslashes($_REQUEST['agency']));

-if ($agency != "") $agencyQ = "agencyName = '" . $agency . "' AND ";

-$supplier = mysql_real_escape_string(stripslashes($_REQUEST['supplier']));

-if ($supplier != "") {

-  $supplierParts = explode("-", $supplier);

-  if ($supplierParts[0] > 0) $supplierQ = "supplierABN = '" . $supplierParts[0] . "' AND ";

-  else $supplierQ = "supplierName LIKE '%" . $supplierParts[1] . "%' AND ";

-}

-$year = mysql_real_escape_string(stripslashes($_REQUEST['year']));

-if ($year != "") $yearQ = "YEAR(publishDate) = " . $year . " AND ";

-

-$start =0.0;

-

-function include_header($title) {

-    global $start;

-?>

-<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"  

-"http://www.w3.org/TR/html4/strict.dtd"> 

-<html>

-    <head>

-        <title>Contract Dashboard - <?=$title?></title>

-        <link rel="stylesheet" type="text/css" href="http://yui.yahooapis.com/2.8.0r4/build/reset-fonts-grids/reset-fonts-grids.css"> 

-        <link type="text/css" rel="stylesheet" href="style.css">

-            <script type="text/javascript" src="lib/bsn.AutoSuggest_2.1.3_comp.js" charset="utf-8"></script>

-

-<link rel="stylesheet" href="autosuggest_inquisitor.css" type="text/css" media="screen" charset="utf-8" />

-            <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js"></script>

-<script type="text/javascript">

-$(document).ready(function()

-{

-  //hide the all of the element with class msg_body

-  $(".msg_body").hide();

-  //toggle the componenet with class msg_body

-  $(".msg_head").click(function()

-  {

-    $(this).next(".msg_body").slideToggle(600);

-  });

-});

-</script>

-        </head>

-<body><div id="doc3" class="yui-t4">

-    <div id="hd">

-        <h1> contract dashboard</h1>

-        </div>

-        <div id="sitenav">

-            <a href="displayAgency.php">agencies</a>

-            <a href="displaySupplier.php">suppliers</a>

-            <a href="displayCategory.php">product/service categories</a>

-            <a href="displayCalendar.php">time periods</a>

-            | metrics |

-            <a href="displayProcurementMethod.php">procurement method</a>

-            <a href="displayConfidentialities.php">contractual confidentiality</a>

-            <a href="displayConsultancies.php">consultancies</a>

-            <a href="displayAmendments.php">amendments</a>

-            <a href="displayMap.php">postcodes</a>

-            <div id="search"><form method="post" action="search.php" class="asholder">

-	<label for="searchKeyword">search</label>

-	<input type="text" id="searchKeyword" name="searchKeyword"  value="" style="width:150px" />

-        <input type="hidden" id="searchID" name="searchID" value=""/><input type="submit" value="submit" />

-</form></div>

-            <script type="text/javascript">

-	

-	var options_xml = {

-		script: function (input) { return "search_autosuggest.php?input="+input; },

-		varname:"input",

-		callback: function (obj) { document.getElementById('searchID').value = obj.id; }

-	};

-	var as_xml = new bsn.AutoSuggest('searchKeyword', options_xml);

-</script>

-

-            </div>

-             <div id="bd" role="main"> 

-    <div id="yui-main"> 

-        <div class="yui-b"><div class="yui-g">

-<?php

-$start = (float) array_sum(explode(' ',microtime())); 

-}

-

-function include_footer() {

-    global $start;

-$end = (float) array_sum(explode(' ',microtime()));

-    ?>

-    </div> 

-</div> 

-    </div> 

-    <div class="yui-b">

-        Filter by:<ul>

-        <li>year

-            <ul><li>2008</li>

-            </ul>

-        </li>

-        </ul> <br>        </div> </div>

-     

-    <?php

-   echo '<div id="ft"><p>'."Processing time: ". sprintf("%.4f", ($end-$start))." seconds".'</p></div>'; 

-   echo '</div> </body> </html>';

-} 

-?>

-
+<?php
+date_default_timezone_set("Australia/ACT");
+
+error_reporting(E_ALL ^ E_NOTICE);
+
+
+$conn = new PDO("pgsql:dbname=contractDashboard;user=postgres;password=snmc;host=localhost");
+
+if (!$conn) {
+    die("A database error occurred.\n");
+}
+
+define('ROOT', pathinfo(__FILE__, PATHINFO_DIRNAME));
+if (strstr($_SERVER['PHP_SELF'], "labs/") || strstr($_SERVER['PHP_SELF'], "admin/") || strstr($_SERVER['PHP_SELF'], "heuristics/")) {
+    $basePath = "../";
+}
+require $basePath."lib/amon-php/amon.php";
+Amon::config(array('address'=> 'http://127.0.0.1:2464', 
+		'protocol' => 'http', 
+		'secret_key' => "JBcSUdFOi5lK0vCjLjbHDpQamcBnRA4iV7QLaTADeDQ"));
+Amon::setup_exception_handler();
+
+require ROOT . DIRECTORY_SEPARATOR . '..' . DIRECTORY_SEPARATOR . 'lib' . DIRECTORY_SEPARATOR . 'openid.php';
+$openid = new LightOpenID($_SERVER['HTTP_HOST']);
+// you have to open the session to be able to modify or remove it
+session_start();
+function login() {
+    global $openid;
+    if (!$openid->mode) {
+        $openid->required = array('contact/email');
+        $openid->identity = 'https://www.google.com/accounts/o8/id';
+        header('Location: ' . $openid->authUrl());
+    }
+}
+
+function auth() {
+    global $openid;
+    if ($_SESSION['authed'] == true) {
+        return true;
+    }
+
+    if ($openid->mode) {
+        $attr = $openid->getAttributes();
+        if ($attr['contact/email'] != 'maxious@gmail.com') {
+            die('Access Denied');
+        } else {
+            $_SESSION['authed'] = true;
+        }
+    } else {
+        login();
+    }
+}
+
+//   $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
+function databaseError($errMsg) {
+    if ($errMsg[2] != "") {
+        echo '<div class="alert-message error">';
+        Amon::log(print_r($errMsg, true).print_r($_REQUEST, true).print_r($_SERVER, true), array('error'));
+        die(print_r($errMsg, true));
+        echo "</div>";
+    }
+}
+
+function ucsmart($str) {
+    $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))));
+    foreach ($strArray as &$word) {
+        if (strlen($word) <= 4 && !in_array($word, $shortWords))
+            $word = strtoupper($word);
+    }
+    return implode(" ", $strArray);
+}
+
+function percent($num_amount, $num_total) {
+    $count1 = $num_amount / $num_total;
+    $count2 = $count1 * 100;
+    $count = number_format($count2, 2);
+    return $count;
+}
+
+function array_sum_all($a) {
+    if (!is_array($a))
+        return $a;
+    foreach ($a as $key => $value)
+        $totale += array_sum_all($value);
+    return $totale;
+}
+
+// magic query modifiers
+$agency = filter_var($_REQUEST['agency'], FILTER_SANITIZE_STRING);
+if ($agency != "")
+    $agencyQ = "agencyName = '" . $agency . "' AND ";
+
+$supplier = filter_var($_REQUEST['supplier'], FILTER_SANITIZE_STRING);
+if ($supplier != "") {
+    $supplierParts = explode("-", $supplier);
+    $supplierName = "%" . $supplierParts[1] . "%";
+    $supplierABN = $supplierParts[0];
+    if ($supplierParts[0] > 0)
+        $supplierQ = ' "supplierABN" = :supplierABN AND ';
+    else
+        $supplierQ = ' "supplierName" LIKE :supplierName AND ';
+}
+
+$startYear = 2007;
+$year = filter_var($_REQUEST['year'], FILTER_SANITIZE_NUMBER_INT);
+if ($year != "") {
+    $yearQ = "YEAR(publishDate) = " . $year . " AND ";
+}
+$standardQ = ' "childCN" is null '; // AND YEAR(contractStart) >= 2007 AND YEAR(contractStart) <= 2010';
+$start = 0.0;
+
+function local_url() {
+    return "http://" . $_SERVER['HTTP_HOST'] . rtrim(dirname($_SERVER['PHP_SELF']), '/\\') . "/";
+}
+
+function include_header($title) {
+    global $start;
+    ?>
+    <!DOCTYPE html>
+    <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en" version="XHTML+RDFa 1.1"
+       xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#"
+       xmlns:rdfs="http://www.w3.org/2000/01/rdf-schema#"
+       xmlns:gr="http://purl.org/goodrelations/v1#"
+       xmlns:dc="http://purl.org/dc/terms/"
+       xmlns:pc="http://purl.org/procurement#"
+       xmlns:unspsc="http://www.ksl.stanford.edu/projects/DAML/UNSPSC.daml#"
+       xmlns:xsd="http://www.w3.org/2001/XMLSchema#"
+       xmlns:pcdt="http://purl.org/procurement/public-contracts-datatypes#"
+        prefix="rdf: http://www.w3.org/1999/02/22-rdf-syntax-ns# 
+              rdfs: http://www.w3.org/2000/01/rdf-schema# 
+              gr: http://purl.org/goodrelations/v1# 
+              dcterms: http://purl.org/dc/terms/ 
+              pc: http://purl.org/procurement/public-contracts# 
+              cpv: http://purl.org/weso/pscs/cpv/2008/resource/ 
+              unspsc: http://www.ksl.stanford.edu/projects/DAML/UNSPSC.daml#
+              v: http://www.w3.org/2006/vcard/ns# 
+              payment: http://reference.data.gov.uk/def/payment# 
+              br: http://purl.org/business-register#              
+              xsd: http://www.w3.org/2001/XMLSchema# 
+              pcdt: http://purl.org/procurement/public-contracts-datatypes#">
+        <head>
+            <title><?php echo $title; ?> - Contract Dashboard</title>
+            <link rel="stylesheet" type="text/css" href="bootstrap.min.css"> 
+            <link rel="stylesheet" type="text/css" href="bootstrap-responsive.css"> 
+            <!-- Le HTML5 shim, for IE6-8 support of HTML elements -->
+            <!--[if lt IE 9]>
+              <script src="http://html5shim.googlecode.com/svn/trunk/html5.js"></script>
+            <![endif]-->
+            <script type="text/javascript" src="lib/bsn.AutoSuggest_2.1.3_comp.js" charset="utf-8"></script>
+            <link rel="stylesheet" href="autosuggest_inquisitor.css" type="text/css" media="screen" charset="utf-8" />
+            <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.6.4/jquery.min.js"></script>
+            <script type="text/javascript">
+                $(document).ready(function()
+                {
+                    //hide the all of the element with class msg_body
+                    $(".msg_body").hide();
+                    //toggle the componenet with class msg_body
+                    $(".msg_head").click(function()
+                    {
+                        $(this).next(".msg_body").slideToggle(600);
+                    });
+                });
+            </script>
+
+            <style type="text/css" title="currentStyle"> 
+                @import "media/css/demo_table.css";
+            </style> 
+            <script type="text/javascript" language="javascript" src="media/js/jquery.dataTables.js"></script> 
+            <script type="text/javascript" language="javascript" src="lib/bootstrap-dropdown.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();
+                } );
+            </script> 
+            <link type="text/css" rel="stylesheet" href="style.css">
+        </head>
+        <body>
+            <div class="navbar">
+                <div class="navbar-inner">
+                    <div class="container-fluid">
+                        <a class="brand" href="#">contract dashboard</a>
+                        <ul class="nav">
+                            <li><a href="displayAgency.php">agencies</a></li>
+                            <li><a href="displaySupplier.php">suppliers</a></li>
+                            <li><a href="displayCategory.php">categories</a></li>
+                            <li><a href="displayCalendar.php">time periods</a></li>
+                            <!--  <li class="dropdown">
+                                  <a href="#" class="dropdown-toggle">metrics</a>
+                                  <ul class="dropdown-menu">-->
+                            <li><a href="displayProcurementMethod.php">tenderm</a></li>
+                            <li><a href="displayConfidentialities.php">confidentiality</a></li>
+                            <li><a href="displayConsultancies.php">consultancies</a></li>
+                            <li><a href="displayAmendments.php">amendments</a></li>
+                            <li><a href="displayMap.php">geo</a></li>
+                        </ul>  
+
+
+                        <form method="post" action="search.php" class="pull-right">
+                            <input type="text" id="searchKeyword" name="searchKeyword"  value="" placeholder="Search" />
+                            <input type="hidden" id="searchID" name="searchID" value=""/>
+                        </form>
+
+                    </div>
+                </div><!-- /topbar-inner -->
+            </div><!-- /topbar -->
+        </div><!-- /topbar-wrapper -->
+        <script type="text/javascript">
+                        	
+            var options_xml = {
+                script: function (input) { return "search_autosuggest.php?input="+input; },
+                varname:"input",
+                callback: function (obj) { document.getElementById('searchID').value = obj.id; }
+            };
+            var as_xml = new bsn.AutoSuggest('searchKeyword', options_xml);
+        </script>
+        <div class="container-fluid">
+            <div class="row-fluid">
+		<div class="span3">
+                	<div class="well sidebar-nav">
+                    		<li class="nav-header">Filter by:</li>
+                    		<li>2008</li>
+                	</div> 
+		</div>
+            	<div class="span9">
+                <?php
+                $start = (float) array_sum(explode(' ', microtime()));
+            }
+
+            function include_footer() {
+                global $start;
+                $end = (float) array_sum(explode(' ', microtime()));
+
+                echo '</div>  <footer>' . "Processing time: " . sprintf("%.4f", ($end - $start)) . " seconds" . '  <footer>';
+
+                    if (strpos($_SERVER['SERVER_NAME'], ".gs")) {
+                    ?>
+                    <script type="text/javascript">
+
+                        var _gaq = _gaq || [];
+                        _gaq.push(['_setAccount', 'UA-12341040-3']);
+                        _gaq.push(['_trackPageview']);
+
+                        (function() {
+                            var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true;
+                            ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js';
+                            var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);
+                        })();
+
+                    </script>
+                    <?php
+                }
+                echo '</div> </div></body> </html>';
+            }
+
+            include ("graphs.inc.php");
+
+
+
+            
+