/** wpsc list subcategories function used to get an array of all the subcategories of a category. */ function wpsc_list_subcategories($category_id = null) { global $wpdb, $category_data; $category_list = $wpdb->get_col($wpdb->prepare("SELECT `id` FROM `" . WPSC_TABLE_PRODUCT_CATEGORIES . "` WHERE `category_parent` = %d", $category_id)); if ($category_list != null) { foreach ($category_list as $subcategory_id) { $category_list = array_merge((array) $category_list, (array) wpsc_list_subcategories($subcategory_id)); } } return $category_list; }
function &get_products() { global $wpdb, $wp_query; do_action_ref_array('pre_get_products', array(&$this)); if ($this->query_vars['category_url_name'] != '') { $category_data = $wpdb->get_row("SELECT `id`, `image_height`, `image_width` FROM `" . WPSC_TABLE_PRODUCT_CATEGORIES . "` WHERE `active`='1' AND `nice-name` = '{$this->query_vars['category_url_name']}' LIMIT 1", ARRAY_A); $this->query_vars['category_id'] = $category_data['id']; $this->category = $this->query_vars['category_id']; } else { if ($this->query_vars['category_id'] > 0) { $category_data = $wpdb->get_row("SELECT `image_height`, `image_width` FROM `" . WPSC_TABLE_PRODUCT_CATEGORIES . "` WHERE `active`='1' AND `id` = '{$this->query_vars['category_id']}' LIMIT 1", ARRAY_A); } } // Show subcategory products on parent category page? $show_subcatsprods_in_cat = get_option('show_subcatsprods_in_cat'); $this->category_id_list = array($this->query_vars['category_id']); if ($show_subcatsprods_in_cat && $this->query_vars['category_id'] > 0) { $this->category_id_list = array_merge((array) $this->category_id_list, (array) wpsc_list_subcategories($this->query_vars['category_id'])); } //exit('Here:<pre>'.print_r($category_id_list, true).'</pre>'); if (is_array($category_data)) { $this->category_product['image_height'] = $category_data['image_height']; $this->category_product['image_width'] = $category_data['image_width']; } if ($this->query_vars['product_url_name'] != null) { $product_id = $wpdb->get_var("SELECT `product_id` FROM `" . WPSC_TABLE_PRODUCTMETA . "` WHERE `meta_key` IN ( 'url_name' ) AND `meta_value` IN ( '" . stripslashes($this->query_vars['product_url_name']) . "' ) ORDER BY `product_id` DESC LIMIT 1"); } else { $product_id = absint($this->query_vars['product_id']); } if ($product_id > 0) { $product_list = $wpdb->get_results("SELECT * FROM `" . WPSC_TABLE_PRODUCT_LIST . "` WHERE `id`='" . (int) $product_id . "' AND `active` IN('1') AND `publish` IN('1') LIMIT 1", ARRAY_A); } if (isset($_SESSION['price_range']) && isset($_GET['range'])) { if (is_numeric($_GET['range']) || isset($_SESSION['price_range'])) { $price_ranges = $_SESSION['price_range']; $selected_price_point = absint($_GET['range']); $next_price_point = $selected_price_point + 1; //echo "<pre>".print_r($ranges,true)."</pre>"; $product_range_sql_parts = array(); $variation_sql_parts = array(); $product_sql_parts = array(); if (isset($price_ranges[$selected_price_point])) { $product_range_sql_parts[] = "(`price` - `special_price`) >= '" . absint($price_ranges[$selected_price_point]) . "'"; $variation_sql_parts[] = "`price` >= '" . absint($price_ranges[$selected_price_point]) . "'"; if (isset($price_ranges[$next_price_point])) { $product_range_sql_parts[] = "(`price` - `special_price`) < '" . absint($price_ranges[$next_price_point]) . "'"; $variation_sql_parts[] = "`price` < '" . absint($price_ranges[$next_price_point]) . "'"; } $variation_product_ids = (array) $wpdb->get_col("SELECT DISTINCT `product_id` FROM `" . WPSC_TABLE_VARIATION_PROPERTIES . "` WHERE " . implode(" AND ", $variation_sql_parts) . ""); if (count($variation_product_ids) > 0) { $product_sql_parts[] = "( (" . implode(" AND ", $product_range_sql_parts) . ") OR `id` IN('" . implode("', '", $variation_product_ids) . "') )"; } else { $product_sql_parts += $product_range_sql_parts; } } $product_sql_parts[] = "`active` IN ('1')"; $product_sql_parts[] = "`publish` IN('1')"; $range_sql = "SELECT * FROM `" . WPSC_TABLE_PRODUCT_LIST . "` WHERE " . implode(" AND ", $product_sql_parts) . ""; $product_list = $wpdb->get_results($range_sql, ARRAY_A); } } //exit('Here:<pre>'.print_r($this->query_vars, true).'</pre>'); if (count($product_list) > 0 && !isset($_GET['range'])) { // if is a single product $this->is_single = true; $this->products = $product_list; $this->category = $this->query_vars['category_id']; } elseif (count($product_list) > 0 && isset($_GET['range'])) { $this->is_single = false; $this->products = $product_list; $this->category = $this->query_vars['category_id']; } else { // Otherwise //if we are using pages, how many items per page and what page? if (get_option('use_pagination') == 1) { $products_per_page = $this->query_vars['number_per_page']; if ($this->query_vars['page'] > 0) { $startnum = ($this->query_vars['page'] - 1) * $products_per_page; } else { $startnum = 0; } } else { $startnum = 0; } // search section is done here if (function_exists('gold_shpcrt_search_sql') && $_GET['product_search'] != '') { $search_sql = gold_shpcrt_search_sql(); if ($search_sql != '') { // this cannot currently list products that are associated with no categories $rowcount = $wpdb->get_var("SELECT COUNT( DISTINCT `" . WPSC_TABLE_PRODUCT_LIST . "`.`id`) AS `count` FROM `" . WPSC_TABLE_PRODUCT_LIST . "`,`" . WPSC_TABLE_ITEM_CATEGORY_ASSOC . "` WHERE `" . WPSC_TABLE_PRODUCT_LIST . "`.`publish`='1' AND `" . WPSC_TABLE_PRODUCT_LIST . "`.`active`='1' AND `" . WPSC_TABLE_PRODUCT_LIST . "`.`id` = `" . WPSC_TABLE_ITEM_CATEGORY_ASSOC . "`.`product_id` AND `" . WPSC_TABLE_PRODUCT_LIST . "`.donation = '0' {$search_sql}"); if (isset($_SESSION['item_per_page'])) { $products_per_page = $_SESSION['item_per_page']; } //exit($products_per_page); if (!is_numeric($products_per_page) || $products_per_page < 1) { $products_per_page = $rowcount; } if ($startnum >= $rowcount) { $startnum = 0; } $sql = "SELECT DISTINCT `" . WPSC_TABLE_PRODUCT_LIST . "`.* FROM `" . WPSC_TABLE_PRODUCT_LIST . "`,`" . WPSC_TABLE_ITEM_CATEGORY_ASSOC . "` WHERE `" . WPSC_TABLE_PRODUCT_LIST . "`.`publish`='1' AND `" . WPSC_TABLE_PRODUCT_LIST . "`.`active`='1' AND `" . WPSC_TABLE_PRODUCT_LIST . "`.`id` = `" . WPSC_TABLE_ITEM_CATEGORY_ASSOC . "`.`product_id` {$no_donations_sql} {$search_sql} ORDER BY `" . WPSC_TABLE_PRODUCT_LIST . "`.`special` DESC LIMIT {$startnum}, {$products_per_page}"; } } else { if ($wp_query->query_vars['ptag'] != null || $_GET['ptag'] != null) { // search by tags is done here if ($wp_query->query_vars['ptag'] != null) { $tag = $wp_query->query_vars['ptag']; } else { $tag = $_GET['ptag']; } $results = $wpdb->get_results("SELECT * FROM `{$wpdb->terms}` WHERE slug='{$tag}'"); $term_id = $results[0]->term_id; $results = $wpdb->get_results("SELECT * FROM `{$wpdb->term_taxonomy}` WHERE term_id = '" . $term_id . "' AND taxonomy='product_tag'"); $taxonomy_id = $results[0]->term_taxonomy_id; $results = $wpdb->get_results("SELECT * FROM `{$wpdb->term_relationships}` WHERE term_taxonomy_id = '" . $taxonomy_id . "'"); foreach ($results as $result) { $product_ids[] = $result->object_id; } $product_id = implode(",", $product_ids); $sql = "SELECT * FROM " . WPSC_TABLE_PRODUCT_LIST . " WHERE id IN (" . $product_id . ") AND `publish` IN('1') AND `active` IN('1')"; //Transom - added publish & active } else { // select by category is done here if (is_numeric($this->query_vars['category_id']) && $this->query_vars['category_id'] > 0) { /* * The reason this is so complicated is because of the product ordering, it is done by category/product association * If you can see a way of simplifying it and speeding it up, then go for it. */ $rowcount = $wpdb->get_var("SELECT COUNT( DISTINCT `" . WPSC_TABLE_PRODUCT_LIST . "`.`id`) AS `count` FROM `" . WPSC_TABLE_PRODUCT_LIST . "` LEFT JOIN `" . WPSC_TABLE_ITEM_CATEGORY_ASSOC . "` ON `" . WPSC_TABLE_PRODUCT_LIST . "`.`id` = `" . WPSC_TABLE_ITEM_CATEGORY_ASSOC . "`.`product_id` WHERE `" . WPSC_TABLE_PRODUCT_LIST . "`.`publish`='1' AND `" . WPSC_TABLE_PRODUCT_LIST . "`.`active` = '1' AND `" . WPSC_TABLE_ITEM_CATEGORY_ASSOC . "`.`category_id` IN ('" . implode("','", $this->category_id_list) . "') {$no_donations_sql}"); if (!is_numeric($products_per_page) || $products_per_page < 1) { $products_per_page = $rowcount; } if ($startnum >= $rowcount) { $startnum = 0; } if ($this->query_vars['sort_order'] == 'DESC') { $order = 'DESC'; } else { $order = 'ASC'; } // Invert this for alphabetical ordering. if (get_option('wpsc_sort_by') == 'name') { $order_by = "`products`.`name` {$order}"; } else { if (get_option('wpsc_sort_by') == 'price') { $order_by = "`products`.`price` {$order}"; } else { //$order = 'ASC'; if ($order == 'ASC') { $product_id_order = 'DESC'; } else { $product_id_order = 'ASC'; } $order_by = " `category`.`name`, `order_state` DESC,`order`.`order` {$order}, `products`.`id` {$product_id_order}"; //$order_by = " `order_state` DESC, `products`.`id` $product_id_order,`order`.`order` $order"; } } $sql = "SELECT DISTINCT `products`. * , `category`.`name` AS `category` , `cat_assoc`.`category_id` , `order`.`order` , IF( ISNULL( `order`.`order` ) , 0, 1 ) AS `order_state`\n\t\t\t\tFROM `" . WPSC_TABLE_PRODUCT_LIST . "` AS `products`\n\t\t\t\tLEFT JOIN `" . WPSC_TABLE_ITEM_CATEGORY_ASSOC . "` AS `cat_assoc`\n\t\t\t\t\tON `products`.`id` = `cat_assoc`.`product_id`\n\t\t\t\tLEFT JOIN `" . WPSC_TABLE_PRODUCT_CATEGORIES . "` AS `category`\n\t\t\t\t\tON `cat_assoc`.`category_id` = `category`.`id`\n\t\t\t\tLEFT JOIN `" . WPSC_TABLE_PRODUCT_ORDER . "` AS `order`\n\t\t\t\t\tON (\n\t\t\t\t\t\t(`products`.`id` = `order`.`product_id`)\n\t\t\t\t\t\tAND\n\t\t\t\t\t\t(`cat_assoc`.`category_id` = `order`.`category_id`)\n\t\t\t\t\t)\n\t\t\t\tWHERE `products`.`publish` = '1'\n\t\t\t\tAND `products`.`active` = '1'\n\t\t\t\tAND `cat_assoc`.`category_id` IN ( '" . implode("','", $this->category_id_list) . "' ) {$no_donations_sql}\n\t\t\t\tGROUP BY `products`.`id`\n\t\t\t\tORDER BY {$order_by} LIMIT {$startnum}, {$products_per_page}"; } else { if ($this->query_vars['sort_order'] == 'DESC') { $order = 'DESC'; } else { $order = 'ASC'; } if (get_option('wpsc_sort_by') == 'name') { $order_by = "`" . WPSC_TABLE_PRODUCT_LIST . "`.`name` {$order}"; } else { if (get_option('wpsc_sort_by') == 'price') { $order_by = "`" . WPSC_TABLE_PRODUCT_LIST . "`.`price` {$order}"; } elseif (get_option('wpsc_sort_by') == 'dragndrop') { $order_by = "`" . WPSC_TABLE_PRODUCT_ORDER . "`.`order` ASC"; } else { if ($order == 'ASC') { $order = 'DESC'; } else { $order = 'ASC'; } $order_by = "`" . WPSC_TABLE_PRODUCT_LIST . "`.`id` {$order}"; } } $rowcount = $wpdb->get_var("SELECT COUNT( DISTINCT `" . WPSC_TABLE_PRODUCT_LIST . "`.`id`) AS `count` FROM `" . WPSC_TABLE_PRODUCT_LIST . "`,`" . WPSC_TABLE_ITEM_CATEGORY_ASSOC . "` WHERE `" . WPSC_TABLE_PRODUCT_LIST . "`.`publish`='1' AND `" . WPSC_TABLE_PRODUCT_LIST . "`.`active`='1' AND `" . WPSC_TABLE_PRODUCT_LIST . "`.`id` = `" . WPSC_TABLE_ITEM_CATEGORY_ASSOC . "`.`product_id` {$no_donations_sql} {$group_sql}"); if (!is_numeric($products_per_page) || $products_per_page < 1) { $products_per_page = $rowcount; } if ($startnum >= $rowcount) { $startnum = 0; } $sql = "SELECT DISTINCT `" . WPSC_TABLE_PRODUCT_LIST . "`.*, `" . WPSC_TABLE_PRODUCT_ORDER . "`.`order` FROM `" . WPSC_TABLE_PRODUCT_LIST . "`\n\t\t\t\t LEFT JOIN `" . WPSC_TABLE_ITEM_CATEGORY_ASSOC . "` ON `" . WPSC_TABLE_PRODUCT_LIST . "`.`id` = `" . WPSC_TABLE_ITEM_CATEGORY_ASSOC . "`.`product_id`\n\t\t\t\t LEFT JOIN `" . WPSC_TABLE_PRODUCT_ORDER . "` ON `" . WPSC_TABLE_PRODUCT_LIST . "`.`id` = `" . WPSC_TABLE_PRODUCT_ORDER . "`.`product_id`\n\t\t\t\t WHERE `" . WPSC_TABLE_PRODUCT_LIST . "`.`publish`='1' AND `" . WPSC_TABLE_PRODUCT_LIST . "`.`active`='1' {$no_donations_sql} {$group_sql} ORDER BY `" . WPSC_TABLE_PRODUCT_LIST . "`.`special`, {$order_by} LIMIT {$startnum}, {$products_per_page}"; if (get_option('wpsc_sort_by') == 'dragndrop') { $sql = "SELECT `products`.* FROM `" . WPSC_TABLE_PRODUCT_LIST . "` AS `products` LEFT JOIN `" . WPSC_TABLE_PRODUCT_ORDER . "` AS `order` ON `products`.`id`= `order`.`product_id` WHERE `products`.`active`='1' AND `products`.`publish`='1' AND `order`.`category_id`='0' {$search_sql} ORDER BY `order`.`order`"; } } } } //exit($sql); //echo "{$sql}"; $this->category = $this->query_vars['category_id']; $this->products = $wpdb->get_results($sql, ARRAY_A); // exit('<pre>'.print_r($this->products,true).'</pre>'); $this->total_product_count = $rowcount; if ($rowcount > $products_per_page) { if ($products_per_page > 0) { $pages = ceil($rowcount / $products_per_page); } else { $pages = 1; } } } if (get_option('permalink_structure') != '') { $seperator = "?"; } else { $seperator = "&"; } $product_view_url = wpsc_category_url($this->category); if (!is_numeric($_GET['category']) && $_GET['product_search'] != '') { $product_view_url = add_query_arg('product_search', $_GET['product_search'], $product_view_url); //$product_view_url_not_used .= "product_search=".$_GET['product_search']."&"."view_type=".$_GET['view_type']."&"."item_per_page=".$_GET['item_per_page']."&"; } if (!is_numeric($_GET['category']) && $_GET['item_per_page'] > 0) { $product_view_url = add_query_arg('item_per_page', $_GET['item_per_page'], $product_view_url); } if (isset($_GET['order']) && $_GET['order'] == 'ASC' || $_GET['order'] == 'DESC') { $product_view_url = add_query_arg('order', $_GET['order'], $product_view_url); } if (isset($_GET['view_type']) && $_GET['view_type'] == 'default' || $_GET['view_type'] == 'grid') { $product_view_url = add_query_arg('view_type', $_GET['view_type'], $product_view_url); } for ($i = 1; $i <= $pages; $i++) { if ($this->query_vars['page'] == $i || $this->query_vars['page'] <= $i && $i <= 1) { if ($_GET['view_all'] != 'true') { $selected = true; } } else { $selected = false; } if (get_option('permalink_structure')) { //if() $page_url = wpsc_category_url($this->category, true) . "page/{$i}/"; } else { $page_url = add_query_arg('page_number', $i, $product_view_url); } $this->pages[$i - 1]['number'] = $i; $this->pages[$i - 1]['url'] = $page_url; $this->pages[$i - 1]['selected'] = $selected; } $this->page_count = count($this->pages); //if ( !$q['suppress_filters'] ) $this->products = apply_filters('the_products', $this->products); $this->product_count = count($this->products); if ($this->product_count > 0) { $this->product = $this->products[0]; } // get the breadcrumbs $this->get_breadcrumbs(); return $this->products; }