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再与用户表关联查询出技师名称

先不要了没设计： 营销类型  砍价减免金额  打赏金额  悬赏金额  跟单客服是系统表里的
*/
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 /* 订单编号 */,
    p.title /* 项目名称 */,
    mu.nickname /* 用户名称 */,
    o.created_at /* 下单时间 */,
    o.total_amount /* 支付金额 */,
    os_state.state /* 服务状态 */,
    CASE o.order_source
        WHEN 'PLATFORM' THEN CONCAT('技师-', coach_mu.nickname)
        ELSE CONCAT('店铺-', sa.shop_name)
    END /* 技师/店铺 */,
    os_accept.created_at /* 技师/店铺接单时间 */,
    o.area_code /* 区域 */,
    o.address /* 地址 */,
    o.remark /* 备注 */,
    o.order_source /* 订单来源 */,
    os_arrive.created_at /* 到达时间 */,
    TIMESTAMPDIFF(
        MINUTE,
        NOW(),
        os_arrive.created_at
    ) /* 订单倒计时 */,
    os_start.created_at /* 服务开始时间 */,
    mc_platform.discount_value /* 平台优惠券 */,
    cp.discount_amount /* 技师减免 */,
    mc_shop.discount_value /* 商铺代金券 */,
    wpr.payment_method /* 支付方式 */,
    o.traffic_amount /* 路费 */,
    o.order_type /* 订单类型 */,
    os_user_leave.created_at /* 用户确认撤离时间 */,
    os_coach_leave.created_at /* 技师确认离开时间 */
FROM
    `order` o
    LEFT JOIN project p ON o.service_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.service_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
    AND mc_platform.coupon_type = 'PLATFORM'
    /* 关联优惠券记录和优惠券表-店铺券 */
    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
    AND mc_shop.coupon_type = 'SHOP'
    /* 关联订单状态-当前状态 */
    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 = 'ACCEPTED'
    AND os_accept.rn = 1
    /* 关联订单状态-到达时间 */
    LEFT JOIN order_status os_arrive ON o.id = os_arrive.order_id
    AND os_arrive.state = 'ARRIVED'
    AND os_arrive.rn = 1
    /* 关联订单状态-开始服务时间 */
    LEFT JOIN order_status os_start ON o.id = os_start.order_id
    AND os_start.state = 'STARTED'
    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 = 'USER_CONFIRMED_LEAVE'
    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 = 'COACH_CONFIRMED_LEAVE'
    AND os_coach_leave.rn = 1
WHERE
    o.deleted_at IS NULL
ORDER BY o.created_at DESC;