CODEWITHSUNDEEP
phpsqlwordpresswoocommercehook-woocommerce
enc
enc

Reputation: 121

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

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

Upvotes: 1

Views: 492

Answers (2)

mujuonly
mujuonly

Reputation: 11861

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>";
}

Upvotes: 1

Andrii Filenko
Andrii Filenko

Reputation: 984

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.

Upvotes: 1

Related Questions