<?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.wechat.dao.WechatEvectionDao">

    <select id="getEvectionList" resultType="com.bonus.system.wechat.entity.WechatEvectionBean">
        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,
        CASE
        WHEN l.source = '1' THEN '系统'
        WHEN l.source = '3' THEN '小程序'
        ELSE ''
        END AS source
        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.source = '1' OR l.source = '3')
        <if test="bean.params.beginTime != null and bean.params.beginTime != ''">
            and (
                date_format(l.leave_start_date,'%y%m%d') between date_format(#{bean.params.beginTime},'%y%m%d') and date_format(#{bean.params.endTime},'%y%m%d') or
                date_format(l.leave_end_date,'%y%m%d') between date_format(#{bean.params.beginTime},'%y%m%d') and date_format(#{bean.params.endTime},'%y%m%d')
            )
        </if>
        <if test='bean.orgList != null and bean.orgList.size() > 0'>
            and l.org_id in (
            <foreach collection="bean.orgList" item="item" separator=",">
                #{item}
            </foreach>
            )
        </if>
        <if test="bean.userName != null and bean.userName != ''">
            AND l.user_name like concat('%', #{bean.userName}, '%')
        </if>
        <if test="bean.orgId != null and bean.orgId != ''">
            AND l.org_id = #{bean.orgId}
        </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.wechat.entity.WechatEvectionBean">
        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
              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.host_user_name,
                       l.is_agree,
                       l.location,
                       l.remark
              ORDER BY l.update_time DESC) a
                 LEFT JOIN sys_user su ON su.user_id = a.submit_id
        GROUP BY uuId
    </select>

</mapper>