-- ============================================
-- 检查生美业绩统计差异
-- ============================================
-- 问题：品项明细表统计生美数据是1869781.81，但日报天王团统计教育一部+教育二部合计是1876061.21，差异6279.40
-- 
-- 可能原因：
-- 1. 门店在lq_md_target表中有重复记录（同一月份多条记录）
-- 2. 统计范围不一致（时间范围或门店范围）
-- 3. 数据关联逻辑问题

-- ============================================
-- 1. 检查lq_md_target表中是否有重复记录（同一门店同一月份多条记录）
-- ============================================
SELECT 
    F_StoreId,
    F_Month,
    COUNT(*) as record_count,
    GROUP_CONCAT(DISTINCT F_EducationDepartment) as education_depts
FROM lq_md_target
WHERE F_Month = DATE_FORMAT(NOW(), '%Y%m')
    AND (F_EducationDepartment IS NOT NULL AND F_EducationDepartment != '')
GROUP BY F_StoreId, F_Month
HAVING COUNT(*) > 1;

-- ============================================
-- 2. 检查品项明细表统计生美业绩（所有门店，不限制部门归属）
-- ============================================
SELECT 
    '品项明细表统计' AS 统计来源,
    COALESCE(SUM(pxmx.F_ActualPrice), 0) as 生美业绩总额
FROM lq_kd_pxmx pxmx
INNER JOIN lq_kd_kdjlb billing ON pxmx.glkdbh = billing.F_Id
INNER JOIN lq_xmzl item ON pxmx.px = item.F_Id
WHERE pxmx.F_IsEffective = 1
    AND billing.F_IsEffective = 1
    AND item.F_IsEffective = 1
    AND item.qt2 = '生美'
    AND billing.kdrq >= DATE_FORMAT(NOW(), '%Y-%m-01') 
    AND billing.kdrq < DATE_ADD(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL 1 MONTH);

-- ============================================
-- 3. 检查日报天王团统计生美业绩（只统计有部门归属的门店，按部门分组）
-- ============================================
SELECT 
    '日报天王团统计' AS 统计来源,
    target.F_EducationDepartment as 部门ID,
    COALESCE(SUM(pxmx.F_ActualPrice), 0) as 生美业绩
FROM lq_kd_pxmx pxmx
INNER JOIN lq_kd_kdjlb billing ON pxmx.glkdbh = billing.F_Id
INNER JOIN lq_xmzl item ON pxmx.px = item.F_Id
INNER JOIN lq_md_target target ON billing.djmd = target.F_StoreId 
    AND target.F_Month = DATE_FORMAT(NOW(), '%Y%m')
WHERE pxmx.F_IsEffective = 1
    AND billing.F_IsEffective = 1
    AND item.F_IsEffective = 1
    AND item.qt2 = '生美'
    AND billing.kdrq >= DATE_FORMAT(NOW(), '%Y-%m-01') 
    AND billing.kdrq < DATE_ADD(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL 1 MONTH)
    AND target.F_EducationDepartment IS NOT NULL
    AND target.F_EducationDepartment != ''
GROUP BY target.F_EducationDepartment;

-- ============================================
-- 4. 检查是否有生美品项的开单记录，但门店在lq_md_target表中没有设置F_EducationDepartment
-- ============================================
SELECT 
    '未归属门店的生美业绩' AS 统计来源,
    COUNT(DISTINCT billing.djmd) as 门店数量,
    COUNT(*) as 开单记录数,
    COALESCE(SUM(pxmx.F_ActualPrice), 0) as 生美业绩总额
FROM lq_kd_pxmx pxmx
INNER JOIN lq_kd_kdjlb billing ON pxmx.glkdbh = billing.F_Id
INNER JOIN lq_xmzl item ON pxmx.px = item.F_Id
LEFT JOIN lq_md_target target ON billing.djmd = target.F_StoreId 
    AND target.F_Month = DATE_FORMAT(NOW(), '%Y%m')
WHERE pxmx.F_IsEffective = 1
    AND billing.F_IsEffective = 1
    AND item.F_IsEffective = 1
    AND item.qt2 = '生美'
    AND billing.kdrq >= DATE_FORMAT(NOW(), '%Y-%m-01') 
    AND billing.kdrq < DATE_ADD(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL 1 MONTH)
    AND (target.F_StoreId IS NULL 
         OR target.F_EducationDepartment IS NULL 
         OR target.F_EducationDepartment = '');

-- ============================================
-- 5. 检查是否有门店在lq_md_target表中有多条记录（可能导致重复统计）
-- ============================================
SELECT 
    billing.djmd as 门店ID,
    COUNT(DISTINCT target.F_Id) as 目标表记录数,
    COUNT(*) as 开单记录数,
    COALESCE(SUM(pxmx.F_ActualPrice), 0) as 生美业绩总额,
    GROUP_CONCAT(DISTINCT target.F_EducationDepartment) as 教育部门列表
FROM lq_kd_pxmx pxmx
INNER JOIN lq_kd_kdjlb billing ON pxmx.glkdbh = billing.F_Id
INNER JOIN lq_xmzl item ON pxmx.px = item.F_Id
INNER JOIN lq_md_target target ON billing.djmd = target.F_StoreId 
    AND target.F_Month = DATE_FORMAT(NOW(), '%Y%m')
WHERE pxmx.F_IsEffective = 1
    AND billing.F_IsEffective = 1
    AND item.F_IsEffective = 1
    AND item.qt2 = '生美'
    AND billing.kdrq >= DATE_FORMAT(NOW(), '%Y-%m-01') 
    AND billing.kdrq < DATE_ADD(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL 1 MONTH)
    AND target.F_EducationDepartment IS NOT NULL
    AND target.F_EducationDepartment != ''
GROUP BY billing.djmd
HAVING COUNT(DISTINCT target.F_Id) > 1;

-- ============================================
-- 6. 详细检查：查看每个门店的生美业绩和部门归属情况
-- ============================================
SELECT 
    billing.djmd as 门店ID,
    md.Dm as 门店名称,
    target.F_EducationDepartment as 教育部门ID,
    dept.F_FullName as 教育部门名称,
    COUNT(*) as 开单记录数,
    COALESCE(SUM(pxmx.F_ActualPrice), 0) as 生美业绩
FROM lq_kd_pxmx pxmx
INNER JOIN lq_kd_kdjlb billing ON pxmx.glkdbh = billing.F_Id
INNER JOIN lq_xmzl item ON pxmx.px = item.F_Id
INNER JOIN lq_md_target target ON billing.djmd = target.F_StoreId 
    AND target.F_Month = DATE_FORMAT(NOW(), '%Y%m')
LEFT JOIN lq_mdxx md ON billing.djmd = md.F_Id
LEFT JOIN base_organize dept ON target.F_EducationDepartment = dept.F_Id
WHERE pxmx.F_IsEffective = 1
    AND billing.F_IsEffective = 1
    AND item.F_IsEffective = 1
    AND item.qt2 = '生美'
    AND billing.kdrq >= DATE_FORMAT(NOW(), '%Y-%m-01') 
    AND billing.kdrq < DATE_ADD(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL 1 MONTH)
    AND target.F_EducationDepartment IS NOT NULL
    AND target.F_EducationDepartment != ''
GROUP BY billing.djmd, md.Dm, target.F_EducationDepartment, dept.F_FullName
ORDER BY 生美业绩 DESC;