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
|
DROP VIEW "public"."vendor_response_detail";--> statement-breakpoint
CREATE VIEW "public"."vendor_response_detail" AS (
SELECT
var.id as response_id,
ba.rfq_id,
br.rfq_code,
var.rfq_type,
var.rfq_record_id,
-- 첨부파일 정보
ba.id as attachment_id,
ba.attachment_type,
ba.serial_no,
ba.description as attachment_description,
-- 벤더 정보
v.id as vendor_id,
v.vendor_code,
v.vendor_name,
v.country as vendor_country,
-- 응답 상태
var.response_status,
var.current_revision,
var.responded_revision,
-- 코멘트 (새로 추가된 필드 포함)
var.response_comment,
var.vendor_comment,
var.revision_request_comment,
-- 날짜 (새로 추가된 필드 포함)
var.requested_at,
var.responded_at,
var.revision_requested_at,
-- 발주처 최신 리비전
latest_rev.revision_no as latest_client_revision_no,
latest_rev.original_file_name as latest_client_file_name,
latest_rev.file_size as latest_client_file_size,
latest_rev.revision_comment as latest_client_revision_comment,
-- 리비전 분석
CASE
WHEN var.responded_revision = ba.current_revision THEN true
ELSE false
END as is_version_matched,
CASE
WHEN var.responded_revision IS NULL OR ba.current_revision IS NULL THEN NULL
ELSE CAST(SUBSTRING(ba.current_revision FROM '[0-9]+') AS INTEGER) -
CAST(SUBSTRING(var.responded_revision FROM '[0-9]+') AS INTEGER)
END as version_lag,
CASE
WHEN var.response_status = 'RESPONDED'
AND var.responded_revision != ba.current_revision THEN true
ELSE false
END as needs_update,
CASE
WHEN revision_count.total_revisions > 1 THEN true
ELSE false
END as has_multiple_revisions,
-- 응답 파일 정보
COALESCE(file_stats.total_files, 0) as total_response_files,
file_stats.latest_file_name as latest_response_file_name,
file_stats.latest_file_size as latest_response_file_size,
file_stats.latest_uploaded_at as latest_response_uploaded_at,
-- 효과적인 상태
CASE
WHEN var.response_status = 'NOT_RESPONDED' THEN 'NOT_RESPONDED'
WHEN var.response_status = 'WAIVED' THEN 'WAIVED'
WHEN var.response_status = 'REVISION_REQUESTED' THEN 'REVISION_REQUESTED'
WHEN var.response_status = 'RESPONDED' AND var.responded_revision = ba.current_revision THEN 'UP_TO_DATE'
WHEN var.response_status = 'RESPONDED' AND var.responded_revision != ba.current_revision THEN 'VERSION_MISMATCH'
ELSE var.response_status
END as effective_status
FROM vendor_attachment_responses var
JOIN b_rfq_attachments ba ON var.attachment_id = ba.id
JOIN b_rfqs br ON ba.rfq_id = br.id
LEFT JOIN vendors v ON var.vendor_id = v.id
LEFT JOIN b_rfq_attachment_revisions latest_rev ON ba.latest_revision_id = latest_rev.id
LEFT JOIN (
SELECT
attachment_id,
COUNT(*) as total_revisions
FROM b_rfq_attachment_revisions
GROUP BY attachment_id
) revision_count ON ba.id = revision_count.attachment_id
LEFT JOIN (
SELECT
vendor_response_id,
COUNT(*) as total_files,
MAX(original_file_name) as latest_file_name,
MAX(file_size) as latest_file_size,
MAX(uploaded_at) as latest_uploaded_at
FROM vendor_response_attachments_b
GROUP BY vendor_response_id
) file_stats ON var.id = file_stats.vendor_response_id
);
|
