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;
}
/**
* Get the member type according to specific extra condition and order clause.
*
* @param dbCon the database connection
* @param extraCond the extra condition
* @param orderClause the order clause
* @return the result list
* @throws SQLException throws if failed to execute any SQL statement
* @throws BusinessException throws if any discount associated with the member type is NOT found
*/
public static List<MemberType> getByCond(
DBCon dbCon, Staff staff, ExtraCond extraCond, String orderClause)
throws SQLException, BusinessException {
final List<MemberType> result = new ArrayList<MemberType>();
String sql;
sql =
" SELECT "
+ " member_type_id, restaurant_id, exchange_rate, charge_rate, name, attribute, initial_point, type, `desc` "
+ " FROM "
+ Params.dbName
+ ".member_type MT "
+ " WHERE 1 = 1 "
+ " AND MT.restaurant_id = "
+ (staff.isBranch() ? staff.getGroupId() : staff.getRestaurantId())
+ (extraCond != null ? extraCond.toString() : " ")
+ (orderClause != null ? orderClause : "");
dbCon.rs = dbCon.stmt.executeQuery(sql);
while (dbCon.rs.next()) {
MemberType mt = new MemberType(dbCon.rs.getInt("member_type_id"));
mt.setRestaurantId(dbCon.rs.getInt("restaurant_id"));
mt.setName(dbCon.rs.getString("name"));
mt.setType(MemberType.Type.valueOf(dbCon.rs.getInt("type")));
mt.setChargeRate(dbCon.rs.getFloat("charge_rate"));
mt.setExchangeRate(dbCon.rs.getFloat("exchange_rate"));
mt.setAttribute(dbCon.rs.getInt("attribute"));
mt.setInitialPoint(dbCon.rs.getInt("initial_point"));
mt.setDesc(dbCon.rs.getString("desc"));
result.add(mt);
}
dbCon.rs.close();
for (MemberType eachType : result) {
if (staff.isBranch()) {
// Get the chain discount associated with this member type.
sql =
" SELECT discount_id, type FROM "
+ Params.dbName
+ ".member_chain_discount WHERE group_member_type_id = "
+ eachType.getId()
+ " AND branch_id = "
+ staff.getRestaurantId();
dbCon.rs = dbCon.stmt.executeQuery(sql);
while (dbCon.rs.next()) {
Discount distToMemberType = new Discount(dbCon.rs.getInt("discount_id"));
eachType.addDiscount(distToMemberType);
if (MemberType.DiscountType.valueOf(dbCon.rs.getInt("type"))
== MemberType.DiscountType.DEFAULT) {
eachType.setDefaultDiscount(distToMemberType);
}
}
dbCon.rs.close();
for (Discount discount : eachType.getDiscounts()) {
discount.copyFrom(DiscountDao.getById(dbCon, staff, discount.getId()));
}
if (eachType.hasDefaultDiscount()) {
eachType.setDefaultDiscount(
DiscountDao.getById(dbCon, staff, eachType.getDefaultDiscount().getId()));
}
// Get the chain price associated with this member type.
sql =
" SELECT price_plan_id, type FROM "
+ Params.dbName
+ ".member_chain_price WHERE group_member_type_id = "
+ eachType.getId()
+ " AND branch_id = "
+ staff.getRestaurantId();
dbCon.rs = dbCon.stmt.executeQuery(sql);
while (dbCon.rs.next()) {
PricePlan pp = new PricePlan(dbCon.rs.getInt("price_plan_id"));
eachType.addPricePlan(pp);
if (MemberType.PriceType.valueOf(dbCon.rs.getInt("type"))
== MemberType.PriceType.DEFAULT) {
eachType.setDefaultPrice(pp);
}
}
dbCon.rs.close();
for (PricePlan pp : eachType.getPrices()) {
pp.copyFrom(PricePlanDao.getById(dbCon, staff, pp.getId()));
}
if (eachType.hasDefaultPrice()) {
eachType.setDefaultPrice(
PricePlanDao.getById(dbCon, staff, eachType.getDefaultPrice().getId()));
}
// Get the chain charge associated with this member type
sql =
" SELECT charge_plan_id FROM "
+ Params.dbName
+ ".member_chain_charge WHERE group_member_type_id = "
+ eachType.getId()
+ " AND branch_id = "
+ staff.getRestaurantId();
dbCon.rs = dbCon.stmt.executeQuery(sql);
while (dbCon.rs.next()) {
eachType.addChargePlan(new ChargePlan(dbCon.rs.getInt("charge_plan_id")));
}
dbCon.rs.close();
for (ChargePlan c : eachType.getCharges()) {
c.copyFrom(ChargePlanDao.getById(dbCon, staff, c.getId()));
}
} else {
// Get the discount associated with this member type.
sql =
" SELECT discount_id, type FROM "
+ Params.dbName
+ ".member_type_discount WHERE member_type_id = "
+ eachType.getId();
dbCon.rs = dbCon.stmt.executeQuery(sql);
while (dbCon.rs.next()) {
Discount distToMemberType = new Discount(dbCon.rs.getInt("discount_id"));
eachType.addDiscount(distToMemberType);
if (MemberType.DiscountType.valueOf(dbCon.rs.getInt("type"))
== MemberType.DiscountType.DEFAULT) {
eachType.setDefaultDiscount(distToMemberType);
}
}
dbCon.rs.close();
for (Discount discount : eachType.getDiscounts()) {
discount.copyFrom(DiscountDao.getById(dbCon, staff, discount.getId()));
}
if (eachType.hasDefaultDiscount()) {
eachType.setDefaultDiscount(
DiscountDao.getById(dbCon, staff, eachType.getDefaultDiscount().getId()));
}
// Get the price plan associated with this member type.
sql =
" SELECT price_plan_id, type FROM "
+ Params.dbName
+ ".member_type_price WHERE member_type_id = "
+ eachType.getId();
dbCon.rs = dbCon.stmt.executeQuery(sql);
while (dbCon.rs.next()) {
PricePlan price = new PricePlan(dbCon.rs.getInt("price_plan_id"));
eachType.addPricePlan(price);
if (dbCon.rs.getInt("type") == MemberType.PriceType.DEFAULT.getVal()) {
eachType.setDefaultPrice(price);
}
}
dbCon.rs.close();
for (PricePlan pp : eachType.getPrices()) {
pp.copyFrom(PricePlanDao.getById(dbCon, staff, pp.getId()));
}
if (eachType.hasDefaultPrice()) {
eachType.setDefaultPrice(
PricePlanDao.getById(dbCon, staff, eachType.getDefaultPrice().getId()));
}
// Get the charge associated with this member type
sql =
" SELECT charge_plan_id FROM "
+ Params.dbName
+ ".member_type_charge WHERE member_type_id = "
+ eachType.getId();
dbCon.rs = dbCon.stmt.executeQuery(sql);
while (dbCon.rs.next()) {
eachType.addChargePlan(new ChargePlan(dbCon.rs.getInt("charge_plan_id")));
}
dbCon.rs.close();
for (ChargePlan c : eachType.getCharges()) {
c.copyFrom(ChargePlanDao.getById(dbCon, staff, c.getId()));
}
}
}
return result;
}
/**
* 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);
}
}
}