public static List<MemberType> getNotBelongMemberType(DBCon dbCon, Staff staff)
throws SQLException {
List<MemberType> list = new ArrayList<MemberType>();
String sql =
"SELECT member_type_id, name, attribute FROM "
+ Params.dbName
+ ".member_type WHERE restaurant_id = "
+ staff.getRestaurantId()
+ " AND member_type_id NOT IN (SELECT member_type_id FROM "
+ Params.dbName
+ ".member_level WHERE restaurant_id = "
+ staff.getRestaurantId()
+ ")";
dbCon.rs = dbCon.stmt.executeQuery(sql);
while (dbCon.rs.next()) {
MemberType mType = new MemberType(-1);
mType.setId(dbCon.rs.getInt("member_type_id"));
mType.setName(dbCon.rs.getString("name"));
mType.setAttribute(dbCon.rs.getInt("attribute"));
list.add(mType);
}
return list;
}
/**
* Insert a new member type.
*
* @param dbCon the database connection
* @param staff the staff to perform this action
* @param builder the builder to insert a new member type
* @return the id to member type just inserted
* @throws SQLException throws if failed to execute any SQL statement
*/
public static int insert(DBCon dbCon, Staff staff, MemberType.InsertBuilder builder)
throws SQLException {
MemberType mt = builder.build();
// 插入新数据
String sql =
" INSERT INTO "
+ Params.dbName
+ ".member_type "
+ " ( `restaurant_id`, `name`, `type`, `exchange_rate`, `charge_rate`, `attribute`, `initial_point`, `desc` )"
+ " VALUES("
+ staff.getRestaurantId()
+ ","
+ "'"
+ mt.getName()
+ "',"
+ mt.getType().getVal()
+ ","
+ +mt.getExchangeRate()
+ ","
+ mt.getChargeRate()
+ ","
+ mt.getAttribute().getVal()
+ ","
+ mt.getInitialPoint()
+ ","
+ "'"
+ mt.getDesc()
+ "'"
+ ")";
dbCon.stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
dbCon.rs = dbCon.stmt.getGeneratedKeys();
if (dbCon.rs.next()) {
mt.setId(dbCon.rs.getInt(1));
} else {
throw new SQLException("Failed to generated the member type id.");
}
// Insert the discounts associated with this member type.
for (Discount discount : mt.getDiscounts()) {
sql =
" INSERT INTO "
+ Params.dbName
+ ".member_type_discount"
+ " (`member_type_id`, `discount_id`, `type`) "
+ " VALUES ("
+ mt.getId()
+ ","
+ discount.getId()
+ ","
+ MemberType.DiscountType.NORMAL.getVal()
+ ")";
dbCon.stmt.executeUpdate(sql);
}
// Update the default discount.
if (mt.hasDefaultDiscount()) {
sql =
" UPDATE "
+ Params.dbName
+ ".member_type_discount"
+ " SET type = "
+ MemberType.DiscountType.DEFAULT.getVal()
+ " WHERE member_type_id = "
+ mt.getId()
+ " AND discount_id = "
+ mt.getDefaultDiscount().getId();
dbCon.stmt.executeUpdate(sql);
}
// Insert the price plans associated with this member type.
for (PricePlan plan : mt.getPrices()) {
sql =
" INSERT INTO "
+ Params.dbName
+ ".member_type_price"
+ " (`member_type_id`, `price_plan_id`, `type`) VALUES ("
+ mt.getId()
+ ","
+ plan.getId()
+ ","
+ MemberType.PriceType.NORMAL.getVal()
+ ")";
dbCon.stmt.executeUpdate(sql);
}
if (mt.hasDefaultPrice()) {
// Update the default price plan.
sql =
" UPDATE "
+ Params.dbName
+ ".member_type_price"
+ " SET type = "
+ MemberType.PriceType.DEFAULT.getVal()
+ " WHERE member_type_id = "
+ mt.getId()
+ " AND price_plan_id = "
+ mt.getDefaultPrice().getId();
dbCon.stmt.executeUpdate(sql);
}
// Update the chain discounts.
for (MemberType.Discount4Chain chainDiscount : builder.getChainDiscounts()) {
for (Discount discount : chainDiscount.getDiscounts()) {
sql =
" INSERT INTO "
+ Params.dbName
+ ".member_chain_discount "
+ " (group_member_type_id, branch_id, discount_id, type) VALUES ( "
+ mt.getId()
+ ","
+ chainDiscount.getBranchId()
+ ","
+ discount.getId()
+ ","
+ DiscountType.NORMAL.getVal()
+ ")";
dbCon.stmt.executeUpdate(sql);
}
sql =
" UPDATE "
+ Params.dbName
+ ".member_chain_discount SET type = "
+ DiscountType.DEFAULT.getVal()
+ " WHERE group_member_type_id = "
+ mt.getId()
+ " AND branch_id = "
+ chainDiscount.getBranchId()
+ " AND discount_id = "
+ chainDiscount.getDefaultDiscount().getId();
dbCon.stmt.executeUpdate(sql);
}
// Update the chain prices.
for (MemberType.Price4Chain chainPrice : builder.getChainPrices()) {
for (PricePlan plan : chainPrice.getPrices()) {
sql =
" INSERT INTO "
+ Params.dbName
+ ".member_chain_price "
+ " (group_member_type_id, branch_id, price_plan_id, type) VALUES ( "
+ mt.getId()
+ ","
+ chainPrice.getBranchId()
+ ","
+ plan.getId()
+ ","
+ MemberType.PriceType.NORMAL.getVal()
+ ")";
dbCon.stmt.executeUpdate(sql);
}
sql =
" UPDATE "
+ Params.dbName
+ ".member_chain_price SET type = "
+ MemberType.PriceType.DEFAULT.getVal()
+ " WHERE group_member_type_id = "
+ mt.getId()
+ " AND branch_id = "
+ chainPrice.getBranchId()
+ " AND price_plan_id = "
+ chainPrice.getDefaultPrice().getId();
dbCon.stmt.executeUpdate(sql);
}
// Insert the charge plans associated with this member type
for (ChargePlan plan : mt.getCharges()) {
sql =
" INSERT INTO "
+ Params.dbName
+ ".member_type_charge "
+ " (member_type_id, charge_plan_id) VALUES ( "
+ mt.getId()
+ ","
+ plan.getId()
+ ")";
dbCon.stmt.executeUpdate(sql);
}
// Insert the chain charges
for (MemberType.Charge4Chain chainCharges : builder.getChainCharges()) {
for (ChargePlan plan : chainCharges.getCharges()) {
sql =
" INSERT INTO "
+ Params.dbName
+ ".member_chain_charge "
+ " (group_member_type_id, branch_id, charge_plan_id) VALUES ) "
+ mt.getId()
+ ","
+ chainCharges.getBranchId()
+ ","
+ plan.getId()
+ ","
+ ")";
dbCon.stmt.executeUpdate(sql);
}
}
return mt.getId();
}