125 lines
5.1 KiB
PL/PgSQL
125 lines
5.1 KiB
PL/PgSQL
-- 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;
|
||
$$;
|