1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
|
DROP VIEW "public"."gtc_clauses_tree_view";--> statement-breakpoint
DROP VIEW "public"."gtc_vendor_clauses_view";--> statement-breakpoint
ALTER TABLE "gtc_clauses" ADD COLUMN "images" json;--> statement-breakpoint
ALTER TABLE "gtc_templates" ADD COLUMN "document_id" integer NOT NULL;--> statement-breakpoint
ALTER TABLE "gtc_templates" ADD CONSTRAINT "gtc_templates_document_id_gtc_documents_id_fk" FOREIGN KEY ("document_id") REFERENCES "public"."gtc_documents"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "gtc_clauses" DROP COLUMN "number_variable_name";--> statement-breakpoint
ALTER TABLE "gtc_clauses" DROP COLUMN "subtitle_variable_name";--> statement-breakpoint
ALTER TABLE "gtc_clauses" DROP COLUMN "content_variable_name";--> statement-breakpoint
CREATE VIEW "public"."gtc_clauses_tree_view" AS (select "gtc_clauses"."id", "gtc_clauses"."document_id", "gtc_clauses"."parent_id", "gtc_clauses"."item_number", "gtc_clauses"."category", "gtc_clauses"."subtitle", "gtc_clauses"."content", "gtc_clauses"."sort_order", "gtc_clauses"."depth", "gtc_clauses"."full_path", "gtc_clauses"."images", "gtc_clauses"."is_active", "gtc_clauses"."created_at", "gtc_clauses"."created_by_id", "gtc_clauses"."updated_at", "gtc_clauses"."updated_by_id", "gtc_clauses"."edit_reason", "gtc_documents"."type", "gtc_documents"."file_name", "gtc_documents"."revision", "gtc_documents"."project_id", created_by_user.name as "created_by_name", created_by_user.email as "created_by_email", updated_by_user.name as "updated_by_name", updated_by_user.email as "updated_by_email", parent_clause.item_number as "parent_item_number", parent_clause.subtitle as "parent_subtitle",
(
SELECT count(*)
FROM gtc_clauses children
WHERE children.parent_id = "gtc_clauses"."id"
AND children.is_active = true
)
as "children_count",
(
SELECT count(*)
FROM gtc_clauses siblings
WHERE siblings.parent_id = "gtc_clauses"."parent_id"
AND siblings.is_active = true
)
as "siblings_count",
"gtc_clauses"."created_by_id" != "gtc_clauses"."updated_by_id" OR
"gtc_clauses"."created_at" != "gtc_clauses"."updated_at"
as "has_edit_history" from "gtc_clauses" left join "gtc_documents" on "gtc_clauses"."document_id" = "gtc_documents"."id" left join users created_by_user on "gtc_clauses"."created_by_id" = created_by_user.id left join users updated_by_user on "gtc_clauses"."updated_by_id" = updated_by_user.id left join gtc_clauses parent_clause on "gtc_clauses"."parent_id" = parent_clause.id);--> statement-breakpoint
CREATE VIEW "public"."gtc_vendor_clauses_view" AS (select "gtc_vendor_clauses"."id", "gtc_vendor_clauses"."vendor_document_id", "gtc_vendor_clauses"."base_clause_id", "gtc_vendor_clauses"."parent_id",
COALESCE("gtc_vendor_clauses"."modified_item_number", "gtc_clauses"."item_number")
as "effective_item_number",
COALESCE("gtc_vendor_clauses"."modified_category", "gtc_clauses"."category")
as "effective_category",
COALESCE("gtc_vendor_clauses"."modified_subtitle", "gtc_clauses"."subtitle")
as "effective_subtitle",
COALESCE("gtc_vendor_clauses"."modified_content", "gtc_clauses"."content")
as "effective_content", "gtc_vendor_clauses"."is_number_modified", "gtc_vendor_clauses"."is_category_modified", "gtc_vendor_clauses"."is_subtitle_modified", "gtc_vendor_clauses"."is_content_modified", "gtc_clauses"."item_number", "gtc_clauses"."category", "gtc_clauses"."subtitle", "gtc_clauses"."content", "gtc_vendor_documents"."vendor_id", "vendors"."vendor_code", "vendors"."vendor_name", "gtc_vendor_documents"."base_document_id", "gtc_documents"."type", "gtc_documents"."file_name", "gtc_vendor_clauses"."review_status", "gtc_vendor_clauses"."negotiation_note", "gtc_vendor_clauses"."is_excluded", "gtc_vendor_clauses"."sort_order", "gtc_vendor_clauses"."depth", "gtc_vendor_clauses"."full_path",
"gtc_vendor_clauses"."is_number_modified" OR
"gtc_vendor_clauses"."is_category_modified" OR
"gtc_vendor_clauses"."is_subtitle_modified" OR
"gtc_vendor_clauses"."is_content_modified"
as "has_modifications", "gtc_vendor_clauses"."created_at", "gtc_vendor_clauses"."updated_at" from "gtc_vendor_clauses" left join "gtc_clauses" on "gtc_vendor_clauses"."base_clause_id" = "gtc_clauses"."id" left join "gtc_vendor_documents" on "gtc_vendor_clauses"."vendor_document_id" = "gtc_vendor_documents"."id" left join "vendors" on "gtc_vendor_documents"."vendor_id" = "vendors"."id" left join "gtc_documents" on "gtc_vendor_documents"."base_document_id" = "gtc_documents"."id");
|
