src/Ox/HoardBundle/Controller/SearchController.php line 296

Open in your IDE?
  1. <?php
  3. namespace App\Ox\HoardBundle\Controller;
  5. use Symfony\Component\HttpFoundation\Request;
  6. use Symfony\Component\HttpFoundation\Response;
  7. use Symfony\Component\HttpFoundation\JsonResponse;
  8. use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
  9. use Sensio\Bundle\FrameworkExtraBundle\Configuration\Method;
  10. use Sensio\Bundle\FrameworkExtraBundle\Configuration\Route;
  11. use Sensio\Bundle\FrameworkExtraBundle\Configuration\Template;
  12. use Symfony\Component\Security\Core\Exception\AccessDeniedException;
  13. use Symfony\Component\Security\Core\Security;
  14. use Doctrine\ORM\Query\Expr\Select;
  15. use Doctrine\ORM\Query\Expr\From;
  16. use Symfony\Component\Form\Extension\Core\Type\SubmitType;
  17. use Symfony\Component\Form\Extension\Core\Type\ButtonType;
  18. use Symfony\Component\Form\Extension\Core\Type\HiddenType;
  19. use Symfony\Component\Form\Extension\Core\Type\CheckboxType;
  20. use Symfony\Component\Form\Extension\Core\Type\ChoiceType;
  21. use Knp\Component\Pager\PaginatorInterface;
  23. use App\Ox\HoardBundle\Entity\Hoard;
  24. use App\Ox\HoardBundle\Entity\HObject;
  25. use App\Ox\HoardBundle\Entity\Coin;
  26. use App\Ox\HoardBundle\Entity\Container;
  27. use App\Ox\HoardBundle\Entity\HoardCoinCount;
  28. use App\Ox\HoardBundle\Entity\HoardImage;
  29. use App\Ox\HoardBundle\Entity\OcreLookup;
  31. use App\Ox\HoardBundle\Form\HoardType;
  32. use App\Ox\HoardBundle\Form\HoardSearchType;
  33. use App\Ox\HoardBundle\Form\SummaryCoinType;
  34. use App\Ox\HoardBundle\Form\SearchType;
  36. /**
  37.  * Hoard controller.
  38.  *
  39.  * @Route("/search")
  40.  */
  41. class SearchController extends AbstractController
  42. {
  43.     private $security;
  44.     public function __construct(Security $security)
  45.     {
  46.         $this->security = $security;
  47.     }
  48.     
  49.     /**
  50.      * Show an excel-like editing interface for a list of hoards
  51.      *
  52.      * @Route("/editmultiple", name="hoard_bulk_edit", methods={"GET"})
  53.      * @Template()
  54.      */
  55.     public function massEditAction(Request $request) {
  56.         
  57.         $em = $this->getDoctrine()->getManager();
  58.         
  59.         //get all available options for the various dropdowns
  60.         $data = array();
  61.         $data['hoardTypes'] = $em->getRepository('OxHoardBundle:HoardType')->findAll();
  62.         $data['countries'] = $em->getRepository('OxHoardBundle:Country')->findAll();
  63.         $data['provinces'] = $em->getRepository('OxHoardBundle:Province')->findAll();
  64.         $data['findSpotLocationDetails'] = $em->getRepository('OxHoardBundle:FindSpotLocationDetail')->findAll();
  65.         $data['ancientPlaces'] = $em->getRepository('OxHoardBundle:AncientPlace')->findAll();
  66.         $data['reigns'] = $em->getRepository('OxHoardBundle:Reign')->findAll();
  67.         $data['discoveryMethods'] = $em->getRepository('OxHoardBundle:DiscoveryMethod')->findAll();
  68.         $data['discoveryLandUses'] = $em->getRepository('OxHoardBundle:DiscoveryLandUse')->findAll();
  69.         $data['archaeologyRecoveryMethods'] = $em->getRepository('OxHoardBundle:ArchaeologyRecoveryMethod')->findAll();
  70.         $data['archaeologySiteContexts'] = $em->getRepository('OxHoardBundle:ArchaeologySiteContext')->findAll();
  71.         $data['archaeologySiteContextDetails'] = $em->getRepository('OxHoardBundle:ArchaeologySiteContextDetail')->findAll();
  72.         $data['archaeologyPeriods'] = $em->getRepository('OxHoardBundle:ArchaeologyPeriod')->findAll();
  73.         $data['archaeologyContextNatures'] = $em->getRepository('OxHoardBundle:ArchaeologyContextNature')->findAll();
  74.         $data['coinLevelDatas'] = $em->getRepository('OxHoardBundle:CoinLevelData')->findAll();
  75.         $data['ratings'] = $em->getRepository('OxHoardBundle:Rating')->findAll();
  76.         $data['hideWhats'] = $em->getRepository('OxHoardBundle:HideWhat')->findAll();
  77.         $data['hideFroms'] = $em->getRepository('OxHoardBundle:HideFrom')->findAll();
  78.         return array(
  79.             'data' => $data
  80.         );
  81.     }
  83.     /**
  84.      * Lists all Hoard entities.
  85.      *
  86.      * @Route("/", name="hoard", methods={"GET", "POST"})
  87.      * @Template()
  88.      */
  89.     public function indexAction(Request $request, PaginatorInterface $paginator, $isCoinsChart = false) {
  90.         $activeTab = 0;   // Default to Search tab
  91.         // var_dump($request);
  92.         // var_dump($request->query);
  93.         $search_type = $request->query->get('search_type');
  94.         $format = $request->query->get('format');
  96.         $initialSliderValues = array();
  97.         $initialSliderValues['hoardDiscoveryYearRangeStart'] = $this->getMinDiscoveryDate();
  98.         $initialSliderValues['hoardDiscoveryYearRangeEnd'] = $this->getMaxDiscoveryDate();
  99.         $initialSliderValues['hoardTerminalYearRangeStart'] = $this->getMinTerminalDate();
  100.         $initialSliderValues['hoardTerminalYearRangeEnd'] = $this->getMaxTerminalDate();
  101.         $initialSliderValues['hoardOpeningYearRangeStart'] = $this->getMinOpeningDate();
  102.         $initialSliderValues['hoardOpeningYearRangeEnd'] = $this->getMaxOpeningDate();
  103.         $initialSliderValues['hoardArchaeologyContextDateRangeStart'] = $this->getMinArchaeologyContextDate();
  104.         $initialSliderValues['hoardArchaeologyContextDateRangeEnd'] = $this->getMaxArchaeologyContextDate();
  105.         $coinminmax = $this->getMinMaxCoinDate();
  106.         $initialSliderValues['coinYearRangeStart'] = $coinminmax['min1'];
  107.         $initialSliderValues['coinYearRangeEnd'] = $coinminmax['max1'];
  108.         $initialSliderValues['hoardCoinCountMin'] = 0;
  109.         $initialSliderValues['hoardCoinCountMax'] = 100000;
  111.         $isAdmin = $this->userIsAdmin();
  113.         $isAuthenticated = false;
  114.         if($this->getUser()) {
  115.             $isAuthenticated = true;
  116.         }
  117.         
  118.         $searchHasCoinFields = false;
  119.         
  120.         $limit = 20;
  121.         $em = $this->getDoctrine()->getManager();
  122.         $em->getFilters()->enable('softdeleteable');
  123.         if(isset($request->query->get('ox_hs')['rules']) && strlen($request->query->get('ox_hs')['rules'])) {
  124.           // this is an advanced search
  125.           
  126.           // check if coin fields are included in the search set $searchHasCoinFields
  127.           $rulesStr = $request->query->get('ox_hs')['rules'];
  128.           if ( strpos($rulesStr, '"field":"coin') || strpos($rulesStr, '"field":"c.') ) {
  129.             $searchHasCoinFields = true;
  130.           }
  131.           
  132.           $rules = json_decode($rulesStr, true);
  133.           $doctrineQueryBuilder = $this->buildQuery($rules, $search_type, $format);
  134.           $activeTab = 3;   // Set tab to Results tab
  135.         } 
  136.         else {
  137.             // this is a simple search
  138.             if ($request->query->get('ox_hs')) {
  139.                 foreach ($request->query->get('ox_hs') as $key => $value) {
  140.                     // check if coin fields are included in the search set $searchHasCoinFields
  141.                     if (substr( $key, 0, 8 ) === "coinYear") {
  142.                         if ($value != $initialSliderValues[$key]) {
  143.                             $searchHasCoinFields = true;
  144.                         }
  145.                     } 
  146.                     elseif (substr( $key, 0, 4 ) === "coin") {
  147.                         if ($value && $value != "") {
  148.                             $searchHasCoinFields = true;
  149.                         }
  150.                     }
  151.                 }
  152.             }
  154.           $doctrineQueryBuilder = $em->createQueryBuilder()
  155.             ->andWhere('h IS NOT NULL'); // always exclude deleted hoards (this leftJoin('c.hoard', 'h') will include coins from deleted hoards)
  157.           $didJoinCoins = false;
  158.           if ($format == 'csv-coins' || $format == 'csv') {
  159.             if ($format == 'csv-coins') {
  160.                 $doctrineQueryBuilder->select('h, c')->from('OxHoardBundle:Hoard', 'h')->leftJoin('h.coins', 'c');
  161.                 $didJoinCoins = true;
  162.             } elseif ($format == 'csv') {
  163.                 if ($search_type == 'search-coins' ) {
  164.                     $doctrineQueryBuilder->select('h')->from('OxHoardBundle:Hoard', 'h')->leftJoin('h.coins', 'c');
  165.                     $didJoinCoins = true;
  166.             } else {
  167.                     $doctrineQueryBuilder->select('h')->from('OxHoardBundle:Hoard', 'h');
  168.                 }
  169.             }
  170.           } else {
  171.               if ($search_type == 'search-coins' ) {
  172.                 $doctrineQueryBuilder->select('c, h')->from('OxHoardBundle:Coin', 'c')->leftJoin('c.hoard', 'h');
  173.                 $didJoinCoins = true;
  174.             } else {
  175.                 $doctrineQueryBuilder->select('h')->from('OxHoardBundle:Hoard', 'h');
  176.               }
  177.           }
  178.           if (($search_type == 'search-coins' && $format != 'csv') ||
  179.             $format == 'csv-coins') {
  180.              $doctrineQueryBuilder
  181.                  ->addSelect('hcountriessort') // countries are going to be used for the results order
  182.                  ->leftJoin('h.countries', 'hcountriessort');
  183.           }
  185.           $coinValues  = array();
  186.           // find coin-related query params to join Coins if needed
  187.           if (!empty($request->query->get('ox_hs'))) {
  188.             foreach ($request->query->get('ox_hs') as $key => $value) {
  189.                 if (is_array($value) or strlen($value)) {
  190.                     if (strpos($key, 'coin') === 0) {
  191.                         $coinValues[$key] = $value;
  192.                     }
  193.                 }
  194.             }
  195.           }
  196.           //ignore coin year range if set to initial values
  197.           if(isset($coinValues['coinYearRangeStart']) && $coinValues['coinYearRangeStart'] == $initialSliderValues['coinYearRangeStart']) {
  198.             unset($coinValues['coinYearRangeStart']);
  199.           }
  200.           if(isset($coinValues['coinYearRangeEnd']) && $coinValues['coinYearRangeEnd'] == $initialSliderValues['coinYearRangeEnd']) {
  201.             unset($coinValues['coinYearRangeEnd']);
  202.           }
  203.           // only join coins to hoards if coins fields were used
  204.           if (!$didJoinCoins && count($coinValues) > 0) {
  205.             $doctrineQueryBuilder->leftJoin('h.coins', 'c');
  206.           }
  208.           // If the request is a POST, read the POST parameters from the search form and construct a search query
  209.           $hoardValues = array();
  210.           $coinValues  = array();
  211.           $queryParameters = array();
  212.           if (!empty($request->query->get('ox_hs'))) {
  213.               $this->constructSearchQuery($request, $doctrineQueryBuilder, $activeTab,
  214.                                           $hoardValues, $coinValues, $queryParameters, $initialSliderValues);
  215.           }
  217.           //apply the 'only mine'
  218.           if (isset($request->query->get('ox_hs')['showOnlyUsersHoards'])) {
  219.               $doctrineQueryBuilder->andWhere('(h.created = :queryUser) OR (h.modified = :queryUser)')
  220.               ->setParameter('queryUser', $this->getUser());
  221.           }
  222.           
  223.           foreach ($queryParameters as $key => $value) {
  224.               $doctrineQueryBuilder->setParameter($key, $value);
  225.           }
  226.         }
  227.         // Add handling of boundaries set for maps.
  228.         $boundary = isset($request->query->get('ox_hs')["boundary"]) ?
  229.             $request->query->get('ox_hs')["boundary"] :
  230.             null;
  231.         if ($boundary) {
  232.             $doctrineQueryBuilder->andWhere('ST_Contains(ST_GEOMFROMTEXT(\'Polygon((' . $boundary . '))\'), Point(h.findSpotLatitude, h.findSpotLongitude)) = true');
  233.         }
  235.         if (!$isAuthenticated) {
  236.             // public can only see hoards which are validated by the user,
  237.             // and which are either not hidden, or don't have everything hidden
  238.             $doctrineQueryBuilder->andWhere('(h.validatedByUser = true) AND (h.hideFrom = 3 OR h.hideWhat < 3 OR h.hideWhat IS NULL)');
  240.             // if coin fields are included in the search
  241.             if ($searchHasCoinFields) {
  242.                 // public can only see hoards with coins which are validated by the user,
  243.                 // and which are either not hidden, or don't have anything hidden
  244.                 $doctrineQueryBuilder->andWhere('(h.coinDataValidatedByUser = true) AND (h.hideFrom = 3 OR h.hideWhat IS NULL)');
  245.             }
  246.         }
  248.         // decide the order of the items
  249.         if ($format == 'csv-coins') {
  250.             $doctrineQueryBuilder->orderBy('hcountriessort.country', 'ASC');
  251.             $doctrineQueryBuilder->orderBy('h.findSpotName', 'ASC');
  252.             $doctrineQueryBuilder->orderBy('h.id', 'DESC');
  253.         }
  254.         else if ($format == 'csv'|| $search_type != 'search-coins') {
  255.             $sort = $request->query->get('sort');
  256.             if ($this->getUser() && !$sort) {
  257.                 $sort = 'h.modifiedDate';
  258.             }
  259.             $sortDirection = $request->query->get('direction');
  260.             
  261.             if (!$sortDirection){
  262.                 // default to ascending except for modified/creationDate
  263.                 if ($sort=='h.modifiedDate' || $sort=='h.creationDate') {
  264.                     $sortDirection = "DESC";
  265.                 } 
  266.                 else {
  267.                     $sortDirection = "ASC";
  268.                 }
  269.             }
  270.             
  271.             if ($sort) {
  272.                 $doctrineQueryBuilder->orderBy($sort, $sortDirection);
  273.             }
  274.         } 
  275.         else {
  276.             $doctrineQueryBuilder->orderBy('hcountriessort.country', 'ASC');
  277.             $doctrineQueryBuilder->orderBy('h.findSpotName', 'ASC');
  278.             $doctrineQueryBuilder->orderBy('h.id', 'DESC');
  279.         }
  281.         // if the indexAcion was called to produce the coin entries for the charts
  282.         if ($isCoinsChart) {
  283.             $doctrineQuery = $doctrineQueryBuilder->getQuery();
  284.             return $doctrineQuery->getResult();
  285.         }
  287.         $MAX_HOARDS_IN_CHART = 5000;
  288.         $doctrineQueryBuilderAllRows = clone $doctrineQueryBuilder;
  289.         $doctrineQuery = $doctrineQueryBuilder->getQuery();
  290.         
  291.         // we have to use paginator to find if results are more than 1001 bacause doctrine distinct doesn't work as expected:
  292.         // https://stackoverflow.com/questions/50199102/setmaxresults-does-not-works-fine-when-doctrine-query-has-join
  293.         $paginatorAllRows = $paginator->paginate(
  294.             $doctrineQueryBuilderAllRows->getQuery(),
  295.             $request->query->getInt('page', 1),
  296.             $MAX_HOARDS_IN_CHART+1
  297.         );
  298.         $c = count($paginatorAllRows);
  300.         $allHoards = array();
  301.         $allHoardResults = array();
  302.         $hoardsOverEditLimit = true;
  303.         $hoardsOverChartLimit = true;
  304.         if ($c < 1000) {
  305.             $hoardsOverEditLimit = false;
  306.         }
  307.         if ($c <= $MAX_HOARDS_IN_CHART) {
  308.           $hoardsOverChartLimit = false;
  309.           $doctrineQueryBuilderAllRows->distinct();
  310.           $doctrineQueryAllRows = $doctrineQueryBuilderAllRows->getQuery();
  311.           $allHoardResults = $doctrineQueryAllRows->getResult();
  312.           foreach (array_slice($allHoardResults, 0, 1000) as $hoard) {
  313.             $h = array();
  314.             $h['id'] = $hoard->getId();
  315.             if ($this->checkAccess($hoard, 'edit', false)) {
  316.               $h['permission'] = true;
  317.             } else {
  318.               $h['permission'] = false;
  319.             }
  320.             array_push($allHoards, $h);
  321.           }
  322.         }
  323.         if ($format == 'csv') {
  324.             //join up all of the relevant tables
  325.             $doctrineQueryBuilder ->addSelect(
  326.                             'partial type.{id, hoardType}',
  327.                             'coinCount',
  328.                             'partial countries.{id, country}',
  329.                             'partial province.{id, province}',
  330.                             'partial coinLevelData.{id, coinLevelData}',
  331.                             'partial ancientPlace.{id, ancientPlace}',
  332.                             'partial location_detail.{id, findSpotLocationDetail}',
  333.                             'partial reign1.{id, reign}',
  334.                             'partial reign2.{id, reign}',
  335.                             'partial disc_method.{id, discoveryMethod}',
  336.                             'partial disc_landuse.{id, discoveryLandUse}',
  337.                             'partial recovery_method.{id, archaeologyRecoveryMethod}',
  338.                             'partial site_context.{id, archaeologySiteContext}',
  339.                             'partial site_context_details.{id, archaeologySiteContextDetail}',
  340.                             'partial arch_period.{id, archaeologyPeriod}',
  341.                             'partial arch_end_period.{id, archaeologyPeriod}',
  342.                             'partial context_natures.{id, title}',
  343.                             'partial findspot_rating.{id, rating}',
  344.                             'partial contextual_rating.{id, rating}',
  345.                             'partial rating.{id, rating}',
  346.                             'partial containers.{id, container}',
  347.                             'partial objects.{id, object}',
  348.                             'partial hoardReferences.{id, reference_str}',
  349.                             'partial references.{id, abbreviation, authors, year, title}',
  350.                             'partial refType.{id}',
  351.                         )
  352.                     ->leftJoin('h.hoardType', 'type')
  353.                 ->leftJoin('h.coinCount', 'coinCount')
  354.                 ->leftJoin('h.countries', 'countries')
  355.                 ->leftJoin('h.province', 'province')
  356.                 ->leftJoin('h.coinLevelData', 'coinLevelData')
  357.                 ->leftJoin('h.ancientPlace', 'ancientPlace')
  358.                 ->leftJoin('h.findSpotLocationDetail', 'location_detail')
  359.                 ->leftJoin('h.closingReign1', 'reign1')
  360.                 ->leftJoin('h.closingReign2', 'reign2')
  361.                 ->leftJoin('h.discoveryMethod', 'disc_method')
  362.                 ->leftJoin('h.discoveryLandUse', 'disc_landuse')
  363.                 ->leftJoin('h.archaeologyRecoveryMethod', 'recovery_method')
  364.                 ->leftJoin('h.archaeologySiteContext', 'site_context')
  365.                 ->leftJoin('h.archaeologySiteContextDetails', 'site_context_details')
  366.                 ->leftJoin('h.archaeologyPeriod', 'arch_period')
  367.                 ->leftJoin('h.archaeologyContextNatures', 'context_natures')
  368.                 ->leftJoin('h.archaeologyEndPeriod', 'arch_end_period')
  369.                 ->leftJoin('h.findSpotRating', 'findspot_rating')
  370.                 ->leftJoin('h.contextualRating', 'contextual_rating')
  371.                 ->leftJoin('h.rating', 'rating')
  372.                 ->leftJoin('h.containers', 'containers')
  373.                 ->leftJoin('h.objects', 'objects')
  374.                 ->leftJoin('h.hoardReferences', 'hoardReferences')
  375.                 ->leftJoin('hoardReferences.reference', 'references')
  376.                 ->leftJoin('references.referenceType', 'refType')
  377.             ;
  378.             $data = $doctrineQueryBuilder->getQuery()->getArrayResult();
  379.             //loop over hoards and flag if they should be hidden
  380.             foreach($data as &$hoard) {
  381.                 if($isAuthenticated) {
  382.                     $hoard['can_see_location'] = true;
  383.                 } else {
  384.                     $hoard['can_see_location'] = !$hoard['hideLocation'];
  385.                 }
  386.                 
  387.                 $hoardOnlineDatabases = array();
  388.                 $hoard['hoardReferences'] = array_filter($hoard['hoardReferences'], function($reference) use (&$hoardOnlineDatabases) {
  389.                     if ($reference && $reference['reference'] &&
  390.                         $reference['reference']['referenceType'] &&
  391.                         $reference['reference']['referenceType']['id'] &&
  392.                         $reference['reference']['referenceType']['id'] != '11') { // reference's type is a "Permalink in other online database"
  393.                             return true;
  394.                     } else {
  395.                         $hoardOnlineDatabases[] = $reference;
  396.                         return false;
  397.                     }
  398.                 });
  399.                 $hoard['hoardOnlineDatabases'] = $hoardOnlineDatabases;
  400.             }
  401.             return $this->outputCSV($data, 'hoard_export');
  402.         }
  403.         if ($format == 'csv-coins') {
  404.             $doctrineQueryBuilder ->addSelect('
  405.                 partial cp.{id, period}, 
  406.                 partial cd.{id, denomination}, 
  407.                 partial cmat.{id, material}, 
  408.                 partial cmint.{id, mint}, 
  409.                 partial cr.{id, reign}, 
  410.                 partial cpers.{id, person, title}, 
  411.                 partial ccond.{id, condition}, 
  412.                 partial ca.{id, aspect},
  413.                 partial container.{id, container},
  414.                 partial coinAdditionalFields.{id, comment},
  415.                 partial additionalFields.{id, additionalField, description},
  416.                 partial coinRefs.{id, reference_str, comment},
  417.                 partial refs.{id, abbreviation, authors, year, title, sortValue, hasOwnColumn}
  418.                 '
  419.             );
  420.             
  421.             $doctrineQueryBuilder
  422.                 ->leftJoin('c.period', 'cp')
  423.                 ->leftJoin('c.denominations', 'cd')
  424.                 ->leftJoin('cd.material', 'cmat')
  425.                 ->leftJoin('c.mints', 'cmint')
  426.                 ->leftJoin('c.reigns', 'cr')
  427.                 ->leftJoin('c.persons', 'cpers')
  428.                 ->leftJoin('c.condition', 'ccond')
  429.                 ->leftJoin('c.aspects', 'ca')
  430.                 ->leftJoin('c.container', 'container')
  431.                 ->leftJoin('c.coinAdditionalFields', 'coinAdditionalFields')
  432.                 ->leftJoin('coinAdditionalFields.additionalField', 'additionalFields')
  433.                 ->leftJoin('c.coinReferences', 'coinRefs')
  434.                 ->leftJoin('coinRefs.reference', 'refs')
  435.             ;
  436.             $data = $doctrineQueryBuilder->getQuery()->getArrayResult();
  437.             
  438.             $refData = $this->processHoardDataForCSV($data);
  440.             return $this->outputCSV($data, 'coins_export', true, $refData);
  441.         }
  443.         $coinTotalQuantity = null;
  444.         if ($search_type == 'search-coins') {
  445.             $doctrineQueryBuilderCoinQuantity = clone $doctrineQueryBuilder;
  446.             $coinTotalQuantityQuery = $doctrineQueryBuilderCoinQuantity
  447.                 ->select("c.id, c.quantity as q")
  448.                 ->getQuery()
  449.                 ->getArrayResult();
  451.             $uniqQuantities = array_unique($coinTotalQuantityQuery, SORT_REGULAR);
  452.             $coinTotalQuantity = array_sum(array_column($uniqQuantities, 'q'));
  453.         }
  455.         $pagination = $paginator->paginate(
  456.             $doctrineQuery,
  457.             $request->query->getInt('page', 1)/*page number*/,
  458.             $limit/*limit per page*/
  459.         );
  461.         $sortFields = array(
  462.             array('label' => 'Hoard number', 'key' => 'h.id'),
  463.             array('label' => 'Find Spot Name', 'key' => 'h.findSpotName'),
  464.             array('label' => 'Discovery Year', 'key' => 'h.discoveryYear1'),
  465.             array('label' => 'Terminal Year', 'key' => 'h.terminalYear1'),
  466.             array('label' => 'Opening Year', 'key' => 'h.openingYear1'),
  467.             array('label' => 'Date of Context', 'key' => 'h.archaeologyContextDate1'),
  468.             array('label' => 'Date Added', 'key' => 'h.creationDate'),
  469.             array('label' => 'Date Modified', 'key' => 'h.modifiedDate')
  470.         );
  472.         $searchForm = $this->createSearchForm();
  473.         $searchForm->handleRequest($request);
  475.         $hoardDiscoveryYearRangeStart = $initialSliderValues['hoardDiscoveryYearRangeStart'];
  476.         if (isset($hoardValues['hoardDiscoveryYearRangeStart'])) {
  477.             $hoardDiscoveryYearRangeStart = $hoardValues['hoardDiscoveryYearRangeStart'];
  478.         }
  479.         $hoardDiscoveryYearRangeEnd = $initialSliderValues['hoardDiscoveryYearRangeEnd'];
  480.         if (isset($hoardValues['hoardDiscoveryYearRangeEnd'])) {
  481.             $hoardDiscoveryYearRangeEnd = $hoardValues['hoardDiscoveryYearRangeEnd'] ;
  482.         }
  483.         $hoardTerminalYearRangeStart = $initialSliderValues['hoardTerminalYearRangeStart'];
  484.         if (isset($hoardValues['hoardTerminalYearRangeStart'])) {
  485.             $hoardTerminalYearRangeStart = $hoardValues['hoardTerminalYearRangeStart'] ;
  486.         }
  487.         $hoardTerminalYearRangeEnd = $initialSliderValues['hoardTerminalYearRangeEnd'];
  488.         if (isset($hoardValues['hoardTerminalYearRangeEnd'])) {
  489.             $hoardTerminalYearRangeEnd = $hoardValues['hoardTerminalYearRangeEnd'] ;
  490.         }
  491.         $hoardOpeningYearRangeStart = $initialSliderValues['hoardOpeningYearRangeStart'];
  492.         if (isset($hoardValues['hoardOpeningYearRangeStart'])) {
  493.             $hoardOpeningYearRangeStart = $hoardValues['hoardOpeningYearRangeStart'] ;
  494.         }
  495.         $hoardOpeningYearRangeEnd = $initialSliderValues['hoardOpeningYearRangeEnd'];
  496.         if (isset($hoardValues['hoardOpeningYearRangeEnd'])) {
  497.             $hoardOpeningYearRangeEnd = $hoardValues['hoardOpeningYearRangeEnd'] ;
  498.         }
  499.         $hoardArchaeologyContextDateRangeStart = $initialSliderValues['hoardArchaeologyContextDateRangeStart'];
  500.         if (isset($hoardValues['hoardArchaeologyContextDateRangeStart'])) {
  501.             $hoardArchaeologyContextDateRangeStart = $hoardValues['hoardArchaeologyContextDateRangeStart'];
  502.         }
  503.         $hoardArchaeologyContextDateRangeEnd = $initialSliderValues['hoardArchaeologyContextDateRangeEnd'];
  504.         if (isset($hoardValues['hoardArchaeologyContextDateRangeEnd'])) {
  505.             $hoardArchaeologyContextDateRangeEnd = $hoardValues['hoardArchaeologyContextDateRangeEnd'] ;
  506.         }
  507.         $coinYearRangeStart = $initialSliderValues['coinYearRangeStart'];
  508.         if (isset($coinValues['coinYearRangeStart'])) {
  509.             $coinYearRangeStart = $coinValues['coinYearRangeStart'];
  510.         }
  511.         $coinYearRangeEnd = $initialSliderValues['coinYearRangeEnd'];
  512.         if (isset($coinValues['coinYearRangeEnd'])) {
  513.             $coinYearRangeEnd = $coinValues['coinYearRangeEnd'];
  514.         }
  515.         $hoardCoinCountMin = $initialSliderValues['hoardCoinCountMin'];
  516.         if (isset($hoardValues['hoardCoinCountMin'])) {
  517.             $hoardCoinCountMin = $hoardValues['hoardCoinCountMin'] ;
  518.         }
  519.         $hoardCoinCountMax = $initialSliderValues['hoardCoinCountMax'];
  520.         if (isset($hoardValues['hoardCoinCountMax'])) {
  521.             $hoardCoinCountMax = $hoardValues['hoardCoinCountMax'] ;
  522.         }
  523.         foreach ($pagination as $hoard) {
  524.             if ($this->checkAccess($hoard, 'edit', false)) {
  525.                 $hoard->isEditableByUser = true;
  526.             }
  527.             else {
  528.                 $hoard->isEditableByUser = false;
  529.             }
  530.         }
  532.         $queryOptions = $this->queryOptions();
  533.         
  534.         // use the coins or hoards template depending on the search type
  535.         if ($search_type == 'search-coins') {
  536.             $search_template = '@OxHoardBundle/search/coinslist.html.twig';
  537.         } 
  538.         else {
  539.             $search_template = '@OxHoardBundle/search/list.html.twig';
  540.         }
  541.         // parameters to template
  542.         return $this->render($search_template,
  543.             array(
  544.                 'pagination' => $pagination,
  545.                 'is_authenticated' => $isAuthenticated,
  546.                 'is_authorised_to_create' => $this->userIsAuthorisedToCreate(),
  547.                 'is_authorised_to_import' => $this->userIsImporter(),
  548.                 'is_admin'  => $isAdmin,
  549.                 'sort_fields' => $sortFields,
  550.                 'current_sort' => $sort ?? NULL,
  551.                 'current_sort_direction' => $sortDirection ?? NULL,
  552.                 'search_form'   => $searchForm->createView(),
  553.                 'active_tab' => $activeTab,
  554.                 'hoardDiscoveryYearRangeStart' => $hoardDiscoveryYearRangeStart,
  555.                 'hoardDiscoveryYearRangeEnd' => $hoardDiscoveryYearRangeEnd,
  556.                 'hoardTerminalYearRangeStart' => $hoardTerminalYearRangeStart,
  557.                 'hoardTerminalYearRangeEnd' => $hoardTerminalYearRangeEnd,
  558.                 'hoardOpeningYearRangeStart' => $hoardOpeningYearRangeStart,
  559.                 'hoardOpeningYearRangeEnd' => $hoardOpeningYearRangeEnd,
  560.                 'coinYearRangeStart' => $coinYearRangeStart,
  561.                 'coinYearRangeEnd' => $coinYearRangeEnd,
  562.                 'hoardArchaeologyContextDateRangeStart' => $hoardArchaeologyContextDateRangeStart,
  563.                 'hoardArchaeologyContextDateRangeEnd' => $hoardArchaeologyContextDateRangeEnd,
  564.                 'hoardCoinCountMin' => $hoardCoinCountMin,
  565.                 'hoardCoinCountMax' => $hoardCoinCountMax,
  566.                 'initialHoardDiscoveryYearRangeStart' => $initialSliderValues['hoardDiscoveryYearRangeStart'],
  567.                 'initialHoardDiscoveryYearRangeEnd' => $initialSliderValues['hoardDiscoveryYearRangeEnd'],
  568.                 'initialHoardTerminalYearRangeStart' => $initialSliderValues['hoardTerminalYearRangeStart'],
  569.                 'initialHoardTerminalYearRangeEnd' => $initialSliderValues['hoardTerminalYearRangeEnd'],
  570.                 'initialHoardOpeningYearRangeStart' => $initialSliderValues['hoardOpeningYearRangeStart'],
  571.                 'initialHoardOpeningYearRangeEnd' => $initialSliderValues['hoardOpeningYearRangeEnd'],
  572.                 'initialHoardArchaeologyContextDateRangeStart' => $initialSliderValues['hoardArchaeologyContextDateRangeStart'],
  573.                 'initialHoardArchaeologyContextDateRangeEnd' => $initialSliderValues['hoardArchaeologyContextDateRangeEnd'],
  574.                 'initialHoardCoinCountMin' => $initialSliderValues['hoardCoinCountMin'],
  575.                 'initialHoardCoinCountMax' => $initialSliderValues['hoardCoinCountMax'],
  576.                 'initialCoinYearRangeStart' => $initialSliderValues['coinYearRangeStart'],
  577.                 'initialCoinYearRangeEnd' => $initialSliderValues['coinYearRangeEnd'],
  578.                 'initialCoinYearRangeStart' => $initialSliderValues['coinYearRangeStart'],
  579.                 'initialCoinYearRangeEnd' => $initialSliderValues['coinYearRangeEnd'],
  580.                 'queryOptions' => $queryOptions,
  581.                 'allHoardResults' => $allHoards,
  582.                 'hoardsOverEditLimit' => $hoardsOverEditLimit,
  583.                 'hoardsOverChartLimit' => $hoardsOverChartLimit,
  584.                 // 'allHoardsChart' => $allHoardChartResults,
  585.                 'allHoardsChart' => $allHoardResults,
  586.                 'coinTotalQuantity' => $coinTotalQuantity,
  587.                 'queryString' => $request->getQueryString(),
  588.                 'search_type' => $search_type
  589.             ));
  590.     }
  592.     /**
  593.      * creates csv and appropriate page response for csv of supplied query
  594.      *
  595.      */
  596.     private function outputCSV(Array $data, $prefix=null, $exportCoins=false, $refData=null) {
  597.         
  598.         if($prefix == null) {
  599.             $prefix = "export";
  600.         }
  601.         $filename = $prefix."_".date("Y_m_d_His").".csv";
  604.         if($exportCoins) {
  605.             //build an array of column headings for the reference columns
  606.             if($refData) {
  607.                 $refHeadings = [];
  608.                 foreach($refData['own_column_references'] as $o_c_ref) {
  609.                     if(isset($o_c_ref['abbreviation'])) {
  610.                         $refHeadings[] = $o_c_ref['abbreviation'] . ' Reference';
  611.                     } else {
  612.                         $refHeadings[] = $o_c_ref['title'] . ' Reference';
  613.                     }
  614.                 }
  615.                 for($i=1; $i <= $refData['num_other_references']; $i++) {
  616.                     $refHeadings[] = 'Other Reference '.$i;
  617.                 }
  618.             }
  619.             $response = $this->render('@OxHoardBundle/coin/csvExport.html.twig', array(
  620.                                             'data' => $data,
  621.                                             // 'numRefs' => 10,
  622.                                             'ref_headings' => $refHeadings,
  623.                                             'num_ref_columns' => count($refHeadings)
  624.                                         ));
  625.         } else {
  626.             $response = $this->render('@OxHoardBundle/hoard/csvExport.html.twig', array(
  627.                 'is_authenticated' => !!$this->getUser(),
  628.                 'data' => $data
  629.             ));
  630.         }
  631.         
  632.         $response->headers->set('Content-Type', 'text/csv');
  633.         $response->setStatusCode(200);
  634.         $response->setCharset('UTF-8');
  635.         $response->headers->set('Content-Type', 'text/csv');
  636.         $response->headers->set('Content-Description', 'Hoards Export');
  637.         $response->headers->set('Content-Disposition', 'attachment; filename='.$filename);
  638.         $response->headers->set('Content-Transfer-Encoding', 'binary');
  639.         $response->headers->set('Pragma', 'no-cache');
  640.         $response->headers->set('Expires', '0');
  642.         return $response;
  643.      }
  645.     /**
  646.     * Creates a form to search Hoard entities.
  647.     *
  648.     * @return \Symfony\Component\Form\Form The form
  649.     */
  650.     private function createSearchForm()
  651.     {
  652.         $form = $this->createForm(SearchType::class, null, array(
  653.             'action' => $this->generateUrl('hoard'),
  654.             'method' => 'GET',
  655.         ));
  657.         if($this->getUser()) {
  658.             // $form->add('hoardHideLocation', ChoiceType::class, array(
  659.             //     'choices' => array('Yes' => true, 'No' => false),
  660.             //     'required' => false,
  661.             // ));
  662.             //add the validated by user option
  663.             $form->add('hoardValidatedByUser', ChoiceType::class, array(
  664.                 'choices' => array('Yes' => true, 'No' => false),
  665.                 'required' => false,
  666.             ));
  667.             $form->add('hoardCoinDataValidatedByUser', ChoiceType::class, array(
  668.                 'choices' => array('Yes' => true, 'No' => false),
  669.                 'required' => false,
  670.             ));
  671.             $form->add('showOnlyUsersHoards', CheckboxType::class, array(
  672.                 'required' => false
  673.             ));
  674.         }
  676.         if($this->userIsAdmin()) {
  677.             //add the validated by user option
  678.             $form->add('hoardValidatedByAdmin', ChoiceType::class, array(
  679.                 'choices' => array('Yes' => true, 'No' => false),
  680.                 'required' => false,
  681.             ));
  682.             $form->add('hoardCoinDataValidatedByAdmin', ChoiceType::class, array(
  683.                 'choices' => array('Yes' => true, 'No' => false),
  684.                 'required' => false,
  685.             ));
  687.         }
  689.         $form->add('rules', HiddenType::class, array());
  690.         $form->add('boundary', HiddenType::class, array());
  691.         
  692.         // these fields have to be added to the for to make this message dissapear:
  693.         // "This form should not contain extra fields.""
  694.         // $form->add('hoardDiscoveryYearRangeStart', 'text', array('label' => '', 'required' => false))
  695.         //     ->add('hoardDiscoveryYearRangeEnd', 'text')
  696.         //     ->add('hoardTerminalYearRangeStart', 'text')
  697.         //     ->add('hoardTerminalYearRangeEnd', 'text')
  698.         //     ->add('coinYearRangeStart', 'text')
  699.         //     ->add('coinYearRangeEnd', 'text')
  700.         //     ->add('hoardCoinCountMin', 'text')
  701.         //     ->add('hoardCoinCountMax', 'text');
  702.         $form->add('submit', SubmitType::class, array('label' => 'Search hoards'));
  703.         $form->add('coin_submit', SubmitType::class, array('label' => 'Search coins'));
  705.         return $form;
  706.     }
  707.     
  708.     //process the hoard data
  709.     // This sorts the coins and normalises the references to consistent columns 
  710.     private function processHoardDataForCSV(&$data) {
  711.         $seenOwnColumnReferences = [];
  712.         $seenOtherReferences = [];
  713.     
  714.         //sort the references by sort value
  715.         foreach($data as &$hoard) {
  716.             foreach($hoard['coins'] as &$coin) {
  717.                 //get the sort value
  718.                 $reigns = $coin['reigns'];
  719.                 if ($reigns && count($reigns)) {
  720.                     // just look at first reign
  721.                     $reign = $reigns[0];
  722.                     $lookups = isset($reign['ocreLookups']) ?
  723.                         $reign['ocreLookups'] :
  724.                         [];
  725.                     foreach($lookups as $lookup) {
  726.                         $coinPersons = [];
  727.                         foreach($coin['persons'] as $person) {
  728.                             $coinPersons[] = $person['id'];
  729.                         }
  730.                         if (in_array($lookup['person']['id'], $coinPersons)) {
  731.                             // check mint if necessary
  732.                             if (isset($lookup['mint'])) {
  733.                                 // see if it matches
  734.                                 $coinMints = [];
  735.                                 foreach ($coin['mints'] as $mint) {
  736.                                     $coinMints[] = $mint['id'];
  737.                                 }
  738.                                 if (in_array($lookup['mint']['id'], $coinMints)) {
  739.                                     // this is a match
  740.                                     $coin['sortValue'] = $lookup['sortValue'];
  741.                                 }
  742.                             } 
  743.                             else {
  744.                                 // this is a match
  745.                                 $coin['sortValue'] = $lookup['sortValue'];
  746.                             }
  747.                         }
  748.                     }
  749.                 }
  750.                 // echo '<pre>'; print_r($coin); echo '</pre>';
  751.                 
  752.                 //note which references are seen
  753.                 // keep separate arrays of references which should have their own column
  754.                 foreach($coin['coinReferences'] as $coinRef) {
  755.                     $ref = $coinRef['reference'];
  756.                     if($ref) {
  757.                         if($coinRef['reference']['hasOwnColumn']) {
  758.                             if(!in_array($ref, $seenOwnColumnReferences)) {
  759.                                 $seenOwnColumnReferences[] = $ref;
  760.                             }
  761.                         } else {
  762.                             if(!in_array($ref, $seenOtherReferences)) {
  763.                                 $seenOtherReferences[] = $ref;
  764.                             }
  765.                         }
  766.                     }
  767.                 }
  768.                 
  769.                 usort($coin['coinReferences'], array($this,"compareRefs"));
  770.             }
  771.             
  772.             
  773.             //now sort coins by their sort value
  774.             usort($seenOwnColumnReferences, array($this,"compareSeenRefs"));
  775.             usort($hoard['coins'], array($this,"compareCoins"));
  776.             
  777.             // var_dump($hoard);
  778.         }
  779.         //at this point we have a list of coin references seen in this dataset, and the coin references are sorted
  780.         
  781.         //rewrite the coin references with the references in the relevant columns
  782.         $maxOtherReferences = 0;
  783.         foreach($data as &$hoard) {
  784.             foreach($hoard['coins'] as &$coin) {
  785.                 //keyed array of those references which should have their own column
  786.                 $newOwnColumnCoinReferences = [];
  787.                 
  788.                 //plain array of all other references
  789.                 $newOtherCoinReferences = [];
  790.                 
  791.                 $coinReferences = $coin['coinReferences'];
  793.                 foreach($coinReferences as $coinRef) {
  794.                     if(isset($coinRef['reference']) && $coinRef['reference']['hasOwnColumn']) {
  795.                         $key = $coinRef['reference']['id'];
  796.                         $newOwnColumnCoinReferences[$key] = $coinRef;
  797.                     } else {
  798.                         $newOtherCoinReferences[] = $coinRef;
  799.                     }
  800.                 }
  801.                 
  802.                 
  803.                 
  804.                 //now build the full array of references
  805.                 $newCoinReferences = [];
  806.                 
  807.                 // var_dump($seenOwnColumnReferences);
  808.                 foreach($seenOwnColumnReferences as $ownColumnRef) {
  809.                     $val = '';
  810.                     // var_dump($seenOwnColumnReferences);
  811.                     if(isset($newOwnColumnCoinReferences[$ownColumnRef['id']])) {
  812.                         
  813.                         $val = $newOwnColumnCoinReferences[$ownColumnRef['id']];
  814.                     } else {
  815.                         $val = array();
  816.                     }
  817.                     $newCoinReferences[] = $val;
  818.                 }
  819.                 
  820.                 //keep track of how many columns we need
  821.                 if(count($newOtherCoinReferences) > $maxOtherReferences) {
  822.                     $maxOtherReferences = count($newOtherCoinReferences);
  823.                 }
  824.                 
  825.                 
  826.                 //add the non-own-column references
  827.                 $newCoinReferences = array_merge($newCoinReferences, $newOtherCoinReferences);
  828.                 $coin['coinReferences'] = $newCoinReferences;
  829.             }
  830.         }
  831.         
  832.         //return some data about the references seen
  833.         return array(
  834.             'own_column_references' => $seenOwnColumnReferences,
  835.             'num_other_references' => $maxOtherReferences
  836.         );
  837.     }
  839.     function compareCoins($a, $b) {
  840.         //compare period
  841.         if (isset($a['period'])) { $periodA = $a['period']['id']; } else { $periodA = 0; }
  842.         if (isset($b['period'])) { $periodB = $b['period']['id']; } else { $periodB = 0; }
  843.         if($periodA < $periodB) { return -1; }
  844.         else if($periodB < $periodA ) { return 1; }
  845.         else {
  846.             if(isset($a['sortValue'])) { $sortA = $a['sortValue']; } else { $sortA = 0; }
  847.             if(isset($b['sortValue'])) { $sortB = $b['sortValue']; } else { $sortB = 0; }
  848.             //periods are equal, compare by ocre table
  849.             if($sortA > $sortB) { return 1; }
  850.             else if($sortB > $sortA) { return -1; }
  851.             else {
  852.                 //same ocre prefix
  853.                 //todo compare ric reference value
  854.                 $refValueA = $this->getSortableRef($a);
  855.                 if($refValueA) {
  856.                     $refValueB = $this->getSortableRef($b);
  857.                     if($refValueB) {
  858.                         //compare numerical part
  859.                         $numA = intval($refValueA['num']);
  860.                         $numB = intval($refValueB['num']);
  861.                         if($numA > $numB) { return 1; }
  862.                         else if($numB > $numA) {return -1; }
  863.                         else {
  864.                             //compare alphabetic part
  865.                             $lowerA = strtolower($refValueA['char']);
  866.                             $lowerB = strtolower($refValueB['char']);
  867.                             if($lowerA < $lowerB) { return -1; }
  868.                             else if($lowerB < $lowerA) { return 1; }
  869.                             else return 0;
  870.                         }
  871.                     }
  872.                 }
  873.                 
  874.                 return 0;
  875.             }
  876.         }
  877.     }
  879.     
  880.     //given an array of coinData, get the most relevant reference as an array of numerical part and alphabetic part
  881.     function getSortableRef($coinArray) {
  882.         // var_dump($coinArray);
  883.         foreach($coinArray['coinReferences'] as $coinRef) {
  884.             if(isset($coinRef['reference'])) {
  885.                 $refs = ['RIC', 'RIC (2nd ed.)', 'RRC', 'RPC'];
  886.                 if(in_array($coinRef['reference']['abbreviation'], $refs)) {
  887.                     //this is a reference we care about sorting on
  888.                     //extract the numerical and alphabetic components
  889.                     $matches = null;
  890.                     if (preg_match('/(\d+)\/(\d+)(\w*)/', $coinRef['reference_str'], $matches)) {
  891.                         //RRC reference format e.g. 123/1b
  892.                         //derive a number that can be used to compare
  893.                         $num = $matches[1] * 1000 + $matches[2];
  894.                         $refData = array(
  895.                             'num' => $num,
  896.                             'char' => $matches[3]
  897.                         );
  898.                         return $refData;
  899.                     } else if (preg_match('/(\d+)(\w*)/', $coinRef['reference_str'], $matches)) {
  900.                         //RIC reference format e.g. 123a (or somethings 123a/b or )
  901.                         $refData = array(
  902.                             'num' => $matches[1],
  903.                             'char' => $matches[2]
  904.                         );
  905.                         return $refData;
  906.                     }
  907.                 }
  908.             }
  909.         }
  910.         return null;
  911.     }
  914.     //order coinreference array data by sortvalue of the reference (decreasing)
  915.     function compareSeenRefs($a, $b) {
  916.         if($a['sortValue'] > $b['sortValue']) {
  917.             return -1;
  918.         } else if ($a['sortValue'] == $b['sortValue']) {
  919.             return 0;
  920.         } else {
  921.             return 1;
  922.         }
  923.     }
  925.     //order coinreference array data by sortvalue of the reference (decreasing)
  926.     function compareRefs($a, $b) {
  927.         if($a['reference']['sortValue'] > $b['reference']['sortValue']) {
  928.             return -1;
  929.         } else if ($a['reference']['sortValue'] == $b['reference']['sortValue']) {
  930.             return 0;
  931.         } else {
  932.             return 1;
  933.         }
  934.     }
  936.     private function getMinDiscoveryDate() {
  937.         return 900;
  938.         // $em = $this->getDoctrine()->getManager();
  939.         // 
  940.         // // Generate the coin summaries
  941.         // $queryBuilder = $em->createQueryBuilder();
  942.         // $queryBuilder->select('MIN(h.discoveryYear1) as min1, MIN(h.discoveryYear2) as min2')
  943.         //     ->from('OxHoardBundle:Hoard', 'h');
  944.         // 
  945.         // $query = $queryBuilder->getQuery();
  946.         // 
  947.         // $queryResult = $query->getArrayResult();
  948.         // 
  949.         // if(count($queryResult)) {
  950.         //     return min($queryResult[0]);
  951.         // }
  952.         // else {
  953.         //     return 1750;
  954.         // }
  955.     }
  957.     private function getMaxDiscoveryDate() {
  958.         return date("Y");
  959.         // $em = $this->getDoctrine()->getManager();
  960.         // 
  961.         // // Generate the coin summaries
  962.         // $queryBuilder = $em->createQueryBuilder();
  963.         // $queryBuilder->select('MAX(h.discoveryYear1) as max1, MAX(h.discoveryYear2) as max2')
  964.         //     ->from('OxHoardBundle:Hoard', 'h');
  965.         // 
  966.         // $query = $queryBuilder->getQuery();
  967.         // 
  968.         // $queryResult = $query->getArrayResult();
  969.         // 
  970.         // if(count($queryResult)) {
  971.         //     return max($queryResult[0]);
  972.         // }
  973.         // else {
  974.         //     return date("Y");
  975.         // }
  976.     }
  978.     private function getMinTerminalDate() {
  979.         //checking every hoard v slow
  980.         return -500;
  981.         
  982.         // $em = $this->getDoctrine()->getManager();
  983.         // 
  984.         // // Generate the coin summaries
  985.         // $queryBuilder = $em->createQueryBuilder();
  986.         // $queryBuilder->select('MIN(h.terminalYear1) as min1, MIN(h.terminalYear2) as min2')
  987.         //     ->from('OxHoardBundle:Hoard', 'h');
  988.         // 
  989.         // $query = $queryBuilder->getQuery();
  990.         // 
  991.         // $queryResult = $query->getArrayResult();
  992.         // 
  993.         // if(count($queryResult)) {
  994.         //     return min($queryResult[0]);
  995.         // }
  996.         // else {
  997.         //     return -100;
  998.         // }
  999.     }
  1001.     private function getMaxTerminalDate() {
  1002.         //checking every hoard is rather slow.
  1003.         return 1000;
  1004.         
  1005.         // $em = $this->getDoctrine()->getManager();
  1006.         // 
  1007.         // // Generate the coin summaries
  1008.         // $queryBuilder = $em->createQueryBuilder();
  1009.         // $queryBuilder->select('MAX(h.terminalYear1) as max1, MAX(h.terminalYear2) as max2')
  1010.         //     ->from('OxHoardBundle:Hoard', 'h');
  1011.         // 
  1012.         // $query = $queryBuilder->getQuery();
  1013.         // 
  1014.         // $queryResult = $query->getArrayResult();
  1015.         // 
  1016.         // if(count($queryResult)) {
  1017.         //     return max($queryResult[0]);
  1018.         // }
  1019.         // else {
  1020.         //     return 550;
  1021.         // }
  1022.     }
  1023.     
  1024.     private function getMinOpeningDate() {
  1025.         return -500;
  1026.     
  1027.     }
  1028.     private function getMaxOpeningDate() {
  1029.         return 1000;
  1030.     }
  1031.     private function getMinArchaeologyContextDate() {
  1032.         return -500;
  1033.     
  1034.     }
  1035.     private function getMaxArchaeologyContextDate() {
  1036.         return 1000;
  1037.     }
  1039.     private function getMinMaxCoinDate() {
  1040.         // $em = $this->getDoctrine()->getManager();
  1041.         // $qb = $em->createQueryBuilder();
  1042.         // $qb->select('MAX(c.endingDate) as max1, MIN(c.startingDate) as min1')
  1043.         //     ->from('OxHoardBundle:Coin', 'c');
  1044.         //     
  1045.         // $query = $qb->getQuery();
  1046.         // $result = $query->getArrayResult();
  1047.         // return $result[0];
  1048.         return array('min1'=>-500, 'max1'=>1000);
  1049.         
  1050.         
  1051.     }
  1053.     /**
  1054.      * checks permission of user's current request
  1055.      *
  1056.      * @param mixed $entity The entity being validated
  1057.      *
  1058.      * @param string $attribute - 'view' or 'edit' or 'delete'
  1059.      *
  1060.      * @param boolean $throwException - whether to throw an exception if false - defaults to true
  1061.      *
  1062.      * @return boolean
  1063.      *
  1064.      * @throws \Symfony\Component\Security\Core\Exception\AccessDeniedException
  1065.      */
  1066.     private function checkAccess($entity, $attribute, $throwException = true) {
  1067.         // call security voter(s)
  1068.         if (false === $this->security->isGranted($attribute, $entity)) {
  1069.             if ($throwException) {
  1070.                 throw new AccessDeniedException('Unauthorised access!');
  1071.             }
  1072.             return false;
  1073.         }
  1074.         return true;
  1075.     }
  1077.     private function userIsAdmin() {
  1078.         if($this->getUser() && ($this->getUser()->hasRole('ROLE_ADMIN') || $this->getUser()->hasRole('ROLE_SUPER_ADMIN')))
  1079.         {
  1080.             return true;
  1081.         }
  1082.         return false;
  1083.     }
  1085.     private function userIsImporter() {
  1086.         if($this->getUser() && ($this->getUser()->hasRole('ROLE_IMPORTER')))
  1087.         {
  1088.             return true;
  1089.         }
  1090.         return $this->userIsAdmin();
  1091.     }
  1093.     private function userIsAuthorisedToCreate() {
  1094.         // user must either be admin or be authorised to edit hoards for at least one country
  1095.         if($this->userIsAdmin() || ($this->getUser() && count($this->getUser()->getAccessibleCountries()) > 0)) {
  1096.             return true;
  1097.         }
  1098.         return false;
  1099.     }
  1101.       /**
  1102.        * Retrieves coordinates of all the hoards from database
  1103.        *
  1104.        * @Route("/ajax_all_hoard_locations", name="ajax_all_hoard_locations", methods={"GET"})
  1105.        */
  1106.       public function ajaxAllHoardLocations()
  1107.       {
  1108.             $em = $this->getDoctrine()->getManager();
  1110.             $queryBuilder = $em -> createQueryBuilder()
  1111.                                 ->select("h.id, h.findSpotLatitude, h.findSpotLongitude, h.terminalYear1, h.terminalYear2, h.findSpotName, h.validatedByUser")
  1112.                                 ->from("OxHoardBundle:Hoard", "h")
  1113.                                 ->where('h.findSpotLatitude IS NOT NULL')
  1114.                                 ->andWhere('h.findSpotLongitude IS NOT NULL');
  1116.             //filter list for public to only those 'published' (validated by user)
  1117.             if(!$this->getUser())
  1118.             {
  1119.                   //public can see all hoards which are not hidden from the public, and which don't have location hidden
  1120.                   $queryBuilder->andWhere('(h.hideFrom = 3 OR h.hideWhat < 3  OR h.hideWhat IS NULL)')
  1121.                                 ->andWhere('(h.hideLocation IS NULL OR h.hideLocation != 1)')
  1122.                                 ;
  1123.             }
  1125.             $query = $queryBuilder->getQuery();
  1126.             
  1127.             $allHoards = $query -> getArrayResult();
  1129.             return new JsonResponse(
  1130.                   $allHoards
  1131.             );
  1132.       }
  1134.       /***
  1135.          * Given a rules array, biuld a doctrine query
  1136.          *
  1137.          */
  1138.         private function buildQuery($rules, $search_type, $format) {
  1139.             $em = $this->getDoctrine()->getManager();
  1140.             $qb = $em->createQueryBuilder();
  1141.             
  1142.             // if format=csv select hoards. coins are going to be listed with a hoard.coins loop in the twig template
  1143.             if ($format == 'csv-coins' || $format == 'csv') {
  1144.                 $qb->add('select', new Select(array('h', 'c')))
  1145.                     ->add('from', new From('OxHoardBundle:Hoard', 'h'))
  1146.                     ->leftJoin('h.coins', 'c');
  1147.             } else {
  1148.                 if ($search_type == 'search-coins') {
  1149.                     $qb->add('select', new Select(array('c', 'h')))
  1150.                         ->add('from', new From('OxHoardBundle:Coin', 'c'))
  1151.                         ->leftJoin('c.hoard', 'h');
  1152.                 } else {
  1153.                     $qb->add('select', new Select(array('h', 'c')))
  1154.                         ->add('from', new From('OxHoardBundle:Hoard', 'h'))
  1155.                         ->leftJoin('h.coins', 'c');
  1156.                 }
  1157.             }
  1158.             if (($search_type == 'search-coins' && $format != 'csv') || $format == 'csv-coins') {
  1159.                 $qb->addSelect('hcountriessort')
  1160.                     ->leftJoin('h.countries', 'hcountriessort');
  1161.             }
  1162.             
  1163.         //set join flags to false 
  1164.         $this->joinHoardImages = $this->joinHoardCountries = $this->joinHoardArchaeologySiteContextDetail = $this->joinCoinImages = $this->joinCoinPersons =
  1165.         $this->joinCoinDenominations = $this->joinCoinMints = $this->joinCoinAspects = $this->joinCoinCoinReferences =
  1166.         $this->joinCoinReferences = $this->joinCoinReigns = $this->joinContainers = $this->joinContainerImages =
  1167.         $this->joinContainerForms = $this->joinObjects = $this->joinObjectImages = 
  1168.         $this->joinHoardAncientPlaces = $this->joinFindSpotLocationDetails = $this->joinHoardReferences =
  1169.         $this->joinDiscoveryMethods = $this->joinDiscoveryLandUses = $this->joinNameSimple = $this->joinArchaeologyContextNatures = false;
  1170.         
  1171.             //build expression recursively
  1172.             $expr1 = $this->getExpressionForRule($qb, $rules);
  1173.             $qb->add('where', $expr1);
  1174.         
  1175.         //join tables if necessary
  1176.         if ($this->joinHoardCountries) $qb->leftJoin('h.countries', 'hoardCountries');
  1177.         if ($this->joinHoardArchaeologySiteContextDetail) $qb->leftJoin('h.archaeologySiteContextDetails', 'hoardArchaeologySiteContextDetails');
  1178.         if ($this->joinHoardImages) $qb->leftJoin('h.hoardImages', 'hoardImages');
  1179.         if ($this->joinCoinImages) $qb->leftJoin('c.coinImages', 'coinImages');
  1180.         if ($this->joinCoinPersons) $qb->leftJoin('c.persons', 'coinPersons');
  1181.         if ($this->joinCoinDenominations) $qb->leftJoin('c.denominations', 'coinDenominations');
  1182.         if ($this->joinCoinMints) $qb->leftJoin('c.mints', 'coinMints');
  1183.         if ($this->joinCoinAspects) $qb->leftJoin('c.aspects', 'coinAspects');
  1184.         if ($this->joinCoinCoinReferences) $qb->leftJoin('c.coinReferences', 'coinCoinReferences');
  1185.         if ($this->joinCoinReferences) $qb->leftJoin('coinCoinReferences.reference', 'coinReferences');
  1186.         if ($this->joinCoinReigns) $qb->leftJoin('c.reigns', 'coinReigns');
  1187.         if ($this->joinContainers) $qb->leftJoin('h.containers', 'containers');
  1188.         if ($this->joinContainerImages) $qb->leftJoin('containers.containerImages', 'containerImages');
  1189.         if ($this->joinContainerForms) $qb->leftJoin('containers.containerForm', 'containerForms');
  1190.         if ($this->joinObjects) $qb->leftJoin('h.objects', 'objects');
  1191.         if ($this->joinObjectImages) $qb->leftJoin('objects.objectImages', 'objectImages');
  1192.         if ($this->joinHoardReferences) $qb->leftJoin('h.hoardReferences', 'hoardReferences');
  1193.         if ($this->joinHoardAncientPlaces) $qb->leftJoin('h.ancientPlace', 'hoardAncientPlaces');
  1194.         if ($this->joinArchaeologyContextNatures) $qb->leftJoin('h.archaeologyContextNatures', 'hoardArchaeologyContextNatures');
  1195.         if ($this->joinFindSpotLocationDetails) $qb->leftJoin('h.findSpotLocationDetail', 'hoardFindSpotLocationDetails');
  1196.         if ($this->joinDiscoveryMethods) $qb->leftJoin('h.discoveryMethod', 'hoardDiscoveryMethods');
  1197.         if ($this->joinDiscoveryLandUses) $qb->leftJoin('h.discoveryLandUse', 'hoardDiscoveryLandUses');
  1198.         if ($this->joinNameSimple) $qb->leftJoin('h.nameSimple', 'nameSimple');
  1199.             return $qb;
  1200.         }
  1202.       private function getExpressionForRule($qb, $rule) {
  1203.             // Recursive function to build up an expression for the rule
  1204.             //  if this is a compound rule, make a boolean expression out of the sub-expressions
  1205.             //  otherwise make an atomic (simple) expression
  1206.             if(isset($rule['condition'])) {
  1207.                 //compound rule
  1208.                 $rules = $rule['rules'];
  1209.                 switch($rule['condition']) {
  1210.                     case 'AND':
  1211.                         $expr = $qb->expr()->andX();
  1212.                         break;
  1213.                     case 'OR':
  1214.                         $expr = $qb->expr()->orX();
  1215.                         break;
  1216.                 }
  1217.                 foreach($rules as $rule) {
  1218.                     $subexpr = $this->getExpressionForRule($qb, $rule);
  1219.                     $expr->add($subexpr);
  1220.                 }
  1221.             } else {
  1222.                 //atomic rule
  1223.                 $expr = $this->getExpressionForAtomicRule($qb, $rule);
  1224.             }
  1225.             return $expr;
  1226.         }
  1227.         
  1228.         private function getExpressionForAtomicRule($qb, $rule) {
  1229.         
  1230.         // get field
  1231.             $field = $rule['field'];
  1232.             // get value
  1233.             $value = $rule['value'];
  1234.             // get type
  1235.             $type = $rule['type'];
  1236.             $is_array = isset($rule['data']['isArray']) ?
  1237.                 $rule['data']['isArray'] === true :
  1238.                 false;
  1239.             // get range: if the field is a range (eg discoveryYear1-discoveryYear2)
  1240.             // then $range is true and $fiald is discoveryYear
  1241.             $range = false;
  1242.             $checkCollection = false;
  1244.         // set join flags to true if necessary
  1245.         if( $field == 'hoardCountries.id') {
  1246.           $this->joinHoardCountries = true;
  1247.         }
  1248.         if( $field == 'hoardArchaeologySiteContextDetails.id') {
  1249.             $this->joinHoardArchaeologySiteContextDetail = true;
  1250.         }
  1251.         if( $field == 'ancientPlace') {
  1252.           $this->joinAncientPlaces = true;
  1253.         }
  1254.         if( $field == 'hoardArchaeologyContextNatures.id') {
  1255.             $this->joinArchaeologyContextNatures = true;
  1256.         }
  1257.         if( $field == 'findSpotLocationDetail') {
  1258.             $this->joinFindSpotLocationDetails = true;
  1259.         }
  1260.         if( $field == 'discoveryMethod') {
  1261.             $this->joinDiscoveryMethods = true;
  1262.         }
  1263.         if( $field == 'discoveryLandUse') {
  1264.             $this->joinDiscoveryLandUses = true;
  1265.         }
  1266.         if( $field == 'hoardReferences.reference') {
  1267.             $this->joinHoardReferences = true;
  1268.         }
  1269.         if( $field == 'hoardImages') {
  1270.           $this->joinHoardImages = true;
  1271.         }
  1272.         if( $field == 'c.period') {
  1273.         }
  1274.         if( $field == 'coinPersons.id') {
  1275.           $this->joinCoinPersons = true;
  1276.         }
  1277.         if( $field == 'coinReigns.id') {
  1278.           $this->joinCoinReigns = true;
  1279.         }
  1280.         if( $field == 'coinDenominations.id') {
  1281.           $this->joinCoinDenominations = true;
  1282.         }
  1283.         if( $field == 'coinDenominations.material') {
  1284.           $this->joinCoinDenominations = true;
  1285.           
  1286.         }
  1287.         if( $field == 'coinMints.id') {
  1288.           $this->joinCoinMints = true;
  1289.         }
  1290.         if( $field == 'coinAspects.id') {
  1291.           $this->joinCoinAspects = true;
  1292.         }
  1293.         if( $field == 'coinReferences.id') {
  1294.           $this->joinCoinCoinReferences = true;
  1295.           $this->joinCoinReferences = true;
  1296.         }
  1297.         if( $field == 'coinCoinReferences.reference_str') {
  1298.           $this->joinCoinCoinReferences = true;
  1299.           $this->joinCoinReferences = true;
  1300.           $this->joinCoinReferences = true;
  1301.         }
  1302.         if( $field == 'coinImages') {
  1303.           $this->joinCoinImages = true;
  1304.         }
  1305.         if( $field == 'containers' || $field == 'containers.container' || $field == 'containers.comment') {
  1306.           $this->joinContainers = true;
  1307.         }
  1308.         if( $field == 'containerImages') {
  1309.           $this->joinContainers = true;
  1310.           $this->joinContainerImages = true;
  1311.         }
  1312.         if( $field == 'containers.material') {
  1313.           $this->joinContainers = true;
  1314.         }
  1315.         if( $field == 'containers.containerForm') {
  1316.           $this->joinContainers = true;
  1317.           $this->joinContainerForms = true;
  1318.         }
  1319.         if( $field == 'objects' || $field == 'objects.comment') {
  1320.           $this->joinObjects = true;
  1321.         }
  1322.         if( $field == 'objectImages') {
  1323.           $this->joinObjects = true;
  1324.           $this->joinObjectImages = true;
  1325.         }
  1326.         if( $field == 'objects.object') {
  1327.           $this->joinObjects = true;
  1328.         }
  1329.         if( $field == 'objects.material') {
  1330.           $this->joinObjects = true;
  1331.         }
  1332.         if( $field == 'h.findSpotName') {
  1333.           $this->joinNameSimple = true;
  1334.         }
  1335.         
  1336.             if (isset($rule['data'])) {
  1337.                 if (isset($rule['data']['range'])) {
  1338.                     $range = $rule['data']['range'];
  1339.                 }
  1340.                 if (isset($rule['data']['checkCollection'])) {
  1341.                     $checkCollection = $rule['data']['checkCollection'];
  1342.                 }
  1343.             }
  1344.             if(isset($rule['operator'])) {
  1345.                 $expr = NULL;
  1346.               if ($field == 'h.findSpotName' && (
  1347.                   $rule['operator'] == 'begins_with' || $rule['operator'] == 'ends_with' ||
  1348.                   $rule['operator'] == 'contains' || $rule['operator'] == 'equal' || 'not_equal'
  1349.               )) {
  1350.                   switch($rule['operator']) {
  1351.                       case 'contains':
  1352.                       $newValue = '%'.$value.'%';
  1353.                       $expr = "(h.findSpotName LIKE '".$newValue."' OR h.findSpotOtherNames LIKE '".$newValue."' OR nameSimple.name LIKE '".$newValue."')";
  1354.                       break;
  1355.                       case 'equal':
  1356.                       $newValue = $value;
  1357.                       $expr = "(h.findSpotName = '".$newValue."' OR h.findSpotOtherNames = '".$newValue."' OR nameSimple.name = '".$newValue."')";
  1358.                       break;
  1359.                       case 'not_equal':
  1360.                       $newValue = $value;
  1361.                       $expr = "(h.findSpotName <> '".$newValue."' OR h.findSpotOtherNames <> '".$newValue."' OR nameSimple.name <> '".$newValue."')";
  1362.                       break;
  1363.                   }
  1364.               } 
  1365.               else {
  1366.                 switch($rule['operator']) {
  1367.                     case 'begins_with':
  1368.                         $expr = $qb->expr()->like($field, $qb->expr()->literal($value.'%'));
  1369.                         break;
  1370.                     case 'ends_with':
  1371.                         $expr = $qb->expr()->like($field, $qb->expr()->literal('%'.$value));
  1372.                     case 'contains':
  1373.                         $expr = $qb->expr()->like($field, $qb->expr()->literal('%'.$value.'%'));
  1374.                         break;
  1375.                     case 'equal':
  1376.                         if ($range) {
  1377.                             if ($type == "integer") {
  1378.                                 $expr = '(' .$field. '1 <= ' .$value. ' AND ' .$field. '2 >= ' .$value. ') OR ' .$field. '1 = ' .$value .' OR ' .$field. '2 = ' .$value;
  1379.                                 break;
  1380.                             }
  1381.                         } else {
  1382.                             if ($type == "string") {
  1383.                                 if ($is_array) {
  1384.                                     $expr = $field. ' IN (' .$value. ')';
  1385.                                 } else {
  1386.                                     $expr = $qb->expr()->eq($field, $qb->expr()->literal($value));
  1387.                                 }
  1388.                                 break;
  1389.                             }
  1390.                             if ($type == "integer" || $type == "double") {
  1391.                                 if ($field=="c.period") {
  1392.                                     $expr = $field. ' = ' .$value;
  1393.                                 } else {
  1394.                                     $expr = $field. ' = ' .$value;
  1395.                                 }
  1396.                                 break;
  1397.                             }
  1398.                             if ($type == "boolean") {
  1399.                                 if ($checkCollection) {
  1400.                                     if ($value == "true") {
  1401.                                         $expr = $field. ' IS NOT NULL';
  1402.                                         break;
  1403.                                     } else {
  1404.                                         $expr = $field. ' IS NULL';
  1405.                                         break;
  1406.                                     }
  1407.                                 }
  1408.                                 if ($field == "h.modified") {
  1409.                                     $currentUser = $this->getUser()->getId();
  1410.                                     $expr = 'h.modified = ' .$currentUser. ' OR h.created = ' .$currentUser;
  1411.                                     break;
  1412.                                 } else {
  1413.                                     $bool_value = $value == false ? '0' : $value;
  1414.                                     $expr = $field. ' = ' .$bool_value;
  1415.                                     break;
  1416.                                 }
  1417.                             }
  1418.                         }
  1419.                         case 'not_equal':
  1420.                             if ($range) {
  1421.                                 if ($type == "integer") {
  1422.                                     $expr = $field. '1>' .$value.' OR ' .$field. '2<' .$value.' OR (' .$field. '1!=' .$value. 'AND ' .$field. '2 IS NULL) OR (' .$field. '2!=' .$value. ' AND ' .$field. '1 IS NULL)' ;
  1423.                                     break;
  1424.                                 }
  1425.                             } else {
  1426.                                 if ($type == "string") {
  1427.                                     $expr = $qb->expr()->neq($field, $qb->expr()->literal($value));
  1428.                                     break;
  1429.                                 }
  1430.                                 if ($type == "integer" || $type == "double") {
  1431.                                     $expr = $field. '!=' .$value;
  1432.                                     break;
  1433.                                 }
  1434.                             }
  1435.                     case 'greater':
  1436.                         if ($range) {
  1437.                             $expr = $field. '1>' .$value. ' OR ' .$field. '2>' .$value ;
  1438.                             break;
  1439.                         } else {
  1440.                             $expr = $field. '>' .$value;
  1441.                             break;
  1442.                         }
  1443.                     case 'less':
  1444.                         if ($range) {
  1445.                             $expr = $field. '2<' .$value. ' OR ' .$field. '1<' .$value ;
  1446.                             break;
  1447.                         } else {
  1448.                             $expr = $field. '<' .$value;
  1449.                             break;
  1450.                         }
  1451.                     case 'is_null':
  1452.                             if ($range) {
  1453.                                 if ($type == "integer") {
  1454.                                     $expr = $field. '1 IS NULL AND ' .$field. '2 IS NULL' ;
  1455.                                     break;
  1456.                                 }
  1457.                             } else {
  1458.                                 $expr = $field.' IS NULL';
  1459.                                 break;
  1460.                             }
  1461.                     case 'is_not_null':
  1462.                             if ($range) {
  1463.                                 if ($type == "integer") {
  1464.                                     $expr = $field. '1 IS NOT NULL OR ' .$field. '2 IS NOT NULL' ;
  1465.                                     break;
  1466.                                 }
  1467.                             } else {
  1468.                                 $expr = $field.' IS NOT NULL';
  1469.                                 break;
  1470.                             }
  1471.                     default:
  1472.                         //error
  1473.                 }
  1474.               }
  1475.               if (($field == "h.city" || $field == "h.address") && !$this->getUser()) {
  1476.                   $expr = $expr . ' AND h.hideCity = 0 OR h.hideCity IS NULL';
  1477.               }
  1478.               if ($field == "h.county" && !$this->getUser()) {
  1479.                   $expr = $expr . ' AND h.hideCounty = 0 OR h.hideCounty IS NULL';
  1480.               }
  1481.               return $expr;
  1482.             } 
  1483.             else {
  1484.                 // error
  1485.             }
  1486.         }
  1487.         
  1488.         private function queryOptions() {
  1489.                 $em = $this->getDoctrine()->getManager();
  1490.                 
  1491.                 //get all available options for the various dropdowns
  1492.                 $data = array();
  1493.                 $data['hoardTypes'] = $em->getRepository('OxHoardBundle:HoardType')->findAll();
  1494.                 $data['countries'] = $em->getRepository('OxHoardBundle:Country')->findAll();
  1495.                 $data['provinces'] = $em->getRepository('OxHoardBundle:Province')->findAll();
  1496.                 $data['ancientPlaces'] = $em->getRepository('OxHoardBundle:AncientPlace')->findAll();
  1497.                 $data['findSpotLocationDetails'] = $em->getRepository('OxHoardBundle:FindSpotLocationDetail')->findAll();
  1498.                 $data['discoveryLandUses'] = $em->getRepository('OxHoardBundle:DiscoveryLandUse')->findAll();
  1499.                 $data['discoveryMethods'] = $em->getRepository('OxHoardBundle:DiscoveryMethod')->findAll();
  1500.                 $data['reigns'] = $em->getRepository('OxHoardBundle:Reign')->findAll();
  1501.                 $data['discoveryMethods'] = $em->getRepository('OxHoardBundle:DiscoveryMethod')->findAll();
  1502.                 $data['discoveryLandUses'] = $em->getRepository('OxHoardBundle:DiscoveryLandUse')->findAll();
  1503.                 $data['archaeologyRecoveryMethods'] = $em->getRepository('OxHoardBundle:ArchaeologyRecoveryMethod')->findAll();
  1504.                 $data['archaeologySiteContexts'] = $em->getRepository('OxHoardBundle:ArchaeologySiteContext')->findAll();
  1505.                 $data['archaeologyRecoveryMethods'] = $em->getRepository('OxHoardBundle:ArchaeologyRecoveryMethod')->findAll();
  1506.                 $data['archaeologySiteContextDetails'] = $em->getRepository('OxHoardBundle:ArchaeologySiteContextDetail')->findAll();
  1507.                 $data['archaeologyPeriods'] = $em->getRepository('OxHoardBundle:ArchaeologyPeriod')->findAll();
  1508.                 $data['archaeologyContextNatures'] = $em->getRepository('OxHoardBundle:ArchaeologyContextNature')->findAll();
  1509.                 $data['coinLevelDatas'] = $em->getRepository('OxHoardBundle:CoinLevelData')->findAll();
  1510.                 $data['ratings'] = $em->getRepository('OxHoardBundle:Rating')->findAll();
  1511.                 $data['hideWhats'] = $em->getRepository('OxHoardBundle:HideWhat')->findAll();
  1512.                 $data['hideFroms'] = $em->getRepository('OxHoardBundle:HideFrom')->findAll();
  1514.                 $data['persons'] = $em->getRepository('OxHoardBundle:Person')->findAll();
  1515.                 $data['period'] = $em->getRepository('OxHoardBundle:Period')->findAll();
  1516.                 $data['reigns'] = $em->getRepository('OxHoardBundle:Reign')->findAll();
  1517.                 $data['mints'] = $em->getRepository('OxHoardBundle:Mint')->findAll();
  1518.                 $data['denominations'] = $em->getRepository('OxHoardBundle:Denomination')->findAll();
  1519.                 $data['materials'] = array(
  1520.                     array("ids" => "23", "name" => "Gold"),
  1521.                     array("ids" => "22,31", "name" => "Silver"),
  1522.                     array("ids" => "7,49,53", "name" => "Bronze"),
  1523.                 );
  1524.                 $data['condition'] = $em->getRepository('OxHoardBundle:Condition')->findAll();
  1525.                 $data['aspects'] = $em->getRepository('OxHoardBundle:Aspect')->findAll();
  1526.                 $data['references'] = $em->getRepository('OxHoardBundle:Reference')->findBy(array(), array('sortValue' => 'DESC', 'authors' => 'ASC', 'title' => 'ASC'));
  1527.                 // var_dump($data['references']);
  1528.                 $data['containerForms'] = $em->getRepository('OxHoardBundle:ContainerForm')->findAll();
  1529.                 return $data;
  1530.         }
  1532.     private function constructSearchQuery($request, &$doctrineQueryBuilder, &$activeTab, &$hoardValues, &$coinValues, &$queryParameters, $initialSliderValues)
  1533.     {
  1534.         // Construct a query based on all the form values.
  1535.         // We'll add JOINs and WHERE clauses to the doctrineQueryBuilder object.
  1536.         // We'll add any parameter values to the queryParameters array, and after this function join them to the existing query (they seemed to get lost if I added them directly to the doctrineQueryBuilder)
  1537.         // The hardValues and coinValues arrays will contain parameters appropriate to those objects and be populated by this function
  1540.         if (!empty($request->query->get('ox_hs'))) {
  1541.             foreach ($request->query->get('ox_hs') as $key => $value) {
  1542.                 if (is_array($value) or strlen($value)) {
  1543.                     if (strpos($key, 'hoard') === 0) {
  1544.                         $hoardValues[$key] = $value;
  1545.                     }
  1546.                     else if (strpos($key, 'coin') === 0) {
  1547.                         $coinValues[$key] = $value;
  1548.                     }
  1549.                 }
  1550.             }
  1551.         }
  1554.         if($this->userIsAdmin()) {
  1555.             #echo "<pre>hoard params: " . print_r($hoardValues,1) . "</pre>";
  1556.             #echo "<pre>coin params: " . print_r($coinValues,1) . "</pre>";
  1557.         }
  1559.         if (!empty($hoardValues)) {
  1560.             $isHoardRefJoined = false;
  1561.             foreach ($hoardValues as $key => $value) {
  1562.                 $field = lcfirst(substr($key, strlen('hoard')));
  1564.                 if ($field == "findSpotName") {
  1565.                     //   Search in both findSpotName and findSpotOtherNames fields
  1566.                     $altField = "findSpotOtherNames";
  1567.                     $doctrineQueryBuilder->join('h.nameSimple', 'nameSimple');
  1568.                     $doctrineQueryBuilder->andWhere('h.' .$field. ' LIKE :' .$key. ' OR ' . 'h.' .$altField. ' LIKE :' .$key. ' OR ' . 'nameSimple.name LIKE :' .$key);
  1569.                     $queryParameters[$key] = '%'.$value.'%';
  1570.                 } else if ($field == "type") {
  1571.                     //   Search in both findSpotName and findSpotOtherNames fields
  1572.                     $doctrineQueryBuilder->andWhere('h.' . 'hoardType' . ' = :' . $key);
  1573.                     $queryParameters[$key] = $value;
  1574.                 } else if ($field == "countries") {
  1575.                     $doctrineQueryBuilder->join('h.' . 'countries', 'country');
  1576.                     
  1577.                     $fieldQuery = "( ";
  1578.                     foreach ($value as $index => $val) {
  1579.                         if ($index > 0) {
  1580.                             $fieldQuery .= " OR ";
  1581.                         }
  1582.                         $fieldQuery .= 'country.id = :' . $key . "_" . $index;
  1583.                         $queryParameters[$key . "_" . $index] = $val;
  1584.                     }
  1585.                     $fieldQuery .= " )";
  1586.                     $doctrineQueryBuilder->andWhere($fieldQuery);
  1587.                 } else if (
  1588.                     $field == "comment" || $field == "credit" || $field == "region" ||
  1589.                     $field == "city" || $field == "county" || $field == "address" ||
  1590.                     $field == "owner" || $field == "findSpotComment" || $field == "finder" ||
  1591.                     $field == "archaeologyNaturalFeatures" || $field == "archaeologyAssociatedFeatures" ||
  1592.                     $field == "archaeologySiteComment" || $field == "internalNote"
  1593.                 ) {
  1594.                     $doctrineQueryBuilder->andWhere('h.' . $field . ' LIKE :' . $key);
  1595.                     if (($field == "city" || $field == "address") && !$this->getUser()) {
  1596.                         $doctrineQueryBuilder->andWhere('h.hideCity = 0 OR h.hideCity IS NULL');
  1597.                     }
  1598.                     if ($field == "county" && !$this->getUser()) {
  1599.                         $doctrineQueryBuilder->andWhere('h.hideCounty = 0 OR h.hideCounty IS NULL');
  1600.                     }
  1601.                     $queryParameters[$key] = '%'.$value.'%';
  1602.                 } else if ($field == "archaeologyContextNatures") {
  1603.                     $doctrineQueryBuilder->join('h.' . 'archaeologyContextNatures', 'archaeologyContextNatures');
  1604.                     $fieldQuery = "( ";
  1605.                     foreach ($value as $index => $val) {
  1606.                         if ($index > 0) {
  1607.                             $fieldQuery .= " OR ";
  1608.                         }
  1609.                         $fieldQuery .= 'archaeologyContextNatures.id = :' . $key . "_" . $index;
  1610.                         $queryParameters[$key . "_" . $index] = $val;
  1611.                     }
  1612.                     $fieldQuery .= " )";
  1613.                     $doctrineQueryBuilder->andWhere($fieldQuery);
  1614.                 } else if ($field == "archaeologySiteContextDetails") {
  1615.                     $doctrineQueryBuilder->join('h.' . 'archaeologySiteContextDetails', 'archaeologySiteContextDetail');
  1616.                     
  1617.                     $fieldQuery = "( ";
  1618.                     foreach ($value as $index => $val) {
  1619.                         if ($index > 0) {
  1620.                             $fieldQuery .= " OR ";
  1621.                         }
  1622.                         $fieldQuery .= 'archaeologySiteContextDetail.id = :' . $key . "_" . $index;
  1623.                         $queryParameters[$key . "_" . $index] = $val;
  1624.                     }
  1625.                     $fieldQuery .= " )";
  1626.                     $doctrineQueryBuilder->andWhere($fieldQuery);
  1627.                 } else if ($field == "discoveryYearRangeStart") {
  1628.                     if ($hoardValues['hoardDiscoveryYearRangeEnd']) {
  1629.                         if (($hoardValues['hoardDiscoveryYearRangeStart'] == $initialSliderValues['hoardDiscoveryYearRangeStart'])
  1630.                             and ($hoardValues['hoardDiscoveryYearRangeEnd'] == $initialSliderValues['hoardDiscoveryYearRangeEnd'])) {
  1631.                             # Do nothing
  1632.                         } else {
  1633.                             $endKey = "hoardDiscoveryYearRangeEnd";
  1634.                             $endValue = $hoardValues['hoardDiscoveryYearRangeEnd'];
  1636.                             if ($hoardValues['hoardDiscoveryYearRangeStart'] > $initialSliderValues['hoardDiscoveryYearRangeStart']) {
  1637.                                 $rangeCondition  = ' (h.' . 'discoveryYear1' . ' >= :' . $key;
  1638.                                 $rangeCondition .=   ' AND h.' . 'discoveryYear1' . ' <= :' . $endKey;
  1639.                                 $rangeCondition .= ' )';
  1640.                                 $rangeCondition .= ' OR ';
  1641.                                 $rangeCondition .= ' (h.' . 'discoveryYear2' . ' >= :' . $key;
  1642.                                 $rangeCondition .=   '  AND h.' . 'discoveryYear2' . ' <= :' . $endKey;
  1643.                                 $rangeCondition .= ' )';
  1644.                                 $doctrineQueryBuilder->andWhere($rangeCondition);
  1645.                                 $queryParameters[$key] = $value;
  1646.                                 $queryParameters[$endKey] = $endValue;
  1647.                             } else {
  1648.                                 # just search on end date
  1649.                                 $rangeCondition  = ' h.' . 'discoveryYear1' . ' <= :' . $endKey;
  1650.                                 $rangeCondition .= ' OR ';
  1651.                                 $rangeCondition .= ' h.' . 'discoveryYear2' . ' <= :' . $endKey;
  1652.                                 $doctrineQueryBuilder->andWhere($rangeCondition);
  1653.                                 $queryParameters[$endKey] = $endValue;
  1654.                             }
  1655.                         }
  1656.                     } else {
  1657.                         if ($hoardValues['hoardDiscoveryYearRangeStart'] == $initialSliderValues['hoardDiscoveryYearRangeStart']) {
  1658.                             # Do nothing
  1659.                         } else {
  1660.                             $doctrineQueryBuilder->andWhere('h.' . 'discoveryYear1' . ' = :' . $key);
  1661.                             $queryParameters[$key] = $value;
  1662.                         }
  1663.                     }
  1664.                 } else if ($field == "terminalYearRangeStart") {
  1665.                     if (isset($hoardValues['hoardTerminalYearRangeEnd'])) {
  1666.                         if (($hoardValues['hoardTerminalYearRangeStart'] == $initialSliderValues['hoardTerminalYearRangeStart'])
  1667.                             and ($hoardValues['hoardTerminalYearRangeEnd'] == $initialSliderValues['hoardTerminalYearRangeEnd'])) {
  1668.                             # Do nothing
  1669.                         } else {
  1670.                             $endKey = "hoardTerminalYearRangeEnd";
  1671.                             $endValue = $hoardValues['hoardTerminalYearRangeEnd'];
  1672.                             if (($hoardValues['hoardTerminalYearRangeStart'] > $initialSliderValues['hoardTerminalYearRangeStart'])
  1673.                                 and
  1674.                                ($hoardValues['hoardTerminalYearRangeEnd'] < $initialSliderValues['hoardTerminalYearRangeEnd'])) {
  1675.                                 $rangeCondition  = ' (h.' . 'terminalYear1' . ' >= :' . $key;
  1676.                                 $rangeCondition .=   ' AND h.' . 'terminalYear1' . ' <= :' . $endKey;
  1677.                                 $rangeCondition .= ' )';
  1678.                                 $rangeCondition .= ' OR ';
  1679.                                 $rangeCondition .= ' (h.' . 'terminalYear2' . ' >= :' . $key;
  1680.                                 $rangeCondition .=   '  AND h.' . 'terminalYear2' . ' <= :' . $endKey;
  1681.                                 $rangeCondition .= ' )';
  1682.                                 $doctrineQueryBuilder->andWhere($rangeCondition);
  1683.                                 $queryParameters[$key] = $value;
  1684.                                 $queryParameters[$endKey] = $endValue;
  1685.                             } else if ($hoardValues['hoardTerminalYearRangeStart'] > $initialSliderValues['hoardTerminalYearRangeStart']) {
  1686.                                 $rangeCondition  = ' h.' . 'terminalYear1' . ' >= :' . $key;
  1687.                                 $rangeCondition .= ' OR ';
  1688.                                 $rangeCondition .= ' h.' . 'terminalYear2' . ' >= :' . $key;
  1689.                                 $doctrineQueryBuilder->andWhere($rangeCondition);
  1690.                                 $queryParameters[$key] = $value;
  1691.                             } else if ($hoardValues['hoardTerminalYearRangeEnd'] < $initialSliderValues['hoardTerminalYearRangeEnd']) {
  1692.                                 $rangeCondition  = ' h.' . 'terminalYear1' . ' <= :' . $endKey;
  1693.                                 $rangeCondition .= ' OR ';
  1694.                                 $rangeCondition .= ' h.' . 'terminalYear2' . ' <= :' . $endKey;
  1695.                                 $doctrineQueryBuilder->andWhere($rangeCondition);
  1696.                                 $queryParameters[$endKey] = $endValue;
  1697.                             }
  1698.                         }
  1699.                     } else {
  1700.                         if ($hoardValues['hoardTerminalYearRangeStart'] == $initialSliderValues['hoardTerminalYearRangeStart']) {
  1701.                             # Do nothing
  1702.                         } else {
  1703.                             $doctrineQueryBuilder->andWhere('h.' . 'terminalYear1' . ' = :' . $key);
  1704.                             $queryParameters[$key] = $value;
  1705.                         }
  1706.                     }
  1707.                 } else if ($field == "openingYearRangeStart") {
  1708.                     if (isset($hoardValues['hoardOpeningYearRangeEnd'])) {
  1709.                         if (($hoardValues['hoardOpeningYearRangeStart'] == $initialSliderValues['hoardOpeningYearRangeStart'])
  1710.                             and ($hoardValues['hoardOpeningYearRangeEnd'] == $initialSliderValues['hoardOpeningYearRangeEnd'])) {
  1711.                             # Do nothing
  1712.                         } else {
  1713.                             $endKey = "hoardOpeningYearRangeEnd";
  1714.                             $endValue = $hoardValues['hoardOpeningYearRangeEnd'];
  1715.                             if (($hoardValues['hoardOpeningYearRangeStart'] > $initialSliderValues['hoardOpeningYearRangeStart'])
  1716.                                 and
  1717.                                ($hoardValues['hoardOpeningYearRangeEnd'] < $initialSliderValues['hoardOpeningYearRangeEnd'])) {
  1718.                                 $rangeCondition  = ' (h.' . 'openingYear1' . ' >= :' . $key;
  1719.                                 $rangeCondition .=   ' AND h.' . 'openingYear1' . ' <= :' . $endKey;
  1720.                                 $rangeCondition .= ' )';
  1721.                                 $rangeCondition .= ' OR ';
  1722.                                 $rangeCondition .= ' (h.' . 'openingYear2' . ' >= :' . $key;
  1723.                                 $rangeCondition .=   '  AND h.' . 'openingYear2' . ' <= :' . $endKey;
  1724.                                 $rangeCondition .= ' )';
  1725.                                 $doctrineQueryBuilder->andWhere($rangeCondition);
  1726.                                 $queryParameters[$key] = $value;
  1727.                                 $queryParameters[$endKey] = $endValue;
  1728.                             } else if ($hoardValues['hoardOpeningYearRangeStart'] > $initialSliderValues['hoardOpeningYearRangeStart']) {
  1729.                                 $rangeCondition  = ' h.' . 'openingYear1' . ' >= :' . $key;
  1730.                                 $rangeCondition .= ' OR ';
  1731.                                 $rangeCondition .= ' h.' . 'openingYear2' . ' >= :' . $key;
  1732.                                 $doctrineQueryBuilder->andWhere($rangeCondition);
  1733.                                 $queryParameters[$key] = $value;
  1734.                             } else if ($hoardValues['hoardOpeningYearRangeEnd'] < $initialSliderValues['hoardOpeningYearRangeEnd']) {
  1735.                                 $rangeCondition  = ' h.' . 'openingYear1' . ' <= :' . $endKey;
  1736.                                 $rangeCondition .= ' OR ';
  1737.                                 $rangeCondition .= ' h.' . 'openingYear2' . ' <= :' . $endKey;
  1738.                                 $doctrineQueryBuilder->andWhere($rangeCondition);
  1739.                                 $queryParameters[$endKey] = $endValue;
  1740.                             }
  1741.                         }
  1742.                     } else {
  1743.                         if ($hoardValues['hoardOpeningYearRangeStart'] == $initialSliderValues['hoardOpeningYearRangeStart']) {
  1744.                             # Do nothing
  1745.                         } else {
  1746.                             $doctrineQueryBuilder->andWhere('h.' . 'openingYear1' . ' = :' . $key);
  1747.                             $queryParameters[$key] = $value;
  1748.                         }
  1749.                     }
  1750.                 } else if ($field == "archaeologyContextDateRangeStart") {
  1751.                     if ($hoardValues['hoardArchaeologyContextDateRangeEnd']) {
  1752.                         if (($hoardValues['hoardArchaeologyContextDateRangeStart'] == $initialSliderValues['hoardArchaeologyContextDateRangeStart'])
  1753.                             and ($hoardValues['hoardArchaeologyContextDateRangeEnd'] == $initialSliderValues['hoardArchaeologyContextDateRangeEnd'])) {
  1754.                             # Do nothing
  1755.                         } else {
  1756.                             $endKey = "hoardArchaeologyContextDateRangeEnd";
  1757.                             $endValue = $hoardValues['hoardArchaeologyContextDateRangeEnd'];
  1759.                             if ($hoardValues['hoardArchaeologyContextDateRangeStart'] > $initialSliderValues['hoardArchaeologyContextDateRangeStart']) {
  1760.                                 $rangeCondition  = ' (h.' . 'archaeologyContextDate1' . ' >= :' . $key;
  1761.                                 $rangeCondition .=   ' AND h.' . 'archaeologyContextDate1' . ' <= :' . $endKey;
  1762.                                 $rangeCondition .= ' )';
  1763.                                 $rangeCondition .= ' OR ';
  1764.                                 $rangeCondition .= ' (h.' . 'archaeologyContextDate2' . ' >= :' . $key;
  1765.                                 $rangeCondition .=   '  AND h.' . 'archaeologyContextDate2' . ' <= :' . $endKey;
  1766.                                 $rangeCondition .= ' )';
  1767.                                 $doctrineQueryBuilder->andWhere($rangeCondition);
  1768.                                 $queryParameters[$key] = $value;
  1769.                                 $queryParameters[$endKey] = $endValue;
  1770.                             } else {
  1771.                                 # just search on end date
  1772.                                 $rangeCondition  = ' h.' . 'archaeologyContextDate1' . ' <= :' . $endKey;
  1773.                                 $rangeCondition .= ' OR ';
  1774.                                 $rangeCondition .= ' h.' . 'archaeologyContextDate2' . ' <= :' . $endKey;
  1775.                                 $doctrineQueryBuilder->andWhere($rangeCondition);
  1776.                                 $queryParameters[$endKey] = $endValue;
  1777.                             }
  1778.                         }
  1779.                     } else {
  1780.                         if ($hoardValues['hoardArchaeologyContextDateRangeStart'] == $initialSliderValues['hoardArchaeologyContextDateRangeStart']) {
  1781.                             # Do nothing
  1782.                         } else {
  1783.                             $doctrineQueryBuilder->andWhere('h.' . 'archaeologyContextDate1' . ' = :' . $key);
  1784.                             $queryParameters[$key] = $value;
  1785.                         }
  1786.                     }
  1787.                 } else if ($field == "coinCountMin") {
  1788.                     $endKey = "hoardCoinCountMax";
  1789.                     $endValue = $hoardValues['hoardCoinCountMax'];
  1791.                     if (($hoardValues['hoardCoinCountMin'] == $initialSliderValues['hoardCoinCountMin'])
  1792.                         and ($hoardValues['hoardCoinCountMax'] == $initialSliderValues['hoardCoinCountMax'])) {
  1793.                         # Do nothing
  1794.                     } else {
  1795.                         $doctrineQueryBuilder->join('h.coinCount', 'coinCt');
  1796.                         $doctrineQueryBuilder->andWhere('coinCt.coinCount >= :' . $key);
  1797.                         $queryParameters[$key] = $value;
  1798.                         if ($hoardValues['hoardCoinCountMax'] < $initialSliderValues['hoardCoinCountMax']) {
  1799.                             $doctrineQueryBuilder->andWhere('coinCt.coinCount <= :' . $endKey);
  1800.                             $queryParameters[$endKey] = $endValue;
  1801.                         }
  1802.                     }
  1805.                 } else if ((in_array($field, array("discoveryYearRangeEnd", "terminalYearRangeEnd", "openingYearRangeEnd", "archaeologyContextDateRangeEnd", "coinCountMax")))) {
  1806.                     # skip
  1808.                 } else if ($field == "hoardValidatedByUser" && $this->getUser()) {
  1809.                     $doctrineQueryBuilder->andWhere('h.validatedByUser = :' . $key);
  1810.                     $queryParameters[$key] = intval($value);
  1811.                 } else if ($field == "hoardValidatedByAdmin" && $this->userIsAdmin()) {
  1812.                     $doctrineQueryBuilder->andWhere('h.validatedByAdmin = :' . $key);
  1813.                     $queryParameters[$key] = intval($value);
  1814.                 } else if ($field == "hoardCoinDataValidatedByUser" && $this->getUser()) {
  1815.                     $doctrineQueryBuilder->andWhere('h.coinDataValidatedByUser = :' . $key);
  1816.                     $queryParameters[$key] = intval($value);
  1817.                 } else if ($field == "hoardCoinDataValidatedByAdmin" && $this->userIsAdmin()) {
  1818.                     $doctrineQueryBuilder->andWhere('h.coinDataValidatedByAdmin = :' . $key);
  1819.                     $queryParameters[$key] = intval($value);
  1820.                 } else if ($field == "objectComment") {
  1821.                     $doctrineQueryBuilder->join('h.objects', 'objects')
  1822.                         ->andWhere('objects.comment LIKE :' . $key);
  1823.                     $queryParameters[$key] = '%'.$value.'%';
  1824.                 } else if ($field == "objectType") {
  1825.                     $doctrineQueryBuilder->join('h.objects', 'objects')
  1826.                                         ->andWhere('objects.object LIKE :'.$key);
  1827.                     $queryParameters[$key] = $value.'%';
  1828.                 } else if ($field == "hasContainer") {
  1829.                     $doctrineQueryBuilder->andWhere('h.containers IS NOT EMPTY');
  1830.                 } else if ($field == "hasObject") {
  1831.                     $doctrineQueryBuilder->andWhere('h.objects IS NOT EMPTY');
  1832.                 } else if ($field == "hasImage") {
  1833.                     $doctrineQueryBuilder->andWhere('h.hoardImages IS NOT EMPTY');
  1834.                 } else if ($field == "hideWhat") {
  1835.                     $doctrineQueryBuilder->andWhere('h.' . 'hideWhat' . ' = :' . $key);
  1836.                     $queryParameters[$key] = $value;
  1837.                 } else if ($field == "hideFrom") {
  1838.                     $doctrineQueryBuilder->andWhere('h.' . 'hideFrom' . ' = :' . $key);
  1839.                     $queryParameters[$key] = $value;
  1840.                 } else if ($field == "hasObjectWithImage") {
  1841.                     $doctrineQueryBuilder->join('h.objects', 'o')
  1842.                                         ->andWhere('o.objectImages IS NOT EMPTY');
  1843.                 } else if ($field == "containerString") {
  1844.                     $doctrineQueryBuilder->join('h.containers', 'cont')
  1845.                         ->andWhere('cont.container LIKE :' . $key);
  1846.                     $queryParameters[$key] = '%'.$value.'%';
  1847.                 } else if ($field == "containerComment") {
  1848.                     $doctrineQueryBuilder->join('h.containers', 'cont')
  1849.                         ->andWhere('cont.comment LIKE :' . $key);
  1850.                     $queryParameters[$key] = '%'.$value.'%';
  1851.                 } else if ($field == "containerHasImage") {
  1852.                     $doctrineQueryBuilder->join('h.containers', 'cont')
  1853.                                         ->andWhere('cont.containerImages IS NOT EMPTY');
  1854.                 }
  1856.                 else {
  1857.                     if ($field == "containerMaterials") {
  1858.                         $object = $field;
  1859.                         // $doctrineQueryBuilder->join('c.' . 'denominations', 'denomination_materials');
  1860.                         // $doctrineQueryBuilder->join('denomination_materials.material', 'materials');
  1861.                         $doctrineQueryBuilder->join('h.' . 'containers', 'container_materials');
  1862.                         $doctrineQueryBuilder->join('container_materials.material', 'containerMaterials');
  1863.                         $field = "id";
  1864.                     } else if ($field == "containerForms") {
  1865.                         $object = $field;
  1866.                         $doctrineQueryBuilder->join('h.' . 'containers', 'container_form');
  1867.                         $doctrineQueryBuilder->join('container_form.containerForm', 'containerForms');
  1868.                         $field = "id";
  1869.                     } else if ($field == "objectMaterials") {
  1870.                         $object = $field;
  1871.                         $doctrineQueryBuilder->join('h.' . 'objects', 'object_materials');
  1872.                         $doctrineQueryBuilder->join('object_materials.material', 'objectMaterials');
  1873.                         $field = "id";
  1874.                     } else if ($field == "reference") {
  1875.                         if (!$isHoardRefJoined) {
  1876.                             $doctrineQueryBuilder->join('h.' . 'hoardReferences', 'hoard_references');
  1877.                             $isHoardRefJoined = true;
  1878.                         }
  1879.                         $doctrineQueryBuilder->join('hoard_references.reference', 'hoardref');
  1880.                         $object = 'hoardref';
  1881.                         $field = "id";
  1882.                     } else if ($field == "reference_string") {
  1883.                         if (!$isHoardRefJoined) {
  1884.                             $doctrineQueryBuilder->join('h.' . 'hoardReferences', 'hoard_references');
  1885.                             $isHoardRefJoined = true;
  1886.                         }
  1887.                         $object = 'hoard_references';
  1888.                         $field = "reference_str";
  1889.                     }
  1890.                     if (is_array($value)) {
  1891.                         if(!isset($object)) {
  1892.                             $object = 'h';
  1893.                         }
  1894.                         $fieldQuery = "( ";
  1895.                         foreach ($value as $index => $val) {
  1896.                             if ($index > 0) {
  1897.                                 $fieldQuery .= " OR ";
  1898.                             }
  1899.                             $fieldQuery .= $object . '.' . $field . ' = :' . $key . "_" . $index;
  1900.                             $queryParameters[$key . "_" . $index] = $val;
  1901.                         }
  1902.                         $fieldQuery .= " )";
  1903.                         $doctrineQueryBuilder->andWhere($fieldQuery);
  1904.                     } else {
  1905.                         if ($field == "reference_str") {
  1906.                             $doctrineQueryBuilder->andWhere($object . '.' . $field . ' LIKE :' . $key);
  1907.                             $queryParameters[$key] = '%'.$value.'%';
  1908.                             // this would allow users to use their own wildcards
  1909.                             // $val = str_replace("*", "%", $value);
  1910.                             // $val = str_replace("?", "_", $val);
  1911.                             // $queryParameters[$key] = $val;
  1912.                         } else {
  1913.                             $doctrineQueryBuilder->andWhere('h.' . $field . ' = :' . $key);
  1914.                             $queryParameters[$key] = $value;
  1915.                         }
  1916.                     }
  1917.                 }
  1918.             }
  1919.             $activeTab = 3;   // Set tab to Results tab
  1920.         }
  1922.         //ignore coin year range if set to initial values
  1923.         if(isset($coinValues['coinYearRangeStart']) && $coinValues['coinYearRangeStart'] == $initialSliderValues['coinYearRangeStart']) {
  1924.             unset($coinValues['coinYearRangeStart']);
  1925.         }
  1926.         
  1927.         if(isset($coinValues['coinYearRangeEnd']) && $coinValues['coinYearRangeEnd'] == $initialSliderValues['coinYearRangeEnd']) {
  1928.             unset($coinValues['coinYearRangeEnd']);
  1929.         }
  1932.         if (!empty($coinValues)) {
  1933.             $isCoinRefJoined = false;
  1934.             foreach ($coinValues as $key => $value) {
  1935.                 $field = lcfirst(substr($key, strlen('coin')));
  1936.                 $object = "c";
  1937.                 $subTables = array("persons", "reigns", "denominations", "mints", "aspects");
  1938.                 if (in_array($field, $subTables)) {
  1939.                     $object = $field;
  1940.                     $doctrineQueryBuilder->join('c.' . $field, $object);
  1941.                     $field = "id";
  1942.                 } else if ($field == "yearRangeStart") {
  1943.                     if(isset($coinValues['coinYearRangeEnd'])) {
  1944.                         if (($coinValues['coinYearRangeStart'] == $initialSliderValues['coinYearRangeStart'])
  1945.                             and ($coinValues['coinYearRangeEnd'] == $initialSliderValues['coinYearRangeEnd'])) {
  1946.                                 #do nothing
  1947.                         } else {
  1948.                             $endKey = "coinYearRangeEnd";
  1949.                             $endValue = $coinValues['coinYearRangeEnd'];
  1950.                             
  1951.                             if($coinValues['coinYearRangeStart'] > $initialSliderValues['coinYearRangeStart']) {
  1952.                                 $rangeCondition  = ' (c.' . 'startingDate' . ' >= :' . $key;
  1953.                                 $rangeCondition .= ' AND c.' . 'startingDate' . ' <= :' . $endKey;
  1954.                                 $rangeCondition .= ' )';
  1955.                                 $rangeCondition .= ' OR ';
  1956.                                 $rangeCondition .= ' (c.' . 'endingDate' . ' >= :' . $key;
  1957.                                 $rangeCondition .= ' AND c.' . 'endingDate' . ' <= :' . $endKey;
  1958.                                 $rangeCondition .= ' )';
  1959.                                 $doctrineQueryBuilder->andWhere($rangeCondition);
  1960.                                 $queryParameters[$key] = $value;
  1961.                                 $queryParameters[$endKey] = $endValue;
  1962.                             } else {
  1963.                                 # just search on end date
  1964.                                 $rangeCondition  = ' c.' . 'startingDate' . ' <= :' . $endKey;
  1965.                                 $rangeCondition .= ' OR ';
  1966.                                 $rangeCondition .= ' c.' . 'endingDate' . ' <= :' . $endKey;
  1967.                                 $doctrineQueryBuilder->andWhere($rangeCondition);
  1968.                                 $queryParameters[$endKey] = $endValue;
  1969.                             }
  1970.                         }
  1971.                     } else {
  1972.                         if ($coinValues['coinYearRangeStart'] == $initialSliderValues['coinYearRangeStart']) {
  1973.                             # Do nothing
  1974.                         } else {
  1975.                             $doctrineQueryBuilder->andWhere('c.' . 'startingDate' . ' = :' . $key);
  1976.                             $queryParameters[$key] = $value;
  1977.                         }
  1978.                     }
  1979.                 } else if ($field == "materials") {
  1980.                     $object = $field;
  1981.                     $doctrineQueryBuilder->join('c.' . 'denominations', 'denomination_materials');
  1982.                     $doctrineQueryBuilder->join('denomination_materials.material', 'materials');
  1983.                     $field = "id";
  1984.                 } else if ($field == "reference") {
  1985.                     $object = 'coinRef';
  1986.                     if (!$isCoinRefJoined) {
  1987.                         $doctrineQueryBuilder->join('c.' . 'coinReferences', $object);
  1988.                         $isCoinRefJoined = true;
  1989.                     }
  1990.                     $field = "reference";
  1991.                 } else if ($field == "referenceString") {
  1992.                     $object = 'coinRef';
  1993.                     if (!$isCoinRefJoined) {
  1994.                         $doctrineQueryBuilder->join('c.' . 'coinReferences', $object);
  1995.                         $isCoinRefJoined = true;
  1996.                     }
  1997.                     $field = "reference_str";
  1998.                 }
  2000.                 // materials is a special case as users only have the option to search for:
  2001.                 // "23" => "Gold", "22,31" => "Silver", "7,49,53" => "Bronze"
  2002.                 // material ids have to be converted vrom comma separated values to an array
  2003.                 if ($key == "coinMaterials") {
  2004.                     $value = explode(",", $value);
  2005.                 }
  2006.                 
  2007.                 if (is_array($value)) {
  2008.                     $fieldQuery = "( ";
  2009.                     foreach ($value as $index => $val) {
  2010.                         if ($index > 0) {
  2011.                             $fieldQuery .= " OR ";
  2012.                         }
  2013.                         $fieldQuery .= $object . '.' . $field . ' = :' . $key . "_" . $index;
  2014.                         $queryParameters[$key . "_" . $index] = $val;
  2015.                     }
  2016.                     $fieldQuery .= " )";
  2017.                     $doctrineQueryBuilder->andWhere($fieldQuery);
  2019.                 } else {
  2020.                     if ($field == "hasImage") {
  2021.                         $doctrineQueryBuilder->join('c.' . 'coinImages', 'coin_images');
  2022.                         $doctrineQueryBuilder->andWhere('coin_images.deleted IS NULL');
  2023.                         //$queryParameters[$key] = $value;
  2024.                     } else {
  2025.                         if($field == 'yearRangeStart' || $field == 'yearRangeEnd') {
  2026.                             //this has already been handled
  2027.                         } elseif ($field == "reference_str") {
  2028.                             $doctrineQueryBuilder->andWhere($object . '.' . $field . ' LIKE :' . $key);
  2029.                             $val = str_replace("*", "%", $value);
  2030.                             $val = str_replace("?", "_", $val);
  2031.                             $queryParameters[$key] = $val;
  2032.                         } else {
  2033.                             // do a straight equality condition
  2034.                             $doctrineQueryBuilder->andWhere($object . '.' . $field . ' = :' . $key);
  2035.                             $queryParameters[$key] = $value;
  2036.                         }
  2037.                     }
  2038.                 }
  2039.            }
  2040.         }
  2041.     }
  2043.     // get different hoards based on query
  2044.     /**
  2045.      * Retrieves coordinates of the hoards from database based on query
  2046.      *
  2047.      * @Route("/ajax_hoard_locations", name="ajax_hoard_locations", methods={"GET"})
  2048.      */
  2049.     public function ajaxGetHoardLocationQuery(Request $request) {
  2050.         $search_type = $request->query->get('search_type');
  2051.         $isCoinMintSearch = false; 
  2052.         $isAuthenticated = false;
  2053.         if ($this->getUser()) {
  2054.             $isAuthenticated = true;
  2055.         }
  2056.         
  2057.         $searchHasCoinFields = false;
  2059.         $initialSliderValues = array();
  2060.         $initialSliderValues['hoardDiscoveryYearRangeStart'] = $this->getMinDiscoveryDate();
  2061.         $initialSliderValues['hoardDiscoveryYearRangeEnd'] = $this->getMaxDiscoveryDate();
  2062.         $initialSliderValues['hoardTerminalYearRangeStart'] = $this->getMinTerminalDate();
  2063.         $initialSliderValues['hoardTerminalYearRangeEnd'] = $this->getMaxTerminalDate();
  2064.         $initialSliderValues['hoardOpeningYearRangeStart'] = $this->getMinOpeningDate();
  2065.         $initialSliderValues['hoardOpeningYearRangeEnd'] = $this->getMaxOpeningDate();
  2066.         $initialSliderValues['hoardArchaeologyContextDateRangeStart'] = $this->getMinArchaeologyContextDate();
  2067.         $initialSliderValues['hoardArchaeologyContextDateRangeEnd'] = $this->getMaxArchaeologyContextDate();
  2068.         $coinminmax = $this->getMinMaxCoinDate();
  2069.         $initialSliderValues['coinYearRangeStart'] = $coinminmax['min1'];
  2070.         $initialSliderValues['coinYearRangeEnd'] = $coinminmax['max1'];
  2071.         $initialSliderValues['hoardCoinCountMin'] = 0;
  2072.         $initialSliderValues['hoardCoinCountMax'] = 100000;
  2073.       
  2074.         $em = $this->getDoctrine()->getManager();
  2075.         if (isset($request->query->get('ox_hs')['rules']) && strlen($request->query->get('ox_hs')['rules'])) {
  2076.             // check if coin fields are included in the search set $searchHasCoinFields
  2077.             $rulesStr = $request->query->get('ox_hs')['rules'];
  2078.             if ( strpos($rulesStr, '"field":"coin') || strpos($rulesStr, '"field":"c.') ) {
  2079.                 $searchHasCoinFields = true;
  2080.             }
  2082.             $rules = json_decode($rulesStr, true);
  2083.             $doctrineQueryBuilder = $this->buildQuery($rules, $search_type, $request->request->get('format'));
  2084.             $doctrineQueryBuilder->addSelect('h.id, h.findSpotLatitude, h.findSpotLongitude, h.findSpotName');
  2085.             if ($search_type == 'search-coins') {
  2086.                 $doctrineQueryBuilder->addSelect('c.quantity');
  2087.                 if (strpos($rulesStr, '"field":"coinMints.id"')) {
  2088.                     $isCoinMintSearch = true;
  2089.                     // if coin mint was searched and searching for coins
  2090.                     // add mint locations to the response
  2091.                     $doctrineQueryBuilder
  2092.                         ->addSelect('coinMints.id AS mintId, coinMints.mint, coinMints.latitude AS mintLatitude, coinMints.longitude AS mintLongitude');
  2093.                 }
  2094.             }
  2095.         } 
  2096.         else {
  2097.             foreach ($request->query->get('ox_hs') as $key => $value) {
  2098.                 // check if coin fields are included in the search set $searchHasCoinFields
  2099.                 if (substr( $key, 0, 8 ) === "coinYear") {
  2100.                     if ($value != $initialSliderValues[$key]) {
  2101.                         $searchHasCoinFields = true;
  2102.                     }
  2103.                 } 
  2104.                 elseif (substr( $key, 0, 4 ) === "coin") {
  2105.                     if ($value && $value != "") {
  2106.                         $searchHasCoinFields = true;
  2107.                     }
  2108.                 }
  2109.             }
  2111.             $doctrineQueryBuilder = $em->createQueryBuilder();
  2113.             if ($search_type == 'search-coins') {
  2114.                 $doctrineQueryBuilder
  2115.                     ->from('OxHoardBundle:Coin', 'c')
  2116.                     ->leftJoin('c.hoard', 'h')
  2117.                     ->addSelect('c.id, c.quantity');
  2118.             } 
  2119.             else {
  2120.                 $doctrineQueryBuilder->select('h')
  2121.                     ->from('OxHoardBundle:Hoard', 'h')
  2122.                     ->leftJoin('h.coins', 'c');
  2123.             }
  2125.             $doctrineQueryBuilder->addSelect('h.id, h.findSpotLatitude, h.findSpotLongitude, h.findSpotName, h.validatedByUser')
  2126.                 ->distinct();
  2127.             $em->getFilters()->enable('softdeleteable');
  2129.             $currentYear = date("Y");
  2131.             $activeTab = 1;
  2132.             $hoardValues = array();
  2133.             $coinValues  = array();
  2134.             $queryParameters = array();
  2135.             if (!empty($request->query->get('ox_hs'))) {
  2136.                 $this->constructSearchQuery($request, $doctrineQueryBuilder, $activeTab,
  2137.                     $hoardValues, $coinValues, $queryParameters, $initialSliderValues);
  2138.             }
  2140.             foreach ($queryParameters as $key => $value) {
  2141.                 $doctrineQueryBuilder->setParameter($key, $value);
  2142.             }
  2143.             // if coin mint was searched and searching for coins
  2144.             if ($search_type == 'search-coins' && strpos($doctrineQueryBuilder->getDql(), "mints.id = :coinMints")) {
  2145.                 $isCoinMintSearch = true;
  2146.                 // add mint locations to the response
  2147.                 $doctrineQueryBuilder
  2148.                     ->addSelect('mints.id AS mintId, mints.mint, mints.latitude AS mintLatitude, mints.longitude AS mintLongitude');
  2149.             }
  2150.         }
  2151.         
  2152.         if (!$isAuthenticated) {
  2153.             // public can only see hoards which are validated by the user,
  2154.             // and which are either not hidden, or don't have everything hidden
  2155.             $doctrineQueryBuilder
  2156.                 ->andWhere('(h.validatedByUser = true) AND (h.hideFrom = 3 OR h.hideWhat < 3 OR h.hideWhat IS NULL)')
  2157.                 ->andWhere('(h.hideLocation IS NULL OR h.hideLocation != 1)');
  2159.             // if coin fields are included in the search
  2160.             if ($searchHasCoinFields) {
  2161.                 // public can only see hoards with coins which are validated by the user,
  2162.                 // and which are either not hidden, or don't have anything hidden
  2163.                 $doctrineQueryBuilder->andWhere('(h.coinDataValidatedByUser = true) AND (h.hideFrom = 3 OR h.hideWhat IS NULL)');
  2164.             }
  2165.         }
  2166.         
  2167.         $doctrineQueryBuilderAllRows = clone $doctrineQueryBuilder;
  2168.         $boundary = isset($request->query->get('ox_hs')["boundary"]) ?
  2169.             $request->query->get('ox_hs')["boundary"] :
  2170.             null;
  2171.         $noCoOrds = [];
  2172.         $hasBoundary = true;
  2173.         if (!$boundary || is_null($boundary)) {
  2174.             $hasBoundary = false;
  2175.             $doctrineQueryBuilderNoCoOrds = clone $doctrineQueryBuilderAllRows;
  2176.             $doctrineQueryBuilderNoCoOrds
  2177.                 ->andWhere('h.findSpotLongitude IS NULL OR h.findSpotLatitude IS NULL');
  2178.             $doctrineQueryNoCoOrds = $doctrineQueryBuilderNoCoOrds->getQuery();
  2179.             $noCoOrds = $doctrineQueryNoCoOrds->getArrayResult();
  2180.             // Add countries to "No CoOrd" results, if present.
  2181.             foreach ($noCoOrds as &$noCoOrds_hoard) {
  2182.                 if ($noCoOrds_hoard['id']) {
  2183.                     $hoardEntity = $em->getRepository('OxHoardBundle:Hoard')->find($noCoOrds_hoard['id']);
  2184.                     $countries = $hoardEntity->getCountries();
  2185.                     foreach ($countries as $country) {
  2186.                         if (isset($noCoOrds_hoard['country'])) {
  2187.                             $noCoOrds_hoard['country'] .= ", " . $country->getCountry();
  2188.                         }
  2189.                         else {
  2190.                             $noCoOrds_hoard['country'] = ", " . $country->getCountry();
  2191.                         }
  2192.                     }
  2193.                 }
  2194.             }
  2195.         }
  2197.         $doctrineQueryBuilder
  2198.             ->andWhere('h.findSpotLatitude IS NOT NULL')
  2199.             ->andWhere('h.findSpotLongitude IS NOT NULL');
  2200.         // Add handling of boundaries set for maps.
  2201.         $boundary = isset($request->query->get('ox_hs')["boundary"]) ?
  2202.             $request->query->get('ox_hs')["boundary"] :
  2203.             null;
  2204.         if ($boundary) {
  2205.             $doctrineQueryBuilder->andWhere('ST_Contains(ST_GEOMFROMTEXT(\'Polygon((' . $boundary . '))\'), Point(h.findSpotLatitude, h.findSpotLongitude)) = true');
  2206.         }
  2207.         
  2208.         $doctrineQuery = $doctrineQueryBuilder->getQuery();
  2209.         $doctrineQueryAllRows = $doctrineQueryBuilderAllRows->getQuery();
  2211.         $searchedHoards = $doctrineQuery -> getArrayResult();
  2212.         //
  2213.         $minTerminalYear = $initialSliderValues['hoardTerminalYearRangeStart'];
  2214.         $maxTerminalYear = $initialSliderValues['hoardTerminalYearRangeEnd'];
  2215.         $termYears = [];
  2216.         foreach ($searchedHoards as $hoard) {
  2217.             if (isset($hoard[0]["terminalYear1"])) {
  2218.                 $termYears[] = $hoard[0]["terminalYear1"];
  2219.             }
  2220.         }
  2221.         sort($termYears);
  2222.         $minTerminalYear = isset($termYears[0]) ? 
  2223.             $termYears[0] :
  2224.             $minTerminalYear;
  2225.         $maxTerminalYear = isset($termYears[count($termYears) - 1]) ? 
  2226.             $termYears[count($termYears) - 1] :
  2227.             $maxTerminalYear;
  2229.         $allSearchedHoards = $doctrineQueryAllRows->getArrayResult();
  2230.         if ($search_type == "search-coins") {
  2231.             $totalPoints =  array_reduce($allSearchedHoards, function ($carry, $item) {
  2232.                 if ($item['id'] && $item['findSpotName']) {
  2233.                     $carry += $item['quantity'];
  2234.                 }
  2235.                 return $carry;
  2236.             });
  2237.         } 
  2238.         else {
  2239.             $totalPoints = count($allSearchedHoards);
  2240.         }
  2241.         return new JsonResponse(array(
  2242.             'data' => $searchedHoards,
  2243.             'terminalYears' => [
  2244.                 'min' => $minTerminalYear,
  2245.                 'max' => $maxTerminalYear,
  2246.             ],
  2247.             'searchType' => $search_type,
  2248.             'isAuthenticated' => $isAuthenticated,
  2249.             'totalPoints' => $totalPoints,
  2250.             'noCoOrds' => $noCoOrds,
  2251.             'boundary' => $hasBoundary,
  2252.             'isCoinMintSearch' => $isCoinMintSearch,
  2253.         ));
  2254.     }
  2256.     /**
  2257.      * Get all coins to use in charts
  2258.      *
  2259.      * @param Request $request
  2260.      * @Route("/coins_chart_ajax", name="coins_chart_get", methods={"GET"})
  2261.      */
  2262.     public function restGetCoinsChart(Request $request, PaginatorInterface $paginator)
  2263.     {
  2264.         // call the indexAction with the given request string
  2265.         $coins = $this->indexAction($request, $paginator, true); 
  2267.         $allCoins = array();
  2268.         foreach ($coins as $coin) {
  2269.             $quantity = $coin->getQuantity();
  2270.             $startingDate = $coin->getStartingDate();
  2271.             $endingDate = $coin->getEndingDate();
  2272.             $reigns = $coin->getReigns();
  2273.             foreach($reigns as $reign) {
  2274.                 if ($startingDate || $endingDate ||
  2275.                     ($reign->getId() != 2191 && $reign->getId() != 242)) { // exclude reigns: Uncertain, Republic
  2276.                     array_push($allCoins, array(
  2277.                         'quantity' => $quantity / count($reigns),
  2278.                         'startingDate' => $startingDate,
  2279.                         'endingDate' => $endingDate,
  2280.                         "reignStartDate" => $reign->getStartDate(),
  2281.                         "reignEndDate" => $reign->getEndDate()
  2282.                     ));
  2283.                 }
  2284.             }
  2285.         }
  2287.         return new JsonResponse($allCoins);
  2288.     }
  2289. }