<?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.ruoyi.system.mapper.InvestigateTableMapper">

    <resultMap type="InvestigateTable" id="InvestigateTableResult">
        <result property="investigateTableId" column="investigate_table_id"/>
        <result property="investigateName" column="investigate_name"/>
        <result property="content" column="content"/>
        <result property="beginTime" column="begin_time"/>
        <result property="endTime" column="end_time"/>
        <result property="cipher" column="cipher"/>
        <result property="receptionUnit" column="reception_unit"/>
        <result property="investigateDispositionIds" column="investigate_disposition_ids"/>
        <result property="investigateDispositionIdsJieDai" column="investigate_disposition_ids_jie_dai"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
        <result property="remark" column="remark"/>
        <collection property="investigateUserList" javaType="java.util.List" resultMap="InvestigateUserResult"/>
        <collection property="investigateDispositionTableList" javaType="java.util.List" resultMap="InvestigateDispositionTableResult"/>
    </resultMap>

    <resultMap type="InvestigateDispositionTable" id="InvestigateDispositionTableResult">
        <result property="investigateTableId" column="idt_investigate_table_id"/>
        <result property="investigateDispositionId" column="idt_investigate_disposition_id"/>
        <result property="url" column="idt_url"/>
        <result property="title" column="idt_title"/>
        <result property="content" column="idt_content"/>
    </resultMap>

    <resultMap type="InvestigateUser" id="InvestigateUserResult">
        <result property="id" column="id"/>
        <result property="investigateId" column="investigate_id"/>
        <result property="userName" column="user_name"/>
        <result property="phonenumber" column="phonenumber"/>
        <result property="idCard" column="id_card"/>
        <result property="duty" column="duty"/>
        <result property="loginDate" column="login_date"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
        <result property="remark" column="remark"/>
    </resultMap>
    <sql id="selectInvestigateTableVo">
        select investigate_table_id,
               investigate_name,
               content,
               begin_time,
               end_time,
               investigate_disposition_ids,
               investigate_disposition_ids_jie_dai,
               cipher,
               reception_unit,
               create_by,
               create_time,
               update_by,
               update_time,
               remark
        from investigate_table
    </sql>

    <select id="selectInvestigateTableList" parameterType="InvestigateTable" resultMap="InvestigateTableResult">
        SELECT
        t.investigate_table_id,
        t.investigate_name,
        t.content,
        t.end_time,
        t.begin_time,
        t.investigate_disposition_ids,
        t.investigate_disposition_ids_jie_dai,
        t.cipher,
        t.reception_unit,
        t.create_by,
        t.create_time,
        t.update_by,
        t.update_time
        FROM
        investigate_table t
        <where>
            <if test="investigateName != null  and investigateName != ''">and t.investigate_name like concat('%',
                #{investigateName}, '%')
            </if>
            <if test="content != null  and content != ''">and t.content = #{content}</if>
            <if test="endTime != null ">and t.end_time = #{endTime}</if>
            <if test="cipher != null  and cipher != ''">and t.cipher = #{cipher}</if>
        </where>
        order by t.create_time desc
    </select>

    <select id="selectInvestigateTableListALL" parameterType="InvestigateTable" resultMap="InvestigateTableResult">
        SELECT
        t.investigate_table_id,
        t.investigate_name,
        t.content,
        t.end_time,
        t.begin_time,
        t.investigate_disposition_ids,
        t.investigate_disposition_ids_jie_dai,
        t.cipher,
        t.reception_unit,
        t.create_by,
        t.create_time,
        t.update_by,
        t.update_time,
        u.id,
        u.investigate_id,
        u.user_name,
        u.phonenumber,
        u.id_card,
        u.duty,
        idt.investigate_table_id as idt_investigate_table_id,
        idt.investigate_disposition_id as idt_investigate_disposition_id,
        idt.url as idt_url,
        idt.title as idt_title,
        idt.content as idt_content
        FROM
        investigate_table t
        left join investigate_user u on t.investigate_table_id = u.investigate_id
        left join investigate_disposition_table idt on idt.investigate_table_id = t.investigate_table_id
        <where>
            <if test="investigateTableIds != null">
            and t.investigate_table_id in
                <foreach collection="investigateTableIds.split(',')" item="id" index="index" open="(" close=")" separator=",">
                    #{id}
                </foreach>
        </if>
        </where>
        order by t.create_time DESC
    </select>

    <select id="selectInvestigateTableById" parameterType="Long" resultMap="InvestigateTableResult">
        SELECT
            t.investigate_table_id,
            t.investigate_name,
            t.content,
            t.end_time,
            t.begin_time,
            t.investigate_disposition_ids,
            t.investigate_disposition_ids_jie_dai,
            t.cipher,
            t.reception_unit,
            t.create_by,
            t.create_time,
            t.update_by,
            t.update_time,
            u.id,
            u.investigate_id,
            u.user_name,
            u.phonenumber,
            u.id_card,
            u.duty,
            idt.investigate_table_id as idt_investigate_table_id,
            idt.investigate_disposition_id as idt_investigate_disposition_id,
            idt.url as idt_url,
            idt.title as idt_title,
            idt.content as idt_content
        FROM
            investigate_table t
                left join investigate_user u on t.investigate_table_id = u.investigate_id
                left join investigate_disposition_table idt on idt.investigate_table_id = t.investigate_table_id
        where t.investigate_table_id = #{investigateTableId}
        order by  idt.create_time DESC
    </select>

    <insert id="insertInvestigateTable" parameterType="InvestigateTable" useGeneratedKeys="true"
            keyProperty="investigateTableId">
        insert into investigate_table
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="investigateName != null">investigate_name,</if>
            <if test="content != null">content,</if>
            <if test="endTime != null">end_time,</if>
            <if test="beginTime != null">begin_time,</if>
            <if test="investigateDispositionIds != null">investigate_disposition_ids,</if>
            <if test="investigateDispositionIdsJieDai != null">investigate_disposition_ids_jie_dai,</if>
            <if test="cipher != null">cipher,</if>
            <if test="receptionUnit != null">reception_unit,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="remark != null">remark,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="investigateName != null">#{investigateName},</if>
            <if test="content != null">#{content},</if>
            <if test="endTime != null">#{endTime},</if>
            <if test="beginTime != null">#{beginTime},</if>
            <if test="investigateDispositionIds != null">#{investigateDispositionIds},</if>
            <if test="investigateDispositionIdsJieDai != null">#{investigateDispositionIdsJieDai},</if>
            <if test="cipher != null">#{cipher},</if>
            <if test="receptionUnit != null">#{receptionUnit},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="remark != null">#{remark},</if>
        </trim>
    </insert>

    <update id="updateInvestigateTable" parameterType="InvestigateTable">
        update investigate_table
        <trim prefix="SET" suffixOverrides=",">
            <if test="investigateName != null">investigate_name = #{investigateName},</if>
            <if test="content != null">content = #{content},</if>
            <if test="endTime != null">end_time = #{endTime},</if>
            <if test="beginTime != null">begin_time = #{beginTime},</if>
            <if test="investigateDispositionIds != null">investigate_disposition_ids = #{investigateDispositionIds},</if>
            <if test="investigateDispositionIdsJieDai != null">investigate_disposition_ids_jie_dai = #{investigateDispositionIdsJieDai},</if>
            <if test="cipher != null">cipher = #{cipher},</if>
            <if test="receptionUnit != null">reception_unit = #{receptionUnit},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="remark != null">remark = #{remark},</if>
        </trim>
        where investigate_table_id = #{investigateTableId}
    </update>

    <delete id="deleteInvestigateTableById" parameterType="Long">
        delete
        from investigate_table
        where investigate_table_id = #{investigateTableId}
    </delete>

    <delete id="deleteInvestigateTableByIds" parameterType="String">
        delete from investigate_table where investigate_table_id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>
</mapper>