From 0005de786fe88595f01b112038fa94e8186c8955 Mon Sep 17 00:00:00 2001 From: Steve Sutton Date: Thu, 26 Jun 2014 15:59:02 -0400 Subject: [PATCH] Update trail search form adding city search setup the activities and parks as filters also --- Toolkit/Members/SearchDisplay.php | 27 +++ Toolkit/Members/SearchQueryGenerator.php | 34 ++- Toolkit/Members/UserSearchForm.php | 27 ++- Toolkit/Members/formAjax.php | 44 +++- Toolkit/Members/templates/userSearchForm.html | 201 ++++++++++++++---- Toolkit/Page.php | 15 ++ libjs/findATrail.js | 126 ++++++----- templates/template.html | 21 +- 8 files changed, 375 insertions(+), 120 deletions(-) diff --git a/Toolkit/Members/SearchDisplay.php b/Toolkit/Members/SearchDisplay.php index e986558..c843865 100644 --- a/Toolkit/Members/SearchDisplay.php +++ b/Toolkit/Members/SearchDisplay.php @@ -43,6 +43,7 @@ class Toolkit_Members_SearchDisplay 'parkId' => FILTER_VALIDATE_INT, 'regionId' => FILTER_VALIDATE_INT, 'countyId' => FILTER_VALIDATE_INT, + 'cityId' => FILTER_VALIDATE_INT, 'amenity' => array( 'filter' => FILTER_VALIDATE_INT, 'flags' => FILTER_FORCE_ARRAY @@ -61,6 +62,10 @@ class Toolkit_Members_SearchDisplay $parkName = $this->getCategoryName($dbh, $parkId); $this->_searchTerms->offsetSet('park', $parkName); } + if ($cityId) { + $cityName = $this->getCityName($dbh, $cityId); + $this->_searchTerms->offsetSet('city', $cityName); + } if ($destinationId) { $destinationName = $this->getCategoryName($dbh, $destinationId); $this->_searchTerms->offsetSet('destination', $destinationName); @@ -112,6 +117,7 @@ class Toolkit_Members_SearchDisplay $html .= 'Parks '; } } + if ($this->_searchTerms->offsetExists('destination')) { $dest = $this->_searchTerms->offsetGet('destination'); $html .= 'in ' . $dest . ' '; @@ -125,6 +131,11 @@ class Toolkit_Members_SearchDisplay . ' County '; } + if ($this->_searchTerms->offsetExists('city')) { + $city = $this->_searchTerms->offsetGet('city'); + $html .= 'in ' . $city . ' '; + } + } return $html; } @@ -178,6 +189,22 @@ class Toolkit_Members_SearchDisplay } } + protected function getCityName(PDO $dbh, $id) + { + try { + $sql = " + SELECT city_name + FROM members.city + WHERE city_id = :id"; + $stmt = $dbh->prepare($sql); + $stmt->bindParam(':id', $id, PDO::PARAM_INT); + $stmt->execute(); + return $stmt->fetchColumn(); + } catch (PDOException $e) { + Toolkit_Common::handleError($e); + } + } + protected function getAmenityName(PDO $dbh, $id) { try { diff --git a/Toolkit/Members/SearchQueryGenerator.php b/Toolkit/Members/SearchQueryGenerator.php index db7c6aa..6263221 100644 --- a/Toolkit/Members/SearchQueryGenerator.php +++ b/Toolkit/Members/SearchQueryGenerator.php @@ -236,28 +236,52 @@ class Toolkit_Members_SearchQueryGenerator 'destinationId' => FILTER_VALIDATE_INT, 'regionId' => FILTER_VALIDATE_INT, 'countyId' => FILTER_VALIDATE_INT, + 'cityId' => FILTER_VALIDATE_INT, 'activity' => array( 'filter' => FILTER_VALIDATE_INT, 'flags' => FILTER_FORCE_ARRAY ), - 'amenity' => array( + 'amenity' => array( 'filter' => FILTER_VALIDATE_INT, 'flags' => FILTER_FORCE_ARRAY ), 'park' => array( 'filter' => FILTER_VALIDATE_INT, 'flags' => FILTER_FORCE_ARRAY + ), + 'filterActivity' => array( + 'filter' => FILTER_VALIDATE_INT, + 'flags' => FILTER_FORCE_ARRAY + ), + 'filterParks' => array( + 'filter' => FILTER_VALIDATE_INT, + 'flags' => FILTER_FORCE_ARRAY ) ) ); extract($postedArrays); + // These first three have to be grouped into OR's if (!empty($amenity) && $amenity[0]) { - $params[] = "m.member_id IN ( + $params2[] = "m.member_id IN ( SELECT member_id FROM member_amenity WHERE amenity_id IN (" .implode(',', $amenity)."))"; } + if (!empty($filterActivity)) { + $params2[] = "m.member_id IN ( + SELECT member_id + FROM member_category + WHERE category_id IN (" + .implode(',', $filterActivity)."))"; + } + if (!empty($filterParks)) { + $params2[] = "m.member_id IN ( + SELECT member_id + FROM member_category + WHERE category_id IN (" + .implode(',', $filterParks)."))"; + } // if (!empty($activity) && $activity[0]) { // $params[] = "m.member_id IN ( // SELECT member_id @@ -265,6 +289,9 @@ class Toolkit_Members_SearchQueryGenerator // WHERE category_id IN (" // .implode(',', $activity)."))"; // } + if ($cityId) { + $params[] = "m.city_id = $cityId"; + } if ($activityId) { $params[] = "m.member_id IN ( SELECT member_id @@ -401,6 +428,9 @@ class Toolkit_Members_SearchQueryGenerator if (!empty($params)) { $sql .= ' WHERE ' . implode(' AND ', $params); } + if (!empty($params2)) { + $sql .= ' AND (' . implode(' OR ', $params2) . ')'; + } return $sql; } diff --git a/Toolkit/Members/UserSearchForm.php b/Toolkit/Members/UserSearchForm.php index f844a47..a5a314b 100644 --- a/Toolkit/Members/UserSearchForm.php +++ b/Toolkit/Members/UserSearchForm.php @@ -583,6 +583,7 @@ class Toolkit_Members_UserSearchForm $destinations = $this->getDestinations(); $regions = $this->getAvailableRegions($this->dbh); $counties = $this->getCounties(); + $cities = $this->getCities(); $page = new stdClass(); $page->formURL @@ -609,6 +610,10 @@ class Toolkit_Members_UserSearchForm = (!empty($counties) && count($counties) > 1) ? $counties : null; + $page->cities + = (!empty($cities) && count($cities) > 1) + ? $cities + : null; $page->amenities = $amenities; return $tpl->bufferedOutputObject($page); @@ -869,7 +874,7 @@ class Toolkit_Members_UserSearchForm public function getCounties() { $counties = array(); - $sql .= " + $sql = " SELECT * FROM county WHERE county_id IN ( @@ -885,4 +890,24 @@ class Toolkit_Members_UserSearchForm } return $counties; } + + public function getCities() + { + $cities = array(); + $sql = " + SELECT * + FROM city + WHERE city_id IN ( + SELECT DISTINCT city_id + FROM member + WHERE active + AND city_id is not null) + ORDER BY city_name"; + $stmt = $this->dbh->prepare($sql); + $stmt->execute(); + while ($row = $stmt->fetch()) { + $cities[$row['city_id']] = $row['city_name']; + } + return $cities; + } } diff --git a/Toolkit/Members/formAjax.php b/Toolkit/Members/formAjax.php index fa6948e..a74ccc4 100644 --- a/Toolkit/Members/formAjax.php +++ b/Toolkit/Members/formAjax.php @@ -45,25 +45,47 @@ try { break; } } + $reqionSql + = ($regionId) + ? "AND region_id = $regionId" + : ''; $sql = " - SELECT * - FROM members.county - WHERE county_id IN ( - SELECT county + SELECT county_id,county_name + FROM members.county + WHERE county_id IN (SELECT county FROM members.member WHERE active = '1' AND new_member <> true - AND member_id IN ( - SELECT member_id - FROM members.member_category - {$whereCategory}) - )"; + AND member_id IN (SELECT member_id + FROM members.member_category + {$whereCategory})) + {$reqionSql} + ORDER BY county_name"; $stmt = $dbh->prepare($sql); $stmt->execute(); echo json_encode($stmt->fetchAll(PDO::FETCH_ASSOC)); exit; } switch ($searchBy) { + case 'cities': + $countySql + = ($countyId) + ? "AND county_id = $countyId" + : ''; + $sql = " + SELECT city_id,city_name + FROM city + WHERE city_id IN (SELECT city_id + FROM member + WHERE active = '1' + AND new_member <> true) + {$countySql} + ORDER BY city_name"; + $stmt = $dbh->prepare($sql); + $stmt->execute(); + echo json_encode($stmt->fetchAll(PDO::FETCH_ASSOC)); + exit; + break; case 'parks': $searchById = "AND parent_id = " @@ -187,7 +209,7 @@ try { } if ($regionId) { $sql = " - SELECT * + SELECT county_id,county_name FROM members.county WHERE region_id = :region AND region_id IN ( @@ -204,7 +226,7 @@ try { } if (isset($_REQUEST['regionId']) && !$regionId) { $sql = " - SELECT * + SELECT county_id,county_name FROM members.county WHERE region_id IN ( SELECT region diff --git a/Toolkit/Members/templates/userSearchForm.html b/Toolkit/Members/templates/userSearchForm.html index 6ad3d47..762603c 100644 --- a/Toolkit/Members/templates/userSearchForm.html +++ b/Toolkit/Members/templates/userSearchForm.html @@ -62,6 +62,18 @@ border-radius: 8px 8px 8px 8px; box-shadow: 0 0 10px rgba(176, 164, 135, 0.7) inset; } + .filterCol { + position: relative; + font-family: Arial, Helvetica, sans-serif; + float: left; + width: 32.33333333%; + padding: 10px 1%; + /*height: 160px;*/ + } + .filterCol label { + width: 100%; + display: block; + } #lowerbox { position: relative; font-family: Arial, Helvetica, sans-serif; @@ -108,6 +120,20 @@ } #filter_header { /*border-bottom: 1px solid #D4D4D4;*/ + font-family: Arial, Helvetica, sans-serif; + background: #B8C728; + width: auto; + bottom: 10px; + right: 2%; + height: 29px; + cursor: pointer; + background: #8A1E03; + border: 2px solid #FFF; + border-radius: 6px; + color: #FFF; + font-weight: bold; + text-transform: uppercase; + padding: 3px 8px; } #trail-form-name input[type=search] { display: block; @@ -193,84 +219,96 @@
-
Search by:
-
- + + {foreach:regions,id,name} '.$name.''; ?> {end:}
-
- + + {foreach:counties,id,name} '.$name.''; ?> {end:}
-
- + + {foreach:cities,id,name} '.$name.''; ?> {end:}
+ flexy:ignore="yes" + action="{formURL:h}" + id="trail-form-ap" + method="get">
-
Search by Regions & Counties:
-
- + + {foreach:destinations,id,name} '.$name.''; ?> {end:}
-
- + + {foreach:activities,id,name} '.$name.''; + ?> + {end:} + +
+
+ '; } + if ($city = filter_var($_REQUEST['cityId'], FILTER_VALIDATE_INT)) { + echo ''; + } ?>
Filter Results:
-
-
+ - +

Parks

+ {foreach:parks,id,name} + + {end:} +
+ +
+