Automating stock and price updates in WooCommerce
Challenge: Keeping product stock levels synchronized with external inventory sources
Solution: Implementing WooCommerce stock plugin integration that automates the synchronization process
Maintaining accurate and up-to-date stock levels and pricing is crucial in word of Ecommerce. WooCommerce, a powerful and versatile e-commerce platform, offers extensive capabilities for product management. However, the real challenge lies in ensuring that product data remains synchronized with external inventory sources or supplier databases. This article describes technical details of real WooCommerce stock plugin integration.
WooCommerce Integration
At its core, WooCommerce integration involves establishing a connection between the WooCommerce platform and external systems. This connection allows for the continuous flow of data, ensuring that products are synchronized. Whether you’re dealing with a straightforward CSV file or a more complex external system, the fundamental principle remains the same: create a reliable and efficient pipeline for data exchange.
Fetching all woocommerce products
While WordPress provides built-in methods like get_posts() for fetching product data, these can be less efficient when dealing with a large number of products. To optimize performance, we’ll bypass these higher-level functions and interact directly with the database using pure SQL queries. This approach not only speeds up the data retrieval process but also gives us finer control over the data we fetch.
We use the $wpdb global object for direct database access. This allows us to execute raw SQL queries for fetching data. The SQL query is crafted to fetch only the necessary fields: product ID, name, SKU, regular price, and a custom field for the margin (if it exists).
function bioplanet_sync_get_all_product_skus()
{
global $wpdb;
$margin_key = BIOPLANET_PURCHASE_MARGIN_KEY;
$query = "
SELECT p.ID, p.post_title as product_name, pm.meta_value as sku, pm2.meta_value as regular_price, pm3.meta_value as 'oa_margin'
FROM {$wpdb->posts} p
INNER JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id AND pm.meta_key = '_sku'
INNER JOIN {$wpdb->postmeta} pm2 ON p.ID = pm2.post_id AND pm2.meta_key = '_regular_price'
LEFT JOIN {$wpdb->postmeta} pm3 ON p.ID = pm3.post_id AND pm3.meta_key = '{$margin_key}'
WHERE p.post_type = 'product' AND p.post_status = 'publish'
";
$results = $wpdb->get_results($query, ARRAY_A);
$skus = array();
foreach ($results as $row) {
$margin_val = isset($row['oa_margin']) ? $row['oa_margin'] : null;
if (!empty($row['sku'])) {
$skus[$row['sku']] = array(
'id' => $row['ID'],
'product_name' => $row['product_name'],
'regular_price' => $row['regular_price'],
'oa_margin' => $margin_val
);
}
}
return $skus;
}Connecting to an External API for CSV Retrieval
Process of synchronizing: when the data is provided through an API, as is often the case with inventory systems, it’s essential to establish efficient connection. The CSV file’s URL is constructed by appending the API key to the base API URL. The wp_remote_get() function is used to send an HTTP GET request to the API. This function is a part of the WordPress HTTP API, offering a standardized way to send HTTP requests. If the request is successful, the function retrieves the body of the response, which contains the CSV data.
function bioplanet_sync_download_csv()
{
$api_key = get_option('bioplanet_api_key');
if (!$api_key) {
return;
}
$csv_url = BIOPLANET_SYNC_API_URL . $api_key;
$start_time = microtime(true);
$args = array(
'timeout' => 20
);
$response = wp_remote_get($csv_url, $args);
$end_time = microtime(true);
$total_time = $end_time - $start_time;
error_log("BioPlanet Sync : total request time: " . $total_time . " seconds");
if (is_wp_error($response)) {
$error_message = $response->get_error_message();
throw new Exception("Request Error: Can't connect with BioPlanet server. Error: " . $error_message);
}
$csv_data = wp_remote_retrieve_body($response);
return $csv_data;
}Parsing External Data (CSV Files)
Synchronization begins with properly reading and parsing the data from your external sources. This function begins by defining a delimiter, which is used to accurately parse the CSV data. It then processes the CSV line by line, the result is the array of products, each represented as an associative array, ready to be synchronized with your WooCommerce store.
function bioplanet_sync_parse_csv($csv_data)
{
$delimiter = ';';
// Split the CSV data into lines
$lines = explode("\n", $csv_data);
$headers = str_getcsv(array_shift($lines), $delimiter); // Use the defined delimiter
$products = [];
foreach ($lines as $line) {
if (empty($line)) continue; // Skip empty lines
$row = str_getcsv($line, $delimiter); // Use the defined delimiter
if ($row && count($row) == count($headers)) {
$products[] = array_combine($headers, $row);
}
}
return $products;
}Updating Stock Levels and Prices in WooCommerce
The function not only updates stock quantities based on external CSV data but also handles price adjustments, ensuring that your WooCommerce store reflects the most accurate and current information. The use of raw SQL queries allows for bulk updates in a single operation, significantly reducing the overhead compared to individual product updates through standard WooCommerce functions.
We are calculating new prices based on external data and margin settings. Products not present in the external CSV are marked as out of stock in a single operation. Method wc_update_product_lookup_tables_column is a part of WooCommerce’s database management system. By calling these functions after product stock and price updates, the snippet ensures that the WooCommerce product lookup tables reflect the most recent changes.
function bioplanet_sync_update_woocommerce_stock($products)
{
global $wpdb;
$all_skus = bioplanet_sync_get_all_product_skus();
$updated_count = 0;
$instock_count = 0;
$outofstock_count = 0;
$not_in_csv_count = 0;
// Check if WooCommerce Prices Update is enabled
$update_prices = get_option('bioplanet_update_woocommerce_prices') === '1';
foreach ($products as $product_data) {
$sku = $product_data['sku'];
$qty = intval($product_data['qty']);
$marginPercentage = isset($all_skus[$sku]['oa_margin']) ? $all_skus[$sku]['oa_margin'] : null;
$priceAfterDiscountNet = floatval(str_replace(',', '.', $product_data['priceAfterDiscountNet']));
if (!is_null($marginPercentage)) {
$marginPercentage = floatval($marginPercentage / 100);
}
$tax = floatval($product_data['vat']);
if (isset($all_skus[$sku])) {
$product_id = $all_skus[$sku]['id'];
$sql_update = "UPDATE `{$wpdb->prefix}postmeta` SET `meta_value` = CASE ";
$sql_update .= "WHEN `meta_key` = '_stock' THEN '$qty' ";
if ($qty > 0) {
$sql_update .= "WHEN `meta_key` = '_stock_status' THEN 'instock' ";
$instock_count++;
} else {
$sql_update .= "WHEN `meta_key` = '_stock_status' THEN 'outofstock' ";
$outofstock_count++;
}
// Calculate the new prices
if ($update_prices && !is_null($marginPercentage)) {
if ($priceAfterDiscountNet == 0) {
// If the price after discount is zero, set final price to zero
$finalPrice = 0;
} else {
$retail_price = $priceAfterDiscountNet / (1 - $marginPercentage);
$tax_percentage = $tax / 100;
$finalPrice = $retail_price + ($retail_price * $tax_percentage);
$finalPrice = ct_calculate_final_price($finalPrice);
}
// Include the price update in the same SQL statement
$sql_update .= "WHEN `meta_key` = '_price' THEN '" . $finalPrice . "' ";
$sql_update .= "WHEN `meta_key` = '_regular_price' THEN '" . $finalPrice . "' ";
$meta_keys_to_update = array('_stock', '_stock_status', '_price', '_regular_price');
} else {
$meta_keys_to_update = array('_stock', '_stock_status');
}
$sql_update .= "END WHERE `post_id` = $product_id AND `meta_key` IN ('" . implode("', '", $meta_keys_to_update) . "')";
// Execute SQL query
try {
$wpdb->query($sql_update);
$updated_count++;
$tempItem = $all_skus[$sku];
// Remove the SKU from the list as it's found in the CSV
unset($all_skus[$sku]);
// If $finalPrice is defined and equals 0, re-add the saved item to the list
if ($priceAfterDiscountNet == 0) {
$all_skus[$sku] = $tempItem;
}
} catch (Exception $e) {
error_log('Error updating stock: ' . $e->getMessage());
}
}
}
// Handle remaining products (not in CSV) as out of stock
if (!empty($all_skus)) {
$out_of_stock_ids = implode(',', array_map(function ($item) {
return $item['id'];
}, $all_skus));
// Update stock status to 'outofstock' and stock quantity to 0 in one query
$sql_update_stock = "UPDATE `{$wpdb->prefix}postmeta`
SET `meta_value` = CASE
WHEN `meta_key` = '_stock_status' THEN 'outofstock'
WHEN `meta_key` = '_stock' THEN '0'
END
WHERE `post_id` IN ($out_of_stock_ids)
AND `meta_key` IN ('_stock_status', '_stock')";
try {
$wpdb->query($sql_update_stock);
$not_in_csv_count = count($all_skus);
} catch (Exception $e) {
error_log('Error setting products out of stock: ' . $e->getMessage());
}
}
wc_update_product_lookup_tables_column('stock_quantity');
wc_update_product_lookup_tables_column('stock_status');
wc_update_product_lookup_tables_column('min_max_price');
return [
'updated' => $updated_count,
'instock' => $instock_count,
'out_of_stock' => $outofstock_count,
'not_in_csv' => $not_in_csv_count,
'prices_updated' => json_encode($update_prices)
];
}Handling Synchronization Challenges
In the realm of data synchronization, challenges are inevitable. Monitoring the process and logging the results is important for maintaining system integrity and facilitating troubleshooting. The BioPlanet Sync plugin utilizes the robust Action Scheduler to log the final results of the stock and price update process.
function bioplanet_sync_update_stock_function()
{
$start_time = microtime(true);
$csv_data = bioplanet_sync_download_csv();
if (empty($csv_data)) {
throw new Exception('CSV data is empty. BioPlanet Sync could not update stock.');
}
$products = bioplanet_sync_parse_csv($csv_data);
$stock_update_counts = bioplanet_sync_update_woocommerce_stock($products);
$end_time = microtime(true);
$execution_time = $end_time - $start_time;
$log_message = "Bioplanet update: Execution time: " . $execution_time . " seconds. " .
"Products Updated: " . $stock_update_counts['updated'] . ", " .
"Products Set In Stock: " . $stock_update_counts['instock'] . ", " .
"Products Set Out of Stock (CSV): " . $stock_update_counts['out_of_stock'] . ", " .
"Products Not In CSV (Set Out of Stock): " . $stock_update_counts['not_in_csv'] . ", " .
"Prices updated: " . $stock_update_counts['prices_updated'];
as_enqueue_async_action('bioplanet_sync_log_execution_time', array('message' => $log_message), 'bioplanet-sync-group');
}
add_action('bioplanet_sync_log_execution_time', 'bioplanet_sync_log_execution_time_function');
function bioplanet_sync_log_execution_time_function($log_data)
{
if (isset($log_data['message'])) {
error_log($log_data['message']); // This logs to the PHP error log. Change as needed.
}
}
The Role of Action Scheduler
Action Scheduler is a scalable, robust background processing solution for WordPress. It’s used to queue and execute long-running tasks without impacting page load times. In the context of the BioPlanet Sync plugin, we utilize WooCommerce action scheduler system to synchronize product stocks every 15 minutes.
This function is the main method of the plugin; it handles downloading the CSV data from the external source, parsing it, updating the WooCommerce stock and prices, and logging the results. All these operations are performed in the background.
add_action('init', 'bioplanet_sync_schedule_stock_update');
function bioplanet_sync_schedule_stock_update()
{
if (!as_next_scheduled_action('bioplanet_sync_update_stock_action')) {
as_schedule_recurring_action(strtotime(BIOPLANET_SYNC_INTERVAL_MINUTES . ' minutes'), BIOPLANET_SYNC_INTERVAL_MINUTES * MINUTE_IN_SECONDS, 'bioplanet_sync_update_stock_action', array(), 'bioplanet-sync-group');
}
}
add_action('bioplanet_sync_update_stock_action', 'bioplanet_sync_update_stock_function');In conclusion, by leveraging WooCommerce Action Scheduler, the BioPlanet Sync plugin provides a reliable, efficient, and automated solution to keep your WooCommerce inventory synchronized. This regular and automated synchronization ensures that your store provides a reliable and satisfying shopping experience for your customers.
If you need a good ecommerce marketing agency reach us today!
