Differences

This shows you the differences between two versions of the page.

--- en:cold_chain:dashboard [2022/05/26 03:48] – created Kat
+++ en:cold_chain:dashboard [2022/05/31 04:37] (current) – Add telegram bot section Kat
@@ Line 1: / Line 1: @@
 ====== Dashboard ======
-These queries generally need to be set up by an administrator but have been included here as a reference and to provide some ideas of what kinds of data we can show on the [[https://wiki.msupply.foundation/en:dashboard|mSupply Dashboard]].
+These queries generally need to be set up by an administrator but have been included here as a reference to demonstrate some of the mSupply cold chain data that can be made available on the [[https://wiki.msupply.foundation/en:dashboard|mSupply Dashboard]].
 ===== Sensor Graph =====
 {{ :en:cold_chain:pasted:20220526-034519.png?600 | }}
+==== Query ====
+<code>
+WITH temp AS (
+SELECT
+  s.name,
+  CONCAT(TO_CHAR(date,'YYYY-MM-DD'),' ', TO_CHAR(time,'HH24:MI:SS'))::timestamptz AS log_datetime,
+  tl.temperature
+FROM temperature_log tl
+JOIN sensor s ON tl.sensor_id = s.id
+WHERE
+  CONCAT(TO_CHAR(date,'YYYY-MM-DD'),' ', TO_CHAR(time,'HH24:MI:SS'))::timestamptz >= $__timeFrom()
+)
+SELECT
+  $__time(log_datetime),
+  name,
+  temperature
+FROM temp
+ORDER BY log_datetime
+</code>
 ===== Unacknowledged Breaches =====
 {{ :en:cold_chain:pasted:20220526-034712.png?600 | }}
+==== Query ====
+<code>
+WITH breaches AS (
+SELECT
+        store_id,
+        location_id,
+        sensor_id,
+        s.name,
+        SUM(CASE WHEN type = 'HOT_CONSECUTIVE' or type = 'COLD_CONSECUTIVE' then 1 else 0 end) AS count_consecutive
+    FROM temperature_breach
+    JOIN sensor s ON s.id = temperature_breach.sensor_id
+    WHERE CONCAT(TO_CHAR(start_date,'YYYY-MM-DD'),' ', TO_CHAR(start_time,'HH24:MI:SS'))::timestamp >= $__timeFrom()
+    AND acknowledged='false'
+    GROUP BY store_id, location_id,sensor_id, s.name)
+SELECT COUNT(*) FROM breaches
+</code>
 ===== Batches affected =====
 {{ :en:cold_chain:pasted:20220526-034728.png?600 }}
+==== Query ====
+<code>
+WITH batch_count AS (
+SELECT
+        temperature_breach.store_id,
+        temperature_breach.location_id,
+        sensor_id,
+        s.name,
+        il.id,
+        SUM(CASE WHEN type = 'HOT_CONSECUTIVE' or type = 'COLD_CONSECUTIVE' then 1 else 0 end) AS count_consecutive
+    FROM temperature_breach
+    JOIN sensor s ON s.id = temperature_breach.sensor_id
+    JOIN item_line il ON il.location_id = temperature_breach.location_id
+    WHERE CONCAT(TO_CHAR(start_date,'YYYY-MM-DD'),' ', TO_CHAR(start_time,'HH24:MI:SS'))::timestamp >= $__timeFrom()
+    AND acknowledged='false'
+    GROUP BY temperature_breach.store_id, temperature_breach.location_id,sensor_id, s.name, il.id
+)
+SELECT COUNT(*) FROM batch_count
+</code>
 ===== Sensor battery life =====
 {{ :en:cold_chain:pasted:20220526-034747.png?600 }}
+==== Query ====
+<code>
+select
+sum(CASE WHEN batterylevel < 20 then 1 else 0 end) as "Low battery",
+sum(CASE WHEN batterylevel < 50 then 1 else 0 end) as "Battery to be replaced within month",
+sum(CASE WHEN batterylevel >= 50 then 1 else 0 end) as "Battery OK"
+from sensor s
+where s.is_active = true
+</code>
+===== Telegram notifications for Cold Chain =====
+General instructions are here: https://gist.github.com/ilap/cb6d512694c3e4f2427f85e4caec8ad7
+But note that you need to invite your bot, not the bot father to the channel
+Temperature logs rely on the alternate server in mSupply Desktop to be synced from the tablet to the server. If the alternate server is not running then no logs will be synced (and hence nothing will show on the Dashboard).
+Can see if the alternate server process is really running by checking port 8081 in Windows cmd with netstat -ano | findstr :8081
+In this case process with ID 2188 is running ok.
+{{ :en:cold_chain:pasted:20220531-043624.png?800 | }}