<?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.buy507.mall.dao.OmsOrderDao">
    <resultMap id="orderDetailResultMap" type="com.buy507.mall.dto.OmsOrderDetail" extends="com.buy507.mall.mapper.OmsOrderMapper.BaseResultMap">
    	<result column="nickname" jdbcType="VARCHAR" property="nickname" />
        <collection property="orderItemList" resultMap="com.buy507.mall.mapper.OmsOrderItemMapper.BaseResultMap" columnPrefix="item_"/>
        <collection property="historyList" resultMap="com.buy507.mall.mapper.OmsOrderOperateHistoryMapper.BaseResultMap" columnPrefix="history_"/>
    </resultMap>
    
    <resultMap id="orderResultMap" type="com.buy507.mall.dto.OmsOrderDetail" extends="com.buy507.mall.mapper.OmsOrderMapper.BaseResultMap">
       <result column="nickname" jdbcType="VARCHAR" property="nickname" />
    </resultMap>
    
    <select id="getList" resultMap="orderResultMap">
        SELECT *, (select nickname from ums_member a where a.id = oms_order.member_id) as nickname
        FROM
        oms_order
        WHERE
        delete_status = 0
        <if test="queryParam.orderSn!=null and queryParam.orderSn!=''">
            AND order_sn = #{queryParam.orderSn}
        </if>
        <if test="queryParam.status!=null">
            AND `status` = #{queryParam.status}
        </if>
        <if test="queryParam.sourceType!=null">
            AND source_type = #{queryParam.sourceType}
        </if>
        <if test="queryParam.orderType!=null">
            AND order_type = #{queryParam.orderType}
        </if>
        <if test="queryParam.createTime!=null and queryParam.createTime!=''">
            AND create_time LIKE concat(#{queryParam.createTime},"%")
        </if>
        <if test="queryParam.receiverKeyword!=null and queryParam.receiverKeyword!=''">
            AND (
            receiver_name LIKE concat("%",#{queryParam.receiverKeyword},"%")
            OR receiver_phone LIKE concat("%",#{queryParam.receiverKeyword},"%")
            )
        </if>
        ORDER BY modify_time DESC
    </select>
    <update id="delivery">
        UPDATE oms_order
        SET
        delivery_sn = CASE id
        <foreach collection="list" item="item">
            WHEN #{item.orderId} THEN #{item.deliverySn}
        </foreach>
        END,
        delivery_company = CASE id
        <foreach collection="list" item="item">
            WHEN #{item.orderId} THEN #{item.deliveryCompany}
        </foreach>
        END,
        delivery_time = CASE id
        <foreach collection="list" item="item">
            WHEN #{item.orderId} THEN now()
        </foreach>
        END,
        `status` = CASE id
        <foreach collection="list" item="item">
            WHEN #{item.orderId} THEN 2
        </foreach>
        END
        WHERE
        id IN
        <foreach collection="list" item="item" separator="," open="(" close=")">
            #{item.orderId}
        </foreach>
        AND `status` = 1
    </update>
    
    <select id="getDetail" resultMap="orderDetailResultMap">
        SELECT o.*,
            oi.id item_id,
            oi.product_id item_product_id,
            oi.product_sn item_product_sn,
            oi.product_pic item_product_pic,
            oi.product_name item_product_name,
            oi.product_brand item_product_brand,
            oi.product_price item_product_price,
            oi.product_quantity item_product_quantity,
            oi.product_attr item_product_attr,
            oi.sp1 item_sp1,
            oi.sp2 item_sp2,
            oi.sp3 item_sp3,
            oh.id history_id,
            oh.operate_man history_operate_man,
            oh.create_time history_create_time,
            oh.order_status history_order_status,
            oh.note history_note,
            (select nickname from ums_member a where a.id = o.member_id) as nickname
        FROM
            oms_order o
            LEFT JOIN oms_order_item oi ON o.id = oi.order_id
            LEFT JOIN oms_order_operate_history oh ON o.id = oh.order_id
        WHERE
            o.id = #{id}
        ORDER BY oi.id ASC,oh.create_time DESC
    </select>
    
    <select id="getTotalAmountByMonth" parameterType="java.lang.Integer" resultType="java.math.BigDecimal">
        select IFNULL(sum(o.total_amount), 0) as totalAmount 
          from oms_order o 
         where month(o.create_time) = #{month}
           and o.status in (1, 2, 3)
    </select>
    
</mapper>