diff options
Diffstat (limited to 'db/migrations/0243_serious_korg.sql')
| -rw-r--r-- | db/migrations/0243_serious_korg.sql | 127 |
1 files changed, 127 insertions, 0 deletions
diff --git a/db/migrations/0243_serious_korg.sql b/db/migrations/0243_serious_korg.sql new file mode 100644 index 00000000..2833aa61 --- /dev/null +++ b/db/migrations/0243_serious_korg.sql @@ -0,0 +1,127 @@ +CREATE VIEW "public"."document_stages_only_view" AS ( + 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 +);
\ No newline at end of file |