ساختار پایگاه داده (اسامی جداول با فیلدهای مورد نیاز) یک سیستم جامع مدیریت پرسنل و سازمان hrm
در اینجا ساختار کامل پایگاه داده برای یک سامانه جامع مدیریت پرسنل و سازمان (HRM) ارائه میدهم:
1. جدول سازمان و واحدها (Departments)
sql
CREATE TABLE Departments ( DepartmentID INT PRIMARY KEY AUTO_INCREMENT, DepartmentCode VARCHAR(20) UNIQUE NOT NULL, DepartmentName NVARCHAR(100) NOT NULL, ParentDepartmentID INT NULL, ManagerID INT NULL, CostCenter VARCHAR(50), Location NVARCHAR(200), Phone VARCHAR(15), Email VARCHAR(100), IsActive BOOLEAN DEFAULT TRUE, SortOrder INT DEFAULT 0, CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP, UpdatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (ParentDepartmentID) REFERENCES Departments(DepartmentID), INDEX idx_parent (ParentDepartmentID), INDEX idx_active (IsActive) );
2. جدول پستهای سازمانی (Positions)
sql
CREATE TABLE Positions ( PositionID INT PRIMARY KEY AUTO_INCREMENT, PositionCode VARCHAR(20) UNIQUE NOT NULL, PositionTitle NVARCHAR(100) NOT NULL, JobDescription TEXT, JobRequirements TEXT, DepartmentID INT NOT NULL, ParentPositionID INT NULL, PositionLevel INT DEFAULT 1, IsHead BOOLEAN DEFAULT FALSE, IsActive BOOLEAN DEFAULT TRUE, CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID), FOREIGN KEY (ParentPositionID) REFERENCES Positions(PositionID), INDEX idx_department (DepartmentID) );
3. جدول کارکنان (Employees)
sql
CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY AUTO_INCREMENT, EmployeeCode VARCHAR(20) UNIQUE NOT NULL, NationalCode VARCHAR(10) UNIQUE, FirstName NVARCHAR(50) NOT NULL, LastName NVARCHAR(50) NOT NULL, FatherName NVARCHAR(50), BirthDate DATE, BirthPlace NVARCHAR(100), Gender ENUM('Male', 'Female') NOT NULL, MaritalStatus ENUM('Single', 'Married', 'Divorced', 'Widowed'), MilitaryStatus ENUM('Exempt', 'Completed', 'EducationalExempt', 'NotApplicable'), InsuranceNumber VARCHAR(20), TaxNumber VARCHAR(20), -- اطلاعات تماس PersonalMobile VARCHAR(11), WorkMobile VARCHAR(11), PersonalEmail VARCHAR(100), WorkEmail VARCHAR(100), EmergencyContact NVARCHAR(100), EmergencyPhone VARCHAR(11), Address TEXT, -- اطلاعات شغلی DepartmentID INT NOT NULL, PositionID INT NOT NULL, EmploymentType ENUM('Permanent', 'Contractual', 'Temporary', 'ProjectBased'), EmploymentDate DATE NOT NULL, EmploymentEndDate DATE NULL, WorkShiftID INT, WorkSchedule JSON, -- برای ساعتهای کاری خاص -- اطلاعات حقوقی BasicSalary DECIMAL(12,2) DEFAULT 0, BankName NVARCHAR(100), BankAccountNumber VARCHAR(30), BankShebaNumber VARCHAR(26), -- وضعیت IsActive BOOLEAN DEFAULT TRUE, Status ENUM('Active', 'Suspended', 'Leave', 'Terminated') DEFAULT 'Active', StatusDescription TEXT, -- سیستم ProfileImage VARCHAR(255), SignatureImage VARCHAR(255), CreatedBy INT, CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP, UpdatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID), FOREIGN KEY (PositionID) REFERENCES Positions(PositionID), FOREIGN KEY (WorkShiftID) REFERENCES WorkShifts(ShiftID), INDEX idx_department (DepartmentID), INDEX idx_active (IsActive), INDEX idx_status (Status) );
4. جدول سوابق تحصیلی (Educations)
sql
CREATE TABLE Educations ( EducationID INT PRIMARY KEY AUTO_INCREMENT, EmployeeID INT NOT NULL, EducationLevel ENUM('Diploma', 'Associate', 'Bachelor', 'Master', 'PhD', 'PostDoc'), FieldOfStudy NVARCHAR(100), University NVARCHAR(100), StartDate DATE, EndDate DATE, GPA DECIMAL(3,2), IsCurrent BOOLEAN DEFAULT FALSE, Description TEXT, DocumentFile VARCHAR(255), CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID), INDEX idx_employee (EmployeeID) );
5. جدول سوابق کاری (WorkExperiences)
sql
CREATE TABLE WorkExperiences ( ExperienceID INT PRIMARY KEY AUTO_INCREMENT, EmployeeID INT NOT NULL, CompanyName NVARCHAR(100) NOT NULL, JobTitle NVARCHAR(100), StartDate DATE, EndDate DATE, IsCurrent BOOLEAN DEFAULT FALSE, Salary DECIMAL(10,2), LeaveReason TEXT, Achievements TEXT, ReferenceContact VARCHAR(100), CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID), INDEX idx_employee (EmployeeID) );
6. جدول مهارتها (Skills)
sql
CREATE TABLE Skills ( SkillID INT PRIMARY KEY AUTO_INCREMENT, SkillName NVARCHAR(100) NOT NULL, SkillCategory NVARCHAR(50), Description TEXT, IsActive BOOLEAN DEFAULT TRUE, CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE EmployeeSkills ( EmployeeSkillID INT PRIMARY KEY AUTO_INCREMENT, EmployeeID INT NOT NULL, SkillID INT NOT NULL, ProficiencyLevel ENUM('Beginner', 'Intermediate', 'Advanced', 'Expert'), YearsOfExperience INT DEFAULT 0, CertificateFile VARCHAR(255), VerifiedBy INT NULL, VerifiedAt DATETIME NULL, CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID), FOREIGN KEY (SkillID) REFERENCES Skills(SkillID), FOREIGN KEY (VerifiedBy) REFERENCES Employees(EmployeeID), UNIQUE KEY unique_employee_skill (EmployeeID, SkillID) );
7. جدول دورههای آموزشی (Trainings)
sql
CREATE TABLE Trainings ( TrainingID INT PRIMARY KEY AUTO_INCREMENT, TrainingCode VARCHAR(20) UNIQUE, TrainingName NVARCHAR(200) NOT NULL, TrainingType ENUM('Internal', 'External', 'Online'), Provider NVARCHAR(100), StartDate DATE, EndDate DATE, DurationHours INT, Cost DECIMAL(10,2), Location NVARCHAR(200), Description TEXT, IsActive BOOLEAN DEFAULT TRUE, CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE EmployeeTrainings ( EmployeeTrainingID INT PRIMARY KEY AUTO_INCREMENT, EmployeeID INT NOT NULL, TrainingID INT NOT NULL, EnrollmentDate DATE, CompletionDate DATE, Status ENUM('Registered', 'InProgress', 'Completed', 'Cancelled'), Score DECIMAL(5,2), CertificateFile VARCHAR(255), Feedback TEXT, CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID), FOREIGN KEY (TrainingID) REFERENCES Trainings(TrainingID), INDEX idx_employee (EmployeeID) );
8. جدول ارزیابی عملکرد (PerformanceReviews)
sql
CREATE TABLE PerformanceReviews ( ReviewID INT PRIMARY KEY AUTO_INCREMENT, EmployeeID INT NOT NULL, ReviewDate DATE NOT NULL, ReviewerID INT NOT NULL, -- ارزیاب ReviewPeriod ENUM('Monthly', 'Quarterly', 'SemiAnnual', 'Annual'), StartDate DATE, EndDate DATE, OverallScore DECIMAL(5,2), OverallRating ENUM('Excellent', 'Good', 'Average', 'BelowAverage', 'Poor'), Strengths TEXT, ImprovementAreas TEXT, Goals TEXT, EmployeeComments TEXT, ReviewerComments TEXT, Status ENUM('Draft', 'Completed', 'Approved', 'Rejected') DEFAULT 'Draft', NextReviewDate DATE, CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID), FOREIGN KEY (ReviewerID) REFERENCES Employees(EmployeeID), INDEX idx_employee (EmployeeID), INDEX idx_reviewer (ReviewerID) ); CREATE TABLE ReviewItems ( ReviewItemID INT PRIMARY KEY AUTO_INCREMENT, ReviewID INT NOT NULL, ItemName NVARCHAR(200) NOT NULL, ItemDescription TEXT, Weight DECIMAL(5,2) DEFAULT 100, -- وزن در ارزیابی Score DECIMAL(5,2), MaxScore DECIMAL(5,2) DEFAULT 100, Comments TEXT, FOREIGN KEY (ReviewID) REFERENCES PerformanceReviews(ReviewID) ON DELETE CASCADE );
9. جدول حقوق و دستمزد (Payroll)
sql
CREATE TABLE PayrollPeriods ( PeriodID INT PRIMARY KEY AUTO_INCREMENT, PeriodName NVARCHAR(100) NOT NULL, PeriodYear INT NOT NULL, PeriodMonth INT NOT NULL, StartDate DATE NOT NULL, EndDate DATE NOT NULL, PaymentDate DATE, Status ENUM('Draft', 'Calculated', 'Approved', 'Paid', 'Closed') DEFAULT 'Draft', CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY unique_period (PeriodYear, PeriodMonth) ); CREATE TABLE EmployeePayroll ( PayrollID INT PRIMARY KEY AUTO_INCREMENT, PeriodID INT NOT NULL, EmployeeID INT NOT NULL, -- اقلام حقوق BasicSalary DECIMAL(12,2) DEFAULT 0, Allowances JSON, -- {"housing": 1000000, "transportation": 500000, ...} Deductions JSON, -- {"tax": 200000, "insurance": 150000, ...} Bonuses JSON, -- {"performance": 500000, "attendance": 200000, ...} -- محاسبات GrossSalary DECIMAL(12,2) DEFAULT 0, TotalDeductions DECIMAL(12,2) DEFAULT 0, NetSalary DECIMAL(12,2) DEFAULT 0, -- اطلاعات پرداخت PaymentMethod ENUM('Cash', 'BankTransfer', 'Check'), PaymentStatus ENUM('Pending', 'Paid', 'Failed'), PaymentReference VARCHAR(100), -- تاییدیه CalculatedBy INT, CalculatedAt DATETIME, ApprovedBy INT, ApprovedAt DATETIME, CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (PeriodID) REFERENCES PayrollPeriods(PeriodID), FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID), FOREIGN KEY (CalculatedBy) REFERENCES Employees(EmployeeID), FOREIGN KEY (ApprovedBy) REFERENCES Employees(EmployeeID), UNIQUE KEY unique_employee_period (EmployeeID, PeriodID) );
10. جدول مرخصیها و غیبتها (Leaves)
sql
CREATE TABLE LeaveTypes ( LeaveTypeID INT PRIMARY KEY AUTO_INCREMENT, LeaveTypeCode VARCHAR(20) UNIQUE NOT NULL, LeaveTypeName NVARCHAR(100) NOT NULL, IsPaid BOOLEAN DEFAULT TRUE, MaxDaysPerYear INT DEFAULT 0, CarryForward BOOLEAN DEFAULT FALSE, RequiresApproval BOOLEAN DEFAULT TRUE, IsActive BOOLEAN DEFAULT TRUE, CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE EmployeeLeaves ( LeaveID INT PRIMARY KEY AUTO_INCREMENT, EmployeeID INT NOT NULL, LeaveTypeID INT NOT NULL, StartDate DATE NOT NULL, EndDate DATE NOT NULL, StartTime TIME NULL, EndTime TIME NULL, TotalDays DECIMAL(4,2) DEFAULT 0, Reason TEXT, ContactDuringLeave VARCHAR(100), Status ENUM('Pending', 'Approved', 'Rejected', 'Cancelled') DEFAULT 'Pending', -- مسیر approval RequestedBy INT NOT NULL, RequestedAt DATETIME DEFAULT CURRENT_TIMESTAMP, ApprovedBy INT NULL, ApprovedAt DATETIME NULL, RejectedBy INT NULL, RejectedAt DATETIME NULL, RejectionReason TEXT, CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID), FOREIGN KEY (LeaveTypeID) REFERENCES LeaveTypes(LeaveTypeID), FOREIGN KEY (RequestedBy) REFERENCES Employees(EmployeeID), FOREIGN KEY (ApprovedBy) REFERENCES Employees(EmployeeID), FOREIGN KEY (RejectedBy) REFERENCES Employees(EmployeeID), INDEX idx_employee (EmployeeID), INDEX idx_status (Status) ); CREATE TABLE EmployeeLeaveBalances ( BalanceID INT PRIMARY KEY AUTO_INCREMENT, EmployeeID INT NOT NULL, LeaveTypeID INT NOT NULL, Year INT NOT NULL, TotalEntitlement DECIMAL(4,2) DEFAULT 0, UsedBalance DECIMAL(4,2) DEFAULT 0, RemainingBalance DECIMAL(4,2) DEFAULT 0, CarryForwardFromPrevious DECIMAL(4,2) DEFAULT 0, UpdatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID), FOREIGN KEY (LeaveTypeID) REFERENCES LeaveTypes(LeaveTypeID), UNIQUE KEY unique_employee_leave_year (EmployeeID, LeaveTypeID, Year) );
11. جدول حضور و غیاب (Attendance)
sql
CREATE TABLE AttendanceRecords ( RecordID BIGINT PRIMARY KEY AUTO_INCREMENT, EmployeeID INT NOT NULL, RecordDate DATE NOT NULL, RecordTime TIME NOT NULL, RecordType ENUM('CheckIn', 'CheckOut', 'BreakStart', 'BreakEnd'), DeviceID INT, Location VARCHAR(100), VerificationMethod ENUM('Fingerprint', 'Card', 'Face', 'PIN'), IsManual BOOLEAN DEFAULT FALSE, ManualReason TEXT, CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID), INDEX idx_employee_date (EmployeeID, RecordDate), INDEX idx_date (RecordDate) ); CREATE TABLE DailyAttendance ( DailyAttendanceID BIGINT PRIMARY KEY AUTO_INCREMENT, EmployeeID INT NOT NULL, AttendanceDate DATE NOT NULL, ScheduledHours DECIMAL(4,2) DEFAULT 8.0, ActualHours DECIMAL(4,2) DEFAULT 0, OvertimeHours DECIMAL(4,2) DEFAULT 0, LateMinutes INT DEFAULT 0, EarlyDepartureMinutes INT DEFAULT 0, Status ENUM('Present', 'Absent', 'Late', 'HalfDay', 'Holiday', 'Leave'), CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID), UNIQUE KEY unique_employee_date (EmployeeID, AttendanceDate) );
12. جدول مستندات (Documents)
sql
CREATE TABLE DocumentTypes ( DocumentTypeID INT PRIMARY KEY AUTO_INCREMENT, TypeName NVARCHAR(100) NOT NULL, TypeCode VARCHAR(50) UNIQUE, IsRequired BOOLEAN DEFAULT FALSE, IsActive BOOLEAN DEFAULT TRUE, CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE EmployeeDocuments ( DocumentID INT PRIMARY KEY AUTO_INCREMENT, EmployeeID INT NOT NULL, DocumentTypeID INT NOT NULL, DocumentName NVARCHAR(200) NOT NULL, DocumentNumber VARCHAR(100), IssueDate DATE, ExpiryDate DATE, IssuingAuthority NVARCHAR(100), FilePath VARCHAR(500), FileSize INT, IsVerified BOOLEAN DEFAULT FALSE, VerifiedBy INT, VerifiedAt DATETIME, Notes TEXT, CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID), FOREIGN KEY (DocumentTypeID) REFERENCES DocumentTypes(DocumentTypeID), FOREIGN KEY (VerifiedBy) REFERENCES Employees(EmployeeID), INDEX idx_employee (EmployeeID), INDEX idx_expiry (ExpiryDate) );
13. جدول تنظیمات سیستم (SystemSettings)
sql
CREATE TABLE SystemSettings ( SettingID INT PRIMARY KEY AUTO_INCREMENT, SettingKey VARCHAR(100) UNIQUE NOT NULL, SettingValue TEXT, SettingType ENUM('String', 'Number', 'Boolean', 'JSON', 'Date'), SettingGroup VARCHAR(50) DEFAULT 'General', Description TEXT, IsEncrypted BOOLEAN DEFAULT FALSE, UpdatedBy INT, UpdatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (UpdatedBy) REFERENCES Employees(EmployeeID) );
14. جدول لاگ سیستم (SystemLogs)
sql
CREATE TABLE SystemLogs ( LogID BIGINT PRIMARY KEY AUTO_INCREMENT, LogLevel ENUM('Info', 'Warning', 'Error', 'Debug'), Module VARCHAR(100), Action VARCHAR(100), UserID INT NULL, Description TEXT, IPAddress VARCHAR(45), UserAgent TEXT, RequestData JSON, CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (UserID) REFERENCES Employees(EmployeeID), INDEX idx_module (Module), INDEX idx_created (CreatedAt) );
دادههای اولیه:
sql
-- درج واحدهای سازمانی نمونه INSERT INTO Departments (DepartmentCode, DepartmentName, ParentDepartmentID) VALUES ('HR', 'مدیریت منابع انسانی', NULL), ('FIN', 'امور مالی', NULL), ('IT', 'فناوری اطلاعات', NULL), ('SALES', 'فروش', NULL); -- درج انواع مرخصی INSERT INTO LeaveTypes (LeaveTypeCode, LeaveTypeName, IsPaid, MaxDaysPerYear) VALUES ('ANNUAL', 'مرخصی استحقاقی', TRUE, 26), ('SICK', 'مرخصی استعلاجی', TRUE, 30), ('MATERNITY', 'مرخصی زایمان', TRUE, 180), ('UNPAID', 'مرخصی بدون حقوق', FALSE, 0); -- درج تنظیمات سیستم INSERT INTO SystemSettings (SettingKey, SettingValue, SettingType, SettingGroup) VALUES ('company_name', 'شرکت نمونه', 'String', 'General'), ('work_hours_per_day', '8', 'Number', 'Attendance'), ('currency', 'ریال', 'String', 'Payroll'), ('tax_percentage', '10', 'Number', 'Payroll');
این ساختار پایگاه داده یک سیستم HRM کاملاً جامع را ارائه میدهد که میتواند نیازهای سازمانهای کوچک تا بزرگ را پوشش دهد.
در اینجا API های کامل برای مدیریت سیستم HRM ارائه میدهم:
1. فایل پیکربندی (config.php
)
php
<?php
header("Content-Type: application/json; charset=utf-8");
header("Access-Control-Allow-Origin: *");
header("Access-Control-Allow-Headers: Content-Type, Authorization");
header("Access-Control-Allow-Methods: GET, POST, PUT, DELETE, OPTIONS");
if ($_SERVER['REQUEST_METHOD'] == 'OPTIONS') {
exit(0);
}
// تنظیمات دیتابیس
define('DB_HOST', 'localhost');
define('DB_USER', 'username');
define('DB_PASS', 'password');
define('DB_NAME', 'hrm_system');
define('DB_CHARSET', 'utf8mb4');
// تابع اتصال به دیتابیس
function getDBConnection() {
$conn = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
if ($conn->connect_error) {
http_response_code(500);
echo json_encode(['success' => false, 'message' => 'Database connection failed']);
exit;
}
$conn->set_charset(DB_CHARSET);
return $conn;
}
// تابع پاسخ استاندارد
function sendResponse($success, $data = null, $message = '', $statusCode = 200) {
http_response_code($statusCode);
echo json_encode([
'success' => $success,
'message' => $message,
'data' => $data,
'timestamp' => date('Y-m-d H:i:s')
], JSON_UNESCAPED_UNICODE);
exit;
}
// تابع اعتبارسنجی توکن (JWT ساده)
function validateToken() {
$headers = getallheaders();
$token = isset($headers['Authorization']) ? str_replace('Bearer ', '', $headers['Authorization']) : '';
if (empty($token)) {
sendResponse(false, null, 'Access token required', 401);
}
// در اینجا منطق اعتبارسنجی توکن را پیادهسازی کنید
return true;
}
?>
2. API مدیریت کارکنان (employees_api.php
)
php
<?php
include 'config.php';
$method = $_SERVER['REQUEST_METHOD'];
$conn = getDBConnection();
switch ($method) {
case 'GET':
getEmployees();
break;
case 'POST':
addEmployee();
break;
case 'PUT':
updateEmployee();
break;
case 'DELETE':
deleteEmployee();
break;
default:
sendResponse(false, null, 'Method not allowed', 405);
}
function getEmployees() {
global $conn;
$employeeId = $_GET['employee_id'] ?? null;
$departmentId = $_GET['department_id'] ?? null;
$isActive = $_GET['is_active'] ?? 1;
$page = $_GET['page'] ?? 1;
$limit = $_GET['limit'] ?? 10;
$offset = ($page - 1) * $limit;
$query = "
SELECT
e.*,
d.DepartmentName,
p.PositionTitle,
ws.ShiftName
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.IsActive = ?
";
$params = [$isActive];
$types = "i";
if ($employeeId) {
$query .= " AND e.EmployeeID = ?";
$params[] = $employeeId;
$types .= "i";
}
if ($departmentId) {
$query .= " AND e.DepartmentID = ?";
$params[] = $departmentId;
$types .= "i";
}
// گرفتن تعداد کل
$countQuery = "SELECT COUNT(*) as total FROM Employees e WHERE e.IsActive = ?";
$countStmt = $conn->prepare($countQuery);
$countStmt->bind_param("i", $isActive);
$countStmt->execute();
$totalResult = $countStmt->get_result();
$total = $totalResult->fetch_assoc()['total'];
$query .= " LIMIT ? OFFSET ?";
$params[] = $limit;
$params[] = $offset;
$types .= "ii";
$stmt = $conn->prepare($query);
$stmt->bind_param($types, ...$params);
$stmt->execute();
$result = $stmt->get_result();
$employees = [];
while ($row = $result->fetch_assoc()) {
$employees[] = $row;
}
sendResponse(true, [
'employees' => $employees,
'pagination' => [
'page' => intval($page),
'limit' => intval($limit),
'total' => $total,
'pages' => ceil($total / $limit)
]
], 'Employees retrieved successfully');
}
function addEmployee() {
global $conn;
$data = json_decode(file_get_contents("php://input"), true);
$required = ['EmployeeCode', 'FirstName', 'LastName', 'NationalCode', 'DepartmentID', 'PositionID', 'EmploymentDate'];
foreach ($required as $field) {
if (!isset($data[$field]) || empty($data[$field])) {
sendResponse(false, null, "Field $field is required", 400);
}
}
$query = "
INSERT INTO Employees (
EmployeeCode, NationalCode, FirstName, LastName, FatherName, BirthDate,
BirthPlace, Gender, MaritalStatus, MilitaryStatus, InsuranceNumber, TaxNumber,
PersonalMobile, WorkMobile, PersonalEmail, WorkEmail, EmergencyContact, EmergencyPhone, Address,
DepartmentID, PositionID, EmploymentType, EmploymentDate, EmploymentEndDate, WorkShiftID,
BasicSalary, BankName, BankAccountNumber, BankShebaNumber, ProfileImage, CreatedBy
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
";
$stmt = $conn->prepare($query);
$stmt->bind_param(
"sssssssssssssssssssiisssisdsssssi",
$data['EmployeeCode'], $data['NationalCode'], $data['FirstName'], $data['LastName'],
$data['FatherName'] ?? null, $data['BirthDate'] ?? null, $data['BirthPlace'] ?? null,
$data['Gender'], $data['MaritalStatus'] ?? null, $data['MilitaryStatus'] ?? null,
$data['InsuranceNumber'] ?? null, $data['TaxNumber'] ?? null,
$data['PersonalMobile'] ?? null, $data['WorkMobile'] ?? null,
$data['PersonalEmail'] ?? null, $data['WorkEmail'] ?? null,
$data['EmergencyContact'] ?? null, $data['EmergencyPhone'] ?? null,
$data['Address'] ?? null,
$data['DepartmentID'], $data['PositionID'], $data['EmploymentType'] ?? 'Permanent',
$data['EmploymentDate'], $data['EmploymentEndDate'] ?? null,
$data['WorkShiftID'] ?? null, $data['BasicSalary'] ?? 0,
$data['BankName'] ?? null, $data['BankAccountNumber'] ?? null,
$data['BankShebaNumber'] ?? null, $data['ProfileImage'] ?? null,
$data['CreatedBy'] ?? 1
);
if ($stmt->execute()) {
sendResponse(true, ['employee_id' => $stmt->insert_id], 'Employee added successfully', 201);
} else {
sendResponse(false, null, 'Failed to add employee: ' . $stmt->error, 500);
}
}
function updateEmployee() {
global $conn;
$data = json_decode(file_get_contents("php://input"), true);
$employeeId = $_GET['employee_id'] ?? $data['employee_id'];
if (!$employeeId) {
sendResponse(false, null, 'Employee ID is required', 400);
}
$fields = [];
$params = [];
$types = "";
$updatableFields = [
'FirstName', 'LastName', 'FatherName', 'BirthDate', 'BirthPlace', 'Gender',
'MaritalStatus', 'MilitaryStatus', 'PersonalMobile', 'WorkMobile',
'PersonalEmail', 'WorkEmail', 'EmergencyContact', 'EmergencyPhone', 'Address',
'DepartmentID', 'PositionID', 'EmploymentType', 'EmploymentEndDate',
'WorkShiftID', 'BasicSalary', 'BankName', 'BankAccountNumber', 'BankShebaNumber',
'ProfileImage', 'IsActive', 'Status'
];
foreach ($updatableFields as $field) {
if (isset($data[$field])) {
$fields[] = "$field = ?";
$params[] = $data[$field];
$types .= "s";
}
}
if (empty($fields)) {
sendResponse(false, null, 'No fields to update', 400);
}
$params[] = $employeeId;
$types .= "i";
$query = "UPDATE Employees SET " . implode(", ", $fields) . " WHERE EmployeeID = ?";
$stmt = $conn->prepare($query);
$stmt->bind_param($types, ...$params);
if ($stmt->execute()) {
sendResponse(true, null, 'Employee updated successfully');
} else {
sendResponse(false, null, 'Failed to update employee: ' . $stmt->error, 500);
}
}
function deleteEmployee() {
global $conn;
$employeeId = $_GET['employee_id'];
if (!$employeeId) {
sendResponse(false, null, 'Employee ID is required', 400);
}
// soft delete
$stmt = $conn->prepare("UPDATE Employees SET IsActive = 0 WHERE EmployeeID = ?");
$stmt->bind_param("i", $employeeId);
if ($stmt->execute()) {
sendResponse(true, null, 'Employee deleted successfully');
} else {
sendResponse(false, null, 'Failed to delete employee', 500);
}
}
?>
3. API مدیریت دپارتمانها (departments_api.php
)
php
<?php
include 'config.php';
$method = $_SERVER['REQUEST_METHOD'];
$conn = getDBConnection();
switch ($method) {
case 'GET':
getDepartments();
break;
case 'POST':
manageDepartment('add');
break;
case 'PUT':
manageDepartment('update');
break;
case 'DELETE':
deleteDepartment();
break;
}
function getDepartments() {
global $conn;
$departmentId = $_GET['department_id'] ?? null;
$includeTree = $_GET['include_tree'] ?? false;
if ($includeTree) {
getDepartmentTree();
return;
}
$query = "SELECT * FROM Departments WHERE IsActive = 1";
$params = [];
$types = "";
if ($departmentId) {
$query .= " AND DepartmentID = ?";
$params[] = $departmentId;
$types .= "i";
}
$query .= " ORDER BY SortOrder, DepartmentName";
$stmt = $conn->prepare($query);
if (!empty($params)) {
$stmt->bind_param($types, ...$params);
}
$stmt->execute();
$result = $stmt->get_result();
$departments = [];
while ($row = $result->fetch_assoc()) {
$departments[] = $row;
}
sendResponse(true, $departments, 'Departments retrieved successfully');
}
function getDepartmentTree() {
global $conn;
$stmt = $conn->prepare("
SELECT d1.*, d2.DepartmentName as ParentDepartmentName,
COUNT(e.EmployeeID) as EmployeeCount
FROM Departments d1
LEFT JOIN Departments d2 ON d1.ParentDepartmentID = d2.DepartmentID
LEFT JOIN Employees e ON d1.DepartmentID = e.DepartmentID AND e.IsActive = 1
WHERE d1.IsActive = 1
GROUP BY d1.DepartmentID
ORDER BY d1.SortOrder, d1.DepartmentName
");
$stmt->execute();
$result = $stmt->get_result();
$departments = [];
while ($row = $result->fetch_assoc()) {
$departments[] = $row;
}
// ساختار درختی
$tree = buildTree($departments);
sendResponse(true, $tree, 'Department tree retrieved successfully');
}
function buildTree($elements, $parentId = null) {
$branch = [];
foreach ($elements as $element) {
if ($element['ParentDepartmentID'] == $parentId) {
$children = buildTree($elements, $element['DepartmentID']);
if ($children) {
$element['children'] = $children;
}
$branch[] = $element;
}
}
return $branch;
}
function manageDepartment($action) {
global $conn;
$data = json_decode(file_get_contents("php://input"), true);
if ($action === 'add') {
$query = "
INSERT INTO Departments (DepartmentCode, DepartmentName, ParentDepartmentID, ManagerID,
CostCenter, Location, Phone, Email, SortOrder)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
";
$stmt = $conn->prepare($query);
$stmt->bind_param(
"ssiissssi",
$data['DepartmentCode'], $data['DepartmentName'], $data['ParentDepartmentID'] ?? null,
$data['ManagerID'] ?? null, $data['CostCenter'] ?? null, $data['Location'] ?? null,
$data['Phone'] ?? null, $data['Email'] ?? null, $data['SortOrder'] ?? 0
);
if ($stmt->execute()) {
sendResponse(true, ['department_id' => $stmt->insert_id], 'Department added successfully', 201);
} else {
sendResponse(false, null, 'Failed to add department', 500);
}
} else {
$departmentId = $_GET['department_id'] ?? $data['DepartmentID'];
$query = "
UPDATE Departments SET
DepartmentName = ?, ParentDepartmentID = ?, ManagerID = ?,
CostCenter = ?, Location = ?, Phone = ?, Email = ?, SortOrder = ?
WHERE DepartmentID = ?
";
$stmt = $conn->prepare($query);
$stmt->bind_param(
"siissssii",
$data['DepartmentName'], $data['ParentDepartmentID'] ?? null,
$data['ManagerID'] ?? null, $data['CostCenter'] ?? null,
$data['Location'] ?? null, $data['Phone'] ?? null,
$data['Email'] ?? null, $data['SortOrder'] ?? 0,
$departmentId
);
if ($stmt->execute()) {
sendResponse(true, null, 'Department updated successfully');
} else {
sendResponse(false, null, 'Failed to update department', 500);
}
}
}
function deleteDepartment() {
global $conn;
$departmentId = $_GET['department_id'];
// بررسی وجود کارمند در دپارتمان
$checkStmt = $conn->prepare("SELECT COUNT(*) as count FROM Employees WHERE DepartmentID = ? AND IsActive = 1");
$checkStmt->bind_param("i", $departmentId);
$checkStmt->execute();
$result = $checkStmt->get_result();
$count = $result->fetch_assoc()['count'];
if ($count > 0) {
sendResponse(false, null, 'Cannot delete department with active employees', 400);
}
$stmt = $conn->prepare("UPDATE Departments SET IsActive = 0 WHERE DepartmentID = ?");
$stmt->bind_param("i", $departmentId);
if ($stmt->execute()) {
sendResponse(true, null, 'Department deleted successfully');
} else {
sendResponse(false, null, 'Failed to delete department', 500);
}
}
?>
4. API مدیریت حقوق و دستمزد (payroll_api.php
)
php
<?php
include 'config.php';
$method = $_SERVER['REQUEST_METHOD'];
$conn = getDBConnection();
switch ($method) {
case 'GET':
getPayroll();
break;
case 'POST':
calculatePayroll();
break;
case 'PUT':
updatePayrollStatus();
break;
}
function getPayroll() {
global $conn;
$periodId = $_GET['period_id'] ?? null;
$employeeId = $_GET['employee_id'] ?? null;
$year = $_GET['year'] ?? date('Y');
$month = $_GET['month'] ?? date('m');
if ($periodId) {
$query = "
SELECT ep.*, e.FirstName, e.LastName, e.EmployeeCode,
p.PeriodName, p.PeriodYear, p.PeriodMonth, p.Status as PeriodStatus
FROM EmployeePayroll ep
INNER JOIN Employees e ON ep.EmployeeID = e.EmployeeID
INNER JOIN PayrollPeriods p ON ep.PeriodID = p.PeriodID
WHERE ep.PeriodID = ?
";
if ($employeeId) {
$query .= " AND ep.EmployeeID = ?";
$stmt = $conn->prepare($query);
$stmt->bind_param("ii", $periodId, $employeeId);
} else {
$stmt = $conn->prepare($query);
$stmt->bind_param("i", $periodId);
}
} else {
$query = "
SELECT p.*,
COUNT(ep.PayrollID) as EmployeeCount,
SUM(ep.NetSalary) as TotalNetSalary
FROM PayrollPeriods p
LEFT JOIN EmployeePayroll ep ON p.PeriodID = ep.PeriodID
WHERE p.PeriodYear = ? AND p.PeriodMonth = ?
GROUP BY p.PeriodID
";
$stmt = $conn->prepare($query);
$stmt->bind_param("ii", $year, $month);
}
$stmt->execute();
$result = $stmt->get_result();
if ($periodId && !$employeeId) {
$payrolls = [];
while ($row = $result->fetch_assoc()) {
$payrolls[] = $row;
}
sendResponse(true, $payrolls, 'Payroll data retrieved successfully');
} else {
$data = $result->fetch_assoc();
sendResponse(true, $data, 'Payroll period data retrieved successfully');
}
}
function calculatePayroll() {
global $conn;
$data = json_decode(file_get_contents("php://input"), true);
$periodId = $data['period_id'] ?? null;
$employeeIds = $data['employee_ids'] ?? [];
if (!$periodId) {
sendResponse(false, null, 'Period ID is required', 400);
}
// شروع تراکنش
$conn->begin_transaction();
try {
// محاسبات حقوق برای هر کارمند
foreach ($employeeIds as $employeeId) {
// اینجا منطق محاسبه حقوق پیادهسازی شود
$basicSalary = 0;
$allowances = json_encode([]);
$deductions = json_encode([]);
$bonuses = json_encode([]);
$grossSalary = $basicSalary;
$totalDeductions = 0;
$netSalary = $grossSalary - $totalDeductions;
$query = "
INSERT INTO EmployeePayroll
(PeriodID, EmployeeID, BasicSalary, Allowances, Deductions, Bonuses,
GrossSalary, TotalDeductions, NetSalary, CalculatedBy, CalculatedAt)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())
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)
";
$stmt = $conn->prepare($query);
$stmt->bind_param(
"iidsssdddi",
$periodId, $employeeId, $basicSalary, $allowances, $deductions, $bonuses,
$grossSalary, $totalDeductions, $netSalary, $data['calculated_by']
);
$stmt->execute();
}
// آپدیت وضعیت دوره
$updateStmt = $conn->prepare("UPDATE PayrollPeriods SET Status = 'Calculated' WHERE PeriodID = ?");
$updateStmt->bind_param("i", $periodId);
$updateStmt->execute();
$conn->commit();
sendResponse(true, null, 'Payroll calculated successfully');
} catch (Exception $e) {
$conn->rollback();
sendResponse(false, null, 'Payroll calculation failed: ' . $e->getMessage(), 500);
}
}
?>
5. API مدیریت مرخصیها (leaves_api.php
)
php
<?php
include 'config.php';
$method = $_SERVER['REQUEST_METHOD'];
$conn = getDBConnection();
switch ($method) {
case 'GET':
getLeaves();
break;
case 'POST':
manageLeave('request');
break;
case 'PUT':
manageLeave('update');
break;
}
function getLeaves() {
global $conn;
$leaveId = $_GET['leave_id'] ?? null;
$employeeId = $_GET['employee_id'] ?? null;
$status = $_GET['status'] ?? null;
$startDate = $_GET['start_date'] ?? null;
$endDate = $_GET['end_date'] ?? null;
$query = "
SELECT el.*,
e.FirstName, e.LastName, e.EmployeeCode,
lt.LeaveTypeName, lt.IsPaid,
a.FirstName as ApproverFirstName, a.LastName as ApproverLastName
FROM EmployeeLeaves el
INNER JOIN Employees e ON el.EmployeeID = e.EmployeeID
INNER JOIN LeaveTypes lt ON el.LeaveTypeID = lt.LeaveTypeID
LEFT JOIN Employees a ON el.ApprovedBy = a.EmployeeID
WHERE 1=1
";
$params = [];
$types = "";
if ($leaveId) {
$query .= " AND el.LeaveID = ?";
$params[] = $leaveId;
$types .= "i";
}
if ($employeeId) {
$query .= " AND el.EmployeeID = ?";
$params[] = $employeeId;
$types .= "i";
}
if ($status) {
$query .= " AND el.Status = ?";
$params[] = $status;
$types .= "s";
}
if ($startDate) {
$query .= " AND el.StartDate >= ?";
$params[] = $startDate;
$types .= "s";
}
if ($endDate) {
$query .= " AND el.EndDate <= ?";
$params[] = $endDate;
$types .= "s";
}
$query .= " ORDER BY el.StartDate DESC";
$stmt = $conn->prepare($query);
if (!empty($params)) {
$stmt->bind_param($types, ...$params);
}
$stmt->execute();
$result = $stmt->get_result();
$leaves = [];
while ($row = $result->fetch_assoc()) {
$leaves[] = $row;
}
sendResponse(true, $leaves, 'Leaves retrieved successfully');
}
function manageLeave($action) {
global $conn;
$data = json_decode(file_get_contents("php://input"), true);
if ($action === 'request') {
$query = "
INSERT INTO EmployeeLeaves
(EmployeeID, LeaveTypeID, StartDate, EndDate, StartTime, EndTime,
TotalDays, Reason, ContactDuringLeave, RequestedBy)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
";
// محاسبه تعداد روزها
$start = new DateTime($data['StartDate']);
$end = new DateTime($data['EndDate']);
$totalDays = $start->diff($end)->days + 1;
$stmt = $conn->prepare($query);
$stmt->bind_param(
"iissssdssi",
$data['EmployeeID'], $data['LeaveTypeID'], $data['StartDate'], $data['EndDate'],
$data['StartTime'] ?? null, $data['EndTime'] ?? null, $totalDays,
$data['Reason'] ?? '', $data['ContactDuringLeave'] ?? '',
$data['RequestedBy']
);
if ($stmt->execute()) {
sendResponse(true, ['leave_id' => $stmt->insert_id], 'Leave request submitted successfully', 201);
} else {
sendResponse(false, null, 'Failed to submit leave request', 500);
}
} else {
// approve/reject leave
$leaveId = $data['leave_id'];
$status = $data['status'];
$approvedBy = $data['approved_by'];
if ($status === 'Approved') {
$query = "
UPDATE EmployeeLeaves
SET Status = ?, ApprovedBy = ?, ApprovedAt = NOW()
WHERE LeaveID = ?
";
$stmt = $conn->prepare($query);
$stmt->bind_param("sii", $status, $approvedBy, $leaveId);
} else {
$query = "
UPDATE EmployeeLeaves
SET Status = ?, RejectedBy = ?, RejectedAt = NOW(), RejectionReason = ?
WHERE LeaveID = ?
";
$stmt = $conn->prepare($query);
$stmt->bind_param("sisi", $status, $approvedBy, $data['rejection_reason'], $leaveId);
}
if ($stmt->execute()) {
sendResponse(true, null, 'Leave request updated successfully');
} else {
sendResponse(false, null, 'Failed to update leave request', 500);
}
}
}
?>
6. فایل روتر اصلی (router.php
)
php
<?php
include 'config.php';
$request_uri = $_SERVER['REQUEST_URI'];
$method = $_SERVER['REQUEST_METHOD'];
$path = parse_url($request_uri, PHP_URL_PATH);
// مسیرهای API
$routes = [
'GET' => [
'/api/employees' => 'employees_api.php',
'/api/departments' => 'departments_api.php',
'/api/positions' => 'positions_api.php',
'/api/payroll' => 'payroll_api.php',
'/api/leaves' => 'leaves_api.php',
'/api/attendance' => 'attendance_api.php',
'/api/reports/employees' => 'reports_api.php?type=employees',
'/api/reports/attendance' => 'reports_api.php?type=attendance'
],
'POST' => [
'/api/employees' => 'employees_api.php',
'/api/departments' => 'departments_api.php',
'/api/payroll/calculate' => 'payroll_api.php',
'/api/leaves/request' => 'leaves_api.php',
'/api/attendance/record' => 'attendance_api.php'
],
'PUT' => [
'/api/employees' => 'employees_api.php',
'/api/departments' => 'departments_api.php',
'/api/leaves/approve' => 'leaves_api.php',
'/api/payroll/approve' => 'payroll_api.php'
],
'DELETE' => [
'/api/employees' => 'employees_api.php',
'/api/departments' => 'departments_api.php'
]
];
// پیدا کردن مسیر منطبق
$matched = false;
foreach ($routes[$method] as $route => $file) {
if (strpos($path, $route) === 0) {
include($file);
$matched = true;
break;
}
}
if (!$matched) {
sendResponse(false, null, 'Endpoint not found', 404);
}
?>
7. مثال استفاده از API ها
javascript
// گرفتن لیست کارکنان fetch('/api/employees?page=1&limit=10') .then(response => response.json()) .then(data => console.log(data)); // افزودن کارمند جدید fetch('/api/employees', { method: 'POST', headers: {'Content-Type': 'application/json'}, body: JSON.stringify({ EmployeeCode: 'EMP1001', FirstName: 'علی', LastName: 'محمدی', NationalCode: '1234567890', DepartmentID: 1, PositionID: 1, EmploymentDate: '2024-01-01' }) }); // گرفتن ساختار درختی دپارتمانها fetch('/api/departments?include_tree=true') .then(response => response.json()) .then(data => console.log(data)); // درخواست مرخصی fetch('/api/leaves/request', { method: 'POST', headers: {'Content-Type': 'application/json'}, body: JSON.stringify({ EmployeeID: 1, LeaveTypeID: 1, StartDate: '2024-02-01', EndDate: '2024-02-03', Reason: 'مسافرت', RequestedBy: 1 }) });
این API ها تمام نیازهای اصلی یک سیستم HRM را پوشش میدهند و میتوانند بر اساس نیازهای خاص توسعه داده شوند.
نظرات (۰)
هیچ نظری هنوز ثبت نشده است