diff options
Diffstat (limited to 'db/migrations/0125_handy_shaman.sql')
| -rw-r--r-- | db/migrations/0125_handy_shaman.sql | 62 |
1 files changed, 62 insertions, 0 deletions
diff --git a/db/migrations/0125_handy_shaman.sql b/db/migrations/0125_handy_shaman.sql new file mode 100644 index 00000000..5d7c4beb --- /dev/null +++ b/db/migrations/0125_handy_shaman.sql @@ -0,0 +1,62 @@ +DROP VIEW "public"."rfq_dashboard";--> 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, + 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 + 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 +);
\ No newline at end of file |