/* $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
                       := 'VacancyApplicants';
                       
BEGIN
      
  xxas_com_sr_pkg.set_rest_service
    (p_service_name       => l_SERVICE
    ,p_description        => 'View and transact vacancies'
    ,p_path               => '/vacancies/{vacancyId}/applicants'
    ,p_consumes           => 'application/xml'
    ,p_produces           => 'application/xml'
    );
   
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetVacancyApplicants'
    ,p_description        => 'Get a list of applicants for a vacancy'
    ,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
                     ("XXAS_VACANCY_APPLICANT"
                     ,XMLForest
                       (va.assignment_id id
                       ,va.person_id
                       ,xxas_com_rest_util_pkg.uri_from_path
                           ('/vacancies/' || {pathParam.vacancyId} || '/applicants/' || va.assignment_id) href
                       ,xxas_com_rest_util_pkg.uri_from_path
                           ('/vacancies/' || {pathParam.vacancyId} || '/applicants/' || va.assignment_id || '/view-schema') view_schema_href
                       ,va.image_href
                       ,va.person_name
                       ,'location' contact1_icon
                       ,va.town_or_city
                         || decode(va.region_1, null, null, ', ')
                         || xxas_util_pkg.decode_lookup('GB_COUNTY', va.region_1) contact1_text
                       ,'smartphone' contact2_icon
                       ,va.phone_number contact2_text
                       ,'email' contact3_icon
                       ,va.is_terminated
                       ,decode(va.email_address
                               ,null, null
                               ,'&lt;a href="mailto:' || va.email_address || '"&gt;' || va.email_address || '&lt;/a&gt;') contact3_text
                       ,decode
                         (va.has_offer, 'N'
                         ,decode(xxas_com_sec_pkg.person_on_rec_team_edit_flag(va.vacancy_id,'Y','Y'),'Y',
                                 xxas_com_sec_pkg.test_instance('XXAS_APL_CREATE_OFFER_PERM', va.assignment_status_type_id),'N')
                          ) create_offer_allowed	
                       ,va.applicant_rank
                       ,va.interested
                       ,va.system_meaning system_status
                       ,va.assignment_status current_status
                       )
                     ,XMLElement
                       ("linked"
                       ,XMLElement
                         ("resumesLetters"
                         ,XMLElement
                           ("href"
                         ,xxas_com_rest_util_pkg.uri_from_path('/vacancies/' || va.vacancy_id ||'/applicants/'||va.assignment_id|| '/resumes-letters/'))
                         ,xxas_com_rest_mgr_pkg.get_xml_entity('/vacancies/' || va.vacancy_id ||'/applicants/'||va.assignment_id|| '/resumes-letters')
                         )
                       ,XMLElement
                         ("statuses"
                         ,XMLElement
                           ("href"
                           ,xxas_com_rest_util_pkg.uri_from_path('/vacancies/' || {pathParam.vacancyId} || '/applicants/' || va.assignment_id || '/statuses'))
                         ,xxas_com_rest_mgr_pkg.get_xml_entity('/vacancies/' || {pathParam.vacancyId} || '/applicants/' || va.assignment_id || '/statuses')
                         )
                       )
                     )
                   ORDER BY va.last_name, va.person_name
                   )
                 ) AS "data"
        FROM   xxas_vacancy_applicants_v va
        WHERE  va.vacancy_id = {pathParam.vacancyId}
        AND    xxas_com_sec_pkg.test_instance('XXAS_DATA_VAC_VIEW', va.vacancy_id) = 'Y'
      ]'
    );
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetVacancyApplicant'
    ,p_description        => 'Get an applicant for a vacancy'
    ,p_path               => '{applicantId}'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_bipub_dd_app       => 'XXAS'
    ,p_bipub_dd_code      => 'XXASAPL'
    ,p_sql                =>
      q'[
        SELECT XMLElement
                 ("data"
                 ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                 ,XMLAgg
                   (XMLElement
                     ("XXAS_VACANCY_APPLICANT"
                     ,XMLForest
                       (va.assignment_id id
                       ,va.person_id
                       ,xxas_com_rest_util_pkg.uri_from_path
                           ('/vacancies/' || {pathParam.vacancyId} || '/applicants/' || va.assignment_id) href
                         ,xxas_com_rest_util_pkg.uri_from_path
                             ('/vacancies/' || {pathParam.vacancyId} || '/applicants/' || va.assignment_id || '/view-schema') view_schema_href
                       ,va.image_href
                       ,va.person_name
                       ,'location' contact1_icon
                       ,va.town_or_city
                         || decode(va.region_1, null, null, ', ')
                         || xxas_util_pkg.decode_lookup('GB_COUNTY', va.region_1) contact1_text
                       ,'smartphone' contact2_icon
                       ,va.phone_number contact2_text
                       ,'email' contact3_icon
                       ,va.is_terminated
                       ,decode(va.email_address
                              ,null, null
                              ,'&lt;a href="mailto:' || va.email_address || '"&gt;' || va.email_address || '&lt;/a&gt;') contact3_text
                       ,decode
                         (va.has_offer, 'N'
                         ,decode(xxas_com_sec_pkg.person_on_rec_team_edit_flag(va.vacancy_id,'Y','Y'),'Y',
                                 xxas_com_sec_pkg.test_instance('XXAS_APL_CREATE_OFFER_PERM', va.assignment_status_type_id),'N')
                          ) create_offer_allowed	
                       ,va.applicant_rank
                       ,va.interested
                       ,va.system_meaning system_status
                       ,va.assignment_status current_status
                       )
                     ,XMLElement
                       ("linked"
                       ,XMLElement
                         ("resumesLetters"
                         ,XMLElement
                           ("href"
                         ,xxas_com_rest_util_pkg.uri_from_path('/vacancies/' || va.vacancy_id ||'/applicants/'||va.assignment_id|| '/resumes-letters/'))
                         ,xxas_com_rest_mgr_pkg.get_xml_entity('/vacancies/' || va.vacancy_id ||'/applicants/'||va.assignment_id|| '/resumes-letters')
                         )
                       ,XMLElement
                         ("statuses"
                         ,XMLElement
                           ("href"
                           ,xxas_com_rest_util_pkg.uri_from_path('/vacancies/' || {pathParam.vacancyId} || '/applicants/' || va.assignment_id || '/statuses'))
                         ,xxas_com_rest_mgr_pkg.get_xml_entity('/vacancies/' || {pathParam.vacancyId} || '/applicants/' || va.assignment_id || '/statuses')
                         )
                       ,decode
                          (nvl({requestParam.includeViewSchema}, 'false')
                          ,'true'
                          ,XMLElement
                            ("viewSchema"
                            ,XMLElement
                              ("href"
                              ,xxas_com_rest_util_pkg.uri_from_path('/vacancies/' || {pathParam.vacancyId} || '/applicants/' || va.assignment_id || '/view-schema/EXP'))
                            ,xxas_com_rest_mgr_pkg.get_xml_entity('/vacancies/' || {pathParam.vacancyId} || '/applicants/' || va.assignment_id || '/view-schema/EXP'))
                          ,null)
                       )
                     )
                   ORDER BY va.last_name, va.person_name
                   )
               ) AS "data"
        FROM   xxas_vacancy_applicants_v va
        WHERE  va.vacancy_id = {pathParam.vacancyId}
        AND    va.assignment_id IN
                 (SELECT to_number(column_value)
                  FROM   table(xxas_util_pkg.string_to_varchar2tbl({pathParam.applicantId})))
        AND    xxas_com_sec_pkg.test_instance('XXAS_DATA_VAC_VIEW', va.vacancy_id) = 'Y'
      ]'
    );
	
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetVacancyApplicantViewSchemaExport'
    ,p_description        => 'Gets an applicant view schema export'
    ,p_methods            => 'GET'
    ,p_path               => '{applicantId}/view-schema/EXP'
    ,p_sql                =>
      q'[
        SELECT xxas_applicant_pkg.get_view_schema_xml
                (xxas_applicant_pkg.base_to_obj({pathParam.applicantId},'EXP')) "SCHEMA"
        FROM   dual
        WHERE  xxas_com_sec_pkg.test_instance('XXAS_DATA_VAC_VIEW', {pathParam.vacancyId}) = 'Y'
      ]'
    ); 
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetVacancyApplicantViewSchema'
    ,p_description        => 'Gets an applicant view schema'
    ,p_methods            => 'GET'
    ,p_path               => '{applicantId}/view-schema'
    ,p_sql                =>
      q'[
        SELECT xxas_applicant_pkg.get_view_schema_xml
                (xxas_applicant_pkg.base_to_obj({pathParam.applicantId})) "SCHEMA"
        FROM   dual
        WHERE  xxas_com_sec_pkg.test_instance('XXAS_DATA_VAC_VIEW', {pathParam.vacancyId}) = 'Y'
      ]'
    );
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'PutVacancyApplicantViewSchema'
    ,p_description        => 'Refreshes an applicant view schema'
    ,p_methods            => 'PUT'
    ,p_path               => '{applicantId}/view-schema'
    ,p_plsql              =>
      q'[
        DECLARE
          l_schema   XMLType;
        BEGIN
       
          --
          -- Return an updated schema based on the given data
          --
          l_schema := xxas_applicant_pkg.get_view_schema_xml
            (p_assignment_id      => {pathParam.applicantId}
            ,p_business_group_id  => xxas_assignment_pkg.get_bg_from_asg({pathParam.applicantId})
            ,p_view_data_xml      => XMLType({requestParam._entity})
            ,p_refresh_event      => {requestParam.event}
            ,p_refresh_field_name => {requestParam.field});
              
          SELECT XMLElement
                   ("SCHEMA"
                   ,l_schema)
          INTO   l_schema
          FROM   dual;
          
          {response} := xxas_http_response.ok().entity(l_schema).build();

        END
      ]'
    );
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'UpdateVacancyApplicant'
    ,p_description        => 'Updates an applicant for a vacancy'
    ,p_path               => '{applicantId}'
    ,p_methods            => 'PUT'
    ,p_plsql              =>
      q'[
        DECLARE
          
          CURSOR csr_apl_details
            (cp_xml IN XMLType)
          IS
          SELECT *
          FROM   XMLTable
                   ('/XXAS_VACANCY_APPLICANT'
                   PASSING (cp_xml)
                   COLUMNS applicant_rank NUMBER path 'APPLICANT_RANK'
                          ,interested     NUMBER path 'INTERESTED'
                    ) apl;
                        
          l_apl_details csr_apl_details%ROWTYPE;
          l_resp        xxas_http_resp_obj;
          
        BEGIN
        
          IF NOT xxas_com_sec_pkg.test_instance_rtn_boolean
            ('XXAS_DATA_VAC_VIEW', {pathParam.vacancyId}) THEN
            l_resp := xxas_http_response
                        .status(xxas_http_resp_status_pkg.unauthorised)
                        .build();
          ELSE
          
            OPEN  csr_apl_details(XMLType({requestParam._entity}));
            FETCH csr_apl_details INTO l_apl_details;
            CLOSE csr_apl_details;

            xxas_applicant_pkg.update_applicant
              (p_assignment_id  => {pathParam.applicantId}
              ,p_effective_date => trunc(sysdate)
              ,p_applicant_rank => l_apl_details.applicant_rank
              ,p_interested     => l_apl_details.interested);
              
            l_resp := xxas_com_rest_mgr_pkg.get_xml
                       ('vacancies/' || {pathParam.vacancyId} || '/applicants/' || {pathParam.applicantId});
            
          END IF;
          
          {response} := l_resp;

        END
      ]'
    );
    
END;
/
COMMIT;
EXIT;
