@Kim_Andersen
As follow-up in different topic, this will be quite long but complete guide how to setup working connection between SMA Inverter via Bluetooth using RPI and then monitoring/publishing gathered data into MQTT broker.
Which from there can be used as anyone wants. As well data will be stored for further use in MYSQL database.
let’s use RPI3b+ as it has no issues with BT
for MySQL I would recommend your QNAP if it is running 24/7 as QNAP has got easy to go interface where you can install mysql and phpmyadmin fairly easily.
If not, then we will setup sql on RPI, this guid will assume running SQL on QNAP for now.
And it can run Mosquitto MQTT broker as well if you wish. But for this guide I use RPI for that.
disclaimer: do not follow blindly, try to understand each command 
I assume you know what shell and linux is and you can google stuff in case of some error messages
I do recommend to do it on TEST rpi/card/usb whatever so you can start from scratch anytime!!!, then do it on production when you feel comfortable
RPI:
sudo apt install python3-pip
sudo pip3 install paho-mqtt
pip3 install paho-mqtt
sudo openhabian-config
-> menu 20 | Optional Components -> 23 | Mosquitto
Check as well if your BT is enabled
vanilla sbfspot: (this step is needed)
get these files to your desktop and run them in QNAP mysql phpmyadmin
https://github.com/SBFspot/SBFspot/blob/master/SBFspot/CreateMySQLDB.sql
https://github.com/SBFspot/SBFspot/blob/master/SBFspot/CreateMySQLUser.sql
then back to RPI when you have your mysql up and ready with created user/db ^
cd ~
curl -s https://raw.githubusercontent.com/sbfspot/sbfspot-config/master/sbfspot-config | sudo bash
follow on screen instruction which will result of working sbfspot saving data to mysql
After this step and during the day you should have your mysql database populated every 5 minutes by some data, you can use those as you want already.
but we want MQTT … so let’s fun begin… 
brace yourself, there will be some compiling of your very own program
create data folder and edit fstab
sudo mkdir /var/data
sudo nano /etc/fstab
add ramdisk mounted to /var/data to preserver SD card (goes to the end of your fstab)
tmpfs /var/data tmpfs defaults,noatime,nosuid,mode=0777,size=256k 0 0
hit CTRL+X and Y to save it
this will after reboot ensure your /var/data folder is used as ramdisk (eg. no SD card use) and it will use 256Kb of your RAM
now we need to grab latest SBFspot source code and edit some files.
cd ~
mkdir install
cd install
git clone https://github.com/SBFspot/SBFspot.git
cd SBFspot/SBFspot
nano -l db_MySQL.cpp
find
int db_SQL_Base::device_status(InverterData *inverters[], time_t spottime)
(should be on line 150)
after
if ((rc = exec_query(sql.str())) != SQL_OK)
print_error("exec_query() returned", sql.str());
add
std::ofstream file("/var/data/pve_now");
file << inverters[inv]->TotalPac;
file.close();
std::ofstream file1("/var/data/pve_daily");
file1 << inverters[inv]->EToday;
file1.close();
std::ofstream file2("/var/data/pve_total");
file2 << inverters[inv]->ETotal * 0.000001;
file2.close();
std::ofstream file3("/var/data/pve_wakeup");
file3 << inverters[inv]->WakeupTime;
file3.close();
std::ofstream file4("/var/data/pve_status");
file4 << status_text(inverters[inv]->DeviceStatus);
file4.close();
std::ofstream file5("/var/data/pve_runtime");
file5 << inverters[inv]->FeedInTime/3600;
file5.close();
std::ofstream file6("/var/data/pve_temperature");
file6 << inverters[inv]->Temperature/100;
file6.close();
^^^^ This may vary depends on your SMA type. Check SBFspot data inserts if your’s matching these… but it should
I know it’s not nice pieace of code having it hardcoded, but I don’t care … PVE is not changing anyway and this was by far the easiest and quickest way how to get data which I needed. Feel free to write separate functions to have it out of MySQL.cpp …
if you want to track each panels or BT signal …
nano -l db_MySQL_Export.cpp
find
int db_SQL_Export::spot_data(InverterData *inv[], time_t spottime)
(should be 217)
after
if ((rc = exec_query(sql.str())) != SQL_OK)
{
print_error("[spot_data]exec_query() returned", sql.str());
break;
}
add
std::ofstream file("/var/data/pve_signal");
file << inv[i]->BT_Signal;
file.close();
std::ofstream file1("/var/data/pve_panel_south");
file1 << inv[i]->Pdc1;
file1.close();
std::ofstream file2("/var/data/pve_panel_east");
file2 << inv[i]->Pdc2;
file2.close();
Now we are ready to compile your mqtt pre-ready SBFspot…
make mysql
it will end up with error complaining you are missing some libs
from top of my head you will need
sudo apt install libboost-dev libmysqlclient-dev libbost-system-dev libboost-datetime-dev
mysql-client was replaced by mariadb-client if I remember correctly, so you might be googling a bit
and for sure some more which I don’t remember, but basically you just google what is missing and first answer is usually what you need to install (and there is no need to anything else than APT INSTALL, after which always rerun.
make mysql
till the point it will compile without any errors
after that
sudo make install_mysql
Not there yet … but it’s almost done
now we need to create MQTT script. It’s hopefully self explanatory so briefly.
It’s using Homie 4 convention and as such will be picked out by OH autodiscovery, It’s tailored for my SMA for upper hacked SBFspot, all you don’t like simply rename, or remove, but make sure you are renaming stuff in SBFspot as well!
This script is monitoring ram-stored files each 10seconds. If anything is different from last state, it will update self and publish new value to the MQTT. As well it maintains connection to MQTT so you simply just leave it running.
cd ~
mkdir scripts
nano scripts/mqtt.py
and paste this
#!/usr/bin/env python
import time
import paho.mqtt.client as mqtt
h_ver = "4.0"
h_n = "SMA"
h_nodes = "pve"
retain = True
qos = 1
homie_prefix = "homie"
homie_id = "powerplant"
node = homie_prefix + "/" + homie_id + "/"
url = "/var/data/"
files = {"daily":"pve_daily","now":"pve_now","east":"pve_panel_east","south":"pve_panel_south","runtime":"pve_runtime","signal":"pve_signal","status":"pve_status","temp":"pve_temperature","total":"pve_total","wake":"pve_wakeup"}
#### FUNCTIONS ########################################
def get_data(name):
data = open(url + name,"r").read()
return data
last_data = {"daily":get_data(files["daily"]),"now":get_data(files["now"]),"east":get_data(files["east"]),"south":get_data(files["south"]),"runtime":get_data(files["runtime"]),"signal":get_data(files["signal"]),"status":get_data(files["status"]),"temp":get_data(files["temp"]),"total":get_data(files["total"]),"wake":get_data(files["wake"])}
def pub(path,data):
client.publish(node + path, data, qos, retain)
def advertize_device():
pub("$state","init")
device_info()
nodes_info()
pub("$state","ready")
# heartbeat()
def device_info():
pub("$homie", h_ver)
pub("$name", h_n)
pub("$nodes", h_nodes)
def nodes_info():
pub("pve/$name","PVE Inverter")
pub("pve/$properties","now,daily,east,south,runtime,signal,status,temp,total,wake")
pub("pve/now",last_data["now"])
pub("pve/now/$name","Actual Yield")
pub("pve/now/$unit","W")
pub("pve/now/$datatype","integer")
pub("pve/daily",last_data["daily"])
pub("pve/daily/$name","Daily Yield")
pub("pve/daily/$unit","Wh")
pub("pve/daily/$datatype","integer")
pub("pve/east",last_data["east"])
pub("pve/east/$name","East Side")
pub("pve/east/$unit","W")
pub("pve/east/$datatype","integer")
pub("pve/south",last_data["south"])
pub("pve/south/$name","South Side")
pub("pve/south/$unit","W")
pub("pve/south/$datatype","integer")
pub("pve/runtime",last_data["runtime"])
pub("pve/runtime/$name","Feed in Time")
pub("pve/runtime/$datatype","integer")
pub("pve/signal",last_data["signal"])
pub("pve/signal/$name","Bluetooth Signal")
pub("pve/signal/$datatype","float")
pub("pve/status",last_data["status"])
pub("pve/status/$name","Inverter Status")
pub("pve/status/$datatype","string")
pub("pve/temp",last_data["temp"])
pub("pve/temp/$name","Inverter Temperature")
pub("pve/temp/$unit","°C")
pub("pve/temp/$datatype","float")
pub("pve/total",last_data["total"])
pub("pve/total/$name","Total Yield")
pub("pve/total/$unit","MWh")
pub("pve/total/$datatype","float")
pub("pve/wake",last_data["wake"])
pub("pve/wake/$name","Wake & Sleep Time")
pub("pve/wake/$datatype","integer")
def on_connect(client, userdata, flags, rc):
advertize_device()
#######################################################
##### CONNECTION LOGIC
client = mqtt.Client("PVE")
client.connect("localhost")
client.on_connect = on_connect
client.will_set(node + "$state", "lost")
client.loop_start()
time.sleep(10)
try:
data = {}
while True:
## update loop
for name,path in files.items():
data[name] = get_data(path)
if(data[name] != last_data[name]):
pub("pve/" + str(name), data[name])
last_data[name] = data[name]
## wait
time.sleep(10)
except KeyboardInterrupt:
print("Stopping client")
pub("$state","disconnected")
client.disconnect()
client.loop_stop()
And final touches…
comment anything sbfspot created there, we don’t want to run it as normal user
crontab -e
## SBFspot
#*/5 6-22 * * * sudo /usr/local/bin/sbfspot.3/daydata
#55 05 * * * sudo /usr/local/bin/sbfspot.3/monthdata
Now we need to adjust root crontab (as we need to have access to ramdisk which is under root permissions).
(it can be done in non root crontab as well, but u need to make sure to have sudo before sma pooling scripts as well as openhabian user without sudo password or make sure regular user can write to /var/data after reboot)
firstly after reboot it will populate SMA data which is needed to recreate /var/data files
“-finq” forces sbfspot to fetch data even when SMA is sleeping, eg. during the night
then we need to run our mqtt daemon, it’s waiting 2minutes just to be on safe side to have SMA data already there.
and then we have regular SMA data pooling each 5minutes.
second one is for monthly data, uncommend it if you want those in your mysql. Otherwise it’s not used by MQTT at all.
sudo crontab -e
## initial PVE data
@reboot perl -le "sleep 30" && /usr/local/bin/sbfspot.3/daydata -finq
#### MQTT
@reboot perl -le "sleep 120" && python3 /home/openhabian/scripts/mqtt.py
## SBFspot
*/5 6-22 * * * /usr/local/bin/sbfspot.3/daydata
#55 05 * * * /usr/local/bin/sbfspot.3/monthdata
and FINALLY
sudo shutdown -r
If you’ve done everything right, after reboot and waiting period of rougly 2minutes you should see in your mosquitto broker messages in /homie/powerplant/
OH will automatically autodiscover this new Thing named SMA if you have MQTT Binding up and running 
Yeah I know… but it’s worth every hour you spend on it
If I forgot something, appologies … in that case I’ll help you out, no worries
and sorry for typos, there will be plenty I guess
Cheers
