Woocommerce Question posted in
The official Woocommerce documentation can be found here.

How to count how many products were sold each week from Friday to Friday – Woocommerce

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>';
        }
Tags:

2

Answers


  1. 0

    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. 0

    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