Jsp Upload CSV (02)

本文详细阐述了如何使用特定的脚本和数据库操作来批量导入AP发票数据,包括验证会话、数据处理、数据库插入及调用存储过程等关键步骤。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >




 






  public boolean isDebug =false;
  //public int uploadrst =1;//add 100301 -1 fail 0 mdsexits 1 successfull
 
  public static final String RCS_ID ="$Header: uploadfile.jsp 115.1 2001/10/29 20:50:06 pkm ship   $";
   
  public static final boolean RCS_ID_RECORDED =VersionInfo.recordClassVersion(RCS_ID,"oa_html.jsp.fnd");
  /*
   * Helper function to print out messages.//contentType="text/html; charset=UTF-8"
   */
  void msg ( JspWriter o, String message ) throws IOException
  {
    if (isDebug) o.println(message + "
");
    System.out.println(message + "
");
  }
  /*
   * Helper function to print out exceptions.
   */
  void msgException ( JspWriter o, Exception e ) throws IOException
  {
   if (isDebug) {
     o.println("
");
     e.printStackTrace(new PrintWriter(o));
     o.println("
");
    }
  }
public String[] splitString(String source, char useChar) {
    List list = new ArrayList();
    String sub;
    String[] result;
    if (source.charAt(0) == useChar)
      source ="nodata"+ source;
    if (source.charAt(source.length() - 1) == useChar)
      source = source.substring(0, source.length() - 1);
    int start = 0;
    int end = source.indexOf(useChar);
    while (end > 0) {
      sub = source.substring(start, end);
      list.add(sub);
      start = end + 1;
      end = source.indexOf(useChar, start);
    }
   
    sub = source.substring(start, source.length());
    list.add(sub);
   
    result = new String[list.size()];
   
    Iterator iter = list.iterator();
    int i = 0;
    while (iter.hasNext()) {
      result[i++] = (String) iter.next();
    }
    return result;
  }

  // some column content include comma ,such as "XXX,XX"  ,need to process specially
 
public String[] specialSplitString(String source, char useChar) {
 List list = new ArrayList();
 String[] result;
        int startPos=source.indexOf("\"");   
        int commaPos=source.indexOf(useChar);
        int endPos=source.indexOf("\"",startPos+1);
        String columnData=null;
       while(source.length()>0)
       {
           if(startPos==0&&endPos>1)
          {
            columnData=source.substring(startPos+1,endPos);
            list.add(columnData);
            if(endPos            {
             source=source.substring(endPos+2);
            }
            else
            {
             source="";
            }
          }
          else if(commaPos          {
               columnData=source.substring(0,commaPos);
               list.add(columnData);
               source=source.substring(commaPos+1);
          }
          else if(commaPos!=-1)
          {
              columnData=source.substring(0,commaPos);
              source=source.substring(commaPos+1);
              list.add(columnData);
          }
          else
          {
              columnData=source.substring(0);
              list.add(columnData);
              source="";
          }
           startPos=source.indexOf("\"");   
           commaPos=source.indexOf(useChar);
           endPos=source.indexOf("\"",startPos+1);
       //    System.out.println("startPos: "+startPos+", endPos: "+endPos+", commaPos: "+commaPos);
       //    System.out.println("line is "+line);
       }
      
    //   for(int i=0;i    //   {
     //      System.out.println("list["+i+"]= "+list.get(i));
    //   }            
     result = new String[list.size()];    
     Iterator iter = list.iterator();
     int i = 0;
     while (iter.hasNext()) {
       result[i++] = (String) iter.next();
     }
     return result;
 }
//Date/*
public static class StringOrDate {
 
 public static String dateToString(Date date, String type) {
  String str = null;
  DateFormat format = new SimpleDateFormat("yyyy-MM-dd");
  if (type.equals("SHORT")) {
   // 07-1-18
   format = DateFormat.getDateInstance(DateFormat.SHORT);
   str = format.format(date);
  } else if (type.equals("MEDIUM")) {
   // 2007-1-18
   format = DateFormat.getDateInstance(DateFormat.MEDIUM);
   str = format.format(date);
  } else if (type.equals("FULL")) {
   // 2007年1月18日 星期四
   format = DateFormat.getDateInstance(DateFormat.FULL);
   str = format.format(date);
  }
  return str;
 }
 
 public static Date stringToDate(String str)
 {
  DateFormat format = new SimpleDateFormat("yyyy-MM-dd");
  Date date = null;
  
  try {
   // Fri Feb 24 00:00:00 CST 2012
   date = format.parse(str);
  } catch (ParseException e) {
   e.printStackTrace();
  }
  // 2012-02-24
  date = java.sql.Date.valueOf(str);
           
  return date;
 }
 
 public static void main(String[] args) {
  Date date = new Date();
  System.out.println(StringOrDate.dateToString(date, "MEDIUM"));
  String str = "2012-2-24";
  System.out.println(StringOrDate.stringToDate(str));
 }
}
*/
//Date 
%>

  /*
   * This example uses the WebRequestUtil.validateContext convenience
   * method to take care of handling all the validate session calls
   * for you.
   */
   //Declare some arguments
    int uploadrst =1;//add 100301 -1 fail 0 mdsexits 1 successfull
 int LineNo;
   
 int  v_receipt_line_num= 0;
 int  v_supplier_num = 0;
    int  v_seq_num = 0;
    /*
    Float v_RECEIVED_QTY = 0;
    Float v_PO_PRICE = 0;
    Float v_amount = 0;*/
// float v_uph = 0;
 //float v_workingday = 0;
 /*
 String v_receipt_num = null;
    String v_supplier_site_code = null;
    String v_invoice_num = null;
    String v_custom_num = null;
    */
    String v_TRXDateStr = null;
    String v_InvoiceDateStr =null ;
    String v_GLDateStr =null ;
 
    int sOrgID = 0;
 int sUserID = 0;
 int sBatchNum = 0;
 //Declare some arguments
   
    //*  only fit for oracle web container
  try
  {
    WebAppsContext ctx = null;
    // Call validateContext at the start of each page to make sure the
    // session is still valid.  If it is not, then return an error.
    //       
    ctx = WebRequestUtil.validateContext(request, response);
    if (ctx == null)
    {
      msg(out,"Failed to authenticate session");
      return;
    
 }
 
    //
    // If validation succeeds, first set the client encoding in order
    // to ensure translateability.  After that, get the database
    // connection to use from the WebAppsContext.
    //
    WebRequestUtil.setClientEncoding(response, ctx);
    Connection conn=ctx.getJDBCConnection();
 
 //PreparedStatement statement = null;
    ResultSet rs = null;
 if ( conn==null )
    {
      msg(out,"Failed to get JDBC connection");
      return;
    }
    //
    // Your application-specific code goes here.  In this simple
    // example we just select the user and responsibility from dual.
    //
 
    PreparedStatement ps=null;
    PreparedStatement ps_del=null;
 CallableStatement cs=null;
 
 
    try
    {
       String sql = "select fnd_global.user_name, fnd_global.resp_name, fnd_profile.VALUE ('BG_ORG_ID'), fnd_global.user_id , BG_AP_INVOICES_UPLOAD_S.NEXTVAL " + "from dual";
        ps =conn.prepareStatement(sql);

        rs = ps.executeQuery();
     if ( rs.next() )
        {
       ResourceStore resStore = ctx.getResourceStore();
       String userLabel = resStore.getResourceText("FND", "USER");
      String respLabel = resStore.getResourceText("FND", "RESPONSIBILITY");
         sOrgID=rs.getInt(3);
         sUserID=rs.getInt(4);
   sBatchNum=rs.getInt(5);
   
      msg(out,"Successfully authenticated session!");
      msg(out, "
");
      msg(out, userLabel+ " - " + rs.getString(1));
      msg(out, respLabel+ " - " + rs.getString(2));
      msg(out, "Organization ID - " + sOrgID);
      msg(out, "User ID - " + sUserID);
   msg(out, "Batch Number - " + sBatchNum);
      msg(out, "
");
  } 
  // Main upload routine
   Vector vLineData = new Vector(); // Record all data
  String line;
  String[] inData =null;
  String tempString = null;
  //request.setCharacterEncoding("GB2312");
  BufferedReader in = new BufferedReader(new InputStreamReader(request.getInputStream(),"GBK"));//,"UTF-8"
         Locale locale=null;
         boolean existsData=false; 
    LineNo = 0;
  
  // Close PS
     if(ps!=null)
     {
         try
   {
       ps.close();
         }
     catch(Exception e1)
      {}
     }
  /*//Delete data
   ps_del =conn.prepareStatement("DELETE FROM bg_kpi_hcwh where organization_id = ? and bg_year = ? and bg_week = ? and version = ?");
        ps_del.setInt(1, sOrgID);
        ps_del.setInt(2, v_year);
        ps_del.setInt(3, v_week);
        ps_del.setInt(4, v_version);
  if ( ps_del.executeUpdate() != 1)
        {
            msg(out, "Record Deletion Error!");
        }                            
  // Close ps_del
        if(ps_del!=null)
        {
            try
            {
               ps_del.close();
            }
            catch(Exception e1)
            {}
      }
     */
//
  ps =conn.prepareStatement("INSERT INTO BG_UPLOAD_AP_INVOICE_TEMP(creation_date,created_by, last_update_date, last_updated_by, organization_id, RECEIPT_NUM, RECEIPT_LINE_NUM, TRANSACTION_DATE, SUPPLIER_NUM, SUPPLIER_SITE_CODE, PO_NUMBER, PR_NUMBER, SUB_PAR_NUMBER, ITEM_NUMBER, CUST_ITEM_NUMBER ," + "ACCOUNT_CODE, TRANSACTION_TYPE, RECEIVED_QTY,  UOM_CODE, CURRENCY_CODE, PO_PRICE , AMOUNT, INVOICE_TAX, WAYBILL, SEQ, INVOICE_DATE, INVOICE_NUM , CUSTOM_NUM, PAYMENT_TERM, GL_DATE,row_num,BATCH_NUM,INVOICE_TYPE ) VALUES (SYSDATE,?,SYSDATE,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");             
    //13 columns     
  while ( (line = in.readLine())!=null) 
  {
             LineNo++;
    msg(out, "line - " + LineNo + "-" + line);
    if (LineNo >= 6)
    {
       if ( ((line.trim()).equals("")) == false  )
    {
         inData = splitString(line, ',');
                    for(int p=0;p                        msg(out, "inData["+p+"]="+inData[p]);
     if (inData.length > 1)  // i.e. not blank line or end of file
        {
       /*Delete  data
         if (LineNo == 7)
         {
             ps_del =conn.prepareStatement("DELETE FROM bg_kpi_hcwh where organization_id = ? and bg_year = ? and bg_week = ? and version = ?");
                   ps_del.setInt(1, sOrgID);
                   ps_del.setInt(2, v_year);
                   ps_del.setInt(3, v_week);
                   ps_del.setInt(4, v_version);
       if ( ps_del.executeUpdate() != 1)
                   {
                         msg(out, "Record Deletion Error!");
                       }                            
       // Close ps_del
                         if(ps_del!=null)
                         {
                             try
                       {
                             ps_del.close();
                             }
                             catch(Exception e1)
                          {}
                         }
       
            }
            */
   
 //get the available locale
                    /*
                                     Locale[] availLocale=Locale.getAvailableLocales();
                                          for(int h=0;h                                      {
                                     locale=availLocale[h];
                                    // System.out.println("try locale: "+locale);
                                     DateFormat formatter=new SimpleDateFormat("dd-MMM-yy",locale);
                                   try{
                                   formatter.parse(v_TRXDateStr); 
                                formatter.parse(v_InvoiceDateStr); 
                                formatter.parse(v_GLDateStr); 
                               
                               // System.out.println("Find: "+locale);
                                      }
                                    catch(Exception e)
                                    { }   
                                       }*/
                  
// get the available locale       
 /* SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
    Date b=sdf.parse(birthdate);
    java.sql.Date b2=new java.sql.Date(b.getTime());
    ps.setDate(6,b2);*/
//Validate data null or not //
             DateFormat myDateFormat = new SimpleDateFormat("dd-MMM-yy",Locale.US);
 float fqty=0;
 float price=0;
 float amount=0;
              // Insert Record//
               ps.setInt(1, sUserID);                     
               ps.setInt(2, sUserID);                     
               ps.setInt(3, sOrgID);
                 
       //Receipt number 4
           if (inData[0].trim().equals("") == false)
              { ps.setString(4,inData[0]); }
              else
              { ps.setNull(4, Types.VARCHAR);
               
               }
            
       //Receipt line number 5
           if (inData[1].trim().equals("") == false)
              {  v_receipt_line_num= Integer.parseInt(inData[1]);
                 ps.setInt(5,v_receipt_line_num);
                }
              else
               { ps.setNull(5,  Types.INTEGER);} 
             
              //Transaction date 6
          if (inData[2].trim().equals("") == false)
              {  v_TRXDateStr = inData[2];
                 java.util.Date TRXDate= myDateFormat.parse(v_TRXDateStr);       
           java.sql.Date  TRX_Date=new java.sql.Date(TRXDate.getTime());
           ps.setDate(6, TRX_Date); //TRANSACTION_DATE
                 }
              else
                  {
                   ps.setNull(6, Types.DATE);
            }
           
           //Supplier Num  ?:7 indata[3]
           if (inData[3].trim().equals("") == false)
              {  
                 ps.setString(7,inData[3]);
                }
              else
       { ps.setNull(7,  Types.VARCHAR);} 
       
                       //Supplier site code  ?:8 indata[4]
           if (inData[4].trim().equals("") == false)
              {  ps.setString(8,inData[4]);
                }
              else
       { ps.setNull(8,  Types.VARCHAR);}
                   
                     //PO Number ?:9 indata[5]
           if (inData[5].trim().equals("") == false)
              {  ps.setString(9,inData[5]);
                }
              else
       { ps.setNull(9,  Types.VARCHAR);}
         
         //PR Number ?:10 indata[6]
           if (inData[6].trim().equals("") == false)
              {  ps.setString(10,inData[6]);
                }
              else
       { ps.setNull(10,  Types.VARCHAR);}
         
        //Sub Par Number ?:11 indata[7]
           if (inData[7].trim().equals("") == false)
              {  ps.setString(11,inData[7]);
                }
              else
       { ps.setNull(11,  Types.VARCHAR);}
         
         //Belton P/N ?:12 indata[8]
           if (inData[8].trim().equals("") == false)
              {  ps.setString(12,inData[8]);
                }
              else
       { ps.setNull(12,  Types.VARCHAR);}
         
          //Customer P/N ?:13 indata[9]
           if (inData[9].trim().equals("") == false)
              {  ps.setString(13,inData[9]);
                }
              else
       { ps.setNull(13,  Types.VARCHAR);}
        
          //Account code ?:14 indata[10]
           if (inData[10].trim().equals("") == false)
              {  ps.setString(14,inData[10]);
                }
              else
       { ps.setNull(14,  Types.VARCHAR);}
        
        
         //Transaction type ?:15 indata[11]
           if (inData[11].trim().equals("") == false)
              {  ps.setString(15,inData[11]);
                }
              else
       { ps.setNull(15,  Types.VARCHAR);}
        
          //Received Qty ?:16 indata[12]
           if (inData[12].trim().equals("") == false)
              {
                 //java.lang.Float//
                 fqty = Float.parseFloat(inData[12]);
                 ps.setFloat(16,fqty);
               }
              else
       { ps.setNull(16,Types.FLOAT);}
        
         //UOM Code ?:17 indata[13]
           if (inData[13].trim().equals("") == false)
              {  ps.setString(17,inData[13]);
                }
              else
       { ps.setNull(17,  Types.VARCHAR);}
        
         //Currency Code ?:18 indata[14]
           if (inData[14].trim().equals("") == false)
              {  ps.setString(18,inData[14]);
                }
              else
       { ps.setNull(18,  Types.VARCHAR);}
       
          //PO Price ?:19 indata[15]
           if (inData[15].trim().equals("") == false)
              {
                 price= Float.parseFloat(inData[15]);
                 ps.setFloat(19,price);
               }
              else
       { ps.setNull(19,Types.FLOAT);}
        
            //Amount  ?:20 indata[16]
           if (inData[16].trim().equals("") == false)
              {
                   amount= Float.parseFloat(inData[16]);
                 ps.setFloat(20,amount);
               }
              else
       { ps.setNull(20,Types.FLOAT);}
        
          //Invoice Tax ?:21 indata[17]
           if (inData[17].trim().equals("") == false)
              {  ps.setString(21,inData[17]);
                }
              else
       { ps.setNull(21,  Types.VARCHAR);}
       
         //Waybill ?:22 indata[18]
           if (inData[18].trim().equals("") == false)
              {  ps.setString(22,inData[18]);
                }
              else
       { ps.setNull(22,  Types.VARCHAR);}
       
        //Seq Number ?:23 indata[19]
          if (inData[3].trim().equals("") == false)
             { int seq_num= Integer.parseInt(inData[19]);
                 ps.setInt(23,seq_num); 
                }
              else
       { ps.setNull(23,  Types.INTEGER);} 
       
           //Invoice Date ?:24 indata[20] 
           if (inData[20].trim().equals("") == false)
              {  v_InvoiceDateStr = inData[20];
                 java.util.Date InvoiceDate = myDateFormat.parse(v_InvoiceDateStr);       
           java.sql.Date Invoice_date=new java.sql.Date(InvoiceDate.getTime()); 
           ps.setDate(24, Invoice_date);
               }
              else
                  {
                    ps.setNull(24, Types.DATE);
            }
             //Invoice Number ?:25 indata[21]
           if (inData[21].trim().equals("") == false)
              {  ps.setString(25,inData[21]);
                }
              else
       { ps.setNull(25,  Types.VARCHAR);}
         
          //Custom Number ?:26 indata[22]
           if (inData[22].trim().equals("") == false)
              {  ps.setString(26,inData[22]);
                }
              else
       { ps.setNull(26,  Types.VARCHAR);}
       
            //Payment Term ?:27 indata[23]
           if (inData[23].trim().equals("") == false)
              {  ps.setString(27,inData[23]);
                }
              else
       { ps.setNull(27,  Types.VARCHAR);}
       
             //GL Date ?:28 indata[24] 
            if (inData[24].trim().equals("") == false)
              { v_GLDateStr= inData[24];
                 java.util.Date GLDate= myDateFormat.parse(v_GLDateStr);       
           java.sql.Date  GL_Date=new java.sql.Date(GLDate.getTime());   
           ps.setDate(28, GL_Date);
         }
              else
                  {
                    ps.setNull(28, Types.DATE);
            }
      
             
                 ps.setInt(29,LineNo); 
                 ps.setInt(30,sBatchNum); 
               //Invoice Type ?:31 indata[25] 
              if (inData[25].trim().equals("") == false)
              {  ps.setString(31,inData[25]);
                }
              else
       { ps.setNull(31,  Types.VARCHAR);}
       
            
//Validate data null or not //     
                   
                   if ( ps.executeUpdate() != 1)
                   {
                         msg(out, "Record Insert Error!");
                       }
           
       
                    }
    }
    }
   
  }       
//Call procedure for Import Receords to AP invoice interface  //
     cs =conn.prepareCall("{call APXINVIMP_BG_1(?)}");
  cs.setInt(1, sBatchNum);
 // cs.setInt(2, v_week);
  cs.execute();
        cs.close();
//Call procedure for Import Receords to AP invoice interface //
  // Print out imported records
  out.println("The following records have been imported to the system.
");
  // out.println("Version: " + Integer.toString(v_version));
     out.print("
Receipt NumberReceipt line NumberTransaction DateSupplier NameSupplier NumberSupplier Site CodePO NumberPR NumberSub Par NumberBelton P/NCustomer P/NItem DescriptionAccount CodeTransaction TypeReceived QtyUOMCurrencyPO PriceAmountInvoice TaxWaybillSeqInvoice DateInvoice NumberCustom NumberPayment TermGL DateInvoice TypeStatusMessage
 //Print Data//
  ps =conn.prepareStatement("select m.RECEIPT_NUM,to_char(m.RECEIPT_LINE_NUM) ,to_char(m.TRANSACTION_DATE ,'DD-Mon-YY'), p.vendor_name , m.SUPPLIER_NUM ,m.SUPPLIER_SITE_CODE ,m.PO_NUMBER ,m.PR_NUMBER ,m.SUB_PAR_NUMBER ,m.ITEM_NUMBER, m.CUST_ITEM_NUMBER , m.ACCOUNT_CODE, m.TRANSACTION_TYPE , to_char(m.RECEIVED_QTY) , m.UOM_CODE , m.CURRENCY_CODE ,to_char(m.PO_PRICE) ,to_char(m.AMOUNT) ,m.INVOICE_TAX ,m.WAYBILL, to_char(m.SEQ), to_char(m.INVOICE_DATE,'DD-Mon-YY'), m.INVOICE_NUM , m.CUSTOM_NUM, m.payment_term, to_char(m.GL_DATE ,'DD-Mon-YY'),m.INVOICE_TYPE from "
                                 +" BG_UPLOAD_AP_INVOICE_TEMP m ,po_vendors p " +
          " where m.BATCH_NUM = ? and p.segment1=m.supplier_num "+" order by m.row_num asc " );

  ps.setInt(1, sBatchNum);
   rs = null;
        rs = ps.executeQuery();
     while ( rs.next() )
        {
         
   out.println("  ");
   out.println("    " + rs.getString(1) + "");
   out.println("    " + rs.getString(2) + "");
   out.println("    " + rs.getString(3) + "");
      out.println("    " + rs.getString(4) + "");
   out.println("    " +rs.getString(5) + "");
   out.println("    " + rs.getString(6) + "");
      out.println("    " + rs.getString(7) + "");
      out.println("    " + rs.getString(8) + "");
      out.println("    " + rs.getString(9) + "");
      out.println("    " + rs.getString(10) + "");
      out.println("    " + rs.getString(11) + "");
      out.println("    " + rs.getString(12) + "");
      out.println("    " + rs.getString(13) + "");
      out.println("    " + rs.getString(14) + "");
      out.println("    " + rs.getString(15) + "");
      out.println("    " + rs.getString(16) + "");
      out.println("    " + rs.getString(17) + "");
      out.println("    " + rs.getString(18) + "");
      out.println("    " + rs.getString(19) + "");
      out.println("    " + rs.getString(20) + "");
      out.println("    " + rs.getString(21) + "");
      out.println("    " + rs.getString(22) + "");
      out.println("    " + rs.getString(23) + "");
        out.println("    " + rs.getString(24) + "");
          out.println("    " + rs.getString(25) + "");
              out.println("    " + rs.getString(26) + "");
                  out.println("    " + rs.getString(27) + "");
                    // out.println("    " + rs.getString(28) + "");, m.status , m.message
                      //  out.println("    " + rs.getString(29) + "");
 
  // out.println("    " + rs.getFloat(10) + "");
   out.println("  ");
  } 
  out.println("
");
//Print Data// 
 }
    catch ( SQLException sqle )
    {
      msgException(out, sqle);
      out.println(sqle); 
    }
    finally
    {   // Close PS
   if(ps!=null)
   {
     try{
       ps.close();
        }
     catch(Exception e1)
      {}
    }
    if ( ctx != null ) ctx.freeWebAppsContext();
/*
if (conn != null)
    conn.close();
 */
    }

  }
  catch ( Exception e )
  {
     out.println("File uploaded Failed!  Please check the input data source.");
     out.println(e);
  }
%>

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9182041/viewspace-708075/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/9182041/viewspace-708075/

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值