Stored Procedure های کامل برای مدیریت جداول سیستم HRM

DELIMITER $$

CREATE PROCEDURE sp_GetEmployees(
    IN p_CompanyID INT,
    IN p_DepartmentID INT,
    IN p_PositionID INT,
    IN p_IsActive BOOLEAN,
    IN p_Status ENUM('Active', 'Suspended', 'Leave', 'Terminated'),
    IN p_SearchTerm NVARCHAR(100),
    IN p_Page INT,
    IN p_PageSize INT,
    OUT p_TotalRecords INT,
    OUT p_TotalPages 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 (p_PositionID IS NULL OR e.PositionID = p_PositionID)
    AND (p_IsActive IS NULL OR e.IsActive = p_IsActive)
    AND (p_Status IS NULL OR e.Status = p_Status)
    AND (p_SearchTerm IS NULL OR 
         e.FirstName LIKE CONCAT('%', p_SearchTerm, '%') OR 
         e.LastName LIKE CONCAT('%', p_SearchTerm, '%') OR
         e.EmployeeCode LIKE CONCAT('%', p_SearchTerm, '%') OR
         e.NationalCode LIKE CONCAT('%', p_SearchTerm, '%'));
    
    SET p_TotalPages = CEIL(p_TotalRecords / p_PageSize);
    
    -- گرفتن لیست کارکنان
    SELECT 
        e.EmployeeID,
        e.EmployeeCode,
        e.NationalCode,
        e.FirstName,
        e.LastName,
        e.FatherName,
        e.BirthDate,
        e.Gender,
        e.MaritalStatus,
        e.PersonalMobile,
        e.WorkEmail,
        e.DepartmentID,
        d.DepartmentName,
        e.PositionID,
        p.PositionTitle,
        e.EmploymentType,
        e.EmploymentDate,
        e.EmploymentEndDate,
        e.BasicSalary,
        e.IsActive,
        e.Status,
        e.ProfileImage,
        DATEDIFF(CURDATE(), e.EmploymentDate) as EmploymentDays,
        TIMESTAMPDIFF(YEAR, e.BirthDate, CURDATE()) as Age,
        ws.ShiftName,
        CONCAT(mgr.FirstName, ' ', mgr.LastName) as ManagerName
    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
    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 (p_PositionID IS NULL OR e.PositionID = p_PositionID)
    AND (p_IsActive IS NULL OR e.IsActive = p_IsActive)
    AND (p_Status IS NULL OR e.Status = p_Status)
    AND (p_SearchTerm IS NULL OR 
         e.FirstName LIKE CONCAT('%', p_SearchTerm, '%') OR 
         e.LastName LIKE CONCAT('%', p_SearchTerm, '%') OR
         e.EmployeeCode LIKE CONCAT('%', p_SearchTerm, '%') OR
         e.NationalCode LIKE CONCAT('%', p_SearchTerm, '%'))
    ORDER BY e.FirstName, e.LastName
    LIMIT p_PageSize OFFSET (p_Page - 1) * p_PageSize;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_GetEmployeeInfo(
    IN p_EmployeeID INT
)
BEGIN
    -- اطلاعات اصلی کارمند
    SELECT 
        e.*,
        d.DepartmentName,
        p.PositionTitle,
        ws.ShiftName,
        CONCAT(mgr.FirstName, ' ', mgr.LastName) as ManagerName,
        TIMESTAMPDIFF(YEAR, e.BirthDate, CURDATE()) as Age,
        DATEDIFF(CURDATE(), 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 WorkShifts ws ON e.WorkShiftID = ws.ShiftID
    LEFT JOIN Employees mgr ON d.ManagerID = mgr.EmployeeID
    WHERE e.EmployeeID = p_EmployeeID;
    
    -- سوابق تحصیلی
    SELECT 
        ed.EducationID,
        ed.EducationLevel,
        ed.FieldOfStudy,
        ed.University,
        ed.StartDate,
        ed.EndDate,
        ed.GPA,
        ed.IsCurrent,
        ed.Description,
        ed.DocumentFile
    FROM Educations ed
    WHERE ed.EmployeeID = p_EmployeeID
    ORDER BY ed.StartDate DESC;
    
    -- سوابق کاری
    SELECT 
        we.ExperienceID,
        we.CompanyName,
        we.JobTitle,
        we.StartDate,
        we.EndDate,
        we.IsCurrent,
        we.Salary,
        we.LeaveReason,
        we.Achievements
    FROM WorkExperiences we
    WHERE we.EmployeeID = p_EmployeeID
    ORDER BY we.StartDate DESC;
    
    -- مهارت‌ها
    SELECT 
        es.EmployeeSkillID,
        s.SkillID,
        s.SkillName,
        s.SkillCategory,
        es.ProficiencyLevel,
        es.YearsOfExperience,
        es.CertificateFile
    FROM EmployeeSkills es
    INNER JOIN Skills s ON es.SkillID = s.SkillID
    WHERE es.EmployeeID = p_EmployeeID
    ORDER BY s.SkillCategory, s.SkillName;
    
    -- دوره‌های آموزشی
    SELECT 
        et.EmployeeTrainingID,
        t.TrainingName,
        t.TrainingType,
        t.Provider,
        t.StartDate,
        t.EndDate,
        et.EnrollmentDate,
        et.CompletionDate,
        et.Status,
        et.Score,
        et.CertificateFile
    FROM EmployeeTrainings et
    INNER JOIN Trainings t ON et.TrainingID = t.TrainingID
    WHERE et.EmployeeID = p_EmployeeID
    ORDER BY et.EnrollmentDate DESC;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_CreateEmployee(
    IN p_CompanyID INT,
    IN p_EmployeeCode VARCHAR(20),
    IN p_NationalCode VARCHAR(10),
    IN p_FirstName NVARCHAR(50),
    IN p_LastName NVARCHAR(50),
    IN p_FatherName NVARCHAR(50),
    IN p_BirthDate DATE,
    IN p_BirthPlace NVARCHAR(100),
    IN p_Gender ENUM('Male', 'Female'),
    IN p_MaritalStatus ENUM('Single', 'Married', 'Divorced', 'Widowed'),
    IN p_MilitaryStatus ENUM('Exempt', 'Completed', 'EducationalExempt', 'NotApplicable'),
    IN p_InsuranceNumber VARCHAR(20),
    IN p_PersonalMobile VARCHAR(11),
    IN p_WorkEmail VARCHAR(100),
    IN p_Address TEXT,
    IN p_DepartmentID INT,
    IN p_PositionID INT,
    IN p_EmploymentType ENUM('Permanent', 'Contractual', 'Temporary', 'ProjectBased'),
    IN p_EmploymentDate DATE,
    IN p_WorkShiftID INT,
    IN p_BasicSalary DECIMAL(12,2),
    IN p_BankName NVARCHAR(100),
    IN p_BankAccountNumber VARCHAR(30),
    IN p_ProfileImage VARCHAR(255),
    IN p_CreatedBy INT,
    OUT p_EmployeeID 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_EmployeeID = -1;
    END;
    
    START TRANSACTION;
    
    -- بررسی تکراری نبودن کد کارمند
    SELECT COUNT(*) INTO v_Count 
    FROM Employees 
    WHERE CompanyID = p_CompanyID AND EmployeeCode = p_EmployeeCode;
    
    IF v_Count > 0 THEN
        SET p_ResultMessage = 'کد کارمند تکراری است';
        SET p_EmployeeID = -1;
        ROLLBACK;
    ELSE
        -- بررسی تکراری نبودن کد ملی
        IF p_NationalCode IS NOT NULL THEN
            SELECT COUNT(*) INTO v_Count 
            FROM Employees 
            WHERE CompanyID = p_CompanyID AND NationalCode = p_NationalCode;
            
            IF v_Count > 0 THEN
                SET p_ResultMessage = 'کد ملی تکراری است';
                SET p_EmployeeID = -1;
                ROLLBACK;
            ELSE
                -- ایجاد کارمند
                INSERT INTO Employees (
                    CompanyID, EmployeeCode, NationalCode, FirstName, LastName,
                    FatherName, BirthDate, BirthPlace, Gender, MaritalStatus,
                    MilitaryStatus, InsuranceNumber, PersonalMobile, WorkEmail,
                    Address, DepartmentID, PositionID, EmploymentType, EmploymentDate,
                    WorkShiftID, BasicSalary, BankName, BankAccountNumber, ProfileImage,
                    CreatedBy
                ) VALUES (
                    p_CompanyID, p_EmployeeCode, p_NationalCode, p_FirstName, p_LastName,
                    p_FatherName, p_BirthDate, p_BirthPlace, p_Gender, p_MaritalStatus,
                    p_MilitaryStatus, p_InsuranceNumber, p_PersonalMobile, p_WorkEmail,
                    p_Address, p_DepartmentID, p_PositionID, p_EmploymentType, p_EmploymentDate,
                    p_WorkShiftID, p_BasicSalary, p_BankName, p_BankAccountNumber, p_ProfileImage,
                    p_CreatedBy
                );
                
                SET p_EmployeeID = LAST_INSERT_ID();
                SET p_ResultMessage = 'کارمند با موفقیت ایجاد شد';
                COMMIT;
            END IF;
        ELSE
            -- ایجاد کارمند بدون کد ملی
            INSERT INTO Employees (
                CompanyID, EmployeeCode, NationalCode, FirstName, LastName,
                FatherName, BirthDate, BirthPlace, Gender, MaritalStatus,
                MilitaryStatus, InsuranceNumber, PersonalMobile, WorkEmail,
                Address, DepartmentID, PositionID, EmploymentType, EmploymentDate,
                WorkShiftID, BasicSalary, BankName, BankAccountNumber, ProfileImage,
                CreatedBy
            ) VALUES (
                p_CompanyID, p_EmployeeCode, p_NationalCode, p_FirstName, p_LastName,
                p_FatherName, p_BirthDate, p_BirthPlace, p_Gender, p_MaritalStatus,
                p_MilitaryStatus, p_InsuranceNumber, p_PersonalMobile, p_WorkEmail,
                p_Address, p_DepartmentID, p_PositionID, p_EmploymentType, p_EmploymentDate,
                p_WorkShiftID, p_BasicSalary, p_BankName, p_BankAccountNumber, p_ProfileImage,
                p_CreatedBy
            );
            
            SET p_EmployeeID = LAST_INSERT_ID();
            SET p_ResultMessage = 'کارمند با موفقیت ایجاد شد';
            COMMIT;
        END IF;
    END IF;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_UpdateEmployee(
    IN p_EmployeeID INT,
    IN p_FirstName NVARCHAR(50),
    IN p_LastName NVARCHAR(50),
    IN p_FatherName NVARCHAR(50),
    IN p_BirthDate DATE,
    IN p_BirthPlace NVARCHAR(100),
    IN p_Gender ENUM('Male', 'Female'),
    IN p_MaritalStatus ENUM('Single', 'Married', 'Divorced', 'Widowed'),
    IN p_MilitaryStatus ENUM('Exempt', 'Completed', 'EducationalExempt', 'NotApplicable'),
    IN p_InsuranceNumber VARCHAR(20),
    IN p_PersonalMobile VARCHAR(11),
    IN p_WorkEmail VARCHAR(100),
    IN p_Address TEXT,
    IN p_DepartmentID INT,
    IN p_PositionID INT,
    IN p_EmploymentType ENUM('Permanent', 'Contractual', 'Temporary', 'ProjectBased'),
    IN p_EmploymentEndDate DATE,
    IN p_WorkShiftID INT,
    IN p_BasicSalary DECIMAL(12,2),
    IN p_BankName NVARCHAR(100),
    IN p_BankAccountNumber VARCHAR(30),
    IN p_ProfileImage VARCHAR(255),
    IN p_IsActive BOOLEAN,
    IN p_Status ENUM('Active', 'Suspended', 'Leave', 'Terminated'),
    OUT p_ResultMessage VARCHAR(500)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ResultMessage = 'خطا در به‌روزرسانی کارمند';
    END;
    
    START TRANSACTION;
    
    -- به‌روزرسانی کارمند
    UPDATE Employees 
    SET 
        FirstName = p_FirstName,
        LastName = p_LastName,
        FatherName = p_FatherName,
        BirthDate = p_BirthDate,
        BirthPlace = p_BirthPlace,
        Gender = p_Gender,
        MaritalStatus = p_MaritalStatus,
        MilitaryStatus = p_MilitaryStatus,
        InsuranceNumber = p_InsuranceNumber,
        PersonalMobile = p_PersonalMobile,
        WorkEmail = p_WorkEmail,
        Address = p_Address,
        DepartmentID = p_DepartmentID,
        PositionID = p_PositionID,
        EmploymentType = p_EmploymentType,
        EmploymentEndDate = p_EmploymentEndDate,
        WorkShiftID = p_WorkShiftID,
        BasicSalary = p_BasicSalary,
        BankName = p_BankName,
        BankAccountNumber = p_BankAccountNumber,
        ProfileImage = p_ProfileImage,
        IsActive = p_IsActive,
        Status = p_Status,
        UpdatedAt = CURRENT_TIMESTAMP
    WHERE EmployeeID = p_EmployeeID;
    
    SET p_ResultMessage = 'کارمند با موفقیت به‌روزرسانی شد';
    COMMIT;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_GetDepartments(
    IN p_CompanyID INT,
    IN p_ParentDepartmentID INT,
    IN p_IsActive BOOLEAN,
    IN p_IncludeTree BOOLEAN,
    OUT p_TotalRecords INT
)
BEGIN
    -- محاسبه رکوردهای کل
    SELECT COUNT(*) INTO p_TotalRecords
    FROM Departments 
    WHERE CompanyID = p_CompanyID
    AND (p_ParentDepartmentID IS NULL OR ParentDepartmentID = p_ParentDepartmentID)
    AND (p_IsActive IS NULL OR IsActive = p_IsActive);
    
    IF p_IncludeTree = TRUE THEN
        -- ساختار درختی دپارتمان‌ها
        SELECT 
            d1.DepartmentID,
            d1.DepartmentCode,
            d1.DepartmentName,
            d1.ParentDepartmentID,
            d1.ManagerID,
            d1.CostCenter,
            d1.Location,
            d1.Phone,
            d1.Email,
            d1.IsActive,
            d1.SortOrder,
            d2.DepartmentName as ParentDepartmentName,
            CONCAT(mgr.FirstName, ' ', mgr.LastName) as ManagerName,
            COUNT(e.EmployeeID) as EmployeeCount
        FROM Departments d1
        LEFT JOIN Departments d2 ON d1.ParentDepartmentID = d2.DepartmentID
        LEFT JOIN Employees mgr ON d1.ManagerID = mgr.EmployeeID
        LEFT JOIN Employees e ON d1.DepartmentID = e.DepartmentID AND e.IsActive = 1
        WHERE d1.CompanyID = p_CompanyID
        AND (p_IsActive IS NULL OR d1.IsActive = p_IsActive)
        GROUP BY d1.DepartmentID
        ORDER BY d1.SortOrder, d1.DepartmentName;
    ELSE
        -- لیست ساده دپارتمان‌ها
        SELECT 
            d.DepartmentID,
            d.DepartmentCode,
            d.DepartmentName,
            d.ParentDepartmentID,
            d.ManagerID,
            d.CostCenter,
            d.Location,
            d.Phone,
            d.Email,
            d.IsActive,
            d.SortOrder,
            CONCAT(mgr.FirstName, ' ', mgr.LastName) as ManagerName,
            COUNT(e.EmployeeID) as EmployeeCount
        FROM Departments d
        LEFT JOIN Employees mgr ON d.ManagerID = mgr.EmployeeID
        LEFT JOIN Employees e ON d.DepartmentID = e.DepartmentID AND e.IsActive = 1
        WHERE d.CompanyID = p_CompanyID
        AND (p_ParentDepartmentID IS NULL OR d.ParentDepartmentID = p_ParentDepartmentID)
        AND (p_IsActive IS NULL OR d.IsActive = p_IsActive)
        GROUP BY d.DepartmentID
        ORDER BY d.SortOrder, d.DepartmentName;
    END IF;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_CreateDepartment(
    IN p_CompanyID INT,
    IN p_DepartmentCode VARCHAR(20),
    IN p_DepartmentName NVARCHAR(100),
    IN p_ParentDepartmentID INT,
    IN p_ManagerID INT,
    IN p_CostCenter VARCHAR(50),
    IN p_Location NVARCHAR(200),
    IN p_Phone VARCHAR(15),
    IN p_Email VARCHAR(100),
    IN p_SortOrder INT,
    OUT p_DepartmentID 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_DepartmentID = -1;
    END;
    
    START TRANSACTION;
    
    -- بررسی تکراری نبودن کد دپارتمان
    SELECT COUNT(*) INTO v_Count 
    FROM Departments 
    WHERE CompanyID = p_CompanyID AND DepartmentCode = p_DepartmentCode;
    
    IF v_Count > 0 THEN
        SET p_ResultMessage = 'کد دپارتمان تکراری است';
        SET p_DepartmentID = -1;
        ROLLBACK;
    ELSE
        -- ایجاد دپارتمان
        INSERT INTO Departments (
            CompanyID, DepartmentCode, DepartmentName, ParentDepartmentID,
            ManagerID, CostCenter, Location, Phone, Email, SortOrder
        ) VALUES (
            p_CompanyID, p_DepartmentCode, p_DepartmentName, p_ParentDepartmentID,
            p_ManagerID, p_CostCenter, p_Location, p_Phone, p_Email, p_SortOrder
        );
        
        SET p_DepartmentID = LAST_INSERT_ID();
        SET p_ResultMessage = 'دپارتمان با موفقیت ایجاد شد';
        COMMIT;
    END IF;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_GetEmployeeLeaves(
    IN p_CompanyID INT,
    IN p_EmployeeID INT,
    IN p_Status ENUM('Pending', 'Approved', 'Rejected', 'Cancelled'),
    IN p_LeaveTypeID INT,
    IN p_StartDate DATE,
    IN p_EndDate DATE,
    IN p_Page INT,
    IN p_PageSize INT,
    OUT p_TotalRecords INT,
    OUT p_TotalPages INT
)
BEGIN
    -- محاسبه رکوردهای کل
    SELECT COUNT(*) INTO p_TotalRecords
    FROM EmployeeLeaves el
    INNER JOIN Employees e ON el.EmployeeID = e.EmployeeID
    WHERE e.CompanyID = p_CompanyID
    AND (p_EmployeeID IS NULL OR el.EmployeeID = p_EmployeeID)
    AND (p_Status IS NULL OR el.Status = p_Status)
    AND (p_LeaveTypeID IS NULL OR el.LeaveTypeID = p_LeaveTypeID)
    AND (p_StartDate IS NULL OR el.StartDate >= p_StartDate)
    AND (p_EndDate IS NULL OR el.EndDate <= p_EndDate);
    
    SET p_TotalPages = CEIL(p_TotalRecords / p_PageSize);
    
    -- گرفتن لیست مرخصی‌ها
    SELECT 
        el.LeaveID,
        el.EmployeeID,
        e.EmployeeCode,
        CONCAT(e.FirstName, ' ', e.LastName) as EmployeeName,
        d.DepartmentName,
        el.LeaveTypeID,
        lt.LeaveTypeName,
        el.StartDate,
        el.EndDate,
        el.StartTime,
        el.EndTime,
        el.TotalDays,
        el.Reason,
        el.Status,
        el.RequestedAt,
        el.ApprovedBy,
        el.ApprovedAt,
        CONCAT(app.FirstName, ' ', app.LastName) as ApproverName,
        el.RejectedBy,
        el.RejectedAt,
        CONCAT(rej.FirstName, ' ', rej.LastName) as RejecterName,
        el.RejectionReason
    FROM EmployeeLeaves el
    INNER JOIN Employees e ON el.EmployeeID = e.EmployeeID
    INNER JOIN LeaveTypes lt ON el.LeaveTypeID = lt.LeaveTypeID
    LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID
    LEFT JOIN Employees app ON el.ApprovedBy = app.EmployeeID
    LEFT JOIN Employees rej ON el.RejectedBy = rej.EmployeeID
    WHERE e.CompanyID = p_CompanyID
    AND (p_EmployeeID IS NULL OR el.EmployeeID = p_EmployeeID)
    AND (p_Status IS NULL OR el.Status = p_Status)
    AND (p_LeaveTypeID IS NULL OR el.LeaveTypeID = p_LeaveTypeID)
    AND (p_StartDate IS NULL OR el.StartDate >= p_StartDate)
    AND (p_EndDate IS NULL OR el.EndDate <= p_EndDate)
    ORDER BY el.StartDate DESC, el.LeaveID DESC
    LIMIT p_PageSize OFFSET (p_Page - 1) * p_PageSize;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_CreateLeaveRequest(
    IN p_EmployeeID INT,
    IN p_LeaveTypeID INT,
    IN p_StartDate DATE,
    IN p_EndDate DATE,
    IN p_StartTime TIME,
    IN p_EndTime TIME,
    IN p_Reason TEXT,
    IN p_ContactDuringLeave VARCHAR(100),
    IN p_RequestedBy INT,
    OUT p_LeaveID INT,
    OUT p_ResultMessage VARCHAR(500)
)
BEGIN
    DECLARE v_TotalDays DECIMAL(4,2);
    DECLARE v_AvailableBalance DECIMAL(4,2);
    DECLARE v_Year INT;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ResultMessage = 'خطا در ایجاد درخواست مرخصی';
        SET p_LeaveID = -1;
    END;
    
    START TRANSACTION;
    
    -- محاسبه تعداد روزهای مرخصی
    IF p_StartTime IS NULL AND p_EndTime IS NULL THEN
        -- مرخصی روز کامل
        SET v_TotalDays = DATEDIFF(p_EndDate, p_StartDate) + 1;
    ELSE
        -- مرخصی ساعتی
        SET v_TotalDays = (DATEDIFF(p_EndDate, p_StartDate) + 1) * 0.5; -- فرض: نیم روز
    END IF;
    
    -- بررسی موجودی مرخصی
    SET v_Year = YEAR(p_StartDate);
    
    SELECT RemainingBalance INTO v_AvailableBalance
    FROM EmployeeLeaveBalances
    WHERE EmployeeID = p_EmployeeID 
    AND LeaveTypeID = p_LeaveTypeID 
    AND Year = v_Year;
    
    IF v_AvailableBalance IS NULL THEN
        SET v_AvailableBalance = 0;
    END IF;
    
    IF v_TotalDays > v_AvailableBalance THEN
        SET p_ResultMessage = 'موجودی مرخصی کافی نیست';
        SET p_LeaveID = -1;
        ROLLBACK;
    ELSE
        -- ایجاد درخواست مرخصی
        INSERT INTO EmployeeLeaves (
            EmployeeID, LeaveTypeID, StartDate, EndDate, StartTime, EndTime,
            TotalDays, Reason, ContactDuringLeave, RequestedBy
        ) VALUES (
            p_EmployeeID, p_LeaveTypeID, p_StartDate, p_EndDate, p_StartTime, p_EndTime,
            v_TotalDays, p_Reason, p_ContactDuringLeave, p_RequestedBy
        );
        
        SET p_LeaveID = LAST_INSERT_ID();
        SET p_ResultMessage = 'درخواست مرخصی با موفقیت ایجاد شد';
        COMMIT;
    END IF;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_ApproveRejectLeave(
    IN p_LeaveID INT,
    IN p_Status ENUM('Approved', 'Rejected'),
    IN p_ApprovedBy INT,
    IN p_RejectionReason TEXT,
    OUT p_ResultMessage VARCHAR(500)
)
BEGIN
    DECLARE v_EmployeeID INT;
    DECLARE v_LeaveTypeID INT;
    DECLARE v_TotalDays DECIMAL(4,2);
    DECLARE v_Year INT;
    DECLARE v_StartDate DATE;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ResultMessage = 'خطا در به‌روزرسانی وضعیت مرخصی';
    END;
    
    START TRANSACTION;
    
    -- گرفتن اطلاعات مرخصی
    SELECT EmployeeID, LeaveTypeID, TotalDays, StartDate
    INTO v_EmployeeID, v_LeaveTypeID, v_TotalDays, v_StartDate
    FROM EmployeeLeaves
    WHERE LeaveID = p_LeaveID;
    
    SET v_Year = YEAR(v_StartDate);
    
    IF p_Status = 'Approved' THEN
        -- به‌روزرسانی وضعیت به تایید شده
        UPDATE EmployeeLeaves 
        SET 
            Status = 'Approved',
            ApprovedBy = p_ApprovedBy,
            ApprovedAt = CURRENT_TIMESTAMP,
            RejectedBy = NULL,
            RejectedAt = NULL,
            RejectionReason = NULL
        WHERE LeaveID = p_LeaveID;
        
        -- کسر از موجودی مرخصی
        UPDATE EmployeeLeaveBalances 
        SET 
            UsedBalance = UsedBalance + v_TotalDays,
            RemainingBalance = RemainingBalance - v_TotalDays,
            UpdatedAt = CURRENT_TIMESTAMP
        WHERE EmployeeID = v_EmployeeID 
        AND LeaveTypeID = v_LeaveTypeID 
        AND Year = v_Year;
        
        SET p_ResultMessage = 'مرخصی با موفقیت تایید شد';
    ELSE
        -- به‌روزرسانی وضعیت به رد شده
        UPDATE EmployeeLeaves 
        SET 
            Status = 'Rejected',
            RejectedBy = p_ApprovedBy,
            RejectedAt = CURRENT_TIMESTAMP,
            ApprovedBy = NULL,
            ApprovedAt = NULL,
            RejectionReason = p_RejectionReason
        WHERE LeaveID = p_LeaveID;
        
        SET p_ResultMessage = 'مرخصی با موفقیت رد شد';
    END IF;
    
    COMMIT;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_CreatePayrollPeriod(
    IN p_CompanyID INT,
    IN p_PeriodName NVARCHAR(100),
    IN p_PeriodYear INT,
    IN p_PeriodMonth INT,
    IN p_StartDate DATE,
    IN p_EndDate DATE,
    IN p_PaymentDate DATE,
    IN p_CreatedBy INT,
    OUT p_PeriodID 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_PeriodID = -1;
    END;
    
    START TRANSACTION;
    
    -- بررسی تکراری نبودن دوره
    SELECT COUNT(*) INTO v_Count 
    FROM PayrollPeriods 
    WHERE CompanyID = p_CompanyID 
    AND PeriodYear = p_PeriodYear 
    AND PeriodMonth = p_PeriodMonth;
    
    IF v_Count > 0 THEN
        SET p_ResultMessage = 'دوره حقوق برای این ماه و سال قبلاً ایجاد شده است';
        SET p_PeriodID = -1;
        ROLLBACK;
    ELSE
        -- ایجاد دوره
        INSERT INTO PayrollPeriods (
            CompanyID, PeriodName, PeriodYear, PeriodMonth,
            StartDate, EndDate, PaymentDate, CreatedBy
        ) VALUES (
            p_CompanyID, p_PeriodName, p_PeriodYear, p_PeriodMonth,
            p_StartDate, p_EndDate, p_PaymentDate, p_CreatedBy
        );
        
        SET p_PeriodID = LAST_INSERT_ID();
        SET p_ResultMessage = 'دوره حقوق با موفقیت ایجاد شد';
        COMMIT;
    END IF;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_CalculatePayroll(
    IN p_PeriodID INT,
    IN p_EmployeeIDs JSON,
    IN p_CalculatedBy INT,
    OUT p_ResultMessage VARCHAR(500)
)
BEGIN
    DECLARE v_EmployeeCount INT DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    DECLARE v_EmployeeID INT;
    DECLARE v_BasicSalary DECIMAL(12,2);
    DECLARE v_Allowances JSON;
    DECLARE v_Deductions JSON;
    DECLARE v_Bonuses JSON;
    DECLARE v_GrossSalary DECIMAL(12,2);
    DECLARE v_TotalDeductions DECIMAL(12,2);
    DECLARE v_NetSalary DECIMAL(12,2);
    DECLARE v_WorkingDays INT DEFAULT 30;
    DECLARE v_ActualDays INT DEFAULT 30;
    DECLARE v_AbsenceDays INT DEFAULT 0;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_ResultMessage = 'خطا در محاسبه حقوق';
    END;
    
    START TRANSACTION;
    
    SET v_EmployeeCount = JSON_LENGTH(p_EmployeeIDs);
    SET i = 0;
    
    WHILE i < v_EmployeeCount DO
        SET v_EmployeeID = JSON_EXTRACT(p_EmployeeIDs, CONCAT('$[', i, ']'));
        
        -- گرفتن اطلاعات حقوق پایه
        SELECT BasicSalary INTO v_BasicSalary
        FROM Employees
        WHERE EmployeeID = v_EmployeeID AND IsActive = 1;
        
        IF v_BasicSalary IS NOT NULL THEN
            -- محاسبه روزهای غیبت
            SELECT COALESCE(SUM(TotalDays), 0) INTO v_AbsenceDays
            FROM EmployeeLeaves
            WHERE EmployeeID = v_EmployeeID
            AND Status = 'Approved'
            AND (
                (StartDate BETWEEN (SELECT StartDate FROM PayrollPeriods WHERE PeriodID = p_PeriodID) 
                 AND (SELECT EndDate FROM PayrollPeriods WHERE PeriodID = p_PeriodID))
                OR
                (EndDate BETWEEN (SELECT StartDate FROM PayrollPeriods WHERE PeriodID = p_PeriodID) 
                 AND (SELECT EndDate FROM PayrollPeriods WHERE PeriodID = p_PeriodID))
            );
            
            SET v_ActualDays = v_WorkingDays - v_AbsenceDays;
            
            -- محاسبه حقوق
            SET v_GrossSalary = (v_BasicSalary / v_WorkingDays) * v_ActualDays;
            
            -- محاسبه کسورات (نمونه)
            SET v_Deductions = JSON_OBJECT(
                'tax', v_GrossSalary * 0.1,
                'insurance', v_GrossSalary * 0.07,
                'other', 0
            );
            
            SET v_TotalDeductions = 
                JSON_EXTRACT(v_Deductions, '$.tax') +
                JSON_EXTRACT(v_Deductions, '$.insurance') +
                JSON_EXTRACT(v_Deductions, '$.other');
            
            -- محاسبه اضافه کار و پاداش (نمونه)
            SET v_Bonuses = JSON_OBJECT(
                'overtime', v_BasicSalary * 0.05,
                'performance', v_BasicSalary * 0.03,
                'other', 0
            );
            
            SET v_Allowances = JSON_OBJECT(
                'housing', 2000000,
                'transportation', 500000,
                'other', 0
            );
            
            SET v_NetSalary = v_GrossSalary - v_TotalDeductions + 
                JSON_EXTRACT(v_Bonuses, '$.overtime') +
                JSON_EXTRACT(v_Bonuses, '$.performance') +
                JSON_EXTRACT(v_Allowances, '$.housing') +
                JSON_EXTRACT(v_Allowances, '$.transportation');
            
            -- درج یا به‌روزرسانی حقوق
            INSERT INTO EmployeePayroll (
                PeriodID, EmployeeID, BasicSalary, Allowances, Deductions, Bonuses,
                GrossSalary, TotalDeductions, NetSalary, CalculatedBy, CalculatedAt
            ) VALUES (
                p_PeriodID, v_EmployeeID, v_BasicSalary, v_Allowances, v_Deductions, v_Bonuses,
                v_GrossSalary, v_TotalDeductions, v_NetSalary, p_CalculatedBy, CURRENT_TIMESTAMP
            )
            ON DUPLICATE KEY UPDATE
                BasicSalary = VALUES(BasicSalary),
                Allowances = VALUES(Allowances),
                Deductions = VALUES(Deductions),
                Bonuses = VALUES(Bonuses),
                GrossSalary = VALUES(GrossSalary),
                TotalDeductions = VALUES(TotalDeductions),
                NetSalary = VALUES(NetSalary),
                CalculatedBy = VALUES(CalculatedBy),
                CalculatedAt = VALUES(CalculatedAt);
        END IF;
        
        SET i = i + 1;
    END WHILE;
    
    -- به‌روزرسانی وضعیت دوره
    UPDATE PayrollPeriods 
    SET Status = 'Calculated' 
    WHERE PeriodID = p_PeriodID;
    
    SET p_ResultMessage = 'حقوق کارکنان با موفقیت محاسبه شد';
    COMMIT;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_GetAttendanceReport(
    IN p_CompanyID INT,
    IN p_DepartmentID INT,
    IN p_StartDate DATE,
    IN p_EndDate DATE,
    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,
        COUNT(da.DailyAttendanceID) as WorkingDays,
        SUM(da.TotalWorkMinutes) as TotalWorkMinutes,
        SUM(da.OvertimeMinutes) as TotalOvertime,
        SUM(da.LateMinutes) as TotalLate,
        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 = 'Leave' THEN 1 ELSE 0 END) as LeaveDays,
        ROUND(SUM(da.TotalWorkMinutes) / 60, 2) as TotalWorkHours,
        ROUND(SUM(da.OvertimeMinutes) / 60, 2) as TotalOvertimeHours
    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 p_StartDate AND p_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
    LIMIT p_PageSize OFFSET (p_Page - 1) * p_PageSize;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE sp_GetLeaveReport(
    IN p_CompanyID INT,
    IN p_DepartmentID INT,
    IN p_StartDate DATE,
    IN p_EndDate DATE
)
BEGIN
    SELECT 
        e.EmployeeID,
        e.EmployeeCode,
        CONCAT(e.FirstName, ' ', e.LastName) as EmployeeName,
        d.DepartmentName,
        lt.LeaveTypeName,
        COUNT(el.LeaveID) as LeaveCount,
        SUM(el.TotalDays) as TotalDays,
        MIN(el.StartDate) as FirstLeaveDate,
        MAX(el.StartDate) as LastLeaveDate
    FROM Employees e
    INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
    INNER JOIN EmployeeLeaves el ON e.EmployeeID = el.EmployeeID
    INNER JOIN LeaveTypes lt ON el.LeaveTypeID = lt.LeaveTypeID
    WHERE e.CompanyID = p_CompanyID
    AND (p_DepartmentID IS NULL OR e.DepartmentID = p_DepartmentID)
    AND el.Status = 'Approved'
    AND el.StartDate BETWEEN p_StartDate AND p_EndDate
    GROUP BY e.EmployeeID, lt.LeaveTypeID
    ORDER BY d.DepartmentName, e.FirstName, e.LastName, lt.LeaveTypeName;
    
    -- جمع‌بندی بر اساس نوع مرخصی
    SELECT 
        lt.LeaveTypeName,
        COUNT(el.LeaveID) as TotalRequests,
        SUM(el.TotalDays) as TotalDays,
        AVG(el.TotalDays) as AverageDays
    FROM EmployeeLeaves el
    INNER JOIN LeaveTypes lt ON el.LeaveTypeID = lt.LeaveTypeID
    INNER JOIN Employees e ON el.EmployeeID = e.EmployeeID
    WHERE e.CompanyID = p_CompanyID
    AND (p_DepartmentID IS NULL OR e.DepartmentID = p_DepartmentID)
    AND el.Status = 'Approved'
    AND el.StartDate BETWEEN p_StartDate AND p_EndDate
    GROUP BY lt.LeaveTypeID
    ORDER BY TotalDays DESC;
END$$

DELIMITER ;

<?php
// فراخوانی Stored Procedure برای گرفتن لیست کارکنان
$companyId = 1;
$departmentId = null;
$page = 1;
$pageSize = 20;

$query = "CALL sp_GetEmployees(?, ?, ?, NULL, NULL, ?, ?, @totalRecords, @totalPages)";
$stmt = $conn->prepare($query);
$stmt->bind_param("iiiii", $companyId, $departmentId, $isActive, $page, $pageSize);
$stmt->execute();
$result = $stmt->get_result();

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

// گرفتن خروجی‌ها
$output = $conn->query("SELECT @totalRecords as total_records, @totalPages as total_pages");
$pagination = $output->fetch_assoc();

// فراخوانی برای ایجاد کارمند جدید
$employeeData = [
    'CompanyID' => 1,
    'EmployeeCode' => 'EMP1001',
    'NationalCode' => '1234567890',
    'FirstName' => 'علی',
    'LastName' => 'محمدی',
    'DepartmentID' => 1,
    'PositionID' => 1,
    'EmploymentDate' => '2024-01-15'
];

$query = "CALL sp_CreateEmployee(?, ?, ?, ?, ?, NULL, NULL, 'Male', 'Single', NULL, NULL, NULL, NULL, NULL, ?, ?, 'Permanent', ?, NULL, 0, NULL, NULL, NULL, 1, @employeeId, @resultMessage)";
$stmt = $conn->prepare($query);
$stmt->bind_param(
    "isssssiis",
    $employeeData['CompanyID'],
    $employeeData['EmployeeCode'],
    $employeeData['NationalCode'],
    $employeeData['FirstName'],
    $employeeData['LastName'],
    $employeeData['DepartmentID'],
    $employeeData['PositionID'],
    $employeeData['EmploymentDate']
);
$stmt->execute();

// گرفتن نتیجه
$result = $conn->query("SELECT @employeeId as employee_id, @resultMessage as message");
$output = $result->fetch_assoc();

if ($output['employee_id'] > 0) {
    echo "کارمند با شناسه " . $output['employee_id'] . " ایجاد شد";
} else {
    echo "خطا: " . $output['message'];
}

// فراخوانی برای ایجاد درخواست مرخصی
$leaveData = [
    'EmployeeID' => 1,
    'LeaveTypeID' => 1,
    'StartDate' => '2024-02-01',
    'EndDate' => '2024-02-03',
    'RequestedBy' => 1
];

$query = "CALL sp_CreateLeaveRequest(?, ?, ?, ?, NULL, NULL, NULL, NULL, ?, @leaveId, @resultMessage)";
$stmt = $conn->prepare($query);
$stmt->bind_param(
    "iiiss",
    $leaveData['EmployeeID'],
    $leaveData['LeaveTypeID'],
    $leaveData['StartDate'],
    $leaveData['EndDate'],
    $leaveData['RequestedBy']
);
$stmt->execute();

$result = $conn->query("SELECT @leaveId as leave_id, @resultMessage as message");
$output = $result->fetch_assoc();
?>