protected function build()
{
$this->select[] = 'area_information.*';
if ($this->site) {
$this->where[] = " area_information.site_id = :site_id ";
$this->params['site_id'] = $this->site->getId();
}
if ($this->country) {
$this->where[] = " area_information.country_id = :country_id ";
$this->params['country_id'] = $this->country->getId();
}
if (!$this->include_deleted) {
$this->where[] = " area_information.is_deleted = '0' ";
}
if ($this->noParentArea) {
$this->where[] = ' area_information.parent_area_id IS null ';
} else {
if ($this->parentArea) {
$this->where[] = " area_information.parent_area_id = :parent_id ";
$this->params['parent_id'] = $this->parentArea->getId();
}
}
if ($this->cacheNeedsBuildingOnly) {
$this->where[] = " area_information.cache_area_has_parent_generated = '0'";
}
if ($this->freeTextSearch) {
$this->where[] = ' area_information.title ILIKE :free_text_search ';
$this->params['free_text_search'] = "%" . strtolower($this->freeTextSearch) . "%";
}
if ($this->include_parent_levels > 0) {
$this->joins[] = " LEFT JOIN area_information AS area_information_parent_1 ON area_information.parent_area_id = area_information_parent_1.id ";
$this->select[] = " area_information_parent_1.title AS parent_1_title";
}
}
protected function build()
{
if ($this->site) {
$this->where[] = " human_information.site_id = :site_id ";
$this->params['site_id'] = $this->site->getId();
}
if ($this->humansForEvent) {
$this->joins[] = " JOIN event_has_human ON event_has_human.human_id = human_information.id AND event_has_human.event_id = :event_id AND event_has_human.removed_at IS NULL";
$this->params['event_id'] = $this->humansForEvent->getId();
} else {
if ($this->humansNotForEvent) {
$this->joins[] = " LEFT JOIN event_has_human ON event_has_human.human_id = human_information.id AND event_has_human.event_id = :event_id AND event_has_human.removed_at IS NULL";
$this->params['event_id'] = $this->humansNotForEvent->getId();
$this->where[] = ' event_has_human.event_id IS NULL ';
}
}
if ($this->area) {
// TODO direct areas only, should do child areas to. But not now.
$this->joins[] = " JOIN human_in_area ON human_in_area.human_id = human_information.id AND human_in_area.area_id = :area_id AND human_in_area.removed_at IS NULL";
$this->params['area_id'] = $this->area->getId();
}
if (!$this->include_deleted) {
$this->where[] = " human_information.is_deleted = '0' ";
}
if ($this->freeTextSearch) {
$this->where[] = '(CASE WHEN human_information.title IS NULL THEN \'\' ELSE human_information.title END ) || \' \' || ' . '(CASE WHEN human_information.description IS NULL THEN \'\' ELSE human_information.description END )' . ' ILIKE :free_text_search ';
$this->params['free_text_search'] = "%" . strtolower($this->freeTextSearch) . "%";
}
}
/**
* This will always return something. If one doesn't exist, one will be created.
* @return UserWatchesSiteStopModel
*/
public function getForUserAndArea(UserAccountModel $user, AreaModel $area)
{
global $DB;
$stat = $DB->prepare("SELECT * FROM user_watches_area_stop WHERE user_account_id=:uid AND area_id=:gid");
$stat->execute(array('uid' => $user->getId(), 'gid' => $area->getId()));
if ($stat->rowCount() > 0) {
$uwgs = new UserWatchesAreaStopModel();
$uwgs->setFromDataBaseRow($stat->fetch());
return $uwgs;
}
$uwgs = new UserWatchesAreaStopModel();
$uwgs->setUserAccountId($user->getId());
$uwgs->setAreaId($area->getId());
$uwgs->setAccessKey(createKey(2, 150));
// TODO check not already used
$stat = $DB->prepare("INSERT INTO user_watches_area_stop (user_account_id, area_id, access_key, created_at) " . "VALUES (:user_account_id, :area_id, :access_key, :created_at)");
$stat->execute(array('user_account_id' => $uwgs->getUserAccountId(), 'area_id' => $uwgs->getAreaId(), 'access_key' => $uwgs->getAccessKey(), 'created_at' => \TimeSource::getFormattedForDataBase()));
return $uwgs;
}
public function create(AreaCommentModel $areaComment, AreaModel $area, UserAccountModel $creator)
{
global $DB;
try {
$DB->beginTransaction();
$stat = $DB->prepare("SELECT max(slug) AS c FROM area_comment_information WHERE area_id=:area_id");
$stat->execute(array('area_id' => $area->getId()));
$data = $stat->fetch();
$areaComment->setSlug($data['c'] + 1);
$stat = $DB->prepare("INSERT INTO area_comment_information (area_id, slug, title, comment, user_account_id, is_deleted, created_at, approved_at) " . "VALUES (:area_id, :slug, :title, :comment, :user_account_id, '0', :created_at, :approved_at) RETURNING id");
$stat->execute(array('area_id' => $area->getId(), 'slug' => $areaComment->getSlug(), 'title' => substr($areaComment->getTitle(), 0, VARCHAR_COLUMN_LENGTH_USED), 'comment' => $areaComment->getComment(), 'user_account_id' => $creator->getId(), 'created_at' => \TimeSource::getFormattedForDataBase(), 'approved_at' => \TimeSource::getFormattedForDataBase()));
$data = $stat->fetch();
$areaComment->setId($data['id']);
$stat = $DB->prepare("INSERT INTO area_comment_history (area_comment_id, title, comment, user_account_id, is_deleted, created_at, approved_at, is_new) VALUES " . "(:area_comment_id, :title, :comment, :user_account_id, '0', :created_at, :approved_at, '1')");
$stat->execute(array('area_comment_id' => $areaComment->getId(), 'title' => substr($areaComment->getTitle(), 0, VARCHAR_COLUMN_LENGTH_USED), 'comment' => $areaComment->getComment(), 'user_account_id' => $creator->getId(), 'created_at' => \TimeSource::getFormattedForDataBase(), 'approved_at' => \TimeSource::getFormattedForDataBase()));
$DB->commit();
} catch (Exception $e) {
$DB->rollBack();
}
}
protected function build()
{
global $DB;
$this->select[] = " venue_information.* ";
if ($this->site) {
$this->where[] = " venue_information.site_id = :site_id ";
$this->params['site_id'] = $this->site->getId();
}
if ($this->country) {
$this->where[] = " venue_information.country_id = :country_id ";
$this->params['country_id'] = $this->country->getId();
}
if ($this->area) {
$areaids = array($this->area->getId());
$this->statAreas = $DB->prepare("SELECT area_id FROM cached_area_has_parent WHERE has_parent_area_id=:id");
$this->statAreas->execute(array('id' => $this->area->getId()));
while ($d = $this->statAreas->fetch()) {
$areaids[] = $d['area_id'];
}
$this->where[] = " venue_information.area_id IN (" . implode(",", $areaids) . ")";
}
if ($this->freeTextSearch) {
$this->where[] = '(CASE WHEN venue_information.title IS NULL THEN \'\' ELSE venue_information.title END ) || \' \' || ' . '(CASE WHEN venue_information.description IS NULL THEN \'\' ELSE venue_information.description END ) || \' \' || ' . '(CASE WHEN venue_information.address IS NULL THEN \'\' ELSE venue_information.address END ) || \' \' || ' . '(CASE WHEN venue_information.address_code IS NULL THEN \'\' ELSE venue_information.address_code END ) ' . ' ILIKE :free_text_search ';
$this->params['free_text_search'] = "%" . strtolower($this->freeTextSearch) . "%";
}
if ($this->freeTextSearchAddressCode) {
$this->where[] = ' venue_information.address_code ILIKE :free_text_search_address_code ';
$this->params['free_text_search_address_code'] = "%" . strtolower($this->freeTextSearchAddressCode) . "%";
}
if ($this->freeTextSearchAddress) {
$this->where[] = 'venue_information.address ILIKE :free_text_search_address ';
$this->params['free_text_search_address'] = "%" . strtolower($this->freeTextSearchAddress) . "%";
}
if ($this->freeTextSearchTitle) {
$this->where[] = 'venue_information.title ILIKE :free_text_search_title ';
$this->params['free_text_search_title'] = "%" . strtolower($this->freeTextSearchTitle) . "%";
}
if (!$this->include_deleted) {
$this->where[] = " venue_information.is_deleted = '0' ";
}
if ($this->includeMediasSlugs) {
$this->select[] = " (SELECT array_to_string(array_agg(media_information.slug), ',') FROM media_information " . " JOIN media_in_venue ON media_information.id = media_in_venue.media_id " . " WHERE media_information.deleted_at IS NULL AND media_information.is_file_lost='0' " . " AND media_in_venue.removal_approved_at IS NULL AND media_in_venue.venue_id = venue_information.id " . " GROUP BY venue_information.id ) AS media_venue_slugs ";
}
if ($this->must_have_lat_lng) {
$this->where[] = " venue_information.lat IS NOT NULL ";
$this->where[] = " venue_information.lng IS NOT NULL ";
}
if ($this->editedByUser) {
$this->where[] = " venue_information.id IN (SELECT venue_id FROM venue_history WHERE user_account_id = :editedByUser) ";
$this->params['editedByUser'] = $this->editedByUser->getId();
}
}
protected function newEventFromImportedEventModel(ImportedEventModel $importedEvent)
{
$event = new EventModel();
$event->setFromImportedEventModel($importedEvent);
if ($this->site->getIsFeaturePhysicalEvents() && !$this->site->getIsFeatureVirtualEvents()) {
$event->setIsPhysical(true);
$event->setIsVirtual(false);
} else {
if (!$this->site->getIsFeaturePhysicalEvents() && $this->site->getIsFeatureVirtualEvents()) {
$event->setIsPhysical(false);
$event->setIsVirtual(true);
}
}
if ($this->country) {
// country is set on importer.
$event->setCountryId($this->country->getId());
$timezones = $this->country->getTimezonesAsList();
if ($importedEvent->getTimezone() && in_array($importedEvent->getTimezone(), $timezones)) {
$event->setTimezone($importedEvent->getTimezone());
} else {
if ($timezones) {
// take first timezone in that country at random :-/
$event->setTimezone($timezones[0]);
}
}
if ($this->area) {
$event->setAreaId($this->area->getId());
}
} else {
// if no country set on importer, we just pick first one at random :-/
$crb = new \repositories\builders\CountryRepositoryBuilder();
$crb->setSiteIn($this->site);
$crb->setLimit(1);
$countries = $crb->fetchAll();
if (count($countries) > 0) {
$country = $countries[0];
$event->setCountryId($country->getId());
$timezones = $country->getTimezonesAsList();
if ($importedEvent->getTimezone() && in_array($importedEvent->getTimezone(), $timezones)) {
$event->setTimezone($importedEvent->getTimezone());
} else {
if ($timezones) {
// take first timezone in that country at random :-/
$event->setTimezone($timezones[0]);
}
}
}
}
return $event;
}
protected function build()
{
$this->select[] = 'user_account_information.*';
$this->groupNeeded = false;
if ($this->requestAccessSite) {
$this->joins[] = " LEFT JOIN user_in_site_information ON user_in_site_information.user_account_id = user_account_information.id ";
$this->joins[] = " JOIN site_access_request ON site_access_request.user_account_id = user_account_information.id AND " . " site_access_request.granted_by IS NULL AND site_access_request.rejected_by IS NULL AND site_access_request.site_id = :site_id";
$this->where[] = " ( user_in_site_information.user_account_id IS NULL OR " . "(user_in_site_information.is_editor != '1' AND user_in_site_information.is_administrator != '1' " . " AND user_in_site_information.is_owner != '1' ))";
$this->params['site_id'] = $this->requestAccessSite->getId();
$this->groupNeeded = true;
}
if ($this->editNotOwnCuratedList) {
$this->joins[] = " JOIN user_in_curated_list_information ON user_in_curated_list_information.user_account_id = user_account_information.id " . "AND user_in_curated_list_information.curated_list_id = :curated_list_id ";
$this->params['curated_list_id'] = $this->editNotOwnCuratedList->getId();
$this->where[] = " user_in_curated_list_information.is_owner = '0' AND user_in_curated_list_information.is_editor = '1' ";
}
if ($this->watchesSite) {
$this->joins[] = " JOIN user_watches_site_information ON " . "user_watches_site_information.user_account_id = user_account_information.id AND " . "user_watches_site_information.site_id = :site_id AND " . "user_watches_site_information.is_watching = '1'";
$this->params['site_id'] = $this->watchesSite->getId();
}
if ($this->watchesGroup) {
$this->joins[] = " JOIN user_watches_group_information ON " . "user_watches_group_information.user_account_id = user_account_information.id AND " . "user_watches_group_information.group_id = :group_id AND " . "user_watches_group_information.is_watching = '1'";
$this->params['group_id'] = $this->watchesGroup->getId();
}
if ($this->watchesArea) {
$this->joins[] = " JOIN user_watches_area_information ON " . "user_watches_area_information.user_account_id = user_account_information.id AND " . "user_watches_area_information.area_id = :area_id AND " . "user_watches_area_information.is_watching = '1'";
$this->params['area_id'] = $this->watchesArea->getId();
}
if ($this->inUserGroup) {
$this->joins[] = " JOIN user_in_user_group ON user_in_user_group.user_account_id = user_account_information.id " . "AND user_in_user_group.user_group_id = :user_group_id AND user_in_user_group.removed_at IS NULL ";
$this->params['user_group_id'] = $this->inUserGroup->getId();
}
if ($this->userHasNoEditorPermissionsInSite) {
$this->joins[] = " JOIN user_has_no_editor_permissions_in_site ON user_has_no_editor_permissions_in_site.user_account_id = user_account_information.id " . "AND user_has_no_editor_permissions_in_site.site_id = :no_edit_permissions_in_site_id AND user_has_no_editor_permissions_in_site.removed_at IS NULL ";
$this->params['no_edit_permissions_in_site_id'] = $this->userHasNoEditorPermissionsInSite->getId();
}
if ($this->isOpenBySysAdminsOnly) {
$this->where[] = " user_account_information.is_closed_by_sys_admin = '0' ";
}
}
protected function build()
{
global $DB;
$this->select[] = 'event_information.*';
$this->select[] = " group_information.title AS group_title ";
$this->select[] = " group_information.id AS group_id ";
$this->joins[] = " LEFT JOIN event_in_group ON event_in_group.event_id = event_information.id AND event_in_group.removed_at IS NULL AND event_in_group.is_main_group = '1' ";
$this->joins[] = " LEFT JOIN group_information ON group_information.id = event_in_group.group_id ";
$joinsVenueInfoSQL = " LEFT JOIN venue_information ON venue_information.id = event_information.venue_id ";
if ($this->site) {
$this->where[] = " event_information.site_id = :site_id ";
$this->params['site_id'] = $this->site->getId();
}
if ($this->group) {
// We use a seperate table here so if event is in 2 groups and we select events in 1 group that isn't the main group only,
// the normal event_in_group table still shows the main group.
$this->joins[] = " JOIN event_in_group AS event_in_group_select ON event_in_group_select.event_id = event_information.id " . "AND event_in_group_select.removed_at IS NULL AND event_in_group_select.group_id = :group_id ";
$this->params['group_id'] = $this->group->getId();
}
if ($this->country) {
$this->where[] = " event_information.country_id = :country_id ";
$this->params['country_id'] = $this->country->getId();
}
if ($this->area) {
// We were doing
// $this->joins[] = " LEFT JOIN cached_area_has_parent ON cached_area_has_parent.area_id = venue_information.area_id";
// $this->where[] = " (venue_information.area_id = :area_id OR cached_area_has_parent.has_parent_area_id = :area_id )";
// but then we got duplicates
$areaids = array($this->area->getId());
$this->statAreas = $DB->prepare("SELECT area_id FROM cached_area_has_parent WHERE has_parent_area_id=:id");
$this->statAreas->execute(array('id' => $this->area->getId()));
while ($d = $this->statAreas->fetch()) {
$areaids[] = $d['area_id'];
}
$this->joins[] = $joinsVenueInfoSQL;
$this->where[] = " (venue_information.area_id IN (" . implode(",", $areaids) . ") " . "OR event_information.area_id IN (" . implode(",", $areaids) . ")) ";
}
if ($this->venue) {
$this->where[] = " event_information.venue_id = :venue_id ";
$this->params['venue_id'] = $this->venue->getId();
}
if ($this->importURL) {
$this->joins[] = " LEFT JOIN imported_event_is_event ON imported_event_is_event.event_id = event_information.id ";
$this->joins[] = " LEFT JOIN imported_event ON imported_event.id = imported_event_is_event.imported_event_id ";
$this->where[] = " (imported_event.import_url_id = :import_url_id OR event_information.import_url_id = :import_url_id )";
$this->params['import_url_id'] = $this->importURL->getId();
}
if ($this->importedEvent) {
$this->joins[] = " JOIN imported_event_is_event ON imported_event_is_event.event_id = event_information.id AND imported_event_is_event.imported_event_id = :imported_event_id ";
$this->params['imported_event_id'] = $this->importedEvent->getId();
}
if (!$this->site && !$this->group) {
$this->joins[] = " JOIN site_information ON event_information.site_id = site_information.id ";
$this->select[] = " site_information.slug AS site_slug ";
if (!$this->includeEventsFromClosedSites) {
$this->where[] = " site_information.is_closed_by_sys_admin = '0' ";
}
}
if ($this->curatedList) {
$this->joins[] = " LEFT JOIN event_in_curated_list ON event_in_curated_list.event_id = event_information.id " . " AND event_in_curated_list.removed_at IS NULL AND event_in_curated_list.curated_list_id = :curated_list";
$this->joins[] = " LEFT JOIN ( SELECT event_in_group.event_id, MAX(event_in_group.group_id) AS group_id" . " FROM event_in_group " . " JOIN group_in_curated_list ON group_in_curated_list.group_id = event_in_group.group_id " . " WHERE group_in_curated_list.curated_list_id = :curated_list AND group_in_curated_list.removed_at IS NULL AND event_in_group.removed_at IS NULL " . " GROUP BY event_in_group.event_id " . ") AS event_in_group_in_curated_list ON event_in_group_in_curated_list.event_id = event_information.id ";
$this->where[] = " ( event_in_curated_list.curated_list_id IS NOT NULL OR event_in_group_in_curated_list.event_id IS NOT NULL )";
$this->params['curated_list'] = $this->curatedList->getId();
if ($this->curatedListInformation) {
$this->joins[] = " LEFT JOIN group_information AS group_information_cl ON group_information_cl.id = event_in_group_in_curated_list.group_id ";
$this->select[] = " (CASE WHEN event_in_curated_list.event_id IS NULL THEN 0 ELSE 1 END) AS is_event_in_curated_list ";
$this->select[] = " group_information_cl.id AS in_curated_list_group_id ";
$this->select[] = " group_information_cl.slug AS in_curated_list_group_slug ";
$this->select[] = " group_information_cl.title AS in_curated_list_group_title ";
}
}
if ($this->end) {
$this->where[] = ' event_information.end_at = :end';
$this->params['end'] = $this->end->format("Y-m-d H:i:s");
} else {
if ($this->after) {
$this->where[] = ' event_information.end_at > :after';
$this->params['after'] = $this->after->format("Y-m-d H:i:s");
} else {
if ($this->endBefore) {
$this->where[] = ' event_information.end_at < :before';
$this->params['before'] = $this->endBefore->format("Y-m-d H:i:s");
}
}
}
if ($this->start) {
$this->where[] = ' event_information.start_at = :start';
$this->params['start'] = $this->start->format("Y-m-d H:i:s");
} else {
if ($this->before) {
$this->where[] = ' event_information.start_at < :before';
$this->params['before'] = $this->before->format("Y-m-d H:i:s");
} else {
if ($this->startAfter) {
$this->where[] = ' event_information.start_at > :startAfter';
$this->params['startAfter'] = $this->startAfter->format("Y-m-d H:i:s");
}
}
}
if (!$this->include_deleted) {
$this->where[] = " event_information.is_deleted = '0' ";
}
if (!$this->include_cancelled) {
$this->where[] = " event_information.is_cancelled = '0' ";
}
if (!$this->include_imported) {
$this->where[] = " event_information.import_url_id is null ";
}
if ($this->userAccount) {
// user at event. we want info on this always for the extra selects, so outside if statement
$this->joins[] = " LEFT JOIN user_at_event_information ON user_at_event_information.event_id = event_information.id " . "AND user_at_event_information.user_account_id = :user_account_id ";
$this->select[] = " user_at_event_information.is_plan_attending AS user_is_plan_attending ";
$this->select[] = " user_at_event_information.is_plan_maybe_attending AS user_is_plan_maybe_attending ";
if (!$this->userAccountIncludeAll) {
$w = array();
if ($this->userAccountIncludeWatching) {
$w[] = " event_information.id IN (SELECT event_information.id FROM event_information " . " LEFT JOIN user_watches_site_information ON user_watches_site_information.site_id = event_information.site_id " . "AND user_watches_site_information.user_account_id = :user_account_id AND user_watches_site_information.is_watching='1' " . " LEFT JOIN event_in_group ON event_in_group.event_id = event_information.id AND event_in_group.removed_at IS NULL " . " LEFT JOIN user_watches_group_information ON user_watches_group_information.group_id = event_in_group.group_id " . "AND user_watches_group_information.user_account_id = :user_account_id AND user_watches_group_information.is_watching='1' " . " LEFT JOIN venue_information ON venue_information.id = event_information.venue_id " . " LEFT JOIN cached_area_has_parent ON ( venue_information.area_id = cached_area_has_parent.area_id OR event_information.area_id = cached_area_has_parent.area_id) " . " LEFT JOIN user_watches_area_information ON ( " . "user_watches_area_information.area_id = event_information.area_id OR user_watches_area_information.area_id = venue_information.area_id " . " OR user_watches_area_information.area_id = cached_area_has_parent.has_parent_area_id " . ") " . "AND user_watches_area_information.user_account_id = :user_account_id AND user_watches_area_information.is_watching='1' " . " WHERE user_watches_site_information.is_watching='1' OR user_watches_group_information.is_watching='1' OR user_watches_area_information.is_watching='1'" . " ) ";
}
if ($this->userAccountIncludeAttending) {
if ($this->userAccountIncludePrivate) {
$w[] = " user_at_event_information.is_plan_attending = '1' ";
$w[] = " user_at_event_information.is_plan_maybe_attending = '1' ";
} else {
$w[] = " (user_at_event_information.is_plan_attending = '1' AND user_at_event_information.is_plan_public = '1' )";
$w[] = " (user_at_event_information.is_plan_maybe_attending = '1' AND user_at_event_information.is_plan_public = '1' )";
}
}
$this->where[] = " ( " . implode(" OR ", $w) . ") ";
}
$this->params['user_account_id'] = $this->userAccount->getId();
}
if ($this->include_venue_information || $this->include_area_information || $this->must_have_lat_lng) {
if (!in_array($joinsVenueInfoSQL, $this->joins)) {
$this->joins[] = $joinsVenueInfoSQL;
}
if ($this->include_venue_information) {
$this->select[] = " venue_information.lng AS venue_lng";
$this->select[] = " venue_information.lat AS venue_lat";
$this->select[] = " venue_information.title AS venue_title";
$this->select[] = " venue_information.slug AS venue_slug";
$this->select[] = " venue_information.description AS venue_description";
$this->select[] = " venue_information.address AS venue_address";
$this->select[] = " venue_information.address_code AS venue_address_code";
}
if ($this->include_area_information) {
$this->joins[] = " LEFT JOIN area_information ON area_information.id = event_information.area_id OR area_information.id = venue_information.area_id";
$this->select[] = " area_information.title AS area_title";
$this->select[] = " area_information.slug AS area_slug";
$this->select[] = " area_information.id AS area_information_id";
// we already have a area_id so called something different
}
if ($this->must_have_lat_lng) {
$this->where[] = " venue_information.lat IS NOT NULL ";
$this->where[] = " venue_information.lng IS NOT NULL ";
}
}
if ($this->include_country_information) {
$this->joins[] = " LEFT JOIN country ON country.id = event_information.country_id ";
$this->select[] = " country.two_char_code AS country_two_char_code";
$this->select[] = " country.title AS country_title";
}
if ($this->venueVirtualOnly) {
$this->where[] = " event_information.is_virtual = '1' ";
}
if ($this->event_recur_set_id) {
$this->where[] = " event_information.event_recur_set_id = :event_recur_set_id ";
$this->params['event_recur_set_id'] = $this->event_recur_set_id;
}
if ($this->tag) {
$this->joins[] = " JOIN event_has_tag ON event_has_tag.event_id = event_information.id AND event_has_tag.tag_id = :tag_id AND event_has_tag.removed_at IS NULL";
$this->params['tag_id'] = $this->tag->getId();
}
if ($this->freeTextSearch) {
$this->where[] = '(CASE WHEN event_information.summary IS NULL THEN \'\' ELSE event_information.summary END) || ' . '\' \' || ' . '(CASE WHEN event_information.description IS NULL THEN \'\' ELSE event_information.description END) || ' . '\' \' || ' . '(CASE WHEN group_information.title IS NULL THEN \'\' ELSE group_information.title END)' . ' ILIKE :free_text_search ';
$this->params['free_text_search'] = "%" . strtolower($this->freeTextSearch) . "%";
}
if ($this->includeMediasSlugs) {
$this->select[] = " (SELECT array_to_string(array_agg(media_information.slug), ',') FROM media_information " . " JOIN media_in_event ON media_information.id = media_in_event.media_id " . " WHERE media_information.deleted_at IS NULL AND media_information.is_file_lost='0' " . " AND media_in_event.removal_approved_at IS NULL AND media_in_event.event_id = event_information.id " . " GROUP BY event_information.id ) AS media_event_slugs ";
$this->select[] = " (SELECT array_to_string(array_agg(media_information.slug), ',') FROM media_information " . " JOIN media_in_group ON media_information.id = media_in_group.media_id " . " JOIN event_in_group ON event_in_group.group_id = media_in_group.group_id " . " WHERE media_information.deleted_at IS NULL AND media_information.is_file_lost='0' " . " AND media_in_group.removal_approved_at IS NULL " . " AND event_in_group.removal_approved_at IS NULL AND event_in_group.event_id = event_information.id " . " GROUP BY event_information.id ) AS media_group_slugs ";
$this->select[] = " (SELECT array_to_string(array_agg(media_information.slug), ',') FROM media_information " . " JOIN media_in_venue ON media_information.id = media_in_venue.media_id " . " WHERE media_information.deleted_at IS NULL AND media_information.is_file_lost='0' " . " AND media_in_venue.removal_approved_at IS NULL AND media_in_venue.venue_id = event_information.venue_id " . " GROUP BY event_information.venue_id ) AS media_venue_slugs ";
}
if ($this->editedByUser) {
$this->where[] = " event_information.id IN (SELECT event_id FROM event_history WHERE user_account_id = :editedByUser) ";
$this->params['editedByUser'] = $this->editedByUser->getId();
}
}
public function markDuplicateWithMetaData(AreaModel $duplicateArea, AreaModel $originalArea, AreaEditMetaDataModel $areaEditMetaDataModel)
{
global $DB;
if ($duplicateArea->getId() == $originalArea->getId()) {
return;
}
try {
$DB->beginTransaction();
$duplicateArea->setIsDuplicateOfId($originalArea->getId());
$duplicateArea->setIsDeleted(true);
$this->areaDBAccess->update($duplicateArea, array('is_duplicate_of_id', 'is_deleted'), $areaEditMetaDataModel);
// Move Venues
$venueDBAccess = new VenueDBAccess($DB, new \TimeSource());
$vrb = new VenueRepositoryBuilder();
$vrb->setArea($duplicateArea);
$venueEditMetaData = new VenueEditMetaDataModel();
$venueEditMetaData->setUserAccount($areaEditMetaDataModel->getUserAccount());
foreach ($vrb->fetchAll() as $venue) {
$venue->setAreaId($originalArea->getId());
$venueDBAccess->update($venue, array('area_id'), $venueEditMetaData);
}
// Move Events
$eventRepoBuilder = new EventRepositoryBuilder();
$eventRepoBuilder->setArea($duplicateArea);
$eventDBAccess = new EventDBAccess($DB, new \TimeSource());
$eventEditMetaData = new EventEditMetaDataModel();
$eventEditMetaData->setUserAccount($areaEditMetaDataModel->getUserAccount());
foreach ($eventRepoBuilder->fetchAll() as $event) {
// Check Area actually matches here because we may get events at a venue.
// Based on the order we do things in (ie Move Venue, Move Event) we shouldn't but let's be safe.
if ($event->getAreaId() == $duplicateArea->getId() && $event->getVenueId() == null) {
$event->setAreaId($originalArea->getId());
$eventDBAccess->update($event, array('area_id'), $eventEditMetaData);
}
}
// Move Child Areas
$areaRepoBuilder = new AreaRepositoryBuilder();
$areaRepoBuilder->setParentArea($duplicateArea);
$areaRepoBuilder->setIncludeParentLevels(0);
$flag = false;
foreach ($areaRepoBuilder->fetchAll() as $area) {
// lets just double check we haven't got any child areas.
if ($area->getParentAreaId() == $duplicateArea->getId()) {
$area->setParentAreaId($originalArea->getId());
$this->areaDBAccess->update($area, array('parent_area_id'), $areaEditMetaDataModel);
$flag = true;
}
}
if ($flag) {
// now must clear caches
$this->deleteParentCacheForArea($originalArea);
$this->deleteParentCacheForArea($duplicateArea);
}
$DB->commit();
} catch (Exception $e) {
$DB->rollBack();
}
}
public function removeHumanFromAllButOneArea(HumanModel $human, AreaModel $area, UserAccountModel $user = null)
{
global $DB;
$stat = $DB->prepare("UPDATE human_in_area SET removed_by_user_account_id=:removed_by_user_account_id," . " removed_at=:removed_at, removal_approved_at=:removal_approved_at WHERE " . " area_id != :area_id AND human_id=:human_id AND removed_at IS NULL ");
$stat->execute(array('area_id' => $area->getId(), 'human_id' => $human->getId(), 'removed_at' => \TimeSource::getFormattedForDataBase(), 'removal_approved_at' => \TimeSource::getFormattedForDataBase(), 'removed_by_user_account_id' => $user ? $user->getId() : null));
}
public function startUserWatchingAreaIfNotWatchedBefore(UserAccountModel $user, AreaModel $area)
{
$this->startUserWatchingAreaIdIfNotWatchedBefore($user, $area->getId());
}
public function update(AreaModel $area, $fields, AreaEditMetaDataModel $areaEditMetaDataModel)
{
$alreadyInTransaction = $this->db->inTransaction();
// Make Information Data
$fieldsSQL1 = array();
$fieldsParams1 = array('id' => $area->getId());
foreach ($fields as $field) {
$fieldsSQL1[] = " " . $field . "=:" . $field . " ";
if ($field == 'title') {
$fieldsParams1['title'] = substr($area->getTitle(), 0, VARCHAR_COLUMN_LENGTH_USED);
} else {
if ($field == 'description') {
$fieldsParams1['description'] = $area->getDescription();
} else {
if ($field == 'parent_area_id') {
$fieldsParams1['parent_area_id'] = $area->getParentAreaId();
} else {
if ($field == 'country_id') {
$fieldsParams1['country_id'] = $area->getCountryId();
} else {
if ($field == 'is_duplicate_of_id') {
$fieldsParams1['is_duplicate_of_id'] = $area->getIsDuplicateOfId();
} else {
if ($field == 'is_deleted') {
$fieldsParams1['is_deleted'] = $area->getIsDeleted() ? 1 : 0;
}
}
}
}
}
}
}
// Make History Data
$fieldsSQL2 = array('area_id', 'user_account_id', 'created_at', 'approved_at');
$fieldsSQLParams2 = array(':area_id', ':user_account_id', ':created_at', ':approved_at');
$fieldsParams2 = array('area_id' => $area->getId(), 'user_account_id' => $areaEditMetaDataModel->getUserAccount() ? $areaEditMetaDataModel->getUserAccount()->getId() : null, 'created_at' => $this->timesource->getFormattedForDataBase(), 'approved_at' => $this->timesource->getFormattedForDataBase());
if ($areaEditMetaDataModel->getEditComment()) {
$fieldsSQL2[] = ' edit_comment ';
$fieldsSQLParams2[] = ' :edit_comment ';
$fieldsParams2['edit_comment'] = $areaEditMetaDataModel->getEditComment();
}
foreach ($this->possibleFields as $field) {
if (in_array($field, $fields) || $field == 'title') {
$fieldsSQL2[] = " " . $field . " ";
$fieldsSQLParams2[] = " :" . $field . " ";
if ($field == 'title') {
$fieldsParams2['title'] = substr($area->getTitle(), 0, VARCHAR_COLUMN_LENGTH_USED);
} else {
if ($field == 'description') {
$fieldsParams2['description'] = $area->getDescription();
} else {
if ($field == 'country_id') {
$fieldsParams2['country_id'] = $area->getCountryId();
} else {
if ($field == 'parent_area_id') {
$fieldsParams2['parent_area_id'] = $area->getParentAreaId();
} else {
if ($field == 'is_duplicate_of_id') {
$fieldsParams2['is_duplicate_of_id'] = $area->getIsDuplicateOfId();
} else {
if ($field == 'is_deleted') {
$fieldsParams2['is_deleted'] = $area->getIsDeleted() ? 1 : 0;
}
}
}
}
}
}
$fieldsSQL2[] = " " . $field . "_changed ";
$fieldsSQLParams2[] = " 0 ";
} else {
$fieldsSQL2[] = " " . $field . "_changed ";
$fieldsSQLParams2[] = " -2 ";
}
}
try {
if (!$alreadyInTransaction) {
$this->db->beginTransaction();
}
// Information SQL
$stat = $this->db->prepare("UPDATE area_information SET " . implode(",", $fieldsSQL1) . " WHERE id=:id");
$stat->execute($fieldsParams1);
// History SQL
$stat = $this->db->prepare("INSERT INTO area_history (" . implode(",", $fieldsSQL2) . ") VALUES (" . implode(",", $fieldsSQLParams2) . ")");
$stat->execute($fieldsParams2);
if (!$alreadyInTransaction) {
$this->db->commit();
}
} catch (Exception $e) {
if (!$alreadyInTransaction) {
$this->db->rollBack();
}
throw $e;
}
}
