skip to Main Content

I try to count how many products were sold each week from Friday to Friday.
I try to use meta_key total_sales, but it shows the total amount of products sold.
I want to create a report that shows what products and how many were sold in one week.
I wanted to specify the name of the product and the number of units sold for each example:
Butter 20 sold
Bread B 20 sold
e.t.c

        $sql_total_sold=("SELECT p.post_title as product, pm.meta_value as total_sales FROM {$wpdb->posts} AS p LEFT JOIN {$wpdb->postmeta} AS pm ON (p.ID = pm.post_id AND pm.meta_key LIKE 'total_sales') WHERE pm.meta_value >0 AND p.post_type LIKE 'product' AND p.post_status LIKE 'publish'");
        $results = $wpdb->get_results($sql_total_sold);
        $counter=0;
        foreach($results as $result){
            $counter++;
            echo $counter.' Product name :  '.$result->product.'quantity'. $result->total_sales .'</br>';
        }

2

Answers


  1. Display last week total sales ( number of products sold )

    add_action( 'woocommerce_single_product_summary', 'wc_product_sold_count_last_week', 11 );
    
    function wc_product_sold_count_last_week() {
    
       //  Get last week orders
       $all_orders = wc_get_orders(
          array(
             'limit' => -1,
             'status' => array_map( 'wc_get_order_status_name', wc_get_is_paid_statuses() ),
             'date_after' => date( 'Y-m-d', strtotime( '-1 week' ) ),
             'return' => 'ids',
          )
       );
    
       // Sum quantities purchased
    
       $count = 0;
       $produt_sale_list = array();
       foreach ( $all_orders as $all_order ) {
          $order = wc_get_order( $all_order );
          $items = $order->get_items();
          foreach ( $items as $item ) {
    
                $produt_sale_list[$item->get_name()][] = $item['qty'];
                $count = $count + absint( $item['qty'] ); 
    
          }
       }
    
       foreach ( $produt_sale_list as $produt_sold => $value ) {
           echo "<p>$produt_sold sold: ".array_sum($value)."</p>";
    
       }
       if ( $count > 0 ) echo "<p>Recent sales: $count</p>";
    }
    
    Login or Signup to reply.
  2. Try to change your query to something like:

    SELECT 
        p.post_title AS product, SUM(pm.meta_value) AS total_sales
    FROM
        {$wpdb->posts} AS p
            LEFT JOIN
        {$wpdb->postmeta} AS pm ON (p.ID = pm.post_id
            AND pm.meta_key LIKE 'total_sales')
    WHERE
        pm.meta_value > 0
            AND p.post_type LIKE 'product'
            AND p.post_status LIKE 'publish'
    GROUP BY UNIX_TIMESTAMP(p.sale_date) DIV 604800, product
    

    But I am not sure if it is going to work correct with summer time.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search