🔹 total_result_location

Bảng dữ liệu liên quan:

Tên hàm PHP sẽ gọi:

Thời gian chạy:
03:00 08:00 12:00 20:00

Ghi chú SQL (nếu cần backup):

REPLACE INTO wp_total_result_location (
    ma_co_so,
    month,
    sum_employee,
    sum_total_work,
    sum_salary,
    sum_thu_viec,
    sum_nghi_viec,
    sum_chinh_thuc
)
SELECT 
    s.ma_co_so,
    s.month,
    s.sum_employee,
    COALESCE(w.sum_total_work, 0),
    COALESCE(l.sum_salary, 0),
    s.sum_thu_viec,
    s.sum_nghi_viec,
    s.sum_chinh_thuc
FROM (
    -- Tổng hợp số lượng nhân sự theo trạng thái
    SELECT 
        ma_co_so, 
        DATE_FORMAT(start_date, '%Y-%m') AS month,
        COUNT(DISTINCT user_id) AS sum_employee,
        COUNT(DISTINCT CASE WHEN trang_thai = 'thu_viec' THEN user_id END) AS sum_thu_viec,
        COUNT(DISTINCT CASE WHEN trang_thai = 'nghi_viec' THEN user_id END) AS sum_nghi_viec,
        COUNT(DISTINCT CASE WHEN trang_thai = 'chinh_thuc' THEN user_id END) AS sum_chinh_thuc
    FROM wp_salary_history
    GROUP BY ma_co_so, DATE_FORMAT(start_date, '%Y-%m')
) AS s
LEFT JOIN (
    -- Tổng work (tong_cong_huong_luong + tong_cong_tang_ca_sau_gio)
    SELECT 
        ma_co_so, 
        month,
        SUM(luong + tang_ca) AS sum_total_work
    FROM (
        SELECT 
            user_id,
            ma_co_so,
            DATE_FORMAT(start_date, '%Y-%m') AS month,
            SUM(CASE WHEN ma_thanh_phan = 'tong_cong_huong_luong' THEN gia_tri ELSE 0 END) AS luong,
            SUM(CASE WHEN ma_thanh_phan = 'tong_cong_tang_ca_sau_gio' THEN gia_tri ELSE 0 END) AS tang_ca 
        FROM wp_salary_history
        WHERE ma_thanh_phan IN ('tong_cong_huong_luong', 'tong_cong_tang_ca_sau_gio') 
        GROUP BY user_id, ma_co_so, DATE_FORMAT(start_date, '%Y-%m')
    ) AS sub
    GROUP BY ma_co_so, month
) AS w ON s.ma_co_so = w.ma_co_so AND s.month = w.month
LEFT JOIN (
    -- Tổng thực lĩnh từ bảng lương mới (sử dụng start_date)
    SELECT 
        ma_co_so,
        DATE_FORMAT(start_date, '%Y-%m') AS month,
        SUM(thuc_linh) AS sum_salary
    FROM wp_salary_final
    GROUP BY ma_co_so, DATE_FORMAT(start_date, '%Y-%m')
) AS l ON s.ma_co_so = l.ma_co_so AND s.month = l.month;

Kích hoạt

Lần chạy gần nhất: 2025-04-29 12:00:13

🧩 Danh sách các hàm tự động

🔹 total_result_location

➕ Thêm hàm mới