Tuesday, 24 November 2015

Multiple records delete datatables ajax

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.
  1. <script src="<?php echo base_url(); ?>assets/js/jquery-1.11.0.min.js"></script>
  2. <script src="<?php echo base_url(); ?>assets/js/jquery.dataTables.min.js"></script>
  3. <script src="<?php echo base_url(); ?>assets/js/dataTables.bootstrap.js"></script>
  4. <script src="<?php echo base_url();?>assets/js/datatables/responsive/js/datatables.responsive.js">
  5. </script>
  6. <script src="<?php echo base_url(); ?>assets/js/datatables/jquery.dataTables.columnFilter.js">
  7. </script>
2. Your html source file or you can say your view file will goes like below.
  1. <h3 class="page-title">Accounts</h3>
  2. <div class="panel panel-primary" data-collapsed="0">
  3. <div class="col-sm-12">
  4. <div class="panel-body">
  5. <form id="Gexcel" name="Gexcel" method="POST" action="<?php echo base_url(); ?
  6. >accounts/create_document">
  7. <input type="hidden" name="aidds" id="aidds" ><input type="hidden" name="atype" id="atype" value="da" >
  8. </form>
  9. <table id="demo_account_listing" class="display" cellspacing="0" width="100%">
  10. <thead>
  11. <tr>
  12. <th class="no-sort"></th>
  13. <th class="trigger_reload">Sr No</th>
  14. <th>ID</th>
  15. <th>Date</th>
  16. <th class="no-sort">Days Left</th>
  17. <th>Name</th>
  18. <th>Email Address</th>
  19. <th>Country</th>
  20. <th>Phone</th>
  21. <th>Mobile</th>
  22. <th>Photo</th>
  23. <th>Source</th>
  24. <th class="no-sort"> Action</th>
  25. </tr>
  26. </thead>
  27. </table>
  28. </div>
  29. </div>
  30. </div>
  31. <script type="text/javascript">
  32. jQuery(window).load(function () {
  33. var $ = jQuery;
  34. var oAllLinksTable = $("#demo_account_listing").dataTable({
  35. "bProcessing": true,
  36. "bServerSide": true,
  37. "sAjaxSource": baseurl + 'accounts/get_account_listing',
  38. "sPaginationType": "bootstrap",
  39. "oLanguage": {
  40. "sEmptyTable": "No account found.",
  41. "sProcessing": "<img src='<?php echo base_url(); ?>/assets/global/img/loading-spinner-blue.gif'>"
  42. },
  43. "columnDefs": [{
  44. "targets": 'no-sort',
  45. "orderable": false
  46. }],
  47. "aoColumnDefs": [{"bVisible": true, "aTargets": [0]}],
  48. "order": [[0, "desc"]],
  49. processing: true,
  50. "fnServerParams": function (aoData) {
  51. var last_days = $('input[name=days_filter]:checked').val();
  52. aoData.push({name: "days_filter", value: last_days});
  53. },
  54. "fnRowCallback": function (nRow, aData, iDisplayIndex) {
  55. var oSettings = oAllLinksTable.fnSettings();
  56. $("td:nth-child(2)", nRow).html(oSettings._iDisplayStart + iDisplayIndex + 1);
  57. $(nRow).attr("id", 'tr_' + aData[0]);
  58. $("td:nth-child(1)", nRow).html("
  59. <input type='checkbox' name='contacts[]' class='mail-checkbox check_' value='" + aData[0] + "'>");
  60. return nRow;
  61. }
  62. });
  63. var action_radio = '<div class="pull-right check_filter" id="filter_group_radio"><span>
  64. <input type="radio" name="days_filter" id="dsv" class="form-control days_filter" value="7">
  65. Last7 Days</span>&nbsp;<span>
  66. <input type="radio" id="df" name="days_filter" class="form-control days_filter" value="14">
  67. Last 14 Days &nbsp;</span><span>
  68. <input type="radio" id="dthi" name="days_filter" class="form-control days_filter" value="30">
  69. Last 30 Days&nbsp;</span><span>
  70. <input type="radio" id="dsx" name="days_filter" class="form-control days_filter" value="60">
  71. Last 60 Days</span></div>';
  72. var extra_action = action_radio + '<div class="pull-right">
  73. <a href="<?php echo base_url(); ?>accounts/new_account">
  74. <button class="btn purple-plum pull-right" type="button">Open New Demo Account</button>
  75. </a>&nbsp;</div>';
  76. $custome_search_filter = extra_action + ' <span class="btn blue" style="height:34px;">
  77. <input type="checkbox" class="checkallbox" id="select_all_"></span><div class="pull-right">
  78. <div class="btn-group myremove"><button data-toggle="dropdown"
  79. class="btn blue dropdown-toggle dropup" aria-expanded="false">
  80. Bulk Action<i class="fa fa-angle-down"></i></button><ul role="menu"
  81. class="dropdown-menu pull-right">
  82. <li role="presentation"><a data-toggle="modal" class="deleteRecord"
  83. href="" tabindex="-1" role="menuitem">
  84. Delete</a></li><li role="presentation">
  85. <a data-toggle="modal" class="exportExcel" href="" tabindex="-1"
  86. role="menuitem">Export to excel</a></li></ul></div></div>';
  87. $($custome_search_filter).appendTo("#demo_account_listing_length > label");
  88. $("#demo_account_listing_length").parent().removeClass('col-md-6').addClass('col-md-10');
  89. $("#demo_account_listing_filter").parent().removeClass('col-md-6').removeClass('col-sm-12');
  90. $(function () {
  91. $(document).on('change', '.days_filter', function (event) {
  92. event.preventDefault();
  93. oAllLinksTable.fnFilter(this.value, 12);
  94. $(this).find('#filter_group_radio input:radio').prop('checked', true);
  95. });
  96. });
  97. $(document).on('click', '.checkallbox', function () {
  98. if ($('#select_all_').is(':checked')) {
  99. $(".check_").prop("checked", "checked");
  100. } else {
  101. $(".check_").removeAttr('checked');
  102. }
  103. });
  104. $(document).on('click', '.check_', function () {
  105. var $this = $(this);
  106. // $this will contain a reference to the checkbox
  107. var numberOfChecked = $('.check_:checked').length;
  108. var totalCheckboxes = $('.check_').length;
  109. var numberNotChecked = totalCheckboxes - numberOfChecked;
  110. if (numberOfChecked == totalCheckboxes) {
  111. $('#select_all_').prop('checked', 'checked');
  112. }
  113. else {
  114. $('#select_all_').removeAttr('checked');
  115. }
  116. });
  117. $(document).on('click', '.deleteRecord', function (e) {
  118. e.preventDefault();
  119. var allVals = [];
  120. $('.check_').each(function () {
  121. if ($(this).is(':checked')) {
  122. allVals.push($(this).val());
  123. }
  124. });
  125. if ($('.check_').is(':checked')) {
  126. bootbox.confirm("Are you sure you want to delete?", function (result) {
  127. if (result == true) {
  128. $.ajax({
  129. url: baseurl + 'accounts/removeAccounts?time=' + timestamp,
  130. type: 'post',
  131. data: 'atype=la&rids=' + allVals,
  132. dataType: 'json',
  133. success: function (response) {
  134. $("#showmessage_jscbf").html("");
  135. switch (response.Mstatus) {
  136. case 'success':
  137. $.each(response.idds.split(','), function () {
  138. $("#tr_" + this).hide();
  139. });
  140. $(".trigger_reload").trigger('click');
  141. break;
  142. case 'error':
  143. $("#showmessage_jscbf").html(response.msg);
  144. break;
  145. }
  146. }
  147. });
  148. }
  149. });
  150. return false;
  151. } else {
  152. alert("Please select one record to delete.")
  153. }
  154. });
  155. $(document).on('click', '.exportExcel', function (e) {
  156. e.preventDefault();
  157. var allVals = [];
  158. $('.check_').each(function () {
  159. if ($(this).is(':checked')) {
  160. allVals.push($(this).val());
  161. }
  162. });
  163. if ($('.check_').is(':checked')) {
  164. $("#aidds").val(allVals);
  165. document.Gexcel.submit();
  166. } else {
  167. alert("Please select one records to export.")
  168. }
  169. });
  170. });
  171. $(function () {
  172. $(document).on('click', '.removeRcords', function (event) {
  173. event.preventDefault();
  174. var idss = $(this).attr('id');
  175. var rid = idss.split("_")[1];
  176. do_remove_ajax('account_type_remove', rid);
  177. });
  178. });
  179. </script>
Notes:
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
  1. public function get_account_listing() {
  2. $this->load->model('common');
  3. $aColumns = array('id', 'id', 'account_no', 'regdate', 'daysleft', 'name', 'email',
  4. 'country', 'mobile', 'leverage', 'balance', 'lead_source');
  5. $aColumnsWhere = array('a.id', 'a.account_no', 'a.date_time', 'a.email', 'a.leverage', 'a.balance',
  6. 'da.firstname', 'da.lastname', 'da.regdate', 'da.mobile', 'c.country_name', 'da.lead_source');
  7. $sIndexColumn = "id";
  8. $sTable = "account";
  9. //custome filter
  10. $days_filter = (int) $this->input->get('days_filter', TRUE);
  11. $iDisplayStart = $this->input->get('iDisplayStart', true);
  12. $iDisplayLength = $this->input->get('iDisplayLength', true);
  13. $iSortCol_0 = $this->input->get('iSortCol_0', true);
  14. $iSortingCols = $this->input->get('iSortingCols', true);
  15. $sLimit = "";
  16. if (isset($iDisplayStart) && $iDisplayLength != '-1') {
  17. $sLimit = "LIMIT " . $iDisplayStart . ", " .
  18. $iDisplayLength;
  19. }
  20. if (isset($iSortCol_0)) {
  21. $sOrder = "ORDER BY ";
  22. for ($i = 0; $i < intval($iSortingCols); $i++) {
  23. if ($_GET['bSortable_' . intval($_GET['iSortCol_' . $i])] == "true") {
  24. $sOrder .= $aColumns[intval($_GET['iSortCol_' . $i])] . "
  25. " . trim($_GET['sSortDir_' . $i]) . ", ";
  26. }
  27. }
  28. $sOrder = substr_replace($sOrder, "", -2);
  29. if ($sOrder == "ORDER BY") {
  30. $sOrder = "";
  31. }
  32. }
  33. $sSearch = $this->input->get('sSearch', true);
  34. $sWhere = "";
  35. if ($sSearch != "") {
  36. $sWhere = "AND (";
  37. for ($i = 0; $i < count($aColumnsWhere); $i++) {
  38. $sWhere .= $aColumnsWhere[$i] . " LIKE '%" . $sSearch . "%' OR ";
  39. }
  40. $sWhere = substr_replace($sWhere, "", -3);
  41. $sWhere .= ')';
  42. }
  43. if ($days_filter != '') {
  44. $sWhere .= "AND (";
  45. $sWhere .= '' . $aColumnsWhere[2] . ' BETWEEN DATE_SUB(NOW(),INTERVAL ' . $days_filter . ' DAY) AND
  46. NOW()';
  47. //$sWhere .= 'DATE('.$aColumnsWhere[12].')' . " BETWEEN
  48. '" . DATE('Y-m-d',strtotime($start_date)) . "' AND '".DATE('Y-m-d',strtotime($end_date))."'";
  49. $sWhere .= ')';
  50. }
  51. /* Individual column filtering */
  52. for ($i = 0; $i < count($aColumns); $i++) {
  53. if ($_GET['bSearchable_' . $i] == "true" && $_GET['sSearch_' . $i] != '') {
  54. if ($sWhere == "") {
  55. $sWhere = " WHERE ";
  56. } else {
  57. $sWhere .= " AND ";
  58. }
  59. $sWhere .= $aColumns[$i] . " LIKE '%" . trim($_GET['sSearch_' . $i]) . "%'";
  60. }
  61. }
  62. $sEcho = $this->input->get('sEcho');
  63. $result = $this->common->demo_account_listing($sTable, $sWhere, $sOrder, $sLimit,
  64. $aColumns, $sIndexColumn, $sEcho);
  65. echo json_encode($result);
  66. }
4. Your model code goes like below
  1. function demo_account_listing($sTable, $sWhere, $sOrder,$sLimit, $aColumns, $sIndexColumn, $sEcho){
  2. $sJoin_q_fields = "SELECT SQL_CALC_FOUND_ROWS a.id,a.account_no,a.date_time,
  3. a.email,a.leverage,a.balance, da.regdate, da.firstname as name,
  4. da.mobile, c.country_name as country, da.lead_source";
  5. $sJoin_q = " FROM (account a)";
  6. $sJoin_q .= ' INNER JOIN demo_register da ON a.email = da.email';
  7. $sJoin_q .= ' INNER JOIN country c ON da.country = c.id';
  8. $sJoin_q .= ' WHERE a.act_type = "ABC" ';
  9. $qJoin = $sJoin_q_fields . $sJoin_q . ' ' . $sWhere . ' ' . $sOrder . ' ' . $sLimit;
  10. $rResult = $this->db->query($qJoin);
  11. $rResult_array = $rResult->result_array();
  12. $iFilteredTotal = count($rResult_array);
  13. /* Total data set length */
  14. $sJoin_q_count = "SELECT COUNT(" . $sIndexColumn . ") AS TotalRecords";
  15. $sQuery_TR = $sJoin_q_count . $sJoin_q.$sWhere;
  16. $rResult_TR = $this->db->query($sQuery_TR);
  17. $rResult_array_TR = $rResult_TR->result_array();
  18. $iTotal = $rResult_array_TR[0]['TotalRecords'];
  19. $output = array(
  20. "sEcho" => intval($sEcho),
  21. "iTotalRecords" => intval($iTotal),
  22. "iTotalDisplayRecords" => intval($iTotal), //$iFilteredTotal,
  23. "aaData" => array()
  24. );
  25. $i = 0;
  26. $last = intval($iTotal);
  27. $seclast = intval($iTotal) - 1;
  28. foreach ($rResult_array as $aRow) {
  29. $row = array();
  30. foreach ($aColumns as $col) {
  31. if ($col == 'daysleft') {
  32. $rem = $this->ref_time - strtotime($aRow['regdate']);
  33. $row[] = 30 - floor($rem / 86400);
  34. } else if ($aRow[$col] == 'D') {
  35. $row[] = '<a href="javascript:void(0)" id="E_' . $aRow['id'] . '" name=""
  36. class="btn btn-success btn-sm "><i class="entypo-check"></i> Enable</a>';
  37. } else if ($aRow[$col] == 'E') {
  38. $row[] = '<a href="javascript:void(0)" id="D_' . $aRow['id'] . '"
  39. name="broker_account_status_request_ed" class="btn btn-danger btn-sm ">
  40. <i class="entypo-minus"></i> Disable</a>';
  41. } else {
  42. $row[] = $aRow[$col];
  43. }
  44. }
  45. $i++;
  46. if ($i == $last || $i == $seclast) {
  47. $classaction = 'dropup';
  48. } else {
  49. $classaction = '';
  50. }
  51. array_push($row, '<div class="btn-group ' . $classaction . ' ">
  52. <button data-toggle="dropdown" class="btn red dropdown-toggle" aria-expanded="false">Action
  53. <i class="fa fa-angle-down"></i>
  54. </button>
  55. <ul role="menu" class="dropdown-menu pull-right">
  56. <li role="presentation">
  57. <a href="' . base_url() . 'accounts/change_mt4_pw/' . $aRow['id'] . '" tabindex="-1" role="menuitem">
  58. Change Password
  59. </a>
  60. </li>
  61. <li role="presentation">
  62. <a href="' . base_url() . 'accounts/delete_account/' . $aRow['id'] . '" tabindex="-1" role="menuitem">
  63. Delete Account
  64. </a>
  65. </li>
  66. <li role="presentation">
  67. <a href="' . base_url() . 'accounts/link_account/' . $aRow['id'] . '" tabindex="-1" role="menuitem">
  68. Link Account
  69. </a>
  70. </li>
  71. </ul>
  72. </div>');
  73. $output['aaData'][] = $row;
  74. }
  75. return $output;
  76. }
5. Your view load function will goes like below
  1. public function index() {
  2. $data['meta_title'] = 'Accounts';
  3. $data['meta_keywords'] = 'Accounts';
  4. $data['meta_desc'] = 'Accounts';
  5. $data['top_mobile_menu'] = 'admin/dashboard/top_mobile_menu';
  6. $data['top_menu'] = 'admin/dashboard/top_menu';
  7. $data['main'] = 'admin/dashboard/accounts/account_listing';
  8. $data['footer'] = 'admin/dashboard/footer';
  9. $this->load->vars($data);
  10. $this->load->view($this->admin_dashboard);
  11. }
6. Your function for exporting i am using dompdf library for codeigniter. you can get it after googling.
  1. public function create_document() {
  2. $this->load->helper('dompdf');
  3. $this->load->helper('file');
  4. $this->load->model('prints');
  5. $account_ids = $this->input->post('aidds');
  6. $account_type = $this->input->post('atype');
  7. $result = $this->prints->print_account_listing(format_in_clause($account_ids));
  8. if (!empty($result)) {
  9. $this->load->library('excel');
  10. $this->excel->to_excel($result, "Aaccount-Listing");
  11. exit;
  12. } else {
  13. $this->session->set_flashdata('msg',
  14. alertmessage($message_type = 'alert-danger', $message = 'Unable to process your requiest.'));
  15. redirect(base_url() . 'accounts');
  16. }
  17. }
7. Your function for removing records.
  1. public function removeAccounts() {
  2. $this->load->model('common');
  3. $id = trim($this->input->post('rids'));
  4. $account_type = trim($this->input->post('atype'));
  5. if (isset($id)) {
  6. $thread_ids = explode(",", $id);
  7. foreach ($thread_ids as $values) {
  8. $this->common->do_remove_records('account', array('id' => $values, 'act_type' => 'DEMO'));
  9. }
  10. echo json_encode(array('Mstatus' => 'success', 'idds' => $id,
  11. 'msg' => alertmessage($message_type = 'alert-success', $message = 'Accounts has been removed.')));
  12. } else {
  13. echo json_encode(array('Mstatus' => 'error',
  14. 'msg' => alertmessage($message_type = 'alert-danger', $message = 'Error in process. Try later.')));
  15. }
  16. }
  17. 8. alertmessage is a helper function for generating bootstrap message.
  18. function alertmessage($message_type, $message) {
  19. $output = '';
  20. if (!empty($message_type) && !empty($message)) {
  21. $output.="<div class='alert showmessage_jscbf " . $message_type . "'>" . $message . "</div>";
  22. return $output;
  23. } else {
  24. return $output;
  25. }
  26. }
9. format_in_clause is a helper function.
  1. function format_in_clause($string) {
  2. if (isset($string)) {
  3. $realArray = explode(',', $string);
  4. if (is_array($realArray)) {
  5. $stringForIn = "'" . implode("','", $realArray) . "'";
  6. return $stringForIn;
  7. } else {
  8. return $string;
  9. }
  10. } else {
  11. return NULL;
  12. }
  13. }