diff options
Diffstat (limited to 'db/migrations/0005_jittery_may_parker.sql')
| -rw-r--r-- | db/migrations/0005_jittery_may_parker.sql | 23 |
1 files changed, 23 insertions, 0 deletions
diff --git a/db/migrations/0005_jittery_may_parker.sql b/db/migrations/0005_jittery_may_parker.sql new file mode 100644 index 00000000..feb88236 --- /dev/null +++ b/db/migrations/0005_jittery_may_parker.sql @@ -0,0 +1,23 @@ +DROP VIEW "public"."vendor_response_cbe_view";--> statement-breakpoint +DROP VIEW "public"."vendor_tbe_view";--> statement-breakpoint +ALTER TABLE "vendor_commercial_responses" ADD COLUMN "response_status" varchar(30) DEFAULT 'PENDING' NOT NULL;--> statement-breakpoint +ALTER TABLE "vendor_technical_responses" ADD COLUMN "response_status" varchar(30) DEFAULT 'PENDING' NOT NULL;--> statement-breakpoint +CREATE VIEW "public"."vendor_response_cbe_view" AS (select "vendor_responses"."id" as "response_id", "vendor_responses"."rfq_id" as "rfq_id", "vendor_responses"."vendor_id" as "vendor_id", "vendor_responses"."response_status" as "response_status", "vendor_responses"."notes" as "response_notes", "vendor_responses"."responded_by" as "responded_by", "vendor_responses"."responded_at" as "responded_at", "vendor_responses"."updated_at" as "response_updated_at", "rfqs"."rfq_code" as "rfq_code", "rfqs"."description" as "rfq_description", "rfqs"."due_date" as "rfq_due_date", "rfqs"."status" as "rfq_status", "rfqs"."rfq_type" as "rfq_type", "vendors"."vendor_name" as "vendor_name", "vendors"."vendor_code" as "vendor_code", "vendors"."status" as "vendor_status", "projects"."id" as "project_id", "projects"."code" as "project_code", "projects"."name" as "project_name", "vendor_commercial_responses"."id" as "commercial_response_id", "vendor_commercial_responses"."response_status" as "commercial_response_status", "vendor_commercial_responses"."total_price" as "total_price", "vendor_commercial_responses"."currency" as "currency", "vendor_commercial_responses"."payment_terms" as "payment_terms", "vendor_commercial_responses"."incoterms" as "incoterms", "vendor_commercial_responses"."delivery_period" as "delivery_period", "vendor_commercial_responses"."warranty_period" as "warranty_period", "vendor_commercial_responses"."validity_period" as "validity_period", "vendor_commercial_responses"."price_breakdown" as "price_breakdown", "vendor_commercial_responses"."commercial_notes" as "commercial_notes", "vendor_commercial_responses"."created_at" as "commercial_created_at", "vendor_commercial_responses"."updated_at" as "commercial_updated_at", ( + SELECT COUNT(*) + FROM "vendor_response_attachments" + WHERE "vendor_response_attachments"."response_id" = "vendor_responses"."id" + ) as "attachment_count", ( + SELECT COUNT(*) + FROM "vendor_response_attachments" + WHERE "vendor_response_attachments"."commercial_response_id" = "vendor_commercial_responses"."id" + ) as "commercial_attachment_count", ( + SELECT COUNT(*) + FROM "vendor_response_attachments" + WHERE "vendor_response_attachments"."response_id" = "vendor_responses"."id" + AND "vendor_response_attachments"."attachment_type" = 'TECHNICAL_SPEC' + ) as "technical_attachment_count", ( + SELECT MAX("uploaded_at") + FROM "vendor_response_attachments" + WHERE "vendor_response_attachments"."response_id" = "vendor_responses"."id" + ) as "latest_attachment_date" from "vendor_responses" inner join "rfqs" on "vendor_responses"."rfq_id" = "rfqs"."id" inner join "vendors" on "vendor_responses"."vendor_id" = "vendors"."id" left join "projects" on "rfqs"."project_id" = "projects"."id" left join "vendor_commercial_responses" on "vendor_commercial_responses"."response_id" = "vendor_responses"."id");--> statement-breakpoint +CREATE VIEW "public"."vendor_tbe_view" AS (select "vendors"."id" as "vendor_id", "vendors"."vendor_name" as "vendor_name", "vendors"."vendor_code" as "vendor_code", "vendors"."address" as "address", "vendors"."country" as "country", "vendors"."email" as "email", "vendors"."website" as "website", "vendors"."status" as "vendor_status", "vendor_responses"."id" as "vendor_response_id", "vendor_responses"."rfq_id" as "rfq_id", "vendor_responses"."response_status" as "rfq_vendor_status", "vendor_responses"."updated_at" as "rfq_vendor_updated", "vendor_technical_responses"."id" as "technical_response_id", "vendor_technical_responses"."response_status" as "technical_response_status", "vendor_technical_responses"."summary" as "technical_summary", "vendor_technical_responses"."notes" as "technical_notes", "vendor_technical_responses"."updated_at" as "technical_updated", "rfqs"."rfq_code" as "rfq_code", "rfqs"."rfq_type" as "rfq_type", "rfqs"."description" as "description", "rfqs"."due_date" as "due_date", "projects"."id" as "project_id", "projects"."code" as "project_code", "projects"."name" as "project_name", "rfq_evaluations"."id" as "tbe_id", "rfq_evaluations"."result" as "tbe_result", "rfq_evaluations"."notes" as "tbe_note", "rfq_evaluations"."updated_at" as "tbe_updated" from "vendors" left join "vendor_responses" on "vendor_responses"."vendor_id" = "vendors"."id" left join "rfqs" on "vendor_responses"."rfq_id" = "rfqs"."id" left join "projects" on "rfqs"."project_id" = "projects"."id" left join "vendor_technical_responses" on "vendor_technical_responses"."response_id" = "vendor_responses"."id" left join "rfq_evaluations" on ("rfq_evaluations"."vendor_id" = "vendors"."id" and "rfq_evaluations"."eval_type" = 'TBE' and "rfq_evaluations"."rfq_id" = "vendor_responses"."rfq_id"));
\ No newline at end of file |