Created
October 28, 2023 22:53
-
-
Save TerryE/13c6f3a7db7929854453fd0398324061 to your computer and use it in GitHub Desktop.
NodeRED Function to Download OVO Smart-meter Readings
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// This function uses a companion HTTP Request node to load data from the OVO API | |
// service, with the HTTP response looped back into this node's input. | |
// | |
// - Hence an initiating request will be cycled through this node many times | |
//. | |
// - The state and context is maintained in msg using the msg.state object. | |
// | |
// - msg.state.id used to sequence the cycles. | |
// | |
// - The initiating cycle has a null state with msg.user and msg.pwd containing | |
// the OVE creds, and msg.body the dates for the last download. | |
// | |
// - Each of the subsequent cycles processes the results of the previous OVO | |
// request optionally creating a SQL insert to load the data in the DB and a | |
// URL request for the OVO API service to load the next data chunk. | |
const MYOVO = 'my.ovoenergy.com'; | |
const PAYMAPI = 'smartpaymapi.ovoenergy.com'; | |
let body = msg.payload; | |
Object.assign(msg, { // Override various msg properties | |
headers: { | |
'User-Agent': 'Mozilla/5.0', | |
'Accept': 'application/json,text/html', | |
'Accept-Language': 'en-GB,en;q=0.5', | |
'DNT': '1', | |
'Host': PAYMAPI, | |
'Connection': 'keep-alive' | |
}, | |
method: 'GET', | |
cookies: (msg.responseCookies || msg.cookies || {}), | |
payload: null, | |
responseCookies: null, | |
}); | |
if (!msg.state) { msg.state = { id: -1 }; } | |
const state = msg.state; | |
state.id++; | |
const setDataURL = s => { | |
let start = new Date(s.start); | |
start.setDate(start.getDate() + 1); | |
s.start = start.toISOString().substring(0, 10); | |
return `https://${PAYMAPI}/usage/api/half-hourly/${s.account}?date=${s.start}`; | |
}; | |
const extDate = d => (new Date(d)).toISOString().substring(0, 10); // extract YYYY-MM-DD | |
const nextDay = d => { | |
let start = new Date(d); start.setDate(start.getDate() + 1); | |
return start.toISOString().substring(0, 10); | |
}; | |
const processingStage = [ | |
() => { // On first page move stuff to state | |
state.user = msg.user; | |
state.pwd = msg.pwd; | |
state.dailyStart = extDate(body[0][0].date); | |
state.meterStart = extDate(body[1][0].date); | |
msg.url = '/login'; | |
msg.headers.Host = MYOVO; | |
delete msg.user; delete msg.pwd; delete msg.topic; | |
return [null, msg]; | |
}, | |
() => { // Now do OVO loging to establish credentials | |
msg.method = 'POST'; | |
msg.payload = { username: state.user, password: state.pwd, rememberMe: true }; | |
msg.url = '/api/v2/auth/login'; | |
msg.headers.Host = MYOVO; | |
return [null, msg]; | |
}, | |
() => { // Setup API session | |
msg.url = '/first-login/api/bootstrap/v2/'; | |
return [null, msg]; | |
}, | |
() => { // Lookup account details | |
state.account = body.selectedAccountId; | |
state.customer = body.customerId; | |
msg.url = '/orex/api/plans/' + state.account; | |
return [null, msg]; | |
}, | |
() => { // Cache account details and request meter readings | |
const contract = body.electricity; | |
state.mpxn = contract.mpxn; | |
state.msn = contract.msn; | |
state.price = { standingCharge: contract.standingCharge.amount }; | |
for (let r of contract.unitRates) { state.price[r.name] = r.unitRate.amount; } | |
msg.url = '/rlc/rac-public-api/api/v5/supplypoints/electricity/' + | |
`${state.mpxn}/meters/${state.msn}/readings?from=${state.dailyStart}`; | |
return [null, msg]; | |
}, | |
() => { // save daily readings in MySQL and start daily ½hr reading poll | |
const v = body.map(e => ({ | |
dts: e.readingDateTime.substring(0, 10), | |
peak: Number(e.tiers[0].meterRegisterReading), | |
offpeak: Number(e.tiers[1].meterRegisterReading) | |
})).filter(e => e.dts > state.dailyStart); | |
let BV = []; | |
for (let i = v.length - 2; i >= 0; i--) { | |
const P = state.price; | |
const [e, el] = [v[i], v[i + 1]]; | |
const [p, op] = [e.peak - el.peak, e.offpeak - el.offpeak]; | |
const Rs = P.standingCharge; | |
const [Rp, Rop] = [P.day, P.night]; | |
const cost = ((Rp * p) + (Rop * op)).toFixed(3); | |
BV.push([el.dts, (p + op).toFixed(3), cost, p.toFixed(3), op.toFixed(3), Rs, Rp, Rop]); | |
} | |
const updateExtTempSQL = ` | |
UPDATE daily_readings, meter_readings | |
(SELECT date(dts) AS Tdate, | |
substring(substring_index(value,',',3), | |
length(substring_index(value,',',2))+2) AS temp | |
FROM eventlog | |
WHERE event='external-temp' AND | |
dts > '${state.dailyStart}') AS T | |
SET extTemp = T.temp | |
WHERE daily_readings.dts = T.Tdate | |
AND daily_readings.extTemp IS NULL;`; | |
state.start = nextDay(state.meterStart); | |
msg.url = `/usage/api/half-hourly/${state.account}?date=${state.start}`; | |
return [[{ | |
topic: 'INSERT INTO daily_readings(dts,Duse,cost,Puse,Ouse,Rstand,Rpeak,Roffp) VALUES ?;', | |
payload: [BV] | |
}, | |
{ topic: updateExtTempSQL }], | |
msg]; | |
}, | |
() => { // save half-hourly readings in MySQL | |
if (!body.electricity) return [null, null]; | |
let BV = []; | |
for (const h of body.electricity.data) { | |
const t = h.interval.start; | |
BV.push([t.substring(0, 10) + ' ' + t.substring(11, 16), h.consumption]); | |
} | |
state.start = nextDay(state.start); | |
state.id--; | |
msg.url = `/usage/api/half-hourly/${state.account}?date=${state.start}`; | |
if (!body.electricity.next) { msg = null; } // turn off next query if next is false | |
return [{ | |
topic: 'INSERT INTO meter_readings(dts,`use`) VALUES ?;', | |
payload: [BV] | |
}, msg]; | |
} | |
]; | |
try { | |
try {body = JSON.parse(body);} catch (e) {} // nearly all bodies are JSON | |
const [SQLmsg, URLmsg] = processingStage[state.id](); | |
if (URLmsg) { | |
URLmsg.url = `https://${URLmsg.headers.Host}` + URLmsg.url; | |
} | |
return [SQLmsg, URLmsg]; | |
} catch (e) { // after last batch add ext temp field for new daily_readings rows | |
node.warn(e.stack); return null; | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment