<?php
/* Copyright (c) 2007 IBM, made available under EPL v1.0
 * Contributors Nick Boldt
 *
 * The common parameter parsing module for the REST web-api 
 * for retrieving data from the database. This is NOT part
 * of the public web-api.
 *
 * top=name
 * project=name
 * year=yyyy
 * month=yyyymm
 * day=yyyymmdd
 * email=name
 * replytoid=name
 * newsgroup=name
 * messageid=name
 *  
 */
ini_set('display_errors', 1); ini_set('error_reporting', E_ALL);
$debug = isset($_GET["debug"]) ? $_GET["debug"] : 0;

$user="dashboard";
require_once("dbpassword.php");
$database="dashboard";
if (isset($password) && $password)
{
	$_dbh = mysql_connect('dashdbhost', $user, $password);
	mysql_select_db($database, $_dbh);
}
else
{
	$_dbh = null;
}

/* Get X, Y, where as returned array of variables. Assign with list($columnX, $columnY, $where) = getXYWHERE(); */ 
function getWHERE()
{
	global $debug;
	$param = null;
	$columnX = "";
	$columnY = "";
	$where = "";

	$input_patterns = array(
		/* regex => array(fieldname => http get variable) */
		"#([0-9%]+)#" => array(
			"YEAR" => "year",
			"YEARMONTH" => "month",
			"YEARMONTHDAY" => "day",
			"REPLYDAYS" => "replydays"
		),
		"#([a-zA-Z0-9%_\.\@-]+)#" => array(
			"EMAIL" => "email",
		),
		"#([a-zA-Z%_\.-]+)#" => array(
			"NEWSGROUP" => "newsgroup",
			"TOPPROJECT" => "top",
		),
		"#([a-zA-Z0-9%_\.-]+)#" => array(
			"PROJECT" => "project",
		),
	);
	
	foreach($input_patterns as $regex => $pairs)
	{
		foreach ($pairs as $sqlfieldname => $httpfieldname)
		{
			$param = isset($_GET[$httpfieldname]) ? $_GET[$httpfieldname] : (isset($_GET[$sqlfieldname]) ? $_GET[$sqlfieldname] : null);
			if ($debug) 
			{
				print "<pre>     [$regex][$sqlfieldname][$httpfieldname] Got \$param = $param     </pre>\n";
			}
			if ($param)
			{
				if (preg_match($regex, $param, $matches))
				{
		  			$where .= ($where != "" ? " AND " : "") . $sqlfieldname . " LIKE " . 
		  				(is_numeric($matches[1]) ? $matches[1] : "'" . $matches[1] . "'"); /* add to WHERE clause */
		  		}
			}
		}
	}
	return $where;
}

/* get user-specified fields as CSV string, or fall back to a default list */
function getFields($defaultFields = "")
{
	$fieldsOut = "";
	$validFields = array(
		"DATE", "YEAR", "YEARMONTH", "YEARMONTHDAY",
		"TOPPROJECT", "PROJECT",
		"MESSAGEID", "NEWSGROUP", 
		"EMAIL", "REPLYTOID", "REPLYDAYS" 
	);
	if (isset($_GET["fields"]) && preg_match("#([a-zA-Z,_ ]+)#", $_GET["fields"], $matches))
	{
		$bits = preg_split("#[, ]+#", $matches[1]);
		foreach ($bits as $bit)
		{
			if (in_array(strtoupper($bit), $validFields)) 
			{
				$fieldsOut .= ($fieldsOut != "" ? ", " : "") . strtoupper($bit); // if valid field name, collect     
			}
		}
	}

	// support $defaultFields input as array or CSV string 
	$defaultFields = $defaultFields && is_array($defaultFields) && sizeof($defaultFields) > 1 ? implode(", ", $defaultFields) : $defaultFields; 
	
	// return the input collected values; or, the function's default list; or, the entire list if no default given
	return $fieldsOut ? $fieldsOut : ($defaultFields ? $defaultFields : implode(", ", $validFields));
}

function displayQuery($_query)
{
	global $_dbh;
	echo( "# " . $_query . "\n" );
	$result = mysql_query($_query,$_dbh);
	if (!$result) {
		echo("# MySQL Error: ".mysql_error() . "\n");
	} else {
		while($row = mysql_fetch_array($result)){
			for ($i=0; $i<sizeof($row); $i++)
			{
				print isset($row[$i]) ? $row[$i] . "\t" : ""; 
			}
			print "\n";
		}
	}
	print "\n";
}

/* get user-specified list of queries to run as an array of query names, or fall back to a default list */
function getQueries($defaultQueries = array()) // default queries to show if none requested
{
	global $validQueries;
	$queriesOut = array();
	$validQueryNames = array_keys($validQueries); $validQueryNames[] = "all";
	if (isset($_GET["queries"]) && preg_match("#([a-zA-Z0-9,_ ]+)#", $_GET["queries"], $matches))
	{
		$bits = preg_split("#[, ]+#", $matches[1]);
		foreach ($bits as $bit)
		{
			if (in_array(strtolower($bit), $validQueryNames)) 
			{
				$queriesOut[] = strtolower($bit); // if valid query name, collect
			}
		}
	}

	// return the input collected values; or, the function's default list; or, the entire list if no default given
	return $queriesOut ? $queriesOut : ($defaultQueries && is_array($defaultQueries) && sizeof($defaultQueries) > 0 ? $defaultQueries : $validQueryNames);
}

/* return array of lines from a given host and URL */
function http_file($host, $url, $qs=null)
{
	if (ini_get('allow_url_fopen'))
	{
		return file("http://" . $host . (isset($url) ? $url : '/') . (isset($qs) ? '?' . $qs : ''));
	}
	else
	{
		return preg_split("#(\n\r|\r\n|\n|\r)#", http_file_get_contents($host, $url));
	}
}

/* return a block of text (including newlines) from a given host and URL */
function http_file_get_contents($host, $url, $qs=null)
{
	if (ini_get('allow_url_fopen'))
	{
		return file_get_contents("http://" . $host . (isset($url) ? $url : '/') . (isset($qs) ? '?' . $qs : ''));
	}
	else
	{
		$file_contents = "";
		$fp = fsockopen($host, 80, $errno, $errstr, 30);
		if (!$fp)
		{
			return "ERROR connecting to $host: $errstr ($errno)";
		}
		else
		{
			fputs($fp, "GET " . (isset($url) ? $url : '/') . (isset($qs) ? '?' . $qs : '') . " HTTP/1.1\r\n");
			fputs($fp, "Host: $host\r\nUser-Agent: PHP Script\r\nContent-Type: application/x-www-form-urlencoded\r\nConnection: close\r\n\r\n");
			while (!feof($fp))
			{
				$file_contents .= fgets($fp, 128);
			}
			fclose($fp);
		}
		return $file_contents;
	}
}
?>