<?php

/*
 * voir http://chromero.blogspot.fr/search/label/jQueryTable
 */
try {
    session_start();
    if (!array_key_exists('token', $_SESSION) || $_SESSION['token'] != $_REQUEST['token']) {
        die("invalid token");
    }
    include('db.php');

    $pdo = $pdo = new PDO(
            "mysql:host=$host;dbname=$database", $user, $pass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->query("SET NAMES 'UTF8'");

    $fields = '*';
    $filter = array();
    if(array_key_exists('filter', $_REQUEST)) {
        $temp = json_decode($_REQUEST['filter'], true);
        $filter = $temp['content'];
    }
    $sort = array_key_exists('sort', $_REQUEST) ? $_REQUEST['sort'] : '';
    $limit = '1,30';
    if (array_key_exists('limit', $_REQUEST)) {
        $limit = $_REQUEST['limit'];
    }
    $sql = new SQL($pdo, 'maps_ville');

    $sql->setFields($fields)->setFilter($filter)->setSort($sort)->setLimit($limit);
    if ($_REQUEST['action'] == 'columns') {
        $ret = $sql->getColumns();
    } else {
        $stmt = $sql->prepare($_REQUEST['action'] == 'count');
        $sql->execute();
        $res = $stmt->fetchAll(PDO::FETCH_NUM);
        //print_r($res);
        if ($_REQUEST['action'] == 'count') {
            $ret = $res[0][0];
        } else {
            $ret = $res;
        }
    }
    print(json_encode($ret));
} catch (Exception $e) {
    print($sql->getSql());
    print_r($sql->filter);
    print_r($sql->params);
    print '<pre>' . $e->getMessage();
    print $e->getTraceAsString();
    print('</pre>');
}

class SQL {

    private $pdo;
    private $table;
    private $fields = '*';
    public $filter = array();
    private $sort = '';
    private $limit;
    public $params = array();

    function __construct($pdo, $table) {
        $this->pdo = $pdo;
        $this->table = $table;
    }

    public function getLimit() {
        return $this->limit;
    }

    public function setLimit($limit) {
        $this->limit = $limit;
    }

    public function getFields() {
        return $this->fields;
    }

    public function setFields($fields) {
        $this->fields = $fields;
        return $this;
    }

    public function getFilter() {
        return $this->filter;
    }

    public function setFilter($filter) {
        $this->filter = $filter;
        return $this;
    }

    public function getSort() {
        return $this->sort;
    }

    public function setSort($sort) {
        $this->sort = $sort;
        return $this;
    }

    public function getSql($count = false) {
        //print("count:$count ".$_REQUEST['action']);
        $fields = ($count) ? 'count(*)' : $this->fields;
        $sql = "SELECT $fields FROM " . $this->table;
        $cnt = count($this->filter);
        // {field:'',op:'',value:''}

        $where = '';
        $sep = ' WHERE ';
        for ($index = 0; $index < $cnt; $index++) {
            $field = $this->filter[$index]['field'];
            $where .= $where . $sep . $field . ' ' . $this->filter[$index]['op'] . ' :' . $field;
            $this->params[':' . $field] = $this->filter[$index]['value'];
            $sep = ' AND ';
        }
        $sort = '';
        $limit = '';

        if (!$count) {
            if (!empty($this->sort)) {
                $sort=  str_replace(array("';"), array('  '), $this->sort);
                $sort = ' ORDER BY ' . $sort;
            }
            if (!empty($this->limit)) {
                $limites = explode(',', $this->limit);
                // par sécurité on converti les limites en numérique
                $lims = array_map(function($a){return 0+$a;}, $limites);
                $limit = ' LIMIT ' . implode(',', $lims);
            }
        }
        return $sql . $where . $sort . $limit;
    }

    public function prepare($count = false) {
        $this->stmt = $this->pdo->prepare($this->getSql($count));
        return $this->stmt;
    }

    public function execute() {
        return $this->stmt->execute($this->params);
    }

    public function getColumns() {

        $this->setLimit('1');
        $ret = array();
        $this->prepare();
        $this->execute();
        //$this->stmt->fetch();
        $colCount = $this->stmt->columnCount();
        for ($i = 0; $i < $colCount; $i++) {
            $tab = $this->stmt->getColumnMeta($i);
            $ret[] = array('id' => $tab['name'], 'label' => $tab['name'], 'type' => $tab['native_type']);
        }
        return $ret;
    }

}