-- ============================================
-- 排查生美业绩统计差异详细分析
-- ============================================
-- 问题：品项明细表统计生美数据是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 ORDER BY 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,
    dept.F_FullName 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 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 target.F_EducationDepartment, dept.F_FullName;

-- ============================================
-- 4. 检查是否有门店在lq_md_target表中有多条记录，导致重复统计
-- ============================================
SELECT 
    billing.djmd as 门店ID,
    md.Dm as 门店名称,
    COUNT(DISTINCT target.F_Id) as 目标表记录数,
    COUNT(*) as 开单记录数,
    COALESCE(SUM(pxmx.F_ActualPrice), 0) as 生美业绩总额,
    GROUP_CONCAT(DISTINCT target.F_EducationDepartment ORDER BY 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')
LEFT JOIN lq_mdxx md ON billing.djmd = md.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
HAVING COUNT(DISTINCT target.F_Id) > 1;

-- ============================================
-- 5. 检查是否有生美品项的开单记录，但门店在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 = '');

-- ============================================
-- 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 生美业绩,
    COUNT(DISTINCT target.F_Id) 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;

-- ============================================
-- 7. 检查是否有门店在lq_md_target表中有多条记录（不同月份，但查询时可能有问题）
-- ============================================
SELECT 
    F_StoreId,
    COUNT(DISTINCT F_Month) as 月份数,
    GROUP_CONCAT(DISTINCT F_Month ORDER BY F_Month) as 月份列表,
    COUNT(*) as 总记录数
FROM lq_md_target
WHERE F_StoreId IN (
    SELECT DISTINCT billing.djmd
    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)
)
    AND (F_EducationDepartment IS NOT NULL AND F_EducationDepartment != '')
GROUP BY F_StoreId
HAVING COUNT(*) > 1;