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
|
DROP VIEW "public"."vendor_response_attachments_enhanced";--> statement-breakpoint
CREATE VIEW "public"."vendor_response_attachments_enhanced" AS (
SELECT
vra.id as response_attachment_id,
vra.vendor_response_id,
vra.file_name,
vra.original_file_name,
vra.file_path,
vra.file_size,
vra.file_type,
vra.description,
vra.uploaded_at,
-- 응답 기본 정보
var.attachment_id,
var.vendor_id,
var.rfq_type,
var.rfq_record_id,
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,
-- 첨부파일 정보
ba.attachment_type,
ba.serial_no,
ba.rfq_id,
-- 벤더 정보
v.vendor_code,
v.vendor_name,
v.country as vendor_country,
-- 발주처 현재 리비전 정보
latest_rev.id as latest_client_revision_id,
latest_rev.revision_no as latest_client_revision_no,
latest_rev.original_file_name as latest_client_file_name,
-- 리비전 비교
CASE
WHEN var.responded_revision = ba.current_revision THEN true
ELSE false
END as is_version_matched,
-- 버전 차이 계산 (Rev.0, Rev.1 형태 가정)
CASE
WHEN var.responded_revision IS NULL THEN NULL
WHEN 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,
-- 파일 순서
ROW_NUMBER() OVER (
PARTITION BY var.id
ORDER BY vra.uploaded_at DESC
) as file_sequence,
-- 최신 응답 파일 여부
CASE
WHEN ROW_NUMBER() OVER (
PARTITION BY var.id
ORDER BY vra.uploaded_at DESC
) = 1 THEN true
ELSE false
END as is_latest_response_file
FROM vendor_response_attachments_b vra
JOIN vendor_attachment_responses var ON vra.vendor_response_id = var.id
JOIN b_rfq_attachments ba ON var.attachment_id = ba.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
);
|
