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