diff options
Diffstat (limited to 'lib/dashboard/partners-service.ts')
| -rw-r--r-- | lib/dashboard/partners-service.ts | 447 |
1 files changed, 447 insertions, 0 deletions
diff --git a/lib/dashboard/partners-service.ts b/lib/dashboard/partners-service.ts new file mode 100644 index 00000000..327a16a9 --- /dev/null +++ b/lib/dashboard/partners-service.ts @@ -0,0 +1,447 @@ +"use server"; + +import db from "@/db/db"; +import { sql } from "drizzle-orm"; +import { getServerSession } from "next-auth/next"; +import { authOptions } from "@/app/api/auth/[...nextauth]/route"; +import { getPartnerTablesByDomain } from "@/config/partners-dashboard-table"; +import { TableConfig } from "@/types/dashboard"; + +export interface PartnersDashboardStats { + tableName: string; + displayName: string; + total: number; + pending: number; + inProgress: number; + completed: number; +} + +export interface PartnersUserDashboardStats extends PartnersDashboardStats { + myTotal: number; + myPending: number; + myInProgress: number; + myCompleted: number; +} + +export interface PartnersDashboardData { + domain: string; + companyId: string; + teamStats: PartnersDashboardStats[]; + userStats: PartnersUserDashboardStats[]; + summary: { + totalTasks: number; + myTasks: number; + teamPending: number; + teamInProgress: number; + teamCompleted: number; + myPending: number; + myInProgress: number; + myCompleted: number; + }; +} + +// Partners 팀 대시보드 데이터 조회 (회사 필터링 포함) +export async function getPartnersTeamDashboardData(domain: string): Promise<PartnersDashboardStats[]> { + try { + const session = await getServerSession(authOptions); + if (!session?.user?.companyId) { + throw new Error("회사 정보가 없습니다."); + } + + const companyId = session.user.companyId; + const tables = getPartnerTablesByDomain(domain); + + if (tables.length === 0) { + console.warn(`파트너 도메인 '${domain}'에 대한 테이블이 없습니다.`); + return []; + } + + console.log(`👥 회사 ID: ${companyId}로 파트너 데이터 조회`); + + // 병렬 처리로 성능 향상 + const results = await Promise.allSettled( + tables.map(tableConfig => getPartnersTableStats(tableConfig, companyId)) + ); + + // 성공한 결과만 반환 + const successfulResults: PartnersDashboardStats[] = []; + results.forEach((result, index) => { + if (result.status === 'fulfilled') { + successfulResults.push(result.value); + } else { + console.error(`파트너 테이블 ${tables[index].tableName} 통계 조회 실패:`, result.reason); + } + }); + + console.log('📊 파트너 팀 대시보드 결과:', successfulResults); + return successfulResults; + } catch (error) { + console.error("파트너 팀 대시보드 데이터 조회 실패:", error); + throw new Error("파트너 팀 대시보드 데이터를 불러오는데 실패했습니다."); + } +} + +// Partners 사용자 대시보드 데이터 조회 +export async function getPartnersUserDashboardData(domain: string): Promise<PartnersUserDashboardStats[]> { + try { + const session = await getServerSession(authOptions); + if (!session?.user?.id || !session?.user?.companyId) { + throw new Error("사용자 또는 회사 정보가 없습니다."); + } + + const userId = session.user.id; + const companyId = session.user.companyId; + const tables = getPartnerTablesByDomain(domain); + + if (tables.length === 0) { + console.warn(`파트너 도메인 '${domain}'에 대한 테이블이 없습니다.`); + return []; + } + + console.log(`👤 사용자 ID: ${userId}, 회사 ID: ${companyId}`); + + // 병렬 처리로 성능 향상 + const results = await Promise.allSettled( + tables.map(async (tableConfig) => { + const [teamStats, userStats] = await Promise.all([ + getPartnersTableStats(tableConfig, companyId), + getPartnersUserTableStats(tableConfig, companyId, userId) + ]); + + return { + ...teamStats, + myTotal: userStats.total, + myPending: userStats.pending, + myInProgress: userStats.inProgress, + myCompleted: userStats.completed + } as PartnersUserDashboardStats; + }) + ); + + // 성공한 결과만 반환 + const successfulResults: PartnersUserDashboardStats[] = []; + results.forEach((result, index) => { + if (result.status === 'fulfilled') { + successfulResults.push(result.value); + } else { + console.error(`파트너 테이블 ${tables[index].tableName} 사용자 통계 조회 실패:`, result.reason); + } + }); + + return successfulResults; + } catch (error) { + console.error("파트너 사용자 대시보드 데이터 조회 실패:", error); + throw new Error("파트너 사용자 대시보드 데이터를 불러오는데 실패했습니다."); + } +} + +// Partners 전체 대시보드 데이터 조회 +export async function getPartnersDashboardData(domain: string): Promise<PartnersDashboardData> { + try { + const session = await getServerSession(authOptions); + if (!session?.user?.id || !session?.user?.companyId) { + throw new Error("사용자 또는 회사 정보가 없습니다."); + } + + const [teamStats, userStats] = await Promise.all([ + getPartnersTeamDashboardData(domain), + getPartnersUserDashboardData(domain) + ]); + + // 요약 통계 계산 + const summary = { + totalTasks: teamStats.reduce((sum, stat) => sum + stat.total, 0), + myTasks: userStats.reduce((sum, stat) => sum + stat.myTotal, 0), + teamPending: teamStats.reduce((sum, stat) => sum + stat.pending, 0), + teamInProgress: teamStats.reduce((sum, stat) => sum + stat.inProgress, 0), + teamCompleted: teamStats.reduce((sum, stat) => sum + stat.completed, 0), + myPending: userStats.reduce((sum, stat) => sum + stat.myPending, 0), + myInProgress: userStats.reduce((sum, stat) => sum + stat.myInProgress, 0), + myCompleted: userStats.reduce((sum, stat) => sum + stat.myCompleted, 0) + }; + + return { + domain, + companyId: session.user.companyId, + teamStats, + userStats, + summary + }; + } catch (error) { + console.error("파트너 대시보드 데이터 조회 실패:", error); + throw new Error("파트너 대시보드 데이터를 불러오는데 실패했습니다."); + } +} + +// Partners 테이블별 전체 통계 조회 (회사 필터링 포함) +async function getPartnersTableStats(config: TableConfig, companyId: string): Promise<PartnersDashboardStats> { + try { + console.log(`\n🔍 파트너 테이블 ${config.tableName} 통계 조회 (회사: ${companyId})`); + + // 1단계: 회사별 총 개수 확인 + const totalQuery = ` + SELECT COUNT(*)::INTEGER as total + FROM "${config.tableName}" + WHERE "vendor_id" = '${companyId}' + `; + console.log("Total SQL:", totalQuery); + + const totalResult = await db.execute(sql.raw(totalQuery)); + console.log("Total 결과:", totalResult.rows[0]); + + // 2단계: 회사별 상태값 분포 확인 + const statusQuery = ` + SELECT "${config.statusField}" as status, COUNT(*) as count + FROM "${config.tableName}" + WHERE "vendor_id" = '${companyId}' AND "${config.statusField}" IS NOT NULL + GROUP BY "${config.statusField}" + ORDER BY count DESC + `; + console.log("Status SQL:", statusQuery); + + const statusResult = await db.execute(sql.raw(statusQuery)); + console.log("Status 결과:", statusResult.rows); + + // 3단계: 상태별 개수 조회 + const pendingValues = Object.entries(config.statusMapping) + .filter(([_, mapped]) => mapped === 'pending') + .map(([original]) => original); + + const inProgressValues = Object.entries(config.statusMapping) + .filter(([_, mapped]) => mapped === 'in_progress') + .map(([original]) => original); + + const completedValues = Object.entries(config.statusMapping) + .filter(([_, mapped]) => mapped === 'completed') + .map(([original]) => original); + + console.log("파트너 상태 매핑:"); + console.log("- pending:", pendingValues); + console.log("- inProgress:", inProgressValues); + console.log("- completed:", completedValues); + + let pendingCount = 0; + let inProgressCount = 0; + let completedCount = 0; + + // Pending 개수 (회사 필터 포함) + if (pendingValues.length > 0) { + const pendingValuesList = pendingValues.map(v => `'${v.replace(/'/g, "''")}'`).join(','); + const pendingQuery = ` + SELECT COUNT(*)::INTEGER as count + FROM "${config.tableName}" + WHERE "vendor_id" = '${companyId}' AND "${config.statusField}" IN (${pendingValuesList}) + `; + + const pendingResult = await db.execute(sql.raw(pendingQuery)); + pendingCount = parseInt(pendingResult.rows[0]?.count || '0'); + console.log("Pending 개수:", pendingCount); + } + + // In Progress 개수 (회사 필터 포함) + if (inProgressValues.length > 0) { + const inProgressValuesList = inProgressValues.map(v => `'${v.replace(/'/g, "''")}'`).join(','); + const inProgressQuery = ` + SELECT COUNT(*)::INTEGER as count + FROM "${config.tableName}" + WHERE "vendor_id" = '${companyId}' AND "${config.statusField}" IN (${inProgressValuesList}) + `; + + const inProgressResult = await db.execute(sql.raw(inProgressQuery)); + inProgressCount = parseInt(inProgressResult.rows[0]?.count || '0'); + console.log("InProgress 개수:", inProgressCount); + } + + // Completed 개수 (회사 필터 포함) + if (completedValues.length > 0) { + const completedValuesList = completedValues.map(v => `'${v.replace(/'/g, "''")}'`).join(','); + const completedQuery = ` + SELECT COUNT(*)::INTEGER as count + FROM "${config.tableName}" + WHERE "vendor_id" = '${companyId}' AND "${config.statusField}" IN (${completedValuesList}) + `; + + const completedResult = await db.execute(sql.raw(completedQuery)); + completedCount = parseInt(completedResult.rows[0]?.count || '0'); + console.log("Completed 개수:", completedCount); + } + + const stats = { + tableName: config.tableName, + displayName: config.displayName, + total: parseInt(totalResult.rows[0]?.total || '0'), + pending: pendingCount, + inProgress: inProgressCount, + completed: completedCount + }; + + console.log(`✅ 파트너 ${config.tableName} 최종 통계:`, stats); + return stats; + } catch (error) { + console.error(`❌ 파트너 테이블 ${config.tableName} 통계 조회 중 오류:`, error); + return createEmptyPartnersStats(config); + } +} + +// Partners 사용자별 테이블 통계 조회 (회사 + 사용자 필터링) +async function getPartnersUserTableStats(config: TableConfig, companyId: string, userId: string): Promise<PartnersDashboardStats> { + try { + // 사용자 필드가 없는 경우 빈 통계 반환 + if (!hasUserFields(config)) { + console.log(`⚠️ 파트너 테이블 ${config.tableName}에 사용자 필드가 없습니다.`); + return createEmptyPartnersStats(config); + } + + console.log(`\n👤 파트너 사용자 ${userId}의 ${config.tableName} 통계 조회 (회사: ${companyId})`); + + // 사용자 조건 생성 (회사 필터 포함) + const userConditions = []; + if (config.userFields.creator) { + userConditions.push(`"${config.userFields.creator}" = '${userId}'`); + } + if (config.userFields.updater) { + userConditions.push(`"${config.userFields.updater}" = '${userId}'`); + } + if (config.userFields.assignee) { + userConditions.push(`"${config.userFields.assignee}" = '${userId}'`); + } + + if (userConditions.length === 0) { + return createEmptyPartnersStats(config); + } + + const userConditionStr = userConditions.join(' OR '); + + // 1. 사용자 + 회사 총 개수 + const userTotalQuery = ` + SELECT COUNT(*)::INTEGER as total + FROM "${config.tableName}" + WHERE "vendor_id" = '${companyId}' AND (${userConditionStr}) + `; + console.log("User Total SQL:", userTotalQuery); + + const userTotalResult = await db.execute(sql.raw(userTotalQuery)); + console.log("User Total 결과:", userTotalResult.rows[0]); + + // 2. 사용자 + 회사 상태별 개수 + const pendingValues = Object.entries(config.statusMapping) + .filter(([_, mapped]) => mapped === 'pending') + .map(([original]) => original); + + const inProgressValues = Object.entries(config.statusMapping) + .filter(([_, mapped]) => mapped === 'in_progress') + .map(([original]) => original); + + const completedValues = Object.entries(config.statusMapping) + .filter(([_, mapped]) => mapped === 'completed') + .map(([original]) => original); + + let userPendingCount = 0; + let userInProgressCount = 0; + let userCompletedCount = 0; + + // User + Company Pending 개수 + if (pendingValues.length > 0) { + const pendingValuesList = pendingValues.map(v => `'${v.replace(/'/g, "''")}'`).join(','); + const userPendingQuery = ` + SELECT COUNT(*)::INTEGER as count + FROM "${config.tableName}" + WHERE "vendor_id" = '${companyId}' AND (${userConditionStr}) AND "${config.statusField}" IN (${pendingValuesList}) + `; + + const userPendingResult = await db.execute(sql.raw(userPendingQuery)); + userPendingCount = parseInt(userPendingResult.rows[0]?.count || '0'); + console.log("User Pending 개수:", userPendingCount); + } + + // User + Company In Progress 개수 + if (inProgressValues.length > 0) { + const inProgressValuesList = inProgressValues.map(v => `'${v.replace(/'/g, "''")}'`).join(','); + const userInProgressQuery = ` + SELECT COUNT(*)::INTEGER as count + FROM "${config.tableName}" + WHERE "vendor_id" = '${companyId}' AND (${userConditionStr}) AND "${config.statusField}" IN (${inProgressValuesList}) + `; + + const userInProgressResult = await db.execute(sql.raw(userInProgressQuery)); + userInProgressCount = parseInt(userInProgressResult.rows[0]?.count || '0'); + console.log("User InProgress 개수:", userInProgressCount); + } + + // User + Company Completed 개수 + if (completedValues.length > 0) { + const completedValuesList = completedValues.map(v => `'${v.replace(/'/g, "''")}'`).join(','); + const userCompletedQuery = ` + SELECT COUNT(*)::INTEGER as count + FROM "${config.tableName}" + WHERE "vendor_id" = '${companyId}' AND (${userConditionStr}) AND "${config.statusField}" IN (${completedValuesList}) + `; + + const userCompletedResult = await db.execute(sql.raw(userCompletedQuery)); + userCompletedCount = parseInt(userCompletedResult.rows[0]?.count || '0'); + console.log("User Completed 개수:", userCompletedCount); + } + + const stats = { + tableName: config.tableName, + displayName: config.displayName, + total: parseInt(userTotalResult.rows[0]?.total || '0'), + pending: userPendingCount, + inProgress: userInProgressCount, + completed: userCompletedCount + }; + + console.log(`✅ 파트너 사용자 ${config.tableName} 최종 통계:`, stats); + return stats; + } catch (error) { + console.error(`❌ 파트너 테이블 ${config.tableName} 사용자 통계 조회 중 오류:`, error); + return createEmptyPartnersStats(config); + } +} + +// 유틸리티 함수들 +function createEmptyPartnersStats(config: TableConfig): PartnersDashboardStats { + return { + tableName: config.tableName, + displayName: config.displayName, + total: 0, + pending: 0, + inProgress: 0, + completed: 0 + }; +} + +function hasUserFields(config: TableConfig): boolean { + return !!(config.userFields.creator || config.userFields.updater || config.userFields.assignee); +} + +// 디버깅 함수: Partners 전용 +export async function simplePartnersTest(tableName: string, statusField: string, companyId: string) { + try { + console.log(`\n🧪 파트너 ${tableName} 간단한 테스트 (회사: ${companyId}):`); + + // 1. 회사별 총 개수 + const totalQuery = `SELECT COUNT(*) as total FROM "${tableName}" WHERE "vendor_id" = '${companyId}'`; + const totalResult = await db.execute(sql.raw(totalQuery)); + console.log("회사별 총 개수:", totalResult.rows[0]); + + // 2. 회사별 상태 분포 + const statusQuery = ` + SELECT "${statusField}" as status, COUNT(*) as count + FROM "${tableName}" + WHERE "vendor_id" = '${companyId}' + GROUP BY "${statusField}" + ORDER BY count DESC + `; + const statusResult = await db.execute(sql.raw(statusQuery)); + console.log("회사별 상태 분포:", statusResult.rows); + + return { + total: totalResult.rows[0], + statusDistribution: statusResult.rows + }; + } catch (error) { + console.error("파트너 간단한 테스트 실패:", error); + return null; + } +}
\ No newline at end of file |