stockmanagementdatabean.java

一个使用java 写的进销存代码 使用了ejb 等技术 是学习j2ee的好

        insertPstmt.setInt(2, rs.getInt("saleType"));
        insertPstmt.setString(3, rs.getString("customerName"));
        insertPstmt.setString(4, rs.getString("counterUser"));
        insertPstmt.setString(5, rs.getString("creditUser"));
        insertPstmt.setString(6, rs.getString("cashUser"));
        insertPstmt.setString(7, rs.getString("address"));
        insertPstmt.setTimestamp(8, rs.getTimestamp("fillerDate"));
        insertPstmt.setTimestamp(9, rs.getTimestamp("deliveryDate"));
        insertPstmt.setInt(10, rs.getInt("onProcess"));
        insertPstmt.setString(11, rs.getString("remark"));
        insertPstmt.executeUpdate();
      }
      //创建取得上期saleSubLedger数据表未完成单据的记录
      sql = "select * from saleSubLedger" + lastLedgerDate + " where saleId = ?";
      //创建插入saleSubLedger数据表的SQL语句
      insertSql = "insert into saleSubLedger" + ledgerDate + " values(?, ?, ?, ?, ?)";
      selectPstmt = conn.prepareStatement(sql);
      insertPstmt = conn.prepareStatement(insertSql);
      //序列号从0开始
      serialId = 0;
      for(int i = 0; i < saleIdVector.size(); i++){
        tempSaleId = (String)saleIdVector.get(i);
        //设置SQL语句的单据变量
        selectPstmt.setString(1, tempSaleId);
        //根据单据标识取得产品数据
        rs = selectPstmt.executeQuery();
        while(rs.next()){
          serialId++;
          insertPstmt.setInt(1, serialId);
          insertPstmt.setString(2, tempSaleId);
          insertPstmt.setString(3, rs.getString("goodsBarCode"));
          insertPstmt.setDouble(4, rs.getDouble("actualPrice"));
          insertPstmt.setInt(5, rs.getInt("quantity"));
          insertPstmt.executeUpdate();
        }
      }
/*取得currentAccountLedger数据表的上期数据*/
      //创建取得上期currentAccountLedger数据表未完成单据的记录
      sql = "select * from currentAccountLedger" + lastLedgerDate + " where onProcess = 0";
      rs = stmt.executeQuery(sql);
      //创建插入的SQL语句
      insertSql = "insert into currentAccountLedger" + ledgerDate +
          " values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
      insertPstmt = conn.prepareStatement(insertSql);
      while(rs.next()){
        insertPstmt.setString(1, rs.getString("currentAccountId"));
        insertPstmt.setString(2, rs.getString("linkId"));
        insertPstmt.setInt(3, rs.getInt("documentType"));
        insertPstmt.setDouble(4, rs.getDouble("amount"));
        insertPstmt.setString(5, rs.getString("receiverName"));
        insertPstmt.setString(6, rs.getString("documentFiller"));
        insertPstmt.setString(7, rs.getString("cashUser"));
        insertPstmt.setTimestamp(8, rs.getTimestamp("fillDate"));
        insertPstmt.setTimestamp(9, rs.getTimestamp("payDate"));
        insertPstmt.setInt(10, rs.getInt("onProcess"));
        insertPstmt.setString(11, rs.getString("remark"));
        insertPstmt.executeUpdate();
      }
/*取得accountEntryLedger数据表和accountEntrySubLedger数据表的上期数据*/
      //创建插入的SQL语句
      insertSql = "insert into accountEntryLedger" + ledgerDate +
          " values(?, ?, ?, ?, ?, ?, ?, ?)";
      //创建带参数的插入SQL语句执行类
      insertPstmt = conn.prepareStatement(insertSql);
      //创建accountEntryLedger数据表的序号
      int aelSerial = 1;
      //为会计分录账套数据表加入上期数据记录
      insertPstmt.setInt(1, aelSerial);
      insertPstmt.setString(2, "上期转入");
      insertPstmt.setString(3, "");
      insertPstmt.setString(4, "");
      insertPstmt.setTimestamp(5, dataMethod.getCurrentDate());
      insertPstmt.setTimestamp(6, null);
      insertPstmt.setInt(7, 2);
      insertPstmt.setString(8, "");
      insertPstmt.executeUpdate();
      //创建取得accountEntrySubLedger数据表的会计科目余额的SQL语句
      sql = "select debitCredit, accountName, sum(amount) as amount"
            + " from accountEntryLedger" + lastLedgerDate + " as a , "
            + " accountEntrySubLedger" + lastLedgerDate + " as b "
            + " where a.serialId = b.linkSerialId and onProcess = 2 "
            + " group by debitCredit, accountName"
            + " order by debitCredit";
      //创建插入accountEntrySubLedger数据表的SQL语句
      insertSql = "insert into accountEntrySubLedger" + ledgerDate + " values(?, ?, ?, ?, ?)";
      insertPstmt = conn.prepareStatement(insertSql);
      //创建accountEntrySubLedger数据表的序号
      int aeslSerial = 0;
      //取得accountEntrySubLedger数据表的上期数据
      rs = stmt.executeQuery(sql);
      while(rs.next()){
        aeslSerial++;
        insertPstmt.setInt(1, aeslSerial);
        insertPstmt.setInt(2, aelSerial);
        insertPstmt.setInt(3, rs.getInt("debitCredit"));
        insertPstmt.setString(4, rs.getString("accountName"));
        insertPstmt.setDouble(5, rs.getDouble("amount"));
        insertPstmt.executeUpdate();
      }
      //创建取得上期accountEntryLedger数据表未完成单据的记录
      sql = "select * from accountEntryLedger" + lastLedgerDate + " where onProcess = 0";
      rs = stmt.executeQuery(sql);
      //创建插入的SQL语句
      insertSql = "insert into accountEntryLedger" + ledgerDate +
          " values(?, ?, ?, ?, ?, ?, ?, ?)";
      insertPstmt = conn.prepareStatement(insertSql);
      //创建标识集合类
      Vector serialIdVector = new Vector();
      while(rs.next()){
        aelSerial++;
        serialIdVector.addElement(new Integer(aelSerial));
        insertPstmt.setInt(1, aelSerial);
        insertPstmt.setString(2, rs.getString("linkId"));
        insertPstmt.setString(3, rs.getString("filler"));
        insertPstmt.setString(4, rs.getString("auditUser"));
        insertPstmt.setTimestamp(5, rs.getTimestamp("fillDate"));
        insertPstmt.setTimestamp(6, rs.getTimestamp("auditDate"));
        insertPstmt.setInt(7, rs.getInt("onProcess"));
        insertPstmt.setString(8, rs.getString("remark"));
        insertPstmt.executeUpdate();
      }
      //创建取得上期accountEntrySubLedger数据表未完成会计分录的记录
      sql = "select * from accountEntrySubLedger" + lastLedgerDate + " where linkSerialId = ?";
      //创建插入accountEntrySubLedger数据表的SQL语句
      insertSql = "insert into accountEntrySubLedger" + ledgerDate + " values(?, ?, ?, ?, ?)";
      selectPstmt = conn.prepareStatement(sql);
      insertPstmt = conn.prepareStatement(insertSql);
      int tempSerialId = 0;
      for(int i = 0; i < serialIdVector.size(); i++){
        tempSerialId = ((Integer)serialIdVector.get(i)).intValue();
        //设置SQL语句的变量
        selectPstmt.setInt(1, tempSerialId);
        //取得记录
        rs = selectPstmt.executeQuery();
        while(rs.next()){
          aeslSerial++;
          insertPstmt.setInt(1, aeslSerial);
          insertPstmt.setInt(2, tempSerialId);
          insertPstmt.setInt(3, rs.getInt("debitCredit"));
          insertPstmt.setString(4, rs.getString("accountName"));
          insertPstmt.setDouble(5, rs.getDouble("amount"));
          insertPstmt.executeUpdate();
        }
      }
      //提交事条
      conn.commit();
      result = 1;
    }catch(Exception ex){
      try{
        //撤消事务
        conn.rollback();
      }catch(Exception ex1){
        ex1.printStackTrace();
      }
      ex.printStackTrace();
    }
    //清空数据库联接
    cleanup(conn);
    return result;
  }
  //删除账套的方法
  public int deleteLedger(String ledgerDate) {
    int result = 0;
    String[] ledgerNames = getLedgerNames();
    //如果数据库没有账套数据，返回0
    if(ledgerNames.length == 0){
      return 0;
    }
    //为字符串数组排序
    Arrays.sort(ledgerNames);
    //如果不是最后一个账套，不能实现删除操作
    if(ledgerNames[ledgerNames.length - 1].indexOf(ledgerDate) == -1){
      return 0;
    }
    //取得数据库联接
    Connection conn = getConnection();
    //创建删除数据表数组
    String[] delTableNames = new String[] {
        "stockLedger" + ledgerDate, "stockSubLedger" + ledgerDate,
        "saleLedger" + ledgerDate, "saleSubLedger" + ledgerDate,
        "currentAccountLedger" + ledgerDate, "cashLedger" + ledgerDate,
        "accountEntryLedger" + ledgerDate, "accountEntrySubLedger" + ledgerDate
    };
    //创建删除SQL语句
    String deleteSql = "";
    try{
      //开始事务
      conn.setAutoCommit(false);
      //创建不带参数的SQL语句执行类
      Statement stmt = conn.createStatement();
      //删除数据表
      for(int i = 0; i < delTableNames.length; i++){
        deleteSql = "drop table " + delTableNames[i];
        stmt.executeUpdate(deleteSql);
      }
      //提交事务
      conn.commit();
      result = 1;
    }catch(Exception ex){
      try{
        //撤消事务
        conn.rollback();
      }catch(Exception ex1){
        ex1.printStackTrace();
      }
      ex.printStackTrace();
    }
    //清空数据库联接
    cleanup(conn);
    return result;
  }
  //创建商品类别的方法
  public int createGoodsCategory(int parentId, String categoryName, String categoryDescription) {
    int result = 0;
    try {
      Collection col = goodsCategoryHome.findAll();
      //根据集合创建Vector集合类
      Vector vector = new Vector(col);
      Integer categoryId = null;
      if (col.size() > 0) {
        //取得最后一条记录
        GoodsCategory goodsCategory = (GoodsCategory) PortableRemoteObject.narrow(
            vector.lastElement(), GoodsCategory.class);
        //创建新序号
        int newInt = goodsCategory.getCategoryId().intValue() + 1;
        categoryId = new Integer(newInt);
      }
      else {
        //如果集合不返回记录，开始序号是1
        categoryId = new Integer(1);
      }
      goodsCategoryHome.create(categoryId , parentId, categoryName,
                               categoryDescription);
      result = 1;
    }catch (Exception ex) {
      ex.printStackTrace();
    }
    return result;
  }
  //更新商品类别的方法
  public int updateGoodsCategory(int categoryId, int parentId, String categoryName, String categoryDescription) {
    int result = 0;
    try {
      goodsCategory = goodsCategoryHome.findByPrimaryKey(new Integer(categoryId));
      goodsCategory.setParentId(parentId);
      goodsCategory.setCategoryName(categoryName);
      goodsCategory.setCategoryDescription(categoryDescription);
      result = 1;
    }catch (Exception ex) {
      ex.printStackTrace();
    }
    return result;
  }
  //删除商品类别的方法
  public int deleteGoodsCategory(int categoryId) {
    int result = 0;
    try {
      Collection col = goodsHome.findByGoodsCategory(categoryId);
      //只有商品类别没有商品数量才可以删除商品类别
      if(col.size() == 0){
        goodsCategory = goodsCategoryHome.findByPrimaryKey(new Integer(categoryId));
        goodsCategory.remove();
        result = 1;
      }
    }catch (Exception ex) {
      ex.printStackTrace();
    }
    return result;
  }
  //取得所有商品类别的方法
  public String[][] getAllGoodsCategory() {
    String[][] detail = new String[0][4];
    try{
      //取得商品类别的所有记录
      Collection col = goodsCategoryHome.findAll();
      if(col.size() > 0){
        Iterator iterator = col.iterator();
        //重新创建数组
        detail = new String[col.size()][4];
        int i = 0;
        while (iterator.hasNext()) {
          //取得远程接口
          goodsCategory = (GoodsCategory) PortableRemoteObject.narrow(
              iterator.next(), GoodsCategory.class);
          detail[i][0] = String.valueOf(goodsCategory.getCategoryId());
          detail[i][1] = String.valueOf(goodsCategory.getParentId());
          detail[i][2] = goodsCategory.getCategoryName();
          detail[i][3] = goodsCategory.getCategoryDescription();
          i++;
        }
      }
    }catch(Exception ex){
      ex.printStackTrace();
    }
    return detail;
  }
  //创建商品的方法
  public int createGoods(String goodsBarCode, int categoryId, String goodsName, String goodsNickName, String goodsAssistantName, String goodsPYName, String unit, String specification, String producer, int upperLimit, int lowerLimit, double salePrice, double discount) {
     int result = 0;
     try {
       goodsHome.create(goodsBarCode, categoryId, goodsName, goodsNickName,
                     goodsAssistantName, goodsPYName, unit,
                     specification, producer, upperLimit, lowerLimit,
                     salePrice, discount);
       result = 1;
     }catch (Exception ex) {
       ex.printStackTrace();
     }
     return result;
  }
  //更新商品的方法
  public int updateGoods(String goodsBarCode, int categoryId, String goodsName, String goodsNickName, String goodsAssistantName, String goodsPYName, String unit, String specification, String producer, int upperLimit, int lowerLimit, double salePrice, double discount) {
    int result = 0;
    try {
      goods = goodsHome.findByPrimaryKey(goodsBarCode);
      goods.setCategoryId(categoryId);
      goods.setGoodsName(goodsName);
      goods.setGoodsNickName(goodsNickName);
      goods.setGoodsAssistantName(goodsAssistantName);
      goods.setGoodsPYName(goodsPYName);
      goods.setUnit(unit);
      goods.setSpecification(specification);
      goods.setProducer(producer);
      goods.setUpperLimit(upperLimit);
      goods.setLowerLimit(lowerLimit);
      goods.setSalePrice(salePrice);
      goods.setDiscount(discount);
      result = 1;
    }catch (Exception ex) {
      ex.printStackTrace();
    }
    return result;
  }
  //删除商品的方法