ساختار پایگاه داده (اسامی جداول با فیلدهای مورد نیاز) یک سامانه حضور و غیاب (کنترل ترد کارکنان) در MySQL
Here's a comprehensive database structure for an attendance system (سامانه حضور و غیاب) in MySQL:
1. Employees Table (جدول کارکنان)
sql
CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY AUTO_INCREMENT, EmployeeCode VARCHAR(20) UNIQUE NOT NULL, FirstName NVARCHAR(50) NOT NULL, LastName NVARCHAR(50) NOT NULL, NationalCode VARCHAR(10) UNIQUE, PersonalCode VARCHAR(20) UNIQUE, DepartmentID INT, PositionID INT, EmploymentDate DATE, IsActive BOOLEAN DEFAULT TRUE, Mobile VARCHAR(11), Email VARCHAR(100), Address TEXT, 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) );
2. Departments Table (جدول دپارتمانها)
sql
CREATE TABLE Departments ( DepartmentID INT PRIMARY KEY AUTO_INCREMENT, DepartmentName NVARCHAR(100) NOT NULL, DepartmentCode VARCHAR(20) UNIQUE, ParentDepartmentID INT NULL, ManagerID INT NULL, IsActive BOOLEAN DEFAULT TRUE, CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (ParentDepartmentID) REFERENCES Departments(DepartmentID), FOREIGN KEY (ManagerID) REFERENCES Employees(EmployeeID) );
3. Positions Table (جدول سمتها)
sql
CREATE TABLE Positions ( PositionID INT PRIMARY KEY AUTO_INCREMENT, PositionName NVARCHAR(100) NOT NULL, PositionCode VARCHAR(20) UNIQUE, IsActive BOOLEAN DEFAULT TRUE, CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP );
4. Attendance Devices Table (جدول دستگاههای حضورغیاب)
sql
CREATE TABLE AttendanceDevices ( DeviceID INT PRIMARY KEY AUTO_INCREMENT, DeviceName NVARCHAR(100) NOT NULL, DeviceCode VARCHAR(50) UNIQUE NOT NULL, DeviceType ENUM('Fingerprint', 'FaceRecognition', 'Card', 'Mobile') DEFAULT 'Fingerprint', IPAddress VARCHAR(15), Port INT, Location NVARCHAR(200), IsActive BOOLEAN DEFAULT TRUE, LastSync DATETIME NULL, CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP );
5. Attendance Records Table (جدول سوابق حضورغیاب)
sql
CREATE TABLE AttendanceRecords ( RecordID BIGINT PRIMARY KEY AUTO_INCREMENT, EmployeeID INT NOT NULL, DeviceID INT NOT NULL, RecordDateTime DATETIME NOT NULL, RecordType ENUM('Entry', 'Exit') NOT NULL, RecordDate DATE GENERATED ALWAYS AS (DATE(RecordDateTime)) STORED, RecordTime TIME GENERATED ALWAYS AS (TIME(RecordDateTime)) STORED, VerifyMethod ENUM('Fingerprint', 'Card', 'Face', 'Password') DEFAULT 'Fingerprint', IsManual BOOLEAN DEFAULT FALSE, ManualReason TEXT NULL, CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_employee_date (EmployeeID, RecordDate), INDEX idx_datetime (RecordDateTime), FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID), FOREIGN KEY (DeviceID) REFERENCES AttendanceDevices(DeviceID) );
6. Work Shifts Table (جدول شیفتهای کاری)
sql
CREATE TABLE WorkShifts ( ShiftID INT PRIMARY KEY AUTO_INCREMENT, ShiftName NVARCHAR(50) NOT NULL, ShiftCode VARCHAR(20) UNIQUE, StartTime TIME NOT NULL, EndTime TIME NOT NULL, GracePeriodBefore INT DEFAULT 15, -- دقیقه مجاز قبل از شروع GracePeriodAfter INT DEFAULT 15, -- دقیقه مجاز بعد از شروع IsActive BOOLEAN DEFAULT TRUE, CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP );
7. Employee Shifts Table (جدول شیفتهای کارکنان)
sql
CREATE TABLE EmployeeShifts ( EmployeeShiftID BIGINT PRIMARY KEY AUTO_INCREMENT, EmployeeID INT NOT NULL, ShiftID INT NOT NULL, EffectiveDate DATE NOT NULL, ExpiryDate DATE NULL, IsActive BOOLEAN DEFAULT TRUE, CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_employee_effective (EmployeeID, EffectiveDate), FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID), FOREIGN KEY (ShiftID) REFERENCES WorkShifts(ShiftID) );
8. Daily Attendance Summary (جدول خلاصه حضور روزانه)
sql
CREATE TABLE DailyAttendance ( DailyAttendanceID BIGINT PRIMARY KEY AUTO_INCREMENT, EmployeeID INT NOT NULL, AttendanceDate DATE NOT NULL, ShiftID INT NULL, PlannedEntryTime TIME NULL, PlannedExitTime TIME NULL, ActualEntryTime TIME NULL, ActualExitTime TIME NULL, EntryDelay INT DEFAULT 0, -- تاخیر ورود به دقیقه ExitEarly INT DEFAULT 0, -- تعجیل خروج به دقیقه TotalWorkMinutes INT DEFAULT 0, -- مجموع دقایق کار RequiredWorkMinutes INT DEFAULT 480, -- دقایق کار مورد نیاز OvertimeMinutes INT DEFAULT 0, -- اضافه کار ShortageMinutes INT DEFAULT 0, -- کسری کار Status ENUM('Present', 'Absent', 'Leave', 'Holiday', 'Mission') DEFAULT 'Absent', CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY unique_employee_date (EmployeeID, AttendanceDate), INDEX idx_date_status (AttendanceDate, Status), FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID), FOREIGN KEY (ShiftID) REFERENCES WorkShifts(ShiftID) );
9. Leave Types Table (جدول انواع مرخصی)
sql
CREATE TABLE LeaveTypes ( LeaveTypeID INT PRIMARY KEY AUTO_INCREMENT, LeaveTypeName NVARCHAR(100) NOT NULL, LeaveTypeCode VARCHAR(20) UNIQUE, IsPaid BOOLEAN DEFAULT FALSE, MaxDaysPerYear INT DEFAULT 0, IsActive BOOLEAN DEFAULT TRUE, CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP );
10. Employee Leaves Table (جدول مرخصیهای کارکنان)
sql
CREATE TABLE EmployeeLeaves ( LeaveID BIGINT 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, Description TEXT, Status ENUM('Pending', 'Approved', 'Rejected') DEFAULT 'Pending', ApprovedBy INT NULL, ApprovedAt DATETIME NULL, CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_employee_date (EmployeeID, StartDate), FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID), FOREIGN KEY (LeaveTypeID) REFERENCES LeaveTypes(LeaveTypeID), FOREIGN KEY (ApprovedBy) REFERENCES Employees(EmployeeID) );
11. Holidays Table (جدول تعطیلات)
sql
CREATE TABLE Holidays ( HolidayID INT PRIMARY KEY AUTO_INCREMENT, HolidayName NVARCHAR(100) NOT NULL, HolidayDate DATE NOT NULL, IsRecurring BOOLEAN DEFAULT TRUE, Description TEXT, CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY unique_holiday_date (HolidayDate) );
12. Settings Table (جدول تنظیمات)
sql
CREATE TABLE SystemSettings ( SettingID INT PRIMARY KEY AUTO_INCREMENT, SettingKey VARCHAR(100) UNIQUE NOT NULL, SettingValue TEXT, SettingGroup VARCHAR(50) DEFAULT 'General', Description TEXT, UpdatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );
دادههای اولیه:
sql
-- درج شیفتهای پیشفرض INSERT INTO WorkShifts (ShiftName, ShiftCode, StartTime, EndTime) VALUES ('شیفت صبح', 'MORNING', '08:00:00', '16:00:00'), ('شیفت عصر', 'EVENING', '16:00:00', '24:00:00'), ('شیفت شب', 'NIGHT', '00:00:00', '08:00:00'); -- درج انواع مرخصی INSERT INTO LeaveTypes (LeaveTypeName, LeaveTypeCode, IsPaid, MaxDaysPerYear) VALUES ('مرخصی استحقاقی', 'ANNUAL', TRUE, 26), ('مرخصی استعلاجی', 'SICK', TRUE, 30), ('مرخصی بدون حقوق', 'UNPAID', FALSE, 0), ('مرخصی زایمان', 'MATERNITY', TRUE, 180); -- درج تنظیمات سیستم INSERT INTO SystemSettings (SettingKey, SettingValue, SettingGroup, Description) VALUES ('company_name', 'شرکت نمونه', 'General', 'نام شرکت'), ('work_hours_per_day', '8', 'Attendance', 'ساعات کار روزانه'), ('auto_calculate_attendance', '1', 'Attendance', 'محاسبه خودکار حضورغیاب');
ویژگیهای این ساختار:
مدیریت کامل پرسنل
پشتیبانی از انواع دستگاههای حضورغیاب
مدیریت شیفتهای کاری
محاسبه خودکار تاخیر و اضافه کار
سیستم مرخصی
تعطیلات رسمی
گزارشگیری پیشرفته
پشتیبانی از چند شیفتی
این ساختار برای یک سامانه حضور و غیاب کامل و حرفهای مناسب است.
بله، کامل! در اینجا API های ضروری برای سامانه حضور و غیاب به زبان PHP ارائه میدهم:
1. فایل پیکربندی دیتابیس (db.php
)
php
<?php
$host = 'localhost';
$username = 'username';
$password = 'password';
$database = 'attendance_system';
$conn = new mysqli($host, $username, $password, $database);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$conn->set_charset("utf8");
?>
2. API ثبت حضور و غیاب
php
<?php
header("Content-Type: application/json");
header("Access-Control-Allow-Origin: *");
header("Access-Control-Allow-Headers: *");
header("Access-Control-Allow-Methods: POST, GET, OPTIONS");
include('db.php');
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
$data = json_decode(file_get_contents("php://input"), true);
$employeeCode = $data['employee_code'];
$deviceId = $data['device_id'];
$recordType = $data['record_type']; // 'Entry' or 'Exit'
$verifyMethod = $data['verify_method'] ?? 'Fingerprint';
// دریافت EmployeeID از کد کارمند
$stmt = $conn->prepare("SELECT EmployeeID FROM Employees WHERE EmployeeCode = ? AND IsActive = 1");
$stmt->bind_param("s", $employeeCode);
$stmt->execute();
$result = $stmt->get_result();
if ($result->num_rows > 0) {
$employee = $result->fetch_assoc();
$employeeId = $employee['EmployeeID'];
// ثبت رکورد حضورغیاب
$insertStmt = $conn->prepare("
INSERT INTO AttendanceRecords (EmployeeID, DeviceID, RecordDateTime, RecordType, VerifyMethod)
VALUES (?, ?, NOW(), ?, ?)
");
$insertStmt->bind_param("iiss", $employeeId, $deviceId, $recordType, $verifyMethod);
if ($insertStmt->execute()) {
echo json_encode([
'success' => true,
'message' => 'رکورد با موفقیت ثبت شد',
'record_id' => $insertStmt->insert_id,
'timestamp' => date('Y-m-d H:i:s')
]);
} else {
echo json_encode(['success' => false, 'message' => 'خطا در ثبت رکورد']);
}
} else {
echo json_encode(['success' => false, 'message' => 'کارمند یافت نشد']);
}
}
?>
3. API گزارش حضورغیاب روزانه
php
<?php
header("Content-Type: application/json");
header("Access-Control-Allow-Origin: *");
header("Access-Control-Allow-Headers: *");
include('db.php');
if ($_SERVER['REQUEST_METHOD'] == 'GET') {
$date = $_GET['date'] ?? date('Y-m-d');
$departmentId = $_GET['department_id'] ?? null;
$query = "
SELECT
e.EmployeeID,
e.FirstName,
e.LastName,
e.EmployeeCode,
d.DepartmentName,
ar.EntryTime,
ar.ExitTime,
ar.Status,
ar.TotalWorkMinutes,
ar.OvertimeMinutes,
ar.ShortageMinutes
FROM DailyAttendance ar
INNER JOIN Employees e ON ar.EmployeeID = e.EmployeeID
LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE ar.AttendanceDate = ?
";
if ($departmentId) {
$query .= " AND e.DepartmentID = ?";
$stmt = $conn->prepare($query);
$stmt->bind_param("si", $date, $departmentId);
} else {
$stmt = $conn->prepare($query);
$stmt->bind_param("s", $date);
}
$stmt->execute();
$result = $stmt->get_result();
$attendance = [];
while ($row = $result->fetch_assoc()) {
$attendance[] = $row;
}
echo json_encode([
'success' => true,
'date' => $date,
'data' => $attendance
]);
}
?>
4. API اطلاعات پرسنل
php
<?php
header("Content-Type: application/json");
header("Access-Control-Allow-Origin: *");
header("Access-Control-Allow-Headers: *");
include('db.php');
if ($_SERVER['REQUEST_METHOD'] == 'GET') {
$employeeId = $_GET['employee_id'] ?? null;
$departmentId = $_GET['department_id'] ?? null;
$isActive = $_GET['is_active'] ?? 1;
$query = "
SELECT
e.*,
d.DepartmentName,
p.PositionName,
es.ShiftID,
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 EmployeeShifts es ON e.EmployeeID = es.EmployeeID AND es.IsActive = 1
LEFT JOIN WorkShifts ws ON es.ShiftID = 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";
}
$stmt = $conn->prepare($query);
$stmt->bind_param($types, ...$params);
$stmt->execute();
$result = $stmt->get_result();
$employees = [];
while ($row = $result->fetch_assoc()) {
$employees[] = $row;
}
echo json_encode([
'success' => true,
'count' => count($employees),
'data' => $employees
]);
}
?>
5. API ثبت درخواست مرخصی
php
<?php
header("Content-Type: application/json");
header("Access-Control-Allow-Origin: *");
header("Access-Control-Allow-Headers: *");
include('db.php');
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
$data = json_decode(file_get_contents("php://input"), true);
$employeeId = $data['employee_id'];
$leaveTypeId = $data['leave_type_id'];
$startDate = $data['start_date'];
$endDate = $data['end_date'];
$startTime = $data['start_time'] ?? null;
$endTime = $data['end_time'] ?? null;
$description = $data['description'] ?? '';
// محاسبه تعداد روزها
$start = new DateTime($startDate);
$end = new DateTime($endDate);
$totalDays = $start->diff($end)->days + 1;
$stmt = $conn->prepare("
INSERT INTO EmployeeLeaves
(EmployeeID, LeaveTypeID, StartDate, EndDate, StartTime, EndTime, TotalDays, Description)
VALUES (?, ?, ?, ?, ?, ?, ?, ?)
");
$stmt->bind_param("iissssds", $employeeId, $leaveTypeId, $startDate, $endDate, $startTime, $endTime, $totalDays, $description);
if ($stmt->execute()) {
echo json_encode([
'success' => true,
'message' => 'درخواست مرخصی با موفقیت ثبت شد',
'leave_id' => $stmt->insert_id
]);
} else {
echo json_encode(['success' => false, 'message' => 'خطا در ثبت درخواست مرخصی']);
}
}
?>
6. API گزارش ماهانه
php
<?php
header("Content-Type: application/json");
header("Access-Control-Allow-Origin: *");
header("Access-Control-Allow-Headers: *");
include('db.php');
if ($_SERVER['REQUEST_METHOD'] == 'GET') {
$month = $_GET['month'] ?? date('Y-m');
$employeeId = $_GET['employee_id'] ?? null;
$startDate = $month . '-01';
$endDate = date('Y-m-t', strtotime($startDate));
$query = "
SELECT
e.EmployeeID,
e.FirstName,
e.LastName,
e.EmployeeCode,
COUNT(da.DailyAttendanceID) as working_days,
SUM(da.TotalWorkMinutes) as total_work_minutes,
SUM(da.OvertimeMinutes) as total_overtime,
SUM(da.ShortageMinutes) as total_shortage,
SUM(CASE WHEN da.Status = 'Absent' THEN 1 ELSE 0 END) as absent_days,
SUM(CASE WHEN da.Status = 'Leave' THEN 1 ELSE 0 END) as leave_days
FROM Employees e
LEFT JOIN DailyAttendance da ON e.EmployeeID = da.EmployeeID
AND da.AttendanceDate BETWEEN ? AND ?
WHERE e.IsActive = 1
";
$params = [$startDate, $endDate];
$types = "ss";
if ($employeeId) {
$query .= " AND e.EmployeeID = ?";
$params[] = $employeeId;
$types .= "i";
}
$query .= " GROUP BY e.EmployeeID";
$stmt = $conn->prepare($query);
$stmt->bind_param($types, ...$params);
$stmt->execute();
$result = $stmt->get_result();
$report = [];
while ($row = $result->fetch_assoc()) {
$report[] = $row;
}
echo json_encode([
'success' => true,
'month' => $month,
'data' => $report
]);
}
?>
7. API مدیریت شیفتها
php
<?php
header("Content-Type: application/json");
header("Access-Control-Allow-Origin: *");
header("Access-Control-Allow-Headers: *");
include('db.php');
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
$data = json_decode(file_get_contents("php://input"), true);
$action = $data['action'];
switch ($action) {
case 'assign_shift':
$employeeId = $data['employee_id'];
$shiftId = $data['shift_id'];
$effectiveDate = $data['effective_date'];
// غیرفعال کردن شیفت قبلی
$updateStmt = $conn->prepare("
UPDATE EmployeeShifts
SET IsActive = 0
WHERE EmployeeID = ? AND IsActive = 1
");
$updateStmt->bind_param("i", $employeeId);
$updateStmt->execute();
// اختصاص شیفت جدید
$insertStmt = $conn->prepare("
INSERT INTO EmployeeShifts (EmployeeID, ShiftID, EffectiveDate)
VALUES (?, ?, ?)
");
$insertStmt->bind_param("iis", $employeeId, $shiftId, $effectiveDate);
if ($insertStmt->execute()) {
echo json_encode(['success' => true, 'message' => 'شیفت با موفقیت اختصاص داده شد']);
} else {
echo json_encode(['success' => false, 'message' => 'خطا در اختصاص شیفت']);
}
break;
case 'get_shifts':
$stmt = $conn->prepare("SELECT * FROM WorkShifts WHERE IsActive = 1");
$stmt->execute();
$result = $stmt->get_result();
$shifts = [];
while ($row = $result->fetch_assoc()) {
$shifts[] = $row;
}
echo json_encode(['success' => true, 'data' => $shifts]);
break;
}
}
?>
8. API دستگاههای حضورغیاب
php
<?php
header("Content-Type: application/json");
header("Access-Control-Allow-Origin: *");
header("Access-Control-Allow-Headers: *");
include('db.php');
if ($_SERVER['REQUEST_METHOD'] == 'GET') {
$stmt = $conn->prepare("SELECT * FROM AttendanceDevices WHERE IsActive = 1");
$stmt->execute();
$result = $stmt->get_result();
$devices = [];
while ($row = $result->fetch_assoc()) {
$devices[] = $row;
}
echo json_encode([
'success' => true,
'count' => count($devices),
'data' => $devices
]);
}
?>
9. فایل اصلی روتر (index.php
)
php
<?php
header("Content-Type: application/json");
header("Access-Control-Allow-Origin: *");
header("Access-Control-Allow-Headers: *");
header("Access-Control-Allow-Methods: POST, GET, OPTIONS");
if ($_SERVER['REQUEST_METHOD'] == 'OPTIONS') {
exit(0);
}
$request_uri = $_SERVER['REQUEST_URI'];
$method = $_SERVER['REQUEST_METHOD'];
// مسیرهای API
$routes = [
'POST' => [
'/api/attendance/record' => 'record_attendance.php',
'/api/leave/request' => 'leave_request.php',
'/api/shifts/manage' => 'manage_shifts.php'
],
'GET' => [
'/api/attendance/daily' => 'daily_attendance.php',
'/api/employees' => 'employees.php',
'/api/attendance/monthly' => 'monthly_report.php',
'/api/devices' => 'devices.php'
]
];
$path = parse_url($request_uri, PHP_URL_PATH);
if (isset($routes[$method][$path])) {
include($routes[$method][$path]);
} else {
http_response_code(404);
echo json_encode(['success' => false, 'message' => 'API not found']);
}
?>
نحوه استفاده:
bash
# ثبت حضور POST /api/attendance/record { "employee_code": "EMP001", "device_id": 1, "record_type": "Entry" } # گزارش روزانه GET /api/attendance/daily?date=2024-01-15 # گزارش ماهانه GET /api/attendance/monthly?month=2024-01 # لیست کارکنان GET /api/employees
این API ها تمام نیازهای اصلی یک سامانه حضور و غیاب را پوشش میدهند.
نظرات (۰)
هیچ نظری هنوز ثبت نشده است