diff options
Diffstat (limited to 'db/migrations/0126_vengeful_kid_colt.sql')
| -rw-r--r-- | db/migrations/0126_vengeful_kid_colt.sql | 151 |
1 files changed, 151 insertions, 0 deletions
diff --git a/db/migrations/0126_vengeful_kid_colt.sql b/db/migrations/0126_vengeful_kid_colt.sql new file mode 100644 index 00000000..25bf4ed0 --- /dev/null +++ b/db/migrations/0126_vengeful_kid_colt.sql @@ -0,0 +1,151 @@ +DROP VIEW "public"."rfq_dashboard";--> statement-breakpoint +DROP VIEW "public"."vendor_response_summary";--> statement-breakpoint +CREATE VIEW "public"."rfq_dashboard" AS ( + 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 + FROM b_rfqs br + LEFT JOIN projects p ON br.project_id = p.id + 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 절 확장 ------------------------------------------- + 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 + );
\ No newline at end of file |