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
|
DROP VIEW "public"."periodic_evaluations_aggregated_view";--> statement-breakpoint
CREATE VIEW "public"."periodic_evaluations_aggregated_view" AS (select CONCAT("evaluation_year", '_', "vendor_id") as "id", "evaluation_year", "vendor_id", "vendor_code", "vendor_name", "domestic_foreign", "material_type", ROUND(AVG(NULLIF("process_score", 0)), 1) as "process_score", ROUND(AVG(NULLIF("price_score", 0)), 1) as "price_score", ROUND(AVG(NULLIF("delivery_score", 0)), 1) as "delivery_score", ROUND(AVG(NULLIF("self_evaluation_score", 0)), 1) as "self_evaluation_score", ROUND(AVG(NULLIF("participation_bonus", 0)), 1) as "participation_bonus", ROUND(AVG(NULLIF("quality_deduction", 0)), 1) as "quality_deduction", ROUND(AVG(NULLIF("final_score", 0)), 1) as "final_score",
CASE
WHEN (
AVG(NULLIF("process_score", 0)) +
AVG(NULLIF("price_score", 0)) +
AVG(NULLIF("delivery_score", 0)) +
AVG(NULLIF("self_evaluation_score", 0)) +
AVG(NULLIF("participation_bonus", 0)) -
AVG(NULLIF("quality_deduction", 0))
) >= 90 THEN 'S'
WHEN (
AVG(NULLIF("process_score", 0)) +
AVG(NULLIF("price_score", 0)) +
AVG(NULLIF("delivery_score", 0)) +
AVG(NULLIF("self_evaluation_score", 0)) +
AVG(NULLIF("participation_bonus", 0)) -
AVG(NULLIF("quality_deduction", 0))
) >= 80 THEN 'A'
WHEN (
AVG(NULLIF("process_score", 0)) +
AVG(NULLIF("price_score", 0)) +
AVG(NULLIF("delivery_score", 0)) +
AVG(NULLIF("self_evaluation_score", 0)) +
AVG(NULLIF("participation_bonus", 0)) -
AVG(NULLIF("quality_deduction", 0))
) >= 70 THEN 'B'
WHEN (
AVG(NULLIF("process_score", 0)) +
AVG(NULLIF("price_score", 0)) +
AVG(NULLIF("delivery_score", 0)) +
AVG(NULLIF("self_evaluation_score", 0)) +
AVG(NULLIF("participation_bonus", 0)) -
AVG(NULLIF("quality_deduction", 0))
) >= 60 THEN 'C'
ELSE 'D'
END
as "evaluation_grade",
CASE
WHEN AVG(NULLIF("final_score", 0)) >= 90 THEN 'S'
WHEN AVG(NULLIF("final_score", 0)) >= 80 THEN 'A'
WHEN AVG(NULLIF("final_score", 0)) >= 70 THEN 'B'
WHEN AVG(NULLIF("final_score", 0)) >= 60 THEN 'C'
ELSE 'D'
END
as "final_grade",
CASE
WHEN COUNT(CASE WHEN "status" = 'FINALIZED' THEN 1 END) = COUNT(*) THEN 'FINALIZED'
WHEN COUNT(CASE WHEN "status" IN ('REVIEW_COMPLETED', 'FINALIZED') THEN 1 END) = COUNT(*) THEN 'REVIEW_COMPLETED'
WHEN COUNT(CASE WHEN "status" IN ('IN_REVIEW', 'REVIEW_COMPLETED', 'FINALIZED') THEN 1 END) > 0 THEN 'IN_REVIEW'
WHEN COUNT(CASE WHEN "status" IN ('SUBMITTED', 'IN_REVIEW', 'REVIEW_COMPLETED', 'FINALIZED') THEN 1 END) > 0 THEN 'SUBMITTED'
ELSE 'PENDING_SUBMISSION'
END
as "status",
CASE
WHEN COUNT(CASE WHEN "order_eval_status" = 'COMPLETED' THEN 1 END) > 0 THEN 'COMPLETED'
WHEN COUNT(CASE WHEN "order_eval_status" = 'IN_PROGRESS' THEN 1 END) > 0 THEN 'IN_PROGRESS'
WHEN COUNT(CASE WHEN "order_eval_status" = 'NOT_STARTED' THEN 1 END) > 0 THEN 'NOT_STARTED'
ELSE 'NOT_ASSIGNED'
END
as "order_eval_status",
CASE
WHEN COUNT(CASE WHEN "procurement_eval_status" = 'COMPLETED' THEN 1 END) > 0 THEN 'COMPLETED'
WHEN COUNT(CASE WHEN "procurement_eval_status" = 'IN_PROGRESS' THEN 1 END) > 0 THEN 'IN_PROGRESS'
WHEN COUNT(CASE WHEN "procurement_eval_status" = 'NOT_STARTED' THEN 1 END) > 0 THEN 'NOT_STARTED'
ELSE 'NOT_ASSIGNED'
END
as "procurement_eval_status",
CASE
WHEN COUNT(CASE WHEN "quality_eval_status" = 'COMPLETED' THEN 1 END) > 0 THEN 'COMPLETED'
WHEN COUNT(CASE WHEN "quality_eval_status" = 'IN_PROGRESS' THEN 1 END) > 0 THEN 'IN_PROGRESS'
WHEN COUNT(CASE WHEN "quality_eval_status" = 'NOT_STARTED' THEN 1 END) > 0 THEN 'NOT_STARTED'
ELSE 'NOT_ASSIGNED'
END
as "quality_eval_status",
CASE
WHEN COUNT(CASE WHEN "design_eval_status" = 'COMPLETED' THEN 1 END) > 0 THEN 'COMPLETED'
WHEN COUNT(CASE WHEN "design_eval_status" = 'IN_PROGRESS' THEN 1 END) > 0 THEN 'IN_PROGRESS'
WHEN COUNT(CASE WHEN "design_eval_status" = 'NOT_STARTED' THEN 1 END) > 0 THEN 'NOT_STARTED'
ELSE 'NOT_ASSIGNED'
END
as "design_eval_status",
CASE
WHEN COUNT(CASE WHEN "cs_eval_status" = 'COMPLETED' THEN 1 END) > 0 THEN 'COMPLETED'
WHEN COUNT(CASE WHEN "cs_eval_status" = 'IN_PROGRESS' THEN 1 END) > 0 THEN 'IN_PROGRESS'
WHEN COUNT(CASE WHEN "cs_eval_status" = 'NOT_STARTED' THEN 1 END) > 0 THEN 'NOT_STARTED'
ELSE 'NOT_ASSIGNED'
END
as "cs_eval_status",
CASE
WHEN COUNT(CASE WHEN "admin_eval_status" = 'COMPLETED' THEN 1 END) > 0 THEN 'COMPLETED'
WHEN COUNT(CASE WHEN "admin_eval_status" = 'IN_PROGRESS' THEN 1 END) > 0 THEN 'IN_PROGRESS'
WHEN COUNT(CASE WHEN "admin_eval_status" = 'NOT_STARTED' THEN 1 END) > 0 THEN 'NOT_STARTED'
ELSE 'NOT_ASSIGNED'
END
as "admin_eval_status",
BOOL_AND("documents_submitted")
as "documents_submitted", MAX("submission_date") as "submission_date", MAX("submission_deadline") as "submission_deadline", MAX("review_completed_at") as "review_completed_at", MAX("finalized_at") as "finalized_at",
CASE
WHEN COUNT(DISTINCT "division") > 1 THEN 'BOTH'
ELSE MAX("division")
END
as "division", COUNT(*)::int as "evaluation_count", STRING_AGG(DISTINCT "division", ',') as "divisions", SUM("total_reviewers")::int as "total_reviewers", SUM("completed_reviewers")::int as "completed_reviewers", SUM("pending_reviewers")::int as "pending_reviewers", MAX("evaluation_period") as "evaluation_period", STRING_AGG("evaluation_note", ' | ') as "evaluation_note", (ARRAY_AGG("periodic_evaluations_view"."finalized_by" ORDER BY "periodic_evaluations_view"."finalized_at" DESC NULLS LAST))[1] as "finalized_by", (ARRAY_AGG("periodic_evaluations_view"."name" ORDER BY "periodic_evaluations_view"."finalized_at" DESC NULLS LAST))[1] as "finalized_by_user_name", (ARRAY_AGG("periodic_evaluations_view"."email" ORDER BY "periodic_evaluations_view"."finalized_at" DESC NULLS LAST))[1] as "finalized_by_user_email", MIN("created_at") as "created_at", MAX("updated_at") as "updated_at", (ARRAY_AGG("periodic_evaluations_view"."evaluation_target_id"))[1] as "evaluation_target_id",
STRING_AGG(DISTINCT "admin_comment", ' | ')
as "evaluation_target_admin_comment",
STRING_AGG(DISTINCT "consolidated_comment", ' | ')
as "evaluation_target_consolidated_comment", (ARRAY_AGG("periodic_evaluations_view"."consensus_status" ORDER BY "periodic_evaluations_view"."updated_at" DESC NULLS LAST))[1] as "evaluation_target_consensus_status",
MAX("confirmed_at")
as "evaluation_target_confirmed_at" from "periodic_evaluations_view" group by "periodic_evaluations_view"."evaluation_year", "periodic_evaluations_view"."vendor_id", "periodic_evaluations_view"."vendor_code", "periodic_evaluations_view"."vendor_name", "periodic_evaluations_view"."domestic_foreign", "periodic_evaluations_view"."material_type");
|
