diff options
| -rw-r--r-- | .dockerignore | 2 | ||||
| -rw-r--r-- | .gitignore | 2 | ||||
| -rw-r--r-- | app.py | 46 | ||||
| -rw-r--r-- | database.py | 28 | ||||
| -rw-r--r-- | db.env.example | 2 | ||||
| -rw-r--r-- | devices.py | 2 | ||||
| -rw-r--r-- | docker-compose.yml | 2 | ||||
| -rw-r--r-- | mikrotik.py | 89 | ||||
| -rw-r--r-- | power.env.example | 10 | ||||
| -rw-r--r-- | requirements.txt | 3 |
10 files changed, 177 insertions, 9 deletions
diff --git a/.dockerignore b/.dockerignore index f827b66..f37314c 100644 --- a/.dockerignore +++ b/.dockerignore @@ -1 +1 @@ -db.env +power.env @@ -1,4 +1,4 @@ -db.env +power.env # Byte-compiled / optimized / DLL files __pycache__/ @@ -0,0 +1,46 @@ +import database +import mikrotik +import devices +import flask +import os + +app = flask.Flask(__name__) +switch = mikrotik.MikroTikSerialDevice() + +@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() + ) + +@app.route("/api/mikrotik_devices") +def api_get_mikrotik_devices(): + return flask.jsonify(switch.interfaces) + +@app.route("/api/mikrotik_interface/<interface>") +def api_poll_mikrotik_interface(interface): + try: + return flask.jsonify( + { + "interface": interface, + "description": switch.interfaces[interface], + "poe_status": switch.get_poe_info(interface) + } + ) + except (IndexError, KeyError): + return flask.abort(400) + +@app.route("/api/plugs") +def api_poll_plugs(): + with database.PowerDatabase(host = devices.HOST) as db: + return flask.jsonify(db.get_last_plug_readings()) + +@app.route("/api/daily_chart") +def api_get_watt_chart(): + with database.PowerDatabase(host = devices.HOST) as db: + return flask.jsonify(db.get_watt_chart()) + +if __name__ == "__main__": + app.run(host = "0.0.0.0", port = int(os.environ["APP_PORT"]), debug = True)
\ No newline at end of file diff --git a/database.py b/database.py index 846da4d..59250d0 100644 --- a/database.py +++ b/database.py @@ -109,13 +109,37 @@ class PowerDatabase: with self.__connection.cursor() as cursor:
cursor.execute("INSERT INTO kwh_readings (host, reading) VALUES (%s, %s);", (host, reading))
+ def get_last_plug_readings(self):
+ 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()
+
+ readings = []
+ for host, datetime in plugtimes:
+ cursor.execute("SELECT host, datetime, reading FROM watt_readings WHERE host = %s AND datetime = %s;", (host, datetime))
+ readings.append(cursor.fetchone())
+ return readings
+
+ def get_watt_chart(self):
+ with self.__connection.cursor() as cursor:
+ cursor.execute("SELECT DISTINCT host FROM watt_readings;")
+ hosts = [i[0] for i in cursor.fetchall()]
+
+ out = {}
+ for host in hosts:
+ cursor.execute("SELECT datetime, reading FROM watt_readings WHERE host = %s ORDER BY datetime;", (host, ))
+ out[host] = cursor.fetchall()
+
+ return out
+
if __name__ == "__main__":
if not os.path.exists(".docker"):
import dotenv
- dotenv.load_dotenv(dotenv_path = "db.env")
+ dotenv.load_dotenv(dotenv_path = "power.env")
host = "srv.athome"
else:
host = None
with PowerDatabase(host = host) as db:
- print(db.get_tasmota_devices())
+ print(db.get_watt_chart())
diff --git a/db.env.example b/db.env.example deleted file mode 100644 index 3427fc5..0000000 --- a/db.env.example +++ /dev/null @@ -1,2 +0,0 @@ -MYSQL_ROOT_PASSWORD=**********
-TASMOTA_DEVICES=switch.plug:admin:***********,nas.plug:admin:***********,router.plug:admin:************
@@ -8,7 +8,7 @@ import os if not os.path.exists(os.path.join("/app", ".docker")): import dotenv - dotenv.load_dotenv(dotenv_path = "db.env") + dotenv.load_dotenv(dotenv_path = "power.env") HOST = "srv.athome" else: HOST = None diff --git a/docker-compose.yml b/docker-compose.yml index a387601..6907a00 100644 --- a/docker-compose.yml +++ b/docker-compose.yml @@ -7,4 +7,4 @@ services: dockerfile: cron_Dockerfile
image: jwansek/power
env_file:
- - ./db.env
+ - ./power.env
diff --git a/mikrotik.py b/mikrotik.py new file mode 100644 index 0000000..4cd9231 --- /dev/null +++ b/mikrotik.py @@ -0,0 +1,89 @@ +from dataclasses import dataclass, field +import threading +import serial +import devices +import time +import os +import re + +@dataclass +class MikroTikSerialDevice: + """This is a horrible, horrible way of doing this + pretty much anything else would be better, for example connecting + over SSH instead of serial + + Even using a serial connection like this is an abomination + Please seriously do not do this, this is some necromancy, like it doesn't + log out of the serial connection properly so make sure nothing else is plugged + into the switch serial port + + I am doing it this way because I do not understand mikrotik scripting + """ + device: str = os.environ["MIKROTIK_DEVICE"] + user: str = os.environ["MIKROTIK_USER"] + passwd: str = os.environ["MIKROTIK_PASS"] + + def __post_init__(self): + self.interfaces = {} + self.last_return = {} + 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) + if self.is_being_polled.is_set(): + fetched_cache = self.poe_cache[interface] + fetched_cache["cached"] = True + return fetched_cache + + self.is_being_polled.set() + self.ser = serial.Serial(self.device, int(os.environ["MIKROTIK_BAUD"]), timeout=0.25) + + if self.last_return == {}: + self._push_serial("") + self._push_serial(self.user) + self._push_serial(self.passwd) + self._push_serial("/interface/ethernet/poe/monitor %s" % interface) + time.sleep(0.05) + self.ser.write(bytes("q", 'ISO-8859-1')) + out = self._read() + self.ser.close() + self.is_being_polled.clear() + + return self._post_out(out, interface) + + def _push_serial(self, text): + time.sleep(0.05) + self.ser.write(bytes(text + "\r\n", 'ISO-8859-1')) + time.sleep(0.05) + + def _read(self): + return self.ser.readlines() + + def _post_out(self, out, interface, was_cached = False): + d = {} + for line in out: + line = line.decode().strip() + # print("line:", line) + if line.startswith("poe"): + d.__setitem__(*line.split(": ")) + + self.last_return = d + self.poe_cache[interface] = d + d["cached"] = was_cached + return d + + + + +if __name__ == "__main__": + if not os.path.exists(os.path.join("/app", ".docker")): + import dotenv + dotenv.load_dotenv(dotenv_path = "power.env") + + mikrotik = MikroTikSerialDevice() + for interface in mikrotik.interfaces: + print(interface, mikrotik.get_poe_info(interface)) + diff --git a/power.env.example b/power.env.example new file mode 100644 index 0000000..36cc9f2 --- /dev/null +++ b/power.env.example @@ -0,0 +1,10 @@ +MYSQL_ROOT_PASSWORD=************
+MYSQL_HOST=192.168.69.3
+TASMOTA_DEVICES=switch.plug:admin:=************,nas.plug:admin:=************,12vbrick.plug:admin:=************,backup.plug:admin:=************
+
+MIKROTIK_DEVICE=COM6
+MIKROTIK_USER=admin
+MIKROTIK_PASS==************
+MIKROTIK_INTERFACES=ether1:pfsense router,ether2:interface2,ether3:interface3,ether4:interface4,ether5:interface5,ether6:interface6,ether7:interface7,ether8:interface8
+
+APP_PORT = 5021
\ No newline at end of file diff --git a/requirements.txt b/requirements.txt index d15029f..c8fabfc 100644 --- a/requirements.txt +++ b/requirements.txt @@ -2,4 +2,5 @@ tasmotadevicecontroller==0.0.8 aiohttp==3.8.3 pymysql python-dotenv - +pyserial +flask |