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