diff options
Diffstat (limited to 'db/migrations/0140_careless_agent_zero.sql')
| -rw-r--r-- | db/migrations/0140_careless_agent_zero.sql | 42 |
1 files changed, 42 insertions, 0 deletions
diff --git a/db/migrations/0140_careless_agent_zero.sql b/db/migrations/0140_careless_agent_zero.sql new file mode 100644 index 00000000..53bd8724 --- /dev/null +++ b/db/migrations/0140_careless_agent_zero.sql @@ -0,0 +1,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 +);
\ No newline at end of file |