How to exclude suborders from a user’s total spend?

MultivendorX

• Author
  Posts
• • March 26, 2024 at 7:04 PM #218527
    l.gan

    Participant

    Hello

    I have a code that shows the user’s current total spend of all orders that are “Completed”, and displays content based on his total spend. It works great, but unfortunately, suborders are also counted in this total span.

    Please tell me how to correct the code to exclude suborders?

  • March 26, 2024 at 7:04 PM #218528
    l.gan

    Participant

    This reply has been marked as private.
  • March 26, 2024 at 7:32 PM #218534
    Sangita Support Squad

    Keymaster

    @l.gan, our team is looking into this, kindly provide them some time.

    • May 11, 2024 at 9:29 PM #219827
      l.gan

      Participant

      Hello, please tell me how to exclude suborders from the total spread? #218528

  • May 11, 2024 at 9:37 PM #219828
    Sangita Support Squad

    Keymaster

    It is really difficult for us to look into your custom code and provide you with a solution. So kindly let us know exactly what flow are you looking for on your end so that we can assist you accordingly.

    • May 11, 2024 at 9:48 PM #219829
      l.gan

      Participant

      This reply has been marked as private.
  • May 11, 2024 at 10:07 PM #219830
    Sangita Support Squad

    Keymaster

    Thanks for sharing the details with us. Kindly give us some time to check this and get back to you accordingly.

    Also our replies might get a little delayed as we are closed during the weekends.

  • May 13, 2024 at 5:53 PM #219893
    Sangita Support Squad

    Keymaster

    Hello There, Our team has added some modification in the code. Kindly add the modified code from below and check whether everything works accordingly –

    
    // Utility function: Get customer orders total amount from specific status on a period
    function get_orders_sum_amount_for( $user_id, $period = '2 year', $status = 'completed' ) {
        global $wpdb;
        return $wpdb->get_var( $wpdb->prepare( "
            SELECT SUM(total_amount)
            FROM {$wpdb->prefix}wc_orders
            WHERE status = %s
            AND customer_id = %d
            AND parent_order_id = %d
            AND date_created_gmt >= %s
        ", 'wc-'.$status, $user_id, 0, date('Y-m-d H:i:s', strtotime('-'.$period) ) ) );
    }
    add_shortcode('user_year_total_spent', 'get_user_year_total_spent');
    function get_user_year_total_spent( $atts ) {
        extract( shortcode_atts( array(
            'user_id' => get_current_user_id(),
        ), $atts, 'user_year_total_spent' ) );
    
        if( ! $user_id ) return;
    
        $total_spent = get_orders_sum_amount_for( $user_id ); // Get total spent amount
    
        if( $total_spent > 5000 && $total_spent < 1000 ){
                $text = __( '', 'woocommerce' );
            }elseif( $total_spent > 10001 && $total_spent < 15000){
                $text = __( '', 'woocommerce' );
            }elseif( $total_spent > 15001){
                $text = __( '', 'woocommerce' );
            }else{
                $text = __( '', 'woocommerce' );
            }
            
        return sprintf(__(' %s. %s', 'woocommerce'), wc_price($total_spent), $text);
    }
    

    Copy
    • May 13, 2024 at 7:35 PM #219895
      l.gan

      Participant

      This reply has been marked as private.
  • May 13, 2024 at 7:41 PM #219896
    Sangita Support Squad

    Keymaster

    Great to hear that our modified code works fine for you.

    Now regarding the new modification, kindly give us some time to check this and get back to you accordingly.

  • May 14, 2024 at 5:52 PM #219948
    Sangita Support Squad

    Keymaster

    Hello There, You may use the below shared modified code on your end and check whether everything works accordingly for you –

    
    function get_orders_sum_amount_for( $user_id, $period = '1 year', $status = 'completed' ) {
        global $wpdb;
    
        $date   = date('Y-m-d H:i:s', strtotime('-'.$period) );
        $status = 'wc-'.$status;
    
        $total_orders = (float) $wpdb->get_var( $wpdb->prepare( "
            SELECT SUM(total_amount) FROM {$wpdb->prefix}wc_orders
            WHERE status = %s AND customer_id = %d AND parent_order_id = %d AND date_created_gmt >= %s
        ", $status, $user_id, $date ) );
    
        $total_refunds = (float) $wpdb->get_var( $wpdb->prepare( "
            SELECT SUM(r.total_amount)
            FROM {$wpdb->prefix}wc_orders r
            LEFT JOIN {$wpdb->prefix}wc_orders o ON r.parent_order_id = 0
            WHERE r.type = 'shop_order_refund' AND r.status = %s
            AND o.customer_id = %d AND o.date_created_gmt >= %s
        ", $status, $user_id, $date) );
    
        return $total_orders + $total_refunds;
    }
    
    

    Copy

    Let us know if you need any further assistance from our end.

• Author
  Posts