/** * 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; }