77 lines
3.4 KiB
Transact-SQL
77 lines
3.4 KiB
Transact-SQL
CREATE OR ALTER PROCEDURE [dbo].[Hr_S_AttendanceImport]
|
||
@TenantId nvarchar(64),
|
||
@UserId nvarchar(64),
|
||
@Items nvarchar(MAX)
|
||
AS
|
||
BEGIN
|
||
SET NOCOUNT ON;
|
||
|
||
-- 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.
|
||
DECLARE @Tenant uniqueidentifier = TRY_CONVERT(uniqueidentifier, @TenantId),
|
||
@Actor uniqueidentifier = TRY_CONVERT(uniqueidentifier, @UserId);
|
||
|
||
IF @Items IS NULL OR ISJSON(@Items) <> 1
|
||
THROW 51210, N'Items geçerli bir JSON dizisi olmalıdır.', 1;
|
||
|
||
CREATE TABLE #aiItem
|
||
(
|
||
CardNo nvarchar(64) NULL, UserName nvarchar(256) NULL, EventTime datetime2 NULL,
|
||
Direction nvarchar(8) NULL, DeviceCode nvarchar(64) NULL, EmployeeId uniqueidentifier NULL
|
||
);
|
||
|
||
INSERT INTO #aiItem (CardNo, UserName, EventTime, Direction, DeviceCode)
|
||
SELECT COALESCE(NULLIF(LTRIM(RTRIM(CardNo)), N''), NULLIF(LTRIM(RTRIM(RegistrationNo)), N'')),
|
||
NULLIF(LTRIM(RTRIM(UserName)), N''),
|
||
TRY_CONVERT(datetime2, EventTime),
|
||
CASE WHEN Direction IN (N'In', N'Out') THEN Direction ELSE N'Auto' END,
|
||
DeviceCode
|
||
FROM OPENJSON(@Items)
|
||
WITH (
|
||
CardNo nvarchar(64) '$.CardNo',
|
||
RegistrationNo nvarchar(64) '$.RegistrationNo',
|
||
UserName nvarchar(256) '$.UserName',
|
||
EventTime nvarchar(40) '$.EventTime',
|
||
Direction nvarchar(8) '$.Direction',
|
||
DeviceCode nvarchar(64) '$.DeviceCode'
|
||
);
|
||
|
||
DECLARE @Received int = (SELECT COUNT(*) FROM #aiItem);
|
||
|
||
DELETE FROM #aiItem WHERE EventTime IS NULL OR (CardNo IS NULL AND UserName IS NULL);
|
||
|
||
UPDATE i
|
||
SET EmployeeId = u.Id
|
||
FROM #aiItem i
|
||
INNER JOIN AbpUsers u ON u.UserName = i.UserName AND u.IsDeleted = 0
|
||
AND (u.TenantId = @Tenant OR (u.TenantId IS NULL AND @Tenant IS NULL))
|
||
WHERE i.UserName IS NOT NULL;
|
||
|
||
DECLARE @Inserted TABLE (Id uniqueidentifier);
|
||
|
||
INSERT INTO Hr_T_AttendanceLog
|
||
(Id, TenantId, EmployeeId, CardNo, EventTime, Direction, Source, DeviceCode, IsProcessed, CreationTime, CreatorId, IsDeleted)
|
||
OUTPUT inserted.Id INTO @Inserted (Id)
|
||
SELECT NEWID(), @Tenant, n.EmployeeId, n.CardNo, n.EventTime, n.Direction, N'Pdks', n.DeviceCode, 0, GETUTCDATE(), @Actor, 0
|
||
FROM (
|
||
SELECT DISTINCT EmployeeId, CardNo, EventTime, Direction, DeviceCode FROM #aiItem
|
||
) n
|
||
WHERE NOT EXISTS (
|
||
SELECT 1 FROM Hr_T_AttendanceLog l
|
||
WHERE l.IsDeleted = 0 AND l.EventTime = n.EventTime
|
||
AND (l.TenantId = @Tenant OR (l.TenantId IS NULL AND @Tenant IS NULL))
|
||
AND ((n.EmployeeId IS NOT NULL AND l.EmployeeId = n.EmployeeId)
|
||
OR (n.EmployeeId IS NULL AND l.CardNo = n.CardNo)));
|
||
|
||
EXEC dbo.Hr_S_AttendanceProcess 0;
|
||
|
||
SELECT @Received AS Received,
|
||
(SELECT COUNT(*) FROM @Inserted) AS Inserted,
|
||
@Received - (SELECT COUNT(*) FROM @Inserted) AS Skipped,
|
||
(SELECT COUNT(*) FROM Hr_T_AttendanceLog l INNER JOIN @Inserted i ON i.Id = l.Id
|
||
WHERE l.ProcessError IS NOT NULL) AS Errors;
|
||
END
|
||
GO
|