<?
	require_once('ElectorateRow.php');
	require_once('Database.php');

	/* Access class for the Electorate table. THis will return ElectorateRow objects
	   for the querries done.*/
	Class Electorate {
		var $link;
		var $errorMessage;
		var $errorLine;
		
		function Electorate() {
			global $MYSQL_HOST, $MYSQL_DATABASE, $MYSQL_USER, $MYSQL_PASS;
			/* Initialize the database */
			$this->link = mysql_connect($MYSQL_HOST, $MYSQL_USER, $MYSQL_PASS);
			if (!mysql_select_db($MYSQL_DATABASE, $this->link)) {
				print "No Database selected: $MYSQL_DATABASE - $MYSQL_HOST - $MYSQL_USER - $MYSQL_PASS\n";
				$this->errorMessage = "DB Error(" . mysql_errno() . ") - " . mysql_error();
				$this->errorLine = __LINE__;
			}
		}

		/* Get a single electorate Row by its uniqe ID */
		function fetchRecordByID($id) {
			return(new ElectorateRow($id));
		}

		/* Fetch a list of records based on a given SQL query. This is a helper
		   function that should only be called from inside the Electorate 
			class */
		function fetchRecordsByQuery($query) {
			/* Execute the query */
			$result = mysql_query($query, $this->link);
			if (!$result) {
				$this->errorMessage = "DB Error(" . mysql_errno() . ") - " . mysql_error();
				$this->errorLine = __LINE__;
				return(false);
			}
			/* Fetch the resuls and add them to an array */
			$rtnArray = Array();
			while ($row = mysql_fetch_assoc($result)) {
				array_push($rtnArray, new ElectorateRow($row['ID']));
			}
			mysql_free_result($result);
			/* Return the array */
			return($rtnArray);
		}


		/* Fetch a list of Electorate Rows by the phone number associated with it */
		function fetchRecordsByPhone($number, $sortField = '', $sortOrder = 'ASC') {
			/* Create the query */
			$query = "SELECT ID FROM Electorate WHERE PhoneNumber = '$number'";
			if ($sortField != '') {
				$query .= " SORT BY $sortField $sortOrder";
			}
			/* Return the results */
			return($this->fetchRecordsByQuery($query));
		}

		/* Fetch a list of Electorate Rows by the PollNumber */
		function fetchRecordsByPoll($poll, $sortType = 'canvas') {
			/* Create the query */
            $sqlPoll = mysql_escape_string($poll);
			$query = "
SELECT 
    ID, StreetNumber % 2 AS __oddEven
FROM 
    Electorate 
WHERE 
    PollNumber = '$sqlPoll'  ";
			if ($sortType == 'canvas') {
				$query = $query . " ORDER BY Street, __oddEven, StreetNumber ";
			}
			else if ($sortType == 'phone') {
				$query = $query . " ORDER BY LastName, FirstName, MiddleName ";
			}
			else {
				return null;
			}
			/* Return the results */
			return($this->fetchRecordsByQuery($query));
		}

		/* Fetch a list of polls by priority. This will return an associated array. The 
		   index will be the poll Priority and the value will be an array of the polls
			that are of that priority sorted numerically. */
		function getPollsByPriority() {
			/* Create the query */
			$query = "SELECT Priority, PollNumber FROM PollPriority ORDER BY Priority, PollNumber";
			/* Execute the query */
			$result = mysql_query($query, $this->link);
			/* Fetch the results */
			$rtnArray = Array();
			while ($row = mysql_fetch_assoc($result)) {
				if (!is_array($rtnArray[$row['Priority']])) {
					$rtnArray[$row['Priority']] = Array();
				}
				array_push($rtnArray[$row['Priority']], $row['PollNumber']);
			}
			mysql_free_result($result);

			/* Return the sorted array */
			return($rtnArray);
		}

		/* Fetch a list of all polls, organized by poll name */
		function getPollsByName() {
			/* Create the query */
			$query = "SELECT DISTINCT PollNumber FROM Electorate ORDER BY PollNumber";
			/* Execute the query */
			$result = mysql_query($query, $this->link);
			print(mysql_error());
			/* Fetch the results */
			$rtnArray = Array();
			while ($row = mysql_fetch_assoc($result)) {
				array_push($rtnArray, $row['PollNumber']);
			}
			mysql_free_result($result);

			/* Return the sorted array */
			return($rtnArray);
		}

		/** 
		 * Gets the state of the given poll(s). The results are 
		 * returned in an array of hashtables (ordered by priority 
		 * and percent complete), each hashtable has the following 
		 * keys: 
		 * 'poll' => the poll number,
		 * 'priority' => the priority of the poll,
		 * 'voters' => the number of voters in the poll,
		 * 'canvasTotal' => the number of door knocks that have 
		 *	occured
		 * 'phoneTotal' => the number of phone calls that have 
		 *      occured
		 * 'contactedTotal' => the number of electors contacted
		 * 'lookupTotal' => the number of phone lookups that have 
		 *	occured
		 * 'attn1' => the number of 1s in the 'hood.
		 * 'attn2' => the number of 2s in the 'hood.
		 * 'attn3' => the number of 3s in the 'hood.
		 * 'attn4' => the number of 4s in the 'hood.
		 * 'attn5' => the number of 5s in the 'hood.
		 */
		function getCanvasReportForPolls($polls) {
			/* Create the query */
			$ors = array();
			foreach ($polls as $poll) {
				$ors[] = "(El.PollNumber=$poll)";
			}
			$query = 
"SELECT 
	El.PollNumber AS poll, 
	pp.Priority AS priority, 
	COUNT(El.ID) AS voters,
	SUM(El.Canvassed) AS canvasTotal,
	SUM(El.Phoned) AS phoneTotal,
	SUM(El.PhoneLookedUp) AS lookupTotal,
	SUM(IF(
		El.Canvassed <> 0
		OR El.Phoned <> 0,
		1, NULL)) AS contactedTotal,
	COUNT(IF(El.VotingAttention='Definite',1,NULL)) AS attn1,
	COUNT(IF(El.VotingAttention='Probably',2,NULL)) AS attn2,
	COUNT(IF(El.VotingAttention='Possibly',3,NULL)) AS attn3,
	COUNT(IF(El.VotingAttention='Not',4,NULL)) AS attn4,
	COUNT(IF(El.VotingAttention='Hostile',5,NULL)) AS attn5
FROM
	Electorate AS El LEFT JOIN PollPriority AS pp ON 
	pp.PollNumber=El.PollNumber
WHERE 
	(" . join(' OR ', $ors) . ")
GROUP BY
	El.PollNumber
";
			/* Execute the query */
			$result = mysql_query($query, $this->link);
			print(mysql_error());
			/* Fetch the results */
			$rtnArray = Array();
			while ($row = mysql_fetch_assoc($result)) {
				$rtnArray[] = $row;
			}
			mysql_free_result($result);

			/* Return the sorted array */
			return($rtnArray);
		}
	}
?>