(김준회) RFQ 인터페이스 처리, 변수명 오타 수정(VendorPossibleMaterials), 협력업체 관리페이지 구매요구사항 반영

2 files changed, 152 insertions, 8 deletions

diff --git a/db/schema/rfqLast.ts b/db/schema/rfqLast.ts
index a9f91308..95d2cc9b 100644
--- a/db/schema/rfqLast.ts
+++ b/db/schema/rfqLast.ts
@@ -1,4 +1,4 @@
-import { foreignKey, pgTable, pgView, serial, varchar, text, timestamp, boolean, integer, numeric, date, alias, check, uniqueIndex } from "drizzle-orm/pg-core";
+import { pgTable, pgView, serial, varchar, text, timestamp, boolean, integer, numeric, date, alias, check, uniqueIndex } from "drizzle-orm/pg-core";
 import { eq, sql, relations } from "drizzle-orm";
 import { projects } from "./projects";
 import { users } from "./users";
@@ -22,8 +22,16 @@ export const rfqsLast = pgTable(
     {
         id: serial("id").primaryKey(),
 
+        // RFQ 채번 룰에 따라 생성한 넘버
+        // 생성 규칙은 `RFQ${userCode}${00001}` 형식으로 순차 증가
+        // 구매그룹코드(userCode)별로 일련번호를 증가시켜야 하는지는 강미경프로 답변에 따라 작업
         rfqCode: varchar("rfq_code", { length: 50 }).unique(), // ex) "RFQ-2025-001"
 
+
+        // ANFNR (RFQ/Bidding No) 로 ECC측 RFQ ID (내부적으로만 사용)
+        // IF로 받은 게 아니면 null로 처리되고 유일성 검사에서 제외됨
+        ANFNR: varchar("ANFNR", { length: 50 }).unique(),
+
         projectId: integer("project_id")
             .references(() => projects.id, { onDelete: "set null" }),
 
diff --git a/db/schema/vendors.ts b/db/schema/vendors.ts
index f6f74373..c03949e0 100644
--- a/db/schema/vendors.ts
+++ b/db/schema/vendors.ts
@@ -65,6 +65,9 @@ export const vendors = pgTable("vendors", {
 
   businessSize: varchar("business_size", { length: 255 }),
 
+  // 성조회 가입여부: 공제회 가입여부이며, 구매에서 직접 입력하겠다는 값임. 'E'=해당없음, 'Y'=가입, 'N'=미가입, null='-'
+  isAssociationMember: varchar("is_association_member", { length: 1 }),
+
   // // PQ 승인시 받을 정보
   // // 그룹사
   // groupCompany: varchar("group_company", { length: 255 }),
@@ -121,7 +124,7 @@ export const vendorPossibleItems = pgTable("vendor_possible_items", {
 // MDG 자재마스터에 대해 벤더의 공급품목 정보를 저장하는 테이블
 // 업체 회원 가입시 입력 정보는 업체입력정보(notConfirmed), 구매담당자가 PQ, 실사, 정규업체등록요청 했을 때, PO I/F 통해 받은 자재그룹코드는 확정정보(Confirmed)임
 // MDG 자재마스터와의 정규화 레벨은 의도적으로 낮춤. (스캔 비용)
-export const vendorPossibleMateirals = pgTable("vendor_possible_materials", {
+export const vendorPossibleMaterials = pgTable("vendor_possible_materials", {
   // 인공키
   id: serial("id").primaryKey(),
   // 벤더 아이디
@@ -177,8 +180,8 @@ export const vendorMaterialsView = pgView("vendor_materials_view").as((qb) => {
   return qb
     .select({
       // vendorPossibleItems의 "id" -> "vendorItemId"
-      vendorItemId: vendorPossibleMateirals.id,
-      vendorId: vendorPossibleMateirals.vendorId,
+      vendorItemId: vendorPossibleMaterials.id,
+      vendorId: vendorPossibleMaterials.vendorId,
 
       // items의 "id" -> "itemId"
       // itemId: items.id,
@@ -189,11 +192,11 @@ export const vendorMaterialsView = pgView("vendor_materials_view").as((qb) => {
       steelType: materials.steelType,
       gradeMaterial: materials.gradeMaterial,
 
-      createdAt: vendorPossibleMateirals.createdAt,
-      updatedAt: vendorPossibleMateirals.updatedAt,
+      createdAt: vendorPossibleMaterials.createdAt,
+      updatedAt: vendorPossibleMaterials.updatedAt,
     })
-    .from(vendorPossibleMateirals)
-    .leftJoin(materials, eq(vendorPossibleMateirals.itemCode, materials.itemCode))
+    .from(vendorPossibleMaterials)
+    .leftJoin(materials, eq(vendorPossibleMaterials.itemCode, materials.itemCode))
 })
 
 export const vendorAttachments = pgTable("vendor_attachments", {
@@ -466,6 +469,7 @@ export const vendorsWithTypesView = pgView("vendors_with_types").as((qb) => {
       creditAgency: sql<string>`${vendors.creditAgency}`.as("credit_agency"),
       creditRating: sql<string>`${vendors.creditRating}`.as("credit_rating"),
       cashFlowRating: sql<string>`${vendors.cashFlowRating}`.as("cash_flow_rating"),
+      isAssociationMember: sql<string>`${vendors.isAssociationMember}`.as("is_association_member"),
       createdAt: sql<Date>`${vendors.createdAt}`.as("created_at"),
       updatedAt: sql<Date>`${vendors.updatedAt}`.as("updated_at"),
 
@@ -490,9 +494,141 @@ export const vendorsWithTypesView = pgView("vendors_with_types").as((qb) => {
     );
 });
 
+// 확장된 업체 뷰 - 대표품목 정보 포함
+export const vendorsWithTypesAndMaterialsView = pgView("vendors_with_types_and_materials").as((qb) => {
+  return qb
+    .select({
+      // 기존 모든 필드들
+      id: sql<number>`${vendors.id}`.as("id"),
+      vendorName: sql<string>`${vendors.vendorName}`.as("vendor_name"),
+      vendorCode: sql<string>`${vendors.vendorCode}`.as("vendor_code"),
+      taxId: sql<string>`${vendors.taxId}`.as("tax_id"),
+      address: sql<string>`${vendors.address}`.as("address"),
+      addressDetail: sql<string>`${vendors.addressDetail}`.as("address_detail"),
+      postalCode: sql<string>`${vendors.postalCode}`.as("postal_code"),
+      country: sql<string>`${vendors.country}`.as("country"),
+      phone: sql<string>`${vendors.phone}`.as("phone"),
+      email: sql<string>`${vendors.email}`.as("email"),
+      businessSize: sql<string>`${vendors.businessSize}`.as("business_size"),
+      website: sql<string>`${vendors.website}`.as("website"),
+      status: sql<string>`${vendors.status}`.as("status"),
+      vendorTypeId: sql<number>`${vendors.vendorTypeId}`.as("vendor_type_id"),
+      representativeName: sql<string>`${vendors.representativeName}`.as("representative_name"),
+      representativeBirth: sql<string>`${vendors.representativeBirth}`.as("representative_birth"),
+      representativeEmail: sql<string>`${vendors.representativeEmail}`.as("representative_email"),
+      representativePhone: sql<string>`${vendors.representativePhone}`.as("representative_phone"),
+      corporateRegistrationNumber: sql<string>`${vendors.corporateRegistrationNumber}`.as("corporate_registration_number"),
+      items: sql<string>`${vendors.items}`.as("items"),
+      creditAgency: sql<string>`${vendors.creditAgency}`.as("credit_agency"),
+      creditRating: sql<string>`${vendors.creditRating}`.as("credit_rating"),
+      cashFlowRating: sql<string>`${vendors.cashFlowRating}`.as("cash_flow_rating"),
+      isAssociationMember: sql<string>`${vendors.isAssociationMember}`.as("is_association_member"),
+      createdAt: sql<Date>`${vendors.createdAt}`.as("created_at"),
+      updatedAt: sql<Date>`${vendors.updatedAt}`.as("updated_at"),
+
+      // Vendor type fields
+      vendorTypeName: sql<string>`${vendorTypes.nameKo}`.as("vendor_type_name"),
+      vendorTypeNameEn: sql<string>`${vendorTypes.nameEn}`.as("vendor_type_name_en"),
+      vendorTypeCode: sql<string>`${vendorTypes.code}`.as("vendor_type_code"),
+
+      // Computed vendor category field
+      vendorCategory: sql<string>`
+        CASE
+          WHEN ${vendors.status} = 'ACTIVE' THEN '정규업체'
+          WHEN ${vendors.status} IN ('INACTIVE', 'BLACKLISTED', 'REJECTED') THEN ''
+          ELSE '잠재업체'
+        END
+      `.as("vendor_category"),
+
+      // 업체대표품목 1, 2, 3 - isConfirmed = true를 우선적으로, 그 다음 false 순으로 정렬
+      primaryMaterial1: sql<string>`
+        (SELECT CASE 
+           WHEN vpm1.item_name IS NOT NULL AND vpm1.item_code IS NOT NULL 
+           THEN vpm1.item_name || E'\n(' || vpm1.item_code || ')'
+           ELSE NULL
+         END
+         FROM vendor_possible_materials vpm1
+         WHERE vpm1.vendor_id = ${vendors.id}
+         ORDER BY vpm1.is_confirmed DESC NULLS LAST, vpm1.created_at DESC
+         LIMIT 1 OFFSET 0)
+      `.as("primary_material_1"),
+
+      primaryMaterial2: sql<string>`
+        (SELECT CASE 
+           WHEN vpm2.item_name IS NOT NULL AND vpm2.item_code IS NOT NULL 
+           THEN vpm2.item_name || E'\n(' || vpm2.item_code || ')'
+           ELSE NULL
+         END
+         FROM vendor_possible_materials vpm2
+         WHERE vpm2.vendor_id = ${vendors.id}
+         ORDER BY vpm2.is_confirmed DESC NULLS LAST, vpm2.created_at DESC
+         LIMIT 1 OFFSET 1)
+      `.as("primary_material_2"),
+
+      primaryMaterial3: sql<string>`
+        (SELECT CASE 
+           WHEN vpm3.item_name IS NOT NULL AND vpm3.item_code IS NOT NULL 
+           THEN vpm3.item_name || E'\n(' || vpm3.item_code || ')'
+           ELSE NULL
+         END
+         FROM vendor_possible_materials vpm3
+         WHERE vpm3.vendor_id = ${vendors.id}
+         ORDER BY vpm3.is_confirmed DESC NULLS LAST, vpm3.created_at DESC
+         LIMIT 1 OFFSET 2)
+      `.as("primary_material_3"),
+
+      // 최근 발주 실적 정보 - 가장 최신 계약 정보
+      recentPoNumber: sql<string>`
+        (SELECT c.contract_no
+         FROM contracts c
+         WHERE c.vendor_id = ${vendors.id}
+         ORDER BY c.start_date DESC NULLS LAST
+         LIMIT 1)
+      `.as("recent_po_number"),
+      
+      recentPoOrderBy: sql<string>`
+        (SELECT CASE 
+           WHEN u.name IS NOT NULL AND u.user_code IS NOT NULL 
+           THEN u.name || E'\n(' || u.user_code || ')'
+           ELSE c.purchase_group
+         END
+         FROM contracts c
+         LEFT JOIN users u ON c.purchase_group = u.user_code
+         WHERE c.vendor_id = ${vendors.id}
+         ORDER BY c.start_date DESC NULLS LAST
+         LIMIT 1)
+      `.as("recent_po_order_by"),
+      
+      recentPoDate: sql<Date>`
+        (SELECT c.start_date
+         FROM contracts c
+         WHERE c.vendor_id = ${vendors.id}
+         ORDER BY c.start_date DESC NULLS LAST
+         LIMIT 1)
+      `.as("recent_po_date"),
+
+      // 최근 조달 실적 정보 (TODO 구현을 위한 플레이스홀더)
+      recentDeliveryNumber: sql<string>`NULL`.as("recent_delivery_number"),
+      recentDeliveryBy: sql<string>`NULL`.as("recent_delivery_by"),
+      recentDeliveryDate: sql<Date>`NULL`.as("recent_delivery_date"),
+
+      // TODO 컬럼들 (UI만 구현)
+      regularEvaluationGrade: sql<string>`NULL`.as("regular_evaluation_grade"),
+      faContract: sql<string>`NULL`.as("fa_contract"),
+      avlRegistration: sql<string>`NULL`.as("avl_registration"),
+      regularVendorRegistration: sql<string>`NULL`.as("regular_vendor_registration")
+    })
+    .from(vendors)
+    .leftJoin(
+      vendorTypes,
+      sql`${vendors.vendorTypeId} = ${vendorTypes.id}`
+    );
+});
+
 
 // You can also create interfaces for the view
 export type VendorWithType = typeof vendorsWithTypesView.$inferSelect;
+export type VendorWithTypeAndMaterials = typeof vendorsWithTypesAndMaterialsView.$inferSelect;