Infoplus Support

Use Case: To know, at the end of a month, the daily average cubic feet of storage space used for each item.  

This script is a Report type script, which is meant to be used with a report from the Items table.  The report must contain the following columns:  SKU, LOB, Length, Width, Height.  The Smart Filter that is used to create the report will determine which Items are included in the report. 

The script will, for each item in the report, lookup all Inventory Snapshot records for the item for the previous month, summing all quantities together, then dividing by the number of days in the month, to get the average daily quantity.  Then it will multiply that number by the item's dimensions, and add the computed column to the report.  

///////////////////////////////////////////////////////////////
// set the first/min date to the 1st of the previous month,  //
// by subtracting one from today's month, then set date to 1 //
///////////////////////////////////////////////////////////////
var minDate = new Date();
minDate.setMonth(minDate.getMonth() - 1);
minDate.setDate(1);

////////////////////////////////////////////////////////////////////////
// set the last/max date to the last day of the previous month,       //
// by just making a new date (today), and setting to the 0th of this  //
// month (which gets auto-adjusted to the last day of previous month) //
////////////////////////////////////////////////////////////////////////
var maxDate = new Date();
maxDate.setDate(0);

///////////////////////////////////////////////////////////////////////////////////////////////////////
// the number of days we'll be running for is the date field from the maxDate (ie, 28, 29, 30 or 31) //
///////////////////////////////////////////////////////////////////////////////////////////////////////
var totalDays = maxDate.getDate();

///////////////////////////////////
// reformat dates for api filter //
///////////////////////////////////
minDate = (1900+minDate.getYear()) + "-" + (1+minDate.getMonth()) + "-" + (minDate.getDate()) + "T00:00:00.000Z";
maxDate = (1900+maxDate.getYear()) + "-" + (1+maxDate.getMonth()) + "-" + (maxDate.getDate()) + "T00:00:00.000Z";

utils.log("Running for date range [" + minDate + "] through [" + maxDate + "] (" + totalDays + " days)");



///////////////////////////////////////////////////////////////////////////
// object to store item identifiers (lobName & sku) mapped to quantities //
// and functions to add to these values and fetch them                   //
///////////////////////////////////////////////////////////////////////////
var quantityMap = {};

function addQuantity(lob, sku, quantity)
{
    var key = lob + "\n" + sku;
    if(! quantityMap[key])
    {
        quantityMap[key] = 0;
    }
    
    quantityMap[key] = Number(quantityMap[key]) + Number(quantity);
}

function getTotalQuantity(lob, sku)
{
    var key = lob + "\n" + sku;
    return(quantityMap[key]);
}



///////////////////////////////////////////////
// object to store lob names mapped to ids   //
// and function to populate and use this map //
///////////////////////////////////////////////
var lobIdMap = {};
function getLobId(lobName)
{
    if(!lobIdMap[lobName])
    {
        var lobs = infoplusApi.search("LineOfBusiness", lobName, null, null, null);
        if(lobs && lobs.size())
        {
            var lob = lobs.get(0);
            lobIdMap[lobName] = lob.id;
        }
    }
    
    return(lobIdMap[lobName]);
}



////////////////////////////////////////////////////////////
// look up inventorySnapshots for all items in the report //
////////////////////////////////////////////////////////////
var PAGE_SIZE = 250;
for(var i=0; i<report.originalRows.size(); i++)
{
    var reportRow = report.originalRows.get(i);
    var sku = reportRow.get("SKU");
    var lobName = reportRow.get("LOB");
    
    if(! sku)
    {
        throw("Error:  SKU column must be present in report");
    }   
    
    if(! lobName)
    {
        throw("Error:  LOB column must be present in report");
    }

    var lobId = getLobId(lobName);
    if(! lobId)
    {
        throw("Error:  LOB Id could not be found for LOB name: " + lobName);
    }
    
    ///////////////////////////////////////////////
    // seed the quantity map with 0 for the item //
    ///////////////////////////////////////////////
    addQuantity(lobName, sku, 0);
    utils.log("\nProcessing SKU: " + sku);

    ////////////////////////////////////////////////////////////////////////////////////////////////////
    // lookup all InventorySnapshot rows for this item in the month (rows a per (sku, location, date) //
    // Just sum them all together per sku (not caring how many there are by date),                    //
    // since we'll just be dividing the item's sum by the number of days in the end.                  //
    ////////////////////////////////////////////////////////////////////////////////////////////////////
    var filter = "lobId eq " + lobId + " and sku eq '" + sku + "' and snapshotDate between ('" + minDate + "','" + maxDate + "')";
    for(var pageNo = 1; pageNo < 1000; pageNo++)
    {
        utils.log("Searching for inventory snapshots, page: " + pageNo + ", filter: " + filter);
        var inventorySnapshots = infoplusApi.search("InventorySnapshot", filter, pageNo, PAGE_SIZE, 'id');
        if(inventorySnapshots && inventorySnapshots.size())
        {
            utils.log("   Found [" + inventorySnapshots.size() + "] results.");
            for(var j=0; j<inventorySnapshots.size(); j++)
            {
                var inventorySnapshot = inventorySnapshots.get(j);
                addQuantity(lobName, sku, inventorySnapshot.quantity);
            }
            
            if(inventorySnapshots.size() < PAGE_SIZE)
            {
                utils.log("   Breaking loop - less than a full page of [" + PAGE_SIZE + "] results was found.");
                break;
            }
        }
        else
        {
            utils.log("   Breaking loop - no results were found.");
            break;
        }
    }
}



////////////////////////////////////
// add the new data to the report //
////////////////////////////////////
report.addColumn("Average Daily On Hand");
report.addColumn("Average Daily Cubic Feet");
for(var i=0; i<report.originalRows.size(); i++)
{
    var reportRow = report.originalRows.get(i);
    var sku = reportRow.get("SKU");
    var lobName = reportRow.get("LOB");
    var length = reportRow.get("Length (in)")
    var width = reportRow.get("Width (in)")
    var height = reportRow.get("Height (in)")
    
    var totalMonthlyOnHand = getTotalQuantity(lobName, sku);    
    var averageDailyOnHand = totalMonthlyOnHand / totalDays;
    reportRow.put("Average Daily On Hand", averageDailyOnHand.toFixed(2));

    if(length && width && height)
    {
        var averageCubicFeet = (length * width * height * averageDailyOnHand) / (12*12*12);
        if(averageCubicFeet)
        {
            reportRow.put("Average Daily Cubic Feet", averageCubicFeet.toFixed(2));   
        }
    }
    report.addRow(reportRow);
}