diff options
Diffstat (limited to 'lib/itb/service.ts')
| -rw-r--r-- | lib/itb/service.ts | 741 |
1 files changed, 741 insertions, 0 deletions
diff --git a/lib/itb/service.ts b/lib/itb/service.ts new file mode 100644 index 00000000..66732c02 --- /dev/null +++ b/lib/itb/service.ts @@ -0,0 +1,741 @@ +// app/actions/purchase-requests.ts +"use server"; + +import db from "@/db/db"; +import { purchaseRequestsView, purchaseRequests, purchaseRequestAttachments, rfqsLast, rfqLastAttachments, rfqLastAttachmentRevisions,users } from "@/db/schema"; +import { eq, and, desc, ilike, or, sql, asc, inArray ,like} from "drizzle-orm"; +import { revalidatePath, revalidateTag } from "next/cache"; +import { getServerSession } from 'next-auth/next' +import { authOptions } from '@/app/api/auth/[...nextauth]/route' +import { filterColumns } from "@/lib/filter-columns"; +import { unstable_cache } from "@/lib/unstable-cache"; +import { GetPurchaseRequestsSchema } from "./validations"; +import { z } from "zod" + +const createRequestSchema = z.object({ + requestTitle: z.string().min(1), + requestDescription: z.string().optional(), + projectId: z.number().optional(), + projectCode: z.string().optional(), + projectName: z.string().optional(), + projectCompany: z.string().optional(), + projectSite: z.string().optional(), + classNo: z.string().optional(), + packageNo: z.string().optional(), + packageName: z.string().optional(), + majorItemMaterialCategory: z.string().optional(), + majorItemMaterialDescription: z.string().optional(), + smCode: z.string().optional(), + estimatedBudget: z.string().optional(), + requestedDeliveryDate: z.date().optional(), + items: z.array(z.object({ + id: z.string(), + itemCode: z.string(), + itemName: z.string(), + specification: z.string(), + quantity: z.number(), + unit: z.string(), + estimatedUnitPrice: z.number().optional(), + remarks: z.string().optional(), + })).optional(), + attachments: z.array(z.object({ + fileName: z.string(), + originalFileName: z.string(), + filePath: z.string(), + fileSize: z.number(), + fileType: z.string(), + })).optional(), +}) + +// 구매요청 생성 +export async function createPurchaseRequest(input: z.infer<typeof createRequestSchema>) { + try { + const session = await getServerSession(authOptions) + if (!session?.user?.id) { + return { error: "인증되지 않은 사용자입니다." } + } + + const userId = Number(session.user.id) + const data = createRequestSchema.parse(input) + + const result = await db.transaction(async (tx) => { + // 요청 번호 생성 + const year = new Date().getFullYear() + const lastRequest = await tx + .select() + .from(purchaseRequests) + .where(ilike(purchaseRequests.requestCode, `PR-${year}-%`)) + .orderBy(desc(purchaseRequests.requestCode)) + .limit(1) + + const nextNumber = lastRequest.length > 0 + ? parseInt(lastRequest[0].requestCode.split('-')[2]) + 1 + : 1 + + const requestCode = `PR-${year}-${String(nextNumber).padStart(5, '0')}` + + // 요청 생성 + const [request] = await tx + .insert(purchaseRequests) + .values({ + requestCode, + projectId: data.projectId, + projectCode: data.projectCode, + projectName: data.projectName, + projectCompany: data.projectCompany, + projectSite: data.projectSite, + classNo: data.classNo, + packageNo: data.packageNo, + packageName: data.packageName, + majorItemMaterialCategory: data.majorItemMaterialCategory, + majorItemMaterialDescription: data.majorItemMaterialDescription, + smCode: data.smCode, + requestTitle: data.requestTitle, + requestDescription: data.requestDescription, + estimatedBudget: data.estimatedBudget, + requestedDeliveryDate: data.requestedDeliveryDate, + items: data.items || [], + engPicId: userId, + engPicName: session.user.name, + status: "작성중", + createdBy: userId, + updatedBy: userId, + }) + .returning() + + // 첨부파일 저장 + if (data.attachments && data.attachments.length > 0) { + await tx.insert(purchaseRequestAttachments).values( + data.attachments.map(file => ({ + requestId: request.id, + ...file, + category: "설계문서", + createdBy: userId, + })) + ) + } + + return request + }) + + revalidateTag("purchase-requests") + + return { + success: true, + data: result, + message: `구매요청 ${result.requestCode}이(가) 생성되었습니다.` + } + } catch (error) { + console.error("Create purchase request error:", error) + return { error: "구매요청 생성 중 오류가 발생했습니다." } + } +} + +// 구매요청 수정 +export async function updatePurchaseRequest( + id: number, + input: Partial<z.infer<typeof createRequestSchema>> +) { + try { + const session = await getServerSession(authOptions) + if (!session?.user?.id) { + return { error: "인증되지 않은 사용자입니다." } + } + + const userId = Number(session.user.id) + + const result = await db.transaction(async (tx) => { + // 수정 가능한 상태인지 확인 + const [existing] = await tx + .select() + .from(purchaseRequests) + .where(eq(purchaseRequests.id, id)) + .limit(1) + + if (!existing) { + return { error: "구매요청을 찾을 수 없습니다." } + } + + if (existing.status !== "작성중") { + return { error: "작성중 상태의 요청만 수정할 수 있습니다." } + } + + // 구매요청 업데이트 + const [updated] = await tx + .update(purchaseRequests) + .set({ + projectId: input.projectId, + projectCode: input.projectCode, + projectName: input.projectName, + projectCompany: input.projectCompany, + projectSite: input.projectSite, + classNo: input.classNo, + packageNo: input.packageNo, + packageName: input.packageName, + majorItemMaterialCategory: input.majorItemMaterialCategory, + majorItemMaterialDescription: input.majorItemMaterialDescription, + smCode: input.smCode, + requestTitle: input.requestTitle, + requestDescription: input.requestDescription, + estimatedBudget: input.estimatedBudget, + requestedDeliveryDate: input.requestedDeliveryDate, + items: input.items || [], + updatedBy: userId, + updatedAt: new Date(), + }) + .where(eq(purchaseRequests.id, id)) + .returning() + + // 첨부파일 처리 + if (input.attachments !== undefined) { + // 기존 첨부파일 모두 삭제 + await tx + .delete(purchaseRequestAttachments) + .where(eq(purchaseRequestAttachments.requestId, id)) + + // 새 첨부파일 추가 + if (input.attachments && input.attachments.length > 0) { + await tx.insert(purchaseRequestAttachments).values( + input.attachments.map(file => ({ + requestId: id, + fileName: file.fileName, + originalFileName: file.originalFileName, + filePath: file.filePath, + fileSize: file.fileSize, + fileType: file.fileType, + category: "설계문서", + createdBy: userId, + })) + ) + } + } + + return updated + }) + + revalidateTag("purchase-requests") + + return { + success: true, + data: result, + message: "구매요청이 수정되었습니다." + } + } catch (error) { + console.error("Update purchase request error:", error) + return { error: "구매요청 수정 중 오류가 발생했습니다." } + } +} + +// 첨부파일 삭제 +export async function deletePurchaseRequestAttachment(attachmentId: number) { + const session = await getServerSession(authOptions) + if (!session?.user?.id) throw new Error("Unauthorized"); + await db.delete(purchaseRequestAttachments) + .where(eq(purchaseRequestAttachments.id, attachmentId)); + + revalidatePath("/evcp/purchase-requests"); +} + +// 요청 확정 (작성중 → 요청완료) +export async function confirmPurchaseRequest(requestId: number) { + const session = await getServerSession(authOptions) + if (!session?.user?.id) throw new Error("Unauthorized"); + + const userId = Number(session.user.id) + + const [request] = await db + .select() + .from(purchaseRequests) + .where(eq(purchaseRequests.id, requestId)); + + if (!request) throw new Error("Request not found"); + if (request.status !== "작성중") { + throw new Error("Only draft requests can be confirmed"); + } + + const [updated] = await db + .update(purchaseRequests) + .set({ + status: "요청완료", + confirmedAt: new Date(), + confirmedBy: userId, + updatedBy: userId, + updatedAt: new Date(), + }) + .where(eq(purchaseRequests.id, requestId)) + .returning(); + + revalidatePath("/evcp/purchase-requests"); + return updated; +} + +// 요청 승인 및 RFQ 생성 (첨부파일 이관 포함) +export async function approvePurchaseRequestAndCreateRfq( + requestId: number, + purchasePicId?: number +) { + const session = await getServerSession(authOptions) + if (!session?.user?.id) throw new Error("Unauthorized"); + const userId = Number(session.user.id) + + + return await db.transaction(async (tx) => { + // 구매 요청 및 첨부파일 조회 + const [request] = await tx + .select() + .from(purchaseRequests) + .where(eq(purchaseRequests.id, requestId)); + + const attachments = await tx + .select() + .from(purchaseRequestAttachments) + .where(eq(purchaseRequestAttachments.requestId, requestId)); + + + const rfqCode = await generateItbRfqCode(purchasePicId); + + const [rfq] = await tx.insert(rfqsLast).values({ + rfqCode, + projectId: request.projectId, + itemCode: request.items?.[0]?.itemCode, + itemName: request.items?.[0]?.itemName, + packageNo: request.packageNo, + packageName: request.packageName, + EngPicName: request.engPicName, + pic: purchasePicId || null, + status: "RFQ 생성", + projectCompany: request.projectCompany, + projectSite: request.projectSite, + smCode: request.smCode, + createdBy: userId, + updatedBy: userId, + }).returning(); + + // 첨부파일 이관 + for (const [index, attachment] of attachments.entries()) { + const [rfqAttachment] = await tx.insert(rfqLastAttachments).values({ + attachmentType: "설계", + serialNo: `ENG-${String(index + 1).padStart(3, '0')}`, + rfqId: rfq.id, + description: attachment.description || `설계문서 - ${attachment.originalFileName}`, + currentRevision: "Rev.0", + createdBy: userId, + }).returning(); + + const [revision] = await tx.insert(rfqLastAttachmentRevisions).values({ + attachmentId: rfqAttachment.id, + revisionNo: "Rev.0", + revisionComment: "구매 요청에서 이관된 설계 문서", + isLatest: true, + fileName: attachment.fileName, + originalFileName: attachment.originalFileName, + filePath: attachment.filePath, + fileSize: attachment.fileSize, + fileType: attachment.fileType, + createdBy: userId, + }).returning(); + + await tx + .update(rfqLastAttachments) + .set({ latestRevisionId: revision.id }) + .where(eq(rfqLastAttachments.id, rfqAttachment.id)); + } + + // 구매 요청 상태 업데이트 + await tx + .update(purchaseRequests) + .set({ + status: "RFQ생성완료", + rfqId: rfq.id, + rfqCode: rfq.rfqCode, + rfqCreatedAt: new Date(), + purchasePicId, + updatedBy: userId, + updatedAt: new Date(), + }) + .where(eq(purchaseRequests.id, requestId)); + + return rfq; + }); +} + +export async function getAllPurchaseRequests(input: GetPurchaseRequestsSchema) { + return unstable_cache( + async () => { + // 페이징 + const offset = ((input.page ?? 1) - 1) * (input.perPage ?? 10); + const limit = input.perPage ?? 10; + + // 고급 필터 + const advancedWhere = filterColumns({ + table: purchaseRequestsView, + filters: input.filters ?? [], + joinOperator: input.joinOperator ?? "and", + }); + + // 글로벌 검색 + let globalWhere; + if (input.search) { + const s = `%${input.search}%`; + globalWhere = or( + sql`${purchaseRequestsView.requestCode} ILIKE ${s}`, + sql`${purchaseRequestsView.requestTitle} ILIKE ${s}`, + sql`${purchaseRequestsView.projectCode} ILIKE ${s}`, + sql`${purchaseRequestsView.projectName} ILIKE ${s}`, + sql`${purchaseRequestsView.packageNo} ILIKE ${s}`, + sql`${purchaseRequestsView.packageName} ILIKE ${s}`, + sql`${purchaseRequestsView.engPicName} ILIKE ${s}`, + sql`${purchaseRequestsView.purchasePicName} ILIKE ${s}`, + sql`${purchaseRequestsView.majorItemMaterialCategory} ILIKE ${s}`, + sql`${purchaseRequestsView.smCode} ILIKE ${s}` + ); + } + + // 최종 WHERE + const finalWhere = and(advancedWhere, globalWhere); + + // 정렬 + const orderBy = input.sort?.length + ? input.sort.map((s) => { + const col = (purchaseRequestsView as any)[s.id]; + return s.desc ? desc(col) : asc(col); + }) + : [desc(purchaseRequestsView.createdAt)]; + + // 메인 SELECT + const [rows, total] = await db.transaction(async (tx) => { + const data = await tx + .select() + .from(purchaseRequestsView) + .where(finalWhere) + .orderBy(...orderBy) + .offset(offset) + .limit(limit); + + const [{ count }] = await tx + .select({ count: sql<number>`count(*)`.as("count") }) + .from(purchaseRequestsView) + .where(finalWhere); + + return [data, Number(count)]; + }); + + const pageCount = Math.ceil(total / limit); + return { data: rows, pageCount }; + }, + [JSON.stringify(input)], + { + revalidate: 60, + tags: ["purchase-requests"], + } + )(); +} + +// 통계 정보 조회 함수 (선택사항) +export async function getPurchaseRequestStats() { + return unstable_cache( + async () => { + const stats = await db + .select({ + total: sql<number>`count(*)`.as("total"), + draft: sql<number>`count(*) filter (where status = '작성중')`.as("draft"), + rfqCreated: sql<number>`count(*) filter (where status = 'RFQ생성완료')`.as("rfqCreated"), + }) + .from(purchaseRequestsView); + + return stats[0]; + }, + [], + { + revalidate: 60, + tags: ["purchase-request-stats"], + } + )(); +} + + +// 스키마 정의 +const deleteSchema = z.object({ + ids: z.array(z.number()), +}) + + +// 구매요청 삭제 (단일 또는 복수) +export async function deletePurchaseRequests(input: z.infer<typeof deleteSchema>) { + try { + const session = await getServerSession(authOptions) + if (!session?.user?.id) { + return { error: "인증되지 않은 사용자입니다." } + } + + const { ids } = deleteSchema.parse(input) + + if (ids.length === 0) { + return { error: "삭제할 요청을 선택해주세요." } + } + + // 삭제 가능한 상태인지 확인 (작성중 상태만 삭제 가능) + const requests = await db + .select({ + id: purchaseRequests.id, + status: purchaseRequests.status, + requestCode: purchaseRequests.requestCode, + }) + .from(purchaseRequests) + .where(inArray(purchaseRequests.id, ids)) + + const nonDeletable = requests.filter(req => req.status !== "작성중") + + if (nonDeletable.length > 0) { + const codes = nonDeletable.map(req => req.requestCode).join(", ") + return { + error: `다음 요청은 삭제할 수 없습니다: ${codes}. 작성중 상태의 요청만 삭제 가능합니다.` + } + } + + // 트랜잭션으로 삭제 처리 + await db.transaction(async (tx) => { + // 1. 첨부파일 먼저 삭제 + await tx + .delete(purchaseRequestAttachments) + .where(inArray(purchaseRequestAttachments.requestId, ids)) + + // 2. 구매요청 삭제 + await tx + .delete(purchaseRequests) + .where(inArray(purchaseRequests.id, ids)) + }) + + // 캐시 무효화 + revalidateTag("purchase-requests") + + return { + success: true, + message: ids.length === 1 + ? "구매요청이 삭제되었습니다." + : `${ids.length}개의 구매요청이 삭제되었습니다.` + } + } catch (error) { + console.error("Delete purchase requests error:", error) + return { error: "구매요청 삭제 중 오류가 발생했습니다." } + } +} + +export async function getPurchaseRequestAttachments(requestId: number) { + try { + const attachments = await db + .select({ + id: purchaseRequestAttachments.id, + fileName: purchaseRequestAttachments.fileName, + originalFileName: purchaseRequestAttachments.originalFileName, + filePath: purchaseRequestAttachments.filePath, + fileSize: purchaseRequestAttachments.fileSize, + fileType: purchaseRequestAttachments.fileType, + category: purchaseRequestAttachments.category, + description: purchaseRequestAttachments.description, + createdBy: purchaseRequestAttachments.createdBy, + createdAt: purchaseRequestAttachments.createdAt, + }) + .from(purchaseRequestAttachments) + .where(eq(purchaseRequestAttachments.requestId, requestId)) + .orderBy(desc(purchaseRequestAttachments.createdAt)) + + return { + success: true, + data: attachments + } + } catch (error) { + console.error("Get attachments error:", error) + return { + success: false, + error: "첨부파일 조회 중 오류가 발생했습니다.", + data: [] + } + } +} + + +export async function generateItbRfqCode(purchasePicId?: number): Promise<string> { + try { + let userCode = "???"; + + // purchasePicId가 있으면 users 테이블에서 userCode 조회 + if (purchasePicId) { + const [user] = await db + .select({ userCode: users.userCode }) + .from(users) + .where(eq(users.id, purchasePicId)) + .limit(1); + + if (user?.userCode) { + userCode = user.userCode; + } + } + + // 동일한 userCode로 시작하는 마지막 RFQ 조회 + const lastRfq = await db + .select({ rfqCode: rfqsLast.rfqCode }) + .from(rfqsLast) + .where(like(rfqsLast.rfqCode, `I${userCode}%`)) + .orderBy(desc(rfqsLast.createdAt)) + .limit(1); + + let nextNumber = 1; + + if (lastRfq.length > 0 && lastRfq[0].rfqCode) { + const rfqCode = lastRfq[0].rfqCode; + const serialNumber = rfqCode.slice(-5); // 마지막 5자리 + + if (/^\d{5}$/.test(serialNumber)) { + nextNumber = parseInt(serialNumber) + 1; + } + } + + const paddedNumber = String(nextNumber).padStart(5, "0"); + + return `I${userCode}${paddedNumber}`; + } catch (error) { + console.error("Error generating ITB RFQ code:", error); + const fallback = Date.now().toString().slice(-5); + return `I???${fallback}`; + } + } + + + // lib/purchase-requests/service.ts에 추가 + +// 여러 구매 요청 승인 및 RFQ 생성 +export async function approvePurchaseRequestsAndCreateRfqs( + requestIds: number[], + purchasePicId?: number + ) { + try { + const session = await getServerSession(authOptions) + if (!session?.user?.id) throw new Error("Unauthorized"); + const userId = Number(session.user.id) + + const results = [] + + for (const requestId of requestIds) { + try { + const result = await db.transaction(async (tx) => { + // 구매 요청 조회 + const [request] = await tx + .select() + .from(purchaseRequests) + .where(eq(purchaseRequests.id, requestId)) + + if (!request) { + throw new Error(`구매 요청 ${requestId}를 찾을 수 없습니다.`) + } + + if (request.status === "RFQ생성완료") { + return { skipped: true, requestId, message: "이미 RFQ가 생성되었습니다." } + } + + const attachments = await tx + .select() + .from(purchaseRequestAttachments) + .where(eq(purchaseRequestAttachments.requestId, requestId)) + + const rfqCode = await generateItbRfqCode(purchasePicId) + + const [rfq] = await tx + .insert(rfqsLast) + .values({ + rfqCode, + projectId: request.projectId, + itemCode: request.items?.[0]?.itemCode, + itemName: request.items?.[0]?.itemName, + packageNo: request.packageNo, + packageName: request.packageName, + EngPicName: request.engPicName, + pic: purchasePicId || null, + status: "RFQ 생성", + projectCompany: request.projectCompany, + projectSite: request.projectSite, + smCode: request.smCode, + createdBy: userId, + updatedBy: userId, + }) + .returning() + + // 첨부파일 이관 + for (const [index, attachment] of attachments.entries()) { + const [rfqAttachment] = await tx + .insert(rfqLastAttachments) + .values({ + attachmentType: "설계", + serialNo: `ENG-${String(index + 1).padStart(3, "0")}`, + rfqId: rfq.id, + description: + attachment.description || + `설계문서 - ${attachment.originalFileName}`, + currentRevision: "Rev.0", + createdBy: userId, + }) + .returning() + + const [revision] = await tx + .insert(rfqLastAttachmentRevisions) + .values({ + attachmentId: rfqAttachment.id, + revisionNo: "Rev.0", + revisionComment: "구매 요청에서 이관된 설계 문서", + isLatest: true, + fileName: attachment.fileName, + originalFileName: attachment.originalFileName, + filePath: attachment.filePath, + fileSize: attachment.fileSize, + fileType: attachment.fileType, + createdBy: userId, + }) + .returning() + + await tx + .update(rfqLastAttachments) + .set({ latestRevisionId: revision.id }) + .where(eq(rfqLastAttachments.id, rfqAttachment.id)) + } + + // 구매 요청 상태 업데이트 + await tx + .update(purchaseRequests) + .set({ + status: "RFQ생성완료", + rfqId: rfq.id, + rfqCode: rfq.rfqCode, + rfqCreatedAt: new Date(), + purchasePicId, + updatedBy: userId, + updatedAt: new Date(), + }) + .where(eq(purchaseRequests.id, requestId)) + + return { success: true, rfq, requestId } + }) + + results.push(result) + } catch (err: any) { + console.error(`구매 요청 ${requestId} 처리 중 오류:`, err) + results.push({ + success: false, + requestId, + error: err.message || "알 수 없는 오류 발생", + }) + } + } + + // 캐시 무효화 + revalidateTag("purchase-requests") + revalidateTag( "purchase-request-stats") + + revalidateTag("rfqs") + + return results + } catch (err: any) { + console.error("approvePurchaseRequestsAndCreateRfqs 실행 오류:", err) + throw new Error(err.message || "구매 요청 처리 중 오류가 발생했습니다.") + } + } +
\ No newline at end of file |