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
|
DROP VIEW "public"."document_stages_view";--> statement-breakpoint
DROP VIEW "public"."vendor_documents_view";--> statement-breakpoint
ALTER TABLE "issue_stages" ADD COLUMN "stage_status" varchar(50) DEFAULT 'PLANNED' NOT NULL;--> statement-breakpoint
ALTER TABLE "issue_stages" ADD COLUMN "stage_order" integer DEFAULT 0;--> statement-breakpoint
ALTER TABLE "issue_stages" ADD COLUMN "priority" varchar(20) DEFAULT 'MEDIUM';--> statement-breakpoint
ALTER TABLE "issue_stages" ADD COLUMN "assignee_id" integer;--> statement-breakpoint
ALTER TABLE "issue_stages" ADD COLUMN "assignee_name" varchar(100);--> statement-breakpoint
ALTER TABLE "issue_stages" ADD COLUMN "reminder_days" integer DEFAULT 3;--> statement-breakpoint
ALTER TABLE "issue_stages" ADD COLUMN "description" varchar(500);--> statement-breakpoint
ALTER TABLE "issue_stages" ADD COLUMN "notes" varchar(1000);--> statement-breakpoint
ALTER TABLE "revisions" ADD COLUMN "revision_status" varchar(50) DEFAULT 'SUBMITTED' NOT NULL;--> statement-breakpoint
ALTER TABLE "revisions" ADD COLUMN "submitted_date" date;--> statement-breakpoint
ALTER TABLE "revisions" ADD COLUMN "review_start_date" date;--> statement-breakpoint
ALTER TABLE "revisions" ADD COLUMN "rejected_date" date;--> statement-breakpoint
ALTER TABLE "revisions" ADD COLUMN "reviewer_id" integer;--> statement-breakpoint
ALTER TABLE "revisions" ADD COLUMN "reviewer_name" varchar(100);--> statement-breakpoint
ALTER TABLE "revisions" ADD COLUMN "review_comments" varchar(1000);--> statement-breakpoint
CREATE UNIQUE INDEX "document_stage_order" ON "issue_stages" USING btree ("document_id","stage_order");--> statement-breakpoint
ALTER TABLE "revisions" DROP COLUMN "status";--> 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
),
stage_aggregation AS (
SELECT
document_id,
json_agg(
json_build_object(
'id', id,
'stageName', stage_name,
'stageStatus', stage_status,
'stageOrder', stage_order,
'planDate', plan_date,
'actualDate', actual_date,
'assigneeName', assignee_name,
'priority', priority
) ORDER BY stage_order
) as all_stages
FROM issue_stages
GROUP BY 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.title,
d.pic,
d.status,
d.issued_date,
d.contract_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
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
);--> statement-breakpoint
CREATE VIEW "public"."document_stages_view" AS (
SELECT
d.id AS document_id,
d.doc_number,
d.title,
d.status,
d.issued_date,
d.contract_id,
(SELECT COUNT(*) FROM issue_stages WHERE document_id = d.id) AS stage_count,
COALESCE(
(SELECT json_agg(i.stage_name) FROM issue_stages i WHERE i.document_id = d.id),
'[]'
) AS stage_list,
d.created_at,
d.updated_at
FROM documents d
);--> statement-breakpoint
CREATE VIEW "public"."vendor_documents_view" AS (
SELECT
d.id,
d.doc_number,
d.title,
d.pic,
d.status,
d.issued_date,
d.contract_id,
(SELECT id FROM issue_stages WHERE document_id = d.id ORDER BY created_at DESC LIMIT 1) AS latest_stage_id,
(SELECT stage_name FROM issue_stages WHERE document_id = d.id ORDER BY created_at DESC LIMIT 1) AS latest_stage_name,
(SELECT plan_date FROM issue_stages WHERE document_id = d.id ORDER BY created_at DESC LIMIT 1) AS latest_stage_plan_date,
(SELECT actual_date FROM issue_stages WHERE document_id = d.id ORDER BY created_at DESC LIMIT 1) AS latest_stage_actual_date,
(SELECT r.id FROM revisions r JOIN issue_stages i ON r.issue_stage_id = i.id WHERE i.document_id = d.id ORDER BY r.created_at DESC LIMIT 1) AS latest_revision_id,
(SELECT r.revision FROM revisions r JOIN issue_stages i ON r.issue_stage_id = i.id WHERE i.document_id = d.id ORDER BY r.created_at DESC LIMIT 1) AS latest_revision,
(SELECT r.uploader_type FROM revisions r JOIN issue_stages i ON r.issue_stage_id = i.id WHERE i.document_id = d.id ORDER BY r.created_at DESC LIMIT 1) AS latest_revision_uploader_type,
(SELECT r.uploader_name FROM revisions r JOIN issue_stages i ON r.issue_stage_id = i.id WHERE i.document_id = d.id ORDER BY r.created_at DESC LIMIT 1) AS latest_revision_uploader_name,
(SELECT COUNT(*) FROM document_attachments a JOIN revisions r ON a.revision_id = r.id JOIN issue_stages i ON r.issue_stage_id = i.id WHERE i.document_id = d.id) AS attachment_count,
d.created_at,
d.updated_at
FROM documents d
JOIN contracts c ON d.contract_id = c.id
);
|
