stockmanagementdatabean.java~3455~

java进销存信息管理系统,实现了仓库产品价格人员报表等功能

        }else{
          insertSql += "?" + ",";
        }
      }
      //创建带参数的SQL语句执行类
      PreparedStatement pstmt = conn.prepareStatement(insertSql);
      //创建日期转换类
      java.text.DateFormat dateLongFormat = java.text.DateFormat.getDateTimeInstance();
      java.text.DateFormat dateShortFormat = java.text.DateFormat.getDateInstance();
      //声明java.sql类包的时间变量
      Timestamp timeStamp = null;
      //将数组写入数据表
      for(int row = 0; row < data.length; row++){
        for (int col = 0; col < colCount; col++) {
          //设置字符串参数
          if (resultSetMetaData.getColumnTypeName(col + 1).equals("varchar") |
              resultSetMetaData.getColumnTypeName(col + 1).equals("longvarchar")) {
            pstmt.setString(col+1, data[row][col]);
          }
          //设置bit类型参数
          else if(resultSetMetaData.getColumnTypeName(col + 1).equals("bit")){
            pstmt.setInt(col+1, Integer.parseInt(data[row][col]));
          }
          //设置int类型参数
          else if(resultSetMetaData.getColumnTypeName(col + 1).equals("int")){
            pstmt.setInt(col+1, Integer.parseInt(data[row][col]));
          }
          //设置float类型参数
          else if(resultSetMetaData.getColumnTypeName(col + 1).equals("float") |
                  resultSetMetaData.getColumnTypeName(col + 1).equals("decimal")){
            pstmt.setDouble(col+1, Double.parseDouble(data[row][col]));
          }
          //设置timestamp类型参数
          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);
          }
        }
        //执行插入操作
        pstmt.execute();
      }
      //提交事条
      conn.commit();
      result = 1;
    }catch(Exception ex){
      try{
        //撤消事务
        conn.rollback();
      }catch(Exception ex1){
        ex1.printStackTrace();
      }
      data = new String[0][0];
      ex.printStackTrace();
    }
    //清空数据库联接
    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){
          //取得数据表的日期字符串
          vector.addElement(tableNames[i].substring(11, tableNames[i].length()));
        }
      }
    }
    //将vector集合的数据放入数组
    ledgerNames = new String[vector.size()];
    for(int i = 0; i < vector.size(); i++){
      ledgerNames[i] = (String)vector.get(i);
    }
    return ledgerNames;
  }
  //创建8个空账套的方法
  private void createEmptyLedger(Statement stmt, String ledgerDate) throws Exception {
    //创建SQL语句数组
    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)";
    //创建8个账套数据表
    for(int i = 0; i < createSqls.length; i++){
      stmt.executeUpdate(createSqls[i]);
    }
  }
  //创建账套的方法
  public int createLedger(String ledgerDate) {
    int result = 0;
    //取得数据库联接
    Connection conn = getConnection();
    try{
      //取得账套数组
      String[] ledgerNames = getLedgerNames();
      //开始事条
      conn.setAutoCommit(false);
      //创建不带参数的SQL语句执行类
      Statement stmt = conn.createStatement();
      //如果数据库没有账套，直接创建8个空账套数据表便可
      if (ledgerNames.length == 0) {
        //创建8个空账套
        createEmptyLedger(stmt, ledgerDate);
        conn.commit();
        return 1;
      }
      //为字符串数据排序
      Arrays.sort(ledgerNames);
      //取得上一个时间字符串
      String lastLedgerDate = ledgerNames[ledgerNames.length - 1];
      //如果新日期不大于旧日期，不能进行创建操作
      if (ledgerDate.compareTo(lastLedgerDate) <= 0) {
        return 0;
      }
      //创建8个空账套
      createEmptyLedger(stmt, ledgerDate);
/*取得stockLedger数据表和stockSubLedger数据表的上期数据*/
      String sql = "select distinct(a.warehouse) "
              + "from stockLedger" + lastLedgerDate
              + " as a , stockSubLedger" + lastLedgerDate
              + " as b where a.orderId = b.orderId and a.onProcess = 2";
     //创建取得stockLedger数据表的仓库的SQL语句
      ResultSet rs = stmt.executeQuery(sql);
      //创建仓库集合
      Vector warehouseVector = new Vector();
      String tempWarehouse;
      while(rs.next()){
        tempWarehouse = rs.getString(1).trim();
        if(tempWarehouse.length() > 0){
          warehouseVector.addElement(tempWarehouse);
        }
      }
      //创建上期库存单据数组
      String[] orderIds = new String[warehouseVector.size()];
      for(int i = 0; i < orderIds.length; i++){
        orderIds[i] = "上" + lastLedgerDate + dataMethod.changeSerial(i + 1);
      }
      //创建插入的SQL语句
      String insertSql = "insert into stockLedger" + ledgerDate +
          " values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
      //创建带参数的插入SQL语句执行类
      PreparedStatement insertPstmt = conn.prepareStatement(insertSql);
      //为库存账套数据表加入上期数据记录
      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();
      }
      //创建取得stockLedger数据表的不同仓库的产品总数的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";
      //创建插入stockSubLedger数据表的SQL语句
      insertSql = "insert into stockSubLedger" + ledgerDate + " values(?, ?, ?, ?, ?, ?)";
      //创建带参数的选择SQL语句执行类
      PreparedStatement selectPstmt = conn.prepareStatement(sql);
      insertPstmt = conn.prepareStatement(insertSql);
      //创建stockSubLedger数据表的序号
      int serialId = 0;
      for(int i = 0; i < warehouseVector.size(); i++){
        //设置SQL语句的仓库变量
        selectPstmt.setString(1, (String)warehouseVector.get(i));
        //取得仓库的产品数据
        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();
        }
      }
      //创建取得上期stockLedger数据表未完成单据的记录
      sql = "select * from stockLedger" + lastLedgerDate + " where onProcess = 0";
      rs = stmt.executeQuery(sql);
      //创建插入的SQL语句
      insertSql = "insert into stockLedger" + ledgerDate +
          " values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
      insertPstmt = conn.prepareStatement(insertSql);
      //创建单据标识集合类
      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();
      }
      //创建取得上期stockSubLedger数据表未完成单据的记录
      sql = "select * from stockSubLedger" + lastLedgerDate + " where orderId = ?";
      //创建插入stockSubLedger数据表的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);
        //设置SQL语句的单据变量
        selectPstmt.setString(1, tempOrderId);
        //根据单据标识取得产品数据
        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数据表和saleSubLedger数据表的上期数据*/
      //创建取得上期saleLedger数据表未完成单据的记录
      sql = "select * from saleLedger" + lastLedgerDate + " where onProcess = 0";
      rs = stmt.executeQuery(sql);
      //创建插入的SQL语句
      insertSql = "insert into saleLedger" + ledgerDate +
          " values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
      insertPstmt = conn.prepareStatement(insertSql);
      //创建单据标识集合类
      Vector saleIdVector = new Vector();
      String tempSaleId = "";
      while(rs.next()){
        tempSaleId = rs.getString("saleId");
        saleIdVector.addElement(tempSaleId);
        insertPstmt.setString(1, tempSaleId);