OTGN-776
Develop the Template as per the given mockup and dimensions
- The number of IFs in the print document will be based on the number of IFs selected in each pagination.
- And the print will have multiple pages for the same IF based on the number of pallets in the Pallet information sublist.
- Need to add an additional column in the bulk print page to see the postal code.
- We have to make custom records for each mapping provided in excel.
- Script will look up the custom record for the related datas (Store Number) matching to the Final Destination Store Number (netsuite field) value.
Assumptions
- The print has multiple pages with mapping & specifications provided for the given layout.
- The number of IFs in the print document is based on the number of IFs selected in each pagination.
- The page size is 4” x 6”.
- The fields for generating Barcodes are always integers.
- All three barcodes are of Code-128 type.
- Exactly the same content in the excel mapping is populated in the print on matching the Store Number(Column Name in excel) and Final Destination Store Number (Netsuite Field).
- All the other manual input data follow the exact format as entered in the Netsuite Records
- SSCC Code will be imported to the Pallet Information Column field upon IF creation and this will be used in the Print
Risks
- PDF creation will be delayed based on the number of transactions be to be processed
- Here we proposed a custom button to create the provided print. The new layout can be taken from the print button option only.
- The script is a little complicated and there can be chances of exceeding the script execution time limit when taking print for 75 numbers in a single button click. In that case, we will split up the document instead of having one single document with 75 IFs. We can only confirm the number of IF split into separated documents while developing.
- The Print will follow the order of PO number.
- SSCC code Auto-generation is not considered in this scope
Solution
Suitelet for rendering print
/**
* @NApiVersion 2.1
* @NScriptType Suitelet
*/
/******************************************************************************************************************************
* CLIENTNAME:Ox Tools USA
* OTGN-774
******************************************************************************************************************************
* Date :03/09/2021
*
* Author: Jobin & Jismi IT Services LLP
* Script Description : Script used to generate a pdf print for ssscc/gs1-128 label.
* Date created : 26/08/2021
*
* ******************************************************************************************************************************
*
* REVISION HISTORY
*
*
*
********************************************************************************************************************************/
define(['N/render', 'N/record', 'N/currentRecord', 'N/format', 'N/file', 'N/runtime', 'N/search','N/xml'],
(render, record, currentRecord, format, file, runtime, search,xml) => {
/**
* 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 {
var response = scriptContext.response;
var request = scriptContext.request;
// log.debug('request obj', request);
var record_internal_ids = request.parameters.recId;
var json = JSON.parse(record_internal_ids);
log.debug('json data', json);
log.debug('json length', json.length);
// var ifObj = {};
var ifArray = [];
//var customRecordObj = {};
var customRecordArray = [];
var palletInformation = [];
var shipfromVBU;
var shiptoRDC;
var shipAddress;
var postCode;
var Prefixpostcode;
var shipCarrier;
var trackNum;
var poNum;
var deptName;
var DestStoreNum;
var ssccBarcode;
var storeNum
var name
var storeShipAdd
var storeCity
var storeState
var storePostCode
var storeCountry
var storeNumPrefix
var x
var arr
var intid
// if (DestStoreNum) {
var customrecord_jj_store_location_detailsSearchObj = search.create({
type: "customrecord_jj_store_location_details",
filters:
[],
columns:
[
search.createColumn({name: "custrecord_jj_store_number", label: "Store Number"}),
search.createColumn({name: "custrecord_jj_entity", label: "Name"}),
search.createColumn({name: "custrecord_jj_shipping_address", label: "Shipping Address"}),
search.createColumn({name: "custrecord_jj_city", label: "City"}),
search.createColumn({name: "custrecord_jj_state", label: "State"}),
search.createColumn({name: "custrecord_jj_postal_code", label: "Postal Code"}),
search.createColumn({name: "custrecord_jj_country", label: "Country"}),
search.createColumn({
name: "formulatext",
formula: "CONCAT('(91)', CONCAT((CASE WHEN LENGTH ({custrecord_jj_store_number}) = 1 THEN '000' ELSE CASE WHEN LENGTH ({custrecord_jj_store_number}) = 2 THEN '00' ELSE CASE WHEN LENGTH ({custrecord_jj_store_number}) = 3 THEN '0' ELSE '' END END END), {custrecord_jj_store_number}))",
label: "Formula (Text)"
})
]
});
var searchResultCount = customrecord_jj_store_location_detailsSearchObj.runPaged().count;
log.debug("customrecord_jj_store_location_detailsSearchObj result count", searchResultCount);
customrecord_jj_store_location_detailsSearchObj.run().each(function (result) {
storeNum = result.getValue(customrecord_jj_store_location_detailsSearchObj.columns[0])
name = result.getValue(customrecord_jj_store_location_detailsSearchObj.columns[1])
storeShipAdd = result.getValue(customrecord_jj_store_location_detailsSearchObj.columns[2])
storeCity = result.getValue(customrecord_jj_store_location_detailsSearchObj.columns[3])
storeState = result.getValue(customrecord_jj_store_location_detailsSearchObj.columns[4])
storePostCode = result.getValue(customrecord_jj_store_location_detailsSearchObj.columns[5])
storeCountry = result.getValue(customrecord_jj_store_location_detailsSearchObj.columns[6])
storeNumPrefix = result.getValue(customrecord_jj_store_location_detailsSearchObj.columns[7])
var customRecordObj = {};
customRecordObj.storeNum = storeNum;
customRecordObj.name = escapeXml(name);
customRecordObj.storeShipAdd = escapeXml(storeShipAdd);
customRecordObj.storeCity = escapeXml(storeCity);
customRecordObj.storeState = escapeXml(storeState);
customRecordObj.storePostCode = storePostCode;
customRecordObj.storeCountry = escapeXml(storeCountry);
customRecordObj.storeNumPrefix = storeNumPrefix;
customRecordArray.push(customRecordObj);
return true;
});
// log.debug('customRecordObj', customRecordObj)
log.debug('customRecordArray', customRecordArray)
// // }
var itemfulfillmentSearchObj = search.create({
type: "itemfulfillment",
filters:
[
["type", "anyof", "ItemShip"],
"AND",
["mainline", "is", "T"],
"AND",
["internalid", "anyof", json]//4283602, 5086740
],
columns:
[
search.createColumn({
name: "custbody36",
summary: "GROUP",
label: "Ship From VBU"
}),
search.createColumn({
name: "custbody32",
summary: "GROUP",
label: "SHIP TO RDC NUMBER"
}),
search.createColumn({
name: "shipaddress",
summary: "GROUP",
label: "Shipping Address"
}),
search.createColumn({
name: "shipzip",
summary: "GROUP",
label: "Shipping Zip"
}),
search.createColumn({
name: "formulatext",
summary: "GROUP",
formula: "CONCAT('(420)', {shipzip})",
label: "Formula (Text)"
}),
search.createColumn({
name: "custbody_ctl_carrier",
summary: "GROUP",
}),
search.createColumn({
name: "custrecord_jj_tracking_number",
join: "CUSTRECORD_JJ_PALLET_PACK_PARENT",
summary: "GROUP",
label: "Tracking Number"
}),
search.createColumn({
name: "custbody_ozlink_purchaseorder",
summary: "GROUP",
label: "Purchase Order #"
}),
search.createColumn({
name: "custbody44",
summary: "GROUP",
label: "Department Name"
}),
search.createColumn({
name: "custbody33",
summary: "GROUP",
label: "Lowes Final Destination Store Number"
}),
search.createColumn({
name: "custrecord_jj_sscc_barcode",
join: "CUSTRECORD_JJ_PALLET_PACK_PARENT",
summary: "GROUP",
label: "SSCC Barcode"
}),
search.createColumn({
name: "internalid",
summary: "GROUP",
label: "Internal ID"
})
]
});
var searchResultCount = itemfulfillmentSearchObj.runPaged().count;
log.debug("itemfulfillmentSearchObj result count", searchResultCount);
itemfulfillmentSearchObj.run().each(function (result) {
shipfromVBU = result.getValue(itemfulfillmentSearchObj.columns[0])
shiptoRDC = result.getValue(itemfulfillmentSearchObj.columns[1])
shipAddress = result.getValue(itemfulfillmentSearchObj.columns[2])
postCode = result.getValue(itemfulfillmentSearchObj.columns[3])
Prefixpostcode = result.getValue(itemfulfillmentSearchObj.columns[4])
shipCarrier = result.getText(itemfulfillmentSearchObj.columns[5])
poNum = result.getValue(itemfulfillmentSearchObj.columns[7])
deptName = result.getValue(itemfulfillmentSearchObj.columns[8])
DestStoreNum = result.getValue(itemfulfillmentSearchObj.columns[9])
intid = result.getValue(itemfulfillmentSearchObj.columns[11])
var ifObj = {};
ifObj.intid = intid
ifObj.shipfromVBU = escapeXml(shipfromVBU)
ifObj.shiptoRDC = escapeXml(convertNon(shiptoRDC))
ifObj.shipAddress = escapeXml(shipAddress);
ifObj.postCode = postCode;
ifObj.Prefixpostcode = Prefixpostcode;
ifObj.shipCarrier = escapeXml(convertNon(shipCarrier));
ifObj.poNum = escapeXml(poNum);
ifObj.deptName = standardEscape(deptName);
ifObj.DestStoreNum = escapeXml(DestStoreNum);
ifObj.palletInformation = [];
if (DestStoreNum) {
//finds x match in customRecordArray
arr = customRecordArray;
storeDetails = arr.filter((a) => {
if (a.storeNum == DestStoreNum) {
return a
}
});
log.debug("storeDetails", storeDetails)
var length = storeDetails.length
log.debug("length", length)
if (length == 1 || length > 1) {
log.debug("entered", "entered if")
ifObj.storeNum = storeDetails[0].storeNum;
ifObj.name = storeDetails[0].name;
ifObj.storeShipAdd = storeDetails[0].storeShipAdd;
ifObj.storeCity = storeDetails[0].storeCity;
ifObj.storeState = storeDetails[0].storeState;
ifObj.storePostCode = storeDetails[0].storePostCode;
ifObj.storeCountry = storeDetails[0].storeCountry;
ifObj.storeNumPrefix = storeDetails[0].storeNumPrefix
} else {
ifObj.DestStoreNum = ""
ifObj.storeNum = "- None -"
ifObj.name = ""
ifObj.storeShipAdd = ""
ifObj.storeCity = ""
ifObj.storeState = ""
ifObj.storePostCode = ""
ifObj.storeCountry = ""
ifObj.storeNumPrefix = " "
}
} else {
ifObj.DestStoreNum = ""
ifObj.storeNum = "- None -"
ifObj.name = ""
ifObj.storeShipAdd = ""
ifObj.storeCity = ""
ifObj.storeState = ""
ifObj.storePostCode = ""
ifObj.storeCountry = ""
ifObj.storeNumPrefix = " "
}
if (intid) {
ifObj.palletInformation = [{
trackNum: escapeXml(convertNon(result.getValue(itemfulfillmentSearchObj.columns[6]))),
ssccBarcode: result.getValue(itemfulfillmentSearchObj.columns[10])
}]
}
ifArray.push(ifObj);
//data structure [{ a: "", b: "", pallet: [{}, {}, {}] }, {}]
return true;
});
log.debug('ifArray', ifArray)
//Logic to avoid duplicates
var dataArr = ifArray.map(item => {
return [item.intid, item]
}); // creates array of array
var maparr = new Map(dataArr); // create key value pair from array of array
var resultsArray = [...maparr.values()];//converting back to array from mapobject
log.debug("final result's Array", resultsArray);
log.debug("ifArray length",ifArray.length)
var barCodeFile = renderPdfFile(ifArray);
log.debug("barCodeFile",barCodeFile)
scriptContext.response.writeFile(barCodeFile, true)
log.debug('governance', runtime.getCurrentScript().getRemainingUsage())
} catch (error) {
log.error('onRequest', error);
}
}
/**
* @description Function for rendering the template file.
*/
function renderPdfFile(resultsArray){
var renderer = render.create();
renderer.templateContent = file.load(26850166).getContents();
renderer.addCustomDataSource({
format: render.DataSource.OBJECT,
alias: "resultsArray",
data: {
resultsArray: resultsArray,
}
});
var barCodeFile = renderer.renderAsPdf();
return barCodeFile;
//context.response.writeFile(barCodeFile, true)
}
/**
* @description The function for escape special characters.
* @param unsafe
* @return {*}
*/
function escapeXml(unsafe) {
return unsafe.replace(/[<>&'"]/g, function (c) {
switch (c) {
case '<':
return '<';
case '>':
return '>';
case '&':
return '&';
case '\'':
return ''';
case '"':
return '"';
}
});
}
/**
* @description Function for escaping specials characters
* @param params {String}
* @return {string}
*/
function standardEscape(params){
var xmlEscapedDocument = xml.escape({
xmlText : params
});
return xmlEscapedDocument;
}
function convertNon(param){
if(param == "- None -"){
param = ""
}
return param;
}
return {onRequest}
});
<?xml version="1.0"?><!DOCTYPE pdf PUBLIC "-//big.faceless.org//report" "report-1.1.dtd">
<pdfset>
<#list resultsArray.resultsArray as resultsArray>
<pdf>
<head>
<link name="NotoSans" type="font" subtype="truetype" src="${nsfont.NotoSans_Regular}" src-bold="${nsfont.NotoSans_Bold}" src-italic="${nsfont.NotoSans_Italic}" src-bolditalic="${nsfont.NotoSans_BoldItalic}" bytes="2" />
<#if .locale == "zh_CN">
<link name="NotoSansCJKsc" type="font" subtype="opentype" src="${nsfont.NotoSansCJKsc_Regular}" src-bold="${nsfont.NotoSansCJKsc_Bold}" bytes="2" />
<#elseif .locale == "zh_TW">
<link name="NotoSansCJKtc" type="font" subtype="opentype" src="${nsfont.NotoSansCJKtc_Regular}" src-bold="${nsfont.NotoSansCJKtc_Bold}" bytes="2" />
<#elseif .locale == "ja_JP">
<link name="NotoSansCJKjp" type="font" subtype="opentype" src="${nsfont.NotoSansCJKjp_Regular}" src-bold="${nsfont.NotoSansCJKjp_Bold}" bytes="2" />
<#elseif .locale == "ko_KR">
<link name="NotoSansCJKkr" type="font" subtype="opentype" src="${nsfont.NotoSansCJKkr_Regular}" src-bold="${nsfont.NotoSansCJKkr_Bold}" bytes="2" />
<#elseif .locale == "th_TH">
<link name="NotoSansThai" type="font" subtype="opentype" src="${nsfont.NotoSansThai_Regular}" src-bold="${nsfont.NotoSansThai_Bold}" bytes="2" />
</#if>
<style type="text/css">* {
<#if .locale == "zh_CN">
font-family: NotoSans, NotoSansCJKsc, sans-serif;
<#elseif .locale == "zh_TW">
font-family: NotoSans, NotoSansCJKtc, sans-serif;
<#elseif .locale == "ja_JP">
font-family: NotoSans, NotoSansCJKjp, sans-serif;
<#elseif .locale == "ko_KR">
font-family: NotoSans, NotoSansCJKkr, sans-serif;
<#elseif .locale == "th_TH">
font-family: NotoSans, NotoSansThai, sans-serif;
<#else>
font-family: NotoSans, sans-serif;
</#if>
}
table {
font-size: 8pt;
table-layout:auto;
}
td.From{
width: 31.25%;
height:1in;
border:1px;
}
td.To{
width: 68.75%;
height:1in;
border:1px;
}
td.From1{
width: 62.5%;
height:1in;
border:1px;
}
td.To1{
width: 37.5%;
height:1in;
border:1px;
}
</style>
</head>
<body padding=".0in .0in 0.0in 0.0in" width="4.15in" height="6.25in">
<table width="100%">
<tr>
<td style="width:1.25in; height:1.05in; border:.1px;">
<#if resultsArray.shipfromVBU == "107308">
<b>FROM</b> <br/> OX TOOLS USA,1000<br/> N.County Line Rd.,<br/> Elmhurst, <br/> IL 60126.
<#elseif resultsArray.shipfromVBU == "112285">
<b>FROM</b> <br/> OX TOOLS USA,<br/> 500 Frith Drive,<br/> Building A,Ridgeway,<br/> VA 24148.
<#else>
<b>FROM</b>
</#if>
</td>
<td style="width:2.75in; height:1in;"><b>TO</b> <#if resultsArray.shiptoRDC!= ""><br/> ${resultsArray.shiptoRDC}</#if> <br/>
<#if resultsArray.shipAddress != "- None -">
<#assign addr =resultsArray.shipAddress/>
<#list addr?split("\n") as address>
<#assign len = address?length>
<#if len > 30>
<#assign tempIndex = address?index_of(" ", 30)>
<#assign originalIndex = tempIndex - 30>
<#assign splitIndex = originalIndex + 30>
<#assign splitVal1 = address?substring(0,splitIndex)>
<#assign splitVal2 = address?substring(splitIndex)>
${splitVal1}<br/> ${splitVal2}<br/>
<#else>
${address}<br/>
</#if>
</#list></#if>
</td>
</tr>
</table>
<table border=".1px" width="100%">
<#if resultsArray.postCode != "- None -">
<tr>
<td style="width:2.5in; height:1.05in; border:.1px;" ><b>SHIP TO POST</b><span style="aling:center;">   (420)${resultsArray.postCode} </span>
<br/><#if resultsArray.postCode?has_content><barcode codetype="code128" style="width: 2.4in; height: .5in;" showtext="false" value= "${resultsArray.Prefixpostcode}" ></barcode></#if>
</td>
<td style="width:1.5in; height:1.05in;" ><b>CARRIER</b><br/><br/><br/>
<#assign len1 = resultsArray.shipCarrier?length>
<#if len1 > 20>
<#assign tempIndex = resultsArray.shipCarrier?index_of(" ", 10)>
<#assign originalIndex = tempIndex - 10>
<#assign splitIndex = originalIndex + 10>
<#assign splitVal1 = resultsArray.shipCarrier?substring(0,splitIndex)>
<#assign splitVal2 = resultsArray.shipCarrier?substring(splitIndex)>
${splitVal1} <br/> ${splitVal2}
<#else>
${resultsArray.shipCarrier}
</#if>
<br/> BLL: ${resultsArray.palletInformation.trackNum}</td>
</tr>
<#else/>
<tr>
<td style="width:4in; height:1.05in; border:.1px; line-height: 130%;" colspan="2"><b>CARRIER</b><br/><br/><br/>
<#if resultsArray.shipCarrier != "- None -"> ${resultsArray.shipCarrier}</#if>
<br/> BLL: <#if resultsArray.palletInformation.trackNum?string != "- None -"> ${resultsArray.palletInformation.trackNum} </#if></td>
</tr>
</#if>
<tr>
<td colspan="2" style="width:4in; height:1.05in; border:.1px; font-size: 12pt; line-height: 130%;" ><br/> PO: ${resultsArray.poNum}<br/> Dept #: 14 Dept Name : <#if resultsArray.deptName != "- None -"> ${resultsArray.deptName} </#if></td>
</tr>
<tr>
<td style="width:2.5in; height:1.05in; border:.1px;"><b>FOR</b>       ${resultsArray.storeNumPrefix}
<br/><#if resultsArray.storeNum != "- None -"><barcode codetype="code128" style="width: 2.4in; height: .75in;" showtext="false" value= "${resultsArray.storeNumPrefix}" ></barcode></#if></td>
<td style="width:1.5in; height:1.05in; border:.1px;"><#if resultsArray.storeNumPrefix?has_content><br/>${resultsArray.name}<br/>${resultsArray.storeShipAdd}<br/>${resultsArray.storeCity}<br/>${resultsArray.storeCountry} ${resultsArray.storeState}<br/>${resultsArray.storePostCode}</#if> </td>
</tr>
<tr>
<td colspan="2" style="width:4in; height:2.04in;"><b>SSCC</b> <#if resultsArray.palletInformation.ssccBarcode != "- None -">     <b><span align="center">
<#assign firstVal = resultsArray.palletInformation.ssccBarcode?substring(0,5)>
<#assign secondVal = resultsArray.palletInformation.ssccBarcode?substring(5,12)>
<#assign thirdVal = resultsArray.palletInformation.ssccBarcode?substring(12,21)>
<#assign lastVal = resultsArray.palletInformation.ssccBarcode?substring(21)>
${firstVal}  ${secondVal}  ${thirdVal}  ${lastVal}</span></b><barcode codetype="code128" style="width: 3.8in; height: 1.25in;" showtext="false" value="${resultsArray.palletInformation.ssccBarcode}"></barcode></#if></td>
</tr></table>
</body>
</pdf>
</#list>
</pdfset>