<?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.ShopProdStatisticMapper">

    <select id="findProdRandingSummaryPage" resultType="com.czg.order.vo.DataSummaryProductSaleRankingVo">
        select
        t1.prod_id,
        t2.name as product_name,
        sum(t1.sale_count) as number,
        sum(t1.sale_amount) as amount
        from tb_shop_prod_statistic t1
        left join tb_product t2 on t1.prod_id = t2.id
        where t1.shop_id = #{shopId}
        and t1.create_day in
        <foreach item="day" collection="days" open="(" separator="," close=")">
            #{day}
        </foreach>
        group by t1.prod_id,t2.name
        order by sum(t1.sale_count) desc
    </select>
    <select id="getSaleSummaryCount" resultType="com.czg.order.vo.SaleSummaryCountVo">
        select
        sum(t1.sale_count) as sale_count,
        sum(t1.sale_amount) as total_amount,
        sum(t1.refund_count) as refund_count,
        sum(t1.refund_amount) as refund_amount
        from tb_shop_prod_statistic t1
        left join tb_product t2 on t1.prod_id = t2.id
        where t1.shop_id = #{shopId}
        <if test="productName != null and productName != ''">
          and t2.name like concat('%', #{productName}, '%')
        </if>
        <if test="prodCategoryId != null">
            and t2.category_id  = #{prodCategoryId}
        </if>
        <if test="beginDate != null and beginDate != ''">
            and t1.create_day >= str_to_date(#{beginDate}, '%Y-%m-%d')
        </if>
        <if test="endDate != null and endDate != ''">
            <![CDATA[
            and t1.create_day <= str_to_date(#{endDate}, '%Y-%m-%d')
            ]]>
        </if>
    </select>
    <select id="findSaleSummaryList" resultType="com.czg.order.vo.SaleSummaryInfoVo">
        select
            t1.prod_id,
            max(t1.id) as id,
            t2.NAME AS product_name,
            t3.name as category_name,
            sum(t1.sale_count) as sale_count,
            sum(t1.sale_amount) as sale_amount,
            sum(t1.refund_count) as refund_count,
            sum(t1.refund_amount) as refund_amount
        from tb_shop_prod_statistic t1
        left join tb_product t2 on t1.prod_id = t2.id
        left join tb_shop_prod_category t3 on t2.category_id = t3.id
        where t1.shop_id = #{shopId}
        <if test="productName != null and productName != ''">
            and t2.name like concat('%', #{productName}, '%')
        </if>
        <if test="prodCategoryId != null">
            and t2.category_id  = #{prodCategoryId}
        </if>
        <if test="beginDate != null and beginDate != ''">
            and t1.create_day >= str_to_date(#{beginDate}, '%Y-%m-%d')
        </if>
        <if test="endDate != null and endDate != ''">
            <![CDATA[
            and t1.create_day <= str_to_date(#{endDate}, '%Y-%m-%d')
            ]]>
        </if>
        group by t1.prod_id
        ORDER BY sum( t1.sale_count ) DESC,max(t1.id) DESC
    </select>
    <select id="getSaleSummaryCount2" resultType="com.czg.order.vo.SaleSummaryCountVo">
        SELECT
            sum(t1.pay_amount) as totalAmount,
            sum(t1.refund_amount) as  refundAmount,
            count(*) as saleCount,
            count(case when t1.refund_amount > 0 then 1 end) as refundCount
        FROM
            tb_order_info t1
        left join (
            SELECT
                x1.order_id,
                concat( ',', GROUP_CONCAT( x2.category_id ), ',' ) AS category_id,
                GROUP_CONCAT( x1.product_name ) AS product_name
            FROM
                tb_order_detail x1
            LEFT JOIN tb_product x2 ON x1.product_id = x2.id
            GROUP BY x1.order_id
        ) t2 on t1.id = t2.order_id
        where t1.shop_id = #{shopId}
        and t1.status in ('part-refund','refund','done')
        <if test="productName != null and productName != ''">
            and t2.product_name like concat('%', #{productName}, '%')
        </if>
        <if test="prodCategoryId != null">
            and t2.category_id like concat('%,', #{prodCategoryId}, ',%')
        </if>
        <if test="beginDate != null and beginDate != ''">
            and t1.create_time >= str_to_date(#{beginDate}, '%Y-%m-%d %H:%i:%s')
        </if>
        <if test="endDate != null and endDate != ''">
            <![CDATA[
            and t1.create_time <= str_to_date(#{endDate}, '%Y-%m-%d %H:%i:%s')
            ]]>
        </if>
    </select>
    <select id="findSaleSummaryList2" resultType="com.czg.order.vo.SaleSummaryInfoVo">
        SELECT
            t1.product_id,
            t1.product_name as productName,
            t3.name as category_name,
            sum( t1.num ) AS saleCount,
            sum( t1.pay_amount ) AS saleAmount,
            sum( t1.return_num ) AS refundCount,
            sum( t1.return_amount ) AS refundAmount
        FROM
            tb_order_detail t1
                left join tb_product t2 on t1.product_id = t2.id
                left join tb_shop_prod_category t3 on t2.category_id = t3.id
        where t1.shop_id = #{shopId}
        and t1.status in ('part-refund','refund','done')
        <if test="productName != null and productName != ''">
            and t1.product_name like concat('%', #{productName}, '%')
        </if>
        <if test="prodCategoryId != null">
            and t2.category_id  = #{prodCategoryId}
        </if>
        <if test="beginDate != null and beginDate != ''">
            and t1.create_time >= str_to_date(#{beginDate}, '%Y-%m-%d %H:%i:%s')
        </if>
        <if test="endDate != null and endDate != ''">
            <![CDATA[
            and t1.create_time <= str_to_date(#{endDate}, '%Y-%m-%d %H:%i:%s')
            ]]>
        </if>
        GROUP BY t1.product_id,t1.product_name
        ORDER BY sum( t1.num ) DESC,max(t1.product_id) DESC
    </select>
    <select id="findProdRandingSummaryPage2" resultType="com.czg.order.vo.DataSummaryProductSaleRankingVo">
        select
            t1.product_id,
            t1.product_name,
            sum(t1.num) as number,
            sum(t1.pay_amount) as amount,
            sum(t1.return_num) AS refundCount,
            sum(t1.return_amount) AS refundAmount
        from tb_order_detail t1
        where t1.shop_id = #{shopId}
        and t1.status in ('part-refund','refund','done')
        <if test="beginDate != null and beginDate != ''">
            and t1.create_time >= str_to_date(#{beginDate}, '%Y-%m-%d %H:%i:%s')
        </if>
        <if test="endDate != null and endDate != ''">
            <![CDATA[
            and t1.create_time <= str_to_date(#{endDate}, '%Y-%m-%d %H:%i:%s')
            ]]>
        </if>
        GROUP BY t1.product_id,t1.product_name
        order by sum(t1.num) desc
    </select>
</mapper>
