Complex calculation in datatables sum of columns

问题:

I have a DataTables done as follows:

month_col = [13,14,15,16,17,18,19,20,21,22,23,24];

  $('#revenueTable').DataTable({
    scrollX: true,
    stateSave: true,
    order: [[0, 'asc']],
    lengthMenu: [
        [ 10, 25, 50, -1 ],
        [ '10 rows', '25 rows', '50 rows', 'Show all' ]
    ],
    dom: 'Bfrtip',
    columnDefs: [
            {
                "targets": [ 2,9 ],
                "visible": false,
                "searchable": false
            }
        ],
    buttons: [
      {
        extend: "colvis",
        className: "btn-sm",
        columns: [0,1,3,4,5,6,7,8,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24]
      },
      {
        extend: "pageLength",
        className: "btn-sm"
      },
      {
        extend: "csv",
        className: "btn-sm",
        exportOptions: {
            columns: ':visible'
        }
      },
      {
        extend: "excel",
        className: "btn-sm",
        exportOptions: {
            columns: ':visible'
        }
      },
      {
        extend: "print",
        className: "btn-sm",
        exportOptions: {
            columns: ':visible'
        }
      },
    ],
    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;
      };

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

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

        // Update footer
        $( api.column( value ).footer() ).html(
            '<div style="font-size: 150%;">'+pageTotal+'</div>'
        );
      });

    }

  });

And you can find below a picture of the table with some sample data:

At the moment, the footer callback is summing the columns. The problem is I need to make the sum in a certain condition: If the column "project status" is started then I add the value but if the column "project status" is pipeline then I need to add the value multiplied by the win ratio.

Is it possible?

回答1:

It is definitely possible:

//source data sample
const srcData = [
  {name: 'Network visibility', status: 'Pipeline', ratio: 0.3, jan: 1, feb: 10, mar: 0},
  {name: 'Network visibility', status: 'Pipeline', ratio: 0.3, jan: 2, feb: 0, mar: 20},
  {name: 'Security PoC', status: 'Started', ratio: 0, jan: 0, feb: 5, mar: 0},
  {name: 'EAM recurring consulting', status: 'Started', ratio: 1, jan: 1, feb: 1, mar: 0},
];
//DataTables initialization
const dataTable = $('#revenueTable').DataTable({
  data: srcData,
  order: [],
  columns: [
    {title: 'Project name', data: 'name'},
    {title: 'Project status', data: 'status'},
    {title: 'Win ratio (%)', data: 'ratio', render: data => data*100},
    {title: 'Jan', data: 'jan'},
    {title: 'Feb', data: 'feb'},
    {title: 'Mar', data: 'mar'}
  ],
  drawCallback: function () {
	//append tfoot
	$('#revenueTable').append('<tfoot><tr></tr></tfoot>');
	//grab conditional totals into object
  	const totals = this.api().rows({page:'current'}).data().toArray().reduce((totalsObj, entry) => {
  			Object.keys(entry).slice(3).forEach(month => totalsObj[month] = (totalsObj[month] || 0) + (entry.status == 'Pipeline' ? entry[month] * entry.ratio : entry.status == 'Started' ? entry[month] : 0));
  			return totalsObj;
  		}, {});
  	//render totals object into tfoot row
	$('#revenueTable tfoot').append(Object.values(totals).reduce((totalsHtml, monthTotal) => totalsHtml += `<td>${monthTotal}</td>` , '<td colspan="3" style="text-align:right;font-weight:bold">Monthly totals:</td>'));
  }
});

tfoot td {padding:10px !important}

<!doctype html>
<html>
<head>
  <script type="application/javascript" src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
  <script type="application/javascript" src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
  <script type="application/javascript" src="test.js"></script>
  <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css">
</head>
<body>
<table id="revenueTable"></table>
</body>
</html>