下载MySQL
创建数据库,数据表
CREATE TABLE IF NOT EXISTS attendance (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255) NOT NULL,
sign_in_time DATETIME NOT NULL,
sign_out_time DATETIME,
late ENUM('是', '否') DEFAULT '否',
early_leave ENUM('是', '否') DEFAULT '否',
CONSTRAINT unique_sign_in UNIQUE (username, sign_in_time)
);
CREATE TABLE users (
id INT(11) NOT NULL AUTO_INCREMENT,
username VARCHAR(50) NOT NULL UNIQUE,
password VARCHAR(50) NOT NULL,
is_admin TINYINT(1) DEFAULT 0,
PRIMARY KEY (id)
);
编写shell脚本
#!/bin/bash
DB="**"
DB_USER="**"
DB_PASS="**"
DB_HOST="**"
# 主菜单函数
function main_menu() {
echo "考勤管理系统"
echo "1. 上班签到"
echo "2. 下班签出"
echo "3. 缺勤信息查阅"
echo "4. 用户信息维护"
echo "5. 请假申请"
echo "6. 异常签到修改"
echo "7. 退出"
read -p "请选择操作: " choice
case $choice in
1) sign_in ;;
2) sign_out ;;
3) view_absence ;;
4) manage_users ;;
5) apply_leave ;;
6) modify_attendance ;;
7) exit 0 ;;
*) echo "无效的选择,请重试。" ;;
esac
}
function modify_attendance() {
if authenticate_user; then
check_admin $username
fi
read -p "请输入用户名: " username
read -p "请输入日期和时间 (YYYY-MM-DD HH:MM:SS): " datetime
# 查询指定用户在指定日期的签到记录
sign_in_record=$(mysql -u "$DB_USER" -p"$DB_PASS" -h "$DB_HOST" -D "$DB" -sse "SELECT sign_in_time, sign_out_time, late, early_leave FROM attendance WHERE username='$username' AND DATE_FORMAT(sign_in_time, '%Y-%m-%d %H:%i:%s')='$datetime';")
if [[ -z "$sign_in_record" ]]; then
echo "找不到指定日期的签到记录。"
return 1
fi
echo "当前签到记录:"
echo "$sign_in_record"
read -p "是否要修改签到时间?(y/n): " modify_sign_in
if [[ "$modify_sign_in" == "y" ]]; then
read -p "请输入新的签到时间 (YYYY-MM-DD HH:MM:SS): " new_sign_in_time
is_late="否"
sign_in_hour=$(date -d "$new_sign_in_time" +%-H) # 去掉前导零
sign_in_minute=$(date -d "$new_sign_in_time" +%M)
if (( sign_in_hour > 8 )) || (( sign_in_hour == 8 && sign_in_minute > 0 )); then
is_late="是"
fi
echo "更新签到时间为 $new_sign_in_time,迟到: $is_late"
mysql -u "$DB_USER" -p"$DB_PASS" -h "$DB_HOST" -D "$DB" -e "UPDATE attendance SET sign_in_time='$new_sign_in_time', late='$is_late' WHERE username='$username' AND DATE_FORMAT(sign_in_time, '%Y-%m-%d %H:%i:%s')='$datetime';"
echo "签到时间已更新。"
fi
read -p "是否要修改签退时间?(y/n): " modify_sign_out
if [[ "$modify_sign_out" == "y" ]]; then
read -p "请输入新的签退时间 (YYYY-MM-DD HH:MM:SS): " new_sign_out_time
is_early_leave="否"
sign_out_hour=$(date -d "$new_sign_out_time" +%-H) # 去掉前导零
if (( sign_out_hour < 18 )); then
is_early_leave="是"
fi
echo "更新签退时间为 $new_sign_out_time,早退: $is_early_leave"
mysql -u "$DB_USER" -p"$DB_PASS" -h "$DB_HOST" -D "$DB" -e "UPDATE attendance SET sign_out_time='$new_sign_out_time', early_leave='$is_early_leave' WHERE username='$username' AND DATE_FORMAT(sign_in_time, '%Y-%m-%d %H:%i:%s')='$datetime';"
echo "签退时间已更新。"
fi
}
# 上班签到函数
function sign_in() {
read -p "请输入用户名进行签到: " username
read -s -p "请输入密码: " password
echo
# 验证用户密码
local user_auth=$(mysql -u $DB_USER -p$DB_PASS -h $DB_HOST -D $DB -sse "SELECT COUNT(*) FROM users WHERE username='$username' AND password='$password';")
if [[ "$user_auth" -ne 1 ]]; then
echo "用户名或密码错误"
return 1
fi
# 检查用户是否已经签到但尚未签退
local unsigned_out=$(mysql -u $DB_USER -p$DB_PASS -h $DB_HOST -D $DB -sse "SELECT COUNT(*) FROM attendance WHERE username='$username' AND sign_out_time IS NULL;")
if [[ "$unsigned_out" -gt 0 ]]; then
echo "您已签到但尚未签退,请先签退。"
return 1
fi
# 获取当前时间
local current_time=$(date "+%Y-%m-%d %H:%M:%S")
# 设置预期的签到时间为上午8点
local expected_sign_in_time="08:00:00"
# 判断是否迟到
if [[ "$current_time" > "$expected_sign_in_time" ]]; then
local late="是"
else
local late="否"
fi
# 将签到记录插入到数据库中
mysql -u $DB_USER -p$DB_PASS -h $DB_HOST -D $DB -e "INSERT INTO attendance (username, sign_in_time, sign_out_time, late, early_leave) VALUES ('$username', '$current_time', NULL, '$late', NULL);"
echo "签到成功"
}
# 下班签出函数
function sign_out() {
read -p "请输入用户名进行签出: " username
read -s -p "请输入密码: " password
echo
# 验证用户密码
local user_auth=$(mysql -u $DB_USER -p$DB_PASS -h $DB_HOST -D $DB -sse "SELECT COUNT(*) FROM users WHERE username='$username' AND password='$password';")
if [[ "$user_auth" -ne 1 ]]; then
echo "用户名或密码错误"
return 1
fi
# 检查用户是否已经签到但尚未签退
local unsigned_out=$(mysql -u $DB_USER -p$DB_PASS -h $DB_HOST -D $DB -sse "SELECT COUNT(*) FROM attendance WHERE username='$username' AND sign_out_time IS NULL;")
if [[ "$unsigned_out" -eq 0 ]]; then
echo "您尚未签到,无法签退。"
return 1
fi
local current_time=$(date "+%Y-%m-%d %H:%M:%S")
local current_hour=$(date "+%-H")
if [[ "$current_hour" -lt "18" ]]; then
local early_leave="是"
mysql -u $DB_USER -p$DB_PASS -h $DB_HOST -D $DB -e "UPDATE attendance SET sign_out_time='$current_time', early_leave='$early_leave' WHERE username='$username' AND sign_out_time IS NULL;"
echo "您早于下午6点签出,早退信息已记录"
else
mysql -u $DB_USER -p$DB_PASS -h $DB_HOST -D $DB -e "UPDATE attendance SET sign_out_time='$current_time' WHERE username='$username' AND sign_out_time IS NULL;"
echo "签出成功"
fi
}
# 缺勤信息查阅函数
function view_absence() {
echo "考勤信息查询"
if authenticate_user; then
check_admin $username
if [[ $? -eq 0 ]]; then
echo "迟到记录:"
mysql -u $DB_USER -p$DB_PASS -h $DB_HOST -D $DB -e "SELECT username, sign_in_time,late FROM attendance WHERE late='是';"
echo "早退记录:"
mysql -u $DB_USER -p$DB_PASS -h $DB_HOST -D $DB -e "SELECT username, sign_in_time, sign_out_time, early_leave FROM attendance WHERE early_leave='是';"
echo "签到但未签退记录:"
mysql -u $DB_USER -p$DB_PASS -h $DB_HOST -D $DB -e "SELECT username, sign_in_time,sign_out_time FROM attendance WHERE sign_out_time IS NULL;"
echo "用户正常签到情况:"
mysql -u $DB_USER -p$DB_PASS -h $DB_HOST -D $DB -e "SELECT username, sign_in_time, sign_out_time FROM attendance WHERE late='否' AND early_leave='否';"
fi
fi
}
# 用户信息维护函数
function manage_users() {
if authenticate_user; then
check_admin $username
fi
while true; do
echo "用户信息维护"
echo "1. 添加用户"
echo "2. 删除用户"
echo "3. 显示全部用户及密码"
echo "4. 查询请假用户"
echo "5. 返回主菜单"
check_admin $username
if [[ $? -eq 0 ]]; then
read -p "请选择操作: " user_choice
case $user_choice in
1) add_user ;;
2) delete_user ;;
3) show_all_users ;;
4) query_leave_users ;;
5) break ;;
*) echo "无效的选择,请重试。" ;;
esac
fi
done
}
# 添加用户函数
function add_user() {
read -p "请输入新用户名: " new_user
local existing_user=$(mysql -u $DB_USER -p$DB_PASS -h $DB_HOST -D $DB -sse "SELECT COUNT(*) FROM users WHERE username='$new_user';")
if [[ "$existing_user" -gt 0 ]]; then
echo "用户名已存在,请选择其他用户名。"
return 1
fi
read -s -p "请输入密码: " new_password
echo
mysql -u $DB_USER -p$DB_PASS -h $DB_HOST -D $DB -e "INSERT INTO users (username, password) VALUES ('$new_user', '$new_password');"
echo "用户 $new_user 添加成功"
}
# 删除用户函数
function delete_user() {
read -p "请输入要删除的用户名: " del_user
mysql -u $DB_USER -p$DB_PASS -h $DB_HOST -D $DB -e "DELETE FROM leave_requests WHERE username='$del_user';"
mysql -u $DB_USER -p$DB_PASS -h $DB_HOST -D $DB -e "DELETE FROM attendance WHERE username='$del_user';"
mysql -u $DB_USER -p$DB_PASS -h $DB_HOST -D $DB -e "DELETE FROM users WHERE username='$del_user';"
echo "用户 $del_user 删除成功"
}
# 显示全部用户及密码
function show_all_users() {
echo "所有用户及密码:"
mysql -u $DB_USER -p$DB_PASS -h $DB_HOST -D $DB -e "SELECT username, password FROM users;"
}
# 查询请假用户函数
function query_leave_users() {
echo "请假用户查询"
local leave_users=$(mysql -u $DB_USER -p$DB_PASS -h $DB_HOST -D $DB -sse "SELECT * FROM leave_requests;")
if [[ -z "$leave_users" ]]; then
echo "当前无请假用户"
else
echo "$leave_users"
fi
}
# 请假申请函数
function apply_leave() {
read -p "请输入用户名: " username
read -p "请输入请假开始日期 (YYYY-MM-DD): " start_date
read -p "请输入请假结束日期 (YYYY-MM-DD): " end_date
# 检查请假日期是否小于7天
local days=$(( ($(date -d "$end_date" +%s) - $(date -d "$start_date" +%s)) / 86400 ))
if [[ $days -gt 7 ]]; then
echo "请假天数不能超过7天"
return 1
fi
mysql -u $DB_USER -p$DB_PASS -h $DB_HOST -D $DB -e "INSERT INTO leave_requests (username, start_date, end_date) VALUES ('$username', '$start_date', '$end_date');"
echo "请假申请已提交"
}
# 用户身份验证函数
function authenticate_user() {
read -p "请输入管理员账号: " username
read -s -p "请输入管理员密码: " password
echo
local stored_password=$(mysql -h $DB_HOST -u $DB_USER -p$DB_PASS -D $DB -sse "SELECT password FROM users WHERE username='$username';")
if [[ -z "$stored_password" ]];then
echo "用户名不存在"
return 1
elif [[ "$stored_password" != "$password" ]];then
echo "密码错误"
return 1
else
return 0
fi
}
# 检查用户是否是管理员
function check_admin() {
local is_admin=$(mysql -u $DB_USER -p$DB_PASS -h $DB_HOST -D $DB -sse "SELECT is_admin FROM users WHERE username='$1';")
if [[ "$is_admin" -eq 1 ]]; then
return 0
else
echo "您没有管理员权限"
main_menu
fi
}
# 循环显示主菜单
while true; do
main_menu
done