-- 创建科技部老师业绩流水视图
-- 包含所有业务类型的流水记录，并包含对应的信息表ID和品项次数

CREATE OR REPLACE VIEW v_tech_teacher_flow AS
SELECT 
    teacher_id,
    teacher_name,
    teacher_account,
    business_type,
    achievement,
    labor_cost,
    project_count,
    customer_id,
    customer_name,
    business_date,
    source_id,
    source_table,
    item_count
FROM (
    -- 开卡流水
    SELECT 
        k.kjbls as teacher_id,
        k.kjblsxm as teacher_name,
        k.kjblszh as teacher_account,
        '开卡' as business_type,
        CAST(COALESCE(k.kjblsyj, 0) AS DECIMAL(10,2)) as achievement,
        CAST(COALESCE(k.F_LaborCost, 0) AS DECIMAL(10,2)) as labor_cost,
        CAST(COALESCE(pm.F_ProjectNumber, 0) AS DECIMAL(10,2)) as project_count,
        o.kdhy as customer_id,
        o.kdhyc as customer_name,
        k.yjsj as business_date,
        k.F_kdpxid as source_id,
        'lq_kd_pxmx' as source_table,
        COALESCE(pm.F_ProjectNumber, 0) as item_count
    FROM lq_kd_kjbsyj k
    LEFT JOIN lq_kd_kdjlb o ON k.glkdbh = o.F_Id
    LEFT JOIN lq_kd_pxmx pm ON k.F_kdpxid = pm.F_Id
    WHERE k.kjbls IS NOT NULL AND k.kjblsxm IS NOT NULL

    UNION ALL

    -- 耗卡流水
    SELECT 
        x.kjbls as teacher_id,
        x.kjblsxm as teacher_name,
        x.kjblszh as teacher_account,
        '耗卡' as business_type,
        CAST(COALESCE(x.kjblsyj, 0) AS DECIMAL(10,2)) as achievement,
        CAST(COALESCE(x.F_LaborCost, 0) AS DECIMAL(10,2)) as labor_cost,
        CAST(COALESCE(pm.F_ProjectNumber, 0) AS DECIMAL(10,2)) as project_count,
        h.hy as customer_id,
        h.hymc as customer_name,
        x.yjsj as business_date,
        x.F_hkpxid as source_id,
        'lq_xh_pxmx' as source_table,
        COALESCE(pm.F_ProjectNumber, 0) as item_count
    FROM lq_xh_kjbsyj x
    LEFT JOIN lq_xh_hyhk h ON x.glkdbh = h.F_Id
    LEFT JOIN lq_xh_pxmx pm ON x.F_hkpxid = pm.F_Id
    WHERE x.kjbls IS NOT NULL AND x.kjblsxm IS NOT NULL

    UNION ALL

    -- 退卡流水
    SELECT 
        r.kjbls as teacher_id,
        r.kjblsxm as teacher_name,
        r.kjblszh as teacher_account,
        '退卡' as business_type,
        CAST(COALESCE(r.kjblsyj, 0) AS DECIMAL(10,2)) as achievement,
        CAST(COALESCE(r.F_LaborCost, 0) AS DECIMAL(10,2)) as labor_cost,
        CAST(COALESCE(r.F_tkpxNumber, 0) AS DECIMAL(10,2)) as project_count,
        t.hy as customer_id,
        t.hymc as customer_name,
        r.tksj as business_date,
        r.F_Id as source_id,
        'lq_hytk_kjbsyj' as source_table,
        0 as item_count
    FROM lq_hytk_kjbsyj r
    LEFT JOIN lq_hytk_hytk t ON r.gltkbh = t.F_Id
    WHERE r.kjbls IS NOT NULL AND r.kjblsxm IS NOT NULL AND (r.F_DeleteMark IS NULL OR r.F_DeleteMark = 0)
) t;