/**
* @NApiVersion 2.1
* @NScriptType Suitelet
*/
/*************************************************************************************************************************
* CLIENTNAME:
* AMVOP-117
* Vat Report
* **********************************************************************************************************************
* Date 23/07/2021
*
* Author: Jobin & Jismi IT Services LLP
* Script Description : This Script is to create suitelet page for vat report
* Date created : 23/07/2021
*
* REVISION HISTORY
*
* Revision 1.0 ${23/07/2021} created
*
*
**************************************************************************************************************************/
define(['N/record', 'N/search', 'N/ui/serverWidget', 'N/file', 'N/encode', 'N/format', 'N/format/i18n'],
/**
* @param{record} record
* @param{search} search
* @param{serverWidget} serverWidget
* @param{file} file
* @param{encode} encode
* @param{format} format
* @param{i18n} i18n
*/
(record, search, serverWidget, file, encode, format, formati) => {
/**
* 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 obj = {};
if (scriptContext.request.method == 'POST') {
var objdata = scriptContext.request.parameters.custpage_jsondata;
downloadExcel(scriptContext, objdata);
} else {
var fromDate = checkForParameter(scriptContext.request.parameters.fromdate, '');
var toDate = checkForParameter(scriptContext.request.parameters.todate, '');
var transactionSearchObj1 = search.create({
type: "transaction",
filters:
[],
columns:
[
search.createColumn({
name: "formulatext",
formula: "TO_CHAR(TO_DATE('" + fromDate + "','YYYY-MM-DD'))",
label: "fromDate"
}),
search.createColumn({
name: "formulatext",
formula: "TO_CHAR(TO_DATE('" + toDate + "','YYYY-MM-DD'))",
label: "toDate"
}),
search.createColumn({
name: "formulatext",
formula: "TO_CHAR(TO_DATE(TRUNC({today}, 'MONTH'), 'DD/MM/YYYY'))",
label: "firstdate"
}),
search.createColumn({
name: "formulatext",
formula: "TO_CHAR(LAST_DAY(TRUNC({today}, 'MONTH')))",
label: "lastdate"
})
]
});
var searchResultCount3 = transactionSearchObj1.runPaged().count;
var objects = {};
transactionSearchObj1.run().each(function (result) {
// .run().each has a limit of 4,000 results
for (var i = 0; i < transactionSearchObj1.columns.length; i++) {
objects[transactionSearchObj1.columns[i].label] = result.getValue(transactionSearchObj1.columns[i]);
}
return false;
});
var filterarray;
if (fromDate == "" && toDate == "") {
filterarray = ["trandate", "within", objects.firstdate, objects.lastdate];
} else {
filterarray = ["trandate", "within", objects.fromDate, objects.toDate];
}
var filterValues;
if (fromDate == "" && toDate == "") {
filterValues = [objects.firstdate, objects.lastdate]
objects.fromDate = objects.firstdate;
objects.toDate = objects.lastdate;
} else {
filterValues = [objects.fromDate, objects.toDate];
}
var totalamount = 0;
var totalvat = 0;
var totalpurchaseamount = 0;
var totalpurchasevat = 0;
var titlecontent = objects.fromDate + " - " + objects.toDate;
obj.fromDate = objects.fromDate;
obj.toDate = objects.toDate;
var soVatSearch = search.load({
id: 'customsearch235',
});
var filters = soVatSearch.filters;
var filterOne = search.createFilter({
name: 'trandate',
operator: search.Operator.WITHIN,
values: filterValues
});
filters.push(filterOne);
var searchResultCount = soVatSearch.runPaged().count;
log.debug("salesorderSearchObj result count", searchResultCount);
soVatSearch.run().each(function (result) {
var vatonsales = result.getValue(soVatSearch.columns[0]);
if (vatonsales == '<b>Standard rated sales</b>') {
obj.stdRateSalesamount = result.getValue(soVatSearch.columns[1]);
totalamount = Number(totalamount) + Number(checkForParameter(obj.stdRateSalesamount, 0));
} else if (vatonsales == '<b>Exempt sales</b>') {
obj.exemptSaleAmount = result.getValue(soVatSearch.columns[1]);
totalamount = Number(totalamount) + Number(checkForParameter(obj.exemptSaleAmount, 0));
} else if (vatonsales == '<b>Exports</b>') {
obj.exportsAmount = result.getValue(soVatSearch.columns[1]);
totalamount = Number(totalamount) + Number(checkForParameter(obj.exportsAmount, 0));
} else if (vatonsales == '<b>Zero rated domestic sales</b>') {
obj.zeroratedAmount = result.getValue(soVatSearch.columns[1]);
totalamount = Number(totalamount) + Number(checkForParameter(obj.zeroratedAmount, 0));
}
if (vatonsales == '<b>Standard rated sales</b>') {
obj.stdRateSalesVat = result.getValue(soVatSearch.columns[2]);
totalvat = Number(totalvat) + Number(checkForParameter(obj.stdRateSalesVat, 0));
} else if (vatonsales == '<b>Exempt sales</b>') {
obj.exemptSaleVat = result.getValue(soVatSearch.columns[2]);
totalvat = Number(totalvat) + Number(checkForParameter(obj.exemptSaleVat, 0));
} else if (vatonsales == '<b>Exports</b>') {
obj.exportsVat = result.getValue(soVatSearch.columns[2]);
totalvat = Number(totalvat) + Number(checkForParameter(obj.exportsVat, 0));
} else if (vatonsales == '<b>Zero rated domestic sales</b>') {
obj.zeroratedVat = result.getValue(soVatSearch.columns[2]);
totalvat = Number(totalvat) + Number(checkForParameter(obj.zeroratedVat, 0));
}
return true;
});
obj.totalamount = totalamount;
obj.totalvat = totalvat;
var poVatSearch = search.load({
id: 'customsearch257',
});
var vatfilters = poVatSearch.filters;
var vatfilterOne = search.createFilter({
name: 'trandate',
operator: search.Operator.WITHIN,
values: filterValues
});
vatfilters.push(vatfilterOne);
var searchResultCount = poVatSearch.runPaged().count;
log.debug("purchaseorderSearchObj result count", searchResultCount);
poVatSearch.run().each(function (result) {
var vatonpurchase = result.getValue(poVatSearch.columns[0]);
if (vatonpurchase == '<b>Standard rated domestic purchases</b>') {
obj.pstdRateDomesticamount = result.getValue(poVatSearch.columns[1]);
totalpurchaseamount = Number(totalpurchaseamount) + Number(checkForParameter(obj.pstdRateDomesticamount, 0));
} else if (vatonpurchase == '<b>Exempt purchases</b>') {
obj.pexemptPurchasesAmount = result.getValue(poVatSearch.columns[1]);
totalpurchaseamount = Number(totalpurchaseamount) + Number(checkForParameter(obj.pexemptPurchasesAmount, 0));
} else if (vatonpurchase == '<b>Imports Subject to VAT Paid at Customs</b>') {
obj.pimportsubjectAmount = result.getValue(poVatSearch.columns[1]);
totalpurchaseamount = Number(totalpurchaseamount) + Number(checkForParameter(obj.pimportsubjectAmount, 0));
} else if (vatonpurchase == '<b>Zero rated purchases</b>') {
obj.pzeroRatedPurchaseAmount = result.getValue(poVatSearch.columns[1]);
totalpurchaseamount = Number(totalpurchaseamount) + Number(checkForParameter(obj.pzeroRatedPurchaseAmount, 0));
} else if (vatonpurchase == '<b>Imports Subject to VAT Accounted for through the Reverse Charge Mechanism</b>') {
obj.pimportsubjectreverseAmount = result.getValue(poVatSearch.columns[1]);
totalpurchaseamount = Number(totalpurchaseamount) + Number(checkForParameter(obj.pimportsubjectreverseAmount, 0));
}
if (vatonpurchase == '<b>Standard rated domestic purchases</b>') {
obj.pstdRateDomesticVat = result.getValue(poVatSearch.columns[2]);
log.debug('obj.pstdRateDomesticVat 363',obj.pstdRateDomesticVat);
totalpurchasevat = Number(totalpurchasevat) + Number(checkForParameter(obj.pstdRateDomesticVat, 0));
} else if (vatonpurchase == '<b>Exempt purchases</b>') {
obj.pexemptPurchasesVat = result.getValue(poVatSearch.columns[2]);
totalpurchasevat = Number(totalpurchasevat) + Number(checkForParameter(obj.pexemptPurchasesVat, 0));
} else if (vatonpurchase == '<b>Imports Subject to VAT Paid at Customs</b>') {
obj.pimportsubjectVat = result.getValue(poVatSearch.columns[2]);
totalpurchasevat = Number(totalpurchasevat) + Number(checkForParameter(obj.pimportsubjectVat, 0));
} else if (vatonpurchase == '<b>Zero rated purchases</b>') {
obj.pzeroRatedPurchaseVat = result.getValue(poVatSearch.columns[2]);
totalpurchasevat = Number(totalpurchasevat) + Number(checkForParameter(obj.pzeroRatedPurchaseVat, 0));
} else if (vatonpurchase == '<b>Imports Subject to VAT Accounted for through the Reverse Charge Mechanism</b>') {
obj.pimportsubjectreverseVat = result.getValue(poVatSearch.columns[2]);
totalpurchasevat = Number(totalpurchasevat) + Number(checkForParameter(obj.pimportsubjectreverseVat, 0));
}
return true;
});
var netvat = totalvat - totalpurchasevat
obj.totalpurchaseamount = totalpurchaseamount;
obj.totalpurchasevat = totalpurchasevat;
obj.netvat = netvat;
obj.stdRateSalesamount = checkForParameter(obj.stdRateSalesamount, '- ');
if (obj.stdRateSalesamount != '- ') {
obj.stdRateSalesamount = makeItCurrency(Number(obj.stdRateSalesamount)).replace("SAR", "");
}
obj.exemptSaleAmount = checkForParameter(obj.exemptSaleAmount, '- ');
if (obj.exemptSaleAmount != '- ') {
obj.exemptSaleAmount = makeItCurrency(Number(obj.exemptSaleAmount)).replace("SAR", "");
}
obj.exportsAmount = checkForParameter(obj.exportsAmount, '- ');
if (obj.exportsAmount != '- ') {
obj.exportsAmount = makeItCurrency(Number(obj.exportsAmount)).replace("SAR", "");
}
obj.zeroratedAmount = checkForParameter(obj.zeroratedAmount, '- ');
if (obj.zeroratedAmount != '- ') {
obj.zeroratedAmount = makeItCurrency(Number(obj.zeroratedAmount)).replace("SAR", "");
}
obj.stdRateSalesVat = checkForParameter(obj.stdRateSalesVat, '- ');
if (obj.stdRateSalesVat != '- ') {
obj.stdRateSalesVat = makeItCurrency(Number(obj.stdRateSalesVat)).replace("SAR", "");
}
obj.exemptSaleVat = checkForParameter(obj.exemptSaleVat, '- ');
if (obj.exemptSaleVat != '- ') {
obj.exemptSaleVat = makeItCurrency(Number(obj.exemptSaleVat)).replace("SAR", "");
}
obj.exportsVat = checkForParameter(obj.exportsVat, '- ');
if (obj.exportsVat != '- ') {
obj.exportsVat = makeItCurrency(Number(obj.exportsVat)).replace("SAR", "");
}
obj.zeroratedVat = checkForParameter(obj.zeroratedVat, '- ');
if (obj.zeroratedVat != '- ') {
obj.zeroratedVat = makeItCurrency(Number(obj.zeroratedVat)).replace("SAR", "");
}
obj.pstdRateDomesticamount = checkForParameter(obj.pstdRateDomesticamount, '- ');
if (obj.pstdRateDomesticamount != '- ') {
obj.pstdRateDomesticamount = makeItCurrency(Number(obj.pstdRateDomesticamount)).replace("SAR", "");
}
obj.pexemptPurchasesAmount = checkForParameter(obj.pexemptPurchasesAmount, '- ');
if (obj.pexemptPurchasesAmount != '- ') {
obj.pexemptPurchasesAmount = makeItCurrency(Number(obj.pexemptPurchasesAmount)).replace("SAR", "");
}
obj.pimportsubjectAmount = checkForParameter(obj.pimportsubjectAmount, '- ');
if (obj.pimportsubjectAmount != '- ') {
obj.pimportsubjectAmount = makeItCurrency(Number(obj.pimportsubjectAmount)).replace("SAR", "");
}
obj.pzeroRatedPurchaseAmount = checkForParameter(obj.pzeroRatedPurchaseAmount, '- ');
if (obj.pzeroRatedPurchaseAmount != '- ') {
obj.pzeroRatedPurchaseAmount = makeItCurrency(Number(obj.pzeroRatedPurchaseAmount)).replace("SAR", "");
}
obj.pimportsubjectreverseAmount = checkForParameter(obj.pimportsubjectreverseAmount, '- ');
if (obj.pimportsubjectreverseAmount != '- ') {
obj.pimportsubjectreverseAmount = makeItCurrency(Number(obj.pimportsubjectreverseAmount)).replace("SAR", "");
}
obj.pstdRateDomesticVat = checkForParameter(obj.pstdRateDomesticVat, '- ');
if (obj.pstdRateDomesticVat != '- ') {
obj.pstdRateDomesticVat = makeItCurrency(Number(obj.pstdRateDomesticVat)).replace("SAR", "");
}
obj.pexemptPurchasesVat = checkForParameter(obj.pexemptPurchasesVat, '- ');
if (obj.pexemptPurchasesVat != '- ') {
obj.pexemptPurchasesVat = makeItCurrency(Number(obj.pexemptPurchasesVat)).replace("SAR", "");
}
obj.pimportsubjectVat = checkForParameter(obj.pimportsubjectVat, '- ');
if (obj.pimportsubjectVat != '- ') {
obj.pimportsubjectVat = makeItCurrency(Number(obj.pimportsubjectVat)).replace("SAR", "");
}
obj.pzeroRatedPurchaseVat = checkForParameter(obj.pzeroRatedPurchaseVat, '- ');
if (obj.pzeroRatedPurchaseVat != '- ') {
obj.pzeroRatedPurchaseVat = makeItCurrency(Number(obj.pzeroRatedPurchaseVat)).replace("SAR", "");
}
obj.pimportsubjectreverseVat = checkForParameter(obj.pimportsubjectreverseVat, '- ');
if (obj.pimportsubjectreverseVat != '- ') {
obj.pimportsubjectreverseVat = makeItCurrency(Number(obj.pimportsubjectreverseVat)).replace("SAR", "");
}
obj.totalamount = checkForParameter(obj.totalamount, '- ');
if (obj.totalamount != '- ') {
obj.totalamount = makeItCurrency(Number(obj.totalamount)).replace("SAR", "");
}
obj.totalvat = checkForParameter(obj.totalvat, '- ');
if (obj.totalvat != '- ') {
obj.totalvat = makeItCurrency(Number(obj.totalvat)).replace("SAR", "");
}
obj.totalpurchaseamount = checkForParameter(obj.totalpurchaseamount, '- ');
if (obj.totalpurchaseamount != '- ') {
obj.totalpurchaseamount = makeItCurrency(Number(obj.totalpurchaseamount)).replace("SAR", "");
}
obj.totalpurchasevat = checkForParameter(obj.totalpurchasevat, '- ');
if (obj.totalpurchasevat != '- ') {
obj.totalpurchasevat = makeItCurrency(Number(obj.totalpurchasevat)).replace("SAR", "");
}
obj.netvat = checkForParameter(obj.netvat, '- ');
var netnegative = false;
if(Number(obj.netvat)<0){
netnegative = true;
obj.netvat = (obj.netvat)*-1
}
if (obj.netvat != '- ') {
obj.netvat = makeItCurrency(Number(obj.netvat)).replace(" SAR", "");
}
var netamountvalue = obj.netvat
if(netnegative == true){
netamountvalue = '('+obj.netvat+')';
}
obj.netamountvalue = netamountvalue;
var content =
'<html><head>' +
'<style>' +
'table.maintable{width:80%; border:2px solid #85a3e0; border-collapse: collapse;}' +
'table.subtable{width:100%; border-collapse: collapse; padding: 0;}' +
'td.headmain{text-align: center; font-weight: bold; font-size: 15px;}' +
'td.head2{text-align: center; border:1px solid #85a3e0; background-color: #404040; color: white; font-weight: bold; font-size: 14px;}' +
'td.tdwhite{border:1px solid #85a3e0; font-size: 14px;}' +
'td.tdgreen{border:1px solid #85a3e0; background-color: #f2ffe6; font-size: 14px;}' +
'td.tdpink{border:1px solid #85a3e0; background-color: #ffe6e6; font-size: 14px;}' +
'td.tdgray{border:1px solid #85a3e0; background-color: #bfbfbf; font-size: 14px;}' +
'.dateFilter{width:50%; border: none; padding: 5px 5px; text-align: center; text-decoration: none; display: inline-block; font-size: 14px; margin: 2px 2px; cursor: pointer; border-radius: 2px;}' +
'input[type="date"] {' +
'font-family: "Helvetica", arial, sans-serif;' +
'font-size: 14px;' +
'border:1px solid #ecf0f1;' +
'background:#ecf0f1;' +
'padding:5px;' +
'display: inline-block !important;' +
'visibility: visible !important;' +
'}' +
'input[type="date"], focus {' +
' color: #95a5a6;' +
' box-shadow: none;' +
' -webkit-box-shadow: none;' +
' -moz-box-shadow: none;' +
'}' +
'</style>' +
'</head>' +
'<body>' +
// '<form method="post">' +
// '<button type="submit">ExportasExcel</button>' +
'<table align="center" width="65%" ><tr><td align="left" width="35%" style="font-size:14px;"><b><label>From Date:</label></b><input type="date" id="fromDate" name="fromDate"></td>' +
'<td width="35%" style="font-size:14px;"><b><label>To Date:</label></b><input type="date" id="toDate" name="toDate"></td>' +
'<td align="left"><button type="button" class="dateFilter" onclick="myFunction()">Filter</button></td></tr></table>' +
'<table style="height: 20px;width: 100%"><tr><td></td></tr></table>' +
'<table class="maintable">' +
'<tr><td class="headmain" style="width:87%; padding:0px; text-align: center; border-collapse: collapse; border-right: 1px solid #85a3e0;">VAT Summary</td>' +
'<td class="headmain" style="width:13%; text-align: center; padding:0px; border-collapse: collapse;">' + titlecontent + '</td></tr>' +
'<tr><td colspan="2" style="padding: 0; border-collapse: collapse;">' +
'<table class="subtable">' +
'<tr><td class="head2" style="width:5%; border-left: none;">Sr. #</td><td class="head2" style="width:60%;">VAT on Sales</td><td class="head2" style="width:10%;">Amount (SAR)</td><td class="head2" style="width:12%;">Adjustment (SAR)</td><td class="head2" style="width:13%; border-right: none;">VAT (SAR)</td></tr>' +
'<tr><td class="tdwhite" style="text-align: center; border-left: none;">1</td><td class="tdwhite">Standard rated sales</td>' +
'<td class="tdgreen" style="text-align: right; padding-right: 5px;"><b>' + obj.stdRateSalesamount + '</b></td>' +
'<td class="tdwhite"></td><td class="tdgreen" style="text-align: right; padding-right: 5px; border-right: none;"><b>' + obj.stdRateSalesVat + '</b></td></tr>' +
// '<tr><td class="tdwhite" style="text-align: center">2</td><td class="tdwhite">Private healthcare/private education sales to citizens</td>' +
// '<td class="tdgreen"></td><td class="tdwhite"></td><td class="tdgreen"></td></tr>' +
'<tr><td class="tdwhite" style="text-align: center; border-left: none;">2</td><td class="tdwhite">Zero rated domestic sales</td>' +
'<td class="tdgreen" style="text-align: right; padding-right: 5px;"><b>' + obj.zeroratedAmount + '</b></td>' +
'<td class="tdwhite"></td><td class="tdgreen" style="text-align: right; padding-right: 5px; border-right: none;"><b>' + obj.zeroratedVat + '</b></td></tr>' +
'<tr><td class="tdwhite" style="text-align: center; border-left: none;">3</td><td class="tdwhite">Exports</td>' +
'<td class="tdgreen" style="text-align: right; padding-right: 5px;"><b>' + obj.exportsAmount + '</b></td>' +
'<td class="tdwhite"></td><td class="tdgreen" style="text-align: right; padding-right: 5px; border-right: none;"><b>' + obj.exportsVat + '</b></td></tr>' +
'<tr><td class="tdwhite" style="text-align: center; border-left: none;">4</td><td class="tdwhite">Exempt sales</td>' +
'<td class="tdgreen" style="text-align: right; padding-right: 5px;"><b>' + obj.exemptSaleAmount + '</b></td>' +
'<td class="tdwhite"></td><td class="tdgreen" style="text-align: right; padding-right: 5px; border-right: none;"><b>' + obj.exemptSaleVat + '</b></td></tr>' +
'<tr><td class="tdgreen" style="text-align: center; border-left: none;"><b></b></td><td class="tdgreen" style="text-align: center"><b>Total Sales</b></td>' +
'<td class="tdgreen" style="text-align: right; padding-right: 5px;"><b>' + obj.totalamount + '</b></td><td class="tdgreen"></td>' +
'<td class="tdgreen" style="text-align: right; padding-right: 5px; border-right: none;"><b>' + obj.totalvat + '</b></td></tr>' +
'</table>' +
'</td></tr>' +
'<tr><td style="width:87%; padding:0px; border-collapse: collapse; "> </td>' +
'<td style="width:13%; padding:0px; border-collapse: collapse;"> </td></tr>' +
'<tr><td colspan="2" style="padding: 0; border-collapse: collapse;">' +
'<table class="subtable">' +
'<tr><td class="head2" style="width:5%; border-left: none"></td><td class="head2" style="width:60%;">VAT on Purchases</td><td class="head2" style="width:10%;">Amount (SAR)</td>' +
'<td class="head2" style="width:12%;">Adjustment (SAR)</td><td class="head2" style="width:13%; border-right: none;">VAT (SAR)</td></tr>' +
'<tr><td class="tdwhite" style="text-align: center; border-left: none;">5</td><td class="tdwhite">Standard rated domestic purchases</td>' +
'<td class="tdpink" style="text-align: right; padding-right: 5px;"><b>' + obj.pstdRateDomesticamount + '</b></td><td class="tdwhite"></td><td class="tdpink" style="text-align: right; padding-right: 5px; border-right: none;"><b>' + obj.pstdRateDomesticVat + '</b></td></tr>' +
'<tr><td class="tdwhite" style="text-align: center; border-left: none;">6</td><td class="tdwhite">Imports Subject to VAT Paid at Customs</td>' +
'<td class="tdpink" style="text-align: right; padding-right: 5px;"><b>' + obj.pimportsubjectAmount + '</b></td><td class="tdwhite"></td><td class="tdpink" style="text-align: right; padding-right: 5px; border-right: none;"><b>' +obj.pimportsubjectVat + '</b></td></tr>' +
'<tr><td class="tdwhite" style="text-align: center; border-left: none;">7</td><td class="tdwhite">Imports Subject to VAT Accounted for through the Reverse Charge Mechanism</td>' +
'<td class="tdpink" style="text-align: right; padding-right: 5px;"><b>' + obj.pimportsubjectreverseAmount + '</b></td><td class="tdwhite"></td><td class="tdpink" style="text-align: right; padding-right: 5px; border-right: none;"><b>' + obj.pimportsubjectreverseVat + '</b></td></tr>' +
'<tr><td class="tdwhite" style="text-align: center; border-left: none;">8</td><td class="tdwhite">Zero rated purchases</td>' +
'<td class="tdpink" style="text-align: right; padding-right: 5px;"><b>' + obj.pzeroRatedPurchaseAmount + '</b></td><td class="tdwhite"></td><td class="tdpink" style="text-align: right; padding-right: 5px; border-right: none;"><b>' + obj.pzeroRatedPurchaseVat + '</b></td></tr>' +
'<tr><td class="tdwhite" style="text-align: center; border-left: none;">9</td><td class="tdwhite">Exempt purchases</td>' +
'<td class="tdpink" style="text-align: right; padding-right: 5px;"><b>' + obj.pexemptPurchasesAmount + '</b></td><td class="tdwhite"></td><td class="tdpink" style="text-align: right; padding-right: 5px; border-right: none;"><b>' + obj.pexemptPurchasesVat + '</b></td></tr>' +
'<tr><td class="tdpink" style="text-align: center; border-left: none;"><b></b></td><td class="tdpink" style="text-align: center"><b>Total Purchases</b></td>' +
'<td class="tdpink" style="text-align: right; padding-right: 5px;"><b>' + obj.totalpurchaseamount + '</b></td><td class="tdpink"></td><td class="tdpink" style="text-align: right; padding-right: 5px; border-right: none;"><b>' + obj.totalpurchasevat + '</b></td></tr>' +
'<tr><td class="tdgray" style="border-left: none; border-bottom: none;"></td><td class="tdgray" style="text-align: center; border-bottom: none;"><b>Net VAT</b></td>' +
'<td class="tdgray" style="border-bottom: none;"></td><td class="tdgray" style="border-bottom: none;"></td><td class="tdgray" style="text-align: right; padding-right: 5px; border-right: none; border-bottom: none;"><b>' + netamountvalue + '</b></td></tr>' +
'</table>' +
'</td></tr>' +
'</table>' +
'<script> ' +
'function myFunction(){' +
'var queryString = window.location.search;' +
'var urlParams = new URLSearchParams(queryString);' +
// 'var currentuser='+currentuser+';' +
'var fromDate = document.getElementById("fromDate").value;' +
'var toDate = document.getElementById("toDate").value;' +
//
'if(fromDate=="" && toDate==""){' +
'window.location.href=("/app/site/hosting/scriptlet.nl?script=420&deploy=1&fromdate="+fromDate+"&todate="+toDate);' +
'}' +
'else if(fromDate!=""&& toDate!=""){' +
'if(fromDate>toDate){' +
'alert( "From Date is earlier than To Date" );}' +
'else{' +
'window.location.href=("/app/site/hosting/scriptlet.nl?script=420&deploy=1&fromdate="+fromDate+"&todate="+toDate);' +
'}' +
'}' +
'else{' +
'alert( "Invalid Date filter" );' +
'}' +
'}' +
'</script>' +
'</body></html>';
log.debug('335');
var form = serverWidget.createForm({
title: 'VAT Report'
});
var custpage = form.addField({
id: 'custpage_testcol3',
label: 'User Type',
type: serverWidget.FieldType.INLINEHTML
});
custpage.defaultValue = content
var objdata = JSON.stringify(obj);
var jsondata = form.addField({
id: 'custpage_jsondata',
type: serverWidget.FieldType.LONGTEXT,
label: 'JSONDATA',
}).updateLayoutType({layoutType: serverWidget.FieldLayoutType.ENDROW});
jsondata.defaultValue = objdata;
jsondata.updateDisplayType({
displayType: serverWidget.FieldDisplayType.HIDDEN
});
scriptContext.response.writePage(form);
form.addSubmitButton({
label: 'Export as Excel'
});
}
} catch (e) {
log.debug({
title: e.name,
details: e
});
}
}
//function to download excel
function downloadExcel(scriptContext, objdata) {
try {
log.debug('inside downloadExcel')
// var obj = objdata;
var obj = JSON.parse(objdata);
log.debug('obj==', obj);
log.debug('obj.stdRateSalesamount', obj.stdRateSalesamount);
var xmlStr = '<?xml version="1.0"?><?mso-application progid="Excel.Sheet"?>';
xmlStr += '<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" ';
xmlStr += 'xmlns:o="urn:schemas-microsoft-com:office:office" ';
xmlStr += 'xmlns:x="urn:schemas-microsoft-com:office:excel" ';
xmlStr += 'xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ';
xmlStr += 'xmlns:html="http://www.w3.org/TR/REC-html40">';
xmlStr += '<Styles>'
+ '<Style ss:ID="s63">'
+ '<Alignment ss:Horizontal="Center"/>'
+ '<Borders>\n' +
' <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="2" ss:Color = "#85a3e0"/>\n' +
' </Borders>\n'
+ '<Font x:CharSet="204" ss:Size="11" ss:Color="#000000" ss:Bold="1" />'
+ '</Style>' +
'<Style ss:ID="s631">'
+ '<Alignment ss:Horizontal="Center"/>'
+ '<Borders>\n' +
' <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="2" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="2" ss:Color = "#85a3e0"/>\n' +
' </Borders>\n'
+ '<Font x:CharSet="204" ss:Size="11" ss:Color="#000000" ss:Bold="1" />'
+ '</Style>' +
'<Style ss:ID="plainright">\n' +
'<Alignment ss:Horizontal="Center"/>' +
' <Borders>\n' +
' <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="2" ss:Color = "#85a3e0"/>\n' +
' </Borders>\n' +
' </Style>' +
'<Style ss:ID="scenterw">\n' +
'<Alignment ss:Horizontal="Center"/>' +
' <Borders>\n' +
' <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' </Borders>\n' +
'<Font x:CharSet="204" ss:Size="11"/>'+
' </Style>' +
'<Style ss:ID="swhite">\n' +
' <Borders>\n' +
' <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' </Borders>\n' +
'<Font x:CharSet="204" ss:Size="11"/>'+
' </Style>' +
'<Style ss:ID="centerpink">\n' +
'<Alignment ss:Horizontal="Center"/>' +
' <Borders>\n' +
' <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' </Borders>\n' +
' <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Bold="1"/>\n' +
' <Interior ss:Color="#ffe6e6" ss:Pattern="Solid"/>\n' +
' </Style>' +
'<Style ss:ID="rightpink">\n' +
'<Alignment ss:Horizontal="Right"/>' +
' <Borders>\n' +
' <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' </Borders>\n' +
' <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Bold="1"/>\n' +
' <Interior ss:Color="#ffe6e6" ss:Pattern="Solid"/>\n' +
' </Style>' +
'<Style ss:ID="rightpink1">\n' +
'<Alignment ss:Horizontal="Right"/>' +
' <Borders>\n' +
' <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="2" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' </Borders>\n' +
' <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Bold="1"/>\n' +
' <Interior ss:Color="#ffe6e6" ss:Pattern="Solid"/>\n' +
' </Style>' +
'<Style ss:ID="centergreen">\n' +
'<Alignment ss:Horizontal="Center"/>' +
' <Borders>\n' +
' <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' </Borders>\n' +
' <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Bold="1"/>\n' +
' <Interior ss:Color="#f2ffe6" ss:Pattern="Solid"/>\n' +
' </Style>' +
'<Style ss:ID="rightgreen">\n' +
'<Alignment ss:Horizontal="Right"/>' +
' <Borders>\n' +
' <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' </Borders>\n' +
' <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Bold="1"/>\n' +
' <Interior ss:Color="#f2ffe6" ss:Pattern="Solid"/>\n' +
' </Style>' +
'<Style ss:ID="rightgreen1">\n' +
'<Alignment ss:Horizontal="Right"/>' +
' <Borders>\n' +
' <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="2" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' </Borders>\n' +
' <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Bold="1"/>\n' +
' <Interior ss:Color="#f2ffe6" ss:Pattern="Solid"/>\n' +
' </Style>' +
'<Style ss:ID="rightgray">\n' +
'<Alignment ss:Horizontal="Right"/>' +
' <Borders>\n' +
' <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="2" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="2" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' </Borders>\n' +
' <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Bold="1"/>\n' +
' <Interior ss:Color="#bfbfbf" ss:Pattern="Solid"/>\n' +
' </Style>' +
'<Style ss:ID="Green">\n' +
' <Borders>\n' +
' <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' </Borders>\n' +
' <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Bold="1"/>\n' +
' <Interior ss:Color="#f2ffe6" ss:Pattern="Solid"/>\n' +
' </Style>' +
'<Style ss:ID="Pink">\n' +
' <Borders>\n' +
' <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' </Borders>\n' +
' <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Bold="1"/>\n' +
' <Interior ss:Color="#ffe6e6" ss:Pattern="Solid"/>\n' +
' </Style>' +
'<Style ss:ID="Gray">\n' +
'<Alignment ss:Horizontal="Center"/>' +
' <Borders>\n' +
' <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="2" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' </Borders>\n' +
' <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Bold="1"/>\n' +
' <Interior ss:Color="#bfbfbf" ss:Pattern="Solid"/>\n' +
' </Style>' +
'<Style ss:ID="Black">\n' +
'<Alignment ss:Horizontal="Center"/>' +
' <Borders>\n' +
' <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' </Borders>\n' +
' <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="white" ss:Bold="1"/>\n' +
' <Interior ss:Color="#404040" ss:Pattern="Solid" ss:Bold="1"/>\n' +
' </Style>'+
'<Style ss:ID="Black1">\n' +
'<Alignment ss:Horizontal="Center"/>' +
' <Borders>\n' +
' <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="2" ss:Color = "#85a3e0"/>\n' +
' <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color = "#85a3e0"/>\n' +
' </Borders>\n' +
' <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="white" ss:Bold="1"/>\n' +
' <Interior ss:Color="#404040" ss:Pattern="Solid" ss:Bold="1"/>\n' +
' </Style>'
+ '</Styles>'
xmlStr += '<Worksheet ss:Name="Sheet1">';
xmlStr += '<Table>'
+ '<Column ss:AutoFitWidth="0" ss:Width="50"/>'
+ '<Column ss:AutoFitWidth="0" ss:Width="650"/>'
+ ' <Column ss:AutoFitWidth="0" ss:Width="130"/>'
+ ' <Column ss:AutoFitWidth="0" ss:Width="160"/>'
+ ' <Column ss:AutoFitWidth="0" ss:Width="140"/>'
+ '<Row ss:Height="10">'
+ '<Cell><Data></Data></Cell>'
+ '<Cell><Data></Data></Cell>'
+ '</Row>';
xmlStr += '<Row>'
+ '<Cell ss:MergeAcross="3" ss:StyleID="s63"><Data ss:Type="String"> VAT Summary </Data></Cell>'
+ '<Cell ss:StyleID="s631"><Data ss:Type="String">' + obj.fromDate + ' - ' + obj.toDate + '</Data></Cell>'
+ '</Row>';
xmlStr += '<Row>'
+ '<Cell ss:StyleID="Black"><Data ss:Type="String">Sr. #</Data></Cell>'
+ '<Cell ss:StyleID="Black"><Data ss:Type="String">VAT on Sales</Data></Cell>'
+ '<Cell ss:StyleID="Black"><Data ss:Type="String">Amount (SAR)</Data></Cell>'
+ '<Cell ss:StyleID="Black"><Data ss:Type="String">Adjustment (SAR)</Data></Cell>'
+ '<Cell ss:StyleID="Black1"><Data ss:Type="String">VAT (SAR)</Data></Cell>'
+ '</Row>';
xmlStr += '<Row>'
+ '<Cell ss:StyleID="scenterw"><Data ss:Type="String">1</Data></Cell>'
+ '<Cell ss:StyleID="swhite"><Data ss:Type="String">Standard rated sales</Data></Cell>'
+ '<Cell ss:StyleID="rightgreen"><Data ss:Type="String">' + checkForParameter(obj.stdRateSalesamount, '-') + '</Data></Cell>'
+ '<Cell ss:StyleID="swhite"><Data ss:Type="String"></Data></Cell>'
+ '<Cell ss:StyleID="rightgreen1"><Data ss:Type="String">' + checkForParameter(obj.stdRateSalesVat, '-') + '</Data></Cell>'
+ '</Row>';
// xmlStr += '<Row>'
// + '<Cell ss:StyleID="scenterw"><Data ss:Type="String">2</Data></Cell>'
// + '<Cell ss:StyleID="swhite"><Data ss:Type="String">Private healthcare/private education sales to citizens</Data></Cell>'
// + '<Cell ss:StyleID="rightgreen"><Data ss:Type="String"></Data></Cell>'
// + '<Cell ss:StyleID="swhite"><Data ss:Type="String"></Data></Cell>'
// + '<Cell ss:StyleID="rightgreen"><Data ss:Type="String"></Data></Cell>'
// + '</Row>';
xmlStr += '<Row>'
+ '<Cell ss:StyleID="scenterw"><Data ss:Type="String">2</Data></Cell>'
+ '<Cell ss:StyleID="swhite"><Data ss:Type="String">Zero rated domestic sales</Data></Cell>'
+ '<Cell ss:StyleID="rightgreen"><Data ss:Type="String">' + checkForParameter(obj.zeroratedAmount, '-') + '</Data></Cell>'
+ '<Cell ss:StyleID="swhite"><Data ss:Type="String"> </Data></Cell>'
+ '<Cell ss:StyleID="rightgreen1"><Data ss:Type="String">' + checkForParameter(obj.zeroratedVat, '-') + '</Data></Cell>'
+ '</Row>';
xmlStr += '<Row>'
+ '<Cell ss:StyleID="scenterw"><Data ss:Type="String">3</Data></Cell>'
+ '<Cell ss:StyleID="swhite"><Data ss:Type="String">Exports</Data></Cell>'
+ '<Cell ss:StyleID="rightgreen"><Data ss:Type="String">' + checkForParameter(obj.exportsAmount, '-') + '</Data></Cell>'
+ '<Cell ss:StyleID="swhite"><Data ss:Type="String"> </Data></Cell>'
+ '<Cell ss:StyleID="rightgreen1"><Data ss:Type="String">' + checkForParameter(obj.exportsVat, '-') + '</Data></Cell>'
+ '</Row>';
xmlStr += '<Row>'
+ '<Cell ss:StyleID="scenterw"><Data ss:Type="String">4</Data></Cell>'
+ '<Cell ss:StyleID="swhite"><Data ss:Type="String">Exempt sales</Data></Cell>'
+ '<Cell ss:StyleID="rightgreen"><Data ss:Type="String"><b>' + checkForParameter(obj.exemptSaleAmount, '-') + '</b></Data></Cell>'
+ '<Cell ss:StyleID="swhite"><Data ss:Type="String"> </Data></Cell>'
+ '<Cell ss:StyleID="rightgreen1"><Data ss:Type="String">' + checkForParameter(obj.exemptSaleVat, '-') + '</Data></Cell>'
+ '</Row>';
xmlStr += '<Row>'
+ '<Cell ss:StyleID="centergreen"><Data ss:Type="String"></Data></Cell>'
+ '<Cell ss:StyleID="centergreen"><Data ss:Type="String">Total Sales</Data></Cell>'
+ '<Cell ss:StyleID="rightgreen"><Data ss:Type="String">' + obj.totalamount + '</Data></Cell>'
+ '<Cell ss:StyleID="Green"><Data ss:Type="String"> </Data></Cell>'
+ '<Cell ss:StyleID="rightgreen1"><Data ss:Type="String">' + obj.totalvat + '</Data></Cell>'
+ '</Row>';
xmlStr += '<Row>'
+ '<Cell><Data ss:Type="String"> </Data></Cell>'
+ '<Cell><Data ss:Type="String"> </Data></Cell>'
+ '<Cell><Data ss:Type="String"> </Data></Cell>'
+ '<Cell><Data ss:Type="String"> </Data></Cell>'
+ '<Cell ss:StyleID="plainright"><Data ss:Type="String"> </Data></Cell>'
+ '</Row>';
xmlStr += '<Row>'
+ '<Cell ss:StyleID="Black"><Data ss:Type="String"> </Data></Cell>'
+ '<Cell ss:StyleID="Black"><Data ss:Type="String">VAT on Purchases</Data></Cell>'
+ '<Cell ss:StyleID="Black"><Data ss:Type="String">Amount (SAR)</Data></Cell>'
+ '<Cell ss:StyleID="Black"><Data ss:Type="String">Adjustment (SAR)</Data></Cell>'
+ '<Cell ss:StyleID="Black1"><Data ss:Type="String">VAT (SAR)</Data></Cell>'
+ '</Row>';
xmlStr += '<Row>'
+ '<Cell ss:StyleID="scenterw"><Data ss:Type="String">5</Data></Cell>'
+ '<Cell ss:StyleID="swhite"><Data ss:Type="String">Standard rated domestic purchases</Data></Cell>'
+ '<Cell ss:StyleID="rightpink"><Data ss:Type="String">' + checkForParameter(obj.pstdRateDomesticamount, '-') + '</Data></Cell>'
+ '<Cell ss:StyleID="swhite"><Data ss:Type="String"> </Data></Cell>'
+ '<Cell ss:StyleID="rightpink1"><Data ss:Type="String">' + checkForParameter(obj.pstdRateDomesticVat, '-') + '</Data></Cell>'
+ '</Row>';
xmlStr += '<Row>'
+ '<Cell ss:StyleID="scenterw"><Data ss:Type="String">6</Data></Cell>'
+ '<Cell ss:StyleID="swhite"><Data ss:Type="String">Imports Subject to VAT Paid at Customs</Data></Cell>'
+ '<Cell ss:StyleID="rightpink"><Data ss:Type="String">' + checkForParameter(obj.pimportsubjectAmount, '-') + '</Data></Cell>'
+ '<Cell ss:StyleID="swhite"><Data ss:Type="String"> </Data></Cell>'
+ '<Cell ss:StyleID="rightpink1"><Data ss:Type="String">' + checkForParameter(obj.pimportsubjectVat, '-') + '</Data></Cell>'
+ '</Row>';
xmlStr += '<Row>'
+ '<Cell ss:StyleID="scenterw"><Data ss:Type="String">7</Data></Cell>'
+ '<Cell ss:StyleID="swhite"><Data ss:Type="String">Imports Subject to VAT Accounted for through the Reverse Charge Mechanism</Data></Cell>'
+ '<Cell ss:StyleID="rightpink"><Data ss:Type="String">' + checkForParameter(obj.pimportsubjectreverseAmount, '-') + '</Data></Cell>'
+ '<Cell ss:StyleID="swhite"><Data ss:Type="String"> </Data></Cell>'
+ '<Cell ss:StyleID="rightpink1"><Data ss:Type="String">' + checkForParameter(obj.pimportsubjectreverseVat, '-') + '</Data></Cell>'
+ '</Row>';
xmlStr += '<Row>'
+ '<Cell ss:StyleID="scenterw"><Data ss:Type="String">8</Data></Cell>'
+ '<Cell ss:StyleID="swhite"><Data ss:Type="String">Zero rated purchases</Data></Cell>'
+ '<Cell ss:StyleID="rightpink"><Data ss:Type="String">' + checkForParameter(obj.pzeroRatedPurchaseAmount, '-') + '</Data></Cell>'
+ '<Cell ss:StyleID="swhite"><Data ss:Type="String"> </Data></Cell>'
+ '<Cell ss:StyleID="rightpink1"><Data ss:Type="String">' + checkForParameter(obj.pzeroRatedPurchaseVat, '-') + '</Data></Cell>'
+ '</Row>';
xmlStr += '<Row>'
+ '<Cell ss:StyleID="scenterw"><Data ss:Type="String">9</Data></Cell>'
+ '<Cell ss:StyleID="swhite"><Data ss:Type="String">Exempt purchases</Data></Cell>'
+ '<Cell ss:StyleID="rightpink"><Data ss:Type="String">' + checkForParameter(obj.pexemptPurchasesAmount, '-') + '</Data></Cell>'
+ '<Cell ss:StyleID="swhite"><Data ss:Type="String"> </Data></Cell>'
+ '<Cell ss:StyleID="rightpink1"><Data ss:Type="String">' + checkForParameter(obj.pexemptPurchasesVat, '-') + '</Data></Cell>'
+ '</Row>';
xmlStr += '<Row>'
+ '<Cell ss:StyleID="centerpink"><Data ss:Type="String"></Data></Cell>'
+ '<Cell ss:StyleID="centerpink"><Data ss:Type="String">Total Purchases</Data></Cell>'
+ '<Cell ss:StyleID="rightpink"><Data ss:Type="String">' + obj.totalpurchaseamount + '</Data></Cell>'
+ '<Cell ss:StyleID="Pink"><Data ss:Type="String"> </Data></Cell>'
+ '<Cell ss:StyleID="rightpink1"><Data ss:Type="String">' + obj.totalpurchasevat + '</Data></Cell>'
+ '</Row>';
xmlStr += '<Row>'
+ '<Cell ss:StyleID="Gray"><Data ss:Type="String"></Data></Cell>'
+ '<Cell ss:StyleID="Gray"><Data ss:Type="String">Net VAT </Data></Cell>'
+ '<Cell ss:StyleID="Gray"><Data ss:Type="String"> </Data></Cell>'
+ '<Cell ss:StyleID="Gray"><Data ss:Type="String"> </Data></Cell>'
+ '<Cell ss:StyleID="rightgray"><Data ss:Type="String">' + obj.netamountvalue + '</Data></Cell>'
+ '</Row>';
xmlStr += '</Table></Worksheet></Workbook>';
var strXmlEncoded = encode.convert({
string: xmlStr,
inputEncoding: encode.Encoding.UTF_8,
outputEncoding: encode.Encoding.BASE_64
});
var objXlsFile = file.create({
name: 'sampleExport.xls',
fileType: file.Type.EXCEL,
contents: strXmlEncoded
});
// log.debug('objXlsFile', objXlsFile);
scriptContext.response.writeFile({
file: objXlsFile,
isInline: false
});
return;
//
} catch (e) {
log.debug({
title: e.name,
details: e
});
}
}
// function to format number based on currency
function makeItCurrency(myNumber) {
// log.debug('myNumber', myNumber);
var myFormat = formati.getCurrencyFormatter({currency: "SAR"});
var newCur = myFormat.format({
number: myNumber
});
return newCur;
}
// Function to check values and set default values
function checkForParameter(parameter, defaultparameter) {
try {
if (parameter !== null && parameter !== undefined
&& parameter !== "null" && parameter !== "NaN"
&& parameter !== "undefined"
&& parameter != ""
&& parameter != " ") {
return parameter;
} else {
return defaultparameter;
}
} catch (e) {
log.error("Err@ FN checkForParameter", e);
}
}
return {onRequest}
});