diff options
Diffstat (limited to 'db/migrations/0292_aromatic_mandrill.sql')
| -rw-r--r-- | db/migrations/0292_aromatic_mandrill.sql | 231 |
1 files changed, 231 insertions, 0 deletions
diff --git a/db/migrations/0292_aromatic_mandrill.sql b/db/migrations/0292_aromatic_mandrill.sql new file mode 100644 index 00000000..8effebfa --- /dev/null +++ b/db/migrations/0292_aromatic_mandrill.sql @@ -0,0 +1,231 @@ +DROP VIEW "public"."simplified_documents_view";--> statement-breakpoint +CREATE VIEW "public"."simplified_documents_view" AS ( + WITH + -- 리비전별 첨부파일 집계 + 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, + 'dolceFilePath', da.dolce_file_path, + '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, + 'usage', r.usage, + 'usageType', r.usage_type, + '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', COALESCE(ra.attachments, '[]'::json) + ) 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', COALESCE(sr.revisions, '[]'::json) + ) 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 + ), + + -- 첫 번째 스테이지 정보 (drawingKind에 따라 다른 조건) + first_stage_info AS ( + SELECT + document_id, + first_stage_id, + first_stage_name, + first_stage_plan_date, + first_stage_actual_date + FROM ( + SELECT + ist.document_id, + ist.id as first_stage_id, + ist.stage_name as first_stage_name, + ist.plan_date as first_stage_plan_date, + ist.actual_date as first_stage_actual_date, + ROW_NUMBER() OVER (PARTITION BY ist.document_id ORDER BY ist.stage_order ASC) as rn + FROM issue_stages ist + JOIN documents d ON ist.document_id = d.id + WHERE + (d.drawing_kind = 'B4' AND LOWER(ist.stage_name) LIKE '%pre%') OR + (d.drawing_kind = 'B3' AND LOWER(ist.stage_name) LIKE '%approval%') OR + (d.drawing_kind = 'B5' AND LOWER(ist.stage_name) LIKE '%first%') + ) ranked + WHERE rn = 1 + ), + + -- 두 번째 스테이지 정보 (drawingKind에 따라 다른 조건) + second_stage_info AS ( + SELECT + document_id, + second_stage_id, + second_stage_name, + second_stage_plan_date, + second_stage_actual_date + FROM ( + SELECT + ist.document_id, + ist.id as second_stage_id, + ist.stage_name as second_stage_name, + ist.plan_date as second_stage_plan_date, + ist.actual_date as second_stage_actual_date, + ROW_NUMBER() OVER (PARTITION BY ist.document_id ORDER BY ist.stage_order ASC) as rn + FROM issue_stages ist + JOIN documents d ON ist.document_id = d.id + WHERE + (d.drawing_kind = 'B4' AND LOWER(ist.stage_name) LIKE '%work%') OR + (d.drawing_kind = 'B3' AND LOWER(ist.stage_name) LIKE '%work%') OR + (d.drawing_kind = 'B5' AND LOWER(ist.stage_name) LIKE '%second%') + ) ranked + WHERE rn = 1 + ), + + -- 첨부파일 수 집계 + 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.project_id, + d.vendor_id, + d.doc_number, + d.drawing_kind, + d.drawing_move_gbn, + d.discipline, + d.vendor_doc_number, + d.title, + d.pic, + d.status, + d.issued_date, + d.contract_id, + + -- 외부 시스템 연동 정보 + d.external_document_id, + d.external_system_type, + d.external_synced_at, + + -- DOLCE 응답의 추가 정보들 + d.shi_drawing_no, + d.manager, + d.manager_enm, + d.manager_no, + d.register_group, + d.register_group_id, + + -- 생성자 정보 + d.create_user_no, + d.create_user_id, + d.create_user_enm, + + -- 프로젝트 및 벤더 정보 + p.code as project_code, + v.vendor_name, + v.vendor_code, + + -- B4 전용 필드들 + d.c_gbn, + d.d_gbn, + d.degree_gbn, + d.dept_gbn, + d.s_gbn, + d.j_gbn, + + -- 첫 번째 스테이지 정보 + fsi.first_stage_id, + fsi.first_stage_name, + fsi.first_stage_plan_date, + fsi.first_stage_actual_date, + + -- 두 번째 스테이지 정보 + ssi.second_stage_id, + ssi.second_stage_name, + ssi.second_stage_plan_date, + ssi.second_stage_actual_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 + -- projects, vendors 테이블 JOIN (projectId가 이제 documents에 직접 있음) + LEFT JOIN projects p ON d.project_id = p.id AND p.type = 'ship' + LEFT JOIN contracts c ON d.contract_id = c.id + LEFT JOIN vendors v ON c.vendor_id = v.id + + -- 스테이지 정보 JOIN + LEFT JOIN first_stage_info fsi ON d.id = fsi.document_id + LEFT JOIN second_stage_info ssi ON d.id = ssi.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 |