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