Changes between Version 1 and Version 2 of Views

Timestamp:

09/15/25 04:11:35 (5 days ago)

Author:

222004

Comment:

--

Views

@@ -14 +14 @@
 
 CREATE OR REPLACE VIEW synergymed.v_client_full_activity_overview AS
+WITH order_summary AS (
+    SELECT co.client_id,
+           COUNT(co.id) AS total_orders,
+           COALESCE(SUM(co.total_price), 0) AS total_spent
+    FROM synergymed.clientorder co
+    GROUP BY co.client_id
+),
+payment_summary AS (
+    SELECT p.client_id,
+           COUNT(*) FILTER (WHERE p.status = 'завршено') AS successful_payments,
+           COUNT(*) FILTER (WHERE p.status = 'неуспешно') AS failed_payments,
+           MAX(p.payment_date) AS last_payment_date
+    FROM synergymed.payment p
+    GROUP BY p.client_id
+),
+allergy_clients AS (
+    SELECT DISTINCT hp.client_id
+    FROM synergymed.healthprofile hp
+    JOIN synergymed.allergicreaction_healthprofile_medicine ahm 
+         ON ahm.health_profile_id = hp.id
+),
+prescription_clients AS (
+    SELECT DISTINCT pr.client_id
+    FROM synergymed.prescription pr
+    WHERE pr.valid_to >= CURRENT_DATE
+)
 SELECT 
     u.id AS user_id,
@@ -20 +46 @@
     c.is_verified,
     COALESCE(cc.points, 0) AS loyalty_points,
-    
-    -- narachki
-    COUNT(DISTINCT co.id) AS total_orders,
-    COALESCE(SUM(co.total_price), 0) AS total_spent,
-    
-    -- plakjanja
-    COUNT(DISTINCT p.id) FILTER (WHERE p.status = 'завршено') AS successful_payments,
-    COUNT(DISTINCT p.id) FILTER (WHERE p.status = 'неуспешно') AS failed_payments,
-    MAX(p.payment_date) AS last_payment_date,
-    
-    -- alergii
-    CASE 
-        WHEN EXISTS (
-            SELECT 1 FROM synergymed.healthprofile hp
-            JOIN synergymed.allergicreaction_healthprofile_medicine ahm ON ahm.health_profile_id = hp.id
-            WHERE hp.client_id = c.user_id
-        ) THEN TRUE ELSE FALSE
-    END AS has_allergies,
-    
-    -- recepti
-    CASE 
-        WHEN EXISTS (
-            SELECT 1 FROM synergymed.prescription pr
-            WHERE pr.client_id = c.user_id
-              AND pr.valid_to >= CURRENT_DATE
-        ) THEN TRUE ELSE FALSE
-    END AS has_active_prescriptions
+
+    COALESCE(order_summary.total_orders, 0) AS total_orders,
+    COALESCE(order_summary.total_spent, 0) AS total_spent,
+
+    COALESCE(payment_summary.successful_payments, 0) AS successful_payments,
+    COALESCE(payment_summary.failed_payments, 0) AS failed_payments,
+    payment_summary.last_payment_date,
+
+    (allergy_clients.client_id IS NOT NULL) AS has_allergies,
+    (prescription_clients.client_id IS NOT NULL) AS has_active_prescriptions
 
 FROM synergymed.client c
 JOIN synergymed.users u ON u.id = c.user_id
 LEFT JOIN synergymed.clubcard cc ON cc.user_id = c.user_id
-LEFT JOIN synergymed.clientorder co ON co.client_id = c.user_id
-LEFT JOIN synergymed.payment p ON p.client_id = c.user_id
-GROUP BY u.id, client_name, u.e_mail, c.is_verified, cc.points;
+LEFT JOIN order_summary ON order_summary.client_id = c.user_id
+LEFT JOIN payment_summary ON payment_summary.client_id = c.user_id
+LEFT JOIN allergy_clients ON allergy_clients.client_id = c.user_id
+LEFT JOIN prescription_clients ON prescription_clients.client_id = c.user_id;
 
 }}}