Example #1
0
	/**
	 * Loads a list of products for the category
	 * 
	 * @since 1.0
	 * @version 1.1
	 *
	 * @param array $loading Loading options for the category
	 * @return void
	 **/
	function load_products ($loading=false) {
		global $Ecart,$wp;
		$db = DB::get();

		$catalogtable = DatabaseObject::tablename(Catalog::$table);
		$producttable = DatabaseObject::tablename(Product::$table);
		$pricetable = DatabaseObject::tablename(Price::$table);
		$discounttable = DatabaseObject::tablename(Discount::$table);
		$promotable = DatabaseObject::tablename(Promotion::$table);
		$imagetable = DatabaseObject::tablename(ProductImage::$table);

		$this->paged = false;
		$this->pagination = $Ecart->Settings->get('catalog_pagination');
		$this->page = (get_query_var('paged') > 0)?get_query_var('paged'):1;

		if (empty($this->page)) $this->page = 1;

		$limit = 1000; // Hard product limit per category to keep resources "reasonable"

		if (!$loading) $loading = $this->loading;
		else $loading = array_merge($this->loading,$loading);

		if (!empty($loading['columns'])) $loading['columns'] = ", ".$loading['columns'];
		else $loading['columns'] = '';

		// Allow override for loading unpublished products
		if (isset($loading['published'])) $this->published = value_is_true($loading['published']);

		$where = array();
		if (!empty($loading['where'])) $where[] = "({$loading['where']})";

		$having = array();
		if (!empty($loading['having'])) $having[] = "({$loading['having']})";

		// Handle default WHERE clause matching this category id
		if (empty($loading['where']) && !empty($this->id))
			$where[] = "p.id in (SELECT product FROM $catalogtable WHERE (parent=$this->id AND type='category'))";

		if (!isset($loading['nostock']) && ($Ecart->Settings->get('outofstock_catalog') == "off"))
			$where[] = "p.id in (SELECT product FROM $pricetable WHERE type != 'N/A' AND inventory='off' OR (inventory='on' AND stock > 0))";
		else $where[] = "p.id in (SELECT product FROM $pricetable WHERE type != 'N/A')";

		if (!isset($loading['joins'])) $loading['joins'] = '';
		if (!empty($Ecart->Flow->Controller->browsing[$this->slug])) {
			$spectable = DatabaseObject::tablename(Spec::$table);

			$f = 1;
			$filters = "";
			foreach ($Ecart->Flow->Controller->browsing[$this->slug] as $facet => $value) {
				if (empty($value)) continue;
				$specalias = "spec".($f++);

				// Handle Number Range filtering
				$match = "";
				if (!is_array($value) &&
						preg_match('/^.*?(\d+[\.\,\d]*).*?\-.*?(\d+[\.\,\d]*).*$/',$value,$matches)) {
					if ($facet == "Price") { // Prices require complex matching on price line entries
						$min = floatvalue($matches[1]);
						$max = floatvalue($matches[2]);
						if ($matches[1] > 0) $match .= " ((onsale=0 AND (minprice >= $min OR maxprice >= $min)) OR (onsale=1 AND (minsaleprice >= $min OR maxsaleprice >= $min)))";
						if ($matches[2] > 0) $match .= (empty($match)?"":" AND ")." ((onsale=0 AND (minprice <= $max OR maxprice <= $max)) OR (onsale=1 AND (minsaleprice <= $max OR maxsaleprice <= $max)))";
					} else { // Spec-based numbers are somewhat more straightforward
						if ($matches[1] > 0) $match .= "$specalias.numeral >= {$matches[1]}";
						if ($matches[2] > 0) $match .= (empty($match)?"":" AND ")."$specalias.numeral <= {$matches[2]}";
					}
				} else $match = "$specalias.value='$value'"; // No range, direct value match

				// Use HAVING clause for filtering by pricing information
				// because of data aggregation
				if ($facet == "Price") {
					$having[] = $match;
					continue;
				}

				$loading['joins'] .= " LEFT JOIN $spectable AS $specalias ON $specalias.parent=p.id AND $specalias.context='product' AND $specalias.type='spec' AND $specalias.name='$facet'";
				$filters .= (empty($filters))?$match:" AND ".$match;
			}
			if (!empty($filters)) $where[] = $filters;

		}

		// WP TZ setting based time - (timezone offset:[PHP UTC adjusted time - MySQL UTC adjusted time])
		$now = time()."-(".(time()-date("Z",time()))."-UNIX_TIMESTAMP(UTC_TIMESTAMP()))";

		if ($this->published) $where[] = "(p.status='publish' AND $now >= UNIX_TIMESTAMP(p.publish))";
		else $where[] = "(p.status!='publish' OR $now < UNIX_TIMESTAMP(p.publish))";

		$defaultOrder = $Ecart->Settings->get('default_product_order');
		if (empty($defaultOrder)) $defaultOrder = "";
		$ordering = isset($Ecart->Flow->Controller->browsing['orderby'])?
						$Ecart->Flow->Controller->browsing['orderby']:$defaultOrder;
		if (!empty($loading['order'])) $ordering = $loading['order'];
		switch ($ordering) {
			case "bestselling":
				$purchasedtable = DatabaseObject::tablename(Purchased::$table);
				$loading['columns'] .= ',count(DISTINCT pur.id) AS sold';
				$loading['joins'] .= " LEFT JOIN $purchasedtable AS pur ON p.id=pur.product";
				$loading['order'] = "sold DESC,p.name ASC";
				break;
			case "highprice": $loading['order'] = "highprice DESC"; break;
			case "lowprice": $loading['order'] = "lowprice ASC"; break;
			case "newest": $loading['order'] = "p.publish DESC,p.name ASC"; break;
			case "oldest": $loading['order'] = "p.publish ASC,p.name ASC"; break;
			case "random": $loading['order'] = "RAND(".crc32($Ecart->Shopping->session).")"; break;
			case "title": $loading['order'] = "p.name ASC"; break;
			default:
				// Need to add the catalog table for access to category-product priorities
				if (!isset($this->smart)) {
					$loading['joins'] .= " LEFT JOIN $catalogtable AS c ON c.product=p.id AND c.parent = '$this->id'";
					$loading['order'] = "c.priority ASC,p.name ASC";
				} else $loading['order'] = "p.name ASC";
				break;
		}
		if (!empty($loading['orderby'])) $loading['order'] = $loading['orderby'];

		if (isset($loading['adjacent']) && isset($loading['product'])) {

			$product = $loading['product'];
			$field = substr($loading['order'],0,strpos($loading['order'],' '));
			$op = $loading['adjacent'] != "next"?'<':'>';

			// Flip the sort order for previous
			if ($op == '<') {
				$loading['order'] = str_replace(array('ASC','DESC'),array('DSC','ACE'),$loading['order']);
				$loading['order'] = str_replace(array('DSC','ACE'),array('DESC','ASC'),$loading['order']);
			}

			switch ($field) {
				case "sold":
					if ($product->sold() == 0) {
						$field = 'p.name';
						$target = "'".$db->escape($product->name)."'";
					} else $target = $product->sold();
					$where[] = "$field $op $target";
					break;
				case "highprice":
					if (empty($product->prices)) $product->load_data(array('prices'));
					$target = !empty($product->max['saleprice'])?$product->max['saleprice']:$product->max['price'];
					$where[] = "$target $op IF (pd.sale='on' OR pr.discount>0,pd.saleprice,pd.price) AND p.id != $product->id";
					break;
				case "lowprice":
					if (empty($product->prices)) $product->load_data(array('prices'));
					$target = !empty($product->max['saleprice'])?$product->max['saleprice']:$product->max['price'];
					$where[] = "$target $op= IF (pd.sale='on' OR pr.discount>0,pd.saleprice,pd.price) AND p.id != $product->id";
					break;
				case "p.name": $where[] = "$field $op '".$db->escape($product->name)."'"; break;
				default:
					if ($product->priority == 0) {
						$field = 'p.name';
						$target = "'".$db->escape($product->name)."'";
					} else $target = $product->priority;
					$where[] = "$field $op $target";
					break;
			}

		}

		if (!empty($having)) $loading['having'] = "HAVING ".join(" AND ",$having);
		else $loading['having'] = '';
		$loading['where'] = join(" AND ",$where);

		if (empty($loading['limit'])) {
			if ($this->pagination > 0 && is_numeric($this->page)) {
				if( !$this->pagination || $this->pagination < 0 )
					$this->pagination = $limit;
				$start = ($this->pagination * ($this->page-1));

				$loading['limit'] = "$start,$this->pagination";
			} else $loading['limit'] = $limit;
		} else $limit = (int)$loading['limit'];

		$columns = "p.*,
					img.id AS image,img.value AS imgmeta,MAX(pr.status) as promos,
					SUM(DISTINCT IF(pr.type='Percentage Off',pr.discount,0))AS percentoff,
					SUM(DISTINCT IF(pr.type='Amount Off',pr.discount,0)) AS amountoff,
					if (pr.type='Free Shipping',1,0) AS freeshipping,
					if (pr.type='Buy X Get Y Free',pr.buyqty,0) AS buyqty,
					if (pr.type='Buy X Get Y Free',pr.getqty,0) AS getqty,
					MAX(pd.price) AS maxprice,MIN(pd.price) AS minprice,
					IF(pd.sale='on',1,IF (pr.discount > 0,1,0)) AS onsale,
					MAX(pd.saleprice) as maxsaleprice,MIN(pd.saleprice) AS minsaleprice,
					IF (pd.sale='on' AND MIN(pd.saleprice) > 0,MIN(pd.saleprice),MIN(pd.price)) AS lowprice,
					IF (pd.sale='on' AND MIN(pd.saleprice) > 0,MAX(pd.saleprice),MAX(pd.price)) AS highprice,
					IF(pd.inventory='on',1,0) AS inventory,
					SUM(pd.stock) as stock";

		// Query without promotions for MySQL servers prior to 5
		if (version_compare($db->mysql,'5.0','<')) {
			$columns = "p.*,
						img.id AS image,img.value AS imgmeta,
						MAX(pd.price) AS maxprice,MIN(pd.price) AS minprice,
						IF(pd.sale='on',1,0) AS onsale,
						MAX(pd.saleprice) as maxsaleprice,MIN(pd.saleprice) AS minsaleprice,
						IF(pd.inventory='on',1,0) AS inventory,
						SUM(pd.stock) as stock";
		}

		// Handle alphabetic page requests
		if ((!isset($Ecart->Category->controls) ||
				(isset($Ecart->Category->controls) && $Ecart->Category->controls !== false)) &&
				((isset($loading['pagination']) && $loading['pagination'] == "alpha") ||
				!is_numeric($this->page))) {

			$alphanav = range('A','Z');

			$ac = "SELECT count(DISTINCT p.id) AS total,IF(LEFT(p.name,1) REGEXP '[0-9]',LEFT(p.name,1),LEFT(SOUNDEX(p.name),1)) AS letter,AVG(IF(pd.sale='on',pd.saleprice,pd.price)) as avgprice
						FROM $producttable AS p
						LEFT JOIN $pricetable AS pd ON pd.product=p.id AND pd.type != 'N/A'
						LEFT JOIN $discounttable AS dc ON dc.product=p.id AND dc.price=pd.id
						LEFT JOIN $promotable AS pr ON pr.id=dc.promo
						LEFT JOIN $imagetable AS img ON img.parent=p.id AND img.context='product' AND img.type='image' AND img.sortorder=0
						{$loading['joins']}
						WHERE {$loading['where']}
						GROUP BY letter";
			$alpha = $db->query($ac);

			$existing = current($alpha);
			if (!isset($this->alpha['0-9'])) {
				$this->alpha['0-9'] = new stdClass();
				$this->alpha['0-9']->letter = '0-9';
				$this->alpha['0-9']->total = 0;
				$this->alpha['0-9']->avg = 0;
			}
			while (is_numeric($existing->letter)) {
				$this->alpha['0-9']->total += $existing->total;
				$this->alpha['0-9']->avg = ($this->alpha['0-9']->avg+$existing->avg)/2;
				$this->alpha['0-9']->letter = '0-9';
				$existing = next($alpha);
			}

			foreach ($alphanav as $letter) {
				if ($existing->letter == $letter) {
					$this->alpha[$letter] = $existing;
					$existing = next($alpha);
				} else {
					$entry = new stdClass();
					$entry->letter = $letter;
					$entry->total = 0;
					$entry->avg = 0;
					$this->alpha[$letter] = $entry;
				}
			}
			$this->paged = true;
			if (!is_numeric($this->page)) {
				$alphafilter = $this->page == "0-9"?
					"(LEFT(p.name,1) REGEXP '[0-9]') = 1":
					"IF(LEFT(p.name,1) REGEXP '[0-9]',LEFT(p.name,1),LEFT(SOUNDEX(p.name),1))='$this->page'";
				$loading['where'] .= (empty($loading['where'])?"":" AND ").$alphafilter;
			}

		}

		$query = "SELECT SQL_CALC_FOUND_ROWS $columns{$loading['columns']}
					FROM $producttable AS p
					LEFT JOIN $pricetable AS pd ON pd.product=p.id AND pd.type != 'N/A'
					LEFT JOIN $discounttable AS dc ON dc.product=p.id AND dc.price=pd.id
					LEFT JOIN $promotable AS pr ON pr.id=dc.promo
					LEFT JOIN $imagetable AS img ON img.parent=p.id AND img.context='product' AND img.type='image' AND img.sortorder=0
					{$loading['joins']}
					WHERE {$loading['where']}
					GROUP BY p.id {$loading['having']}
					ORDER BY {$loading['order']}
					LIMIT {$loading['limit']}";

		// Execute the main category products query
		$products = $db->query($query,AS_ARRAY);

		$total = $db->query("SELECT FOUND_ROWS() as count");
		$this->total = $total->count;

		if ($this->pagination > 0 && $limit > $this->pagination) {
			$this->pages = ceil($this->total / $this->pagination);
			if ($this->pages > 1) $this->paged = true;
		}

		// if ($this->pagination == 0 || $limit < $this->pagination)
		// 	$this->total = count($this->products);

		$this->pricing['min'] = 0;
		$this->pricing['max'] = 0;

		$prices = array();
		foreach ($products as $i => &$product) {
			if ($product->maxsaleprice == 0) $product->maxsaleprice = $product->maxprice;
			if ($product->minsaleprice == 0) $product->minsaleprice = $product->minprice;

			$prices[] = $product->onsale?$product->minsaleprice:$product->minprice;

			if (!empty($product->percentoff)) {
				$product->maxsaleprice = $product->maxsaleprice - ($product->maxsaleprice * ($product->percentoff/100));
				$product->minsaleprice = $product->minsaleprice - ($product->minsaleprice * ($product->percentoff/100));
			}

			if (!empty($product->amountoff)) {
				$product->maxsaleprice = $product->maxsaleprice - $product->amountoff;
				$product->minsaleprice = $product->minsaleprice - $product->amountoff;
			}

			$this->pricing['max'] = max($this->pricing['max'],$product->maxsaleprice);
			$this->pricing['min'] = min($this->pricing['min'],$product->minsaleprice);

			$this->products[$product->id] = new Product();
			$this->products[$product->id]->populate($product);

			if (isset($product->score))
				$this->products[$product->id]->score = $product->score;

			// Special property for Bestseller category
			if (isset($product->sold) && $product->sold)
				$this->products[$product->id]->sold = $product->sold;

			// Special property Promotions
			if (isset($product->promos))
				$this->products[$product->id]->promos = $product->promos;

			if (!empty($product->image)) {
				$image = new ProductImage();
				$image->id = $product->image;
				$image->value = unserialize($product->imgmeta);
				$image->expopulate();
				$this->products[$product->id]->images = array($image);
			}

		}
		$this->pricing['average'] = 0;
		if (count($prices) > 0) $this->pricing['average'] = array_sum($prices)/count($prices);

		if (!isset($loading['load'])) $loading['load'] = array('prices');

		if (count($this->products) > 0) {
			$Processing = new Product();
			$Processing->load_data($loading['load'],$this->products);
		}

		$this->loaded = true;

	}
 function load_products($loading = false)
 {
     global $Shopp, $wp;
     $db = DB::get();
     $catalogtable = DatabaseObject::tablename(Catalog::$table);
     $producttable = DatabaseObject::tablename(Product::$table);
     $pricetable = DatabaseObject::tablename(Price::$table);
     $discounttable = DatabaseObject::tablename(Discount::$table);
     $promotable = DatabaseObject::tablename(Promotion::$table);
     $assettable = DatabaseObject::tablename(Asset::$table);
     $this->paged = false;
     $this->pagination = $Shopp->Settings->get('catalog_pagination');
     $this->page = isset($wp->query_vars['paged']) ? $wp->query_vars['paged'] : 1;
     if (empty($this->page)) {
         $this->page = 1;
     }
     $limit = 1000;
     // Hard product limit per category to keep resources "reasonable"
     if (!$loading) {
         $loading = $this->loading;
     } else {
         $loading = array_merge($this->loading, $loading);
     }
     if (!empty($loading['columns'])) {
         $loading['columns'] = ", " . $loading['columns'];
     } else {
         $loading['columns'] = '';
     }
     $where = array();
     if (!empty($loading['where'])) {
         $where[] = "({$loading['where']})";
     }
     // Handle default WHERE clause matching this category id
     if (empty($loading['where']) && !empty($this->id)) {
         $where[] = "p.id in (SELECT product FROM {$catalogtable} WHERE category={$this->id})";
     }
     if (!isset($loading['nostock']) && $Shopp->Settings->get('outofstock_catalog') == "off") {
         $where[] = "p.id in (SELECT product FROM {$pricetable} WHERE type != 'N/A' AND inventory='off' OR (inventory='on' AND stock > 0))";
     } else {
         $where[] = "p.id in (SELECT product FROM {$pricetable} WHERE type != 'N/A')";
     }
     if (!isset($loading['joins'])) {
         $loading['joins'] = '';
     }
     if (!empty($Shopp->Cart->data->Category[$this->slug])) {
         $spectable = DatabaseObject::tablename(Spec::$table);
         $f = 1;
         $filters = "";
         foreach ($Shopp->Cart->data->Category[$this->slug] as $facet => $value) {
             if (empty($value)) {
                 continue;
             }
             $specalias = "spec" . $f++;
             // Handle Number Range filtering
             $match = "";
             if (!is_array($value) && preg_match('/^.*?(\\d+[\\.\\,\\d]*).*?\\-.*?(\\d+[\\.\\,\\d]*).*$/', $value, $matches)) {
                 if ($facet == "Price") {
                     // Prices require complex matching on price line entries
                     $min = floatvalue($matches[1]);
                     $max = floatvalue($matches[2]);
                     if ($matches[1] > 0) {
                         $match .= " ((onsale=0 AND (minprice >= {$min} OR maxprice >= {$min})) OR (onsale=1 AND (minsaleprice >= {$min} OR maxsaleprice >= {$min})))";
                     }
                     if ($matches[2] > 0) {
                         $match .= (empty($match) ? "" : " AND ") . " ((onsale=0 AND (minprice <= {$max} OR maxprice <= {$max})) OR (onsale=1 AND (minsaleprice <= {$max} OR maxsaleprice <= {$max})))";
                     }
                 } else {
                     // Spec-based numbers are somewhat more straightforward
                     if ($matches[1] > 0) {
                         $match .= "{$specalias}.numeral >= {$matches[1]}";
                     }
                     if ($matches[2] > 0) {
                         $match .= (empty($match) ? "" : " AND ") . "{$specalias}.numeral <= {$matches[2]}";
                     }
                 }
             } else {
                 $match = "{$specalias}.content='{$value}'";
             }
             // No range, direct value match
             // Use HAVING clause for filtering by pricing information
             // because of data aggregation
             if ($facet == "Price") {
                 $loading['having'] .= (empty($loading['having']) ? 'HAVING ' : ' AND ') . $match;
                 continue;
             }
             $loading['joins'] .= " LEFT JOIN {$spectable} AS {$specalias} ON {$specalias}.product=p.id AND {$specalias}.name='{$facet}'";
             $filters .= empty($filters) ? $match : " AND " . $match;
         }
         if (!empty($filters)) {
             $where[] = $filters;
         }
     }
     $where[] = "p.published='on'";
     $loading['where'] = join(" AND ", $where);
     $defaultOrder = $Shopp->Settings->get('default_product_order');
     if (empty($defaultOrder)) {
         $defaultOrder = "title";
     }
     $ordering = isset($Shopp->Cart->data->Category['orderby']) ? $Shopp->Cart->data->Category['orderby'] : $defaultOrder;
     if (!empty($loading['order'])) {
         $ordering = $loading['order'];
     }
     switch ($ordering) {
         case "bestselling":
             $purchasedtable = DatabaseObject::tablename(Purchased::$table);
             $loading['columns'] .= ',count(DISTINCT pur.id) AS sold';
             $loading['joins'] .= " LEFT JOIN {$purchasedtable} AS pur ON p.id=pur.product";
             $loading['order'] = "sold DESC";
             break;
         case "highprice":
             $loading['order'] = "pd.price DESC";
             break;
         case "lowprice":
             $loading['order'] = "pd.price ASC";
             break;
         case "newest":
             $loading['order'] = "pd.created DESC";
             break;
         case "oldest":
             $loading['order'] = "pd.created ASC";
             break;
         case "random":
             $loading['order'] = "RAND()";
             break;
         case "":
         case "title":
         default:
             $loading['order'] = "p.name ASC";
             break;
     }
     if (!empty($loading['orderby'])) {
         $loading['order'] = $loading['orderby'];
     }
     if (empty($loading['limit'])) {
         if ($this->pagination > 0 && is_numeric($this->page)) {
             if (!$this->pagination || $this->pagination < 0) {
                 $this->pagination = $limit;
             }
             $start = $this->pagination * ($this->page - 1);
             $loading['limit'] = "{$start},{$this->pagination}";
         } else {
             $loading['limit'] = $limit;
         }
     } else {
         $limit = (int) $loading['limit'];
     }
     $columns = "p.*,\n\t\t\t\t\timg.id AS thumbnail,img.properties AS thumbnail_properties,MAX(pr.status) as promos,\n\t\t\t\t\tSUM(DISTINCT IF(pr.type='Percentage Off',pr.discount,0))AS percentoff,\n\t\t\t\t\tSUM(DISTINCT IF(pr.type='Amount Off',pr.discount,0)) AS amountoff,\n\t\t\t\t\tif (pr.type='Free Shipping',1,0) AS freeshipping,\n\t\t\t\t\tif (pr.type='Buy X Get Y Free',pr.buyqty,0) AS buyqty,\n\t\t\t\t\tif (pr.type='Buy X Get Y Free',pr.getqty,0) AS getqty,\n\t\t\t\t\tMAX(pd.price) AS maxprice,MIN(pd.price) AS minprice,\n\t\t\t\t\tIF(pd.sale='on',1,IF (pr.discount > 0,1,0)) AS onsale,\n\t\t\t\t\tMAX(pd.saleprice) as maxsaleprice,MIN(pd.saleprice) AS minsaleprice,\n\t\t\t\t\tIF(pd.inventory='on',1,0) AS inventory,\n\t\t\t\t\tSUM(pd.stock) as stock";
     // Query without promotions for MySQL servers prior to 5
     if (version_compare($db->version, '5.0', '<')) {
         $columns = "p.*,\n\t\t\t\t\t\timg.id AS thumbnail,img.properties AS thumbnail_properties,\n\t\t\t\t\t\tMAX(pd.price) AS maxprice,MIN(pd.price) AS minprice,\n\t\t\t\t\t\tIF(pd.sale='on',1,0) AS onsale,\n\t\t\t\t\t\tMAX(pd.saleprice) as maxsaleprice,MIN(pd.saleprice) AS minsaleprice,\n\t\t\t\t\t\tIF(pd.inventory='on',1,0) AS inventory,\n\t\t\t\t\t\tSUM(pd.stock) as stock";
     }
     // Handle alphabetic page requests
     if ((!isset($Shopp->Category->controls) || isset($Shopp->Category->controls) && $Shopp->Category->controls !== false) && (isset($loading['pagination']) && $loading['pagination'] == "alpha" || !is_numeric($this->page))) {
         $alphanav = range('A', 'Z');
         $ac = "SELECT count(DISTINCT p.id) AS total,IF(LEFT(p.name,1) REGEXP '[0-9]',LEFT(p.name,1),LEFT(SOUNDEX(p.name),1)) AS letter,AVG(IF(pd.sale='on',pd.saleprice,pd.price)) as avgprice \n\t\t\t\t\t\tFROM {$producttable} AS p \n\t\t\t\t\t\tLEFT JOIN {$pricetable} AS pd ON pd.product=p.id AND pd.type != 'N/A' \n\t\t\t\t\t\tLEFT JOIN {$discounttable} AS dc ON dc.product=p.id AND dc.price=pd.id\n\t\t\t\t\t\tLEFT JOIN {$promotable} AS pr ON pr.id=dc.promo \n\t\t\t\t\t\tLEFT JOIN {$assettable} AS img ON img.parent=p.id AND img.context='product' AND img.datatype='thumbnail' AND img.sortorder=0 \n\t\t\t\t\t\t{$loading['joins']}\n\t\t\t\t\t\tWHERE {$loading['where']}\n\t\t\t\t\t\tGROUP BY letter";
         $alpha = $db->query($ac);
         $existing = current($alpha);
         if (!isset($this->alpha['0-9'])) {
             $this->alpha['0-9'] = new stdClass();
             $this->alpha['0-9']->letter = '0-9';
             $this->alpha['0-9']->total = 0;
             $this->alpha['0-9']->avg = 0;
         }
         while (is_numeric($existing->letter)) {
             $this->alpha['0-9']->total += $existing->total;
             $this->alpha['0-9']->avg = ($this->alpha['0-9']->avg + $existing->avg) / 2;
             $this->alpha['0-9']->letter = '0-9';
             $existing = next($alpha);
         }
         foreach ($alphanav as $letter) {
             if ($existing->letter == $letter) {
                 $this->alpha[$letter] = $existing;
                 $existing = next($alpha);
             } else {
                 $entry = new stdClass();
                 $entry->letter = $letter;
                 $entry->total = 0;
                 $entry->avg = 0;
                 $this->alpha[$letter] = $entry;
             }
         }
         $this->paged = true;
         if (!is_numeric($this->page)) {
             $alphafilter = $this->page == "0-9" ? "(LEFT(p.name,1) REGEXP '[0-9]') = 1" : "IF(LEFT(p.name,1) REGEXP '[0-9]',LEFT(p.name,1),LEFT(SOUNDEX(p.name),1))='{$this->page}'";
             $loading['where'] .= (empty($loading['where']) ? "" : " AND ") . $alphafilter;
         }
     }
     $query = "SELECT SQL_CALC_FOUND_ROWS {$columns}{$loading['columns']}\n\t\t\t\t\tFROM {$producttable} AS p \n\t\t\t\t\tLEFT JOIN {$pricetable} AS pd ON pd.product=p.id AND pd.type != 'N/A' \n\t\t\t\t\tLEFT JOIN {$discounttable} AS dc ON dc.product=p.id AND dc.price=pd.id\n\t\t\t\t\tLEFT JOIN {$promotable} AS pr ON pr.id=dc.promo \n\t\t\t\t\tLEFT JOIN {$assettable} AS img ON img.parent=p.id AND img.context='product' AND img.datatype='thumbnail' AND img.sortorder=0 \n\t\t\t\t\t{$loading['joins']}\n\t\t\t\t\tWHERE {$loading['where']}\n\t\t\t\t\tGROUP BY p.id {$loading['having']}\n\t\t\t\t\tORDER BY {$loading['order']} \n\t\t\t\t\tLIMIT {$loading['limit']}";
     // Execute the main category products query
     $products = $db->query($query, AS_ARRAY);
     if ($this->pagination > 0 && $limit > $this->pagination) {
         $total = $db->query("SELECT FOUND_ROWS() as count");
         $this->total = $total->count;
         $this->pages = ceil($this->total / $this->pagination);
         if ($this->pages > 1) {
             $this->paged = true;
         }
     }
     if ($this->pagination == 0 || $limit < $this->pagination) {
         $this->total = count($this->products);
     }
     $this->pricing['min'] = 0;
     $this->pricing['max'] = 0;
     $prices = array();
     foreach ($products as &$product) {
         if ($product->maxsaleprice == 0) {
             $product->maxsaleprice = $product->maxprice;
         }
         if ($product->minsaleprice == 0) {
             $product->minsaleprice = $product->minprice;
         }
         $prices[] = $product->onsale ? $product->minsaleprice : $product->minprice;
         if (!empty($product->percentoff)) {
             $product->maxsaleprice = $product->maxsaleprice - $product->maxsaleprice * ($product->percentoff / 100);
             $product->minsaleprice = $product->minsaleprice - $product->minsaleprice * ($product->percentoff / 100);
         }
         if (!empty($product->amountoff)) {
             $product->maxsaleprice = $product->maxsaleprice - $product->amountoff;
             $product->minsaleprice = $product->minsaleprice - $product->amountoff;
         }
         if ($this->pricing['max'] == 0 || $product->maxsaleprice > $this->pricing['max']) {
             $this->pricing['max'] = $product->maxsaleprice;
         }
         if ($this->pricing['min'] == 0 || $product->minsaleprice < $this->pricing['min']) {
             $this->pricing['min'] = $product->minsaleprice;
         }
         $this->products[$product->id] = new Product();
         $this->products[$product->id]->populate($product);
         // Special property for Bestseller category
         if (isset($product->sold) && $product->sold) {
             $this->products[$product->id]->sold = $product->sold;
         }
         // Special property Promotions
         if (isset($product->promos)) {
             $this->products[$product->id]->promos = $product->promos;
         }
         if (!empty($product->thumbnail)) {
             $image = new stdClass();
             $image->properties = unserialize($product->thumbnail_properties);
             if (SHOPP_PERMALINKS) {
                 $image->uri = $Shopp->imguri . $product->thumbnail;
             } else {
                 $image->uri = add_query_arg('shopp_image', $product->thumbnail, $Shopp->imguri);
             }
             $this->products[$product->id]->imagesets['thumbnail'] = array();
             $this->products[$product->id]->imagesets['thumbnail'][] = $image;
             $this->products[$product->id]->thumbnail =& $this->products[$product->id]->imagesets['thumbnail'][0];
         }
     }
     $this->pricing['average'] = 0;
     if (count($prices) > 0) {
         $this->pricing['average'] = array_sum($prices) / count($prices);
     }
     if (!isset($loading['load'])) {
         $loading['load'] = array('prices');
     }
     if (count($this->products) > 0) {
         $Processing = new Product();
         $Processing->load_data($loading['load'], $this->products);
     }
     $this->loaded = true;
 }