617 lines
18 KiB
PHP
617 lines
18 KiB
PHP
<?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;
|
|
}
|
|
} |