sozsoft-platform/configs/seeds/sql/object/Hr_S_PerformancePeriodActivate.sql

CREATE OR ALTER PROCEDURE [dbo].[Hr_S_PerformancePeriodActivate]
    @PeriodId uniqueidentifier,
    @ActorId uniqueidentifier = NULL
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @TenantId uniqueidentifier;

    SELECT @TenantId = TenantId
    FROM Hr_T_PerformancePeriod
    WHERE Id = @PeriodId AND IsDeleted = 0 AND Status = N'Active';

    IF @@ROWCOUNT = 0
        RETURN;

    DECLARE @Created TABLE (Id uniqueidentifier, EmployeeId uniqueidentifier);

    -- Dönem aktif olduğunda kiracının aktif her kullanıcısına bir değerlendirme açılır; var olana
    -- dokunulmaz, böylece dönem yeniden kaydedildiğinde yalnızca yeni katılan çalışanlar eklenir.
    -- Şube: çalışanın departman şubesi, yoksa çalışana atanmış ilk şube, yoksa işlemi yapanın ilk şubesi (kolon NOT NULL).
    INSERT INTO Hr_T_PerformanceReview (Id, TenantId, BranchId, PeriodId, EmployeeId, EmployeeName, DepartmentId, JobPositionId, Status, LastStatus, IsActive, CreationTime, CreatorId, IsDeleted)
    OUTPUT inserted.Id, inserted.EmployeeId INTO @Created
    SELECT NEWID(), @TenantId, COALESCE((SELECT d.BranchId FROM Hr_T_Department d WHERE d.Id = u.DepartmentId), (SELECT TOP 1 b.Id FROM Sas_T_Branch b INNER JOIN Sas_T_BranchUsers bu ON bu.BranchId = b.Id AND bu.UserId = u.Id WHERE b.IsDeleted = 0 AND b.IsActive = 1 ORDER BY b.Name), (SELECT TOP 1 b.Id FROM Sas_T_Branch b INNER JOIN Sas_T_BranchUsers bu ON bu.BranchId = b.Id AND bu.UserId = @ActorId WHERE b.IsDeleted = 0 AND b.IsActive = 1 ORDER BY b.Name)), @PeriodId, u.Id, LEFT(LTRIM(COALESCE(u.Name, N'') + N' ' + COALESCE(u.Surname, N'')), 128), u.DepartmentId, u.JobPositionId, N'GoalSetting', N'GoalSetting', 1, GETUTCDATE(), @ActorId, 0
    FROM AbpUsers u
    WHERE u.IsDeleted = 0 AND u.IsActive = 1
      AND (u.TenantId = @TenantId OR (u.TenantId IS NULL AND @TenantId IS NULL))
      AND NOT EXISTS (
          SELECT 1 FROM Hr_T_PerformanceReview rv
          WHERE rv.PeriodId = @PeriodId AND rv.EmployeeId = u.Id AND rv.IsDeleted = 0);

    -- Dönemin yetkinlik seti eksik olan her değerlendirmeye kopyalanır (sonradan eklenen yetkinlik dahil).
    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(), @ActorId, 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.PeriodId = @PeriodId AND rv.IsDeleted = 0 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);

    DECLARE @ReviewId uniqueidentifier, @EmployeeId uniqueidentifier;
    DECLARE created CURSOR LOCAL FAST_FORWARD FOR SELECT Id, EmployeeId FROM @Created;
    OPEN created;
    FETCH NEXT FROM created INTO @ReviewId, @EmployeeId;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC dbo.Hr_S_PerformanceNotify @ReviewId, @EmployeeId, N'PerformansDurumDegisti',
            N'Hr.Performance.Notification.Status.Subject', N'Hr.Performance.Notification.Status.Message', @ActorId;
        FETCH NEXT FROM created INTO @ReviewId, @EmployeeId;
    END
    CLOSE created;
    DEALLOCATE created;
END
GO