實作 : 工讀生薪資管理系統

今天剛好碰到王老闆,他經營了三家飲料店、兩間拉麵餐廳、三間超市。因為工讀生越來越多,每個工讀生都有不同的工作時間。王老闆希望可以開發一套工讀生薪資管理系統,讓會計人員可以登錄工讀生的工作時間來計算薪資,並記錄登錄資料的會計人員,並提供工讀生以電子郵件登入查詢自己的薪資,你覺得這個工讀生薪資系統的DFD與ERD應該怎麼畫?

(1) 工讀生薪資系統的DFD與ERD應該怎麼畫?

根據前述DFD(Data Flow Diagram)的文章,我們先畫Level-0的DFD,大致如下圖 : 


data-flow-diagram
這個Level-0的DFD只是描述有哪些人會跟這個系統互動,會計人員登錄工讀生的工作時間來計算薪資,並記錄登錄資料的會計人員;工讀生可以登入查詢自己的薪資,因此外部entity只有會計人員與工讀生。

你可以試試畫一下Level-1的DFD。

以下則是大致的ERD(Entity Relationship Diagram),描述三個實體 : 工讀生、商店、薪水,為了簡化思考,先把會計放一邊。

商店為何是一個需要考慮的實體? 因為王老闆經營多種商店,工讀生薪資計算可能不同,並且她應該會需要知道各種商店的狀況,因此把商店也視為實體。

當然以下只是目前初步的ERD,後續如果把會計也加進去,可能還會再修改。

Entity Relationship Diagram

上圖為何工讀生跟商店沒有關聯? 而只跟薪水有關連呢? 如果工讀生跟商店關聯,再由商店跟薪水關聯會如何? 如果工讀生跟商店、薪水一起關聯又會如何呢?

這個你可以自己思考看看,我們後續再來討論。

(2) 邏輯資料表及實體資料表應該怎麼規畫?

這個工讀生薪資系統的邏輯資料表及實體資料表應該怎麼設計?這個工讀生薪資系統的主鍵(Primary Key)跟外鍵(Foreign Key)怎麼設計

如果根據上面的ERD來看,邏輯資料表應該如下 (主鍵為畫底線者) : 

stores (id, cate_id)
students (id, stud_name, stud_password, stud_email)
salary (id, store_id, stud_id, salary_date, salary_start, salary_end)

因為登入需要email,所以加入stud_email,並且要記得宣告為NOT NULL UNIQUE (為何?)。

如果再修飾一下,邏輯資料表應該變成如下 :

store_category (id, category_name)
stores (id, cate_id) 商店的cate_id關連到store_category的id
students (id, stud_name, stud_password, stud_email)
salary (id, store_id, stud_id, salary_date, salary_start, salary_end) 
薪水的store_id關連到store的id,stud_id關連到students的id

以上的邏輯資料表是在工讀時薪固定的情況下,如果時薪會變動就必須做些更動如下 :

store_category (id, category_name)
stores (id, cate_id, pay_per_hour)
students (id, stud_name, stud_password, stud_email)
salary (id, store_id, stud_id, salary_date, salary_start, salary_end, salary_total)

在store加入pay_per_hour來調整時薪,在salary加入salary_total紀錄時薪,方便每次工作計算當時薪水。因為沒有每次計算的話,pay_per_hour調整時,整個歷史薪水就全亂了。

然後由以下指令來計算工作多久 :

工作多少分鐘 = TIME_TO_SEC(TIMEDIFF(salary_endsalary_start)) / 60

TIMEDIFF語法參考 https://www.w3schools.com/sql/func_mysql_timediff.asp
TIME_TO_SEC
語法參考 https://www.w3schools.com/sql/func_mysql_time_to_sec.asp

每分鐘薪水 = (pay_per_hour / 60)

salary_total = ROUND(每分鐘薪水*工作多少分鐘, 0) 
以上的0表示四捨五入到整數

ROUND語法參考 https://www.w3schools.com/sql/func_mysql_round.asp

例如 13:00:00 工作到 18:40:00 ,若時薪為$176元 (以每分鐘薪水$2.93計算),則薪水總額是 : 

salary_total = ROUND(2.93*(TIME_TO_SEC(TIMEDIFF('18:40:00', '13:00:00')) / 60), 0) = $996元

工讀生開始工作打卡時,就會寫入 商店id, 工讀生id, 打卡日期, 開始時間

例如
INSERT INTO salary (store_
id, stud_id, salary_date, salary_start) VALUES (1, 1, '2023-05-18', '13:00:00');

工讀生結束工作打卡時,就會寫入 結束時間, 本次薪水

例如 先計算出來本次薪水 @total 

@total = ROUND(ROUND(pay_per_hour/60, 2)*(TIME_TO_SEC(TIMEDIFF('18:40:00', '13:00:00')) / 60), 0) 

然後
UPDATE salary SET salary_end ='18:40:00', salary_total=@total
WHERE id=1; (這個id就根據
開始工作打卡時產生的id為準)

然後實體資料表設計如下 : 

-- store_category 資料表
CREATE TABLE store_category (
id INT AUTO_INCREMENT PRIMARY KEY,
category_name VARCHAR(20)
); 

-- stores 資料表
CREATE TABLE stores (
id INT AUTO_INCREMENT PRIMARY KEY,
cate_id INT,
pay_per_hour DECIMAL(10, 0),
FOREIGN KEY (cate_id) REFERENCES store_category(id)
); 

-- students 資料表
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
stud_name VARCHAR(50),
stud_password VARCHAR(20),
stud_email VARCHAR(100) NOT NULL UNIQUE
);

-- salary 資料表
CREATE TABLE salary (
id INT AUTO_INCREMENT PRIMARY KEY,
store_id INT,
stud_id INT,
salary_date DATE,
salary_start TIME,
salary_end TIME,
salary_total DECIMAL(10, 0),
FOREIGN KEY (store_id) REFERENCES stores(id),
FOREIGN KEY (stud_id) REFERENCES students(id)
);


現在先來插入範例資料 :

-- 插入 store_category 資料
INSERT INTO store_category (category_name) VALUES ('便利商店'),('咖啡廳'),('書店');

-- 插入 stores 資料
INSERT INTO stores (cate_id, pay_per_hour) VALUES (1, 160),(2, 180),(3, 150); 

-- 插入 students 資料
INSERT INTO students (stud_name, stud_password, stud_email) VALUES ('張三', 'password123', '[email protected]'),('李四', 'password456', '[email protected]'),('王五', 'password789', '[email protected]'); 

-- 插入 salary 資料 (開始工作打卡)
INSERT INTO salary (store_id, stud_id, salary_date, salary_start) VALUES (1, 1, '2023-05-18', '13:00:00'),(2, 2, '2023-05-18', '14:00:00'),(3, 3, '2023-05-18', '15:00:00'); 

-- 更新 salary 資料 (結束工作打卡) 
UPDATE salary SET salary_end = '18:40:00' WHERE id = 1;
UPDATE salary SET salary_end = '17:30:00' WHERE id = 2;
UPDATE salary SET salary_end = '20:00:00' WHERE id = 3;

然後就開始寫了觸發器 :

(以下是錯誤的版本)

mysql-trigger


原先設想在工讀生寫入打工的結束時間,以觸發器計算薪水寫入salary表單,後來發現觸發器不管是BEFORE UPDATE或是AFTER UPDATE,都會產生如下的錯誤。

#1442 - Can't update table 'salary' in stored function/trigger because it is already used by statement which invoked this stored function/trigger

意思是無法更新salary表單,因為正在使用中,所以當salary表單在寫入打工的結束時間時,觸發器無法再去更新salary表單。

所以要計算薪水寫入salary表單,不能使用Trigger,需要使用stored procedure來處理了,上面的UPDATE也不能這樣使用了。因此原本UPDATE的欄位salary_end要清空為null (為何要清為null?)


~~~~~~~~~~~~ 後記 ~~~~~~~~~~~~ 

重寫trigger,發現以下方式可以排除錯誤,在BEFORE UPDATE ON salary時,去更新salary中的資料,但是還沒有加上檢查salary_end是否為null。


DELIMITER //

CREATE TRIGGER update_salary_total

BEFORE UPDATE ON salary

FOR EACH ROW

BEGIN

    DECLARE work_minutes DECIMAL(10,2);

    DECLARE pay_per_minute DECIMAL(10,2);

    -- 計算工作多少分鐘

    SET work_minutes = TIME_TO_SEC(TIMEDIFF(NEW.salary_end, NEW.salary_start)) / 60;

    -- 取得每分鐘薪水

    SELECT (pay_per_hour / 60) INTO pay_per_minute 

    FROM stores 

    WHERE id = NEW.store_id;

    -- 計算薪水總額並更新到 NEW.salary_total

    SET NEW.salary_total = ROUND(pay_per_minute * work_minutes, 0);

END //

DELIMITER ;


~~~~~~~~~~~~ ~~~~~~~~~~~~ ~~~~~~~~~~~~ ~~~~~~~~~~~~ ~~~~~~~~~~~~ 


現在為了有效率的寫出stored procedure,我們呼叫ChatGPT-4來幫忙。

當告訴ChatGPT-4所有條件後,他給了以下的stored procedure :

(以下是錯誤的版本)

mysql-trigger

使用 CALL update_salary_end_and_total(1, '18:40:00'); 來寫入結束時間。

但是問題來了,需要輸入salary_id跟結束時間? 工讀生刷卡時並沒有給stud_id 如何可以找到salary_id呢? 

我們這個疑問 「工讀生結束工作時,如何知道p_id? 是否用stud_id較好?」,ChatGPT同意我們的看法,給了以下的修改版本 : 

(以下是錯誤的版本)

mysql-trigger

他的stored procedure這次需要stud_id、salary_date、salary_end,但是問題又來了,我們的疑問是 「工讀生可能一天會工作兩個時段」,這樣透過stud_id、salary_date可能抓到不只一筆資料,當然就無法得到正確的資料。

ChatGPT同意我們的看法,再給了以下的修改版本 : 

(以下是錯誤的版本)

mysql-trigger

這次需要stud_id、salary_date、salary_start、salary_end,我們給他建議「需要p_salary_start 並不好,應該是提供stud_id、salary_end,然後stored procedure根據當天時間,找到salary_end是 null的資料去 update」。

ChatGPT又聽從我們的建議,再改寫如下 :

(以下是錯誤的版本)

mysql-trigger

可是奇怪的是,原本插入的資料是

INSERT INTO salary (store_id, stud_id, salary_date, salary_start) VALUES (1, 1, '2023-05-18', '13:00:00'); 

使用 CALL update_salary_end_and_total(1, '18:40:00'); 當日日期是 5/20,但是資料竟然更新進去2023-05-18的資料了,顯然條件判斷出了問題。

ChatGPT又再改寫如下 :

(以下是錯誤的版本)

mysql-trigger

「但是還有問題,萬一工作的時間跨過凌晨該如何處理?」

ChatGPT又改了語法,處理工作的時間跨過凌晨的問題,後續總共給了五個版本,到第六個版本才搞正確,這五個錯誤的版本都是沒有正確判斷工作時間跨過凌晨,我們就不提供這五個錯誤的版本。

以下是最後正確的版本 :

DELIMITER $$
CREATE PROCEDURE update_salary_end_and_total(
  IN p_stud_id INT,
  IN p_salary_date DATE,
  IN p_salary_end TIME
)

BEGIN
  DECLARE v_id INT;
  DECLARE v_salary_start TIMESTAMP;
  DECLARE v_salary_end TIMESTAMP;
  DECLARE v_pay_per_hour DECIMAL(10, 0);
  DECLARE v_minutes_worked INT;
  DECLARE v_total DECIMAL(10, 0);

-- 獲取工資紀錄的 id 和對應的 salary_start

  SELECT id, CONCAT(salary_date, ' ', salary_start) AS salary_start
  INTO v_id, v_salary_start
  FROM salary
  WHERE stud_id = p_stud_id AND salary_end IS NULL
  ORDER BY salary_date DESC, salary_start DESC
  LIMIT 1;

-- 如果找到了對應的工資紀錄,則繼續執行更新操作

  IF v_id IS NOT NULL THEN

    -- 計算结束時間

    SET v_salary_end = TIMESTAMP(CONCAT(p_salary_date, ' ', p_salary_end));

    -- 獲取每小時的薪資

    SELECT pay_per_hour
    INTO v_pay_per_hour
    FROM stores
    JOIN salary ON stores.id = salary.store_id
    WHERE salary.id = v_id;

    -- 計算工作分鐘數

    SET v_minutes_worked = TIMESTAMPDIFF(MINUTE, v_salary_start, v_salary_end);

    -- 計算薪水

    SET v_total = ROUND((v_pay_per_hour / 60) * v_minutes_worked, 0);

    -- 更新结束時間和薪水

    UPDATE salary
    SET salary_end = p_salary_end, salary_total = v_total
    WHERE id = v_id;
  ELSE

    -- 如果未找到對應的薪水紀錄,則輸出錯誤訊息

    SELECT 'Error: No matching salary record found' AS error_message;

  END IF;

END $$
DELIMITER ;

再使用 CALL update_salary_end_and_total(1, '2023-05-19', '02:30:00'); 終於獲得了正確的結果。

以上就是與ChatGPT協同把解答找到的過程,如果你沒能找到關鍵問題讓他修改,你得到的就是一個無法正常運作的東西而已。

我們自己去寫這個stored procedure,可能需要搞很久,或是根本寫不出來,但是搭配ChatGPT的能力,可能產出了「看起來很正常的結果」,如果你沒有質疑,ChatGPT就混過去了。

當你找到關鍵問題之後,ChatGPT就會把尚無法運作的stored procedure 進行逐步修改。


(3) 如何彙整資料給王老闆?

問題 : 如果王老闆想知道某工讀生2023年一月到四月份的薪資?MySQL語法應該如何

這個需求只牽涉到薪資,因此只跟salary資料表有關。

假設工讀生id=1

SELECT SUM(salary_total)
FROM salary
WHERE
stud_id = 1 AND salary_date BETWEEN '2023-01-01' AND  '2023-04-30';


或是

SELECT salary_date, salary_total
FROM salary
WHERE
stud_id = 1 AND salary_date BETWEEN '2023-01-01' AND  '2023-04-30';


問題 : 如果王老闆知道2023年一月到四月份拉麵餐廳中,哪家拉麵餐廳工讀生的薪資總額最高?MySQL語法應該如何

假設拉麵餐廳類型的 id=1

(以下發現有錯誤)

SELECT stores.id, SUM(salary_total) AS total_salary
FROM stores s
INNER JOIN store_category ON s.cate_id=1
INNER JOIN salary ON stores.id = salary.store_id
WHERE salary_date >= '2023-01-01' AND salary_date <= '2023-04-30'
GROUP BY stores.id
ORDER BY total_salary DESC
LIMIT 1;

(更改為以下)

SELECT st.id, SUM(salary_total) AS total_salary
FROM salary s
INNER JOIN stores st ON s.store_id=st.id
WHERE st.cate_id=1 AND salary_date BETWEEN '2023-01-01' AND  '2023-04-30'
GROUP BY st.id
ORDER BY total_salary DESC
LIMIT 1;


問題 : 如果王老闆知道2022年整年,哪類店家工讀生的薪資總額最高?MySQL語法應該如何

(以下發現有錯誤)

SELECT sc.category_name, SUM(s.salary_total) AS total_salary
FROM stores s
INNER JOIN store_category sc ON s.cate_id = sc.id
INNER JOIN salary sa ON s.id = sa.store_id
WHERE YEAR(sa.salary_date) = 2022
GROUP BY sc.category_name
ORDER BY total_salary DESC
LIMIT 1;

(更改為以下)

SELECT sc.category_name, SUM(s.salary_total) AS total_salary
FROM salary s
INNER JOIN stores st ON s.store_id = st.id
INNER JOIN store_category sc ON st.cate_id = sc.id
WHERE YEAR(s.salary_date) = 2022
GROUP BY sc.category_name
ORDER BY total_salary DESC
LIMIT 1;

以上是從DFD、ERD、邏輯資料庫、實體資料庫,到實際需求的紙上作業。

後續再來實際驗證,並加入比較詳細的資料。

[後記]

(1) 我要如何下指令去查,是否有工讀生的工作時間不在商店營業時間內,商店營業時間是09:00~21:00。 

(2) 我要如何下指令去查,是否有工讀生在同一天的工作時間超過10小時。

(3) 我要如何下指令去查,是否有工讀生謊報工作時間的情況,也就是相同工讀生,他的工作時間是重疊的。

張貼留言

0 留言