}else{
          insertSql += "?" + ",";
        }
      }
      //創(chuàng)建帶參數(shù)的SQL語句執(zhí)行類
      PreparedStatement pstmt = conn.prepareStatement(insertSql);
      //創(chuàng)建日期轉(zhuǎn)換類
      java.text.DateFormat dateLongFormat = java.text.DateFormat.getDateTimeInstance();
      java.text.DateFormat dateShortFormat = java.text.DateFormat.getDateInstance();
      //聲明java.sql類包的時間變量
      Timestamp timeStamp = null;
      //將數(shù)組寫入數(shù)據(jù)表
      for(int row = 0; row < data.length; row++){
        for (int col = 0; col < colCount; col++) {
          //設(shè)置字符串參數(shù)
          if (resultSetMetaData.getColumnTypeName(col + 1).equals("varchar") |
              resultSetMetaData.getColumnTypeName(col + 1).equals("longvarchar")) {
            pstmt.setString(col+1, data[row][col]);
          }
          //設(shè)置bit類型參數(shù)
          else if(resultSetMetaData.getColumnTypeName(col + 1).equals("bit")){
            pstmt.setInt(col+1, Integer.parseInt(data[row][col]));
          }
          //設(shè)置int類型參數(shù)
          else if(resultSetMetaData.getColumnTypeName(col + 1).equals("int")){
            pstmt.setInt(col+1, Integer.parseInt(data[row][col]));
          }
          //設(shè)置float類型參數(shù)
          else if(resultSetMetaData.getColumnTypeName(col + 1).equals("float") |
                  resultSetMetaData.getColumnTypeName(col + 1).equals("decimal")){
            pstmt.setDouble(col+1, Double.parseDouble(data[row][col]));
          }
          //設(shè)置timestamp類型參數(shù)
          else if(resultSetMetaData.getColumnTypeName(col + 1).equals("timestamp")){
            if(data[row][col].equals("null")){
              timeStamp = null;
            }else if(data[row][col].length() > 10){
              timeStamp = new Timestamp(dateLongFormat.parse(data[row][col]).getTime());
            }else{
              timeStamp = new Timestamp(dateShortFormat.parse(data[row][col]).getTime());
            }
            pstmt.setTimestamp(col+1, timeStamp);
          }
        }
        //執(zhí)行插入操作
        pstmt.execute();
      }
      //提交事條
      conn.commit();
      result = 1;
    }catch(Exception ex){
      try{
        //撤消事務(wù)
        conn.rollback();
      }catch(Exception ex1){
        ex1.printStackTrace();
      }
      data = new String[0][0];
      ex.printStackTrace();
    }
    //清空數(shù)據(jù)庫聯(lián)接
    cleanup(conn);
    return result;
  }
  //取得賬套名字的方法
  public String[] getLedgerNames() {
    String[] ledgerNames = new String[0];
    Vector vector = new Vector();
    String[] tableNames = getTableNames();
    for(int i = 0; i < tableNames.length; i++){
      if(tableNames[i].indexOf("stockLedger") > -1){
        if(tableNames[i].length() > 11){
          //取得數(shù)據(jù)表的日期字符串
          vector.addElement(tableNames[i].substring(11, tableNames[i].length()));
        }
      }
    }
    //將vector集合的數(shù)據(jù)放入數(shù)組
    ledgerNames = new String[vector.size()];
    for(int i = 0; i < vector.size(); i++){
      ledgerNames[i] = (String)vector.get(i);
    }
    return ledgerNames;
  }
  //創(chuàng)建8個空賬套的方法
  private void createEmptyLedger(Statement stmt, String ledgerDate) throws Exception {
    //創(chuàng)建SQL語句數(shù)組
    String[] createSqls = new String[8];
    createSqls[0] = "CREATE TABLE accountEntryLedger" + ledgerDate
      + " (serialId int NOT NULL PRIMARY KEY,"
      + "linkId nvarchar(20) NOT NULL ,"
      + "filler nvarchar(50) NOT NULL ,"
      + "auditUser nvarchar(50) NOT NULL ,"
      + "fillDate datetime NOT NULL ,"
      + "auditDate datetime NULL ,"
      + "onProcess int NOT NULL ,"
      + "remark ntext NOT NULL)";
    createSqls[1] = "CREATE TABLE accountEntrySubLedger" + ledgerDate
      + " (serialId int NOT NULL PRIMARY KEY,"
      + "linkSerialId int NOT NULL ,"
      + "debitCredit int NOT NULL ,"
      + "accountName nvarchar(100) NOT NULL,"
      + "amount numeric(18, 2) NOT NULL)";
    createSqls[2] = "CREATE TABLE cashLedger" + ledgerDate
      + " (serialId int NOT NULL PRIMARY KEY,"
      + "linkId nvarchar(20) NOT NULL ,"
      + "debitCredit int NOT NULL ,"
      + "filler nvarchar(50) NOT NULL ,"
      + "amount numeric(18, 2) NOT NULL ,"
      + "fillDate datetime NOT NULL)";
    createSqls[3] = "CREATE TABLE currentAccountLedger" + ledgerDate
      + " (currentAccountId nvarchar(20) NOT NULL PRIMARY KEY,"
      + "linkId nvarchar(20) NOT NULL ,"
      + "documentType int NOT NULL ,"
      + "amount numeric(18, 2) NOT NULL ,"
      + "receiverName nvarchar (50)  NOT NULL ,"
      + "documentFiller nvarchar (50)  NOT NULL ,"
      + "cashUser nvarchar (50)  NOT NULL ,"
      + "fillDate datetime NOT NULL ,"
      + "payDate datetime NULL ,"
      + "onProcess int NOT NULL,"
      + "remark ntext  NOT NULL)";
    createSqls[4] = "CREATE TABLE saleLedger" + ledgerDate
      + " (saleId nvarchar (20) NOT NULL PRIMARY KEY,"
      + "saleType int NOT NULL ,"
      + "customerName nvarchar(50) NOT NULL ,"
      + "counterUser nvarchar(50) NOT NULL ,"
      + "creditUser nvarchar(50) NOT NULL ,"
      + "cashUser nvarchar(50) NOT NULL ,"
      + "address nvarchar(100) NOT NULL ,"
      + "fillerDate datetime NOT NULL ,"
      + "deliveryDate datetime NULL ,"
      + "onProcess int NOT NULL ,"
      + "remark ntext  NOT NULL)";
    createSqls[5] = "CREATE TABLE saleSubLedger" + ledgerDate
      + " (serialId int NOT NULL PRIMARY KEY,"
      + "saleId nvarchar(20) NOT NULL ,"
      + "goodsBarCode nvarchar(20) NOT NULL ,"
      + "actualPrice numeric(18, 2) NOT NULL ,"
      + "quantity int NOT NULL)";
    createSqls[6] = "CREATE TABLE stockLedger" + ledgerDate
      + " (orderId nvarchar(20) NOT NULL PRIMARY KEY,"
      + "orderType int NOT NULL ,"
      + "supplierName nvarchar(50) NOT NULL ,"
      + "submitUser nvarchar(50) NOT NULL ,"
      + "commitUser nvarchar(50) NOT NULL ,"
      + "checkUser nvarchar(50) NOT NULL ,"
      + "cashUser nvarchar(50) NOT NULL ,"
      + "address nvarchar(100) NOT NULL ,"
      + "warehouse nvarchar(20) NOT NULL ,"
      + "orderDate datetime NULL ,"
      + "stockDate datetime NULL ,"
      + "onProcess int NOT NULL ,"
      + "remark ntext  NOT NULL)";
    createSqls[7] = "CREATE TABLE stockSubLedger" + ledgerDate
      + " (serialId int NOT NULL PRIMARY KEY,"
      + "orderId nvarchar(20) NOT NULL ,"
      + "goodsBarCode nvarchar(20) NOT NULL ,"
      + "costPrice numeric(18, 2) NOT NULL ,"
      + "quantity int NOT NULL ,"
      + "usefulLife datetime NOT NULL)";
    //創(chuàng)建8個賬套數(shù)據(jù)表
    for(int i = 0; i < createSqls.length; i++){
      stmt.executeUpdate(createSqls[i]);
    }
  }
  //創(chuàng)建賬套的方法
  public int createLedger(String ledgerDate) {
    int result = 0;
    //取得數(shù)據(jù)庫聯(lián)接
    Connection conn = getConnection();
    try{
      //取得賬套數(shù)組
      String[] ledgerNames = getLedgerNames();
      //開始事條
      conn.setAutoCommit(false);
      //創(chuàng)建不帶參數(shù)的SQL語句執(zhí)行類
      Statement stmt = conn.createStatement();
      //如果數(shù)據(jù)庫沒有賬套，直接創(chuàng)建8個空賬套數(shù)據(jù)表便可
      if (ledgerNames.length == 0) {
        //創(chuàng)建8個空賬套
        createEmptyLedger(stmt, ledgerDate);
        conn.commit();
        return 1;
      }
      //為字符串?dāng)?shù)據(jù)排序
      Arrays.sort(ledgerNames);
      //取得上一個時間字符串
      String lastLedgerDate = ledgerNames[ledgerNames.length - 1];
      //如果新日期不大于舊日期，不能進(jìn)行創(chuàng)建操作
      if (ledgerDate.compareTo(lastLedgerDate) <= 0) {
        return 0;
      }
      //創(chuàng)建8個空賬套
      createEmptyLedger(stmt, ledgerDate);
/*取得stockLedger數(shù)據(jù)表和stockSubLedger數(shù)據(jù)表的上期數(shù)據(jù)*/
      String sql = "select distinct(a.warehouse) "
              + "from stockLedger" + lastLedgerDate
              + " as a , stockSubLedger" + lastLedgerDate
              + " as b where a.orderId = b.orderId and a.onProcess = 2";
     //創(chuàng)建取得stockLedger數(shù)據(jù)表的倉庫的SQL語句
      ResultSet rs = stmt.executeQuery(sql);
      //創(chuàng)建倉庫集合
      Vector warehouseVector = new Vector();
      String tempWarehouse;
      while(rs.next()){
        tempWarehouse = rs.getString(1).trim();
        if(tempWarehouse.length() > 0){
          warehouseVector.addElement(tempWarehouse);
        }
      }
      //創(chuàng)建上期庫存單據(jù)數(shù)組
      String[] orderIds = new String[warehouseVector.size()];
      for(int i = 0; i < orderIds.length; i++){
        orderIds[i] = "上" + lastLedgerDate + dataMethod.changeSerial(i + 1);
      }
      //創(chuàng)建插入的SQL語句
      String insertSql = "insert into stockLedger" + ledgerDate +
          " values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
      //創(chuàng)建帶參數(shù)的插入SQL語句執(zhí)行類
      PreparedStatement insertPstmt = conn.prepareStatement(insertSql);
      //為庫存賬套數(shù)據(jù)表加入上期數(shù)據(jù)記錄
      for(int i = 0; i < orderIds.length; i++){
        insertPstmt.setString(1, orderIds[i]);
        insertPstmt.setInt(2, 8);
        insertPstmt.setString(3, "");
        insertPstmt.setString(4, "");
        insertPstmt.setString(5, "");
        insertPstmt.setString(6, "");
        insertPstmt.setString(7, "");
        insertPstmt.setString(8, "");
        insertPstmt.setString(9, (String)warehouseVector.get(i));
        insertPstmt.setTimestamp(10, null);
        insertPstmt.setTimestamp(11, null);
        insertPstmt.setInt(12, 2);
        insertPstmt.setString(13, "");
        insertPstmt.executeUpdate();
      }
      //創(chuàng)建取得stockLedger數(shù)據(jù)表的不同倉庫的產(chǎn)品總數(shù)的SQL語句
      sql = "select b.goodsBarCode, b.costprice, sum(b.quantity) as quantity, "
          + " b.usefullife from stockLedger" + lastLedgerDate
          + " as a , stockSubLedger" + lastLedgerDate + " as b "
          + "where a.orderId = b.orderId and a.onProcess = 2 and warehouse = ? "
          + "group by goodsBarCode, b.costprice, b.usefulLife "
          + "order by b.goodsBarCode, b.usefullife";
      //創(chuàng)建插入stockSubLedger數(shù)據(jù)表的SQL語句
      insertSql = "insert into stockSubLedger" + ledgerDate + " values(?, ?, ?, ?, ?, ?)";
      //創(chuàng)建帶參數(shù)的選擇SQL語句執(zhí)行類
      PreparedStatement selectPstmt = conn.prepareStatement(sql);
      insertPstmt = conn.prepareStatement(insertSql);
      //創(chuàng)建stockSubLedger數(shù)據(jù)表的序號
      int serialId = 0;
      for(int i = 0; i < warehouseVector.size(); i++){
        //設(shè)置SQL語句的倉庫變量
        selectPstmt.setString(1, (String)warehouseVector.get(i));
        //取得倉庫的產(chǎn)品數(shù)據(jù)
        rs = selectPstmt.executeQuery();
        while(rs.next()){
          serialId++;
          insertPstmt.setInt(1, serialId);
          insertPstmt.setString(2, orderIds[i]);
          insertPstmt.setString(3, rs.getString("goodsBarCode"));
          insertPstmt.setDouble(4, rs.getDouble("costPrice"));
          insertPstmt.setInt(5, rs.getInt("quantity"));
          insertPstmt.setTimestamp(6, rs.getTimestamp("usefulLife"));
          insertPstmt.executeUpdate();
        }
      }
      //創(chuàng)建取得上期stockLedger數(shù)據(jù)表未完成單據(jù)的記錄
      sql = "select * from stockLedger" + lastLedgerDate + " where onProcess = 0";
      rs = stmt.executeQuery(sql);
      //創(chuàng)建插入的SQL語句
      insertSql = "insert into stockLedger" + ledgerDate +
          " values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
      insertPstmt = conn.prepareStatement(insertSql);
      //創(chuàng)建單據(jù)標(biāo)識集合類
      Vector orderIdVector = new Vector();
      String tempOrderId = "";
      while(rs.next()){
        tempOrderId = rs.getString("orderId");
        orderIdVector.addElement(tempOrderId);
        insertPstmt.setString(1, tempOrderId);
        insertPstmt.setInt(2, rs.getInt("orderType"));
        insertPstmt.setString(3, rs.getString("supplierName"));
        insertPstmt.setString(4, rs.getString("submitUser"));
        insertPstmt.setString(5, rs.getString("commitUser"));
        insertPstmt.setString(6, rs.getString("checkUser"));
        insertPstmt.setString(7, rs.getString("cashUser"));
        insertPstmt.setString(8, rs.getString("address"));
        insertPstmt.setString(9, rs.getString("warehouse"));
        insertPstmt.setTimestamp(10, rs.getTimestamp("orderDate"));
        insertPstmt.setTimestamp(11, rs.getTimestamp("stockDate"));
        insertPstmt.setInt(12, rs.getInt("onProcess"));
        insertPstmt.setString(13, rs.getString("remark"));
        insertPstmt.executeUpdate();
      }
      //創(chuàng)建取得上期stockSubLedger數(shù)據(jù)表未完成單據(jù)的記錄
      sql = "select * from stockSubLedger" + lastLedgerDate + " where orderId = ?";
      //創(chuàng)建插入stockSubLedger數(shù)據(jù)表的SQL語句
      insertSql = "insert into stockSubLedger" + ledgerDate + " values(?, ?, ?, ?, ?, ?)";
      selectPstmt = conn.prepareStatement(sql);
      insertPstmt = conn.prepareStatement(insertSql);
      for(int i = 0; i < orderIdVector.size(); i++){
        tempOrderId = (String)orderIdVector.get(i);
        //設(shè)置SQL語句的單據(jù)變量
        selectPstmt.setString(1, tempOrderId);
        //根據(jù)單據(jù)標(biāo)識取得產(chǎn)品數(shù)據(jù)
        rs = selectPstmt.executeQuery();
        while(rs.next()){
          serialId++;
          insertPstmt.setInt(1, serialId);
          insertPstmt.setString(2, tempOrderId);
          insertPstmt.setString(3, rs.getString("goodsBarCode"));
          insertPstmt.setDouble(4, rs.getDouble("costPrice"));
          insertPstmt.setInt(5, rs.getInt("quantity"));
          insertPstmt.setTimestamp(6, rs.getTimestamp("usefulLife"));
          insertPstmt.executeUpdate();
        }
      }
/*取得saleLedger數(shù)據(jù)表和saleSubLedger數(shù)據(jù)表的上期數(shù)據(jù)*/
      //創(chuàng)建取得上期saleLedger數(shù)據(jù)表未完成單據(jù)的記錄
      sql = "select * from saleLedger" + lastLedgerDate + " where onProcess = 0";
      rs = stmt.executeQuery(sql);
      //創(chuàng)建插入的SQL語句
      insertSql = "insert into saleLedger" + ledgerDate +
          " values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
      insertPstmt = conn.prepareStatement(insertSql);
      //創(chuàng)建單據(jù)標(biāo)識集合類
      Vector saleIdVector = new Vector();
      String tempSaleId = "";
      while(rs.next()){
        tempSaleId = rs.getString("saleId");
        saleIdVector.addElement(tempSaleId);
        insertPstmt.setString(1, tempSaleId);