Calculate Interval Between Dates With Advanced Grid

Introduction

This page will guide you through creating an application using Advanced Grid to compute the time interval between two dates. While our example focuses on hours and minutes, you can easily customize it to suit your requirements. You refer to this interval as effort.

How does it work?

For this example, you will have the following forms and grids:

• Grid Form Design
  

• Request Form Design
  

Insert a Custom HTML with the following script:

<script>
    $(function() {
        var grid = FormUtil.getField("advgrid"); // Field id of advance grid

        $(grid).on("change", function() {
            var start = getGridCellValues("advgrid.start");
            var end = getGridCellValues("advgrid.end");
            var totalHrs = 0;
            var totalMins = 0;

            for (var i = 0; i < start.length; i++) {

                if (!start[i] || start[i] == "" || !end[i] || end[i] == "") continue;
                var startDate = new Date(start[i]);
                var endDate = new Date(end[i]);
                var diffMs = (endDate - startDate);
                var diffHrs = Math.floor(diffMs / 3600000); // Hours
                var diffMins = Math.round((diffMs % 3600000) / 60000); // Minutes
                var totalDuration = diffHrs + ":" + diffMins;
                totalHrs = totalHrs + diffHrs;
                totalMins = totalMins + diffMins;
                var total = totalHrs + ":" + totalMins;
                

                setValue(grid, i, 3, totalDuration);
                setValue(grid, i, 4, total);

            }
        });

        function setValue(grid, rowIndx, colIndx, colValue) {
            var DM = $(grid).find(".pq_grid").pqGrid("option", "dataModel");
            var data = DM.data;
            var json = data[rowIndx][data[rowIndx].length - 1];
            var obj = eval("[" + json + "]");

            var colKey = DM.columnKey[colIndx];

            obj[0][colKey] = colValue;

            //Update data
            json = JSON.encode(obj);
            json = json.substring(1, json.length - 1);
            DM.data[rowIndx][colIndx + 1] = colValue;
            DM.data[rowIndx][data[rowIndx].length - 1] = json;

            //Update hidden table
            var indexKey = DM.data[rowIndx][0];
            var tbl = $(grid).find('.table_container table');
            tbl.find("tr.key_" + indexKey).find("[column_key=" + colKey + "]").text(colValue);
            tbl.find("tr.key_" + indexKey).find("textarea").val(json);

            //Update table cell
            var colCell = $(grid).find("tr[pq-row-indx=" + rowIndx + "] .pq-grid-cell[pq-col-indx=" + (colIndx + 1) + "]");
            $(colCell).find(".pq-td-div").html('<div class="form-cell-value"><span>' + colValue + '</span></div>');
        }
    }); 
    
    // FormUtil.getGridCellValues but with a few adjustments
    function getGridCellValues(cellFieldId) {
        var fieldId = cellFieldId.split(".")[0];
        var values = new Array();
        
        var field = FormUtil.getField(fieldId);
        
        var gridDataObject = field.data("gridDataObject");
        if (gridDataObject !== null && gridDataObject !== undefined) {
            var cellId = cellFieldId.split(".")[1];
            
            for (var i in gridDataObject) {
                var value = gridDataObject[i][cellId];
                values.push(value);
            }
        } else {
            field.find("tr.grid-row").each(function() {
                if ($(this).find("textarea[id$=_jsonrow]").length > 0) {
                    var cellId = cellFieldId.split(".")[1];

                    //get json data from hidden textarea
                    var data = $(this).find("textarea[id$=_jsonrow]").val();
                    var dataObj = $.parseJSON(data);

                    if (dataObj[cellId] !== undefined) {
                        values.push(dataObj[cellId]);
                    } 
                    // added else condition
                    else {
                        values.push("");
                    }
                } else {
                    var cellId = cellFieldId.replace(/\./g, '_');
                    var cell = $(this).find("[name=" + cellId + "], [name$=_" + cellId + "]");
                    //removed if condition
                    //if (cell.length > 0) {
                        values.push(cell.text());
                    //}
                }
            });
        }
        
        return values;
    }
</script>

Once you add the script, you will get the following result:

Download sample app