How to show total in datatables?

user_17861987 0
Viewed 57

I have a small problem. I am creating a table with datatable and bootstrap 4 and the truth is that it works great, but I have the following problem: I am trying to put a totalizer so that the total of one (or several) columns appears at the end. The code works fine (Because it calculates the total) the problem is that it places it in the header instead of in the footer. Do you know why? Thanks.

                 echo '<div class="card-body"><table id="html5-extension" class="table table-hover table-striped " style="width:100%">';
                 echo '<thead class=""><tr role="row">
                 <th >'.$palabra['numeroor'].'</th>
                 <th >'.$palabra['serieano'].'</th>
                 <th >'.$palabra['taller'].'</th>
                 <th >'.$palabra['fecha'].'</th>
                 <th >'.$palabra['matricula'].'</th>
                 <th >'.$palabra['vehiculo'].'</th>
                 <th >'.$palabra['cristalroto'].'</th>
                 <th >'.$palabra['compania'].'</th>
                 <th ></th>
                 <th></th>
                   </tr> </thead>';
                  echo ' <tfoot>
                          <td></td>
                          <td></td>
                          <td></td>
                          <td></td>
                          <td></td>
                          <td></td>
                          <td></td>
                          <td></td>
                          <td></td>
                          <td></td>
                      </tfoot>
                    <tbody>';
                     echo '<tr role="row">
                         <td scope="row">'.$lisc['or_interna'].'</td>
                         <td scope="row">'.$lisc['serie_or'].'/'.$lisc['ano_or'].'</td>
                         <td >'.$datostaller->nombre.'</td>
                         <td >'.cfecha($lisc['fecha_entrada']).'</td>
                         <td >'.$lisc['matricula'].'</td>
                         <td >'.$marca.$espacio.$modelo.'</td>
                         <td >'.$arraydano['nombre'].'</td>
                         <td >'.$cia->nombre_comercial.'</td>
                     ';

my js code:

<script>
 //#html5-extension

 $(document).ready(function () {
       $('#html5-extension tfoot td').each(function () {
           var title = $(this).text();
           $(this).html('<input type="text" placeholder="Filtrar.." />');
       });

       var table = $('#html5-extension').DataTable({
           "dom": 'B<"float-left"i><"float-right"f>t<"float-left"l><"float-right"p><"clearfix">',
           "responsive": false,
           "language": {
               "url": "https://cdn.datatables.net/plug-ins/1.10.19/i18n/Spanish.json"
           },
           "initComplete": function () {
               this.api().columns().every(function () {
                   var that = this;

                   $('input', this.footer()).on('keyup change', function () {
                       if (that.search() !== this.value) {
                           that
                               .search(this.value)
                               .draw();
                       }
                   });
               })
           },
              footerCallback: function (row, data, start, end, display) {
              var api = this.api();

              // Remove the formatting to get integer data for summation
              var intVal = function (i) {
                  return typeof i === 'string' ? i.replace(/[\$,]/g, '') * 1 : typeof i === 'number' ? i : 0;
              };

              // Total over all pages
              total = api
                  .column(0)
                  .data()
                  .reduce(function (a, b) {
                      return intVal(a) + intVal(b);
                  }, 0);

              // Total over this page
              pageTotal = api
                  .column(0, { page: 'current' })
                  .data()
                  .reduce(function (a, b) {
                      return intVal(a) + intVal(b);
                  }, 0);

              // Update footer
              $(api.column(0).footer()).html('<th>€' + pageTotal + ' ( €' + total + ' en total)</th>');
          },
            "ordering": false,

           buttons: [
              {
                extend: 'copyHtml5',
                footer: true,
                text: 'Copiar',
                exportOptions: {
                   columns: [ 0, 1, 2, 3, 4, 5, 6, 7 ]
               },
                text: '<i class="glyphicon glyphicon-floppy-disk"></i><span> Copiar</span>'
              },
              {
                extend: 'excelHtml5',
                footer: true,
                autoFilter: true,
                sheetName: 'Ordenes de Trabajo',
                exportOptions: {
                   columns: [ 0, 1, 2, 3, 4, 5, 6, 7 ]
               },
                text: '<i class="glyphicon glyphicon-download-alt"></i><span> Generar Excel</span>',
                filename: 'Listado de Ordenes de Trabajo',
              },
              {
                extend: 'pdfHtml5',
                footer: true,
                exportOptions: {
                   columns: [ 0, 1, 2, 3, 4, 5, 6, 7 ]
               },
                text: '<i class="glyphicon glyphicon-save-file"></i><span> Generar PDF</span>',
                filename: 'Listado de Ordenes de Trabajo',
              },
              {
                extend: 'print',
                footer: true,
                exportOptions: {
                   columns: [ 0, 1, 2, 3, 4, 5, 6, 7 ]
               },
                text: '<i class="glyphicon glyphicon-print"></i><span> Imprimir</span>',
              }
          ],

           "lengthMenu": [[10, 25, 50, -1], [10, 25, 50, "Todos"]]
       });

   });
</script>

I edit. I have the following code so that the search engines appear at the top (instead of at the bottom). I know that's why the total comes out at the top, what I don't know is how to fix the total at the bottom and the search engines at the top

    <style>
        #html5-extension tfoot input {
            width: 100% !important;
        }

        #html5-extension tfoot {
            display: table-header-group !important;
        }
    </style>
bootstrap-4 datatable javascript
1 Answers

I think you would be better off using this example as your starting point, because it places filters in the heading correctly.

The linked example creates a second header row so that the sorting controls can be kept separate from the filtering controls. The orderCellsTop: true option tells DataTables that the first header row is the one to be used for sorting.

(The linked example also uses FixedHeader, which I think you do not need, so you can ignore that piece.)

That leaves you free to place your totals in your footer - which is not used when building the filter controls.

So, now you can handle your totals logic in the footerCallback section of the DataTable, similar to this example, which I think is what you are using already in your DataTable.

Here is a runnable demo with it all combined into one DataTable. My sample data is from the DataTables web site so I use the "age" data for summing (just to show how the code works).

You would still need to change the data, and add back in the Bootstrap library. But this approach does not require you to use your custom CSS to try to manage where controls are placed. And it does not need to use !important anywhere.

$(document).ready(function () {
    // Setup - add a text input to each footer cell
    $('#example thead tr')
        .clone(true)
        .addClass('filters')
        .appendTo('#example thead');
 
    var table = $('#example').DataTable({
        orderCellsTop: true,
        fixedHeader: true,
        
        footerCallback: function ( row, data, start, end, display ) {
            var api = this.api(), data;
 
            // Remove the formatting to get integer data for summation
            var intVal = function ( i ) {
                return typeof i === 'string' ?
                    i.replace(/[\$,]/g, '')*1 :
                    typeof i === 'number' ?
                        i : 0;
            };
 
            // Total over all pages
            total = api
                .column( 3 )
                .data()
                .reduce( function (a, b) {
                    return intVal(a) + intVal(b);
                }, 0 );
 
            // Total over this page
            pageTotal = api
                .column( 3, { page: 'current'} )
                .data()
                .reduce( function (a, b) {
                    return intVal(a) + intVal(b);
                }, 0 );
 
            // Update footer
            $( api.column( 3 ).footer() ).html(
                '€'+pageTotal +' (€'+ total +' total)'
            );
        },
        
        initComplete: function () {
            var api = this.api();
 
            // For each column
            api
                .columns()
                .eq(0)
                .each(function (colIdx) {
                    // Set the header cell to contain the input element
                    var cell = $('.filters th').eq(
                        $(api.column(colIdx).header()).index()
                    );
                    var title = $(cell).text();
                    $(cell).html('<input type="text" placeholder="' + title + '" />');
 
                    // On every keypress in this input
                    $(
                        'input',
                        $('.filters th').eq($(api.column(colIdx).header()).index())
                    )
                        .off('keyup change')
                        .on('change', function (e) {
                            // Get the search value
                            $(this).attr('title', $(this).val());
                            var regexr = '({search})'; //$(this).parents('th').find('select').val();
 
                            var cursorPosition = this.selectionStart;
                            // Search the column for that value
                            api
                                .column(colIdx)
                                .search(
                                    this.value != ''
                                        ? regexr.replace('{search}', '(((' + this.value + ')))')
                                        : '',
                                    this.value != '',
                                    this.value == ''
                                )
                                .draw();
                        })
                        .on('keyup', function (e) {
                            e.stopPropagation();
 
                            $(this).trigger('change');
                            $(this)
                                .focus()[0]
                                .setSelectionRange(cursorPosition, cursorPosition);
                        });
                });
        },
    });
});
<!doctype html>
<html>
<head>
  <meta charset="UTF-8">
  <title>Demo</title>
  <script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
  <script src="https://cdn.datatables.net/1.12.1/js/jquery.dataTables.min.js"></script>
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.12.1/css/jquery.dataTables.min.css">
  <link rel="stylesheet" type="text/css" href="https://datatables.net/media/css/site-examples.css">
</style>

</head>

<body>

<div style="margin: 20px;">

    <table id="example" class="display" style="width:100%">
        <thead>
            <tr>
                <th>Name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Age</th>
                <th>Start date</th>
                <th>Salary</th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td>Tiger Nixon</td>
                <td>System Architect</td>
                <td>Edinburgh</td>
                <td>61</td>
                <td>2011-04-25</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>$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>$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>$433,060</td>
            </tr>
            <tr>
                <td>Airi Satou</td>
                <td>Accountant</td>
                <td>Tokyo</td>
                <td>33</td>
                <td>2008-11-28</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>$372,000</td>
            </tr>
            <tr>
                <td>Herrod Chandler</td>
                <td>Sales Assistant</td>
                <td>San Francisco</td>
                <td>59</td>
                <td>2012-08-06</td>
                <td>$137,500</td>
            </tr>
            <tr>
                <td>Rhona Davidson</td>
                <td>Integration Specialist</td>
                <td>Tokyo</td>
                <td>55</td>
                <td>2010-10-14</td>
                <td>$327,900</td>
            </tr>
            <tr>
                <td>Colleen Hurst</td>
                <td>Javascript Developer</td>
                <td>San Francisco</td>
                <td>39</td>
                <td>2009-09-15</td>
                <td>$205,500</td>
            </tr>
            <tr>
                <td>Sonya Frost</td>
                <td>Software Engineer</td>
                <td>Edinburgh</td>
                <td>23</td>
                <td>2008-12-13</td>
                <td>$103,600</td>
            </tr>
            <tr>
                <td>Jena Gaines</td>
                <td>Office Manager</td>
                <td>London</td>
                <td>30</td>
                <td>2008-12-19</td>
                <td>$90,560</td>
            </tr>
            <tr>
                <td>Quinn Flynn</td>
                <td>Support Lead</td>
                <td>Edinburgh</td>
                <td>22</td>
                <td>2013-03-03</td>
                <td>$342,000</td>
            </tr>
            <tr>
                <td>Donna Snider</td>
                <td>Customer Support</td>
                <td>New York</td>
                <td>27</td>
                <td>2011-01-25</td>
                <td>$112,000</td>
            </tr>
        </tbody>
        <tfoot>
            <tr>
                <th></th>
                <th></th>
                <th></th>
                <th>123</th>
                <th></th>
                <th></th>
            </tr>
        </tfoot>
    </table>
    
</div>

</body>
</html>

user_125673650
Related