-- EMPLOYEE DETAIL
SELECT EMPLOYEE_TBL.*,
       NVL(LATE_TBL.LATE_IN, 0) LATE_IN,
       NVL(EARLY_TBL.EARLY_OUT, 0) EARLY_OUT,
       NVL(MISSED_PUNCH_TBL.MISSED_PUNCH, 0) MISSED_PUNCH,
       NVL(PRESENT_TBL.PRESENT_DAY, 0) PRESENT_DAY,
       NVL(ABSENT_TBL.ABSENT_DAY, 0) ABSENT_DAY,
       NVL(LEAVE_TBL.LEAVE, 0) LEAVE,
       NVL(WOH_TBL.WOH, 0) WOH,
       NVL(TOUR_TBL.TOUR, 0) TOUR,
       NVL(TRAINING_TBL.TRAINING, 0) TRAINING,
       NVL(CUR_MONTH_WOH_TBL.CUR_MONTH_WOH, 0) CUR_MONTH_WOH,
       NVL(PREV_MONTH_WOH_TBL.PREV_MONTH_WOH, 0) PREV_MONTH_WOH
FROM
  ( SELECT EMP.EMPLOYEE_ID,
           ( CASE
                 WHEN MIDDLE_NAME IS NULL THEN EMP.FIRST_NAME || ' ' || EMP.LAST_NAME
                 ELSE EMP.FIRST_NAME || ' ' || EMP.MIDDLE_NAME || ' ' || EMP.LAST_NAME
             END ) FULL_NAME,
           EMP.EMAIL_OFFICIAL,
           EMP.EMAIL_PERSONAL,
           TO_CHAR(EMP.JOIN_DATE, 'DD-MON-YYYY') JOIN_DATE,
           TRUNC(MONTHS_BETWEEN(SYSDATE, EMP.JOIN_DATE) / 12) AS SERVICE_YEARS,
           TRUNC(MOD(MONTHS_BETWEEN(SYSDATE, EMP.JOIN_DATE), 12)) AS SERVICE_MONTHS,
           TRUNC(SYSDATE) - ADD_MONTHS(EMP.JOIN_DATE, TRUNC(MONTHS_BETWEEN(SYSDATE, EMP.JOIN_DATE))) AS SERVICE_DAYS,
           DSG.DESIGNATION_TITLE,
           EFL.FILE_PATH
   FROM HRIS_EMPLOYEES EMP,
        HRIS_DESIGNATIONS DSG,
        HRIS_EMPLOYEE_FILE EFL
   WHERE EMP.DEPARTMENT_ID = DSG.DESIGNATION_ID
     AND EMP.PROFILE_PICTURE_ID = EFL.FILE_CODE(+)
     AND EMP.RETIRED_FLAG = 'N'
     AND EMP.EMPLOYEE_ID = 6
     AND EMP.COMPANY_ID = 2 ) EMPLOYEE_TBL 
-- LATE IN
LEFT JOIN
  ( SELECT ATTEN.EMPLOYEE_ID, COUNT (*) LATE_IN
   FROM
     ( SELECT A.EMPLOYEE_ID,
              S.START_TIME,
              A.IN_TIME,
              (((TRUNC (S.START_TIME) - S.START_TIME)) - (TRUNC (A.IN_TIME) - A.IN_TIME) ) LATE_HRS,
              S.LATE_IN - TRUNC (S.LATE_IN) LATE_GRACE
      FROM HRIS_ATTENDANCE_DETAIL A,
           HRIS_SHIFTS S
      WHERE 1 = 1
        AND A.EMPLOYEE_ID = 6
        AND A.SHIFT_ID = S.SHIFT_ID
        AND A.IN_TIME BETWEEN TO_DATE('16-JUL-2016', 'DD-MON-YYYY') AND TO_DATE('16-JUL-2017', 'DD-MON-YYYY') ) ATTEN
   WHERE ATTEN.LATE_HRS > LATE_GRACE
   GROUP BY ATTEN.EMPLOYEE_ID ) LATE_TBL ON LATE_TBL.EMPLOYEE_ID = EMPLOYEE_TBL.EMPLOYEE_ID 
-- EARLY OUT
LEFT JOIN
  ( SELECT EMPLOYEE_ID, COUNT (*) EARLY_OUT
   FROM
     ( SELECT A.EMPLOYEE_ID,
              S.END_TIME,
              A.OUT_TIME,
              ((TRUNC (A.OUT_TIME) - A.OUT_TIME) - ((TRUNC (S.END_TIME) - S.END_TIME)) ) EARLY_HRS,
              S.EARLY_OUT - TRUNC (S.EARLY_OUT) EARLY_GRACE
      FROM HRIS_ATTENDANCE_DETAIL A,
           HRIS_SHIFTS S
      WHERE 1 = 1
        AND A.EMPLOYEE_ID = 6
        AND A.SHIFT_ID = S.SHIFT_ID
        AND A.IN_TIME BETWEEN TO_DATE('16-JUL-2016', 'DD-MON-YYYY') AND TO_DATE('16-JUL-2017', 'DD-MON-YYYY') ) ATTEN
   WHERE ATTEN.EARLY_HRS > EARLY_GRACE
   GROUP BY EMPLOYEE_ID ) EARLY_TBL ON EARLY_TBL.EMPLOYEE_ID = EMPLOYEE_TBL.EMPLOYEE_ID 
-- MISSED PUNCH
LEFT JOIN
  ( SELECT EMPLOYEE_ID, COUNT(*) MISSED_PUNCH
   FROM
     ( SELECT EMPLOYEE_ID, ATTENDANCE_DT, COUNT (*)
      FROM HRIS_ATTENDANCE
      WHERE EMPLOYEE_ID = 6
        AND ATTENDANCE_DT BETWEEN TO_DATE('16-JUL-2016', 'DD-MON-YYYY') AND TO_DATE('16-JUL-2017', 'DD-MON-YYYY')
      GROUP BY EMPLOYEE_ID,
               ATTENDANCE_DT
      HAVING MOD(COUNT(*), 2) <> 0)
   GROUP BY EMPLOYEE_ID ) MISSED_PUNCH_TBL ON MISSED_PUNCH_TBL.EMPLOYEE_ID = EMPLOYEE_TBL.EMPLOYEE_ID 
-- PRESENT DAY
LEFT JOIN
  ( SELECT EMPLOYEE_ID, COUNT (*) PRESENT_DAY
   FROM HRIS_ATTENDANCE_DETAIL
   WHERE IN_TIME IS NOT NULL
     OR LEAVE_ID IS NOT NULL
     OR HOLIDAY_ID IS NOT NULL
     OR TRAINING_ID IS NOT NULL
     OR TRAVEL_ID IS NOT NULL
     AND DAYOFF_FLAG = 'N'
     AND EMPLOYEE_ID = 6
     AND ATTENDANCE_DT BETWEEN TO_DATE('16-JUL-2016', 'DD-MON-YYYY') AND TO_DATE('16-JUL-2017', 'DD-MON-YYYY')
   GROUP BY EMPLOYEE_ID ) PRESENT_TBL ON PRESENT_TBL.EMPLOYEE_ID = EMPLOYEE_TBL.EMPLOYEE_ID 
-- ABSENT DAY
LEFT JOIN
  ( SELECT EMPLOYEE_ID, COUNT (*) ABSENT_DAY
   FROM HRIS_ATTENDANCE_DETAIL
   WHERE IN_TIME IS NULL
     AND LEAVE_ID IS NULL
     AND HOLIDAY_ID IS NULL
     AND TRAINING_ID IS NULL
     AND TRAVEL_ID IS NULL
     AND DAYOFF_FLAG = 'N'
     AND EMPLOYEE_ID = 6
     AND ATTENDANCE_DT BETWEEN TO_DATE('16-JUL-2016', 'DD-MON-YYYY') AND TO_DATE('16-JUL-2017', 'DD-MON-YYYY')
   GROUP BY EMPLOYEE_ID ) ABSENT_TBL ON ABSENT_TBL.EMPLOYEE_ID = EMPLOYEE_TBL.EMPLOYEE_ID 
-- LEAVE COUNT
LEFT JOIN
  ( SELECT EMPLOYEE_ID, COUNT (*) LEAVE
   FROM HRIS_ATTENDANCE_DETAIL
   WHERE LEAVE_ID IS NOT NULL
     AND DAYOFF_FLAG = 'N'
     AND EMPLOYEE_ID = 6
     AND ATTENDANCE_DT BETWEEN TO_DATE('16-JUL-2016', 'DD-MON-YYYY') AND TO_DATE('16-JUL-2017', 'DD-MON-YYYY')
   GROUP BY EMPLOYEE_ID ) LEAVE_TBL ON LEAVE_TBL.EMPLOYEE_ID = EMPLOYEE_TBL.EMPLOYEE_ID 
-- WOH
LEFT JOIN
  ( SELECT EMPLOYEE_ID, COUNT (*) WOH
   FROM HRIS_ATTENDANCE_DETAIL
   WHERE HOLIDAY_ID IS NOT NULL
     AND IN_TIME IS NOT NULL
     AND OUT_TIME IS NOT NULL
     AND DAYOFF_FLAG = 'N'
     AND EMPLOYEE_ID = 6
     AND ATTENDANCE_DT BETWEEN TO_DATE('16-JUL-2016', 'DD-MON-YYYY') AND TO_DATE('16-JUL-2017', 'DD-MON-YYYY')
   GROUP BY EMPLOYEE_ID ) WOH_TBL ON WOH_TBL.EMPLOYEE_ID = EMPLOYEE_TBL.EMPLOYEE_ID 
-- ON TOUR
LEFT JOIN
  ( SELECT EMPLOYEE_ID, COUNT (*) TOUR
   FROM HRIS_ATTENDANCE_DETAIL
   WHERE TRAVEL_ID IS NOT NULL
     AND DAYOFF_FLAG = 'N'
     AND EMPLOYEE_ID = 6
     AND ATTENDANCE_DT BETWEEN TO_DATE('16-JUL-2016', 'DD-MON-YYYY') AND TO_DATE('16-JUL-2017', 'DD-MON-YYYY')
   GROUP BY EMPLOYEE_ID ) TOUR_TBL ON TOUR_TBL.EMPLOYEE_ID = EMPLOYEE_TBL.EMPLOYEE_ID
-- TRAINING
LEFT JOIN
  ( SELECT EMPLOYEE_ID, COUNT (*) TRAINING
   FROM HRIS_ATTENDANCE_DETAIL
   WHERE HOLIDAY_ID IS NULL
     AND TRAINING_ID IS NOT NULL
     AND EMPLOYEE_ID = 6
     AND ATTENDANCE_DT BETWEEN TO_DATE('16-JUL-2016', 'DD-MON-YYYY') AND TO_DATE('16-JUL-2017', 'DD-MON-YYYY')
   GROUP BY EMPLOYEE_ID ) TRAINING_TBL ON TRAINING_TBL.EMPLOYEE_ID = EMPLOYEE_TBL.EMPLOYEE_ID
-- CURRENT MONTH WOH
LEFT JOIN
  (SELECT EMPLOYEE_ID, COUNT(*) CUR_MONTH_WOH
   FROM HRIS_ATTENDANCE_DETAIL
   WHERE TO_CHAR (ATTENDANCE_DT, 'MM') = TO_CHAR (SYSDATE, 'MM')
     AND IN_TIME IS NOT NULL
     AND (DAYOFF_FLAG = 'Y' OR HOLIDAY_ID IS NOT NULL)
   GROUP BY EMPLOYEE_ID) CUR_MONTH_WOH_TBL ON CUR_MONTH_WOH_TBL.EMPLOYEE_ID = EMPLOYEE_TBL.EMPLOYEE_ID
-- PREVIOUS MONTH WOH
LEFT JOIN
  (SELECT EMPLOYEE_ID, COUNT(*) PREV_MONTH_WOH
   FROM HRIS_ATTENDANCE_DETAIL
   WHERE ADD_MONTHS (TRUNC (SYSDATE, 'MM'), -1) = ADD_MONTHS (TRUNC (ATTENDANCE_DT, 'MM'), 0)
     AND IN_TIME IS NOT NULL
     AND (DAYOFF_FLAG = 'Y' OR HOLIDAY_ID IS NOT NULL)
   GROUP BY EMPLOYEE_ID) PREV_MONTH_WOH_TBL ON PREV_MONTH_WOH_TBL.EMPLOYEE_ID = EMPLOYEE_TBL.EMPLOYEE_ID;
                  
/* NOTICE LIST */
SELECT NEWS_ID,
       NEWS_DATE,
       NEWS_TITLE,
       NEWS_EDESC
FROM HRIS_NEWS
WHERE NEWS_TYPE = 'NOTICE'
  AND NEWS_DATE > TRUNC(SYSDATE) - 1
  AND COMPANY_ID = :V_COMPANY_CODE
  AND BRANCH_ID = :V_BRANCH_CODE
  AND DESIGNATION_ID = :V_DESIGNATION_ID
  AND DEPARTMENT_ID = :V_DEPARTMENT_ID
ORDER BY NEWS_DATE DESC;
  
/* HOLIDAY LIST */
SELECT HOLIDAY_ID,
       HOLIDAY_ENAME,
       GENDER_ID,
       BRANCH_ID,
       START_DATE,
       END_DATE,
       HALFDAY,
       START_DATE - TRUNC(SYSDATE) DAYS_REMAINING
FROM HRIS_HOLIDAY_MASTER_SETUP
WHERE 1 = 1
  AND TRUNC(SYSDATE)-1 < START_DATE
  AND (GENDER_ID IS NULL
       OR GENDER_ID = :V_GENDER_ID)
  AND (BRANCH_ID IS NULL
       OR BRANCH_ID = :V_BRANCH_ID)
ORDER BY START_DATE;
 
/* BIRTHDAYS */
SELECT * FROM
  (SELECT EMPLOYEE_ID, FIRST_NAME, BIRTH_DATE,'TODAY' BIRTHDAYFOR
   FROM HRIS_EMPLOYEES
   WHERE TO_CHAR(BIRTH_DATE,'MMDD') = TO_CHAR(SYSDATE,'MMDD')
     UNION ALL
     SELECT EMPLOYEE_ID, FIRST_NAME, BIRTH_DATE,'UPCOMMING' BIRTHDAYFOR
     FROM HRIS_EMPLOYEES WHERE TO_CHAR(BIRTH_DATE,'MMDD') > TO_CHAR(SYSDATE,'MMDD'))
ORDER BY TO_CHAR(BIRTH_DATE,'MMDD');
/*TODO LIST */
SELECT TASK_ID,
       TASK_EDESC,
       END_DATE,
       STATUS
FROM HRIS_TASK
WHERE (END_DATE> TRUNC(SYSDATE)
       OR STATUS = 'O');

/*Gender wise -- Pie */
  SELECT COUNT (*), HE.GENDER_ID, HG.GENDER_NAME
    FROM HRIS_EMPLOYEES HE, HRIS_GENDERS HG
   WHERE HE.GENDER_ID = HG.GENDER_ID
     AND HG.STATUS = 'E'
                AND HE.RETIRED_FLAG = 'N'
   AND HE.COMPANY_ID = :V_COMPANY_ID
GROUP BY HE.GENDER_ID, HG.GENDER_NAME
 
/*Department wise -- Bar */
  SELECT COUNT (*), HD.DEPARTMENT_ID , HD.DEPARTMENT_NAME
    FROM HRIS_EMPLOYEES HE, HRIS_DEPARTMENTS HD
   WHERE HE.DEPARTMENT_ID(+) = HD.DEPARTMENT_ID
   AND HD.STATUS = 'E'
                AND HE.RETIRED_FLAG = 'N'
   AND HE.COMPANY_ID = :V_COMPANY_ID
GROUP BY HD.DEPARTMENT_ID, HD.DEPARTMENT_NAME
 
/* Branch Wise - Bar */
  SELECT COUNT (*), HB.BRANCH_ID , HB.BRANCH_NAME
    FROM HRIS_EMPLOYEES HE, HRIS_BRANCHES HB
   WHERE HE.BRANCH_ID(+) = HB.BRANCH_ID
   AND HB.STATUS = 'E'
                AND HE.RETIRED_FLAG = 'N'
   AND HE.COMPANY_ID = :V_COMPANY_ID
GROUP BY HB.BRANCH_ID , HB.BRANCH_NAME
 
 
/* Desgnation wise - Bar */
SELECT COUNT (*), HD.DESIGNATION_ID, HD.DESIGNATION_TITLE
    FROM HRIS_EMPLOYEES HE, HRIS_DESIGNATIONS HD
   WHERE HE.DESIGNATION_ID(+) = HD.DESIGNATION_ID
   AND HD.STATUS = 'E'
                AND HE.RETIRED_FLAG = 'N'
   AND HE.COMPANY_ID = :V_COMPANY_ID
GROUP BY HD.DESIGNATION_ID, HD.DESIGNATION_TITLE
 
/* Service type wise - Pie */
SELECT COUNT (*), HST.SERVICE_TYPE_ID , HST.SERVICE_TYPE_NAME
    FROM HRIS_EMPLOYEES HE, HRIS_SERVICE_TYPES HST
   WHERE HE.SERVICE_TYPE_ID(+) = HST.SERVICE_TYPE_ID
   AND HST.STATUS = 'E'
                AND HE.RETIRED_FLAG = 'N'
   AND HE.COMPANY_ID = :V_COMPANY_ID
GROUP BY HST.SERVICE_TYPE_ID , HST.SERVICE_TYPE_NAME

/* JOINED THIS MONTH */
SELECT COUNT (*)
    FROM HRIS_EMPLOYEES
   WHERE TO_CHAR(JOIN_DATE,'YYYYMM') = TO_CHAR(SYSDATE,'YYYYMM')
   AND STATUS = 'E'

                AND HE.RETIRED_FLAG = 'N'
   AND HE.COMPANY_ID = :V_COMPANY_ID
GROUP BY HST.SERVICE_TYPE_ID , HST.SERVICE_TYPE_NAME
SELECT COUNT(*) FROM HRIS_EMPLOYEES WHERE TO_CHAR(JOIN_DATE,'YYYYMM') = TO_CHAR(SYSDATE,'YYYYMM') AND STATUS='E'

