# work-report

**Repository Path**: lcxm_work_kjlink/work-report

## Basic Information

- **Project Name**: work-report
- **Description**: 工时项目，内部使用
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 1
- **Forks**: 0
- **Created**: 2024-12-31
- **Last Updated**: 2026-05-12

## Categories & Tags

**Categories**: Uncategorized

**Tags**: work, kjlink

## README

# 内部工作/工时日报系统
> start at 20240429  Vic.xu

**需求**：参见[基本需求来源](https://docs.qq.com/sheet/DQ1ptSnVUU1FZS3ll?tab=BB08J2)

**打包** ：`mvn clean package '-Dmaven.test.skip=true' -Denv=prod`
 - `mvn8 clean package '-Dmaven.test.skip=true' -Denv=prod`
### front code
- [v3-report](https://gitee.com/lcxm-front-project/v3-report)

### jira database
```
<jdbc-datasource>
    <url>jdbc:jtds:sqlserver://192.168.80.220:1433/jira</url>
    <driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class>
    <username>sa</username>
    <password>kjlink@DB</password>
    <pool-size>15</pool-size>
  </jdbc-datasource>
```


main sql:
```
-- jira号  assignee reporter content   父jira号   type  status version release_date

select a.pkey AS code, c.pkey  as pcode, 
 a.ASSIGNEE  as assignee, a.REPORTER  as reporter, a.SUMMARY  as  content, 
 d.pname  as type, e.pname  as status,
 g.vname  as version, g.RELEASEDATE  as releaseDate
from jiraissue a 
left join issuelink  b on a.ID  = b.DESTINATION  and b.LINKTYPE  = 10100
left join jiraissue c on c.ID  = b.source 
left join issuetype d on a.issuetype  = d.ID 
left join issuestatus e on a.issuestatus = e.ID 
left join nodeassociation f on a.id = f.SOURCE_NODE_ID and  f.sink_node_entity='Version' and  f.association_type='IssueFixVersion'			
left join projectversion g on f.SINK_NODE_ID  = g.id
where a.pkey like 'ICBCSTL%'
-- and c.pkey is not null
and a.pkey ='ICBCSTL-81816'
;

-- 查询待发布版本 以及为解决的问题数量
 select 
    a.id as pid, 
    a.pname, 
    a.pkey, 
    b.id as vid, 
    b.vname, 
    b.RELEASEDATE as releasedDate, 
    b.DESCRIPTION,
    -- 新增字段：统计对应版本下状态为open/reopen的Jira数量
    (
        select count(j.id) 
        from jiraissue j
        left join nodeassociation na on j.id = na.SOURCE_NODE_ID 
            and na.sink_node_entity='Version' 
            and na.association_type='IssueFixVersion'
        left join issuestatus s on j.issuestatus = s.ID
        where na.SINK_NODE_ID = b.id  -- 关联当前版本
            and s.pname in ('In Progress',  'Open','Reopened')  -- 指定要统计的状态
    ) as unresolvedCount  -- 自定义字段名：问题数量
from  project a 
left join projectversion b on a.id = b.project 
where 1=1 
    and b.RELEASEDATE is not null
and  b.vname = '国银20260129常规版本'
order by b.releasedate desc;
```
some sql:
```
select version.vname,version.releasedate ,jira.pkey			
from  jiraissue jira,nodeassociation node ,projectversion version 			
where jira.pkey='ICBCSTL-84955' and			
jira.id=node.source_node_id  and node.sink_node_entity='Version'			
 and node.association_type='IssueFixVersion'			
and node.sink_node_id=version.id	;;


select * from jiraissue a where a.id in (50214, 23065, 163013)

select * from  jiraissue  where id in ( 

select  id from  issuelink  link where source in
(
select id from  jiraissue  where pkey in ('ICBCSTL-2')
)
)

select * from jiraissue c where c.id = (
select b.SOURCE  from jiraissue a 
left join issuelink b on a.ID  = b.DESTINATION 
where a.pkey = 'ICBCSTL-16988'

) 
select * from  issuelink a where  a.ID  =  '50214';

select * from issuelink   a where a.source = '23065';

SELECT  * from jiraissue a where a.pkey = 'ICBCSTL-2';


select a.user_name , a.last_name  from cwd_user a 

select a.ID , a.pname  from issuetype  a  where 1=1;



```

## 工时统计
- 按项目统计 ：关闭/打开状态
  - 月份统计： 每日统计当月的工时 
  - 年度统计:  每日统计当年
  - 总工时统计: 每日统计总工时