Процедури

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 
  clientId INT;
  isVerified BOOLEAN;
  problems TEXT := '';
  orderItem RECORD;
  medComponent RECORD;
  activePrescription RECORD;
  totalStock INT;
BEGIN
  SELECT client_id INTO clientId
  FROM synergymed.clientorder
  WHERE id = p_order_id;

  IF clientId IS NULL THEN
    RAISE EXCEPTION 'Нема нарачка со id=%', p_order_id;
  END IF;

  SELECT is_verified INTO isVerified
  FROM synergymed.client
  WHERE user_id = clientId;

  IF COALESCE(isVerified, FALSE) = FALSE THEN
    UPDATE synergymed.clientorder
    SET status = 'неуспешно'
    WHERE id = p_order_id;
    RAISE EXCEPTION 'Клиентот % не е верифициран!', clientId;
  END IF;

  FOR orderItem IN
    SELECT cobm.branded_medicine_id, cobm.quantity
    FROM synergymed.clientorder_brandedmedicine cobm
    WHERE cobm.order_id = p_order_id
  LOOP
    FOR medComponent IN
      SELECT bmm.medicine_id
      FROM synergymed.branded_medicine_instanceof_medicine bmm
      WHERE bmm.branded_medicine_id = orderItem.branded_medicine_id
    LOOP
      FOR activePrescription IN
        SELECT pr.medicine_id
        FROM synergymed.prescription pr
        WHERE pr.client_id = clientId
          AND pr.valid_to >= CURRENT_DATE
      LOOP
        IF EXISTS (
          SELECT 1
          FROM synergymed.medicineinteraction mi
          WHERE (mi.medicine_id_1 = medComponent.medicine_id AND mi.medicine_id_2 = activePrescription.medicine_id)
             OR (mi.medicine_id_1 = activePrescription.medicine_id AND mi.medicine_id_2 = medComponent.medicine_id)
        ) THEN
          problems := problems || E'\n Интеракција помеѓу нов бренд лек ' || orderItem.branded_medicine_id || ' (компонента ' || medComponent.medicine_id
                 || ') и постоечки лек ' || activePrescription.medicine_id;
        END IF;
      END LOOP;
    END LOOP;

    SELECT COALESCE(SUM(ibm.quantity), 0) INTO totalStock
    FROM synergymed.inventory_brandedmedicine ibm
    WHERE ibm.branded_medicine_id = orderItem.branded_medicine_id;

    IF totalStock < orderItem.quantity THEN
      problems := problems || E'\n Недоволна залиха за бренд лек ' || orderItem.branded_medicine_id || ' (потребно: ' || orderItem.quantity
    || ', достапно: ' || totalStock || ')';
    END IF;
  END LOOP;

  IF problems <> '' THEN
    UPDATE synergymed.clientorder
    SET status = 'неуспешно'
    WHERE id = p_order_id;
    RAISE EXCEPTION 'Нарачката % има проблеми:%', p_order_id, problems;
  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;
$$;