Home Assistant port of KEGCOP

[lagerbeer666]

https://github.com/lagerbeer/kegcop_home_assistant

OPERATION:

The tool is managed in the screen below. There are only three functions that need to be managed.

1. New Keg Date: enter in the date that the keg is tapped. The tool will add up the pulses based on the start date. Change date when the keg is empty and replaced with a new keg.
2. Keg Size: The keg volume will be based off of the amount selected in the slider.
3. Pulse per Oz calibration: Pour a measured amount and then change this number to get the correct calculation per pour. For example, pour 8 oz of liquid and check the control panel results for that pour. You can hard code your pulse per oz results in the input_numbers.yaml in the initial field. If your home assistant instance restarts it will reset this number back to the initial value.

 

[rkhanso]

Thanks @lagerbeer666
I'll definitely be checking this out.

 

[rkhanso]

I'm definitely not a HA expert. And when it comes to coding -- no clue.
But is there a reason you installed MariaDB instead of using InfluxDB? I already have Influx installed.

And is the db_url the same IP/URL as the HA server/PI?

 

[rkhanso]

Nevermind. I see in the HA documentation, InfluxDB is not listed.

 

[rkhanso]

I see on the HA recorder: page, a mention of the importance of database startup before Home Assistant starts. Did you have to do that?
https://www.home-assistant.io/integrations/recorder/#database-startup

 

[lagerbeer666]

I see on the HA recorder: page, a mention of the importance of database startup before Home Assistant starts. Did you have to do that?
https://www.home-assistant.io/integrations/recorder/#database-startup

I have this in the config yaml:

recorder:
db_url: !secret db_url
purge_keep_days: 30
commit_interval: 20

 

[rkhanso]

There are a few different MariaDB formats for URL in the recorder: page of HA.
MariaDB (omit pymysql)
mysql://userassword@SERVER_IP/DB_NAME?charset=utf8mb4
MariaDB (omit pymysql, using TLS encryption)
mysql://userassword@SERVER_IP/DB_NAME?charset=utf8mb4;ssl=true
MariaDB (omit pymysql, Socket)
mysql://userassword@SERVER_IP/DB_NAME?unix_socket=/var/run/mysqld/mysqld.sock&charset=utf8mb4
MariaDB
mysql+pymysql://userassword@SERVER_IP/DB_NAME?charset=utf8mb4
MariaDB (Socket)
mysql+pymysql://userassword@localhost/DB_NAME?unix_socket=/var/run/mysqld/mysqld.sock&charset=utf8mb4

I'm "guessing" you're using the format in bold, right? in your secrets.yaml...
THe smiley face is really a colon and lowercase p

 

[day_trippr]

fwiw, you can avoid those emoticons by bracketing with {code} and {/code} tags (replace squiggles with square brackets)...

Cheers!

 

[lagerbeer666]

There are a few different MariaDB formats for URL in the recorder: page of HA.
MariaDB (omit pymysql)
mysql://userassword@SERVER_IP/DB_NAME?charset=utf8mb4
MariaDB (omit pymysql, using TLS encryption)
mysql://userassword@SERVER_IP/DB_NAME?charset=utf8mb4;ssl=true
MariaDB (omit pymysql, Socket)
mysql://userassword@SERVER_IP/DB_NAME?unix_socket=/var/run/mysqld/mysqld.sock&charset=utf8mb4
MariaDB
mysql+pymysql://userassword@SERVER_IP/DB_NAME?charset=utf8mb4
MariaDB (Socket)
mysql+pymysql://userassword@localhost/DB_NAME?unix_socket=/var/run/mysqld/mysqld.sock&charset=utf8mb4

I'm "guessing" you're using the format in bold, right? in your secrets.yaml...
THe smiley face is really a colon and lowercase p

Use any password for the connection string.

db_url: mysql://homeassistantASSWORD@core-mariadb/homeassistant?charset=utf8mb4

 

[lagerbeer666]

funny... the post is changing the P in "Password" with a smiley face.

 

[rkhanso]

I have a lot to learn about making dashboards from scratch. It will take me many days to get a dashboard that looks like yours. But am learning along the way.

Adding MariaDB and whatever configs I put in for that broke my Grafana stats. Gotta figure that one out because I use Grafana frequently.

 

[lagerbeer666]

I have a lot to learn about making dashboards from scratch. It will take me many days to get a dashboard that looks like yours. But am learning along the way.

You can copy the dashboard.yaml from GIT into an new dashboard directly to get the same setup as mine then select raw configuration editor and save

Y

 

[rkhanso]

I'm starting to get some things to show up. Not totally working though. I just have 1 keg, 1 kegerator (and relay to turn it on/off), 1 flow meter, 3 temp sensors, 1 solenoid for turning on a tower fan.
Getting some Entity not available errors (keg size, keg date), and sensor.beer_flow_meters_lower_chamber_temp, upper chamber temp, garage_freezer, garage fridge, garage frz_temperature.

Still working on it.

 

[lagerbeer666]

I'm starting to get some things to show up. Not totally working though. I just have 1 keg, 1 kegerator (and relay to turn it on/off), 1 flow meter, 3 temp sensors, 1 solenoid for turning on a tower fan.
Getting some Entity not available errors (keg size, keg date), and sensor.beer_flow_meters_lower_chamber_temp, upper chamber temp, garage_freezer, garage fridge, garage frz_temperature.

Still working on it.

you can build the keg size and keg date sensors by going to "Settings" and select "Device and Services". At the top left select helpers and on the bottom left you can add these using the date and number functions.

 

[farcouet]

Hello,
I have flash a ESP32 with the ESPHOME and connected it to signal generator do start testing.
I have configured mariadb, added the template.yaml and sql.yaml content to my configuration.yaml and created the helpers.
I can see pulses coming. But I still get errors in the dashboard. I have tested only keg1 for now so will show what I see for keg1. It seems that the number of pulses is seen but not updating the quantity left and last pour date. I am thinking that some of the sql update code is not being executed but I am not sure.

Any idea what I am doing wrong?
Thanks!
Fred

I really don't know where too look (for now at least).

 

[farcouet]

Ok I have been able to correct the input and the reason is that I forgot to include it in my configuration.yaml file. So the pulses per oz calibration works.

But I don't see anything for Beer_Flow_Meters_count_1 in the SQL DB (the following querry just return information about my beer chiller and fermenter).

SELECT * FROM states_meta WHERE entity_id like'%Beer%';

 

[farcouet]

I just found my mistake. I have not named correctly the esphome device. So after fixing that then it works correctly!

Thanks for having shared all your work. I will replace my Raspberri PI with this so that will make my whole setup much simpler to track what's left in the kegs ;-)