owl-admin/doc/系统设计/数据库设计/物理模型/分析后台设计增加/10报警订单.sql

-- Active: 1732070527111@@192.168.110.85@3306@xiaoding_test

/*
订单编号是coach_alerts表的order_id，用户名称是coach_alerts表通过order_id关联order表查询出的user_id通过order表的user_id关联member_users表查询出nickname为用户名称，
项目名称是coach_alerts表通过order_id关联order表查询出的service_id通过order表的service_id关联project表查询出title为项目名称,
下单时间是coach_alerts表通过order_id关联的order_records表经过判断order_id是否存在以及state状态是否是接单状态最后都满足则获取order_records表的created_at字段为下单时间，
支付金额是coach_alerts表通过order_id直接与wallet_payment_records表关联判断订单编号是否存在，存在则获取wallet_payment_records表的actual_amount为支付金额，
服务状态是coach_alerts表通过order_id关联的order_records表经过判断order_id是否存在以及state最新状态，
技师昵称是coach_alerts表通过order_id关联order表查询出的coach_id通过order表的coach_id关联coach_users表查询出user_id然后再去关联member_users表查询出nickname为技师名称，
技师接单时间是coach_alerts表通过order_id关联的order_records表经过判断order_id是否存在以及state状态是否是技师接单状态最后都满足则获取order_records表的created_at字段为技师接单时间，
区域是coach_alerts表的order_id关联的order表中的area_code字段，
地址是coach_alerts表的order_id关联的order表中的address字段，
备注是coach_alerts表的order_id关联的order表中的remark字段，
代理商是coach_alerts表的order_id关联的order表获取agent_id再去关联agent_infos表获取user_id再去关联member_users表中的nickname字段为代理商，
代理商电话是coach_alerts表的order_id关联的order表获取agent_id再去关联agent_infos表获取user_id再去关联member_users表中的mobile字段为代理商电话

查询不出的字段：跟单客服是系统表的
没设计的字段：附近站点电话、负责人电话
*/

/* 
查询订单编号、项目名称、用户名称、下单时间、支付金额、服务状态、技师、技师接单时间、区域、地址、备注、代理商、代理商电话
*/
DROP TABLE IF EXISTS report_alarm_order;
CREATE TABLE report_alarm_order AS
WITH order_create_time AS (
    /* 获取下单时间 */
    SELECT 
        order_id,
        created_at 
    FROM order_records ord1 
    WHERE state = '接单状态' /* 请替换为实际的接单状态值 */
    AND created_at = (
        SELECT MIN(created_at) 
        FROM order_records ord2 
        WHERE ord1.order_id = ord2.order_id 
        AND state = '接单状态'
    )
),
coach_accept_time AS (
    /* 获取技师接单时间 */
    SELECT 
        order_id,
        created_at 
    FROM order_records ord1 
    WHERE state = '技师接单状态' /* 请替换为实际的技师接单状态值 */
    AND created_at = (
        SELECT MIN(created_at) 
        FROM order_records ord2 
        WHERE ord1.order_id = ord2.order_id 
        AND state = '技师接单状态'
    )
)
SELECT 
    ca.order_id as order_id /* 订单编号 */,
    p.title as project_title /* 项目名称 */,
    mu1.nickname as user_name /* 用户名称 */,
    oct.created_at as order_time /* 下单时间 */,
    wpr.actual_amount as payment_amount /* 支付金额 */,
    orr.state as service_state /* 服务状态 */,
    mu2.nickname as coach_name /* 技师昵称 */,
    cat.created_at as coach_accept_time /* 技师接单时间 */,
    t1.area_code as area_code /* 区域 */,
    t1.address as address /* 地址 */,
    t1.remark as remark /* 备注 */,
    mu3.nickname as agent_name /* 代理商 */,
    mu3.mobile as agent_mobile /* 代理商电话 */
FROM coach_alerts ca
LEFT JOIN `order` t1 ON ca.order_id = t1.id
LEFT JOIN project p ON t1.service_id = p.id
LEFT JOIN member_users mu1 ON t1.user_id = mu1.id
LEFT JOIN order_create_time oct ON ca.order_id = oct.order_id
LEFT JOIN wallet_payment_records wpr ON ca.order_id = wpr.order_id
LEFT JOIN order_records orr ON ca.order_id = orr.order_id
LEFT JOIN coach_users cu ON t1.coach_id = cu.id
LEFT JOIN member_users mu2 ON cu.user_id = mu2.id
LEFT JOIN coach_accept_time cat ON ca.order_id = cat.order_id
LEFT JOIN agent_infos ai ON t1.agent_id = ai.id
LEFT JOIN member_users mu3 ON ai.user_id = mu3.id
WHERE orr.created_at = (
    /* 获取最新状态记录 */
    SELECT MAX(created_at)
    FROM order_records
    WHERE order_id = ca.order_id
)
ORDER BY ca.order_id DESC;