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

CREATE OR ALTER VIEW [dbo].[Hr_V_LeaveBalanceYear]
AS
-- Yıllık izin defteri: çalışan başına her hak ediş yılı bir satır (İş Kanunu md.53, kademeler
-- İnsan Kaynakları ayarlarından). YearNo = 0 satırı devir/düzeltmeleri ve ilk hak edişten önce
-- kullanılan izni toplar; böylece satırların toplamı bakiyeyi verir.
WITH TenantCfg AS (
    SELECT t.TenantId,
           COALESCE(TRY_CAST(dbo.Hr_F_HrSetting(t.TenantId, N'Adm.HrSettings.Leave.AnnualDays1To5') AS decimal(5, 1)), 14) AS Days1To5,
           COALESCE(TRY_CAST(dbo.Hr_F_HrSetting(t.TenantId, N'Adm.HrSettings.Leave.AnnualDays5To15') AS decimal(5, 1)), 20) AS Days5To15,
           COALESCE(TRY_CAST(dbo.Hr_F_HrSetting(t.TenantId, N'Adm.HrSettings.Leave.AnnualDays15Plus') AS decimal(5, 1)), 26) AS Days15Plus,
           COALESCE(TRY_CAST(dbo.Hr_F_HrSetting(t.TenantId, N'Adm.HrSettings.Leave.MinDaysYoungOld') AS decimal(5, 1)), 20) AS MinDaysYoungOld
    FROM (SELECT DISTINCT TenantId FROM AbpUsers WHERE IsDeleted = 0) t
),
Emp AS (
    SELECT u.Id AS EmployeeId, u.TenantId,
           CAST(COALESCE(u.SeniorityStartDate, u.HireDate) AS date) AS SeniorityStartDate,
           CAST(u.BirthDate AS date) AS BirthDate,
           CAST(u.TerminationDate AS date) AS TerminationDate,
           c.Days1To5, c.Days5To15, c.Days15Plus, c.MinDaysYoungOld
    FROM AbpUsers u
    INNER JOIN TenantCfg c ON c.TenantId = u.TenantId OR (c.TenantId IS NULL AND u.TenantId IS NULL)
    WHERE u.IsDeleted = 0
),
Years AS (
    SELECT e.*, n.YearNo,
           DATEADD(year, n.YearNo, e.SeniorityStartDate) AS AccrualDate,
           DATEADD(year, n.YearNo + 1, e.SeniorityStartDate) AS NextPeriodDate
    FROM Emp e
    CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14), (15),
                       (16), (17), (18), (19), (20), (21), (22), (23), (24), (25), (26), (27), (28), (29), (30),
                       (31), (32), (33), (34), (35), (36), (37), (38), (39), (40), (41), (42), (43), (44), (45),
                       (46), (47), (48), (49), (50)) n (YearNo)
    WHERE e.SeniorityStartDate IS NOT NULL
),
Entitlement AS (
    SELECT y.*,
           CASE
               WHEN y.YearNo <= 5 THEN y.Days1To5
               WHEN y.YearNo < 15 THEN y.Days5To15
               ELSE y.Days15Plus
           END AS BaseDays,
           CASE
               WHEN y.BirthDate IS NULL THEN NULL
               ELSE DATEDIFF(year, y.BirthDate, y.AccrualDate)
                    - CASE WHEN DATEADD(year, DATEDIFF(year, y.BirthDate, y.AccrualDate), y.BirthDate) > y.AccrualDate THEN 1 ELSE 0 END
           END AS AgeAtAccrual
    FROM Years y
),
Accrued AS (
    SELECT e.EmployeeId, e.TenantId, e.YearNo, e.AccrualDate, e.NextPeriodDate,
           CASE
               WHEN (e.AgeAtAccrual <= 18 OR e.AgeAtAccrual >= 50) AND e.BaseDays < e.MinDaysYoungOld THEN e.MinDaysYoungOld
               ELSE e.BaseDays
           END AS EntitledDays,
           CASE WHEN e.AccrualDate <= CAST(GETDATE() AS date)
                 AND (e.TerminationDate IS NULL OR e.AccrualDate <= e.TerminationDate) THEN 1 ELSE 0 END AS IsAccrued
    FROM Entitlement e
),
NextAccrual AS (
    SELECT a.EmployeeId, MIN(a.AccrualDate) AS NextAccrualDate
    FROM Accrued a
    WHERE a.IsAccrued = 0
    GROUP BY a.EmployeeId
),
Requests AS (
    -- Yalnız yıllık izinden düşen türler; reddedilen talep bakiyeye girmez.
    SELECT r.EmployeeId, CAST(r.StartDate AS date) AS StartDate, r.Days,
           CASE WHEN r.ApprovalStatus = N'Onaylandı' THEN 1 ELSE 0 END AS IsApproved
    FROM Hr_T_LeaveRequest r
    INNER JOIN Hr_T_LeaveType t ON t.Id = r.LeaveTypeId
    WHERE r.IsDeleted = 0 AND t.DeductsFromAnnualLeave = 1
      AND (r.ApprovalStatus IS NULL OR r.ApprovalStatus <> N'Reddedildi')
),
Ledger AS (
    SELECT a.EmployeeId, a.TenantId, a.YearNo, a.AccrualDate, DATEADD(day, -1, a.NextPeriodDate) AS PeriodEndDate,
           a.EntitledDays,
           COALESCE((SELECT SUM(r.Days) FROM Requests r WHERE r.EmployeeId = a.EmployeeId AND r.IsApproved = 1
                       AND r.StartDate >= a.AccrualDate AND r.StartDate < a.NextPeriodDate), 0) AS UsedDays,
           COALESCE((SELECT SUM(r.Days) FROM Requests r WHERE r.EmployeeId = a.EmployeeId AND r.IsApproved = 0
                       AND r.StartDate >= a.AccrualDate AND r.StartDate < a.NextPeriodDate), 0) AS PendingDays
    FROM Accrued a
    WHERE a.IsAccrued = 1
    UNION ALL
    SELECT e.EmployeeId, e.TenantId, 0, NULL, NULL,
           COALESCE((SELECT SUM(x.Days) FROM Hr_T_LeaveBalanceAdjustment x
                     WHERE x.EmployeeId = e.EmployeeId AND x.IsDeleted = 0 AND x.IsActive = 1), 0),
           COALESCE((SELECT SUM(r.Days) FROM Requests r WHERE r.EmployeeId = e.EmployeeId AND r.IsApproved = 1
                       AND (e.SeniorityStartDate IS NULL OR r.StartDate < DATEADD(year, 1, e.SeniorityStartDate))), 0),
           COALESCE((SELECT SUM(r.Days) FROM Requests r WHERE r.EmployeeId = e.EmployeeId AND r.IsApproved = 0
                       AND (e.SeniorityStartDate IS NULL OR r.StartDate < DATEADD(year, 1, e.SeniorityStartDate))), 0)
    FROM Emp e
)
SELECT CONCAT(CONVERT(nvarchar(36), l.EmployeeId), N'-', l.YearNo) AS Id,
       l.TenantId,
       l.EmployeeId,
       CONCAT(u.Name, N' ', u.Surname) AS EmployeeName,
       u.DepartmentId,
       u.JobPositionId,
       l.YearNo,
       l.AccrualDate,
       l.PeriodEndDate,
       l.EntitledDays,
       l.UsedDays,
       l.PendingDays,
       l.EntitledDays - l.UsedDays AS RemainingDays,
       SUM(l.EntitledDays - l.UsedDays) OVER (PARTITION BY l.EmployeeId ORDER BY l.YearNo
                                              ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CumulativeRemainingDays,
       na.NextAccrualDate,
       (SELECT a.EntitledDays FROM Accrued a WHERE a.EmployeeId = l.EmployeeId AND a.AccrualDate = na.NextAccrualDate) AS NextAccrualDays
FROM Ledger l
INNER JOIN AbpUsers u ON u.Id = l.EmployeeId
LEFT JOIN NextAccrual na ON na.EmployeeId = l.EmployeeId
WHERE l.YearNo > 0 OR l.EntitledDays <> 0 OR l.UsedDays <> 0 OR l.PendingDays <> 0
GO