diff options
Diffstat (limited to 'db/migrations/0303_tired_nitro.sql')
| -rw-r--r-- | db/migrations/0303_tired_nitro.sql | 53 |
1 files changed, 53 insertions, 0 deletions
diff --git a/db/migrations/0303_tired_nitro.sql b/db/migrations/0303_tired_nitro.sql new file mode 100644 index 00000000..085195f6 --- /dev/null +++ b/db/migrations/0303_tired_nitro.sql @@ -0,0 +1,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");
\ No newline at end of file |