Ejemplo n.º 1
0
 public function searchAction()
 {
     $logger = $this->getServiceLocator()->get('Logger');
     $form = new Form\SearchOrder();
     $result = array();
     /*
      * - use "" (quotes) to search exakt strings with spaces
      * - use - (minus) to exclude a string
      * - use space as and operator
      * - use (a,b,c) as or operator
      */
     $searchText = $this->params()->fromQuery('q');
     if (!empty($searchText)) {
         $form->get('q')->setValue($searchText);
         $matches = array();
         preg_match('/"[^"]+"/', $searchText, $matches);
         $searchElements = preg_replace('/"/', '', $matches);
         $logger->info('found matches:');
         $logger->info($matches);
         $searchArray = split(' ', $searchText);
         $exclude = false;
         $excludeElements = array();
         foreach ($searchArray as $element) {
             if (preg_match('/^"/', $element)) {
                 $exclude = true;
             }
             if (!$exclude) {
                 if (preg_match('/^-/', $element)) {
                     $excludeElements[] = preg_replace('/^-/', '', $element);
                 } else {
                     $searchElements[] = $element;
                 }
             }
             if (preg_match('/"$/', $element)) {
                 $exclude = false;
             }
         }
         $logger->info('search elements:');
         $logger->info($searchElements);
         $logger->info('exclude elements:');
         $logger->info($excludeElements);
         $searchString = array();
         $em = $this->getServiceLocator()->get('Doctrine\\ORM\\EntityManager');
         $result = array();
         /*
          * search code
          */
         $qb = $em->getRepository("ErsBase\\Entity\\Order")->createQueryBuilder('o');
         $qb->join('o.code', 'oc');
         $qb->join('o.packages', 'p');
         $qb->join('p.code', 'pc');
         $i = 0;
         foreach ($searchElements as $element) {
             if ($i == 0) {
                 $qb->where('oc.value LIKE :param' . $i);
             } else {
                 $qb->orWhere('oc.value LIKE :param' . $i);
             }
             $qb->setParameter('param' . $i, $element);
             $i++;
             $code = new Entity\Code();
             $code->setValue($element);
             if ($code->checkCode()) {
                 $qb->orWhere('oc.value LIKE :param' . $i);
                 $qb->orWhere('pc.value LIKE :param' . $i);
                 $qb->setParameter('param' . $i, $code->getValue());
                 $i++;
             }
         }
         $check_first = $i;
         foreach ($excludeElements as $elemnt) {
             if ($i == $check_first) {
                 $qb->where('oc.value NOT LIKE :param' . $i);
             } else {
                 $qb->orWhere('oc.value NOT LIKE :param' . $i);
             }
             $qb->orWhere('pc.value NOT LIKE :param' . $i);
             $qb->setParameter('param' . $i, $element);
             $i++;
         }
         $result = array_merge($result, $qb->getQuery()->getResult());
         /*
          * search firstname, surname, email, birthdate
          * of buyer and participant
          */
         $qb = $em->getRepository("ErsBase\\Entity\\Order")->createQueryBuilder('o');
         $qb->join('o.buyer', 'b');
         $qb->join('o.packages', 'p');
         $qb->join('p.participant', 'u');
         $i = 0;
         foreach ($searchElements as $element) {
             $b_expr = $qb->expr()->lower($qb->expr()->concat('b.firstname', $qb->expr()->concat($qb->expr()->literal(' '), 'b.surname')));
             $u_expr = $qb->expr()->lower($qb->expr()->concat('u.firstname', $qb->expr()->concat($qb->expr()->literal(' '), 'u.surname')));
             $be_expr = $qb->expr()->lower('b.email');
             $ue_expr = $qb->expr()->lower('u.email');
             if ($i == 0) {
                 $qb->where($qb->expr()->like($b_expr, ':param' . $i));
             } else {
                 $qb->orWhere($qb->expr()->like($b_expr, ':param' . $i));
             }
             $qb->orWhere($qb->expr()->like($u_expr, ':param' . $i));
             $qb->orWhere($qb->expr()->like($be_expr, ':param' . $i));
             $qb->orWhere($qb->expr()->like($ue_expr, ':param' . $i));
             $qb->setParameter('param' . $i, '%' . strtolower($element) . '%');
             $i++;
         }
         $result = array_merge($result, $qb->getQuery()->getResult());
     } else {
         $logger->warn($form->getMessages());
     }
     return new ViewModel(array('form' => $form, 'result' => $result));
 }
Ejemplo n.º 2
0
 public function moveAction()
 {
     $logger = $this->getServiceLocator()->get('Logger');
     $id = (int) $this->params()->fromRoute('id', 0);
     if (!$id) {
         return $this->redirect()->toRoute('admin/order', array());
     }
     $em = $this->getServiceLocator()->get('Doctrine\\ORM\\EntityManager');
     $package = $em->getRepository("ErsBase\\Entity\\Package")->findOneBy(array('id' => $id));
     $form = new Form\SearchOrder();
     $results = [];
     $q = trim($this->params()->fromQuery('q'));
     if (!empty($q)) {
         $form->get('q')->setValue($q);
         $em = $this->getServiceLocator()->get('Doctrine\\ORM\\EntityManager');
         $qb = $em->createQueryBuilder()->select('u')->from('ErsBase\\Entity\\User', 'u')->orderBy('u.firstname')->where('1=1');
         /*$qb = $em->createQueryBuilder()
           ->select('p')
           ->from('ErsBase\Entity\Package', 'p')
           ->join('p.participant', 'u')
           ->join('p.code', 'pcode')
           ->join('p.order', 'o')
           ->join('o.code', 'ocode')
           ->join('o.buyer', 'b')
           ->orderBy('u.firstname')
           ->where('1=1');*/
         if (preg_match('~^\\d+$~', $q)) {
             // if the entire query consists of nothing but a number, treat it as a user ID
             $qb->andWhere('u.id = :id');
             $qb->setParameter(':id', (int) $q);
         } else {
             $exprUName = $qb->expr()->concat('u.firstname', $qb->expr()->concat($qb->expr()->literal(' '), 'u.surname'));
             //$exprBName = $qb->expr()->concat('b.firstname', $qb->expr()->concat($qb->expr()->literal(' '), 'b.surname'));
             $words = preg_split('~\\s+~', $q);
             $i = 0;
             foreach ($words as $word) {
                 try {
                     $wordAsDate = new \DateTime($word);
                 } catch (\Exception $ex) {
                     $wordAsDate = NULL;
                 }
                 $param = ':p' . $i;
                 $paramDate = ':pd' . $i;
                 $qb->andWhere($qb->expr()->orX($qb->expr()->like($exprUName, $param), $qb->expr()->like('u.email', $param), $wordAsDate ? $qb->expr()->eq('u.birthday', $paramDate) : '1=0'));
                 $qb->setParameter($param, '%' . $word . '%');
                 if ($wordAsDate) {
                     $qb->setParameter($paramDate, $wordAsDate);
                 }
                 $i++;
             }
         }
         $results = $qb->getQuery()->getResult();
     }
     return new ViewModel(array('form' => $form, 'package' => $package, 'results' => $results));
 }