Getting sum of salary when using date range filter

FRS4002 · August 2022

First of all, I have this test case http://live.datatables.net/veqetisa/1/edit
and I don't need to display the table on load. I need to display the table when the user choose dates. I did this by using hide and show functions and it is working perfectly fine, but is this a good way for this? Or there is a better way? Second, I don't need to display the table unless BOTH dates are entered not one date only, also I did this that as shown above in the test case, Is there a better way of doing this? Third, the important thing, when the user enters both dates, I need to get the sum of salary and display it rather than showing the original table (which is the date and salary). How can I do this?

kthorngren · August 2022

One problem is this:

  $('#min').on('change', function(){
  $('#max').on('change', function(){
...

Each time you change #min you are creating a new (additional) event handler for #max. Now when you change max the event handler will run multiple times. Plus you have the reverse of this so you will end up with multiple event handlers running when selecting the dates. Remove the inner event handler.

Instead of OR use an AND conditional, like this:

  if($('#min').val().length > 0 && $('#max').val().length > 0) {
      $('#mytable').show();
        mytable.draw();
    }else{
    $('#mytable').hide();
    }

This example shows how to get the sum of a column. Use the selector-modifier of { search:'applied' } to sum the data of the matched rows. Similar to the Total over this page example.

Kevin

FRS4002 · August 2022

@kthorngren Thanks Kevin! I fixed the min and max issue. Now regarding the sum, here is the test case that I did http://live.datatables.net/fifemisi/1/edit
I faced a lot of errors, and I don't really know how to fix them... I removed total over this page section because I need it on all pages... Also, footerCallback, does that means the sum will be displayed at the footer? I don't need to display it at the footer I just need to display it instead of the table (replace the whole table with the sum). Is this possible?

kthorngren · August 2022

Using the footerCallback is one option. Its one of many callbacks that are called on every draw. The key is it gives you easy access to the footer. You don't have to place the result in the footer though. You can place it in any DOM element. Just to eliminate confusion, when you debug the code a year from now, it might be better to use a more generic callback like `-option drawCallback.

I faced a lot of errors

You have the footerCallback code outside the Datatables initialization code. It needs to be placed in the init object, like the example.

Kevin

FRS4002 · August 2022

@kthorngren My bad... I now placed the code inside datatables init code here is the test case http://live.datatables.net/lasapecu/1/edit
But I'm still getting errors... I copied the same code from the link https://datatables.net/examples/advanced_init/footer_callback.html
with some changes, but still I am facing errors...

kthorngren · August 2022

You are getting this error:

Uncaught SyntaxError: Unexpected token ';'

This line is not correct:

return typeof i === 'string' ? typeof i === 'number' ? i : 0;

I replaced it with the statement from the footercallback example.

Next you are getting this error:

Uncaught ReferenceError: table is not defined

Its from this line because you don't have table defined:

table.rows( {search:'applied'} ).nodes();

Next you are getting this error:

datatables.min.js:67 Uncaught TypeError: Cannot set properties of undefined (setting 'nTf')

It's due to having

                <tfoot>
            <tr>
                <th colspan="4" style="text-align:right">Total:</th>
                <th></th>
            </tr>
        </tfoot>

You have 2 columns defined for the table but you have essentially defined 5 with the colspan="4"

I added the {search:'applied'}. I believe this is what you are asking for.

http://live.datatables.net/lasapecu/2/edit

Kevin

FRS4002 · August 2022

@kthorngren Yep! That's right! Now, I edited the code so it appears like this http://live.datatables.net/wijiqaqa/1/edit
which almost near to what I want... Now I need to display the "total" under "amount" in the table not in the footer... How this can be done?

kthorngren · August 2022

I would add a span tag, with an id attribute, to the cell you want to place the total then update the span with the total. Like this:
http://live.datatables.net/wijiqaqa/2/edit

Two changes were made:

      <tr>
        <td>Total sales</td>
        <td><span  id="amount"></span></td>
        <td></td>
      </tr>

    $('#amount').html(total.toFixed(2));

Not the use of toFixed() to show only two decimal places. Remove the toFixed to see the difference.

Kevin

FRS4002 · August 2022

@kthorngren Yeah, thanks it worked! Now after a lot of testing, I've found there is some validation issue in this code
if($('#min').val().length > 0 && $('#max').val().length > 0) {
this code means if both inputs are not empty. I must change this to, if both inputs has VALID dates not if the they are not empty. Because, if you type in the first input 01/02/2022 and in the second input 0/02/2022 it will still give me the total, which is not what I want... So, I need to check if both inputs has valid dates to calculate total else show nothing. What is the best way to do this?

kthorngren · August 2022

Since you are using moment.js in the plugin maybe you can use moment.js' isValid() method. You can find examples in their docs.

Kevin

FRS4002 · August 2022

@kthorngren Thanks Kevin! Now I implemented the code in the last test case in my real project (almost the same code) it worked when I try to input 2 same dates, but when I try to input 2 different dates, it will always gives me the the first record (the first amount), meaning it only recognizes the first amount. Also, another issue, if I input same dates and then if I need to change either dates, it will still give me the first amount, it does not update...So, I found that it will always gives me the first amount or 0.00 So, I thought the issue is that the data that I brought is from the database, I am using PHP and MYSQL to bring the data, and I think this is the issue. So, I used ajax, and I successfully brought the data. I added this code to my previous code:
"ajax":{ "url": "ajax/datatables_data.php", "dataSrc": "" }, "columns" :[ {"data": "invoice_date"}, {"data": "final_total"} ],
Is it really the issue that the data is coming from the database? And is it correct what I did which is using ajax? Or the issue is not that, it is just another issue? Because even when I used ajax, and input 2 dates, I am always getting 0.00 total... I am really confused... By the way, I checked if the data is really being brought from the database, both using ajax or just using PHP and MYSQL and the data appeared well. Also, I tried entering data as pure HTML like the test case above everything worked 100%. So, I guess the issue is related to the data being brought from the database. Please I need you to confirm.

kthorngren · August 2022

Sounds like you are having a problem with the totals from the footerCallback. The data source doesn’t matter. There might be a difference with the data. Use the browser’s debugger or console log statements to debug the footerCallback function.

Kevin

FRS4002 · August 2022

@kthorngren Could you please give me more details? Is what I did correct? Which is using ajax? Or no need for ajax? You said data source doesn't matter... But I think it does matter I am not sure, because for example, when using PHP and MYSQL, the total in the second table does not update because it is static, whereas using ajax the data is not static... Does it make sense? Or I am wrong? Because, as I said previously when I enter the data in the table using pure HTML as the test case, it works perfectly fine.

FRS4002 · August 2022

@kthorngren Ok, after a lot of testing I made it work! I found that I MUST use ajax in this situation, also my issue was in the date format. Ok, now I have this test case http://live.datatables.net/hobobabe/1/edit
For some reason, buttons are not showing in the test case but in my real project is showing, anyways. I need to show the PDF button and print button when the second table appear. Also, I need when I press on PDF button, download the table as PDF it is working, but it gives 0.00 as an amount... Same thing for print button. Why is this happening?

kthorngren · August 2022

buttons are not showing in the test case

You are loading datatables.js including the buttons multiple times. Only load them once. I used the download builder to generate new CDN with buttons, etc and replaced everything you had.

but it gives 0.00 as an amount... Same thing for print button. Why is this happening?

You turned your second table into a Datatable. When the footerCallback of table1 updates the cell in table2 its using the jQuery method:

  $('#mytable2 td:eq(1)').html(total.toFixed(2));

Now that table 2 is a Datatable you need to tell it about the update. Datatables does not know about this direct HTML update. See this FAQ for more details. In this case you can use cell().data() to update the Datatable. Note you will need to initialize the second table first or you will get errors when the first table initializes. Or use ataTable.isDataTable() to check if its a Datatable before using the cell().data() command.

Updated example:
http://live.datatables.net/xalahegu/1/edit

Kevin

FRS4002 · August 2022

@kthorngren Thanks Kevin! That worked! I did use isValid, but I think I need more of validation. Try to input dates that are not available in the table, it will show the table with 0.00 amount. I don't need that, I need to show no matching records instead of the table. So, the table mustn't show unless there is a valid date + date available in the first table. Last thing, If I press the PDF button and download the table it worked perfectly fine, but I need to change the width of the table, as you can see the width is too short in comparison of the table in the page...

kthorngren · August 2022

Try to input dates that are not available in the table, it will show the table with 0.00 amount.

One option is to get the number of rows in table 1 after the plugin runs. You can use rows() with count() to get the number of rows displayed, something like this:

table1.rows({search: 'applied'}).count(

If the result is 0 then display "No matching records" otherwise show table 2.

but I need to change the width of the table,

See if this thread about setting columns widths when exporting PDF helps.

Kevin

FRS4002 · August 2022

@kthorngren Thanks Kevin! I fixed the width issue. This thread https://datatables.net/forums/discussion/62138/how-to-set-pdf-column-widths-using-customize
didn't fix the width issue. I used this https://datatables.net/forums/discussion/33464/export-pdf-with-100-width
and this
https://stackoverflow.com/questions/48120995/how-do-i-center-the-entire-datatable-in-the-pdf-using-jquery-pdfhtml5
to fix the width. Now regarding
table1.rows({search: 'applied'}).count(
Where should I put it?

kthorngren · August 2022

Place it in your click event. Maybe something like this:

  $('#min, #max').on('input change', function () {
   if(moment($('#min').val(), 'DD/MM/YYYY').isValid() && moment($('#max').val(), 'DD/MM/YYYY').isValid()){
      mytable1.draw();
      if ( table1.rows({search: 'applied'}).count() === 0 ) {
          // Code to display No matching records element
      } else {
          $('#mytable2').show();
      }
  }else{

Kevin

FRS4002 · August 2022

@kthorngren Thanks Kevin! But the code you wrote only works when BOTH dates are not available in the table. But if one date is available, the second table will still appear...

kthorngren · August 2022

Sounds like your search plugin is not hiding all of the rows the way you expect. I would start by commenting out the statements that hide table1 so you can see what is happening. Then make adjustments to your search plugin to hide the rows as per your requirements. I suspect the initial table shows all the rows and your click event only updates when both inputs are valid.

Kevin

FRS4002 · August 2022

@kthorngren Ok, I think I have a better idea... Since I am using ajax anyways, can I have only 1 datatables table, and I do the date range filter + calculations all server side from the database, meaning the date range filter will take both inputs directly from datatabase and calculate amount, and then showing the amount in 1 table rather than having 2 tables and rather than showing all rows for the first table and filter from the first table... Also, I think this should help of taking long time to load all records from the database and reduce load. I am using PHP and MYSQL. Is this better and possible?

kthorngren · August 2022

Thats a fantastic idea. You can use ajax.data as a function for table2 to pass the two date inputs. The server script can fetch and compile the data the way you want for table2. In the change event use ajax.reload() to fetch the new data set. See this example for passing the parameters. You don't need to enable server side processing for this to work.

Kevin

FRS4002 · August 2022

@kthorngren After searching on YouTube for video explanations on how to do it, all videos that I watched, data must be fetched/display in a table first to start using date range filter... What I need to do is that I don't want to fetch/display anything on a table, I just need on the screen the 2 date inputs, and then after I enter both dates, the salary must be calculated automatically and display in a table, without fetching/showing data in a previous table. Is that possible? And if possible please is there any explanations on how to do it?

kthorngren · August 2022

There isn't much to change in the client. You will remove the search plugin and table 1. Table 2 will fetch the data via ajax and use ajax.data aas a function to send the min and max inputs as parameters. In the input change event you will use ajax.reload() instead of mytable1.draw();.

You will need to use the ajax.reload() callback function to display the No matching rows or the table. Something like this:

$('#min, #max').on('input change', function () {
 if(moment($('#min').val(), 'DD/MM/YYYY').isValid() && moment($('#max').val(), 'DD/MM/YYYY').isValid()){
    mytable12.ajax.reload(  function ( json ) {
      if ( json.data.length === 0 ) {
        // Code to display No matching records element
      } else {
          $('#mytable2').show();
      }
    } );
}else{

This will check the length of the returned JSON data. I didn't test the code so there might be errors but it should get you close.

Your server script will need to get the min and max parameters and handle them appropriately.

Kevin

FRS4002 · August 2022

@kthorngren It it really confusing to me... When I removed the search plugin, I can't now choose dates... Here is my HTML and Javascript code for now, could you please check it for me:

<div class="container-fluid">
  <div class="row d-flex justify-content-center">
    <div class="col-md-2">
<label>Start date:</label>
<input type="search" class="form-control form-control-sm" id="min">
</div>
<div class="col-md-2">
<label>End date:</label>
<input type="search" class="form-control form-control-sm" id="max">
</div>
</div>
<table class="table table-bordered table-sm table-striped border-secondary" id="mytable">
    <thead><th></th><th>Amount</th><th>Vat</th></thead>
</table>
</div>

<script>
let mytable = $('#mytable').DataTable({
  "ajax":{
    "url": "ajax/datatables_data.php",
    "dataSrc": "",
    data:function(dtParms){
      dtParms.minDate = $('#min').val();
      dtParms.maxDate = $('#max').val();
      return dtParms
}
  },
  "columns" :[
    {"data": "invoice_date"},
    {"data": "final_total"}
  ],
dom: 'rt',
  });

  $('#min, #max').on('input change', function () {
    if(moment($('#min').val(), 'DD/MM/YYYY').isValid() && moment($('#max').val(), 'DD/MM/YYYY').isValid()){
       mytable.ajax.reload(  function ( json ) {
         if ( json.data.length === 0 ) {
          // Code to display No matching records element
         }else{
       $('#mytable').show();
     }
     } );
   }else{
         $('#mytable').hide();
   }
  });
</script>

kthorngren · August 2022

When I removed the search plugin, I can't now choose dates

That doesn't make sense as the search plugin has nothing to do with choosing the dates. Maybe check the browser's console for errors. Otherwise its hard to say why the date picker isn't working. Please update your test case to show the issue.

If you need to use "dataSrc": "" then change line 38 to if ( json.length === 0 ) { removing the .data.

Kevin

kthorngren · August 2022

Also the ajax.data function you are using does not expect the return dtParms in line 25. Remove it.

Kevin

FRS4002 · August 2022

@kthorngren Ok, after I checked the code above, it seems that I removed datetime code by mistake

let minDate, maxDate;

minDate = new DateTime($('#min'), {
    format: 'DD/MM/YYYY'
});
maxDate = new DateTime($('#max'), {
    format: 'DD/MM/YYYY'
});

Ok now the new full Javascript code is:

let minDate, maxDate;

minDate = new DateTime($('#min'), {
    format: 'DD/MM/YYYY'
});
maxDate = new DateTime($('#max'), {
    format: 'DD/MM/YYYY'
});

let mytable = $('#mytable').DataTable({
  "ajax":{
    "url": "ajax/datatables_data.php",
    "dataSrc": "",
  },
  "columns" :[
    {"data": "invoice_date"},
    {"data": "final_total"}
  ],
dom: 'rt',
  });

  $('#min, #max').on('input change', function () {
    if(moment($('#min').val(), 'DD/MM/YYYY').isValid() && moment($('#max').val(), 'DD/MM/YYYY').isValid()){
       mytable.ajax.reload(  function ( json ) {
         if ( json.length === 0 ) {
          // Code to display No matching records element
         }else{
       $('#mytable').show();
     }
     } );
   }else{
         $('#mytable').hide();
   }
  });

Now I can choose between dates. Also, I guess dataSrc, is important right?
How can I post this as a test case? Since, I am using PHP and MYSQL as a server script. Also, I can't upload it on a live server/host at the moment... Please, If you can keep up with me here. So, does my JavaScript code ok for now?

kthorngren · August 2022

ajax.dataSrc points Datatables to the location of your row data in the JSON response. See the Ajax docs for details. If you are seeing the row data then you have it set correctly.

Looks like you removed the ajax.data function but otherwise there aren't any obvious errors. When I said to remove it I meant just the unexpected return dtParms statement.

Kevin

FRS4002 · August 2022

@kthorngren Thanks Kevin! I added this code

    data:function(dtParms){
      dtParms.minDate = $('#min').val();
      dtParms.maxDate = $('#max').val();
return dtParms
}

after looking on this post https://datatables.net/forums/discussion/44998/serverside-processing-range-search
But now after you told me to remove only return dtParams I removed it as you said

    data:function(dtParms){
      dtParms.minDate = $('#min').val();
      dtParms.maxDate = $('#max').val();
}

Now where should I do the calculation of the salary? Is it in client script or server script? Here is the server script, please check it for me:

<?php
$minDate = $_GET['minDate'];
$maxDate = $_GET['maxDate'];
$sql = $db->query("SELECT DATE_FORMAT(invoice_date, '%d/%m/%Y')
                   AS invoice_date, final_total
                   FROM invoice
                   WHERE invoice_date LIKE '$minDate' AND invoice_date LIKE '$maxDate'");
$rows = array();
while($row = mysqli_fetch_array($sql)){
  $rows[] = $row;
}

echo json_encode($rows, JSON_UNESCAPED_UNICODE);

Is the server script ok?

Getting sum of salary when using date range filter

Getting sum of salary when using date range filter

Answers

Howdy, Stranger!

Categories

DataTables

Getting sum of salary when using date range filter

Getting sum of salary when using date range filter

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables