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

-- Active: xiaoding_test@@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表查询出的project_id通过order表的project_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 VIEW IF EXISTS manage_alarm_order;

CREATE OR REPLACE VIEW manage_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 /* 序号 */,
    t1.order_no AS order_no /* 订单号 */,
    p.title AS project_name /* 项目名称 */,
    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.project_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;

SELECT * FROM manage_alarm_order;