How to add one daterange filter which will be applied to first column of all tables?

papicj2

I would like to apply daterange filter to all tables and then export it to excel sheets. I would really appreciate if someone can help me here.
http://live.datatables.net/kuyayeto/1/edit

I have used this script for daterange filter but it works just with first table.

$(document).ready( function () {
     // Date range vars
 minDateFilter = "";
 maxDateFilter = "";

 $("#daterange").daterangepicker({
     "timePicker": true,
    "timePicker24Hour": true
 });
 
 $("#daterange").on("apply.daterangepicker", function(ev, picker) {
  minDateFilter = Date.parse(picker.startDate);
  maxDateFilter = Date.parse(picker.endDate);
  
  $.fn.dataTable.ext.search.push(function(settings, data, dataIndex) {
  var date = Date.parse(data[0]);

  if (
   (isNaN(minDateFilter) && isNaN(maxDateFilter)) ||
   (isNaN(minDateFilter) && date <= maxDateFilter) ||
   (minDateFilter <= date && isNaN(maxDateFilter)) ||
   (minDateFilter <= date && date <= maxDateFilter)
  ) {
   return true;
  }
  return false;
 });
 table.draw();
}); 

kthorngren

Maybe I missed it but I don't see your date range filter in the test case. The first problem is that you are using $.fn.dataTable.ext.search.push( .. ) in teh data picker event. So each time a date is selected an new search plugin instance ($.fn.dataTable.ext.search.push( .. )) will be pushed. After 10 date selections the search plugin will be executed 10 times.

Move the search plugin outside the date packer event so its initialized once. Something like this:

  $.fn.dataTable.ext.search.push(function(settings, data, dataIndex) {
  var date = Date.parse(data[0]);
  minDateFilter = Date.parse(picker.startDate);
  maxDateFilter = Date.parse(picker.endDate);
 
  if (
   (isNaN(minDateFilter) && isNaN(maxDateFilter)) ||
   (isNaN(minDateFilter) && date <= maxDateFilter) ||
   (minDateFilter <= date && isNaN(maxDateFilter)) ||
   (minDateFilter <= date && date <= maxDateFilter)
  ) {
   return true;
  }
  return false;
 });

 $("#daterange").on("apply.daterangepicker", function(ev, picker) {
 table.draw();
});

In the date picker event handler you can call draw() for each table you want. The search plugin will be used for each table. If the date column is different for each table you can use settings.sTableId to get the table ID that the plugin is running against to get the correct column, like this:

  $.fn.dataTable.ext.search.push(function(settings, data, dataIndex) {
  var date = Date.parse(data[0]);

       if ( settings.sTableId !== 'table1 ) {
          date = Date.parse(data[2]);
       } else if ( settings.sTableId !== 'table2' ) {
          date = Date.parse(data[4]);
       }
  minDateFilter = Date.parse(picker.startDate);
  maxDateFilter = Date.parse(picker.endDate);
 
  if (
   (isNaN(minDateFilter) && isNaN(maxDateFilter)) ||
   (isNaN(minDateFilter) && date <= maxDateFilter) ||
   (minDateFilter <= date && isNaN(maxDateFilter)) ||
   (minDateFilter <= date && date <= maxDateFilter)
  ) {
   return true;
  }
  return false;
 });

 $("#daterange").on("apply.daterangepicker", function(ev, picker) {
 table.draw();
});

Kevin

papicj2

Hello Kevin,
Thank you very much for detailed explanation. Apologize for wrong link. Here is the right one. It is similar principe but here are six tables and daterange input also. Maybe now my problem is clearer and you can see just the first table is filtered. Thanks in advance. Hope you can help me.

http://live.datatables.net/tagozopa/1/edit

kthorngren

Here you go:
http://live.datatables.net/gidebiko/1/edit

In addition to the above .... I placed the search plugin after the Datatables initialization so it doesn't apply until the picker event handler executes. In the search plugin I use this to get the date picker inputs:

$("#daterange").data('daterangepicker').startDate

I used the technique in this example to initialize all the Datatables at once into one variable. Then use the tables() API to get all the tables and use draw() in one statement in the event handler. You could individually call draw for each table if you prefer.

Kevin

papicj2

Thanks Kevin, this looks very well but excel button now export all data but not date filtered data. I understand if you have no more patient

kthorngren

See this row selector example. Use search: 'applied as shown in the selector-modifier examples.

Kevin

papicj2

Hi Kevin,
Just first table is filtered in xlsx. file but rest of them are not. Could you please edit http://live.datatables.net/gidebiko/1/edit so I can see the solution.

kthorngren

The simple option is to add the {search: 'applied'} to this loop in the getTableData() function.

    // Loop through each row to append to sheet.    
    table.rows( {search: 'applied'} ).every( function ( rowIdx, tableLoop, rowLoop ) {
      var data = this.data();
      
      // If data is object based then it needs to be converted 
      // to an array before sending to buildRow()
      ws += buildRow(data, rowNum, '');
      
      rowNum++;
    } );

I modified the code you are using in this thread to allow for using some of the buttons customizations like messageTop or the modifier to customize the Excel output. If you need more flexibility changing to the updated code "shouldn't" be too hard. If not then use what you have

Kevin