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