/**
* 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();
}
/**
* Update the member type.
*
* @param dbCon the database connection
* @param staff the staff to perform this action
* @param builder the builder to update a member type
* @throws SQLException throws if failed to execute any SQL statement
* @throws BusinessException throws if the member type to update does NOT exist
*/
public static void update(DBCon dbCon, Staff staff, MemberType.UpdateBuilder builder)
throws SQLException, BusinessException {
String sql;
MemberType mt = builder.build();
// 更新数据
sql =
" UPDATE "
+ Params.dbName
+ ".member_type SET "
+ " member_type_id = "
+ mt.getId()
+ (builder.isNameChanged() ? " ,name = '" + mt.getName() + "'" : "")
+ (builder.isExchangRateChanged() ? " ,exchange_rate = " + mt.getExchangeRate() : "")
+ (builder.isChargeRateChanged() ? " ,charge_rate = " + mt.getChargeRate() : "")
+ (builder.isAttributeChanged() ? " ,attribute = " + mt.getAttribute().getVal() : "")
+ (builder.isInitialPointChanged() ? " ,initial_point = " + mt.getInitialPoint() : "")
+ (builder.isDescChanged() ? ",`desc` = '" + mt.getDesc() + "'" : "")
+ " WHERE restaurant_id = "
+ staff.getRestaurantId()
+ " AND member_type_id = "
+ mt.getId();
if (dbCon.stmt.executeUpdate(sql) == 0) {
throw new BusinessException(MemberError.MEMBER_TYPE_NOT_EXIST);
}
// Update the chain discounts.
if (builder.isChainDiscountChanged() && staff.isGroup()) {
for (MemberType.Discount4Chain chainDiscount : builder.getChainDiscounts()) {
sql =
" DELETE FROM "
+ Params.dbName
+ ".member_chain_discount "
+ " WHERE 1 = 1 "
+ " AND group_member_type_id = "
+ mt.getId()
+ " AND branch_id = "
+ chainDiscount.getBranchId();
dbCon.stmt.executeUpdate(sql);
// Check to see whether the branch belongs to the group.
sql =
" SELECT COUNT(*) FROM "
+ Params.dbName
+ ".restaurant_chain WHERE branch_id = "
+ chainDiscount.getBranchId()
+ " AND group_id = "
+ staff.getRestaurantId();
dbCon.rs = dbCon.stmt.executeQuery(sql);
if (dbCon.rs.next()) {
if (dbCon.rs.getInt(1) == 0) {
dbCon.rs.close();
continue;
}
}
dbCon.rs.close();
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.
if (builder.isChainPricesChanged() && staff.isGroup()) {
for (MemberType.Price4Chain chainPrice : builder.getChainPrices()) {
sql =
" DELETE FROM "
+ Params.dbName
+ ".member_chain_price "
+ " WHERE 1 = 1 "
+ " AND group_member_type_id = "
+ mt.getId()
+ " AND branch_id = "
+ chainPrice.getBranchId();
dbCon.stmt.executeUpdate(sql);
// Check to see whether the branch belongs to the group.
sql =
" SELECT COUNT(*) FROM "
+ Params.dbName
+ ".restaurant_chain WHERE branch_id = "
+ chainPrice.getBranchId()
+ " AND group_id = "
+ staff.getRestaurantId();
dbCon.rs = dbCon.stmt.executeQuery(sql);
if (dbCon.rs.next()) {
if (dbCon.rs.getInt(1) == 0) {
dbCon.rs.close();
continue;
}
}
dbCon.rs.close();
for (PricePlan pp : 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()
+ ","
+ pp.getId()
+ ","
+ DiscountType.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);
}
}
// Update the chain charge
if (builder.isChainChargesChanged() && staff.isGroup()) {
for (MemberType.Charge4Chain chainCharge : builder.getChainCharges()) {
sql =
" DELETE FROM "
+ Params.dbName
+ ".member_chain_charge "
+ " WHERE 1 = 1 "
+ " AND group_member_type_id = "
+ mt.getId()
+ " AND branch_id = "
+ chainCharge.getBranchId();
dbCon.stmt.executeUpdate(sql);
// check to see whether the branch belongs to the group
sql =
" SELECT COUNT(*) FROM "
+ Params.dbName
+ ".restaurant_chain WHERE branch_id = "
+ chainCharge.getBranchId()
+ " AND group_id = "
+ staff.getRestaurantId();
dbCon.rs = dbCon.stmt.executeQuery(sql);
if (dbCon.rs.next()) {
if (dbCon.rs.getInt(1) == 0) {
dbCon.rs.close();
continue;
}
}
dbCon.rs.close();
for (ChargePlan c : chainCharge.getCharges()) {
sql =
" INSERT INTO "
+ Params.dbName
+ ".member_chain_charge "
+ " (group_member_type_id, branch_id, charge_plan_id) VALUES ( "
+ mt.getId()
+ ","
+ chainCharge.getBranchId()
+ ","
+ c.getId()
+ ")";
dbCon.stmt.executeUpdate(sql);
}
}
}
if (builder.isDiscountChanged() || builder.isDefaultDiscountChanged()) {
sql =
" DELETE FROM "
+ Params.dbName
+ ".member_type_discount WHERE member_type_id = "
+ mt.getId();
dbCon.stmt.executeUpdate(sql);
// 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.
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);
}
if (builder.isPriceChanged() || builder.isDefaultPriceChanged()) {
sql =
" DELETE FROM "
+ Params.dbName
+ ".member_type_price WHERE member_type_id = "
+ mt.getId();
dbCon.stmt.executeUpdate(sql);
// Insert the price plan associated with this member type.
for (PricePlan price : mt.getPrices()) {
sql =
" INSERT INTO "
+ Params.dbName
+ ".member_type_price"
+ " (`member_type_id`, `price_plan_id`, `type`) "
+ " VALUES ("
+ mt.getId()
+ ","
+ price.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);
}
}
if (builder.isChargeChanged()) {
sql =
" DELETE FROM "
+ Params.dbName
+ ".member_type_charge WHERE member_type_id = "
+ mt.getId();
dbCon.stmt.executeUpdate(sql);
// Insert the charge plan associated with this member type
for (ChargePlan charge : mt.getCharges()) {
sql =
"INSERT INTO "
+ Params.dbName
+ ".member_type_charge"
+ " (member_type_id, charge_plan_id) "
+ " VALUES ( "
+ mt.getId()
+ ","
+ charge.getId()
+ ")";
dbCon.stmt.executeUpdate(sql);
}
}
}