Ensure resource time entry matches booking planning and project worked hours
This script ensures that resources are not logging time after the task assignment related booking end date, or exceeding assigned planned hours.
-
Keep worked time in-sync with planned allocation
-
Stay within planned budget
-
Works on mobile devices (iPhone/Android )
Follow the steps below or download the solutions file, see Creating Solutions for details.
Setup
-
Create a new Timesheet [Edit] form script deployment.
-
Enter a Filename and click SAVE. The extension ‘.js’ is automatically appended if not supplied.
-
Click on the script link to launch the Scripting Studio.
-
(1) Copy the Program Listing below into the editor, (2) set the Before approval event, and set verify_timeentry_policy as the Entrance Function.
Program Listing
function verify_timeentry_policy(type) {
var timesheet = NSOA.form.getOldRecord();
// Only check on approval request and if current user is the timesheet owner
if (type != 'approve_request' || timesheet.userid != NSOA.wsapi.whoami().id)
return;
// Load task data
var taskFilter = new NSOA.record.oaTask();
taskFilter.timesheetid = timesheet.id;
// disable current user's filter for this script
NSOA.wsapi.disableFilterSet(true);
// Analyze tasks to load related records
var task_readRequest = {
type: "Task",
fields: "id, date, projecttaskid, decimal_hours",
method: "equal to",
objects: [taskFilter],
attributes: [{
name: "limit",
value: "1000"
}, {
name: "filter",
value: "current-user"
}]
};
var task_arrayOfreadResult = NSOA.wsapi.read(task_readRequest);
var tasks_by_uniqueKey = {};
var ts_pta_worked_hours = {};
var ptaFilters = {};
var bookingFilters = {};
if (!task_arrayOfreadResult || !task_arrayOfreadResult[0])
NSOA.form.error('', "Internal error loading %task% details.");
else if (task_arrayOfreadResult[0].errors === null && task_arrayOfreadResult[0].objects)
task_arrayOfreadResult[0].objects.forEach(function(task) {
NSOA.meta.log('debug', "Got task: " + JSON.stringify(task));
// Only consider project task assignments
if (!task.projecttaskid)
return;
// Correlate booking <=> project_task_assignment via task tuple(project_task_id,user_id)
var uniqueKey = task.projecttaskid;
// Store information about this time entry
if (tasks_by_uniqueKey[uniqueKey])
tasks_by_uniqueKey[uniqueKey].push(task);
else
tasks_by_uniqueKey[uniqueKey] = [task];
ts_pta_worked_hours[uniqueKey] += parseFloat(task.decimal_hours);
// Prepare related booking filters
var bookingFilter = new NSOA.record.oaBooking();
bookingFilter.project_taskid = task.projecttaskid;
bookingFilters[uniqueKey] = bookingFilter; // elimiate duplicates
// Prepare related project_task_assign filters
var ptaFilter = new NSOA.record.oaProjecttaskassign();
ptaFilter.projecttaskid = task.projecttaskid;
ptaFilters[uniqueKey] = ptaFilter; // elimiate duplicates
});
else
return; // assume no data found
// Now load and analyze project task assignments (one read request)
if (Object.keys(ptaFilters).length > 0) {
var equalTo = [];
for (var i = 0; i < Object.keys(ptaFilters).length; i++)
equalTo.push("equal to");
var ptaFilter = [];
Object.keys(ptaFilters).forEach(function(k) {
ptaFilter.push(ptaFilters[k]);
});
var pta_readRequest = {
type: "Projecttaskassign",
fields: "id, planned_hours, userid, projecttaskid",
method: equalTo.join(', or '),
objects: ptaFilter,
attributes: [{
name: "limit",
value: "1000"
}, {
name: "filter",
value: "current-user"
}]
};
NSOA.meta.log('debug', "pta_readRequest=" + JSON.stringify(pta_readRequest));
var pta_arrayOfreadResult = NSOA.wsapi.read(pta_readRequest);
NSOA.meta.log('debug', "pta_arrayOfreadResult=" + JSON.stringify(pta_arrayOfreadResult));
var pta_planned_hours = {};
var pta_worked_hours = {};
if (!pta_arrayOfreadResult || !pta_arrayOfreadResult[0])
NSOA.form.error('', "Internal error loading %project_task% assignment details.");
else if (pta_arrayOfreadResult[0].errors === null && pta_arrayOfreadResult[0].objects)
pta_arrayOfreadResult[0].objects.forEach(function(pta) {
var uniqueKey = pta.projecttaskid;
var planned_hours = parseFloat(pta.planned_hours);
// Skip assignment if no planned hours
if (!planned_hours)
return;
// Compute worked hours for current user's assignment
var taskFilter = new NSOA.record.oaTask();
taskFilter.projecttaskid = pta.projecttaskid;
taskFilter.userid = pta.userid;
var task_readRequest = {
type: "Task",
fields: "id, decimal_hours",
method: "equal to",
objects: [taskFilter],
attributes: [{
name: "limit",
value: "1000"
}, {
name: "filter",
value: "current-user"
}]
};
var task_arrayOfreadResult = NSOA.wsapi.read(task_readRequest);
var worked_hours = 0;
if (!task_arrayOfreadResult || !task_arrayOfreadResult[0])
NSOA.form.error('', "Internal error loading %timeentry% assignment details.");
else if (task_arrayOfreadResult[0].errors === null && task_arrayOfreadResult[0].objects)
task_arrayOfreadResult[0].objects.forEach(function(task) {
worked_hours += parseFloat(task.decimal_hours);
});
// Verify user's worked hours haven't exceeded as a result of this timesheet
NSOA.meta.log('debug', "worked=" + worked_hours + ",planned=" + planned_hours);
if (worked_hours && worked_hours > planned_hours) {
var pt = NSOA.record.oaProjecttask(pta.projecttaskid);
var error = "Worked %hours% (" + worked_hours + ") including %timeentrys% on this %timesheet% exceeds your planned %hours% (" + planned_hours + ") for %project% '" + NSOA.record.oaProject(pt.projectid).name + "' %project_task% '" + pt.name + "'.";
if (ts_pta_worked_hours[uniqueKey]) {
error += "This %timesheet% adds " + ts_pta_worked_hours[uniqueKey] + " %hours%.";
var worked_excluding_ts = worked_hours - ts_pta_worked_hours[uniqueKey];
if (worked_excluding_ts <= planned_hours)
error += "Please reduce your worked %hours% by " + (worked_hours - planned_hours) + ".";
}
NSOA.form.error('', error);
}
});
}
// Now load and analyze bookings
var df = require('lib_date_format');
if (Object.keys(bookingFilters).length > 0) {
var equalTo = [];
for (var i = 0; i < Object.keys(bookingFilters).length; i++)
equalTo.push("equal to");
var bookingFilter = [];
Object.keys(bookingFilters).forEach(function(k) {
bookingFilter.push(bookingFilters[k]);
});
var booking_readRequest = {
type: "Booking",
fields: "id, enddate, userid, project_taskid, projectid",
method: equalTo.join(', or '),
objects: bookingFilter,
attributes: [{
name: "limit",
value: "1000"
}, {
name: "filter",
value: "current-user"
}]
};
NSOA.meta.log('debug', "booking_readRequest=" + JSON.stringify(booking_readRequest));
var booking_arrayOfreadResult = NSOA.wsapi.read(booking_readRequest);
NSOA.meta.log('debug', "booking_arrayOfreadResult=" + JSON.stringify(booking_arrayOfreadResult));
if (!booking_arrayOfreadResult || !booking_arrayOfreadResult[0])
NSOA.form.error('', "Internal error loading %project_task% assignment details.");
else if (booking_arrayOfreadResult[0].errors === null && booking_arrayOfreadResult[0].objects)
booking_arrayOfreadResult[0].objects.forEach(function(booking) {
var uniqueKey = booking.project_taskid;
NSOA.meta.log('debug', uniqueKey + "," + JSON.stringify(tasks_by_uniqueKey));
var tasks = tasks_by_uniqueKey[uniqueKey];
if (!tasks)
return;
tasks.forEach(function(task) {
var taskDate = new Date(task.date.substr(0, 10));
taskDate.setDate(taskDate.getDate() + 1);
NSOA.meta.log('debug', JSON.stringify(task));
var bookingDate = new Date(booking.enddate.substr(0, 10));
NSOA.meta.log('debug', "Check: " + taskDate + '>' + bookingDate);
if (taskDate && bookingDate) {
if (taskDate > bookingDate) {
var pt = NSOA.record.oaProjecttask(booking.project_taskid);
NSOA.form.error('', "Task on date " + df.userDateFormat(taskDate) + " exceeds booking end date " + df.userDateFormat(bookingDate) + " for for %project% '" + NSOA.record.oaProject(pt.projectid).name + "' %project_task% '" + pt.name + "'.");
return;
}
}
});
});
}
}