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
|
DROP VIEW "public"."periodic_evaluations_view";--> statement-breakpoint
DROP VIEW "public"."reviewer_evaluations_view";--> 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"."review_completed_at", "periodic_evaluations"."finalized_at", "periodic_evaluations"."finalized_by", "periodic_evaluations"."evaluation_note", "periodic_evaluations"."created_at", "periodic_evaluations"."updated_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");--> statement-breakpoint
CREATE VIEW "public"."reviewer_evaluations_view" AS (select "reviewer_evaluations"."id", "reviewer_evaluations"."periodic_evaluation_id", "reviewer_evaluations"."evaluation_target_reviewer_id", "reviewer_evaluations"."process_score", "reviewer_evaluations"."price_score", "reviewer_evaluations"."delivery_score", "reviewer_evaluations"."self_evaluation_score", "reviewer_evaluations"."participation_bonus", "reviewer_evaluations"."quality_deduction", "reviewer_evaluations"."total_score", "reviewer_evaluations"."grade", "reviewer_evaluations"."is_completed", "reviewer_evaluations"."completed_at", "reviewer_evaluations"."reviewer_comment", "reviewer_evaluations"."created_at", "reviewer_evaluations"."updated_at", "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"."status", "periodic_evaluations"."review_completed_at", "periodic_evaluations"."finalized_at", "periodic_evaluations"."finalized_by", "periodic_evaluations"."evaluation_note", "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", "evaluation_targets"."admin_comment", "evaluation_targets"."consolidated_comment", "evaluation_targets"."confirmed_at", "evaluation_targets"."confirmed_by", "evaluation_targets"."ld_claim_count", "evaluation_targets"."ld_claim_amount", "evaluation_targets"."ld_claim_currency", "evaluation_target_reviewers"."department_code", "evaluation_target_reviewers"."department_name_from", "evaluation_target_reviewers"."reviewer_user_id", reviewer_user.name as "reviewer_name", reviewer_user.email as "reviewer_email", "evaluation_target_reviewers"."assigned_at", "evaluation_target_reviewers"."assigned_by", assigned_by_user.name as "assigned_by_user_name", finalized_by_user.name as "finalized_by_user_name", finalized_by_user.email as "finalized_by_user_email",
CASE
WHEN "reviewer_evaluations"."is_completed" = true THEN 'COMPLETED'
WHEN "reviewer_evaluations"."total_score" IS NOT NULL THEN 'IN_PROGRESS'
ELSE 'NOT_STARTED'
END
as "evaluation_progress",
CASE
WHEN "periodic_evaluations"."submission_deadline" IS NOT NULL
THEN EXTRACT(DAY FROM "periodic_evaluations"."submission_deadline" - CURRENT_DATE)::int
ELSE NULL
END
as "days_until_deadline",
CASE
WHEN "periodic_evaluations"."submission_deadline" IS NOT NULL
AND "periodic_evaluations"."submission_deadline" < CURRENT_DATE
AND "reviewer_evaluations"."is_completed" = false
THEN true
ELSE false
END
as "is_overdue" from "reviewer_evaluations" left join "periodic_evaluations" on "reviewer_evaluations"."periodic_evaluation_id" = "periodic_evaluations"."id" left join "evaluation_targets" on "periodic_evaluations"."evaluation_target_id" = "evaluation_targets"."id" left join "evaluation_target_reviewers" on "reviewer_evaluations"."evaluation_target_reviewer_id" = "evaluation_target_reviewers"."id" left join users reviewer_user on "evaluation_target_reviewers"."reviewer_user_id" = reviewer_user.id left join users assigned_by_user on "evaluation_target_reviewers"."assigned_by" = assigned_by_user.id left join users finalized_by_user on "periodic_evaluations"."finalized_by" = finalized_by_user.id order by "reviewer_evaluations"."is_completed" ASC, "reviewer_evaluations"."updated_at" DESC);
|
