/**
   * Get the weixin order according to specific extra condition{@link ExtraCond}.
   *
   * @param dbCon the database connection
   * @param staff the staff to perform this action
   * @param extraCond the extra condition {@link ExtraCond}
   * @return the result to wx order
   * @throws SQLException throws if failed to execute any SQL statement
   */
  public static List<WxOrder> getByCond(
      DBCon dbCon, Staff staff, ExtraCond extraCond, String orderClause) throws SQLException {
    String sql;
    sql =
        " SELECT * FROM "
            + Params.dbName
            + ".weixin_order "
            + " WHERE 1 = 1 "
            + " AND restaurant_id = "
            + staff.getRestaurantId()
            + (extraCond != null ? extraCond.toString() : " ")
            + (orderClause != null ? orderClause : "");
    dbCon.rs = dbCon.stmt.executeQuery(sql);

    List<WxOrder> result = new ArrayList<WxOrder>();
    while (dbCon.rs.next()) {
      WxOrder wxOrder = new WxOrder(dbCon.rs.getInt("wx_order_id"));
      wxOrder.setRestaurant(dbCon.rs.getInt("restaurant_id"));
      wxOrder.setStatus(WxOrder.Status.valueOf(dbCon.rs.getInt("status")));
      wxOrder.setType(WxOrder.Type.valueOf(dbCon.rs.getInt("type")));
      if (wxOrder.getType() == WxOrder.Type.TAKE_OUT) {
        TakeoutAddress address = new TakeoutAddress(dbCon.rs.getInt("address_id"));
        address.setAddress(dbCon.rs.getString("address"));
        wxOrder.setTakoutAddress(address);
      }
      wxOrder.setCode(dbCon.rs.getInt("code"));
      wxOrder.setOrderId(dbCon.rs.getInt("order_id"));
      wxOrder.setBirthDate(dbCon.rs.getTimestamp("birth_date").getTime());
      wxOrder.setWeixinSerial(dbCon.rs.getString("weixin_serial"));
      result.add(wxOrder);
    }
    dbCon.rs.close();

    return result;
  }
  /**
   * Delete the wx order to extra condition {@link ExtraCond}.
   *
   * @param dbCon the database connection
   * @param staff the staff to perform this action
   * @param extraCond the extra condition {@link ExtraCond}
   * @return the amount to wx order deleted
   * @throws SQLException throws if failed to execute any SQL statement
   */
  public static int deleteByCond(DBCon dbCon, Staff staff, ExtraCond extraCond)
      throws SQLException {
    int amount = 0;
    for (WxOrder wxOrder : getByCond(dbCon, staff, extraCond, null)) {
      if (OrderDao.getByCond(
              dbCon,
              staff,
              new OrderDao.ExtraCond(DateType.TODAY).setOrderId(wxOrder.getOrderId()),
              null)
          .isEmpty()) {
        String sql;
        // Delete the wx order.
        sql =
            " DELETE FROM "
                + Params.dbName
                + ".weixin_order WHERE wx_order_id = "
                + wxOrder.getId();
        dbCon.stmt.executeUpdate(sql);
        // Delete the associated order food.
        sql =
            " DELETE FROM "
                + Params.dbName
                + ".weixin_order_food WHERE wx_order_id = "
                + wxOrder.getId();
        dbCon.stmt.executeUpdate(sql);

        amount++;
      }
    }
    return amount;
  }
  /**
   * Insert the new weixin order for inside according to builder {@link
   * WxOrder#InsertBuilder4Inside}
   *
   * @param dbCon the database connection
   * @param staff the staff to perform this action
   * @param builder the builder {@link WxOrder#InsertBuilder4Inside}
   * @return the id to weixin order just inserted
   * @throws SQLException throws if failed to execute any SQL statement
   * @throws BusinessException throws if the member to this weixin serial does NOT exist
   */
  public static int insert(DBCon dbCon, Staff staff, WxOrder.InsertBuilder4Inside builder)
      throws SQLException, BusinessException {

    WxOrder wxOrder = builder.build();

    // Make the previous inside committed orders invalid.
    for (WxOrder order :
        getByCond(
            dbCon,
            staff,
            new ExtraCond()
                .setWeixin(wxOrder.getWeixinSerial())
                .setType(WxOrder.Type.INSIDE)
                .addStatus(WxOrder.Status.COMMITTED),
            null)) {
      try {
        update(
            dbCon,
            staff,
            new WxOrder.UpdateBuilder(order.getId()).setStatus(WxOrder.Status.INVALID));
      } catch (BusinessException ignored) {
        ignored.printStackTrace();
      }
    }

    // Insert the new inside order.
    return insert(dbCon, staff, wxOrder);
  }
 private static void fillDetail(DBCon dbCon, Staff staff, WxOrder wxOrder)
     throws SQLException, BusinessException {
   String sql;
   sql =
       " SELECT * FROM "
           + Params.dbName
           + ".weixin_order_food WHERE wx_order_id = "
           + wxOrder.getId();
   dbCon.rs = dbCon.stmt.executeQuery(sql);
   while (dbCon.rs.next()) {
     OrderFood of = new OrderFood();
     of.asFood().copyFrom(FoodDao.getById(staff, dbCon.rs.getInt("food_id")));
     of.setCount(dbCon.rs.getFloat("food_count"));
     wxOrder.addFood(of);
   }
   dbCon.rs.close();
   wxOrder.setMember(MemberDao.getByWxSerial(dbCon, staff, wxOrder.getWeixinSerial()));
 }
  /**
   * Update the wx order according to specific builder {@link WxOrder#UpdateBuilder}.
   *
   * @param dbCon the database connection
   * @param staff the staff to perform this action
   * @param builder the update builder {@link WxOrder#UpdateBuilder}
   * @throws SQLException throws if failed to execute any SQL statement
   * @throws BusinessException throws if the wx order to update does NOT exist
   */
  public static void update(DBCon dbCon, Staff staff, WxOrder.UpdateBuilder builder)
      throws SQLException, BusinessException {

    WxOrder wxOrder = builder.build();

    String sql;
    sql =
        " UPDATE "
            + Params.dbName
            + ".weixin_order SET "
            + " wx_order_id = "
            + wxOrder.getId()
            + (builder.isStatusChanged() ? " ,status = " + wxOrder.getStatus().getVal() : "")
            + (builder.isOrderChanged() ? " ,order_id = " + wxOrder.getOrderId() : "")
            + " WHERE wx_order_id = "
            + wxOrder.getId();

    if (dbCon.stmt.executeUpdate(sql) == 0) {
      throw new BusinessException(WxOrderError.WX_ORDER_NOT_EXIST);
    }
  }
  /**
   * Insert the new weixin order for take out according to builder {@link
   * WxOrder#InsertBuilder4Takeout}.
   *
   * @param dbCon the database connection
   * @param staff the staff to perform this action
   * @param builder the builder {@link WxOrder#InsertBuilder4Takeout}
   * @return the id to weixin order just inserted
   * @throws SQLException throws if failed to execute any SQL statement
   * @throws BusinessException throws if the take-out address to this order does NOT exist
   */
  public static int insert(DBCon dbCon, Staff staff, WxOrder.InsertBuilder4Takeout builder)
      throws SQLException, BusinessException {
    WxOrder wxOrder = builder.build();

    Member member = MemberDao.getByWxSerial(dbCon, staff, wxOrder.getWeixinSerial());
    if (TakeoutAddressDao.getByCond(
            dbCon,
            staff,
            new TakeoutAddressDao.ExtraCond()
                .setMember(member)
                .setId(wxOrder.getTakeoutAddress().getId()))
        .isEmpty()) {
      throw new BusinessException("外卖地址信息不属于此会员", WxOrderError.WX_TAKE_OUT_ORDER_NOT_ALLOW);
    }

    wxOrder.setTakoutAddress(
        TakeoutAddressDao.getById(dbCon, staff, wxOrder.getTakeoutAddress().getId()));

    int id = insert(dbCon, staff, wxOrder);
    String sql;
    sql =
        " UPDATE "
            + Params.dbName
            + ".weixin_order SET "
            + " wx_order_id = "
            + id
            + " ,address_id = "
            + wxOrder.getTakeoutAddress().getId()
            + " ,address = '"
            + wxOrder.getTakeoutAddress().getAddress()
            + "'"
            + " WHERE wx_order_id = "
            + id;
    dbCon.stmt.executeUpdate(sql);

    // Update the last used to take-out address
    sql =
        " UPDATE "
            + Params.dbName
            + ".take_out_address SET "
            + " last_used = NOW() "
            + " WHERE id = "
            + wxOrder.getTakeoutAddress().getId();
    dbCon.stmt.executeUpdate(sql);

    return id;
  }
  private static int insert(DBCon dbCon, Staff staff, WxOrder wxOrder)
      throws SQLException, BusinessException {

    String sql;

    // Check to see whether the member to this weixin serial exist.
    if (MemberDao.getByCond(
            dbCon,
            staff,
            new MemberDao.ExtraCond().setWeixinSerial(wxOrder.getWeixinSerial()),
            null)
        .isEmpty()) {
      throw new BusinessException("微信序列号对应的会员不存在", MemberError.MEMBER_NOT_EXIST);
    }

    // Generate the operation code.
    sql =
        " SELECT IFNULL(MAX(code) + 1, 100) FROM "
            + Params.dbName
            + ".weixin_order WHERE restaurant_id = "
            + staff.getRestaurantId();
    dbCon.rs = dbCon.stmt.executeQuery(sql);
    int code = 0;
    if (dbCon.rs.next()) {
      code = dbCon.rs.getInt(1);
    }
    dbCon.rs.close();

    // Insert the weixin order.
    sql =
        " INSERT INTO "
            + Params.dbName
            + ".`weixin_order` "
            + " (restaurant_id, weixin_serial, weixin_serial_crc, birth_date, status, type, code) "
            + " VALUES( "
            + staff.getRestaurantId()
            + ","
            + "'"
            + wxOrder.getWeixinSerial()
            + "',"
            + "CRC32('"
            + wxOrder.getWeixinSerial()
            + "'),"
            + " NOW(), "
            + wxOrder.getStatus().getVal()
            + ","
            + wxOrder.getType().getVal()
            + ","
            + code
            + " ) ";
    dbCon.stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
    dbCon.rs = dbCon.stmt.getGeneratedKeys();
    int wxOrderId;
    if (dbCon.rs.next()) {
      wxOrderId = dbCon.rs.getInt(1);
    } else {
      throw new SQLException("The id to wx order is NOT generated successfully.");
    }
    dbCon.rs.close();

    // Insert the associated order foods.
    for (OrderFood of : wxOrder.getFoods()) {
      sql =
          " INSERT INTO "
              + Params.dbName
              + ".weixin_order_food "
              + " (wx_order_id, food_id, food_count) VALUES( "
              + wxOrderId
              + ","
              + of.getFoodId()
              + ","
              + of.getCount()
              + ")";
      dbCon.stmt.executeUpdate(sql);
    }

    return wxOrderId;
  }