Changes between Initial Version and Version 1 of DatabaseProgramming

Timestamp:

05/09/26 20:14:52 (2 weeks ago)

Author:

231020

Comment:

--

DatabaseProgramming

@@ +1 @@
+= Функции, процедури и тригери
+
+== Функции
+
+Функција 1: Проверка дали корисникот има доволно баланс за BUY
+
+{{{
+CREATE OR REPLACE FUNCTION check_portfolio_balance(
+    p_portfolio_id BIGINT,
+    p_price FLOAT8,
+    p_quantity INT
+)
+RETURNS BOOLEAN AS $$
+DECLARE
+    v_balance NUMERIC(18,2);
+    v_total FLOAT8;
+BEGIN
+    SELECT balance INTO v_balance
+    FROM portfolios
+    WHERE id = p_portfolio_id;
+
+    v_total := p_price * p_quantity;
+
+    IF v_balance >= v_total THEN
+        RETURN TRUE;
+    ELSE
+        RETURN FALSE;
+    END IF;
+END;
+$$ LANGUAGE plpgsql;
+}}}
+
+Функција 2: Вредност на портфолио за конкретен корисник
+
+{{{
+CREATE OR REPLACE FUNCTION get_portfolio_value(
+    p_user_id BIGINT
+)
+RETURNS FLOAT8 AS $$
+DECLARE
+    v_value FLOAT8;
+BEGIN
+    SELECT SUM(ph.quantity * s.current_price) INTO v_value
+    FROM portfolios p
+    JOIN portfolio_holdings ph ON p.id = ph.portfolio_id
+    JOIN stock s ON ph.stock_id = s.id
+    WHERE p.user_id = p_user_id;
+
+    RETURN COALESCE(v_value, 0);
+END;
+$$ LANGUAGE plpgsql;
+}}}
+
+
+== Процедури
+
+Процедура 1: Креирање на trade request од корисник
+
+{{{
+CREATE OR REPLACE PROCEDURE create_trade_request(
+    p_portfolio_id BIGINT,
+    p_price FLOAT8,
+    p_quantity INT,
+    p_stock_symbol VARCHAR(20),
+    p_type VARCHAR(10)
+)
+AS $$
+DECLARE
+    v_user_id BIGINT;
+BEGIN
+    SELECT user_id INTO v_user_id
+    FROM portfolios
+    WHERE id = p_portfolio_id;
+
+    IF p_type = 'BUY' THEN
+        IF NOT check_portfolio_balance(p_portfolio_id, p_price, p_quantity) THEN
+            RAISE EXCEPTION 'Недоволен баланс за купување. Portfolio ID: %', p_portfolio_id;
+        END IF;
+    END IF;
+
+    INSERT INTO trade_request (
+        portfolio_id, price_per_unit, quantity,
+        status, stock_symbol, timestamp, type
+    )
+    VALUES (
+        p_portfolio_id, p_price, p_quantity,
+        'OPEN', p_stock_symbol, NOW(), p_type
+    );
+
+    RAISE NOTICE 'Trade request креиран за корисник % — % x % на %',
+        v_user_id, p_type, p_quantity, p_stock_symbol;
+END;
+$$ LANGUAGE plpgsql;
+}}}
+
+
+Процедура 2: Одобрување на trade request од admin - креира broker order и settlement
+
+
+{{{
+CREATE OR REPLACE PROCEDURE approve_trade_request(
+    p_trade_request_id BIGINT
+)
+AS $$
+DECLARE
+    v_tr trade_request%ROWTYPE;
+    v_portfolio portfolios%ROWTYPE;
+    v_broker_order_id BIGINT;
+    v_stock_id BIGINT;
+BEGIN
+
+    SELECT * INTO v_tr
+    FROM trade_request
+    WHERE id = p_trade_request_id AND status = 'OPEN';
+
+    IF NOT FOUND THEN
+        RAISE EXCEPTION 'Trade request % не постои или не е OPEN', p_trade_request_id;
+    END IF;
+
+    SELECT * INTO v_portfolio
+    FROM portfolios
+    WHERE id = v_tr.portfolio_id;
+
+
+    SELECT id INTO v_stock_id
+    FROM stock
+    WHERE symbol = v_tr.stock_symbol;
+
+
+    UPDATE trade_request
+    SET status = 'CLOSED'
+    WHERE id = p_trade_request_id;
+
+
+    INSERT INTO broker_order (
+        order_type, limit_price, quantity, status,
+        executed_price, broker_fee, mse_reference_number,
+        submitted_at, executed_at, trade_request_id
+    )
+    VALUES (
+        v_tr.type, v_tr.price_per_unit, v_tr.quantity, 'EXECUTED',
+        v_tr.price_per_unit, (random() * 50)::FLOAT8,
+        'MSE-' || p_trade_request_id,
+        v_tr.timestamp, NOW(), p_trade_request_id
+    )
+    RETURNING id INTO v_broker_order_id;
+
+
+    INSERT INTO settlement_record (
+        broker_order_id, user_id, stock_symbol, quantity,
+        settled_price, total_cost, settlement_status, settled_at
+    )
+    VALUES (
+        v_broker_order_id, v_portfolio.user_id, v_tr.stock_symbol,
+        v_tr.quantity, v_tr.price_per_unit,
+        v_tr.price_per_unit * v_tr.quantity,
+        'SETTLED', NOW()
+    );
+
+
+    INSERT INTO trade_transaction (
+        price, quantity, timestamp, type, origin, user_id, stock_id
+    )
+    VALUES (
+        v_tr.price_per_unit, v_tr.quantity, NOW(),
+        v_tr.type, 'INTERNAL', v_portfolio.user_id, v_stock_id
+    );
+
+    RAISE NOTICE 'Trade request % одобрен — Broker Order % креиран', 
+        p_trade_request_id, v_broker_order_id;
+END;
+$$ LANGUAGE plpgsql;
+}}}
+
+
+
+== Тригери
+
+Тригер 1: При SELL - провери дали корисникот има доволно акции во holdings
+
+
+{{{
+CREATE OR REPLACE FUNCTION check_holdings_before_sell()
+RETURNS TRIGGER AS $$
+DECLARE
+    v_stock_id BIGINT;
+    v_available_qty INT;
+BEGIN
+    IF NEW.type = 'SELL' THEN
+        SELECT id INTO v_stock_id
+        FROM stock WHERE symbol = NEW.stock_symbol;
+
+        SELECT quantity INTO v_available_qty
+        FROM portfolio_holdings
+        WHERE portfolio_id = NEW.portfolio_id
+          AND stock_id = v_stock_id;
+
+        IF NOT FOUND OR v_available_qty < NEW.quantity THEN
+            RAISE EXCEPTION 'Недоволно акции за продажба. Достапно: %, Побарано: %',
+                COALESCE(v_available_qty, 0), NEW.quantity;
+        END IF;
+    END IF;
+
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER trg_check_holdings_before_sell
+BEFORE INSERT ON trade_request
+FOR EACH ROW
+EXECUTE FUNCTION check_holdings_before_sell();
+}}}