86 lines
3.9 KiB
PL/PgSQL
86 lines
3.9 KiB
PL/PgSQL
CREATE OR REPLACE PROCEDURE public."Hr_S_PerformanceReviewRecalc"(
|
||
p_ReviewId uuid
|
||
)
|
||
LANGUAGE plpgsql
|
||
AS $$
|
||
BEGIN
|
||
IF p_ReviewId IS NULL THEN
|
||
RETURN;
|
||
END IF;
|
||
|
||
-- Gerçekleşme %: Artan hedefte Gerçekleşen/Hedef, Azalan hedefte Hedef/Gerçekleşen; 0–150'ye kırpılır.
|
||
-- Azalan hedefte sıfır gerçekleşme hedefin tamamen tutturulduğu anlamına gelir, tavan verilir.
|
||
UPDATE "Hr_T_PerformanceGoal" g
|
||
SET "AchievementRate" = (
|
||
SELECT CASE
|
||
WHEN x.rate IS NULL THEN NULL
|
||
WHEN x.rate > 150 THEN 150
|
||
WHEN x.rate < 0 THEN 0
|
||
ELSE x.rate
|
||
END
|
||
FROM (SELECT CAST(CASE
|
||
WHEN g."ActualValue" IS NULL THEN NULL
|
||
WHEN g."Direction" = 'Decreasing' AND g."ActualValue" <= 0 THEN 150
|
||
WHEN g."Direction" = 'Decreasing' THEN g."TargetValue" / g."ActualValue" * 100
|
||
ELSE g."ActualValue" / g."TargetValue" * 100
|
||
END AS numeric(18, 2)) AS rate) x)
|
||
WHERE g."ReviewId" = p_ReviewId AND g."IsDeleted" = FALSE;
|
||
|
||
-- Yetkinlik puanı yöneticinin seçtiği seviyenin Progress değeridir.
|
||
UPDATE "Hr_T_PerformanceCompetency" c
|
||
SET "Score" = (
|
||
SELECT l."Progress"
|
||
FROM "Hr_T_SkillLevel" l
|
||
WHERE l."Id" = c."ManagerLevelId" AND l."IsDeleted" = FALSE)
|
||
WHERE c."ReviewId" = p_ReviewId AND c."IsDeleted" = FALSE;
|
||
|
||
UPDATE "Hr_T_PerformanceFeedback" f
|
||
SET "Score" = (
|
||
SELECT CAST(AVG(CAST(l."Progress" AS numeric(9, 2))) AS numeric(6, 2))
|
||
FROM "Hr_T_PerformanceFeedbackScore" s
|
||
INNER JOIN "Hr_T_SkillLevel" l ON l."Id" = s."LevelId" AND l."IsDeleted" = FALSE
|
||
WHERE s."FeedbackId" = f."Id" AND s."IsDeleted" = FALSE)
|
||
WHERE f."ReviewId" = p_ReviewId AND f."IsDeleted" = FALSE;
|
||
|
||
UPDATE "Hr_T_PerformanceReview" rv
|
||
SET "GoalScore" = s.goal_score,
|
||
"CompetencyScore" = s.competency_score,
|
||
"Feedback360Score" = s.feedback_score,
|
||
"OverallScore" = CASE
|
||
WHEN s.goal_score IS NULL AND s.competency_score IS NULL THEN NULL
|
||
ELSE CAST((COALESCE(s.goal_score, 0) * s.goal_weight + COALESCE(s.competency_score, 0) * s.competency_weight) / 100 AS numeric(6, 2))
|
||
END
|
||
FROM (
|
||
SELECT r."Id",
|
||
p."GoalWeight" AS goal_weight,
|
||
p."CompetencyWeight" AS competency_weight,
|
||
(SELECT CASE WHEN COUNT(*) = 0 THEN NULL
|
||
ELSE CAST(SUM(COALESCE(g."AchievementRate", 0) * g."Weight") / 100 AS numeric(6, 2)) END
|
||
FROM "Hr_T_PerformanceGoal" g
|
||
WHERE g."ReviewId" = r."Id" AND g."IsDeleted" = FALSE) AS goal_score,
|
||
(SELECT CAST(AVG(c."Score") AS numeric(6, 2))
|
||
FROM "Hr_T_PerformanceCompetency" c
|
||
WHERE c."ReviewId" = r."Id" AND c."IsDeleted" = FALSE AND c."Score" IS NOT NULL) AS competency_score,
|
||
(SELECT CAST(AVG(f."Score") AS numeric(6, 2))
|
||
FROM "Hr_T_PerformanceFeedback" f
|
||
WHERE f."ReviewId" = r."Id" AND f."IsDeleted" = FALSE AND f."Status" = 'Submitted' AND f."Score" IS NOT NULL) AS feedback_score
|
||
FROM "Hr_T_PerformanceReview" r
|
||
INNER JOIN "Hr_T_PerformancePeriod" p ON p."Id" = r."PeriodId"
|
||
WHERE r."Id" = p_ReviewId
|
||
) s
|
||
WHERE rv."Id" = s."Id";
|
||
|
||
-- Derece kalibre puandan, yoksa genel puandan; aralık alt sınırı dahil, üst sınırı hariçtir.
|
||
UPDATE "Hr_T_PerformanceReview" rv
|
||
SET "RatingId" = (
|
||
SELECT rt."Id"
|
||
FROM "Hr_T_PerformanceRating" rt
|
||
WHERE rt."IsDeleted" = FALSE AND rt."IsActive" = TRUE
|
||
AND (rt."TenantId" = rv."TenantId" OR (rt."TenantId" IS NULL AND rv."TenantId" IS NULL))
|
||
AND COALESCE(rv."CalibratedScore", rv."OverallScore") >= rt."MinScore"
|
||
AND COALESCE(rv."CalibratedScore", rv."OverallScore") < rt."MaxScore"
|
||
ORDER BY rt."Level" DESC
|
||
LIMIT 1)
|
||
WHERE rv."Id" = p_ReviewId;
|
||
END
|
||
$$;
|