Added plug usage table to web UI, with HTTP requests

12 files changed, 153 insertions, 100 deletions

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/<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/requirements.txt b/cron_requirements.txt
index c8fabfc..2c2bbc9 100644
--- a/requirements.txt
+++ b/cron_requirements.txt
@@ -3,4 +3,4 @@ aiohttp==3.8.3
 pymysql
 python-dotenv
 pyserial
-flask
+
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/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 @@
                 </li>
             </ul>
         </div>
+
+        <table id="power_table">
+            <tr id="header_row">
+                <td>Plug local hostname</td>
+                <td>Plug description</td>
+                <td>Current power usage (W)</td>
+                <td>Power usage yesterday (kWh)</td>
+            </tr>
+            {% for host, description_md in tasmota_devices %}
+                <tr id="tr_{{ host }}">
+                    <td><a href="http://{{ host }}" target="_blank">{{ host }}</a></td>
+                    <td>{{ description_md|safe }}</td>
+                    <td id="{{ host }}_watts_now"></td>
+                    <td id="{{ host }}_watts_yesterday"></td>
+                </tr>
+            {% endfor %}
+            <tr id="sum_row">
+                <td></td>
+                <td></td>
+                <td id="sum_watts_now"></td>
+                <td id="sum_watts_yesterday"></td>
+            </tr>
+        </table>
     </div>
 
+    <ul id="last_updated_ul">
+        <li id="watts_last_updated">Current power usage never updated</li>
+        <li id="kwh_last_updated">Yesterday's power usage never updated</li>
+        <li id="switch_last_updated">Switch power usage never updated</li>
+    </ul>
+
     <footer>
         <p><a href="https://github.com/jwansek/power.eda.gay">Source code released under GPLv3</a></p>
         <p><a href="https://www.fsf.org/campaigns/freejs">Read the Free Software Foundations statements about JavaScript</a></p>