/** * @param string $imageName image name is comprised of UUID and original name (UUID/origName.extension) * @throws DBALException * @throws FileRemovalException */ public function removeImage($imageName) { $id = mb_substr($imageName, 0, mb_strpos($imageName, '/')); $file = sprintf('%s/%s', $this->imageFileRoot, $imageName); try { $this->em->beginTransaction(); $d = $this->em->createQuery('DELETE ' . Image::class . ' i WHERE i.id = :id')->execute(['id' => $id]); $directory = sprintf('%s/%s', $this->imageFileRoot, $id); // checks whether directory exists (each directory has always one file) if ($d > 0 and \file_exists($directory) and is_dir($directory)) { $r = \unlink($file); // and if so then remove file in it if ($r === false) { // file couldn't be removed $this->em->rollback(); $this->em->close(); throw new FileRemovalException(); } else { // remove directory FileSystem::delete($directory); } } $this->em->commit(); } catch (DBALException $e) { $this->em->rollback(); $this->em->close(); throw $e; } }
public function findEventsByType($logTypeID) { return $this->cache->load('logEvents-' . $logTypeID, function (&$dependencies) use($logTypeID) { return array_column($this->em->createQuery('SELECT e.id, e.name FROM ' . EventLog::class . ' e INDEX BY e.id WHERE e.logType = :typeID')->setParameter('typeID', $logTypeID)->getArrayResult(), 'name', 'id'); }); }
public function findAllLocales() { return $this->cache->load('locales', function () { $locales = $this->em->createQuery('SELECT l FROM ' . Locale::class . ' l INDEX BY l.name')->getArrayResult(); if (empty($locales)) { return []; } return $locales; }); }
/** * @return ArrayHash|null */ public function loadOptions() { $options = $this->cache->load(Option::getCacheKey(), function () use(&$dependencies) { $options = $this->em->createQuery('SELECT o FROM ' . Option::class . ' o')->getArrayResult(); if (empty($options)) { return null; } $options = ArrayHash::from(Arrays::associate($options, 'name=value')); $dependencies = [Cache::TAGS => Option::getCacheKey()]; return $options; }); return $options; }
/** * @param Comment $comment * @throws ActionFailedException */ public function remove(Comment $comment) { try { $this->em->beginTransaction(); $this->em->remove($comment)->flush(); $this->em->createQuery('UPDATE ' . Comment::class . ' c SET c.order = c.order - 1 WHERE c.page = :page and c.order > :order')->execute(['page' => $comment->getPageId(), 'order' => $comment->getOrder()]); $this->em->commit(); } catch (\Exception $e) { $this->em->rollback(); $this->em->close(); throw new ActionFailedException(); } }
/** * Returns current user identity, if any. * @return IIdentity|NULL */ public function getIdentity() { $identity = parent::getIdentity(); // if we have our fake identity, we now want to // convert it back into the real entity if ($identity instanceof FakeIdentity) { return $this->cache->load(sprintf('user-%s', $identity->getId()), function () use($identity) { return $this->entityManager->createQuery('SELECT u, roles FROM ' . $identity->getClass() . ' u LEFT JOIN u.roles roles WHERE u.id = :id')->setParameter('id', $identity->getId())->getOneOrNullResult(); }); //return $this->entityManager->getReference($identity->getClass(), $identity->getId()); } return $identity; }
/** * @param $event * @return EventLog|null Returns null if there is no EventLog with given name * @throws \Doctrine\ORM\ORMException * @throws \Exception */ private function getEventLog($event) { $eventLog = $this->cache->load(self::getEventLogCacheKey($event), function () use($event) { return $this->em->createQuery('SELECT el, lt FROM ' . EventLog::class . ' el JOIN el.logType lt WHERE el.name = :name')->setParameter('name', $event)->getOneOrNullResult(); }); return $eventLog; }
/** * @param Listing $listing * @param WorkedHours $newWorkedHours * @param array $daysToChange * @return ListingItem[] */ public function changeWorkedHours(Listing $listing, WorkedHours $newWorkedHours, array $daysToChange) { $workedHours = $this->workedHoursProvider->setupWorkedHoursEntity($newWorkedHours); $this->em->createQuery('UPDATE ' . ListingItem::class . ' li SET li.workedHours = :workedHours WHERE li.listing = :listing AND li.day IN (:days)')->setParameters(['workedHours' => $workedHours, 'listing' => $listing, 'days' => $daysToChange])->execute(); $updatedItems = $items = $this->listingItemsReader->findListingItems($listing, $daysToChange); return $updatedItems; }
/** * @param User $user * @return mixed */ public function getTotalWorkedStatistics(User $user) { $stats = $this->em->createQuery('SELECT SUM(time_to_sec(ADDTIME(SUBTIME(SUBTIME(wh.workEnd, wh.workStart), wh.lunch), wh.otherHours))) AS total_worked_hours, COUNT(li.id) AS worked_days FROM ' . Listing::class . ' l LEFT JOIN ' . ListingItem::class . ' li WITH li.listing = l LEFT JOIN li.workedHours wh WHERE l.user = :user')->setParameter('user', $user); return $stats->getSingleResult(); }
/** * @param Locality $locality * @return Locality * @throws \Doctrine\DBAL\DBALException * @throws \Exception */ public function setupLocalityEntity(Locality $locality) { /* In order to NOT auto increment locality ID counter in DB by INSERTs that actually wont happen (e.g. safePersist()) and because Doctrine2 does NOT support locking of entire tables, we have to use native SQL(MySQL) query. DUAL is "dummy" table - there is no need to reference any table (more info in MySQL SELECT documentation) */ $this->em->getConnection()->executeQuery('INSERT INTO locality (name) SELECT :name FROM DUAL WHERE NOT EXISTS( SELECT l.name FROM locality l WHERE l.name = :name) LIMIT 1', ['name' => $locality->getName()]); $result = $this->em->createQuery('SELECT l FROM ' . Locality::class . ' l WHERE l.name = :name')->setParameters(['name' => $locality->getName()])->getSingleResult(); return $result; }
/** * Vraci nahodne clanky * @param int $count * @param DateTime|NULL $period * @return Entities\Article[] */ public function getRandArticles($count = 1, $period = NULL) { if ($period === NULL) { $now = new DateTime(); $period = $now->modify("-6 month"); } $cacheId = 'rand-' . $count; $countAllArticles = (int) $this->countAllArticles(); $query = "SELECT a FROM \\App\\Model\\Entities\\Article a WHERE a.published = true " . "AND a.publishDate >= :date "; $randArticles = $this->em->createQuery($query)->setParameter('date', $period)->setMaxResults($count)->setFirstResult(mt_rand(0, $countAllArticles - 1))->useResultCache(TRUE, 600, $cacheId)->getResult(); return $randArticles; }
private function loadPermissions(Permission $acl) { $permissions = $this->em->createQuery('SELECT p, pr FROM ' . \Users\Authorization\Permission::class . ' p LEFT JOIN p.privilege pr')->execute(); /** @var \Users\Authorization\Permission $permission */ foreach ($permissions as $permission) { if ($permission->isAllowed() === true) { $acl->allow($permission->getRoleName(), $permission->getResourceName(), $permission->getPrivilegeName()); } else { $acl->deny($permission->getRoleName(), $permission->getResourceName(), $permission->getPrivilegeName()); } } $acl->allow(Role::GOD, IAuthorizator::ALL, IAuthorizator::ALL); }
/** * @param WorkedHours $workedHours * @return WorkedHours * @throws \Doctrine\DBAL\DBALException * @throws \Exception */ public function setupWorkedHoursEntity(WorkedHours $workedHours) { $values = $workedHours->toArray(true); /* In order to NOT auto increment workedHours ID counter in DB by INSERTs that actually wont happen (e.g. safePersist()) and because Doctrine2 does NOT support locking of entire tables, we have to use native SQL(MySQL) query. DUAL is "dummy" table - there is no need to reference any table (more info in MySQL SELECT documentation) */ $this->em->getConnection()->executeQuery('INSERT INTO worked_hours (work_start, work_end, lunch, other_hours) SELECT :workStart, :workEnd, :lunch, :otherHours FROM DUAL WHERE NOT EXISTS( SELECT work_start, work_end, lunch, other_hours FROM worked_hours WHERE work_start = :workStart AND work_end = :workEnd AND lunch = :lunch AND other_hours = :otherHours) LIMIT 1', $values); $result = $this->em->createQuery('SELECT wh FROM ' . WorkedHours::class . ' wh WHERE wh.workStart = :workStart AND wh.workEnd = :workEnd AND wh.lunch = :lunch AND wh.otherHours = :otherHours')->setParameters($values)->getOneOrNullResult(); return $result; }
/** * @param $path * @return null|Url */ private function loadUrlEntity($path) { /** @var Url $urlEntity */ $urlEntity = $this->cache->load($path, function (&$dependencies) use($path) { /** @var Url $urlEntity */ $urlEntity = $this->em->createQuery('SELECT u, rt FROM ' . Url::class . ' u LEFT JOIN u.actualUrlToRedirect rt WHERE u.urlPath = :urlPath')->setParameter('urlPath', $path)->getOneOrNullResult(); if ($urlEntity === null) { $this->logger->addError(sprintf('Page not found. URL_PATH: %s', $path)); return null; } $dependencies = [Nette\Caching\Cache::TAGS => $urlEntity->getCacheKey()]; return $urlEntity; }); return $urlEntity; }
/** * Method used for search functionality * * @param int $tagID * @return array */ public function searchByTag($tagID) { $rsm = new ResultSetMappingBuilder($this->em); $rsm->addEntityResult(Page::class, 'p'); $rsm->addFieldResult('p', 'id', 'id'); $rsm->addFieldResult('p', 'title', 'title'); $rsm->addFieldResult('p', 'intro', 'intro'); $rsm->addFieldResult('p', 'intro_html', 'introHtml'); //$rsm->addFieldResult('p', 'text', 'text'); $rsm->addMetaResult('p', 'author', 'author'); $rsm->addMetaResult('p', 'url', 'url'); $rsm->addFieldResult('p', 'created_at', 'createdAt'); $rsm->addFieldResult('p', 'is_draft', 'isDraft'); $rsm->addFieldResult('p', 'published_at', 'publishedAt'); $rsm->addFieldResult('p', 'allowed_comments', 'allowedComments'); $rsm->addScalarResult('commentsCount', 'commentsCount', 'integer'); $rsm->addIndexByColumn('p', 'id'); $rsm->addJoinedEntityResult(Locale::class, 'l', 'p', 'locale'); $rsm->addFieldResult('l', 'locale_id', 'id'); $rsm->addFieldResult('l', 'locale_name', 'name'); $rsm->addFieldResult('l', 'locale_code', 'code'); $rsm->addFieldResult('l', 'locale_lang', 'lang'); $rsm->addFieldResult('l', 'locale_default', 'default'); $nativeQuery = $this->em->createNativeQuery('SELECT p.id, p.title, p.intro, p.intro_html, p.author, p.url, p.created_at, p.is_draft, p.published_at, p.allowed_comments, COUNT(c.page) AS commentsCount, l.id AS locale_id, l.name AS locale_name, l.code AS locale_code, l.lang AS locale_lang, l.default AS locale_default FROM ( SELECT pts.page_id, pts.tag_id FROM page_tag pts WHERE pts.tag_id = :id GROUP BY pts.page_id ) AS pt JOIN page p ON (p.id = pt.page_id AND p.is_draft = 0 AND p.published_at <= NOW()) JOIN locale l ON (l.id = p.locale) LEFT JOIN comment c ON (c.page = pt.page_id) GROUP BY pt.page_id', $rsm)->setParameter('id', $tagID); $pages = $nativeQuery->getResult(); $this->em->createQuery('SELECT PARTIAL page.{id}, tags FROM ' . Page::class . ' page LEFT JOIN page.tags tags INDEX BY tags.id WHERE page.id IN (:ids)')->setParameter('ids', array_keys($pages))->execute(); return $pages; }
private function findOptions() { return $this->em->createQuery('SELECT o FROM ' . Option::class . ' o')->getResult(); }
/** * @param Role $role * @param array $permissionDefinitions * @throws DBALException * @throws \Exception */ public function save(Role $role, array $permissionDefinitions) { $resources = $this->em->createQuery('SELECT r FROM ' . Resource::class . ' r INDEX BY r.id')->execute(); $privileges = $this->em->createQuery('SELECT p FROM ' . Privilege::class . ' p INDEX BY p.id')->execute(); try { $this->em->beginTransaction(); $this->em->createQuery('DELETE ' . Permission::class . ' p WHERE p.role = :role')->execute(['role' => $role->getId()]); $parentRole = null; if ($role->hasParent()) { /** @var Role $parentRole */ $parentRole = $this->em->find(Role::class, $role->getParentId()); } foreach ($permissionDefinitions as $definition => $isAllowed) { $isAllowed = (bool) $isAllowed; $x = explode('-', $definition); // eg. 1-3 /** @var \Users\Authorization\Resource $resource */ $resource = $resources[$x[0]]; /** @var Privilege $privilege */ $privilege = $privileges[$x[1]]; // check Users\Authorization\Authorizator ACL assembling // Role without parent // privilege: allowed -> must be in database // privilege: denied -> does NOT have to be in database // Role with parent (all depths) /* ------------------------------------------------------------ parent | descendant | should be persisted? ------------------------------------------------------------ allowed allowed NO allowed denied YES denied denied NO denied allowed YES ------------------------------------------------------------ We save records where permission and denial differ */ if ($parentRole !== null) { // has parent if ($this->authorizator->isAllowed($parentRole, $resource->getName(), $privilege->getName()) === $isAllowed) { continue; } } else { // doesn't have parent if ($isAllowed === false) { continue; } } $permission = new Permission($role, $resource, $privilege, $isAllowed); $this->em->persist($permission); } $this->em->flush(); $this->em->commit(); $this->cache->remove('acl'); $this->onSuccessRolePermissionsEditing($role); } catch (\Exception $e) { $this->em->rollback(); $this->em->close(); // todo log error throw new $e(); } }
/** * @param int $actualUrlID * @return array */ private function findByActualUrl($actualUrlID) { return $this->em->createQuery('SELECT u FROM ' . Url::class . ' u WHERE u.actualUrlToRedirect = :urlToRedirect')->setParameter('urlToRedirect', $actualUrlID)->getResult(); }
/** * @param string $presenter * @param string $action * @param int $internal_id * @return mixed * @throws \Doctrine\ORM\NonUniqueResultException */ public function getUrl($presenter, $action, $internal_id) { return $this->em->createQuery('SELECT u FROM ' . Url::class . ' u WHERE u.presenter = :presenter AND u.action = :action AND u.internalId = :internalID')->setParameters(['presenter' => $presenter, 'action' => $action, 'internalID' => $internal_id])->getOneOrNullResult(); }
/** * @param string|Invitation $invitation Invitation's E-mail or instance of invitation * @return void */ public function removeInvitation($invitation) { $this->em->createQuery('DELETE ' . Invitation::class . ' i WHERE i = :invitation')->execute(['invitation' => $invitation]); }
/** * @param array $messagesReferencesIDs * @return void */ public function removeMessagesReferences(array $messagesReferencesIDs) { $this->em->createQuery('UPDATE ' . ReceivedMessage::class . ' rm SET rm.deleted = 1 WHERE rm.id IN(:IDs)')->setParameter('IDs', $messagesReferencesIDs)->execute(); }
/** * @param Page $page * @param array $commentsIDs * @return Comment[] */ private function findCommentsToReply(Page $page, array $commentsIDs) { return $this->em->createQuery('SELECT c FROM ' . Comment::class . ' c INDEX BY c.order WHERE c.page = :page AND c.order IN (:orderNumbers)')->execute(['page' => $page, 'orderNumbers' => $commentsIDs]); }
/** * @param $messageID * @return array */ public function findReceivedMessages($messageID) { return $this->em->createQuery('SELECT rm, partial r.{id, username} FROM ' . ReceivedMessage::class . ' rm JOIN rm.recipient r WHERE rm.message = :messageID')->setParameter('messageID', $messageID)->getArrayResult(); }
private function emailExists($email) { return (bool) $this->em->createQuery('SELECT COUNT(u.email) FROM ' . User::class . ' u WHERE u.email = :email')->setParameter('email', $email)->getSingleScalarResult(); }
/** * @param User $owner * @param int $year * @param int $month * @return array */ public function findListingsToMerge(User $owner, $year, $month) { return $this->em->createQuery('SELECT l.id, l.description FROM ' . Listing::class . ' l WHERE l.user = :user AND l.year = :year AND l.month = :month')->setParameters(['user' => $owner, 'year' => $year, 'month' => $month])->getArrayResult(); }
public function createQuery($query) { return $this->entityManager->createQuery($query); }
/** * @param $day * @param Listing $listing */ public function removeListingItem($day, Listing $listing) { $this->em->createQuery('DELETE ' . ListingItem::class . ' l WHERE l.listing = :listing AND l.day = :day')->execute(['listing' => $listing, 'day' => $day]); }