-- ============================================================
-- 科美业绩差异：剔除「储扣一致」后的真实差异开单
-- 时间范围：2026-01-01 ~ 2026-01-22（与台账一致）
-- ============================================================
--
-- 【剔除规则】
-- 若 品项明细金额 - 科技部业绩 ≈ 该开单的储扣金额（全部储扣，不限于科美，误差<=0.02），
-- 视为储扣导致，从结果中剔除；只列出「剔除后仍存在差异」的开单。
--
-- 【本脚本包含两个查询】
-- ① 有差异的开单列表：只输出 开单ID、开单日期，用于把有差异的开单列出来；
-- ② 有差异的开单明细：输出 开单ID、开单日期、品项明细金额、科技部业绩金额、储扣金额_全部、差异，用于分析。
--
-- 【查询②结果列】开单ID | 开单日期 | 品项明细金额 | 科技部业绩金额 | 储扣金额_全部 | 差异
--
-- ============================================================

-- 一、有差异的开单列表（仅列开单ID与日期，便于核对）
SELECT
    kd.F_Id AS 开单ID,
    kd.kdrq 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 = '科美'
  AND kd.kdrq >= '2026-01-01 00:00:00'
  AND kd.kdrq <= '2026-01-22 23:59:59'
GROUP BY kd.F_Id, kd.kdrq
HAVING ABS(SUM(px.F_ActualPrice) - (SELECT COALESCE(SUM(CAST(NULLIF(TRIM(k2.kjblsyj), '') AS DECIMAL(18,2))), 0)
                                    FROM lq_kd_kjbsyj k2
                                    WHERE k2.glkdbh = kd.F_Id AND k2.F_IsEffective = 1 AND k2.F_ItemCategory = '科美'
                                      AND k2.yjsj >= '2026-01-01 00:00:00' AND k2.yjsj <= '2026-01-22 23:59:59')) > 0.01
   AND ABS((SUM(px.F_ActualPrice) - (SELECT COALESCE(SUM(CAST(NULLIF(TRIM(k2.kjblsyj), '') AS DECIMAL(18,2))), 0)
                                    FROM lq_kd_kjbsyj k2
                                    WHERE k2.glkdbh = kd.F_Id AND k2.F_IsEffective = 1 AND k2.F_ItemCategory = '科美'
                                      AND k2.yjsj >= '2026-01-01 00:00:00' AND k2.yjsj <= '2026-01-22 23:59:59'))
           - COALESCE((SELECT SUM(d.F_Amount) FROM lq_kd_deductinfo d
                       WHERE d.F_BillingId = kd.F_Id AND d.F_IsEffective = 1), 0)) > 0.02
ORDER BY kd.kdrq;

-- 二、有差异的开单明细（含金额与储扣，便于分析）
SELECT
    kd.F_Id AS 开单ID,
    kd.kdrq AS 开单日期,
    ROUND(SUM(px.F_ActualPrice), 2) AS 品项明细金额,
    ROUND((SELECT COALESCE(SUM(CAST(NULLIF(TRIM(k2.kjblsyj), '') AS DECIMAL(18,2))), 0)
           FROM lq_kd_kjbsyj k2
           WHERE k2.glkdbh = kd.F_Id AND k2.F_IsEffective = 1 AND k2.F_ItemCategory = '科美'
             AND k2.yjsj >= '2026-01-01 00:00:00' AND k2.yjsj <= '2026-01-22 23:59:59'), 2) AS 科技部业绩金额,
    ROUND(COALESCE((SELECT SUM(d.F_Amount) FROM lq_kd_deductinfo d
                    WHERE d.F_BillingId = kd.F_Id AND d.F_IsEffective = 1), 0), 2) AS 储扣金额_全部,
    ROUND(SUM(px.F_ActualPrice) - (SELECT COALESCE(SUM(CAST(NULLIF(TRIM(k2.kjblsyj), '') AS DECIMAL(18,2))), 0)
                                  FROM lq_kd_kjbsyj k2
                                  WHERE k2.glkdbh = kd.F_Id AND k2.F_IsEffective = 1 AND k2.F_ItemCategory = '科美'
                                    AND k2.yjsj >= '2026-01-01 00:00:00' AND k2.yjsj <= '2026-01-22 23:59:59'), 2) 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 = '科美'
  AND kd.kdrq >= '2026-01-01 00:00:00'
  AND kd.kdrq <= '2026-01-22 23:59:59'
GROUP BY kd.F_Id, kd.kdrq
HAVING ABS(SUM(px.F_ActualPrice) - (SELECT COALESCE(SUM(CAST(NULLIF(TRIM(k2.kjblsyj), '') AS DECIMAL(18,2))), 0)
                                    FROM lq_kd_kjbsyj k2
                                    WHERE k2.glkdbh = kd.F_Id AND k2.F_IsEffective = 1 AND k2.F_ItemCategory = '科美'
                                      AND k2.yjsj >= '2026-01-01 00:00:00' AND k2.yjsj <= '2026-01-22 23:59:59')) > 0.01
   AND ABS((SUM(px.F_ActualPrice) - (SELECT COALESCE(SUM(CAST(NULLIF(TRIM(k2.kjblsyj), '') AS DECIMAL(18,2))), 0)
                                    FROM lq_kd_kjbsyj k2
                                    WHERE k2.glkdbh = kd.F_Id AND k2.F_IsEffective = 1 AND k2.F_ItemCategory = '科美'
                                      AND k2.yjsj >= '2026-01-01 00:00:00' AND k2.yjsj <= '2026-01-22 23:59:59'))
           - COALESCE((SELECT SUM(d.F_Amount) FROM lq_kd_deductinfo d
                       WHERE d.F_BillingId = kd.F_Id AND d.F_IsEffective = 1), 0)) > 0.02
ORDER BY kd.kdrq;