<?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="selectProStatByDay" resultType="com.czg.order.entity.ShopProdStatistic">
        SELECT
        #{shopId} as shopId,
        #{day} as createDay,
        prod.category_id AS categoryId,
        CASE WHEN detail.is_temporary = 1 THEN -1 ELSE detail.product_id END AS prodId,
        CASE WHEN detail.is_temporary = 1 THEN '临时菜' ELSE prod.name END AS productName,
        sum(detail.num-detail.return_num) as saleCount,
        sum(detail.pay_amount) as saleAmount,
        sum(detail.refund_num) refundCount,
        sum(detail.return_amount) refundAmount
        FROM
        tb_order_info `order`
        INNER JOIN tb_order_detail detail ON `order`.id = detail.order_id
        INNER JOIN tb_product prod ON detail.product_id = prod.id
        WHERE
        `order`.shop_id = #{shopId}
        AND `order`.trade_day = #{day}
        AND `order`.paid_time IS NOT NULL
        <if test="productName != null and productName != ''">
            AND detail.product_name LIKE CONCAT('%',#{productName},'%')
        </if>
        <if test="categoryId != null and categoryId > 0">
            AND prod.category_id = #{categoryId}
        </if>
        GROUP BY prodId
    </select>

    <select id="getProdStatSingleDate" resultType="com.czg.order.entity.ShopProdStatistic">
        SELECT
        tb_shop_prod_statistic.*,
        CASE WHEN tb_shop_prod_statistic.prod_id = -1 THEN '临时菜' ELSE prod.name END AS productName
        FROM
        tb_shop_prod_statistic
        LEFT JOIN tb_product prod ON tb_shop_prod_statistic.prod_id = prod.id
        WHERE
        tb_shop_prod_statistic.shop_id = #{shopId}
        AND tb_shop_prod_statistic.create_day = #{day}
        <if test="productName != null and productName != ''">
            AND prod.name LIKE CONCAT('%',#{productName},'%')
        </if>
        <if test="categoryId != null and categoryId > 0">
            AND prod.category_id = #{categoryId}
        </if>
        ORDER BY
        tb_shop_prod_statistic.sale_count DESC, tb_shop_prod_statistic.prod_id
    </select>
    <select id="getProdStatDateRange" resultType="com.czg.order.entity.ShopProdStatistic">
        SELECT
        tb_shop_prod_statistic.prod_id as prodId,
        CASE WHEN prod_id = -1 THEN '临时菜' ELSE prod.name END AS productName,
        tb_shop_prod_statistic.shop_id as shopId,
        SUM(tb_shop_prod_statistic.sale_count) AS saleCount,
        SUM(tb_shop_prod_statistic.sale_amount) AS saleAmount,
        SUM(tb_shop_prod_statistic.refund_count) AS refundCount,
        SUM(tb_shop_prod_statistic.refund_amount) AS refundAmount
        FROM
        tb_shop_prod_statistic
        LEFT JOIN tb_product prod ON tb_shop_prod_statistic.prod_id = prod.id
        WHERE
        tb_shop_prod_statistic.shop_id = #{shopId}
        AND tb_shop_prod_statistic.create_day &gt;= #{start}
        AND tb_shop_prod_statistic.create_day &lt;= #{end}
        <if test="productName != null and productName != ''">
            AND prod.name LIKE CONCAT('%',#{productName},'%')
        </if>
        <if test="categoryId != null and categoryId > 0">
            AND prod.category_id = #{categoryId}
        </if>
        GROUP BY
        tb_shop_prod_statistic.prod_id
        ORDER BY
        tb_shop_prod_statistic.sale_count DESC, tb_shop_prod_statistic.prod_id
    </select>


    <select id="summaryCountByDay" resultType="com.czg.order.vo.SaleSummaryCountVo">
        SELECT
        sum(detail.num-detail.return_num) as saleCount,
        sum(detail.pay_amount) as totalAmount,
        sum(detail.refund_num) refundCount,
        sum(detail.return_amount) refundAmount
        FROM
        tb_order_info `order`
        INNER JOIN tb_order_detail detail ON `order`.id = detail.order_id
        INNER JOIN tb_product prod ON detail.product_id = prod.id
        WHERE
        `order`.shop_id = #{shopId}
        AND `order`.trade_day = #{day}
        AND `order`.paid_time IS NOT NULL
        <if test="productName != null and productName != ''">
            AND detail.product_name LIKE CONCAT('%',#{productName},'%')
        </if>
        <if test="categoryId != null and categoryId > 0">
            AND prod.category_id = #{categoryId}
        </if>
    </select>
    <select id="summaryCountSingleDate" resultType="com.czg.order.vo.SaleSummaryCountVo">
        SELECT
        sum(statistic.sale_count) AS saleCount,
        sum(statistic.sale_amount) AS saleAmount,
        sum(statistic.refund_count) AS refundCount,
        sum(statistic.refund_amount) AS refundAmount
        FROM
        tb_shop_prod_statistic statistic
        INNER JOIN tb_product prod ON statistic.prod_id = prod.id
        WHERE
        statistic.shop_id = #{shopId}
        AND statistic.create_day = #{day}
        <if test="productName != null and productName != ''">
            AND prod.name LIKE CONCAT('%',#{productName},'%')
        </if>
        <if test="categoryId != null and categoryId > 0">
            AND prod.category_id = #{categoryId}
        </if>
    </select>

     <select id="summaryCountDateRange" resultType="com.czg.order.vo.SaleSummaryCountVo">
         SELECT
         sum(statistic.sale_count) AS saleCount,
         sum(statistic.sale_amount) AS totalAmount,
         sum(statistic.refund_count) AS refundCount,
         sum(statistic.refund_amount) AS refundAmount
         FROM
         tb_shop_prod_statistic statistic
         INNER JOIN tb_product prod ON statistic.prod_id = prod.id
         WHERE
         statistic.shop_id = #{shopId}
         AND statistic.create_day &gt;= #{start}
         AND statistic.create_day &lt;= #{end}
         <if test="productName != null and productName != ''">
             AND prod.name LIKE CONCAT('%',#{productName},'%')
         </if>
         <if test="categoryId != null and categoryId > 0">
             AND prod.category_id = #{categoryId}
         </if>
     </select>


</mapper>
