<?php
/**
* PmWiki module to run a MySQL SELECT query based on info provided as parameters
* in the (:selectquery:) tag or via a form.
* Written for Permaculture Collaborative,
*  Copyleft June 2006, 23 February 2007 by Ben Stallings <ben@interdependentweb.com>
*  AS clause in linked colums codded by Guy Moreau <gmyx@gpws.ca>
* Database constants (DB_*) must be defined in config.php or here.
*/

$SQofflimits = array ('passwd'); //columns that cannot be queried

Markup('selectquery', 'fulltext', "/^\(:selectquery[ 	]*(.*?):\)\s*$/e", "SelectQuery(PSS('$1'))");

Markup('databasefield', 'inline', "/{`(.*?)`}/e", "DatabaseField('$1')");

Markup('ternarylogic', 'directives', "/{\((.*?) \? (.*?) : (.*?)\)}/e", "TernaryLogic('$1','$2','$3')");

if (!is_array($SQdata)) $SQdata = array();
SDVA($SQdata,$_REQUEST);

function SelectQuery($params) {
 global $SQofflimits, $SQdata, $UpdateUserID, $UpdateUsername;
 // Connect to Database
 $dblink = mysql_connect(DB_SERVER, DB_USER, DB_PASS) or die("Could not connect : " . mysql_error());
 mysql_select_db(DB_NAME,$dblink) or die("Could not select database: ".mysql_error());
 // combine parameters passed in the (:selectquery:) tag with those submitted in a form
 $params = ParseArgs($params);
 SDVA($params,$SQdata);
 // where statements may contain characters that get escaped or translated by the wiki
 $where = html_entity_decode(stripslashes($params['where']));
 
 //handle additional parameters submitted as options
 foreach (explode(",", $params['options']) as $option) {
	if ($option>'') {
	 $shortname = (strpos($option, '.') ? substr($option, (strpos($option, '.')+1)) : $option);
	 //check each parameter for validity and add quotes if necessary
	 if ($option == $UpdateUserID) $value = $UpdateUsername;
	 else $value = $params[$shortname];
	 if (get_magic_quotes_gpc()) {$value = stripslashes($value);}
   if (!is_numeric($value)) {$value = "'" . mysql_real_escape_string($value) . "'";}
	 //append parameter to the query
	 $where = "$option = $value". ($where>"" ? " AND $where" : "");
	}
 }
 
 //add in 'match' and 'against' parameters
 if (($params['match']>'') and ($params['against']>'')) {
  $against = $_REQUEST[$params['against']];
	if ($against == "") $against = "*";
	if (strpos($against,'*')=== 0) { //do a LIKE search
	 $like = str_replace("*","%",$_REQUEST[$params['against']]);
	 $where = "(".str_replace(","," LIKE '$like' OR ",$params['match'])." LIKE '$like')". ($where>"" ? " AND $where" : "");
	} else { //do a fulltext search
	 $where = "MATCH (".$params['match'].") AGAINST ('"
	 . ($_REQUEST[$params['against']] ? $_REQUEST[$params['against']] : $params['against'])
	 . "' IN BOOLEAN MODE)". ($where>"" ? " AND $where" : "");
	}
 }
		
 if (($params['columns']>'') and ($params['tables']>'') and ($where>'')) { //ready to go
  // check for semicolons, which may indicate a hacking attempt
	if ((strpos($params['columns'],';')) or (strpos($params['tables'],';')) or (strpos($where,';'))) {
	 $out = "%red%Please do not attempt to send multiple SQL statements using this program.";
	} else {
	
	 //check for forbidden columns
	 $badcolumn = 0;
	 foreach ($SQofflimits as $offlimit) {
	  $offlimit = strtolower($offlimit);
	  if ((in_array($offlimit,explode(',',$params['columns']))) or (strpos(strtolower($params['link']),$offlimit))) {$badcolumn++;}
	 }
	 if ($badcolumn>0) {
	  $out = "%red%One or more columns you have requested are off limits to this program.";
	 } else {
	 
	  //prepare any column links, submitted in format "column,target,param;column,target,param"
		$linkcols = array(); $linkshorts = array();
		if ($params['link']>'') {
		 foreach (explode(';',$params['link']) as $linkloop) {
		  list($column,$target,$param) = explode(',',$linkloop);
			$shortname = (strpos($param, '.') ? substr($param, (strpos($param, '.')+1)) : $param);
			$tablelink = (strpos($param, '.') ? substr($param, 0, (strpos($param, '.'))) : "");
			$as = (strpos($shortname, ' as ') ? substr($shortname, (strpos($shortname, ' as ') + 4)) : $shortname);
			$shortname = (strpos($shortname, ' as ') ? substr($shortname, 0, (strpos($shortname, ' as '))) : $shortname);
			$link[$column] = array($target,$shortname,$as);
			if (!in_array(strtolower($param),explode(',',$params['columns']))) {
			 $linkcols[] = ($tablelink ? $tablelink.'.'.$shortname:$shortname); $linkshorts[] = $shortname;
			}
		 }
		}
		
		//so run the query already
	  $query = "SELECT ".$params['columns']
		. (count($linkcols) ? ",".implode(',',$linkcols) : "")
		. " FROM ".$params['tables']." WHERE $where"
		. ($params['order']>'' ? " ORDER BY ".$params['order'] : "")
		. ($params['limit']>'' ? " LIMIT ".$params['limit'] : "");
		//print $query; //uncomment to debug;
	  if ($queryd = mysql_query($query)) {
		 
		 //display number of rows found
		 $rows = mysql_num_rows($queryd);
		 if ($params['display'] != 'custom') $out = "$rows rows selected.\n\n";
		 if ($rows < 1) return $out;
		 //figure out which columns to display
		 $columns = array();
		 for ($i=0;$i < mysql_num_fields($queryd);$i++) {
		  $col = mysql_field_name($queryd,$i);
			if (!in_array(strtolower($col),$linkshorts)) $columns[] = $col;
		 }
		 
		 //create page variables and conditionals that can be used in the page
		 SDVA($SQdata,mysql_fetch_assoc($queryd));
		 mysql_data_seek($queryd,0); //reset the row counter
			
		 if ($params['display']=='custom') return; //skip displaying the table
		 
		 //display the table
		 $out .= "|| class=selectquery\n||! ";
		 foreach ($columns as $colname) {
		  $out .= $colname.' ||! ';
		 }
		 while ($data = mysql_fetch_assoc($queryd)) {
			
		  $out .= "\n||";
			foreach ($columns as $colname) {
			 
			 //wikify line breaks
			 $data[$colname] = str_replace("\n","\\\\\n",$data[$colname]);	
			 
			 if ($link[$colname][0]) {
			   //check for as clause				
			   if ($link[$colname][2]) {
			      //link column with as clause
			      $data[$colname] = '[['.$link[$colname][0].'?'.$link[$colname][2].'='
				   . urlencode($data[$link[$colname][1]]).' | '.$data[$colname].']]';
			   } else {
  			      //link column - no as clause
			      $data[$colname] = '[['.$link[$colname][0].'?'.$link[$colname][1].'='
				   . urlencode($data[$link[$colname][1]]).' | '.$data[$colname].']]';
			   }
			 }
			 
			 //right-justify numbers, left-justify text
			 $out .= (is_numeric($data[$colname]) ? ' '.$data[$colname] : $data[$colname].' ') . '||';
			} //end of "foreach column"
		 } //end of "while $data"
		} else { //query didn't work
		 $out = "%red%$query\\\\\n".mysql_error();
		}
	 } //end of "if $badcolumn"
	} //end of "if no semicolons"
 } //end of "if all parameters present"
 mysql_close();
 return $out;
} //end of function

function DatabaseField($fieldname) {
 global $SQdata;
 //return str_replace("\n","[[<<]]",$SQdata[$fieldname]);
 return nl2br($SQdata[$fieldname]);
}

function TernaryLogic($if,$then,$else) {
 global $SQdata;
 //return print_r($SQdata,true);
 if (!isset($SQdata)) return;
 $if = html_entity_decode($if); //turn $lt; back to < so it evaluates properly
 
 //put parentheses around conditionals separated by " and "
 $ifarray = explode(" and ",$if);
 if (count($ifarray)>1) {
  for ($i=0;$i<count($ifarray);$i++) {
   $ifarray[$i] = '('.$ifarray[$i].')';
  }
  $if = implode(" and ",$ifarray);
 }
 
 foreach ($SQdata as $key => $value) { //substitute values for variables
  $key = '`'.$key.'`';
	if ($value+0 !== $value) {$ifvalue = "'$value'";} else {$ifvalue = $value;}
  $if = str_replace($key,$ifvalue,$if);
  $then = str_replace($key,$value,$then);
  $else = str_replace($key,$value,$else);
 }
 
 //uncomment to debug
 //return "if $if then $then else $else";
 //return "\$if = ($if);";
 
 //evaluate ternary logic
 eval("\$if = ($if);");
 return ($if ? $then : $else);
}