<!--月度考勤汇总list列表-->
<select id="queryAttendanceSummaryList" resultType="org.jeecg.modules.oa.vo.AttendanceSummaryMonthVo">
SELECT
a1.id AS employeeId,
a1.organization_id AS organizationId,
a1.department AS department,
a1.employee_number AS employeeNumber,
a1.position AS position,
IFNULL((select count(distinct DATE_FORMAT(checktime, '%Y-%m-%d')) from oa_checkin where user_number = a1.employee_number AND DATE_FORMAT(checktime, '%Y-%m') = #{date}),0) as 'attendanceDays',
<!--查询当前月天数(2020-09-01)传入值带年月日
当前月天数减去打卡天数,值为未打卡天数-->
IFNULL((SELECT DAYOFMONTH(LAST_DAY(#{dateNum}))-IFNULL((select count(distinct DATE_FORMAT(checktime, '%Y-%m-%d')) from oa_checkin where user_number = a1.employee_number AND DATE_FORMAT(checktime, '%Y-%m') = #{date}),0)),0) as 'notClockedDays',
<!--计算请假天数,人员id=申请人id -->
IFNULL((SELECT sum(days) from oa_attendance b where b.user_id = a1.id and b.approval_status = '3' and b.oa_type = '1' AND DATE_FORMAT(start_time, '%Y-%m') = #{date}),0) as 'vacationDays',
IFNULL((SELECT sum(days) from oa_attendance b where b.user_id = a1.id and b.approval_status = '3' and b.oa_type = '2' AND DATE_FORMAT(start_time, '%Y-%m') = #{date}),0) as 'outworkDays',
IFNULL((SELECT sum(days) from oa_attendance b where b.user_id = a1.id and b.approval_status = '3' and b.oa_type = '3' AND DATE_FORMAT(start_time, '%Y-%m') = #{date}),0) as 'travelDays',
IFNULL((SELECT sum(days) from oa_overtime_leave b where b.employee_id = a1.id and b.approval_status = '3' and b.overtime_leave_type = '1' AND DATE_FORMAT(start_time, '%Y-%m') = #{date}),0) as 'overtimeDays',
IFNULL((SELECT sum(days) from oa_overtime_leave b where b.employee_id = a1.id and b.approval_status = '3' and b.overtime_leave_type = '2' AND DATE_FORMAT(start_time, '%Y-%m') = #{date}),0) as 'leaveOffDays'
FROM
org_employee a1
WHERE
a1.state = '0' and a1.del_flag = '0'
<if test="department != null and department != ''">
AND a1.department = #{department}
</if>
<if test="employeeId != null and employeeId != ''">
AND a1.id = #{employeeId}
</if>
<if test="organizationId != null and organizationId != ''">
AND a1.organization_id = #{organizationId}
</if>
ORDER BY (attendanceDays+0) desc
</select>
sql拼接查询
最新推荐文章于 2023-07-03 10:34:58 发布