Aggregate data for different categories

[vasikgreifParticipant]

I have following function to display column total outside of the grid:

function get_total() {
        var summaryData = $("#jqxgrid").jqxGrid('getcolumnaggregateddata', 'budget', [{
            'Total': function (aggregatedValue, currentValue) {
                return parseInt(aggregatedValue) + currentValue;
            }
        }]);
        $('.total').text(summaryData['Total']);
    };

This works fine. Now, in othe column, category, I have dropdown with different categories. For each of the selected categories, I would like to get total of the budget column. How can that be done?

Thanks

[Peter StoevKeymaster]

Hi vasikgreif,

I suppose that you’ll have to update your get_total function and pass an additional Category parameter. Depending on that parameter, your custom aggregation function will either return parseInt(aggregatedValue) or parseInt(aggregatedValue) + currentValue;

Best Regards,
Peter Stoev

jQWidgets Team
http://www.jqwidgets.com

[vasikgreifParticipant]

Could you please explain more, from where can I pass the parameter? I’m calling the function on update_row and init_editor now, please see my code bellow (shortened to make it easier to read):

    $(document).ready(function () {
        var data = {};
        var theme = 'classic';
        var dropDownListSource =
        {
            datatype: "json",
            datafields: [
                { name: 'name' },
                { name: 'term_id' }
            ],
            id: 'id',
            url: Ajax.ajaxurl,
            mtype: 'POST',
            data: {
                action: 'get_taxonomy_terms'
            }
        };

        var dropdownListAdapter = new $.jqx.dataAdapter(dropDownListSource, { autoBind: true, async: false });

        var source =
        {
            datatype: "json",
            datafields: [
                { name: 'id' },
                { name: 'title' },
                { name: 'budget',type: 'number' },
                { name: 'supplier_estimate', type: 'number' },
                { name: 'spent',type: 'number' },
                { name: 'note' },
                { name: 'nonce' },
                { name: 'category', value: 'term_id', values: { source: dropdownListAdapter.records, value: 'term_id', name: 'name' }},
                { name: 'term_id'},
            ],
            id: 'id',
            url: Ajax.ajaxurl,
            mtype: 'POST',
            data: {
                action: 'get_table_data',
                post_type: 'budget'
            },
            sortcolumn: 'id',
            sortdirection: 'asc',

            addrow: function (rowid, rowdata, position, commit) {
                commit(true);
            },
            updaterow: function (rowid, rowdata, commit) {
                // I'm calling the get_total function here
                get_total();
            }
        };

        // initialize jqxGrid
        $("#jqxgrid").jqxGrid(
            {
                width: 1200,
                height: 350,
                selectionmode: 'singlerow',
                source: source,
                ready: function() {
                  // I'm calling the get_total function also here  
                  get_total();
                },
                columns:
                    [
                        { text: 'ID', datafield: 'id', width: 100, hidden: true },
                        { text: Ajax.category, columntype: 'dropdownlist', datafield: 'term_id', displayfield: 'category', width: 100,
                            createeditor: function (row, value, editor) {
                                editor.jqxDropDownList({ source: dropdownListAdapter , displayMember: 'name', valueMember: 'term_id' });
                            }
                        },
                        { text: Ajax.budget, datafield: 'budget', width: 100, cellsalign: 'right'  }
                    ]
            });
    });

Basically, what I need is get the sum of budget column for each term_id. Could you point me to the right direction to do this?

[vasikgreifParticipant]

I found I can get column and record valued inside the function, so I solved this as following:

        var total = [];
        var total_budget = $("#jqxgrid").jqxGrid('getcolumnaggregateddata', 'budget', [{
            'sum': function (aggregatedValue, currentValue, column, record) {
                // on update, term_id is string, on init it is Array
                if (record.term_id instanceof Array) {
                    var cat_id = record.term_id[0];
                }
                else var cat_id = record.term_id;

                // if there's not a category with this id in total array yet, add a new object for it
                if (typeof(total[cat_id]) == 'undefined') {
                    total[cat_id] = {};
                    total[cat_id].name = record.category;
                    total[cat_id].budget = 0;
                    total[cat_id].spent = 0;
                    total[cat_id].suppl_est = 0;

                }

                // all these checks are necesarry because the records are sent in different formats on init and update
                if (typeof(record.budget) != 'undefined') {
                    if (typeof(record.budget[0]) != 'undefined') {
                        var budget_val = parseInt(record.budget[0]) || 0;
                        total[cat_id].budget = total[cat_id].budget + budget_val;
                    }
                    else {
                        var budget_val = parseInt(record.budget) || 0;
                        total[cat_id].budget = total[cat_id].budget + budget_val;
                    }
                }

                if (typeof(record.spent) != 'undefined') {

                    if (typeof(record.spent[0]) != 'undefined') {
                        var spent_val = parseInt(record.spent[0]) || 0;
                        total[cat_id].spent= total[cat_id].spent + spent_val;
                    }
                    else {
                        var spent_val = parseInt(record.spent) || 0;
                        total[cat_id].spent = total[cat_id].spent + spent_val;
                    }
                }

                if (typeof(record.supplier_estimate) != 'undefined') {
                    if (typeof(record.supplier_estimate[0]) != 'undefined') {
                        var suppl_val = parseInt(record.supplier_estimate[0]) || 0;
                        total[cat_id].suppl_est = total[cat_id].suppl_est + suppl_val;
                    }
                    else {
                        var suppl_val = parseInt(record.supplier_estimate) || 0;
                        total[cat_id].suppl_est = total[cat_id].suppl_est + suppl_val;
                    }
                }
                return parseInt(aggregatedValue) + currentValue;
            }
        }]);
        // $('.total .total_sum') is my table
        $('.total .total_sum').html('');

        // for each category, add row with correct totals
        total.forEach(function(entry) {
            $('.total .total_sum').append('<tr><td>'+entry.name+'</td><td>'+entry.budget+'</td><td>'+parseInt(entry.suppl_est)+'</td><td>'+entry.spent+'</td></tr>');
        });
        

There might be better way to do this, but I hope this helps someone…

[Author]

Posts