select * from (select re.recruit_name,
re.emp_no,
re.psDeptname,
re.branch_name2,
re.branch_name3,
re.branch_name4,
to_char(ci.check_time, 'yyyy/mm/dd hh24:mi') check_time,
re.checkin_name,
decode(ci.checked, null, '未打卡', 'N', '未打卡', 'Y','已打卡', 'R', '代打卡') checked,
ci.checked checkin_state,
ci.remark,
ci.operator
from (select r.recruit_name,
'' psDeptname,
tt.emp_no,
decode(br.branch_code2,
null,
decode(r.branch_code, '86', '总公司', null),
br.branch_name2) branch_name2,
br.branch_name3,
br.branch_name4,
cr.checkin_name,
cr.rule_no,
cr.start_time
from checkin_rule cr,
train_trainee tt,
recruit r,
branch_relation br
where cr.is_valid = 'Y'
and tt.is_valid = 'Y'
and r.is_valid = 'Y'
and cr.train_no = #{marketCheckInBO.trainNo}
<if test="marketCheckInBO.ruleNo != null and marketCheckInBO.ruleNo != ''">
and cr.rule_no = #{marketCheckInBO.ruleNo}
</if>
<if test="marketCheckInBO.checkinSeqList != null and marketCheckInBO.checkinSeqList.size > 0">
and cr.checkin_seq in
<foreach collection="marketCheckInBO.checkinSeqList" item="seq" open="(" close=")" separator=",">
#{seq}
</foreach>
</if>
and tt.trainee_type = 3
<if test="marketCheckInBO.branchCode != null and marketCheckInBO.branchCode != ''">
and exists (select 1
from branch_info bi
where r.branch_code = bi.branch_code
start with bi.branch_code = #{marketCheckInBO.branchCode}
connect by prior bi.branch_code = bi.parent_branch)
</if>
<if test="marketCheckInBO.recruitName != null and marketCheckInBO.recruitName != ''">
and r.recruit_name like '%'||#{marketCheckInBO.recruitName}||'%'
</if>
<if test="marketCheckInBO.empNo != null and marketCheckInBO.empNo != ''">
and r.id_no = #{marketCheckInBO.empNo}
</if>
and tt.train_no = cr.train_no
and r.id_no = tt.emp_no
and br.branch_code = r.branch_code) re,
check_in ci
where ci.is_valid(+) = 'Y'
and ci.rule_no(+) = re.rule_no
and ci.emp_no(+) = re.emp_no
order by re.start_time desc,
ci.checked asc,
ci.check_time desc,
re.branch_name4 asc,
re.recruit_name asc
) t
union
select * from (select re.recruit_name,
re.emp_no,
re.psDeptname,
re.branch_name2,
re.branch_name3,
re.branch_name4,
to_char(ci.check_time, 'yyyy/mm/dd hh24:mi') check_time,
re.checkin_name,
decode(ci.checked, null, '未打卡', 'N', '未打卡', 'Y','已打卡', 'R', '代打卡') checked,
ci.checked checkin_state,
ci.remark,
ci.operator
from (select st.emp_name recruit_name,
'' psDeptname,
tt.emp_no,
br.branch_name2,
br.branch_name3,
br.branch_name4,
cr.checkin_name,
cr.rule_no,
cr.start_time
from checkin_rule cr,train_trainee tt,staff_info st left join dept_info di on st.department_no = di.dept_no
left join branch_relation br on di.branch_code = br.branch_code
where cr.is_valid = 'Y'
and tt.is_valid = 'Y'
and st.is_valid = 'Y'
and cr.train_no = #{marketCheckInBO.trainNo}
<if test="marketCheckInBO.branchCode != null and marketCheckInBO.branchCode != ''">
AND br.branch_code IN
(SELECT
bi.branch_code
FROM branch_info bi
START WITH bi.branch_code = #{marketCheckInBO.branchCode}
CONNECT BY PRIOR bi.branch_code = bi.parent_branch)
</if>
<if test="marketCheckInBO.recruitName != null and marketCheckInBO.recruitName != ''">
AND st.EMP_NAME LIKE '%'||#{marketCheckInBO.recruitName}||'%'
</if>
<if test="marketCheckInBO.empNo != null and marketCheckInBO.empNo != ''">
AND tt.EMP_NO = #{marketCheckInBO.empNo}
</if>
<if test="marketCheckInBO.ruleNo != null and marketCheckInBO.ruleNo != ''">
and cr.rule_no = #{marketCheckInBO.ruleNo}
</if>
and tt.trainee_type =1
and tt.train_no = cr.train_no
and tt.emp_no = st.emp_no
) re,
check_in ci
where ci.is_valid(+) = 'Y'
and ci.rule_no(+) = re.rule_no
and ci.emp_no(+) = re.emp_no
order by re.start_time desc,
ci.checked asc,
ci.check_time desc,
re.branch_name4 asc,
re.recruit_name asc
)t
union
select * from (
select re.recruit_name,
re.emp_no,
re.psDeptname,
re.branch_name2,
re.branch_name3,
re.branch_name4,
to_char(ci.check_time, 'yyyy/mm/dd hh24:mi') check_time,
re.checkin_name,
decode(ci.checked, null, '未打卡', 'N', '未打卡', 'Y','已打卡', 'R', '代打卡') checked,
ci.checked checkin_state,
ci.remark,
ci.operator
from (select TEMP.recruit_name,
TEMP.emp_no,
TEMP.psDeptname,
'' branch_name2,
'' branch_name3,
'' branch_name4,
cr.checkin_name,
cr.rule_no,
cr.start_time
from checkin_rule cr,
(select * from (
SELECT EMPO.*,
ROW_NUMBER() OVER(PARTITION BY EMPO.EMP_NO ORDER BY EMPO.CREATED_DATE ASC) RN
FROM (
select tt.EMP_NO,
tt.CREATED_DATE,
te.name recruit_name,
te.psDeptname psDeptname,
tt.train_no
from TRAIN_TRAINEE tt,
(select distinct psop.emplid emplId,
psop.name_display name,
pod.ps_deptname psDeptname
from ps_oa_personaldata psop,
ps_oa_job poa,
ps_oa_dept pod,
ps_oa_ad_user poau
where psop.emplid = poa.emplid
and poa.deptid = pod.deptid
and psop.emplid = poau.emplid
and psop.is_valid_flag = 'Y'
and poa.is_valid = 'Y'
and pod.is_valid = 'Y'
and poa.EMPL_RCD = '0'
and psop.business_unit = 'SINOL'
) te
where tt.IS_VALID = 'Y'
and tt.trainee_type = '2'
and te.emplid(+) = tt.emp_no
<if test="marketCheckInBO.recruitName != null and marketCheckInBO.recruitName != ''">
AND te.name LIKE '%'||#{marketCheckInBO.recruitName}||'%'
</if>
<if test="marketCheckInBO.empNo != null and marketCheckInBO.empNo != ''">
AND tt.emp_no = #{marketCheckInBO.empNo}
</if>
<if test="marketCheckInBO.psDeptname != null and marketCheckInBO.psDeptname != ''">
AND te.psDeptname LIKE '%'||#{marketCheckInBO.psDeptname}||'%'
</if>
AND tt.TRAIN_NO = #{marketCheckInBO.trainNo}
order by tt.CREATED_DATE
) EMPO) PO
WHERE RN = 1) TEMP
where cr.is_valid = 'Y'
and TEMP.train_no = cr.train_no
and cr.train_no = #{marketCheckInBO.trainNo}
<if test="marketCheckInBO.ruleNo != null and marketCheckInBO.ruleNo != ''">
and cr.rule_no = #{marketCheckInBO.ruleNo}
</if>
) re,
check_in ci
where ci.is_valid(+) = 'Y'
and ci.rule_no(+) = re.rule_no
and ci.emp_no(+) = re.emp_no
order by re.start_time desc,
ci.checked asc,
ci.check_time desc,
re.branch_name4 asc,
re.recruit_name asc
)t