I have created custom bookings tables.
Table Structure
wp_et_bk_bookings
id - PK
order_id - FK
from_time
to_time
booking_date
user_id - FK
product_id - FK
status
wp_et_bk_bookings_details
id - PK
et_bk_bookings_id - FK
et_bk_event_ticket_types_dates_id - FK
ticket_type_name
quantity
I have two hooks used for block the stock exausts
In process_woocommerce_checkout_order_processed() once order confirmed I have added the booking.
But when user clicked place order for same ticket at same time it conflict and my stock gone in minus.
<?php
add_action('woocommerce_before_checkout_process', 'custom_remove_exhausted_booking_items');
add_action('woocommerce_checkout_order_processed', 'process_woocommerce_checkout_order_processed', 10, 1);
function custom_remove_exhausted_booking_items()
{
global $wpdb;
$wpdb->query('START TRANSACTION');
$exhausted = false;
foreach (WC()->cart->get_cart() as $cart_item_key => $cart_item) {
$current_quantity = $cart_item['quantity'];
$time_slot_id = isset($cart_item['time_slot_id']) ? $cart_item['time_slot_id'] : null;
$et_bk_event_ticket_types_dates_id = isset($cart_item['et_bk_event_ticket_types_dates_id']) ? $cart_item['et_bk_event_ticket_types_dates_id'] : null;
if (! $time_slot_id || ! $et_bk_event_ticket_types_dates_id) {
continue;
}
$ticket_types = $wpdb->get_results("
SELECT tt.*, ttd.id AS et_bk_event_ticket_types_dates_id, ttd.date, ttd.price AS date_price, ttd.quantity AS date_quantity, ttd.deposit_amount AS date_deposit
FROM {$wpdb->prefix}et_bk_event_ticket_types tt
LEFT JOIN {$wpdb->prefix}et_bk_event_ticket_types_dates ttd ON tt.id = ttd.et_bk_event_ticket_types_id
WHERE tt.et_bk_event_timeslots_id = $time_slot_id
AND ttd.id = $et_bk_event_ticket_types_dates_id
ORDER BY tt.sort_order ASC
");
if (empty($ticket_types)) {
continue;
}
foreach ($ticket_types as $ticket) {
$booked_quantity = $wpdb->get_var("
SELECT SUM(bd.quantity)
FROM {$wpdb->prefix}et_bk_bookings b
INNER JOIN {$wpdb->prefix}et_bk_bookings_details bd ON b.id = bd.et_bk_bookings_id
WHERE bd.et_bk_event_ticket_types_dates_id = {$ticket->et_bk_event_ticket_types_dates_id}
AND b.status IN ('pending', 'processing', 'on-hold', 'completed')
");
$booked_quantity += $current_quantity;
if ($booked_quantity > $ticket->date_quantity) {
WC()->cart->remove_cart_item($cart_item_key);
$exhausted = true;
}
}
}
if ($exhausted) {
$wpdb->query('ROLLBACK');
throw new Exception('Some items in your cart are no longer available for booking. Please review your cart and try again. <script>setTimeout(() => { location.reload(); }, 1000);</script>');
} else {
$wpdb->query('COMMIT');
}
}
function process_woocommerce_checkout_order_processed($order_id)
{
$inserted = $wpdb->insert(
$wpdb->prefix . 'et_bk_bookings',
array(
'order_id' => $order_id,
'time_slot_id' => $time_slot_id,
'time_slot' => $time_slot,
'booking_date' => date('Y-m-d', strtotime($booking_date)),
'user_id' => $user_id,
'product_id' => $product_id,
'from_time' => $time_slot_details->from_time,
'to_time' => $time_slot_details->to_time,
'status' => $status
),
array(
'%d',
'%d',
'%s',
'%s',
'%d',
'%d',
'%s',
'%s',
'%s',
)
);
if ($inserted) {
$booking_id = $wpdb->insert_id;
$future_payment_total = 0;
foreach ($original_order->get_items() as $item_id => $item) {
$ticket_type_name = $item->get_meta('ticket_type_name');
$et_bk_event_ticket_types_dates_id = $item->get_meta('et_bk_event_ticket_types_dates_id');
// Insert multiple entries in event_ticket_bookings_details
$wpdb->insert(
$wpdb->prefix . 'et_bk_bookings_details',
array(
'et_bk_bookings_id' => $booking_id,
'et_bk_event_ticket_types_dates_id' => $et_bk_event_ticket_types_dates_id,
'ticket_type_name' => $ticket_type_name,
'quantity' => $item->get_quantity()
),
array(
'%d',
'%d',
'%s',
'%d'
)
);
// Retrieve future payment from meta and convert to float
$future_payment = $item->get_meta('future_payment');
$future_payment = preg_replace('/[^d.]/', '', $future_payment); // Clean up the future payment value
$future_payment = floatval($future_payment);
if ($future_payment) {
$future_payment_total += $future_payment * $item->get_quantity();
}
}
Note – If user at different time only added in cart and then try to place order then it wont allowed.
