Getting sum of salary when using date range filter

kthorngren · August 2022

I'm not familiar with PHP so won't be much help there. You can probably use the SUM() function to sum the results of the query to return as the data for the table.

Stack Overflow is a good resource to search for and ask about general PHP and SQL questions.

Kevin

FRS4002 · August 2022

@kthorngren Thanks Kevin! LOL, I think we need @allan or @colin reinforcement for PHP script. Anyways, could you please explain the idea of what I should do in server script, so I can ask clearly in stack overflow? Remember, I need when I load the page I just need to show the 2 date inputs. The table must show only when I choose between 2 dates, with only the salary. So, I don't need to show all records from the database in the page when on load.

kthorngren · August 2022

Take a look at the SUM() link I gave. Expand upon its example:

SELECT SUM(column_name)
FROM table_name
WHERE condition;

The WHERE condition will be the date range and SUM(column_name) is the column you want to sum. You might be able to specify a string for the first column. Maybe something like this:

SELECT 'Total sales', SUM(final_total)
FROM invoice
WHERE invoice_date >='$minDate' AND invoice_date <= '$maxDate'";

SQL date range WHERE condition example:
https://www.garron.me/en/bits/mysql-select-from-range-dates.html

You may or may not be able to get all the rows and columns you want in one sql statement. Depends on your specific data structure and what you are trying to get from the tables. You might need multiple queries which need combined for a single response back to Datatables.

I would start with an admin tool for your DB server to experiment with SQL queries before trying to get them to work with Datatables. Once you get the queries working then do the PHP and Datatables coding. Otherwise you will be fitting a bunch of different issues if you try making them all work at once.

To be honest Allan and Colin's priorities are with answering Datatables specific questions. Their time is limited - they may help with your PHP questions but instead of waiting on them, I would suggest using Stack Overflow as there are more people there to help with both.

Once you get the queries working we'll guide you to package it in a Datatables support format. Make sure to read through the Ajax docs for details. Probably the easiest for the simple table you have is array structure like this example but you can use objects if preferred like this example.

Kevin

FRS4002 · August 2022

@kthorngren Thanks Kevin! After a lot of work, I finally made server script work successfully and I am getting the total appear in the table. But, I have an important thing to do. I need to remove this HTML code:

<table class="table table-bordered table-sm table-striped border-secondary" id="mytable">
    <thead><th></th><th>Amount</th><th>Vat</th></thead>
</table>

and add it to jQuery code using prepend(). So, I need to say if there is a valid date in the database show the table with the total, without using show() hide(). I don't want the table to appear empty when the page is loaded. Here is the full code:

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

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

  $('#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();
   }
  });
</script>

kthorngren · August 2022

There are lots of Stack Overflow threads, like this one, that provide techniques to dynamically add tables using jQuery. You should be able to find a solution that meets your needs. You will need the table inserted before initializing Datatables. And you will need thead inserted or you can use columns.title. See the HTML docs for the requirements.

Kevin

FRS4002 · August 2022

@kthorngren Thanks Kevin! I successfully get the data and showed it in the table 100% finally. But I have a little issue. I need to have a messageTop in the print page, I did it but it does not show... Also, same thing for PDF, Here is the code:

              buttons: [
                {
                  extend: 'print',
                  messageTop: 'test',
                  exportOptions: {
                    columns: [2, 1, 0]
                  },
                  customize: function (doc) {
                    $(doc.document.body).css('text-align', 'center');
                    $(doc.document.body).find('table').css('text-align', 'right');
                  },
                },
              ],

kthorngren · August 2022

Your code snippet seems to work here:
http://live.datatables.net/levahacu/1/edit

Please post a link to your page or a test case, update the above, showing the issue so we can help debug.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

FRS4002 · August 2022

@kthorngren The print button in the test case you post is not working at all. I am using ajax to get data from the datatabse so I can't post a test case... I'll post the whole html and javascript code:

<div class="container-fluid" id="show_here">
  <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>
</div>

<script>
let minDate, maxDate;

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

  $('#min, #max').on('change', function () {
    if($("#min").val().length > 0 && $("#max").val().length > 0){
    $.ajax({
      url: "ajax/datatables_data.php",
      data: {
       'minDate': $('#min').val(),
       'maxDate': $('#max').val(),
      },
      success: function(response){
        if(response){
          if($("#mytable_wrapper").length > 0){
              $("#mytable_wrapper").remove();
            }else{
            $("#show_here").append(`<table class="table table-bordered table-sm table-striped border-secondary" id="mytable">` +
            `<thead><th></th><th>Amount</th><th>Vat</th></thead><tbody><tr><td>Total sales</td><td>${response}` +
            `</td><td></td></tr><tr><td>Total returned</td><td></td><td></td></tr><tr><td></td><td></td><td></td>` +
            `</tr></tbody></table>`);
            $("#no_data").remove();
            let mytable = $('#mytable').DataTable({
              initComplete: function () {
                $('.dt-buttons').removeClass('btn-group');
              },
              dom: 'Brt',
              "ordering": false,
              buttons: [
                {
                  extend: 'print',
                  // messageTop: 'test',
                  exportOptions: {
                    columns: [2, 1, 0]
                  },
                  title: 'moonlight',
                  customize: function (doc) {
                    $(doc.document.body).css('text-align', 'center');
                    $(doc.document.body).find('table').css('text-align', 'right');
                  },
                },
                {
                  extend: 'pdfHtml5',
                  // messageTop: 'test',
                  exportOptions: {
                    columns: [2, 1, 0]
                  },
                  title: 'moonlight',
                  customize: function (doc) {
                    doc.content[1].alignment = 'center';
                    var colCount = new Array();
                    $('table').find('tbody tr:first-child td').each(function(){
                      if($(this).attr('colspan')){
                        for(var i=1;i<=$(this).attr('colspan');$i++){
                          colCount.push('*');
                        }
                      }else{
                        colCount.push('*');
                      }
                    });
                    doc.content[1].table.widths = colCount;
                  },
                },
              ],
            });
          }
       }else{
         if($("#no_data").length > 0){
             $("#no_data").remove();
           }else{
         $("#show_here").append(`<h5 class="text-center" id="no_data"><br><br>No data</h5>`);
         $("#mytable_wrapper").remove();
       }
       }
      }
    });
  }else{
    $("#mytable_wrapper").remove();
  }
  });
</script>

kthorngren · August 2022

When I click the print button I get this so it is working:

What exactly happens when you click the print button in my example?

It looks like you have messageTop commented out in lines 51 and 63.

// messageTop: 'test',

The problem you are reporting is not dependent on your data nor that you are using ajax. We just need you to show the problem you are having. You can use fake data. You can use one of the ajax sources here if you want ajax data for the fake data.

Kevin

FRS4002 · August 2022

@kthorngren Yeah sorry I forgot to remove the comment from both lines when pasted here, but in my real project they are both not commented. When I click on print button nothing shows at all... For PDF, I am getting this error "Uncaught TypeError: Cannot set properties of undefined (setting 'widths')" and I can't download PDF file. But if I commented the messageTop, I can download the PDF but without the messageTop...

FRS4002 · August 2022

@kthorngren Ok I found the issue for PDF! I fixed it by changing this

doc.content[1].table.widths = colCount;

to this:

doc.content[2].table.widths = colCount;

This fixed it for PDF, but for print button still...

kthorngren · August 2022

To help troubleshoot please provide a test case showing the issue. My example with your print button code snippet works.

Kevin

FRS4002 · August 2022

@kthorngren Ok so this test case that you sent http://live.datatables.net/levahacu/1/edit
worked when I click on "Run with JS" button, if I don't click on this button, the print button won't work. And yes the messageTop appeared in your test case but it didn't appear on mine...

FRS4002 · August 2022

@kthorngren Here is the full code:

<script>
let minDate, maxDate;

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

  $(document).ready(function(){
  $('#min, #max').on('change input', function () {
    if($("#min").val().length > 0 && $("#max").val().length > 0){
    $.ajax({
      url: "ajax/datatables_data.php",
      data: {
       'minDate': $('#min').val(),
       'maxDate': $('#max').val(),
      },
      success: function(response){
        if(response){
          if($("#mytable_wrapper").length > 0){
              $("#mytable_wrapper").remove();
            }else{
            let get_vat = new Big(parseFloat(response));
            let vat = new Big(parseFloat(jQuery('#vat').html()));
            let calculate = parseFloat(get_vat.times(vat).div(100));
            $("#show_here").append(`<table class="table table-bordered table-sm table-striped border-secondary" id="mytable">` +
            `<thead><th>وصف</th><th>المبلغ</th><th>الضريبة</th></thead><tbody><tr><td>إجمالي المبيعات</td><td>${response}</td><td>` +
            `${calculate}</td></tr><tr><td>إجمالي مبيعات العائد</td><td>0.00</td><td>0.00</td></tr><tr><td>إجمالي الشراء</td>` +
            `<td>0.00</td><td>0.00</td></tr><tr><td>إجمالي عائد الشراء</td><td>0.00</td><td>0.00</td></tr><tr><td>نفقات أخرى</td>` +
            `<td>0.00</td><td>0.00</td></tr></tbody></table>`);
            $("#no_data").remove();
            let mytable = $('#mytable').DataTable({
              initComplete: function () {
                $('.dt-buttons').removeClass('btn-group');
                $('.buttons-print').removeClass('btn-secondary').addClass('btn-success');
                $('.buttons-pdf').removeClass('btn-secondary').addClass('btn-success');
              },
              dom: 'Brt',
              "ordering": false,
              buttons: [
                {
                  extend: 'print',
                  text: 'طباعة',
                  messageTop: 'test',
                  exportOptions: {
                    columns: [2, 1, 0]
                  },
                  title: '<?=(isset($infoQuery['name'])?$infoQuery['name']:'');?>',
                  customize: function (doc) {
                    $(doc.document.body).css('text-align', 'center');
                    $(doc.document.body).find('table').css('text-align', 'right');
                    // $(doc.document.body).find('table').prepend(`<h5 class="text-center">لا توجد بيانات</h5>`);
                  },
                },
              ],
            });
          }
       }else{
         if($("#no_data").length > 0){
             $("#no_data").remove();
           }else{
         $("#show_here").append(`<h5 class="text-center" id="no_data"><br><br>لا توجد بيانات</h5>`);
         $("#mytable_wrapper").remove();
       }
       }
      }
    });
  }else{
    $("#mytable_wrapper").remove();
  }
  });
});
</script>

kthorngren · August 2022

worked when I click on "Run with JS" button, if I don't click on this button, the print button won't work.

Are you saying that the Print button doesn't appear until you click Run with JS? That is expected with JS Bin. You need to click the Run with JS to execute the page.

but it didn't appear on mine

One problem might be this line:

title: '<?=(isset($infoQuery['name'])?$infoQuery['name']:'');?>',

You are using single quotes for the full string and single quotes within the string, ie, ['name']). This should be giving you a syntax error. Use double quotes for the full string like this:

title: "<?=(isset($infoQuery['name'])?$infoQuery['name']:'');?>",

The messageTop: 'test', looks correct and looks to be in the correct spot. If this doesn't help then please post a test case showing the issue so we can help debug.

Kevin

FRS4002 · August 2022

@kthorngren Ok, I know the issue now. I want if I click on print button, I don't want it to automatically open a new tab. So, I took this 5th comment (jvcunha's comment) that is available here
https://datatables.net/forums/discussion/45126/buttons-print-chrome-problem
and pasted instead of the official code and it worked. But it ruined the messageTop. So, I tried to edit the modified code in line 160, I removed message: '', and I added messageTop: '*', and under it messageBottom:'*',. Now the messageTop appear but it says undefined... Definitely, there must be other modifications in the code, but I don't know how to do it... Is there a better solution to implement messageTop + not to automatically open in a new tab? Or could you please help me edit the code? Allan said he will implement in the source code, but he didn't until now...

Getting sum of salary when using date range filter - Page 2

Getting sum of salary when using date range filter

Answers

Howdy, Stranger!

Categories

DataTables

Getting sum of salary when using date range filter - Page 2

Getting sum of salary when using date range filter

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables