<?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.dashboard.mapper.DashboardMapper">

    <resultMap id="BusinessStatisticsStoreVO" type="BusinessStatisticsVO" autoMapping="true">
        <result property="deviceCount" column="device_count" typeHandler="com.ruoyi.system.mapper.typehandler.NonNullIntegerTyperHandler"/>
        <result property="orderCount" column="order_count" typeHandler="com.ruoyi.system.mapper.typehandler.NonNullIntegerTyperHandler"/>
        <result property="seconds" column="seconds" typeHandler="com.ruoyi.system.mapper.typehandler.NonNullDecimalTypeHandler"/>
        <result property="ele" column="ele" typeHandler="com.ruoyi.system.mapper.typehandler.NonNullDecimalTypeHandler"/>
        <result property="arrivalAmount" column="arrival_amount" typeHandler="com.ruoyi.system.mapper.typehandler.NonNullDecimalTypeHandler"/>
    </resultMap>


    <sql id="selectBusinessStatisticsVO">
        select
            if (stb.store_id is null, -1, stb.store_id) as store_id,
            if (stb.store_id is null, '未分配店铺', ss.name) as store_name,
            count(distinct stb.device_id) as device_count,
            sd.device_name as device_name,
            count(stb.bill_id) as order_count,
            sum(if(stb.suit_fee_type in ('1', '4'), timestampdiff(second, stb.suit_start_time, stb.suit_end_time), 0)) as `seconds`,
            sum(if(stb.suit_fee_type in ('2', '3'), stb.suit_end_ele - stb.suit_start_ele, 0)) as `ele`,
            sum(stb.arrival_amount) as arrival_amount
        from sm_transaction_bill stb
            left join sm_store ss on ss.store_id = stb.store_id
            left join sm_device sd on sd.device_id = stb.device_id
        <where>
            <if test="mchId != null">and stb.mch_id = #{mchId}</if>
            <!--店铺id=-1时，查询未分配店铺的数据-->
            <if test="storeId != null and storeId == -1">and stb.store_id is null</if>
            <if test="storeId != null and storeId != -1">and stb.store_id = #{storeId}</if>
            <if test="billType != null">and stb.type = #{billType}</if>
            <if test="storeName != null and storeName != ''">and ss.name like concat('%', #{storeName}, '%')</if>
            <if test="dateRange != null and dateRange.size() == 2">
                and date(stb.create_time) >= #{dateRange[0]} and date(stb.create_time) &lt;= #{dateRange[1]}
            </if>
            <if test="billStatusList != null and billStatusList.size() > 0">
                and stb.status in
                <foreach collection="billStatusList" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
        </where>
    </sql>

    <select id="selectBusinessStatisticsByStore" resultMap="BusinessStatisticsStoreVO">
        <include refid="selectBusinessStatisticsVO"/>
        group by stb.store_id
    </select>

    <select id="selectBusinessStatisticsByDevice" resultMap="BusinessStatisticsStoreVO">
        <include refid="selectBusinessStatisticsVO"/>
        group by stb.store_id, stb.device_id
    </select>

    <resultMap id="DashboardBonusGroupByMchVO" type="DashboardBonusGroupByMchVO">
        <result property="totalAmount" column="total_amount" typeHandler="com.ruoyi.system.mapper.typehandler.NonNullDecimalTypeHandler"/>
    </resultMap>
    <select id="selectBonusGroupByMch" resultMap="DashboardBonusGroupByMchVO">
        select
            sb.arrival_id as arrival_id,
            if(su.is_real, su.real_name, su.user_name) as arrival_name,
            sum(sb.payed_amount) as total_amount
        from ss_bonus sb
            left join sm_user su on su.user_id = sb.arrival_id
        <where>
            <include refid="com.ruoyi.ss.bonus.mapper.BonusMapper.searchCondition"/>
        </where>
        group by sb.arrival_id
    </select>
</mapper>
