public function getProductQuery($where = '', $order = '', $limit = null, $start = null) { $additionalColumns = array( 'FLOOR(prodratingtotal/prodnumratings) AS prodavgrating', getProdCustomerGroupPriceSQL() ); $additionalJoins = array(); $query = " SELECT p.*, pi.*, ".implode(', ', $additionalColumns)." FROM [|PREFIX|]products p LEFT JOIN [|PREFIX|]product_images pi ON (p.productid=pi.imageprodid AND pi.imageisthumb=1) ".implode("\n", $additionalJoins)." WHERE p.prodvisible=1 "; if($where) { $query .= " AND ".$where; } $query .= getProdCustomerGroupPermissionsSQL(); if($order) { $query .= " ORDER BY ".$order; } if($start !== null) { $query .= " OFFSET ".(int)$start; } if($limit !== null) { $query .= " LIMIT ".(int)$limit; } return $query; }
/** * Load and set extended product and variation details for items in the * quote. * * @param ISC_QUOTE_ITEM $item Optional item, to ensure it is also loaded. * @return ISC_QUOTE This instance of ISC_QUOTE. */ public function loadProductData(ISC_QUOTE_ITEM $additionalItem = null) { $productIds = array(); $variationIds = array(); if ($additionalItem !== null) { $productIds[] = $additionalItem->getProductId(); $variationIds[] = $additionalItem->getVariationId(); } foreach ($this->items as $item) { $productIds[] = $item->getProductId(); $variationIds[] = $item->getVariationId(); } $productIds = array_unique($productIds); $variationIds = array_unique($variationIds); if (empty($productIds)) { return $this; } $productData = array(); $variationData = array(); // Load up the data for the products $query = " SELECT p.productid, prodcurrentinv, prodcode, prodinvtrack, prodweight, prodwidth, prodheight, prodvariationid, proddepth, prodname, prodprice, prodretailprice, prodsaleprice, prodcalculatedprice, tax_class_id, prodavailability, prodtype, prodcostprice, prodfixedshippingcost, prodfreeshipping, prodoptionsrequired, pi.*, prodwrapoptions, prodvendorid, prodeventdaterequired, prodeventdatefieldname, prodpreorder, prodreleasedate, prodpreordermessage, prodcatids, prodminqty, prodmaxqty, disable_google_checkout, ".getProdCustomerGroupPriceSQL().", ( SELECT GROUP_CONCAT(ca.categoryid SEPARATOR ',') FROM [|PREFIX|]categoryassociations ca WHERE p.productid=ca.productid ) AS categoryids FROM [|PREFIX|]products p LEFT JOIN [|PREFIX|]product_images pi ON (p.productid=pi.imageprodid AND pi.imageisthumb=1) WHERE p.productid in (".implode(', ', $productIds).") "; $result = $GLOBALS['ISC_CLASS_DB']->Query($query); while ($product = $GLOBALS['ISC_CLASS_DB']->Fetch($result)) { $productData[$product['productid']] = $product; } // Are there any variations to load? if (!empty($variationIds)) { $query = " SELECT combinationid, vcproductid, vcimage, vcimagethumb, vcweight, vcweightdiff, vcstock, vcpricediff, vcprice, vcsku FROM [|PREFIX|]product_variation_combinations WHERE combinationid IN (".implode(",", $variationIds).") "; $result = $GLOBALS['ISC_CLASS_DB']->Query($query); $variationImages = array(); while ($variation = $GLOBALS['ISC_CLASS_DB']->Fetch($result)) { $variationData[$variation['combinationid']] = $variation; } } foreach ($this->items as $item) { $productId = $item->getProductId(); $variationId = $item->getVariationId(); if (isset($productData[$productId])) { $data = $productData[$productId]; if (isset($variationData[$variationId])) { $data['variation'] = $variationData[$variationId]; } $item->setProductData($data); } } if ($additionalItem !== null) { $productId = $additionalItem->getProductId(); $variationId = $additionalItem->getVariationId(); if (isset($productData[$productId])) { $data = $productData[$productId]; if (isset($variationData[$variationId])) { $data['variation'] = $variationData[$variationId]; } $additionalItem->setProductData($data); } } return $this; }