jqgrid calculate total column cells amount depending on other column cell values

Question

After using jqgrid's setFooterData function to calculate the total amount like in this question , this is my grid and functions:

<script type="text/javascript">

    function calculateTotal(grid_ , column_id_)
    {
        var _total_amount = 0;
        var i = getColumnIndexByName(grid_ , column_id_); 

        // TO ADD - calculate only if row "status" cell = "1:Confirmed"
        $("tbody > tr.jqgrow > td:nth-child("+(i+1)+")" , grid_[0]).each(function()
        {
            _total_amount += Number(getTextFromCell(this));
        });

        return _total_amount;
    }

    function getColumnIndexByName(grid_ , column_id_)
    {
        var cm = grid_.jqGrid('getGridParam','colModel');
        for (var i=0,l=cm.length; i<l; i++)
        {
            if (cm[i].name===column_id_)
            {
                return i; // return the index
            }
        }
        return -1;
    }

    function getTextFromCell(cellNode)
    {
        return cellNode.childNodes[0].nodeName === "INPUT"?
                  cellNode.childNodes[0].value:
                  cellNode.textContent || cellNode.innerText;
    }

    $(document).ready(function () {

        var grid = $("#list"),lastSel;

        grid.jqGrid({
            url:'url',
            datatype: "xml",
            loadonce:true ,
            async: false,
            colNames: ['Inv No', 'Name' , 'Amount' , 'Status'],
            colModel: [
                { name: 'id', index: 'id', width: 65, sorttype: 'int', hidden: true },
                { name: 'name', index: 'name', editable: true, width: 90, sortable: false },
                { name: 'amount', index: 'amount', editable: true, width: 70, formatter: 'number', align: 'right', sortable: false },
                {name:'status',index:'status', width:90, sorttype:"int" , editable:true,
                  edittype:"select", formatter:'select',
                  editoptions:
                  {
                     value:"1:Confirmed ;2:Open ; 3:Rejected" ,
                     dataInit: function(elem)
                     {
                        $(elem).width(90);
                     }
                  }
                },
            ],
            rowNum: 1000,
            pager: '#pager',
            viewrecords: true,
            sortorder: "desc",                
            height: "100%",                
            footerrow:true,
            xmlReader: {
                                          root:"rows",
                                          row:"row",
                                          repeatitems:false
                                       },
            shrinkToFit: false,
            beforeSelectRow: function(row_id_, e)
            {
            },
            onSelectRow: function(id)
            {
                 grid.jqGrid('saveRow' , lastSel , false, 'clientArray');
                 grid.editRow(id , false);
                 lastSel=id;
            },
            loadComplete:function()
            {
                grid.jqGrid('footerData' , 'set' , {name:'TOTAL:' , amount: calculateTotal(grid , 'amount')});
            }
        });            
    });    
</script>

My question Is how can I calculate the total amount sum depending on the value that is iniside the "status" combobox. I want to sum the amount only if the value iniside the "status" cell is equels to "Confirmed" (=1).

How can this be done?

Thank's.

Answer 1

I made new demo where I used another way to enumerate the cells in the grid (see the answer).

In the demo I modified the code of getTextFromCell and calculateTotal functions to the following:

var getTextFromCell = function(cellNode) {
        if (cellNode.childNodes[0].nodeName.toUpperCase() === "SELECT") {
            var selOptions = $("option:selected", cellNode);
            if (selOptions.length>0) {
                return selOptions.text();
            }
        }
        return cellNode.childNodes[0].nodeName.toUpperCase() === "INPUT"?
               cellNode.childNodes[0].value:
               cellNode.textContent || cellNode.innerText;
    },
    calculateTotal = function() {
        var totalAmount = 0,
            iAmount=getColumnIndexByName(grid,'amount'),
            iStatus=getColumnIndexByName(grid,'status');
        var i=0, rows = grid[0].rows, rowsCount = rows.length, row, status;

        for(;i<rowsCount;i++) {
            row = rows[i];
            if (row.className.indexOf('jqgrow') !== -1) {
                status = getTextFromCell(row.cells[iStatus]);
                if (status === "Confirmed") {
                    totalAmount += Number(getTextFromCell(row.cells[iAmount]));
                }
            }
        }

        grid.jqGrid('footerData','set',{name:'TOTAL Confirmed',amount:totalAmount});
    };

Now in the total lines will be displayed the sum of all values from the 'Amount' columns, but only the rows having "Confirmed" in the 'Status' column will be taken in the consideration.