96 lines
4.2 KiB
Transact-SQL
96 lines
4.2 KiB
Transact-SQL
CREATE OR ALTER PROCEDURE [dbo].[Hr_S_AttendanceProcess]
|
||
@FailOnClosed bit = 1
|
||
AS
|
||
BEGIN
|
||
SET NOCOUNT ON;
|
||
|
||
-- İşlenmemiş hareketleri (ekran, Excel import, PDKS ucu; silinenler dahil) işler: çalışanı kullanıcı
|
||
-- kartındaki PDKS Kart No'dan (yoksa Sicil No'dan) bulur, hareketin iş gününü vardiyaya göre belirler ve etkilenen günlerin puantajını
|
||
-- yeniden hesaplar. Ekrandan gelen değişiklik kapalı döneme düşerse kayıt geri alınır
|
||
-- (@FailOnClosed = 1); PDKS ve gece işinde satır hatalı işaretlenir.
|
||
CREATE TABLE #apLog
|
||
(
|
||
Id uniqueidentifier PRIMARY KEY, TenantId uniqueidentifier NULL, EmployeeId uniqueidentifier NULL,
|
||
CardNo nvarchar(64) NULL, EventTime datetime2, IsDeleted bit, OldWorkDate date NULL,
|
||
NewWorkDate date NULL, Error nvarchar(32) NULL
|
||
);
|
||
|
||
INSERT INTO #apLog (Id, TenantId, EmployeeId, CardNo, EventTime, IsDeleted, OldWorkDate)
|
||
SELECT Id, TenantId, EmployeeId, CardNo, EventTime, IsDeleted, WorkDate
|
||
FROM Hr_T_AttendanceLog
|
||
WHERE IsProcessed = 0;
|
||
|
||
IF NOT EXISTS (SELECT 1 FROM #apLog)
|
||
RETURN;
|
||
|
||
UPDATE a
|
||
SET EmployeeId = u.Id
|
||
FROM #apLog a
|
||
CROSS APPLY (
|
||
SELECT TOP 1 x.Id
|
||
FROM AbpUsers x
|
||
WHERE (x.PdksCardNo = a.CardNo OR x.RegistrationNo = a.CardNo) AND x.IsDeleted = 0
|
||
AND (x.TenantId = a.TenantId OR (x.TenantId IS NULL AND a.TenantId IS NULL))
|
||
ORDER BY CASE WHEN x.PdksCardNo = a.CardNo THEN 0 ELSE 1 END, x.IsActive DESC
|
||
) u
|
||
WHERE a.EmployeeId IS NULL AND a.CardNo IS NOT NULL AND a.CardNo <> N'';
|
||
|
||
UPDATE #apLog SET Error = N'UnknownCard' WHERE EmployeeId IS NULL AND IsDeleted = 0;
|
||
|
||
UPDATE a
|
||
SET NewWorkDate = CAST(DATEADD(minute, -s.DayOffset, a.EventTime) AS date)
|
||
FROM #apLog a
|
||
INNER JOIN Hr_V_AttendanceShift s ON s.EmployeeId = a.EmployeeId
|
||
WHERE a.IsDeleted = 0 AND a.Error IS NULL;
|
||
|
||
-- Kapalı dönem: hareketin eski ya da yeni günü kapalı aya düşüyorsa.
|
||
UPDATE a
|
||
SET Error = N'ClosedPeriod'
|
||
FROM #apLog a
|
||
WHERE EXISTS (
|
||
SELECT 1 FROM Hr_T_TimesheetPeriod p
|
||
WHERE p.IsDeleted = 0 AND p.Status = N'Closed'
|
||
AND (p.TenantId = a.TenantId OR (p.TenantId IS NULL AND a.TenantId IS NULL))
|
||
AND ((p.PeriodYear = YEAR(a.OldWorkDate) AND p.PeriodMonth = MONTH(a.OldWorkDate))
|
||
OR (p.PeriodYear = YEAR(a.NewWorkDate) AND p.PeriodMonth = MONTH(a.NewWorkDate))));
|
||
|
||
IF @FailOnClosed = 1 AND EXISTS (SELECT 1 FROM #apLog WHERE Error = N'ClosedPeriod')
|
||
THROW 51200, N'Kapalı puantaj dönemine ait hareket eklenemez, değiştirilemez ya da silinemez. Dönemi İnsan Kaynakları açabilir.', 1;
|
||
|
||
UPDATE l
|
||
SET EmployeeId = a.EmployeeId,
|
||
WorkDate = CASE WHEN a.Error = N'ClosedPeriod' THEN l.WorkDate ELSE a.NewWorkDate END,
|
||
ProcessError = a.Error,
|
||
Source = COALESCE(l.Source, N'Import'),
|
||
IsProcessed = 1
|
||
FROM Hr_T_AttendanceLog l
|
||
INNER JOIN #apLog a ON a.Id = l.Id;
|
||
|
||
-- Etkilenen günler: hareketin eski günü (taşındıysa ya da silindiyse) ve yeni günü.
|
||
DECLARE @Keys TABLE (TenantId uniqueidentifier NULL, EmployeeId uniqueidentifier, FromDate date, ToDate date);
|
||
|
||
INSERT INTO @Keys (TenantId, EmployeeId, FromDate, ToDate)
|
||
SELECT TenantId, EmployeeId, MIN(D), MAX(D)
|
||
FROM (
|
||
SELECT TenantId, EmployeeId, OldWorkDate AS D FROM #apLog
|
||
WHERE EmployeeId IS NOT NULL AND OldWorkDate IS NOT NULL AND COALESCE(Error, N'') <> N'ClosedPeriod'
|
||
UNION ALL
|
||
SELECT TenantId, EmployeeId, NewWorkDate FROM #apLog
|
||
WHERE EmployeeId IS NOT NULL AND NewWorkDate IS NOT NULL AND Error IS NULL
|
||
) k
|
||
GROUP BY TenantId, EmployeeId;
|
||
|
||
DECLARE @TenantId uniqueidentifier, @EmployeeId uniqueidentifier, @FromDate date, @ToDate date;
|
||
|
||
DECLARE key_cursor CURSOR LOCAL FAST_FORWARD FOR SELECT TenantId, EmployeeId, FromDate, ToDate FROM @Keys;
|
||
OPEN key_cursor;
|
||
FETCH NEXT FROM key_cursor INTO @TenantId, @EmployeeId, @FromDate, @ToDate;
|
||
WHILE @@FETCH_STATUS = 0
|
||
BEGIN
|
||
EXEC dbo.Hr_S_TimesheetCalculate @TenantId, @EmployeeId, @FromDate, @ToDate, NULL;
|
||
FETCH NEXT FROM key_cursor INTO @TenantId, @EmployeeId, @FromDate, @ToDate;
|
||
END
|
||
CLOSE key_cursor;
|
||
DEALLOCATE key_cursor;
|
||
END
|
||
GO
|