diff options
Diffstat (limited to 'db/migrations/0127_sweet_spiral.sql')
| -rw-r--r-- | db/migrations/0127_sweet_spiral.sql | 119 |
1 files changed, 119 insertions, 0 deletions
diff --git a/db/migrations/0127_sweet_spiral.sql b/db/migrations/0127_sweet_spiral.sql new file mode 100644 index 00000000..93df76ee --- /dev/null +++ b/db/migrations/0127_sweet_spiral.sql @@ -0,0 +1,119 @@ +DROP VIEW "public"."rfq_dashboard";--> statement-breakpoint +DROP VIEW "public"."vendor_response_summary";--> statement-breakpoint +CREATE VIEW "public"."rfq_dashboard" AS ( + -- ② SELECT 절 확장 ------------------------------------------- + SELECT + br.id AS rfq_id, + br.rfq_code, + br.description, + br.status, + br.due_date, + p.code AS project_code, + p.name AS project_name, + br.package_no, + br.package_name, + br.pic_code, + br.pic_name, + br.eng_pic_name, + br.project_company, + br.project_flag, + br.project_site, + br.remark, + + -- 첨부/벤더 요약 ----------------------- + COALESCE(att_count.total_attachments, 0) AS total_attachments, + COALESCE(init_summary.vendor_count, 0) AS initial_vendor_count, + COALESCE(final_summary.vendor_count, 0) AS final_vendor_count, + COALESCE(init_summary.avg_response_rate, 0) AS initial_response_rate, + COALESCE(final_summary.avg_response_rate, 0) AS final_response_rate, + + -- 진행률·마감까지 일수 -------------- + CASE + WHEN br.status = 'DRAFT' THEN 0 + WHEN br.status = 'Doc. Received' THEN 10 + WHEN br.status = 'PIC Assigned' THEN 20 + WHEN br.status = 'Doc. Confirmed' THEN 30 + WHEN br.status = 'Init. RFQ Sent' THEN 40 + WHEN br.status = 'Init. RFQ Answered' THEN 50 + WHEN br.status = 'TBE started' THEN 60 + WHEN br.status = 'TBE finished' THEN 70 + WHEN br.status = 'Final RFQ Sent' THEN 80 + WHEN br.status = 'Quotation Received' THEN 90 + WHEN br.status = 'Vendor Selected' THEN 100 + ELSE 0 + END AS overall_progress, + (br.due_date - CURRENT_DATE) AS days_to_deadline, + + br.created_at, + br.updated_at, + + -- 💡 추가되는 컬럼 ------------------- + upd.name AS updated_by_name, + upd.email AS updated_by_email + FROM b_rfqs br + LEFT JOIN projects p ON br.project_id = p.id + + -- ③ 사용자 정보 조인 -------------------- + LEFT JOIN users upd ON br.updated_by = upd.id + + -- (나머지 이미 있던 JOIN 들은 그대로) ----- + LEFT JOIN ( + SELECT rfq_id, COUNT(*) AS total_attachments + FROM b_rfq_attachments + GROUP BY rfq_id + ) att_count ON br.id = att_count.rfq_id + + LEFT JOIN ( + SELECT + rfq_id, + COUNT(DISTINCT vendor_id) AS vendor_count, + AVG(response_rate) AS avg_response_rate + FROM vendor_response_summary + WHERE rfq_type = 'INITIAL' + GROUP BY rfq_id + ) init_summary ON br.id = init_summary.rfq_id + + LEFT JOIN ( + SELECT + rfq_id, + COUNT(DISTINCT vendor_id) AS vendor_count, + AVG(response_rate) AS avg_response_rate + FROM vendor_response_summary + WHERE rfq_type = 'FINAL' + GROUP BY rfq_id + ) final_summary ON br.id = final_summary.rfq_id + );--> statement-breakpoint + + +CREATE VIEW "public"."vendor_response_summary" AS ( + SELECT + br.id as rfq_id, + br.rfq_code, + br.status as rfq_status, + v.id as vendor_id, + v.vendor_code, + v.vendor_name, + v.country as vendor_country, + v.business_size as vendor_business_size, + var.rfq_type, + COUNT(var.id) as total_attachments, + 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(CASE WHEN var.response_status = 'WAIVED' THEN 1 END) as waived_count, + COUNT(CASE WHEN var.response_status = 'REVISION_REQUESTED' THEN 1 END) as revision_requested_count, + ROUND( + (COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END) * 100.0 / + NULLIF(COUNT(CASE WHEN var.response_status != 'WAIVED' THEN 1 END), 0)), + 2 + ) as response_rate, + ROUND( + ((COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END) + + COUNT(CASE WHEN var.response_status = 'WAIVED' THEN 1 END)) * 100.0 / COUNT(var.id)), + 2 + ) as completion_rate + FROM b_rfqs br + JOIN b_rfq_attachments bra ON br.id = bra.rfq_id + JOIN vendor_attachment_responses var ON bra.id = var.attachment_id + JOIN vendors v ON var.vendor_id = v.id + GROUP BY br.id, br.rfq_code, br.status, v.id, v.vendor_code, v.vendor_name, v.country, v.business_size, var.rfq_type +);
\ No newline at end of file |