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 create(ImportedEventModel $importedEvent)
{
global $DB;
$stat = $DB->prepare("INSERT INTO imported_event ( import_url_id, import_id, title, " . "description, start_at, end_at, timezone, is_deleted, url, ticket_url, created_at, reoccur ) " . " VALUES ( :import_url_id, :import_id, :title, " . ":description, :start_at, :end_at, :timezone, '0', :url, :ticket_url, :created_at, :reoccur ) RETURNING id");
$stat->execute(array('import_url_id' => $importedEvent->getImportUrlId(), 'import_id' => $importedEvent->getImportId(), 'title' => substr($importedEvent->getTitle(), 0, VARCHAR_COLUMN_LENGTH_USED), 'description' => $importedEvent->getDescription(), 'start_at' => $importedEvent->getStartAtInUTC()->format("Y-m-d H:i:s"), 'end_at' => $importedEvent->getEndAtInUTC()->format("Y-m-d H:i:s"), 'timezone' => $importedEvent->getTimezone(), 'url' => $importedEvent->getUrl(), 'ticket_url' => $importedEvent->getTicketUrl(), 'reoccur' => $importedEvent->getReoccur() ? json_encode($importedEvent->getReoccur()) : null, 'created_at' => \TimeSource::getFormattedForDataBase()));
$data = $stat->fetch();
$importedEvent->setId($data['id']);
}
public function create(ContactSupportModel $contact)
{
global $DB;
$stat = $DB->prepare("INSERT INTO contact_support (subject, message, email, user_account_id, ip, browser, created_at, is_spam_honeypot_field_detected) " . "VALUES (:subject, :message, :email, :user_account_id, :ip, :browser, :created_at, :is_spam_honeypot_field_detected) RETURNING id");
$stat->execute(array('subject' => substr($contact->getSubject(), 0, VARCHAR_COLUMN_LENGTH_USED), 'message' => $contact->getMessage(), 'email' => substr($contact->getEmail(), 0, VARCHAR_COLUMN_LENGTH_USED), 'user_account_id' => $contact->getUserAccountId(), 'ip' => substr($contact->getIp(), 0, VARCHAR_COLUMN_LENGTH_USED), 'browser' => $contact->getBrowser(), 'created_at' => \TimeSource::getFormattedForDataBase(), 'is_spam_honeypot_field_detected' => $contact->getIsSpamHoneypotFieldDetected() ? 1 : 0));
$data = $stat->fetch();
$contact->setId($data['id']);
}
/**
*
* @return \models\API2ApplicationRequestTokenModel
*/
public function create(API2ApplicationModel $app, API2ApplicationRequestTokenModel $requestToken)
{
global $DB;
$requestToken->setRequestToken(createKey(1, 255));
// TODO make sure token is unique!!!!!
$stat = $DB->prepare("INSERT INTO api2_application_request_token (api2_application_id, request_token, created_at, user_id, " . "callback_url, is_callback_display, is_callback_javascript, is_editor, state_from_user) " . "VALUES (:api2_application_id, :request_token, :created_at, :user_id, :callback_url, " . ":is_callback_display, :is_callback_javascript, :is_editor, :state_from_user)");
$stat->execute(array('api2_application_id' => $app->getId(), 'request_token' => $requestToken->getRequestToken(), 'created_at' => \TimeSource::getFormattedForDataBase(), 'user_id' => null, 'callback_url' => $app->getIsCallbackUrl() ? $requestToken->getCallbackUrl() : null, 'is_callback_display' => $app->getIsCallbackDisplay() && $requestToken->getIsCallbackDisplay() ? 1 : 0, 'is_callback_javascript' => $app->getIsCallbackJavascript() && $requestToken->getIsCallbackJavascript() ? 1 : 0, 'is_editor' => $requestToken->getIsEditor() && $app->getIsEditor() ? 1 : 0, 'state_from_user' => $requestToken->getStateFromUser()));
return $requestToken;
}
public function createForAppAndUserId(API2ApplicationModel $app, $userID)
{
global $DB;
$stat = $DB->prepare("SELECT api2_application_user_token_information.* FROM api2_application_user_token_information WHERE " . "api2_application_id =:api2_application_id AND user_id =:user_id");
$stat->execute(array('api2_application_id' => $app->getId(), 'user_id' => $userID));
if ($stat->rowCount() == 0) {
$stat = $DB->prepare("INSERT INTO api2_application_user_token_information " . "(api2_application_id, user_id, user_token, user_secret, created_at) " . "VALUES (:api2_application_id, :user_id, :user_token, :user_secret, :created_at)");
$stat->execute(array('api2_application_id' => $app->getId(), 'user_id' => $userID, 'user_token' => createKey(1, 255), 'user_secret' => createKey(1, 255), 'created_at' => \TimeSource::getFormattedForDataBase()));
// TODO check for unique user_token
}
}
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;
}
public function addCountryToSite(CountryModel $country, SiteModel $site, UserAccountModel $user)
{
global $DB;
$stat = $DB->prepare("SELECT * FROM country_in_site_information WHERE site_id =:site_id AND country_id =:country_id");
$stat->execute(array('country_id' => $country->getId(), 'site_id' => $site->getId()));
if ($stat->rowCount() == 1) {
$stat = $DB->prepare("UPDATE country_in_site_information SET is_in='1' WHERE site_id =:site_id AND country_id =:country_id");
$stat->execute(array('country_id' => $country->getId(), 'site_id' => $site->getId()));
} else {
$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)");
$stat->execute(array('country_id' => $country->getId(), 'site_id' => $site->getId(), 'created_at' => \TimeSource::getFormattedForDataBase()));
}
}
/**
*
* @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 resetAccount(UserAccountModel $user, UserAccountResetModel $reset)
{
global $DB;
try {
$DB->beginTransaction();
$stat = $DB->prepare("UPDATE user_account_information SET password_hash=:password_hash WHERE id =:id");
$stat->execute(array('id' => $user->getId(), 'password_hash' => $user->getPasswordHash()));
$stat = $DB->prepare("UPDATE user_account_reset SET reset_at=:reset_at WHERE user_account_id =:user_account_id AND access_key=:access_key");
$stat->execute(array('user_account_id' => $user->getId(), 'access_key' => $reset->getAccessKey(), 'reset_at' => \TimeSource::getFormattedForDataBase()));
$DB->commit();
} catch (Exception $e) {
$DB->rollBack();
}
}
public function create(\BaseIncomingLink $incomingLink, SiteModel $site = null)
{
global $DB;
try {
$DB->beginTransaction();
$stat = $DB->prepare("INSERT INTO incoming_link (site_id, extension_id, type, source_url, target_url, reporter_useragent, reporter_ip, created_at) " . "VALUES (:site_id, :extension_id, :type, :source_url, :target_url, :reporter_useragent, :reporter_ip, :created_at) RETURNING id");
$stat->execute(array('site_id' => $site ? $site->getId() : null, 'extension_id' => $incomingLink->getTypeExtensionID(), 'type' => $incomingLink->getType(), 'source_url' => $incomingLink->getSourceURL(), 'target_url' => $incomingLink->getTargetURL(), 'reporter_useragent' => $incomingLink->getReporterUseragent(), 'reporter_ip' => $incomingLink->getReporterIp(), 'created_at' => \TimeSource::getFormattedForDataBase()));
$data = $stat->fetch();
$incomingLink->setId($data['id']);
$DB->commit();
} catch (Exception $e) {
$DB->rollBack();
}
}
public function markVerifiedByUserAccountIDAndAccessKey($id, $access, $fromIP = null)
{
global $DB;
try {
$DB->beginTransaction();
$stat = $DB->prepare("UPDATE user_account_verify_email SET verified_at=:verified_at, verified_from_ip=:verified_from_ip WHERE user_account_id =:user_account_id AND access_key=:access_key");
$stat->execute(array('user_account_id' => $id, 'access_key' => $access, 'verified_at' => \TimeSource::getFormattedForDataBase(), 'verified_from_ip' => $fromIP));
$stat = $DB->prepare("UPDATE user_account_information SET is_email_verified='1' WHERE id =:id");
$stat->execute(array('id' => $id));
$DB->commit();
} catch (Exception $e) {
$DB->rollBack();
}
}
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 startUserWatchingSite(UserAccountModel $user, SiteModel $site)
{
global $DB;
$uws = $this->loadByUserAndSite($user, $site);
if ($uws && $uws->getIsWatching()) {
// all done!
} else {
if ($uws && !$uws->getIsWatching()) {
$stat = $DB->prepare("UPDATE user_watches_site_information SET is_watching='1', last_watch_started=:last_watch_started WHERE user_account_id =:user_account_id AND site_id=:site_id");
$stat->execute(array('user_account_id' => $user->getId(), 'site_id' => $site->getId(), 'last_watch_started' => \TimeSource::getFormattedForDataBase()));
} else {
$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' => $user->getId(), 'site_id' => $site->getId(), 'is_watching' => '1', 'is_was_once_watching' => '1', 'created_at' => \TimeSource::getFormattedForDataBase(), 'last_watch_started' => \TimeSource::getFormattedForDataBase()));
}
}
}
public function markUserInterestedInSite(UserAccountModel $user, SiteModel $site)
{
global $DB;
$uiis = $this->loadByUserAndSite($user, $site);
if ($uiis && $uiis->isInterested()) {
// all done!
} else {
if ($uiis && !$uiis->isInterested()) {
$stat = $DB->prepare("UPDATE user_interested_in_site_information SET is_interested='1' WHERE user_account_id =:user_account_id AND site_id=:site_id");
$stat->execute(array('user_account_id' => $user->getId(), 'site_id' => $site->getId()));
} else {
$stat = $DB->prepare("INSERT INTO user_interested_in_site_information (user_account_id,site_id,is_interested,created_at) " . "VALUES (:user_account_id,:site_id,:is_interested,:created_at)");
$stat->execute(array('user_account_id' => $user->getId(), 'site_id' => $site->getId(), 'is_interested' => '1', 'created_at' => \TimeSource::getFormattedForDataBase()));
}
}
}
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();
}
}
/**
* 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 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 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();
}
}
/** @return \models\EventRecurSetModel **/
public function getForEvent(EventModel $event)
{
global $DB;
$eventRecurSet = $this->loadForEvent($event);
if (!$eventRecurSet) {
try {
$DB->beginTransaction();
$stat = $DB->prepare("INSERT INTO event_recur_set (created_at) VALUES (:created_at) RETURNING id");
$stat->execute(array('created_at' => \TimeSource::getFormattedForDataBase()));
$data = $stat->fetch();
$eventRecurSet = new EventRecurSetModel();
$eventRecurSet->setId($data['id']);
$stat = $DB->prepare("UPDATE event_information SET event_recur_set_id = :ersi WHERE id = :id");
$stat->execute(array('ersi' => $eventRecurSet->getId(), 'id' => $event->getId()));
$DB->commit();
} catch (Exception $e) {
$DB->rollBack();
}
}
return $eventRecurSet;
}
public function createOrEdit(SiteModel $site, UserAccountModel $user)
{
global $DB;
$createdat = \TimeSource::getFormattedForDataBase();
try {
$DB->beginTransaction();
$stat = $DB->prepare("SELECT * FROM site_profile_media_information WHERE site_id=:site_id");
$stat->execute(array('site_id' => $site->getId()));
if ($stat->rowCount() == 1) {
$stat = $DB->prepare("UPDATE site_profile_media_information SET logo_media_id=:logo_media_id " . " WHERE site_id=:site_id");
} else {
$stat = $DB->prepare("INSERT INTO site_profile_media_information (site_id, logo_media_id) " . " VALUES (:site_id, :logo_media_id)");
}
$stat->execute(array('logo_media_id' => $site->getLogoMediaId(), 'site_id' => $site->getId()));
$stat = $DB->prepare("INSERT INTO site_profile_media_history (site_id, logo_media_id, user_account_id, created_at) " . " VALUES (:site_id, :logo_media_id, :user_account_id, :created_at)");
$stat->execute(array('site_id' => $site->getId(), 'logo_media_id' => $site->getLogoMediaId(), 'created_at' => $createdat, 'user_account_id' => $user->getId()));
$data = $stat->fetch();
$DB->commit();
} catch (Exception $e) {
$DB->rollBack();
}
}
public function setPermissionsForUserInApp(API2ApplicationUserPermissionsModel $permissions, 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 not there, just add
if ($stat->rowCount() == 0) {
$stat = $DB->prepare("INSERT INTO user_in_api2_application_information " . "(api2_application_id, user_id, is_editor, created_at) " . "VALUES (:api2_application_id, :user_id, :is_editor, :created_at)");
$stat->execute(array('api2_application_id' => $app->getId(), 'user_id' => $user->getId(), 'is_editor' => $permissions->getIsEditorGranted() ? 1 : 0, 'created_at' => \TimeSource::getFormattedForDataBase()));
return;
}
################## get data, check if we need to escalate or remove permissions
$userInAppData = $stat->fetch();
if ($permissions->getIsEditorGranted() && $userInAppData['is_editor'] == 0) {
$stat = $DB->prepare("UPDATE user_in_api2_application_information " . " SET is_editor='1' " . " 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 ($permissions->getIsEditorRefused() && $userInAppData['is_editor'] == 1) {
$stat = $DB->prepare("UPDATE user_in_api2_application_information " . " SET is_editor='0' " . " WHERE api2_application_id =:api2_application_id AND user_id =:user_id ");
$stat->execute(array('api2_application_id' => $app->getId(), 'user_id' => $user->getId()));
}
}
public function createWithMetaData(VenueModel $venue, SiteModel $site, VenueEditMetaDataModel $venueEditMetaDataModel)
{
global $DB, $EXTENSIONHOOKRUNNER;
$EXTENSIONHOOKRUNNER->beforeVenueSave($venue, $venueEditMetaDataModel->getUserAccount());
try {
$DB->beginTransaction();
$stat = $DB->prepare("SELECT max(slug) AS c FROM venue_information WHERE site_id=:site_id");
$stat->execute(array('site_id' => $site->getId()));
$data = $stat->fetch();
$venue->setSlug($data['c'] + 1);
$stat = $DB->prepare("INSERT INTO venue_information (site_id, slug, title," . "description,lat,lng,country_id,area_id,created_at,approved_at,address,address_code, is_deleted) " . "VALUES (:site_id, :slug, :title, " . ":description, :lat, :lng,:country_id, :area_id,:created_at,:approved_at,:address,:address_code, '0') RETURNING id");
$stat->execute(array('site_id' => $site->getId(), 'slug' => $venue->getSlug(), 'title' => substr($venue->getTitle(), 0, VARCHAR_COLUMN_LENGTH_USED), 'lat' => $venue->getLat(), 'lng' => $venue->getLng(), 'description' => $venue->getDescription(), 'address' => $venue->getAddress(), 'address_code' => $venue->getAddressCode(), 'country_id' => $venue->getCountryId(), 'area_id' => $venue->getAreaId(), 'created_at' => \TimeSource::getFormattedForDataBase(), 'approved_at' => \TimeSource::getFormattedForDataBase()));
$data = $stat->fetch();
$venue->setId($data['id']);
$stat = $DB->prepare("INSERT INTO venue_history (venue_id, title,description,lat,lng, country_id,area_id,user_account_id , created_at,approved_at,address,address_code, is_new, is_deleted, edit_comment) VALUES " . "(:venue_id,:title, :description, :lat, :lng,:country_id,:area_id,:user_account_id , :created_at,:approved_at,:address,:address_code, '1', '0', :edit_comment)");
$stat->execute(array('venue_id' => $venue->getId(), 'title' => substr($venue->getTitle(), 0, VARCHAR_COLUMN_LENGTH_USED), 'lat' => $venue->getLat(), 'lng' => $venue->getLng(), 'description' => $venue->getDescription(), 'address' => $venue->getAddress(), 'address_code' => $venue->getAddressCode(), 'user_account_id' => $venueEditMetaDataModel->getUserAccount() ? $venueEditMetaDataModel->getUserAccount()->getId() : null, 'country_id' => $venue->getCountryId(), 'area_id' => $venue->getAreaId(), 'created_at' => \TimeSource::getFormattedForDataBase(), 'approved_at' => \TimeSource::getFormattedForDataBase(), 'edit_comment' => $venueEditMetaDataModel->getEditComment()));
$data = $stat->fetch();
$DB->commit();
} catch (Exception $e) {
$DB->rollBack();
}
}
public function create(AreaModel $area, AreaModel $parentArea = null, SiteModel $site, CountryModel $country, UserAccountModel $creator = null)
{
global $DB;
try {
$DB->beginTransaction();
$stat = $DB->prepare("SELECT max(slug) AS c FROM area_information WHERE site_id=:site_id");
$stat->execute(array('site_id' => $site->getId()));
$data = $stat->fetch();
$area->setSlug($data['c'] + 1);
if ($parentArea) {
$area->setParentAreaId($parentArea->getId());
}
$stat = $DB->prepare("INSERT INTO area_information (site_id, slug, title,description,country_id,parent_area_id,created_at,approved_at,cache_area_has_parent_generated, is_deleted) " . "VALUES (:site_id, :slug, :title,:description,:country_id,:parent_area_id,:created_at,:approved_at,:cache_area_has_parent_generated, '0') RETURNING id");
$stat->execute(array('site_id' => $site->getId(), 'slug' => $area->getSlug(), 'title' => substr($area->getTitle(), 0, VARCHAR_COLUMN_LENGTH_USED), 'description' => $area->getDescription(), 'country_id' => $country->getId(), 'parent_area_id' => $parentArea ? $parentArea->getId() : null, 'created_at' => \TimeSource::getFormattedForDataBase(), 'approved_at' => \TimeSource::getFormattedForDataBase(), 'cache_area_has_parent_generated' => $parentArea ? '0' : '1'));
$data = $stat->fetch();
$area->setId($data['id']);
$stat = $DB->prepare("INSERT INTO area_history (area_id, title,description,country_id,parent_area_id,user_account_id , created_at, approved_at, is_new, is_deleted) VALUES " . "(:area_id, :title,:description,:country_id,:parent_area_id,:user_account_id, :created_at,:approved_at,'1','0')");
$stat->execute(array('area_id' => $area->getId(), 'title' => substr($area->getTitle(), 0, VARCHAR_COLUMN_LENGTH_USED), 'description' => $area->getDescription(), 'country_id' => $country->getId(), 'parent_area_id' => $parentArea ? $parentArea->getId() : null, 'user_account_id' => $creator ? $creator->getId() : null, 'created_at' => \TimeSource::getFormattedForDataBase(), 'approved_at' => \TimeSource::getFormattedForDataBase()));
$DB->commit();
} catch (Exception $e) {
$DB->rollBack();
}
}
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 update(UserGroupModel $userGroup, $fields, UserAccountModel $user = null)
{
$alreadyInTransaction = $this->db->inTransaction();
// Make Information Data
$fieldsSQL1 = array();
$fieldsParams1 = array('id' => $userGroup->getId());
foreach ($fields as $field) {
$fieldsSQL1[] = " " . $field . "=:" . $field . " ";
if ($field == 'title') {
$fieldsParams1['title'] = substr($userGroup->getTitle(), 0, VARCHAR_COLUMN_LENGTH_USED);
} else {
if ($field == 'description') {
$fieldsParams1['description'] = $userGroup->getDescription();
} else {
if ($field == 'is_deleted') {
$fieldsParams1['is_deleted'] = $userGroup->getIsDeleted() ? 1 : 0;
} else {
if ($field == 'is_in_index') {
$fieldsParams1['is_in_index'] = $userGroup->getIsDeleted() ? 1 : 0;
} else {
if ($field == 'is_includes_anonymous') {
$fieldsParams1['is_includes_anonymous'] = $userGroup->getIsIncludesAnonymous() ? 1 : 0;
} else {
if ($field == 'is_includes_users') {
$fieldsParams1['is_includes_users'] = $userGroup->getIsIncludesUsers() ? 1 : 0;
} else {
if ($field == 'is_includes_verified_users') {
$fieldsParams1['is_includes_verified_users'] = $userGroup->getIsIncludesVerifiedUsers() ? 1 : 0;
}
}
}
}
}
}
}
}
// Make History Data
$fieldsSQL2 = array('user_group_id', 'user_account_id', 'created_at');
$fieldsSQLParams2 = array(':user_group_id', ':user_account_id', ':created_at');
$fieldsParams2 = array('user_group_id' => $userGroup->getId(), 'user_account_id' => $user ? $user->getId() : null, 'created_at' => $this->timesource->getFormattedForDataBase());
foreach ($this->possibleFields as $field) {
if (in_array($field, $fields) || $field == 'title') {
$fieldsSQL2[] = " " . $field . " ";
$fieldsSQLParams2[] = " :" . $field . " ";
if ($field == 'title') {
$fieldsParams2['title'] = substr($userGroup->getTitle(), 0, VARCHAR_COLUMN_LENGTH_USED);
} else {
if ($field == 'description') {
$fieldsParams2['description'] = $userGroup->getDescription();
} else {
if ($field == 'is_deleted') {
$fieldsParams2['is_deleted'] = $userGroup->getIsDeleted() ? 1 : 0;
} else {
if ($field == 'is_in_index') {
$fieldsParams2['is_in_index'] = $userGroup->getIsDeleted() ? 1 : 0;
} else {
if ($field == 'is_includes_anonymous') {
$fieldsParams2['is_includes_anonymous'] = $userGroup->getIsIncludesAnonymous() ? 1 : 0;
} else {
if ($field == 'is_includes_users') {
$fieldsParams2['is_includes_users'] = $userGroup->getIsIncludesUsers() ? 1 : 0;
} else {
if ($field == 'is_includes_verified_users') {
$fieldsParams2['is_includes_verified_users'] = $userGroup->getIsIncludesVerifiedUsers() ? 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 user_group_information SET " . implode(",", $fieldsSQL1) . " WHERE id=:id");
$stat->execute($fieldsParams1);
// History SQL
$stat = $this->db->prepare("INSERT INTO user_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 create(UserAtEventModel $userAtEvent)
{
global $DB;
$stat = $DB->prepare("INSERT INTO user_at_event_information (user_account_id,event_id,is_plan_attending,is_plan_maybe_attending,is_plan_public,created_at) " . "VALUES (:user_account_id,:event_id,:is_plan_attending,:is_plan_maybe_attending,:is_plan_public,:created_at)");
$stat->execute(array('user_account_id' => $userAtEvent->getUserAccountId(), 'event_id' => $userAtEvent->getEventId(), 'is_plan_attending' => $userAtEvent->getIsPlanAttending() ? 1 : 0, 'is_plan_maybe_attending' => $userAtEvent->getIsPlanMaybeAttending() ? 1 : 0, 'is_plan_public' => $userAtEvent->getIsPlanPublic() ? 1 : 0, 'created_at' => \TimeSource::getFormattedForDataBase()));
}
public function markSent(OrganiserEmailModel $organiserEmailModel)
{
global $DB;
$stat = $DB->prepare("UPDATE organiser_email SET sent_at=:sent_at WHERE id=:id");
$stat->execute(array('id' => $organiserEmailModel->getId(), 'sent_at' => \TimeSource::getFormattedForDataBase()));
}
