Sum time in grid fields

From pmusers
Jump to: navigation, search
  1. Sum time in grid fields

This JavaScript example shows how to sum time entered into grid fields. It adds together all the time fields in a row and then inserts the row's sum in a total field. Then, it sums the total column for a grand total that it inserts in the summary field at the bottom of the grid. In order to sum the time, it converts time strings in the format "23:59:59" into seconds, then it converts seconds back into time strings to display in the total field.


Dynaform: Sum time in grid.json (right click and select Save link as)
Author: Amos Batto (amos@processmaker.com)
Version: 1.0 (2018-09-04)
Tested in: ProcessMaker 3.2.3 Community in Debian 9.5 with Firefox 52.9.0


Create the following Dynaform with a grid which has 7 fields for each day of the week and a total field with the ID "weekTotal":

gridWithTimeFieldsDesigner.png

In each day's field, set the validate property to ^(\d{0,2}:)?[0-5]?\d(:[0-5]\d)?$ to only accept times in the format of 59, 59:59 or 23:59:59. Also set the validation error message to Need time 23:59:59 to let the user know the proper time format to use.

setValidateForTime.png

Then, add the following JavaScript to the Dynaform:

var gridId           = "timeList";            //set to the ID of the grid
var gridTotalFieldId = "weekTotal";           //set to the ID of the grid's total field
var gridTotalColNo   = 8;                     //set to the column number of the grid's total field
var aGridFieldsToSum = [1, 2, 3, 4, 5, 6, 7]; //set to the column numbers of the grid fields to sum

var formId = $("form").prop("id");
var oGrid = $("#"+gridId);
var reGridField = new RegExp("^\\["+gridId+"\\]\\[(\\d+)\\]\\[");

//Get number of seconds from string in the "23:59:59" format
function getSeconds(sTime) {
  sTime += ""; //make sure it is a string
  if (sTime.trim() == '') {
    return 0;
  }
  
  var found = sTime.match(/^(\d{0,2}:)?[0-5]?\d(:[0-5]\d)?$/);
  
  if (found == null) {
    return NaN;
  }
  
  var aParts = sTime.split(":").reverse();
  var seconds = 0;
  
  for (var i=0; i < aParts.length; i++) {
    seconds += parseInt(aParts[i]) * Math.pow(60, i);
  }
  
  return seconds; 
}  

//convert seconds to a time string in the format "23:59:59":
function secondsToTime(seconds) {
  var h = Math.floor(seconds / 3600);
  var m = Math.floor(seconds % 3600 / 60);
  var s = Math.floor(seconds % 3600 % 60);
  var time = '';
  time += h ? h+":" : '';
  time += (m ? ( m < 10 ? "0"+m : m) : "00") + ":";
  time += s < 10 ? "0"+s : s;
  return time;
}

function sumRow(rowNo) {
  var weekSum = 0;
    
  for (i in aGridFieldsToSum) {
    var time = oGrid.getValue(rowNo, aGridFieldsToSum[i]);
    weekSum += getSeconds(time);
  }  
    
  oGrid.setValue(secondsToTime(weekSum), rowNo, gridTotalColNo);
  return weekSum;
}

//sum grid's total column:
function sumTotalColumn() {
  var nRows = oGrid.getNumberRows();
  var runningTotal = 0;
    
  for (i = 1; i <= nRows; i++) {
    runningTotal += getSeconds( oGrid.getValue(i, gridTotalColNo) );
  }
    
  var grandTotal = secondsToTime(runningTotal);
  $("#sum-"+gridId+"-"+gridTotalFieldId).val(grandTotal);
}

//when value inside grid is changed
$("#"+formId).setOnchange(function (fieldId, newVal, oldVal) {
  var aMatches = fieldId.match(reGridField);
  
  if (aMatches) {
    sumRow( aMatches[1] );
  }
  
  sumTotalColumn();
});

//sum again when row deleted from grid:
oGrid.onDeleteRow(function(oGrid, aRow, rowIndex) {
  sumTotalColumn();
});


//sum time in grid when form loads:
var nRows = oGrid.getNumberRows();
    
for (i = 1; i <= nRows; i++) {
  sumRow(i);
}

sumTotalColumn();

When the user fills in the grid with times, the times in each row will be summed and inserted in the "weekTotal" field, then the "weekTotal" column will be summed and insert in its summary field below the grid.

filledTimeInGrid.png

If the user enters an invalid time in one of the fields, a red error message will appear below the field to inform the user and the total will be calculated as 00:NaN (where NaN means "Not a Number").

ErrorInTimeFormat.png