Implement a DataTable in Osclass

A DataTable in Osclass is a dynamic table to view records from the administration (oc-admin) and make editing, manipulation, activation, deactivation and deletion easier in bulk or filter a search of the records, all in one place.

Osclass has several DataTable, for example: in Manage Plugins it shows a DataTable of all the plugins uploaded to the system, Manage Listings is another DataTable where the ads are shown, it includes pagination and filtered search.

This tutorial is an extension of Make a plugin in Osclass, which involves using your own DataTable; so that it starts working depending on the following files:

- model/MyPlugin.php (model)

- classes/datatables/CrudDataTable.php

- controller/admin/crud.php

- views/admin/crud.php

 

model/MyPlugin.php

In this file there are two methods that you have to pay attention to: public function registers($params) and public function registersTotal() are the only ones that will be used to build or filter the type of query with which the DataTable will be filled in and its respective pagination .

/**
 * Search registers
 *
 * This function is for search with parameters in the CrudDataTable.php
 *
 * @access public
 * @param array $params Is a array variable witch containt all parameters for the search and pagination
 * @return array
 */
public function registers($params)
{
    $start          = (isset($params['start']) && $params['start'] != '' ) ? $params['start']: 0;
    $limit          = (isset($params['limit']) && $params['limit'] != '' ) ? $params['limit']: 10;

    $sort           = (isset($params['sort']) && $params['sort'] != '') ? $params['sort'] : '';
    $sort           = strtolower($sort);

    switch ($sort) {
        case 'date':
            $sort = 'dt_date';
            break;

        case 'pub_date':
            $sort = 'dt_pub_date';
            break;

        default:
            $sort = 'dt_date';
            break;
    }

    $direction      = (isset($params['direction']) && $params['direction'] == 'ASC') ? $params['direction'] : 'DESC';
    $direction      = strtoupper($direction);

    $date           = (isset($params['dt_date']) && $params['dt_date'] != '') ? $params['dt_date'] : '';
    $dateControl    = (isset($params['date_control']) && $params['date_control']!='') ? $params['date_control'] : '';
    $url            = (isset($params['s_url']) && $params['s_url'] != '') ? $params['s_url'] : '';
    $status         = (isset($params['b_active']) && $params['b_active'] != '') ? $params['b_active'] : '';

    $this->dao->select('*');
    $this->dao->from($this->getTable_table_one());
    $this->dao->orderBy($sort, $direction);

    if ($date != '') {
        switch ($dateControl) {
            case 'equal':
                $this->dao->where('dt_date', $date);
                break;

            case 'greater':
                $this->dao->where("dt_date > '$date'");
                break;

            case 'greater_equal':
                $this->dao->where("dt_date >= '$date'");
                break;

            case 'less':
                $this->dao->where("dt_date < '$date'");
                break;

            case 'less_equal':
                $this->dao->where("dt_date <= '$date'");
                break;

            case 'not_equal':
                $this->dao->where("dt_date != '$date'");
                break;
            
            default:
                $this->dao->where('dt_date', $date);
                break;
        }
    }
    
    if ($status != '') {
        if ($status == 0) {
            $this->dao->where('b_active', 0);
        } else {
            $this->dao->where('b_active', 1);
        }
    }

    if ($url != '') {
        $this->dao->like('s_url', $url);
    }
    
    $this->dao->limit($limit, $start);
    $result = $this->dao->get();
    if($result) {
        return $result->result();
    }
    return array();
}

/**
 * Count total registers
 *
 * @access public
 * @return integer
 */
public function registersTotal()
{
    $this->dao->select('COUNT(*) as total') ;
    $this->dao->from($this->getTable_table_one());
    $result = $this->dao->get();
    if($result) {
        $row = $result->row();
        if(isset($row['total'])) {
            return $row['total'];
        }
    }
    return 0;
}

 

classes/datatables/CrudDataTable.php

It works as one more library, in which the CrudDataTable class inherits functionalities from another mother library called DataTable.

- Starting [classes/datatables/CrudDataTable.php]:

<?php if ( ! defined('ABS_PATH')) exit('ABS_PATH is not loaded. Direct access is not allowed.');

	class CrudDataTable extends DataTable
	{
		public function __construct()
        {
        	osc_add_filter('datatable_crud_status_class', array(&$this, 'row_class'));
            osc_add_filter('datatable_crud_status_text', array(&$this, '_status'));
        }

 

- Method that captures the data of the search/paging parameters filtered in the URL [classes/datatables/CrudDataTable]:

        /**
         * Build the table in the php file: controller/admin/crud.php
         *
         * Build the table of all registers with filter and pagination
         *
         * @access public
         * @param array $params
         * @return array
         */
        public function table($params)
        {
        	$this->addTableHeader();

            $start = ((int)$params['iPage']-1) * $params['iDisplayLength'];

            $this->start = intval($start);
            $this->limit = intval($params['iDisplayLength']);

            $registers = MyPlugin::newInstance()->registers(array(
                'start'         => $this->start,
                'limit'         => $this->limit,

                'sort'          => Params::getParam('sort'),
                'direction'     => Params::getParam('direction'),

                'dt_date'       => Params::getParam('date'),
                'date_control'  => Params::getParam('dateControl'),
                's_url'         => Params::getParam('s_url'),
                'b_active'      => Params::getParam('b_active')
            ));
            $this->processData($registers);

            $this->total = MyPlugin::newInstance()->registersTotal();
            $this->total_filtered = $this->total;

            return $this->getData();
        }

 

- Header [classes/datatables/CrudDataTable.php]:

        private function addTableHeader()
        {
            $this->addColumn('status-border', '');
            $this->addColumn('status', __("Status", 'my_plugin'));
            $this->addColumn('bulkactions', '<input id="check_all" type="checkbox" />');

            $this->addColumn('name', __("Name", 'my_plugin'));

            $dummy = &$this;
            osc_run_hook("admin_my_plugin_registers_table", $dummy);
        }

- Table content processing [classes/datatables/CrudDataTable.php]:

        private function processData($registers)
        {
            if(!empty($registers)) {

                foreach($registers as $aRow) {
                    $row = array();

                    $row['status-border']   = '';
                    $row['status']          = $aRow['b_active'];
                    $row['bulkactions']     = '<input type="checkbox" name="id[]" value="' . $aRow['pk_i_id'] . '" /></div>';
                    
                    $row['name']            = $aRow['s_name'] . $actions;

                    $row = osc_apply_filter('my_plugin_registers_processing_row', $row, $aRow);

                    $this->addRow($row);
                    $this->rawRows[] = $aRow;
                }

            }
        }

It can be extended to add options that will be shown in the table, so it looks like this:

        private function processData($registers)
        {
            if(!empty($registers)) {

                foreach($registers as $aRow) {
                    $row = array();
                    $options = array();

                    // Actions of DataTable
                    $options[] = '<a href="'.osc_route_admin_url('my-plugin-admin-crud').'&register='.$aRow['pk_i_id'].'">'.__("Edit", 'my_plugin').'</a>';
                    $options[] = '<a href="javascript:delete_dialog('.$aRow['pk_i_id'].')">'.__("Delete", 'my_plugin').'</a>';

                    if( $aRow['b_active'] == 1 ) {
                        $options[]  = '<a href="javascript:deactivate_dialog('.$aRow['pk_i_id'].')">' . __("Deactivate", 'my_plugin') . '</a>';
                    } else {
                        $options[]  = '<a href="javascript:activate_dialog('.$aRow['pk_i_id'].')">' . __("Activate", 'my_plugin') . '</a>';
                    }

To add more hidden options:

                    $options_more = array();
                    
                    //$options_more[] = '<a href="#">' . __('Custom option') . '</a>';

                    // more actions
                    $options_more = osc_apply_filter('more_actions_manage_registers', $options_more, $aRow);
                    if (count($options_more) > 0 && $options_more != "" && $options_more != NULL) {
                        $moreOptions = '<li class="show-more">'.PHP_EOL.'<a href="#" class="show-more-trigger">'. __("Show more", 'my_plugin') .'...</a>'. PHP_EOL .'<ul>'. PHP_EOL;
                        foreach( $options_more as $actual ) {
                            $moreOptions .= '<li>'.$actual."</li>".PHP_EOL;
                        }
                        $moreOptions .= '</ul>'. PHP_EOL .'</li>'.PHP_EOL;
                    }

Ending with:

                    $actions = '';
                    $options = osc_apply_filter('actions_manage_registers', $options, $aRow);
                    if (count($options) > 0 && $options != "" && $options != NULL) {
                        // create list of actions
                        $auxOptions = '<ul>'.PHP_EOL;
                        foreach( $options as $actual ) {
                            $auxOptions .= '<li>'.$actual.'</li>'.PHP_EOL;
                        }
                        $auxOptions  .= $moreOptions;
                        $auxOptions  .= '</ul>'.PHP_EOL;

                        $actions = '<div class="actions">'.$auxOptions.'</div>'.PHP_EOL;
                    }

                    $row['status-border']   = '';
                    $row['status']          = $aRow['b_active'];
                    $row['bulkactions']     = '<input type="checkbox" name="id[]" value="' . $aRow['pk_i_id'] . '" /></div>';
                    
                    $row['name']            = $aRow['s_name'] . $actions;

Notice that the old line $row['name'] = $sRow['s_name']; now it happened to mean $row['name'] = $aRow['s_name'] . $action;.

You should also note that both for $options and $options_more have a filter ($options = osc_apply_filter('actions_manage_registers', $options, $aRow); and $options_more = osc_apply_filter('more_actions_manage_registers', $options_more, $aRow);) for add options outside the same file specifically from the index.php of another plugin, with the following sample code:

// Custom more options for CrudDataTable
function custom_register_add_more_actions($options_more, $aRow) {
    $options_more[] = '<a href="#">'.__("Option 1", 'my_plugin').'</a>';
    if ($aRow['b_active'] == 1) {
        $options_more[] = '<a href="#">'.__("Option 2", 'my_plugin').'</a>';
    }
    return $options_more;
}
osc_add_filter('more_actions_manage_registers', 'custom_register_add_more_actions');

 

controller/admin/crud.php

This file has a public function doModel() method that with switch operates all the actions of the CRUD that would come from the requests of the forms in the view (views/admin/crud.php). But there is a case that attention should be paid and it is in which the DataTable variables are processed and sent to the respective view.

default:
	$this->_exportVariableToView('registerById', $registerById);

    // DataTable
    require_once MY_PLUGIN_PATH . "classes/datatables/CrudDataTable.php";

    if( Params::getParam('iDisplayLength') != '' ) {
        Cookie::newInstance()->push('listing_iDisplayLength', Params::getParam('iDisplayLength'));
        Cookie::newInstance()->set();
    } else {
        // Set a default value if it's set in the cookie
        $listing_iDisplayLength = (int) Cookie::newInstance()->get_value('listing_iDisplayLength');
        if ($listing_iDisplayLength == 0) $listing_iDisplayLength = 10;
        Params::setParam('iDisplayLength', $listing_iDisplayLength );
    }
    $this->_exportVariableToView('iDisplayLength', Params::getParam('iDisplayLength'));

    $page  = (int)Params::getParam('iPage');
    if($page==0) { $page = 1; };
    Params::setParam('iPage', $page);

    $params = Params::getParamsAsArray();

    $crudDataTable = new CrudDataTable();
    $crudDataTable->table($params);
    $aData = $crudDataTable->getData();
    $this->_exportVariableToView('aData', $aData);

    if(count($aData['aRows']) == 0 && $page!=1) {
        $total = (int)$aData['iTotalDisplayRecords'];
        $maxPage = ceil( $total / (int)$aData['iDisplayLength'] );

        $url = osc_admin_base_url(true).'?'.$_SERVER['QUERY_STRING'];

        if($maxPage==0) {
            $url = preg_replace('/&iPage=(\d)+/', '&iPage=1', $url);
            ob_get_clean();
            $this->redirectTo($url);
        }

        if($page > $maxPage) {
            $url = preg_replace('/&iPage=(\d)+/', '&iPage='.$maxPage, $url);
            ob_get_clean();
            $this->redirectTo($url);
        }
    }

    $bulk_options = array(
        array('value' => '', 'data-dialog-content' => '', 'label' => __("Bulk actions", 'my_plugin')),
        array('value' => 'activate', 'data-dialog-content' => sprintf(__("Are you sure you want to %s the selected register(s)?", 'my_plugin'), strtolower(__("Activate", 'my_plugin'))), 'label' => __("Activate", 'my_plugin')),
        array('value' => 'deactivate', 'data-dialog-content' => sprintf(__("Are you sure you want to %s the selected register(s)", 'my_plugin'), strtolower(__("Deactivate", 'my_plugin'))), 'label' => __("Deactivate", 'my_plugin')),
        array('value' => 'delete', 'data-dialog-content' => sprintf(__("Are you sure you want to %s the selected register(s)", 'my_plugin'), strtolower(__("Delete", 'my_plugin'))), 'label' => __("Delete", 'my_plugin'))
    );

    $bulk_options = osc_apply_filter("register_bulk_filter", $bulk_options);
    $this->_exportVariableToView('bulk_options', $bulk_options);
    break;

 

views/admin/crud.php

- DataTable variables [views/admin/crud.php]:

<?php
$registerById = __get('registerById');

// Vars for build the DataTable
$iDisplayLength = __get('iDisplayLength');
$aData          = __get('aData');
$columns        = $aData['aColumns'];
$rows           = $aData['aRows'];
?>

The view receives the variables from the controller to help arm the DataTable.

 

- DataTable [views/admin/crud.php]:

<!-- DataTable -->
<div class="relative">
    <form class="" id="datatablesForm" method="post" action="<?php echo osc_route_admin_url('my-plugin-admin-crud'); ?>">
        <input type="hidden" name="page" value="plugins" />
        <input type="hidden" name="action" value="renderplugin" />
        <input type="hidden" name="route" value="my-plugin-admin-crud" />

        <!-- Bulk actions -->
        <div id="bulk-actions">
            <label>
                <?php osc_print_bulk_actions('bulk_actions', 'plugin_action', __get('bulk_options'), 'select-box-extra'); ?>
                <input type="submit" id="bulk_apply" class="btn" value="<?php echo osc_esc_html( __("Apply", 'my_plugin') ); ?>" />
            </label>
        </div>

        <!-- DataTable -->
        <div class="table-contains-actions">
            <table class="table" cellpadding="0" cellspacing="0">
                <thead>
                    <tr>
                        <?php foreach($columns as $k => $v) {
                            echo '<th class="col-'.$k.' '.($sort==$k?($direction=='desc'?'sorting_desc':'sorting_asc'):'').'">'.$v.'</th>';
                        }; ?>
                    </tr>
                </thead>
                <tbody>
                <?php if( count($rows) > 0 ) { ?>
                    <?php foreach($rows as $key => $row) {
                        $status = $row['status'];
                        $row['status'] = osc_apply_filter('datatable_crud_status_text', $row['status']);
                         ?>
                        <tr class="<?php echo osc_apply_filter('datatable_crud_status_class',  $status); ?>">
                            <?php foreach($row as $k => $v) { ?>
                                <td class="col-<?php echo $k; ?>"><?php echo $v; ?></td>
                            <?php }; ?>
                        </tr>
                    <?php }; ?>
                <?php } else { ?>
                    <tr>
                        <td colspan="<?php echo count($columns)+1; ?>" class="text-center">
                            <p><?php _e("No data available in table", 'my_plugin'); ?></p>
                        </td>
                    </tr>
                <?php } ?>
                </tbody>
            </table>
            <div id="table-row-actions"></div> <!-- used for table actions -->
        </div>
    </form>
</div>

In essence that would be the DataTable, without its Toolbar (I have omitted it here, but it is complete in the download files).

 

- Pagination of the DataTable [views/admin/crud.php]:

<!-- DataTable pagination -->
<?php
function showingResults(){
    $aData = __get('aData');
    echo '<ul class="showing-results"><li><span>'.osc_pagination_showing((Params::getParam('iPage')-1)*$aData['iDisplayLength']+1, ((Params::getParam('iPage')-1)*$aData['iDisplayLength'])+count($aData['aRows']), $aData['iTotalDisplayRecords'], $aData['iTotalRecords']).'</span></li></ul>';
}
osc_add_hook('before_show_pagination_admin','showingResults');
osc_show_pagination_admin($aData);
?>

Download files

Direct download | Download from Bitbucket