From 247a962c39e4ade2d9fb0b280d400d82fd8db56d Mon Sep 17 00:00:00 2001 From: jwansek Date: Sun, 15 Oct 2023 21:07:46 +0100 Subject: Added plug usage table to web UI, with HTTP requests --- app.py | 13 +++++- app_requirements.txt | 2 + cron_Dockerfile | 4 +- cron_requirements.txt | 6 +++ database.py | 25 +++++++---- devices.py | 6 ++- docker-compose.yml | 1 + mikrotik.py | 21 +++++++--- power.env.example | 15 ++++--- requirements.txt | 6 --- static/scripts.js | 108 ++++++++++++++++-------------------------------- static/style.css | 27 +++++++++++- templates/index.html.j2 | 29 +++++++++++++ 13 files changed, 158 insertions(+), 105 deletions(-) create mode 100644 app_requirements.txt create mode 100644 cron_requirements.txt delete mode 100644 requirements.txt diff --git a/app.py b/app.py index 1d2853c..c4db499 100644 --- a/app.py +++ b/app.py @@ -1,4 +1,5 @@ import database +import mistune import mikrotik import devices import flask @@ -6,18 +7,22 @@ import os app = flask.Flask(__name__) switch = mikrotik.MikroTikSerialDevice() +markdown_renderer = mistune.create_markdown( + renderer = mistune.HTMLRenderer(), + plugins = ["strikethrough", "table", "url"] +) @app.route("/") def route_index(): with database.PowerDatabase(host = devices.HOST) as db: return flask.render_template( "index.html.j2", - tasmota_devices = db.get_tasmota_devices() + tasmota_devices = [[i[0], markdown_renderer(i[-1])] for i in db.get_tasmota_devices()] ) @app.route("/api/mikrotik_devices") def api_get_mikrotik_devices(): - return flask.jsonify(switch.interfaces) + return flask.jsonify({i[0]: markdown_renderer(i[1]) for i in switch.interfaces.items()}) @app.route("/api/mikrotik_interface/") def api_poll_mikrotik_interface(interface): @@ -31,6 +36,10 @@ def api_poll_mikrotik_interface(interface): ) except (IndexError, KeyError): return flask.abort(400) + +@app.route("/api/mikrotik_plug") +def api_get_mikrotik_plug(): + return flask.jsonify({"parent": os.environ["MIKROTIK_TASMOTA"]}) @app.route("/api/plugs") def api_poll_plugs(): diff --git a/app_requirements.txt b/app_requirements.txt new file mode 100644 index 0000000..df52348 --- /dev/null +++ b/app_requirements.txt @@ -0,0 +1,2 @@ +flask +mistune \ No newline at end of file diff --git a/cron_Dockerfile b/cron_Dockerfile index f198662..ad6ea6a 100644 --- a/cron_Dockerfile +++ b/cron_Dockerfile @@ -8,9 +8,9 @@ RUN mkdir app COPY . /app WORKDIR /app RUN touch .docker -RUN pip3 install -r requirements.txt +RUN pip3 install -r cron_requirements.txt RUN echo "*/1 * * * * root python3 /app/devices.py nothourly > /proc/1/fd/1 2>/proc/1/fd/2" > /etc/crontab -RUN echo "@daily root python3 /app/devices.py daily > /proc/1/fd/1 2>/proc/1/fd/2" >> /etc/crontab +RUN echo "@daily root python3 /app/devices.py daily > /proc/1/fd/1 2>/proc/1/fd/2" >> /etc/crontab ENTRYPOINT ["bash"] CMD ["entrypoint.sh"] \ No newline at end of file diff --git a/cron_requirements.txt b/cron_requirements.txt new file mode 100644 index 0000000..2c2bbc9 --- /dev/null +++ b/cron_requirements.txt @@ -0,0 +1,6 @@ +tasmotadevicecontroller==0.0.8 +aiohttp==3.8.3 +pymysql +python-dotenv +pyserial + diff --git a/database.py b/database.py index 60ad520..8b57d25 100644 --- a/database.py +++ b/database.py @@ -28,7 +28,7 @@ class PowerDatabase: with self.__connection.cursor() as cursor: if "TASMOTA_DEVICES" in os.environ.keys(): - for host, username, password in self.get_tasmota_devices(): + for host, username, password, description in self.get_tasmota_devices(): cursor.execute(""" INSERT INTO tasmota_devices (host, username, password) VALUES (%s, %s, %s) @@ -98,8 +98,9 @@ class PowerDatabase: def get_tasmota_devices(self): o = [] - for d in os.environ["TASMOTA_DEVICES"].split(","): - o.append(d.split(":")) + for d in os.environ["TASMOTA_DEVICES"].split(";"): + line = d.split(",") + o.append(line) return o def append_watt_readings(self, host, reading): @@ -115,12 +116,20 @@ class PowerDatabase: plugs = [i[0] for i in self.get_tasmota_devices()] with self.__connection.cursor() as cursor: cursor.execute("SELECT host, MAX(datetime) FROM watt_readings WHERE host IN %s GROUP BY host;", (plugs, )) - plugtimes = cursor.fetchall() + wattplugtimes = cursor.fetchall() - readings = [] - for host, datetime in plugtimes: + cursor.execute("SELECT host, MAX(datetime) FROM kwh_readings WHERE host IN %s GROUP BY host;", (plugs, )) + kwhplugtimes = {i[0]: i[1] for i in cursor.fetchall()} + + readings = {} + for host, datetime in wattplugtimes: cursor.execute("SELECT host, datetime, reading FROM watt_readings WHERE host = %s AND datetime = %s;", (host, datetime)) - readings.append(cursor.fetchone()) + o1 = cursor.fetchone() + readings[host] = {"watts": (o1[1], o1[2])} + + cursor.execute("SELECT host, datetime, reading FROM kwh_readings WHERE host = %s AND datetime = %s;", (host, kwhplugtimes[host])) + o2 = cursor.fetchone() + readings[host]["kWh"] = (o2[1], o2[2]) return readings def get_watt_chart(self): @@ -159,4 +168,4 @@ if __name__ == "__main__": host = None with PowerDatabase(host = host) as db: - print(to_series(db.get_kwh_chart())) + print(db.get_last_plug_readings()) diff --git a/devices.py b/devices.py index f8f17fe..4193780 100644 --- a/devices.py +++ b/devices.py @@ -38,7 +38,9 @@ def poll_watt_all(): loop = asyncio.new_event_loop() asyncio.set_event_loop(loop) with database.PowerDatabase(host = HOST) as db: - for host, username, password in db.get_tasmota_devices(): + devices = db.get_tasmota_devices() + print("There are devices: ", [i[0] for i in devices]) + for host, username, password, description in devices: while True: try: asyncio.run(poll_watt_for(db, host, username, password)) @@ -52,7 +54,7 @@ def poll_kwh_all(): loop = asyncio.new_event_loop() asyncio.set_event_loop(loop) with database.PowerDatabase(host = HOST) as db: - for host, username, password in db.get_tasmota_devices(): + for host, username, password, description in db.get_tasmota_devices(): while True: try: asyncio.run(poll_yesterday_kwh_for(db, host, username, password)) diff --git a/docker-compose.yml b/docker-compose.yml index 6907a00..c531fcb 100644 --- a/docker-compose.yml +++ b/docker-compose.yml @@ -8,3 +8,4 @@ services: image: jwansek/power env_file: - ./power.env + restart: always diff --git a/mikrotik.py b/mikrotik.py index 4cd9231..b739132 100644 --- a/mikrotik.py +++ b/mikrotik.py @@ -26,13 +26,15 @@ class MikroTikSerialDevice: def __post_init__(self): self.interfaces = {} self.last_return = {} - for i in os.environ["MIKROTIK_INTERFACES"].split(","): - self.interfaces.__setitem__(*i.split(":")) + for i in os.environ["MIKROTIK_INTERFACES"].split(";"): + self.interfaces.__setitem__(*i.split(",")) self.is_being_polled = threading.Event() self.poe_cache = {interface: {} for interface in self.interfaces} def get_poe_info(self, interface): - print(self.poe_cache) + # fetch from cache so that multiple processes don't try to access serial at the same time + # this means that the same MikroTikSerialDevice object must be used for multiple threads + # if another thread is accessing the critical region, return from cache if self.is_being_polled.is_set(): fetched_cache = self.poe_cache[interface] fetched_cache["cached"] = True @@ -70,7 +72,13 @@ class MikroTikSerialDevice: if line.startswith("poe"): d.__setitem__(*line.split(": ")) - self.last_return = d + # also fetch from cache if it returned nothing + if d == {}: + fetched_cache = self.poe_cache[interface] + fetched_cache["cached"] = True + return fetched_cache + + self.last_return = d self.poe_cache[interface] = d d["cached"] = was_cached return d @@ -84,6 +92,7 @@ if __name__ == "__main__": dotenv.load_dotenv(dotenv_path = "power.env") mikrotik = MikroTikSerialDevice() - for interface in mikrotik.interfaces: - print(interface, mikrotik.get_poe_info(interface)) + for i in range(10): + for interface in mikrotik.interfaces: + print(interface, mikrotik.get_poe_info(interface)) diff --git a/power.env.example b/power.env.example index 36cc9f2..1b0b3f0 100644 --- a/power.env.example +++ b/power.env.example @@ -1,10 +1,13 @@ -MYSQL_ROOT_PASSWORD=************ +MYSQL_ROOT_PASSWORD=******************* MYSQL_HOST=192.168.69.3 -TASMOTA_DEVICES=switch.plug:admin:=************,nas.plug:admin:=************,12vbrick.plug:admin:=************,backup.plug:admin:=************ +# strings cannot contain , ; or # +TASMOTA_DEVICES=switch.plug,admin,*******************,[Mikrotik CRS112-8P-4S-IN](https://wiki.eda.gay/index.php/Switches);nas.plug,admin,*******************,[TrueNAS NAS](https://wiki.eda.gay/index.php/TrueNAS_NAS);12vbrick.plug,admin,*******************,KVM Switch & Backup External Hard Drive & etc;backup.plug,admin,*******************,Backup NAS -MIKROTIK_DEVICE=COM6 +MIKROTIK_DEVICE=/dev/ttyUSB0 +MIKROTIK_BAUD=115200 MIKROTIK_USER=admin -MIKROTIK_PASS==************ -MIKROTIK_INTERFACES=ether1:pfsense router,ether2:interface2,ether3:interface3,ether4:interface4,ether5:interface5,ether6:interface6,ether7:interface7,ether8:interface8 +MIKROTIK_PASS=******************* +MIKROTIK_INTERFACES=ether1,[EAP225 Wi-Fi AP](https://wiki.eda.gay/index.php/Main_Page);ether2,[TL-RP108GE](https://wiki.eda.gay/index.php/Switches) & [pfsense router](https://wiki.eda.gay/index.php/Pfsense_router);ether3,[Mikrotik CSS610-8G-2S+IN](https://wiki.eda.gay/index.php/Switches);ether4,[PiKVM](https://wiki.eda.gay/index.php/Blikvm_PiKVM);ether5,interface5;ether6,[Intel Compute Stick](https://wiki.eda.gay/index.php/Intel_Compute_Stick);ether7,SSH/Git/PiHole Raspberry Pi;ether8,interface8 +MIKROTIK_TASMOTA=switch.plug -APP_PORT = 5021 \ No newline at end of file +APP_PORT = 5021 diff --git a/requirements.txt b/requirements.txt deleted file mode 100644 index c8fabfc..0000000 --- a/requirements.txt +++ /dev/null @@ -1,6 +0,0 @@ -tasmotadevicecontroller==0.0.8 -aiohttp==3.8.3 -pymysql -python-dotenv -pyserial -flask diff --git a/static/scripts.js b/static/scripts.js index 390c2c3..f252fc5 100644 --- a/static/scripts.js +++ b/static/scripts.js @@ -1,73 +1,37 @@ $(document).ready(function() { - Highcharts.chart('longterm_chart', { - chart: { - type: 'area' - }, - - title: { - text: 'Estimated Worldwide Population Growth by Region' - }, - - subtitle: { - text: 'Source: Wikipedia.org' - }, - - xAxis: { - categories: ['1750', '1800', '1850', '1900', '1950', '1999', '2050'], - tickmarkPlacement: 'on', - title: { - enabled: false - } - - }, - - yAxis: { - title: { - text: 'Billions' - }, - - labels: { - formatter: function() { - return this.value / 1000; - - } - - } - - }, - - tooltip: { - split: true, - valueSuffix: ' millions' - }, - - plotOptions: { - area: { - stacking: 'normal', - lineColor: '#666666', - lineWidth: 1, - marker: { - lineWidth: 1, - lineColor: '#666666' - } - - }, - - series: [{ - name: 'Asia', - data: [502, 635, 809, 947, 1402, 3634, 5268] - }, { - name: 'Africa', - data: [106, 107, 111, 133, 221, 767, 1766] - }, { - name: 'Europe', - data: [163, 203, 276, 408, 547, 729, 628] - }, { - name: 'America', - data: [18, 31, 54, 156, 339, 818, 1201] - }, { - name: 'Oceania', - data: [2, 2, 2, 6, 13, 30, 46] - }] - }}); -}) \ No newline at end of file + fetch("/api/mikrotik_plug").then((resp) => { + resp.json().then((body) => { + const MIKROTIK_PARENT = body["parent"]; + }); + }); + + console.log(MIKROTIK_PARENT); + + get_main_table(); +}) + +function get_main_table() { + fetch("/api/plugs").then((resp) => { + resp.json().then((body) => { + let watts_sum = 0; + let kwh_sum = 0; + Object.keys(body).forEach((host, i) => { + watts = body[host]["watts"]; + kwh = body[host]["kWh"]; + document.getElementById(host + "_watts_now").innerHTML = watts[1]; + document.getElementById(host + "_watts_yesterday").innerHTML = kwh[1]; + watts_sum += watts[1]; + kwh_sum += kwh[1]; + + document.getElementById("watts_last_updated").innerHTML = "Current power usage last updated at " + watts[0]; + document.getElementById("kwh_last_updated").innerHTML = "Yesterday's power usage last updated at " + kwh[0]; + + console.log(host, watts[0], watts[1], kwh[1]) + }); + document.getElementById("sum_watts_now").innerHTML = watts_sum; + document.getElementById("sum_watts_yesterday").innerHTML = kwh_sum; + }); + }); + + setTimeout(get_main_table, 30000); +} \ No newline at end of file diff --git a/static/style.css b/static/style.css index 2bc7b32..56627aa 100644 --- a/static/style.css +++ b/static/style.css @@ -70,7 +70,7 @@ footer { list-style-type: none; width: 45%; display: inline-flex; - /* background-color: pink; */ + background-color: pink; min-height: 350px; margin-bottom: 7px; overflow: hidden; @@ -87,6 +87,31 @@ footer { flex-direction: row-reverse; } +#power_table { + width: 90%; +} + +#header_row { + background-color: black; +} + +#header_row td { + color: #f1f3f3; + font-weight: bold; +} + +#power_table tr { + margin-bottom: 3px; +} + +#last_updated_ul { + font-size: small; +} + +#sum_row { + background-color: gainsboro; +} + @media screen and (max-width: 1200px) { #multicharts ul li { width: 100%; diff --git a/templates/index.html.j2 b/templates/index.html.j2 index 534b1ce..69fcd31 100644 --- a/templates/index.html.j2 +++ b/templates/index.html.j2 @@ -52,8 +52,37 @@ + + + + + + + + + {% for host, description_md in tasmota_devices %} + + + + + + + {% endfor %} + + + + + + +
Plug local hostnamePlug descriptionCurrent power usage (W)Power usage yesterday (kWh)
{{ host }}{{ description_md|safe }}
+ +