diff options
Diffstat (limited to 'db')
| -rw-r--r-- | db/schema/rfqLast.ts | 10 | ||||
| -rw-r--r-- | db/schema/vendors.ts | 150 |
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; |