(대표님/최겸) 20250702 변경사항 업데이트

1 files changed, 454 insertions, 0 deletions

diff --git a/lib/dashboard/service.ts b/lib/dashboard/service.ts
new file mode 100644
index 00000000..16b05d45
--- /dev/null
+++ b/lib/dashboard/service.ts
@@ -0,0 +1,454 @@
+"use server";
+
+import db from "@/db/db";
+import { sql, eq, or, and, count, sum, inArray } from "drizzle-orm";
+import { getServerSession } from "next-auth/next";
+import { authOptions } from "@/app/api/auth/[...nextauth]/route";
+import { getTablesByDomain } from "@/config/dashboard-table";
+import { TableConfig } from "@/types/dashboard";
+
+export interface DashboardStats {
+  tableName: string;
+  displayName: string;
+  total: number;
+  pending: number;
+  inProgress: number;
+  completed: number;
+}
+
+export interface UserDashboardStats extends DashboardStats {
+  myTotal: number;
+  myPending: number;
+  myInProgress: number;
+  myCompleted: number;
+}
+
+export interface DashboardData {
+  domain: string;
+  teamStats: DashboardStats[];
+  userStats: UserDashboardStats[];
+  summary: {
+    totalTasks: number;
+    myTasks: number;
+    teamPending: number;
+    teamInProgress: number;
+    teamCompleted: number;
+    myPending: number;
+    myInProgress: number;
+    myCompleted: number;
+  };
+}
+
+// 팀 대시보드 데이터 조회
+export async function getTeamDashboardData(domain: string): Promise<DashboardStats[]> {
+  try {
+    const tables = getTablesByDomain(domain);
+    
+    if (tables.length === 0) {
+      console.warn(`도메인 '${domain}'에 대한 테이블이 없습니다.`);
+      return [];
+    }
+
+    // 병렬 처리로 성능 향상
+    const results = await Promise.allSettled(
+      tables.map(tableConfig => getTableStats(tableConfig))
+    );
+
+    // 성공한 결과만 반환, 실패한 것들은 로그 출력
+    const successfulResults: DashboardStats[] = [];
+    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("팀 대시보드 데이터를 불러오는데 실패했습니다.");
+  }
+}
+
+// 사용자 대시보드 데이터 조회
+export async function getUserDashboardData(domain: string): Promise<UserDashboardStats[]> {
+  try {
+    // 현재 사용자 정보 가져오기
+    const session = await getServerSession(authOptions);
+    if (!session?.user?.id) {
+      throw new Error("인증되지 않은 사용자입니다.");
+    }
+
+    const userId = session.user.id;
+    const tables = getTablesByDomain(domain);
+    
+    if (tables.length === 0) {
+      console.warn(`도메인 '${domain}'에 대한 테이블이 없습니다.`);
+      return [];
+    }
+
+    console.log(`👤 사용자 ID: ${userId}`);
+
+    // 병렬 처리로 성능 향상
+    const results = await Promise.allSettled(
+      tables.map(async (tableConfig) => {
+        const [teamStats, userStats] = await Promise.all([
+          getTableStats(tableConfig),
+          getUserTableStats(tableConfig, userId)
+        ]);
+
+        return {
+          ...teamStats,
+          myTotal: userStats.total,
+          myPending: userStats.pending,
+          myInProgress: userStats.inProgress,
+          myCompleted: userStats.completed
+        } as UserDashboardStats;
+      })
+    );
+
+    // 성공한 결과만 반환
+    const successfulResults: UserDashboardStats[] = [];
+    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("사용자 대시보드 데이터를 불러오는데 실패했습니다.");
+  }
+}
+
+// 전체 대시보드 데이터 조회 (팀 + 개인)
+export async function getDashboardData(domain: string): Promise<DashboardData> {
+  try {
+    const session = await getServerSession(authOptions);
+    if (!session?.user?.id) {
+      throw new Error("인증되지 않은 사용자입니다.");
+    }
+
+    // 병렬 처리로 성능 향상
+    const [teamStats, userStats] = await Promise.all([
+      getTeamDashboardData(domain),
+      getUserDashboardData(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,
+      teamStats,
+      userStats,
+      summary
+    };
+  } catch (error) {
+    console.error("대시보드 데이터 조회 실패:", error);
+    throw new Error("대시보드 데이터를 불러오는데 실패했습니다.");
+  }
+}
+
+// 테이블별 전체 통계 조회 (완전히 수정된 버전)
+async function getTableStats(config: TableConfig): Promise<DashboardStats> {
+  try {
+    console.log(`\n🔍 테이블 ${config.tableName} 통계 조회 시작`);
+    
+    // 1단계: 기본 총 개수 확인
+    console.log("1단계: 총 개수 조회");
+    const totalQuery = `SELECT COUNT(*)::INTEGER as total FROM "${config.tableName}"`;
+    console.log("Total SQL:", totalQuery);
+    
+    const totalResult = await db.execute(sql.raw(totalQuery));
+    console.log("Total 결과:", totalResult.rows[0]);
+    
+    // 2단계: 실제 상태값 확인
+    console.log("2단계: 상태값 분포 확인");
+    const statusQuery = `
+      SELECT "${config.statusField}" as status, COUNT(*) as count
+      FROM "${config.tableName}"
+      WHERE "${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단계: 상태별 개수 조회 (개별 쿼리)
+    console.log("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 "${config.statusField}" IN (${pendingValuesList})
+      `;
+      console.log("Pending SQL:", pendingQuery);
+      
+      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 "${config.statusField}" IN (${inProgressValuesList})
+      `;
+      console.log("InProgress SQL:", inProgressQuery);
+      
+      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 "${config.statusField}" IN (${completedValuesList})
+      `;
+      console.log("Completed SQL:", completedQuery);
+      
+      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 createEmptyStats(config);
+  }
+}
+
+// 사용자별 테이블 통계 조회 (수정된 버전)
+async function getUserTableStats(config: TableConfig, userId: string): Promise<DashboardStats> {
+  try {
+    // 사용자 필드가 없는 경우 빈 통계 반환
+    if (!hasUserFields(config)) {
+      console.log(`⚠️ 테이블 ${config.tableName}에 사용자 필드가 없습니다.`);
+      return createEmptyStats(config);
+    }
+
+    console.log(`\n👤 사용자 ${userId}의 ${config.tableName} 통계 조회`);
+
+    // 사용자 조건 생성
+    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 createEmptyStats(config);
+    }
+    
+    const userConditionStr = userConditions.join(' OR ');
+    
+    // 1. 사용자 총 개수
+    const userTotalQuery = `
+      SELECT COUNT(*)::INTEGER as total 
+      FROM "${config.tableName}"
+      WHERE ${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 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 (${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 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 (${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 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 (${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 createEmptyStats(config);
+  }
+}
+
+// 유틸리티 함수들
+function createEmptyStats(config: TableConfig): DashboardStats {
+  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);
+}
+
+// 디버깅 함수: 단순한 테스트
+export async function simpleTest(tableName: string, statusField: string) {
+  try {
+    console.log(`\n🧪 ${tableName} 간단한 테스트:`);
+    
+    // 1. 총 개수
+    const totalQuery = `SELECT COUNT(*) as total FROM "${tableName}"`;
+    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}"
+      GROUP BY "${statusField}"
+      ORDER BY count DESC
+    `;
+    const statusResult = await db.execute(sql.raw(statusQuery));
+    console.log("상태 분포:", statusResult.rows);
+    
+    // 3. 특정 상태 테스트
+    const draftQuery = `SELECT COUNT(*) as count FROM "${tableName}" WHERE "${statusField}" = 'DRAFT'`;
+    const draftResult = await db.execute(sql.raw(draftQuery));
+    console.log("DRAFT 개수:", draftResult.rows[0]);
+    
+    const docConfirmedQuery = `SELECT COUNT(*) as count FROM "${tableName}" WHERE "${statusField}" = 'Doc. Confirmed'`;
+    const docConfirmedResult = await db.execute(sql.raw(docConfirmedQuery));
+    console.log("Doc. Confirmed 개수:", docConfirmedResult.rows[0]);
+    
+    return {
+      total: totalResult.rows[0],
+      statusDistribution: statusResult.rows,
+      draft: draftResult.rows[0],
+      docConfirmed: docConfirmedResult.rows[0]
+    };
+  } catch (error) {
+    console.error("간단한 테스트 실패:", error);
+    return null;
+  }
+}
\ No newline at end of file