/**
* Find a matching zone for a given package.
* @since 2.6.0
* @uses wc_make_numeric_postcode()
* @param object $package
* @return WC_Shipping_Zone
*/
public static function get_zone_matching_package($package)
{
global $wpdb;
$country = strtoupper(wc_clean($package['destination']['country']));
$state = strtoupper(wc_clean($package['destination']['state']));
$continent = strtoupper(wc_clean(WC()->countries->get_continent_code_for_country($country)));
$postcode = wc_normalize_postcode(wc_clean($package['destination']['postcode']));
$cache_key = WC_Cache_Helper::get_cache_prefix('shipping_zones') . 'wc_shipping_zone_' . md5(sprintf('%s+%s+%s', $country, $state, $postcode));
$matching_zone_id = wp_cache_get($cache_key, 'shipping_zones');
if (false === $matching_zone_id) {
// Work out criteria for our zone search
$criteria = array();
$criteria[] = $wpdb->prepare("( ( location_type = 'country' AND location_code = %s )", $country);
$criteria[] = $wpdb->prepare("OR ( location_type = 'state' AND location_code = %s )", $country . ':' . $state);
$criteria[] = $wpdb->prepare("OR ( location_type = 'continent' AND location_code = %s ) )", $continent);
// Postcode range and wildcard matching
$postcode_locations = $wpdb->get_results("SELECT zone_id, location_code FROM {$wpdb->prefix}woocommerce_shipping_zone_locations WHERE location_type = 'postcode';");
if ($postcode_locations) {
$zone_ids_with_postcode_rules = array_map('absint', wp_list_pluck($postcode_locations, 'zone_id'));
$matches = wc_postcode_location_matcher($postcode, $postcode_locations, 'zone_id', 'location_code');
$do_not_match = array_unique(array_diff($zone_ids_with_postcode_rules, array_keys($matches)));
if (!empty($do_not_match)) {
$criteria[] = "AND zones.zone_id NOT IN (" . implode(',', $do_not_match) . ")";
}
}
// Get matching zones
$matching_zone_id = $wpdb->get_var("\n\t\t\t\tSELECT zones.zone_id FROM {$wpdb->prefix}woocommerce_shipping_zones as zones\n\t\t\t\tLEFT OUTER JOIN {$wpdb->prefix}woocommerce_shipping_zone_locations as locations ON zones.zone_id = locations.zone_id\n\t\t\t\tWHERE " . implode(' ', $criteria) . "\n\t\t\t\tORDER BY zone_order ASC LIMIT 1\n\t\t\t");
wp_cache_set($cache_key, $matching_zone_id, 'shipping_zones');
}
return new WC_Shipping_Zone($matching_zone_id ? $matching_zone_id : 0);
}
/**
* Loop through a set of tax rates and get the matching rates (1 per priority).
*
* @param string $country
* @param string $state
* @param string $postcode
* @param string $city
* @param string $tax_class
* @return array
*/
private static function get_matched_tax_rates($country, $state, $postcode, $city, $tax_class)
{
global $wpdb;
// Query criteria - these will be ANDed
$criteria = array();
$criteria[] = $wpdb->prepare("tax_rate_country IN ( %s, '' )", strtoupper($country));
$criteria[] = $wpdb->prepare("tax_rate_state IN ( %s, '' )", strtoupper($state));
$criteria[] = $wpdb->prepare("tax_rate_class = %s", sanitize_title($tax_class));
// Pre-query postcode ranges for PHP based matching.
$postcode_search = wc_get_wildcard_postcodes($postcode, $country);
$postcode_ranges = $wpdb->get_results("SELECT tax_rate_id, location_code FROM {$wpdb->prefix}woocommerce_tax_rate_locations WHERE location_type = 'postcode' AND location_code LIKE '%...%';");
if ($postcode_ranges) {
$matches = wc_postcode_location_matcher($postcode, $postcode_ranges, 'tax_rate_id', 'location_code', $country);
if (!empty($matches)) {
foreach ($matches as $matched_postcodes) {
$postcode_search = array_merge($postcode_search, $matched_postcodes);
}
}
}
$postcode_search = array_unique($postcode_search);
/**
* Location matching criteria - ORed
* Needs to match:
* - rates with no postcodes and cities
* - rates with a matching postcode and city
* - rates with matching postcode, no city
* - rates with matching city, no postcode
*/
$locations_criteria = array();
$locations_criteria[] = "locations.location_type IS NULL";
$locations_criteria[] = "\n\t\t\tlocations.location_type = 'postcode' AND locations.location_code IN ('" . implode("','", array_map('esc_sql', $postcode_search)) . "')\n\t\t\tAND (\n\t\t\t\t( locations2.location_type = 'city' AND locations2.location_code = '" . esc_sql(strtoupper($city)) . "' )\n\t\t\t\tOR NOT EXISTS (\n\t\t\t\t\tSELECT sub.tax_rate_id FROM {$wpdb->prefix}woocommerce_tax_rate_locations as sub\n\t\t\t\t\tWHERE sub.location_type = 'city'\n\t\t\t\t\tAND sub.tax_rate_id = tax_rates.tax_rate_id\n\t\t\t\t)\n\t\t\t)\n\t\t";
$locations_criteria[] = "\n\t\t\tlocations.location_type = 'city' AND locations.location_code = '" . esc_sql(strtoupper($city)) . "'\n\t\t\tAND NOT EXISTS (\n\t\t\t\tSELECT sub.tax_rate_id FROM {$wpdb->prefix}woocommerce_tax_rate_locations as sub\n\t\t\t\tWHERE sub.location_type = 'postcode'\n\t\t\t\tAND sub.tax_rate_id = tax_rates.tax_rate_id\n\t\t\t)\n\t\t";
$criteria[] = '( ( ' . implode(' ) OR ( ', $locations_criteria) . ' ) )';
$found_rates = $wpdb->get_results("\n\t\t\tSELECT tax_rates.*\n\t\t\tFROM {$wpdb->prefix}woocommerce_tax_rates as tax_rates\n\t\t\tLEFT OUTER JOIN {$wpdb->prefix}woocommerce_tax_rate_locations as locations ON tax_rates.tax_rate_id = locations.tax_rate_id\n\t\t\tLEFT OUTER JOIN {$wpdb->prefix}woocommerce_tax_rate_locations as locations2 ON tax_rates.tax_rate_id = locations2.tax_rate_id\n\t\t\tWHERE 1=1 AND " . implode(' AND ', $criteria) . "\n\t\t\tGROUP BY tax_rate_id\n\t\t\tORDER BY tax_rate_priority\n\t\t");
$found_rates = self::sort_rates($found_rates);
$matched_tax_rates = array();
$found_priority = array();
foreach ($found_rates as $found_rate) {
if (in_array($found_rate->tax_rate_priority, $found_priority)) {
continue;
}
$matched_tax_rates[$found_rate->tax_rate_id] = array('rate' => $found_rate->tax_rate, 'label' => $found_rate->tax_rate_name, 'shipping' => $found_rate->tax_rate_shipping ? 'yes' : 'no', 'compound' => $found_rate->tax_rate_compound ? 'yes' : 'no');
$found_priority[] = $found_rate->tax_rate_priority;
}
return apply_filters('woocommerce_matched_tax_rates', $matched_tax_rates, $country, $state, $postcode, $city, $tax_class);
}
