<?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.evection.dao.EvectionDao">

    <insert id="insertEvection">
        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
            , leave_form_id, submit_id,examine_status,is_outside,is_xizang,is_plateau,plateau_subsidy)
        VALUES (#{userId}, #{userName}, #{orgId}, #{orgName}, '出差','出差', #{leaveReason},
        #{leaveStartDate},
        <if test="leaveStartInterval != null">
            #{leaveStartInterval},
        </if>
        #{leaveEndDate},
        <if test="leaveEndInterval != null">
            #{leaveEndInterval},
        </if>
                #{leaveDuration}, #{source}, #{createUserId}, #{isAgree}, #{location}, #{hostUserId},#{hostUserName}, #{remark},
                #{uuid}, #{createUserId},'1',#{isOutside},#{isXizang},#{isPlateau},#{plateauSubsidy})
    </insert>

    <update id="updateEvection">
        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="type != null and type != ''">
                type = #{type},
            </if>
            <if test="hostUserId != null and hostUserId != ''">
                host_user_id = #{hostUserId},
            </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 leave_form_id = #{uuid}
    </update>

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

    <select id="getEvectionList" resultType="com.bonus.system.evection.entity.EvectionBean">
        SELECT a.*,
        GROUP_CONCAT( a.user_ids SEPARATOR ', ' ) AS travelers,
        GROUP_CONCAT( a.user_names SEPARATOR ', ' ) AS travelersName,
        su.user_id ,su.user_name
        FROM (
        SELECT
        l.id,
        l.user_id as user_ids,
        l.user_name as user_names,
        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.leave_form_id as uuid,
        l.submit_id,
        l.update_time
        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.leave_type = '出差' AND l.source = '1'
        <if test="params.beginTime != null and params.beginTime != ''">
            and (
                date_format(l.leave_start_date,'%y%m%d') between date_format(#{params.beginTime},'%y%m%d') and date_format(#{params.endTime},'%y%m%d') or
                date_format(l.leave_end_date,'%y%m%d') between date_format(#{params.beginTime},'%y%m%d') and date_format(#{params.endTime},'%y%m%d')
            )
        </if>

        <if test='bean.orgList != null and bean.orgList.size() > 0'>
            and suo.org_id in (
            <foreach collection="bean.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,
        l.submit_id
        ORDER BY l.update_time DESC
        ) a
        LEFT JOIN sys_user su ON su.user_id = a.submit_id
        <where>
            <if test="bean.userId!=null and bean.userId!=''">
                and a.submit_id = #{bean.userId}
            </if>
        </where>
        GROUP BY uuid
        ORDER BY leave_start_date DESC
    </select>

    <select id="selectEvectionById"
            resultType="com.bonus.system.evection.entity.EvectionBean">
        SELECT a.*,
               GROUP_CONCAT( a.user_ids SEPARATOR ', ' ) AS travelers,
               GROUP_CONCAT( a.user_names SEPARATOR ', ' ) AS travelersName,
               su.user_id,
               su.user_name
        FROM (SELECT l.id,
                     l.user_id                                 as user_ids,
                     l.user_name                               as user_names,
                     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.leave_form_id                           as uuid,
                     l.submit_id,
                     l.host_user_id,
                     l.host_user_name,
                     l.is_agree,
                     l.location,
                     l.remark,
                     l.is_outside,
                     l.is_xizang,
                     l.is_plateau,
                     l.plateau_subsidy
              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.is_active = '1'
                AND l.leave_type = '出差'
                and l.leave_form_id = #{uuid}
              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,
                       l.submit_id,
                       l.host_user_id,
                       l.is_agree,
                       l.location,
                       l.remark,
                       l.is_outside,
                       l.is_xizang,
                       l.is_plateau,
                       l.plateau_subsidy
              ORDER BY l.update_time DESC) a
                 LEFT JOIN sys_user su ON su.user_id = a.submit_id
        GROUP BY uuid
    </select>

    <select id="getPostName" resultType="com.bonus.system.evection.entity.EvectionBean">
        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.evection.entity.EvectionBean">
        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="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>
    <!--查询出差详情-->
    <select id="getEvectionById" resultType="com.bonus.system.evection.entity.EvectionBean">
        SELECT la.id,
               la.leave_start_date AS leaveStartDate,
               la.leave_end_date AS leaveEndDate,
               la.org_id AS orgId
        FROM leave_apply la
        <where>
            <if test="type == 1">
               AND la.id = #{id}
            </if>
            <if test="type == 2">
                AND la.leave_form_id = #{id} AND submit_id = user_id
            </if>
        </where>
    </select>
</mapper>