<?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.account.mapper.BkOrderMapper">

    <select id="bookings" resultType="com.czg.account.entity.BkOrder">
        SELECT
        o.*, IFNULL(GROUP_CONCAT(DISTINCT t.table_name SEPARATOR '、'), '') AS tableInFos
        FROM bk_order o
        LEFT JOIN bk_order_table t ON t.book_order_id = o.id
        WHERE o.shop_id = #{shopId}
        <if test="search != null and search != ''">
            AND (o.call_username LIKE CONCAT('%', #{search}, '%')
            OR o.call_phone LIKE CONCAT('%', #{search}, '%')
            OR o.booking_username LIKE CONCAT('%', #{search}, '%')
            OR o.booking_phone LIKE CONCAT('%', #{search}, '%'))
        </if>
        <if test="start != null and end != null">
            AND o.booking_time >= CONCAT(#{start}, ' 00:00:00')
            AND o.booking_time &lt;= CONCAT(#{end}, ' 23:59:59')
        </if>
        <if test="status != null and status != ''">
            <if test="status == '已超时'">
                AND o.status = '待到店'
                AND o.booking_time &lt; DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s')
            </if>
            <if test="status != '已超时'">
                AND o.status = #{status}
            </if>
        </if>
        group by o.id
        order by FIELD(o.status, '待到店', '已到店', '已取消'), o.booking_time asc, o.id desc
    </select>

    <select id="bookingOrderStatistics" resultType="com.czg.account.vo.BookingOrderStatisticsVO">
        SELECT
        COUNT(CASE WHEN DATE(create_time) = CURDATE() THEN 1 END) as todayBookingCount,
        COUNT(CASE WHEN YEAR(create_time) = YEAR(CURDATE()) AND MONTH(create_time) = MONTH(CURDATE()) THEN 1 END) as thisMonthBookingCount,
        COUNT(CASE WHEN status = '待到店' THEN 1 END) as arrivedCount,
        COALESCE(
            ROUND(
                COUNT(CASE WHEN status = '已到店' THEN 1 END) * 100.0 /
                NULLIF(COUNT(CASE WHEN status != '已取消' THEN 1 END), 0),
                2
            ), 0
        ) as arrivalRate
        FROM bk_order o
        WHERE shop_id = #{shopId}
        <if test="search != null and search != ''">
            AND (o.call_username LIKE CONCAT('%', #{search}, '%')
            OR o.call_phone LIKE CONCAT('%', #{search}, '%')
            OR o.booking_username LIKE CONCAT('%', #{search}, '%')
            OR o.booking_phone LIKE CONCAT('%', #{search}, '%'))
        </if>
        <if test="start != null and end != null">
            AND o.booking_time >= CONCAT(#{start}, ' 00:00:00')
            AND o.booking_time &lt;= CONCAT(#{end}, ' 23:59:59')
        </if>
        <if test="status != null and status != ''">
            <if test="status == '已超时'">
                AND o.status = '待到店'
                AND o.booking_time &lt; DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s')
            </if>
            <if test="status != '已超时'">
                AND o.status = #{status}
            </if>
        </if>
    </select>
</mapper>
