<?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.leaveReporting.dao.LeaveReportingDao">

    <insert id="insertLeaveReporting">
        INSERT INTO leave_apply(user_id, user_name, org_id, org_name, leave_type, type, leave_reason,
                                leave_start_date,
                                <if test="leaveStartInterval != null">
                                    leave_start_interval,
                                </if>
                                leave_end_date,
                                <if test="leaveEndInterval != null">
                                    leave_end_interval,
                                </if>
                                leave_duration, source, create_user_id, is_agree, location, host_user_id,host_user_name, remark,examine_status)
        VALUES (#{userId}, #{userName}, #{orgId}, #{orgName}, #{leaveType} , '请假', #{leaveReason},
                #{leaveStartDate},
                <if test="leaveStartInterval != null">
                    #{leaveStartInterval},
                </if>
                #{leaveEndDate},
                <if test="leaveEndInterval != null">
                    #{leaveEndInterval},
                </if>
                #{leaveDuration}, #{source}, #{createUserId}, #{isAgree}, #{location}, #{hostUserId},#{hostUserName}, #{remark},'1')
    </insert>

    <update id="updateLeaveReporting">
        UPDATE leave_apply
        <set>
            <if test="userId != null and userId != ''">
                user_id = #{userId},
            </if>
            <if test="userName != null and userName != ''">
                user_name = #{userName},
            </if>
            <if test="orgId != null and orgId != ''">
                org_id = #{orgId},
            </if>
            <if test="orgName != null and orgName != ''">
                org_name = #{orgName},
            </if>

            <if test="leaveType != null and leaveType != ''">
                leave_type = #{leaveType},
            </if>
            <if test="hostUserId != null and hostUserId != ''">
                host_user_id = #{hostUserId},
            </if>
            <if test="hostUserName != null and hostUserName != ''">
                host_user_name = #{hostUserName},
            </if>
            <if test="location != null and location != ''">
                location = #{location},
            </if>
            <if test="isAgree != null and isAgree != ''">
                is_agree = #{isAgree},
            </if>

            <if test="remark != null and remark != ''">
                remark = #{remark},
            </if>

            leave_reason = #{leaveReason},
            leave_start_date = #{leaveStartDate},
            leave_start_interval = #{leaveStartInterval},
            leave_end_date = #{leaveEndDate},
            leave_end_interval = #{leaveEndInterval},
            leave_duration = #{leaveDuration},
            update_user_id = #{updateUserId}
        </set>
        where id = #{id}
    </update>

    <update id="deleteLeaveReportingById">
        update leave_apply
        SET is_active = '0'
        WHERE id = #{id}
    </update>

    <select id="getLeaveReportingList" resultType="com.bonus.system.leaveReporting.entity.LeaveReportingBean">
        SELECT
        l.id,
        l.user_id,
        l.user_name,
        l.org_id,
        l.org_name,
        l.create_time,
        l.leave_type,
        l.type,
        l.leave_reason,
        l.leave_start_date,
        l.leave_start_interval,
        l.leave_end_date,
        l.leave_end_interval,
        l.leave_duration,
        GROUP_CONCAT(d.dict_label SEPARATOR ', ') AS postName
        FROM
        leave_apply l
        LEFT JOIN sys_user_post p ON p.user_id = l.user_id
        LEFT JOIN sys_dict_data d on d.dict_code = p.post_id
        LEFT JOIN sys_user_org suo on suo.user_id=l.user_id and suo.is_active=1
        WHERE
        l.is_active = '1' AND l.type = '请假' AND l.source = '1'
        <if test='params.userId != null '>
            and l.user_id = #{params.userId}
        </if>
        <if test="params.params.beginTime != null and params.params.beginTime != ''"><!-- 开始时间检索 -->
            and (
                date_format(l.leave_start_date,'%y%m%d') between date_format(#{params.params.beginTime},'%y%m%d') and date_format(#{params.params.endTime},'%y%m%d') or
                date_format(l.leave_end_date,'%y%m%d') between date_format(#{params.params.beginTime},'%y%m%d') and date_format(#{params.params.endTime},'%y%m%d')
            )
        </if>

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

        GROUP BY
        l.id,
        l.user_id,
        l.user_name,
        l.org_id,
        l.org_name,
        l.create_time,
        l.leave_type,
        l.leave_reason,
        l.leave_start_date,
        l.leave_start_interval,
        l.leave_end_date,
        l.leave_end_interval,
        l.leave_duration,
        l.examine_status
        ORDER BY l.leave_start_date DESC
    </select>

    <select id="selectLeaveReportingById"
            resultType="com.bonus.system.leaveReporting.entity.LeaveReportingBean">
        SELECT l.id,
               l.user_id,
               l.user_name,
               l.org_id,
               l.org_name,
               l.create_time,
               l.leave_type,
               l.type,
               l.leave_reason,
               l.leave_start_date,
               l.leave_start_interval,
               l.leave_end_date,
               l.leave_end_interval,
               l.leave_duration,
               GROUP_CONCAT(d.dict_label SEPARATOR ', ') AS postName,
               l.host_user_id,
               l.host_user_name,
               l.is_agree,
               l.location,
               l. remark as remark
        FROM
            leave_apply l
                LEFT JOIN sys_user_post p ON p.user_id = l.user_id
                LEFT JOIN sys_dict_data d on d.dict_code = p.post_id
        WHERE l.id = #{id}
    </select>

    <select id="getPostName" resultType="com.bonus.system.leaveReporting.entity.LeaveReportingBean">
        SELECT GROUP_CONCAT(d.dict_label SEPARATOR ', ') AS postName
        FROM sys_user_post p
                 LEFT JOIN sys_dict_data d on d.dict_code = p.post_id
        WHERE p.user_id = #{id}
    </select>

    <select id="getUserList" resultType="com.bonus.system.leaveReporting.entity.LeaveReportingBean">
        SELECT 0 as userId, '/' AS userName
        UNION ALL
        SELECT su.user_id as userId , su.user_name as userName
        FROM sys_user su
        LEFT JOIN sys_user_org suo ON su.user_id = suo.user_id and su.user_id !=#{params.id}
        WHERE 1=1
        <if test='params.orgList != null and params.orgList.size() > 0'>
            and suo.org_id in (
            <foreach collection="params.orgList" item="item" separator=",">
                #{item}
            </foreach>
            )
        </if>
    </select>

    <select id="getFestivalAndHoliday" resultType="com.bonus.system.leaveReporting.entity.LeaveReportingBean">
        SELECT id, date, `name`, type
        FROM
            sys_holiday
        where type ='1'
    </select>
    <select id="getAttTypeByUserId" resultType="java.lang.String">
        SELECT att_type
        FROM
            att_group_person_relation agpr
        left join att_group ag on agpr.group_id = ag.id
        where user_id = #{userId} and agpr.is_active = '1'
    </select>
    <!--判断休假报备数据是否被核对-->
    <select id="isCheck" resultType="java.lang.Integer">
        SELECT status
        FROM leave_check lc
        WHERE lc.org_id = #{orgId} AND (DATE_FORMAT(#{leaveStartDate},'%Y-%m') = lc.month OR DATE_FORMAT(#{leaveEndDate},'%Y-%m') = lc.month)
        AND lc.is_active = '1'
        ORDER BY lc.create_time
    </select>
</mapper>