<?php

/*
 * Helper functions for building a DataTables server-side processing SQL query
 *
 * The static functions in this class are just helper functions to help build
 * the SQL used in the DataTables demo server-side processing scripts. These
 * functions obviously do not represent all that can be done with server-side
 * processing, they are intentionally simple to show how it works. More complex
 * server-side processing operations will likely require a custom script.
 *
 * See http://datatables.net/usage/server-side for full details on the server-
 * side processing requirements of DataTables.
 *
 * @license MIT - http://datatables.net/license_mit
 */

class DataTable {
    /**
     * Create the data output array for the DataTables rows
     *
     *  @param  array $columns Column information array
     *  @param  array $data    Data from the SQL get
     *  @return array          Formatted data in a row based format
     */
//    static function data_output($columns, $data, $request = array(), $is_export = false) {
//        $out = array();
//        for ($i = 0, $ien = count($data); $i < $ien; $i++) {
//            $row = array();
//            for ($j = 0, $jen = count($columns); $j < $jen; $j++) {
//                $column = $columns[$j];
//                if ($is_export && is_array($request['ExportedColumns']) && !in_array($column['dt'], $request['ExportedColumns']))
//                    continue;
//                // Is there a formatter?
//                if (isset($column['formatter']))
//                    $row[$column['dt']] = $column['formatter']($data[$i][$column['db']], $data[$i], $is_export);
//                else
//                    $row[$column['dt']] = $data[$i][$columns[$j]['db']];
//            }
//            $out[] = $row;
//        }
//        return $out;
//    }

    /**
     * Database connection
     *
     * Obtain an PHP PDO connection from a connection details array
     *
     *  @param  array $conn SQL connection details. The array should have
     *    the following properties
     *     * host - host name
     *     * db   - database name
     *     * user - user name
     *     * pass - user password
     *  @return resource PDO connection
     */
    static function db() {
        return self::sql_connect();
    }

    /**
     * Paging
     *
     * Construct the LIMIT clause for server-side processing SQL query
     *
     *  @param  array $request Data sent to server by DataTables
     *  @param  array $columns Column information array
     *  @return string SQL limit clause
     */
    static function limit($request, $columns) {
        $limit = '';

        if (isset($request['start']) && $request['length'] != -1) {
            $limit = "LIMIT " . intval($request['start']) . ", " . intval($request['length']);
        }

        return $limit;
    }

    /**
     * Ordering
     *
     * Construct the ORDER BY clause for server-side processing SQL query
     *
     *  @param  array $request Data sent to server by DataTables
     *  @param  array $columns Column information array
     *  @return string SQL order by clause
     */
    static function order($request, $columns) {
        $order = '';

        if (isset($request['order']) && count($request['order'])) {

            $orderBy = array();
            $dtColumns = self::pluck($columns, 'dt');

            for ($i = 0, $ien = count($request['order']); $i < $ien; $i++) {
                if (empty($request['order'][$i]))
                    continue;
                // Convert the column index into the column data property
                $columnIdx = intval($request['order'][$i]['column']);
                $requestColumn = $request['columns'][$columnIdx];
                $columnIdx = array_search($requestColumn['data'], $dtColumns);
                $column = $columns[$columnIdx];
                if ($requestColumn['orderable'] == 'true') {
                    $dir = $request['order'][$i]['dir'] === 'asc' ? 'ASC' : 'DESC';
                    $orderBy[] = '`' . $column['db'] . '` ' . $dir;
                }
            }
            if (!empty($orderBy))
                $order = 'ORDER BY ' . implode(',', $orderBy);
        }
        return $order;
    }

    /**
     * Searching / Filtering
     *
     * Construct the WHERE clause for server-side processing SQL query.
     *
     * NOTE this does not match the built-in DataTables filtering which does it
     * word by word on any field. It's possible to do here performance on large
     * databases would be very poor
     *
     *  @param  array $request Data sent to server by DataTables
     *  @param  array $columns Column information array
     *  @param  array $bindings Array of values for PDO bindings, used in the
     *    sql_exec() function
     *  @return string SQL where clause
     */
    static function filter($request, $columns, &$bindings, $is_group = false) {
        $globalSearch = array();
        $columnSearch = array();
        $dtColumns = self::pluck($columns, 'dt');

        if (isset($request['search']) && $request['search']['value'] != '') {
            $str = $request['search']['value'];
            for ($i = 0, $ien = count($request['columns']); $i < $ien; $i++) {
                $requestColumn = $request['columns'][$i];
                $columnIdx = array_search($requestColumn['data'], $dtColumns);
                $column = $columns[$columnIdx];
//                preg_match('/.*\(.*\).*/', $column['db'], $match);
                if ($requestColumn['searchable'] == 'true' /* && empty($match) */) {
                    $tmp_str = $str;
                    if (isset($column['full_search']) && $column['full_search'] == true)
                        $tmp_str = str_replace(" ", "%", trim($tmp_str));
                    $binding = self::bind($bindings, '%' . $tmp_str . '%', PDO::PARAM_STR);
                    //below if column is "col1*col2" or concat or .. then it give error since `col1*col2` like '..'
                    $fltr_col = preg_match("/^[a-zA-Z]+$/", $column['db']) ? "`" . $column['db'] . "`" : $column['db'];
                    $globalSearch[] = $fltr_col . " LIKE " . $binding;
                }
            }
        }

        // Individual column filtering
        if (isset($request['columns'])) {
            for ($i = 0, $ien = count($request['columns']); $i < $ien; $i++) {
                $requestColumn = $request['columns'][$i];
                $columnIdx = array_search($requestColumn['data'], $dtColumns);
                $column = $columns[$columnIdx];
                $str = $requestColumn['search']['value'];
                if ($requestColumn['searchable'] == 'true' && $str != '') {
                    if (isset($column['full_search']) && $column['full_search'] == true)
                        $str = str_replace(" ", "%", trim($str));
                    $binding = self::bind($bindings, '%' . $str . '%', PDO::PARAM_STR);
                    $fltr_col = preg_match("/^[a-zA-Z]+$/", $column['db']) ? "`" . $column['db'] . "`" : $column['db'];
                    $columnSearch[] = $fltr_col . " LIKE " . $binding;
                }
            }
        }

        // Combine the filters into a single string
        $where = '';

        if (count($globalSearch)) {
            $where = '(' . implode(' OR ', $globalSearch) . ')';
        }

        if (count($columnSearch)) {
            $where = $where === '' ?
                    implode(' AND ', $columnSearch) :
                    $where . ' AND ' . implode(' AND ', $columnSearch);
        }

        if ($where !== '') {
            $where = ($is_group ? ' HAVING ' : ' WHERE ') . $where;
        }

        return $where;
    }

    /**
     * Perform the SQL queries needed for an server-side processing requested,
     * utilising the helper functions of this class, limit(), order() and
     * filter() among others. The returned array is ready to be encoded as JSON
     * in response to an SSP request, or can be modified if needed before
     * sending back to the client.
     *
     *  @param  array $request Data sent to server by DataTables
     *  @param  array|PDO $conn PDO connection resource or connection parameters array
     *  @param  string $table SQL table to query
     *  @param  string $primaryKey Primary key of the table
     *  @param  array $columns Column information array
     *  @return array          Server-side processing response array
     */
    static function simple($request, $table, $primaryKey, $columns) {
        $bindings = array();
        $db = self::db();
        self::parse_fix_columns_arr($columns);
        $is_export = !empty($request['smc__is_export_csv']);
        // Build the SQL query string from the request
        $limit = $is_export ? "" : self::limit($request, $columns);
        $order = self::order($request, $columns);
        $where = self::filter($request, $columns, $bindings);

        // Main query to actually get the data
        $data = self::sql_exec($db, $bindings, "SELECT `" . implode("`, `", self::pluck($columns, 'db')) . "`
			 FROM `$table`
			 $where
			 $order
			 $limit", ['col' => $columns, 'req' => $request, 'exp' => $is_export]
        );
        if (!$is_export) {
            // Data set length after filtering
            $resFilterLength = self::sql_exec($db, $bindings, "SELECT COUNT(`{$primaryKey}`)
                             FROM   `$table`
                             $where"
            );
            $recordsFiltered = $resFilterLength[0][0];

            // Total data set length
            $resTotalLength = self::sql_exec($db, "SELECT COUNT(`{$primaryKey}`)
                             FROM   `$table`"
            );
            $recordsTotal = $resTotalLength[0][0];
        }
        /*
         * Output
         */
//        $result_data = self::data_output($columns, $data, $request, $is_export);
        //
        if ($is_export)
            self::auto_export_data($table, $columns, $data, $request);
        //
        return array(
            "draw" => isset($request['draw']) ? intval($request['draw']) : 0,
            "recordsTotal" => intval($recordsTotal),
            "recordsFiltered" => intval($recordsFiltered),
            "data" => $data
        );
    }

    /**
     * The difference between this method and the `simple` one, is that you can
     * apply additional `where` conditions to the SQL queries. These can be in
     * one of two forms:
     *
     * * 'Result condition' - This is applied to the result set, but not the
     *   overall paging information query - i.e. it will not effect the number
     *   of records that a user sees they can have access to. This should be
     *   used when you want apply a filtering condition that the user has sent.
     * * 'All condition' - This is applied to all queries that are made and
     *   reduces the number of records that the user can access. This should be
     *   used in conditions where you don't want the user to ever have access to
     *   particular records (for example, restricting by a login id).
     *
     *  @param  array $request Data sent to server by DataTables
     *  @param  string $table SQL table to query
     *  @param  string $primaryKey Primary key of the table
     *  @param  array $columns Column information array
     *  @param  string $whereResult WHERE condition to apply to the result set
     *  @param  string $whereAll WHERE condition to apply to all queries
     *  @return array          Server-side processing response array
     */
    static function complex($request, $table, $primaryKey, $columns, $whereResult = null, $whereAll = null, $group = null, $optns = array()) {
        $bindings = array();
        $db = self::db();
        self::parse_fix_columns_arr($columns);
        $localWhereResult = array();
        $localWhereAll = array();
        $whereAllSql = '';
        $group = empty($group) ? '' : $group;
        $is_export = !empty($request['smc__is_export_csv']);
        // Build the SQL query string from the request
        $limit = $is_export ? "" : self::limit($request, $columns);
        $order = self::order($request, $columns);
        if (empty($order) && !empty($optns['order']))
            $order = " order by " . $optns['order'];
        $where_have = '';
        $where = '';
        if (empty($group))
            $where = self::filter($request, $columns, $bindings);
        else {
            $where_have = self::filter($request, $columns, $bindings, true);
            //if already there is having in group by then remove it below
            if (strpos(strtolower($group), ' having ') !== false && !empty($where_have))
                $where_have = ' and ' . substr($where_have, 8);
        }
        $whereResult = self::_flatten($whereResult);
        $whereAll = self::_flatten($whereAll);

        if ($whereResult) {
            $where = $where ?
                    $where . ' AND ' . $whereResult :
                    'WHERE ' . $whereResult;
        }
        //
        if ($whereAll) {
            $where = $where ?
                    $where . ' AND ' . $whereAll :
                    'WHERE ' . $whereAll;

            $whereAllSql = 'WHERE ' . $whereAll;
        }
        // Main query to actually get the data
        if (!empty($optns['is_favorite'])) {
            $original_cb = [];
            if (!empty($optns['fetch_row_callback']))
                $original_cb['fetch_row_callback'] = $optns['fetch_row_callback'];
            if (!empty($optns['fetch_all_callback']))
                $original_cb['fetch_all_callback'] = $optns['fetch_all_callback'];
            $tmp_tbl_nm = (is_string($optns['is_favorite']) && $optns['is_favorite'] != '1' ? $optns['is_favorite'] : $table );
            $tmp_cb_fetch = CommonFunctions::favorite_callback_arr($tmp_tbl_nm, $primaryKey, $original_cb);
            $fetch_row_callback = empty($tmp_cb_fetch['fetch_row_callback']) ? null : $tmp_cb_fetch['fetch_row_callback'];
            $fetch_all_callback = empty($tmp_cb_fetch['fetch_all_callback']) ? null : $tmp_cb_fetch['fetch_all_callback'];
        } else {
            $fetch_row_callback = empty($optns['fetch_row_callback']) ? null : $optns['fetch_row_callback'];
            $fetch_all_callback = empty($optns['fetch_all_callback']) ? null : $optns['fetch_all_callback'];
        }
        $query_string = "SELECT " . implode(", ", self::pluck($columns, 'db')) . "
                            FROM {$table}
                        {$where} 
                        {$group} {$where_have}
                        {$order} {$limit}";
        if (!empty($optns['is_debug'])) {
            echo "<pre>" . $query_string . "<br/><br/>";
            print_r($bindings);
            exit;
        }
        $data = self::sql_exec($db, $bindings, $query_string, ['col' => $columns, 'req' => $request, 'exp' => $is_export], $fetch_row_callback, $fetch_all_callback);
        // Data set length after filtering
        if (!$is_export) {
            $slct_flds_for_counts = "";
            if (!empty($where_have)) //since if there is having like he search and not in the select will get error
                $slct_flds_for_counts .= ", " . implode(", ", self::pluck($columns, 'db'));
            $fltrd_rcrds_query = "SELECT COUNT(DISTINCT {$primaryKey}) as __tmp_primary_key_count {$slct_flds_for_counts}
                                 FROM {$table}
                                 {$where} {$group} {$where_have} ";
            if (!empty($group))
                $fltrd_rcrds_query = " select count(__tmp_primary_key_count) from ({$fltrd_rcrds_query}) as tmp_tbl ";
            $resFilterLength = self::sql_exec($db, $bindings, $fltrd_rcrds_query);
            $recordsFiltered = empty($resFilterLength[0]) ? 0 : $resFilterLength[0][0];
            // Total data set length
            $ttl_rcrds_query = "SELECT COUNT({$primaryKey}) as __tmp_primary_key_count {$slct_flds_for_counts}
                                FROM {$table} 
                                {$whereAllSql} {$group} {$where_have} ";
            if (!empty($group))
                $ttl_rcrds_query = " select count(__tmp_primary_key_count) from ({$ttl_rcrds_query}) as tmp_tbl ";
            $resTotalLength = self::sql_exec($db, $bindings, $ttl_rcrds_query);
            $recordsTotal = $resTotalLength[0][0];
        }
        /*
         * Output
         */
//        $result_data = self::data_output($columns, $data, $request, $is_export);
        //
        if ($is_export)
            self::auto_export_data($table, $columns, $data, $request);
        //
        return array(
            "draw" => isset($request['draw']) ? intval($request['draw']) : 0,
            "recordsTotal" => intval($recordsTotal),
            "recordsFiltered" => intval($recordsFiltered),
            "data" => $data
        );
    }

    /**
     * Connect to the database
     *
     * @param  array $sql_details SQL server connection details array, with the
     *   properties:
     *     * host - host name
     *     * db   - database name
     *     * user - user name
     *     * pass - user password
     * @return resource Database connection handle
     */
    static function sql_connect() {
        static $db = null;
        if (is_null($db)) {
            try {
                $db = @new PDO("mysql:host=" . DB_HOST . ";dbname=" . DB_NAME, DB_USER, DB_PASS, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
                $db->query("SET NAMES 'utf8'");
                $db->query("SET CHARACTER SET utf8");
            } catch (PDOException $e) {
                self::fatal(
                        "An error occurred while connecting to the database. " .
                        "The error reported by the server was: " . $e->getMessage()
                );
            }
        }
        return $db;
    }

    /**
     * Execute an SQL query on the database
     *
     * @param  resource $db  Database handler
     * @param  array    $bindings Array of PDO binding values from bind() to be
     *   used for safely escaping strings. Note that this can be given as the
     *   SQL query string if no bindings are required.
     * @param  string   $sql SQL query to execute.
     * @return array         Result from the query (all rows)
     */
    static function sql_exec($db, $bindings, $sql = null, $output_arr = array(), $fetch_row_callback = null, $fetch_all_callback = null) {
//        die($sql);
        // Argument shifting
        if ($sql === null) {
            $sql = $bindings;
        }

        $stmt = $db->prepare($sql);
        //echo $sql;
        // Bind parameters
        if (is_array($bindings)) {
            for ($i = 0, $ien = count($bindings); $i < $ien; $i++) {
                $binding = $bindings[$i];
                $stmt->bindValue($binding['key'], $binding['val'], $binding['type']);
            }
        }

        // Execute
        try {
            $stmt->execute();
        } catch (PDOException $e) {
            self::fatal("An SQL error occurred: " . $e->getMessage());
        }

        // Return all
        $data = array();
        $cb_ft = null;
        if (!empty($fetch_row_callback)) {
            $cb_ft = $fetch_row_callback['ft'];
            $cb_param = empty($fetch_row_callback['param']) ? array() : $fetch_row_callback['param'];
        }
        //
        $columns = array();
        $request = array();
        $is_export = false;
        if (!empty($output_arr)) {
            if (isset($output_arr['col']))
                $columns = $output_arr['col'];
            if (isset($output_arr['req']))
                $request = $output_arr['req'];
            if (isset($output_arr['exp']))
                $is_export = $output_arr['exp'];
        }
        //
        if (empty($cb_ft))
            $data = $stmt->fetchAll(PDO::FETCH_BOTH);
        else {
            while ($row = $stmt->fetch(PDO::FETCH_BOTH)) {
                $tmp_cb_param = array_merge(array(&$data, &$row), $cb_param);
                call_user_func_array($cb_ft, $tmp_cb_param);
                $data[] = $row;
            }
        }
        //
        if (!empty($fetch_all_callback)) {
            $cb_all_ft = $fetch_all_callback['ft'];
            $cb_all_param = empty($fetch_all_callback['param']) ? array() : $fetch_all_callback['param'];
            if (!empty($cb_all_ft)) {
                $cb_all_param = array_merge(array(&$data), $cb_all_param);
                call_user_func_array($cb_all_ft, $cb_all_param);
            }
        }
        //the content of data_output method, we use it here not inside above loop sinec in fetch all we may need to 
        //use $row by key name ex: $row['jv_id'] also we need to overwrite it like $row['abc'] = 'sss' and then we 
        //call the formatter below
        if (!empty($columns)) {
            $tmp_data = $data;
            unset($data);
            $data = array();
            foreach ($tmp_data as $tmp_row) {
                $row = array();
                for ($j = 0, $jen = count($columns); $j < $jen; $j++) {
                    $column = $columns[$j];
                    if ($is_export && is_array($request['ExportedColumns']) && !in_array($column['dt'], $request['ExportedColumns']))
                        continue;
                    // Is there a formatter?
                    if (isset($column['formatter']))
                        $row[$column['dt']] = $column['formatter']($tmp_row[$column['db']], $tmp_row, $is_export);
                    else
                        $row[$column['dt']] = $tmp_row[$columns[$j]['db']];
                    if ($is_export && !isset($column['keep_tags']))
                        $row[$column['dt']] = strip_tags($row[$column['dt']]);
                }
                $data[] = $row;
            }
        }
        //
        return $data;
    }

    /*     * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     * Internal methods
     */

    /**
     * Throw a fatal error.
     *
     * This writes out an error message in a JSON string which DataTables will
     * see and show to the user in the browser.
     *
     * @param  string $msg Message to send to the client
     */
    static function fatal($msg) {
        echo json_encode(array(
            "error" => $msg
        ));

        exit(0);
    }

    /**
     * Create a PDO binding key which can be used for escaping variables safely
     * when executing a query with sql_exec()
     *
     * @param  array &$a    Array of bindings
     * @param  *      $val  Value to bind
     * @param  int    $type PDO field type
     * @return string       Bound key to be used in the SQL where this parameter
     *   would be used.
     */
    static function bind(&$a, $val, $type) {
        $key = ':binding_' . count($a);

        $a[] = array(
            'key' => $key,
            'val' => $val,
            'type' => $type
        );

        return $key;
    }

    public static function parse_fix_columns_arr(&$columns) {
        $tmp_sort_indx = array();
        for ($i = 0, $len = count($columns); $i < $len; $i++) {
            if (!isset($columns[$i]['db'])) {
                if (is_array($columns[$i]))
                    $columns[$i]['db'] = $columns[$i][0];
                else
                    $columns[$i] = array('db' => $columns[$i]);
            }
            if (!isset($columns[$i]['dt']))
                $columns[$i]['dt'] = $i;
            $tmp_sort_indx[$i] = $columns[$i]['dt'];
        }
        //here we resort them depend on the 'dt', since maybe we add col. with specific dt=6 then add without dt 
        //so it for exported csv for ex: it will show this dt=6 as 1st col. for that we sort them here.
        $tmp_columns = array();
        asort($tmp_sort_indx);
        foreach ($tmp_sort_indx as $indx => $dt)
            $tmp_columns[] = $columns[$indx];
        $columns = $tmp_columns;
    }

    /**
     * Pull a particular property from each assoc. array in a numeric array, 
     * returning and array of the property values from each item.
     *
     *  @param  array  $a    Array to get data from
     *  @param  string $prop Property to read
     *  @return array        Array of property values
     */
    static function pluck($a, $prop) {
        $out = array();

        for ($i = 0, $len = count($a); $i < $len; $i++) {
            $out[] = $a[$i][$prop];
        }

        return $out;
    }

    /**
     * Return a string from an array or a string
     *
     * @param  array|string $a Array to join
     * @param  string $join Glue for the concatenation
     * @return string Joined string
     */
    static function _flatten($a, $join = ' AND ') {
        if (!$a) {
            return '';
        } else if ($a && is_array($a)) {
            return implode($join, $a);
        }
        return $a;
    }

    /**
     * export excel called from data table here
     */
    public static function auto_export_data($table, $columns, $data, $request) {
        $file_name = basename(parse_url($_SERVER['REQUEST_URI'], PHP_URL_PATH));
        //
        $header_row = array();
        for ($j = 0, $col_count = count($columns); $j < $col_count; $j++) {
            if (is_array($request['ExportedColumns']) && !in_array($columns[$j]['dt'], $request['ExportedColumns']))
                continue;
            $header_row[] = (isset($request['LabelColumns'][$j]) ? $request['LabelColumns'][$j] : $columns[$j]['db']);
        }
        self::export_data($file_name, $header_row, $data, $columns);
    }

    //
    public static function export_data($file_name, $header_row, $data, $columns = array(), $rows_info = array()) {

        //
        function tmp__excel_fix_cell_data($cell_data, $col_type = 'string') {
            $cell_data = preg_replace('/<br\s?\/?>/ius', "\r\n", $cell_data);
            $cell_data = strip_tags($cell_data);
            if ($col_type == 'number') {
                //remove comma format
                $cell_data = str_replace(',', '', $cell_data);
                //in case of negative we are putting number in () ex: -50 it will be (50) so here we fix it
                if (strpos($cell_data, "(") !== false)
                    $cell_data = ("-" . str_replace(array('(', ')'), '', $cell_data));
            }
            return $cell_data;
        }

        //
        $is_too_large_data = (count($data) > 100000);
        //if not too large data then export it as php excel
        if (!$is_too_large_data) {
            $objPHPExcel = new PHPExcel();
            $objPHPExcel->getProperties()->setCreator("Click Round")->setLastModifiedBy("Clic Round")
                    ->setTitle("HXA System - " . $file_name)->setSubject("HXA System - " . $file_name)
                    ->setDescription("HXA System")->setKeywords("HXA Click Round")->setCategory("HXA - Click Round");
            $objPHPExcel->setActiveSheetIndex(0);
            $sheet = $objPHPExcel->getActiveSheet();
            //Rename worksheet | Max. 31 characters allowed
            $sheet_title = mb_strlen($file_name) <= 31 ? $file_name : (mb_substr($file_name, 0, 29) . '..');
            $sheet->setTitle($sheet_title);
            //freeze first row
            $sheet->freezePane('A2');   //to freeze first col. & row use 'B2'
            //output the column headings
            $crnt_row = 1;
            $crnt_col = null;
            foreach ($header_row as $col_label) {
                $crnt_col = (is_null($crnt_col) ? 'A' : ++$crnt_col);
                $col_label = tmp__excel_fix_cell_data($col_label);
                $sheet->setCellValueExplicit($crnt_col . $crnt_row, $col_label);
            }
            //$sheet->getStyle("A{$crnt_row}:{$crnt_col}{$crnt_row}")->getFont()->setBold(true)->setSize(15);
            $sheet->getStyle("A{$crnt_row}:{$crnt_col}{$crnt_row}")->applyFromArray(array(
                "font" => array("bold" => true, 'size' => 15, 'color' => array('rgb' => 'FFFFFF'))
                , "fill" => array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'color' => array('rgb' => '0000FF'))
                , "borders" => array("allborders" => array("style" => PHPExcel_Style_Border::BORDER_MEDIUM, "color" => array("rgb" => "000000")))
                , "alignment" => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER)));
            //
            foreach ($data as $data_indx => $data_row) {
                $crnt_row++;
                $crnt_col = 'A';
                foreach ($data_row as $col_indx => $col_value) {
                    $cell_indx = ($crnt_col++) . $crnt_row;
                    $col_type = isset($columns[$col_indx]['col_type']) ? $columns[$col_indx]['col_type'] : 'string';
                    $col_value = tmp__excel_fix_cell_data($col_value, $col_type);
                    //we can set explicit specific type ex: BOOL, NUMERIC, STRING, FORMULA,.. [default string if don't send it]
                    if ($col_type == 'string')  //here for example a value "0015" we want to keep it as it is string and not be cast it to number "15"
                        $sheet->setCellValueExplicit($cell_indx, $col_value, PHPExcel_Cell_DataType::TYPE_STRING);
                    else    //number, auto: php will work out what datatype it should use
                        $sheet->setCellValue($cell_indx, $col_value);
                }
                //
                if (!empty($rows_info[$data_indx]['is_bold']))
                    $sheet->getStyle("A{$crnt_row}:{$crnt_col}{$crnt_row}")->getFont()->setBold(true);
            }
            //here for more accurate we can include below line but it may decrease performance [must test it on large data 1st]
            //PHPExcel_Shared_Font::setAutoSizeMethod(PHPExcel_Shared_Font::AUTOSIZE_METHOD_EXACT);
            for ($col = 'A'; $col < $crnt_col; $col++)
                $sheet->getColumnDimension($col)->setAutoSize(true);
            //sheet will not calc. auto size until necessary so we call this to force calc. it since we need it
            $sheet->calculateColumnWidths();
            //here if col. width is more than max. width so set width to max. width and make it wrap
            $max_col_width = 50;
            $col_indx = 0;
            for ($col = 'A'; $col < $crnt_col; $col++) {
                $col_width = $sheet->getColumnDimension($col)->getWidth();
                if ($col_width > $max_col_width) {
                    $sheet->getColumnDimension($col)->setAutoSize(false)->setWidth($max_col_width);
                    $sheet->getStyle("{$col}1:{$col}{$crnt_row}")->getAlignment()->setWrapText(true);
                }
                $col_type = isset($columns[$col_indx]['col_type']) ? $columns[$col_indx]['col_type'] : 'string';
                if ($col_type == 'number')
                    $sheet->getStyle("{$col}2:{$col}{$crnt_row}")->getNumberFormat()
                            ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
                $col_indx++;
            }
            //set default vertical & horizontal align
            $vrtcl_align = PHPExcel_Style_Alignment::VERTICAL_TOP;
            $hrzntl_align = PHPExcel_Style_Alignment::HORIZONTAL_CENTER;
            if (function_exists('smc_session')) {
                $ssn_tbl_align = smc_session()['default_setting']['datatable_default_align'];
                $lang_code = empty(smc_session()['lang_code']) ? 'en' : smc_session()['lang_code'];
                if ($ssn_tbl_align == 'right' || ($ssn_tbl_align == 'language' && $lang_code == 'ar'))
                    $hrzntl_align = PHPExcel_Style_Alignment::HORIZONTAL_RIGHT;
                elseif ($ssn_tbl_align == 'left' || ($ssn_tbl_align == 'language' && $lang_code != 'ar'))
                    $hrzntl_align = PHPExcel_Style_Alignment::HORIZONTAL_LEFT;
            }
            $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal($hrzntl_align)->setVertical($vrtcl_align);
            //if want to protect cells
            //$sheet->getProtection()->setSheet(true);	
            //$sheet->protectCells('A3:E13', 'PHPExcel');
            // Set active sheet index to the first sheet, so Excel opens this as the first sheet
            $objPHPExcel->setActiveSheetIndex(0);
            $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
            // Redirect output to a client’s web browser (Excel2007)
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment;filename="' . $file_name . '.xlsx"');
            header('Cache-Control: max-age=0');
            // If you're serving to IE 9, then the following may be needed
            header('Cache-Control: max-age=1');
            // If you're serving to IE over SSL, then the following may be needed
            header('Expires: Tue, 22 Oct 2019 05:00:00 GMT'); // Date in the past
            header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
            header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
            header('Pragma: public'); // HTTP/1.0
            $objWriter->save('php://output');
        }
        //in case of too large data then export csv
        else {
            header('Content-Type: text/csv; charset=utf-8');
            header('Content-Disposition: attachment; filename=' . $file_name . '.csv');
            // create a file pointer connected to the output stream
            $output_file = fopen('php://output', 'w');
            // output the column headings
            fprintf($output_file, chr(0xEF) . chr(0xBB) . chr(0xBF)); //for arrabic, add BOM to fix utf-8 encoding
            fputcsv($output_file, $header_row);
            // output the data result
            foreach ($data as $data_row)
                fputcsv($output_file, $data_row);
        }
        exit;
    }

}
