Hello Everyone,
we are trying to connect our Servicenow to Outsystems to extract the time bookings.
we would like to have fields from cost center table,user table,task and services table.
i know that we have to use scripted APIs but i dont know how to do that.
Below i have attached the script i am using currently can somebody guide me here as to how this can be implemented in scripted API.
var timesToSend = {'times':[]};
var ignoredTimesToSend = {'ignoredTimes':[]};
var ignoredReason = "";
var times = new GlideAggregate('task_time_worked');// table where the time worked is stored
times.addEncodedQuery("sys_created_onONLast month@javascript:gs.beginningOfLastMonth()@javascript:gs.endOfLastMonth()");//run the query for last month and copy the query.
times.addAggregate('SUM', 'time_worked');//summing up the time worked by a employee
times.groupBy('user');
times.groupBy('task');
times.addTrend('sys_created_on','Date');
times.query();
while (times.next()) {
var sum = times.getAggregate('SUM', 'time_worked');
var sumArray = sum.split(':');
var hours = parseFloat(sumArray[0]*1 +(sumArray[1]/60) + (sumArray[2]/60/60)).toFixed(2);
var employeeNo = times.user.employee_number.toString();
var user_id=times.user.user_name.toString();
var employeeLocationCode = times.user.location.u_sap_location_code.toString();
var costCenter = times.user.cost_center.code.toString();
var wbsElement = times.task.business_service.cost_center.code.toString();
wbsElement = wbsElement.replace('XXX',employeeLocationCode);
var taskNumber = times.task.getDisplayValue();
var date = (times.getValue('timeref').split('/'))[0];
var dateArray = date.split('-');
var dateFormatted = dateArray[2]+'/'+dateArray[1]+'/'+dateArray[0];
if(hours ==="0.00" || employeeNo === "" || (costCenter === "" && (wbsElement === "" || employeeLocationCode === "")) || taskNumber === "")
{
var ignoredTaskUserTime = {
'personal_number':employeeNo,
'user_name':user_id,
'wbs_element':wbsElement,
'activity_text':'OTHERS / SONSTIGES',
'comment':taskNumber,
'sum':hours,
'date':dateFormatted,
// 'rec_cost_center':wbsElement === '' ? costCenter : '',
// 'send_cost_center':costCenter,
'ignored_reason':ignoredReason
};
if(taskNumber === "" && employeeNo === "")
{
ignoredTaskUserTime.ignored_reason = "Time has been ignored ("+hours+" hours) because employee number and task number are missing.\n";
ignoredTimesToSend.ignoredTimes.push(ignoredTaskUserTime);
}
else if(taskNumber === "" && employeeNo !== "")
{
ignoredTaskUserTime.ignored_reason = "Time has been ignored for employee "+employeeNo+" ("+hours+" hours) because task number is missing.\n";
ignoredTimesToSend.ignoredTimes.push(ignoredTaskUserTime);
}
else if(employeeNo === "" && taskNumber !== "")
{
ignoredTaskUserTime.ignored_reason = "Time has been ignored on "+taskNumber+" ("+hours+" hours) because employee number is missing.\n";
ignoredTimesToSend.ignoredTimes.push(ignoredTaskUserTime);
}
else if(costCenter === "" && (wbsElement === "" || employeeLocationCode === ""))
{
if(wbsElement !== "" || employeeLocationCode === "")
{
ignoredTaskUserTime.ignored_reason = "Time has been ignored on "+taskNumber+" ("+hours+" hours) because location code is missing.\n";
ignoredTimesToSend.ignoredTimes.push(ignoredTaskUserTime);
}
else
{
ignoredTaskUserTime.ignored_reason = "Time has been ignored on "+taskNumber+" ("+hours+" hours) because cost center and wbs element are missing.\n";
ignoredTimesToSend.ignoredTimes.push(ignoredTaskUserTime);
}
}
else if (hours === "0.00")
{
hours="0.01";
}
continue;
}
var taskUserTime = {
'personal_number':employeeNo,
'user_name':user_id,
'wbs_element':wbsElement,
'activity_text':'OTHERS / SONSTIGES',
'comment':taskNumber,
'sum':hours,
'date':dateFormatted,
'ignored_reason':ignoredReason
//'rec_cost_center':wbsElement === '' ? costCenter : '',
//'send_cost_center':costCenter
};
timesToSend.times.push(taskUserTime);
}
gs.log(JSON.stringify(timesToSend));
gs.log(JSON.stringify(ignoredTimesToSend));
Thank you all in Advance,
Renu.
