diff options
Diffstat (limited to 'db/schema')
| -rw-r--r-- | db/schema/gtc.ts | 4 | ||||
| -rw-r--r-- | db/schema/vendorDocu.ts | 190 |
2 files changed, 192 insertions, 2 deletions
diff --git a/db/schema/gtc.ts b/db/schema/gtc.ts index a217f681..610804a3 100644 --- a/db/schema/gtc.ts +++ b/db/schema/gtc.ts @@ -99,8 +99,8 @@ import { vendors } from "./vendors" export type GtcDocumentWithRelations = GtcDocument & { project?: { id: number - code: string - name: string + projectCode: string + projectName: string } createdBy?: { id: number diff --git a/db/schema/vendorDocu.ts b/db/schema/vendorDocu.ts index 80eb5c5d..8ab86160 100644 --- a/db/schema/vendorDocu.ts +++ b/db/schema/vendorDocu.ts @@ -1042,3 +1042,193 @@ export const simplifiedDocumentsView = pgView("simplified_documents_view", { // 타입 추출 export type SimplifiedDocumentsView = typeof simplifiedDocumentsView.$inferSelect + + +export const documentStagesOnlyView = pgView("document_stages_only_view", { + // 기본 문서 정보 + documentId: integer("document_id").notNull(), + docNumber: varchar("doc_number", { length: 100 }).notNull(), + drawingKind: varchar("drawing_kind", { length: 50 }), + vendorDocNumber: varchar("vendor_doc_number", { length: 100 }), + title: varchar("title", { length: 255 }).notNull(), + pic: varchar("pic", { length: 50 }), + status: varchar("status", { length: 50 }).notNull(), + issuedDate: date("issued_date"), + contractId: integer("contract_id").notNull(), + + + // 프로젝트 및 벤더 정보 + projectCode: varchar("project_code", { length: 50 }), + vendorName: varchar("vendor_name", { length: 255 }), + vendorCode: varchar("vendor_code", { length: 50 }), + vendorId: varchar("vendor_id", { 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 + d.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 documents d + LEFT JOIN issue_stages ist ON d.id = ist.document_id + GROUP BY d.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 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 issue_stages ist + GROUP BY ist.document_id + ) + + SELECT + d.id as document_id, + d.doc_number, + d.drawing_kind, + d.vendor_doc_number, + d.title, + d.pic, + d.status, + d.issued_date, + d.contract_id, + + -- 프로젝트 및 벤더 정보 + p.code as project_code, + v.vendor_name as vendor_name, + v.vendor_code as vendor_code, + c.vendor_id as vendor_id, + + -- 현재 스테이지 정보 + 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, + + -- 메타 정보 + d.created_at, + d.updated_at + + FROM documents d + -- 프로젝트 및 벤더 정보 JOIN + LEFT JOIN contracts c ON d.contract_id = c.id + LEFT JOIN projects p ON c.project_id = p.id + LEFT JOIN vendors v ON c.vendor_id = v.id + + -- 스테이지 관련 정보 JOIN + LEFT JOIN document_stats ds ON d.id = ds.document_id + LEFT JOIN current_stage_info csi ON d.id = csi.document_id + LEFT JOIN stage_aggregation sa ON d.id = sa.document_id + + ORDER BY d.created_at DESC +`); + +// 타입 추출 +export type DocumentStagesOnlyView = typeof documentStagesOnlyView.$inferSelect |