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 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;
/
