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
|
CREATE VIEW "public"."avl_list_summary_view" AS (
WITH latest_revisions AS (
-- 표준 AVL의 경우: 공사부문, 선종, AVL종류, H/T 구분별 최신 revision 찾기
SELECT
construction_sector,
ship_type,
avl_kind,
ht_division,
MAX(rev) as max_rev,
'standard' as avl_type,
NULL::varchar as project_code
FROM avl_list
WHERE is_template = true
GROUP BY construction_sector, ship_type, avl_kind, ht_division
UNION ALL
-- 프로젝트 AVL의 경우: 프로젝트 코드별 최신 revision 찾기
SELECT
construction_sector,
ship_type,
avl_kind,
ht_division,
MAX(rev) as max_rev,
'project' as avl_type,
project_code
FROM avl_list
WHERE is_template = false
GROUP BY project_code, construction_sector, ship_type, avl_kind, ht_division
),
latest_avl_lists AS (
-- 최신 revision의 실제 AVL 리스트 가져오기
SELECT
al.*,
CASE
WHEN al.is_template = true THEN 'standard'
ELSE 'project'
END as avl_type
FROM avl_list al
INNER JOIN latest_revisions lr ON (
(al.is_template = true AND lr.avl_type = 'standard' AND
al.construction_sector = lr.construction_sector AND
al.ship_type = lr.ship_type AND
al.avl_kind = lr.avl_kind AND
al.ht_division = lr.ht_division AND
al.rev = lr.max_rev)
OR
(al.is_template = false AND lr.avl_type = 'project' AND
al.project_code = lr.project_code AND
al.rev = lr.max_rev)
)
)
SELECT
al.id,
al.is_template,
al.construction_sector,
al.project_code,
al.ship_type,
al.avl_kind,
al.ht_division,
al.rev,
al.created_at,
al.created_by,
al.updated_at,
al.updated_by,
-- 집계 필드들
COALESCE(COUNT(DISTINCT CASE WHEN avi.package_code IS NOT NULL THEN avi.package_code END), 0)::integer as pkg_count,
COALESCE(COUNT(DISTINCT CASE WHEN avi.material_group_code IS NOT NULL THEN avi.material_group_code END), 0)::integer as material_group_count,
COALESCE(COUNT(DISTINCT CASE WHEN avi.vendor_name IS NOT NULL THEN avi.vendor_name END), 0)::integer as vendor_count,
COALESCE(COUNT(DISTINCT CASE WHEN avi.tier IS NOT NULL THEN avi.tier END), 0)::integer as tier_count,
COALESCE(SUM(CASE WHEN avi.owner_suggestion = true THEN 1 ELSE 0 END), 0)::integer as owner_suggestion_count,
COALESCE(SUM(CASE WHEN avi.shi_suggestion = true THEN 1 ELSE 0 END), 0)::integer as shi_suggestion_count
FROM latest_avl_lists al
LEFT JOIN avl_vendor_info avi ON al.id = avi.avl_list_id
GROUP BY
al.id, al.is_template, al.construction_sector, al.project_code,
al.ship_type, al.avl_kind, al.ht_division, al.rev,
al.created_at, al.created_by, al.updated_at, al.updated_by
ORDER BY
al.is_template ASC,
al.construction_sector,
al.project_code,
al.ship_type,
al.avl_kind,
al.ht_division,
al.rev DESC
);
|
