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 mSupply Dashboard.

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

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

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

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