<?php
/*
 *  Copyright (C) 2012
 *     Ed Rackham (http://github.com/a1phanumeric/PHP-MySQL-Class)
 *  Changes to Version 0.8.1 copyright (C) 2013
 *	Christopher Harms (http://github.com/neurotroph)
 *
 *  This program is free software: you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation, either version 3 of the License, or
 *  (at your option) any later version.
 *
 *  This program is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with this program.  If not, see <http://www.gnu.org/licenses/>.
*/

// MySQL Class v0.8.1
class MySQL {
	
	// Base variables
    public  $lastError;         // Holds the last error
	public  $lastQuery;         // Holds the last query
	public  $result;            // Holds the MySQL query result
	public  $records;           // Holds the total number of records returned
	public  $affected;          // Holds the total number of records affected
	public  $rawResults;        // Holds raw 'arrayed' results
	public  $arrayedResult;     // Holds an array of the result
	
	private $hostname;          // MySQL Hostname
	private $username;          // MySQL Username
	private $password;          // MySQL Password
	private $database;          // MySQL Database
	
	private $databaseLink;      // Database Connection Link
	


	/* *******************
	 * Class Constructor *
	 * *******************/
	
	function __construct($database, $username, $password, $hostname='localhost', $port=3306, $persistant = false){
		$this->database = $database;
		$this->username = $username;
		$this->password = $password;
		$this->hostname = $hostname.':'.$port;
		
		$this->Connect($persistant);
	}
	
	/* *******************
	 * Class Destructor  *
	 * *******************/
	
	function __destruct(){
		$this->closeConnection();
	}
	
	public function makeArray($aArray) {
		
		if(is_array($aArray) && !isset($aArray[0])) {
			$aArrayTMP = $aArray; unset($aArray);
			$aArray[0] = $aArrayTMP; unset($aArrayTMP);
		}
		
		return $aArray;
	}
	
	public function inputForm($aInsert, $sPage = FALSE) {
		$bError = FALSE;
		foreach($aInsert as $k => $v) {
			if(!$this->insert($k, $v)) $bError = TRUE;
		}
		
		if(!$bError) {
			echo '
					<div style="text-align:center;margin-bottom:10px;font-size: 16px;">
					<p>Eintragung in die Datenbank ist erfolgt</p>
					<a class="btn btn-success" href="' . URL . $sPage . '" target="_top">Seite aktualisieren</a></div>
			';
		}
	}
	
	public function readyForAjax($aArray) {
		if(!empty($aArray['save_where']) && !empty($aArray['save_who'])) {
			$aSQL['where'][$aArray['save_where']] = $aArray['save_who'];
			
			unset($aArray['save_where']); unset($aArray['save_who']);
		} else {
			return false;
		}
		
		if(!empty($aArray)) {
			foreach($aArray as $k => $v) {
				foreach($v as $ks => $vs) $aSQL['update'][$k][$ks] = trim($vs);
			}
		} else {
			return false;
		}
		
		return $aSQL;
	}
	
	/* *******************
	 * Private Functions *
	 * *******************/
	
	// Connects class to database
	// $persistant (boolean) - Use persistant connection?
	private function Connect($persistant = false){
		$this->CloseConnection();
		
		if($persistant){
			$this->databaseLink = mysql_pconnect($this->hostname, $this->username, $this->password);
		}else{
			$this->databaseLink = mysql_connect($this->hostname, $this->username, $this->password);
		}
		
		if(!$this->databaseLink){
   		$this->lastError = 'Could not connect to server: ' . mysql_error($this->databaseLink);
			return false;
		}
		
		if(!$this->UseDB()){
			$this->lastError = 'Could not connect to database: ' . mysql_error($this->databaseLink);
			return false;
		}
		
		$this->setCharset(); // TODO: remove forced charset find out a specific management
		return true;
	}
	
	
	// Select database to use
	private function UseDB(){
		if(!mysql_select_db($this->database, $this->databaseLink)){
			$this->lastError = 'Cannot select database: ' . mysql_error($this->databaseLink);
			return false;
		}else{
			return true;
		}
	}
	
	
	// Performs a 'mysql_real_escape_string' on the entire array/string
	private function SecureData($data, $types=array()){
		if(is_array($data)){
            $i = 0;
			foreach($data as $key=>$val){
				if(!is_array($data[$key])){
                    $data[$key] = $this->CleanData($data[$key], $types[$i]);
					$data[$key] = mysql_real_escape_string($data[$key], $this->databaseLink);
                    $i++;
				}
			}
		}else{
            $data = $this->CleanData($data, $types);
			$data = mysql_real_escape_string($data, $this->databaseLink);
		}
		return $data;
	}
    
    // clean the variable with given types
    // possible types: none, str, int, float, bool, datetime, ts2dt (given timestamp convert to mysql datetime)
    // bonus types: hexcolor, email
    private function CleanData($data, $type = ''){
        switch($type) {
            case 'none':
				// useless do not reaffect just do nothing
                //$data = $data;
                break;
            case 'str':
            case 'string':
                settype( $data, 'string');
                break;
            case 'int':
            case 'integer':
                settype( $data, 'integer');
                break;
            case 'float':
                settype( $data, 'float');
                break;
            case 'bool':
            case 'boolean':
                settype( $data, 'boolean');
                break;
            // Y-m-d H:i:s
            // 2014-01-01 12:30:30
            case 'datetime':
                $data = trim( $data );
                $data = preg_replace('/[^\d\-: ]/i', '', $data);
                preg_match( '/^([\d]{4}-[\d]{2}-[\d]{2} [\d]{2}:[\d]{2}:[\d]{2})$/', $data, $matches );
                $data = $matches[1];
                break;
            case 'ts2dt':
                settype( $data, 'integer');
                $data = date('Y-m-d H:i:s', $data);
                break;

            // bonus types
            case 'hexcolor':
                preg_match( '/(#[0-9abcdef]{6})/i', $data, $matches );
                $data = $matches[1];
                break;
            case 'email':
                $data = filter_var($data, FILTER_VALIDATE_EMAIL);
                break;
            default:
                break;
        }
        return $data;
    }



    /* ******************
     * Public Functions *
     * ******************/

    // Executes MySQL query
    public function executeSQL($query){
        $this->lastQuery = $query;
        if($this->result = mysql_query($query, $this->databaseLink)){
            if (gettype($this->result) === 'resource') {
                $this->records  = @mysql_num_rows($this->result);
            } else {
               $this->records  = 0;
            }
            $this->affected = @mysql_affected_rows($this->databaseLink);

            if($this->records > 0){
                $this->arrayResults();
                return $this->arrayedResult;
            }else{
                return true;
            }

        }else{
            $this->lastError = mysql_error($this->databaseLink);
            return false;
        }
    }

	public function commit(){
		return mysql_query("COMMIT", $this->databaseLink);
	}
  
	public function rollback(){
		return mysql_query("ROLLBACK", $this->databaseLink);
	}

	public function setCharset( $charset = 'UTF8' ) {
		return mysql_set_charset ( $this->SecureData($charset,'string'), $this->databaseLink);
	}
	
    // Adds a record to the database based on the array key names
    public function insert($table, $vars, $exclude = '', $datatypes=array()){

        // Catch Exclusions
        if($exclude == ''){
            $exclude = array();
        }

        array_push($exclude, 'MAX_FILE_SIZE'); // Automatically exclude this one

        // Prepare Variables
        $vars = $this->SecureData($vars, $datatypes);

        $query = "INSERT INTO `{$table}` SET ";
        foreach($vars as $key=>$value){
            if(in_array($key, $exclude)){
                continue;
            }
            $query .= "`{$key}` = '{$value}', ";
        }

        $query = trim($query, ', ');

        return $this->executeSQL($query);
    }

    // Deletes a record from the database
    public function delete($table, $where='', $limit='', $like=false, $wheretypes=array()){
        $query = "DELETE FROM `{$table}` WHERE ";
        if(is_array($where) && $where != ''){
            // Prepare Variables
            $where = $this->SecureData($where, $wheretypes);

            foreach($where as $key=>$value){
                if($like){
                    $query .= "`{$key}` LIKE '%{$value}%' AND ";
                }else{
                    $query .= "`{$key}` = '{$value}' AND ";
                }
            }

            $query = substr($query, 0, -5);
        }

        if($limit != ''){
            $query .= ' LIMIT ' . $limit;
        }

        return $this->executeSQL($query);
    }
	
	private function searchQuerys($sQuery) {
		// Die aktuell angeforderte Seite
		$sReturn['page']['view'] = $_GET['page'] + 0;
		
		// Falls noch keine Seitenzahl übergeben wurde, den Wert auf die erste Seite setzen
		if(empty($sReturn['page']['view'])) $sReturn['page']['view'] = 1;
			
		// Berechnet die nächsten Eintraege aus MAX_EINTRAEGE
		$sReturn['start'] = $sReturn['page']['view'] * MAX_EINTRAEGE - MAX_EINTRAEGE; 
		
		// Ermittelt die Gesamtzahl der Datensätze
		$sReturn['result'] = @mysql_query($sQuery);
		$sReturn['count'] = @mysql_result($sReturn['result'],0,"menge");
		
		$sReturn['complete'] = ceil($sReturn['count'] / MAX_EINTRAEGE);
		
		return $sReturn;
	}
	
	public function navigationsLeiste($extVariables, $sQuery, $js = FALSE, $wordpress = FALSE){
		
		$aVars = $this->searchQuerys($sQuery);
		$sURL = !empty($extVariables) ? $extVariables : URL .'?';
		
		// Die Menge der angezeigten Links für die Seiten werden errechnet 
		$NavCeil = floor(NAV_LEISTE / 2);
		
		// Eine Seite zurück oder zum Anfang nur anzeigen, wenn mindestens eine Seite zurück
		// geblättert werden kann 
		if($aVars['page']['view'] > 1){
			$string .= '
			<li class="paginate_button previous" id="example1_previous">
				<a href="' . $sURL . '" aria-controls="example1" data-dt-idx="0" tabindex="0"' . ($js == TRUE ? ' onclick="paginateSession(1);return false;"' : FALSE) . '><i class="fa fa-caret-left"></i></a>
			</li>';
			
			$string .= '
			<li class="paginate_button next" id="example1_next">
				<a href="' . $sURL . ($aVars['page']['view']-1 != 1 ? (!$wordpress ? 'page=' . ($aVars['page']['view']-1) : ($aVars['page']['view']-1) . '/') : FALSE). '" aria-controls="example1" data-dt-idx="8" tabindex="0"' . ($js == TRUE ? ' onclick="paginateSession('.($aVars['page']['view']-1).');return false;"' : FALSE) . '><i class="fa fa-angle-double-left"></i>&nbsp;&nbsp;zurück</a>
			</li>';
		}
		
		// Baut die Seitennavigation aúf (1 2 3 4 5 6 ... n)
		for($x=$aVars['page']['view']-$NavCeil;$x<=$aVars['page']['view']+$NavCeil;$x++){
			// Alle Seitenzahlen vor und nach der aktuellen Seite verlinken
			if(($x>0 && $x<$aVars['page']['view']) || ($x>$aVars['page']['view'] && $x<=$aVars['complete'])) {
				$string .= '
				<li class="paginate_button ">
					<a href="' . $sURL . ($x != 1 ? (!$wordpress ? 'page=' . $x : $x . '/') : FALSE) . '" aria-controls="example1" data-dt-idx="5" tabindex="0"' . ($js == TRUE ? ' onclick="paginateSession(' . $x . ');return false;"' : FALSE) . '>' . $x . '</a>
				</li>';
				//$string .= '<a href="?site='.$x.$extVariables.'">'.$x.'</a>&nbsp;&nbsp;';
			}
			
			// Die Seitenzahl der aktuellen Seite nicht verlinken
			if($x==$aVars['page']['view']) {
				$string .= '
				<li class="paginate_button active">
					<a href="#" aria-controls="example1" data-dt-idx="4" tabindex="0">'.$x.'</a>
				</li>';
			}
		}
		
		// Eine Seite vor oder zum Ende nur anzeigen, wenn mindestens eine Seite weiter
		// geblättert werden kann 
		if($aVars['page']['view'] < $aVars['complete']){
			
			$string .= '
			<li class="paginate_button previous" id="example1_previous">
				<a href="' . $sURL . (!$wordpress ? 'page=' . ($aVars['page']['view']+1) : ($aVars['page']['view']+1) . '/') . '" aria-controls="example1" data-dt-idx="0" tabindex="0"' . ($js == TRUE ? ' onclick="paginateSession('.($aVars['page']['view']+1).');return false;"' : FALSE) . '>weiter&nbsp;&nbsp;<i class="fa fa-angle-double-right"></i></a>
			</li>';
			
			$string .= '
			<li class="paginate_button next" id="example1_next">
				<a href="' .$sURL . (!$wordpress ? 'page=' . $aVars['complete'] : $aVars['complete'] . '/') . '" aria-controls="example1" data-dt-idx="8" tabindex="0"' . ($js == TRUE ? ' onclick="paginateSession('.$aVars['complete'].');return false;"' : FALSE) . '><i class="fa fa-caret-right"></i></a>
			</li>';
		}
		
		return array('return' => $string, 'count' => $aVars['count'], 'start' => $aVars['start'], 'view' => $aVars['start']+MAX_EINTRAEGE);
	}


    // Gets a single row from $from where $where is true
    public function select($from, $where='', $orderBy='', $limit='', $like=false, $operand='AND',$cols='*', $wheretypes=array()){
        // Catch Exceptions
        if(trim($from) == ''){
            return false;
        }

        $query = "SELECT {$cols} FROM `{$from}` WHERE ";

        if(is_array($where) && $where != ''){
            // Prepare Variables
            $where = $this->SecureData($where, $wheretypes);

            foreach($where as $key=>$value){
                if($like){
                    $query .= "`{$key}` LIKE '%{$value}%' {$operand} ";
                }else{
                    $query .= "`{$key}` = '{$value}' {$operand} ";
                }
            }

            $query = substr($query, 0, -(strlen($operand)+2));

        }else{
            $query = substr($query, 0, -6);
        }

        if($orderBy != ''){
            $query .= ' ORDER BY ' . $orderBy;
        }

        if($limit != ''){
            $query .= ' LIMIT ' . $limit;
        }

        $result = $this->executeSQL($query);
        if(is_array($result)) return $result;
        return array();

    }

    // Updates a record in the database based on WHERE
    public function update($table, $set, $where, $exclude = '', $datatypes=array(), $wheretypes=array()){
        // Catch Exceptions
        if(trim($table) == '' || !is_array($set) || !is_array($where)){
            return false;
        }
        if($exclude == ''){
            $exclude = array();
        }

        array_push($exclude, 'MAX_FILE_SIZE'); // Automatically exclude this one

        $set 	= $this->SecureData($set, $datatypes);
        $where 	= $this->SecureData($where,$wheretypes);

        // SET

        $query = "UPDATE `{$table}` SET ";

        foreach($set as $key=>$value){
			
			// Änderungen mitloggen
			if($table != "pacim_view") {
				$this->logging($key, $value, $table, $where);
			}
			
            if(in_array($key, $exclude)){
                continue;
            }
            $query .= "`{$key}` = '{$value}', ";
        }

        $query = substr($query, 0, -2);

        // WHERE

        $query .= ' WHERE ';

        foreach($where as $key=>$value){
            $query .= "`{$key}` = '{$value}' AND ";
        }

        $query = substr($query, 0, -5);

        return $this->executeSQL($query);
    }
	
	private function logging($key, $value, $table, $where) {
		
		foreach($where as $k => $v) $sWhere = $v;
		
		$aHistory['history_key'] = $key;
		$aHistory['history_value'] = $value;
		$aHistory['history_table'] = $table;
		$aHistory['history_where'] = $sWhere;
		$aHistory['history_user'] = $_SESSION['userID'];
		$aHistory['history_datetime'] = date('Y-m-d H:i:s');
		
		$this->insert('pacim_history', $aHistory);
	}

    // 'Arrays' a single result
    public function arrayResult(){
        $this->arrayedResult = mysql_fetch_assoc($this->result) or die (mysql_error($this->databaseLink));
        return $this->arrayedResult;
    }

    // 'Arrays' multiple result
    public function arrayResults(){

        //if($this->records == 1){
            //return $this->arrayResult();
        //}

        $this->arrayedResult = array();
        while ($data = mysql_fetch_assoc($this->result)){
            $this->arrayedResult[] = $data;
        }
        return $this->arrayedResult;
    }

    // 'Arrays' multiple results with a key
    public function arrayResultsWithKey($key='id'){
        if(isset($this->arrayedResult)){
            unset($this->arrayedResult);
        }
        $this->arrayedResult = array();
        while($row = mysql_fetch_assoc($this->result)){
            foreach($row as $theKey => $theValue){
                $this->arrayedResult[$row[$key]][$theKey] = $theValue;
            }
        }
        return $this->arrayedResult;
    }

    // Returns last insert ID
    public function lastInsertID(){
        return mysql_insert_id($this->databaseLink);
    }

    // Return number of rows
    public function countRows($from, $where=''){
        $result = $this->select($from, $where, '', '', false, 'AND','count(*)');
        return $result["count(*)"];
    }

    // Closes the connections
    public function closeConnection(){
        if($this->databaseLink){
			// Commit before closing just in case :)
			$this->commit();
            mysql_close($this->databaseLink);
        }
    }
}