diff options
Diffstat (limited to 'db/migrations/0051_young_ultron.sql')
| -rw-r--r-- | db/migrations/0051_young_ultron.sql | 64 |
1 files changed, 64 insertions, 0 deletions
diff --git a/db/migrations/0051_young_ultron.sql b/db/migrations/0051_young_ultron.sql new file mode 100644 index 00000000..937d162d --- /dev/null +++ b/db/migrations/0051_young_ultron.sql @@ -0,0 +1,64 @@ +DROP VIEW "public"."procurement_rfqs_view";--> statement-breakpoint +ALTER TABLE "procurement_rfqs" ADD COLUMN "pic_code" varchar(50);--> statement-breakpoint +CREATE VIEW "public"."procurement_rfqs_view" AS (select "procurement_rfqs"."id", "procurement_rfqs"."rfq_code", "projects"."code", "projects"."name", "procurement_rfqs"."series", "procurement_rfqs"."rfq_sealed_yn", "items"."item_code", "items"."item_name", "procurement_rfqs"."status", "procurement_rfqs"."pic_code", "procurement_rfqs"."rfq_send_date", "procurement_rfqs"."due_date", "created_by_user"."name", "procurement_rfqs"."updated_at", "updated_by_user"."name", "procurement_rfqs"."remark", ( + SELECT material_code + FROM pr_items + WHERE procurement_rfqs_id = "procurement_rfqs"."id" + AND major_yn = true + LIMIT 1 + ) as "major_item_material_code", ( + SELECT pr_no + FROM pr_items + WHERE procurement_rfqs_id = "procurement_rfqs"."id" + AND major_yn = true + LIMIT 1 + ) as "po_no", ( + SELECT COUNT(*) + FROM pr_items + WHERE procurement_rfqs_id = "procurement_rfqs"."id" + ) as "pr_items_count" from "procurement_rfqs" left join "projects" on "procurement_rfqs"."project_id" = "projects"."id" left join "items" on "procurement_rfqs"."item_id" = "items"."id" left join "users" "created_by_user" on "procurement_rfqs"."created_by" = "created_by_user"."id" left join "users" "updated_by_user" on "procurement_rfqs"."updated_by" = "updated_by_user"."id"); + + +CREATE VIEW "public"."procurement_rfqs_view" AS ( + SELECT + "procurement_rfqs"."id" AS "id", + "procurement_rfqs"."rfq_code", + "projects"."code" AS "project_code", + "projects"."name" AS "project_name", + "procurement_rfqs"."series", + "procurement_rfqs"."pic_code", + "procurement_rfqs"."rfq_sealed_yn", + "items"."item_code", + "items"."item_name", + "procurement_rfqs"."status", + "procurement_rfqs"."rfq_send_date", + "procurement_rfqs"."due_date", + "created_by_user"."name" AS "created_by_user_name", + "procurement_rfqs"."updated_at", + "updated_by_user"."name" AS "updated_by_user_name", + "procurement_rfqs"."remark", + ( + SELECT material_code + FROM pr_items + WHERE procurement_rfqs_id = "procurement_rfqs"."id" + AND major_yn = true + LIMIT 1 + ) AS "major_item_material_code", + ( + SELECT pr_no + FROM pr_items + WHERE procurement_rfqs_id = "procurement_rfqs"."id" + AND major_yn = true + LIMIT 1 + ) AS "po_no", + ( + SELECT COUNT(*) + FROM pr_items + WHERE procurement_rfqs_id = "procurement_rfqs"."id" + ) AS "pr_items_count" + FROM "procurement_rfqs" + LEFT JOIN "projects" ON "procurement_rfqs"."project_id" = "projects"."id" + LEFT JOIN "items" ON "procurement_rfqs"."item_id" = "items"."id" + LEFT JOIN "users" "created_by_user" ON "procurement_rfqs"."created_by" = "created_by_user"."id" + LEFT JOIN "users" "updated_by_user" ON "procurement_rfqs"."updated_by" = "updated_by_user"."id" +);
\ No newline at end of file |