Delete processing orders after 10 days

Question

I am trying to create a function that should check orders made to WooCommerce store. If the status of an order hasn't been changed from processing to completed within 10days the function should cancel the order.

I have found wpdb where I think I have managed to make a lookup to find orders older than 10days. Could someone point me in the right direction on how to proceed from here? I am assuming I have to create a function that contains WC_Order::update_status() in order to update the order status. I just dunno how to link this all together.

I've been looking through the Wordpress codex and found wpdb, which contains a set of functions used to interact with a database. I am assuming I have to use wpdb in my function but dunno how to proceed from here.

I've also been looking at replacing the native Wordpress cron with a real cron that checks the date and updates the status of orders. However, after some research I thought this was a too complicated way to solve my problem.

function get_processing_expiry() {        
            global $wpdb;

            $processing_expiry = $wpdb->get_col( $wpdb->prepare( "
                    SELECT posts.ID
                    FROM {$wpdb->posts} AS posts
                    WHERE posts.post_status = 'wc-processing'
                    AND posts.post_date < %s
            ", date( 'Y-m-d H:i:s', strtotime('-10 days') ) ) );

            return $processing_expiry;
    }

I am expecting the order status to change from processing to cancel when the order has been processing for more than ten days.

Answer 1

This function will handle it:

function expire_after_x_days(){
        global $wpdb;
    // Get current time
        $today = date("mdy");

    // set time to expire
        $time_to_expire = "-10 days";
        $expiration_date = date("mdy", strtotime( $today . $time_to_expire));

    // Get orders with processing status
        $result = $wpdb->get_results("SELECT * FROM $wpdb->posts WHERE post_type = 'shop_order' AND post_status = 'wc-processing'");

        if( !empty($result)) foreach ($result as $order){
            // Get order's time
            $order_time = get_the_time('mdy', $order->ID );

    // Compare order's time with current time -10 days
        if ( $order_time < $expiration_date ){

    // Update order status    
                $orders = array();
                $orders['ID'] = $order->ID;
                $orders['post_status'] = 'wc-cancelled';
                wp_update_post( $orders );
            }
        }
} 

    // Use the best HOOK for your case 
    add_action( 'admin_footer', 'expire_after_x_days' );

    // OR simply call the function if you are pasting this in your functions.php 

Answer 2

In MySQL you have a lot of date and time functions to check if a record is older than 10 days for example. For this you can use INTERVAL something like this:

SELECT posts.ID
FROM {$wpdb->posts} AS posts
WHERE posts.post_status = 'wc-processing'
AND posts.post_date < NOW() - INTERVAL 10 DAY

Then you can use only SQL to solve it.

https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

Answer 3

You can achieve this without even using WP, You need to work with MySQL Scheduled Events, to do that you need to create a stored procedure Cancel_Orders() having logic to delete or cancel orders and schedule an event every day or some interval.

CREATE EVENT Cencel_Orders_Event
ON SCHEDULE EVERY 5 SECOND
DO
CALL Cancel_Orders();

refer to http://www.mysqltutorial.org/mysql-triggers/working-mysql-scheduled-event/