= Изворен код

[https://github.com/dacc9/PharmaExport 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;
}}}

= Трансакции

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