pck_apex_demo AUTH

create or replace package pck_apex_demo as

	--
	procedure print_html_region;

	--
	function user_is_admin(p_username in varchar2)
		return boolean;

	--
	function get_broj_racuna return varchar2;

  --
	procedure parse_csv(p_name in apex_application_temp_files.name%type);

	--
	function custom_app_auth (
		p_username in varchar2
	 ,p_password in varchar2
	) return boolean;

end pck_apex_demo;
/





create or replace package body pck_apex_demo as

	--
	procedure print_html_region is
	begin
		htp.p('<p>Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.</p>');
	end print_html_region;

	--
	function user_is_admin(p_username in varchar2)
		return boolean is
	begin
		if p_username = 'USER_ADMIN' then
			return true;
		else
			return false;
		end if;
	end user_is_admin;
	--
	function get_broj_racuna return varchar2 is
	  v_count pls_integer;
	begin
	  select count(*)
		  into v_count
			from racun;

		return lpad(v_count+1, 5, '0')||'-'||to_char(sysdate, 'rrrr');
	end get_broj_racuna;

	--
	procedure parse_csv(p_name in apex_application_temp_files.name%type) is
    v_blob_data  apex_application_temp_files.blob_content%type;
    v_wwv_id     wwv_flow_files.id%type;
    v_blob_len   number;
    v_position   number;
    v_raw_chunk  raw(10000);
    v_char       varchar2(50);
    c_chunk_len  number := 1;
    v_line       varchar2(32767) := null;
    v_data_array wwv_flow_global.vc_arr2;
    v_sr_no      number := 1;
    v_string     varchar2(2000);
    v_index      number;
    v_exit       number;
    ex_no_csv exception;
    ex_wrong_param exception;
    v_rownum         number;
    v_datum          date;
    v_interna_oznaka number(10);
    v_br_sati_rada   number(3);
    v_number15_2     number(15, 2);
    v_odaa_faktor    varchar2(2000);
    v_data_type_num  number(1);
    v_param_errm     varchar2(4000);


  -------------------------------------------------------------------------------------------
  -- uklanja novi redak, tab, radi trim
  function f_remove_special_chars(p_in varchar2) return varchar2 is
    v_ret varchar2(30000);
  begin
    v_ret := replace(trim(replace(replace(replace(p_in, chr(10), ' '), chr(13), ' ')
                                 ,chr(9)
                                 ,' '))
                    ,'.'
                    ,'');
    return v_ret;
  end;

  -----------------------------------------------------------------------------------
  -- funkcija konvertira hex vrijednosti u decimalne
  function hex_to_decimal(p_hex_str in varchar2) return number is
    v_dec number;
    v_hex varchar2(16) := '0123456789ABCDEF';
  begin
    v_dec := 0;
    for indx in 1 .. length(p_hex_str)
    loop
      v_dec := v_dec * 16 + instr(v_hex, upper(substr(p_hex_str, indx, 1))) - 1;
    end loop;
    return v_dec;
  end hex_to_decimal;
  ---------------------------------------------------------------------------
  -- funkcija konvertira decimalnu vrijednost column indexa u ascii vrijednost
  function convert_colnum_to_letter(p_colnum number) return varchar2 is
  begin
    if p_colnum > 26 then
      return CHR(trunc((p_colnum - 1) / 26) + 64) || CHR(((p_colnum - 1) mod 26) + 65);
    else
      return CHR(p_colnum + 64);
    end if;
  end convert_colnum_to_letter;


  begin
    if p_name is null then
      raise ex_no_csv;
    end if;
    delete from tmp_import;

    delete from apex_application_temp_files f where upper(f.name) != upper(p_name);

    -- Read data from wwv_flow_files
    select f.blob_content, f.id
      into v_blob_data, v_wwv_id
      from apex_application_temp_files f
     where upper(f.name) = upper(p_name);

    v_blob_len := dbms_lob.getlength(v_blob_data);
    v_position := 1;
    -- Read and convert binary to char
    while (v_position <= v_blob_len)
    loop
      v_raw_chunk := dbms_lob.substr(v_blob_data, c_chunk_len, v_position);
      v_char      := chr(hex_to_decimal(rawtohex(v_raw_chunk)));
      v_line      := v_line || v_char;
      v_position  := v_position + c_chunk_len;
      v_index     := 1;
      -- When a whole line is retrieved
      if v_char = chr(10) or  v_position > v_blob_len then
        -- Convert each column separated by ; into array of data
        for i in (select substr(str
                               ,instr(str, ';', 1, level) + 1
                               ,instr(str, ';', 1, level + 1) -
                                instr(str, ';', 1, level) - 1) element
                    from (select ';' || v_line || ';' str from dual)
                  connect by level <= length(str) - length(replace(str, ';')) - 1)

        loop
          if v_index = 2 and i.element is null then
            -- ako je kolona sa siframa prazna
            v_exit := 1;
          end if;
          exit when v_exit = 1;
          v_exit := 0;
          v_data_array(v_index) := i.element;
          v_index := v_index + 1;

        end loop;
        if v_exit = 0 then
          v_string := 'insert into tmp_import  (id, ';
          for i in 1 .. v_data_array.count
          loop
            v_string := v_string || 'COL' || i || ',';
          end loop;

          v_string := substr(v_string, 1, length(v_string) - 1) ||
                      ') values (zaposlenik_seq.nextval,';

          for j in 1 .. v_data_array.count
          loop
            v_string := v_string || chr(39) || f_remove_special_chars(v_data_array(j)) || chr(39) || ',';
          end loop;
          v_string := substr(v_string, 1, length(v_string) - 1) || ')';
          execute immediate (v_string);
        end if;

        v_line  := null;
        v_sr_no := v_sr_no + 1;
      end if;
    end loop;

  end parse_csv;

	--
	function custom_app_auth (
		p_username in varchar2
	 ,p_password in varchar2
	) return boolean is
		v_ret   boolean := false;
		v_count pls_integer := 0;
	begin
		begin
			if p_username is not null and p_password is not null then
				select count(*)
					into v_count
					from my_app_users
				 where upper(username) = p_username
					 and password = p_password;
			end if;

			v_ret := v_count > 0;

		exception
			when others then
			  v_ret := false;
		end;

		return v_ret;

	end custom_app_auth;

end pck_apex_demo;
/