I am adding a plugin that adds a custom meta box on the admin woocommerce product detail page. What this does it lists the completed orders that contain the product. I was able to successfully add that box with the data. However, I want to add a filter button with two date pickers to filter out the data. I've been looking at the meta box examples and they save data to the record. In my case though, I'm not saving data, just returning data that exists. How would I go into accomplishing that?

Update: Here is the code I have currently:

function wporg_add_custom_box()
{
    $screens = ['product'];
    foreach ($screens as $screen) {
        add_meta_box(
            'wporg_box_id',           // Unique ID
            'Orders that purchased this product',  // Box title
            'wporg_custom_box_html',  // Content callback, must be of type callable
            $screen                   // Post type
        );
    }
}
add_action('add_meta_boxes', 'wporg_add_custom_box');

function wporg_custom_box_html($post)
{
?>
    <table>
        <tr>
            <td>Order ID</td>
            <td>Name</td>
            <td>Email</td>
            <td>Item Name</td>
            <td>Date Created</td>

        </tr>
    <?php
        // Access WordPress database
        global $wpdb;

        // Select Product ID
        $product_id = $post->ID;

        // Get the orders that bought the product
        // Only get those that are paid
        $statuses = array_map( 'esc_sql', wc_get_is_paid_statuses() );
        $orders = $wpdb->get_col("
           SELECT DISTINCT p.ID FROM {$wpdb->posts} AS p
           INNER JOIN {$wpdb->postmeta} AS pm ON p.ID = pm.post_id
           INNER JOIN {$wpdb->prefix}woocommerce_order_items AS i ON p.ID = i.order_id
           INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS im ON i.order_item_id = im.order_item_id
           WHERE p.post_status IN ( 'wc-" . implode( "','wc-", $statuses ) . "' )
           AND im.meta_key IN ( '_product_id')
           AND im.meta_value = $product_id
        ");


        $i = 0;
        $arrayLength = count($orders);
        if ($arrayLength<=0)
        {
        echo '<tr><td colspan="5">No one bought this yet.</td></tr>';
        }
        else
        {
            while ($i < $arrayLength)
                {
                    //echo "<tr><td>". $orders[$i] ."</td></tr>";
                    $order = wc_get_order($orders[$i]);
                    foreach ($order->get_items() as $item_key => $item ){
                        //the variable stores it as string whereas the object returns it as integer
                        if ($item->get_product_id() === (int)$product_id){
                            echo "<tr><td>".$orders[$i]."</td><td>". $order->get_billing_first_name() . " " . $order->get_billing_last_name() ."</td><td>". $order->get_billing_email() ."</td><td>". $item->get_name() ."</td><td>". $order->get_date_created()->date('m-d-Y H:i:s') ."</td></tr>";

                        }
                    }

                    $i++;
                }   
        }

    ?>
    <table>
<?php
}
?>

I am adding a plugin that adds a custom meta box on the admin woocommerce product detail page. What this does it lists the completed orders that contain the product. I was able to successfully add that box with the data. However, I want to add a filter button with two date pickers to filter out the data. I've been looking at the meta box examples and they save data to the record. In my case though, I'm not saving data, just returning data that exists. How would I go into accomplishing that?

Update: Here is the code I have currently:

function wporg_add_custom_box()
{
    $screens = ['product'];
    foreach ($screens as $screen) {
        add_meta_box(
            'wporg_box_id',           // Unique ID
            'Orders that purchased this product',  // Box title
            'wporg_custom_box_html',  // Content callback, must be of type callable
            $screen                   // Post type
        );
    }
}
add_action('add_meta_boxes', 'wporg_add_custom_box');

function wporg_custom_box_html($post)
{
?>
    <table>
        <tr>
            <td>Order ID</td>
            <td>Name</td>
            <td>Email</td>
            <td>Item Name</td>
            <td>Date Created</td>

        </tr>
    <?php
        // Access WordPress database
        global $wpdb;

        // Select Product ID
        $product_id = $post->ID;

        // Get the orders that bought the product
        // Only get those that are paid
        $statuses = array_map( 'esc_sql', wc_get_is_paid_statuses() );
        $orders = $wpdb->get_col("
           SELECT DISTINCT p.ID FROM {$wpdb->posts} AS p
           INNER JOIN {$wpdb->postmeta} AS pm ON p.ID = pm.post_id
           INNER JOIN {$wpdb->prefix}woocommerce_order_items AS i ON p.ID = i.order_id
           INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta AS im ON i.order_item_id = im.order_item_id
           WHERE p.post_status IN ( 'wc-" . implode( "','wc-", $statuses ) . "' )
           AND im.meta_key IN ( '_product_id')
           AND im.meta_value = $product_id
        ");


        $i = 0;
        $arrayLength = count($orders);
        if ($arrayLength<=0)
        {
        echo '<tr><td colspan="5">No one bought this yet.</td></tr>';
        }
        else
        {
            while ($i < $arrayLength)
                {
                    //echo "<tr><td>". $orders[$i] ."</td></tr>";
                    $order = wc_get_order($orders[$i]);
                    foreach ($order->get_items() as $item_key => $item ){
                        //the variable stores it as string whereas the object returns it as integer
                        if ($item->get_product_id() === (int)$product_id){
                            echo "<tr><td>".$orders[$i]."</td><td>". $order->get_billing_first_name() . " " . $order->get_billing_last_name() ."</td><td>". $order->get_billing_email() ."</td><td>". $item->get_name() ."</td><td>". $order->get_date_created()->date('m-d-Y H:i:s') ."</td></tr>";

                        }
                    }

                    $i++;
                }   
        }

    ?>
    <table>
<?php
}
?>

• woocommerce-offtopic
• query
• search
• metabox

• Can you add the code you have already to the question? – BenB Commented Jan 28, 2020 at 3:32
• @BenB - I modified my post with my code. – pmb88 Commented Jan 29, 2020 at 15:09

1 Answer 1

To add a date picker to your code, you should load set jQuery date picker which included in WordPress to load in the product admin page and write some custom JS code which will allow to filter the products by the dates picked.

You can load all the products and then filter them with code, or load via Ajax only the products between specific dates. If you have many products that Ajax approach will be better to prevent long load time.

Here you can find how to load the jQuery UI date picker: https://stackoverflow/a/27463687/2011434

Here you can find a few ways to enqueue the script in the product page How do I Enqueue styles/scripts on Certain /wp-admin Pages?

You can see here example how to perform a Ajax call to admin Ajax https://wordpress.stackexchange/a/112717/62909