var appStartTime = performance.now();

writeln;
writeln( "------------------------------------------------------------------------------------------");
writeln( "NetSuite Inventory to Excel via SuiteTalk" );
writeln( "------------------------------------------------------------------------------------------");
writeln;

// Require SheetJS ( https://sheetjs.com )
require( "shim.min.js" );
var XLSX = require( "xlsx.full.min.js" );

// Require Moment ( https://momentjs.com )
var moment = require( 'moment.min.js' );

// Load NetSuite Suitetalk credentials.
var ns = require( "netsuite-creds-ironforge-suitetalk.js" );

// SuiteQL query to retrieve current inventory levels for all active inventory items, by location,
// including item information, base and online prices.
var query = <<<EOQ
	SELECT
		Item.ID,				
		BUILTIN.DF( AggregateItemLocation.Location ) AS Location,
		Item.ItemID,
		Item.Description,
		BasePrice.Price AS BasePrice,
		OnlinePrice.Price AS OnlinePrice,
		AggregateItemLocation.QuantityAvailable				
	FROM 
		Item
		INNER JOIN AggregateItemLocation ON
			( AggregateItemLocation.Item = Item.ID )
		INNER JOIN ItemPrice AS BasePrice ON
			( BasePrice.Item = Item.ID ) 
			AND ( BasePrice.PriceLevelName = 'Base Price' )
		INNER JOIN ItemPrice AS OnlinePrice ON
			( OnlinePrice.Item = Item.ID ) 
			AND ( OnlinePrice.PriceLevelName = 'Online Price' )
	WHERE
		( Item.ItemType = 'InvtPart' )
		AND ( Item.IsInactive = 'F' )
	ORDER BY
		Item.ID,
		BUILTIN.DF( AggregateItemLocation.Location )
EOQ;

// Define the request payload.
var payload = {
  q: query
}

// Initialize the array of Excel rows.
var rows = new Array();

// Add header rows to the array.
rows.push( [ "Stormwind Logistics Corp." ] );
rows.push( [ "Inventory as of " + moment().format( "MMMM Do YYYY, h:mm a" ) ] );
rows.push( [] );
rows.push( [ "ID", "Location", "Item", "Base Price", "Online Price", "Qty Available" ] );

// Initialize the offset to use when sending the first request to SuiteTalk.
var offset = 0;

// Repeat until no additional records are available...
do {

	// Set the URL to use when sending the request.
	var URL = ns.suiteTalkURL + "/services/rest/query/v1/suiteql?limit=1000&offset=" + ( offset );
	writeln( "Retrieving data..." );	
	writeln( "• URL: " + URL );
	
	var requestStartTime = performance.now();

	// Specify the settings to use when sending the request.
	var nsConnectRequest = {
		accountNumber: ns.accountNumber,
		consumerKey: ns.consumerKey,
		consumerSecret: ns.consumerSecret,
		tokenID: ns.tokenID,	
		tokenSecret: ns.tokenSecret,
		method: "POST",
		url: URL,
		connectionTimeout: 10,
		timeout: 10,
		payload: JSON.stringify( payload )
	}

	// Send the request, and get the response.
	var nsConnectResponse = nsConnect( JSON.stringify( nsConnectRequest, null, 5 ) );

	var requestElapsedTime = ( performance.now() - requestStartTime ) / 1000;
	writeln( "• Retrieved " + nsConnectResponse.count + " records in " + requestElapsedTime.toFixed(2) + " seconds." );

	// Parse the JSON-encoded response.
	nsConnectResponse = JSON.parse( nsConnectResponse );

	// For each record returned...
	for (var i = 0; i < nsConnectResponse.items.length; i++) {

		// Get the next item.
		var item = nsConnectResponse.items[i];
			
		// Add a row for the item.
		rows.push( [ item.id, item.location, item.itemid, item.baseprice, item.onlineprice, item.quantityavailable ] );
	
	}
	
	// Set the offset to use for the next request.
	offset += 1000;

} while ( nsConnectResponse.hasMore == true );


// Create a workbook.
var workbook = XLSX.utils.book_new();

// Add a sheet to the workbook.
workbook.SheetNames.push("Inventory");

// Create a worksheet, and add the rows.
var worksheet = XLSX.utils.aoa_to_sheet(rows);

// Assign the worksheet.
workbook.Sheets["Inventory"] = worksheet;	

// Generate a Base64-encoded Excel file, and get its contents.
var fileContent = XLSX.write( workbook, { bookType: "xlsx", type: "base64" } );

// Generate the file name.
var fileName = "./inventory-" + moment().format( "YYYYMMDD-HHmm" ) + ".xlsx"

// Save the file.
// The "true" parameter indicates that the content is Base64-encoded,
// and should be decoded by the function prior to writing it.	
var writeStatus = writeFile( fileName, fileContent, true);

// Done!
var appElapsedTime = ( performance.now() - appStartTime ) / 1000;
writeln( "Process completed in " + appElapsedTime.toFixed(2) + " seconds." );