349 lines
10 KiB
TypeScript
349 lines
10 KiB
TypeScript
import mysql from 'mysql2/promise'
|
||
|
||
// MySQL 連接設定
|
||
const dbConfig = {
|
||
host: process.env.DB_HOST!,
|
||
port: parseInt(process.env.DB_PORT!),
|
||
database: process.env.DB_DATABASE!,
|
||
user: process.env.DB_USERNAME!,
|
||
password: process.env.DB_PASSWORD!,
|
||
waitForConnections: true,
|
||
connectionLimit: 10,
|
||
queueLimit: 0
|
||
}
|
||
|
||
// 創建連接池
|
||
export const db = mysql.createPool(dbConfig)
|
||
|
||
// 測試連接函數
|
||
export async function testConnection() {
|
||
try {
|
||
const connection = await db.getConnection()
|
||
console.log('MySQL 資料庫連接成功')
|
||
connection.release()
|
||
return true
|
||
} catch (error) {
|
||
console.error('MySQL 資料庫連接失敗:', error)
|
||
return false
|
||
}
|
||
}
|
||
|
||
// Database types
|
||
export interface User {
|
||
id: string
|
||
name: string
|
||
email: string
|
||
role: "executive" | "manager" | "hr"
|
||
avatar_url?: string
|
||
created_at: string
|
||
updated_at: string
|
||
}
|
||
|
||
export interface KPI {
|
||
id: string
|
||
user_id: string
|
||
title: string
|
||
description?: string
|
||
category: "financial" | "operational" | "team" | "innovation" | "strategy"
|
||
weight: number
|
||
target_value: number
|
||
current_value: number
|
||
unit?: string
|
||
start_date: string
|
||
end_date: string
|
||
approved_by?: string
|
||
status: "pending" | "approved" | "rejected" | "active" | "completed"
|
||
created_at: string
|
||
updated_at: string
|
||
}
|
||
|
||
export interface KPIProgress {
|
||
id: string
|
||
kpi_id: string
|
||
progress: number
|
||
comment?: string
|
||
attachment_url?: string
|
||
blockers?: string
|
||
created_by: string
|
||
created_at: string
|
||
}
|
||
|
||
export interface Review {
|
||
id: string
|
||
reviewer_id: string
|
||
reviewee_id: string
|
||
review_type: "quarterly" | "annual" | "one_on_one" | "goal_setting"
|
||
scheduled_date: string
|
||
completed_date?: string
|
||
status: "scheduled" | "in_progress" | "completed" | "cancelled"
|
||
summary?: string
|
||
overall_rating?: number
|
||
created_at: string
|
||
updated_at: string
|
||
}
|
||
|
||
// CRUD API functions
|
||
export const kpiService = {
|
||
// READ - 獲取用戶的 KPI
|
||
async getKPIsByUser(userId: string): Promise<KPI[]> {
|
||
try {
|
||
const [rows] = await db.execute(
|
||
'SELECT * FROM kpi WHERE user_id = ? AND status = ?',
|
||
[userId, 'active']
|
||
)
|
||
return rows as KPI[]
|
||
} catch (error) {
|
||
console.error('獲取 KPI 失敗:', error)
|
||
throw error
|
||
}
|
||
},
|
||
|
||
// CREATE - 創建新 KPI
|
||
async createKPI(kpi: Omit<KPI, "id" | "created_at" | "updated_at">): Promise<KPI> {
|
||
try {
|
||
const id = `kpi_${Date.now()}_${Math.random().toString(36).substr(2, 9)}`
|
||
const now = new Date().toISOString()
|
||
|
||
await db.execute(
|
||
`INSERT INTO kpi (id, user_id, title, description, category, weight, target_value,
|
||
current_value, unit, start_date, end_date, approved_by, status, created_at, updated_at)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
|
||
[
|
||
id, kpi.user_id, kpi.title, kpi.description, kpi.category, kpi.weight,
|
||
kpi.target_value, kpi.current_value, kpi.unit, kpi.start_date, kpi.end_date,
|
||
kpi.approved_by, kpi.status, now, now
|
||
]
|
||
)
|
||
|
||
const [rows] = await db.execute('SELECT * FROM kpi WHERE id = ?', [id])
|
||
return (rows as KPI[])[0]
|
||
} catch (error) {
|
||
console.error('創建 KPI 失敗:', error)
|
||
throw error
|
||
}
|
||
},
|
||
|
||
// UPDATE - 更新 KPI 進度
|
||
async updateKPIProgress(kpiId: string, progress: number, comment?: string, blockers?: string, userId?: string): Promise<void> {
|
||
try {
|
||
const id = `progress_${Date.now()}_${Math.random().toString(36).substr(2, 9)}`
|
||
const now = new Date().toISOString()
|
||
|
||
await db.execute(
|
||
`INSERT INTO kpi_progress (id, kpi_id, progress, comment, blockers, created_by, created_at)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?)`,
|
||
[id, kpiId, progress, comment, blockers, userId || 'system', now]
|
||
)
|
||
|
||
// 同時更新 KPI 的當前值
|
||
await db.execute(
|
||
'UPDATE kpi SET current_value = ?, updated_at = ? WHERE id = ?',
|
||
[progress, now, kpiId]
|
||
)
|
||
} catch (error) {
|
||
console.error('更新 KPI 進度失敗:', error)
|
||
throw error
|
||
}
|
||
},
|
||
|
||
// UPDATE - 更新 KPI
|
||
async updateKPI(id: string, updates: Partial<KPI>): Promise<KPI> {
|
||
try {
|
||
const now = new Date().toISOString()
|
||
const fields = Object.keys(updates).filter(key => key !== 'id').map(key => `${key} = ?`)
|
||
const values = Object.values(updates).filter((_, index) => Object.keys(updates)[index] !== 'id')
|
||
|
||
if (fields.length === 0) throw new Error('沒有要更新的欄位')
|
||
|
||
await db.execute(
|
||
`UPDATE kpi SET ${fields.join(', ')}, updated_at = ? WHERE id = ?`,
|
||
[...values, now, id]
|
||
)
|
||
|
||
const [rows] = await db.execute('SELECT * FROM kpi WHERE id = ?', [id])
|
||
return (rows as KPI[])[0]
|
||
} catch (error) {
|
||
console.error('更新 KPI 失敗:', error)
|
||
throw error
|
||
}
|
||
},
|
||
|
||
// DELETE - 刪除 KPI（軟刪除，將狀態改為 deleted）
|
||
async deleteKPI(id: string): Promise<void> {
|
||
try {
|
||
const now = new Date().toISOString()
|
||
await db.execute(
|
||
'UPDATE kpi SET status = ?, updated_at = ? WHERE id = ?',
|
||
['deleted', now, id]
|
||
)
|
||
} catch (error) {
|
||
console.error('刪除 KPI 失敗:', error)
|
||
throw error
|
||
}
|
||
},
|
||
}
|
||
|
||
export const reviewService = {
|
||
// READ - 獲取即將到來的評審
|
||
async getUpcomingReviews(userId: string): Promise<Review[]> {
|
||
try {
|
||
const [rows] = await db.execute(
|
||
`SELECT r.*, u.name as reviewee_name, u.email as reviewee_email
|
||
FROM reviews r
|
||
LEFT JOIN users u ON r.reviewee_id = u.id
|
||
WHERE r.reviewer_id = ? AND r.status = ?
|
||
ORDER BY r.scheduled_date ASC`,
|
||
[userId, 'scheduled']
|
||
)
|
||
return rows as Review[]
|
||
} catch (error) {
|
||
console.error('獲取評審列表失敗:', error)
|
||
throw error
|
||
}
|
||
},
|
||
|
||
// CREATE - 創建新評審
|
||
async createReview(review: Omit<Review, "id" | "created_at" | "updated_at">): Promise<Review> {
|
||
try {
|
||
const id = `review_${Date.now()}_${Math.random().toString(36).substr(2, 9)}`
|
||
const now = new Date().toISOString()
|
||
|
||
await db.execute(
|
||
`INSERT INTO reviews (id, reviewer_id, reviewee_id, review_type, scheduled_date,
|
||
completed_date, status, summary, overall_rating, created_at, updated_at)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
|
||
[
|
||
id, review.reviewer_id, review.reviewee_id, review.review_type, review.scheduled_date,
|
||
review.completed_date, review.status, review.summary, review.overall_rating, now, now
|
||
]
|
||
)
|
||
|
||
const [rows] = await db.execute('SELECT * FROM reviews WHERE id = ?', [id])
|
||
return (rows as Review[])[0]
|
||
} catch (error) {
|
||
console.error('創建評審失敗:', error)
|
||
throw error
|
||
}
|
||
},
|
||
|
||
// READ - 獲取所有評審
|
||
async getAllReviews(): Promise<Review[]> {
|
||
try {
|
||
const [rows] = await db.execute('SELECT * FROM reviews ORDER BY created_at DESC')
|
||
return rows as Review[]
|
||
} catch (error) {
|
||
console.error('獲取所有評審失敗:', error)
|
||
throw error
|
||
}
|
||
},
|
||
|
||
// UPDATE - 更新評審
|
||
async updateReview(id: string, updates: Partial<Review>): Promise<Review> {
|
||
try {
|
||
const now = new Date().toISOString()
|
||
const fields = Object.keys(updates).filter(key => key !== 'id').map(key => `${key} = ?`)
|
||
const values = Object.values(updates).filter((_, index) => Object.keys(updates)[index] !== 'id')
|
||
|
||
if (fields.length === 0) throw new Error('沒有要更新的欄位')
|
||
|
||
await db.execute(
|
||
`UPDATE reviews SET ${fields.join(', ')}, updated_at = ? WHERE id = ?`,
|
||
[...values, now, id]
|
||
)
|
||
|
||
const [rows] = await db.execute('SELECT * FROM reviews WHERE id = ?', [id])
|
||
return (rows as Review[])[0]
|
||
} catch (error) {
|
||
console.error('更新評審失敗:', error)
|
||
throw error
|
||
}
|
||
},
|
||
|
||
// DELETE - 刪除評審
|
||
async deleteReview(id: string): Promise<void> {
|
||
try {
|
||
await db.execute('DELETE FROM reviews WHERE id = ?', [id])
|
||
} catch (error) {
|
||
console.error('刪除評審失敗:', error)
|
||
throw error
|
||
}
|
||
},
|
||
}
|
||
|
||
// 用戶管理服務
|
||
export const userService = {
|
||
// CREATE - 創建用戶
|
||
async createUser(user: Omit<User, "id" | "created_at" | "updated_at">): Promise<User> {
|
||
try {
|
||
const id = `user_${Date.now()}_${Math.random().toString(36).substr(2, 9)}`
|
||
const now = new Date().toISOString()
|
||
|
||
await db.execute(
|
||
`INSERT INTO users (id, name, email, role, avatar_url, created_at, updated_at)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?)`,
|
||
[id, user.name, user.email, user.role, user.avatar_url, now, now]
|
||
)
|
||
|
||
const [rows] = await db.execute('SELECT * FROM users WHERE id = ?', [id])
|
||
return (rows as User[])[0]
|
||
} catch (error) {
|
||
console.error('創建用戶失敗:', error)
|
||
throw error
|
||
}
|
||
},
|
||
|
||
// READ - 獲取所有用戶
|
||
async getAllUsers(): Promise<User[]> {
|
||
try {
|
||
const [rows] = await db.execute('SELECT * FROM users ORDER BY created_at DESC')
|
||
return rows as User[]
|
||
} catch (error) {
|
||
console.error('獲取用戶列表失敗:', error)
|
||
throw error
|
||
}
|
||
},
|
||
|
||
// READ - 根據 ID 獲取用戶
|
||
async getUserById(id: string): Promise<User | null> {
|
||
try {
|
||
const [rows] = await db.execute('SELECT * FROM users WHERE id = ?', [id])
|
||
const users = rows as User[]
|
||
return users.length > 0 ? users[0] : null
|
||
} catch (error) {
|
||
console.error('獲取用戶失敗:', error)
|
||
throw error
|
||
}
|
||
},
|
||
|
||
// UPDATE - 更新用戶
|
||
async updateUser(id: string, updates: Partial<User>): Promise<User> {
|
||
try {
|
||
const now = new Date().toISOString()
|
||
const fields = Object.keys(updates).filter(key => key !== 'id').map(key => `${key} = ?`)
|
||
const values = Object.values(updates).filter((_, index) => Object.keys(updates)[index] !== 'id')
|
||
|
||
if (fields.length === 0) throw new Error('沒有要更新的欄位')
|
||
|
||
await db.execute(
|
||
`UPDATE users SET ${fields.join(', ')}, updated_at = ? WHERE id = ?`,
|
||
[...values, now, id]
|
||
)
|
||
|
||
const [rows] = await db.execute('SELECT * FROM users WHERE id = ?', [id])
|
||
return (rows as User[])[0]
|
||
} catch (error) {
|
||
console.error('更新用戶失敗:', error)
|
||
throw error
|
||
}
|
||
},
|
||
|
||
// DELETE - 刪除用戶
|
||
async deleteUser(id: string): Promise<void> {
|
||
try {
|
||
await db.execute('DELETE FROM users WHERE id = ?', [id])
|
||
} catch (error) {
|
||
console.error('刪除用戶失敗:', error)
|
||
throw error
|
||
}
|
||
},
|
||
} |