1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
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
);
|
