<?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.ty.lift.ud.chat.mapper.ChatSessionMapper" >
  <resultMap id="BaseResultMap" type="cn.com.ty.lift.ud.chat.mapper.entity.ChatSessionEntity" >
    <!--
      WARNING - @mbggenerated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Tue Apr 28 14:25:10 CST 2020.
    -->
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="sessionid" property="sessionid" jdbcType="VARCHAR" />
    <result column="data_id" property="dataId" jdbcType="INTEGER" />
    <result column="data_table" property="dataTable" jdbcType="INTEGER" />
    <result column="user_id" property="userId" jdbcType="VARCHAR" />
    <result column="create_user_id" property="createUserId" jdbcType="VARCHAR" />
    <result column="pwd" property="pwd" jdbcType="VARCHAR" />
    <result column="statuz" property="statuz" jdbcType="INTEGER" />
    <result column="entrance" property="entrance" jdbcType="INTEGER" />
    <result column="create_by" property="createBy" jdbcType="VARCHAR" />
    <result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
    <result column="update_by" property="updateBy" jdbcType="VARCHAR" />
    <result column="update_time" property="updateTime" jdbcType="TIMESTAMP" />
  </resultMap>
  <sql id="Base_Column_List" >
    <!--
      WARNING - @mbggenerated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Tue Apr 28 14:25:10 CST 2020.
    -->
    id, sessionid, data_id, data_table, user_id, create_user_id, pwd, statuz, create_by, 
    create_time, update_by, update_time,entrance
  </sql>
  <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
    <!--
      WARNING - @mbggenerated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Tue Apr 28 14:25:10 CST 2020.
    -->
    select 
    <include refid="Base_Column_List" />
    from chat_session
    where id = #{id,jdbcType=INTEGER}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
    <!--
      WARNING - @mbggenerated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Tue Apr 28 14:25:10 CST 2020.
    -->
    delete from chat_session
    where id = #{id,jdbcType=INTEGER}
  </delete>
  <insert id="insertSelective" parameterType="cn.com.ty.lift.ud.chat.mapper.entity.ChatSessionEntity" >
    <!--
      WARNING - @mbggenerated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Tue Apr 28 14:25:10 CST 2020.
    -->
    insert into chat_session
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        id,
      </if>
      <if test="sessionid != null" >
        sessionid,
      </if>
      <if test="dataId != null" >
        data_id,
      </if>
      <if test="dataTable != null" >
        data_table,
      </if>
      <if test="userId != null" >
        user_id,
      </if>
      <if test="createUserId != null" >
        create_user_id,
      </if>
      <if test="pwd != null" >
        pwd,
      </if>
      <if test="statuz != null" >
        statuz,
      </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="entrance != null" >
        entrance,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        #{id,jdbcType=INTEGER},
      </if>
      <if test="sessionid != null" >
        #{sessionid,jdbcType=VARCHAR},
      </if>
      <if test="dataId != null" >
        #{dataId,jdbcType=INTEGER},
      </if>
      <if test="dataTable != null" >
        #{dataTable,jdbcType=INTEGER},
      </if>
      <if test="userId != null" >
        #{userId,jdbcType=VARCHAR},
      </if>
      <if test="createUserId != null" >
        #{createUserId,jdbcType=VARCHAR},
      </if>
      <if test="pwd != null" >
        #{pwd,jdbcType=VARCHAR},
      </if>
      <if test="statuz != null" >
        #{statuz,jdbcType=INTEGER},
      </if>
      <if test="createBy != null" >
        #{createBy,jdbcType=VARCHAR},
      </if>
      <if test="createTime != null" >
        #{createTime,jdbcType=TIMESTAMP},
      </if>
      <if test="updateBy != null" >
        #{updateBy,jdbcType=VARCHAR},
      </if>
      <if test="updateTime != null" >
        #{updateTime,jdbcType=TIMESTAMP},
      </if>
      <if test="entrance != null" >
        #{entrance},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="cn.com.ty.lift.ud.chat.mapper.entity.ChatSessionEntity" >
    <!--
      WARNING - @mbggenerated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Tue Apr 28 14:25:10 CST 2020.
    -->
    update chat_session
    <set >
      <if test="sessionid != null" >
        sessionid = #{sessionid,jdbcType=VARCHAR},
      </if>
      <if test="dataId != null" >
        data_id = #{dataId,jdbcType=INTEGER},
      </if>
      <if test="dataTable != null" >
        data_table = #{dataTable,jdbcType=INTEGER},
      </if>
      <if test="userId != null" >
        user_id = #{userId,jdbcType=VARCHAR},
      </if>
      <if test="createUserId != null" >
        create_user_id = #{createUserId,jdbcType=VARCHAR},
      </if>
      <if test="pwd != null" >
        pwd = #{pwd,jdbcType=VARCHAR},
      </if>
      <if test="statuz != null" >
        statuz = #{statuz,jdbcType=INTEGER},
      </if>
      <if test="createBy != null" >
        create_by = #{createBy,jdbcType=VARCHAR},
      </if>
      <if test="createTime != null" >
        create_time = #{createTime,jdbcType=TIMESTAMP},
      </if>
      <if test="updateBy != null" >
        update_by = #{updateBy,jdbcType=VARCHAR},
      </if>
      <if test="updateTime != null" >
        update_time = #{updateTime,jdbcType=TIMESTAMP},
      </if>
      <if test="entrance != null" >
        entrance = #{entrance},
      </if>
    </set>
    where id = #{id,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="cn.com.ty.lift.ud.chat.mapper.entity.ChatSessionEntity" >
    <!--
      WARNING - @mbggenerated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Tue Apr 28 14:25:10 CST 2020.
    -->
    update chat_session
    set sessionid = #{sessionid,jdbcType=VARCHAR},
      data_id = #{dataId,jdbcType=INTEGER},
      data_table = #{dataTable,jdbcType=INTEGER},
      user_id = #{userId,jdbcType=VARCHAR},w
      create_user_id = #{createUserId,jdbcType=VARCHAR},
      pwd = #{pwd,jdbcType=VARCHAR},
      statuz = #{statuz,jdbcType=INTEGER},
      create_by = #{createBy,jdbcType=VARCHAR},
      create_time = #{createTime,jdbcType=TIMESTAMP},
      update_by = #{updateBy,jdbcType=VARCHAR},
      update_time = #{updateTime,jdbcType=TIMESTAMP},
      entrance = #{entrance}
    where id = #{id,jdbcType=INTEGER}
  </update>
  
  <select id="findOneBySessionIdAndPwd" resultType="cn.com.ty.lift.ud.chat.mapper.entity.ChatSessionEntity">
    select chat.*,users.name as userName
    from chat_session as chat left join user_info as users on  chat.create_user_id = users.user_id
    where chat.sessionid = #{sessionId} and chat.pwd = #{pwd}
  </select>
  
  <update id="batchUpdate" >
   update chat_session set statuz = #{req.status} where  sessionid in 
   <foreach collection="req.sessionList" item="item" index="index" open="(" close=")" separator=",">
        #{item}
    </foreach>
  </update>

    <update id="setTop">
        update chat_session set top_flag = #{req.topFlag} where  sessionid = #{req.sessionId}
    </update>
  
  <update id="batchUpdateEntrance" >
   update chat_session set entrance = #{entrance} where  sessionid =  #{sessionId}
  </update>
  
   <select id="selectByQuery" resultType="cn.com.ty.lift.ud.chat.mapper.entity.ChatSessionEntity" >
      SELECT t.*,users.`name` as userName,users.avatar_url as avatarUrl,lc.expression as expression,
      ( select msg.create_time  from chat_msg msg  where  msg.sessionid = t.sessionid  ORDER BY msg.create_time  desc  limit 1) as msgCreateTime
      from chat_session as t
     left join lift_case as lc on lc.id = t.data_id
    LEFT JOIN user_info as users on t.user_id = users.user_id 
    <where>
        ${ew.sqlSegment}
    </where>
    limit #{page.pageIndex},#{page.pageSize}
  </select>
  <select id="countByQuery" resultType="java.lang.Integer">
     SELECT count(0) ,1 as msgCreateTime from chat_session as t  
    LEFT JOIN user_info as users on t.create_user_id = users.user_id
    <where>
        ${ew.sqlSegment}
    </where>
  </select>
  
  <select id="findDBListByUserId" resultType="cn.com.ty.lift.ud.chat.mapper.entity.ChatSessionEntity" >
    <if test="caseExpression == null or caseExpression.length() == 0" >
      select
        usr.`name` AS userName,
        usr.avatar_url AS avatarUrl,
        temp.*
      from (
        select
          replace(REPLACE(user_id,#{userId},""),",","") targetId,
          sessio.*,
          '' caseType,
          '' acceptStatus,
          '' expression,
          sessio.top_flag topFlag
          from chat_session sessio
          where FIND_IN_SET(#{userId},sessio.user_id) and sessio.statuz = 1 and sessio.data_table = 3
      )temp left join user_info usr on usr.user_id=temp.targetId
      union
    </if>
    select
      usr.`name` AS userName,
      usr.avatar_url AS avatarUrl,
      temp.*
    from (
      SELECT
        replace(REPLACE(user_id,#{userId},""),",","") targetId,
        sessio.*,
        cas.data_table caseType,
        cas.accept_status acceptStatus,
        cas.expression expression,
        sessio.top_flag topFlag
      FROM chat_session sessio
      LEFT JOIN lift_case cas ON cas.session_id = sessio.sessionid
      WHERE FIND_IN_SET(#{userId}, sessio.user_id) AND sessio.statuz = 1 AND sessio.data_table != 3
    <if test="caseExpression != null and caseExpression.length() > 0" >
      and cas.expression like CONCAT("%",#{caseExpression},"%")
    </if>
    )temp left join user_info usr on usr.user_id=temp.targetId
      <!--select * from (
      select
          usr.`name` AS userName,
          usr.avatar_url AS avatarUrl,
          sessio.*,
          '' caseType,
          '' acceptStatus,
          '' expression,
          sessio.top_flag topFlag
      from chat_session sessio
      left join user_info usr on usr.user_id = sessio.user_id
      where sessio.sessionid in (select sessionid from chat_session where user_id =   #{userId,javaType=java.lang.String})
      and sessio.user_id !=   #{userId,javaType=java.lang.String}
      and sessio.statuz = 1 and sessio.data_table = 3
      union
      select
          usr.`name` AS userName,
          usr.avatar_url AS avatarUrl,
          sessio.*,
          cas.data_table caseType,
          cas.accept_status acceptStatus,
          cas.expression expression,
          sessio.top_flag topFlag
      from chat_session sessio
      left join lift_case cas on cas.session_id = sessio.sessionid
      left join user_info usr on usr.user_id = sessio.user_id
      where sessio.sessionid in (select sessionid from chat_session where user_id =   #{userId,javaType=java.lang.String})
      and sessio.user_id !=   #{userId,javaType=java.lang.String}
      <if test="caseExpression != null" >
          and cas.expression like CONCAT("%",#{caseExpression},"%")
      </if>
      and sessio.statuz = 1 and sessio.data_table != 3
      and cas.accept_status = 1
      union
      select
          '' AS userName,
          '' AS avatarUrl,
          sessio.*,
          cas.data_table caseType,
          cas.accept_status acceptStatus,
          cas.expression expression,
          sessio.top_flag topFlag
      from chat_session sessio
      left join lift_case cas on cas.session_id = sessio.sessionid
      left join user_info usr on usr.user_id = sessio.user_id
      where sessio.user_id =   #{userId,javaType=java.lang.String}
      <if test="caseExpression != null" >
          and cas.expression like CONCAT("%",#{caseExpression},"%")
      </if>
      and sessio.data_table != 3
      and sessio.statuz = 1
      and cas.accept_status != 1
      )temp order by topFlag desc-->
  </select>


  <select id="findAllSession" resultType="cn.com.ty.lift.ud.chat.mapper.entity.ChatSessionEntity" >
      select
        usr.name username,
        usr.avatar_url avatarUrl,
        cas.data_table caseType,
        cas.accept_status acceptStatus,
        cas.expression expression,
        sessio.*
    from chat_session sessio
    left join lift_case cas on cas.session_id = sessio.sessionid
    left join user_info usr on sessio.user_id = usr.user_id
  </select>
</mapper>