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