<?php if (!defined('PmWiki')) exit();
/**
 * PmWiki module to run a MySQL SELECT query based on info provided as parameters
 * in the (:selectquery:) tag or via a form.
 * Copyleft June 2006, 23 February 2007 by Ben Stallings <ben@interdependentweb.com>
 * Version 1.4 by Guy Moreau <gmyx@gpws.ca>
 * Version 2.0 Beta 1, March 29th 2007, by Guy Moreau <gmyx@gpws.ca>.
 * Version 2.0 Build 8, June 20, 2009 by Guy Moreau <gmyx@gpws.ca>
 * version 3.7: updated for php7 - Pete Kay pkay42@gmail.com
 * version 4.0: December 13, 2017 by Ben Stallings ben@interdependentweb.com
 *
 * Version 4.0 is a complete rewrite of this recipe.
 * For full revision history including changelog, see version 3.7.
 *
 * Changes since version 3.7:
 * * Converted functions and global variables to object-oriented programming.
 * * Discarded DatabaseStandard in favor of PDO.
 * * Removed undocumented features.
 */

$RecipeInfo['SelectQuery']['4.0'] = '2017-12-13';

Markup('selectquery', '>if', "/\(:selectquery[\s]+([^:]*):\)/", function ($m) {
  global $SelectQuery;
  $params = ParseArgs($m[1]);
  $SelectQuery = new SelectQuery($params);
  if ($SelectQuery->error > '') {
    return ("SelectQuery error: ". $SelectQuery->error);
  } else {
    $out = $SelectQuery->query($params);
    return $out;
  }
});

Markup('databasefield', 'inline', "/{`(.*?)`}/", function ($m) {
  global $SelectQuery;
  if (is_object($SelectQuery)) return $SelectQuery->DatabaseField($m[1]);
});

Markup('ternarylogic', 'directives', "/{\((.*?) \? (.*?) : (.*?)\)}/", function ($m) {
  global $SelectQuery;
  if (is_object($SelectQuery)) return $SelectQuery->TernaryLogic($m[1],$m[2],$m[3]);
});

/**
 * All the functionality of this recipe is in the SelectQuery class.
 * Due to the order of processing markup, the (:selectquery:) tag should always be processed
 * before any databasefield or ternarylogic tags, so that the object is present and ready for use.
 * The component functions below are ordered alphabetically.
 */
class SelectQuery {
  var $database = '', $FieldID = '`', $OffLimits, $Separator = '.', $TableID = '';
  var $dbh, $columns, $error, $formdata, $links, $SQdata, $tokens;
  function __construct($params) {
    global $Databases, $SelectQuerySettings, $SQofflimits;
    // Copy the settings from the global variable to the corresponding object properties.
    foreach (array('database','FieldID','OffLimits','Separator','TableID') as $s) {
      if (isset($SelectQuerySettings[$s])) $this->$s = $SelectQuerySettings[$s];
    }
    // Support legacy $SQofflimits global variable.
    if (is_array($SQofflimits) && !is_array($SelectQuerySettings['OffLimits'])) {
      $this->OffLimits = $SQofflimits;
    } else $this->OffLimits = array();
    // Fix typo from version 3
    if (isset($SelectQuerySettings['Seperator']) && !isset($SelectQuerySettings['Separator'])) {
      $this->Separator = $SelectQuerySettings['Seperator'];
    }
    // Copy any submitted form data to an object property so we can alter it if necessary.
    if ($_POST['action'] != 'edit') $this->formdata = $_REQUEST;
    // Check for run-time override of which database to use
    if ($params['connection'] > '') {
      $this->database = $params['connection'];
    }
    // If database is still not defined, check for legacy constants
    if ($this->database == '' && defined('DB_SERVER') && defined('DB_NAME') && defined('DB_USER') && defined('DB_PASS')) {
      $Databases['ContantsConverted'] = array(
        'driver' => 'mysql',
        'hostname' => DB_SERVER,
        'database' => DB_NAME,
        'username' => DB_USER,
        'password' => DB_PASS);
      $this->database = 'ContantsConverted';
    }
    // Connect to database
    try {
      $db = $Databases[$this->database];
      $this->dbh = new PDO($db['driver'] .':host='. $db['hostname'] .';dbname='. $db['database'], $db['username'
      ], $db['password']);
    } catch (PDOException $e) {
      $this->error = $e->getMessage();
    }
  }

  /**
   * Basic sanity checking on the values that have been passed in.
   * $params is passed by reference; this function only returns a value if there's an error.
   */
  private function checkParams(&$params) {
    // Combine parameters passed in the (:selectquery:) tag with those submitted in a form.
    if (is_array($this->formdata)) $params = array_merge($params,$this->formdata);
    // At the very least, tables and columns must be specified.
    if (!isset($params['tables']) || $params['tables'] == '') return "No table name supplied.";
    if (!isset($params['columns']) || $params['columns'] == '') return "No column names supplied.";
    // Check for wildcards in columns, which are not supported.
    if (preg_match('/.*[\*%\?].*/', $params['columns'])) {
      return "Wildcards (*, ? and %) are not supported in columns.";
    }
    // 'where' parameters may contain characters that get escaped or translated by the wiki.
    $params['where'] = html_entity_decode(stripslashes($params['where']));
    // If there are semicolons in parameters, assume that commas were intended, and not SQL injection.
    foreach (array('columns', 'order', 'tables', 'where') as $p) {
      $params[$p] = str_replace(';', ',', $params[$p]);
    }
    // Check for off-limits columns.
    $searchstring = strtolower(",". $params['columns'] .",". $params['link'] .",");
    foreach ($this->OffLimits as $offlimit) {
      if (strpos($searchstring, strtolower(",$offlimit,")) !== FALSE) {
        return "One or more columns you have requested are off limits to queries.";
      }
    }
  }

  /**
   * Display a specified value from the database query that just ran.
   */
  function DatabaseField($fieldname) {
    if (isset($this->SQdata[$fieldname])) return nl2br($this->SQdata[$fieldname]);
  }

  /**
   * Display some helpful information about the query if display=debug.
   */
  private function debug($params, $sql) {
    $out = "|| border=1\n||! settings key||!value ||\n";
    foreach (array('database','FieldID','OffLimits','Separator','TableID') as $s) {
      $out .= "|| $s||". $this->$s ." ||\n";
    }
    $out .= "\n|| border=1\n||! params key||!value ||\n";
    foreach ($params as $key => $value) {
      $out .= "|| $key||$value ||\n";
    }
    $out .= "<p>query: $sql</p><p>arguments: ". print_r($this->tokens,1) ."</p>";
    return $out;
  }

  /**
   * Display the query results in a series of divs instead of a table.
   */
  private function displayDiv($params, $result) {
    $out = "<div class=selectquery>";
    // Header row.
    if (strpos('noheader', $params['display']) === FALSE) {
      $out .= '<div class="selectqueryrow">';
      foreach (explode(',', $params['columns']) as $c) {
        $pos = stripos($c, ' as ');
        if ($pos) $c = trim(substr($c, $pos+4),"'");
        $out .= '<div class="selectqueryheaders">'. $c .'</div>';
      }
      $out .= '</div>';
    }
    // Data rows. Note that if there's a string literal column appended by the link() function below,
    // that extra column will not have a header row.
    foreach ($result as $r) {
      $out .= '<div class="selectqueryrow">';
      foreach ($r as $c) {
        $out .= '<div class="selectquerycell">'. $c .'</div>';
      }
      $out .= "</div>";
    }
    $out .= "</div>";
    return "$out\n";
  }

  /**
   * Display the results in a wiki table.
   */
  private function displayTable($params, $result) {
    $out = "\n|| border=1 class=selectquery\n||";
    // Header row.
    if (strpos('noheader', $params['display']) === FALSE) {
      foreach (explode(',', $params['columns']) as $c) {
        $pos = stripos($c, ' as ');
        if ($pos) $c = trim(substr($c, $pos+4),"'");
        $out .= "! $c ||";
      }
    }
    // Data rows. Note that if there's a string literal column appended by the link() function below,
    // that extra column will not have a header row.
    foreach ($result as $r) {
      $out .= "\n||";
      foreach ($r as $c) {
        // align numbers right and text left
        if (is_numeric($c)) $out .= " $c||";
        else $out .= "$c ||";
      }
    }
    return "$out\n";
  }

  /**
   * Turn specified values into links.
   */
  private function link($params, $result) {
    if (!isset($params['link']) || $params['link'] == '') return $result;
    $links = explode(';', $params['link']);
    // Process any links, in the form (field,destination,parameters)
    foreach ($links as $link) {
      list($field,$dest,$param) = explode(',', $link);
      // Check for as clause in parameter
      if (strpos(strtolower($param), ' as ')) {
        list($param,$as) = explode(' as ', strtolower($param));
      } else {
        $as = $param;
      }
      // Strip FieldID from field name
      $field = trim($field, ' '. $this->FieldID);
      foreach ($result as $k => $row) {
        // If this row doesn't have a param value, skip it.
        if (!isset($row[$param])) continue;
        // Attempt to find the field in the result.
        if (isset($row[$field])) {
          // Turn the field into a link.
          $row[$field] = '[['. $dest ."?$as=". $row[$param] .' | '. $row[$field] .']]';
        } else {
          //append the field as a string literal, stripping quotes
          $row[$field] = '[['. trim($field, "'") ."?$as=". $row[$param] .' | '. $field .']]';
        }
        $result[$k] = $row;
      }
    }
    return $result;
  }

  /**
   * This is the main part of the recipe; it calls the various helper functions as necessary.
   */
  function query($params) {
    $this->tokens = array();
    // Perform some basic validity checking on the parameters, and exit if any errors are returned.
    // Note that checkParams() receives $params as a reference and returns an error message if it fails.
    $out = $this->checkParams($params);
    if ($out > '') return "%red%$out";
    $where = $this->where($params);
    // Uncomment this line to retrieve all rows by default.
    // This is undesirable; instead provide 'where="1=1"' in the (:selectquery:) tag.
    //if ($where == '') $where = '1=1';
    if ($where == '') return FALSE;
    $sql = 'SELECT '. $params['columns'] .' FROM '. $this->TableID . $params['tables'] . $this->TableID .' WHERE '. $where;
    if ($params['order'] > '') $sql .= ' ORDER BY '. $params['order'];
    if ($params['limit'] > '') $sql .= ' LIMIT '. $params['limit'];
    // If 'debug' is one of the display fields, display debugging output prior to the query output.
    $display = explode(',', $params['display']);
    if (in_array('debug', $display)) $out .= $this->debug($params, $sql);
    // Run the query already.
    try {
      $sth = $this->dbh->prepare($sql);
      $sth->execute($this->tokens);
      $result = $sth->fetchAll(PDO::FETCH_ASSOC);
    } catch (PDOException $e) {
      return "%red%". $e->getMessage();
    }
    $this->SQdata['RowCount'] = count($result);
    if (count($result)) {
      $result = $this->link($params, $result);
      if (in_array('custom', $display)) {
        // Set SQdata for only the first row of results,
        // for use in $this->DatabaseFields() and $this->TernaryLogic().
        foreach ($result[0] as $k => $v) {
          $pos = stripos($k, ' as ');
          if ($pos) $k = substr($k, $pos+4);
          $this->SQdata[$k] = $v;
        }
      } else {
        // Display all the results as a table or a div.
        if (!in_array('norowcount', $display)) $out .= count($result) ." rows selected.\n\n";
        if (in_array('div', $display)) $out .= $this->displayDiv($params,$result);
        else $out .= $this->displayTable($params, $result);
      }
    } else $out .= ($params['nodata'] > '' ? $params['nodata'] : "The dataset is empty.");
    return "$out\n";
  }

  /**
   * Provide an alternative syntax for conditional display of values based on database data.
   */
  function TernaryLogic($if, $then, $else) {
    // Turn &lt; back to < so it evaluates properly.
    $if = html_entity_decode($if);
    // Put parentheses around any conditionals separated by " and ".
    $if = "(". implode(") and (", explode(" and ", $if)) .")";
    // Substitute values for variables.
    foreach ($this->SQdata as $key => $value) {
      $key = '`'.$key.'`';
      $ifvalue = (is_numeric($value) ? $value : "'$value'");
      $if = str_replace($key, $ifvalue, $if);
      $then = str_replace($key, $value, $then);
      $else = str_replace($key, $value, $else);
    }
    //evaluate ternary logic
    eval("\$if = ($if);");
    return ($if ? $then : $else);
  }

  /**
   * Filter the query as specified.
   */
  private function where($params) {
    $where = $params['where'];
    // Add in 'match' and 'against' parameters, if present.
    if (($params['match'] > '') and ($params['against'] > '')) {
      $against = $this->formdata[$params['against']];
      if ($where > '') $where .= " AND ";
      if ($params['type'] == 'like' || $against == "*" || strpos($against,'*') !== FALSE) {
        //do a LIKE search
        if ($params['type'] == 'like' &&  strpos($against, '*') === FALSE) {
          $this->tokens[':like'] = "%$against%";
        } else {
          $this->tokens[':like'] = str_replace("*", "%", $against);
        }
        $where .= "(". str_replace(",", " LIKE :like OR ", $params['match']) ." LIKE :like)";
      } else {
        //do a fulltext search*/
        $where .= "MATCH (".$params['match'].") AGAINST (:against IN BOOLEAN MODE)";
        $this->tokens[':against'] = ($against ? "+$against" : $params['against']);
      }
    }
    // Handle additional parameters submitted as options.
    foreach (explode(',', $params['options']) as $option) {
      if ($option>'') {
        // If the field name has a table name prepended, remove it.
        if (strpos($option, $this->Separator)) {
          $shortname = substr($option, (strpos($option, $this->Separator)+1));
        } else $shortname = $option;
        // Append a placeholder to the where clause, and the value to the tokens.
        if ($where > '') $where .= " AND ";
        $where .= "$option = :$shortname";
        $this->tokens[":$shortname"] = $this->formdata[$shortname];
      }
    }
    return $where;
  }
}