<?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.ycwl.basic.mapper.OrderMapper">
    <resultMap id="PCBaseResultListMap" type="com.ycwl.basic.model.pc.order.resp.OrderRespVO">
        <id column="id" property="id"/>
        <result column="scenic_id" property="scenicId"/>
        <result column="member_id" property="memberId"/>
        <result column="nickname" property="memberNickname"/>
        <result column="uid" property="memberUid"/>
        <result column="openid" property="openId"/>
        <result column="price" property="price"/>
        <result column="slash_price" property="slashPrice"/>
        <result column="pay_price" property="payPrice"/>
        <result column="remark" property="remark"/>
        <result column="broker_id" property="brokerId"/>
        <result column="promo_code" property="promoCode"/>
        <result column="refund_reason" property="refundReason"/>
        <result column="refund_status" property="refundStatus"/>
        <result column="status" property="status"/>
        <result column="refund_at" property="refundAt"/>
        <result column="pay_at" property="payAt"/>
        <result column="cancel_at" property="cancelAt"/>
        <result column="create_at" property="createAt"/>
        <result column="update_at" property="updateAt"/>
    </resultMap>
    <resultMap id="PCBaseResultMap" type="com.ycwl.basic.model.pc.order.resp.OrderRespVO">
        <id column="id" property="id"/>
        <result column="scenic_id" property="scenicId"/>
        <result column="member_id" property="memberId"/>
        <result column="nickname" property="memberNickname"/>
        <result column="uid" property="memberUid"/>
        <result column="openid" property="openId"/>
        <result column="price" property="price"/>
        <result column="slash_price" property="slashPrice"/>
        <result column="pay_price" property="payPrice"/>
        <result column="remark" property="remark"/>
        <result column="broker_id" property="brokerId"/>
        <result column="promo_code" property="promoCode"/>
        <result column="refund_reason" property="refundReason"/>
        <result column="refund_status" property="refundStatus"/>
        <result column="status" property="status"/>
        <result column="refund_at" property="refundAt"/>
        <result column="pay_at" property="payAt"/>
        <result column="cancel_at" property="cancelAt"/>
        <result column="create_at" property="createAt"/>
        <result column="update_at" property="updateAt"/>
        <collection property="orderItemList" select="getOrderItemList" column="id" ofType="com.ycwl.basic.model.pc.order.resp.OrderItemVO">
            <result column="oiId" property="id"/>
            <result column="orderId" property="orderId"/>
            <result column="goods_id" property="goodsId"/>
            <result column="goodsName" property="goodsName"/>
            <result column="videoUrl" property="videoUrl"/>
            <result column="imgUrl" property="imgUrl"/>
            <result column="sourceType" property="sourceType"/>
            <result column="createTime" property="createTime"/>
        </collection>
    </resultMap>
    <resultMap id="AppBaseResultMap" type="com.ycwl.basic.model.pc.order.resp.OrderAppRespVO">
        <id column="id" property="id"/>
        <result column="price" property="price"/>
        <result column="slash_price" property="slashPrice"/>
        <result column="pay_price" property="payPrice"/>
        <result column="remark" property="remark"/>
        <result column="refund_reason" property="refundReason"/>
        <result column="refund_status" property="refundStatus"/>
        <result column="status" property="status"/>
        <result column="refund_at" property="refundAt"/>
        <result column="pay_at" property="payAt"/>
        <result column="cancel_at" property="cancelAt"/>
        <result column="create_at" property="createAt"/>
        <result column="update_at" property="updateAt"/>
        <result column="scenic_id" property="scenicId"/>
        <collection property="orderItemList" select="getOrderItemList" column="id" ofType="com.ycwl.basic.model.pc.order.resp.OrderItemVO">
            <result column="oiId" property="id"/>
            <result column="orderId" property="orderId"/>
            <result column="goods_id" property="goodsId"/>
            <result column="face_id" property="faceId"/>
            <result column="goods_name" property="goodsName"/>
            <result column="videoUrl" property="videoUrl"/>
            <result column="coverUrl" property="coverUrl"/>
            <result column="imgUrl" property="imgUrl"/>
        </collection>
    </resultMap>
    <select id="getOrderItemList" parameterType="java.lang.Long" resultType="com.ycwl.basic.model.pc.order.resp.OrderItemVO">
        WITH member_video_data AS (
            SELECT mv.member_id, mv.video_id, t.cover_url, t.name, mv.face_id, f.face_url, v.video_url
            FROM member_video mv
                     LEFT JOIN face f ON mv.face_id = f.id
                     LEFT JOIN template t ON mv.template_id = t.id
                     LEFT JOIN video v ON mv.video_id = v.id
        ),
             member_source_data AS (
                 SELECT ms.member_id, ms.type, ms.source_id, ms.face_id, f.face_url, s.video_url, s.url
                 FROM member_source ms
                          LEFT JOIN face f ON ms.face_id = f.id
                          LEFT JOIN source s ON ms.source_id = s.id
                 WHERE s.id IS NOT NULL
        ),
            member_photo_data AS (
                 SELECT mp.member_id, 3 as type, mp.id, mp.crop_url as url, mp.quantity, mp.status, mp.create_time
                  FROM member_print mp
            ),
            member_aio_photo_data AS (
                 SELECT 4 as type, s.id, s.url as url
                  FROM source s
            )
        SELECT
            oi.id AS oiId,
            oi.order_id AS orderId,
            oi.goods_id,
            msd.source_id,
            o.scenic_id AS scenic_id,
            CASE oi.goods_type
                WHEN '0' THEN mvd.cover_url
                WHEN '3' THEN mpd.url
                END AS coverUrl,
            oi.goods_type,
            CASE oi.goods_type
                WHEN '0' THEN mvd.name
                WHEN '1' THEN '录像集'
                WHEN '2' THEN '照片集'
                WHEN '3' THEN '照片打印'
                WHEN '4' THEN '一体机照片打印'
                ELSE '其他'
                END AS goods_name,
            CASE oi.goods_type
                WHEN '0' THEN mvd.face_id
                WHEN '1' THEN oi.goods_id
                WHEN '2' THEN oi.goods_id
                END AS face_id,
            CASE oi.goods_type
                WHEN '0' THEN mvd.face_url
                WHEN '1' THEN msd.face_url
                WHEN '2' THEN msd.face_url
                END AS face_url,
            CASE oi.goods_type
                WHEN '0' THEN mvd.video_url
                WHEN '1' THEN msd.video_url
                END AS videoUrl,
            CASE oi.goods_type
                WHEN '1' THEN msd.url
                WHEN '2' THEN msd.url
                WHEN '3' THEN mpd.url
                WHEN '4' THEN mpa.url
                END AS imgUrl
        FROM order_item oi
                 LEFT JOIN `order` o ON oi.order_id = o.id
                 LEFT JOIN member_video_data mvd ON o.face_id = mvd.face_id AND oi.goods_id = mvd.video_id
                 LEFT JOIN member_source_data msd ON o.face_id = msd.face_id  AND oi.goods_id = msd.face_id AND msd.type = oi.goods_type
                 LEFT JOIN member_photo_data mpd ON oi.goods_id = mpd.id AND mpd.type = oi.goods_type
                 LEFT JOIN member_aio_photo_data mpa ON oi.goods_id = mpa.id AND mpa.type = oi.goods_type
        WHERE oi.order_id = #{id};
    </select>

    <insert id="add">
        insert into `order`(id, member_id, openid, face_id, `type`, `price_config_id`, slash_price, price, pay_price, remark, broker_id, promo_code, scenic_id, status, coupon_price, coupon_id, coupon_record_id)
        VALUES (#{id}, #{memberId}, #{openId}, #{faceId}, #{type}, #{priceConfigId}, #{slashPrice}, #{price}, #{payPrice}, #{remark}, #{brokerId}, #{promoCode}, #{scenicId}, #{status}, #{couponPrice}, #{couponId}, #{couponRecordId})
    </insert>
    <insert id="addOrderItems">
        insert into order_item(order_id,goods_type, goods_id) VALUES
        <foreach collection="orderItems" item="item" index="index" separator=",">
            (#{item.orderId}, #{item.goodsType},#{item.goodsId})
        </foreach>
    </insert>
    <update id="update">
        update `order`
        <set>
            <if test="price!= null ">
                price = #{price},
            </if>
            <if test="payPrice!= null ">
                pay_price = #{payPrice},
            </if>
            <if test="remark!= null">
                remark = #{remark},
            </if>
            <if test="refundReason!= null and refundReason!= ''">
                refund_reason = #{refundReason},
            </if>
            <if test="refundStatus!= null ">
                refund_status = #{refundStatus},
            </if>
            <if test="status!= null ">
                `status` = #{status},
            </if>
            <if test="refundAt!= null ">
                refund_at = #{refundAt},
            </if>
            <if test="payAt!= null ">
                pay_at = #{payAt},
            </if>
            <if test="cancelAt!= null ">
                cancel_at = #{cancelAt},
            </if>
        </set>
        where id = #{id}
    </update>

    <update id="updateOrder">
        update `order`
        <set>
            <if test="remark!= null and remark!= ''">
                remark = #{remark},
            </if>
            <if test="refundReason!= null and refundReason!= ''">
                refund_reason = #{refundReason},
            </if>
            <if test="refundStatus!= null ">
                refund_status = #{refundStatus},
            </if>
            <if test="status!= null ">
                `status` = #{status},
            </if>
            <if test="refundAt!= null ">
                refund_at = #{refundAt},
            </if>
            <if test="payAt!= null ">
                pay_at = #{payAt},
            </if>
            <if test="cancelAt!= null ">
                cancel_at = #{cancelAt},
            </if>
        </set>
        where id = #{id}
    </update>
    <update id="updateOrderPrice">
        update `order`
        set
            coupon_price = #{couponPrice},
            coupon_id = #{couponId},
            coupon_record_id = #{couponRecordId},
            price = #{price},
            pay_price = `price` - `coupon_price`
        where id = #{id}
    </update>
    <delete id="deleteById">
        delete from `order` where id = #{id}
    </delete>
    <select id="list" resultMap="PCBaseResultListMap">
        select o.id, o.scenic_id, o.member_id,m.nickname, m.uid, o.type, o.openid, o.face_id, f.face_url, o.price, pay_price, remark, o.broker_id, o.promo_code,
               o.coupon_price,
               refund_reason, refund_status, o.`status`, refund_at, pay_at, cancel_at, o.create_at
        from `order`  AS o
        left join face f on o.face_id = f.id
        left join member m on o.member_id = m.id
        <where>
            <if test="id!= null ">
                and o.id LIKE CONCAT('%',#{id},'%')
            </if>
            <if test="scenicId != null">
                and o.scenic_id = #{scenicId}
            </if>
            <if test="memberNickname!= null and memberNickname!=''">
                and m.nickname like concat('%',#{memberNickname},'%')
            </if>
            <if test="memberId != null">
                and (o.member_id like concat('%',#{memberId},'%') or m.uid like concat('%',#{memberId},'%'))
            </if>
            <if test="memberUid!= null">
                and m.uid = #{memberUid}
            </if>
            <if test="remark!= null and remark!= ''">
                and remark like concat('%',#{remark},'%')
            </if>
            <if test="brokerId!= null ">
                and o.broker_id = #{brokerId}
            </if>
            <if test="refundReason!= null and refundReason!= ''">
                and refund_reason like concat('%',#{refundReason},'%')
            </if>
            <if test="refundStatus!= null ">
                and refund_status = #{refundStatus}
            </if>
            <if test="status!= null ">
                and o.`status` = #{status}
            </if>
            <if test="startCreateTime!= null ">
                and o.create_at >= #{startCreateTime}
            </if>
            <if test="endCreateTime!= null ">
                and o.create_at &lt;= #{endCreateTime}
            </if>
            <if test="startPayTime!= null ">
                and pay_at &gt;= #{startPayTime}
            </if>
            <if test="endPayTime!= null ">
                and pay_at &lt;= #{endPayTime}
            </if>
            <if test="startRefundTime!= null ">
                and refund_at &gt;= #{startRefundTime}
            </if>
            <if test="endRefundTime!= null ">
                and refund_at &lt;= #{endRefundTime}
            </if>
            <if test="startCancelTime!= null ">
                and cancel_at &gt;= #{startCancelTime}
            </if>
            <if test="endCancelTime!= null ">
                and cancel_at &lt;= #{endCancelTime}
            </if>
            <if test="type != null">
                <if test="type &lt; 0">
                    and o.type = #{type}
                </if>
                <if test="type &gt;= 0">
                    and o.id in (select order_id from order_item where goods_type = #{type})
                </if>
            </if>
        </where>
        order by o.create_at desc
    </select>
    <select id="listDetail" resultMap="PCBaseResultMap">
        select o.id, o.scenic_id, o.member_id,m.nickname, m.uid, o.type, o.openid, o.face_id, f.face_url, o.price, pay_price, remark, o.broker_id, o.promo_code,
        o.coupon_price,
        refund_reason, refund_status, o.`status`, refund_at, pay_at, cancel_at, o.create_at
        from `order`  AS o
        left join face f on o.face_id = f.id
        left join member m on o.member_id = m.id
        <where>
            <if test="id!= null ">
                and o.id = #{id}
            </if>
            <if test="scenicId != null">
                and o.scenic_id = #{scenicId}
            </if>
            <if test="memberNickname!= null and memberNickname!=''">
                and m.nickname like concat('%',#{memberNickname},'%')
            </if>
            <if test="memberUid!= null">
                and m.uid = #{memberUid}
            </if>
            <if test="remark!= null and remark!= ''">
                and remark like concat('%',#{remark},'%')
            </if>
            <if test="brokerId!= null ">
                and o.broker_id = #{brokerId}
            </if>
            <if test="promoCode!= null and promoCode!= ''">
                and o.promo_code like concat('%',#{promoCode},'%')
            </if>
            <if test="refundReason!= null and refundReason!= ''">
                and refund_reason like concat('%',#{refundReason},'%')
            </if>
            <if test="refundStatus!= null ">
                and refund_status = #{refundStatus}
            </if>
            <if test="status!= null ">
                and o.`status` = #{status}
            </if>
            <if test="startCreateTime!= null ">
                and o.create_at >= #{startCreateTime}
            </if>
            <if test="endCreateTime!= null ">
                and o.create_at &lt;= #{endCreateTime}
            </if>
            <if test="startPayTime!= null ">
                and pay_at &gt;= #{startPayTime}
            </if>
            <if test="endPayTime!= null ">
                and pay_at &lt;= #{endPayTime}
            </if>
            <if test="startRefundTime!= null ">
                and refund_at &gt;= #{startRefundTime}
            </if>
            <if test="endRefundTime!= null ">
                and refund_at &lt;= #{endRefundTime}
            </if>
            <if test="startCancelTime!= null ">
                and cancel_at &gt;= #{startCancelTime}
            </if>
            <if test="endCancelTime!= null ">
                and cancel_at &lt;= #{endCancelTime}
            </if>
        </where>
        order by o.create_at desc
    </select>
    <select id="getById" resultMap="PCBaseResultMap">
        select o.id, o.scenic_id, o.member_id, o.type, o.openid, o.face_id, f.face_url, o.price, o.pay_price, o.remark, o.broker_id, o.promo_code, o.refund_reason,
               o.coupon_price,
               o.refund_status, o.status, o.create_at, o.update_at, o.pay_at, o.cancel_at, o.refund_at,
               m.nickname, m.uid
            from `order` o
        left join face f on o.face_id = f.id
        left join member m on m.id = o.member_id
        where o.id = #{id}
    </select>
    <select id="countByUserId" resultType="java.lang.Integer">
        select count(1) num
        from `order` o
        where o.member_id = #{userId}
    </select>
    <select id="appList" resultMap="AppBaseResultMap">
        select  o.id, o.member_id,o.openid, o.type, o.face_id, f.face_url, o.price, pay_price, remark, o.broker_id, o.promo_code,
        o.coupon_price,
        refund_reason, refund_status, o.`status`, o.create_at, refund_at, pay_at, cancel_at,
        o.scenic_id
        from `order` AS o
        left join face f on o.face_id = f.id
        order by o.create_at desc
    </select>
    <select id="appDetail" resultMap="AppBaseResultMap">
        select o.id, o.member_id, o.face_id,o.openid, o.type, o.price, o.slash_price, pay_price, remark, o.broker_id, o.promo_code,
               o.coupon_price,
                        refund_reason, refund_status, o.`status`, o.create_at, refund_at, pay_at, cancel_at,
                        o.scenic_id
        from `order`  AS o
                 left join member m on o.member_id = m.id
        where o.id = #{id}
    </select>
    <select id="refundList" resultType="com.ycwl.basic.model.pc.order.resp.OrderRespVO">
        select o.id, o.scenic_id, o.member_id,m.nickname,m.uid as memberUid , o.type, o.openid, o.price, pay_price, remark, o.broker_id, o.promo_code,
        refund_reason, refund_status, o.`status`, refund_at, pay_at, cancel_at, o.create_at
        from `order`  AS o
        left join member m on o.member_id = m.id
        <where>
            o.refund_status != 0
            <if test="id!= null ">
                and o.id = #{id}
            </if>
            <if test="scenicId != null">
                and o.scenic_id = #{scenicId}
            </if>
            <if test="memberNickname!= null and memberNickname!=''">
                and m.nickname like concat('%',#{memberNickname},'%')
            </if>
            <if test="memberUid!= null">
                and m.uid = #{memberUid}
            </if>
            <if test="remark!= null and remark!= ''">
                and remark like concat('%',#{remark},'%')
            </if>
            <if test="brokerId!= null ">
                and o.broker_id = #{brokerId}
            </if>
            <if test="promoCode!= null and promoCode!= ''">
                and o.promo_code like concat('%',#{promoCode},'%')
            </if>
            <if test="refundReason!= null and refundReason!= ''">
                and refund_reason like concat('%',#{refundReason},'%')
            </if>
            <if test="status!= null ">
                and o.`status` = #{status}
            </if>
            <if test="startCreateTime!= null ">
                and o.create_at >= #{startCreateTime}
            </if>
            <if test="endCreateTime!= null ">
                and o.create_at &lt;= #{endCreateTime}
            </if>
            <if test="startPayTime!= null ">
                and pay_at &gt;= #{startPayTime}
            </if>
            <if test="endPayTime!= null ">
                and pay_at &lt;= #{endPayTime}
            </if>
            <if test="startRefundTime!= null ">
                and refund_at &gt;= #{startRefundTime}
            </if>
            <if test="endRefundTime!= null ">
                and refund_at &lt;= #{endRefundTime}
            </if>
            <if test="startCancelTime!= null ">
                and cancel_at &gt;= #{startCancelTime}
            </if>
            <if test="endCancelTime!= null ">
                and cancel_at &lt;= #{endCancelTime}
            </if>
        </where>
        order by o.create_at desc
    </select>
    <select id="listOrderItemByOrderId" resultType="com.ycwl.basic.model.pc.order.entity.OrderItemEntity">
        select * from order_item where order_id = #{orderId}
    </select>
    <select id="getUserBuyItem" resultType="com.ycwl.basic.model.pc.order.entity.OrderEntity">
        select o.*
        from order_item oi
        left join `order` o on o.id = oi.order_id
        where o.member_id = #{userId}
          and oi.goods_id = #{goodsId}
          and oi.goods_type = #{goodsType}
          and o.status = 1
        limit 1
    </select>
    <select id="get" resultType="com.ycwl.basic.model.pc.order.entity.OrderEntity">
        select * from `order` where id = #{id}
    </select>
    <select id="getOrderItem" resultType="com.ycwl.basic.model.pc.order.entity.OrderItemEntity">
        select * from order_item where id = #{id}
    </select>
    <select id="queryTypeOrder" resultType="com.ycwl.basic.model.pc.order.entity.OrderEntity">
        select * from `order` where member_id = #{userId} and face_id = #{faceId} and type = #{orderType} and price_config_id = #{priceConfigId}
                              order by pay_at desc
        limit 1
    </select>
    <select id="getUserOrderItem" resultType="com.ycwl.basic.model.pc.order.entity.OrderEntity">
        select o.*
        from order_item oi
        left join `order` o on o.id = oi.order_id
        where o.member_id = #{userId} and o.scenic_id = #{scenicId} and o.type = #{orderType}
        <if test="configId != null">
            and o.price_config_id = #{configId}
        </if>
        <if test="goodsType != null">
            and oi.goods_type = #{goodsType}
        </if>
        <if test="goodsId != null">
            and oi.goods_id = #{goodsId}
        </if>
    </select>
    <select id="getOrderItems" resultType="com.ycwl.basic.model.pc.order.entity.OrderItemEntity">
        select * from order_item where order_id = #{orderId}
    </select>
    <update id="updateMemberIdByFaceId">
        update `order`
        set member_id = #{memberId}
        where face_id = #{faceId}
    </update>
</mapper>