protected function build()
{
if ($this->site) {
$this->where[] = " import_url_information.site_id = :site_id ";
$this->params['site_id'] = $this->site->getId();
}
if ($this->group) {
$this->where[] = " import_url_information.group_id = :group_id ";
$this->params['group_id'] = $this->group->getId();
}
}
protected function build()
{
if ($this->onlyCurrent) {
$this->joins[] = " JOIN group_information ON group_information.id = user_watches_group_information.group_id ";
$this->joins[] = " LEFT JOIN user_watches_site_information ON user_watches_site_information.site_id = group_information.site_id " . "AND user_watches_site_information.user_account_id = user_watches_group_information.user_account_id " . " AND user_watches_site_information.is_watching = '1' ";
$this->where[] = " user_watches_group_information.is_watching = '1' AND user_watches_site_information.is_watching IS NULL ";
}
if ($this->group) {
$this->where[] = " user_watches_group_information.group_id = :group_id";
$this->params['group_id'] = $this->group->getId();
}
if ($this->user) {
$this->where[] = " user_watches_group_information.user_account_id = :user_account_id";
$this->params['user_account_id'] = $this->user->getId();
}
}
public function getLastGroupPromptEmailSent(UserWatchesSiteModel $userWatchesSite, GroupModel $group)
{
global $DB;
$stat = $DB->prepare("SELECT MAX(sent_at) AS c FROM user_watches_site_group_prompt_email WHERE user_account_id=:user_account_id AND group_id=:group_id");
$stat->execute(array('user_account_id' => $userWatchesSite->getUserAccountId(), 'group_id' => $group->getId()));
$data = $stat->fetch();
return $data['c'] ? new \DateTime($data['c'], new \DateTimeZone("UTC")) : null;
}
/**
* This will always return something. If one doesn't exist, one will be created.
* @return UserWatchesSiteStopModel
*/
public function getForUserAndGroup(UserAccountModel $user, GroupModel $group)
{
global $DB;
$stat = $DB->prepare("SELECT * FROM user_watches_group_stop WHERE user_account_id=:uid AND group_id=:gid");
$stat->execute(array('uid' => $user->getId(), 'gid' => $group->getId()));
if ($stat->rowCount() > 0) {
$uwgs = new UserWatchesGroupStopModel();
$uwgs->setFromDataBaseRow($stat->fetch());
return $uwgs;
}
$uwgs = new UserWatchesGroupStopModel();
$uwgs->setUserAccountId($user->getId());
$uwgs->setGroupId($group->getId());
$uwgs->setAccessKey(createKey(2, 150));
// TODO check not already used
$stat = $DB->prepare("INSERT INTO user_watches_group_stop (user_account_id, group_id, access_key, created_at) " . "VALUES (:user_account_id, :group_id, :access_key, :created_at)");
$stat->execute(array('user_account_id' => $uwgs->getUserAccountId(), 'group_id' => $uwgs->getGroupId(), 'access_key' => $uwgs->getAccessKey(), 'created_at' => \TimeSource::getFormattedForDataBase()));
return $uwgs;
}
protected function build()
{
if ($this->site) {
$this->where[] = " media_information.site_id = :site_id ";
$this->params['site_id'] = $this->site->getId();
}
if ($this->group) {
$this->joins[] = " JOIN media_in_group AS media_in_group ON media_in_group.media_id = media_information.id " . "AND media_in_group.removed_at IS NULL AND media_in_group.group_id = :group_id ";
$this->params['group_id'] = $this->group->getId();
} else {
if ($this->groupNotIn) {
$this->joins[] = " LEFT JOIN media_in_group AS media_in_group ON media_in_group.media_id = media_information.id " . "AND media_in_group.removed_at IS NULL AND media_in_group.group_id = :group_id ";
$this->where[] = " media_in_group.added_at IS NULL ";
$this->params['group_id'] = $this->groupNotIn->getId();
}
}
if ($this->venue) {
$this->joins[] = " JOIN media_in_venue AS media_in_venue ON media_in_venue.media_id = media_information.id " . "AND media_in_venue.removed_at IS NULL AND media_in_venue.venue_id = :venue_id ";
$this->params['venue_id'] = $this->venue->getId();
} else {
if ($this->venueNotIn) {
$this->joins[] = " LEFT JOIN media_in_venue AS media_in_venue ON media_in_venue.media_id = media_information.id " . "AND media_in_venue.removed_at IS NULL AND media_in_venue.venue_id = :venue_id ";
$this->where[] = " media_in_venue.added_at IS NULL ";
$this->params['venue_id'] = $this->venueNotIn->getId();
}
}
if ($this->event) {
$this->joins[] = " JOIN media_in_event AS media_in_event ON media_in_event.media_id = media_information.id " . "AND media_in_event.removed_at IS NULL AND media_in_event.event_id = :event_id ";
$this->params['event_id'] = $this->event->getId();
} else {
if ($this->eventNotIn) {
$this->joins[] = " LEFT JOIN media_in_event AS media_in_event ON media_in_event.media_id = media_information.id " . "AND media_in_event.removed_at IS NULL AND media_in_event.event_id = :event_id ";
$this->where[] = " media_in_event.added_at IS NULL ";
$this->params['event_id'] = $this->eventNotIn->getId();
}
}
if (!$this->include_deleted) {
$this->where[] = " media_information.deleted_at IS NULL ";
}
}
protected function build()
{
$this->select[] = ' curated_list_information.* ';
if ($this->userAccount) {
$this->joins[] = " JOIN user_in_curated_list_information ON user_in_curated_list_information.curated_list_id = curated_list_information.id " . "AND user_in_curated_list_information.user_account_id = :user_account_id ";
$this->params['user_account_id'] = $this->userAccount->getId();
$this->where[] = " (user_in_curated_list_information.is_owner = '1' OR user_in_curated_list_information.is_editor = '1' ) ";
}
if ($this->site) {
$this->where[] = " curated_list_information.site_id = :site_id ";
$this->params['site_id'] = $this->site->getId();
}
if ($this->containsEvent) {
$this->params['event_id'] = $this->containsEvent->getId();
// event directly in list?
$this->joins[] = " LEFT JOIN event_in_curated_list ON event_in_curated_list.curated_list_id = curated_list_information.id AND " . " event_in_curated_list.event_id = :event_id AND event_in_curated_list.removed_at IS NULL ";
// event in list via group?
$this->joins[] = " LEFT JOIN ( SELECT group_in_curated_list.curated_list_id, MAX(group_in_curated_list.group_id) AS group_id FROM group_in_curated_list " . " JOIN event_in_group ON event_in_group.group_id = group_in_curated_list.group_id " . " WHERE event_in_group.event_id = :event_id AND group_in_curated_list.removed_at IS NULL AND event_in_group.removed_at IS NULL " . " GROUP BY group_in_curated_list.curated_list_id ) AS event_in_curated_list_via_group_table ON event_in_curated_list_via_group_table.curated_list_id = curated_list_information.id ";
$this->where[] = " (event_in_curated_list.added_at IS NOT NULL OR event_in_curated_list_via_group_table.group_id IS NOT NULL) ";
} else {
if ($this->eventInfo) {
$this->params['event_id'] = $this->eventInfo->getId();
// event directly in list?
$this->joins[] = " LEFT JOIN event_in_curated_list ON event_in_curated_list.curated_list_id = curated_list_information.id AND " . " event_in_curated_list.event_id = :event_id AND event_in_curated_list.removed_at IS NULL ";
$this->select[] = " event_in_curated_list.added_at AS is_event_in_list ";
// event in list via group?
$this->joins[] = " LEFT JOIN ( SELECT group_in_curated_list.curated_list_id, MAX(group_in_curated_list.group_id) AS group_id FROM group_in_curated_list " . " JOIN event_in_group ON event_in_group.group_id = group_in_curated_list.group_id " . " WHERE event_in_group.event_id = :event_id AND group_in_curated_list.removed_at IS NULL AND event_in_group.removed_at IS NULL " . " GROUP BY group_in_curated_list.curated_list_id ) AS event_in_curated_list_via_group_table ON event_in_curated_list_via_group_table.curated_list_id = curated_list_information.id ";
$this->select[] = " event_in_curated_list_via_group_table.group_id AS event_in_list_via_group_id ";
}
}
if ($this->containsGroup) {
$this->joins[] = " LEFT JOIN group_in_curated_list ON group_in_curated_list.curated_list_id = curated_list_information.id AND " . " group_in_curated_list.group_id = :group_id AND group_in_curated_list.removed_at IS NULL ";
$this->params['group_id'] = $this->containsGroup->getId();
$this->where[] = " group_in_curated_list.added_at IS NOT NULL ";
} else {
if ($this->groupInfo) {
$this->joins[] = " LEFT JOIN group_in_curated_list ON group_in_curated_list.curated_list_id = curated_list_information.id AND " . " group_in_curated_list.group_id = :group_id AND group_in_curated_list.removed_at IS NULL ";
$this->params['group_id'] = $this->groupInfo->getId();
$this->select[] = " group_in_curated_list.added_at AS is_group_in_list ";
}
}
if (!$this->include_deleted) {
$this->where[] = " curated_list_information.is_deleted = '0' ";
}
}
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' ";
}
}
function setGroup(GroupModel $group)
{
$this->data['group'] = $group->getId();
}
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 update(GroupModel $group, $fields, GroupEditMetaDataModel $groupEditMetaDataModel)
{
$alreadyInTransaction = $this->db->inTransaction();
// Make Information Data
$fieldsSQL1 = array();
$fieldsParams1 = array('id' => $group->getId());
foreach ($fields as $field) {
$fieldsSQL1[] = " " . $field . "=:" . $field . " ";
if ($field == 'title') {
$fieldsParams1['title'] = substr($group->getTitle(), 0, VARCHAR_COLUMN_LENGTH_USED);
} else {
if ($field == 'description') {
$fieldsParams1['description'] = $group->getDescription();
} else {
if ($field == 'url') {
$fieldsParams1['url'] = substr($group->getUrl(), 0, VARCHAR_COLUMN_LENGTH_USED);
} else {
if ($field == 'twitter_username') {
$fieldsParams1['twitter_username'] = substr($group->getTwitterUsername(), 0, VARCHAR_COLUMN_LENGTH_USED);
} else {
if ($field == 'is_duplicate_of_id') {
$fieldsParams1['is_duplicate_of_id'] = $group->getIsDuplicateOfId();
} else {
if ($field == 'is_deleted') {
$fieldsParams1['is_deleted'] = $group->getIsDeleted() ? 1 : 0;
}
}
}
}
}
}
}
// Make History Data
$fieldsSQL2 = array('group_id', 'user_account_id', 'created_at', 'approved_at');
$fieldsSQLParams2 = array(':group_id', ':user_account_id', ':created_at', ':approved_at');
$fieldsParams2 = array('group_id' => $group->getId(), 'user_account_id' => $groupEditMetaDataModel->getUserAccount() ? $groupEditMetaDataModel->getUserAccount()->getId() : null, 'created_at' => $this->timesource->getFormattedForDataBase(), 'approved_at' => $this->timesource->getFormattedForDataBase());
if ($groupEditMetaDataModel->getEditComment()) {
$fieldsSQL2[] = ' edit_comment ';
$fieldsSQLParams2[] = ' :edit_comment ';
$fieldsParams2['edit_comment'] = $groupEditMetaDataModel->getEditComment();
}
foreach ($this->possibleFields as $field) {
if (in_array($field, $fields) || $field == 'title') {
$fieldsSQL2[] = " " . $field . " ";
$fieldsSQLParams2[] = " :" . $field . " ";
if ($field == 'title') {
$fieldsParams2['title'] = substr($group->getTitle(), 0, VARCHAR_COLUMN_LENGTH_USED);
} else {
if ($field == 'description') {
$fieldsParams2['description'] = $group->getDescription();
} else {
if ($field == 'url') {
$fieldsParams2['url'] = substr($group->getUrl(), 0, VARCHAR_COLUMN_LENGTH_USED);
} else {
if ($field == 'twitter_username') {
$fieldsParams2['twitter_username'] = substr($group->getTwitterUsername(), 0, VARCHAR_COLUMN_LENGTH_USED);
} else {
if ($field == 'is_duplicate_of_id') {
$fieldsParams2['is_duplicate_of_id'] = $group->getIsDuplicateOfId();
} else {
if ($field == 'is_deleted') {
$fieldsParams2['is_deleted'] = $group->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 group_information SET " . implode(",", $fieldsSQL1) . " WHERE id=:id");
$stat->execute($fieldsParams1);
// History SQL
$stat = $this->db->prepare("INSERT INTO group_history (" . implode(",", $fieldsSQL2) . ") VALUES (" . implode(",", $fieldsSQLParams2) . ")");
$stat->execute($fieldsParams2);
if (!$alreadyInTransaction) {
$this->db->commit();
}
} catch (Exception $e) {
if (!$alreadyInTransaction) {
$this->db->rollBack();
}
throw $e;
}
}
public function remove(MediaModel $media, GroupModel $group, UserAccountModel $user)
{
global $DB;
$stat = $DB->prepare("UPDATE media_in_group SET removed_by_user_account_id=:removed_by_user_account_id," . " removed_at=:removed_at, removal_approved_at=:removal_approved_at WHERE " . " group_id=:group_id AND media_id=:media_id AND removed_at IS NULL ");
$stat->execute(array('group_id' => $group->getId(), 'media_id' => $media->getId(), 'removed_at' => \TimeSource::getFormattedForDataBase(), 'removal_approved_at' => \TimeSource::getFormattedForDataBase(), 'removed_by_user_account_id' => $user->getId()));
}
public function createWithMetaData(EventModel $event, SiteModel $site, EventEditMetaDataModel $eventEditMetaDataModel, GroupModel $group = null, $additionalGroups = null, ImportedEventModel $importedEvent = null, $tags = array(), $medias = array())
{
global $DB;
try {
$DB->beginTransaction();
$stat = $DB->prepare("SELECT max(slug) AS c FROM event_information WHERE site_id=:site_id");
$stat->execute(array('site_id' => $site->getId()));
$data = $stat->fetch();
$event->setSlug($data['c'] + 1);
$stat = $DB->prepare("INSERT INTO event_information (site_id, slug, summary,description,start_at,end_at," . " created_at, event_recur_set_id,venue_id,country_id,timezone, " . " url, ticket_url, is_physical, is_virtual, area_id, approved_at, is_deleted, is_cancelled, custom_fields) " . " VALUES (:site_id, :slug, :summary, :description, :start_at, :end_at, " . " :created_at, :event_recur_set_id,:venue_id,:country_id,:timezone, " . " :url, :ticket_url, :is_physical, :is_virtual, :area_id, :approved_at, '0', '0', :custom_fields) RETURNING id");
$stat->execute(array('site_id' => $site->getId(), 'slug' => $event->getSlug(), 'summary' => substr($event->getSummary(), 0, VARCHAR_COLUMN_LENGTH_USED), 'description' => $event->getDescription(), 'start_at' => $event->getStartAtInUTC()->format("Y-m-d H:i:s"), 'end_at' => $event->getEndAtInUTC()->format("Y-m-d H:i:s"), 'created_at' => \TimeSource::getFormattedForDataBase(), 'approved_at' => \TimeSource::getFormattedForDataBase(), 'event_recur_set_id' => $event->getEventRecurSetId(), 'country_id' => $event->getCountryId(), 'venue_id' => $event->getVenueId(), 'area_id' => $event->getVenueId() ? null : $event->getAreaId(), 'timezone' => $event->getTimezone(), 'url' => substr($event->getUrl(), 0, VARCHAR_COLUMN_LENGTH_USED), 'ticket_url' => substr($event->getTicketUrl(), 0, VARCHAR_COLUMN_LENGTH_USED), 'is_physical' => $event->getIsPhysical() ? 1 : 0, 'is_virtual' => $event->getIsVirtual() ? 1 : 0, 'custom_fields' => json_encode($event->getCustomFields())));
$data = $stat->fetch();
$event->setId($data['id']);
$stat = $DB->prepare("INSERT INTO event_history (event_id, summary, description,start_at, end_at, " . " user_account_id , created_at,venue_id,country_id,timezone," . " url, ticket_url, is_physical, is_virtual, area_id, is_new, approved_at, is_deleted, is_cancelled, edit_comment, custom_fields) VALUES " . " (:event_id, :summary, :description, :start_at, :end_at, " . " :user_account_id , :created_at,:venue_id,:country_id,:timezone," . " :url, :ticket_url, :is_physical, :is_virtual, :area_id, '1', :approved_at, '0', '0', :edit_comment, :custom_fields)");
$stat->execute(array('event_id' => $event->getId(), 'summary' => substr($event->getSummary(), 0, VARCHAR_COLUMN_LENGTH_USED), 'description' => $event->getDescription(), 'start_at' => $event->getStartAtInUTC()->format("Y-m-d H:i:s"), 'end_at' => $event->getEndAtInUTC()->format("Y-m-d H:i:s"), 'user_account_id' => $eventEditMetaDataModel->getUserAccount() ? $eventEditMetaDataModel->getUserAccount()->getId() : null, 'created_at' => \TimeSource::getFormattedForDataBase(), 'approved_at' => \TimeSource::getFormattedForDataBase(), 'country_id' => $event->getCountryId(), 'venue_id' => $event->getVenueId(), 'area_id' => $event->getVenueId() ? null : $event->getAreaId(), 'timezone' => $event->getTimezone(), 'url' => substr($event->getUrl(), 0, VARCHAR_COLUMN_LENGTH_USED), 'ticket_url' => substr($event->getTicketUrl(), 0, VARCHAR_COLUMN_LENGTH_USED), 'is_physical' => $event->getIsPhysical() ? 1 : 0, 'is_virtual' => $event->getIsVirtual() ? 1 : 0, 'edit_comment' => $eventEditMetaDataModel->getEditComment(), 'custom_fields' => json_encode($event->getCustomFields())));
// Group can be passed as model or as field on event!
if (!$group && $event->getGroupId()) {
$group = new GroupModel();
$group->setId($event->getGroupId());
}
if ($group || $additionalGroups) {
$stat = $DB->prepare("INSERT INTO event_in_group (group_id,event_id,added_by_user_account_id,added_at,is_main_group,addition_approved_at) " . "VALUES (:group_id,:event_id,:added_by_user_account_id,:added_at,:is_main_group,:addition_approved_at)");
if ($group) {
$stat->execute(array('group_id' => $group->getId(), 'event_id' => $event->getId(), 'added_by_user_account_id' => $eventEditMetaDataModel->getUserAccount() ? $eventEditMetaDataModel->getUserAccount()->getId() : null, 'added_at' => \TimeSource::getFormattedForDataBase(), 'addition_approved_at' => \TimeSource::getFormattedForDataBase(), 'is_main_group' => 1));
}
if ($additionalGroups && is_array($additionalGroups)) {
foreach ($additionalGroups as $additionalGroup) {
if ($additionalGroup->getId() != $group->getId()) {
$stat->execute(array('group_id' => $additionalGroup->getId(), 'event_id' => $event->getId(), 'added_by_user_account_id' => $eventEditMetaDataModel->getUserAccount() ? $eventEditMetaDataModel->getUserAccount()->getId() : null, 'added_at' => \TimeSource::getFormattedForDataBase(), 'addition_approved_at' => \TimeSource::getFormattedForDataBase(), 'is_main_group' => 0));
}
}
}
}
if ($eventEditMetaDataModel->getUserAccount()) {
if ($event->getGroupId()) {
$ufgr = new UserWatchesGroupRepository();
$ufgr->startUserWatchingGroupIdIfNotWatchedBefore($eventEditMetaDataModel->getUserAccount(), $event->getGroupId());
} else {
// TODO watch site?
}
}
if ($importedEvent) {
$stat = $DB->prepare("INSERT INTO imported_event_is_event (imported_event_id, event_id, created_at) " . "VALUES (:imported_event_id, :event_id, :created_at)");
$stat->execute(array('imported_event_id' => $importedEvent->getId(), 'event_id' => $event->getId(), 'created_at' => \TimeSource::getFormattedForDataBase()));
}
if ($tags) {
$stat = $DB->prepare("INSERT INTO event_has_tag (tag_id,event_id,added_by_user_account_id,added_at,addition_approved_at) " . "VALUES (:tag_id,:event_id,:added_by_user_account_id,:added_at,:addition_approved_at)");
foreach ($tags as $tag) {
$stat->execute(array('tag_id' => $tag->getId(), 'event_id' => $event->getId(), 'added_by_user_account_id' => $eventEditMetaDataModel->getUserAccount() ? $eventEditMetaDataModel->getUserAccount()->getId() : null, 'added_at' => \TimeSource::getFormattedForDataBase(), 'addition_approved_at' => \TimeSource::getFormattedForDataBase()));
}
}
if ($medias) {
$stat = $DB->prepare("INSERT INTO media_in_event (event_id,media_id,added_by_user_account_id,added_at,addition_approved_at) " . "VALUES (:event_id,:media_id,:added_by_user_account_id,:added_at,:addition_approved_at)");
foreach ($medias as $media) {
$stat->execute(array('event_id' => $event->getId(), 'media_id' => $media->getId(), 'added_by_user_account_id' => $eventEditMetaDataModel->getUserAccount() ? $eventEditMetaDataModel->getUserAccount()->getId() : null, 'added_at' => \TimeSource::getFormattedForDataBase(), 'addition_approved_at' => \TimeSource::getFormattedForDataBase()));
}
}
if ($eventEditMetaDataModel->getCreatedFromNewEventDraftID()) {
$stat = $DB->prepare("UPDATE new_event_draft_information SET event_id=:event_id WHERE id=:id");
$stat->execute(array('event_id' => $event->getId(), 'id' => $eventEditMetaDataModel->getCreatedFromNewEventDraftID()));
$stat = $DB->prepare("INSERT INTO new_event_draft_history (new_event_draft_id, event_id, user_account_id,created_at,details_changed,was_existing_event_id_changed,not_duplicate_events_changed) " . "VALUES (:new_event_draft_id, :event_id, :user_account_id,:created_at,-2,-2,-2 )");
$stat->execute(array('new_event_draft_id' => $eventEditMetaDataModel->getCreatedFromNewEventDraftID(), 'event_id' => $event->getId(), 'user_account_id' => $eventEditMetaDataModel->getUserAccount() ? $eventEditMetaDataModel->getUserAccount()->getId() : null, 'created_at' => \TimeSource::getFormattedForDataBase()));
}
$DB->commit();
} catch (Exception $e) {
$DB->rollBack();
}
}
public function purge(GroupModel $group)
{
global $DB;
try {
$DB->beginTransaction();
$stat = $DB->prepare("UPDATE group_history SET is_duplicate_of_id=NULL, is_duplicate_of_id_changed=0 WHERE is_duplicate_of_id=:id");
$stat->execute(array('id' => $group->getId()));
$stat = $DB->prepare("UPDATE group_information SET is_duplicate_of_id=NULL WHERE is_duplicate_of_id=:id");
$stat->execute(array('id' => $group->getId()));
$stat = $DB->prepare("DELETE FROM user_watches_group_stop WHERE group_id=:id");
$stat->execute(array('id' => $group->getId()));
$stat = $DB->prepare("DELETE FROM user_watches_group_information WHERE group_id=:id");
$stat->execute(array('id' => $group->getId()));
$stat = $DB->prepare("DELETE FROM event_in_group WHERE group_id=:id");
$stat->execute(array('id' => $group->getId()));
$stat = $DB->prepare("DELETE FROM group_history WHERE group_id=:id");
$stat->execute(array('id' => $group->getId()));
$stat = $DB->prepare("DELETE FROM group_information WHERE id=:id");
$stat->execute(array('id' => $group->getId()));
$DB->commit();
} catch (Exception $e) {
$DB->rollBack();
throw $e;
}
}
public function startUserWatchingGroupIfNotWatchedBefore(UserAccountModel $user, GroupModel $group)
{
$this->startUserWatchingGroupIdIfNotWatchedBefore($user, $group->getId());
}
public function removeGroupFromCuratedList(GroupModel $group, CuratedListModel $curatedList, UserAccountModel $user)
{
global $DB;
$stat = $DB->prepare("UPDATE group_in_curated_list SET removed_by_user_account_id=:removed_by_user_account_id," . " removed_at=:removed_at WHERE " . " group_id=:group_id AND curated_list_id=:curated_list_id AND removed_at IS NULL ");
$stat->execute(array('group_id' => $group->getId(), 'curated_list_id' => $curatedList->getId(), 'removed_at' => \TimeSource::getFormattedForDataBase(), 'removed_by_user_account_id' => $user->getId()));
}
