-- =============================================================================
-- 与后端 LqMonthlyEmployeeFactHelper / LqMonthlyEmployeeFactQueryService 口径一致：
-- 统计某自然月内在考勤、业绩、消耗、金三角、各岗位工资归档等任一来源出现过的员工（BASE_USER.F_Id）。
-- 用途：DBA 核对、报表、或在不调用 API 时直接查库。
-- 使用：修改下方 @stat_year、@stat_month 后整段执行（MySQL 5.7+ / 8.0+）。
-- 说明：jkszh/kjblszh 等与 F_ACCOUNT 或 F_Id 匹配；仅排除 BASE_USER 软删（F_DeleteMark IS NULL）。
--       不新建表、不改表结构。
-- =============================================================================

SET @stat_year := 2026;
SET @stat_month := 3;
SET @d0 := STR_TO_DATE(CONCAT(@stat_year, '-', LPAD(@stat_month, 2, '0'), '-01'), '%Y-%m-%d');
SET @d1 := LAST_DAY(@d0);
SET @d1_open := DATE_ADD(@d1, INTERVAL 1 DAY);
SET @month_str := DATE_FORMAT(@d0, '%Y%m');

SELECT DISTINCT u.F_Id AS user_id,
                u.F_REALNAME AS real_name,
                u.F_ACCOUNT AS account,
                u.F_GW AS gw,
                u.F_ENABLEDMARK AS enabled_mark,
                u.F_IsOnJob AS is_on_job
FROM (
         SELECT F_UserId AS k FROM lq_attendance_summary
         WHERE F_Year = @stat_year AND F_Month = @stat_month AND F_IsEffective = 1
         UNION
         SELECT F_EmployeeId FROM lq_employee_store_assignment
         WHERE F_IsEffective = 1 AND F_StartDate <= @d1 AND (F_EndDate IS NULL OR F_EndDate >= @d0)
         UNION
         SELECT F_UserId FROM lq_attendance_record
         WHERE F_AttendanceDate >= @d0 AND F_AttendanceDate <= @d1 AND F_IsEffective = 1
         UNION
         SELECT F_PersonId FROM lq_person_times_record
         WHERE F_WorkMonth = @month_str AND F_IsEffective = 1
         UNION
         SELECT user_id FROM lq_jinsanjiao_user
         WHERE F_Month = @month_str AND F_DeleteMark = 0
         UNION
         SELECT jkszh FROM lq_kd_jksyj
         WHERE yjsj >= @d0 AND yjsj < @d1_open AND F_IsEffective = 1 AND jkszh IS NOT NULL AND jkszh <> ''
         UNION
         SELECT jkszh FROM lq_xh_jksyj
         WHERE yjsj >= @d0 AND yjsj < @d1_open AND F_IsEffective = 1 AND jkszh IS NOT NULL AND jkszh <> ''
         UNION
         SELECT jkszh FROM lq_hytk_jksyj
         WHERE tksj >= @d0 AND tksj < @d1_open AND F_IsEffective = 1 AND jkszh IS NOT NULL AND jkszh <> ''
         UNION
         SELECT kjblszh FROM lq_kd_kjbsyj
         WHERE yjsj >= @d0 AND yjsj < @d1_open AND F_IsEffective = 1 AND kjblszh IS NOT NULL AND kjblszh <> ''
         UNION
         SELECT k.kjblszh
         FROM lq_xh_kjbsyj k
                  INNER JOIN lq_xh_hyhk h ON k.glkdbh = h.F_Id AND h.F_IsEffective = 1
         WHERE k.F_IsEffective = 1 AND h.Hksj >= @d0 AND h.Hksj < @d1_open
           AND k.kjblszh IS NOT NULL AND k.kjblszh <> ''
         UNION
         SELECT kjblszh FROM lq_hytk_kjbsyj
         WHERE tksj >= @d0 AND tksj < @d1_open AND F_IsEffective = 1 AND kjblszh IS NOT NULL AND kjblszh <> ''
         UNION
         SELECT F_EmployeeId FROM lq_salary_statistics WHERE F_StatisticsMonth = @month_str
         UNION
         SELECT F_EmployeeId FROM lq_tech_teacher_salary_statistics WHERE F_StatisticsMonth = @month_str
         UNION
         SELECT F_EmployeeId FROM lq_tech_general_manager_salary_statistics WHERE F_StatisticsMonth = @month_str
         UNION
         SELECT F_EmployeeId FROM lq_director_salary_statistics WHERE F_StatisticsMonth = @month_str
         UNION
         SELECT F_EmployeeId FROM lq_store_manager_salary_statistics WHERE F_StatisticsMonth = @month_str
         UNION
         SELECT F_EmployeeId FROM lq_assistant_salary_statistics WHERE F_StatisticsMonth = @month_str
         UNION
         SELECT F_EmployeeId FROM lq_major_project_director_salary_statistics WHERE F_StatisticsMonth = @month_str
         UNION
         SELECT F_EmployeeId FROM lq_major_project_teacher_salary_statistics WHERE F_StatisticsMonth = @month_str
         UNION
         SELECT F_EmployeeId FROM lq_business_unit_manager_salary_statistics WHERE F_StatisticsMonth = @month_str
     ) raw
         INNER JOIN BASE_USER u ON (u.F_Id = raw.k OR u.F_ACCOUNT = raw.k)
WHERE u.F_DeleteMark IS NULL
ORDER BY u.F_REALNAME;