Getting sum of salary when using date range filter

Getting sum of salary when using date range filter

FRS4002FRS4002 Posts: 85Questions: 11Answers: 0

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?

This question has accepted answers - jump to:

«1

Answers

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736
    Answer ✓

    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

  • FRS4002FRS4002 Posts: 85Questions: 11Answers: 0

    @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?

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    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

  • FRS4002FRS4002 Posts: 85Questions: 11Answers: 0

    @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...

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736
    edited 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

  • FRS4002FRS4002 Posts: 85Questions: 11Answers: 0

    @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?

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736
    Answer ✓

    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

  • FRS4002FRS4002 Posts: 85Questions: 11Answers: 0

    @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?

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    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

  • FRS4002FRS4002 Posts: 85Questions: 11Answers: 0
    edited 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.

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    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

  • FRS4002FRS4002 Posts: 85Questions: 11Answers: 0
    edited 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.

  • FRS4002FRS4002 Posts: 85Questions: 11Answers: 0
    edited 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?

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736
    Answer ✓

    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

  • FRS4002FRS4002 Posts: 85Questions: 11Answers: 0
    edited 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...

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    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

  • FRS4002FRS4002 Posts: 85Questions: 11Answers: 0

    @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?

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736
    edited 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

  • FRS4002FRS4002 Posts: 85Questions: 11Answers: 0
    edited 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...

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    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

  • FRS4002FRS4002 Posts: 85Questions: 11Answers: 0

    @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?

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    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

  • FRS4002FRS4002 Posts: 85Questions: 11Answers: 0

    @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?

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    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

  • FRS4002FRS4002 Posts: 85Questions: 11Answers: 0

    @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>
    
  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    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

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

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

    Kevin

  • FRS4002FRS4002 Posts: 85Questions: 11Answers: 0
    edited 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?

  • kthorngrenkthorngren Posts: 20,144Questions: 26Answers: 4,736

    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

  • FRS4002FRS4002 Posts: 85Questions: 11Answers: 0

    @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?

Sign In or Register to comment.