Here is the flow:
[{"id":"7e24edac.5a24d4","type":"inject","z":"2b6c3ed9.22a7ea","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":116,"y":2865,"wires":[["2148b319.453f6c"]]},{"id":"2148b319.453f6c","type":"e-mail in","z":"2b6c3ed9.22a7ea","name":"Get Unread Emails","protocol":"IMAP","server":"imap.gmail.com","useSSL":true,"autotls":"never","port":"993","box":"Inbox","disposition":"Read","criteria":"UNSEEN","repeat":"300","fetch":"trigger","inputs":1,"x":160.00003051757812,"y":2936.3333740234375,"wires":[["952f8ba2.699798","b6a64831.19d9c"]]},{"id":"b6a64831.19d9c","type":"switch","z":"2b6c3ed9.22a7ea","name":"Only if meter readings","property":"topic","propertyType":"msg","rules":[{"t":"cont","v":"Fwd: Smart Meter Texas – Subscription Report","vt":"str"}],"checkall":"true","repair":false,"outputs":1,"x":393.9999694824219,"y":2935.666748046875,"wires":[["16247b97.702d4c","ce16063c.0be738"]]},{"id":"ce16063c.0be738","type":"change","z":"2b6c3ed9.22a7ea","name":"Get attachment","rules":[{"t":"delete","p":"payload","pt":"msg"},{"t":"set","p":"payload","pt":"msg","to":"attachments[0].content","tot":"msg"},{"t":"delete","p":"header","pt":"msg"},{"t":"delete","p":"from","pt":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":640.0000076293945,"y":2937.666748046875,"wires":[["e60c9dba.dc8c98"]]},{"id":"e60c9dba.dc8c98","type":"function","z":"2b6c3ed9.22a7ea","name":"Extract CSV","func":"msg.payload=msg.attachments[0].content.toString('latin1')\n\ndelete msg.attachments\ndelete msg.html\n\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":819.0000076293945,"y":2937.666748046875,"wires":[["a04e2a60.ea0078","683db5a2.969ac4"]]},{"id":"683db5a2.969ac4","type":"csv","z":"2b6c3ed9.22a7ea","name":"Parse CSV","sep":",","hdrin":true,"hdrout":"none","multi":"mult","ret":"\\n","temp":"","skip":"0","strings":true,"include_empty_strings":"","include_null_values":"","x":1003.0000305175781,"y":2936.666748046875,"wires":[["246f6c0b.801184","302b144e.8bec24"]]},{"id":"302b144e.8bec24","type":"function","z":"2b6c3ed9.22a7ea","name":"Calc Daily Totals","func":"const data = msg.payload;\nconst dataLength = data.length;\n\nlet date = new Date(data[0].USAGE_DATE)\nlet consumption = 0\nlet surplus = 0\n\nfor (var i = 0; i < dataLength; i++) {\n if (data[i].CONSUMPTION_SURPLUSGENERATION == 'Consumption') {\n consumption = consumption + Number(data[i].USAGE_KWH)\n } else {\n surplus = surplus + Number(data[i].USAGE_KWH)\n }\n}\n\ndelete msg.payload\ndelete msg.columns\n\nmsg.date=date.toISOString().split(\"T\")[0]\nmsg.consumption = +(consumption.toFixed(3))\nmsg.surplus = +(surplus.toFixed(3))\nmsg.net = msg.consumption - msg.surplus\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":180.00003051757812,"y":3040.666748046875,"wires":[["9500e402.e923d","3e2886a5.49dbda"]]},{"id":"3e2886a5.49dbda","type":"function","z":"2b6c3ed9.22a7ea","name":"SQL Insert Query","func":"\n\nlet sqlString = 'INSERT INTO meter_reading (reading_date,consumption,surplus,net) VALUE ('\n\nconst quote = \"'\"\nconst comma = \",\"\n\n\nsqlString +=\"STR_TO_DATE(\"+quote+msg.date+quote+\",'%Y-%m-%d')\"+comma\nsqlString +=msg.consumption+comma\nsqlString +=msg.surplus+comma\nsqlString +=msg.net+\")\"\n\n\nmsg.topic = sqlString\n\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":407.0002136230469,"y":3039.666748046875,"wires":[["d5e64711.3718f","fa64f025.7cb4"]]}]
and this is what the flow looks like:
A couple of things to note:
-
I set up a Google account to use for the email subscriptions from smartmetertexas.com and enabled app specific password for it. This way, it will should not be impacted by Google turning of "less secure" method for accessing email.
-
The email node is configured to look for only unread mails ("unseen") and then mark them as "read" so it does not keep sending the same stuff again.
-
The data is an attachment to the email (CSV format) with meter readings every 15 minutes. I sum up the data for the day in a function node before storing it in the database. The 15 minute interval format is the only format that includes data on consumption (used from the grid) and surplus (supplied to the grid).
-
In the flow, I have removed the last node (mySQL node), but you can save the data to any database. The SQL to create the table is
//Meter Readings table
CREATE TABLE
meter_reading
(
id INT NOT NULL AUTO_INCREMENT,
reading_date DATETIME,
consumption DECIMAL(10,3),
surplus DECIMAL(10,3),
net DECIMAL(10,3),
created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
);
Hope this helps. Feel free to ping me if you have any questions.
