diff options
Diffstat (limited to 'db/migrations/0200_shallow_sabretooth.sql')
| -rw-r--r-- | db/migrations/0200_shallow_sabretooth.sql | 268 |
1 files changed, 268 insertions, 0 deletions
diff --git a/db/migrations/0200_shallow_sabretooth.sql b/db/migrations/0200_shallow_sabretooth.sql new file mode 100644 index 00000000..250bb07d --- /dev/null +++ b/db/migrations/0200_shallow_sabretooth.sql @@ -0,0 +1,268 @@ +DROP VIEW "public"."periodic_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", ( + SELECT COALESCE(AVG(CAST(red.score AS DECIMAL(5,2))), 0) + FROM "reviewer_evaluation_details" red + JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id + JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id + JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id + WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" + AND rec.category2 = 'processScore' + AND re.is_completed = true + ) as "process_score", ( + SELECT COALESCE(AVG(CAST(red.score AS DECIMAL(5,2))), 0) + FROM "reviewer_evaluation_details" red + JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id + JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id + JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id + WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" + AND rec.category2 = 'priceScore' + AND re.is_completed = true + ) as "price_score", ( + SELECT COALESCE(AVG(CAST(red.score AS DECIMAL(5,2))), 0) + FROM "reviewer_evaluation_details" red + JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id + JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id + JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id + WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" + AND rec.category2 = 'deliveryScore' + AND re.is_completed = true + ) as "delivery_score", ( + SELECT COALESCE(AVG(CAST(red.score AS DECIMAL(5,2))), 0) + FROM "reviewer_evaluation_details" red + JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id + JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id + JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id + WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" + AND rec.category2 = 'selfEvaluationScore' + AND re.is_completed = true + ) as "self_evaluation_score", ( + SELECT COALESCE(AVG(CAST(red.score AS DECIMAL(5,2))), 0) + FROM "reviewer_evaluation_details" red + JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id + JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id + JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id + WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" + AND rec.category2 = 'bonus' + AND re.is_completed = true + ) as "participation_bonus", ( + SELECT COALESCE(AVG(CAST(red.score AS DECIMAL(5,2))), 0) + FROM "reviewer_evaluation_details" red + JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id + JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id + JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id + WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" + AND rec.category2 = 'penalty' + AND re.is_completed = true + ) as "quality_deduction", ( + SELECT COALESCE( + (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM "reviewer_evaluation_details" red JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" AND rec.category2 = 'processScore' AND re.is_completed = true), 0 + ) + + COALESCE( + (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM "reviewer_evaluation_details" red JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" AND rec.category2 = 'priceScore' AND re.is_completed = true), 0 + ) + + COALESCE( + (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM "reviewer_evaluation_details" red JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" AND rec.category2 = 'deliveryScore' AND re.is_completed = true), 0 + ) + + COALESCE( + (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM "reviewer_evaluation_details" red JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" AND rec.category2 = 'selfEvaluationScore' AND re.is_completed = true), 0 + ) + ) as "total_score", ( + SELECT ( + COALESCE( + (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM "reviewer_evaluation_details" red JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" AND rec.category2 = 'processScore' AND re.is_completed = true), 0 + ) + + COALESCE( + (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM "reviewer_evaluation_details" red JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" AND rec.category2 = 'priceScore' AND re.is_completed = true), 0 + ) + + COALESCE( + (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM "reviewer_evaluation_details" red JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" AND rec.category2 = 'deliveryScore' AND re.is_completed = true), 0 + ) + + COALESCE( + (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM "reviewer_evaluation_details" red JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" AND rec.category2 = 'selfEvaluationScore' AND re.is_completed = true), 0 + ) + + COALESCE( + (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM "reviewer_evaluation_details" red JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" AND rec.category2 = 'bonus' AND re.is_completed = true), 0 + ) - + COALESCE( + (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM "reviewer_evaluation_details" red JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" AND rec.category2 = 'penalty' AND re.is_completed = true), 0 + ) + ) + ) as "evaluation_score", ( + CASE + WHEN ( + COALESCE( + (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM "reviewer_evaluation_details" red JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" AND rec.category2 = 'processScore' AND re.is_completed = true), 0 + ) + + COALESCE( + (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM "reviewer_evaluation_details" red JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" AND rec.category2 = 'priceScore' AND re.is_completed = true), 0 + ) + + COALESCE( + (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM "reviewer_evaluation_details" red JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" AND rec.category2 = 'deliveryScore' AND re.is_completed = true), 0 + ) + + COALESCE( + (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM "reviewer_evaluation_details" red JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" AND rec.category2 = 'selfEvaluationScore' AND re.is_completed = true), 0 + ) + + COALESCE( + (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM "reviewer_evaluation_details" red JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" AND rec.category2 = 'bonus' AND re.is_completed = true), 0 + ) - + COALESCE( + (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM "reviewer_evaluation_details" red JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" AND rec.category2 = 'penalty' AND re.is_completed = true), 0 + ) + ) >= 90 THEN 'S' + WHEN ( + COALESCE( + (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM "reviewer_evaluation_details" red JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" AND rec.category2 = 'processScore' AND re.is_completed = true), 0 + ) + + COALESCE( + (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM "reviewer_evaluation_details" red JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" AND rec.category2 = 'priceScore' AND re.is_completed = true), 0 + ) + + COALESCE( + (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM "reviewer_evaluation_details" red JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" AND rec.category2 = 'deliveryScore' AND re.is_completed = true), 0 + ) + + COALESCE( + (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM "reviewer_evaluation_details" red JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" AND rec.category2 = 'selfEvaluationScore' AND re.is_completed = true), 0 + ) + + COALESCE( + (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM "reviewer_evaluation_details" red JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" AND rec.category2 = 'bonus' AND re.is_completed = true), 0 + ) - + COALESCE( + (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM "reviewer_evaluation_details" red JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" AND rec.category2 = 'penalty' AND re.is_completed = true), 0 + ) + ) >= 80 THEN 'A' + WHEN ( + COALESCE( + (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM "reviewer_evaluation_details" red JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" AND rec.category2 = 'processScore' AND re.is_completed = true), 0 + ) + + COALESCE( + (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM "reviewer_evaluation_details" red JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" AND rec.category2 = 'priceScore' AND re.is_completed = true), 0 + ) + + COALESCE( + (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM "reviewer_evaluation_details" red JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" AND rec.category2 = 'deliveryScore' AND re.is_completed = true), 0 + ) + + COALESCE( + (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM "reviewer_evaluation_details" red JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" AND rec.category2 = 'selfEvaluationScore' AND re.is_completed = true), 0 + ) + + COALESCE( + (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM "reviewer_evaluation_details" red JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" AND rec.category2 = 'bonus' AND re.is_completed = true), 0 + ) - + COALESCE( + (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM "reviewer_evaluation_details" red JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" AND rec.category2 = 'penalty' AND re.is_completed = true), 0 + ) + ) >= 70 THEN 'B' + WHEN ( + COALESCE( + (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM "reviewer_evaluation_details" red JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" AND rec.category2 = 'processScore' AND re.is_completed = true), 0 + ) + + COALESCE( + (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM "reviewer_evaluation_details" red JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" AND rec.category2 = 'priceScore' AND re.is_completed = true), 0 + ) + + COALESCE( + (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM "reviewer_evaluation_details" red JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" AND rec.category2 = 'deliveryScore' AND re.is_completed = true), 0 + ) + + COALESCE( + (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM "reviewer_evaluation_details" red JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" AND rec.category2 = 'selfEvaluationScore' AND re.is_completed = true), 0 + ) + + COALESCE( + (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM "reviewer_evaluation_details" red JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" AND rec.category2 = 'bonus' AND re.is_completed = true), 0 + ) - + COALESCE( + (SELECT AVG(CAST(red.score AS DECIMAL(5,2))) FROM "reviewer_evaluation_details" red JOIN "reg_eval_criteria_details" recd ON red.reg_eval_criteria_details_id = recd.id JOIN "reg_eval_criteria" rec ON recd.criteria_id = rec.id JOIN "reviewer_evaluations" re ON red.reviewer_evaluation_id = re.id WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" AND rec.category2 = 'penalty' AND re.is_completed = true), 0 + ) + ) >= 60 THEN 'C' + ELSE 'D' + END + ) as "evaluation_grade", "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"."admin_comment", "evaluation_targets"."consolidated_comment", "evaluation_targets"."consensus_status", "evaluation_targets"."confirmed_at", ( + SELECT CASE + WHEN re.id IS NULL THEN 'NOT_ASSIGNED' + WHEN re.is_completed = true THEN 'COMPLETED' + WHEN ( + SELECT COALESCE(SUM(CAST(red.score AS DECIMAL(5,2))), 0) + FROM "reviewer_evaluation_details" red + WHERE red.reviewer_evaluation_id = re.id + ) > 0 THEN 'IN_PROGRESS' + ELSE 'NOT_STARTED' + END + FROM "reviewer_evaluations" re + JOIN "evaluation_target_reviewers" etr ON re.evaluation_target_reviewer_id = etr.id + WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" + AND etr.department_code = 'ORDER_EVAL' + LIMIT 1 + ) as "order_eval_status", ( + SELECT CASE + WHEN re.id IS NULL THEN 'NOT_ASSIGNED' + WHEN re.is_completed = true THEN 'COMPLETED' + WHEN ( + SELECT COALESCE(SUM(CAST(red.score AS DECIMAL(5,2))), 0) + FROM "reviewer_evaluation_details" red + WHERE red.reviewer_evaluation_id = re.id + ) > 0 THEN 'IN_PROGRESS' + ELSE 'NOT_STARTED' + END + FROM "reviewer_evaluations" re + JOIN "evaluation_target_reviewers" etr ON re.evaluation_target_reviewer_id = etr.id + WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" + AND etr.department_code = 'PROCUREMENT_EVAL' + LIMIT 1 + ) as "procurement_eval_status", ( + SELECT CASE + WHEN re.id IS NULL THEN 'NOT_ASSIGNED' + WHEN re.is_completed = true THEN 'COMPLETED' + WHEN ( + SELECT COALESCE(SUM(CAST(red.score AS DECIMAL(5,2))), 0) + FROM "reviewer_evaluation_details" red + WHERE red.reviewer_evaluation_id = re.id + ) > 0 THEN 'IN_PROGRESS' + ELSE 'NOT_STARTED' + END + FROM "reviewer_evaluations" re + JOIN "evaluation_target_reviewers" etr ON re.evaluation_target_reviewer_id = etr.id + WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" + AND etr.department_code = 'QUALITY_EVAL' + LIMIT 1 + ) as "quality_eval_status", ( + SELECT CASE + WHEN re.id IS NULL THEN 'NOT_ASSIGNED' + WHEN re.is_completed = true THEN 'COMPLETED' + WHEN ( + SELECT COALESCE(SUM(CAST(red.score AS DECIMAL(5,2))), 0) + FROM "reviewer_evaluation_details" red + WHERE red.reviewer_evaluation_id = re.id + ) > 0 THEN 'IN_PROGRESS' + ELSE 'NOT_STARTED' + END + FROM "reviewer_evaluations" re + JOIN "evaluation_target_reviewers" etr ON re.evaluation_target_reviewer_id = etr.id + WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" + AND etr.department_code = 'DESIGN_EVAL' + LIMIT 1 + ) as "design_eval_status", ( + SELECT CASE + WHEN re.id IS NULL THEN 'NOT_ASSIGNED' + WHEN re.is_completed = true THEN 'COMPLETED' + WHEN ( + SELECT COALESCE(SUM(CAST(red.score AS DECIMAL(5,2))), 0) + FROM "reviewer_evaluation_details" red + WHERE red.reviewer_evaluation_id = re.id + ) > 0 THEN 'IN_PROGRESS' + ELSE 'NOT_STARTED' + END + FROM "reviewer_evaluations" re + JOIN "evaluation_target_reviewers" etr ON re.evaluation_target_reviewer_id = etr.id + WHERE re.periodic_evaluation_id = "periodic_evaluations"."id" + AND etr.department_code = 'CS_EVAL' + LIMIT 1 + ) as "cs_eval_status", ( + 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");
\ No newline at end of file |