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
|
DROP VIEW "public"."stage_documents_view";--> statement-breakpoint
CREATE VIEW "public"."stage_documents_view" AS (
WITH document_stats AS (
SELECT
sd.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 stage_documents sd
LEFT JOIN stage_issue_stages ist ON sd.id = ist.document_id
GROUP BY sd.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
),
-- 문서별 스테이지 집계 (리비전 제외)
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,
'description', ist.description,
'notes', ist.notes,
'reminderDays', ist.reminder_days
) ORDER BY ist.stage_order
) as all_stages
FROM stage_issue_stages ist
GROUP BY ist.document_id
)
SELECT
sd.id as document_id,
sd.doc_number,
sd.vendor_doc_number,
sd.title,
sd.status,
sd.issued_date,
-- 프로젝트 및 벤더 정보 (직접 참조로 간소화)
sd.project_id,
sd.contract_id,
p.code as project_code,
sd.vendor_id,
v.vendor_name,
v.vendor_code,
-- 현재 스테이지 정보
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,
-- 전체 스테이지 (리비전 제외)
COALESCE(sa.all_stages, '[]'::json) as all_stages,
-- 메타 정보
sd.created_at,
sd.updated_at
FROM stage_documents sd
-- 간소화된 JOIN (vendors는 vendor_id로 직접 조인)
LEFT JOIN projects p ON sd.project_id = p.id
LEFT JOIN vendors v ON sd.vendor_id = v.id
-- 스테이지 관련 정보 JOIN
LEFT JOIN document_stats ds ON sd.id = ds.document_id
LEFT JOIN current_stage_info csi ON sd.id = csi.document_id
LEFT JOIN stage_aggregation sa ON sd.id = sa.document_id
ORDER BY sd.created_at DESC
);
|
