<?php
/**
 * DokuWiki Plugin sqlcomp (Syntax Component)
 *
 * @license GPL 2 http://www.gnu.org/licenses/gpl-2.0.html
 * @author  Oliver Geisen <oliver@rehkopf-geisen.de>
 * @author  Christoph Lang <calbity@gmx.de>
 */

// must be run within Dokuwiki
if (!defined('DOKU_INC')) die();

class syntax_plugin_sqlcomp extends DokuWiki_Syntax_Plugin {
    /**
     * @return string Syntax mode type
     */
    public function getType() {
        return 'substition';
    }
    /**
     * @return string Paragraph type
     */
    public function getPType() {
        return 'block';
    }
    /**
     * @return int Sort order - Low numbers go before high numbers
     */
    public function getSort() {
        return 267;
    }

    /**
     * Connect lookup pattern to lexer.
     *
     * @param string $mode Parser mode
     */
    public function connectTo($mode) {
//        $this->Lexer->addEntryPattern('<FIXME>',$mode,'plugin_sqlcomp');
        $this->Lexer->addSpecialPattern('\[\[mysql\:.*?\]\]', $mode, 'plugin_sqlcomp');
        $this->Lexer->addSpecialPattern('\[\[mssql\:.*?\]\]', $mode, 'plugin_sqlcomp');
        $this->Lexer->addSpecialPattern('\[\[oracle\:.*?\]\]', $mode, 'plugin_sqlcomp');
        $this->Lexer->addSpecialPattern('\[\[sqlite\:.*?\]\]', $mode, 'plugin_sqlcomp');
        $this->Lexer->addSpecialPattern('\[\[sqlaccess\:.*?\]\]', $mode, 'plugin_sqlcomp');
        $this->Lexer->addSpecialPattern('\[\[postgresql\:.*?\]\]', $mode, 'plugin_sqlcomp');
        $this->Lexer->addSpecialPattern('\[\[sqlcsv\:.*?\]\]', $mode, 'plugin_sqlcomp');

        $aliases = $this->_getAliases();
        if ($aliases) {
            foreach($aliases as $name => $def) {
                $this->Lexer->addSpecialPattern('\[\['.$name.'\|.*?\]\]', $mode, 'plugin_sqlcomp');
            }
        }
    }

//    public function postConnect() {
//        $this->Lexer->addExitPattern('</FIXME>','plugin_sqlcomp');
//    }

    /**
     * Handle matches of the sqlcomp syntax
     *
     * @param string          $match   The match of the syntax
     * @param int             $state   The state of the handler
     * @param int             $pos     The position in the document
     * @param Doku_Handler    $handler The handler
     * @return array Data for the renderer
     */
    public function handle($match, $state, $pos, Doku_Handler $handler){
        $data = array();

        $match = substr($match,2,-2);
        $match = explode("|",$match); # CONNECTION, SQL, OPTIONS

        // replace dbaliases with connection-string from config
        $dbaliases = $this->_getAliases();
        foreach($dbaliases as $key => $value) {
            if($key == strtolower($match[0])) {
                $match[0] = $value;
            }
        }
        $con = explode(":",$match[0]); # DBTYPE, DBSERVER, DBUSER, DBPASS, DBNAME
        if(count($con) != 5) {
            msg($this->getLang("syntax_dbcon"), -1);
            return;
        }
        $data[] = $con;

        $data[] = $match[1]; # SQL (multiline)

        $opts = array();        
        if(isset($match[2])) {
            // handle options
            $o = explode("&", $match[2]);
            foreach($o as $opt) {
                $opt = strtolower($opt);
                if(ctype_digit($opt)) {
                    $opts['refresh'] = $opt;
                }
                else {
                    msg($this->getLang("syntax_option").': "'.$opt.'"', -1);
                    return;
                }
            }
        } else {
            // apply defaults
            $opts['refresh'] = $this->getConf('default_refresh');
        }
        $data[] = $opts;

        return $data;
    }

    /**
     * Render xhtml output or metadata
     *
     * @param string         $mode      Renderer mode (supported modes: xhtml)
     * @param Doku_Renderer  $renderer  The renderer
     * @param array          $data      The data from the handler() function
     * @return bool If rendering was successful.
     */
    public function render($mode, Doku_Renderer $renderer, $data) {
        if($mode != 'xhtml') return false;
        $renderer->doc .= utf8_encode($this->_query($data));
        return true;
    }


    //------------------------------------------------------------------------//
    // SQLCOMP FUNCTONS
    //------------------------------------------------------------------------//

    /**
     * Layout
     */
    private $aMessages = array(
        "error" => "<div id=\"error\" style=\"text-align:center; font-weight: bold; border: 2px solid #0f0;background-color: #f00; padding: 5px; margin: 5px\">%text%</div>\n",
        "message" => "<div id=\"difference\" style=\"text-align:center; font-weight: bold; border: 2px solid #fd0;background-color: #ffd; padding: 5px; margin: 5px\">%text%</div>\n",
        "pre" => "<table class=\"inline\">\n",
        "post" => "</table>\n",
        #"th" => "<th class=\"row%number%\" style=\"%type%\">%text%</th>",
        "th" => "<th class=\"row%number%\" style=\"text-align:center;%type%\">%text%</th>",
        "td" => "<td class=\"col%number%\" style=\"%type%\">%text%</td>",
        "tr" => "<tr class=\"row%number%\" style=\"%type%\">%text%</tr>\n",
        "same" => "",
        "new" => "border:2px solid green;",
        "old" => "color:#a0a0a0; text-decoration:line-through;",
        #"deleted" => "border:2px solid red;",
        "deleted" => "background:red; text-decoration:line-through;",
        #"changed" => "border:2px solid blue;"
        "changed" => "background:#F2EA0D;",
    );

    private function _getAliases() {
        $aliases = trim($this->getConf('dbaliases'));
        if ($aliases == '') return;

        $data = array();
        $aliases = explode("\r", $aliases);
        foreach($aliases as $rec) {
            if (substr_count($rec, '=') == 1) {
                list($name, $def) = explode('=', trim($rec));
                $name = strtolower($name);
                $data[$name] = $def;
            }
        }
        return($data);
    }

    private function _error($text){
      return str_replace("%text%",$text,$this->aMessages["error"]);
    }

    private function _message($text){
      return str_replace("%text%",$text,$this->aMessages["message"]);
    }

    private function _debug($data){
        $sResponse = "<pre>";
        if (is_array($data) && !empty($data)) {
            foreach($data as $key => $value){
                $sResponse .= "".$key . "=> " .$value ."<br/>\n";
            }
        } else {
            $sResponse .= "data IS EMPTY";
        }
        $sResponse .= "</pre>";
        return $sResponse;
    }

    private function _load($filename){
      
        $Cache = null;
        $Update = true;
        if(file_exists($filename)){
          $Cache = file_get_contents($filename);
          $Cache = unserialize($Cache);
                    
          $Update = $Cache["Update"];
          if(time() > $Update)
            $Update = true;
          else
            $Update = false;
          $Cache = $Cache["Table"];
        }        
        
        return array($Update,$Cache);
    }
    
    private function _save($filename,$rs,$timestamp){
        $timestamp = (time() + ($timestamp*60));
        $Cache["Update"] = $timestamp;
        $Cache["Table"] = $rs;
        
        $Cache = serialize($Cache);
                
        $handle = fopen($filename,"w");
        fwrite($handle,$Cache);
        fclose($handle);
    }

    private function _query($data,$type=null) {
        #return $this->_debug($data);
        
        if(!is_array($data) || count($data) != 3) {
            msg($this->_error($this->getLang("wrong")), -1);
            return;
        }

        $dbcon = $data[0];
        $sql = $data[1];
        $opts = $data[2];

        $Update = false;
        if($opts['refresh'] > 0) {  
            $sPath = DOKU_INC . "data/cache/sql/";
            if(!is_dir($sPath)) {
                if (!@mkdir($sPath)) {
                    msg($this->_error($this->getLang("cachedir")), -1);
                    return;
                }
            }
            $filename = $sPath.md5(implode('',$dbcon));
            $Cache = $this->_load($filename);
            $Update = true;
            if(is_array($Cache)){
              $Update = $Cache[0];
              $Cache = $Cache[1];
            }
        }
                
        try{  
          switch($dbcon[0]){
            case "mysql": $rs = $this->_mysql($dbcon[1], $dbcon[2], $dbcon[3], $dbcon[4], $sql, $opts); break;
            case "mssql": $rs = $this->_mssql($dbcon[1], $dbcon[2], $dbcon[3], $dbcon[4], $sql, $opts); break;
            case "oracle": $rs = $this->_oracle($dbcon[1], $dbcon[2], $dbcon[3], $dbcon[4], $sql, $opts); break;
            case "sqlite": $rs = $this->_sqlite($dbcon[1], $dbcon[2], $dbcon[3], $dbcon[4], $sql, $opts); break;
            case "sqlaccess": $rs = $this->_sqlaccess($dbcon[1], $dbcon[2], $dbcon[3], $dbcon[4], $sql, $opts); break;
            case "postgresql": $rs = $this->_postgresql($dbcon[1], $dbcon[2], $dbcon[3], $dbcon[4], $sql, $opts); break;
            case "sqlcsv": $rs = $this->_sqlcsv($dbcon[1], $dbcon[2], $dbcon[3], $dbcon[4], $sql, $opts); break;
            default: msg($this->_error($this->getLang("nohandler")), -1); return;
          }
        }catch(Exception $ex){
          msg($this->_error($this->getLang("problem")), -1);
          if(isset($Cache)){
            msg($this->_error($this->getLang("cache")), -1);
            return($this->_print($Cache));
          }
          return;
        }
        
        if ($rs === false){
          return $this->_error($this->getLang("empty") );
        }
        
        if(isset($type) && $type == "csv")
          return $this->array2csv($rs);
        
        if($opts['refresh'] > 0) {  
            $difference = $this->_difference($Cache,$rs);
            $sResponse = $difference[0];
            $sResponse .= $difference[1];
        } else {
            $sResponse = $this->_print($rs);
        }
        
        if($opts['refresh'] > 0) {  
            if($Update && isset($rs)){
              $this->_save($filename,$rs,$data[6]);
            }
        }
        
        return $sResponse;
    }

    private function _print($array){
      
        $i = 0;
        
        $th = "";
        $td = "";
        $tr = "";
        if(!isset($array[0]))
          return $this->_error($this->getLang("problem"));
          
        $temp = array_keys($array[0]);
        foreach($temp as $column){
          if($column == "type")
            continue;  
          $th .= str_replace(array("%number%","%text%","%type%"),array(0,$column,""),$this->aMessages["th"]);
        }
        $tr = str_replace(array("%number%","%text%","%type%"),array(0,$th,""),$this->aMessages["tr"]);
          
        foreach($array as $row) {
          
          $j = 0;
          $td = "";
          if(!isset($row["type"]))
            $row["type"] = $this->aMessages["same"];
            
          foreach($row as $key => $Value){
            if($key == "type")
              continue;
            $td .= str_replace(array("%number%","%text%","%type%"),array($j,$Value,$row["type"]),$this->aMessages["td"]);
            $j++;
          }
          $tr .= str_replace(array("%number%","%text%","%type%"),array($i,$td,$row["type"]),$this->aMessages["tr"]);
          $i++;
        }
        
        $sResponse = $this->aMessages["pre"];
        $sResponse .= $tr;
        $sResponse .= $this->aMessages["post"];
        
        return $sResponse;
      
    }

    private function _difference($Cache,$New){
             
        if($New == $Cache){
          return array($this->_print($New),"");
          return array($this->_print($New),$this->_message($this->getLang("same")));
        }

        if(!isset($New) && isset($Cache))
          return array($this->_print($Cache),$this->_message($this->getLang("difference")));

        if(isset($New) && !isset($Cache))
          return array($this->_print($New),$this->_message($this->getLang("first")));

        if(count($New) <= 0)
          return array($this->_print($Cache),$this->_message($this->getLang("connection")));

        $Max = count($Cache);
        if(count($New) > count($Cache))
          $Max = count($New);
        
        $PrintArray = array();

        for($i=0; $i < $Max; $i++){
          if(isset($Cache[$i]) && !isset($New[$i]))
            $PrintArray[] = array_merge($Cache[$i],array("type" => $this->aMessages["deleted"]));

          if(!isset($Cache[$i]) && isset($New[$i]))
            $PrintArray[] = array_merge($New[$i],array("type" => $this->aMessages["new"]));

          if(isset($Cache[$i]) && isset($New[$i])){
            if($Cache[$i] != $New[$i]){
              $PrintArray[] = array_merge($Cache[$i],array("type" => $this->aMessages["changed"]));
              $PrintArray[] = array_merge($New[$i],array("type" => $this->aMessages["changed"]));
            }else{
              $PrintArray[] = array_merge($New[$i],array("type" => $this->aMessages["same"]));
            }
          }
          
        }

        return array($this->_print($PrintArray),$this->_message($this->getLang("difference")));
    }

    private function _sqlaccess($Server,$User,$Pass,$Database,$Query,$Opts){
                  
        if(!$connection = odbc_connect("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=$Database", "ADODB.Connection", $Pass, "SQL_CUR_USE_ODBC") or false)
          throw new Exception($this->getLang("problem"));

        $rs = odbc_exec($connection,$Query);

        $dbArray = array();
        while ($row = odbc_fetch_array($rs))
          $dbArray[] = $row;

        odbc_close($connection);
        return $dbArray;

    }

    private function _postgresql($Server,$User,$Pass,$Database,$Query,$Opts){
                  
        if(!$connection = pg_connect("host=".$Server." dbname=".$Database." user=".$User." password=".$Pass) or false)
          throw new Exception($this->getLang("problem"));
          
        $rs = pg_exec($Query);
        $dbArray = pg_fetch_array($result, NULL, PGSQL_ASSOC);
      
        pg_close($connection);
        return $dbArray;
      
    }
    
    private function _mysql($Server,$User,$Pass,$Database,$Query,$Opts){
        if(!$connection = mysqli_connect($Server, $User, $Pass) or false)
          throw new Exception(mysqli_connect_error());
          
        if (!mysqli_select_db($connection, $Database))
          throw new Exception(mysqli_error($connection));

        $rs = mysqli_query($connection, $Query);
        $dbArray = array();
        
        if($rs === true)
          $dbArray[] = array( $this->getLang("affected") => mysqli_affected_rows ($connection));
        else
          while ($row = mysqli_fetch_assoc($rs))
            $dbArray[] = $row;
        
        
        mysqli_close($connection);
        return $dbArray;
      
    }

    private function _mssql($Server,$User,$Pass,$Database,$Query,$Opts){
      
        if(!$dbhandle = mssql_connect($Server, $User, $Pass))
          throw new Exception($this->getLang("problem"));
          
        mssql_select_db($Database, $dbhandle);
        
        $rs = mssql_query($Query);

        $dbArray = array();
        
        if($rs === true)
          $dbArray[] = array( $this->getLang("affected") => mssql_rows_affected ($connection));
        else
          while ($row = mssql_fetch_assoc($rs))
            $dbArray[] = $row;

        mssql_close($dbhandle);
        return $dbArray;

    }
    
    private function _oracle($Server,$User,$Pass,$Database,$Query,$Opts){
        if(!$connection = oci_connect($User, $Pass, $Server) or false)
            throw new Exception(oci_error());

        // execute query
        $rs = oci_parse($connection, $Query);
        oci_execute($rs);
        $dbArray = array();

        if($rs === true) {
            $dbArray[] = array($this->aString["affected"] => oci_affected_rows($connection));
        }
        else {
            while($row = oci_fetch_assoc($rs)) {
                $dbArray[] = $row;
            }
        }

        oci_free_statement($rs);
        oci_close($connection);

        return $dbArray;
    }
    
    private function _sqlcsv($Server,$User,$Pass,$Database,$Query,$Opts){
          
        if(!$handle = fopen($Database,"r"))
          throw new Exception($this->getLang("nohandler"));
          
        $dbArray = array();
        $keys = fgetcsv ( $handle , 1000, $Query);

        while ($row = fgetcsv ( $handle , 1000, $Query)){
          $temprow = array();
          foreach($row as $key => $value)
            $temprow[$keys[$key]] = $value;

          $dbArray[] = $temprow;

        }

        fclose($handle);
        return $dbArray;

    }

    private function _sqlite($Server,$User,$Pass,$Database,$Query,$Opts){

        $dbHandle = new PDO('sqlite:'.$Database);

        $result = $dbHandle->query($Query);
        if(!$result)
          throw new PDOException;
        $dbArray = array();
        
        if($result->rowCount() > 0)
          $dbArray[] = array( $this->getLang("affected") => $result->rowCount() );
        else
          while($row = $result->fetch(PDO::FETCH_ASSOC))
            $dbArray[] = $row;
          
        return $dbArray;
        
    }

    private function array2csv($data){
      
      $sResponse = "";
      
      $keys = array_keys($data[0]);
      $sResponse .= implode(";",$keys)."\n";
      foreach($data as $row)
        $sResponse .= implode(";",$row)."\n";
      
      return $sResponse;
      
    }

 }
// vim:ts=4:sw=4:et: