Query on how to produce page and all page totals for calculated columns in datatables

cpshart

Hi

I am trying to produce page and all page totals using the footer callback for columns from my MySQL database tables within Wordpress using a PHP snippet

Extract of my columns, where the totals for calculated columns are returning 0 for page and document totals, but dm_cash_accounts_transactions.amount is totalled correctly

....
    $('#cash_accounts_transactions').DataTable( {
        dom: "Bfrtip",
        ajax: {
            url: '../../Editor-PHP-1.8.1/controllers/cash_accounts_transactions.php',
            type: 'POST',
            data: function ( d ) {
                d.userid = $('#passuserid').val();
                                }
            },
        columns: [
            { data: "dm_cash_accounts_transactions.datetime" },
            { data: "dm_cash_accounts_transactions.name" },
            { data: "dm_cash_accounts_transactions.amount" },
            { data: null, // SignedAmount
                        render: function(data, type, row) {
                            if (row.dm_cash_accounts_transactions.type === 'CREDIT') {
                            return row.dm_cash_accounts_transactions.amount; }  
                            else {
                            return 0 - row.dm_cash_accounts_transactions.amount; }                                  
                            }                           
            },
            { data: null, // PaidIn
                        render: function(data, type, row) {
                            if (row.dm_cash_accounts_transactions.type === 'CREDIT') {
                            return row.dm_cash_accounts_transactions.amount; }  
                            else {
                            return ''; }                                    
                            }                           
            },
            { data: null, // PaidOut
                        render: function(data, type, row) {
                            if (row.dm_cash_accounts_transactions.type === 'DEBIT') {
                            return row.dm_cash_accounts_transactions.amount; }  
                            else {
                            return ''; }                                    
                            }                           
            },
            { data: "dm_cash_accounts.code" },
...

Footer callback code extract below

        "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 Current Page/All Pages
                         DisplaySum(2);
                         DisplaySum(3);
                         DisplaySum(4);
            
                         function DisplaySum(indexval) 
                         {             
                                columnValuePage = api
                                    .column( indexval, { page: 'current'} )
                                    .data()
                                    .reduce( function (a, b) {
                                        return intVal(a) + intVal(b);
                                    }, 0 );                         
                                columnValueAll = api
                                    .column( indexval, { search: 'applied' } )
                                    .data()
                                    .reduce( function (a, b) {
                                        return intVal(a) + intVal(b);
                                    }, 0 );                         
                                // Update footer
                                $( api.column( indexval ).footer() ).html(
                                    '£' + intVal(columnValuePage).toFixed(0)+ ' / ' + '£' + intVal(columnValueAll).toFixed(0)
                                );
                        }               
                        
        } // "footerCallback"      

I have attached a partial screen shot of the resulting report, showing amount is totalling but other columns SignedAmount, PaidIn are evaluating to zero.

Any help much appreciated.

Thanks

Colin

kthorngren

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

You will need to debug the specific data in your columns. Please update my test case with your data so we can help debug.

Kevin

cpshart

Hi Kevin

Thanks for your quick response, the columns are being summated where the values are sourced directly from the MySQL table

e.g.

            { data: "dm_cash_accounts_transactions.amount" },

but where the column is rendered to a different value depending upon the value of another column i.e. transaction type DEBIT or CREDIT, the totals are evaluated to zero, but each row displays correctly in the datatable.

e.g. the next 3 columns shown above, 1st one SignedAmount shown below

            { data: null, // SignedAmount
                        render: function(data, type, row) {
                            if (row.dm_cash_accounts_transactions.type === 'CREDIT') {
                            return row.dm_cash_accounts_transactions.amount; } 
                            else {
                            return 0 - row.dm_cash_accounts_transactions.amount; }                                 
                            }                          
            },

I have tried and failed to integrate my data to your example, as I am using javascript libraries installed on the webserver for the datatables editor in my file.

Many Thanks

Colin

colin

Hi @cpshart ,

You can ignore the ajax bit of your test, and just put the data into a variable which can be passed to DataTables like this example here (though note this is array, and not an object). This should still demonstrate your problem, as the problem is with the rendering, not the data source.

Hope that helps to get a demo going.

Cheers,

Colin

cpshart

Hi Kevin

This may give me the answers I need, still investigating ..

http://live.datatables.net/qoxadado/1/edit

Thanks again Colin

cpshart

Hi Kevin

I have added a function to display page and document totals for rendered columns using http://live.datatables.net/qoxadado/1/edit

as follows

                         function DisplayRenderedSum(indexval) 
                         {   
                                columnValuePage = api
                                    .cells( null, indexval, { page: 'current' } )
                                    .render('display')
                                    .reduce( function (a, b) {
                                        return intVal(a) + intVal(b);
                                    }, 0 );
                                columnValueAll = api
                                    .cells( null, indexval, { search: 'applied' } )
                                    .render('display')
                                    .reduce( function (a, b) {
                                        return intVal(a) + intVal(b);
                                    }, 0 );
                                // Update footer
                                $( api.column( indexval ).footer() ).html(
                                    '£' + intVal(columnValuePage).toFixed(0)+ ' / ' + '£' + intVal(columnValueAll).toFixed(0)
                                );
                        }               

all working now

Thanks Colin

kthorngren

Great, I was just about to work up an example with cells().render(). Good job figuring it out!

Kevin