Stored Procedure های کامل برای مدیریت جداول سیستم حضور و غیاب

DELIMITER $$

CREATE PROCEDURE sp_GetAttendanceDevices(
    IN p_CompanyID INT,
    IN p_IsActive BOOLEAN,
    IN p_DeviceType ENUM('Fingerprint', 'FaceRecognition', 'Card', 'Mobile'),
    IN p_Page INT,
    IN p_PageSize INT,
    OUT p_TotalRecords INT
)
BEGIN
    -- محاسبه رکوردهای کل
    SELECT COUNT(*) INTO p_TotalRecords
    FROM AttendanceDevices 
    WHERE CompanyID = p_CompanyID
    AND (p_IsActive IS NULL OR IsActive = p_IsActive)
    AND (p_DeviceType IS NULL OR DeviceType = p_DeviceType);
    
    -- گرفتن لیست دستگاه‌ها
    SELECT 
        DeviceID,
        DeviceCode,
        DeviceName,
        DeviceType,
        IPAddress,
        Port,
        Location,
        IsActive,
        LastSync,
        CreatedAt,
        DATEDIFF(CURDATE(), LastSync) as DaysSinceLastSync,
        CASE 
            WHEN LastSync IS NULL THEN 'Never'
            WHEN DATEDIFF(CURDATE(), LastSync) = 0 THEN 'Today'
            WHEN DATEDIFF(CURDATE(), LastSync) = 1 THEN 'Yesterday'
            WHEN DATEDIFF(CURDATE(), LastSync) <= 7 THEN 'This Week'
            ELSE 'Older'
        END as SyncStatus
    FROM AttendanceDevices
    WHERE CompanyID = p_CompanyID
    AND (p_IsActive IS NULL OR IsActive = p_IsActive)
    AND (p_DeviceType IS NULL OR DeviceType = p_DeviceType)
    ORDER BY DeviceName
    LIMIT p_PageSize OFFSET (p_Page - 1) * p_PageSize;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_CreateAttendanceDevice(
    IN p_CompanyID INT,
    IN p_DeviceCode VARCHAR(50),
    IN p_DeviceName NVARCHAR(100),
    IN p_DeviceType ENUM('Fingerprint', 'FaceRecognition', 'Card', 'Mobile'),
    IN p_IPAddress VARCHAR(15),
    IN p_Port INT,
    IN p_Location NVARCHAR(200),
    OUT p_DeviceID INT,
    OUT p_ResultMessage VARCHAR(500)
)
BEGIN
    DECLARE v_Count INT DEFAULT 0;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ResultMessage = 'خطا در ایجاد دستگاه';
        SET p_DeviceID = -1;
    END;
    
    START TRANSACTION;
    
    -- بررسی تکراری نبودن کد دستگاه
    SELECT COUNT(*) INTO v_Count 
    FROM AttendanceDevices 
    WHERE CompanyID = p_CompanyID AND DeviceCode = p_DeviceCode;
    
    IF v_Count > 0 THEN
        SET p_ResultMessage = 'کد دستگاه تکراری است';
        SET p_DeviceID = -1;
        ROLLBACK;
    ELSE
        -- ایجاد دستگاه
        INSERT INTO AttendanceDevices (
            CompanyID, DeviceCode, DeviceName, DeviceType,
            IPAddress, Port, Location
        ) VALUES (
            p_CompanyID, p_DeviceCode, p_DeviceName, p_DeviceType,
            p_IPAddress, p_Port, p_Location
        );
        
        SET p_DeviceID = LAST_INSERT_ID();
        SET p_ResultMessage = 'دستگاه با موفقیت ایجاد شد';
        COMMIT;
    END IF;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_CreateAttendanceRecord(
    IN p_EmployeeCode VARCHAR(20),
    IN p_DeviceID INT,
    IN p_RecordType ENUM('CheckIn', 'CheckOut', 'BreakStart', 'BreakEnd'),
    IN p_VerifyMethod ENUM('Fingerprint', 'Card', 'Face', 'PIN'),
    IN p_Location VARCHAR(100),
    OUT p_RecordID BIGINT,
    OUT p_ResultMessage VARCHAR(500)
)
BEGIN
    DECLARE v_EmployeeID INT;
    DECLARE v_LastRecordType ENUM('CheckIn', 'CheckOut', 'BreakStart', 'BreakEnd');
    DECLARE v_LastRecordTime DATETIME;
    DECLARE v_CurrentDate DATE;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ResultMessage = 'خطا در ثبت تردد';
        SET p_RecordID = -1;
    END;
    
    START TRANSACTION;
    
    -- پیدا کردن EmployeeID از کد کارمند
    SELECT EmployeeID INTO v_EmployeeID
    FROM Employees 
    WHERE EmployeeCode = p_EmployeeCode AND IsActive = 1;
    
    IF v_EmployeeID IS NULL THEN
        SET p_ResultMessage = 'کارمند یافت نشد';
        SET p_RecordID = -1;
        ROLLBACK;
    ELSE
        SET v_CurrentDate = CURDATE();
        
        -- پیدا کردن آخرین رکورد امروز
        SELECT RecordType, RecordDateTime INTO v_LastRecordType, v_LastRecordTime
        FROM AttendanceRecords 
        WHERE EmployeeID = v_EmployeeID 
        AND DATE(RecordDateTime) = v_CurrentDate
        ORDER BY RecordDateTime DESC 
        LIMIT 1;
        
        -- اعتبارسنجی منطقی تردد
        IF v_LastRecordType IS NOT NULL THEN
            CASE 
                WHEN p_RecordType = 'CheckIn' AND v_LastRecordType IN ('CheckIn', 'BreakStart') THEN
                    SET p_ResultMessage = 'ورود قبلاً ثبت شده است';
                    SET p_RecordID = -1;
                    ROLLBACK;
                WHEN p_RecordType = 'CheckOut' AND v_LastRecordType IN ('CheckOut', 'BreakEnd') THEN
                    SET p_ResultMessage = 'خروج قبلاً ثبت شده است';
                    SET p_RecordID = -1;
                    ROLLBACK;
                WHEN p_RecordType = 'BreakStart' AND v_LastRecordType IN ('BreakStart', 'CheckOut') THEN
                    SET p_ResultMessage = 'شروع استراحت قبلاً ثبت شده است';
                    SET p_RecordID = -1;
                    ROLLBACK;
                WHEN p_RecordType = 'BreakEnd' AND v_LastRecordType IN ('BreakEnd', 'CheckIn') THEN
                    SET p_ResultMessage = 'پایان استراحت قبلاً ثبت شده است';
                    SET p_RecordID = -1;
                    ROLLBACK;
                ELSE
                    -- ثبت رکورد
                    INSERT INTO AttendanceRecords (
                        EmployeeID, DeviceID, RecordDateTime, RecordType,
                        VerifyMethod, Location
                    ) VALUES (
                        v_EmployeeID, p_DeviceID, NOW(), p_RecordType,
                        p_VerifyMethod, p_Location
                    );
                    
                    SET p_RecordID = LAST_INSERT_ID();
                    SET p_ResultMessage = 'تردد با موفقیت ثبت شد';
                    COMMIT;
            END CASE;
        ELSE
            -- اولین رکورد روز باید CheckIn باشد
            IF p_RecordType != 'CheckIn' THEN
                SET p_ResultMessage = 'اولین تردد روز باید ورود باشد';
                SET p_RecordID = -1;
                ROLLBACK;
            ELSE
                -- ثبت رکورد
                INSERT INTO AttendanceRecords (
                    EmployeeID, DeviceID, RecordDateTime, RecordType,
                    VerifyMethod, Location
                ) VALUES (
                    v_EmployeeID, p_DeviceID, NOW(), p_RecordType,
                    p_VerifyMethod, p_Location
                );
                
                SET p_RecordID = LAST_INSERT_ID();
                SET p_ResultMessage = 'تردد با موفقیت ثبت شد';
                COMMIT;
            END IF;
        END IF;
    END IF;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_CreateManualAttendance(
    IN p_EmployeeID INT,
    IN p_RecordDate DATE,
    IN p_RecordTime TIME,
    IN p_RecordType ENUM('CheckIn', 'CheckOut', 'BreakStart', 'BreakEnd'),
    IN p_ManualReason TEXT,
    IN p_CreatedBy INT,
    OUT p_RecordID BIGINT,
    OUT p_ResultMessage VARCHAR(500)
)
BEGIN
    DECLARE v_RecordDateTime DATETIME;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ResultMessage = 'خطا در ثبت دستی تردد';
        SET p_RecordID = -1;
    END;
    
    START TRANSACTION;
    
    SET v_RecordDateTime = CONCAT(p_RecordDate, ' ', p_RecordTime);
    
    -- ثبت رکورد دستی
    INSERT INTO AttendanceRecords (
        EmployeeID, RecordDateTime, RecordType,
        IsManual, ManualReason, CreatedBy
    ) VALUES (
        p_EmployeeID, v_RecordDateTime, p_RecordType,
        TRUE, p_ManualReason, p_CreatedBy
    );
    
    SET p_RecordID = LAST_INSERT_ID();
    
    -- به‌روزرسانی خلاصه حضور و غیاب
    CALL sp_UpdateDailyAttendance(p_EmployeeID, p_RecordDate);
    
    SET p_ResultMessage = 'تردد دستی با موفقیت ثبت شد';
    COMMIT;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_GetDailyAttendanceReport(
    IN p_CompanyID INT,
    IN p_ReportDate DATE,
    IN p_DepartmentID INT,
    IN p_IncludeAbsent BOOLEAN,
    IN p_Page INT,
    IN p_PageSize INT,
    OUT p_TotalRecords INT
)
BEGIN
    -- محاسبه رکوردهای کل
    SELECT COUNT(*) INTO p_TotalRecords
    FROM Employees e
    WHERE e.CompanyID = p_CompanyID
    AND (p_DepartmentID IS NULL OR e.DepartmentID = p_DepartmentID)
    AND e.IsActive = 1;
    
    -- گزارش تردد روزانه
    SELECT 
        e.EmployeeID,
        e.EmployeeCode,
        CONCAT(e.FirstName, ' ', e.LastName) as EmployeeName,
        d.DepartmentName,
        p.PositionTitle,
        ws.ShiftName,
        ws.StartTime as ShiftStartTime,
        ws.EndTime as ShiftEndTime,
        -- زمان ورود
        (SELECT RecordTime 
         FROM AttendanceRecords 
         WHERE EmployeeID = e.EmployeeID 
         AND DATE(RecordDateTime) = p_ReportDate 
         AND RecordType = 'CheckIn'
         ORDER BY RecordDateTime ASC LIMIT 1) as CheckInTime,
        
        -- زمان خروج
        (SELECT RecordTime 
         FROM AttendanceRecords 
         WHERE EmployeeID = e.EmployeeID 
         AND DATE(RecordDateTime) = p_ReportDate 
         AND RecordType = 'CheckOut'
         ORDER BY RecordDateTime DESC LIMIT 1) as CheckOutTime,
        
        -- محاسبه تاخیر
        CASE 
            WHEN (SELECT RecordTime 
                  FROM AttendanceRecords 
                  WHERE EmployeeID = e.EmployeeID 
                  AND DATE(RecordDateTime) = p_ReportDate 
                  AND RecordType = 'CheckIn'
                  ORDER BY RecordDateTime ASC LIMIT 1) > ws.StartTime 
            THEN TIMESTAMPDIFF(MINUTE, ws.StartTime, 
                 (SELECT RecordTime 
                  FROM AttendanceRecords 
                  WHERE EmployeeID = e.EmployeeID 
                  AND DATE(RecordDateTime) = p_ReportDate 
                  AND RecordType = 'CheckIn'
                  ORDER BY RecordDateTime ASC LIMIT 1))
            ELSE 0 
        END as LateMinutes,
        
        -- محاسبه تعجیل در خروج
        CASE 
            WHEN (SELECT RecordTime 
                  FROM AttendanceRecords 
                  WHERE EmployeeID = e.EmployeeID 
                  AND DATE(RecordDateTime) = p_ReportDate 
                  AND RecordType = 'CheckOut'
                  ORDER BY RecordDateTime DESC LIMIT 1) < ws.EndTime 
            THEN TIMESTAMPDIFF(MINUTE, 
                 (SELECT RecordTime 
                  FROM AttendanceRecords 
                  WHERE EmployeeID = e.EmployeeID 
                  AND DATE(RecordDateTime) = p_ReportDate 
                  AND RecordType = 'CheckOut'
                  ORDER BY RecordDateTime DESC LIMIT 1), ws.EndTime)
            ELSE 0 
        END as EarlyDepartureMinutes,
        
        -- وضعیت حضور
        CASE 
            WHEN EXISTS (SELECT 1 FROM AttendanceRecords 
                        WHERE EmployeeID = e.EmployeeID 
                        AND DATE(RecordDateTime) = p_ReportDate) THEN 'Present'
            ELSE 'Absent'
        END as AttendanceStatus,
        
        -- مدت زمان کار
        CASE 
            WHEN (SELECT RecordTime FROM AttendanceRecords 
                  WHERE EmployeeID = e.EmployeeID 
                  AND DATE(RecordDateTime) = p_ReportDate 
                  AND RecordType = 'CheckIn' LIMIT 1) IS NOT NULL
                 AND
                 (SELECT RecordTime FROM AttendanceRecords 
                  WHERE EmployeeID = e.EmployeeID 
                  AND DATE(RecordDateTime) = p_ReportDate 
                  AND RecordType = 'CheckOut' LIMIT 1) IS NOT NULL
            THEN TIMESTAMPDIFF(MINUTE,
                 (SELECT RecordTime FROM AttendanceRecords 
                  WHERE EmployeeID = e.EmployeeID 
                  AND DATE(RecordDateTime) = p_ReportDate 
                  AND RecordType = 'CheckIn' LIMIT 1),
                 (SELECT RecordTime FROM AttendanceRecords 
                  WHERE EmployeeID = e.EmployeeID 
                  AND DATE(RecordDateTime) = p_ReportDate 
                  AND RecordType = 'CheckOut' LIMIT 1))
            ELSE 0 
        END as WorkMinutes
        
    FROM Employees e
    LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID
    LEFT JOIN Positions p ON e.PositionID = p.PositionID
    LEFT JOIN WorkShifts ws ON e.WorkShiftID = ws.ShiftID
    WHERE e.CompanyID = p_CompanyID
    AND (p_DepartmentID IS NULL OR e.DepartmentID = p_DepartmentID)
    AND e.IsActive = 1
    AND (p_IncludeAbsent = TRUE OR EXISTS (
        SELECT 1 FROM AttendanceRecords 
        WHERE EmployeeID = e.EmployeeID 
        AND DATE(RecordDateTime) = p_ReportDate
    ))
    ORDER BY d.DepartmentName, e.FirstName, e.LastName
    LIMIT p_PageSize OFFSET (p_Page - 1) * p_PageSize;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_GetMonthlyAttendanceReport(
    IN p_CompanyID INT,
    IN p_ReportYear INT,
    IN p_ReportMonth INT,
    IN p_DepartmentID INT
)
BEGIN
    DECLARE v_StartDate DATE;
    DECLARE v_EndDate DATE;
    
    SET v_StartDate = CONCAT(p_ReportYear, '-', LPAD(p_ReportMonth, 2, '0'), '-01');
    SET v_EndDate = LAST_DAY(v_StartDate);
    
    -- گزارش خلاصه ماهانه
    SELECT 
        e.EmployeeID,
        e.EmployeeCode,
        CONCAT(e.FirstName, ' ', e.LastName) as EmployeeName,
        d.DepartmentName,
        p.PositionTitle,
        
        -- آمار ماهانه
        COUNT(da.DailyAttendanceID) as WorkingDays,
        SUM(da.TotalWorkMinutes) as TotalWorkMinutes,
        SUM(da.OvertimeMinutes) as TotalOvertime,
        SUM(da.LateMinutes) as TotalLate,
        SUM(da.EarlyDepartureMinutes) as TotalEarlyDeparture,
        SUM(da.ShortageMinutes) as TotalShortage,
        
        -- روزهای حضور و غیاب
        SUM(CASE WHEN da.Status = 'Present' THEN 1 ELSE 0 END) as PresentDays,
        SUM(CASE WHEN da.Status = 'Absent' THEN 1 ELSE 0 END) as AbsentDays,
        SUM(CASE WHEN da.Status = 'Late' THEN 1 ELSE 0 END) as LateDays,
        SUM(CASE WHEN da.Status = 'HalfDay' THEN 1 ELSE 0 END) as HalfDays,
        SUM(CASE WHEN da.Status = 'Leave' THEN 1 ELSE 0 END) as LeaveDays,
        SUM(CASE WHEN da.Status = 'Holiday' THEN 1 ELSE 0 END) as HolidayDays,
        
        -- میانگین‌ها
        ROUND(AVG(da.TotalWorkMinutes), 2) as AvgWorkMinutesPerDay,
        ROUND(AVG(da.LateMinutes), 2) as AvgLateMinutesPerDay,
        ROUND(AVG(da.OvertimeMinutes), 2) as AvgOvertimePerDay
        
    FROM Employees e
    LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID
    LEFT JOIN Positions p ON e.PositionID = p.PositionID
    LEFT JOIN DailyAttendance da ON e.EmployeeID = da.EmployeeID 
        AND da.AttendanceDate BETWEEN v_StartDate AND v_EndDate
    WHERE e.CompanyID = p_CompanyID
    AND (p_DepartmentID IS NULL OR e.DepartmentID = p_DepartmentID)
    AND e.IsActive = 1
    GROUP BY e.EmployeeID
    ORDER BY d.DepartmentName, e.FirstName, e.LastName;
    
    -- جمع‌بندی کلی
    SELECT 
        COUNT(DISTINCT e.EmployeeID) as TotalEmployees,
        SUM(CASE WHEN da.Status = 'Present' THEN 1 ELSE 0 END) as TotalPresentDays,
        SUM(CASE WHEN da.Status = 'Absent' THEN 1 ELSE 0 END) as TotalAbsentDays,
        ROUND(AVG(da.TotalWorkMinutes), 2) as CompanyAvgWorkMinutes,
        SUM(da.OvertimeMinutes) as TotalCompanyOvertime,
        SUM(da.LateMinutes) as TotalCompanyLate
    FROM Employees e
    LEFT JOIN DailyAttendance da ON e.EmployeeID = da.EmployeeID 
        AND da.AttendanceDate BETWEEN v_StartDate AND v_EndDate
    WHERE e.CompanyID = p_CompanyID
    AND (p_DepartmentID IS NULL OR e.DepartmentID = p_DepartmentID)
    AND e.IsActive = 1;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_UpdateDailyAttendance(
    IN p_EmployeeID INT,
    IN p_AttendanceDate DATE
)
BEGIN
    DECLARE v_CheckInTime TIME;
    DECLARE v_CheckOutTime TIME;
    DECLARE v_ShiftID INT;
    DECLARE v_ShiftStart TIME;
    DECLARE v_ShiftEnd TIME;
    DECLARE v_RequiredWorkMinutes INT;
    DECLARE v_ActualWorkMinutes INT;
    DECLARE v_LateMinutes INT;
    DECLARE v_EarlyDepartureMinutes INT;
    DECLARE v_OvertimeMinutes INT;
    DECLARE v_ShortageMinutes INT;
    DECLARE v_AttendanceStatus ENUM('Present', 'Absent', 'Late', 'HalfDay', 'Holiday', 'Leave');
    
    -- گرفتن اطلاعات شیفت
    SELECT WorkShiftID INTO v_ShiftID
    FROM Employees WHERE EmployeeID = p_EmployeeID;
    
    SELECT StartTime, EndTime, TIMESTAMPDIFF(MINUTE, StartTime, EndTime) 
    INTO v_ShiftStart, v_ShiftEnd, v_RequiredWorkMinutes
    FROM WorkShifts WHERE ShiftID = v_ShiftID;
    
    -- پیدا کردن زمان ورود و خروج
    SELECT MIN(RecordTime) INTO v_CheckInTime
    FROM AttendanceRecords 
    WHERE EmployeeID = p_EmployeeID 
    AND DATE(RecordDateTime) = p_AttendanceDate
    AND RecordType = 'CheckIn';
    
    SELECT MAX(RecordTime) INTO v_CheckOutTime
    FROM AttendanceRecords 
    WHERE EmployeeID = p_EmployeeID 
    AND DATE(RecordDateTime) = p_AttendanceDate
    AND RecordType = 'CheckOut';
    
    -- محاسبه تاخیر
    IF v_CheckInTime IS NOT NULL AND v_CheckInTime > v_ShiftStart THEN
        SET v_LateMinutes = TIMESTAMPDIFF(MINUTE, v_ShiftStart, v_CheckInTime);
    ELSE
        SET v_LateMinutes = 0;
    END IF;
    
    -- محاسبه تعجیل در خروج
    IF v_CheckOutTime IS NOT NULL AND v_CheckOutTime < v_ShiftEnd THEN
        SET v_EarlyDepartureMinutes = TIMESTAMPDIFF(MINUTE, v_CheckOutTime, v_ShiftEnd);
    ELSE
        SET v_EarlyDepartureMinutes = 0;
    END IF;
    
    -- محاسبه مدت زمان کار واقعی
    IF v_CheckInTime IS NOT NULL AND v_CheckOutTime IS NOT NULL THEN
        SET v_ActualWorkMinutes = TIMESTAMPDIFF(MINUTE, v_CheckInTime, v_CheckOutTime);
        
        -- کسر زمان استراحت (فرض: 60 دقیقه)
        SET v_ActualWorkMinutes = v_ActualWorkMinutes - 60;
        
        -- محاسبه اضافه کار و کسری
        IF v_ActualWorkMinutes > v_RequiredWorkMinutes THEN
            SET v_OvertimeMinutes = v_ActualWorkMinutes - v_RequiredWorkMinutes;
            SET v_ShortageMinutes = 0;
        ELSE
            SET v_OvertimeMinutes = 0;
            SET v_ShortageMinutes = v_RequiredWorkMinutes - v_ActualWorkMinutes;
        END IF;
    ELSE
        SET v_ActualWorkMinutes = 0;
        SET v_OvertimeMinutes = 0;
        SET v_ShortageMinutes = v_RequiredWorkMinutes;
    END IF;
    
    -- تعیین وضعیت حضور
    IF v_CheckInTime IS NULL AND v_CheckOutTime IS NULL THEN
        SET v_AttendanceStatus = 'Absent';
    ELSEIF v_LateMinutes > 30 OR v_ShortageMinutes > 120 THEN
        SET v_AttendanceStatus = 'HalfDay';
    ELSEIF v_LateMinutes > 0 THEN
        SET v_AttendanceStatus = 'Late';
    ELSE
        SET v_AttendanceStatus = 'Present';
    END IF;
    
    -- درج یا به‌روزرسانی رکورد
    INSERT INTO DailyAttendance (
        EmployeeID, AttendanceDate, ShiftID,
        PlannedEntryTime, PlannedExitTime,
        ActualEntryTime, ActualExitTime,
        EntryDelay, ExitEarly,
        TotalWorkMinutes, RequiredWorkMinutes,
        OvertimeMinutes, ShortageMinutes,
        Status
    ) VALUES (
        p_EmployeeID, p_AttendanceDate, v_ShiftID,
        v_ShiftStart, v_ShiftEnd,
        v_CheckInTime, v_CheckOutTime,
        v_LateMinutes, v_EarlyDepartureMinutes,
        v_ActualWorkMinutes, v_RequiredWorkMinutes,
        v_OvertimeMinutes, v_ShortageMinutes,
        v_AttendanceStatus
    )
    ON DUPLICATE KEY UPDATE
        ActualEntryTime = VALUES(ActualEntryTime),
        ActualExitTime = VALUES(ActualExitTime),
        EntryDelay = VALUES(EntryDelay),
        ExitEarly = VALUES(ExitEarly),
        TotalWorkMinutes = VALUES(TotalWorkMinutes),
        OvertimeMinutes = VALUES(OvertimeMinutes),
        ShortageMinutes = VALUES(ShortageMinutes),
        Status = VALUES(Status);
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_CalculateMonthlyAttendance(
    IN p_CompanyID INT,
    IN p_Year INT,
    IN p_Month INT,
    OUT p_ResultMessage VARCHAR(500)
)
BEGIN
    DECLARE v_StartDate DATE;
    DECLARE v_EndDate DATE;
    DECLARE v_CurrentDate DATE;
    DECLARE v_EmployeeID INT;
    DECLARE done INT DEFAULT FALSE;
    
    DECLARE cur_employees CURSOR FOR 
        SELECT EmployeeID 
        FROM Employees 
        WHERE CompanyID = p_CompanyID AND IsActive = 1;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    SET v_StartDate = CONCAT(p_Year, '-', LPAD(p_Month, 2, '0'), '-01');
    SET v_EndDate = LAST_DAY(v_StartDate);
    SET v_CurrentDate = v_StartDate;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ResultMessage = 'خطا در محاسبه خلاصه ماهانه';
    END;
    
    START TRANSACTION;
    
    OPEN cur_employees;
    
    -- پیمایش روی تمام کارکنان
    read_loop: LOOP
        FETCH cur_employees INTO v_EmployeeID;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- پیمایش روی تمام روزهای ماه
        WHILE v_CurrentDate <= v_EndDate DO
            -- به‌روزرسانی خلاصه روزانه برای هر کارمند
            CALL sp_UpdateDailyAttendance(v_EmployeeID, v_CurrentDate);
            SET v_CurrentDate = DATE_ADD(v_CurrentDate, INTERVAL 1 DAY);
        END WHILE;
        
        SET v_CurrentDate = v_StartDate; -- بازنشانی برای کارمند بعدی
    END LOOP;
    
    CLOSE cur_employees;
    
    SET p_ResultMessage = 'خلاصه حضور و غیاب ماهانه با موفقیت محاسبه شد';
    COMMIT;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_GetWorkShifts(
    IN p_CompanyID INT,
    IN p_IsActive BOOLEAN
)
BEGIN
    SELECT 
        ShiftID,
        ShiftName,
        ShiftCode,
        StartTime,
        EndTime,
        GracePeriodBefore,
        GracePeriodAfter,
        IsActive,
        TIMESTAMPDIFF(MINUTE, StartTime, EndTime) as DurationMinutes,
        CASE 
            WHEN StartTime < '12:00:00' THEN 'صبح'
            WHEN StartTime < '17:00:00' THEN 'عصر'
            ELSE 'شب'
        END as ShiftType
    FROM WorkShifts
    WHERE CompanyID = p_CompanyID
    AND (p_IsActive IS NULL OR IsActive = p_IsActive)
    ORDER BY StartTime;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_AssignEmployeeShift(
    IN p_EmployeeID INT,
    IN p_ShiftID INT,
    IN p_EffectiveDate DATE,
    IN p_AssignedBy INT,
    OUT p_ResultMessage VARCHAR(500)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ResultMessage = 'خطا در اختصاص شیفت';
    END;
    
    START TRANSACTION;
    
    -- غیرفعال کردن شیفت قبلی
    UPDATE EmployeeShifts 
    SET IsActive = 0, ExpiryDate = DATE_SUB(p_EffectiveDate, INTERVAL 1 DAY)
    WHERE EmployeeID = p_EmployeeID AND IsActive = 1;
    
    -- اختصاص شیفت جدید
    INSERT INTO EmployeeShifts (
        EmployeeID, ShiftID, EffectiveDate, IsActive
    ) VALUES (
        p_EmployeeID, p_ShiftID, p_EffectiveDate, 1
    );
    
    -- به‌روزرسانی شیفت در پروفایل کارمند
    UPDATE Employees 
    SET WorkShiftID = p_ShiftID
    WHERE EmployeeID = p_EmployeeID;
    
    SET p_ResultMessage = 'شیفت با موفقیت به کارمند اختصاص داده شد';
    COMMIT;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_GetLiveAttendance(
    IN p_CompanyID INT,
    IN p_DepartmentID INT
)
BEGIN
    SELECT 
        e.EmployeeID,
        e.EmployeeCode,
        CONCAT(e.FirstName, ' ', e.LastName) as EmployeeName,
        d.DepartmentName,
        p.PositionTitle,
        ar.RecordDateTime as LastPunchTime,
        ar.RecordType as LastPunchType,
        ar.DeviceID,
        ad.DeviceName,
        ad.Location as DeviceLocation,
        TIMESTAMPDIFF(MINUTE, ar.RecordDateTime, NOW()) as MinutesAgo,
        CASE 
            WHEN TIMESTAMPDIFF(MINUTE, ar.RecordDateTime, NOW()) <= 5 THEN 'Online'
            WHEN TIMESTAMPDIFF(MINUTE, ar.RecordDateTime, NOW()) <= 30 THEN 'Recent'
            ELSE 'Offline'
        END as Status
    FROM AttendanceRecords ar
    INNER JOIN (
        SELECT EmployeeID, MAX(RecordDateTime) as LatestRecord
        FROM AttendanceRecords 
        WHERE DATE(RecordDateTime) = CURDATE()
        GROUP BY EmployeeID
    ) latest ON ar.EmployeeID = latest.EmployeeID AND ar.RecordDateTime = latest.LatestRecord
    INNER JOIN Employees e ON ar.EmployeeID = e.EmployeeID
    LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID
    LEFT JOIN Positions p ON e.PositionID = p.PositionID
    LEFT JOIN AttendanceDevices ad ON ar.DeviceID = ad.DeviceID
    WHERE e.CompanyID = p_CompanyID
    AND (p_DepartmentID IS NULL OR e.DepartmentID = p_DepartmentID)
    AND e.IsActive = 1
    ORDER BY ar.RecordDateTime DESC;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_GetAbsentEmployees(
    IN p_CompanyID INT,
    IN p_AbsentDate DATE,
    IN p_DepartmentID INT
)
BEGIN
    SELECT 
        e.EmployeeID,
        e.EmployeeCode,
        CONCAT(e.FirstName, ' ', e.LastName) as EmployeeName,
        d.DepartmentName,
        p.PositionTitle,
        e.PersonalMobile,
        e.WorkEmail,
        CONCAT(mgr.FirstName, ' ', mgr.LastName) as ManagerName,
        DATEDIFF(p_AbsentDate, e.EmploymentDate) as EmploymentDays
    FROM Employees e
    LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID
    LEFT JOIN Positions p ON e.PositionID = p.PositionID
    LEFT JOIN Employees mgr ON d.ManagerID = mgr.EmployeeID
    WHERE e.CompanyID = p_CompanyID
    AND (p_DepartmentID IS NULL OR e.DepartmentID = p_DepartmentID)
    AND e.IsActive = 1
    AND e.Status = 'Active'
    AND NOT EXISTS (
        SELECT 1 FROM AttendanceRecords 
        WHERE EmployeeID = e.EmployeeID 
        AND DATE(RecordDateTime) = p_AbsentDate
    )
    AND p_AbsentDate >= e.EmploymentDate
    AND (e.EmploymentEndDate IS NULL OR p_AbsentDate <= e.EmploymentEndDate)
    ORDER BY d.DepartmentName, e.FirstName, e.LastName;
END$$

DELIMITER ;

<?php
// فراخوانی برای ثبت تردد
$employeeCode = 'EMP1001';
$deviceId = 1;
$recordType = 'CheckIn';

$query = "CALL sp_CreateAttendanceRecord(?, ?, ?, 'Fingerprint', NULL, @recordId, @resultMessage)";
$stmt = $conn->prepare($query);
$stmt->bind_param("sis", $employeeCode, $deviceId, $recordType);
$stmt->execute();

$result = $conn->query("SELECT @recordId as record_id, @resultMessage as message");
$output = $result->fetch_assoc();

if ($output['record_id'] > 0) {
    echo "تردد با شناسه " . $output['record_id'] . " ثبت شد";
} else {
    echo "خطا: " . $output['message'];
}

// فراخوانی برای گزارش روزانه
$companyId = 1;
$reportDate = '2024-01-15';
$departmentId = null;
$page = 1;
$pageSize = 50;

$query = "CALL sp_GetDailyAttendanceReport(?, ?, ?, TRUE, ?, ?, @totalRecords)";
$stmt = $conn->prepare($query);
$stmt->bind_param("isiii", $companyId, $reportDate, $departmentId, $page, $pageSize);
$stmt->execute();
$result = $stmt->get_result();

$attendanceReport = [];
while ($row = $result->fetch_assoc()) {
    $attendanceReport[] = $row;
}

// فراخوانی برای گزارش ماهانه
$query = "CALL sp_GetMonthlyAttendanceReport(1, 2024, 1, NULL)";
$result = $conn->query($query);

$monthlyReport = [];
while ($row = $result->fetch_assoc()) {
    $monthlyReport[] = $row;
}

// رفتن به نتیجه بعدی (جمع‌بندی)
if ($conn->more_results()) {
    $conn->next_result();
    $summary = $conn->store_result()->fetch_assoc();
}

// فراخوانی برای محاسبه خلاصه ماهانه
$query = "CALL sp_CalculateMonthlyAttendance(1, 2024, 1, @resultMessage)";
$conn->query($query);
$result = $conn->query("SELECT @resultMessage as message");
$output = $result->fetch_assoc();
echo $output['message'];
?>