diff options
Diffstat (limited to 'db/schema')
| -rw-r--r-- | db/schema/bidding.ts | 4 | ||||
| -rw-r--r-- | db/schema/index.ts | 1 | ||||
| -rw-r--r-- | db/schema/pq.ts | 11 | ||||
| -rw-r--r-- | db/schema/vendorDocu.ts | 290 | ||||
| -rw-r--r-- | db/schema/vendorRegistrations.ts | 88 |
5 files changed, 390 insertions, 4 deletions
diff --git a/db/schema/bidding.ts b/db/schema/bidding.ts index 96c189c6..02e869b4 100644 --- a/db/schema/bidding.ts +++ b/db/schema/bidding.ts @@ -237,9 +237,9 @@ export const prItemsForBidding = pgTable('pr_items_for_bidding', { // 수량 및 중량 quantity: decimal('quantity', { precision: 10, scale: 2 }), // 수량 - quantityUnit: quantityUnitEnum('quantity_unit'), // 수량단위 + quantityUnit: varchar('quantity_unit', { length: 50 }), // 수량단위 totalWeight: decimal('total_weight', { precision: 10, scale: 2 }), // 총 중량 - weightUnit: weightUnitEnum('weight_unit'), // 중량단위 + weightUnit: varchar('weight_unit', { length: 50 }), // 중량단위 // 상세 정보 materialDescription: text('material_description'), // 자재내역상세 diff --git a/db/schema/index.ts b/db/schema/index.ts index d19e501e..6f0f11d6 100644 --- a/db/schema/index.ts +++ b/db/schema/index.ts @@ -34,6 +34,7 @@ export * from './docu-list-rule'; export * from './legal'; export * from './consent'; export * from './bidding'; +export * from './vendorRegistrations'; // 부서별 도메인 할당 관리 export * from './departmentDomainAssignments'; diff --git a/db/schema/pq.ts b/db/schema/pq.ts index 7adcbf8b..0bc720f6 100644 --- a/db/schema/pq.ts +++ b/db/schema/pq.ts @@ -133,8 +133,8 @@ export const vendorPQSubmissions = pgTable("vendor_pq_submissions", { dueDate: timestamp("due_date"), agreements: jsonb("agreements").notNull().default({}), // ✅ 체크 항목들을 JSON으로 저장 - // PQ 대상품목 - pqItems: varchar("pq_items", { length: 1000 }), + // PQ 대상품목 - [{ itemCode: string, itemName: string }, ...] + pqItems: jsonb("pq_items"), submittedAt: timestamp("submitted_at"), approvedAt: timestamp("approved_at"), @@ -405,6 +405,9 @@ export const vendorInvestigationsView = pgView( vendorName: vendors.vendorName, vendorCode: vendors.vendorCode, + // PQ 정보 + pqItems: vendorPQSubmissions.pqItems, + // User names and emails instead of just IDs requesterName: sql<string>`requester.name`.as("requesterName"), requesterEmail: sql<string>`requester.email`.as("requesterEmail"), @@ -436,6 +439,10 @@ export const vendorInvestigationsView = pgView( sql`users AS qm_manager`, eq(vendorInvestigations.qmManagerId, sql`qm_manager.id`) ) + .leftJoin( + vendorPQSubmissions, + eq(vendorInvestigations.pqSubmissionId, vendorPQSubmissions.id) + ) }) // 방문실사 요청 테이블 diff --git a/db/schema/vendorDocu.ts b/db/schema/vendorDocu.ts index 1fda2285..58c0ad29 100644 --- a/db/schema/vendorDocu.ts +++ b/db/schema/vendorDocu.ts @@ -21,6 +21,10 @@ export const documents = pgTable( // .notNull() .references(() => contracts.id, { onDelete: "cascade" }), + vendorId: integer("vendor_id") + // .notNull() + .references(() => vendors.id, { onDelete: "cascade" }), + // 기본 문서 정보 docNumber: varchar("doc_number", { length: 100 }).notNull(), vendorDocNumber: varchar("vendor_doc_number", { length: 100 }), @@ -810,6 +814,7 @@ export const simplifiedDocumentsView = pgView("simplified_documents_view", { status: varchar("status", { length: 50 }).notNull(), issuedDate: date("issued_date"), contractId: integer("contract_id"), + vendorId: integer("vendor_id"), // 외부 시스템 연동 정보 externalDocumentId: varchar("external_document_id", { length: 100 }), @@ -1054,6 +1059,7 @@ export const simplifiedDocumentsView = pgView("simplified_documents_view", { SELECT d.id as document_id, d.project_id, + d.vendor_id, d.doc_number, d.drawing_kind, d.drawing_move_gbn, @@ -1323,3 +1329,287 @@ export const documentStagesOnlyView = pgView("document_stages_only_view", { // 타입 추출 export type DocumentStagesOnlyView = typeof documentStagesOnlyView.$inferSelect + + +export const stageDocuments = pgTable( + "stage_documents", + { + id: integer("id").primaryKey().generatedAlwaysAsIdentity(), + + // 필수 문서 정보 + docNumber: varchar("doc_number", { length: 100 }).notNull(), + title: varchar("title", { length: 255 }).notNull(), + status: varchar("status", { length: 50 }).notNull().default("ACTIVE"), + + // 선택적 문서 정보 + vendorDocNumber: varchar("vendor_doc_number", { length: 100 }), + issuedDate: date("issued_date"), + + // 직접 참조로 JOIN 최소화 + projectId: integer("project_id") + .notNull() + .references(() => projects.id, { onDelete: "cascade" }), + vendorId: integer("vendor_id").notNull(), // vendors 테이블의 vendor_id 직접 저장 + contractId: integer("contract_id").notNull(), + // 메타 정보 + createdAt: timestamp("created_at").defaultNow().notNull(), + updatedAt: timestamp("updated_at").defaultNow().notNull(), + }, + (table) => { + return { + // 프로젝트 내에서 문서 번호 유니크 + uniqueProjectDoc: uniqueIndex("unique_project_doc").on( + table.projectId, + table.docNumber, + table.status + ), + + // 벤더 문서 번호 유니크 (있는 경우) + uniqueProjectVendorDoc: uniqueIndex("unique_project_vendor_doc").on( + table.projectId, + table.vendorDocNumber + ).where(sql`${table.vendorDocNumber} IS NOT NULL`), + + // 검색용 인덱스 + vendorIdIndex: index("stage_doc_vendor_id_idx").on(table.vendorId), + statusIndex: index("stage_doc_status_idx").on(table.status), + } + } +) + +// 🎯 간소화된 스테이지 전용 뷰 +export const stageDocumentsView = pgView("stage_documents_view", { + // 기본 문서 정보 + documentId: integer("document_id").notNull(), + docNumber: varchar("doc_number", { length: 100 }).notNull(), + vendorDocNumber: varchar("vendor_doc_number", { length: 100 }), + title: varchar("title", { length: 255 }).notNull(), + status: varchar("status", { length: 50 }).notNull(), + issuedDate: date("issued_date"), + + // 프로젝트 및 벤더 정보 (직접 참조로 간소화) + projectId: integer("project_id").notNull(), + contractId: integer("contract_id").notNull(), + projectCode: varchar("project_code", { length: 50 }), + vendorId: integer("vendor_id").notNull(), + vendorName: varchar("vendor_name", { length: 255 }), + vendorCode: varchar("vendor_code", { length: 50 }), + + // 현재 스테이지 정보 (가장 우선순위가 높은 미완료 스테이지) + currentStageId: integer("current_stage_id"), + currentStageName: varchar("current_stage_name", { length: 100 }), + currentStageStatus: varchar("current_stage_status", { length: 50 }), + currentStageOrder: integer("current_stage_order"), + currentStagePlanDate: date("current_stage_plan_date"), + currentStageActualDate: date("current_stage_actual_date"), + currentStageAssigneeName: varchar("current_stage_assignee_name", { length: 100 }), + currentStagePriority: varchar("current_stage_priority", { length: 20 }), + + // 계산 필드 + daysUntilDue: integer("days_until_due"), + isOverdue: boolean("is_overdue"), + daysDifference: integer("days_difference"), + + // 전체 진행률 + totalStages: integer("total_stages"), + completedStages: integer("completed_stages"), + progressPercentage: integer("progress_percentage"), + + // 전체 스테이지 목록 (리비전 및 첨부파일 제외) + allStages: jsonb("all_stages").$type<Array<{ + id: number; + stageName: string; + stageStatus: string; + stageOrder: number; + planDate: string | null; + actualDate: string | null; + assigneeName: string | null; + priority: string; + description: string | null; + notes: string | null; + reminderDays: number | null; + }>>(), + + // 메타 정보 + createdAt: timestamp("created_at", { withTimezone: true }).notNull(), + updatedAt: timestamp("updated_at", { withTimezone: true }).notNull(), +}).as(sql` + WITH document_stats AS ( + SELECT + sd.id as document_id, + COUNT(ist.id) as total_stages, + COUNT(CASE WHEN ist.stage_status IN ('COMPLETED', 'APPROVED') THEN 1 END) as completed_stages, + CASE + WHEN COUNT(ist.id) > 0 + THEN ROUND((COUNT(CASE WHEN ist.stage_status IN ('COMPLETED', 'APPROVED') THEN 1 END) * 100.0) / COUNT(ist.id)) + ELSE 0 + END as progress_percentage + FROM stage_documents sd + LEFT JOIN stage_issue_stages ist ON sd.id = ist.document_id + GROUP BY sd.id + ), + current_stage_info AS ( + SELECT DISTINCT ON (document_id) + document_id, + id as current_stage_id, + stage_name as current_stage_name, + stage_status as current_stage_status, + stage_order as current_stage_order, + plan_date as current_stage_plan_date, + actual_date as current_stage_actual_date, + assignee_name as current_stage_assignee_name, + priority as current_stage_priority, + CASE + WHEN actual_date IS NULL AND plan_date IS NOT NULL + THEN plan_date - CURRENT_DATE + ELSE NULL + END as days_until_due, + CASE + WHEN actual_date IS NULL AND plan_date < CURRENT_DATE + THEN true + WHEN actual_date IS NOT NULL AND actual_date > plan_date + THEN true + ELSE false + END as is_overdue, + CASE + WHEN actual_date IS NOT NULL AND plan_date IS NOT NULL + THEN actual_date - plan_date + ELSE NULL + END as days_difference + FROM stage_issue_stages + WHERE stage_status NOT IN ('COMPLETED', 'APPROVED') + ORDER BY document_id, stage_order ASC, priority DESC + ), + -- 문서별 스테이지 집계 (리비전 제외) + stage_aggregation AS ( + SELECT + ist.document_id, + json_agg( + json_build_object( + 'id', ist.id, + 'stageName', ist.stage_name, + 'stageStatus', ist.stage_status, + 'stageOrder', ist.stage_order, + 'planDate', ist.plan_date, + 'actualDate', ist.actual_date, + 'assigneeName', ist.assignee_name, + 'priority', ist.priority, + 'description', ist.description, + 'notes', ist.notes, + 'reminderDays', ist.reminder_days + ) ORDER BY ist.stage_order + ) as all_stages + FROM stage_issue_stages ist + GROUP BY ist.document_id + ) + + SELECT + sd.id as document_id, + sd.doc_number, + sd.vendor_doc_number, + sd.title, + sd.status, + sd.issued_date, + + -- 프로젝트 및 벤더 정보 (직접 참조로 간소화) + sd.project_id, + sd.contract_id, + p.code as project_code, + sd.vendor_id, + v.vendor_name, + v.vendor_code, + + -- 현재 스테이지 정보 + csi.current_stage_id, + csi.current_stage_name, + csi.current_stage_status, + csi.current_stage_order, + csi.current_stage_plan_date, + csi.current_stage_actual_date, + csi.current_stage_assignee_name, + csi.current_stage_priority, + + -- 계산 필드 + csi.days_until_due, + csi.is_overdue, + csi.days_difference, + + -- 진행률 정보 + ds.total_stages, + ds.completed_stages, + ds.progress_percentage, + + -- 전체 스테이지 (리비전 제외) + COALESCE(sa.all_stages, '[]'::json) as all_stages, + + -- 메타 정보 + sd.created_at, + sd.updated_at + + FROM stage_documents sd + -- 간소화된 JOIN (vendors는 vendor_id로 직접 조인) + LEFT JOIN projects p ON sd.project_id = p.id + LEFT JOIN vendors v ON sd.vendor_id = v.id + + -- 스테이지 관련 정보 JOIN + LEFT JOIN document_stats ds ON sd.id = ds.document_id + LEFT JOIN current_stage_info csi ON sd.id = csi.document_id + LEFT JOIN stage_aggregation sa ON sd.id = sa.document_id + + ORDER BY sd.created_at DESC +`); + + +// 🎯 issue_stages 테이블도 stage_documents를 참조하도록 수정 +export const stageIssueStages = pgTable( + "stage_issue_stages", // 또는 기존 issue_stages 테이블을 수정 + { + id: integer("id").primaryKey().generatedAlwaysAsIdentity(), + documentId: integer("document_id") + .notNull() + .references(() => stageDocuments.id, { onDelete: "cascade" }), + stageName: varchar("stage_name", { length: 100 }).notNull(), + + // 기존 필드들 + planDate: date("plan_date"), + actualDate: date("actual_date"), + + // 스케줄 관리 필드들 + stageStatus: varchar("stage_status", { length: 50 }) + .notNull() + .default("PLANNED"), // PLANNED, IN_PROGRESS, SUBMITTED, APPROVED, REJECTED, COMPLETED + stageOrder: integer("stage_order").default(0), + priority: varchar("priority", { length: 20 }).default("MEDIUM"), // HIGH, MEDIUM, LOW + + // 담당자 정보 + assigneeId: integer("assignee_id"), + assigneeName: varchar("assignee_name", { length: 100 }), + + // 알림 및 추가 정보 + reminderDays: integer("reminder_days").default(3), + description: varchar("description", { length: 500 }), + notes: varchar("notes", { length: 1000 }), + + createdAt: timestamp("created_at").defaultNow().notNull(), + updatedAt: timestamp("updated_at").defaultNow().notNull(), + }, + (table) => { + return { + uniqueDocumentStage: uniqueIndex("unique_stage_document_stage").on( + table.documentId, + table.stageName + ), + documentStageOrder: uniqueIndex("stage_document_stage_order").on( + table.documentId, + table.stageOrder + ), + } + } +); + +// 타입 추출 +export type StageDocument = typeof stageDocuments.$inferSelect +export type StageDocumentInsert = typeof stageDocuments.$inferInsert +export type StageDocumentsView = typeof stageDocumentsView.$inferSelect +export type StageIssueStage = typeof stageIssueStages.$inferSelect +export type StageIssueStageInsert = typeof stageIssueStages.$inferInsert diff --git a/db/schema/vendorRegistrations.ts b/db/schema/vendorRegistrations.ts new file mode 100644 index 00000000..308d664f --- /dev/null +++ b/db/schema/vendorRegistrations.ts @@ -0,0 +1,88 @@ +import { relations } from "drizzle-orm";
+import {
+ date,
+ integer,
+ pgTable,
+ serial,
+ text,
+ timestamp,
+ varchar,
+ decimal,
+} from "drizzle-orm/pg-core";
+import { vendors } from "./vendors";
+
+// 정규업체 등록 관리 테이블
+export const vendorRegularRegistrations = pgTable("vendor_regular_registrations", {
+ id: serial("id").primaryKey(),
+ vendorId: integer("vendor_id").notNull().references(() => vendors.id),
+ status: varchar("status", { length: 50 }).notNull().default("audit_pass"), // audit_pass, cp_submitted, cp_review, cp_finished, approval_ready, in_review, pending_approval
+ potentialCode: varchar("potential_code", { length: 20 }), // 잠재코드
+ majorItems: text("major_items"), // 주요품목 (JSON 형태로 저장)
+ registrationRequestDate: date("registration_request_date"), // 등록요청일
+ assignedDepartment: varchar("assigned_department", { length: 100 }), // 담당부서
+ assignedDepartmentCode: varchar("assigned_department_code", { length: 20 }), // 담당부서코드
+ assignedUser: varchar("assigned_user", { length: 100 }), // 담당자
+ assignedUserCode: varchar("assigned_user_code", { length: 20 }), // 담당자코드
+ remarks: text("remarks"), // 비고
+ createdAt: timestamp("created_at").defaultNow(),
+ updatedAt: timestamp("updated_at").defaultNow(),
+});
+
+// 업무담당자 정보 테이블
+export const vendorBusinessContacts = pgTable("vendor_business_contacts", {
+ id: serial("id").primaryKey(),
+ vendorId: integer("vendor_id").notNull().references(() => vendors.id),
+ contactType: varchar("contact_type", { length: 20 }).notNull(), // sales, design, delivery, quality, tax_invoice
+ contactName: varchar("contact_name", { length: 100 }).notNull(), // 담당자명
+ position: varchar("position", { length: 50 }).notNull(), // 직급
+ department: varchar("department", { length: 100 }).notNull(), // 부서
+ responsibility: varchar("responsibility", { length: 200 }).notNull(), // 담당업무
+ email: varchar("email", { length: 255 }).notNull(), // Email
+ createdAt: timestamp("created_at").defaultNow(),
+ updatedAt: timestamp("updated_at").defaultNow(),
+});
+
+// 추가정보 테이블
+export const vendorAdditionalInfo = pgTable("vendor_additional_info", {
+ id: serial("id").primaryKey(),
+ vendorId: integer("vendor_id").notNull().references(() => vendors.id),
+ businessType: varchar("business_type", { length: 50 }), // 사업유형
+ industryType: varchar("industry_type", { length: 50 }), // 산업유형
+ companySize: varchar("company_size", { length: 20 }), // 기업규모
+ revenue: decimal("revenue", { precision: 15, scale: 2 }), // 매출액
+ factoryEstablishedDate: date("factory_established_date"), // 공장설립일
+ preferredContractTerms: text("preferred_contract_terms"), // 선호계약조건
+ createdAt: timestamp("created_at").defaultNow(),
+ updatedAt: timestamp("updated_at").defaultNow(),
+});
+
+// Relations
+export const vendorRegularRegistrationsRelations = relations(
+ vendorRegularRegistrations,
+ ({ one }) => ({
+ vendor: one(vendors, {
+ fields: [vendorRegularRegistrations.vendorId],
+ references: [vendors.id],
+ }),
+ })
+);
+
+export const vendorBusinessContactsRelations = relations(
+ vendorBusinessContacts,
+ ({ one }) => ({
+ vendor: one(vendors, {
+ fields: [vendorBusinessContacts.vendorId],
+ references: [vendors.id],
+ }),
+ })
+);
+
+export const vendorAdditionalInfoRelations = relations(
+ vendorAdditionalInfo,
+ ({ one }) => ({
+ vendor: one(vendors, {
+ fields: [vendorAdditionalInfo.vendorId],
+ references: [vendors.id],
+ }),
+ })
+);
|