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

CREATE OR REPLACE FUNCTION public."Hr_F_LeaveWorkDays"
(
    p_TenantId uuid,
    p_StartDate date,
    p_EndDate date
)
RETURNS numeric(9, 1)
LANGUAGE plpgsql
STABLE
AS $$
DECLARE
    v_Work boolean[];
    v_Total numeric(9, 1) := 0;
    v_Day date := p_StartDate;
    v_Value numeric(3, 1);
    v_Calendar numeric(3, 1);
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 p_StartDate IS NULL OR p_EndDate IS NULL OR p_EndDate < p_StartDate THEN
        RETURN 0;
    END IF;

    -- Gün sırası Pazartesi = 1 (ISODOW). Ayar tanımı yoksa Pzt–Cum.
    SELECT array_agg(
               CASE
                   WHEN s.v IS NULL THEN d.DayIndex <= 5
                   WHEN lower(s.v) = 'true' THEN TRUE
                   ELSE FALSE
               END
               ORDER BY d.DayIndex)
    INTO v_Work
    FROM (VALUES
        (1, 'Adm.HrSettings.Leave.WorkMonday'),
        (2, 'Adm.HrSettings.Leave.WorkTuesday'),
        (3, 'Adm.HrSettings.Leave.WorkWednesday'),
        (4, 'Adm.HrSettings.Leave.WorkThursday'),
        (5, 'Adm.HrSettings.Leave.WorkFriday'),
        (6, 'Adm.HrSettings.Leave.WorkSaturday'),
        (7, 'Adm.HrSettings.Leave.WorkSunday')) d (DayIndex, SettingName)
    CROSS JOIN LATERAL (SELECT public."Hr_F_HrSetting"(p_TenantId, d.SettingName) AS v) s;

    WHILE v_Day <= p_EndDate LOOP
        v_Value := 0;

        IF v_Work[EXTRACT(ISODOW FROM v_Day)::int] THEN
            v_Value := 1;

            SELECT CASE WHEN MAX(CASE WHEN c."DayType" = 'Holiday' THEN 1 ELSE 0 END) = 1 THEN 0 ELSE 0.5 END
            INTO v_Calendar
            FROM "Hr_T_WorkCalendar" c
            WHERE c."IsDeleted" = FALSE AND c."IsActive" = TRUE
              AND c."TenantId" IS NOT DISTINCT FROM p_TenantId
              AND (
                    ((c."RecurrenceRule" IS NULL OR c."RecurrenceRule" = '') AND v_Day BETWEEN c."StartDate" AND c."EndDate")
                 OR (c."RecurrenceRule" = 'FREQ=YEARLY'
                     AND EXTRACT(YEAR FROM v_Day) >= EXTRACT(YEAR FROM c."StartDate")
                     AND v_Day BETWEEN (c."StartDate" + make_interval(years => (EXTRACT(YEAR FROM v_Day) - EXTRACT(YEAR FROM c."StartDate"))::int))::date
                                  AND (c."EndDate" + make_interval(years => (EXTRACT(YEAR FROM v_Day) - EXTRACT(YEAR FROM c."StartDate"))::int))::date)
                  )
            HAVING COUNT(*) > 0;

            -- Takvimde satır yoksa gün tam sayılır (SELECT INTO satırsızken değişkeni NULL yapar).
            IF FOUND THEN
                v_Value := v_Calendar;
            END IF;
        END IF;

        v_Total := v_Total + v_Value;
        v_Day := v_Day + 1;
    END LOOP;

    RETURN v_Total;
END;
$$;