<?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>
*  Version 2.0 Beta 1, March 29th 2007, by Guy Moreau <gmyx@gpws.ca>.
*	* Added display options:
*		* norowcount: do not display the number of selected rows
*		* noheaders: do not display the header row
*		* div: display using divs instead of tables (example of use is with single column data)
*		* debug: allows to output of a) the link data, b) the select string for debug purposes, c) the output string
*	* Converted to database standard
*	* Fixed a bug with fields that were both in the columns list and linked fields not showing
*	Current TODO:
*		* currently cannot handle some fully qualified and some not fully qualified when dealing with links
* Database constants (DB_*) must be defined in config.php or here.
*/

// new connection standards - defaults
SDVA($SelectQuerySettings, array(
	'database' => 'users'
));

$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, $SelectQuerySettings, $DB;
	// Connect to Database
	$dblink = ADOdbConnect($SelectQuerySettings['database']);
	if ($dblink !== TRUE) return $dblink;
 
	// 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']));
 
 	//set up array of display args
 	$display = explode(',',$params['display']);

 	//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.";
			return $out;
		}
	
	 	//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.";
			return $out;		
	 	}
	 
	  	//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);

				//determine field number and need for inclution			

				if (!in_array(strtolower($shortname),explode(',',$params['columns']))) {
					//not in colums list
			 		$linkcols[] = ($tablelink ? $tablelink.'.'.$shortname:$shortname); 
					$linkshorts[] = $shortname;
					$id = count(explode(',',$params['columns'])) + count ($link);
				} else {
					//in columns list
					$id = array_search($shortname, explode(',',$params['columns']));
				}

				//build the liks array for this field
				if (in_array('debug', $display)) {
					print 'target: '.$target.'<br />';
					print 'shortname: '.$shortname.'<br />';
					print 'as: '.$as.'<br />';
					print 'id: '.$id.'<br />';
				}
				$link[$column] = array($target,$shortname,$as,$id);
			}
	 	}
	
		
		//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'] : "");
	
		//check for debug
		if (in_array('debug', $display)) print 'query: '.$query.'<br />';
		$queryd = $DB[$SelectQuerySettings['database']]->Execute($query);
		if ($queryd) {		 
			//display number of rows found
			$rows = $queryd->RecordCount() ;
			if (!in_array('custom', $display) && !in_array('norowcount', $display)) $out = "$rows rows selected.\n\n";
			if ($rows < 1) return $out;
		 
			//create page variables and conditionals that can be used in the page		 
			SDVA($SQdata,$queryd->GetAssoc());
			$queryd->MoveFirst(); //reset the row counter
			
			if (in_array('custom', $display)) return; //skip displaying the table
		 
			//display the table or div depending on display
			if (!in_array('div', $display)) {
				$out .= "|| class='selectquery'\n";
			} else {
			    	$out .= '(:div class="selectquery":)'.chr(10);
                	}
	
			//check if headers should show
			if (!in_array('noheader', $display)) {
				//insert the headers
		 		for ($i=0;$i < $queryd->FieldCount();$i++) {
			  		//ensure we are allowed to display
			          	$field = $queryd->FetchField($i);
					$fname = $field->name;	
		    		  	if (!in_array(strtolower($fname), $linkshorts) && in_array(strtolower($fname),explode(',',$params['columns']))) {
						$colname = $field->name;

						//check display method
						if (!in_array('div', $display)) {
			  				//table
		           				$out .='||! '. $colname.' ';
						} else {
				   			//divs
				   			$out .= '(:div class="selectqueryheaders":)'.chr(10).$colname.chr(10).'(:divend:)'.chr(10);
						}
		    			}
       		 		}
			}

			//display the rows
			while (!$queryd->EOF) {
				//start the row    		 	
		    		//check display method
			     	if (!in_array('div', $display)) {
			        	//table
			        	$out .= "\n||";
				
			     	} else {
					//div
					$out .= '(:div class="selectqueryrow":)'.chr(10);		     	
		  		}

				//do all the fields (colums)
			 	for ($i=0;$i < $queryd->FieldCount();$i++) {
				 	//ensure we are allowed to display
		        	 	$field = $queryd->FetchField($i);
					$fname = $field->name;	
	    		 		if (!in_array(strtolower($fname), $linkshorts) && in_array(strtolower($fname),explode(',',$params['columns']))) {
						//we can display		 

				 		//wikify line breaks
				 		$data = str_replace("\n","\\\\\n",$queryd->fields($i));	
				 
				 		if ($link[$fname][0]) {
			   				//check for as clause			
			   				if ($link[$fname][2]) {
			      					//link column with as clause
			      					$data = '[['.$link[$fname][0].'?'.$link[$fname][2].'='
				   					. urlencode($queryd->fields($link[$fname][3])).' | '.$data.']]';
				   			} else {
  				      				//link column - no as clause
				      				$data = '[['.$link[$fname][0].'?'.$link[$fname][1].'='
					   				. urlencode($queryd->fields($link[$fname][3])).' | '.$data.']]';
			   				}
			 			}
			 	
						 //if divs - add leader
			 			if (in_array('div', $display)) $out .= '(:div class="selectquerycell":)'.chr(10);
	
						//right-justify numbers, left-justify text
						$out .= (is_numeric($data) ? ' '.$data : $data.' ');
				
				 		//determine terimnator
		  	 			if (!in_array('div', $display)) {
			    				//table
			    				$out .= '||';
				 		} else {
				    			//divs
				    			$out .= chr(10).'(:divend:)'.chr(10);
		        	 		}
					} //end of if clause
				} //end of "for field"

				//if divs - ensure all closed
				if (in_array('div', $display)) {
				   //close the group
				   $out .= chr(10).'(:divend:)'.chr(10);
				}

				//move to next row
				$queryd->MoveNext();
			} //end of "while eof"
		 	//if divs - ensure all closed
			if (in_array('div', $display)) {
		 		//close the group
		    		$out .= chr(10).'(:divend:)'.chr(10);
		 	}
		} else { //query didn't work
			$out = "%red%$query\\\\\n".mysql_error();
		}	
 	} //end of "if all parameters present"

	//debug
	if (in_array('debug', $display)) print 'out: '.$out.'<br />';

 	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);
}