CREATE PROCEDURE `get_attendance_in_range`(IN `e_id` BIGINT UNSIGNED, IN `start` DATETIME, IN `end` DATETIME)
    READS SQL DATA SQL SECURITY INVOKER
SELECT
                    DATE(punchin_time) as day,
                    MIN(punchin_time) AS punchin_time,
                    MAX(punchout_time) AS punchout_time,
                    SUM(duration) AS duration,
                    GROUP_CONCAT(status) AS status
                FROM (SELECT
                        MIN(punchin_time) AS punchin_time,
                        MAX(punchout_time) AS punchout_time,
                        SUM(duration) AS duration,
                        GROUP_CONCAT(flag) AS status
                    FROM `attendance_history`
                        WHERE `uid` = e_id
                        AND `active` = '1'
                        GROUP BY `group_no`) attendence
                WHERE COALESCE(`punchin_time`, 0) >= start
                AND COALESCE(`punchin_time`, 0) <= end
                GROUP BY DATE(`punchin_time`)
                ORDER BY `punchin_time` ASC;
CREATE PROCEDURE `get_subordinates_active_including_user`(IN `e_id` BIGINT UNSIGNED)
    READS SQL DATA SQL SECURITY INVOKER
SELECT * FROM (SELECT
            employee_id,
            hrm_id,
            first_name,
            last_name,
            status
          FROM employee_history
          WHERE employee_id = e_id
          UNION SELECT
        employee_id,
        hrm_id,
        first_name,
        last_name,
        status
      FROM employee_history
      WHERE indirect_super_visor_id = e_id
      UNION
      SELECT
        employee_id,
        hrm_id,
        first_name,
        last_name,
        status
      FROM employee_history
      WHERE super_visor_id = e_id
      UNION
      SELECT
        employee_id,
        hrm_id,
        first_name,
        last_name,
        status
      FROM employee_history
      WHERE employee_id IN (SELECT
          employee_id
        FROM employee_history
        WHERE super_visor_id IN (SELECT
            employee_id
          FROM employee_history
          WHERE super_visor_id = e_id
          OR indirect_super_visor_id = e_id))
      UNION
      SELECT
        employee_id,
        hrm_id,
        first_name,
        last_name,
        status
      FROM employee_history
      WHERE employee_id IN (SELECT
          employee_id
        FROM employee_history
        WHERE indirect_super_visor_id IN (SELECT
            employee_id
          FROM employee_history
          WHERE super_visor_id = e_id
          OR indirect_super_visor_id = e_id))) as t WHERE t.status IN ('active') ORDER BY first_name ASC;
CREATE PROCEDURE `get_subordinates_all_including_user`(IN `e_id` BIGINT UNSIGNED)
    READS SQL DATA SQL SECURITY INVOKER
SELECT * FROM (SELECT
            employee_id,
            hrm_id,
            first_name,
            last_name,
            status
          FROM employee_history
          WHERE employee_id = e_id
          UNION SELECT
        employee_id,
        hrm_id,
        first_name,
        last_name,
        status
      FROM employee_history
      WHERE indirect_super_visor_id = e_id
      UNION
      SELECT
        employee_id,
        hrm_id,
        first_name,
        last_name,
        status
      FROM employee_history
      WHERE super_visor_id = e_id
      UNION
      SELECT
        employee_id,
        hrm_id,
        first_name,
        last_name,
        status
      FROM employee_history
      WHERE employee_id IN (SELECT
          employee_id
        FROM employee_history
        WHERE super_visor_id IN (SELECT
            employee_id
          FROM employee_history
          WHERE super_visor_id = e_id
          OR indirect_super_visor_id = e_id))
      UNION
      SELECT
        employee_id,
        hrm_id,
        first_name,
        last_name,
        status
      FROM employee_history
      WHERE employee_id IN (SELECT
          employee_id
        FROM employee_history
        WHERE indirect_super_visor_id IN (SELECT
            employee_id
          FROM employee_history
          WHERE super_visor_id = e_id
          OR indirect_super_visor_id = e_id))) as t ORDER BY first_name ASC;
CREATE PROCEDURE `get_subordinates_all_excluding_user`(IN `e_id` BIGINT UNSIGNED)
    READS SQL DATA SQL SECURITY INVOKER
SELECT * FROM (SELECT
        employee_id,
        hrm_id,
        first_name,
        last_name,
        status
      FROM employee_history
      WHERE indirect_super_visor_id = e_id
      UNION
      SELECT
        employee_id,
        hrm_id,
        first_name,
        last_name,
        status
      FROM employee_history
      WHERE super_visor_id = e_id
      UNION
      SELECT
        employee_id,
        hrm_id,
        first_name,
        last_name,
        status
      FROM employee_history
      WHERE employee_id IN (SELECT
          employee_id
        FROM employee_history
        WHERE super_visor_id IN (SELECT
            employee_id
          FROM employee_history
          WHERE super_visor_id = e_id
          OR indirect_super_visor_id = e_id))
      UNION
      SELECT
        employee_id,
        hrm_id,
        first_name,
        last_name,
        status
      FROM employee_history
      WHERE employee_id IN (SELECT
          employee_id
        FROM employee_history
        WHERE indirect_super_visor_id IN (SELECT
            employee_id
          FROM employee_history
          WHERE super_visor_id = e_id
          OR indirect_super_visor_id = e_id))) as t ORDER BY first_name ASC;
CREATE PROCEDURE `get_subordinates_active_excluding_user`(IN `e_id` BIGINT UNSIGNED)
    READS SQL DATA SQL SECURITY INVOKER
SELECT * FROM (SELECT
        employee_id,
        hrm_id,
        first_name,
        last_name,
        status
      FROM employee_history
      WHERE indirect_super_visor_id = e_id
      UNION
      SELECT
        employee_id,
        hrm_id,
        first_name,
        last_name,
        status
      FROM employee_history
      WHERE super_visor_id = e_id
      UNION
      SELECT
        employee_id,
        hrm_id,
        first_name,
        last_name,
        status
      FROM employee_history
      WHERE employee_id IN (SELECT
          employee_id
        FROM employee_history
        WHERE super_visor_id IN (SELECT
            employee_id
          FROM employee_history
          WHERE super_visor_id = e_id
          OR indirect_super_visor_id = e_id))
      UNION
      SELECT
        employee_id,
        hrm_id,
        first_name,
        last_name,
        status
      FROM employee_history
      WHERE employee_id IN (SELECT
          employee_id
        FROM employee_history
        WHERE indirect_super_visor_id IN (SELECT
            employee_id
          FROM employee_history
          WHERE super_visor_id = e_id
          OR indirect_super_visor_id = e_id))) as t WHERE t.status IN ('active') ORDER BY first_name ASC;