Изворен код

​GitHub линк

Индекси

USERS → email

Бидејќи полето email често ќе се користи за пребарување или автентикација, креирањето индекс на ова поле ќе ја забрза побарувачката:

CREATE INDEX idx_users_email ON USERS(email);

CLIENTS → name

Композитен индекс на name и country може да ја забрза побарувачката на клиенти што филтрираат или сортираат по овие колони:

CREATE INDEX idx_clients_name_country ON CLIENTS (name, country);

BATCHES → batch_code

Ако пребарувате по batch_code, креирање индекс на ова поле ќе ја забрза побарувачката кога се бараат специфични серии:

CREATE INDEX idx_batches_batch_code ON BATCHES (batch_code);

ORDERS → status

Креирање индекс на status во табелата ORDERS може да ја забрза побарувачката кога се пребаруваат нарачки според статусот (на пример, "pending", "completed").

CREATE INDEX idx_orders_status ON ORDERS (status);

PAYMENTS → payment_status

При често филтрирање на плаќања според статус, овој индекс ќе ја забрза побарувачката за информациите за плаќање:

CREATE INDEX idx_payments_payment_status ON PAYMENTS (payment_status);

Тригери

Негативни/нулти количини и цени во ORDER_BATCHES

Кога се извршува INSERT или UPDATE на ORDER_BATCHES, осигурај quantity > 0 и price_per_unit >= 0; пресметај total_price = quantity * price_per_unit и постави timestamps.

CREATE OR REPLACE FUNCTION trg_order_batches_validate() RETURNS trigger AS $
BEGIN
  IF NEW.quantity IS NULL OR NEW.quantity <= 0 THEN
    RAISE EXCEPTION 'quantity must be > 0';
  END IF;
  IF NEW.price_per_unit IS NULL OR NEW.price_per_unit < 0 THEN
    RAISE EXCEPTION 'price_per_unit must be >= 0';
  END IF;
  NEW.total_price := NEW.quantity * NEW.price_per_unit;
  NEW.updated_at := NOW();
  IF TG_OP = 'INSERT' THEN
    NEW.created_at := NOW();
  END IF;
  RETURN NEW;
END;
$ LANGUAGE plpgsql;

CREATE TRIGGER order_batches_validate
BEFORE INSERT OR UPDATE ON order_batches
FOR EACH ROW EXECUTE FUNCTION trg_order_batches_validate();

Нарачки со истечени серии

Пред INSERT или UPDATE на ORDER_BATCHES, провери дали повиканата BATCH не е истечена според датумот на нарачката (orders.date). Се фрла exception ако expiration_date < orders.date.

CREATE OR REPLACE FUNCTION trg_order_batches_no_expired() RETURNS trigger AS $
DECLARE
  ord_date DATE;
  batch_exp DATE;
BEGIN
  SELECT date INTO ord_date FROM orders WHERE id = NEW.order_id;
  SELECT expiration_date INTO batch_exp FROM batches WHERE id = NEW.batch_id;
  IF ord_date IS NULL OR batch_exp IS NULL THEN
    RAISE EXCEPTION 'order or batch not found';
  END IF;
  IF batch_exp < ord_date THEN
    RAISE EXCEPTION 'cannot add expired batch to order (batch % expired %)', NEW.batch_id, batch_exp;
  END IF;
  RETURN NEW;
END;
$ LANGUAGE plpgsql;

CREATE TRIGGER order_batches_no_expired
BEFORE INSERT OR UPDATE ON order_batches
FOR EACH ROW EXECUTE FUNCTION trg_order_batches_no_expired();

Бришење на PRODUCERS/PRODUCTS/BATCHES кои се реферирани

Забрането бришење на:

• producer ако постојат products
• product ако постојат batches
• batch ако е искористен во order_batches.

CREATE OR REPLACE FUNCTION trg_prevent_fk_deletes() RETURNS trigger AS $
BEGIN
  IF TG_TABLE_NAME = 'producers' THEN
    IF EXISTS (SELECT 1 FROM products WHERE producer_id = OLD.id) THEN
      RAISE EXCEPTION 'cannot delete producer with products';
    END IF;
  ELSIF TG_TABLE_NAME = 'products' THEN
    IF EXISTS (SELECT 1 FROM batches WHERE product_id = OLD.id) THEN
      RAISE EXCEPTION 'cannot delete product with batches';
    END IF;
  ELSIF TG_TABLE_NAME = 'batches' THEN
    IF EXISTS (SELECT 1 FROM order_batches WHERE batch_id = OLD.id) THEN
      RAISE EXCEPTION 'cannot delete batch referenced in orders';
    END IF;
  END IF;
  RETURN OLD;
END;
$ LANGUAGE plpgsql;

CREATE TRIGGER prevent_delete_producers
BEFORE DELETE ON producers
FOR EACH ROW EXECUTE FUNCTION trg_prevent_fk_deletes();

CREATE TRIGGER prevent_delete_products
BEFORE DELETE ON products
FOR EACH ROW EXECUTE FUNCTION trg_prevent_fk_deletes();

CREATE TRIGGER prevent_delete_batches
BEFORE DELETE ON batches
FOR EACH ROW EXECUTE FUNCTION trg_prevent_fk_deletes();

Автоматско ажурирање на ORDER.status според плаќања

Кога PAYMENT се означува како paid (payment_status = 'paid' и payment_date не е NULL), провери SUM(payments.amount) >= orders.total_amount, а потоа постави orders.status = 'paid'.

CREATE OR REPLACE FUNCTION trg_payments_update_order_status() RETURNS trigger AS $
DECLARE
  paid_sum NUMERIC;
  ord_total NUMERIC;
BEGIN
  IF TG_OP = 'DELETE' THEN
    RETURN OLD;
  END IF;

  SELECT COALESCE(SUM(amount),0) INTO paid_sum FROM payments WHERE order_id = NEW.order_id AND payment_status = 'paid';
  SELECT COALESCE(total_amount,0) INTO ord_total FROM orders WHERE id = NEW.order_id;

  IF paid_sum >= ord_total AND ord_total > 0 THEN
    UPDATE orders SET status = 'paid' WHERE id = NEW.order_id;
  END IF;
  RETURN NEW;
END;
$ LANGUAGE plpgsql;

CREATE TRIGGER payments_update_order_status
AFTER INSERT OR UPDATE ON payments
FOR EACH ROW EXECUTE FUNCTION trg_payments_update_order_status();

Известувања за промена на статус на нарачка

Известување кога нарачката го променила статусот (ORDER.status) — испраќа RAISE NOTICE со ордедена порака

CREATE OR REPLACE FUNCTION trg_order_status_change_fun()
RETURNS trigger AS $
BEGIN
  IF TG_OP = 'UPDATE' AND NEW.status IS DISTINCT FROM OLD.status THEN
    RAISE NOTICE 'Order % changed it's status: % → %', NEW.id, OLD.status, NEW.status;
    IF NEW.status = 'shipped' OR NEW.status = 'delivered' THEN
      RAISE NOTICE 'Good news! Order % is %!', NEW.id, NEW.status;
    END IF;
  END IF;
  RETURN NEW;
END;
$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_order_status_change ON orders;
CREATE TRIGGER trg_order_status_change
AFTER UPDATE ON orders
FOR EACH ROW
WHEN (OLD.status IS DISTINCT FROM NEW.status)
EXECUTE FUNCTION trg_order_status_change_fun();

Процедури

Креирај нарачка и резервира залиха од серија

Прим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;

Трансакции

Автоматски се извршуваат во апликацијата.