{
Totals:
{
resultCount: 441,
maxPrice: 225,
minPrice: 2
},
CanonicalUrl: "https://www.hwaudio.co.uk/Search/",
ResultSql: ,
TotalSql: "SET group_concat_max_len = 500000000000000;SELECT COUNT(result.id) as resultCount,
GROUP_CONCAT(result.id) as collectionids ,
MAX(result.price-result.availableDiscount)as maxPrice,
MIN(result.price-result.availableDiscount) as minPrice,
result.* FROM (SELECT p1.id,
SUM( FORMAT(CASE WHEN p1.producttype='Product_Bundle' THEN (SELECT CASE WHEN COUNT(CASE WHEN available>0 THEN bundleitemid END)=COUNT(bundleitemid) THEN MIN(available) ELSE 0 END a FROM (SELECT bundleitemid,
(SUM(stockLevel)-SUM(reservedQty)),
b.quantity,
(SUM(stockLevel)-SUM(reservedQty))/b.quantity as available FROM productbundles b LEFT JOIN stockLevels sl ON sl.id=b.itemid GROUP BY bundleitemid,
b.itemid ) tt WHERE bundleitemid=p1.id) ELSE IFNULL((SELECT SUM(sl.stockLevel)-SUM(sl.reservedQty) FROM StockLevels sl LEFT JOIN Locations l ON l.id=sl.stockLocation WHERE sl.id=p1.id AND l.pickFrom='1' GROUP BY sl.id),
0) END,
0) ) as retailerStock,
sp.dropShipEnabled,
sp.minDropShipMargin,
p1.thirdPartyStock as fullTpStock,
p1.discount as blindDiscount ,
CASE WHEN sp.dropShipEnabled=1 THEN sp.dropShipCost ELSE 0 END AS dropShipCost,
IFNULL((SELECT CASE WHEN (CASE WHEN up.usePrice='price' THEN p1.price ELSE p1.altPrice END)>gs.minFreeCarriage THEN GREATEST(((price* gs.shipMarkup)-gs.maxShipSubsidy),
0) ELSE GREATEST(((price* gs.shipMarkup)-gs.maxShipSubsidy),
(price*gs.shipMarkup)) END as cheapestshipping FROM postage po JOIN globalSettings gs WHERE maxWeight >= p1.weight And minWeight < p1.weight GROUP BY serviceName,
delTime ORDER BY po.price ASC LIMIT 1),
0) as cheapestshipping ,
p1.rrp,
CASE WHEN up.usePrice='price' THEN p1.price ELSE p1.altPrice END as price,
p1.price as priceprice,
p1.altPrice,
p1.cost,
p1.sor,
p1.vatRate,
CASE WHEN v.barcode IS NOT NULL AND v.value>0 THEN v.value WHEN v.barcode IS NOT NULL AND v.discount>0 THEN (CASE WHEN up.usePrice='price' THEN p1.price ELSE p1.altPrice END)*(v.discount/100) ELSE IFNULL(p1.discount,
0) END as availableDiscount,
v.instockonly as discountOnlyIfInStock,
IFNULL(c.tradeMarkup,
0) as tradeMarkup,
IFNULL(c.BasePrice,
0) as BasePrice,
(IFNULL(c.cardType,
'Standard')='Trade') as isTrade,
p1.ManageStock as ManageStock FROM `products` p1 JOIN globalSettings gs LEFT JOIN customers C on c.id='0' LEFT JOIN brands on p1.brand=brands.id LEFT JOIN masterCat ON p1.masterCat=masterCat.id LEFT JOIN category ON p1.category=category.id LEFT JOIN subCat ON p1.subCat=subCat.id LEFT JOIN suppliers sp ON sp.id=p1.supplier JOIN (SELECT usePrice FROM locations WHERE id='0') up LEFT JOIN vouchers v ON v.item=p1.id AND v.void='0' AND (v.user='0' OR v.user=0) AND v.expiry >= '1558774250' AND v.type<>5 WHERE p1.mastercat='1' AND p1.display1='1' AND p1.archive='0' GROUP BY TRIM(p1.ParentId),
GREATEST(IFNULL(v.value,
0),
IFNULL(v.discount,
0),
p1.discount)) result ORDER BY Price DESC",
Errors: [],
SpellingSuggestions: [],
SpellingSuggestionsOffered: False
}
HW Audio