Author's posts
Wemos D1 mini v2
De inverter blijkt niet meer nodig. Nu is alleen een weerstand nodig voor spanning op de RX poort. Inverter functie zit in de Arduino software. Wat niet werkt is de Wemos aansturen met de spanning van pin 1 en pin 6 van de slimme meter. Theoretisch zou die voldoende vermogen moeten hebben, maar hij komt niet verder dan een knipperende blauwe led.
Slimme meter telegram
/XMX5LGBBFFB231170430 1-3:0.2.8(40) // Version information for P1 output 0-0:1.0.0(150531205441S) // Date/time 0-0:96.1.1(4530303035303031353735373435333134) // Equipment identifier 1-0:1.8.1(000992.992*kWh) // Elektra verbruik laag tarief 1-0:2.8.1(000348.890*kWh) // Elektra opbrengst laag tarief 1-0:1.8.2(000560.157*kWh) // Elektra verbruik hoog tarief 1-0:2.8.2(000859.885*kWh) // Elektra opbrengst hoog tarief 0-0:96.14.0(0001) // Tarief indicator 1-0:1.7.0(00.424*kW) // Huidig verbruik 1-0:2.7.0(00.000*kW) // Huidige teruglevering 0-0:17.0.0(999.9*kW) // Max power per fase 0-0:96.3.10(1) // Switch position 0-0:96.7.21(00003) // Number of power failures in any phase 0-0:96.7.9(00000) // Number of long power failures in any phase 1-0:99.97.0(0)(0-0:96.7.19) // Power Failure Event Log (long power failures) 1-0:32.32.0(00000) // Number of voltage sags in phase L1 1-0:52.32.0(00000) // Number of voltage sags in phase L2 1-0:72.32.0(00000) // Number of voltage sags in phase L3 1-0:32.36.0(00000) // Number of voltage swells in phase L1 1-0:52.36.0(00000) // Number of voltage swells in phase L2 1-0:72.36.0(00000) // Number of voltage swells in phase L3 0-0:96.13.1() // Numeric message 0-0:96.13.0() // Text message (1024 char) 1-0:31.7.0(001*A) // Instantaneous current L1 in A resolution 1-0:51.7.0(000*A) // Instantaneous current L2 in A resolution 1-0:71.7.0(001*A) // Instantaneous current L3 in A resolution 1-0:21.7.0(00.214*kW) // Instantaneous active power L1 (+P) in W resolution 1-0:41.7.0(00.000*kW) // Instantaneous active power L2 (+P) in W resolution 1-0:61.7.0(00.208*kW) // Instantaneous active power L3 (+P) in W resolution 1-0:22.7.0(00.000*kW) // Instantaneous active power L1 (-P) in W resolution 1-0:42.7.0(00.000*kW) // Instantaneous active power L2 (-P) in W resolution 1-0:62.7.0(00.000*kW) // Instantaneous active power L3 (-P) in W resolution 0-1:24.1.0(003) // Device type 0-1:96.1.0(4730303032333430313638393836383134) // Equipment identifier (Gas) 0-1:24.2.1(150531200000S)(00811.923*m3) // Gasmeterstand 0-1:24.4.0(1) // Switch position !5F31
Scripts voor uitlezen slimme meter
Hieronder staan de scripts die ik gebruikt heb om de slimme meter uit te lezen en de gegevens toe te voegen aan de SBFspot database in MySQL.
De MySQL database is als volgt aangemaakt:
USE SBFspot; CREATE TABLE 'SlimmeMeter' ( 'id' int(11) NOT NULL AUTO_INCREMENT, 'time' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 'lt_verbruik' int(11) NOT NULL, 'ht_verbruik' int(11) NOT NULL, 'lt_opbrengst' int(11) NOT NULL, 'ht_opbrengst' int(11) NOT NULL, 'huidig_verbruik' int(4) NOT NULL, 'gas' int(11) NOT NULL, 'sp_opbrengst' int(11) DEFAULT NULL, 'sp_power' int(11) DEFAULT NULL, PRIMARY KEY ('id') ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1; CREATE TABLE 'SlimmeMeterFouten' LIKE 'SlimmeMeter'; CREATE TABLE 'SlimmeMeterWeek' LIKE 'SlimmeMeter'; CREATE TABLE 'SlimmeMeterMaand' LIKE 'SlimmeMeter';
Het Arduino script:
/* Arduino 'slimme meter' P1-port reader. This sketch reads data from a Dutch smart meter that is equipped with a P1-port. Connect 'RTS' from meter to Arduino pin 5 Connect 'GND' from meter to Arduino GND Connect 'RxD' from meter to Arduino pin 0 (RX) Baudrate 115200, 8N1 BS548 transistor, 10k and 2k2 resistor are needed to make data readable if meter spits out inverted data */ #include <Arduino.h> #include <ESP8266WiFi.h> #include <ESP8266HTTPClient.h> // RJ11 1 RTS - groen - D5 // RJ11 2 GND - wit - Gnd // RJ11 4 Rx - blauw - Rx (D0) // Wemos D1 mini - rood - +5V // Wemos D1 mini - zwart - gnd // Wemos D1 mini - geel - Rx // Wemos D1 mini - groen - D5 // WiFi parameters to be configured const char* ssid = "WLAN"; // Hoofdlettergevoelig const char* password = "WifiPasswd"; // Hoofdlettergevoelig const char* http_site = "http://192.168.1.10"; // Static IP details... IPAddress ip(192, 168, 1, 99); //IPAddress gateway(192, 168, 1, 254); //IPAddress subnet(255, 255, 255, 0); //IPAddress dns(192, 168, 1, 254); const int requestPin = D5; char input; // incoming serial data (byte) bool readnextLine = false; #define BUFSIZE 75 char buffer[BUFSIZE]; //Buffer for serial data to find \n . int bufpos = 0; long mEVLT = 0; //Meter reading Electrics - consumption low tariff long mEVHT = 0; //Meter reading Electrics - consumption high tariff long mEOLT = 0; //Meter reading Electrics - return low tariff long mEOHT = 0; //Meter reading Electrics - return high tariff long mEAL = 0; //Meter reading Electrics - Actual consumption low tariff long mEAH = 0; //Meter reading Electrics - Actual consumption high tariff long mEAV = 0; //Meter reading Electrics - Actual consumption high tariff long mG = 0; //Meter reading Gas long lastTime = 0; // will store last time long interval = 55000; // interval at which to blink (milliseconds) void setup() { Serial.begin(115200); delay(1000); //Set RTS pin high, so smart meter will start sending telegrams pinMode(requestPin, OUTPUT); digitalWrite(requestPin, HIGH); } void loop() { decodeTelegram(); if(millis() - lastTime > interval) { lastTime = millis(); httpRequest(); //Reset variables to zero for next run mEVLT = 0; mEVHT = 0; mEOLT = 0; mEOHT = 0; mEAL = 0; mEAH = 0; mG = 0; } } //Einde loop void decodeTelegram() { long tl = 0; long tld =0; if (Serial.available()) { input = Serial.read(); char inChar = (char)input; // Fill buffer up to and including a new line (\n) buffer[bufpos] = input&127; bufpos++; //Serial.print(input); if (input == '\n') { // We received a new line (data up to \n) // 0-0:1.0.0(150531205441S) // Datum/tijd (yymmddhhmmss"Summer|Winter") // 1-0:1.8.1(000992.992*kWh) // 1-0:1.8.1 = Elektra verbruik laag tarief (DSMR v4.0) if (sscanf(buffer,"1-0:1.8.1(%d.%d" ,&tl, &tld)==2){ mEVLT = (tl*1000)+tld; } // 1-0:2.8.1(000348.890*kWh) // 1-0:1.8.2 = Elektra verbruik hoog tarief (DSMR v4.0) if (sscanf(buffer,"1-0:1.8.2(%d.%d" ,&tl, &tld)==2){ mEVHT = (tl*1000)+tld; } // 1-0:1.8.2(000560.157*kWh) // 1-0:2.8.1 = Elektra opbrengst laag tarief (DSMR v4.0) if (sscanf(buffer,"1-0:2.8.1(%d.%d" ,&tl, &tld)==2){ mEOLT = (tl*1000)+tld; } // 1-0:2.8.2(000859.885*kWh) // 1-0:2.8.2 = Elektra opbrengst hoog tarief (DSMR v4.0) if (sscanf(buffer,"1-0:2.8.2(%d.%d" ,&tl, &tld)==2){ mEOHT = (tl*1000)+tld; } // 1-0:1.7.0(00.424*kW) // 1-0:2.7.0(00.000*kW) // 1-0:1.7.x = Electricity consumption actual usage (DSMR v4.0) if (sscanf(buffer,"1-0:1.7.0(%d.%d" ,&tl , &tld) == 2) { mEAL = (tl*1000)+tld; } if (sscanf(buffer,"1-0:2.7.0(%d.%d" ,&tl , &tld) == 2) { mEAH = (tl*1000)+tld; } // 0-1:24.2.1(150531200000S)(00811.923*m3) // 0-1:24.2.1 = Gas (DSMR v4.0) on Kaifa MA105 meter if (strncmp(buffer, "0-1:24.2.1", strlen("0-1:24.2.1")) == 0) { if (sscanf(strrchr(buffer, '(') + 1, "%d.%d", &tl, &tld) == 2) { mG = (tl*1000)+tld; } } Serial.print(buffer); // Empty buffer again (whole array) for (int i=0; i<80; i++) { buffer[i] = 0; } bufpos = 0; } } //Einde 'if AltSerial.available' } //Einde 'decodeTelegram()' functie void httpRequest() { Serial.println("Start httpRequest"); // First connect Wifi // Static IP Setup Info Here... WiFi.config(ip); //If you need Internet Access You should Add DNS also WiFi.config(ip, gateway, subnet, dns); WiFi.begin(ssid, password); // while wifi not connected yet, print '.' // then after it connected, get out of the loop delay(5000); //while (WiFi.status() != WL_CONNECTED) { for (int i = 0; i <= 20; i++){ delay(500); Serial.print("."); } //} Serial.println("WiFi connected"); String cmd = http_site; cmd += "/SlimmeMeter.php?mEVLT="; cmd += String(mEVLT); cmd += "&mEVHT="; cmd += String(mEVHT); cmd += "&mEOLT="; cmd += String(mEOLT); cmd += "&mEOHT="; cmd += String(mEOHT); cmd += "&mEAL="; cmd += String(mEAL); cmd += "&mEAH="; cmd += String(mEAH); cmd += "&mG="; cmd += String(mG); //Serial.println(cmd); HTTPClient http; WiFiClient client; Serial.println("Sending String 1"); http.begin(client, cmd); http.GET(); WiFi.disconnect(); }
De PHP pagina:
<?php date_default_timezone_set('Europe/Amsterdam'); //ini_set('display_errors', 1); //ini_set('display_startup_errors', 1); //error_reporting(E_ALL); //Connect to database $MyUsername = "dbuser"; $MyPassword = "dbpasswd"; $MyHostname = "localhost"; $MyDatabase = "SBFspot"; $MyTable = "SlimmeMeter"; $MyFaultTable = "SlimmeMeterFouten"; $SMATable = "vwDayData"; $dbh = mysqli_connect($MyHostname , $MyUsername, $MyPassword, $MyDatabase); //Waardes van vorige wegschrijf actie ophalen $query = mysqli_query($dbh, "SELECT lt_verbruik, ht_verbruik, lt_opbrengst, ht_opbrengst, huidig_verbruik, gas FROM ".$MyTable." ORDER BY ".$MyTable.".time DESC LIMIT 1"); $row = mysqli_fetch_array($query); $previous_lt_verbruik = $row[0]; $previous_ht_verbruik = $row[1]; $previous_lt_opbrengst = $row[2]; $previous_ht_opbrengst = $row[3]; $previous_huidig = $row[4]; $previous_gas = $row[5]; $min_vermogen = 0; $max_vermogen = 3*230*25; //Laatste waarde zonnepanelen ophalen $query1 = mysqli_query($dbh,"SELECT TimeStamp,TotalYield,Power FROM ".$SMATable." ORDER BY ".$SMATable.".TimeStamp DESC LIMIT 1"); $row1 = mysqli_fetch_array($query1); $TimeStamp = $row1[0]; $CurYield = $row1[1]; //$CurPower = $row1[1]; if(strtotime($TimeStamp) < strtotime("-10 minutes")) { $CurPower = 0; } else { $CurPower = $row1[2]; } //Alle opgehaalde variabelen op type "INTEGER" zetten ivm vergelijken zometeen settype($previous_lt_verbruik, "integer"); settype($previous_ht_verbruik, "integer"); settype($previous_lt_opbrengst, "integer"); settype($previous_ht_opbrengst, "integer"); settype($previous_huidig, "integer"); settype($previous_gas, "integer"); settype($CurYield, "integer"); settype($CurPower, "integer"); //GET variabelen naar andere variabele schrijven $mEVLT = $_GET["mEVLT"]; $mEVHT = $_GET["mEVHT"]; $mEOLT = $_GET["mEOLT"]; $mEOHT = $_GET["mEOHT"]; $mEAV = $_GET["mEAL"]; $mEAT = $_GET["mEAH"]; $mG = $_GET["mG"]; //GET variabelen op "integer" zetten settype($mEVLT, "integer"); settype($mEVHT, "integer"); settype($mEOLT, "integer"); settype($mEOHT, "integer"); settype($mEAV, "integer"); settype($mEAT, "integer"); settype($mEAR, "integer"); settype($mG, "integer"); //Resultaat verbruik/opbrengst $mEAR = $mEAV-$mEAT; //Checken of binnengekomen standen voldoen aan eisen (meterstand = hoger dan vorige, huidig verbruik > minimale vermogen, kleiner dan maximale vermogen) om incorrecte waarden te negeren if ( $mEVLT >= $previous_lt_verbruik && $mEVHT >= $previous_ht_verbruik && $mEOLT >= $previous_lt_opbrengst && $mEOHT >= $previous_ht_opbrengst && $mG >= $previous_gas //Inkomende meterstand moet groter zijn dan vorige meterstand && $mG <= ($mG + 6000) //Gasmeter is type "G6", which stands for max. 6m3/hour. So value can never be bigger than value + 6000 && $mEAV <= $max_vermogen //Huidig verbruik moet kleiner zijn dan max. vermogen 1fase aansluiting ) { $SQL = "INSERT INTO ".$MyDatabase.".".$MyTable." (id, time, lt_verbruik, ht_verbruik, lt_opbrengst, ht_opbrengst, huidig_verbruik, gas, sp_opbrengst, sp_power) VALUES (NULL, DEFAULT, ".$mEVLT.", ".$mEVHT.", ".$mEOLT.", ".$mEOHT.", ".$mEAR.", ".$mG.", ".$CurYield.", ".$CurPower.")"; mysqli_query($dbh,$SQL); } else { //Foutieve waarden wegschrijven naar tabel voor latere debugging $SQL = "INSERT INTO ".$MyDatabase.".".$MyFaultTable." (id, time, lt_verbruik, ht_verbruik, lt_opbrengst, ht_opbrengst, huidig_verbruik, gas, sp_opbrengst, sp_power) VALUES (NULL, DEFAULT, ".$mEVLT.", ".$mEVHT.", ".$mEOLT.", ".$mEOHT.", ".$mEAR.", ".$mG.", ".$CurYield.", ".$CurPower.")"; mysqli_query($dbh,$SQL); } ?>
Bash script om maandelijks data te archiveren
#!/bin/bash HOST="192.168.1.10" USER="backupuser" TSTAMP=`date +%Y%m%d` STARTDIR="/beheer" LOGFILE="/beheer/var/cron.log" export PATH="$PATH:/usr/local/mysql/bin" cp $STARTDIR/etc/my.cnf ~/.my.cnf LOGTSTAMP=`date "+%Y-%m-%d %T"` echo "$LOGTSTAMP Archive SBFspot" >> $LOGFILE WDATE="`date +%Y-%m-'01' --date='-2 month'`" YDATE="`date +%Y-%m-'01' --date='-1 year'`" WQUERY="INSERT INTO SlimmeMeterWeek (id, time, lt_verbruik, ht_verbruik, lt_opbrengst, ht_opbrengst, gas, sp_opbrengst) SELECT id, time, lt_verbruik, ht_verbruik, lt_opbrengst, ht_opbrengst, gas, sp_opbrengst FROM SlimmeMeter JOIN (SELECT MIN(time) as mints FROM SlimmeMeter GROUP BY DATE(time), HOUR(time)) tt ON SlimmeMeter.time = tt.mints WHERE time < \"$WDATE\" ORDER BY SlimmeMeter.time DESC;" WDELQUERY="DELETE FROM SlimmeMeter WHERE time < \"$WDATE\";" YQUERY="INSERT INTO SlimmeMeterMaand (id, time, lt_verbruik, ht_verbruik, lt_opbrengst, ht_opbrengst, gas, sp_opbrengst) SELECT SlimmeMeterWeek.* FROM SlimmeMeterWeek JOIN (SELECT MIN(time) as mints FROM SlimmeMeterWeek GROUP BY DATE(time)) tt ON SlimmeMeterWeek.time = tt.mints WHERE time < \"$YDATE\" ORDER BY SlimmeMeterWeek.time DESC;" YDELQUERY="DELETE FROM SlimmeMeterWeek WHERE time < \"$YDATE\";" mysql -h $HOST -u $USER -D SBFspot -e "drop table SMbak;" mysql -h $HOST -u $USER -D SBFspot -e "CREATE TABLE SMbak SELECT * FROM SlimmeMeter;" mysql -h $HOST -u $USER -D SBFspot -e "$WQUERY" mysql -h $HOST -u $USER -D SBFspot -e "$WDELQUERY" mysql -h $HOST -u $USER -D SBFspot -e "$YQUERY" mysql -h $HOST -u $USER -D SBFspot -e "$YDELQUERY"
my.cnf voor het backupscript
[mysqldump] user=backupuser password=dbpassword [mysql] user=backupuser password=dbpassword