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