update dt_date set flg_holiday = 'Y' where string_date in ('20190403','20190417','20190419', '20190501');

WITH one_month_absen AS (
         SELECT a_1.user_id,
            b_1.string_date,
            a_1.full_name,
            b_1.flg_holiday
           FROM t_user a_1,
            dt_date b_1
          WHERE (a_1.user_id <> ALL (ARRAY[1::bigint, 2::bigint, 16::bigint])) AND a_1.active::text = 'Y'::text
        ),
  raw_data AS (
 SELECT a.string_date, a.full_name,
    to_char(to_timestamp(a.string_date::text, 'YYYYMMDD'::text), 'FMDay, DD Mon YYYY'::text) AS date,
        CASE
            WHEN b.checkin_datetime IS NOT NULL THEN to_char(to_timestamp(b.checkin_datetime::text, 'YYYYMMDDHH24MISS'::text), 'HH24:MI:SS'::text)
            ELSE '-'::text
        END AS checkin,
        CASE
            WHEN b.checkout_datetime IS NOT NULL AND b.checkout_datetime::text <> ''::text AND b.checkout_datetime::text <> '-'::text THEN to_char(to_timestamp(b.checkout_datetime::text, 'YYYYMMDDHH24MISS'::text), 'HH24:MI:SS'::text)
            ELSE '-'::text
        END AS checkout,
        CASE
            WHEN a.flg_holiday::text = 'N'::text AND b.checkin_datetime IS NULL AND a.string_date::text <= to_char('now'::text::date::timestamp with time zone, 'YYYYMMDD'::text) THEN 'N'::text
            WHEN a.string_date::text > to_char('now'::text::date::timestamp with time zone, 'YYYYMMDD'::text) THEN '-'::text
            ELSE 'Y'::text
        END AS status_masuk,
        CASE
            WHEN a.flg_holiday::text = 'Y'::text AND b.checkin_datetime IS NOT NULL THEN 'Y'::text
            WHEN a.string_date::text > to_char('now'::text::date::timestamp with time zone, 'YYYYMMDD'::text) THEN '-'::text
            ELSE 'N'::text
        END AS status_lembur,
        CASE
            WHEN b.checkout_datetime IS NOT NULL AND b.checkout_datetime::text <> ''::text AND b.checkout_datetime::text <> '-'::text AND a.flg_holiday::text <> 'Y'::text THEN 'N'::text
            ELSE
            CASE
                WHEN a.string_date::text > to_char('now'::text::date::timestamp with time zone, 'YYYYMMDD'::text) OR a.flg_holiday::text = 'Y'::text THEN '-'::text
                ELSE 'Y'::text
            END
        END AS status_absen_kosong,
        CASE
            WHEN b.checkin_datetime IS NOT NULL AND a.flg_holiday::text <> 'Y'::text THEN
            CASE
                WHEN trunc(date_part('epoch'::text, to_timestamp(b.checkin_datetime::text, 'YYYYMMDDHH24MISS'::text) - to_timestamp(to_char(to_timestamp(b.checkin_datetime::text, 'YYYYMMDDHH24MISS'::text), 'YYYYMMDD'::text) || '08300000'::text, 'YYYYMMDDHH24MISS'::text)) / 60::double precision) < 1::double precision THEN 0
                ELSE trunc(date_part('epoch'::text, to_timestamp(b.checkin_datetime::text, 'YYYYMMDDHH24MISS'::text) - to_timestamp(to_char(to_timestamp(b.checkin_datetime::text, 'YYYYMMDDHH24MISS'::text), 'YYYYMMDD'::text) || '08300000'::text, 'YYYYMMDDHH24MISS'::text)) / 60::double precision)
            END
            ELSE 0
        END AS telat,
        CASE
            WHEN b.checkout_datetime IS NOT NULL AND b.checkout_datetime::text <> ''::text AND b.checkout_datetime::text <> '-'::text AND a.flg_holiday::text <> 'Y'::text THEN
            CASE
                WHEN to_char(to_timestamp(b.checkin_datetime::text, 'YYYYMMDDHH24MISS'::text), 'HH24MISS'::text)::integer > 830 THEN
                CASE
                    WHEN (- (trunc(date_part('epoch'::text, to_timestamp(b.checkout_datetime::text, 'YYYYMMDDHH24MISS'::text) - to_timestamp(b.checkin_datetime::text, 'YYYYMMDDHH24MISS'::text)) / 60::double precision) - 540::double precision)) < 1::double precision THEN 0
                    ELSE (- (trunc(date_part('epoch'::text, to_timestamp(b.checkout_datetime::text, 'YYYYMMDDHH24MISS'::text) - to_timestamp(b.checkin_datetime::text, 'YYYYMMDDHH24MISS'::text)) / 60::double precision) - 540::double precision))
                END
                ELSE
                CASE
                    WHEN (- (trunc(date_part('epoch'::text, to_timestamp(b.checkout_datetime::text, 'YYYYMMDDHH24MISS'::text) - to_timestamp(to_char(to_timestamp(b.checkout_datetime::text, 'YYYYMMDDHH24MISS'::text), 'YYYYMMDD'::text) || '08300000'::text, 'YYYYMMDDHH24MISS'::text)) / 60::double precision) - 540::double precision)) < 1::double precision THEN 0
                    ELSE (- (trunc(date_part('epoch'::text, to_timestamp(b.checkout_datetime::text, 'YYYYMMDDHH24MISS'::text) - to_timestamp(to_char(to_timestamp(b.checkout_datetime::text, 'YYYYMMDDHH24MISS'::text), 'YYYYMMDD'::text) || '08300000'::text, 'YYYYMMDDHH24MISS'::text)) / 60::double precision) - 540::double precision))
                END
            END
            ELSE 0
        END AS kurang,
        CASE
            WHEN b.checkin_datetime IS NOT NULL AND b.checkin_datetime::text <> ''::text AND b.checkin_datetime::text <> '-'::text AND (b.checkout_datetime IS NULL OR b.checkout_datetime::text = ''::text OR b.checkout_datetime::text = '-'::text) THEN 'Lupa checkout'::character varying
            ELSE COALESCE(d.reason_name, '-'::character varying)
        END AS description,
        CASE
            WHEN COALESCE(d.reason_code, ''::character varying)::text = ANY (ARRAY['C'::character varying, 'DL'::character varying, 'DD'::character varying]::text[]) THEN 'Y'::text
            ELSE 'N'::text
        END AS escape_denda
   FROM one_month_absen a
     LEFT JOIN at_attendance b ON a.string_date::text = "substring"(b.checkin_datetime::text, 1, 8) AND a.user_id = b.user_id
     LEFT JOIN t_manage_lost_checkin c ON a.string_date::text = c.checkin_date::text AND a.user_id = c.user_id
     LEFT JOIN t_reason d ON c.reason_id = d.reason_id
  WHERE a.flg_holiday::text = 'N'::text OR a.flg_holiday::text = 'Y'::text AND b.checkin_datetime IS NOT NULL
)
SELECT full_name, "date", checkin, checkout, status_masuk, status_lembur, status_absen_kosong, telat, kurang,
	telat+kurang AS "telat+kurang",
	CASE WHEN escape_denda = 'N' THEN
		CASE WHEN status_absen_kosong = 'Y' THEN 'Golongan 4'
		ELSE
			CASE
				WHEN telat BETWEEN 1 AND 15 THEN 'Golongan 0'
				WHEN telat BETWEEN 16 AND 30 THEN 'Golongan 1'
				WHEN telat BETWEEN 31 AND 45 THEN 'Golongan 2'
				WHEN telat BETWEEN 46 AND 60 THEN 'Golongan 3'
				WHEN telat > 60 THEN 'Golongan 4'
				ELSE '-'
			END
		END
	ELSE '-' END AS golongan_denda,
	description
FROM raw_data
WHERE string_date between '20190321' and '20190420'
ORDER BY full_name, string_date;