/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2016 Applaud Solutions UK Ltd                |
 |                             All rights reserved                            |
 *===========================================================================*/

SET VERIFY OFF
SET FEEDBACK OFF
SET SCAN OFF
WHENEVER OSERROR  EXIT FAILURE ROLLBACK
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

DECLARE

  --
  -- Local constants and variables
  --
  l_SERVICE CONSTANT xxas_com_service_repository.service_name%TYPE
                       := 'AbsenceCalendar';

BEGIN

  xxas_com_sr_pkg.set_rest_service
    (p_service_name       => l_SERVICE
    ,p_description        => 'AbsenceCalendar'
    ,p_consumes           => 'application/xml'
    ,p_produces           => 'application/xml'
    ,p_path               => 'absence-calendar');

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetEmployeeCalendarEntries'
    ,p_description        => 'Get the formatted Employee Absence Calendar Entries'
    ,p_path               => 'Employee/{viewName}/{personId}'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
         SELECT XMLElement
               ("data"
               ,XMLAttributes
                 ('true' as "json:force-array"
                 ,'http://json.org/' as "xmlns:json")
               ,XMLAgg
                 (XMLElement
                   ("item"
                   ,XMLForest
                     ('flexible'                 "type"
                     ,cale.calevent_title        "title"
                     ,CASE WHEN cale.calevent_start IS NULL
                           THEN null
                           WHEN to_char(cale.calevent_start, 'HH24:MI') = '00:00'
                           THEN to_char(cale.calevent_start, 'YYYY-MM-DD')
                           ELSE to_char(cale.calevent_start, 'YYYY-MM-DD HH24:MI:SS')
                      END "start"
                     ,CASE WHEN cale.calevent_end IS NULL
                           THEN null
                           WHEN to_char(cale.calevent_end, 'HH24:MI') = '00:00'
                           THEN to_char(cale.calevent_end+1, 'YYYY-MM-DD')
                           ELSE to_char(cale.calevent_end, 'YYYY-MM-DD HH24:MI:SS')
                      END "end"
                     ,cale.calevent_color        "color"
                     ,cale.calevent_border_color "borderColor"
                     ,cale.calevent_text_color   "textColor"
                     ,cale.calevent_bg_color     "backgroundColor"
                     ,cale.summary_icon          "icon"
                     ,cale.page_id               "pageId"
                     ,cale.object_id             "objectId"
                     ,cale.target                "target"
                     )
                   )
                 )
               ) "data"
         FROM TABLE(xxas_cal_eng_pkg.calendar_events
                      (p_person_id        => {pathParam.personId}
                      ,p_formula_name     => {requestParam.formulaName}
                      ,p_view_name        => {pathParam.viewName}
                      ,p_effective_date   => trunc(sysdate)
                      ,p_error_msg_option => 'XXAS')) cale
              ,per_all_people_f rslt
         WHERE cale.person_id = rslt.person_id
         AND   trunc(sysdate) BETWEEN rslt.effective_start_date 
                              AND     rslt.effective_end_date
         AND   (nvl(rslt.current_employee_flag, 'N') = 'Y'
                OR nvl(rslt.current_npw_flag, 'N') = 'Y')
         AND   {authFunctionPredicate.XXAS_DT_EMPLOYEE_ABS_CALENDAR}
      ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetManagerCalendarEntries'
    ,p_description        => 'Get the formatted Manager Absence Calendar Entries'
    ,p_path               => 'Manager/{viewName}/{personId}'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
         SELECT XMLElement
               ("data"
               ,XMLAttributes
                 ('true' as "json:force-array"
                 ,'http://json.org/' as "xmlns:json")
               ,XMLAgg
                 (XMLElement
                   ("item"
                   ,XMLForest
                     ('flexible'                 "type"
                     ,cale.calevent_title        "title"
                     ,CASE WHEN cale.calevent_start IS NULL
                           THEN null
                           WHEN to_char(cale.calevent_start, 'HH24:MI') = '00:00'
                           THEN to_char(cale.calevent_start, 'YYYY-MM-DD')
                           ELSE to_char(cale.calevent_start, 'YYYY-MM-DD HH24:MI:SS')
                      END "start"
                     ,CASE WHEN cale.calevent_end IS NULL
                           THEN null
                           WHEN to_char(cale.calevent_end, 'HH24:MI') = '00:00'
                           THEN to_char(cale.calevent_end+1, 'YYYY-MM-DD')
                           ELSE to_char(cale.calevent_end, 'YYYY-MM-DD HH24:MI:SS')
                      END "end"
                     ,cale.calevent_color        "color"
                     ,cale.calevent_border_color "borderColor"
                     ,cale.calevent_text_color   "textColor"
                     ,cale.calevent_bg_color     "backgroundColor"
                     ,cale.summary_icon          "icon"
                     ,cale.page_id               "pageId"
                     ,cale.object_id             "objectId"
                     ,cale.target                "target"
                     )
                   )
                 )
               ) "data"
         FROM TABLE(xxas_cal_eng_pkg.calendar_events
                      (p_person_id        => {pathParam.personId}
                      ,p_formula_name     => {requestParam.formulaName}
                      ,p_view_name        => {pathParam.viewName}
                      ,p_effective_date   => trunc(sysdate)
                      ,p_error_msg_option => 'XXAS')) cale
              ,per_all_people_f rslt
         WHERE cale.person_id = rslt.person_id
         AND   trunc(sysdate) BETWEEN rslt.effective_start_date 
                              AND     rslt.effective_end_date
         AND   (nvl(rslt.current_employee_flag, 'N') = 'Y'
                OR nvl(rslt.current_npw_flag, 'N') = 'Y')
         AND   {authFunctionPredicate.XXAS_DT_MANAGER_ABS_CALENDAR}
      ]'
    );

END;
/
COMMIT;
EXIT;
