create or replace package MIP_FILES is

  -- Author  : PRIESTJ
  -- Created : 02/11/2007 10:59:52
  -- Purpose : Handle files for the webMIP system

  -- Public function and procedure declarations
function set_file_association(p_uri in varchar2,
                              p_description in varchar2,
                              p_docu_type in varchar2,
                              p_rt_code in varchar2,
                              p_qute_id in number,
                              p_enqu_id in number,
                              p_drwg_code in varchar2,
                              p_doro_type in varchar2) return boolean;
                              
PROCEDURE export_table_to_csv(p_table IN VARCHAR2,
                             p_column_headers IN VARCHAR2,
                             p_delimiter IN VARCHAR2 DEFAULT ',');

end MIP_FILES;
/
create or replace package body MIP_FILES as
  /*
   FUNCTION drawing_file_exists
   - takes the drawing code and searches for associated file in APEX Application
   - Files, returns true if the file exists or false if it does not exist
   %param p_drwg_code - the drawing code
  */
  function drawing_file_exists(p_drwg_code varchar2) return boolean is
    l_file_exists  number;
    CURSOR c_file_exists(cp_drwg_code varchar2) IS
      select 1 
      from wwv_flow_files
      where name=(select d.uri
                  from mip_dev_e.documents d, mip_dev_e.document_roles dr 
                  where dr.drwg_code = cp_drwg_code and d.id = dr.docu_id);   
  begin
    IF NOT c_file_exists%ISOPEN THEN
      OPEN c_file_exists(p_drwg_code);
    END IF;
    FETCH c_file_exists
      INTO l_file_exists;
    CLOSE c_file_exists;
    if l_file_exists is not null and l_file_exists>0 then
      return true;
    end if;
  end drawing_file_exists;
  /*
   FUNCTION update_file_association
   - updates the association between a object within webmip to a file held in the
   - APEX application files view and ophans any existing files.
   %param p_uri - the uri of the file (the contents of the name column in APEX application files)
   %param p_description - description of the file, will be set in documents and document_roles table
   %param p_docu_type - INDO (held within webmip db), EXDO (held outside webmip), FIDO (on file system)
   %param p_qute_id - the quote id if applicable otherwise supply null
   %param p_enqu_id - the enquiry id if applicable otherwise supply null
   %param p_drwg_code - the drawing code if applicable otherwise supply null
  */
  function update_file_association(p_uri in varchar2,
                                p_description in varchar2,
                                p_docu_type in varchar2,
                                p_qute_id in number,
                                p_enqu_id in number,
                                p_drwg_code in varchar2) return boolean is
    l_doc_id number;
    
  begin
      --reference it in the documents table
      insert into documents( uri, description, id, docu_type )
      values (p_uri,
              p_description,
              docu_seq.NEXTVAL,
              p_docu_type)  
      returning id into l_doc_id;
      --set up a role for the document
      update document_roles set docu_id = l_doc_id    
      where drwg_code = p_drwg_code or qute_id = p_qute_id or enqu_id = p_enqu_id;     
      --if it works then return true
      return true;
  end update_file_association;  
  /*
   FUNCTION set_file_association
   - sets the association between a object within webmip to a file held in the
   - APEX application files view.
   %param p_uri - the uri of the file (the contents of the name column in APEX application files)
   %param p_description - description of the file, will be set in documents and document_roles table
   %param p_docu_type - INDO (held within webmip db), EXDO (held outside webmip), FIDO (on file system)
   %param p_rt_code - type of role for docment eg 'ENQUIRY SUPPORTING DOC' see role types table
   %param p_qute_id - the quote id if applicable otherwise supply null
   %param p_enqu_id - the enquiry id if applicable otherwise supply null
   %param p_drwg_code - the drawing code if applicable otherwise supply null
   %param p_doro_type - DRRO (for a drawing), ENDO (for an enquiry), QUDO (for a quote)
  */
  function set_file_association(p_uri in varchar2,
                                p_description in varchar2,
                                p_docu_type in varchar2,
                                p_rt_code in varchar2,
                                p_qute_id in number,
                                p_enqu_id in number,
                                p_drwg_code in varchar2,
                                p_doro_type in varchar2) return boolean is
    l_doc_id number;
    
  begin
      --reference it in the documents table
      insert into documents( uri, description, id, docu_type )
      values (p_uri,
              p_description,
              docu_seq.NEXTVAL,
              p_docu_type)  
      returning id into l_doc_id;
      --set up a role for the document
      insert into document_roles(rt_code, start_date,description,qute_id,enqu_id,drwg_code,id,doro_type,docu_id)    
      values (p_rt_code,
              sysdate,
              p_description,
              p_qute_id,
              p_enqu_id,
              p_drwg_code,
              doro_seq.NEXTVAL,
              p_doro_type,
              l_doc_id);     
      --if it works then return true
      return true;
  end set_file_association;
  /*
   PROCEDURE export_data_to_csv
   - allows an admin user to create a CSV export of a table in webMIP.
   - This function will be used to extract data from the online webMIP system
   - and pull the data into Metering I&C's backend systems.
   %param p_table - the name of the table we're to export
   %param p_column_headers - tables column names be exported as a header
   %param p_delimiter - what is the delimiter value is for each field (default ',')
  */
  PROCEDURE export_table_to_csv(p_table IN VARCHAR2,
                                p_column_headers IN VARCHAR2,
                                p_delimiter IN VARCHAR2 DEFAULT ',') is

    l_select VARCHAR2(2000); --will hold our dynamically created sql query
    l_cursor INTEGER DEFAULT dbms_sql.open_cursor; --a handle for the recordset we can then loop through
    l_status INTEGER;
    l_return VARCHAR2(4000);
    l_found  BOOLEAN := TRUE;
    l_headers VARCHAR(2000); --used to collect the column headers
  BEGIN
     dbms_output.put_line('me is here');
    -- Build the dynamic SQL statement to get the tables column names
    FOR f IN (SELECT column_name
            FROM   user_tab_columns
            WHERE  table_name = upper(p_table)
            ORDER  BY column_id) LOOP
      --
      -- AG's clever delimiting ensures that the rows from the table are output
      -- in a nice CSV format
      l_select := l_select ||'''"''||'|| f.column_name || '||''"'||p_delimiter||'''||';
      --
      -- Get the header row, slightly inefficient in that we may not need to
      -- do it every time
      l_headers := l_headers || f.column_name || p_delimiter;
      --
    END LOOP;
    --
    -- Trim the unnecessary additional delimiting chars on the headers and
    -- add a carriage return
    l_headers := RTRIM(l_headers,p_delimiter) || CHR(13);
    --
    -- build the dynamic SQL that will return all the table rows
    l_select := 'SELECT ' || RTRIM(l_select,'||') || CHR(13)||CHR(10) ||' FROM ' || p_table;
    --
    -- Now we have the select, let's execute it
    --
     dbms_output.put_line(l_select);
    dbms_sql.parse(l_cursor
                  ,l_select
                  ,dbms_sql.native);
    --
    dbms_sql.define_column(l_cursor
                          ,1
                          ,l_return
                          ,4000);
    --
    l_status := dbms_sql.execute(l_cursor); --could be used for further manipulation
    --
    WHILE (dbms_sql.fetch_rows(l_cursor) > 0) LOOP
      --
      IF l_found THEN
        --
        -- Set the header MIME type
        owa_util.mime_header( 'application/octet', FALSE );
        -- Set the name of the file
        htp.p('Content-Disposition: attachment; filename="'||lower(p_table)||'.csv"');
        -- Close the HTTP Header
        owa_util.http_header_close;
        --
        l_found := FALSE;
        --
        -- If the user has requested that the tables column names be exported
        -- then display them
        if upper(p_column_headers) = 'YES' then
          --print column headers
          htp.p(l_headers);
        end if;
        --
      END IF;
      --
      -- Main CSV output
      dbms_sql.column_value(l_cursor
                           ,1
                           ,l_return);
      --
      htp.p(l_return);
      --
    END LOOP;
    --
  END export_table_to_csv;

end MIP_FILES;
/
