<?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.github.wxiaoqi.security.admin.mapper.UserCommentMapper">


    <select id="selectList"  resultType="com.github.wxiaoqi.security.admin.vo.UserCommentVo" parameterType="com.github.wxiaoqi.security.admin.dto.UserCommentFindDTO">
        SELECT
        c.*,
        IFNULL(d1.realname,d1.nickname) as userName,
        d1.headimgurl as headimgurl,
        IFNULL(d2.realname,d2.nickname) as toUserName,
        d2.headimgurl as toHeadimgurl,
        b.`name`as companyName,
        b.`company_pic`as companyPic
         FROM user_comment c
        LEFT JOIN app_user_detail d1 ON c.user_id=d1.userid
        LEFT JOIN app_user_detail d2 ON c.to_user_id=d2.userid
        LEFT JOIN branch_company b ON c.company_id=b.id
        <where>
              c.is_del = 0
            <if test="isGoods != null and isGoods == 2 ">
                AND  ( c.user_id = #{userId} or (c.to_user_id = #{userId} and c.company_id = #{companyId} ))
            </if>
            <if test="soureId != null ">
                AND  c.soure_id=#{soureId}
            </if>
            <if test="parentId != null ">
                AND  c.`parent_id`= #{parentId}
            </if>
            <if test="rootParentId != null  and rootParentId > 0 ">
                AND ( c.`parent_id` > 0 and  c.root_parent_id = #{rootParentId} )
            </if>
            <if test="startTime != null and  startTime > 0">
                AND  UNIX_TIMESTAMP(c.`create_time`) >= #{startTime}
            </if>
            <if test="endTime != null and  endTime > 0">
                AND  UNIX_TIMESTAMP(c.`create_time`) &lt;=  #{endTime}
            </if>
            <if test="soureType != null and soureType > 0">
                AND  c.`soure_type`= #{soureType}
            </if>
            <if test="isShow != null ">
                AND  c.`is_show`= #{isShow}
            </if>
            <if test="name != null and name != '' ">
                AND  IFNULL(d1.realname,d1.nickname) like concat('%', #{name}, '%')
            </if>
        </where>
        <choose>
            <when test="isDesc != null and isDesc == 1">
                order by c.create_time desc
            </when>
            <otherwise>
                order by c.create_time ASC
            </otherwise>
        </choose>

    </select>
    <select id="selectListByToUserId"  resultType="com.github.wxiaoqi.security.admin.vo.UserCommentVo" parameterType="com.github.wxiaoqi.security.admin.dto.UserCommentFindDTO">
        SELECT
        c.*,
        b.`name`as companyName,
        b.company_pic as companyPic,
        IFNULL(d.realname,d.nickname) as userName,
        d.headimgurl
        FROM
        (
       SELECT * FROM   user_comment
       WHERE id in (SELECT MAX(id) FROM user_comment
        WHERE is_show =1
         <choose>
             <when test="isGoods != null and isGoods == 1">
                 and   to_user_id=#{userId}
                 GROUP BY soure_id,to_user_id )
             </when>
             <when test="isGoods != null and isGoods == 2">
                 and  company_id = 0 and  soure_user_id=#{companyId}
                 GROUP BY soure_id,user_id )
             </when>
             <otherwise>
                 and   ((soure_type =1 and soure_user_id=#{userId} and parent_id = 0) or to_user_id=#{userId})
                 GROUP BY soure_id,to_user_id )
             </otherwise>
         </choose>
        ) c
        LEFT JOIN branch_company b ON c.company_id=b.id
        LEFT JOIN app_user_detail d ON c.user_id=d.userid
        order by c.create_time DESC
    </select>
</mapper>