diff options
Diffstat (limited to 'public/0107.sql')
| -rw-r--r-- | public/0107.sql | 213 |
1 files changed, 213 insertions, 0 deletions
diff --git a/public/0107.sql b/public/0107.sql new file mode 100644 index 00000000..46daf36b --- /dev/null +++ b/public/0107.sql @@ -0,0 +1,213 @@ +DROP VIEW "public"."enhanced_documents_view";--> statement-breakpoint +CREATE VIEW "public"."enhanced_documents_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 + ), + latest_revision_info AS ( + SELECT DISTINCT ON (ist.document_id) + ist.document_id, + r.id as latest_revision_id, + r.revision as latest_revision, + r.revision_status as latest_revision_status, + r.uploader_name as latest_revision_uploader_name, + r.submitted_date as latest_submitted_date + FROM revisions r + JOIN issue_stages ist ON r.issue_stage_id = ist.id + ORDER BY ist.document_id, r.created_at DESC + ), + -- 리비전별 첨부파일 집계 + revision_attachments AS ( + SELECT + r.id as revision_id, + COALESCE( + json_agg( + json_build_object( + 'id', da.id, + 'revisionId', da.revision_id, + 'fileName', da.file_name, + 'filePath', da.file_path, + 'fileSize', da.file_size, + 'fileType', da.file_type, + 'createdAt', da.created_at, + 'updatedAt', da.updated_at + ) ORDER BY da.created_at + ) FILTER (WHERE da.id IS NOT NULL), + '[]'::json + ) as attachments + FROM revisions r + LEFT JOIN document_attachments da ON r.id = da.revision_id + GROUP BY r.id + ), + -- 스테이지별 리비전 집계 (첨부파일 포함) + stage_revisions AS ( + SELECT + ist.id as stage_id, + COALESCE( + json_agg( + json_build_object( + 'id', r.id, + 'issueStageId', r.issue_stage_id, + 'revision', r.revision, + 'uploaderType', r.uploader_type, + 'uploaderId', r.uploader_id, + 'uploaderName', r.uploader_name, + 'comment', r.comment, + 'revisionStatus', r.revision_status, + 'submittedDate', r.submitted_date, + 'uploadedAt', r.uploaded_at, + 'approvedDate', r.approved_date, + 'reviewStartDate', r.review_start_date, + 'rejectedDate', r.rejected_date, + 'reviewerId', r.reviewer_id, + 'reviewerName', r.reviewer_name, + 'reviewComments', r.review_comments, + 'createdAt', r.created_at, + 'updatedAt', r.updated_at, + 'attachments', ra.attachments + ) ORDER BY r.created_at + ) FILTER (WHERE r.id IS NOT NULL), + '[]'::json + ) as revisions + FROM issue_stages ist + LEFT JOIN revisions r ON ist.id = r.issue_stage_id + LEFT JOIN revision_attachments ra ON r.id = ra.revision_id + GROUP BY ist.id + ), + -- 문서별 스테이지 집계 (리비전 포함) + 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, + 'revisions', sr.revisions + ) ORDER BY ist.stage_order + ) as all_stages + FROM issue_stages ist + LEFT JOIN stage_revisions sr ON ist.id = sr.stage_id + GROUP BY ist.document_id + ), + attachment_counts AS ( + SELECT + ist.document_id, + COUNT(da.id) as attachment_count + FROM issue_stages ist + LEFT JOIN revisions r ON ist.id = r.issue_stage_id + LEFT JOIN document_attachments da ON r.id = da.revision_id + GROUP BY ist.document_id + ) + + SELECT + d.id as document_id, + d.doc_number, + 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, + + -- 현재 스테이지 정보 + 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, + + -- 최신 리비전 정보 + lri.latest_revision_id, + lri.latest_revision, + lri.latest_revision_status, + lri.latest_revision_uploader_name, + lri.latest_submitted_date, + + -- 전체 스테이지 (리비전 및 첨부파일 포함) + COALESCE(sa.all_stages, '[]'::json) as all_stages, + + -- 기타 + COALESCE(ac.attachment_count, 0) as attachment_count, + d.created_at, + d.updated_at + + FROM documents d + -- ✅ contracts, projects, vendors 테이블 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 + + 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 latest_revision_info lri ON d.id = lri.document_id + LEFT JOIN stage_aggregation sa ON d.id = sa.document_id + LEFT JOIN attachment_counts ac ON d.id = ac.document_id + + ORDER BY d.created_at DESC +);
\ No newline at end of file |