<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.bonus.system.att.dao.AttendanceDetailsDao">

    <select id="selectAttDetailsList" resultType="com.bonus.system.att.entity.AttDataDetailsBean">
        select su.user_name, so.org_name,IF(awa.outCount is null,0,awa.outCount) as outCount,
               COUNT(DISTINCT asd.att_current_time ) AS attCount,
               v.* from v_att_update_data v
        left join sys_user su on su.user_id = v.user_id
        left join sys_organization so on so.id = v.org_id
        LEFT JOIN (SELECT user_id,att_current_day,count(user_id) as outCount  FROM att_work_abnormal GROUP BY att_current_day,user_id) awa ON v.user_id = awa.user_id
        AND v.att_current_day = awa.att_current_day
        LEFT JOIN att_source_data asd ON su.user_name = asd.`name` and asd.att_current_day = v.att_current_day
        where v.att_current_day BETWEEN #{bean.startDate} AND #{bean.endDate}
        <if test="bean.orgId != null and bean.orgId != ''">
            AND v.org_id = #{bean.orgId}
        </if>
        <if test="bean.userId != null and bean.userId != ''">
            AND v.user_id = #{bean.userId}
        </if>

        <if test="bean.userName != null and bean.userName != ''">
            AND su.user_name like concat('%', #{bean.userName}, '%')
        </if>
        <if test='bean.attStatus != null and bean.attStatus == "6"'>
            and (v.toWorkAttStatus in (SELECT dict_value
            FROM sys_dict_data
            where dict_type = 'att_status' and is_leave = '1') or v.offWorkAttStatus in (SELECT dict_value
            FROM sys_dict_data
            where dict_type = 'att_status' and is_leave = '1'))
        </if>
        <if test='bean.attStatus != null and bean.attStatus != "6" and bean.attStatus!="27"'>
            <if test='bean.attStatus == "1"'>
                and ( toWorkAttStatus = #{bean.attStatus} and offWorkAttStatus = #{bean.attStatus} )
            </if>
            <if test='bean.attStatus != "1"'>
                and ( toWorkAttStatus = #{bean.attStatus} or offWorkAttStatus = #{bean.attStatus} )
            </if>
        </if>
        <if test='bean.attStatus != null and bean.attStatus == "27"'>
            and (v.toWorkAttStatus in (SELECT dict_value
            FROM sys_dict_data
            where dict_type = 'att_status' and is_leave IN ('1','2')) or v.offWorkAttStatus in (SELECT dict_value
            FROM sys_dict_data
            where dict_type = 'att_status' and is_leave IN ('1','2')))
            AND (v.offWorkAttCurrentTime IS NOT NULL OR v.toWorkAttCurrentTime IS NOT NULL)
        </if>
        <if test='bean.orgList != null and bean.orgList.size() > 0'>
            and v.org_id in (
            <foreach collection="bean.orgList" item="item" separator=",">
                #{item}
            </foreach>
            )
        </if>

        GROUP BY
        v.att_current_day,
        v.user_id
        ORDER BY
        v.att_current_day DESC
    </select>

    <select id="exportAttDetailsList" resultType="com.bonus.system.att.entity.AttDataDetailsBean">
        SELECT
        v.user_id,
        su.user_name,
        GROUP_CONCAT(DISTINCT so.org_name) as orgName,
        v.att_current_day,
        v.toWorkAttStatus,
        v.toWorkAttAddress,
        v.toWorkErrorRemake,
        v.offWorkAttStatus,
        v.offWorkAttAddress,
        v.offWorkErrorRemake
        FROM
        v_att_update_data v
        LEFT JOIN sys_user su ON su.user_id = v.user_id
        LEFT JOIN sys_organization so ON so.id = v.org_id
        LEFT JOIN (SELECT user_id,att_current_day,count(user_id) as outCount  FROM att_work_abnormal GROUP BY att_current_day,user_id) awa ON v.user_id = awa.user_id
        AND v.att_current_day = awa.att_current_day
        LEFT JOIN att_source_data asd ON su.user_name = asd.`name` and asd.att_current_day = v.att_current_day
        WHERE
        v.att_current_day BETWEEN #{bean.startDate} AND #{bean.endDate} and su.is_active = 1
        <if test="bean.orgId != null and bean.orgId != ''">
            AND v.org_id = #{bean.orgId}
        </if>
        <if test="bean.userName != null and bean.userName != ''">
            AND su.user_name like concat('%', #{bean.userName}, '%')
        </if>

        <if test="bean.userId != null and bean.userId != ''">
            AND v.user_id = #{bean.userId}
        </if>

        <if test='bean.attStatus != null and bean.attStatus == "6"'>
            and (v.toWorkAttStatus in (SELECT dict_value
            FROM sys_dict_data
            where dict_type = 'att_status' and is_leave = '1') or v.offWorkAttStatus in (SELECT dict_value
            FROM sys_dict_data
            where dict_type = 'att_status' and is_leave = '1'))
        </if>
        <if test='bean.attStatus != null and bean.attStatus != "6"'>
            <if test='bean.attStatus == "1"'>
                and ( toWorkAttStatus = #{bean.attStatus} and offWorkAttStatus = #{bean.attStatus} )
            </if>
            <if test='bean.attStatus != "1"'>
                and ( toWorkAttStatus = #{bean.attStatus} or offWorkAttStatus = #{bean.attStatus} )
            </if>
        </if>

        <if test='bean.orgList != null and bean.orgList.size() > 0'>
            and v.org_id in (
            <foreach collection="bean.orgList" item="item" separator=",">
                #{item}
            </foreach>
            )
        </if>

        GROUP BY v.user_id,v.att_current_day
        ORDER BY CHAR_LENGTH(GROUP_CONCAT(DISTINCT so.org_name)) DESC
    </select>

    <select id="exportAttOriginalsList" resultType="com.bonus.system.att.entity.AttDataDetailsBean">
        SELECT
        v.user_id,
        su.user_name,
        GROUP_CONCAT(DISTINCT so.org_name) as orgName,
        v.att_current_day,
        v.toWorkAttStatus,
        v.toWorkAttAddress,
        v.toWorkErrorRemake,
        v.offWorkAttStatus,
        v.offWorkAttAddress,
        v.offWorkErrorRemake
        FROM
        v_att_data v
        LEFT JOIN sys_user su ON su.user_id = v.user_id
        LEFT JOIN sys_organization so ON so.id = v.org_id
        LEFT JOIN (SELECT user_id,att_current_day,count(user_id) as outCount  FROM att_work_abnormal GROUP BY att_current_day,user_id) awa ON v.user_id = awa.user_id
        AND v.att_current_day = awa.att_current_day
        LEFT JOIN att_source_data asd ON su.user_name = asd.`name` and asd.att_current_day = v.att_current_day
        WHERE
        v.att_current_day BETWEEN #{bean.startDate} AND #{bean.endDate} and su.is_active = 1
        <if test="bean.orgId != null and bean.orgId != ''">
            AND v.org_id = #{bean.orgId}
        </if>
        <if test="bean.userName != null and bean.userName != ''">
            AND su.user_name like concat('%', #{bean.userName}, '%')
        </if>
        <if test="bean.userId != null and bean.userId != ''">
            AND v.user_id = #{bean.userId}
        </if>

        <if test='bean.attStatus != null and bean.attStatus == "6"'>
            and (v.toWorkAttStatus in (SELECT dict_value
            FROM sys_dict_data
            where dict_type = 'att_status' and is_leave = '1') or v.offWorkAttStatus in (SELECT dict_value
            FROM sys_dict_data
            where dict_type = 'att_status' and is_leave = '1'))
        </if>
        <if test='bean.attStatus != null and bean.attStatus != "6"'>
            <if test='bean.attStatus == "1"'>
                and ( toWorkAttStatus = #{bean.attStatus} and offWorkAttStatus = #{bean.attStatus} )
            </if>
            <if test='bean.attStatus != "1"'>
                and ( toWorkAttStatus = #{bean.attStatus} or offWorkAttStatus = #{bean.attStatus} )
            </if>
        </if>

        <if test='bean.orgList != null and bean.orgList.size() > 0'>
            and v.org_id in (
            <foreach collection="bean.orgList" item="item" separator=",">
                #{item}
            </foreach>
            )
        </if>

        GROUP BY v.user_id,v.att_current_day
        ORDER BY CHAR_LENGTH(GROUP_CONCAT(DISTINCT so.org_name)) DESC
    </select>

    <select id="selectAttOriginalList" resultType="com.bonus.system.att.entity.AttDataDetailsBean">
        select su.user_name, so.org_name, v.* from v_att_data v
        left join sys_user su on su.user_id = v.user_id
        left join sys_organization so on so.id = v.org_id
        where v.att_current_day BETWEEN #{bean.startDate} AND #{bean.endDate} and su.is_active = 1
        <if test="bean.orgId != null and bean.orgId != ''">
            AND v.org_id = #{bean.orgId}
        </if>
        <if test="bean.userName != null and bean.userName != ''">
            AND su.user_name like concat('%', #{bean.userName}, '%')
        </if>
        <if test="bean.attStatus != null and bean.attStatus != ''">
            AND (v.toWorkAttStatus = #{bean.attStatus} OR v.offWorkAttStatus = #{bean.attStatus})
        </if>
        <if test="bean.roleType =='2' || bean.roleType == 2 ">
            and v.org_id in (#{bean.orgListId})
        </if>
        <if test="bean.roleType =='3' || bean.roleType == 3 ">
            and su.user_id = #{bean.userId}
        </if>
        <if test='bean.orgIdList != null and bean.orgIdList.size() > 0'>
            and v.org_id in (
            <foreach collection="bean.orgIdList" item="item" separator=",">
                #{item}
            </foreach>
            )
        </if>

        <if test='bean.orgList != null and bean.orgList.size() > 0'>
            and v.org_id in (
            <foreach collection="bean.orgList" item="item" separator=",">
                #{item}
            </foreach>
            )
        </if>
        ORDER BY att_current_day DESC
    </select>

    <select id="selectAttUpdateList" resultType="com.bonus.system.att.entity.AttDataDetailsBean">
        select su.user_name, so.org_name, v.* from v_att_update_data v
        left join sys_user su on su.user_id = v.user_id
        left join sys_organization so on so.id = v.org_id
        where v.att_current_day BETWEEN #{startDate} AND #{endDate} and v.is_update = 1 and su.is_active = 1
        <if test="orgId != null and orgId != ''">
            AND v.org_id = #{orgId}
        </if>
        <if test='orgList != null and orgList.size() > 0'>
            and v.org_id in (
            <foreach collection="orgList" item="item" separator=",">
                #{item}
            </foreach>
            )
        </if>
        <if test="userId != null and userId != ''">
            AND v.user_id = #{userId}
        </if>
        <if test="userName != null and userName != ''">
            AND su.user_name like concat('%', #{userName}, '%')
        </if>
        <if test="attStatus != null and attStatus != ''">
            AND v.toWorkAttStatus = #{attStatus} AND v.offWorkAttStatus = #{attStatus}
        </if>
    </select>

    <select id="getAttDayReportList" resultType="com.bonus.system.att.entity.AttDayReportBean">
        SELECT
            adr.*,
            IF
            ( awa.outCount IS NULL, 0, awa.outCount ) AS outCount
        FROM
            att_day_report adr
                LEFT JOIN ( SELECT att_current_day,  count( DISTINCT user_id ) AS outCount FROM att_work_abnormal GROUP BY att_current_day ) awa ON awa.att_current_day = adr.att_current_day
        WHERE adr.att_current_day BETWEEN #{startDate} AND #{endDate}
    </select>

    <select id="getAttMonthReportList" resultType="com.bonus.system.att.entity.AttMonthReportBean">
        select v.*, v.name as userName,IF(awa.outCount is null,0,awa.outCount) as outCount
        from att_month_report v
        left join att_group_person_relation p on p.user_id = v.user_id and p.is_active = 1
        LEFT JOIN (SELECT user_id,count(user_id) as outCount  FROM att_work_abnormal WHERE LOCATE(#{bean.month},att_current_day)  GROUP BY user_id) awa ON awa.user_id = v.user_id
        where v.att_current_month BETWEEN #{bean.startMonth} AND #{bean.endMonth} and p.group_id is not null
        <if test="bean.orgId != null and bean.orgId != ''">
            AND v.org_id = #{bean.orgId}
        </if>
        <if test="bean.userName != null and bean.userName != ''">
            AND v.name like concat('%', #{bean.userName}, '%')
        </if>

        <if test="bean.userId != null and bean.userId != ''">
            AND v.user_id = #{bean.userId}
        </if>

        <if test='bean.orgList != null and bean.orgList.size() > 0'>
            and v.org_id in (
            <foreach collection="bean.orgList" item="item" separator=",">
                #{item}
            </foreach>
            )
        </if>
    </select>

    <select id="getAttDayReportDetailsList" resultType="com.bonus.system.att.entity.AttDataDetailsBean">
        select su.user_name, so.org_name, v.* from v_att_update_data v
        left join sys_user su on su.user_id = v.user_id
        left join sys_organization so on so.id = v.org_id
        <where>
            <if test="attCurrentDay != null and attCurrentDay != ''">
                AND att_current_day = #{attCurrentDay}
            </if>
            <if test="startDate != null and startDate != ''">
                AND att_current_day between #{startDate} AND #{endDate}
            </if>
            <if test='attStatus != null and attStatus == "6"'>
                and (toWorkAttStatus in (SELECT dict_value
                FROM sys_dict_data
                where dict_type = 'att_status' and is_leave = '1') or offWorkAttStatus in (SELECT dict_value
                FROM sys_dict_data
                where dict_type = 'att_status' and is_leave = '1'))
            </if>
            <if test='attStatus != null and attStatus != "6"'>
                <if test='attStatus == "1"'>
                    and ( toWorkAttStatus = #{attStatus} and offWorkAttStatus = #{attStatus} )
                </if>
                <if test='attStatus != "1"'>
                    and ( toWorkAttStatus = #{attStatus} or offWorkAttStatus = #{attStatus} )
                </if>
            </if>
            <if test="userName != null and userName != ''">
                AND su.user_name like concat('%', #{userName}, '%')
            </if>
        </where>
        ORDER BY att_current_day DESC
    </select>
    <select id="getOutCountList" resultType="com.bonus.system.att.entity.AttDataDetailsBean">
        SELECT
        awa.user_name,
        so.org_name,
        awa.att_current_day,
        awa.att_current_time,
        awa.att_address
        FROM
        att_work_abnormal awa
        LEFT JOIN sys_organization so ON so.id = awa.org_id and so.is_active = '1'
        <where>
            <if test="bean.attCurrentDay != null and bean.attCurrentDay != ''">
                and awa.att_current_day = #{bean.attCurrentDay}
            </if>
            <if test="bean.startDate != null and bean.startDate != '' and bean.endDate != null and bean.endDate != ''">
                and awa.att_current_day between #{bean.startDate} AND #{bean.endDate}
            </if>
            <if test="bean.userId != null and bean.userId != ''">
                and awa.user_id = #{bean.userId}
            </if>
            <if test="bean.userName != null and bean.userName != ''">
                and locate(#{bean.userName},awa.user_name)
            </if>
            <if test="bean.attCurrentMonth != null and bean.attCurrentMonth != ''">
                and locate(#{bean.attCurrentMonth},awa.att_current_day)
            </if>

            <if test='bean.orgList != null and bean.orgList.size() > 0'>
                and so.id in (
                <foreach collection="bean.orgList" item="item" separator=",">
                    #{item}
                </foreach>
                )
            </if>
        </where>
        ORDER BY
        awa.att_current_day DESC
    </select>

    <select id="getAttDayList" resultType="com.bonus.system.att.entity.AttDataDetailsBean">
        SELECT
        su.user_name, so.org_name, a.*
        FROM
        sys_user su
        LEFT JOIN v_att_update_data a ON a.user_id = su.user_id
        LEFT JOIN att_group_person_relation agpr ON agpr.user_id = su.user_id
        LEFT JOIN sys_user_org org ON org.user_id = su.user_id
        left join sys_organization so on so.id = org.org_id
        WHERE su.is_active = 1 AND agpr.is_active = 1
        <if test="attCurrentDay != null and attCurrentDay != ''">
            AND att_current_day = #{attCurrentDay}
        </if>
        <if test="attendType == 1 || attendType == '1'">
            <if test='attStatus == "1"'>
                and ( toWorkAttStatus = #{attStatus} OR offWorkAttStatus = #{attStatus} )
            </if>
        </if>
        <if test="attendType == 2 || attendType == '2'">
            AND su.user_id not in (
                SELECT su.user_id FROM sys_user su
                LEFT JOIN v_att_update_data a ON a.user_id = su.user_id
                LEFT JOIN att_group_person_relation agpr ON agpr.user_id = su.user_id
                LEFT JOIN sys_user_org org ON org.user_id = su.user_id
                left join sys_organization so on so.id = org.org_id
                WHERE su.is_active = 1 AND agpr.is_active = 1
                AND att_current_day = #{attCurrentDay}
                and ( toWorkAttStatus = #{attStatus} OR offWorkAttStatus = #{attStatus} )
            )
        </if>
        <if test="attStatusToday != null and attStatusToday != ''">
            and ( toWorkAttStatus = #{attStatusToday} or offWorkAttStatus = #{attStatusToday} )
        </if>
        <if test="userName != null and userName != ''">
            AND su.user_name like concat('%', #{userName}, '%')
        </if>
        GROUP BY agpr.user_id
    </select>
    <select id="getAttDayReportDetailsListData" resultType="com.bonus.system.att.entity.AttDataDetailsBean">
        select su.user_name, so.org_name, v.* from v_att_update_data v
        left join sys_user su on su.user_id = v.user_id
        left join sys_organization so on so.id = v.org_id
        <where>
            <if test="attCurrentDay != null and attCurrentDay != ''">
                AND att_current_day = #{attCurrentDay}
            </if>
            <if test="startDate != null and startDate != ''">
                AND att_current_day between #{startDate} AND #{endDate}
            </if>
            <if test='attStatus != null and attStatus == "6"'>
                and (toWorkAttStatus in (SELECT dict_value
                FROM sys_dict_data
                where dict_type = 'att_status' and is_leave = '1') or offWorkAttStatus in (SELECT dict_value
                FROM sys_dict_data
                where dict_type = 'att_status' and is_leave = '1'))
            </if>
            <if test='attStatus != null and attStatus != "6"'>
                <if test='attStatus == "1"'>
                    and ( toWorkAttStatus = #{attStatus} or offWorkAttStatus = #{attStatus} )
                </if>
                <if test='attStatus != "1"'>
                    and ( toWorkAttStatus = #{attStatus} or offWorkAttStatus = #{attStatus} )
                </if>
            </if>
            <if test="userName != null and userName != ''">
                AND su.user_name like concat('%', #{userName}, '%')
            </if>
        </where>
        ORDER BY att_current_day DESC
    </select>
    <select id="getAttCountList" resultType="com.bonus.system.att.entity.AttDataDetailsBean">
        SELECT
            att_current_day,att_current_time,att_address
        from
            att_source_data
        <where>
            <if test="attCurrentDay != null and attCurrentDay != ''">
                and att_current_day = #{attCurrentDay}
            </if>
            <if test="userName != null and userName != ''">
                and `name` = #{userName}
            </if>
        </where>
        ORDER BY
            att_current_time ASC
    </select>

    <select id="getOrgUserOutCountList" resultType="com.bonus.system.att.entity.AttDataDetailsBean">
        SELECT
            awa.user_name,
            so.org_name,
            awa.att_current_day,
            awa.att_current_time,
            awa.att_address
        FROM
            sys_user_org suo
                INNER JOIN att_work_abnormal awa ON suo.user_id = awa.user_id
                LEFT JOIN sys_organization so ON so.id = suo.org_id
        <where>
            suo.is_active = '1'
            <if test="bean.attCurrentDay != null and bean.attCurrentDay != ''">
                and awa.att_current_day = #{bean.attCurrentDay}
            </if>
            <if test="bean.startDate != null and bean.startDate != '' and bean.endDate != null and bean.endDate != ''">
                  AND awa.att_current_day BETWEEN #{bean.startDate} AND #{bean.endDate}
            </if>
            <if test="bean.userName != null and bean.userName != ''">
                and locate(#{bean.userName},awa.user_name)
            </if>
            <if test="bean.orgId != null and bean.orgId != ''">
                and so.id = #{bean.orgId}
            </if>
            <if test='bean.orgList != null and bean.orgList.size() > 0'>
                and so.id in (
                <foreach collection="bean.orgList" item="item" separator=",">
                    #{item}
                </foreach>
                )
            </if>
        </where>
        ORDER BY
        awa.att_current_day DESC
    </select>
    <select id="getAttStatusList" resultType="com.bonus.system.api.domain.MapVo">
        SELECT
            dict_sort as id,
            dict_label as name
        FROM
            sys_dict_data
        WHERE
            dict_type = 'att_status'
        ORDER BY
            dict_sort ASC
    </select>
    <!--查询打卡记录-->
    <select id="getClockingRecordNum" resultType="java.lang.Integer">
        SELECT COUNT(*) FROM att_source_data
                        WHERE name = #{c.userName} AND DATE_FORMAT(att_current_day,'%Y-%m') = #{c.attCurrentMonth} AND is_active = '1'
    </select>
    <!--考勤报表->月报表->打卡记录-->
    <select id="getClockingRecordListByUserId" resultType="com.bonus.system.att.entity.AttClockingRecordVo">
        SELECT att_current_day AS attCurrentDay,
               att_current_time AS attCurrentTime,
               att_type AS attType,
               att_address AS attAddress,
               remark
        FROM att_source_data
        WHERE name = #{userName} AND DATE_FORMAT(att_current_day,'%Y-%m') = #{month} AND is_active = '1'
    </select>
    <!--查询考勤修改的审核状态-->
    <select id="getAuditStatus" resultType="java.lang.Integer">
        SELECT reviewer_status
        FROM att_data_update
        WHERE user_id = #{userId} AND att_current_day = #{attCurrentDay} AND org_id = #{orgId} LIMIT 1
    </select>

    <update id="updateAttDetailsData">
        <foreach collection="list" item="params" separator=";">
            update att_data_update set update_att_current_time = #{params.toWorkAttCurrentTime},
            update_att_status = #{params.toWorkAttStatus}, reviewer_status = 0, is_update = 1,manager_audit_status = 0,
            reason_content = #{params.toErrorRemake}
            where user_id = #{params.userId} and org_id = #{params.orgId} and att_type = 1
            and att_current_day = #{params.attCurrentDay};

            update att_data_update set update_att_current_time = #{params.offWorkAttCurrentTime},
            update_att_status = #{params.offWorkAttStatus}, reviewer_status = 0, is_update = 1,manager_audit_status = 0,
            reason_content = #{params.offErrorRemake}
            where user_id = #{params.userId} and org_id = #{params.orgId} and att_type = 2
            and att_current_day = #{params.attCurrentDay}
        </foreach>
    </update>

    <update id="updateAttDetailsDataExamine">
        <foreach collection="list" item="params" separator=";">
            <if test="params.currentAuditStatus == 0">
                update att_data_update set reviewer_status = #{params.reviewerStatus},
                manager_audit_id = #{params.managerAuditId}, manager_audit_time = #{params.managerAuditTime},
                manager_audit_status = #{params.managerAuditStatus}
                where user_id = #{params.userId} and org_id = #{params.orgId} and att_type = 1
                and att_current_day = #{params.attCurrentDay};

                update att_data_update set reviewer_status = #{params.reviewerStatus},
                manager_audit_id = #{params.managerAuditId}, manager_audit_time = #{params.managerAuditTime},
                manager_audit_status = #{params.managerAuditStatus}
                where user_id = #{params.userId} and org_id = #{params.orgId} and att_type = 2
                and att_current_day = #{params.attCurrentDay}
            </if>
            <if test="params.currentAuditStatus == 3">
                update att_data_update set reviewer_status = #{params.reviewerStatus},
                hr_audit_id = #{params.hrAuditId}, hr_audit_time = #{params.hrAuditTime},
                hr_audit_status = #{params.hrAuditStatus}
                where user_id = #{params.userId} and org_id = #{params.orgId} and att_type = 1
                and att_current_day = #{params.attCurrentDay};

                update att_data_update set reviewer_status = #{params.reviewerStatus},
                hr_audit_id = #{params.hrAuditId}, hr_audit_time = #{params.hrAuditTime},
                hr_audit_status = #{params.hrAuditStatus}
                where user_id = #{params.userId} and org_id = #{params.orgId} and att_type = 2
                and att_current_day = #{params.attCurrentDay}
            </if>
        </foreach>
    </update>
</mapper>