diff options
Diffstat (limited to 'db/migrations/0269_remarkable_shard.sql')
| -rw-r--r-- | db/migrations/0269_remarkable_shard.sql | 219 |
1 files changed, 219 insertions, 0 deletions
diff --git a/db/migrations/0269_remarkable_shard.sql b/db/migrations/0269_remarkable_shard.sql new file mode 100644 index 00000000..2901df0f --- /dev/null +++ b/db/migrations/0269_remarkable_shard.sql @@ -0,0 +1,219 @@ +CREATE VIEW "public"."bidding_list_view" AS (select "biddings"."id", "biddings"."bidding_number", "biddings"."revision", "biddings"."project_name", "biddings"."item_name", "biddings"."title", "biddings"."description", "biddings"."content", "biddings"."contract_type", "biddings"."bidding_type", "biddings"."award_count", "biddings"."contract_period", "biddings"."pre_quote_date", "biddings"."bidding_registration_date", "biddings"."submission_start_date", "biddings"."submission_end_date", "biddings"."evaluation_date", "biddings"."has_specification_meeting", "biddings"."has_pr_document", "biddings"."pr_number", "biddings"."currency", "biddings"."budget", "biddings"."target_price", "biddings"."final_bid_price", "biddings"."status", "biddings"."is_public", "biddings"."manager_name", "biddings"."manager_email", "biddings"."manager_phone", "biddings"."remarks", "biddings"."created_by", "biddings"."created_at", "biddings"."updated_at", "biddings"."updated_by", "specification_meetings"."id" IS NOT NULL as "has_specification_meeting_details", "specification_meetings"."meeting_date", "specification_meetings"."location", "specification_meetings"."contact_person", "specification_meetings"."is_required", + COALESCE(( + SELECT count(*) + FROM pr_documents + WHERE bidding_id = "biddings"."id" + ), 0) + as "pr_document_count", + ( + SELECT array_agg(document_name ORDER BY registered_at DESC) + FROM pr_documents + WHERE bidding_id = "biddings"."id" + LIMIT 5 + ) + as "pr_document_names", + COALESCE(( + SELECT count(*) + FROM bidding_companies + WHERE bidding_id = "biddings"."id" + ), 0) + as "participant_expected", + COALESCE(( + SELECT count(*) + FROM bidding_companies + WHERE bidding_id = "biddings"."id" + AND invitation_status = 'submitted' + ), 0) + as "participant_participated", + COALESCE(( + SELECT count(*) + FROM bidding_companies + WHERE bidding_id = "biddings"."id" + AND invitation_status = 'declined' + ), 0) + as "participant_declined", + COALESCE(( + SELECT count(*) + FROM bidding_companies + WHERE bidding_id = "biddings"."id" + AND invitation_status IN ('pending', 'sent') + ), 0) + as "participant_pending", + COALESCE(( + SELECT count(*) + FROM bidding_companies + WHERE bidding_id = "biddings"."id" + AND invitation_status = 'accepted' + ), 0) + as "participant_accepted", + CASE + WHEN ( + SELECT count(*) + FROM bidding_companies + WHERE bidding_id = "biddings"."id" + ) > 0 + THEN ROUND( + ( + SELECT count(*)::decimal + FROM bidding_companies + WHERE bidding_id = "biddings"."id" + AND invitation_status = 'submitted' + ) / ( + SELECT count(*)::decimal + FROM bidding_companies + WHERE bidding_id = "biddings"."id" + ) * 100, 1 + ) + ELSE 0 + END + as "participation_rate", + ( + SELECT AVG(pre_quote_amount) + FROM bidding_companies + WHERE bidding_id = "biddings"."id" + AND pre_quote_amount IS NOT NULL + ) + as "avg_pre_quote_amount", + ( + SELECT MIN(pre_quote_amount) + FROM bidding_companies + WHERE bidding_id = "biddings"."id" + AND pre_quote_amount IS NOT NULL + ) + as "min_pre_quote_amount", + ( + SELECT MAX(pre_quote_amount) + FROM bidding_companies + WHERE bidding_id = "biddings"."id" + AND pre_quote_amount IS NOT NULL + ) + as "max_pre_quote_amount", + ( + SELECT AVG(final_quote_amount) + FROM bidding_companies + WHERE bidding_id = "biddings"."id" + AND final_quote_amount IS NOT NULL + ) + as "avg_final_quote_amount", + ( + SELECT MIN(final_quote_amount) + FROM bidding_companies + WHERE bidding_id = "biddings"."id" + AND final_quote_amount IS NOT NULL + ) + as "min_final_quote_amount", + ( + SELECT MAX(final_quote_amount) + FROM bidding_companies + WHERE bidding_id = "biddings"."id" + AND final_quote_amount IS NOT NULL + ) + as "max_final_quote_amount", + COALESCE(( + SELECT count(*) + FROM bidding_companies + WHERE bidding_id = "biddings"."id" + AND is_pre_quote_selected = true + ), 0) + as "selected_for_final_bid_count", + COALESCE(( + SELECT count(*) + FROM bidding_companies + WHERE bidding_id = "biddings"."id" + AND is_winner = true + ), 0) + as "winner_count", + ( + SELECT array_agg(v.vendor_name ORDER BY v.vendor_name) + FROM bidding_companies bc + JOIN vendors v ON bc.company_id = v.id + WHERE bc.bidding_id = "biddings"."id" + AND bc.is_winner = true + ) + as "winner_company_names", + CASE + WHEN "biddings"."submission_start_date" IS NULL OR "biddings"."submission_end_date" IS NULL + THEN 'not_scheduled' + WHEN NOW() < "biddings"."submission_start_date" + THEN 'scheduled' + WHEN NOW() BETWEEN "biddings"."submission_start_date" AND "biddings"."submission_end_date" + THEN 'active' + WHEN NOW() > "biddings"."submission_end_date" + THEN 'closed' + ELSE 'unknown' + END + as "submission_status", + CASE + WHEN "biddings"."submission_end_date" IS NOT NULL + AND NOW() < "biddings"."submission_end_date" + THEN EXTRACT(DAYS FROM ("biddings"."submission_end_date" - NOW()))::integer + ELSE NULL + END + as "days_until_deadline", + CASE + WHEN "biddings"."submission_start_date" IS NOT NULL + AND NOW() < "biddings"."submission_start_date" + THEN EXTRACT(DAYS FROM ("biddings"."submission_start_date" - NOW()))::integer + ELSE NULL + END + as "days_until_start", + CASE + WHEN "biddings"."budget" IS NOT NULL AND "biddings"."budget" > 0 + AND ( + SELECT MIN(final_quote_amount) + FROM bidding_companies + WHERE bidding_id = "biddings"."id" + AND final_quote_amount IS NOT NULL + ) IS NOT NULL + THEN ROUND( + ( + SELECT MIN(final_quote_amount) + FROM bidding_companies + WHERE bidding_id = "biddings"."id" + AND final_quote_amount IS NOT NULL + ) / "biddings"."budget" * 100, 1 + ) + ELSE NULL + END + as "budget_efficiency_rate", + CASE + WHEN "biddings"."target_price" IS NOT NULL AND "biddings"."target_price" > 0 + AND ( + SELECT MIN(final_quote_amount) + FROM bidding_companies + WHERE bidding_id = "biddings"."id" + AND final_quote_amount IS NOT NULL + ) IS NOT NULL + THEN ROUND( + ( + SELECT MIN(final_quote_amount) + FROM bidding_companies + WHERE bidding_id = "biddings"."id" + AND final_quote_amount IS NOT NULL + ) / "biddings"."target_price" * 100, 1 + ) + ELSE NULL + END + as "target_price_efficiency_rate", + CASE "biddings"."status" + WHEN 'bidding_generated' THEN 10 + WHEN 'request_for_quotation' THEN 20 + WHEN 'received_quotation' THEN 40 + WHEN 'set_target_price' THEN 60 + WHEN 'bidding_opened' THEN 70 + WHEN 'bidding_closed' THEN 80 + WHEN 'evaluation_of_bidding' THEN 90 + WHEN 'vendor_selected' THEN 100 + WHEN 'bidding_disposal' THEN 0 + ELSE 0 + END + as "progress_score", + GREATEST( + "biddings"."updated_at", + COALESCE(( + SELECT MAX(updated_at) + FROM bidding_companies + WHERE bidding_id = "biddings"."id" + ), "biddings"."updated_at") + ) + as "last_activity_date" from "biddings" left join "specification_meetings" on "biddings"."id" = "specification_meetings"."bidding_id");
\ No newline at end of file |