Annotation of /com_supacart/trunk/admin_files/html/shop_browse_queries.php
Parent Directory
| 
Revision Log
Revision 4 - (view) (download)
| 1 : | andphe | 4 | <?php |
| 2 : | defined( '_VALID_MOS' ) or die( 'Direct Access to this location is not allowed.' ); | ||
| 3 : | /* | ||
| 4 : | * This file is to be included from the file shop.browse.php | ||
| 5 : | * and uses variables from the environment of the file shop.browse.php | ||
| 6 : | * | ||
| 7 : | * @version $Id: shop_browse_queries.php 854 2007-05-09 20:28:20Z soeren_nb $ | ||
| 8 : | * @package SupaCart | ||
| 9 : | * @subpackage html | ||
| 10 : | * See COPYRIGHT.php for copyright notices and details. | ||
| 11 : | * @license GNU/GPL Version 2, see LICENSE.php | ||
| 12 : | * SupaCart is free software, originally derived from Virtuemart.. This version may have been modified pursuant | ||
| 13 : | * to the GNU General Public License, and as distributed it includes or | ||
| 14 : | * is derivative of works licensed under the GNU General Public License or | ||
| 15 : | * other free or open source software licenses. | ||
| 16 : | * See /administrator/components/com_supacart/COPYRIGHT.php for copyright notices and details. | ||
| 17 : | * | ||
| 18 : | * http://www.supacart.com | ||
| 19 : | */ | ||
| 20 : | mm_showMyFileName( __FILE__ ); | ||
| 21 : | |||
| 22 : | /** Prepare the SQL Queries | ||
| 23 : | * | ||
| 24 : | */ | ||
| 25 : | // These are the names of all fields we fetch data from | ||
| 26 : | $fieldnames = "`product_name`,`products_per_row`,`category_browsepage`,`category_flypage`,`#__{sc}_category`.`category_id`, | ||
| 27 : | `#__{sc}_product`.`product_id`,`product_full_image`,`product_thumb_image`,`product_s_desc`,`product_parent_id`,`product_publish`,`product_in_stock`,`product_sku`, `product_url`, | ||
| 28 : | `product_weight`,`product_weight_uom`,`product_length`,`product_width`,`product_height`,`product_lwh_uom`,`product_in_stock`,`product_available_date`,`product_availability`,`#__{sc}_product`.`mdate`, `#__{sc}_product`.`cdate`"; | ||
| 29 : | $count_name = "COUNT(DISTINCT `#__{sc}_product`.`product_sku`) as num_rows"; | ||
| 30 : | $table_names = '`#__{sc}_product`, `#__{sc}_category`, `#__{sc}_product_category_xref`,`#__{sc}_shopper_group`'; | ||
| 31 : | |||
| 32 : | $join_array = array( 'LEFT JOIN `#__{sc}_product_price` ON `#__{sc}_product`.`product_id` = `#__{sc}_product_price`.`product_id`' ); | ||
| 33 : | $where_clause = array(); | ||
| 34 : | |||
| 35 : | switch( $orderby ) { | ||
| 36 : | case 'product_name': | ||
| 37 : | $orderbyField = '`#__{sc}_product`.`product_name`'; break; | ||
| 38 : | case 'product_price': | ||
| 39 : | $orderbyField = '`#__{sc}_product_price`.`product_price`'; break; | ||
| 40 : | case 'product_sku': | ||
| 41 : | $orderbyField = '`#__{sc}_product`.`product_sku`'; break; | ||
| 42 : | case 'product_cdate': | ||
| 43 : | $orderbyField = '`#__{sc}_product`.`cdate`'; break; | ||
| 44 : | default: | ||
| 45 : | $orderbyField = '`#__{sc}_product`.`product_name`'; break; | ||
| 46 : | } | ||
| 47 : | |||
| 48 : | |||
| 49 : | // Filter Products by Category | ||
| 50 : | $where_clause[] = "`#__{sc}_product_category_xref`.`product_id`=`#__{sc}_product`.`product_id`"; | ||
| 51 : | $where_clause[] = "`#__{sc}_product_category_xref`.`category_id`=`#__{sc}_category`.`category_id`"; | ||
| 52 : | |||
| 53 : | if( $category_id ) { | ||
| 54 : | $where_clause[] = "`#__{sc}_product_category_xref`.`category_id`=".$category_id; | ||
| 55 : | } | ||
| 56 : | if( strtoupper(mosGetParam($_REQUEST, 'featured', 'N' )) == 'Y' ) { | ||
| 57 : | // Filter all except Featured Products (="on special") | ||
| 58 : | $where_clause[] = '`#__{sc}_product`.`product_special`=\'Y\''; | ||
| 59 : | } | ||
| 60 : | if( strtoupper(mosGetParam($_REQUEST, 'discounted', 'N' )) == 'Y' ) { | ||
| 61 : | // Filter all except Discounted Products | ||
| 62 : | $where_clause[] = '`#__{sc}_product`.`product_discount_id` > 0'; | ||
| 63 : | } | ||
| 64 : | // This is the "normal" search | ||
| 65 : | if( !empty($keyword) ) { | ||
| 66 : | $sq = "("; | ||
| 67 : | $keywords = explode( " ", $keyword, 10 ); | ||
| 68 : | $numKeywords = count( $keywords ); | ||
| 69 : | $i = 1; | ||
| 70 : | foreach( $keywords as $searchstring ) { | ||
| 71 : | $searchstring = trim( stripslashes($searchstring) ); | ||
| 72 : | if( !empty( $searchstring )) { | ||
| 73 : | if( $searchstring[0] == "\"" || $searchstring[0]=="'" ) { | ||
| 74 : | $searchstring[0] = " "; | ||
| 75 : | } | ||
| 76 : | if( $searchstring[strlen($searchstring)-1] == "\"" || $searchstring[strlen($searchstring)-1]=="'" ) { | ||
| 77 : | $searchstring[strlen($searchstring)-1] = " "; | ||
| 78 : | } | ||
| 79 : | $searchstring = $db_browse->getEscaped( $searchstring ); | ||
| 80 : | $sq .= "\n (`#__{sc}_product`.`product_name` LIKE '%$searchstring%' OR "; | ||
| 81 : | $sq .= "\n `#__{sc}_product`.`product_sku` LIKE '%$searchstring%' OR "; | ||
| 82 : | $sq .= "\n `#__{sc}_product`.`product_s_desc` LIKE '%$searchstring%' OR "; | ||
| 83 : | $sq .= "\n `#__{sc}_product`.`product_desc` LIKE '%$searchstring%') "; | ||
| 84 : | } | ||
| 85 : | if( $i++ < $numKeywords ) { | ||
| 86 : | $sq .= "\n AND "; | ||
| 87 : | } | ||
| 88 : | } | ||
| 89 : | $sq .= ")"; | ||
| 90 : | $where_clause[] = $sq; | ||
| 91 : | } | ||
| 92 : | // This is the "advanced" search, filter by Keyword1 and Keyword2 | ||
| 93 : | elseif( !empty($keyword1) ) { | ||
| 94 : | $sq = "("; | ||
| 95 : | if ($search_limiter=="name") { | ||
| 96 : | $sq .= "\n `#__{sc}_product`.`product_name` LIKE '%$keyword1%' "; | ||
| 97 : | } | ||
| 98 : | elseif ($search_limiter=="cp") { | ||
| 99 : | $sq .= "\n `#__{sc}_product`.`product_url` LIKE '%$keyword1%' "; | ||
| 100 : | } | ||
| 101 : | elseif ($search_limiter=="desc") { | ||
| 102 : | $sq .= "\n `#__{sc}_product`.`product_s_desc` LIKE '%$keyword1%' OR "; | ||
| 103 : | $sq .= "\n `#__{sc}_product`.`product_desc` LIKE '%$keyword1%'"; | ||
| 104 : | } | ||
| 105 : | else { | ||
| 106 : | $sq .= "\n `#__{sc}_product`.`product_name` LIKE '%$keyword1%' OR "; | ||
| 107 : | $sq .= "\n `#__{sc}_product`.`product_url` LIKE '%$keyword1%' OR "; | ||
| 108 : | $sq .= "\n `#__{sc}_category`.`category_name` LIKE '%$keyword1%' OR "; | ||
| 109 : | $sq .= "\n `#__{sc}_product`.`product_sku` LIKE '%$keyword1%' OR "; | ||
| 110 : | $sq .= "\n `#__{sc}_product`.`product_s_desc` LIKE '%$keyword1%' OR "; | ||
| 111 : | $sq .= "\n `#__{sc}_product`.`product_desc` LIKE '%$keyword1%'"; | ||
| 112 : | } | ||
| 113 : | $sq .= ") "; | ||
| 114 : | /*** KEYWORD 2 TO REFINE THE SEARCH ***/ | ||
| 115 : | if ( !empty($keyword2) ) { | ||
| 116 : | $sq .= "\n $search_op ("; | ||
| 117 : | if ($search_limiter=="name") { | ||
| 118 : | $sq .= "\n `#__{sc}_product`.product_name LIKE '%$keyword2%' "; | ||
| 119 : | } | ||
| 120 : | elseif ($search_limiter=="cp") { | ||
| 121 : | $sq .= "\n `#__{sc}_product`.product_url LIKE '%$keyword2%' "; | ||
| 122 : | } | ||
| 123 : | elseif ($search_limiter=="desc") { | ||
| 124 : | $sq .= "\n `#__{sc}_product`.`product_s_desc` LIKE '%$keyword2%' OR "; | ||
| 125 : | $sq .= "\n `#__{sc}_product`.`product_desc` LIKE '%$keyword2%'"; | ||
| 126 : | } | ||
| 127 : | else { | ||
| 128 : | $sq .= "\n `#__{sc}_product`.`product_name` LIKE '%$keyword2%' OR "; | ||
| 129 : | $sq .= "\n `#__{sc}_product`.`product_url` LIKE '%$keyword2%' OR "; | ||
| 130 : | $sq .= "\n `#__{sc}_category`.`category_name` LIKE '%$keyword2%' OR "; | ||
| 131 : | $sq .= "\n `#__{sc}_product`.`product_sku` LIKE '%$keyword2%' OR "; | ||
| 132 : | $sq .= "\n `#__{sc}_product`.`product_s_desc` LIKE '%$keyword2%' OR "; | ||
| 133 : | $sq .= "\n `#__{sc}_product`.`product_desc` LIKE '%$keyword2%'"; | ||
| 134 : | } | ||
| 135 : | $sq .= "\n ) "; | ||
| 136 : | } | ||
| 137 : | $where_clause[] = $sq; | ||
| 138 : | } | ||
| 139 : | |||
| 140 : | |||
| 141 : | /*** GET ALL PUBLISHED PRODUCTS FROM THAT MANUFACTURER ***/ | ||
| 142 : | if (!empty($manufacturer_id)) { | ||
| 143 : | $table_names .= ',`#__{sc}_product_mf_xref`'; | ||
| 144 : | $where_clause[] = "manufacturer_id='".$manufacturer_id."'"; | ||
| 145 : | $where_clause[] = "`#__{sc}_product`.`product_id`=`#__{sc}_product_mf_xref`.`product_id` "; | ||
| 146 : | |||
| 147 : | } | ||
| 148 : | // Filter Products by Product Type | ||
| 149 : | if (!empty($product_type_id)) { | ||
| 150 : | require_once (CLASSPATH."ps_product_type.php"); | ||
| 151 : | $ps_product_type = new ps_product_type(); | ||
| 152 : | |||
| 153 : | // list parameters: | ||
| 154 : | $q = "SELECT `parameter_name`, `parameter_type` FROM `#__{sc}_product_type_parameter` WHERE `product_type_id`='$product_type_id'"; | ||
| 155 : | $db_browse->query($q); | ||
| 156 : | |||
| 157 : | /*** GET ALL PUBLISHED PRODUCT WHICH MATCH PARAMETERS ***/ | ||
| 158 : | $join_array[] = "LEFT JOIN `#__{sc}_product_type_$product_type_id` ON `#__{sc}_product`.`product_id` = `#__{sc}_product_type_$product_type_id`.`product_id` "; | ||
| 159 : | $join_array[] = "LEFT JOIN `#__{sc}_product_product_type_xref` ON `#__{sc}_product`.`product_id` = `#__{sc}_product_product_type_xref`.`product_id` "; | ||
| 160 : | $where_clause[] = "`#__{sc}_product_product_type_xref`.`product_type_id`=$product_type_id "; | ||
| 161 : | |||
| 162 : | // find by parameters | ||
| 163 : | while ($db_browse->next_record()) { | ||
| 164 : | $parameter_name = $db_browse->f("parameter_name"); | ||
| 165 : | $item_name = "product_type_$product_type_id"."_".$parameter_name; | ||
| 166 : | $get_item_value = mosgetparam($_REQUEST, $item_name, ""); | ||
| 167 : | $get_item_value_comp = mosgetparam($_REQUEST, $item_name."_comp", ""); | ||
| 168 : | |||
| 169 : | if (is_array($get_item_value) ? count($get_item_value) : strlen($get_item_value) ) { | ||
| 170 : | // comparison | ||
| 171 : | switch ($get_item_value_comp) { | ||
| 172 : | case "lt": $comp = " < "; break; | ||
| 173 : | case "le": $comp = " <= "; break; | ||
| 174 : | case "eq": $comp = " <=> "; break; | ||
| 175 : | case "ge": $comp = " >= "; break; | ||
| 176 : | case "gt": $comp = " > "; break; | ||
| 177 : | case "ne": $comp = " <> "; break; | ||
| 178 : | case "texteq": | ||
| 179 : | $comp = " <=> "; | ||
| 180 : | break; | ||
| 181 : | case "like": | ||
| 182 : | $comp = " LIKE "; | ||
| 183 : | $get_item_value = "%".$get_item_value."%"; | ||
| 184 : | break; | ||
| 185 : | case "notlike": | ||
| 186 : | $comp = "COALESCE(`".$parameter_name."` NOT LIKE '%".$get_item_value."%',1)"; | ||
| 187 : | $parameter_name = ""; | ||
| 188 : | $get_item_value = ""; | ||
| 189 : | break; | ||
| 190 : | case "in": // Multiple section List of values | ||
| 191 : | $comp = " IN ('".join("','",$get_item_value)."')"; | ||
| 192 : | $get_item_value = ""; | ||
| 193 : | break; | ||
| 194 : | case "fulltext": | ||
| 195 : | $comp = "MATCH (`".$parameter_name."`) AGAINST "; | ||
| 196 : | $parameter_name = ""; | ||
| 197 : | $get_item_value = "('".$get_item_value."')"; | ||
| 198 : | break; | ||
| 199 : | case "find_in_set": | ||
| 200 : | $comp = "FIND_IN_SET('$get_item_value',`$parameter_name`)"; | ||
| 201 : | $parameter_name = ""; | ||
| 202 : | $get_item_value = ""; | ||
| 203 : | break; | ||
| 204 : | case "find_in_set_all": | ||
| 205 : | case "find_in_set_any": | ||
| 206 : | $comp = array(); | ||
| 207 : | foreach($get_item_value as $value) { | ||
| 208 : | array_push($comp,"FIND_IN_SET('$value',`$parameter_name`)"); | ||
| 209 : | } | ||
| 210 : | $comp = "(" . join($get_item_value_comp == "find_in_set_all"?" AND ":" OR ", $comp) . ")"; | ||
| 211 : | $parameter_name = ""; | ||
| 212 : | $get_item_value = ""; | ||
| 213 : | break; | ||
| 214 : | } | ||
| 215 : | switch ($db_browse->f("parameter_type")) { | ||
| 216 : | case "D": $get_item_value = "CAST('".$get_item_value."' AS DATETIME)"; break; | ||
| 217 : | case "A": $get_item_value = "CAST('".$get_item_value."' AS DATE)"; break; | ||
| 218 : | case "M": $get_item_value = "CAST('".$get_item_value."' AS TIME)"; break; | ||
| 219 : | case "C": $get_item_value = "'".substr($get_item_value,0,1)."'"; break; | ||
| 220 : | default: | ||
| 221 : | if( strlen($get_item_value) ) $get_item_value = "'".$get_item_value."'"; | ||
| 222 : | } | ||
| 223 : | if( !empty($parameter_name) ) $parameter_name = "`".$parameter_name."`"; | ||
| 224 : | $where_clause[] = $parameter_name.$comp.$get_item_value." "; | ||
| 225 : | } | ||
| 226 : | } | ||
| 227 : | $item_name = "price"; | ||
| 228 : | $get_item_value = mosgetparam($_REQUEST, $item_name, ""); | ||
| 229 : | $get_item_value_comp = mosgetparam($_REQUEST, $item_name."_comp", ""); | ||
| 230 : | // search by price | ||
| 231 : | if (!empty($get_item_value)) { | ||
| 232 : | // comparison | ||
| 233 : | switch ($get_item_value_comp) { | ||
| 234 : | case "lt": $comp = " < "; break; | ||
| 235 : | case "le": $comp = " <= "; break; | ||
| 236 : | case "eq": $comp = " = "; break; | ||
| 237 : | case "ge": $comp = " >= "; break; | ||
| 238 : | case "gt": $comp = " > "; break; | ||
| 239 : | case "ne": $comp = " <> "; break; | ||
| 240 : | } | ||
| 241 : | $where_clause[] = "( ISNULL(product_price) OR product_price".$comp.$get_item_value." ) "; | ||
| 242 : | $auth = $_SESSION['auth']; | ||
| 243 : | // get Shopper Group | ||
| 244 : | $sgq = "( ISNULL(`#__{sc}_product_price`.`shopper_group_id`) OR `#__{sc}_product_price`.`shopper_group_id` IN ("; | ||
| 245 : | $comma=""; | ||
| 246 : | if ($auth["user_id"] != 0) { // find user's Shopper Group | ||
| 247 : | $q2 = "SELECT `shopper_group_id` FROM `#__{sc}_shopper_vendor_xref` WHERE `user_id`='".$auth["user_id"]."'"; | ||
| 248 : | $db_browse->query($q2); | ||
| 249 : | while ($db_browse->next_record()) { | ||
| 250 : | $sgq .= $comma.$db_browse->f("shopper_group_id"); | ||
| 251 : | $comma=","; | ||
| 252 : | } | ||
| 253 : | } | ||
| 254 : | // find default Shopper Groups | ||
| 255 : | $q2 = "SELECT `shopper_group_id` FROM `#__{sc}_shopper_group` WHERE `default` = 1"; | ||
| 256 : | $db_browse->query($q2); | ||
| 257 : | while ($db_browse->next_record()) { | ||
| 258 : | $sgq .= $comma.$db_browse->f("shopper_group_id"); | ||
| 259 : | $comma=","; | ||
| 260 : | } | ||
| 261 : | $sgq .= "\n )) "; | ||
| 262 : | $where_clause[] = $sgq; | ||
| 263 : | } | ||
| 264 : | |||
| 265 : | } | ||
| 266 : | |||
| 267 : | ////////////////////////////////// | ||
| 268 : | // ASSEMBLE THE QUERY | ||
| 269 : | ////////////////////////////////// | ||
| 270 : | $list = "SELECT DISTINCT $fieldnames FROM ($table_names) "; | ||
| 271 : | $count = "SELECT $count_name FROM ($table_names) "; | ||
| 272 : | |||
| 273 : | if( $perm->is_registered_customer($my->id) ) { | ||
| 274 : | $where_clause[] = "(`#__{sc}_product`.`product_id`=`#__{sc}_product_price`.`product_id` OR `#__{sc}_product_price`.`product_id` IS NULL) "; | ||
| 275 : | $join_array[] = 'LEFT JOIN `#__{sc}_shopper_vendor_xref` ON (`#__{sc}_shopper_vendor_xref`.`user_id` ='.$my->id.' AND `#__{sc}_shopper_vendor_xref`.`shopper_group_id`=`#__{sc}_shopper_group`.`shopper_group_id`)'; | ||
| 276 : | } | ||
| 277 : | else { | ||
| 278 : | $where_clause[] = "((`#__{sc}_product`.`product_id`=`#__{sc}_product_price`.`product_id` AND `#__{sc}_shopper_group`.`shopper_group_id`=`#__{sc}_product_price`.`shopper_group_id`) OR `#__{sc}_product_price`.`product_id` IS NULL) "; | ||
| 279 : | $where_clause[] = '`#__{sc}_shopper_group`.`default` = 1'; | ||
| 280 : | } | ||
| 281 : | $where_clause[] = "`product_parent_id`=0"; | ||
| 282 : | if( !$perm->check("admin,storeadmin") ) { | ||
| 283 : | $where_clause[] = "`product_publish`='Y' "; | ||
| 284 : | if( CHECK_STOCK && PSHOP_SHOW_OUT_OF_STOCK_PRODUCTS != "1") { | ||
| 285 : | $where_clause[] = 'product_in_stock > 0'; | ||
| 286 : | } | ||
| 287 : | } | ||
| 288 : | $q = implode("\n", $join_array ).' WHERE '. implode("\n AND ", $where_clause ); | ||
| 289 : | $count .= $q; | ||
| 290 : | |||
| 291 : | $q .= "\n GROUP BY `#__{sc}_product`.`product_sku` "; | ||
| 292 : | $q .= "\n ORDER BY $orderbyField $DescOrderBy"; | ||
| 293 : | $list .= $q . " LIMIT $limitstart, " . $limit; | ||
| 294 : | |||
| 295 : | // BACK TO shop.browse.php ! | ||
| 296 : | ?> |
| ViewVC Help | |
| Powered by ViewVC 1.0.0 |
Web Hosting provided by Network Redux.