Jira Code: TC-62
This script takes customer id as a parameter from the Taipan website and it will find the total invoice amount, total credit amount within each quarter. Calculated the rebate amount for each quarter based on the invoice and credit amount. These values are returned to the website as an object.
define(['N/error', 'N/record', 'N/search'],
function(error, record, search) {
return {
onRequest: function getCustomerRebateDetails(context){
try {
logme("context.request.method",context.request.method);
var callBackFuncStr = context.request.parameters.callback;
logme("callBackFuncStr",callBackFuncStr);
//If there is callback
if(callBackFuncStr != ""){
try {
var customerID = context.request.parameters.custid;//get the customer id
customerID=parseInt(customerID);
logme("customerID",customerID);
var invoiceSearchObj = search.create({//search the invoice amount
type: "invoice",
filters:
[["type","anyof","CustInvc"],
"AND",
["mainline","is","T"],
"AND",
["name","anyof",customerID],
"AND",
["datecreated","within","thisyear"]],
columns:
[
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "case when((TO_CHAR({datecreated},'MM'))=1 OR TO_CHAR({datecreated},'MM')=2 OR TO_CHAR({datecreated},'MM')=3) then {amount} else 0 end",
label: "Jan thru March"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "case when((TO_CHAR({datecreated},'MM'))=4 OR TO_CHAR({datecreated},'MM')=5 OR TO_CHAR({datecreated},'MM')=6) then {amount} else 0 end",
label: "April thru June"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "case when((TO_CHAR({datecreated},'MM'))=7 OR TO_CHAR({datecreated},'MM')=8 OR TO_CHAR({datecreated},'MM')=9) then {amount} else 0 end",
label: "July thru Sept"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "case when((TO_CHAR({datecreated},'MM'))=10 OR TO_CHAR({datecreated},'MM')=11 OR TO_CHAR({datecreated},'MM')=12) then {amount} else 0 end",
label: "October thru December"
}),
search.createColumn({
name: "amount",
summary: "SUM",
label: "Amount"
})
]
});
var searchResult = invoiceSearchObj.run().getRange({
start : 0,
end : 1
});
var columns = invoiceSearchObj.columns;
//logme('searchResult[0]',searchResult[0]);
var q1_invoiceamount = searchResult[0].getValue(
columns[0]);
/*var q1_invoiceamount = searchResult[0].getValue({ //get the invoice quarter1 amount
name: "formulanumeric",
summary: "SUM",
formula: "case when((TO_CHAR({datecreated},'MM'))=1 OR TO_CHAR({datecreated},'MM')=2 OR TO_CHAR({datecreated},'MM')=3) then {amount} else 0 end"
});*/
//logme('q1_invoiceamount',q1_invoiceamount);
var q2_invoiceamount = searchResult[0].getValue(
columns[1]);
//logme('q2_invoiceamount',q2_invoiceamount);
var q3_invoiceamount = searchResult[0].getValue(
columns[2]);
//logme('q3_invoiceamount',q3_invoiceamount);
var q4_invoiceamount = searchResult[0].getValue(
columns[3]);
//logme('q3_invoiceamount',q3_invoiceamount);
var totalInvoiceAmount = searchResult[0].getValue(
columns[4]);
logme('totalInvoiceAmount',totalInvoiceAmount);
if(q1_invoiceamount){
q1_invoiceamount = q1_invoiceamount;
}else{
q1_invoiceamount = 0.00;
}
if(q2_invoiceamount){
q2_invoiceamount = q2_invoiceamount;
}else{
q2_invoiceamount = 0.00;
}
if(q3_invoiceamount){
q3_invoiceamount = q3_invoiceamount;
}else{
q3_invoiceamount = 0.00;
}
if(q4_invoiceamount){
q4_invoiceamount = q4_invoiceamount;
}else{
q4_invoiceamount = 0.00;
}
if(totalInvoiceAmount){
totalInvoiceAmount = totalInvoiceAmount;
}else{
totalInvoiceAmount = 0.00;
}
var creditMemoSearchObj = search.create({//search the invoice amount
type: "creditmemo",
filters:
[
["type","anyof","CustCred"],
"AND",
["mainline","is","T"],
"AND",
["name","anyof",customerID],
"AND",
["datecreated","within","thisyear"]
],
columns:
[
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "case when((TO_CHAR({datecreated},'MM'))=1 OR TO_CHAR({datecreated},'MM')=2 OR TO_CHAR({datecreated},'MM')=3) then {amount} else 0 end",
label: "Jan thru March"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "case when((TO_CHAR({datecreated},'MM'))=4 OR TO_CHAR({datecreated},'MM')=5 OR TO_CHAR({datecreated},'MM')=6) then {amount} else 0 end",
label: "April thru June"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "case when((TO_CHAR({datecreated},'MM'))=7 OR TO_CHAR({datecreated},'MM')=8 OR TO_CHAR({datecreated},'MM')=9) then {amount} else 0 end",
label: "July thru Sept"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "case when((TO_CHAR({datecreated},'MM'))=10 OR TO_CHAR({datecreated},'MM')=11 OR TO_CHAR({datecreated},'MM')=12) then {amount} else 0 end",
label: "October thru December"
}),
search.createColumn({
name: "amount",
summary: "SUM",
label: "Amount"
})
]
});
var creditSearchResult = creditMemoSearchObj.run().getRange({
start : 0,
end : 1
});
var columns1 = creditMemoSearchObj.columns;
//logme('creditSearchResult[0]',creditSearchResult[0]);
var q1CreditAmount = creditSearchResult[0].getValue(
columns1[0]);
var q2CreditAmount = creditSearchResult[0].getValue(
columns1[1]);
var q3CreditAmount = creditSearchResult[0].getValue(
columns1[2]);
var q4CreditAmount = creditSearchResult[0].getValue(
columns1[3]);
var totalCreditAmount = creditSearchResult[0].getValue(
columns1[4]);
if(q1CreditAmount){
q1CreditAmount = q1CreditAmount;
}else{
q1CreditAmount = 0.00;
}
if(q2CreditAmount){
q2CreditAmount = q2CreditAmount;
}else{
q2CreditAmount = 0.00;
}
if(q3CreditAmount){
q3CreditAmount = q3CreditAmount;
}else{
q3CreditAmount = 0.00;
}
if(q4CreditAmount){
q4CreditAmount = q4CreditAmount;
}else{
q4CreditAmount = 0.00;
}
if(totalCreditAmount){
totalCreditAmount = totalCreditAmount;
}else{
totalCreditAmount = 0.00;
}
//logme('totalCreditAmount',totalCreditAmount);
var q1_elegibleSales = parseFloat(q1_invoiceamount) + parseFloat(q1CreditAmount);
if(q1_elegibleSales){
q1_elegibleSales = q1_elegibleSales;
}else{
q1_elegibleSales = 0.00;
}
var q2_elegibleSales = parseFloat(q2_invoiceamount) + parseFloat(q2CreditAmount);
if(q2_elegibleSales){
q2_elegibleSales = q2_elegibleSales;
}else{
q2_elegibleSales = 0.00;
}
var q3_elegibleSales = parseFloat(q3_invoiceamount) + parseFloat(q3CreditAmount);
if(q3_elegibleSales){
q3_elegibleSales = q3_elegibleSales;
}else{
q3_elegibleSales = 0.00;
}
var q4_elegibleSales = parseFloat(q4_invoiceamount) + parseFloat(q4CreditAmount);
if(q4_elegibleSales){
q4_elegibleSales = q4_elegibleSales;
}else{
q4_elegibleSales = 0.00;
}
var totalElegibleSales = parseFloat(totalInvoiceAmount) + parseFloat(totalCreditAmount);
if(totalElegibleSales){
totalElegibleSales = totalElegibleSales;
}else{
totalElegibleSales = 0.00;
}
//logme('totalElegibleSales',totalElegibleSales);
var rebateAmount = search.lookupFields({ //get customer credit amount value
type:"customer",id:customerID,
columns: ['custentity_rebate_amount','custentity_bonus_rebate_amount','custentity_bonus_rebate_percent']
});
var rebatePercent,bonusRebateTotalAmount;
var bonusRebateAmount = rebateAmount.custentity_bonus_rebate_amount;
var bonusRebatePercent = rebateAmount.custentity_bonus_rebate_percent;
if(bonusRebateAmount){
bonusRebateAmount = bonusRebateAmount;
}else{
bonusRebateAmount = 0.00;
}
if(bonusRebatePercent){
bonusRebatePercent = bonusRebatePercent;
}else{
bonusRebatePercent = '-';
}
if(totalElegibleSales && bonusRebatePercent){
bonusRebateTotalAmount = ((rebateAmount.custentity_bonus_rebate_percent.split('%')[0] * totalElegibleSales)/100).toFixed(2);
}
logme('bonusRebateTotalAmount',bonusRebateTotalAmount);
if(rebateAmount){
rebatePercent = rebateAmount.custentity_rebate_amount;
rebateAmount = rebateAmount.custentity_rebate_amount.split('%')[0];
//rebateAmount = rebateAmount.split['%'][0];
}else{
rebateAmount = 0;
}
if(rebatePercent){
rebatePercent = rebatePercent;
}else{
rebatePercent='-';
}
logme('rebateAmount',rebateAmount);
var totalRebateAmount,q1RebateAmount,q2RebateAmount,q3RebateAmount,q4RebateAmount;
if(q1_elegibleSales && rebateAmount){
q1RebateAmount = ((rebateAmount * q1_elegibleSales)/100).toFixed(2);
}
else{
q1RebateAmount = 0.00;
}
if(q2_elegibleSales && rebateAmount){
q2RebateAmount = ((rebateAmount * q2_elegibleSales)/100).toFixed(2);
}
else{
q2RebateAmount = 0.00;
}
if(q3_elegibleSales && rebateAmount){
q3RebateAmount = ((rebateAmount * q3_elegibleSales)/100).toFixed(2);
}
else{
q3RebateAmount = 0.00;
}
if(q4_elegibleSales && rebateAmount){
q4RebateAmount = ((rebateAmount * q4_elegibleSales)/100).toFixed(2);
}
else{
q4RebateAmount = 0.00;
}
if(totalElegibleSales && rebateAmount){
totalRebateAmount = ((rebateAmount * totalElegibleSales)/100).toFixed(2);
}
else{
totalRebateAmount = 0.00;
}
var rebateDetails={ // add details to array
'q1_invoiceamount':q1_invoiceamount,
'q2_invoiceamount':q2_invoiceamount,
'q3_invoiceamount':q3_invoiceamount,
'q4_invoiceamount':q4_invoiceamount,
'totalInvoiceAmount': totalInvoiceAmount,
'q1CreditAmount':q1CreditAmount,
'q2CreditAmount':q2CreditAmount,
'q3CreditAmount':q3CreditAmount,
'q4CreditAmount':q4CreditAmount,
'totalCreditAmount':totalCreditAmount,
'q1_elegibleSales':q1_elegibleSales,
'q2_elegibleSales':q2_elegibleSales,
'q3_elegibleSales':q3_elegibleSales,
'q4_elegibleSales':q4_elegibleSales,
'totalElegibleSales':totalElegibleSales,
'rebateAmount': rebatePercent,
'q1RebateAmount':q1RebateAmount,
'q2RebateAmount':q2RebateAmount,
'q3RebateAmount':q3RebateAmount,
'q4RebateAmount':q4RebateAmount,
'totalRebateAmount':totalRebateAmount,
'bonusRebateAmount':bonusRebateAmount,
'bonusRebatePercent':bonusRebatePercent,
'bonusRebateTotalAmount':bonusRebateTotalAmount
};
logme('rebateDetails',rebateDetails);
result = rebateDetails;
var strJson = callBackFuncStr + '(\'' + JSON.stringify(result)
+ '\')';
context.response.write(strJson);
} catch (e) {
logme('err@callback',e);
result = "failure";
var strJson = callBackFuncStr + '(\'' + JSON.stringify(result)
+ '\')';
context.response.write(strJson);
}
}
//If there is no callback
else{
logme('failure','No callback function detected');
result = "failure";
var strJson = callBackFuncStr + '(\'' + JSON.stringify(result)
+ '\')';
context.response.write(strJson);
}
} catch (e) {
logme('err@getCustomerRebateDetails',e);
result = "failure";
var strJson = callBackFuncStr + '(\'' + JSON.stringify(result)
+ '\')';
context.response.write(strJson);
}
}
};
});
/*******************************************************************************
* return error
*
* @param e
* @returns
*
*/
function getError(e) {
var stErrMsg = '';
if (e.getDetails != undefined) {
stErrMsg = '_' + e.getCode() + '<br>' + e.getDetails() + '<br>'
+ e.getStackTrace();
} else {
stErrMsg = '_' + e.toString();
}
return stErrMsg;
}
/*******************************************************************************
* Log these data
*
* @param title
* @param details
* @returns
*/
function logme(title, details) {
log.debug({
title : title,
details : details
});
}