113 lines
No EOL
6.2 KiB
Transact-SQL
113 lines
No EOL
6.2 KiB
Transact-SQL
CREATE OR ALTER PROCEDURE [dbo].[Hr_S_PerformanceReviewAfterSave]
|
||
@ReviewId uniqueidentifier,
|
||
@UserId uniqueidentifier,
|
||
@Actor nvarchar(16)
|
||
AS
|
||
BEGIN
|
||
SET NOCOUNT ON;
|
||
|
||
DECLARE @Status nvarchar(16), @Last nvarchar(16), @EmployeeId uniqueidentifier, @ManagerId uniqueidentifier, @TenantId uniqueidentifier;
|
||
|
||
SELECT @Status = Status, @Last = COALESCE(LastStatus, N'GoalSetting'), @EmployeeId = EmployeeId, @ManagerId = ManagerId, @TenantId = TenantId
|
||
FROM Hr_T_PerformanceReview
|
||
WHERE Id = @ReviewId AND IsDeleted = 0;
|
||
|
||
IF @Status IS NULL
|
||
RETURN;
|
||
|
||
-- Elle açılan değerlendirmede organizasyon bilgisi çalışandan, yetkinlikler dönemin setinden gelir.
|
||
-- Ad, pano kartının başlığı olduğu için kayıtta metin olarak tutulur.
|
||
UPDATE rv
|
||
SET EmployeeName = LEFT(LTRIM(COALESCE(u.Name, N'') + N' ' + COALESCE(u.Surname, N'')), 128),
|
||
DepartmentId = COALESCE(rv.DepartmentId, u.DepartmentId),
|
||
JobPositionId = COALESCE(rv.JobPositionId, u.JobPositionId)
|
||
FROM Hr_T_PerformanceReview rv
|
||
INNER JOIN AbpUsers u ON u.Id = rv.EmployeeId
|
||
WHERE rv.Id = @ReviewId;
|
||
|
||
INSERT INTO Hr_T_PerformanceCompetency (Id, TenantId, ReviewId, SkillId, ExpectedLevelId, IsActive, CreationTime, CreatorId, IsDeleted)
|
||
SELECT NEWID(), rv.TenantId, rv.Id, ps.SkillId, ps.ExpectedLevelId, 1, GETUTCDATE(), @UserId, 0
|
||
FROM Hr_T_PerformanceReview rv
|
||
INNER JOIN Hr_T_PerformancePeriodSkill ps ON ps.PeriodId = rv.PeriodId AND ps.IsDeleted = 0 AND ps.IsActive = 1
|
||
WHERE rv.Id = @ReviewId AND rv.Status <> N'Completed'
|
||
AND NOT EXISTS (
|
||
SELECT 1 FROM Hr_T_PerformanceCompetency c
|
||
WHERE c.ReviewId = rv.Id AND c.SkillId = ps.SkillId AND c.IsDeleted = 0);
|
||
|
||
IF @Status <> @Last
|
||
BEGIN
|
||
-- İK her geçişi yapar; çalışan öz değerlendirmeyi gönderir; yönetici hedefleri onaylar,
|
||
-- değerlendirmesini kalibrasyona gönderir ya da kaydı bir önceki aşamaya iade eder.
|
||
DECLARE @Valid bit = CASE
|
||
WHEN @Actor = N'Hr' THEN 1
|
||
WHEN @Actor = N'Employee' AND @Last = N'SelfReview' AND @Status = N'ManagerReview' THEN 1
|
||
WHEN @Actor = N'Manager' AND @Last = N'GoalSetting' AND @Status = N'SelfReview' THEN 1
|
||
WHEN @Actor = N'Manager' AND @Last = N'SelfReview' AND @Status = N'GoalSetting' THEN 1
|
||
WHEN @Actor = N'Manager' AND @Last = N'ManagerReview' AND @Status IN (N'SelfReview', N'Calibration') THEN 1
|
||
ELSE 0
|
||
END;
|
||
|
||
-- Hedef belirleme ancak ağırlıkları toplamı 100 olan en az bir hedefle kapanır.
|
||
IF @Valid = 1 AND @Last = N'GoalSetting' AND @Status <> N'GoalSetting' AND NOT EXISTS (
|
||
SELECT 1 FROM Hr_T_PerformanceGoal g
|
||
WHERE g.ReviewId = @ReviewId AND g.IsDeleted = 0
|
||
HAVING SUM(g.Weight) = 100)
|
||
SET @Valid = 0;
|
||
|
||
IF @Valid = 0
|
||
BEGIN
|
||
UPDATE Hr_T_PerformanceReview SET Status = @Last WHERE Id = @ReviewId;
|
||
SET @Status = @Last;
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
UPDATE Hr_T_PerformanceReview
|
||
SET LastStatus = @Status,
|
||
SelfSubmittedDate = IIF(@Status = N'ManagerReview', GETUTCDATE(), SelfSubmittedDate),
|
||
ManagerSubmittedDate = IIF(@Status = N'Calibration', GETUTCDATE(), ManagerSubmittedDate),
|
||
CompletedDate = IIF(@Status = N'Completed', GETUTCDATE(), NULL)
|
||
WHERE Id = @ReviewId;
|
||
|
||
IF @Status IN (N'GoalSetting', N'SelfReview', N'Completed')
|
||
EXEC dbo.Hr_S_PerformanceNotify @ReviewId, @EmployeeId, N'PerformansDurumDegisti',
|
||
N'Hr.Performance.Notification.Status.Subject', N'Hr.Performance.Notification.Status.Message', @UserId;
|
||
|
||
IF @Status IN (N'ManagerReview', N'Completed')
|
||
EXEC dbo.Hr_S_PerformanceNotify @ReviewId, @ManagerId, N'PerformansDurumDegisti',
|
||
N'Hr.Performance.Notification.Status.Subject', N'Hr.Performance.Notification.Status.Message', @UserId;
|
||
|
||
-- Tamamlanan değerlendirmenin yönetici seviyeleri çalışanın yetkinlik profiline yazılır
|
||
-- (Progress 0–100 → UserSkill.Level 1–5).
|
||
IF @Status = N'Completed'
|
||
BEGIN
|
||
UPDATE us
|
||
SET [Level] = x.[Level], Note = LEFT(N'Performans: ' + p.Name, 512),
|
||
LastModificationTime = GETUTCDATE(), LastModifierId = @UserId
|
||
FROM Adm_T_UserSkill us
|
||
INNER JOIN Hr_T_PerformanceCompetency c ON c.SkillId = us.SkillId AND c.ReviewId = @ReviewId AND c.IsDeleted = 0
|
||
INNER JOIN Hr_T_SkillLevel l ON l.Id = c.ManagerLevelId
|
||
INNER JOIN Hr_T_PerformanceReview rv ON rv.Id = c.ReviewId
|
||
INNER JOIN Hr_T_PerformancePeriod p ON p.Id = rv.PeriodId
|
||
CROSS APPLY (SELECT IIF(l.Progress <= 20, 1, IIF(l.Progress >= 100, 5, CAST(CEILING(l.Progress / 20.0) AS int))) AS [Level]) x
|
||
WHERE us.UserId = @EmployeeId AND us.IsDeleted = 0
|
||
AND (us.TenantId = @TenantId OR (us.TenantId IS NULL AND @TenantId IS NULL));
|
||
|
||
INSERT INTO Adm_T_UserSkill (Id, TenantId, UserId, SkillId, [Level], Note, CreationTime, CreatorId, IsDeleted)
|
||
SELECT NEWID(), @TenantId, @EmployeeId, c.SkillId, x.[Level], LEFT(N'Performans: ' + p.Name, 512), GETUTCDATE(), @UserId, 0
|
||
FROM Hr_T_PerformanceCompetency c
|
||
INNER JOIN Hr_T_SkillLevel l ON l.Id = c.ManagerLevelId
|
||
INNER JOIN Hr_T_PerformanceReview rv ON rv.Id = c.ReviewId
|
||
INNER JOIN Hr_T_PerformancePeriod p ON p.Id = rv.PeriodId
|
||
CROSS APPLY (SELECT IIF(l.Progress <= 20, 1, IIF(l.Progress >= 100, 5, CAST(CEILING(l.Progress / 20.0) AS int))) AS [Level]) x
|
||
WHERE c.ReviewId = @ReviewId AND c.IsDeleted = 0
|
||
AND NOT EXISTS (
|
||
SELECT 1 FROM Adm_T_UserSkill us
|
||
WHERE us.UserId = @EmployeeId AND us.SkillId = c.SkillId AND us.IsDeleted = 0
|
||
AND (us.TenantId = @TenantId OR (us.TenantId IS NULL AND @TenantId IS NULL)));
|
||
END
|
||
END
|
||
END
|
||
|
||
EXEC dbo.Hr_S_PerformanceReviewRecalc @ReviewId;
|
||
END
|
||
GO |