Subversion Repositories VORC

## View: v_shift_announcer
#
# +--------------+-----------------------------------------+------+-----+---------+-------+
# | Field        | Type                                    | Null | Key | Default | Extra |
# +--------------+-----------------------------------------+------+-----+---------+-------+
# | id           | int(11)                                 | NO   |     | 0       |       |
# | teams        | varchar(261)                            | YES  |     | NULL    |       |
# | date         | date                                    | NO   |     | NULL    |       |
# | dayofweek    | varchar(9)                              | YES  |     | NULL    |       |
# | time         | varchar(23)                             | YES  |     | NULL    |       |
# | start_time   | time                                    | NO   |     | NULL    |       |
# | end_time     | time                                    | NO   |     | NULL    |       |
# | volhours     | double                                  | YES  |     | NULL    |       |
# | track        | varchar(45)                             | NO   |     | NULL    |       |
# | level        | varchar(45)                             | NO   |     | NULL    |       |
# | restrictions | varchar(45)                             | YES  |     | NULL    |       |
# | gtype        | varchar(45)                             | NO   |     | NULL    |       |
# | signup       | enum('open','selected','reserved')      | NO   |     | NULL    |       |
# | notes        | mediumtext                              | YES  |     | NULL    |       |
# | role         | varchar(64)                             | NO   |     | NULL    |       |
# | tla          | char(8)                                 | YES  |     | NULL    |       |
# | name         | varchar(45)                             | YES  |     | NULL    |       |
# | type         | enum('referee','nso','obs','announcer') | YES  |     | NULL    |       |
# | RCid         | int(11)                                 | YES  |     | 0       |       |
# | derby_name   | varchar(45)                             | YES  |     | NULL    |       |
# | real_name    | varchar(45)                             | YES  |     | NULL    |       |
# | email        | varchar(45)                             | YES  |     | NULL    |       |
# +--------------+-----------------------------------------+------+-----+---------+-------+

CREATE OR REPLACE VIEW `v_shift_announcer` AS select `game`.`id` AS `id`,concat_ws('',`game`.`title`,`game`.`team1`,' vs. ',`game`.`team2`) AS `teams`,`game`.`date` AS `date`,dayname(`game`.`date`) AS `dayofweek`,concat(convert(time_format(`game`.`time`,'%H:%i') using utf8mb4),' - ',convert(time_format(`game`.`end_time`,'%H:%i') using utf8mb4)) AS `time`,`game`.`time` AS `start_time`,`game`.`end_time` AS `end_time`,(trim((time_to_sec(timediff(`game`.`end_time`,`game`.`time`)) / 3600)) + 0) AS `volhours`,`game`.`track` AS `track`,`game`.`level` AS `level`,`game`.`restrictions` AS `restrictions`,`game`.`type` AS `gtype`,`staff_template`.`assignment` AS `signup`,`game`.`notes` AS `notes`,`staff_template`.`role` AS `role`,`role`.`TLA` AS `tla`,`role`.`name` AS `name`,`role`.`type` AS `type`,`official`.`RCid` AS `RCid`,`official`.`derby_name` AS `derby_name`,`official`.`real_name` AS `real_name`,`official`.`email` AS `email` from ((((`game` join `staff_template` on((`game`.`type` = `staff_template`.`type`))) left join `assignment` on(((`game`.`id` = `assignment`.`Gid`) and (`staff_template`.`role` = `assignment`.`role`)))) left join `role` on((`role`.`TLA` = substring_index(`staff_template`.`role`,'-',1)))) left join `official` on((`assignment`.`RCid` = `official`.`RCid`))) where (`role`.`type` = 'announcer') order by `game`.`date`,`game`.`track`,concat(convert(time_format(`game`.`time`,'%H:%i') using utf8mb4),' - ',convert(time_format(`game`.`end_time`,'%H:%i') using utf8mb4)),`game`.`type`,`role`.`TLA`,`official`.`derby_name`