1 files changed, 753 insertions, 0 deletions
diff --git a/lib/pcr/service.ts b/lib/pcr/service.ts
new file mode 100644
index 00000000..8a85a9b1
--- /dev/null
+++ b/lib/pcr/service.ts
@@ -0,0 +1,753 @@
+"use server"
+
+import { unstable_noStore, revalidatePath } from "next/cache";
+import db from "@/db/db";
+import {
+  pcrPo,
+  pcrPr,
+  vendors,
+  pcrPrAttachment
+} from "@/db/schema";
+import { and, desc, eq, ilike, or, sql, inArray, count, asc } from "drizzle-orm";
+import { getErrorMessage } from "@/lib/handle-error";
+import {
+  type PcrPoFilters,
+} from "./types";
+import { getServerSession } from "next-auth/next";
+import { authOptions } from "@/app/api/auth/[...nextauth]/route";
+
+// 정렬 타입 정의
+// 의도적으로 any 사용 - drizzle ORM의 orderBy 타입이 복잡함
+// eslint-disable-next-line @typescript-eslint/no-explicit-any
+type OrderByType = any;
+
+/**
+ * PCR_PO 데이터를 조회하는 기본 함수 (JOIN 포함)
+ */
+function selectPcrPoWithJoin() {
+  return db
+    .select({
+      // PCR_PO 필드들
+      id: pcrPo.id,
+      pcrApprovalStatus: pcrPo.pcrApprovalStatus,
+      changeType: pcrPo.changeType,
+      details: pcrPo.details,
+      project: pcrPo.project,
+      pcrRequestDate: pcrPo.pcrRequestDate,
+      poContractNumber: pcrPo.poContractNumber,
+      revItemNumber: pcrPo.revItemNumber,
+      purchaseContractManager: pcrPo.purchaseContractManager,
+      pcrCreator: pcrPo.pcrCreator,
+      poContractAmountBefore: pcrPo.poContractAmountBefore,
+      poContractAmountAfter: pcrPo.poContractAmountAfter,
+      contractCurrency: pcrPo.contractCurrency,
+      pcrReason: pcrPo.pcrReason,
+      detailsReason: pcrPo.detailsReason,
+      rejectionReason: pcrPo.rejectionReason,
+      pcrResponseDate: pcrPo.pcrResponseDate,
+      vendorId: pcrPo.vendorId,
+      createdBy: pcrPo.createdBy,
+      updatedBy: pcrPo.updatedBy,
+      createdAt: pcrPo.createdAt,
+      updatedAt: pcrPo.updatedAt,
+
+      // JOIN된 필드들
+      vendorName: vendors.vendorName,
+    })
+    .from(pcrPo)
+    .leftJoin(vendors, eq(pcrPo.vendorId, vendors.id))
+    .$dynamic();
+}
+
+/**
+ * PCR_PR 데이터를 조회하는 기본 함수
+ */
+function selectPcrPrWithJoin() {
+  return db
+    .select({
+      // PCR_PR 필드들
+      id: pcrPr.id,
+      materialNumber: pcrPr.materialNumber,
+      materialDetails: pcrPr.materialDetails,
+      quantityBefore: pcrPr.quantityBefore,
+      quantityAfter: pcrPr.quantityAfter,
+      weightBefore: pcrPr.weightBefore,
+      weightAfter: pcrPr.weightAfter,
+      subcontractorWeightBefore: pcrPr.subcontractorWeightBefore,
+      subcontractorWeightAfter: pcrPr.subcontractorWeightAfter,
+      supplierWeightBefore: pcrPr.supplierWeightBefore,
+      supplierWeightAfter: pcrPr.supplierWeightAfter,
+      specDrawingBefore: pcrPr.specDrawingBefore,
+      specDrawingAfter: pcrPr.specDrawingAfter,
+      initialPoContractDate: pcrPr.initialPoContractDate,
+      specChangeDate: pcrPr.specChangeDate,
+      poContractModifiedDate: pcrPr.poContractModifiedDate,
+      confirmationDate: pcrPr.confirmationDate,
+      designManager: pcrPr.designManager,
+      poContractNumber: pcrPr.poContractNumber,
+      createdBy: pcrPr.createdBy,
+      updatedBy: pcrPr.updatedBy,
+      createdAt: pcrPr.createdAt,
+      updatedAt: pcrPr.updatedAt,
+    })
+    .from(pcrPr)
+    .$dynamic();
+}
+
+/**
+ * PCR_PO 데이터를 조회하고 카운트하는 함수들
+ */
+const selectPcrPoWithJoinDynamic = selectPcrPoWithJoin();
+const countPcrPoWithJoin = () =>
+  db
+    .select({ count: count() })
+    .from(pcrPo)
+    .leftJoin(vendors, eq(pcrPo.vendorId, vendors.id));
+
+/**
+ * PCR_PR 데이터를 조회하고 카운트하는 함수들
+ */
+const selectPcrPrWithJoinDynamic = selectPcrPrWithJoin();
+
+/**
+ * PCR_PO 목록 조회 (EvcP 페이지용 - 모든 데이터 조회)
+ */
+export async function getPcrPoList(input: {
+  page?: number;
+  perPage?: number;
+  sort?: OrderByType;
+  filters?: PcrPoFilters;
+  search?: string;
+}) {
+  unstable_noStore();
+
+  try {
+    const { page = 1, perPage = 10, sort, filters, search } = input;
+
+    const offset = (page - 1) * perPage;
+
+    // 필터링 조건 구성
+    const whereConditions: any[] = [];
+
+    if (filters) {
+      if (filters.pcrApprovalStatus) {
+        whereConditions.push(eq(pcrPo.pcrApprovalStatus, filters.pcrApprovalStatus));
+      }
+      if (filters.changeType) {
+        whereConditions.push(eq(pcrPo.changeType, filters.changeType));
+      }
+      if (filters.project) {
+        whereConditions.push(ilike(pcrPo.project, `%${filters.project}%`));
+      }
+      if (filters.poContractNumber) {
+        whereConditions.push(ilike(pcrPo.poContractNumber, `%${filters.poContractNumber}%`));
+      }
+      if (filters.vendorId) {
+        whereConditions.push(eq(pcrPo.vendorId, filters.vendorId));
+      }
+      if (filters.startDate) {
+        whereConditions.push(sql`${pcrPo.pcrRequestDate} >= ${filters.startDate}`);
+      }
+      if (filters.endDate) {
+        whereConditions.push(sql`${pcrPo.pcrRequestDate} <= ${filters.endDate}`);
+      }
+    }
+
+    // 검색 조건
+    if (search) {
+      whereConditions.push(
+        or(
+          ilike(pcrPo.poContractNumber, `%${search}%`),
+          ilike(pcrPo.project, `%${search}%`),
+          ilike(pcrPo.pcrCreator, `%${search}%`),
+          ilike(vendors.vendorName, `%${search}%`)
+        )
+      );
+    }
+
+    const whereClause = whereConditions.length > 0 ? and(...whereConditions) : undefined;
+
+    // 데이터 조회
+    const data = await selectPcrPoWithJoinDynamic
+      .where(whereClause)
+      .orderBy(sort ? sort : desc(pcrPo.createdAt))
+      .limit(perPage)
+      .offset(offset);
+
+    // 전체 카운트
+    const totalCount = await countPcrPoWithJoin()
+      .where(whereClause)
+      .then((result) => result[0]?.count ?? 0);
+
+    return {
+      data,
+      totalCount,
+      pageCount: Math.ceil(totalCount / perPage),
+    };
+  } catch (error) {
+    console.error("PCR_PO 목록 조회 오류:", error);
+    throw new Error("PCR_PO 데이터를 조회하는데 실패했습니다.");
+  }
+}
+
+/**
+ * PCR_PO 목록 조회 (Partners 페이지용 - 현재 사용자의 vendorId에 해당하는 데이터만 조회)
+ */
+export async function getPcrPoListForPartners(input: {
+  page?: number;
+  perPage?: number;
+  sort?: OrderByType;
+  filters?: Omit<PcrPoFilters, 'vendorId'>;
+  search?: string;
+  vendorId: number;
+}) {
+  unstable_noStore();
+
+  try {
+    const { page = 1, perPage = 10, sort, filters, search, vendorId } = input;
+
+    const offset = (page - 1) * perPage;
+
+    // 필터링 조건 구성 (vendorId는 필수로 포함)
+    const whereConditions = [eq(pcrPo.vendorId, vendorId)];
+
+    if (filters) {
+      if (filters.pcrApprovalStatus) {
+        whereConditions.push(eq(pcrPo.pcrApprovalStatus, filters.pcrApprovalStatus));
+      }
+      if (filters.changeType) {
+        whereConditions.push(eq(pcrPo.changeType, filters.changeType));
+      }
+      if (filters.project) {
+        whereConditions.push(ilike(pcrPo.project, `%${filters.project}%`));
+      }
+      if (filters.poContractNumber) {
+        whereConditions.push(ilike(pcrPo.poContractNumber, `%${filters.poContractNumber}%`));
+      }
+      if (filters.startDate) {
+        whereConditions.push(sql`${pcrPo.pcrRequestDate} >= ${filters.startDate}`);
+      }
+      if (filters.endDate) {
+        whereConditions.push(sql`${pcrPo.pcrRequestDate} <= ${filters.endDate}`);
+      }
+    }
+
+    // 검색 조건
+    if (search) {
+      whereConditions.push(
+        or(
+          ilike(pcrPo.poContractNumber, `%${search}%`),
+          ilike(pcrPo.project, `%${search}%`),
+          ilike(pcrPo.pcrCreator, `%${search}%`)
+        )
+      );
+    }
+
+    const whereClause = and(...whereConditions);
+
+    // 데이터 조회
+    const data = await selectPcrPoWithJoinDynamic
+      .where(whereClause)
+      .orderBy(sort ? sort : desc(pcrPo.createdAt))
+      .limit(perPage)
+      .offset(offset);
+
+    // 전체 카운트
+    const totalCount = await countPcrPoWithJoin()
+      .where(whereClause)
+      .then((result) => result[0]?.count ?? 0);
+
+    return {
+      data,
+      totalCount,
+      pageCount: Math.ceil(totalCount / perPage),
+    };
+  } catch (error) {
+    console.error("PCR_PO Partners 목록 조회 오류:", error);
+    throw new Error("PCR_PO 데이터를 조회하는데 실패했습니다.");
+  }
+}
+
+/**
+ * 특정 PO/계약 번호에 해당하는 PCR_PR 데이터 조회
+ */
+export async function getPcrPrListByPoContractNumber(poContractNumber: string) {
+  unstable_noStore();
+
+  try {
+    // PCR_PR 데이터 조회
+    const pcrPrData = await selectPcrPrWithJoinDynamic
+      .where(eq(pcrPr.poContractNumber, poContractNumber))
+      .orderBy(asc(pcrPr.materialNumber));
+
+    // 첨부파일 데이터 조회 (해당 PCR_PR들의 ID로)
+    const pcrPrIds = pcrPrData.map(item => item.id);
+    let attachments: any[] = [];
+
+    if (pcrPrIds.length > 0) {
+      const attachmentsResult = await db
+        .select()
+        .from(pcrPrAttachment)
+        .where(inArray(pcrPrAttachment.pcrPrId, pcrPrIds))
+        .orderBy(pcrPrAttachment.pcrPrId, pcrPrAttachment.type);
+
+      attachments = attachmentsResult;
+    }
+
+    // PCR_PR 데이터에 첨부파일 추가
+    const dataWithAttachments = pcrPrData.map(pcrPrItem => ({
+      ...pcrPrItem,
+      attachments: attachments.filter(att => att.pcrPrId === pcrPrItem.id)
+    }));
+
+    return dataWithAttachments;
+  } catch (error) {
+    console.error("PCR_PR 조회 오류:", error);
+    throw new Error("PCR_PR 데이터를 조회하는데 실패했습니다.");
+  }
+}
+
+/**
+ * PCR_PO 단일 조회
+ */
+export async function getPcrPoById(id: number) {
+  unstable_noStore();
+
+  try {
+    const data = await selectPcrPoWithJoinDynamic
+      .where(eq(pcrPo.id, id))
+      .limit(1);
+
+    if (data.length === 0) {
+      throw new Error("PCR_PO 데이터를 찾을 수 없습니다.");
+    }
+
+    return data[0];
+  } catch (error) {
+    console.error("PCR_PO 단일 조회 오류:", error);
+    throw new Error("PCR_PO 데이터를 조회하는데 실패했습니다.");
+  }
+}
+
+/**
+ * PCR_PR 단일 조회
+ */
+export async function getPcrPrById(id: number) {
+  unstable_noStore();
+
+  try {
+    const data = await selectPcrPrWithJoinDynamic
+      .where(eq(pcrPr.id, id))
+      .limit(1);
+
+    if (data.length === 0) {
+      throw new Error("PCR_PR 데이터를 찾을 수 없습니다.");
+    }
+
+    return data[0];
+  } catch (error) {
+    console.error("PCR_PR 단일 조회 오류:", error);
+    throw new Error("PCR_PR 데이터를 조회하는데 실패했습니다.");
+  }
+}
+
+/**
+ * PCR_PO 생성
+ */
+export async function createPcrPo(data: {
+  pcrApprovalStatus?: string;
+  changeType?: string;
+  details?: string;
+  project?: string;
+  pcrRequestDate: Date;
+  poContractNumber: string;
+  revItemNumber?: string;
+  purchaseContractManager?: string;
+  pcrCreator?: string;
+  poContractAmountBefore?: number;
+  poContractAmountAfter?: number;
+  contractCurrency?: string;
+  pcrReason?: string;
+  detailsReason?: string;
+  rejectionReason?: string;
+  pcrResponseDate?: Date;
+  vendorId?: number;
+}) {
+  try {
+    const session = await getServerSession(authOptions);
+    if (!session?.user?.id) {
+      throw new Error("로그인이 필요합니다.");
+    }
+
+    const now = new Date();
+    const insertData = {
+      ...data,
+      pcrApprovalStatus: data.pcrApprovalStatus || 'PENDING',
+      changeType: data.changeType || 'OTHER',
+      contractCurrency: data.contractCurrency || 'KRW',
+      createdBy: session.user.id,
+      updatedBy: session.user.id,
+      createdAt: now,
+      updatedAt: now,
+    };
+
+    const result = await db.insert(pcrPo).values(insertData).returning();
+
+    // 캐시 무효화
+    revalidatePath("/evcp/pcr");
+    revalidatePath("/partners/pcr");
+
+    return {
+      success: true,
+      data: result[0],
+      message: "PCR_PO가 성공적으로 생성되었습니다."
+    };
+  } catch (error) {
+    console.error("PCR_PO 생성 오류:", error);
+    return {
+      success: false,
+      error: getErrorMessage(error),
+    };
+  }
+}
+
+/**
+ * PCR_PR 생성
+ */
+export async function createPcrPr(data: {
+  materialNumber: string;
+  materialDetails?: string;
+  quantityBefore?: number;
+  quantityAfter?: number;
+  weightBefore?: number;
+  weightAfter?: number;
+  subcontractorWeightBefore?: number;
+  subcontractorWeightAfter?: number;
+  supplierWeightBefore?: number;
+  supplierWeightAfter?: number;
+  specDrawingBefore?: string;
+  specDrawingAfter?: string;
+  initialPoContractDate?: Date;
+  specChangeDate?: Date;
+  poContractModifiedDate?: Date;
+  confirmationDate?: Date;
+  designManager?: string;
+  poContractNumber: string;
+}) {
+  try {
+    const session = await getServerSession(authOptions);
+    if (!session?.user?.id) {
+      throw new Error("로그인이 필요합니다.");
+    }
+
+    const now = new Date();
+    const insertData = {
+      ...data,
+      createdBy: session.user.id,
+      updatedBy: session.user.id,
+      createdAt: now,
+      updatedAt: now,
+    };
+
+    const result = await db.insert(pcrPr).values(insertData).returning();
+
+    // 캐시 무효화
+    revalidatePath("/evcp/pcr");
+    revalidatePath("/partners/pcr");
+
+    return {
+      success: true,
+      data: result[0],
+      message: "PCR_PR이 성공적으로 생성되었습니다."
+    };
+  } catch (error) {
+    console.error("PCR_PR 생성 오류:", error);
+    return {
+      success: false,
+      error: getErrorMessage(error),
+    };
+  }
+}
+
+/**
+ * PCR_PO 업데이트
+ */
+export async function updatePcrPo(id: number, data: Partial<{
+  pcrApprovalStatus: string;
+  changeType: string;
+  details: string;
+  project: string;
+  pcrRequestDate: Date;
+  poContractNumber: string;
+  revItemNumber: string;
+  purchaseContractManager: string;
+  pcrCreator: string;
+  poContractAmountBefore: number;
+  poContractAmountAfter: number;
+  contractCurrency: string;
+  pcrReason: string;
+  detailsReason: string;
+  rejectionReason: string;
+  pcrResponseDate: Date;
+  vendorId: number;
+}>) {
+  try {
+    const session = await getServerSession(authOptions);
+    if (!session?.user?.id) {
+      throw new Error("로그인이 필요합니다.");
+    }
+
+    const updateData = {
+      ...data,
+      updatedBy: session.user.id,
+      updatedAt: new Date(),
+    };
+
+    const result = await db
+      .update(pcrPo)
+      .set(updateData)
+      .where(eq(pcrPo.id, id))
+      .returning();
+
+    if (result.length === 0) {
+      return {
+        success: false,
+        error: "PCR_PO 데이터를 찾을 수 없습니다.",
+      };
+    }
+
+    // 캐시 무효화
+    revalidatePath("/evcp/pcr");
+    revalidatePath("/partners/pcr");
+
+    return {
+      success: true,
+      data: result[0],
+      message: "PCR_PO가 성공적으로 업데이트되었습니다."
+    };
+  } catch (error) {
+    console.error("PCR_PO 업데이트 오류:", error);
+    return {
+      success: false,
+      error: getErrorMessage(error),
+    };
+  }
+}
+
+/**
+ * PCR_PR 업데이트
+ */
+export async function updatePcrPr(id: number, data: Partial<{
+  materialNumber: string;
+  materialDetails: string;
+  quantityBefore: number;
+  quantityAfter: number;
+  weightBefore: number;
+  weightAfter: number;
+  subcontractorWeightBefore: number;
+  subcontractorWeightAfter: number;
+  supplierWeightBefore: number;
+  supplierWeightAfter: number;
+  specDrawingBefore: string;
+  specDrawingAfter: string;
+  initialPoContractDate: Date;
+  specChangeDate: Date;
+  poContractModifiedDate: Date;
+  confirmationDate: Date;
+  designManager: string;
+  poContractNumber: string;
+}>) {
+  try {
+    const session = await getServerSession(authOptions);
+    if (!session?.user?.id) {
+      throw new Error("로그인이 필요합니다.");
+    }
+
+    const updateData = {
+      ...data,
+      updatedBy: session.user.id,
+      updatedAt: new Date(),
+    };
+
+    const result = await db
+      .update(pcrPr)
+      .set(updateData)
+      .where(eq(pcrPr.id, id))
+      .returning();
+
+    if (result.length === 0) {
+      return {
+        success: false,
+        error: "PCR_PR 데이터를 찾을 수 없습니다.",
+      };
+    }
+
+    // 캐시 무효화
+    revalidatePath("/evcp/pcr");
+    revalidatePath("/partners/pcr");
+
+    return {
+      success: true,
+      data: result[0],
+      message: "PCR_PR이 성공적으로 업데이트되었습니다."
+    };
+  } catch (error) {
+    console.error("PCR_PR 업데이트 오류:", error);
+    return {
+      success: false,
+      error: getErrorMessage(error),
+    };
+  }
+}
+
+/**
+ * PCR_PO 삭제
+ */
+export async function deletePcrPo(id: number) {
+  try {
+    const session = await getServerSession(authOptions);
+    if (!session?.user?.id) {
+      throw new Error("로그인이 필요합니다.");
+    }
+
+    const result = await db
+      .delete(pcrPo)
+      .where(eq(pcrPo.id, id))
+      .returning();
+
+    if (result.length === 0) {
+      return {
+        success: false,
+        error: "PCR_PO 데이터를 찾을 수 없습니다.",
+      };
+    }
+
+    // 캐시 무효화
+    revalidatePath("/evcp/pcr");
+    revalidatePath("/partners/pcr");
+
+    return {
+      success: true,
+      message: "PCR_PO가 성공적으로 삭제되었습니다."
+    };
+  } catch (error) {
+    console.error("PCR_PO 삭제 오류:", error);
+    return {
+      success: false,
+      error: getErrorMessage(error),
+    };
+  }
+}
+
+/**
+ * PCR_PR 삭제
+ */
+export async function deletePcrPr(id: number) {
+  try {
+    const session = await getServerSession(authOptions);
+    if (!session?.user?.id) {
+      throw new Error("로그인이 필요합니다.");
+    }
+
+    const result = await db
+      .delete(pcrPr)
+      .where(eq(pcrPr.id, id))
+      .returning();
+
+    if (result.length === 0) {
+      return {
+        success: false,
+        error: "PCR_PR 데이터를 찾을 수 없습니다.",
+      };
+    }
+
+    // 캐시 무효화
+    revalidatePath("/evcp/pcr");
+    revalidatePath("/partners/pcr");
+
+    return {
+      success: true,
+      message: "PCR_PR이 성공적으로 삭제되었습니다."
+    };
+  } catch (error) {
+    console.error("PCR_PR 삭제 오류:", error);
+    return {
+      success: false,
+      error: getErrorMessage(error),
+    };
+  }
+}
+
+/**
+ * 간단한 협력업체 목록 조회 (PCR 생성용)
+ */
+export async function getVendorsForPcr(input?: {
+  vendorId?: number; // 특정 vendorId만 조회 (Partners 페이지용)
+  limit?: number; // 조회 개수 제한
+}) {
+  unstable_noStore();
+
+  try {
+    const { vendorId, limit = 100 } = input || {};
+
+    let query = db
+      .select({
+        id: vendors.id,
+        vendorName: vendors.vendorName,
+        vendorCode: vendors.vendorCode,
+      })
+      .from(vendors)
+      .orderBy(asc(vendors.vendorName));
+
+    // 특정 vendorId 필터링 (Partners 페이지용)
+    if (vendorId) {
+      query = query.where(eq(vendors.id, vendorId));
+    }
+
+    // 개수 제한
+    if (limit) {
+      query = query.limit(limit);
+    }
+
+    const result = await query;
+
+    return {
+      success: true,
+      data: result,
+    };
+  } catch (error) {
+    console.error("협력업체 조회 오류:", error);
+    return {
+      success: false,
+      error: getErrorMessage(error),
+      data: [],
+    };
+  }
+}
+
+/**
+ * PCR_PR 첨부파일 조회
+ */
+export async function getPcrPrAttachments(pcrPrId: number) {
+  unstable_noStore();
+
+  try {
+    const result = await db
+      .select()
+      .from(pcrPrAttachment)
+      .where(eq(pcrPrAttachment.pcrPrId, pcrPrId))
+      .orderBy(pcrPrAttachment.type, pcrPrAttachment.createdAt);
+
+    return {
+      success: true,
+      data: result,
+    };
+  } catch (error) {
+    console.error("PCR_PR 첨부파일 조회 오류:", error);
+    return {
+      success: false,
+      error: getErrorMessage(error),
+      data: [],
+    };
+  }
+}
+\ No newline at end of file