Changes between Version 2 and Version 3 of Procedures

Timestamp:

09/04/25 04:48:22 (8 weeks ago)

Author:

222004

Comment:

--

Procedures

@@ -39 +39 @@
 AS $$
 DECLARE 
-  v_client_id INT;
-  v_verified BOOLEAN;
-  v_problem TEXT := '';
-  v_item RECORD;
-  v_active_pres RECORD;
+  clientId INT;
+  isVerified BOOLEAN;
+  problems TEXT := '';
+  orderItem RECORD;
+  medComponent RECORD;
+  activePrescription RECORD;
+  totalStock INT;
 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;
+  SELECT client_id INTO clientId
+  FROM synergymed.clientorder
+  WHERE id = p_order_id;
 
-  IF NOT v_verified THEN
-    RAISE EXCEPTION 'Клиентот % не е верифициран!', v_client_id;
+  IF clientId IS NULL THEN
+    RAISE EXCEPTION 'Нема нарачка со id=%', p_order_id;
   END IF;
 
-  FOR v_item IN
-    SELECT cobm.branded_medicine_id, cobm.quantity, bm.medicine_id
+  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
-    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
+    FOR medComponent IN
+      SELECT bmm.medicine_id
+      FROM synergymed.branded_medicine_instanceof_medicine bmm
+      WHERE bmm.branded_medicine_id = orderItem.branded_medicine_id
     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;
+      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;
-        
-    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;
+
+    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 v_problem <> '' THEN
-    RAISE EXCEPTION 'Нарачката % има проблеми: %', p_order_id, v_problem;
+  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 = 'потврдено'
+    SET status = 'во тек'
     WHERE id = p_order_id;
-    RAISE NOTICE 'Нарачката % е успешно потврдена.', p_order_id;
+    RAISE NOTICE 'Нарачката % е успешно потврдена и е во тек.', p_order_id;
   END IF;
 END;
 $$;
+
 }}}