Changes between Initial Version and Version 1 of Views

Timestamp:

08/31/25 23:53:06 (2 months ago)

Author:

222004

Comment:

--

Views

@@ +1 @@
+== **Погледи**
+----
+
+== 1) Поглед што дава детален overview за клиент:
+**Основни податоци\\
+Вкупни нарачки и сума на потрошено\\
+Број на успешни/неуспешни плаќања\\
+Лојалност (поени од clubcard)\\
+Проверка дали клиентот е верифициран\\
+Дали има регистрирани алергии\\
+Дали има активни рецепти\\**
+
+{{{
+
+CREATE OR REPLACE VIEW synergymed.v_client_full_activity_overview AS
+SELECT 
+    u.id AS user_id,
+    u.first_name || ' ' || u.last_name AS client_name,
+    u.e_mail,
+    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
+
+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;
+
+}}}
+----
+== 2) Поглед за движењето на лековите низ целиот supply-chain:
+**
+Кој дистрибутер испорачува**\\
+**Од лек и колку**\\
+**На која аптека**\\
+**Статус на нарачката**\\
+**Очекуван датум на пристигнување**\\
+**Последна залиха во магацин**\\
+
+{{{
+
+CREATE OR REPLACE VIEW synergymed.v_supply_chain_overview AS
+SELECT 
+    so.id AS supply_order_id,
+    d.company_id AS distributor_id,
+    cd.company_name AS distributor_name,
+    ph.company_id AS pharmacy_id,
+    cp.company_name AS pharmacy_name,
+    bm.id AS branded_medicine_id,
+    bm.name AS branded_medicine_name,
+    sobm.quantity AS ordered_quantity,
+    so.status AS order_status,
+    so.order_date,
+    so.expected_arrival_date,
+    
+    COALESCE(ibm.quantity, 0) AS last_known_inventory
+FROM synergymed.supplyorder so
+JOIN synergymed.distributor d ON d.company_id = so.distributor_id
+JOIN synergymed.company cd ON cd.id = d.company_id
+JOIN synergymed.pharmacy ph ON ph.company_id = so.pharmacy_id
+JOIN synergymed.company cp ON cp.id = ph.company_id
+JOIN synergymed.supplyorder_brandedmedicine sobm ON sobm.supply_order_id = so.id
+JOIN synergymed.brandedmedicine bm ON bm.id = sobm.branded_medicine_id
+LEFT JOIN synergymed.inventory i ON i.facility_id IN (
+    SELECT f.id FROM synergymed.facility f WHERE f.company_id = ph.company_id
+)
+LEFT JOIN synergymed.inventory_brandedmedicine ibm 
+       ON ibm.inventory_id = i.id AND ibm.branded_medicine_id = bm.id;
+
+
+}}}
+----
+== 3) Поглед што ги прикажува залихите на сите лекови во секоја аптека, со датум на последна промена.
+{{{
+
+CREATE OR REPLACE VIEW synergymed.v_inventory_status AS
+SELECT 
+    ph.company_id AS pharmacy_id,
+    phc.branded_medicine_id,
+    bm.name AS branded_name,
+    ibm.quantity,
+    ibm.last_changed
+FROM synergymed.inventory_brandedmedicine ibm
+JOIN synergymed.inventory i ON ibm.inventory_id = i.id
+JOIN synergymed.facility f ON i.facility_id = f.id
+JOIN synergymed.pharmacy ph ON ph.company_id = f.company_id
+JOIN synergymed.pharmacy_catalog phc ON phc.pharmacy_id = ph.company_id
+JOIN synergymed.brandedmedicine bm ON bm.id = phc.branded_medicine_id;
+
+}}}
+----
+== 4) Поглед што дава детална историја на нарачки за секој клиент, со информации за статус, цена и датум.
+{{{
+
+CREATE OR REPLACE VIEW synergymed.v_client_order_history AS
+SELECT 
+    co.id AS order_id,
+    u.first_name || ' ' || u.last_name AS client_name,
+    co.order_date,
+    co.expected_arrival_date,
+    co.status,
+    co.total_price,
+    pm.method_name AS payment_method
+FROM synergymed.clientorder co
+JOIN synergymed.client c ON co.client_id = c.user_id
+JOIN synergymed.users u ON c.user_id = u.id
+JOIN synergymed.payment p ON co.payment_id = p.id
+JOIN synergymed.paymentmethod pm ON p.payment_method_id = pm.id;
+
+}}}