set serveroutput on;
set verify off;
clear buffer;
clear columns;

declare
  s2x number(38,5);
  s2y number(38,5);
  s2z number(38,5);
  s2d number(38,5);
  s2s number(38,5);
  ansx number(38,5);
  ansy number(38,5);
  ansz number(38,5);
  ansd number(38,5);
  anss number(38,5);
  w1   number(38,5);
  w2   number(38,5);
  vname varchar2(20);
  x varchar2(20);
  pdtname varchar2(4000);
  filehandle  utl_file.file_type;
  file_pdt_name varchar2(4000);
  file_is_open boolean;
  invalid_path exception;
  invalid_mode exception;
  invalid_filehandle exception;
  invalid_operation exception;
  read_error exception;
  no_data_found exception;
  value_error exception;
  cursor curs1 is
    select id,name,x,y,z,direct,pdt_name from format2 where setup = 1 
      order by pdt_name,name desc,id;
begin
  dbms_output.enable(1000000);
  begin
    filehandle := utl_file.fopen('/usr/tmp','alayer-list','r');
    exception 
     when invalid_path then
      dbms_output.put_line('invalid path');
     when invalid_mode then
      dbms_output.put_line('invalid mode');
     when invalid_operation then
      dbms_output.put_line('invalid operation open');
     when others then
      dbms_output.put_line('cannot open file '); 
   end;
    begin
      file_is_open:=utl_file.is_open(filehandle);
      while file_is_open loop
      begin
        utl_file.get_line(filehandle,pdtname);
  	dbms_output.put_line(pdtname);
        exception
          when invalid_filehandle then
            dbms_output.put_line('invalid filehandle');
          when invalid_operation then
            dbms_output.put_line('invalid operation get_line');
          when read_error then
            dbms_output.put_line('read error');
          when value_error then
            dbms_output.put_line('value error'); 
          when others then
            utl_file.fclose(filehandle);
            file_is_open:=utl_file.is_open(filehandle);
       end;
 

      for curs1mem in curs1 loop

       if curs1mem.x is not null and curs1mem.y is not null and 
          curs1mem.z is not null and 
          upper(curs1mem.pdt_name)=upper(pdtname)
        then
            begin
            select x,y,z,direct into s2x,s2y,s2z,s2d from format2 where 
              id=curs1mem.id and name=curs1mem.name and setup=2
              and upper(pdt_name)=upper(pdtname);

            if s2x is null or s2y is null or s2z is null 
            then
              ansx:=curs1mem.x;
              ansy:=curs1mem.y;
              ansz:=curs1mem.z;
              ansd:=curs1mem.direct;
              anss:=1;
            elsif s2x is not null and s2y is not null and s2z is not null
                    and s2d is not null
            then
              if( s2d=1 or s2d=3) 
               then 
                w2:=1.0;
              elsif s2d=2 
               then 
                w2:=2.0;
              end if;       
              if (curs1mem.direct=1 or curs1mem.direct=3) 
               then 
                w1:=1;
              elsif (curs1mem.direct=2) 
              then 
                w1:=2.0;
              end if;
              ansx:=(curs1mem.x/w1+s2x/w2)/(1/w1+1/w2);
              ansy:=(curs1mem.y/w1+s2y/w2)/(1/w1+1/w2);
              ansz:=(curs1mem.z/w1+s2z/w2)/(1/w1+1/w2);
              ansd:=curs1mem.direct+s2d;
              anss:=12;
           end if;
           end;
       elsif (curs1mem.x is null or curs1mem.y is null or 
                    curs1mem.z is null) and 
                    upper(curs1mem.pdt_name)=upper(pdtname)
        then  
            begin
            select x,y,z,direct into s2x,s2y,s2z,s2d from format2 where 
              id=curs1mem.id and name=curs1mem.name and setup= 2
              and upper(pdtname)=upper(pdt_name);
             ansx:=s2x;
             ansy:=s2y;
             ansz:=s2z;             
             ansd:=s2d;
             anss:=2;
            end;
        end if; 
        if (s2x is not null or curs1mem.x is not null) and
            upper(curs1mem.pdt_name)=upper(pdtname) and  
            (curs1mem.name='REFHOLE' 
            or curs1mem.name='NEWTB')
            then
             if curs1mem.name='NEWTB'
              then
               vname:='NEWTB  ';
             elsif curs1mem.name='REFHOLE'
              then
               vname:='REFHOLE';
             elsif curs1mem.name='TB'
              then
               vname:='TB     ';
             end if;
            dbms_output.put_line(curs1mem.pdt_name||'  '||
            vname||' '||
            to_char(curs1mem.id,'99')||'   '||to_char(ansx,'999.999')
            ||'  '||to_char(ansy,'999.999')||
            '  '||to_char(ansz,'999.999')||'   '||to_char(ansd,'99')
            ||' '||to_char(anss,'99'));
        end if;
       end loop;
   end loop;
   utl_file.fclose(filehandle);
  end;
end;

/