Subversion Repositories VORC

## View: v_class
#
# +------------+------------------+------+-----+---------+-------+
# | Field      | Type             | Null | Key | Default | Extra |
# +------------+------------------+------+-----+---------+-------+
# | id         | int(10) unsigned | NO   |     | 0       |       |
# | name       | varchar(128)     | YES  |     | NULL    |       |
# | coach      | varchar(45)      | YES  |     | NULL    |       |
# | coachRCid  | int(11)          | YES  |     | 0       |       |
# | date       | date             | YES  |     | NULL    |       |
# | dayofweek  | varchar(9)       | YES  |     | NULL    |       |
# | location   | varchar(45)      | YES  |     | NULL    |       |
# | level      | varchar(45)      | YES  |     | NULL    |       |
# | time       | varchar(23)      | YES  |     | NULL    |       |
# | start_time | varchar(10)      | YES  |     | NULL    |       |
# | end_time   | varchar(10)      | YES  |     | NULL    |       |
# | note       | mediumtext       | YES  |     | NULL    |       |
# | capacity   | int(10) unsigned | YES  |     | NULL    |       |
# | count      | bigint(21)       | NO   |     | 0       |       |
# | available  | decimal(21,0)    | YES  |     | NULL    |       |
# | stars      | double           | YES  |     | NULL    |       |
# | responses  | bigint(21)       | YES  |     | 0       |       |
# +------------+------------------+------+-----+---------+-------+

CREATE OR REPLACE VIEW `v_class` AS select `v_class_signup`.`id` AS `id`,`v_class_signup`.`name` AS `name`,`official`.`derby_name` AS `coach`,`official`.`RCid` AS `coachRCid`,`v_class_signup`.`date` AS `date`,dayname(`v_class_signup`.`date`) AS `dayofweek`,`v_class_signup`.`location` AS `location`,`v_class_signup`.`level` AS `level`,concat(convert(time_format(`v_class_signup`.`start_time`,'%H:%i') using utf8mb4),' - ',convert(time_format(`v_class_signup`.`end_time`,'%H:%i') using utf8mb4)) AS `time`,time_format(`v_class_signup`.`start_time`,'%H:%i') AS `start_time`,time_format(`v_class_signup`.`end_time`,'%H:%i') AS `end_time`,`v_class_signup`.`note` AS `note`,`v_class_signup`.`capacity` AS `capacity`,count(`v_class_signup`.`RCid`) AS `count`,if((`v_class_signup`.`capacity` < count(`v_class_signup`.`RCid`)),0,(`v_class_signup`.`capacity` - count(`v_class_signup`.`RCid`))) AS `available`,`v_survey_results`.`average` AS `stars`,`v_survey_results`.`count` AS `responses` from ((`v_class_signup` left join `official` on((`v_class_signup`.`coach` = `official`.`RCid`))) left join `v_survey_results` on(((`v_class_signup`.`id` = `v_survey_results`.`classid`) and (`v_survey_results`.`qid` = 8)))) group by `v_class_signup`.`id`