Subversion Repositories VORC

Rev

Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
208 - 1 
## View: v_shift
2 
#
3 
# +------------+------------------+------+-----+---------+-------+
4 
# | Field      | Type             | Null | Key | Default | Extra |
5 
# +------------+------------------+------+-----+---------+-------+
6 
# | id         | int(10) unsigned | NO   |     | 0       |       |
7 
# | dept       | varchar(45)      | YES  |     | NULL    |       |
8 
# | role       | varchar(45)      | YES  |     | NULL    |       |
9 
# | type       | varchar(45)      | YES  |     | NULL    |       |
10 
# | date       | date             | YES  |     | NULL    |       |
11 
# | dayofweek  | varchar(9)       | YES  |     | NULL    |       |
12 
# | location   | varchar(45)      | YES  |     | NULL    |       |
13 
# | time       | varchar(23)      | YES  |     | NULL    |       |
14 
# | volhours   | double           | YES  |     | NULL    |       |
15 
# | start_time | time             | YES  |     | NULL    |       |
16 
# | end_time   | time             | YES  |     | NULL    |       |
17 
# | doubletime | tinyint(1)       | NO   |     | 0       |       |
18 
# | note       | mediumtext       | YES  |     | NULL    |       |
19 
# | mod_time   | double           | YES  |     | NULL    |       |
20 
# | RCid       | varchar(45)      | YES  |     | NULL    |       |
21 
# | derby_name | varchar(45)      | YES  |     | NULL    |       |
22 
# | full_name  | varchar(45)      | YES  |     | NULL    |       |
23 
# +------------+------------------+------+-----+---------+-------+
24 
 
25 
CREATE OR REPLACE VIEW `v_shift` AS select `shift`.`id` AS `id`,`shift`.`dept` AS `dept`,`shift`.`role` AS `role`,`shift`.`type` AS `type`,`shift`.`date` AS `date`,dayname(`shift`.`date`) AS `dayofweek`,`shift`.`location` AS `location`,concat(convert(time_format(`shift`.`start_time`,'%H:%i') using utf8mb4),' - ',convert(time_format(`shift`.`end_time`,'%H:%i') using utf8mb4)) AS `time`,if(((`shift`.`type` = 'request') or (`shift`.`type` = 'denied')),0,if(`shift`.`doubletime`,((trim(((time_to_sec(timediff(if((`shift`.`start_time` <= `shift`.`end_time`),`shift`.`end_time`,addtime(`shift`.`end_time`,'24:00:00')),`shift`.`start_time`)) / 3600) + coalesce(`shift`.`mod_time`,0))) + 0) * 2),(trim(((time_to_sec(timediff(if((`shift`.`start_time` <= `shift`.`end_time`),`shift`.`end_time`,addtime(`shift`.`end_time`,'24:00:00')),`shift`.`start_time`)) / 3600) + coalesce(`shift`.`mod_time`,0))) + 0))) AS `volhours`,`shift`.`start_time` AS `start_time`,`shift`.`end_time` AS `end_time`,`shift`.`doubletime` AS `doubletime`,`shift`.`note` AS `note`,(trim(`shift`.`mod_time`) + 0) AS `mod_time`,`shift`.`assignee_id` AS `RCid`,`official`.`derby_name` AS `derby_name`,`official`.`real_name` AS `full_name` from (`shift` left join `official` on((`shift`.`assignee_id` = `official`.`RCid`)))