Example 3 - Stacks, Consolidation & References

 1//load the data
 2const data1 = await loadJSON('site1.json'),
 3      data2 = await loadJSON('site2.json'),
 4      data3 = await loadJSON('site3.json'),
 5      data4 = await loadJSON('steel_suppliers.json'); 
 6
 7//
 8//create and define the stack; which acts as a table factory and allows us to use consolidation facets
 9//        
10let stack = defineStack('item', 'steel_m2', 'unit_offer', 'quantity'),
11    site_1 = stack.createTable('site1'),
12    site_2 = stack.createTable('site2'),
13    site_3 = stack.createTable('site3');
14
15//set the facets used by the stack
16stack.addFacets(raw_materials, lineitems, gross_margin);
17
18//set a consolidation facet
19stack.consolidate(consolidation);            
20
21//
22//populate the tables
23//
24site_1.load(data1);
25site_2.load(data2);
26site_3.load(data3);
27
28//Create a table for the steel suppliers
29let suppliers = createTable(['supplier', 'steel_m2', 'transport_m2'], steel_suppliers);
30
31//load and calculate
32suppliers.load(data4);
33suppliers.calculate();
34
35//Set the average_steel_cost_m2 reference required by the raw_materials facets to use
36//the average_cost_per_m2 aggregate from the suppliers table
37stack.setDefaultReferences( function(uses) {
38
39    this.average_steel_cost_m2 = uses(suppliers).average_cost_per_m2;
40});  

1"use strict";
2/**
3Calculate line item cost from raw materials
4*/
5export default function(table, row, refs) {
6
7    row.unit_cost = () => row.steel_m2 * (refs.manufacturing_cost_per_m2 + refs.average_steel_cost_m2);
8};

 1"use strict";
 2/**
 3A simple invoice/quotation calculator
 4*/
 5export default function(table, row) {
 6
 7    row.line_cost     = () => row.quantity * row.unit_cost;    
 8    row.line_offer    = () => row.quantity * row.unit_offer;
 9
10    table.total_quantity = () => SUM(this.column('quantity'));
11    table.total_cost     = () => SUM(this.column('line_cost'));
12    table.total_offer    = () => SUM(this.column('line_offer'));
13    table.profit         = () => table.total_offer - table.total_cost;
14};

 1"use strict";
 2/**
 3 * Calculate the gross margin for line items and for the whole table
 4 * 
 5 * @param {*} table     Table query object
 6 * @param {*} row       The table row
 7 * @param {*} refs      External references
 8 */
 9export default function(table, row, refs) {
10
11    /**
12     * The row Gross Margin
13     * @returns number
14     */    
15    row.gross_margin  = () => 1 - (row.unit_cost / row.unit_offer);
16
17    /**
18     * The total Gross Margin
19     * @returns number
20     */    
21    table.gross_margin = () => 1 - (table.total_cost / table.total_offer);
22
23    /**
24     * Returns true if the total Gross Margin is below the system threshold
25     * @returns boolean
26     */    
27    table.low_margin_warning = () => table.gross_margin <= refs.low_margin_threshold / 100;
28};

 1"use strict";
 2/**
 3 * Consolidate table stack line items
 4 * 
 5 * @param {*} columns   Table columns query object
 6 * @param {*} refs      External references
 7 */
 8export default function(columns, refs) {
 9
10    /**
11     * The total cost of all the line items in the table stack
12     * @returns number
13     */
14    this.total_cost  = () => SUM( ...columns('line_cost') );
15
16    /**
17     * The total offer price of all the line items in the table stack
18     * @returns number
19     */
20    this.total_offer = () => SUM( ...columns('line_offer') );
21
22    /**
23     * Total profit
24     * @returns number
25     */
26    this.profit      = () => this.total_offer - this.total_cost;
27
28    /**
29     * The total Gross Margin
30     * @returns number
31     */
32    this.gross_margin = () => 1 - (this.total_cost / this.total_offer);
33
34    /**
35     * Returns true if the total Gross Margin is below the system threshold
36     * @returns boolean
37     */
38    this.low_margin_warning = () => this.gross_margin <= refs.low_margin_threshold / 100;            
39};

1"use strict";
2/**
3Calculate steel supply costs
4*/
5export default function(table, row, refs) {
6
7    row.cost_per_m2 = () => row.steel_m2 + row.transport_m2;
8    table.average_cost_per_m2 = () => AVERAGE(this.column('cost_per_m2'));
9};