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

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

/*
订单编号是order表的id，项目名称是order表通过service_id与project表关联查询出项目名称title,
用户名称是order表通过user_id和member_users表关联查询出用户姓名nickname,
下单时间是order表的中的created_at，支付金额、支付方式是order表的id与 wallet_payment_records表的order_id关联查询出，
技师/店铺的意思是该订单有可能是技师接单也有可能是店铺接单是通过订单来源类型进行区分的，区域是order表的area_code,
地址是order表的address,备注是order表的remark,订单来源是order表的order_source，路费是order表的路程金额，
服务状态是order表通过id与order_records表关联查询state,技师/店铺接单时间以及到达时间、开始服务时间都是从order_records表中的created_at字段，
技师减免是order表通过coach_id和project_id与coach_project关联通过判断找出该discount_amount字段，
店铺优惠劵和平台优惠劵是order表中id与market_coupon_records表判断是否存在该订单编号存在然后通过优惠卷编号与market_coupons表关联获取优惠劵金额，
订单类型是order表order_type字段，上级订单是因为客户在原有订单基础上再次下单从而产生的关联原有订单,
上级订单项目是因为客户在原有订单基础上再次下单从而产生的关联原有订单项目名称，
用户确认撤离时间、技师确认离开时间都是从order_records表中的created_at字段。
补充逻辑：技师名称是根据order表的coach_is与coach_users关联查询出user_id再与用户表关联查询出技师名称
先不要了没设计： 营销类型  砍价减免金额  打赏金额  悬赏金额  跟单客服是系统表里的
*/
/* 订单列表视图 */
DROP VIEW IF EXISTS manage_order_list;

CREATE OR REPLACE VIEW manage_order_list AS
WITH
    order_status AS (
        SELECT
            order_id,
            state /* 状态 */,
            created_at /* 状态变更时间 */,
            ROW_NUMBER() OVER (
                PARTITION BY
                    order_id,
                    state
                ORDER BY created_at
            ) AS rn
        FROM order_records
    )
SELECT
    o.id AS order_id /* 序号 */,
    o.order_no AS order_no /* 订单号 */,
    p.title AS project_title /* 项目名称 */,
    mu.nickname AS user_name /* 用户名称 */,
    o.created_at AS order_time /* 下单时间 */,
    o.total_amount AS total_amount /* 支付金额 */,
    os_state.state AS service_state /* 服务状态 */,
    os_accept.created_at AS accept_time /* 技师/店铺接单时间 */,
    o.area_code AS area_code /* 区域 */,
    o.address AS address /* 地址 */,
    o.remark AS remark /* 备注 */,
    o.source AS source /* 订单来源 */,
    os_arrive.created_at AS arrive_time /* 到达时间 */,
    TIMESTAMPDIFF(
        MINUTE,
        NOW(),
        os_arrive.created_at
    ) AS countdown /* 订单倒计时 */,
    os_start.created_at AS start_time /* 服务开始时间 */,
    mc_platform.discount_value AS platform_discount /* 平台优惠券 */,
    cp.discount_amount AS coach_discount /* 技师减免 */,
    mc_shop.discount_value AS shop_discount /* 商铺代金券 */,
    wpr.payment_method AS payment_method /* 支付方式 */,
    o.traffic_amount AS traffic_amount /* 路费 */,
    o.type AS type /* 订单类型 */,
    os_user_leave.created_at AS user_leave_time /* 用户确认撤离时间 */,
    os_coach_leave.created_at AS coach_leave_time /* 技师确认离开时间 */
FROM
    `order` o
    LEFT JOIN project p ON o.project_id = p.id /* 关联项目表 */
    LEFT JOIN member_users mu ON o.user_id = mu.id /* 关联用户表 */
    /* 关联技师表和用户表获取技师名称 */
    LEFT JOIN coach_users cu ON o.coach_id = cu.id /* 关联技师表 */
    LEFT JOIN member_users coach_mu ON cu.user_id = coach_mu.id /* 关联用户表获取技师名称 */
    LEFT JOIN shop_auth_records sa ON o.shop_id = sa.shop_id /* 关联店铺认证表 */
    LEFT JOIN coach_project cp ON o.coach_id = cp.coach_id
    AND o.project_id = cp.project_id /* 关联技师项目表 */
    LEFT JOIN wallet_payment_records wpr ON o.id = wpr.order_id /* 关联支付记录表 */
    /* 关联优惠券记录和优惠券表-平台券 */
    LEFT JOIN market_coupon_records mcr_p ON o.id = mcr_p.order_id
    LEFT JOIN market_coupons mc_platform ON mcr_p.coupon_id = mc_platform.id
    /* 关联优惠券记录和优惠券表-店铺券 */
    LEFT JOIN market_coupon_records mcr_s ON o.id = mcr_s.order_id
    LEFT JOIN market_coupons mc_shop ON mcr_s.coupon_id = mc_shop.id
    /* 关联订单状态-当前状态 */
    LEFT JOIN order_status os_state ON o.id = os_state.order_id
    AND os_state.rn = 1
    /* 关联订单状态-接单时间 */
    LEFT JOIN order_status os_accept ON o.id = os_accept.order_id
    AND os_accept.state = 8
    AND os_accept.rn = 1
    /* 关联订单状态-到达时间 */
    LEFT JOIN order_status os_arrive ON o.id = os_arrive.order_id
    AND os_arrive.state = 10
    AND os_arrive.rn = 1
    /* 关联订单状态-开始服务时间 */
    LEFT JOIN order_status os_start ON o.id = os_start.order_id
    AND os_start.state = 11
    AND os_start.rn = 1
    /* 关联订单状态-用户确认撤离时间 */
    LEFT JOIN order_status os_user_leave ON o.id = os_user_leave.order_id
    AND os_user_leave.state = 14
    AND os_user_leave.rn = 1
    /* 关联订单状态-技师确认离开时间 */
    LEFT JOIN order_status os_coach_leave ON o.id = os_coach_leave.order_id
    AND os_coach_leave.state = 14
    AND os_coach_leave.rn = 1
WHERE
    o.deleted_at IS NULL
ORDER BY o.created_at DESC;




/* 订单管理新视图 */
DROP VIEW IF EXISTS v_order_list;

CREATE OR REPLACE VIEW v_order_list AS
SELECT
    o.id AS id /* id */,
    o.order_no AS order_no /* 订单交易编号 */,
    o.`type` AS `type` /* 订单类型 */,
    o.`source` AS `source` /* 订单来源 */,
    o.payment_type AS payment_type /* 支付方式 */,
    o.project_amount AS project_amount /* 项目金额 */,
    o.traffic_amount AS traffic_amount /* 路费 */,
    o.total_amount AS total_amount /* 订单金额 */,
    o.balance_amount AS balance_amount /* 余额支付金额 */,
    o.pay_amount AS pay_amount /* 支付金额 */,
    o.discount_amount AS discount_amount /* 优惠金额 */,
    o.tip_amount AS tip_amount /* 打赏金额 */,
    o.distance AS distance /* 距离 */,
    o.latitude AS latitude /* 客户纬度 */,
    o.longitude AS longitude /* 客户经度 */,
    o.location AS location /* 客户位置 */,
    o.address AS address /* 客户地址 */,
    o.area_code AS area_code /* 客户区域 */,
    o.remark AS remark /* 客户备注 */,
    o.created_at AS created_at /* 下单时间 */,
    o.state AS state /* 订单状态 */,
    o.service_start_time AS service_start_time /* 开始服务时间 */,
    o.service_end_time AS service_end_time /* 结束服务时间 */,
    mu.id AS user_id, /* 用户ID */
    mu.nickname AS user_nickname /* 用户昵称 */,
    mu.mobile AS user_mobile /* 用户联系方式 */,
    cu.id AS coach_id, /* 技师ID */
    cu.level AS coach_level /* 技师等级 */,
    cu.work_status AS coach_work_status /* 技师工作状态 */,
    cu.formal_photo AS coach_formal_photo /* 技师正装照 */,
    cu.score AS coach_score /* 技师评分 */,
    cir.id AS coach_info_id, /* 技师信息ID */
    cir.nickname AS coach_nickname /* 技师昵称 */,
    cir.mobile AS coach_mobile /* 技师联系方式 */,
    cir.avatar AS coach_avatar /* 技师头像 */,
    p.id AS project_id, /* 项目ID */
    p.title AS project_name /* 项目名称 */,
    p.price AS project_price /* 项目金额 */,
    p.duration AS service_duration /* 服务时长 */,
    p.cover AS project_cover /* 项目封面 */,
    sa.id AS shop_id, /* 店铺ID */
    sa.shop_name AS shop_name, /* 店铺名称 */
    ara.id AS agent_id, /* 代理商ID */
    ara.real_name AS real_name, /* 代理商 */
    /* 客户订单统计 */
    COUNT(CASE WHEN o.type != 3 AND o.state = 12 THEN 1 END) as order_count /* 订单次数 */,
    COUNT(CASE WHEN o.type = 3 AND o.state = 12 THEN 1 END) as overtime_count /* 加钟次数 */,
    COUNT(CASE WHEN o.tip_amount IS NOT NULL AND o.tip_amount > 0 AND o.state = 12 THEN 1 END) as reward_count, /* 打赏次数 */
    /* 技师订单统计 */
    COUNT(CASE WHEN o.type != 3 AND o.state = 12 THEN 1 END) OVER (PARTITION BY o.coach_id) as coach_order_count /* 技师订单次数 */,
    COUNT(CASE WHEN o.type = 3 AND o.state = 12 THEN 1 END) OVER (PARTITION BY o.coach_id) as coach_overtime_count /* 技师加钟次数 */
FROM `order` o
LEFT JOIN member_users mu ON o.user_id = mu.id
LEFT JOIN coach_users cu ON o.coach_id = cu.id
LEFT JOIN coach_info_records cir ON cu.info_record_id = cir.id
LEFT JOIN project p ON o.project_id = p.id /* 关联项目表 */
LEFT JOIN shop_auth_records sa ON o.shop_id = sa.shop_id /* 关联店铺认证表 */
LEFT JOIN agent_real_auth_records ara ON o.agent_id = ara.agent_id /* 关联法人认证表 */
GROUP BY o.id, o.order_no, o.type, o.source, o.payment_type, o.project_amount, o.traffic_amount,
         o.total_amount, o.balance_amount, o.pay_amount, o.discount_amount, o.tip_amount,
         o.distance, o.latitude, o.longitude, o.location, o.address, o.area_code,
         o.remark, o.created_at, o.state, o.service_start_time, o.service_end_time,
         mu.id, mu.nickname, mu.mobile, cu.id, cu.level, cu.work_status,
         cu.formal_photo, cu.score, cir.id, cir.nickname, cir.mobile, cir.avatar,
         p.id, p.title, p.price, p.duration, p.cover, sa.id, sa.shop_name,
         ara.id, ara.real_name;