<?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.younike.system.mapper.GoodsCommentMapper">

    <resultMap id="GoodsCommentResult" type="com.younike.system.domain.GoodsComment">
        <result column="commont_id" property="commontId"/>
        <result column="member_id" property="memberId"/>
        <result column="server_id" property="serverId"/>
        <result column="order_id" property="orderId"/>
        <result column="merchant_id" property="merchantId"/>
        <result column="goods_id" property="goodsId"/>
        <result column="content" property="content"/>
        <result column="score" property="score"/>
        <result column="reply" property="reply"/>
        <result column="reply_time" property="replyTime"/>
        <result column="re_content" property="reContent"/>
        <result column="re_content_time" property="reContentTime"/>
        <result column="is_anonymous" property="isAnonymous"/>
        <result column="del_flag" property="delFlag"/>
        <result column="create_by" property="createBy"/>
        <result column="create_time" property="createTime"/>
        <result column="update_by" property="updateBy"/>
        <result column="update_time" property="updateTime"/>
        <result column="column1" property="column1"/>
        <result column="column2" property="column2"/>
        <result column="column3" property="column3"/>
    </resultMap>

    <resultMap id="GoodsCommentVoResult" type="com.younike.system.domain.vo.GoodsCommentVo">
        <result column="commont_id" property="commontId"/>
        <result column="member_id" property="memberId"/>
        <result column="server_id" property="serverId"/>
        <result column="order_id" property="orderId"/>
        <result column="merchant_id" property="merchantId"/>
        <result column="goods_id" property="goodsId"/>
        <result column="content" property="content"/>
        <result column="score" property="score"/>
        <result column="reply" property="reply"/>
        <result column="reply_time" property="replyTime"/>
        <result column="re_content" property="reContent"/>
        <result column="re_content_time" property="reContentTime"/>
        <result column="is_anonymous" property="isAnonymous"/>
        <result column="del_flag" property="delFlag"/>
        <result column="create_by" property="createBy"/>
        <result column="create_time" property="createTime"/>
        <result column="update_by" property="updateBy"/>
        <result column="update_time" property="updateTime"/>
        <result column="column1" property="column1"/>
        <result column="column2" property="column2"/>
        <result column="column3" property="column3"/>
        <result column="avatar" property="avatar"/>
        <result column="phone" property="phone"/>
        <result column="order_index" property="orderIndex"/>
        <result column="merchant_name" property="merchantName"/>
        <result column="category_name" property="categoryName"/>
        <result column="goods_name" property="goodsName"/>
        <result column="server_name" property="serverName"/>
        <result column="server_phone" property="serverPhone"/>
        <collection property="gradeList" select="gradeCommentId" column="commont_id"
                    ofType="com.younike.system.domain.vo.GoodsCommentDimensionVo">
        </collection>
        <collection property="resourceList" select="resourceCommentId" column="commont_id"
                    ofType="com.younike.system.domain.GoodsCommentResource">
        </collection>
    </resultMap>

    <sql id="selectGoodsCommentVo">select commont_id, member_id, server_id, order_id, merchant_id, goods_id, content, score, reply, reply_time, re_content, re_content_time, is_anonymous, del_flag, create_by, create_time, update_by, update_time, column1, column2, column3 from goods_comment </sql>

    <select id="commentList" resultMap="GoodsCommentVoResult" >
        select gc.* ,mer.merchant_name, g.name as goods_name, c.category_name, su.phone as server_phone,su.real_name as server_name,  m.avatar,m.phone,o.order_index
        from goods_comment gc
        LEFT JOIN merchant mer on gc.merchant_id = mer.merchant_id and mer.del_flag = 0
        LEFT JOIN goods g on gc.goods_id = g.goods_id and g.del_flag = 0
        LEFT JOIN category c on g.cate_id = c.category_id
        LEFT JOIN member m on gc.member_id=m.member_id and m.del_flag=0
        LEFT JOIN `order` o on o.order_id=gc.order_id and o.del_flag=0
        LEFT JOIN server_user su on gc.server_id = su.server_id and su.del_flag = 0
        where gc.del_flag=0
        <if test="merchantId != null and merchantId != ''"> and gc.merchant_id = #{merchantId}</if>
        <if test="merchantName != null  and merchantName != ''"> and mer.merchant_name like concat('%', #{merchantName}, '%')</if>
        <if test="goodsId != null and goodsId != ''"> and og.goods_id = #{goodsId}</if>
        <if test="name != null and name != ''"> and og.name like concat('%', #{name}, '%')</if>
        <if test="serverPhone != null  and serverPhone != ''"> and su.phone = #{serverPhone}</if>
        <if test="memberPhone != null  and memberPhone != ''"> and m.phone = #{memberPhone}</if>
        <if test="categoryId != null  and categoryId != ''"> and og.cate_id = #{categoryId}</if>

        <if test="beginTime != null and beginTime != ''"><!-- 开始时间检索 -->
            and date_format(gc.create_time,'%y%m%d') &gt;= date_format(#{beginTime},'%y%m%d')
        </if>
        <if test="endTime != null and endTime != ''"><!-- 结束时间检索 -->
            and date_format(gc.create_time,'%y%m%d') &lt;= date_format(#{endTime},'%y%m%d')
        </if>
        <if test="scoreStart != null">
            and gc.score >= #{scoreStart}
        </if>
        <if test="scoreEnd != null">
            and gc.score &lt;= #{scoreEnd}
        </if>
        ORDER BY gc.create_time desc

    </select>

    <select id="selectGoodsCommentList" resultMap="GoodsCommentResult"
            parameterType="com.younike.system.domain.GoodsComment">

        <include refid="selectGoodsCommentVo"/>
        <where>
            <if test="memberId != null ">and member_id = #{memberId}</if>
            <if test="serverId != null ">and server_id = #{serverId}</if>
            <if test="orderId != null ">and order_id = #{orderId}</if>
            <if test="merchantId != null ">and merchant_id = #{merchantId}</if>
            <if test="goodsId != null and goodsId != ''">and goods_id = #{goodsId}</if>
            <if test="content != null and content != ''">and content = #{content}</if>
            <if test="score != null ">and score = #{score}</if>
            <if test="reply != null and reply != ''">and reply = #{reply}</if>
            <if test="replyTime != null ">and reply_time = #{replyTime}</if>
            <if test="reContent != null and reContent != ''">and re_content = #{reContent}</if>
            <if test="reContentTime != null ">and re_content_time = #{reContentTime}</if>
            <if test="isAnonymous != null ">and is_anonymous = #{isAnonymous}</if>
            <if test="column1 != null and column1 != ''">and column1 = #{column1}</if>
            <if test="column2 != null and column2 != ''">and column2 = #{column2}</if>
            <if test="column3 != null and column3 != ''">and column3 = #{column3}</if>
        </where>

    </select>


    <select id="selectGoodsCommentByCommontId" resultMap="GoodsCommentResult" parameterType="Long">

        <include refid="selectGoodsCommentVo"/>
        where commont_id = #{commontId}
    </select>


    <insert id="insertGoodsComment" parameterType="com.younike.system.domain.GoodsComment" useGeneratedKeys="true" keyProperty="commontId">
        insert into goods_comment
        <trim suffixOverrides="," suffix=")" prefix="(">
            <if test="commontId != null">commont_id,</if>
            <if test="memberId != null">member_id,</if>
            <if test="serverId != null">server_id,</if>
            <if test="orderId != null">order_id,</if>
            <if test="merchantId != null">merchant_id,</if>
            <if test="goodsId != null">goods_id,</if>
            <if test="content != null">content,</if>
            <if test="score != null">score,</if>
            <if test="reply != null">reply,</if>
            <if test="replyTime != null">reply_time,</if>
            <if test="reContent != null">re_content,</if>
            <if test="reContentTime != null">re_content_time,</if>
            <if test="isAnonymous != null">is_anonymous,</if>
            <if test="delFlag != null and delFlag != ''">del_flag,</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="column1 != null">column1,</if>
            <if test="column2 != null">column2,</if>
            <if test="column3 != null">column3,</if>
        </trim>
        <trim suffixOverrides="," suffix=")" prefix="values (">
            <if test="commontId != null">#{commontId},</if>
            <if test="memberId != null">#{memberId},</if>
            <if test="serverId != null">#{serverId},</if>
            <if test="orderId != null">#{orderId},</if>
            <if test="merchantId != null">#{merchantId},</if>
            <if test="goodsId != null">#{goodsId},</if>
            <if test="content != null">#{content},</if>
            <if test="score != null">#{score},</if>
            <if test="reply != null">#{reply},</if>
            <if test="replyTime != null">#{replyTime},</if>
            <if test="reContent != null">#{reContent},</if>
            <if test="reContentTime != null">#{reContentTime},</if>
            <if test="isAnonymous != null">#{isAnonymous},</if>
            <if test="delFlag != null and delFlag != ''">#{delFlag},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="column1 != null">#{column1},</if>
            <if test="column2 != null">#{column2},</if>
            <if test="column3 != null">#{column3},</if>
        </trim>
    </insert>

    <update id="updateGoodsComment" parameterType="com.younike.system.domain.GoodsComment">
        update goods_comment
        <trim suffixOverrides="," prefix="SET">
            <if test="memberId != null">member_id = #{memberId},</if>
            <if test="serverId != null">server_id = #{serverId},</if>
            <if test="orderId != null">order_id = #{orderId},</if>
            <if test="merchantId != null">merchant_id = #{merchantId},</if>
            <if test="goodsId != null">goods_id = #{goodsId},</if>
            <if test="content != null">content = #{content},</if>
            <if test="score != null">score = #{score},</if>
            <if test="reply != null">reply = #{reply},</if>
            <if test="replyTime != null">reply_time = #{replyTime},</if>
            <if test="reContent != null">re_content = #{reContent},</if>
            <if test="reContentTime != null">re_content_time = #{reContentTime},</if>
            <if test="isAnonymous != null">is_anonymous = #{isAnonymous},</if>
            <if test="delFlag != null and delFlag != ''">del_flag = #{delFlag},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="column1 != null">column1 = #{column1},</if>
            <if test="column2 != null">column2 = #{column2},</if>
            <if test="column3 != null">column3 = #{column3},</if>
        </trim>
        where commont_id = #{commontId}
    </update>

    <delete id="deleteGoodsCommentByCommontId"
            parameterType="Long">delete from goods_comment where commont_id = #{commontId} </delete>

    <delete id="deleteGoodsCommentByCommontIds" parameterType="String">
        delete from goods_comment where commont_id in
        <foreach close=")" separator="," open="(" collection="array" item="commontId">#{commontId}</foreach>

    </delete>
    <!--    获取我的评价列表-->
    <select id="getCommentlist" resultMap="GoodsCommentVoResult" parameterType="Long">
        select a.* ,
        b.avatar,
        b.phone,
        f.order_index
        from goods_comment a
        LEFT JOIN member b on a.member_id=b.member_id and b.del_flag=0 and b.status=0 and b.del_flag=0
        LEFT JOIN `order` f on f.order_id=a.order_id and f.del_flag=0
          where a.del_flag=0
          and a.member_id=#{memberId}
	    ORDER BY a.create_time

    </select>
    <!--    删除追评-->
    <update id="traceRemove" parameterType="com.younike.system.domain.GoodsComment">
    update goods_comment
     set re_content =null,
        re_content_time=null,
         update_time =#{updateTime}
     where commont_id = #{commontId}
    </update>
    <!--    根据商品id查询追评列表-->
    <select id="getCommentVolist" resultMap="GoodsCommentVoResult" resultType="Long">
         SELECT a.*, b.avatar, b.phone, f.order_index
            FROM goods_comment a
                LEFT JOIN member b
                ON a.member_id = b.member_id
                    AND b.del_flag = 0
                    AND b.status = 0
                    AND b.del_flag = 0
                LEFT JOIN `order` f
                ON f.order_id = a.order_id
                    AND f.del_flag = 0
            WHERE a.del_flag = 0
                AND a.goods_id = #{goodsId}
            ORDER BY a.create_time
    </select>

    <select id="gradeCommentId"
            resultMap="com.younike.system.mapper.GoodsCommentDimensionMapper.GoodsCommentDimensionVoResult">
        select d.*, e.name
        from  goods_comment_dimension d
        LEFT JOIN dimension e on e.dimension_id=d.dimension_id and e.del_flag=0
        where d.comment_id =#{commont_id}
        and d.is_del=0
    </select>

    <select id="resourceCommentId"
            resultMap="com.younike.system.mapper.GoodsCommentResourceMapper.GoodsCommentResourceResult">
        select * from   goods_comment_resource  where comment_id=#{comment_id}
    </select>
</mapper>
