src/OceanExpertBundle/Controller/ProfileController.php line 488

Open in your IDE?
  1. <?php
  3. namespace OceanExpertBundle\Controller;
  5. use DateTime;
  6. use Doctrine\ORM\AbstractQuery;
  7. use Doctrine\ORM\Query;
  8. use FOS\UserBundle\Mailer\MailerInterface;
  9. use FOS\UserBundle\Model\UserManagerInterface;
  10. use FOS\UserBundle\Util\TokenGeneratorInterface;
  11. use OceanExpertBundle\Entity\Indiv;
  12. use OceanExpertBundle\Entity\MemberEditsCountry;
  13. use OceanExpertBundle\Entity\MemberEditsInstitution;
  14. use OceanExpertBundle\Entity\MemberGroups;
  15. use OceanExpertBundle\Entity\ProfileImages;
  16. use OceanExpertBundle\Entity\IndivMeta;
  17. use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
  18. use Symfony\Component\HttpFoundation\RedirectResponse;
  19. use Symfony\Component\HttpFoundation\Request;
  20. use Symfony\Component\HttpFoundation\Response;
  21. use CommerceGuys\Addressing\Model\Address;
  22. use CommerceGuys\Addressing\Formatter\DefaultFormatter;
  23. use CommerceGuys\Addressing\Repository\AddressFormatRepository;
  24. use CommerceGuys\Addressing\Repository\CountryRepository;
  25. use CommerceGuys\Addressing\Repository\SubdivisionRepository;
  26. use Symfony\Component\HttpFoundation\JsonResponse;
  28. class ProfileController extends AbstractController
  29. {
  30.     private UserManagerInterface $fosUserManager;
  31.     private TokenGeneratorInterface $fosTokenGenerator;
  32.     private MailerInterface $fosMailer;
  34.     public function __construct(
  35.         UserManagerInterface $fosUserManager, 
  36.         TokenGeneratorInterface $fosTokenGenerator, 
  37.         MailerInterface $fosMailer
  38.     ){
  39.         $this->fosUserManager = $fosUserManager;
  40.         $this->fosTokenGenerator = $fosTokenGenerator;
  41.         $this->fosMailer = $fosMailer;
  42.     }
  44.     /**
  45.      * @param string  $user
  46.      * @param Request $request
  47.      *
  48.      * @return Response
  49.      */
  50.     public function viewProfileAction(Request $request, string $user = ''): Response
  51.     {
  52.         $loggedUser = $this->get('security.token_storage')->getToken()->getUser();
  53.         $isLoggedIn = $this->get('security.authorization_checker')->isGranted('ROLE_USER');
  55.         if ($isLoggedIn
  56.             && $user == 'me'
  57.         ) {
  58.             //get the user id from the currently logged in user
  59.             $user = $loggedUser->getId();
  60.         } elseif (!is_numeric($user)) {
  61.             //try to find the user id
  63.             //try to find the user id by looking at the login credentials
  64.             $searchUser = $this->fosUserManager->findUserByUsername($user);
  65.             if ($searchUser) {
  66.                 $user = $searchUser->getId();
  67.             } else {
  68.                 //last try to find the user id
  69.                 $searchUser = $this->fosUserManager->findUserByUsername($user);
  70.                 if ($searchUser) {
  71.                     $user = $searchUser->getId();
  72.                 } else {
  73.                     return new Response("no idea what to do with user '$user', not found in DB");
  74.                 }
  75.             }
  76.         }
  78.         $qb = $this->getDoctrine()
  79.             ->getManager()
  80.             ->createQueryBuilder()
  81.             ->add('select', 'i,ins,p')
  82.             ->add('from', 'OceanExpertBundle:Indiv i')
  83.             ->leftJoin('OceanExpertBundle:ProfileImages', 'p', 'WITH', 'p.idInd = i.idInd')
  84.             ->leftJoin('OceanExpertBundle:IndivInstitution', 'idins', 'WITH', 'i.idInd = idins.idInd')
  85.             ->leftJoin('OceanExpertBundle:Institutions', 'ins', 'WITH', 'idins.idInst = ins.idInst')
  86.             ->where('i.idInd = :userId')
  87.             ->setParameter('userId', $user);
  89.         if ($this->get('security.authorization_checker')->isGranted('ROLE_GLOBAL_EDITOR')
  90.             || ($isLoggedIn
  91.                 && $user == $loggedUser->getId()
  92.             )
  93.         ) {
  94.             //@todo what should be here? Arno 10/06/2021
  95.         } else {
  96.             $qb->andWhere('i.status = 1');
  97.         }
  99.         $expert = $qb->getQuery()
  100.             ->getResult(AbstractQuery::HYDRATE_ARRAY);
  101.         
  102.         $metadata = array();
  103.         $usermeta = array();
  105.         /*
  106.          * collect the data we need for the OIH JSON-LD in this array
  107.          * in the end we will need something like
  108.          * {
  109.             "@context": {
  110.                 "@vocab": "https://schema.org/"
  111.             },
  112.             "@id": "https://example.org/id/x",
  113.             "@type": "Person",
  114.             "name": "Jane Doe",
  115.             "jobTitle": "Professor",
  116.             "telephone": "(425) 123-4567",
  117.             "url": "http://www.janedoe.com",
  118.             "knowsAbout": [
  119.                 {
  120.                     "@type": "Text",
  121.                     "description": "Invasive species in brackish water"
  122.                 },
  123.                 {
  124.                     "@type": "URL",
  125.                     "url": "https://www.wikidata.org/wiki/Q183368"
  126.                 },
  127.                 {
  128.                     "@id": "https://example.org/id/course/x",
  129.                     "@type": "Course",
  130.                     "description": "In this course ...",
  131.                     "url": "URL to the course"
  132.                 }
  133.             ],
  134.             "identifier": {
  135.                 "@id": "https://orcid.org/0000-0002-2257-9127",
  136.                 "@type": "PropertyValue",
  137.                 "propertyID": "https://registry.identifiers.org/registry/orcid",
  138.                 "url": "https://orcid.org/0000-0002-2257-9127",
  139.                 "description": "Optional description of this record..."
  140.             }
  141.           }
  142.          */
  143.         $OIHData = array(
  144.             '@context' => array(
  145.                 '@vocab' => 'https://schema.org/'
  146.             ),
  147.             '@id' => 'https://oceanexpert.org/expert/' . $user,
  148.             '@type' => 'Person'
  149.         );
  151.         if ($expert
  152.             && count($expert) > 0
  153.         ) {
  154.             $metadata = $this->getMetaData($user);
  155.             $jobtype = $this->getJobTypes($user);
  156.             $expert['jobType'] = $jobtype['jobtype'];
  158.             $jobTypes = explode('\r', $expert['jobType']);
  159.             foreach ($jobTypes as $jobType) {
  160.                 $OIHData['knowsAbout'][] = array(
  161.                     '@type' => 'Text',
  162.                     'description' => trim($jobType)
  163.                 );
  164.             }
  165.             $expert['groups'] = $this->getUserGroups($user);
  166.             $usermeta = $this->getMetaData($user);
  167.         } else {
  168.             $userExists = $this->fosUserManager->findUserBy(array('id' => $user));
  169.             if (!null == $userExists
  170.                 && ($this->get('security.authorization_checker')->isGranted('ROLE_GLOBAL_EDITOR'))
  171.                 || ($isLoggedIn
  172.                     && ($user == $loggedUser->getId())
  173.                 )
  174.             ) {
  175.                 $searchUser = $this->fosUserManager->findUserBy(array('id'=>$user));
  176.                 $expert[0]['idInd'] = $user;
  177.                 $expert['username'] = $searchUser->getUsername();
  178.                 $expert['error'] = "Incomplete Profile.";
  179.                 return $this->render(
  180.                     'Profile/profile.html.twig',
  181.                     array(
  182.                         'expert' => $expert
  183.                     )
  184.                 );
  185.             } else {
  186.                 $expert['error'] = 'User not found.';
  187.             }
  188.         }
  191.         if (isset($expert)
  192.             && isset($expert[0])
  193.             && is_array($expert[0])
  194.         ) {
  195.             //make the name
  196.             //for the website this is done in the profile.html.twig template
  197.             $userNameParts = array();
  198.             foreach (array('fname','mname','sname') as $namePart) {
  199.                 if (isset($expert[0][$namePart])
  200.                     && $expert[0][$namePart] != ''
  201.                 ) {
  202.                     $userNameParts[] = trim($expert[0][$namePart]);
  203.                 }
  204.             }
  205.             $OIHData['name'] = implode(' ', $userNameParts);
  207.             //make the address
  208.             $addressParts = array();
  209.             foreach (array('addr1','addr2') as $addressPart) {
  210.                 if (isset($expert[0][$addressPart])
  211.                     && $expert[0][$addressPart] != ''
  212.                 ) {
  213.                     $addressParts[] = trim($expert[0][$addressPart]);
  214.                 }
  215.             }
  216.             $address = implode(', ', $addressParts);
  217.             if (isset($expert[0]['postcode'])
  218.                 && $expert[0]['postcode'] != ''
  219.             ) {
  220.                 $address .= ', ' . $expert[0]['postcode'];
  221.             }
  223.             if (isset($expert[0]['city'])
  224.                 && $expert[0]['city'] != ''
  225.             ) {
  226.                 $address .= ' ' . $expert[0]['city'];
  227.             }
  229.             if (isset($expert[0]['countryCode'])
  230.                 && $expert[0]['countryCode'] != ''
  231.             ) {
  232.                 $address .= ', ' . $this->getCountryFromIds(
  233.                     explode(
  234.                         ',',
  235.                         $expert[0]['countryCode']
  236.                     )
  237.                 );
  238.             }
  239.             $OIHData['workLocation'] = array(
  240.                 '@type' => 'Place',
  241.                 'address' => trim($address)
  242.             );
  244.             if (isset($expert[0]['languages'])) {
  246.                 /*
  247.                  * never used, remove
  248.                  * Arno 30/07/2021
  249.                  *
  250.                 $qb1 = $this->getDoctrine()->getManager()->createQueryBuilder();
  251.                 $qb1->add('select', 'l')
  252.                     ->add('from', 'OceanExpertBundle:Locale l')
  253.                     ->where('l.locCode in (:locCode)')
  254.                     ->setParameter('locCode', explode(',', $expert[0]['languages']));
  256.                 $userLangs = $qb1->getQuery()->getResult(Query::HYDRATE_ARRAY);
  257.                 */
  259.                 $knowsLanguages = explode(',', $expert[0]['languages']);
  260.                 foreach ($knowsLanguages as $knowsLanguage) {
  261.                     $OIHData['knowsLanguage'][] = array(
  262.                         '@type' => 'Text',
  263.                         'name' => trim($knowsLanguage)
  264.                     );
  265.                 }
  266.             }
  268.             if (isset($expert[0]['jobtitle'])) {
  269.                 $expert['jobtitle'] = $this->getJobTypeByCodes(explode(',', $expert[0]['jobtitle']));
  271.                 $OIHData['jobTitle'] = trim($expert[0]['jobtitle']);
  272.             }
  274.             if (isset($expert['0']['lastEditBy'])) {
  275.                 $expert['lastEditBy'] = $this->getUpdatedBy($expert['0']['lastEditBy']);
  276.             }
  278.             if (isset($expert['0']['createdBy'])) {
  279.                 $expert['createdBy'] = $this->getUpdatedBy($expert['0']['createdBy']);
  280.             }
  282.             $expert['subjectArea'] = $this->getSubjectAreas($user);
  283.             if ($expert['subjectArea']
  284.                 && $expert['subjectArea'] != ''
  285.             ) {
  286.                 $subjectAreas = explode(',', $expert['subjectArea']);
  287.                 foreach ($subjectAreas as $subjectArea) {
  288.                     $OIHData['knowsAbout'][] = array(
  289.                         '@type' => 'Text',
  290.                         'description' => trim($subjectArea)
  291.                     );
  292.                 }
  293.             }
  295.             if (isset($expert[0]['idNationality'])) {
  296.                 $expert['nationality'] = $this->getCountryFromIds(
  297.                     explode(
  298.                         ',',
  299.                         $expert[0]['idNationality']
  300.                     )
  301.                 );
  302.                 $expertCountryCode = $this->getCountryCodeFromId($expert[0]['idNationality']);
  304.                 if (trim($expert['nationality'] != '')) {
  305.                     $OIHData['nationality'][] = array(
  306.                         '@type' => 'Country',
  307.                         'name' => trim($expert['nationality'])
  308.                     );
  309.                     $OIHData['nationality'][] = array(
  310.                         '@type' => 'DefinedTerm',
  311.                         'url' => 'https://unece.org/trade/cefact/unlocode-code-list-country-and-territory',
  312.                         'inDefinedTermSet' => 'UN/LOCODE Code List by Country and Territory',
  313.                         'name' => trim($expert['nationality']),
  314.                         'termCode' => trim($expertCountryCode)
  315.                     );
  316.                 }
  317.             }
  319.             foreach (array('url1', 'url2', 'url3') as $urlId) {
  320.                 if (isset($expert[0][$urlId])
  321.                     && trim($expert[0][$urlId]) != ''
  322.                 ) {
  323.                     $OIHData['url'][] = trim($expert[0][$urlId]);
  324.                 }
  325.             }
  327.             if (isset($expert[0]['studyregion'])) {
  328.                 $expert['studyregion'] = $this->getResearchAreaByCodes(
  329.                     explode(
  330.                         ',',
  331.                         $expert[0]['studyregion']
  332.                     )
  333.                 );
  334.             }
  336.             if (isset($expert[0]['countryCode'])) {
  337.                 $expert['expertCountry'] = $this->getCountryFromId($expert[0]['countryCode']);
  338.                 $expert['exrtCtryCode'] = $this->getCountryCodeFromId($expert[0]['countryCode']);
  339.             }
  341.             if (isset($expert[2]['countryCode'])) {
  342.                 $expert['instituteCountry'] = $this->getCountryFromId($expert[2]['countryCode']);
  343.                 $expert['instCtryCode'] = $this->getCountryCodeFromId($expert[2]['countryCode']);
  344.             }
  346.             if (isset($expert[0]['qualityCheckedBy'])) {
  347.                 $expert['qcby'] = $this->getUpdatedBy($expert[0]['qualityCheckedBy']);
  348.             }
  350.             if (isset($expert[0]['doNoInviteBy'])
  351.                 && isset($expert[0]['doNoInviteBy'])!=0
  352.             ) {
  353.                 $expert['doNoInviteBy'] = $this->getUpdatedBy($expert[0]['doNoInviteBy']);
  354.             }
  356.             if (isset($expert[0]['twitter'])) {
  357.                 $twitterUser = $expert[0]['twitter'];
  358.                 /*
  359.                 if (strpos($twitterUser, '@') !== false) {
  360.                     $arrVal = preg_split('@', $twitterUser);
  361.                     $twitterUser = "https://twitter.com/@" . $arrVal[1];
  362.                 }
  363.                 */
  364.                 if (preg_match('/(@.+)/', $twitterUser, $matches)) {
  365.                     $twitterUser = 'https://twitter.com/' . $matches[1];
  366.                 }
  367.                 $expert['twitter'] = $twitterUser;
  368.             }
  370.             if (isset($expert[0]['idInd'])) {
  371.                 $addressFormatRepository = new AddressFormatRepository();
  372.                 $countryRepository = new CountryRepository();
  373.                 $subdivisionRepository = new SubdivisionRepository();
  374.                 $formatter = new DefaultFormatter(
  375.                     $addressFormatRepository,
  376.                     $countryRepository,
  377.                     $subdivisionRepository
  378.                 );
  380.                 $itemId = $expert[0]['idInd'];
  382.                 $em = $this->getDoctrine()
  383.                     ->getRepository('OceanExpertBundle:MemberGroups');
  384.                 $query = $em->createQueryBuilder('m')
  385.                     ->select('m.idGroup')
  386.                     ->where('m.idInd =:idInd')
  387.                     ->setParameter('idInd', $itemId)
  388.                     ->getQuery();
  389.                 $groups = $query->getResult();
  390.                 $expert['groupids'] = array_column($groups, 'idGroup');
  391.                 $em = $this->getDoctrine()
  392.                     ->getRepository('OceanExpertBundle:MemberEditsCountry');
  393.                 $query = $em->createQueryBuilder('m')
  394.                     ->select('m.idCountry')
  395.                     ->where('m.idInd =:idInd')
  396.                     ->setParameter('idInd', $itemId)
  397.                     ->getQuery();
  398.                 $countries = $query->getResult();
  399.                 $expert['countries'] = array_column($countries, 'idCountry');
  400.                 $em = $this->getDoctrine()
  401.                     ->getRepository('OceanExpertBundle:MemberEditsInstitution');
  402.                 $expert['canEditOwnInst'] = 0;
  403.                 if (isset($expert[2]['idInst'])) {
  404.                     $query = $em->createQueryBuilder('m')
  405.                         ->select('m')
  406.                         ->where('m.idInd =:idInd')
  407.                         ->andWhere('m.idInst =:idInst')
  408.                         ->setParameters(
  409.                             array(
  410.                                 'idInst' => $expert[2]['idInst'],
  411.                                 'idInd' => $itemId
  412.                             )
  413.                         )
  414.                         ->getQuery();
  415.                     $canEditOwnInst = $query->getOneOrNullResult(AbstractQuery::HYDRATE_ARRAY);
  416.                     if (is_array($canEditOwnInst)
  417.                         && count($canEditOwnInst)
  418.                     ) {
  419.                         $expert['canEditOwnInst'] = 1;
  420.                     }
  421.                 }
  422.                 $address = new Address();
  423.                 $address = $address
  424.                     ->withCountryCode($expert['exrtCtryCode'])
  425.                     ->withAdministrativeArea($expert[0]['state'])
  426.                     ->withLocality($expert[0]['city'])
  427.                     ->withPostalCode($expert[0]['postcode'])
  428.                     ->withAddressLine2($expert[0]['addr2'])
  429.                     ->withAddressLine1($expert[0]['addr1']);
  430.                 $expert['expertAddress'] = $formatter->format($address);
  432.                 if (array_key_exists('instCtryCode',$expert)) {
  433.                     $address = new Address();
  434.                     $address = $address
  435.                         ->withCountryCode($expert['instCtryCode'])
  436.                         ->withAdministrativeArea($expert[2]['state'])
  437.                         ->withLocality($expert[2]['city'])
  438.                         ->withPostalCode($expert[2]['postcode'])
  439.                         ->withAddressLine2($expert[2]['addr2'])
  440.                         ->withAddressLine1($expert[2]['instAddress']);
  441.                     $expert['instAddress'] = $formatter->format($address);
  442.                 }
  443.             }
  445.             $fosUserData = $this->fosUserManager
  446.                 ->findUserBy(
  447.                     array(
  448.                         'id'=>$user
  449.                     )
  450.                 );
  452.             if ($fosUserData) {
  453.                 $expert['roles']=$fosUserData->getRoles();
  454.                 $expert['username'] = $fosUserData->getUsername();
  455.                 $expert['email'] = $fosUserData->getEmail();
  456.                 $userLink = $this->generateUrl(
  457.                     'view_profile',
  458.                     array(
  459.                         'user' => $fosUserData->getUsername()
  460.                     )
  461.                 );
  462.                 $expert['profileUrl'] = $request->getUriForPath($userLink);
  463.                 $expert['username'] = $fosUserData->getUsername();
  464.             }
  466.             $adminOption['countries'] = $this->getDoctrine()
  467.                 ->getRepository('OceanExpertBundle:Countries')
  468.                 ->findBy(
  469.                     [],
  470.                     ['country' => 'ASC']
  471.                 );
  473.             if (isset($expert[1]['path'])) {
  474.                 $expert['imgPath'] = $request
  475.                     ->getUriForPath('/uploads/profile/' . $expert[1]['path']);
  476.             }
  478.             $adminOption['userGroups'] = $this->getDoctrine()
  479.                 ->getManager()
  480.                 ->getRepository('OceanExpertBundle:Groups')
  481.                 ->findBy([], ['groupname' => 'ASC']);
  483.             $eventData = '';
  484.             if ($this->container->get('security.authorization_checker')->isGranted('IS_AUTHENTICATED_FULLY')) {
  485.                 $eventData = $this->getExpertEventParticipation($user, $request);
  486.             }
  488.             $OIHEventData = $this->getExpertEventParticipation($user, $request);
  489.             /*
  490.              * #552
  491.              *
  492.             foreach ($OIHEventData as $event) {
  493.                 $OIHData['knowsAbout'][] = array(
  494.                     '@type' => 'Event',
  495.                     'identifier' => 'https://www.oceanexpert.org/event/' . $event['idEvent'],
  496.                     'url' => 'https://www.oceanexpert.org/event/' . $event['idEvent'],
  497.                     'description' => $event['title'],
  498.                     'endDate' => $event['endOn'],
  499.                     'startDate' => $event['startOn']
  500.                 );
  501.             }
  502.             */
  504.             if (isset($metadata['orcid'])
  505.                 && $metadata['orcid'] != ''
  506.             ) {
  507.                 $OIHData['identifier'][] = array(
  508.                     '@id' => $metadata['orcid'],
  509.                     '@type' => 'PropertyValue',
  510.                     'propertyID' => 'https://registry.identifiers.org/registry/orcid',
  511.                     'url' => 'https://orcid.org/' . $metadata['orcid']
  512.                 );
  513.             }
  515.             if (isset($metadata['researcherid'])
  516.                 && $metadata['researcherid'] != ''
  517.             ) {
  518.                 $OIHData['identifier'][] = array(
  519.                     '@id' => $metadata['researcherid'],
  520.                     '@type' => 'PropertyValue',
  521.                     'propertyID' => 'https://www.researcherid.com',
  522.                     'url' => 'https://www.researcherid.com/' . $metadata['researcherid']
  523.                 );
  524.             }
  526.             if (isset($usermeta['researcharea'])
  527.                 && $usermeta['researcharea'] != ''
  528.             ) {
  529.                 $OIHData['knowsAbout'][] = array(
  530.                     '@type' => 'Text',
  531.                     'description' => $usermeta['researcharea']
  532.                 );
  533.             }
  534.             //dump($OIHData);
  535.             //die;
  536.         }
  538.         $OIHData = json_encode(
  539.             $OIHData,
  540.             JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_NUMERIC_CHECK
  541.         );
  543.         return $this->render(
  544.             'Profile/profile.html.twig',
  545.             array(
  546.                 'OIHData' => $OIHData,
  547.                 'expert' => $expert,
  548.                 'usermeta' => $usermeta,
  549.                 'metadata' => $metadata,
  550.                 'adminOption' => $adminOption,
  551.                 'eventData' => $eventData
  552.             )
  553.         );
  554.     }
  556.     /**
  557.      * @todo fix missing $data
  558.      */
  559.     public function userProfileAction(Request $request): Response
  560.     {
  561.         if (!isset($data['userEmail'])) {
  562.             if ($this->container->get('security.authorization_checker')->isGranted('IS_AUTHENTICATED_FULLY')) {
  563.                 $username = $this->get('security.token_storage')->getToken()->getUser()->getUsername();
  564.                 return $this->redirect('expert/' . $username);
  565.             }
  566.         }
  567.         if (isset($data['userEmail'])) {
  569.             $data = $request->request->all();
  570.             $userId = $this->fosUserManager->findUserByEmail($data['userEmail'])->getId();
  571.             unset($data['_wysihtml5_mode']);
  572.             $data = array_filter($data);
  573.             if (isset($data['researcharea'])) {
  574.                 $data['research'] = $this->getResearchAreaByCodes($data['researcharea']);
  575.             }
  576.             if (isset($data['studyregion'])) {
  577.                 $data['study'] = $this->getResearchAreaByCodes($data['studyregion']);
  578.             } else {
  579.                 $data['studyregion'] = array();
  580.             }
  582.             if (null !== ($data['country'])) {
  583.                 $data['countryName'] = $this->getCountryByCode($data['country']);
  584.             }
  585.             if (null !== ($data['jobtype'])) {
  586.                 $data['job'] = $this->getJobTypeByCodes($data['jobtype']);
  587.             }
  588.             if ($data['reg-type'] == '1') {
  589.                 $data['type'] = "Organisation";
  590.             } else {
  591.                 $data['type'] = "Personal";
  592.                 $data['country'] = $data['country2'];
  593.                 $data['locality'] = isset($data['city2']) ? $data['city2'] : '';
  594.                 $data['administrative_area_level_1'] = isset($data['state2']) ? $data['state2'] : '';
  595.                 $data['postal_code'] = isset($data['postcode2']) ? $data['postcode2'] : '';
  596.                 $data['box'] = isset($data['box2']) ?: '';
  597.                 $data['route'] = isset($data['address2']) ? $data['address2'] : '';
  598.                 $data['addrline2'] = isset($data['addrline2']) ? $data['addrline2'] : '';
  600.             }
  601.             if (null !== $request->files->get('file')) {
  602.                 $file = $request->files->get('file');
  603.                 $filetype = $request->files->get('file')->guessClientExtension();
  604.                 $filename = "profile_" . $userId . "." . $filetype;
  605.                 $em = $this->getDoctrine()->getManager();
  606.                 $item = $em->getRepository('OceanExpertBundle:ProfileImages')->findOneBy(array('idInd' => $userId));
  607.                 if ($item) {
  608.                     $item->setFile($file);
  609.                     $item->setName($filename);
  610.                     $item->setPath($filename);
  611.                     $item->upload();
  612.                     $em->persist($item);
  613.                     $em->flush();
  614.                     $data['profileId'] = $item->getId();
  615.                 } else {
  616.                     $profile = new ProfileImages();
  617.                     $profile->setFile($file);
  618.                     $profile->setName($filename);
  619.                     $profile->setPath($filename);
  620.                     $profile->setIdInd($userId);
  621.                     $profile->upload();
  622.                     $em->persist($profile);
  623.                     $em->flush();
  624.                     if ($profile) {
  625.                         $data['profileId'] = $profile->getId();
  626.                     }
  627.                 }
  628.             }
  630.             try {
  631.                 $privateaddress = isset($data['route'])?trim($data['route']) . '<br />':'';
  632.                 $privateaddress .= isset($data['addrline2'])?trim($data['addrline2']) . '<br />':'';
  633.                 $privateaddress .= isset($data['postal_code'])?trim($data['postal_code']).", ":'';
  634.                 $privateaddress .= isset($data['locality'])?trim($data['locality']).", ":'';
  635.                 $privateaddress .= isset($data['administrative_area_level_1'])?trim($data['administrative_area_level_1']) . '<br />':'';
  636.                 $privateaddress .= isset($data['country'])?trim($this->getCountryByCode($data['country'])) . '<br />':'';
  637.                 
  638.                 $indiv = new Indiv();
  639.                 $indiv->setTitle(isset($data['title']) ? $data['title'] : '');
  640.                 $indiv->setFname(isset($data['firstname']) ? $data['firstname'] : '');
  641.                 $indiv->setMname(isset($data['middlename']) ? $data['middlename'] : '');
  642.                 $indiv->setSname(isset($data['lastname']) ? $data['lastname'] : '');
  643.                 $indiv->setRegType(isset($data['reg-type']) ? $data['reg-type'] : '');
  644.                 $indiv->setIdentifier('');
  645.                 $indiv->setUseInstAddr(isset($data['instaddress']) ? $data['instaddress'] : 0);
  646.                 $indiv->setPrivateAddress($privateaddress);
  647.                 $indiv->setCity(isset($data['locality']) ? $data['locality'] : '');
  648.                 $indiv->setAddr1(isset($data['route']) ? $data['route'] : '');
  649.                 $indiv->setAddr2(isset($data['addrline2']) ? $data['addrline2'] : '');
  650.                 $indiv->setState(isset($data['administrative_area_level_1']) ? $data['administrative_area_level_1'] : '');
  651.                 $indiv->setPostcode(isset($data['postal_code']) ? $data['postal_code'] : '');
  652.                 $indiv->setCountryCode(isset($data['country']) ? $data['country'] : '');
  653.                 $indiv->setIDNationality(isset($data['id_nationality']) ? $data['id_nationality'] : 0);
  654.                 $indiv->setGender(isset($data['gender']) ? $data['gender'] : '');
  655.                 $indiv->setTel(isset($data['phone']) ? $data['phone'] : '');
  657.                 //we are already in a condition where we are sure the $data['userEmail'] exists
  658.                 $indiv->setEmail1($data['userEmail']);
  660.                 $indiv->setEmail2(isset($data['alternateEmail']) ? $data['alternateEmail'] : '');
  661.                 $indiv->setUrl1(isset($data['website-personal']) ? $data['website-personal'] : '');
  662.                 $indiv->setUrl2(isset($data['website-institution']) ? $data['website-institution'] : '');
  663.                 $indiv->setLinkedin(isset($data['linkedin']) ? $data['linkedin'] : '');
  664.                 $indiv->setFacebook(isset($data['facebook']) ? $data['facebook'] : '');
  665.                 $indiv->setTwitter(isset($data['twitter']) ? $data['twitter'] : '');
  666.                 $indiv->setOther(isset($data['other']) ? $data['other'] : '');
  667.                 $indiv->setDegree(isset($data['degree']) ? $data['degree'] : '');
  668.                 $indiv->setJobtitle(isset($data['job']) ? $data['job'] : '');
  669.                 $indiv->setDept(isset($data['department']) ? $data['department'] : '');
  670.                 $indiv->setStudyregion(isset($data['study']) ? $data['study'] : '');
  671.                 $indiv->setLanguages(implode(',', $data['languages']));
  672.                 $indiv->setComments(isset($data['comments']) ? $data['comments'] : '');
  673.                 $indiv->setSkills(isset($data['skills']) ? $data['skills'] : '');
  674.                 $indiv->setIdImagefile(isset($data['profileId']) ? $data['profileId'] : null);
  675.                 $indiv->setFDateEnt(new DateTime('now'));
  676.                 $indiv->setLDateUpd(new DateTime('now'));
  677.                 $indiv->setCreatedBy(isset($data['']) ? $data[''] : 0);
  678.                 $indiv->setLastEditBy(isset($data['']) ? $data[''] : 0);
  679.                 $indiv->setFax(isset($data['']) ? $data[''] : '');
  680.                 $indiv->setEmail2(isset($data['']) ? $data[''] : '');
  681.                 $indiv->setEmail3(isset($data['']) ? $data[''] : '');
  682.                 $indiv->setUrl3(isset($data['']) ? $data[''] : '');
  683.                 $indiv->setUrlsChecked(new DateTime('now'));
  684.                 $indiv->setFlickr(isset($data['']) ? $data[''] : '');
  685.                 $indiv->setActiveng(isset($data['']) ? $data[''] : '');
  686.                 $indiv->setActivother(isset($data['']) ? $data[''] : '');
  687.                 $indiv->setDoNotInvite(isset($data['']) ? $data[''] : 0);
  688.                 $indiv->setAdminComments(isset($data['']) ? $data[''] : '');
  689.                 $indiv->setIsGlobal(isset($data['']) ? $data[''] : 0);
  690.                 $indiv->setStatus(isset($data['']) ? $data[''] : 0);
  691.                 $indiv->setStatusLastChanged(new DateTime());
  692.                 $indiv->setDeceased(isset($data['']) ? $data[''] : 0);
  693.                 $indiv->setRetired(isset($data['']) ? $data[''] : 0);
  695.                 $em = $this->getDoctrine()->getManager();
  696.                 $em->persist($indiv); //marks object to be saved in the next transaction.
  697.                 $em->flush(); //performs all saves and transactions.
  698.                 if (!isset($data['orcid'])) {
  699.                     $data['orcid'] = '';
  700.                 }
  701.                 if (!isset($data['researcherid'])) {
  702.                     $data['researcherid'] = '';
  703.                 }
  704.                 if (!isset($data['google-scholar'])) {
  705.                     $data['google-scholar'] = '';
  706.                 }
  707.                 if (!isset($data['researchgate'])) {
  708.                     $data['researchgate'] = '';
  709.                 }
  710.                 if (!isset($data['others'])) {
  711.                     $data['others'] = '';
  712.                 }
  713.                 if (!isset($data['researcharea'])) {
  714.                     $data['researcharea'] = array();
  715.                 }
  717.                 if ($indiv->getIdInd()) {
  718.                     $usermeta = array(
  719.                         'orcid' => $data['orcid'],
  720.                         'researcherid' => $data['researcherid'],
  721.                         'researcharea' => implode(', ', $data['researcharea']),
  722.                         'google-scholar' => $data['google-scholar'],
  723.                         'researchgate' => $data['researchgate'],
  724.                         'others' => $data['others']
  725.                     );
  726.                     $usermeta = array_filter($usermeta);
  728.                     foreach ($usermeta as $key => $value) {
  729.                         $chkmeta = $em->getRepository('OceanExpertBundle:IndivMeta')
  730.                             ->findOneBy(
  731.                                 array(
  732.                                     'indivId' => $indiv->getIdInd(),
  733.                                     'metaOption' => $key
  734.                                 )
  735.                             );
  737.                         if ($chkmeta) {
  738.                             $chkmeta->setMetaValue($value);
  739.                             $em->persist($chkmeta);
  740.                             $em->flush();
  741.                         } else {
  742.                             $meta = new IndivMeta();
  743.                             $meta->setIndivId($indiv->getIdInd());
  744.                             $meta->setMetaOption($key);
  745.                             $meta->setMetaValue($value);
  746.                             $em = $this->getDoctrine()->getManager();
  747.                             $em->persist($meta);
  748.                             $em->flush();
  749.                         }
  750.                     }
  751.                 }
  752.             } catch (Exception $e) {
  753.                 //@todo what happens here Arno 10/06/2021
  754.             }
  755.         }
  756.         return $this->redirect($this->generateUrl('profile_status'));
  757.     }
  759.     /**
  760.      * edit an existing user profile
  761.      *
  762.      * @param int|null $userId the id of the profile to edit
  763.      *
  764.      * @return Response
  765.      *
  766.      * @throws \Psr\Container\ContainerExceptionInterface
  767.      * @throws \Psr\Container\NotFoundExceptionInterface
  768.      */
  769.     public function editAction(int $userId = null): Response
  770.     {
  771.         //only logged-in users can get the right to edit
  772.         //this is only a first check (see #493)
  773.         if (!$this->get('security.authorization_checker')->isGranted('ROLE_USER')) {
  774.             $url = $this->container->get('router')->generate('frontend_homepage');
  775.             return new RedirectResponse($url);
  776.         }
  778.         //we will alway need to know the current user to be sure that not everybody can change another account
  779.         $user = $this->get('security.token_storage')->getToken()->getUser();
  781.         if ($userId == '') {
  782.             //this is the simplest case
  783.             //no userid is given so edit the profile from the person that is logged in
  784.             $userId = $user->getId();
  785.         }
  787.         //we want to edit an existing user, so this user must have an entry in the fos_user db
  788.         $fosUserData = $this->fosUserManager
  789.             ->findUserBy(
  790.                 array(
  791.                     'id'=>$userId
  792.                 )
  793.             );
  794.         if (!$fosUserData) {
  795.             //no such user, strange...
  796.             $message = "There is no user with this id ($userId).";
  797.             return $this->render(
  798.                 'Default/error.html.twig',
  799.                 array(
  800.                     'message' => $message
  801.                 )
  802.             );
  803.         }
  805.         //here it gets tricky
  806.         //who can edit, what profile??
  807.         if ($userId == $user->getId() //no problem, everybody can adapt his/here own profile
  808.             || $this->get('security.authorization_checker')->isGranted('ROLE_ADMIN')) //no problem, admin users can change the profiles of others
  809.         {
  810.             $em = $this->getDoctrine()->getManager();
  811.             $indiv =  $this->getDoctrine()
  812.                 ->getRepository('OceanExpertBundle:Indiv')
  813.                 ->findOneByIdInd($userId);
  814.             if(!$indiv) {
  815.                 //create a new profile as it does not exist yet
  816.                 //this happens when people subscribe and want to complete their profile
  818.                 $indiv = new Indiv();
  819.                 $indiv->setTitle('');
  820.                 $indiv->setFname('');
  821.                 $indiv->setMname('');
  822.                 $indiv->setSname('');
  823.                 $indiv->setIdentifier('');
  824.                 $indiv->setRegType('');
  825.                 $indiv->setUseInstAddr(0);
  826.                 $indiv->setPrivateAddress('');
  827.                 $indiv->setAddr1('');
  828.                 $indiv->setAddr2('');
  829.                 $indiv->setCity('');
  830.                 $indiv->setState('');
  831.                 $indiv->setPostcode('');
  832.                 $indiv->setIdNationality('');
  833.                 $indiv->setTel('');
  834.                 $indiv->setFax('');
  835.                 $indiv->setEmail2('');
  836.                 $indiv->setEmail3('');
  837.                 $indiv->setUrl1('');
  838.                 $indiv->setUrl2('');
  839.                 $indiv->setUrl3('');
  840.                 $indiv->setGender('');
  841.                 $indiv->setLinkedin('');
  842.                 $indiv->setFacebook('');
  843.                 $indiv->setFlickr('');
  844.                 $indiv->setTwitter('');
  845.                 $indiv->setOther('');
  846.                 $indiv->setDegree('');
  847.                 $indiv->setActiveng('');
  848.                 $indiv->setActivother('');
  849.                 $indiv->setJobtitle('');
  850.                 $indiv->setDept('');
  851.                 $indiv->setStudyregion('');
  852.                 $indiv->setLanguages('');
  853.                 $indiv->setSkills('');
  854.                 $indiv->setComments('');
  855.                 $indiv->setDoNotInvite('');
  856.                 $indiv->setAdminComments('');
  857.                 $indiv->setIsGlobal(1);
  858.                 $indiv->setStatus(0);
  859.                 $indiv->setDeceased(0);
  860.                 $indiv->setRetired(0);
  861.                 $indiv->setIdInd($userId);
  862.                 $indiv->setCountryCode(21);
  863.                 $indiv->setEmail1($fosUserData->getEmail());
  864.                 $em->persist($indiv);
  865.                 $em->flush();
  866.             }
  867.         } else {
  868.             //this is not correct #491
  869.             $message = 'You do not have the correct rights (your are not this person and you are not an admin user) to edit this profile.';
  870.             return $this->render(
  871.                 'Default/error.html.twig',
  872.                 array(
  873.                     'message' => $message
  874.                 )
  875.             );
  876.         }
  878.         $qb = $this->getDoctrine()
  879.             ->getManager()
  880.             ->createQueryBuilder();
  882.         $qb->add('select', 'i, ins, p, c')
  883.             ->add('from', 'OceanExpertBundle:Indiv i')
  884.             ->leftJoin('OceanExpertBundle:ProfileImages', 'p', 'WITH', 'p.idInd = i.idInd')
  885.             ->leftJoin('OceanExpertBundle:IndivInstitution', 'idins', 'WITH', 'i.idInd = idins.idInd')
  886.             ->leftJoin('OceanExpertBundle:Institutions', 'ins', 'WITH', 'idins.idInst = ins.idInst')
  887.             ->leftJoin('OceanExpertBundle:Countries', 'c', 'WITH', 'c.idCountry = i.countryCode')
  888.             ->where('i.idInd = :userId')
  889.             ->setParameter('userId', $userId);
  891.         //get the expert data as far as we can find it
  892.         $expert = $qb->getQuery()->getResult(AbstractQuery::HYDRATE_ARRAY);
  894.         $metadata = $this->getMetaData($userId);
  895.         $jobType = $this->getJobTypes($userId);
  897.         $expert['jobType'] = $jobType['jobId'];
  898.         $expert['userId'] = $userId;
  899.         $expert['subjectArea'] = $this->getSubjectAreas($userId);
  900.         $expert['roles'] = $fosUserData->getRoles();
  901.         $expert['username'] = $fosUserData->getUsername();
  902.         $expert['email'] = $fosUserData->getEmail();
  904.         //get some data for the rest of the form
  905.         $countries = $this->getDoctrine()
  906.             ->getRepository('OceanExpertBundle:Countries')
  907.             ->findBy(
  908.                 [],
  909.                 ['country' => 'ASC']
  910.             );
  911.         $locales = $this->getDoctrine()
  912.             ->getRepository('OceanExpertBundle:Locale')
  913.             ->findBy(
  914.                 [],
  915.                 ['locale' => 'ASC']
  916.             );
  917.         $studyregions = $this->getDoctrine()
  918.             ->getRepository('OceanExpertBundle:Regions')
  919.             ->findBy(
  920.                 [],
  921.                 ['name' => 'ASC']
  922.             );
  923.         $instituteSeaRegion = $this->getDoctrine()
  924.             ->getRepository('OceanExpertBundle:Regions')
  925.             ->seaRegions();
  926.         $allSubjects = $this->getDoctrine()
  927.             ->getRepository('OceanExpertBundle:Subjects')
  928.             ->findBy(
  929.                 [],
  930.                 ['subname' => 'ASC']
  931.             );
  933.         return $this->render('Profile/editProfile.html.twig', array(
  934.             'expert' => $expert,
  935.             'countries' => $countries,
  936.             'locales' => $locales,
  937.             'nationalities' => $countries, 
  938.             'metadata' => $metadata, 
  939.             'studyregions' => $studyregions,
  940.             'availableSeaRegions' => $instituteSeaRegion,
  941.             'allSubjects' => $allSubjects
  942.         ));
  943.     }
  945.     public function updateProfileTimeAction(Request $request): Response
  946.     {
  947.         $security_context = $this->get('security.authorization_checker');
  948.         if ($security_context->isGranted('ROLE_USER')) {
  949.             $userId = $request->request->get('userId');
  950.             if ($userId) {
  951.                 $indiv = $this->getDoctrine()->getRepository('OceanExpertBundle:Indiv')->findOneByIdInd($userId);
  952.                 if ($indiv) {
  953.                     $indiv->setLDateUpd(new DateTime('now'));
  954.                     $indiv->setLastEditBy($security_context->getToken()->getUser()->getId());
  955.                     $em = $this->getDoctrine()->getManager();
  956.                     $em->persist($indiv); //marks object to be saved in the next transaction.
  957.                     $em->flush(); //performs all saves and transactions.
  958.                 }
  959.                 return new Response("updated successfully");
  960.             } else {
  961.                 return new Response("cannot update");
  962.             }
  963.         }
  964.         return new Response("cannot update");
  965.     }
  967.     public function activateUserAction($userId): Response
  968.     {
  969.         $security_context = $this->get('security.authorization_checker');
  971.         if ($security_context->isGranted('ROLE_GLOBAL_EDITOR')) {
  972.             if ($userId) {
  973.                 $indiv = $this->getDoctrine()
  974.                     ->getRepository('OceanExpertBundle:Indiv')
  975.                     ->findOneByIdInd($userId);
  976.                 if ($indiv) {
  977.                     $indiv->setStatus(1);
  978.                     $indiv->setStatusLastChanged(new DateTime('now'));
  979.                     $em = $this->getDoctrine()->getManager();
  980.                     $em->persist($indiv); //marks object to be saved in the next transaction.
  981.                     $em->flush(); //performs all saves and transactions.
  982.                 }
  983.                 return new JsonResponse(
  984.                     array(
  985.                         'status' => true,
  986.                         'msg' => "updated successfully"
  987.                     )
  988.                 );
  989.             } else {
  990.                 return new Response("cannot update");
  991.             }
  992.         }
  993.         return new Response($userId);
  994.     }
  996.     public function deactivateUserAction($userId): Response
  997.     {
  998.         $security_context = $this->get('security.authorization_checker');
  999.         $message = array();
  1000.         if ($security_context->isGranted('ROLE_GLOBAL_EDITOR')) {
  1001.             if ($userId) {
  1002.                 $em = $this->getDoctrine()->getManager();
  1004.                 //disable the login
  1005.                 $FOSUser = $em
  1006.                     ->getRepository('OceanExpertBundle:FosUser')
  1007.                     ->findOneById($userId);
  1008.                 if ($FOSUser) {
  1009.                     $FOSUser->setEnabled(0);
  1010.                     $FOSUser->setUpdatedAt(new DateTime('now'));
  1011.                     $em->persist($FOSUser);
  1012.                     $em->flush();
  1013.                 } else {
  1014.                     $message[] = array(
  1015.                         'id' => $userId,
  1016.                         'status' => 0,
  1017.                         'message' => "no user found in fos_user table with id $userId"
  1018.                     );
  1019.                 }
  1021.                 //deactivate the expert
  1022.                 $indiv = $em
  1023.                     ->getRepository('OceanExpertBundle:Indiv')
  1024.                     ->findOneByIdInd($userId);
  1025.                 if ($indiv) {
  1026.                     $indiv->setStatus(3);
  1027.                     $indiv->setStatusLastChanged(new DateTime('now'));
  1028.                     $em->persist($indiv); //marks object to be saved in the next transaction.
  1029.                     $em->flush(); //performs all saves and transactions.
  1030.                 } else {
  1031.                     $message[] = array(
  1032.                         'id' => $userId,
  1033.                         'status' => 0,
  1034.                         'message' => "no user found in indiv table with idInd $userId"
  1035.                     );
  1036.                 }
  1038.                 if (!count($message)) {
  1039.                     //no error messages yet so all should be fine
  1040.                     $message[] = array(
  1041.                         'id' => $userId,
  1042.                         'status' => true,
  1043.                         'message' => "user with idInd $userId deactivated"
  1044.                     );
  1045.                 }
  1046.             } else {
  1047.                 $message[] = array(
  1048.                     'id' => $userId,
  1049.                     'status' => 0,
  1050.                     'message' => 'incorrect or no user id given'
  1051.                 );
  1052.             }
  1053.         } else {
  1054.             $message[] = array(
  1055.                 'id' => $userId,
  1056.                 'status' => 0,
  1057.                 'message' => 'you do not have enough rights to do this'
  1058.             );
  1059.         }
  1061.         return new JsonResponse($message);
  1062.     }
  1064.     public function getNationalityFromId($id)
  1065.     {
  1066.         $em = $this->getDoctrine()->getRepository('OceanExpertBundle:Nationality')->findOneById($id);
  1067.         if ($em == null) {
  1068.             return false;
  1069.         } else {
  1070.             return $em->getNationality();
  1071.         }
  1072.     }
  1074.     public function getCountryFromId($id)
  1075.     {
  1076.         $em = $this->getDoctrine()->getRepository('OceanExpertBundle:Countries')->findOneByIdCountry($id);
  1077.         if ($em == null) {
  1078.             return false;
  1079.         } else {
  1080.             return $em->getCountry();
  1081.         }
  1082.     }
  1084.     public function getCountryCodeFromId($id)
  1085.     {
  1086.         $em = $this->getDoctrine()
  1087.             ->getRepository('OceanExpertBundle:Countries')
  1088.             ->findOneByIdCountry($id);
  1089.         if ($em == null) {
  1090.             return false;
  1091.         } else {
  1092.             return $em->getCountryCode();
  1093.         }
  1094.     }
  1097.     public function getCountryFromIds($countryArr)
  1098.     {
  1099.         $em = $this->getDoctrine()
  1100.             ->getRepository('OceanExpertBundle:Countries');
  1101.         $query = $em->createQueryBuilder('c')
  1102.             ->select('c.country')
  1103.             ->where('c.idCountry in (:idCountry)')
  1104.             ->setParameter('idCountry', $countryArr)
  1105.             ->getQuery();
  1107.         $countries = $query->getResult();
  1108.         $countryList = array_column($countries, 'country');
  1109.         return implode(', ', $countryList);
  1110.     }
  1113.     public function csvComma($value = '')
  1114.     {
  1116.         return "'" . implode("','", explode(",", $value)) . "'";
  1117.     }
  1119.     public function getJobTypeByCodes($jobArr)
  1120.     {
  1121.         $em = $this->getDoctrine()
  1122.             ->getRepository('OceanExpertBundle:Jobtypes');
  1123.         $query = $em->createQueryBuilder('a')
  1124.             ->select('a.jobname')
  1125.             ->where('a.idJob in (:jobArr)')
  1126.             ->setParameter('jobArr', $jobArr)
  1127.             ->getQuery();
  1129.         $jobs = $query->getResult();
  1130.         $jobList = array_column($jobs, 'jobname');
  1131.         return implode(', ', $jobList);
  1132.     }
  1134.     public function getResearchAreaByCodes($codeArr)
  1135.     {
  1136.         $em = $this->getDoctrine()
  1137.             ->getRepository('OceanExpertBundle:Regions');
  1138.         $query = $em->createQueryBuilder('a')
  1139.             ->select('a.name')
  1140.             ->where('a.idRegion in (:codeArr)')
  1141.             ->setParameter('codeArr', $codeArr)
  1142.             ->getQuery();
  1144.         $regions = $query->getResult();
  1145.         $regionsList = array_column($regions, 'name');
  1146.         return implode(', ', $regionsList);
  1147.     }
  1149.     /**
  1150.      * get all the meta data for a given userId
  1151.      * this data comes as a key-value pair
  1152.      *
  1153.      * @param int $userId the userId of the expert
  1154.      *
  1155.      * @return array
  1156.      */
  1157.     public function getMetaData(int $userId): array
  1158.     {
  1159.         $qb = $this->getDoctrine()->getManager()->createQueryBuilder();
  1160.         $qb->add('select', 'm.metaOption,m.metaValue')
  1161.             ->add('from', 'OceanExpertBundle:IndivMeta m')
  1162.             ->where('m.indivId = :userId')
  1163.             ->setParameter('userId', $userId);
  1165.         $meta = $qb->getQuery()->getResult(AbstractQuery::HYDRATE_ARRAY);
  1166.         $metadata = array();
  1167.         foreach ($meta as $value) {
  1168.             $metadata[$value['metaOption']] = $value['metaValue'];
  1169.         }
  1170.         return $metadata;
  1171.     }
  1173.     /**
  1174.      * get all the different job types for a given userId
  1175.      *
  1176.      * @param int $userId the userId of the expert
  1177.      *
  1178.      * @return array
  1179.      */
  1180.     public function getJobTypes(int $userId): array
  1181.     {
  1182.         $qb = $this->getDoctrine()->getManager()->createQueryBuilder();
  1183.         $qb->add('select', 'jt.jobname,jt.idJob')
  1184.             ->add('from', 'OceanExpertBundle:IndivJobtype j')
  1185.             ->leftJoin('OceanExpertBundle:Jobtypes', 'jt', 'WITH', 'jt.idJob = j.idJob')
  1186.             ->where('j.idInd = :userId')
  1187.             ->setParameter('userId', $userId);
  1189.         $meta = $qb->getQuery()->getResult(AbstractQuery::HYDRATE_ARRAY);
  1190.         $jobTypes = implode(
  1191.             '\r',
  1192.             array_column(
  1193.                 $meta,
  1194.                 'jobname'
  1195.             )
  1196.         );
  1197.         $jobId = implode(
  1198.             ',',
  1199.             array_column(
  1200.                 $meta,
  1201.                 'idJob'
  1202.             )
  1203.         );
  1204.         return array(
  1205.             'jobtype' => $jobTypes,
  1206.             'jobId' => $jobId
  1207.         );
  1208.     }
  1210.     public function getUserGroups($userId) {
  1211.         $qb = $this->getDoctrine()->getManager()->createQueryBuilder();
  1212.         $qb->add('select', 'g.groupname, g.idGroup')
  1213.             ->add('from', 'OceanExpertBundle:MemberGroups mg')
  1214.             ->leftJoin('OceanExpertBundle:Groups', 'g', 'WITH', 'mg.idGroup = g.idGroup')
  1215.             ->where('mg.idInd = :userId')
  1216.             ->setParameter('userId', $userId)
  1217.             ->orderBy('g.groupname','ASC');
  1219.         return $qb->getQuery()->getResult(AbstractQuery::HYDRATE_ARRAY);
  1220.     }
  1222.     /**
  1223.      * get all the subject areas for a given userId
  1224.      *
  1225.      * @param int $userId the userId of the expert
  1226.      *
  1227.      * @return string
  1228.      */
  1229.     public function getSubjectAreas(int $userId): string
  1230.     {
  1231.         $qb = $this->getDoctrine()->getManager()->createQueryBuilder();
  1232.         $qb->add('select', 's.subname,s.idSub')
  1233.             ->add('from', 'OceanExpertBundle:IndivSubjects ivs')
  1234.             ->leftJoin('OceanExpertBundle:Subjects', 's', 'WITH', 's.idSub = ivs.idSub')
  1235.             ->where('ivs.idInd = :userId')
  1236.             ->setParameter('userId', $userId);
  1238.         $meta = $qb->getQuery()->getResult(AbstractQuery::HYDRATE_ARRAY);
  1239.         return implode(
  1240.             ',',
  1241.             array_column(
  1242.                 $meta,
  1243.                 'subname'
  1244.             )
  1245.         );
  1246.     }
  1248.     public function getUpdatedBy($id = '')
  1249.     {
  1250.         $updatedBy = $this->getDoctrine()->getRepository('OceanExpertBundle:Indiv')->findOneByIdInd($id);
  1251.         if ($updatedBy) {
  1252.             $name = $updatedBy->getfname() . " " . strtoupper($updatedBy->getsname());
  1253.         } else {
  1254.             $name = false;
  1255.         }
  1256.         return $name;
  1257.     }
  1259.     public function qccontrolAction(Request $request): Response
  1260.     {
  1261.         $id = $request->request->get('userId');
  1262.         $action = $request->request->get('action');
  1263.         $return = '';
  1264.         $security_context = $this->get('security.authorization_checker');
  1265.         if ($security_context->isGranted('ROLE_GLOBAL_EDITOR')) {
  1266.             $author = $this->get('security.token_storage')->getToken()->getUser()->getId();
  1267.             $updatedBy = $this->getDoctrine()->getRepository('OceanExpertBundle:Indiv')->findOneByIdInd($id);
  1268.             if ($action == 1) {
  1269.                 $updatedBy->setQualityCheckedBy($author);
  1270.                 $updatedBy->setQualityCheckedDate(new DateTime('now'));
  1271.                 $updatedBy->setQualityChecked(1);
  1272.                 $return = "This record has been marked as quality controlled on <strong>" . date('F j, Y') . "</strong> by <strong>" . $this->getUpdatedBy($author)."</strong>";
  1273.             } else {
  1274.                 $updatedBy->setQualityCheckedBy(null);
  1275.                 $updatedBy->setQualityCheckedDate(null);
  1276.                 $updatedBy->setQualityChecked(0);
  1277.                 $return = "This record has not been quality controlled";
  1278.             }
  1279.             $updatedBy->setLDateUpd(new DateTime('now'));
  1280.             $updatedBy->setLastEditBy($author);
  1281.             $em = $this->getDoctrine()->getManager();
  1282.             $em->persist($updatedBy); //marks object to be saved in the next transaction.
  1283.             $em->flush(); //performs all saves and transactions.
  1284.         }
  1285.         return new Response($return);
  1286.     }
  1288.     public function setRetiredAction(Request $request): Response
  1289.     {
  1290.         $id = $request->request->get('userId');
  1291.         $date = $request->request->get('retiredDate');
  1293.         $retiredStatus = $request->request->get('retiredStatus');
  1294.         $security_context = $this->get('security.authorization_checker');
  1295.         if ($security_context->isGranted('ROLE_USER')) {
  1296.             $author = $this->get('security.token_storage')->getToken()->getUser()->getId();
  1297.             $updateRecord = $this->getDoctrine()->getRepository('OceanExpertBundle:Indiv')->findOneByIdInd($id);
  1298.             if ($updateRecord) {
  1299.                 if ($retiredStatus == 1) {
  1300.                     $updateRecord->setRetired(1);
  1301.                     if (trim($date) == '') {
  1302.                         $updateRecord->setRetiredDate(null);
  1303.                     } else {
  1304.                         $updateRecord->setRetiredDate(DateTime::createFromFormat('Y-m-d', $date));
  1305.                     }
  1306.                 } else {
  1307.                     $updateRecord->setRetired(0);
  1308.                     $updateRecord->setRetiredDate(null);
  1309.                 }
  1310.             }
  1311.             $updateRecord->setLDateUpd(new DateTime('now'));
  1312.             $updateRecord->setLastEditBy($author);
  1313.             $em = $this->getDoctrine()->getManager();
  1314.             $em->persist($updateRecord); //marks object to be saved in the next transaction.
  1315.             $em->flush(); //performs all saves and transactions.
  1316.         }
  1317.         return new Response($date);
  1318.     }
  1320.     public function setDeceasedAction(Request $request): Response
  1321.     {
  1322.         $id = $request->request->get('userId');
  1323.         $date = $request->request->get('deceasedDate');
  1324.         $deceasedStatus = $request->request->get('deceasedStatus');
  1325.         $return = '';
  1326.         $security_context = $this->get('security.authorization_checker');
  1327.         if ($security_context->isGranted('ROLE_GLOBAL_EDITOR')) {
  1328.             $author = $this->get('security.token_storage')->getToken()->getUser()->getId();
  1329.             $updateRecord = $this->getDoctrine()
  1330.                 ->getRepository('OceanExpertBundle:Indiv')
  1331.                 ->findOneByIdInd($id);
  1332.             if ($updateRecord) {
  1333.                 if ($deceasedStatus == 1) {
  1334.                     $updateRecord->setDeceased(1);
  1335.                     if (trim($date) == '') {
  1336.                         $updateRecord->setDeceasedDate(null);
  1337.                     } else {
  1338.                         $updateRecord->setDeceasedDate(DateTime::createFromFormat('Y-m-d', $date));
  1339.                     }
  1340.                 } else {
  1341.                     $updateRecord->setDeceased(0);
  1342.                     $updateRecord->setDeceasedDate(null);
  1343.                 }
  1344.             }
  1345.             $updateRecord->setLDateUpd(new DateTime('now'));
  1346.             $updateRecord->setLastEditBy($author);
  1347.             $em = $this->getDoctrine()->getManager();
  1348.             $em->persist($updateRecord); //marks object to be saved in the next transaction.
  1349.             $em->flush(); //performs all saves and transactions.
  1350.         }
  1351.         return new Response($return);
  1352.     }
  1354.     public function setDoNotInviteAction(Request $request): Response
  1355.     {
  1356.         $id = $request->request->get('userId');
  1357.         $status = $request->request->get('status');
  1358.         $return = '';
  1359.         $security_context = $this->get('security.authorization_checker');
  1360.         if ($security_context->isGranted('ROLE_USER')) {
  1361.             $author = $this->get('security.token_storage')->getToken()->getUser()->getId();
  1362.             $updateRecord = $this->getDoctrine()
  1363.                 ->getRepository('OceanExpertBundle:Indiv')
  1364.                 ->findOneByIdInd($id);
  1365.             if ($updateRecord) {
  1366.                 if ($status == 1) {
  1367.                     $updateRecord->setDoNotInvite(1);
  1368.                     $updateRecord->setDoNoInviteBy($author);
  1369.                     $updateRecord->setDoNoInviteOn(new DateTime('now'));
  1370.                     $return = 'This record has been set as do not invite on <strong>' . date('F j, Y') . '</strong>';
  1371.                     $return .= 'by <strong>' . $this->getUpdatedBy($author) . '</strong>';
  1372.                 } else {
  1373.                     $updateRecord->setDoNotInvite(0);
  1374.                     $updateRecord->setDoNoInviteBy(0);
  1375.                     $updateRecord->setDoNoInviteOn(NULL);
  1376.                     $return = 'Please select "Yes" to add this member to the do not invite list.';
  1377.                 }
  1378.             }
  1379.             $updateRecord->setLDateUpd(new DateTime('now'));
  1380.             $updateRecord->setLastEditBy($author);
  1381.             $em = $this->getDoctrine()->getManager();
  1382.             $em->persist($updateRecord); //marks object to be saved in the next transaction.
  1383.             $em->flush(); //performs all saves and transactions.
  1384.         }
  1385.         return new Response($return);
  1386.     }
  1388.     public function setAdmincommentsAction(Request $request): Response
  1389.     {
  1390.         $id = $request->request->get('userId');
  1391.         $comments = $request->request->get('comments');
  1392.         $return = '';
  1393.         if (trim($comments) != '') {
  1394.             $security_context = $this->get('security.authorization_checker');
  1395.             if ($security_context->isGranted('ROLE_GLOBAL_EDITOR')) {
  1396.                 $author = $this->get('security.token_storage')->getToken()->getUser()->getId();
  1397.                 $updateRecord = $this->getDoctrine()
  1398.                     ->getRepository('OceanExpertBundle:Indiv')
  1399.                     ->findOneByIdInd($id);
  1400.                 $comments .= '<br />';
  1401.                 $comments .= '(Added by ' . $this->getUpdatedBy($author);
  1402.                 $comments .= ' on ' . date('d-m-y');
  1403.                 $comments .= ')<br /><br />';
  1404.                 $comments .= $updateRecord->getAdminComments();
  1405.                 if ($updateRecord) {
  1406.                     $updateRecord->setAdminComments($comments);
  1407.                 }
  1408.                 $updateRecord->setLDateUpd(new DateTime('now'));
  1409.                 $updateRecord->setLastEditBy($author);
  1410.                 $em = $this->getDoctrine()->getManager();
  1411.                 $em->persist($updateRecord); //marks object to be saved in the next transaction.
  1412.                 $em->flush(); //performs all saves and transactions.
  1413.                 $return = $comments;
  1414.             }
  1415.         }
  1416.         return new Response($return);
  1417.     }
  1419.     public function setGroupsAction(Request $request): Response
  1420.     {
  1421.         $id = $request->request->get('userId');
  1422.         $groups = $request->request->get('groups');
  1423.         $return = '';
  1424.         $security_context = $this->get('security.authorization_checker');
  1425.         if ($security_context->isGranted('ROLE_GLOBAL_EDITOR')) {
  1426.             $author = $this->get('security.token_storage')
  1427.                 ->getToken()
  1428.                 ->getUser()
  1429.                 ->getId();
  1430.             $updateRecord = $this->getDoctrine()
  1431.                 ->getRepository('OceanExpertBundle:Indiv')
  1432.                 ->findOneByIdInd($id);
  1433.             if ($updateRecord) {
  1434.                 $updateRecord->setLDateUpd(new DateTime('now'));
  1435.                 $updateRecord->setLastEditBy($author);
  1436.                 $em = $this->getDoctrine()->getManager();
  1437.                 $em->persist($updateRecord); //marks object to be saved in the next transaction.
  1438.                 $em->flush(); //performs all saves and transactions.
  1439.             }
  1440.             if (is_array($groups)) {
  1441.                 $em = $this->getDoctrine()->getManager();
  1442.                 $results = $em->createQueryBuilder()
  1443.                     ->add('select', 'm')
  1444.                     ->add('from', 'OceanExpertBundle:MemberGroups m')
  1445.                     ->where('m.idGroup not in (:groups)')
  1446.                     ->andWhere('m.idInd =:idInd')
  1447.                     ->setParameters(array('groups' => $groups, 'idInd' => $id))
  1448.                     ->getQuery()->getResult();
  1450.                 foreach ($results as $result) {
  1451.                     $em->remove($result);
  1452.                 }
  1453.                 $em->flush();
  1455.                 foreach ($groups as $value) {
  1456.                     $updateGroup = $this->getDoctrine()
  1457.                         ->getRepository('OceanExpertBundle:MemberGroups')
  1458.                         ->find(
  1459.                             array(
  1460.                                 'idInd' => $id, 
  1461.                                 'idGroup' => $value
  1462.                             )
  1463.                         );
  1464.                     if (!$updateGroup) {
  1465.                         $addGroups = new MemberGroups();
  1466.                         $addGroups->setIdInd($id);
  1467.                         $addGroups->setIdGroup($value);
  1468.                         $addGroups->setRole('');
  1469.                         $addGroups->setMemberOrder(NULL);
  1470.                         $addGroups->setIsLeader(0);
  1471.                         $em = $this->getDoctrine()->getManager();
  1472.                         $em->persist($addGroups); //marks object to be saved in the next transaction.
  1473.                         $em->flush(); //performs all saves and transactions.
  1474.                     }
  1475.                 }
  1476.             } else {
  1477.                 $updateGroup = $this->getDoctrine()
  1478.                     ->getRepository('OceanExpertBundle:MemberGroups')
  1479.                     ->findBy(array('idInd' => $id));
  1480.                 $em = $this->getDoctrine()->getEntityManager();
  1481.                 foreach ($updateGroup as $groups) {
  1482.                     $em->remove($groups);
  1483.                 }
  1484.                 $em->flush();
  1485.             }
  1486.         }
  1487.         return new Response($return);
  1488.     }
  1490.     public function setPrivilegesAction(Request $request): Response
  1491.     {
  1492.         //get all the values from the request
  1493.         //these values are send from profile.html.twig $("#addPrivileges").click(function () {....
  1494.         $id = $request->request->get('userId');
  1495.         $countries = $request->request->get('countries');
  1496.         $ownInstitute = $request->request->get('ownInstitute');
  1497.         $countryList = $request->request->get('countryList');
  1498.         $editor = $request->request->get('editor');
  1501.         $security_context = $this->get('security.authorization_checker');
  1502.         if ($security_context->isGranted('ROLE_GLOBAL_EDITOR')) {
  1503.             $return = array(
  1504.                 'status' => 0,
  1505.                 'message' => array()
  1506.             );
  1507.             //who is doing this update
  1508.             $author = $this->get('security.token_storage')
  1509.                 ->getToken()
  1510.                 ->getUser()
  1511.                 ->getId();
  1513.             //what record needs to be updated
  1514.             $updateRecord = $this->getDoctrine()
  1515.                 ->getRepository('OceanExpertBundle:Indiv')
  1516.                 ->findOneByIdInd($id);
  1517.             if ($updateRecord) {
  1518.                 $updateRecord->setLDateUpd(new DateTime('now'));
  1519.                 $updateRecord->setLastEditBy($author);
  1520.                 $em = $this->getDoctrine()->getManager();
  1521.                 $em->persist($updateRecord); //marks object to be saved in the next transaction.
  1522.                 $em->flush(); //performs all saves and transactions.
  1523.             }
  1525.             if ($ownInstitute == 1) {
  1526.                 $updateInstEdit = $this->getDoctrine()
  1527.                     ->getRepository('OceanExpertBundle:MemberEditsInstitution')
  1528.                     ->findOneBy(array(
  1529.                             'idInd' => $id,
  1530.                             'idInst' => $this->getIndivInstitute($id)
  1531.                         )
  1532.                     );
  1533.                 if (!$updateInstEdit) {
  1534.                     $updateInstEdit = new MemberEditsInstitution();
  1535.                     $updateInstEdit->setIdInd($id);
  1536.                     $updateInstEdit->setIdInst($this->getIndivInstitute($id));
  1537.                 } else {
  1538.                     $updateInstEdit->setIdInst($this->getIndivInstitute($id));
  1539.                 }
  1540.                 $em->persist($updateInstEdit); //marks object to be saved in the next transaction.
  1541.                 $em->flush(); //performs all saves and transactions.
  1542.                 $return['message'][] = 'update own institute activated';
  1543.             } else {
  1544.                 $qb = $em->createQueryBuilder();
  1545.                 $query = $qb->delete('OceanExpertBundle:MemberEditsInstitution', 'm')
  1546.                     ->where('m.idInd = :id')
  1547.                     ->andWhere('m.idInst = :idInst')
  1548.                     ->setParameters(
  1549.                         array(
  1550.                             'id' => $id,
  1551.                             'idInst' => $this->getIndivInstitute($id)
  1552.                         )
  1553.                     )
  1554.                     ->getQuery();
  1555.                 $query->execute();
  1556.                 $em->flush();
  1557.                 $return['message'][] = 'update own institute removed';
  1558.             }
  1559.             if (is_array($countries)
  1560.                 && $countryList == 1
  1561.             ) {
  1562.                 $em = $this->getDoctrine()->getManager();
  1563.                 $results = $em->createQueryBuilder()
  1564.                     ->add('select', 'm')
  1565.                     ->add('from', 'OceanExpertBundle:MemberEditsCountry m')
  1566.                     ->where('m.idCountry not in (:countries)')
  1567.                     ->andWhere('m.idInd =:idInd')
  1568.                     ->setParameters(
  1569.                         array(
  1570.                             'countries' => $countries, 
  1571.                             'idInd' => $id
  1572.                         )
  1573.                     )
  1574.                     ->getQuery()->getResult();
  1576.                 foreach ($results as $result) {
  1577.                     $em->remove($result);
  1578.                 }
  1579.                 $em->flush();
  1581.                 foreach ($countries as $value) {
  1582.                     $updateCountries = $this->getDoctrine()
  1583.                         ->getRepository('OceanExpertBundle:MemberEditsCountry')
  1584.                         ->find(array(
  1585.                             'idInd' => $id,
  1586.                                 'idCountry' => $value
  1587.                             )
  1588.                     );
  1589.                     if (!$updateCountries) {
  1590.                         $addCountry = new MemberEditsCountry();
  1591.                         $addCountry->setIdInd($id);
  1592.                         $addCountry->setIdCountry($value);
  1593.                         $em = $this->getDoctrine()->getManager();
  1594.                         $em->persist($addCountry); //marks object to be saved in the next transaction.
  1595.                         $em->flush(); //performs all saves and transactions.
  1596.                     }
  1597.                 }
  1598.                 $return['message'][] = 'countries added';
  1599.             } else {
  1600.                 $updateGroup = $this->getDoctrine()
  1601.                     ->getRepository('OceanExpertBundle:MemberEditsCountry')
  1602.                     ->findBy(array('idInd' => $id));
  1603.                 $em = $this->getDoctrine()->getEntityManager();
  1604.                 foreach ($updateGroup as $groups) {
  1605.                     $em->remove($groups);
  1606.                 }
  1607.                 $em->flush();
  1608.                 $return['message'][] = 'countries removed';
  1609.             }
  1612.             $user= $em->getRepository('OceanExpertBundle:User')
  1613.                 ->findOneBy(
  1614.                     array(
  1615.                         'id' => $id
  1616.                     )
  1617.                 );
  1618.             if($editor == 1) {
  1619.                 $user->addRole("ROLE_GLOBAL_EDITOR");
  1620.                 $return['message'][] = 'global editor activated';
  1621.             } elseif ($editor == 0) {
  1622.                 $user->removeRole('ROLE_GLOBAL_EDITOR');
  1623.                 $return['message'][] = 'global editor removed';
  1624.             }
  1625.             $em->persist($user);
  1626.             $em->flush();
  1628.         } else {
  1629.             $return = array(
  1630.                 'status' => 1,
  1631.                 'error' => 'you have no rights to do this'
  1632.             );
  1633.         }
  1634.         return new Response(json_encode($return));
  1635.     }
  1637.     public function resetPasswordLinkAction(Request $request): Response
  1638.     {
  1639.         if ($request->query->get('username')) {
  1640.             $username = $request->query->get('username');
  1641.         } else {
  1642.             $username = $request->request->get('username');
  1643.         }
  1645.         $user = $this->fosUserManager->findUserByUsernameOrEmail($username);
  1647.         if (null === $user) {
  1648.             $return = "Error in sending email. User not found.";
  1649.             return new Response($return);
  1650.         }
  1652.         if (null === $user->getConfirmationToken()) {
  1653.             $user->setConfirmationToken($this->fosTokenGenerator->generateToken());
  1654.         }
  1656.         $this->fosMailer->sendResettingEmailMessage($user);
  1657.         $user->setPasswordRequestedAt(new DateTime());
  1658.         $this->fosUserManager->updateUser($user);
  1659.         $return = "Password reset link send successfully.";
  1660.         if ($request->query->get('username')) {
  1661.             $email = $user->getEmail();
  1662.             if (false !== $pos = strpos($email, '@')) {
  1663.                 $email = '...' . substr($email, $pos);
  1664.             }
  1665.             return new RedirectResponse(
  1666.                 $this->generateUrl(
  1667.                     'fos_user_resetting_check_email',
  1668.                     array('email' => $email)
  1669.                 )
  1670.             );
  1671.         }
  1672.        
  1673.         return new Response($return);
  1674.     }
  1676.     public function getIndivInstitute($idInd)
  1677.     {
  1678.         $getInstId = $this->getDoctrine()
  1679.             ->getRepository('OceanExpertBundle:IndivInstitution')
  1680.             ->findOneByIdInd($idInd);
  1681.         if ($getInstId) {
  1682.             return $getInstId->getIdInst();
  1683.         } else {
  1684.             return 0;
  1685.         }
  1686.     }
  1688.     /**
  1689.      * @return Response
  1690.      */
  1691.     public function assignRolesAction(Request $request): Response
  1692.     {
  1693.         $username =  $request->request->get('username');
  1694.         $em = $this->getDoctrine()->getManager();
  1695.         $user= $em->getRepository("OceanExpertBundle:User")
  1696.             ->findOneBy(array('username' => $username));
  1697.         if ($request->request->get('superadmin')==1) {
  1698.             $user->addRole('ROLE_SUPERADMIN');
  1699.         } else {
  1700.             $user->removeRole('ROLE_SUPERADMIN');
  1701.         }
  1702.         if ($request->request->get('administrator')==1) {
  1703.             $user->addRole('ROLE_ADMIN');
  1704.         } else {
  1705.             $user->removeRole('ROLE_ADMIN');
  1706.         }
  1707.         if ($request->request->get('manager')==1) {
  1708.             $user->addRole('ROLE_MANAGER');
  1709.         } else {
  1710.             $user->removeRole('ROLE_MANAGER');
  1711.         }
  1712.         if ($request->request->get('lme')==1) {
  1713.             $user->addRole('ROLE_LME');
  1714.         } else {
  1715.             $user->removeRole('ROLE_LME');
  1716.         }
  1717.         $em->persist($user);
  1718.         $em->flush();
  1720.         return new JsonResponse(
  1721.             array(
  1722.                 'status' => 1 ,
  1723.                 'message' => 'Role assigned successfully',
  1724.                 'roles' => $username,
  1725.                 'user' => $user
  1726.             )
  1727.         );
  1728.     }
  1730.     /**
  1731.      * @return JsonResponse
  1732.      */
  1733.     public function getExpertsAjaxAction(Request $request): Response
  1734.     {
  1735.         if (!null == $request->query->get('q')) {
  1736.             $query = $request->query->get('q');
  1737.             $em = $this->getDoctrine()->getManager();
  1738.             $connection = $em->getConnection();
  1739.             $statement = $connection->prepare("SELECT 
  1740.                 i.id_ind AS id, 
  1741.                 i.fname, 
  1742.                 i.sname, 
  1743.                 i.jobtitle,
  1744.                 inst.inst_name,
  1745.                 c.country 
  1746.             FROM indiv i
  1747.             LEFT JOIN countries c ON c.id_country =  i.country_code
  1748.             LEFT JOIN indiv_institution ii ON ii.id_ind = i.id_ind
  1749.             LEFT JOIN institutions inst ON inst.id_inst = ii.id_inst
  1750.             LEFT JOIN countries ic ON ic.id_country =  inst.country_code
  1751.             WHERE ( MATCH (fname,sname) AGAINST (:searchterm IN BOOLEAN MODE) 
  1752.                 OR fname like '%$query%' 
  1753.                 OR fname like '%$query%' 
  1754.                 OR sname like '%$query%' 
  1755.                 OR sname like '%$query%') 
  1756.                 AND status = 1;
  1757.             ");
  1758.             $statement->bindValue('searchterm', $query);
  1759.             $statement->execute();
  1760.             $query = $statement->fetchAll();
  1761.                 
  1762.             $data = array();
  1763.             if($query) {
  1764.                 $data = array(
  1765.                     'incomplete_results' => false,
  1766.                     'items' => $query,
  1767.                     'total_count' => count($query),
  1768.                 );
  1769.             }
  1771.             return new JsonResponse($data);
  1772.         }
  1773.         return new JsonResponse(array());
  1774.     }
  1775.     
  1776.     function getExpertEventParticipation($idInd, $request) {
  1777.         $em = $this->getDoctrine()->getManager();
  1778.         $participation = $em->createQueryBuilder()
  1779.             ->add('select', 'ep.idEvent')
  1780.             ->add('from', 'OceanExpertBundle:EventParticipants ep')
  1781.             ->where('ep.idInd =:idInd')
  1782.             ->setParameters(array('idInd' => $idInd ))
  1783.             ->getQuery()->getResult();
  1784.         $participation = array_column($participation, "idEvent");
  1785.         $contacts = $em->createQueryBuilder()
  1786.             ->add('select', 'ec.idEvent')
  1787.             ->add('from', 'OceanExpertBundle:EventContacts ec')
  1788.             ->where('ec.idInd =:idInd')
  1789.             ->setParameters(array('idInd' => $idInd ))
  1790.             ->getQuery()->getResult();
  1791.         $contacts = array_column($contacts, "idEvent");
  1792.         $staff = $em->createQueryBuilder()
  1793.             ->add('select', 'es.idEvent')
  1794.             ->add('from', 'OceanExpertBundle:EventStaff es')
  1795.             ->where('es.idInd =:idInd')
  1796.             ->setParameters(array('idInd' => $idInd ))
  1797.             ->getQuery()->getResult();
  1798.         $staff = array_column($staff, "idEvent");
  1799.         $results = array_merge($participation,$contacts,$staff);
  1800.         $results = array_unique($results);
  1802.         $qb = $this->getDoctrine()->getManager()->createQueryBuilder();
  1803.         $qb->add('select', 'e.idEvent,e.title, e.startOn, e.endOn, e.address, e.city, e.state, e.postcode, c.country')
  1804.             ->add('from', 'OceanExpertBundle:Events e')
  1805.             ->leftJoin('OceanExpertBundle:Countries', 'c', 'WITH', 'e.idCountry = c.idCountry')
  1806.             ->where('e.idEvent in (:idEvent)')
  1807.             ->andWhere('e.status = 1')
  1808.             ->orderBy('e.startOn', 'DESC')
  1809.             ->setParameter('idEvent', $results );
  1810.         $resultData = $qb->getQuery()->getResult();
  1812.         $paginator = $this->get('knp_paginator');
  1813.         $members = $paginator->paginate(
  1814.             $resultData,
  1815.             $request->query->getInt('page', 1),
  1816.             5,
  1817.             array(
  1818.                 'pageParameterName' => 'page',
  1819.                 'sortDirectionParameterName' => 'dir'
  1820.             )
  1821.         );
  1823.         //@todo some code goes here (i.e. your symfony request dispatching)
  1825.         return $members;
  1826.     }
  1827. }