/**
* Migrate data to plugin database from core table
*
* @param EntityManager $em
* @param OutputInterface $output
*
* @return void
*/
public function migrateData($em, $output)
{
$rsm = new ResultSetMapping();
$rsm->addEntityResult('Newscoop\\CommunityTickerBundle\\Entity\\CommunityTickerEvent', 'e');
$rsm->addFieldResult('e', 'id', 'id');
$rsm->addFieldResult('e', 'event', 'event');
$rsm->addFieldResult('e', 'params', 'params');
$rsm->addFieldResult('e', 'created', 'created');
$rsm->addJoinedEntityResult('Newscoop\\Entity\\User', 'u', 'e', 'user');
$rsm->addFieldResult('u', 'Id', 'id');
$query = $em->createNativeQuery('SELECT e.id, e.event, e.params, e.created, u.Id FROM community_ticker_event e ' . 'LEFT JOIN liveuser_users u ON u.id = e.user_id', $rsm);
$events = $query->getArrayResult();
foreach ($events as $key => $event) {
$user = $em->getRepository('Newscoop\\Entity\\User')->findOneBy(array('id' => $event['user']['id']));
$existingEvent = $em->getRepository('Newscoop\\CommunityTickerBundle\\Entity\\CommunityTickerEvent')->findOneBy(array('created' => $event['created'], 'params' => $event['params']));
if (!$existingEvent) {
$newEvent = new CommunityTickerEvent();
$newEvent->setEvent($event['event']);
$newEvent->setParams($event['params'] != '[]' ? json_decode($event['params'], true) : array());
$newEvent->setCreated($event['created']);
$newEvent->setIsActive(true);
if ($user) {
$newEvent->setUser($user);
}
$em->persist($newEvent);
}
}
$em->flush();
$output->writeln('<info>Data migrated to plugin table!</info>');
$output->writeln('<info>Removing old table...</info>');
}
public function getNotasPorPeriodo($periodo, $alumno)
{
$planestuRepository = $this->getDoctrine()->getRepository('AppBundle:Planestu');
$em = $this->getDoctrine()->getManager();
// Configuracion de la entidad a adoptar la consulta
$rsm = new ResultSetMapping();
$rsm->addEntityResult('AppBundle:Materias', 'u');
$rsm->addFieldResult('u', 'cmate', 'cmate');
$rsm->addFieldResult('u', 'nmate', 'nmate');
// plan de estudio exceptuando las materias excluidas
$query = $em->createNativeQuery('SELECT * FROM materias LEFT JOIN planestu
ON materias.cmate = planestu.cmate WHERE planestu.cgrupo=?
AND materias.cmate NOT IN (SELECT mateexclu.cmate FROM mateexclu WHERE calum =?
AND (cperi =? OR cperi =?)) ORDER BY orden', $rsm);
// set parametros al query
$query->setParameter(1, $alumno->getCgrupo()->getCgrupo());
$query->setParameter(2, $alumno->getCalum());
$query->setParameter(3, $periodo);
$query->setParameter(4, 'T');
$materias = $query->getResult();
$notasArray = array();
foreach ($materias as $materia) {
$nota = $this->getNota($materia->getCmate(), $alumno->getCalum(), $periodo);
$notasArray[$materia->getNmate()] = $nota;
}
return $notasArray;
}
/**
* select u.name from CmsUser u where u.id = 1
*
* @dataProvider singleScalarResultSetProvider
*/
public function testHydrateSingleScalar($name, $resultSet)
{
$rsm = new ResultSetMapping();
$rsm->addEntityResult('Doctrine\\Tests\\Models\\CMS\\CmsUser', 'u');
$rsm->addFieldResult('u', 'u__id', 'id');
$rsm->addFieldResult('u', 'u__name', 'name');
$stmt = new HydratorMockStatement($resultSet);
$hydrator = new \Doctrine\ORM\Internal\Hydration\SingleScalarHydrator($this->_em);
if ($name == 'result1') {
$result = $hydrator->hydrateAll($stmt, $rsm);
$this->assertEquals('romanb', $result);
} else {
if ($name == 'result2') {
$result = $hydrator->hydrateAll($stmt, $rsm);
$this->assertEquals(1, $result);
} else {
if ($name == 'result3' || $name == 'result4') {
try {
$result = $hydrator->hydrateAll($stmt, $rsm);
$this->fail();
} catch (\Doctrine\ORM\NonUniqueResultException $e) {
}
}
}
}
}
public function testIssue()
{
$config = $this->_em->getConfiguration();
$config->addEntityNamespace('MyNamespace', 'Doctrine\\Tests\\ORM\\Functional\\Ticket');
$user = new DDC2256User();
$user->name = 'user';
$group = new DDC2256Group();
$group->name = 'group';
$user->group = $group;
$this->_em->persist($user);
$this->_em->persist($group);
$this->_em->flush();
$this->_em->clear();
$sql = 'SELECT u.id, u.name, g.id as group_id, g.name as group_name FROM ddc2256_users u LEFT JOIN ddc2256_groups g ON u.group_id = g.id';
// Test ResultSetMapping.
$rsm = new ResultSetMapping();
$rsm->addEntityResult('MyNamespace:DDC2256User', 'u');
$rsm->addFieldResult('u', 'id', 'id');
$rsm->addFieldResult('u', 'name', 'name');
$rsm->addJoinedEntityResult('MyNamespace:DDC2256Group', 'g', 'u', 'group');
$rsm->addFieldResult('g', 'group_id', 'id');
$rsm->addFieldResult('g', 'group_name', 'name');
$this->_em->createNativeQuery($sql, $rsm)->getResult();
// Test ResultSetMappingBuilder.
$rsm = new ResultSetMappingBuilder($this->_em);
$rsm->addRootEntityFromClassMetadata('MyNamespace:DDC2256User', 'u');
$rsm->addJoinedEntityFromClassMetadata('MyNamespace:DDC2256Group', 'g', 'u', 'group', array('id' => 'group_id', 'name' => 'group_name'));
$this->_em->createNativeQuery($sql, $rsm)->getResult();
}
private function _getCommonRSM()
{
$rsm = new ResultSetMapping();
$rsm->addEntityResult('UserBundle:AdminUserRole', 'a');
$rsm->addFieldResult('a', 'id', 'id');
$rsm->addFieldResult('a', 'name', 'name');
$rsm->addFieldResult('a', 'label', 'label');
$rsm->addFieldResult('a', 'status', 'status');
return $rsm;
}
public function getUnassignedUser()
{
$rsm = new ResultSetMapping();
$rsm->addEntityResult('AppBundle:User', 'u');
$rsm->addFieldResult('u', 'id', 'id');
$rsm->addFieldResult('u', 'username', 'username');
$rsm->addFieldResult('u', 'email', 'email');
$q = $this->getEntityManager()->createNativeQuery('SELECT u.id, u.username, u.email FROM `user` as u WHERE u.id IN (SELECT getUnassginedUser())', $rsm);
return $q->getResult();
}
public function getStatistics()
{
$rsm = new ResultSetMapping();
$rsm->addEntityResult('AppBundle:Stats', 's');
$rsm->addFieldResult('s', 'player', 'player');
$rsm->addFieldResult('s', 'win', 'win');
$rsm->addFieldResult('s', 'lose', 'lose');
$rsm->addFieldResult('s', 'draw', 'draw');
$rsm->addFieldResult('s', 'rocks', 'rocks');
$rsm->addFieldResult('s', 'papers', 'papers');
$rsm->addFieldResult('s', 'scissors', 'scissors');
$rsm->addFieldResult('s', 'lizards', 'lizards');
$rsm->addFieldResult('s', 'spocks', 'spocks');
$query = $this->_em->createNativeQuery('SELECT
(SELECT name FROM player WHERE id = player_id) as player,
SUM(CASE WHEN result = 1 THEN 1 ELSE 0 END ) as win,
SUM(CASE WHEN result = 0 THEN 1 ELSE 0 END ) as lose,
SUM(CASE WHEN result = 2 THEN 1 ELSE 0 END ) as draw,
SUM(CASE WHEN threw = \'Rock\' THEN 1 ELSE 0 END ) as rocks,
SUM(CASE WHEN threw = \'Paper\' THEN 1 ELSE 0 END ) as papers,
SUM(CASE WHEN threw = \'Scissors\' THEN 1 ELSE 0 END ) as scissors,
SUM(CASE WHEN threw = \'Lizard\' THEN 1 ELSE 0 END ) as lizards,
SUM(CASE WHEN threw = \'Spock\' THEN 1 ELSE 0 END ) as spocks
FROM stat
GROUP BY player_id
ORDER BY player_id', $rsm);
return $query->getResult();
}
public function getChiefsByOrchestra($codeOrchestra)
{
$rsm = new ResultSetMapping();
$rsm->addEntityResult('IUT\\CatalogBundle\\Entity\\Musicien', 'c');
$rsm->addFieldResult('c', 'Code_Musicien', 'codeMusicien');
$rsm->addFieldResult('c', 'Nom_Musicien', 'nomMusicien');
$rsm->addFieldResult('c', 'Prénom_Musicien', 'prénomMusicien');
$rsm->addFieldResult('c', 'Année_Naissance', 'annéeNaissance');
$rsm->addFieldResult('c', 'Photo', 'Image');
return $this->getEntityManager()->createNativeQuery('SELECT DISTINCT c.* FROM Musicien c ' . 'inner join Direction ON c.Code_Musicien = Direction.Code_Musicien ' . 'inner join Orchestres on Direction.Code_Orchestre = Orchestres.Code_Orchestre ' . 'where Orchestres.Code_Orchestre = ? ', $rsm)->setParameter(1, $codeOrchestra)->getResult();
}
public function getHolidays()
{
$rsm = new ResultSetMapping();
$rsm->addEntityResult('STX\\CroissantsBundle\\Entity\\Friday_Subscriptions', 'fs');
$rsm->addFieldResult('fs', 'id', 'id');
$rsm->addFieldResult('fs', 'date', 'date');
$rsm->addFieldResult('fs', 'remark', 'remark');
$sql = 'select fs.id, fs.date, fs.remark' . ' from friday_subscriptions fs ' . ' where fs.date >= NOW() ' . ' AND fs.remark like \'FERIE -%\'' . ' ORDER BY fs.date ASC ';
$query = $this->_em->createNativeQuery($sql, $rsm);
$results = $query->getResult();
return $results;
}
/**
* Returns all competences linked to the specific matiere
*
* @param $matiereID
*/
public function findByExamenId($examenID)
{
$sql = "SELECT c.id, c.name " . "FROM c3csi_examen exam " . "LEFT JOIN c3csi_examen_rel_competence exam_comp ON exam_comp.examen_id = exam.id " . "LEFT JOIN c3csi_competence c ON c.id = exam_comp.competence_id " . "WHERE exam.id = ?";
$rsm = new ResultSetMapping();
$rsm->addEntityResult('SkillBundle\\Entity\\Competence', 'c');
$rsm->addFieldResult('c', 'id', 'id');
$rsm->addFieldResult('c', 'name', 'name');
$query = $this->_em->createNativeQuery($sql, $rsm);
$query->setParameter(1, $examenID);
$result = $query->getResult();
return $result;
}
protected function execute(InputInterface $input, OutputInterface $output)
{
/** @var ObjectManager $em */
$em = $this->getContainer()->get('doctrine')->getManager();
$currentUtc = (new \DateTime())->setTimezone(new \DateTimeZone('UTC'));
$calculationDate = clone $currentUtc;
$calculationDate->modify('midnight');
$systemOffset = (new \DateTime())->getOffset();
$rsm = new ResultSetMapping();
$rsm->addEntityResult('AcmeUserBundle:User', 'u');
$rsm->addFieldResult('u', 'id', 'id');
$rsm->addFieldResult('u', 'timezone', 'timezone');
$str = 'select u.id as id,
u.timezone
from ed_users u
WHERE DATE_ADD("' . $currentUtc->format('Y-m-d H:i') . '", INTERVAL u.timezone HOUR) > "' . $calculationDate->format('Y-m-d H:i') . '" AND
NOT EXISTS(SELECT * FROM ed_progress_statistics ps WHERE ps.user_id=u.id AND DATE_ADD(ps.calculated_at, INTERVAL (u.timezone - ' . $systemOffset / 3600 . ') HOUR) > "' . $calculationDate->format('Y-m-d H:i') . '")
';
$query = $em->createNativeQuery($str, $rsm);
$users = $query->getResult();
$dayStart = clone $calculationDate;
$dayStart->sub(new \DateInterval('P1D'));
$dayFinish = clone $calculationDate;
foreach ($users as $user) {
// $user = $em->find('AcmeUserBundle:User', $user->getId());
$progress = new ProgressStatistic();
$progress->setUser($user);
$queryBuilder = $em->createQueryBuilder();
$actions = $queryBuilder->from('AcmeEdelaBundle:UserAction', 'ua')->addSelect('uap.result as progress')->addSelect('IDENTITY(a.goal) as goal_id')->leftJoin('AcmeEdelaBundle:Action', 'a', Join::WITH, 'ua.action=a')->leftJoin('AcmeUserBundle:User', 'u', Join::WITH, 'ua.user=u')->leftJoin('AcmeEdelaBundle:UserActionProgress', 'uap', Join::WITH, 'uap.userAction=ua AND
DATE_ADD2(uap.createdAt, INTERVAL (u.timezone - ' . $systemOffset / 3600 . ') HOUR) > :dayStart AND
DATE_ADD2(uap.createdAt, INTERVAL (u.timezone - ' . $systemOffset / 3600 . ') HOUR) < :dayFinish
')->setParameter('dayFinish', $dayFinish)->setParameter('dayStart', $dayStart)->where('ua.user = :user')->setParameter('user', $user);
$orX = $queryBuilder->expr()->orX();
$orX->add($queryBuilder->expr()->gt('BIT_AND(ua.periodicity, :dayOfWeek)', '0'));
$orX->add($queryBuilder->expr()->eq('MOD(DATE_DIFF(ua.createdAt, :date), ua.periodicityInterval)', '0'));
$actions->andWhere($orX)->setParameter('date', $dayStart)->setParameter('dayOfWeek', (int) (1 << $dayStart->format('w')));
$actions->andWhere('ua.startAt < :dayFinish');
var_dump($actions->getQuery()->getSQL(), $dayFinish, $dayStart);
$actions = $actions->getQuery()->getArrayResult();
var_dump($actions);
$progress->setTotalActions(count($actions));
$progress->setProgressedActions(count(array_filter($actions, function ($item) {
return (bool) $item['progress'];
})));
$goaledActions = count(array_filter($actions, function ($item) {
return (bool) $item['goal_id'];
}));
$progress->setEfficiency($progress->getProgressedActions() * 3 + $goaledActions);
$em->persist($progress);
}
$em->flush();
$output->writeln('Done');
}
/**
* Returns the examens related to the intervenant passed in parameter
*
* @param $intervenantID
*/
public function findByIntervenantId($intervenantID)
{
$sql = "SELECT exam.id, exam.name, exam.description " . "FROM c3csi_user user " . "LEFT JOIN c3csi_examen exam ON exam.intervenant_id = user.id " . "WHERE user.id = ?";
$rsm = new ResultSetMapping();
$rsm->addEntityResult('EvaluationBundle\\Entity\\Examen', 'm');
$rsm->addFieldResult('m', 'id', 'id');
$rsm->addFieldResult('m', 'name', 'name');
$rsm->addFieldResult('m', 'description', 'description');
$query = $this->_em->createNativeQuery($sql, $rsm);
$query->setParameter(1, $intervenantID);
$result = $query->getResult();
return $result;
}
/**
* @group DDC-1470
*
* @expectedException \Doctrine\ORM\Internal\Hydration\HydrationException
* @expectedExceptionMessage The discriminator column "discr" is missing for "Doctrine\Tests\Models\Company\CompanyPerson" using the DQL alias "p".
*/
public function testMissingDiscriminatorColumnException()
{
$rsm = new ResultSetMapping();
$rsm->addEntityResult('Doctrine\\Tests\\Models\\Company\\CompanyPerson', 'p');
$rsm->addFieldResult('p', 'p__id', 'id');
$rsm->addFieldResult('p', 'p__name', 'name');
$rsm->addMetaResult('p ', 'discr', 'discr');
$rsm->setDiscriminatorColumn('p', 'discr');
$resultSet = array(array('u__id' => '1', 'u__name' => 'Fabio B. Silva'));
$stmt = new HydratorMockStatement($resultSet);
$hydrator = new \Doctrine\ORM\Internal\Hydration\SimpleObjectHydrator($this->_em);
$hydrator->hydrateAll($stmt, $rsm);
}
public function getAlbumsByOrchestra($codeOrchestra)
{
$rsm = new ResultSetMapping();
$rsm->addEntityResult('IUT\\CatalogBundle\\Entity\\Album', 'a');
$rsm->addFieldResult('a', 'Code_Album', 'codeAlbum');
$rsm->addFieldResult('a', 'Titre_Album', 'titreAlbum');
$rsm->addFieldResult('a', 'Année_Album', 'anneeAlbum');
//$rsm->addFieldResult('a', 'Code_Genre', 'codeGenre');
// $rsm->addFieldResult('a', 'Code_Editeur', 'codeEditeur');
$rsm->addFieldResult('a', 'Pochette', 'pochette');
$rsm->addFieldResult('a', 'ASIN', 'ASIN');
// build rsm here
return $this->getEntityManager()->createNativeQuery('SELECT DISTINCT a.* FROM Album a ' . 'inner join Disque ON a.Code_Album = Disque.Code_Album ' . 'inner join Composition_Disque on Disque.Code_Disque = Composition_Disque.Code_Disque ' . 'inner join Enregistrement on Composition_Disque.Code_Morceau = Enregistrement.Code_Morceau ' . 'inner join Direction on Enregistrement.Code_Morceau = Direction.Code_Morceau ' . 'inner join Orchestres on Direction.Code_Orchestre = Orchestres.Code_Orchestre ' . 'where Orchestres.Code_Orchestre = ? ', $rsm)->setParameter(1, $codeOrchestra)->getResult();
}
/**
* @group DDC-644
*/
public function testSkipUnknownColumns()
{
$rsm = new ResultSetMapping();
$rsm->addEntityResult('Doctrine\\Tests\\Models\\CMS\\CmsUser', 'u');
$rsm->addFieldResult('u', 'u__id', 'id');
$rsm->addFieldResult('u', 'u__name', 'name');
$rsm->addScalarResult('foo1', 'foo');
$rsm->addScalarResult('bar2', 'bar');
$rsm->addScalarResult('baz3', 'baz');
$resultSet = array(array('u__id' => '1', 'u__name' => 'romanb', 'foo1' => 'A', 'bar2' => 'B', 'baz3' => 'C', 'foo' => 'bar'));
$stmt = new HydratorMockStatement($resultSet);
$hydrator = new \Doctrine\ORM\Internal\Hydration\ScalarHydrator($this->_em);
$result = $hydrator->hydrateAll($stmt, $rsm);
}
/**
* @param $id
*/
public function findOneById($id, $fullObject = true)
{
$sql = "SELECT *" . "FROM c3csi_matiere m WHERE id = ?";
$rsm = new ResultSetMapping();
$rsm->addEntityResult('SchoolBundle\\Entity\\Matiere', 'm');
$rsm->addFieldResult('m', 'id', 'id');
$rsm->addFieldResult('m', 'name', 'name');
$query = $this->_em->createNativeQuery($sql, $rsm);
$query->setParameter(1, $id);
$result = $query->getResult();
if (isset($result[0])) {
return $result[0];
}
return $result;
}
public function testExtraFieldInResultSetShouldBeIgnore()
{
$rsm = new ResultSetMapping();
$rsm->addEntityResult('Doctrine\\Tests\\Models\\CMS\\CmsAddress', 'a');
$rsm->addFieldResult('a', 'a__id', 'id');
$rsm->addFieldResult('a', 'a__city', 'city');
$resultSet = array(array('a__id' => '1', 'a__city' => 'Cracow', 'doctrine_rownum' => '1'));
$expectedEntity = new \Doctrine\Tests\Models\CMS\CmsAddress();
$expectedEntity->id = 1;
$expectedEntity->city = 'Cracow';
$stmt = new HydratorMockStatement($resultSet);
$hydrator = new \Doctrine\ORM\Internal\Hydration\SimpleObjectHydrator($this->_em);
$result = $hydrator->hydrateAll($stmt, $rsm);
$this->assertEquals($result[0], $expectedEntity);
}
/**
* Returns a specific examen
*
* @param $id
*/
public function findOneById($id, $fullObject = true)
{
$sql = "SELECT * " . "FROM c3csi_type_note tn WHERE id = ?";
$rsm = new ResultSetMapping();
$rsm->addEntityResult('EvaluationBundle\\Entity\\TypeNote', 'tn');
$rsm->addFieldResult('tn', 'id', 'id');
$rsm->addFieldResult('tn', 'label', 'label');
$rsm->addFieldResult('tn', 'value', 'value');
$query = $this->_em->createNativeQuery($sql, $rsm);
$query->setParameter(1, $id);
$result = $query->getResult();
if (isset($result[0])) {
return $result[0];
}
return $result;
}
/**
* Get all ancillary services by institution medical center
*
* @param Mixed <InstitutionMedicalCenter, int> $institutionMedicalCenter
* @return array OfferedService
*/
public function getAllServicesByInstitutionMedicalCenter($institutionMedicalCenter, $hydrationMode = Query::HYDRATE_OBJECT)
{
if ($institutionMedicalCenter instanceof InstitutionMedicalCenter) {
$institutionMedicalCenterId = $institutionMedicalCenter->getId();
} else {
$institutionMedicalCenterId = $institutionMedicalCenter;
}
$rsm = new ResultSetMapping();
$rsm->addEntityResult('AdminBundle:OfferedService', 'b');
$rsm->addFieldResult('b', 'id', 'id');
$rsm->addFieldResult('b', 'name', 'name');
$rsm->addFieldResult('b', 'status', 'status');
$rsm->addFieldResult('b', 'date_created', 'dateCreated');
$sql = "SELECT b.* FROM institution_medical_center_properties a JOIN offered_services b ON b.id = a.value \n WHERE a.institution_medical_center_id = :imcId\n AND a.institution_property_type_id = :propertyType\n AND b.status = 1\n ORDER BY b.name ASC";
$query = $this->getEntityManager()->createNativeQuery($sql, $rsm)->setParameter('imcId', $institutionMedicalCenterId)->setParameter('propertyType', InstitutionPropertyType::ANCILLIARY_SERVICE_ID);
return $query->getResult($hydrationMode);
}
/**
* Select u.id, u.name from CmsUser u
*/
public function testNewHydrationSimpleEntityQuery()
{
$rsm = new ResultSetMapping();
$rsm->addEntityResult('Kitpages\\DataGridBundle\\Tests\\TestEntities\\Node', 'node');
$rsm->addEntityResult('Kitpages\\DataGridBundle\\Tests\\TestEntities\\NodeAssoc', 'assoc');
$rsm->addFieldResult('node', 'id1', 'id');
$rsm->addFieldResult('node', 'user2', 'user');
$rsm->addFieldResult('node', 'content3', 'content');
$rsm->addFieldResult('node', 'parent_id4', 'parentId');
$rsm->addFieldResult('node', 'created_at5', 'createdAt');
$rsm->addFieldResult('assoc', 'id6', 'id');
$rsm->addFieldResult('assoc', 'name7', 'name');
$rsm->addScalarResult('intervals8', 'intervals');
// Faked result set
$resultSet = array(array('id1' => 11, 'user2' => 'toto', 'content3' => 'I like it!', 'parent_id4' => 0, 'created_at5' => new \DateTime('2010-04-21 12:14:20'), 'id6' => 1, 'name7' => 'tutu', 'intervals8' => 10));
$stmt = new HydratorMockStatement($resultSet);
$hydrator = new DataGridHydrator($this->getEntityManager());
$result = $hydrator->hydrateAll($stmt, $rsm);
$this->assertTrue(is_array($result));
$this->assertEquals(1, count($result));
$this->assertEquals(11, $result[0]['node.id']);
$this->assertEquals('toto', $result[0]['node.user']);
$this->assertEquals('I like it!', $result[0]['node.content']);
$this->assertEquals(0, $result[0]['node.parentId']);
$this->assertEquals(new \DateTime('2010-04-21 12:14:20'), $result[0]['node.createdAt']);
$this->assertEquals(1, $result[0]['assoc.id']);
$this->assertEquals('tutu', $result[0]['assoc.name']);
$this->assertEquals(10, $result[0]['intervals']);
}
public function getEvolutionCounts($period = 50)
{
$rsm = new ResultSetMapping();
$rsm->addEntityResult('Knp\\Bundle\\KnpBundlesBundle\\Entity\\Score', 'e');
$rsm->addFieldResult('e', 'id', 'id');
$rsm->addFieldResult('e', 'date', 'date');
$rsm->addFieldResult('e', 'value', 'value');
$sql = <<<EOF
SELECT id, COUNT(id) as `value`, DATE(createdAt) as `date`
FROM bundle
WHERE createdAt > :period
GROUP BY `date`
ORDER BY `date` ASC
EOF;
$periodDate = new \DateTime(sprintf('%d days ago', $period));
$periodDate = $periodDate->format('Y-m-d H:i:s');
return $this->getEntityManager()->createNativeQuery($sql, $rsm)->setParameter('period', $periodDate)->getResult();
}
/**
* Select u.id, u.name from CmsUser u
*/
public function testNewHydrationSimpleEntityQuery()
{
$rsm = new ResultSetMapping();
$rsm->addEntityResult('Doctrine\\Tests\\Models\\CMS\\CmsUser', 'u');
$rsm->addFieldResult('u', 'u__id', 'id');
$rsm->addFieldResult('u', 'u__name', 'name');
// Faked result set
$resultSet = array(array('u__id' => '1', 'u__name' => 'romanb'), array('u__id' => '2', 'u__name' => 'jwage'));
$stmt = new HydratorMockStatement($resultSet);
$hydrator = new \Doctrine\ORM\Internal\Hydration\ScalarHydrator($this->_em);
$result = $hydrator->hydrateAll($stmt, $rsm);
$this->assertTrue(is_array($result));
$this->assertEquals(2, count($result));
$this->assertEquals('romanb', $result[0]['u_name']);
$this->assertEquals(1, $result[0]['u_id']);
$this->assertEquals('jwage', $result[1]['u_name']);
$this->assertEquals(2, $result[1]['u_id']);
}
public function testBasicNativeQuery()
{
$user = new CmsUser();
$user->name = 'Roman';
$user->username = '******';
$user->status = 'dev';
$this->_em->save($user);
$this->_em->flush();
$rsm = new ResultSetMapping();
$rsm->addEntityResult('Doctrine\\Tests\\Models\\CMS\\CmsUser', 'u');
$rsm->addFieldResult('u', 'id', 'id');
$rsm->addFieldResult('u', 'name', 'name');
$query = $this->_em->createNativeQuery('SELECT id, name FROM cms_users WHERE username = ?', $rsm);
$query->setParameter(1, 'romanb');
$users = $query->getResultList();
$this->assertEquals(1, count($users));
$this->assertTrue($users[0] instanceof CmsUser);
$this->assertEquals('Roman', $users[0]->name);
}
/**
* @param Event $event
* @param Constraint $constraint
*/
public function validate($event, Constraint $constraint)
{
$eventEndDate = $event->getDate()->format('Y-m-d H:i:s');
$eventEndDate = new \DateTime($eventEndDate);
$eventEndDate->modify("+" . $event->getDuration() . " minutes")->format('Y-m-d H:i:s');
// Map result set to my Entity.
$rsm = new ResultSetMapping();
$rsm->addEntityResult('AppBundle\\Entity\\Event', 'e');
$rsm->addFieldResult('e', 'termin', 'date');
$rsm->addFieldResult('e', 'dauer', 'duration');
// Using MySQL for better DateTime handling
$query = $this->em->createNativeQuery("SELECT * FROM veranstaltung e\n WHERE e.termin < :endDate\n AND e.termin + INTERVAL e.dauer MINUTE > :startDate", $rsm);
$query->setParameters(array("endDate" => $eventEndDate, 'startDate' => $event->getDate()->format('Y-m-d H:i:s')));
$overlappingEntities = $query->getResult();
/** @var $constraint HasNoDateOverlap */
if ($overlappingEntities) {
$this->context->addViolation($constraint->message);
}
}
/**
* @return Event[]
*/
public function findByCurrentEvents()
{
$rsm = new ResultSetMapping();
$rsm->addEntityResult('BauerIncidentDashboardCoreBundle:Event', 'event');
$rsm->addFieldResult('event', 'id', 'id');
$rsm->addFieldResult('event', 'message', 'message');
$rsm->addFieldResult('event', 'identifier', 'identifier');
$rsm->addFieldResult('event', 'system', 'system');
$rsm->addFieldResult('event', 'status', 'status');
$rsm->addFieldResult('event', 'created', 'created');
$rsm->addFieldResult('event', 'url', 'url');
$rsm->addFieldResult('event', 'isUnique', 'unique');
$rsm->addFieldResult('event', 'type', 'type');
$query = $this->getEntityManager()->createNativeQuery('SELECT sub.* FROM (SELECT * FROM `event` ORDER BY `id` DESC) as sub GROUP BY `status`, `identifier` ORDER BY `created` DESC', $rsm);
return $query->getResult();
}
public function hasPasswordExpired($userid, $em)
{
$settings = $em->getRepository("\\Application\\Entity\\Settings")->find(1);
$rsm = new ResultSetMapping();
$rsm->addEntityResult('Application\\Entity\\User', 'u');
$rsm->addFieldResult('u', 'PASSWORDLASTCHANGED', 'passwordlastchanged');
$query = $em->createNativeQuery("SELECT PASSWORDLASTCHANGED FROM user " . " where DATEDIFF(CURDATE(),PASSWORDLASTCHANGED) > :num1 AND PK_USERID = :num2 ", $rsm);
$query->setParameter('num1', $settings->getPasswordExpireydays());
$query->setParameter('num2', $userid);
return count($query->getResult()) > 0 ? true : false;
}
public function getNotasPorPeriodo($periodo, $alumno)
{
$planestuRepository = $this->getDoctrine()->getRepository('AppBundle:Planestu');
$em = $this->getDoctrine()->getManager();
// Configuracion de la entidad a adoptar la consulta
$rsm = new ResultSetMapping();
$rsm->addEntityResult('AppBundle:Materias', 'u');
$rsm->addFieldResult('u', 'cmate', 'cmate');
$rsm->addFieldResult('u', 'nmate', 'nmate');
//$query = $em->createNativeQuery('SELECT cmate, cgrupo FROM planestu WHERE cgrupo=? ', $rsm);
$query = $em->createNativeQuery('SELECT * FROM materias LEFT JOIN planestu ON materias.cmate = planestu.cmate WHERE planestu.cgrupo = ? ORDER BY orden', $rsm);
$query->setParameter(1, $alumno->getCgrupo()->getCgrupo());
$materias = $query->getResult();
$notasArray = array();
foreach ($materias as $materia) {
$nota = $this->getNota($materia->getCmate(), $alumno->getCalum(), $periodo);
$notasArray[$materia->getNmate()] = $nota;
}
return $notasArray;
}
public function getEvolutionCounts($period = 50)
{
$rsm = new ResultSetMapping();
$rsm->addEntityResult('Knp\\Bundle\\KnpBundlesBundle\\Entity\\Score', 'e');
$rsm->addFieldResult('e', 'id', 'id');
$rsm->addFieldResult('e', 'date', 'date');
$rsm->addFieldResult('e', 'value', 'value');
$sql = <<<EOF
SELECT id, COUNT(id) as `value`, DATE(createdAt) as `date`
FROM owner
WHERE createdAt > :period
AND discriminator = :entityType
GROUP BY `date`
ORDER BY `date` ASC
EOF;
$periodDate = new \DateTime(sprintf('%d days ago', $period));
$periodDate = $periodDate->format('Y-m-d H:i:s');
$entityType = strstr($this->getEntityName(), 'Developer') ? 'developer' : 'organization';
return $this->getEntityManager()->createNativeQuery($sql, $rsm)->setParameter('period', $periodDate)->setParameter('entityType', $entityType)->getResult();
}
public function testHydrateColumn()
{
$rsm = new ResultSetMapping();
$rsm->addEntityResult('Doctrine\\Tests\\Models\\CMS\\CmsUser', 'u');
$rsm->addFieldResult('u', 'u__name', 'name');
// Faked result set
$resultSet = array(array('u__name' => 'romanb'), array('u__name' => 'jwage'));
$stmt = new HydratorMockStatement($resultSet);
$hydrator = new ColumnHydrator($this->_em);
$result = $hydrator->hydrateAll($stmt, $rsm);
$this->assertEquals(array('romanb', 'jwage'), $result);
}
/**
* @param Entity\Contact $contact
* @param Entity\Facebook $facebook
* @return bool
*/
public function isContactInFacebook(Entity\Contact $contact, Entity\Facebook $facebook)
{
$resultSetMap = new ResultSetMapping();
$resultSetMap->addEntityResult('Contact\\Entity\\Contact', 'c');
/**
* Don't map the contact_id because that will overwrite the existing contact object leaving an emtpy one
*/
$resultSetMap->addFieldResult('c', 'email', 'email');
$queryInString = sprintf("SELECT %s FROM %s WHERE %s", $facebook->getContactKey(), $facebook->getFromClause(), $facebook->getWhereClause());
$query = $this->getEntityManager()->createNativeQuery(sprintf("SELECT email FROM contact WHERE contact_id = %s AND contact_id IN (%s) AND date_end IS NULL", $contact->getId(), $queryInString), $resultSetMap);
return sizeof($query->getResult()) > 0;
}
