vrekapabsen isAllApproved

/****** Object:  View [dbo].[vrekapabsen]    Script Date: 28/11/2023 14:50:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO











ALTER VIEW [dbo].[vrekapabsen]
AS
SELECT        EmployeeID, tipe, tgl, TimeIn, timeout, note, color
FROM            (SELECT DISTINCT
                                                    ao.EmployeeID, 'Absen' AS tipe, CONVERT(datetime, ao.Date, 105) AS tgl, ISNULL(ao.TimeIn, ao.Date) AS TimeIn, ISNULL(ao.TimeOut, ao.Date) AS timeout, 'In:' + ISNULL(LEFT(CONVERT(varchar,
                                                    ao.TimeIn, 108), 5), '-') + '  Out:' + ISNULL(LEFT(CONVERT(varchar, ao.TimeOut, 108), 5), '-') + '  Late:' + ISNULL(LEFT(CONVERT(varchar, ao.LateTimeDouble, 108), 5), '00:00:00') + ' - Absen' AS note,
                                                    'limegreen' AS color
                          FROM            dbo.AttendanceOverview AS ao LEFT OUTER JOIN
                                                    dbo.Attendance AS a ON ao.AttendanceOverviewID = a.AttendanceOverviewID
                          WHERE        (a.isAllApproved = 1)
                          UNION ALL
                          SELECT DISTINCT
                                                   ao.EmployeeID, 'Pengajuan' AS tipe, CONVERT(datetime, ao.Date, 105) AS tgl, ISNULL(ao.TimeIn, ao.Date) AS Expr1, ISNULL(ao.TimeOut, ISNULL(ao.TimeIn, ao.Date)) AS timeout,
                                                   'In:' + ISNULL(LEFT(CONVERT(varchar, ao.TimeIn, 108), 5), '-') + '  Out:' + ISNULL(LEFT(CONVERT(varchar, ao.TimeOut, 108), 5), '-') + '  Late:' + ISNULL(LEFT(CONVERT(varchar, ao.LateTimeDouble,
                                                   108), 5), '00:00:00') + ' - Pengajuan' AS note, 'orangered' AS color
                          FROM            dbo.AttendanceOverview AS ao LEFT OUTER JOIN
                                                   dbo.Attendance AS a ON ao.AttendanceOverviewID = a.AttendanceOverviewID
                          WHERE        (ISNULL(a.isAllApproved, 0) = 0) AND (ao.TimeIn IS NULL) AND (ao.TimeOut IS NULL)
                          UNION ALL
                          SELECT        d.EmployeeID, 'Day Off' AS tipe, ISNULL(v.tgl, d.Date) AS Expr3, ISNULL(v.tgl, d.Date) AS Expr1, ISNULL(v.tgl, d.Date) AS Expr2, d.Note + ' - Day Off' AS Expr4, 'MediumOrchid' AS color
                          FROM            dbo.DayOff AS d LEFT OUTER JOIN
                                                   dbo.vwp AS v ON d.EmployeeID = v.employeeid AND CONVERT(date, v.tgl, 105) BETWEEN CONVERT(date, d.Date, 105) AND DATEADD(day, d.HowManyDays - 1, CONVERT(date, d.Date, 105)) AND
                                                   v.tipe = 'Reguler'
                          WHERE        (d.isAllApproved = 1)
                          UNION ALL
                          SELECT        EmployeeID, 'Overtime' AS tipe, DateStart, DateStart AS Expr1, DateEnd, Note + ' - Lembur' AS Expr2, 'deepskyblue' AS color
                          FROM            dbo.Overtime
                          WHERE        (isAllApproved = 1)) AS a_1
GO