SQLize Online / PHPize Online  /  SQLtest Online

CREATE OR REPLACE PROCEDURE recalculate_subscription_price(pgameid BIGINT, pold_stake_price BIGINT, pold_plus_price BIGINT, pnew_stake_price BIGINT, pnew_plus_price BIGINT, do_update BOOLEAN) LANGUAGE SQL BEGIN DECLARE sub CURSOR WITH RETURN FOR SELECT g.name AS main_game, g.iddggame AS gameid, s.iddgsubscription, t.wageramount AS old_wager_amount, b.modifier AS is_plus, COALESCE(b.picksystem, '0') AS picksystem, b.boardstake, COUNT(1) AS nr_of_boards, b.boardstake / pold_stake_price AS stakes, (pnew_stake_price * (b.boardstake / pold_stake_price)) AS new_board_stake, (pnew_stake_price + (CASE WHEN b.modifier = 'true' THEN pnew_plus_price ELSE 0 END)) * (b.boardstake / pold_stake_price) * COUNT(1) * 1 AS new_wager_amount FROM gis.DGSUBSCRIPTION S INNER JOIN gis.DGGAME G ON G.IDDGGAME = S.IDDGGAME INNER JOIN gis.DGBOARDSTACK BS ON BS.IDDGSUBSCRIPTION = S.IDDGSUBSCRIPTION INNER JOIN gis.DGBOARD B ON B.IDDGBOARDSTACK = BS.IDDGBOARDSTACK INNER JOIN gis.DGsubs_WAGER_TEMPLATE T ON T.IDDGSUBSCRIPTION = S.IDDGSUBSCRIPTION WHERE S.STATE = 'ACTIVE' AND S.IDDGGAME = pgameid GROUP BY G.IDDGGAME, G.NAME, S.IDDGSUBSCRIPTION, S.PLAYERID, T.wageramount, B.MODIFIER, B.PICKSYSTEM, B.BOARDSTAKE HAVING t.wageramount = (pold_stake_price + (CASE WHEN b.modifier = 'true' THEN pold_plus_price ELSE 0 END)) * (b.boardstake / pold_stake_price) * COUNT(1) * 1 ORDER BY t.wageramount DESC, S.IDDGSUBSCRIPTION DESC, MAIN_GAME; DECLARE subid BIGINT; DECLARE old_wagertemplate BIGINT; DECLARE new_wagertemplate BIGINT; DECLARE old_boardstake BIGINT; DECLARE new_boardstake BIGINT; DECLARE i INT; IF do_update THEN SIGNAL SQLSTATE '75000' SET MESSAGE_TEXT = 'ALL CHANGES ARE COMMITTED TO DATABASE'; ELSE SIGNAL SQLSTATE '75000' SET MESSAGE_TEXT = 'dry run only, no actual updates will be done'; END IF; OPEN sub; FETCH FROM sub INTO subid, old_wagertemplate, new_wagertemplate, old_boardstake, new_boardstake, i; WHILE (SQLSTATE = '00000') DO IF do_update THEN UPDATE gis.dgsubs_wager_template uswt SET wageramount = new_wagertemplate WHERE uswt.iddgsubscription = subid AND uswt.iddggame = pgameid; UPDATE gis.dgboard db SET boardstake = new_boardstake WHERE db.iddgboardstack IN (SELECT dbs.iddgboardstack FROM gis.dgboardstack dbs WHERE iddgsubscription = subid); END IF; INSERT INTO public.sub_price_change_log (gameid, subscription_id, old_wageramount, new_wageramount, old_boardstake, new_boardstake) VALUES (pgameid, subid, old_wagertemplate, new_wagertemplate, old_boardstake, new_boardstake); IF i % 1000 = 0 THEN COMMIT; END IF; FETCH FROM sub INTO subid, old_wagertemplate, new_wagertemplate, old_boardstake, new_boardstake, i; END WHILE; COMMIT; END;