/** * Return a list of entities based on the given search criteria. * In this case, returns entities with the given metadata between two values inclusive * * @param mixed $meta_start_name * @param mixed $meta_end_name * @param mixed $meta_start_value - start of metadata range, must be numerical value * @param mixed $meta_end_value - end of metadata range, must be numerical value * @param string $entity_type The type of entity to look for, eg 'site' or 'object' * @param string $entity_subtype The subtype of the entity. * @param mixed $owner_guid Either one integer user guid or an array of user guids * @param int $container_guid If supplied, the result is restricted to events associated with a specific container * @param int $limit * @param int $offset * @param string $order_by Optional ordering. * @param int $site_guid The site to get entities for. Leave as 0 (default) for the current site; -1 for all sites. * @param boolean $filter Filter by events in personal calendar if true * @param true|false $count If set to true, returns the total number of entities rather than a list. (Default: false) * * @return int|array A list of entities, or a count if $count is set to true */ function event_calendar_get_entities_from_metadata_between($meta_start_name, $meta_end_name, $meta_start_value, $meta_end_value, $entity_type = "", $entity_subtype = "", $owner_guid = 0, $container_guid = 0, $limit = 10, $offset = 0, $order_by = "", $site_guid = 0, $filter = false, $count = false, $region = '-') { global $CONFIG; // This should not be possible, but a sanity check just in case if (!is_numeric($meta_start_value) || !is_numeric($meta_end_value)) { return FALSE; } $meta_start_n = get_metastring_id($meta_start_name); $meta_end_n = get_metastring_id($meta_end_name); if ($region && $region != '-') { $region_n = get_metastring_id('region'); $region_value_n = get_metastring_id($region); if (!$region_n || !$region_value_n) { if ($count) { return 0; } else { return FALSE; } } } $entity_type = sanitise_string($entity_type); $entity_subtype = get_subtype_id($entity_type, $entity_subtype); $limit = (int) $limit; $offset = (int) $offset; //if ($order_by == "") $order_by = "e.time_created desc"; if ($order_by == "") { $order_by = "v.string asc"; } $order_by = sanitise_string($order_by); $site_guid = (int) $site_guid; if (is_array($owner_guid) && count($owner_guid)) { foreach ($owner_guid as $key => $guid) { $owner_guid[$key] = (int) $guid; } } else { $owner_guid = (int) $owner_guid; } if (is_array($container_guid) && count($container_guid)) { foreach ($container_guid as $key => $guid) { $container_guid[$key] = (int) $guid; } } else { $container_guid = (int) $container_guid; } if ($site_guid == 0) { $site_guid = $CONFIG->site_guid; } //$access = get_access_list(); $where = array(); if ($entity_type != "") { $where[] = "e.type='{$entity_type}'"; } if ($entity_subtype) { $where[] = "e.subtype={$entity_subtype}"; } $where[] = "m.name_id='{$meta_start_n}'"; $where[] = "m2.name_id='{$meta_end_n}'"; $where[] = "((v.string >= {$meta_start_value} AND v.string <= {$meta_end_value}) OR ( v2.string >= {$meta_start_value} AND v2.string <= {$meta_end_value}) OR (v.string <= {$meta_start_value} AND v2.string >= {$meta_start_value}) OR ( v2.string <= {$meta_end_value} AND v2.string >= {$meta_end_value}))"; if ($region && $region != '-') { $where[] = "m3.name_id='{$region_n}'"; $where[] = "m3.value_id='{$region_value_n}'"; } if ($site_guid > 0) { $where[] = "e.site_guid = {$site_guid}"; } if ($filter) { if (is_array($owner_guid)) { $where[] = "ms2.string in (" . implode(",", $owner_guid) . ")"; } else { if ($owner_guid > 0) { $where[] = "ms2.string = {$owner_guid}"; } } $where[] = "ms.string = 'personal_event'"; } else { if (is_array($owner_guid)) { $where[] = "e.owner_guid in (" . implode(",", $owner_guid) . ")"; } else { if ($owner_guid > 0) { $where[] = "e.owner_guid = {$owner_guid}"; } } } if (is_array($container_guid)) { $where[] = "e.container_guid in (" . implode(",", $container_guid) . ")"; } else { if ($container_guid > 0) { $where[] = "e.container_guid = {$container_guid}"; } } if (!$count) { $query = "SELECT distinct e.* "; } else { $query = "SELECT count(distinct e.guid) as total "; } $query .= "from {$CONFIG->dbprefix}entities e JOIN {$CONFIG->dbprefix}metadata m on e.guid = m.entity_guid JOIN {$CONFIG->dbprefix}metadata m2 on e.guid = m2.entity_guid "; if ($filter) { $query .= "JOIN {$CONFIG->dbprefix}annotations a ON (a.entity_guid = e.guid) "; $query .= "JOIN {$CONFIG->dbprefix}metastrings ms ON (a.name_id = ms.id) "; $query .= "JOIN {$CONFIG->dbprefix}metastrings ms2 ON (a.value_id = ms2.id) "; } if ($region && $region != '-') { $query .= "JOIN {$CONFIG->dbprefix}metadata m3 ON (e.guid = m3.entity_guid) "; } $query .= "JOIN {$CONFIG->dbprefix}metastrings v on v.id = m.value_id JOIN {$CONFIG->dbprefix}metastrings v2 on v2.id = m2.value_id where"; foreach ($where as $w) { $query .= " {$w} and "; } $query .= get_access_sql_suffix("e"); // Add access controls $query .= ' and ' . get_access_sql_suffix("m"); // Add access controls $query .= ' and ' . get_access_sql_suffix("m2"); // Add access controls if (!$count) { $query .= " order by {$order_by} limit {$offset}, {$limit}"; // Add order and limit $entities = get_data($query, "entity_row_to_elggstar"); if (elgg_get_plugin_setting('add_to_group_calendar', 'event_calendar') == 'yes') { if (get_entity($container_guid) instanceof ElggGroup) { $entities = event_calendar_get_entities_from_metadata_between_related($meta_start_name, $meta_end_name, $meta_start_value, $meta_end_value, $entity_type, $entity_subtype, $owner_guid, $container_guid, $limit = 10, $offset = 0, $order_by = "", $site_guid = 0, $filter = false, $count = false, $region = '-', $entities); } } return $entities; } else { if ($row = get_data_row($query)) { return $row->total; } } return false; }
/** * Return a list of entities based on the given search criteria. * In this case, returns entities with the given metadata between two values inclusive * * @param mixed $meta_start_name * @param mixed $meta_end_name * @param mixed $meta_start_value - start of metadata range, must be numerical value * @param mixed $meta_end_value - end of metadata range, must be numerical value * @param string $entity_type The type of entity to look for, eg 'site' or 'object' * @param string $entity_subtype The subtype of the entity. * @param mixed $owner_guid Either one integer user guid or an array of user guids * @param int $container_guid If supplied, the result is restricted to events associated with a specific container * @param int $limit * @param int $offset * @param string $order_by Optional ordering. * @param int $site_guid The site to get entities for. Leave as 0 (default) for the current site; -1 for all sites. * @param boolean $filter Filter by events in personal calendar if true * @param true|false $count If set to true, returns the total number of entities rather than a list. (Default: false) * @param string $meta_max metadata name containing maximum annotation count * @param string $annotation_name annotation name to count * * @return int|array A list of entities, or a count if $count is set to true * * TODO: see if the new API is robust enough to avoid this custom query */ function event_calendar_get_entities_from_metadata_between2($meta_start_name, $meta_end_name, $meta_start_value, $meta_end_value, $entity_type = "", $entity_subtype = "", $owner_guid = 0, $container_guid = 0, $limit = 10, $offset = 0, $order_by = "", $site_guid = 0, $filter = false, $count = false, $region = '-', $meta_max = '', $annotation_name = '') { global $CONFIG; // This should not be possible, but a sanity check just in case if (!is_numeric($meta_start_value) || !is_numeric($meta_end_value)) { return FALSE; } $meta_start_n = get_metastring_id($meta_start_name); $meta_end_n = get_metastring_id($meta_end_name); if ($region && $region != '-') { $region_n = get_metastring_id('region'); $region_value_n = get_metastring_id($region); if (!$region_n || !$region_value_n) { if ($count) { return 0; } else { return false; } } } $entity_type = sanitise_string($entity_type); $entity_subtype = get_subtype_id($entity_type, $entity_subtype); $limit = (int) $limit; $offset = (int) $offset; //if ($order_by == "") $order_by = "e.time_created desc"; if ($order_by == "") { $order_by = "v.string asc"; } $order_by = sanitise_string($order_by); $site_guid = (int) $site_guid; if (is_array($owner_guid) && count($owner_guid)) { foreach ($owner_guid as $key => $guid) { $owner_guid[$key] = (int) $guid; } } else { $owner_guid = (int) $owner_guid; } if (is_array($container_guid) && count($container_guid)) { foreach ($container_guid as $key => $guid) { $container_guid[$key] = (int) $guid; } } else { $container_guid = (int) $container_guid; } if ($site_guid == 0) { $site_guid = $CONFIG->site_guid; } //$access = get_access_list(); $where = array(); if ($entity_type != "") { $where[] = "e.type='{$entity_type}'"; } if ($entity_subtype) { $where[] = "e.subtype={$entity_subtype}"; } $where[] = "m.name_id='{$meta_start_n}'"; $where[] = "m2.name_id='{$meta_end_n}'"; $where[] = "((v.string >= {$meta_start_value} AND v.string <= {$meta_end_value}) OR ( v2.string >= {$meta_start_value} AND v2.string <= {$meta_end_value}) OR (v.string <= {$meta_start_value} AND v2.string >= {$meta_start_value}) OR ( v2.string <= {$meta_end_value} AND v2.string >= {$meta_end_value}))"; if ($region && $region != '-') { $where[] = "m3.name_id='{$region_n}'"; $where[] = "m3.value_id='{$region_value_n}'"; } if ($site_guid > 0) { $where[] = "e.site_guid = {$site_guid}"; } if ($filter) { if (is_array($owner_guid)) { $where[] = "ms2.string in (" . implode(",", $owner_guid) . ")"; } else { if ($owner_guid > 0) { $where[] = "ms2.string = {$owner_guid}"; } } $where[] = "ms.string = 'personal_event'"; } else { if (is_array($owner_guid)) { $where[] = "e.owner_guid in (" . implode(",", $owner_guid) . ")"; } else { if ($owner_guid > 0) { $where[] = "e.owner_guid = {$owner_guid}"; } } } if (is_array($container_guid)) { $where[] = "e.container_guid in (" . implode(",", $container_guid) . ")"; } else { if ($container_guid > 0) { $where[] = "e.container_guid = {$container_guid}"; } } if (!$count) { $query = "SELECT distinct e.* "; } else { $query = "SELECT count(distinct e.guid) as total "; } $query .= "FROM {$CONFIG->dbprefix}entities e JOIN {$CONFIG->dbprefix}metadata m on e.guid = m.entity_guid JOIN {$CONFIG->dbprefix}metadata m2 on e.guid = m2.entity_guid "; if ($filter) { $query .= "JOIN {$CONFIG->dbprefix}annotations a ON (a.entity_guid = e.guid) "; $query .= "JOIN {$CONFIG->dbprefix}metastrings ms ON (a.name_id = ms.id) "; $query .= "JOIN {$CONFIG->dbprefix}metastrings ms2 ON (a.value_id = ms2.id) "; } if ($region && $region != '-') { $query .= "JOIN {$CONFIG->dbprefix}metadata m3 ON (e.guid = m3.entity_guid) "; } if ($meta_max && $annotation_name) { // This groups events for which the meta max name is defined // perhaps this should be a left join and accept null values? // so it would return groups with no spots defined as well $meta_max_n = get_metastring_id($meta_max); $ann_n = get_metastring_id($annotation_name); if (!$meta_max_n || !$ann_n) { if ($count) { return 0; } else { return false; } } $query .= " LEFT JOIN {$CONFIG->dbprefix}metadata m4 ON (e.guid = m4.entity_guid AND m4.name_id={$meta_max_n}) "; $query .= " LEFT JOIN {$CONFIG->dbprefix}metastrings ms4 ON (m4.value_id = ms4.id) "; $where[] = "((ms4.string is null) OR (ms4.string = \"\") OR (CONVERT(ms4.string,SIGNED) > (SELECT count(id) FROM {$CONFIG->dbprefix}annotations ann WHERE ann.entity_guid = e.guid AND name_id = {$ann_n} GROUP BY entity_guid)))"; } $query .= "JOIN {$CONFIG->dbprefix}metastrings v on v.id = m.value_id JOIN {$CONFIG->dbprefix}metastrings v2 on v2.id = m2.value_id where"; foreach ($where as $w) { $query .= " {$w} AND "; } $query .= get_access_sql_suffix("e"); // Add access controls $query .= ' AND ' . get_access_sql_suffix("m"); // Add access controls $query .= ' AND ' . get_access_sql_suffix("m2"); // Add access controls if (!$count) { $query .= " order by {$order_by}"; if ($limit) { $query .= " limit {$offset}, {$limit}"; // Add order and limit } $entities = get_data($query, "entity_row_to_elggstar"); if (elgg_get_plugin_setting('add_to_group_calendar', 'event_calendar') == 'yes') { if (get_entity($container_guid) instanceof ElggGroup) { $entities = event_calendar_get_entities_from_metadata_between_related($meta_start_name, $meta_end_name, $meta_start_value, $meta_end_value, $entity_type, $entity_subtype, $owner_guid, $container_guid, 0, 0, "", 0, false, false, '-', $entities); } } return $entities; } else { if ($row = get_data_row($query)) { return $row->total; } } return false; }