src/OceanExpertBundle/Controller/SearchController.php line 2472

Open in your IDE?
  1. <?php
  2. namespace OceanExpertBundle\Controller;
  3. use Doctrine\Common\Persistence\ManagerRegistry;
  4. use Doctrine\ORM\AbstractQuery;
  5. use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
  6. use Symfony\Component\HttpFoundation\JsonResponse;
  7. use Symfony\Component\HttpFoundation\Response;
  8. use Symfony\Component\HttpFoundation\Request;
  9. class SearchController extends AbstractController
  10. {
  11.     public function generalSearchAction(Request $request): Response
  12.     {
  13.         $searchParams['searchQuery'] = $request->query->get('q');
  14.         $searchParams['searchQuery'] = self::cleaned($searchParams['searchQuery']);
  15.         if (null !== ($request->query->get('limit'))) {
  16.             $limit $request->query->get('limit');
  17.         } else {
  18.             $limit 10;
  19.         }
  20.         $em self::getDoctrine()->getManager();
  21.         $connection $em->getConnection();
  22.         $statement $connection->prepare("
  23.             SELECT 
  24.                 * 
  25.             FROM 
  26.                 indiv 
  27.             WHERE 
  28.                 ( 
  29.                      MATCH (fname,mname,sname) AGAINST (:searchQuery IN BOOLEAN MODE) 
  30.                      OR fname LIKE :percSearchQuery 
  31.                      OR fname LIKE :searchQueryPerc  
  32.                      OR mname LIKE :percSearchQuery 
  33.                      OR mname LIKE :searchQueryPerc 
  34.                      OR sname LIKE :percSearchQuery 
  35.                      OR sname LIKE :searchQueryPerc
  36.                 ) 
  37.                 AND status = 1;
  38.             ");
  39.         $statement->bindValue('searchQuery'$searchParams['searchQuery']);
  40.         $statement->bindValue('percSearchQuery''%' $searchParams['searchQuery']);
  41.         $statement->bindValue('searchQueryPerc'$searchParams['searchQuery'] . '%');
  42.         $statement->execute();
  43.         $query $statement->fetchAll();
  44.         $paginator $this->get('knp_paginator');
  45.         $pagination $paginator->paginate(
  46.             $query,
  47.             $request->query->getInt('experts'1),
  48.             $limit,
  49.             array('pageParameterName' => 'experts''sortDirectionParameterName' => 'dir')
  50.         );
  51.         $ids = array();
  52.         foreach ($pagination->getItems() as $value) {
  53.             $ids[] = $value['id_ind'];
  54.         }
  55.         $indiv $em->getRepository('OceanExpertBundle:Indiv');
  56.         $experts $indiv->createQueryBuilder('i')
  57.             ->select('i,ix.instName,g.groupname,m.idGroup,m.role,c.country')
  58.             ->leftJoin('OceanExpertBundle:IndivInstitution''ii''WITH''ii.idInd = i.idInd')
  59.             ->leftJoin('OceanExpertBundle:Institutions''ix''WITH''ix.idInst = ii.idInst')
  60.             ->leftJoin('OceanExpertBundle:MemberGroups''m''WITH''i.idInd = m.idInd')
  61.             ->leftJoin('OceanExpertBundle:Groups''g''WITH''g.idGroup = m.idGroup')
  62.             ->leftJoin('OceanExpertBundle:Countries''c''WITH''c.idCountry = i.countryCode')
  63.             ->where('i.idInd IN (:group)')
  64.             ->andWhere('i.status = 1')
  65.             ->setParameter('group'$ids)
  66.             ->addOrderBy('ix.instName''DESC')
  67.             ->addOrderBy('i.sname''ASC')
  68.             ->getQuery()
  69.             ->getResult();
  70.         $em self::getDoctrine()->getManager();
  71.         $connection $em->getConnection();
  72.         $statement $connection->prepare("
  73.             SELECT 
  74.                 * 
  75.             FROM 
  76.                 institutions 
  77.             WHERE 
  78.                 (
  79.                     MATCH (inst_name,inst_name_eng,acronym) AGAINST (:searchQuery IN BOOLEAN MODE)
  80.                     OR inst_name LIKE :percSearchQuery 
  81.                     OR inst_name LIKE :searchQueryPerc 
  82.                     OR inst_name_eng LIKE :percSearchQuery 
  83.                     OR inst_name_eng LIKE :searchQueryPerc 
  84.                     OR acronym LIKE :percSearchQuery 
  85.                     OR acronym LIKE :searchQueryPerc
  86.                     OR activities LIKE :percSearchQueryPerc
  87.                 )
  88.                 AND activated = 1;
  89.         ");
  90.         $statement->bindValue('searchQuery'$searchParams['searchQuery']);
  91.         $statement->bindValue('percSearchQuery''%' $searchParams['searchQuery']);
  92.         $statement->bindValue('searchQueryPerc'$searchParams['searchQuery'] . '%');
  93.         $statement->bindValue('percSearchQueryPerc''%' $searchParams['searchQuery'] . '%');
  94.         $statement->execute();
  95.         $institutions $statement->fetchAll();
  96.         $instPage $paginator->paginate(
  97.             $institutions,
  98.             $request->query->getInt('insts'1),
  99.             12,
  100.             array('pageParameterName' => 'insts''sortDirectionParameterName' => 'dir')
  101.         );
  102.         $evnts $em->getRepository('OceanExpertBundle:Events');
  103.         $events $evnts->createQueryBuilder('e')
  104.             ->select('e')
  105.             ->where('e.title LIKE :searchQuery')
  106.             ->where('e.shorttitle LIKE :searchQuery')
  107.             ->where('e.summary LIKE :searchQuery')
  108.             ->setParameter('searchQuery''%' $searchParams['searchQuery'] . '%')
  109.             ->addOrderBy('e.title''ASC')
  110.             ->getQuery()
  111.             ->getResult();
  112.         $eventspage $paginator->paginate(
  113.             $events,
  114.             $request->query->getInt('events'1),
  115.             10,
  116.             array('pageParameterName' => 'events''sortDirectionParameterName' => 'dir')
  117.         );
  118.         return $this->render(
  119.             'Search/generalSearch.html.twig',
  120.             array(
  121.                 'pagination' => $pagination,
  122.                 'experts' => $experts,
  123.                 'institutions' => $instPage,
  124.                 'events' => $eventspage,
  125.                 'items' => array(),
  126.                 'qry' => $searchParams['searchQuery']
  127.             )
  128.         );
  129.     }
  130.     /**
  131.      * extract the possible filters from the results
  132.      * these can be used to filter down the results even move (especially in the webinterface)
  133.      *
  134.      * @param array $dataResult the results from a search
  135.      *
  136.      * @return array
  137.      */
  138.     static function getFilters(array $dataResult): array
  139.     {
  140.         $filters = array();
  141.         foreach(['type''country'] as $type) {
  142.             $filters[$type] = SearchController::getUnique(
  143.                 $dataResult,
  144.               $type
  145.             );
  146.         }
  147.         return $filters;
  148.     }
  149.     /**
  150.      * extract all the params we need to do the search
  151.      * these params should be reflected in the documentation of the API
  152.      * as these are all the possible params that can be given and have effect on the result
  153.      * they are shown here in alfabetic order to be sure there are no doubles in the variables
  154.      *
  155.      * @param Request $request
  156.      *
  157.      * @return array all the params in an array
  158.      */
  159.     static function extractParams(Request $request): array
  160.     {
  161.         $searchParams['queryString']    = $request->query->all();
  162.         $searchParams['action']         = $request->query->get('action') ?? 'advSearch';
  163.         $searchParams['countryName']    = $request->query->get('countryName') ?? '';
  164.         $searchParams['endDate']        = $request->query->get('endDate');
  165.         $searchParams['expertLname']    = $request->query->get('expertLname') ?? '';
  166.         $searchParams['countryName']    = $request->query->get('countryName') ?? '';
  167.         $searchParams['expertSubject']  = $request->query->get('expertSubject') ?? '';
  168.         $searchParams['expertRegion']   = $request->query->get('expertRegion') ?? '';
  169.         $searchParams['instCountry']    = $request->query->get('instCountry') ?? '';
  170.         $searchParams['instCountry']    = $request->query->get('instCountry') ?? '';
  171.         $searchParams['instName']       = $request->query->get('instName') ?? '';
  172.         $searchParams['instRegion']     = $request->query->get('instRegion') ?? '';
  173.         $searchParams['instType']       = $request->query->get('instType') ?? '';
  174.         $searchParams['searchKeywords'] = $request->query->get('keywords', array());
  175.         $searchParams['limit']          = $request->query->get('limit') ?? 10;
  176.         $searchParams['sortOrder']      = $request->query->get('order') ?? 'ASC';
  177.         $searchParams['searchQuery']    = $request->query->get('query') ?? '';
  178.         $searchParams['sortby']         = $request->query->get('sortby') ?? '';
  179.         $searchParams['startDate']      = $request->query->get('startDate');
  180.         $searchParams['searchToggle']   = $request->query->get('toggle');
  181.         //what kind of items are we looking for
  182.         $searchParams['searchType']     = $request->query->get('type', array());
  183.         //default value for the type when using browse should be 'all'
  184.         if (null == $request->query->get('type')
  185.             || $request->query->get('type') == ''
  186.             || (is_array($searchParams['searchType'])
  187.                 && !count($searchParams['searchType']))
  188.         ) {
  189.             $searchParams['searchType'] = array('all');
  190.         }
  191.         //search types shouls always be an array, even with one element
  192.         if (!is_array($searchParams['searchType'])) {
  193.             $searchParams['searchType'] = array($searchParams['searchType']);
  194.         }
  195.         //get rid of those empty types that get added somewhere by javascript
  196.         foreach ($searchParams['searchType'] as $key => $val) {
  197.             if ($val === '') {
  198.                 unset($searchParams['searchType'][$key]);
  199.             }
  200.         }
  201.         //independent of the searchType, what type should we use to filter the results
  202.         //typically afte a search with searchType == all
  203.         $searchParams['searchFilterType']= $request->query->get('filterType');
  204.         //cleanup the filters
  205.         $searchParams['searchFilterType'] = array_unique(
  206.             explode(
  207.                 ',',
  208.                 $searchParams['searchFilterType']
  209.             )
  210.         );
  211.         if ($searchParams['searchFilterType'][0] == '') {
  212.             unset($searchParams['searchFilterType']);
  213.         }
  214.         //special case of FilterType
  215.         $searchParams['filterCountry']  = $request->query->get('filterCountry');
  216.         //not sure if this is used everywhere/somewhere and what it should do
  217.         $searchParams['searchFilter']   = $request->query->get('filter');
  218.         //clenaup the query
  219.         $searchParams['searchQuery'] = self::cleaned($searchParams['searchQuery']);
  220.         //remember the search conditions, so we can recreate the search in the interface
  221.         $conditions = array();
  222.         foreach ($searchParams['searchType'] as $i => $value) {
  223.             $conditions[$i]['type'] = $value;
  224.             if (!isset($searchParams['searchFilter'][$i])) {
  225.                 $searchParams['searchFilter'][$i] = '';
  226.             }
  227.             $conditions[$i]['filter'] = $searchParams['searchFilter'][$i];
  228.             $conditions[$i]['keywords'] = $searchParams['searchKeywords'][$i] ?? '';
  229.             if (!isset($searchParams['searchToggle'][$i])) {
  230.                 $searchParams['searchToggle'][$i] = ' OR ';
  231.             }
  232.             $conditions[$i]['toggle'] = $searchParams['searchToggle'][$i];
  233.             if (!isset($searchParams['startDate'][$i])) {
  234.                 $searchParams['startDate'][$i] = '';
  235.             }
  236.             $conditions[$i]['startDate'] = $searchParams['startDate'][$i];
  237.             if (!isset($searchParams['endDate'][$i])) {
  238.                 $searchParams['endDate'][$i] = '';
  239.             }
  240.             $conditions[$i]['endDate'] = $searchParams['endDate'][$i];
  241.         }
  242.         $searchParams['conditions'] = $conditions;
  243.         //what to do with the sortby if there is none defined (yet)
  244.         $sortFields = array(
  245.             'relevance' => 'Relevance'
  246.         );
  247.         if ($searchParams['sortby'] == '') {
  248.             //for all, experts and institutions use 'relevance' as sortby in case we have a search for 'name contains'
  249.             //in other cases use inst_name for institutions
  250.             // or sname for experts
  251.             if (!empty(array_intersect($searchParams['searchType'], array('all''experts''institutions')))
  252.                 && !empty(array_intersect(['Name contains'], $searchParams['searchFilter']))
  253.             ) {
  254.                 $searchParams['sortby'] = 'relevance';
  255.                 $sortFields['relevance'] = 'Relevance';
  256.             } elseif (!empty(array_intersect($searchParams['searchType'], array('experts')))) {
  257.                 $searchParams['sortby'] = 'sname';
  258.                 $sortFields['sname'] = 'Last Name';
  259.             } elseif (!empty(array_intersect($searchParams['searchType'], array('documents')))) {
  260.                 $searchParams['sortby'] = 'title';
  261.                 $sortFields['country'] = 'Country';
  262.             } elseif (!empty(array_intersect($searchParams['searchType'], array('institutions')))) {
  263.                 $searchParams['sortby'] = 'inst_name';
  264.                 $sortFields['inst_name'] = 'Institute Name';
  265.             }
  266.         }
  267.         //if we still have no sortby, default to sname for both browse and advSearch
  268.         if ($searchParams['sortby'] == ''
  269.             && ($searchParams['action'] == 'browse'
  270.                 || $searchParams['action'] == 'advSearch')
  271.         ) {
  272.             $searchParams['sortby'] = 'sname';
  273.             unset($sortFields['relevance']);
  274.             $sortFields['sname'] = 'Last Name';
  275.         }
  276.         if (count($searchParams['searchType'])) {
  277.             if (in_array('institutions'$searchParams['searchType'])) {
  278.                 unset($sortFields['relevance']);
  279.                 $sortFields['instType'] = 'Institute Type';
  280.                 $sortFields['name'] = 'Institute Name';
  281.             }
  282.             if (in_array('experts'$searchParams['searchType'])) {
  283.                 $sortFields['jobtitle'] = 'Position';
  284.                 $sortFields['sname'] = 'Last Name';
  285.             }
  286.             if (in_array('events'$searchParams['searchType'])) {
  287.                 $sortFields['eventtype'] = 'Event Type';
  288.             }
  289.             $sortFields['country'] = 'Country';
  290.         }
  291.         $searchParams['sortFields'] = $sortFields;
  292.         return $searchParams;
  293.     }
  294.     /**
  295.      * get the results for the search when action=browse
  296.      *
  297.      * @param array  $searchParams all the search parameters that have been passed in this request
  298.      * @param object $doctrine
  299.      *
  300.      * @return array with the results
  301.      */
  302.     public static function getBrowseSearchResults(array $searchParamsobject $doctrine): array
  303.     {
  304.         $connection $doctrine->getManager()->getConnection();
  305.         $searchResultType 'Browse Results';
  306.         $dataResult = array();
  307.         $sortFields $searchParams['sortFields'];
  308.         $message '';
  309.         //status=0 means everything is ok
  310.         $status 0;
  311.         if (is_array($searchParams['searchType'])
  312.             && count($searchParams['searchType']) > 1
  313.         ) {
  314.             //impossible for action=browse, this should be a simple query in the end
  315.             $message 'You can only have one (filter)type when you use action=browse.';
  316.             $message .= ' Provided (filter)types: ' implode(', '$searchParams['searchType']);
  317.             return array(
  318.                 'status' => 1,
  319.                 'message' => $message
  320.             );
  321.         }
  322.         //for the searchtype 'browse' we do not need an array of search types
  323.         if (isset($searchParams['searchType'])
  324.             && is_array($searchParams['searchType'])
  325.             && count($searchParams['searchType'])
  326.         ) {
  327.             $searchParams['searchType'] = $searchParams['searchType'][0];
  328.         }
  329.         if ($searchParams['searchQuery'] != '') {
  330.             $indiv = array();
  331.             $indivNames = array();
  332.             $indivGroups = array();
  333.             if (($searchParams['searchType'] == 'all'
  334.                     || $searchParams['searchType'] == 'experts'
  335.                 )
  336.                 && !(isset($searchParams['queryString']['searchType'])
  337.                     && $searchParams['queryString']['searchType'] === 'group')
  338.             ) {
  339.                 $newString self::fulltext($searchParams['searchQuery']);
  340.                 $stmtInd $connection->prepare("
  341.                         SELECT 
  342.                             'experts' AS type, 
  343.                             i.status, 
  344.                             i.id_ind, 
  345.                             i.title,   
  346.                             concat(i.fname,' ',i.sname) AS name,
  347.                             i.fname,
  348.                             i.mname,
  349.                             i.sname,
  350.                             i.jobtitle,
  351.                             i.addr_1,
  352.                             i.addr_2,
  353.                             i.city,
  354.                             i.state,
  355.                             i.postcode,
  356.                             i.use_inst_addr, 
  357.                             i.deceased, 
  358.                             i.retired, 
  359.                             i.quality_checked, 
  360.                             inst.inst_name,
  361.                             inst.inst_address,
  362.                             inst.addr_2 AS instAddr2,
  363.                             inst.city AS instCity,
  364.                             inst.state AS instState,
  365.                             inst.postcode AS instPostCode, 
  366.                             (IF(i.use_inst_addr = 1, ic.country, c.country)) AS country, 
  367.                             MATCH (i.fname,i.sname) AGAINST (:newString IN BOOLEAN MODE) AS relevance, 
  368.                             'zz' AS instType, 
  369.                             'zz' AS eventtype, 
  370.                             '' AS inst_logo, 
  371.                             'zz' AS name
  372.                         FROM 
  373.                             indiv i
  374.                             LEFT JOIN countries c ON c.id_country = i.country_code
  375.                             LEFT JOIN indiv_institution ii ON ii.id_ind = i.id_ind
  376.                             LEFT JOIN institutions inst ON inst.id_inst = ii.id_inst
  377.                             LEFT JOIN countries ic ON ic.id_country = inst.country_code
  378.                         WHERE 
  379.                             ( 
  380.                                 MATCH (fname, mname, sname) AGAINST (:newString IN BOOLEAN MODE) 
  381.                                 OR i.fname LIKE :percSearchQuery
  382.                                 OR i.fname LIKE :searchQueryPerc 
  383.                                 OR i.mname LIKE :percSearchQuery
  384.                                 OR i.mname LIKE :searchQueryPerc 
  385.                                 OR i.sname LIKE :percSearchQuery
  386.                                 OR i.sname LIKE :searchQueryPerc
  387.                                 OR i.private_address LIKE :percSearchQueryPerc
  388.                                 OR i.addr_1 LIKE :percSearchQueryPerc
  389.                                 OR i.addr_2 LIKE :percSearchQueryPerc
  390.                                 OR i.city LIKE :percSearchQueryPerc
  391.                                 OR i.state LIKE :percSearchQueryPerc
  392.                             ) 
  393.                             " $searchParams['indivStatus'] . '
  394.                         ORDER BY relevance DESC, sname ASC;
  395.                     ');
  396.                 $stmtInd->bindValue('newString'$newString);
  397.                 $stmtInd->bindValue('percSearchQuery''%' $searchParams['searchQuery']);
  398.                 $stmtInd->bindValue('searchQueryPerc'$searchParams['searchQuery'] . '%');
  399.                 $stmtInd->bindValue('percSearchQueryPerc''%' $searchParams['searchQuery'] . '%');
  400.                 $stmtInd->execute();
  401.                 $indiv $stmtInd->fetchAll();
  402.                 $sortFields['jobtitle'] = 'Position';
  403.             }
  404.             if ($searchParams['searchType'] == 'name') {
  405.                 $newString self::fulltext($searchParams['searchQuery']);
  406.                 $stmtInd $connection->prepare("
  407.                         SELECT 
  408.                             'experts' AS type, 
  409.                             i.status, 
  410.                             i.id_ind, 
  411.                             i.title,   
  412.                             concat(i.fname,' ',i.sname) AS name,
  413.                             i.fname,
  414.                             i.mname,
  415.                             i.sname,
  416.                             i.jobtitle,
  417.                             i.addr_1,
  418.                             i.addr_2,
  419.                             i.city,
  420.                             i.state,
  421.                             i.postcode,
  422.                             i.use_inst_addr, 
  423.                             i.deceased, 
  424.                             i.retired, 
  425.                             i.quality_checked, 
  426.                             inst.inst_name,
  427.                             inst.inst_address,
  428.                             inst.addr_2 AS instAddr2,
  429.                             inst.city AS instCity,
  430.                             inst.state AS instState,
  431.                             inst.postcode AS instPostCode, 
  432.                             (IF(i.use_inst_addr = 1, ic.country, c.country)) AS country, 
  433.                             MATCH (i.fname,i.sname) AGAINST (:newString IN BOOLEAN MODE) AS relevance, 
  434.                             'zz' AS instType, 
  435.                             'zz' AS eventtype, 
  436.                             '' AS inst_logo, 
  437.                             'zz' AS name
  438.                         FROM 
  439.                             indiv i
  440.                             LEFT JOIN countries c ON c.id_country = i.country_code
  441.                             LEFT JOIN indiv_institution ii ON ii.id_ind = i.id_ind
  442.                             LEFT JOIN institutions inst ON inst.id_inst = ii.id_inst
  443.                             LEFT JOIN countries ic ON ic.id_country = inst.country_code
  444.                         WHERE 
  445.                             ( 
  446.                                 MATCH (fname, mname, sname) AGAINST (:newString IN BOOLEAN MODE) 
  447.                                 OR i.fname LIKE :percSearchQuery
  448.                                 OR i.fname LIKE :searchQueryPerc 
  449.                                 OR i.mname LIKE :percSearchQuery
  450.                                 OR i.mname LIKE :searchQueryPerc 
  451.                                 OR i.sname LIKE :percSearchQuery
  452.                                 OR i.sname LIKE :searchQueryPerc
  453.                             ) 
  454.                             " $searchParams['indivStatus'] . '
  455.                         ORDER BY relevance DESC, sname ASC;
  456.                     ');
  457.                 $stmtInd->bindValue('newString'$newString);
  458.                 $stmtInd->bindValue('percSearchQuery''%' $searchParams['searchQuery']);
  459.                 $stmtInd->bindValue('searchQueryPerc'$searchParams['searchQuery'] . '%');
  460.                 $stmtInd->execute();
  461.                 $indivNames $stmtInd->fetchAll();
  462.                 $sortFields['jobtitle'] = 'Position';
  463.             }
  464.             if ($searchParams['searchType'] == 'all'
  465.                 || (isset($searchParams['queryString']['searchType'])
  466.                 && $searchParams['queryString']['searchType'] == 'group'
  467.                 && $searchParams['searchType'] == 'experts')
  468.                 || $searchParams['searchType'] == 'group'
  469.             ) {
  470.                 //looking for experts that are in a group that matches the searchTerm
  471.                 $newString self::fulltext($searchParams['searchQuery']);
  472.                 $stmtInd $connection->prepare("
  473.                         SELECT 
  474.                             'experts' AS type, 
  475.                             i.status, 
  476.                             i.id_ind, 
  477.                             i.title,   
  478.                             concat(i.fname,' ',i.sname) AS name,
  479.                             i.fname,
  480.                             i.mname,
  481.                             i.sname,
  482.                             i.jobtitle,
  483.                             i.addr_1,
  484.                             i.addr_2,
  485.                             i.city,
  486.                             i.state,
  487.                             i.postcode,
  488.                             i.use_inst_addr, 
  489.                             i.deceased, 
  490.                             i.retired, 
  491.                             i.quality_checked, 
  492.                             inst.inst_name,
  493.                             inst.inst_address,
  494.                             inst.addr_2 AS instAddr2,
  495.                             inst.city AS instCity,
  496.                             inst.state AS instState,
  497.                             inst.postcode AS instPostCode, 
  498.                             g.groupname AS groupName,
  499.                             g.id_group AS groupId,
  500.                             '' AS relevance,   
  501.                             (IF(i.use_inst_addr = 1, ic.country, c.country)) AS country
  502.                         FROM 
  503.                             indiv i
  504.                             LEFT JOIN countries c ON c.id_country = i.country_code
  505.                             LEFT JOIN indiv_institution ii ON ii.id_ind = i.id_ind
  506.                             LEFT JOIN institutions inst ON inst.id_inst = ii.id_inst
  507.                             LEFT JOIN countries ic ON ic.id_country = inst.country_code
  508.                             LEFT JOIN member_groups mg ON mg.id_ind = i.id_ind
  509.                             LEFT JOIN groups g ON mg.id_group = g.id_group
  510.                         WHERE 
  511.                             ( 
  512.                                 g.groupname LIKE :percSearchQueryPerc
  513.                             ) 
  514.                             " $searchParams['indivStatus'] . '
  515.                         ORDER BY sname ASC;
  516.                     ');
  517.                 $stmtInd->bindValue('percSearchQueryPerc''%' $searchParams['searchQuery'] . '%');
  518.                 $stmtInd->execute();
  519.                 $indivGroups $stmtInd->fetchAll();
  520.                 $sortFields['jobtitle'] = 'Position';
  521.             }
  522.             $institutions = array();
  523.             if ($searchParams['searchType'] == 'all'
  524.                 || $searchParams['searchType'] == 'institutions'
  525.             ) {
  526.                 $newString self::fulltext($searchParams['searchQuery']);
  527.                 $stmtInst $connection->prepare("
  528.                         SELECT 
  529.                             'institutions' AS type, 
  530.                             i.activated, 
  531.                             i.id_inst, 
  532.                             insttype_name AS instType, 
  533.                             inst_name,
  534.                             inst_name_eng,
  535.                             inst_address,
  536.                             addr_2,
  537.                             city AS instCity,
  538.                             state AS instState,
  539.                             postcode AS instPostCode,
  540.                             country,
  541.                             inst_logo,
  542.                             activities, 
  543.                             MATCH (inst_name,inst_name_eng,acronym) AGAINST (:newString) AS relevance, 
  544.                             'zz' AS jobtitle, 
  545.                             'zz' AS eventtype,
  546.                             'zz' AS fname,  
  547.                             'zz' AS sname, 
  548.                             'zz' AS name
  549.                         FROM 
  550.                             institutions i
  551.                             LEFT JOIN countries c ON c.id_country =  i.country_code
  552.                             LEFT JOIN insttypes it ON it.id_insttype =  i.inst_type_id
  553.                         WHERE 
  554.                             (
  555.                                 MATCH (inst_name,inst_name_eng,acronym) AGAINST (:newString IN BOOLEAN MODE)
  556.                                 OR inst_name LIKE :percSearchQueryPerc 
  557.                                 OR inst_name_eng LIKE :percSearchQueryPerc 
  558.                                 OR inst_address LIKE :percSearchQueryPerc 
  559.                                 OR addr_2 LIKE :percSearchQueryPerc 
  560.                                 OR city LIKE :percSearchQueryPerc 
  561.                                 OR state LIKE :percSearchQueryPerc 
  562.                                 OR acronym LIKE :percSearchQueryPerc 
  563.                                 OR activities LIKE :percSearchQueryPerc
  564.                             )
  565.                             " $searchParams['instStatus'] . '
  566.                         ORDER BY relevance DESC, i.inst_name ASC;
  567.                     ');
  568.                 $stmtInst->bindValue('newString'$newString);
  569.                 $stmtInst->bindValue('percSearchQueryPerc''%'.$searchParams['searchQuery'].'%');
  570.                 $stmtInst->execute();
  571.                 $institutions $stmtInst->fetchAll();
  572.                 $sortFields['instType'] = 'Institution Type';
  573.             }
  574.             $events = array();
  575.             if ($searchParams['searchType'] == 'all'
  576.                 || $searchParams['searchType'] == 'events'
  577.             ) {
  578.                 $sortFields['name'] = 'Event Title';
  579.                 if ($searchParams['sortby'] == '') {
  580.                     $searchParams['sortby'] = 'name';
  581.                 }
  582.                 $query "
  583.                         SELECT 
  584.                             'events' AS type, 
  585.                             et.eventtype_name AS eventtype, 
  586.                             e.status AS activated,
  587.                             e.id_event, 
  588.                             e.title AS name, 
  589.                             e.start_on,
  590.                             e.end_on, 
  591.                             e.address AS addr_1,
  592.                             e.city, 
  593.                             e.state, 
  594.                             c.country AS country,
  595.                             'zz' AS jobtitle, 
  596.                             'zz' AS instType,
  597.                             'zz' AS fname,
  598.                             'zz' AS sname,
  599.                             'zz' AS inst_name
  600.                         FROM 
  601.                             events e
  602.                             LEFT JOIN countries c on c.id_country =  e.id_country
  603.                             LEFT JOIN eventtypes et on et.id_eventtype =  e.id_eventtype
  604.                         WHERE 
  605.                             (
  606.                                 e.title LIKE :percSearchQueryPerc 
  607.                                 OR e.summary LIKE :percSearchQueryPerc
  608.                                 OR e.shorttitle LIKE :percSearchQueryPerc
  609.                                 OR e.keywords LIKE :percSearchQueryPerc
  610.                                 OR e.website LIKE :percSearchQueryPerc
  611.                                 OR e.city LIKE :percSearchQueryPerc
  612.                                 OR e.address LIKE :percSearchQueryPerc
  613.                             )
  614.                             " $searchParams['eventStatus'] . '
  615.                          ORDER BY e.title ASC;
  616.                      ';
  617.                 $stmtEvent $connection->prepare($query);
  618.                 $stmtEvent->bindValue('searchQuery'$searchParams['searchQuery']);
  619.                 $stmtEvent->bindValue('percSearchQueryPerc''%' $searchParams['searchQuery'] . '%');
  620.                 $stmtEvent->execute();
  621.                 $events $stmtEvent->fetchAll();
  622.                 $sortFields['eventtype'] = 'Event Type';
  623.             }
  624.             $documents = array();
  625.             if ($searchParams['searchType'] == 'all'
  626.                 || $searchParams['searchType'] == 'documents'
  627.             ) {
  628.                 $sortFields['title'] = 'Document Title';
  629.                 if ($searchParams['sortby'] == '') {
  630.                     $searchParams['sortby'] = 'title';
  631.                 }
  632.                 $query "
  633.                         SELECT 
  634.                             'documents' AS type, 
  635.                             d.id_doc,
  636.                             dt.doctypename AS documenttype, 
  637.                             d.title, 
  638.                             d.created_at,
  639.                             'undefined' AS country
  640.                         FROM 
  641.                              documents d, 
  642.                              doctypes dt 
  643.                         WHERE 
  644.                             dt.id_doctype = d.id_doctype
  645.                             AND d.title LIKE :percSearchQueryPerc
  646.                         ORDER BY d.title ASC
  647.                     ";
  648.                 $stmtDocument $connection->prepare($query);
  649.                 $stmtDocument->bindValue('percSearchQueryPerc''%' $searchParams['searchQuery'] . '%');
  650.                 $stmtDocument->execute();
  651.                 $documents $stmtDocument->fetchAll();
  652.                 $sortFields['documenttype'] = 'Document Type';
  653.             }
  654.             $dataResult array_unique(
  655.                 array_merge(
  656.                     $indiv,
  657.                     $indivNames,
  658.                     $indivGroups,
  659.                     $institutions,
  660.                     $events,
  661.                     $documents
  662.                 ),
  663.                 SORT_REGULAR
  664.             );
  665.             if ($searchParams['searchType'] == 'all') {
  666.                 if (count($dataResult) == 0) {
  667.                     $message 'We could not find any results matching "<strong>' self::cleaned($searchParams['searchQuery']) . ' </strong>". ';
  668.                     $message.= 'Please try again with different keyword(s).';
  669.                     return array(
  670.                         'status' => 3,
  671.                         'message' => $message,
  672.                         'dataResult' => array(),
  673.                         'searchResultType' => '',
  674.                         'sortFields' => array()
  675.                     );
  676.                 }
  677.                 $message 'Your search for "<strong>' self::cleaned($searchParams['searchQuery']) . '</strong>" ';
  678.                 $message.= 'returned <strong>' count($dataResult) . '</strong> result(s).';
  679.             } elseif ($searchParams['searchType'] == 'experts') {
  680.                 if (count($dataResult) == 0) {
  681.                     $message 'We could not find any results matching "<strong>' self::cleaned($searchParams['searchQuery']) . '</strong>" in Experts. ';
  682.                     $message.= 'Please try again with different keyword(s).';
  683.                     return array(
  684.                         'status' => 3,
  685.                         'message' => $message,
  686.                         'dataResult' => array(),
  687.                         'searchResultType' => '',
  688.                         'sortFields' => array()
  689.                     );
  690.                 }
  691.                 $message 'Your search for "<strong>' self::cleaned($searchParams['searchQuery']) . '</strong>" in "<strong>Experts</strong>" ';
  692.                 $message.= 'returned <strong>' count($dataResult) . '</strong> result(s).';
  693.             } elseif ($searchParams['searchType'] == 'institutions') {
  694.                 if (count($dataResult) == 0) {
  695.                     $message 'We could not find any results matching "<strong>' self::cleaned($searchParams['searchQuery']) . '</strong>" in Institutions. ';
  696.                     $message.= 'Please try again with different keyword(s).';
  697.                     return array(
  698.                         'status' => 3,
  699.                         'message' => $message,
  700.                         'dataResult' => array(),
  701.                         'searchResultType' => '',
  702.                         'sortFields' => array()
  703.                     );
  704.                 }
  705.                 $message 'Your search for "<strong>' self::cleaned($searchParams['searchQuery']) . '</strong>" in "<strong>Institutions</strong>" ';
  706.                 $message.= 'returned <strong>' count($dataResult) . '</strong> result(s).';
  707.             } elseif ($searchParams['searchType'] == 'documents') {
  708.                 $searchParams['countryName'] = '';
  709.                 $searchParams['expertLname'] = '';
  710.                 if (count($dataResult) == 0) {
  711.                     $message 'We could not find any results matching "<strong>' self::cleaned($searchParams['searchQuery']) . '</strong>" in the title of Documents. ';
  712.                     $message .= 'Please try again with different word(s).';
  713.                     return array(
  714.                         'status' => 3,
  715.                         'message' => $message,
  716.                         'dataResult' => array(),
  717.                         'searchResultType' => '',
  718.                         'sortFields' => array()
  719.                     );
  720.                 }
  721.                 $message "Your search for \"<strong>" self::cleaned($searchParams['searchQuery']) . "</strong>\" ";
  722.                 $message .= "in \"<strong>Documents</strong>\" ";
  723.                 $message .= "returned <strong>" count($dataResult) . "</strong> result(s).";
  724.             }
  725.         }
  726.         if ($searchParams['expertLname'] != '') {
  727.             $stmtInd $connection->prepare("
  728.                     SELECT 
  729.                         'experts' AS type, 
  730.                         i.status, 
  731.                         i.id_ind, 
  732.                         concat(i.fname, ' ', i.sname) AS name,
  733.                         i.fname,
  734.                         i.mname,
  735.                         i.sname,
  736.                         inst_name,
  737.                         jobtitle,
  738.                         i.addr_1,
  739.                         i.addr_2,
  740.                         i.city,
  741.                         i.state,
  742.                         i.postcode,
  743.                         inst.inst_address,
  744.                         inst.addr_2 AS instAddr2,
  745.                         inst.city AS instCity,
  746.                         inst.state AS instState,
  747.                         inst.postcode AS instPostCode, 
  748.                         use_inst_addr, 
  749.                         deceased, 
  750.                         retired, 
  751.                         quality_checked, 
  752.                         (IF(use_inst_addr = 1, ic.country, c.country)) AS country, 
  753.                         'zz' AS instType, 
  754.                         'zz' AS eventtype, 
  755.                         '' AS inst_logo
  756.                     FROM 
  757.                         indiv i
  758.                         LEFT JOIN countries c on c.id_country =  i.country_code
  759.                         LEFT JOIN indiv_institution ii on ii.id_ind = i.id_ind
  760.                         LEFT JOIN institutions inst on inst.id_inst = ii.id_inst
  761.                         LEFT JOIN countries ic on ic.id_country =  inst.country_code
  762.                     WHERE 
  763.                         (sname LIKE :expertLNamePerc)
  764.                         " $searchParams['indivStatus'] . 
  765.                     ORDER BY sname, fname ASC
  766.                 ');
  767.             $stmtInd->bindValue('expertLNamePerc'$searchParams['expertLname'] . '%');
  768.             $stmtInd->execute();
  769.             $dataResult $stmtInd->fetchAll();
  770.             $sortFields['jobtitle'] = 'Position';
  771.             $message 'There are <strong>' count($dataResult) . '</strong> experts ';
  772.             $message.= 'with their <strong>last name</strong> starting with "<strong>' self::cleaned($searchParams['expertLname']) . '</strong>".';
  773.         }
  774.         if ($searchParams['countryName'] != '') {
  775.             if ($searchParams['searchType'] == 'experts') {
  776.                 $query "
  777.                         SELECT 
  778.                             'experts' AS type, 
  779.                             i.status, 
  780.                             i.id_ind, 
  781.                             concat(i.fname,' ',i.sname) AS name,
  782.                             i.fname,
  783.                             i.mname,
  784.                             i.sname,
  785.                             inst_name,
  786.                             jobtitle,
  787.                             i.addr_1,
  788.                             i.addr_2,
  789.                             i.city,
  790.                             i.state,
  791.                             i.postcode,
  792.                             inst.inst_address,
  793.                             inst.addr_2 AS instAddr2,
  794.                             inst.city AS instCity,
  795.                             inst.state AS instState,
  796.                             inst.postcode AS instPostCode, 
  797.                             use_inst_addr, 
  798.                             deceased, 
  799.                             retired, 
  800.                             quality_checked, 
  801.                             (IF(use_inst_addr = 1, ic.country, c.country)) AS country, 
  802.                             'zz' AS instType, 
  803.                             'zz' AS eventtype, 
  804.                             '' AS inst_logo
  805.                         FROM    
  806.                             indiv i
  807.                             LEFT JOIN countries c on c.id_country =  i.country_code
  808.                             LEFT JOIN indiv_institution ii on ii.id_ind = i.id_ind
  809.                             LEFT JOIN institutions inst on inst.id_inst = ii.id_inst
  810.                             LEFT JOIN countries ic on ic.id_country =  inst.country_code
  811.                         WHERE 
  812.                             ( CASE WHEN use_inst_addr = 1 THEN ";
  813.                 if (strlen($searchParams['countryName']) === 1) {
  814.                     $query .= 'ic.country LIKE :countryNamePerc ELSE c.country LIKE :countryNamePerc END) ';
  815.                 } else {
  816.                     $query .= 'ic.country LIKE :countryName ELSE c.country LIKE :countryName END) ';
  817.                 }
  818.                 $query .=  $searchParams['indivStatus'] . ' ORDER BY sname ASC ';
  819.                 $stmtInd $connection->prepare($query);
  820.                 $stmtInd->bindValue('countryName'$searchParams['countryName']);
  821.                 $stmtInd->bindValue('countryNamePerc'$searchParams['countryName'] . '%');
  822.                 $stmtInd->execute();
  823.                 $dataResult $stmtInd->fetchAll();
  824.                 $sortFields['jobtitle'] = 'Position';
  825.                 $message 'There are <strong>' count($dataResult) . '</strong> experts ';
  826.                 $message.= 'located in <strong>countries with names</strong> starting with "<strong>' self::cleaned($searchParams['countryName']) . '</strong>".';
  827.                 if (isset($searchParams['referrer'])
  828.                     && ($searchParams['referrer'] == 'map')
  829.                 ) {
  830.                     $message 'There are <strong>' count($dataResult) . '</strong> experts ';
  831.                     $message.= 'located in  "<strong>' self::cleaned($searchParams['countryName']) . '</strong>".';
  832.                 }
  833.             } elseif ($searchParams['searchType'] == 'institution') {
  834.                 $stmtInd $connection->prepare("
  835.                         SELECT 
  836.                             'institutions' AS type, 
  837.                             insttype_name AS jobtitle, 
  838.                             i.activated, 
  839.                             i.id_inst, 
  840.                             insttype_name AS instType, 
  841.                             inst_name,
  842.                             inst_name_eng,
  843.                             inst_address,
  844.                             addr_2,
  845.                             city AS instCity,
  846.                             state AS instState,
  847.                             postcode AS instPostCode,
  848.                             country,
  849.                             activities, 
  850.                             inst_logo, 
  851.                             'zz' AS jobtitle,
  852.                             'zz' AS fname, 
  853.                             'zz' AS sname
  854.                         FROM 
  855.                             institutions i
  856.                             LEFT JOIN countries c on c.id_country =  i.country_code
  857.                             LEFT JOIN insttypes it on it.id_insttype =  i.inst_type_id
  858.                         WHERE 
  859.                             ( c.country LIKE '" $searchParams['countryName'] . "' )
  860.                             " $searchParams['instStatus'] . 
  861.                         ORDER BY inst_name ASC
  862.                     ');
  863.                 $stmtInd->execute();
  864.                 $dataResult $stmtInd->fetchAll();
  865.                 $sortFields['instType'] = 'Institution Type';
  866.                 $message 'There are <strong>' count($dataResult) . '</strong> experts ';
  867.                 $message.= 'located in <strong>countries with names</strong> starting with "<strong>' self::cleaned($searchParams['countryName']) . '</strong>".';
  868.                 if (isset($searchParams['referrer'])
  869.                     && ($searchParams['referrer'] == 'map')
  870.                 ) {
  871.                     $message 'There are <strong>' count($dataResult) . '</strong> experts ';
  872.                     $message.= 'located in  "<strong>' self::cleaned($searchParams['countryName']) . '</strong>".';
  873.                 }
  874.             }
  875.         }
  876.         if ($searchParams['expertSubject'] != '') {
  877.             $stmtInd $connection->prepare("
  878.                     SELECT 
  879.                         'experts' AS type, 
  880.                         i.status, 
  881.                         i.id_ind, 
  882.                         concat(i.fname,' ',i.sname) AS name,
  883.                         i.fname,
  884.                         i.mname,
  885.                         i.sname,
  886.                         inst_name,
  887.                         jobtitle,
  888.                         i.addr_1,
  889.                         i.addr_2,
  890.                         i.city,
  891.                         i.state,
  892.                         i.postcode,
  893.                         inst.inst_address,
  894.                         inst.addr_2 AS instAddr2,
  895.                         inst.city AS instCity,
  896.                         inst.state AS instState,
  897.                         inst.postcode AS instPostCode, 
  898.                         use_inst_addr, 
  899.                         deceased, 
  900.                         retired, 
  901.                         quality_checked, 
  902.                         (IF(use_inst_addr = 1, ic.country, c.country)) AS country, 
  903.                         'zz' AS instType, 
  904.                         'zz' AS eventtype, 
  905.                         '' AS inst_logo
  906.                     FROM 
  907.                         indiv i
  908.                         LEFT JOIN countries c on c.id_country =  i.country_code
  909.                         LEFT JOIN indiv_institution ii on ii.id_ind = i.id_ind
  910.                         LEFT JOIN institutions inst on inst.id_inst = ii.id_inst
  911.                         LEFT JOIN indiv_subjects ins on ins.id_ind = i.id_ind
  912.                         LEFT JOIN countries ic on ic.id_country =  inst.country_code
  913.                     WHERE 
  914.                         (ins.id_sub = :expertSubject)
  915.                         " $searchParams['indivStatus'] . 
  916.                     ORDER BY sname ASC
  917.                 ');
  918.             $stmtInd->bindValue('expertSubject'$searchParams['expertSubject']);
  919.             $stmtInd->execute();
  920.             $dataResult $stmtInd->fetchAll();
  921.             $sortFields['jobtitle'] = 'Position';
  922.             $message 'There are <strong>' count($dataResult) . '</strong> experts ';
  923.             $message.= "working in \"<strong>" self::getSubjectAreaById($searchParams['expertSubject'], $doctrine) . "</strong>\".";
  924.         }
  925.         if ($searchParams['expertRegion'] != '') {
  926.             $stmtInd $connection->prepare("
  927.                     SELECT 
  928.                         'experts' AS type, 
  929.                         i.status, 
  930.                         i.id_ind, 
  931.                         concat(i.fname,' ',i.sname) AS name,
  932.                         i.fname,
  933.                         i.mname,
  934.                         i.sname,
  935.                         inst_name,
  936.                         jobtitle,
  937.                         i.addr_1,
  938.                         i.addr_2,
  939.                         i.city,
  940.                         i.state,
  941.                         i.postcode,
  942.                         inst.inst_address,
  943.                         inst.addr_2 AS instAddr2,
  944.                         inst.city AS instCity,
  945.                         inst.state AS instState,
  946.                         inst.postcode AS instPostCode, 
  947.                         use_inst_addr, 
  948.                         deceased, 
  949.                         retired, 
  950.                         quality_checked, 
  951.                         (IF(use_inst_addr = 1, ic.country, c.country)) AS country, 
  952.                         'zz' AS instType, 
  953.                         'zz' AS eventtype, 
  954.                         '' AS inst_logo
  955.                     FROM 
  956.                         indiv i
  957.                         LEFT JOIN countries c on c.id_country =  i.country_code
  958.                         LEFT JOIN indiv_institution ii on ii.id_ind = i.id_ind
  959.                         LEFT JOIN institutions inst on inst.id_inst = ii.id_inst
  960.                         LEFT JOIN countries ic on ic.id_country =  inst.country_code
  961.                     WHERE 
  962.                         FIND_IN_SET (
  963.                             :expertRegion, 
  964.                             i.studyregion
  965.                         )
  966.                         " $searchParams['indivStatus'] . 
  967.                     ORDER BY sname ASC
  968.                 ');
  969.             $stmtInd->bindValue('expertRegion'$searchParams['expertRegion']);
  970.             $stmtInd->execute();
  971.             $dataResult $stmtInd->fetchAll();
  972.             $sortFields['jobtitle'] = 'Position';
  973.             $message 'There are <strong>' count($dataResult) . '</strong> experts ';
  974.             $message.= "working in the \"<strong>" self::getSeaRegionById($searchParams['expertRegion'], $doctrine) . "</strong>\".";
  975.         }
  976.         if ($searchParams['instName'] != '') {
  977.             $stmtInst $connection->prepare("
  978.                     SELECT 
  979.                         'institutions' AS type, 
  980.                         i.activated, 
  981.                         i.id_inst, 
  982.                         insttype_name AS instType, 
  983.                         inst_name,
  984.                         inst_name_eng,
  985.                         inst_address,
  986.                         addr_2,
  987.                         city AS instCity,
  988.                         state AS instState,
  989.                         postcode AS instPostCode,
  990.                         country,
  991.                         activities, 
  992.                         inst_logo, 
  993.                         'zz' AS jobtitle, 
  994.                         'zz' AS eventtype,
  995.                         'zz' AS fname, 
  996.                         'zz' AS sname
  997.                     FROM 
  998.                         institutions i
  999.                         LEFT JOIN countries c on c.id_country =  i.country_code
  1000.                         LEFT JOIN insttypes it on it.id_insttype =  i.inst_type_id
  1001.                     WHERE 
  1002.                         ( 
  1003.                             inst_name LIKE :instNamePerc
  1004.                             OR inst_name_eng LIKE :instNamePerc 
  1005.                             OR acronym LIKE :instNamePerc
  1006.                         )
  1007.                         " $searchParams['instStatus'] . 
  1008.                     ORDER BY inst_name ASC 
  1009.                 ");
  1010.             $stmtInst->bindValue('instNamePerc'$searchParams['instName'] . '%');
  1011.             $stmtInst->execute();
  1012.             $dataResult $stmtInst->fetchAll();
  1013.             $sortFields['instType'] = 'Institution Type';
  1014.             $sortFields['inst_name'] = 'Institution Name';
  1015.             $searchParams['sortby'] = 'inst_name';
  1016.             $message 'There are <strong>' count($dataResult) . '</strong> institutions ';
  1017.             $message.= 'with their name starting with "<strong>' self::cleaned($searchParams['instName']) . '</strong>".';
  1018.         }
  1019.         if ($searchParams['instCountry'] != '') {
  1020.             $stmtInst $connection->prepare("
  1021.                     SELECT 
  1022.                         'institutions' AS type, 
  1023.                         i.activated, 
  1024.                         i.id_inst, 
  1025.                         insttype_name AS instType, 
  1026.                         inst_name,
  1027.                         inst_name_eng,
  1028.                         inst_address,
  1029.                         addr_2,
  1030.                         city AS instCity,
  1031.                         state AS instState,
  1032.                         postcode AS instPostCode,
  1033.                         country,
  1034.                         activities, 
  1035.                         inst_logo, 
  1036.                         'zz' AS jobtitle, 
  1037.                         'zz' AS eventtype,
  1038.                         'zz' AS fname, 
  1039.                         'zz' AS sname
  1040.                     FROM 
  1041.                         institutions i
  1042.                         LEFT JOIN countries c on c.id_country =  i.country_code
  1043.                         LEFT JOIN insttypes it on it.id_insttype =  i.inst_type_id
  1044.                     WHERE 
  1045.                         ( c.country LIKE :instCountryPerc )
  1046.                         " $searchParams['instStatus'] . 
  1047.                     ORDER BY inst_name ASC 
  1048.                 ');
  1049.             $stmtInst->bindValue('instCountryPerc'$searchParams['instCountry'] . '%');
  1050.             $stmtInst->execute();
  1051.             $dataResult $stmtInst->fetchAll();
  1052.             $sortFields['instType'] = 'Institution Type';
  1053.             $sortFields['inst_name'] = 'Institution Name';
  1054.             $searchParams['sortby'] = 'inst_name';
  1055.             $message 'There are <strong>' count($dataResult) . '</strong> institutions ';
  1056.             $message.= 'located in countries with names starting with "<strong>' self::cleaned($searchParams['instCountry']) . '</strong>".';
  1057.         }
  1058.         if ($searchParams['instRegion'] != '') {
  1059.             $stmtInst $connection->prepare("
  1060.                     SELECT 
  1061.                         'institutions' AS type, 
  1062.                         i.activated, 
  1063.                         i.id_inst, 
  1064.                         insttype_name AS instType, 
  1065.                         inst_name,
  1066.                         inst_name_eng,
  1067.                         inst_address,
  1068.                         addr_2,
  1069.                         city AS instCity,
  1070.                         state AS instState,
  1071.                         postcode AS instPostCode,
  1072.                         country,
  1073.                         activities,
  1074.                         inst_logo, 
  1075.                         'zz' AS jobtitle, 
  1076.                         'zz' AS eventtype,
  1077.                         'zz' AS fname, 
  1078.                         'zz' AS sname
  1079.                     FROM 
  1080.                         institutions i
  1081.                         LEFT JOIN countries c on c.id_country =  i.country_code
  1082.                         LEFT JOIN insttypes it on it.id_insttype =  i.inst_type_id
  1083.                     WHERE 
  1084.                         FIND_IN_SET (
  1085.                             :instRegion, 
  1086.                             i.inst_region
  1087.                         )
  1088.                         " $searchParams['instStatus'] . 
  1089.                     ORDER BY inst_name ASC 
  1090.                 ');
  1091.             $stmtInst->bindValue('instRegion'$searchParams['instRegion']);
  1092.             $stmtInst->execute();
  1093.             $dataResult $stmtInst->fetchAll();
  1094.             $sortFields['instType'] = 'Institution Type';
  1095.             $sortFields['inst_name'] = 'Institution Name';
  1096.             $searchParams['sortby'] = 'inst_name';
  1097.             $message 'There are <strong>' count($dataResult) . '</strong> institutions ';
  1098.             $message.= 'working in the "<strong>' self::getSeaRegionById($searchParams['instRegion'], $doctrine) . '</strong>".';
  1099.         }
  1100.         if ($searchParams['instType'] != '') {
  1101.             $stmtInst $connection->prepare("
  1102.                     SELECT 
  1103.                         'institutions' AS type, 
  1104.                         i.activated, 
  1105.                         i.id_inst, 
  1106.                         insttype_name AS instType, 
  1107.                         inst_name,
  1108.                         inst_name_eng,
  1109.                         inst_address,
  1110.                         addr_2,
  1111.                         city AS instCity,
  1112.                         state AS instState,
  1113.                         postcode AS instPostCode,
  1114.                         country,
  1115.                         activities,
  1116.                         inst_logo, 
  1117.                         'zz' AS jobtitle, 
  1118.                         'zz' AS eventtype,
  1119.                         'zz' AS fname, 
  1120.                         'zz' AS sname
  1121.                     FROM 
  1122.                         institutions i
  1123.                         LEFT JOIN countries c on c.id_country =  i.country_code
  1124.                         LEFT JOIN insttypes it on it.id_insttype =  i.inst_type_id
  1125.                     WHERE 
  1126.                         ( i.inst_type_id = :instType) 
  1127.                         " $searchParams['instStatus'] . 
  1128.                     ORDER BY inst_name ASC 
  1129.                 ');
  1130.             $stmtInst->bindValue('instType'$searchParams['instType']);
  1131.             $stmtInst->execute();
  1132.             $dataResult $stmtInst->fetchAll();
  1133.             $sortFields['instType'] = 'Institution Type';
  1134.             $sortFields['inst_name'] = 'Institution Name';
  1135.             $searchParams['sortby'] = 'inst_name';
  1136.             $message 'There are <strong>' count($dataResult) . '</strong> institutions ';
  1137.             $message.= 'with type "<strong>' SearchController::getInstituteTypeById($searchParams['instType'], $doctrine) . '</strong>".';
  1138.         }
  1139.         //status=0 means everything is ok
  1140.         return array(
  1141.             'status' => $status,
  1142.             'dataResult' => $dataResult,
  1143.             'sortFields' => $sortFields,
  1144.             'sortby' => $searchParams['sortby'],
  1145.             'message' => $message,
  1146.             'searchResultType' => $searchResultType
  1147.         );
  1148.     }
  1149.     /**
  1150.      * get the results for the search when action=advSearch
  1151.      *
  1152.      * @param array  $searchParams all the search parameters that have been passed in this request
  1153.      * @param object $doctrine
  1154.      *
  1155.      * @return array with the results
  1156.      */
  1157.     public static function getAdvSearchResults(array $searchParamsobject $doctrine): array
  1158.     {
  1159.         $connection $doctrine->getManager()->getConnection();
  1160.         $searchResultType 'Advanced Search Results';
  1161.         $dataResult = array();
  1162.         $sortBy $searchParams['sortby'];
  1163.         //$searchParams['sortby'] = 'sname';
  1164.         $messageStart 'Your search for ';
  1165.         $messagePart '';
  1166.         $sortFields $searchParams['sortFields'];
  1167.         if (isset($searchParams['searchType'])
  1168.             && isset($searchParams['searchType'][0])
  1169.             && $searchParams['searchType'][0] === 'all'
  1170.             && !count($searchParams['queryString'])
  1171.         ) {
  1172.             //we don't want a search for all types with specific search params
  1173.             return array(
  1174.                 'status' => 0,
  1175.                 'dataResult' => array(),
  1176.                 'sortFields' => $sortFields,
  1177.                 'sortby' => 'relevance',
  1178.                 'message' => 'we cannot show the results, too many results!!!!',
  1179.                 'searchResultType' => $searchResultType
  1180.             );
  1181.         } elseif (isset($searchParams['searchType'])
  1182.             && count($searchParams['searchType']) > 0
  1183.         ) {
  1184.             //where to store the results for the different types
  1185.             $dataResultInd = array();
  1186.             $dataResultInst = array();
  1187.             $dataResultEvnt = array();
  1188.             $dataResultDocument = array();
  1189.             $instCondCnt 1;
  1190.             $endQueryInst false;
  1191.             $endQueryInd false;
  1192.             $endQueryEvent false;
  1193.             $endQueryDocument false;
  1194.             $whereInd ' WHERE ( 1=1 ';
  1195.             $whereInst ' WHERE ( 1=1 ';
  1196.             $whereEvent ' WHERE ( 1=1 ';
  1197.             $whereDocument '';
  1198.             $joinsInd '';
  1199.             $searchParams['searchToggle'][-1] = ' AND ';
  1200.             $operationInd ' AND ';
  1201.             $operationInst ' AND ';
  1202.             $operationEvnt ' AND ';
  1203.             $operation '';
  1204.             foreach ($searchParams['searchType'] as $key => $type) {
  1205.                 if (isset($searchParams['searchKeywords'])
  1206.                     && isset($searchParams['searchKeywords'][$key])
  1207.                 ) {
  1208.                     if (is_array($searchParams['searchKeywords'][$key])) {
  1209.                         foreach($searchParams['searchKeywords'][$key] as $value) {
  1210.                             $string[] = addcslashes($value,'\'');
  1211.                         }
  1212.                     } else {
  1213.                         $string addcslashes($searchParams['searchKeywords'][$key], '\'');
  1214.                     }
  1215.                 } else {
  1216.                     $string '';
  1217.                 }
  1218.                 //what kind of filter are we using
  1219.                 //there should be one at least, except for type=all
  1220.                 if (isset($searchParams['searchFilter'][$key])) {
  1221.                     $value $searchParams['searchFilter'][$key] ?? '';
  1222.                 } else {
  1223.                     $value $searchParams['searchFilter'][array_key_last($searchParams['searchFilter'])];
  1224.                 }
  1225.                 if ($type != 'all'
  1226.                     && $value == ''
  1227.                 ) {
  1228.                     continue;
  1229.                 }
  1230.                 if ($type == 'experts') {
  1231.                     if ($sortBy == '') {
  1232.                         $sortBy 'sname';
  1233.                     }
  1234.                     $bindValues[$type][$key] = array();
  1235.                     $selectQryInd "
  1236.                             SELECT 
  1237.                                 'experts' AS type, 
  1238.                                 i.status, 
  1239.                                 i.id_ind, 
  1240.                                 concat(i.fname,' ',i.sname) AS name,
  1241.                                 i.fname,
  1242.                                 i.mname,
  1243.                                 i.sname,
  1244.                                 inst.inst_name,
  1245.                                 i.jobtitle,
  1246.                                 i.addr_1,
  1247.                                 i.addr_2,
  1248.                                 i.city,
  1249.                                 i.state,
  1250.                                 i.postcode,
  1251.                                 inst.inst_address,
  1252.                                 inst.addr_2 AS instAddr2,
  1253.                                 inst.city AS instCity,
  1254.                                 inst.state AS instState,
  1255.                                 inst.postcode AS instPostCode, 
  1256.                                 i.use_inst_addr, 
  1257.                                 deceased, 
  1258.                                 retired, 
  1259.                                 quality_checked, 
  1260.                                 (CASE WHEN i.use_inst_addr = 1 THEN ic.country ELSE c.country END) AS country, 
  1261.                                 'zz' AS instType, 
  1262.                                 'zz' AS eventtype, 
  1263.                                 '' AS inst_logo ";
  1264.                     $commonJoins ' LEFT JOIN countries c on c.id_country =  i.country_code
  1265.                                 LEFT JOIN indiv_institution ii on ii.id_ind = i.id_ind
  1266.                                 LEFT JOIN institutions inst on inst.id_inst = ii.id_inst
  1267.                                 LEFT JOIN countries ic on ic.id_country =  inst.country_code ';
  1268.                     $matchOrder '';
  1269.                     switch ($value) {
  1270.                         case 'Name contains':
  1271.                             $newString self::fulltext($string);
  1272.                             $selectQryInd .= " , MATCH (fname, mname, sname) AGAINST ('$newString' IN BOOLEAN MODE) AS relevance ";
  1273.                             $whereInd .= ' ' $operationInd " ( i.fname LIKE  :percString$key
  1274.                                                     OR i.fname LIKE  :stringPerc$key
  1275.                                                     OR i.mname LIKE  :percString$key
  1276.                                                     OR i.mname LIKE  :stringPerc$key
  1277.                                                     OR i.sname LIKE  :percString$key
  1278.                                                     OR i.sname LIKE  :stringPerc$key
  1279.                                                     OR MATCH (fname, mname, sname) AGAINST ('$newString' IN BOOLEAN MODE) ) ";
  1280.                             $messagePart .= "$operation \"<strong>" self::cleaned($string) . "</strong>\" ";
  1281.                             $messagePart .= "in $type ";
  1282.                             $messagePart .= "using filter \"<strong>" self::readableFilter($value) . "</strong>\" ";
  1283.                             $matchOrder ' ORDER BY relevance DESC';
  1284.                             //remember what value has to be bound to what placeholder
  1285.                             $bindValues[$type][$key] = array(
  1286.                                 'percString' $key => '%' $string,
  1287.                                 'stringPerc' $key => $string '%'
  1288.                             );
  1289.                             break;
  1290.                         case 'First name contains':
  1291.                             $whereInd .= ' ' $operationInd " ( 
  1292.                                                        i.fname LIKE  :percString$key
  1293.                                                     OR i.fname LIKE  :stringPerc$key ) ";
  1294.                             $messagePart .= "$operation \"<strong>" self::cleaned($string) . "</strong>\" ";
  1295.                             $messagePart .= "in $type ";
  1296.                             $messagePart .= "using filter \"<strong>" self::readableFilter($value) . "</strong>\" ";
  1297.                             //remember what value has to be bound to what placeholder
  1298.                             $bindValues[$type][$key] = array(
  1299.                                 'percString' $key => '%' $string,
  1300.                                 'stringPerc' $key => $string '%'
  1301.                             );
  1302.                             break;
  1303.                         case 'Last name contains':
  1304.                             $whereInd .= ' ' $operationInd "( 
  1305.                                                        i.sname LIKE  :percString$key
  1306.                                                     OR i.sname LIKE  :stringPerc$key ) ";
  1307.                             $messagePart .= "$operation \"<strong>" self::cleaned($string) . "</strong>\" ";
  1308.                             $messagePart .= "in $type ";
  1309.                             $messagePart .= "using filter \"<strong>" self::readableFilter($value) . "</strong>\" ";
  1310.                             //remember what value has to be bound to what placeholder
  1311.                             $bindValues[$type][$key] = array(
  1312.                                 'percString' $key => '%' $string,
  1313.                                 'stringPerc' $key => $string '%'
  1314.                             );
  1315.                             break;
  1316.                         case 'Current address contains':
  1317.                             $whereInd .= ' ' $operationInd 
  1318.                                         ( CASE 
  1319.                                             WHEN i.use_inst_addr = 1 THEN 
  1320.                                                 inst.inst_address LIKE  :percStringPerc$key
  1321.                                                     OR inst.addr_2 LIKE  :percStringPerc$key
  1322.                                                     OR inst.city LIKE  :percStringPerc$key
  1323.                                                     OR inst.state LIKE  :percStringPerc$key
  1324.                                             ELSE 
  1325.                                                 i.addr_1 LIKE  :percStringPerc$key
  1326.                                                     OR i.addr_2 LIKE  :percStringPerc$key
  1327.                                                     OR i.city LIKE  :percStringPerc$key
  1328.                                                     OR i.state LIKE  :percStringPerc$key
  1329.                                                     OR i.private_address LIKE  :percStringPerc$key
  1330.                                             END
  1331.                                         ) ";
  1332.                             $messagePart .= "$operation \"<strong>" self::cleaned($string) . "</strong>\" ";
  1333.                             $messagePart .= "in $type ";
  1334.                             $messagePart .= "using filter \"<strong>" self::readableFilter($value) . "</strong>\" ";
  1335.                             //remember what value has to be bound to what placeholder
  1336.                             $bindValues[$type][$key] = array(
  1337.                                 'percStringPerc' $key => '%' $string '%'
  1338.                             );
  1339.                             break;
  1340.                         /*
  1341.                          * should not be used
  1342.                          * #680
  1343.                         case 'Current/Previous addresses contain':
  1344.                             $joinsInd .= ' LEFT JOIN previous_addresses pa on pa.id_record = i.id_ind ';
  1345.                             $whereInd .= ' ' . $operationInd . "
  1346.                                             ( CASE
  1347.                                                 WHEN i.use_inst_addr = 1 THEN
  1348.                                                     inst.inst_address LIKE  :percStringPerc$key
  1349.                                                         OR inst.addr_2 LIKE  :percStringPerc$key
  1350.                                                         OR inst.city LIKE  :percStringPerc$key
  1351.                                                         OR inst.state LIKE  :percStringPerc$key
  1352.                                                 ELSE
  1353.                                                     i.addr_1 LIKE  :percStringPerc$key
  1354.                                                         OR i.addr_2 LIKE  :percStringPerc$key
  1355.                                                         OR i.city LIKE  :percStringPerc$key
  1356.                                                         OR i.state LIKE  :percStringPerc$key
  1357.                                                         OR i.private_address LIKE  :percStringPerc$key
  1358.                                                         OR pa.city LIKE  :percStringPerc$key
  1359.                                                         OR pa.state LIKE  :percStringPerc$key
  1360.                                                         OR pa.address LIKE  :percStringPerc$key
  1361.                                                 END
  1362.                                             ) ";
  1363.                             $messagePart .= "$operation \"<strong>" . self::cleaned($string) . "</strong>\" ";
  1364.                             $messagePart .= "in $type ";
  1365.                             $messagePart .= "using filter \"<strong>" . self::readableFilter($value) . "</strong>\" ";
  1366.                             //remember what value has to be bound to what placeholder
  1367.                             $bindValues[$type][$key] = array(
  1368.                                 'percStringPerc' . $key => '%' . $string . '%'
  1369.                             );
  1370.                             break;
  1371.                         */
  1372.                         case 'Phone/Fax contains':
  1373.                             $whereInd .= ' ' $operationInd " (
  1374.                                                        i.tel LIKE  :percStringPerc$key
  1375.                                                     OR i.fax LIKE  :percStringPerc$key
  1376.                                                     )";
  1377.                             $messagePart .= "$operation \"<strong>" self::cleaned($string) . "</strong>\" ";
  1378.                             $messagePart .= "in $type ";
  1379.                             $messagePart .= "using filter \"<strong>" self::readableFilter($value) . "</strong>\" ";
  1380.                             //remember what value has to be bound to what placeholder
  1381.                             $bindValues[$type][$key] = array(
  1382.                                 'percStringPerc' $key => '%' $string '%'
  1383.                             );
  1384.                             break;
  1385.                         case 'Email contains':
  1386.                             $whereInd .= ' ' $operationInd " (
  1387.                                                        i.email1 LIKE  :percStringPerc$key
  1388.                                                     OR i.email2 LIKE  :percStringPerc$key
  1389.                                                     OR i.email3 LIKE  :percStringPerc$key
  1390.                                                     ) ";
  1391.                             $messagePart .= "$operation \"<strong>" self::cleaned($string) . "</strong>\" ";
  1392.                             $messagePart .= "in $type ";
  1393.                             $messagePart .= "using filter \"<strong>" self::readableFilter($value) . "</strong>\" ";
  1394.                             //remember what value has to be bound to what placeholder
  1395.                             $bindValues[$type][$key] = array(
  1396.                                 'percStringPerc' $key => '%' $string '%'
  1397.                             );
  1398.                             break;
  1399.                         case 'Website URL contains':
  1400.                             $whereInd .= ' ' $operationInd " (
  1401.                                                        i.url1 LIKE  :percStringPerc$key
  1402.                                                     OR i.url2 LIKE  :percStringPerc$key
  1403.                                                     OR i.url3 LIKE  :percStringPerc$key
  1404.                                                     ) ";
  1405.                             $messagePart .= "$operation \"<strong>" self::cleaned($string) . "</strong>\" ";
  1406.                             $messagePart .= "in $type ";
  1407.                             $messagePart .= "using filter \"<strong>" self::readableFilter($value) . "</strong>\" ";
  1408.                             //remember what value has to be bound to what placeholder
  1409.                             $bindValues[$type][$key] = array(
  1410.                                 'percStringPerc' $key => '%' $string '%'
  1411.                             );
  1412.                             break;
  1413.                         case 'Country is':
  1414.                             $whereInd .= ' ' $operationInd 
  1415.                                             ( CASE 
  1416.                                                 WHEN i.use_inst_addr = 1 THEN
  1417.                                                        inst.country_code = $string
  1418.                                                 ELSE
  1419.                                                        i.country_code = $string
  1420.                                                 END
  1421.                                             ) ";
  1422.                             $messagePart .= "$operation $type from  \"<strong>" self::getCountryById($string$doctrine) . "</strong>\" ";
  1423.                             break;
  1424.                         case 'In multiple countries':
  1425.                             $string implode(','$string);
  1426.                             $whereInd .= ' ' $operationInd 
  1427.                                             ( CASE 
  1428.                                                 WHEN i.use_inst_addr = 1 THEN
  1429.                                                     inst.country_code in ($string)
  1430.                                                 ELSE
  1431.                                                     i.country_code in ($string)
  1432.                                                 END
  1433.                                             ) ";
  1434.                             $messagePart .= "$operation $type from  \"<strong>" implode(', 'self::getMultipleCountryById($string$doctrine)) . "</strong>\" ";
  1435.                             break;
  1436.                         case 'Sea regions of study is':
  1437.                             $whereInd .= ' ' $operationInd " (
  1438.                                                        FIND_IN_SET ('$string', i.studyregion)
  1439.                                                     ) ";
  1440.                             $messagePart .= "$operation \"<strong>" self::getRegionById($string$doctrine) . "</strong>\" ";
  1441.                             $messagePart .= "in $type ";
  1442.                             $messagePart .= "using filter \"<strong>" self::readableFilter($value) . "</strong>\" ";
  1443.                             break;
  1444.                         case 'Member of group or sub-group':
  1445.                             $groups implode(','self::getChildGroup($string$doctrine));
  1446.                             $joinsInd .= ' LEFT JOIN member_groups mg ON mg.id_ind = i.id_ind ';
  1447.                             $whereInd .= ' ' $operationInd " (
  1448.                                                         mg.id_group IN ($groups)
  1449.                                                     ) ";
  1450.                             $messagePart .= "$operation \"<strong>" self::getGroupById($string$doctrine) . "</strong>\" ";
  1451.                             $messagePart .= "in $type ";
  1452.                             $messagePart .= "using filter \"<strong>Member of group OR sub-group</strong>\" ";
  1453.                             break;
  1454.                         case 'Job type is':
  1455.                             $joinsInd .= " LEFT JOIN indiv_jobtype ij ON ij.id_ind = i.id_ind ";
  1456.                             $whereInd .= ' ' $operationInd " (
  1457.                                                        id_job = $string
  1458.                                                     ) ";
  1459.                             $messagePart .= "$operation \"<strong>" self::getJobTypesById($string$doctrine) . "</strong>\" ";
  1460.                             $messagePart .= "in $type ";
  1461.                             $messagePart .= "using filter \"<strong>" self::readableFilter($value) . "</strong>\" ";
  1462.                             break;
  1463.                         case 'Job title contains':
  1464.                             $whereInd .= ' ' $operationInd " (
  1465.                                                        i.jobtitle LIKE  :percString$key
  1466.                                                     OR i.jobtitle LIKE  :stringPerc$key
  1467.                                                     ) ";
  1468.                             $messagePart .= "$operation \"<strong>" self::cleaned($string) . "</strong>\" ";
  1469.                             $messagePart .= "in $type ";
  1470.                             $messagePart .= "using filter \"<strong>" self::readableFilter($value) . "</strong>\" ";
  1471.                             //remember what value has to be bound to what placeholder
  1472.                             $bindValues[$type][$key] = array(
  1473.                                 'percString' $key => '%' $string,
  1474.                                 'stringPerc' $key => $string '%'
  1475.                             );
  1476.                             break;
  1477.                         case 'Department contains':
  1478.                             $whereInd .= ' ' $operationInd " (
  1479.                                                        i.dept LIKE  :percString$key
  1480.                                                     OR i.dept LIKE  :stringPerc$key
  1481.                                                     ) ";
  1482.                             $messagePart .= "$operation \"<strong>" self::cleaned($string) . "</strong>\" ";
  1483.                             $messagePart .= "in $type ";
  1484.                             $messagePart .= "using filter \"<strong>" self::readableFilter($value) . "</strong>\" ";
  1485.                             //remember what value has to be bound to what placeholder
  1486.                             $bindValues[$type][$key] = array(
  1487.                                 'percString' $key => '%' $string,
  1488.                                 'stringPerc' $key => $string '%'
  1489.                             );
  1490.                             break;
  1491.                         case 'Institution name contains':
  1492.                             $whereInd .= ' ' $operationInd " (
  1493.                                                        inst.inst_name LIKE  :percStringPerc$key
  1494.                                                     OR inst.inst_name_eng LIKE  :percStringPerc$key
  1495.                                                     OR inst.acronym LIKE  :percStringPerc$key
  1496.                                                     ) ";
  1497.                             $messagePart .= "$operation \"<strong>" self::cleaned($string) . "</strong>\" ";
  1498.                             $messagePart .= "in $type ";
  1499.                             $messagePart .= "using filter \"<strong>" self::readableFilter($value) . "</strong>\" ";
  1500.                             //remember what value has to be bound to what placeholder
  1501.                             $bindValues[$type][$key] = array(
  1502.                                 'percStringPerc' $key => '%' $string '%'
  1503.                             );
  1504.                             break;
  1505.                         case 'Subject Area is':
  1506.                             $joinsInd .= " LEFT JOIN indiv_meta im ON im.indiv_id = i.id_ind ";
  1507.                             $whereInd .= ' ' $operationInd " (
  1508.                                                        im.meta_option = 'researcharea'
  1509.                                                     AND im.meta_value LIKE  :percStringPerc$key
  1510.                                                     ) ";
  1511.                             $messagePart .= "$operation \"<strong>" self::cleaned($string) . "</strong>\" ";
  1512.                             $messagePart .= "in $type ";
  1513.                             $messagePart .= "using filter \"<strong>" self::readableFilter($value) . "</strong>\" ";
  1514.                             //remember what value has to be bound to what placeholder
  1515.                             $bindValues[$type][$key] = array(
  1516.                                 'percStringPerc' $key => '%' $string '%'
  1517.                             );
  1518.                             break;
  1519.                         case 'Activities include':
  1520.                             $whereInd .= ' ' $operationInd " (
  1521.                                                        i.activeng LIKE  :percStringPerc$key
  1522.                                                     ) ";
  1523.                             $messagePart .= "$operation \"<strong>" self::cleaned($string) . "</strong>\" ";
  1524.                             $messagePart .= "in $type ";
  1525.                             $messagePart .= "using filter \"<strong>" self::readableFilter($value) . "</strong>\" ";
  1526.                             //remember what value has to be bound to what placeholder
  1527.                             $bindValues[$type][$key] = array(
  1528.                                 'percStringPerc' $key => '%' $string '%'
  1529.                             );
  1530.                             break;
  1531.                         case 'Citations include':
  1532.                             $joinsInd .= " LEFT JOIN indiv_meta im ON im.indiv_id = i.id_ind ";
  1533.                             $whereInd .= ' ' $operationInd " (
  1534.                                                         (
  1535.                                                             im.meta_option = 'researcharea'
  1536.                                                             AND im.meta_value LIKE  :percStringPerc$key
  1537.                                                         )
  1538.                                                         OR (
  1539.                                                             im.meta_option = 'other-citation'
  1540.                                                             AND im.meta_value LIKE  :percStringPerc$key
  1541.                                                         )
  1542.                                                         OR (
  1543.                                                             im.meta_option = 'researchgate'
  1544.                                                             AND im.meta_value LIKE  :percStringPerc$key
  1545.                                                         )
  1546.                                                     ) ";
  1547.                             $messagePart .= "$operation \"<strong>" self::cleaned($string) . "</strong>\" ";
  1548.                             $messagePart .= "in $type ";
  1549.                             $messagePart .= "using filter \"<strong>" self::readableFilter($value) . "</strong>\" ";
  1550.                             //remember what value has to be bound to what placeholder
  1551.                             $bindValues[$type][$key] = array(
  1552.                                 'percStringPerc' $key => '%' $string '%'
  1553.                             );
  1554.                             break;
  1555.                         case 'Working languages includes':
  1556.                             $whereInd .= ' ' $operationInd " (
  1557.                                                        FIND_IN_SET ('$string', i.languages)
  1558.                                                     ) ";
  1559.                             $messagePart .= "$operation \"<strong>" self::cleaned($string) . "</strong>\" ";
  1560.                             $messagePart .= "in $type ";
  1561.                             $messagePart .= "using filter \"<strong>" self::readableFilter($value) . "</strong>\" ";
  1562.                             break;
  1563.                         case 'Degree contains':
  1564.                             $whereInd .= ' ' $operationInd " (
  1565.                                                        i.degree LIKE  :percStringPerc$key
  1566.                                                     ) ";
  1567.                             $messagePart .= "$operation \"<strong>" self::cleaned($string) . "</strong>\" ";
  1568.                             $messagePart .= "in $type ";
  1569.                             $messagePart .= "using filter \"<strong>" self::readableFilter($value) . "</strong>\" ";
  1570.                             //remember what value has to be bound to what placeholder
  1571.                             $bindValues[$type][$key] = array(
  1572.                                 'percStringPerc' $key => '%' $string '%'
  1573.                             );
  1574.                             break;
  1575.                         case 'Is retired':
  1576.                             $whereInd .= ' ' $operationInd " (
  1577.                                                        i.retired = $string
  1578.                                                     ) ";
  1579.                             $messagePart .= "$operation experts who are \"<strong>Retired</strong>\" ";
  1580.                             break;
  1581.                         case 'Is deceased':
  1582.                             $whereInd .= ' ' $operationInd " (
  1583.                                                        i.deceased = $string
  1584.                                                     ) ";
  1585.                             $messagePart .= "$operation  experts who are \"<strong>Deceased</strong>\" ";
  1586.                             break;
  1587.                         case 'Is quality checked':
  1588.                             $whereInd .= ' ' $operationInd " (
  1589.                                                        i.quality_checked = $string
  1590.                                                     ) ";
  1591.                             $messagePart .= "$operation experts who are \"<strong>Quality checked</strong>\" ";
  1592.                             break;
  1593.                         case 'Comment contains':
  1594.                             $whereInd .= ' ' $operationInd " (
  1595.                                                        i.comments LIKE  :percStringPerc$key
  1596.                                                     ) ";
  1597.                             $messagePart .= "$operation \"<strong>" self::cleaned($string) . "</strong>\" ";
  1598.                             $messagePart .= "in $type ";
  1599.                             $messagePart .= "using filter \"<strong>" self::readableFilter($value) . "</strong>\" ";
  1600.                             //remember what value has to be bound to what placeholder
  1601.                             $bindValues[$type][$key] = array(
  1602.                                 'percStringPerc' $key => '%' $string '%'
  1603.                             );
  1604.                             break;
  1605.                         case 'Is Active':
  1606.                             $whereInd .= ' ' $operationInd " (
  1607.                                                         i.status = $string
  1608.                                                 ) ";
  1609.                             if ($string == '1') {
  1610.                                 $messagePart .= "$operation expert is <strong>Active</strong> ";
  1611.                             } else {
  1612.                                 $messagePart .= "$operation expert is <strong>not active</strong> ";
  1613.                             }
  1614.                             break;
  1615.                         case 'Gender is':
  1616.                             $whereInd .= ' ' $operationInd " ( 
  1617.                                                         i.gender = '$string'
  1618.                                                 ) ";
  1619.                             $messagePart .= "$operation $type \"<strong>" self::readableFilter($value) . "</strong>\"  \"<strong>$string</strong>\" ";
  1620.                             break;
  1621.                         case 'Do Not Invite':
  1622.                             $whereInd .= ' ' $operationInd " (
  1623.                                                         i.do_not_invite = 1
  1624.                                                 ) ";
  1625.                             $messagePart .= "$operation $type using filter \"<strong>" self::readableFilter($value) . "</strong>\" ";
  1626.                             break;
  1627.                         case 'Unique Identifier':
  1628.                             $whereInd .= ' ' $operationInd " (
  1629.                                                 i.id_ind = '$string'
  1630.                                                 ) ";
  1631.                             $messagePart .= "$operation $type having unique identifier \"<strong>$string</strong>\" ";
  1632.                             break;
  1633.                         case 'Updated':
  1634.                             $startDate = isset($searchParams['startDate'][$key]) ? "l_date_upd >= '" self::cleanDate($searchParams['startDate'][$key]) . "' " "1=1 ";
  1635.                             $endDate = isset($searchParams['endDate'][$key]) ? "and l_date_upd <= '" self::cleanDate($searchParams['endDate'][$key]) . "' " " AND 1=1 ";
  1636.                             $whereInd .= ' ' $operationInd ' ' $startDate ' ' $endDate;
  1637.                             $messagePart .= "$operation $type updated ";
  1638.                             $messagePart .= "between \"<strong>" self::cleaned($searchParams['startDate'][$key]) . "</strong>\" ";
  1639.                             $messagePart .= "and \"<strong>" self::cleaned($searchParams['endDate'][$key]) . "</strong>\" ";
  1640.                             break;
  1641.                         case 'Created':
  1642.                             $startDate = isset($searchParams['startDate'][$key]) ? "f_date_ent >= '" self::cleanDate($searchParams['startDate'][$key]) . "' " "1=1 ";
  1643.                             $endDate = isset($searchParams['endDate'][$key]) ? "and f_date_ent <= '" self::cleanDate($searchParams['endDate'][$key]) . "' " " AND 1=1 ";
  1644.                             $whereInd .= ' ' $operationInd ' ' $startDate ' ' $endDate;
  1645.                             $messagePart .= "$operation $type \"<strong>" self::readableFilter($value) . "</strong>\" ";
  1646.                             $messagePart .= "between \"<strong>" self::cleaned($searchParams['startDate'][$key]) . "</strong>\" ";
  1647.                             $messagePart .= "and \"<strong>" self::cleaned($searchParams['endDate'][$key]) . "</strong>\" ";
  1648.                             break;
  1649.                         case 'Quality last checked':
  1650.                             $startDate = isset($searchParams['startDate'][$key]) ? "quality_checked_date > '" self::cleanDate($searchParams['startDate'][$key]) . "' " "1=1 ";
  1651.                             $endDate = isset($searchParams['endDate'][$key]) ? "and quality_checked_date < '" self::cleanDate($searchParams['endDate'][$key]) . "' " " AND 1=1 ";
  1652.                             $whereInd .= ' ' $operationInd ' ' $startDate ' ' $endDate;
  1653.                             $messagePart .= "$operation $type \"<strong>" self::readableFilter($value) . "</strong>\" ";
  1654.                             $messagePart .= "between \"<strong>" self::cleaned($searchParams['startDate'][$key]) . "</strong>\" ";
  1655.                             $messagePart .= "and \"<strong>" self::cleaned($searchParams['endDate'][$key]) . "</strong>\" ";
  1656.                             break;
  1657.                         default:
  1658.                             break;
  1659.                     }
  1660.                     $operationInd $searchParams['searchToggle'][$key] ?? " OR ";
  1661.                     $operation $searchParams['searchToggle'][$key] ?? " OR ";
  1662.                 } elseif ($type == 'institutions') {
  1663.                     if ($sortBy == '') {
  1664.                         $sortBy 'name';
  1665.                     }
  1666.                     $bindValues[$type][$key] = array();
  1667.                     $selectQryInst "
  1668.                             SELECT 
  1669.                                 'institutions' AS type, 
  1670.                                 i.activated, 
  1671.                                 i.id_inst, 
  1672.                                 it.insttype_name AS instType, 
  1673.                                 i.inst_name,
  1674.                                 i.inst_name_eng,
  1675.                                 i.inst_address,
  1676.                                 i.addr_2,
  1677.                                 i.city AS instCity,
  1678.                                 i.state AS instState,
  1679.                                 i.postcode AS instPostCode,
  1680.                                 c.country,
  1681.                                 i.activities,
  1682.                                 i.inst_logo,
  1683.                                 i.parent_id,   
  1684.                                 '' AS jobtitle, 
  1685.                                 '' AS eventtype,
  1686.                                 '' AS fname, 
  1687.                                 '' AS sname
  1688.                             FROM 
  1689.                                 institutions i
  1690.                         ";
  1691.                     $joinsInst ' LEFT JOIN countries c ON c.id_country =  i.country_code
  1692.                                         LEFT JOIN insttypes it ON it.id_insttype =  i.inst_type_id ';
  1693.                     switch ($value) {
  1694.                         case 'Name contains':
  1695.                             $whereInst .= ' ' $operationInst " ( 
  1696.                                                    i.inst_name LIKE  :percStringPerc$key
  1697.                                                 OR i.inst_name_eng LIKE  :percStringPerc$key
  1698.                                                 OR i.acronym LIKE  :percStringPerc$key
  1699.                                                         ) ";
  1700.                             $messagePart .= "$operation \"<strong>" self::cleaned($string) . "</strong>\" ";
  1701.                             $messagePart .= "in $type ";
  1702.                             $messagePart .= "using filter \"<strong>" self::readableFilter($value) . "</strong>\" ";
  1703.                             //remember what value has to be bound to what placeholder
  1704.                             $bindValues[$type][$key] = array(
  1705.                                 'percStringPerc' $key => '%' $string '%'
  1706.                             );
  1707.                             break;
  1708.                         case 'Current address contains':
  1709.                             $whereInst .= ' ' $operationInst " ( 
  1710.                                                           i.inst_address LIKE  :percStringPerc$key
  1711.                                                     OR i.addr_2 LIKE  :percStringPerc$key
  1712.                                                     OR i.city LIKE  :percStringPerc$key
  1713.                                                     OR i.state LIKE  :percStringPerc$key
  1714.                                                        ) ";
  1715.                             $messagePart .= "$operation \"<strong>" self::cleaned($string) . "</strong>\" ";
  1716.                             $messagePart .= "in $type ";
  1717.                             $messagePart .= "using filter \"<strong>" self::readableFilter($value) . "</strong>\" ";
  1718.                             //remember what value has to be bound to what placeholder
  1719.                             $bindValues[$type][$key] = array(
  1720.                                 'percStringPerc' $key => '%' $string '%'
  1721.                             );
  1722.                             break;
  1723.                         /*
  1724.                          * #680
  1725.                          *
  1726.                         case 'Current/Previous addresses contain':
  1727.                             $whereInst .= ' ' . $operationInst . " (
  1728.                                                           i.inst_address LIKE  :percString$key
  1729.                                                     OR i.addr_2 LIKE  :percStringPerc$key
  1730.                                                     OR i.city LIKE  :percStringPerc$key
  1731.                                                     OR i.state LIKE  :percStringPerc$key
  1732.                                                        ) ";
  1733.                             $messagePart .= "$operation \"<strong>" . self::cleaned($string) . "</strong>\" ";
  1734.                             $messagePart .= "in $type ";
  1735.                             $messagePart .= "using filter \"<strong>" . self::readableFilter($value) . "</strong>\" ";
  1736.                             //remember what value has to be bound to what placeholder
  1737.                             $bindValues[$type][$key] = array(
  1738.                                 'percString' . $key => '%' . $string,
  1739.                                 'percStringPerc' . $key => '%' . $string . '%'
  1740.                             );
  1741.                             break;
  1742.                         */
  1743.                         case 'Country is':
  1744.                             $whereInst .= ' ' $operationInst " ( 
  1745.                                                           i.country_code = $string
  1746.                                                        ) ";
  1747.                             $messagePart .= "$operation $type ";
  1748.                             $messagePart .= "from  \"<strong>" self::getCountryById($string$doctrine) . "</strong>\" ";
  1749.                             break;
  1750.                         case 'Sea regions of study is':
  1751.                             $whereInst .= ' ' $operationInst " ( 
  1752.                                                           FIND_IN_SET ('$string', i.inst_region)
  1753.                                                        ) ";
  1754.                             $messagePart .= "$operation $type ";
  1755.                             $messagePart .= "having $value \"<strong>" self::getRegionById($string$doctrine) . "</strong>\" ";
  1756.                             break;
  1757.                         case 'Type is':
  1758.                             $whereInst .= ' ' $operationInst " ( 
  1759.                                                           i.inst_type_id = $string
  1760.                                                        ) ";
  1761.                             $messagePart .= "$operation $type having " self::readableFilter($value) . " \"<strong>";
  1762.                             $messagePart .= SearchController::getInstituteTypeById($string$doctrine) . "</strong>\"";
  1763.                             break;
  1764.                         case 'Activities contains':
  1765.                             $whereInst .= ' ' $operationInst " (
  1766.                                                     i.activities LIKE  :percStringPerc$key
  1767.                                                     ) ";
  1768.                             $messagePart .= "$operation \"<strong>" self::cleaned($string) . "</strong>\" ";
  1769.                             $messagePart .= "in $type ";
  1770.                             $messagePart .= "using filter \"<strong>" self::readableFilter($value) . "</strong>\" ";
  1771.                             //remember what value has to be bound to what placeholder
  1772.                             $bindValues[$type][$key] = array(
  1773.                                 'percStringPerc' $key => '%' $string '%'
  1774.                             );
  1775.                             break;
  1776.                         case 'Tel/Fax contains':
  1777.                             $whereInst .= ' ' $operationInst " ( 
  1778.                                                           i.inst_tel LIKE  :percStringPerc$key
  1779.                                                     OR i.inst_fax LIKE  :percStringPerc$key
  1780.                                                        ) ";
  1781.                             $messagePart .= "$operation \"<strong>" self::cleaned($string) . "</strong>\" ";
  1782.                             $messagePart .= "in $type ";
  1783.                             $messagePart .= "using filter \"<strong>" self::readableFilter($value) . "</strong>\" ";
  1784.                             //remember what value has to be bound to what placeholder
  1785.                             $bindValues[$type][$key] = array(
  1786.                                 'percStringPerc' $key => '%' $string '%'
  1787.                             );
  1788.                             break;
  1789.                         case 'Website URL contains':
  1790.                             $whereInst .= ' ' $operationInst " ( 
  1791.                                                           i.inst_url LIKE  :percStringPerc$key
  1792.                                                        ) ";
  1793.                             $messagePart .= "$operation \"<strong" self::cleaned($string) . "</strong>\" ";
  1794.                             $messagePart .= "in $type ";
  1795.                             $messagePart .= "using filter \"<strong>" self::readableFilter($value) . "</strong>\" ";
  1796.                             //remember what value has to be bound to what placeholder
  1797.                             $bindValues[$type][$key] = array(
  1798.                                 'percStringPerc' $key => '%' $string '%'
  1799.                             );
  1800.                             break;
  1801.                         case 'EDMO Code is':
  1802.                             $whereInst .= ' ' $operationInst " ( 
  1803.                                                           i.edmo_code = '$string'
  1804.                                                           AND edmo_code != 0
  1805.                                                           ) ";
  1806.                             $messagePart .= "$operation \"<strong>" self::cleaned($string) . "</strong>\" ";
  1807.                             $messagePart .= "in $type ";
  1808.                             $messagePart .= "using filter \"<strong>" self::readableFilter($value) . "</strong>\" ";
  1809.                             break;
  1810.                         case 'Updated':
  1811.                             $startDate = isset($searchParams['startDate'][$key]) ? "l_date_updated >= '" self::cleanDate($searchParams['startDate'][$key]) . "' " "1=1 ";
  1812.                             $endDate = isset($searchParams['endDate'][$key]) ? "AND l_date_updated <= '" self::cleanDate($searchParams['endDate'][$key]) . "' " " AND 1=1 ";
  1813.                             $whereInst .= ' ' $operationInst ' ' $startDate ' ' $endDate;
  1814.                             $messagePart .= "$operation $type \"<strong>" self::readableFilter($value) . "</strong>\" ";
  1815.                             $messagePart .= "between \"<strong>" self::cleaned($searchParams['startDate'][$key]) . "</strong>\" ";
  1816.                             $messagePart .= "and \"<strong>" self::cleaned($searchParams['endDate'][$key]) . "</strong>\" ";
  1817.                             break;
  1818.                         case 'Created':
  1819.                             $startDate = isset($searchParams['startDate'][$key]) ? "f_date_entered >= '" self::cleanDate($searchParams['startDate'][$key]) . "' " "1=1 ";
  1820.                             $endDate = isset($searchParams['endDate'][$key]) ? "AND f_date_entered <= '" self::cleanDate($searchParams['endDate'][$key]) . "' " " AND 1=1 ";
  1821.                             $whereInst .= ' ' $operationInst ' ' $startDate ' ' $endDate;
  1822.                             $messagePart .= "$operation $type \"<strong>" self::readableFilter($value) . "</strong>\" ";
  1823.                             $messagePart .= "between \"<strong>" self::cleaned($searchParams['startDate'][$key]) . "</strong>\" ";
  1824.                             $messagePart .= "and \"<strong>" self::cleaned($searchParams['endDate'][$key]) . "</strong>\" ";
  1825.                             break;
  1826.                     }
  1827.                     $instCondCnt++;
  1828.                     $operationInst $searchParams['searchToggle'][$key] ?? " OR ";
  1829.                     $operation $searchParams['searchToggle'][$key] ?? " OR ";
  1830.                 } elseif ($type == 'all') {
  1831.                     $newString self::fulltext($string);
  1832.                     $stmtInd $connection->prepare("
  1833.                             SELECT 
  1834.                                 'experts' AS type, 
  1835.                                 i.status, 
  1836.                                 i.id_ind, 
  1837.                                 concat(i.fname,' ',i.sname) AS name, 
  1838.                                 i.fname,
  1839.                                 i.mname,
  1840.                                 i.sname, 
  1841.                                 inst_name,
  1842.                                 jobtitle,
  1843.                                 i.addr_1,
  1844.                                 i.addr_2,
  1845.                                 i.city,
  1846.                                 i.state,
  1847.                                 i.postcode,
  1848.                                 inst.inst_address,
  1849.                                 inst.addr_2 AS instAddr2,
  1850.                                 inst.city AS instCity,
  1851.                                 inst.state AS instState,
  1852.                                 inst.postcode AS instPostCode, 
  1853.                                 use_inst_addr, 
  1854.                                 deceased, 
  1855.                                 retired, 
  1856.                                 quality_checked, 
  1857.                                 (IF(use_inst_addr = 1, ic.country, c.country)) AS country, 
  1858.                                 MATCH (fname, sname) AGAINST (:newString IN BOOLEAN MODE) AS relevance, 
  1859.                                 'zz' AS instType, 
  1860.                                 'zz' AS eventtype, 
  1861.                                 '' AS inst_logo
  1862.                             FROM 
  1863.                                 indiv i
  1864.                                 LEFT JOIN countries c ON c.id_country =  i.country_code
  1865.                                 LEFT JOIN indiv_institution ii ON ii.id_ind = i.id_ind
  1866.                                 LEFT JOIN institutions inst ON inst.id_inst = ii.id_inst
  1867.                                 LEFT JOIN countries ic ON ic.id_country =  inst.country_code
  1868.                             WHERE 
  1869.                                 ( 
  1870.                                     MATCH (fname, mname, sname) AGAINST (:newString IN BOOLEAN MODE)
  1871.                                     OR fname LIKE :string
  1872.                                     OR fname LIKE :stringPerc
  1873.                                     OR fname LIKE :percString
  1874.                                     OR mname LIKE :stringPerc
  1875.                                     OR mname LIKE :percString
  1876.                                     OR sname LIKE :stringPerc
  1877.                                     OR sname LIKE :percString
  1878.                                 )
  1879.                                 " $searchParams['indivStatus'] . '
  1880.                             ORDER BY relevance DESC, i.sname ASC;
  1881.                         ');
  1882.                     $stmtInd->bindValue('newString'$newString);
  1883.                     $stmtInd->bindValue('string'$string);
  1884.                     $stmtInd->bindValue('percString''%' $string);
  1885.                     $stmtInd->bindValue('stringPerc'$string '%');
  1886.                     $stmtInd->bindValue('percStringPerc''%' $string '%');
  1887.                     $stmtInd->execute();
  1888.                     $dataResultInd $stmtInd->fetchAll();
  1889.                     $sortFields['jobtitle'] = 'Position';
  1890.                     $stmtInst $connection->prepare("
  1891.                             SELECT 
  1892.                                 'institutions' AS type, 
  1893.                                 i.activated, 
  1894.                                 i.id_inst, 
  1895.                                 insttype_name AS instType, 
  1896.                                 inst_name,
  1897.                                 inst_name_eng,
  1898.                                 inst_address,
  1899.                                 addr_2,
  1900.                                 city AS instCity,
  1901.                                 state AS instState,
  1902.                                 postcode AS instPostCode,
  1903.                                 country,
  1904.                                 activities,
  1905.                                 inst_logo,
  1906.                                 'zz' AS jobtitle, 
  1907.                                 MATCH (inst_name,inst_name_eng,acronym) AGAINST (:newString) AS relevance, 
  1908.                                 'zz' AS eventtype, 
  1909.                                 'zz' AS fname, 
  1910.                                 'zz' AS sname
  1911.                             FROM 
  1912.                                 institutions i
  1913.                                 LEFT JOIN countries c ON c.id_country =  i.country_code
  1914.                                 LEFT JOIN insttypes it ON it.id_insttype =  i.inst_type_id
  1915.                             WHERE 
  1916.                                 (
  1917.                                     MATCH (inst_name, inst_name_eng, acronym) AGAINST (:newString IN BOOLEAN MODE)
  1918.                                     OR inst_name LIKE :percStringPerc
  1919.                                     OR inst_name_eng LIKE :percStringPerc
  1920.                                     OR acronym LIKE :percStringPerc
  1921.                                     OR activities LIKE :percStringPerc
  1922.                                 )
  1923.                                  " $searchParams['instStatus'] . '
  1924.                             ORDER BY relevance DESC, i.inst_name ASC;
  1925.                         ');
  1926.                     $stmtInst->bindValue('newString'$newString);
  1927.                     $stmtInst->bindValue('percStringPerc''%' $string '%');
  1928.                     $stmtInst->execute();
  1929.                     $dataResultInst $stmtInst->fetchAll();
  1930.                     $sortFields['instType'] = 'Institution Type';
  1931.                     /**
  1932.                      *    Will be taken care when PPC
  1933.                      */
  1934.                     $stmtEvnt $connection->prepare("
  1935.                             SELECT 
  1936.                                 'events' AS type, 
  1937.                                 et.eventtype_name AS eventtype, 
  1938.                                 e.status AS activated, 
  1939.                                 e.id_event, 
  1940.                                 e.title AS name, 
  1941.                                 e.start_on, 
  1942.                                 e.end_on, 
  1943.                                 e.address AS addr_1, 
  1944.                                 e.city, 
  1945.                                 e.state, 
  1946.                                 c.country AS country, 
  1947.                                 'zz' AS jobtitle, 
  1948.                                 'zz' AS instType, 
  1949.                                 'zz' AS fname, 
  1950.                                 'zz' AS sname, 
  1951.                                 'zz' AS inst_name
  1952.                             FROM 
  1953.                                 events e
  1954.                                 LEFT JOIN countries c on c.id_country =  e.id_country
  1955.                                 LEFT JOIN eventtypes et on et.id_eventtype =  e.id_eventtype
  1956.                             WHERE 
  1957.                                 (
  1958.                                     title LIKE  :percStringPerc
  1959.                                     OR shorttitle LIKE  :percStringPerc
  1960.                                     OR summary LIKE  :percStringPerc
  1961.                                     OR keywords LIKE  :percStringPerc
  1962.                                 )
  1963.                                 " $searchParams['eventStatus'] . '
  1964.                             ORDER BY start_on desc
  1965.                         ');
  1966.                     $stmtEvnt->bindValue('percStringPerc''%' $string '%');
  1967.                     $stmtEvnt->execute();
  1968.                     $dataResultEvnt $stmtEvnt->fetchAll();
  1969.                     $messagePart .= ' "<strong>' self::cleaned($string) . '</strong>" ';
  1970.                 } elseif ($type == 'events') {
  1971.                     $sortFields['name'] = 'Event Title';
  1972.                     if ($sortBy == '') {
  1973.                         $sortBy 'name';
  1974.                     }
  1975.                     $bindValues[$type][$key] = array();
  1976.                     $selectQryEvnt "
  1977.                             SELECT 
  1978.                                 'events' AS type, 
  1979.                                 et.eventtype_name AS eventtype, 
  1980.                                 e.status AS activated, 
  1981.                                 e.id_event, 
  1982.                                 e.title AS name, 
  1983.                                 e.start_on, 
  1984.                                 e.end_on, 
  1985.                                 e.address AS addr_1, 
  1986.                                 e.city, 
  1987.                                 e.state, 
  1988.                                 c.country AS country, 
  1989.                                 'zz' AS jobtitle, 
  1990.                                 'zz' AS instType,
  1991.                                 'zz' AS fname, 
  1992.                                 'zz' AS sname
  1993.                             FROM 
  1994.                                  events e ";
  1995.                     $joinsEvnt ' LEFT JOIN countries c ON c.id_country = e.id_country
  1996.                                        LEFT JOIN eventtypes et ON et.id_eventtype = e.id_eventtype ';
  1997.                     switch ($value) {
  1998.                         case 'Title contains':
  1999.                             $whereEvent .= ' ' $operationEvnt " (
  2000.                                                   e.title LIKE  :percStringPerc$key
  2001.                                                        ) ";
  2002.                             $messagePart .= "$operation \"<strong>" self::cleaned($string) . "</strong>\" ";
  2003.                             $messagePart .= "in $type ";
  2004.                             $messagePart .= "using filter \"<strong>" self::readableFilter($value) . "</strong>\" ";
  2005.                             //remember what value has to be bound to what placeholder
  2006.                             $bindValues[$type][$key] = array(
  2007.                                 'percStringPerc' $key => '%' $string '%'
  2008.                             );
  2009.                             break;
  2010.                         case 'eType is':
  2011.                             $whereEvent .= ' ' $operationEvnt " (
  2012.                                                   e.id_eventtype = $string
  2013.                                                        ) ";
  2014.                             $messagePart .= "$operation $type ";
  2015.                             $messagePart .= "having Event Type \"<strong>" SearchController::getInstituteTypeById($string$doctrine) . "</strong>\" ";
  2016.                             break;
  2017.                         case 'Summary contains':
  2018.                             $whereEvent .= ' ' $operationEvnt " (
  2019.                                                   e.summary LIKE  :percStringPerc$key
  2020.                                                        ) ";
  2021.                             $messagePart .= "$operation \"<strong>" self::cleaned($string) . "</strong>\" ";
  2022.                             $messagePart .= "in $type using filter \"<strong>" self::readableFilter($value) . "</strong>\" ";
  2023.                             //remember what value has to be bound to what placeholder
  2024.                             $bindValues[$type][$key] = array(
  2025.                                 'percStringPerc' $key => '%' $string '%'
  2026.                             );
  2027.                             break;
  2028.                         case 'Keywords contain':
  2029.                             $whereEvent .= ' ' $operationEvnt " (
  2030.                                                e.keywords LIKE  :percStringPerc$key
  2031.                                               ) ";
  2032.                             $messagePart .= "$operation \"<strong>" self::cleaned($string) . "</strong>\" ";
  2033.                             $messagePart .= "in $type using filter \"<strong>" self::readableFilter($value) . "</strong>\" ";
  2034.                             //remember what value has to be bound to what placeholder
  2035.                             $bindValues[$type][$key] = array(
  2036.                                 'percStringPerc' $key => '%' $string '%'
  2037.                             );
  2038.                             break;
  2039.                         case 'Address contains':
  2040.                             $whereEvent .= ' ' $operationEvnt " (
  2041.                                                e.address LIKE  :percStringPerc$key
  2042.                                                OR e.city LIKE  :percStringPerc$key
  2043.                                                OR e.state LIKE  :percStringPerc$key
  2044.                                               ) ";
  2045.                             $messagePart .= "$operation \"<strong>" self::cleaned($string) . "</strong>\" ";
  2046.                             $messagePart .= "in $type using filter \"<strong>" self::readableFilter($value) . "</strong>\" ";
  2047.                             //remember what value has to be bound to what placeholder
  2048.                             $bindValues[$type][$key] = array(
  2049.                                 'percStringPerc' $key => '%' $string '%'
  2050.                             );
  2051.                             break;
  2052.                         case 'Country is':
  2053.                             $whereEvent .= ' ' $operationEvnt " (
  2054.                                                e.id_country = $string
  2055.                                               ) ";
  2056.                             $messagePart .= "$operation $type ";
  2057.                             $messagePart .= "from  \"<strong>" self::getCountryById($string$doctrine) . "</strong>\" ";
  2058.                             break;
  2059.                         case 'Date between':
  2060.                             $date '';
  2061.                             $messagePart .= "$operation $type with parameters '<strong>" self::readableFilter($value) . "</strong>': ";
  2062.                             if (isset($searchParams['startDate'][$key])
  2063.                                 && $searchParams['startDate'][$key] != ''
  2064.                                 && isset($searchParams['endDate'][$key])
  2065.                                 && $searchParams['endDate'][$key] != ''
  2066.                             ) {
  2067.                                 $startDate self::cleanDate($searchParams['startDate'][$key]);
  2068.                                 $endDate self::cleanDate($searchParams['endDate'][$key]);
  2069.                                 $date " start_on >= '$startDate
  2070.                                         AND end_on <= '$endDate' ";
  2071.                                 $messagePart .= "between '<strong>$startDate</strong>' ";
  2072.                                 $messagePart .= "and '<strong>$endDate</strong>' ";
  2073.                             } elseif (isset($searchParams['startDate'][$key])
  2074.                                 && (!$searchParams['endDate'][$key]
  2075.                                     || $searchParams['endDate'][$key] == '')
  2076.                             ) {
  2077.                                 $startDate self::cleanDate($searchParams['startDate'][$key]);
  2078.                                 $date " start_on >= '$startDate' ";
  2079.                                 $messagePart .= " -> after '<strong>$startDate</strong>' ";
  2080.                             } elseif (isset($searchParams['endDate'][$key])
  2081.                                 && (!$searchParams['startDate'][$key]
  2082.                                     || $searchParams['startDate'][$key] == '')
  2083.                             ) {
  2084.                                 $endDate self::cleanDate($searchParams['endDate'][$key]);
  2085.                                 $date " end_on <= '$endDate' ";
  2086.                                 $messagePart .= " -> before '<strong>$endDate</strong>' ";
  2087.                             } else {
  2088.                                 $whereEvent .= '';
  2089.                             }
  2090.                             $whereEvent .= ' ' $operationInst ' ' $date;
  2091.                             break;
  2092.                         case 'Updated':
  2093.                             $endDate = isset($searchParams['endDate'][$key]) ? "AND updated_at <= '" self::cleanDate($searchParams['endDate'][$key]) . "' " " AND 1=1 ";
  2094.                             $whereEvent .= ' ' $operationInst ' ' self::cleaned($searchParams['startDate'][$key]) . ' ' $endDate;
  2095.                             $messagePart .= "$operation $type \"<strong>" self::readableFilter($value) . "</strong>\" ";
  2096.                             $messagePart .= "between \"<strong>" self::cleaned($searchParams['startDate'][$key]) . "</strong>\" ";
  2097.                             $messagePart .= "and \"<strong>" self::cleaned($searchParams['endDate'][$key]) . "</strong>\" ";
  2098.                             break;
  2099.                         case 'Created':
  2100.                             $startDate = isset($searchParams['startDate'][$key]) ? "created_at >= '" self::cleanDate($searchParams['startDate'][$key]) . "' " "1=1 ";
  2101.                             $endDate = isset($searchParams['endDate'][$key]) ? "AND created_at <= '" self::cleanDate($searchParams['endDate'][$key]) . "' " " AND 1=1 ";
  2102.                             $whereEvent .= ' ' $operationInst ' ' $startDate ' ' $endDate;
  2103.                             $messagePart .= "$operation $type \"<strong>" self::readableFilter($value) . "</strong>\" ";
  2104.                             $messagePart .= "between \"<strong>" self::cleaned($searchParams['startDate'][$key]) . "</strong>\" ";
  2105.                             $messagePart .= "and \"<strong>" self::cleaned($searchParams['endDate'][$key]) . "</strong>\" ";
  2106.                             break;
  2107.                     }
  2108.                     $operationEvnt $searchParams['searchToggle'][$key] ?? " OR ";
  2109.                 } elseif ($type == 'documents') {
  2110.                     $sortFields['name'] = 'Document Title';
  2111.                     if ($sortBy == '') {
  2112.                         $sortBy 'title';
  2113.                     }
  2114.                     $bindValues[$type][$key] = array();
  2115.                     $selectQryDocument "
  2116.                             SELECT 
  2117.                                 'documents' AS type, 
  2118.                                 d.id_doc,
  2119.                                 dt.id_doctype AS id_doctype, 
  2120.                                 dt.doctypename AS documenttype, 
  2121.                                 d.title, 
  2122.                                 d.created_at,
  2123.                                 d.doc_code,
  2124.                                 'undefined' AS country
  2125.                             FROM 
  2126.                                  documents d, 
  2127.                                  doctypes dt 
  2128.                             WHERE 
  2129.                                   dt.id_doctype = d.id_doctype ";
  2130.                     switch ($value) {
  2131.                         case 'Title contains':
  2132.                             $whereDocument .= ' ' $operationEvnt " (
  2133.                                                   d.title LIKE  :percStringPerc$key
  2134.                                                        ) ";
  2135.                             $messagePart .= "$operation \"<strong>" self::cleaned($string) . "</strong>\" ";
  2136.                             $messagePart .= "in $type using filter \"<strong>" self::readableFilter($value) . "</strong>\" ";
  2137.                             //remember what value has to be bound to what placeholder
  2138.                             $bindValues[$type][$key] = array(
  2139.                                 'percStringPerc' $key => '%' $string '%'
  2140.                             );
  2141.                             break;
  2142.                         case 'Document type is':
  2143.                             $whereDocument .= ' ' $operationEvnt " (
  2144.                                                   dt.id_doctype = :idDoctype
  2145.                                                        ) ";
  2146.                             $messagePart .= "$operation $type having Document Type ";
  2147.                             $messagePart .= "\"<strong> " self::readableFilter($value) . "</strong>\" ";
  2148.                             //remember what value has to be bound to what placeholder
  2149.                             $bindValues[$type][$key] = array(
  2150.                                 'idDoctype' => $string
  2151.                             );
  2152.                             break;
  2153.                         case 'Document code is':
  2154.                             $whereDocument .= ' ' $operationEvnt " (
  2155.                                                   d.doc_code LIKE  :percStringPerc$key
  2156.                                                        ) ";
  2157.                             $messagePart .= "$operation $type having Document Code ";
  2158.                             $messagePart .= "\"<strong> " self::readableFilter($value) . "</strong>\" ";
  2159.                             //remember what value has to be bound to what placeholder
  2160.                             $bindValues[$type][$key] = array(
  2161.                                 'percStringPerc' $key => '%' $string '%'
  2162.                             );
  2163.                             break;
  2164.                         case 'Summary contains':
  2165.                             $whereDocument .= ' ' $operationEvnt " (
  2166.                                                   d.summary LIKE  :percStringPerc$key
  2167.                                                        ) ";
  2168.                             $messagePart .= "$operation \"<strong>" self::cleaned($string) . "</strong>\" ";
  2169.                             $messagePart .= "in $type using filter \"<strong>" self::readableFilter($value) . "</strong>\" ";
  2170.                             //remember what value has to be bound to what placeholder
  2171.                             $bindValues[$type][$key] = array(
  2172.                                 'percStringPerc' $key => '%' $string '%'
  2173.                             );
  2174.                             break;
  2175.                         case 'Keywords contain':
  2176.                             $whereDocument .= ' ' $operationEvnt " (
  2177.                                                d.keywords LIKE  :percStringPerc$key
  2178.                                               ) ";
  2179.                             $messagePart .= "$operation \"<strong>" self::cleaned($string) . "</strong>\" ";
  2180.                             $messagePart .= "in $type using filter \"<strong>" self::readableFilter($value) . "</strong>\" ";
  2181.                             //remember what value has to be bound to what placeholder
  2182.                             $bindValues[$type][$key] = array(
  2183.                                 'percStringPerc' $key => '%' $string '%'
  2184.                             );
  2185.                             break;
  2186.                         case 'Updated':
  2187.                             if (isset($searchParams['endDate'][$key])) {
  2188.                                 $endDate self::cleanDate($searchParams['endDate'][$key]);
  2189.                                 $endDate "AND updated_at <= '$endDate' ";
  2190.                             } else {
  2191.                                 $endDate ' AND 1=1 ';
  2192.                             }
  2193.                             $whereDocument .= ' ' $operationInst ' ' self::cleaned($searchParams['startDate'][$key]) . ' ' $endDate;
  2194.                             $messagePart .= "$operation $type \"<strong>" self::readableFilter($value) . "</strong>\" ";
  2195.                             $messagePart .= "between \"<strong>" self::cleaned($searchParams['startDate'][$key]) . "</strong>\" ";
  2196.                             $messagePart .= "and \"<strong>" self::cleaned($searchParams['endDate'][$key]) . "</strong>\" ";
  2197.                             break;
  2198.                         case 'Created':
  2199.                             if (isset($searchParams['startDate'][$key])) {
  2200.                                 $startDate self::cleanDate($searchParams['startDate'][$key]);
  2201.                                 $startDate "created_at >= '$startDate' ";
  2202.                             } else {
  2203.                                 $startDate '1=1 ';
  2204.                             }
  2205.                             if (isset($searchParams['endDate'][$key])) {
  2206.                                 $endDate self::cleanDate($searchParams['endDate'][$key]);
  2207.                                 $endDate "AND created_at <= '$endDate' ";
  2208.                             } else {
  2209.                                 $endDate ' AND 1=1 ';
  2210.                             }
  2211.                             $whereDocument .= ' ' $operationInst ' ' $startDate ' ' $endDate;
  2212.                             $messagePart .= "$operation $type \"<strong>" self::readableFilter($value) . "</strong>\" ";
  2213.                             $messagePart .= "between \"<strong>" self::cleaned($searchParams['startDate'][$key]) . "</strong>\" ";
  2214.                             $messagePart .= "and \"<strong>" self::cleaned($searchParams['endDate'][$key]) . "</strong>\" ";
  2215.                             break;
  2216.                     }
  2217.                     $operationEvnt $searchParams['searchToggle'][$key] ?? " OR ";
  2218.                 }
  2219.             }
  2220.             if (isset($selectQryInd)) {
  2221.                 $selectQryInd .= " FROM indiv i ";
  2222.                 if ($endQueryInd == false) {
  2223.                     $whereInd .= ' ) ' $searchParams['indivStatus'] . 'GROUP BY i.id_ind';
  2224.                 }
  2225.                 $queryInd $selectQryInd ' ' $commonJoins ' ' $joinsInd ' ' $whereInd ' ' $matchOrder;
  2226.                 $stmtInd $connection->prepare($queryInd);
  2227.                 foreach($bindValues['experts'] as $bindValue) {
  2228.                     foreach($bindValue as $placeholder => $value) {
  2229.                         $stmtInd->bindValue($placeholder$value);
  2230.                     }
  2231.                 }
  2232.                 $stmtInd->execute();
  2233.                 $dataResultInd $stmtInd->fetchAll();
  2234.                 $sortFields['jobtitle'] = 'Position';
  2235.             }
  2236.             if (isset($selectQryInst)) {
  2237.                 if ($endQueryInst == false) {
  2238.                     $whereInst .= " ) " $searchParams['instStatus'] . " GROUP BY i.id_inst ";
  2239.                 }
  2240.                 $queryInst $selectQryInst ' ' $joinsInst ' ' $whereInst "  ORDER BY i.inst_name ASC";
  2241.                 $stmtInst $connection->prepare($queryInst);
  2242.                 foreach($bindValues['institutions'] as $bindValue) {
  2243.                     foreach($bindValue as $placeholder => $value) {
  2244.                         $stmtInst->bindValue($placeholder$value);
  2245.                     }
  2246.                 }
  2247.                 $stmtInst->execute();
  2248.                 $dataResultInst $stmtInst->fetchAll();
  2249.                 $sortFields["instType"] = "Institution Type";
  2250.             }
  2251.             if (isset($selectQryEvnt)) {
  2252.                 if ($endQueryEvent == false) {
  2253.                     $whereEvent .= ' ) ' $searchParams['eventStatus'];
  2254.                     $whereEvent.= ' GROUP BY e.id_event ORDER BY start_on DESC ';
  2255.                 }
  2256.                 $queryEvnt $selectQryEvnt ' ' $joinsEvnt ' ' $whereEvent;
  2257.                 $stmtEvnt $connection->prepare($queryEvnt);
  2258.                 foreach($bindValues['events'] as $bindValue) {
  2259.                     foreach($bindValue as $placeholder => $value) {
  2260.                         $stmtEvnt->bindValue($placeholder$value);
  2261.                     }
  2262.                 }
  2263.                 $stmtEvnt->execute();
  2264.                 $dataResultEvnt $stmtEvnt->fetchAll();
  2265.             }
  2266.             if (isset($selectQryDocument)) {
  2267.                 if ($endQueryDocument == false) {
  2268.                     $whereDocument .= ' ORDER BY created_at DESC';
  2269.                 }
  2270.                 $queryDocument $selectQryDocument ' ' $whereDocument;
  2271.                 $stmtDocument $connection->prepare($queryDocument);
  2272.                 foreach($bindValues['documents'] as $bindValue) {
  2273.                     foreach($bindValue as $placeholder => $value) {
  2274.                         $stmtDocument->bindValue($placeholder$value);
  2275.                     }
  2276.                 }
  2277.                 $stmtDocument->execute();
  2278.                 $dataResultDocument $stmtDocument->fetchAll();
  2279.             }
  2280.             $dataResult array_merge(
  2281.                 $dataResultInd,
  2282.                 $dataResultInst,
  2283.                 $dataResultEvnt,
  2284.                 $dataResultDocument
  2285.             );
  2286.         }
  2287.         if ($messagePart == '') {
  2288.             $message 'no data found at all';
  2289.         } else {
  2290.             $messageEnd 'returned <strong>' count($dataResult) . '</strong> result(s).';
  2291.             $message $messageStart $messagePart $messageEnd;
  2292.         }
  2293.         //status 0 means there are no errors to be mentioned
  2294.         $return = array(
  2295.             'status' => 0,
  2296.             'dataResult' => $dataResult,
  2297.             'sortFields' => $sortFields,
  2298.             'sortby' => $sortBy,
  2299.             'message' => $message,
  2300.             'searchResultType' => $searchResultType
  2301.         );
  2302.         return $return;
  2303.     }
  2304.     /**
  2305.      * show the results of the advanced search
  2306.      *
  2307.      * @param Request $request
  2308.      *
  2309.      * @return Response
  2310.      */
  2311.     public function advancedSearchAction(Request $request): Response
  2312.     {
  2313.         //if we do a 'browse' search (from the homepage)
  2314.         //AND we try to filter the results (this can be the case after searching for a word in 'all')
  2315.         //we should redirect this to a more complex = advanced search type
  2316.         $searchParams $this::extractParams($request);
  2317.         //we want to see inactive/unapproved/... results as a global editor
  2318.         //normal users should not see that
  2319.         if ($this->get('security.authorization_checker')->isGranted('ROLE_GLOBAL_EDITOR')) {
  2320.             $indivStatus ' ';
  2321.             $instStatus ' ';
  2322.             $eventStatus ' ';
  2323.         } else {
  2324.             $indivStatus ' AND status = 1 ';
  2325.             $instStatus ' AND activated = 1 ';
  2326.             $eventStatus ' AND status = 1 ';
  2327.         }
  2328.         $searchParams['indivStatus'] = $indivStatus;
  2329.         $searchParams['instStatus'] = $instStatus;
  2330.         $searchParams['eventStatus'] = $eventStatus;
  2331.         //remember the filters that we have applied
  2332.         //used to add action to the filter labels shown above the results
  2333.         $appliedFilters = array();
  2334.         $appliedCountryFilters = array();
  2335.         $doctrine $this->get('doctrine');
  2336.         //get the results depending on the defined action
  2337.         if ($searchParams['action'] == 'browse') {
  2338.             $results self::getBrowseSearchResults($searchParams$doctrine);
  2339.         } elseif ($searchParams['action'] == 'advSearch') {
  2340.             $results self::getAdvSearchResults($searchParams$doctrine);
  2341.         } else {
  2342.             $results = array(
  2343.                 'status' => 2,
  2344.                 'message' => 'we need at least the parameter "action" (browse or advSearch)'
  2345.             );
  2346.         }
  2347.         //this means we have an error somewhere, or we have no results (status == 4)
  2348.         if ($results['status']
  2349.             && $results['status'] != 3
  2350.         ) {
  2351.             return $this->render(
  2352.                 'Exception/error.html.twig',
  2353.                 array(
  2354.                     'message' => $results['message']
  2355.                 )
  2356.             );
  2357.         }
  2358.         $dataResult $results['dataResult'];
  2359.         $searchResultType $results['searchResultType'];
  2360.         //only keep the results that match one of the filtertypes (experts, institutions,....) we are asking
  2361.         if (isset($searchParams['searchFilterType'])
  2362.             && is_array($searchParams['searchFilterType'])
  2363.         ) {
  2364.             $filterData = array();
  2365.             foreach (array_unique($searchParams['searchFilterType']) as $type) {
  2366.                 $filterData array_merge(
  2367.                     $filterData,
  2368.                     $this->addFilter(
  2369.                       $type,
  2370.                         'type',
  2371.                         $dataResult
  2372.                     )
  2373.                 );
  2374.                 $appliedFilters[] = $type;
  2375.             }
  2376.             $dataResult $filterData;
  2377.         }
  2378.         //only show the results for the country we are filtering
  2379.         if (isset($searchParams['filterCountry'])
  2380.             && trim($searchParams['filterCountry']) != ''
  2381.         ) {
  2382.             $filterData = array();
  2383.             foreach (explode(','$searchParams['filterCountry']) as $countryValue) {
  2384.                 if (trim($countryValue) != '') {
  2385.                     $filterData array_merge(
  2386.                         $filterData,
  2387.                         $this->addFilter(
  2388.                             $countryValue,
  2389.                             'country',
  2390.                             $dataResult
  2391.                         )
  2392.                     );
  2393.                     $appliedCountryFilters[] = $countryValue;
  2394.                 }
  2395.             }
  2396.             $dataResult $filterData;
  2397.         }
  2398.         //if sortby is defined other than relevance, then manually sort by name;
  2399.         if (isset($results['sortby'])
  2400.             && $results['sortby'] != 'relevance'
  2401.         ) {
  2402.             $this->sortBy(
  2403.                 $results['sortby'],
  2404.                 $dataResult,
  2405.                 $searchParams['sortOrder']
  2406.             );
  2407.         } else {
  2408.             $results['sortby'] = 'relevance';
  2409.         }
  2410.         $data $this->get('knp_paginator')
  2411.             ->paginate(
  2412.                 $dataResult,
  2413.                 $request->query->getInt('page'1),
  2414.                 $searchParams['limit'],
  2415.                 array(
  2416.                     'pageParameterName' => 'page',
  2417.                     'sortDirectionParameterName' => 'dir'
  2418.                 )
  2419.             );
  2420.         //gather all the email address already
  2421.         //could be done at the receiving end also...
  2422.         //or are we the receiving end already?
  2423.         $emailusers = array();
  2424.         foreach ($dataResult as $expert) {
  2425.             if ($expert['type'] == 'experts') {
  2426.                 $emailusers[] = $expert['id_ind'];
  2427.             }
  2428.         }
  2429.         //who is making this query
  2430.         //this is used for searches where we want to send emails to the resulting experts
  2431.         $user self::getDoctrine()
  2432.             ->getRepository('OceanExpertBundle:Indiv')
  2433.             ->findOneBy(
  2434.                 array(
  2435.                     'idInd' => $this->get('security.token_storage')->getToken()->getUser()
  2436.                 )
  2437.             );
  2438.         //show what we have
  2439.         return $this->render(
  2440.             'Search/advancedSearch.html.twig',
  2441.             array(
  2442.                 'action' => $searchParams['action'],
  2443.                 'data' => $data,
  2444.                 'filters' => self::getFilters($dataResult),
  2445.                 'searchResultText' => $results['message'],
  2446.                 'appliedFilters' => $appliedFilters,
  2447.                 'appliedCountryFilters' => $appliedCountryFilters,
  2448.                 'searchResultType' => $searchResultType,
  2449.                 'searchConditions' => $searchParams['conditions'],
  2450.                 'sortfields' => $results['sortFields'],
  2451.                 'user' => $user,
  2452.                 'emailUsers' => $emailusers,
  2453.                 'sortby' => $results['sortby']
  2454.             )
  2455.         );
  2456.     }
  2457.     public static function sortBy($field, &$array$direction 'ASC'): bool
  2458.     {
  2459.         usort($array, function($a$b) use ($field$direction) {
  2460.             $a $a[$field] ?? '';
  2461.             $b $b[$field] ?? '';
  2462.             if ($a == $b) {
  2463.                 return 0;
  2464.             }
  2465.             if (strtolower($direction) == 'desc') {
  2466.                 return $a $b ? -1;
  2467.             } else {
  2468.                 return $a $b ? -1;
  2469.             }
  2470.         });
  2471.         return true;
  2472.     }
  2473.     /**
  2474.      * @param $dataArr
  2475.      * @param $type
  2476.      * @return array
  2477.      *
  2478.      */
  2479.     public static function getUnique($dataArr$type): array
  2480.     {
  2481.         SearchController::sortBy($type$dataArr'ASC');
  2482.         $fields = array();
  2483.         foreach ($dataArr as $value) {
  2484.             if (isset($value[$type])
  2485.                 && trim($value[$type]) != ''
  2486.             ) {
  2487.                 if (array_key_exists($value[$type], $fields)) {
  2488.                     $fields[$value[$type]] += 1;
  2489.                 } else {
  2490.                     $fields[$value[$type]] = 1;
  2491.                 }
  2492.             }
  2493.         }
  2494.         ksort($fields);
  2495.         return $fields;
  2496.     }
  2497.     public static function addFilter($filter$filterKey$dataArr): array
  2498.     {
  2499.         $filterData = array();
  2500.         foreach ($dataArr as $key => $value) {
  2501.             if ($filter == 'all'
  2502.                 || (isset($value[$filterKey])
  2503.                 && $value[$filterKey] == $filter)
  2504.             ) {
  2505.                 $filterData[$key] = $value;
  2506.             }
  2507.         }
  2508.         return $filterData;
  2509.     }
  2510.     /**
  2511.      * get the subject area name by its id
  2512.      *
  2513.      * @param int    $id id of the subject area
  2514.      * @param object $doctrine
  2515.      *
  2516.      * @return string
  2517.      */
  2518.     public static function getSubjectAreaById(int $idobject $doctrine): string
  2519.     {
  2520.         $subjectArea $doctrine
  2521.             ->getRepository('OceanExpertBundle:Subjects')
  2522.             ->findOneBy(array('idSub' => $id));
  2523.         return $subjectArea->getSubname();
  2524.     }
  2525.     /**
  2526.      * get the name of the sea region by its id
  2527.      *
  2528.      * @param int    $id       id of the sea area
  2529.      * @param object $doctrine doctrine
  2530.      *
  2531.      * @return string
  2532.      */
  2533.     public static function getSeaRegionById(int $idobject $doctrine): string
  2534.     {
  2535.         $seaRegion $doctrine
  2536.             ->getRepository('OceanExpertBundle:Searegions')
  2537.             ->findOneBy(array('idSearegion' => $id));
  2538.         return $seaRegion->getName();
  2539.     }
  2540.     /**
  2541.      * get the name of the institution type
  2542.      * replaced by getInstituteTypeNameById()
  2543.      *
  2544.      * @param int    $id       the id of the institution type
  2545.      * @param object $doctrine doctrine
  2546.      *
  2547.      * @return string
  2548.      */
  2549.     public static function getInstituteTypeById(int $idobject $doctrine): string
  2550.     {
  2551.         return self::getInstituteTypeNameById($id$doctrine);
  2552.     }
  2553.     /**
  2554.      * get the name of the institution type
  2555.      * used to be getInstituteTypeById()
  2556.      *
  2557.      * @param int    $id       the id of the institution type
  2558.      * @param object $doctrine doctrine
  2559.      *
  2560.      * @return string
  2561.      */
  2562.     public function getInstituteTypeNameById(int $idobject $doctrine): string
  2563.     {
  2564.         $instType $doctrine
  2565.             ->getRepository('OceanExpertBundle:Insttypes')
  2566.             ->findOneBy(
  2567.                 array(
  2568.                     'idInsttype' => $id
  2569.                 )
  2570.             );
  2571.         if ($instType) {
  2572.             return $instType->getInsttypeName();
  2573.         } else {
  2574.             return 'no institution found';
  2575.         }
  2576.     }
  2577.     public static function getJobTypesById($id$doctrine)
  2578.     {
  2579.         $jobType $doctrine
  2580.             ->getRepository('OceanExpertBundle:Jobtypes')
  2581.             ->findOneBy(array('idJob' => $id));
  2582.         return $jobType->getJobName();
  2583.     }
  2584.     public static function getCountryById($idManagerRegistry $doctrine null): bool
  2585.     {
  2586.         if ($id != 0) {
  2587.             $country $doctrine
  2588.                 ->getRepository('OceanExpertBundle:Countries')
  2589.                 ->findOneBy(array('idCountry' => $id));
  2590.             return $country->getCountry();
  2591.         } else {
  2592.             return false;
  2593.         }
  2594.     }
  2595.     public static function getMultipleCountryById($id$doctrine): array
  2596.     {
  2597.         $id explode(','$id);
  2598.         $countries $doctrine
  2599.             ->getRepository('OceanExpertBundle:Countries')
  2600.             ->createQueryBuilder('c')
  2601.             ->select('c.country')
  2602.             ->where('c.idCountry in (:idCountries)')
  2603.             ->setParameter('idCountries'$id)
  2604.             ->orderBy('c.country')
  2605.             ->getQuery()
  2606.             ->getResult(AbstractQuery::HYDRATE_ARRAY);
  2607.         return array_column($countries'country');
  2608.     }
  2609.     public static function getRegionById($id$doctrine)
  2610.     {
  2611.         $region $doctrine
  2612.             ->getRepository('OceanExpertBundle:Searegions')
  2613.             ->findOneBy(array('idSearegion' => $id));
  2614.         return $region->getName();
  2615.     }
  2616.     public static function getGroupById($id$doctrine)
  2617.     {
  2618.         $group $doctrine
  2619.             ->getRepository('OceanExpertBundle:Groups')
  2620.             ->findOneBy(array('idGroup' => $id));
  2621.         return $group->getGroupName();
  2622.     }
  2623.     public static function readableFilter($filter): string
  2624.     {
  2625.         return ucfirst(preg_replace('/_/'' '$filter));
  2626.     }
  2627.     public function getSeaRegionsAction(): Response
  2628.     {
  2629.         $regions self::getDoctrine()
  2630.             ->getRepository('OceanExpertBundle:Searegions')
  2631.             ->seaRegions();
  2632.         return new JsonResponse($regions);
  2633.     }
  2634.     public function getMemberGroupsAction(): Response
  2635.     {
  2636.         $groups self::getDoctrine()
  2637.             ->getRepository('OceanExpertBundle:Groups')
  2638.             ->createQueryBuilder('e')
  2639.             ->select('e.idGroup AS id,e.groupname AS name')
  2640.             ->orderBy('e.groupname')
  2641.             ->getQuery()
  2642.             ->getResult(AbstractQuery::HYDRATE_ARRAY);
  2643.         return new JsonResponse($groups);
  2644.     }
  2645.     public static function getChildGroup($group$doctrine): array
  2646.     {
  2647.         $groupids = array();
  2648.         $allgroups = array($group);
  2649.         $groups $doctrine
  2650.             ->getRepository('OceanExpertBundle:Groups')
  2651.             ->createQueryBuilder('e')
  2652.             ->select('e.idGroup,e.groupname')
  2653.             ->orderBy('e.groupname')
  2654.             ->where('e.idParentgroup = :group')
  2655.             ->setParameter('group'$group)
  2656.             ->getQuery()
  2657.             ->getResult(AbstractQuery::HYDRATE_ARRAY);
  2658.         foreach ($groups as $value) {
  2659.             $groupids[] = $value['idGroup'];
  2660.             $allgroups[] = $value['idGroup'];
  2661.         }
  2662.         if (count($groupids) > 0) {
  2663.             do {
  2664.                 $group2 $doctrine
  2665.                     ->getRepository('OceanExpertBundle:Groups')
  2666.                     ->createQueryBuilder('e')
  2667.                     ->select('e.idGroup AS id,e.groupname AS name')
  2668.                     ->orderBy('e.groupname')
  2669.                     ->where('e.idParentgroup in(:groupids)')
  2670.                     ->setParameter('groupids'$groupids)
  2671.                     ->getQuery()
  2672.                     ->getResult(AbstractQuery::HYDRATE_ARRAY);
  2673.                 $groupids = array();
  2674.                 foreach ($group2 as $value) {
  2675.                     $groupids[] = $value['id'];
  2676.                     $allgroups[] = $value['id'];
  2677.                 }
  2678.             } while (count($group2) > 0);
  2679.         }
  2680.         return $allgroups;
  2681.     }
  2682.     public function getJobTypesAction(): Response
  2683.     {
  2684.         $jobTypes self::getDoctrine()
  2685.             ->getRepository('OceanExpertBundle:Jobtypes')
  2686.             ->createQueryBuilder('e')
  2687.             ->select('e.idJob AS id,e.jobname AS name')
  2688.             ->orderBy('e.jobname')
  2689.             ->getQuery()
  2690.             ->getResult(AbstractQuery::HYDRATE_ARRAY);
  2691.         return new JsonResponse($jobTypes);
  2692.     }
  2693.     public function getCountriesAction(): Response
  2694.     {
  2695.         $countries self::getDoctrine()
  2696.             ->getRepository('OceanExpertBundle:Countries')
  2697.             ->createQueryBuilder('e')
  2698.             ->select('e.idCountry AS id,e.country AS name')
  2699.             ->orderBy('e.country')
  2700.             ->getQuery()
  2701.             ->getResult(AbstractQuery::HYDRATE_ARRAY);
  2702.         return new JsonResponse($countries);
  2703.     }
  2704.     public function getSubjectAreaAction(): Response
  2705.     {
  2706.         $subjectArea self::getDoctrine()
  2707.             ->getRepository('OceanExpertBundle:Subjects')
  2708.             ->createQueryBuilder('e')
  2709.             ->select('e.subname AS id,e.subname AS name')
  2710.             ->orderBy('e.subname')
  2711.             ->getQuery()
  2712.             ->getResult(AbstractQuery::HYDRATE_ARRAY);
  2713.         return new JsonResponse($subjectArea);
  2714.     }
  2715.     public function getEventTypesAction(): Response
  2716.     {
  2717.         $eventTypes self::getDoctrine()
  2718.             ->getRepository('OceanExpertBundle:Eventtypes')
  2719.             ->createQueryBuilder('e')
  2720.             ->select('e.idEventtype AS id,e.eventtypeName AS name')
  2721.             ->orderBy('e.eventtypeName')
  2722.             ->getQuery()
  2723.             ->getResult(AbstractQuery::HYDRATE_ARRAY);
  2724.         return new JsonResponse($eventTypes);
  2725.     }
  2726.     /**
  2727.      * here for backward compatibility
  2728.      *
  2729.      * @deprecated
  2730.      *
  2731.      * @return JsonResponse
  2732.      */
  2733.     function getInstitutionTypesAction(): JsonResponse
  2734.     {
  2735.         return $this->getInstituteTypesAction();
  2736.     }
  2737.     /**
  2738.      * @return JsonResponse
  2739.      */
  2740.     public function getInstituteTypesAction(): JsonResponse
  2741.     {
  2742.         $instTypes self::getDoctrine()
  2743.             ->getRepository('OceanExpertBundle:Insttypes')
  2744.             ->createQueryBuilder('i')
  2745.             ->select('i.idInsttype AS id,i.insttypeName AS name')
  2746.             ->orderBy('i.insttypeName')
  2747.             ->getQuery()
  2748.             ->getResult(AbstractQuery::HYDRATE_ARRAY);
  2749.         return new JsonResponse($instTypes);
  2750.     }
  2751.     /**
  2752.      * get all possible document types
  2753.      * can be used by the search to provide a dropdown menu
  2754.      * @see #609
  2755.      *
  2756.      * @return Response
  2757.      */
  2758.     public function getDocumentTypesAction(): Response
  2759.     {
  2760.         $documentTypes self::getDoctrine()
  2761.             ->getRepository('OceanExpertBundle:Doctypes')
  2762.             ->createQueryBuilder('i')
  2763.             ->select('i.idDoctype AS id,i.doctypename AS name')
  2764.             ->orderBy('i.doctypename')
  2765.             ->getQuery()
  2766.             ->getResult(AbstractQuery::HYDRATE_ARRAY);
  2767.         return new JsonResponse($documentTypes);
  2768.     }
  2769.     public function getMembersAction(Request $request): Response
  2770.     {
  2771.         $doctrine self::getDoctrine();
  2772.         $members = array();
  2773.         if (null !== $request->request->get('searchterm')) {
  2774.             $searchTerm $request->request->get('searchterm');
  2775.             $type $request->request->get('searchparam');
  2776.         } elseif (null !== $request->query->get('searchterm')) {
  2777.             $searchTerm $request->query->get('searchterm');
  2778.             $type $request->query->get('searchparam');
  2779.         } else {
  2780.             return new JsonResponse(
  2781.                 array(
  2782.                     'status' => 1,
  2783.                     'error' => 'please send "searchparam" and "searchterm"'
  2784.                 )
  2785.             );
  2786.         }
  2787.         $searchParams $this::extractParams($request);
  2788.         $searchParams['searchQuery'] = $searchTerm;
  2789.         $searchParams['searchType'] = $type;
  2790.         $searchParams['searchFilterType'] = ['experts'];
  2791.         $connection $doctrine->getManager()->getConnection();
  2792.         if ($type == 'name'
  2793.             || $type == 'all'
  2794.         ) {
  2795.             /*
  2796.             $statement = $connection->prepare("
  2797.                 SELECT
  2798.                     i.id_ind AS DT_RowId,
  2799.                     i.id_ind,
  2800.                     i.title,
  2801.                     i.fname,
  2802.                     i.mname,
  2803.                     i.sname,
  2804.                     inst.inst_name,
  2805.                     c.country,
  2806.                     i.status,
  2807.                     MATCH (i.fname,i.sname) AGAINST (:searchQuery IN BOOLEAN MODE) AS relevance
  2808.                 FROM
  2809.                     indiv i
  2810.                     LEFT JOIN countries c ON c.id_country =  i.country_code
  2811.                     LEFT JOIN indiv_institution ii ON ii.id_ind = i.id_ind
  2812.                     LEFT JOIN institutions inst ON inst.id_inst = ii.id_inst
  2813.                 WHERE
  2814.                     (
  2815.                          MATCH (i.fname,i.mname,i.sname) AGAINST (:searchQuery IN BOOLEAN MODE)
  2816.                         OR i.fname LIKE :percSearchQuery
  2817.                         OR i.fname LIKE :searchQueryPerc
  2818.                         OR i.mname LIKE :percSearchQuery
  2819.                         OR i.mname LIKE :searchQueryPerc
  2820.                         OR i.sname LIKE :percSearchQuery
  2821.                         OR i.sname LIKE :searchQueryPerc
  2822.                     )
  2823.                 ORDER BY relevance DESC, sname ASC;
  2824.             ");
  2825.             $statement->bindValue('searchQuery', $searchParams['searchQuery']);
  2826.             $statement->bindValue('searchQueryPerc', $searchParams['searchQuery'] . '%');
  2827.             $statement->bindValue('percSearchQuery', '%' . $searchParams['searchQuery']);
  2828.             $statement->execute();
  2829.             $members = array_merge($members, $statement->fetchAll());
  2830.             */
  2831.             //we only need the active ones
  2832.             $indivStatus ' AND status = 1 ';
  2833.             $instStatus ' AND activated = 1 ';
  2834.             $eventStatus ' AND status = 1 ';
  2835.             $searchParams['indivStatus'] = $indivStatus;
  2836.             $searchParams['instStatus'] = $instStatus;
  2837.             $searchParams['eventStatus'] = $eventStatus;
  2838.             $membersTmp self::getBrowseSearchResults($searchParams$doctrine);
  2839.             //recreate the array we expected here (see old code)
  2840.             foreach($membersTmp['dataResult'] as $dataResult) {
  2841.                 if (!isset($dataResult['id_ind'])) {
  2842.                     //this is not an expert, skip
  2843.                     continue;
  2844.                 }
  2845.                 $members[] = array(
  2846.                     "DT_RowId" => $dataResult['id_ind'],
  2847.                     "id_ind" => $dataResult['id_ind'],
  2848.                     "title" => $dataResult['title'],
  2849.                     "fname" => $dataResult['fname'],
  2850.                     "mname" => $dataResult['mname'],
  2851.                     "sname" => $dataResult['sname'],
  2852.                     "inst_name" => $dataResult['inst_name'],
  2853.                     "country" => $dataResult['country'],
  2854.                     "status" => $dataResult['status'],
  2855.                     "relevance" => $dataResult['relevance'],
  2856.                 );
  2857.             }
  2858.         } elseif ($type == 'email') {
  2859.             $statement $connection->prepare("
  2860.                 SELECT 
  2861.                     i.id_ind AS DT_RowId,
  2862.                     i.id_ind, 
  2863.                     i.title, 
  2864.                     i.fname, 
  2865.                     i.mname, 
  2866.                     i.sname,
  2867.                     inst.inst_name,
  2868.                     c.country, 
  2869.                     i.status, 
  2870.                     MATCH (i.email1) AGAINST (:searchQuery IN BOOLEAN MODE) AS relevance 
  2871.                 FROM 
  2872.                     indiv i
  2873.                     LEFT JOIN countries c ON c.id_country =  i.country_code
  2874.                     LEFT JOIN indiv_institution ii ON ii.id_ind = i.id_ind
  2875.                     LEFT JOIN institutions inst ON inst.id_inst = ii.id_inst
  2876.                 WHERE 
  2877.                     email1 = :searchQuery
  2878.                     OR email2 = :searchQuery
  2879.                     OR email3 = :searchQuery
  2880.                 ORDER BY relevance DESC;
  2881.             ");
  2882.             $statement->bindValue(':searchQuery'$searchParams['searchQuery']);
  2883.             $statement->execute();
  2884.             $members array_merge(
  2885.                 $members,
  2886.                 $statement->fetchAll()
  2887.             );
  2888.         } elseif ($type == 'group') {
  2889.             //we only need the active ones
  2890.             $indivStatus ' AND status = 1 ';
  2891.             $instStatus ' AND activated = 1 ';
  2892.             $eventStatus ' AND status = 1 ';
  2893.             $searchParams['indivStatus'] = $indivStatus;
  2894.             $searchParams['instStatus'] = $instStatus;
  2895.             $searchParams['eventStatus'] = $eventStatus;
  2896.             $membersTmp self::getBrowseSearchResults($searchParams$doctrine);
  2897.             //recreate the array we expected here (see old code)
  2898.             foreach($membersTmp['dataResult'] as $dataResult) {
  2899.                 if (!isset($dataResult['id_ind'])) {
  2900.                     //this is not an experts, skip
  2901.                     continue;
  2902.                 }
  2903.                 $members[] = array(
  2904.                     "DT_RowId" => $dataResult['id_ind'],
  2905.                     "id_ind" => $dataResult['id_ind'],
  2906.                     "title" => $dataResult['title'],
  2907.                     "fname" => $dataResult['fname'],
  2908.                     "mname" => $dataResult['mname'],
  2909.                     "sname" => $dataResult['sname'],
  2910.                     "inst_name" => $dataResult['inst_name'],
  2911.                     "country" => $dataResult['country'],
  2912.                     "status" => $dataResult['status'],
  2913.                     "relevance" => $dataResult['relevance'],
  2914.                     "groupName" => $dataResult['groupName'],
  2915.                     "groupId" => $dataResult['groupId']
  2916.                 );
  2917.             }
  2918.             /*
  2919.             $statement = $connection->prepare('
  2920.                 SELECT
  2921.                     i.id_ind AS DT_RowId,
  2922.                     i.id_ind,
  2923.                     i.title,
  2924.                     i.fname,
  2925.                     i.mname,
  2926.                     i.sname,
  2927.                     inst.inst_name,
  2928.                     c.country,
  2929.                     i.status,
  2930.                     1 AS relevance
  2931.                 FROM
  2932.                     indiv i
  2933.                     LEFT JOIN countries c ON c.id_country =  i.country_code
  2934.                     LEFT JOIN indiv_institution ii ON ii.id_ind = i.id_ind
  2935.                     LEFT JOIN institutions inst ON inst.id_inst = ii.id_inst
  2936.                     LEFT JOIN member_groups m ON i.id_ind = m.id_ind
  2937.                 WHERE
  2938.                     m.id_group like "%t%"
  2939.                 ORDER BY relevance DESC;
  2940.             ');
  2941.             $statement->bindValue('searchQuery', $searchParams['searchQuery']);
  2942.             $statement->execute();
  2943.             $members = array_merge($members, $statement->fetchAll());
  2944.             */
  2945.         }
  2946.         return new JsonResponse(
  2947.             array(
  2948.                 'data' => $members
  2949.             )
  2950.         );
  2951.     }
  2952.     /**
  2953.      * @param int $expert expert id
  2954.      *
  2955.      * @return int
  2956.      *
  2957.      * @todo is this used somewhere??? Arno 15/04/2022
  2958.      */
  2959.     function getExpertEvents(int $expert): int
  2960.     {
  2961.         $em self::getDoctrine()->getManager();
  2962.         $qb $em->createQueryBuilder();
  2963.         $qb->select('e.idEvent');
  2964.         $qb->from('OceanExpertBundle:EventParticipants''e');
  2965.         $qb->where('e.idInd = :idInd');
  2966.         $qb->setParameter('idInd'$expert);
  2967.         $participants $qb->getQuery()->getResult();
  2968.         $participants array_map('current'$participants);
  2969.         $qb $em->createQueryBuilder();
  2970.         $qb->select('e.idEvent');
  2971.         $qb->from('OceanExpertBundle:EventStaff''e');
  2972.         $qb->where('e.idInd = :idInd');
  2973.         $qb->setParameter('idInd'$expert);
  2974.         $staff $qb->getQuery()->getResult();
  2975.         $staff array_map('current'$staff);
  2976.         $qb $em->createQueryBuilder();
  2977.         $qb->select('e.idEvent');
  2978.         $qb->from('OceanExpertBundle:EventContacts''e');
  2979.         $qb->where('e.idInd = :idInd');
  2980.         $qb->setParameter('idInd'$expert);
  2981.         $contacts $qb->getQuery()->getResult();
  2982.         $contacts array_map('current'$contacts);
  2983.         $result array_merge($participants$staff$contacts);
  2984.         $results array_unique($resultSORT_REGULAR);
  2985.         return count($results);
  2986.     }
  2987.     /**
  2988.      * clean input strings to prevent XSS and other problems
  2989.      *
  2990.      * @param string $string input string
  2991.      *
  2992.      * @return string
  2993.      */
  2994.     public static function cleaned(string $string): string
  2995.     {
  2996.         $string nl2br(stripslashes($string));
  2997.         return preg_replace(
  2998.             '/[<>]/',
  2999.             '--*--',
  3000.             $string
  3001.         );
  3002.     }
  3003.     /**
  3004.      * in case we get the wrong format for the date
  3005.      * cleanup and return the correct format
  3006.      * we want a string like 'yyyy-mm-dd'
  3007.      *
  3008.      * @param string $string the date to be checked
  3009.      *
  3010.      * @return string
  3011.      */
  3012.     public static function cleanDate(string $string): string
  3013.     {
  3014.         if (strpos($string'/')) {
  3015.             $date explode('/'$string);
  3016.             if (strlen($date[0]) == 4) {
  3017.                 // this was something like yyyy/mm/dd I guess
  3018.                 return $date[0] . '-' $date[1] . '-' $date[2];
  3019.             } else {
  3020.                 // this was something like dd/mm/yyyy I guess
  3021.                 return $date[2] . '-' $date[1] . '-' $date[0];
  3022.             }
  3023.         } elseif (strpos($string'-')) {
  3024.             $date explode('-'$string);
  3025.             if (strlen($date[0]) == 4) {
  3026.                 // this was something like yyyy-mm-dd I guess
  3027.                 //that is the correct format already
  3028.                 return $string;
  3029.             } else {
  3030.                 // this was something like dd-mm-yyyy I guess
  3031.                 return $date[2] . '-' $date[1] . '-' $date[0];
  3032.             }
  3033.         } else {
  3034.             //no idea what to clean up
  3035.             return $string;
  3036.         }
  3037.     }
  3038.     public static function fulltext($string): string
  3039.     {
  3040.         $newString '';
  3041.         if (trim($string) != '') {
  3042.             $arrStr explode(' '$string);
  3043.             foreach ($arrStr as &$value) {
  3044.                 $value '+' $value;
  3045.             }
  3046.             $newString implode(' '$arrStr);
  3047.         }
  3048.         return $newString;
  3049.     }
  3050. }