<?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.LiftCaseMapper" >
  <resultMap id="BaseResultMap" type="cn.com.ty.lift.ud.chat.mapper.entity.LiftCaseEntity" >
    <!--
      WARNING - @mbggenerated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Thu May 07 14:53:11 CST 2020.
    -->
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="expression" property="expression" jdbcType="VARCHAR" />
    <result column="used_methods" property="usedMethods" jdbcType="VARCHAR" />
    <result column="imgs" property="imgs" jdbcType="VARCHAR" />
    <result column="brand_id" property="brandId" jdbcType="INTEGER" />
    <result column="session_id" property="sessionId" jdbcType="VARCHAR" />
    <result column="address" property="address" jdbcType="VARCHAR" />
    <result column="charger_id" property="chargerId" jdbcType="INTEGER" />
    <result column="travel_cost" property="travelCost" jdbcType="DECIMAL" />
    <result column="service_cost" property="serviceCost" jdbcType="DECIMAL" />
    <result column="total_cost" property="totalCost" jdbcType="DECIMAL" />
    <result column="arrived_time" property="arrivedTime" jdbcType="TIMESTAMP" />
    <result column="statuz" property="statuz" jdbcType="INTEGER" />
    <result column="arrived_flag" property="arrivedFlag" jdbcType="INTEGER" />
    <result column="accept_status" property="acceptStatus" jdbcType="INTEGER" />
    <result column="arrived_address" property="arrivedAddress" jdbcType="VARCHAR" />
    <result column="data_table" property="dataTable" jdbcType="INTEGER" />
    <result column="create_user_id" property="createUserId" jdbcType="INTEGER" />
    <result column="customer_service_id" property="customerServiceId" jdbcType="INTEGER" />
     <result column="after_repair" property="afterRepair" jdbcType="VARCHAR" />
      <result column="before_repair" property="beforeRepair" jdbcType="VARCHAR" />
    <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 Thu May 07 14:53:11 CST 2020.
    -->
    id, expression, used_methods, imgs, brand_id, session_id, address, charger_id, travel_cost, 
    service_cost, total_cost, arrived_time, statuz, arrived_flag, arrived_address, data_table, 
    create_user_id, create_by, create_time, update_by, update_time,customer_service_id
  </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 Thu May 07 14:53:11 CST 2020.
    -->
    select 
    <include refid="Base_Column_List" />
    from lift_case
    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 Thu May 07 14:53:11 CST 2020.
    -->
    delete from lift_case
    where id = #{id,jdbcType=INTEGER}
  </delete>
  <insert id="insertSelective" parameterType="cn.com.ty.lift.ud.chat.mapper.entity.LiftCaseEntity" useGeneratedKeys="true" keyProperty="id">
    <!--
      WARNING - @mbggenerated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Thu May 07 14:53:11 CST 2020.
    -->
    insert into lift_case
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        id,
      </if>
      <if test="expression != null" >
        expression,
      </if>
      <if test="usedMethods != null" >
        used_methods,
      </if>
      <if test="imgs != null" >
        imgs,
      </if>
      <if test="acceptStatus != null" >
        accept_status,
      </if>
      <if test="brandId != null" >
        brand_id,
      </if>
      <if test="sessionId != null" >
        session_id,
      </if>
      <if test="address != null" >
        address,
      </if>
      <if test="chargerId != null" >
        charger_id,
      </if>
      <if test="travelCost != null" >
        travel_cost,
      </if>
      <if test="serviceCost != null" >
        service_cost,
      </if>
      <if test="totalCost != null" >
        total_cost,
      </if>
      <if test="arrivedTime != null" >
        arrived_time,
      </if>
      <if test="statuz != null" >
        statuz,
      </if>
      <if test="arrivedFlag != null" >
        arrived_flag,
      </if>
      <if test="arrivedAddress != null" >
        arrived_address,
      </if>
      <if test="dataTable != null" >
        data_table,
      </if>
      <if test="createUserId != null" >
        create_user_id,
      </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="updateTime != null" >
        customer_service_id,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        #{id,jdbcType=INTEGER},
      </if>
      <if test="expression != null" >
        #{expression,jdbcType=VARCHAR},
      </if>
      <if test="usedMethods != null" >
        #{usedMethods,jdbcType=VARCHAR},
      </if>
      <if test="imgs != null" >
        #{imgs,jdbcType=VARCHAR},
      </if>
      <if test="acceptStatus != null" >
         #{acceptStatus,jdbcType=INTEGER},
      </if>
      <if test="brandId != null" >
        #{brandId,jdbcType=INTEGER},
      </if>
      <if test="sessionId != null" >
        #{sessionId,jdbcType=INTEGER},
      </if>
      <if test="address != null" >
        #{address,jdbcType=VARCHAR},
      </if>
      <if test="chargerId != null" >
        #{chargerId,jdbcType=INTEGER},
      </if>
      <if test="travelCost != null" >
        #{travelCost,jdbcType=DECIMAL},
      </if>
      <if test="serviceCost != null" >
        #{serviceCost,jdbcType=DECIMAL},
      </if>
      <if test="totalCost != null" >
        #{totalCost,jdbcType=DECIMAL},
      </if>
      <if test="arrivedTime != null" >
        #{arrivedTime,jdbcType=TIMESTAMP},
      </if>
      <if test="statuz != null" >
        #{statuz,jdbcType=INTEGER},
      </if>
      <if test="arrivedFlag != null" >
        #{arrivedFlag,jdbcType=INTEGER},
      </if>
      <if test="arrivedAddress != null" >
        #{arrivedAddress,jdbcType=VARCHAR},
      </if>
      <if test="dataTable != null" >
        #{dataTable,jdbcType=INTEGER},
      </if>
      <if test="createUserId != null" >
        #{createUserId,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="customerServiceId != null" >
        #{customerServiceId},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="cn.com.ty.lift.ud.chat.mapper.entity.LiftCaseEntity" >
    <!--
      WARNING - @mbggenerated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Thu May 07 14:53:11 CST 2020.
    -->
    update lift_case
    <set >
      <if test="expression != null" >
        expression = #{expression,jdbcType=VARCHAR},
      </if>
      <if test="usedMethods != null" >
        used_methods = #{usedMethods,jdbcType=VARCHAR},
      </if>
      <if test="imgs != null" >
        imgs = #{imgs,jdbcType=VARCHAR},
      </if>
      <if test="brandId != null" >
        brand_id = #{brandId,jdbcType=INTEGER},
      </if>
      <if test="sessionId != null" >
        session_id = #{sessionId,jdbcType=VARCHAR},
      </if>
      <if test="address != null" >
        address = #{address,jdbcType=VARCHAR},
      </if>
      <if test="chargerId != null" >
        charger_id = #{chargerId,jdbcType=INTEGER},
      </if>
      <if test="travelCost != null" >
        travel_cost = #{travelCost,jdbcType=DECIMAL},
      </if>
      <if test="serviceCost != null" >
        service_cost = #{serviceCost,jdbcType=DECIMAL},
      </if>
      <if test="totalCost != null" >
        total_cost = #{totalCost,jdbcType=DECIMAL},
      </if>
      <if test="arrivedTime != null" >
        arrived_time = #{arrivedTime,jdbcType=TIMESTAMP},
      </if>
      <if test="statuz != null" >
        statuz = #{statuz,jdbcType=INTEGER},
      </if>
      <if test="acceptStatus != null" >
        accept_status = #{acceptStatus,jdbcType=INTEGER},
      </if>
      <if test="arrivedFlag != null" >
        arrived_flag = #{arrivedFlag,jdbcType=INTEGER},
      </if>
      <if test="arrivedAddress != null" >
        arrived_address = #{arrivedAddress,jdbcType=VARCHAR},
      </if>
      <if test="dataTable != null" >
        data_table = #{dataTable,jdbcType=INTEGER},
      </if>
      <if test="createUserId != null" >
        create_user_id = #{createUserId,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="customerServiceId != null" >
        customer_service_id = #{customerServiceId,jdbcType=TIMESTAMP},
      </if>
      <if test="isPlatformSpecified != null" >
        is_platform_specified = #{isPlatformSpecified,jdbcType=TIMESTAMP},
      </if>
    </set>
    where id = #{id,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="cn.com.ty.lift.ud.chat.mapper.entity.LiftCaseEntity" >
    <!--
      WARNING - @mbggenerated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Thu May 07 14:53:11 CST 2020.
    -->
    update lift_case
    set expression = #{expression,jdbcType=VARCHAR},
      used_methods = #{usedMethods,jdbcType=VARCHAR},
      imgs = #{imgs,jdbcType=VARCHAR},
      brand_id = #{brandId,jdbcType=INTEGER},
      session_id = #{sessionId,jdbcType=VARCHAR},
      address = #{address,jdbcType=VARCHAR},
      charger_id = #{chargerId,jdbcType=INTEGER},
      travel_cost = #{travelCost,jdbcType=DECIMAL},
      service_cost = #{serviceCost,jdbcType=DECIMAL},
      total_cost = #{totalCost,jdbcType=DECIMAL},
      arrived_time = #{arrivedTime,jdbcType=TIMESTAMP},
      statuz = #{statuz,jdbcType=INTEGER},
      arrived_flag = #{arrivedFlag,jdbcType=INTEGER},
      arrived_address = #{arrivedAddress,jdbcType=VARCHAR},
      data_table = #{dataTable,jdbcType=INTEGER},
      create_user_id = #{createUserId,jdbcType=INTEGER},
      create_by = #{createBy,jdbcType=VARCHAR},
      create_time = #{createTime,jdbcType=TIMESTAMP},
      update_by = #{updateBy,jdbcType=VARCHAR},
      customer_service_id = #{customerServiceId,jdbcType=VARCHAR},
      update_time = #{updateTime,jdbcType=TIMESTAMP}
    where id = #{id,jdbcType=INTEGER}
  </update>


  <!-- 查询用户全部诊单 -->
  <select id="queryUserAll" resultType="cn.com.ty.lift.ud.chat.mapper.entity.LiftCaseEntity">
    SELECT customer.`name` as customerName ,t.*,brand.`name` as brandName,
    que.examine_flag examineFlag,que.not_examine_reason notExamineReason
    from lift_case as t
    LEFT JOIN user_info AS ui ON t.create_user_id = ui.user_id
    LEFT JOIN user_info AS ci ON t.charger_id = ci.user_id
    LEFT JOIN user_info as customer ON t.customer_service_id = customer.user_id
    LEFT JOIN lift_brand as brand ON brand.id = t.brand_id
    left join question_bank que on que.lift_case_id = t.id
    where 1=1 and t.create_user_id=#{query.createUserId}
    ORDER BY t.create_time DESC
    limit #{query.pageIndex},#{query.pageSize}
  </select>
  <select id="queryUserAllCount" resultType="java.lang.Integer">
    SELECT count(0)
    from lift_case as t
    LEFT JOIN user_info AS ui ON t.create_user_id = ui.user_id
    LEFT JOIN user_info AS ci ON t.charger_id = ci.user_id
    LEFT JOIN user_info as customer ON t.customer_service_id = customer.user_id
    LEFT JOIN lift_brand as brand ON brand.id = t.brand_id
    left join question_bank que on que.lift_case_id = t.id
    where 1=1 and t.create_user_id=#{query.createUserId}
  </select>


  <!-- 查询专家全部诊单 -->
  <select id="queryChargeAll" resultType="cn.com.ty.lift.ud.chat.mapper.entity.LiftCaseEntity">
    SELECT customer.`name` as customerName ,t.*,brand.`name` as brandName,
    que.examine_flag examineFlag,que.not_examine_reason notExamineReason
    from lift_case as t
    LEFT JOIN user_info AS ui ON t.create_user_id = ui.user_id
    LEFT JOIN user_info AS ci ON t.charger_id = ci.user_id
    LEFT JOIN user_info as customer ON t.customer_service_id = customer.user_id
    LEFT JOIN lift_brand as brand ON brand.id = t.brand_id
    left join question_bank que on que.lift_case_id = t.id
    where 1=1 and t.charger_id=#{query.chargerId}
    ORDER BY t.create_time DESC
    limit #{query.pageIndex},#{query.pageSize}
  </select>
  <select id="queryChargeAllCount" resultType="java.lang.Integer">
    SELECT count(0)
    from lift_case as t
    LEFT JOIN user_info AS ui ON t.create_user_id = ui.user_id
    LEFT JOIN user_info AS ci ON t.charger_id = ci.user_id
    LEFT JOIN user_info as customer ON t.customer_service_id = customer.user_id
    LEFT JOIN lift_brand as brand ON brand.id = t.brand_id
    left join question_bank que on que.lift_case_id = t.id
    where 1=1 and t.charger_id=#{query.chargerId}
  </select>


  <!-- 查询用户代付款诊单 -->
  <select id="queryToPay" resultType="cn.com.ty.lift.ud.chat.mapper.entity.LiftCaseEntity">
    select * from (
        SELECT customer.`name` as customerName ,t.*,brand.`name` as brandName,
        que.examine_flag examineFlag,que.not_examine_reason notExamineReason
        from lift_case as t
        LEFT JOIN user_info AS ui ON t.create_user_id = ui.user_id
        LEFT JOIN user_info AS ci ON t.charger_id = ci.user_id
        LEFT JOIN user_info as customer ON t.customer_service_id = customer.user_id
        LEFT JOIN lift_brand as brand ON brand.id = t.brand_id
        left join question_bank que on que.lift_case_id = t.id
        where 1=1 and t.create_user_id=#{query.createUserId} and t.data_table = 1 and t.statuz = 0
        union
        SELECT customer.`name` as customerName ,t.*,brand.`name` as brandName,
        que.examine_flag examineFlag,que.not_examine_reason notExamineReason
        from lift_case as t
        LEFT JOIN user_info AS ui ON t.create_user_id = ui.user_id
        LEFT JOIN user_info AS ci ON t.charger_id = ci.user_id
        LEFT JOIN user_info as customer ON t.customer_service_id = customer.user_id
        LEFT JOIN lift_brand as brand ON brand.id = t.brand_id
        left join question_bank que on que.lift_case_id = t.id
        where 1=1 and t.create_user_id=#{query.createUserId} and t.data_table = 2 and t.statuz = 2
    )temp
    ORDER BY temp.create_time DESC
    limit #{query.pageIndex},#{query.pageSize}
  </select>
  <select id="queryToPayCount" resultType="java.lang.Integer">
    select count(0) from (
        SELECT customer.`name` as customerName ,t.*,brand.`name` as brandName,
        que.examine_flag examineFlag,que.not_examine_reason notExamineReason
        from lift_case as t
        LEFT JOIN user_info AS ui ON t.create_user_id = ui.user_id
        LEFT JOIN user_info AS ci ON t.charger_id = ci.user_id
        LEFT JOIN user_info as customer ON t.customer_service_id = customer.user_id
        LEFT JOIN lift_brand as brand ON brand.id = t.brand_id
        left join question_bank que on que.lift_case_id = t.id
        where 1=1 and t.create_user_id=#{query.createUserId} and t.data_table = 1 and t.statuz = 0
        union
        SELECT customer.`name` as customerName ,t.*,brand.`name` as brandName,
        que.examine_flag examineFlag,que.not_examine_reason notExamineReason
        from lift_case as t
        LEFT JOIN user_info AS ui ON t.create_user_id = ui.user_id
        LEFT JOIN user_info AS ci ON t.charger_id = ci.user_id
        LEFT JOIN user_info as customer ON t.customer_service_id = customer.user_id
        LEFT JOIN lift_brand as brand ON brand.id = t.brand_id
        left join question_bank que on que.lift_case_id = t.id
        where 1=1 and t.create_user_id=#{query.createUserId} and t.data_table = 2 and t.statuz = 2
    )temp
  </select>

  <!-- 查询用户待确认诊单 -->
  <select id="queryUserToSure" resultType="cn.com.ty.lift.ud.chat.mapper.entity.LiftCaseEntity">
    select * from (
        SELECT customer.`name` as customerName ,t.*,brand.`name` as brandName,
        que.examine_flag examineFlag,que.not_examine_reason notExamineReason
        from lift_case as t
        LEFT JOIN user_info AS ui ON t.create_user_id = ui.user_id
        LEFT JOIN user_info AS ci ON t.charger_id = ci.user_id
        LEFT JOIN user_info as customer ON t.customer_service_id = customer.user_id
        LEFT JOIN lift_brand as brand ON brand.id = t.brand_id
        left join question_bank que on que.lift_case_id = t.id
        where 1=1 and t.create_user_id=#{query.createUserId} and t.data_table = 1 and FIND_IN_SET(t.statuz,'3,4')
        union
        SELECT customer.`name` as customerName ,t.*,brand.`name` as brandName,
        que.examine_flag examineFlag,que.not_examine_reason notExamineReason
        from lift_case as t
        LEFT JOIN user_info AS ui ON t.create_user_id = ui.user_id
        LEFT JOIN user_info AS ci ON t.charger_id = ci.user_id
        LEFT JOIN user_info as customer ON t.customer_service_id = customer.user_id
        LEFT JOIN lift_brand as brand ON brand.id = t.brand_id
        left join question_bank que on que.lift_case_id = t.id
        where 1=1 and t.create_user_id=#{query.createUserId} and t.data_table = 2 and FIND_IN_SET(t.statuz,'3,4')
    )temp
    ORDER BY temp.create_time DESC
    limit #{query.pageIndex},#{query.pageSize}
  </select>
  <select id="queryUserToSureCount" resultType="java.lang.Integer">
    select count(0) from (
        SELECT customer.`name` as customerName ,t.*,brand.`name` as brandName,
        que.examine_flag examineFlag,que.not_examine_reason notExamineReason
        from lift_case as t
        LEFT JOIN user_info AS ui ON t.create_user_id = ui.user_id
        LEFT JOIN user_info AS ci ON t.charger_id = ci.user_id
        LEFT JOIN user_info as customer ON t.customer_service_id = customer.user_id
        LEFT JOIN lift_brand as brand ON brand.id = t.brand_id
        left join question_bank que on que.lift_case_id = t.id
        where 1=1 and t.create_user_id=#{query.createUserId} and t.data_table = 1 and FIND_IN_SET(t.statuz,'3,4')
        union
        SELECT customer.`name` as customerName ,t.*,brand.`name` as brandName,
        que.examine_flag examineFlag,que.not_examine_reason notExamineReason
        from lift_case as t
        LEFT JOIN user_info AS ui ON t.create_user_id = ui.user_id
        LEFT JOIN user_info AS ci ON t.charger_id = ci.user_id
        LEFT JOIN user_info as customer ON t.customer_service_id = customer.user_id
        LEFT JOIN lift_brand as brand ON brand.id = t.brand_id
        left join question_bank que on que.lift_case_id = t.id
        where 1=1 and t.create_user_id=#{query.createUserId} and t.data_table = 2 and FIND_IN_SET(t.statuz,'3,4')
    )temp
  </select>
  <!-- 查询用户待评价诊单 -->
  <select id="queryToEva" resultType="cn.com.ty.lift.ud.chat.mapper.entity.LiftCaseEntity">
    select * from (
        SELECT customer.`name` as customerName ,t.*,brand.`name` as brandName,
        que.examine_flag examineFlag,que.not_examine_reason notExamineReason
        from lift_case as t
        LEFT JOIN user_info AS ui ON t.create_user_id = ui.user_id
        LEFT JOIN user_info AS ci ON t.charger_id = ci.user_id
        LEFT JOIN user_info as customer ON t.customer_service_id = customer.user_id
        LEFT JOIN lift_brand as brand ON brand.id = t.brand_id
        left join question_bank que on que.lift_case_id = t.id
        where 1=1 and t.create_user_id=#{query.createUserId} and t.data_table = 1 and FIND_IN_SET(t.statuz,'5')
        union
        SELECT customer.`name` as customerName ,t.*,brand.`name` as brandName,
        que.examine_flag examineFlag,que.not_examine_reason notExamineReason
        from lift_case as t
        LEFT JOIN user_info AS ui ON t.create_user_id = ui.user_id
        LEFT JOIN user_info AS ci ON t.charger_id = ci.user_id
        LEFT JOIN user_info as customer ON t.customer_service_id = customer.user_id
        LEFT JOIN lift_brand as brand ON brand.id = t.brand_id
        left join question_bank que on que.lift_case_id = t.id
        where 1=1 and t.create_user_id=#{query.createUserId} and t.data_table = 2 and FIND_IN_SET(t.statuz,'5')
    )temp
    ORDER BY temp.create_time DESC
    limit #{query.pageIndex},#{query.pageSize}
  </select>
  <select id="queryToEvaCount" resultType="java.lang.Integer">
    select count(0) from (
        SELECT customer.`name` as customerName ,t.*,brand.`name` as brandName,
        que.examine_flag examineFlag,que.not_examine_reason notExamineReason
        from lift_case as t
        LEFT JOIN user_info AS ui ON t.create_user_id = ui.user_id
        LEFT JOIN user_info AS ci ON t.charger_id = ci.user_id
        LEFT JOIN user_info as customer ON t.customer_service_id = customer.user_id
        LEFT JOIN lift_brand as brand ON brand.id = t.brand_id
        left join question_bank que on que.lift_case_id = t.id
        where 1=1 and t.create_user_id=#{query.createUserId} and t.data_table = 1 and FIND_IN_SET(t.statuz,'5')
        union
        SELECT customer.`name` as customerName ,t.*,brand.`name` as brandName,
        que.examine_flag examineFlag,que.not_examine_reason notExamineReason
        from lift_case as t
        LEFT JOIN user_info AS ui ON t.create_user_id = ui.user_id
        LEFT JOIN user_info AS ci ON t.charger_id = ci.user_id
        LEFT JOIN user_info as customer ON t.customer_service_id = customer.user_id
        LEFT JOIN lift_brand as brand ON brand.id = t.brand_id
        left join question_bank que on que.lift_case_id = t.id
        where 1=1 and t.create_user_id=#{query.createUserId} and t.data_table = 2 and FIND_IN_SET(t.statuz,'5')
    )temp
  </select>
  <!-- 查询专家待报价 -->
  <select id="queryToOffer" resultType="cn.com.ty.lift.ud.chat.mapper.entity.LiftCaseEntity">
    SELECT customer.`name` as customerName ,t.*,brand.`name` as brandName,
    que.examine_flag examineFlag,que.not_examine_reason notExamineReason
    from lift_case as t
    LEFT JOIN user_info AS ui ON t.create_user_id = ui.user_id
    LEFT JOIN user_info AS ci ON t.charger_id = ci.user_id
    LEFT JOIN user_info as customer ON t.customer_service_id = customer.user_id
    LEFT JOIN lift_brand as brand ON brand.id = t.brand_id
    left join question_bank que on que.lift_case_id = t.id
    where 1=1 and t.charger_id=#{query.chargerId} and t.data_table = 2 and FIND_IN_SET(t.statuz,'1')
    ORDER BY t.create_time DESC
    limit #{query.pageIndex},#{query.pageSize}
  </select>
  <select id="queryToOfferCount" resultType="java.lang.Integer">
    SELECT count(0)
    from lift_case as t
    LEFT JOIN user_info AS ui ON t.create_user_id = ui.user_id
    LEFT JOIN user_info AS ci ON t.charger_id = ci.user_id
    LEFT JOIN user_info as customer ON t.customer_service_id = customer.user_id
    LEFT JOIN lift_brand as brand ON brand.id = t.brand_id
    left join question_bank que on que.lift_case_id = t.id
    where 1=1 and t.charger_id=#{query.chargerId} and t.data_table = 2 and FIND_IN_SET(t.statuz,'1')
  </select>
  <!-- 查询专家待打卡 -->
  <select id="queryToClock" resultType="cn.com.ty.lift.ud.chat.mapper.entity.LiftCaseEntity">
    SELECT customer.`name` as customerName ,t.*,brand.`name` as brandName,
    que.examine_flag examineFlag,que.not_examine_reason notExamineReason
    from lift_case as t
    LEFT JOIN user_info AS ui ON t.create_user_id = ui.user_id
    LEFT JOIN user_info AS ci ON t.charger_id = ci.user_id
    LEFT JOIN user_info as customer ON t.customer_service_id = customer.user_id
    LEFT JOIN lift_brand as brand ON brand.id = t.brand_id
    left join question_bank que on que.lift_case_id = t.id
    where 1=1 and t.charger_id=#{query.chargerId} and t.data_table = 2 and FIND_IN_SET(t.statuz,'3') and t.arrived_Flag=0
    ORDER BY t.create_time DESC
    limit #{query.pageIndex},#{query.pageSize}
  </select>
  <select id="queryToClockCount" resultType="java.lang.Integer">
    SELECT count(0)
    from lift_case as t
    LEFT JOIN user_info AS ui ON t.create_user_id = ui.user_id
    LEFT JOIN user_info AS ci ON t.charger_id = ci.user_id
    LEFT JOIN user_info as customer ON t.customer_service_id = customer.user_id
    LEFT JOIN lift_brand as brand ON brand.id = t.brand_id
    left join question_bank que on que.lift_case_id = t.id
    where 1=1 and t.charger_id=#{query.chargerId} and t.data_table = 2 and FIND_IN_SET(t.statuz,'3') and t.arrived_Flag=0
  </select>
  <!-- 查询专家待确认诊单 -->
  <select id="queryChargeToSure" resultType="cn.com.ty.lift.ud.chat.mapper.entity.LiftCaseEntity">
    select * from (
        SELECT customer.`name` as customerName ,t.*,brand.`name` as brandName,
        que.examine_flag examineFlag,que.not_examine_reason notExamineReason
        from lift_case as t
        LEFT JOIN user_info AS ui ON t.create_user_id = ui.user_id
        LEFT JOIN user_info AS ci ON t.charger_id = ci.user_id
        LEFT JOIN user_info as customer ON t.customer_service_id = customer.user_id
        LEFT JOIN lift_brand as brand ON brand.id = t.brand_id
        left join question_bank que on que.lift_case_id = t.id
        where 1=1 and t.create_user_id=#{query.createUserId} and t.data_table = 1 and FIND_IN_SET(t.statuz,'2,3')
        union
        SELECT customer.`name` as customerName ,t.*,brand.`name` as brandName,
        que.examine_flag examineFlag,que.not_examine_reason notExamineReason
        from lift_case as t
        LEFT JOIN user_info AS ui ON t.create_user_id = ui.user_id
        LEFT JOIN user_info AS ci ON t.charger_id = ci.user_id
        LEFT JOIN user_info as customer ON t.customer_service_id = customer.user_id
        LEFT JOIN lift_brand as brand ON brand.id = t.brand_id
        left join question_bank que on que.lift_case_id = t.id
        where 1=1 and t.charger_id=#{query.chargerId} and t.data_table = 2 and FIND_IN_SET(t.statuz,'3') and t.arrived_Flag=1
    )temp
    ORDER BY temp.create_time DESC
    limit #{query.pageIndex},#{query.pageSize}
  </select>
  <select id="queryChargeToSureCount" resultType="java.lang.Integer">
    SELECT count(0) from (
        from lift_case as t
        LEFT JOIN user_info AS ui ON t.create_user_id = ui.user_id
        LEFT JOIN user_info AS ci ON t.charger_id = ci.user_id
        LEFT JOIN user_info as customer ON t.customer_service_id = customer.user_id
        LEFT JOIN lift_brand as brand ON brand.id = t.brand_id
        left join question_bank que on que.lift_case_id = t.id
        where 1=1 and t.create_user_id=#{query.createUserId} and t.data_table = 1 and FIND_IN_SET(t.statuz,'2,3')
        union
        SELECT customer.`name` as customerName ,t.*,brand.`name` as brandName,
        que.examine_flag examineFlag,que.not_examine_reason notExamineReason
        from lift_case as t
        LEFT JOIN user_info AS ui ON t.create_user_id = ui.user_id
        LEFT JOIN user_info AS ci ON t.charger_id = ci.user_id
        LEFT JOIN user_info as customer ON t.customer_service_id = customer.user_id
        LEFT JOIN lift_brand as brand ON brand.id = t.brand_id
        left join question_bank que on que.lift_case_id = t.id
        where 1=1 and t.charger_id=#{query.chargerId} and t.data_table = 2 and FIND_IN_SET(t.statuz,'3') and t.arrived_Flag=1
    )temp
  </select>
  <!-- 查询专家待归档 -->
  <select id="queryToClock" resultType="cn.com.ty.lift.ud.chat.mapper.entity.LiftCaseEntity">
    SELECT customer.`name` as customerName ,t.*,brand.`name` as brandName,
    que.examine_flag examineFlag,que.not_examine_reason notExamineReason
    from lift_case as t
    LEFT JOIN user_info AS ui ON t.create_user_id = ui.user_id
    LEFT JOIN user_info AS ci ON t.charger_id = ci.user_id
    LEFT JOIN user_info as customer ON t.customer_service_id = customer.user_id
    LEFT JOIN lift_brand as brand ON brand.id = t.brand_id
    left join question_bank que on que.lift_case_id = t.id
    where 1=1 and t.charger_id=#{query.chargerId} and t.data_table = 2 and FIND_IN_SET(t.statuz,'4')
    ORDER BY t.create_time DESC
    limit #{query.pageIndex},#{query.pageSize}
  </select>
  <select id="queryToClockCount" resultType="java.lang.Integer">
    SELECT count(0)
    from lift_case as t
    LEFT JOIN user_info AS ui ON t.create_user_id = ui.user_id
    LEFT JOIN user_info AS ci ON t.charger_id = ci.user_id
    LEFT JOIN user_info as customer ON t.customer_service_id = customer.user_id
    LEFT JOIN lift_brand as brand ON brand.id = t.brand_id
    left join question_bank que on que.lift_case_id = t.id
    where 1=1 and t.charger_id=#{query.chargerId} and t.data_table = 2 and FIND_IN_SET(t.statuz,'4')
  </select>
  <!-- 查询专家工作台 -->
  <select id="queryWorkbench" resultType="cn.com.ty.lift.ud.chat.mapper.entity.LiftCaseEntity">
    select * from (
        SELECT customer.`name` as customerName ,t.*,brand.`name` as brandName,
        que.examine_flag examineFlag,que.not_examine_reason notExamineReason
        from lift_case as t
        LEFT JOIN user_info AS ui ON t.create_user_id = ui.user_id
        LEFT JOIN user_info AS ci ON t.charger_id = ci.user_id
        LEFT JOIN user_info as customer ON t.customer_service_id = customer.user_id
        LEFT JOIN lift_brand as brand ON brand.id = t.brand_id
        left join question_bank que on que.lift_case_id = t.id
        where 1=1 and t.create_user_id=#{query.createUserId} and t.data_table = 1 and FIND_IN_SET(t.statuz,'1')
        union
        SELECT customer.`name` as customerName ,t.*,brand.`name` as brandName,
        que.examine_flag examineFlag,que.not_examine_reason notExamineReason
        from lift_case as t
        LEFT JOIN user_info AS ui ON t.create_user_id = ui.user_id
        LEFT JOIN user_info AS ci ON t.charger_id = ci.user_id
        LEFT JOIN user_info as customer ON t.customer_service_id = customer.user_id
        LEFT JOIN lift_brand as brand ON brand.id = t.brand_id
        left join question_bank que on que.lift_case_id = t.id
        where 1=1 and t.charger_id=#{query.chargerId} and t.data_table = 2 and FIND_IN_SET(t.statuz,'0')
    )temp
    ORDER BY temp.create_time DESC
    limit #{query.pageIndex},#{query.pageSize}
  </select>
  <select id="queryWorkbenchCount" resultType="java.lang.Integer">
    SELECT count(0) from (
        SELECT customer.`name` as customerName ,t.*,brand.`name` as brandName,
        que.examine_flag examineFlag,que.not_examine_reason notExamineReason
        from lift_case as t
        LEFT JOIN user_info AS ui ON t.create_user_id = ui.user_id
        LEFT JOIN user_info AS ci ON t.charger_id = ci.user_id
        LEFT JOIN user_info as customer ON t.customer_service_id = customer.user_id
        LEFT JOIN lift_brand as brand ON brand.id = t.brand_id
        left join question_bank que on que.lift_case_id = t.id
        where 1=1 and t.create_user_id=#{query.createUserId} and t.data_table = 1 and FIND_IN_SET(t.statuz,'1')
        union
        SELECT customer.`name` as customerName ,t.*,brand.`name` as brandName,
        que.examine_flag examineFlag,que.not_examine_reason notExamineReason
        from lift_case as t
        LEFT JOIN user_info AS ui ON t.create_user_id = ui.user_id
        LEFT JOIN user_info AS ci ON t.charger_id = ci.user_id
        LEFT JOIN user_info as customer ON t.customer_service_id = customer.user_id
        LEFT JOIN lift_brand as brand ON brand.id = t.brand_id
        left join question_bank que on que.lift_case_id = t.id
        where 1=1 and t.charger_id=#{query.chargerId} and t.data_table = 2 and FIND_IN_SET(t.statuz,'0')
    )temp
  </select>
  <!-- 后台监控中心 -->
  <select id="queryMonitoringCenter" resultType="cn.com.ty.lift.ud.chat.mapper.entity.LiftCaseEntity">
    SELECT customer.`name` as customerName ,t.*,brand.`name` as brandName,
    que.examine_flag examineFlag,que.not_examine_reason notExamineReason
    from lift_case as t
    LEFT JOIN user_info AS ui ON t.create_user_id = ui.user_id
    LEFT JOIN user_info AS ci ON t.charger_id = ci.user_id
    LEFT JOIN user_info as customer ON t.customer_service_id = customer.user_id
    LEFT JOIN lift_brand as brand ON brand.id = t.brand_id
    left join question_bank que on que.lift_case_id = t.id
    ORDER BY t.update_time DESC
    limit #{query.pageIndex},#{query.pageSize}
  </select>
  <select id="queryMonitoringCenterCount" resultType="java.lang.Integer">
    SELECT count(0)
    from lift_case as t
    LEFT JOIN user_info AS ui ON t.create_user_id = ui.user_id
    LEFT JOIN user_info AS ci ON t.charger_id = ci.user_id
    LEFT JOIN user_info as customer ON t.customer_service_id = customer.user_id
    LEFT JOIN lift_brand as brand ON brand.id = t.brand_id
    left join question_bank que on que.lift_case_id = t.id
  </select>




  <select id="selectByQuery" resultType="cn.com.ty.lift.ud.chat.mapper.entity.LiftCaseEntity">
    SELECT customer.`name` as customerName ,t.*,brand.`name` as brandName,
    que.examine_flag examineFlag,que.not_examine_reason notExamineReason
    from lift_case as t
    LEFT JOIN user_info AS ui ON t.create_user_id = ui.user_id
    LEFT JOIN user_info AS ci ON t.charger_id = ci.user_id
    LEFT JOIN user_info as customer ON t.customer_service_id = customer.user_id
    LEFT JOIN lift_brand as brand ON brand.id = t.brand_id
    left join question_bank que on que.lift_case_id = t.id
    where 1=1   
    <if test="page.statuz1 != null and page.statuz2 != null">
      <!-- and ((t.data_table=1 and t.statuz=#{page.statuz1}) or (t.data_table=2 and t.statuz=#{page.statuz2})) -->
      and ((t.data_table=1 and FIND_IN_SET(t.statuz,#{page.statuz1})) or (t.data_table=2 and FIND_IN_SET(t.statuz,#{page.statuz2})))
    </if>
    <if test="page.statuz1 != null and page.statuz2 == null">
      <!-- and(t.data_table=1 and t.statuz=#{page.statuz1}) -->
      and(t.data_table=1 and FIND_IN_SET(t.statuz,#{page.statuz1}))
    </if>
    <if test="page.statuz2 != null and page.statuz1 == null">
      <!-- and (t.data_table=2 and t.statuz=#{page.statuz2}) -->
     and (t.data_table=2 and FIND_IN_SET(t.statuz,#{page.statuz2}))
    </if>
    <!-- <if test="page.arrivedFlag != null and page.statuz2 == 3" > -->
    <if test="page.arrivedFlag != null and page.dataTable == 2" >
     and t.arrived_Flag=#{page.arrivedFlag}
    </if>
    <if test="page.haveSessionId == true" >
     and t.session_id IS NOT NULL
    </if>
    <if test="page.originator != null" >
     and ui.name LIKE concat('%',#{page.originator},'%')
    </if>
    <if test="page.brandName != null" >
     and brand.name LIKE concat('%',#{page.brandName},'%')
    </if>
    <if test="page.chargeName != null" >
      and ci.name LIKE concat('%',#{page.chargeName},'%')
    </if>
    <if test="page.acceptStatus != null" >
     and t.accept_status=#{page.acceptStatus}
    </if>
    <if test="page.isInvoice != null" >
     and t.is_invoice=#{page.isInvoice}
    </if>
    <if test="page.createUserId != null" >
     and t.create_user_id=#{page.createUserId}
    </if>
    <if test="page.chargerId != null" >
     and t.charger_id=#{page.chargerId}
    </if>
    <if test="page.sessionId != null" >
     and t.session_id=#{page.sessionId}
    </if>
    ORDER BY t.create_time DESC
    limit #{page.pageIndex},#{page.pageSize}
  </select>
  <select id="countByQuery" resultType="java.lang.Integer">
     SELECT count(0)from lift_case as t  
     LEFT JOIN user_info AS ui ON t.create_user_id = ui.user_id
     LEFT JOIN user_info AS ci ON t.charger_id = ci.user_id
    LEFT JOIN user_info as customer ON t.customer_service_id = customer.user_id
    LEFT JOIN lift_brand as brand ON brand.id = t.brand_id
    where 1=1
    <if test="page.statuz1 != null and page.statuz2 != null">
      <!-- and ((t.data_table=1 and t.statuz=#{page.statuz1}) or (t.data_table=2 and t.statuz=#{page.statuz2})) -->
      and ((t.data_table=1 and FIND_IN_SET(t.statuz,#{page.statuz1})) or (t.data_table=2 and FIND_IN_SET(t.statuz,#{page.statuz2})))
    </if>
    <if test="page.statuz1 != null and page.statuz2 == null">
      <!--  and(t.data_table=1 and t.statuz=#{page.statuz1}) -->
      and(t.data_table=1 and FIND_IN_SET(t.statuz,#{page.statuz1}))
    </if>
    <if test="page.statuz2 != null and page.statuz1 == null">
      <!-- and (t.data_table=2 and t.statuz=#{page.statuz2}) -->
      and (t.data_table=2 and FIND_IN_SET(t.statuz,#{page.statuz2}))
    </if>
    <!-- <if test="page.arrivedFlag != null and page.statuz2 == 3" > -->
    <if test="page.arrivedFlag != null and page.dataTable == 2" >
     and t.arrived_Flag=#{page.arrivedFlag}
    </if>
    <if test="page.acceptStatus != null" >
     and t.accept_status=#{page.acceptStatus}
    </if>
    <if test="page.originator != null" >
     and ui.name LIKE concat('%',#{page.originator},'%')
    </if>
    <if test="page.brandName != null" >
     and brand.name LIKE concat('%',#{page.brandName},'%')
    </if>
     <if test="page.chargeName != null" >
     and ci.name LIKE concat('%',#{page.chargeName},'%')
    </if>
    <if test="page.haveSessionId == true" >
     and t.session_id IS NOT NULL
    </if>
    <if test="page.isInvoice != null" >
     and t.is_invoice=#{page.isInvoice}
    </if>
    <if test="page.createUserId != null" >
     and t.create_user_id=#{page.createUserId}
    </if>
    <if test="page.chargerId != null" >
     and t.charger_id=#{page.chargerId}
    </if>
  </select>
  
  
  <select id="queryPageByInvoiceId" resultType="cn.com.ty.lift.ud.chat.mapper.entity.LiftCaseEntity" >
    SELECT c.* FROM apply_invoice a LEFT JOIN apply_invoice_item b ON  a.id=b.`invoice_id` LEFT JOIN lift_case c ON b.`data_id`=c.id
     WHERE a.id=#{id}
    limit #{page.pageIndex},#{page.pageSize}
  </select>
  <select id="countByInvoiceId" resultType="java.lang.Integer">
    SELECT count(*) FROM apply_invoice a LEFT JOIN apply_invoice_item b ON  a.id=b.`invoice_id` LEFT JOIN lift_case c ON b.`data_id`=c.id
    WHERE a.id=#{id}
  </select>
  
  <select id="getAllById" resultType="cn.com.ty.lift.ud.chat.mapper.entity.LiftCaseEntity">
  SELECT t.*,brand.`name` as brandName from lift_case as t
    LEFT JOIN lift_brand as brand ON brand.id = t.brand_id
    where t.id=#{id}
  </select>


  <update id="cancelCase">
    update lift_case set statuz=9 where id=#{id}
  </update>

  <select id="getAllCaseToInvoice" resultType="cn.com.ty.lift.ud.chat.mapper.entity.LiftCaseEntity">
    SELECT t.*,users.`name` AS NAME,users.avatar_url AS avatarUrl,brand.`name` AS brandName
    FROM lift_case AS t
    LEFT JOIN user_info AS users ON t.create_user_id = users.user_id
    LEFT JOIN lift_brand AS brand ON brand.id = t.brand_id
    WHERE ((t.data_table=1 AND t.statuz IN (4,5,6)) OR (t.data_table=2 AND t.statuz IN (5,6)))
    AND is_invoice=0
    AND t.id NOT IN (SELECT data_id FROM lift_case_appeal WHERE statuz=4)
    AND create_user_id=#{userId}
  </select>


  <select id="selectDealingCount" resultType="java.lang.Integer">
  select count(0) from (
  select * from lift_case where charger_id=#{userId} and data_table = 1 and FIND_IN_SET(statuz,'1,2,3,4,5,7,8')
  union
  select * from lift_case where charger_id=#{userId} and data_table = 2 and FIND_IN_SET(statuz,'0,1,2,3,4,5,7,8')
  )temp
  </select>
</mapper>