<?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.frequency.mallorder.mapper.MallOrderMapper">

    <resultMap type="com.ruoyi.frequency.mallorder.entity.MallOrder" id="MallOrderResult">
        <result property="id"    column="id"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="delFlag"    column="del_flag"    />
        <result property="userType"    column="user_type"    />
        <result property="userId"    column="user_id"    />
        <result property="goodsPrice"    column="goods_price"    />
        <result property="payPrice"    column="pay_price"    />
        <result property="orderNo"    column="order_no"    />
        <result property="payOrderNo"    column="pay_order_no"    />
        <result property="payStatus"    column="pay_status"    />
        <result property="orderStatus"    column="order_status"    />
        <result property="payTime"    column="pay_time"    />
        <result property="cancelTime"    column="cancel_time"    />
        <result property="sendTime"    column="send_time"    />
        <result property="receiveTime"    column="receive_time"    />
        <result property="completeTime"    column="complete_time"    />
        <result property="sendComCourierId"    column="send_com_courier_id"    />
        <result property="sendComLogistics"    column="send_com_logistics"    />
        <result property="sendComLogisticsCode"    column="send_com_logistics_code"    />
        <result property="remark"    column="remark"    />
        <result property="payType"    column="pay_type"    />
        <result property="aliReturnPayId"    column="ali_return_pay_id"    />
        <result property="arrivalTime"    column="arrival_time"    />
        <result property="afterSalesDays"    column="after_sales_days"    />
        <result property="customerShow"    column="customer_show"    />
        <result property="orderGoodsId"    column="order_goods_id"    />
        <result property="givePrice"    column="give_price"    />
        <result property="integralPrice"    column="integral_price"    />
    </resultMap>


    <sql id="selectMallOrderVo">
        select
            t.id ,
            t.create_by ,
            t.create_time ,
            t.update_by ,
            t.update_time ,
            t.del_flag ,
            t.user_type ,
            t.user_id ,
            t.goods_price ,
            t.pay_price ,
            t.order_no ,
            t.pay_order_no ,
            t.pay_status ,
            t.order_status ,
            t.pay_time ,
            t.cancel_time ,
            t.send_time ,
            t.receive_time ,
            t.complete_time ,
            t.send_com_courier_id ,
            t.send_com_logistics ,
            t.send_com_logistics_code ,
            t.remark ,
            if(t.pay_price = 0,null,t.pay_type) pay_type,
            t.ali_return_pay_id ,
            t.arrival_time ,
            t.after_sales_days ,
            t.customer_show ,
            t.order_goods_id ,
            t.give_price ,
            t.integral_price
        from mall_order AS t
        LEFT JOIN t_customer t2 on t.user_id = t2.id
    </sql>

    <select id="selectMallOrderList" parameterType="com.ruoyi.frequency.mallorder.entity.MallOrder" resultMap="MallOrderResult">
        <include refid="selectMallOrderVo"/>
        <where> AND t.del_flag != 2
            <if test="userType != null "> and t.user_type = #{userType}</if>
            <if test="userId != null "> and t.user_id = #{userId}</if>
            <if test="goodsPrice != null "> and t.goods_price = #{goodsPrice}</if>
            <if test="payPrice != null "> and t.pay_price = #{payPrice}</if>
            <if test="orderNo != null  and orderNo != ''"> and t.order_no like concat('%',#{orderNo},'%')</if>
            <if test="payOrderNo != null  and payOrderNo != ''"> and t.pay_order_no = #{payOrderNo}</if>
            <if test="payStatus != null "> and t.pay_status = #{payStatus}</if>
            <if test="orderStatus != null "> and t.order_status = #{orderStatus}</if>
            <if test="payTime != null "> and t.pay_time = #{payTime}</if>
            <if test="cancelTime != null "> and t.cancel_time = #{cancelTime}</if>
            <if test="sendTime != null "> and t.send_time = #{sendTime}</if>
            <if test="receiveTime != null "> and t.receive_time = #{receiveTime}</if>
            <if test="completeTime != null "> and t.complete_time = #{completeTime}</if>
            <if test="sendComCourierId != null  and sendComCourierId != ''"> and t.send_com_courier_id = #{sendComCourierId}</if>
            <if test="sendComLogistics != null  and sendComLogistics != ''"> and t.send_com_logistics = #{sendComLogistics}</if>
            <if test="sendComLogisticsCode != null  and sendComLogisticsCode != ''"> and t.send_com_logistics_code = #{sendComLogisticsCode}</if>
            <if test="payType != null "> and t.pay_type = #{payType}</if>
            <if test="aliReturnPayId != null  and aliReturnPayId != ''"> and t.ali_return_pay_id = #{aliReturnPayId}</if>
            <if test="arrivalTime != null "> and t.arrival_time = #{arrivalTime}</if>
            <if test="afterSalesDays != null "> and t.after_sales_days = #{afterSalesDays}</if>
            <if test="customerShow != null "> and t.customer_show = #{customerShow}</if>
            <if test="orderGoodsId != null "> and t.order_goods_id = #{orderGoodsId}</if>
            <if test="givePrice != null "> and t.give_price = #{givePrice}</if>
            <if test="integralPrice != null "> and t.integral_price = #{integralPrice}</if>
            <if test="delFlag != null and delFlag != ''"> and t.del_flag = #{delFlag}</if>
            <if test="beginTime != null ">
                and  t.create_time >= #{beginTime}

            </if>
            <if test="endTime != null ">
                and  t.create_time &lt; #{endTime}
            </if>

            <if test="name != null and name != ''"> and t2.name LIKE CONCAT('%',#{name},'%')</if>
            <if test="nickname != null and nickname != ''"> and t2.nickname LIKE CONCAT('%',#{nickname},'%')</if>
            <if test="mobile != null and mobile != ''"> and t2.mobile LIKE CONCAT('%',#{mobile},'%')</if>
        </where>
        order by t.create_time desc
    </select>

    <select id="selectMallOrderById" parameterType="Long" resultMap="MallOrderResult">
        <include refid="selectMallOrderVo"/>
        where t.id = #{id}
    </select>

    <insert id="insertMallOrder" parameterType="com.ruoyi.frequency.mallorder.entity.MallOrder">
        insert into mall_order
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">id,</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="delFlag != null">del_flag,</if>
            <if test="userType != null">user_type,</if>
            <if test="userId != null">user_id,</if>
            <if test="goodsPrice != null">goods_price,</if>
            <if test="payPrice != null">pay_price,</if>
            <if test="orderNo != null">order_no,</if>
            <if test="payOrderNo != null">pay_order_no,</if>
            <if test="payStatus != null">pay_status,</if>
            <if test="orderStatus != null">order_status,</if>
            <if test="payTime != null">pay_time,</if>
            <if test="cancelTime != null">cancel_time,</if>
            <if test="sendTime != null">send_time,</if>
            <if test="receiveTime != null">receive_time,</if>
            <if test="completeTime != null">complete_time,</if>
            <if test="sendComCourierId != null">send_com_courier_id,</if>
            <if test="sendComLogistics != null">send_com_logistics,</if>
            <if test="sendComLogisticsCode != null">send_com_logistics_code,</if>
            <if test="remark != null">remark,</if>
            <if test="payType != null">pay_type,</if>
            <if test="aliReturnPayId != null">ali_return_pay_id,</if>
            <if test="arrivalTime != null">arrival_time,</if>
            <if test="afterSalesDays != null">after_sales_days,</if>
            <if test="customerShow != null">customer_show,</if>
            <if test="orderGoodsId != null">order_goods_id,</if>
            <if test="givePrice != null">give_price,</if>
            <if test="integralPrice != null">integral_price,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">#{id},</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="delFlag != null">#{delFlag},</if>
            <if test="userType != null">#{userType},</if>
            <if test="userId != null">#{userId},</if>
            <if test="goodsPrice != null">#{goodsPrice},</if>
            <if test="payPrice != null">#{payPrice},</if>
            <if test="orderNo != null">#{orderNo},</if>
            <if test="payOrderNo != null">#{payOrderNo},</if>
            <if test="payStatus != null">#{payStatus},</if>
            <if test="orderStatus != null">#{orderStatus},</if>
            <if test="payTime != null">#{payTime},</if>
            <if test="cancelTime != null">#{cancelTime},</if>
            <if test="sendTime != null">#{sendTime},</if>
            <if test="receiveTime != null">#{receiveTime},</if>
            <if test="completeTime != null">#{completeTime},</if>
            <if test="sendComCourierId != null">#{sendComCourierId},</if>
            <if test="sendComLogistics != null">#{sendComLogistics},</if>
            <if test="sendComLogisticsCode != null">#{sendComLogisticsCode},</if>
            <if test="remark != null">#{remark},</if>
            <if test="payType != null">#{payType},</if>
            <if test="aliReturnPayId != null">#{aliReturnPayId},</if>
            <if test="arrivalTime != null">#{arrivalTime},</if>
            <if test="afterSalesDays != null">#{afterSalesDays},</if>
            <if test="customerShow != null">#{customerShow},</if>
            <if test="orderGoodsId != null">#{orderGoodsId},</if>
            <if test="givePrice != null">#{givePrice},</if>
            <if test="integralPrice != null">#{integralPrice},</if>
        </trim>
    </insert>

    <update id="updateMallOrder" parameterType="com.ruoyi.frequency.mallorder.entity.MallOrder">
        update mall_order
        <trim prefix="SET" suffixOverrides=",">
            <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="delFlag != null">del_flag = #{delFlag},</if>
            <if test="userType != null">user_type = #{userType},</if>
            <if test="userId != null">user_id = #{userId},</if>
            <if test="goodsPrice != null">goods_price = #{goodsPrice},</if>
            <if test="payPrice != null">pay_price = #{payPrice},</if>
            <if test="orderNo != null">order_no = #{orderNo},</if>
            <if test="payOrderNo != null">pay_order_no = #{payOrderNo},</if>
            <if test="payStatus != null">pay_status = #{payStatus},</if>
            <if test="orderStatus != null">order_status = #{orderStatus},</if>
            <if test="payTime != null">pay_time = #{payTime},</if>
            <if test="cancelTime != null">cancel_time = #{cancelTime},</if>
            <if test="sendTime != null">send_time = #{sendTime},</if>
            <if test="receiveTime != null">receive_time = #{receiveTime},</if>
            <if test="completeTime != null">complete_time = #{completeTime},</if>
            <if test="sendComCourierId != null">send_com_courier_id = #{sendComCourierId},</if>
            <if test="sendComLogistics != null">send_com_logistics = #{sendComLogistics},</if>
            <if test="sendComLogisticsCode != null">send_com_logistics_code = #{sendComLogisticsCode},</if>
            <if test="remark != null">remark = #{remark},</if>
            <if test="payType != null">pay_type = #{payType},</if>
            <if test="aliReturnPayId != null">ali_return_pay_id = #{aliReturnPayId},</if>
            <if test="arrivalTime != null">arrival_time = #{arrivalTime},</if>
            <if test="afterSalesDays != null">after_sales_days = #{afterSalesDays},</if>
            <if test="customerShow != null">customer_show = #{customerShow},</if>
            <if test="orderGoodsId != null">order_goods_id = #{orderGoodsId},</if>
            <if test="givePrice != null">give_price = #{givePrice},</if>
            <if test="integralPrice != null">integral_price = #{integralPrice},</if>
        </trim>
        where id = #{id}
    </update>

    <select id="orderPage" resultType="com.ruoyi.controller.resp.MallOrderPageResp">
        SELECT
        t.id orderId,
        t.goods_price,
        t.pay_price,
        t.order_status,
        t.send_time,
        t.cancel_time,
        t.integral_price,
        if(t.pay_price = 0,null,t.pay_type) pay_type,
        t.send_com_courier_id,
        t.send_com_logistics,
        t.send_com_logistics_code,
        case when
        (SELECT
        COUNT(1)
        FROM
        mall_refund r
        WHERE
        r.del_flag = 1
        and r.order_id = t.id)> 0
        then
        2 else 1 end isRefund,
        (SELECT
        r.status
        FROM
        mall_refund r
        WHERE
        r.del_flag = 1
        and r.order_id = t.id order by r.create_time desc limit 1) refundStatus,
        t.order_no,
        t.integral_price
        FROM
        mall_order t
        WHERE
        t.user_id = #{userVo.userId}
        AND t.user_type = #{userVo.userType}
        AND t.del_flag = 1
        AND t.customer_show = 1
        <if test="req.status != null and req.status != ''">
            <if test="req.status != 7">
                AND (SELECT
                COUNT(1)
                FROM
                mall_refund r
                WHERE
                r.del_flag = 1
                and r.order_id = t.id)= 0
            </if>
            <if test="req.status == 1">
                AND t.order_status = 1
            </if>
            <if test="req.status == 2">
                AND t.order_status = 2
            </if>
            <if test="req.status == 3">
                AND t.order_status = 3
            </if>
            <if test="req.status == 4">
                AND (t.order_status = 4 or t.order_status = 5)
            </if>
            <if test="req.status == 5">
                AND t.order_status = 5
            </if>
            <if test="req.status == 6">
                AND t.order_status = 6
            </if>
            <if test="req.status == -1">
                AND t.order_status = -1
            </if>
            <if test="req.status == 7">
                AND (SELECT
                COUNT(1)
                FROM
                mall_refund r
                WHERE
                r.del_flag = 1
                and r.order_id = t.id)> 0
            </if>
        </if>
        ORDER BY
        t.create_time DESC
    </select>

    <select id="orderDetail" resultType="com.ruoyi.controller.resp.MallOrderDetailResp">
        SELECT
            t.id,
            t.create_time,
            t.user_type,
            t.user_id,
            t.goods_price,
            t.pay_price,
            t.order_no,
            t.pay_status,
            t.order_status,
            t.pay_time,
            t.complete_time,
            t.receive_time,
            t.send_time,
            t.cancel_time,
            t.integral_price,
            if(t.pay_price = 0,null,t.pay_type) pay_type,
            t.send_com_courier_id,
            t.send_com_logistics,
            t.send_com_logistics_code,
            DATE_SUB( t.receive_time, INTERVAL - t.after_sales_days DAY ) afterSalesTime,
            DATE_ADD( t.send_time, INTERVAL ( IFNULL(( SELECT d.dict_value FROM sys_dict_data d WHERE d.`code` = 'CONFIRM_TIME' ), 7 )) DAY ) voluntarilyReceiptTime,
            (SELECT
                 r.status
             FROM
                 mall_refund r
             WHERE
                 r.del_flag = 1
               and r.order_id = t.id order by r.create_time desc limit 1) refundStatus
        FROM
            mall_order AS t
        WHERE
            t.id = #{orderId}
    </select>


    <select id="selectOrderNeedConfirmReceipt"
            resultType="com.ruoyi.frequency.mallorder.entity.MallOrder">
        SELECT
            t.id,
            t.user_id,
            t.user_type
        FROM
            mall_order t
        WHERE
            t.order_status = 3
          AND del_flag = 1
          AND TIMESTAMPDIFF(DAY, t.send_time, now()) >= #{days}
    </select>

    <select id="arrivalAmount" resultType="com.ruoyi.frequency.mallorder.entity.MallOrder">
        SELECT
            t.*
        FROM
            mall_order t
        WHERE
            t.order_status = 4
          AND t.id NOT IN (
            SELECT
                order_id
            FROM
                mall_refund
            WHERE
                (
                    refund_type = 1
                        and order_type=1
                        AND (STATUS = 1 OR STATUS = 2)
                        OR (
                        refund_type = 2
                            AND (
                            STATUS = 1
                                OR STATUS = 2
                                OR STATUS = 3
                                OR STATUS = 4
                            )
                        )
                    )
              AND del_flag = 1
        )
          AND TIMESTAMPDIFF(DAY, t.receive_time, now()) >= t.after_sales_days
    </select>


    <select id="orderList" resultType="com.ruoyi.controller.resp.MallOrderPageResp">
        SELECT
        t.id orderId,
        t.goods_price,
        t.pay_price,
        t.order_status,
        case when
        (SELECT
        COUNT(1)
        FROM
        mall_refund r
        WHERE
        r.del_flag = 1
        and r.order_id = t.id)> 0
        then
        2 else 1 end isRefund,
        (SELECT
        r.status
        FROM
        mall_refund r
        WHERE
        r.del_flag = 1
        and r.order_id = t.id order by r.create_time desc limit 1) refundStatus,
        t.order_no,
        t.integral_price,
        t.send_com_courier_id,
        t.send_com_logistics,
        t.send_com_logistics_code
        FROM
        mall_order t
        WHERE
           t.del_flag = 1
        AND t.customer_show = 1
        AND t.id in
        <foreach item="id" collection="list" open="(" separator="," close=")">
            #{id}
        </foreach>
        ORDER BY
        t.create_time DESC
    </select>
    <select id="hasBuyNum" resultType="java.lang.Integer">
        SELECT
            IFNULL(sum(t.buy_num), 0)
        FROM
            mall_order_goods t
                LEFT JOIN mall_order t2 ON t.order_id = t2.id
        WHERE
            t2.user_id = #{userVo.userId}
          AND t2.user_type = #{userVo.userType}
          AND t2.order_status !=- 1
          AND t.goods_id = #{goodsId}
    </select>
    <select id="statistics" resultType="com.ruoyi.frequency.mallorder.model.result.MallOrderCount">
        SELECT
            (
                SELECT
                    count(t.id)
                FROM
                    mall_order t
                WHERE
                    t.id in
                    <foreach item="id" collection="list" open="(" separator="," close=")">
                        #{id}
                    </foreach>
                    AND t.order_status IN (3, 4, 5, 6)
            ) hasSend,
            (
                SELECT
                    count(t.id)
                FROM
                    mall_order t
                WHERE
                t.id in
                <foreach item="id" collection="list" open="(" separator="," close=")">
                    #{id}
                </foreach>
                AND t.order_status IN (2)
            ) notSend,
            (
            SELECT
            count(t.id)
            FROM
            mall_order t
            WHERE
            t.id in
            <foreach item="id" collection="list" open="(" separator="," close=")">
                #{id}
            </foreach>
            ) num
    </select>

</mapper>
