"Paribet"

It`s one of my student django projects. Site created for bets on sports, in this project I've used PostgreSQL 9.5.
This site provides you with opportunities such as:

• Register, login;
• Match viewing;
• Filter by kind of sport or leagues;
• Place a bet;
• Account balance replenishment and view operations;

Percents and coefficients of any match, calculating in the DB View and it also has a few tgiggers, code below:

---

View to calculate percents and coefficients of matches

Create view match_koef as (
with
asd as(select distinct match_id, bet_team_id, count(bet_team_id) over(partition by match_id, bet_team_id) c from bet_bet),
dsa as (SELECT match_id, bet_team_id, c , sum(c) over(partition by match_id) s from asd),
sda as (SELECT match_id, bet_team_id, (c/s)*100 perc from dsa)
SELECT match_id, bet_team_id, round(perc,1) perc, round(100/perc,2) koef from sda)

---

Trigger to give money to everyone who betted on the winners team

CREATE OR REPLACE FUNCTION match_rewards_F() RETURNS TRIGGER
AS $$ DECLARE
win_koef match_koef.koef%TYPE; m_id bet_bet.match_id%TYPE; b_team_id bet_bet.bet_team_id%TYPE; acc_id bet_bet.account_number_id%TYPE; b_amount bet_bet.bet_amount%TYPE; BEGIN

Select koef into win_koef from match_koef where match_koef.match_id = New.match_id and match_koef.bet_team_id = New.team_id;

FOR m_id, b_team_id, acc_id, b_amount in SELECT bet_bet.match_id, bet_team_id, account_number_id, bet_amount FROM bet_bet where bet_bet.match_id = New.match_id and bet_bet.bet_team_id = New.team_id
LOOP
UPDATE bet_player SET balance = balance + (b_amountwin_koef) WHERE account_number_id = acc_id;
INSERT INTO bet_operation (account_number_id, date, card, operation_type, transaction_amount) VALUES (acc_id, now(), ('Выиграш по матчу №' || m_id), 'Зачисление', (b_amountwin_koef));

END LOOP;
RETURN NEW;
END;
$$ LANGUAGE plpgSQL;
CREATE TRIGGER match_rewards AFTER INSERT ON bet_winner
FOR EACH ROW EXECUTE PROCEDURE match_rewards_F();

---

Trigger to give money to everyone who betted on the draw

CREATE OR REPLACE FUNCTION match_draw_rewards_F() RETURNS TRIGGER
AS $$ DECLARE
draw_koef match_koef.koef%TYPE; m_id bet_bet.match_id%TYPE; b_team_id bet_bet.bet_team_id%TYPE; acc_id bet_bet.account_number_id%TYPE; b_amount bet_bet.bet_amount%TYPE; BEGIN

Select koef into draw_koef from match_koef where match_koef.match_id = New.match_id and match_koef.bet_team_id = 7;

FOR m_id, b_team_id, acc_id, b_amount in SELECT bet_bet.match_id, bet_team_id, account_number_id, bet_amount FROM bet_bet where bet_bet.match_id = New.match_id and bet_bet.bet_team_id = 7
LOOP
UPDATE bet_player SET balance = balance + (b_amountdraw_koef) WHERE account_number_id = acc_id;
INSERT INTO bet_operation (account_number_id, date, card, operation_type, transaction_amount) VALUES (acc_id, now(), ('Выиграш по матчу №' || m_id), 'Зачисление', (b_amountdraw_koef));
END LOOP;
RETURN NEW;
END;
$$ LANGUAGE plpgSQL;
CREATE TRIGGER match_draw_rewards AFTER INSERT ON bet_draw
FOR EACH ROW EXECUTE PROCEDURE match_draw_rewards_F();

---

Trigger to check if match already has an result

CREATE OR REPLACE FUNCTION match_result_F() RETURNS TRIGGER
AS $$ BEGIN

IF (EXISTS(select match_id from bet_draw where bet_draw.match_id = NEW.match_id) OR EXISTS(select match_id from bet_winner where bet_winner.match_id = NEW.match_id)) THEN
RAISE EXCEPTION 'This match already have result!';
ELSE RETURN NEW;
END IF;
END;
$$ LANGUAGE plpgSQL;
CREATE TRIGGER match_result_d BEFORE INSERT ON bet_draw
FOR EACH ROW EXECUTE PROCEDURE match_result_F();
CREATE TRIGGER match_result_w BEFORE INSERT ON bet_winner
FOR EACH ROW EXECUTE PROCEDURE match_result_F();