Creates a new Query object.
| public createQuery ( $dql = "" ) : Doctrine\ORM\Query | ||
| Résultat | Doctrine\ORM\Query | |
/**
* Loads a locale.
*
* @param mixed $resource A resource
* @param string $locale A locale
* @param string $domain The domain
*
* @return MessageCatalogue A MessageCatalogue instance
*
* @throws NotFoundResourceException when the resource cannot be found
* @throws InvalidResourceException when the resource cannot be loaded
*/
public function load($resource, $locale, $domain = 'messages')
{
if ($this->loadAll !== true) {
return new MessageCatalogue($locale);
}
$dql = <<<'DQL'
SELECT
t
FROM
MjrLibraryEntitiesBundle:System\Translation t
WHERE
t.Locale = :locale
ORDER BY t.Id ASC
DQL;
$query = $this->entityManager->createQuery($dql);
$query->setParameter(':locale', $locale);
/** @var Translation[] $results */
$results = $query->getResult();
$catalogue = new MessageCatalogue($locale);
if (count($results) > 0) {
foreach ($results as $result) {
$catalogue->set($result->getIdentity(), $result->getTranslation(), $domain);
}
}
return $catalogue;
}
private function loadLicenses()
{
$dql = 'SELECT l FROM Doctrine\\Bundle\\LicenseManagerBundle\\Entity\\License l INDEX BY l.spdxIdentifier';
$query = $this->entityManager->createQuery($dql);
$licenses = $query->getResult();
$url = "http://spdx.org/licenses/";
$content = file_get_contents($url);
$crawler = new Crawler($content);
$rows = $crawler->filter('table tbody tr');
foreach ($rows as $row) {
$row = new Crawler($row);
$tds = $row->filter('td');
$isOSIApproved = $tds->eq(2)->text() === "Y";
if (!$isOSIApproved) {
continue;
}
$identifier = trim($tds->eq(1)->text());
if (isset($licenses[$identifier])) {
continue;
}
$name = $tds->eq(0)->text();
$licenseUrl = $url . substr($tds->eq(3)->filter('a')->attr('href'), 2);
$license = new License($name, $licenseUrl, $identifier);
$this->entityManager->persist($license);
}
$this->entityManager->flush();
}
public function eliminarItemsPorInstrumento($idInstrumento)
{
$query = "DELETE\r\n\r\n FROM AppModelBundle:InstrumentoItem iin\r\n\r\n WHERE 1 = 1\r\n \r\n AND iin.ins = :idInstrumento\r\n ";
$query = $this->em->createQuery($query);
$query->setParameter('idInstrumento', $idInstrumento);
return $query->execute();
}
/**
* @param VersionableInterface $resource
* @return array
*/
public function getVersions(VersionableInterface $resource)
{
$query = $this->_em->createQuery("SELECT v FROM ResourceVersion v INDEX BY v.version " . "WHERE v.resourceName = ?1 AND v.resourceId = ?2 ORDER BY v.version DESC");
$query->setParameter(1, get_class($resource));
$query->setParameter(2, $resource->getResourceId());
return $query->getResult();
}
/**
* @param $names
* @return array
*/
public function getIngredientsByName($names)
{
$query = $this->entityManager->createQuery('SELECT i FROM CookWithMeBundle:Ingredient i WHERE i.name IN (:names)');
$query->setParameters(array('names' => $names));
$ingredients = $query->getResult();
return $ingredients;
}
public function obtenerAlternativasPorItem($idItem)
{
$query = "SELECT\r\n\t\tite.id as idItem,\r\n\t\talt.id as idAlternativa,\r\n\t\talt.texto as textoAlternativa\r\n\r\n\t\tFROM AppModelBundle:Alternativa alt\r\n\t\tJOIN alt.ite ite\r\n\r\n\t\tWHERE 1 = 1\r\n\t\t\r\n\t\tAND ite.id = :idItem\r\n\t\t";
$query = $this->em->createQuery($query);
$query->setParameter('idItem', $idItem);
return $query->getArrayResult();
}
/**
* ConfigService constructor.
* @param ContainerInterface $config
* @param EntityManager $em
* @throws ConfigValueCanNotBeDecoded
* @internal param Redis $redis
*/
public function __construct(ContainerInterface $config, EntityManager $em)
{
$this->Cache = $config->get('snc_redis.default');
$this->EntityManager = $em;
$this->Entries = unserialize($this->Cache->get(self::CACHE_KEY));
if (!is_array($this->Entries) || count($this->Entries) < 1) {
$this->Entries = array();
$dql = <<<'DQL'
SELECT c FROM MjrLibraryEntitiesBundle:Config c
DQL;
$query = $this->EntityManager->createQuery($dql);
/** @var Config[] $results */
$results = $query->getResult();
if (count($results) > 0) {
foreach ($results as $result) {
if (!isset($this->Entries[$result->getModule()])) {
$this->Entries[$result->getModule()] = array();
}
$value = unserialize($result->getValue());
if (!$value instanceof ConfigInterface) {
throw new ConfigValueCanNotBeDecoded('The Config Value for Module ' . $result->getModule() . ' with the ident ' . $result->getIdent() . ' could not be unserialized. Please check the Value!');
}
$this->Entries[$result->getModule()][$result->getIdent()] = $value;
}
}
$this->Cache->set(self::CACHE_KEY, serialize($this->Entries));
}
}
protected function execute(InputInterface $input, OutputInterface $output)
{
$app = $this->getProjectApplication();
$this->logger = $this->getLogger();
$this->regionService = $app['region.skyforge.service'];
$this->regionService->setRegion($input->getOption('region'));
/** @var EntityManager $em */
$this->em = $app['orm.ems'][$this->regionService->getDbConnectionNameByRegion()];
/** @var StatService $statService */
$this->statService = $app['stat.skyforge.service'];
$pantheonRepository = $this->em->getRepository('Erliz\\SkyforgeBundle\\Entity\\Pantheon');
$communityRepository = $this->em->getRepository('Erliz\\SkyforgeBundle\\Entity\\Community');
// $lockFilePath = $app['config']['app']['path'].'/cache/curl/parse.lock';
// if (is_file($lockFilePath)) {
// throw new RuntimeException('Another parse in progress');
// } else {
// file_put_contents($lockFilePath, getmypid());
// }
if ($playerId = $input->getOption('avatar')) {
$this->statService->updatePlayer($playerId, true);
}
if ($communityId = $input->getOption('id')) {
$community = $communityRepository->find($communityId);
if (!$community) {
$community = $pantheonRepository->find($communityId);
}
if (!$community) {
$this->logger->addInfo(sprintf('Community with id %s not found in db', $communityId));
} else {
$this->updateCommunityMembers($community, $output);
$this->flush();
}
}
if ($input->getOption('pantheons') || $input->getOption('communities')) {
$lastId = $input->getOption('lastId');
if ($input->getOption('pantheons')) {
$sqlResponse = $this->em->createQuery("\n SELECT pt.id, count(pl.id) cnt\n FROM Erliz\\SkyforgeBundle\\Entity\\Pantheon pt\n JOIN pt.members pl\n group by pt.id\n order by cnt DESC")->getScalarResult();
$repo = $pantheonRepository;
} else {
$sqlResponse = $this->em->createQuery("\n SELECT pt.id, count(pl.id) cnt\n FROM Erliz\\SkyforgeBundle\\Entity\\Community pt\n JOIN pt.members pl\n group by pt.id\n order by cnt DESC")->getScalarResult();
$repo = $communityRepository;
}
$communityIds = array_map('current', $sqlResponse);
$communitiesCount = count($communityIds);
/** @var CommunityInterface $community */
foreach ($communityIds as $index => $communityId) {
if ($communityId == $lastId) {
$lastId = false;
}
if ($lastId) {
continue;
}
$this->updateCommunityMembers($repo->find($communityId), $output);
$this->logger->addInfo(sprintf('Processed %s / %s', $index + 1, $communitiesCount));
$this->flush();
}
}
// unlink($lockFilePath);
}
public function setDefaultOptions(OptionsResolverInterface $resolver)
{
$roles = $this->em->createQuery('SELECT r FROM TSKUserBundle:Role r')->execute();
foreach ($roles as $role) {
$choices[$role->getName()] = $role->getName();
}
$resolver->setDefaults(array('required' => true, 'expanded' => true, 'multiple' => true, 'choices' => $choices));
}
/**
* Marks all tips as unread
*
* @param Request $request The request
*
* @return array|\Dunglas\ApiBundle\Model\PaginatorInterface|\Traversable
*
* @throws RuntimeException
*/
public function __invoke(Request $request)
{
$dql = "DELETE FROM PartKeepr\\TipOfTheDayBundle\\Entity\\TipOfTheDayHistory th WHERE th.user = :user";
$query = $this->entityManager->createQuery($dql);
$query->setParameter("user", $this->userService->getUser());
$query->execute();
return new Response("OK");
}
/**
* После успешного логина
*
* @param InteractiveLoginEvent $event
*/
public function onSecurityInteractiveLogin(InteractiveLoginEvent $event)
{
# запоминаем время логина пользователя
$userId = $event->getAuthenticationToken()->getUser()->getId();
if ($userId) {
$lastLogin = date('Y-m-d H:i:s');
$this->em->createQuery("\r\n\t\t\t\tUPDATE EvrikaMainBundle:User u\r\n\t\t\t\tSET u.lastLogin = '******'\r\n\t\t\t\tWHERE u.id = {$userId}\r\n\t\t\t")->execute();
}
}
public function obtenerAreasPorPlan($idPlan, $idNivel, $idPeriodo)
{
$query = "SELECT\r\n are.id as idArea,\r\n are.nombre as nombreArea\r\n\r\n FROM AppModelBundle:PlanArea pit\r\n JOIN pit.are are\r\n\r\n WHERE 1 = 1\r\n \r\n AND pit.pla = :idPlan\r\n AND pit.niv = :idNivel\r\n AND pit.per = :idPeriodo\r\n\r\n ";
$query = $this->em->createQuery($query);
$query->setParameter('idPlan', $idPlan);
$query->setParameter('idNivel', $idNivel);
$query->setParameter('idPeriodo', $idPeriodo);
return $query->getArrayResult();
}
/**
* Execute given dql query
*
* @param string $dql
* @param string $type
* @return array
*/
private function executeQuery($dql, $type)
{
$query = $this->em->createQuery($dql);
$query->setParameters($this->params);
$query->setParameter('type', $type);
return array_map(function (array $row) {
return $row['email'];
}, $query->getResult());
}
/**
* Load translations from the DB
*
* @param string $locale example: en_US
* @param string $UnusedTextDomain not used
*
* @return \Zend\I18n\Translator\TextDomain
*/
public function load($locale, $UnusedTextDomain = null)
{
$messages = $this->entityMgr->createQuery('SELECT m.defaultText, m.text ' . 'FROM RcmI18n\\Entity\\Message m ' . 'WHERE m.locale = ?1')->setParameter(1, $locale)->getArrayResult();
$textDomain = new TextDomain();
foreach ($messages as &$message) {
$textDomain[$message['defaultText']] = $message['text'];
}
return $textDomain;
}
/**
* Loads from the database
*
* @param string $table The table that contains the pages (with title, slug and controller, configure this as resource in your routing.yml, provide as type: db )
* @param string $type The resource type
* @return RouteCollection the collection of routes stored in the database table
*/
public function load($table, $type = null)
{
$collection = new RouteCollection();
$pages = $this->em->createQuery('SELECT p FROM ' . $table . ' p')->execute();
foreach ($pages as $page) {
$collection->add('Cms' . $page->getTitle(), new Route($page->getSlug(), array('_controller' => $page->getController(), 'pageId' => $page->getId())));
}
return $collection;
}
public function getStocks(EntityManager $em, $parametres)
{
//$parametres de where
//categories $wct
$wct = ' 1=1 ';
if (isset($parametres['categories']) && is_array($parametres['categories'])) {
$wct = '';
$or = ' ';
foreach ($parametres['categories'] as $pcategorie) {
$wct .= $or . ' c.id=' . $pcategorie . ' ';
$or = ' OR ';
}
}
//echo "<br/>\$wct=".$wct."";
//couleurs $wco
$wco = ' 1=1 ';
if (isset($parametres['couleurs']) && is_array($parametres['couleurs'])) {
$wco = '';
$or = ' ';
foreach ($parametres['couleurs'] as $pcouleur) {
$wco .= $or . ' s.idcouleur=' . $pcouleur . ' ';
$or = ' OR ';
}
}
//echo "<br/>\$wco=".$wco."";
//tailles $wt
$wt = ' 1=1 ';
if (isset($parametres['tailles']) && is_array($parametres['tailles'])) {
$wt = '';
$or = ' ';
foreach ($parametres['tailles'] as $ptaille) {
$wt .= $or . ' s.idtaille=' . $ptaille . ' ';
$or = ' OR ';
}
}
//echo "<br/>\$wt=".$wt."";
//paramètres de regroupement group By
if (isset($parametres['regrouper']) && $parametres['regrouper'] == "produit") {
/* $q = $queryBuilder->select("'IDENTITY(s.id) AS id', sum(s.qtestock) as qtestock, sum(s.qtedefectueux) as qtedefectueux, s.idproduit ")
->from("ImieProduitBundle:Stock", "s")
->leftJoin("s.idproduit", "p")
->leftJoin("p.idcategorie", "c")
->leftJoin("p.idimage", "i")
;*/
$query = $em->createQuery("SELECT \n count(s.id) as gammes, sum(s.qtestock) as qtestock, sum(s.qtedefectueux) as qtedefectueux,\n 'IDENTITY(s.idproduit) AS idproduit' , p.id as idproduit, p.nom as nom, p.prix as prix, \n 'IDENTITY(p.idcategorie) AS idcategorie' , c.nom as categorie, \n 'IDENTITY(p.idimage) AS idimage' , i.nom as image\n FROM ImieProduitBundle:Stock s \n LEFT JOIN ImieProduitBundle:produit p with s.idproduit = p.id \n LEFT JOIN ImieProduitBundle:categorie c with p.idcategorie = c.id \n LEFT JOIN ImieProduitBundle:image i with p.idimage = i.id \n WHERE ( (" . $wct . ") AND (" . $wco . ") AND (" . $wt . ") )\n GROUP BY s.idproduit , p.idcategorie, p.idimage, c.nom, i.nom\n ORDER BY p.idcategorie ASC, s.idproduit ASC\n ");
} elseif (isset($parametres['regrouper']) && $parametres['regrouper'] == "categorie") {
$query = $em->createQuery("SELECT \n count(s.id) as gammes, sum(s.qtestock) as qtestock, sum(s.qtedefectueux) as qtedefectueux,\n 'IDENTITY(s.idproduit) AS idproduit' ,\n 'IDENTITY(p.idcategorie) AS idcategorie' , c.nom as categorie \n FROM ImieProduitBundle:Stock s \n LEFT JOIN ImieProduitBundle:produit p with s.idproduit = p.id \n LEFT JOIN ImieProduitBundle:categorie c with p.idcategorie = c.id \n WHERE ( (" . $wct . ") AND (" . $wco . ") AND (" . $wt . ") )\n GROUP BY p.idcategorie, c.nom\n ORDER BY p.idcategorie ASC, s.idproduit ASC\n ");
} else {
$queryBuilder = $em->createQueryBuilder();
$q = $queryBuilder->select("s, c, p, i")->from("ImieProduitBundle:Stock", "s")->leftJoin("s.idproduit", "p")->leftJoin("p.idcategorie", "c")->leftJoin("p.idimage", "i");
$q->where(" 1 = 1 ")->andWhere($wct)->andWhere($wco)->andWhere($wt);
$q->add('orderBy', 'p.idcategorie ASC, s.idproduit ASC,s.idtaille ASC ');
$query = $q->getQuery();
}
$stocks = $query->getResult();
return $stocks;
}
public function testFindOpenOpps()
{
$result = $this->em->createQuery('SELECT a,b
FROM MeetingMinder\\AttendeeBundle\\Entity\\OpenOpp a
JOIN a.sales b
WHERE a.sales = :id
ORDER BY a.week ASC')->setParameter(':id', 2)->getArrayResult();
dump($result);
}
public function getVilles()
{
$query = $this->em->createQuery('SELECT DISTINCT s.ville FROM AddressBook\\Entity\\Societe s ORDER BY s.ville');
$villes = $query->getScalarResult();
// Pour revenir à une dimension
$villes = array_map(function ($row) {
return $row['ville'];
}, $villes);
return $villes;
}
/**
* @param int $limit
*
* @return array
*/
public function getCountGroupByArtist($limit = 10)
{
$query = $this->entityManager->createQuery('
SELECT COUNT(t) AS nb, a.name AS name, a.id AS id
FROM AppBundle:Artist a
LEFT JOIN a.tracks t
GROUP BY a.id
')->setMaxResults($limit);
return $query->getResult();
}
public function removeGarbageFiles()
{
$dql = 'SELECT cf FROM SciGroupTinymcePluploadFileManagerBundle:ContentFile cf WHERE cf.uploadedAt < :uploaded_at';
$threshold = new \DateTime(sprintf('now - %d secs', $this->garbageFileTtl));
$garbageFiles = $this->entityManager->createQuery($dql)->setParameter('uploaded_at', $threshold)->iterate();
foreach ($garbageFiles as $garbageFile) {
$this->entityManager->remove($garbageFile[0]);
}
$this->entityManager->flush();
}
/**
* Build a populated paginator instance
*
* @param int $limit
* @return \Leapt\CoreBundle\Paginator\PaginatorInterface
*/
protected function buildPaginator($limit)
{
$this->loadFixture(new LoadPlayerData($limit));
$dql = <<<DQL
SELECT p FROM Leapt\\CoreBundle\\Tests\\Paginator\\Entity\\Player p
DQL;
$query = static::$em->createQuery($dql)->setMaxResults($limit);
$paginator = new DoctrineORMPaginator($query);
return $paginator;
}
/**
* Checks if the specified manufacturer exists.
*
* @param string $name The manufacturer name
*
* @return true if the manufacturer exists, false otherwise
*/
protected function manufacturerExists($name)
{
$dql = "SELECT COUNT(m) FROM PartKeepr\\ManufacturerBundle\\Entity\\Manufacturer m WHERE m.name = :name";
$query = $this->entityManager->createQuery($dql);
$query->setParameter('name', $name);
if ($query->getSingleScalarResult() == 0) {
return false;
} else {
return true;
}
}
public function createPaginator($dql, $page, $limit)
{
$offset = ($page - 1) * $limit;
$query = $this->entityManager->createQuery($dql)->setMaxResults($limit)->setFirstResult($offset);
$paginator = new Paginator($query);
$adapter = new DoctrinePaginatorAdapter($paginator);
$zendPaginator = new ZendPaginator($adapter);
$zendPaginator->setCurrentPageNumber($page);
$zendPaginator->setItemCountPerPage($limit);
return $zendPaginator;
}
public function testEhCapazDeCriarUmUsuario()
{
$this->_em = $this->doctrineContainer->getEntityManager();
$usuario = $this->getUsuario();
$this->assertEquals('Corrupto', $usuario->getNome());
$this->_em->persist($usuario);
$this->_em->flush();
$usuarios = $this->_em->createQuery('select u from Application\\Entity\\Usuario u')->execute();
$this->assertEquals(2, count($usuarios));
//FIX: usuário admin
}
public function listCompanies($page = 1, $pageSize = 20)
{
$query = $this->em->createQuery('SELECT c, cls FROM SSEICSSBundle:Company c LEFT JOIN c.class cls ORDER BY c.id ASC');
$paginator = new Paginator($query);
$paginator->setUseOutputWalkers(false);
$totalRecruits = count($paginator);
$pagesCount = ceil($totalRecruits / $pageSize);
$paginator->getQuery()->setFirstResult($pageSize * ($page - 1))->setMaxResults($pageSize);
$list = $query->getArrayResult();
return ["totalRecruits" => $totalRecruits, "currentPage" => $page, "pagesCount" => $pagesCount, "list" => $list];
}
/**
* Loads a locale.
*
* @param mixed $resource A resource
* @param string $locale A locale
* @param string $domain The domain
*
* @return MessageCatalogue A MessageCatalogue instance
*
* @api
*
* @throws NotFoundResourceException when the resource cannot be found
* @throws InvalidResourceException when the resource cannot be loaded
*/
public function load($resource, $locale, $domain = 'messages')
{
$query = $this->em->createQuery("SELECT Trn " . "FROM AppBundle:Translation\\Language Lng, AppBundle:Translation\\Message Msg, AppBundle:Translation\\Translation Trn " . "WHERE Lng.locale = :locale " . "AND Trn.language = Lng AND Trn.domain = :domain AND Trn.inCharge = true " . "AND Trn.message = Msg")->setParameter('locale', $locale)->setParameter('domain', $domain == 'messages' ? '' : $domain);
$translations = $query->getResult();
$catalogue = new MessageCatalogue($locale);
/** @var \AppBundle\Entity\Translation\Translation $translation */
foreach ($translations as $translation) {
$catalogue->set($translation->getMessage()->getName(), $translation->getText(), $translation->getDomain() == '' ? 'messages' : $translation->getDomain());
}
return $catalogue;
}
/**
* Add command to job queue if it has not been added earlier
*
* @param string $command
* @param array $commandArgs
* @param boolean $needFlush
* @return boolean|integer
*/
protected function addJob($command, $commandArgs = array(), $needFlush = false)
{
$currJob = $this->em->createQuery("SELECT j FROM JMSJobQueueBundle:Job j WHERE j.command = :command AND j.state <> :state")->setParameter('command', $command)->setParameter('state', Job::STATE_FINISHED)->getOneOrNullResult();
if (!$currJob) {
$job = new Job($command, $commandArgs);
$this->em->persist($job);
if ($needFlush) {
$this->em->flush($job);
}
}
return $currJob ? $currJob->getId() : true;
}
public function getIdRandomQuestion($leve)
{
$res = $this->em->createQuery('SELECT q.id FROM LDS\\Bundles\\DashboardBundle\\Entity\\Questions q WHERE q.level = :level')->setParameter('level', $leve)->getScalarResult();
$ids = array_map('current', $res);
$resutl = $this->em->createQuery('
SELECT q FROM LDS\\Bundles\\DashboardBundle\\Entity\\Questions q
WHERE q.id >= :random
AND q.level = :level
ORDER BY q.id ASC
')->setParameters(array('random' => $ids[array_rand($ids)], 'level' => $leve))->setMaxResults(1)->getSingleResult();
return $resutl;
}
/**
* Updates the tip database. Expects an array of page names.
*
* This method clears all page names and re-creates them. This saves
* alot of engineering, because we don't need to match contents
* within the database against contents in an array.
*
* @param array $aPageNames The page names as array. Page names are stored as string.
*/
private function updateTipDatabase(array $aPageNames)
{
$dql = 'DELETE FROM PartKeepr\\TipOfTheDayBundle\\Entity\\TipOfTheDay';
$query = $this->entityManager->createQuery($dql);
$query->execute();
foreach ($aPageNames as $pageName) {
$tip = new TipOfTheDay();
$tip->setName($pageName);
$this->entityManager->persist($tip);
}
$this->entityManager->flush();
}
public function testNaCriacao()
{
$tarefa = new Entity\Tarefa('TesteNome', 100, 1);
$this->_em = $this->doctrineContainer->getEntityManager();
$this->_em->persist($tarefa);
$this->_em->flush();
$tarefas = $this->_em->createQuery('select t from Application\\Entity\\Tarefa t')->execute();
$this->assertEquals(1, count($tarefas));
$tarefaBanco = $tarefas[0];
$this->assertEquals('TesteNome', $tarefaBanco->getNome());
$this->assertEquals(100, $tarefaBanco->getCusto());
$this->assertEquals(1, $tarefaBanco->getPasso());
}
