summaryrefslogtreecommitdiff
path: root/db
diff options
context:
space:
mode:
Diffstat (limited to 'db')
-rw-r--r--db/schema/bRfq.ts2
-rw-r--r--db/schema/bidding.ts8
-rw-r--r--db/schema/generalContract.ts19
-rw-r--r--db/schema/index.ts4
-rw-r--r--db/schema/rfq.ts2
-rw-r--r--db/schema/rfqLast.ts109
-rw-r--r--db/schema/rfqLastTBE.ts7
7 files changed, 128 insertions, 23 deletions
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<Date | null>(),
+ 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<Date | null>(),
+ selectionApprovalComment: text("selection_approval_comment"),
+
+ // 계약 전환 정보 (선택사항)
+ contractStatus: varchar("contract_status", { length: 30 })
+ .$type<"대기" | "진행중" | "완료" | "취소" | null>(),
+ contractCreatedAt: timestamp("contract_created_at", { withTimezone: true })
+ .$type<Date | null>(),
+ 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<string[]>(),
+
+ // 선정 승인 정보 (옵션)
+ 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<number>`(
- 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<Date | null>`${tbeSession.actualStartDate}`.as("actual_start_date"),
generated by cgit v1.2.3 (git 2.39.1) at 2025-12-07 06:57:47 +0000