public function getTransactionsByMember(Corporation $corp, array $member_ids, Carbon $date)
 {
     $start = $date->copy();
     $start->subMonth()->setTime(0, 0, 0);
     $end = $date->copy();
     $end->setTime(23, 59, 59);
     $sql = 'SELECT jt.owner_id2, group_concat(DISTINCT jt.id) as ids
         FROM journal_transactions as jt
         LEFT JOIN accounts as acc on jt.account_id=acc.id
         WHERE  acc.corporation_id = :corp_id
         AND jt.owner_id2 in ( :owner_ids )
         AND jt.date >= :start_date
         AND jt.date <= :end_date
         GROUP BY jt.owner_id2';
     $rsm = new ResultSetMappingBuilder($this->getEntityManager());
     $rsm->addRootEntityFromClassMetadata('AppBundle\\Entity\\JournalTransaction', 'jt');
     $rsm->addFieldResult('jt', 'owner_id2', 'owner_id2');
     $rsm->addFieldResult('jt', 'ids', 'id');
     $q = $this->getEntityManager()->createNativeQuery($sql, $rsm);
     $q->setParameter('corp_id', $corp->getId());
     $q->setParameter('owner_ids', $member_ids, Connection::PARAM_INT_ARRAY);
     $q->setParameter('start_date', $start);
     $q->setParameter('end_date', $end);
     $results = $q->getResult();
     $real_res = [];
     foreach ($results as $res) {
         $ids = explode(',', $res->getId());
         $rt = $this->createQueryBuilder('jt')->select('sum(jt.amount) as total_amount')->where('jt.id in (:j_ids)')->setParameter('j_ids', $ids)->getQuery()->getResult();
         $r = $this->createQueryBuilder('jt')->select('jt')->where('jt.id in (:j_ids)')->setParameter('j_ids', $ids)->getQuery()->getResult();
         $real_res[] = ['user' => $res->getOwnerId2(), 'total' => $rt, 'orig_ids' => $r];
     }
     return $real_res;
 }

 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();
 }

 /**
  * Map N unused coupon codes to the given customer
  *
  * We need very strict isolation in order to avoid mapping
  * an already mapped code to another user in case of lots
  * of calls to mapNToCustomer happening in parallel.
  *
  * Therefore, we wrap everything into a transaction
  * and read with SELECT ... FOR UPDATE.
  *
  * This ensures a) that no codes are mapped at all if not all
  * mappings where successful, and b) locks the row of a free
  * code that is currently read.
  *
  * @see https://dev.mysql.com/doc/refman/5.5/en/innodb-locking-reads.html
  *
  * @param $count
  * @param \AppBundle\Entity\Customer $customer
  * @return bool
  */
 public function mapNToCustomer($count, Customer $customer)
 {
     $this->em->getConnection()->setTransactionIsolation(Connection::TRANSACTION_SERIALIZABLE);
     $this->em->getConnection()->beginTransaction();
     try {
         for ($i = 0; $i < $count; $i++) {
             $rsm = new ResultSetMappingBuilder($this->em);
             $rsm->addRootEntityFromClassMetadata('\\AppBundle\\Entity\\Couponcode', 'c');
             $query = $this->em->createNativeQuery('SELECT * FROM couponcode WHERE customer_id IS NULL LIMIT 1 FOR UPDATE', $rsm);
             $couponcode = $query->getOneOrNullResult();
             if (is_null($couponcode)) {
                 $this->em->getConnection()->rollback();
                 return false;
             }
             $couponcode->setCustomer($customer);
             //
             if (strlen($couponcode->getCode()) === 64) {
                 $normalizedEmployeeNumber = preg_replace("/[^0-9]+/", "", $customer->getEmployeeNumber());
                 $code = $couponcode->getCode();
                 $newCode = substr($code, 0, 45);
                 $newCode .= str_pad(substr($normalizedEmployeeNumber, 0, 16), 16, '0', \STR_PAD_LEFT);
                 $newCode .= '0' . $customer->getSalesdivision();
                 $newCode .= substr($code, -1, 1);
                 $couponcode->setCode($newCode);
             }
             $this->em->persist($couponcode);
             $this->em->flush();
         }
         $this->em->getConnection()->commit();
         return true;
     } catch (Exception $e) {
         $this->em->getConnection()->rollback();
         return false;
     }
 }

 /**
  * return All Product Shop
  *
  * @param string $name
  * @param int $count
  *
  * @return array
  */
 public function findByProductShop($name, $count)
 {
     $rsm = new ResultSetMappingBuilder($this->getEntityManager());
     $rsm->addEntityResult("ShopProductBundle:Product", "p");
     $rsm->addFieldResult("p", "id", "id");
     $rsm->addFieldResult("p", "price", "price");
     $rsm->addJoinedEntityResult("UserUserBundle:Users", "u", "p", "likeProduct");
     $rsm->addFieldResult("u", "likes", "id");
     $rsm->addJoinedEntityResult("ShopProductBundle:ProductImage", "pi", "p", "image");
     $rsm->addFieldResult("pi", "image_id", "id");
     $rsm->addFieldResult("pi", "path", "path");
     $query = $this->getEntityManager()->createNativeQuery('
             SELECT DISTINCT ON (p.id) p.id, p.price, count(u.id) as likes, pi.id as image_id, pi.path
             FROM product p
               LEFT JOIN product_image pi ON pi.product_id = p.id
               LEFT JOIN product_like pl ON pl.product_id = p.id
               LEFT JOIN users u ON pl.users_id = u.id
               LEFT JOIN shops s ON s.id = p.shops_id
             WHERE s.unique_name = ?
             GROUP BY p.id, p.price, pi.id, pi.path
             LIMIT 16 OFFSET ?
         ', $rsm)->setParameters([1 => $name, 2 => $count]);
     try {
         return $query->getArrayResult();
     } catch (\Doctrine\ORM\NoResultException $e) {
         return null;
     }
 }

    public function findByLog($field, $value)
    {
        $search = 's:' . strlen($field) . ':"' . $field . '";s:' . strlen($value) . ':"' . $value . '";';
        $search = '"' . $field . '":' . str_replace('\\', '\\\\', json_encode($value));
        $sql = 'SELECT * FROM content_log cl
					WHERE cl.data LIKE \'%' . $search . '%\' ESCAPE \'|\'
					GROUP BY cl.object_id
					ORDER BY cl.id DESC
			';
        $rsm = new ResultSetMappingBuilder($this->getEntityManager());
        $rsm->addRootEntityFromClassMetadata(str_replace('\\Entity\\Content', '\\Entity\\Log\\Content', $this->getClassName()) . 'Log', 'cl');
        $q = $this->getEntityManager()->createNativeQuery($sql, $rsm);
        $logValues = $q->getResult();
        $ret = array();
        foreach ($logValues as $logValue) {
            $object = $this->find($logValue->getObjectId());
            if ($object) {
                if ($logValue->getLocale()) {
                    $object->setTranslatableLocale($logValue->getLocale());
                    $this->getEntityManager()->refresh($object);
                }
                $ret[] = $object;
            }
        }
        return $ret;
    }

 public function searchAll()
 {
     $rsm = new ResultSetMappingBuilder($this->getEntityManager());
     $rsm->addRootEntityFromClassMetadata('AMiE\\EntreprisesBundle\\Entity\\Partenaire', 'p');
     $query = 'SELECT p.* FROM Partenaire p WHERE 1 ';
     $request = $this->getEntityManager()->createNativeQuery($query, $rsm);
     return $request;
 }

 /**
  * @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 searchSecteur($secteur)
 {
     $rsm = new ResultSetMappingBuilder($this->getEntityManager());
     $rsm->addRootEntityFromClassMetadata('AMiE\\UserBundle\\Entity\\User', 'u');
     $query = 'SELECT count(u.secteur) FROM AMiE\\UserBundle\\Entity\\User u WHERE u.secteur = :secteur AND u.typeUt = \'Entreprise\'';
     $request = $this->getEntityManager()->createQuery($query);
     $request->setParameters(array('secteur' => $secteur));
     return $request;
 }

 /**
  * Get a random proxy (only enabled ones)
  *
  * @return ProxyEntity
  */
 public function findRandom()
 {
     $rsmb = new ResultSetMappingBuilder($this->_em);
     $rsmb->addRootEntityFromClassMetadata('steevanb\\ProxyBundle\\Entity\\Proxy', 'p');
     $rsmb->addIndexBy('p', 'id');
     $query = $this->_em->createNativeQuery('SELECT * FROM proxy_proxies p WHERE state = :state ORDER BY RAND() LIMIT 1', $rsmb);
     $query->setParameter('state', ProxyEntity::STATE_ENABLED);
     return $query->getSingleResult();
 }

 /**
  * Wyszukuje wydarzenia w zadanej odległości od podanych współrzędnych
  * 
  * @param float $lat
  * @param float $lng
  * @param int $distance
  * @return ArrayObject $events
  */
 public function findEventsInRadius($lat, $lng, $distance = 2)
 {
     $query = "\n            SELECT\n                subSel2.*\n            FROM (\n                SELECT\n                    sin(subSel.dlat / 2) * \n                    sin(subSel.dlat / 2) + \n                    cos(subSel.lat1) * \n                    cos(subSel.lat2) * \n                    sin(subSel.dlng / 2) * \n                    sin(subSel.dlng / 2) sel,\n                    subSel.*\n                FROM (\n                    SELECT \n                        (radians(:lat)-radians(lat)) dlat, \n                        (radians(:lng)-radians(lng)) dlng, \n                        radians(lat) lat1, \n                        radians(lng) lng1,\n                        radians(:lat) lat2,\n                        radians(:lng) lng2,\n                        Event.*\n                    From \n                        Event \n                ) subSel \n            ) subSel2\n            WHERE\n                (6372.797 * \n                (2 * atan2(sqrt(subSel2.sel), sqrt(1 - subSel2.sel)))) <= :distance\n            ";
     $rsm = new ResultSetMappingBuilder($this->entityManger);
     $rsm->addRootEntityFromClassMetadata('Events\\Entity\\Event', 'event');
     $nativeQuery = $this->entityManger->createNativeQuery($query, $rsm);
     $nativeQuery->setParameters(array('lat' => $lat, 'lng' => $lng, 'distance' => $distance));
     return $nativeQuery->getResult();
 }

 /**
  * @param Project $project
  *
  * @return Doctrine\Common\Collections\Collection
  */
 public function findLastByRefs(Project $project)
 {
     $query = 'SELECT b.* FROM (SELECT * FROM build WHERE build.project_id = ? ORDER BY created_at DESC) b GROUP BY b.ref';
     $rsm = new ResultSetMappingBuilder($this->getEntityManager());
     $rsm->addRootEntityFromClassMetadata('Model:Build', 'b');
     $query = $this->getEntityManager()->createNativeQuery($query, $rsm);
     $query->setParameter(1, $project->getId());
     return $query->execute();
 }

 /**
  * @param Player $player
  *
  * @return PointLog[]
  */
 public function getForPlayer(Player $player)
 {
     $sql = "\n            SELECT\n                *\n            FROM\n                pointlog pl\n            LEFT JOIN\n                match m ON m.id = pl.match_id\n            WHERE\n                m.player1_id = :id\n                OR\n                m.player2_id = :id\n                OR\n                m.team1attack = :id\n                OR\n                m.team1defence = :id\n                OR\n                m.team2attack = :id\n                OR\n                m.team2defence = :id\n            ORDER BY\n                m.date DESC";
     $rsm = new ResultSetMappingBuilder($this->_em);
     $rsm->addRootEntityFromClassMetadata('Application\\Model\\Entity\\PointLog', 'pl');
     $query = $this->_em->createNativeQuery($sql, $rsm);
     $query->setParameter('id', $player->getId());
     $logs = $query->getResult();
     return $logs;
 }

 /**
  * usage: http://doctrine-orm.readthedocs.org/projects/doctrine-orm/en/latest/reference/native-sql.html
  */
 public function getQuizList($status, $amount)
 {
     $rsm = new ResultSetMappingBuilder($this->_em);
     $rsm->addRootEntityFromClassMetadata('Eng:PhrasesEntity', 'w');
     // Limit parameter can not be parameterized
     $query = $this->_em->createNativeQuery("SELECT *, (success - failure) seq FROM phrases w WHERE w.status = :status ORDER BY seq ASC, failure DESC, create_time ASC LIMIT " . intval($amount), $rsm);
     $query->setParameter('status', $status, \PDO::PARAM_INT);
     $words = $query->getArrayResult();
     return $words;
 }

 /**
  * @param Event $event
  *
  * @return Unregistration[]
  */
 public function getUnprocessedUnregistrations(Event $event)
 {
     $rsm = new ResultSetMappingBuilder($this->_em);
     $rsm->addRootEntityFromClassMetadata('BCRM\\BackendBundle\\Entity\\Event\\Unregistration', 'u');
     $eventId = $event->getId();
     $unregistrations = "SELECT MAX(id) FROM unregistration WHERE confirmed = 1 AND event_id = {$eventId} AND processed = 0 GROUP BY event_id, email ORDER BY created ASC, id ASC";
     $sql = "SELECT * FROM unregistration WHERE id IN ({$unregistrations})";
     $query = $this->_em->createNativeQuery($sql, $rsm);
     return $query->getResult();
 }

 public function buildAliasedSelectClause(ResultSetMappingBuilder $rsm)
 {
     $clauses = [];
     foreach ($rsm->getAliasMap() as $tableAlias => $class) {
         foreach ($this->getColumnAliases($class, $tableAlias) as $columnName => $columnAlias) {
             $clauses[] = $this->quoter->quoteIdentifier($tableAlias) . '.' . $this->quoter->quoteIdentifier($columnName) . ' as ' . $this->quoter->quoteIdentifier($columnAlias);
         }
     }
     return join(', ', $clauses);
 }

 public function findPaths($limit = 0)
 {
     $query = 'SELECT e.* FROM ( ' . 'SELECT * FROM `mrk_gitpushwatcherbundle_push` ORDER BY datetime DESC ' . ') AS e GROUP BY e.path ORDER BY e.datetime DESC ';
     if ($limit > 0) {
         $query .= 'LIMIT ' . $limit;
     }
     $em = $this->getEntityManager();
     $rsm = new ResultSetMappingBuilder($em);
     $rsm->addRootEntityFromClassMetadata('Mrk\\GitPushWatcherBundle\\Entity\\Push', 'e');
     return $em->createNativeQuery($query, $rsm)->getResult();
 }

    public function findRelatedItems(Taggable $item, $class, $locale, $nbOfItems = 1)
    {
        $instance = new $class();
        if (!$instance instanceof Taggable) {
            return NULL;
        }
        $em = $this->em;
        $rsm = new ResultSetMappingBuilder($em);
        $rsm->addRootEntityFromClassMetadata($class, 'i');
        $meta = $em->getClassMetadata($class);
        $tableName = $meta->getTableName();
        $escapedClass = str_replace('\\', '\\\\', $class);
        $query = <<<EOD
            SELECT i.*, COUNT(i.id) as number
            FROM {$tableName} i
            LEFT JOIN kuma_taggings t
            ON t.resource_id = i.id
            AND t.resource_type = '{$instance->getTaggableType()}'
            WHERE t.tag_id IN (
                SELECT tg.tag_id
                FROM kuma_taggings tg
                WHERE tg.resource_id = {$item->getId()}
                AND tg.resource_type = '{$item->getTaggableType()}'
            )
            AND i.id <> {$item->getId()}
EOD;
        if ($item instanceof AbstractPage) {
            $query .= <<<EOD
                AND i.id IN (
                    SELECT nodeversion.refId
                    FROM kuma_nodes as node
                    INNER JOIN kuma_node_translations as nodetranslation
                    ON node.id = nodetranslation.node
                    AND nodetranslation.lang = '{$locale}'
                    INNER JOIN kuma_node_versions as nodeversion
                    ON nodetranslation.publicNodeVersion = nodeversion.id
                    AND nodeversion.refEntityname = '{$escapedClass}'
                    AND node.deleted = 0
                    AND nodetranslation.online = 1
                )
EOD;
        }
        $query .= <<<EOD
            GROUP BY
                i.id
            HAVING
                number > 0
            ORDER BY
                number DESC
            LIMIT {$nbOfItems};
EOD;
        $items = $em->createNativeQuery($query, $rsm)->getResult();
        return $items;
    }

 public function searchTypeContrat($type)
 {
     $rsm = new ResultSetMappingBuilder($this->getEntityManager());
     $rsm->addRootEntityFromClassMetadata('AMiE\\MiagistesBundle\\Entity\\Formulaire', 'f');
     //   $query = 'SELECT count(*) FROM Formulaire f WHERE typeContrat = :type';
     //   $request = $this->getEntityManager()->createNativeQuery($query, $rsm);
     $query = 'SELECT count(f.typeContrat) FROM AMiE\\MiagistesBundle\\Entity\\Formulaire f WHERE f.typeContrat = :type';
     $request = $this->getEntityManager()->createQuery($query);
     $request->setParameters(array('type' => $type));
     return $request;
 }

 /**
  * Find all members with a birthday in the next $days days.
  *
  * When $days equals 0 or isn't given, it will give all birthdays of today.
  *
  * @param int $days The number of days to look ahead.
  *
  * @return array Of members sorted by birthday
  */
 public function findBirthdayMembers($days)
 {
     // unfortunately, there is no support for functions like DAY() and MONTH()
     // in doctrine2, thus we have to use the NativeSQL here
     $builder = new ResultSetMappingBuilder($this->em);
     $builder->addRootEntityFromClassMetadata('Decision\\Model\\Member', 'm');
     $select = $builder->generateSelectClause(array('m' => 't1'));
     $sql = "SELECT {$select} FROM Member AS t1" . " WHERE DATEDIFF(DATE_SUB(t1.birth, INTERVAL YEAR(t1.birth) YEAR)," . " DATE_SUB(CURDATE(), INTERVAL YEAR(CURDATE()) YEAR)) BETWEEN 0 AND :days" . " AND t1.expiration >= CURDATE()" . "ORDER BY DATE_SUB(t1.birth, INTERVAL YEAR(t1.birth) YEAR) ASC";
     $query = $this->em->createNativeQuery($sql, $builder);
     $query->setParameter('days', $days);
     return $query->getResult();
 }

 /**
  * {@inheritDocs}
  */
 public function getNextVipRegistrations(Event $event, $day)
 {
     $rsm = new ResultSetMappingBuilder($this->_em);
     $rsm->addRootEntityFromClassMetadata('BCRM\\BackendBundle\\Entity\\Event\\Registration', 'r');
     $types = join(',', array(Registration::TYPE_SPONSOR, Registration::TYPE_VIP));
     $eventId = $event->getId();
     $dayName = $day == Ticket::DAY_SATURDAY ? 'saturday' : 'sunday';
     $registrations = "SELECT MAX(id) FROM registration WHERE confirmed = 1 AND type IN ({$types}) AND event_id = {$eventId} GROUP BY event_id, email ORDER BY created ASC, id ASC";
     $sql = "SELECT * FROM registration WHERE id IN ({$registrations}) AND email NOT IN (SELECT email FROM ticket WHERE day = {$day} AND event_id = {$eventId}) AND {$dayName} = 1";
     $query = $this->_em->createNativeQuery($sql, $rsm);
     return $query->getResult();
 }