diff options
Diffstat (limited to 'db/schema/rfqVendor.ts')
| -rw-r--r-- | db/schema/rfqVendor.ts | 224 |
1 files changed, 222 insertions, 2 deletions
diff --git a/db/schema/rfqVendor.ts b/db/schema/rfqVendor.ts index 5752b1c2..0ddf109b 100644 --- a/db/schema/rfqVendor.ts +++ b/db/schema/rfqVendor.ts @@ -1,9 +1,10 @@ import { pgTable, pgView, serial, varchar, text, timestamp, boolean, integer, numeric, date, alias, jsonb } from "drizzle-orm/pg-core"; -import { eq, sql, relations } from "drizzle-orm"; +import { eq, sql, relations,and } from "drizzle-orm"; import { rfqsLast, rfqLastDetails, rfqPrItems } from "./rfqLast"; import { users } from "./users"; import { vendors } from "./vendors"; import { incoterms, paymentTerms } from "./procurementRFQ"; +import { projects } from "./projects"; // ========================================== // 1. 벤더 응답 메인 테이블 (견적서 헤더) @@ -458,4 +459,223 @@ export const vendorQuotationItemsRelations = relations( export type VendorResponse = typeof rfqLastVendorResponses.$inferSelect; export type VendorQuotationItem = typeof rfqLastVendorQuotationItems.$inferSelect; export type VendorAttachment = typeof rfqLastVendorAttachments.$inferSelect; -export type VendorResponseHistory = typeof rfqLastVendorResponseHistory.$inferSelect;
\ No newline at end of file +export type VendorResponseHistory = typeof rfqLastVendorResponseHistory.$inferSelect; + + +// vendorQuotationView - 벤더별 견적 현황을 보여주는 통합 뷰 +export const vendorQuotationView = pgView("vendor_quotation_view").as((qb) => { + const createdByUser = alias(users, "created_by_user"); + const updatedByUser = alias(users, "updated_by_user"); + const sentByUser = alias(users, "sent_by_user"); + const picUser = alias(users, "pic_user"); + + return qb + .select({ + // ===== RFQ 기본 정보 (rfqsLastView에서 가져온 필드들) ===== + id: sql<number>`${rfqsLast.id}`.as("id"), + rfqCode: sql<string>`${rfqsLast.rfqCode}`.as("rfq_code"), + series: sql<string | null>`${rfqsLast.series}`.as("series"), + rfqSealedYn: sql<boolean | null>`${rfqsLast.rfqSealedYn}`.as("rfq_sealed_yn"), + + // RFQ 타입 정보 + rfqType: sql<string | null>`${rfqsLast.rfqType}`.as("rfq_type"), + rfqTitle: sql<string | null>`${rfqsLast.rfqTitle}`.as("rfq_title"), + + // ITB 관련 필드 + projectCompany: sql<string | null>`${rfqsLast.projectCompany}`.as("project_company"), + projectFlag: sql<string | null>`${rfqsLast.projectFlag}`.as("project_flag"), + projectSite: sql<string | null>`${rfqsLast.projectSite}`.as("project_site"), + smCode: sql<string | null>`${rfqsLast.smCode}`.as("sm_code"), + + // RFQ 추가 필드 + prNumber: sql<string | null>`${rfqsLast.prNumber}`.as("pr_number"), + prIssueDate: sql<Date | null>`${rfqsLast.prIssueDate}`.as("pr_issue_date"), + + // 프로젝트 정보 + projectId: sql<number | null>`${rfqsLast.projectId}`.as("project_id"), + projectCode: sql<string | null>`${projects.code}`.as("project_code"), + projectName: sql<string | null>`${projects.name}`.as("project_name"), + + // 아이템 정보 + itemCode: sql<string | null>`${rfqsLast.itemCode}`.as("item_code"), + itemName: sql<string | null>`${rfqsLast.itemName}`.as("item_name"), + + // 패키지 정보 + packageNo: sql<string | null>`${rfqsLast.packageNo}`.as("package_no"), + packageName: sql<string | null>`${rfqsLast.packageName}`.as("package_name"), + + engPicName: sql<string | null>`${rfqsLast.EngPicName}`.as("eng_pic_name"), + + // 상태와 날짜 + status: sql<string>`${rfqsLast.status}`.as("status"), + rfqSendDate: sql<Date | null>`${rfqsLast.rfqSendDate}`.as("rfq_send_date"), + dueDate: sql<Date | null>`${rfqsLast.dueDate}`.as("due_date"), + + // PIC 정보 + picId: sql<number | null>`${rfqsLast.pic}`.as("pic_id"), + picCode: sql<string | null>`${rfqsLast.picCode}`.as("pic_code"), + picName: sql<string | null>`${rfqsLast.picName}`.as("pic_name"), + picUserName: sql<string | null>`${picUser.name}`.as("pic_user_name"), + + // 감사 정보 + createdBy: sql<number>`${rfqsLast.createdBy}`.as("created_by"), + createdByUserName: sql<string | null>`${createdByUser.name}`.as("created_by_user_name"), + createdAt: sql<Date>`${rfqsLast.createdAt}`.as("created_at"), + sentBy: sql<number | null>`${rfqsLast.sentBy}`.as("sent_by"), + sentByUserName: sql<string | null>`${sentByUser.name}`.as("sent_by_user_name"), + updatedBy: sql<number>`${rfqsLast.updatedBy}`.as("updated_by"), + updatedByUserName: sql<string | null>`${updatedByUser.name}`.as("updated_by_user_name"), + updatedAt: sql<Date>`${rfqsLast.updatedAt}`.as("updated_at"), + remark: sql<string | null>`${rfqsLast.remark}`.as("remark"), + + // ===== 벤더별 정보 ===== + vendorId: sql<number | null>`${vendors.id}`.as("vendor_id"), + vendorName: sql<string | null>`${vendors.vendorName}`.as("vendor_name"), + vendorCode: sql<string | null>`${vendors.vendorCode}`.as("vendor_code"), + + // rfqLastDetails 정보 + rfqLastDetailsId: sql<number | null>`${rfqLastDetails.id}`.as("rfq_last_details_id"), + emailSentAt: sql<Date | null>`${rfqLastDetails.emailSentAt}`.as("email_sent_at"), + emailStatus: sql<string | null>`${rfqLastDetails.emailStatus}`.as("email_status"), + shortList: sql<boolean>`${rfqLastDetails.shortList}`.as("short_list"), + + // ===== 벤더 응답 정보 (rfqLastVendorResponses) ===== + vendorResponseId: sql<number | null>`${rfqLastVendorResponses.id}`.as("vendor_response_id"), + + // 참여 상태 + participationStatus: sql<string | null>`${rfqLastVendorResponses.participationStatus}`.as("participation_status"), + participationRepliedAt: sql<Date | null>`${rfqLastVendorResponses.participationRepliedAt}`.as("participation_replied_at"), + nonParticipationReason: sql<string | null>`${rfqLastVendorResponses.nonParticipationReason}`.as("non_participation_reason"), + + // 응답 상태 + responseStatus: sql<string | null>`${rfqLastVendorResponses.status}`.as("response_status"), + responseVersion: sql<number | null>`${rfqLastVendorResponses.responseVersion}`.as("response_version"), + submittedAt: sql<Date | null>`${rfqLastVendorResponses.submittedAt}`.as("submitted_at"), + + // 금액 정보 + totalAmount: sql<number | null>`${rfqLastVendorResponses.totalAmount}`.as("total_amount"), + vendorCurrency: sql<string | null>`${rfqLastVendorResponses.vendorCurrency}`.as("vendor_currency"), + + // 벤더 제안 조건 + vendorPaymentTermsCode: sql<string | null>`${rfqLastVendorResponses.vendorPaymentTermsCode}`.as("vendor_payment_terms_code"), + vendorIncotermsCode: sql<string | null>`${rfqLastVendorResponses.vendorIncotermsCode}`.as("vendor_incoterms_code"), + vendorDeliveryDate: sql<Date | null>`${rfqLastVendorResponses.vendorDeliveryDate}`.as("vendor_delivery_date"), + + // ===== 계산된 필드 - displayStatus ===== + displayStatus: sql<string | null>` + CASE + WHEN ${rfqLastVendorResponses.participationStatus} = '불참' THEN '불참' + WHEN ${rfqLastVendorResponses.participationStatus} = '참여' THEN + COALESCE(${rfqLastVendorResponses.status}, '작성중') + WHEN ${rfqLastVendorResponses.participationStatus} = '미응답' OR ${rfqLastVendorResponses.participationStatus} IS NULL THEN + CASE + WHEN ${rfqLastDetails.emailSentAt} IS NOT NULL THEN '미응답' + ELSE NULL + END + ELSE '미응답' + END + `.as("display_status"), + + // ===== 집계 정보 (RFQ 레벨) ===== + vendorCount: sql<number>`( + SELECT COUNT(*) + FROM rfq_last_details d + WHERE d.rfqs_last_id = ${rfqsLast.id} + AND d.is_latest = true + )`.as("vendor_count"), + + shortListedVendorCount: sql<number>`( + SELECT COUNT(*) + FROM rfq_last_details d + WHERE d.rfqs_last_id = ${rfqsLast.id} + AND d.short_list = true + AND d.is_latest = true + )`.as("short_listed_vendor_count"), + + quotationReceivedCount: sql<number>`( + SELECT COUNT(DISTINCT r.vendor_id) + FROM rfq_last_vendor_responses r + WHERE r.rfqs_last_id = ${rfqsLast.id} + AND r.submitted_at IS NOT NULL + AND r.is_latest = true + )`.as("quotation_received_count"), + + earliestQuotationSubmittedAt: sql<Date | null>`( + SELECT MIN(r.submitted_at) + FROM rfq_last_vendor_responses r + WHERE r.rfqs_last_id = ${rfqsLast.id} + AND r.submitted_at IS NOT NULL + AND r.is_latest = true + )`.as("earliest_quotation_submitted_at"), + + // PR Items 관련 정보 + majorItemMaterialCode: sql<string | null>`( + SELECT material_code + FROM rfq_pr_items + WHERE rfqs_last_id = ${rfqsLast.id} + AND major_yn = true + LIMIT 1 + )`.as("major_item_material_code"), + + majorItemMaterialDescription: sql<string | null>`( + SELECT material_description + FROM rfq_pr_items + WHERE rfqs_last_id = ${rfqsLast.id} + AND major_yn = true + LIMIT 1 + )`.as("major_item_material_description"), + + majorItemMaterialCategory: sql<string | null>`( + SELECT material_category + FROM rfq_pr_items + WHERE rfqs_last_id = ${rfqsLast.id} + AND major_yn = true + LIMIT 1 + )`.as("major_item_material_category"), + + majorItemPrNo: sql<string | null>`( + SELECT pr_no + FROM rfq_pr_items + WHERE rfqs_last_id = ${rfqsLast.id} + AND major_yn = true + LIMIT 1 + )`.as("major_item_pr_no"), + + prItemsCount: sql<number>`( + SELECT COUNT(*) + FROM rfq_pr_items + WHERE rfqs_last_id = ${rfqsLast.id} + )`.as("pr_items_count"), + + majorItemsCount: sql<number>`( + SELECT COUNT(*) + FROM rfq_pr_items + WHERE rfqs_last_id = ${rfqsLast.id} + AND major_yn = true + )`.as("major_items_count") + }) + .from(rfqsLast) + .innerJoin(rfqLastDetails, + and( + eq(rfqLastDetails.rfqsLastId, rfqsLast.id), + eq(rfqLastDetails.isLatest, true) + ) + ) + .leftJoin(vendors, eq(rfqLastDetails.vendorsId, vendors.id)) + .leftJoin(rfqLastVendorResponses, + and( + eq(rfqLastVendorResponses.rfqsLastId, rfqsLast.id), + eq(rfqLastVendorResponses.vendorId, vendors.id), + eq(rfqLastVendorResponses.isLatest, true) + ) + ) + .leftJoin(projects, eq(rfqsLast.projectId, projects.id)) + .leftJoin(createdByUser, eq(rfqsLast.createdBy, createdByUser.id)) + .leftJoin(updatedByUser, eq(rfqsLast.updatedBy, updatedByUser.id)) + .leftJoin(sentByUser, eq(rfqsLast.sentBy, sentByUser.id)) + .leftJoin(picUser, eq(rfqsLast.pic, picUser.id)); +}); + +// Type export +export type VendorQuotationView = typeof vendorQuotationView.$inferSelect; + |