Category Archives: Web Tools

Datatable Server Side Processing (Laravel)

Javascript:

var dt_members = function(){
    var $c = null;
    var $t = null;
    var datatable = null;

    function init(){
        $c = $(".main-content");
        $t = $c.find('#dt_members');

        $t.find('input[name="sell_id"]').on('click', function(e){
            var $this = $(this);
            if($this.prop('checked') == true){
                total += parseInt($this.data('qty'));
            }else{
                total -= parseInt($this.data('qty'));
            }

            refresh_total();
        });

        init_dt();
    }

    function init_dt(){
        datatable = $t.DataTable({
            "searching": true,
            "info": true,
            "lengthChange": true,
            "paging": true,
            'serverSide': true, 
            'processing': true, 
            "ajax": {
                'url': base_url + 'admin/ajax/members',
                'data': function(d){
                    //var $c = $('#c-tabs-1');
                    //d.keyword = get_value('keyword', $c);
                }
            },
            //"order": [[ 6, "asc" ], [ 5, "" ]], 
            "columnDefs": [
                {
                    "targets": [ 1 ],
                    "visible": true,
                    "searchable": false
                }
            ], 
            "drawCallback": function( settings ) {
                var api = this.api();
                //wrap.loaded(settings);
                loaded(settings);
            }, 
            'initComplete': function(settings, json){
                //loaded(settings);
            },
            "language" : {
                "url": "/plugins/datatable_zhtw.json"
            }
        });
    }

    function loaded(oSettings){
        $t.find('.action_edit').on('click', function(e){
            e.preventDefault();
            var $this = $(this);
            var member_id = $this.data('id');
            
            member_app.show(member_id, 'edit');
        });

        $t.find('.action_buy').on('click', function(e){
            e.preventDefault();
            var $this = $(this);
            var member_id = $this.data('id');
            
            ticket_app.show(member_id, 'buy');
        });

        $t.find('.action_sell').on('click', function(e){
            e.preventDefault();
            var $this = $(this);
            var member_id = $this.data('id');
            
            ticket_app.show(member_id, 'sell');
        });
    }

    function refresh(){
        //d_table.fnDestroy();
        if(datatable != null){
            datatable.destroy();
        }
        //var $t = $c.find('.datatable');
        //console.log($.fn.dataTable.isDataTable($t));
        
        init_dt();
    }

    return {
        init: init, 
        refresh: refresh
    }
}();

100

101

102

var dt_members = function(){

var $c = null;

var $t = null;

var datatable = null;

function init(){

$c = $(".main-content");

$t = $c.find('#dt_members');

$t.find('input[name="sell_id"]').on('click', function(e){

var $this = $(this);

if($this.prop('checked') == true){

total += parseInt($this.data('qty'));

}else{

total -= parseInt($this.data('qty'));

}

refresh_total();

});

init_dt();

}

function init_dt(){

datatable = $t.DataTable({

"searching": true,

"info": true,

"lengthChange": true,

"paging": true,

'serverSide': true,

'processing': true,

"ajax": {

'url': base_url + 'admin/ajax/members',

'data': function(d){

//var $c = $('#c-tabs-1');

//d.keyword = get_value('keyword', $c);

}

//"order": [[ 6, "asc" ], [ 5, "" ]],

"columnDefs": [

{

"targets": [ 1 ],

"visible": true,

"searchable": false

}

"drawCallback": function( settings ) {

var api = this.api();

//wrap.loaded(settings);

loaded(settings);

'initComplete': function(settings, json){

//loaded(settings);

"language" : {

"url": "/plugins/datatable_zhtw.json"

}

});

}

function loaded(oSettings){

$t.find('.action_edit').on('click', function(e){

e.preventDefault();

var $this = $(this);

var member_id = $this.data('id');

member_app.show(member_id, 'edit');

});

$t.find('.action_buy').on('click', function(e){

e.preventDefault();

var $this = $(this);

var member_id = $this.data('id');

ticket_app.show(member_id, 'buy');

});

$t.find('.action_sell').on('click', function(e){

e.preventDefault();

var $this = $(this);

var member_id = $this.data('id');

ticket_app.show(member_id, 'sell');

});

}

function refresh(){

//d_table.fnDestroy();

if(datatable != null){

datatable.destroy();

}

//var $t = $c.find('.datatable');

//console.log($.fn.dataTable.isDataTable($t));

init_dt();

}

return {

init: init,

refresh: refresh

}

}();

Config:

<?php
return [
    'member_table' => [
        'id' => ['ID', true, 'int'],
        'name' => ['姓名', true, 'int'],
        'email' => ['Email', true, 'string'],
        'phone' => ['電話', true, 'string'],
        'pid' => ['身份證', true, 'string'],
        'status' => ['狀態', true, 'int'],
        'created_time' => ['建立時間', true, 'datetime'], 
        'action' => ['動作', true, 'string']
    ], 
];

<?php

return [

'member_table' => [

'id' => ['ID', true, 'int'],

'name' => ['姓名', true, 'int'],

'email' => ['Email', true, 'string'],

'phone' => ['電話', true, 'string'],

'pid' => ['身份證', true, 'string'],

'status' => ['狀態', true, 'int'],

'created_time' => ['建立時間', true, 'datetime'],

'action' => ['動作', true, 'string']

];

Controller:

    protected function datatable_response($total, $filtered_total, $data=[], $draw=0){
        header('Content-Type: application/json');

        $out = array();
        $out['draw'] = $draw;
        $out['recordsTotal'] = $total;
        $out['recordsFiltered'] = $filtered_total;
        $out['data'] = $data;
     
        echo json_encode($out);
        exit();
    }

    public function members(Request $req){
        $this->check_login();

        $draw = $req->input('draw');
        $start = $req->input('start');
        $length = $req->input('length');
        $search = $req->input('search');
        $keyword = $search['value'];

        //Get sorting array with column name
        $fields = Config::get('admin.member_table');
        $order = $req->input('order');
        $sorting = MemberM::datatable_sorting($fields, $order);
        //logg($sorting);

        $options = [
            'start' => $start, 
            'limit' => $length, 
            'sort' => $sorting, 
            'keyword' => $keyword
        ];
        $total = MemberM::items($options, MemberM::DT_TOTAL);
        $filtered_total = MemberM::items($options, MemberM::DT_FILTERED);
        $members = MemberM::items($options);

        $members = MemberM::datatable_data($members);

        $this->datatable_response($total, $filtered_total, $members, $draw);
    }

protected function datatable_response($total, $filtered_total, $data=[], $draw=0){

header('Content-Type: application/json');

$out = array();

$out['draw'] = $draw;

$out['recordsTotal'] = $total;

$out['recordsFiltered'] = $filtered_total;

$out['data'] = $data;

echo json_encode($out);

exit();

}

public function members(Request $req){

$this->check_login();

$draw = $req->input('draw');

$start = $req->input('start');

$length = $req->input('length');

$search = $req->input('search');

$keyword = $search['value'];

//Get sorting array with column name

$fields = Config::get('admin.member_table');

$order = $req->input('order');

$sorting = MemberM::datatable_sorting($fields, $order);

//logg($sorting);

$options = [

'start' => $start,

'limit' => $length,

'sort' => $sorting,

'keyword' => $keyword

];

$total = MemberM::items($options, MemberM::DT_TOTAL);

$filtered_total = MemberM::items($options, MemberM::DT_FILTERED);

$members = MemberM::items($options);

$members = MemberM::datatable_data($members);

$this->datatable_response($total, $filtered_total, $members, $draw);

}

Model:

    static function items($options, $return_type=0){
        $result = DB::table('member');

        if($return_type == self::DT_TOTAL){
            return $result->count();
        }

        $keyword = val($options, 'keyword');
        if($keyword != false){
            $result = $result->where(function($query) use ($keyword){
                $find = '%' . $keyword . '%';
                $query->orWhere('name', 'like', $find);
                $query->orWhere('email', 'like', $find);
                $query->orWhere('phone', 'like', $find);
                $query->orWhere('pid', 'like', $find);
            });
        }

        if($return_type == self::DT_FILTERED){
            return $result->count();
        }

        if(val($options, 'sort') != false){
            $sorting = val($options, 'sort');
            foreach ($sorting as $key => $value) {
                $result = $result->orderBy($key, $value);
            }
        }else{
            $result = $result->orderBy('created_time', 'desc');
        }

        $result = $result->skip($options['start'])
            ->take($options['limit'])
            ->get();

        return $result;
    }

    static function datatable_sorting($columns, $order){
        //$columns = $this->config->item('adm_page_columns');
        //$order = $this->input->get('order');
     
        $sort = array();
        foreach($order as $key => $item){
            $col_index = $item['column'];
            $dir = $item['dir'];
     
            $column = null;
            $column_key = null;
            $i = 0;
            foreach ($columns as $key => $item) {
                if($i == $col_index){
                    $column = $item;
                    $column_key = $key;
                    break;
                }
                $i++;
            }
     
            $sort[$column_key] = $dir;
        }
     
        return $sort;
    }

    static function datatable_data($data){
        $fields = Config::get('admin.member_table');

        $new_data = array();
        foreach($data as $item){
            $item_values = [];

            foreach ($fields as $key => $field) {
                $value = val($item, $key);

                if($key == 'status'){
                    $value = trans('front.member_status.' . $item->status);
                }

                if($key == 'action'){
                    $value = '<div class="dropdown">
                            <button class="btn btn-default dropdown-toggle" type="button" id="dropdownMenu1" data-toggle="dropdown" aria-haspopup="true" aria-expanded="true">
                                選擇
                                <span class="caret"></span>
                            </button>
                            <ul class="dropdown-menu" aria-labelledby="dropdownMenu1">
                                <li><a href="#" class="action_edit" data-id="' . $item->id . '">修改</a></li>
                                <li><a href="#" class="action_buy" data-id="' . $item->id . '">下買單</a></li>
                                <li><a href="#" class="action_sell" data-id="' . $item->id . '">下賣單</a></li>
                            </ul>
                        </div>';
                }

                $item_values[] = $value;
            }

            array_push($new_data, $item_values);
        }

        return $new_data;
    }

100

101

static function items($options, $return_type=0){

$result = DB::table('member');

if($return_type == self::DT_TOTAL){

return $result->count();

}

$keyword = val($options, 'keyword');

if($keyword != false){

$result = $result->where(function($query) use ($keyword){

$find = '%' . $keyword . '%';

$query->orWhere('name', 'like', $find);

$query->orWhere('email', 'like', $find);

$query->orWhere('phone', 'like', $find);

$query->orWhere('pid', 'like', $find);

});

}

if($return_type == self::DT_FILTERED){

return $result->count();

}

if(val($options, 'sort') != false){

$sorting = val($options, 'sort');

foreach ($sorting as $key => $value) {

$result = $result->orderBy($key, $value);

}

}else{

$result = $result->orderBy('created_time', 'desc');

}

$result = $result->skip($options['start'])

->take($options['limit'])

->get();

return $result;

}

static function datatable_sorting($columns, $order){

//$columns = $this->config->item('adm_page_columns');

//$order = $this->input->get('order');

$sort = array();

foreach($order as $key => $item){

$col_index = $item['column'];

$dir = $item['dir'];

$column = null;

$column_key = null;

$i = 0;

foreach ($columns as $key => $item) {

if($i == $col_index){

$column = $item;

$column_key = $key;

break;

}

$i++;

}

$sort[$column_key] = $dir;

}

return $sort;

}

static function datatable_data($data){

$fields = Config::get('admin.member_table');

$new_data = array();

foreach($data as $item){

$item_values = [];

foreach ($fields as $key => $field) {

$value = val($item, $key);

if($key == 'status'){

$value = trans('front.member_status.' . $item->status);

}

if($key == 'action'){

$value = '<div class="dropdown">

選擇

</button>

</ul>

</div>';

}

$item_values[] = $value;

}

array_push($new_data, $item_values);

}

return $new_data;

}

Clear everything in Redis

redis-cli flushall

1	redis-cli flushall

Reference:

https://stackoverflow.com/questions/6851909/how-do-i-delete-everything-in-redis

Disable datatable warnings

//Disable datatable warnings
$.fn.dataTable.ext.errMode = 'none';

1 2	//Disable datatable warnings $.fn.dataTable.ext.errMode = 'none';

Reference:

https://datatables.net/forums/discussion/28726/how-to-disable-the-warning-message

SendGrid API Example

Doc: https://github.com/sendgrid/sendgrid-php

Complete example:
https://github.com/sendgrid/sendgrid-php/blob/master/USE_CASES.md#attachments

Example:

   public function send($subject, $msg, $contact, $attachment=null){
        $from = new SendGrid\Email($this->sender['name'], $this->sender['email']);
        $subject = $subject;
        $to = new SendGrid\Email($contact['name'], $contact['email']);
        $content = new SendGrid\Content("text/html", $msg);
        $mail = new SendGrid\Mail($from, $subject, $to, $content);

        if($attachment != false){
            $att = $this->process_attachment($attachment);
            $mail->addAttachment($att);
        }

        $apiKey = $this->sg_key;
        $sg = new \SendGrid($apiKey);

        $response = $sg->client->mail()->send()->post($mail);
        if($response->statusCode() == 202){
            return true;
        }else{
            $this->err = '寄送Email失敗: ' . $contact['email'];
            return false;
        }


        echo '<prev>';
        echo $response->statusCode();
        print_r($response->headers());
        echo $response->body();
        echo '</prev>';
    }

    public function process_attachment($file){
        $file_encoded = base64_encode(file_get_contents($file));
        $attachment = new SendGrid\Attachment();
        $attachment->setContent($file_encoded);
        $attachment->setType("application/pdf");
        $attachment->setDisposition("attachment");
        $attachment->setFilename("order.pdf");

        return $attachment;
    }

public function send($subject, $msg, $contact, $attachment=null){

$from = new SendGrid\Email($this->sender['name'], $this->sender['email']);

$subject = $subject;

$to = new SendGrid\Email($contact['name'], $contact['email']);

$content = new SendGrid\Content("text/html", $msg);

$mail = new SendGrid\Mail($from, $subject, $to, $content);

if($attachment != false){

$att = $this->process_attachment($attachment);

$mail->addAttachment($att);

}

$apiKey = $this->sg_key;

$sg = new \SendGrid($apiKey);

$response = $sg->client->mail()->send()->post($mail);

if($response->statusCode() == 202){

return true;

}else{

$this->err = '寄送Email失敗: ' . $contact['email'];

return false;

}

echo '<prev>';

echo $response->statusCode();

print_r($response->headers());

echo $response->body();

echo '</prev>';

}

public function process_attachment($file){

$file_encoded = base64_encode(file_get_contents($file));

$attachment = new SendGrid\Attachment();

$attachment->setContent($file_encoded);

$attachment->setType("application/pdf");

$attachment->setDisposition("attachment");

$attachment->setFilename("order.pdf");

return $attachment;

}

TCPDF

Doc: https://tcpdf.org/docs/srcdoc/tcpdf/class-TCPDF/

Note:

HTML裡面的圖片網址一定要是完整路徑，不能是localhost, 否則輸出會有”unable to get the size of the image” error
crontab執行的時候，base_url()就是抓到localhost，所以要注意

Example:

	public function pdf_test($html){
		// create new PDF document
		$pdf = new TCPDF(PDF_PAGE_ORIENTATION, PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);

		// set document information
		$pdf->SetCreator(PDF_CREATOR);
		$pdf->SetAuthor('xxx');
		$pdf->SetTitle('TCPDF Example 001');
		$pdf->SetSubject('TCPDF Tutorial');
		$pdf->SetKeywords('TCPDF, PDF, example, test, guide');

		// remove default header/footer
		$pdf->setPrintHeader(false);
		$pdf->setPrintFooter(false);

		// set default monospaced font
		$pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);

		// set margins
		$pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT);
		$pdf->SetHeaderMargin(PDF_MARGIN_HEADER);
		$pdf->SetFooterMargin(PDF_MARGIN_FOOTER);

		// set auto page breaks
		$pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);

		// set image scale factor
		$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);

		// set some language-dependent strings (optional)
		if (@file_exists(dirname(__FILE__).'/lang/eng.php')) {
			require_once(dirname(__FILE__).'/lang/eng.php');
			$pdf->setLanguageArray($l);
		}

		// ---------------------------------------------------------

		// set default font subsetting mode
		$pdf->setFontSubsetting(true);

		// Set font
		// dejavusans is a UTF-8 Unicode font, if you only need to
		// print standard ASCII chars, you can use core fonts like
		// helvetica or times to reduce file size.
		$pdf->SetFont('cid0jp', '', 14, '', true);//中文字型

		// Add a page
		// This method has several options, check the source code documentation for more information.
		$pdf->AddPage();

		// set text shadow effect
		$pdf->setTextShadow(array('enabled'=>true, 'depth_w'=>0.2, 'depth_h'=>0.2, 'color'=>array(196,196,196), 'opacity'=>1, 'blend_mode'=>'Normal'));

		// Set some content to print
		//$html = $this->email_handler->get_template('template_order.html');

		$ds = DIRECTORY_SEPARATOR;
    	$logo = FCPATH . 'assets' . $ds . 'front' . $ds . 'img' . $ds . 'logo_email.png';
    	//$url = 'https://www.xxx.com/assets/front/img/logo.png';
    	$pdf->Image ($logo);
    	$pdf->ln(10);

    	$img_row = '<tr>
    <td colspan="3"><a href="http://www.xxx.com"><img src="http://www.xxx.com/assets/front/img/logo.png" width="146" height="47" border="0" /></a></td>
  </tr>';
    	$html = str_replace($img_row, '', $html);

		// Print text using writeHTMLCell()
		$pdf->writeHTMLCell(0, 0, '', '', $html, 0, 1, 0, true, '', true);

		// ---------------------------------------------------------

		// Close and output PDF document
		// This method has several options, check the source code documentation for more information.
		$pdf_file = FCPATH . 'uploads_temp' . DIRECTORY_SEPARATOR . 'pdf_test.pdf';
		$pdf->Output($pdf_file, 'F');

		return $pdf_file;
	}

public function pdf_test($html){

// create new PDF document

$pdf = new TCPDF(PDF_PAGE_ORIENTATION, PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);

// set document information

$pdf->SetCreator(PDF_CREATOR);

$pdf->SetAuthor('xxx');

$pdf->SetTitle('TCPDF Example 001');

$pdf->SetSubject('TCPDF Tutorial');

$pdf->SetKeywords('TCPDF, PDF, example, test, guide');

// remove default header/footer

$pdf->setPrintHeader(false);

$pdf->setPrintFooter(false);

// set default monospaced font

$pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);

// set margins

$pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT);

$pdf->SetHeaderMargin(PDF_MARGIN_HEADER);

$pdf->SetFooterMargin(PDF_MARGIN_FOOTER);

// set auto page breaks

$pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);

// set image scale factor

$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);

// set some language-dependent strings (optional)

if (@file_exists(dirname(__FILE__).'/lang/eng.php')) {

require_once(dirname(__FILE__).'/lang/eng.php');

$pdf->setLanguageArray($l);

}

// ---------------------------------------------------------

// set default font subsetting mode

$pdf->setFontSubsetting(true);

// Set font

// dejavusans is a UTF-8 Unicode font, if you only need to

// print standard ASCII chars, you can use core fonts like

// helvetica or times to reduce file size.

$pdf->SetFont('cid0jp', '', 14, '', true);//中文字型

// Add a page

// This method has several options, check the source code documentation for more information.

$pdf->AddPage();

// set text shadow effect

$pdf->setTextShadow(array('enabled'=>true, 'depth_w'=>0.2, 'depth_h'=>0.2, 'color'=>array(196,196,196), 'opacity'=>1, 'blend_mode'=>'Normal'));

// Set some content to print

//$html = $this->email_handler->get_template('template_order.html');

$ds = DIRECTORY_SEPARATOR;

$logo = FCPATH . 'assets' . $ds . 'front' . $ds . 'img' . $ds . 'logo_email.png';

//$url = 'https://www.xxx.com/assets/front/img/logo.png';

$pdf->Image ($logo);

$pdf->ln(10);

$img_row = '<tr>

</tr>';

$html = str_replace($img_row, '', $html);

// Print text using writeHTMLCell()

$pdf->writeHTMLCell(0, 0, '', '', $html, 0, 1, 0, true, '', true);

// ---------------------------------------------------------

// Close and output PDF document

// This method has several options, check the source code documentation for more information.

$pdf_file = FCPATH . 'uploads_temp' . DIRECTORY_SEPARATOR . 'pdf_test.pdf';

$pdf->Output($pdf_file, 'F');

return $pdf_file;

}

Reference:

Chinese Font:
http://books.bod.idv.tw/2012/03/phptcpdfpdf.html
https://dotblogs.com.tw/jses88001/2014/12/22/php_tcpdf_convert_fonts
Output as file:
https://stackoverflow.com/questions/12304553/tcpdf-save-file-to-folder
Write image:
http://www.c-sharpcorner.com/UploadFile/d9da8a/tcpdf-with-an-image-and-html-in-php/
中文字型:
https://www.weblink.idv.tw/2017/10
字型轉檔:
https://phperzh.com/articles/3580

Localization of datatable

Localization file:

{
    "sProcessing":   "處理中...",
    "sLengthMenu":   "顯示 _MENU_ 項結果",
    "sZeroRecords":  "沒有符合的結果",
    "sInfo":         "顯示第 _START_ 至 _END_ 項結果，共 _TOTAL_ 項",
    "sInfoEmpty":    "顯示第 0 至 0 項結果，共 0 項",
    "sInfoFiltered": "(由 _MAX_ 項結果中過濾)",
    "sInfoPostFix":  "",
    "sSearch":       "搜尋:",
    "sUrl":          "",
    "sEmptyTable":     "沒有符合的結果",
    "sLoadingRecords": "處理中...",
    "sInfoThousands":  ",",
    "oPaginate": {
        "sFirst":    "第一頁",
        "sPrevious": "上一頁",
        "sNext":     "下一頁",
        "sLast":     "最後一頁"
    },
    "oAria": {
        "sSortAscending":  ": 升冪排列",
        "sSortDescending": ": 降冪排列"
    }
}

{

"sProcessing": "處理中...",

"sLengthMenu": "顯示 _MENU_ 項結果",

"sZeroRecords": "沒有符合的結果",

"sInfo": "顯示第 _START_ 至 _END_ 項結果，共 _TOTAL_ 項",

"sInfoEmpty": "顯示第 0 至 0 項結果，共 0 項",

"sInfoFiltered": "(由 _MAX_ 項結果中過濾)",

"sInfoPostFix": "",

"sSearch": "搜尋:",

"sUrl": "",

"sEmptyTable": "沒有符合的結果",

"sLoadingRecords": "處理中...",

"sInfoThousands": ",",

"oPaginate": {

"sFirst": "第一頁",

"sPrevious": "上一頁",

"sNext": "下一頁",

"sLast": "最後一頁"

"oAria": {

"sSortAscending": ": 升冪排列",

"sSortDescending": ": 降冪排列"

}

Sample code:

function init_dt(){
    datatable = $('#member_table').DataTable({
        "searching": false,
	    "info": false,
	    "lengthChange": false,
	    "paging": true,
        "ajax": {
        	'url': base_url + 'admin/ajax/members',
        	'data': function(d){
        		var $c = $('#c-tabs-1');

        		d.member_type = $("#member_type").val();
        		d.keyword = $('#keyword').val();
        		d.keyword_col = $('#keyword_col').val();
        		d.extra_options = get_value('extra_options[]', $c);
        	}
        },
        //"order": [[ 6, "asc" ], [ 5, "" ]], 
        "columnDefs": [
            {
                "targets": [ 6 ],
                "visible": true,
                "searchable": false
            }
        ], 
        "drawCallback": function( settings ) {
            var api = this.api();
            //wrap.loaded(settings);
        }, 
        'initComplete': function(settings, json){
        	loaded(settings);
        },
        "language" : {
        	"url": "/plugins/datatable_zhtw.json",
            paginate: {
                first:    '«',
                previous: '‹',
                next:     '›',
                last:     '»'
            },
            aria: {
                paginate: {
                    first:    '第一頁',
                    previous: '上一頁',
                    next:     '下一頁',
                    last:     '最後一頁'
                }
            }
        }
    });
}

function init_dt(){

datatable = $('#member_table').DataTable({

"searching": false,

"info": false,

"lengthChange": false,

"paging": true,

"ajax": {

'url': base_url + 'admin/ajax/members',

'data': function(d){

var $c = $('#c-tabs-1');

d.member_type = $("#member_type").val();

d.keyword = $('#keyword').val();

d.keyword_col = $('#keyword_col').val();

d.extra_options = get_value('extra_options[]', $c);

}

//"order": [[ 6, "asc" ], [ 5, "" ]],

"columnDefs": [

{

"targets": [ 6 ],

"visible": true,

"searchable": false

}

"drawCallback": function( settings ) {

var api = this.api();

//wrap.loaded(settings);

'initComplete': function(settings, json){

loaded(settings);

"language" : {

"url": "/plugins/datatable_zhtw.json",

paginate: {

first: '«',

previous: '‹',

next: '›',

last: '»'

aria: {

paginate: {

first: '第一頁',

previous: '上一頁',

next: '下一頁',

last: '最後一頁'

}

});

}

Display priority:

Localization file
paginate option
aria

Reference:

Language file:
https://datatables.net/examples/advanced_init/language_file.html
How to use:
https://datatables.net/plug-ins/i18n/

PHPexcel: Error loading file “uploaded_order.xls”: Unknown codepage: 10002

Error loading file “uploaded_order.xls”: Unknown codepage: 10002

Reason: This file could made on Mac and cause some identify issue.

Solution:

Add this line in PHPExcel/Shared/CodePage.php

case 10002:	return 'MAC';				break;	//	Apple (By Rex)

1	case 10002: return 'MAC'; break; // Apple (By Rex)

Reference:

http://blog.flamelin.com/posts/phpexcel-unknown-codepage-error/

Filezilla Server Connection Issue

How to fix “Can’t open data connection for transfer of “/”” error when connecting to a FTP server with Filezilla server.

Change Filezilla server setting
Settings -> Passive mode settings -> Use custom port range
6000-6010
Create a new inbound rule in Windows Firewall for above port range

Reference:

Datatable Server Side Processing

Javascript:

d_table = $('#datatable').dataTable( {
	'serverSide': true, 
	"ajax": {
		url: base_url + 'admin/page/all/' + cat_id + '/?' + $.param(params), 
		data: function(d){
			d.key = value;
		}, 
		dataSrc: function(res){
			//Check if session is timeout
			if(typeof res.status != 'undefined' && res.code == 1){
				alert('Please log in.');
				return false;
			}
			return res.data;
		}
	}, 
	"order": sorting, 
	"columnDefs": column_def, 
	"drawCallback": function( settings ) {
		var api = this.api();
		wrap.loaded(settings);
	},
	createdRow: function (row, data, index) {
		//用第5欄的value來判斷row的顏色
		if(typeof data[4] != 'undefined'){
			if(/^多.*/.test(data[4]) == true){
				$(row).addClass('text-danger');
			}else{
				$(row).addClass('text-success');
			}
		}

		$(row).attr('data-id', data[1]);
	},
	"language" : {
		"url": "/plugins/datatable_zhtw.json"
	}
});

//Refresh table content without clear all content first
function refresh(){
	d_table.ajax.reload(function (data) {
		if (data.status == 'success') {
			wrap.d_table.clear();
			wrap.d_table.rows.add(data.data);
			wrap.d_table.draw();
		}
	});
}

d_table = $('#datatable').dataTable( {

'serverSide': true,

"ajax": {

url: base_url + 'admin/page/all/' + cat_id + '/?' + $.param(params),

data: function(d){

d.key = value;

dataSrc: function(res){

//Check if session is timeout

if(typeof res.status != 'undefined' && res.code == 1){

alert('Please log in.');

return false;

}

return res.data;

}

"order": sorting,

"columnDefs": column_def,

"drawCallback": function( settings ) {

var api = this.api();

wrap.loaded(settings);

createdRow: function (row, data, index) {

//用第5欄的value來判斷row的顏色

if(typeof data[4] != 'undefined'){

if(/^多.*/.test(data[4]) == true){

$(row).addClass('text-danger');

}else{

$(row).addClass('text-success');

}

$(row).attr('data-id', data[1]);

"language" : {

"url": "/plugins/datatable_zhtw.json"

}

});

//Refresh table content without clear all content first

function refresh(){

d_table.ajax.reload(function (data) {

if (data.status == 'success') {

wrap.d_table.clear();

wrap.d_table.rows.add(data.data);

wrap.d_table.draw();

}

});

}

Server:

private function datatable_sorting(){
	$columns = $this->config->item('adm_page_columns');
	$order = $this->input->get('order');

	$sort = array();
	foreach($order as $key => $item){
		$col_index = $item['column'];
		$dir = $item['dir'];

		$column = null;
		$column_key = null;
		$i = 0;
		foreach ($columns as $key => $item) {
			if($i == $col_index){
				$column = $item;
				$column_key = $key;
				break;
			}
			$i++;
		}

		$sort[$column_key] = $dir;
	}

	return $sort;
}

public function all($cat_id=0){
	$start = $this->input->get('start');
	$length = $this->input->get('length');
	$search = $this->input->get('search');
	$keyword = $search['value'];
	$sort = $this->datatable_sorting();

	//Get total record count
	$options = array(
		'all' => 1, 
		'cat_id' => $cat_id
	);
	$data = $this->pages_m->get_pages($this->lang_id, $options);
	if($data == false){
		$total = 0;
	}else{
		$total = count($data);
	}

	//Get total record count
	$options['start'] = $start;
	$options['limit'] = $length;
	$options['sort'] = $sort;
	$data = $this->pages_m->get_pages($this->lang_id, $options);

	$data = $this->pages_m->to_table($data);
	$this->datatable_response($total, $data);
}

public function datatable_response($total, $data = array())
{
	//Output;
	header('Cache-Control: no-cache, must-revalidate');
	header('Content-type: application/json;charset=utf-8');

	$out = array();
	$out['draw'] = $this->input->get('draw');
	$out['recordsTotal'] = $total;
	$out['recordsFiltered'] = $total;
	$out['data'] = $data;

	echo json_encode($out);
	exit();
}

private function datatable_sorting(){

$columns = $this->config->item('adm_page_columns');

$order = $this->input->get('order');

$sort = array();

foreach($order as $key => $item){

$col_index = $item['column'];

$dir = $item['dir'];

$column = null;

$column_key = null;

$i = 0;

foreach ($columns as $key => $item) {

if($i == $col_index){

$column = $item;

$column_key = $key;

break;

}

$i++;

}

$sort[$column_key] = $dir;

}

return $sort;

}

public function all($cat_id=0){

$start = $this->input->get('start');

$length = $this->input->get('length');

$search = $this->input->get('search');

$keyword = $search['value'];

$sort = $this->datatable_sorting();

//Get total record count

$options = array(

'all' => 1,

'cat_id' => $cat_id

);

$data = $this->pages_m->get_pages($this->lang_id, $options);

if($data == false){

$total = 0;

}else{

$total = count($data);

}

//Get total record count

$options['start'] = $start;

$options['limit'] = $length;

$options['sort'] = $sort;

$data = $this->pages_m->get_pages($this->lang_id, $options);

$data = $this->pages_m->to_table($data);

$this->datatable_response($total, $data);

}

public function datatable_response($total, $data = array())

{

//Output;

header('Cache-Control: no-cache, must-revalidate');

header('Content-type: application/json;charset=utf-8');

$out = array();

$out['draw'] = $this->input->get('draw');

$out['recordsTotal'] = $total;

$out['recordsFiltered'] = $total;

$out['data'] = $data;

echo json_encode($out);

exit();

}

Update Excel Content

function download_tpl(){
	//$this->check_permission('orders', 'update', array('download', 'download_m'));

	$sep = DIRECTORY_SEPARATOR;

	$file_name = 'coupon_upload_tpl_origin.xls';
	$tpl = FCPATH . 'assets' . $sep . 'admin' . $sep . 'excel_templates' . $sep . $file_name;

	$file_name = 'coupon_upload_tpl.xls';
	$file = FCPATH . 'assets' . $sep . 'admin' . $sep . 'excel_templates' . $sep . $file_name;
	$file_url = base_url() . 'assets/admin/excel_templates/' . $file_name;
	
	// Initiate cache
	require(APPPATH.'libraries/PHPExcel.php');
	$inputFileType = PHPExcel_IOFactory::identify($tpl);
	$objReader = PHPExcel_IOFactory::createReader($inputFileType);
	$objPHPExcel = $objReader->load($tpl);

	//選項sheet
	$worksheet = $objPHPExcel->getSheet(1); 
	//logg($worksheet);
	
	//修改產品清單
	$rowN = 2;
	$columnN = 5;
	$products = $this->b2c_products();

	foreach($products as $key => $prod){
		$worksheet->setCellValueByColumnAndRow($columnN, $rowN, $prod->title);
		$worksheet->setCellValueByColumnAndRow($columnN+1, $rowN, $prod->unique_id);

		$rowN++;
	}
	
	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
	$objWriter->save($file);

	redirect($file_url);
	exit();
}

function download_tpl(){

//$this->check_permission('orders', 'update', array('download', 'download_m'));

$sep = DIRECTORY_SEPARATOR;

$file_name = 'coupon_upload_tpl_origin.xls';

$tpl = FCPATH . 'assets' . $sep . 'admin' . $sep . 'excel_templates' . $sep . $file_name;

$file_name = 'coupon_upload_tpl.xls';

$file = FCPATH . 'assets' . $sep . 'admin' . $sep . 'excel_templates' . $sep . $file_name;

$file_url = base_url() . 'assets/admin/excel_templates/' . $file_name;

// Initiate cache

require(APPPATH.'libraries/PHPExcel.php');

$inputFileType = PHPExcel_IOFactory::identify($tpl);

$objReader = PHPExcel_IOFactory::createReader($inputFileType);

$objPHPExcel = $objReader->load($tpl);

//選項sheet

$worksheet = $objPHPExcel->getSheet(1);

//logg($worksheet);

//修改產品清單

$rowN = 2;

$columnN = 5;

$products = $this->b2c_products();

foreach($products as $key => $prod){

$worksheet->setCellValueByColumnAndRow($columnN, $rowN, $prod->title);

$worksheet->setCellValueByColumnAndRow($columnN+1, $rowN, $prod->unique_id);

$rowN++;

}

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

$objWriter->save($file);

redirect($file_url);

exit();

}

Rex's Personal Site

This site is built on WordPress and contains personal notes, photos and videos

Category Archives: Web Tools

Datatable Server Side Processing (Laravel)

Clear everything in Redis

Disable datatable warnings

SendGrid API Example

TCPDF

Localization of datatable

PHPexcel: Error loading file “uploaded_order.xls”: Unknown codepage: 10002

Filezilla Server Connection Issue

Datatable Server Side Processing

Update Excel Content

September 2018
M	T	W	T	F	S	S
« Aug
					1	2
3	4	5	6	7	8	9
10	11	12	13	14	15	16
17	18	19	20	21	22	23
24	25	26	27	28	29	30