Requirement
We will be implementing an API to fetch the customer balance data from Netsuite to an application.
Request : GET
Parameters:
- CompanyCode : Company code(Company code field in the subsidiary record corresponding to subsidiary field in customer record)- Mandatory
- CustomerCode : Customer Code(id:custentity_code) field in the Customer record- Mandatory
PageIndex: Index of the page(starting from 1)- Mandatory
Solution
/**
* @NApiVersion 2.1
* @NScriptType Restlet
*/
define(['N/https', 'N/record', 'N/runtime', 'N/search', 'N/url'],
/**
* @param{https} https
* @param{record} record
* @param{runtime} runtime
* @param{search} search
* @param{url} url
*/
(https, record, runtime, search, url) => {
/**
* Defines the function that is executed when a GET request is sent to a RESTlet.
* @param {Object} requestParams - Parameters from HTTP request URL; parameters passed as an Object (for all supported
* content types)
* @returns {string | Object} HTTP response body; returns a string when request Content-Type is 'text/plain'; returns an
* Object when request Content-Type is 'application/json' or 'application/xml'
* @since 2015.2
*/
let PAGE_SIZE = 25;
let pageRangeLength;
function customer_balance(comp_code, cust_code, page_index) {
//Transaction search created to retrieve data for the transaction with invoice and credit note open
var transactionSearchObj = search.create({
type: "transaction",
settings: [
search.createSetting({
name: 'consolidationtype',
value: 'NONE'
})],
filters:
[
["type", "anyof", "CustCred", "CustInvc"],
"AND",
["status", "anyof", "CustInvc:A", "CustCred:A"],
"AND",
["subsidiary.custrecord_company_code", "is", comp_code],
"AND",
["customer.entityid", "is", cust_code],
"AND",
["mainline", "is", "T"]
],
columns:
[
search.createColumn({
name: "custrecord_company_code",
join: "subsidiary",
label: "Company Code"
}),
search.createColumn({
name: "entityid",
join: "customer",
label: "ID"
}),
// search.createColumn({
// name: "formulacurrency",
// formula: "(nvl({totalamount},0)-nvl({discountamount},0)-nvl({taxtotal},0)-nvl({shippingamount},0))/({exchangerate}))",
// label: "Formula (Currency)"
// }),
search.createColumn({
name: "formulacurrency",
formula: "(nvl({totalamount},0)-nvl({discountamount},0)-nvl({taxtotal},0)-nvl({shippingamount},0))/({exchangerate})",
label: "Formula (Currency)"
}),
search.createColumn({name: "tranid", label: "Document Number"}),
search.createColumn({name: "trandate", label: "Date"}),
search.createColumn({name: "currency", label: "Currency"}),
search.createColumn({name: "duedate", label: "Due Date/Receive By"}),
search.createColumn({name: "formulanumeric", formula: "{today}-{duedate}", label: "daysDiff"}),
search.createColumn({
name: "formulanumeric",
formula: "ROUND(ABS({today}-{duedate}))",
label: "Formula (Numeric)"
}),
search.createColumn({name: "currency", label: "Currency"}),
search.createColumn({name: "terms", label: "Terms"}),
search.createColumn({name: "otherrefnum", label: "PO/Check Number"}),
search.createColumn({name: "internalid", label: "Internal ID"}),
search.createColumn({name: "type", label: "Type"}),
search.createColumn({
name: "tranid",
join: "createdFrom",
label: "Document Number"
}),
search.createColumn({name: "taxtotal", label: "Amount (Transaction Tax Total)"}),
search.createColumn({name: "exchangerate", label: "Exchange Rate"}),
]
});
let searchPageRanges;
//Setting pagesize to 25
try {
searchPageRanges = transactionSearchObj.runPaged({
pageSize: 25
});
} catch (err) {
return [];
}
log.debug('Searhcpageranges',searchPageRanges)
if (searchPageRanges.pageRanges.length < 1)
return [];
pageRangeLength = searchPageRanges.pageRanges.length;
log.debug('pageRangeLength', pageRangeLength);
var getIndex=checkIndex(page_index,pageRangeLength)
log.debug('Check index value in get',getIndex)
if(getIndex){
return true;
}else{
var response = [];
// for (let pageIndex = 1; pageIndex <= pageRangeLength; pageIndex++) {
searchPageRanges.fetch({
index: page_index
}).data.forEach(function (result) {
response.push(result);
});
log.debug('Response',response)
// }
return response;
}
// var response = [];
// // for (let pageIndex = 1; pageIndex <= pageRangeLength; pageIndex++) {
// searchPageRanges.fetch({
// index: page_index
// }).data.forEach(function (result) {
// response.push(result);
// });
//
// // }
//
// return response;
}
function checkIndex(page_index,pageRangeLength){
if((Number(page_index >= pageRangeLength))) {
return true;
}
else{
return false;
}
}
function checkForParameter(parameter) {
if (
parameter !== "" && parameter !== null && parameter !== undefined && parameter !== false && parameter !== "null"
&& parameter !== "undefined" && parameter !== " " && parameter !== 'false'
// parameter != "" && parameter != null && parameter != undefined && parameter != "null" &&
// parameter != "undefined" && parameter != " " && parameter != false
) {
return true;
} else {
return false;
}
}
const get = (requestParams) => {
try {
const requestObj = {
company_code: requestParams?.company_code,
customer_code: requestParams?.customer_code,
page_index:(requestParams?.page_index),
};
log.debug('Requestparams',requestObj)
if ((!checkForParameter(requestObj?.page_index))|| ((requestObj?.page_index)<0)) {
return JSON.stringify({
summary: {
status: "FAILURE",
reason: "PAGENUMBER_FORMAT_IS_INVALID"
},
result: {
pageInfo: {
pageLength: 0,
pageIndex: 0,
isLastPage: false
},
lines: []
}
});
}
// requestObj.page_index = requestObj.page_index < 1 ? 1 : requestObj.page_index;
//Search created to check whether the company code is existing
function comp_code_search(compny_code) {
var subsidiarySearchObj = search.create({
type: "subsidiary",
filters:
[
["custrecord_company_code", "is", compny_code]
],
columns:
[
search.createColumn({name: "custrecord_company_code", label: "Company Code"})
]
});
var searchResultCount = subsidiarySearchObj.runPaged().count;
if (searchResultCount > 0) {
return true;
} else {
return false;
}
}
// Error response return if company code is null
if (!checkForParameter(requestParams?.company_code)) {
return JSON.stringify({
"summary": {
"status": "ERROR",
"message": "COMPANY_CODE_IS_NULL",
"company_code": requestParams?.company_code,
"cust_no": requestParams?.customer_code
}
});
}
//Error response return if Customer code is null
else if (!checkForParameter(requestParams?.customer_code)) {
return JSON.stringify({
"summary": {
"status": "ERROR",
"message": "CUSTOMER_CODE_IS_NULL",
"company_code": requestParams?.company_code,
"cust_no": requestParams?.customer_code
}
});
}
//Error response return if the company code is invalid
else if (!(comp_code_search(requestParams?.company_code))) {
return JSON.stringify({
"summary": {
"status": "ERROR",
"message": "INVALID_COMPANY_CODE",
"company_code": requestParams?.company_code,
"cust_no": requestParams?.customer_code
}
});
}
var search_res = customer_balance(requestParams?.company_code, requestParams?.customer_code, requestParams?.page_index)
if(search_res===true){
return JSON.stringify({
"summary": {
"status": "ERROR",
"message": "PAGE_INDEX_IS_OUT_OF_RANGE",
"company_code": requestParams?.company_code,
"cust_no": requestParams?.customer_code
}
});
}
var search_count = search_res.length;
log.debug('Count', search_count)
var results = [];
let cust_bal_obj = {};
var subtotal;
var dueDays;
var doc_typ;
var crdr_indicator;
// var dayDifference;
//If the search result is not empty
if (search_count > 0) {
for (var i = 0; i < search_res.length; i++) {
var dueDate = search_res[i].getValue({name: "duedate", label: "Due Date/Receive By"});
//If type is credt note set subtotal value to negative value
//Subtotal value is subtotal+taxtotal
var amount=search_res[i].getValue({
name: "formulacurrency",
formula: "(nvl({totalamount},0)-nvl({discountamount},0)-nvl({taxtotal},0)-nvl({shippingamount},0)))",
label: "Formula (Currency)"
})
log.debug('Amoutn',amount)
var caluculatedTax=(Number(search_res[i].getValue({
name: "taxtotal",
label: "Amount (Transaction Tax Total)"
}))/(Number(search_res[i].getValue({name: "exchangerate", label: "Exchange Rate"}))))
log.debug('Calculate Tax',caluculatedTax)
if (search_res[i].getValue({name: "type", label: "Type"}) === 'CustCred') {
subtotal = (Number(search_res[i].getValue({
name: "formulacurrency",
formula: "(nvl({totalamount},0)-nvl({discountamount},0)-nvl({taxtotal},0)-nvl({shippingamount},0))/({exchangerate})",
label: "Formula (Currency)"
}))+caluculatedTax)
// (Number(search_res[i].getValue({
// name: "taxtotal",
// label: "Amount (Transaction Tax Total)"
// }))))
doc_typ = 'RC'
crdr_indicator = 'H'
}
else if (search_res[i].getValue({name: "type", label: "Type"}) === 'CustInvc') {
subtotal = (Number(search_res[i].getValue({
name: "formulacurrency",
formula: "(nvl({totalamount},0)-nvl({discountamount},0)-nvl({taxtotal},0)-nvl({shippingamount},0))/({exchangerate}))",
label: "Formula (Currency)"
}))+caluculatedTax)
// (Number(search_res[i].getValue({
// name: "taxtotal",
// label: "Amount (Transaction Tax Total)"
// }))))
doc_typ = 'RA',
crdr_indicator = 'S'
}
var daysDiff = search_res[i].getValue({
name: "formulanumeric",
formula: "ROUND(ABS({today}-{duedate}))",
label: "Formula (Numeric)"
})
const today = new Date();
var dd = String(today.getDate());
var mm = String(today.getMonth() + 1).padStart(2, '0'); //January is 0!
var yyyy = today.getFullYear();
var tod = dd + '/' + mm + '/' + yyyy;
var dateValue = dueDate.split('/')
var newDate = dateValue[1] + '/' + dateValue[0] + '/' + dateValue[2]
const date2 = new Date(newDate)
var dayDifference;
if(daysDiff!==""){
if (date2.getTime() < today.getTime()) {
dayDifference = (Number(daysDiff-1))
} else if (date2.getTime() > today.getTime()) {
dayDifference = -(Number(daysDiff)+1)
}
}else if(daysDiff===""){
dayDifference=daysDiff
log.debug("set")
}
var fiscel_year = search_res[i].getValue({name: "trandate", label: "Date"})
var fis = fiscel_year.split('/')
//Setting values to the object
cust_bal_obj = {
"company_code": search_res[i].getValue({
name: "custrecord_company_code",
join: "subsidiary",
label: "Company Code"
}),
"cust_no": search_res[i].getValue({
name: "entityid",
join: "customer",
label: "ID"
}),
"fiscal_year": fis[2],
"acct_no": search_res[i].getValue({name: "tranid", label: "Document Number"}),
"post_date": search_res[i].getValue({name: "trandate", label: "Date"}),
"assign_no": search_res[i].getValue({name: "tranid", label: "Document Number"}),
"doc_date": search_res[i].getValue({name: "trandate", label: "Date"}),
"bill_number": search_res[i].getValue({name: "tranid", label: "Document Number"}),
"so_number": search_res[i].getValue({
name: "tranid",
join: "createdFrom",
label: "Document Number"
}),
"local_amt": subtotal,
"doc_amt": subtotal,
"doc_curr": search_res[i].getText({name: "currency", label: "Currency"}),
"due_date": search_res[i].getValue({name: "duedate", label: "Due Date/Receive By"}),
"due_days": dayDifference,
"payment_trm": search_res[i].getValue({name: "terms", label: "Terms"}),
"ref_doc": search_res[i].getValue({name: "tranid", label: "Document Number"}),
"doc_type": doc_typ,
"crdr_indicator": crdr_indicator,
"thai_amt": "",
"exchange_rate_thai": "",
"text": search_res[i].getText({name: "terms", label: "Terms"}),
"flag_excess": "",
"flag_due": "",
"po_number": search_res[i].getValue({name: "otherrefnum", label: "PO/Check Number"}),
"custom_inv": "",
"status": "success"
}
log.debug('Object.............', cust_bal_obj)
results.push(cust_bal_obj)
}
var islast;
log.debug('result', results)
if ((pageRangeLength - 1) == Number(requestParams.page_index)) {
islast = true
} else {
islast = false
}
// Returning the RECORD FOUND SUCCESS response
return JSON.stringify({
summary: {
status: "SUCCESS",
reason: "RECORD_FOUND"
},
"result": {
"pageInfo": {
"pageLength": pageRangeLength,
"pageIndex": Number(requestParams.page_index),
"isLastPage": islast
},
lines: results
}
});
} else {
return JSON.stringify({
"summary": {
"status": "ERROR",
"message": "RECORD_NOT_FOUND",
"company_code": requestParams?.company_code,
"cust_no": requestParams?.customer_code
}
});
}
} catch (e) {
}
}
const put = (requestBody) => {
}
const post = (requestBody) => {
}
const doDelete = (requestParams) => {
}
return {get}
});