Changes between Version 3 and Version 4 of DatabaseProgramming

Timestamp:

06/07/26 23:24:04 (3 days ago)

Author:

231020

Comment:

--

DatabaseProgramming

@@ -3 +3 @@
 == Функции
 
-Функција 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;
+Функција 1: Ажурира и враќа моменталната добивка/загуба на CFD позицијата врз основа на најновата цена на акциите.
+
+{{{
+CREATE OR REPLACE FUNCTION calculate_cfd_profit_loss(p_cfd_id BIGINT)
+RETURNS FLOAT8
+AS $$
+DECLARE
+    v_quantity INT;
+    v_open_price FLOAT8;
+    v_current_price FLOAT8;
+    v_profit_loss FLOAT8;
+BEGIN
+    SELECT c.quantity,
+           c.open_price,
+           s.current_price
+    INTO v_quantity,
+         v_open_price,
+         v_current_price
+    FROM CFD_position c
+    JOIN stock s ON c.stock_id = s.id
+    WHERE c.id = p_cfd_id;
+
+    v_profit_loss :=
+        (v_current_price - v_open_price) * v_quantity;
+
+    UPDATE CFD_position
+    SET profit_loss = v_profit_loss
+    WHERE id = p_cfd_id;
+
+    RETURN v_profit_loss;
 END;
 $$ LANGUAGE plpgsql;
@@ -52 +58 @@
 }}}
 
+Функција 3: Проверка дали корисникот има доволно баланс за 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;
+}}}
+
 
 
@@ -58 +92 @@
 == Процедури
 
-Процедура 1: Креирање на trade request од корисник
+Процедура 1: Одобрување на 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;
+}}}
+
+
+
+Процедура 2: Креирање на trade request од корисник
 
 {{{
@@ -98 +212 @@
 
 
-Процедура 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();
-}}}
-
-
-Тригер 2: цената треба автоматски да се менува кога ќе се случи нова трансакција
+Тригер 1: Цената треба автоматски да се менува кога ќе се случи нова трансакција
 
 {{{
@@ -247 +246 @@
 
 
+Тригер 2: При 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();
+}}}
+
+
+
+