diff options
Diffstat (limited to 'public/0107.sql')
| -rw-r--r-- | public/0107.sql | 213 |
1 files changed, 0 insertions, 213 deletions
diff --git a/public/0107.sql b/public/0107.sql deleted file mode 100644 index 46daf36b..00000000 --- a/public/0107.sql +++ /dev/null @@ -1,213 +0,0 @@ -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 |