If you want to implement add bulk action like Export CSV , Removing all , single records , Export seleted records to excel and adding
filters like days filter to data tables listings using ajax approch
1. You need to include required jQuery for datatables , ajax processing.
2. Your html source file or you can say your view file will goes like below.
- <script src="<?php echo base_url(); ?>assets/js/jquery-1.11.0.min.js"></script>
- <script src="<?php echo base_url(); ?>assets/js/jquery.dataTables.min.js"></script>
- <script src="<?php echo base_url(); ?>assets/js/dataTables.bootstrap.js"></script>
- <script src="<?php echo base_url();?>assets/js/datatables/responsive/js/datatables.responsive.js">
- </script>
- <script src="<?php echo base_url(); ?>assets/js/datatables/jquery.dataTables.columnFilter.js">
- </script>
Notes:
- <h3 class="page-title">Accounts</h3>
- <div class="panel panel-primary" data-collapsed="0">
- <div class="col-sm-12">
- <div class="panel-body">
- <form id="Gexcel" name="Gexcel" method="POST" action="<?php echo base_url(); ?
- >accounts/create_document">
- <input type="hidden" name="aidds" id="aidds" ><input type="hidden" name="atype" id="atype" value="da" >
- </form>
- <table id="demo_account_listing" class="display" cellspacing="0" width="100%">
- <thead>
- <tr>
- <th class="no-sort"></th>
- <th class="trigger_reload">Sr No</th>
- <th>ID</th>
- <th>Date</th>
- <th class="no-sort">Days Left</th>
- <th>Name</th>
- <th>Email Address</th>
- <th>Country</th>
- <th>Phone</th>
- <th>Mobile</th>
- <th>Photo</th>
- <th>Source</th>
- <th class="no-sort"> Action</th>
- </tr>
- </thead>
- </table>
- </div>
- </div>
- </div>
- <script type="text/javascript">
- jQuery(window).load(function () {
- var $ = jQuery;
- var oAllLinksTable = $("#demo_account_listing").dataTable({
- "bProcessing": true,
- "bServerSide": true,
- "sAjaxSource": baseurl + 'accounts/get_account_listing',
- "sPaginationType": "bootstrap",
- "oLanguage": {
- "sEmptyTable": "No account found.",
- "sProcessing": "<img src='<?php echo base_url(); ?>/assets/global/img/loading-spinner-blue.gif'>"
- },
- "columnDefs": [{
- "targets": 'no-sort',
- "orderable": false
- }],
- "aoColumnDefs": [{"bVisible": true, "aTargets": [0]}],
- "order": [[0, "desc"]],
- processing: true,
- "fnServerParams": function (aoData) {
- var last_days = $('input[name=days_filter]:checked').val();
- aoData.push({name: "days_filter", value: last_days});
- },
- "fnRowCallback": function (nRow, aData, iDisplayIndex) {
- var oSettings = oAllLinksTable.fnSettings();
- $("td:nth-child(2)", nRow).html(oSettings._iDisplayStart + iDisplayIndex + 1);
- $(nRow).attr("id", 'tr_' + aData[0]);
- $("td:nth-child(1)", nRow).html("
- <input type='checkbox' name='contacts[]' class='mail-checkbox check_' value='" + aData[0] + "'>");
- return nRow;
- }
- });
- var action_radio = '<div class="pull-right check_filter" id="filter_group_radio"><span>
- <input type="radio" name="days_filter" id="dsv" class="form-control days_filter" value="7">
- Last7 Days</span> <span>
- <input type="radio" id="df" name="days_filter" class="form-control days_filter" value="14">
- Last 14 Days </span><span>
- <input type="radio" id="dthi" name="days_filter" class="form-control days_filter" value="30">
- Last 30 Days </span><span>
- <input type="radio" id="dsx" name="days_filter" class="form-control days_filter" value="60">
- Last 60 Days</span></div>';
- var extra_action = action_radio + '<div class="pull-right">
- <a href="<?php echo base_url(); ?>accounts/new_account">
- <button class="btn purple-plum pull-right" type="button">Open New Demo Account</button>
- </a> </div>';
- $custome_search_filter = extra_action + ' <span class="btn blue" style="height:34px;">
- <input type="checkbox" class="checkallbox" id="select_all_"></span><div class="pull-right">
- <div class="btn-group myremove"><button data-toggle="dropdown"
- class="btn blue dropdown-toggle dropup" aria-expanded="false">
- Bulk Action<i class="fa fa-angle-down"></i></button><ul role="menu"
- class="dropdown-menu pull-right">
- <li role="presentation"><a data-toggle="modal" class="deleteRecord"
- href="" tabindex="-1" role="menuitem">
- Delete</a></li><li role="presentation">
- <a data-toggle="modal" class="exportExcel" href="" tabindex="-1"
- role="menuitem">Export to excel</a></li></ul></div></div>';
- $($custome_search_filter).appendTo("#demo_account_listing_length > label");
- $("#demo_account_listing_length").parent().removeClass('col-md-6').addClass('col-md-10');
- $("#demo_account_listing_filter").parent().removeClass('col-md-6').removeClass('col-sm-12');
- $(function () {
- $(document).on('change', '.days_filter', function (event) {
- event.preventDefault();
- oAllLinksTable.fnFilter(this.value, 12);
- $(this).find('#filter_group_radio input:radio').prop('checked', true);
- });
- });
- $(document).on('click', '.checkallbox', function () {
- if ($('#select_all_').is(':checked')) {
- $(".check_").prop("checked", "checked");
- } else {
- $(".check_").removeAttr('checked');
- }
- });
- $(document).on('click', '.check_', function () {
- var $this = $(this);
- // $this will contain a reference to the checkbox
- var numberOfChecked = $('.check_:checked').length;
- var totalCheckboxes = $('.check_').length;
- var numberNotChecked = totalCheckboxes - numberOfChecked;
- if (numberOfChecked == totalCheckboxes) {
- $('#select_all_').prop('checked', 'checked');
- }
- else {
- $('#select_all_').removeAttr('checked');
- }
- });
- $(document).on('click', '.deleteRecord', function (e) {
- e.preventDefault();
- var allVals = [];
- $('.check_').each(function () {
- if ($(this).is(':checked')) {
- allVals.push($(this).val());
- }
- });
- if ($('.check_').is(':checked')) {
- bootbox.confirm("Are you sure you want to delete?", function (result) {
- if (result == true) {
- $.ajax({
- url: baseurl + 'accounts/removeAccounts?time=' + timestamp,
- type: 'post',
- data: 'atype=la&rids=' + allVals,
- dataType: 'json',
- success: function (response) {
- $("#showmessage_jscbf").html("");
- switch (response.Mstatus) {
- case 'success':
- $.each(response.idds.split(','), function () {
- $("#tr_" + this).hide();
- });
- $(".trigger_reload").trigger('click');
- break;
- case 'error':
- $("#showmessage_jscbf").html(response.msg);
- break;
- }
- }
- });
- }
- });
- return false;
- } else {
- alert("Please select one record to delete.")
- }
- });
- $(document).on('click', '.exportExcel', function (e) {
- e.preventDefault();
- var allVals = [];
- $('.check_').each(function () {
- if ($(this).is(':checked')) {
- allVals.push($(this).val());
- }
- });
- if ($('.check_').is(':checked')) {
- $("#aidds").val(allVals);
- document.Gexcel.submit();
- } else {
- alert("Please select one records to export.")
- }
- });
- });
- $(function () {
- $(document).on('click', '.removeRcords', function (event) {
- event.preventDefault();
- var idss = $(this).attr('id');
- var rid = idss.split("_")[1];
- do_remove_ajax('account_type_remove', rid);
- });
- });
- </script>
I. Gexcel is a form where all selected ids will be store in hidden id aidds textbox.
II. demo_account_listing_length , you can just open your firebug and can get your id from there.you can see in above html that my id is demo_account_listing so datatable attaching his event to this id.
III. action_radio , $custome_search_filter , extra_action is the filter html , we have to inject to datatables.
IV. fnRowCallback function is a datatable function , i have to put s.no there also $("td:nth-child(1)", nRow) is checkbox which will inject with primary key of your table in mysql.
V. bootbox.confirm is alert box , you can replace simple confim alert box of javascript if you don't need.
VI. $.each(response.idds.split(','), function () { $("#tr_" + this).hide();}); from ajax response comma seperated ids will be revert back from your controller.
3. Your controller function will go like below
4. Your model code goes like below
- public function get_account_listing() {
- $this->load->model('common');
- $aColumns = array('id', 'id', 'account_no', 'regdate', 'daysleft', 'name', 'email',
- 'country', 'mobile', 'leverage', 'balance', 'lead_source');
- $aColumnsWhere = array('a.id', 'a.account_no', 'a.date_time', 'a.email', 'a.leverage', 'a.balance',
- 'da.firstname', 'da.lastname', 'da.regdate', 'da.mobile', 'c.country_name', 'da.lead_source');
- $sIndexColumn = "id";
- $sTable = "account";
- //custome filter
- $days_filter = (int) $this->input->get('days_filter', TRUE);
- $iDisplayStart = $this->input->get('iDisplayStart', true);
- $iDisplayLength = $this->input->get('iDisplayLength', true);
- $iSortCol_0 = $this->input->get('iSortCol_0', true);
- $iSortingCols = $this->input->get('iSortingCols', true);
- $sLimit = "";
- if (isset($iDisplayStart) && $iDisplayLength != '-1') {
- $sLimit = "LIMIT " . $iDisplayStart . ", " .
- $iDisplayLength;
- }
- if (isset($iSortCol_0)) {
- $sOrder = "ORDER BY ";
- for ($i = 0; $i < intval($iSortingCols); $i++) {
- if ($_GET['bSortable_' . intval($_GET['iSortCol_' . $i])] == "true") {
- $sOrder .= $aColumns[intval($_GET['iSortCol_' . $i])] . "
- " . trim($_GET['sSortDir_' . $i]) . ", ";
- }
- }
- $sOrder = substr_replace($sOrder, "", -2);
- if ($sOrder == "ORDER BY") {
- $sOrder = "";
- }
- }
- $sSearch = $this->input->get('sSearch', true);
- $sWhere = "";
- if ($sSearch != "") {
- $sWhere = "AND (";
- for ($i = 0; $i < count($aColumnsWhere); $i++) {
- $sWhere .= $aColumnsWhere[$i] . " LIKE '%" . $sSearch . "%' OR ";
- }
- $sWhere = substr_replace($sWhere, "", -3);
- $sWhere .= ')';
- }
- if ($days_filter != '') {
- $sWhere .= "AND (";
- $sWhere .= '' . $aColumnsWhere[2] . ' BETWEEN DATE_SUB(NOW(),INTERVAL ' . $days_filter . ' DAY) AND
- NOW()';
- //$sWhere .= 'DATE('.$aColumnsWhere[12].')' . " BETWEEN
- '" . DATE('Y-m-d',strtotime($start_date)) . "' AND '".DATE('Y-m-d',strtotime($end_date))."'";
- $sWhere .= ')';
- }
- /* Individual column filtering */
- for ($i = 0; $i < count($aColumns); $i++) {
- if ($_GET['bSearchable_' . $i] == "true" && $_GET['sSearch_' . $i] != '') {
- if ($sWhere == "") {
- $sWhere = " WHERE ";
- } else {
- $sWhere .= " AND ";
- }
- $sWhere .= $aColumns[$i] . " LIKE '%" . trim($_GET['sSearch_' . $i]) . "%'";
- }
- }
- $sEcho = $this->input->get('sEcho');
- $result = $this->common->demo_account_listing($sTable, $sWhere, $sOrder, $sLimit,
- $aColumns, $sIndexColumn, $sEcho);
- echo json_encode($result);
- }
5. Your view load function will goes like below
- function demo_account_listing($sTable, $sWhere, $sOrder,$sLimit, $aColumns, $sIndexColumn, $sEcho){
- $sJoin_q_fields = "SELECT SQL_CALC_FOUND_ROWS a.id,a.account_no,a.date_time,
- a.email,a.leverage,a.balance, da.regdate, da.firstname as name,
- da.mobile, c.country_name as country, da.lead_source";
- $sJoin_q = " FROM (account a)";
- $sJoin_q .= ' INNER JOIN demo_register da ON a.email = da.email';
- $sJoin_q .= ' INNER JOIN country c ON da.country = c.id';
- $sJoin_q .= ' WHERE a.act_type = "ABC" ';
- $qJoin = $sJoin_q_fields . $sJoin_q . ' ' . $sWhere . ' ' . $sOrder . ' ' . $sLimit;
- $rResult = $this->db->query($qJoin);
- $rResult_array = $rResult->result_array();
- $iFilteredTotal = count($rResult_array);
- /* Total data set length */
- $sJoin_q_count = "SELECT COUNT(" . $sIndexColumn . ") AS TotalRecords";
- $sQuery_TR = $sJoin_q_count . $sJoin_q.$sWhere;
- $rResult_TR = $this->db->query($sQuery_TR);
- $rResult_array_TR = $rResult_TR->result_array();
- $iTotal = $rResult_array_TR[0]['TotalRecords'];
- $output = array(
- "sEcho" => intval($sEcho),
- "iTotalRecords" => intval($iTotal),
- "iTotalDisplayRecords" => intval($iTotal), //$iFilteredTotal,
- "aaData" => array()
- );
- $i = 0;
- $last = intval($iTotal);
- $seclast = intval($iTotal) - 1;
- foreach ($rResult_array as $aRow) {
- $row = array();
- foreach ($aColumns as $col) {
- if ($col == 'daysleft') {
- $rem = $this->ref_time - strtotime($aRow['regdate']);
- $row[] = 30 - floor($rem / 86400);
- } else if ($aRow[$col] == 'D') {
- $row[] = '<a href="javascript:void(0)" id="E_' . $aRow['id'] . '" name=""
- class="btn btn-success btn-sm "><i class="entypo-check"></i> Enable</a>';
- } else if ($aRow[$col] == 'E') {
- $row[] = '<a href="javascript:void(0)" id="D_' . $aRow['id'] . '"
- name="broker_account_status_request_ed" class="btn btn-danger btn-sm ">
- <i class="entypo-minus"></i> Disable</a>';
- } else {
- $row[] = $aRow[$col];
- }
- }
- $i++;
- if ($i == $last || $i == $seclast) {
- $classaction = 'dropup';
- } else {
- $classaction = '';
- }
- array_push($row, '<div class="btn-group ' . $classaction . ' ">
- <button data-toggle="dropdown" class="btn red dropdown-toggle" aria-expanded="false">Action
- <i class="fa fa-angle-down"></i>
- </button>
- <ul role="menu" class="dropdown-menu pull-right">
- <li role="presentation">
- <a href="' . base_url() . 'accounts/change_mt4_pw/' . $aRow['id'] . '" tabindex="-1" role="menuitem">
- Change Password
- </a>
- </li>
- <li role="presentation">
- <a href="' . base_url() . 'accounts/delete_account/' . $aRow['id'] . '" tabindex="-1" role="menuitem">
- Delete Account
- </a>
- </li>
- <li role="presentation">
- <a href="' . base_url() . 'accounts/link_account/' . $aRow['id'] . '" tabindex="-1" role="menuitem">
- Link Account
- </a>
- </li>
- </ul>
- </div>');
- $output['aaData'][] = $row;
- }
- return $output;
- }
6. Your function for exporting i am using dompdf library for codeigniter. you can get it after googling.
- public function index() {
- $data['meta_title'] = 'Accounts';
- $data['meta_keywords'] = 'Accounts';
- $data['meta_desc'] = 'Accounts';
- $data['top_mobile_menu'] = 'admin/dashboard/top_mobile_menu';
- $data['top_menu'] = 'admin/dashboard/top_menu';
- $data['main'] = 'admin/dashboard/accounts/account_listing';
- $data['footer'] = 'admin/dashboard/footer';
- $this->load->vars($data);
- $this->load->view($this->admin_dashboard);
- }
7. Your function for removing records.
- public function create_document() {
- $this->load->helper('dompdf');
- $this->load->helper('file');
- $this->load->model('prints');
- $account_ids = $this->input->post('aidds');
- $account_type = $this->input->post('atype');
- $result = $this->prints->print_account_listing(format_in_clause($account_ids));
- if (!empty($result)) {
- $this->load->library('excel');
- $this->excel->to_excel($result, "Aaccount-Listing");
- exit;
- } else {
- $this->session->set_flashdata('msg',
- alertmessage($message_type = 'alert-danger', $message = 'Unable to process your requiest.'));
- redirect(base_url() . 'accounts');
- }
- }
9. format_in_clause is a helper function.
- public function removeAccounts() {
- $this->load->model('common');
- $id = trim($this->input->post('rids'));
- $account_type = trim($this->input->post('atype'));
- if (isset($id)) {
- $thread_ids = explode(",", $id);
- foreach ($thread_ids as $values) {
- $this->common->do_remove_records('account', array('id' => $values, 'act_type' => 'DEMO'));
- }
- echo json_encode(array('Mstatus' => 'success', 'idds' => $id,
- 'msg' => alertmessage($message_type = 'alert-success', $message = 'Accounts has been removed.')));
- } else {
- echo json_encode(array('Mstatus' => 'error',
- 'msg' => alertmessage($message_type = 'alert-danger', $message = 'Error in process. Try later.')));
- }
- }
- 8. alertmessage is a helper function for generating bootstrap message.
- function alertmessage($message_type, $message) {
- $output = '';
- if (!empty($message_type) && !empty($message)) {
- $output.="<div class='alert showmessage_jscbf " . $message_type . "'>" . $message . "</div>";
- return $output;
- } else {
- return $output;
- }
- }
- function format_in_clause($string) {
- if (isset($string)) {
- $realArray = explode(',', $string);
- if (is_array($realArray)) {
- $stringForIn = "'" . implode("','", $realArray) . "'";
- return $stringForIn;
- } else {
- return $string;
- }
- } else {
- return NULL;
- }
- }