OA加班申请明细表

select flow_ins_id as 流水号,
(case when fi_history='1' then '办结' when fi_history='0' then '在处理' when fi_history='4' then '作废' else '' end ) as 状态,'多人' as 来自单据,
填表人,填表部门,填表日期,登录人单位代码,
加班人工号,加班人姓名,加班人部门,加班人岗位,b.归属日期,b.加班日期 as 开始日期,b.结束日期,
(case when b.扣除用餐时间='1' then '是' else '否' end) as 扣除用餐时间,
b.加班小时, 是否计划调休,
a.加班原因,b.加班类型,班次类型,班次类型名称,本月累计加班小时,属性,是否撒消,
(select end_time from V_OA_FLOW_INST_LIST_H where id =flow_ins_id) AS 完成日期 from C20200509135053 a inner join S20200509135356 b on a.加班人员明细=b.main_id
where fi_history not in ('4')
 AND a.登录人单位代码='${据点}' 
 and datepart(year,b.归属日期)='${年份}' and datepart(month,b.归属日期)='${月份}'
AND  1=1 ${if(len(工号) == 0," ","and 加班人工号= ('" + 工号 + "')")}
AND  1=1 ${if(len(姓名) == 0," ","and 加班人姓名= ('" + 姓名 + "')")}
AND  1=1 ${if(len(部门名称) == 0," ","and 加班人部门= ('" + 部门名称 + "')")}

--查OA部门控制组权限，
and (加班人部门 in (select HR部门名称 from F_OAHR部门控制组 where 工号='${empcode}' and 所属据点='${据点}') or 加班人工号 in (select 工号 from HR人员信息 where 工号='${empcode}') )
 union 

select flow_ins_id as 流水号,
(case when fi_history='1' then '办结' when fi_history='0' then '在处理' when fi_history='4' then '作废' else '' end ) as 状态,'职员' as 来自单据,
填表人,填表部门,填表日期,登录人单位代码,
加班人工号,加班人姓名,加班人部门,加班人岗位,归属日期,加班日期 as 开始日期,结束日期,
(case when 扣除用餐时间='1' then '是' else '否' end) as 扣除用餐时间,
加班小时,
是否计划调休,
加班原因,加班类型,'' as 班次类型,'' as 班次类型名称,本月累计加班小时,属性,是否撒消,(select end_time from V_OA_FLOW_INST_LIST_H where id =flow_ins_id) AS 完成日期 
 from C20200610125704
 where fi_history not in ('4')
 AND 登录人单位代码='${据点}' 
 and datepart(year,归属日期)='${年份}' and datepart(month,归属日期)='${月份}'
AND  1=1 ${if(len(工号) == 0," ","and 加班人工号= ('" + 工号 + "')")}
AND  1=1 ${if(len(姓名) == 0," ","and 加班人姓名= ('" + 姓名 + "')")}
AND  1=1 ${if(len(部门名称) == 0," ","and 加班人部门= ('" + 部门名称 + "')")}
--查OA部门控制组权限，
and (加班人部门 in (select HR部门名称 from F_OAHR部门控制组 where 工号='${empcode}' and 所属据点='${据点}') or 加班人工号 in (select 工号 from HR人员信息 where 工号='${empcode}') )