From c9b6b9a5ca082fe7c1b6f58d7713f785a9eb6a5c Mon Sep 17 00:00:00 2001
From: Martial Simon <msimon_fr@hotmail.com>
Date: Mon, 15 Sep 2025 01:08:27 +0200
Subject: add: graphs et rushs

---
 graphs/sql/maas/req03.sql | 47 +++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 47 insertions(+)
 create mode 100644 graphs/sql/maas/req03.sql

(limited to 'graphs/sql/maas/req03.sql')

diff --git a/graphs/sql/maas/req03.sql b/graphs/sql/maas/req03.sql
new file mode 100644
index 0000000..9b724cc
--- /dev/null
+++ b/graphs/sql/maas/req03.sql
@@ -0,0 +1,47 @@
+CREATE VIEW memorin.zoned_devices_logs AS
+    SELECT
+    device_serial,
+    version_id,
+    zone_id,
+    latitude AS lat,
+    longitude AS long,
+    temperature AS temp,
+    battery_percentage AS battery,
+    signal_strength
+    FROM
+    memorin.device_logs AS logs
+    WHERE
+    device_serial IN (
+        SELECT serial_number
+        FROM memorin.devices
+        WHERE deactivated_at IS NULL
+    )
+    AND version_id IN (
+        SELECT id
+        FROM memorin.device_versions
+        WHERE id = logs.version_id
+    )
+    AND temperature BETWEEN 34 AND 45
+    AND signal_strength BETWEEN 0 AND 5
+    AND battery_percentage BETWEEN 0 AND 100
+    AND longitude BETWEEN (
+        SELECT min_longitude
+        FROM memorin.geographic_zones
+        WHERE id = logs.zone_id
+    )
+    AND (
+        SELECT max_longitude
+        FROM memorin.geographic_zones
+        WHERE id = logs.zone_id
+    )
+    AND latitude BETWEEN (
+        SELECT min_latitude
+        FROM memorin.geographic_zones
+        WHERE id = logs.zone_id
+    )
+    AND (
+        SELECT max_latitude
+        FROM memorin.geographic_zones
+        WHERE id = logs.zone_id
+    )
+    WITH CHECK OPTION;
-- 
cgit v1.2.3

