/**
* 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;
}