Changes between Version 4 and Version 5 of App

Timestamp:

09/30/25 11:18:45 (2 weeks ago)

Author:

211561

Comment:

--

App

@@ -187 +187 @@
 }}}
 
-= Трансакции
+= Процедури
+
+== Креирај нарачка и резервира залиха од партија
+
+Примa влезни параметри ({{{buyer_id}}}, {{{batch_id}}}, {{{quantity}}}) и креира нарачка, ред во {{{ORDER_BATCHES}}} и автоматски пресметува {{{total_price}}}. Ако нема доволно {{{units_per_batch}}} враќа грешка и откажува.
+
+{{{
+CREATE PROCEDURE create_order_from_batch(IN p_buyer_id VARCHAR(50), IN p_batch_id VARCHAR(50), IN p_quantity INT)
+BEGIN
+DECLARE v_product_id VARCHAR(50);
+DECLARE v_price DECIMAL(18,2);
+DECLARE v_units INT;
+DECLARE v_total DECIMAL(18,2);
+DECLARE v_order_id VARCHAR(50);
+SELECT product_id, units_per_batch INTO v_product_id, v_units FROM BATCHES WHERE id = p_batch_id FOR UPDATE;
+IF v_units IS NULL THEN
+SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Batch not found';
+END IF;
+IF p_quantity > v_units THEN
+SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient units in batch';
+END IF;
+SELECT price INTO v_price FROM PRODUCTS WHERE id = v_product_id;
+SET v_total = v_price * p_quantity;
+SET v_order_id = CONCAT('o_',UUID());
+INSERT INTO ORDERS (id, date, status, estimated_delivery_date, buyer_id, receiver_id) VALUES (v_order_id, CURRENT_DATE, 'pending', DATE_ADD(CURRENT_DATE, INTERVAL 7 DAY), p_buyer_id, p_buyer_id);
+INSERT INTO ORDER_BATCHES (id, order_id, batch_id, quantity, price_per_unit, total_price, created_at, updated_at) VALUES (CONCAT('ob_',UUID()), v_order_id, p_batch_id, p_quantity, v_price, v_total, NOW(), NOW());
+UPDATE BATCHES SET units_per_batch = units_per_batch - p_quantity WHERE id = p_batch_id;
+END;
+}}}
+
+== Поврат на партија и креирање кредит нота
+
+Примa {{{order_batch_id}}} и {{{reason}}}; враќа количина во {{{BATCHES}}}, ажурира {{{ORDER_BATCHES}}} и креира негативен {{{PAYMENTS}}} запис како кредит.
+
+{{{
+CREATE PROCEDURE return_batch_and_credit(IN p_order_batch_id VARCHAR(50), IN p_reason VARCHAR(255))
+BEGIN
+DECLARE v_batch_id VARCHAR(50);
+DECLARE v_quantity INT;
+DECLARE v_total DECIMAL(18,2);
+DECLARE v_order_id VARCHAR(50);
+SELECT batch_id, quantity, total_price, order_id INTO v_batch_id, v_quantity, v_total, v_order_id FROM ORDER_BATCHES WHERE id = p_order_batch_id FOR UPDATE;
+IF v_batch_id IS NULL THEN
+SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Order batch not found';
+END IF;
+UPDATE BATCHES SET units_per_batch = units_per_batch + v_quantity WHERE id = v_batch_id;
+UPDATE ORDER_BATCHES SET quantity = 0, total_price = 0.00, updated_at = NOW() WHERE id = p_order_batch_id;
+INSERT INTO PAYMENTS (id, order_id, amount, currency, due_date, exchange_rate, payment_date, payment_method, payment_status) VALUES (CONCAT('pay_cr_',UUID()), v_order_id, -v_total, 'EUR', CURRENT_DATE, 1.00, CURRENT_DATE, 'credit_note', 'refunded');
+END;
+}}}
+
+== Автоматско консолидирање на нарачки за транспорт
+
+Примa {{{transport_id}}} и список на {{{order_ids}}} ({{{CSV}}}); поврзува секоја нарачка со транспорт и ажурира статус на {{{orders}}} на {{{'in_transit'}}}.
+
+{{{
+CREATE PROCEDURE consolidate_orders_to_transport(IN p_transport_id VARCHAR(50), IN p_order_ids TEXT)
+BEGIN
+DECLARE v_order_id VARCHAR(50);
+DECLARE v_pos INT DEFAULT 1;
+DECLARE v_len INT;
+DECLARE v_token VARCHAR(100);
+SET v_len = CHAR_LENGTH(p_order_ids);
+WHILE v_pos <= v_len DO
+SET v_token = TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(p_order_ids, ',', v_pos), ',', -1));
+IF v_token <> '' THEN
+UPDATE ORDERS SET transport_id = p_transport_id, status = 'in_transit' WHERE id = v_token;
+END IF;
+SET v_pos = v_pos + 1;
+IF v_pos > 1000 THEN LEAVE; END IF;
+END WHILE;
+END;
+}}}
+
+== Евиденција на увоз со конверзија на валута
+
+Примa {{{order_id}}} и {{{exchange_rate}}}; ажурира {{{PAYMENTS.exchange_rate}}} и пресметува {{{amount}}} во локална валута, поставува {{{payment_status}}} на {{{'pending'}}}.
+
+{{{
+CREATE PROCEDURE record_import_with_exchange(IN p_order_id VARCHAR(50), IN p_exchange_rate DECIMAL(12,4))
+BEGIN
+DECLARE v_amount DECIMAL(18,2);
+SELECT amount INTO v_amount FROM PAYMENTS WHERE order_id = p_order_id FOR UPDATE;
+IF v_amount IS NULL THEN
+SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Payment not found for order';
+END IF;
+UPDATE PAYMENTS SET exchange_rate = p_exchange_rate, payment_status = 'pending' WHERE order_id = p_order_id;
+END;
+}}}
+
+== Симулација на рок на траење и известување за истекување
+
+Примa {{{days_ahead}}} и враќа листа со {{{batch_id}}} и {{{days_until_expiry}}} за сите серии кои истекуваат во рок од {{{days_ahead}}}; дополнително ажурира поле {{{status}}} во {{{BATCHES}}}.
+
+{{{
+CREATE PROCEDURE upcoming_expirations(IN p_days_ahead INT)
+BEGIN
+SELECT id AS batch_id, DATEDIFF(expiration_date, CURRENT_DATE) AS days_until_expiry FROM BATCHES WHERE DATEDIFF(expiration_date, CURRENT_DATE) <= p_days_ahead;
+END;
+}}}
+
+= Погледи
+
+== {{{active_orders_overview}}}
+
+Преглед на активни (processing, pending, in_transit) нарачки со вкупна сума и број на различни партији.
+
+{{{
+CREATE VIEW active_orders_overview AS
+SELECT
+  o.id AS order_id,
+  o.date AS order_date,
+  o.status,
+  o.buyer_id,
+  o.receiver_id,
+  COALESCE(SUM(ob.total_price),0) AS total_order_value,
+  COUNT(DISTINCT ob.batch_id) AS batches_count
+FROM ORDERS o
+LEFT JOIN ORDER_BATCHES ob ON ob.order_id = o.id
+WHERE o.status IN ('processing','pending','in_transit')
+GROUP BY o.id, o.date, o.status, o.buyer_id, o.receiver_id;
+}}}
+
+== {{{product_stock_summary}}}
+
+{{{
+CREATE VIEW product_stock_summary AS
+SELECT
+  p.id AS product_id,
+  p.name AS product_name,
+  p.unit_of_measure,
+  COALESCE(SUM(b.units_per_batch),0) AS total_units_available,
+  COALESCE(AVG(p.price),0) AS avg_price
+FROM PRODUCTS p
+LEFT JOIN BATCHES b ON b.product_id = p.id
+GROUP BY p.id, p.name, p.unit_of_measure;
+}}}
+
+== {{{client_financial_position}}}
+
+Финансиска состојба по клиент — вкупно фактурирано, платено и останат долг.
+
+{{{
+CREATE VIEW client_financial_position AS
+SELECT
+  c.id AS client_id,
+  c.name AS client_name,
+  COALESCE(SUM(pay.amount),0) AS total_invoiced_amount,
+  COALESCE(SUM(CASE WHEN pay.payment_status IN ('paid','refunded') THEN pay.amount ELSE 0 END),0) AS total_cleared_amount,
+  COALESCE(SUM(CASE WHEN pay.payment_status NOT IN ('paid','refunded') THEN pay.amount ELSE 0 END),0) AS outstanding_amount
+FROM CLIENTS c
+LEFT JOIN ORDERS o ON o.buyer_id = c.id
+LEFT JOIN PAYMENTS pay ON pay.order_id = o.id
+GROUP BY c.id, c.name;
+}}}
+
+== {{{batches_near_expiry}}}
+
+Список на партији кои истекуваат во следните 30 дена (може да се користи и со WHERE услов за други периоди).
+
+{{{
+CREATE VIEW batches_near_expiry AS
+SELECT
+  b.id AS batch_id,
+  b.batch_code,
+  b.product_id,
+  p.name AS product_name,
+  b.expiration_date,
+  DATEDIFF(b.expiration_date, CURRENT_DATE) AS days_until_expiry,
+  b.units_per_batch
+FROM BATCHES b
+LEFT JOIN PRODUCTS p ON p.id = b.product_id
+WHERE DATEDIFF(b.expiration_date, CURRENT_DATE) BETWEEN 0 AND 30;
+}}}
+
+== {{{transport_load_summary}}}
+
+Консолидиран преглед на транспортните единици со вкупен број на нарачки и вкупна тежина (приближно, користи net_weight * quantity).
+
+{{{
+CREATE VIEW transport_load_summary AS
+SELECT
+  t.id AS transport_id,
+  t.name AS transport_name,
+  t.departure_point,
+  t.arrival_point,
+  COUNT(DISTINCT o.id) AS orders_count,
+  COALESCE(SUM(b.net_weight * ob.quantity),0) AS approximate_total_net_weight
+FROM TRANSPORTS t
+LEFT JOIN ORDERS o ON o.transport_id = t.id
+LEFT JOIN ORDER_BATCHES ob ON ob.order_id = o.id
+LEFT JOIN BATCHES b ON b.id = ob.batch_id
+GROUP BY t.id, t.name, t.departure_point, t.arrival_point;
+}}}