/**
* @inheritdoc
*/
public function index($entities = null)
{
$em = $this->modelManager->getManager();
$repo = $this->modelManager->get($this->getModel());
$indexRepo = $this->modelManager->get($this->getIndexModel());
// first, we need to check this is actually fulltext table
$rsm = new Query\ResultSetMappingBuilder($em);
$rsm->addScalarResult('Index_type', 'type');
$rsm->addScalarResult('Key_name', 'key');
$meta = $em->getClassMetadata($indexRepo->getClassName());
$indexName = 'search_text_index';
$indexCheck = $em->createNativeQuery("SHOW INDEX FROM {$meta->getTableName()} WHERE KEY_NAME = '{$indexName}' AND Index_type='FULLTEXT'", $rsm);
$q = $indexCheck->execute();
if (!count($q)) {
throw new FullTextIndexMissingException($meta->getTableName(), $indexName, array('text'));
}
$className = $repo->getClassName();
if ($entities instanceof $className) {
$entities = array($entities);
} elseif ($entities === null) {
$entities = $repo->findAll();
} else {
throw new EntityNotSupportedException($entities);
}
foreach ($entities as $entity) {
$deleteQb = $indexRepo->createQueryBuilder('t');
$deleteQb->delete()->where("t.entity = :entity");
$deleteQb->setParameter(':entity', $entity);
$deleteQb->getQuery()->execute();
/** @var FullText $indexEnitiy */
foreach ($this->entityMappings as $field => $properties) {
if (is_array($properties)) {
foreach ($properties as $subField) {
$subEntities = $entity->{"get{$field}"}();
if ($subEntities instanceof Collection) {
foreach ($subEntities as $subEntity) {
$indexEnitiy = $this->modelManager->create($this->getIndexModel());
$indexEnitiy->setEntity($entity);
$indexEnitiy->setText($subEntity->{"get{$subField}"}());
$em->persist($indexEnitiy);
}
} else {
$indexEnitiy = $this->modelManager->create($this->getIndexModel());
$indexEnitiy->setEntity($entity);
$indexEnitiy->setText($subEntities->{"get{$subField}"}());
$em->persist($indexEnitiy);
}
}
} else {
$indexEnitiy = $this->modelManager->create($this->getIndexModel());
$indexEnitiy->setEntity($entity);
$indexEnitiy->setText($entity->{"get{$field}"}());
$em->persist($indexEnitiy);
}
}
$em->flush();
}
}
public function register(Container $app)
{
$app['mc.cache.owners'] = $app->protect(function ($class) use($app) {
$em = $app['orm.em'];
$isClass = strrpos($class, '\\');
$class = strtolower($isClass ? substr($class, $isClass + 1) : $class);
$sql = "SELECT (SELECT value FROM jsonb_array_elements(json#>'{contact}') AS c WHERE\n c->'contactId' = (SELECT value FROM\n jsonb_array_elements(json#>'{metadata,resourceInfo,citation,responsibleParty}') AS rol\n WHERE rol@>'{\"role\":\"owner\"}' LIMIT 1)->'contactId')->>'organizationName' as \"owner\"\n FROM {$class} p\n GROUP BY owner";
$rsm = new ResultSetMappingBuilder($em);
$rsm->addScalarResult('owner', 'owner');
$query = $em->createNativeQuery($sql, $rsm);
$query->useResultCache(true, null, "mc_{$class}_owner");
$result = $query->getArrayResult();
return $result;
});
}
public function getAdminsOfBases(array $basList)
{
$rsm = new ResultSetMappingBuilder($this->em);
$rsm->addRootEntityFromClassMetadata('Alchemy\\Phrasea\\Model\\Entities\\User', 'u');
$rsm->addScalarResult('base_id', 'base_id');
$selectClause = $rsm->generateSelectClause();
$query = $this->em->createNativeQuery('
SELECT b.base_id, ' . $selectClause . ' FROM Users u, basusr b
WHERE u.id = b.usr_id
AND b.base_id IN (' . implode(', ', $basList) . ')
AND u.model_of IS NULL
AND b.actif="1"
AND b.canadmin="1"
AND u.deleted="0"', $rsm);
return $query->getResult();
}
/**
* @Route("/periodo-medio-pago", name="periodo_medio_pago")
*/
public function periodoMedioPagoAction(Request $request)
{
$em = $this->getDoctrine()->getManager();
$desde = $request->get('desde');
$hasta = $request->get('hasta');
if (!$desde) {
$desde = \DateTime::createFromFormat('d/m/Y', '01/01/1970');
} else {
$desde = \DateTime::createFromFormat('d/m/Y', $desde);
}
if (!$hasta) {
$hasta = \DateTime::createFromFormat('d/m/Y', '31/12/2050');
} else {
$hasta = \DateTime::createFromFormat('d/m/Y', $hasta);
}
$year = $desde->format('Y');
$trimestre = intval($desde->format('m'));
if ($trimestre >= 1 && $trimestre <= 3) {
$trimestre = "1er";
} elseif ($trimestre >= 4 && $trimestre <= 6) {
$trimestre = "2o";
} elseif ($trimestre >= 7 && $trimestre <= 9) {
$trimestre = "3er";
}
if ($trimestre >= 10 && $trimestre <= 12) {
$trimestre = "4o";
}
$rsm = new ResultSetMappingBuilder($em);
$rsm->addScalarResult('pagos', 'pagos');
$rsm->addScalarResult('total', 'total');
$rsm->addScalarResult('media', 'media');
$trs = array("aprovisionamiento", "adquisicion", "desagregar");
$resultados = array();
// FACTURAS PAGADAS EN EL TRIMESTRE
$query = $em->createNativeQuery("SELECT COUNT(*) as pagos,SUM(total_pagar) as total,(SUM(total_pagar*(DATEDIFF(f_pago,f_registro)-30)) ) as media" . " FROM rec,estado_factura WHERE tipo_registro=0 AND estado_factura_id = estado_factura.id AND estado_factura.anulada = 0 " . " AND f_registro < :hasta AND f_pago BETWEEN :desde AND :hasta", $rsm);
// Dentro del periodo legal de pago
$query->setParameter('desde', $desde->format('Y-m-d'));
$query->setParameter('hasta', $hasta->format('Y-m-d'));
$result = $query->getResult();
$resultados['pagadas'] = $result[0];
// FACTURAS NO PAGADAS EN EL TRIMESTRE
$query = $em->createNativeQuery("SELECT COUNT(*) as pagos,SUM(total_pagar) as total,(SUM(total_pagar*(DATEDIFF(:hasta,f_registro)-30)) ) as media" . " FROM rec,estado_factura WHERE tipo_registro=0 AND estado_factura_id = estado_factura.id AND estado_factura.anulada = 0" . " AND f_registro <= :hasta AND (f_pago IS NULL OR f_pago > :hasta)", $rsm);
// Dentro del periodo legal de pago
$query->setParameter('desde', $desde->format('Y-m-d'));
$query->setParameter('hasta', $hasta->format('Y-m-d'));
$result = $query->getResult();
$resultados['nopagadas'] = $result[0];
//print_r($resultados);
//return $this->render('::base.html.twig');
$html = $this->renderView('reports/periodoMedioPago.html.twig', array('resultados' => $resultados, 'trimestre' => $trimestre, 'year' => $year));
return new Response($this->get('knp_snappy.pdf')->getOutputFromHtml($html, array('orientation' => 'Landscape')), 200, array('Content-Type' => 'application/pdf', 'Content-Disposition' => 'attachment; filename="periodoMedioPago.pdf"'));
}
public function search($page = 1, $limit = 0)
{
if ($limit === null || $limit === 0) {
$limit = $this->configManager->get('newpost.max', "forum");
}
$configUsermanager = $this->configManager->getSymbbConfig('usermanager');
$configGroupManager = $this->configManager->getSymbbConfig('groupmanager');
$userlcass = $configUsermanager['user_class'];
$groupclass = $configGroupManager['group_class'];
$sql = "SELECT\n p\n FROM\n SymbbCoreForumBundle:Post p\n INNER JOIN\n SymbbCoreForumBundle:Topic t WITH\n t.id = p.topic\n LEFT JOIN\n SymbbCoreSystemBundle:Flag f WITH\n f.objectClass = 'Symbb\\Core\\ForumBundle\\Entity\\Post' AND\n f.objectId = p.id AND\n f.user = :user AND\n f.flag = '" . AbstractFlagHandler::FLAG_NEW . "'\n WHERE\n p.author != :user AND\n (\n ( SELECT COUNT(a.id) FROM SymbbCoreSystemBundle:Access a WHERE\n a.objectId = t.forum AND\n a.object = 'Symbb\\Core\\ForumBundle\\Entity\\Forum' AND\n a.identity = :userclass AND\n a.identityId = :user AND\n a.access = 'VIEW'\n ) > 0 OR\n ( SELECT COUNT(a2.id) FROM SymbbCoreSystemBundle:Access a2 WHERE\n a2.objectId = t.forum AND\n a2.object = 'Symbb\\Core\\ForumBundle\\Entity\\Forum' AND\n a2.identity = :groupclass AND\n a2.identityId IN (:groups) AND\n a2.access = 'VIEW'\n ) > 0\n )\n GROUP BY\n p.id\n ORDER BY\n f.id DESC,\n p.created DESC ";
$groupIds = array();
foreach ($this->getUser()->getGroups() as $group) {
$groupIds[] = $group->getId();
}
//// count
$query = $this->em->createQuery($sql);
$rsm = new ResultSetMappingBuilder($this->em);
$rsm->addScalarResult('count', 'count');
$queryCount = $query->getSQL();
$queryCount = "SELECT COUNT(*) count FROM (" . $queryCount . ") as temp";
$queryCount = $this->em->createNativeQuery($queryCount, $rsm);
$queryCount->setParameter(0, $this->getUser()->getId());
$queryCount->setParameter(1, $this->getUser()->getId());
$queryCount->setParameter(2, $userlcass);
$queryCount->setParameter(3, $this->getUser()->getId());
$queryCount->setParameter(4, $groupclass);
$queryCount->setParameter(5, $groupIds);
$count = $queryCount->getSingleScalarResult();
////
if (!$count) {
$count = 0;
}
$query->setParameter('user', $this->getUser()->getId());
$query->setParameter('userclass', $userlcass);
$query->setParameter('groupclass', $groupclass);
$query->setParameter('groups', $groupIds);
$query->setHint('knp_paginator.count', $count);
$pagination = $this->paginator->paginate($query, $page, $limit, array('distinct' => false));
return $pagination;
}
public function createPagination($query, $page, $limit)
{
$rsm = new ResultSetMappingBuilder($this->em);
$rsm->addScalarResult('count', 'count');
$queryCount = $query->getSql();
$queryCount = "SELECT COUNT(*) as count FROM (" . $queryCount . ") as temp";
$queryCount = $this->em->createNativeQuery($queryCount, $rsm);
$queryCount->setParameters($query->getParameters());
$count = $queryCount->getSingleScalarResult();
if (!$count) {
$count = 0;
}
if ($limit === null) {
$limit = 20;
}
if ($page === 'last') {
$page = $count / (int) $limit;
$page = ceil($page);
}
if ($page <= 0) {
$page = 1;
}
$query->setHint('knp_paginator.count', $count);
$pagination = $this->paginator->paginate($query, (int) $page, $limit, array('distinct' => false));
return $pagination;
}
public function connect(Application $app)
{
$controllers = $app['controllers_factory'];
$controllers->get('/{id}/view', function (Application $app, $id) {
$em = $app['orm.em'];
try {
$query = $em->createQuery("SELECT 1 from MetaCat\\Entity\\Project c where c.projectid = ?1");
$query->setParameter(1, $id);
$item = $query->getSingleScalarResult();
$subRequest = Request::create($app['url_generator']->generate('projectview', ['id' => $id]), 'GET');
return $app->handle($subRequest, HttpKernelInterface::SUB_REQUEST);
} catch (\Doctrine\ORM\NoResultException $e) {
try {
$query = $em->createQuery("SELECT 1 from MetaCat\\Entity\\Product c where c.productid = ?1");
$query->setParameter(1, $id);
$item = $query->getSingleScalarResult();
$subRequest = Request::create($app['url_generator']->generate('productview', ['id' => $id]), 'GET');
return $app->handle($subRequest, HttpKernelInterface::SUB_REQUEST);
} catch (\Doctrine\ORM\NoResultException $e) {
throw new HttpException(404, "No record found for id: {$id}");
}
}
})->bind('metadatabaseview');
$controllers->get('/{entity}/{id}.{format}', function (Application $app, $entity, $id, $format) {
$em = $app['orm.em'];
$qb = $em->createQueryBuilder();
$qb->select('c.' . $format)->from('MetaCat\\Entity\\' . ucfirst($entity), 'c')->where("c.{$entity}id = ?1")->setParameter(1, $id);
$query = $qb->getQuery();
try {
$result = $query->getSingleScalarResult();
} catch (\Doctrine\ORM\NoResultException $e) {
throw new HttpException(404, "No record found for id: {$id}");
}
return [trim($result)];
})->bind('metadata')->value('format', 'json');
$controllers->get('/{id}.{format}', function (Application $app, Request $request, $id, $format) {
$em = $app['orm.em'];
if (isset($app['config']['white']['entity'][$id])) {
$em = $app['orm.em'];
$class = 'MetaCat\\Entity\\' . ucfirst($id);
//check owner
$owners = array_flip(array_column($app['mc.cache.owners']($class), 'owner'));
$owner = $request->query->get('owner', '');
if ($owner && !isset($owners[$owner])) {
throw new HttpException(404, "Owner does not exist: {$owner}");
}
$sql = "SELECT json_agg(p.json) as out FROM {$id} p";
$where = " WHERE ((SELECT value FROM jsonb_array_elements(json#>'{contact}') AS c WHERE\n c->'contactId' = (SELECT value FROM\n jsonb_array_elements(json#>'{metadata,resourceInfo,citation,responsibleParty}') AS role\n WHERE role@>'{\"role\":\"owner\"}' LIMIT 1)->'contactId') ->>'organizationName') = ? ";
//add filter
if ($owner) {
$sql .= $where;
}
$rsm = new ResultSetMappingBuilder($em);
$rsm->addRootEntityFromClassMetadata($class, 'p');
$rsm->addScalarResult('out', 'out');
$query = $em->createNativeQuery($sql, $rsm);
if ($owner) {
$query->setParameter(1, $owner);
}
$item = $query->getSingleScalarResult();
if (!$item) {
throw new HttpException(404, "No {$id} records found.");
}
return [$item];
}
try {
$query = $em->createQuery("SELECT c.{$format} from MetaCat\\Entity\\Project c where c.projectid = ?1");
$query->setParameter(1, $id);
$item = $query->getSingleScalarResult();
} catch (\Doctrine\ORM\NoResultException $e) {
try {
$query = $em->createQuery("SELECT c.{$format} from MetaCat\\Entity\\Product c where c.productid = ?1");
$query->setParameter(1, $id);
$item = $query->getSingleScalarResult();
} catch (\Doctrine\ORM\NoResultException $e) {
throw new HttpException(404, "No record found for id: {$id}");
}
}
return [trim($item)];
})->bind('metadatabase')->value('format', 'json');
$controllers->get('{entity1}/{id}/{entity2}.{format}', function (Application $app, Request $request, $entity1, $id, $entity2) {
$em = $app['orm.em'];
$white = $app['config']['white']['entity'];
if (isset($white[$entity1], $white[$entity2])) {
$em = $app['orm.em'];
$class = 'MetaCat\\Entity\\' . ucfirst($entity2);
$class2 = 'MetaCat\\Entity\\' . ucfirst($entity1);
$col = $request->get('short') ? 'p.json#>\'{metadata,resourceInfo,citation}\'' : 'p.json';
$sql = "SELECT json_agg({$col}) as out FROM {$entity2} p JOIN {$entity1} p2 USING(projectid) WHERE p2.{$entity1}id = ?";
$rsm = new ResultSetMappingBuilder($em);
$rsm->addRootEntityFromClassMetadata($class, 'p');
$rsm->addJoinedEntityResult($class2, 'p2', 'p', 'projectid');
$rsm->addScalarResult('out', 'out');
$query = $em->createNativeQuery($sql, $rsm);
$query->setParameter(1, $id);
} else {
throw new HttpException(403, 'Entity name not allowed. Valid entities are: ' . join('|', array_keys($white)));
}
try {
$recs = $query->getScalarResult();
$out = @$recs[0]['out'];
if ($out) {
$request->request->set('format', 'json');
return [$out];
} else {
throw new HttpException(404, "No {$entity2}(s) found for {$entity1} id: {$id}");
}
} catch (\Doctrine\ORM\NoResultException $e) {
throw new HttpException(404, "No record found for id: {$id}");
}
})->bind('related')->value('format', 'json');
return $controllers;
}
protected function _count($alias = null)
{
$sql = $this->countSql($alias);
$rsm = new ResultSetMappingBuilder($this->em());
$rsm->addScalarResult('COUNT(*)', 'COUNT(*)', Type::INTEGER);
$query = $this->em()->createNativeQuery($sql, $rsm);
$this->setParams($query);
return (int) $query->getSingleScalarResult();
}
/**
* Search for a series
*
* @param string $q The search query
* @param integer $page The page of results to retrieve
* @param integer $perPage How many results to retrieve
* @return SearchResult The search results
* @see ChaosTangent\FansubEbooks\Bundle\AppBundle\DataFixtures\ORM\CreateSearchIndex
*/
public function search($q, $page = 1, $perPage = 30)
{
// count total results
$countSql = 'SELECT COUNT(s.id)
FROM series s
WHERE to_tsvector(:config, s.title) @@ to_tsquery(:query)';
$total = $this->_em->getConnection()->fetchColumn($countSql, [':config' => 'english', ':query' => $q], 0);
$fileSql = 'SELECT fs.id, COUNT(f.id) AS file_count
FROM series fs
JOIN files f ON f.series_id = fs.id
GROUP BY fs.id';
$lineSql = 'SELECT ls.id, COUNT(l.id) AS line_count
FROM series ls
JOIN files lf ON lf.series_id = ls.id
JOIN lines l ON l.file_id = lf.id
GROUP BY ls.id';
$tweetSql = 'SELECT ts.id, COUNT(t.id) AS tweet_count
FROM series ts
JOIN files tf ON tf.series_id = ts.id
JOIN lines tl ON tl.file_id = tf.id
JOIN tweets t ON t.line_id = tl.id
GROUP BY ts.id';
// fetch page of results
$rsm = new ResultSetMappingBuilder($this->_em);
$rsm->addRootEntityFromClassMetadata('ChaosTangent\\FansubEbooks\\Entity\\Series', 's');
$rsm->addScalarResult('file_count', 'file_count', 'integer');
$rsm->addScalarResult('line_count', 'line_count', 'integer');
$rsm->addScalarResult('tweet_count', 'tweet_count', 'integer');
$sql = 'WITH file_counts AS (' . $fileSql . '), line_counts AS (' . $lineSql . '), tweet_counts AS (' . $tweetSql . ')
SELECT ' . $rsm->generateSelectClause() . ', fc.file_count, lc.line_count, tc.tweet_count
FROM series s
LEFT JOIN file_counts fc ON fc.id = s.id
LEFT JOIN line_counts lc ON lc.id = s.id
LEFT JOIN tweet_counts tc ON tc.id = s.id
WHERE to_tsvector(:config, s.title) @@ to_tsquery(:query)
ORDER BY ts_rank(to_tsvector(:config, s.title), to_tsquery(:query))
LIMIT :limit OFFSET :offset';
$query = $this->_em->createNativeQuery($sql, $rsm);
$query->setParameters(['query' => $q, 'limit' => $perPage, 'offset' => ($page - 1) * $perPage, 'config' => 'english']);
$result = $query->getResult();
$ret = [];
foreach ($result as $row) {
$ret[] = $row[0]->setFileCount($row['file_count'])->setLineCount($row['line_count'])->setTweetCount($row['tweet_count']);
}
return new SearchResult($q, $ret, $total, $page, $perPage);
}
/**
* Search for a line
*
* @param string $q The search query
* @param integer $page The page of results to retrieve
* @param integer $perPage How many results to retrieve
* @return SearchResult The search results
* @see ChaosTangent\FansubEbooks\Bundle\AppBundle\DataFixtures\ORM\CreateSearchIndex
*/
public function search($q, $page = 1, $perPage = 30, Series $series = null)
{
if (empty(trim($q))) {
return new SearchResult($q, [], 0, $page, $perPage);
}
// default query parameters
$defaultParams = [':query' => trim($q), ':config' => 'english'];
// default where clause
$whereClause = 'WHERE to_tsvector(:config, l.line) @@ to_tsquery(:query)';
if ($series !== null) {
$whereClause .= ' AND f.series_id = :series';
$defaultParams['series'] = $series->getId();
}
// count total results from search query
$countSql = 'SELECT COUNT(l.id)
FROM lines l
JOIN files f ON f.id = l.file_id ' . $whereClause;
$total = $this->_em->getConnection()->fetchColumn($countSql, $defaultParams, 0);
// get the selected page of results from search query
$rsm = new ResultSetMappingBuilder($this->_em);
$rsm->addRootEntityFromClassMetadata('ChaosTangent\\FansubEbooks\\Entity\\Line', 'l');
$rsm->addScalarResult('positive_votes', 'positive_votes', 'integer');
$rsm->addScalarResult('negative_votes', 'negative_votes', 'integer');
$rsm->addScalarResult('tweet_id', 'tweet_id');
$sql = 'SELECT ' . $rsm->generateSelectClause() . ',
SUM(CASE WHEN v.positive = true THEN 1 ELSE 0 END) AS positive_votes,
SUM(CASE WHEN v.positive = false THEN 1 ELSE 0 END) AS negative_votes,
t.tweet_id
FROM lines l
JOIN files f ON f.id = l.file_id
LEFT JOIN votes v ON v.line_id = l.id
LEFT JOIN tweets t ON t.line_id = l.id ' . $whereClause . '
GROUP BY l.id, t.tweet_id
ORDER BY ts_rank(to_tsvector(:config, l.line), to_tsquery(:query))
LIMIT :limit OFFSET :offset';
$query = $this->_em->createNativeQuery($sql, $rsm);
$query->setParameters(array_merge($defaultParams, ['limit' => $perPage, 'offset' => ($page - 1) * $perPage]));
$result = $query->getResult();
$ret = [];
foreach ($result as $row) {
$ret[] = $row[0]->setPositiveVoteCount($row['positive_votes'])->setNegativeVoteCount($row['negative_votes'])->setTweetId($row['tweet_id']);
}
// bundle it all into a searchresult object
return new SearchResult($q, $ret, $total, $page, $perPage);
}
public function createPagination($query, $page, $limit)
{
$rsm = new ResultSetMappingBuilder($this->em);
$rsm->addScalarResult('count', 'count');
// get sql, get the from part and remove all other fields then the id field
// so that we have a query who select only one field
// for count this is better because we dont need the data
$queryCount = $query->getSql();
$queryCountTmp = explode("FROM", $queryCount);
$queryCountSelect = array_shift($queryCountTmp);
$queryCountEnd = implode("FROM", $queryCountTmp);
$queryCountSelect = explode(",", $queryCountSelect);
$queryCountSelect = reset($queryCountSelect);
if (strpos($queryCountEnd, "GROUP BY") === false) {
$queryCount = " SELECT COUNT(*) as count FROM " . $queryCountEnd;
} else {
$queryCount = "SELECT COUNT(*) as count FROM (" . $queryCountSelect . " FROM " . $queryCountEnd . ") as temp";
}
// create now the query based on the native sql query and get the count
$queryCount = $this->em->createNativeQuery($queryCount, $rsm);
$queryCount->setParameters($query->getParameters());
$count = $queryCount->getSingleScalarResult();
if (!$count) {
$count = 0;
}
if ($page === 'last') {
$page = $count / $limit;
$page = ceil($page);
}
if ($page <= 0) {
$page = 1;
}
$query->setHint('knp_paginator.count', $count);
$pagination = $this->paginator->paginate($query, (int) $page, $limit, array('distinct' => false));
return $pagination;
}
/**
* @expectedException Doctrine\ORM\Cache\CacheException
* @expectedExceptionMessage Second level cache does not support scalar results.
*/
public function testScalarResultException()
{
$result = array();
$key = new QueryCacheKey('query.key1', 0);
$rsm = new ResultSetMappingBuilder($this->em);
$rsm->addScalarResult('id', 'u');
$this->queryCache->put($key, $rsm, $result);
}
