From c657ef972feeafff16ab0e07cb4771f7dd141ba0 Mon Sep 17 00:00:00 2001
From: dujinkim <dujin.kim@dtsolution.co.kr>
Date: Thu, 10 Jul 2025 09:55:45 +0000
Subject: (대표님) 20250710 작업사항 - 평가 첨부, 로그인, SEDP 변경 요구사항
 반영
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

---
 db/migrations/0206_tan_nomad.sql | 269 +++++++++++++++++++++++++++++++++++++++
 1 file changed, 269 insertions(+)
 create mode 100644 db/migrations/0206_tan_nomad.sql

(limited to 'db/migrations/0206_tan_nomad.sql')

diff --git a/db/migrations/0206_tan_nomad.sql b/db/migrations/0206_tan_nomad.sql
new file mode 100644
index 00000000..f191ea68
--- /dev/null
+++ b/db/migrations/0206_tan_nomad.sql
@@ -0,0 +1,269 @@
+CREATE TABLE "reviewer_evaluation_attachments" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"reviewer_evaluation_detail_id" integer NOT NULL,
+	"original_file_name" varchar(255) NOT NULL,
+	"stored_file_name" varchar(255) NOT NULL,
+	"file_path" varchar(500) NOT NULL,
+	"public_path" varchar(500) NOT NULL,
+	"file_size" integer NOT NULL,
+	"mime_type" varchar(100),
+	"file_extension" varchar(10),
+	"description" text,
+	"uploaded_by" integer NOT NULL,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+DROP VIEW "public"."periodic_evaluations_view";--> statement-breakpoint
+ALTER TABLE "vendor_contacts" ADD COLUMN "contact_department" varchar(100);--> statement-breakpoint
+ALTER TABLE "vendor_contacts" ADD COLUMN "contact_task" varchar(100);--> statement-breakpoint
+ALTER TABLE "reviewer_evaluation_attachments" ADD CONSTRAINT "reviewer_evaluation_attachments_reviewer_evaluation_detail_id_reviewer_evaluation_details_id_fk" FOREIGN KEY ("reviewer_evaluation_detail_id") REFERENCES "public"."reviewer_evaluation_details"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "reviewer_evaluation_attachments" ADD CONSTRAINT "reviewer_evaluation_attachments_uploaded_by_users_id_fk" FOREIGN KEY ("uploaded_by") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
+CREATE INDEX "reviewer_evaluation_detail_id_idx" ON "reviewer_evaluation_attachments" USING btree ("reviewer_evaluation_detail_id");--> 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
+            )
+          ) >= 95 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
+            )
+          ) >= 90 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
-- 
cgit v1.2.3

