-- ============================================================
-- 科美业绩(1163803) vs 科技部老师开单业绩(1165702.63) 差异核对
-- 理论上两者应相等，差异约 1899.63。本脚本用于排查原因，不修改任何数据。
-- ============================================================

-- 1) 口径说明
-- 科美业绩：一般指「开单品项明细」中 品项分类=科美 的 F_ActualPrice 之和（或来自台账选科美时的汇总）
-- 科技部老师开单业绩：lq_kd_kjbsyj 表的 kjblsyj 之和

-- 2) 同一时间范围下两数分别多少（请按需修改时间）
SET @start = '2026-01-01 00:00:00';
SET @end   = '2026-01-31 23:59:59';

-- 2.1 科美业绩：按开单品项明细，品项分类=科美，有效开单+有效明细
SELECT 
    '科美业绩-按品项明细(pxmx.F_ItemCategory=科美)' AS 口径,
    COALESCE(SUM(px.F_ActualPrice), 0) AS 金额
FROM lq_kd_pxmx px
INNER JOIN lq_kd_kdjlb kd ON px.glkdbh = kd.F_Id AND kd.F_IsEffective = 1
WHERE px.F_IsEffective = 1
  AND (px.F_ItemCategory = '科美' OR EXISTS (SELECT 1 FROM lq_xmzl zl WHERE zl.F_Id = px.px AND zl.F_IsEffective = 1 AND zl.qt2 = '科美'))
  AND kd.kdrq >= @start
  AND kd.kdrq <= @end;

-- 2.2 科技部老师开单业绩：lq_kd_kjbsyj 全表汇总（当前系统统计方式，未过滤科美）
SELECT 
    '科技部老师开单业绩-全表kjblsyj' AS 口径,
    COALESCE(SUM(CAST(NULLIF(TRIM(k.kjblsyj), '') AS DECIMAL(18,2))), 0) AS 金额
FROM lq_kd_kjbsyj k
INNER JOIN lq_kd_kdjlb kd ON k.glkdbh = kd.F_Id AND kd.F_IsEffective = 1
WHERE k.F_IsEffective = 1
  AND k.yjsj >= @start
  AND k.yjsj <= @end
  AND k.kjblsyj IS NOT NULL
  AND TRIM(k.kjblsyj) != ''
  AND TRIM(k.kjblsyj) != '0';

-- 2.3 科技部老师开单业绩：仅 F_ItemCategory='科美'（若只统计科美，应与科美业绩一致）
SELECT 
    '科技部老师开单业绩-仅科美F_ItemCategory' AS 口径,
    COALESCE(SUM(CAST(NULLIF(TRIM(k.kjblsyj), '') AS DECIMAL(18,2))), 0) AS 金额
FROM lq_kd_kjbsyj k
INNER JOIN lq_kd_kdjlb kd ON k.glkdbh = kd.F_Id AND kd.F_IsEffective = 1
WHERE k.F_IsEffective = 1
  AND k.F_ItemCategory = '科美'
  AND k.yjsj >= @start
  AND k.yjsj <= @end
  AND k.kjblsyj IS NOT NULL
  AND TRIM(k.kjblsyj) != ''
  AND TRIM(k.kjblsyj) != '0';

-- 3) 差异来源：科技部表里「非科美」或「品项分类为空」的金额（多出来的部分）
SELECT 
    '科技部-非科美或空分类金额' AS 口径,
    COALESCE(SUM(CAST(NULLIF(TRIM(k.kjblsyj), '') AS DECIMAL(18,2))), 0) AS 金额
FROM lq_kd_kjbsyj k
INNER JOIN lq_kd_kdjlb kd ON k.glkdbh = kd.F_Id AND kd.F_IsEffective = 1
WHERE k.F_IsEffective = 1
  AND k.yjsj >= @start
  AND k.yjsj <= @end
  AND (k.F_ItemCategory IS NULL OR k.F_ItemCategory = '' OR k.F_ItemCategory != '科美')
  AND k.kjblsyj IS NOT NULL
  AND TRIM(k.kjblsyj) != ''
  AND TRIM(k.kjblsyj) != '0';

-- 4) 科美品项有明细但科技部无业绩 / 科技部有业绩但品项非科美（明细级抽查）
-- 4.1 科美品项明细对应的科技部业绩合计（按开单明细ID关联）
SELECT 
    '按开单明细关联-科美品项对应kjbsyj之和' AS 口径,
    COALESCE(SUM(CAST(NULLIF(TRIM(k.kjblsyj), '') AS DECIMAL(18,2))), 0) AS 金额
FROM lq_kd_pxmx px
INNER JOIN lq_kd_kdjlb kd ON px.glkdbh = kd.F_Id AND kd.F_IsEffective = 1
LEFT JOIN lq_kd_kjbsyj k ON k.F_kdpxid = px.F_Id AND k.F_IsEffective = 1
WHERE px.F_IsEffective = 1
  AND px.F_ItemCategory = '科美'
  AND kd.kdrq >= @start
  AND kd.kdrq <= @end
  AND k.kjblsyj IS NOT NULL
  AND TRIM(k.kjblsyj) != ''
  AND TRIM(k.kjblsyj) != '0';

-- 4.2 科美品项明细的 ActualPrice 之和（与 4.1 同范围，用于对比）
SELECT 
    '科美品项ActualPrice之和(同范围)' AS 口径,
    COALESCE(SUM(px.F_ActualPrice), 0) AS 金额
FROM lq_kd_pxmx px
INNER JOIN lq_kd_kdjlb kd ON px.glkdbh = kd.F_Id AND kd.F_IsEffective = 1
WHERE px.F_IsEffective = 1
  AND (px.F_ItemCategory = '科美' OR EXISTS (SELECT 1 FROM lq_xmzl zl WHERE zl.F_Id = px.px AND zl.F_IsEffective = 1 AND zl.qt2 = '科美'))
  AND kd.kdrq >= @start
  AND kd.kdrq <= @end;

-- 5) 查看 lq_kd_kjbsyj 中 F_ItemCategory 的分布（是否有空或非科美）
SELECT 
    COALESCE(k.F_ItemCategory, '(空)') AS 品项分类,
    COUNT(*) AS 条数,
    COALESCE(SUM(CAST(NULLIF(TRIM(k.kjblsyj), '') AS DECIMAL(18,2))), 0) AS 金额合计
FROM lq_kd_kjbsyj k
INNER JOIN lq_kd_kdjlb kd ON k.glkdbh = kd.F_Id AND kd.F_IsEffective = 1
WHERE k.F_IsEffective = 1
  AND k.yjsj >= @start
  AND k.yjsj <= @end
  AND k.kjblsyj IS NOT NULL
  AND TRIM(k.kjblsyj) != ''
  AND TRIM(k.kjblsyj) != '0'
GROUP BY COALESCE(k.F_ItemCategory, '(空)');