How to Show a Warning if a Customer's Credit Card is About to Expire in WooCommerce

Many payments in WooCommerce just fail because a credit card wasn’t updated recently when WooCommerce Subscriptions or another plugin tried to bill the account. It’s better if this issue is avoided whenever possible.

It’s way better to inform your customers that their card is about to expire.

I tried several WooCommerce methods and functions to pull the expiration month and year from an order but none of them worked, so unfortunately I had to use a direct SQL query and pull the info for the database and not via official functions. If any of the readers knows a better way to do this let me know. It seems WooCommerce stores the expiration month and year in these db tables: DB_PREFIX_woocommerce_payment_tokens and DB_PREFIX_woocommerce_payment_tokenmeta

The woocommerce_payment_tokenmeta table is more interesting as it contains the expiry_month and expiry_year information for a given payment payment method.

Note: not all payment gateways support so called tokenization where the credit card is stored.

What is Credit Card Tokenization?

Credit card tokenization is a security method used to protect your credit card information. When you enter your credit card number on a website or app, tokenization replaces your real credit card number with a random set of numbers and letters. This replacement is called a "token". The token is like a secret code. It can be used by the website or app to process your payments, but it doesn't reveal your actual credit card number.

Showing the message

The code below can be inserted into functions.php or in a custom WordPress plugin. Always backup the files before editing them! You can duplicate them under a different name but keep the same extension though and/or download them on your computer.

Do make sure you only copy the code without the opening php tag <?php if it's appending to an existing file or keep it if you're adding to a brand new file.

The code will start displaying an error message wen the credit card is about to expire in fewer than 90 days or if it has expired already.

<?php

/**
 * This is part of a tutorial https://orbisius.com/7667 that shows show to show a message when customer's credit card is about to expire.
 * @author Svetoslav Marinov | https://orbisius.com
 */
class Orb_7667_CC_Reminder_Util
{
    /**
     * Orb_7667_CC_Reminder_Util::getUserCards();
     * Get an array of card information for a given user.
     *
     * @param int $user_id User ID for whom to retrieve card info.
     * @return array Array of card information or false on failure.
     */
    public static function getUserCards($user_id = 0)
    {
        global $wpdb;

        $user_id = empty($user_id) ? get_current_user_id() : (int)$user_id;

        // Define the table names with proper prefixes
        $payment_tokens_table = $wpdb->prefix . 'woocommerce_payment_tokens';
        $payment_tokenmeta_table = $wpdb->prefix . 'woocommerce_payment_tokenmeta';

        // Prepare the SQL query to join the tables and retrieve card info
        $sql = $wpdb->prepare(
            "SELECT pt.*, ptm.meta_key, ptm.meta_value
        FROM $payment_tokens_table AS pt
        INNER JOIN $payment_tokenmeta_table AS ptm ON pt.token_id = ptm.payment_token_id
        WHERE pt.user_id = %d",
            $user_id
        );

        // Execute the SQL query
        $results = $wpdb->get_results($sql, ARRAY_A);

        if ($results) {
            $card_info = array();

            foreach ($results as $result) {
                $token_id = $result['token_id'];
                $card_info[$token_id]['gateway_id'] = $result['gateway_id']; // Add gateway_id to the array
                $card_info[$token_id][$result['meta_key']] = $result['meta_value'];
            }

            return $card_info;
        }

        return [];
    }

    /**
     * Orb_7667_CC_Reminder_Util::calcCreditCardExpiration();
     * Check if a credit card is about to expire.
     *
     * @param array $card_info Array of card information.
     * @return array False on error. about to expire, otherwise an array with 'months_remaining', 'days_remaining', and 'expiry_date' keys.
     */
    public static function calcCreditCardExpiration($card_info)
    {
        $month = 0;
        $year = 0;

        $rec = [
            'status' => false,
            'msg' => '',
        ];

        if (!empty($card_info['expiry_month'])) {
            $month = (int)$card_info['expiry_month'];
        } else if (!empty($card_info['month'])) {
            $month = (int)$card_info['month'];
        }

        if (!empty($card_info['expiry_year'])) {
            $year = (int)$card_info['expiry_year'];
        } else if (!empty($card_info['year'])) {
            $year = (int)$card_info['year'];
        }

        if (empty($month) || empty($year)) {
            $rec['msg'] = "Missing month or year";
            return $rec; // Missing expiry information
        }

        // Get the current date and time
        $current_date = current_time('mysql');

        // Create a new date based on expiry_month and expiry_year
        $expiry_date = date('Y-m-t', strtotime($year . '-' . $month));

        // Calculate the difference in days between the current date and expiry date
        $date_diff = strtotime($expiry_date) - strtotime($current_date);
        $days_remaining = floor($date_diff / (24 * 60 * 60)); // Convert seconds to days

        // Calculate the number of months remaining
        $months_remaining = ceil($days_remaining / 30);

        $rec['expiry_date'] = $expiry_date;
        $rec['days_remaining'] = $days_remaining;
        $rec['months_remaining'] = $months_remaining;
        $rec['status'] = true;

        return $rec;
    }

    /**
     * @return void
     */
    public static function maybeShowExpiringCardsNotice() {
        $cards = Orb_7667_CC_Reminder_Util::getUserCards();

        if (empty($cards)) {
            return;
        }

        foreach ($cards as $token_id => $card_rec) {
            $exp_res = Orb_7667_CC_Reminder_Util::calcCreditCardExpiration($card_rec);

            if (!empty($exp_res['status'])) {
                $fmt_date = date('F j, Y', strtotime($exp_res['expiry_date']));

                $error = '';

                if ( empty( $exp_res['days_remaining'] ) || $exp_res['days_remaining'] <= 0 ) {
                    $error = sprintf("Your credit card ending in *%s has expired on %s", $card_rec['last4'], $fmt_date);
                } elseif ( $exp_res['days_remaining'] <= 90 ) {
                    $error = sprintf("Your credit card ending in *%s expires in %d day(s) on %s", $card_rec['last4'], $exp_res['days_remaining'], $fmt_date);
                }

                if (!empty($error)) {
                    $payment_methods_url = wc_get_endpoint_url( 'payment-methods' );

                    echo '<div class="woocommerce-error" role="alert">' . "\n";
                    echo $error;
                    echo sprintf("<br/>Please, go to <a href='%s'>Payment Methods</a> to update your payment details to prevent failed payments.", $payment_methods_url);
                    echo '</div>' . "\n";
                }
            }
        }
    }
}

// this shows a message just above all the WC user account nav (pub side)
add_action('woocommerce_before_account_navigation', 'Orb_7667_CC_Reminder_Util::maybeShowExpiringCardsNotice');

The message this code will show will look similar to this one but the calculated days will be shorter of course