Jira Code: NHS-2, NHS-3,NHS-4
This task is to show inventory book details of transaction in a period. Period will be entered by user. Report should contain the details from following transactions Movements Receipts / Movements Issues, Purchase, Purchases for Vendors Receipts / Sales for Customer Returns, Sales. Report will consider assembly and inventory items only.
Portlet script
/**
* @NApiVersion 2.x
* @NScriptType Portlet
* @NModuleScope SameAccount
* @ScriptId
* @ScriptTiltle
* @Description It is to Show portlet form in home
* @CreatedBY AJ 10/12/2018
* @RelatedScripts:
*
*/
define(['N/record', 'N/search', 'N/ui/serverWidget','N/url'],
/**
* @param {record} record
* @param {search} search
* @param {serverWidget} serverWidget
*/
function(record, search, serverWidget,url) {
/**
* Definition of the Portlet script trigger point.
*
* @param {Object} params
* @param {Portlet} params.portlet - The portlet object used for rendering
* @param {number} params.column - Specifies whether portlet is placed in left (1), center (2) or right (3) column of the dashboard
* @param {string} params.entity - (For custom portlets only) references the customer ID for the selected customer
* @Since 2015.2
*/
function render(params) {
try{
var portlet = params.portlet;
portlet.title = 'Inventory Book Detail';
portlet.clientScriptFileId = '51469';
var dateFrom = portlet.addField({
id: 'date_datefrom',
type: 'date',
label: 'From Date'
});
dateFrom.isMandatory = true;
dateFrom.updateLayoutType({
layoutType : 'normal'
});
dateFrom.updateBreakType({
breakType : 'startcol'
});
var location = portlet.addField({
id: 'text_location',
type: serverWidget.FieldType.SELECT,
source:'location',
label: 'Location'
});
location.isMandatory = true;
location.updateBreakType({
breakType : 'startrow'
});
location.updateLayoutType({
layoutType : 'normal'
});
var test = portlet.addField({
id: 'test',
type: 'inlinehtml',
label: null
});
test.updateLayoutType({
layoutType : 'normal'
});
test.padding =2;
test.updateBreakType({
breakType : 'startrow'
});
var myvar = '<html lang="en">'+
'<head>'+
' <meta charset="utf-8">'+
' <meta name="viewport" content="width=device-width, initial-scale=1">'+
' <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">'+
' <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>'+
' <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>'+
'</head>'+
'<body>'+
''+
'<div class="after-class">'+
' <h2></h2>'+
' <div class="alert alert-success">'+
' <strong>Note:</strong> Please add Date from and Date To, Subsidiary.'+
' </div>'+
' '+
'</div>'+
''+
'</body>';
test.defaultValue = myvar;
test.updateLayoutType({
layoutType : serverWidget.FieldLayoutType.STARTROW
});
var dateTo = portlet.addField({
id: 'date_dateto',
type: 'date',
label: 'To Date'
});
dateTo.isMandatory = true;
dateTo.updateLayoutType({
layoutType : 'normal'
});
dateTo.updateBreakType({
breakType : 'startcol'
});
var customer = portlet.addField({
id: 'text_customers',
type: serverWidget.FieldType.SELECT,
source:'subsidiary',
label: 'Subsidiary'
});
customer.isMandatory = true;
customer.updateBreakType({
breakType : 'startcol'
});
customer.updateLayoutType({
layoutType : 'normal'
});
portlet.setSubmitButton({
url:'www',
label:'Submit'
});
}catch(e)
{
log.debug("Err@ FN ",e.message);
log.error("Err@ FN ",e.message);
}
}
return {
render: render
};
});
Client Script
/**
* @NApiVersion 2.x
* @NScriptType ClientScript
* @NModuleScope SameAccount
*/
/*******************************************************************************
* CLIENTNAME:NetU
* NHS 2 Create a report
*************************************************************************
* Date : 06/10/2018
*
* Author: Jobin & Jismi IT Services LLP
* Script Description :This is client script to open a suitelet
* Date created : 06/10/2018
*
* REVISION HISTORY
*
* Revision 1.0 ${06/10/2018} aj : created
*
*
******************************************************************************/
define(['N/record', 'N/search', 'N/url','N/currentRecord','N/format'],
/**
* @param {record} record
* @param {search} search
* @param {url} url
*/
function(record, search, url,currentRecord,format) {
/**
* Validation function to be executed when record is saved.
*
* @param {Object} scriptContext
* @param {Record} scriptContext.currentRecord - Current form record
* @returns {boolean} Return true if record is valid
*
* @since 2015.2
*/
function saveRecord(scriptContext) {
try{
var currentRec = scriptContext.currentRecord;
var dateFrom = currentRec.getValue({
fieldId : 'date_datefrom'
});
var dateTo = currentRec.getValue({
fieldId : 'date_dateto'
});
var customer = currentRec.getValue({
fieldId : 'text_customers'
});
var location = currentRec.getValue({
fieldId : 'text_location'
});
dateFrom = format.format({value:dateFrom, type: format.Type.DATE});
dateTo = format.format({value:dateTo, type: format.Type.DATE});
if (dateFrom == ""||dateTo == ""||customer == ""||location == "") {
alert("Please Fill the Fields");
}
else{
console.log('dateFrom='+dateFrom+'*dateTo='+dateTo+'**customer='+customer,'*****location=',location);
var TOSL = url.resolveScript({
scriptId: 'customscript_nhs2_sl_show_inv_book',
deploymentId: 'customdeploy_nhs2_sl_show_inv_book',
returnExternalUrl: false,
params:{
'dateFrom':dateFrom,
'dateTo':dateTo,
'customer':customer,
'location':location
}
});
window.open(TOSL);
}
}catch(e)
{
log.error("Err@ FN ",e.message);
console.log("Err@ FN =",e.message);
}
}
return {
saveRecord: saveRecord
};
});
Suitelet script
/**
* @NApiVersion 2.x
* @NScriptType Suitelet
* @NModuleScope SameAccount
*/
/*******************************************************************************
* CLIENTNAME:NETU
* NHS-2
*************************************************************************
* Date : 08/10/2018
*
* Author: Jobin & Jismi IT Services LLP
* Date created : 08/10/2018
*
* REVISION HISTORY
*
* Revision 1.0 ${08/10/2018} aj : created
*
*
******************************************************************************/
define(['N/record', 'N/search', 'N/ui/serverWidget','N/url','N/format','N/file', 'N/encode'],
/**
* @param {record} record
* @param {search} search
* @param {serverWidget} serverWidget
*/
function(record, search, serverWidget,url,format,file,encode) {
var pageIndex=100;
/**
* 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{
var dateFrom = context.request.parameters.dateFrom;
var dateTo = context.request.parameters.dateTo;
var subsidiary = context.request.parameters.customer;
var location = context.request.parameters.location;
var mode=context.request.parameters._method;
var input = transactionData(context);
var inputArray =input["maxObject"];
var arrayOfItems =input["arrayOfItems"];
var outObject = inputArray.reduce(function (a, e) {
var estKey = e["location"];
(a[estKey] ? a[estKey] : a[estKey] = null || []).push(e);
return a;
}, {});
var historicalData = findHistoricalData(outObject,arrayOfItems,context);
var Forms = generateForm(context,historicalData,dateFrom,dateTo,mode,subsidiary);
if (mode != 'download')
context.response.write(Forms);
/****======>
* Search
*/
}catch(e)
{
log.debug("Err@ FN onRequest",e.message);
log.error("Err@ FN onRequest",e.message);
}
}
/**
*
* Function to get the Items & Values
*
*/
function transactionData(context)
{
try{
var data = {};
var dateFrom = context.request.parameters.dateFrom;
var dateTo = context.request.parameters.dateTo;
var subsidiary = context.request.parameters.customer;
var location = context.request.parameters.location;
// NEW SERACH BY NETU TEAM ID 3377
var transactionSearchObj = search.create({
type: "transaction",
filters:
[
["posting","is","T"],
"AND",
["trandate","within",dateFrom,dateTo],
"AND",
["taxline","is","F"],
"AND",
["shipping","is","F"],
"AND",
["item.type","anyof","Assembly","InvtPart"],
"AND",
["subsidiary","anyof",subsidiary],
"AND",
["location","anyof",location]
],
columns:
[
search.createColumn({
name: "item",
summary: "GROUP",
sort: search.Sort.ASC,
label: "Item"
}),
search.createColumn({
name: "salesdescription",
join: "item",
summary: "GROUP",
label: "Description"
}),
search.createColumn({
name: "location",
summary: "GROUP",
label: "Location"
}),
search.createColumn({
name: "custitem_netu_item_group",
join: "item",
summary: "GROUP",
label: "Item Group"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "case when {type} in ('Bill','Bill Credit','Credit Card') and {createdfrom} is NULL then {quantity} when {type}= 'Bill Credit' and {createdfrom} like 'Bill%' then {quantity} when {type} like 'Item Fulfillment%' and {createdfrom} like 'Vendor%'and {quantity}< 0 then {quantity} when {type} like 'Item Receipt%' and {createdfrom} like 'Purchase%' and {quantity}> 0 then {quantity} else 0 end",
label: "Purchases quantity"
}),
search.createColumn({
name: "formulacurrency",
summary: "SUM",
formula: "case when {type} in ('Bill','Credit Card') and {createdfrom} is NULL then {amount} when {type}= 'Bill' and {createdfrom} like 'Purchase%' then ({amount}*-1) when {type}= 'Bill Credit' and {createdfrom} like 'Vendor%' then {amount} when {type}= 'Bill Credit' and {createdfrom} like 'Bill%' then {amount} when {type}= 'Bill Credit' and {createdfrom} is NULL and {quantity} is not NULL then {amount} else 0 end",
label: "Purchases Value"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "case when {type} in ('Assembly Build','Assembly Unbuild','Inventory Distribution', 'Inventory Worksheet') and {quantity}>0 then {quantity} when {type} in ('Transfer Order','Inventory Transfer','Inventory Adjustment') and {quantity} >0 and {accounttype} like 'Other Current%' then {quantity} when {type}= 'Item Receipt' and {createdfrom} like 'Transfer%' and {quantity} >0 and {account} not like 'Intercompany Clearing Account%' then {quantity} when {type}= 'Item Receipt' and {createdfrom} is NULL and {quantity} >0 then {quantity} else 0 end",
label: "Movement Receipts Quantity"
}),
search.createColumn({
name: "formulacurrency",
summary: "SUM",
formula: "case when {type} in ('Assembly Build','Assembly Unbuild','Inventory Distribution', 'Inventory Worksheet', 'Transfer', 'Work Order Completion') and {quantity} >0 then {amount} when {type} in ('Transfer Order','Inventory Transfer','Inventory Adjustment') and {quantity} >0 and {accounttype} like 'Other Current%' then {amount} when {type}= 'Item Receipt' and {createdfrom} like 'Transfer%' and {quantity} >0 and {account} not like 'Intercompany Clearing Account%' then {amount} when {type}= 'Item Receipt' and {createdfrom} is NULL and {quantity} >0 then {amount} else 0 end",
label: "Movement Receipts Value"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "case when {type} in ('Assembly Build','Assembly Unbuild','Inventory Distribution', 'Inventory Worksheet') and {quantity} <0 then {quantity} when {type} in ('Transfer Order','Inventory Transfer','Inventory Adjustment') and {quantity} <0 and {accounttype} like 'Other Current%' then {quantity} when {type}= 'Item Fulfillment' and {createdfrom} like 'Transfer%' and {quantity} <0 then {quantity} when {type}= 'Item Fulfillment' and {createdfrom} is NULL and {quantity} <0 then {quantity} else 0 end",
label: "Movement Issues Quantity"
}),
search.createColumn({
name: "formulacurrency",
summary: "SUM",
formula: "case when {type} in ('Assembly Build','Assembly Unbuild','Inventory Distribution', 'Inventory Worksheet', 'Transfer') and {quantity} <0 then {amount} when {type} in ('Transfer Order','Inventory Transfer','Inventory Adjustment') and {quantity} <0 and {accounttype} like 'Other Current%' then {amount} when {type}= 'Item Fulfillment' and {createdfrom} like 'Transfer%' and {quantity} <0 then {amount} when {type}= 'Item Fulfillment' and {createdfrom} is NULL and {quantity} <0 then {amount} else 0 end",
label: "Movement Issues Value"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "case when {type} in ('Invoice','Cash Sale','Credit Memo') and {createdfrom} is NULL then {quantity} when {type} in ('Invoice','Cash Sale') and {createdfrom} like 'Estimate%' then {quantity} when {type} like 'Item Fulfillment%' and {createdfrom} like 'Sales%'and {quantity}< 0 and {status} like 'Shipped%' then ({quantity}*-1) when {type} like 'Item Receipt%' and {createdfrom} like 'Return%' and {quantity}< 0 then {quantity} else 0 end",
label: "Sales Quantity"
}),
search.createColumn({
name: "formulacurrency",
summary: "SUM",
formula: "case when {type}= 'Invoice' and {createdfrom} is NULL and {accounttype} like 'Income%' then {amount} when {type} in ('Invoice','Cash Sale') and {createdfrom} like 'Estimate%' and {accounttype} like 'Income%%' then {amount} when {type}= 'Cash Sale' and {createdfrom} is NULL and {accounttype} not like 'Cost of Goods Sold%' then {amount} when {type}= 'Credit Memo' and {createdfrom} like 'Return%' then {amount} when {type}= 'Credit Memo' and {createdfrom} is NULL and {amount}<0 and {accounttype} not like 'Cost of Goods Sold%' then {amount} else 0 end",
label: "Sales Value"
}),
search.createColumn({
name: "formulacurrency",
summary: "SUM",
formula: "case when {type}= 'Invoice' and {createdfrom} is NULL and {amount}<0 and {accounttype} not like 'Accounts Rec%' then ({amount}*-1) when {type}= 'Cash Sale' and {createdfrom} is NULL and {accounttype} like 'Cost of Goods%' then {amount} when {type} in ('Invoice','Cash Sale') and {createdfrom} like 'Estimate%' and {accounttype} like 'Cost of Goods Sold%' then {amount} when {type}= 'Credit Memo' and {createdfrom} is NULL and {amount}<0 and {accounttype} like 'Cost of Goods Sold%' then {amount} when {type} like 'Item Fulfillment%' and {createdfrom} like 'Sales%'and {quantity}< 0 and {status} like 'Shipped%' then ({amount}*-1) when {type} like 'Item Receipt%' and {createdfrom} like 'Return%' and {quantity}< 0 then {amount} else 0 end",
label: "Cost of Sales"
}),
search.createColumn({
name: "formulacurrency",
summary: "SUM",
formula: "(case when {type}= 'Invoice' and {createdfrom} is NULL and {accounttype} like 'Income%' then {amount} when {type} in ('Invoice','Cash Sale') and {createdfrom} like 'Estimate%' and {accounttype} like 'Incom%' then {amount} when {type}= 'Cash Sale' and {createdfrom} is NULL and {accounttype} not like 'Cost of Goods Sold%' then {amount} when {type}= 'Credit Memo' and {createdfrom} like 'Return%' then {amount} when {type}= 'Credit Memo' and {createdfrom} is NULL and {amount}<0 and {accounttype} not like 'Cost of Goods Sold%' then {amount} else 0 end)-(case when {type}= 'Invoice' and {createdfrom} is NULL and {amount}<0 and {accounttype} not like 'Accounts Rec%' then ({amount}*-1) when {type}= 'Cash Sale' and {createdfrom} is NULL and {accounttype} like 'Cost of Goods%' then {amount} when {type} in ('Invoice','Cash Sale') and {createdfrom} like 'Estimate%' and {accounttype} like 'Cost of Goods Sold%' then {amount} when {type}= 'Credit Memo' and {createdfrom} is NULL and {amount}<0 and {accounttype} like 'Cost of Goods Sold%' then {amount} when {type} like 'Item Fulfillment%' and {createdfrom} like 'Sales%'and {quantity}< 0 and {status} like 'Shipped%' then ({amount}*-1) when {type} like 'Item Receipt%' and {createdfrom} like 'Return%' and {quantity}< 0 then {amount} else 0 end)",
label: "Gross Margin"
})
]
});
var searchResultCount = transactionSearchObj.runPaged().count;
var data ={},maxObject=[],arrayOfItems=[];
var searchResults = [];
var result;
var singleresult;
var start = 0, end=1000 ;
var group={};
if(searchResultCount<1000)
{
end=searchResultCount;
}
for (var i = 0; i < Math.ceil(searchResultCount / 1000); i++)
{
result = transactionSearchObj.run().getRange({
start: start,
end: end
});
for (var j = 0; j < result.length; j++) {
singleresult = result[j];
searchResults.push(singleresult);
}
start = end;
end = end + 1000;
}
if (searchResultCount > 0) {
for (var i = 0; i < searchResults.length; i++) {
var columns = transactionSearchObj.columns;
var singleItem ={};
var item =searchResults[i].getValue({
name: "item",
summary: "GROUP",
sort: search.Sort.ASC,
label: "Item"
});
arrayOfItems.push(item);
var itemName =searchResults[i].getText({
name: "item",
summary: "GROUP",
sort: search.Sort.ASC,
label: "Item"
});
var itemDes =searchResults[i].getValue({
name: "salesdescription",
join: "item",
summary: "GROUP",
label: "Description"
});
var location = searchResults[i].getText({
name: "location",
summary: "GROUP",
label: "Location"
});
var grp=searchResults[i].getText({
name: "custitem_netu_item_group",
join: "item",
summary: "GROUP",
label: "Item Group"
});
var pQuantity = searchResults[i].getValue(
columns[4]
);
var pValue = searchResults[i].getValue(
columns[5]
);
var sQty = searchResults[i].getValue(
columns[10]
);
var sValue = searchResults[i].getValue(
columns[11]
);
var MIQ = searchResults[i].getValue(
columns[8]
);
var MIV = searchResults[i].getValue(
columns[9]
);
var MRQ = searchResults[i].getValue(
columns[6]
);
var MRV = searchResults[i].getValue(
columns[7]
);
var COS = searchResults[i].getValue(
columns[12]
);
var GrossMrgin = searchResults[i].getValue(
columns[13]
);
singleItem={"itemName":itemName,"group":grp,"location":location,"itemDes":itemDes,"pQuantity":pQuantity,"COS":COS,"GrossMrgin":GrossMrgin,
"pValue":pValue,"sQty":sQty,"sValue":sValue,"MIQ":MIQ,"MIV":MIV,"MRQ":MRQ,"MRV":MRV,"balanceForwardQty":0,"balanceForwardAmt":0,"unitAvgCost":0};
maxObject.push(singleItem);
}
}
}catch(e)
{
log.debug("Err@trans Data Search",e);
log.error("Err@trans Data Search",e);
}
return {maxObject:maxObject,arrayOfItems:arrayOfItems};
}
/**
*
* Function to get the Onhand & Value
*
*/
/**
*
* Function to get the Onhand & Value
*
*/
function findHistoricalData(outObject,arrayOfItems,context)
{
var dateFrom = context.request.parameters.dateFrom;
var dateTo = context.request.parameters.dateTo;
var subsidiary = context.request.parameters.customer;
var location = context.request.parameters.location;
try{
// NEW SEARCH BY NETU TEAM ==>UPDATED O7/01/2019 SAERCH ID 3375
var historicalSearchObj = search.create({
type: "transaction",
filters:
[
["posting","is","T"],
"AND",
["trandate","onorbefore",dateFrom],
"AND",
["taxline","is","F"],
"AND",
["shipping","is","F"],
"AND",
["item.type","anyof","Assembly","InvtPart"],
"AND",
["subsidiary","anyof",subsidiary],
"AND",
["location","anyof",location]
],
columns:
[
search.createColumn({
name: "item",
summary: "GROUP",
sort: search.Sort.ASC,
label: "Item"
}),
search.createColumn({
name: "location",
summary: "GROUP",
label: "Location"
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "(case when {type} in ('Bill','Bill Credit','Credit Card') and {createdfrom} is NULL then {quantity} when {type}= 'Bill Credit' and {createdfrom} like 'Bill%' then {quantity} when {type} like 'Item Fulfillment%' and {createdfrom} like 'Vendor%'and {quantity}< 0 then {quantity} when {type} like 'Item Receipt%' and {createdfrom} like 'Purchase%' and {quantity}> 0 then {quantity} else 0 end )+(case when {type} in ('Assembly Build','Assembly Unbuild','Inventory Distribution', 'Inventory Worksheet') and {quantity}>0 then {quantity} when {type} in ('Transfer Order','Inventory Transfer','Inventory Adjustment') and {quantity} >0 and {accounttype} like 'Other Current%' then {quantity} when {type}= 'Item Receipt' and {createdfrom} like 'Transfer%' and {quantity} >0 and {account} not like 'Intercompany Clearing Account%' then {quantity} when {type}= 'Item Receipt' and {createdfrom} is NULL and {quantity} >0 then {quantity} else 0 end )-ABS(case when {type} in ('Assembly Build','Assembly Unbuild','Inventory Distribution', 'Inventory Worksheet') and {quantity} <0 then {quantity} when {type} in ('Transfer Order','Inventory Transfer','Inventory Adjustment') and {quantity} <0 and {accounttype} like 'Other Current%' then {quantity} when {type}= 'Item Fulfillment' and {createdfrom} like 'Transfer%' and {quantity} <0 and {accounttype} like 'Other Current%'then {quantity} when {type}= 'Item Fulfillment' and {createdfrom} is NULL and {quantity} <0 then {quantity} else 0 end)-(case when {type} in ('Invoice','Cash Sale','Credit Memo') and {createdfrom} is NULL then {quantity} when {type} in ('Invoice','Cash Sale') and {createdfrom} like 'Estimate%' then {quantity} when {type} like 'Item Fulfillment%' and {createdfrom} like 'Sales%'and {quantity}< 0 and {status} like 'Shipped%' then ({quantity}*-1) when {type} like 'Item Receipt%' and {createdfrom} like 'Return%' and {quantity}< 0 then {quantity} else 0 end)",
label: "Quantity "
}),
search.createColumn({
name: "formulanumeric",
summary: "SUM",
formula: "(case when {type} in ('Bill','Credit Card') and {createdfrom} is NULL then {amount} when {type}= 'Bill' and {createdfrom} like 'Purchase%' then ({amount}*-1) when {type}= 'Bill Credit' and {createdfrom} like 'Vendor%' then {amount} when {type}= 'Bill Credit' and {createdfrom} like 'Bill%' then {amount} when {type}= 'Bill Credit' and {createdfrom} is NULL and {quantity} is not NULL then {amount} else 0 end )+(case when {type} in ('Assembly Build','Assembly Unbuild','Inventory Distribution', 'Inventory Worksheet', 'Transfer') and {quantity} >0 then {amount} when {type} in ('Transfer Order','Inventory Transfer','Inventory Adjustment') and {quantity} >0 and {accounttype} like 'Other Current%' then {amount} when {type}= 'Item Receipt' and {createdfrom} like 'Transfer%' and {quantity} >0 and {account} not like 'Intercompany Clearing Account%' then {amount} when {type}= 'Item Receipt' and {createdfrom} is NULL and {quantity} >0 then {amount} else 0 end )-ABS(case when {type} in ('Assembly Build','Assembly Unbuild','Inventory Distribution', 'Inventory Worksheet', 'Transfer') and {quantity} <0 then {amount} when {type} in ('Transfer Order','Inventory Transfer','Inventory Adjustment') and {quantity} <0 and {accounttype} like 'Other Current%' then {amount} when {type}= 'Item Fulfillment' and {createdfrom} like 'Transfer%' and {quantity} <0 and {accounttype} like 'Other Current%'then {amount} when {type}= 'Item Fulfillment' and {createdfrom} is NULL and {quantity} <0 then {amount} else 0 end )-(case when {type}= 'Invoice' and {createdfrom} is NULL and {amount}<0 and {accounttype} not like 'Accounts Rec%' then ({amount}*-1) when {type}= 'Cash Sale' and {createdfrom} is NULL and {accounttype} like 'Cost of Goods%' then {amount} when {type} in ('Invoice','Cash Sale') and {createdfrom} like 'Estimate%' and {accounttype} like 'Cost of Goods Sold%' then {amount} when {type}= 'Credit Memo' and {createdfrom} is NULL and {amount}<0 and {accounttype} like 'Cost of Goods Sold%' then {amount} when {type} like 'Item Fulfillment%' and {createdfrom} like 'Sales%'and {quantity}< 0 then ({amount}*-1) when {type} like 'Item Receipt%' and {createdfrom} like 'Return%' and {quantity}< 0 then {amount} else 0 end)",
label: "Value"
}),
search.createColumn({
name: "formulacurrency",
summary: "SUM",
formula: "sum((case when{type} in ('Bill','Credit Card')and{createdfrom} is NULL then{amount} when{type}='Bill' and{createdfrom} like 'Purch%' then({amount}*-1) when{type}= 'Bill Credit' and{createdfrom} like 'Vend%' then{amount} when{type}= 'Bill Credit' and{createdfrom} like 'Bill%' then{amount} when{type}='Bill Credit' and{createdfrom} is NULL and{quantity} is not NULL then{amount} else 0 end)+(case when{type} in ('Assembly Build','Assembly Unbuild','Inventory Distribution', 'Inventory Worksheet') and{quantity} >0 then{amount} when{type} in ('Transfer Order','Inventory Transfer','Inventory Adjustment') and{quantity} >0 and{accounttype} like 'Other Current%' then{amount} when{type}='Item Receipt' and{createdfrom} like 'Tr%' and{quantity}>0 and{account} not like 'Intercompany Clearing Account%' then{amount} when{type}= 'Item Receipt' and{createdfrom} is NULL and{quantity} >0 then{amount} else 0 end)-ABS(case when{type} in ('Assembly Build','Assembly Unbuild','Inventory Distribution', 'Inventory Worksheet', 'Transfer') and{quantity} <0 then{amount} when{type} in ('Transfer Order','Inventory Transfer','Inventory Adjustment') and{quantity} <0 and{accounttype} like 'Other Current%' then{amount} when{type}= 'Item Fulfillment' and{createdfrom} like 'Transfer%' and{quantity} <0 and{accounttype} like 'Other Current%'then{amount} when{type}= 'Item Fulfillment' and{createdfrom} is NULL and{quantity} <0 then{amount} else 0 end)-(case when{type}= 'Invoice' and{createdfrom} is NULL and{amount}<0 and{accounttype} not like 'Accounts Rec%' then({amount}*-1) when{type}= 'Cash Sale' and{createdfrom} is NULL and{accounttype} like 'Cost of Goods%' then{amount} when{type} in ('Invoice','Cash Sale') and{createdfrom} like 'Estimate%' and{accounttype} like 'Cost of Goods Sold%' then{amount} when{type}= 'Credit Memo' and{createdfrom} is NULL and{amount}<0 and{accounttype} like '%Goods Sold%' then{amount} when{type}='Item Fulfillment' and{createdfrom} like 'Sal%'and{quantity}< 0 then({amount}*-1) when{type}='Item Receipt' and{createdfrom} like 'Retu%' and{quantity}< 0 then{amount} else 0 end))/NULLIF(sum((case when{type} in ('Bill','Bill Credit','Credit Card') and{createdfrom} is NULL then{quantity} when{type}='Bill Credit' and{createdfrom} like 'Bill%' then{quantity} when{type}='Item Fulfillment' and{createdfrom} like 'Vend%'and{quantity}< 0 then{quantity} when{type} like 'Item R%' and{createdfrom} like 'Purc%' and{quantity}> 0 then{quantity} else 0 end)+(case when{type} in ('Assembly Build','Assembly Unbuild','Inventory Distribution', 'Inventory Worksheet') and{quantity}>0 then{quantity} when{type} in ('Transfer Order','Inventory Transfer','Inventory Adjustment') and{quantity} >0 and{accounttype} like 'Other Current%' then{quantity} when{type}= 'Item Receipt' and{createdfrom} like 'Tr%' and{quantity} >0 and{account} not like 'Intercompany Clearing Account%' then{quantity} when{type}= 'Item Receipt' and{createdfrom} is NULL and{quantity} >0 then{quantity} else 0 end)-ABS(case when{type} in ('Assembly Build','Assembly Unbuild','Inventory Distribution', 'Inventory Worksheet') and{quantity} <0 then{quantity} when{type} in ('Transfer Order','Inventory Transfer','Inventory Adjustment') and{quantity} <0 and{accounttype} like 'Other Current%' then{quantity} when{type}= 'Item Fulfillment' and{createdfrom} like 'Transfer%' and{quantity} <0 and{accounttype} like 'Other Current%'then{quantity} when{type}= 'Item Fulfillment' and{createdfrom} is NULL and{quantity} <0 then{quantity} else 0 end)-(case when{type} in ('Invoice','Cash Sale','Credit Memo') and{createdfrom} is NULL then{quantity} when{type} in ('Invoice','Cash Sale') and{createdfrom} like 'Est%' then{quantity} when{type} like 'Item F%' and{createdfrom} like 'Sal%'and{quantity}<0 then({quantity}*-1) when{type} like 'Item R%' and{createdfrom} like 'Ret%' and{quantity}<0 then{quantity} else 0 end)),0)",
label: "Unit Average Cost"
})
]
});
var searchResultCount = historicalSearchObj.runPaged().count;
// to get the result into an object
var data ={},maxObject=[];
var searchResults = [];
var result;
var singleresult;
var start = 0, end=1000 ;
if(searchResultCount<1000)
{
end=searchResultCount;
}
var searchResults = [];
var result;
var singleresult;
var start = 0, end=1000 ;
var group={};
if(searchResultCount<1000)
{
end=searchResultCount;
}
for (var i = 0; i < Math.ceil(searchResultCount / 1000); i++)
{
result = historicalSearchObj.run().getRange({
start: start,
end: end
});
for (var j = 0; j < result.length; j++) {
singleresult = result[j];
var columns = historicalSearchObj.columns;
searchResults.push(singleresult);
}
start = end;
end = end + 1000;
}
if (searchResultCount > 0) {
for (var i = 0; i < searchResults.length; i++) {
var singleItem ={};
var item =searchResults[i].getText({
name: "item",
summary: "GROUP",
label: "Item"
});
var location = searchResults[i].getText({
name: "location",
summary: "GROUP",
label: "Location"
});
var balanceForwardQty = searchResults[i].getValue(
columns[2]
);
var balanceForwardAmt = searchResults[i].getValue(
columns[3]
);
var unitAvgCost = searchResults[i].getValue(
columns[4]
);
// to get the data match
if(outObject[location])
{
for(var k=0;k<outObject[location].length;k++)
{
if(outObject[location][k].itemName==item &&(outObject[location][k].itemName!=null && outObject[location][k].itemName!=" "&& outObject[location][k].itemName!="" || outObject[location][k].itemName!=undefined))
{
if(balanceForwardQty)
outObject[location][k].balanceForwardQty = balanceForwardQty;
else
outObject[location][k].balanceForwardQty = 0;
if(balanceForwardAmt)
outObject[location][k].balanceForwardAmt=balanceForwardAmt;
else
outObject[location][k].balanceForwardAmt=0;
if(unitAvgCost)
outObject[location][k].unitAvgCost=unitAvgCost;
else
outObject[location][k].unitAvgCost=0;
}
}
}
}
}
}catch(e)
{
log.debug("Err@ FN findHistoricalData",e.message);
log.error("Err@ FN findHistoricalData",e.message);
}
return outObject;
}
/****
*
* Function Generate form
*/
function generateForm(context,searchR,dateFrom,dateTo,mode,subId)
{
try
{
var location = context.request.parameters.location;
var subsidiarySearchObj = search.create({
type: "subsidiary",
filters:
[
["internalidnumber","equalto",subId]
],
columns:
[
search.createColumn({
name: "name",
sort: search.Sort.ASC,
label: "Name"
}),
search.createColumn({name: "custrecord_netu_sub_prof", label: "Profession"}),
search.createColumn({
name: "address",
join: "address",
label: " Address"
}),
search.createColumn({name: "taxidnum", label: "Tax ID"}),
search.createColumn({
name: "formuladate",
formula: "{today}",
label: "Formula (Date)"
})
]
});
var searchResultCount = subsidiarySearchObj.runPaged().count;
subsidiarySearchObj.run().each(function(result){
return true;
});
if (searchResultCount > 0) {
var resultSet = subsidiarySearchObj.run();
var searchResults = resultSet.getRange({
start : 0,
end : 1
});
var companyName =searchResults[0].getValue({
name : 'name'
});
var companyProff = searchResults[0].getValue({
name : 'custrecord_netu_sub_prof'
});
var companyAddress = searchResults[0].getValue({
name: "address",
join: "address"
});
var VATReg = searchResults[0].getValue({
name : 'taxidnum'
});
var dateInRegion=searchResults[0].getValue({
name: "formuladate",
formula: "{today}",
label: "Formula (Date)"
});
}
var totalBFQty=0,totalBFValue=0,totalPQuantity=0,totalPvalue=0,totalSQuantity=0,totalSvalue=0,totalunitAvgCost=0,totalBalanceValue=0;
var totalIssueQty=0,totalIssueValue=0,totalReptQty=0,totalReptValue=0,totalCOS=0,totalGross=0,totalBalanceQty=0;
if(mode=='download')
{
var TABLE = "";
var len=Object.keys(searchR).length;
if(len>0)
{
var XML ='';
var 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">'+
'<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">'+
'<Author>Windows User</Author>'+
'<LastAuthor>Windows User</LastAuthor>'+
'<Created>2018-11-12T05:41:57Z</Created>'+
'<LastSaved>2018-11-12T06:33:00Z</LastSaved>'+
'<Version>14.00</Version>'+
'</DocumentProperties>'+
'<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">'+
'<AllowPNG/>'+
'</OfficeDocumentSettings>'+
'<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">'+
'<WindowHeight>5970</WindowHeight>'+
'<WindowWidth>20115</WindowWidth>'+
'<WindowTopX>360</WindowTopX>'+
'<WindowTopY>150</WindowTopY>'+
'<ProtectStructure>False</ProtectStructure>'+
'<ProtectWindows>False</ProtectWindows>'+
'</ExcelWorkbook>'+
'<Styles>'+
'<Style ss:ID="Default" ss:Name="Normal">'+
'<Alignment ss:Vertical="Bottom"/>'+
'<Borders/>'+
'<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>'+
'<Interior/>'+
'<NumberFormat/>'+
'<Protection/>'+
'</Style>'+
'<Style ss:ID="m87230976">'+
'<Alignment ss:Horizontal="Left" ss:Vertical="Bottom" ss:WrapText="1"/>'+
'<Borders>'+
'<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'</Borders>'+
'<Font ss:FontName="Arial" x:Family="Swiss" ss:Size="11" ss:Color="#333333" ss:Bold="1"/>'+
'<Interior ss:Color="#E6B8B7" ss:Pattern="Solid"/>'+
'</Style>'+
'<Style ss:ID="m87230996">'+
'<Alignment ss:Horizontal="Left" ss:Vertical="Bottom" ss:WrapText="1"/>'+
'<Borders>'+
'<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'</Borders>'+
'<Font ss:FontName="Arial" x:Family="Swiss" ss:Size="11" ss:Color="#333333" ss:Bold="1"/>'+
'<Interior ss:Color="#E6B8B7" ss:Pattern="Solid"/>'+
'</Style>'+
'<Style ss:ID="m87231016">'+
'<Alignment ss:Horizontal="Left" ss:Vertical="Bottom" ss:WrapText="1"/>'+
'<Borders>'+
'<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'</Borders>'+
'<Font ss:FontName="Arial" x:Family="Swiss" ss:Size="11" ss:Color="#333333" ss:Bold="1"/>'+
'<Interior ss:Color="#D8E4BC" ss:Pattern="Solid"/>'+
'</Style>'+
'<Style ss:ID="m71097472">'+
'<Alignment ss:Horizontal="Left" ss:Vertical="Bottom" ss:WrapText="1"/>'+
'<Borders>'+
'<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'</Borders>'+
'<Font ss:FontName="Arial" x:Family="Swiss" ss:Size="11" ss:Color="#333333" ss:Bold="1"/>'+
'<Interior ss:Color="#E6B8B7" ss:Pattern="Solid"/>'+
'</Style>'+
'<Style ss:ID="m71097492">'+
'<Alignment ss:Horizontal="Left" ss:Vertical="Bottom" ss:WrapText="1"/>'+
'<Borders>'+
'<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'</Borders>'+
'<Font ss:FontName="Arial" x:Family="Swiss" ss:Size="11" ss:Color="#333333" ss:Bold="1"/>'+
'<Interior ss:Color="#E6B8B7" ss:Pattern="Solid"/>'+
'</Style>'+
'<Style ss:ID="m71097512">'+
'<Alignment ss:Horizontal="Left" ss:Vertical="Bottom" ss:WrapText="1"/>'+
'<Borders>'+
'<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'</Borders>'+
'<Font ss:FontName="Arial" x:Family="Swiss" ss:Size="11" ss:Color="#333333" ss:Bold="1"/>'+
'<Interior ss:Color="#E6B8B7" ss:Pattern="Solid"/>'+
'</Style>'+
'<Style ss:ID="m71097532">'+
'<Alignment ss:Horizontal="Left" ss:Vertical="Bottom" ss:WrapText="1"/>'+
'<Borders>'+
'<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'</Borders>'+
'<Font ss:FontName="Arial" x:Family="Swiss" ss:Size="11" ss:Color="#333333" ss:Bold="1"/>'+
'<Interior ss:Color="#E6B8B7" ss:Pattern="Solid"/>'+
'</Style>'+
'<Style ss:ID="m71097552">'+
'<Alignment ss:Horizontal="Left" ss:Vertical="Bottom" ss:WrapText="1"/>'+
'<Borders>'+
'<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'</Borders>'+
'<Font ss:FontName="Arial" x:Family="Swiss" ss:Size="11" ss:Color="#333333" ss:Bold="1"/>'+
'<Interior ss:Color="#E6B8B7" ss:Pattern="Solid"/>'+
'</Style>'+
'<Style ss:ID="s17">'+
'<Alignment ss:Horizontal="Center" ss:Vertical="Bottom" ss:WrapText="1"/>'+
'<Borders>'+
'<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'</Borders>'+
'<Font ss:FontName="Arial" x:Family="Swiss" ss:Size="11" ss:Color="#333333" ss:Bold="1"/>'+
'<Interior ss:Color="#ADD8E6" ss:Pattern="Solid"/>'+
'</Style>'+
'<Style ss:ID="s18">'+
'<Alignment ss:Horizontal="Center" ss:Vertical="Bottom" ss:WrapText="1"/>'+
'<Borders>'+
'<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'</Borders>'+
'<Font ss:FontName="Arial" x:Family="Swiss" ss:Size="11" ss:Color="#333333" ss:Bold="1"/>'+
'<Interior ss:Color="#D8E4BC" ss:Pattern="Solid"/>'+
'</Style>'+
'<Style ss:ID="s26">'+
'<Interior ss:Color="#E6B8B7" ss:Pattern="Solid"/>'+
'</Style>'+
'<Style ss:ID="s27">'+
'<Alignment ss:Horizontal="Center" ss:Vertical="Bottom" ss:WrapText="1"/>'+
'<Borders>'+
'<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'</Borders>'+
'<Font ss:FontName="Arial" x:Family="Swiss" ss:Size="11" ss:Color="#333333" ss:Bold="1"/>'+
'<Interior ss:Color="#E6B8B7" ss:Pattern="Solid"/>'+
'</Style>'+
'<Style ss:ID="s32">'+
'<Alignment ss:Vertical="Bottom"/>'+
'<Interior/>'+
'</Style>'+
'<Style ss:ID="s33">'+
'<Interior/>'+
'</Style>'+
'<Style ss:ID="s34">'+
'<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>'+
'<Interior/>'+
'</Style>'+
'<Style ss:ID="s36">'+
'<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>'+
'<Borders>'+
'<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'</Borders>'+
'<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000" ss:Bold="1"/>'+
'<Interior ss:Color="#D8E4BC" ss:Pattern="Solid"/>'+
'</Style>'+
'<Style ss:ID="s37">'+
'<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>'+
'<Interior ss:Color="#D8E4BC" ss:Pattern="Solid"/>'+
'</Style>'+
'<Style ss:ID="s38">'+
'<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>'+
'<Interior ss:Color="#E6B8B7" ss:Pattern="Solid"/>'+
'</Style>'+
'<Style ss:ID="s44">'+
'<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>'+
'<Font ss:FontName="Arial" x:Family="Swiss" ss:Size="11" ss:Color="#333333" ss:Bold="1"/>'+
'<Interior ss:Color="#D8E4BC" ss:Pattern="Solid"/>'+
'</Style>'+
'<Style ss:ID="s45">'+
'<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>'+
'<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000" ss:Bold="1"/>'+
'<Interior ss:Color="#D8E4BC" ss:Pattern="Solid"/>'+
'</Style>'+
'<Style ss:ID="s46">'+
'<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>'+
'<Borders>'+
'<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="2" ss:Color="#DDDDDD"/>'+
'</Borders>'+
'<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000" ss:Bold="1"/>'+
'<Interior ss:Color="#D8E4BC" ss:Pattern="Solid"/>'+
'</Style>'+
'<Style ss:ID="s107">'+
' <Interior ss:Color="#FCD5B4" ss:Pattern="Solid"/>'+
' </Style>'+
' <Style ss:ID="s108">'+
' <Interior ss:Color="#FDE9D9" ss:Pattern="Solid"/>'+
' </Style>'+
'<Style ss:ID="s47">'+
'<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>'+
'<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="18" ss:Color="#000000" ss:Bold="1"/>'+
'</Style>'+
'</Styles>'+
'<Worksheet ss:Name="Sheet1">'+
'<Table ss:ExpandedColumnCount="20" ss:ExpandedRowCount="10000000" x:FullColumns="1" x:FullRows="1" ss:DefaultRowHeight="15">'+
'<Column ss:AutoFitWidth="0" ss:Width="138.75"/>'+
'<Column ss:AutoFitWidth="0" ss:Width="72"/>'+
'<Column ss:Index="12" ss:AutoFitWidth="0" ss:Width="75.75"/>'+
'<Column ss:Index="16" ss:AutoFitWidth="0" ss:Width="54.75"/>'+
'<Row ss:AutoFitHeight="0" ss:Height="15.75">'+
'<Cell ss:MergeAcross="16" ss:MergeDown="1" ss:StyleID="s47">'+
'<Data ss:Type="String">Inventory Book Detail</Data>'+
'</Cell>'+
'</Row>'+
//'<Row ss:AutoFitHeight="0"/>'+
'<Row ss:Index="4">'+
'<Cell>'+
'<Data ss:Type="String">Company Information</Data>'+
'</Cell>'+
'<Cell>'+
'<Data ss:Type="String">'+companyName+'</Data>'+
'</Cell>'+
'<Cell ss:Index="16">'+
'<Data ss:Type="String">Print Date</Data>'+
'</Cell>'+
'<Cell>'+
'<Data ss:Type="String">'+dateInRegion+'</Data>'+
'</Cell>'+
'</Row>'+
'<Row>'+
'<Cell>'+
'<Data ss:Type="String">Company Address</Data>'+
'</Cell>'+
'<Cell>'+
'<Data ss:Type="String">'+companyAddress+'</Data>'+
'</Cell>'+
'</Row>'+
'<Row>'+
'<Cell>'+
'<Data ss:Type="String">Company Profession</Data>'+
'</Cell>'+
'<Cell>'+
'<Data ss:Type="String">'+companyProff+'</Data>'+
'</Cell>'+
'</Row>'+
'<Row>'+
'<Cell>'+
'<Data ss:Type="String">Company Tax Red Number</Data>'+
'</Cell>'+
'<Cell>'+
'<Data ss:Type="String">'+VATReg+'</Data>'+
'</Cell>'+
'</Row>'+
'<Row ss:Index="8">'+
'<Cell ss:MergeAcross="16" ss:StyleID="s37">'+
'<Data ss:Type="String">ΒΙΒΛΙΟ ΑΠΟΘΗΚΗΣ</Data>'+
'</Cell>'+
'<Cell ss:StyleID="s32"/>'+
'<Cell ss:StyleID="s33"/>'+
'<Cell ss:StyleID="s33"/>'+
'</Row>'+
'<Row>'+
'<Cell ss:MergeAcross="16" ss:StyleID="s37">'+
'<Data ss:Type="String">Ημερομηνία από: '+dateFrom+'έως:'+dateTo+'</Data>'+
'</Cell>'+
'<Cell ss:StyleID="s32"/>'+
'<Cell ss:StyleID="s32"/>'+
'<Cell ss:StyleID="s32"/>'+
'</Row>'+
'<Row>'+
'<Cell ss:MergeAcross="16" ss:StyleID="s38">'+
'<Data ss:Type="String">GR - INVENTORY BOOK</Data>'+
'</Cell>'+
'<Cell ss:StyleID="s34"/>'+
'</Row>'+
'<Row>'+
'<Cell ss:MergeAcross="16" ss:StyleID="s38">'+
'<Data ss:Type="String">Date From: '+dateFrom+' To: '+dateTo+'</Data>'+
'</Cell>'+
'<Cell ss:StyleID="s32"/>'+
'</Row>'+
'<Row ss:Height="15.75"/>'+
'<Row ss:Height="30.75">'+
'<Cell ss:StyleID="s18">'+
'<Data ss:Type="String">ΒιβλίοΚ ωδικός</Data>'+
'</Cell>'+
'<Cell ss:StyleID="s18">'+
'<Data ss:Type="String">Αποθήκης Περιγραφή</Data>'+
'</Cell>'+
'<Cell ss:MergeAcross="1" ss:StyleID="m87231016">'+
'<Data ss:Type="String">Από Μεταφορά</Data>'+
'</Cell>'+
'<Cell ss:MergeAcross="1" ss:StyleID="s46">'+
'<Data ss:Type="String">Αγορές</Data>'+
'</Cell>'+
'<Cell ss:MergeAcross="1" ss:StyleID="s36">'+
'<Data ss:Type="String">Λοιπές Εισαγωγές</Data>'+
'</Cell>'+
'<Cell ss:MergeAcross="1" ss:StyleID="s44">'+
'<Data ss:Type="String">Λοιπές Εξαγωγές</Data>'+
'</Cell>'+
'<Cell ss:MergeAcross="2" ss:StyleID="s45">'+
'<Data ss:Type="String">Πωλήσεις</Data>'+
'</Cell>'+
'<Cell ss:MergeAcross="1" ss:StyleID="s45">'+
'<Data ss:Type="String">Αποτέλεσμα</Data>'+
'</Cell>'+
'<Cell ss:MergeAcross="1" ss:StyleID="s36">'+
'<Data ss:Type="String">Υπόλοιπο</Data>'+
'</Cell>'+
'</Row>'+
'<Row ss:Height="45.75">'+
'<Cell ss:StyleID="s17"/>'+
'<Cell ss:StyleID="s17"/>'+
'<Cell ss:StyleID="s17">'+
'<Data ss:Type="String">Ποσότητα</Data>'+
'</Cell>'+
'<Cell ss:StyleID="s17">'+
'<Data ss:Type="String">Αξία</Data>'+
'</Cell>'+
'<Cell ss:StyleID="s17">'+
'<Data ss:Type="String">Ποσότητα</Data>'+
'</Cell>'+
'<Cell ss:StyleID="s17">'+
'<Data ss:Type="String">Αξία</Data>'+
'</Cell>'+
'<Cell ss:StyleID="s17">'+
'<Data ss:Type="String">Ποσότητα</Data>'+
'</Cell>'+
'<Cell ss:StyleID="s17">'+
'<Data ss:Type="String">Αξία</Data>'+
'</Cell>'+
'<Cell ss:StyleID="s17">'+
'<Data ss:Type="String">Ποσότητα</Data>'+
'</Cell>'+
'<Cell ss:StyleID="s17">'+
'<Data ss:Type="String">Αξία</Data>'+
'</Cell>'+
'<Cell ss:StyleID="s17">'+
'<Data ss:Type="String">Ποσότητα</Data>'+
'</Cell>'+
'<Cell ss:StyleID="s17">'+
'<Data ss:Type="String">Αξία</Data>'+
'</Cell>'+
'<Cell ss:StyleID="s17">'+
'<Data ss:Type="String">Κόστος Π.</Data>'+
'</Cell>'+
'<Cell ss:StyleID="s17">'+
'<Data ss:Type="String">Μικτό Κέρ.</Data>'+
'</Cell>'+
'<Cell ss:StyleID="s17">'+
'<Data ss:Type="String">Μέση Τιμή Μον.</Data>'+
'</Cell>'+
'<Cell ss:StyleID="s17">'+
'<Data ss:Type="String">Ποσότητα</Data>'+
'</Cell>'+
'<Cell ss:StyleID="s17">'+
'<Data ss:Type="String">Αξία</Data>'+
'</Cell>'+
'</Row>'+
'<Row ss:Height="15.75">'+
'<Cell ss:StyleID="s26"/>'+
'<Cell ss:StyleID="s27"/>'+
'<Cell ss:MergeAcross="1" ss:StyleID="m71097472">'+
'<Data ss:Type="String">Balance Forwarded</Data>'+
'</Cell>'+
'<Cell ss:MergeAcross="1" ss:StyleID="m71097492">'+
'<Data ss:Type="String">Purchase</Data>'+
'</Cell>'+
'<Cell ss:MergeAcross="1" ss:StyleID="m71097512">'+
'<Data ss:Type="String">Movement Receipts</Data>'+
'</Cell>'+
'<Cell ss:MergeAcross="1" ss:StyleID="m71097532">'+
'<Data ss:Type="String">Movement Issue</Data>'+
'</Cell>'+
'<Cell ss:MergeAcross="1" ss:StyleID="m71097552">'+
'<Data ss:Type="String">Sales</Data>'+
'</Cell>'+
'<Cell ss:MergeAcross="2" ss:StyleID="m87230976"/>'+
'<Cell ss:MergeAcross="1" ss:StyleID="m87230996">'+
'<Data ss:Type="String">Balance</Data>'+
'</Cell>'+
'</Row>'+
'<Row ss:AutoFitHeight="0" ss:Height="30">'+
'<Cell ss:StyleID="s27">'+
'<Data ss:Type="String">Item code</Data>'+
'</Cell>'+
'<Cell ss:StyleID="s27">'+
'<Data ss:Type="String">Item Description</Data>'+
'</Cell>'+
'<Cell ss:StyleID="s27">'+
'<Data ss:Type="String">QTY</Data>'+
'</Cell>'+
'<Cell ss:StyleID="s27">'+
'<Data ss:Type="String">Price</Data>'+
'</Cell>'+
'<Cell ss:StyleID="s27">'+
'<Data ss:Type="String">QTY</Data>'+
'</Cell>'+
'<Cell ss:StyleID="s27">'+
'<Data ss:Type="String">Price</Data>'+
'</Cell>'+
'<Cell ss:StyleID="s27">'+
'<Data ss:Type="String">QTY</Data>'+
'</Cell>'+
'<Cell ss:StyleID="s27">'+
'<Data ss:Type="String">Price</Data>'+
'</Cell>'+
'<Cell ss:StyleID="s27">'+
'<Data ss:Type="String">QTY</Data>'+
'</Cell>'+
'<Cell ss:StyleID="s27">'+
'<Data ss:Type="String">Price</Data>'+
'</Cell>'+
'<Cell ss:StyleID="s27">'+
'<Data ss:Type="String">QTY</Data>'+
'</Cell>'+
'<Cell ss:StyleID="s27">'+
'<Data ss:Type="String">Price</Data>'+
'</Cell>'+
'<Cell ss:StyleID="s27">'+
'<Data ss:Type="String">Cost of Style</Data>'+
'</Cell>'+
'<Cell ss:StyleID="s27">'+
'<Data ss:Type="String">Gross Margin</Data>'+
'</Cell>'+
'<Cell ss:StyleID="s27">'+
'<Data ss:Type="String">Unit Avg Cost</Data>'+
'</Cell>'+
'<Cell ss:StyleID="s27">'+
'<Data ss:Type="String">QTY</Data>'+
'</Cell>'+
'<Cell ss:StyleID="s27">'+
'<Data ss:Type="String">Price</Data>'+
'</Cell>'+
'</Row>';
for (var key in searchR) {
var objItems ={};
// For Loc total
var locBFQty=0,locBFValue=0,locPQuantity=0,locPvalue=0,locSQuantity=0,locSvalue=0,locUnitAvgCost=0,locBalanceValue=0,locBalanceQty=0,locGross=0,locCOS=0,locReptValue=0;
var locReptQty=0,locIssueValue=0,locIssueQty=0;
// to sort with grp
var outObject = searchR[key].reduce(function (a, e) {
var estKey = e["group"];
(a[estKey] ? a[estKey] : a[estKey] = null || []).push(e);
return a;
}, {});
XML += "<Row ss:AutoFitHeight=\"0\">";
XML += "<Cell><Data ss:Type=\"String\">Location : "+key+"<\/Data><\/Cell>";
XML += "<\/Row>";
for (var key2 in outObject) {
XML += "<Row ss:AutoFitHeight=\"0\">";
XML += "<Cell><Data ss:Type=\"String\">Group : "+key2+"<\/Data><\/Cell>";
XML += "<\/Row>";
var GrpBFQty=0,GrpBFValue=0,GrpPQuantity=0,GrpPvalue=0,GrpSQuantity=0,GrpSvalue=0,GrpUnitAvgCost=0,GrpBalanceValue=0,GrpBalanceQty=0,GrpGross=0,GrpCOS=0,GrpReptValue=0;
var GrpReptQty=0,GrpIssueValue=0,GrpIssueQty=0;
for(var i=0;i<outObject[key2].length;i++)
{
XML += "<Row ss:AutoFitHeight=\"0\">";
XML += "<Cell><Data ss:Type=\"String\">"+checkif(outObject[key2][i].itemName)+"<\/Data><\/Cell>";
XML += "<Cell><Data ss:Type=\"String\">"+checkif(outObject[key2][i].itemDes)+"<\/Data><\/Cell>";
XML += "<Cell><Data ss:Type=\"String\">"+formatCurrency(checkif(fixFloat(outObject[key2][i].balanceForwardQty)))+"<\/Data><\/Cell>";
locBFQty=parseFloat(locBFQty)+parseFloat(outObject[key2][i].balanceForwardQty);
var BFValue=outObject[key2][i].balanceForwardAmt;
locBFValue=parseFloat(locBFValue)+parseFloat(BFValue);
XML += " <Cell><Data ss:Type=\"String\">"+formatCurrency((fixFloat(BFValue)))+"<\/Data><\/Cell>";
locPQuantity=parseFloat(locPQuantity)+parseFloat(outObject[key2][i].pQuantity);
XML += " <Cell><Data ss:Type=\"String\">"+formatCurrency((fixFloat(outObject[key2][i].pQuantity)))+"<\/Data><\/Cell>";
locPvalue=parseFloat(locPvalue)+parseFloat(outObject[key2][i].pValue);
XML += " <Cell><Data ss:Type=\"String\">"+formatCurrency((fixFloat(outObject[key2][i].pValue)))+"<\/Data><\/Cell>";
locReptQty=parseFloat(locReptQty)+parseFloat(outObject[key2][i].MRQ);
XML += " <Cell><Data ss:Type=\"String\">"+formatCurrency(checkif(outObject[key2][i].MRQ))+"<\/Data><\/Cell>";
locReptValue=parseFloat(locReptValue)+parseFloat(outObject[key2][i].MRV);
XML += " <Cell><Data ss:Type=\"String\">"+formatCurrency(checkif(outObject[key2][i].MRV))+"<\/Data><\/Cell>";
locIssueQty=parseFloat(locIssueQty)+parseFloat(outObject[key2][i].MIQ);
XML += " <Cell><Data ss:Type=\"String\">"+formatCurrency(checkif(outObject[key2][i].MIQ))+"<\/Data><\/Cell>";
locIssueValue=parseFloat(locIssueValue)+parseFloat(outObject[key2][i].MIV);
XML += " <Cell><Data ss:Type=\"String\">"+formatCurrency(checkif(outObject[key2][i].MIV))+"<\/Data><\/Cell>";
locSQuantity=parseFloat(locSQuantity)+parseFloat(outObject[key2][i].sQty);
XML += " <Cell><Data ss:Type=\"String\">"+formatCurrency((fixFloat(outObject[key2][i].sQty)))+"<\/Data><\/Cell>";
locSvalue=parseFloat(locSvalue)+parseFloat(outObject[key2][i].sValue);
XML += " <Cell><Data ss:Type=\"String\">"+formatCurrency((fixFloat(outObject[key2][i].sValue)))+"<\/Data><\/Cell>";
var BQuantity = parseFloat(outObject[key2][i].balanceForwardQty)+parseFloat(outObject[key2][i].pQuantity)+parseFloat(outObject[key2][i].MRQ)-parseFloat(outObject[key2][i].MIQ)-parseFloat(outObject[key2][i].sQty);
// locBalanceQty
locBalanceQty=parseFloat(locBalanceQty)+parseFloat(BQuantity);
GrpBalanceQty=parseFloat(GrpBalanceQty)+parseFloat(BQuantity);
var Bvalue = BQuantity*outObject[key2][i].unitAvgCost;
// locBalanceValue
locBalanceValue=parseFloat(locBalanceValue)+parseFloat(Bvalue);
GrpBalanceValue=parseFloat(GrpBalanceValue)+parseFloat(Bvalue);
var COS =parseFloat(outObject[key2][i].COS);
// locCOS
locCOS=parseFloat(locCOS)+parseFloat(outObject[key2][i].COS);
// var grossMargin = parseFloat(outObject[key2][i].sValue)-COS;
var grossMargin = parseFloat(outObject[key2][i].GrossMrgin)
// locGross
locGross=parseFloat(locGross)+parseFloat(outObject[key2][i].GrossMrgin);
// GROUP TOTAL
GrpBFQty=parseFloat(GrpBFQty)+parseFloat(outObject[key2][i].balanceForwardQty);
GrpBFValue=parseFloat(GrpBFValue)+parseFloat(BFValue);
GrpPQuantity=parseFloat(GrpPQuantity)+parseFloat(outObject[key2][i].pQuantity);
GrpPvalue=parseFloat(GrpPvalue)+parseFloat(outObject[key2][i].pValue);
GrpReptQty=parseFloat(GrpReptQty)+parseFloat(outObject[key2][i].MRQ);
GrpReptValue=parseFloat(GrpReptValue)+parseFloat(outObject[key2][i].MRV);
GrpIssueQty=parseFloat(GrpIssueQty)+parseFloat(outObject[key2][i].MIQ);
GrpIssueValue=parseFloat(GrpIssueValue)+parseFloat(outObject[key2][i].MIV);
GrpSQuantity=parseFloat(GrpSQuantity)+parseFloat(outObject[key2][i].sQty);
GrpSvalue=parseFloat(GrpSvalue)+parseFloat(outObject[key2][i].sValue);
GrpBalanceQty=parseFloat(GrpBalanceQty)+parseFloat(BQuantity);
GrpBalanceValue=parseFloat(GrpBalanceValue)+parseFloat(Bvalue);
GrpCOS=parseFloat(GrpCOS)+parseFloat(outObject[key2][i].COS);
GrpGross=parseFloat(GrpGross)+parseFloat(outObject[key2][i].GrossMrgin);
GrpUnitAvgCost=parseFloat(GrpUnitAvgCost)+parseFloat(outObject[key2][i].unitAvgCost);
// Grand total
totalBFQty=parseFloat(totalBFQty)+parseFloat(outObject[key2][i].balanceForwardQty);
totalBFValue=parseFloat(totalBFValue)+parseFloat(BFValue);
totalPQuantity=parseFloat(totalPQuantity)+parseFloat(outObject[key2][i].pQuantity);
totalPvalue=parseFloat(totalPvalue)+parseFloat(outObject[key2][i].pValue);
totalSQuantity = parseFloat(totalSQuantity)+parseFloat(outObject[key2][i].sQty);
totalSvalue=parseFloat(totalSvalue)+parseFloat(outObject[key2][i].sValue);
totalunitAvgCost=parseFloat(totalunitAvgCost)+parseFloat(outObject[key2][i].unitAvgCost);
totalBalanceQty=parseFloat(totalBalanceQty)+parseFloat(BQuantity);
totalBalanceValue=parseFloat(totalBalanceValue)+parseFloat(Bvalue);
totalIssueQty=parseFloat(totalIssueQty)+parseFloat(outObject[key2][i].MIQ);
totalIssueValue=parseFloat(totalIssueValue)+parseFloat(outObject[key2][i].MIV);
totalReptQty=parseFloat(totalReptQty)+parseFloat(outObject[key2][i].MRQ);
totalReptValue=parseFloat(totalReptValue)+parseFloat(outObject[key2][i].MRV);
totalCOS=parseFloat(totalCOS)+(outObject[key2][i].COS);
totalGross=parseFloat(totalGross)+parseFloat(outObject[key2][i].GrossMrgin);
XML += " <Cell><Data ss:Type=\"String\">"+formatCurrency((outObject[key2][i].COS))+"<\/Data><\/Cell>";
XML += " <Cell><Data ss:Type=\"String\">"+formatCurrency((outObject[key2][i].GrossMrgin))+"<\/Data><\/Cell>";
XML += " <Cell><Data ss:Type=\"String\">"+formatCurrency((fixFloat(outObject[key2][i].unitAvgCost)))+"<\/Data><\/Cell>";
XML += " <Cell><Data ss:Type=\"String\">"+formatCurrency((fixFloat(BQuantity)))+"<\/Data><\/Cell>";
XML += " <Cell><Data ss:Type=\"String\">"+formatCurrency((fixFloat(Bvalue)))+"<\/Data><\/Cell>";
XML += " <\/Row>";
}
// To show GRP total
XML += "<Row ss:AutoFitHeight=\"0\">";
XML += "<Cell ss:StyleID=\"s108\"><Data ss:Type=\"String\">Group Total<\/Data><\/Cell>";
XML += "<Cell ss:StyleID=\"s108\"><Data ss:Type=\"String\"><\/Data><\/Cell>";
XML += "<Cell ss:StyleID=\"s108\"><Data ss:Type=\"String\">"+formatCurrency((fixFloat(GrpBFQty)))+"<\/Data><\/Cell>";
XML += " <Cell ss:StyleID=\"s108\"><Data ss:Type=\"String\">"+formatCurrency((fixFloat(GrpBFValue)))+"<\/Data><\/Cell>";
XML += " <Cell ss:StyleID=\"s108\"><Data ss:Type=\"String\">"+formatCurrency((fixFloat(GrpPQuantity)))+"<\/Data><\/Cell>";
XML += " <Cell ss:StyleID=\"s108\"><Data ss:Type=\"String\">"+formatCurrency((fixFloat(GrpPvalue)))+"<\/Data><\/Cell>";
XML += " <Cell ss:StyleID=\"s108\"><Data ss:Type=\"String\">"+formatCurrency(fixFloat(GrpReptQty))+"<\/Data><\/Cell>";
XML += " <Cell ss:StyleID=\"s108\"><Data ss:Type=\"String\">"+formatCurrency(fixFloat(GrpReptValue))+"<\/Data><\/Cell>";
XML += " <Cell ss:StyleID=\"s108\"><Data ss:Type=\"String\">"+formatCurrency(fixFloat(GrpIssueQty))+"<\/Data><\/Cell>";
XML += " <Cell ss:StyleID=\"s108\"><Data ss:Type=\"String\">"+formatCurrency(fixFloat(GrpIssueValue))+"<\/Data><\/Cell>";
XML += " <Cell ss:StyleID=\"s108\"><Data ss:Type=\"String\">"+formatCurrency(fixFloat(GrpSQuantity))+"<\/Data><\/Cell>";
XML += " <Cell ss:StyleID=\"s108\"><Data ss:Type=\"String\">"+formatCurrency(fixFloat(GrpSvalue))+"<\/Data><\/Cell>";
XML += " <Cell ss:StyleID=\"s108\"><Data ss:Type=\"String\">"+formatCurrency(fixFloat(GrpCOS))+"<\/Data><\/Cell>";
XML += " <Cell ss:StyleID=\"s108\"><Data ss:Type=\"String\">"+formatCurrency(fixFloat(GrpGross))+"<\/Data><\/Cell>";
XML += " <Cell ss:StyleID=\"s108\"><Data ss:Type=\"String\">"+formatCurrency(fixFloat(GrpUnitAvgCost))+"<\/Data><\/Cell>";
XML += " <Cell ss:StyleID=\"s108\"><Data ss:Type=\"String\">"+formatCurrency(fixFloat(GrpBalanceQty))+"<\/Data><\/Cell>";
XML += " <Cell ss:StyleID=\"s108\"><Data ss:Type=\"String\">"+formatCurrency(fixFloat(GrpBalanceValue))+"<\/Data><\/Cell>";
XML += " <\/Row>";
}
// To show Loc total
XML += "<Row ss:AutoFitHeight=\"0\">";
XML += " <\/Row>";
XML += "<Row ss:AutoFitHeight=\"0\">";
XML += "<Cell ss:StyleID=\"s107\"><Data ss:Type=\"String\">Location Total<\/Data><\/Cell>";
XML += "<Cell ss:StyleID=\"s107\"><Data ss:Type=\"String\"><\/Data><\/Cell>";
XML += "<Cell ss:StyleID=\"s107\"><Data ss:Type=\"String\">"+formatCurrency((fixFloat(locBFQty)))+"<\/Data><\/Cell>";
XML += " <Cell ss:StyleID=\"s107\"><Data ss:Type=\"String\">"+formatCurrency((fixFloat(locBFValue)))+"<\/Data><\/Cell>";
XML += " <Cell ss:StyleID=\"s107\"><Data ss:Type=\"String\">"+formatCurrency((fixFloat(locPQuantity)))+"<\/Data><\/Cell>";
XML += " <Cell ss:StyleID=\"s107\"><Data ss:Type=\"String\">"+formatCurrency((fixFloat(locPvalue)))+"<\/Data><\/Cell>";
XML += " <Cell ss:StyleID=\"s107\"><Data ss:Type=\"String\">"+formatCurrency(fixFloat(locReptQty))+"<\/Data><\/Cell>";
XML += " <Cell ss:StyleID=\"s107\"><Data ss:Type=\"String\">"+formatCurrency(fixFloat(locReptValue))+"<\/Data><\/Cell>";
XML += " <Cell ss:StyleID=\"s107\"><Data ss:Type=\"String\">"+formatCurrency(fixFloat(locIssueQty))+"<\/Data><\/Cell>";
XML += " <Cell ss:StyleID=\"s107\"><Data ss:Type=\"String\">"+formatCurrency(fixFloat(locIssueValue))+"<\/Data><\/Cell>";
XML += " <Cell ss:StyleID=\"s107\"><Data ss:Type=\"String\">"+formatCurrency(fixFloat(locSQuantity))+"<\/Data><\/Cell>";
XML += " <Cell ss:StyleID=\"s107\"><Data ss:Type=\"String\">"+formatCurrency(fixFloat(locSvalue))+"<\/Data><\/Cell>";
XML += " <Cell ss:StyleID=\"s107\"><Data ss:Type=\"String\">"+formatCurrency(fixFloat(locCOS))+"<\/Data><\/Cell>";
XML += " <Cell ss:StyleID=\"s107\"><Data ss:Type=\"String\">"+formatCurrency(fixFloat(locGross))+"<\/Data><\/Cell>";
XML += " <Cell ss:StyleID=\"s107\"><Data ss:Type=\"String\">"+formatCurrency(fixFloat(locUnitAvgCost))+"<\/Data><\/Cell>";
XML += " <Cell ss:StyleID=\"s107\"><Data ss:Type=\"String\">"+formatCurrency(fixFloat(locBalanceQty))+"<\/Data><\/Cell>";
XML += " <Cell ss:StyleID=\"s107\"><Data ss:Type=\"String\">"+formatCurrency(fixFloat(locBalanceValue))+"<\/Data><\/Cell>";
XML += " <\/Row>";
}
XML += "<Row ss:AutoFitHeight=\"0\">";
XML += " <\/Row>";
XML += " <Row ss:AutoFitHeight=\"0\">";
XML += " <Cell ss:StyleID=\"s107\"><Data ss:Type=\"String\">GRAND TOTAL<\/Data><\/Cell>";
XML += " <Cell ss:StyleID=\"s107\"><Data ss:Type=\"String\"><\/Data><\/Cell>";
XML += " <Cell ss:StyleID=\"s107\"><Data ss:Type=\"String\">"+formatCurrency((fixFloat(totalBFQty)))+"<\/Data><\/Cell>";
XML += " <Cell ss:StyleID=\"s107\"><Data ss:Type=\"String\">"+formatCurrency((fixFloat(totalBFValue)))+"<\/Data><\/Cell>";
XML += " <Cell ss:StyleID=\"s107\"><Data ss:Type=\"String\">"+formatCurrency((fixFloat(totalPQuantity)))+"<\/Data><\/Cell>";
XML += " <Cell ss:StyleID=\"s107\"><Data ss:Type=\"String\">"+formatCurrency((fixFloat(totalPvalue)))+"<\/Data><\/Cell>";
XML += " <Cell ss:StyleID=\"s107\"><Data ss:Type=\"String\">"+formatCurrency((fixFloat(totalReptQty)))+"<\/Data><\/Cell>";
XML += " <Cell ss:StyleID=\"s107\"><Data ss:Type=\"String\">"+formatCurrency((fixFloat(totalReptValue)))+"<\/Data><\/Cell>";
XML += " <Cell ss:StyleID=\"s107\"><Data ss:Type=\"String\">"+formatCurrency((fixFloat(totalIssueQty)))+"<\/Data><\/Cell>";
XML += " <Cell ss:StyleID=\"s107\"><Data ss:Type=\"String\">"+formatCurrency((fixFloat(totalIssueValue)))+"<\/Data><\/Cell>";
XML += " <Cell ss:StyleID=\"s107\"><Data ss:Type=\"String\">"+formatCurrency((fixFloat(totalSQuantity)))+"<\/Data><\/Cell>";
XML += " <Cell ss:StyleID=\"s107\"><Data ss:Type=\"String\">"+formatCurrency((fixFloat(totalSvalue)))+"<\/Data><\/Cell>";
XML += " <Cell ss:StyleID=\"s107\"><Data ss:Type=\"String\">"+formatCurrency((fixFloat(totalCOS)))+"<\/Data><\/Cell>";
XML += " <Cell ss:StyleID=\"s107\"><Data ss:Type=\"String\">"+formatCurrency((fixFloat(totalGross)))+"<\/Data><\/Cell>";
XML += " <Cell ss:StyleID=\"s107\"><Data ss:Type=\"String\">"+formatCurrency((fixFloat(totalunitAvgCost)))+"<\/Data><\/Cell>";
XML += " <Cell ss:StyleID=\"s107\"><Data ss:Type=\"String\">"+formatCurrency((fixFloat(totalBalanceQty)))+"<\/Data><\/Cell>";
XML += " <Cell ss:StyleID=\"s107\"><Data ss:Type=\"String\">"+formatCurrency((fixFloat(totalBalanceValue)))+"<\/Data><\/Cell>";
XML += " <\/Row>";
XML += '</Table>'+
' <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>'+
' <Unsynced/>'+
' <Print>'+
' <ValidPrinterInfo/>'+
' <PaperSizeIndex>9</PaperSizeIndex>'+
' <HorizontalResolution>600</HorizontalResolution>'+
' <VerticalResolution>600</VerticalResolution>'+
' </Print>'+
' <Selected/>'+
' <Panes>'+
' <Pane>'+
' <Number>3</Number>'+
' <ActiveRow>11</ActiveRow>'+
' <ActiveCol>8</ActiveCol>'+
' </Pane>'+
' </Panes>'+
' <ProtectObjects>False</ProtectObjects>'+
' <ProtectScenarios>False</ProtectScenarios>'+
' </WorksheetOptions>'+
' </Worksheet>'+
' <Worksheet ss:Name="Sheet2">'+
' <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1000000" x:FullColumns="1"'+
' x:FullRows="1" ss:DefaultRowHeight="15">'+
' <Row ss:AutoFitHeight="0"/>'+
' </Table>'+
' <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>'+
' <Unsynced/>'+
' <ProtectObjects>False</ProtectObjects>'+
' <ProtectScenarios>False</ProtectScenarios>'+
' </WorksheetOptions>'+
' </Worksheet>'+
' <Worksheet ss:Name="Sheet3">'+
' <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"'+
' x:FullRows="1" ss:DefaultRowHeight="15">'+
' <Row ss:AutoFitHeight="0"/>'+
' </Table>'+
' <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>'+
' <Unsynced/>'+
' <ProtectObjects>False</ProtectObjects>'+
' <ProtectScenarios>False</ProtectScenarios>'+
' </WorksheetOptions>'+
' </Worksheet>'+
'</Workbook>';
var XML_TO_PRINT = XML;
var strXmlEncoded = encode.convert({
string : XML,
inputEncoding : encode.Encoding.UTF_8,
outputEncoding : encode.Encoding.BASE_64
});
var excelFile = file.create({
name : 'Inventory Book Detail.xls',
fileType : file.Type.EXCEL,
contents : strXmlEncoded
});
context.response.writeFile(excelFile, true);
}
else
{
context.response.write("No Search Result found, Kindly change the date range or Subsidiary!!");
}
}
else
{
var CURRENT_PAGE = url.resolveScript({
scriptId: "customscript_nhs2_sl_show_inv_book",
deploymentId: "customdeploy_nhs2_sl_show_inv_book",
returnExternalUrl: false,
params:{
'dateFrom':dateFrom,
'dateTo':dateTo,
'customer':subId,
'location':location
}
});
///AJ MODIFIED ON 07/01/2018
//HTML PAGE CONTENTS
var HTML_PAGE = '';
HTML_PAGE += '<!DOCTYPE html> <html> <head> <title>Inventoy Book Detail Report</title> <meta charset="utf-8"> <meta name="viewport" content="width=device-width, initial-scale=1"> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css"> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script> <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>';
//CSS on HTML HEAD
HTML_PAGE += '<style>';
HTML_PAGE += 'th, td {text-align: center; } .bold-font, .bold-font td, .bold-font tr {font-weight: bold; }';
HTML_PAGE += '.collection-visibility, .payment-visibility {display: none; }';
HTML_PAGE += '</style>';
//SCRIPT on HTML HEAD
HTML_PAGE += '</head><body>';
HTML_PAGE += '<div class="container-fluid" style="width: 93%;">';
HTML_PAGE += '<div class="" style="width: 100%;">';
HTML_PAGE += '<button style="float: right;" type="button" class="btn btn-link" onclick="exportAsExcel();">↓Download</button>';
HTML_PAGE += '</div>';
HTML_PAGE +='<div class="container-fluid"><h2>Inventory Book Details Report</h2><div class="table-responsive"><table class="table table-bordered table-striped table-hover"><thead>';
//To Download this page
HTML_PAGE += '<script type="text/javascript">';
HTML_PAGE += 'function exportAsExcel(){';
HTML_PAGE += 'var url ="' + CURRENT_PAGE + '";';
log.debug("CURRENT_PAGE=",CURRENT_PAGE);
HTML_PAGE += 'window.open(url+"&_method=download");';
HTML_PAGE += '}';
HTML_PAGE += '</script>';
HTML_PAGE += '<tr><th style="text-align:left;padding: 2px;" colspan="2";>Company Information:</th><th style="text-align:left;padding: 2px;" colspan="11">'+companyName+'</th><th style="text-align:left;padding: 2px;" colspan="4";>Print Date:'+dateInRegion+'</th></tr>';
HTML_PAGE += '<tr><th style="text-align:left;padding: 2px;" colspan="2">Company Profession</th>';
HTML_PAGE += '<th style="text-align:left;padding: 2px;" colspan="15">'+companyProff+'</th></tr>';
HTML_PAGE += '<tr><th style="text-align:left;padding: 2px;" colspan="2">Company address</th>';
HTML_PAGE += '<th style="text-align:left;padding: 2px;" colspan="15">'+companyAddress+'</th></tr>';
HTML_PAGE += '<tr><th style="text-align:left;padding: 2px;" colspan="2">Company Tax Red Number</th>';
HTML_PAGE += '<th style="text-align:left;padding: 2px;" colspan="15">'+VATReg+'</th></tr>';
HTML_PAGE +='<tr style="background-color: aliceblue;"><th colspan="17">ΒΙΒΛΙΟ ΑΠΟΘΗΚΗΣ</th></tr>';
HTML_PAGE +='<tr style="background-color: aliceblue;"><th colspan="17">Ημερομηνία από:'+dateFrom+ ' έως:'+dateTo+'</th></tr>';
HTML_PAGE +='<tr style="background-color: oldlace;"><th colspan="17">GR - INVENTORY BOOK</th></tr>';
HTML_PAGE +='<tr style="background-color: oldlace;"><th colspan="17">Date From:'+dateFrom+' To:'+dateTo+'</th></tr>';
for (var key in searchR) {
var objItems ={};
// For Loc total
var locBFQty=0,locBFValue=0,locPQuantity=0,locPvalue=0,locSQuantity=0,locSvalue=0,locUnitAvgCost=0,locBalanceValue=0,locBalanceQty=0,locGross=0,locCOS=0,locReptValue=0;
var locReptQty=0,locIssueValue=0,locIssueQty=0;
// to sort with grp
var outObject = searchR[key].reduce(function (a, e) {
var estKey = e["group"];
(a[estKey] ? a[estKey] : a[estKey] = null || []).push(e);
return a;
}, {});
HTML_PAGE +='<tr style="background-color: white;text-align:left;"><td colspan="17" style="text-align:left;padding: 2px;">Location: '+key+'</td></tr>';
// loop with grp
for(var key2 in outObject)
{
HTML_PAGE +='<tr style="background-color: white;"><td colspan="17" style="text-align:left;padding: 2px;">Product Group : '+key2+'</td></tr>';
HTML_PAGE+='<tr style="background-color: aliceblue;"><th>ΒιβλίοΚ ωδικός</th><th>Αποθήκης Περιγραφή</th><th style="text-align:left;" colspan="2">Από Μεταφορά </th><th style="text-align:left;" colspan="2">Αγορές</th><th style="text-align:left;" colspan="2">Λοιπές Εισαγωγές</th><th style="text-align:left;" colspan="2">Λοιπές Εξαγωγές</th><th style="text-align:left;" colspan="2">Πωλήσεις</th><th style="text-align:left;" colspan="3">Αποτέλεσμα</th><th style="text-align:left;" colspan="2">Υπόλοιπο</th></tr>';
HTML_PAGE +='<tr style="background-color: aliceblue;"><th></th><th></th><th>Ποσότητα</th><th>Αξία</th><th>Ποσότητα</th><th>Αξία</th><th>Ποσότητα</th><th>Αξία</th><th>Ποσότητα</th><th>Αξία</th><th>Ποσότητα</th><th>Αξία</th><th>Κόστος Π.</th><th>Μικτό Κέρ.</th><th>Μέση Τιμή Μον.</th><th>Ποσότητα</th><th>Αξία</th></tr>';
HTML_PAGE +='<tr style="background-color:oldlace;"><th></th><th></th><th style="text-align:left;" colspan="2">Balance Forwarded</th><th style="text-align:left;" colspan="2">Purchase</th>';
HTML_PAGE +='<th style="text-align:left;" colspan="2">Movements receipts</th><th style="text-align:left;" colspan="2">Movement Issue</th>';
HTML_PAGE +='<th style="text-align:left;" colspan="2">Sales</th><th style="text-align:left;" colspan="3"></th><th style="text-align:left;" colspan="2">Balance</th></tr>';
HTML_PAGE +=' <tr style="background-color:oldlace;"><th>Item code</th><th>Item Description</th><th>QTY</th><th>Price</th><th>QTY</th><th>Price</th><th>QTY</th><th>Price</th><th>QTY</th><th>Price</th><th>QTY</th><th>Price</th><th>Cost of Sale</th><th>Gross Margin</th><th>Unit Avg Cost</th><th>QTY</th><th>Price</th></tr>';
HTML_PAGE +='</thead><tbody>';
var GrpBFQty=0,GrpBFValue=0,GrpPQuantity=0,GrpPvalue=0,GrpSQuantity=0,GrpSvalue=0,GrpUnitAvgCost=0,GrpBalanceValue=0,GrpBalanceQty=0,GrpGross=0,GrpCOS=0,GrpReptValue=0;
var GrpReptQty=0,GrpIssueValue=0,GrpIssueQty=0;
for(var i=0;i<outObject[key2].length;i++)
{
HTML_PAGE +='<tr class="bold-font"><td>'+outObject[key2][i].itemName+'</td>';
HTML_PAGE +='<td>'+outObject[key2][i].itemDes+'</td>';
HTML_PAGE +='<td>'+formatCurrency(fixFloat(outObject[key2][i].balanceForwardQty))+'</td>';
GrpBFQty=parseFloat(GrpBFQty)+parseFloat(outObject[key2][i].balanceForwardQty);
locBFQty=parseFloat(locBFQty)+parseFloat(outObject[key2][i].balanceForwardQty);
var BFValue=outObject[key2][i].balanceForwardAmt;
GrpBFValue=parseFloat(GrpBFValue)+parseFloat(BFValue);
locBFValue=parseFloat(locBFValue)+parseFloat(BFValue);
HTML_PAGE +='<td>'+formatCurrency((fixFloat(BFValue)))/*.toFixed(2)*/+'</td>';
HTML_PAGE +='<td>'+formatCurrency((fixFloat(outObject[key2][i].pQuantity)))/*.toFixed(2)*/+'</td>';
locPQuantity=parseFloat(locPQuantity)+parseFloat(outObject[key2][i].pQuantity);
GrpPQuantity=parseFloat(GrpPQuantity)+parseFloat(outObject[key2][i].pQuantity);
HTML_PAGE +='<td>'+formatCurrency((fixFloat(outObject[key2][i].pValue)))/*.toFixed(2)*/+'</td>';
locPvalue=parseFloat(locPvalue)+parseFloat(outObject[key2][i].pValue);
GrpPvalue=parseFloat(GrpPvalue)+parseFloat(outObject[key2][i].pValue);
HTML_PAGE +='<td>'+formatCurrency(roundFloat((fixFloat(outObject[key2][i].MRQ))))+'</td>';
locReptQty=parseFloat(locReptQty)+parseFloat(outObject[key2][i].MRQ);
GrpReptQty=parseFloat(GrpReptQty)+parseFloat(outObject[key2][i].MRQ);
HTML_PAGE +='<td>'+formatCurrency(roundFloat((fixFloat(outObject[key2][i].MRV))))+'</td>';
locReptValue=parseFloat(locReptValue)+parseFloat(outObject[key2][i].MRV);
GrpReptValue=parseFloat(GrpReptValue)+parseFloat(outObject[key2][i].MRV);
HTML_PAGE +='<td>'+formatCurrency(roundFloat((fixFloat(outObject[key2][i].MIQ))))+'</td>';
locIssueQty=parseFloat(locIssueQty)+parseFloat(outObject[key2][i].MIQ);
GrpIssueQty=parseFloat(GrpIssueQty)+parseFloat(outObject[key2][i].MIQ);
HTML_PAGE +='<td>'+formatCurrency(roundFloat((fixFloat(outObject[key2][i].MIV))))+'</td>';
locIssueValue=parseFloat(locIssueValue)+parseFloat(outObject[key2][i].MIV);
GrpIssueValue=parseFloat(GrpIssueValue)+parseFloat(outObject[key2][i].MIV);
HTML_PAGE +='<td>'+formatCurrency(roundFloat((fixFloat(outObject[key2][i].sQty))))/*.toFixed(2)*/+'</td>';
// locSQuantity
locSQuantity=parseFloat(locSQuantity)+parseFloat(outObject[key2][i].sQty);
GrpSQuantity=parseFloat(GrpSQuantity)+parseFloat(outObject[key2][i].sQty);
HTML_PAGE +='<td>'+formatCurrency(roundFloat((fixFloat(outObject[key2][i].sValue))))/*.toFixed(2)*/+'</td>';
// locSvalue
locSvalue=parseFloat(locSvalue)+parseFloat(outObject[key2][i].sValue);
GrpSvalue=parseFloat(GrpSvalue)+parseFloat(outObject[key2][i].sValue);
var BQuantity = parseFloat(outObject[key2][i].balanceForwardQty)+parseFloat(outObject[key2][i].pQuantity)+parseFloat(outObject[key2][i].MRQ)-parseFloat(outObject[key2][i].MIQ)-parseFloat(outObject[key2][i].sQty);
// locBalanceQty
locBalanceQty=parseFloat(locBalanceQty)+parseFloat(BQuantity);
GrpBalanceQty=parseFloat(GrpBalanceQty)+parseFloat(BQuantity);
var Bvalue = BQuantity*outObject[key2][i].unitAvgCost;
// locBalanceValue
locBalanceValue=parseFloat(locBalanceValue)+parseFloat(Bvalue);
GrpBalanceValue=parseFloat(GrpBalanceValue)+parseFloat(Bvalue);
// locCOS
locCOS=parseFloat(locCOS)+parseFloat(outObject[key2][i].COS);
GrpCOS=parseFloat(GrpCOS)+parseFloat(outObject[key2][i].COS);
// locGross
locGross=parseFloat(locGross)+parseFloat(outObject[key2][i].GrossMrgin);
GrpGross=parseFloat(GrpGross)+parseFloat(outObject[key2][i].GrossMrgin);
HTML_PAGE +='<td>'+formatCurrency(roundFloat(outObject[key2][i].COS))+'</td>';
HTML_PAGE +='<td>'+formatCurrency(roundFloat(outObject[key2][i].GrossMrgin))+'</td>';
HTML_PAGE +='<td>'+formatCurrency((fixFloat(outObject[key2][i].unitAvgCost)))/*.toFixed(2)*/+'</td>';
// locUnitAvgCost
locUnitAvgCost=parseFloat(locUnitAvgCost)+parseFloat(outObject[key2][i].unitAvgCost);
GrpUnitAvgCost=parseFloat(GrpUnitAvgCost)+parseFloat(outObject[key2][i].unitAvgCost);
HTML_PAGE +='<td>'+formatCurrency(roundFloat(BQuantity))+'</td>';
HTML_PAGE +='<td>'+formatCurrency(roundFloat(Bvalue))/*.toFixed(2)*/+'</td>';
totalBFQty=parseFloat(totalBFQty)+parseFloat(outObject[key2][i].balanceForwardQty);
totalBFValue=parseFloat(totalBFValue)+parseFloat(BFValue);
totalPQuantity=parseFloat(totalPQuantity)+parseFloat(outObject[key2][i].pQuantity);
totalPvalue=parseFloat(totalPvalue)+parseFloat(outObject[key2][i].pValue);
totalSQuantity = parseFloat(totalSQuantity)+parseFloat(outObject[key2][i].sQty);
totalSvalue=parseFloat(totalSvalue)+parseFloat(outObject[key2][i].sValue);
totalunitAvgCost=parseFloat(totalunitAvgCost)+parseFloat(outObject[key2][i].unitAvgCost);
totalBalanceQty=parseFloat(totalBalanceQty)+parseFloat(BQuantity);
totalBalanceValue=parseFloat(totalBalanceValue)+parseFloat(Bvalue);
totalIssueQty=parseFloat(totalIssueQty)+parseFloat(outObject[key2][i].MIQ);
totalIssueValue=parseFloat(totalIssueValue)+parseFloat(outObject[key2][i].MIV);
totalReptQty=parseFloat(totalReptQty)+parseFloat(outObject[key2][i].MRQ);
totalReptValue=parseFloat(totalReptValue)+parseFloat(outObject[key2][i].MRV);
totalCOS=parseFloat(totalCOS)+parseFloat(outObject[key2][i].COS);
totalGross=parseFloat(totalGross)+parseFloat(outObject[key2][i].GrossMrgin);
HTML_PAGE +='</tr>';
}
// to show Grp total
HTML_PAGE +='<tr class="bold-font" style="background-color: oldlace;"><td>Group Total</td><td></td><td>'+formatCurrency((fixFloat(GrpBFQty)))+'</td><td>'+formatCurrency((fixFloat(GrpBFValue)))/*.toFixed(2)*/+'</td><td>'+formatCurrency((fixFloat(GrpPQuantity)))/*.toFixed(2)*/+'</td><td>'+formatCurrency((fixFloat(GrpPvalue)))/*.toFixed(2)*/+'</td><td>'+formatCurrency((fixFloat(GrpReptQty)))+'</td><td>'+formatCurrency((fixFloat(GrpReptValue)))+'</td><td>'+formatCurrency((fixFloat(GrpIssueQty)))+'</td><td>'+formatCurrency((fixFloat(GrpIssueValue)))+'</td><td>'+(fixFloat(GrpSQuantity))/*.toFixed(2)*/+'</td><td>'+formatCurrency((fixFloat(GrpSvalue)))/*.toFixed(2)*/+'</td><td>'+formatCurrency((fixFloat(GrpCOS)))+'</td><td>'+formatCurrency((fixFloat(GrpGross)))+'</td><td>'+formatCurrency((fixFloat(GrpUnitAvgCost)))/*.toFixed(2)*/+'</td><td>'+formatCurrency((fixFloat(GrpBalanceQty)))+'</td><td>'+formatCurrency((fixFloat(GrpBalanceValue)))/*.toFixed(2)*/+'</td></tr>'
}
HTML_PAGE +='<tr><td colspan="17" style="background-color: white;"></td></tr>';
// to show loc total
HTML_PAGE +='<tr class="bold-font" style="background-color: oldlace;"><td>Total For Location </td><td></td><td>'+formatCurrency((fixFloat(locBFQty)))+'</td><td>'+formatCurrency((fixFloat(locBFValue)))/*.toFixed(2)*/+'</td><td>'+formatCurrency((fixFloat(locPQuantity)))/*.toFixed(2)*/+'</td><td>'+formatCurrency((fixFloat(locPvalue)))/*.toFixed(2)*/+'</td><td>'+formatCurrency((fixFloat(locReptQty)))+'</td><td>'+formatCurrency((fixFloat(locReptValue)))+'</td><td>'+formatCurrency((fixFloat(locIssueQty)))+'</td><td>'+formatCurrency((fixFloat(locIssueValue)))+'</td><td>'+formatCurrency((fixFloat(locSQuantity)))/*.toFixed(2)*/+'</td><td>'+formatCurrency((fixFloat(locSvalue)))/*.toFixed(2)*/+'</td><td>'+formatCurrency((fixFloat(locCOS)))+'</td><td>'+formatCurrency((fixFloat(locGross)))+'</td><td>'+formatCurrency((fixFloat(locUnitAvgCost)))/*.toFixed(2)*/+'</td><td>'+formatCurrency((fixFloat(locBalanceQty)))+'</td><td>'+formatCurrency((fixFloat(locBalanceValue)))/*.toFixed(2)*/+'</td></tr>'
HTML_PAGE +='<tr><td colspan="17" style="background-color: white;"></td></tr>';
}
// to show the total
HTML_PAGE +='</tr><tr class="bold-font" style="background-color: oldlace;"><td>Total</td><td></td><td>'+formatCurrency((fixFloat(totalBFQty)))+'</td><td>'+formatCurrency((fixFloat(totalBFValue)))/*.toFixed(2)*/+'</td><td>'+formatCurrency((fixFloat(totalPQuantity)))/*.toFixed(2)*/+'</td><td>'+formatCurrency((fixFloat(totalPvalue)))/*.toFixed(2)*/+'</td><td>'+formatCurrency((fixFloat(totalReptQty)))+'</td><td>'+formatCurrency((fixFloat(totalReptValue)))+'</td><td>'+formatCurrency((fixFloat(totalIssueQty)))+'</td><td>'+formatCurrency((fixFloat(totalIssueValue)))+'</td><td>'+formatCurrency((fixFloat(totalSQuantity)))/*.toFixed(2)*/+'</td><td>'+formatCurrency((fixFloat(totalSvalue)))/*.toFixed(2)*/+'</td><td>'+formatCurrency((fixFloat(totalCOS)))+'</td><td>'+formatCurrency((fixFloat(totalGross)))+'</td><td>'+formatCurrency((fixFloat(totalunitAvgCost)))/*.toFixed(2)*/+'</td><td>'+formatCurrency((fixFloat(totalBalanceQty)))+'</td><td>'+formatCurrency((fixFloat(totalBalanceValue)))/*.toFixed(2)*/+'</td></tr>'
HTML_PAGE +='<tr><td colspan="17" style="background-color: white;"></td></tr>';
HTML_PAGE +='</tbody></table></div></div></body></html>';
HTML_PAGE +='';
return HTML_PAGE;
}
}catch(e)
{
log.debug("Err@html content",e.message);
log.error("Err@html content",e.message);
return e.message;
}
}
/**
* Function to check the decimal system
*/
function formatCurrency(num) {
num = num.toString().replace(/\$|\,/g, '');
var dec = num.split(".")[1];
num = num.split(".")[0];
for (var i = 0; i < Math.floor((num.length - (1 + i)) / 3); i++)
num = num.substring(0, num.length - (4 * i + 3)) + '.' +
num.substring(num.length - (4 * i + 3));
if(dec)
return (((num) ? '' : '-') + num) + ","+ dec;
else
return (((num) ? '' : '-') + num)
}
/**
* ends
*/
function checkif(singleitem) {
if (singleitem == "" || singleitem == null || singleitem == undefined) {
return "0";
} else {
return singleitem;
}
}
function fixFloat(value, decimals) {
if (!decimals)
decimals = 2;
return roundFloat(parseFloat(value), parseInt(decimals)).toFixed(parseInt(decimals));
}
/*To round a float number */
function roundFloat(value, decimals) {
if (!decimals)
decimals = 2;
return Number(Math.round(parseFloat(value) + 'e' + parseInt(decimals)) + 'e-' + parseInt(decimals));
}
return {
onRequest: onRequest//,
};
});