/**
* 単価取得メソッド
*
* @author Tahara Shoki
* @since 2015/07/13
* @param itemId 商品ID
* @return price
*/
public int selectPrice(int itemId) {
int price = 0;
try {
DBconnector dbConnection = new DBconnector();
Connection con = dbConnection.getConnection();
String sql = "select * from merchandise_info where item_id = ?";
PreparedStatement ps = con.prepareStatement(sql);
ps.setInt(1, itemId);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
price = rs.getInt(3);
}
} catch (SQLException e) {
return price;
}
return price;
}
/**
* 在庫数取得メソッド
*
* @author Tahara Shoki
* @since 2015/07/13
* @param shopId 店舗ID
* @param itemId 商品ID
* @param rentalDate レンタル日
* @return itemCount
*/
public int selectItemCount(int shopId, int itemId, String rentalDate) {
int itemCount = 0;
try {
DBconnector dbConnection = new DBconnector();
Connection con = dbConnection.getConnection();
String sql =
"select * from inventory_info where shop_id = ? and item_id = ? and rental_date = ?";
PreparedStatement ps = con.prepareStatement(sql);
ps.setInt(1, shopId);
ps.setInt(2, itemId);
ps.setString(3, rentalDate);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
itemCount = rs.getInt(3);
}
} catch (SQLException e) {
return itemCount;
}
return itemCount;
}
/**
* 予約履歴を削除するメソッド
*
* @author Tahara Shoki
* @since 2015/07/13
* @param customerId 顧客ID
* @param rentalDate レンタル日
* @param itemId 商品ID
* @param shopId 店舗ID
* @return result
*/
public boolean deleteOrder(int customerId, String rentalDate, int itemId, int shopId) {
boolean result = false;
try {
DBconnector dbConnection = new DBconnector();
Connection con = dbConnection.getConnection();
String sql =
"delete from purchase_history where customer_id = ? and rental_date = ? and item_Id = ? and shop_id = ?";
PreparedStatement ps = con.prepareStatement(sql);
ps.setInt(1, customerId);
ps.setString(2, rentalDate);
ps.setInt(3, itemId);
ps.setInt(4, shopId);
int pr = ps.executeUpdate();
if (pr > 0) {
result = true;
} else {
result = false;
}
return result;
} catch (SQLException e) {
return result;
}
}
/**
* 在庫情報を更新するメソッド
*
* @author Tahara Shoki
* @since 2015/07/13
* @param salesCount 予約数
* @param shopId 店舗ID
* @param itemId 商品ID
* @param rentalDate レンタル日
* @param itemCount 在庫数
* @param deleteCount 削除数
* @return result
*/
public boolean updateInventory(
int salesCount, int shopId, int itemId, String rentalDate, int itemCount, int deleteCount) {
boolean result = false;
try {
DBconnector dbConnection = new DBconnector();
Connection con = dbConnection.getConnection();
String sql =
"update inventory_info set item_count = ? where shop_id = ? and item_id = ? and rental_date = ?";
PreparedStatement ps = con.prepareStatement(sql);
ps.setInt(1, itemCount + deleteCount);
ps.setInt(2, shopId);
ps.setInt(3, itemId);
ps.setString(4, rentalDate);
int pr = ps.executeUpdate();
if (pr > 0) {
result = true;
} else {
result = false;
}
return result;
} catch (SQLException e) {
return result;
}
}
/**
* 予約履歴を更新するメソッド
*
* @author Tahara Shoki
* @since 2015/07/13
* @param salesCount 予約数
* @param rentalDate レンタル日
* @param customerId 顧客ID
* @param unitPrice 合計価格
* @param shopId 店舗ID
* @param itemId 商品ID
* @param price 単価
* @param deleteCount 削除数
* @return result
*/
public boolean updatePurchase(
int salesCount,
String rentalDate,
int customerId,
int unitPrice,
int shopId,
int itemId,
int price,
int deleteCount) {
boolean result = false;
try {
DBconnector dbConnection = new DBconnector();
Connection con = dbConnection.getConnection();
String sql =
"update purchase_history set sales_count = ?, unit_price = ? where customer_id = ? "
+ "and rental_date = ? and sales_count = ? and unit_price = ? and shop_id = ? and item_id = ?";
PreparedStatement ps = con.prepareStatement(sql);
ps.setInt(1, salesCount - deleteCount);
ps.setInt(2, price * (salesCount - deleteCount));
ps.setInt(3, customerId);
ps.setString(4, rentalDate);
ps.setInt(5, salesCount);
ps.setInt(6, unitPrice);
ps.setInt(7, shopId);
ps.setInt(8, itemId);
int pr = ps.executeUpdate();
if (pr > 0) {
result = true;
} else {
result = false;
}
return result;
} catch (SQLException e) {
return result;
}
}