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
41
42
43
44
45
46
47
48
49
50
51
52
53
|
ALTER TABLE "basic_contract" ADD COLUMN "deadline" date NOT NULL;--> statement-breakpoint
ALTER TABLE "basic_contract" ADD COLUMN "vendor_signed_at" timestamp;--> statement-breakpoint
ALTER TABLE "basic_contract" ADD COLUMN "buyer_signed_at" timestamp;--> statement-breakpoint
ALTER TABLE "basic_contract" ADD COLUMN "legal_review_requested_at" timestamp;--> statement-breakpoint
ALTER TABLE "basic_contract" ADD COLUMN "legal_review_completed_at" timestamp;--> statement-breakpoint
CREATE VIEW "public"."basic_contract_template_stats_view" AS (select "basic_contract_templates"."id" as "template_id", "basic_contract_templates"."template_name" as "template_name", "basic_contract_templates"."revision" as "revision", "basic_contract_templates"."legal_review_required" as "legal_review_required", "basic_contract_templates"."validity_period" as "validity_period", "basic_contract_templates"."created_at" as "template_created_at", COUNT("basic_contract"."id") as "total_sent_count",
COUNT(CASE
WHEN "basic_contract"."deadline" < CURRENT_DATE
AND "basic_contract"."status" NOT IN ('COMPLETED', 'REJECTED')
THEN 1
END)
as "overdue_count",
COUNT(CASE
WHEN "basic_contract"."status" = 'PENDING'
THEN 1
END)
as "unsigned_count",
COUNT(CASE
WHEN "basic_contract"."status" IN ('VENDOR_SIGNED', 'BUYER_SIGNED', 'LEGAL_REVIEW_REQUESTED', 'LEGAL_REVIEW_COMPLETED', 'COMPLETED')
THEN 1
END)
as "vendor_signed_count",
COUNT(CASE
WHEN "basic_contract"."status" IN ('LEGAL_REVIEW_REQUESTED', 'LEGAL_REVIEW_COMPLETED')
THEN 1
END)
as "legal_request_count",
COUNT(CASE
WHEN "basic_contract"."status" = 'LEGAL_REVIEW_COMPLETED'
OR ("basic_contract"."status" = 'COMPLETED' AND "basic_contract"."legal_review_completed_at" IS NOT NULL)
THEN 1
END)
as "legal_completed_count",
COUNT(CASE
WHEN "basic_contract"."status" = 'COMPLETED'
THEN 1
END)
as "contract_completed_count",
COUNT(CASE
WHEN "basic_contract"."status" IN ('BUYER_SIGNED', 'LEGAL_REVIEW_REQUESTED', 'LEGAL_REVIEW_COMPLETED', 'COMPLETED')
THEN 1
END)
as "buyer_signed_count",
COUNT(CASE
WHEN "basic_contract"."status" = 'REJECTED'
THEN 1
END)
as "rejected_count",
AVG(CASE
WHEN "basic_contract"."status" = 'COMPLETED' AND "basic_contract"."completed_at" IS NOT NULL
THEN EXTRACT(EPOCH FROM ("basic_contract"."completed_at" - "basic_contract"."created_at")) / 86400
END)
as "avg_processing_days", MAX("basic_contract"."updated_at") as "last_activity_date" from "basic_contract_templates" left join "basic_contract" on "basic_contract_templates"."id" = "basic_contract"."template_id" where "basic_contract_templates"."status" = 'ACTIVE' group by "basic_contract_templates"."id", "basic_contract_templates"."template_name", "basic_contract_templates"."revision", "basic_contract_templates"."legal_review_required", "basic_contract_templates"."validity_period", "basic_contract_templates"."created_at");
|
