create or replace package app is
    
  SUBTYPE st_maxvarchar2 IS VARCHAR2(32767) ; 
    
  PROCEDURE data(
    p_callback  IN VARCHAR2,
    p_net_id    IN VARCHAR2
    );  
  
  PROCEDURE get_org_tree(p_callback IN VARCHAR2,
                         p_node     IN VARCHAR2 DEFAULT NULL,
                         p_netid    IN VARCHAR2 DEFAULT NULL);
end app;
/
CREATE OR REPLACE PACKAGE BODY app IS
  
--  SUBTYPE st_maxvarchar2 IS VARCHAR2(32767) ; 
  v_result                st_maxvarchar2:='';

  PROCEDURE data(
    p_callback  IN VARCHAR2,
    p_net_id    IN VARCHAR2
    )
    IS
  /* -------------------------------------------------------------------------------------------
      Procedure: data
      Author:    Brian Griffin
      Created:   June 7 2012
      Purpose:   Given that the application is now ready, return the user's login information
                  
      Parameters:
        p_callback
        p_net_id
        
      Example usage: 
             
      Copyright UC Riverside, 2012
  ------------------------------------------------------------------------------------------- */
    -- EACS Variables:
    v_EACS_URL            VARCHAR2(100) := ''; 
    v_schema              VARCHAR2(10) := '';
    k_APP_ID              CONSTANT VARCHAR2(10) := '129'; -- FBO Reporting System
    k_EACSDEV             CONSTANT VARCHAR2(100) := 'http://eacsdev.ucr.edu/';
    k_EACSTEST            CONSTANT VARCHAR2(190) := 'http://iastest.ucr.edu/';
    k_EACSPROD            CONSTANT VARCHAR2(190) := 'http://eacs.ucr.edu/';
    v_EACS_Acct_Struct    st_maxvarchar2 := '';
    v_netid               VARCHAR2(100) := ''; 
    v_first               VARCHAR2(100) := '';
    v_last                VARCHAR2(100) := '';

    -- XML Variables: 
    v_XML_Req         VARCHAR2(2000); -- The XML request
    v_XML_data        xmltype;
    v_xml_value       st_maxvarchar2 := '';
    v_xml_user_role   st_maxvarchar2 := '';
    v_pos             INTEGER :=1;

    
  BEGIN
    -- ALlow for a switch
    IF p_net_id = 'griff' THEN
      v_netid := 'griff'; 
    ELSE
      v_netid := p_net_id;
    END IF;
    
    -- Determine the environment we are in 
    SELECT CASE 
      WHEN instr(uu.username, 'DEV') > 0  THEN '_dev'
      WHEN instr(uu.username, 'TEST') > 0 THEN '_test'
      ELSE ''
      END environ_url_suffix
    INTO v_schema
    FROM user_users uu;
        
    -- Construct the URL to call
    v_EACS_URL := '/eacs' || v_schema || '/EACS_CNC_UTIL.Check_user?v_user_netid=' || v_netid || '&v_app_id=' || k_APP_ID;
    IF v_schema = '_dev' THEN
      v_EACS_URL := k_EACSDEV || v_EACS_URL;
    ELSIF v_schema = '_test' THEN
      v_EACS_URL := k_EACSTEST || v_EACS_URL;
    ELSE
      v_EACS_URL := k_EACSPROD || v_EACS_URL;
    END if;
    
    -- Using a web service, request the data 
    v_XML_Req := utl_http.request(v_EACS_URL);
    -- and convert it into an Oracle XML object that can be parsed. 
    v_XML_data := xmltype.createxml(v_XML_Req);

    -- Pull out the first occurance of the USER ROLE 
    SELECT EXTRACTVALUE(v_XML_data, '/USER_XML/SAALIST/SAA_INFO[1]/USER_ROLE') 
    INTO v_xml_user_role
    FROM dual;
    
    -- Pull out the first occurance of the EACS role 
    SELECT EXTRACTVALUE(v_XML_data, '/USER_XML/SAALIST/SAA_INFO[1]/ORG_VALUE') 
    INTO v_xml_value
    FROM dual;
    
    -- Since multiple roles maybe assigned to a user, we must scan through them all until NULL are returned 
    WHILE v_xml_value IS NOT NULL LOOP
      
      v_pos := v_pos + 1;
      v_EACS_Acct_Struct := v_EACS_Acct_Struct ||  '''' || v_xml_value || ''', ';
      
      SELECT EXTRACTVALUE(v_XML_data, '/USER_XML/SAALIST/SAA_INFO[' || v_pos || ']/ORG_VALUE')
      INTO v_xml_value
      FROM dual;

    END LOOP;
    v_EACS_Acct_Struct := rtrim(v_EACS_Acct_Struct, ', ');

    SELECT u.first, u.last
    INTO   v_first, v_last
    FROM   ucr_netid@acctuser_prod10 u
    WHERE  u.net_id = p_net_id;
        
    /*htp.p(p_callback || '
      ({
        "success": true, 
        "welcome_message":"Welcome to PIWRS",
        "appData": {
          "netid":"' || p_net_id || '",
          "user_name":"' || v_first || '",
          "last_name":"' || v_last || '",
          "acct_struct":"'|| v_EACS_Acct_Struct ||'",
          "user_role":"' || v_xml_user_role || '",
          "fiscal_year":"2012",
          "accounting_period":"12",
          "app_name":"PIWRS",
          "app_title":"Financial Budget Office Reporting System",
          "app_version":"0.1"
          } 
      }) 
    ');*/
    
    htp.p(p_callback || '({
      "success": true, 
      "welcome_message":"Welcome to PIWRS",
      "appData": {
        "netid"               : "paulinel",
        "fiscal_year"         : "2014",
        "cal_year"            : "2013",
        "accounting_period"   : "6",
        "window_closed"       : "Y",
        "user_name"           : "Juan",
        "last_name"           : "Gonzales",
        "role_name"           : "admin",
        "switch_user"         : true,
        "acct_struct"         : "ORG??",
        "num_funds_to_certify": 0, 
        "num_funds_collab"    : 0, 
        "viewPiList"          : false
      } 
      })
   ');     

    EXCEPTION WHEN OTHERS THEN 
      htp.p(p_callback || '({
        "success": false, 
        "message": "' || SQLERRM || '",
        "appData": {
          "netid"            : "' || p_net_id || '",
          "user_name"        : "' || v_first || '",
          "last_name"        : "' || v_last || '",
          "acct_struct"      : "'|| v_EACS_Acct_Struct ||'",
          "user_role"        : "' || v_xml_user_role || '",
          "fiscal_year"      : "2012",
          "accounting_period": "12",
          "app_name"         : "PIWRS",
          "app_title"        : "PI Web Reporting System",
          "app_version":"0.1"
        } 
      }) 
      ');
      
    END data;
  
  PROCEDURE get_org_tree(p_callback IN VARCHAR2,
                         p_node     IN VARCHAR2 DEFAULT NULL,
                         p_netid    IN VARCHAR2 DEFAULT NULL) IS
    v_response     st_maxvarchar2 := '';  
  BEGIN
                
    -- At the root level of the Nav tree, we'll need to get the user's EACS accountability structure
    IF p_node = 'root' THEN
      v_response := p_callback || '({
       "text"    : ".",
       "children": [
       {
         "text"       : "Welcome",
         "iconCls"    : "task",
         "leaf"       : "true",
         "id"         : "welc",       
         "report_type": "dept",         
         "qtip"       : "Click here to view PI Reports",
         "comments"   : "update",
         "expanded"   : "false"
       },
       {
         "text"       : "View PI Reports",
         "iconCls"    : "task",
         "leaf"       : "true",
         "id"         : "vpir",       
         "report_type": "dept",         
         "qtip"       : "Click here to view PI Reports",
         "comments"   : "update",
         "expanded"   : "false"
       },             
       {                 
         "text"       : "Annual Certifications Due (0)",
         "iconCls"    : "task",
         "leaf"       : "true",
         "id"         : "acd",       
         "report_type": "dept",
         "qtip"       : "Click to view Annual Certs Due",
         "comments"   : "update",
         "expanded"   : "false"
        },
       ';
       
       IF p_netid = 'newAlias' THEN
         v_response := v_response || '
       {                 
         "text"       : "Annual Certifications Completed",
         "iconCls"    : "task",
         "leaf"       : "true",
         "id"         : "acc",       
         "report_type": "dept",
         "qtip"       : "Click to view Annual Certs Completed",
         "comments"   : "update",
         "expanded"   : "false"
        },
        {                 
         "text"       : "View Reports",
         "iconCls"    : "task",
         "leaf"       : "true",
         "id"         : "vpa",       
         "report_type": "dept",
         "qtip"       : "Click to view Admin Reports",
         "comments"   : "update",
         "expanded"   : "false"
        },  
         ';  
       END IF;
       
       v_response := v_response || '   
        {                 
         "text"       : "Shared Contract and Grant Funds",
         "iconCls"    : "task",
         "leaf"       : "true",
         "id"         : "D01299",       
         "report_type": "cng",
         "qtip"       : "Click to view Shared C&G Funds",
         "comments"   : "update",
         "expanded"   : "false"
        },                 
        {                 
         "text"       : "Preferences",
         "iconCls"    : "task",
         "leaf"       : "true",
         "id"         : "pref",       
         "report_type": "dept",
         "qtip"       : "Click to view Preferences",
         "comments"   : "update",
         "expanded"   : "false"
        },
        {                 
         "text"       : "Understanding PIWRS",
         "iconCls"    : "task",
         "leaf"       : "true",
         "id"         : "help",       
         "report_type": "dept",
         "qtip"       : "Click to view PIWRS help info",
         "comments"   : "update",
         "expanded"   : "false"
        },
        {                 
         "text"       : "Exit Application",
         "iconCls"    : "task",
         "leaf"       : "true",
         "id"         : "exit",       
         "report_type": "dept",
         "qtip"       : "Click to exit application",
         "comments"   : "update",
         "expanded"   : "false"
        }
        ], 
        "success": true
      })
      ';  
          
    /*ELSIF SUBSTR(p_node, 1, 3) = 'ORG' THEN
      v_response := p_callback || '(
        {
       "text":".",
       "children": [
          {
         "text": "D01050 - Nematology",
         "iconCls":"task-folder",
         "id": "D01050",       
         "report_type": "dept",
         "qtip": "Nematology",
         "comments": "update",
         "expanded": "false"},{
                 
         "text": "D01209 - Dining Services",
         "iconCls":"task-folder",
         "id": "D01209",       
         "report_type": "dept",
         "qtip": "Dining Services",
         "comments": "update",
         "expanded": "false"},{
                 
         "text": "DIV171 - Academic Personnel",
         "iconCls":"task-folder",
         "id": "DIV171",       
         "report_type": "div",
         "qtip": "Academic Personnel",
         "comments": "update",
         "expanded": "false"},{                 

         "text": "ORG21 - Computing & Communications",
         "iconCls":"task-folder",
         "id": "ORG21",       
         "report_type": "org",
         "qtip": "Computing & Communications",
         "comments": "update",
         "expanded": "false"},{
                 
         "text": "ORG39 - Finance & Business Operations",
         "iconCls":"task-folder",
         "id": "ORG39",       
         "report_type": "org",
         "qtip": "Finance & Business Operations",
         "comments": "update",
         "expanded": "false"
        }
        ], 
        "success":true
      })
      ';*/
        
    ELSE 
      htp.p(p_callback || '( {
        "success":false, 
        "message":"' || translate(dbms_utility.format_error_stack || ' ' || dbms_utility.format_error_backtrace, '"', '`') || ' ",
        "v_response":"' || v_response || '"
        } )') ;
      RETURN;      
    END IF;         
    
    htp.p(v_response);
  EXCEPTION WHEN OTHERS THEN 
      --v_result := login.display_error_info('griff', DBMS_UTILITY.FORMAT_ERROR_STACK, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, $$PLSQL_UNIT, USERENV('sessionid'), 'text');
      htp.p(p_callback || '( {
        "success":false, 
        "message":"' || v_result || '"
        } )') ;
      RETURN;
    
  END get_org_tree;
    
BEGIN
  NULL;
END app;
/