pfPfZfF1

1CREATE DEFINER=`root`@`localhost` PROCEDURE `dt`(
2    week_num_1 int,
3    week_num_2 int,
4    mask int,
5    sum_avg boolean,
6    team_id_spec int,
7    min_max boolean,
8    min_minutes_played int
9)
10begin
11
12declare player_id int;
13declare value float;
14declare type int;
15declare team_id int;
16declare done int default 0;
17declare cnt_g int;
18declare cnt_z int;
19declare cnt_p int;
20declare cnt_n int;
21declare flag_z int;
22declare skip int;
23
24declare cur1 cursor for
25select tmp.player_id, tmp.value, tmp.type, tmp.team_id
26from tmp
27where tmp.player_id not in (select res.player_id from res)
28order by tmp.value desc;
29
30declare continue handler for not found set done = 1;
31
32set cnt_g = 0, cnt_z = 0, cnt_p = 0, cnt_n = 0, flag_z = 1;
33
34create temporary table if not exists tmp( player_id int, value int, type int, team_id int);
35truncate tmp;
36
37insert into tmp( player_id, value, type, team_id)
38select ps.player_id
39,
40SUM(
41bit_count( mask & 1) * ps.minutes_played
42+ bit_count( mask & 2) * ps.goals_scored
43+ bit_count( mask & 4) * ps.assists
44+ bit_count( mask & 8) * ps.clean_sheets
45+ bit_count( mask & 16) * ps.goals_conceded
46+ bit_count( mask & 32) * ps.own_goals
47+ bit_count( mask & 64) * ps.penalties_saved
48+ bit_count( mask & 128) * ps.penalties_missed
49+ bit_count( mask & 256) * ps.yellow_cards
50+ bit_count( mask & 512) * ps.red_cards
51+ bit_count( mask & 1024) * ps.saves
52+ bit_count( mask & 2048) * ps.bonus
53+ bit_count( mask & 4096) * ps.esp
54+ bit_count( mask & 8192) * ps.net_transfers
55+ bit_count( mask & 16384) * ps.value
56+ bit_count( mask & 32768) * ps.points
57) / if(sum_avg, 1, (week_num_2 - week_num_1 + 1)) * if(min_max, -1, 1)
58, p.type
59, p.team_id
60from player_stat ps, player p
61where ps.gameweek_num between week_num_1 AND week_num_2
62    and p.id = ps.player_id
63    and ( team_id_spec is null or p.team_id = team_id_spec)
64    and
65    (
66         min_minutes_played is null
67         or p.id in
68         (
69             select ps2.player_id
70             from player_stat ps2
71             where ps.gameweek_num between week_num_1 and week_num_2
72             group by ps2.player_id
73             having sum( ps2.minutes_played) >= min_minutes_played
74         )
75    )
76group by ps.player_id;
77
78create temporary table if not exists res( player_id int, value int, flag int );
79truncate res;
80
81INSERT INTO res
82select player_id, VALUE, NULL
83from tmp
84where Type = 1
85order by VALUE limit 1
86
87INSERT INTO res
88select player_id, VALUE, NULL
89from tmp
90where Type = 2
91order by VALUE limit 3
92
93INSERT INTO res
94select player_id, VALUE, NULL
95from tmp
96where Type = 4
97order by VALUE limit 1
98
99open cur1;
100
101read_loop: loop
102    fetch cur1 into player_id, value, type, team_id;
103
104    if (cnt_g + cnt_z + cnt_p + cnt_n = 11) or done = 1 then
105        leave read_loop;
106    end if;
107
108    set skip = 0;
109    if (select count(*) from res, player where res.player_id = player.id and player.team_id = team_id) = 3 and team_id_spec is null then
110        set skip = 1;
111    end if;
112
113    if type = 1 and cnt_g < 1 and skip = 0 then
114        insert into res values (player_id, value, null);
115        set cnt_g = cnt_g + 1;
116    elseif type = 2 and cnt_z < 5 and cnt_z + cnt_p + cnt_n < 10 and skip = 0 then
117        insert into res values (player_id, value, null);
118        set cnt_z = cnt_z + 1;
119    elseif type = 3 and cnt_p < 5 and cnt_z + cnt_p + cnt_n < 10 and skip = 0 then
120        insert into res values (player_id, value, null);
121        set cnt_p = cnt_p + 1;
122    elseif type = 4 and cnt_n < 3 and cnt_z + cnt_p + cnt_n < 10 and skip = 0 then
123        insert into res values (player_id, value, null);
124        set cnt_n = cnt_n + 1;
125    end if;
126end loop read_loop;
127
128close cur1;
129set done = 0;
130open cur1;
131
132read_loop2: loop
133    fetch cur1 into player_id, value, type, team_id;
134
135    if (cnt_g + cnt_z + cnt_p + cnt_n = 15) or done = 1 then
136        leave read_loop2;
137    end if;
138
139    set skip = 0;
140    if (select count(*) from res, player where res.player_id = player.id and player.team_id = team_id) = 3 and team_id_spec is null then
141        set skip = 1;
142    end if;
143
144    if type = 1 and cnt_g < 2 and skip = 0 then
145        insert into res values (player_id, value, 0);
146        set cnt_g = cnt_g + 1;
147    elseif type = 2 and cnt_z < 5 and skip = 0 then
148        insert into res values (player_id, value, flag_z);
149        set cnt_z = cnt_z + 1, flag_z = flag_z + 1;
150    elseif type = 3 and cnt_p < 5 and skip = 0 then
151        insert into res values (player_id, value, flag_z);
152        set cnt_p = cnt_p + 1, flag_z = flag_z + 1;
153    elseif type = 4 and cnt_n < 3 and skip = 0 then
154        insert into res values (player_id, value, flag_z);
155        set cnt_n = cnt_n + 1, flag_z = flag_z + 1;
156    end if;
157
158end loop read_loop2;
159
160close cur1;
161
162select * from res where value is not null order by flag;
163END