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
|
CREATE VIEW "public"."document_stages_only_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
),
-- 문서별 스테이지 집계 (리비전 제외)
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 issue_stages ist
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,
-- 프로젝트 및 벤더 정보
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,
-- 전체 스테이지 (리비전 제외)
COALESCE(sa.all_stages, '[]'::json) as all_stages,
-- 메타 정보
d.created_at,
d.updated_at
FROM documents d
-- 프로젝트 및 벤더 정보 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
-- 스테이지 관련 정보 JOIN
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 stage_aggregation sa ON d.id = sa.document_id
ORDER BY d.created_at DESC
);
|
