<?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.liftCaseAppeal.mapper.LiftCaseAppealMapper">
    <resultMap id="BaseResultMap" type="cn.com.ty.lift.ud.liftCaseAppeal.mapper.entity.LiftCaseAppealEntity">
        <id column="id" property="id" jdbcType="INTEGER"/>
        <result column="content" property="content" jdbcType="VARCHAR"/>
        <result column="imgs" property="imgs" jdbcType="VARCHAR"/>
        <result column="statuz" property="statuz" jdbcType="INTEGER"/>
        <result column="data_id" property="dataId" jdbcType="INTEGER"/>
        <result column="appealer_id" property="appealerId" jdbcType="INTEGER"/>
        <result column="cancel_season" property="cancelSeason" 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">
        id, content, imgs, statuz, data_id, appealer_id, cancel_season,
        create_by, create_time, update_by, update_time
    </sql>

    <!--多表查询-->
    <select id="selectByQuery" resultType="cn.com.ty.lift.ud.liftCaseAppeal.mapper.entity.LiftCaseAppealEntity">
        select a.* ,expert.name as expertName , users.name as userName ,c.expression as expression,
        c.id as caseId ,c.session_id as sessionid ,c.before_repair as beforeRepair,c.after_repair as afterRepair,
        c.data_table as dataTable
        from lift_case_appeal a
        left join lift_case c on c.id = a.data_id
        left join user_info expert on expert.user_id = c.charger_id
        left join user_info users on users.user_id = a.appealer_id
        <where>
            ${ew.sqlSegment}
        </where>
        limit #{page.pageIndex},#{page.pageSize}
    </select>

    <!--查询总数量-->
    <select id="countByQuery" resultType="java.lang.Integer">
        select count(0) from lift_case_appeal a
        left join lift_case c on c.id = a.data_id
        left join user_info expert on expert.user_id = c.charger_id
        left join user_info users on users.user_id = a.appealer_id
        <where>
            ${ew.sqlSegment}
        </where>
    </select>
    <select id="selectListByUserForCase" resultType="cn.com.ty.lift.ud.chat.mapper.entity.LiftCaseEntity">
    SELECT b.*,customer.`name` AS customerName ,brand.`name` AS brandName ,a.statuz liftCaseAppealStatus 
    FROM lift_case_appeal a 
    LEFT JOIN lift_case b ON a.data_id=b.id 
    LEFT JOIN user_info AS customer ON b.customer_service_id = customer.user_id
    LEFT JOIN lift_brand AS brand ON brand.id = b.brand_id
    where 1=1
    <if test="page.appealerId != null" >
     and b.create_user_id=#{page.appealerId}
    </if>
    ORDER BY b.create_time DESC
    limit #{page.pageIndex},#{page.pageSize}
    </select>
    <select id="selectCountByUserForCase" resultType="java.lang.Integer">
    SELECT count(0) FROM lift_case_appeal a 
    LEFT JOIN lift_case b ON a.data_id=b.id 
    LEFT JOIN user_info AS customer ON b.customer_service_id = customer.user_id
    LEFT JOIN lift_brand AS brand ON brand.id = b.brand_id
     where 1=1
    <if test="page.appealerId != null" >
     and b.create_user_id=#{page.appealerId}
    </if>
    </select>
</mapper>