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