Subversion Repositories VORC

## View: v_class_new
#
# +---------------+------------------+------+-----+---------+-------+
# | Field         | Type             | Null | Key | Default | Extra |
# +---------------+------------------+------+-----+---------+-------+
# | id            | int(10) unsigned | NO   |     | 0       |       |
# | name          | varchar(128)     | YES  |     | NULL    |       |
# | coach         | text             | YES  |     | NULL    |       |
# | coachRCid     | text             | YES  |     | NULL    |       |
# | assistant     | text             | YES  |     | NULL    |       |
# | assistantRCid | text             | YES  |     | NULL    |       |
# | 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)       | YES  |     | 0       |       |
# | available     | decimal(21,0)    | YES  |     | NULL    |       |
# | stars         | double           | YES  |     | NULL    |       |
# | responses     | bigint(21)       | YES  |     | 0       |       |
# +---------------+------------------+------+-----+---------+-------+

CREATE VIEW `v_class_new` AS select `class`.`id` AS `id`,`class`.`name` AS `name`,`v_class_coaches`.`coaches` AS `coach`,`v_class_coaches`.`cRCids` AS `coachRCid`,`v_class_coach_assistants`.`assistants` AS `assistant`,`v_class_coach_assistants`.`aRCids` AS `assistantRCid`,`class`.`date` AS `date`,dayname(`class`.`date`) AS `dayofweek`,`class`.`location` AS `location`,`class`.`level` AS `level`,concat(time_format(`class`.`start_time`,'%H:%i'),' - ',time_format(`class`.`end_time`,'%H:%i')) AS `time`,time_format(`class`.`start_time`,'%H:%i') AS `start_time`,time_format(`class`.`end_time`,'%H:%i') AS `end_time`,`class`.`note` AS `note`,`class`.`capacity` AS `capacity`,ifnull(`v_class_signup_counts`.`signups`,0) AS `count`,if(`class`.`capacity` < ifnull(`v_class_signup_counts`.`signups`,0),0,`class`.`capacity` - ifnull(`v_class_signup_counts`.`signups`,0)) AS `available`,`v_survey_results`.`average` AS `stars`,`v_survey_results`.`count` AS `responses` from ((((`class` left join `v_class_signup_counts` on(`class`.`id` = `v_class_signup_counts`.`id`)) left join `v_class_coaches` on(`class`.`date` = `v_class_coaches`.`date` and `class`.`location` = `v_class_coaches`.`location` and `class`.`start_time` = `v_class_coaches`.`start_time`)) left join `v_class_coach_assistants` on(`class`.`date` = `v_class_coach_assistants`.`date` and `class`.`location` = `v_class_coach_assistants`.`location` and `class`.`start_time` = `v_class_coach_assistants`.`start_time`)) left join `v_survey_results` on(`class`.`id` = `v_survey_results`.`classid` and `v_survey_results`.`qid` = 8))