Hi ServiceNow Experts,
@Ankur Bawiskar @Laszlo Balla @Community Alums
Need small help. We have a requirement in which we need to populate quarterly data in to the variables based on u_start_date which are more than 4years records count. in Q1 variable we need to get from tomorrow to next year march end records count which are more than 4 years, in q2 we need next year April starting to June ending, in q2 we need next year July starting to September ending. in q4 we need next year October starting to December ending. And finally we need Total of all records. In this i am able to populate Today variable count which are more than 4 years till today.
Script include:
var ComputerCountScriptInclude = Class.create();
ComputerCountScriptInclude.prototype = Object.extendsObject(AbstractAjaxProcessor, {
getComputerCountByFormFactors: function(formFactors) {
var count = 0;
var fourYearsAgo = new GlideDateTime();
fourYearsAgo.addYearsUTC(-4);
formFactors.forEach(function(formFactor) {
var gr = new GlideRecord('cmdb_ci_computer');
gr.addEncodedQuery('asset.model_category=81feb9c137101000deeabfc8bcbe5dc4^asset.install_status!=7^asset.model.u_form_factorIN' + formFactor + '^u_start_date<=' + fourYearsAgo.getDisplayValue());
gr.query();
count += gr.getRowCount();
});
return count;
},
getComputerCountByQuarter: function(quarter) {
var count = 0;
var startDate = new GlideDateTime();
var endDate = new GlideDateTime();
// Set the start and end dates based on the quarter
if (quarter === 'Q1') {
startDate.addDays(1);
endDate.addYearsUTC(1).setMonthUTC(2).setDayOfMonthUTC(endDate.getDaysInMonthUTC(2)); // March
} else if (quarter === 'Q2') {
startDate.addYearsUTC(1).setMonthUTC(3); // April
endDate.addYearsUTC(1).setMonthUTC(5).setDayOfMonthUTC(endDate.getDaysInMonthUTC(5)); // June
} else if (quarter === 'Q3') {
startDate.addYearsUTC(1).setMonthUTC(6); // July
endDate.addYearsUTC(1).setMonthUTC(8).setDayOfMonthUTC(endDate.getDaysInMonthUTC(8)); // September
} else if (quarter === 'Q4') {
startDate.addYearsUTC(1).setMonthUTC(9); // October
endDate.addYearsUTC(1).setMonthUTC(11).setDayOfMonthUTC(endDate.getDaysInMonthUTC(11)); // December
}
// Set the start date for records more than 4 years old
var fourYearsAgo = new GlideDateTime();
fourYearsAgo.addYearsUTC(-4);
// Perform the query for records within the specified quarter and more than 4 years old
var gr = new GlideRecord('cmdb_ci_computer');
gr.addEncodedQuery('asset.model_category=81feb9c137101000deeabfc8bcbe5dc4^asset.install_status!=7^u_start_date>=' + startDate.getDisplayValue() + '^u_start_date<=' + endDate.getDisplayValue() + '^u_start_date<=' + fourYearsAgo.getDisplayValue());
gr.query();
count += gr.getRowCount();
return count;
},
getComputerlaptopCount: function() {
return this.getComputerCountByFormFactors(['laptop']);
},
getComputerdesktopCount: function() {
return this.getComputerCountByFormFactors(['desktop']);
},
getComputerworkstationCount: function() {
return this.getComputerCountByFormFactors(['workstations']);
},
getComputertabletCount: function() {
return this.getComputerCountByFormFactors(['tablet']);
},
getComputerQ1Count: function() {
return this.getComputerCountByQuarter('Q1');
},
getComputerQ2Count: function() {
return this.getComputerCountByQuarter('Q2');
},
getComputerQ3Count: function() {
return this.getComputerCountByQuarter('Q3');
},
getComputerQ4Count: function() {
return this.getComputerCountByQuarter('Q4');
},
getTotalComputerCount: function() {
var total = 0;
// Today's count
total += this.getComputerCountByFormFactors(['laptop', 'desktop', 'workstations', 'tablet']);
// Q1 count
total += this.getComputerCountByQuarter('Q1');
// Q2 count
total += this.getComputerCountByQuarter('Q2');
// Q3 count
total += this.getComputerCountByQuarter('Q3');
// Q4 count
total += this.getComputerCountByQuarter('Q4');
return total;
},
type: 'ComputerCountScriptInclude'
});
Client Script:
// Name: ComputerCountClientScript
// Table: Catalog Client Script
// Type: OnChange
function onChange(control, oldValue, newValue, isLoading, isTemplate) {
if (isLoading || newValue === '') {
return;
}
var selectedCategory = g_form.getValue('category');
var ajaxParams = {
'Laptop': 'getComputerlaptopCount',
'Desktop': 'getComputerdesktopCount',
'Workstation': 'getComputerworkstationCount',
'Tablet': 'getComputertabletCount'
};
// Check if the selected category is in the mapping
if (ajaxParams.hasOwnProperty(selectedCategory)) {
// Call the server-side script include using GlideAjax for aged_today
var glideAjaxAgedToday = new GlideAjax('ComputerCountScriptInclude');
glideAjaxAgedToday.addParam('sysparm_name', ajaxParams[selectedCategory]);
glideAjaxAgedToday.getXMLAnswer(function(response) {
// Update the catalog variable with the count
g_form.setValue('aged_today', response);
});
// Call the server-side script include using GlideAjax for Q1
var glideAjaxQ1 = new GlideAjax('ComputerCountScriptInclude');
glideAjaxQ1.addParam('sysparm_name', 'getComputerQ1Count');
glideAjaxQ1.getXMLAnswer(function(response) {
// Update the catalog variable with the count
g_form.setValue('aged_q1', response);
});
// Call the server-side script include using GlideAjax for Q2
var glideAjaxQ2 = new GlideAjax('ComputerCountScriptInclude');
glideAjaxQ2.addParam('sysparm_name', 'getComputerQ2Count');
glideAjaxQ2.getXMLAnswer(function(response) {
// Update the catalog variable with the count
g_form.setValue('aged_q2', response);
});
// Call the server-side script include using GlideAjax for Q3
var glideAjaxQ3 = new GlideAjax('ComputerCountScriptInclude');
glideAjaxQ3.addParam('sysparm_name', 'getComputerQ3Count');
glideAjaxQ3.getXMLAnswer(function(response) {
// Update the catalog variable with the count
g_form.setValue('aged_q3', response);
});
// Call the server-side script include using GlideAjax for Q4
var glideAjaxQ4 = new GlideAjax('ComputerCountScriptInclude');
glideAjaxQ4.addParam('sysparm_name', 'getComputerQ4Count');
glideAjaxQ4.getXMLAnswer(function(response) {
// Update the catalog variable with the count
g_form.setValue('aged_q4', response);
});
// Call the server-side script include using GlideAjax for total
var glideAjaxTotal = new GlideAjax('ComputerCountScriptInclude');
glideAjaxTotal.addParam('sysparm_name', 'getTotalComputerCount');
glideAjaxTotal.getXMLAnswer(function(response) {
// Update the catalog variable with the count
g_form.setValue('aged_total', response);
});
}
}
Thanks in advance
