diff options
Diffstat (limited to 'db/migrations/0228_rainy_sabretooth.sql')
| -rw-r--r-- | db/migrations/0228_rainy_sabretooth.sql | 121 |
1 files changed, 121 insertions, 0 deletions
diff --git a/db/migrations/0228_rainy_sabretooth.sql b/db/migrations/0228_rainy_sabretooth.sql new file mode 100644 index 00000000..8d5745d8 --- /dev/null +++ b/db/migrations/0228_rainy_sabretooth.sql @@ -0,0 +1,121 @@ +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", + CASE + WHEN COUNT(CASE WHEN "documents_submitted" = true THEN 1 END) = COUNT(*) THEN true + ELSE false + END + 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("finalized_by" ORDER BY "finalized_at" DESC NULLS LAST))[1] + as "finalized_by", + (ARRAY_AGG("name" ORDER BY "finalized_at" DESC NULLS LAST))[1] + as "finalized_by_user_name", + (ARRAY_AGG("email" ORDER BY "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("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", + MAX("consensus_status") + 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 |