problem

mail@pastecode.io avatar
unknown
mysql
a month ago
3.3 kB
6
Indexable
Never
Transaction t2 = new Transaction();

    t2.AddQuery("CREATE PROCEDURE IF NOT EXISTS `UpdateMapPoints`(IN `in_map_id` INT, IN `in_run_type` INT, IN `in_run_id` INT, IN `in_class` INT) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY INVOKER BEGIN");
    t2.AddQuery("DECLARE v_rec_id, v_player_id, completions, v_tier INT;");
    t2.AddQuery("DECLARE v_rank INT DEFAULT 0;");
    t2.AddQuery("DECLARE calculated_points, default_points, wr, pr DOUBLE;");
    t2.AddQuery("DECLARE done INT DEFAULT FALSE;");
    t2.AddQuery("DECLARE cur CURSOR FOR SELECT record_id, player_id, time FROM records WHERE map_id = in_map_id AND run_type = in_run_type AND run_id = in_run_id AND class = in_class ORDER BY time ASC;");
    t2.AddQuery("DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;");
    t2.AddQuery("OPEN cur;");
    t2.AddQuery("SELECT COUNT(*) INTO completions FROM records WHERE map_id = in_map_id AND run_type = in_run_type AND run_id = in_run_id AND class = in_class;");
    t2.AddQuery("IF in_class = 0");
    t2.AddQuery("THEN");
    t2.AddQuery("SELECT soldier_tier INTO v_tier FROM map_info WHERE map_id = in_map_id AND run_type = in_run_type AND run_id = in_run_id;");
    t2.AddQuery("ELSEIF in_class = 1");
    t2.AddQuery("THEN");
    t2.AddQuery("SELECT demoman_tier INTO v_tier FROM map_info WHERE map_id = in_map_id AND run_type = in_run_type AND run_id = in_run_id;");
    t2.AddQuery("END IF;");
    t2.AddQuery("SELECT pts INTO default_points FROM points WHERE tier = v_tier;");
    t2.AddQuery("loop_through_rows:LOOP");
    t2.AddQuery("FETCH cur INTO v_rec_id, v_player_id, pr;");
    t2.AddQuery("IF done THEN");
    t2.AddQuery("LEAVE loop_through_rows;");
    t2.AddQuery("END IF;");
    t2.AddQuery("SET v_rank = v_rank + 1;");
    t2.AddQuery("IF v_rank = 1");
    t2.AddQuery("THEN");
    t2.AddQuery("SET wr = pr;");
    t2.AddQuery("SET calculated_points = default_points + ((default_points * ((wr / pr) * 1.5)) * 1.3) + completions;");
    t2.AddQuery("ELSE");
    t2.AddQuery("SET calculated_points = default_points + ((default_points * ((wr / pr) * 1.5)) / 1.3) + completions * 0.75;");
    t2.AddQuery("END IF;");
    t2.AddQuery("UPDATE records SET `records`.`rank` = (SELECT v_rank), `records`.`points` = (SELECT calculated_points) WHERE record_id = v_rec_id;");
    t2.AddQuery("IF in_class = 0");
    t2.AddQuery("THEN");
    t2.AddQuery("UPDATE players SET soldier_points = (SELECT SUM(points) FROM records WHERE player_id = v_player_id AND class = in_class) WHERE id = v_player_id;");
    t2.AddQuery("UPDATE players SET soldier_rank = (SELECT COUNT(*) + 1 FROM records WHERE (SELECT soldier_points WHERE id = v_player_id) > soldier_points) WHERE id = v_player_id;");
    t2.AddQuery("ELSEIF in_class = 1");
    t2.AddQuery("THEN");
    t2.AddQuery("UPDATE players SET demoman_points = (SELECT SUM(points) FROM records WHERE player_id = v_player_id AND class = in_class) WHERE id = v_player_id;");
    t2.AddQuery("UPDATE players SET demoman_rank = (SELECT COUNT(*) + 1 FROM records WHERE (SELECT demoman_points WHERE id = v_player_id) > demoman_points) WHERE id = v_player_id;");
    t2.AddQuery("END IF;");
    t2.AddQuery("END LOOP;");
    t2.AddQuery("CLOSE cur;");
    t2.AddQuery("END;");

    g_hDatabase.Execute(t2, _, Thread_Empty_TransactionFail);
Leave a Comment