# This script calculates anytime maximum demand, net consumption volume and approximate gross consumption volume, based on outputs from the base case vSPD run.

# The script uses a dataset 'reconciliation_allocations_for_spd_method_3yr_long' that has not been published because it is commercially sensitive.
# Therefore, you will not be able to run this code in the form shown below.
# However, so that you can reproduce the remainder of the analysis, the key outputs of this script have been provided in .csv form and can be loaded by running the commented-out code below.

USE tpm2;

CREATE TABLE amd AS
SELECT purchaser, MAX(mw) AS amd FROM (
SELECT record_date, tp, purchaser, SUM(net_demand*proportion) AS mw
FROM o_raw_node_ir AS a
INNER JOIN reconciliation_allocations_for_spd_method_3yr_long AS b
ON a.record_date=b.trading_date AND a.tp=b.trading_period AND a.node=b.poc
WHERE (
purchaser='Fonterra' OR
purchaser='Pacific Steel' OR
purchaser='Pacific Aluminium' OR
purchaser='Daiken MDF' OR
purchaser='NZ Steel' OR
purchaser='Winstones' OR
purchaser='Methanex' OR
purchaser='Kiwirail' OR
purchaser='CHH' OR
purchaser='PanPac' OR
purchaser='Rayonier' OR
purchaser='Norske Skog' OR
0) AND runtype='baseTPM2'
GROUP BY record_date, tp, purchaser
  ) AS c GROUP BY purchaser;

DROP TABLE IF EXISTS load_customer_volumes; # net
CREATE TABLE load_customer_volumes AS
SELECT purchaser AS customer, SUM(a.net_demand*b.proportion)/2000 AS gwh
FROM o_raw_node_ir AS a
INNER JOIN reconciliation_allocations_by_network_3yr_long AS b
ON a.record_date=b.trading_date AND a.tp=b.trading_period AND a.node=b.poc
WHERE runtype='baseTPM2' AND net_demand>0
GROUP BY purchaser;

# As you do not have the 'reconciliation_allocations_by_network_3yr_long' table and hence cannot run the code above, 
#  use this commented-out code instead to have the same effect:
#
# DROP TABLE IF EXISTS amd;
# CREATE TABLE amd (
#  `purchaser` varchar(19) CHARACTER SET utf8 NOT NULL DEFAULT '',
#  `amd` double DEFAULT NULL
# ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
# insert into amd(`purchaser`,`amd`) values ('CHH',87.24200057983398),('Daiken MDF',11.885000228881836),('Fonterra',9.85099983215332),('Kiwirail',17.674999952316284),('Methanex',9.897000312805176),('Norske Skog',120.97899627685547),('NZ Steel',171.52545348057524),('Pacific Aluminium',634.6259765625),('Pacific Steel',57.650001525878906),('PanPac',87.50998081186295),('Rayonier',9.619000434875488),('Winstones',43.64500045776367);
#
# DROP TABLE IF EXISTS load_customer_volumes;
# CREATE TABLE load_customer_volumes (
#  `customer` varchar(22) CHARACTER SET utf8 NOT NULL DEFAULT '',
#  `gwh` double DEFAULT NULL
# ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
# insert into load_customer_volumes(`customer`,`gwh`) values ('Alpine Energy',2298.118383634357),('Aurora Energy',3856.104575767714),('Buller Electricity',349.7193115210077),('CHH',1901.204926984596),('Contact',87.61206458599347),('Counties Power',1457.4456571515734),('Daiken MDF',221.6023420087397),('Eastland Network',926.6457506735513),('Electra',1415.0736488015762),('Electricity Ashburton',1705.7925938476621),('Fonterra',133.51701298670832),('Genesis',4.675770601145268),('Horizon',1454.4194927305691),('Kiwirail',118.43500251175166),('Mainpower',1587.1526400995372),('Marlborough Lines',1189.0027327489852),('Meridian',24.08762819209686),('Methanex',138.5588225095272),('MRP',20.731946043276228),('Network Tasman',2473.1378922257945),('Network Waitaki',744.0308249400466),('Norske Skog',1550.8601043615445),('Northpower',3157.0514814827443),('NZ Steel',3081.3473189053852),('Orion',9986.762831205368),('Other',326.31215715495085),('Pacific Aluminium',15263.701820709228),('Pacific Steel',606.9763249495849),('PanPac',1556.4648230461146),('Powerco',12619.983763397706),('PowerNet',4060.8276980604624),('Rayonier',171.59925173807088),('Scanpower',276.052474974066),('The Lines Company',686.1713140565928),('Todd',1.5673215017952606),('Top Energy',528.0298554724823),('TrustPower',31.066108507922095),('Unison',5544.50208769606),('Vector',26860.962692303503),('Waipa Power',1191.067602955818),('WEL',3649.080159044605),('Wellington Electricity',8080.11776673157),('Westpower',708.3987055145905),('Winstones',834.9215047931327);

CREATE TABLE load_customer_net_to_gross_approximate_conversion AS
SELECT customer, 1.00000 AS net_to_gross
FROM load_customer_volumes;
UPDATE load_customer_net_to_gross_approximate_conversion SET net_to_gross=(1/0.92) WHERE customer='Aurora Energy';
UPDATE load_customer_net_to_gross_approximate_conversion SET net_to_gross=(1/0.92) WHERE customer='Horizon';
UPDATE load_customer_net_to_gross_approximate_conversion SET net_to_gross=(1/0.92) WHERE customer='Powerco';
UPDATE load_customer_net_to_gross_approximate_conversion SET net_to_gross=(1/0.90) WHERE customer='PowerNet';
UPDATE load_customer_net_to_gross_approximate_conversion SET net_to_gross=(1/0.75) WHERE customer='The Lines Company';
UPDATE load_customer_net_to_gross_approximate_conversion SET net_to_gross=(1/0.50) WHERE customer='Top Energy';
UPDATE load_customer_net_to_gross_approximate_conversion SET net_to_gross=(1/0.92) WHERE customer='WEL';
UPDATE load_customer_net_to_gross_approximate_conversion SET net_to_gross=(1/0.80) WHERE customer='Westpower';

