{ Totals: { resultCount: 0, maxPrice: 0, minPrice: 0 }, Results: [], CanonicalUrl: "https://www.hwaudio.co.uk/Search/", ResultSql: "SET group_concat_max_len = 500000000000000;SELECT result.*, CASE WHEN dropShipEnabled=1 AND vatRate<> 'margin' AND (((price-blindDiscount+cheapestshipping)/((100+VatRate)/100)-(cost+dropShipCost))/((price-blindDiscount+cheapestshipping)/((100+VatRate)/100))*100) >minDropShipMargin THEN fullTpStock ELSE 0 END as thirdPartyStock, retailerStock+ CASE WHEN dropShipEnabled=1 AND vatRate<> 'margin' AND (((price-blindDiscount+cheapestshipping)/((100+VatRate)/100)-(cost+dropShipCost))/((price-blindDiscount+cheapestshipping)/((100+VatRate)/100))*100) >minDropShipMargin THEN fullTpStock ELSE 0 END as totalAvailableStock FROM (SELECT 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, p1.bulk, p1.bulktype, p1.sale, CASE WHEN v.barcode IS NOT NULL THEN 'True' ELSE 'False' END as promoCodeAvailable, CASE WHEN v.barcode IS NOT NULL THEN v.barcode ELSE 0 END as discountCode, IFNULL(v.type, 6) as codetype, p1.LastEdit, p1.AvailableOnAmazon, p1.ExistsOnAmazon, IFNULL((SELECT GROUP_CONCAT(ebay_id, '|', relistifsold, '|', ListedPrice, '|', ShippingPrice, '|', qtyAvailable) FROM ebayListings e WHERE e.sam_id=p1.id AND (e.endedtime=0 AND e.endTime > '1555827673')), 0) as ebay_id, SUBSTRING_INDEX(GROUP_CONCAT(p1.id ORDER BY p1.price), ', ', 1) as id, p1.ParentId, p1.item, p1.fullName, p1.`colour`, p1.`option`, p1.variant3, p1.variant4, p1.strapLine, p1.warranty, p1.weight, p1.Length, p1.Width, p1.Height, p1.description, p1.mainImage, p1.bStock, CASE WHEN p1.archive='1' THEN 'True' ELSE 'False' END as isArchived, p1.`restrict`, p1.ProductType, p1.QuantityPurchase, p1.Special, p1.upc, p1.ASIN, p1.sku, p1.masterCat as masterCategoryId, p1.category as categoryId, p1.subCat as subCatId, p1.brand as brandId, masterCat.name as masterCategory, category.name as categoryName, subCat.name as subCategory, brands.name as brandName, 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 , CASE WHEN p1.productType='Product_Bundle' THEN (SELECT COUNT(itemid) FROM productBundles WHERE bundleitemid=p1.id) ELSE 0 END as BundleItemCount, COUNT(DISTINCT p1.id) as Variants, COUNT(DISTINCT CASE WHEN p1.totalStock>0 THEN p1.id END) as VariantsInStock, GROUP_CONCAT(DISTINCT CONCAT( 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) , '|', CONVERT(CASE WHEN sp.dropShipEnabled=1 AND p1.vatRate<> 'margin' AND (((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) + (CASE WHEN up.usePrice='altPrice' THEN p1.altPrice ELSE p1.price END)-p1.discount)/((100+p1.VatRate)/100)-(cost+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) +(CASE WHEN up.usePrice='altPrice' THEN p1.altPrice ELSE p1.price END)-p1.discount)/((100+p1.VatRate)/100))*100) >minDropShipMargin THEN p1.thirdPartyStock ELSE 0 END , CHAR(8)), '|', CONVERT(p1.id, CHAR(8)), '|', CONVERT(p1.mainImage, CHAR(16)), '|', p1.colour, '|', p1.`option`, '|', p1.Variant3, '|', p1.Variant4, '|', CASE WHEN up.usePrice='price' THEN p1.price ELSE p1.altPrice END, '|', p1.cost, '|', CONVERT(p1.Bulk, CHAR(1)), '|', p1.vatRate, '|', 0, '|', '', '|', 'p1.price', '|', p1.ProductType, '|', p1.altPrice, '|', p1.weight, '|', IFNULL(p1.bulktype, '')) ORDER BY CONVERT(p1.totalStock, CHAR(8)) DESC SEPARATOR '~') as VariantListByStock, MAX(price) as maxPrice, MIN(price) as minPrice 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 >= '1555827673' AND v.type<>5 WHERE p1.mastercat='5' AND p1.category='66' AND p1.subcat='374' AND p1.display1='1' AND p1.archive='0' GROUP BY TRIM(p1.ParentId), COALESCE(v.value, 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 p1.price*(v.discount/100) ELSE IFNULL(p1.discount, 0) END)) result ORDER BY (((retailerStock+LEAST(2, thirdPartyStock))/VariantsInStock)*maxPrice) DESC, maxPrice DESC LIMIT 0, 10", 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 >= '1555827673' AND v.type<>5 WHERE p1.mastercat='5' AND p1.category='66' AND p1.subcat='374' 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
HW Audio Music Shops in the North West of England
r2d2=true&mastercat=5&category=66&subcat=374
Search
X Contact Us

Oops! Nothing Found.

We are sorry, but no products are found using this criteria.

Please amend your search query, perhaps remove some category or brand filters?

Alternatively, please use the Contact Us button above to send us an enquiry.

Contact Us:X

(Unsubscribe Any Time)