Fronius API Bash Script Logging to MariaDB: Difference between revisions
From KlavoWiki
Jump to navigationJump to search
(Created page with "I have created a BASH script to save inverter data and history. I am running a Raspberry Pi and a local MariaDB server. = BASH Script = <pre> #/bin/bash rows=20...") |
|||
Line 5: | Line 5: | ||
<pre> | <pre> | ||
#/bin/bash | #/bin/bash | ||
Red='\033[0;31m' | |||
Green='\033[0;32m' | |||
BrownO='\033[0;33m' | |||
Blue='\033[0;34m' | |||
Magenta='\033[0;35m' | |||
Cyan='\033[0;36m' | |||
White='\033[1;37m' | |||
NC='\033[0m' | |||
rows=20 #Lines to Display | rows=20 #Lines to Display | ||
Line 10: | Line 20: | ||
db_Name=solar #Database Name | db_Name=solar #Database Name | ||
db_Table=5kw #Table Name | db_Table=5kw #Table Name | ||
db_Server=localhost #Server Address, IP or FQDN | db_Server=localhost #Server Address, IP or FQDN | ||
Line 22: | Line 30: | ||
if [ $count -eq 1 ]; then | if [ $count -eq 1 ]; then | ||
printf "${White}Date\t\t\tWatts\tVolts\tAMPs\tToday\tGross Total\tYear Total${NC}\n" | |||
fi | fi | ||
echo -e $strDateTime ' | echo -e $strDateTime '\t'$PAC '\t'$VAC '\t'$IAC '\t'$ETODAY '\t'$ETOTAL'\t\t'$YTOTAL | ||
if [ $count -eq $rows ]; then | if [ $count -eq $rows ]; then | ||
Line 41: | Line 49: | ||
write_sql () { | write_sql () { | ||
mysql | /usr/bin/mysql -D "$db_Name" -e "insert into $db_Table (date, etotal, etoday, ytotal, pac, vac, iac, fac) values ('$strDateTime', '$ETOTAL', '$ETODAY', '$YTOTAL', '$PAC', '$VAC', '$IAC', '$FAC');" | ||
Line 50: | Line 58: | ||
strDateTime=$(date '+%Y-%m-%d %H:%M:%S') | strDateTime=$(date '+%Y-%m-%d %H:%M:%S') | ||
timeloop=$(date '+%H%M') | |||
strVal=$(curl -sG http:// | strVal=$(/usr/bin/curl -sG http://10.13.13.246/solar_api/v1/GetInverterRealtimeData.cgi?'Scope=Device&DataCollection=CommonInverterData&DeviceId=1') | ||
result=$? | result=$? | ||
Line 62: | Line 71: | ||
fi | fi | ||
ETODAY=$(jq '.Body.Data.DAY_ENERGY.Value' <<< $strVal) | ETODAY=$(/usr/bin/jq '.Body.Data.DAY_ENERGY.Value' <<< $strVal) | ||
ETOTAL=$(jq '.Body.Data.TOTAL_ENERGY.Value' <<< $strVal) | ETOTAL=$(/usr/bin/jq '.Body.Data.TOTAL_ENERGY.Value' <<< $strVal) | ||
YTOTAL=$(jq '.Body.Data.YEAR_ENERGY.Value' <<< $strVal) | YTOTAL=$(/usr/bin/jq '.Body.Data.YEAR_ENERGY.Value' <<< $strVal) | ||
PAC=$(jq '.Body.Data.PAC.Value' <<< $strVal) | PAC=$(/usr/bin/jq '.Body.Data.PAC.Value' <<< $strVal) | ||
VAC=$(jq '.Body.Data.UAC.Value' <<< $strVal) | VAC=$(/usr/bin/jq '.Body.Data.UAC.Value' <<< $strVal) | ||
IAC=$(jq '.Body.Data.IAC.Value' <<< $strVal) | IAC=$(/usr/bin/jq '.Body.Data.IAC.Value' <<< $strVal) | ||
FAC=$(jq '.Body.Data.FAC.Value' <<< $strVal) | FAC=$(/usr/bin/jq '.Body.Data.FAC.Value' <<< $strVal) | ||
if [ $PAC -lt 7 ] && [ $timeloop -gt 1600 ]; then | if [ $PAC -lt 7 ] && [ $timeloop -gt 1600 ]; then | ||
Line 75: | Line 84: | ||
fi | fi | ||
write_sql | if [ $PAC != 'null' ]; then | ||
write_sql | |||
fi | |||
#display_output | #display_output | ||
Revision as of 00:29, 3 April 2021
I have created a BASH script to save inverter data and history. I am running a Raspberry Pi and a local MariaDB server.
BASH Script
#/bin/bash Red='\033[0;31m' Green='\033[0;32m' BrownO='\033[0;33m' Blue='\033[0;34m' Magenta='\033[0;35m' Cyan='\033[0;36m' White='\033[1;37m' NC='\033[0m' rows=20 #Lines to Display count=1 #Predefine rows db_Name=solar #Database Name db_Table=5kw #Table Name db_Server=localhost #Server Address, IP or FQDN timeloop=$(date '+%H%M') display_output () { if [ $count -eq 1 ]; then printf "${White}Date\t\t\tWatts\tVolts\tAMPs\tToday\tGross Total\tYear Total${NC}\n" fi echo -e $strDateTime '\t'$PAC '\t'$VAC '\t'$IAC '\t'$ETODAY '\t'$ETOTAL'\t\t'$YTOTAL if [ $count -eq $rows ]; then count=0 echo echo fi ((count++)) } write_sql () { /usr/bin/mysql -D "$db_Name" -e "insert into $db_Table (date, etotal, etoday, ytotal, pac, vac, iac, fac) values ('$strDateTime', '$ETOTAL', '$ETODAY', '$YTOTAL', '$PAC', '$VAC', '$IAC', '$FAC');" } while [ $timeloop -lt 1930 ]; do strDateTime=$(date '+%Y-%m-%d %H:%M:%S') timeloop=$(date '+%H%M') strVal=$(/usr/bin/curl -sG http://10.13.13.246/solar_api/v1/GetInverterRealtimeData.cgi?'Scope=Device&DataCollection=CommonInverterData&DeviceId=1') result=$? if [ $result -ne 0 ]; then echo echo Unable to access URL of inverter. echo Exiting. echo exit fi ETODAY=$(/usr/bin/jq '.Body.Data.DAY_ENERGY.Value' <<< $strVal) ETOTAL=$(/usr/bin/jq '.Body.Data.TOTAL_ENERGY.Value' <<< $strVal) YTOTAL=$(/usr/bin/jq '.Body.Data.YEAR_ENERGY.Value' <<< $strVal) PAC=$(/usr/bin/jq '.Body.Data.PAC.Value' <<< $strVal) VAC=$(/usr/bin/jq '.Body.Data.UAC.Value' <<< $strVal) IAC=$(/usr/bin/jq '.Body.Data.IAC.Value' <<< $strVal) FAC=$(/usr/bin/jq '.Body.Data.FAC.Value' <<< $strVal) if [ $PAC -lt 7 ] && [ $timeloop -gt 1600 ]; then echo Time to exit exit 0 fi if [ $PAC != 'null' ]; then write_sql fi #display_output sleep 19 done
SQL Database
mysql
create database solar
CREATE TABLE `5kw` ( `DATE` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `ETODAY` int(5) NOT NULL DEFAULT 0.00, `PAC` int(4) NOT NULL DEFAULT 0, `IAC` float(4,2) DEFAULT NULL, `VAC` float(4,1) DEFAULT NULL, `FAC` float(4,2) DEFAULT NULL, `YTOTAL` int(9) DEFAULT NULL, `ETOTAL` float(10) DEFAULT NULL, PRIMARY KEY (`DATE`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;