<?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="cn.com.ctop.reimburse.mondule.mapper.ReimburseApplyMapper">

    <select id="queryPageList" resultType="cn.com.ctop.reimburse.mondule.entity.ReimburseApply">

        SELECT * FROM (
        SELECT *, 1 as 'flag' FROM ctop_reimburse_apply
        where 1=1
        <if test="financialReview != 2">
            AND user_id = #{userId}
        </if>
        <if test="companyId != null and companyId != ''">
            AND company_id =#{companyId}
        </if>
        <if test="media != null and media != ''">
            AND media =#{media}
        </if>
        <if test="type != null and type != ''">
            AND `type` =#{type}
        </if>
        <if test="status != null and status != ''">
            AND status =#{status}
        </if>
        <if test="userName != null and userName != ''">
            AND user_name like concat('%',#{userName},'%')
        </if>
        <if test="startDate != null and startDate != ''">
            AND stat_date >=#{startDate}
            AND stat_date &lt;=#{endDate}
        </if>
        union all
        SELECT *, 2 as 'flag' FROM ctop_reimburse_apply
        where 1=1
        <if test="financialReview == 1">
            AND leader_id = ''
            AND final_status = 1
        </if>
        <if test="financialReview == null">
            AND leader_id = #{userId}
        </if>
        <if test="companyId != null and companyId != ''">
            AND company_id =#{companyId}
        </if>
        <if test="media != null and media != ''">
            AND media =#{media}
        </if>
        <if test="type != null and type != ''">
            AND `type` =#{type}
        </if>
        <if test="status != null and status != ''">
            AND status =#{status}
        </if>
        <if test="userName != null and userName != ''">
            AND user_name like concat('%',#{userName},'%')
        </if>
        <if test="startDate != null and startDate != ''">
            AND stat_date >=#{startDate}
            AND stat_date &lt;=#{endDate}
        </if>
        union all
        SELECT t1.*, 3 as 'flag' FROM ctop_reimburse_apply t1
        left join ctop_reimburse_apply_leader_rel t2 on t1.id = t2.apply_id
        where
        t2.user_id = #{userId}
        AND t1.id not in (
        SELECT id FROM ctop_reimburse_apply
        where 1=1
        <if test="financialReview == 1">
            AND leader_id = ''
            AND final_status = 1
        </if>
        <if test="financialReview == null">
            AND leader_id = #{userId}
        </if>
        <if test="companyId != null and companyId != ''">
            AND company_id =#{companyId}
        </if>
        <if test="media != null and media != ''">
            AND media =#{media}
        </if>
        <if test="type != null and type != ''">
            AND `type` =#{type}
        </if>
        <if test="status != null and status != ''">
            AND status =#{status}
        </if>
        <if test="userName != null and userName != ''">
            AND user_name like concat('%',#{userName},'%')
        </if>
        <if test="startDate != null and startDate != ''">
            AND stat_date >=#{startDate}
            AND stat_date &lt;=#{endDate}
        </if>
        )
        <if test="companyId != null and companyId != ''">
            AND company_id =#{companyId}
        </if>
        <if test="media != null and media != ''">
            AND media =#{media}
        </if>
        <if test="type != null and type != ''">
            AND `type` =#{type}
        </if>
        <if test="status != null and status != ''">
            AND status =#{status}
        </if>
        <if test="startDate != null and startDate != ''">
            AND stat_date >=#{startDate}
            AND stat_date &lt;=#{endDate}
        </if>
        ) t
        group by id
        order by update_time desc
    </select>

    <select id="queryList" resultType="com.alibaba.fastjson.JSONObject">
        SELECT * from (
        SELECT
        t1.id,
        t1.user_id,
        t1.user_name as 'userName',
        t1.depart_name as 'departName',
        t1.company_name as 'companyName',
        t1.media,
        t2.type,
        t2.project,
        t2.amount,
        t2.stat_date as 'expenseDate',
        t1.stat_date as 'statDate',
        t2.remarks,
        t1.`status`
        FROM ctop_reimburse_apply t1
        LEFT JOIN ctop_reimburse_project t2 ON t1.id = t2.apply_id
        WHERE 1=1
        <if test="financialReview == 1">
            AND leader_id = ''
            AND final_status = 1
        </if>
        <if test="financialReview == null">
            AND ( t1.user_id = #{userId}
            OR
            t1.leader_id = #{userId})
        </if>
        <if test="companyId != null and companyId != ''">
            AND t1.company_id =#{companyId}
        </if>
        <if test="media != null and media != ''">
            AND t1.media =#{media}
        </if>
        <if test="type != null and type != ''">
            AND t1.`type` =#{type}
        </if>
        <if test="status != null and status != ''">
            AND t1.status =#{status}
        </if>
        <if test="startDate != null and startDate != ''">
            AND t1.stat_date >=#{startDate}
            AND t1.stat_date &lt;=#{endDate}
        </if>
        UNION
        SELECT
        t1.id,
        t1.user_id,
        t1.user_name as 'userName',
        t1.depart_name as 'departName',
        t1.company_name as 'companyName',
        t1.media,
        t2.type,
        t2.project,
        t2.amount,
        t2.stat_date as 'expenseDate',
        t1.stat_date as 'statDate',
        t2.remarks,
        t1.`status`
        FROM ctop_reimburse_apply t1
        LEFT JOIN ctop_reimburse_project t2 ON t1.id = t2.apply_id
        LEFT JOIN ctop_reimburse_apply_leader_rel t3 on t1.id = t3.apply_id
        where t3.user_id = #{userId}
        <if test="companyId != null and companyId != ''">
            AND t1.company_id =#{companyId}
        </if>
        <if test="media != null and media != ''">
            AND t1.media =#{media}
        </if>
        <if test="type != null and type != ''">
            AND t1.`type` =#{type}
        </if>
        <if test="status != null and status != ''">
            AND t1.status =#{status}
        </if>
        <if test="startDate != null and startDate != ''">
            AND t1.stat_date >=#{startDate}
            AND t1.stat_date &lt;=#{endDate}
        </if>
        ) t
        order by t.user_id,t.statDate
    </select>

    <select id="selectLeaders" resultType="com.alibaba.fastjson.JSONObject">
        SELECT *
        FROM ctop_reimburse_leader
        where company_id = #{companyId}
    </select>

    <select id="queryLeaderCompanyList" resultType="com.alibaba.fastjson.JSONObject">
        SELECT company_id as 'companyId', company_name as 'companyName'
        FROM ctop_reimburse_leader
        group by company_id
    </select>

    <select id="selectFinalLeaders" resultType="String">
        SELECT final_id
        FROM ctop_reimburse_leader
        where company_id = #{companyId}
          AND final_id = #{userId} LIMIT 1
    </select>

    <select id="selectWelfareLeader" resultType="String">
        SELECT first_id
        FROM ctop_reimburse_leader
        where depart_name = #{departName} LIMIT 1
    </select>

    <insert id="insertRel">
        replace
        into ctop_reimburse_apply_leader_rel(apply_id,user_id)VALUE (
        #{applyId},
        #{leader}
        )
    </insert>
</mapper>