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(TagModel $tag, $fields, TagEditMetaDataModel $tagEditMetaDataModel)
 {
     $alreadyInTransaction = $this->db->inTransaction();
     // Make Information Data
     $fieldsSQL1 = array();
     $fieldsParams1 = array('id' => $tag->getId());
     foreach ($fields as $field) {
         $fieldsSQL1[] = " " . $field . "=:" . $field . " ";
         if ($field == 'title') {
             $fieldsParams1['title'] = substr($tag->getTitle(), 0, VARCHAR_COLUMN_LENGTH_USED);
         } else {
             if ($field == 'description') {
                 $fieldsParams1['description'] = $tag->getDescription();
             } else {
                 if ($field == 'is_deleted') {
                     $fieldsParams1['is_deleted'] = $tag->getIsDeleted() ? 1 : 0;
                 }
             }
         }
     }
     // Make History Data
     $fieldsSQL2 = array('tag_id', 'user_account_id', 'created_at', 'approved_at');
     $fieldsSQLParams2 = array(':tag_id', ':user_account_id', ':created_at', ':approved_at');
     $fieldsParams2 = array('tag_id' => $tag->getId(), 'user_account_id' => $tagEditMetaDataModel->getUserAccount() ? $tagEditMetaDataModel->getUserAccount()->getId() : null, 'created_at' => $this->timesource->getFormattedForDataBase(), 'approved_at' => $this->timesource->getFormattedForDataBase());
     if ($tagEditMetaDataModel->getEditComment()) {
         $fieldsSQL2[] = ' edit_comment ';
         $fieldsSQLParams2[] = ' :edit_comment ';
         $fieldsParams2['edit_comment'] = $tagEditMetaDataModel->getEditComment();
     }
     foreach ($this->possibleFields as $field) {
         if (in_array($field, $fields) || $field == 'title') {
             $fieldsSQL2[] = " " . $field . " ";
             $fieldsSQLParams2[] = " :" . $field . " ";
             if ($field == 'title') {
                 $fieldsParams2['title'] = substr($tag->getTitle(), 0, VARCHAR_COLUMN_LENGTH_USED);
             } else {
                 if ($field == 'description') {
                     $fieldsParams2['description'] = $tag->getDescription();
                 } else {
                     if ($field == 'is_deleted') {
                         $fieldsParams2['is_deleted'] = $tag->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 tag_information  SET " . implode(",", $fieldsSQL1) . " WHERE id=:id");
         $stat->execute($fieldsParams1);
         // History SQL
         $stat = $this->db->prepare("INSERT INTO tag_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 purge(TagModel $tag)
 {
     global $DB;
     try {
         $DB->beginTransaction();
         $stat = $DB->prepare("DELETE FROM event_has_tag WHERE tag_id=:id");
         $stat->execute(array('id' => $tag->getId()));
         $stat = $DB->prepare("DELETE FROM tag_history WHERE tag_id=:id");
         $stat->execute(array('id' => $tag->getId()));
         $stat = $DB->prepare("DELETE FROM tag_information WHERE id=:id");
         $stat->execute(array('id' => $tag->getId()));
         $DB->commit();
     } catch (Exception $e) {
         $DB->rollBack();
         throw $e;
     }
 }