(대표님) 입찰, EDP 변경사항 대응, spreadJS 오류 수정, 벤더실사 수정

1 files changed, 226 insertions, 0 deletions

diff --git a/db/migrations/0262_faulty_weapon_omega.sql b/db/migrations/0262_faulty_weapon_omega.sql
new file mode 100644
index 00000000..b893b4f0
--- /dev/null
+++ b/db/migrations/0262_faulty_weapon_omega.sql
@@ -0,0 +1,226 @@
+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,
+            'usage', r.usage,
+            '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.drawing_kind,
+    d.vendor_doc_number, -- ✅ 벤더 문서 번호 추가
+    d.title,
+    d.pic,
+    d.status,
+    d.issued_date,
+    d.contract_id,
+
+    d.c_gbn,
+    d.d_gbn,
+    d.degree_gbn,
+    d.dept_gbn,
+    d.s_gbn,
+    d.j_gbn,
+
+
+    
+    -- ✅ 프로젝트 및 벤더 정보 추가
+    c.project_id as project_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,
+    
+    -- 최신 리비전 정보
+    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