2

The code below is working perfectly but I need to add a new functionality to allow the user to filter on a range of records based on their start date and end date, the user should be able to get all the records in between, I was able to find a datatables page tutorial to implement this but I was not able to add that to the code, can you please help me, here is the link

https://datatables.net/plug-ins/filtering/row-based/range_dates

$(document).ready(function() {
  $(function() {
    $("#datepickerStart").datepicker();
    $("#datepickerEnd").datepicker();
  });

  // Setup - add a text input to each footer cell
  $('#example tfoot th').each(function() {
    var title = $(this).text();
    if (title === "Start date") {
      $(this).html('<input type="text" id="datepickerStart" placeholder="Search ' + title + '" />');
    } else if (title === "End date") {
      $(this).html('<input type="text" id="datepickerEnd" placeholder="Search ' + title + '" />');
    } else {
      $(this).html('<input type="text" placeholder="Search ' + title + '" />');
    }
  });

  // DataTable
  var table = $('#example').DataTable({


  });







  $('#example tbody').on('click', 'tr', function() {
    $(this).toggleClass('selected');
  });


  $('#example tbody')
    .on('mouseenter', 'td', function() {
      var colIdx = table.cell(this).index().column;

      $(table.cells().nodes()).removeClass('highlight');
      $(table.column(colIdx).nodes()).addClass('highlight');
    });


  $('#button').click(function() {
    alert(table.rows('.selected').data().length + ' row(s) selected');
  });

  // Apply the search
  table.columns().every(function() {
    var that = this;

    $('input', this.footer()).on('keyup change', function() {
      if (that.search() !== this.value) {
        that
          .search(this.value)
          .draw();
      }
    });
  });
});
tfoot input {
  width: 100%;
  padding: 3px;
  box-sizing: border-box;
}

tr.highlight {
  background-color: blue !important;
}
<script src="https://code.jquery.com/jquery-1.11.1.min.js"></script>
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
<script src="https://cdn.datatables.net/1.10.13/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/plug-ins/9dcbecd42ad/integration/jqueryui/dataTables.jqueryui.js"></script>
<link rel="stylesheet" href="https://code.jquery.com/ui/1.10.13/themes/smoothness/jquery-ui.css">
<link rel="stylesheet" href="https://cdn.datatables.net/plug-ins/9dcbecd42ad/integration/jqueryui/dataTables.jqueryui.css">
<link rel="stylesheet" href="https://code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">


<table id="example" class="display" cellspacing="0" width="100%">

  <thead>
    <tr>
      <th>Name</th>
      <th>Position</th>
      <th>Office</th>
      <th>Age</th>
      <th>Start date</th>
      <th>End date</th>
      <th>Salary</th>
    </tr>
  </thead>
  <tfoot>
    <tr>
      <th>Name</th>
      <th>Position</th>
      <th>Office</th>
      <th>Age</th>
      <th id="min">Start date</th>
      <th id="max">End date</th>
      <th>Salary</th>
    </tr>
  </tfoot>
  <tbody>
    <tr>
      <td>Tiger Nixon</td>
      <td>System Architect</td>
      <td>Edinburgh</td>
      <td>61</td>
      <td>2011/04/25</td>
      <td>2011/04/27</td>
      <td>$320,800</td>
    </tr>
    <tr>
      <td>Garrett Winters</td>
      <td>Accountant</td>
      <td>Tokyo</td>
      <td>63</td>
      <td>2011/07/25</td>
      <td>2011/04/29</td>
      <td>$170,750</td>
    </tr>
    <tr>
      <td>Ashton Cox</td>
      <td>Junior Technical Author</td>
      <td>San Francisco</td>
      <td>66</td>
      <td>2009/01/12</td>
      <td>2011/04/28</td>
      <td>$86,000</td>
    </tr>
    <tr>
      <td>Cedric Kelly</td>
      <td>Senior Javascript Developer</td>
      <td>Edinburgh</td>
      <td>22</td>
      <td>2012/03/29</td>
      <td>2012/03/30</td>
      <td>$433,060</td>
    </tr>
    <tr>
      <td>Airi Satou</td>
      <td>Accountant</td>
      <td>Tokyo</td>
      <td>33</td>
      <td>2008/11/28</td>
      <td>2008/11/30</td>
      <td>$162,700</td>
    </tr>
    <tr>
      <td>Brielle Williamson</td>
      <td>Integration Specialist</td>
      <td>New York</td>
      <td>61</td>
      <td>2012/12/02</td>
      <td>2012/12/21</td>
      <td>$372,000</td>
    </tr>

  </tbody>
</table>

Improve this question

3 Answers 3

Reset to default
0 
    <script src="https://code.jquery.com/jquery-1.11.1.min.js"></script> 
    <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
    <script src="https://cdn.datatables.net/1.10.13/js/jquery.dataTables.min.js"></script>
    <script src="//cdn.datatables.net/plug-ins/1.10.13/filtering/row-based/range_dates.js"></script>
    <script src="https://cdn.datatables.net/plug-ins/9dcbecd42ad/integration/jqueryui/dataTables.jqueryui.js"></script>
    <link rel="stylesheet" href="https://code.jquery.com/ui/1.10.13/themes/smoothness/jquery-ui.css">
    <link rel="stylesheet" href="https://cdn.datatables.net/plug-ins/9dcbecd42ad/integration/jqueryui/dataTables.jqueryui.css">
    <link rel="stylesheet" href="https://code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
    <script type="text/javascript" >

    $(document).ready(function() {
        $( function() {
         $( "#datepickerStart" ).datepicker();
          $( "#datepickerEnd" ).datepicker();
        } );

        // Setup - add a text input to each footer cell
        $('#example tfoot th').each( function () {
            var title = $(this).text();
            if (title === "Start date") {
                $(this).html( '<input type="text" id="datepickerStart" placeholder="Search '+title+'" />' );
                }
            else if (title === "End date") {
                $(this).html( '<input type="text" id="datepickerEnd" placeholder="Search '+title+'" />' );
                }
                else {
                                    $(this).html( '<input type="text" placeholder="Search '+title+'" />' );
                }
        } );

        // DataTable
        var table = $('#example').DataTable({ 

      $('#min').keyup( function() { table.draw(); } );
      $('#max').keyup( function() { table.draw(); } );

        }
        );


        var iFini = document.getElementById('datepickerStart').value; 
        var iFfin = document.getElementById('datepickerEnd').value; 
        var iStartDateCol = 4; 
        var iEndDateCol = 5; 


        $.fn.dataTableExt.afnFiltering.push(
    function( oSettings, aData, iDataIndex ) {
        var iFini = document.getElementById('fini').value;
        var iFfin = document.getElementById('ffin').value;
        var iStartDateCol = 6;
        var iEndDateCol = 7;

        iFini=iFini.substring(6,10) + iFini.substring(3,5)+ iFini.substring(0,2);
        iFfin=iFfin.substring(6,10) + iFfin.substring(3,5)+ iFfin.substring(0,2);

        var datofini=aData[iStartDateCol].substring(6,10) + aData[iStartDateCol].substring(3,5)+ aData[iStartDateCol].substring(0,2);
        var datoffin=aData[iEndDateCol].substring(6,10) + aData[iEndDateCol].substring(3,5)+ aData[iEndDateCol].substring(0,2);

        if ( iFini === "" && iFfin === "" )
        {
            return true;
        }
        else if ( iFini <= datofini && iFfin === "")
        {
            return true;
        }
        else if ( iFfin >= datoffin && iFini === "")
        {
            return true;
        }
        else if (iFini <= datofini && iFfin >= datoffin)
        {
            return true;
        }
        return false;
    }
);




        $('#example tbody').on( 'click', 'tr', function () {
        $(this).toggleClass('selected');
        } );


         $('#example tbody')
            .on( 'mouseenter', 'td', function () {
                var colIdx = table.cell(this).index().column;

                $( table.cells().nodes() ).removeClass( 'highlight' );
                $( table.column( colIdx ).nodes() ).addClass( 'highlight' );
            } );


        $('#button').click( function () {
            alert( table.rows('.selected').data().length +' row(s) selected' );
        } );

        // Apply the search
        table.columns().every( function () {
            var that = this;

            $( 'input', this.footer() ).on( 'keyup change', function () {
                if ( that.search() !== this.value ) {
                    that
                        .search( this.value )
                        .draw();
                }
            } );
        } );
    } );






    </script>

    <style>
    tfoot input {
            width: 100%;
            padding: 3px;
            box-sizing: border-box;
        }
    tr.highlight {
        background-color: blue !important;
    }

    </style>





    <table id="example" class="display" cellspacing="0" width="100%">

            <thead>
                <tr>
                    <th>Name</th>
                    <th>Position</th>
                    <th>Office</th>
                    <th>Age</th>
                    <th>Start date</th>
                    <th>End date</th>
                    <th>Salary</th>
                </tr>
            </thead>
            <tfoot>
                <tr>
                    <th>Name</th>
                    <th>Position</th>
                    <th>Office</th>
                    <th>Age</th>
                    <th id="min">Start date</th>
                    <th id="max">End date</th>
                    <th>Salary</th>
                </tr>
            </tfoot>
            <tbody>
                <tr>
                    <td>Tiger Nixon</td>
                    <td>System Architect</td>
                    <td>Edinburgh</td>
                    <td>61</td>
                    <td>2011/04/25</td>
                    <td>2011/04/27</td>
                    <td>$320,800</td>
                </tr>
                <tr>
                    <td>Garrett Winters</td>
                    <td>Accountant</td>
                    <td>Tokyo</td>
                    <td>63</td>
                    <td>2011/07/25</td>
                    <td>2011/04/29</td>
                    <td>$170,750</td>
                </tr>
                <tr>
                    <td>Ashton Cox</td>
                    <td>Junior Technical Author</td>
                    <td>San Francisco</td>
                    <td>66</td>
                    <td>2009/01/12</td>
                    <td>2011/04/28</td>
                    <td>$86,000</td>
                </tr>
                <tr>
                    <td>Cedric Kelly</td>
                    <td>Senior Javascript Developer</td>
                    <td>Edinburgh</td>
                    <td>22</td>
                    <td>2012/03/29</td>
                    <td>2012/03/30</td>
                    <td>$433,060</td>
                </tr>
                <tr>
                    <td>Airi Satou</td>
                    <td>Accountant</td>
                    <td>Tokyo</td>
                    <td>33</td>
                    <td>2008/11/28</td>
                    <td>2008/11/30</td>
                    <td>$162,700</td>
                </tr>
                <tr>
                    <td>Brielle Williamson</td>
                    <td>Integration Specialist</td>
                    <td>New York</td>
                    <td>61</td>
                    <td>2012/12/02</td>
                    <td>2012/12/21</td>
                    <td>$372,000</td>
                </tr>

            </tbody>
        </table>
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you, were you able to load that html page ? I just copy/pasted your code and the page did not load for me ...
0

You should be able to put that code anywhere within your javascript after you build the table.

You just have to change these lines like so:

    $.fn.dataTableExt.afnFiltering.push(
function( oSettings, aData, iDataIndex ) {
  var iFini = document.getElementById('datepickerStart').value; 
  var iFfin = document.getElementById('datepickerEnd').value; 
  var iStartDateCol = 4; 
  var iEndDateCol = 5; 

    iFini=iFini.substring(6,10) + iFini.substring(3,5)+ iFini.substring(0,2);
    iFfin=iFfin.substring(6,10) + iFfin.substring(3,5)+ iFfin.substring(0,2);

    var datofini=aData[iStartDateCol].substring(6,10) + aData[iStartDateCol].substring(3,5)+ aData[iStartDateCol].substring(0,2);
    var datoffin=aData[iEndDateCol].substring(6,10) + aData[iEndDateCol].substring(3,5)+ aData[iEndDateCol].substring(0,2);

    if ( iFini === "" && iFfin === "" )
    {
        return true;
    }
    else if ( iFini <= datofini && iFfin === "")
    {
        return true;
    }
    else if ( iFfin >= datoffin && iFini === "")
    {
        return true;
    }
    else if (iFini <= datofini && iFfin >= datoffin)
    {
        return true;
    }
    return false;
}
);
Improve this answer

2 Comments

Hello, I have added the code and you line as well as the cdn of that plugin, but the page is not loading properly, see below the full code
That code goes under this: function( oSettings, aData, iDataIndex ) { See updated answer.
0

I don't know if helps but i do the thing you ask like this

EDIT

Here more explanation. The $.fn.DataTable.ext.search it's the API provided for Datatables to extend the functions of filtering. First there is an array wich has the names of the tables who has to apply the function below (this is in case there is more than one table on the same page), then gets the value from de datepickers and split for create another Date who has the same format in the three cases (From, To and StartDate). Then compares that dates between them and filter as appropiate. The StartDate is from the current record of the Table.

At the datepickers i added the class date-range-filter

var allowFilter = ['tableOT'];

$('.date-range-filter').change(function() {
            oTable.draw();
        });

$.fn.dataTable.ext.search.push(
      function(settings, data, dataIndex) {
          // check if current table is part of the allow list
          if ( $.inArray( settings.nTable.getAttribute('id'), allowFilter ) == -1 )
          {
              // if not table should be ignored
              return true;
          }
          var min = $("#<%=txtFechaDesde.ClientID %>").val();
          var max = $("#<%=txtFechaHasta.ClientID %>").val();
          var fromDate;
          var toDate;
          // need to change str order before making  date obect since it uses a new Date("mm/dd/yyyy") format for short date.
          var d = data[0].split("/");
          var startDate = new Date(d[1]+ "/" +  d[0] +"/" + d[2]);
          if(min != ""){
              var fd = min.split("/");
              fromDate =  new Date(fd[1]+ "/" +  fd[0] +"/" + fd[2]);
          }
          if(max != ""){
              var td = max.split("/");
              toDate =  new Date(td[1]+ "/" +  td[0] +"/" + td[2]);
          }

          if (fromDate == null && toDate == null) { return true; }
          if (fromDate == null && startDate <= toDate) { return true;}
          if(toDate == null && startDate >= fromDate) {return true;}
          if (startDate <= toDate && startDate >= fromDate) { return true; }
          return false;
      }
    );
Improve this answer

1 Comment

Could you offer more explanation?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.