Porsche Chen
360533393f
Major Features: - ✅ Multi-tenant architecture (tenant isolation) - ✅ Employee CRUD with lifecycle management (onboarding/offboarding) - ✅ Department tree structure with email domain management - ✅ Company info management (single-record editing) - ✅ System functions CRUD (permission management) - ✅ Email account management (multi-account per employee) - ✅ Keycloak SSO integration (auth.lab.taipei) - ✅ Redis session storage (10.1.0.254:6379) - Solves Cookie 4KB limitation - Cross-system session sharing - Sliding expiration (8 hours) - Automatic token refresh Technical Stack: Backend: - FastAPI + SQLAlchemy - PostgreSQL 16 (10.1.0.20:5433) - Keycloak Admin API integration - Docker Mailserver integration (SSH) - Alembic migrations Frontend: - Next.js 14 (App Router) - NextAuth 4 with Keycloak Provider - Redis session storage (ioredis) - Tailwind CSS Infrastructure: - Redis 7 (10.1.0.254:6379) - Session + Cache - Keycloak 26.1.0 (auth.lab.taipei) - Docker Mailserver (10.1.0.254) Architecture Highlights: - Session管理由 Keycloak + Redis 統一控制 - 支援多系統 (HR/WebMail/Calendar/Drive/Office) 共享 session - Token 自動刷新,異質服務整合 - 未來可無縫遷移到雲端 Co-Authored-By: Claude Sonnet 4.5 <noreply@anthropic.com>
276 lines
6.5 KiB
SQL
276 lines
6.5 KiB
SQL
-- ====================================
|
|
-- HR Portal - 測試資料 (修正版)
|
|
-- ====================================
|
|
|
|
-- 插入測試員工
|
|
INSERT INTO employees (
|
|
employee_id,
|
|
username,
|
|
first_name,
|
|
last_name,
|
|
chinese_name,
|
|
email,
|
|
mobile,
|
|
business_unit_id,
|
|
position,
|
|
job_level,
|
|
hire_date,
|
|
status
|
|
) VALUES
|
|
-- 管理層
|
|
(
|
|
'E0001',
|
|
'porsche.chen',
|
|
'Porsche',
|
|
'Chen',
|
|
'陳博駿',
|
|
'porsche.chen@porscheworld.tw',
|
|
'0912-345-678',
|
|
4, -- 管理部門
|
|
'CTO / 技術長',
|
|
'C-Level',
|
|
'2020-01-01',
|
|
'active'
|
|
),
|
|
|
|
-- 智能研發服務事業部
|
|
(
|
|
'E1001',
|
|
'alice.wang',
|
|
'Alice',
|
|
'Wang',
|
|
'王小華',
|
|
'alice.wang@lab.taipei',
|
|
'0922-111-222',
|
|
3, -- 智能研發
|
|
'Technical Director',
|
|
'Director',
|
|
'2020-06-01',
|
|
'active'
|
|
),
|
|
|
|
(
|
|
'E1002',
|
|
'bob.chen',
|
|
'Bob',
|
|
'Chen',
|
|
'陳大明',
|
|
'bob.chen@lab.taipei',
|
|
'0933-222-333',
|
|
3, -- 智能研發
|
|
'Senior Software Engineer',
|
|
'Senior',
|
|
'2021-03-15',
|
|
'active'
|
|
),
|
|
|
|
-- 玄鐵風能授權服務事業部
|
|
(
|
|
'E2001',
|
|
'charlie.lin',
|
|
'Charlie',
|
|
'Lin',
|
|
'林小風',
|
|
'charlie.lin@ease.taipei',
|
|
'0944-333-444',
|
|
1, -- 玄鐵風能
|
|
'Wind Energy Consultant',
|
|
'Senior',
|
|
'2021-08-01',
|
|
'active'
|
|
),
|
|
|
|
-- 國際碳權申請服務事業部
|
|
(
|
|
'E3001',
|
|
'diana.wu',
|
|
'Diana',
|
|
'Wu',
|
|
'吳小綠',
|
|
'diana.wu@ease.taipei',
|
|
'0955-444-555',
|
|
2, -- 國際碳權
|
|
'Carbon Credit Specialist',
|
|
'Staff',
|
|
'2022-01-10',
|
|
'active'
|
|
);
|
|
|
|
-- 插入郵件帳號
|
|
INSERT INTO email_accounts (employee_id, email_address, mailbox_quota_mb, is_active)
|
|
SELECT
|
|
e.id,
|
|
e.email,
|
|
CASE e.job_level
|
|
WHEN 'C-Level' THEN 20480
|
|
WHEN 'Director' THEN 10240
|
|
WHEN 'Manager' THEN 10240
|
|
WHEN 'Senior' THEN 5120
|
|
ELSE 5120
|
|
END as quota,
|
|
true
|
|
FROM employees e;
|
|
|
|
-- 插入網路硬碟
|
|
INSERT INTO network_drives (
|
|
employee_id,
|
|
drive_name,
|
|
drive_path,
|
|
quota_gb,
|
|
webdav_url,
|
|
smb_path,
|
|
is_active
|
|
)
|
|
SELECT
|
|
e.id,
|
|
e.username || '_personal',
|
|
'/nas/users/' || e.username,
|
|
CASE e.job_level
|
|
WHEN 'C-Level' THEN 500
|
|
WHEN 'Director' THEN 200
|
|
WHEN 'Manager' THEN 100
|
|
WHEN 'Senior' THEN 80
|
|
ELSE 50
|
|
END as quota,
|
|
'https://nas.porscheworld.tw/webdav/' || e.username,
|
|
'//10.1.0.30/homes/' || e.username,
|
|
true
|
|
FROM employees e;
|
|
|
|
-- 插入系統權限
|
|
INSERT INTO system_permissions (employee_id, system_name, access_level, is_active, notes)
|
|
SELECT e.id, 'HR Portal',
|
|
CASE
|
|
WHEN e.job_level IN ('C-Level', 'Director') THEN 'admin'
|
|
WHEN e.job_level = 'Manager' THEN 'manager'
|
|
ELSE 'user'
|
|
END,
|
|
true,
|
|
'Initial access granted'
|
|
FROM employees e;
|
|
|
|
-- 額外權限: Gitea
|
|
INSERT INTO system_permissions (employee_id, system_name, access_level, is_active, notes)
|
|
SELECT e.id, 'Gitea', 'user', true, 'Git repository access'
|
|
FROM employees e
|
|
WHERE e.business_unit_id = 3; -- 智能研發事業部
|
|
|
|
-- 額外權限: Portainer (給 IT 人員)
|
|
INSERT INTO system_permissions (employee_id, system_name, access_level, is_active, notes)
|
|
VALUES
|
|
((SELECT id FROM employees WHERE username = 'porsche.chen'), 'Portainer', 'admin', true, 'Docker management');
|
|
|
|
-- 插入測試專案
|
|
INSERT INTO projects (project_code, project_name, description, project_manager_id, start_date, status)
|
|
VALUES
|
|
(
|
|
'WIND-2024-001',
|
|
'離岸風電技術評估',
|
|
'台中港離岸風電場技術可行性評估',
|
|
(SELECT id FROM employees WHERE username = 'charlie.lin'),
|
|
'2024-01-15',
|
|
'in_progress'
|
|
),
|
|
(
|
|
'CARBON-2024-001',
|
|
'企業碳盤查輔導',
|
|
'協助製造業進行 ISO 14064-1 碳盤查',
|
|
(SELECT id FROM employees WHERE username = 'diana.wu'),
|
|
'2024-02-01',
|
|
'in_progress'
|
|
),
|
|
(
|
|
'AI-2024-001',
|
|
'HR Portal 系統開發',
|
|
'開發整合 Keycloak 的人資管理系統',
|
|
(SELECT id FROM employees WHERE username = 'alice.wang'),
|
|
'2024-01-01',
|
|
'in_progress'
|
|
);
|
|
|
|
-- 插入專案成員
|
|
INSERT INTO project_members (project_id, employee_id, role)
|
|
VALUES
|
|
-- WIND-2024-001 團隊
|
|
((SELECT id FROM projects WHERE project_code = 'WIND-2024-001'),
|
|
(SELECT id FROM employees WHERE username = 'charlie.lin'),
|
|
'Project Manager'),
|
|
|
|
-- CARBON-2024-001 團隊
|
|
((SELECT id FROM projects WHERE project_code = 'CARBON-2024-001'),
|
|
(SELECT id FROM employees WHERE username = 'diana.wu'),
|
|
'Project Manager'),
|
|
|
|
-- AI-2024-001 團隊
|
|
((SELECT id FROM projects WHERE project_code = 'AI-2024-001'),
|
|
(SELECT id FROM employees WHERE username = 'alice.wang'),
|
|
'Project Manager'),
|
|
((SELECT id FROM projects WHERE project_code = 'AI-2024-001'),
|
|
(SELECT id FROM employees WHERE username = 'bob.chen'),
|
|
'Lead Developer'),
|
|
((SELECT id FROM projects WHERE project_code = 'AI-2024-001'),
|
|
(SELECT id FROM employees WHERE username = 'porsche.chen'),
|
|
'Technical Advisor');
|
|
|
|
-- 驗證結果
|
|
SELECT '=== 員工列表 ===' as info;
|
|
SELECT
|
|
e.employee_id,
|
|
e.username,
|
|
e.chinese_name,
|
|
bu.name as business_unit,
|
|
e.position,
|
|
e.job_level
|
|
FROM employees e
|
|
LEFT JOIN business_units bu ON e.business_unit_id = bu.id
|
|
ORDER BY e.employee_id;
|
|
|
|
SELECT '=== 郵件帳號 ===' as info;
|
|
SELECT
|
|
e.username,
|
|
ea.email_address,
|
|
ea.mailbox_quota_mb || ' MB' as quota
|
|
FROM email_accounts ea
|
|
JOIN employees e ON ea.employee_id = e.id
|
|
ORDER BY e.employee_id;
|
|
|
|
SELECT '=== 網路硬碟 ===' as info;
|
|
SELECT
|
|
e.username,
|
|
nd.drive_name,
|
|
nd.quota_gb || ' GB' as quota
|
|
FROM network_drives nd
|
|
JOIN employees e ON nd.employee_id = e.id
|
|
ORDER BY e.employee_id;
|
|
|
|
SELECT '=== 系統權限 ===' as info;
|
|
SELECT
|
|
e.username,
|
|
sp.system_name,
|
|
sp.access_level
|
|
FROM system_permissions sp
|
|
JOIN employees e ON sp.employee_id = e.id
|
|
WHERE sp.is_active = true
|
|
ORDER BY e.employee_id, sp.system_name;
|
|
|
|
SELECT '=== 專案列表 ===' as info;
|
|
SELECT
|
|
p.project_code,
|
|
p.project_name,
|
|
e.chinese_name as manager,
|
|
COUNT(pm.id) as members,
|
|
p.status
|
|
FROM projects p
|
|
LEFT JOIN employees e ON p.project_manager_id = e.id
|
|
LEFT JOIN project_members pm ON p.id = pm.project_id
|
|
GROUP BY p.id, p.project_code, p.project_name, e.chinese_name, p.status
|
|
ORDER BY p.project_code;
|
|
|
|
SELECT '=== 完成! ===' as info;
|
|
SELECT
|
|
(SELECT COUNT(*) FROM employees) as employees,
|
|
(SELECT COUNT(*) FROM email_accounts) as email_accounts,
|
|
(SELECT COUNT(*) FROM network_drives) as network_drives,
|
|
(SELECT COUNT(*) FROM system_permissions WHERE is_active = true) as permissions,
|
|
(SELECT COUNT(*) FROM projects) as projects;
|