Symfony Profiler

<?php
namespace OceanExpertBundle\Controller;
use Doctrine\Common\Persistence\ManagerRegistry;
use Doctrine\ORM\AbstractQuery;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\JsonResponse;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\HttpFoundation\Request;
class SearchController extends AbstractController
{
public function generalSearchAction(Request $request): Response
{
$searchParams['searchQuery'] = $request->query->get('q');
$searchParams['searchQuery'] = self::cleaned($searchParams['searchQuery']);
if (null !== ($request->query->get('limit'))) {
$limit = $request->query->get('limit');
} else {
$limit = 10;
}
$em = self::getDoctrine()->getManager();
$connection = $em->getConnection();
$statement = $connection->prepare("
SELECT
*
FROM
indiv
WHERE
(
MATCH (fname,mname,sname) AGAINST (:searchQuery IN BOOLEAN MODE)
OR fname LIKE :percSearchQuery
OR fname LIKE :searchQueryPerc
OR mname LIKE :percSearchQuery
OR mname LIKE :searchQueryPerc
OR sname LIKE :percSearchQuery
OR sname LIKE :searchQueryPerc
)
AND status = 1;
");
$statement->bindValue('searchQuery', $searchParams['searchQuery']);
$statement->bindValue('percSearchQuery', '%' . $searchParams['searchQuery']);
$statement->bindValue('searchQueryPerc', $searchParams['searchQuery'] . '%');
$statement->execute();
$query = $statement->fetchAll();
$paginator = $this->get('knp_paginator');
$pagination = $paginator->paginate(
$query,
$request->query->getInt('experts', 1),
$limit,
array('pageParameterName' => 'experts', 'sortDirectionParameterName' => 'dir')
);
$ids = array();
foreach ($pagination->getItems() as $value) {
$ids[] = $value['id_ind'];
}
$indiv = $em->getRepository('OceanExpertBundle:Indiv');
$experts = $indiv->createQueryBuilder('i')
->select('i,ix.instName,g.groupname,m.idGroup,m.role,c.country')
->leftJoin('OceanExpertBundle:IndivInstitution', 'ii', 'WITH', 'ii.idInd = i.idInd')
->leftJoin('OceanExpertBundle:Institutions', 'ix', 'WITH', 'ix.idInst = ii.idInst')
->leftJoin('OceanExpertBundle:MemberGroups', 'm', 'WITH', 'i.idInd = m.idInd')
->leftJoin('OceanExpertBundle:Groups', 'g', 'WITH', 'g.idGroup = m.idGroup')
->leftJoin('OceanExpertBundle:Countries', 'c', 'WITH', 'c.idCountry = i.countryCode')
->where('i.idInd IN (:group)')
->andWhere('i.status = 1')
->setParameter('group', $ids)
->addOrderBy('ix.instName', 'DESC')
->addOrderBy('i.sname', 'ASC')
->getQuery()
->getResult();
$em = self::getDoctrine()->getManager();
$connection = $em->getConnection();
$statement = $connection->prepare("
SELECT
*
FROM
institutions
WHERE
(
MATCH (inst_name,inst_name_eng,acronym) AGAINST (:searchQuery IN BOOLEAN MODE)
OR inst_name LIKE :percSearchQuery
OR inst_name LIKE :searchQueryPerc
OR inst_name_eng LIKE :percSearchQuery
OR inst_name_eng LIKE :searchQueryPerc
OR acronym LIKE :percSearchQuery
OR acronym LIKE :searchQueryPerc
OR activities LIKE :percSearchQueryPerc
)
AND activated = 1;
");
$statement->bindValue('searchQuery', $searchParams['searchQuery']);
$statement->bindValue('percSearchQuery', '%' . $searchParams['searchQuery']);
$statement->bindValue('searchQueryPerc', $searchParams['searchQuery'] . '%');
$statement->bindValue('percSearchQueryPerc', '%' . $searchParams['searchQuery'] . '%');
$statement->execute();
$institutes = $statement->fetchAll();
$institutesPage = $paginator->paginate(
$institutes,
$request->query->getInt('insts', 1),
12,
array(
'pageParameterName' => 'insts',
'sortDirectionParameterName' => 'dir'
)
);
$events = $em->getRepository('OceanExpertBundle:Events');
$events = $events->createQueryBuilder('e')
->select('e')
->where('e.title LIKE :searchQuery')
->where('e.shorttitle LIKE :searchQuery')
->where('e.summary LIKE :searchQuery')
->setParameter('searchQuery', '%' . $searchParams['searchQuery'] . '%')
->addOrderBy('e.title', 'ASC')
->getQuery()
->getResult();
$eventsPage = $paginator->paginate(
$events,
$request->query->getInt('events', 1),
10,
array('pageParameterName' => 'events', 'sortDirectionParameterName' => 'dir')
);
return $this->render(
'Search/generalSearch.html.twig',
array(
'pagination' => $pagination,
'experts' => $experts,
'institutions' => $institutesPage,
'events' => $eventsPage,
'items' => array(),
'qry' => $searchParams['searchQuery']
)
);
}
/**
* extract the possible filters from the results
* these can be used to filter down the results even move (especially in the webinterface)
*
* @param array $dataResult the results from a search
*
* @return array
*/
static function getFilters(array $dataResult): array
{
$filters = array();
foreach(['type', 'country'] as $type) {
$filters[$type] = SearchController::getUnique(
$dataResult,
$type
);
}
return $filters;
}
/**
* Extract all the params we need to do the search.
* These params should be reflected in the documentation of the API.
* As these are all the possible params that can be given and have effect on the result.
* They are shown here in alphabetic order to be sure there are no doubles in the variables.
*
* @param Request $request
*
* @return array all the params in an array
*/
static function extractParams(Request $request): array
{
$searchParams['queryString'] = $request->query->all();
$searchParams['action'] = $request->query->get('action') ?? 'advSearch';
$searchParams['countryName'] = $request->query->get('countryName') ?? '';
$searchParams['endDate'] = $request->query->get('endDate');
$searchParams['expertLname'] = $request->query->get('expertLname') ?? '';
$searchParams['expertSubject'] = $request->query->get('expertSubject') ?? '';
$searchParams['expertRegion'] = $request->query->get('expertRegion') ?? '';
$searchParams['instCountry'] = $request->query->get('instCountry') ?? '';
$searchParams['instName'] = $request->query->get('instName') ?? '';
$searchParams['instRegion'] = $request->query->get('instRegion') ?? '';
$searchParams['instType'] = $request->query->get('instType') ?? '';
$searchParams['searchKeywords'] = $request->query->get('keywords', array());
$searchParams['limit'] = $request->query->get('limit') ?? 10;
$searchParams['sortOrder'] = $request->query->get('order') ?? 'ASC';
$searchParams['searchQuery'] = $request->query->get('query') ?? '';
$searchParams['sortby'] = $request->query->get('sortby') ?? '';
$searchParams['startDate'] = $request->query->get('startDate');
$searchParams['searchToggle'] = $request->query->get('toggle');
//what kind of items are we looking for
$searchParams['searchType'] = $request->query->get('type', array());
//default value for the type when using browse should be 'all'
if (null == $request->query->get('type')
|| $request->query->get('type') == ''
|| (is_array($searchParams['searchType'])
&& !count($searchParams['searchType']))
) {
$searchParams['searchType'] = array('all');
}
//search types should always be an array, even with one element
if (!is_array($searchParams['searchType'])) {
$searchParams['searchType'] = array($searchParams['searchType']);
}
//get rid of those empty types that get added somewhere by javascript
foreach ($searchParams['searchType'] as $key => $val) {
if ($val === '') {
unset($searchParams['searchType'][$key]);
}
}
//independent of the searchType, what type should we use to filter the results
//typically after a search with searchType == all
$searchParams['searchFilterType']= $request->query->get('filterType');
//cleanup the filters
$searchParams['searchFilterType'] = array_unique(
explode(
',',
$searchParams['searchFilterType']
)
);
if ($searchParams['searchFilterType'][0] == '') {
unset($searchParams['searchFilterType']);
}
//special case of FilterType
$searchParams['filterCountry'] = $request->query->get('filterCountry');
//not sure if this is used everywhere/somewhere and what it should do
$searchParams['searchFilter'] = $request->query->get('filter');
//cleanup the query
$searchParams['searchQuery'] = self::cleaned($searchParams['searchQuery']);
//remember the search conditions, so we can recreate the search in the interface
$conditions = array();
foreach ($searchParams['searchType'] as $i => $value) {
$conditions[$i]['type'] = $value;
if (!isset($searchParams['searchFilter'][$i])) {
$searchParams['searchFilter'][$i] = '';
}
$conditions[$i]['filter'] = $searchParams['searchFilter'][$i];
$conditions[$i]['keywords'] = $searchParams['searchKeywords'][$i] ?? '';
if (!isset($searchParams['searchToggle'][$i])) {
$searchParams['searchToggle'][$i] = ' OR ';
}
$conditions[$i]['toggle'] = $searchParams['searchToggle'][$i];
if (!isset($searchParams['startDate'][$i])) {
$searchParams['startDate'][$i] = '';
}
$conditions[$i]['startDate'] = $searchParams['startDate'][$i];
if (!isset($searchParams['endDate'][$i])) {
$searchParams['endDate'][$i] = '';
}
$conditions[$i]['endDate'] = $searchParams['endDate'][$i];
}
$searchParams['conditions'] = $conditions;
//what to do with the sortby if there is none defined (yet)
$sortFields = array(
'relevance' => 'Relevance'
);
if ($searchParams['sortby'] == '') {
//for all, experts and institutions use 'relevance' as sortby in case we have a search for 'name contains'
//in other cases use inst_name for institutions
// or sname for experts
if (!empty(array_intersect($searchParams['searchType'], array('all', 'experts', 'institutions')))
&& !empty(array_intersect(['Name contains'], $searchParams['searchFilter']))
) {
$searchParams['sortby'] = 'relevance';
$sortFields['relevance'] = 'Relevance';
} elseif (!empty(array_intersect($searchParams['searchType'], array('experts')))) {
$searchParams['sortby'] = 'sname';
$sortFields['sname'] = 'Last Name';
} elseif (!empty(array_intersect($searchParams['searchType'], array('documents')))) {
$searchParams['sortby'] = 'title';
$sortFields['country'] = 'Country';
} elseif (!empty(array_intersect($searchParams['searchType'], array('institutions')))) {
$searchParams['sortby'] = 'inst_name';
$sortFields['inst_name'] = 'Institute Name';
}
}
//if we still have no sortby, default to sname for both browse and advSearch
if ($searchParams['sortby'] == ''
&& ($searchParams['action'] == 'browse'
|| $searchParams['action'] == 'advSearch')
) {
$searchParams['sortby'] = 'sname';
unset($sortFields['relevance']);
$sortFields['sname'] = 'Last Name';
}
if (count($searchParams['searchType'])) {
if (in_array('institutions', $searchParams['searchType'])) {
unset($sortFields['relevance']);
$sortFields['instType'] = 'Institute Type';
$sortFields['name'] = 'Institute Name';
}
if (in_array('experts', $searchParams['searchType'])) {
$sortFields['jobtitle'] = 'Position';
$sortFields['sname'] = 'Last Name';
}
if (in_array('events', $searchParams['searchType'])) {
$sortFields['eventtype'] = 'Event Type';
}
$sortFields['country'] = 'Country';
}
$searchParams['sortFields'] = $sortFields;
return $searchParams;
}
/**
* get the results for the search when action=browse
*
* @param array $searchParams all the search parameters that have been passed in this request
* @param object $doctrine
*
* @return array with the results
*/
public static function getBrowseSearchResults(array $searchParams, object $doctrine): array
{
$connection = $doctrine->getManager()->getConnection();
$searchResultType = 'Browse Results';
$dataResult = array();
$sortFields = $searchParams['sortFields'];
$message = '';
//status=0 means everything is ok
$status = 0;
if (is_array($searchParams['searchType'])
&& count($searchParams['searchType']) > 1
) {
//impossible for action=browse, this should be a simple query in the end
$message = 'You can only have one (filter)type when you use action=browse.';
$message .= ' Provided (filter)types: ' . implode(', ', $searchParams['searchType']);
return array(
'status' => 1,
'message' => $message
);
}
//for the searchtype 'browse' we do not need an array of search types
if (isset($searchParams['searchType'])
&& is_array($searchParams['searchType'])
&& count($searchParams['searchType'])
) {
$searchParams['searchType'] = $searchParams['searchType'][0];
}
if ($searchParams['searchQuery'] != '') {
$indiv = array();
$indivNames = array();
$indivGroups = array();
if (($searchParams['searchType'] == 'all'
|| $searchParams['searchType'] == 'experts'
)
&& !(isset($searchParams['queryString']['searchType'])
&& $searchParams['queryString']['searchType'] === 'group')
) {
$newString = self::fulltext($searchParams['searchQuery']);
$stmtInd = $connection->prepare("
SELECT
'experts' AS type,
i.status,
i.id_ind,
i.title,
concat(i.fname,' ',i.sname) AS name,
i.fname,
i.mname,
i.sname,
i.jobtitle,
i.addr_1,
i.addr_2,
i.city,
i.state,
i.postcode,
i.use_inst_addr,
i.deceased,
i.retired,
i.quality_checked,
inst.inst_name,
inst.inst_address,
inst.addr_2 AS instAddr2,
inst.city AS instCity,
inst.state AS instState,
inst.postcode AS instPostCode,
(IF(i.use_inst_addr = 1, ic.country, c.country)) AS country,
MATCH (i.fname,i.sname) AGAINST (:newString IN BOOLEAN MODE) AS relevance,
'zz' AS instType,
'zz' AS eventtype,
'' AS inst_logo,
'zz' AS name
FROM
indiv i
LEFT JOIN countries c ON c.id_country = i.country_code
LEFT JOIN indiv_institution ii ON ii.id_ind = i.id_ind
LEFT JOIN institutions inst ON inst.id_inst = ii.id_inst
LEFT JOIN countries ic ON ic.id_country = inst.country_code
WHERE
(
MATCH (fname, mname, sname) AGAINST (:newString IN BOOLEAN MODE)
OR i.fname LIKE :percSearchQuery
OR i.fname LIKE :searchQueryPerc
OR i.mname LIKE :percSearchQuery
OR i.mname LIKE :searchQueryPerc
OR i.sname LIKE :percSearchQuery
OR i.sname LIKE :searchQueryPerc
OR i.private_address LIKE :percSearchQueryPerc
OR i.addr_1 LIKE :percSearchQueryPerc
OR i.addr_2 LIKE :percSearchQueryPerc
OR i.city LIKE :percSearchQueryPerc
OR i.state LIKE :percSearchQueryPerc
)
" . $searchParams['indivStatus'] . '
ORDER BY relevance DESC, sname ASC;
');
$stmtInd->bindValue('newString', $newString);
$stmtInd->bindValue('percSearchQuery', '%' . $searchParams['searchQuery']);
$stmtInd->bindValue('searchQueryPerc', $searchParams['searchQuery'] . '%');
$stmtInd->bindValue('percSearchQueryPerc', '%' . $searchParams['searchQuery'] . '%');
$stmtInd->execute();
$indiv = $stmtInd->fetchAll();
$sortFields['jobtitle'] = 'Position';
}
if ($searchParams['searchType'] == 'name') {
$newString = self::fulltext($searchParams['searchQuery']);
$stmtInd = $connection->prepare("
SELECT
'experts' AS type,
i.status,
i.id_ind,
i.title,
concat(i.fname,' ',i.sname) AS name,
i.fname,
i.mname,
i.sname,
i.jobtitle,
i.addr_1,
i.addr_2,
i.city,
i.state,
i.postcode,
i.use_inst_addr,
i.deceased,
i.retired,
i.quality_checked,
inst.inst_name,
inst.inst_address,
inst.addr_2 AS instAddr2,
inst.city AS instCity,
inst.state AS instState,
inst.postcode AS instPostCode,
(IF(i.use_inst_addr = 1, ic.country, c.country)) AS country,
MATCH (i.fname,i.sname) AGAINST (:newString IN BOOLEAN MODE) AS relevance,
'zz' AS instType,
'zz' AS eventtype,
'' AS inst_logo,
'zz' AS name
FROM
indiv i
LEFT JOIN countries c ON c.id_country = i.country_code
LEFT JOIN indiv_institution ii ON ii.id_ind = i.id_ind
LEFT JOIN institutions inst ON inst.id_inst = ii.id_inst
LEFT JOIN countries ic ON ic.id_country = inst.country_code
WHERE
(
MATCH (fname, mname, sname) AGAINST (:newString IN BOOLEAN MODE)
OR i.fname LIKE :percSearchQuery
OR i.fname LIKE :searchQueryPerc
OR i.mname LIKE :percSearchQuery
OR i.mname LIKE :searchQueryPerc
OR i.sname LIKE :percSearchQuery
OR i.sname LIKE :searchQueryPerc
)
" . $searchParams['indivStatus'] . '
ORDER BY relevance DESC, sname ASC;
');
$stmtInd->bindValue('newString', $newString);
$stmtInd->bindValue('percSearchQuery', '%' . $searchParams['searchQuery']);
$stmtInd->bindValue('searchQueryPerc', $searchParams['searchQuery'] . '%');
$stmtInd->execute();
$indivNames = $stmtInd->fetchAll();
$sortFields['jobtitle'] = 'Position';
}
if ($searchParams['searchType'] == 'all'
|| (isset($searchParams['queryString']['searchType'])
&& $searchParams['queryString']['searchType'] == 'group'
&& $searchParams['searchType'] == 'experts')
|| $searchParams['searchType'] == 'group'
) {
//looking for experts that are in a group that matches the searchTerm
$newString = self::fulltext($searchParams['searchQuery']);
$stmtInd = $connection->prepare("
SELECT
'experts' AS type,
i.status,
i.id_ind,
i.title,
concat(i.fname,' ',i.sname) AS name,
i.fname,
i.mname,
i.sname,
i.jobtitle,
i.addr_1,
i.addr_2,
i.city,
i.state,
i.postcode,
i.use_inst_addr,
i.deceased,
i.retired,
i.quality_checked,
inst.inst_name,
inst.inst_address,
inst.addr_2 AS instAddr2,
inst.city AS instCity,
inst.state AS instState,
inst.postcode AS instPostCode,
g.groupname AS groupName,
g.id_group AS groupId,
'' AS relevance,
(IF(i.use_inst_addr = 1, ic.country, c.country)) AS country
FROM
indiv i
LEFT JOIN countries c ON c.id_country = i.country_code
LEFT JOIN indiv_institution ii ON ii.id_ind = i.id_ind
LEFT JOIN institutions inst ON inst.id_inst = ii.id_inst
LEFT JOIN countries ic ON ic.id_country = inst.country_code
LEFT JOIN member_groups mg ON mg.id_ind = i.id_ind
LEFT JOIN groups g ON mg.id_group = g.id_group
WHERE
(
g.groupname LIKE :percSearchQueryPerc
)
" . $searchParams['indivStatus'] . '
ORDER BY sname ASC;
');
$stmtInd->bindValue('percSearchQueryPerc', '%' . $searchParams['searchQuery'] . '%');
$stmtInd->execute();
$indivGroups = $stmtInd->fetchAll();
$sortFields['jobtitle'] = 'Position';
}
$institutes = array();
if ($searchParams['searchType'] == 'all'
|| $searchParams['searchType'] == 'institutions'
) {
$newString = self::fulltext($searchParams['searchQuery']);
$stmtInst = $connection->prepare("
SELECT
'institutions' AS type,
i.activated,
i.id_inst,
insttype_name AS instType,
inst_name,
inst_name_eng,
inst_address,
addr_2,
city AS instCity,
state AS instState,
postcode AS instPostCode,
country,
inst_logo,
activities,
MATCH (inst_name,inst_name_eng,acronym) AGAINST (:newString) AS relevance,
'zz' AS jobtitle,
'zz' AS eventtype,
'zz' AS fname,
'zz' AS sname,
'zz' AS name
FROM
institutions i
LEFT JOIN countries c ON c.id_country = i.country_code
LEFT JOIN insttypes it ON it.id_insttype = i.inst_type_id
WHERE
(
MATCH (inst_name,inst_name_eng,acronym) AGAINST (:newString IN BOOLEAN MODE)
OR inst_name LIKE :percSearchQueryPerc
OR inst_name_eng LIKE :percSearchQueryPerc
OR inst_address LIKE :percSearchQueryPerc
OR addr_2 LIKE :percSearchQueryPerc
OR city LIKE :percSearchQueryPerc
OR state LIKE :percSearchQueryPerc
OR acronym LIKE :percSearchQueryPerc
OR activities LIKE :percSearchQueryPerc
)
" . $searchParams['instStatus'] . '
ORDER BY relevance DESC, i.inst_name ASC;
');
$stmtInst->bindValue('newString', $newString);
$stmtInst->bindValue('percSearchQueryPerc', '%'.$searchParams['searchQuery'].'%');
$stmtInst->execute();
$institutes = $stmtInst->fetchAll();
$sortFields['instType'] = 'Institution Type';
}
$events = array();
if ($searchParams['searchType'] == 'all'
|| $searchParams['searchType'] == 'events'
) {
$sortFields['name'] = 'Event Title';
if ($searchParams['sortby'] == '') {
$searchParams['sortby'] = 'name';
}
$query = "
SELECT
'events' AS type,
et.eventtype_name AS eventtype,
e.status AS activated,
e.id_event,
e.title AS name,
e.start_on,
e.end_on,
e.address AS addr_1,
e.city,
e.state,
c.country AS country,
'zz' AS jobtitle,
'zz' AS instType,
'zz' AS fname,
'zz' AS sname,
'zz' AS inst_name
FROM
events e
LEFT JOIN countries c on c.id_country = e.id_country
LEFT JOIN eventtypes et on et.id_eventtype = e.id_eventtype
WHERE
(
e.title LIKE :percSearchQueryPerc
OR e.summary LIKE :percSearchQueryPerc
OR e.shorttitle LIKE :percSearchQueryPerc
OR e.keywords LIKE :percSearchQueryPerc
OR e.website LIKE :percSearchQueryPerc
OR e.city LIKE :percSearchQueryPerc
OR e.address LIKE :percSearchQueryPerc
)
" . $searchParams['eventStatus'] . '
ORDER BY e.title ASC;
';
$stmtEvent = $connection->prepare($query);
$stmtEvent->bindValue('searchQuery', $searchParams['searchQuery']);
$stmtEvent->bindValue('percSearchQueryPerc', '%' . $searchParams['searchQuery'] . '%');
$stmtEvent->execute();
$events = $stmtEvent->fetchAll();
$sortFields['eventtype'] = 'Event Type';
}
$documents = array();
if ($searchParams['searchType'] == 'all'
|| $searchParams['searchType'] == 'documents'
) {
$sortFields['title'] = 'Document Title';
if ($searchParams['sortby'] == '') {
$searchParams['sortby'] = 'title';
}
$query = "
SELECT
'documents' AS type,
d.id_doc,
dt.doctypename AS documenttype,
d.title,
d.created_at,
'undefined' AS country
FROM
documents d,
doctypes dt
WHERE
dt.id_doctype = d.id_doctype
AND d.title LIKE :percSearchQueryPerc
ORDER BY d.title ASC
";
$stmtDocument = $connection->prepare($query);
$stmtDocument->bindValue('percSearchQueryPerc', '%' . $searchParams['searchQuery'] . '%');
$stmtDocument->execute();
$documents = $stmtDocument->fetchAll();
$sortFields['documenttype'] = 'Document Type';
}
$dataResult = array_unique(
array_merge(
$indiv,
$indivNames,
$indivGroups,
$institutes,
$events,
$documents
),
SORT_REGULAR
);
if ($searchParams['searchType'] == 'all') {
if (count($dataResult) == 0) {
$message = 'We could not find any results matching "' . self::cleaned($searchParams['searchQuery']) . ' ". ';
$message.= 'Please try again with different keyword(s).';
return array(
'status' => 3,
'message' => $message,
'dataResult' => array(),
'searchResultType' => '',
'sortFields' => array()
);
}
$message = 'Your search for "' . self::cleaned($searchParams['searchQuery']) . '" ';
$message.= 'returned ' . count($dataResult) . ' result(s).';
} elseif ($searchParams['searchType'] == 'experts') {
if (count($dataResult) == 0) {
$message = 'We could not find any results matching "' . self::cleaned($searchParams['searchQuery']) . '" in Experts. ';
$message.= 'Please try again with different keyword(s).';
return array(
'status' => 3,
'message' => $message,
'dataResult' => array(),
'searchResultType' => '',
'sortFields' => array()
);
}
$message = 'Your search for "' . self::cleaned($searchParams['searchQuery']) . '" in "Experts" ';
$message.= 'returned ' . count($dataResult) . ' result(s).';
} elseif ($searchParams['searchType'] == 'institutions') {
if (count($dataResult) == 0) {
$message = 'We could not find any results matching "' . self::cleaned($searchParams['searchQuery']) . '" in Institutions. ';
$message.= 'Please try again with different keyword(s).';
return array(
'status' => 3,
'message' => $message,
'dataResult' => array(),
'searchResultType' => '',
'sortFields' => array()
);
}
$message = 'Your search for "' . self::cleaned($searchParams['searchQuery']) . '" in "Institutions" ';
$message.= 'returned ' . count($dataResult) . ' result(s).';
} elseif ($searchParams['searchType'] == 'documents') {
$searchParams['countryName'] = '';
$searchParams['expertLname'] = '';
if (count($dataResult) == 0) {
$message = 'We could not find any results matching "' . self::cleaned($searchParams['searchQuery']) . '" in the title of Documents. ';
$message .= 'Please try again with different word(s).';
return array(
'status' => 3,
'message' => $message,
'dataResult' => array(),
'searchResultType' => '',
'sortFields' => array()
);
}
$message = "Your search for \"" . self::cleaned($searchParams['searchQuery']) . "\" ";
$message .= "in \"Documents\" ";
$message .= "returned " . count($dataResult) . " result(s).";
}
}
if ($searchParams['expertLname'] != '') {
$stmtInd = $connection->prepare("
SELECT
'experts' AS type,
i.status,
i.id_ind,
concat(i.fname, ' ', i.sname) AS name,
i.fname,
i.mname,
i.sname,
inst_name,
jobtitle,
i.addr_1,
i.addr_2,
i.city,
i.state,
i.postcode,
inst.inst_address,
inst.addr_2 AS instAddr2,
inst.city AS instCity,
inst.state AS instState,
inst.postcode AS instPostCode,
use_inst_addr,
deceased,
retired,
quality_checked,
(IF(use_inst_addr = 1, ic.country, c.country)) AS country,
'zz' AS instType,
'zz' AS eventtype,
'' AS inst_logo
FROM
indiv i
LEFT JOIN countries c on c.id_country = i.country_code
LEFT JOIN indiv_institution ii on ii.id_ind = i.id_ind
LEFT JOIN institutions inst on inst.id_inst = ii.id_inst
LEFT JOIN countries ic on ic.id_country = inst.country_code
WHERE
(sname LIKE :expertLNamePerc)
" . $searchParams['indivStatus'] . '
ORDER BY sname, fname ASC
');
$stmtInd->bindValue('expertLNamePerc', $searchParams['expertLname'] . '%');
$stmtInd->execute();
$dataResult = $stmtInd->fetchAll();
$sortFields['jobtitle'] = 'Position';
$message = 'There are ' . count($dataResult) . ' experts ';
$message.= 'with their last name starting with "' . self::cleaned($searchParams['expertLname']) . '".';
}
if ($searchParams['countryName'] != '') {
if ($searchParams['searchType'] == 'experts') {
$query = "
SELECT
'experts' AS type,
i.status,
i.id_ind,
concat(i.fname,' ',i.sname) AS name,
i.fname,
i.mname,
i.sname,
inst_name,
jobtitle,
i.addr_1,
i.addr_2,
i.city,
i.state,
i.postcode,
inst.inst_address,
inst.addr_2 AS instAddr2,
inst.city AS instCity,
inst.state AS instState,
inst.postcode AS instPostCode,
use_inst_addr,
deceased,
retired,
quality_checked,
(IF(use_inst_addr = 1, ic.country, c.country)) AS country,
'zz' AS instType,
'zz' AS eventtype,
'' AS inst_logo
FROM
indiv i
LEFT JOIN countries c on c.id_country = i.country_code
LEFT JOIN indiv_institution ii on ii.id_ind = i.id_ind
LEFT JOIN institutions inst on inst.id_inst = ii.id_inst
LEFT JOIN countries ic on ic.id_country = inst.country_code
WHERE
( CASE WHEN use_inst_addr = 1 THEN ";
if (strlen($searchParams['countryName']) === 1) {
$query .= 'ic.country LIKE :countryNamePerc ELSE c.country LIKE :countryNamePerc END) ';
} else {
$query .= 'ic.country LIKE :countryName ELSE c.country LIKE :countryName END) ';
}
$query .= $searchParams['indivStatus'] . ' ORDER BY sname ASC ';
$stmtInd = $connection->prepare($query);
$stmtInd->bindValue('countryName', $searchParams['countryName']);
$stmtInd->bindValue('countryNamePerc', $searchParams['countryName'] . '%');
$stmtInd->execute();
$dataResult = $stmtInd->fetchAll();
$sortFields['jobtitle'] = 'Position';
$message = 'There are ' . count($dataResult) . ' experts ';
$message.= 'located in countries with names starting with "' . self::cleaned($searchParams['countryName']) . '".';
if (isset($searchParams['referrer'])
&& ($searchParams['referrer'] == 'map')
) {
$message = 'There are ' . count($dataResult) . ' experts ';
$message.= 'located in "' . self::cleaned($searchParams['countryName']) . '".';
}
} elseif ($searchParams['searchType'] == 'institution') {
$stmtInd = $connection->prepare("
SELECT
'institutions' AS type,
insttype_name AS jobtitle,
i.activated,
i.id_inst,
insttype_name AS instType,
inst_name,
inst_name_eng,
inst_address,
addr_2,
city AS instCity,
state AS instState,
postcode AS instPostCode,
country,
activities,
inst_logo,
'zz' AS jobtitle,
'zz' AS fname,
'zz' AS sname
FROM
institutions i
LEFT JOIN countries c on c.id_country = i.country_code
LEFT JOIN insttypes it on it.id_insttype = i.inst_type_id
WHERE
( c.country LIKE '" . $searchParams['countryName'] . "' )
" . $searchParams['instStatus'] . '
ORDER BY inst_name ASC
');
$stmtInd->execute();
$dataResult = $stmtInd->fetchAll();
$sortFields['instType'] = 'Institution Type';
$message = 'There are ' . count($dataResult) . ' experts ';
$message.= 'located in countries with names starting with "' . self::cleaned($searchParams['countryName']) . '".';
if (isset($searchParams['referrer'])
&& ($searchParams['referrer'] == 'map')
) {
$message = 'There are ' . count($dataResult) . ' experts ';
$message.= 'located in "' . self::cleaned($searchParams['countryName']) . '".';
}
}
}
if ($searchParams['expertSubject'] != '') {
$stmtInd = $connection->prepare("
SELECT
'experts' AS type,
i.status,
i.id_ind,
concat(i.fname,' ',i.sname) AS name,
i.fname,
i.mname,
i.sname,
inst_name,
jobtitle,
i.addr_1,
i.addr_2,
i.city,
i.state,
i.postcode,
inst.inst_address,
inst.addr_2 AS instAddr2,
inst.city AS instCity,
inst.state AS instState,
inst.postcode AS instPostCode,
use_inst_addr,
deceased,
retired,
quality_checked,
(IF(use_inst_addr = 1, ic.country, c.country)) AS country,
'zz' AS instType,
'zz' AS eventtype,
'' AS inst_logo
FROM
indiv i
LEFT JOIN countries c on c.id_country = i.country_code
LEFT JOIN indiv_institution ii on ii.id_ind = i.id_ind
LEFT JOIN institutions inst on inst.id_inst = ii.id_inst
LEFT JOIN indiv_subjects ins on ins.id_ind = i.id_ind
LEFT JOIN countries ic on ic.id_country = inst.country_code
WHERE
(ins.id_sub = :expertSubject)
" . $searchParams['indivStatus'] . '
ORDER BY sname ASC
');
$stmtInd->bindValue('expertSubject', $searchParams['expertSubject']);
$stmtInd->execute();
$dataResult = $stmtInd->fetchAll();
$sortFields['jobtitle'] = 'Position';
$message = 'There are ' . count($dataResult) . ' experts ';
$message.= "working in \"" . self::getSubjectAreaById($searchParams['expertSubject'], $doctrine) . "\".";
}
if ($searchParams['expertRegion'] != '') {
$stmtInd = $connection->prepare("
SELECT
'experts' AS type,
i.status,
i.id_ind,
concat(i.fname,' ',i.sname) AS name,
i.fname,
i.mname,
i.sname,
inst_name,
jobtitle,
i.addr_1,
i.addr_2,
i.city,
i.state,
i.postcode,
inst.inst_address,
inst.addr_2 AS instAddr2,
inst.city AS instCity,
inst.state AS instState,
inst.postcode AS instPostCode,
use_inst_addr,
deceased,
retired,
quality_checked,
(IF(use_inst_addr = 1, ic.country, c.country)) AS country,
'zz' AS instType,
'zz' AS eventtype,
'' AS inst_logo
FROM
indiv i
LEFT JOIN countries c on c.id_country = i.country_code
LEFT JOIN indiv_institution ii on ii.id_ind = i.id_ind
LEFT JOIN institutions inst on inst.id_inst = ii.id_inst
LEFT JOIN countries ic on ic.id_country = inst.country_code
WHERE
FIND_IN_SET (
:expertRegion,
i.studyregion
)
" . $searchParams['indivStatus'] . '
ORDER BY sname ASC
');
$stmtInd->bindValue('expertRegion', $searchParams['expertRegion']);
$stmtInd->execute();
$dataResult = $stmtInd->fetchAll();
$sortFields['jobtitle'] = 'Position';
$message = 'There are ' . count($dataResult) . ' experts ';
$message.= "working in the \"" . self::getSeaRegionById($searchParams['expertRegion'], $doctrine) . "\".";
}
if ($searchParams['instName'] != '') {
$stmtInst = $connection->prepare("
SELECT
'institutions' AS type,
i.activated,
i.id_inst,
insttype_name AS instType,
inst_name,
inst_name_eng,
inst_address,
addr_2,
city AS instCity,
state AS instState,
postcode AS instPostCode,
country,
activities,
inst_logo,
'zz' AS jobtitle,
'zz' AS eventtype,
'zz' AS fname,
'zz' AS sname
FROM
institutions i
LEFT JOIN countries c on c.id_country = i.country_code
LEFT JOIN insttypes it on it.id_insttype = i.inst_type_id
WHERE
(
inst_name LIKE :instNamePerc
OR inst_name_eng LIKE :instNamePerc
OR acronym LIKE :instNamePerc
)
" . $searchParams['instStatus'] . "
ORDER BY inst_name ASC
");
$stmtInst->bindValue('instNamePerc', $searchParams['instName'] . '%');
$stmtInst->execute();
$dataResult = $stmtInst->fetchAll();
$sortFields['instType'] = 'Institution Type';
$sortFields['inst_name'] = 'Institution Name';
$searchParams['sortby'] = 'inst_name';
$message = 'There are ' . count($dataResult) . ' institutions ';
$message.= 'with their name starting with "' . self::cleaned($searchParams['instName']) . '".';
}
if ($searchParams['instCountry'] != '') {
$stmtInst = $connection->prepare("
SELECT
'institutions' AS type,
i.activated,
i.id_inst,
insttype_name AS instType,
inst_name,
inst_name_eng,
inst_address,
addr_2,
city AS instCity,
state AS instState,
postcode AS instPostCode,
country,
activities,
inst_logo,
'zz' AS jobtitle,
'zz' AS eventtype,
'zz' AS fname,
'zz' AS sname
FROM
institutions i
LEFT JOIN countries c on c.id_country = i.country_code
LEFT JOIN insttypes it on it.id_insttype = i.inst_type_id
WHERE
( c.country LIKE :instCountryPerc )
" . $searchParams['instStatus'] . '
ORDER BY inst_name ASC
');
$stmtInst->bindValue('instCountryPerc', $searchParams['instCountry'] . '%');
$stmtInst->execute();
$dataResult = $stmtInst->fetchAll();
$sortFields['instType'] = 'Institution Type';
$sortFields['inst_name'] = 'Institution Name';
$searchParams['sortby'] = 'inst_name';
$message = 'There are ' . count($dataResult) . ' institutions ';
$message.= 'located in countries with names starting with "' . self::cleaned($searchParams['instCountry']) . '".';
}
if ($searchParams['instRegion'] != '') {
$stmtInst = $connection->prepare("
SELECT
'institutions' AS type,
i.activated,
i.id_inst,
insttype_name AS instType,
inst_name,
inst_name_eng,
inst_address,
addr_2,
city AS instCity,
state AS instState,
postcode AS instPostCode,
country,
activities,
inst_logo,
'zz' AS jobtitle,
'zz' AS eventtype,
'zz' AS fname,
'zz' AS sname
FROM
institutions i
LEFT JOIN countries c on c.id_country = i.country_code
LEFT JOIN insttypes it on it.id_insttype = i.inst_type_id
WHERE
FIND_IN_SET (
:instRegion,
i.inst_region
)
" . $searchParams['instStatus'] . '
ORDER BY inst_name ASC
');
$stmtInst->bindValue('instRegion', $searchParams['instRegion']);
$stmtInst->execute();
$dataResult = $stmtInst->fetchAll();
$sortFields['instType'] = 'Institution Type';
$sortFields['inst_name'] = 'Institution Name';
$searchParams['sortby'] = 'inst_name';
$message = 'There are ' . count($dataResult) . ' institutions ';
$message.= 'working in the "' . self::getSeaRegionById($searchParams['instRegion'], $doctrine) . '".';
}
if ($searchParams['instType'] != '') {
$stmtInst = $connection->prepare("
SELECT
'institutions' AS type,
i.activated,
i.id_inst,
insttype_name AS instType,
inst_name,
inst_name_eng,
inst_address,
addr_2,
city AS instCity,
state AS instState,
postcode AS instPostCode,
country,
activities,
inst_logo,
'zz' AS jobtitle,
'zz' AS eventtype,
'zz' AS fname,
'zz' AS sname
FROM
institutions i
LEFT JOIN countries c on c.id_country = i.country_code
LEFT JOIN insttypes it on it.id_insttype = i.inst_type_id
WHERE
( i.inst_type_id = :instType)
" . $searchParams['instStatus'] . '
ORDER BY inst_name ASC
');
$stmtInst->bindValue('instType', $searchParams['instType']);
$stmtInst->execute();
$dataResult = $stmtInst->fetchAll();
$sortFields['instType'] = 'Institution Type';
$sortFields['inst_name'] = 'Institution Name';
$searchParams['sortby'] = 'inst_name';
$message = 'There are ' . count($dataResult) . ' institutions ';
$message.= 'with type "' . SearchController::getInstituteTypeById($searchParams['instType'], $doctrine) . '".';
}
//status=0 means everything is ok
return array(
'status' => $status,
'dataResult' => $dataResult,
'sortFields' => $sortFields,
'sortby' => $searchParams['sortby'],
'message' => $message,
'searchResultType' => $searchResultType
);
}
/**
* get the results for the search when action=advSearch
*
* @param array $searchParams all the search parameters that have been passed in this request
* @param object $doctrine
*
* @return array with the results
*/
public static function getAdvSearchResults(array $searchParams, object $doctrine): array
{
$connection = $doctrine->getManager()->getConnection();
$searchResultType = 'Advanced Search Results';
$dataResult = array();
$sortBy = $searchParams['sortby'];
//$searchParams['sortby'] = 'sname';
$messageStart = 'Your search for ';
$messagePart = '';
$sortFields = $searchParams['sortFields'];
if (isset($searchParams['searchType'])
&& isset($searchParams['searchType'][0])
&& $searchParams['searchType'][0] === 'all'
&& !count($searchParams['queryString'])
) {
//we don't want a search for all types with specific search params
return array(
'status' => 0,
'dataResult' => array(),
'sortFields' => $sortFields,
'sortby' => 'relevance',
'message' => 'we cannot show the results, too many results!!!!',
'searchResultType' => $searchResultType
);
} elseif (isset($searchParams['searchType'])
&& count($searchParams['searchType']) > 0
) {
//dump($searchParams);
//where to store the results for the different types
$dataResultInd = array();
$dataResultInst = array();
$dataResultEvent = array();
$dataResultDocument = array();
$instCondCnt = 1;
$endQueryInst = false;
$endQueryInd = false;
$endQueryEvent = false;
$endQueryDocument = false;
$whereInd = ' WHERE ( 1=1 ';
$whereInst = ' WHERE ( 1=1 ';
$whereEvent = ' WHERE ( 1=1 ';
$whereDocument = '';
$joinsInd = '';
$searchParams['searchToggle'][-1] = ' AND ';
$operationInd = ' AND ';
$operationInst = ' AND ';
$operationEvent = ' AND ';
$operation = '';
foreach ($searchParams['searchType'] as $key => $type) {
if (isset($searchParams['searchKeywords'])
&& isset($searchParams['searchKeywords'][$key])
) {
if (is_array($searchParams['searchKeywords'][$key])) {
foreach($searchParams['searchKeywords'][$key] as $value) {
$string[] = addcslashes($value,'\'');
}
} else {
$string = addcslashes($searchParams['searchKeywords'][$key], '\'');
}
} else {
$string = '';
}
//what kind of filter are we using
//there should be one at least, except for type=all
if (isset($searchParams['searchFilter'][$key])) {
$value = $searchParams['searchFilter'][$key] ?? '';
} else {
$value = $searchParams['searchFilter'][array_key_last($searchParams['searchFilter'])];
}
if ($type != 'all'
&& $value == ''
) {
continue;
}
if ($type == 'experts') {
if ($sortBy == '') {
$sortBy = 'sname';
}
$bindValues[$type][$key] = array();
$selectQryInd = "
SELECT
'experts' AS type,
i.status,
i.id_ind,
concat(i.fname,' ',i.sname) AS name,
i.fname,
i.mname,
i.sname,
inst.inst_name,
i.jobtitle,
i.addr_1,
i.addr_2,
i.city,
i.state,
i.postcode,
inst.inst_address,
inst.addr_2 AS instAddr2,
inst.city AS instCity,
inst.state AS instState,
inst.postcode AS instPostCode,
i.use_inst_addr,
deceased,
retired,
quality_checked,
(CASE WHEN i.use_inst_addr = 1 THEN ic.country ELSE c.country END) AS country,
'zz' AS instType,
'zz' AS eventtype,
'' AS inst_logo ";
$commonJoins = ' LEFT JOIN countries c on c.id_country = i.country_code
LEFT JOIN indiv_institution ii on ii.id_ind = i.id_ind
LEFT JOIN institutions inst on inst.id_inst = ii.id_inst
LEFT JOIN countries ic on ic.id_country = inst.country_code ';
$matchOrder = '';
switch ($value) {
case 'Name contains':
$newString = self::fulltext($string);
$selectQryInd .= " , MATCH (fname, mname, sname) AGAINST ('$newString' IN BOOLEAN MODE) AS relevance ";
$whereInd .= ' ' . $operationInd . " ( i.fname LIKE :percString$key
OR i.fname LIKE :stringPerc$key
OR i.mname LIKE :percString$key
OR i.mname LIKE :stringPerc$key
OR i.sname LIKE :percString$key
OR i.sname LIKE :stringPerc$key
OR MATCH (fname, mname, sname) AGAINST ('$newString' IN BOOLEAN MODE) ) ";
$messagePart .= "$operation \"" . self::cleaned($string) . "\" ";
$messagePart .= "in $type ";
$messagePart .= "using filter \"" . self::readableFilter($value) . "\" ";
$matchOrder = ' ORDER BY relevance DESC';
//remember what value has to be bound to what placeholder
$bindValues[$type][$key] = array(
'percString' . $key => '%' . $string,
'stringPerc' . $key => $string . '%'
);
break;
case 'First name contains':
$whereInd .= ' ' . $operationInd . " (
i.fname LIKE :percString$key
OR i.fname LIKE :stringPerc$key ) ";
$messagePart .= "$operation \"" . self::cleaned($string) . "\" ";
$messagePart .= "in $type ";
$messagePart .= "using filter \"" . self::readableFilter($value) . "\" ";
//remember what value has to be bound to what placeholder
$bindValues[$type][$key] = array(
'percString' . $key => '%' . $string,
'stringPerc' . $key => $string . '%'
);
break;
case 'Last name contains':
$whereInd .= ' ' . $operationInd . "(
i.sname LIKE :percString$key
OR i.sname LIKE :stringPerc$key ) ";
$messagePart .= "$operation \"" . self::cleaned($string) . "\" ";
$messagePart .= "in $type ";
$messagePart .= "using filter \"" . self::readableFilter($value) . "\" ";
//remember what value has to be bound to what placeholder
$bindValues[$type][$key] = array(
'percString' . $key => '%' . $string,
'stringPerc' . $key => $string . '%'
);
break;
case 'Worklocation contains':
$whereInd .= ' ' . $operationInd . "
( CASE
WHEN i.use_inst_addr = 1 THEN
inst.inst_address LIKE :percStringPerc$key
OR inst.addr_2 LIKE :percStringPerc$key
OR inst.city LIKE :percStringPerc$key
OR inst.state LIKE :percStringPerc$key
ELSE
i.addr_1 LIKE :percStringPerc$key
OR i.addr_2 LIKE :percStringPerc$key
OR i.city LIKE :percStringPerc$key
OR i.state LIKE :percStringPerc$key
OR i.private_address LIKE :percStringPerc$key
END
) ";
$messagePart .= "$operation \"" . self::cleaned($string) . "\" ";
$messagePart .= "in $type ";
$messagePart .= "using filter \"" . self::readableFilter($value) . "\" ";
//remember what value has to be bound to what placeholder
$bindValues[$type][$key] = array(
'percStringPerc' . $key => '%' . $string . '%'
);
break;
/*
* should not be used
* #680
case 'Current/Previous addresses contain':
$joinsInd .= ' LEFT JOIN previous_addresses pa on pa.id_record = i.id_ind ';
$whereInd .= ' ' . $operationInd . "
( CASE
WHEN i.use_inst_addr = 1 THEN
inst.inst_address LIKE :percStringPerc$key
OR inst.addr_2 LIKE :percStringPerc$key
OR inst.city LIKE :percStringPerc$key
OR inst.state LIKE :percStringPerc$key
ELSE
i.addr_1 LIKE :percStringPerc$key
OR i.addr_2 LIKE :percStringPerc$key
OR i.city LIKE :percStringPerc$key
OR i.state LIKE :percStringPerc$key
OR i.private_address LIKE :percStringPerc$key
OR pa.city LIKE :percStringPerc$key
OR pa.state LIKE :percStringPerc$key
OR pa.address LIKE :percStringPerc$key
END
) ";
$messagePart .= "$operation \"" . self::cleaned($string) . "\" ";
$messagePart .= "in $type ";
$messagePart .= "using filter \"" . self::readableFilter($value) . "\" ";
//remember what value has to be bound to what placeholder
$bindValues[$type][$key] = array(
'percStringPerc' . $key => '%' . $string . '%'
);
break;
*/
case 'Phone/Fax contains':
$whereInd .= ' ' . $operationInd . " (
i.tel LIKE :percStringPerc$key
OR i.fax LIKE :percStringPerc$key
)";
$messagePart .= "$operation \"" . self::cleaned($string) . "\" ";
$messagePart .= "in $type ";
$messagePart .= "using filter \"" . self::readableFilter($value) . "\" ";
//remember what value has to be bound to what placeholder
$bindValues[$type][$key] = array(
'percStringPerc' . $key => '%' . $string . '%'
);
break;
case 'Email contains':
$whereInd .= ' ' . $operationInd . " (
i.email1 LIKE :percStringPerc$key
OR i.email2 LIKE :percStringPerc$key
OR i.email3 LIKE :percStringPerc$key
) ";
$messagePart .= "$operation \"" . self::cleaned($string) . "\" ";
$messagePart .= "in $type ";
$messagePart .= "using filter \"" . self::readableFilter($value) . "\" ";
//remember what value has to be bound to what placeholder
$bindValues[$type][$key] = array(
'percStringPerc' . $key => '%' . $string . '%'
);
break;
case 'Website URL contains':
$whereInd .= ' ' . $operationInd . " (
i.url1 LIKE :percStringPerc$key
OR i.url2 LIKE :percStringPerc$key
OR i.url3 LIKE :percStringPerc$key
) ";
$messagePart .= "$operation \"" . self::cleaned($string) . "\" ";
$messagePart .= "in $type ";
$messagePart .= "using filter \"" . self::readableFilter($value) . "\" ";
//remember what value has to be bound to what placeholder
$bindValues[$type][$key] = array(
'percStringPerc' . $key => '%' . $string . '%'
);
break;
case 'Worklocation is':
$whereInd .= ' ' . $operationInd . "
( CASE
WHEN i.use_inst_addr = 1 THEN
inst.country_code = $string
ELSE
i.country_code = $string
END
) ";
$messagePart .= "$operation $type from \"" . self::getCountryById($string, $doctrine) . "\" ";
break;
case 'Worklocation in countries':
$string = implode(',', $string);
$whereInd .= ' ' . $operationInd . "
( CASE
WHEN i.use_inst_addr = 1 THEN
inst.country_code in ($string)
ELSE
i.country_code in ($string)
END
) ";
$messagePart .= "$operation $type from \"" . implode(', ', self::getMultipleCountriesById($string, $doctrine)) . "\" ";
break;
case 'Worklocation in region':
//get the countries that are in that region
//the rest of the query is identical to 'Worklocation in countries'
$idRegion = $string;
//reset $string, will be an array
$string = array();
//get the countries in that region
//and create an array with the ids of the countries
$countriesInRegion = self::getCountriesInRegion($idRegion, $doctrine);
foreach ($countriesInRegion as $country) {
$string[] = $country->getIdCountry();
}
$string = implode(',', $string);
//this is identical to 'Worklocation in countries'
$whereInd .= ' ' . $operationInd . "
( CASE
WHEN i.use_inst_addr = 1 THEN
inst.country_code in ($string)
ELSE
i.country_code in ($string)
END
) ";
$messagePart .= "$operation $type from the region ";
$messagePart .= '"' . self::getRegionById($idRegion, $doctrine) . '" ';
$messagePart .= ' (' . implode(', ', self::getMultipleCountriesById($string, $doctrine)) . ') ';
break;
case 'Sea regions of study is':
$whereInd .= ' ' . $operationInd . " (
FIND_IN_SET ('$string', i.studyregion)
) ";
$messagePart .= "$operation \"" . self::getSeaRegionById($string, $doctrine) . "\" ";
$messagePart .= "in $type ";
$messagePart .= "using filter \"" . self::readableFilter($value) . "\" ";
break;
case 'Member of group or sub-group':
$groups = implode(',', self::getChildGroup($string, $doctrine));
$joinsInd .= ' LEFT JOIN member_groups mg ON mg.id_ind = i.id_ind ';
$whereInd .= ' ' . $operationInd . " (
mg.id_group IN ($groups)
) ";
$messagePart .= "$operation \"" . self::getGroupById($string, $doctrine) . "\" ";
$messagePart .= "in $type ";
$messagePart .= "using filter \"Member of group OR sub-group\" ";
break;
case 'Job type is':
$joinsInd .= " LEFT JOIN indiv_jobtype ij ON ij.id_ind = i.id_ind ";
$whereInd .= ' ' . $operationInd . " (
id_job = $string
) ";
$messagePart .= "$operation \"" . self::getJobTypesById($string, $doctrine) . "\" ";
$messagePart .= "in $type ";
$messagePart .= "using filter \"" . self::readableFilter($value) . "\" ";
break;
case 'Job title contains':
$whereInd .= ' ' . $operationInd . " (
i.jobtitle LIKE :percString$key
OR i.jobtitle LIKE :stringPerc$key
) ";
$messagePart .= "$operation \"" . self::cleaned($string) . "\" ";
$messagePart .= "in $type ";
$messagePart .= "using filter \"" . self::readableFilter($value) . "\" ";
//remember what value has to be bound to what placeholder
$bindValues[$type][$key] = array(
'percString' . $key => '%' . $string,
'stringPerc' . $key => $string . '%'
);
break;
case 'Department contains':
$whereInd .= ' ' . $operationInd . " (
i.dept LIKE :percString$key
OR i.dept LIKE :stringPerc$key
) ";
$messagePart .= "$operation \"" . self::cleaned($string) . "\" ";
$messagePart .= "in $type ";
$messagePart .= "using filter \"" . self::readableFilter($value) . "\" ";
//remember what value has to be bound to what placeholder
$bindValues[$type][$key] = array(
'percString' . $key => '%' . $string,
'stringPerc' . $key => $string . '%'
);
break;
case 'Institution name contains':
$whereInd .= ' ' . $operationInd . " (
inst.inst_name LIKE :percStringPerc$key
OR inst.inst_name_eng LIKE :percStringPerc$key
OR inst.acronym LIKE :percStringPerc$key
) ";
$messagePart .= "$operation \"" . self::cleaned($string) . "\" ";
$messagePart .= "in $type ";
$messagePart .= "using filter \"" . self::readableFilter($value) . "\" ";
//remember what value has to be bound to what placeholder
$bindValues[$type][$key] = array(
'percStringPerc' . $key => '%' . $string . '%'
);
break;
case 'Subject Area is':
$joinsInd .= " LEFT JOIN indiv_meta im ON im.indiv_id = i.id_ind ";
$whereInd .= ' ' . $operationInd . " (
im.meta_option = 'researcharea'
AND im.meta_value LIKE :percStringPerc$key
) ";
$messagePart .= "$operation \"" . self::cleaned($string) . "\" ";
$messagePart .= "in $type ";
$messagePart .= "using filter \"" . self::readableFilter($value) . "\" ";
//remember what value has to be bound to what placeholder
$bindValues[$type][$key] = array(
'percStringPerc' . $key => '%' . $string . '%'
);
break;
case 'Activities include':
$whereInd .= ' ' . $operationInd . " (
i.activeng LIKE :percStringPerc$key
) ";
$messagePart .= "$operation \"" . self::cleaned($string) . "\" ";
$messagePart .= "in $type ";
$messagePart .= "using filter \"" . self::readableFilter($value) . "\" ";
//remember what value has to be bound to what placeholder
$bindValues[$type][$key] = array(
'percStringPerc' . $key => '%' . $string . '%'
);
break;
case 'Citations include':
$joinsInd .= " LEFT JOIN indiv_meta im ON im.indiv_id = i.id_ind ";
$whereInd .= ' ' . $operationInd . " (
(
im.meta_option = 'researcharea'
AND im.meta_value LIKE :percStringPerc$key
)
OR (
im.meta_option = 'other-citation'
AND im.meta_value LIKE :percStringPerc$key
)
OR (
im.meta_option = 'researchgate'
AND im.meta_value LIKE :percStringPerc$key
)
) ";
$messagePart .= "$operation \"" . self::cleaned($string) . "\" ";
$messagePart .= "in $type ";
$messagePart .= "using filter \"" . self::readableFilter($value) . "\" ";
//remember what value has to be bound to what placeholder
$bindValues[$type][$key] = array(
'percStringPerc' . $key => '%' . $string . '%'
);
break;
case 'Working languages includes':
$whereInd .= ' ' . $operationInd . " (
FIND_IN_SET ('$string', i.languages)
) ";
$messagePart .= "$operation \"" . self::cleaned($string) . "\" ";
$messagePart .= "in $type ";
$messagePart .= "using filter \"" . self::readableFilter($value) . "\" ";
break;
case 'Degree contains':
$whereInd .= ' ' . $operationInd . " (
i.degree LIKE :percStringPerc$key
) ";
$messagePart .= "$operation \"" . self::cleaned($string) . "\" ";
$messagePart .= "in $type ";
$messagePart .= "using filter \"" . self::readableFilter($value) . "\" ";
//remember what value has to be bound to what placeholder
$bindValues[$type][$key] = array(
'percStringPerc' . $key => '%' . $string . '%'
);
break;
case 'Is retired':
$whereInd .= ' ' . $operationInd . " (
i.retired = $string
) ";
$messagePart .= "$operation experts who are \"Retired\" ";
break;
case 'Is deceased':
$whereInd .= ' ' . $operationInd . " (
i.deceased = $string
) ";
$messagePart .= "$operation experts who are \"Deceased\" ";
break;
case 'Is quality checked':
$whereInd .= ' ' . $operationInd . " (
i.quality_checked = $string
) ";
$messagePart .= "$operation experts who are \"Quality checked\" ";
break;
case 'Comment contains':
$whereInd .= ' ' . $operationInd . " (
i.comments LIKE :percStringPerc$key
) ";
$messagePart .= "$operation \"" . self::cleaned($string) . "\" ";
$messagePart .= "in $type ";
$messagePart .= "using filter \"" . self::readableFilter($value) . "\" ";
//remember what value has to be bound to what placeholder
$bindValues[$type][$key] = array(
'percStringPerc' . $key => '%' . $string . '%'
);
break;
case 'Is Active':
$whereInd .= ' ' . $operationInd . " (
i.status = $string
) ";
if ($string == '1') {
$messagePart .= "$operation expert is Active ";
} else {
$messagePart .= "$operation expert is not active ";
}
break;
case 'Gender is':
// #734 we have different values for this 'gender' filter
// 'male' and 'female' are simple
// but we have 'na', 'other' and '' in the database
// '' and 'na' mean 'prefer not to say'
// 'other' means 'other'
$whereInd .= ' ' . $operationInd . " (
i.gender = '$string' ";
if ($string == 'na') {
$whereInd .= ' OR i.gender = "" ';
$string = 'Prefer not to say';
} else {
$string = ucfirst($string);
}
$whereInd .= ' ) ';
$messagePart .= "$operation $type \"" . self::readableFilter($value) . "\" \"$string\" ";
break;
case 'Do Not Invite':
$whereInd .= ' ' . $operationInd . " (
i.do_not_invite = 1
) ";
$messagePart .= "$operation $type using filter \"" . self::readableFilter($value) . "\" ";
break;
case 'Unique Identifier':
$whereInd .= ' ' . $operationInd . " (
i.id_ind = '$string'
) ";
$messagePart .= "$operation $type having unique identifier \"$string\" ";
break;
case 'Updated':
$startDate = isset($searchParams['startDate'][$key]) ? "l_date_upd >= '" . self::cleanDate($searchParams['startDate'][$key]) . "' " : "1=1 ";
$endDate = isset($searchParams['endDate'][$key]) ? "and l_date_upd <= '" . self::cleanDate($searchParams['endDate'][$key]) . "' " : " AND 1=1 ";
$whereInd .= ' ' . $operationInd . ' ' . $startDate . ' ' . $endDate;
$messagePart .= "$operation $type updated ";
$messagePart .= "between \"" . self::cleaned($searchParams['startDate'][$key]) . "\" ";
$messagePart .= "and \"" . self::cleaned($searchParams['endDate'][$key]) . "\" ";
break;
case 'Created':
$startDate = isset($searchParams['startDate'][$key]) ? "f_date_ent >= '" . self::cleanDate($searchParams['startDate'][$key]) . "' " : "1=1 ";
$endDate = isset($searchParams['endDate'][$key]) ? "and f_date_ent <= '" . self::cleanDate($searchParams['endDate'][$key]) . "' " : " AND 1=1 ";
$whereInd .= ' ' . $operationInd . ' ' . $startDate . ' ' . $endDate;
$messagePart .= "$operation $type \"" . self::readableFilter($value) . "\" ";
$messagePart .= "between \"" . self::cleaned($searchParams['startDate'][$key]) . "\" ";
$messagePart .= "and \"" . self::cleaned($searchParams['endDate'][$key]) . "\" ";
break;
case 'Quality last checked':
$startDate = isset($searchParams['startDate'][$key]) ? "quality_checked_date > '" . self::cleanDate($searchParams['startDate'][$key]) . "' " : "1=1 ";
$endDate = isset($searchParams['endDate'][$key]) ? "and quality_checked_date < '" . self::cleanDate($searchParams['endDate'][$key]) . "' " : " AND 1=1 ";
$whereInd .= ' ' . $operationInd . ' ' . $startDate . ' ' . $endDate;
$messagePart .= "$operation $type \"" . self::readableFilter($value) . "\" ";
$messagePart .= "between \"" . self::cleaned($searchParams['startDate'][$key]) . "\" ";
$messagePart .= "and \"" . self::cleaned($searchParams['endDate'][$key]) . "\" ";
break;
default:
break;
}
$operationInd = $searchParams['searchToggle'][$key] ?? " OR ";
$operation = $searchParams['searchToggle'][$key] ?? " OR ";
} elseif ($type == 'institutions') {
if ($sortBy == '') {
$sortBy = 'name';
}
$bindValues[$type][$key] = array();
$selectQryInst = "
SELECT
'institutions' AS type,
i.activated,
i.id_inst,
it.insttype_name AS instType,
i.inst_name,
i.inst_name_eng,
i.inst_address,
i.addr_2,
i.city AS instCity,
i.state AS instState,
i.postcode AS instPostCode,
c.country,
i.activities,
i.inst_logo,
i.parent_id,
'' AS jobtitle,
'' AS eventtype,
'' AS fname,
'' AS sname
FROM
institutions i
";
$joinsInst = ' LEFT JOIN countries c ON c.id_country = i.country_code
LEFT JOIN insttypes it ON it.id_insttype = i.inst_type_id ';
switch ($value) {
case 'Name contains':
$whereInst .= ' ' . $operationInst . " (
i.inst_name LIKE :percStringPerc$key
OR i.inst_name_eng LIKE :percStringPerc$key
OR i.acronym LIKE :percStringPerc$key
) ";
$messagePart .= "$operation \"" . self::cleaned($string) . "\" ";
$messagePart .= "in $type ";
$messagePart .= "using filter \"" . self::readableFilter($value) . "\" ";
//remember what value has to be bound to what placeholder
$bindValues[$type][$key] = array(
'percStringPerc' . $key => '%' . $string . '%'
);
break;
/**
* does not exist
*
case 'Worklocation contains':
$whereInst .= ' ' . $operationInst . " (
i.inst_address LIKE :percStringPerc$key
OR i.addr_2 LIKE :percStringPerc$key
OR i.city LIKE :percStringPerc$key
OR i.state LIKE :percStringPerc$key
) ";
$messagePart .= "$operation \"" . self::cleaned($string) . "\" ";
$messagePart .= "in $type ";
$messagePart .= "using filter \"" . self::readableFilter($value) . "\" ";
//remember what value has to be bound to what placeholder
$bindValues[$type][$key] = array(
'percStringPerc' . $key => '%' . $string . '%'
);
break;
*/
/*
* #680
*
case 'Current/Previous addresses contain':
$whereInst .= ' ' . $operationInst . " (
i.inst_address LIKE :percString$key
OR i.addr_2 LIKE :percStringPerc$key
OR i.city LIKE :percStringPerc$key
OR i.state LIKE :percStringPerc$key
) ";
$messagePart .= "$operation \"" . self::cleaned($string) . "\" ";
$messagePart .= "in $type ";
$messagePart .= "using filter \"" . self::readableFilter($value) . "\" ";
//remember what value has to be bound to what placeholder
$bindValues[$type][$key] = array(
'percString' . $key => '%' . $string,
'percStringPerc' . $key => '%' . $string . '%'
);
break;
*/
case 'Country is':
$whereInst .= ' ' . $operationInst . " (
i.country_code = $string
) ";
$messagePart .= "$operation $type ";
$messagePart .= "from \"" . self::getCountryById($string, $doctrine) . "\" ";
break;
case 'Country in region':
$idRegion = $string;
//reset $string, will be an array
$string = array();
//get the countries in that region
//and create an array with the ids of the countries
$countriesInRegion = self::getCountriesInRegion($idRegion, $doctrine);
foreach ($countriesInRegion as $country) {
$string[] = $country->getIdCountry();
}
$string = implode(',', $string);
//this is identical to 'Worklocation in countries'
$whereInst .= ' ' . $operationInd . " (
i.country_code in ($string)
) ";
$messagePart .= "$operation $type from the region ";
$messagePart .= '"' . self::getRegionById($idRegion, $doctrine) . '" ';
$messagePart .= ' (' . implode(', ', self::getMultipleCountriesById($string, $doctrine)) . ') ';
break;
case 'Sea regions of study is':
$whereInst .= ' ' . $operationInst . " (
FIND_IN_SET ('$string', i.inst_region)
) ";
$messagePart .= "$operation $type ";
$messagePart .= "having $value \"" . self::getSeaRegionById($string, $doctrine) . "\" ";
break;
case 'Type is':
$whereInst .= ' ' . $operationInst . " (
i.inst_type_id = $string
) ";
$messagePart .= "$operation $type having " . self::readableFilter($value) . " \"";
$messagePart .= SearchController::getInstituteTypeById($string, $doctrine) . "\"";
break;
case 'Activities contains':
$whereInst .= ' ' . $operationInst . " (
i.activities LIKE :percStringPerc$key
) ";
$messagePart .= "$operation \"" . self::cleaned($string) . "\" ";
$messagePart .= "in $type ";
$messagePart .= "using filter \"" . self::readableFilter($value) . "\" ";
//remember what value has to be bound to what placeholder
$bindValues[$type][$key] = array(
'percStringPerc' . $key => '%' . $string . '%'
);
break;
case 'Tel/Fax contains':
$whereInst .= ' ' . $operationInst . " (
i.inst_tel LIKE :percStringPerc$key
OR i.inst_fax LIKE :percStringPerc$key
) ";
$messagePart .= "$operation \"" . self::cleaned($string) . "\" ";
$messagePart .= "in $type ";
$messagePart .= "using filter \"" . self::readableFilter($value) . "\" ";
//remember what value has to be bound to what placeholder
$bindValues[$type][$key] = array(
'percStringPerc' . $key => '%' . $string . '%'
);
break;
case 'Website URL contains':
$whereInst .= ' ' . $operationInst . " (
i.inst_url LIKE :percStringPerc$key
) ";
$messagePart .= "$operation \"<strong" . self::cleaned($string) . "\" ";
$messagePart .= "in $type ";
$messagePart .= "using filter \"" . self::readableFilter($value) . "\" ";
//remember what value has to be bound to what placeholder
$bindValues[$type][$key] = array(
'percStringPerc' . $key => '%' . $string . '%'
);
break;
case 'EDMO Code is':
$whereInst .= ' ' . $operationInst . " (
i.edmo_code = '$string'
AND edmo_code != 0
) ";
$messagePart .= "$operation \"" . self::cleaned($string) . "\" ";
$messagePart .= "in $type ";
$messagePart .= "using filter \"" . self::readableFilter($value) . "\" ";
break;
case 'Updated':
$startDate = isset($searchParams['startDate'][$key]) ? "l_date_updated >= '" . self::cleanDate($searchParams['startDate'][$key]) . "' " : "1=1 ";
$endDate = isset($searchParams['endDate'][$key]) ? "AND l_date_updated <= '" . self::cleanDate($searchParams['endDate'][$key]) . "' " : " AND 1=1 ";
$whereInst .= ' ' . $operationInst . ' ' . $startDate . ' ' . $endDate;
$messagePart .= "$operation $type \"" . self::readableFilter($value) . "\" ";
$messagePart .= "between \"" . self::cleaned($searchParams['startDate'][$key]) . "\" ";
$messagePart .= "and \"" . self::cleaned($searchParams['endDate'][$key]) . "\" ";
break;
case 'Created':
$startDate = isset($searchParams['startDate'][$key]) ? "f_date_entered >= '" . self::cleanDate($searchParams['startDate'][$key]) . "' " : "1=1 ";
$endDate = isset($searchParams['endDate'][$key]) ? "AND f_date_entered <= '" . self::cleanDate($searchParams['endDate'][$key]) . "' " : " AND 1=1 ";
$whereInst .= ' ' . $operationInst . ' ' . $startDate . ' ' . $endDate;
$messagePart .= "$operation $type \"" . self::readableFilter($value) . "\" ";
$messagePart .= "between \"" . self::cleaned($searchParams['startDate'][$key]) . "\" ";
$messagePart .= "and \"" . self::cleaned($searchParams['endDate'][$key]) . "\" ";
break;
}
$instCondCnt++;
$operationInst = $searchParams['searchToggle'][$key] ?? " OR ";
$operation = $searchParams['searchToggle'][$key] ?? " OR ";
} elseif ($type == 'all') {
$newString = self::fulltext($string);
$stmtInd = $connection->prepare("
SELECT
'experts' AS type,
i.status,
i.id_ind,
concat(i.fname,' ',i.sname) AS name,
i.fname,
i.mname,
i.sname,
inst_name,
jobtitle,
i.addr_1,
i.addr_2,
i.city,
i.state,
i.postcode,
inst.inst_address,
inst.addr_2 AS instAddr2,
inst.city AS instCity,
inst.state AS instState,
inst.postcode AS instPostCode,
use_inst_addr,
deceased,
retired,
quality_checked,
(IF(use_inst_addr = 1, ic.country, c.country)) AS country,
MATCH (fname, sname) AGAINST (:newString IN BOOLEAN MODE) AS relevance,
'zz' AS instType,
'zz' AS eventtype,
'' AS inst_logo
FROM
indiv i
LEFT JOIN countries c ON c.id_country = i.country_code
LEFT JOIN indiv_institution ii ON ii.id_ind = i.id_ind
LEFT JOIN institutions inst ON inst.id_inst = ii.id_inst
LEFT JOIN countries ic ON ic.id_country = inst.country_code
WHERE
(
MATCH (fname, mname, sname) AGAINST (:newString IN BOOLEAN MODE)
OR fname LIKE :string
OR fname LIKE :stringPerc
OR fname LIKE :percString
OR mname LIKE :stringPerc
OR mname LIKE :percString
OR sname LIKE :stringPerc
OR sname LIKE :percString
)
" . $searchParams['indivStatus'] . '
ORDER BY relevance DESC, i.sname ASC;
');
$stmtInd->bindValue('newString', $newString);
$stmtInd->bindValue('string', $string);
$stmtInd->bindValue('percString', '%' . $string);
$stmtInd->bindValue('stringPerc', $string . '%');
$stmtInd->bindValue('percStringPerc', '%' . $string . '%');
$stmtInd->execute();
$dataResultInd = $stmtInd->fetchAll();
$sortFields['jobtitle'] = 'Position';
$stmtInst = $connection->prepare("
SELECT
'institutions' AS type,
i.activated,
i.id_inst,
insttype_name AS instType,
inst_name,
inst_name_eng,
inst_address,
addr_2,
city AS instCity,
state AS instState,
postcode AS instPostCode,
country,
activities,
inst_logo,
'zz' AS jobtitle,
MATCH (inst_name,inst_name_eng,acronym) AGAINST (:newString) AS relevance,
'zz' AS eventtype,
'zz' AS fname,
'zz' AS sname
FROM
institutions i
LEFT JOIN countries c ON c.id_country = i.country_code
LEFT JOIN insttypes it ON it.id_insttype = i.inst_type_id
WHERE
(
MATCH (inst_name, inst_name_eng, acronym) AGAINST (:newString IN BOOLEAN MODE)
OR inst_name LIKE :percStringPerc
OR inst_name_eng LIKE :percStringPerc
OR acronym LIKE :percStringPerc
OR activities LIKE :percStringPerc
)
" . $searchParams['instStatus'] . '
ORDER BY relevance DESC, i.inst_name ASC;
');
$stmtInst->bindValue('newString', $newString);
$stmtInst->bindValue('percStringPerc', '%' . $string . '%');
$stmtInst->execute();
$dataResultInst = $stmtInst->fetchAll();
$sortFields['instType'] = 'Institution Type';
/**
* Will be taken care when PPC
*/
$stmtEvent = $connection->prepare("
SELECT
'events' AS type,
et.eventtype_name AS eventtype,
e.status AS activated,
e.id_event,
e.title AS name,
e.start_on,
e.end_on,
e.address AS addr_1,
e.city,
e.state,
c.country AS country,
'zz' AS jobtitle,
'zz' AS instType,
'zz' AS fname,
'zz' AS sname,
'zz' AS inst_name
FROM
events e
LEFT JOIN countries c on c.id_country = e.id_country
LEFT JOIN eventtypes et on et.id_eventtype = e.id_eventtype
WHERE
(
title LIKE :percStringPerc
OR shorttitle LIKE :percStringPerc
OR summary LIKE :percStringPerc
OR keywords LIKE :percStringPerc
)
" . $searchParams['eventStatus'] . '
ORDER BY start_on desc
');
$stmtEvent->bindValue('percStringPerc', '%' . $string . '%');
$stmtEvent->execute();
$dataResultEvent = $stmtEvent->fetchAll();
$messagePart .= ' "' . self::cleaned($string) . '" ';
} elseif ($type == 'events') {
$sortFields['name'] = 'Event Title';
if ($sortBy == '') {
$sortBy = 'name';
}
$bindValues[$type][$key] = array();
$selectQryEvent = "
SELECT
'events' AS type,
et.eventtype_name AS eventtype,
e.status AS activated,
e.id_event,
e.title AS name,
e.start_on,
e.end_on,
e.address AS addr_1,
e.city,
e.state,
c.country AS country,
'zz' AS jobtitle,
'zz' AS instType,
'zz' AS fname,
'zz' AS sname
FROM
events e ";
$joinsEvent = ' LEFT JOIN countries c ON c.id_country = e.id_country
LEFT JOIN eventtypes et ON et.id_eventtype = e.id_eventtype ';
switch ($value) {
case 'Title contains':
$whereEvent .= ' ' . $operationEvent . " (
e.title LIKE :percStringPerc$key
) ";
$messagePart .= "$operation \"" . self::cleaned($string) . "\" ";
$messagePart .= "in $type ";
$messagePart .= "using filter \"" . self::readableFilter($value) . "\" ";
//remember what value has to be bound to what placeholder
$bindValues[$type][$key] = array(
'percStringPerc' . $key => '%' . $string . '%'
);
break;
case 'eType is':
$whereEvent .= ' ' . $operationEvent . " (
e.id_eventtype = $string
) ";
$messagePart .= "$operation $type ";
$messagePart .= "having Event Type \"" . SearchController::getInstituteTypeById($string, $doctrine) . "\" ";
break;
case 'Summary contains':
$whereEvent .= ' ' . $operationEvent . " (
e.summary LIKE :percStringPerc$key
) ";
$messagePart .= "$operation \"" . self::cleaned($string) . "\" ";
$messagePart .= "in $type using filter \"" . self::readableFilter($value) . "\" ";
//remember what value has to be bound to what placeholder
$bindValues[$type][$key] = array(
'percStringPerc' . $key => '%' . $string . '%'
);
break;
case 'Keywords contain':
$whereEvent .= ' ' . $operationEvent . " (
e.keywords LIKE :percStringPerc$key
) ";
$messagePart .= "$operation \"" . self::cleaned($string) . "\" ";
$messagePart .= "in $type using filter \"" . self::readableFilter($value) . "\" ";
//remember what value has to be bound to what placeholder
$bindValues[$type][$key] = array(
'percStringPerc' . $key => '%' . $string . '%'
);
break;
case 'Address contains':
$whereEvent .= ' ' . $operationEvent . " (
e.address LIKE :percStringPerc$key
OR e.city LIKE :percStringPerc$key
OR e.state LIKE :percStringPerc$key
) ";
$messagePart .= "$operation \"" . self::cleaned($string) . "\" ";
$messagePart .= "in $type using filter \"" . self::readableFilter($value) . "\" ";
//remember what value has to be bound to what placeholder
$bindValues[$type][$key] = array(
'percStringPerc' . $key => '%' . $string . '%'
);
break;
case 'Country is':
dump($string);
$whereEvent .= ' ' . $operationEvent . " (
e.id_country = $string
) ";
$messagePart .= "$operation $type ";
$messagePart .= "from \"" . self::getCountryById($string, $doctrine) . "\" ";
break;
case 'Country in region':
$idRegion = $string;
//reset $string, will be an array
$string = array();
//get the countries in that region
//and create an array with the ids of the countries
$countriesInRegion = self::getCountriesInRegion($idRegion, $doctrine);
foreach ($countriesInRegion as $country) {
$string[] = $country->getIdCountry();
}
$string = implode(',', $string);
$whereEvent .= ' ' . $operationInd . " (
e.id_country in ($string)
) ";
$messagePart .= "$operation $type from the region ";
$messagePart .= '"' . self::getRegionById($idRegion, $doctrine) . '" ';
$messagePart .= ' (' . implode(', ', self::getMultipleCountriesById($string, $doctrine)) . ') ';
break;
case 'Date between':
$date = '';
$messagePart .= "$operation $type with parameters '" . self::readableFilter($value) . "': ";
if (isset($searchParams['startDate'][$key])
&& $searchParams['startDate'][$key] != ''
&& isset($searchParams['endDate'][$key])
&& $searchParams['endDate'][$key] != ''
) {
$startDate = self::cleanDate($searchParams['startDate'][$key]);
$endDate = self::cleanDate($searchParams['endDate'][$key]);
$date = " start_on >= '$startDate'
AND end_on <= '$endDate' ";
$messagePart .= "between '$startDate' ";
$messagePart .= "and '$endDate' ";
} elseif (isset($searchParams['startDate'][$key])
&& (!$searchParams['endDate'][$key]
|| $searchParams['endDate'][$key] == '')
) {
$startDate = self::cleanDate($searchParams['startDate'][$key]);
$date = " start_on >= '$startDate' ";
$messagePart .= " -> after '$startDate' ";
} elseif (isset($searchParams['endDate'][$key])
&& (!$searchParams['startDate'][$key]
|| $searchParams['startDate'][$key] == '')
) {
$endDate = self::cleanDate($searchParams['endDate'][$key]);
$date = " end_on <= '$endDate' ";
$messagePart .= " -> before '$endDate' ";
} else {
$whereEvent .= '';
}
$whereEvent .= ' ' . $operationInst . ' ' . $date;
break;
case 'Updated':
$endDate = isset($searchParams['endDate'][$key]) ? "AND updated_at <= '" . self::cleanDate($searchParams['endDate'][$key]) . "' " : " AND 1=1 ";
$whereEvent .= ' ' . $operationInst . ' ' . self::cleaned($searchParams['startDate'][$key]) . ' ' . $endDate;
$messagePart .= "$operation $type \"" . self::readableFilter($value) . "\" ";
$messagePart .= "between \"" . self::cleaned($searchParams['startDate'][$key]) . "\" ";
$messagePart .= "and \"" . self::cleaned($searchParams['endDate'][$key]) . "\" ";
break;
case 'Created':
$startDate = isset($searchParams['startDate'][$key]) ? "created_at >= '" . self::cleanDate($searchParams['startDate'][$key]) . "' " : "1=1 ";
$endDate = isset($searchParams['endDate'][$key]) ? "AND created_at <= '" . self::cleanDate($searchParams['endDate'][$key]) . "' " : " AND 1=1 ";
$whereEvent .= ' ' . $operationInst . ' ' . $startDate . ' ' . $endDate;
$messagePart .= "$operation $type \"" . self::readableFilter($value) . "\" ";
$messagePart .= "between \"" . self::cleaned($searchParams['startDate'][$key]) . "\" ";
$messagePart .= "and \"" . self::cleaned($searchParams['endDate'][$key]) . "\" ";
break;
}
$operationEvent = $searchParams['searchToggle'][$key] ?? " OR ";
} elseif ($type == 'documents') {
$sortFields['name'] = 'Document Title';
if ($sortBy == '') {
$sortBy = 'title';
}
$bindValues[$type][$key] = array();
$selectQryDocument = "
SELECT
'documents' AS type,
d.id_doc,
dt.id_doctype AS id_doctype,
dt.doctypename AS documenttype,
d.title,
d.created_at,
d.doc_code,
'undefined' AS country
FROM
documents d,
doctypes dt
WHERE
dt.id_doctype = d.id_doctype ";
switch ($value) {
case 'Title contains':
$whereDocument .= ' ' . $operationEvent . " (
d.title LIKE :percStringPerc$key
) ";
$messagePart .= "$operation \"" . self::cleaned($string) . "\" ";
$messagePart .= "in $type using filter \"" . self::readableFilter($value) . "\" ";
//remember what value has to be bound to what placeholder
$bindValues[$type][$key] = array(
'percStringPerc' . $key => '%' . $string . '%'
);
break;
case 'Document type is':
$whereDocument .= ' ' . $operationEvent . " (
dt.id_doctype = :idDoctype
) ";
$messagePart .= "$operation $type having Document Type ";
$messagePart .= "\" " . self::readableFilter($value) . "\" ";
//remember what value has to be bound to what placeholder
$bindValues[$type][$key] = array(
'idDoctype' => $string
);
break;
case 'Document code is':
$whereDocument .= ' ' . $operationEvent . " (
d.doc_code LIKE :percStringPerc$key
) ";
$messagePart .= "$operation $type having Document Code ";
$messagePart .= "\" " . self::readableFilter($value) . "\" ";
//remember what value has to be bound to what placeholder
$bindValues[$type][$key] = array(
'percStringPerc' . $key => '%' . $string . '%'
);
break;
case 'Summary contains':
$whereDocument .= ' ' . $operationEvent . " (
d.summary LIKE :percStringPerc$key
) ";
$messagePart .= "$operation \"" . self::cleaned($string) . "\" ";
$messagePart .= "in $type using filter \"" . self::readableFilter($value) . "\" ";
//remember what value has to be bound to what placeholder
$bindValues[$type][$key] = array(
'percStringPerc' . $key => '%' . $string . '%'
);
break;
case 'Keywords contain':
$whereDocument .= ' ' . $operationEvent . " (
d.keywords LIKE :percStringPerc$key
) ";
$messagePart .= "$operation \"" . self::cleaned($string) . "\" ";
$messagePart .= "in $type using filter \"" . self::readableFilter($value) . "\" ";
//remember what value has to be bound to what placeholder
$bindValues[$type][$key] = array(
'percStringPerc' . $key => '%' . $string . '%'
);
break;
case 'Updated':
if (isset($searchParams['endDate'][$key])) {
$endDate = self::cleanDate($searchParams['endDate'][$key]);
$endDate = "AND updated_at <= '$endDate' ";
} else {
$endDate = ' AND 1=1 ';
}
$whereDocument .= ' ' . $operationInst . ' ' . self::cleaned($searchParams['startDate'][$key]) . ' ' . $endDate;
$messagePart .= "$operation $type \"" . self::readableFilter($value) . "\" ";
$messagePart .= "between \"" . self::cleaned($searchParams['startDate'][$key]) . "\" ";
$messagePart .= "and \"" . self::cleaned($searchParams['endDate'][$key]) . "\" ";
break;
case 'Created':
if (isset($searchParams['startDate'][$key])) {
$startDate = self::cleanDate($searchParams['startDate'][$key]);
$startDate = "created_at >= '$startDate' ";
} else {
$startDate = '1=1 ';
}
if (isset($searchParams['endDate'][$key])) {
$endDate = self::cleanDate($searchParams['endDate'][$key]);
$endDate = "AND created_at <= '$endDate' ";
} else {
$endDate = ' AND 1=1 ';
}
$whereDocument .= ' ' . $operationInst . ' ' . $startDate . ' ' . $endDate;
$messagePart .= "$operation $type \"" . self::readableFilter($value) . "\" ";
$messagePart .= "between \"" . self::cleaned($searchParams['startDate'][$key]) . "\" ";
$messagePart .= "and \"" . self::cleaned($searchParams['endDate'][$key]) . "\" ";
break;
}
$operationEvent = $searchParams['searchToggle'][$key] ?? " OR ";
}
}
if (isset($selectQryInd)) {
$selectQryInd .= " FROM indiv i ";
if ($endQueryInd == false) {
$whereInd .= ' ) ' . $searchParams['indivStatus'] . 'GROUP BY i.id_ind';
}
$queryInd = $selectQryInd . ' ' . $commonJoins . ' ' . $joinsInd . ' ' . $whereInd . ' ' . $matchOrder;
$stmtInd = $connection->prepare($queryInd);
foreach($bindValues['experts'] as $bindValue) {
foreach($bindValue as $placeholder => $value) {
$stmtInd->bindValue($placeholder, $value);
}
}
$stmtInd->execute();
$dataResultInd = $stmtInd->fetchAll();
$sortFields['jobtitle'] = 'Position';
}
if (isset($selectQryInst)) {
if ($endQueryInst == false) {
$whereInst .= " ) " . $searchParams['instStatus'] . " GROUP BY i.id_inst ";
}
$queryInst = $selectQryInst . ' ' . $joinsInst . ' ' . $whereInst . " ORDER BY i.inst_name ASC";
$stmtInst = $connection->prepare($queryInst);
foreach($bindValues['institutions'] as $bindValue) {
foreach($bindValue as $placeholder => $value) {
$stmtInst->bindValue($placeholder, $value);
}
}
$stmtInst->execute();
$dataResultInst = $stmtInst->fetchAll();
$sortFields["instType"] = "Institution Type";
}
if (isset($selectQryEvent)) {
if ($endQueryEvent == false) {
$whereEvent .= ' ) ' . $searchParams['eventStatus'];
$whereEvent.= ' GROUP BY e.id_event ORDER BY start_on DESC ';
}
$queryEvent = $selectQryEvent . ' ' . $joinsEvent . ' ' . $whereEvent;
$stmtEvent = $connection->prepare($queryEvent);
foreach($bindValues['events'] as $bindValue) {
foreach($bindValue as $placeholder => $value) {
$stmtEvent->bindValue($placeholder, $value);
}
}
$stmtEvent->execute();
$dataResultEvent = $stmtEvent->fetchAll();
}
if (isset($selectQryDocument)) {
if ($endQueryDocument == false) {
$whereDocument .= ' ORDER BY created_at DESC';
}
$queryDocument = $selectQryDocument . ' ' . $whereDocument;
$stmtDocument = $connection->prepare($queryDocument);
foreach($bindValues['documents'] as $bindValue) {
foreach($bindValue as $placeholder => $value) {
$stmtDocument->bindValue($placeholder, $value);
}
}
$stmtDocument->execute();
$dataResultDocument = $stmtDocument->fetchAll();
}
$dataResult = array_merge(
$dataResultInd,
$dataResultInst,
$dataResultEvent,
$dataResultDocument
);
}
if ($messagePart == '') {
$message = 'no data found at all';
} else {
$messageEnd = 'returned ' . count($dataResult) . ' result(s).';
$message = $messageStart . $messagePart . $messageEnd;
}
//status 0 means there are no errors to be mentioned
$return = array(
'status' => 0,
'dataResult' => $dataResult,
'sortFields' => $sortFields,
'sortby' => $sortBy,
'message' => $message,
'searchResultType' => $searchResultType
);
return $return;
}
/**
* show the results of the advanced search
*
* @param Request $request
*
* @return Response
*/
public function advancedSearchAction(Request $request): Response
{
//if we do a 'browse' search (from the homepage)
//AND we try to filter the results (this can be the case after searching for a word in 'all')
//we should redirect this to a more complex = advanced search type
$searchParams = $this::extractParams($request);
//dump($searchParams);
//we want to see inactive/unapproved/... results as a global editor
//normal users should not see that
if ($this->get('security.authorization_checker')->isGranted('ROLE_GLOBAL_EDITOR')) {
$indivStatus = ' ';
$instStatus = ' ';
$eventStatus = ' ';
} else {
$indivStatus = ' AND status = 1 ';
$instStatus = ' AND activated = 1 ';
$eventStatus = ' AND status = 1 ';
}
$searchParams['indivStatus'] = $indivStatus;
$searchParams['instStatus'] = $instStatus;
$searchParams['eventStatus'] = $eventStatus;
//remember the filters that we have applied
//used to add action to the filter labels shown above the results
$appliedFilters = array();
$appliedCountryFilters = array();
$doctrine = $this->get('doctrine');
//get the results depending on the defined action
if ($searchParams['action'] == 'browse') {
$results = self::getBrowseSearchResults($searchParams, $doctrine);
} elseif ($searchParams['action'] == 'advSearch') {
$results = self::getAdvSearchResults($searchParams, $doctrine);
} else {
$results = array(
'status' => 2,
'message' => 'we need at least the parameter "action" (browse or advSearch)'
);
}
//this means we have an error somewhere, or we have no results (status == 4)
if ($results['status']
&& $results['status'] != 3
) {
return $this->render(
'Exception/error.html.twig',
array(
'message' => $results['message']
)
);
}
$dataResult = $results['dataResult'];
$searchResultType = $results['searchResultType'];
//only keep the results that match one of the filtertypes (experts, institutions,....) we are asking
if (isset($searchParams['searchFilterType'])
&& is_array($searchParams['searchFilterType'])
) {
$filterData = array();
foreach (array_unique($searchParams['searchFilterType']) as $type) {
$filterData = array_merge(
$filterData,
$this->addFilter(
$type,
'type',
$dataResult
)
);
$appliedFilters[] = $type;
}
$dataResult = $filterData;
}
//only show the results for the country we are filtering
if (isset($searchParams['filterCountry'])
&& trim($searchParams['filterCountry']) != ''
) {
$filterData = array();
foreach (explode(',', $searchParams['filterCountry']) as $countryValue) {
if (trim($countryValue) != '') {
$filterData = array_merge(
$filterData,
$this->addFilter(
$countryValue,
'country',
$dataResult
)
);
$appliedCountryFilters[] = $countryValue;
}
}
$dataResult = $filterData;
}
//if sortby is defined other than relevance, then manually sort by name;
if (isset($results['sortby'])
&& $results['sortby'] != 'relevance'
) {
$this->sortBy(
$results['sortby'],
$dataResult,
$searchParams['sortOrder']
);
} else {
$results['sortby'] = 'relevance';
}
$data = $this->get('knp_paginator')
->paginate(
$dataResult,
$request->query->getInt('page', 1),
$searchParams['limit'],
array(
'pageParameterName' => 'page',
'sortDirectionParameterName' => 'dir'
)
);
//gather all the email address already
//could be done at the receiving end also...
//or are we the receiving end already?
$emailusers = array();
foreach ($dataResult as $expert) {
if ($expert['type'] == 'experts') {
$emailusers[] = $expert['id_ind'];
}
}
//who is making this query
//this is used for searches where we want to send emails to the resulting experts
$user = self::getDoctrine()
->getRepository('OceanExpertBundle:Indiv')
->findOneBy(
array(
'idInd' => $this->get('security.token_storage')->getToken()->getUser()
)
);
//show what we have
return $this->render(
'Search/advancedSearch.html.twig',
array(
'action' => $searchParams['action'],
'data' => $data,
'filters' => self::getFilters($dataResult),
'searchResultText' => $results['message'],
'appliedFilters' => $appliedFilters,
'appliedCountryFilters' => $appliedCountryFilters,
'searchResultType' => $searchResultType,
'searchConditions' => $searchParams['conditions'],
'sortfields' => $results['sortFields'],
'user' => $user,
'emailUsers' => $emailusers,
'sortby' => $results['sortby']
)
);
}
public static function sortBy($field, &$array, $direction = 'ASC'): bool
{
usort($array, function($a, $b) use ($field, $direction) {
$a = $a[$field] ?? '';
$b = $b[$field] ?? '';
if ($a == $b) {
return 0;
}
if (strtolower($direction) == 'desc') {
return $a > $b ? -1 : 1;
} else {
return $a < $b ? -1 : 1;
}
});
return true;
}
/**
* @param $dataArr
* @param $type
* @return array
*
*/
public static function getUnique($dataArr, $type): array
{
SearchController::sortBy($type, $dataArr, 'ASC');
$fields = array();
foreach ($dataArr as $value) {
if (isset($value[$type])
&& trim($value[$type]) != ''
) {
if (array_key_exists($value[$type], $fields)) {
$fields[$value[$type]] += 1;
} else {
$fields[$value[$type]] = 1;
}
}
}
ksort($fields);
return $fields;
}
public static function addFilter($filter, $filterKey, $dataArr): array
{
$filterData = array();
foreach ($dataArr as $key => $value) {
if ($filter == 'all'
|| (isset($value[$filterKey])
&& $value[$filterKey] == $filter)
) {
$filterData[$key] = $value;
}
}
return $filterData;
}
/**
* get the subject area name by its id
*
* @param int $id id of the subject area
* @param object $doctrine
*
* @return string
*/
public static function getSubjectAreaById(int $id, object $doctrine): string
{
$subjectArea = $doctrine
->getRepository('OceanExpertBundle:Subjects')
->findOneBy(array('idSub' => $id));
return $subjectArea->getSubname();
}
/**
* get the name of the sea region by its id
* used to be getRegionById()
*
* @param int $id id of the sea area
* @param object $doctrine doctrine
*
* @return string
*/
public static function getSeaRegionById(int $id, object $doctrine): string
{
$seaRegion = $doctrine
->getRepository('OceanExpertBundle:Searegions')
->findOneBy(array('idSearegion' => $id));
return $seaRegion->getName();
}
/**
* get the name of the institution type
* replaced by getInstituteTypeNameById()
*
* @param int $id the id of the institution type
* @param object $doctrine doctrine
*
* @return string
*/
public static function getInstituteTypeById(int $id, object $doctrine): string
{
return self::getInstituteTypeNameById($id, $doctrine);
}
/**
* get the name of the institution type
* used to be getInstituteTypeById()
*
* @param int $id the id of the institution type
* @param object $doctrine doctrine
*
* @return string
*/
public function getInstituteTypeNameById(int $id, object $doctrine): string
{
$instType = $doctrine
->getRepository('OceanExpertBundle:Insttypes')
->findOneBy(
array(
'idInsttype' => $id
)
);
if ($instType) {
return $instType->getInsttypeName();
} else {
return 'no institution found';
}
}
public static function getJobTypesById($id, $doctrine)
{
$jobType = $doctrine
->getRepository('OceanExpertBundle:Jobtypes')
->findOneBy(array('idJob' => $id));
return $jobType->getJobName();
}
/**
* get the name of the country by its id
* used to be getCountryById()
*
* @param int $id id of the country
* @param object $doctrine
*
* @return string|bool
*/
public static function getCountryById(int $id, object $doctrine): string
{
if ($id != 0) {
$country = $doctrine
->getRepository('OceanExpertBundle:Countries')
->findOneBy(array('idCountry' => $id));
return $country->getCountry();
} else {
return '';
}
}
public static function getMultipleCountriesById($id, $doctrine): array
{
$id = explode(',', $id);
$countries = $doctrine
->getRepository('OceanExpertBundle:Countries')
->createQueryBuilder('c')
->select('c.country')
->where('c.idCountry in (:idCountries)')
->setParameter('idCountries', $id)
->orderBy('c.country')
->getQuery()
->getResult(AbstractQuery::HYDRATE_ARRAY);
return array_column($countries, 'country');
}
/**
* get the name of the region by its id
*
* @param int $id id of the region
* @param object $doctrine doctrine
*
* @return string
*/
public static function getRegionById(int $id, object $doctrine): string
{
$region = $doctrine
->getRepository('OceanExpertBundle:Regions')
->findOneBy(array('idRegion' => $id));
return $region->getName();
}
/**
* get all regions
* can be used by the search to provide a dropdown menu
*
* @return JsonResponse
*/
public function getRegionsAction()
{
$regions = self::getDoctrine()
->getRepository('OceanExpertBundle:Regions')
->findAll();
$regions = array_map(function ($region)
{
return array(
'id' => $region->getIdRegion(),
'name' => $region->getName()
);
},
$regions
);
return new JsonResponse($regions);
}
/**
* get all all the country id's that are in this region
*
* @return array
*/
public static function getCountriesInRegion($idRegion, $doctrine): array
{
$countryIds = $doctrine
->getRepository('OceanExpertBundle:RegionCountries')
->findBy(
array(
'idRegion' => $idRegion
)
);
return $countryIds;
}
public static function getGroupById($id, $doctrine)
{
$group = $doctrine
->getRepository('OceanExpertBundle:Groups')
->findOneBy(array('idGroup' => $id));
return $group->getGroupName();
}
public static function readableFilter($filter): string
{
return ucfirst(preg_replace('/_/', ' ', $filter));
}
/**
* get all sea regions
* can be used by the search to provide a dropdown menu
*
* @return Response
*/
public function getSeaRegionsAction(): Response
{
$regions = self::getDoctrine()
->getRepository('OceanExpertBundle:Searegions')
->seaRegions();
return new JsonResponse($regions);
}
public function getMemberGroupsAction(): Response
{
$groups = self::getDoctrine()
->getRepository('OceanExpertBundle:Groups')
->createQueryBuilder('e')
->select('e.idGroup AS id,e.groupname AS name')
->orderBy('e.groupname')
->getQuery()
->getResult(AbstractQuery::HYDRATE_ARRAY);
return new JsonResponse($groups);
}
public static function getChildGroup($group, $doctrine): array
{
$groupids = array();
$allgroups = array($group);
$groups = $doctrine
->getRepository('OceanExpertBundle:Groups')
->createQueryBuilder('e')
->select('e.idGroup,e.groupname')
->orderBy('e.groupname')
->where('e.idParentgroup = :group')
->setParameter('group', $group)
->getQuery()
->getResult(AbstractQuery::HYDRATE_ARRAY);
foreach ($groups as $value) {
$groupids[] = $value['idGroup'];
$allgroups[] = $value['idGroup'];
}
if (count($groupids) > 0) {
do {
$group2 = $doctrine
->getRepository('OceanExpertBundle:Groups')
->createQueryBuilder('e')
->select('e.idGroup AS id,e.groupname AS name')
->orderBy('e.groupname')
->where('e.idParentgroup in(:groupids)')
->setParameter('groupids', $groupids)
->getQuery()
->getResult(AbstractQuery::HYDRATE_ARRAY);
$groupids = array();
foreach ($group2 as $value) {
$groupids[] = $value['id'];
$allgroups[] = $value['id'];
}
} while (count($group2) > 0);
}
return $allgroups;
}
public function getJobTypesAction(): Response
{
$jobTypes = self::getDoctrine()
->getRepository('OceanExpertBundle:Jobtypes')
->createQueryBuilder('e')
->select('e.idJob AS id,e.jobname AS name')
->orderBy('e.jobname')
->getQuery()
->getResult(AbstractQuery::HYDRATE_ARRAY);
return new JsonResponse($jobTypes);
}
/**
* get all countries
* can be used by the search to provide a dropdown menu
*
* @return Response
*/
public function getCountriesAction(): Response
{
$countries = self::getDoctrine()
->getRepository('OceanExpertBundle:Countries')
->createQueryBuilder('e')
->select('e.idCountry AS id,e.country AS name')
->orderBy('e.country')
->getQuery()
->getResult(AbstractQuery::HYDRATE_ARRAY);
return new JsonResponse($countries);
}
public function getSubjectAreaAction(): Response
{
$subjectArea = self::getDoctrine()
->getRepository('OceanExpertBundle:Subjects')
->createQueryBuilder('e')
->select('e.subname AS id,e.subname AS name')
->orderBy('e.subname')
->getQuery()
->getResult(AbstractQuery::HYDRATE_ARRAY);
return new JsonResponse($subjectArea);
}
public function getEventTypesAction(): Response
{
$eventTypes = self::getDoctrine()
->getRepository('OceanExpertBundle:Eventtypes')
->createQueryBuilder('e')
->select('e.idEventtype AS id,e.eventtypeName AS name')
->orderBy('e.eventtypeName')
->getQuery()
->getResult(AbstractQuery::HYDRATE_ARRAY);
return new JsonResponse($eventTypes);
}
/**
* here for backward compatibility
*
* @deprecated
*
* @return JsonResponse
*/
function getInstitutionTypesAction(): JsonResponse
{
return $this->getInstituteTypesAction();
}
/**
* @return JsonResponse
*/
public function getInstituteTypesAction(): JsonResponse
{
$instTypes = self::getDoctrine()
->getRepository('OceanExpertBundle:Insttypes')
->createQueryBuilder('i')
->select('i.idInsttype AS id,i.insttypeName AS name')
->orderBy('i.insttypeName')
->getQuery()
->getResult(AbstractQuery::HYDRATE_ARRAY);
return new JsonResponse($instTypes);
}
/**
* get all possible document types
* can be used by the search to provide a dropdown menu
* @see #609
*
* @return Response
*/
public function getDocumentTypesAction(): Response
{
$documentTypes = self::getDoctrine()
->getRepository('OceanExpertBundle:Doctypes')
->createQueryBuilder('i')
->select('i.idDoctype AS id,i.doctypename AS name')
->orderBy('i.doctypename')
->getQuery()
->getResult(AbstractQuery::HYDRATE_ARRAY);
return new JsonResponse($documentTypes);
}
public function getMembersAction(Request $request): Response
{
$doctrine = self::getDoctrine();
$members = array();
if (null !== $request->request->get('searchterm')) {
$searchTerm = $request->request->get('searchterm');
$type = $request->request->get('searchparam');
} elseif (null !== $request->query->get('searchterm')) {
$searchTerm = $request->query->get('searchterm');
$type = $request->query->get('searchparam');
} else {
return new JsonResponse(
array(
'status' => 1,
'error' => 'please send "searchparam" and "searchterm"'
)
);
}
$searchParams = $this::extractParams($request);
$searchParams['searchQuery'] = $searchTerm;
$searchParams['searchType'] = $type;
$searchParams['searchFilterType'] = ['experts'];
$connection = $doctrine->getManager()->getConnection();
if ($type == 'name'
|| $type == 'all'
) {
/*
$statement = $connection->prepare("
SELECT
i.id_ind AS DT_RowId,
i.id_ind,
i.title,
i.fname,
i.mname,
i.sname,
inst.inst_name,
c.country,
i.status,
MATCH (i.fname,i.sname) AGAINST (:searchQuery IN BOOLEAN MODE) AS relevance
FROM
indiv i
LEFT JOIN countries c ON c.id_country = i.country_code
LEFT JOIN indiv_institution ii ON ii.id_ind = i.id_ind
LEFT JOIN institutions inst ON inst.id_inst = ii.id_inst
WHERE
(
MATCH (i.fname,i.mname,i.sname) AGAINST (:searchQuery IN BOOLEAN MODE)
OR i.fname LIKE :percSearchQuery
OR i.fname LIKE :searchQueryPerc
OR i.mname LIKE :percSearchQuery
OR i.mname LIKE :searchQueryPerc
OR i.sname LIKE :percSearchQuery
OR i.sname LIKE :searchQueryPerc
)
ORDER BY relevance DESC, sname ASC;
");
$statement->bindValue('searchQuery', $searchParams['searchQuery']);
$statement->bindValue('searchQueryPerc', $searchParams['searchQuery'] . '%');
$statement->bindValue('percSearchQuery', '%' . $searchParams['searchQuery']);
$statement->execute();
$members = array_merge($members, $statement->fetchAll());
*/
//we only need the active ones
$indivStatus = ' AND status = 1 ';
$instStatus = ' AND activated = 1 ';
$eventStatus = ' AND status = 1 ';
$searchParams['indivStatus'] = $indivStatus;
$searchParams['instStatus'] = $instStatus;
$searchParams['eventStatus'] = $eventStatus;
$membersTmp = self::getBrowseSearchResults($searchParams, $doctrine);
//recreate the array we expected here (see old code)
foreach($membersTmp['dataResult'] as $dataResult) {
if (!isset($dataResult['id_ind'])) {
//this is not an expert, skip
continue;
}
$members[] = array(
"DT_RowId" => $dataResult['id_ind'],
"id_ind" => $dataResult['id_ind'],
"title" => $dataResult['title'],
"fname" => $dataResult['fname'],
"mname" => $dataResult['mname'],
"sname" => $dataResult['sname'],
"inst_name" => $dataResult['inst_name'],
"country" => $dataResult['country'],
"status" => $dataResult['status'],
"relevance" => $dataResult['relevance'],
);
}
} elseif ($type == 'email') {
$statement = $connection->prepare("
SELECT
i.id_ind AS DT_RowId,
i.id_ind,
i.title,
i.fname,
i.mname,
i.sname,
inst.inst_name,
c.country,
i.status,
MATCH (i.email1) AGAINST (:searchQuery IN BOOLEAN MODE) AS relevance
FROM
indiv i
LEFT JOIN countries c ON c.id_country = i.country_code
LEFT JOIN indiv_institution ii ON ii.id_ind = i.id_ind
LEFT JOIN institutions inst ON inst.id_inst = ii.id_inst
WHERE
email1 = :searchQuery
OR email2 = :searchQuery
OR email3 = :searchQuery
ORDER BY relevance DESC;
");
$statement->bindValue(':searchQuery', $searchParams['searchQuery']);
$statement->execute();
$members = array_merge(
$members,
$statement->fetchAll()
);
} elseif ($type == 'group') {
//we only need the active ones
$indivStatus = ' AND status = 1 ';
$instStatus = ' AND activated = 1 ';
$eventStatus = ' AND status = 1 ';
$searchParams['indivStatus'] = $indivStatus;
$searchParams['instStatus'] = $instStatus;
$searchParams['eventStatus'] = $eventStatus;
$membersTmp = self::getBrowseSearchResults($searchParams, $doctrine);
//recreate the array we expected here (see old code)
foreach($membersTmp['dataResult'] as $dataResult) {
if (!isset($dataResult['id_ind'])) {
//this is not an experts, skip
continue;
}
$members[] = array(
"DT_RowId" => $dataResult['id_ind'],
"id_ind" => $dataResult['id_ind'],
"title" => $dataResult['title'],
"fname" => $dataResult['fname'],
"mname" => $dataResult['mname'],
"sname" => $dataResult['sname'],
"inst_name" => $dataResult['inst_name'],
"country" => $dataResult['country'],
"status" => $dataResult['status'],
"relevance" => $dataResult['relevance'],
"groupName" => $dataResult['groupName'],
"groupId" => $dataResult['groupId']
);
}
/*
$statement = $connection->prepare('
SELECT
i.id_ind AS DT_RowId,
i.id_ind,
i.title,
i.fname,
i.mname,
i.sname,
inst.inst_name,
c.country,
i.status,
1 AS relevance
FROM
indiv i
LEFT JOIN countries c ON c.id_country = i.country_code
LEFT JOIN indiv_institution ii ON ii.id_ind = i.id_ind
LEFT JOIN institutions inst ON inst.id_inst = ii.id_inst
LEFT JOIN member_groups m ON i.id_ind = m.id_ind
WHERE
m.id_group like "%t%"
ORDER BY relevance DESC;
');
$statement->bindValue('searchQuery', $searchParams['searchQuery']);
$statement->execute();
$members = array_merge($members, $statement->fetchAll());
*/
}
return new JsonResponse(
array(
'data' => $members
)
);
}
/**
* @param int $expert expert id
*
* @return int
*
* @todo is this used somewhere??? Arno 15/04/2022
*/
function getExpertEvents(int $expert): int
{
$em = self::getDoctrine()->getManager();
$qb = $em->createQueryBuilder();
$qb->select('e.idEvent');
$qb->from('OceanExpertBundle:EventParticipants', 'e');
$qb->where('e.idInd = :idInd');
$qb->setParameter('idInd', $expert);
$participants = $qb->getQuery()->getResult();
$participants = array_map('current', $participants);
$qb = $em->createQueryBuilder();
$qb->select('e.idEvent');
$qb->from('OceanExpertBundle:EventStaff', 'e');
$qb->where('e.idInd = :idInd');
$qb->setParameter('idInd', $expert);
$staff = $qb->getQuery()->getResult();
$staff = array_map('current', $staff);
$qb = $em->createQueryBuilder();
$qb->select('e.idEvent');
$qb->from('OceanExpertBundle:EventContacts', 'e');
$qb->where('e.idInd = :idInd');
$qb->setParameter('idInd', $expert);
$contacts = $qb->getQuery()->getResult();
$contacts = array_map('current', $contacts);
$result = array_merge($participants, $staff, $contacts);
$results = array_unique($result, SORT_REGULAR);
return count($results);
}
/**
* clean input strings to prevent XSS and other problems
*
* @param string $string input string
*
* @return string
*/
public static function cleaned(string $string): string
{
$string = nl2br(stripslashes($string));
return preg_replace(
'/[<>]/',
'--*--',
$string
);
}
/**
* in case we get the wrong format for the date
* cleanup and return the correct format
* we want a string like 'yyyy-mm-dd'
*
* @param string $string the date to be checked
*
* @return string
*/
public static function cleanDate(string $string): string
{
if (strpos($string, '/')) {
$date = explode('/', $string);
if (strlen($date[0]) == 4) {
// this was something like yyyy/mm/dd I guess
return $date[0] . '-' . $date[1] . '-' . $date[2];
} else {
// this was something like dd/mm/yyyy I guess
return $date[2] . '-' . $date[1] . '-' . $date[0];
}
} elseif (strpos($string, '-')) {
$date = explode('-', $string);
if (strlen($date[0]) == 4) {
// this was something like yyyy-mm-dd I guess
//that is the correct format already
return $string;
} else {
// this was something like dd-mm-yyyy I guess
return $date[2] . '-' . $date[1] . '-' . $date[0];
}
} else {
//no idea what to clean up
return $string;
}
}
public static function fulltext($string): string
{
$newString = '';
if (trim($string) != '') {
$arrStr = explode(' ', $string);
foreach ($arrStr as &$value) {
$value = '+' . $value;
}
$newString = implode(' ', $arrStr);
}
return $newString;
}
}

src/OceanExpertBundle/Controller/SearchController.php line 2484