<?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.yiqi.core.mapper.GoodsCommentMapper">

    <resultMap type="GoodsComment" id="GoodsCommentResult">
        <result property="id" column="id"/>
        <result property="userId" column="user_id"/>
        <result property="orderId" column="order_id"/>
        <result property="goodsId" column="goods_id"/>
        <result property="commentScore" column="comment_score"/>
        <result property="commentType" column="comment_type"/>
        <result property="commentContent" column="comment_content"/>
        <result property="commentImgs" column="comment_imgs"/>
        <result property="commentSrcType" column="comment_src_type"/>
        <result property="delFlag" column="del_flag"/>
        <result property="createBy" column="create_by"/>
        <result property="createById" column="create_by_id"/>
        <result property="createTime" column="create_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateById" column="update_by_id"/>
        <result property="updateTime" column="update_time"/>
        <result property="remark" column="remark"/>
        <result property="isShow" column="is_show"/>
        <result property="isVirtual" column="is_virtual"/>
    </resultMap>

    <sql id="selectGoodsCommentVo">
        select id, user_id, order_id, goods_id, comment_score, comment_type, comment_content, comment_imgs, comment_src_type, del_flag, create_by, create_by_id, create_time, update_by, update_by_id, update_time, remark, is_show,is_virtual
        from goods_comment
    </sql>

    <select id="selectGoodsCommentList"  resultType="com.yiqi.core.domain.vo.GoodsCommentQueryVO">
        select a.id as id , a.user_id as userId, a.order_id as orderId, a.goods_id as goodsId, a.comment_score as commentScore,
               a.comment_type as commentType, a.comment_content as commentContent, a.comment_imgs as commentImgs,
                a.comment_src_type as commentSrcType, a.create_by as createBy, a.create_by_id as createById, a.create_time as createTime, a.remark, a.is_show as isShow, a.is_virtual as isVirtual,
        b.phone_number as phoneNumber, b.nick_name as nickName, b.real_name as realName,
         c.province , c.city, c.area, c.address, c.address_detail as addressDetail, c.contact_name as contactName, c.contact_phone as contactPhone
        from goods_comment a
            left join app_user b on a.user_id = b.id
            left join  app_user_address c on b.id = c.app_user_id
        <where>
            and a.del_flag='0' and a.goods_type = #{goodsType}
            <if test="orderId != null ">
                and a.order_id = #{orderId}
            </if>
            <if test="goodsId != null ">
                and a.goods_id = #{goodsId}
            </if>
            <if test="commentType != null  and commentType != ''">
                and a.comment_type = #{commentType}
            </if>
            <if test="isShow != null  and isShow != ''">
                and a.is_show = #{isShow}
            </if>
            <if test="isVirtual != null  and isVirtual != ''">
                and a.is_virtual = #{isVirtual}
            </if>
            <if test="phoneNumber != null  and phoneNumber != ''">
                and b.phone_number = #{phoneNumber}
            </if>
            <if test="createTimeFrom != null and createTimeFrom !=''">
                <![CDATA[and a.create_time >= concat(#{createTimeFrom}, ' 00:00:00')]]>
            </if>
            <if test="createTimeTo != null and createTimeTo !=''">
                <![CDATA[and a.create_time <= concat(#{createTimeTo}, ' 23:59:59')]]>
            </if>
        </where>
        order by a.create_time desc
        limit #{startRow},#{pageSize}
    </select>

    <select id="selectGoodsCommentById" parameterType="Long"
            resultMap="GoodsCommentResult">
        <include refid="selectGoodsCommentVo"/>
        where id = #{id}
        and del_flag='0'
    </select>
    <select id="countNumberByGoodsCommentList" resultType="java.lang.Integer">
        select count(a.id)
        from goods_comment a
        left join app_user b on a.user_id = b.id
        left join  app_user_address c on b.id = c.app_user_id
        <where>
            and a.del_flag='0' and a.goods_type = #{goodsType}
            <if test="orderId != null ">
                and a.order_id = #{orderId}
            </if>
            <if test="goodsId != null ">
                and a.goods_id = #{goodsId}
            </if>
            <if test="commentType != null  and commentType != ''">
                and a.comment_type = #{commentType}
            </if>
            <if test="isShow != null  and isShow != ''">
                and a.is_show = #{isShow}
            </if>
            <if test="isVirtual != null  and isVirtual != ''">
                and a.is_virtual = #{isVirtual}
            </if>
            <if test="phoneNumber != null  and phoneNumber != ''">
                and b.phone_number = #{phoneNumber}
            </if>
            <if test="createTimeFrom != null and createTimeFrom !=''">
                <![CDATA[and a.create_time >= concat(#{createTimeFrom}, ' 00:00:00')]]>
            </if>
            <if test="createTimeTo != null and createTimeTo !=''">
                <![CDATA[and a.create_time <= concat(#{createTimeTo}, ' 23:59:59')]]>
            </if>
        </where>
    </select>


</mapper>