/* $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
                       := 'Forms';
                       
BEGIN
      
  xxas_com_sr_pkg.set_rest_service
    (p_service_name       => l_SERVICE
    ,p_description        => 'Manage forms' 
    ,p_path               => 'form'    
    ,p_consumes           => 'application/xml'
    ,p_produces           => 'application/xml'
    );

   
    xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'FindFormFields'
    ,p_description        => 'Finds form fields'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_path               => '{formId}/form-fields'
    ,p_sql                =>
      q'[  SELECT XMLElement
                 ("data"
                 ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                  ,xxas_forms_pkg.form_fields_list_xml({pathParam.formId}))  "data" from dual
      ]'
    );                           
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'DuplicateForm'
    ,p_description        => 'Duplicates a form'
    ,p_path               => '{formId}/duplicate-form'
    ,p_methods            => 'PUT'
    ,p_plsql              =>
      q'[
        DECLARE
          l_xdb_uri VARCHAR2(1000);
          l_resp        xxas_http_resp_obj;
          l_new_form_id VARCHAR2(100);

        BEGIN

          l_xdb_uri := xxas_forms_pkg.form_xdb_uri({pathParam.formId});
          -- Check access to delete the plan
          IF xxas_com_sec_pkg.test_instance('XXAS_BA_DUPLICATE_FORM',l_xdb_uri,{pathParam.formId}) = 'Y' THEN
               
            l_new_form_id := xxas_forms_pkg.duplicate_form({pathParam.formId},l_xdb_uri);
          
          END IF;
          
          l_resp := xxas_http_response
                      .ok(xxas_com_msg_pkg.get_success_message_xml
                           (xxas_com_msg_type(xxas_util_pkg.app, 'XXAS_COM_CHNG_SAVE', null)
                           ,'forms?modal=/edit-form/'||l_new_form_id)
                         )
                      .build();
         
         {response} := l_resp;
         
        END
      ]'
    ); 
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'DeleteForm'
    ,p_description        => 'Deletes a form'
    ,p_path               => '{formId}/delete-form'
    ,p_methods            => 'DELETE'
    ,p_plsql              =>
      q'[
        DECLARE
          l_xdb_uri VARCHAR2(1000);
          l_resp        xxas_http_resp_obj;
        BEGIN
          l_xdb_uri := xxas_forms_pkg.form_xdb_uri({pathParam.formId});
          -- Check access to delete the plan
          IF xxas_com_sec_pkg.test_instance('XXAS_BA_DELETE_FORM',l_xdb_uri,{pathParam.formId}) = 'Y' THEN
               
            xxas_forms_pkg.delete_form(l_xdb_uri);
          
          END IF;
          
          l_resp := xxas_http_response
                      .ok(xxas_com_msg_pkg.get_success_message_xml
                           (xxas_com_msg_type(xxas_util_pkg.app, 'XXAS_COM_CHNG_SAVE', null)
                           ,'forms'
                           )
                         )
                      .build();
         
         {response} := l_resp;
         
        END
      ]'
    );    
    
  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'DeleteFormField'
    ,p_description        => 'Deletes a form field'
    ,p_path               => '{formId}/delete-form-field/{fieldId}'
    ,p_methods            => 'DELETE'
    ,p_plsql              =>
      q'[
        DECLARE
          l_xdb_uri VARCHAR2(1000);
          l_resp        xxas_http_resp_obj;
        BEGIN
          l_xdb_uri := xxas_forms_pkg.form_xdb_uri({pathParam.formId});
          -- Check access to delete the plan
          IF xxas_com_sec_pkg.test_instance('XXAS_BA_DELETE_FORM_FIELD',l_xdb_uri,{pathParam.formId},{pathParam.fieldId}) = 'Y' THEN
               
            xxas_forms_pkg.delete_form_field(l_xdb_uri, {pathParam.fieldId});
          
          END IF;
          
          l_resp := xxas_http_response
                      .ok(xxas_com_msg_pkg.get_success_message_xml
                           (xxas_com_msg_type(xxas_util_pkg.app, 'XXAS_COM_CHNG_SAVE', null)
                           ,'forms?modal=/form-view/'||{pathParam.formId}
                           )
                         )
                      .build();
         
         {response} := l_resp;
         
        END
      ]'
    );
    
    xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'FindContainerFormFields'
    ,p_description        => 'Finds form fields'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_path               => 'container-form-fields/{formId}/{formFieldId}'
    ,p_sql                =>
      q'[   SELECT XMLElement
                 ("data"
                 ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                  ,xxas_forms_pkg.form_fields_list_xml({pathParam.formId},REPLACE({pathParam.formFieldId},'-','_')))  "data" from dual
      ]'
    );   
    
    xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'FindFormFields11'
    ,p_description        => 'Finds form fields'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_path               => 'form-fields11/{xdbURI}'
    ,p_sql                =>
      q'[   SELECT XMLElement
                 ("data"
                 ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                 ,XMLAgg
                   (XMLElement
                     ("field"
                     ,XMLForest
                       (ff.field_type
                        ,ff.field_id
                        ,ff.description
                        ,ff.label
                        ,ff.visible
                        ,ff.enabled
                        ,ff.required
                        ,ff.hide_if
                        ,ff.post_to_href
                        ,ff.refresh_on_change
                        ,ff.max_length
                        ,ff.service_name
                        ,ff.google_place_type
                        ,ff.place_holder
                        ,ff.richT_text
                        ,ff.list_function
                        ,ff.style
                        ,ff.weight
                        ,ff.color
                        ,ff.value_generator
                        ,ff.max_attachments
                        ,ff.entity_name
                        ,ff.category_code
                        ,ff.attachment_title
                        ,ff.attachmentD_description
                        ,ff.pk1_field_value
                        ,ff.pk2_field_value
                        ,ff.pk3_field_value
                        ,ff.pk4_field_value
                        ,ff.pk5_field_value
                        ,ff.file_types
                        ,ff.file_name
                        ,ff.file_href
                        ,ff.button_text
                        ,ff.post_new_to_href
                        ,ff.decimal_precision
                        ,ff.date_format
                        ,ff.list_provider_type
                        ,ff.list_provider
                        ,ff.lookup_type
                        ,ff.value_set
                        ,ff.rest_api
                        ,ff.positive_boolean_value
                        ,ff.longlist
                        ,ff.no_data_message
                        ,ff.create_allowed
                        ,ff.create_label
                        ,ff.min_rows
                        ,ff.max_rows
                        ,ff.multirow_xdb_uri
                        ,ff.include_empty_row
                        ,ff.flexfield
                        ,ff.columnName_prefix
                        ,ff.question_ref_id
                        ,ff.application_id
                        ,ff.descriptive_flexfield_name
                        ,ff.descriptive_flex_context_code)
                     )
                   )
                 ) "data" 
                 FROM
                  XMLTable
           ('//EditFields/*'
            PASSING (SELECT XDBURIType(any_path).getxml()
                     FROM   xxas_forms_v
                     WHERE  any_path = REPLACE({pathParam.xdbURI},'_','/') )
            COLUMNS  field_type               VARCHAR2(4000)  path 'name()'
                    ,field_id                 VARCHAR2(4000)  path '@id'
                    ,description              VARCHAR2(4000)  PATH  '@description'
                    ,label                    VARCHAR2(4000)  PATH  '@label'
                    ,visible                  VARCHAR2(4000)  PATH  '@visible'
                    ,enabled                  VARCHAR2(4000)  PATH  '@enabled'
                    ,required                 VARCHAR2(4000)  PATH  '@required'
                    ,hide_if                  VARCHAR2(4000)  PATH  '@hideIf'
                    ,post_to_href             VARCHAR2(4000)  PATH  '@postToHref'  
                    ,refresh_on_change        VARCHAR2(4000)  PATH  '@refreshOnChange'
                    ,max_length               VARCHAR2(4000)  PATH  '@maxlength'
                    ,service_name             VARCHAR2(4000)  PATH  '@serviceName'
                    ,google_place_type        VARCHAR2(4000)  PATH  '@googlePlaceType'
                    ,place_holder             VARCHAR2(4000)  PATH  '@placeholder'    
                    ,richT_text               VARCHAR2(4000)  PATH  '@richText'
                    ,list_function            VARCHAR2(4000)  PATH  '@listFunction'
                    ,style                    VARCHAR2(4000)  PATH  '@style'
                    ,weight                   VARCHAR2(4000)  PATH  '@weight'
                    ,color                    VARCHAR2(4000)  PATH  '@color'
                    ,value_generator          VARCHAR2(4000)  PATH  '@valueGenerator'
                    ,max_attachments          VARCHAR2(4000)  PATH  '@maxAttachments'
                    ,entity_name              VARCHAR2(4000)  PATH  '@entityName' 
                    ,category_code            VARCHAR2(4000)  PATH  '@categoryCode'
                    ,attachment_title         VARCHAR2(4000)  PATH  '@attachmentTitle'
                    ,attachmentD_description  VARCHAR2(4000)  PATH  '@attachmentDescription'
                    ,pk1_field_value          VARCHAR2(4000)  PATH  '@pk1FieldValue'
                    ,pk2_field_value          VARCHAR2(4000)  PATH  '@pk2FieldValue'
                    ,pk3_field_value          VARCHAR2(4000)  PATH  '@pk3FieldValue'
                    ,pk4_field_value          VARCHAR2(4000)  PATH  '@pk4FieldValue'
                    ,pk5_field_value          VARCHAR2(4000)  PATH  '@pk5FieldValue'
                    ,file_types               VARCHAR2(4000)  PATH  '@fileTypes'
                    ,file_name                VARCHAR2(4000)  PATH  '@fileName'
                    ,file_href                VARCHAR2(4000)  PATH  '@fileHref'
                    ,button_text              VARCHAR2(4000)  PATH  '@buttonText'
                    ,post_new_to_href         VARCHAR2(4000)  PATH  '@postNewToHref' 
                    ,decimal_precision        VARCHAR2(4000)  PATH  '@decimalPrecision'
                    ,date_format              VARCHAR2(4000)  PATH  '@dateFormat' 
                    ,list_provider_type       VARCHAR2(4000)  PATH  '@listProviderType'
                    ,list_provider            VARCHAR2(4000)  path  '@listProvider'
                    ,lookup_type              VARCHAR2(4000)  PATH  '@lookupType'
                    ,value_set                VARCHAR2(4000)  PATH  '@valueSet'
                    ,rest_api                 VARCHAR2(4000)  PATH  '@restAPI'
                    ,positive_boolean_value   VARCHAR2(4000)  PATH  '@positiveBooleanValue'
                    ,longlist                 VARCHAR2(4000)  PATH  '@longlist'
                    ,no_data_message          VARCHAR2(4000)  PATH  '@noDataMessage'
                    ,create_allowed           VARCHAR2(4000)  PATH  '@createAllowed'
                    ,create_label             VARCHAR2(4000)  PATH  '@createLabel'
                    ,min_rows                 VARCHAR2(4000)  PATH  '@minRows'
                    ,max_rows                 VARCHAR2(4000)  PATH  '@maxRows'
                    ,multirow_xdb_uri         VARCHAR2(4000)  PATH  '@xdbUri'
                    ,include_empty_row        VARCHAR2(4000)  PATH  '@includeEmptyRow'
                    ,flexfield                VARCHAR2(4000)  PATH  '@flexfield'
                    ,columnName_prefix        VARCHAR2(4000)  PATH  '@columnNamePrefix'
                    ,question_ref_id          VARCHAR2(4000)  PATH  '@questionRefId'
                    ,application_id            VARCHAR2(4000)  PATH  '@applicationId'
                    ,descriptive_flexfield_name VARCHAR2(4000)  PATH  '@descriptiveFlexfieldName'
                    ,descriptive_flex_context_code VARCHAR2(4000)  PATH  '@descriptiveFlexContextCode')
                    ff
      ]'
    );                           

    xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'FindFormFieldAttribute'
    ,p_description        => 'Find Form Field Attribute'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_path               => 'form-field-attributes/{formId}/{formFieldId}/{fieldType}'
    ,p_sql                =>
      q'[   SELECT XMLElement
                 ("data"
                 ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                 ,XMLElement
                     ("field"
                     ,XMLForest
                       ( 'flexible' "type"
                        ,ff.field_type "line2"
                        ,CASE upper(ff.field_type)
                          WHEN 'FLEXFIELD' THEN
                            ff.descriptive_flexfield_name
                          ELSE
                            ff.field_id
                          END "line1"
                        ,CASE upper(ff.field_type)
                          WHEN 'FLEXFIELD' THEN
                            ff.descriptive_flexfield_name
                          ELSE
                            ff.field_id
                          END field_id   
                        ,xxas_com_rest_util_pkg.uri_from_app_route('form-view/'||{pathParam.formId}||'?modal=/form-field-attributes')||'/'||{pathParam.formId}||'_'||REPLACE(field_id,'_','-')||'_'||ff.field_type "href"
                        ,ff.description
                        ,ff.label
                        ,ff.visible
                        ,ff.enabled
                        ,ff.required
                        ,ff.hide_if
                        ,ff.post_to_href
                        ,ff.refresh_on_change
                        ,ff.max_length
                        ,ff.service_name
                        ,ff.google_place_type
                        ,ff.place_holder
                        ,ff.richT_text
                        ,ff.list_function
                        ,ff.style
                        ,ff.weight
                        ,ff.color
                        ,ff.value_generator
                        ,ff.max_attachments
                        ,ff.entity_name
                        ,ff.category_code
                        ,ff.attachment_title
                        ,ff.attachmentD_description
                        ,ff.pk1_field_value
                        ,ff.pk2_field_value
                        ,ff.pk3_field_value
                        ,ff.pk4_field_value
                        ,ff.pk5_field_value
                        ,ff.file_types
                        ,ff.file_name
                        ,ff.file_href
                        ,ff.button_text
                        ,ff.post_new_to_href
                        ,ff.decimal_precision
                        ,ff.date_format
                        ,ff.list_provider_type
                        ,ff.list_provider
                        ,ff.lookup_type
                        ,ff.value_set
                        ,ff.rest_api
                        ,ff.positive_boolean_value
                        ,ff.longlist
                        ,ff.no_data_message
                        ,ff.create_allowed
                        ,ff.create_label
                        ,ff.min_rows
                        ,ff.max_rows
                        ,ff.include_empty_row
                        ,ff.flexfield
                        ,ff.columnName_prefix
                        ,ff.question_ref_id
                        ,ff.application_id
                        ,ff.descriptive_flexfield_name
                        ,ff.descriptive_flex_context_code)
                     )
                   
                 ) "data" 
               FROM
                  XMLTable
           ('/*'
            PASSING (xxas_forms_pkg.form_element_xml({pathParam.formId},{pathParam.fieldType},{pathParam.formFieldId}) )
            COLUMNS  field_type               VARCHAR2(4000)  path 'name()'
                    ,field_id                 VARCHAR2(4000)  path '@id'
                    ,description              VARCHAR2(4000)  PATH  '@description'
                    ,label                    VARCHAR2(4000)  PATH  '@label'
                    ,visible                  VARCHAR2(4000)  PATH  '@visible'
                    ,enabled                  VARCHAR2(4000)  PATH  '@enabled'
                    ,required                 VARCHAR2(4000)  PATH  '@required'
                    ,hide_if                  VARCHAR2(4000)  PATH  '@hideIf'
                    ,post_to_href             VARCHAR2(4000)  PATH  '@postToHref'  
                    ,refresh_on_change        VARCHAR2(4000)  PATH  '@refreshOnChange'
                    ,max_length               VARCHAR2(4000)  PATH  '@maxlength'
                    ,service_name             VARCHAR2(4000)  PATH  '@serviceName'
                    ,google_place_type        VARCHAR2(4000)  PATH  '@googlePlaceType'
                    ,place_holder             VARCHAR2(4000)  PATH  '@placeholder'    
                    ,richT_text               VARCHAR2(4000)  PATH  '@richText'
                    ,list_function            VARCHAR2(4000)  PATH  '@listFunction'
                    ,style                    VARCHAR2(4000)  PATH  '@style'
                    ,weight                   VARCHAR2(4000)  PATH  '@weight'
                    ,color                    VARCHAR2(4000)  PATH  '@color'
                    ,value_generator          VARCHAR2(4000)  PATH  '@valueGenerator'
                    ,max_attachments          VARCHAR2(4000)  PATH  '@maxAttachments'
                    ,entity_name              VARCHAR2(4000)  PATH  '@entityName' 
                    ,category_code            VARCHAR2(4000)  PATH  '@categoryCode'
                    ,attachment_title         VARCHAR2(4000)  PATH  '@attachmentTitle'
                    ,attachmentD_description  VARCHAR2(4000)  PATH  '@attachmentDescription'
                    ,pk1_field_value          VARCHAR2(4000)  PATH  '@pk1FieldValue'
                    ,pk2_field_value          VARCHAR2(4000)  PATH  '@pk2FieldValue'
                    ,pk3_field_value          VARCHAR2(4000)  PATH  '@pk3FieldValue'
                    ,pk4_field_value          VARCHAR2(4000)  PATH  '@pk4FieldValue'
                    ,pk5_field_value          VARCHAR2(4000)  PATH  '@pk5FieldValue'
                    ,file_types               VARCHAR2(4000)  PATH  '@fileTypes'
                    ,file_name                VARCHAR2(4000)  PATH  '@fileName'
                    ,file_href                VARCHAR2(4000)  PATH  '@fileHref'
                    ,button_text              VARCHAR2(4000)  PATH  '@buttonText'
                    ,post_new_to_href         VARCHAR2(4000)  PATH  '@postNewToHref' 
                    ,decimal_precision        VARCHAR2(4000)  PATH  '@decimalPrecision'
                    ,date_format              VARCHAR2(4000)  PATH  '@dateFormat' 
                    ,list_provider_type       VARCHAR2(4000)  PATH  '@listProviderType'
                    ,list_provider            VARCHAR2(4000)  path  '@listProvider'
                    ,lookup_type              VARCHAR2(4000)  PATH  '@lookupType'
                    ,value_set                VARCHAR2(4000)  PATH  '@valueSet'
                    ,rest_api                 VARCHAR2(4000)  PATH  '@restAPI'
                    ,positive_boolean_value   VARCHAR2(4000)  PATH  '@positiveBooleanValue'
                    ,longlist                 VARCHAR2(4000)  PATH  '@longlist'
                    ,no_data_message          VARCHAR2(4000)  PATH  '@noDataMessage'
                    ,create_allowed           VARCHAR2(4000)  PATH  '@createAllowed'
                    ,create_label             VARCHAR2(4000)  PATH  '@createLabel'
                    ,min_rows                 VARCHAR2(4000)  PATH  '@minRows'
                    ,max_rows                 VARCHAR2(4000)  PATH  '@maxRows'
                    ,include_empty_row        VARCHAR2(4000)  PATH  '@includeEmptyRow'
                    ,flexfield                VARCHAR2(4000)  PATH  '@flexfield'
                    ,columnName_prefix        VARCHAR2(4000)  PATH  '@columnNamePrefix'
                    ,question_ref_id          VARCHAR2(4000)  PATH  '@questionRefId'
                    ,application_id            VARCHAR2(4000)  PATH  '@applicationId'
                    ,descriptive_flexfield_name VARCHAR2(4000)  PATH  '@descriptiveFlexfieldName'
                    ,descriptive_flex_context_code VARCHAR2(4000)  PATH  '@descriptiveFlexContextCode')
                    ff

      ]'
    );    
     
    xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'LabelFields'
    ,p_description        => 'Finds labels for attributes'
    ,p_methods            => 'GET'
    ,p_remove_root_ele    => true
    ,p_path               => '{formId}/auth-fields/{input}'
    ,p_sql                =>
      q'[  SELECT XMLElement
                 ("data"
                 ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                  ,XMLAGG
                    (XMLElement
                      ("fields"
                       ,XMLForest
                       (ffa.field_id "id"
                       ,ffa.label    "label"
                       ,ffa.label    "value")
                      )
                    )
                  )"data" 
           from xxas_form_field_attributes_v ffa
           where ffa.form_id = {pathParam.formId}
           and ffa.field_type = {pathParam.input}
      ]'
    );
	 
	 
END;
/
COMMIT;
EXIT;