/*创建视图*/
create view v_zentao_bug as 
select `a`.`id` AS `id`,`c`.`name` AS `项目`,case when `a`.`product` = 1 then '服务型' when `a`.`product` = 5 then '销售型' end AS `项目类别`,`a`.`title` AS `标题`,case when `a`.`os` = ',V3' then 'V3' when `a`.`os` = ',V4' then 'V4' when `a`.`os` = ',V6' then 'V6' end AS `系统版本`,`d`.`realname` AS `创建人`,(select `zt`.`realname` from `zt_user` `zt` where `c`.`PM` = `zt`.`account`) AS `对接人`,`e`.`realname` AS `确认人`,(select `us`.`realname` from `zt_user` `us` where `us`.`account` = `a`.`assignedTo`) AS `指派人`,case when `a`.`severity` = 1 then '致命' when `a`.`severity` = 2 then '严重' when `a`.`severity` = 3 then '一般' when `a`.`severity` = 4 then '提示' when `a`.`severity` = 5 then '建议' end AS `严重程度`,case when `a`.`confirmed` = 1 then '已确认' when `a`.`confirmed` = 0 then '未确认' end AS `确认状态`,case when `a`.`status` = 'active' and `a`.`toTask` = 0 then '待处理' when `a`.`status` = 'resolved' then '已解决' when `a`.`status` = 'closed' then '已关闭' when `a`.`resolution` = 'postponed' then '延期处理' when `a`.`toTask` > 0 then '转开发' else '处理中' end AS `bug状态`,(select case when `aa`.`status` = 'wait' then '未开始' when `aa`.`status` = 'doing' then '进行中' when `aa`.`status` = 'done' then '已完成' when `aa`.`status` = 'pause' then '已暂停' when `aa`.`status` = 'cancel' then '已取消' when `aa`.`status` = 'closed' then '已关闭' end from `zt_task` `aa` where `aa`.`id` = `a`.`toTask`) AS `开发状态`,(select `a2`.`name` from (`zt_task` `a1` join `zt_project` `a2`) where `a1`.`execution` = `a2`.`id` and `a1`.`id` = `a`.`toTask`) AS `开发类型`,case when `a`.`resolution` = 'bydesign' then '无需解决' when `a`.`resolution` = 'duplicate' then '重复问题' when `a`.`resolution` = 'external' then '环境问题' when `a`.`resolution` = 'fixed' then '已解决' when `a`.`resolution` = 'notrepro' then '无法重现' when `a`.`resolution` = 'postponed' then '延期处理' when `a`.`resolution` = 'willnotfix' then '不予解决' when `a`.`resolution` = 'others' then '不予解决' when `a`.`resolution` = 'delete' then '拒绝处理' when `a`.`status` = 'active' and `a`.`toTask` = 0 then '待处理' when `a`.`resolution` = 'todeve' or `a`.`toTask` > 0 then '转开发' end AS `解决方案`,case when `a`.`type` = 'codeerror' then '操作问题' when `a`.`type` = 'config' then '配置相关' when `a`.`type` = 'security' then '环境问题' when `a`.`type` = 'install ' then '升级异常' when `a`.`type` = 'performance' then '数据问题' when `a`.`type` = 'standard' then '新需求' when `a`.`type` = 'automation' then '脚本异常' when `a`.`type` = 'designdefect' then '程序bug' when `a`.`type` = 'others' then '其他' when `a`.`resolution` = 'postponed' then '延期处理' when `a`.`status` = 'active' and `a`.`toTask` = 0 then '待处理' when `a`.`toTask` > 0 then '转开发' end AS `BUG类型`,`a`.`openedDate` AS `创建时间`,date_format(`a`.`openedDate`,'%Y-%m-%d') AS `创建日期`,if(date_format(`a`.`openedDate`,'%Y-%m-%d') = date_format(current_timestamp(),'%Y-%m-%d'),'1','0') AS `今日新增`,if(`a`.`confirmed` = 1,cast(timestampdiff(SECOND,`a`.`openedDate`,`b`.`date`) / 3600 as decimal(8,2)),cast(timestampdiff(SECOND,`a`.`openedDate`,current_timestamp()) / 3600 as decimal(8,2))) AS `等待时长`,if(`b`.`date` = '0000-00-00 00:00:00',NULL,`b`.`date`) AS `确认日期`,if(`a`.`resolvedDate` = '0000-00-00 00:00:00',NULL,`a`.`resolvedDate`) AS `解决日期`,`b`.`action` AS `操作类型`,(select replace(replace(max(`aa`.`comment`),'<p>',''),'</p>','') from `zt_action` `aa` where `aa`.`objectID` = `a`.`id` and `aa`.`objectType` = 'bug' and `aa`.`action` = 'resolved' and `aa`.`extra` = 'fixed') AS `备注`,(select replace(replace(max(`aa`.`comment`),'<p>',''),'</p>','') from `zt_action` `aa` where `aa`.`objectID` = `a`.`id` and `aa`.`objectType` = 'bug' and `aa`.`action` = 'resolved' and `aa`.`extra` = 'external') AS `备注2`,(select replace(replace(max(`aa`.`comment`),'<p>',''),'</p>','') from `zt_action` `aa` where `aa`.`objectID` = `a`.`id` and `aa`.`objectType` = 'bug' and `aa`.`action` = 'resolved' and `aa`.`extra` = 'postponed') AS `备注3`,(select replace(replace(max(`aa`.`comment`),'<p>',''),'</p>','') from `zt_action` `aa` where `aa`.`objectID` = `a`.`id` and `aa`.`objectType` = 'bug' and `aa`.`action` = 'resolved' and `aa`.`extra` = 'bydesign') AS `备注4`,`a`.`toTask` AS `taskid`,date_format(`a`.`openedDate`,'%m月') AS `月度`,date_format(`a`.`openedDate`,'%d日') AS `日` from ((((`zt_bug` `a` join `zt_action` `b`) join `zt_project` `c`) join `zt_user` `d`) join `zt_user` `e`) where `a`.`id` = `b`.`objectID` and `b`.`project` = `c`.`id` and `a`.`openedBy` = `d`.`account` and `b`.`actor` = `e`.`account` and `a`.`deleted` = 1 and `b`.`objectType` = 'bug' and `b`.`action` in (if(`a`.`confirmed` = 0,'opened','bugconfirmed'),'resolved') and `b`.`id` = (select max(`aa`.`id`) from `zt_action` `aa` where `aa`.`objectID` = `a`.`id` and `aa`.`action` in ('bugconfirmed','opened','resolved') and `aa`.`objectType` = 'bug');


/*创建视图*/
create table  zentao_bug as select * from v_zentao_bug a;


/*创建触发器*/
create trigger tri_insert_zentao_bug after insert 
    on zt_bug for each row 
    insert into zentao_bug select * from v_zentao_bug a where a.id=new.id;


select * from zentao_bug a where a.id=711;
select * from v_zentao_bug a where a.id=711;

禅道版本 18.7 Linux安装包
操作系统 CentOS
客户端浏览器 Chrome