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