/**
   * 根据外键(主表ID)查找采购到货单明细
   *
   * @param con
   * @param param
   * @return
   * @throws SQLException
   */
  public Collection findPurchasesItemsByFK(Connection con, FinPurchaseForm param)
      throws SQLException {
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = null;
    Collection coll = new ArrayList();

    try {

      sql =
          "select a.*, b.item_code, b.name as item_name "
              + "from fin_ps_dtl a inner join prd_items b on a.item_id = b.item_id "
              + "where a.ps_id = ?";
      pstmt = con.prepareStatement(sql);
      pstmt.setInt(1, param.getPsID());
      rs = pstmt.executeQuery();

      while (rs.next()) {
        FinPurchaseItemsForm data = new FinPurchaseItemsForm();
        data.setPsDtlID(rs.getInt("PS_DTL_ID"));
        data.setPsID(rs.getInt("PS_ID"));
        data.setItemID(rs.getInt("ITEM_ID"));
        data.setItemCode(rs.getString("ITEM_CODE"));
        data.setItemName(rs.getString("ITEM_NAME"));
        data.setPurQty(rs.getDouble("PUR_QTY"));
        data.setPurPrice(rs.getDouble("PUR_PRICE"));
        data.setTaxPrice(rs.getDouble("TAX_PRICE"));
        data.setPurAmt(rs.getDouble("PUR_AMT"));
        data.setTaxAmt(rs.getDouble("TAX_AMT"));
        data.setAmt(rs.getDouble("AMT"));
        data.setTax(rs.getDouble("TAX"));
        data.setUseQty(rs.getDouble("USE_QTY"));
        data.setFinishQty(rs.getDouble("FINISH_QTY"));
        data.setFinishAmt(rs.getDouble("FINISH_AMT"));
        data.setReturnQty(rs.getDouble("RETURN_QTY"));
        data.setStatus(rs.getString("STATUS"));
        coll.add(data);
      }

    } catch (SQLException e) {
      logger.error("query error: [" + param.getPsID() + "]");
      throw e;
    } finally {
      if (rs != null)
        try {
          rs.close();
        } catch (Exception e) {
        }
      if (pstmt != null)
        try {
          pstmt.close();
        } catch (Exception e) {
        }
    }
    return coll;
  }
  /**
   * 根据发票明细的一些(个)主键查询发票明细行 用于弹出页关闭返回主页面
   *
   * @param con
   * @param PKs:格式为用逗号分开的字符串格式,如:1001,1002,1003;
   * @return
   * @throws SQLException
   */
  public Collection findCheckedPurchasesItemsByPKs(Connection con, String PKs) throws SQLException {
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = null;
    Collection coll = new ArrayList();

    try {

      sql =
          "select d.*, b.pro_no, b.pro_name, c.op_desc, e.item_code, e.name as item_name "
              + "from fin_ps_mst a inner join providers b on a.pro_no = b.pro_no "
              + "inner join fin_operation_mst c on a.operation_class = c.operation_class "
              + "inner join fin_ps_dtl d on a.ps_id = d.ps_id "
              + "inner join prd_items e on d.item_id = e.item_id "
              + "where a.status in ('2', '3') and d.status in('1', '2') "
              + "and d.ps_dtl_id in ("
              + PKs
              + ")";
      pstmt = con.prepareStatement(sql);
      rs = pstmt.executeQuery();
      while (rs.next()) {
        FinPurchaseItemsForm data = new FinPurchaseItemsForm();
        data.setPsDtlID(rs.getInt("PS_DTL_ID"));
        data.setPsID(rs.getInt("PS_ID"));
        data.setItemID(rs.getInt("ITEM_ID"));
        data.setItemCode(rs.getString("ITEM_CODE"));
        data.setItemName(rs.getString("ITEM_NAME"));
        data.setPurQty(rs.getDouble("PUR_QTY"));
        data.setPurPrice(rs.getDouble("PUR_PRICE"));
        data.setTaxPrice(rs.getDouble("TAX_PRICE"));
        data.setPurAmt(rs.getDouble("PUR_AMT"));
        data.setTaxAmt(rs.getDouble("TAX_AMT"));
        data.setAmt(rs.getDouble("AMT"));
        data.setTax(rs.getDouble("TAX"));
        data.setUseQty(rs.getDouble("USE_QTY"));
        data.setFinishQty(rs.getDouble("FINISH_QTY"));
        data.setFinishAmt(rs.getDouble("FINISH_AMT"));
        data.setReturnQty(rs.getDouble("RETURN_QTY"));
        data.setStatus(rs.getString("STATUS"));
        data.setProNO(rs.getString("PRO_NO"));
        data.setProName(rs.getString("PRO_NAME"));
        coll.add(data);
      }

    } catch (SQLException e) {
      logger.error("query error: [" + PKs + "]");
      throw e;
    } finally {
      if (rs != null)
        try {
          rs.close();
        } catch (Exception e) {
        }
      if (pstmt != null)
        try {
          pstmt.close();
        } catch (Exception e) {
        }
    }
    return coll;
  }
  /**
   * 根据供应商或产品查询主表状态为:2-审核,3-部分结算 从表状态为:1-未结算,2-部分结算的记录 用于新增发票时的弹出页
   *
   * @param con
   * @param param
   * @return
   * @throws SQLException
   */
  public Collection findCheckedPurchasesItems(Connection con, FinPurchaseForm param)
      throws SQLException {
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql = null;
    Collection coll = new ArrayList();

    try {

      sql =
          "select d.*, b.pro_no, b.pro_name, c.op_desc, e.item_code, e.name as item_name, "
              + "a.ps_code, purchasedate, res_no "
              + "from fin_ps_mst a inner join providers b on a.pro_no = b.pro_no "
              + "inner join fin_operation_mst c on a.operation_class = c.operation_class "
              + "inner join fin_ps_dtl d on a.ps_id = d.ps_id "
              + "inner join prd_items e on d.item_id = e.item_id "
              + "where a.status in ('2', '3') and d.status in('1', '2') ";
      if (param.getProOrItem() == 1) { // 供应商名称
        sql += "and b.pro_name like '%" + param.getProOrItemCondition() + "%' ";
      } else if (param.getProOrItem() == 2) { // 产品名称
        sql += "and e.name like '%" + param.getProOrItemCondition() + "%' ";
      } else if (param.getProOrItem() == 3) { // 供应商代码
        sql += "and b.pro_no = '" + param.getProOrItemCondition() + "' ";
      } else if (param.getProOrItem() == 4) { // 产品代码
        sql += "and e.item_code = '" + param.getProOrItemCondition() + "' ";
      }
      if (param.getIds() != null && !param.getIds().equals("")) {
        sql += "and d.ps_dtl_id not in(" + param.getIds() + ")";
      }
      // sql += "order by item_name ";
      if (param.getOrderByCondition1() != 0) {
        switch (param.getOrderByCondition1()) {
          case 1:
            sql += "order by purchasedate ";
            break;
          case 2:
            sql += "order by e.item_code ";
            break;
          case 3:
            sql += "order by e.name ";
            break;
          case 4:
            sql += "order by b.pro_no ";
            break;
          case 5:
            sql += "order by b.pro_name ";
            break;
          case 6:
            sql += "order by d.PUR_QTY ";
            break;
          case 7:
            sql += "order by d.PUR_PRICE ";
            break;
          case 8:
            sql += "order by d.USE_QTY ";
            break;
          case 9:
            sql += "order by d.FINISH_QTY ";
            break;
          case 10:
            sql += "order by a.ps_code ";
            break;
          case 11:
            sql += "order by res_no ";
            break;
        }
        if (param.getAscOrDesc1() == 0) {
          sql += "asc ";
        } else {
          sql += "desc ";
        }
      }
      if (param.getOrderByCondition2() != 0) {
        if (param.getOrderByCondition1() == 0) {
          switch (param.getOrderByCondition2()) {
            case 1:
              sql += "order by purchasedate ";
              break;
            case 2:
              sql += "order by e.item_code ";
              break;
            case 3:
              sql += "order by e.name ";
              break;
            case 4:
              sql += "order by b.pro_no ";
              break;
            case 5:
              sql += "order by b.pro_name ";
              break;
            case 6:
              sql += "order by d.PUR_QTY ";
              break;
            case 7:
              sql += "order by d.PUR_PRICE ";
              break;
            case 8:
              sql += "order by d.USE_QTY ";
              break;
            case 9:
              sql += "order by d.FINISH_QTY ";
              break;
            case 10:
              sql += "order by a.ps_code ";
              break;
            case 11:
              sql += "order by res_no ";
              break;
          }
          if (param.getAscOrDesc2() == 0) {
            sql += "asc ";
          } else {
            sql += "desc ";
          }
        } else {
          switch (param.getOrderByCondition2()) {
            case 1:
              sql += ",purchasedate ";
              break;
            case 2:
              sql += ",e.item_code ";
              break;
            case 3:
              sql += ",e.name ";
              break;
            case 4:
              sql += ",b.pro_no ";
              break;
            case 5:
              sql += ",b.pro_name ";
              break;
            case 6:
              sql += ",d.PUR_QTY ";
              break;
            case 7:
              sql += ",d.PUR_PRICE ";
              break;
            case 8:
              sql += ",d.USE_QTY ";
              break;
            case 9:
              sql += ",d.FINISH_QTY ";
              break;
            case 10:
              sql += ",a.ps_code ";
              break;
            case 11:
              sql += ",res_no ";
              break;
          }
          if (param.getAscOrDesc2() == 0) {
            sql += "asc ";
          } else {
            sql += "desc ";
          }
        }
      }
      if (param.getOrderByCondition3() != 0) {
        if (param.getOrderByCondition1() == 0 && param.getOrderByCondition2() == 0) {
          switch (param.getOrderByCondition3()) {
            case 1:
              sql += "order by purchasedate ";
              break;
            case 2:
              sql += "order by e.item_code ";
              break;
            case 3:
              sql += "order by e.name ";
              break;
            case 4:
              sql += "order by b.pro_no ";
              break;
            case 5:
              sql += "order by b.pro_name ";
              break;
            case 6:
              sql += "order by d.PUR_QTY ";
              break;
            case 7:
              sql += "order by d.PUR_PRICE ";
              break;
            case 8:
              sql += "order by d.USE_QTY ";
              break;
            case 9:
              sql += "order by d.FINISH_QTY ";
              break;
            case 10:
              sql += "order by a.ps_code ";
              break;
            case 11:
              sql += "order by res_no ";
              break;
          }
          if (param.getAscOrDesc3() == 0) {
            sql += "asc ";
          } else {
            sql += "desc ";
          }
        } else {
          switch (param.getOrderByCondition3()) {
            case 1:
              sql += ",purchasedate ";
              break;
            case 2:
              sql += ",e.item_code ";
              break;
            case 3:
              sql += ",e.name ";
              break;
            case 4:
              sql += ",b.pro_no ";
              break;
            case 5:
              sql += ",b.pro_name ";
              break;
            case 6:
              sql += ",d.PUR_QTY ";
              break;
            case 7:
              sql += ",d.PUR_PRICE ";
              break;
            case 8:
              sql += ",d.USE_QTY ";
              break;
            case 9:
              sql += ",d.FINISH_QTY ";
              break;
            case 10:
              sql += ",a.ps_code ";
              break;
            case 11:
              sql += ",res_no ";
              break;
          }
          if (param.getAscOrDesc3() == 0) {
            sql += "asc ";
          } else {
            sql += "desc ";
          }
        }
      }
      pstmt = con.prepareStatement(sql);
      rs = pstmt.executeQuery();

      while (rs.next()) {
        FinPurchaseItemsForm data = new FinPurchaseItemsForm();
        data.setPsDtlID(rs.getInt("PS_DTL_ID"));
        data.setPsID(rs.getInt("PS_ID"));
        data.setItemID(rs.getInt("ITEM_ID"));
        data.setItemCode(rs.getString("ITEM_CODE"));
        data.setItemName(rs.getString("ITEM_NAME"));
        data.setPurQty(rs.getDouble("PUR_QTY"));
        data.setPurPrice(rs.getDouble("PUR_PRICE"));
        data.setTaxPrice(rs.getDouble("TAX_PRICE"));
        data.setPurAmt(rs.getDouble("PUR_AMT"));
        data.setTaxAmt(rs.getDouble("TAX_AMT"));
        data.setAmt(rs.getDouble("AMT"));
        data.setTax(rs.getDouble("TAX"));
        data.setUseQty(rs.getDouble("USE_QTY"));
        data.setFinishQty(rs.getDouble("FINISH_QTY"));
        data.setFinishAmt(rs.getDouble("FINISH_AMT"));
        data.setReturnQty(rs.getDouble("RETURN_QTY"));
        data.setStatus(rs.getString("STATUS"));
        data.setProNO(rs.getString("PRO_NO"));
        data.setProName(rs.getString("PRO_NAME"));
        data.setResNO(rs.getString("RES_NO"));
        data.setPsCode(rs.getString("PS_CODE"));
        data.setPurchaseDate(rs.getDate("PURCHASEDATE"));
        coll.add(data);
      }
      System.out.println(sql);
    } catch (SQLException e) {
      logger.error("query error: [" + param.getProOrItemCondition() + "]");
      throw e;
    } finally {
      if (rs != null)
        try {
          rs.close();
        } catch (Exception e) {
        }
      if (pstmt != null)
        try {
          pstmt.close();
        } catch (Exception e) {
        }
    }
    return coll;
  }