Погледи
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;
Last modified
32 hours ago
Last modified on 08/31/25 23:53:06
Note:
See TracWiki
for help on using the wiki.