Jira code: TEL-2 Task 2
Created a Customer Statement in excel format and mail it on a button click from the sales order record. For that place one button in the SO record. On that button click, created the customer statement as an Excel file and it is mailed with a specific sender, recipient and a message body.
User Event Script
/**
* @NApiVersion 2.x
* @NScriptType UserEventScript
* @NModuleScope SameAccount
*/
/**
* Script Description: This script is for setting a button 'Send Customer Statement' in the Customer record's Edit/View mode
*/
/*******************************************************************************
* * Telegram* *
* **************************************************************************
* Date:9/2/18
* Script name: TG UE Email Btn CS
* Script id: customscript_tg_ue_email_btn_cs
* Deployment id: customdeploy_tg_ue_email_btn_cs
* Applied to: Customer Record
*
******************************************************************************/
define(['N/record'],
function(record) {
/**
* Function definition to be triggered before record is loaded.
*
* @param {Object} scriptContext
* @param {Record} scriptContext.newRecord - New record
* @param {string} scriptContext.type - Trigger type
* @param {Form} scriptContext.form - Current form
* @Since 2015.2
*/
function beforeLoad(scriptContext) {
try {
//Set a button and Provide the action for the button by calling the function in client script
var CustRec=scriptContext.form;
CustRec.addButton({
id:'custpage_newemail_btn',
label:'Send Customer Statement',
functionName:'createEmailAction'
});
//Loading Client Script
CustRec.clientScriptModulePath = 'SuiteScripts/TG CS EMAIL BTN ACTION.js';
} catch (e) {
log.debug({
title: e.name,
details: e.message
});
}
}
return {
beforeLoad: beforeLoad
};
});Client Script
/**
* @NApiVersion 2.x
* @NScriptType ClientScript
* @NModuleScope SameAccount
*/
/**
* Script Description: This script defining the function of 'Send Customer Statement' button action
* Using this script redirect to suitelet
*/
/*******************************************************************************
* * Telegram* *
* **************************************************************************
* Date:9/2/18
* Script name: TG CS Email Btn Action CS
* Script id: customscript_tg_cs_email_btn_action_cs
* Deployment id: customdeploy_tg_cs_email_btn_action_cs
* Applied to: Customer Record
*
******************************************************************************/
define(['N/url','N/currentRecord','N/https'],
function(url,currentRecord,https) {
/**
* 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) {
}
return {
pageInit: pageInit,
createEmailAction: function(){
//Getting current record id
var currentRec = currentRecord.get();
var id=currentRec.id;
//Setting the url of the suitelet script
var output = url.resolveScript({
scriptId: 'customscript_tg_ss_create_excel',
deploymentId: 'customdeploy_tg_ss_create_excel',
returnExternalUrl: false,
})+ '&recId=' + id;
//Opens url on a new Window
newWindow = window.open(output, 'netsuite', 'menubar=1,resizable=1,width=740,height=800');
}
};
});Suitelet
/**
* @NApiVersion 2.x
* @NScriptType Suitelet
* @NModuleScope SameAccount
*/
/**
* Script Description: Creating the Customer Statement in excel format and direct to a email message
* For that fetching the required details from the record
* Setting the excel sheet content in XML
* From XML creating the excel file and store it in file cabinet folder with internal id 194690
* Redirect to an email message
*/
/*******************************************************************************
* * Telegram* *
* **************************************************************************
* Date:9/2/18
* Script name: TG SS Excel CS
* Script id: customscript_tg_ss_create_excel
* Deployment id: customdeploy_tg_ss_create_excel
*
******************************************************************************/
define([ 'N/record', 'N/search', 'N/ui/serverWidget', 'N/render', 'N/xml',
'N/file', 'N/encode', 'N/redirect', 'N/email', 'N/runtime','N/email','N/config'],
function(record, search, serverWidget, render, xml, file, encode, redirect,
email, runtime,config) {
/**
* 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 {
//Getting the record id
var csId = context.request.parameters.recId;
log.debug({
title : "csId",
details : csId
});
//Load the record
var csRecord = record.load({
type : record.Type.CUSTOMER,
id : csId,
isDynamic : true
});
//Getting the required details from the loaded record
var address=csRecord.getValue({
fieldId : 'defaultaddress'
});
var replacedAddress = address.replace(/\r?\n|\r/g,
'\\');
var addressArray = replacedAddress.split("\\");
log.debug({
title : "addressArray",
details : addressArray
});
var curr_type = csRecord.getText({
fieldId : 'currency',
});
var str_no = csRecord.getText({
fieldId : 'custentity_tc_cust_store_number',
});
//Fetching the aging details using saved search
var invoiceSearchObj = search.create({
type: "transaction",
filters: [
["accounttype","anyof","AcctRec"],
"AND",
["status","noneof","CustInvc:B","CustInvc:D","CustInvc:E","CustInvc:V","CustPymt:B","CustPymt:A","CustPymt:C"],
"AND",
[["name","anyof",csId],"OR",["customersubof","anyof",csId]],
"AND",
["type","anyof","CustCred","CustInvc"],
"AND",
["mainline","is","T"]
],
columns: [
/*search.createColumn({
name: "entity",
summary: "GROUP"
}),*/
search.createColumn({
name: "mainline",
summary: "GROUP"
}),
search.createColumn({
name: "formulacurrency",
summary: "SUM",
formula: "NVL(case when trunc({today})-{trandate} <= 0 then (case when {type}<>'Invoice' then -{amountremaining} else {amountremaining} end) end,0)"
}),
search.createColumn({
name: "formulacurrency",
summary: "SUM",
formula: "NVL(case when trunc({today})-{trandate} between 1 and 30 then (case when {type}<>'Invoice' then -{amountremaining} else {amountremaining} end) end,0)"
}),
search.createColumn({
name: "formulacurrency",
summary: "SUM",
formula: "case when trunc({today})-{trandate} between 31 and 60 then (case when {type}<>'Invoice' then -{amountremaining} else {amountremaining} end) end"
}),
search.createColumn({
name: "formulacurrency",
summary: "SUM",
formula: "case when trunc({today})-{trandate} between 61 and 90 then (case when {type}<>'Invoice' then -{amountremaining} else {amountremaining} end) end"
}),
search.createColumn({
name: "formulacurrency",
summary: "SUM",
formula: "case when trunc({today})-{trandate} > 90 then (case when {type}<>'Invoice' then -{amountremaining} else {amountremaining} end) end"
}),
search.createColumn({
name: "formulacurrency",
summary: "SUM",
formula: "NVL(case when trunc({today})-{trandate} <= 0 then (case when {type}<>'Invoice' then -{amountremaining} else {amountremaining} end) end,0)+ NVL(case when trunc({today})-{trandate} between 1 and 30 then (case when {type}<>'Invoice' then -{amountremaining} else {amountremaining} end) end,0) + NVL(case when trunc({today})-{trandate} between 31 and 60 then (case when {type}<>'Invoice' then -{amountremaining} else {amountremaining} end) end,0) + NVL( case when trunc({today})-{trandate} between 61 and 90 then (case when {type}<>'Invoice' then -{amountremaining} else {amountremaining} end) end ,0)+ NVL(case when trunc({today})-{trandate} > 91 then (case when {type}<>'Invoice' then -{amountremaining} else {amountremaining} end) end,0)"
})
]
});
//Running and loading the saved search result
var resultSetc = invoiceSearchObj.run();
var firstResultc = resultSetc.getRange({
start: 0,
end: 1
})[0];
log.debug({
title : "firstResult",
details : firstResultc
});
current = 0.0;
var lastmonth = 0.0;
var last2month = 0.0;
var last3month = 0.0;
var over90 = 0.0;
var total=0.0;
var current = 0.0;
if((firstResultc!='')&&(firstResultc!=null)&&(firstResultc!=undefined)){
// get the value of all columns
if(((firstResultc.getValue(resultSetc.columns[1]))!='')&&((firstResultc.getValue(resultSetc.columns[1]))!=null)&&((firstResultc.getValue(resultSetc.columns[1]))!=undefined)){
current = parseFloat(firstResultc.getValue(resultSetc.columns[1]));
}
if(((firstResultc.getValue(resultSetc.columns[2]))!='')&&((firstResultc.getValue(resultSetc.columns[2]))!=null)&&((firstResultc.getValue(resultSetc.columns[2]))!=undefined)){
lastmonth = parseFloat(firstResultc.getValue(resultSetc.columns[2]));
}
if(((firstResultc.getValue(resultSetc.columns[3]))!='')&&((firstResultc.getValue(resultSetc.columns[3]))!=null)&&((firstResultc.getValue(resultSetc.columns[3]))!=undefined)){
last2month = parseFloat(firstResultc.getValue(resultSetc.columns[3]));
}
if(((firstResultc.getValue(resultSetc.columns[4]))!='')&&((firstResultc.getValue(resultSetc.columns[4]))!=null)&&((firstResultc.getValue(resultSetc.columns[4]))!=undefined)){
last3month = parseFloat(firstResultc.getValue(resultSetc.columns[4]));
}
if(((firstResultc.getValue(resultSetc.columns[5]))!='')&&((firstResultc.getValue(resultSetc.columns[5]))!=null)&&((firstResultc.getValue(resultSetc.columns[5]))!=undefined)){
over90 = parseFloat(firstResultc.getValue(resultSetc.columns[5]));
}
if(((firstResultc.getValue(resultSetc.columns[6]))!='')&&((firstResultc.getValue(resultSetc.columns[6]))!=null)&&((firstResultc.getValue(resultSetc.columns[6]))!=undefined)){
total = parseFloat(firstResultc.getValue(resultSetc.columns[6]));
}
//total=parseFloat(lastmonth+last2month+last3month+over90); // removed current
current=current.toFixed(2);
lastmonth=lastmonth.toFixed(2);
last2month=last2month.toFixed(2);
last3month=last3month.toFixed(2);
over90=over90.toFixed(2);
total=total.toFixed(2);
log.debug({
title : "lastmonth",
details : lastmonth
});
log.debug({
title : "last2month",
details : last2month
});
log.debug({
title : "last3month",
details : last3month
});
log.debug({
title : "over90",
details : over90
});
log.debug({
title : "total",
details : total
});
}
log.debug({
title : "info",
details : DateNow()+' '+DateBefore()+' '+address+' '+curr_type
});
var date_today=escape_for_xml(DateNow(),xml);
var date_old=escape_for_xml(DateBefore(),xml);
//Fetching the transaction details using a transaction saved search
var transactionSearchObj = search.create({
type: "transaction",
filters: [
["mainline","is","T"],
"AND",
["status","anyof","CustCred:A","CustInvc:A"],
"AND",
[["name","anyof",csId],"OR",["customersubof","anyof",csId]],
"AND",
["trandate","onorafter","startoflastmonth"]
//["accountingperiod.startdate","within","lastmonthtodate"]
//["trandate","after","lastmonthtodate"]
],
columns: [
search.createColumn({
name: "trandate",
sort: search.Sort.ASC
}),
search.createColumn({
name: "consoldepositbalance",
join: "customerMain"
}),
"type",
"tranid",
"entity",
"account",
"amount",
"amountremaining"
]
});
var searchResultCount = transactionSearchObj.runPaged().count;
//Running and loading the saved search result
transactionSearchObj.run().each(function(result){
// .run().each has a limit of 4,000 results
return true;
});
log.debug({
title : "searchResultCount",
details : searchResultCount
});
var amount_due=0.0;
var count=0;
var colBal=over90;
log.debug({
title : "conBal",
details : colBal
});
var itemArray=new Array;
if(searchResultCount>0){
var resultSet = transactionSearchObj.run();
var searchResults = resultSet.getRange({
start: 0,
end: 1000
});
log.debug({
title : "searchResults",
details : searchResults
});
count=searchResults.length;
var runBal=0;
//inserting the transaction details into a Array
for (var i = 0; i < searchResults.length; i++) {
var item=new Object;
// get the value of all columns
item.runningBalance = over90;
item.tr_date = searchResults[i].getValue({
name: 'trandate'
});
item.type = searchResults[i].getText({
name: 'type'
});
item.docno = searchResults[i].getValue({
name: 'tranid'
});
//Mrudul - Added this section to take care of Running balance from second line.
item.amount = parseFloat(searchResults[i].getValue({
name: 'amount'
}));
item.amountremain = parseFloat(searchResults[i].getValue({
name: 'amountremaining'
}));
//Calculating the running balance
if(i>=1){
item.runningBalance = parseFloat(runBal)+parseFloat(item.amount);
runBal=parseFloat(item.runningBalance);
}
else{
item.runningBalance=parseFloat(item.runningBalance)+parseFloat(item.amount);
runBal=parseFloat(item.runningBalance);
}
log.debug({
title : "runningBalance",
details : item.runningBalance
});
itemArray.push(item);
amount_due= item.runningBalance;
}
}
var DATA_TO_ADD = getXMLData(count, xml,itemArray,str_no,date_today,date_old,amount_due,addressArray,curr_type,colBal,current,lastmonth,last2month,last3month,over90,total);
var objXlsFile = generateDataFile(DATA_TO_ADD, encode, file, csId);
log.debug({
title : "objXlsFile",
details : objXlsFile
});
var emailId = csRecord.getText({
fieldId : 'email'
});
var name = csRecord.getText({
fieldId : 'altname'
});
var subject='Telegram Co: Statement As of '+date_old;
var msg=' Hello '+name+',<br><br> Please find attached your statement as of '+date_old+'.<br><br> Best regards <br><br> Telegram Co. Accounts <br> Tel. 03 9318 0822';
log.debug({
title : "Email content",
details : msg
});
//Redirect to a Email message
redirect.toRecord({
type : record.Type.MESSAGE,
id : null,
parameters : {
entity : csId,
l : 'T',
templatetype : 'EMAIL',
entitytype : 'custjob',
recordtype : 1,
iscustom : 't',
recepient: emailId,
subject: subject,
msg: msg,
mediaitem: objXlsFile
}
});
} catch (err) {
logme("ERROR", err);
}
}
return {
onRequest : onRequest
};
});
/*******************************************************************************
* Log these data
*
* @param title
* @param details
* @returns
*
* Created on 09-Aug-2017
*/
function logme(title, details) {
log.debug({
title : title,
details : details
});
}
/*******************************************************************************
* Finding the Today's date and return that date
*/
function DateNow(){
var today = new Date();
var dd = today.getDate()+1;
var mm = today.getMonth()+1;
var yyyy = today.getFullYear();
today = dd+'/'+mm+'/'+yyyy;
return today;
}
/*******************************************************************************
* Finding the first day of Previous period and return that date
*/
function DateBefore(){
var today = new Date();
var dd = today.getDate()+1;
var mm = today.getMonth()+1;
var yyyy=null;
if(mm==1){
yyyy = today.getFullYear()-1;
mm=12;
}
else{
yyyy = today.getFullYear();
mm=mm-1;
}
dd=1;
previous = dd+'/'+mm+'/'+yyyy;
return previous;
}
/*******************************************************************************
* Creating the XML code of the customer statement in excel format and returning that code
*/
function getXMLData(count, xml,itemArray,str_no,date_today,date_old,amount_due,addressArray,curr_type,colBal,current,lastmonth,last2month,last3month,over90,total) {
var XML = '';
XML=XML+'<?xml version="1.0"?> <?mso-application progid="Excel.Sheet"?> <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40">';
XML = XML+ '<Styles> <Style ss:ID="Default" ss:Name="Normal"> <Alignment ss:Vertical="Bottom"/> <Borders/> <Font ss:FontName="Times New Roman" x:CharSet="204" ss:Color="#000000"/> <Interior/> <NumberFormat/> <Protection/> </Style> <Style ss:ID="m36094912"> <Alignment ss:Horizontal="Center" ss:Vertical="Center"/> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/> </Borders> <Font ss:FontName="Arial" x:Family="Swiss" ss:Size="11" ss:Color="#FFFFFF" ss:Bold="1"/> <Interior ss:Color="#AAAAAA" ss:Pattern="Solid"/> </Style> <Style ss:ID="m42776548"> <Alignment ss:Horizontal="Center" ss:Vertical="Bottom" ss:WrapText="1"/> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/> </Borders> <Font ss:FontName="Times New Roman" x:Family="Roman" ss:Size="11" ss:Color="#000000"/> <Interior/> </Style> <Style ss:ID="m42776588"> <Alignment ss:Horizontal="Center" ss:Vertical="Bottom" ss:WrapText="1"/> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/> </Borders> <Font ss:FontName="Times New Roman" x:Family="Roman" ss:Size="11" ss:Color="#000000"/> <Interior/> </Style> <Style ss:ID="m42776728"> <Alignment ss:Horizontal="Center" ss:Vertical="Top" ss:WrapText="1"/> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/> </Borders> <Font ss:FontName="Arial" x:Family="Swiss" ss:Size="11" ss:Bold="1"/> <Interior ss:Color="#AAAAAA" ss:Pattern="Solid"/> </Style> <Style ss:ID="m42776748"> <Alignment ss:Horizontal="Center" ss:Vertical="Bottom" ss:WrapText="1"/> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/> </Borders> <Font ss:FontName="Times New Roman" x:Family="Roman" ss:Size="11" ss:Color="#000000"/> <Interior/> </Style> <Style ss:ID="m42775508"> <Alignment ss:Horizontal="Center" ss:Vertical="Center"/> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/> </Borders> <Font ss:FontName="Arial" x:Family="Swiss" ss:Size="11" ss:Color="#FFFFFF" ss:Bold="1"/> <Interior ss:Color="#AAAAAA" ss:Pattern="Solid"/> </Style> <Style ss:ID="m42775648"> <Alignment ss:Horizontal="Center" ss:Vertical="Bottom" ss:WrapText="1"/> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/> </Borders> <Font ss:FontName="Times New Roman" x:Family="Roman" ss:Size="11" ss:Color="#000000"/> <Interior/> </Style> <Style ss:ID="m42775688"> <Alignment ss:Horizontal="Center" ss:Vertical="Top" ss:ShrinkToFit="1"/> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/> </Borders> <Font ss:FontName="Arial" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/> <Interior/> <NumberFormat ss:Format="Fixed"/> </Style> <Style ss:ID="m42774968"> <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/> </Borders> <Font ss:FontName="Arial" x:Family="Swiss" ss:Size="11" ss:Color="#FFFFFF" ss:Bold="1"/> <Interior ss:Color="#AAAAAA" ss:Pattern="Solid"/> </Style> <Style ss:ID="m42774988"> <Alignment ss:Horizontal="Center" ss:Vertical="Center"/> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/> </Borders> <Font ss:FontName="Arial" x:Family="Swiss" ss:Size="11" ss:Color="#FFFFFF" ss:Bold="1"/> <Interior ss:Color="#AAAAAA" ss:Pattern="Solid"/> </Style> <Style ss:ID="m42775068"> <Alignment ss:Horizontal="Center" ss:Vertical="Center"/> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/> </Borders> <Font ss:FontName="Arial" x:Family="Swiss" ss:Size="11" ss:Color="#FFFFFF" ss:Bold="1"/> <Interior ss:Color="#AAAAAA" ss:Pattern="Solid"/> </Style> <Style ss:ID="s15"> <Alignment ss:Horizontal="Left" ss:Vertical="Top"/> <Borders/> <Interior/> </Style> <Style ss:ID="s16"> <Alignment ss:Horizontal="Left" ss:Vertical="Top"/> <Borders/> <Font ss:FontName="Arial" x:Family="Swiss" ss:Size="11" ss:Bold="1"/> <Interior/> </Style> <Style ss:ID="s17"> <Alignment ss:Horizontal="Left" ss:Vertical="Top"/> <Borders/> <Font ss:FontName="Arial" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/> <Interior/> </Style> <Style ss:ID="s18"> <Alignment ss:Horizontal="Left" ss:Vertical="Top" ss:WrapText="1"/> <Borders/> <Font ss:FontName="Arial" x:Family="Swiss" ss:Size="11" ss:Bold="1"/> <Interior/> </Style> <Style ss:ID="s19"> <Alignment ss:Vertical="Top" ss:ShrinkToFit="1"/> <Borders/> <Font ss:FontName="Arial" x:Family="Swiss" ss:Size="9" ss:Color="#000000"/> <Interior/> <NumberFormat ss:Format="Fixed"/> </Style> <Style ss:ID="s20"> <Alignment ss:Horizontal="Left" ss:Vertical="Top" ss:ShrinkToFit="1"/> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/> </Borders> <Font ss:FontName="Arial" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/> <Interior/> <NumberFormat ss:Format="d/m/yyyy;@"/> </Style> <Style ss:ID="s21"> <Alignment ss:Horizontal="Left" ss:Vertical="Top"/> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/> </Borders> <Font ss:FontName="Arial" x:Family="Swiss" ss:Size="11"/> <Interior/> </Style> <Style ss:ID="s22"> <Alignment ss:Vertical="Top"/> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/> </Borders> <Font ss:FontName="Times New Roman" x:Family="Roman" ss:Size="11" ss:Color="#000000"/> <Interior/> </Style> <Style ss:ID="s23"> <Alignment ss:Horizontal="Left" ss:Vertical="Top" ss:Indent="5"/> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/> </Borders> <Font ss:FontName="Times New Roman" x:Family="Roman" ss:Size="11" ss:Color="#000000"/> <Interior/> </Style> <Style ss:ID="s24"> <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/> </Borders> <Font ss:FontName="Arial" x:Family="Swiss" ss:Size="11" ss:Color="#FFFFFF" ss:Bold="1"/> <Interior ss:Color="#AAAAAA" ss:Pattern="Solid"/> </Style> <Style ss:ID="s25"> <Alignment ss:Vertical="Top"/> <Borders/> <Font ss:FontName="Arial" x:Family="Swiss" ss:Size="11"/> <Interior/> </Style> <Style ss:ID="s57"> <Alignment ss:Horizontal="Left" ss:Vertical="Top"/> <Borders/> <Font ss:FontName="Arial" x:Family="Swiss" ss:Size="12"/> <Interior/> </Style> <Style ss:ID="s58"> <Alignment ss:Horizontal="Left" ss:Vertical="Top"/> <Borders/> <Font ss:FontName="Arial" x:Family="Swiss" ss:Size="11"/> <Interior/> </Style> <Style ss:ID="s59"> <Alignment ss:Horizontal="Center" ss:Vertical="Top" ss:WrapText="1"/> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/> </Borders> <Font ss:FontName="Arial" x:Family="Swiss" ss:Size="11" ss:Bold="1"/> <Interior ss:Color="#AAAAAA" ss:Pattern="Solid"/> </Style> <Style ss:ID="s60"> <Alignment ss:Horizontal="Center" ss:Vertical="Top" ss:ShrinkToFit="1"/> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/> </Borders> <Font ss:FontName="Arial" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/> <Interior/> <NumberFormat ss:Format="Fixed"/> </Style> <Style ss:ID="s63"> <Alignment ss:Horizontal="Center" ss:Vertical="Top" ss:WrapText="1"/> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/> </Borders> <Font ss:FontName="Arial" x:Family="Swiss" ss:Size="11" ss:Color="#FFFFFF" ss:Bold="1"/> <Interior ss:Color="#AAAAAA" ss:Pattern="Solid"/> </Style> <Style ss:ID="s64"> <Alignment ss:Horizontal="Center" ss:Vertical="Top"/> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/> </Borders> <Font ss:FontName="Arial" x:Family="Swiss" ss:Size="11" ss:Color="#FFFFFF" ss:Bold="1"/> <Interior ss:Color="#AAAAAA" ss:Pattern="Solid"/> </Style> <Style ss:ID="s65"> <Alignment ss:Horizontal="Left" ss:Vertical="Top" ss:WrapText="1"/> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/> </Borders> <Font ss:FontName="Arial" x:Family="Swiss" ss:Size="11"/> <Interior/> </Style> <Style ss:ID="s72"> <Alignment ss:Horizontal="Center" ss:Vertical="Top" ss:WrapText="1"/> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/> </Borders> <Font ss:FontName="Arial" x:Family="Swiss" ss:Size="11"/> <Interior/> </Style> <Style ss:ID="s73"> <Alignment ss:Horizontal="Center" ss:Vertical="Top" ss:WrapText="1"/> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/> </Borders> <Font ss:FontName="Times New Roman" x:Family="Roman" ss:Size="11" ss:Color="#000000"/> <Interior/> </Style> <Style ss:ID="s74"> <Alignment ss:Horizontal="Center" ss:Vertical="Top"/> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/> </Borders> <Font ss:FontName="Arial" x:Family="Swiss" ss:Size="11"/> <Interior/> </Style> <Style ss:ID="s75"> <Alignment ss:Horizontal="Center" ss:Vertical="Top"/> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/> </Borders> <Font ss:FontName="Times New Roman" x:Family="Roman" ss:Size="11" ss:Color="#000000"/> <Interior/> </Style> <Style ss:ID="s79"> <Alignment ss:Horizontal="Left" ss:Vertical="Bottom" ss:WrapText="1"/> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/> </Borders> <Font ss:FontName="Times New Roman" x:Family="Roman" ss:Size="11" ss:Color="#000000"/> <Interior/> </Style> <Style ss:ID="s80"> <Alignment ss:Horizontal="Center" ss:Vertical="Top" ss:ShrinkToFit="1"/> <Borders> <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/> </Borders> <Font ss:FontName="Arial" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/> <Interior/> <NumberFormat ss:Format="Standard"/> </Style> <Style ss:ID="s83"> <Alignment ss:Horizontal="Center" ss:Vertical="Center"/> <Borders/> <Font ss:FontName="Arial" x:Family="Swiss" ss:Size="16" ss:Bold="1"/> <Interior/> </Style> <Style ss:ID="s84"> <Alignment ss:Horizontal="Center" ss:Vertical="Top" ss:ShrinkToFit="1" ss:WrapText="1"/> <Borders/> <Font ss:FontName="Arial" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/> <Interior/> <NumberFormat ss:Format="Short Date"/> </Style> <Style ss:ID="s85"> <Alignment ss:Horizontal="Center" ss:Vertical="Top" ss:ShrinkToFit="1" ss:WrapText="1"/> <Borders/> <Font ss:FontName="Arial" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/> <Interior/> <NumberFormat ss:Format="\$#,##0.00"/> </Style> <Style ss:ID="s86"> <Alignment ss:Horizontal="Center" ss:Vertical="Top" ss:WrapText="1"/> <Borders/> <Font ss:FontName="Arial" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/> <Interior/> </Style> <Style ss:ID="s87"> <Alignment ss:Horizontal="Center" ss:Vertical="Top" ss:WrapText="1"/> <Borders/> <Font ss:FontName="Arial" x:Family="Swiss" ss:Size="11"/> <Interior/> </Style> </Styles>';
XML=XML+'<Worksheet ss:Name="Customer Statement"> <Table ss:ExpandedColumnCount="14" ss:ExpandedRowCount="300" x:FullColumns="1" x:FullRows="1" ss:StyleID="s15" ss:DefaultColumnWidth="40.5"> <Column ss:StyleID="s15" ss:AutoFitWidth="0" ss:Width="52.5"/> <Column ss:StyleID="s15" ss:AutoFitWidth="0" ss:Width="75"/> <Column ss:Index="5" ss:StyleID="s15" ss:AutoFitWidth="0" ss:Width="54.75"/> <Column ss:StyleID="s15" ss:AutoFitWidth="0" ss:Width="37.5"/> <Column ss:StyleID="s15" ss:AutoFitWidth="0" ss:Width="42"/> <Column ss:StyleID="s15" ss:AutoFitWidth="0" ss:Width="30.75"/> <Column ss:StyleID="s15" ss:AutoFitWidth="0" ss:Width="57"/> <Column ss:StyleID="s15" ss:Width="95"/> <Column ss:Index="12" ss:StyleID="s15" ss:AutoFitWidth="0" ss:Width="50"/> <Column ss:StyleID="s15" ss:AutoFitWidth="0" ss:Width="61.5"/> <Row ss:AutoFitHeight="0" ss:Height="24"/> <Row ss:AutoFitHeight="0" ss:Height="32.0625"> <Cell ss:Index="10" ss:MergeAcross="3" ss:StyleID="s83"><ss:Data ss:Type="String" xmlns="http://www.w3.org/TR/REC-html40"><B>Customer Statement</B></ss:Data></Cell> </Row>';
XML=XML+'<Row ss:AutoFitHeight="0" ss:Height="23.0625"/> <Row ss:Height="15"> <Cell ss:Index="2" ss:MergeAcross="1" ss:StyleID="s57"><Data ss:Type="String">Telegram Co</Data></Cell> <Cell ss:StyleID="s17"/> <Cell ss:StyleID="s17"/> <Cell ss:Index="10" ss:StyleID="s18"><Data ss:Type="String">Date</Data></Cell> <Cell ss:MergeAcross="1" ss:StyleID="s84"><Data ss:Type="String">'+date_today+'</Data></Cell> </Row>';
XML=XML+'<Row ss:Height="15"> <Cell ss:Index="2" ss:MergeAcross="3" ss:StyleID="s58"><Data ss:Type="String">63A Little Oxford Street Collingwood VIC 3066</Data></Cell> <Cell ss:Index="10" ss:StyleID="s18"><Data ss:Type="String">Amount Due</Data></Cell> <Cell ss:MergeAcross="1" ss:StyleID="s85"><Data ss:Type="String">'+total+'</Data></Cell> </Row>';
XML=XML+'<Row ss:Height="15"> <Cell ss:Index="2" ss:StyleID="s25"><Data ss:Type="String">ABN # 50 606 166 741</Data></Cell> <Cell ss:StyleID="s25"/> <Cell ss:StyleID="s17"/> <Cell ss:StyleID="s17"/> <Cell ss:Index="10" ss:StyleID="s16"><Data ss:Type="String">Store Number</Data></Cell> <Cell ss:MergeAcross="1" ss:StyleID="s86"><Data ss:Type="Number">'+str_no+'</Data></Cell></Row>';
XML=XML+'<Row ss:Height="15"> <Cell ss:Index="2" ss:MergeAcross="3" ss:StyleID="s58"><Data ss:Type="String">Po Box 1275, Collingwood, VIC 3066</Data></Cell> <Cell ss:Index="10" ss:StyleID="s16"><Data ss:Type="String">Net Amount Due</Data></Cell> <Cell ss:MergeAcross="1" ss:StyleID="s85"><Data ss:Type="String">'+total+'</Data></Cell> </Row>';
XML=XML+'<Row ss:Height="15"> <Cell ss:Index="2" ss:MergeAcross="1" ss:StyleID="s58"><Data ss:Type="String">A.B.N. 50 606 166 741</Data></Cell> <Cell ss:Index="10" ss:StyleID="s18"><Data ss:Type="String">Currency</Data></Cell> <Cell ss:MergeAcross="1" ss:StyleID="s87"><Data ss:Type="String">'+curr_type+'</Data></Cell> </Row> <Row ss:Index="12" ss:Height="15"> <Cell ss:Index="2" ss:StyleID="s16"><Data ss:Type="String">Invoice To</Data></Cell> </Row>';
for (var i = 0; i < addressArray.length; i++) {
XML=XML+'<Row ss:Height="14.25"> <Cell ss:Index="2" ss:StyleID="s17"><Data ss:Type="String">'+addressArray[i]+'</Data></Cell> </Row>';
}
XML=XML+'<Row ss:Height="14.25"> <Cell ss:Index="2" ss:StyleID="s17"><Data ss:Type="String"> </Data></Cell> </Row>';
XML=XML+'<Row ss:AutoFitHeight="0" ss:Height="21.9375"> <Cell ss:Index="2" ss:StyleID="s24"><Data ss:Type="String">Date</Data></Cell> <Cell ss:MergeAcross="2" ss:StyleID="m42774968"><Data ss:Type="String">Description</Data></Cell> <Cell ss:MergeAcross="1" ss:StyleID="m42774988"><Data ss:Type="String">Invoice Amount</Data></Cell> <Cell ss:MergeAcross="1" ss:StyleID="m42775508"><Data ss:Type="String">Payment / Credit</Data></Cell> <Cell ss:MergeAcross="1" ss:StyleID="m42775068"><Data ss:Type="String">Unpaid Amount</Data></Cell> <Cell ss:MergeAcross="1" ss:StyleID="m36094912"><Data ss:Type="String">Running Balance</Data></Cell> </Row>';
XML=XML+'<Row ss:AutoFitHeight="0" ss:Height="12.9375"> <Cell ss:Index="2" ss:StyleID="s20"><Data ss:Type="String">'+date_old+'</Data></Cell> <Cell ss:MergeAcross="2" ss:StyleID="s65"><Data ss:Type="String">Balance Forward</Data></Cell> <Cell ss:MergeAcross="1" ss:StyleID="s73"/> <Cell ss:MergeAcross="1" ss:StyleID="s79"/> <Cell ss:MergeAcross="1" ss:StyleID="s60"><Data ss:Type="Number">'+colBal+'</Data></Cell> <Cell ss:MergeAcross="1" ss:StyleID="s60"><Data ss:Type="Number">'+colBal+'</Data></Cell> </Row>';
for (var i = 0; i <count; i++) {
if(itemArray[i].amount>=0){
XML=XML+'<Row ss:AutoFitHeight="0" ss:Height="12.9375"> <Cell ss:Index="2" ss:StyleID="s20"> <Data ss:Type="String">'+itemArray[i].tr_date+'</Data> </Cell> <Cell ss:MergeAcross="2" ss:StyleID="s65"> <Data ss:Type="String">'+itemArray[i].type+' #'+itemArray[i].docno+'</Data> </Cell> <Cell ss:MergeAcross="1" ss:StyleID="s60"> <Data ss:Type="Number">'+itemArray[i].amount+'</Data> </Cell> <Cell ss:MergeAcross="1" ss:StyleID="s73"/> <Cell ss:MergeAcross="1" ss:StyleID="s60"> <Data ss:Type="Number">'+itemArray[i].amount+'</Data> </Cell> <Cell ss:MergeAcross="1" ss:StyleID="s60"> <Data ss:Type="Number">'+itemArray[i].runningBalance+'</Data> </Cell> </Row>';
}
else if(itemArray[i].amount<0){
itemArray[i].amount=itemArray[i].amount*(-1);
XML=XML+'<Row ss:Height="15"> <Cell ss:Index="2" ss:StyleID="s21"><Data ss:Type="String">'+itemArray[i].tr_date+'</Data></Cell> <Cell ss:MergeAcross="2" ss:StyleID="s21"><Data ss:Type="String">'+itemArray[i].type+' #'+itemArray[i].docno+'</Data></Cell> <Cell ss:MergeAcross="1" ss:StyleID="s74"/> <Cell ss:MergeAcross="1" ss:StyleID="m42775688"><Data ss:Type="Number">'+itemArray[i].amount+'</Data></Cell> <Cell ss:MergeAcross="1" ss:StyleID="s75"><Data ss:Type="Number">'+itemArray[i].amountremain+'</Data></Cell> <Cell ss:MergeAcross="1" ss:StyleID="s60"> <Data ss:Type="Number">'+itemArray[i].runningBalance+'</Data> </Cell> </Row>';
}
}
XML=XML+'<Row ss:AutoFitHeight="0" ss:Height="12.9375"> <Cell ss:Index="2" ss:MergeAcross="1" ss:StyleID="s59"><ss:Data ss:Type="String" xmlns="http://www.w3.org/TR/REC-html40"><B><Font html:Color="#FFFFFF">Current</Font></B></ss:Data></Cell> <Cell ss:MergeAcross="1" ss:StyleID="s63"><Data ss:Type="String">1-30 Days</Data></Cell> <Cell ss:MergeAcross="1" ss:StyleID="s64"><Data ss:Type="String">31-60 Days</Data></Cell> <Cell ss:MergeAcross="1" ss:StyleID="s59"><ss:Data ss:Type="String" xmlns="http://www.w3.org/TR/REC-html40"><B><Font html:Color="#FFFFFF">61-90 Days</Font></B></ss:Data></Cell> <Cell ss:MergeAcross="1" ss:StyleID="m42776728"><ss:Data ss:Type="String" xmlns="http://www.w3.org/TR/REC-html40"><B><Font html:Color="#FFFFFF">Over 90 Days</Font></B></ss:Data></Cell> <Cell ss:MergeAcross="1" ss:StyleID="s59"><ss:Data ss:Type="String" xmlns="http://www.w3.org/TR/REC-html40"><B><Font html:Color="#FFFFFF">Amount Due</Font></B></ss:Data></Cell> </Row>';
XML=XML+'<Row ss:Height="14.25"> <Cell ss:Index="2" ss:MergeAcross="1" ss:StyleID="s60"><Data ss:Type="Number">'+current+'</Data></Cell> <Cell ss:MergeAcross="1" ss:StyleID="s60"><Data ss:Type="Number">'+lastmonth+'</Data></Cell> <Cell ss:MergeAcross="1" ss:StyleID="s60"><Data ss:Type="Number">'+last2month+'</Data></Cell> <Cell ss:MergeAcross="1" ss:StyleID="s60"><Data ss:Type="Number">'+last3month+'</Data></Cell> <Cell ss:MergeAcross="1" ss:StyleID="s60"><Data ss:Type="Number">'+over90+'</Data></Cell> <Cell ss:MergeAcross="1" ss:StyleID="s60"><Data ss:Type="Number">'+total+'</Data></Cell> <Cell ss:StyleID="s19"/> </Row>';
XML=XML+'<Row ss:Height="14.25"> <Cell ss:Index="2" ss:StyleID="s17"><Data ss:Type="String">Bank: CBA</Data></Cell> </Row> <Row ss:Height="14.25"> <Cell ss:Index="2" ss:StyleID="s17"><Data ss:Type="String">Name: Telegram Group Pty Ltd Trading as Telegram Co. BSB 062 000</Data></Cell> </Row> <Row ss:Height="14.25"> <Cell ss:Index="2" ss:StyleID="s17"><Data ss:Type="String">Account: 16226908 </Data></Cell> </Row> </Table>';
XML=XML+'<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"> <PageSetup> <Header x:Margin="0.3"/> <Footer x:Margin="0.3"/> <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/> </PageSetup> <Selected/> <Panes> <Pane> <Number>3</Number> <ActiveRow>5</ActiveRow> <ActiveCol>3</ActiveCol> </Pane> </Panes> <ProtectObjects>False</ProtectObjects> <ProtectScenarios>False</ProtectScenarios> </WorksheetOptions> </Worksheet> </Workbook>';
return XML;
}
/*******************************************************************************
* Creating Excel file of the customer statement and store the file in a folder and returning the file id
*/
function generateDataFile(data, encode, file,csId) {
var strXmlEncoded = encode.convert({
string : data,
inputEncoding : encode.Encoding.UTF_8,
outputEncoding : encode.Encoding.BASE_64
});
var objXlsFile = file.create({
name : 'Customer Statement.xls',
fileType : file.Type.EXCEL,
contents : strXmlEncoded
});
objXlsFile.folder = 194690;
var fileId = objXlsFile.save();
//logme('fileId',fileId);
return fileId;
}
/*******************************************************************************
* Fix text
*
* @param argument
* @returns Created By rosemol on 15-Nov-2017 4:30:21 PM
*/
function escape_for_xml(argument, xml) {
if (argument != "" && argument != null) {
var newString = xml.escape({
xmlText : argument
});
newString = newString.replace(/&/g, '&');
newString = newString.replace(/</g, '<');
newString = newString.replace(/>/g, '>');
newString = newString.replace(/"/g, '"');
newString = newString.replace(/'/g, ''');
return newString;
} else {
return "";
}
}User Event script for setup Email
/**
* @NApiVersion 2.x
* @NScriptType UserEventScript
* @NModuleScope SameAccount
* @author Jobin and Jismi IT services LLP
* @appliedtorecord Message
*
*/
/**
* Script Description: This script for setting the recipient, subject, message and attaching the created excel file in email message
*/
/*******************************************************************************
* * Telegram* *
* **************************************************************************
* Date:9/2/18
* Script name: TG UE Attach Excel
* Script id: customscript_tg_ue_attach_excel
* Deployment id: customdeploy_tg_ue_attach_excel
* Applied to: Message Record
*
******************************************************************************/
define(
[ 'N/email', 'N/http', 'N/https', 'N/record', 'N/runtime',
'N/ui/serverWidget', 'N/url', 'N/redirect' ],
function(email, http, https, record, runtime, serverWidget, url,
redirect) {
/**
* Function definition to be triggered before record is loaded.
*
* @param {Object}
* scriptContext
* @param {Record}
* scriptContext.newRecord - New record
* @param {string}
* scriptContext.type - Trigger type
* @param {Form}
* scriptContext.form - Current form
* @Since 2015.2
*/
function beforeLoad(scriptContext) {
try {
var entryString = scriptContext.newRecord.getValue({
fieldId : 'entryformquerystring'
});
var indexOfiscustom = entryString.indexOf('iscustom');
log.debug({
title : 'b4loadindexOfiscustom',
details : indexOfiscustom
});
log.debug({
title : 'b4loadentryString',
details : entryString
});
if (indexOfiscustom > 0) {
var request = scriptContext.request;
var isCustom = request.parameters.iscustom;
var emailId = request.parameters.recepient;
var mediaitem = request.parameters.mediaitem;
var msg = request.parameters.msg;
//Check whether message created is custom
if (isCustom == 't') {
log.debug({
title : "isCustom",
details : isCustom
});
var objRecord = scriptContext.newRecord;
log.debug({
title : "recipientemail",
details : emailId
});
log.debug({
title : "msg",
details : msg
});
//Set the recipient mail id
objRecord.setText({
fieldId: 'recipientemail',
text: emailId,
ignoreFieldChange: true
});
//Set the Message body
objRecord.setText({
fieldId: 'message',
text: msg,
ignoreFieldChange: true
});
//Attaching the created Excel file in the email
objRecord.setSublistValue({
sublistId: 'mediaitem',
fieldId: 'mediaitem',
value: mediaitem,
line:0,
ignoreFieldChange: true
});
function logme(title, details) {
log.debug({
title : title,
details : details
});
}
}
}
} catch (e) {
log.debug({
title : 'EmailWidget',
details : e.message
});
}
}
return {
beforeLoad : beforeLoad
};
});