113 lines
3.4 KiB
Plaintext
113 lines
3.4 KiB
Plaintext
DELIMITER //
|
|
|
|
CREATE OR REPLACE PROCEDURE eip_getCalendar (
|
|
IN p_functiontag VARCHAR(100),
|
|
IN p_userid VARCHAR(20),
|
|
IN p_start VARCHAR(30),
|
|
IN p_end VARCHAR(30)
|
|
)
|
|
/*
|
|
Function : 取得行事曆資料, 並考慮雲端時間差問題
|
|
Description: 使用 FullCalendar + Cloud 主機時的時間差處理方案
|
|
a. 在 FullCalendar+Vue 程式中使用 local 時間而非 UTC
|
|
b. Cloud Server 使用 UTC 時間，需進行時間調整
|
|
c. 使用 cloud_addtime 函數調整時間差 (目前設為減8小時)
|
|
Build Date : 2023/07/19 (MariaDB 版本)
|
|
call eip_getcalendar('eipm02','admin','2023-12-31T16:00:00.000Z','2026-12-30T16:00:00.000Z');
|
|
|
|
Modify History
|
|
Item Who Date Modify Docs
|
|
==== ========== ========== ================================================
|
|
1 Michael 2022/12/14 加入考慮 Azure 的時間差
|
|
2 Michael 2022/12/23 加入 FullCalendar 相關註解說明
|
|
3 Michael 2024/03/11 傳入參數由 tablename 改為 functiontag
|
|
4 Michael 2025/07/20 改寫為 MariaDB 並最佳化(ChatGPT+DeepSeek)
|
|
*/
|
|
BEGIN
|
|
-- 處理一下日期
|
|
SET p_start = STR_TO_DATE(p_start, '%Y-%m-%dT%H:%i:%s.000Z');
|
|
SET p_end = STR_TO_DATE(p_end, '%Y-%m-%dT%H:%i:%s.000Z');
|
|
|
|
-- 創建臨時表存儲結果
|
|
DROP TEMPORARY TABLE IF EXISTS tmp_calendar;
|
|
CREATE TEMPORARY TABLE tmp_calendar (
|
|
id VARCHAR(100),
|
|
title NVARCHAR(100),
|
|
start DATETIME,
|
|
end DATETIME,
|
|
create_at VARCHAR(20)
|
|
);
|
|
|
|
-- 根據不同的 functiontag 查詢不同的表
|
|
CASE p_functiontag
|
|
WHEN 'eipm15' THEN -- 會議室預約
|
|
INSERT INTO tmp_calendar
|
|
SELECT
|
|
uniqueno,
|
|
response_desc,
|
|
CONCAT(startdate, ' ', starttime),
|
|
CONCAT(end_date, ' ', endtime),
|
|
personid
|
|
FROM eipplace_fc
|
|
WHERE DATE(startdate) >= DATE(p_start)
|
|
AND DATE(end_date) <= DATE(p_end)
|
|
AND (personid = p_userid OR create_user = p_userid OR update_user = p_userid);
|
|
|
|
WHEN 'eipm13' THEN -- 會議通知
|
|
INSERT INTO tmp_calendar
|
|
SELECT
|
|
uniqueno,
|
|
meeting_title,
|
|
CONCAT(startdate, ' ', starttime),
|
|
CONCAT(end_date, ' ', end_time),
|
|
boss_personid
|
|
FROM eipmeetingrec_m
|
|
WHERE DATE(startdate) >= DATE(p_start)
|
|
AND DATE(end_date) <= DATE(p_end)
|
|
AND (boss_personid = p_userid OR create_user = p_userid OR update_user = p_userid);
|
|
|
|
WHEN 'eipm02' THEN -- 行程管理
|
|
INSERT INTO tmp_calendar
|
|
SELECT
|
|
uuid,
|
|
title,
|
|
start_time,
|
|
end_time,
|
|
personid
|
|
FROM eip_new_calendar
|
|
WHERE DATE(start_time) >= DATE(p_start)
|
|
AND DATE(end_time) <= DATE(p_end)
|
|
AND (create_user = p_userid OR personid = p_userid);
|
|
|
|
ELSE -- 默認處理 eip_core_calendar
|
|
INSERT INTO tmp_calendar
|
|
SELECT
|
|
id,
|
|
title,
|
|
start_time,
|
|
end_time,
|
|
personid
|
|
FROM eip_core_calendar
|
|
WHERE start_time >= p_start
|
|
AND end_time <= p_end
|
|
AND personid = p_userid;
|
|
END CASE;
|
|
|
|
-- dataset 0(因為只回傳 dataset = 1 一直會報錯 20251120
|
|
select 0, 'ok' as msg;
|
|
|
|
-- 處理雲端時間差並返回結果
|
|
SELECT
|
|
id,
|
|
title,
|
|
-- cloud_addtime(start) AS start,
|
|
start,
|
|
-- cloud_addtime(end) AS end,
|
|
end,
|
|
create_at
|
|
FROM tmp_calendar;
|
|
|
|
-- 自動清理臨時表 (MariaDB 會話結束時自動刪除)
|
|
END //
|
|
|
|
DELIMITER ; |