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

CREATE OR ALTER FUNCTION [dbo].[Hr_F_LeaveWorkDays]
(
    @TenantId uniqueidentifier,
    @StartDate date,
    @EndDate date
)
RETURNS decimal(9, 1)
AS
BEGIN
    -- İzin günü = ayarlarda çalışma günü işaretli olup Çalışma Takvimi'nde tatil olmayan gün.
    -- Yarım gün (arife) 0,5 sayılır. Yıllık tekrarlı satır başlangıç yılından itibaren her yıl
    -- aynı gün/ay aralığına düşer (29 Şubat artık olmayan yılda 28 Şubat'a kayar).
    IF @StartDate IS NULL OR @EndDate IS NULL OR @EndDate < @StartDate
        RETURN 0;

    -- Gün sırası Pazartesi = 0 (DATEFIRST ayarından bağımsız). Ayar tanımı yoksa Pzt–Cum.
    DECLARE @Work TABLE (DayIndex int PRIMARY KEY, IsWork bit);
    INSERT INTO @Work (DayIndex, IsWork)
    SELECT d.DayIndex,
           CASE
               WHEN s.Value IS NULL THEN CASE WHEN d.DayIndex < 5 THEN 1 ELSE 0 END
               WHEN LOWER(s.Value) = N'true' THEN 1
               ELSE 0
           END
    FROM (VALUES
        (0, N'Adm.HrSettings.Leave.WorkMonday'),
        (1, N'Adm.HrSettings.Leave.WorkTuesday'),
        (2, N'Adm.HrSettings.Leave.WorkWednesday'),
        (3, N'Adm.HrSettings.Leave.WorkThursday'),
        (4, N'Adm.HrSettings.Leave.WorkFriday'),
        (5, N'Adm.HrSettings.Leave.WorkSaturday'),
        (6, N'Adm.HrSettings.Leave.WorkSunday')) d (DayIndex, SettingName)
    CROSS APPLY (SELECT dbo.Hr_F_HrSetting(@TenantId, d.SettingName) AS Value) s;

    DECLARE @Total decimal(9, 1) = 0;
    DECLARE @Day date = @StartDate;
    DECLARE @Value decimal(3, 1);

    WHILE @Day <= @EndDate
    BEGIN
        SET @Value = 0;

        IF EXISTS (SELECT 1 FROM @Work WHERE DayIndex = DATEDIFF(day, '19000101', @Day) % 7 AND IsWork = 1)
        BEGIN
            SET @Value = 1;

            SELECT @Value = CASE WHEN MAX(CASE WHEN c.DayType = N'Holiday' THEN 1 ELSE 0 END) = 1 THEN 0 ELSE 0.5 END
            FROM Hr_T_WorkCalendar c
            WHERE c.IsDeleted = 0 AND c.IsActive = 1
              AND (c.TenantId = @TenantId OR (c.TenantId IS NULL AND @TenantId IS NULL))
              AND (
                    ((c.RecurrenceRule IS NULL OR c.RecurrenceRule = N'') AND @Day BETWEEN c.StartDate AND c.EndDate)
                 OR (c.RecurrenceRule = N'FREQ=YEARLY' AND YEAR(@Day) >= YEAR(c.StartDate)
                     AND @Day BETWEEN DATEADD(year, YEAR(@Day) - YEAR(c.StartDate), c.StartDate)
                                  AND DATEADD(year, YEAR(@Day) - YEAR(c.StartDate), c.EndDate))
                  )
            HAVING COUNT(*) > 0;
        END

        SET @Total += @Value;
        SET @Day = DATEADD(day, 1, @Day);
    END

    RETURN @Total;
END
GO