コード例 #1
0
  /**
   * 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();
  }
コード例 #2
0
  /**
   * 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);
      }
    }
  }