diff options
Diffstat (limited to 'db/migrations/0384_funny_spitfire.sql')
| -rw-r--r-- | db/migrations/0384_funny_spitfire.sql | 160 |
1 files changed, 160 insertions, 0 deletions
diff --git a/db/migrations/0384_funny_spitfire.sql b/db/migrations/0384_funny_spitfire.sql new file mode 100644 index 00000000..3451cb60 --- /dev/null +++ b/db/migrations/0384_funny_spitfire.sql @@ -0,0 +1,160 @@ +DROP VIEW "public"."stage_submission_view";--> statement-breakpoint +ALTER TABLE "stage_submissions" ADD COLUMN "revision_code" varchar(50) NOT NULL;--> statement-breakpoint +CREATE VIEW "public"."stage_submission_view" AS ( + WITH eligible_documents AS ( + -- buyerSystemStatus가 '승인(DC)'인 문서만 선택 + SELECT + sd.id as document_id, + sd.doc_number, + sd.vendor_doc_number, + sd.title as document_title, + sd.project_id, + sd.vendor_id, + p.code as project_code, + v.vendor_name, + v.vendor_code + FROM stage_documents sd + LEFT JOIN projects p ON sd.project_id = p.id + LEFT JOIN vendors v ON sd.vendor_id = v.id + WHERE sd.buyer_system_status = '승인(DC)' + AND sd.status = 'ACTIVE' + ), + + submission_stats AS ( + SELECT + ss.stage_id, + COUNT(*) as total_submissions, + COUNT(CASE WHEN ss.review_status = 'APPROVED' THEN 1 END) as approved_submissions, + COUNT(CASE WHEN ss.review_status = 'REJECTED' THEN 1 END) as rejected_submissions, + COUNT(CASE WHEN ss.review_status = 'PENDING' OR ss.review_status IS NULL THEN 1 END) as pending_submissions, + SUM(ss.total_files) as total_files, + SUM(ss.total_file_size) as total_file_size, + MAX(ss.revision_number) as latest_revision_number + FROM stage_submissions ss + GROUP BY ss.stage_id + ), + + latest_submission AS ( + SELECT DISTINCT ON (stage_id) + stage_id, + id as submission_id, + revision_number, + revision_code, -- ⭐ 추가 + submission_status, + submitted_at, + submitted_by, + review_status, + sync_status, + buyer_system_status, + last_synced_at, + total_files_to_sync, + synced_files_count, + CASE + WHEN total_files_to_sync > 0 + THEN ROUND((synced_files_count * 100.0) / total_files_to_sync) + ELSE 0 + END as sync_progress + FROM stage_submissions + ORDER BY stage_id, revision_number DESC + ), + + submission_history_agg AS ( + SELECT + ss.stage_id, + json_agg( + json_build_object( + 'submissionId', ss.id, + 'revisionNumber', ss.revision_number, + 'revisionCode', ss.revision_code, -- ⭐ 추가 + 'status', ss.submission_status, + 'submittedAt', ss.submitted_at, + 'submittedBy', ss.submitted_by, + 'reviewStatus', ss.review_status, + 'syncStatus', ss.sync_status, + 'fileCount', ss.total_files + ) ORDER BY ss.revision_number DESC + ) as submission_history + FROM stage_submissions ss + GROUP BY ss.stage_id + ) + + SELECT + ed.document_id, + ed.doc_number, + ed.vendor_doc_number, + ed.document_title, + ed.project_id, + ed.project_code, + ed.vendor_id, + ed.vendor_name, + ed.vendor_code, + + ist.id as stage_id, + ist.stage_name, + ist.stage_order, + ist.stage_status, + ist.plan_date as stage_plan_date, + + ls.submission_id as latest_submission_id, + ls.revision_number as latest_revision_number, + ls.revision_code as latest_revision_code, -- ⭐ 추가 + ls.submission_status as latest_submission_status, + ls.submitted_at as latest_submitted_at, + ls.submitted_by as latest_submitted_by, + ls.review_status as latest_review_status, + + -- 동기화 상태 + ls.sync_status as latest_sync_status, + ls.buyer_system_status as latest_buyer_system_status, + ls.last_synced_at, + ls.total_files_to_sync, + ls.synced_files_count, + ls.sync_progress, + + COALESCE(ss.total_submissions, 0) as total_submissions, + COALESCE(ss.approved_submissions, 0) as approved_submissions, + COALESCE(ss.rejected_submissions, 0) as rejected_submissions, + COALESCE(ss.pending_submissions, 0) as pending_submissions, + COALESCE(ss.total_files, 0) as total_files, + COALESCE(ss.total_file_size, 0) as total_file_size, + + -- 제출이 필요한지 판단 + CASE + WHEN ist.stage_status IN ('PLANNED', 'IN_PROGRESS', 'REJECTED') + AND (ls.review_status IS NULL OR ls.review_status != 'APPROVED') + THEN true + ELSE false + END as requires_submission, + + -- 동기화가 필요한지 판단 + CASE + WHEN ls.sync_status IN ('pending', 'failed') + OR ls.synced_files_count < ls.total_files_to_sync + THEN true + ELSE false + END as requires_sync, + + -- 기한 관련 + CASE + WHEN ist.plan_date < CURRENT_DATE + AND ist.stage_status NOT IN ('COMPLETED', 'APPROVED') + THEN true + ELSE false + END as is_overdue, + + CASE + WHEN ist.plan_date IS NOT NULL + THEN ist.plan_date - CURRENT_DATE + ELSE NULL + END as days_until_due, + + COALESCE(sha.submission_history, '[]'::json) as submission_history + + FROM eligible_documents ed + INNER JOIN stage_issue_stages ist ON ed.document_id = ist.document_id + LEFT JOIN submission_stats ss ON ist.id = ss.stage_id + LEFT JOIN latest_submission ls ON ist.id = ls.stage_id + LEFT JOIN submission_history_agg sha ON ist.id = sha.stage_id + + ORDER BY ed.document_id, ist.stage_order +);
\ No newline at end of file |