/**
* 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);
}
/**
* Used by shipping zones and taxes to compare a given $postcode to stored
* postcodes to find matches for numerical ranges, and wildcards.
* @since 2.6.0
* @param string $postcode Postcode you want to match against stored postcodes
* @param array $objects Array of postcode objects from Database
* @param string $object_id_key DB column name for the ID.
* @param string $object_compare_key DB column name for the value.
* @param string $country Country from which this postcode belongs. Allows for formatting.
* @return array Array of matching object ID and matching values.
*/
function wc_postcode_location_matcher($postcode, $objects, $object_id_key, $object_compare_key, $country = '')
{
$postcode = wc_normalize_postcode($postcode);
$wildcard_postcodes = array_map('wc_clean', wc_get_wildcard_postcodes($postcode, $country));
$matches = array();
foreach ($objects as $object) {
$object_id = $object->{$object_id_key};
$compare_against = $object->{$object_compare_key};
// Handle postcodes containing ranges.
if (strstr($compare_against, '...')) {
$range = array_map('trim', explode('...', $compare_against));
if (2 !== sizeof($range)) {
continue;
}
list($min, $max) = $range;
// If the postcode is non-numeric, make it numeric.
if (!is_numeric($min) || !is_numeric($max)) {
$compare = wc_make_numeric_postcode($postcode);
$min = str_pad(wc_make_numeric_postcode($min), strlen($compare), '0');
$max = str_pad(wc_make_numeric_postcode($max), strlen($compare), '0');
} else {
$compare = $postcode;
}
if ($compare >= $min && $compare <= $max) {
$matches[$object_id] = isset($matches[$object_id]) ? $matches[$object_id] : array();
$matches[$object_id][] = $compare_against;
}
// Wildcard and standard comparison.
} elseif (in_array($compare_against, $wildcard_postcodes)) {
$matches[$object_id] = isset($matches[$object_id]) ? $matches[$object_id] : array();
$matches[$object_id][] = $compare_against;
}
}
return $matches;
}
