sozsoft-platform/configs/seeds/sql/object/Hr_V_TimesheetMonthly.sql

CREATE OR ALTER VIEW [dbo].[Hr_V_TimesheetMonthly]
AS
-- Personel bazlı aylık puantaj: günlük puantajın çalışan × ay toplamı. Id günlük satırların
-- PeriodKey değeridir (alt form ilişkisi). Fazla mesai ücreti çarpanı ve yıllık sınır ayardan gelir;
-- yıllık fazla mesai ocaktan o aya kadarki birikimdir. Ücretsiz izin günü (bordroda eksik gün) izinli
-- günlerin ücretsiz türdeki kısmıdır; eğitim günü çalışılmış sayılır.
WITH m AS (
    SELECT t.PeriodKey, t.TenantId, t.EmployeeId, t.PeriodYear, t.PeriodMonth,
           SUM(CASE WHEN t.DayType = N'Work' THEN 1 ELSE 0 END) AS WorkDays,
           CAST(SUM(t.NetMinutes) / 60.0 AS decimal(9, 2)) AS NetHours,
           SUM(t.LateMinutes) AS LateMinutes,
           SUM(CASE WHEN t.LateMinutes > 0 THEN 1 ELSE 0 END) AS LateDays,
           SUM(t.EarlyLeaveMinutes) AS EarlyLeaveMinutes,
           SUM(CASE WHEN t.EarlyLeaveMinutes > 0 THEN 1 ELSE 0 END) AS EarlyLeaveDays,
           CAST(SUM(t.OvertimeHours) AS decimal(9, 2)) AS OvertimeHours,
           SUM(CASE WHEN t.DayType = N'WeeklyOff' THEN 1 ELSE 0 END) AS WeeklyOffDays,
           SUM(CASE WHEN t.DayType = N'Holiday' THEN 1 ELSE 0 END) AS HolidayDays,
           SUM(CASE WHEN t.DayType = N'Leave' THEN 1 ELSE 0 END) AS LeaveDays,
           SUM(CASE WHEN t.DayType = N'Leave' AND lt.IsPaid = 0 THEN 1 ELSE 0 END) AS UnpaidLeaveDays,
           SUM(CASE WHEN t.DayType = N'Training' THEN 1 ELSE 0 END) AS TrainingDays,
           SUM(CASE WHEN t.DayType = N'Sick' THEN 1 ELSE 0 END) AS SickDays,
           SUM(CASE WHEN t.DayType = N'Absent' THEN 1 ELSE 0 END) AS AbsentDays,
           SUM(CASE WHEN t.IsIncomplete = 1 THEN 1 ELSE 0 END) AS IncompleteDays,
           SUM(CASE WHEN t.IsAssumed = 1 THEN 1 ELSE 0 END) AS AssumedDays
    FROM Hr_T_Timesheet t
    LEFT JOIN Hr_T_LeaveType lt ON lt.Id = t.LeaveTypeId
    WHERE t.IsDeleted = 0
    GROUP BY t.PeriodKey, t.TenantId, t.EmployeeId, t.PeriodYear, t.PeriodMonth
)
SELECT m.PeriodKey AS Id, m.TenantId, m.EmployeeId, u.DepartmentId, u.JobPositionId,
       m.PeriodYear, m.PeriodMonth, COALESCE(p.[Status], N'Open') AS PeriodStatus,
       m.WorkDays, m.NetHours, m.LateMinutes, m.LateDays, m.EarlyLeaveMinutes, m.EarlyLeaveDays,
       m.OvertimeHours,
       CAST(m.OvertimeHours * s.Rate AS decimal(9, 2)) AS OvertimePaidHours,
       m.WeeklyOffDays, m.HolidayDays, m.LeaveDays, m.UnpaidLeaveDays, m.TrainingDays, m.SickDays, m.AbsentDays, m.IncompleteDays, m.AssumedDays,
       CAST(SUM(m.OvertimeHours) OVER (PARTITION BY m.TenantId, m.EmployeeId, m.PeriodYear ORDER BY m.PeriodMonth
                                       ROWS UNBOUNDED PRECEDING) AS decimal(9, 2)) AS YearOvertimeHours,
       s.AnnualLimit AS AnnualOvertimeLimit,
       CASE WHEN SUM(m.OvertimeHours) OVER (PARTITION BY m.TenantId, m.EmployeeId, m.PeriodYear ORDER BY m.PeriodMonth
                                            ROWS UNBOUNDED PRECEDING) > s.AnnualLimit THEN 1 ELSE 0 END AS IsOverAnnualLimit
FROM m
INNER JOIN AbpUsers u ON u.Id = m.EmployeeId
OUTER APPLY (
    SELECT TOP 1 x.[Status] FROM Hr_T_TimesheetPeriod x
    WHERE x.IsDeleted = 0 AND x.PeriodYear = m.PeriodYear AND x.PeriodMonth = m.PeriodMonth
      AND (x.TenantId = m.TenantId OR (x.TenantId IS NULL AND m.TenantId IS NULL))
) p
CROSS APPLY (
    SELECT COALESCE(TRY_CONVERT(decimal(9, 2), REPLACE(dbo.Hr_F_HrSetting(m.TenantId, N'Adm.HrSettings.Attendance.OvertimeRate'), N',', N'.')), 1.5) AS Rate,
           COALESCE(TRY_CONVERT(decimal(9, 2), REPLACE(dbo.Hr_F_HrSetting(m.TenantId, N'Adm.HrSettings.Attendance.AnnualOvertimeLimitHours'), N',', N'.')), 270) AS AnnualLimit
) s;
GO