Automating WooCommerce product availability date
Challenge: Manual, error-prone updates of wordpress product availability date from Excel or API.
Solution: A plugin automating updates via external API integration.
Managing product availability date efficiently is crucial for any WooCommerce store. Manual updates can be time-consuming and prone to error. This post introduces a WordPress plugin designed to automate this process by integrating with an external API.
Note: To use this self-written plugin effectively, your supplier or wholesale company must provide access to an Excel file with availability dates or an API connection.
Setting Up Scheduled Tasks
Initially, we establish constants for the API URL and the synchronization interval. Following this, we utilize WordPress’s Cron job to orchestrate a recurring action aimed at synchronizing availability data. This involves connecting with the API and updating a custom field for each product with the date when it will be available for sale.
To initialize a background job and run it every 12 hours, we utilize the ‘Action Scheduler’ tool provided by WooCommerce. This tool is helpful for viewing logs and checking when the next job will run.
define('API_AVAILABILITY_URL', 'https://api.example.com/data/products.xlsx');
define('API_AVAILABILITY_INTERVAL_MINUTES', 720);
add_action('init', 'api_sync_schedule_availability');
function api_sync_schedule_availability()
{
if (!as_next_scheduled_action('api_sync_availability_action')) {
as_schedule_recurring_action(strtotime(API_AVAILABILITY_INTERVAL_MINUTES . ' minutes'), API_AVAILABILITY_INTERVAL_MINUTES * MINUTE_IN_SECONDS, 'api_sync_availability_action', array(), 'api-sync-group');
}
}
Fetching Product SKUs
To sync availability, we first retrieve all product SKUs from the WordPress database:
function api_sync_get_all_product_skus()
{
global $wpdb;
$margin_key = MY_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;
}Downloading and Processing API Data
We use wp_remote_get to download the product availability date from the external API, process the Excel file, and update the product metadata accordingly:
add_action('api_sync_availability_action', 'api_sync_availability_function');
function api_sync_availability_function()
{
$start_time = microtime(true);
$api_data = api_availability_download_xlsx();
if (empty($api_data)) {
throw new Exception('CSV data is empty. Api availability can not run.');
}
$results_stats = api_bulk_availability_processing($api_data);
$end_time = microtime(true);
$execution_time = $end_time - $start_time;
$log_message = "API Availability: Execution time: " . $execution_time . " seconds. " .
"Records Updated: " . $results_stats['updated'] . ", " .
"Records added: " . $results_stats['added'] . " ";
as_enqueue_async_action('api_sync_log_execution_time', array('message' => $log_message), 'api-sync-group');
}Reading an excel file to get product availability date
To read an Excel file, we use a third-party PHP library. First, install the library using Composer:
composer require phpoffice/phpspreadsheetThe Excel file is in xlsx format and comprises three columns: EAN, name, and product availability date.
Then, we fetch the file, process it, and return the contents as an array:
function api_availability_download_xlsx()
{
$xlsx_url = API_AVAILABILITY_URL; // Ensure this URL points to the .xlsx file
$start_time = microtime(true);
$args = array(
'timeout' => 20
);
$response = wp_remote_get($xlsx_url, $args);
$end_time = microtime(true);
$total_time = $end_time - $start_time;
error_log("Api Availability: 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 Api server. Error: " . $error_message);
}
$response_code = wp_remote_retrieve_response_code($response);
if ($response_code != 200) {
throw new Exception("Server Error: Api server returned an error. Status code: $response_code");
}
$content_type = wp_remote_retrieve_header($response, 'content-type');
// Adjust content-type check for Excel files
if (strpos($content_type, 'application/octet-stream') === false && strpos($content_type, 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') === false) {
throw new Exception("Unexpected Content-Type: received $content_type");
}
$xlsx_data = wp_remote_retrieve_body($response);
if (empty($xlsx_data)) {
throw new Exception("No Excel data received.");
}
// Save the Excel data to a temporary file
$temp_file = tmpfile();
fwrite($temp_file, $xlsx_data);
$temp_file_path = stream_get_meta_data($temp_file)['uri'];
// Load the Excel file with PhpSpreadsheet
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReaderForFile($temp_file_path);
$spreadsheet = $reader->load($temp_file_path);
// Optionally, convert the data to CSV or process it as needed
// This is a simple example of reading the first sheet's data
$sheetData = $spreadsheet->getActiveSheet()->toArray();
// Close the temp file
fclose($temp_file);
// Return the data or further process as needed
return $sheetData;
}Bulk products availability date update in Woocommerce
With an array containing availability dates, we can perform a bulk update on the database. Handling updates and inserts separately ensures that existing records are accurately updated without creating duplicate entries.
The function api_bulk_availability_processing starts by clearing the ct_availability_date meta value for all products. After preparing the ground, the function moves on to handle the data received from the API. It checks the SKU (Stock Keeping Unit) of each product against the SKUs that have already been gathered and then saves this data into the database.
Regarding logging results, the plugin employs the hook ‘api_sync_log_execution_time’ to log the outcome of each synchronization process. This hook captures detailed information about the execution, including the duration and the number of records updated or added. For instance, a typical log message might read: ‘API Availability: Execution time: 0.33835792541504 seconds. Records Updated: 189, Records added: 0.’
function api_bulk_availability_processing($api_data) {
global $wpdb;
// step 1
$clear_sql = "UPDATE {$wpdb->postmeta} SET meta_value = '' WHERE meta_key = 'ct_availability_date'";
$wpdb->query($clear_sql);
// Fetch all SKUs and associated product details
$all_skus = api_sync_get_all_product_skus();
// Get existing meta_key entries for 'ct_availability_date'
$existing_meta_keys = $wpdb->get_results("SELECT post_id FROM {$wpdb->postmeta} WHERE meta_key = 'ct_availability_date'", ARRAY_A);
$existing_post_ids = array_map(function($item) { return $item['post_id']; }, $existing_meta_keys);
$updates = []; // To store update data
$inserts = []; // To store insert data
foreach ($api_data as $product_data) {
$product_sku = $product_data[0];
$availability_date = $product_data[2];
if (isset($all_skus[$product_sku])) {
$product_id = $all_skus[$product_sku]['id'];
$prepared_data = $wpdb->prepare("(%d, 'ct_availability_date', %s)", $product_id, $availability_date);
if (in_array($product_id, $existing_post_ids)) {
// Prepare update data
$updates[] = $product_data;
} else {
// Prepare insert data
$inserts[] = $prepared_data;
}
}
}
// Execute updates
$rows_updated = 0;
if (!empty($updates)) {
$rows_updated = api_availability_update($updates, $all_skus);
}
$rows_added = 0;
// Execute inserts in a single query
if (!empty($inserts)) {
$values_sql = implode(',', $inserts);
$sql = "INSERT INTO {$wpdb->postmeta} (post_id, meta_key, meta_value) VALUES $values_sql";
$wpdb->query($sql);
$rows_added = $wpdb->rows_affected;
}
return [
'updated' => $rows_updated,
'added' => $rows_added
];
}
function api_availability_update($api_data, $all_skus) {
global $wpdb;
// Initialize variables for storing update components
$caseParts = [];
$updateValues = []; // Stores all values for the prepared statement
$updatePostIds = []; // Stores post IDs to ensure uniqueness for the IN clause
foreach ($api_data as $product_data) {
$product_sku = $product_data[0];
$availability_date = $product_data[2];
// Check if the product SKU exists in the all_skus array
if (isset($all_skus[$product_sku])) {
$product_id = $all_skus[$product_sku]['id'];
// Prepare the CASE part and values for this product
$caseParts[] = "WHEN %d THEN %s";
$updateValues[] = $product_id; // For the CASE condition
$updateValues[] = $availability_date; // New value for meta_value
// Track which post IDs we're updating to build the IN clause
if (!in_array($product_id, $updatePostIds)) {
$updatePostIds[] = $product_id;
}
}
}
$rows_affected = 0;
if (!empty($caseParts)) {
// Construct the CASE statement part of the SQL query
$caseSql = implode(' ', $caseParts);
// Add the post IDs for the IN clause at the end of the update values
$updateValues = array_merge($updateValues, $updatePostIds);
// Prepare the SQL with placeholders for the IN clause
$inPlaceholder = implode(',', array_fill(0, count($updatePostIds), '%d'));
// Construct the full SQL statement
$sql = "UPDATE {$wpdb->postmeta} SET meta_value = CASE post_id $caseSql END WHERE meta_key = 'ct_availability_date' AND post_id IN ($inPlaceholder)";
// Prepare and execute the SQL statement
$preparedSql = $wpdb->prepare($sql, $updateValues);
$wpdb->query($preparedSql);
$rows_affected = $wpdb->rows_affected;
}
return $rows_affected;
}Displaying Product Availability
Finally, we enhance the customer experience by displaying the availability date on product pages for items that are out of stock:
add_action('woocommerce_product_meta_start', 'display_product_availability_date', 20);
function display_product_availability_date() {
global $post;
// Get the product object
$product = wc_get_product($post->ID);
$availability_date = get_post_meta($post->ID, 'ct_availability_date', true);
// Check if the product is out of stock and the custom field has a value, then display it
if (!empty($availability_date) && !$product->is_in_stock()) {
echo '<div class="product-availability-date">Availability Date: ' . esc_html($availability_date) . '</div>';
}
}Conclusion
This plugin automates the task of updating product availability, reducing the risk of errors and saving valuable time. Developers can customize and extend the plugin based on the unique needs of their WooCommerce store.
If you need help with your e-commerce or need web development company, feel free to reach us!
