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

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