Jira Code: HTL-37
To create a suitelet page with 4 buttons. On click of each button, the corresponding reports will be attached to the email and send to the current user of the account.
Client Script: HLT-18 JJ CS Price Change Research
/**
* @NApiVersion 2.x
* @NScriptType ClientScript
* @NModuleScope SameAccount
*/
define(['N/url','N/https','N/search','N/currentRecord','N/record'],
function(url,https,search,currentRecord,record) {
/**
* 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) {
}
function priceChangeResearch(scriptContext) {
try {
var record = currentRecord.get();
var recid=record.id;
var pcUnique= record.getValue({
fieldId: 'custentity29'
});
if (pcUnique == "" || pcUnique == null || pcUnique == undefined || pcUnique =='- None -' || pcUnique == " "){
alert('The Price Change Unique Number is null.')
return false;
}
//Setting the url of the suitelet script
var output = url.resolveScript({
scriptId: 'customscript_htl18_jj_sl_itemreportform',
deploymentId: 'customdeploy_htl18_jj_sl_itemreportform',
returnExternalUrl: false,
})+ '&recid='+recid;
window.open(output);
} catch (e) {
console.log(e.message);
}
}
function downloadButton1(scriptContext){
try{
var record = currentRecord.get();
var recid=record.id;
var resultarray1 = record.getValue({
fieldId: 'csv_constants1'
});
var today = new Date();
var dd = today.getDate();
var mm = today.getMonth()+1;
var yyyy = today.getFullYear();
today = dd+'/'+mm+'/'+yyyy;
todayTitle = yyyy+''+mm+''+dd;
var suppId = record.getValue({
fieldId: 'supplier_id'
});
var datefrom = record.getText({
fieldId: 'custpage_price_chnage_reasearch'
});
var suppDtls = search.lookupFields({
type: search.Type.VENDOR,
id: suppId,
columns: ['custentity29','entityid','altname']
});
if (suppDtls.custentity29 != null) {
pcUniqueNum =suppDtls.custentity29;
}
if (suppDtls.entityid != null) {
suppIdNm =suppDtls.entityid;
}
if (suppDtls.altname != null) {
suppIdDName =suppDtls.altname;
}
var ReportTitle = suppIdNm+','+suppIdDName+',created: '+today+',Price change: '+datefrom+',PC number: '+pcUniqueNum;
var CSV = '';
//Set Report title in first row or line
CSV += ReportTitle + '\r\n';
CSV += "ITEM INTERNAL ID,ITEM,ITEM DISPLAY NAME,ITEM TYPE,SUPPLIER CURRENCY,CURRENT SUPPLIER CURRENCY PURCHASE PRICE,LAST PURCHASE PRICE NZD,SUPPLIER CURRENCY NEW PURCHASE PRICE,PERCENTAGE CHANGE,PO NOTE,PRICE LEVEL,QUANTITY BREAK,CUSTOMER PRICING,NEW PRICING,CURRENT GP%,GP% IF HTL ABSORBS\n"
var resultarray1 = JSON.parse(resultarray1)
//1st loop is to extract each row
for (var i = 0; i < resultarray1.length; i++) {
var row = "";
//2nd loop will extract each column and convert it in string comma-seprated
for (var index in resultarray1[i]) {
row += '"' + resultarray1[i][index] + '",';
}
row.slice(0, row.length - 1);
//add a line break after each row
CSV += row + '\r\n';
}
if (CSV == '') {
alert("Invalid data");
return;
}
//Initialize file format you want csv or xls
var uri = 'data:text/csv;charset=utf-8,' + escape(CSV);
var output = url.resolveScript({
scriptId: 'customscript_htl18_jj_sl_itemreportform',
deploymentId: 'customdeploy_htl18_jj_sl_itemreportform',
returnExternalUrl: false,
})+ '&recid='+recid
var response1 = https.post({
url: output,
body: JSON.stringify({content:CSV,sts:'dwnload1',supplier:suppIdDName})
});
}catch(err){
console.log('error @ button action 1',err)
}
}
function downloadButton2(){
try{
var record = currentRecord.get();
var recid=record.id;
var resultarray2 = record.getValue({
fieldId: 'csv_constants2'
});
var resultarray2_pars = JSON.parse(resultarray2)
var today = new Date();
var dd = today.getDate();
var mm = today.getMonth()+1;
var yyyy = today.getFullYear();
today = dd+'/'+mm+'/'+yyyy;
todayTitle = yyyy+''+mm+''+dd;
var suppId = record.getValue({
fieldId: 'supplier_id'
});
var datefrom = record.getText({
fieldId: 'custpage_price_chnage_reasearch'
});
var suppDtls = search.lookupFields({
type: search.Type.VENDOR,
id: suppId,
columns: ['custentity29','entityid','altname']
});
if (suppDtls.custentity29 != null) {
pcUniqueNum =suppDtls.custentity29;
}
if (suppDtls.entityid != null) {
suppIdNm =suppDtls.entityid;
}
if (suppDtls.altname != null) {
suppIdDName =suppDtls.altname;
}
var ReportTitle = suppIdNm+','+suppIdDName+',created: '+today+',Price change: '+datefrom+',PC number: '+pcUniqueNum;
var CSV = '';
//Set Report title in first row or line
CSV += ReportTitle + '\r\n';
CSV += "CUSTOMER INTERNAL ID,CUSTOMER ID ,COMPANY NAME,SP REVIEW DATE,ITEM INTERNAL ID,ITEM,DESCRIPTION,ITEM TYPE,QUANTITY SOLD,TOTAL SPEND, PROJECTED SPEND,CURRENT SUPPLIER CURRENCY PURCHASE PRICE,LAST PURCHASE PRICE NZD,SUPPLIER CURRENCY NEW PURCHASE PRICE,PERCENTAGE CHANGE,PO NOTE,PRICE LEVEL,QUANTITY BREAK,CUSTOMER PRICING,NEW PRICING,CURRENT GP%,GP% IF HTL ABSORBS\n"
//1st loop is to extract each row
for (var i = 0; i < resultarray2_pars.length; i++) {
var row = "";
//2nd loop will extract each column and convert it in string comma-seprated
for (var index in resultarray2_pars[i]) {
row += '"' + resultarray2_pars[i][index] + '",';
}
row.slice(0, row.length - 1);
//add a line break after each row
CSV += row + '\r\n';
}
if (CSV == '') {
alert("Invalid data");
return;
}
//Generate a file name
var fileName = '' + suppIdDName + ' ' + todayTitle+' Customer (inventory) Report';
//Initialize file format you want csv or xls
var uri = 'data:text/csv;charset=utf-8,' + escape(CSV);
var output = url.resolveScript({
scriptId: 'customscript_htl18_jj_sl_itemreportform',
deploymentId: 'customdeploy_htl18_jj_sl_itemreportform',
returnExternalUrl: false,
})+ '&recid='+recid
var response1 = https.post({
url: output,
body: JSON.stringify({content:CSV,sts:'dwnload2',supplier:suppIdDName})
});
}catch(err){
console.log('error @ button action 2',err)
}
}
function downloadButtonassm(){
try{
var record = currentRecord.get();
var recid=record.id;
var resultarray3= record.getValue({
fieldId: 'csv_constants3'
});
var resultarray3_pars = JSON.parse(resultarray3)
var today = new Date();
var dd = today.getDate();
var mm = today.getMonth()+1;
var yyyy = today.getFullYear();
today = dd+'/'+mm+'/'+yyyy;
todayTitle = yyyy+''+mm+''+dd;
var suppId = record.getValue({
fieldId: 'supplier_id'
});
var datefrom = record.getText({
fieldId: 'custpage_price_chnage_reasearch'
});
var suppDtls = search.lookupFields({
type: search.Type.VENDOR,
id: suppId,
columns: ['custentity29','entityid','altname']
});
if (suppDtls.custentity29 != null) {
pcUniqueNum =suppDtls.custentity29;
}
if (suppDtls.entityid != null) {
suppIdNm =suppDtls.entityid;
}
if (suppDtls.altname != null) {
suppIdDName =suppDtls.altname;
}
var ReportTitle = suppIdNm+','+suppIdDName+',created: '+today+',Price change: '+datefrom+',PC number: '+pcUniqueNum;
var CSV = '';
//Set Report title in first row or line
CSV += ReportTitle + '\r\n';
CSV += "ITEM INTERNAL ID,ITEM,ITEM DISPLAY NAME,ITEM TYPE,CURRENCY,LAST PURCHASE PRICE,NEW LAST PURCHASE PRICE,PERCENT CHANGE,PO NOTE,PRICE LEVEL,QUANTITY BREAK,CUSTOMER PRICING,NEW PRICING,CURRENT GP%,GP% IF HTL ABSORBS,ACTION\n"
//1st loop is to extract each row
for (var i = 0; i < resultarray3_pars.length; i++) {
var row = "";
//2nd loop will extract each column and convert it in string comma-seprated
for (var index in resultarray3_pars[i]) {
row += '"' + resultarray3_pars[i][index] + '",';
}
row.slice(0, row.length - 1);
//add a line break after each row
CSV += row + '\r\n';
}
if (CSV == '') {
alert("Invalid data");
return;
}
//Generate a file name
var fileName = '' + suppIdDName + ' ' + todayTitle+' Assembly Item Report';
//Initialize file format you want csv or xls
var uri = 'data:text/csv;charset=utf-8,' + escape(CSV);
var output = url.resolveScript({
scriptId: 'customscript_htl18_jj_sl_itemreportform',
deploymentId: 'customdeploy_htl18_jj_sl_itemreportform',
returnExternalUrl: false,
})+ '&recid='+recid
var response1 = https.post({
url: output,
body: JSON.stringify({content:CSV,sts:'dwnload3',supplier:suppIdDName})
});
}catch(err){
console.log('error @ button action assm3',err)
}
}
function downloadButton3(){
try{
var record = currentRecord.get();
var recid=record.id;
var resultarray4= record.getValue({
fieldId: 'csv_constants4'
});
var resultarray4_pars = JSON.parse(resultarray4)
var today = new Date();
var dd = today.getDate();
var mm = today.getMonth()+1;
var yyyy = today.getFullYear();
today = dd+'/'+mm+'/'+yyyy;
todayTitle = yyyy+''+mm+''+dd;
var suppId = record.getValue({
fieldId: 'supplier_id'
});
var datefrom = record.getText({
fieldId: 'custpage_price_chnage_reasearch'
});
var suppDtls = search.lookupFields({
type: search.Type.VENDOR,
id: suppId,
columns: ['custentity29','entityid','altname']
});
if (suppDtls.custentity29 != null) {
pcUniqueNum =suppDtls.custentity29;
}
if (suppDtls.entityid != null) {
suppIdNm =suppDtls.entityid;
}
if (suppDtls.altname != null) {
suppIdDName =suppDtls.altname;
}
//
var ReportTitle = suppIdNm+','+suppIdDName+',created: '+today+',Price change: '+datefrom+',PC number: '+pcUniqueNum;
var CSV = '';
//Set Report title in first row or line
CSV += ReportTitle + '\r\n';
CSV += "CUSTOMER INTERNAL ID,CUSTOMER ID ,COMPANY NAME,SP REVIEW DATE,ITEM INTERNAL ID,ITEM,DESCRIPTION,QUANTITY SOLD,TOTAL SPEND, PROJECTED SPEND,LAST PURCHASE PRICE,NEW LAST PURCHASE PRICE,PERCENT CHANGE,PO NOTE,QUANTITY BREAK,CUSTOMER PRICING,NEW PRICING,CURRENT GP%,GP% IF HTL ABSORBS,ACTION\n"
//1st loop is to extract each row
for (var i = 0; i < resultarray4_pars.length; i++) {
var row = "";
//2nd loop will extract each column and convert it in string comma-seprated
for (var index in resultarray4_pars[i]) {
row += '"' + resultarray4_pars[i][index] + '",';
}
row.slice(0, row.length - 1);
//add a line break after each row
CSV += row + '\r\n';
}
if (CSV == '') {
alert("Invalid data");
return;
}
//Generate a file name
var fileName = '' + suppIdDName + ' ' + todayTitle+' Customer (Assembly) Report';
//Initialize file format you want csv or xls
var uri = 'data:text/csv;charset=utf-8,' + escape(CSV);
var output = url.resolveScript({
scriptId: 'customscript_htl18_jj_sl_itemreportform',
deploymentId: 'customdeploy_htl18_jj_sl_itemreportform',
returnExternalUrl: false,
})+ '&recid='+recid
var response1 = https.post({
url: output,
body: JSON.stringify({content:CSV,sts:'dwnload4',supplier:suppIdDName})
});
}catch(err){
console.log('error @ button action 4',err)
}
}
function absorbChangeClearFields()
{
try {
var record = currentRecord.get();
var recid=record.id;
var pcUniqueNum = record.getValue({
fieldId: 'custentity29'
});
var output = url.resolveScript({
scriptId: 'customscripthtl_18_absb_clear_fields',
deploymentId: 'customdeploy_htl_18_sl_absb_clear_field',
returnExternalUrl: false,
})+'&pcUniqueNum='+pcUniqueNum;
var response=https.get({
url: output
});
var obj = JSON.parse(response.body);
if(obj.id != null){
alert('completed the HTL to Absorb Change Clear fields');
}
} catch (e) {
console.log(e)
}
}
return {
pageInit: pageInit,
priceChangeResearch: priceChangeResearch,
absorbChangeClearFields: absorbChangeClearFields,
downloadButton1: downloadButton1,
downloadButton2: downloadButton2,
downloadButtonassm:downloadButtonassm,
downloadButton3:downloadButton3
};
});Suitelet Script: HTL-18 JJ SL Item Report Form
/**
* @NApiVersion 2.x
* @NScriptType Suitelet
* @NModuleScope SameAccount
*/
/*******************************************************************************
* CLIENTNAME:HTL
* HTL-18
* Popup to collect the date search and calculations
* **************************************************************************
* Date : 19-11-2018
*
* Author: Jobin & Jismi IT Services LLP
* Script Description : This script is to create a form with 4 buttons each for attaching the inventory item report, customer inventory item report, assembly item report, customer assembly item report of each supplier to the current user
* Date created : 19-11-2018
*
* REVISION HISTORY
*
* Revision 1.0 ${date} nd : created
*
*
******************************************************************************/
define(['N/ui/serverWidget','N/search','N/file','N/email'],
function(serverWidget,search,file,email) {
/**
* Definition of the Suitelet script trigger point.
*
* @param {Object} context
* @param {ServerRequest} context.request - Encapsulation of the incoming request
* @param {ServerResponse} context.response - Encapsulation of the Suitelet response
* @Since 2015.2
*/
function onRequest(context) {
try{
if (context.request.method == 'GET'){
var suppId = context.request.parameters.recid;
var suppDtls = search.lookupFields({
type: search.Type.VENDOR,
id: suppId,
columns: ['custentity29','entityid','altname']
});
if (suppDtls.currency != null && suppDtls.currency != "" &&suppDtls.currency != undefined) {
var currency =suppDtls.currency[0].text;
}
if (suppDtls.custentity29 != null) {
var pcUniqueNum =suppDtls.custentity29;
}
if (suppDtls.altname != null) {
var suppIdDName =suppDtls.altname;
}
var form = serverWidget.createForm({
title: 'Report'
});
var CLIENT_SCRIPT_FILE_ID = 686180;
form.clientScriptFileId = CLIENT_SCRIPT_FILE_ID;
// sublist for inventory item report
var field = form.addField({
id: 'custpage_price_chnage_reasearch',
type: serverWidget.FieldType.DATE,
label: 'What date is the price change?'
});
var suppID = form.addField({
id: 'supplier_id',
type: serverWidget.FieldType.TEXT,
label: 'Supplier id'
});
var CSV_titleField1 = form.addField({
id: 'csv_constants1',
type: serverWidget.FieldType.INLINEHTML,
label: 'CSV_Contents1'
});
CSV_titleField1.updateDisplayType({
displayType: serverWidget.FieldDisplayType.HIDDEN
});
var CSV_titleField2 = form.addField({
id: 'csv_constants2',
type: serverWidget.FieldType.INLINEHTML,
label: 'CSV_Contents2'
});
CSV_titleField2.updateDisplayType({
displayType: serverWidget.FieldDisplayType.HIDDEN
});
var CSV_titleField3 = form.addField({
id: 'csv_constants3',
type: serverWidget.FieldType.INLINEHTML,
label: 'CSV_Contents3'
});
CSV_titleField3.updateDisplayType({
displayType: serverWidget.FieldDisplayType.HIDDEN
});
var CSV_titleField4 = form.addField({
id: 'csv_constants4',
type: serverWidget.FieldType.INLINEHTML,
label: 'CSV_Contents4'
});
CSV_titleField4.updateDisplayType({
displayType: serverWidget.FieldDisplayType.HIDDEN
});
suppID.updateDisplayType({
displayType: serverWidget.FieldDisplayType.HIDDEN
});
suppID.defaultValue = suppId;
form.addButton({
id : 'buttonId1',
label : 'Inventory Item Report',
functionName:'downloadButton1'
});
form.addButton({
id : 'buttonId2',
label : 'Customer-Inventory Report',
functionName:'downloadButton2'
});
form.addButton({
id : 'buttonIdassm',
label : 'Assembly Item Report',
functionName:'downloadButtonassm'
});
form.addButton({
id : 'buttonId3',
label : 'Customer-Assembly Report',
functionName:'downloadButton3'
});
//search result from item search
var itemArray=[]
var itemArray_asm=[]
var BASE_PRICE = {};
var obj={}
var return_itemsearch = itemSearchInv(pcUniqueNum,suppId);
var search_item = return_itemsearch[0];
var search_colum = return_itemsearch[1];
var itemInv_array =[];
for (var i = 0; i < search_item.length; i++) {
var CSV_itemInventory={}
var itemId = search_item[i].getValue({
name: "internalid"
});
itemArray.push(itemId)
var itemName = search_item[i].getValue({
name: "itemid",
sort: search.Sort.ASC,
});
var dispName = search_item[i].getValue({
name: "displayname"
});
var type = search_item[i].getValue({
name: "type"
});
var supplier_currency = search_item[i].getText({
name: "currency",
join: "vendor"
});
var CurrentSupplier_cpp = search_item[i].getValue({
name: "cost"
});//CURRENT SUPPLIER CURRENCY PURCHASE PRICE
var lastPurchasePrice = search_item[i].getValue({
name: "lastpurchaseprice"
});
var newPurchasePrice = search_item[i].getValue({
name: "custitem18"
});//supplier currency new purchase price
var percentageChange = search_item[i].getValue({
name: "custitem19"
});
var poNote = search_item[i].getValue({
name: "custitem_ponote"
});
var priceLevel = search_item[i].getText({
name: "pricelevel",
join: "pricing"
});
var priceLevel_val = search_item[i].getValue({
name: "pricelevel",
join: "pricing"
});
var qtyBrk = search_item[i].getValue({
name: "minimumquantity",
sort: search.Sort.ASC,
join: "pricing"
});
var customer_pricing = search_item[i].getValue({
name: "unitprice",
join: "pricing"
});
var new_pric = (parseFloat(customer_pricing)*(100+parseFloat(percentageChange))/100)//updated on Dec 17 2018
var new_pricing = parseFloat(new_pric).toFixed(2)
var curr_GP = (parseFloat(customer_pricing)-parseFloat(lastPurchasePrice))/(parseFloat(customer_pricing)/100)
var current_GP = parseFloat(curr_GP).toFixed(2)
var curr_GP_htl= (parseFloat(customer_pricing)-(parseFloat(lastPurchasePrice)*(100+parseFloat(percentageChange))/100))/(parseFloat(customer_pricing)/100)
var current_GP_htl= parseFloat(curr_GP_htl).toFixed(2)
if(priceLevel=='Base Price'){
if(BASE_PRICE[itemId]){
BASE_PRICE[itemId].push({
id:itemId,
currentSup:CurrentSupplier_cpp,
quantityB:qtyBrk,
custPrice:customer_pricing,
newPrice:new_pricing,
currentGP:current_GP,
currentGPhtl:current_GP_htl
});
}else{
BASE_PRICE[itemId]=[{
id:itemId,
currentSup:CurrentSupplier_cpp,
quantityB:qtyBrk,
custPrice:customer_pricing,
newPrice:new_pricing,
currentGP:current_GP,
currentGPhtl:current_GP_htl
}];
}
}
//object to be passed to search for customer report
if(obj[itemId+'_'+priceLevel])
obj[itemId+'_'+priceLevel].push({
currentSup:CurrentSupplier_cpp,
priceL:priceLevel,
id:itemId,
quantityB:qtyBrk,
custPrice:customer_pricing,
newPrice:new_pricing,
currentGP:current_GP,
currentGPhtl:current_GP_htl
})
else
obj[itemId+'_'+priceLevel] = [{
currentSup:CurrentSupplier_cpp,
priceL:priceLevel,
id:itemId,
quantityB:qtyBrk,
custPrice:customer_pricing,
newPrice:new_pricing,
currentGP:current_GP,
currentGPhtl:current_GP_htl
}]
CSV_itemInventory["ITEM INTERNAL ID "] = checkif(itemId);
CSV_itemInventory["ITEM"] = checkif(itemName);
CSV_itemInventory["ITEM DISPLAY NAME"] = checkif(dispName);
CSV_itemInventory["ITEM TYPE"] = checkif(type);
CSV_itemInventory["SUPPLIER CURRENCY"] = checkif(supplier_currency);
CSV_itemInventory["CURRENT SUPPLIER CURRENCY PURCHASE PRICE"] = checkif(CurrentSupplier_cpp);
CSV_itemInventory["LAST PURCHASE PRICE NZD"] = checkif(lastPurchasePrice);
CSV_itemInventory["SUPPLIER CURRENCY NEW PURCHASE PRICE"] = checkif(newPurchasePrice);
CSV_itemInventory["PERCENTAGE CHANGE"] = checkif(percentageChange);
CSV_itemInventory["PO NOTE"] = checkif(poNote);
CSV_itemInventory["PRICE LEVEL"] = checkif(priceLevel);
CSV_itemInventory["QUANTITY BREAK"] = checkif(qtyBrk);
CSV_itemInventory["CUSTOMER PRICING"] = checkif(customer_pricing);
CSV_itemInventory["NEW PRICING"] = checkif(new_pricing);
CSV_itemInventory["CURRENT GP%"] = checkif(current_GP);
CSV_itemInventory["GP% IF HTL ABSORBS"] = checkif(current_GP_htl);
itemInv_array.push(CSV_itemInventory)
}
CSV_titleField1.defaultValue = JSON.stringify(itemInv_array);
//search result for assembly report
var itemSearchAssm_result = itemSearchAssm(itemArray,pcUniqueNum,suppId);
var search_item_assm = itemSearchAssm_result[0];
var search_col_assm = itemSearchAssm_result[1];
var itemAssm_array = [];//array to store the download object
for (var i = 0; i < search_item_assm.length; i++) {
var CSV_itemAssembly={};//object to store download data
var itemId = search_item_assm[i].getValue({
name: "internalid"
});
itemArray_asm.push(itemId)
var itemName = search_item_assm[i].getValue({
name: "itemid",
sort: search.Sort.ASC,
});
var dispName = search_item_assm[i].getValue({
name: "displayname"
});
var type = search_item_assm[i].getValue({
name: "type"
});
var CurrentSupplier_cpp = search_item_assm[i].getValue({
name: "cost"
});//CURRENT SUPPLIER CURRENCY PURCHASE PRICE
var lastPurchasePrice = search_item_assm[i].getValue({
name: "custitem34"
});
var newPurchasePrice = search_item_assm[i].getValue({
name: "custitem35"
});//supplier currency new purchase price
var percentageChange = search_item_assm[i].getValue({
name: "custitem19"
});
var poNote = search_item_assm[i].getValue({
name: "custitem_ponote"
});
var priceLevel = search_item_assm[i].getText({
name: "pricelevel",
join: "pricing"
});
var qtyBrk = search_item_assm[i].getValue({
name: "minimumquantity",
sort: search.Sort.ASC,
join: "pricing"
});
var customer_pricing = search_item_assm[i].getValue({
name: "unitprice",
join: "pricing"
});
var new_pric = (parseFloat(customer_pricing)*(100+parseFloat(percentageChange))/100)//updated on 18 Dec,2018
var new_pricing = parseFloat(new_pric).toFixed(2)
var curr_GP =(parseFloat(customer_pricing)-parseFloat(lastPurchasePrice))/(parseFloat(customer_pricing)/100)//updated on 18 Dec,2018
var current_GP = parseFloat(curr_GP).toFixed(2)
var curr_GP_htl = (parseFloat(customer_pricing)-parseFloat(newPurchasePrice))/(parseFloat(customer_pricing)/100)
var current_GP_htl= parseFloat(curr_GP_htl).toFixed(2)
//object to be passed to search for customer report
if(priceLevel=='Base Price'){
if(BASE_PRICE[itemId]){
BASE_PRICE[itemId].push({
id:itemId,
quantityB:qtyBrk,
custPrice:customer_pricing,
newPrice:new_pricing,
currentGP:current_GP,
currentGPhtl:current_GP_htl
});
}else{
BASE_PRICE[itemId]=[{
id:itemId,
quantityB:qtyBrk,
custPrice:customer_pricing,
newPrice:new_pricing,
currentGP:current_GP,
currentGPhtl:current_GP_htl
}];
}
}
if(obj[itemId+'_'+priceLevel])
obj[itemId+'_'+priceLevel].push({
currentSup:CurrentSupplier_cpp,
priceL:priceLevel,
id:itemId,
quantityB:qtyBrk,
custPrice:customer_pricing,
newPrice:new_pricing,
currentGP:current_GP,
currentGPhtl:current_GP_htl
})
else
obj[itemId+'_'+priceLevel] = [{
currentSup:CurrentSupplier_cpp,
priceL:priceLevel,
id:itemId,
quantityB:qtyBrk,
custPrice:customer_pricing,
newPrice:new_pricing,
currentGP:current_GP,
currentGPhtl:current_GP_htl
}]
CSV_itemAssembly["ITEM INTERNAL ID "] = checkif(itemId);
CSV_itemAssembly["ITEM"] = checkif(itemName);
CSV_itemAssembly["ITEM DISPLAY NAME"] = checkif(dispName);
CSV_itemAssembly["ITEM TYPE"] = checkif(type);
CSV_itemAssembly["CURRENCY"] = 'NZD';
CSV_itemAssembly["LAST PURCHASE PRICE NZD"] = checkif(lastPurchasePrice);
CSV_itemAssembly["NEW LAST PURCHASE PRICE"] = checkif(newPurchasePrice);
CSV_itemAssembly["PERCENT CHANGE"] = checkif(percentageChange);
CSV_itemAssembly["PO NOTE"] = checkif(poNote);
CSV_itemAssembly["PRICE LEVEL"] = checkif(priceLevel);
CSV_itemAssembly["QUANTITY BREAK"] = checkif(qtyBrk);
CSV_itemAssembly["CUSTOMER PRICING"] = checkif(customer_pricing);
CSV_itemAssembly["NEW PRICING"] = checkif(new_pricing);
CSV_itemAssembly["CURRENT GP%"] = checkif(current_GP);
CSV_itemAssembly["GP% IF HTL ABSORBS"] = checkif(current_GP_htl);
// CSV_itemAssembly["ACTION"] =
itemAssm_array.push(CSV_itemAssembly)
}
CSV_titleField3.defaultValue = JSON.stringify(itemAssm_array);
//search result of search for customer report
var priceResult_inv = itemPricLvlSearch(itemArray)
var customerSearch_result_inv = customerSearch_inv(priceResult_inv,itemArray,obj,CSV_titleField2,BASE_PRICE);
var priceResult_assm = itemPricLvlSearch(itemArray_asm)
var customerSearch_result_assm= customerSearch_assm(priceResult_assm,itemArray_asm,obj,CSV_titleField4,BASE_PRICE)
context.response.writePage(form);
}
else{
var downloadButton =JSON.parse(context.request.body);
var button=downloadButton.sts;
var sup = downloadButton.supplier
if(button == 'dwnload1'){
var csv_1 = downloadButton.content;
var fileObj1 = file.create({
name: 'inventory item.csv',
fileType: file.Type.CSV,
contents:csv_1 ,
description: 'This is a plain text file.',
encoding: file.Encoding.UTF8,
isOnline: true
});
email.send({
author: -5,
recipients: 'devgorio@gmail.com',
subject: 'Inventory Item report',
body: 'The attached is the inventory item report of supplier '+sup,
attachments: [fileObj1]
});
}
else if(button == 'dwnload2'){
var csv_2 = downloadButton.content;
var fileObj2 = file.create({
name: 'customer inventory.csv',
fileType: file.Type.CSV,
contents:csv_2 ,
description: 'This is a plain text file.',
encoding: file.Encoding.UTF8,
isOnline: true
});
email.send({
author: -5,
recipients: 'devgorio@gmail.com',
subject: 'Customer-Inventory Item report',
body: 'The attached is the customer-inventory item report of supplier '+sup,
attachments: [fileObj2]
});
}
else if(button == 'dwnload3'){
var csv_3 = downloadButton.content;
var fileObj3 = file.create({
name: 'Assembly.csv',
fileType: file.Type.CSV,
contents:csv_3 ,
description: 'This is a plain text file.',
encoding: file.Encoding.UTF8,
isOnline: true
});
email.send({
author: -5,
recipients: 'devgorio@gmail.com',
subject: 'Assembly Item report',
body: 'The attached is the assembly item report of supplier '+sup,
attachments: [fileObj3]
});
}
else if(button == 'dwnload4'){
var csv_4 = downloadButton.content;
var fileObj4 = file.create({
name: 'Customer-Assembly.csv',
fileType: file.Type.CSV,
contents:csv_4 ,
description: 'This is a plain text file.',
encoding: file.Encoding.UTF8,
isOnline: true
});
email.send({
author: -5,
recipients: 'devgorio@gmail.com',
subject: 'Customer-Assembly Item report',
body: 'The attached is the customer-assembly item report of supplier '+sup,
attachments: [fileObj4]
});
}
}
}catch(er){
log.error('error@main function',er)
}
}
function itemSearchInv(pcUniqueNum,suppId){
try{
var part_1 = "formulanumeric: CASE WHEN {custitem17} = '" + pcUniqueNum + "' THEN 1 ELSE 0 END";
var part_2 = part_1.toString();
var itemSearchObj = search.create({
type: "item",
filters: [
[part_2, "equalto", "1"],
"AND",
["type", "anyof", "InvtPart", "Assembly"],
"AND",
["custitem18","isnotempty",""],
"AND",
["othervendor","anyof",suppId],
"AND",
["isinactive","is","F"]//updated on 29-01-19 as per client request to remove inactive items
],
columns: [
search.createColumn({ name: "internalid", label: "Internal ID" }),
search.createColumn({
name: "itemid",
sort: search.Sort.ASC,
label: "Name"
}),
search.createColumn({ name: "displayname", label: "Display Name" }),
search.createColumn({ name: "type", label: "Type" }),
search.createColumn({name: "cost", label: "Purchase Price"}),
search.createColumn({
name: "currency",
join: "vendor",
label: "Currency"
}),
search.createColumn({ name: "lastpurchaseprice", label: "Last Purchase Price" }),
search.createColumn({ name: "custitem18", label: "Price Change Purchase Price (Keep in original currency)" }),
search.createColumn({name: "custitem19", label: "Price Change $ as %"}),
search.createColumn({ name: "custitem_ponote", label: "PO Note" }),
search.createColumn({
name: "pricelevel",
join: "pricing",
sort: search.Sort.ASC,
label: "Price Level"
}),
search.createColumn({
name: "minimumquantity",
join: "pricing",
label: "quantity break"
}),
search.createColumn({
name: "unitprice",
join: "pricing",
label: "customer pricing"
})
]
});
var itemSearchObj_col_inv = itemSearchObj.columns;
var searchResultCount = itemSearchObj.runPaged().count;
// to get search result more than 1000
var start = 0;
var end = 1000;
var resultarray = [];
var result;
var singleresult;
for (var i = 0; i < Math.ceil(searchResultCount / 1000); i++)
{
result = itemSearchObj.run().getRange({
start: start,
end: end
});
for (var j = 0; j < result.length; j++) {
singleresult = result[j];
resultarray.push(singleresult);
}
start = end;
end = end + 1000;
}
return [resultarray,itemSearchObj_col_inv]
}catch(err){
log.error('error@ itemInv search',err)
}
}
function itemSearchAssm(itemArray,pcUniqueNum,suppId){
try{
var part_1 = "formulanumeric: CASE WHEN {memberitem.custitem17} = '" + pcUniqueNum + "' THEN 1 ELSE 0 END";
var part_2 = part_1.toString();
var itemSearchObj = search.create({
type: "item",
filters: [
["type","anyof","Assembly"],
"AND",
["memberitem.internalid","anyof",itemArray],
"AND",
["memberitem.type","anyof","InvtPart"],
"AND",
[part_2, "equalto", "1"],
"AND",
["memberitem.othervendor","anyof",suppId],
"AND",
["isinactive","is","F"]//updated on 29-01-19 as per client request to remove inactive items
],
columns: [
search.createColumn({ name: "internalid", label: "Internal ID" }),
search.createColumn({
name: "itemid",
sort: search.Sort.ASC,
label: "Name"
}),
search.createColumn({ name: "displayname", label: "Display Name" }),
search.createColumn({ name: "type", label: "Type" }),
search.createColumn({name: "cost", label: "Purchase Price"}),
search.createColumn({ name: "lastpurchaseprice", label: "Last Purchase Price" }),
search.createColumn({ name: "custitem18", label: "Price Change Purchase Price (Keep in original currency)" }),
search.createColumn({name: "custitem19", label: "Price Change $ as %"}),
search.createColumn({ name: "custitem_ponote", label: "PO Note" }),
search.createColumn({
name: "pricelevel",
join: "pricing",
sort: search.Sort.ASC,
label: "Price Level"
}),
search.createColumn({
name: "minimumquantity",
join: "pricing",
label: "quantity break"
}),
search.createColumn({
name: "unitprice",
join: "pricing",
label: "customer pricing"
}),
search.createColumn({
name: "custitem34",
label: "Current Purchase Price"
}),
search.createColumn({
name: "custitem35",
label: "New Current Purchase Price"
})
]
});
var itemSearchObj_col_assm = itemSearchObj.columns;
var searchResultCount_assm= itemSearchObj.runPaged().count;
// to get search result more than 1000
var start = 0;
var end = 1000;
var resultarray_assm = [];
var result;
var singleresult;
for (var i = 0; i < Math.ceil(searchResultCount_assm / 1000); i++)
{
result = itemSearchObj.run().getRange({
start: start,
end: end
});
for (var j = 0; j < result.length; j++) {
singleresult = result[j];
resultarray_assm.push(singleresult);
}
start = end;
end = end + 1000;
}
return [resultarray_assm,itemSearchObj_col_assm];
}catch(err){
log.error('error @ itemAssm search',err)
}
}
//search for item pricing level fromt transaction search
function itemPricLvlSearch(itemArray_assm){
try{
var pricelevel_arr ={}
var salesorderSearchObj = search.create({
type: "salesorder",
filters:
[
["type","anyof","SalesOrd"],
"AND",
["item.internalid","anyof",itemArray_assm],
"AND",
["trandate","within","previousoneyear"]
],
columns:
[
search.createColumn({
name: "quantity",
summary: "SUM",
sort: search.Sort.ASC,
label: "Quantity"
}),
search.createColumn({
name: "itempricinglevel",
join: "customer",
summary: "GROUP",
label: "Item Pricing Level"
}),
search.createColumn({
name: "internalid",
join: "customer",
summary: "GROUP",
label: "Internal ID"
})
]
});
var search1 = salesorderSearchObj.run().getRange({
start: 0,
end: 1000
});
for (var i = 0; i < search1.length; i++) {
var itemPrcLvl = search1[i].getText({
name: "itempricinglevel",
join: "customer",
summary: "GROUP"
});
var custId1 = search1[i].getText({
name: "internalid",
join: "customer",
summary: "GROUP"
});
if(pricelevel_arr[custId1])
pricelevel_arr[custId1].push({
idCust:custId1,
itemPrc:itemPrcLvl
});
else
pricelevel_arr[custId1]=[{
idCust:custId1,
itemPrc:itemPrcLvl
}];
}
return pricelevel_arr;
}catch(err){
log.error('error @ itemPricLvlSearch',err)
}
}
// search for inventory customer report
function customerSearch_inv(priceResult_inv,itemArray,obj,CSV_titleField2,BASE_PRICE){
try{
var itemId_array=[];
var salesorderSearchObj = search.create({
type: "salesorder",
filters: [
["type", "anyof", "SalesOrd"],
"AND",
["item.internalid", "anyof",itemArray],
"AND",
["trandate","within","previousoneyear"]
],
columns: [
search.createColumn({
name: "internalid",
join: "item",
summary: "GROUP",
label: "Internal ID"
}),
search.createColumn({
name: "internalid",
join: "customer",
summary: "GROUP",
label: "Internal ID"
}),
search.createColumn({
name: "custbody_custid",
summary: "GROUP",
sort: search.Sort.ASC,
label: "Customer ID"
}),
search.createColumn({
name: "companyname",
join: "customer",
summary: "GROUP",
label: "Company Name"
}),
search.createColumn({
name: "custentity16",
join: "customer",
summary: "GROUP",
label: "Special Pricing Review Date"
}),
search.createColumn({
name: "item",
summary: "GROUP",
sort: search.Sort.ASC,
label: "Item"
}),
search.createColumn({
name: "displayname",
join: "item",
summary: "GROUP",
label: "Display Name"
}),
search.createColumn({
name: "type",
join: "item",
summary: "GROUP",
label: "Type"
}),
search.createColumn({
name: "quantity",
summary: "SUM",
label: "Quantity"
}),
search.createColumn({
name: "amount",
summary: "SUM",
label: "Amount"
}),
search.createColumn({
name: "lastpurchaseprice",
join: "item",
summary: "MAX",
label: "Last Purchase Price"
}),
search.createColumn({
name: "pricelevel",
join: "customer",
summary: "GROUP",
label: "Price Level"
}),
search.createColumn({
name: "custitem19",
join: "item",
summary: "MAX",
label: "Price Change $ as %"
}),
search.createColumn({
name: "custitem18",
join: "item",
summary: "MAX",
label: "Price Change Purchase Price (Keep in original currency)"
}),
search.createColumn({
name: "custitem_ponote",
join: "item",
summary: "GROUP",
label: "PO Note"
})
]
});
var cust_searchResultcount= salesorderSearchObj.runPaged().count;
// to get search result more than 1000
var start = 0;
var end = 1000;
var cust_searchResult = [];
var result;
var singleresult;
for (var i = 0; i < Math.ceil(cust_searchResultcount / 1000); i++)
{
result = salesorderSearchObj.run().getRange({
start: start,
end: end
});
for (var j = 0; j < result.length; j++) {
singleresult = result[j];
cust_searchResult.push(singleresult);
}
start = end;
end = end + 1000;
}
var customerInv_array =[];
var toContinue=true;
for (var l=0,j = 0; j<cust_searchResult.length; j++) {
var CSV_CustomerInv= {};
// getting values from the search
var custInternalId = cust_searchResult[j].getValue({
name: "internalid",
join: "customer",
summary: "GROUP"
});
var custId = cust_searchResult[j].getValue({
name: "custbody_custid",
sort: search.Sort.ASC,
summary: "GROUP"
});
var companyname = cust_searchResult[j].getValue({
name: "companyname",
join: "customer",
summary: "GROUP"
});
var date = cust_searchResult[j].getValue({
name: "custentity16",
join: "customer",
summary: "GROUP"
});
var itemId = cust_searchResult[j].getValue({
name: "internalid",
join: "item",
summary: "GROUP"
});
itemId_array.push(itemId);
var item = cust_searchResult[j].getText({
name: "item",
sort: search.Sort.ASC,
summary: "GROUP"
});
var description = cust_searchResult[j].getValue({
name: "displayname",
join: "item",
summary: "GROUP"
});
var type = cust_searchResult[j].getValue({
name: "type",
join: "item",
summary: "GROUP"
});
var qty_sold = cust_searchResult[j].getValue({
name: "quantity",
summary: "SUM"
});
var total_spnd = cust_searchResult[j].getValue({
name: "amount",
summary: "SUM"
});
var last_purchp2 = cust_searchResult[j].getValue({
name: "lastpurchaseprice",
join: "item",
summary: "MAX"
});
var supp_currencynewpp2 = cust_searchResult[j].getValue({
name: "custitem18",
join: "item",
summary: "MAX"
});
var percentageChange2 = cust_searchResult[j].getValue({
name: "custitem19",
join: "item",
summary: "MAX"
});
var curr_supp_cpp2 = parseFloat(total_spnd)*(100+parseFloat(percentageChange2))
var current_supp_cpp2 = parseFloat(curr_supp_cpp2).toFixed(2)
var proj_spnd = parseFloat(total_spnd)*((100+parseFloat(percentageChange2))/100)
var projected_spnd = parseFloat(proj_spnd).toFixed(2)
var po_note2 = cust_searchResult[j].getValue({
name: "custitem_ponote",
join: "item",
summary: "GROUP"
});
if(priceResult_inv[custInternalId]){
var priceLvl2 = priceResult_inv[custInternalId][0]['itemPrc']
}
if(priceLvl2 == "" || priceLvl2 == null || priceLvl2 == undefined || priceLvl2 =='- None -' || priceLvl2 == " "){
priceLvl2= 'Base Price'
}
var qtyBrkfn
var currentSupFn
var custPriceFn
var newPriceFn
var currentGPFn
var currentGPhtlFn
if(obj[itemId+'_'+priceLvl2]){
obj[itemId+'_'+priceLvl2].forEach(function(eachValue){
qtyBrkfn = eachValue.quantityB
currentSupFn =eachValue.currentSup
custPriceFn = eachValue.custPrice
newPriceFn = eachValue.newPrice
currentGPFn = eachValue.currentGP
currentGPhtlFn = eachValue.currentGPhtl
CSV_CustomerInv["CUSTOMER INTERNAL ID"] = checkif(custInternalId);
CSV_CustomerInv["CUSTOMER ID"] = checkif(custId);
CSV_CustomerInv["COMPANY NAME"] = checkif(companyname);
CSV_CustomerInv["SP REVIEW DATE"] = checkif(date);
CSV_CustomerInv["ITEM INTERNAL ID "] = checkif(itemId);
CSV_CustomerInv["ITEM"] = checkif(item);
CSV_CustomerInv["DESCRIPTION"] = checkif(description);
CSV_CustomerInv["ITEM TYPE"] = checkif(type);
if (parseInt(qty_sold) < parseInt(eachValue.quantityB)){
CSV_CustomerInv["QUANTITY SOLD"] = checkif(null);
CSV_CustomerInv["TOTAL SPEND"] = checkif(null);
}else{
CSV_CustomerInv["QUANTITY SOLD"] = checkif(qty_sold);
CSV_CustomerInv["TOTAL SPEND"] = checkif(total_spnd);
}
CSV_CustomerInv["PROJECTED SPEND"] = checkif(projected_spnd);
CSV_CustomerInv["CURRENT SUPPLIER CURRENCY PURCHASE PRICE"] = checkif(eachValue.currentSup);
CSV_CustomerInv["LAST PURCHASE PRICE NZD"] = checkif(last_purchp2);
CSV_CustomerInv["SUPPLIER CURRENCY NEW PURCHASE PRICE"] = checkif(supp_currencynewpp2);
CSV_CustomerInv["PERCENTAGE CHANGE"] = checkif(percentageChange2);
CSV_CustomerInv["PO NOTE"] = checkif(po_note2);
CSV_CustomerInv["PRICE LEVEL"] = checkif(priceLvl2);
CSV_CustomerInv["QUANTITY BREAK"] = checkif(eachValue.quantityB);
CSV_CustomerInv["CUSTOMER PRICING"] = checkif(eachValue.custPrice);
CSV_CustomerInv["NEW PRICING"] = checkif(eachValue.newPrice);
CSV_CustomerInv["CURRENT GP%"] = checkif(eachValue.currentGP);
CSV_CustomerInv["GP% IF HTL ABSORBS"] = checkif(eachValue.currentGPhtl);
customerInv_array.push(CSV_CustomerInv)
l++;
qtyBrkfn='';
CSV_CustomerInv = undefined;
CSV_CustomerInv = {};
});
}
else{
if(!BASE_PRICE[itemId])
continue;
BASE_PRICE[itemId].forEach(function(eachValue){
qtyBrkfn = eachValue.quantityB
currentSupFn = eachValue.currentSup
custPriceFn = eachValue.custPrice
newPriceFn = eachValue.newPrice
currentGPFn = eachValue.currentGP
currentGPhtlFn =eachValue.currentGPhtl
CSV_CustomerInv["CUSTOMER INTERNAL ID"] = checkif(custInternalId);
CSV_CustomerInv["CUSTOMER ID"] = checkif(custId);
CSV_CustomerInv["COMPANY NAME"] = checkif(companyname);
CSV_CustomerInv["SP REVIEW DATE"] = checkif(date);
CSV_CustomerInv["ITEM INTERNAL ID"] = checkif(itemId);
CSV_CustomerInv["ITEM"] = checkif();
CSV_CustomerInv["DESCRIPTION"] = checkif(description);
CSV_CustomerInv["ITEM TYPE"] = checkif(type);
if (parseInt(qty_sold) < parseInt(qtyBrkfn)){
CSV_CustomerInv["QUANTITY SOLD"] = checkif(null);
CSV_CustomerInv["TOTAL SPEND"] = checkif(null);
}else{
CSV_CustomerInv["QUANTITY SOLD"] = checkif(qty_sold);
CSV_CustomerInv["TOTAL SPEND"] = checkif(total_spnd);
}
CSV_CustomerInv["PROJECTED SPEND"] = checkif(projected_spnd);
CSV_CustomerInv["CURRENT SUPPLIER CURRENCY PURCHASE PRICE"] = checkif(currentSupFn);
CSV_CustomerInv["LAST PURCHASE PRICE NZD"] = checkif(last_purchp2);
CSV_CustomerInv["SUPPLIER CURRENCY NEW PURCHASE PRICE"] = checkif(supp_currencynewpp2);
CSV_CustomerInv["PERCENTAGE CHANGE"] = checkif(percentageChange2);
CSV_CustomerInv["PO NOTE"] = checkif(po_note2);
CSV_CustomerInv["PRICE LEVEL"] = checkif(priceLvl2);
CSV_CustomerInv["QUANTITY BREAK"] = checkif(qtyBrkfn);
CSV_CustomerInv["CUSTOMER PRICING"] = checkif(custPriceFn);
CSV_CustomerInv["NEW PRICING"] = checkif(newPriceFn);
CSV_CustomerInv["CURRENT GP%"] = checkif(currentGPFn);
CSV_CustomerInv["GP% IF HTL ABSORBS"] = checkif(currentGPhtlFn);
customerInv_array.push(CSV_CustomerInv)
l++;
qtyBrkfn='';
CSV_CustomerInv = undefined;
CSV_CustomerInv = {};
});
}
qtyBrkfn='';
CSV_CustomerInv = undefined;
CSV_CustomerInv = {};
}
CSV_titleField2.defaultValue = JSON.stringify(customerInv_array);
}catch(err){
log.error('error@customer_inv search',err)
}
return;
}
function customerSearch_assm(priceResult_assm,itemArray_assm,obj,CSV_titleField4,BASE_PRICE){
try{
var itemId_array=[];
var salesorderSearchObj = search.create({
type: "salesorder",
filters: [
["type", "anyof", "SalesOrd"],
"AND",
["item.internalid", "anyof", itemArray_assm],
"AND",
["trandate","within","previousoneyear"]
],
columns: [
search.createColumn({
name: "internalid",
join: "item",
summary: "GROUP",
label: "Internal ID"
}),
search.createColumn({
name: "internalid",
join: "customer",
summary: "GROUP",
label: "Internal ID"
}),
search.createColumn({
name: "custbody_custid",
summary: "GROUP",
sort: search.Sort.ASC,
label: "Customer ID"
}),
search.createColumn({
name: "companyname",
join: "customer",
summary: "GROUP",
label: "Company Name"
}),
search.createColumn({
name: "custentity16",
join: "customer",
summary: "GROUP",
label: "Special Pricing Review Date"
}),
search.createColumn({
name: "item",
summary: "GROUP",
sort: search.Sort.ASC,
label: "Item"
}),
search.createColumn({
name: "displayname",
join: "item",
summary: "GROUP",
label: "Display Name"
}),
search.createColumn({
name: "type",
join: "item",
summary: "GROUP",
label: "Type"
}),
search.createColumn({
name: "quantity",
summary: "SUM",
label: "Quantity"
}),
search.createColumn({
name: "amount",
summary: "SUM",
label: "Amount"
}),
search.createColumn({
name: "lastpurchaseprice",
join: "item",
summary: "MAX",
label: "Last Purchase Price"
}),
search.createColumn({
name: "pricelevel",
join: "customer",
summary: "GROUP",
label: "Price Level"
}),
search.createColumn({
name: "custitem19",
join: "item",
summary: "MAX",
label: "Price Change $ as %"
}),
search.createColumn({
name: "custitem18",
join: "item",
summary: "MAX",
label: "Price Change Purchase Price (Keep in original currency)"
}),
search.createColumn({
name: "custitem_ponote",
join: "item",
summary: "GROUP",
label: "PO Note"
}),
search.createColumn({
name: "formulanumeric",
summary: "MAX",
formula: "ROUND({amount}+({amount}*{item.custitem19}),2)",
label: "Formula (Numeric)"
}),
search.createColumn({
name: "custitem34",
join: "item",
summary: "MAX",
label: "Current Purchase Price"
}),
search.createColumn({
name: "custitem35",
join: "item",
summary: "MAX",
label: "New Current Purchase Price"
})
]
});
var cust_searchResult_assmCount= salesorderSearchObj.runPaged().count;
// to get search result more than 1000
var start = 0;
var end = 1000;
var cust_searchResult_assm = [];
var result;
var singleresult;
for (var i = 0; i < Math.ceil(cust_searchResult_assmCount / 1000); i++)
{
result = salesorderSearchObj.run().getRange({
start: start,
end: end
});
for (var j = 0; j < result.length; j++) {
singleresult = result[j];
cust_searchResult_assm.push(singleresult);
}
start = end;
end = end + 1000;
}
var customerAssm_array =[];
var toContinue=true;
for (var l = 0,j=0; j < cust_searchResult_assm.length; j++) {
var CSV_CustomerAssm= {};
// getting values from the search
var custInternalId4 = cust_searchResult_assm[j].getValue({
name: "internalid",
join: "customer",
summary: "GROUP"
});
var custId4 = cust_searchResult_assm[j].getValue({
name: "custbody_custid",
sort: search.Sort.ASC,
summary: "GROUP"
});
var companyname4 = cust_searchResult_assm[j].getValue({
name: "companyname",
join: "customer",
summary: "GROUP"
});
var date4 = cust_searchResult_assm[j].getValue({
name: "custentity16",
join: "customer",
summary: "GROUP"
});
var itemId4 = cust_searchResult_assm[j].getValue({
name: "internalid",
join: "item",
summary: "GROUP"
});
var item4 = cust_searchResult_assm[j].getText({
name: "item",
sort: search.Sort.ASC,
summary: "GROUP"
});
var description4 = cust_searchResult_assm[j].getValue({
name: "displayname",
join: "item",
summary: "GROUP"
});
var type4 = cust_searchResult_assm[j].getValue({
name: "type",
join: "item",
summary: "GROUP"
});
var qty_sold4 = cust_searchResult_assm[j].getValue({
name: "quantity",
summary: "SUM"
});
var total_spnd4 = cust_searchResult_assm[j].getValue({
name: "amount",
summary: "SUM"
});
var projected_spnd4 = cust_searchResult_assm[j].getValue({
name: "formulanumeric",
summary: "MAX",
formula: "ROUND({amount}+({amount}*{item.custitem19}),2)"
});
var last_purchp4 = cust_searchResult_assm[j].getValue({
name: "custitem34",
join: "item",
summary: "MAX"
});
var supp_currencynewpp4 = cust_searchResult_assm[j].getValue({
name: "custitem35",
join: "item",
summary: "MAX"
});
var percentageChange4 = cust_searchResult_assm[j].getValue({
name: "custitem19",
join: "item",
summary: "MAX"
});
var po_note4 = cust_searchResult_assm[j].getValue({
name: "custitem_ponote",
join: "item",
summary: "GROUP"
});
var priceLvl4 = cust_searchResult_assm[j].getText({
name: "itempricinglevel",
join: "customer",
summary: "GROUP"
});
if(priceLvl4 == "" || priceLvl4 == null || priceLvl4 == undefined || priceLvl4 =='- None -' || priceLvl4 == " "){
priceLvl4= 'Base Price'
}
if(obj[itemId4+'_'+priceLvl4]){
obj[itemId4+'_'+priceLvl4].forEach(function(eachValue){
CSV_CustomerAssm["CUSTOMER INTERNAL ID"] = checkif(custInternalId4);
CSV_CustomerAssm["CUSTOMER ID"] = checkif(custId4);
CSV_CustomerAssm["COMPANY NAME"] = checkif(companyname4);
CSV_CustomerAssm["SP REVIEW DATE"] = checkif(date4);
CSV_CustomerAssm["ITEM INTERNAL ID "] = checkif(itemId4);
CSV_CustomerAssm["ITEM"] = checkif(item4);
CSV_CustomerAssm["DESCRIPTION"] = checkif(description4);
if (parseInt(qty_sold4) < parseInt(eachValue.quantityB)){
CSV_CustomerAssm["QUANTITY SOLD"] = checkif1(null);
CSV_CustomerAssm["TOTAL SPEND"] = checkif1(null);
}else{
CSV_CustomerAssm["QUANTITY SOLD"] = checkif(qty_sold4);
CSV_CustomerAssm["TOTAL SPEND"] = checkif(total_spnd4);
}
CSV_CustomerAssm["PROJECTED SPEND"] = checkif(projected_spnd4);
CSV_CustomerAssm["LAST PURCHASE PRICE "] = checkif(last_purchp4);
CSV_CustomerAssm[" NEW LAST PURCHASE PRICE"] = checkif(supp_currencynewpp4);
CSV_CustomerAssm["PERCENT CHANGE"] = checkif(percentageChange4);
CSV_CustomerAssm["PO NOTE"] = checkif(po_note4);
CSV_CustomerAssm["PRICE LEVEL"] = checkif(priceLvl4);
CSV_CustomerAssm["QUANTITY BREAK"] = checkif(eachValue.quantityB);
CSV_CustomerAssm["CUSTOMER PRICING"] = checkif(eachValue.custPrice);
CSV_CustomerAssm["NEW PRICING"] = checkif(eachValue.newPrice);
CSV_CustomerAssm["CURRENT GP%"] = checkif(eachValue.currentGP);
CSV_CustomerAssm["GP% IF HTL ABSORBS"] = checkif(eachValue.currentGPhtl);
customerAssm_array.push(CSV_CustomerAssm)
l++;
CSV_CustomerAssm= undefined;
CSV_CustomerAssm= {}
});
} else{
if(!BASE_PRICE[itemId4])
continue;
BASE_PRICE[itemId4].forEach(function(eachValue){
CSV_CustomerAssm["CUSTOMER INTERNAL ID"] = checkif(custInternalId4);
CSV_CustomerAssm["CUSTOMER ID"] = checkif(custId4);
CSV_CustomerAssm["COMPANY NAME"] = checkif(companyname4);
CSV_CustomerAssm["SP REVIEW DATE"] = checkif(date4);
CSV_CustomerAssm["ITEM INTERNAL ID"] = checkif(itemId4);
CSV_CustomerAssm["ITEM"] = checkif(item4);
CSV_CustomerAssm["DESCRIPTION"] = checkif(description4);
if (parseInt(qty_sold4) < parseInt(eachValue.quantityB)){
CSV_CustomerAssm["QUANTITY SOLD"] = checkif1(null);
CSV_CustomerAssm["TOTAL SPEND"] = checkif1(null);
}else{
CSV_CustomerAssm["QUANTITY SOLD"] = checkif(qty_sold4);
CSV_CustomerAssm["TOTAL SPEND"] = checkif(total_spnd4);
}
CSV_CustomerAssm["PROJECTED SPEND"] = checkif(projected_spnd4);
CSV_CustomerAssm["LAST PURCHASE PRICE "] = checkif(last_purchp4);
CSV_CustomerAssm["NEW LAST PURCHASE PRICE"] = checkif(supp_currencynewpp4);
CSV_CustomerAssm["PERCENT CHANGE"] = checkif(percentageChange4);
CSV_CustomerAssm["PO NOTE"] = checkif(po_note4);
CSV_CustomerAssm["PRICE LEVEL"] = checkif(priceLvl4);
CSV_CustomerAssm["QUANTITY BREAK"] = checkif(eachValue.quantityB);
CSV_CustomerAssm["CUSTOMER PRICING"] = checkif(eachValue.custPrice);
CSV_CustomerAssm["NEW PRICING"] = checkif(eachValue.newPrice);
CSV_CustomerAssm["CURRENT GP%"] = checkif(eachValue.currentGP);
CSV_CustomerAssm["GP% IF HTL ABSORBS"] = checkif(eachValue.currentGPhtl);
customerAssm_array.push(CSV_CustomerAssm)
l++;
CSV_CustomerAssm= undefined;
CSV_CustomerAssm= {}
});
}
CSV_CustomerAssm= undefined;
CSV_CustomerAssm= {}
}
CSV_titleField4.defaultValue = JSON.stringify(customerAssm_array);
}catch(err){
log.error('error@ customer_assm search',err)
}
}
return {
onRequest: onRequest
};
});
//nullcheck
function checkif(singleitem) {
if (singleitem != "" && singleitem != null && singleitem != undefined && singleitem !='- None -' && singleitem != " " && singleitem != NaN && singleitem != 'NaN') {
return singleitem;
} else{
return "-";
}
}
function checkif1(singleitem) {
if (singleitem == "" || singleitem == null || singleitem == undefined || singleitem =='- None -' || singleitem == " ") {
return ".00";
} else{
return singleitem;
}
}