SELECT * FROM (
(SELECT NULL AS pos2, NULL AS class_spec, NULL AS class_order, NULL AS op2, NULL AS pro2, NULL AS am2, NULL AS wo2, NULL AS leg2, NULL AS wleg2, NULL AS 17_2, NULL AS w17_2,
CASE
WHEN TRUE THEN 0
WHEN posPro IS NOT NULL THEN posPro
WHEN posAm IS NOT NULL THEN posAm
WHEN posWo IS NOT NULL THEN posWo
WHEN posMas IS NOT NULL THEN posMas
WHEN posWMas IS NOT NULL THEN posWMas
WHEN posJun IS NOT NULL THEN posJun
WHEN posWJun IS NOT NULL THEN posWJun
END as Pos,
CASE
WHEN base_class = 'PRO' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posPro>'32') THEN org_eps_spec
WHEN base_class = 'AM' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posAm>'20') THEN org_eps_spec
WHEN base_class = 'WO' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posWo>'8') THEN org_eps_spec
WHEN base_class = 'LEG' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posMas>'8') THEN org_eps_spec
WHEN base_class = 'WLE' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posWMas>'8') THEN org_eps_spec
WHEN base_class = '17' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posJun>'8') THEN org_eps_spec
WHEN base_class = 'W17' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posWJun>'8') THEN org_eps_spec
WHEN base_class = 'NA' THEN 'NA'
ELSE 'Registered!'
END AS eps_spec,
CASE
WHEN base_class = 'PRO' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posPro>'32') THEN org_public_eps_spec
WHEN base_class = 'AM' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posAm>'20') THEN org_public_eps_spec
WHEN base_class = 'WO' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posWo>'8') THEN org_public_eps_spec
WHEN base_class = 'LEG' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posMas>'8') THEN org_public_eps_spec
WHEN base_class = 'WLE' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posWMas>'8') THEN org_public_eps_spec
WHEN base_class = '17' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posJun>'8') THEN org_public_eps_spec
WHEN base_class = 'W17' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posWJun>'8') THEN org_public_eps_spec
WHEN base_class = 'NA' THEN 'NA'
ELSE 'Registered!'
END AS public_eps_spec,
CASE WHEN (racer_reg_class = 'PRO' OR racer_reg_class = 'AM') AND (e_class=1 OR e_class=3) THEN 'OP'
ELSE CASE WHEN (racer_reg_class = 'PRO' OR racer_reg_class = 'AM') THEN racer_reg_class
ELSE CASE WHEN LOCATE(CONCAT(',',racer_reg_class,','),event_class_plan)>0 THEN racer_reg_class
ELSE CASE WHEN racer_reg_class='WGL' AND LOCATE(',WLE,',event_class_plan)>0 THEN 'WLE'
ELSE CASE WHEN racer_reg_class='GL' AND LOCATE(',LEG,',event_class_plan)>0 THEN 'LEG'
ELSE CASE WHEN racer_reg_class='W11' AND LOCATE(',11,',event_class_plan)>0 THEN '11'
ELSE CASE WHEN racer_reg_class='W11' AND LOCATE(',W14,',event_class_plan)>0 THEN 'W14'
ELSE CASE WHEN (racer_reg_class='11' OR racer_reg_class='W11') AND LOCATE(',14,',event_class_plan)>0 THEN '14'
ELSE CASE WHEN (racer_reg_class='W14' OR racer_reg_class='W11') AND LOCATE(',W17,',event_class_plan)>0 THEN 'W17'
ELSE CASE WHEN (racer_reg_class='14' OR racer_reg_class='W14' OR racer_reg_class='11' OR racer_reg_class='W11') AND LOCATE(',17,',event_class_plan)>0 THEN '17'
ELSE CASE WHEN (racer_reg_class='WGL' OR racer_reg_class='WLE' OR racer_reg_class='W17' OR racer_reg_class='W14' OR racer_reg_class='W11') AND LOCATE(',WO,',event_class_plan)>0 THEN 'WO'
ELSE CASE WHEN e_class=1 OR e_class=3 THEN 'OP'
ELSE UPPER(class) END END END END END END END END END END END END AS event_race_class,
RESULT.*
FROM (
SELECT
CASE WHEN RL.base_class = 'PRO' THEN @curRowPro := @curRowPro+1 ELSE NULL END as posPro,
CASE WHEN RL.base_class = 'AM' THEN @curRowAm := @curRowAm+1 ELSE NULL END as posAm,
CASE WHEN RL.base_class = 'WO' THEN @curRowWo := @curRowWo+1 ELSE NULL END as posWo,
CASE WHEN RL.base_class = 'LEG' THEN @curRowMas := @curRowMas+1 ELSE NULL END as posMas,
CASE WHEN RL.base_class = 'WLE' THEN @curRowWMas := @curRowWMas+1 ELSE NULL END as posWMas,
CASE WHEN RL.base_class = '17' THEN @curRowJun := @curRowJun+1 ELSE NULL END as posJun,
CASE WHEN RL.base_class = 'W17' THEN @curRowWJun := @curRowWJun+1 ELSE NULL END as posWJun,
RL.*,
CASE
WHEN rank_order = 10000 THEN CONCAT('No ',rank_string_type,' rank ',rank_string_year)
ELSE CONCAT(rank_order,' ranked ',rank_string_type,' ',SUBSTRING(base_rank_class,4),' ',rank_string_year)
END AS rank_place
FROM (
SELECT R.*,
CASE WHEN R.reg_class IS NOT NULL AND R.reg_class>'' THEN R.reg_class
ELSE CASE WHEN R.default_race_class IS NOT NULL AND R.default_race_class>'' THEN R.default_race_class
ELSE R.base_class END END AS racer_reg_class,
CASE WHEN R.base_class = 'PRO' THEN rnk_year WHEN R.base_class = 'AM' THEN rnk_year WHEN R.base_class = 'WO' THEN rnk_year WHEN R.base_class = 'LEG' THEN rnk_year WHEN R.base_class = 'WLE' THEN rnk_year WHEN R.base_class = '17' THEN rnk_year WHEN R.base_class = 'W17' THEN rnk_year
END AS rank_string_year,
CASE WHEN R.base_class = 'PRO' THEN 'Season' WHEN R.base_class = 'AM' THEN 'Season' WHEN R.base_class = 'WO' THEN 'Season' WHEN R.base_class = 'LEG' THEN 'Season' WHEN R.base_class = 'WLE' THEN 'Season' WHEN R.base_class = '17' THEN 'Season' WHEN R.base_class = 'W17' THEN 'Season'
END AS rank_string_type,
CAST(CASE WHEN R.base_class = 'PRO' THEN COALESCE(Y.place,10000) WHEN R.base_class = 'AM' THEN COALESCE(Y.place,10000) WHEN R.base_class = 'WO' THEN COALESCE(Y.place,10000) WHEN R.base_class = 'LEG' THEN COALESCE(Y.place,10000) WHEN R.base_class = 'WLE' THEN COALESCE(Y.place,10000) WHEN R.base_class = '17' THEN COALESCE(Y.place,10000) WHEN R.base_class = 'W17' THEN COALESCE(Y.place,10000)
END AS UNSIGNED) AS rank_order,
CAST(NULL AS UNSIGNED) AS rank_order2
FROM (
SELECT
CAST(CASE WHEN P.class = 'PRO' THEN '1. PRO' WHEN (P.birth_year >1900) AND (YEAR(CURRENT_DATE) - P.birth_year <18) AND P.sex = 'F' THEN '7. JUNIOR (Girl)' WHEN (P.birth_year >1900) AND (YEAR(CURRENT_DATE) - P.birth_year <18) THEN '4. JUNIOR' WHEN (P.birth_year >1900) AND (YEAR(CURRENT_DATE) - P.birth_year >44) AND P.sex = 'F' THEN '6. MASTER (Woman)' WHEN (P.birth_year >1900) AND (YEAR(CURRENT_DATE) - P.birth_year >44) THEN '2. MASTER' WHEN P.sex = 'F' THEN '5. WOMAN' WHEN P.class = 'AM' THEN '3. AM'
ELSE '9. NOT VALID REGISTRATION'
END AS CHAR(20)) AS base_rank_class,
CASE WHEN P.class = 'PRO' THEN 'PRO' WHEN (P.birth_year >1900) AND (YEAR(CURRENT_DATE) - P.birth_year <18) AND P.sex = 'F' THEN 'W17' WHEN (P.birth_year >1900) AND (YEAR(CURRENT_DATE) - P.birth_year <18) THEN '17' WHEN (P.birth_year >1900) AND (YEAR(CURRENT_DATE) - P.birth_year >44) AND P.sex = 'F' THEN 'WLE' WHEN (P.birth_year >1900) AND (YEAR(CURRENT_DATE) - P.birth_year >44) THEN 'LEG' WHEN P.sex = 'F' THEN 'WO' WHEN P.class = 'AM' THEN 'AM'
ELSE 'NA'
END AS base_class,
CONCAT('[', C.country, CASE WHEN P.State > '' THEN CONCAT(' - ',S.State) ELSE '' END, '] ',P.First_name,' ',P.Family_name) AS Racer,
CASE WHEN P.Birth_year <1900 THEN '-' ELSE CAST( YEAR( CURRENT_DATE ) - birth_year AS CHAR ) END AS Age,
EP.base_disc_order, EP.comp_no, EP.pid, P.family_name, P.first_name, P.nick_name, P.nationality, C.cc, C.country, D.flag_pid,
EP.event_pers_stat, EP.reg_class, P.class, P.default_race_class,
E.e_class, E.add_class_plan, CONCAT(',',E.add_class_plan,',') AS event_class_plan,
PS.eps_spec AS org_eps_spec,
PS.public_eps_spec AS org_public_eps_spec,
PS.icon,
date_reg_end,
CASE WHEN YEAR(date_start) > RS.value_integer THEN RS.value_integer ELSE YEAR(date_start) END AS rnk_year,
CASE WHEN YEAR(date_start) > RSL.value_integer THEN RSL.value_integer ELSE YEAR(date_start) END AS lim_year
FROM event_pers EP
JOIN person P ON P.pid = EP.pid AND P.first_name <> 'T-e-s-t'
JOIN event E ON E.eid = EP.eid
JOIN rank_settings RS on RS.RSID=0
JOIN rank_settings RSL on RSL.RSID=10
JOIN lu_event_pers_stat PS ON PS.lu_eps_id = EP.event_pers_stat
JOIN country C ON C.cid = P.Nationality
LEFT JOIN states S ON S.stid = P.state
LEFT JOIN (
SELECT DISTINCT pid AS flag_pid FROM (
SELECT pid, max(group_year) as year
FROM (SELECT pid, gid FROM person_title WHERE ptid<300 AND ptid<>8 AND ptid<>10) PT
JOIN `group` G ON G.gid=PT.gid AND G.group_year>=YEAR(CURDATE())-1
GROUP BY pid
UNION
SELECT pid, MAX(year) AS latest_top_rank_year
FROM rank_result_year
WHERE year>YEAR(CURDATE())-10 AND ((class = 'PRO' AND place <=10) OR (class = 'WO' AND place <=5))
GROUP BY pid
UNION
SELECT *
FROM (
SELECT pid, count(ptid) as title_count
FROM (SELECT pid, ptid FROM person_title WHERE ptid<300 AND ptid<>8 AND ptid<>10) A
GROUP BY pid
) A
WHERE A.title_count>=10
) B
) D ON D.flag_pid=P.pid
WHERE EP.eid ='10'
) R
LEFT JOIN rank_result_year Y on Y.year=rnk_year and Y.class=R.base_class and Y.pid=R.pid
LEFT JOIN rank_lim RL on RL.year=lim_year and RL.rank_class=R.base_class and RL.pid=R.pid
) AS RL
JOIN (SELECT @curRowPro := 0, @curRowAm := 0, @curRowWo := 0, @curRowMas := 0, @curRowWMas := 0, @curRowJun := 0, @curRowWJun := 0 ) q
ORDER BY RL.base_rank_class, RL.base_disc_order, RL.rank_order
) AS RESULT)
UNION
(SELECT pos AS pos2,
RESULT3.*
FROM (
SELECT SUBSTR(RESULT2.base_rank_class,4,99) AS class_spec, LEFT(RESULT2.base_rank_class,1) AS class_order,
NULL AS op2, NULL AS pro2, NULL AS am2, NULL AS wo2, NULL AS leg2, NULL AS wleg2, NULL AS 17_2, NULL AS w17_2,
RESULT2.*
FROM (
SELECT
CASE
WHEN TRUE THEN 0
WHEN posPro IS NOT NULL THEN posPro
WHEN posAm IS NOT NULL THEN posAm
WHEN posWo IS NOT NULL THEN posWo
WHEN posMas IS NOT NULL THEN posMas
WHEN posWMas IS NOT NULL THEN posWMas
WHEN posJun IS NOT NULL THEN posJun
WHEN posWJun IS NOT NULL THEN posWJun
END as Pos,
CASE
WHEN base_class = 'PRO' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posPro>'32') THEN org_eps_spec
WHEN base_class = 'AM' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posAm>'20') THEN org_eps_spec
WHEN base_class = 'WO' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posWo>'8') THEN org_eps_spec
WHEN base_class = 'LEG' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posMas>'8') THEN org_eps_spec
WHEN base_class = 'WLE' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posWMas>'8') THEN org_eps_spec
WHEN base_class = '17' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posJun>'8') THEN org_eps_spec
WHEN base_class = 'W17' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posWJun>'8') THEN org_eps_spec
WHEN base_class = 'NA' THEN 'NA'
ELSE 'Registered!'
END AS eps_spec,
CASE
WHEN base_class = 'PRO' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posPro>'32') THEN org_public_eps_spec
WHEN base_class = 'AM' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posAm>'20') THEN org_public_eps_spec
WHEN base_class = 'WO' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posWo>'8') THEN org_public_eps_spec
WHEN base_class = 'LEG' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posMas>'8') THEN org_public_eps_spec
WHEN base_class = 'WLE' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posWMas>'8') THEN org_public_eps_spec
WHEN base_class = '17' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posJun>'8') THEN org_public_eps_spec
WHEN base_class = 'W17' AND (date_reg_end is NULL OR event_pers_stat<>5 OR date_reg_end >= curdate() OR posWJun>'8') THEN org_public_eps_spec
WHEN base_class = 'NA' THEN 'NA'
ELSE 'Registered!'
END AS public_eps_spec,
CASE WHEN (racer_reg_class = 'PRO' OR racer_reg_class = 'AM') AND (e_class=1 OR e_class=3) THEN 'OP'
ELSE CASE WHEN (racer_reg_class = 'PRO' OR racer_reg_class = 'AM') THEN racer_reg_class
ELSE CASE WHEN LOCATE(CONCAT(',',racer_reg_class,','),event_class_plan)>0 THEN racer_reg_class
ELSE CASE WHEN racer_reg_class='WGL' AND LOCATE(',WLE,',event_class_plan)>0 THEN 'WLE'
ELSE CASE WHEN racer_reg_class='GL' AND LOCATE(',LEG,',event_class_plan)>0 THEN 'LEG'
ELSE CASE WHEN racer_reg_class='W11' AND LOCATE(',11,',event_class_plan)>0 THEN '11'
ELSE CASE WHEN racer_reg_class='W11' AND LOCATE(',W14,',event_class_plan)>0 THEN 'W14'
ELSE CASE WHEN (racer_reg_class='11' OR racer_reg_class='W11') AND LOCATE(',14,',event_class_plan)>0 THEN '14'
ELSE CASE WHEN (racer_reg_class='W14' OR racer_reg_class='W11') AND LOCATE(',W17,',event_class_plan)>0 THEN 'W17'
ELSE CASE WHEN (racer_reg_class='14' OR racer_reg_class='W14' OR racer_reg_class='11' OR racer_reg_class='W11') AND LOCATE(',17,',event_class_plan)>0 THEN '17'
ELSE CASE WHEN (racer_reg_class='WGL' OR racer_reg_class='WLE' OR racer_reg_class='W17' OR racer_reg_class='W14' OR racer_reg_class='W11') AND LOCATE(',WO,',event_class_plan)>0 THEN 'WO'
ELSE CASE WHEN e_class=1 OR e_class=3 THEN 'OP'
ELSE UPPER(class) END END END END END END END END END END END END AS event_race_class,
RESULT.*
FROM (
SELECT
CASE WHEN RL.base_class = 'PRO' THEN @curRowPro3 := @curRowPro3+1 ELSE NULL END as posPro,
CASE WHEN RL.base_class = 'AM' THEN @curRowAm3 := @curRowAm3+1 ELSE NULL END as posAm,
CASE WHEN RL.base_class = 'WO' THEN @curRowWo3 := @curRowWo3+1 ELSE NULL END as posWo,
CASE WHEN RL.base_class = 'LEG' THEN @curRowMas3 := @curRowMas3+1 ELSE NULL END as posMas,
CASE WHEN RL.base_class = 'WLE' THEN @curRowWMas3 := @curRowWMas3+1 ELSE NULL END as posWMas,
CASE WHEN RL.base_class = '17' THEN @curRowJun3 := @curRowJun3+1 ELSE NULL END as posJun,
CASE WHEN RL.base_class = 'W17' THEN @curRowWJun3 := @curRowWJun3+1 ELSE NULL END as posWJun,
RL.*,
CASE
WHEN rank_order2 = 10000 THEN CONCAT('No ',rank_string_type,' rank ',rank_string_year)
ELSE CONCAT(rank_order2,' ranked ',rank_string_type,' ',SUBSTRING(base_rank_class,4),' ',rank_string_year)
END AS rank_place
FROM (
SELECT R.*,
CASE WHEN R.reg_class IS NOT NULL AND R.reg_class>'' THEN R.reg_class
ELSE CASE WHEN R.default_race_class IS NOT NULL AND R.default_race_class>'' THEN R.default_race_class
ELSE R.base_class END END AS racer_reg_class,
CASE WHEN R.base_class = 'PRO' THEN rnk_year WHEN R.base_class = 'AM' THEN rnk_year WHEN R.base_class = 'WO' THEN rnk_year WHEN R.base_class = 'LEG' THEN rnk_year WHEN R.base_class = 'WLE' THEN rnk_year WHEN R.base_class = '17' THEN rnk_year WHEN R.base_class = 'W17' THEN rnk_year
END AS rank_string_year,
CASE WHEN R.base_class = 'PRO' THEN 'Season' WHEN R.base_class = 'AM' THEN 'Season' WHEN R.base_class = 'WO' THEN 'Season' WHEN R.base_class = 'LEG' THEN 'Season' WHEN R.base_class = 'WLE' THEN 'Season' WHEN R.base_class = '17' THEN 'Season' WHEN R.base_class = 'W17' THEN 'Season'
END AS rank_string_type,
CAST(NULL AS UNSIGNED) AS rank_order,
CAST(CASE WHEN R.base_class = 'PRO' THEN COALESCE(Y.place,10000) WHEN R.base_class = 'AM' THEN COALESCE(Y.place,10000) WHEN R.base_class = 'WO' THEN COALESCE(Y.place,10000) WHEN R.base_class = 'LEG' THEN COALESCE(Y.place,10000) WHEN R.base_class = 'WLE' THEN COALESCE(Y.place,10000) WHEN R.base_class = '17' THEN COALESCE(Y.place,10000) WHEN R.base_class = 'W17' THEN COALESCE(Y.place,10000)
END AS UNSIGNED) AS rank_order2
FROM (
SELECT
CAST(CASE WHEN (
CASE WHEN EP.reg_class IS NOT NULL AND EP.reg_class>'' THEN EP.reg_class
ELSE CASE WHEN P.default_race_class IS NOT NULL AND P.default_race_class>'' THEN P.default_race_class
ELSE P.class END END
) = 'PRO' THEN '1. PRO' WHEN P.sex = 'F' AND EP.reg_class IS NOT NULL AND EP.reg_class>'' AND EP.reg_class = 'WO' THEN '5. WOMAN'
WHEN (P.birth_year >1900) AND (YEAR(CURRENT_DATE) - P.birth_year <15) AND P.sex = 'F' THEN '7. JUNIOR (Girl)'
WHEN (P.birth_year >1900) AND (YEAR(CURRENT_DATE) - P.birth_year <18) AND P.sex = 'F' AND EP.reg_class <> 'WO' AND (P.default_race_class IS NULL OR P.default_race_class <>'WO') THEN '7. JUNIOR (Girl)'
WHEN (P.birth_year >1900) AND (YEAR(CURRENT_DATE) - P.birth_year <15) THEN '4. JUNIOR'
WHEN (P.birth_year >1900) AND (YEAR(CURRENT_DATE) - P.birth_year <18) AND EP.reg_class <> 'AM' AND EP.reg_class <> 'WO' AND (P.default_race_class IS NULL OR (P.default_race_class <>'AM' AND P.default_race_class <>'WO')) THEN '4. JUNIOR'
WHEN (P.birth_year >1900) AND (YEAR(CURRENT_DATE) - P.birth_year >54) AND P.sex = 'F' THEN '6. MASTER (Woman)'
WHEN (P.birth_year >1900) AND (YEAR(CURRENT_DATE) - P.birth_year >44) AND P.sex = 'F' AND EP.reg_class <> 'WO' AND (P.default_race_class IS NULL OR P.default_race_class <>'WO') THEN '6. MASTER (Woman)'
WHEN (P.birth_year >1900) AND (YEAR(CURRENT_DATE) - P.birth_year >54) THEN '3. MASTER'
WHEN (P.birth_year >1900) AND (YEAR(CURRENT_DATE) - P.birth_year >44) AND EP.reg_class <> 'AM' AND EP.reg_class <> 'WO' AND (P.default_race_class IS NULL OR (P.default_race_class <>'AM' AND P.default_race_class <>'WO')) THEN '3. MASTER'
WHEN P.sex = 'F' THEN '5. WOMAN' WHEN P.class = 'AM' THEN '2. AM'
ELSE '9. NOT VALID REGISTRATION'
END AS CHAR(20)) AS base_rank_class,
CASE WHEN (
CASE WHEN EP.reg_class IS NOT NULL AND EP.reg_class>'' THEN EP.reg_class
ELSE CASE WHEN P.default_race_class IS NOT NULL AND P.default_race_class>'' THEN P.default_race_class
ELSE P.class END END
) = 'PRO' THEN 'PRO' WHEN P.sex = 'F' AND EP.reg_class IS NOT NULL AND EP.reg_class>'' AND EP.reg_class = 'WO' THEN 'WO'
WHEN (P.birth_year >1900) AND (YEAR(CURRENT_DATE) - P.birth_year <15) AND P.sex = 'F' THEN 'W17'
WHEN (P.birth_year >1900) AND (YEAR(CURRENT_DATE) - P.birth_year <18) AND P.sex = 'F' AND EP.reg_class <> 'WO' AND (P.default_race_class IS NULL OR P.default_race_class <>'WO') THEN 'W17'
WHEN (P.birth_year >1900) AND (YEAR(CURRENT_DATE) - P.birth_year <15) THEN '17'
WHEN (P.birth_year >1900) AND (YEAR(CURRENT_DATE) - P.birth_year <18) AND EP.reg_class <> 'AM' AND EP.reg_class <> 'WO' AND (P.default_race_class IS NULL OR (P.default_race_class <>'AM' AND P.default_race_class <>'WO')) THEN '17'
WHEN (P.birth_year >1900) AND (YEAR(CURRENT_DATE) - P.birth_year >54) AND P.sex = 'F' THEN 'WLE'
WHEN (P.birth_year >1900) AND (YEAR(CURRENT_DATE) - P.birth_year >44) AND P.sex = 'F' AND EP.reg_class <> 'WO' AND (P.default_race_class IS NULL OR P.default_race_class <>'WO') THEN 'WLE'
WHEN (P.birth_year >1900) AND (YEAR(CURRENT_DATE) - P.birth_year >54) THEN 'LEG'
WHEN (P.birth_year >1900) AND (YEAR(CURRENT_DATE) - P.birth_year >44) AND EP.reg_class <> 'AM' AND EP.reg_class <> 'WO' AND (P.default_race_class IS NULL OR (P.default_race_class <>'AM' AND P.default_race_class <>'WO')) THEN 'LEG'
WHEN P.sex = 'F' THEN 'WO' WHEN P.class = 'AM' THEN 'AM'
ELSE 'NA'
END AS base_class,
CONCAT('[', C.country, CASE WHEN P.State > '' THEN CONCAT(' - ',S.State) ELSE '' END, '] ',P.First_name,' ',P.Family_name) AS Racer,
CASE WHEN P.Birth_year <1900 THEN '-' ELSE CAST( YEAR( CURRENT_DATE ) - birth_year AS CHAR ) END AS Age,
EP.base_disc_order, EP.comp_no, EP.pid, P.family_name, P.first_name, P.nick_name, P.nationality, C.cc, C.country, D.flag_pid,
EP.event_pers_stat, EP.reg_class, P.class, P.default_race_class,
E.e_class, E.add_class_plan, CONCAT(',',E.add_class_plan,',') AS event_class_plan,
PS.eps_spec AS org_eps_spec,
PS.public_eps_spec AS org_public_eps_spec,
PS.icon,
date_reg_end,
CASE WHEN YEAR(date_start) > RS.value_integer THEN RS.value_integer ELSE YEAR(date_start) END AS rnk_year,
CASE WHEN YEAR(date_start) > RSL.value_integer THEN RSL.value_integer ELSE YEAR(date_start) END AS lim_year
FROM event_pers EP
JOIN person P ON P.pid = EP.pid AND P.first_name <> 'T-e-s-t'
JOIN event E ON E.eid = EP.eid
JOIN rank_settings RS on RS.RSID=0
JOIN rank_settings RSL on RSL.RSID=10
JOIN lu_event_pers_stat PS ON PS.lu_eps_id = EP.event_pers_stat
JOIN country C ON C.cid = P.Nationality
LEFT JOIN states S ON S.stid = P.state
LEFT JOIN (
SELECT DISTINCT pid AS flag_pid FROM (
SELECT pid, max(group_year) as year
FROM (SELECT pid, gid FROM person_title WHERE ptid<300 AND ptid<>8 AND ptid<>10) PT
JOIN `group` G ON G.gid=PT.gid AND G.group_year>=YEAR(CURDATE())-1
GROUP BY pid
UNION
SELECT pid, MAX(year) AS latest_top_rank_year
FROM rank_result_year
WHERE year>YEAR(CURDATE())-10 AND ((class = 'PRO' AND place <=10) OR (class = 'WO' AND place <=5))
GROUP BY pid
UNION
SELECT *
FROM (
SELECT pid, count(ptid) as title_count
FROM (SELECT pid, ptid FROM person_title WHERE ptid<300 AND ptid<>8 AND ptid<>10) A
GROUP BY pid
) A
WHERE A.title_count>=10
) B
) D ON D.flag_pid=P.pid
WHERE EP.eid ='10'
) R
LEFT JOIN rank_result_year Y on Y.year=rnk_year and Y.class=R.base_class and Y.pid=R.pid
LEFT JOIN rank_lim RL on RL.year=lim_year and RL.rank_class=R.base_class and RL.pid=R.pid
) AS RL
JOIN (SELECT @curRowPro3 := 0, @curRowAm3 := 0, @curRowWo3 := 0, @curRowMas3 := 0, @curRowWMas3 := 0, @curRowJun3 := 0, @curRowWJun3 := 0 ) q
ORDER BY RL.base_rank_class, RL.base_disc_order, RL.rank_order
) AS RESULT
) AS RESULT2
JOIN discipline_class dc ON dc.class_type=event_race_class
) AS RESULT3)
) AS TOT
ORDER BY class_order, rank_order2, base_rank_class, rank_order
