/**
* @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]);
}
