Order Management

In this code, the "x" button for cancelling the product from the product list actually remove the product from the list and added back the quantity of that product to its stock. The code: 'Invalid order data. Cart is empty or total is zero.']); } if ($status === 'Paid' && ($amount_tendered === null || $amount_tendered < $total_amount)) { wp_send_json_error(['message' => 'Amount tendered is less than the total amount payable.']); } // --- Database Transaction --- $wpdb->query('START TRANSACTION'); try { // 1. Server-side Stock Check (Crucial!) foreach ($cart_data as $item) { $product_id = intval($item['id']); $quantity_ordered = intval($item['quantity']); // *** Use in_stock for check *** $current_stock = $wpdb->get_var($wpdb->prepare( "SELECT in_stock FROM $product_table_name WHERE id = %d FOR UPDATE", // Lock row $product_id )); if ($current_stock === null) { throw new Exception("Product ID {$product_id} not found during final check."); } if ($current_stock < $quantity_ordered) { throw new Exception("Insufficient stock for product '".esc_html($item['name'])."' (Available: {$current_stock}, Ordered: {$quantity_ordered}). Order cannot be completed."); } } // 2. Insert into 'orders' table $order_inserted = $wpdb->insert( $orders_table_name, [ 'order_date' => current_time('mysql', 1), 'total_amount' => $total_amount, 'status' => $status, 'amount_tendered' => ($status === 'Paid') ? $amount_tendered : null, 'change_given' => ($status === 'Paid') ? $change_given : null, ], [ '%s', '%f', '%s', '%f', '%f' ] // formats ); if (!$order_inserted) { throw new Exception("Failed to create order record: " . $wpdb->last_error); } $new_order_id = $wpdb->insert_id; // 3. Insert into 'order_items' and Update 'products' stock foreach ($cart_data as $item) { $product_id = intval($item['id']); $quantity_ordered = intval($item['quantity']); $price_at_order = floatval($item['price']); // Insert order item $item_inserted = $wpdb->insert( $order_items_table_name, [ 'order_id' => $new_order_id, 'product_id' => $product_id, 'quantity' => $quantity_ordered, 'price_at_order' => $price_at_order ], [ '%d', '%d', '%d', '%f' ] ); if (!$item_inserted) { throw new Exception("Failed to record order item for product ID {$product_id}: " . $wpdb->last_error); } // *** Use in_stock for update *** $stock_updated = $wpdb->query($wpdb->prepare( "UPDATE $product_table_name SET in_stock = in_stock - %d WHERE id = %d AND in_stock >= %d", $quantity_ordered, $product_id, $quantity_ordered // Ensure stock hasn't gone below ordered amount since check )); if ($stock_updated === false || $stock_updated !== 1) { throw new Exception("Failed to update stock for product ID {$product_id}. Concurrency issue or stock changed."); } } // If all successful, commit the transaction $wpdb->query('COMMIT'); // Prepare data for receipt if paid $receipt_data = null; if ($status === 'Paid') { $receipt_data = [ 'orderId' => $new_order_id, 'date' => current_time('mysql'), 'items' => $cart_data, // Pass cart back for receipt generation 'total' => $total_amount, 'tendered' => $amount_tendered, 'change' => $change_given ]; } wp_send_json_success([ 'message' => 'Order processed successfully!', 'orderId' => $new_order_id, 'status' => $status, 'receiptData' => $receipt_data // Send receipt data only if paid ]); } catch (Exception $e) { $wpdb->query('ROLLBACK'); // Rollback on any error error_log("Order Processing Error: " . $e->getMessage()); // Log detailed error wp_send_json_error(['message' => 'Order failed: ' . $e->getMessage()]); // Send error to client } // wp_die(); // Included in wp_send_json_* } // --- End AJAX Handler --- // --- Fetch Data for Initial Load --- $categories = []; $products = []; $initial_load_error = ''; // Fetch Categories if($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $category_table_name)) == $category_table_name) { $categories = $wpdb->get_results("SELECT id, name FROM $category_table_name ORDER BY name ASC", ARRAY_A); // Use correct table name } else { $initial_load_error .= "Error: Categories table '{$category_table_name}' not found. "; } // Fetch All Products if($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $product_table_name)) == $product_table_name) { // *** MODIFIED: Select 'in_stock' instead of 'quantity' *** $products = $wpdb->get_results( "SELECT id, name, category, selling_price, in_stock, image_url FROM {$product_table_name} ORDER BY category ASC, name ASC", ARRAY_A ); // *** END MODIFICATION *** } else { $initial_load_error .= "Error: Products table '{$product_table_name}' not found."; } // Get currency symbol $currency_symbol = function_exists('get_woocommerce_currency_symbol') ? get_woocommerce_currency_symbol() : '₱'; ?> Order Management

Error loading initial data:

Product List

0 ? 'low-stock' : ''; ?>

Stock:

No products found.

Order List

Qty	Product	Unit Price	Amount
Click products to add them to the order
Total			0.00

Receipt

Order ID:

Date:

Qty	Item	Price	Amount
Total:
Tendered:
Change:

Thank you!

Product List

Order List

Process Payment

Receipt