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
|
DROP VIEW "public"."attachment_revision_history";--> statement-breakpoint
CREATE VIEW "public"."attachment_revision_history" AS (
SELECT
br.id as rfq_id,
br.rfq_code,
ba.id as attachment_id,
ba.attachment_type,
ba.serial_no,
-- 발주처 리비전 정보
rev.id as client_revision_id,
rev.revision_no as client_revision_no,
rev.original_file_name as client_file_name,
rev.file_size as client_file_size,
rev.file_path as client_file_path,
rev.revision_comment as client_revision_comment,
rev.created_at as client_revision_created_at,
rev.is_latest as is_latest_client_revision,
-- 벤더 응답 통계
COALESCE(response_stats.total_responses, 0) as total_vendor_responses,
COALESCE(response_stats.responded_count, 0) as responded_vendors,
COALESCE(response_stats.pending_count, 0) as pending_vendors,
COALESCE(response_stats.total_files, 0) as total_response_files
FROM b_rfqs br
JOIN b_rfq_attachments ba ON br.id = ba.rfq_id
JOIN b_rfq_attachment_revisions rev ON ba.id = rev.attachment_id
LEFT JOIN (
SELECT
var.attachment_id,
COUNT(*) as total_responses,
COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END) as responded_count,
COUNT(CASE WHEN var.response_status = 'NOT_RESPONDED' THEN 1 END) as pending_count,
COUNT(vra.id) as total_files
FROM vendor_attachment_responses var
LEFT JOIN vendor_response_attachments_b vra ON var.id = vra.vendor_response_id
GROUP BY var.attachment_id
) response_stats ON ba.id = response_stats.attachment_id
ORDER BY ba.id, rev.created_at DESC
);
|
