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

-- Custom Endpoint sonucu (Received/Inserted/Skipped/Errors) okuduğu için prosedür değil, satır dönen fonksiyondur.
CREATE OR REPLACE FUNCTION public."Hr_S_AttendanceImport"(
    p_TenantId varchar(64),
    p_UserId varchar(64),
    p_Items text
)
RETURNS TABLE ("Received" integer, "Inserted" integer, "Skipped" integer, "Errors" integer)
LANGUAGE plpgsql
AS $$
DECLARE
    v_Tenant uuid;
    v_Actor uuid;
    v_Json jsonb;
    v_Item jsonb;
    v_EventText text;
    v_EventTime timestamp;
    v_Received integer;
    v_Inserted integer;
    v_Errors integer;
BEGIN
    -- PDKS ucu (Custom Endpoint POST /hr/attendance/): gövdedeki Items JSON dizisini hareket olarak
    -- yazar. Her öğe: CardNo (kullanıcı kartındaki PDKS Kart No), RegistrationNo (Sicil No) ya da UserName;
    -- EventTime (yerel saat, ISO 8601), Direction (In/Out/Auto), DeviceCode. Kart/sicil eşlemesini
    -- Hr_S_AttendanceProcess yapar. Aynı kişi ve aynı zamanla daha önce gelmiş okutma atlanır; kapalı
    -- döneme düşen ve kartı tanımsız okutma hatalı işaretlenir, gönderimi durdurmaz.
    BEGIN
        v_Tenant := p_TenantId::uuid;
    EXCEPTION WHEN invalid_text_representation THEN
        v_Tenant := NULL;
    END;
    BEGIN
        v_Actor := p_UserId::uuid;
    EXCEPTION WHEN invalid_text_representation THEN
        v_Actor := NULL;
    END;

    -- ISJSON yalnız dizi ve nesneyi geçerli sayar; nesne tek öğe olarak okunur (OPENJSON ... WITH gibi).
    IF p_Items IS NOT NULL THEN
        BEGIN
            v_Json := p_Items::jsonb;
        EXCEPTION WHEN others THEN
            v_Json := NULL;
        END;
    END IF;
    IF v_Json IS NULL OR jsonb_typeof(v_Json) NOT IN ('array', 'object') THEN
        RAISE EXCEPTION 'Items geçerli bir JSON dizisi olmalıdır.' USING ERRCODE = 'P0001', DETAIL = '51210';
    END IF;
    IF jsonb_typeof(v_Json) = 'object' THEN
        v_Json := jsonb_build_array(v_Json);
    END IF;

    DROP TABLE IF EXISTS ai_item;
    CREATE TEMP TABLE ai_item
    (
        CardNo varchar(64) NULL, UserName varchar(256) NULL, EventTime timestamp NULL,
        Direction varchar(8) NULL, DeviceCode varchar(64) NULL, EmployeeId uuid NULL
    ) ON COMMIT DROP;

    DROP TABLE IF EXISTS ai_inserted;
    CREATE TEMP TABLE ai_inserted (Id uuid) ON COMMIT DROP;

    FOR v_Item IN SELECT e FROM jsonb_array_elements(v_Json) e LOOP
        -- Dizideki skaler öğe OPENJSON ... WITH'te bütün alanları boş bir satırdır.
        IF jsonb_typeof(v_Item) <> 'object' THEN
            v_Item := '{}'::jsonb;
        END IF;

        v_EventText := v_Item ->> 'EventTime';
        v_EventTime := NULL;
        IF v_EventText IS NOT NULL THEN
            BEGIN
                v_EventTime := v_EventText::timestamp;
            EXCEPTION WHEN others THEN
                v_EventTime := NULL;
            END;
        END IF;

        INSERT INTO ai_item (CardNo, UserName, EventTime, Direction, DeviceCode)
        VALUES (
            COALESCE(NULLIF(btrim(left(v_Item ->> 'CardNo', 64)), ''), NULLIF(btrim(left(v_Item ->> 'RegistrationNo', 64)), '')),
            NULLIF(btrim(left(v_Item ->> 'UserName', 256)), ''),
            v_EventTime,
            CASE WHEN lower(v_Item ->> 'Direction') IN ('in', 'out') THEN left(v_Item ->> 'Direction', 8) ELSE 'Auto' END,
            left(v_Item ->> 'DeviceCode', 64));
    END LOOP;

    v_Received := (SELECT COUNT(*) FROM ai_item)::integer;

    DELETE FROM ai_item WHERE EventTime IS NULL OR (CardNo IS NULL AND UserName IS NULL);

    UPDATE ai_item i
    SET EmployeeId = u."Id"
    FROM public."AbpUsers" u
    WHERE lower(u."UserName") = lower(i.UserName) AND u."IsDeleted" = FALSE
      AND (u."TenantId" = v_Tenant OR (u."TenantId" IS NULL AND v_Tenant IS NULL))
      AND i.UserName IS NOT NULL;

    WITH ins AS (
        INSERT INTO public."Hr_T_AttendanceLog"
            ("Id", "TenantId", "EmployeeId", "CardNo", "EventTime", "Direction", "Source", "DeviceCode", "IsProcessed", "CreationTime", "CreatorId", "IsDeleted")
        SELECT gen_random_uuid(), v_Tenant, n.EmployeeId, n.CardNo, n.EventTime, n.Direction, 'Pdks', n.DeviceCode, FALSE,
               (NOW() AT TIME ZONE 'UTC'), v_Actor, FALSE
        FROM (
            SELECT DISTINCT a.EmployeeId, a.CardNo, a.EventTime, a.Direction, a.DeviceCode FROM ai_item a
        ) n
        WHERE NOT EXISTS (
            SELECT 1 FROM public."Hr_T_AttendanceLog" l
            WHERE l."IsDeleted" = FALSE AND l."EventTime" = n.EventTime
              AND (l."TenantId" = v_Tenant OR (l."TenantId" IS NULL AND v_Tenant IS NULL))
              AND ((n.EmployeeId IS NOT NULL AND l."EmployeeId" = n.EmployeeId)
                OR (n.EmployeeId IS NULL AND lower(l."CardNo") = lower(n.CardNo))))
        RETURNING "Id"
    )
    INSERT INTO ai_inserted (Id)
    SELECT ins."Id" FROM ins;

    CALL public."Hr_S_AttendanceProcess"(FALSE);

    v_Inserted := (SELECT COUNT(*) FROM ai_inserted)::integer;
    v_Errors := (SELECT COUNT(*) FROM public."Hr_T_AttendanceLog" l INNER JOIN ai_inserted i ON i.Id = l."Id"
                 WHERE l."ProcessError" IS NOT NULL)::integer;

    RETURN QUERY SELECT v_Received, v_Inserted, v_Received - v_Inserted, v_Errors;
END;
$$;