vcalendarrekapabsensi isAllApproved

/****** Object:  View [dbo].[vcalendarrekapabsensi]    Script Date: 28/11/2023 14:32:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO







ALTER VIEW [dbo].[vcalendarrekapabsensi]
AS
SELECT        EmployeeID, tipe, tgl, TimeIn, timeout, note, color, Late
FROM            (SELECT DISTINCT
                                                    ao.EmployeeID, 'H' AS tipe, CONVERT(datetime, ao.Date, 105) AS tgl, ISNULL(ao.TimeIn, ao.Date) AS TimeIn, ISNULL(ao.TimeOut, ao.Date) AS timeout, 'H 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') AS note,
                                                    'limegreen' AS color, ISNULL(LEFT(CONVERT(varchar, ao.LateTimeDouble, 108), 5), '00:00:00') AS Late
                          FROM            dbo.AttendanceOverview AS ao LEFT OUTER JOIN
                                                    dbo.Attendance AS a ON ao.AttendanceOverviewID = a.AttendanceOverviewID
                          WHERE        (a.isAllApproved = 1) and ao.TimeIn is not null and ao.TimeOut is not null
                          UNION ALL
                          SELECT        d.EmployeeID, 'S' AS tipe, ISNULL(v.tgl, d.Date) AS Expr3, ISNULL(v.tgl, d.Date) AS Expr1, ISNULL(v.tgl, d.Date) AS Expr2, 'S ' + d.Note AS Expr4, 'MediumOrchid' AS color, 0 AS Late
                          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
                                                   inner join DayOffDetail dd on d.DayOffID = dd.DayOffID and CONVERT(date, v.tgl, 105) = CONVERT(date, dd.Tgl, 105)
                                                   and v.tipe = 'Reguler'
                          WHERE        (d.isAllApproved = 1) AND (d.IsDisease = 1)
                          UNION ALL
                          SELECT        d.EmployeeID, 'C' AS tipe, ISNULL(v.tgl, d.Date) AS Expr3, ISNULL(v.tgl, d.Date) AS Expr1, ISNULL(v.tgl, d.Date) AS Expr2, 'C ' + d.Note AS Expr4, 'MediumOrchid' AS color, 0 AS Late
                          FROM            dbo.DayOff AS d INNER JOIN
                                                   dbo.Constanta AS C ON d.ConstantaID = C.ConstantaID 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
                                                   inner join DayOffDetail dd on d.DayOffID = dd.DayOffID and CONVERT(date, v.tgl, 105) = CONVERT(date, dd.Tgl, 105)
                                                   and v.tipe = 'Reguler'
                          WHERE        (d.isAllApproved = 1) AND (C.Name LIKE '%Cuti%')
                          UNION ALL
                          SELECT        d.EmployeeID, 'I' AS tipe, ISNULL(v.tgl, d.Date) AS Expr3, ISNULL(v.tgl, d.Date) AS Expr1, ISNULL(v.tgl, d.Date) AS Expr2, 'I ' + d.Note AS Expr4, 'MediumOrchid' AS color, 0 AS Late
                            FROM            dbo.DayOff AS d INNER JOIN
                                                    dbo.Constanta AS C ON d.ConstantaID = C.ConstantaID 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'
                                            inner join DayOffDetail dd on d.DayOffID = dd.DayOffID and CONVERT(date, v.tgl, 105) = CONVERT(date, dd.Tgl, 105)

                            WHERE        (d.isAllApproved = 1) AND (isnull(d.IsDisease,0) = 0) AND (C.Name NOT LIKE '%Cuti%')
                          UNION ALL
                          SELECT DISTINCT
                                                   employeeid, 'A' AS tipe, tgl, CONVERT(datetime, CONVERT(varchar, tgl, 105) + ' ' + CONVERT(varchar(8), masuk), 105) AS masuk, CONVERT(datetime, CONVERT(varchar,
                                                   CASE WHEN masuk > keluar THEN dateadd(day, 1, tgl) ELSE tgl END, 105) + ' ' + CONVERT(varchar(8), keluar), 105) AS keluar, 'A In:' + ISNULL(LEFT(CONVERT(varchar, masuk, 108), 5), '-')
                                                   + '  Out:' + ISNULL(LEFT(CONVERT(varchar, keluar, 108), 5), '-') AS note, 'slategrey' AS color, 0 AS Late
                          FROM            dbo.vwp
                          WHERE        (tipe = 'reguler') AND (tgl IS NOT NULL)) AS a_1
GO