/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2014 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
                       := 'PersonPageContexts';

BEGIN

  xxas_com_sr_pkg.set_rest_service
    (p_service_name       => l_SERVICE
    ,p_description        => 'Contexts for a person'
    ,p_consumes           => 'application/xml'
    ,p_produces           => 'application/xml'
    );
   
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetContexts'
    ,p_description        => 'Get a person''s contexts'
    ,p_methods            => 'GET'
    ,p_path               => '/people/{personId}/contexts'
    ,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
               ("context"
               ,XMLForest
                 (p.id AS "id"
                 ,p.type AS "type"
                 ,p.title AS "title"
                 ,p.sub_title AS "subTitle"
                  )
               ,XMLElement
                 ("links"
                 ,XMLElement
                   ("pages"
                   ,XMLElement
                     ("href", p.pages_href)
                   )
                 ,decode
                   (p.availability_href, null, null
                   ,XMLElement
                     ("availability"
                     ,XMLElement
                       ("href", p.availability_href)
                     )
                   )
                 )))) "data"
  FROM (SELECT paaf.assignment_id id
              ,'jobs' type
              ,xxas_com_asg_pkg.get_job_title
                 (paaf.assignment_id
                 ,least(paaf.effective_end_date
                       ,greatest(paaf.effective_start_date, trunc(sysdate)))) title
              ,xxas_com_asg_pkg.get_status
                 (paaf.assignment_id
                 ,least(paaf.effective_end_date
                       ,greatest(paaf.effective_start_date, trunc(sysdate)))) sub_title
              ,xxas_com_rest_util_pkg.uri_from_path
                ('/people/' || paaf.person_id || '/jobs/' || paaf.assignment_id || '/pages/{pageId}') pages_href
                ,xxas_com_rest_util_pkg.uri_from_path('/people/' || paaf.person_id || '/jobs/' || paaf.assignment_id || '/availability') availability_href
        FROM   per_all_people_f papf
            ,(SELECT pps.person_id
                    ,CASE WHEN max(pps.date_start)
                                 OVER
                                   (PARTITION BY pps.person_id) = pps.date_start
                          THEN 'Y'
                          ELSE 'N'
                     END is_latest
                    ,pps.date_start
                    ,pps.actual_termination_date
              FROM   per_periods_of_service pps) pos
            ,(SELECT ppp.person_id
                    ,CASE WHEN max(ppp.date_start)
                                 OVER
                                  (PARTITION BY ppp.person_id) = ppp.date_start
                          THEN 'Y'
                          ELSE 'N'
                     END is_latest
                    ,ppp.date_start
                    ,ppp.actual_termination_date
              FROM   per_periods_of_placement ppp) pop
             ,per_all_assignments_f paaf
             ,per_assignment_status_types past
        WHERE  papf.person_id = {pathParam.personId}
        AND    papf.person_id = pos.person_id(+)
        AND    pos.is_latest(+) = 'Y'
        AND    papf.person_id = pop.person_id(+)
        AND    pop.is_latest(+) = 'Y'
        AND   ((trunc(sysdate) BETWEEN
                papf.effective_start_date AND papf.effective_end_date)
            OR (nvl(papf.current_employee_flag,'Y') = 'Y' AND
                nvl(papf.current_npw_flag,'Y')= 'Y' AND
               ((papf.effective_start_date = CASE WHEN pos.date_start &gt; trunc(sysdate) THEN pos.date_start END		 
               OR (papf.effective_start_date = CASE WHEN pop.date_start &gt; trunc(sysdate) THEN pop.date_start END))
               ))
        )
        AND   paaf.person_id = papf.person_id
        AND  ((trunc(sysdate) BETWEEN
               paaf.effective_start_date AND paaf.effective_end_date)
           OR (nvl(papf.current_employee_flag, 'N') &lt;&gt; 'Y' AND
               nvl(papf.current_npw_flag, 'N') &lt;&gt; 'Y' AND
               ((paaf.effective_end_date &lt; trunc(sysdate) AND
                ((pos.actual_termination_date IS NOT NULL AND
                  paaf.effective_end_date = pos.actual_termination_date AND
                  paaf.assignment_type = 'E') OR
                 (pop.actual_termination_date IS NOT NULL AND
                  paaf.effective_end_date = pop.actual_termination_date AND
                  paaf.assignment_type = 'C')))
             OR (paaf.effective_start_date &gt; trunc(sysdate) AND
                ((pos.date_start IS NOT NULL AND
                  paaf.effective_start_date = pos.date_start AND
                  paaf.assignment_type = 'E') OR
                 (pop.date_start IS NOT NULL AND
                  paaf.effective_start_date = pop.date_start AND
                  paaf.assignment_type = 'C')))))
             OR  (nvl(papf.current_employee_flag,'Y') = 'Y' AND
                  nvl(papf.current_npw_flag,'Y') = 'Y' AND
                  paaf.effective_start_date &gt; trunc(sysdate) AND
                ((pos.date_start &gt; trunc(sysdate) AND		        
                  pos.date_start IS NOT NULL AND
                  paaf.effective_start_date = pos.date_start AND
                    paaf.assignment_type = 'E') OR
                 (pop.date_start &gt; trunc(sysdate) AND
                  pop.date_start IS NOT NULL AND
                  paaf.effective_start_date = pop.date_start AND
                  paaf.assignment_type = 'C')))
               )
        AND    paaf.assignment_type IN ('E', 'C')
        AND    paaf.assignment_status_type_id = past.assignment_status_type_id
        AND    past.per_system_status &lt;&gt; 'TERM_ASSIGN'
        UNION ALL
        SELECT papf.person_id id
              ,'candidate' type
              ,'Candidate TODO' title
              ,'Candidate Subtitle TODO' sub_title
              ,xxas_com_rest_util_pkg.uri_from_path
                ('/people/' || papf.person_id || '/candidate/' || papf.person_id || '/pages/{pageId}') pages_href
                ,null availability_href
        FROM   per_all_people_f papf
        WHERE  papf.person_id = {pathParam.personId}
        AND    trunc(sysdate) BETWEEN
               papf.effective_start_date AND papf.effective_end_date
        AND    xxas_com_sec_pkg.test('XXAS_EMP_CAND_MENU') = 'Y') p
        ]'
    );
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetMenuContext'
    ,p_description        => 'Get a person''s menu context'
    ,p_methods            => 'GET'
    ,p_path               => '/people/{personId}/menu-context'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
        SELECT XMLElement
                 ("data"
                 ,XMLForest
                   (papf.person_id "id"
                   ,'people' as "type"
                   ,xxas_util_pkg.get_formatted_name
                     (papf.full_name
                     ,papf.global_name
                     ,papf.local_name
                     ,papf.first_name
                     ,papf.last_name
                     ,papf.known_as) AS "title"
                   ,nvl(papf.known_as, papf.first_name) "abbrievatedTitle"
                   ,xxas_com_asg_pkg.get_person_sub_title(papf.person_id) "subTitle"
                   ,(SELECT XMLForest
                              (a.free_busy "freeBusy"
                              ,a.color "color"
                              ,lower(a.icon) "icon"
                              ,a.availability "title"
                              ,a.description "description"
                              )
                    FROM TABLE(xxas_com_avail_event_varray
                                 (xxas_com_avail_pkg.get_person_availability
                                   (papf.person_id))) a
                    WHERE a.free_busy &lt;&gt; 'U'
                     ) "availability"
                   )
                 ,(SELECT XMLElement
                            ("labels"
                            ,XMLAttributes
                               ('true' as "json:force-array"
                               ,'http://json.org/' as "xmlns:json")
                               ,XMLAgg(CASE WHEN x.worker_type IS NULL THEN
                                                NULL
                                            ELSE
                                                XMLElement
                                                      ("label"
                                                      ,XMLForest
                                                        (x.worker_type "title"
                                                        ,'energized' "color")
                                                      )
                                                    
                                       END)
						    ) labels
                   FROM  (SELECT DISTINCT extractvalue
                                           (s.non_search_content
                                           ,'//workerType') worker_type
                          FROM   xxas_asg_snapshot s
                         WHERE  s.person_id = {pathParam.personId}
                         AND    s.language = userenv('LANG')) x)
                 ,XMLElement
                   ("linked"
                   ,XMLElement
                     ("image"
                     ,xxas_com_rest_mgr_pkg.get_xml_entity('/people/' || papf.person_id || '/photo'))
                   ,(SELECT XMLElement
                             ("actions"
                             ,XMLAttributes
                               ('true' as "json:force-array"
                               ,'http://json.org/' as "xmlns:json")
                             ,XMLAgg
                               (XMLElement
                                 ("action"
                                 ,XMLForest
                                   (cd.contact_type_code "id"
                                  ,(CASE WHEN cd.contact_type_code = 'EMAIL' THEN
                                            cd.contact_data
                                          ELSE
                                            cd.contact_type_name || ': ' || cd.contact_data
                                    END) "title"
                                  ,cd.icon "icon"
                                  ,'LINK' "operation"
                                  ,(CASE WHEN cd.contact_type_code = 'EMAIL' THEN
                                           'mailto:' || cd.contact_data
                                         ELSE
                                           'tel:' || cd.contact_data
                                    END) "href"
                                   )
                                 ,XMLElement
                                   ("meta"
                                   ,XMLElement
                                     ("options"
                                     ,XMLForest
                                       ('light' "weight")
                                      )
                                    )
                                  )
                                ORDER BY cd.order_sequence)
                              )
                     FROM   TABLE(xxas_com_contact_pkg.contact_details_tbl
                              (papf.person_id, greatest(trunc(sysdate), papf.effective_start_date))) cd)
                   )
                 ) as "data"
        FROM   per_all_people_f papf

        WHERE ( (trunc(sysdate) BETWEEN
               papf.effective_start_date AND papf.effective_end_date)  OR 
			   ( papf.effective_start_date > trunc(sysdate) AND 
			   papf.effective_start_date = (select min(papf2.effective_start_date) 
			   FROM per_all_people_f papf2 
			   WHERE papf2.person_id = {pathParam.personId} ) ))
		    AND    papf.person_id = {pathParam.personId}
        ]'
    );
    
END;
/
COMMIT;
EXIT;
