LOTEC-146 Material Forecasting Report
Suitelet page for forecasting report:
/**
* @NApiVersion 2.1
* @NScriptType Suitelet
*/
/*************************************************************************************************************************
* L+O Technologies
* LOTEC-148 Report for forecasting the Materials required for each branch in a date range based on Proscope
* Epic: LOTEC-146
*
*********************************************************************************************************************
*
* Author: Jobin & Jismi
*
* Date Created : 08-April-2024
*
* Description : Report for forecasting the Materials required for each branch in a date range based on Proscope
*
* REVISION HISTORY
*
* @version 1.0 LOTEC-148 : 08-April-2024 : Created the initial build by JJ0053
*
**************************************************************************************************************************/
define(['N/ui/serverWidget', 'N/query', 'N/record', 'N/url', 'N/render', 'N/encode', 'N/file', 'N/runtime', 'N/task', 'N/format'],
/**
* @param{https} serverWidget
* @param{query} query
* @param{record} record
* @param{search} url
*/
(serverWidget, query, record, url, render, encode, file, runtime, task, format) => {
/**
* Set the data in the sublist
* @param {Array} reportData
* @param {Object} sublist
*/
function addDataToSublist(reportData, sublist) {
try {
for (let i = 0; i < reportData.length; i++) {
let projectUrl = url.resolveRecord({
recordType: 'job',
recordId: reportData[i].projectId,
isEditMode: false
});
let projectTaskUrl = url.resolveRecord({
recordType: 'projecttask',
recordId: reportData[i].projectTaskId,
isEditMode: false
});
sublist.setSublistValue({
id: 'custpage_project',
line: i,
value: `<a href="${projectUrl}">${reportData[i].projectName}</a>`
});
sublist.setSublistValue({
id: 'custpage_project_task',
line: i,
value: `<a href="${projectTaskUrl}">${reportData[i].projectTask}</a>`
});
sublist.setSublistValue({
id: 'custpage_item',
line: i,
value: reportData[i].itemName
});
sublist.setSublistValue({
id: 'custpage_quantity',
line: i,
value: reportData[i].quantity
});
sublist.setSublistValue({
id: 'custpage_item_cost',
line: i,
value: reportData[i].itemCost
});
sublist.setSublistValue({
id: 'custpage_material_status',
line: i,
value: reportData[i].status
});
}
} catch (e) {
log.error('error@addDataToSublist', e);
}
}
/**
* Create the Suitlet page for searching option
* @param {Object} searchData
* @param {String} department
* @param {String} startDate
* @param {String} endDate
* @returns {form Object}
*/
function createReportPage(searchData, department, startDate, endDate) {
try {
let form = serverWidget.createForm({
title: 'Forecasting Procurement Search',
hideNavBar: false
});
let depField = form.addField({
id: 'custpage_branch',
type: serverWidget.FieldType.SELECT,
label: 'Branch',
source: 'department'
});
depField.defaultValue = department;
let startDateField = form.addField({
id: 'custpage_startdate',
type: serverWidget.FieldType.DATE,
label: 'Start Date'
});
startDateField.isMandatory = true;
startDateField.defaultValue = startDate;
let endDateField = form.addField({
id: 'custpage_enddate',
type: serverWidget.FieldType.DATE,
label: 'End Date'
});
endDateField.isMandatory = true;
endDateField.defaultValue = endDate;
let pageField = form.addField({
id: 'custpage_page',
type: serverWidget.FieldType.SELECT,
label: 'Page'
});
for (let i = 1; i <= searchData.pageCount; i++) {
pageField.addSelectOption({
value: i,
text: 'Page-' + i
});
}
if (!searchData.pageCount) {
pageField.addSelectOption({
value: 1,
text: 'Page-' + 1
});
}
pageField.defaultValue = searchData.pageFilter ? searchData.pageFilter : 1;
let pageLengField = form.addField({
id: 'custpage_page_length',
type: serverWidget.FieldType.TEXT,
label: 'Page Length'
});
pageLengField.defaultValue = searchData.pageCount;
pageLengField.updateDisplayType({
displayType: serverWidget.FieldDisplayType.HIDDEN
});
let sublist = form.addSublist({
id: 'custpage_sublist',
type: serverWidget.SublistType.LIST,
label: 'Requisitions'
});
sublist.addField({
id: 'custpage_project',
type: serverWidget.FieldType.TEXT,
label: 'Project Name'
});
sublist.addField({
id: 'custpage_project_task',
type: serverWidget.FieldType.TEXT,
label: 'Project Task'
});
sublist.addField({
id: 'custpage_item',
type: serverWidget.FieldType.TEXT,
label: 'Item Code'
});
sublist.addField({
id: 'custpage_quantity',
type: serverWidget.FieldType.TEXT,
label: 'Quantity'
});
sublist.addField({
id: 'custpage_item_cost',
type: serverWidget.FieldType.TEXT,
label: 'Item Cost'
});
sublist.addField({
id: 'custpage_material_status',
type: serverWidget.FieldType.TEXT,
label: 'Material Status'
});
addDataToSublist(searchData.requisitions, sublist)
form.addButton({
id: 'custpage_search',
label: 'Search',
functionName: 'searchRequisitions()'
});
form.addButton({
id: 'custpage_download',
label: 'Export Excel',
functionName: 'exportReport()'
});
form.clientScriptModulePath = '../LOTEC-146 Forecasting Search/jj_cs_forecasting_report_lotec_146.js';
return form;
} catch (e) {
log.error('error@createReportPage', e);
}
}
/**
* @description Function to remove HTLML tags
* @param {string} input
* @returns
*/
const removeHtmlTags = (input) => {
if (typeof input == "string")
return input.replace(/<[^>]+>/g, '');
else
return input
}
/**
* function to create and render excel file
* @param {object} scriptContext
* @returns {file Object}
*/
const generateExcel = (scriptContext) => {
try {
let para = scriptContext.request.parameters;
let department = para?.depId ? para.depId : '';
let startDate = para.startDate;
let endDate = para.endDate;
let obj = querySearch(department, startDate, endDate, true);
let data = obj.requisitions;
const lines = data.map(obj => {
for (let key in obj) {
obj[key] = removeHtmlTags(obj[key]);
}
return obj;
});
log.debug("lines", lines)
let renderObj = render.create();
renderObj.templateContent = file.load({
id: './jj_template_export_forecasting_lotec_146.xml'
}).getContents();
renderObj.addCustomDataSource({
format: render.DataSource.OBJECT,
alias: "record",
data: {
lines
}
});
let renderAsString = renderObj.renderAsString();
let strXmlEncoded = encode.convert({
string: renderAsString,
inputEncoding: encode.Encoding.UTF_8,
outputEncoding: encode.Encoding.BASE_64
});
let objXlsFile = file.create({
name: `FORECASTING_PROCUREMENT_SEARCH${Date.now() / 1000}.xls`,
fileType: file.Type.EXCEL,
contents: strXmlEncoded
});
return objXlsFile;
} catch (error) {
log.error("Error @generateExcel", error);
return false;
}
}
/**
* Query serch to fetch the data
* @param {String} department
* @param {String} startDate
* @param {String} endDate
* @param {boolean} isExport
* @param {String|Number} pageFilter
* @returns {Object}
*/
function querySearch(department, startDate, endDate, isExport, pageFilter) {
let requisitions = [];
try {
var requisitionQuery = query.create({
type: 'customrecord_cs_other_costed_items'
});
var activityJoin = requisitionQuery.join({
fieldId: 'custrecord_cs_linked_activity'
});
var projectTaskJoin = activityJoin.join({
fieldId: 'custrecord_cs_as_linked_project_task'
});
var projectJoin = requisitionQuery.join({
fieldId: 'custrecord_cs_linked_proj'
});
var statusJoin = requisitionQuery.join({
fieldId: 'custrecord_cs_material_status'
});
var itemJoin = requisitionQuery.joinTo({
fieldId: 'custrecord_cs_ot_costed_item_code',
target: 'item'
});
var firstCondition = projectTaskJoin.createCondition({
fieldId: 'startDateTime',
operator: query.Operator.ON_OR_AFTER,
values: [startDate]
});
var secondCondition = projectTaskJoin.createCondition({
fieldId: 'startDateTime',
operator: query.Operator.ON_OR_BEFORE,
values: [endDate]
});
var thirdCondition = projectTaskJoin.createCondition({
fieldId: 'endDate',
operator: query.Operator.ON_OR_AFTER,
values: [startDate]
});
var fourthCondition = projectTaskJoin.createCondition({
fieldId: 'endDate',
operator: query.Operator.ON_OR_BEFORE,
values: [endDate]
});
if (department)
var depCondition = projectJoin.createCondition({
fieldId: 'custentity_cs_project_department',
operator: query.Operator.ANY_OF,
values: department
});
var statusCondition = requisitionQuery.createCondition({
fieldId: 'custrecord_cs_material_status',
operator: query.Operator.ANY_OF,
values: ["3", "7", "9"]
});
var emptyStatusCondition = requisitionQuery.createCondition({
fieldId: 'custrecord_cs_material_status',
operator: query.Operator.EMPTY
});
if (department)
requisitionQuery.condition = requisitionQuery.and(
firstCondition, secondCondition, thirdCondition, fourthCondition, depCondition, requisitionQuery.or(statusCondition, emptyStatusCondition)
);
else
requisitionQuery.condition = requisitionQuery.and(
firstCondition, secondCondition, thirdCondition, fourthCondition, requisitionQuery.or(statusCondition, emptyStatusCondition)
);
requisitionQuery.columns = [
requisitionQuery.createColumn({
fieldId: 'id',
alias: 'requisitionId'
}),
requisitionQuery.createColumn({
fieldId: 'custrecord_cs_linked_proj',
alias: 'projectId'
}),
projectJoin.createColumn({
fieldId: 'entityTitle',
alias: 'projectName'
}),
requisitionQuery.createColumn({
fieldId: 'custrecord_cs_ot_costed_item_code',
alias: 'itemId'
}),
itemJoin.createColumn({
fieldId: 'itemId',
alias: 'itemName'
}),
requisitionQuery.createColumn({
fieldId: 'custrecord_cs_ot_cost_qty',
alias: 'quantity'
}),
statusJoin.createColumn({
fieldId: 'name',
alias: 'status'
}),
requisitionQuery.createColumn({
fieldId: 'custrecord_cs_linked_activity',
alias: 'activity'
}),
projectTaskJoin.createColumn({
fieldId: 'title',
alias: 'projectTask'
}),
projectTaskJoin.createColumn({
fieldId: 'id',
alias: 'projectTaskId'
}),
itemJoin.createColumn({
fieldId: 'costEstimate',
alias: 'itemCost'
}),
projectTaskJoin.createColumn({
fieldId: 'startDateTime',
alias: 'startDate'
}),
projectTaskJoin.createColumn({
fieldId: 'endDate',
alias: 'endDate'
}),
];
requisitionQuery.sort = [
requisitionQuery.createSort({
column: requisitionQuery.columns[0],
ascending: true
})
];
if (isExport) {
var resultSet = requisitionQuery.run();
requisitions = resultSet.asMappedResults();
return { requisitions: requisitions }
} else {
let pagedData = requisitionQuery.runPaged({
pageSize: 1000
});
log.debug('pagedData.pageRanges', pagedData.pageRanges)
let pageCount = pagedData.pageRanges.length;
log.debug('pageCount', pageCount);
let hasPage = pagedData.pageRanges.filter((pageRange) => {
return Number(pageRange.index) == (pageFilter ? Number(pageFilter) - 1 : 0);
});
pageFilter = hasPage.length ? Number(hasPage[0].index) + 1 : 1;
if (pageCount) {
let page = pagedData.fetch({ index: hasPage.length ? hasPage[0].index : 0 });
page.data.results.forEach((result) => {
requisitions.push(result.asMap());
});
}
return { requisitions: requisitions, pageCount: pageCount, pageFilter: pageFilter };
}
} catch (e) {
log.error('error@querySearch', e);
return { requisitions: requisitions, pageCount: 0, pageFilter: 1 };
}
}
/**
* Schedule the Export of Search result
* @param {Context Object} scriptContext
* @returns {boolean}
*/
function toBeScheduled(scriptContext) {
try {
log.debug('in scheduled...')
let para = scriptContext.request.parameters;
let depId = para?.depId ? para.depId : '';
let startDate = para.startDate;
let endDate = para.endDate;
let recordObj = record.create({
type: "customrecord_jj_cr_forecast_report_sched", //JJ Report Scheduler
isDynamic: true,
});
recordObj.setValue({
fieldId: "custrecord_jj_report_status", //STATUS
value: "PENDING",
});
var userObj = runtime.getCurrentUser();
recordObj.setValue({
fieldId: "custrecord_jj_report_requester", //REPORT REQUESTOR
value: userObj.id,
});
recordObj.setValue({
fieldId: "custrecord_jj_report_filter", //APPLIED FILTERS
value: JSON.stringify({
depId: depId,
startDate: startDate,
endDate: endDate
}),
});
recordObj.save({
enableSourcing: true,
ignoreMandatoryFields: true,
});
try {
let mrTask = task.create({
taskType: task.TaskType.MAP_REDUCE,
scriptId: "customscript_jj_mr_report_scheduler",
});
let taskId = mrTask.submit();
log.debug('taskId', taskId);
} catch (err) {
log.error("Error@task.create", err);
}
return true;
} catch (err) {
log.error("error@toBeScheduled", err);
return false;
}
}
/**
* Defines the Suitelet script trigger point.
* @param {Object} scriptContext
* @param {ServerRequest} scriptContext.request - Incoming request
* @param {ServerResponse} scriptContext.response - Suitelet response
* @since 2015.2
*/
const onRequest = (scriptContext) => {
try {
let para = scriptContext.request.parameters;
let department = para?.depId ? para.depId : '';
let startDate = para.startDate;
let endDate = para.endDate;
let pageFilter = para.pageNo;
if (!startDate && !endDate) {
let date = new Date(), y = date.getFullYear(), m = date.getMonth();
let firstDay = new Date(y, m, 1);
let lastDay = new Date(y, m + 1, 0);
startDate = format.format({
value: firstDay,
type: format.Type.DATE
});
endDate = format.format({
value: lastDay,
type: format.Type.DATE
});
}
if (scriptContext.request.method === 'GET') {
let searchData = querySearch(department, startDate, endDate, false, pageFilter)
let formObj = createReportPage(searchData, department, startDate, endDate)
scriptContext.response.writePage({
pageObject: formObj ? formObj : "SOMETHING_WENT_WRONG"
});
} else {
if (para.toBeDownloaded == "true") {
let excelFile = generateExcel(scriptContext);
return scriptContext.response.writeFile({
file: excelFile ? excelFile : "SOMETHING_WENT_WRONG"
});
} else if (para.toBeScheduled == "true") {
let handleScheduleRequest = toBeScheduled(scriptContext);
if (handleScheduleRequest) {
return scriptContext.response.write("REPORT_SCHEDULED"), true;
} else {
return (
scriptContext.response.write("REPORT_CANNOT_BE_SCHEDULED"),
true
);
}
}
}
} catch (e) {
log.error('error@onRequest', e);
}
}
return { onRequest }
});
Client script:
/**
* @NApiVersion 2.x
* @NScriptType ClientScript
* @NModuleScope SameAccount
*/
/*************************************************************************************************************************
* L+O Technologies
* LOTEC-148 Report for forecasting the Materials required for each branch in a date range based on Proscope
* Epic: LOTEC-146
*
*********************************************************************************************************************
*
* Author: Jobin & Jismi
*
* Date Created : 09-April-2024
*
* Description : Report for forecasting the Materials required for each branch in a date range based on Proscope
*
* REVISION HISTORY
*
* @version 1.0 LOTEC-148 : 09-April-2024 : Created the initial build by JJ0053
*
**************************************************************************************************************************/
define(['N/currentRecord', 'N/url', 'N/ui/message'],
/**
* @param{currentRecord} currentRecord
* @param{url} url
*/
function (currentRecord, url, message) {
var initalDepId = '';
var initalStartDate = '';
var initalEndDate = '';
var initalPageLeng = '';
/**
* Function to show the loading symbol.
*/
function showLoadingSymbol() {
let loadingDiv = document.createElement("div");
loadingDiv.id = "loading-symbol";
loadingDiv.style.position = "fixed";
loadingDiv.style.top = "50%";
loadingDiv.style.left = "50%";
loadingDiv.style.transform = "translate(-50%, -50%)";
let fileId = 11241941;//sb- 6596341; //internal ID of your image file in the NetSuite File Cabinet.
let imageFileURL = nlapiResolveURL('mediaitem', fileId);
loadingDiv.innerHTML = '<img src="' + imageFileURL + '" alt="Loading...">';
document.body.appendChild(loadingDiv);
}
/**
* Function to be executed after page is initialized.
*
* @param {Object} scriptContext
* @param {Record} scriptContext.currentRecord - Current form record
* @param {string} scriptContext.mode - The mode in which the record is being accessed (create, copy, or edit)
*
* @since 2015.2
*/
function pageInit(scriptContext) {
try {
let recObj = currentRecord.get();
initalDepId = recObj.getValue({ fieldId: 'custpage_branch' });
initalStartDate = recObj.getText({ fieldId: 'custpage_startdate' });
initalEndDate = recObj.getText({ fieldId: 'custpage_enddate' });
initalPageLeng = recObj.getValue({ fieldId: 'custpage_page_length' });
} catch (e) {
console.log('error@pageInit', e);
}
}
/**
* Function to search
*/
function searchRequisitions() {
try {
if (window.onbeforeunload) {// to block the popup while reloading the suitelet page
window.onbeforeunload = function () {
null;
};
}
if (jQuery('#tdbody_custpage_search').length === 1) {
jQuery('#tdbody_custpage_search').css('pointer-events', 'none');
jQuery('#tdbody_custpage_search').css('opacity', '0.5');
}
let recObj = currentRecord.get();
let depId = recObj.getValue({ fieldId: 'custpage_branch' });
let startDate = recObj.getText({ fieldId: 'custpage_startdate' });
let endDate = recObj.getText({ fieldId: 'custpage_enddate' });
let pageNo = recObj.getValue({ fieldId: 'custpage_page' });
if (!startDate) {
alert('Please enter mandatory field, "Start Date"!');
// $('#loading-symbol').hide();
if (jQuery('#tdbody_custpage_search').length === 1) {
jQuery('#tdbody_custpage_search').css('pointer-events', 'auto');
jQuery('#tdbody_custpage_search').css('opacity', '1');
}
return false;
}
if (!endDate) {
alert('Please enter mandatory field, "End Date"!')
if (jQuery('#tdbody_custpage_search').length === 1) {
jQuery('#tdbody_custpage_search').css('pointer-events', 'auto');
jQuery('#tdbody_custpage_search').css('opacity', '1');
}
return false;
}
showLoadingSymbol();
var uniqueUrl = url.resolveScript({
scriptId: 'customscript_jj_sl_forecasting_procureme',
deploymentId: 'customdeploy_jj_sl_forecasting_procureme',
returnExternalUrl: false,
params: {
depId: depId,
startDate: startDate,
endDate: endDate,
pageNo: pageNo
}
});
location.href = uniqueUrl;
}
catch (e) {
console.log("Erorr @ exportSummaryReport", e);
if (jQuery('#loading-symbol').length === 1) {
jQuery('#loading-symbol').remove();
}
if (jQuery('#tdbody_custpage_search').length === 1) {
jQuery('#tdbody_custpage_search').css('pointer-events', 'auto');
jQuery('#tdbody_custpage_search').css('opacity', '1');
}
}
}
/**
* Function to export the detail report
*/
function exportReport() {
try {
if (jQuery('#tdbody_custpage_download').length === 1) {
jQuery('#tdbody_custpage_download').css('pointer-events', 'none');
jQuery('#tdbody_custpage_download').css('opacity', '0.5');
}
if (!initalStartDate) {
alert('Please enter mandatory field, "Start Date" and Click the "Search" button to search first before exporting the Excel!');
if (jQuery('#tdbody_custpage_download').length === 1) {
jQuery('#tdbody_custpage_download').css('pointer-events', 'auto');
jQuery('#tdbody_custpage_download').css('opacity', '1');
}
return false;
}
if (!initalEndDate) {
alert('Please enter mandatory field, "End Date" and Click the "Search" button to search first before exporting the Excel!')
if (jQuery('#tdbody_custpage_download').length === 1) {
jQuery('#tdbody_custpage_download').css('pointer-events', 'auto');
jQuery('#tdbody_custpage_download').css('opacity', '1');
}
return false;
}
if (initalPageLeng && Number(initalPageLeng) == 0)
return alert("There is no data to download");
if (initalPageLeng > 5) {
fetch(
url.resolveScript({
scriptId: 'customscript_jj_sl_forecasting_procureme',
deploymentId: 'customdeploy_jj_sl_forecasting_procureme',
returnExternalUrl: false,
params: {
depId: initalDepId,
startDate: initalStartDate,
endDate: initalEndDate,
toBeDownloaded: false,
toBeScheduled: true
}
}),
{
method: "POST",
headers: {
"Content-Type": "application/json",
},
body: JSON.stringify({}),
}
)
.then(function (response) {
return response.text();
})
.then(function (response) {
console.log(response);
if (response == "REPORT_SCHEDULED")
message.create({
title: "EMAIL SCHEDULE",
message: "Your request for report export has been scheduled",
type: message.Type.CONFIRMATION,
})
.show({
duration: 10000, // will disappear after 10s
});
else
message.create({
title: "EMAIL SCHEDULE",
message:
"Your request for report export cannot be scheduled. Try again later",
type: message.Type.ERROR,
})
.show({
duration: 10000, // will disappear after 10s
});
if (jQuery('#tdbody_custpage_download').length === 1) {
jQuery('#tdbody_custpage_download').css('pointer-events', 'auto');
jQuery('#tdbody_custpage_download').css('opacity', '1');
}
return true;
});
alert(
"This cannot be exported in realtime. You need to schedule this to email the files"
);
return true;
} else {
message.create({
title: "REPORT EXPORT",
message: "Please wait and do not navigate while we prepare the export",
type: message.Type.CONFIRMATION,
})
.show({
duration: 15000, // will disappear after 15s
});
showLoadingSymbol();
var fetchData = fetch(
url.resolveScript({
scriptId: 'customscript_jj_sl_forecasting_procureme',
deploymentId: 'customdeploy_jj_sl_forecasting_procureme',
returnExternalUrl: false,
params: {
depId: initalDepId,
startDate: initalStartDate,
endDate: initalEndDate,
toBeDownloaded: true,
toBeScheduled: false
}
}), {
method: 'POST',
headers: {
},
body: JSON.stringify({}),
});
fetchData.then(function (response) {
return response.blob();
}).then(function (blob) {
console.log("BLOB >>", blob);
var url = window.URL.createObjectURL(blob);
console.log("url", url);
var a = document.createElement('a');
a.href = url;
a.download = 'Forecasting Procurement Report' + "-" + (Date.now() / 1000) + ".xls";
document.body.appendChild(a); // we need to append the element to the dom -> otherwise it will not work in firefox
a.click();
a.remove(); //afterwards we remove the element again
jQuery('#loading-symbol').remove();
if (jQuery('#tdbody_custpage_download').length === 1) {
jQuery('#tdbody_custpage_download').css('pointer-events', 'auto');
jQuery('#tdbody_custpage_download').css('opacity', '1');
}
});
}
} catch (e) {
console.log("Erorr @ exportReport", e)
if (jQuery('#tdbody_custpage_download').length === 1) {
jQuery('#tdbody_custpage_download').css('pointer-events', 'auto');
jQuery('#tdbody_custpage_download').css('opacity', '1');
}
}
}
return {
pageInit: pageInit,
searchRequisitions: searchRequisitions,
exportReport: exportReport
};
});
Mapreduce script:
/**
* @NApiVersion 2.1
* @NScriptType MapReduceScript
*/
/*************************************************************************************************************************
* L+O Technologies
* LOTEC-149 Set Up the Functionalities for the Forecasting Procurement Report
* Epic: LOTEC-146
*
*********************************************************************************************************************
*
* Author: Jobin & Jismi
*
* Date Created : 17-April-2024
*
* Description : Report for forecasting the Materials required for each branch in a date range based on Proscope
*
* REVISION HISTORY
*
* @version 1.0 LOTEC-149 : 17-April-2024 : Created the initial build by JJ0053
*
**************************************************************************************************************************/
define(['N/email', 'N/encode', 'N/file', 'N/query', 'N/record', 'N/search', 'N/config', 'N/render', 'N/url'],
/**
* @param{email} email
* @param{encode} encode
* @param{file} file
* @param{query} query
* @param{record} record
*/
(email, encode, file, query, record, search, config, render, url) => {
/**
* To chunk Array into predefined units
* @param{Array} arr
* @param{Number} n - Chunk size
* @returns {*}
*/
const chunkArray = function (arr, n) {
return arr.reduce(function (p, cur, i) {
(p[i / n | 0] = p[i / n | 0] || []).push(cur);
return p;
}, []);
};
/**
* Query serch to fetch the data
* @param {Object} reportAppliedFilter
* @returns {Array}
*/
function querySearch(reportAppliedFilter) {
let requisitions = [];
try {
var requisitionQuery = query.create({
type: 'customrecord_cs_other_costed_items'
});
var activityJoin = requisitionQuery.join({
fieldId: 'custrecord_cs_linked_activity'
});
var projectTaskJoin = activityJoin.join({
fieldId: 'custrecord_cs_as_linked_project_task'
});
var projectJoin = requisitionQuery.join({
fieldId: 'custrecord_cs_linked_proj'
});
var statusJoin = requisitionQuery.join({
fieldId: 'custrecord_cs_material_status'
});
var itemJoin = requisitionQuery.joinTo({
fieldId: 'custrecord_cs_ot_costed_item_code',
target: 'item'
});
var firstCondition = projectTaskJoin.createCondition({
fieldId: 'startDateTime',
operator: query.Operator.ON_OR_AFTER,
values: [reportAppliedFilter.startDate]
});
var secondCondition = projectTaskJoin.createCondition({
fieldId: 'startDateTime',
operator: query.Operator.ON_OR_BEFORE,
values: [reportAppliedFilter.endDate]
});
var thirdCondition = projectTaskJoin.createCondition({
fieldId: 'endDate',
operator: query.Operator.ON_OR_AFTER,
values: [reportAppliedFilter.startDate]
});
var fourthCondition = projectTaskJoin.createCondition({
fieldId: 'endDate',
operator: query.Operator.ON_OR_BEFORE,
values: [reportAppliedFilter.endDate]
});
if (reportAppliedFilter.depId)
var depCondition = projectJoin.createCondition({
fieldId: 'custentity_cs_project_department',
operator: query.Operator.ANY_OF,
values: reportAppliedFilter.depId
});
var statusCondition = requisitionQuery.createCondition({
fieldId: 'custrecord_cs_material_status',
operator: query.Operator.ANY_OF,
values: ["3", "7", "9"]
});
var emptyStatusCondition = requisitionQuery.createCondition({
fieldId: 'custrecord_cs_material_status',
operator: query.Operator.EMPTY
});
if (reportAppliedFilter.depId)
requisitionQuery.condition = requisitionQuery.and(
firstCondition, secondCondition, thirdCondition, fourthCondition, depCondition, requisitionQuery.or(statusCondition, emptyStatusCondition)
);
else
requisitionQuery.condition = requisitionQuery.and(
firstCondition, secondCondition, thirdCondition, fourthCondition, requisitionQuery.or(statusCondition, emptyStatusCondition)
);
requisitionQuery.columns = [
requisitionQuery.createColumn({
fieldId: 'id',
alias: 'requisitionId'
}),
requisitionQuery.createColumn({
fieldId: 'custrecord_cs_linked_proj',
alias: 'projectId'
}),
projectJoin.createColumn({
fieldId: 'entityTitle',
alias: 'projectName'
}),
requisitionQuery.createColumn({
fieldId: 'custrecord_cs_ot_costed_item_code',
alias: 'itemId'
}),
itemJoin.createColumn({
fieldId: 'itemId',
alias: 'itemName'
}),
requisitionQuery.createColumn({
fieldId: 'custrecord_cs_ot_cost_qty',
alias: 'quantity'
}),
statusJoin.createColumn({
fieldId: 'name',
alias: 'status'
}),
requisitionQuery.createColumn({
fieldId: 'custrecord_cs_linked_activity',
alias: 'activity'
}),
projectTaskJoin.createColumn({
fieldId: 'title',
alias: 'projectTask'
}),
projectTaskJoin.createColumn({
fieldId: 'id',
alias: 'projectTaskId'
}),
itemJoin.createColumn({
fieldId: 'costEstimate',
alias: 'itemCost'
}),
projectTaskJoin.createColumn({
fieldId: 'startDateTime',
alias: 'startDate'
}),
projectTaskJoin.createColumn({
fieldId: 'endDate',
alias: 'endDate'
}),
];
requisitionQuery.sort = [
requisitionQuery.createSort({
column: requisitionQuery.columns[0],
ascending: true
})
];
let pagedData = requisitionQuery.runPaged({
pageSize: 1000
});
log.debug('pagedData.pageRanges', pagedData.pageRanges)
// var resultSet = myCustomerQuery.run();
// requisitions = resultSet.asMappedResults();
pagedData.pageRanges.forEach((pageRange) => {
let page = pagedData.fetch({ index: pageRange.index });
page.data.results.forEach((result) => {
requisitions.push(result.asMap());
});
});
return requisitions;
// var resultSet = requisitionQuery.run();
} catch (e) {
log.error('error@querySearch', e);
return requisitions;
}
}
const schedulerLogic = {
/**
* Predefined unit to chunk the model for passing to the template to render
*/
CHUNK_SIZE: 5000,
/**
* Initiate the report processing logic
* @param{Number} recordId - Internal Id of the custom record
*/
/**
* Initiate the report processing logic
* @param {Number} recordId -Internal Id of the custom record
* @param {Number} reportRequestor
* @param {String} reportAppliedFilter
* @returns {String}
*/
processReport(recordId, reportRequestor, reportAppliedFilter) {
record.submitFields({
type: 'customrecord_jj_cr_forecast_report_sched',
id: recordId,
values: {
custrecord_jj_report_status: 'PROCESSING'
},
options: {
enableSourcing: true,
ignoreMandatoryFields: true
}
});
try {
log.debug('reportAppliedFilter', reportAppliedFilter);
reportAppliedFilter = JSON.parse(reportAppliedFilter);
} catch (err) {
log.error('error@processReport for ID:' + recordId, err);
reportAppliedFilter = {};
}
let response = schedulerLogic.initiateReportExtraction(reportRequestor, reportAppliedFilter, recordId);
let recordObj = record.load({
type: 'customrecord_jj_cr_forecast_report_sched', //JJ Report Scheduler JS-270
id: recordId,
isDynamic: true
});
recordObj.setValue({
fieldId: 'Report Message', //Message
value: JSON.stringify(response)
});
recordObj.setValue({
fieldId: 'custrecord_jj_report_status', //STATUS
value: 'COMPLETED'
});
recordObj.save({
enableSourcing: true,
ignoreMandatoryFields: true
});
return "COMPLETED";
},
/**
*
* @param{Number} reportRequestor - Internal Id of report requestor
* @param{Object} reportAppliedFilter - Object containing filter elements
* @param{Number} recordId - Internal Id of Custom record
* @returns {string|error}
*/
initiateReportExtraction(reportRequestor, reportAppliedFilter, recordId) {
try {
let searchData = querySearch(reportAppliedFilter);
let chunkedResutls = chunkArray(searchData, schedulerLogic.CHUNK_SIZE);
log.debug('chunkedResutls : ' + chunkedResutls.length, chunkedResutls);
if (chunkedResutls.length == 0) {
return "NO_RESULT_TO_PROCESS";
} else if (chunkedResutls.length <= 2) {
let fileObjs = [];
for (let index = 0, len = chunkedResutls.length; index < len; index++) {
let renderObj = render.create();
renderObj.templateContent = file.load({
id: './jj_template_export_forecasting_lotec_146.xml'
}).getContents();
renderObj.addCustomDataSource({
format: render.DataSource.OBJECT,
alias: "record",
data: {
lines: chunkedResutls[index]
}
});
let renderAsString = renderObj.renderAsString();
let strXmlEncoded = encode.convert({
string: renderAsString,
inputEncoding: encode.Encoding.UTF_8,
outputEncoding: encode.Encoding.BASE_64
});
let objXlsFile = file.create({
name: `FORECASTING_PROCUREMENT_SEARCH${Date.now() / 1000}-${(index + 1)}.xls`,
fileType: file.Type.EXCEL,
contents: strXmlEncoded
});
fileObjs.push(objXlsFile);
}
schedulerLogic.sendEmail({
senderId: reportRequestor,
recipientId: reportRequestor,
recordTypeId: recordId,
message: false,
fileObjs: fileObjs,
});
return "PROCESSED_REQUEST";
} else {
for (let index = 0, len = chunkedResutls.length; index < len; index++) {
let renderObj = render.create();
renderObj.templateContent = file.load({
id: './jj_template_export_forecasting_lotec_146.xml'
}).getContents();
renderObj.addCustomDataSource({
format: render.DataSource.OBJECT,
alias: "record",
data: {
lines: chunkedResutls[index]
}
});
let renderAsString = renderObj.renderAsString();
let strXmlEncoded = encode.convert({
string: renderAsString,
inputEncoding: encode.Encoding.UTF_8,
outputEncoding: encode.Encoding.BASE_64
});
let objXlsFile = file.create({
name: `FORECASTING_PROCUREMENT_SEARCH${Date.now() / 1000}-${(index + 1)}.xls`,
fileType: file.Type.EXCEL,
contents: strXmlEncoded
});
objXlsFile.folder = 2729151;//sb-1553591;
let fileId = objXlsFile.save();
record.attach({
record: {
type: 'file',
id: fileId
},
to: {
type: 'customrecord_jj_cr_forecast_report_sched',
id: recordId
}
});
fileObj = undefined;
}
schedulerLogic.sendEmail({
senderId: reportRequestor,
recipientId: reportRequestor,
recordTypeId: recordId,
message: false,
fileObjs: [],
});
return "PROCESSED_REQUEST";
}
} catch (err) {
log.error('error@initiateReportExtraction', err);
schedulerLogic.sendEmail({
senderId: reportRequestor,
recipientId: reportRequestor,
recordTypeId: recordId,
message: 'The report extraction process schedule cannot be executed due to some error',
fileObjs: [],
});
return err;
}
},
/**
* To send email
* @param{Object} param
* @param{Number} param.senderId - Internal Id of sender
* @param{Number} param.recipientId - Internal Id of receiver
* @param{Number} param.recordTypeId - Internal Id of custom record
* @param{Boolean|String} param.message - If we want to pass custom message in the email body
* @param{file[]} param.fileObjs - Contains array of File Objects
* @returns {*}
*/
sendEmail({
senderId,
recipientId,
recordTypeId,
message = false,
fileObjs
}) {
log.debug('sendEmail', {
senderId,
recipientId,
recordTypeId,
message,
fileObjs
});
let companyInfo = config.load({
type: config.Type.COMPANY_INFORMATION
});
let ns_companyid = companyInfo.getValue({
fieldId: 'companyid'
});
ns_companyid = ns_companyid.toString().toLowerCase().replace('_', '-');
return email.send({
author: senderId,
recipients: recipientId,
subject: 'Export Result',
body: `Dear User,<br/>
${message || (fileObjs.length ? 'The report has been processed and the result has been attached along with this email.' :
'The report has been processed and since there are multiple files, you need to login to Netsuite to view the files.')}<br/>
To view the request details, <a href="https://${ns_companyid}.app.netsuite.com${url.resolveRecord({
recordType: 'customrecord_jj_cr_forecast_report_sched',
recordId: recordTypeId,
isEditMode: false
})
}" target="_blank">Click here</a>.<br/>
Thank you`,
attachments: fileObjs,
relatedRecords: {
entityId: recipientId,
customRecord: {
recordType: 'customrecord_jj_cr_forecast_report_sched',
id: recordTypeId,
}
}
});
}
};
/**
* Defines the function that is executed at the beginning of the map/reduce process and generates the input data.
* @param {Object} inputContext
* @param {boolean} inputContext.isRestarted - Indicates whether the current invocation of this function is the first
* invocation (if true, the current invocation is not the first invocation and this function has been restarted)
* @param {Object} inputContext.ObjectRef - Object that references the input data
* @typedef {Object} ObjectRef
* @property {string|number} ObjectRef.id - Internal ID of the record instance that contains the input data
* @property {string} ObjectRef.type - Type of the record instance that contains the input data
* @returns {Array|Object|Search|ObjectRef|File|Query} The input data to use in the map/reduce process
* @since 2015.2
*/
const getInputData = (inputContext) => {
try {
const customrecord_jj_report_scheduler_SearchObj = search.create({
type: "customrecord_jj_cr_forecast_report_sched",
filters: [
["custrecord_jj_report_status", "is", "PENDING"],
"AND",
["isinactive", "is", "F"],
],
columns: [
search.createColumn({
name: "internalid",
sort: search.Sort.DESC,
label: "InternalID"
}),
search.createColumn({ name: "custrecord_jj_report_status", label: "Status" }),
search.createColumn({ name: "custrecord_jj_report_requester", label: "ReportRequestor" }),
search.createColumn({ name: "custrecord_jj_report_filter", label: "ReportFilter" }),
]
});
log.debug('customrecord_jj_report_scheduler_SearchObj', customrecord_jj_report_scheduler_SearchObj);
return customrecord_jj_report_scheduler_SearchObj;
} catch (err) {
log.error('error@getInputData', err);
return [];
}
}
/**
* Defines the function that is executed when the reduce entry point is triggered. This entry point is triggered
* automatically when the associated map stage is complete. This function is applied to each group in the provided context.
* @param {Object} reduceContext - Data collection containing the groups to process in the reduce stage. This parameter is
* provided automatically based on the results of the map stage.
* @param {Iterator} reduceContext.errors - Serialized errors that were thrown during previous attempts to execute the
* reduce function on the current group
* @param {number} reduceContext.executionNo - Number of times the reduce function has been executed on the current group
* @param {boolean} reduceContext.isRestarted - Indicates whether the current invocation of this function is the first
* invocation (if true, the current invocation is not the first invocation and this function has been restarted)
* @param {string} reduceContext.key - Key to be processed during the reduce stage
* @param {List<String>} reduceContext.values - All values associated with a unique key that was passed to the reduce stage
* for processing
* @since 2015.2
*/
const reduce = (reduceContext) => {
try {
if (reduceContext.values.length > 0) {
let values = reduceContext.values.map(JSON.parse);
let internalId = values[0].values.internalid.value;
let requestor = values[0].values.custrecord_jj_report_requester.value;
let filter = values[0].values.custrecord_jj_report_filter;
let processReportStatus = schedulerLogic.processReport(internalId, requestor, filter);
log.debug('processReportStatus', processReportStatus);
}
} catch (err) {
log.error('error@reduce', err);
}
}
return { getInputData, reduce }
});