stockmanagementdata.java~719~

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

    String[][] data = new String[0][5];
    try {
      data = stockManagementData.getSaleSubLedgerBySaleId(ledgerDate, saleId);
    }
    catch (Exception ex) {
      ex.printStackTrace();
    }
    return data;
  }
  //根据字符串字段取得销售账套数据表记录的方法
  public String[][] getSaleLedgerByStringField(String ledgerDate,
                                               String fieldName,
                                               String fieldValue, int saleType) {
    String[][] data = new String[0][11];
    try {
      data = stockManagementData.getSaleLedgerByStringField(ledgerDate,
          fieldName, fieldValue, saleType);
    }
    catch (Exception ex) {
      ex.printStackTrace();
    }
    return data;
  }
  //根据完成状态取得销售账套数据表记录的方法
  public String[][] getSaleLedgerByOnProcess(String ledgerDate, int saleType,
                                             int onProcess) {
    String[][] data = new String[0][11];
    try {
      data = stockManagementData.getSaleLedgerByOnProcess(ledgerDate, saleType,
          onProcess);
    }
    catch (Exception ex) {
      ex.printStackTrace();
    }
    return data;
  }
  //根据订单日期取得销售账套数据表记录的方法
  public String[][] getSaleLedgerByOrderDate(String ledgerDate,
                                             java.sql.Timestamp startDate,
                                             java.sql.Timestamp endDate,
                                             int saleType) {
    String[][] data = new String[0][11];
    try {
      data = stockManagementData.getSaleLedgerByOrderDate(ledgerDate, startDate,
          endDate, saleType);
    }
    catch (Exception ex) {
      ex.printStackTrace();
    }
    return data;
  }
  //前台销售员进行销售账套数据表电子签名的方法
  public int counterUserSignSaleLedgerAndSub(String ledgerDate, String userName,
                                             String remark, String warehouse) {
    int result = 0;
    try{
      result = stockManagementData.counterUserSignSaleLedgerAndSub(ledgerDate,
          userName, remark, warehouse);
    }catch(Exception ex){
      ex.printStackTrace();
    }
    return result;
  }
  //根据商品的条形码取得销售价和折扣
  public double[] getGoodsPriceAndDiscount(String goodsBarcode){
    double[] data = new double[2];
    data[0] = 0;
    data[1] = 0;
    try{
      //取得商品数据
      String[][] goods = stockManagementData.getGoodsByGoodsBarCode(
          goodsBarcode);
      if(goods.length == 1){
        data[0] = Double.parseDouble(goods[0][11]);
        data[1] = Double.parseDouble(goods[0][12]);
      }
    }catch(Exception ex){
      ex.printStackTrace();
    }
    return data;
  }
  //根据条形码、仓库取得商品的数量，然后检查卖出商品的数量是否足够
  public int checkGoodsSaleQuantity(String ledgerDate, String goodsBarcode,
                                    int saleQuantity, String warehouse) {
    int result = 0;
    try{
      //取得商品的库存数组
      String[][] data = stockManagementData.getStocktakeQuantityByWarehouse(ledgerDate, warehouse, 2);
      int stockQuantity = 0;
      for(int i = 0; i < data.length; i++){
        if(data[i][0].equals(goodsBarcode)){
          stockQuantity = Integer.parseInt(data[i][1]);
          break;
        }
      }
      //如果库存数大于等于销售数返回1，否则返回0
      if(stockQuantity >= saleQuantity){
        result = 1;
      }
    }catch(Exception ex){
      ex.printStackTrace();
    }
    return result;
  }
  //信用销售用户进行销售账套数据表电子签名的方法
  public int creditUserSignSaleLedgerAndSub(String ledgerDate, String userName,
                                            String remark, String warehouse,
                                            String saleId, String customerName) {
    int result = 0;
   try{
     result = stockManagementData.creditUserSignSaleLedgerAndSub(ledgerDate,
         userName, remark, warehouse, saleId, customerName);
   }catch(Exception ex){
     ex.printStackTrace();
   }
   return result;
  }
  //检查客户的方法
  public int checkCustomer(String customerName){
    int result = 0;
    String[][] detail = this.getCustomersByCustomerName(customerName);
    for(int i = 0; i < detail.length; i++){
      if(detail[i][0].equals(customerName)){
        result = 1;
        break;
      }
    }
    return result;
  }
  //检查客户信用限额的方法
  public int checkCustomerCreditLimit(String customerName, double amount){
    int result = 0;
    double creditLimit = 0;
    String[][] detail = this.getCustomersByCustomerName(customerName);
    for(int i = 0; i < detail.length; i++){
      if(detail[i][0].equals(customerName)){
        creditLimit = Double.parseDouble(detail[i][15]);
        //如果客户信用限额大于或者等于客户单次购买的金额，通过测试
        if(creditLimit >= amount){
          result = 1;
        }
        break;
      }
    }
    return result;
  }
  //现金管理员对应收票据进行电子签名的方法
  public int cashUserSignAccountReceivable(String ledgerDate, String userName,
                                           String[] currentAccountLedger) {
    int result = 0;
    try{
      result = stockManagementData.cashUserSignAccountReceivable(ledgerDate,
          userName, currentAccountLedger);
    }catch(Exception ex){
      ex.printStackTrace();
    }
    return result;
  }
  //根据关联标识、退货单的商品条形码取得库存账套的销售出库单明细账的数据
  public String[][] getStockSaleOutBySaleOrderLink(String ledgerDate,
      String saleOrderLink, String saleId) {
    String[][] data = new String[0][4];
    try {
      data = stockManagementData.getStockSaleOutBySaleOrderLink(ledgerDate,
          saleOrderLink, saleId);
    }
    catch (Exception ex) {
      ex.printStackTrace();
    }
    return data;
  }
  //前台销售员进行销售退货单电子签名的方法
  public int counterUserSignSaleReturn(String ledgerDate, String saleId,
                                       String userName, String remark,
                                       String warehouse,
                                       String[][] stockSubLedger) {
    int result = 0;
    try{
      result = stockManagementData.counterUserSignSaleReturn(ledgerDate, saleId,
          userName, remark, warehouse, stockSubLedger);
    }catch(Exception ex){
      ex.printStackTrace();
    }
    return result;
  }
  //信用销售员进行信用销售退货单电子签名的方法
  public int creditUserSignSaleReturn(String ledgerDate, String saleId,
                                      String userName, String remark,
                                      String warehouse, String customerName,
                                      String[][] stockSubLedger, boolean isPay) {
    int result = 0;
    //取得帐套集合
    String[] ledgerDates = this.getLedgerNames();
    //只能对当前账套进行操作
    if(!ledgerDates[ledgerDates.length - 1].equals(ledgerDate)){
      return result;
    }
    //取得数据库联接
    Connection conn = getConnection();
    //创建更新语句
    String updateSaleLedgerSql = "update saleLedger" + ledgerDate + " set "
        + " onProcess = 2, remark = ?, deliveryDate = ? where saleId ='" + saleId + "' and onProcess = 0";
    //创建选择语句，取得传入销售退货单的总金额和总数量
    String goodsSoldReturnsql = "select goodsBarcode, sum(actualPrice*quantity) as amount , "
        + " sum(quantity) as quantity from saleSubLedger" + ledgerDate
        + " where saleId ='" + saleId +"'"
        + " group by goodsBarcode";
    try{
      //开始事务
      conn.setAutoCommit(false);
      //创建不带参数的SQL语句执行类
      Statement stmt = conn.createStatement();
      double saleAmount = 0;
      String[][] goodsSoldReturn = new String[0][3];
      int count = 0;
      //取得传入销售退货单的商品条形码、金额和数量
      ResultSet rs = stmt.executeQuery(goodsSoldReturnsql);
      //取得记录数
      while(rs.next()){
        count++;
      }
      goodsSoldReturn = new String[count][3];
      //重新取得记录集
      rs = stmt.executeQuery(goodsSoldReturnsql);
      for(int i = 0; i < count; i++){
        if(rs.next()){
          goodsSoldReturn[i][0] = rs.getString("goodsBarcode");
          //取得销售总金额
          double amount = dataMethod.round(rs.getDouble("amount"));
          saleAmount += amount;
          goodsSoldReturn[i][1] = String.valueOf(amount);
          goodsSoldReturn[i][2] = rs.getString("quantity");
        }
      }
      //创建带参数的SQL语句执行类
      PreparedStatement pstmt = conn.prepareStatement(updateSaleLedgerSql);
      //设置销售账套数据表的SQL语句参数
      pstmt.setString(1, remark);
      pstmt.setTimestamp(2, dataMethod.getCurrentDate());
      //执行销售账套数据表的更新操作，将传入销售退货单设为完成
      int subResult = pstmt.executeUpdate();
      //如果所有销售退货单已完成，退出事务处理
      if(subResult == 0){
        conn.rollback();
        return 0;
      }
      //创建出库单数组
      String[] stockLedger = new String[13];
      stockLedger[1] = "5";                    //5表示销售退货单
      //sa表示与销售账套数据表相联系
      stockLedger[2] = "sa" + saleId;          //关联标识
      stockLedger[3] = userName;               //销售员
      stockLedger[4] = "";
      stockLedger[5] = "";
      stockLedger[6] = "";
      stockLedger[7] = "";
      stockLedger[8] = warehouse;              //仓库名
      stockLedger[9] = dataMethod.getCurrentDate().toString();   //填写日期
      stockLedger[10] = dataMethod.getCurrentDate().toString();  //系统日期
      stockLedger[11] = "2";                   //销售退货单一经创建便处于完成状态
      stockLedger[12] = "";
      //创建会计分录数组,写入销售收入会计分录
      String[] accountEntryLedger = new String[7];
      //sa表示与销售账套数据表相联系
      accountEntryLedger[0] = "sa" + saleId;
      accountEntryLedger[1] = userName;
      accountEntryLedger[2] = "";
      accountEntryLedger[3] = dataMethod.getCurrentDate().toString();
      accountEntryLedger[4] = null;
      accountEntryLedger[5] = "0";
      accountEntryLedger[6] = "";
      //创建会计分录明细账数组，红字分录，借与货的金额均为负数
      String[][] accountEntrySubLedger = new String[goodsSoldReturn.length + 1][3];
      //写入借方分录
      accountEntrySubLedger[0][0] = "0";
      if(isPay){
        //已付款
        accountEntrySubLedger[0][1] = "短期负债@@应付账款@@" + customerName;
      }else{
        //未付款
        accountEntrySubLedger[0][1] = "流动资产@@应收账款@@" + customerName;
      }
      //由于销售退货单的数量为负数，所以saleAmount为负数
      accountEntrySubLedger[0][2] = String.valueOf(saleAmount);
      //写入贷方分录
      for(int i = 1; i < accountEntrySubLedger.length; i++){
        accountEntrySubLedger[i][0] = "1";
        accountEntrySubLedger[i][1] = "销售收入@@" + goodsSoldReturn[i - 1][0];
        accountEntrySubLedger[i][2] = String.valueOf(goodsSoldReturn[i - 1][1]);
      }
      //向数据表写入销售收入会计分录,应用本方法事务
      subResult = this.createAccountEntry(ledgerDate, accountEntryLedger,
                                              accountEntrySubLedger, true, conn);
      //如果不能正确添加会计分录，撤消数据库的事条
      if(subResult == 0){
        conn.rollback();
        return 0;
      }
      //创建会计分录数组,写入销售成本会计分录
      accountEntryLedger = new String[7];
      //sa表示与销售账套数据表相联系
      accountEntryLedger[0] = "sa" + saleId;
      accountEntryLedger[1] = userName;
      accountEntryLedger[2] = "";
      accountEntryLedger[3] = dataMethod.getCurrentDate().toString();
      accountEntryLedger[4] = null;
      accountEntryLedger[5] = "0";
      accountEntryLedger[6] = "";
      //取得成本数组
      String[][] goodsCost = this.getGoodsCost(goodsSoldReturn, stockSubLedger);
      //创建会计分录明细账数组，红字分录，借与货的金额均为负数
      accountEntrySubLedger = new String[goodsCost.length * 2][3];
      //写入借方分录
      for(int i = 0; i < goodsCost.length; i++){
        accountEntrySubLedger[i][0] = "0";
        accountEntrySubLedger[i][1] = "销售成本@@" + goodsCost[i][0];
        accountEntrySubLedger[i][2] = String.valueOf(goodsCost[i][1]);
      }
      //写入贷方分录
      int j = 0;
      for(int i = goodsCost.length; i < accountEntrySubLedger.length; i++){
        accountEntrySubLedger[i][0] = "1";
        accountEntrySubLedger[i][1] = "流动资产@@存货@@" + goodsCost[j][0];
        accountEntrySubLedger[i][2] = String.valueOf(goodsCost[j][1]);
        j++;
      }
      //向数据表写入销售成本会计分录,应用本方法事务
      subResult = this.createAccountEntry(ledgerDate, accountEntryLedger,
                                              accountEntrySubLedger, true, conn);
      //如果不能正确添加会计分录，撤消数据库的事条
      if(subResult == 0){
        conn.rollback();
        return 0;
      }
      if(isPay){
        //创建往来账套数组
        String[] currentAccountLedger = new String[10];
        //sa表示与销售账套数据表相联系
        currentAccountLedger[0] = "sa" + saleId;
        currentAccountLedger[1] = "0"; //0