diff options
Diffstat (limited to 'db/migrations/0282_zippy_king_cobra.sql')
| -rw-r--r-- | db/migrations/0282_zippy_king_cobra.sql | 127 |
1 files changed, 127 insertions, 0 deletions
diff --git a/db/migrations/0282_zippy_king_cobra.sql b/db/migrations/0282_zippy_king_cobra.sql new file mode 100644 index 00000000..6631e8ea --- /dev/null +++ b/db/migrations/0282_zippy_king_cobra.sql @@ -0,0 +1,127 @@ +DROP VIEW "public"."stage_documents_view";--> statement-breakpoint +ALTER TABLE "stage_documents" ADD COLUMN "contract_id" integer NOT NULL;--> statement-breakpoint +CREATE VIEW "public"."stage_documents_view" AS ( + 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 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 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 + 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 +);
\ No newline at end of file |