1 Reply Latest reply: Jul 29, 2012 8:50 AM by Andreas Weiden RSS

    extract data from database tables and download in pdf and csv

    Tshifhiwa
      extract data from database tables and download in pdf and csv
      hi how can i re-write my old form procedure in adf java. the procedure used to extract data from diffirent table and dowload the data in pdf and csv.am not downloading image, i what to extract data from diffirent tables in my database and download that data in pdf and csv. i would like to write this in java adf.i just what direction am not asking anyone to do my work this is my learning curve
      the form code is
      function merge_header3 return varchar2 is 
      begin 
           return '~FACILITY DESCRIPTION~ACCOUNT NO~BRANCH CODE~BANK REF NO.~P/P/ AMOUNT~Postal Address 1~Postal Address 2~Box Postal Code~Dep. Date~Month~BANK NAME~BRANCH NAME~ACCOUNT TYPE~DESCRIPTION~OBJECTIVE DESCRIPTION';
      end;
      ----
      procedure download_file (i_pbat integer) is 
        dir varchar2(80);
        file_name1 varchar2(80);
        file_name2 varchar2(80);
        appl_code varchar2(80);
        fil1 client_text_io.file_type;
        fil2 client_text_io.file_type;
        dat varchar2(1000);
        
        DATA VARCHAR2(1000);
        
        bvspro varchar2(100);
        ssch   varchar2(100);
       
        bvspro_total number(20,2);
        ssch_total   number(20,2);
        grand_total  number(20,2);
          
        cnt    integer;
        
        
        cursor pbat is 
             select *
             from sms_payment_batches
             where id = i_pbat
             ;
        cursor pay  (pb_id integer) is
             select *
             from sms_payment_vw
             where pbat_id = pb_id
             order by subsidy ASC,programme,beneficiary_name
          ;
        cursor cgref (low varchar2) is
             select *
             from cg_ref_codes
             where rv_domain ='SMS'
             and rv_low_value = low
             ; 
        success boolean;      
        begin   
             set_application_property(cursor_style,'busy');
             appl_code := sms_global.ref_code('SMS','APP_CODE','SMS',0);
          dir       := sms_global.ref_code('SMS','PAY_DIR','c:\sms\batch_payments',0);
               success := webutil_file.create_directory(dir);
      
           if webutil_file.file_is_directory(dir) then 
               null;
      --         message ('directory exists');
          else 
      --                  message ('create directory ');
               success := webutil_file.create_directory(dir);
      --         if success then        message ('directory exists');    end if;
          end if;     
      
          for c_pbat in pbat loop
      
               file_name1 := dir ||'\' || appl_code||c_pbat.batch_number||'-'||to_char(c_pbat.batch_dt,'yyyymmdd')||'pay.txt';
               file_name2 := dir ||'\' || appl_code||c_pbat.batch_number||'-'||to_char(c_pbat.batch_dt,'yyyymmdd')||'merge.txt';
               
      --message('create files ');
      --         fil1  := client_text_io.fopen (file_name1,'W'); 
      --         fil2  := client_text_io.fopen (file_name2,'W'); 
          fil1  := client_text_io.fopen (file_name1,'W',''); 
          fil2  := client_text_io.fopen (file_name2,'W',''); 
      
                     dat :=                       'FROM ACCOUNT NUMBER'
                                                                  ||'~'||'FROM ACCOUNT DESCRIPTION'
                                                                  ||'~'||'MY STATEMENT DESCRIPTION'
                                                                  ||'~'||'BENEFICIARY ACCOUNT NUMBER'
                                                                  ||'~'||'BENEFICIARY SUB ACCOUNT NUMBER'         
                                                                  ||'~'||'BENEFICIARY BRANCH CODE'
                                                                  ||'~'||'BENEFICIARY NAME'
                                                                  ||'~'||'BENEFICIARY STATEMENT DESCRIPTION'
                                                                  ||'~'||'AMOUNT'; 
               --     client_text_io.put_line(fil1,dat);
      
               bvspro:= null;
               ssch  := null;
               cnt := 0;     
               dat := '~'||lpad('~',16,'~');
               for c_pay in pay(c_pbat.id) loop 
      --message('cpay loop ' || cnt);              
                 if bvspro is null then 
                       dat := lpad('~',16,'~');
                       dat := utility.put_field(1,c_pay.programme,dat,'~');     
                 client_text_io.put_line(fil2,dat);
                 dat := utility.put_field(1,c_pay.subsidy,dat,'~');
                 client_text_io.put_line(fil2,dat);
                 dat := merge_header3;
                       client_text_io.put_line(fil2,dat);
                       bvspro := c_pay.programme;
                       ssch := c_pay.subsidy;
                       grand_total := 0;
                       bvspro_total := 0;
                       ssch_total := 0;
                 end if;
                 if bvspro <> c_pay.programme then 
                       dat := lpad('~',16,'~');
                       dat := utility.put_field(5,ssch_total,dat,'~');
                       dat := lpad('~',16,'~');
                       dat := utility.put_field(5,bvspro_total,dat,'~');
                 dat := utility.put_field(1,'Total:' || bvspro,dat,'~');
                       client_text_io.put_line(fil2,dat);
                       dat := lpad('~',16,'~');
                 client_text_io.put_line(fil2,dat);
                       dat := utility.put_field(1,c_pay.programme,dat,'~');     
                 client_text_io.put_line(fil2,dat);
                       bvspro := c_pay.programme;
                 dat := utility.put_field(1,c_pay.subsidy,dat,'~');
                 client_text_io.put_line(fil2,dat);
                 dat := merge_header3;
                       client_text_io.put_line(fil2,dat);
                       bvspro := c_pay.programme;
                       ssch := c_pay.subsidy;
                       bvspro_total := 0;
                       ssch_total := 0;
                       cnt :=0;
               end if;                            
                 if ssch <> c_pay.subsidy then 
                       dat := lpad('~',16,'~');
                       dat := utility.put_field(5,ssch_total,dat,'~');
                       dat := lpad('~',16,'~');
                 client_text_io.put_line(fil2,dat);
                 dat := utility.put_field(1,c_pay.subsidy,dat,'~');
                 client_text_io.put_line(fil2,dat);
                 dat := merge_header3;
                       client_text_io.put_line(fil2,dat);
                       ssch := c_pay.subsidy;
                       ssch_total := 0;
                       cnt :=0;
               end if;                            
      
              bvspro_total := bvspro_total + c_pay.amount;
              ssch_total   := ssch_total   + c_pay.amount;              
                     grand_total  := grand_total  + c_pay.amount;              
              cnt := cnt +1;
      
      --message('bfore write file 2 ' );              
              
              client_text_io.put_line(fil2
                                     ,cnt
                              ||'~'|| c_pay.beneficiary_name
                                                                  ||'~'||c_pay.BENEFICIARY_ACCOUNT_NUMBER ||''             
                                                                  ||'~'||c_pay.BRANCH_CODE             ||''            
                                                                  ||'~'|| c_pay.BENEFICIARY_STATEMENT_DESC             
                                                                  ||'~'|| c_pay.AMOUNT                                 
                              ||'~'|| c_pay.address_line1
                              ||'~'|| c_pay.address_line2
                                                      ||'~'|| c_pay.postal_code
                                                      ||'~'|| TO_CHAR(c_pay.deposit_date,'DD-Mon-YYYY')
                                                      ||'~'|| c_pay.month
                                                      ||'~'|| c_pay.bank
                                                      ||'~'|| c_pay.bank_branch
                                                      ||'~'|| c_pay.account_type
                                                      ||'~'|| c_pay.subsidy
                                                      ||'~'|| c_pay.programme)
                                                      ;
                    DATA :=                                  c_pay.FROM_ACCOUNT_NUMBER                    
                                                                  ||'~'||c_pay.FROM_ACCOUNT_DESCR                     
                                                                  ||'~'||c_pay.MY_STATEMENT_DESCR                     
                                                                  ||'~'||c_pay.BENEFICIARY_ACCOUNT_NUMBER 
                                                                  ||'~'
                                                                  ||'~'||c_pay.BRANCH_CODE             
                                                                  ||'~'||c_pay.BENEFICIARY_NAME                       
                                                                  ||'~'||c_pay.BENEFICIARY_STATEMENT_DESC             
                                                                  ||'~'||c_pay.AMOUNT;                                 
                                                                  
              DATA := REPLACE(DATA, ',' , ' ' );
              DATA := REPLACE(DATA, '~' , ',' );
      --message (cnt ||' ' || data);        
      --message('bfore write file 1 ' );              
                    client_text_io.put_line(fil1, data);
      
               end loop;
               
      --message ('end of write');         
                   dat := lpad('~',16,'~');
                    dat := utility.put_field(6,ssch_total,dat,'~');
                    dat := lpad('~',16,'~');
             dat := utility.put_field(1,'Total:' || bvspro,dat,'~');
                   dat := utility.put_field(5,bvspro_total,dat,'~');
                client_text_io.put_line(fil2,dat);
                dat := lpad('~',16,'~');
             client_text_io.put_line(fil2,dat);
             dat := utility.put_field(1,'Grand Total:' ,dat,'~');
                   dat := utility.put_field(5,grand_total,dat,'~');
                client_text_io.put_line(fil2,dat);
      
               -- close file
      /*
      for i in 1..50 loop   
             if substr(i,-1) = 0 then 
                   message ('flush ' || i);
             end if;                 
                    client_text_io.put_line(fil1, lpad(' ',2000));
                    client_text_io.put_line(fil2, lpad(' ',2000));
                    client_text_io.put_line(fil1, lpad(' ',2000));
                    client_text_io.put_line(fil2, lpad(' ',2000));
      end loop;
      */
               client_text_io.fclose(fil1);
               client_text_io.fclose(fil2);
          end loop;
      
         set_application_property(cursor_style,'default');
          exception 
               when others then 
                    message(sqlcode ||' ' ||sqlerrm);
         end download_file;    
      i try this but this code onlydownload image not data from database tables
          public void downloadImage(FacesContext facesContext, OutputStream outputStream)
          {
              BindingContainer bindings = BindingContext.getCurrent().getCurrentBindingsEntry();
      
              // get an ADF attributevalue from the ADF page definitions
              AttributeBinding attr = (AttributeBinding) bindings.getControlBinding("DocumentImage");
              if (attr == null)
              {
                  return;
              }
      
              // the value is a BlobDomain data type
              BlobDomain blob = (BlobDomain) attr.getInputValue();
      
              try
              {   // copy the data from the BlobDomain to the output stream 
                  IOUtils.copy(blob.getInputStream(), outputStream);
                  // cloase the blob to release the recources
                  blob.closeInputStream();
                  // flush the output stream
                  outputStream.flush();
              }
              catch (IOException e)
              {
                  // handle errors
                  e.printStackTrace();
                  FacesMessage msg = new FacesMessage(FacesMessage.SEVERITY_ERROR, e.getMessage(), "");
                  FacesContext.getCurrentInstance().addMessage(null, msg);
              }