# 2021-03-3 禅道工时

# dev
    doc http://127.0.0.1:8085/doc.html



# 参考
    https://blog.csdn.net/watermelonbig/article/details/81217085
    
    官方开发文档 （有留言，等待答复）
    https://devel.easycorp.cn/book/extension/api-intro-43.html#first
    
    查看任务详情
    http://192.168.0.115:8181/zentao/task-view-4171.html
    
    
# 注意事项
    1. 不用随意升级禅道版本，测试使用windows版本的数据库工时表是用zt_effort, 生产环境使用的是zt_taskestimate
    2. 升级后导致开发统计工时的系统不兼容    
    
    
# sql

    # 该任务耗时详情
    select * FROM zt_taskestimate WHERE task='3656';
   
    # 当月该项目个人工时
    select b.* from zt_taskestimate b left join zt_task a on a.id = b.task where a.project = '13' and b.account = 'linjunbo' and DATE_FORMAT( date, '%Y%m' ) = DATE_FORMAT('2021-03-1' , '%Y%m' ) 
    
    # 个人在当月所有项目的工时详情
    select a.*,b.project,c.name as projectName,c.code,d.realname as realname from zt_taskestimate a left join zt_task b on b.id = a.task left join zt_project c on c.id=b.project left join zt_user d  on d.account = a.account where a.account = 'dengdexian' and DATE_FORMAT( date, '%Y%m' ) = DATE_FORMAT('2021-03-1' , '%Y%m' )
        
    # 单个项目查询每个人的工时
    select b.*,a.project,d.realname, c.`name`, SUM(b.consumed) from zt_taskestimate b left join zt_task a on a.id = b.task left join zt_project c on c.id=a.project  left join zt_user d  on d.account = b.account where a.project = '128' and DATE_FORMAT( date, '%Y%m' ) = DATE_FORMAT('2021-03-1' , '%Y%m' ) GROUP BY account;    
    
    # 所有项目按日期查询
    SELECT a.id, a.account, a.task , b.project, ROUND(SUM(a.consumed/8),2) as consumed,c.code, c.name as projectName, d.realname as realname 
                FROM zt_taskestimate a left join zt_task b on b.id = a.task left join zt_project c on c.id=b.project left join zt_user d  on d.account = a.account 
                WHERE b.deleted = '0' AND DATE_FORMAT( date, '%Y%m' ) = DATE_FORMAT('2021-03-1' , '%Y%m' ) GROUP BY a.account, b.project order by c.id, a.account;
                
                

     
     # 按日期查询工时
     select * from zt_taskestimate where account = 'wuweihao' AND date= '2021-04-02';    
     
     # 个人整个月的工时
     select * from zt_taskestimate where account = 'wufan' AND DATE_FORMAT( date, '%Y%m' ) = DATE_FORMAT('2021-03-1' , '%Y%m' ); 
     
     # 按部门查询当年用时最多的项目工时
     select sum(h.consumed) as consumed, h.projectName from 
     ( select a.task, DATE_FORMAT( a.date, '%Y-%m-%d') as date, a.account, ROUND(a.consumed/8,2) as consumed, b.project, b.name as taskName, c.name as projectName,c.code,d.realname as realname, d.slack, e.id as deptId, e.name as deptName, g.name as moduleName from zt_taskestimate a left join zt_task b on b.id = a.task left join zt_module g on g.id=b.module left join zt_project c on c.id=b.project left join zt_user d on d.account = a.account left join zt_dept e on d.dept = e.id where b.deleted = '0' AND DATE_FORMAT( date, '%Y') = DATE_FORMAT('2024-01-01' , '%Y')  and e.id in (11) order by a.account asc) h 
     group by h.projectName ORDER BY consumed desc;
                
## 日志
   2021-03-24
    目前只部署在本地，没有部署到服务器 http://127.0.0.1:8004/doc.html     

## dev
    2020-04-21
    使用jar包自动访问外部静态资源    
    http://127.0.0.1:8085/doc.html
    
## pro
    2020-04-20
   部署在这个服务器： 192.168.0.115
   只有马瑞有权限
   文件端口映射到8016端口
   地址：/opt/tomcat/zentao_age_8085    
   
   使用nginx 访问外部静态资源   
   sssss         
   
   2025-6-11
   http://192.168.0.115:8085/doc.html#/home