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 OR REPLACE 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(convert(time_format(`class`.`start_time`,'%H:%i') using utf8mb4),' - ',convert(time_format(`class`.`end_time`,'%H:%i') using utf8mb4)) 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`,`v_class_signup_counts`.`signups` AS `count`,if((`class`.`capacity` < `v_class_signup_counts`.`signups`),0,(`class`.`capacity` - `v_class_signup_counts`.`signups`)) 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))))