<?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.ruoyi.mall.mapper.MallOrderGoodsMapper">

    <resultMap type="com.ruoyi.mall.domain.MallOrderGoods" id="MallOrderGoodsResult">
        <result property="id"    column="id"    />
        <result property="orderId"    column="order_id"    />
        <result property="goodsId"    column="goods_id"    />
        <result property="orderNo"    column="order_no"    />
        <result property="productId"    column="product_id"    />
        <result property="goodsName"    column="goods_name"    />
        <result property="goodsImg"    column="goods_img"    />
        <result property="productName"    column="product_name"    />
        <result property="price"    column="price"    />
        <result property="total"    column="total"    />
        <result property="comment"    column="comment"    />
        <result property="delFlag"    column="del_flag"    />
        <result property="commission"    column="commission"    />
        <result property="returnStatus"    column="return_status"    />
        <result property="returnTotal"    column="return_total"    />
        <result property="integral"    column="integral"    />
        <result property="returnableQuantity"    column="returnable_quantity"    />
    </resultMap>

    <resultMap type="com.ruoyi.mall.domain.vo.MallOrderGoodsVO" id="MallOrderGoodsVOResult">
        <result property="id"    column="id"    />
        <result property="orderId"    column="order_id"    />
        <result property="goodsId"    column="goods_id"    />
        <result property="productId"    column="product_id"    />
        <result property="goodsName"    column="goods_name"    />
        <result property="goodsImg"    column="goods_img"    />
        <result property="productName"    column="product_name"    />
        <result property="price"    column="price"    />
        <result property="total"    column="total"    />
        <result property="comment"    column="comment"    />
        <result property="commission"    column="commission"    />
        <result property="returnStatus"    column="return_status"    />
        <result property="returnTotal"    column="return_total"    />
        <result property="returnableQuantity"    column="returnable_quantity"    />
        <result property="integral"    column="integral"    />
    </resultMap>

    <resultMap type="com.ruoyi.mall.domain.vo.MallOrderGoodsExportVO" id="MallOrderGoodsExportVOResult">
        <result property="createAt"    column="createAt"    />
        <result property="orderNo"    column="orderNo"    />
        <result property="goodsName"    column="goodsName"    />
        <result property="spec"    column="spec"    />
        <result property="productName"    column="productName"    />
        <result property="total"    column="total"    />
        <result property="price"    column="price"    />
        <result property="totalPrice"    column="totalPrice"    />
        <result property="userName"    column="userName"    />
        <result property="salesmanName"    column="salesmanName"    />
        <result property="orderType"    column="orderType"    />
        <result property="deliveryType"    column="deliveryType"    />
        <result property="receiverName"    column="receiverName"    />
        <result property="receiverPhone"    column="receiverPhone"    />
        <result property="receiverProvince"    column="receiverProvince"    />
        <result property="receiverCity"    column="receiverCity"    />
        <result property="receiverArea"    column="receiverArea"    />
        <result property="receiverAddress"    column="receiverAddress"    />
        <result property="commission"    column="commission"    />
        <result property="integral"    column="integral"    />
    </resultMap>

    <sql id="selectMallOrderGoodsVo">
        select id, order_id, goods_id, product_id, goods_name, goods_img, product_name, price, total, comment, del_flag, commission, return_total,returnable_quantity, return_status,integral from mall_order_goods
    </sql>

    <select id="selectMallOrderGoodsList" parameterType="MallOrderGoods" resultMap="MallOrderGoodsResult">
        select mog.id, mog.order_id, mog.goods_id, moo.order_no,mog.product_id, mog.goods_name, mog.goods_img, mog.product_name, mog.price, mog.total, mog.comment, mog.del_flag
             , mog.commission, mog.return_total, mog.return_status,mog.integral,mog.returnable_quantity
        from mall_order_goods mog
        left join mall_order_order moo on mog.order_id = moo.id
        <where>
            <if test="goodsName != null  and goodsName != ''"> and mog.goods_name like concat('%', #{goodsName}, '%')</if>
            <if test="orderNo != null  and orderNo != ''"> and moo.order_no like concat('%', #{orderNo}, '%')</if>
            <if test="comment != null  and comment != ''"> and mog.comment = #{comment}</if>
            <if test="orderId != null  and orderId != ''"> and mog.order_id = #{orderId}</if>
            <if test="productId != null  and productId != ''"> and mog.product_id = #{productId}</if>
            <if test="goodsId != null  and goodsId != ''"> and mog.goods_id = #{goodsId}</if>
            <if test="returnStatus != null  and returnStatus != ''"> and mog.return_status = #{returnStatus}</if>
        </where>
    </select>

    <select id="selectMallOrderGoodsTotal" parameterType="MallOrderGoods" resultType="MallOrderGoods">
        SELECT
            oo.user_id userId,
            any_value (mu.`name`) memberUserName,
            og.goods_id goodsId,
            any_value (gg.`goods_name`) goodsName,
            any_value (gg.`goods_img`) goodsImg,
            any_value (og.`product_name`) productName,
            any_value (og.`price`) price,
            SUM(og.total) total,
            any_value (oo.`create_at`) createAt
        FROM mall_order_order AS oo
            LEFT JOIN mall_order_goods AS og
                ON oo.id = og.order_id
            LEFT JOIN mall_member_user mu
                ON oo.`user_id` = mu.`id`
            LEFT JOIN mall_goods_goods gg
                ON gg.`id` = og.`goods_id`
        <where>
            <if test="memberUserName != null  and memberUserName != ''"> and mu.`name` like concat('%', #{memberUserName}, '%')</if>
            <if test="goodsName != null  and goodsName != ''"> and gg.`goods_name` like concat('%', #{goodsName}, '%')</if>
            <if test="params.createTimeBegin != null  and params.createTimeEnd != ''">
                and date_format(oo.`create_at`,'%Y-%m-%d') &lt;= #{params.createTimeEnd}
                and date_format(oo.`create_at`,'%Y-%m-%d') &gt;= #{params.createTimeBegin}
            </if>
            and oo.del_flag = 0
            and oo.status in ('2','3')
        </where>
        GROUP BY og.goods_id, oo.user_id
        ORDER BY total DESC
    </select>

    <select id="selectMallOrderGoodsById" parameterType="Long" resultMap="MallOrderGoodsResult">
        <include refid="selectMallOrderGoodsVo"/>
        where id = #{id}
    </select>
    <select id="selectMallOrderGoodsByOrderId" parameterType="Long" resultMap="MallOrderGoodsVOResult">
        select id, order_id, goods_id, product_id, goods_name, goods_img, product_name, price, total, comment, commission, return_total, return_status,integral,returnable_quantity from mall_order_goods
        where order_id = #{id}
    </select>

    <insert id="insertMallOrderGoods" parameterType="MallOrderGoods" useGeneratedKeys="true" keyProperty="id">
        insert into mall_order_goods
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="orderId != null">order_id,</if>
            <if test="goodsId != null">goods_id,</if>
            <if test="productId != null">product_id,</if>
            <if test="goodsName != null">goods_name,</if>
            <if test="goodsImg != null">goods_img,</if>
            <if test="productName != null">product_name,</if>
            <if test="price != null">price,</if>
            <if test="total != null">total,</if>
            <if test="comment != null">comment,</if>
            <if test="delFlag != null">del_flag,</if>
            <if test="commission != null">commission,</if>
            <if test="returnTotal != null">return_total,</if>
            <if test="returnStatus != null">return_status,</if>
            <if test="integral != null">integral,</if>
            <if test="returnableQuantity != null">returnable_quantity,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="orderId != null">#{orderId},</if>
            <if test="goodsId != null">#{goodsId},</if>
            <if test="productId != null">#{productId},</if>
            <if test="goodsName != null">#{goodsName},</if>
            <if test="goodsImg != null">#{goodsImg},</if>
            <if test="productName != null">#{productName},</if>
            <if test="price != null">#{price},</if>
            <if test="total != null">#{total},</if>
            <if test="comment != null">#{comment},</if>
            <if test="delFlag != null">#{delFlag},</if>
            <if test="commission != null">#{commission},</if>
            <if test="returnTotal != null">#{returnTotal},</if>
            <if test="returnStatus != null">#{returnStatus},</if>
            <if test="integral != null">#{integral},</if>
            <if test="returnableQuantity != null">#{returnableQuantity},</if>
         </trim>
    </insert>

    <update id="updateMallOrderGoods" parameterType="MallOrderGoods">
        update mall_order_goods
        <trim prefix="SET" suffixOverrides=",">
            <if test="orderId != null">order_id = #{orderId},</if>
            <if test="goodsId != null">goods_id = #{goodsId},</if>
            <if test="productId != null">product_id = #{productId},</if>
            <if test="goodsName != null">goods_name = #{goodsName},</if>
            <if test="goodsImg != null">goods_img = #{goodsImg},</if>
            <if test="productName != null">product_name = #{productName},</if>
            <if test="price != null">price = #{price},</if>
            <if test="total != null">total = #{total},</if>
            <if test="comment != null">comment = #{comment},</if>
            <if test="delFlag != null">del_flag = #{delFlag},</if>
            <if test="commission != null">commission = #{commission},</if>
            <if test="returnTotal != null">return_total = #{returnTotal},</if>
            <if test="returnStatus != null">return_status = #{returnStatus},</if>
            <if test="integral != null">integral = #{integral},</if>
            <if test="returnableQuantity != null">returnable_quantity = #{returnableQuantity},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteMallOrderGoodsById" parameterType="Long">
        delete from mall_order_goods where id = #{id}
    </delete>

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

    <select id="selectOrderGoodsByOrderId" parameterType="Long" resultMap="MallOrderGoodsResult">
        <include refid="selectMallOrderGoodsVo"/>
        where order_id = #{orderId}
    </select>

    <select id="selectOrderGoodsCommissionByOrderId" resultType="java.math.BigDecimal">
        select ifnull(sum(commission * total), 0)
        from mall_order_goods
        where order_id = #{orderId}
    </select>

    <select id="selectMallBackOrderGoodsList" parameterType="MallOrderGoods" resultMap="MallOrderGoodsResult">
        select mog.id, mog.order_id, mog.goods_id, moo.order_no,mog.product_id, mog.goods_name, mog.goods_img, mog.product_name, mog.price, mog.total, mog.comment, mog.del_flag
        , mog.commission, mog.return_total, mog.return_status,mog.integral
        from mall_order_goods mog
        left join mall_order_order moo on mog.order_id = moo.id
        where
            mog.order_id = #{orderId}
          and mog.return_status != '0'
    </select>

    <select id="getOrderBackList" parameterType="MallOrderGoods" resultMap="MallOrderGoodsResult">
        select mog.id, mog.order_id, mog.goods_id, moo.order_no,mog.product_id, mog.goods_name, mog.goods_img, mog.product_name, mog.price, mog.total, mog.comment, mog.del_flag
        , mog.commission, mog.return_total, mog.return_status,mog.integral
        from mall_order_goods mog
        left join mall_order_order moo on mog.order_id = moo.id
        <where>
            <if test="goodsName != null  and goodsName != ''"> and mog.goods_name like concat('%', #{goodsName}, '%')</if>
            <if test="orderNo != null  and orderNo != ''"> and moo.order_no like concat('%', #{orderNo}, '%')</if>
            <if test="comment != null  and comment != ''"> and mog.comment = #{comment}</if>
            <if test="orderId != null  and orderId != ''"> and mog.order_id = #{orderId}</if>
            <if test="productId != null  and productId != ''"> and mog.product_id = #{productId}</if>
            <if test="goodsId != null  and goodsId != ''"> and mog.goods_id = #{goodsId}</if>
            and mog.return_status != '0'
        </where>
    </select>

    <select id="selectMallOrderGoodsListByOrderIds" resultMap="MallOrderGoodsExportVOResult">
        select moo.create_at createAt, moo.order_no orderNo, moo.order_type orderType, moo.delivery_type deliveryType,
        moo.receiver_name receiverName, moo.receiver_phone receiverPhone, moo.receiver_province receiverProvince,
        moo.receiver_city receiverCity, moo.receiver_area receiverArea, moo.receiver_address receiverAddress,
        mog.goods_name goodsName, mog.goods_name spec, mog.product_name productName, mog.total, mog.price,
        ifnull((mog.total * mog.price), 0) totalPrice,
        mmu.name userName,mog.integral,
        ms.name salesmanName
        , mss.shop_name shopName
        from mall_order_goods mog
        left join mall_order_order moo on mog.order_id = moo.id
        left join mall_member_user mmu on moo.user_id = mmu.id
        left join mall_salesman ms on moo.salesman_id = ms.id
        left join mall_shop_shop mss on mss.shop_id = moo.shop_id
        where order_id in
        <foreach item="orderId" collection="array" open="(" separator="," close=")">
            #{orderId}
        </foreach>
    </select>

    <select id="exportOrder" parameterType="MallOrderOrder" resultMap="MallOrderGoodsExportVOResult">
        select moo.create_at createAt, moo.order_no orderNo, moo.order_type orderType, moo.delivery_type deliveryType,
        moo.receiver_name receiverName, moo.receiver_phone receiverPhone, moo.receiver_province receiverProvince, moo.receiver_city receiverCity, moo.receiver_area receiverArea, moo.receiver_address receiverAddress,
        mog.goods_name goodsName, mog.goods_name spec, mog.product_name productName, mog.total, mog.price, ifnull((mog.total * mog.price), 0) totalPrice,
        mmu.name userName,mog.integral,
        mog.commission,
        ms.name salesmanName, mss.shop_name shopName
        from mall_order_goods mog
        left join mall_order_order moo on mog.order_id = moo.id
        left join mall_member_user mmu on moo.user_id = mmu.id
        left join mall_salesman ms on moo.salesman_id = ms.id
        left join mall_shop_shop mss on mss.shop_id = moo.shop_id
        <where>
            <if test="orderNo != null  and orderNo != ''"> and moo.order_no like concat('%', #{orderNo}, '%')</if>
            <if test="status != null  and status != ''"> and moo.status = #{status}</if>
            <if test="userId != null  and userId != ''"> and moo.user_id = #{userId}</if>
            <if test="createAt != null  and createAt != ''"> and moo.create_at = #{createAt}</if>
            <if test="salesmanId != null"> and moo.salesman_id = #{salesmanId}</if>
            <if test="deliveryType != null"> and moo.delivery_type = #{deliveryType}</if>
            <if test="memberUserId != null"> and mmu.id = #{memberUserId}</if>
            <if test="params.beginCreateAt != null and params.beginCreateAt != '' and params.endCreateAt != null and params.endCreateAt != ''">
                and #{params.beginCreateAt} &lt;= DATE_FORMAT(moo.`create_at`,'%Y-%m-%d')
                and #{params.endCreateAt} &gt;= DATE_FORMAT(moo.`create_at`,'%Y-%m-%d')
            </if>
            <if test="orderType != null"> and moo.order_type = #{orderType}</if>
            <if test="payType != null"> and moo.pay_type = #{payType}</if>
            <if test="shopId != null"> and moo.shop_id = #{shopId}</if>
        </where>
        order by moo.create_at desc
    </select>

    <select id="selectMallOrderGoodsListByOrderId" resultType="map">
        select mog.id, mog.order_id orderId, moo.order_no orderNo,
               CONVERT(IFNULL(mog.goods_name,''),CHAR) goodsName,
               CONVERT(IFNULL(mgg.goods_content,''),CHAR) goodsProduct,
                CONVERT(IFNULL(mog.product_name,''),CHAR) goodsUnit,
               mog.price goodsPrice,
               mog.total goodsTotal,
               mog.return_status returnStatus,
               mog.return_total returnTotal,
               ifnull(mog.price * mog.total, 0) totalPrice,
               ifnull(mog.commission * mog.total, 0) commissionPrice
        from mall_order_goods mog
                 left join mall_goods_goods mgg on mog.goods_id = mgg.id
                 left join mall_order_order moo on mog.order_id = moo.id
        where mog.order_id = #{orderId}
        and mog.del_flag = '0'
    </select>

    <select id="orderGoodsTotal" resultType="java.lang.String">
        select ifnull(sum(total), 0) totalNum
        from mall_order_goods
        where order_id = #{orderId}
    </select>
    <select id="selectMallOrderGoodsByOrderIdAndGoodsId"
            resultMap="MallOrderGoodsVOResult">
        <include refid="selectMallOrderGoodsVo"/>
        where order_id = #{orderId}
          and goods_id = #{goodsId}
    </select>

    <update id="updateMallOrderGoodsReturnStatus">
        update mall_order_goods set
        return_status = #{returnStatus},
         return_total = '0' ,
        returnable_quantity = total
        where order_id = #{orderId}
        and return_status != '0'

    </update>

    <update id="updateMallOrderGoodsByOrderIdAndGoodsId">
        update mall_order_goods set
        return_status = #{returnStatus},
         return_total = #{returnTotal} ,
        returnable_quantity = #{returnableQuantity}
        where order_id = #{orderId}
         and   goods_id = #{goodsId}
        and return_status != '0'

    </update>

    <update id="updateMallOrderGoodsReturns">
        update mall_order_goods set
            return_status = #{returnStatus}
                and return_total = '0'
        where order_id = #{orderId}
          and (return_status != '0' or return_status != '2' )

    </update>
</mapper>
