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