src/Notaris/FrontendBundle/Controller/AddressbookController.php line 13

Open in your IDE?
  1. <?php
  3. namespace Notaris\FrontendBundle\Controller;
  5. use Notaris\AdminBundle\Entity\Notary;
  6. use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
  7. use Symfony\Component\HttpFoundation\JsonResponse;
  8. use Symfony\Component\HttpFoundation\Request;
  10. class AddressbookController extends AbstractController
  11. {
  13.     public function searchAction(Request $request)
  14.     {
  15.         $localeService = $this->get('notaris.locale.listener');
  16.         $locale = $localeService->getLocale();
  18.         $manager = $this->getDoctrine()->getManager();
  20.         $subsite_repo = $manager->getRepository('Notaris\AdminBundle\Entity\Subsite');
  21.         $slug = 'adresboek';
  22.         if ($locale == 'fr') {
  23.             $slug = 'annuaire';
  24.         }
  26.         $subsite = $subsite_repo->getSubsiteBySlugAndLocale($slug, $locale);
  28.         $results = [];
  29.         $query = $request->query->get('query');
  30.         $queryLocation = $request->query->all('queryLocation');
  31.         /** @var  $notabaseOfficeRepository  \Notaris\AdminBundle\Entity\NotabaseOfficeRepository */
  32.         $notabaseOfficeRepository = $manager->getRepository('Notaris\AdminBundle\Entity\NotabaseOffice');
  33.         if (!empty($query) && !str_contains($query, '%')) {
  34.             $results = $notabaseOfficeRepository->getOfficesByName($query, $locale);
  35.         } elseif (!empty($queryLocation)) {
  36.             $postCodes = [];
  37.             foreach ($queryLocation as $location) {
  38.                 $hasPostcode = preg_match('~[0-9]+~', $location);
  39.                 if ($hasPostcode) {
  40.                     $postCodes[] = $location;
  41.                 } else {
  42.                     $postCodes = array_merge($postCodes, $notabaseOfficeRepository->getPostCodesByMunicipality($location, $locale));
  43.                 }
  44.             }
  46.             $mediators = !empty($request->query->get('mediators'));
  47.             $language = $request->query->get('language');
  48.             $results = $notabaseOfficeRepository->getOfficesByPostCodes($postCodes, $locale, $mediators, $language);
  49.         }
  51.         $showOnMap = $request->query->get('show_on_map');
  52.         if (!empty($showOnMap)) {
  53.             /** @var  $notaryRepository \Notaris\AdminBundle\Entity\NotaryRepository */
  54.             $notaryRepository = $manager->getRepository('Notaris\AdminBundle\Entity\Notary');
  55.             /** @var $svenBuildRepository \Notaris\AdminBundle\Entity\SvenBuildRepository * */
  56.             $svenBuildRepository = $manager->getRepository('Notaris\AdminBundle\Entity\SvenBuild');
  58.             $officeNumbers = array_column($results, 'officeNumber');
  59.             $notaries = $notaryRepository->getNotariesByEeNumbers($officeNumbers);
  60.             $results = [];
  62.             if (empty($notaries)) {
  63.                 return $this->render('@NotarisFrontend/addressbook/search.html.twig',
  64.                     [
  65.                     'query' => $query,
  66.                     'subsite' => $subsite,
  67.                     'results' => $results,
  68.                 ]);
  69.             }
  71.             foreach ($notaries as $notary) {
  72.                 $adressen = [];
  74.                 $infoAddress = new \stdClass();
  75.                 $infoAddress->type = 'default';
  76.                 $infoAddress->city = '';
  77.                 $infoAddress->object = false;
  78.                 foreach ($notary->getInfo() as $info) {
  79.                     if ($info->getLocale() == $locale) {
  80.                         $infoAddress->city = $info->getLocality();
  81.                         $infoAddress->object = $info;
  82.                         break;
  83.                     }
  84.                 }
  85.                 $adressen[$notary->getPostal()] = $infoAddress;
  87.                 /** @var  $notary \Notaris\AdminBundle\Entity\Notary */
  88.                 /** @var  $address \Notaris\AdminBundle\Entity\SvenAddress */
  89.                 foreach ($notary->getAddresses() as $address) {
  90.                     $infoAddress = new \stdClass();
  91.                     $infoAddress->type = 'extra';
  92.                     $infoAddress->city = '';
  93.                     $infoAddress->object = false;
  94.                     /** @var  $info \Notaris\AdminBundle\Entity\SvenAddressInfo */
  95.                     foreach ($address->getInfo() as $info) {
  96.                         if ($info->getLocale() == $locale) {
  97.                             $infoAddress->city = $info->getCity();
  98.                             $infoAddress->object = $info;
  99.                             break;
  100.                         }
  101.                     }
  102.                     $adressen[$address->getZipcode()] = $infoAddress;
  103.                 }
  105.                 $currentPostal = $notary->getPostal();
  106.                 $currentAddress = $adressen[$currentPostal];
  108.                 $p = $this->makeNotaryObject($notary, $currentPostal, $currentAddress);
  109.                 $results[] = $p;
  111.                 if (sizeof($adressen) > 1) {
  112.                     foreach ($adressen as $key => $address) {
  113.                         if ($key != $currentPostal) {
  114.                             $p = $this->makeNotaryObject($notary, $key, $address);
  115.                             $results[] = $p;
  116.                         }
  117.                     }
  118.                 }
  119.             }
  121.             return $this->render('@NotarisFrontend/search/notariskaart.html.twig', [
  122.                 'subsite' => $subsite,
  123.                 'results' => $results,
  124.             ]);
  125.         }
  127.         return $this->render('@NotarisFrontend/addressbook/search.html.twig',
  128.             [
  129.             'query' => $query,
  130.             'subsite' => $subsite,
  131.             'results' => $results,
  132.         ]);
  133.     }
  135.     public function autocompleteAction(Request $request)
  136.     {
  137.         $localeService = $this->get('notaris.locale.listener');
  138.         $locale = $localeService->getLocale();
  139.         $manager = $this->getDoctrine()->getManager();
  141.         $results = [];
  142.         $query = $request->query->get('term');
  143.         if (!empty($query) && !str_contains($query, '%')) {
  144.             /** @var  $notabaseOfficeRepository  \Notaris\AdminBundle\Entity\NotabaseOfficeRepository */
  145.             $notabaseOfficeRepository = $manager->getRepository('Notaris\AdminBundle\Entity\NotabaseOffice');
  146.             $results = $notabaseOfficeRepository->getOfficesByName($query, $locale);
  147.         }
  149.         return new JsonResponse($results);
  150.     }
  152.     public function autocompleteByLocationAction(Request $request)
  153.     {
  154.         $localeService = $this->get('notaris.locale.listener');
  155.         $locale = $localeService->getLocale();
  156.         $manager = $this->getDoctrine()->getManager();
  158.         $results = [];
  159.         $query = $request->query->get('term');
  160.         if (!empty($query) && !str_contains($query, '%')) {
  161.             /** @var  $notabaseOfficeRepository  \Notaris\AdminBundle\Entity\NotabaseOfficeRepository */
  162.             $notabaseOfficeRepository = $manager->getRepository('Notaris\AdminBundle\Entity\NotabaseOffice');
  163.             $results = is_numeric($query)
  164.                 ? $notabaseOfficeRepository->getMunicipalitiesByPostCode($query, $locale)
  165.                 : $notabaseOfficeRepository->getMunicipalitiesByAddress($query, $locale);
  166.         }
  168.         return new JsonResponse($results);
  169.     }
  171.     private function makeNotaryObject(Notary $notary, $currentPostal, $currentAddress)
  172.     {
  173.         $notaryObject = new \stdClass();
  174.         $notaryObject->type = 'notary';
  175.         $notaryObject->name = $notary->getName();
  176.         $notaryObject->slug = $notary->getSlug();
  177.         $notaryObject->id = 'n_' . $notary->getId();
  178.         $notaryObject->city = $currentAddress->city;
  180.         if ($currentAddress->type == 'default') {
  181.             $notaryObject->lat = $notary->getLat();
  182.             $notaryObject->lng = $notary->getLng();
  183.             $notaryObject->address = $notary->getStreet();
  184.             $notaryObject->address .= ' ' . $notary->getNumber();
  185.             if ($notary->getBox() != '') {
  186.                 $notaryObject->address .= ' /' . $notary->getBox();
  187.             }
  188.             $notaryObject->address .= ', ' . $notary->getPostal();
  189.             $notaryObject->address .= ' ' . $notaryObject->city;
  190.             $notaryObject->tel = $notary->getPhone();
  191.             $notaryObject->fax = $notary->getFax();
  192.         } else {
  193.             $adresinfo = $currentAddress->object;
  194.             /** @var  $adresinfo \Notaris\AdminBundle\Entity\SvenAddressInfo */
  195.             if ($adresinfo) {
  196.                 $latlng = explode(',', $adresinfo->getAddress()->getLatlng());
  198.                 if (sizeof($latlng) > 1) {
  199.                     $notaryObject->lat = $latlng[0];
  200.                     $notaryObject->lng = $latlng[1];
  201.                 } else {
  202.                     $notaryObject->lat = '';
  203.                     $notaryObject->lng = '';
  204.                 }
  206.                 $notaryObject->address = $adresinfo->getStreet();
  207.                 $notaryObject->address .= ' ' . $adresinfo->getAddress()->getNumber();
  208.                 $notaryObject->address .= ', ' . $currentPostal;
  209.                 $notaryObject->address .= ' ' . $adresinfo->getCity();
  211.                 $notaryObject->tel = $adresinfo->getAddress()->getTel();
  212.                 $notaryObject->fax = $adresinfo->getAddress()->getFax();
  213.             }
  214.         }
  216.         $notaryObject->web = '';
  217.         foreach ($notary->getDomains() as $domain) {
  218.             if ($domain->getIsMain()) {
  219.                 $notaryObject->web = $domain->getUrl();
  220.             }
  221.         }
  223.         return $notaryObject;
  224.     }
  226.     public function searchDeedAction(Request $request)
  227.     {
  228.         $localeService = $this->get('notaris.locale.listener');
  229.         $locale = $localeService->getLocale();
  231.         $manager = $this->getDoctrine()->getManager();
  233.         $subsite_repo = $manager->getRepository('Notaris\AdminBundle\Entity\Subsite');
  234.         $slug = 'adresboek';
  235.         if ($locale == 'fr') {
  236.             $slug = 'annuaire';
  237.         }
  239.         $subsite = $subsite_repo->getSubsiteBySlugAndLocale($slug, $locale);
  241.         $query = $request->query->get('query');
  242.         $queryLocation = $request->query->all('queryLocation');
  243.         $queryLocationCustom = $request->query->get('query-location');
  244.         $queryYearMin = $request->query->get('query-year-min');
  245.         $queryYearMax = $request->query->get('query-year-max');
  246.         $results = [];
  247.         $processedResults = [];
  249.         if (!empty($queryLocationCustom) && empty($queryLocation)) {
  250.             return $this->render('@NotarisFrontend/addressbook/search_deed.html.twig',
  251.                 [
  252.                 'query' => $query,
  253.                 'subsite' => $subsite,
  254.                 'results' => $processedResults,
  255.             ]);
  256.         }
  258.         if (!empty($queryYearMin) && !empty($queryYearMax)) {
  259.             /** @var  $notabasePeriodRepository  \Notaris\AdminBundle\Entity\NotabasePeriodRepository */
  260.             $notabasePeriodRepository = $manager->getRepository('Notaris\AdminBundle\Entity\NotabasePeriod');
  261.             /** @var  $notabaseOfficeRepository  \Notaris\AdminBundle\Entity\NotabaseOfficeRepository */
  262.             $notabaseOfficeRepository = $manager->getRepository('Notaris\AdminBundle\Entity\NotabaseOffice');
  264.             $periods = $notabasePeriodRepository->createQueryBuilder('p')
  265.                 ->select('p, pe.slug')
  266.                 ->leftJoin('NotarisAdminBundle:Person', 'pe', 'WITH', 'pe.rbrnotId = p.personNumber')
  267.                 ->where('(p.addressStartDate > :queryYearMin OR p.addressEndDate > :queryYearMin OR p.addressEndDate IS NULL) AND (p.addressStartDate < :queryYearMax OR p.addressEndDate < :queryYearMax)')
  268.                 ->setParameter('queryYearMin', $queryYearMin . '-01-01')
  269.                 ->setParameter('queryYearMax', $queryYearMax . '-12-31');
  271.             if (!empty($query)) {
  272.                 $periods = $periods->andWhere('p.firstName LIKE :name OR p.lastName LIKE :name OR CONCAT(p.firstName, \' \', p.lastName) LIKE :name OR CONCAT(p.lastName, \' \', p.firstName) LIKE :name')
  273.                     ->setParameter('name', '%' . $query . '%');
  274.             }
  276.             if (!empty($queryLocation)) {
  277.                 $locale = strtoupper($locale);
  278.                 $where = [];
  279.                 foreach ($queryLocation as $i => $location) {
  280.                     $hasPostcode = preg_match('~[0-9]+~', $location);
  281.                     if ($hasPostcode) {
  282.                         $municipality = $location;
  283.                     } else {
  284.                         $municipality = $notabaseOfficeRepository->getPostCodesByMunicipality($location, $locale);
  285.                     }
  287.                     $query = $manager->createQuery("
  288.                         SELECT DISTINCT ld.nis
  289.                         FROM Notaris\AdminBundle\Entity\NotabaseLocalityData ld
  290.                         WHERE ld.name{$locale}Part1 IN (:postCodes)
  291.                     ")->setParameter('postCodes', $municipality);
  293.                     $results = $query->getResult();
  294.                     if (empty($results) || empty($results[0]['nis'])) {
  295.                         continue;
  296.                     }
  298.                     if ($hasPostcode) {
  299.                         $postCode = substr($location, 0, 4);
  300.                         $where[] = "p.postCode = :postCode$i AND p.nis = :nis$i ";
  301.                         $periods = $periods->setParameter('nis' . $i, $results[0]['nis'])
  302.                             ->setParameter('postCode' . $i, $postCode);
  303.                     } else {
  304.                         $where[] = " p.nis = :nis$i ";
  305.                         $periods = $periods->setParameter('nis' . $i, $results[0]['nis']);
  306.                     }
  307.                 }
  308.                 $where = implode(' OR ', $where);
  310.                 $periods = $periods->andWhere($where);
  311.             }
  313.             $results = $periods->groupBy('p.personNumber')
  314.                 ->getQuery()
  315.                 ->getResult();
  317.             $processedResults = [];
  318.             foreach ($results as $result) {
  319.                 $processedResult = $result[0];
  320.                 $processedResult->slug = $result['slug'];
  322.                 $periodLocations = $notabasePeriodRepository->createQueryBuilder('p')
  323.                     ->select('p, CASE WHEN p.addressEndDate IS NULL THEN 1 ELSE 0 END HIDDEN _isFieldNull')
  324.                     ->where('p.personNumber = :personNumber')
  325.                     ->setParameter('personNumber', $processedResult->getPersonNumber())
  326.                     ->orderBy('_isFieldNull', 'DESC')
  327.                     ->addOrderBy('p.postCode')
  328.                     ->getQuery()
  329.                     ->getResult();
  331.                 $processedResult->location = '';
  332.                 $processedResult->orderPostCode = '';
  333.                 $processedResult->orderLocality = '';
  334.                 if (!empty($periodLocations[0])) {
  335.                     $processedResult->orderPostCode = $periodLocations[0]->getPostCode();
  336.                     $processedResult->orderLocality = $locale === 'nl' ? $periodLocations[0]->getLocalityNL() : $periodLocations[0]->getLocalityFR();
  337.                 }
  339.                 $locations = [];
  340.                 foreach ($periodLocations as $periodLocation) {
  341.                     $locations[] = $periodLocation->getPostCode() . ' ' . ($locale === 'nl' ? $periodLocation->getLocalityNL() : $periodLocation->getLocalityFR());
  342.                 }
  343.                 $locations = array_values(array_unique($locations));
  344.                 $processedResult->location = implode(' - ', $locations);
  346.                 $processedResults[] = $processedResult;
  347.             }
  348.         }
  350.         return $this->render('@NotarisFrontend/addressbook/search_deed.html.twig',
  351.             [
  352.             'query' => $query,
  353.             'subsite' => $subsite,
  354.             'results' => $processedResults,
  355.         ]);
  356.     }
  359.     public function notaryAction(Request $request, $personSlug)
  360.     {
  361.         $localeService = $this->get('notaris.locale.listener');
  362.         $locale = $localeService->getLocale();
  364.         $manager = $this->getDoctrine()->getManager();
  366.         $subsite_repo = $manager->getRepository('Notaris\AdminBundle\Entity\Subsite');
  367.         $slug = 'adresboek';
  368.         if ($locale == 'fr') {
  369.             $slug = 'annuaire';
  370.         }
  371.         $subsite = $subsite_repo->getSubsiteBySlugAndLocale($slug, $locale);
  373.         /** @var  $notabasePeriodRepository  \Notaris\AdminBundle\Entity\NotabasePeriodRepository */
  374.         $notabasePeriodRepository = $manager->getRepository('Notaris\AdminBundle\Entity\NotabasePeriod');
  375.         /** @var  $personRepository  \Notaris\AdminBundle\Entity\Person */
  376.         $personRepository = $manager->getRepository('Notaris\AdminBundle\Entity\Person');
  378.         $person = $personRepository->findOneBySlug($personSlug);
  379.         if (!$person) {
  380.             throw $this->createNotFoundException('This Notary does not exist');
  381.         }
  382.         $personNumber = $person->getRbrnotId();
  384.         $periods = $notabasePeriodRepository->createQueryBuilder('p')
  385.             ->select('p, CASE WHEN p.addressEndDate IS NULL THEN 1 ELSE 0 END HIDDEN _isFieldNull')
  386.             ->where('p.personNumber = :personNumber AND p.minuteholderLastName IS NOT NULL')
  387.             ->setParameter('personNumber', $personNumber)
  388.             ->groupBy('p.officeNumber, p.addressStartDate, p.replacedNumber')
  389.             ->orderBy('_isFieldNull', 'DESC')
  390.             ->addOrderBy('p.addressEndDate', 'DESC')
  391.             ->getQuery()
  392.             ->getResult();
  394.         foreach ($periods as $period) {
  395.             $period->minuteholderSlug = null;
  396.             if (!empty($period->getMinuteholderOfficeNumber())) {
  397.                 $notaryRepository = $manager->getRepository('Notaris\AdminBundle\Entity\Notary');
  398.                 $notary = $notaryRepository->findOneByEe($period->getMinuteholderOfficeNumber());
  399.                 if (!empty($notary)) {
  400.                     $period->minuteholderSlug = $notary->getSlug();
  401.                 }
  402.             }
  403.         }
  405.         //backlink
  406.         $backlink = $request->headers->get('referer');
  407.         $host = parse_url($backlink, PHP_URL_HOST);
  409.         if ($backlink == '' or $request->getHttpHost() != $host) {
  410.             $backlink = $this->generateUrl('notaris_frontend_addressbook');
  411.         }
  413.         return $this->render('@NotarisFrontend/addressbook/notary.html.twig',
  414.             [
  415.             'periods' => $periods,
  416.             'subsite' => $subsite,
  417.             'personSlug' => $personSlug,
  418.             'backlink' => $backlink
  419.         ]);
  420.     }
  421. }