<?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.ruoyi.web.modules.openstats.mapper.MallStatsOpenMapper">

    <select id="selectCategoryQtyByFinishYear" resultType="com.ruoyi.web.modules.openstats.dto.CategoryQtyRow">
        select
            ifnull(c1.category_id, 0) as categoryId,
            ifnull(c1.category_name, '未分类') as categoryName,
            sum(oi.quantity) as qty
        from biz_order o
        inner join biz_order_item oi on oi.order_id = o.order_id
        inner join biz_goods g on g.goods_id = oi.goods_id
        left join biz_goods_category c2 on c2.category_id = g.category_id and c2.shop_id is null
        left join biz_goods_category c1 on c1.category_id = c2.parent_id and c1.shop_id is null
        where o.order_status = '3'
          and o.order_status &lt;&gt; '5'
          and o.finish_time is not null
          and year(o.finish_time) = #{statYear}
        group by ifnull(c1.category_id, 0), ifnull(c1.category_name, '未分类')
    </select>

    <select id="selectOrderCountByYear" resultType="com.ruoyi.web.modules.openstats.dto.MonthCountRow">
        select month(o.create_time) as month, count(*) as cnt
        from biz_order o
        where year(o.create_time) = #{statYear}
          and o.order_status &lt;&gt; '5'
        group by month(o.create_time)
    </select>

    <select id="selectShopCountByYear" resultType="com.ruoyi.web.modules.openstats.dto.MonthCountRow">
        select month(create_time) as month, count(*) as cnt
        from biz_shop
        where year(create_time) = #{statYear}
        group by month(create_time)
    </select>

    <select id="selectRegionAmountByYear" resultType="com.ruoyi.web.modules.openstats.dto.RegionAmountRow">
        select o.consignee_address as consigneeAddress, o.pay_amount as amount
        from biz_order o
        where o.order_status = '3'
          and o.order_status &lt;&gt; '5'
          and o.finish_time is not null
          and year(o.finish_time) = #{statYear}
    </select>

    <select id="selectReviewContentsForWordCloud" resultType="String">
        select content
        from biz_goods_review
        where del_flag = '0'
          and show_flag = '1'
          and content is not null
          and trim(content) &lt;&gt; ''
    </select>

</mapper>
