<?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.czg.service.order.mapper.OrderInfoMapper">

    <sql id="handoverCommonWhere">
        AND t1.shop_id =
        #{shopId}
        <![CDATA[
        AND
        t1
        .
        paid_time
        >=
        str_to_date
        (
        #{loginTime},
        '%Y-%m-%d %H:%i:%s'
        )
        AND
        t1
        .
        paid_time
        <=
        str_to_date
        (
        #{handoverTime},
        '%Y-%m-%d %H:%i:%s'
        )
        ]]>
    </sql>
    <update id="decrMoney">
        update tb_order_info set origin_amount = origin_amount-#{amount}, update_time=now() where id=#{id} and origin_amount-#{amount} >= 0;
    </update>

    <select id="getHandoverCashAmount" resultType="java.math.BigDecimal">
        SELECT
        ifnull(sum(t1.pay_amount),0)
        FROM tb_order_info t1
        <where>
            <include refid="handoverCommonWhere"/>
            and t1.pay_type = 'cash_pay'
        </where>
    </select>
    <select id="getHandoverRefundAmount" resultType="java.math.BigDecimal">
        SELECT
        ifnull(sum(t1.refund_amount),0)
        FROM tb_order_info t1
        <where>
            <include refid="handoverCommonWhere"/>
        </where>
    </select>
    <select id="getHandoverTotalAmount" resultType="java.math.BigDecimal">
        SELECT
        ifnull(sum(t1.pay_amount),0)
        FROM tb_order_info t1
        <where>
            <include refid="handoverCommonWhere"/>
        </where>
    </select>
    <select id="getHandoverOrderNum" resultType="java.lang.Integer">
        SELECT
        count(*)
        FROM tb_order_info t1
        <where>
            <include refid="handoverCommonWhere"/>
        </where>
    </select>
    <select id="getHandoverDetailList" resultType="com.czg.account.vo.HandoverProductListVo">
        SELECT
        t2.product_id,
        t2.sku_id,
        GROUP_CONCAT( DISTINCT t2.product_name ) AS product_name,
        GROUP_CONCAT( DISTINCT t2.sku_name ) AS sku_name,
        sum( ifnull(t2.num,0) ) AS num,
        sum( ifnull(t2.pay_amount,0) ) amount
        FROM
        tb_order_detail t2
        LEFT JOIN tb_order_info t1 ON t1.id = t2.order_id
        <where>
            <include refid="handoverCommonWhere"/>
        </where>
        GROUP BY
        t2.product_id,
        t2.sku_id
    </select>
    <select id="getHandoverWechatAmount" resultType="java.math.BigDecimal">
        SELECT
        ifnull(sum(t1.pay_amount),0)
        FROM tb_order_info t1
        <where>
            <include refid="handoverCommonWhere"/>
            and t1.pay_type = 'wechat_mini'
        </where>
    </select>
    <select id="getHandoverAlipayAmount" resultType="java.math.BigDecimal">
        SELECT
        ifnull(sum(t1.pay_amount),0)
        FROM tb_order_info t1
        <where>
            <include refid="handoverCommonWhere"/>
            and t1.pay_type = 'alipay_mini'
        </where>
    </select>
    <select id="getHandoverVipPayAmount" resultType="java.math.BigDecimal">
        SELECT
        ifnull(sum(t1.pay_amount),0)
        FROM tb_order_info t1
        <where>
            <include refid="handoverCommonWhere"/>
            and t1.pay_type = 'vip_pay'
        </where>
    </select>
    <select id="getHandoverVipChargeAmount" resultType="java.math.BigDecimal">
        select ifnull(sum(t1.amount), 0)
        from tb_shop_user_flow t1
        where t1.shop_id = #{shopId}
          and t1.biz_code in ('cashIn', 'wechatIn', 'alipayIn', 'adminIn')
          and t1.recharge_id is not null
        <![CDATA[
          AND t1.create_time >= str_to_date(#{loginTime}, '%Y-%m-%d %H:%i:%s')
          AND t1.create_time <= str_to_date(#{handoverTime}, '%Y-%m-%d %H:%i:%s')
        ]]>
    </select>
    <select id="getHandoverQuickPayAmount" resultType="java.math.BigDecimal">
        SELECT
        ifnull(sum(pay_amount),0)
        FROM tb_order_info t1
        <where>
            <include refid="handoverCommonWhere"/>
            and t1.pay_type in ('main_scan','back_scan')
        </where>
    </select>
    <select id="getHandoverCategoryList" resultType="com.czg.account.vo.HandoverCategoryListVo">
        SELECT case when t3.category_id is null then 0 else t3.category_id end as category_id,
        case when t4.name is null then '未分类' else t4.name end as category_name,
        count(DISTINCT t2.product_id) AS quantity,
        sum(ifnull(t2.num, 0)) AS num,
        sum(ifnull(t2.pay_amount, 0)) amount
        FROM tb_order_detail t2
        LEFT JOIN tb_order_info t1 ON t1.id = t2.order_id
        LEFT JOIN tb_product t3 ON t3.id = t2.product_id
        LEFT JOIN tb_shop_prod_category t4 ON t4.id = t3.category_id
        <where>
            <include refid="handoverCommonWhere"/>
        </where>
        GROUP BY t3.category_id
    </select>
    <select id="getHandoverCreditAmount" resultType="java.math.BigDecimal">
        SELECT
        ifnull(sum(t1.pay_amount),0)
        FROM tb_order_info t1
        <where>
            <include refid="handoverCommonWhere"/>
            and t1.pay_type = 'credit_pay'
        </where>
    </select>

</mapper>
