protected function build()
{
if ($this->user) {
$this->where[] = " user_account_verify_email.user_account_id = :user_account_id";
$this->params['user_account_id'] = $this->user->getId();
}
}
protected function build()
{
if ($this->userInterestedIn) {
$this->params['user_in_site'] = $this->userInterestedIn->getId();
// user watches site
$this->joins[] = " LEFT JOIN user_watches_site_information ON user_watches_site_information.site_id = site_information.id AND user_watches_site_information.user_account_id = :user_in_site ";
// user interested in site
$this->joins[] = " LEFT JOIN user_interested_in_site_information ON user_interested_in_site_information.site_id = site_information.id AND user_interested_in_site_information.user_account_id = :user_in_site ";
// user watches group information
$inner = "SELECT group_information.site_id AS site_id, user_watches_group_information.user_account_id AS user_account_id " . "FROM user_watches_group_information " . " JOIN group_information ON group_information.id = user_watches_group_information.group_id " . " WHERE user_watches_group_information.is_watching = '1' AND user_watches_group_information.user_account_id = :user_in_site " . " GROUP BY group_information.site_id, user_watches_group_information.user_account_id ";
$this->joins[] = " LEFT JOIN (" . $inner . ") AS user_watches_group ON user_watches_group.site_id = site_information.id ";
// user watches area information
$inner = "SELECT area_information.site_id AS site_id, user_watches_area_information.user_account_id AS user_account_id " . "FROM user_watches_area_information " . " JOIN area_information ON area_information.id = user_watches_area_information.area_id " . " WHERE user_watches_area_information.is_watching = '1' AND user_watches_area_information.user_account_id = :user_in_site " . " GROUP BY area_information.site_id, user_watches_area_information.user_account_id ";
$this->joins[] = " LEFT JOIN (" . $inner . ") AS user_watches_area ON user_watches_area.site_id = site_information.id ";
// TODO user at event. https://github.com/OpenACalendar/OpenACalendar-Web-Core/issues/357
// Permissions
$inner = "SELECT user_group_in_site.site_id AS site_id, user_in_user_group.user_account_id AS user_account_id FROM user_group_in_site " . "LEFT JOIN user_in_user_group ON user_in_user_group.user_group_id = user_group_in_site.user_group_id " . "WHERE user_group_in_site.removed_at IS NULL AND user_in_user_group.removed_at IS NULL AND user_in_user_group.user_account_id = :user_in_site " . "GROUP BY user_group_in_site.site_id, user_in_user_group.user_account_id ";
$this->joins[] = " LEFT JOIN (" . $inner . ") AS user_permission_in_site ON user_permission_in_site.site_id = site_information.id ";
// put it all together
$this->where[] = " ( user_watches_site_information.is_watching = '1' " . " OR user_permission_in_site.user_account_id = :user_in_site " . " OR user_watches_group.user_account_id = :user_in_site " . " OR user_watches_area.user_account_id = :user_in_site " . " OR user_interested_in_site_information.is_interested = '1' " . " )";
}
if ($this->isListedInIndexOnly) {
$this->where[] = " site_information.is_listed_in_index = '1' ";
}
if ($this->isOpenBySysAdminsOnly) {
$this->where[] = " site_information.is_closed_by_sys_admin = '0' ";
}
}
protected function build()
{
$this->select[] = 'sysadmin_comment_information.*';
if ($this->user) {
$this->joins[] = " JOIN sysadmin_comment_about_user ON sysadmin_comment_about_user.sysadmin_comment_id = sysadmin_comment_information.id ";
$this->where[] = " sysadmin_comment_about_user.user_account_id = :user_account_id ";
$this->params['user_account_id'] = $this->user->getId();
}
}
protected function build()
{
if ($this->site) {
$this->where[] = " send_email_information.site_id = :site_id ";
$this->params['site_id'] = $this->site->getId();
}
if ($this->userCreatedBy) {
$this->where[] = " send_email_information.created_by = :created_by ";
$this->params['created_by'] = $this->userCreatedBy->getId();
}
}
public function editEmailPreference(UserAccountModel $user, $extensionId, $userNotificationPreferenceType, $value)
{
global $DB;
# is already in DB?
$stat = $DB->prepare("SELECT user_notification_preference.* FROM user_notification_preference " . "WHERE user_id =:user_id AND extension_id=:extension_id AND user_notification_preference_type = :user_notification_preference_type");
$stat->execute(array('user_id' => $user->getId(), 'extension_id' => $extensionId, 'user_notification_preference_type' => $userNotificationPreferenceType));
# update or insert
if ($stat->rowCount() > 0) {
$stat = $DB->prepare("UPDATE user_notification_preference SET is_email = :is_email " . "WHERE user_id =:user_id AND extension_id=:extension_id AND user_notification_preference_type = :user_notification_preference_type");
} else {
$stat = $DB->prepare("INSERT INTO user_notification_preference (user_id,extension_id,user_notification_preference_type,is_email) " . "VALUES (:user_id,:extension_id,:user_notification_preference_type,:is_email)");
}
$stat->execute(array('user_id' => $user->getId(), 'extension_id' => $extensionId, 'user_notification_preference_type' => $userNotificationPreferenceType, 'is_email' => $value ? 1 : 0));
}
protected function build()
{
$this->joins[] = " LEFT JOIN site_information ON site_information.id = user_notification.site_id ";
if ($this->site) {
$this->where[] = " user_notification.site_id = :site_id ";
$this->params['site_id'] = $this->site->getId();
}
if ($this->user) {
$this->where[] = " user_notification.user_id = :user_id ";
$this->params['user_id'] = $this->user->getId();
}
if ($this->isOpenBySysAdminsOnly) {
$this->where[] = " ( site_information.is_closed_by_sys_admin = '0' OR site_information.is_closed_by_sys_admin is null ) ";
}
}
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();
}
}
function userLogIn(UserAccountModel $user)
{
global $WEBSESSION;
if (!$user->getIsClosedBySysAdmin()) {
$WEBSESSION->set('userID', $user->getId());
}
}
protected function build()
{
$this->select = array('group_information.*');
if ($this->site) {
$this->where[] = " group_information.site_id = :site_id ";
$this->params['site_id'] = $this->site->getId();
}
if ($this->event) {
$this->joins[] = " JOIN event_in_group AS event_in_group ON event_in_group.group_id = group_information.id " . "AND event_in_group.removed_at IS NULL AND event_in_group.event_id = :event_id ";
$this->params['event_id'] = $this->event->getId();
} else {
if ($this->notEvent) {
$this->joins[] = " LEFT JOIN event_in_group AS event_in_group ON event_in_group.group_id = group_information.id " . "AND event_in_group.removed_at IS NULL AND event_in_group.event_id = :event_id ";
$this->params['event_id'] = $this->notEvent->getId();
$this->where[] = ' event_in_group.event_id IS NULL ';
}
}
if ($this->freeTextSearch) {
$this->where[] = '(CASE WHEN group_information.title IS NULL THEN \'\' ELSE group_information.title END ) || \' \' || ' . '(CASE WHEN group_information.description IS NULL THEN \'\' ELSE group_information.description END )' . ' ILIKE :free_text_search ';
$this->params['free_text_search'] = "%" . strtolower($this->freeTextSearch) . "%";
}
if (!$this->include_deleted) {
$this->where[] = " group_information.is_deleted = '0' ";
}
if ($this->includeMediasSlugs) {
$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 " . " WHERE media_information.deleted_at IS NULL AND media_information.is_file_lost='0' " . " AND media_in_group.removal_approved_at IS NULL AND media_in_group.group_id = group_information.id " . " GROUP BY group_information.id ) AS media_group_slugs ";
}
if ($this->editedByUser) {
$this->where[] = " group_information.id IN (SELECT group_id FROM group_history WHERE user_account_id = :editedByUser) ";
$this->params['editedByUser'] = $this->editedByUser->getId();
}
}
public function setFeature(SiteModel $site, \BaseSiteFeature $siteFeature, $value, UserAccountModel $userAccountModel = null)
{
try {
$this->app['db']->beginTransaction();
$changeMade = false;
$stat = $this->app['db']->prepare("SELECT is_on FROM site_feature_information WHERE site_id=:site_id AND extension_id =:extension_id AND feature_id =:feature_id");
$stat->execute(array('site_id' => $site->getId(), 'extension_id' => $siteFeature->getExtensionId(), 'feature_id' => $siteFeature->getFeatureId()));
if ($stat->rowCount() == 1) {
$data = $stat->fetch();
if ($data['is_on'] != $value) {
$stat = $this->app['db']->prepare("UPDATE site_feature_information SET is_on=:is_on " . " WHERE site_id=:site_id AND extension_id =:extension_id AND feature_id =:feature_id ");
$stat->execute(array('site_id' => $site->getId(), 'extension_id' => $siteFeature->getExtensionId(), 'feature_id' => $siteFeature->getFeatureId(), 'is_on' => $value ? 1 : 0));
$changeMade = true;
}
} else {
$stat = $this->app['db']->prepare("INSERT INTO site_feature_information (site_id, extension_id, feature_id, is_on) " . " VALUES(:site_id, :extension_id, :feature_id, :is_on) ");
$stat->execute(array('site_id' => $site->getId(), 'extension_id' => $siteFeature->getExtensionId(), 'feature_id' => $siteFeature->getFeatureId(), 'is_on' => $value ? 1 : 0));
$changeMade = true;
}
if ($changeMade) {
$stat = $this->app['db']->prepare("INSERT INTO site_feature_history (site_id, extension_id, feature_id, is_on, user_account_id, created_at) " . " VALUES (:site_id, :extension_id, :feature_id, :is_on, :user_account_id, :created_at)");
$stat->execute(array('site_id' => $site->getId(), 'extension_id' => $siteFeature->getExtensionId(), 'feature_id' => $siteFeature->getFeatureId(), 'is_on' => $value ? 1 : 0, 'user_account_id' => $userAccountModel ? $userAccountModel->getId() : null, 'created_at' => \TimeSource::getFormattedForDataBase()));
}
$this->app['db']->commit();
} catch (Exception $e) {
$this->app['db']->rollBack();
}
}
public function isUserInSite(UserAccountModel $userAccountModel, SiteModel $siteModel)
{
global $DB;
$stat = $DB->prepare("SELECT * FROM user_has_no_editor_permissions_in_site WHERE site_id=:site_id AND user_account_id=:user_account_id AND removed_at IS NULL");
$stat->execute(array("site_id" => $siteModel->getId(), "user_account_id" => $userAccountModel->getId()));
return $stat->rowCount() > 0;
}
public function getLastSentForUserAccount(UserAccountModel $user)
{
global $DB;
$stat = $DB->prepare("SELECT MAX(created_at) AS c FROM user_account_verify_email WHERE user_account_id=:user_account_id");
$stat->execute(array('user_account_id' => $user->getId()));
$data = $stat->fetch();
return $data['c'] ? new \DateTime($data['c'], new \DateTimeZone('UTC')) : 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' ";
}
}
/**
* This will always return something. If one doesn't exist, one will be created.
* @return UserAccountPrivateFeedKeyModel
*/
public function getForUser(UserAccountModel $user)
{
global $DB;
$stat = $DB->prepare("SELECT * FROM user_account_general_security_key WHERE user_account_id=:uid");
$stat->execute(array('uid' => $user->getId()));
if ($stat->rowCount() > 0) {
$uagskm = new UserAccountGeneralSecurityKeyModel();
$uagskm->setFromDataBaseRow($stat->fetch());
return $uagskm;
}
$uagskm = new UserAccountGeneralSecurityKeyModel();
$uagskm->setUserAccountId($user->getId());
$uagskm->setAccessKey(createKey(2, 150));
// TODO check not already used
$stat = $DB->prepare("INSERT INTO user_account_general_security_key (user_account_id, access_key, created_at) " . "VALUES (:user_account_id, :access_key, :created_at)");
$stat->execute(array('user_account_id' => $uagskm->getUserAccountId(), 'access_key' => $uagskm->getAccessKey(), 'created_at' => \TimeSource::getFormattedForDataBase()));
return $uagskm;
}
/**
* 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;
}
/**
* This will always return something. If one doesn't exist, one will be created.
* @return UserWatchesSiteStopModel
*/
public function getForUserAndSite(UserAccountModel $user, SiteModel $site)
{
global $DB;
$stat = $DB->prepare("SELECT * FROM user_watches_site_stop WHERE user_account_id=:uid AND site_id=:sid");
$stat->execute(array('uid' => $user->getId(), 'sid' => $site->getId()));
if ($stat->rowCount() > 0) {
$uwss = new UserWatchesSiteStopModel();
$uwss->setFromDataBaseRow($stat->fetch());
return $uwss;
}
$uwss = new UserWatchesSiteStopModel();
$uwss->setUserAccountId($user->getId());
$uwss->setSiteId($site->getId());
$uwss->setAccessKey(createKey(2, 150));
// TODO check not already used
$stat = $DB->prepare("INSERT INTO user_watches_site_stop (user_account_id, site_id, access_key, created_at) " . "VALUES (:user_account_id, :site_id, :access_key, :created_at)");
$stat->execute(array('user_account_id' => $uwss->getUserAccountId(), 'site_id' => $uwss->getSiteId(), 'access_key' => $uwss->getAccessKey(), 'created_at' => \TimeSource::getFormattedForDataBase()));
return $uwss;
}
public function loadBySlugForSiteAndUser($slug, SiteModel $siteModel, UserAccountModel $userAccountModel)
{
global $DB;
$stat = $DB->prepare("SELECT new_event_draft_information.* FROM new_event_draft_information " . " WHERE new_event_draft_information.slug =:slug AND new_event_draft_information.site_id = :site_id AND new_event_draft_information.user_account_id = :user_id");
$stat->execute(array('slug' => $slug, 'site_id' => $siteModel->getId(), 'user_id' => $userAccountModel->getId()));
if ($stat->rowCount() > 0) {
$event = new NewEventDraftModel();
$event->setFromDataBaseRow($stat->fetch());
return $event;
}
}
/**
*
* @return \models\UserInAPI2ApplicationModel
*/
public function loadByUserAndApplication(UserAccountModel $user, API2ApplicationModel $app)
{
global $DB;
$stat = $DB->prepare("SELECT user_in_api2_application_information.* FROM user_in_api2_application_information " . "WHERE api2_application_id =:api2_application_id AND user_id =:user_id");
$stat->execute(array('api2_application_id' => $app->getId(), 'user_id' => $user->getId()));
if ($stat->rowCount() > 0) {
$app = new UserInAPI2ApplicationModel();
$app->setFromDataBaseRow($stat->fetch());
return $app;
}
}
public function loadByUserAndSiteId(UserAccountModel $user, $siteID)
{
global $DB;
$stat = $DB->prepare("SELECT user_interested_in_site_information.* FROM user_interested_in_site_information WHERE user_account_id =:user_account_id AND site_id=:site_id");
$stat->execute(array('user_account_id' => $user->getId(), 'site_id' => $siteID));
if ($stat->rowCount() > 0) {
$uiis = new UserInterestedInSiteModel();
$uiis->setFromDataBaseRow($stat->fetch());
return $uiis;
}
}
public function create(EventCommentModel $eventComment, EventModel $event, UserAccountModel $creator)
{
global $DB;
try {
$DB->beginTransaction();
$stat = $DB->prepare("SELECT max(slug) AS c FROM event_comment_information WHERE event_id=:event_id");
$stat->execute(array('event_id' => $event->getId()));
$data = $stat->fetch();
$eventComment->setSlug($data['c'] + 1);
$stat = $DB->prepare("INSERT INTO event_comment_information (event_id, slug, title, comment, user_account_id, is_deleted, created_at, approved_at) " . "VALUES (:event_id, :slug, :title, :comment, :user_account_id, '0', :created_at, :approved_at) RETURNING id");
$stat->execute(array('event_id' => $event->getId(), 'slug' => $eventComment->getSlug(), 'title' => substr($eventComment->getTitle(), 0, VARCHAR_COLUMN_LENGTH_USED), 'comment' => $eventComment->getComment(), 'user_account_id' => $creator->getId(), 'created_at' => \TimeSource::getFormattedForDataBase(), 'approved_at' => \TimeSource::getFormattedForDataBase()));
$data = $stat->fetch();
$eventComment->setId($data['id']);
$stat = $DB->prepare("INSERT INTO event_comment_history (event_comment_id, title, comment, user_account_id, is_deleted, created_at, approved_at, is_new) VALUES " . "(:event_comment_id, :title, :comment, :user_account_id, '0', :created_at, :approved_at, '1')");
$stat->execute(array('event_comment_id' => $eventComment->getId(), 'title' => substr($eventComment->getTitle(), 0, VARCHAR_COLUMN_LENGTH_USED), 'comment' => $eventComment->getComment(), 'user_account_id' => $creator->getId(), 'created_at' => \TimeSource::getFormattedForDataBase(), 'approved_at' => \TimeSource::getFormattedForDataBase()));
$DB->commit();
} catch (Exception $e) {
$DB->rollBack();
}
}
public function create(UserAccountModel $user)
{
global $DB;
$uar = new UserAccountResetModel();
$uar->setUserAccountId($user->getId());
$uar->setAccessKey(createKey(2, 250));
// TODO check not already used
$stat = $DB->prepare("INSERT INTO user_account_reset (user_account_id, access_key, created_at) " . "VALUES (:user_account_id, :access_key, :created_at)");
$stat->execute(array('user_account_id' => $uar->getUserAccountId(), 'access_key' => $uar->getAccessKey(), 'created_at' => \TimeSource::getFormattedForDataBase()));
$data = $stat->fetch();
return $uar;
}
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();
}
}
/**
*
* @return \models\API2ApplicationModel
*/
public function create(UserAccountModel $user, $title)
{
global $DB;
$app = new API2ApplicationModel();
$app->setTitle($title);
$app->setAppSecret(createKey(1, 255));
$app->setAppToken(createKey(1, 255));
$stat = $DB->prepare("INSERT INTO api2_application_information (user_id,title,app_token,app_secret,created_at) " . "VALUES (:user_id,:title,:app_token,:app_secret,:created_at) RETURNING id");
$stat->execute(array('user_id' => $user->getId(), 'title' => $title, 'app_token' => $app->getAppToken(), 'app_secret' => $app->getAppSecret(), 'created_at' => \TimeSource::getFormattedForDataBase()));
$data = $stat->fetch();
$app->setId($data['id']);
return $app;
}
public function createForAppAndUserFromRequestToken(API2ApplicationModel $app, UserAccountModel $user, API2ApplicationRequestTokenModel $requestToken)
{
global $DB;
$token = new \models\API2ApplicationUserAuthorisationTokenModel();
$token->setApi2ApplicationId($app->getId());
$token->setUserId($user->getId());
$token->setRequestToken($requestToken->getRequestToken());
$token->setAuthorisationToken(createKey(1, 255));
global $DB;
try {
$DB->beginTransaction();
// Mark Request Token used
$stat = $DB->prepare("UPDATE api2_application_request_token SET used_at=:used_at " . "WHERE api2_application_id=:api2_application_id AND request_token=:request_token");
$stat->execute(array('used_at' => \TimeSource::getFormattedForDataBase(), 'api2_application_id' => $app->getId(), 'request_token' => $requestToken->getRequestToken()));
// TODO make sure token is unique!!!!!
$stat = $DB->prepare("INSERT INTO api2_application_user_authorisation_token (api2_application_id, user_id, authorisation_token, request_token, created_at) " . "VALUES (:api2_application_id, :user_id, :authorisation_token,:request_token, :created_at)");
$stat->execute(array('api2_application_id' => $app->getId(), 'user_id' => $user->getId(), 'authorisation_token' => $token->getAuthorisationToken(), 'request_token' => $token->getRequestToken(), 'created_at' => \TimeSource::getFormattedForDataBase()));
$DB->commit();
} catch (Exception $e) {
$DB->rollBack();
}
return $token;
}
public function deactivate(EventCustomFieldDefinitionModel $model, UserAccountModel $userAccountModel = null)
{
global $DB;
try {
$DB->beginTransaction();
$stat = $DB->prepare("UPDATE event_custom_field_definition_information SET is_active='0' WHERE id=:id");
$stat->execute(array('id' => $model->getId()));
$stat = $DB->prepare("INSERT INTO event_custom_field_definition_history (event_custom_field_definition_id, key_changed, extension_id_changed,type_changed,label_changed,is_active,created_at,user_account_id) " . "VALUES (:event_custom_field_definition_id, -2, -2,-2,-2,'0',:created_at,:user_account_id)");
$stat->execute(array('event_custom_field_definition_id' => $model->getId(), 'created_at' => \TimeSource::getFormattedForDataBase(), 'user_account_id' => $userAccountModel ? $userAccountModel->getId() : null));
$DB->commit();
} catch (Exception $e) {
$DB->rollBack();
}
$this->updateSiteCache($model->getSiteId());
}
public function create(SiteModel $site, UserAccountModel $owner, $countries, SiteQuotaModel $siteQuota, $canAnyUserVerifiedEdit = false)
{
global $DB, $CONFIG, $EXTENSIONHOOKRUNNER;
$createdat = \TimeSource::getFormattedForDataBase();
if (!$site->isSlugValid($site->getSlug(), $CONFIG)) {
throw new Exception("Slug not valid");
}
try {
$DB->beginTransaction();
// TODO should check slug not already exist and nice error
$timezones = array();
foreach ($countries as $country) {
foreach (explode(",", $country->getTimezones()) as $timeZone) {
$timezones[] = $timeZone;
}
}
$site->setCachedTimezonesAsList($timezones);
$site->setCachedIsMultipleCountries(count($countries) > 1);
// Site
$stat = $DB->prepare("INSERT INTO site_information (title, slug, slug_canonical, " . "created_at,cached_is_multiple_timezones,cached_is_multiple_countries," . "cached_timezones,is_feature_map,is_feature_importer,is_feature_curated_list," . "is_listed_in_index,is_web_robots_allowed, " . " prompt_emails_days_in_advance,site_quota_id, " . "is_feature_tag,is_feature_physical_events,is_feature_virtual_events) " . "VALUES (:title, :slug, :slug_canonical, " . " :created_at,:cached_is_multiple_timezones,:cached_is_multiple_countries," . ":cached_timezones,:is_feature_map,:is_feature_importer,:is_feature_curated_list," . ":is_listed_in_index,:is_web_robots_allowed, " . " :prompt_emails_days_in_advance, :site_quota_id, " . ":is_feature_tag,:is_feature_physical_events,:is_feature_virtual_events) RETURNING id");
$stat->execute(array('title' => substr($site->getTitle(), 0, VARCHAR_COLUMN_LENGTH_USED), 'slug' => $site->getSlug(), 'slug_canonical' => SiteModel::makeCanonicalSlug($site->getSlug()), 'cached_is_multiple_timezones' => $site->getCachedIsMultipleTimezones() ? 1 : 0, 'cached_is_multiple_countries' => $site->getCachedIsMultipleCountries() ? 1 : 0, 'cached_timezones' => $site->getCachedTimezones(), 'created_at' => $createdat, 'is_feature_curated_list' => $site->getIsFeatureCuratedList() ? 1 : 0, 'is_feature_importer' => $site->getIsFeatureImporter() ? 1 : 0, 'is_feature_map' => $site->getIsFeatureMap() ? 1 : 0, 'is_feature_tag' => $site->getIsFeatureTag() ? 1 : 0, 'is_feature_virtual_events' => $site->getIsFeatureVirtualEvents() ? 1 : 0, 'is_feature_physical_events' => $site->getIsFeaturePhysicalEvents() ? 1 : 0, 'is_listed_in_index' => $site->getIsListedInIndex() ? 1 : 0, 'is_web_robots_allowed' => $site->getIsWebRobotsAllowed() ? 1 : 0, 'prompt_emails_days_in_advance' => $site->getPromptEmailsDaysInAdvance(), 'site_quota_id' => $siteQuota->getId()));
$data = $stat->fetch();
$site->setId($data['id']);
$stat = $DB->prepare("INSERT INTO site_history (site_id, user_account_id, " . "title, slug, slug_canonical, created_at,is_feature_map,is_feature_importer," . "is_feature_curated_list,is_listed_in_index,is_web_robots_allowed, " . " prompt_emails_days_in_advance, is_new," . "is_feature_tag,is_feature_physical_events,is_feature_virtual_events) " . "VALUES (:site_id, :user_account_id, :title, " . ":slug, :slug_canonical, :created_at,:is_feature_map,:is_feature_importer," . ":is_feature_curated_list,:is_listed_in_index,:is_web_robots_allowed, " . " :prompt_emails_days_in_advance, '1', " . ":is_feature_tag,:is_feature_physical_events,:is_feature_virtual_events)");
$stat->execute(array('site_id' => $site->getId(), 'user_account_id' => $owner->getId(), 'title' => substr($site->getTitle(), 0, VARCHAR_COLUMN_LENGTH_USED), 'slug' => $site->getSlug(), 'slug_canonical' => SiteModel::makeCanonicalSlug($site->getSlug()), 'created_at' => $createdat, 'is_feature_curated_list' => $site->getIsFeatureCuratedList() ? 1 : 0, 'is_feature_importer' => $site->getIsFeatureImporter() ? 1 : 0, 'is_feature_map' => $site->getIsFeatureMap() ? 1 : 0, 'is_feature_tag' => $site->getIsFeatureTag() ? 1 : 0, 'is_feature_virtual_events' => $site->getIsFeatureVirtualEvents() ? 1 : 0, 'is_feature_physical_events' => $site->getIsFeaturePhysicalEvents() ? 1 : 0, 'is_listed_in_index' => $site->getIsListedInIndex() ? 1 : 0, 'is_web_robots_allowed' => $site->getIsWebRobotsAllowed() ? 1 : 0, 'prompt_emails_days_in_advance' => $site->getPromptEmailsDaysInAdvance()));
// Permissions
$ugr = new UserGroupRepository();
$userGroupEditors = new UserGroupModel();
$userGroupEditors->setTitle("Editors");
$userGroupEditors->setIsIncludesVerifiedUsers($canAnyUserVerifiedEdit);
$ugr->createForSite($site, $userGroupEditors, $owner, array(array('org.openacalendar', 'CALENDAR_CHANGE')), array($owner));
$userGroupEditors = new UserGroupModel();
$userGroupEditors->setTitle("Administrators");
$ugr->createForSite($site, $userGroupEditors, $owner, array(array('org.openacalendar', 'CALENDAR_ADMINISTRATE')), array($owner));
// Countries!
$stat = $DB->prepare("INSERT INTO country_in_site_information (site_id,country_id,is_in,is_previously_in,created_at) VALUES (:site_id,:country_id,'1','1',:created_at)");
foreach ($countries as $country) {
$stat->execute(array('country_id' => $country->getId(), 'site_id' => $site->getId(), 'created_at' => $createdat));
}
$stat = $DB->prepare("INSERT INTO user_watches_site_information (user_account_id,site_id,is_watching,is_was_once_watching,last_watch_started,created_at) " . "VALUES (:user_account_id,:site_id,:is_watching,:is_was_once_watching,:last_watch_started,:created_at)");
$stat->execute(array('user_account_id' => $owner->getId(), 'site_id' => $site->getId(), 'is_watching' => '1', 'is_was_once_watching' => '1', 'created_at' => \TimeSource::getFormattedForDataBase(), 'last_watch_started' => \TimeSource::getFormattedForDataBase()));
$DB->commit();
$EXTENSIONHOOKRUNNER->afterSiteCreate($site, $owner);
} catch (Exception $e) {
$DB->rollBack();
}
}
public function createAboutUser(UserAccountModel $aboutUser, $comment, UserAccountModel $author = null)
{
global $DB;
$createdat = \TimeSource::getFormattedForDataBase();
try {
$DB->beginTransaction();
$stat = $DB->prepare("INSERT INTO sysadmin_comment_information (user_account_id, comment, created_at ) " . "VALUES (:user_account_id, :comment, :created_at ) RETURNING id");
$stat->execute(array('user_account_id' => $author ? $author->getId() : null, 'comment' => $comment, 'created_at' => $createdat));
$data = $stat->fetch();
$id = $data['id'];
$stat = $DB->prepare("INSERT INTO sysadmin_comment_about_user (user_account_id, sysadmin_comment_id ) " . "VALUES (:user_account_id, :sysadmin_comment_id)");
$stat->execute(array('user_account_id' => $aboutUser->getId(), 'sysadmin_comment_id' => $id));
$DB->commit();
} catch (Exception $e) {
$DB->rollBack();
}
}
public function delete(MediaModel $media, UserAccountModel $user)
{
global $DB;
try {
$DB->beginTransaction();
$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 " . " media_id=:media_id AND removed_at IS NULL ");
$stat->execute(array('media_id' => $media->getId(), 'removed_at' => \TimeSource::getFormattedForDataBase(), 'removal_approved_at' => \TimeSource::getFormattedForDataBase(), 'removed_by_user_account_id' => $user->getId()));
$stat = $DB->prepare("UPDATE media_in_venue SET removed_by_user_account_id=:removed_by_user_account_id," . " removed_at=:removed_at , removal_approved_at= :removal_approved_at WHERE " . " media_id=:media_id AND removed_at IS NULL ");
$stat->execute(array('media_id' => $media->getId(), 'removed_at' => \TimeSource::getFormattedForDataBase(), 'removal_approved_at' => \TimeSource::getFormattedForDataBase(), 'removed_by_user_account_id' => $user->getId()));
$stat = $DB->prepare("UPDATE media_in_event SET removed_by_user_account_id=:removed_by_user_account_id," . " removed_at=:removed_at , removal_approved_at= :removal_approved_at WHERE " . " media_id=:media_id AND removed_at IS NULL ");
$stat->execute(array('media_id' => $media->getId(), 'removed_at' => \TimeSource::getFormattedForDataBase(), 'removal_approved_at' => \TimeSource::getFormattedForDataBase(), 'removed_by_user_account_id' => $user->getId()));
$stat = $DB->prepare("UPDATE media_information SET deleted_by_user_account_id=:deleted_by_user_account_id," . " deleted_at=:deleted_at WHERE " . " id=:id AND deleted_at IS NULL ");
$stat->execute(array('id' => $media->getId(), 'deleted_at' => \TimeSource::getFormattedForDataBase(), 'deleted_by_user_account_id' => $user->getId()));
$DB->commit();
} catch (Exception $e) {
$DB->rollBack();
}
$media->deleteFiles();
}
public function create(ImportURLModel $importURL, SiteModel $site, UserAccountModel $creator)
{
global $DB;
try {
$DB->beginTransaction();
$stat = $DB->prepare("SELECT max(slug) AS c FROM import_url_information WHERE site_id=:site_id");
$stat->execute(array('site_id' => $site->getId()));
$data = $stat->fetch();
$importURL->setSlug($data['c'] + 1);
$stat = $DB->prepare("INSERT INTO import_url_information (site_id, slug, title,url,url_canonical,created_at,group_id,is_enabled,country_id,area_id, approved_at, is_manual_events_creation) " . "VALUES (:site_id, :slug, :title,:url,:url_canonical, :created_at, :group_id,:is_enabled,:country_id,:area_id,:approved_at,:is_manual_events_creation) RETURNING id");
$stat->execute(array('site_id' => $site->getId(), 'slug' => $importURL->getSlug(), 'title' => substr($importURL->getTitle(), 0, VARCHAR_COLUMN_LENGTH_USED), 'url' => substr($importURL->getUrl(), 0, VARCHAR_COLUMN_LENGTH_USED), 'url_canonical' => substr($importURL->getUrlCanonical(), 0, VARCHAR_COLUMN_LENGTH_USED), 'group_id' => $importURL->getGroupId(), 'country_id' => $importURL->getCountryId(), 'area_id' => $importURL->getAreaId(), 'created_at' => \TimeSource::getFormattedForDataBase(), 'approved_at' => \TimeSource::getFormattedForDataBase(), 'is_enabled' => $importURL->getIsEnabled() ? 1 : 0, 'is_manual_events_creation' => $importURL->getIsManualEventsCreation() ? 1 : 0));
$data = $stat->fetch();
$importURL->setId($data['id']);
$stat = $DB->prepare("INSERT INTO import_url_history (import_url_id, title, user_account_id , created_at,group_id,is_enabled,country_id,area_id, approved_at, is_new, is_manual_events_creation) VALUES " . "(:curated_list_id, :title, :user_account_id , :created_at, :group_id,:is_enabled,:country_id,:area_id, :approved_at, '1', :is_manual_events_creation )");
$stat->execute(array('curated_list_id' => $importURL->getId(), 'title' => substr($importURL->getTitle(), 0, VARCHAR_COLUMN_LENGTH_USED), 'group_id' => $importURL->getGroupId(), 'country_id' => $importURL->getCountryId(), 'area_id' => $importURL->getAreaId(), 'user_account_id' => $creator->getId(), 'created_at' => \TimeSource::getFormattedForDataBase(), 'approved_at' => \TimeSource::getFormattedForDataBase(), 'is_enabled' => $importURL->getIsEnabled() ? 1 : 0, 'is_manual_events_creation' => $importURL->getIsManualEventsCreation() ? 1 : 0));
$DB->commit();
} catch (Exception $e) {
$DB->rollBack();
}
}
public function getPermissionsForUserInSite(UserAccountModel $userAccountModel = null, SiteModel $siteModel, $removeEditorPermissions = false, $includeChildrenPermissions = false)
{
global $DB, $CONFIG;
if ($userAccountModel) {
$stat = $DB->prepare("SELECT permission_in_user_group.* FROM permission_in_user_group " . " JOIN user_group_information ON user_group_information.id = permission_in_user_group.user_group_id AND user_group_information.is_deleted = '0' AND user_group_information.is_in_index = '0' " . " JOIN user_group_in_site ON user_group_in_site.user_group_id = user_group_information.id AND user_group_in_site.site_id = :site_id AND user_group_in_site.removed_at IS NULL " . " LEFT JOIN user_in_user_group ON user_in_user_group.user_group_id = user_group_information.id AND user_in_user_group.removed_at IS NULL " . " WHERE permission_in_user_group.removed_at IS NULL AND " . " ( user_in_user_group.user_account_id = :user_account_id OR user_group_information.is_includes_anonymous = '1' OR user_group_information.is_includes_users = '1' " . ($userAccountModel->getIsEmailVerified() ? " OR user_group_information.is_includes_verified_users = '1' " : "") . " ) ");
$stat->execute(array('user_account_id' => $userAccountModel->getId(), 'site_id' => $siteModel->getId()));
} else {
$stat = $DB->prepare("SELECT permission_in_user_group.* FROM permission_in_user_group " . " JOIN user_group_information ON user_group_information.id = permission_in_user_group.user_group_id AND user_group_information.is_deleted = '0' AND user_group_information.is_in_index = '0' " . " JOIN user_group_in_site ON user_group_in_site.user_group_id = user_group_information.id AND user_group_in_site.site_id = :site_id AND user_group_in_site.removed_at IS NULL " . " WHERE permission_in_user_group.removed_at IS NULL AND user_group_information.is_includes_anonymous = '1' ");
$stat->execute(array('site_id' => $siteModel->getId()));
}
$permissions = array();
while ($data = $stat->fetch()) {
$ext = $this->extensionsManager->getExtensionById($data['extension_id']);
if ($ext) {
$per = $ext->getUserPermission($data['permission_key']);
if ($per) {
$permissions[] = $per;
}
}
}
return new \UserPermissionsList($this->extensionsManager, $permissions, $userAccountModel, $CONFIG->siteReadOnly || $removeEditorPermissions, $includeChildrenPermissions);
}
