/** 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;
}
