Jsp Upload CSV (02)

 






  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 Number

Receipt line Number

Transaction Date

Supplier Name

Supplier Number

Supplier Site Code

PO Number

PR Number

Sub Par Number

Belton P/N

Customer P/N

Item Description

Account Code

Transaction Type

Received Qty

UOM

Currency

PO Price

Amount

Invoice Tax

Waybill

Seq

Invoice Date

Invoice Number

Custom Number

Payment Term

GL Date

Invoice Type

Status

Message

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