diff options
Diffstat (limited to 'db/migrations/0139_real_warlock.sql')
| -rw-r--r-- | db/migrations/0139_real_warlock.sql | 326 |
1 files changed, 326 insertions, 0 deletions
diff --git a/db/migrations/0139_real_warlock.sql b/db/migrations/0139_real_warlock.sql new file mode 100644 index 00000000..7e8e7877 --- /dev/null +++ b/db/migrations/0139_real_warlock.sql @@ -0,0 +1,326 @@ +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.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 +);--> statement-breakpoint +CREATE VIEW "public"."rfq_progress_summary" AS ( + SELECT + br.id as rfq_id, + br.rfq_code, + br.status as rfq_status, + br.due_date, + (br.due_date - CURRENT_DATE) as days_to_deadline, + + -- 첨부파일 통계 + attachment_stats.total_attachments, + attachment_stats.attachments_with_multiple_revisions, + attachment_stats.total_client_revisions, + + -- Initial RFQ 통계 + COALESCE(initial_stats.vendor_count, 0) as initial_vendor_count, + COALESCE(initial_stats.total_responses, 0) as initial_total_responses, + COALESCE(initial_stats.responded_count, 0) as initial_responded_count, + COALESCE(initial_stats.up_to_date_count, 0) as initial_up_to_date_count, + COALESCE(initial_stats.version_mismatch_count, 0) as initial_version_mismatch_count, + COALESCE(initial_stats.response_rate, 0) as initial_response_rate, + COALESCE(initial_stats.version_match_rate, 0) as initial_version_match_rate, + + -- Final RFQ 통계 + COALESCE(final_stats.vendor_count, 0) as final_vendor_count, + COALESCE(final_stats.total_responses, 0) as final_total_responses, + COALESCE(final_stats.responded_count, 0) as final_responded_count, + COALESCE(final_stats.up_to_date_count, 0) as final_up_to_date_count, + COALESCE(final_stats.version_mismatch_count, 0) as final_version_mismatch_count, + COALESCE(final_stats.response_rate, 0) as final_response_rate, + COALESCE(final_stats.version_match_rate, 0) as final_version_match_rate, + + COALESCE(file_stats.total_files, 0) as total_response_files + + FROM b_rfqs br + LEFT JOIN ( + SELECT + ba.rfq_id, + COUNT(*) as total_attachments, + COUNT(CASE WHEN rev_count.total_revisions > 1 THEN 1 END) as attachments_with_multiple_revisions, + SUM(rev_count.total_revisions) as total_client_revisions + FROM b_rfq_attachments ba + LEFT JOIN ( + SELECT + attachment_id, + COUNT(*) as total_revisions + FROM b_rfq_attachment_revisions + GROUP BY attachment_id + ) rev_count ON ba.id = rev_count.attachment_id + GROUP BY ba.rfq_id + ) attachment_stats ON br.id = attachment_stats.rfq_id + + LEFT JOIN ( + SELECT + br.id as rfq_id, + COUNT(DISTINCT var.vendor_id) as vendor_count, + COUNT(*) as total_responses, + COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END) as responded_count, + COUNT(CASE WHEN vrd.effective_status = 'UP_TO_DATE' THEN 1 END) as up_to_date_count, + COUNT(CASE WHEN vrd.effective_status = 'VERSION_MISMATCH' THEN 1 END) as version_mismatch_count, + ROUND( + COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END) * 100.0 / + NULLIF(COUNT(*), 0), 2 + ) as response_rate, + ROUND( + COUNT(CASE WHEN vrd.effective_status = 'UP_TO_DATE' THEN 1 END) * 100.0 / + NULLIF(COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END), 0), 2 + ) as version_match_rate + FROM b_rfqs br + JOIN vendor_response_detail vrd ON br.id = vrd.rfq_id + JOIN vendor_attachment_responses var ON vrd.response_id = var.id + WHERE var.rfq_type = 'INITIAL' + GROUP BY br.id + ) initial_stats ON br.id = initial_stats.rfq_id + + LEFT JOIN ( + SELECT + br.id as rfq_id, + COUNT(DISTINCT var.vendor_id) as vendor_count, + COUNT(*) as total_responses, + COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END) as responded_count, + COUNT(CASE WHEN vrd.effective_status = 'UP_TO_DATE' THEN 1 END) as up_to_date_count, + COUNT(CASE WHEN vrd.effective_status = 'VERSION_MISMATCH' THEN 1 END) as version_mismatch_count, + ROUND( + COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END) * 100.0 / + NULLIF(COUNT(*), 0), 2 + ) as response_rate, + ROUND( + COUNT(CASE WHEN vrd.effective_status = 'UP_TO_DATE' THEN 1 END) * 100.0 / + NULLIF(COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END), 0), 2 + ) as version_match_rate + FROM b_rfqs br + JOIN vendor_response_detail vrd ON br.id = vrd.rfq_id + JOIN vendor_attachment_responses var ON vrd.response_id = var.id + WHERE var.rfq_type = 'FINAL' + GROUP BY br.id + ) final_stats ON br.id = final_stats.rfq_id + + LEFT JOIN ( + SELECT + br.id as rfq_id, + COUNT(vra.id) as total_files + FROM b_rfqs br + JOIN b_rfq_attachments ba ON br.id = ba.rfq_id + JOIN vendor_attachment_responses var ON ba.id = var.attachment_id + LEFT JOIN vendor_response_attachments_b vra ON var.id = vra.vendor_response_id + GROUP BY br.id + ) file_stats ON br.id = file_stats.rfq_id +);--> 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.requested_at, + var.responded_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 +);--> 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.requested_at, + var.responded_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 |