dmasin

I see, great, that works perfectly. Regards David

Hi Oscar,
I tested it now and it seems to work directly with your plugin (without my additional peace of code which I presented earlier in this post).
Well, it did not work straight away on my website, probably because my theme is using some outdated woocommerce customisation, but I tried to switch to most recent Storefront on my local install and it works.
Thank you for taking care
David

Hi,
My website is rockngemshow.com, but outside Czech Republic the problem will not be seen.
I use USD in the US, so you could see that problem in the US or using US proxy, but it will be quite minor as the exchange rate is similar.
David

Minor update, I did not realise that local prices are not labelled by country codes in the database, but by the region codes (I am using country codes as region names in my case, so this is why it worked for me). Fix is simple, attached below. David

add_filter('woocommerce_get_catalog_ordering_args', 'custom_products_order_order', 999);
function custom_products_order_order($args) {
	global $wpdb;
	if ($args['meta_key']=='_price' && $args['orderby']=='meta_value_num') {
            add_filter('posts_clauses', 'custom_order_by_price', 999);
	}
	return $args;
}
function custom_order_by_price($args) {
	global $wpdb;

        $customer_country=WC()->customer->get_country();
        $regions = get_option( 'wc_price_based_country_regions', array() );
        foreach($regions as $key=>$region) {
            $countries_in_region=$region['countries'];
            if( in_array( $customer_country, $countries_in_region ) ) {
                $exchange_rate=$region['exchange_rate'];
                $regionkey=$key;
            }
        }

        if ( isset($regionkey) ) {
            $local_price='_'.strtolower($regionkey).'_price';
            $local_price_method=$local_price.'_method';

            if ( isset( $_SERVER['QUERY_STRING'] ) ) {
		parse_str( $_SERVER['QUERY_STRING'], $params );
            }
            $order = ! empty( $params['product_order'] ) ? $params['product_order'] : 'asc';
            $order = strtoupper( $order );

            $args['join'] .= "
                LEFT JOIN $wpdb->postmeta AS wc_price ON ($wpdb->posts.ID = wc_price.post_id AND wc_price.meta_key = '_price')
                LEFT JOIN $wpdb->postmeta AS wc_price_local ON ($wpdb->posts.ID = wc_price_local.post_id AND wc_price_local.meta_key = '$local_price')
                LEFT JOIN $wpdb->postmeta AS wc_price_local_method ON ($wpdb->posts.ID = wc_price_local_method.post_id AND wc_price_local_method.meta_key = '$local_price_method')
            ";

            $args['orderby'] = "
                IF(wc_price_local_method.meta_value LIKE 'manual',
                    wc_price_local.meta_value+0,
                    wc_price.meta_value * $exchange_rate
                )
            $order";
        }
	remove_filter('posts_clauses', 'custom_order_by_price');
	return $args;
}

Hi,

I finally resolved this issue partially, I modified the code from “WooCommerce Product Price Based on Countries”, which supports price filter. The code is to be included into class-wcj-price-by-country-core.php. I wrote it specifically for my site rockngemshow.com, so it does not work in general (one global and one local currency, max and min fixed, but different for each currency). It would be great if jetpack developers could generalize the code and input it into plugin, woocommerce price filter is great widget but with price-by-country it becomes completely useless.

Regards,
David

This goes into function add_hooks()

add_filter( ‘woocommerce_price_filter_results’, array( $this , ‘price_filter_results’ ), 10, 3 );
add_filter( ‘woocommerce_price_filter_widget_min_amount’, array( $this , ‘price_filter_widget_min_amount’ ) );
add_filter( ‘woocommerce_price_filter_widget_max_amount’, array( $this , ‘price_filter_widget_max_amount’ ) );

These are new functions in class WCJ_Price_by_Country_Core

/**
* Return matched produts where price between min and max
*
* @param array $matched_products_query
* @param int $min
* @param int $max
* @return array
*/
public function price_filter_results( $matched_products_query, $min, $max ){

global $wpdb;

$country_exchange_rate = 1;
$group_id = $this->get_customer_country_group_id();
if ( null == $group_id ) $group_id=-1;
if ( $group_id > 0 ) $country_exchange_rate = get_option( ‘wcj_price_by_country_exchange_rate_group_’ . $group_id, 1 );

if($group_id < 0 ) {if($max==200) $max=1000000;}
else if ($group_id == 1 ) {if($max==5000) $max=1000000;}

$sql = $wpdb->prepare(‘SELECT DISTINCT ID, post_parent, post_type FROM %1$s
INNER JOIN %2$s wc_price ON ID = wc_price.post_id AND wc_price.meta_key = “_price”
INNER JOIN %2$s wc_price_local_1 ON ID = wc_price_local_1.post_id AND wc_price_local_1.meta_key = “_wcj_price_by_country_regular_price_local_1”
WHERE post_type IN ( “product”, “product_variation” ) AND
IF(%6$d < 0,
wc_price.meta_value BETWEEN %4$d AND %5$d,
IF(wc_price_local_1.meta_value > 1,
wc_price_local_1.meta_value BETWEEN %4$d AND %5$d,
wc_price.meta_value * %3$d BETWEEN %4$d AND %5$d
)
)’
, $wpdb->posts, $wpdb->postmeta, $country_exchange_rate, $min, $max, $group_id);

$matched_products_query = $wpdb->get_results( $sql, OBJECT_K );

return $matched_products_query;
}

/**
* Filter for price_filter_widget_min_amount
* @param $amount Min amount
*/
public function price_filter_widget_min_amount( $amount ) {
$amount = 0;
return $amount;
}

/**
* Filter for price_filter_widget_max_amount
* @param $amount Max amount
*/
public function price_filter_widget_max_amount( $amount ) {
$group_id = $this->get_customer_country_group_id();
if($group_id == null ) $amount = 200;
else if ($group_id == 1 ) $amount = 5000;

return $amount;
}