diff options
Diffstat (limited to 'lib/tech-vendor-rfq-response/service.ts')
| -rw-r--r-- | lib/tech-vendor-rfq-response/service.ts | 467 |
1 files changed, 467 insertions, 0 deletions
diff --git a/lib/tech-vendor-rfq-response/service.ts b/lib/tech-vendor-rfq-response/service.ts new file mode 100644 index 00000000..b706d42a --- /dev/null +++ b/lib/tech-vendor-rfq-response/service.ts @@ -0,0 +1,467 @@ +'use server' + +import { revalidateTag, unstable_cache } from "next/cache"; +import db from "@/db/db"; +import { and, desc, eq, inArray, isNull, or, sql } from "drizzle-orm"; +import { rfqAttachments, rfqComments, rfqItems, vendorResponses } from "@/db/schema/rfq"; +import { vendorResponsesView, vendorTechnicalResponses, vendorCommercialResponses, vendorResponseAttachments } from "@/db/schema/rfq"; +import { items, itemOffshoreTop, itemOffshoreHull } from "@/db/schema/items"; +import { GetRfqsForVendorsSchema } from "../rfqs-tech/validations"; +import { ItemData } from "./vendor-cbe-table/rfq-items-table/rfq-items-table"; +import * as z from "zod" + + + +export async function getRfqResponsesForVendor(input: GetRfqsForVendorsSchema, vendorId: number) { + return unstable_cache( + async () => { + const offset = (input.page - 1) * input.perPage; + const limit = input.perPage; + + // 1) 메인 쿼리: vendorResponsesView 사용 + const { rows, total } = await db.transaction(async (tx) => { + // 검색 조건 + let globalWhere; + if (input.search) { + const s = `%${input.search}%`; + globalWhere = or( + sql`${vendorResponsesView.rfqCode} ILIKE ${s}`, + sql`${vendorResponsesView.projectName} ILIKE ${s}`, + sql`${vendorResponsesView.rfqDescription} ILIKE ${s}` + ); + } + + // 협력업체 ID 필터링 + const mainWhere = and(eq(vendorResponsesView.vendorId, vendorId), globalWhere); + + // 정렬: 응답 시간순 + const orderBy = [desc(vendorResponsesView.respondedAt)]; + + // (A) 데이터 조회 + const data = await tx + .select() + .from(vendorResponsesView) + .where(mainWhere) + .orderBy(...orderBy) + .offset(offset) + .limit(limit); + + // (B) 전체 개수 카운트 + const [{ count }] = await tx + .select({ + count: sql<number>`count(*)`.as("count"), + }) + .from(vendorResponsesView) + .where(mainWhere); + + return { rows: data, total: Number(count) }; + }); + + // 2) rfqId 고유 목록 추출 + const distinctRfqs = [...new Set(rows.map((r) => r.rfqId))]; + if (distinctRfqs.length === 0) { + return { data: [], pageCount: 0 }; + } + + // 3) 추가 데이터 조회 + // 3-A) RFQ 아이템 + const itemsAll = await db + .select({ + id: rfqItems.id, + rfqId: rfqItems.rfqId, + itemCode: rfqItems.itemCode, + itemList: sql<string>`COALESCE(${itemOffshoreTop.itemList}, ${itemOffshoreHull.itemList})`.as('itemList'), + subItemList: sql<string>`COALESCE(${itemOffshoreTop.subItemList}, ${itemOffshoreHull.subItemList})`.as('subItemList'), + quantity: rfqItems.quantity, + description: rfqItems.description, + uom: rfqItems.uom, + }) + .from(rfqItems) + .leftJoin(itemOffshoreTop, eq(rfqItems.itemCode, itemOffshoreTop.itemCode)) + .leftJoin(itemOffshoreHull, eq(rfqItems.itemCode, itemOffshoreHull.itemCode)) + .where(inArray(rfqItems.rfqId, distinctRfqs)); + + // 3-B) RFQ 첨부 파일 (협력업체용) + const attachAll = await db + .select() + .from(rfqAttachments) + .where( + and( + inArray(rfqAttachments.rfqId, distinctRfqs), + isNull(rfqAttachments.vendorId) + ) + ); + + // 3-C) RFQ 코멘트 + const commAll = await db + .select() + .from(rfqComments) + .where( + and( + inArray(rfqComments.rfqId, distinctRfqs), + or( + isNull(rfqComments.vendorId), + eq(rfqComments.vendorId, vendorId) + ) + ) + ); + + + // 3-E) 협력업체 응답 상세 - 기술 + const technicalResponsesAll = await db + .select() + .from(vendorTechnicalResponses) + .where( + inArray( + vendorTechnicalResponses.responseId, + rows.map((r) => r.responseId) + ) + ); + + // 3-F) 협력업체 응답 상세 - 상업 + const commercialResponsesAll = await db + .select() + .from(vendorCommercialResponses) + .where( + inArray( + vendorCommercialResponses.responseId, + rows.map((r) => r.responseId) + ) + ); + + // 3-G) 협력업체 응답 첨부 파일 + const responseAttachmentsAll = await db + .select() + .from(vendorResponseAttachments) + .where( + inArray( + vendorResponseAttachments.responseId, + rows.map((r) => r.responseId) + ) + ); + + // 4) 데이터 그룹화 + // RFQ 아이템 그룹화 + const itemsByRfqId = new Map<number, any[]>(); + for (const it of itemsAll) { + if (!itemsByRfqId.has(it.rfqId)) { + itemsByRfqId.set(it.rfqId, []); + } + itemsByRfqId.get(it.rfqId)!.push({ + id: it.id, + itemCode: it.itemCode, + itemList: it.itemList, + subItemList: it.subItemList, + quantity: it.quantity, + description: it.description, + uom: it.uom, + }); + } + + // RFQ 첨부 파일 그룹화 + const attachByRfqId = new Map<number, any[]>(); + for (const att of attachAll) { + const rid = att.rfqId!; + if (!attachByRfqId.has(rid)) { + attachByRfqId.set(rid, []); + } + attachByRfqId.get(rid)!.push({ + id: att.id, + fileName: att.fileName, + filePath: att.filePath, + vendorId: att.vendorId, + evaluationId: att.evaluationId, + }); + } + + // RFQ 코멘트 그룹화 + const commByRfqId = new Map<number, any[]>(); + for (const c of commAll) { + const rid = c.rfqId!; + if (!commByRfqId.has(rid)) { + commByRfqId.set(rid, []); + } + commByRfqId.get(rid)!.push({ + id: c.id, + commentText: c.commentText, + vendorId: c.vendorId, + evaluationId: c.evaluationId, + createdAt: c.createdAt, + }); + } + + + // 기술 응답 그룹화 + const techResponseByResponseId = new Map<number, any>(); + for (const tr of technicalResponsesAll) { + techResponseByResponseId.set(tr.responseId, { + id: tr.id, + summary: tr.summary, + notes: tr.notes, + createdAt: tr.createdAt, + updatedAt: tr.updatedAt, + }); + } + + // 상업 응답 그룹화 + const commResponseByResponseId = new Map<number, any>(); + for (const cr of commercialResponsesAll) { + commResponseByResponseId.set(cr.responseId, { + id: cr.id, + totalPrice: cr.totalPrice, + currency: cr.currency, + paymentTerms: cr.paymentTerms, + incoterms: cr.incoterms, + deliveryPeriod: cr.deliveryPeriod, + warrantyPeriod: cr.warrantyPeriod, + validityPeriod: cr.validityPeriod, + priceBreakdown: cr.priceBreakdown, + commercialNotes: cr.commercialNotes, + createdAt: cr.createdAt, + updatedAt: cr.updatedAt, + }); + } + + // 응답 첨부 파일 그룹화 + const respAttachByResponseId = new Map<number, any[]>(); + for (const ra of responseAttachmentsAll) { + const rid = ra.responseId!; + if (!respAttachByResponseId.has(rid)) { + respAttachByResponseId.set(rid, []); + } + respAttachByResponseId.get(rid)!.push({ + id: ra.id, + fileName: ra.fileName, + filePath: ra.filePath, + attachmentType: ra.attachmentType, + description: ra.description, + uploadedAt: ra.uploadedAt, + uploadedBy: ra.uploadedBy, + }); + } + + // 5) 최종 데이터 결합 + const final = rows.map((row) => { + return { + // 응답 정보 + responseId: row.responseId, + responseStatus: row.responseStatus, + respondedAt: row.respondedAt, + + // RFQ 기본 정보 + rfqId: row.rfqId, + rfqCode: row.rfqCode, + rfqDescription: row.rfqDescription, + rfqDueDate: row.rfqDueDate, + rfqStatus: row.rfqStatus, + + rfqCreatedAt: row.rfqCreatedAt, + rfqUpdatedAt: row.rfqUpdatedAt, + rfqCreatedBy: row.rfqCreatedBy, + + // 프로젝트 정보 + projectId: row.projectId, + projectCode: row.projectCode, + projectName: row.projectName, + + // 협력업체 정보 + vendorId: row.vendorId, + vendorName: row.vendorName, + vendorCode: row.vendorCode, + + // RFQ 관련 데이터 + items: itemsByRfqId.get(row.rfqId) || [], + attachments: attachByRfqId.get(row.rfqId) || [], + comments: commByRfqId.get(row.rfqId) || [], + + // 평가 정보 + tbeEvaluation: row.tbeId ? { + id: row.tbeId, + result: row.tbeResult, + } : null, + cbeEvaluation: row.cbeId ? { + id: row.cbeId, + result: row.cbeResult, + } : null, + + // 협력업체 응답 상세 + technicalResponse: techResponseByResponseId.get(row.responseId) || null, + commercialResponse: commResponseByResponseId.get(row.responseId) || null, + responseAttachments: respAttachByResponseId.get(row.responseId) || [], + + // 응답 상태 표시 + hasTechnicalResponse: row.hasTechnicalResponse, + hasCommercialResponse: row.hasCommercialResponse, + attachmentCount: row.attachmentCount || 0, + }; + }); + + const pageCount = Math.ceil(total / input.perPage); + return { data: final, pageCount }; + }, + [JSON.stringify(input), `${vendorId}`], + { + revalidate: 600, + tags: ["rfqs-vendor", `vendor-${vendorId}`], + } + )(); +} + + +export async function getItemsByRfqId(rfqId: number): Promise<ResponseType> { + try { + if (!rfqId || isNaN(Number(rfqId))) { + return { + success: false, + error: "Invalid RFQ ID provided", + } + } + + // Query the database to get all items for the given RFQ ID + const items = await db + .select() + .from(rfqItems) + .where(eq(rfqItems.rfqId, rfqId)) + .orderBy(rfqItems.itemCode) + + + return { + success: true, + data: items as ItemData[], + } + } catch (error) { + console.error("Error fetching RFQ items:", error) + + return { + success: false, + error: error instanceof Error ? error.message : "Unknown error occurred when fetching RFQ items", + } + } +} + + +// Define the schema for validation +const commercialResponseSchema = z.object({ + responseId: z.number(), + vendorId: z.number(), // Added vendorId field + responseStatus: z.enum(["PENDING", "IN_PROGRESS", "SUBMITTED", "REJECTED", "ACCEPTED"]), + totalPrice: z.number().optional(), + currency: z.string().default("USD"), + paymentTerms: z.string().optional(), + incoterms: z.string().optional(), + deliveryPeriod: z.string().optional(), + warrantyPeriod: z.string().optional(), + validityPeriod: z.string().optional(), + priceBreakdown: z.string().optional(), + commercialNotes: z.string().optional(), +}) + +type CommercialResponseInput = z.infer<typeof commercialResponseSchema> + +interface ResponseType { + success: boolean + error?: string + data?: any +} + +export async function updateCommercialResponse(input: CommercialResponseInput): Promise<ResponseType> { + try { + // Validate input data + const validated = commercialResponseSchema.parse(input) + + // Check if a commercial response already exists for this responseId + const existingResponse = await db + .select() + .from(vendorCommercialResponses) + .where(eq(vendorCommercialResponses.responseId, validated.responseId)) + .limit(1) + + const now = new Date() + + if (existingResponse.length > 0) { + // Update existing record + await db + .update(vendorCommercialResponses) + .set({ + responseStatus: validated.responseStatus, + totalPrice: validated.totalPrice, + currency: validated.currency, + paymentTerms: validated.paymentTerms, + incoterms: validated.incoterms, + deliveryPeriod: validated.deliveryPeriod, + warrantyPeriod: validated.warrantyPeriod, + validityPeriod: validated.validityPeriod, + priceBreakdown: validated.priceBreakdown, + commercialNotes: validated.commercialNotes, + updatedAt: now, + }) + .where(eq(vendorCommercialResponses.responseId, validated.responseId)) + + } else { + // Return error instead of creating a new record + return { + success: false, + error: "해당 응답 ID에 대한 상업 응답 정보를 찾을 수 없습니다." + } + } + + // Also update the main vendor response status if submitted + if (validated.responseStatus === "SUBMITTED") { + // Get the vendor response + const vendorResponseResult = await db + .select() + .from(vendorResponses) + .where(eq(vendorResponses.id, validated.responseId)) + .limit(1) + + if (vendorResponseResult.length > 0) { + // Update the main response status to RESPONDED + await db + .update(vendorResponses) + .set({ + responseStatus: "RESPONDED", + updatedAt: now, + }) + .where(eq(vendorResponses.id, validated.responseId)) + } + } + + // Use vendorId for revalidateTag + revalidateTag(`cbe-vendor-${validated.vendorId}`) + + return { + success: true, + data: { responseId: validated.responseId } + } + + } catch (error) { + console.error("Error updating commercial response:", error) + + if (error instanceof z.ZodError) { + return { + success: false, + error: "유효하지 않은 데이터가 제공되었습니다." + } + } + + return { + success: false, + error: error instanceof Error ? error.message : "Unknown error occurred" + } + } +} +// Helper function to get responseId from rfqId and vendorId +export async function getCommercialResponseByResponseId(responseId: number): Promise<any | null> { + try { + const response = await db + .select() + .from(vendorCommercialResponses) + .where(eq(vendorCommercialResponses.responseId, responseId)) + .limit(1) + + return response.length > 0 ? response[0] : null + } catch (error) { + console.error("Error getting commercial response:", error) + return null + } +}
\ No newline at end of file |