php development attendance system, OA system, simple version Mysql database design

CREATE TABLE `attendance_cfg` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Cells ID',
  `month` char(7) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'Date: 202111',
  `status` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Status: 0=Enable 1=Deactivate 2=delete',
  `off_type` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Rest type: 0=Single off, 1=Weekend',
  `days` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Attendance days: maximum 31',
  `off_days` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Rest days: maximum 31',
  `param_json` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Monthly configuration json',
  `editor_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Operator ID',
  `created_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Attendance: cell attendance configuration table';

CREATE TABLE `attendance_check` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `check_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'Punch in record id',
  `company_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Cells ID',
  `userinfo_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Wood beauty seeking system userinfo_ID',
  `deliuser_id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'Effective system user_ID',
  `check_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Clock in timestamp',
  `check_day` timestamp NULL DEFAULT NULL COMMENT 'Clock in time',
  `check_minute` time NOT NULL DEFAULT '00:00:00' COMMENT 'When punch in-branch-second',
  `check_imei` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'Attendance machine equipment serial number',
  `check_name` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'Attendance machine equipment name',
  `check_type` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'Deli punch in type',
  `check_data` varchar(1024) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'Punch in supplementary data',
  `cal` char(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'Clock in calendar 2021-11-9',
  `status` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Status: 0=Valid 1=Invalid clock out',
  `editor_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'proofreader',
  `editor_type` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Proofreading type 10=Make up card',
  `remark` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'Proofreading notes',
  `created_at` timestamp NULL DEFAULT NULL COMMENT 'record api Export time',
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `userinfo_id` (`userinfo_id`)
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Attendance: Deli Deli Punch in record';

CREATE TABLE `attendance_clock` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Cells ID',
  `clock_type` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Number of clocks: 0=2 Times, 1=4 second',
  `is_remind` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Clock in reminder: 0=No reminder, 1=remind',
  `is_overtime` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Overtime: 0=Not calculated, 1=calculation',
  `reset_num` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Number of card replenishment per month',
  `work_time` decimal(4,1) NOT NULL DEFAULT '8.0' COMMENT 'Working hours 8',
  `status` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Status: 0=Enable 1=Deactivate 2=delete',
  `late_min` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'Count the minutes late',
  `back_num` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'Count the minutes of absenteeism and lateness',
  `clock_json` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Clock in time period every day json',
  `can_late` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'arrive late/Leaving a few minutes earlier is not recorded as an exception: 0=Not allowed, 1=allow',
  `late_str` varchar(512) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'Configuration: late arrival/Leave a few minutes early',
  `can_free` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Late arrival and late departure are allowed/Early arrival and early departure: 0=Not allowed, 1=allow',
  `free_str` varchar(512) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'Configuration: free working hours',
  `editor_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Operator ID',
  `created_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Attendance: clock in configuration table';

CREATE TABLE `attendance_leave` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `userinfo_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Wood beauty seeking system userinfo_ID',
  `day` char(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'Date: 2021-11-11',
  `day_type` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'sick leave|compassionate leave|Funeral leave|maternity leave|Compensatory leave...1-16 paragraph',
  `leave_time` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Holiday duration: 1-8 hour',
  `start_time` time NOT NULL DEFAULT '00:00:00' COMMENT 'Leave start time',
  `end_time` time NOT NULL DEFAULT '00:00:00' COMMENT 'Leave end time',
  `remark` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'remarks',
  `status` tinyint(2) unsigned NOT NULL DEFAULT '0' COMMENT 'state',
  `editor_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Confirmed by ID',
  `created_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `userinfo_id` (`userinfo_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Attendance: Vacation-Record sheet';

CREATE TABLE `attendance_machine` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `imei` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'Machine serial number',
  `name` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'Machine sequence name',
  `address` varchar(1024) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'Location of attendance machine',
  `company_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Cell of attendance machine id',
  `status` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Status: 0=Enable 1=Deactivate 2=delete',
  `editor_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Operator ID',
  `created_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Attendance: equipment management table';

CREATE TABLE `attendance_month` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `userinfo_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Wood beauty seeking system userinfo_ID',
  `company_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Employee's cell id',
  `month` char(7) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'Date: 2021-11',
  `status` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Status: 0=Normal 1=Exception 2=invalid',
  `work_days` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Actual attendance days',
  `work_hours` decimal(6,1) unsigned NOT NULL DEFAULT '0.0' COMMENT 'Actual working hours',
  `except_days` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Abnormal days',
  `later_hours` decimal(6,1) unsigned NOT NULL DEFAULT '0.0' COMMENT 'Late time',
  `before_num` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Early leave times',
  `before_hours` decimal(6,1) unsigned NOT NULL DEFAULT '0.0' COMMENT 'Length of early leave',
  `no_work_hours` decimal(6,1) unsigned NOT NULL DEFAULT '0.0' COMMENT 'Absenteeism duration',
  `un_clock_num` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Missed punch times',
  `reset_num` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Card replenishment times',
  `out_num` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Out times',
  `company_num` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Travel days',
  `year_num` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Annual leave days',
  `leave_num` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Personal leave days',
  `sick_num` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Sick leave days',
  `over_work_num` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Days of compensatory leave',
  `marry_num` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Marriage leave days',
  `baby_num` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'Maternity leave days',
  `daddy_num` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'Days of paternity leave',
  `die_num` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'Funeral leave days',
  `editor_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Confirmed by ID',
  `created_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `month` (`month`),
  KEY `userinfo_id` (`userinfo_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Attendance: Monthly Report';

CREATE TABLE `attendance_records` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `attendance_type` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `attendance_id` bigint(20) unsigned DEFAULT NULL COMMENT 'Examination center id',
  `describe` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Record description',
  `type` tinyint(4) NOT NULL COMMENT 'Record type, logistics, payment, etc',
  `subtype` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Subtype',
  `state` tinyint(3) unsigned DEFAULT NULL COMMENT 'Available flag status',
  `param` text COLLATE utf8mb4_unicode_ci COMMENT 'Record parameters',
  `editor_id` int(11) NOT NULL COMMENT 'Editor ID',
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `attendance_id` (`attendance_id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Attendance: attendance operation record';

CREATE TABLE `attendance_work` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `userinfo_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Wood beauty seeking system userinfo_ID',
  `company_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Employee's cell id',
  `work_day` char(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'Clock in calendar 2021-11-11',
  `status` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Status: 0=Normal 1=Exception 2=invalid',
  `check_num` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Punch in times of the day',
  `reset_num` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Card replenishment times of the day',
  `leave_num` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Card missing times',
  `editor_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'proofreader ID',
  `late_min` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Late time',
  `before_min` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Length of early leave',
  `unwork_min` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Absenteeism duration',
  `over_min` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Overtime hours',
  `record1` time NOT NULL DEFAULT '00:00:00' COMMENT 'Morning working hours',
  `record2` time NOT NULL DEFAULT '00:00:00' COMMENT 'Morning off duty time',
  `record3` time NOT NULL DEFAULT '00:00:00' COMMENT 'Afternoon working hours',
  `record4` time NOT NULL DEFAULT '00:00:00' COMMENT 'Afternoon off duty time',
  `remark` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'remarks',
  `created_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Attendance: calendar';

Rules:

Attendance month: statistics based on the month of filter criteria

Employee: employee file name

Position name: position name of employee file

Days to be on duty: calculate the days to be on duty in the current month (get the working days from the attendance days)

Actual attendance days: required attendance days - abnormal days - personal leave days

Standard working hours: Based on the number of days of attendance in this month * the working hours in the clock out setting

Actual working hours: standard working hours - late hours - absenteeism hours - early leave hours

Status: normal (abnormal days = 0) abnormal (abnormal days ≥ 1)

Abnormal days: count the clock out records of employee exceptions

Length of lateness: employee's minutes of lateness < 120 minutes

Early leave times: accumulated early leave times in the employee clock in record (two early leave times a day, two statistics)

Early leave duration: the accumulated early leave duration in the employee clock in record

Absenteeism duration: the number of minutes employees are late ≥ 120 minutes

Missed clocking times / card replenishment times / times of going out / travel days / annual leave added / personal leave days / sick leave added / compensatory leave days / marriage leave days / maternity leave days / paternity leave days / funeral leave days: statistics are made according to the proofreading type saved by the user

Daily report rules:

Date:

Employee: punch in user name

Position name: the position name in the file

Standard working hours: Based on the number of days of attendance in this month * the working hours in the clock out setting

Actual working hours: standard working hours - late hours - absenteeism hours - early leave hours

Clock in period: configure the clock in period for the login cell

Punch in type: the daily punch in times configured by the user (Format: xx times per day)

Earliest clock in time: the earliest clock in time of the user today (00:00:00-23:59:59)

Latest clock in time: the user enters the last clock in time (00:00:00-23:59:59)

Clock in times: the number of valid clocks in the clock in rules (for example: clock in at 9:00, no matter how many times employees clock in before 9:00 and 9:00, only one clock in is counted)

Status: when the number of clocks is equal to the type of clocking, the status is equal to "normal"; If the number of clocks is less than the clocking type and the actual working hours are less than the standard working hours, the status is "abnormal"

Tardiness duration: judged according to the user's configuration in the clock out setting. When there is no elastic clock out, the number of tardiness minutes will be counted when it exceeds the working hours set by the user; Elastic punch in exceeds the latest time set by the user, and count the minutes late before punch in (for example, 30 minutes late for 9:00, and 31 minutes late for 31 when employees punch in)

Early leave duration: determined according to the configuration in the user's clock out setting. When there is no flexibility to punch in, if the punch in time is earlier than the off-duty time set by the user, it will be regarded as early leave; When there is flexible clock in, the clock in time and the off-duty time will be regarded as early leave if the difference exceeds the earliest minutes set by the user (for example, if you get off work at 18:00, you can clock in 20 minutes earlier, and if you clock in at 17:32, the number of early leave minutes is 28 minutes)

Missing card times: punch in type - punch in times - reissue times

Card replenishment times: statistics are made according to the punching time and proofreading method selected by the user during proofreading today (for example, if the user makes two card replenishment operations in a time period today, it is still counted as one card replenishment)

A Punch in record (9):33 12:10 18:31)
C Punch in record (9):33 11:55 12:01	14:04 18:31)
D Punch in record (9):33 11:55 14:04 18:31)
E Punch in record (9):33 11:45 12:01	18:44 19:31)
F Punch in record (8):44 12:01)
G Clock in time (7):56 8:45 11.45 11:59 13:23 16:43 18:43  20:21)
H Clock in time (8):56)
I Clock in time (18):58)
J Clock in time (8):55 12:01 12:44 13:44 18:56)
K Clock in time (8):33 12:03 18:31 18:59)


How to find out effective 4-time clock in records
 Some subsidiaries punch in twice
 Yes, four times
2021 November 20, 2018:03:59

Tags: Java Database git

Posted on Sun, 21 Nov 2021 02:31:31 -0500 by bsamson