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