Email Notification due date Prior to 2 days excluding weekends

[kks]

Hi All,

Good evening to all.

I had a requirement to notify the problem manager prior to 2 days based on the due date. The below script of mine is working as expected.

My schedule job script below:

Script name : Problem Task Due date Reminder two days

queryPlannedDate();
function queryPlannedDate(){
var qrystr = "active=true^due_date>javascript:gs.daysAgoEnd(-1)^due_date<javascript:gs.daysAgoEnd(-2)"; 
var expdt = new GlideRecord('problem_task');

expdt.addEncodedQuery(qrystr);
expdt.query();

while(expdt.next()){

gs.eventQueue('problem_task.duedate1', expdt, expdt.assigned_to);
}

}

This script will execute on daily basis. It is working fine. Now the user tweaked the request and he wants exclude the week end(saturday and sunday). If the Problem task due is on Monday, The script should skip the saturday and sunday and fire on thursday( 2 days prior excluding weekends ). If the problem task due date is on Tuesday, This script will trigger on Friday.

Is there a way I can modify my script or any suggestions would be of great help because I have no clue on how to exclude weekends from the script.

Note : the above script is running based on al calendar days.

Thanks

Kamal

[kks]

Hi Aman,

I have tested the requirement. It is working fine. Thanks a lot you helped me on this requirement.

Thanks,

Kamal

[ARG645]

Your Welcome! Good to hear that its working.

[Amit Kumar6]

Hi Aman Gurram,

I have similar requirement on sc_request table. I want to trigger notifications 2 days prior to due date excluding weekends.

I tried your code :

var dur = new GlideDuration('-2 00:00:00');
var now = new GlideDateTime();

var sched = new GlideSchedule();
sched.load('090eecae0a0a0b260077e1dfa71da828');  // sys id of 8-5 weekdays excluding weekends schedule.

var required_dueDate = sched.add(now,dur);
queryPlannedDate();

function queryPlannedDate(){

var expdt = new GlideRecord('sc_request');
expdt.addQuery('u_request_type', 'Venue_Maps');
expdt.addQuery('active',true);
expdt.addQuery('due_date',">",required_dueDate.getDate());
expdt.query();
while(expdt.next()){

gs.print(expdt.number);

}
}

I am getting result :

REQ0022244 - 2019-08-06 14:47:00
REQ0033759 - 2019-08-02 13:16:23

ideally it should show te first record but it is showing the 2nd record as well.

Kindly help me asap as it is urgent.

Thanks,
Amit

[Amit Kumar6]

Hi Aman Gurram,

I have similar requirement on sc_request table. I want to trigger notifications 2 days prior to due date excluding weekends.

I tried your code :

var dur = new GlideDuration('-2 00:00:00');
var now = new GlideDateTime();

var sched = new GlideSchedule();
sched.load('090eecae0a0a0b260077e1dfa71da828');  // sys id of 8-5 weekdays excluding weekends schedule.

var required_dueDate = sched.add(now,dur);
queryPlannedDate();

function queryPlannedDate(){

var expdt = new GlideRecord('sc_request');
expdt.addQuery('u_request_type', 'Venue_Maps');
expdt.addQuery('active',true);
expdt.addQuery('due_date',">",required_dueDate.getDate());
expdt.query();
while(expdt.next()){

gs.print(expdt.number);

}
}

I am getting result :

REQ0022244 - 2019-08-06 14:47:00
REQ0033759 - 2019-08-02 13:16:23

ideally it should show te first record but it is showing the 2nd record as well.

Kindly help me asap as it is urgent.

Thanks,
Amit

[ARG645]

The below code is applicable for running in a scheduled job. 

// Why did you use a -2 value ? I changed it to positive
var dur = new GlideDuration('2 00:00:00');
var now = new GlideDateTime();

var sched = new GlideSchedule();
/*
Are you sure you want to add 8-5 weekdays only, or you can do 24hrs weekdays
If you add two days to the 8-5 weekdays schedule, you are adding 48hs to the schedule, which means adding 9 hrs each day (8-5) will result in 5.3 days in advance.
 */
sched.load('090eecae0a0a0b260077e1dfa71da828');  // sys id of 8-5 weekdays excluding weekends schedule.

var required_dueDate = sched.add(now,dur);

gs.print("The caliculated DueDate is "+required_dueDate);

queryPlannedDate();

function queryPlannedDate(){
	
	var expdt = new GlideRecord('sc_request');
	expdt.addQuery('u_request_type', 'Venue_Maps');
	expdt.addQuery('active',true);
	/*
	Now that we caliculated the date which is [current+48hrs] in the schedule, lets compare it to your due date. If records exist with that due date, grab them.
 	*/
	expdt.addQuery('due_date',required_dueDate.getDate());
	expdt.query();
	while(expdt.next()){
		
		gs.print(expdt.number);
		
	}
}