diff options
Diffstat (limited to 'db/migrations/0375_smiling_adam_warlock.sql')
| -rw-r--r-- | db/migrations/0375_smiling_adam_warlock.sql | 246 |
1 files changed, 246 insertions, 0 deletions
diff --git a/db/migrations/0375_smiling_adam_warlock.sql b/db/migrations/0375_smiling_adam_warlock.sql new file mode 100644 index 00000000..81700b1e --- /dev/null +++ b/db/migrations/0375_smiling_adam_warlock.sql @@ -0,0 +1,246 @@ +ALTER TYPE "public"."invitation_status" ADD VALUE 'bidding_invited' BEFORE 'accepted';--> statement-breakpoint +ALTER TYPE "public"."invitation_status" ADD VALUE 'bidding_submitted';--> statement-breakpoint +DROP VIEW "public"."bidding_list_view";--> statement-breakpoint +DROP VIEW "public"."tbe_session_summary_view";--> statement-breakpoint +ALTER TABLE "general_contracts" ALTER COLUMN "type" DROP NOT NULL;--> statement-breakpoint +ALTER TABLE "general_contracts" ALTER COLUMN "execution_method" DROP NOT NULL;--> statement-breakpoint +ALTER TABLE "general_contracts" ALTER COLUMN "name" DROP NOT NULL;--> statement-breakpoint +ALTER TABLE "general_contracts" ALTER COLUMN "start_date" DROP NOT NULL;--> statement-breakpoint +ALTER TABLE "general_contracts" ALTER COLUMN "end_date" DROP NOT NULL;--> statement-breakpoint +ALTER TABLE "general_contracts" ALTER COLUMN "validity_end_date" DROP NOT NULL;--> statement-breakpoint +ALTER TABLE "biddings" ADD COLUMN "bidding_source_type" varchar(20) DEFAULT 'manual' NOT NULL;--> statement-breakpoint +ALTER TABLE "general_contracts" ADD COLUMN "contract_source_type" varchar(20) DEFAULT 'manual' NOT NULL;--> statement-breakpoint +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"."bidding_source_type", "biddings"."is_urgent", "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");--> statement-breakpoint +CREATE VIEW "public"."tbe_session_summary_view" AS (select "tbe"."id" as "tbe_id", "tbe"."session_code" as "session_code", "tbe"."session_title" as "session_title", "rfq"."rfq_code" as "rfq_code", "vendor"."vendor_name" as "vendor_name", "vendor"."vendor_code" as "vendor_code", "tbe"."status" as "status", "tbe"."evaluation_result" as "evaluation_result", "lead_evaluator"."name" as "lead_evaluator_name", ( + SELECT COUNT(*) + FROM rfq_last_tbe_document_reviews + WHERE tbe_session_id = "tbe"."id" + ) as "total_documents", ( + SELECT COUNT(*) + FROM rfq_last_tbe_document_reviews + WHERE tbe_session_id = "tbe"."id" + AND review_status IN ('검토완료', '승인') + ) as "reviewed_documents", ( + SELECT COUNT(*) + FROM rfq_last_tbe_pdftron_comments pc + JOIN rfq_last_tbe_document_reviews dr ON pc.document_review_id = dr.id + WHERE dr.tbe_session_id = "tbe"."id" + ) as "total_comments", "tbe"."actual_start_date" as "actual_start_date", "tbe"."actual_end_date" as "actual_end_date", "tbe"."created_at" as "created_at", "tbe"."updated_at" as "updated_at" from "rfq_last_tbe_sessions" "tbe" left join "rfqs_last" "rfq" on "tbe"."rfqs_last_id" = "rfq"."id" left join "vendors" "vendor" on "tbe"."vendor_id" = "vendor"."id" left join "users" "lead_evaluator" on "tbe"."lead_evaluator_id" = "lead_evaluator"."id");
\ No newline at end of file |