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