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

    <select id="findSummaryExportList" resultType="com.czg.order.vo.TableSummaryExportVo">
        SELECT `order`.table_code,
               `order`.trade_day                                  as create_date,
               concat(`order`.table_code, '_', `order`.trade_day) as tableConcatDate,
               IF(`order`.table_code IS NULL OR `order`.table_code = '' OR `table`.NAME IS NULL, '吧台',
                  `table`.NAME)                                   AS table_name,
               category.`name`                                    AS category_name,
               product.`name`                                     AS product_name,
               product.`id`                                       AS product_id,
               unit.`name`                                        AS unit_name,
               detail.sku_name,
               sum(detail.num - detail.return_num)                AS num,
               sum(detail.pay_amount)                             AS amount,
               sum(detail.refund_num)                             AS refund_num,
               sum(detail.return_amount)                          AS refund_amount
        FROM tb_order_info `order`
                 INNER JOIN tb_order_detail detail ON detail.order_id = `order`.id
            AND detail.is_temporary = 0
                 LEFT JOIN tb_product product ON detail.product_id = product.id
                 LEFT JOIN tb_shop_prod_category category ON product.category_id = category.id
                 LEFT JOIN tb_shop_prod_unit unit ON product.unit_id = unit.id
                 LEFT JOIN tb_shop_table `table`
                           ON `order`.table_code = `table`.table_code AND `order`.shop_id = `table`.shop_id
                               AND `table`.table_code != '' AND `table`.table_code IS NOT NULL
        WHERE `order`.shop_id = #{shopId}
          AND `order`.paid_time IS NOT NULL
          AND `order`.pay_mode != 'no-table'
          AND `order`.trade_day >= #{beginDate}
          AND `order`.trade_day &lt;= #{endDate}
        group by `order`.trade_day, `order`.table_code, detail.product_id, detail.sku_id
        order by `order`.trade_day, `order`.table_code, detail.product_id
    </select>
</mapper>
