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
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
|
DROP VIEW "public"."simplified_documents_view";--> statement-breakpoint
ALTER TABLE "revisions" ADD COLUMN "serial_no" varchar(50);--> 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,
'serialNo', r.serial_no,
'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
);
|
