Calculate duration based on schedule

[madeves]

I need to be able to calculate the difference between two date fields in days based on a schedule (business days).   E.g. If start date is 01/03/2014 and end date is 08/03/2014, I want this answer to be 5 days (as 2 days out of the 7 are weekend days).

There is a mention of this on the Wiki but it talks about packages.com and doesn't seem to work.

I have also seen 'GlideSchedule' referenced but not how to use it.

Has anyone got any thoughts please? Your help would be much appreciated.

[ccajohnson]

Before I begin formulating a solution, I need to know where you are running this calculation. Is this through a Client Script, or a Business Rule?

[madeves]

It would have to be a Catalog Client Script as the calculation would be done in a record producer.

I hope that you can think of an idea.

[ccajohnson]

Since it appears that you want to code this through a Catalog Client Script, the best way to do the calculations is through a GlideAjax call. We can do all of the heavy lifting calculations in a script include that can have the start and end date passed to it, which then returns the calculated number of days that you want.

Step 01. Create a script include:

Name: dateTimeUtilAjax

Client callable: true

Script:

gs.include('DurationCalculator');



var dateTimeUtilAjax = Class.create();

dateTimeUtilAjax.prototype = Object.extendsObject(AbstractAjaxProcessor, {



       getSchedDays: function() {

               var sDate = this.getParameter('sysparm_sdt');

               var eDate = this.getParameter('sysparm_edt');

               var schedID = this.getParameter('sysparm_sid');

               var sDStr = this._convertDate(sDate);

               var eDStr = this._convertDate(eDate);

               var dc = new DurationCalculator();

               var usr = new GlideRecord('sys_user');

               usr.get(gs.getUserID());

               dc.setSchedule(schedID, usr.time_zone);

               var busHrs = this._getHoursinDayfromSchedule(schedID);

               var durSec = dc.calcScheduleDuration(sDStr, eDStr);

               var durDay = durSec / (60 * 60 * busHrs);

               var retDay = durDay.toString().split('.')[0];

               return retDay;

       },



       _convertDate: function(dateStr) {

               dSplit = dateStr.split('/');

               return dSplit[2] + '-' + dSplit[1] + '-' + dSplit[0] + ' 00:00:00';

       },



       _getHoursinDayfromSchedule: function(scheduleID) {

               var durationHours = false;

               schedRec = new GlideRecord('cmn_schedule_span');

               schedRec.addQuery('schedule', scheduleID);

               schedRec.query();

               if (schedRec.next()) {

                       var sdt = schedRec.start_date_time.getDisplayValue();

                       var edt = schedRec.end_date_time.getDisplayValue();

                       var dc = new DurationCalculator();

                       durationHours = dc.calcScheduleDuration(sdt, edt) / (60 * 60);

               }

               return durationHours;

       }



});

Step 02. Create your Catalog Client Script:

I am guessing that you want a variable on the form to be filled out once the start and end dates have been entered. For sample purposes, I am using start_date, end_date, and bus_days as variables in the record producer.

Name: Fill bus_days onChange

Applies to: A Catalog Item

Type: onChange

Catalog item: ***WHATEVER YOUR ITEM IS***

Script:

function onChange(control, oldValue, newValue, isLoading, isTemplate) {

       if (isLoading || newValue == '') {

               return;

       }

       if (newValue != oldValue) {

               var startDate = g_form.getValue(start_date);

               var endDate = g_form.getValue(end_date);

               if (startDate == '' || endDate == '') {

                       alert("The Start and End date must be filled in first");

                       return;

               }

               var ga = new GlideAjax('dateTimeUtilAjax');

               ga.addParam('sysparm_name', 'getSchedDays');

               ga.addParam('sysparm_sdt', startDate);

               ga.addParam('sysparm_edt', endDate);

               ga.addParam('sysparm_sid', '08fcd0830a0a0b2600079f56b1adb9ae');

               ga.getXML(getDays);

       }

}



function getDays(response) {

       var answer = response.responseXML.documentElement.getAttribute("answer");

       g_form.setValue('bus_days', answer);

}

Let me know if you need further explanation, or encounter any difficulty.

[Davina]

Hi,

This is very interesting.... can you tell me is this something you have built and is working?

Is the Catalog Client Script on Change for the start date?

Looking at schedules i cant see the fields for start and end dates? Does this incorporate any holidays referenced within a schedule?

Cheers,