From 2395d6809f18112734662582ae842ff73ccb64b6 Mon Sep 17 00:00:00 2001 From: Steve Sutton Date: Thu, 6 Jun 2013 15:49:38 +0000 Subject: [PATCH] Update for no city assigned to a record If they have no city need to use left outer join and some code clean up --- Toolkit/Members/SearchQueryGenerator.php | 100 +++++++---------------- 1 file changed, 31 insertions(+), 69 deletions(-) diff --git a/Toolkit/Members/SearchQueryGenerator.php b/Toolkit/Members/SearchQueryGenerator.php index 42a6763..9d847ef 100644 --- a/Toolkit/Members/SearchQueryGenerator.php +++ b/Toolkit/Members/SearchQueryGenerator.php @@ -167,21 +167,16 @@ class Toolkit_Members_SearchQueryGenerator { $sql = " SELECT distinct m.*, c.city_name, s.state_name AS state, s.state_abb - AS state_abbr, ma.reservation_id, ma.num_rooms, - ma.year_round + AS state_abbr FROM member m - NATURAL LEFT JOIN member_accommodations ma - JOIN state s ON m.state_id = s.state_id - JOIN city c ON m.city_id = c.city_id, + LEFT OUTER JOIN state s ON m.state_id = s.state_id + LEFT OUTER JOIN city c ON m.city_id = c.city_id, exploded_members_name emn"; $params = array( "new_member <> '1'", "active = '1'", 'emn.mid = m.member_id'); - if (defined('EVENT_MANAGEMENT') && EVENT_MANAGEMENT) { - $params[] = "(events_only IS NULL OR events_only = false)"; - } // Limit to members whose first letter starts with the letter // a user clicked in the narrow alphabetically list. if (isset($_GET['alpha']) && $_GET['alpha'] == '0-9') { @@ -240,78 +235,67 @@ class Toolkit_Members_SearchQueryGenerator $postedArrays = filter_var_array( $_REQUEST, array( - 'activityId' => FILTER_VALIDATE_INT, - 'parkId' => FILTER_VALIDATE_INT, - 'regionId' => FILTER_VALIDATE_INT, - 'countyId' => FILTER_VALIDATE_INT, - 'activity' => array( + 'category_id' => FILTER_VALIDATE_INT, + 'sub_category_id' => FILTER_VALIDATE_INT, + 'activityId' => FILTER_VALIDATE_INT, + 'parkId' => FILTER_VALIDATE_INT, + 'regionId' => FILTER_VALIDATE_INT, + 'countyId' => FILTER_VALIDATE_INT, + 'activity' => array( 'filter' => FILTER_VALIDATE_INT, 'flags' => FILTER_FORCE_ARRAY ), - 'park' => array( + 'park' => array( 'filter' => FILTER_VALIDATE_INT, 'flags' => FILTER_FORCE_ARRAY ) ) ); -// var_dump($_POST); -// var_dump($postedArrays); - if ( !empty($postedArrays['activity']) - && $postedArrays['activity'][0] - ) { + extract($postedArrays); + + if (!empty($activity) && $activity[0]) { $params[] = "m.member_id IN ( SELECT member_id FROM member_category WHERE category_id IN (" - .implode(',', $postedArrays['activity'])."))"; + .implode(',', $activity)."))"; } - if ($postedArrays['activityId']) { + if ($activityId) { $params[] = "m.member_id IN ( SELECT member_id FROM member_category - WHERE category_id = {$postedArrays['activityId']})"; + WHERE category_id = {$activityId})"; } - if ($postedArrays['parkId']) { + if ($parkId) { $params[] = "m.member_id IN ( SELECT member_id FROM member_category - WHERE category_id = {$postedArrays['parkId']})"; + WHERE category_id = {$parkId})"; } - if ($postedArrays['regionId']) { - $params[] = "m.region = {$postedArrays['regionId']}"; + if ($regionId) { + $params[] = "m.region = {$regionId}"; } - if ($postedArrays['countyId']) { - $params[] = "m.county = {$postedArrays['countyId']}"; + if ($countyId) { + $params[] = "m.county = {$countyId}"; } - if ( !empty($postedArrays['park']) - && $postedArrays['park'][0] - ) { + if (!empty($park) && $park[0]) { $params[] = "m.member_id IN ( SELECT member_id FROM member_category WHERE category_id IN (" - .implode(',', $postedArrays['park'])."))"; + .implode(',', $park)."))"; } // Limit to members who have the category assigned to them // that a user selected from the member type box. - if ( isset($_GET['sub_category_id']) - && ctype_digit($_GET['sub_category_id']) - ) { - $subCategoryId = filter_input( - INPUT_GET, - 'sub_category_id', - FILTER_SANITIZE_NUMBER_INT - ); + if ($sub_category_id) { $params[] = " m.member_id IN ( SELECT member_id FROM member_category WHERE category_id = " . - $dbh->quote($subCategoryId) . ')'; - } elseif ( isset($_GET['category_id']) - && ctype_digit($_GET['category_id']) - ) { + $dbh->quote($sub_category_id) . ')'; + } elseif ($category_id) { unset($subCats); if ($this->_includeSubCats) { // Members who are assigned specifically to this category @@ -324,7 +308,7 @@ class Toolkit_Members_SearchQueryGenerator 'category_id', 'parent_id', 'pos', - $_GET['category_id'] + $category_id ); $categories = $this->_getMemberCategoriesForPage($dbh); @@ -344,15 +328,14 @@ class Toolkit_Members_SearchQueryGenerator } } } - if ( ( isset($_GET['category_id']) - && ctype_digit($_GET['category'])) + if ( $category_id || isset($subCats) ) { $params[] = " m.member_id IN ( SELECT member_id FROM member_category - WHERE category_id = {$_GET['category_id']} + WHERE category_id = {$category_id} $subCats)"; } } elseif ($categories = $this->_getMemberCategoriesForPage($dbh)) { @@ -404,30 +387,9 @@ class Toolkit_Members_SearchQueryGenerator } } - if (!is_null($sections)) { - foreach ($sections as $v) { - switch ($v) { - case 'packages' : - $params[] = " - member_id IN ( - SELECT member_id - FROM member_packages - WHERE NOT pending - AND CURRENT_DATE BETWEEN sdate AND edate)"; - break; - - default : - break; - } - } - } - if (!empty($params)) { $sql .= ' WHERE ' . implode(' AND ', $params); } -//var_dump($params); -//var_dump($sql); -//exit; return $sql; } -- 2.17.1