From e09a6147e2c4828760b5afb2b2be05f3e8ad2356 Mon Sep 17 00:00:00 2001
From: liu <2021943741@qq.com>
Date: 星期五, 18 九月 2026 18:09:49 +0800
Subject: [PATCH] docs(sql): 新增全勤奖行内调整数据库脚本
---
doc/sql/20260918_salary_attendance_bonus.sql | 92 ++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 92 insertions(+), 0 deletions(-)
diff --git a/doc/sql/20260918_salary_attendance_bonus.sql b/doc/sql/20260918_salary_attendance_bonus.sql
new file mode 100644
index 0000000..95d4a4b
--- /dev/null
+++ b/doc/sql/20260918_salary_attendance_bonus.sql
@@ -0,0 +1,92 @@
+-- =====================================================================
+-- 鍔熻兘锛氳柂閰牳绠� 鈥斺�� 鍏ㄥ嫟濂栨敮鎸佸伐璧勮〃琛屽唴璋冩暣
+-- 鏃ユ湡锛�2026-09-18
+-- 鍒嗘敮锛歞ev_澶╂触_鐜嫇涔愬櫒
+--
+-- 銆愰噸瑕佽鏄庛��
+-- 鏈姛鑳戒负绾唬鐮佸眰鏀瑰姩锛�
+-- 鍓嶇 formDia.vue锛堝叏鍕ゅ鍒楁斁寮�缂栬緫 + 瀹炴椂閲嶇畻锛�
+-- 鍚庣 SchemeApplicableStaffServiceImpl.resolveAttendanceBonus锛堜紭鍏堝彇琛屽唴浼犲叆鍊硷級
+-- 鎵�渚濊禆鐨� attendance_bonus 瀛楁鍦ㄤ袱涓〃涓�愭湰鏉ュ氨宸插瓨鍦ㄣ�戯紝
+-- 鍥犳鍦ㄦ爣鍑嗙幆澧冧笅銆愭棤闇�鎵ц浠讳綍鏁版嵁搴撳彉鏇淬�戯紝鐩存帴閮ㄧ讲鍓嶅悗绔唬鐮佸嵆鍙��
+--
+-- 鏈剼鏈敤浜庯細鐩爣鐜锛堝瀹㈡埛鐜板満搴撱�佹棫鐗堟湰搴擄級缂哄皯璇ュ瓧娈垫椂瀹夊叏琛ラ綈銆�
+-- 鑴氭湰鍙噸澶嶆墽琛岋紝宸插瓨鍦ㄥ垯鑷姩璺宠繃锛屼笉浼氭姤閿欍��
+--
+-- 娑夊強琛細
+-- staff_on_job.attendance_bonus 鍛樺伐妗ｆ鍏ㄥ嫟濂栵紙NOT NULL 榛樿 100.00锛�
+-- staff_salary_detail.attendance_bonus 宸ヨ祫鏄庣粏鍏ㄥ嫟濂栵紙NOT NULL 榛樿 100.00锛�
+-- =====================================================================
+
+-- ---------------------------------------------------------------------
+-- 1. 鎵ц鍓嶈嚜妫�锛氭煡鐪嬩袱涓瓧娈电殑鐜扮姸
+-- ---------------------------------------------------------------------
+SELECT table_name AS 琛ㄥ悕,
+ column_name AS 瀛楁鍚�,
+ column_type AS 绫诲瀷,
+ is_nullable AS 鍙┖,
+ column_default AS 榛樿鍊�,
+ column_comment AS 娉ㄩ噴
+FROM information_schema.columns
+WHERE table_schema = DATABASE()
+ AND table_name IN ('staff_on_job', 'staff_salary_detail')
+ AND column_name = 'attendance_bonus';
+
+-- ---------------------------------------------------------------------
+-- 2. 骞傜瓑琛ラ綈瀛楁锛堝瓧娈靛凡瀛樺湪鍒欒烦杩囷級
+-- 娉ㄦ剰锛欴ELIMITER 鏄鎴风鎸囦护锛孨avicat / DBeaver / mysql CLI 鍧囨敮鎸侊紱
+-- 鑻ョ敤涓嶆敮鎸� DELIMITER 鐨勫伐鍏凤紝璇峰彧鎵ц鍏朵腑缂哄け鐨勯偅鏉� ALTER銆�
+-- ---------------------------------------------------------------------
+DROP PROCEDURE IF EXISTS sp_add_attendance_bonus;
+
+DELIMITER $$
+CREATE PROCEDURE sp_add_attendance_bonus()
+BEGIN
+ IF NOT EXISTS (SELECT 1 FROM information_schema.columns
+ WHERE table_schema = DATABASE()
+ AND table_name = 'staff_on_job'
+ AND column_name = 'attendance_bonus') THEN
+ ALTER TABLE staff_on_job
+ ADD COLUMN attendance_bonus DECIMAL(10,2) NOT NULL DEFAULT 100.00 COMMENT '鍏ㄥ嫟濂栭噾';
+ END IF;
+
+ IF NOT EXISTS (SELECT 1 FROM information_schema.columns
+ WHERE table_schema = DATABASE()
+ AND table_name = 'staff_salary_detail'
+ AND column_name = 'attendance_bonus') THEN
+ ALTER TABLE staff_salary_detail
+ ADD COLUMN attendance_bonus DECIMAL(10,2) NOT NULL DEFAULT 100.00 COMMENT '鍏ㄥ嫟濂栭噾';
+ END IF;
+END$$
+DELIMITER ;
+
+CALL sp_add_attendance_bonus();
+DROP PROCEDURE sp_add_attendance_bonus;
+
+-- ---------------------------------------------------------------------
+-- 3. 鍘嗗彶鏁版嵁鍏滃簳锛堝彲閫夛級
+-- 浠呭綋鐩爣鐜璇ュ瓧娈靛厑璁� NULL 涓斿瓨鍦� NULL 鍊兼椂锛屾墠闇�瑕佹墽琛屼笅闈袱鏉★紱
+-- 鏍囧噯鐜涓嬪瓧娈典负 NOT NULL 榛樿 100锛屾棤闇�鎵ц銆�
+-- ---------------------------------------------------------------------
+-- UPDATE staff_on_job SET attendance_bonus = 100.00 WHERE attendance_bonus IS NULL;
+-- UPDATE staff_salary_detail SET attendance_bonus = 100.00 WHERE attendance_bonus IS NULL;
+
+-- ---------------------------------------------------------------------
+-- 4. 鎵ц鍚庨獙璇侊細搴旇兘鐪嬪埌涓よ璁板綍锛岀被鍨� decimal(10,2)銆侀粯璁ゅ�� 100.00
+-- ---------------------------------------------------------------------
+SELECT table_name AS 琛ㄥ悕,
+ column_name AS 瀛楁鍚�,
+ column_type AS 绫诲瀷,
+ is_nullable AS 鍙┖,
+ column_default AS 榛樿鍊�,
+ column_comment AS 娉ㄩ噴
+FROM information_schema.columns
+WHERE table_schema = DATABASE()
+ AND table_name IN ('staff_on_job', 'staff_salary_detail')
+ AND column_name = 'attendance_bonus';
+
+-- =====================================================================
+-- 闄勶細鏈鍔熻兘鐨勮涓鸿鏄庯紙鏃犻渶鎵ц SQL锛屼粎澶囨煡锛�
+-- 搴斿彂鍚堣 = 鍏ㄥ嫟濂� + 鍩烘湰宸ヨ祫 脳 鏁堢泭(鎶樺悎鍊嶆暟) + 娲ヨ创 + 鍏朵粬鏀跺叆
+-- 鍏ㄥ嫟濂栧彇鍊间紭鍏堢骇锛氬伐璧勮〃琛屽唴浼犲叆鍊硷紙鏀寔 0锛� > 鍛樺伐妗ｆ staff_on_job.attendance_bonus
+-- =====================================================================
--
Gitblit v1.9.3