Wordpress Stock Update Programatically

Question

I'm trying to develop a plugin that would update the stock quantity of products to 0 (Out of stock) that are older than X days. The logic that I'm using is:

1. Select products who's modofied date is greater than X days (Eg: 10 days)
2. Update the stock quantity to 0 (Out of stock)

SQL Statement :

Select 
t1.id as product_id, 
t1.post_title as product_name,
t1.post_type as product_variant, 
t2.min_price as product_cost, 
t2.stock_quantity as product_stock_qty, 
t2.stock_status as product_stock_status, 
t3.user_nicename as vendor_name, 
t1.post_modified as product_stock_update, 
t4.frequency as frequency 
from 
wp_posts t1 join 
wp_wc_product_meta_lookup  t2 on 
t1.id=t2.product_id join 
wp_users t3 on t3.id=t1.post_author join 
wp_cc_autostockaudit t4 on t4.author_id = t1.post_author 
where DATE(DATE_ADD(t1.post_modified, INTERVAL +  t4.frequency DAY )) <= now() LIMIT 500;

(t4.frequency = 10 days, from my custom table)

Now, with this do a foreach to puck the indivudial elements and trash them to out of stock.

Code:

foreach($productdatas as $productdata){
    $out_of_stock_staus  = 'outofstock';
    $product_id          = $productdata->product_id;
    $product_title       = $productdata->product_name;
    $product_vendor      = $productdata->vendor_name;

echo "About to trash $product_id <br>";
update_post_meta($product_id, '_stock', 0);
update_post_meta( $product_id, '_stock_status', wc_clean( $out_of_stock_staus ) );
wp_set_post_terms( $product_id, 'outofstock', 'product_visibility', true );

My Problem:

1. The products are getting marked as out of stock correctly, but since my SQL is reading the last modified which doesn't get updated, setting a cron job would mess up since this is going to run every time unlimitedly.
2. If I also include one more function to change the post status, wp_update_post(array('ID' => $product_id,'post_status' => 'draft')); , the stock update doesn't work. Based on my working experience on this piece of code, it either updates the products to draft status or trashes the stock to 0 but never does both.

I'm looking for a more reliable way to do this. Can someone please help & Suggest any better way to handle this out?

Version Info: WP : 5.3.2 , Woocommerce: 3.8.1

Answer 1

Wordpress stores three levels of stock information on the wp_postmeta.

1. Each product has manage stock enabled
2. Stock Quantity
3. Stock Status

Since the products on the above questions were imported, few of them didn't had manage stock enabled so updating the stock quantity to 0 didn't work well.

Solution: I forcefully updated the manage stock to be yes and then trashed the quantity to be 0 with status out of stock. Further to achieve the second part, where the select statement pulls the data based on the last modified, I forcefully pushed the product to draft state. This would modify the datetime and would not be involved on the next polling.

update_post_meta($product_id, '_manage_stock','Yes');
update_post_meta($product_id, '_stock', '0');
update_post_meta($product_id, '_stock_status', 'outofstock');

wp_update_post(
        array( 
            'ID' => $product_id, 
            'post_status' => 'draft'
        )
    );