<?php

class Datatable
{
    
    var $aColumns;
    var $eColumns;
    var $sColumns;
    var $sIndexColumn;
    var $sTable;
    var $rResult;
    var $output;
    var $myWhere;
    var $groupBy;
    var $sOrder;
    var $conn_id;
    
    public function __construct($conn_id) {
        $this->conn_id = $conn_id;
    }

    private function __cleanString($string){
        return strtolower(mysqli_real_escape_string($this->conn_id, $string));
    }
    
    public function datatable_process() {
        
        $sLimit = "";
        if (isset($_GET['iDisplayStart']) && $_GET['iDisplayLength'] != '-1') {
            $sLimit = "LIMIT " . $this->__cleanString($_GET['iDisplayStart']) . ", " . $this->__cleanString($_GET['iDisplayLength']);
        }
        
        $sOrder = "";
        if (isset($_GET['iSortCol_0'])) {
            $sOrder = "ORDER BY  ";
            for ($i = 0; $i < intval($_GET['iSortingCols']); $i++) {
                if ($_GET['bSortable_' . intval($_GET['iSortCol_' . $i]) ] == "true") {
                    if (stripos($this->aColumns[intval($_GET['iSortCol_' . $i]) ], "AS") > 0) {
                        $fiel_explode = explode(" AS ", $this->aColumns[intval($_GET['iSortCol_' . $i]) ]);
                        $sOrder.= $fiel_explode[0] . " " . $this->__cleanString($_GET['sSortDir_' . $i]) . ", ";
                    } else $sOrder.= $this->aColumns[intval($_GET['iSortCol_' . $i]) ] . " " . $this->__cleanString($_GET['sSortDir_' . $i]) . ", ";
                }
            }
            $sOrder = substr_replace($sOrder, "", -2);
            if ($sOrder == "ORDER BY") {
                $sOrder = "";
            }
        }
        
        if (empty($sOrder) && !empty($this->sOrder)) {
            $sOrder = $this->sOrder;
        }
        
        $sWhere = $this->myWhere;
        if (isset($_GET['sSearch'])) {
            if ($_GET['sSearch'] != "") {
                $sWhere.= ($sWhere == "") ? " WHERE (" : " AND (";
                for ($i = 0; $i < count($this->aColumns); $i++) {
                    if ($_GET['bSearchable_' . $i] == "true") {
                        $tmp_search = $this->__cleanString($_GET['sSearch']);                        
                        if (stripos($this->aColumns[$i], "AS") > 0) {
                            $fiel_explode = explode(" AS ", $this->aColumns[$i]);
                            $search_field = $fiel_explode[0];

                            if(isset($this->sColumns[$search_field])){
                                if($this->sColumns[$search_field] == 'date'){
                                    if(validateDate(date('Y-m-d', strtotime($tmp_search)))){
                                        $tmp_search = date('Y-m-d', strtotime($tmp_search));
                                    }
                                }
                            }
                        } else {
                            $search_field = $this->aColumns[$i];
                            if(isset($this->sColumns[$search_field])){
                                if($this->sColumns[$search_field] == 'date'){
                                    if(validateDate(date('Y-m-d', strtotime($tmp_search)))){
                                        $tmp_search = date('Y-m-d', strtotime($tmp_search));
                                    }
                                }
                            }
                        }

                        $sWhere.= 'LOWER(' . $search_field . ')' . " LIKE '%" . $tmp_search . "%' OR ";
                    }
                }
                $sWhere = substr_replace($sWhere, "", -3);
                $sWhere.= ')';
            }
        }
        
        /*
         * Group By
        */
        
        $sGroupBy = $this->groupBy;
        $sQuery = "SELECT SQL_CALC_FOUND_ROWS " . str_replace(" , ", " ", implode(", ", $this->aColumns)) . "," . str_replace(" , ", " ", implode(", ", $this->eColumns)) . " FROM   $this->sTable $sWhere $sGroupBy $sOrder $sLimit";

        $this->rResult = mysqli_query($this->conn_id, $sQuery);
        /*echo "<pre>";
        print_r($sQuery);
        echo "</pre>";
        die();*/
        //die($this->db->last_query());
        
        /*
         * Data set length after filtering
        */
        $iFilteredTotal = mysqli_num_rows($this->rResult);
        
        /*
         * Total data set length
        */
        
        $sQuery = "SELECT COUNT(" . $this->sIndexColumn . ") AS count FROM   $this->sTable $sWhere $sGroupBy ";
        if ($sGroupBy != null) {
            $rResultTotal = mysqli_query($this->conn_id, $sQuery);
            $iTotal = mysqli_num_rows($rResultTotal);
        } else {
            $rResultTotal = mysqli_query($this->conn_id, $sQuery);
            $aResultTotal = mysqli_fetch_row($rResultTotal);
            $iTotal = $aResultTotal[0];
        }
        
        /*
         * Output
        */
        
        $this->output = array("sEcho" => intval(isset($_GET['sEcho']) ? $_GET['sEcho'] : 0), "iTotalRecords" => $iTotal, "iTotalDisplayRecords" => $iTotal,
         // $iFilteredTotal,
        "aaData" => array());
    }
}