/**
* Determine if the card or single Customizer controls are active.
*
* @access private
* @since 8.4
*
* @uses cnString::startsWith()
*
* @param bool $active
* @param WP_Customize_Control $control
*
* @return bool
*/
public function setActiveControls($active, $control)
{
//$exemptions = apply_filters(
// 'cn_template_customizer_sections',
// array(
// //'cn_template_customizer_section_strings',
// )
//);
$exemptions = array();
if (cnString::startsWith('cn_', $control->id)) {
if (cnString::startsWith("cn_{$this->slug}_{$this->view}", $control->id) || in_array($control->section, $exemptions)) {
$active = TRUE;
} else {
$active = FALSE;
}
}
return $active;
}
/**
*
* The $atts['meta_query'] can have two different structures when passed to
* @see cnMeta_Query::parse_query_vars(), they are:
*
* array(
* 'meta_key' => (string),
* 'meta_value => (string|array),
* 'meta_type => (string),
* 'meta_compare' => (string)
* )
*
* OR
*
* array(
* 'meta_query' =>
* array(
* 'key' => (string),
* 'value' => (string|array),
* 'compare' => (string),
* 'type' => (string)
* ),
* [array(...),]
* )
*
* The later, 'meta_query', can have multiple arrays.
*
* @access public
* @since unknown
* @version 1.0
* @param array
* @return array
*/
public function entries($atts = array())
{
/** @var $wpdb wpdb */
global $wpdb, $current_user;
// Grab an instance of the Connections object.
$instance = Connections_Directory();
get_currentuserinfo();
$select[] = CN_ENTRY_TABLE . '.*';
$from[] = CN_ENTRY_TABLE;
$join = array();
$where[] = 'WHERE 1=1';
$having = array();
$orderBy = array();
$random = FALSE;
$visibility = array();
$permittedEntryTypes = array('individual', 'organization', 'family', 'connection_group');
$permittedEntryStatus = array('approved', 'pending');
/*
* // START -- Set the default attributes array. \\
*/
$defaults['list_type'] = NULL;
$defaults['category'] = NULL;
$defaults['category_in'] = NULL;
$defaults['exclude_category'] = NULL;
$defaults['category_name'] = NULL;
$defaults['category_slug'] = NULL;
$defaults['wp_current_category'] = FALSE;
$defaults['char'] = '';
$defaults['id'] = NULL;
$defaults['slug'] = NULL;
$defaults['family_name'] = NULL;
$defaults['last_name'] = NULL;
$defaults['title'] = NULL;
$defaults['organization'] = NULL;
$defaults['department'] = NULL;
$defaults['city'] = NULL;
$defaults['state'] = NULL;
$defaults['zip_code'] = NULL;
$defaults['country'] = NULL;
$defaults['visibility'] = NULL;
$defaults['status'] = array('approved');
$defaults['order_by'] = array('sort_column', 'last_name', 'first_name');
$defaults['limit'] = NULL;
$defaults['offset'] = 0;
$defaults['meta_query'] = array();
$defaults['allow_public_override'] = FALSE;
$defaults['private_override'] = FALSE;
$defaults['search_terms'] = NULL;
// $atts vars to support showing entries within a specified radius.
$defaults['near_addr'] = NULL;
$defaults['latitude'] = NULL;
$defaults['longitude'] = NULL;
$defaults['radius'] = 10;
$defaults['unit'] = 'mi';
$defaults['lock'] = FALSE;
$atts = cnSanitize::args($atts, $defaults);
/*
* // END -- Set the default attributes array if not supplied. \\
*/
/*
* // START -- Process the query vars. \\
* NOTE: these will override any values supplied via $atts, which include via the shortcode.
*/
if ((defined('DOING_AJAX') && DOING_AJAX || !is_admin()) && !$atts['lock']) {
// Category slug
$queryCategorySlug = get_query_var('cn-cat-slug');
if (!empty($queryCategorySlug)) {
// If the category slug is a descendant, use the last slug from the URL for the query.
$queryCategorySlug = explode('/', $queryCategorySlug);
if (isset($queryCategorySlug[count($queryCategorySlug) - 1])) {
$atts['category_slug'] = $queryCategorySlug[count($queryCategorySlug) - 1];
}
}
// Category ID
$queryCategoryID = get_query_var('cn-cat');
if (!empty($queryCategoryID)) {
$atts['category'] = $queryCategoryID;
}
// Category in
$queryCategoryIn = get_query_var('cn-cat-in');
if (!empty($queryCategoryIn)) {
$atts['category_in'] = get_query_var('cn-cat-in');
}
// Country
$queryCountry = get_query_var('cn-country');
if (!empty($queryCountry)) {
$atts['country'] = urldecode($queryCountry);
}
// Postal Code
$queryPostalCode = get_query_var('cn-postal-code');
if (!empty($queryPostalCode)) {
$atts['zip_code'] = urldecode($queryPostalCode);
}
// Region [State]
$queryRegion = get_query_var('cn-region');
if (!empty($queryRegion)) {
$atts['state'] = urldecode($queryRegion);
}
// Locality [City]
$queryLocality = get_query_var('cn-locality');
if (!empty($queryLocality)) {
$atts['city'] = urldecode($queryLocality);
}
// Organization
$queryOrganization = get_query_var('cn-organization');
if (!empty($queryOrganization)) {
$atts['organization'] = urldecode($queryOrganization);
}
// Department
$queryDeparment = get_query_var('cn-department');
if (!empty($queryDeparment)) {
$atts['department'] = urldecode($queryDeparment);
}
// Entry slug
// NOTE: The entry slug is saved in the DB URL encoded, so there's no need to urldecode().
$queryEntrySlug = get_query_var('cn-entry-slug');
if (!empty($queryEntrySlug)) {
$atts['slug'] = $queryEntrySlug;
}
// Initial character.
$queryInitialChar = get_query_var('cn-char');
if (!empty($queryInitialChar)) {
$atts['char'] = urldecode($queryInitialChar);
}
// Pagination
$queryPage = get_query_var('cn-pg');
$atts['offset'] = !empty($queryPage) ? ($queryPage - 1) * $atts['limit'] : $atts['offset'];
$atts['offset'] = $atts['offset'] > 0 ? $atts['offset'] : NULL;
// Search terms
$searchTerms = get_query_var('cn-s');
if (!empty($searchTerms)) {
$atts['search_terms'] = $searchTerms;
}
// Geo-location
$queryCoord = get_query_var('cn-near-coord');
if (!empty($queryCoord)) {
$queryCoord = explode(',', $queryCoord);
$atts['latitude'] = $wpdb->prepare('%f', $queryCoord[0]);
$atts['longitude'] = $wpdb->prepare('%f', $queryCoord[1]);
// Get the radius, otherwise the default of 10.
if (get_query_var('cn-radius')) {
$atts['radius'] = $wpdb->prepare('%d', get_query_var('cn-radius'));
}
// Sanitize and set the unit.
$atts['unit'] = get_query_var('cn-unit') ? sanitize_key(get_query_var('cn-unit')) : sanitize_key($atts['unit']);
}
}
/*
* // END -- Process the query vars. \\
*/
/*
* // START -- Reset some of the $atts based if category_slug or entry slug
* is being used. This is done to prevent query conflicts. This should be safe because
* if a specific entry or category is being queried the other $atts can be discarded.
* This has to be done in order to reconcile values passed via the shortcode and the
* query string values.
*
* @TODO I know there are more scenarios to consider ... but this is all I can think of at the moment.
* Thought --- maybe resetting to the default $atts should be done --- something to consider.
*/
if (!empty($atts['slug']) || !empty($atts['category_slug'])) {
$atts['list_type'] = NULL;
$atts['category'] = NULL;
$atts['category_in'] = NULL;
$atts['wp_current_category'] = NULL;
}
if (!empty($atts['slug'])) {
$atts['near_addr'] = NULL;
$atts['latitude'] = NULL;
$atts['longitude'] = NULL;
$atts['radius'] = 10;
$atts['unit'] = 'mi';
}
/*
* // END -- Reset.
*/
/*
* If in a post get the category names assigned to the post.
*/
if ($atts['wp_current_category'] && !is_page()) {
// Get the current post categories.
$wpCategories = get_the_category();
// Build an array of the post categories.
foreach ($wpCategories as $wpCategory) {
$categoryNames[] = $wpdb->prepare('%s', $wpCategory->cat_name);
}
}
/*
* Build and array of the supplied category names and their children.
*/
if (!empty($atts['category_name'])) {
// If value is a string convert to an array.
if (!is_array($atts['category_name'])) {
$atts['category_name'] = explode(',', $atts['category_name']);
}
foreach ($atts['category_name'] as $categoryName) {
// Add the parent category to the array and remove any whitespace from the beginning/end of the name just in case the user added it when using the shortcode.
$categoryNames[] = $wpdb->prepare('%s', trim($categoryName));
// Retrieve the children categories
$results = $this->categoryChildren('name', $categoryName);
foreach ((array) $results as $term) {
// Only add the name if it doesn't already exist. If it doesn't sanitize and add to the array.
if (!in_array($term->name, $categoryNames)) {
$categoryNames[] = $wpdb->prepare('%s', $term->name);
}
}
}
}
/*
* Build and array of the supplied category slugs and their children.
*/
if (!empty($atts['category_slug'])) {
$categorySlugs = array();
// If value is a string convert to an array.
if (!is_array($atts['category_slug'])) {
$atts['category_slug'] = explode(',', $atts['category_slug']);
}
foreach ($atts['category_slug'] as $categorySlug) {
// Add the parent category to the array and remove any whitespace from the beginning/end of the name in case the user added it when using the shortcode.
$categorySlugs[] = sanitize_title(trim($categorySlug));
// Retrieve the children categories.
$results = $this->categoryChildren('slug', $categorySlug);
foreach ((array) $results as $term) {
// Only add the slug if it doesn't already exist. If it doesn't sanitize and add to the array.
if (!in_array($term->name, $categorySlugs)) {
$categorySlugs[] = sanitize_title($term->slug);
}
}
}
}
/*
* Build an array of all the categories and their children based on the supplied category IDs.
*/
if (!empty($atts['category'])) {
$atts['category'] = wp_parse_id_list($atts['category']);
foreach ($atts['category'] as $categoryID) {
// Add the parent category ID to the array.
$categoryIDs[] = absint($categoryID);
// Retrieve the children categories
$results = $this->categoryChildren('term_id', $categoryID);
//print_r($results);
foreach ((array) $results as $term) {
// Only add the ID if it doesn't already exist. If it doesn't sanitize and add to the array.
if (!in_array($term->term_id, $categoryIDs)) {
$categoryIDs[] = absint($term->term_id);
}
}
}
}
/*
* Exclude the specified categories by ID.
*/
if (!empty($atts['exclude_category'])) {
if (!isset($categoryIDs)) {
$categoryIDs = array();
}
$atts['exclude_category'] = wp_parse_id_list($atts['exclude_category']);
$categoryIDs = array_diff((array) $categoryIDs, $atts['exclude_category']);
foreach ($atts['exclude_category'] as $categoryID) {
// Add the parent category ID to the array.
$categoryExcludeIDs[] = absint($categoryID);
// Retrieve the children categories
$results = $this->categoryChildren('term_id', $categoryID);
// var_dump($results);
foreach ((array) $results as $term) {
// Only add the ID if it doesn't already exist. If it doesn't sanitize and add to the array.
if (!in_array($term->term_id, $categoryExcludeIDs)) {
$categoryExcludeIDs[] = absint($term->term_id);
}
}
}
// Merge the children of the excluded category into the excluded category ID array.
$atts['exclude_category'] = array_merge($atts['exclude_category'], (array) $categoryExcludeIDs);
// Ensure unique values only.
$atts['exclude_category'] = array_unique($atts['exclude_category']);
$sql = 'SELECT tr.entry_id FROM ' . CN_TERM_RELATIONSHIP_TABLE . ' AS tr
INNER JOIN ' . CN_TERM_TAXONOMY_TABLE . ' AS tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id)
WHERE 1=1 AND tt.term_id IN (' . implode(", ", $atts['exclude_category']) . ')';
// Store the entryIDs that are to be excluded.
$results = $wpdb->get_col($sql);
//print_r($results);
if (!empty($results)) {
$where[] = 'AND ' . CN_ENTRY_TABLE . '.id NOT IN (' . implode(", ", $results) . ')';
}
/*else {
$where[] = 'AND 1=2';
}*/
}
// Convert the supplied category IDs $atts['category_in'] to an array.
if (!empty($atts['category_in'])) {
$atts['category_in'] = wp_parse_id_list($atts['category_in']);
// Remove empty values from the array.
$atts['category_in'] = array_filter($atts['category_in']);
// Ensure there is something to query after filtering the array.
if (!empty($atts['category_in'])) {
// Exclude any category IDs that may have been set.
$atts['category_in'] = array_diff($atts['category_in'], (array) $atts['exclude_category']);
// Build the query to retrieve entry IDs that are assigned to all the supplied category IDs; operational AND.
$sql = 'SELECT DISTINCT tr.entry_id FROM ' . CN_TERM_RELATIONSHIP_TABLE . ' AS tr
INNER JOIN ' . CN_TERM_TAXONOMY_TABLE . ' AS tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id)
WHERE 1=1 AND tt.term_id IN (' . implode(", ", $atts['category_in']) . ') GROUP BY tr.entry_id HAVING COUNT(*) = ' . count($atts['category_in']) . ' ORDER BY tr.entry_id';
// Store the entryIDs that exist on all of the supplied category IDs
$results = $wpdb->get_col($sql);
//print_r($results);
if (!empty($results)) {
$where[] = 'AND ' . CN_ENTRY_TABLE . '.id IN (' . implode(", ", $results) . ')';
} else {
$where[] = 'AND 1=2';
}
}
/*
* This is the query to use to return entry IDs that are in the same categories. The COUNT value
* should equal the number of category IDs in the IN() statement.
SELECT DISTINCT tr.entry_id FROM `wp_connections_term_relationships` AS tr
INNER JOIN `wp_connections_term_taxonomy` AS tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id)
WHERE 1=1 AND tt.term_id IN ('51','73','76') GROUP BY tr.entry_id HAVING COUNT(*) = 3 ORDER BY tr.entry_id
*/
}
if (!empty($categoryIDs) || !empty($categoryExcludeIDs) || !empty($categoryNames) || !empty($categorySlugs)) {
// Set the query string to INNER JOIN the term relationship and taxonomy tables.
$join[] = 'INNER JOIN ' . CN_TERM_RELATIONSHIP_TABLE . ' ON ( ' . CN_ENTRY_TABLE . '.id = ' . CN_TERM_RELATIONSHIP_TABLE . '.entry_id )';
$join[] = 'INNER JOIN ' . CN_TERM_TAXONOMY_TABLE . ' ON ( ' . CN_TERM_RELATIONSHIP_TABLE . '.term_taxonomy_id = ' . CN_TERM_TAXONOMY_TABLE . '.term_taxonomy_id )';
$join[] = 'INNER JOIN ' . CN_TERMS_TABLE . ' ON ( ' . CN_TERMS_TABLE . '.term_id = ' . CN_TERM_TAXONOMY_TABLE . '.term_id )';
// Set the query string to return entries within the category taxonomy.
$where[] = 'AND ' . CN_TERM_TAXONOMY_TABLE . '.taxonomy = \'category\'';
if (!empty($categoryIDs)) {
$where[] = 'AND ' . CN_TERM_TAXONOMY_TABLE . '.term_id IN (' . implode(", ", $categoryIDs) . ')';
unset($categoryIDs);
}
if (!empty($categoryExcludeIDs)) {
$where[] = 'AND ' . CN_TERM_TAXONOMY_TABLE . '.term_id NOT IN (' . implode(", ", $categoryExcludeIDs) . ')';
unset($categoryExcludeIDs);
}
if (!empty($categoryNames)) {
$where[] = 'AND ' . CN_TERMS_TABLE . '.name IN (' . implode(", ", (array) $categoryNames) . ')';
unset($categoryNames);
}
if (!empty($categorySlugs)) {
$where[] = 'AND ' . CN_TERMS_TABLE . '.slug IN (\'' . implode("', '", (array) $categorySlugs) . '\')';
unset($categorySlugs);
}
}
/*
* // START --> Set up the query to only return the entries that match the supplied IDs.
* NOTE: This includes the entry IDs returned for category_in.
*/
if (!empty($atts['id'])) {
$atts['id'] = wp_parse_id_list($atts['id']);
// Set query string to return specific entries.
$where[] = 'AND ' . CN_ENTRY_TABLE . '.id IN (\'' . implode("', '", $atts['id']) . '\')';
}
/*
* // END --> Set up the query to only return the entries that match the supplied IDs.
*/
/*
* // START --> Set up the query to only return the entries that match the supplied search terms.
*/
if (!empty($atts['search_terms'])) {
$searchResults = $this->search(array('terms' => $atts['search_terms']));
//print_r($searchResults);
// If there were no results, add a WHERE clause that will not return results when performing the whole query.
if (empty($searchResults)) {
$where[] = 'AND 1=2';
} else {
// Set $atts['order_by'] to the order the entry IDs were returned.
// This is to support the relevancy order results being returned by self::search().
$atts['order_by'] = 'id|SPECIFIED';
$atts['id'] = $searchResults;
// Set the entry IDs to be the search results.
$where[] = 'AND ' . CN_ENTRY_TABLE . '.id IN (\'' . implode("', '", $searchResults) . '\')';
}
}
/*
* // END --> Set up the query to only return the entries that match the supplied search terms.
*/
/*
* // START --> Set up the query to only return the entry that matches the supplied slug.
*/
if (!empty($atts['slug'])) {
// Trim the white space from the ends.
$atts['slug'] = trim($atts['slug']);
$where[] = $wpdb->prepare('AND ' . CN_ENTRY_TABLE . '.slug = %s', $atts['slug']);
}
/*
* // END --> Set up the query to only return the entry that matches the supplied slug.
*/
/*
* // START --> Set up the query to only return the entries that match the supplied entry type.
*/
// Convert the supplied entry types $atts['list_type'] to an array.
if (!is_array($atts['list_type']) && !empty($atts['list_type'])) {
// Trim the space characters if present.
$atts['list_type'] = str_replace(' ', '', $atts['list_type']);
// Convert to array.
$atts['list_type'] = explode(',', $atts['list_type']);
}
// Set query string for entry type.
if (!empty($atts['list_type']) && (bool) array_intersect($atts['list_type'], $permittedEntryTypes)) {
// Compatibility code to make sure any occurrences of the deprecated entry type connections_group is changed to entry type family
$atts['list_type'] = str_replace('connection_group', 'family', $atts['list_type']);
$where[] = 'AND `entry_type` IN (\'' . implode("', '", (array) $atts['list_type']) . '\')';
}
/*
* // END --> Set up the query to only return the entries that match the supplied entry type.
*/
/*
* // START --> Set up the query to only return the entries that match the supplied filters.
*/
if (!empty($atts['family_name'])) {
$where[] = $wpdb->prepare('AND `family_name` = %s', $atts['family_name']);
}
if (!empty($atts['last_name'])) {
$where[] = $wpdb->prepare('AND `last_name` = %s', $atts['last_name']);
}
if (!empty($atts['title'])) {
$where[] = $wpdb->prepare('AND `title` = %s', $atts['title']);
}
if (!empty($atts['organization'])) {
$where[] = $wpdb->prepare('AND `organization` = %s', $atts['organization']);
}
if (!empty($atts['department'])) {
$where[] = $wpdb->prepare('AND `department` = %s', $atts['department']);
}
if (!empty($atts['city']) || !empty($atts['state']) || !empty($atts['zip_code']) || !empty($atts['country'])) {
if (!isset($join['address'])) {
$join['address'] = 'INNER JOIN ' . CN_ENTRY_ADDRESS_TABLE . ' ON ( ' . CN_ENTRY_TABLE . '.id = ' . CN_ENTRY_ADDRESS_TABLE . '.entry_id )';
}
if (!empty($atts['city'])) {
$where[] = $wpdb->prepare('AND ' . CN_ENTRY_ADDRESS_TABLE . '.city = %s', $atts['city']);
}
if (!empty($atts['state'])) {
$where[] = $wpdb->prepare('AND ' . CN_ENTRY_ADDRESS_TABLE . '.state = %s', $atts['state']);
}
if (!empty($atts['zip_code'])) {
$where[] = $wpdb->prepare('AND ' . CN_ENTRY_ADDRESS_TABLE . '.zipcode = %s', $atts['zip_code']);
}
if (!empty($atts['country'])) {
$where[] = $wpdb->prepare('AND ' . CN_ENTRY_ADDRESS_TABLE . '.country = %s', $atts['country']);
}
}
if (0 < strlen($atts['char'])) {
$having[] = $wpdb->prepare('HAVING sort_column LIKE %s', $wpdb->esc_like($atts['char']) . '%');
}
/*
* // END --> Set up the query to only return the entries that match the supplied filters.
*/
/*
* // START --> Set up the query to only return the entries based on user permissions.
*/
if (is_user_logged_in()) {
if (!isset($atts['visibility']) || empty($atts['visibility'])) {
if (current_user_can('connections_view_public')) {
$visibility[] = 'public';
}
if (current_user_can('connections_view_private')) {
$visibility[] = 'private';
}
if (current_user_can('connections_view_unlisted') && is_admin()) {
$visibility[] = 'unlisted';
}
} else {
$visibility[] = $atts['visibility'];
}
} else {
//var_dump( $instance->options->getAllowPublic() ); die;
// Display the 'public' entries if the user is not required to be logged in.
if ($instance->options->getAllowPublic()) {
$visibility[] = 'public';
}
// Display the 'public' entries if the public override shortcode option is enabled.
if ($instance->options->getAllowPublicOverride()) {
if ($atts['allow_public_override'] == TRUE) {
$visibility[] = 'public';
}
}
// Display the 'public' & 'private' entries if the private override shortcode option is enabled.
if ($instance->options->getAllowPrivateOverride()) {
// If the user can view private entries then they should be able to view public entries too, so we'll add it. Just check to see if it is already set first.
if (!in_array('public', $visibility) && $atts['private_override'] == TRUE) {
$visibility[] = 'public';
}
if ($atts['private_override'] == TRUE) {
$visibility[] = 'private';
}
}
}
$where[] = 'AND ' . CN_ENTRY_TABLE . '.visibility IN (\'' . implode("', '", (array) $visibility) . '\')';
/*
* // END --> Set up the query to only return the entries based on user permissions.
*/
/*
* // START --> Set up the query to only return the entries based on status.
*/
// Convert the supplied entry statuses $atts['status'] to an array.
if (!is_array($atts['status'])) {
// Trim the space characters if present.
$atts['status'] = str_replace(' ', '', $atts['status']);
// Convert to array.
$atts['status'] = explode(',', $atts['status']);
}
/*else
{
// Query the approved entries
$atts['status'] = array('approved');
}*/
if (is_user_logged_in()) {
// if 'all' was supplied, set the array to all the permitted entry status types.
if (in_array('all', $atts['status'])) {
$atts['status'] = $permittedEntryStatus;
}
// Limit the viewable status per role capability assigned to the current user.
if (current_user_can('connections_edit_entry')) {
$userPermittedEntryStatus = array('approved', 'pending');
$atts['status'] = array_intersect($userPermittedEntryStatus, $atts['status']);
} elseif (current_user_can('connections_edit_entry_moderated')) {
$userPermittedEntryStatus = array('approved', 'pending');
$atts['status'] = array_intersect($userPermittedEntryStatus, $atts['status']);
} else {
$userPermittedEntryStatus = array('approved');
$atts['status'] = array_intersect($userPermittedEntryStatus, $atts['status']);
}
}
/*else
{
// If no user is logged in, set the status for the query to approved.
$atts['status'] = array('approved');
}*/
$where[] = 'AND ' . CN_ENTRY_TABLE . '.status IN (\'' . implode("', '", $atts['status']) . '\')';
/*
* // END --> Set up the query to only return the entries based on status.
*/
/*
* // START --> Geo-limit the query.
*/
//$atts['latitude'] = 40.3663671;
//$atts['longitude'] = -75.8876941;
if (!empty($atts['latitude']) && !empty($atts['longitude'])) {
$earthRadius = 6371;
// Earth's radius in (SI) km.
// Convert the supplied radius from the supplied unit to (SI) km.
$atts['radius'] = cnGeo::convert(array('value' => $atts['radius'], 'from' => $atts['unit'], 'to' => 'km', 'format' => FALSE, 'return' => TRUE));
// Limiting bounding box (in degrees).
$minLat = $atts['latitude'] - rad2deg($atts['radius'] / $earthRadius);
$maxLat = $atts['latitude'] + rad2deg($atts['radius'] / $earthRadius);
$minLng = $atts['longitude'] - rad2deg($atts['radius'] / $earthRadius / cos(deg2rad($atts['latitude'])));
$maxLng = $atts['longitude'] + rad2deg($atts['radius'] / $earthRadius / cos(deg2rad($atts['latitude'])));
// Convert origin of geographic circle to radians.
$atts['latitude'] = deg2rad($atts['latitude']);
$atts['longitude'] = deg2rad($atts['longitude']);
// Add the SELECT statement that adds the `radius` column.
$select[] = $wpdb->prepare('acos(sin(%f)*sin(radians(latitude)) + cos(%f)*cos(radians(latitude))*cos(radians(longitude)-%f))*6371 AS distance', $atts['latitude'], $atts['latitude'], $atts['longitude']);
// Create a subquery that will limit the rows that have the cosine law applied to within the bounding box.
$geoSubselect = $wpdb->prepare('(SELECT entry_id FROM ' . CN_ENTRY_ADDRESS_TABLE . ' WHERE latitude>%f AND latitude<%f AND longitude>%f AND longitude<%f) AS geo_bound', $minLat, $maxLat, $minLng, $maxLng);
// The subquery needs to be added to the beginning of the array so the inner joins on the other tables are joined to the CN_ENTRY_TABLE
array_unshift($from, $geoSubselect);
// Add the JOIN for the address table. NOTE: This JOIN is also set in the ORDER BY section. The 'address' index is to make sure it doea not get added to the query twice.
if (!isset($join['address'])) {
$join['address'] = 'INNER JOIN ' . CN_ENTRY_ADDRESS_TABLE . ' ON ( ' . CN_ENTRY_TABLE . '.id = ' . CN_ENTRY_ADDRESS_TABLE . '.entry_id )';
}
// Add the WHERE statement to limit the query to a geographic circle per the defined radius.
$where[] = $wpdb->prepare('AND acos(sin(%f)*sin(radians(latitude)) + cos(%f)*cos(radians(latitude))*cos(radians(longitude)-%f))*6371 < %f', $atts['latitude'], $atts['latitude'], $atts['longitude'], $atts['radius']);
// This is required otherwise addresses the user may not have permissions to view will be included in the query
// which could be confusing since entries could appear to be outside of the search radius when in fact the entry
// is within the search radius, it is just the address used to determine that is not viewable to the user.
$where[] = 'AND ' . CN_ENTRY_ADDRESS_TABLE . '.visibility IN (\'' . implode("', '", (array) $visibility) . '\')';
// Temporarily set the sort order to 'radius' for testing.
//$atts['order_by'] = array('radius');
}
/*
* // END --> Geo-limit the query.
*/
/*
* // START --> Build the ORDER BY query segment.
*/
//if ( empty($atts['order_by']) )
//{
// Setup the default sort order if none were supplied.
//$orderBy = array('sort_column', 'last_name', 'first_name');
//}
//else
//{
$orderFields = array('id', 'date_added', 'date_modified', 'first_name', 'last_name', 'title', 'organization', 'department', 'city', 'state', 'zipcode', 'country', 'birthday', 'anniversary', 'sort_column');
$orderFlags = array('SPECIFIED' => 'SPECIFIED', 'RANDOM' => 'RANDOM', 'ASC' => 'ASC', 'SORT_ASC' => 'ASC', 'DESC' => 'DESC', 'SORT_DESC' => 'DESC', 'NUMERIC' => '+0', 'SORT_NUMERIC' => '+0', 'SORT_NUMERIC_ASC' => '+0', 'NUMERIC_DESC' => '+0 DESC', 'SORT_NUMERIC_DESC' => '+0 DESC');
// If a geo-bound query is being performed the `radius` order field can be used.
if (!empty($atts['latitude']) && !empty($atts['longitude'])) {
array_push($orderFields, 'distance');
}
// Get registered date types.
$dateTypes = array_keys($instance->options->getDateOptions());
// Convert to an array
if (!is_array($atts['order_by'])) {
// Trim the space characters if present.
$atts['order_by'] = str_replace(' ', '', $atts['order_by']);
// Build an array of each field to order by and its sort order.
$atts['order_by'] = explode(',', $atts['order_by']);
}
// For each field the sort order can be defined.
foreach ($atts['order_by'] as $orderByField) {
$orderByAtts[] = explode('|', $orderByField);
}
// Build the ORDER BY query segment
foreach ($orderByAtts as $field) {
// Trim any spaces the user may have supplied and set it to be lowercase.
$field[0] = strtolower(trim($field[0]));
// Check to make sure the supplied field is one of the valid fields to order by.
if (in_array($field[0], $orderFields) || cnString::startsWith('meta_key:', $field[0])) {
// The date_modified actually maps to the `ts` column in the db.
if ($field[0] == 'date_modified') {
$field[0] = 'ts';
}
// If one of the order fields is an address region add the INNER JOIN to the CN_ENTRY_ADDRESS_TABLE
if ($field[0] == 'city' || $field[0] == 'state' || $field[0] == 'zipcode' || $field[0] == 'country') {
if (!isset($join['address'])) {
$join['address'] = 'INNER JOIN ' . CN_ENTRY_ADDRESS_TABLE . ' ON ( ' . CN_ENTRY_TABLE . '.id = ' . CN_ENTRY_ADDRESS_TABLE . '.entry_id )';
}
}
if (cnString::startsWith('meta_key:', $field[0])) {
// Extract the meta key name from $field[0].
$meta = explode(':', $field[0]);
// Ensure the meta key does exist and is not empty before altering the query.
if (isset($meta[1]) && !empty($meta[1])) {
$atts['meta_query']['meta_query'][] = array('key' => $meta[1]);
if (1 < count($atts['meta_query']['meta_query'])) {
$field[0] = 'mt' . (count($atts['meta_query']['meta_query']) - 1) . '.meta_value';
} else {
$field[0] = CN_ENTRY_TABLE_META . '.meta_value';
}
}
}
// If we're ordering by anniversary or birthday, we need to convert the string to a UNIX timestamp so it is properly ordered.
// Otherwise, it is sorted as a string which can give some very odd results compared to what is expected.
//if ( $field[0] == 'anniversary' || $field[0] == 'birthday' ) {
//
// $field[0] = 'FROM_UNIXTIME( ' . $field[0] . ' )';
//}
if (in_array($field[0], $dateTypes)) {
if (!isset($join['date'])) {
$join['date'] = 'INNER JOIN ' . CN_ENTRY_DATE_TABLE . ' ON ( ' . CN_ENTRY_TABLE . '.id = ' . CN_ENTRY_DATE_TABLE . '.entry_id )';
}
$where[] = $wpdb->prepare('AND ' . CN_ENTRY_DATE_TABLE . '.type = %s', $field[0]);
$field[0] = 'date';
}
// Check to see if an order flag was set and is a valid order flag.
if (isset($field[1])) {
// Trim any spaces the user might have added and change the string to uppercase..
$field[1] = strtoupper(trim($field[1]));
// If a user included a sort flag that is invalid/mis-spelled it is skipped since it can not be used.
if (array_key_exists($field[1], $orderFlags)) {
/*
* The SPECIFIED and RANDOM order flags are special use and should only be used with the id sort field.
* Set the default sort flag if it was use on any other sort field than id.
*/
if (($orderFlags[$field[1]] == 'SPECIFIED' || $orderFlags[$field[1]] == 'RANDOM') && $field[0] != 'id') {
$field[1] = 'SORT_ASC';
}
switch ($orderFlags[$field[1]]) {
/*
* Order the results based on the order of the supplied entry IDs
*/
case 'SPECIFIED':
if (!empty($atts['id'])) {
$orderBy = array('FIELD( ' . CN_ENTRY_TABLE . '.id, ' . implode(', ', (array) $atts['id']) . ' )');
}
break;
/*
* Randomize the order of the results.
*/
/*
* Randomize the order of the results.
*/
case 'RANDOM':
$random = TRUE;
break;
/*
* Return the results in ASC or DESC order.
*/
/*
* Return the results in ASC or DESC order.
*/
default:
$orderBy[] = $field[0] . ' ' . $orderFlags[$field[1]];
break;
}
} else {
$orderBy[] = $field[0];
}
} else {
$orderBy[] = $field[0];
}
}
}
//}
if (!empty($atts['meta_query'])) {
$metaQuery = new cnMeta_Query();
$metaQuery->parse_query_vars($atts['meta_query']);
$metaClause = $metaQuery->get_sql('entry', CN_ENTRY_TABLE, 'id');
$join['meta'] = $metaClause['join'];
$where['meta'] = $metaClause['where'];
}
$orderBy = empty($orderBy) ? 'ORDER BY sort_column, last_name, first_name' : 'ORDER BY ' . implode(', ', $orderBy);
/*
* // END --> Build the ORDER BY query segment.
*/
/*
* // START --> Set up the query LIMIT and OFFSET.
*/
$limit = empty($atts['limit']) ? '' : $wpdb->prepare(' LIMIT %d ', $atts['limit']);
$offset = empty($atts['offset']) ? '' : $wpdb->prepare(' OFFSET %d ', $atts['offset']);
/*
* // END --> Set up the query LIMIT and OFFSET.
*/
/*
* // START --> Build the SELECT query segment.
*/
$select[] = 'CASE `entry_type`
WHEN \'individual\' THEN `last_name`
WHEN \'organization\' THEN `organization`
WHEN \'connection_group\' THEN `family_name`
WHEN \'family\' THEN `family_name`
END AS `sort_column`';
/*
* // END --> Build the SELECT query segment.
*/
/**
* NOTES:
*
* Many queries can produce multiple results per entry ID when we really only want it once.
* For example an entry maybe return once for each category it is assigned or once for each
* address an entry has that is within the search radius.
*
* Simple adding `GROUP BY CN_ENTRY_TABLE.id seems to fix this, but may be incorrect and might fail
* on db/s other than MySQL such as Oracle.
*
* Very useful links that provide more details that require further study:
*
*@link http://www.psce.com/blog/2012/05/15/mysql-mistakes-do-you-use-group-by-correctly/
* @link http://rpbouman.blogspot.com/2007/05/debunking-group-by-myths.html
*/
if ($random) {
$seed = cnFormatting::stripNonNumeric(cnUtility::getIP()) . date('Hdm', current_time('timestamp', 1));
$sql = 'SELECT SQL_CALC_FOUND_ROWS *, RAND(' . $seed . ') AS random FROM ( SELECT DISTINCT ' . implode(', ', $select) . ' FROM ' . implode(', ', $from) . ' ' . implode(' ', $join) . ' ' . implode(' ', $where) . ' GROUP BY ' . CN_ENTRY_TABLE . '.id ' . implode(' ', $having) . ') AS T ORDER BY random' . $limit . $offset;
// print_r($sql);
} else {
$sql = 'SELECT SQL_CALC_FOUND_ROWS DISTINCT ' . implode(', ', $select) . ' FROM ' . implode(', ', $from) . ' ' . implode(' ', $join) . ' ' . implode(' ', $where) . ' GROUP BY ' . CN_ENTRY_TABLE . '.id ' . implode(' ', $having) . ' ' . $orderBy . ' ' . $limit . $offset;
// print_r($sql);
}
if (!($results = $this->results($sql))) {
$results = $wpdb->get_results($sql);
$this->cache($sql, $results);
// The most recent query to have been executed by cnRetrieve::entries
$instance->lastQuery = $wpdb->last_query;
// The most recent query error to have been generated by cnRetrieve::entries
$instance->lastQueryError = $wpdb->last_error;
// ID generated for an AUTO_INCREMENT column by the most recent INSERT query.
$instance->lastInsertID = $wpdb->insert_id;
// The number of rows returned by the last query.
$instance->resultCount = $wpdb->num_rows;
// The number of rows returned by the last query without the limit clause set
$foundRows = $wpdb->get_results('SELECT FOUND_ROWS()');
$instance->resultCountNoLimit = $foundRows[0]->{'FOUND_ROWS()'};
$this->resultCountNoLimit = $foundRows[0]->{'FOUND_ROWS()'};
}
// The total number of entries based on user permissions.
// $instance->recordCount = self::recordCount( array( 'public_override' => $atts['allow_public_override'], 'private_override' => $atts['private_override'] ) );
// The total number of entries based on user permissions with the status set to 'pending'
// $instance->recordCountPending = self::recordCount( array( 'public_override' => $atts['allow_public_override'], 'private_override' => $atts['private_override'], 'status' => array( 'pending' ) ) );
// The total number of entries based on user permissions with the status set to 'approved'
// $instance->recordCountApproved = self::recordCount( array( 'public_override' => $atts['allow_public_override'], 'private_override' => $atts['private_override'], 'status' => array( 'approved' ) ) );
/*
* ONLY in the admin.
*
* Reset the pagination filter for the current user, remove the offset from the query and re-run the
* query if the offset for the query is greater than the record count with no limit set in the query.
*
*/
if (is_admin() && $atts['offset'] > $instance->resultCountNoLimit) {
$instance->currentUser->resetFilterPage('manage');
unset($atts['offset']);
$results = $this->entries($atts);
} elseif ($atts['offset'] > $instance->resultCountNoLimit) {
/*
* This is for the front end, reset the page and offset and re-run the query if the offset
* is greater than the record count with no limit.
*
* @TODO this should somehow be precessed in the parse_request action hook so the URL
* permalink and query vars can be properly updated.
*/
set_query_var('cn-pg', 0);
$atts['offset'] = 0;
$results = $this->entries($atts);
}
return $results;
}