Integrity

SELECT 'run' AS entity, r.id AS record_id, 'missing_sp' AS issue_key, 'data-blocker' AS severity, CONCAT('Run ', r.id, ' has no starting price.') AS message, 'Enter starting price, or confirm this race has no SP market.' AS suggested_action, r.race_id, r.race_date, r.race_number, r.tab_number, r.runner_name, r.runner_id, r.course_id, c.display_name AS course, c.country_id, co.name AS country, co.racing_code AS country_racing_code, r.discipline, r.starting_price FROM run r LEFT JOIN courses c ON c.id = r.course_id LEFT JOIN country co ON co.id = c.country_id WHERE (%(record_id)s IS NULL OR r.id = %(record_id)s) AND (%(record_id)s IS NOT NULL OR %(date_from)s IS NULL OR r.race_date >= %(date_from)s) AND (%(record_id)s IS NOT NULL OR %(date_to)s IS NULL OR r.race_date < DATE_ADD(%(date_to)s, INTERVAL 1 DAY)) AND (%(discipline)s IS NULL OR r.discipline = %(discipline)s) AND (%(country_id)s IS NULL OR c.country_id = %(country_id)s) AND r.finish_position IS NOT NULL AND (r.starting_price IS NULL OR TRIM(r.starting_price) = '')

SELECT 'run' AS entity, r.id AS record_id, 'pre_post_trainer_mismatch' AS issue_key, 'review' AS severity, CONCAT('Run trainer differs from racecard trainer for ', r.runner_name, '.') AS message, 'Review trainer identity in racecard and post-race run data.' AS suggested_action, r.race_id, r.race_date, r.runner_name, r.runner_id, r.course_id, c.display_name AS course, c.country_id, co.name AS country, co.racing_code AS country_racing_code, r.discipline, r.trainer_id AS post_trainer_id, r.trainer_name AS post_trainer_name, rc.trainer_id AS pre_trainer_id, rc.trainer AS pre_trainer_name FROM run r JOIN racecard_runner rc ON rc.race_id = r.race_id AND rc.runner_id = r.runner_id LEFT JOIN courses c ON c.id = r.course_id LEFT JOIN country co ON co.id = c.country_id WHERE (%(record_id)s IS NULL OR r.id = %(record_id)s) AND (%(record_id)s IS NOT NULL OR %(date_from)s IS NULL OR r.race_date >= %(date_from)s) AND (%(record_id)s IS NOT NULL OR %(date_to)s IS NULL OR r.race_date < DATE_ADD(%(date_to)s, INTERVAL 1 DAY)) AND (%(discipline)s IS NULL OR r.discipline = %(discipline)s) AND (%(country_id)s IS NULL OR c.country_id = %(country_id)s) AND COALESCE(r.trainer_id, 0) <> 0 AND COALESCE(rc.trainer_id, 0) <> 0 AND r.trainer_id <> rc.trainer_id

SELECT 'runner' AS entity, r.id AS record_id, CONCAT('possible_duplicate_runner:', LEAST(r.id, r2.id), ':', GREATEST(r.id, r2.id)) AS issue_key, 'review' AS severity, CONCAT('Runner ', r.runner_name, ' may duplicate runner ', r2.id, '.') AS message, 'Open Compare / View and review whether the runner records should be merged.' AS suggested_action, r.runner_name, r.country_code, r.year_of_foal, r.id AS runner_id, r2.id AS duplicate_runner_id, r2.runner_name AS duplicate_runner_name, r2.country_code AS duplicate_country_code, r2.year_of_foal AS duplicate_year_of_foal, r.sire_name, r.dam_name, r2.sire_name AS duplicate_sire_name, r2.dam_name AS duplicate_dam_name FROM runner r JOIN runner r2 ON r2.id <> r.id AND COALESCE(r2.runner_name, '') = COALESCE(r.runner_name, '') AND COALESCE(r2.year_of_foal, 0) = COALESCE(r.year_of_foal, 0) AND COALESCE(r2.country_code, '') = COALESCE(r.country_code, '') WHERE (%(record_id)s IS NULL OR r.id = %(record_id)s) AND r.runner_name IS NOT NULL AND TRIM(r.runner_name) <> '' AND COALESCE(r.year_of_foal, 0) <> 0 AND r.id < r2.id