From 6c11fccc84f4c84fa72ee01f9caad9f76f35cea2 Mon Sep 17 00:00:00 2001 From: dujinkim Date: Tue, 16 Sep 2025 09:20:58 +0000 Subject: (대표님, 최겸) 계약, 업로드 관련, 메뉴처리, 입찰, 프리쿼트, rfqLast관련, tbeLast관련 MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- db/schema/bRfq.ts | 2 +- db/schema/bidding.ts | 8 +++- db/schema/generalContract.ts | 19 ++++---- db/schema/index.ts | 4 +- db/schema/rfq.ts | 2 +- db/schema/rfqLast.ts | 109 ++++++++++++++++++++++++++++++++++++++++++- db/schema/rfqLastTBE.ts | 7 --- 7 files changed, 128 insertions(+), 23 deletions(-) (limited to 'db') diff --git a/db/schema/bRfq.ts b/db/schema/bRfq.ts index 589b2b0f..6eef6ee3 100644 --- a/db/schema/bRfq.ts +++ b/db/schema/bRfq.ts @@ -589,7 +589,7 @@ export const finalRfqDetailView = pgView("final_rfq_detail", { `); // 4. 벤더 응답 현황 요약 뷰 -export const vendorResponseSummaryView = pgView("vendor_response_summary", { +export const vendorResponseSummaryView2 = pgView("vendor_response_summary", { rfqId: integer("rfq_id"), rfqCode: varchar("rfq_code", { length: 50 }), rfqStatus: varchar("rfq_status", { length: 30 }), diff --git a/db/schema/bidding.ts b/db/schema/bidding.ts index 9015026d..b2b21c33 100644 --- a/db/schema/bidding.ts +++ b/db/schema/bidding.ts @@ -73,9 +73,11 @@ export const awardCountEnum = pgEnum('award_count', [ export const invitationStatusEnum = pgEnum('invitation_status', [ 'pending', // 초대 대기 'sent', // 초대 발송 + 'bidding_invited', // 입찰 초대 'accepted', // 참여 수락 'declined', // 참여 거절 - 'submitted' // 견적 제출 완료 + 'submitted', // 견적 제출 완료 + 'bidding_submitted' // 입찰 제출 완료 ]) // 6. 문서 타입 enum @@ -122,7 +124,8 @@ export const biddings = pgTable('biddings', { biddingNumber: varchar('bidding_number', { length: 50 }).unique().notNull(), // 입찰 No. revision: integer('revision').default(0), // Rev. projectId: integer('project_id').references(() => projects.id), - + //견적에서 넘어온 레코드인지, 자체생산인지, 디폴트는 자체생산, notnull + biddingSourceType: varchar('bidding_source_type', { length: 20 }).notNull().default('manual'), // 기본 정보 projectName: varchar('project_name', { length: 300 }), // 프로젝트명 itemName: varchar('item_name', { length: 300 }), // 품목명 @@ -613,6 +616,7 @@ export const biddingListView = pgView('bidding_list_view').as((qb) => title: biddings.title, description: biddings.description, content: biddings.content, + biddingSourceType: biddings.biddingSourceType, isUrgent: biddings.isUrgent, // ═══════════════════════════════════════════════════════════════ diff --git a/db/schema/generalContract.ts b/db/schema/generalContract.ts index fe9e04cb..2571faa3 100644 --- a/db/schema/generalContract.ts +++ b/db/schema/generalContract.ts @@ -29,24 +29,27 @@ export const generalContracts = pgTable('general_contracts', { id: serial('id').primaryKey(), // 계약 고유 ID contractNumber: varchar('contract_number', { length: 255 }).notNull().unique(), // 계약번호 (자동 채번) revision: integer('revision').notNull().default(0), // 계약 개정 번호 + //견적에서 넘어온 레코드인지, 입찰에서 넘어온 레코드인지, 자체 생성한 레코드인지 판단하는 타입, default는 자체생성!, notnull + // contractSourceType: 견적(estimate), 입찰(bid), 자체생성(manual) 중 하나를 저장하는 컬럼, default는 'manual', not null + contractSourceType: varchar('contract_source_type', { length: 20 }).notNull().default('manual'), // ═══════════════════════════════════════════════════════════════ // 계약 분류 및 상태 // ═══════════════════════════════════════════════════════════════ status: varchar('status', { length: 50 }).notNull(), // 계약 상태 (Draft, Complete the Contract, Contract Delete 등) category: varchar('category', { length: 50 }).notNull(), // 계약구분 (단가계약, 일반계약, 매각계약) - type: varchar('type', { length: 50 }).notNull(), // 계약종류 (UP, LE, IL, AL 등) - executionMethod: varchar('execution_method', { length: 50 }).notNull(), // 체결방식 (단가계약, 일반계약 등) - name: varchar('name', { length: 255 }).notNull(), // 계약명 + type: varchar('type', { length: 50 }), // 계약종류 (UP, LE, IL, AL 등) + executionMethod: varchar('execution_method', { length: 50 }), // 체결방식 (단가계약, 일반계약 등) + name: varchar('name', { length: 255 }), // 계약명 selectionMethod: varchar('selection_method', { length: 50 }), // 업체선정방법 // ═══════════════════════════════════════════════════════════════ // 협력업체 및 계약 기간 // ═══════════════════════════════════════════════════════════════ vendorId: integer('vendor_id').notNull().references(() => vendors.id), // 협력업체 ID - startDate: date('start_date').notNull(), // 계약 시작일 - endDate: date('end_date').notNull(), // 계약 종료일 - validityEndDate: date('validity_end_date').notNull(), // 계약 유효기간 종료일 + startDate: date('start_date'), // 계약 시작일 + endDate: date('end_date'), // 계약 종료일 + validityEndDate: date('validity_end_date'), // 계약 유효기간 종료일 // ═══════════════════════════════════════════════════════════════ // 연계 정보 @@ -106,7 +109,7 @@ export const generalContracts = pgTable('general_contracts', { // 기타 계약 조건 및 약관 (JSON 형태) // ═══════════════════════════════════════════════════════════════ terms: jsonb('terms').default({}), // 계약 조건 - complianceChecklist: jsonb('compliance_checklist').default({}), // 하도급법 체크리스트 + complianceChecklist: jsonb('compliance_checklist').default({}), // 컴플라이언스 체크리스트 communicationChannels: jsonb('communication_channels').default({}), // 커뮤니케이션 채널 locations: jsonb('locations').default({}), // 위치 정보 fieldServiceRates: jsonb('field_service_rates').default({}), // 현장 서비스 요금 @@ -158,7 +161,7 @@ export const generalContractItems = pgTable('general_contract_items', { export const generalContractAttachments = pgTable('general_contract_attachments', { id: serial('id').primaryKey(), contractId: integer('contract_id').notNull().references(() => generalContracts.id), - poContractId: integer('po_contract_id').references(() => contracts.id), + poContractId: integer('po_contract_id').references(() => contracts.id), documentName: varchar('document_name', { length: 255 }).notNull(), // '사양 및 공급범위', '단가파일', '계약서 서명본' 등 fileName: varchar('file_name', { length: 255 }).notNull(), // 실제 파일명 filePath: varchar('file_path', { length: 512 }).notNull(), // 파일 저장 경로 (S3 URL 등) diff --git a/db/schema/index.ts b/db/schema/index.ts index 12c7cdaf..2bbdb267 100644 --- a/db/schema/index.ts +++ b/db/schema/index.ts @@ -17,7 +17,7 @@ export * from './setting'; export * from './techSales'; export * from './ocr'; // 명시적 import/export로 vendorResponseSummaryView 이름 충돌 방지 -export { vendorResponseSummaryView as bRfqVendorResponseSummaryView } from './bRfq'; +export * from './bRfq'; export * from './techVendors'; export * from './evaluation'; export * from './evaluationTarget'; @@ -39,7 +39,7 @@ export * from './vendorRegistrations'; export * from './compliance'; export * from './rfqLast'; // 명시적 import/export로 vendorResponseSummaryView 이름 충돌 방지 -export { vendorResponseSummaryView as rfqVendorResponseSummaryView } from './rfqVendor'; +export * from './rfqVendor'; export * from './generalContract'; export * from './rfqLastTBE'; export * from './pcr'; diff --git a/db/schema/rfq.ts b/db/schema/rfq.ts index 66357972..b07e3137 100644 --- a/db/schema/rfq.ts +++ b/db/schema/rfq.ts @@ -572,7 +572,7 @@ export const vendorCbeView = pgView("vendor_cbe_view").as((qb) => { }); // Types for TypeScript -export type VendorResponse = typeof vendorResponses.$inferSelect; +export type VendorResponse2 = typeof vendorResponses.$inferSelect; export type VendorTechnicalResponse = typeof vendorTechnicalResponses.$inferSelect; export type VendorCommercialResponse = typeof vendorCommercialResponses.$inferSelect; export type VendorResponseAttachment = typeof vendorResponseAttachments.$inferSelect; diff --git a/db/schema/rfqLast.ts b/db/schema/rfqLast.ts index 766aeb6b..f643a2fa 100644 --- a/db/schema/rfqLast.ts +++ b/db/schema/rfqLast.ts @@ -1,4 +1,4 @@ -import { index, pgTable, pgView, serial, varchar, text, timestamp, boolean, integer, numeric, date, alias, check, uniqueIndex } from "drizzle-orm/pg-core"; +import { decimal, json,index, pgTable, pgView, serial, varchar, text, timestamp, boolean, integer, numeric, date, alias, check, uniqueIndex, unique } from "drizzle-orm/pg-core"; import { eq, sql, relations } from "drizzle-orm"; import { projects } from "./projects"; import { users } from "./users"; @@ -131,6 +131,40 @@ export const rfqLastDetails = pgTable( placeOfDestination: varchar("place_of_destination", { length: 255 }), remark: text("remark"), cancelReason: text("cancel_reason"), + + // ===== 업체 선정 관련 컬럼 추가 ===== + // 선정 정보 + isSelected: boolean("is_selected").default(false), + selectionDate: timestamp("selection_date", { withTimezone: true }) + .$type(), + selectionReason: text("selection_reason"), + selectedBy: integer("selected_by") + .references(() => users.id, { onDelete: "set null" }), + + // 가격 및 평가 정보 + totalAmount: decimal("total_amount", { precision: 15, scale: 2 }), + priceRank: integer("price_rank"), + technicalScore: decimal("technical_score", { precision: 5, scale: 2 }), // 기술평가 점수 (선택) + commercialScore: decimal("commercial_score", { precision: 5, scale: 2 }), // 상업평가 점수 (선택) + totalScore: decimal("total_score", { precision: 5, scale: 2 }), // 종합점수 (선택) + + // 선정 승인 프로세스 (선택사항) + selectionApprovalStatus: varchar("selection_approval_status", { length: 30 }) + .$type<"대기" | "승인" | "반려" | null>(), + selectionApprovedBy: integer("selection_approved_by") + .references(() => users.id, { onDelete: "set null" }), + selectionApprovedAt: timestamp("selection_approved_at", { withTimezone: true }) + .$type(), + selectionApprovalComment: text("selection_approval_comment"), + + // 계약 전환 정보 (선택사항) + contractStatus: varchar("contract_status", { length: 30 }) + .$type<"대기" | "진행중" | "완료" | "취소" | null>(), + contractCreatedAt: timestamp("contract_created_at", { withTimezone: true }) + .$type(), + contractNo: varchar("contract_no", { length: 50 }), + + // ===== 기존 컬럼들 ===== updatedBy: integer("updated_by") .notNull() .references(() => users.id, { onDelete: "set null" }), @@ -153,7 +187,7 @@ export const rfqLastDetails = pgTable( firstYn: boolean("first_yn").default(false), firstDescription: text("first_description"), - sparepartDescription: text("sparepart_escription"), + sparepartDescription: text("sparepart_description"), // 오타 수정 sendVersion: integer("send_version").default(0), isLatest: boolean("is_latest").notNull().default(true), @@ -165,6 +199,11 @@ export const rfqLastDetails = pgTable( lastEmailSentAt: timestamp("last_email_sent_at"), emailStatus: varchar("email_status", { length: 30 }) .$type<"pending" | "sent" | "failed" | "bounced" | null>(), + + // ===== 추가 메타데이터 ===== + createdAt: timestamp("created_at").defaultNow().notNull(), // 생성일 추가 + createdBy: integer("created_by") + .references(() => users.id, { onDelete: "set null" }), }, (table) => { return { @@ -174,10 +213,18 @@ export const rfqLastDetails = pgTable( .on(table.rfqsLastId, table.vendorsId) .where(sql`${table.isLatest} = true`), + // 선정된 업체는 RFQ당 하나만 (partial unique index) + uniqueSelectedVendor: uniqueIndex("unique_selected_vendor") + .on(table.rfqsLastId) + .where(sql`${table.isSelected} = true AND ${table.isLatest} = true`), + // 성능을 위한 추가 인덱스들 rfqIdIndex: index("idx_rfqs_last_id").on(table.rfqsLastId), vendorIdIndex: index("idx_vendors_id").on(table.vendorsId), isLatestIndex: index("idx_is_latest").on(table.isLatest), + isSelectedIndex: index("idx_is_selected").on(table.isSelected), + priceRankIndex: index("idx_price_rank").on(table.priceRank), + selectionDateIndex: index("idx_selection_date").on(table.selectionDate), }; } ); @@ -822,3 +869,61 @@ export const rfqDetailsLastRelations = relations( }) ); + + +export const vendorSelections = pgTable( + "vendor_selections", + { + id: serial("id").primaryKey(), + + // RFQ 정보 + rfqId: integer("rfq_id") + .notNull() + .references(() => rfqsLast.id, { onDelete: "cascade" }), + rfqCode: varchar("rfq_code", { length: 50 }), + + // 선정된 업체 정보 + vendorId: integer("vendor_id") + .notNull() + .references(() => vendors.id, { onDelete: "restrict" }), + vendorName: varchar("vendor_name", { length: 255 }).notNull(), + vendorCode: varchar("vendor_code", { length: 50 }).notNull(), + + // 선정 금액 정보 + selectedAmount: decimal("selected_amount", { precision: 15, scale: 2 }).notNull(), + currency: varchar("currency", { length: 10 }).notNull(), + + // 선정 사유 및 평가 + selectionReason: text("selection_reason").notNull(), + priceRank: integer("price_rank"), + hasConditionDifferences: boolean("has_condition_differences").default(false), + criticalDifferences: json("critical_differences").$type(), + + // 선정 승인 정보 (옵션) + approvalStatus: varchar("approval_status", { length: 30 }) + .$type<"대기" | "승인" | "반려">() + .default("대기"), + approvedBy: integer("approved_by") + .references(() => users.id, { onDelete: "set null" }), + approvedAt: timestamp("approved_at", { withTimezone: true }), + approvalComment: text("approval_comment"), + + // 메타 정보 + selectedBy: integer("selected_by") + .references(() => users.id, { onDelete: "set null" }), + selectedAt: timestamp("selected_at", { withTimezone: true }).notNull(), + + createdAt: timestamp("created_at").defaultNow().notNull(), + updatedAt: timestamp("updated_at").defaultNow().notNull(), + }, + (table) => { + return { + // RFQ당 하나의 선정만 가능 + uniqueRfqSelection: unique().on(table.rfqId), + // 인덱스 + rfqIdIdx: index("vendor_selections_rfq_id_idx").on(table.rfqId), + vendorIdIdx: index("vendor_selections_vendor_id_idx").on(table.vendorId), + approvalStatusIdx: index("vendor_selections_approval_status_idx").on(table.approvalStatus), + }; + } + ); \ No newline at end of file diff --git a/db/schema/rfqLastTBE.ts b/db/schema/rfqLastTBE.ts index 1efb43bb..8800cd3d 100644 --- a/db/schema/rfqLastTBE.ts +++ b/db/schema/rfqLastTBE.ts @@ -412,13 +412,6 @@ export const tbeSessionSummaryView = pgView("tbe_session_summary_view").as((qb) WHERE dr.tbe_session_id = ${tbeSession.id} )`.as("total_comments"), - unresolvedComments: sql`( - SELECT COUNT(*) - FROM rfq_last_tbe_pdftron_comments pc - JOIN rfq_last_tbe_document_reviews dr ON pc.document_review_id = dr.id - WHERE dr.tbe_session_id = ${tbeSession.id} - AND pc.status = 'open' - )`.as("unresolved_comments"), // 일정 actualStartDate: sql`${tbeSession.actualStartDate}`.as("actual_start_date"), -- cgit v1.2.3