SELECT /* MAIN SEARCH QUERY */
	
	pm.name AS manufacturer,
	pm.name AS manufacturer_name,
	P.id,
	P.id AS product_id,
	P.code,
    P.cost_price,
    P.vat_enabled,
	P.description,
	P.sold_retail,
	P.sold_trade,
	P.sold_wholesale,
    P.country_origin_code,
    P.commodity_code,
    P.price_set,
    P.width,
    P.height,
    P.length,
    P.product_scale_id,
    P.reorder_level,
    P.release_date,
    P.weight,
    P.restriction,
    P.restriction_bit,
    P.active,
    P.minimum_level,
    P.second_hand,
    P.limited_availability,
    P.can_reorder,
    P.warehouse_zone_id,
    P.wholesale_customers,
    P.new_price,
    P.big_postage,
    P.disable_min_order_amount,
    P.last_updated,
    P.notes,
    P.old_product_scale_txt,
    P.has_image_200,
    P.has_image_400,
    P.last_purchase_order_stock,
    P.last_purchase_order_date,
    P.first_delivered_date,
    P.last_moved,
    P.scale_string,
    P.keywords,
    P.finished_date,
    P.availability,
    P.sold,
    P.sold_total,
    P.sold_retail,
    P.sold_trade,
    P.sold_wholesale,
	P.base_price,
	P.base_export_price,
	P.retail_discount,
	P.eu_price,
	P.export_price,
	P.trade_price,
	P.trade_export,
	P.wholesale_price,
	P.wholesale_export,
	P.discount,
	P.backorder_retail_discount,
	P.backorder_discount,
	P.trade_discount,
	P.wholesale_discount,
	P.product_category_id,
    P.product_division_id,
	P.product_type_id,
	P.product_manufacturer_id,
	GROUP_CONCAT(DISTINCT ps.scale ORDER BY ps.scale ASC SEPARATOR ', ') AS scale,
	UNIX_TIMESTAMP(P.date_added) AS date_added,
	UNIX_TIMESTAMP(P.arrival_date) AS arrival_date ,
	DATE(P.arrival_date) AS real_arrival_date ,
	pt.name AS product_type,
	pt.name AS type_name,
	media.filename,
	IF (pp.id is null,0,1) as pending,
	
	if(
		coalesce( (coalesce(sl.count , 0) - coalesce(sc.product_quantity , 0 ) ) , 0 ) >= 0  ,
		if(
			coalesce( (coalesce(sl.count , 0) - coalesce(sc.product_quantity , 0 ) - coalesce(bv.backorders, 0 ) - coalesce(bv.free_backorders, 0 ) ) , 0 ) >= P.show_stock,
			P.show_stock ,
			coalesce( (coalesce(sl.count , 0) - coalesce(sc.product_quantity , 0 ) - coalesce(bv.backorders, 0 ) - coalesce(bv.free_backorders, 0 ) ) , 0 )
		)
	    , 0
	) as available,
    COALESCE(sl.count,0) as stock_level,
    COALESCE(sc.product_quantity,0) AS stock_committed,
    COALESCE(bv.backorders,0) AS backorders,
    COALESCE(bv.free_backorders,0) AS free_backorders,
	pc.id as department_id,
	pc.name as department,
	pc.name as department_name,
	P.show_stock,
	if(
		pc.name = 'Future Releases',
		true,
		false
	) AS future,
    unix_timestamp(media.date_uploaded) as unixtime



FROM product P
LEFT JOIN stock_level sl on P.id = sl.product_id
LEFT JOIN stock_committed sc on sc.product_id = P.id
LEFT JOIN backorder_values bv ON bv.product_id = P.id
LEFT JOIN product_category pc on P.product_category_id = pc.id
LEFT JOIN product_manufacturer pm ON P.product_manufacturer_id = pm.id
LEFT JOIN product_media media ON P.id = media.product_id AND `media`.`order` = 0

JOIN
product_scale_map AS psm ON (psm.product_id = P.id)

LEFT JOIN
product_scale ps ON (psm.scale_id = ps.id)

LEFT JOIN product_type pt ON P.product_type_id = pt.id
LEFT JOIN pending_product pp ON P.id = pp.id
WHERE 1  AND P.restriction_bit & 1 = 1 AND P.product_manufacturer_id=8232 AND ps.id = 954 AND P.price_set = 1 
		 AND P.product_category_id = 102 
		AND pc.show_in_search = 1

	 AND (P.product_category_id = 103 OR P.price_set) GROUP BY P.id ORDER BY DATE(P.arrival_date) desc, P.code
Catalogue | Hannants
Hannants News Feed Subscribe To This Search | Create Search Alert Create Search Notification

Catalogue



Found 9 results
| View per page
Refine your search

Deprecated: Creation of dynamic property Hannants\SearchFilters::$searchDirection is deprecated in /mnt/vault/Sync/Dessol/Sites/hannants.co.uk/php_includes/classes/SearchFilters.php on line 113
Manufacturer & Catalogue No ASC DESC
Scale ASC DESC
Description ASC DESC
Type ASC DESC
Department ASC DESC
Price ASC DESC
Watch
Stock ASC DESC
Buy

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

Techmod TM35002
Re-released! German Balkenkreuz assorted sizes 150mm 200mm 250mm 300mm 360mm 400mm
Military vehicle decals
Catalogue
£9.99
-
In Stock
5+

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

Techmod TM35010
1:35
Allied Yellow ID Stars, 3 styles with or without outline circle in 9 sizes. Double sheet
Military vehicle decals
Catalogue
£7.20
-
In Stock
4

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

Techmod TM35102
1:35
Mil Mi-8MT/Mi-17 Hip-H Helicopter. Polish Ministry Of The Interior; Polish Army; Polish Navy; Slovakia Air Force; Czech Air Force (designed to be used with Trumpeter kits)
Aircraft decals (military)
Catalogue
£9.99
-
In Stock
2

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

Techmod TM35101
1:35
Mil Mi-24D/W [Mi-24W] Mi-24D "Hind-D", "461″, Polish Army Aviation, Operation "Iraqi Freedom", Ad Diwaniyah, Iraq 2007 Mi-24D "Hind-D", "015″, Training Center of Engineering and Aviation, Polish Air Force, Oleśnica, Poland 2001 Mi-24W "Hind-E", "956″, Polish Army Aviation, ISAF, Ghazni, Afghanistan 2013 Mi-24D "Hind-D", "176″, Polish Army Aviation, 49th Air Base, Poland 2012 Mi-24W "Hind-E", "...
Aircraft decals (military)
Catalogue
£9.99
-
In Stock
5

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

Techmod TM35005
1:35
Pz.Kpfw.VI Tiger Ausf.E Early Production. Markings for 10 Tigers in various camouflage schemes in Russia and Tunisia 1943 Camouflage & markings versions "312″, 502 sPzAbt., Russia, September 1943 "334″, 503 sPzAbt., Russia, July 1943 "1311″, sPzKp of SS-PzRgt.1, Panzer Grenadier Division "LSSAH", Kursk, July 1943 "311″, 502 sPzAbt., Russia, September 1943 "111″, 501 sPzAbt., Tunisia, Decembe...
Military vehicle decals
Catalogue
£7.20
-
In Stock
5+

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

Techmod TM35001
1:35
Pz.Kpfw.VI Tiger Ausf.E Late production models. Camouflage & markings versions "007″, sSSPzAbt.101, Hauptsturmfƒ¼hrer Michael Wittmann, Normandy, August 1944 "205″, sSSPzAbt.101, Hauptsturmfƒ¼hrer Michael Wittmann, Normandy, June 1944 "112″, 1.Kompanie sSSPzAbt.101, Normandy, June 1944 "211″, 2.Kompanie sSSPzAbt.102, Normandy, June 1944 "300″, 505 sPzAbt., Lt. Roder, Poland, Summer 1944 "30...
Military vehicle decals
Catalogue
£7.20
-
In Stock
3

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

Techmod TM35009
Allied White ID Stars, 3 styles with or without outline circle in 9 sizes. Double sheet
Military vehicle decals
Catalogue
£7.20
-
In Stock
1

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

Techmod TM35008
1:35
Pz.Kpfw.VI Tiger Ausf.e Early Production (10) 823 Panzerregiment 2; 405 or 411 Panzerregiment 1; 833 Panzerregiment 2; 812 Panzerregiment 2; all Panzer Grey/Dunkelgelb; 821 Panzerregiment 2 Panzer grey with white over coat; 911 Panzerregiment 3 1331 Panzerregiment 1; S14 Panzerregiment 1; Signalbraun/Olivgrun/Dunkelgelb; S04 Panzerregiment 1 Michael Wittman.
Military vehicle decals
Catalogue
£7.20
-
In Stock
3

nothing for manufacturer

nothing for product_id

nothing for has_image_200

nothing for has_image_400

nothing for product_category_id

nothing for product_division_id

nothing for scale

nothing for real_arrival_date

nothing for product_type

nothing for filename

nothing for stock_level

nothing for stock_committed

nothing for backorders

nothing for free_backorders

nothing for department_id

nothing for future

nothing for unixtime

Techmod TM35006
Pz.Kpfw.VI Tiger Ausf.E Mid Production variant. Numbers, badges, names etc. for 12 Tigers in a wide variety of camouflage schemes in Russia, Italy, Normandy etc 1943-44. amouflage & markings versions "3ƒ¢aš-é³, 508 sPzAbt., Roma, March 1944 "301ƒ¢aš-é³, sPzAbt.501; Russia, February 1944 "A", sPzAbt.507; Tarnopol area, April 1944 "331ƒ¢aš-é³, 3.Kompanie sSSPzAbt.101, Normandy, June 1944 "131ƒ¢aš-é³, 1.Kompanie sSSPzAbt.10...
Military vehicle decals
Catalogue
£7.20
-
In Stock
3


Search Area Manufacturer Item Type Search

Part Number Scale Keywords Results per Page
Exact Match
Sort by:
Sort Direction:
Added Within last week
last 2 weeks
last month
last 6 months
last year