-- 创建开单详细记录视图（简化版）
-- 功能：联合开单记录表、开单品项明细表、项目资料表，呈现开单详细记录
-- 包含：开单编号、开单会员、会员手机号、开单时间、开单品项、项目数量、项目单价、项目总价、来源类型、项目分类3

CREATE VIEW v_order_detail_simple AS
SELECT 
    kd.F_Id AS order_id,                    -- 开单编号
    kd.kdhy AS order_member,                -- 开单会员
    kd.kdhysjh AS member_phone,             -- 会员手机号
    kd.kdrq AS order_time,                  -- 开单时间
    px.px AS item_code,                     -- 开单品项（项目编号）
    px.pxmc AS item_name,                   -- 品项名称
    COALESCE(px.F_ProjectNumber, 1) AS project_count,  -- 项目数量（默认为1）
    px.pxjg AS project_unit_price,          -- 项目单价
    (COALESCE(px.F_ProjectNumber, 1) * px.pxjg) AS project_total_price,  -- 项目总价（数量*单价）
    px.F_SourceType AS source_type,         -- 来源类型
    xm.fl3 AS project_category3             -- 项目分类3

FROM 
    lq_kd_kdjlb kd
    LEFT JOIN lq_kd_pxmx px ON kd.F_Id = px.glkdbh
    LEFT JOIN lq_xmzl xm ON px.px = xm.F_Id
WHERE 
    kd.F_Id IS NOT NULL
    AND px.F_Id IS NOT NULL  -- 只显示有品项明细的开单
ORDER BY 
    kd.kdrq DESC, 
    kd.F_Id, 
    px.F_Id;

-- 创建相关索引以优化查询性能
CREATE INDEX IF NOT EXISTS idx_lq_kd_pxmx_glkdbh ON lq_kd_pxmx(glkdbh);
CREATE INDEX IF NOT EXISTS idx_lq_kd_pxmx_px ON lq_kd_pxmx(px);
CREATE INDEX IF NOT EXISTS idx_lq_xmzl_fl3 ON lq_xmzl(fl3);
CREATE INDEX IF NOT EXISTS idx_lq_kd_kdjlb_kdrq ON lq_kd_kdjlb(kdrq);

-- 使用示例查询：
-- 1. 查询所有开单详细记录
-- SELECT * FROM v_order_detail_simple ORDER BY order_time DESC;

-- 2. 查询指定开单编号的详细记录
-- SELECT * FROM v_order_detail_simple WHERE order_id = '具体开单编号';

-- 3. 查询指定会员的开单记录
-- SELECT * FROM v_order_detail_simple WHERE order_member = '具体会员ID' ORDER BY order_time DESC;

-- 4. 查询指定分类3的开单记录
-- SELECT * FROM v_order_detail_simple WHERE project_category3 = '具体分类3名称' ORDER BY order_time DESC;

-- 5. 查询指定来源类型的开单记录
-- SELECT * FROM v_order_detail_simple WHERE source_type = '具体来源类型' ORDER BY order_time DESC;

-- 6. 查询指定日期范围的开单记录
-- SELECT * FROM v_order_detail_simple 
-- WHERE order_time >= '2024-01-01' AND order_time <= '2024-12-31' 
-- ORDER BY order_time DESC;

-- 7. 按分类3统计开单数量和总金额
-- SELECT 
--     project_category3,
--     COUNT(*) AS record_count,
--     SUM(project_total_price) AS total_amount
-- FROM v_order_detail_simple 
-- WHERE project_category3 IS NOT NULL
-- GROUP BY project_category3 
-- ORDER BY total_amount DESC;

-- 8. 按来源类型统计开单数量和总金额
-- SELECT 
--     source_type,
--     COUNT(*) AS record_count,
--     SUM(project_total_price) AS total_amount
-- FROM v_order_detail_simple 
-- WHERE source_type IS NOT NULL
-- GROUP BY source_type 
-- ORDER BY total_amount DESC;

-- 9. 查询项目总价大于指定金额的记录
-- SELECT * FROM v_order_detail_simple WHERE project_total_price > 1000 ORDER BY project_total_price DESC;

-- 10. 按会员统计开单数量和总金额
-- SELECT 
--     order_member,
--     member_phone,
--     COUNT(*) AS order_count,
--     SUM(project_total_price) AS total_amount
-- FROM v_order_detail_simple 
-- GROUP BY order_member, member_phone 
-- ORDER BY total_amount DESC;