<?php
  //error_reporting(0);
  /*
 * Script:    DataTables PDO server-side script for PHP and MySQL
 * CopyLeft: 2015 - wildantea, wildantea.com
 * Email : wildannudin@gmail.com
 */
  class new_table extends Database {
    private $total_filtered;
    private $record_total;
    private $total_sukses;
    private $belum_proses;
    private $total_error;
    private $count_data;
    public $main_table;
    public $primary_key;
    public $offset;
    public $data = array();
    public $request;

     function __construct($type) {
        // Let the parent handle construction. 
        parent::__construct($type); 
    }

    //filter data
    public function get_column($col) {
      foreach ($col as $key) {
        $keys=$key." LIKE ?";
        $mark[]=$keys;
      }

      $im=implode(' OR  ', $mark);
      return $im;
    }

    public function query_count($sql) {
      $this->count_data = $this->fetch_custom_single($sql)->jml; 
    }

    public function get_value($col,$value)    {
      foreach ($col as $key) {
        $val = '%'.$value.'%';
        $result[]=$val;
      }

      return $result;
    }

    //custom query datatable
    public function get_custom($sql,$columns){

      $sql_for_counting = stristr($sql, 'from');



      //all data request
      $requestData= $_REQUEST;
      $this->request = $requestData;

      $jml_data = $this->fetch_custom_single("select count(*) as jml ".$sql_for_counting);




/*      $result = $this->pdo->prepare( $sql );
      $result->execute();
      $result->setFetchMode( PDO::FETCH_OBJ );*/

      if (strpos($sql,'where') !== false || strpos($sql,'WHERE') !== false) {
          $status_condition = "and";
        } else {
          $status_condition = "where";
        }


/*
       if (strpos($sql,'where') == false || strpos($sql,'WHERE') == false || strpos($sql,'AND') == false || strpos($sql,'and') == false) {
        $status_condition = "WHERE";
       }*/

      //sukses count (status=1)
      $sukses = $this->fetch_custom_single("select count(*) as jml ".$sql_for_counting." ".$status_condition." status_error=1");
      $this->total_sukses = $sukses->jml;

      //belum proses count (status=0)
      $belum = $this->fetch_custom_single("select count(*) as jml ".$sql_for_counting." ".$status_condition." status_error=0");
      $this->belum_proses = $belum->jml;


      $error = $this->fetch_custom_single("select count(*) as jml ".$sql_for_counting." ".$status_condition." status_error=2");
      $this->total_error = $error->jml;

      //save total record
      $this->record_total = $jml_data->jml;
      //total filtered default

      $this->total_filtered = $jml_data->jml;

      $offset = $requestData['start'];
      $offsets = $offset?$offset:0;
      $this->offset = $offsets;

      if ($requestData['order'][0]['column']!=0) {
          $do_order_by = $columns[$requestData['order'][0]['column']-1];
          $order_by_type = $requestData['order'][0]['dir'];
      } else {
          $do_order_by = $columns[$requestData['order'][0]['column']];
          $order_by_type = $requestData['order'][0]['dir'];
      }

  
          /*
      if ($this->is_numbering==true) {
          $do_order_by = $columns[$requestData['order'][0]['column']-1];
          $order_by_type = $requestData['order'][0]['dir'];
      } else {
        $do_order_by = $columns[$requestData['order'][0]['column']];
        $order_by_type = $requestData['order'][0]['dir'];
      }*/
      
      if( !empty($requestData['search']['value']) ) {

        if (strpos($sql,'where') !== false || strpos($sql,'WHERE') !== false) {
          $condition = "and";
        } else {
           $condition = "where";
        }

 
        $sql = $sql;
        $sql.=" $condition (".$this->get_column($columns).")";
       
        $result = $this->pdo->prepare($sql);



         
        //offset
        $offset = $requestData['start'];
        $offsets = $offset?$offset:0;
        $this->offset = $offsets;


        $result->execute($this->get_value($columns,$requestData['search']['value']));
        $result->setFetchMode( PDO::FETCH_OBJ );
        $this->total_filtered = $result->rowCount();

        $sql.=" ORDER BY ". $do_order_by."   ".$order_by_type." LIMIT ".$requestData['start']." ,".$requestData['length']."   ";
        
        $result = $this->pdo->prepare($sql);

        $result->execute($this->get_value($columns,$requestData['search']['value']));
        $result->setFetchMode( PDO::FETCH_OBJ );
      } else {
        //offset
        $offset = $requestData['start'];
        $offsets = $offset?$offset:0;
        $this->offset = $offsets;

        $sql = $sql;
        $sql.=" ORDER BY ". $do_order_by."   ".$order_by_type."  LIMIT ".$requestData['start']." ,".$requestData['length']."   ";

        $result = $this->fetch_custom($sql);
/*        $result->execute();
        $result->setFetchMode( PDO::FETCH_OBJ );*/
      }


      //$data = $this->table_data($result,$columns);
      //
      return $result;
    }

    public function get_offset()  {
      return $this->offset;
    }

    public function create_data(){
      $data = $this->data;
      $json_data = array(
      "draw"            => intval( $this->request['draw'] ),
      "recordsTotal"    => intval( $this->record_total ),
      "recordsFiltered" => intval( $this->total_filtered ),
      "total_sukses"    => $this->total_sukses,
      "belum_proses"    => $this->belum_proses,
      "total_errornya"  => $this->total_error,
      "data"            => $data   // total data array
      );
      echo json_encode($json_data);
      // send data as json format
    }

    public function set_data($data){
      $this->data = $data;
    }

  }

  ?>