/** * Builds an array of SQL query conditions based on regularly used arguments * @param array $args * @return array */ function build_sql_conditions($args = array()) { global $wpdb; $events_table = EM_EVENTS_TABLE; $locations_table = EM_LOCATIONS_TABLE; $args = apply_filters('em_object_build_sql_conditions_args', $args); //Format the arguments passed on $scope = $args['scope']; //undefined variable warnings in ZDE, could just delete this (but dont pls!) $recurring = $args['recurring']; $recurrence = $args['recurrence']; $recurrences = $args['recurrences']; $category = $args['category']; // - not used anymore, accesses the $args directly $tag = $args['tag']; // - not used anymore, accesses the $args directly $location = $args['location']; $bookings = $args['rsvp']; $bookings = !empty($args['bookings']) ? $args['bookings'] : $bookings; $owner = $args['owner']; $event = $args['event']; $month = $args['month']; $year = $args['year']; $today = date('Y-m-d', current_time('timestamp')); //Create the WHERE statement //Recurrences $conditions = array(); if ($recurring) { $conditions['recurring'] = "`recurrence`=1"; } elseif ($recurrence > 0) { $conditions['recurrence'] = "`recurrence_id`={$recurrence}"; } else { if ($recurrences !== null) { $conditions['recurrences'] = $recurrences ? "(`recurrence_id` > 0 )" : "(`recurrence_id` IS NULL OR `recurrence_id`=0 )"; } $conditions['recurring'] = "(`recurrence`!=1 OR `recurrence` IS NULL)"; } //Dates - first check 'month', and 'year', and adjust scope if needed if (!($month == '' && $year == '')) { //Sort out month range, if supplied an array of array(month,month), it'll check between these two months if (self::array_is_numeric($month)) { $date_month_start = $month[0]; $date_month_end = $month[1]; } else { if (!empty($month)) { $date_month_start = $date_month_end = $month; } else { $date_month_start = 1; $date_month_end = 12; } } //Sort out year range, if supplied an array of array(year,year), it'll check between these two years if (self::array_is_numeric($year)) { $date_year_start = $year[0]; $date_year_end = $year[1]; } else { $date_year_start = $date_year_end = $year; } $date_start = $date_year_start . "-" . $date_month_start . "-01"; $date_end = date('Y-m-t', mktime(0, 0, 0, $date_month_end, 1, $date_year_end)); $scope = array($date_start, $date_end); //just modify the scope here } //No date requested, so let's look at scope if (is_array($scope)) { //This is an array, let's split it up $date_start = $scope[0]; $date_end = $scope[1]; if (!empty($date_start) && empty($date_end)) { //do a from till infinity $conditions['scope'] = " event_start_date >= CAST('{$date_start}' AS DATE)"; } elseif (empty($date_start) && !empty($date_end)) { //do past till $date_end if (get_option('dbem_events_current_are_past')) { $conditions['scope'] = " event_start_date <= CAST('{$date_end}' AS DATE)"; } else { $conditions['scope'] = " event_end_date <= CAST('{$date_end}' AS DATE)"; } } else { //date range if (get_option('dbem_events_current_are_past')) { $conditions['scope'] = "( event_start_date BETWEEN CAST('{$date_start}' AS DATE) AND CAST('{$date_end}' AS DATE) )"; } else { $conditions['scope'] = "( event_start_date <= CAST('{$date_end}' AS DATE) AND event_end_date >= CAST('{$date_start}' AS DATE) )"; } //$conditions['scope'] = " ( ( event_start_date <= CAST('$date_end' AS DATE) AND event_end_date >= CAST('$date_start' AS DATE) ) OR (event_start_date BETWEEN CAST('$date_start' AS DATE) AND CAST('$date_end' AS DATE)) OR (event_end_date BETWEEN CAST('$date_start' AS DATE) AND CAST('$date_end' AS DATE)) )"; } } elseif (preg_match("/^[0-9]{4}-[0-9]{2}-[0-9]{2}\$/", $scope)) { //Scope can also be a specific date. However, if 'day', 'month', or 'year' are set, that will take precedence if (get_option('dbem_events_current_are_past')) { $conditions['scope'] = "event_start_date = CAST('{$scope}' AS DATE)"; } else { $conditions['scope'] = " ( event_start_date = CAST('{$scope}' AS DATE) OR ( event_start_date <= CAST('{$scope}' AS DATE) AND event_end_date >= CAST('{$scope}' AS DATE) ) )"; } } else { if ($scope == "past") { if (get_option('dbem_events_current_are_past')) { $conditions['scope'] = " event_start_date < '{$today}'"; } else { $conditions['scope'] = " event_end_date < '{$today}'"; } } elseif ($scope == "today") { $conditions['scope'] = " (event_start_date = CAST('{$today}' AS DATE))"; if (!get_option('dbem_events_current_are_past')) { $conditions['scope'] .= " OR (event_start_date <= CAST('{$today}' AS DATE) AND event_end_date >= CAST('{$today}' AS DATE))"; } } elseif ($scope == "tomorrow") { $tomorrow = date('Y-m-d', current_time('timestamp') + 60 * 60 * 24); $conditions['scope'] = "(event_start_date = CAST('{$tomorrow}' AS DATE))"; if (!get_option('dbem_events_current_are_past')) { $conditions['scope'] .= " OR (event_start_date <= CAST('{$tomorrow}' AS DATE) AND event_end_date >= CAST('{$tomorrow}' AS DATE))"; } } elseif ($scope == "month") { $start_month = date('Y-m-d', current_time('timestamp')); $end_month = date('Y-m-t', current_time('timestamp')); $conditions['scope'] = " (event_start_date BETWEEN CAST('{$start_month}' AS DATE) AND CAST('{$end_month}' AS DATE))"; if (!get_option('dbem_events_current_are_past')) { $conditions['scope'] .= " OR (event_start_date < CAST('{$start_month}' AS DATE) AND event_end_date >= CAST('{$start_month}' AS DATE))"; } } elseif ($scope == "next-month") { $start_month_timestamp = strtotime('+1 month', current_time('timestamp')); //get the end of this month + 1 day $start_month = date('Y-m-1', $start_month_timestamp); $end_month = date('Y-m-t', $start_month_timestamp); $conditions['scope'] = " (event_start_date BETWEEN CAST('{$start_month}' AS DATE) AND CAST('{$end_month}' AS DATE))"; if (!get_option('dbem_events_current_are_past')) { $conditions['scope'] .= " OR (event_start_date < CAST('{$start_month}' AS DATE) AND event_end_date >= CAST('{$start_month}' AS DATE))"; } } elseif (preg_match('/([0-9]+)\\-months/', $scope, $matches)) { // next x months means this month (what's left of it), plus the following x months until the end of that month. $months_to_add = $matches[1]; $start_month = date('Y-m-d', current_time('timestamp')); $end_month = date('Y-m-t', strtotime("+{$months_to_add} month", current_time('timestamp'))); $conditions['scope'] = " (event_start_date BETWEEN CAST('{$start_month}' AS DATE) AND CAST('{$end_month}' AS DATE))"; if (!get_option('dbem_events_current_are_past')) { $conditions['scope'] .= " OR (event_start_date < CAST('{$start_month}' AS DATE) AND event_end_date >= CAST('{$start_month}' AS DATE))"; } } elseif ($scope == "future") { $conditions['scope'] = " event_start_date >= CAST('{$today}' AS DATE)"; if (!get_option('dbem_events_current_are_past')) { $conditions['scope'] .= " OR (event_end_date >= CAST('{$today}' AS DATE) AND event_end_date != '0000-00-00' AND event_end_date IS NOT NULL)"; } } if (!empty($conditions['scope'])) { $conditions['scope'] = '(' . $conditions['scope'] . ')'; } } //Filter by Location - can be object, array, or id if (is_numeric($location) && $location > 0) { //Location ID takes precedence $conditions['location'] = " {$locations_table}.location_id = {$location}"; } elseif ($location === 0) { //only helpful is searching events $conditions['location'] = " {$events_table}.location_id = {$location} OR {$events_table}.location_id IS NULL"; } elseif (self::array_is_numeric($location)) { $conditions['location'] = "( {$locations_table}.location_id = " . implode(" OR {$locations_table}.location_id = ", $location) . ' )'; } elseif (is_object($location) && get_class($location) == 'EM_Location') { //Now we deal with objects $conditions['location'] = " {$locations_table}.location_id = {$location->location_id}"; } elseif (is_array($location) && @get_class(current($location) == 'EM_Location')) { //we can accept array of ids or EM_Location objects foreach ($location as $EM_Location) { $location_ids[] = $EM_Location->location_id; } $conditions['location'] = "( {$locations_table}.location_id=" . implode(" {$locations_table}.location_id=", $location_ids) . " )"; } //Filter by Event - can be object, array, or id if (is_numeric($event) && $event > 0) { //event ID takes precedence $conditions['event'] = " {$events_table}.event_id = {$event}"; } elseif (self::array_is_numeric($event)) { //array of ids $conditions['event'] = "( {$events_table}.event_id = " . implode(" OR {$events_table}.event_id = ", $event) . ' )'; } elseif (is_object($event) && get_class($event) == 'EM_Event') { //Now we deal with objects $conditions['event'] = " {$events_table}.event_id = {$event->event_id}"; } elseif (is_array($event) && @get_class(current($event) == 'EM_Event')) { //we can accept array of ids or EM_event objects foreach ($event as $EM_Event) { $event_ids[] = $EM_Event->event_id; } $conditions['event'] = "( {$events_table}.event_id=" . implode(" {$events_table}.event_id=", $event_ids) . " )"; } //Location specific filters //country lookup if (!empty($args['country'])) { $countries = em_get_countries(); //we can accept country codes or names if (in_array($args['country'], $countries)) { //we have a country name, $conditions['country'] = "location_country='" . array_search($args['country'], $countries) . "'"; } elseif (array_key_exists($args['country'], $countries)) { //we have a country code $conditions['country'] = "location_country='" . $args['country'] . "'"; } } //state lookup if (!empty($args['state'])) { $conditions['state'] = "location_state='" . $args['state'] . "'"; } //state lookup if (!empty($args['town'])) { $conditions['town'] = "location_town='" . $args['town'] . "'"; } //region lookup if (!empty($args['region'])) { $conditions['region'] = "location_region='" . $args['region'] . "'"; } //START TAXONOMY FILTERS - can be id, slug, name or comma seperated ids/slugs/names, if negative or prepended with a - then considered a negative filter $taxonomies = self::get_taxonomies(); foreach ($taxonomies as $tax_name => $tax_data) { if (!empty($args[$tax_name]) && is_array($args[$tax_name])) { if (!empty($tax_data['ms'])) { self::ms_global_switch(); } //if in ms global mode, switch here rather than on each EM_Category instance //build array of term ids and negative ids from supplied argument $term_tax_ids = $term_ids = array(); $term_tax_not_ids = $term_not_ids = array(); foreach ($args[$tax_name] as $tax_id) { $tax_id_clean = preg_replace('/^-/', '', $tax_id); if (!is_numeric($tax_id_clean)) { $term = get_term_by('slug', $tax_id_clean, $tax_data['name']); if (empty($term)) { $term = get_term_by('name', $tax_id_clean, $tax_data['name']); } } else { $term = get_term_by('id', $tax_id_clean, $tax_data['name']); } if (!empty($term->term_taxonomy_id)) { if (!preg_match('/^-/', $tax_id)) { $term_tax_ids[] = $term->term_taxonomy_id; if (EM_MS_GLOBAL && !empty($tax_data['ms'])) { $term_ids[] = $term->term_id; } } else { $term_tax_not_ids[] = $term->term_taxonomy_id; if (EM_MS_GLOBAL && !empty($tax_data['ms'])) { $term_not_ids[] = $term->term_id; } } } } if (!empty($tax_data['ms'])) { self::ms_global_switch_back(); } //switch back if ms global mode //create sql conditions if (count($term_tax_ids) > 0 || count($term_tax_not_ids) > 0) { //figure out context - what table/field to search $post_context = EM_EVENTS_TABLE . ".post_id"; $ms_context = EM_EVENTS_TABLE . ".event_id"; if (!empty($tax_data['context']) && self::$context == EM_POST_TYPE_LOCATION && in_array(self::$context, $tax_data['context'])) { //context can be either locations or events, since those are the only two CPTs we deal with $post_context = EM_LOCATIONS_TABLE . ".post_id"; $ms_context = EM_LOCATIONS_TABLE . ".event_id"; } //build conditions $tax_conds = array(); if (EM_MS_GLOBAL && !empty($tax_data['ms'])) { //by default only applies to categories //we're directly looking for tax ids from within the em_meta table if (count($term_ids) > 0) { $tax_conds[] = "{$ms_context} IN ( SELECT object_id FROM " . EM_META_TABLE . " WHERE meta_value IN (" . implode(',', $term_ids) . ") AND meta_key='{$tax_data['ms']}' )"; } if (count($term_not_ids) > 0) { $tax_conds[] = "{$ms_context} NOT IN ( SELECT object_id FROM " . EM_META_TABLE . " WHERE meta_value IN (" . implode(',', $term_not_ids) . ") AND meta_key='{$tax_data['ms']}' )"; } } else { //normal taxonomy filtering if (count($term_tax_ids) > 0) { $tax_conds[] = "{$post_context} IN ( SELECT object_id FROM " . $wpdb->term_relationships . " WHERE term_taxonomy_id IN (" . implode(',', $term_tax_ids) . ") )"; } if (count($term_tax_not_ids) > 0) { $tax_conds[] = "{$post_context} NOT IN ( SELECT object_id FROM " . $wpdb->term_relationships . " WHERE term_taxonomy_id IN (" . implode(',', $term_tax_not_ids) . ") )"; } } if (count($tax_conds) > 0) { $conditions[$tax_name] = '(' . implode(' AND ', $tax_conds) . ')'; } } else { $conditions = array('taxonomy' => '2=1'); //force a false, supplied taxonomies don't exist break; //no point continuing this loop } } } //END TAXONOMY FILTERS //If we want rsvped items, we usually check the event if ($bookings == 1) { $conditions['bookings'] = 'event_rsvp=1'; } //Default ownership belongs to an event, child objects can just overwrite this if needed. if (is_numeric($owner)) { $conditions['owner'] = 'event_owner=' . $owner; } elseif ($owner == 'me' && is_user_logged_in()) { $conditions['owner'] = 'event_owner=' . get_current_user_id(); } elseif ($owner == 'me' && !is_user_logged_in()) { $conditions = array('owner' => '1=2'); //no events to be shown } //reset the context self::$context = EM_POST_TYPE_EVENT; //return values return apply_filters('em_object_build_sql_conditions', $conditions); }