Процедури

1) Генерирање на месечен извештај за промет по аптека

Оваа процедура создава месечен извештај за приходот на сите аптеки. Се користи кога менаџментот сака да следи колку приход остварила секоја аптека во даден месец.
Практична за финансиска анализа.

CREATE OR REPLACE PROCEDURE synergymed.sp_monthly_pharmacy_revenue(p_year INT, p_month INT)
LANGUAGE plpgsql
AS $$
BEGIN
  RAISE NOTICE 'Извештај за промет за %-%:', p_month, p_year;

  SELECT ph.company_id AS pharmacy_id,
         c.company_name,
         SUM(cobm.quantity * bm.price) AS total_revenue
  FROM synergymed.clientorder co
  JOIN synergymed.clientorder_brandedmedicine cobm ON cobm.order_id = co.id
  JOIN synergymed.brandedmedicine bm ON bm.id = cobm.branded_medicine_id
  JOIN synergymed.deliverycompany dc ON dc.company_id = co.delivery_company_id
  JOIN synergymed.pharmacy ph ON ph.company_id = dc.company_id
  JOIN synergymed.company c ON c.id = ph.company_id
  WHERE EXTRACT(YEAR FROM co.order_date) = p_year
    AND EXTRACT(MONTH FROM co.order_date) = p_month
  GROUP BY ph.company_id, c.company_name;
END;
$$;

---

2) Проверка на ризици при нова нарачка

Кога клиент прави нова нарачка, пред таа да се потврди, системот прави комплетна проверка – дали клиентот е верифициран, дали некој од лековите има опасни интеракции со постојните рецепти на клиентот, и дали залихата е доволна. Ако има проблем, процедурата враќа предупредување; ако е сè во ред, ја потврдува нарачката.
Ова е корисно за да се спречат нелегални купувања, медицински ризици и грешки.

CREATE OR REPLACE PROCEDURE synergymed.sp_validate_new_order(p_order_id INT)
LANGUAGE plpgsql
AS $$
DECLARE 
  v_client_id INT;
  v_verified BOOLEAN;
  v_problem TEXT := '';
  v_item RECORD;
  v_active_pres RECORD;
BEGIN
  SELECT client_id INTO v_client_id FROM synergymed.clientorder WHERE id = p_order_id;
  SELECT is_verified INTO v_verified FROM synergymed.client WHERE user_id = v_client_id;

  IF NOT v_verified THEN
    RAISE EXCEPTION 'Клиентот % не е верифициран!', v_client_id;
  END IF;

  FOR v_item IN
    SELECT cobm.branded_medicine_id, cobm.quantity, bm.medicine_id
    FROM synergymed.clientorder_brandedmedicine cobm
    JOIN synergymed.brandedmedicine bm ON bm.id = cobm.branded_medicine_id
    WHERE cobm.order_id = p_order_id
  LOOP
    FOR v_active_pres IN
      SELECT pr.medicine_id
      FROM synergymed.prescription pr
      WHERE pr.client_id = v_client_id AND pr.valid_to >= CURRENT_DATE
    LOOP
      IF EXISTS (
        SELECT 1 
        FROM synergymed.medicineinteraction mi
        WHERE (mi.medicine_id_1, mi.medicine_id_2) IN (
                (v_item.medicine_id, v_active_pres.medicine_id), 
                (v_active_pres.medicine_id, v_item.medicine_id)
              )
      ) THEN
        v_problem := v_problem || E'\n Интеракција помеѓу нов лек ' || v_item.branded_medicine_id || ' и постоечки лек ' || v_active_pres.medicine_id;
      END IF;
    END LOOP;
	
    IF EXISTS (
      SELECT 1
      FROM synergymed.inventory_brandedmedicine ibm
      WHERE ibm.branded_medicine_id = v_item.branded_medicine_id
      GROUP BY ibm.branded_medicine_id
      HAVING SUM(ibm.quantity) < v_item.quantity
    ) THEN
      v_problem := v_problem || E'\n Недоволна залиха за лек ' || v_item.branded_medicine_id;
    END IF;
  END LOOP;

  IF v_problem <> '' THEN
    RAISE EXCEPTION 'Нарачката % има проблеми: %', p_order_id, v_problem;
  ELSE
    UPDATE synergymed.clientorder
    SET status = 'потврдено'
    WHERE id = p_order_id;
    RAISE NOTICE 'Нарачката % е успешно потврдена.', p_order_id;
  END IF;
END;
$$;

---

3) Извештај за истекување на рецепти

Оваа процедура излистува рецепти што ќе истечат во одреден временски период (пример 7 дена). Корисна е за аптеки и лекари за да навремено известат пациенти.
Ова е реално сценарио кое се користи во здравствени системи.

CREATE OR REPLACE PROCEDURE synergymed.sp_expiring_prescriptions(p_days_ahead INT)
LANGUAGE plpgsql
AS $$
BEGIN
  RAISE NOTICE 'Рецепти што истекуваат во наредните % дена:', p_days_ahead;

  SELECT p.id, u.first_name || ' ' || u.last_name AS client_name, p.valid_to
  FROM synergymed.prescription p
  JOIN synergymed.client c ON c.user_id = p.client_id
  JOIN synergymed.users u ON u.id = c.user_id
  WHERE p.valid_to <= CURRENT_DATE + p_days_ahead;
END;
$$;

---

4) Проверка на интеракции при препишување лекови

Оваа процедура проверува дали ново препишаниот лек ќе има интеракции со веќе активни рецепти на клиентот. Корисна е за фармацевти и лекари за да се избегнат опасни комбинации на лекови.
При издавање нов рецепт, системот веднаш предупредува ако постои ризик.

CREATE OR REPLACE PROCEDURE synergymed.sp_check_prescription_interactions(p_client_id INT, p_new_medicine_id INT)
LANGUAGE plpgsql
AS $$
BEGIN
  RAISE NOTICE 'Проверка на интеракции за клиент % со лек %', p_client_id, p_new_medicine_id;

  SELECT DISTINCT m1.medicine_name AS existing_medicine,
                  m2.medicine_name AS new_medicine,
                  mi.type,
                  mi.severity,
                  mi.description
  FROM synergymed.prescription pr
  JOIN synergymed.medicine m1 ON m1.id = pr.medicine_id
  JOIN synergymed.medicineinteraction mi 
       ON (mi.medicine_id_1 = m1.id AND mi.medicine_id_2 = p_new_medicine_id)
       OR (mi.medicine_id_2 = m1.id AND mi.medicine_id_1 = p_new_medicine_id)
  JOIN synergymed.medicine m2 ON m2.id = p_new_medicine_id
  WHERE pr.client_id = p_client_id
    AND pr.valid_to >= CURRENT_DATE;
END;
$$;