How Create Data Table Control SartajPHP

Hi, in this blog i explain you, how can you create a control for Data Table? It is very easy and reusable like create once and use in every project. Requirements Tested on SartajPHP version dev-master First create a control file "datatable.php" in your project folder like below code:-

/**
* SartajPHP component - datatable
*
* 
*/
class datatable extends \Sphp\tools\Control {
    private $row = array();
    private $sql = "";
    private $perPageRows = 10;
    public $result;
    private $pageNo = 0;
    private $cacheTime = 0;
    private $cachefile = '';
    private $cachekey = 'id';
    private $cachesave = false;
    private $repeatable = null;
    private $buttons = "'copy', 'csv', 'excel', 'pdf', 'print'";

    public function oncreate($element) {}
    public function setSQL($param) {
        $this->sql = $param;
    }
    public function getRow($field) {
        if (isset($this->row[$field])) {
            return $this->row[$field];
        } else {
            return "";
        }
    }
    public function setPerPageRows($val) {
        $this->perPageRows = intval($val);
    }
    public function setPageNo($val) {
        $this->pageNo = $val - 1;
    }
    public function getPageNo() {
        return $this->pageNo + 1;
    }
    public function setCacheFile($val) {
        $this->cachefile = $val;
    }
    public function setCacheSave() {
        $this->cachesave = true;
    }
    public function setCacheKey($val) {
        $this->cachekey = $val;
    }
    public function setCacheTime($val) {
        $this->cacheTime = intval($val);
    }

    public function executeSQL() {
        $mysql = \SphpBase::$dbEngine;
        $stro = "";
        if ($this->pageNo < 0) {
            $this->pageNo = 0;
        }

        $startat = $this->pageNo * $this->perPageRows;
        if ($startat < 0) {
            $startat = 0;
        }
        $this->result = $mysql->fetchQuery($this->sql ." LIMIT $startat,$this->perPageRows", $this->cacheTime, $this->cachefile, $this->cachekey, $this->cachesave);

        if (count($this->result) > 0) {
            $roote = $this->tempobj->getChildrenWrapper($this->repeatable);
            foreach ($this->result as $key1 => $keyar) {
                foreach ($keyar as $index => $this->row) {
                    $stro .= $this->tempobj->parseComponentChildren($roote);
                }
            }
            //echo $stro;
            //$this->repeatable->unsetrenderTag();
            $this->repeatable->setInnerHTML($stro);
            //$this->setPreTag('<div id="'. $this->name . 'b"></div>');
        }

    }
    public function setButtons($param) {
        $this->buttons = $param;
    }
    public function onchildevent($evt, $obj) {
        if ($evt == "oncreate" && $obj->getAttribute("repeatable") == "true") {
            $this->repeatable = $obj;
        } else if ($evt == "onrender" && $obj->getAttribute("repeatable") == "true") {
            $this->repeatable = $obj;
            $obj->setHTMLID($this->name . 'rep');
            $this->executeSQL();
        }
    }
    public function sendAjax() {
        $this->executeSQL();
        SphpBase::$JSServer->addJSONBlock("js1", "proces", "window['" . $this->name . '\'].destroy(); console.log("destroy");');
        SphpBase::$JSServer->addJSONBlock("html", $this->name . 'rep', utf8_encode($this->repeatable->getInnerHTML()));
        SphpBase::$JSServer->addJSONBlock("jsp", "proces", "window['" . $this->name . '\'] = $("#'. $this->name .'").DataTable(); console.log("reload");');
        //SphpBase::$JSServer->addJSONBlock("jsp","proces", 'console.log("reload");' );
    }
    public function onjsrender() {
        if ($this->buttons !== '') {
            $this->buttons = str_replace("'print'", "{
               extend: 'print',
               autoPrint: false,
               exportOptions: {
                   stripHtml: false
               }
           }", $this->buttons);
            $this->buttons = str_replace("'pdf'", "{
               extend: 'pdf',
               exportOptions: {
                   stripHtml: true
               }
           }", $this->buttons);
        }
        addHeaderJSFunctionCode("ready", $this->name. 'd1', " window['{$this->name}'] = $('#{$this->name}').DataTable({". '
        dom: \'<"wrapper"Bflrtip>\',
        buttons: [
            '. $this->buttons .'
        ],
        "paging":   true,
        "ordering": true,
        "info":     true,
        "lengthChange": true,
        "row": "'. $this->cachekey .'",
  "columnDefs": [ {
            "searchable": false,
            "orderable": false,
            "targets": 0
        } ],
        "order": [[ 1, \'asc\' ]]
        '. "});
    {$this->name}.on( 'order.dt search.dt', function () {
        {$this->name}.column(0, {search:'applied', order:'applied'}).nodes().each( function (cell, i) {
            cell.innerHTML = i+1;
        } );
    } ).draw();
       ");
    }

    public function onrender() {
        addFileLink($this->myrespath . "/css/dataTables.bootstrap5.css");
        addFileLink($this->myrespath . "/js/jquery.dataTables.min.js");
        addFileLink($this->myrespath . "/js/dataTables.bootstrap5.js");
        addFileLink($this->myrespath . "/js/dataTables.buttons.min.js");
        addFileLink($this->myrespath . "/js/buttons.bootstrap5.min.js");
        addFileLink($this->myrespath . "/js/dataTables.responsive.min.js");
        addFileLink($this->myrespath . "/js/responsive.bootstrap5.min.js");
        $this->tagName = "table";
        $this->setAttribute("class", "table display");
    }
}

You can download datatable css and js files from their website and copy paste under the same folder where is the control file datatable.php. Now you are ready to use datatable control in your temp file.

Create App file in your project folder under sub folder apps so path is projectfolder/apps/index.app

class index extends Sphp\tools\BasicApp{
   private $temp1 = null;

    public function onstart(){
          $this->temp1 = new TempFile($this->apppath . "/forms/home.front");
    }

// handle new event of application
    public function page_new(){

        $this->setTempFile($this->temp1);

    }

// create and handle custom event info 
    public function page_event_info($evtp){

    }
}

Register your app file in projectfolder/reg.php

registerApp("index","apps/index.app");

Now create your temp file and use datatable control in it. projectfolder/apps/forms/home.front

<div class="card">
    <div class="card-header">
        <h3 class="card-title">My Category</h3>
    </div>
    <div class="card-body">
        <table id="dt1" funsetButtons="'pdf','print'" runat="server" path="temp/assets/plugins/datatable/datatable.php"
            funsetSQL="SELECT id,txtcname FROM prod_category" funsetPerPageRows="100"
            class="table table-bordered text-nowrap key-buttons border-bottom  w-100">
            <thead>
                <tr>
                    <th>S.N.</th>
                    <th>Name</th>
                </tr>
            </thead>
            <tbody repeatable="true" runat="server" id="tb1">
                <tr>
                    <td></td>
                    <td>
                        <a href="index-cat-##{ $this->tempobj->dt1->getRow('id') }#.html">
                            ##{ $this->tempobj->dt1->getRow('txtcname') }#
                        </a>
                    </td>
                </tr>
            </tbody>
        </table>
    </div>
</div>

now call index.html in your browser and you will see output from datatable. Thanks to read my blog. I will try to give you more good information on my next blogs