<?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.m2pool.pool.mapper.TicketMapper">

    <select id="getPrivateTicketList" resultType="com.m2pool.pool.dto.PrivateTicketListDto">
        select
            id,create_time `date`,email,status
        from
            ticket
        <where>
            email = #{email}
            <if test="vo.id != null and vo.id != 0">
               and id = #{vo.id}
            </if>
            <if test="vo.start != null and vo.start != ''">
                and create_time &gt;= #{vo.start}
            </if>
            <if test="vo.end != null and vo.end != ''">
                and create_time &lt;= #{vo.end}
            </if>
        </where>
        order by create_time desc
    </select>

    <select id="getPrivateProcessingTicketList" resultType="com.m2pool.pool.dto.PrivateTicketListDto">
        select
            id,
            create_time `date`,
            email,
            status
        from
            ticket
        <where>
            status != 10  and  email = #{email}
            <if test="vo.id != null and vo.id != 0">
                and id = #{vo.id}
            </if>
            <if test="vo.start != null and vo.start != ''">
                and create_time &gt;= #{vo.start}
            </if>
            <if test="vo.end != null and vo.end != ''">
                and create_time &lt;= #{vo.end}
            </if>
        </where>
        order by create_time desc
    </select>

    <select id="getPrivateEndTicketList" resultType="com.m2pool.pool.dto.PrivateTicketListDto">
        select
            id,
            create_time `date`,
            email,
            status
        from
            ticket
        <where>
            status = 10 and  email = #{email}
            <if test="vo.id != null and vo.id != 0">
                and id = #{vo.id}
            </if>
            <if test="vo.start != null and vo.start != ''">
                and create_time &gt;= #{vo.start}
            </if>
            <if test="vo.end != null and vo.end != ''">
                and create_time &lt;= #{vo.end}
            </if>
        </where>
        order by create_time desc
    </select>

    <select id="getTicketById" resultType="com.m2pool.pool.dto.TicketDto">
		select
			id,
			email,
			`desc`,
			create_time createTime,
			status
		from
			ticket
		where
		   id = #{id}
	</select>

    <select id="getTicketResponListByTicketId" resultType="com.m2pool.pool.dto.TicketContentDto">
        select
            create_time `time`,
            respon content,
            responer name,
            files,
            video_file videoPath,
            image_file imagePath,
            audio_file audioPath
        from
            respon
        <where>
            <if test="id != null and id != 0">
                ticket_id = #{id}
            </if>
        </where>
        order BY create_time
    </select>

    <select id="getTicketSupplementCountByTicketId" resultType="java.lang.Integer">
		select count(*) from ticket_supplement where ticket_id = #{id};
	</select>

    <select id="getTicketSupplementListByTicketId" resultType="com.m2pool.pool.dto.TicketContentDto">
        select
            s.create_time `time`,
            s.content content,
            s.files files,
            s.video_file videoPath,
            s.image_file imagePath,
            s.audio_file audioPath,
            t.email name
        from
            ticket_supplement s left join ticket t on s.ticket_id = t.id
        <where>
            <if test="id != null and id != 0">
                s.ticket_id = #{id}
            </if>
        </where>
        order by s.create_time,s.id
    </select>

    <select id="checkTicketExist" resultType="java.lang.Integer">
		select count(1) from ticket where id = #{id} limit 1
	</select>

    <update id="changeTicketUpdateTime">
        UPDATE ticket SET update_time=sysdate() WHERE id = #{id}
    </update>

    <select id="getTicketListByCond" resultType="com.m2pool.pool.dto.AllTicketListDto">
        select
            id,
            create_time `date`,
            email email,
            `desc`,
            status
        from
            ticket
        <where>
            <if test="vo.id != null and vo.id != 0">
                AND id = #{vo.id}
            </if>
            <if test="vo.email != null and vo.email != ''">
                AND email = #{vo.email}
            </if>
            <if test="vo.start != null and vo.start != ''"><!-- 开始时间检索 -->
                and create_time &gt; #{vo.start}
            </if>
            <if test="vo.end != null and vo.end != ''"><!-- 结束时间检索 -->
                and create_time &lt; #{vo.end}
            </if>
            <if test="vo.status != null and vo.status != 0">
                AND status = #{vo.status}
            </if>
        </where>
        order by create_time desc
    </select>

    <select id="getPrivateTicketById" resultType="com.m2pool.pool.dto.TicketPrivateDto">
        select
			id,
			email,
			`desc`,
			create_time createTime,
			status
		from
			ticket t
		where
		   t.id = #{id}
		   and t.email = #{email}
    </select>

    <select id="getTicketListByStatusAndType" resultType="com.m2pool.pool.dto.TicketListDto">
        select
            id,create_time `date`,email,status
        from
            ticket
        <where>
            status = #{vo.status}
            <if test="vo.id != null and vo.id != 0">
                AND id = #{vo.id}
            </if>
            <if test="vo.email != null and vo.email != ''">
                AND email = #{vo.email}
            </if>
        </where>
        order by create_time desc
    </select>

    <select id="getBackendTicketById" resultType="com.m2pool.pool.dto.TicketPrivateDto">
        select
			id,
			email,
			`desc`,
			create_time createTime,
			status
		from
			ticket
		where
		   id = #{id}
    </select>

    <select id="getTicketStatusById" resultType="java.lang.Integer">
        select status from ticket where id=#{id}
    </select>

    <select id="getFileInfoById" resultType="com.m2pool.pool.entity.M2File">
        select id,file_name fileName,url,user_name userName,create_time createTime  from file where id = #{id}
    </select>

    <select id="getAllTicketList" resultType="com.m2pool.pool.dto.TicketListDto">
        select
            id,create_time `date`,email,status
        from
            ticket
        <where>
            <if test="vo.id != null and vo.id != 0">
                AND id = #{vo.id}
            </if>
            <if test="vo.email != null and vo.email != ''">
                AND email = #{vo.email}
            </if>
        </where>
        order by create_time desc
    </select>


    <update id="changeTicketStatus">
		UPDATE ticket SET status=#{status},update_time =sysdate() WHERE id = #{id}
	</update>


    <insert id="insertTicket" useGeneratedKeys="true" keyProperty="id">
        insert into ticket(
        <if test="data.email != null and data.email !=''">email,</if>
        <if test="data.desc != null and data.desc != ''">`desc`,</if>
        <if test="data.status != null and data.status != 0">status,</if>
        create_time,update_time
        )values(
        <if test="data.email != null and data.email != ''">#{data.email},</if>
        <if test="data.desc != null and data.desc != ''">#{data.desc},</if>
        <if test="data.status != null and data.status != 0">1,</if>
        sysdate(),sysdate()
        )
    </insert>

    <insert id="insertTicketSupplement">
        insert into ticket_supplement(
        ticket_id,
        content,
        <if test="vo.files != null and vo.files != ''">files,</if>
        <if test="vo.videoFiles != null and vo.videoFiles != ''">video_file,</if>
        <if test="vo.imageFiles != null and vo.imageFiles != ''">image_file,</if>
        <if test="vo.audioFiles != null and vo.audioFiles != ''">audio_file,</if>
        create_time
        ) values (
        #{vo.id},
        #{vo.desc},
        <if test="vo.files != null and vo.files != ''">#{vo.files},</if>
        <if test="vo.videoFiles != null and vo.videoFiles != ''">#{vo.videoFiles},</if>
        <if test="vo.imageFiles != null and vo.imageFiles != ''">#{vo.imageFiles},</if>
        <if test="vo.audioFiles != null and vo.audioFiles != ''">#{vo.audioFiles},</if>
        sysdate()
        )
	</insert>

    <insert id="insertRespon">
		insert into  respon(
		    ticket_id,
		    respon,
		    responer,
		    create_time
		) values(
		    #{vo.id},
		    #{vo.respon},
		    #{name},
            sysdate()
		)
	</insert>

    <insert id="insertFileInfo" useGeneratedKeys="true" keyProperty="id">
		insert into `file`(url,file_name,user_name,`type`,create_time) values(
		#{vo.url},#{vo.fileName},#{vo.userName},#{vo.fileType},sysdate()
		)
    </insert>
</mapper>
