<?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.AttDetailByMonthDao">

    <select id="getAllParentId" resultType="com.bonus.system.att.entity.AttDetailByMonthBean">
        select id as orgId
        from sys_organization so
        where so.is_active = 1
          and so.parent_id is null
    </select>

    <select id="selectAttDetailList" resultType="com.bonus.system.att.entity.AttDetailByMonthBean">
        SELECT
        amr.user_id AS userId,
        amr.NAME AS NAME,
        amr.org_id AS orgId,
        org_name AS orgName,
        att_current_month AS attCurrentMonth,
        COALESCE ( required_days, 0 ) AS requiredDays,
        COALESCE ( normal_num, 0 ) AS normalNum,
        COALESCE ( late_num, 0 ) AS lateNum,
        COALESCE ( early_num, 0 ) AS earlyNum,
        COALESCE ( skipping_num, 0 ) AS skippingNum,
        COALESCE ( leave_num, 0 ) AS leaveNum,
        COALESCE ( address_error_num, 0 ) AS addressErrorNum,
        COALESCE ( ein_error_num, 0 ) AS einErrorNum
        FROM att_month_report amr
        left join att_group_person_relation p on p.user_id = amr.user_id
        where amr.is_active = 1 and p.is_active = 1
        and amr.required_days != amr.normal_num
        <if test="attCurrentMonth != null and attCurrentMonth != '' ">
            and amr.att_current_month = #{attCurrentMonth}
        </if>
        <if test="name != null and name != '' ">
            and INSTR(amr.name,#{name}) > 0
        </if>
        <if test="orgId != null and orgId != ''">
            AND amr.org_id = #{orgId}
        </if>
        <if test="name != null and name != ''">
            AND amr.name like concat('%', #{name}, '%')
        </if>
        group by amr.user_id
    </select>

    <select id="selectAttAllList" resultType="com.bonus.system.att.entity.AttDetailByMonthBean">
        select
        amr.user_id as userId,
        amr.name as name,
        amr.org_id as orgId,
        amr.org_name as orgName,
        att_current_month as attCurrentMonth,
        coalesce(required_days,0) as requiredDays,
        coalesce(normal_num,0) as normalNum,
        coalesce(late_num,0) as lateNum,
        coalesce(early_num,0) as earlyNum,
        coalesce(skipping_num,0) as skippingNum,
        coalesce(leave_num,0) as leaveNum,
        coalesce(address_error_num,0) as addressErrorNum,
        coalesce(ein_error_num,0) as einErrorNum
        from att_month_report amr
        left join att_group_person_relation p on p.user_id = amr.user_id
        where amr.is_active = 1 and p.is_active = 1
        and amr.required_days != amr.normal_num
        <if test="bean.startMonth != null and bean.startMonth != '' ">
            and amr.att_current_month BETWEEN #{bean.startMonth} AND #{bean.endMonth}
        </if>
        <if test="bean.name != null and bean.name != '' ">
            and INSTR(amr.name,#{bean.name}) > 0
        </if>
        <if test="bean.userId != null and bean.userId != ''">
            AND amr.user_id = #{bean.userId}
        </if>
        <if test="bean.orgId != null and bean.orgId != ''">
            AND amr.org_id = #{bean.orgId}
        </if>
        <if test="bean.name != null and bean.name != ''">
            AND amr.name like concat('%', #{bean.name}, '%')
        </if>
        <if test='bean.orgList != null and bean.orgList.size() > 0'>
            and amr.org_id in (
            <foreach collection="bean.orgList" item="item" separator=",">
                #{item}
            </foreach>
            )
        </if>
        group by amr.user_id
    </select>

    <select id="getOrgParentId" resultType="com.bonus.system.basic.domain.SysOrg">
        select id        as id,
               org_name  as orgName,
               parent_id as parentId
        from sys_organization so
        where so.is_active = 1
          and so.id = #{orgId}
    </select>

    <select id="selectAttParentId" resultType="com.bonus.system.att.entity.AttDetailByMonthBean">
        select
        amr.id as id,
        amr.user_id as userId,
        amr.name as name,
        amr.org_id as orgId,
        amr.org_name as orgName,
        amr.att_current_month as attCurrentMonth,
        coalesce(required_days,0) as requiredDays,
        coalesce(normal_num,0) as normalNum,
        coalesce(late_num,0) as lateNum,
        coalesce(early_num,0) as earlyNum,
        coalesce(skipping_num,0) as skippingNum,
        coalesce(leave_num,0) as leaveNum,
        coalesce(address_error_num,0) as addressErrorNum,
        coalesce(ein_error_num,0) as einErrorNum
        from att_month_report amr
        left join sys_organization so on amr.org_id = so.id and so.is_active = 1
        where amr.is_active = 1
        and amr.required_days != amr.normal_num
        and so.parent_id = 2
        <if test="attCurrentMonth != null and attCurrentMonth != '' ">
            and amr.att_current_month = #{attCurrentMonth}
        </if>
        <if test="name != null and name != '' ">
            and INSTR(amr.name,#{name}) > 0
        </if>
        group by user_id
    </select>

    <select id="selectAttSonsId" resultType="com.bonus.system.att.entity.AttDetailByMonthBean">
        select
        amr.id as id,
        amr.user_id as userId,
        amr.name as name,
        amr.org_id as orgId,
        amr.org_name as orgName,
        amr.att_current_month as attCurrentMonth,
        coalesce(required_days,0) as requiredDays,
        coalesce(normal_num,0) as normalNum,
        coalesce(late_num,0) as lateNum,
        coalesce(early_num,0) as earlyNum,
        coalesce(skipping_num,0) as skippingNum,
        coalesce(leave_num,0) as leaveNum,
        coalesce(address_error_num,0) as addressErrorNum,
        coalesce(ein_error_num,0) as einErrorNum
        from att_month_report amr
        left join sys_organization so on amr.org_id = so.id and so.is_active = 1
        where amr.is_active = 1
        and amr.required_days != amr.normal_num
        <if test="attCurrentMonth != null and attCurrentMonth != '' ">
            and amr.att_current_month = #{attCurrentMonth}
        </if>
        <if test="name != null and name != '' ">
            and INSTR(amr.name,#{name}) > 0
        </if>
        group by user_id
    </select>

    <select id="getDetailList" resultType="com.bonus.system.att.entity.AttDetailBean">
        select
        vat.user_id as userId,
        su.user_name as name,
        vat.org_id as orgId,
        so.org_name as orgName,
        vat.toWorkAttCurrentTime as goWorkTime ,
        vat.toWorkAttStatus as goWorkStatus,
        vat.toWorkAttAddress as goWorkAddress,
        vat.offWorkAttCurrentTime as offWorkTime,
        vat.offWorkAttStatus as offWorkStatus,
        vat.offWorkAttAddress as offWorkAddress,
        vat.att_current_day as attCurrent
        from v_att_update_data vat
        left join sys_user su on vat.user_id = su.user_id and su.is_active = 1
        left join sys_organization so on vat.org_id = so.id
        where 1=1
        <if test="userId != null ">
            and vat.user_id = #{userId}
        </if>
        <if test="name != null ">
            and locate(#{name},su.user_name) > 0
        </if>
        <if test='attStatis != null and attStatis == "6"'>
            and (vat.toWorkAttStatus in (SELECT dict_value
            FROM sys_dict_data
            where dict_type = 'att_status' and is_leave = '1') or vat.offWorkAttStatus in (SELECT dict_value
            FROM sys_dict_data
            where dict_type = 'att_status' and is_leave = '1'))
        </if>
        <if test='attStatis != null and attStatis != "6"'>
                and (vat.toWorkAttStatus = #{attStatis} or vat.offWorkAttStatus = #{attStatis} )
        </if>
        <if test="orgId != null and orgId != '' ">
            and vat.org_id = #{orgId}
        </if>
        <if test='orgList != null and orgList.size() > 0'>
            and vat.org_id in (
            <foreach collection="orgList" item="item" separator=",">
                #{item}
            </foreach>
            )
        </if>
        <if test="attCurrentMonth != null and attCurrentMonth != '' ">
            and LEFT(vat.att_current_day, 7) = #{attCurrentMonth}
        </if>
        <if test="attCurrent != null and attCurrent != ''"><!-- 结束时间检索 -->
            and
            (vat.att_current_day &gt;= CONCAT(#{attCurrent}, '-01') AND
            vat.att_current_day &lt; LAST_DAY(CONCAT(#{attCurrent}, '-01')))
        </if>
        <if test="startDate != null and startDate != ''">
            and vat.att_current_day between #{startDate} and #{endDate}
        </if>
        <if test="params.beginTime != null and params.beginTime != ''"><!-- 开始时间检索 -->
            and date_format(vat.att_current_day,'%y%m%d') &gt;= date_format(#{params.beginTime},'%y%m%d')
        </if>
        <if test="params.endTime != null and params.endTime != ''"><!-- 结束时间检索 -->
            and date_format(vat.att_current_day,'%y%m%d') &lt;= date_format(#{params.endTime},'%y%m%d')
        </if>
    </select>

    <select id="getDetailLeaveList" resultType="com.bonus.system.att.entity.AttDetailLeaveBean">
        select
        id as id,
        user_id as userId,
        user_name as name,
        org_id as orgId,
        org_name as orgName,
        leave_type as leaveType,
        leave_reason as leaveReason,
        leave_start_date as leaveStartDate,
        leave_start_interval as leaveStartInterval,
        leave_end_date as leaveEndDate,
        leave_end_interval as leaveEndInterval,
        leave_duration as leaveDuration
        from leave_apply la
        where
        la.is_active = 1
        # and (la.leave_type not like '%临时外出%' and la.leave_type not like '%轮休%')
        <if test="userId != null ">
            and la.user_id = #{userId}
        </if>
        <if test="orgId != null and orgId != '' ">
            and la.org_id = #{orgId}
        </if>
    </select>

    <select id="getDepartmentById" parameterType="java.util.Map" resultType="java.util.Map">
        select so.id                                as id,
               so.parent_id                         as parentId,
               concat(so.org_name, '/', #{orgName}) as orgName
        from sys_organization so
        where so.id = #{parentId}
          and is_active = 1
    </select>

    <select id="getSonsById" parameterType="java.lang.Integer" resultType="java.util.Map">
        select so.id        as id,
               so.parent_id as parentId,
               so.org_name  as orgName
        from sys_organization so
        where so.parent_id = #{orgId}
          and is_active = 1
    </select>

    <select id="getSonsFinish" parameterType="java.lang.Integer" resultType="java.util.Map">
        select so.id        as id,
               so.parent_id as parentId,
               so.org_name  as orgName
        from sys_organization so
        where so.id = #{orgId}
          and is_active = 1
    </select>
    <!--考勤报表->月异常报表->打卡记录-->
    <select id="getClockingRecordNum" resultType="java.lang.Integer">
        SELECT COUNT(*) FROM att_source_data
        WHERE name = #{c.name} AND DATE_FORMAT(att_current_day,'%Y-%m') = #{c.attCurrentMonth} AND is_active = '1'
    </select>
</mapper>
