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))