jQuery Grid Plugin - jqGrid - Topic: jqGrid: How do I sum the values in columns and use result http://www.trirand.com/blog/?page_id=393/help/jqgrid-how-do-i-sum-the-values-in-columns-and-use-result Simple:Press Version 5.7.5.3 dux on jqGrid: How do I sum the values in columns and use result http://www.trirand.com/blog/?page_id=393/help/jqgrid-how-do-i-sum-the-values-in-columns-and-use-result#p21204 Help http://www.trirand.com/blog/?page_id=393/help/jqgrid-how-do-i-sum-the-values-in-columns-and-use-result#p21204 Thank you, chillifire, great job

 

]]> Tue, 07 Dec 2010 16:53:02 +0200 chillifire on jqGrid: How do I sum the values in columns and use result http://www.trirand.com/blog/?page_id=393/help/jqgrid-how-do-i-sum-the-values-in-columns-and-use-result#p21161 Help http://www.trirand.com/blog/?page_id=393/help/jqgrid-how-do-i-sum-the-values-in-columns-and-use-result#p21161 OK, here is a project solution – works for me. May need some more REGEX magic for theNOT type operators, and you need to add a numerical comparison in parallel to the regex for lt, le, gt, and ge, plus an array comparison for in, ni. Maybe there is a better way of doing the filter comparison , so please if anyone else is interested, they can add to this solution. It will certainly work fine with a searchToolbar, with the standard bw operator and more common character based comparisons. It also takes care of case sensitivity using the "i" option of the RegEx object.

Any improvements welcome, although performance is good, testing on 10.000 records the sum builds as fast as the underlying grid – no decernable delays.

Here goes. Relevant parts in bold/italic:

jQuery("#listSales").jqGrid({
        url:'../json/sales?horizon=1',
        datatype: "json",
        height: 450,
        width: 980,
        colNames:['ID', 'Sub-Account', 'Account', 'Partner', 'Admin', 'Transaction ID', 'Transaction date', 'Name', 'Email', 'Amount', 'Cur', 'Vocher', 'Received', 'Mobile', 'Package Id', 'Package Name', 'Expiration', 'User', 'User Mac', 'NasID', 'Router MAC'],
        colModel:[
            {name:'rowID',hidden: true},
            {name:'account',index:'account', width:60},
            {name:'affiliate',index:'affiliate', width:60},
            {name:'partner',hidden: true},
            {name:'admin',hidden: true},
            {name:'transid',index:'transid', width:110},
            {name:'transdate',index:'transdate', width:90},
            {name:'name',index:'name', width:100},
            {name:'email',index:'email', width:150},
            {name:'amountpaid',index:'amountpaid', width:40, sorttype:'int', formatter: 'number'},
            {name:'currency',index:'currency', width:25},
            {name:'voucher',index:'voucher', width:10,formatter:'checkbox'},
            {name:'youoweus',index:'youoweus', width:10,formatter:'checkbox'},
            {name:'mobile',hidden: true},
            {name:'timeid',hidden: true},
            {name:'package',hidden: true},
            {name:'expiration',hidden: true},
            {name:'userid',hidden: true},
            {name:'mac',hidden: true},
            {name:'nasid',hidden: true},
            {name:'routermac',hidden: true},
        ],
        rowNum:20,
        loadonce:true,
        scroll: 1,
        mtype: "GET",
        rownumbers: true,
        rownumWidth: 30,
        gridview: true,
        pager: '#pager',
        sortname: 'transdate',
        ignoreCase: true,
        loadui: "block",
        viewrecords: true,
        sortorder: "desc",
        caption: "<?= $this->contentHeader ?>",
        footerrow : true,
        userDataOnFooter : true,
        altRows : true,
        loadComplete: function() {
            var footerData = new sumAmountPaid();
            var footerRow = jQuery("#listSales").footerData('set',footerData, false);
        }
    });
    jQuery("#listSales").jqGrid('filterToolbar',{stringResult:true,searchOnEnter:false,defaultSearch:"cn"});

function sumAmountPaid() {
        // read grid data
        var thedata = jQuery("#listSales").getGridParam('data');
        // read filters
        var postData = jQuery("#listSales").getPostData();
        // convert POST filters into usuable REGEX format – but only if there is a search
        if(postData._search) {
            var filterObj = jQuery.parseJSON(postData.filters);
            // extract group Operator
            var groupOp = filterObj.groupOp;
            //construct REGEX filter array
            var filterArray = new Array();
            jQuery.each(filterObj.rules,function(theKey,theValue) {
                // the "i" is required for the jqGrid setting ignoreCase: true,
                // remove it for and ignoreCase: false type jqGrids
                switch (theValue.op) {
                    case 'cn':
                        filterArray[theValue.field] = new RegExp(theValue.data, "i");
                        break;
                    case 'bw':
                        filterArray[theValue.field] = new RegExp('^' + theValue.data, "i");
                        break;
                    case 'ew':
                        filterArray[theValue.field] = new RegExp(theValue.data + '$', "i");
                        break;
                    case 'eq':
                        filterArray[theValue.field] = new RegExp('^' + theValue.data + '$', "i");
                        break;
                    case 'nc':
                        filterArray[theValue.field] = new RegExp('^(?:(?!' + theValue.data +').)+$', "i");
                        break;
                    case 'bn':
                        filterArray[theValue.field] = new RegExp('^(?:(?!' + theValue.data +').)$', "i");
                        break;
                    case 'en':
                        filterArray[theValue.field] = new RegExp('(?:(?!' + theValue.data +').)$', "i");
                        break;
                    case 'ne':
                        filterArray[theValue.field] = new RegExp('^(?:(?!' + theValue.data +').)$', "i");
                        break;
                }
            })
        }
        // initialise values
        var thesum = 0;
        var thecurrency="n/a";
        // loop over grid data
        for(var i=0;i < thedata.length; i++) {
            // add vlaues if there is no search or if the search criteria meet the data
            if (!(postData._search) || matchFilter(thedata[i],filterArray,groupOp)) {
                //make sure data is value and not string – then add
                thesum += parseFloat(thedata[i].amountpaid);
                // the values I add are currencies, only add if it is the same currency, otherwise stop adding – simialr applies for anything with a ubit of measure (UOM)  and if the constance of the UOM caanot be guranteed)
                if (thecurrency == "n/a") {
                    thecurrency = thedata[i].currency;
                } else {
                    // give some standard values, if the UOM is not the same throughout
                    if (thecurrency != thedata[i].currency) {
                        this.name = 'Total:';
                        this.amountpaid = 'n/a';
                        this.currency = 'n/a';
                        return;
                    }
                }
            }
        }
        this.name = 'Total:';
        this.currency = thecurrency;
        // format and round the number
        if(thesum == 0) {
            this.amountpaid = '0.00';
        } else {
            this.amountpaid = roundNumber(thesum, 2);
        }
    }

    function roundNumber(rnum, rlength) { // Arguments: number to round, number of decimal places
        var newnumber = Math.round(rnum*Math.pow(10,rlength))/Math.pow(10,rlength);
        return newnumber;
    }
    function matchFilter(data, filterArray, groupOp) {
        var valid = true;
        // read filter array
        for (var theKey in filterArray) {
            //compare data field with filter
            if (data[theKey].search(filterArray[theKey]) != -1) {
                //if it is an OR, one match is enough
                if(groupOp == "OR") {
                    return true;
                }
                valid = true;
            } else {
                //if it is an AND, one fail is enough
                if(groupOp == "AND") {
                    return false;
                }
                valid = false;
            }
        }
        return valid;
    }

]]> Mon, 06 Dec 2010 00:15:29 +0200 chillifire on jqGrid: How do I sum the values in columns and use result http://www.trirand.com/blog/?page_id=393/help/jqgrid-how-do-i-sum-the-values-in-columns-and-use-result#p21127 Help http://www.trirand.com/blog/?page_id=393/help/jqgrid-how-do-i-sum-the-values-in-columns-and-use-result#p21127 Hi,

I don't think what you are trying to do is contrary to how jqGrid works at all - at least as of 3.7. Responses like #2 are referring to a world where everything was referred back to the server sorting, pagination, totals. As of 3.7 jqGrid introduced the loadonce option and now it is reasonable to expect that it can perform all the actions for which it previously needed server communication locally - using jquery and javascript. In fact sorting and filtering on local data already works.

And advice like 'you should go back to the server for everything' are not very helpful. What if I did requested the total on a filtered set of rows, but the data on the server has already changed due to other user's activity. Then the totlas do not match my local dta naymore. Or I have to load all the data again, subverting the whole point of local data and 'loadonce'.

The missing bit for total is that you cannot easily get to all the filtered data. you can get to all them loaded data, and the filtered data rows  that is currently visible on the screen. As far as I can make out you cannot easily access the array of ALL filtered rows.

So what you have to do until the makers of jqGrid have provided this function in standard.

On a load complete event get all the data with .getGridParam('data'), then get the filters with .getGridParam('postData'), transcribe the filters into a RegEx and run them over all the data. If it fits save the record in a new array with filtered data, calculate your totals, push the totals into userData or directly into the footer with .footerData.

I am working on a custom solution for my specific requirements, buit am happy to post the code, once finished. In the meantime I hope the developers have mercy on us and provide this function in standard as requested here: /blog/?page_id=393/feature-request/totals-on-local-data-for-userdata/&value=local%20data&type=1&include=1&search=1&ret=all

]]> Fri, 03 Dec 2010 22:12:34 +0200 wakelt on jqGrid: How do I sum the values in columns and use result http://www.trirand.com/blog/?page_id=393/help/jqgrid-how-do-i-sum-the-values-in-columns-and-use-result#p21116 Help http://www.trirand.com/blog/?page_id=393/help/jqgrid-how-do-i-sum-the-values-in-columns-and-use-result#p21116 Perhaps my desire to have the client perform the summation is due to my lack of jqgrid understanding...Your help is much

appreciated !! Please address the following jqgrid questions:

1) If I do a row edit within jqGird, a POST to the server occurs with the contents of the row and several other operators.  Will jqGrid take the Server response (assume xml) and re-populate the row that was edited ??? If so, that would be great.

2) Is there a way to submit the entire form (all data rows) to the server ?? Some rows may have dependencie's on others ??

thanks again;

wk

]]> Fri, 03 Dec 2010 16:00:07 +0200 wakelt on jqGrid: How do I sum the values in columns and use result http://www.trirand.com/blog/?page_id=393/help/jqgrid-how-do-i-sum-the-values-in-columns-and-use-result#p21109 Help http://www.trirand.com/blog/?page_id=393/help/jqgrid-how-do-i-sum-the-values-in-columns-and-use-result#p21109

waiting said:You should do this on server (php/asp..), sum and construct data, not at front side.

I realize that the summation can happen on the server side, but..

it seems that if the data is entered on the client side, there is no reason not to sum it on the client side before handing it to the server. This would allow the golfer to see his summed score, without having to go to the server then back to the client with the result. Is it not technically possible to do this with jquery/jqgrid ?? thnx,wk

]]> Fri, 03 Dec 2010 14:44:20 +0200 waiting on jqGrid: How do I sum the values in columns and use result http://www.trirand.com/blog/?page_id=393/help/jqgrid-how-do-i-sum-the-values-in-columns-and-use-result#p21100 Help http://www.trirand.com/blog/?page_id=393/help/jqgrid-how-do-i-sum-the-values-in-columns-and-use-result#p21100 You should do this on server (php/asp..), sum and construct data, not at front side.

]]> Fri, 03 Dec 2010 08:32:10 +0200 wakelt on jqGrid: How do I sum the values in columns and use result http://www.trirand.com/blog/?page_id=393/help/jqgrid-how-do-i-sum-the-values-in-columns-and-use-result#p21097 Help http://www.trirand.com/blog/?page_id=393/help/jqgrid-how-do-i-sum-the-values-in-columns-and-use-result#p21097 I am doing a project for school and have decided to include jqGrid into the learning experience. I am trying to instrument a golf score card. The card has a header row. It then has 4 other rows, one row for each player in the match. I have 9 columns that are used for per hole scores. I'd like to sum these 9 columns and place the result in a 10th column. What's the best way to construct this 10th column ??

Also, how do I change the height of a row ?? My header row text is getting cutoff at the bottom.

Lastly, how do I set the default value for each row w/o having to make a call to the server ?? I'd like to init the score columns to a certain value w/o making a call.

thanks in advance for your help;
Walter

]]> Fri, 03 Dec 2010 07:39:57 +0200