<?php
class grid
{  
  protected $table;
  protected $name;
  protected $dir;
  
  public $order;
  public $search;
  public $filters; 
  public $columns_view;
  public $hidden_columns;
  public $columns_edit;
  public $summary;
  
  public $clear_cache    = true;
  
  public $print          = false;
  public $hide_columns   = true;
  public $show_paging    = true;
  
  public $title          = null;
  
  public $src            = null;
  public $src_filtered   = null;
  
  public $id             = 'id';
  public $columns        = '*';
  public $cp             = 1;
  public $limit          = 15;
  public $limits         = array( 5, 10, 15, 25, 50, 100 );
  public $where          = array();
  public $join           = null;
  public $condensed      = false;
  
  public $sql             = null;
  public $sql_count       = null;
  
  public $include_plugins = true;
  
  public $gdb_opt         = array( 
                              'database_type' => 'mysql',
                              'database_name' => 'db_name',
                              'server'        => 'db_host',
                              'username'      => 'db_user',
                              'password'      => 'db_pass',
                              'port'          => 'db_port'
                            );
  
  public $empty_txt      = 'Brak danych w tabeli.';
  
  public $multiselect     = null;
  public $multidelete_url = null;
  
  public $buttons        = null;
  
  public $actions        = array( 'delete' => false, 'delete_url' => null, 'add' => false, 'add_url' => null, 'edit' => false );
  
  function __construct( $table, $name = '' )
  {
    $this -> table = $table;
    $this -> name  = $name;
    $this -> dir   = dirname( __FILE__ );
    
    $this -> name ? $g_table = $this -> name : $g_table = $this -> table;
    
    if ( !empty( $_SESSION[ 'g' . $g_table . 'filters' ] ) )
      $this -> filters = $_SESSION[ 'g' . $g_table . 'filters' ];
    
    if ( !empty( $_SESSION[ 'g' . $g_table . 'limit' ] ) )
      $this -> limit = $_SESSION[ 'g' . $g_table . 'limit' ];
    
    if ( !empty( $_SESSION[ 'g' . $g_table . 'order' ] ) )
      $this -> order = $_SESSION[ 'g' . $g_table . 'order' ];
    
    if ( $this -> clear_cache )
    {
      $this -> name ? $g_table = $this -> name : $g_table = $this -> table;
      if ( is_array( $_SESSION ) ) foreach ( $_SESSION as $key => $val )
      {
        if ( $key != 'g' . $g_table and @get_class( $val ) == '__PHP_Incomplete_Class' )
          unset( $_SESSION[ $key ] );
      }
    }
  }
  
  public function hide_column( $column, $hidden )
  {
    $this -> name ? $g_table = $this -> name : $g_table = $this -> table;
    
    $db = $this -> connectToDb();
    
    $results = $db -> get( 'grid_settings', 'settings', [ 'name' => $g_table ] );
    $results = unserialize( $results );
    
    $results['hidden_columns'][ $column ] = $hidden;
    
    if ( $db -> count( 'grid_settings', [ 'name' => $g_table ] ) )
      $db -> update( 'grid_settings', [ 'settings' => serialize( $results ) ], [ 'name' => $g_table ] );
    else
      $db -> insert( 'grid_settings', [ 'settings' => serialize( $results ), 'name' => $g_table ] );
  }
  
  public function drawEdit( $id )
  {
    $values = get_object_vars( $this );
    
    $view = new gridView( $this -> dir . '/templates/' );
    $view -> values   = $values;
    $view -> element  = $this -> getElement( $id );
    return $view -> render( 'edit' );
  }
  
  public function draw()
  {    
    $this -> name ? $g_table = $this -> name : $g_table = $this -> table;
    
    $db = $this -> connectToDb();
    
    $results = $db -> get( 'grid_settings', 'settings', [ 'name' => $g_table ] );
    $results = unserialize( $results );
    
    $this -> hidden_columns = $results['hidden_columns'];
    
    
    
    (int)$_SESSION[ 'g' . $g_table . 'cp' ] ? $this -> cp = (int)$_SESSION[ 'g' . $g_table . 'cp' ] : $this -> cp = 1;    
    
    $_SESSION[ 'g' . $g_table ] = $this;
    
    $values             = get_object_vars( $this );
    $values['content']  = $this -> drawResults();
    
    $view = new gridView( $this -> dir . '/templates/' );
    $view -> values = $values;
    return $view -> render( 'container' );
  }
  
  public function connectToDb()
  {
    return new gdb( [
                    'database_type' => $this -> gdb_opt['database_type'],
                    'database_name' => $this -> gdb_opt['database_name'],
                    'server'        => $this -> gdb_opt['server'],
                    'username'      => $this -> gdb_opt['username'],
                    'password'      => $this -> gdb_opt['password'],
                    'port'          => $this -> gdb_opt['port'],
                    'charset'       => 'utf8'
                  ] );
  }
  
  public function getCSV()
  {
    if ( is_array( $this -> src ) )
      $results = $this -> getDataSrc();
    else
      $results = $this -> getData( true );
    
    if ( is_array( $this -> columns_view ) ) foreach ( $this -> columns_view as $column )
    {
      $array_row = array();
      if ( $column['action'] == null and $column['autoincrement'] == null )
      {
        $headers[] = $column['name'];
      }
    }
    
    if ( is_array( $results ) ) foreach ( $results as $row )
    {
      $array_row = array();
      if ( is_array( $this -> columns_view ) ) foreach ( $this -> columns_view as $column )
      {
        if ( $column['action'] == null and $column['autoincrement'] == null )
        {
          if ( $column['content'] )
            $array_row[] = $this -> convertString( $column['content'], $row );
          else
          {
            /* podmiana na wartości tablicy */
            if ( is_array( $column['replace']['array'] ) )
              $array_row[] = $this -> convertString( $column['replace']['array'][ $row[ $column['db'] ] ], $row );

            else if ( $column['replace']['sql'] )
            {
              $sql  = $this -> convertString( $column['replace']['sql'], $row );
              $results = $this -> connectToDb() -> query( $sql ) -> fetchAll();

              if ( $results[0][0] )
                $array_row[] = $results[0][0];
              else
                $array_row[] = '-';
            }

            /* modyfikacja wyświetlania daty */
            else if ( $column['date_mod'] )
            {
              if ( $this -> validateDate( $row[ $column['db'] ] ) )
                $array_row[] = date( $column['date_mod'], strtotime( $row[ $column['db'] ] ) );
              else
                $array_row[] = '-';
            }

            /* zwykla wartość */
            else
              $array_row[] = $row[ $column['db'] ];
          }
        }
      }
      $array[] = $array_row;
    }
    
    $now = gmdate( "D, d M Y H:i:s" );
    
    header( 'Content-Encoding: UTF-8' );
    header( "Expires: Tue, 03 Jul 2001 06:00:00 GMT" );
    header( "Cache-Control: max-age=0, no-cache, must-revalidate, proxy-revalidate" );
    header( "Last-Modified: {$now} GMT" );

    // force download  
    header( "Content-Type: application/force-download" );
    header( "Content-Type: application/octet-stream" );
    header( "Content-Type: application/download" );

    // disposition / encoding on response body
    header( "Content-Disposition: attachment;filename=eksport.csv" );
    header( "Content-Transfer-Encoding: binary" );
    ob_start();
    echo "\xEF\xBB\xBF";
    $df = fopen( "php://output", 'w' );
    fputcsv( $df, $headers, ';' );
    if ( is_array( $array ) ) foreach ( $array as $row )
      fputcsv( $df, $row, ';');
    fclose( $df );
   return ob_get_clean();
  }
  
  public function printResults()
  {
    $values = get_object_vars( $this );
    
    if ( is_array( $this -> src ) )
    {
      $values['count']  = $this -> getDataCountSrc();
    }
    else
    {
      $values['count']    = $this -> getDataCount();
      $values['summary']  = $this -> getDataSummary();
    }
    
    $this -> cp     = 1;
    $this -> limit  = $values['count'];
    
    if ( is_array( $this -> src ) )
      $values['results'] = $this -> getDataSrc();
    else
      $values['results'] = $this -> getData();
    
    $view = new gridView( $this -> dir . '/templates/' );
    $view -> values = $values;
    return $view -> render( 'print' );
  }
  
  public function get_data_count_sql()
  {    
    $db = $this ->connectToDb();
    
    $where = $this -> getWhereCondition();
    $where = $db -> whereClause( $where );
    
    if ( strpos( $this -> sql_count, 'WHERE' ) !== false and !empty( $where ) )
      $where = str_replace( 'WHERE', 'AND', $where );
    
    $this -> sql_tmp = str_replace( '[where]', $where, $this -> sql_count );

    $query = $db -> query( $this -> sql_tmp );
    if ( $query )
    {
      $results = $query -> fetchAll();
      return $results[0][0];
    }
    else
    {
      if ( $this -> debug )
      {
        echo '<div class="alert alert-info">' . $this -> sql_tmp . '</div>';
        echo '<div class="alert alert-danger">' . $db -> error()[2] . '</div>';
      }
      return 0;
    }
  }
  
  public function get_data_sql( $print = false )
  {
    $db = $this -> connectToDb();
    
    $where = $this -> getWhereCondition();
    $where = $db -> whereClause( $where );
    
    if ( strpos( $this -> sql, 'WHERE' ) !== false and !empty( $where ) )
      $where = str_replace( 'WHERE', 'AND', $where );

    if ( !$print )
      $this -> sql_tmp  = $this -> sql . ' LIMIT ' . $this ->  limit . ' OFFSET ' . ( $this -> cp - 1 ) * $this -> limit;
    else
      $this -> sql_tmp  = $this -> sql;
    $this -> sql_tmp  = str_replace( '[where]', $where, $this -> sql_tmp ); 
    $this -> sql_tmp  = str_replace( '[order_p1]', $this -> order['column'], $this -> sql_tmp );
    $this -> sql_tmp  = str_replace( '[order_p2]', $this -> order['type'], $this -> sql_tmp );
    
    $query = $db -> query( $this -> sql_tmp );
    if ( $query )
    {
      $results = $query -> fetchAll();
      return $results;
    }
    else
    {
      if ( $this -> debug )
      {
        echo '<div class="alert alert-info">' . $this -> sql_tmp . '</div>';
        echo '<div class="alert alert-danger">' . $db -> error()[2] . '</div>';
      }
      return null;
    }
  }
  
  public function drawResults()
  {
    $values = get_object_vars( $this );
    
    if ( is_array( $this -> src ) )
      $values['count']  = $this -> getDataCountSrc();
    else if ( isset( $this -> sql ) and isset( $this -> sql_count ) )
      $values['count'] = $this -> get_data_count_sql();
    else
    {
      $values['count']    = $this -> getDataCount();
      $values['summary']  = $this -> getDataSummary();
    }
    
    $ls = ceil( $values['count'] / $this -> limit  );
    
    if ( !(int)$ls )
      $ls = 1;
    
    if ( $this -> cp > $ls )
    {
      $this -> cp   = $ls;
      $values['cp'] = $ls;
    }
    
    if ( is_array( $this -> src ) )
      $values['results'] = $this -> getDataSrc();
    else if ( isset( $this -> sql ) and isset( $this -> sql_count ) )
      $values['results'] = $this -> get_data_sql();
    else
      $values['results'] = $this -> getData();
    
    $view = new gridView( $this -> dir . '/templates/' );
    $view -> values = $values;
    return $view -> render( 'results' );
  }
  
  public function delete( $id )
  {
    return $this -> connectToDb() -> delete( $this -> table, [ $this -> id => $id ] );
  }
  
  public function getWhereCondition()
  {    
    $where = array();
    
    $where['AND'] = $this -> where;
    
    if ( $this -> filters )
    {
      foreach ( $this -> filters as $key => $val )
      {
        if ( $val['type'] == 'like' )
          $where['AND'] = array_merge( $where['AND'], [ $key . '[~]' => $val['value'] ] );
        
        if ( $val['type'] == 'equal' )
          $where['AND'] = array_merge( $where['AND'], [ $key => $val['value'] ] );
        
        if ( $val['type'] == 'date_range' )
        {
          $dates = explode( ' - ', $val['value'] );
          $where['AND'] = array_merge( $where['AND'], [ $key . '[>=]' => trim( $dates[0] ) ] );
          $where['AND'] = array_merge( $where['AND'], [ $key . '[<=]' => trim( $dates[1] ) ] );
        }
      }
    }
    
    if ( count( $where['AND'] ) )
      return $where;
    else
      return array();
  }
  
  public static function searchSrc( $array, $column, $value, $type = 'equal' )
  {
    if ( is_array( $array ) ) foreach ( $array as $key => $val )
    {
      if ( $type == 'equal' )
      {
        if ( $val[ $column ] == $value )
          $array_tmp[] = $val;
      }
      
      if ( $type == 'like' )
      {
        if ( strpos( mb_strtolower( $val[ $column ], 'UTF-8' ), mb_strtolower( $value, 'UTF-8' ) ) !== false )
          $array_tmp[] = $val;
      }
    }
    return $array_tmp;
  }
  
  public function filtrDataSrc()
  {
    $this -> src_filtered = $this -> src;
    
    if ( $this -> filters )
    {
      foreach ( $this -> filters as $key => $val )
      {
        if ( $val['type'] == 'like' )
          $this -> src_filtered = $this -> searchSrc( $this -> src_filtered, $key, $val['value'], 'like' );
        
        if ( $val['type'] == 'equal' )
          $this -> src_filtered = $this -> searchSrc( $this -> src_filtered, $key, $val['value'], 'equal' );
      }
    }
  }
  
  public function getDataSummary()
  {
    $where = self::getWhereCondition();
    
    if ( is_array( $this -> summary ) ) foreach ( $this -> summary as $key )
    {
      if ( $this -> join )
        $summary[ $key ] = $this -> connectToDb() -> sum( $this -> table, $this -> join, $key, $where );
      else
        $summary[ $key ] = $this -> connectToDb() -> sum( $this -> table, $key, $where );
    }
    return $summary;
  }
  
  public function getDataCountSrc()
  {
    $this -> filtrDataSrc();
    return count( $this -> src_filtered );
  }
  
  public function getDataCount()
  {
    $where = self::getWhereCondition();
        
    if ( $this -> join )
      $results = $this -> connectToDb() -> count( $this -> table, $this -> join, '*', $where );
    else
      $results = $this -> connectToDb() -> count( $this -> table, $where );
    
    if ( $results )
      return $results;
    else
      return false;
  }
  
  public function getDataSrc()
  {
    if ( $this -> order )
      $this -> src_filtered = $this -> sortByColumn( $this -> src_filtered, $this -> order['column'], $this -> order['type'] );
    
    $array_tmp = $this -> src_filtered;
    
    if ( is_array( $array_tmp ) )
      return array_splice( $array_tmp, ( $this -> cp - 1 ) * $this -> limit, $this -> limit );
    else
      return false;      
  }
  
  public function getData( $csv = false )
  {
    $where = self::getWhereCondition();
    
    if ( $this -> order )
      $where = array_merge( $where, [ 'ORDER' => [ $this -> order['column'] => $this -> order['type'] ] ] );
    
    if ( $this -> limit and $this -> show_paging === true and !$csv )
      $where = array_merge( $where, [ 'LIMIT' => [ ( $this -> cp - 1 ) * $this -> limit, $this ->  limit ] ] );

    if ( $this -> join )
      $results = $this -> connectToDb() -> select( $this -> table, $this -> join, $this -> columns, $where );
    else
      $results = $this -> connectToDb() -> select( $this -> table, $this -> columns, $where );
    
    if ( $results )
      return $results;
    else
      return false;
  }
  
  public function getElement( $id )
  {
    return $this -> connectToDb() -> get( $this -> table, '*', [ $this -> id => $id ] );
  }
  
  public function saveElement( $values )
  {
    if ( !$values[ $this -> id ] )
    {
      unset( $values[ $this -> id ] );
      return $this -> connectToDb() -> insert( $this -> table, $values );
    }
    else
      return $this -> connectToDb() -> update( $this -> table, $values, [ $this -> id => $values[ $this -> id ] ] );
  }
  
  public static function sortByColumn( &$arr, $col, $sort ) 
  {
    setlocale( LC_COLLATE, 'pl_PL.utf-8' ); 
    $sort == 'ASC' ? $dir = SORT_ASC : $dir = SORT_DESC;
    
    $sort_col = array();
    if ( is_array( $arr ) )
    {
        foreach ( $arr as $key=> $row )
          $sort_col[$key] = $row[$col];

      array_multisort( $sort_col, $dir, SORT_REGULAR, $arr );
    }
    return $arr;
  }
  
  public static function validateDate( $date )
  {
    if ( date( 'Y-m-d', strtotime( $date ) ) != '1970-01-01' )
      return true;
  }
  
  public function save_limit( $limit )
  {
    $this -> name ? $g_table = $this -> name : $g_table = $this -> table;
    
    $_SESSION[ 'g' . $g_table . 'limit' ] = $limit;
  }
  
  public function save_order()
  {
    $this -> name ? $g_table = $this -> name : $g_table = $this -> table;
    
    $_SESSION[ 'g' . $g_table . 'order' ] = $this -> order;
  }
  
  public function save_filters()
  {
    $this -> name ? $g_table = $this -> name : $g_table = $this -> table;
    
    $_SESSION[ 'g' . $g_table . 'filters' ] = $this -> filters;
  }
  
  public function set_cp( $cp )
  {
    $this -> name ? $g_table = $this -> name : $g_table = $this -> table;
    
    $_SESSION[ 'g' . $g_table . 'cp'] = $cp;
    $this -> cp = $cp;
  }
  
  public function getParams( $string )
  {
    $params = array();
    
    preg_match_all( '/\[[a-zA-Z0-9_]*\]/', $string, $results_tmp1 );
    if ( is_array( $results_tmp1[0] ) ) foreach ( $results_tmp1[0] as $row_tmp1 )
    {
      preg_match_all( '/[a-zA-Z0-9_]*/', $row_tmp1, $results_tmp2 );
      if ( is_array( $results_tmp2[0] ) ) foreach ( $results_tmp2[0] as $row_tmp2 )
        if ( $row_tmp2 )
          $params[] = $row_tmp2;
    }
    return $params;
  }
  
  public function  convertString( $string, $row )
  {
    $out = $string;

    preg_match_all( '/\[[a-zA-Z0-9_]*\]/', $string, $results_tmp1 );
    if ( is_array( $results_tmp1[0] ) ) foreach ( $results_tmp1[0] as $row_tmp1 )
    {
      preg_match_all( '/[a-zA-Z0-9_]*/', $row_tmp1, $results_tmp2 );
      if ( is_array( $results_tmp2[0] ) ) foreach ( $results_tmp2[0] as $row_tmp2 )
        if ( $row_tmp2 )
          $out = str_replace( '[' . $row_tmp2 . ']', addslashes( htmlspecialchars( $row[ $row_tmp2 ] ) ), $out );
    }
    return $out;
  }
  
  public function convertStringJS( $string )
  {
    $out = 'var out = "' . addslashes( $string ) . '";' . chr( 13 );
    
    preg_match_all( '/\[[a-zA-Z0-9_]*\]/', $string, $results_tmp1 );
    if ( is_array( $results_tmp1[0] ) ) foreach ( $results_tmp1[0] as $row_tmp1 )
    {
      preg_match_all( '/[a-zA-Z0-9_]*/', $row_tmp1, $results_tmp2 );
      if ( is_array( $results_tmp2[0] ) ) foreach ( $results_tmp2[0] as $row_tmp2 )
        if ( $row_tmp2 )
          $out .= 'out = out.replace( "[' . $row_tmp2 . ']", selector.attr( "' . $row_tmp2 . '" ) );' . chr( 13 );
    }
    return $out;
  }
}