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
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
|
CREATE TABLE "tech_sales_vendor_quotation_attachments" (
"id" serial PRIMARY KEY NOT NULL,
"quotation_id" integer NOT NULL,
"revision_id" integer DEFAULT 0 NOT NULL,
"file_name" varchar(255) NOT NULL,
"original_file_name" varchar(255) NOT NULL,
"file_size" integer NOT NULL,
"file_type" varchar(100),
"file_path" varchar(500) NOT NULL,
"description" text,
"uploaded_by" integer,
"vendor_id" integer,
"is_vendor_upload" boolean DEFAULT true,
"created_at" timestamp DEFAULT now() NOT NULL,
"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "tech_sales_vendor_quotation_revisions" (
"id" serial PRIMARY KEY NOT NULL,
"quotation_id" integer NOT NULL,
"version" integer NOT NULL,
"snapshot" json NOT NULL,
"change_reason" text,
"revision_note" text,
"revised_by" integer,
"revised_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "periodic_evaluations" (
"id" serial PRIMARY KEY NOT NULL,
"evaluation_target_id" integer NOT NULL,
"evaluation_period" varchar(20) NOT NULL,
"documents_submitted" boolean DEFAULT false,
"submission_date" timestamp,
"submission_deadline" timestamp,
"final_score" numeric(5, 2),
"final_grade" varchar(5),
"evaluation_score" numeric(5, 2),
"evaluation_grade" varchar(5),
"process_score" numeric(5, 2) DEFAULT '0',
"price_score" numeric(5, 2) DEFAULT '0',
"delivery_score" numeric(5, 2) DEFAULT '0',
"self_evaluation_score" numeric(5, 2) DEFAULT '0',
"total_score" numeric(5, 2) DEFAULT '0',
"participation_bonus" numeric(5, 2) DEFAULT '0',
"quality_deduction" numeric(5, 2) DEFAULT '0',
"status" varchar(30) DEFAULT 'PENDING_SUBMISSION' NOT NULL,
"review_completed_at" timestamp,
"finalized_at" timestamp,
"finalized_by" integer,
"evaluation_note" text,
"created_at" timestamp DEFAULT now() NOT NULL,
"updated_at" timestamp DEFAULT now() NOT NULL,
CONSTRAINT "unique_evaluation_target" UNIQUE("evaluation_target_id","evaluation_period")
);
--> statement-breakpoint
CREATE TABLE "reviewer_evaluation_details" (
"id" serial PRIMARY KEY NOT NULL,
"reviewer_evaluation_id" integer NOT NULL,
"reg_eval_criteria_details_id" integer NOT NULL,
"score" numeric(5, 2) NOT NULL,
"comment" text,
"created_at" timestamp DEFAULT now() NOT NULL,
"updated_at" timestamp DEFAULT now() NOT NULL,
CONSTRAINT "unique_reviewer_criteria" UNIQUE("reviewer_evaluation_id","reg_eval_criteria_details_id")
);
--> statement-breakpoint
CREATE TABLE "reviewer_evaluations" (
"id" serial PRIMARY KEY NOT NULL,
"periodic_evaluation_id" integer NOT NULL,
"evaluation_target_reviewer_id" integer NOT NULL,
"process_score" numeric(5, 2),
"price_score" numeric(5, 2),
"delivery_score" numeric(5, 2),
"self_evaluation_score" numeric(5, 2),
"participation_bonus" numeric(5, 2) DEFAULT '0',
"quality_deduction" numeric(5, 2) DEFAULT '0',
"total_score" numeric(5, 2),
"grade" varchar(5),
"is_completed" boolean DEFAULT false,
"completed_at" timestamp,
"reviewer_comment" text,
"created_at" timestamp DEFAULT now() NOT NULL,
"updated_at" timestamp DEFAULT now() NOT NULL,
CONSTRAINT "unique_reviewer_evaluation" UNIQUE("periodic_evaluation_id","evaluation_target_reviewer_id")
);
--> statement-breakpoint
ALTER TABLE "tech_sales_vendor_quotations" ALTER COLUMN "status" SET DEFAULT 'Assigned';--> statement-breakpoint
ALTER TABLE "tech_sales_vendor_quotation_attachments" ADD CONSTRAINT "tech_sales_vendor_quotation_attachments_quotation_id_tech_sales_vendor_quotations_id_fk" FOREIGN KEY ("quotation_id") REFERENCES "public"."tech_sales_vendor_quotations"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "tech_sales_vendor_quotation_attachments" ADD CONSTRAINT "tech_sales_vendor_quotation_attachments_uploaded_by_users_id_fk" FOREIGN KEY ("uploaded_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "tech_sales_vendor_quotation_attachments" ADD CONSTRAINT "tech_sales_vendor_quotation_attachments_vendor_id_tech_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."tech_vendors"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "tech_sales_vendor_quotation_revisions" ADD CONSTRAINT "tech_sales_vendor_quotation_revisions_quotation_id_tech_sales_vendor_quotations_id_fk" FOREIGN KEY ("quotation_id") REFERENCES "public"."tech_sales_vendor_quotations"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "periodic_evaluations" ADD CONSTRAINT "periodic_evaluations_evaluation_target_id_evaluation_targets_id_fk" FOREIGN KEY ("evaluation_target_id") REFERENCES "public"."evaluation_targets"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "periodic_evaluations" ADD CONSTRAINT "periodic_evaluations_finalized_by_users_id_fk" FOREIGN KEY ("finalized_by") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "reviewer_evaluation_details" ADD CONSTRAINT "reviewer_evaluation_details_reviewer_evaluation_id_reviewer_evaluations_id_fk" FOREIGN KEY ("reviewer_evaluation_id") REFERENCES "public"."reviewer_evaluations"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "reviewer_evaluation_details" ADD CONSTRAINT "reviewer_evaluation_details_reg_eval_criteria_details_id_reg_eval_criteria_details_id_fk" FOREIGN KEY ("reg_eval_criteria_details_id") REFERENCES "public"."reg_eval_criteria_details"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "reviewer_evaluations" ADD CONSTRAINT "reviewer_evaluations_periodic_evaluation_id_periodic_evaluations_id_fk" FOREIGN KEY ("periodic_evaluation_id") REFERENCES "public"."periodic_evaluations"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "reviewer_evaluations" ADD CONSTRAINT "reviewer_evaluations_evaluation_target_reviewer_id_evaluation_target_reviewers_id_fk" FOREIGN KEY ("evaluation_target_reviewer_id") REFERENCES "public"."evaluation_target_reviewers"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "tech_sales_quotation_revisions_quotation_version_idx" ON "tech_sales_vendor_quotation_revisions" USING btree ("quotation_id","version");--> statement-breakpoint
CREATE VIEW "public"."periodic_evaluations_view" AS (select "periodic_evaluations"."id", "periodic_evaluations"."evaluation_target_id", "evaluation_targets"."evaluation_year", "evaluation_targets"."division", "evaluation_targets"."vendor_id", "evaluation_targets"."vendor_code", "evaluation_targets"."vendor_name", "evaluation_targets"."domestic_foreign", "evaluation_targets"."material_type", "periodic_evaluations"."evaluation_period", "periodic_evaluations"."documents_submitted", "periodic_evaluations"."submission_date", "periodic_evaluations"."submission_deadline", "periodic_evaluations"."final_score", "periodic_evaluations"."final_grade", "periodic_evaluations"."evaluation_score", "periodic_evaluations"."evaluation_grade", "periodic_evaluations"."process_score", "periodic_evaluations"."price_score", "periodic_evaluations"."delivery_score", "periodic_evaluations"."self_evaluation_score", "periodic_evaluations"."total_score", "periodic_evaluations"."participation_bonus", "periodic_evaluations"."quality_deduction", "periodic_evaluations"."status", "periodic_evaluations"."review_completed_at", "periodic_evaluations"."finalized_at", "periodic_evaluations"."finalized_by", "periodic_evaluations"."evaluation_note", "periodic_evaluations"."created_at", "periodic_evaluations"."updated_at", "evaluation_targets"."status", "evaluation_targets"."admin_comment", "evaluation_targets"."consolidated_comment", "evaluation_targets"."consensus_status", "evaluation_targets"."confirmed_at", (
SELECT COUNT(*)::int
FROM "reviewer_evaluations" re
WHERE re.periodic_evaluation_id = "periodic_evaluations"."id"
) as "total_reviewers", (
SELECT COUNT(*)::int
FROM "reviewer_evaluations" re
WHERE re.periodic_evaluation_id = "periodic_evaluations"."id"
AND re.is_completed = true
) as "completed_reviewers", (
SELECT COUNT(*)::int
FROM "reviewer_evaluations" re
WHERE re.periodic_evaluation_id = "periodic_evaluations"."id"
AND re.is_completed = false
) as "pending_reviewers", "users"."name", "users"."email" from "periodic_evaluations" left join "evaluation_targets" on "periodic_evaluations"."evaluation_target_id" = "evaluation_targets"."id" left join "users" on "periodic_evaluations"."finalized_by" = "users"."id" order by "periodic_evaluations"."created_at");
|
