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=362460 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 45 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

No Image
Czech Master Resin CMR144-001
Avro Shackleton MR.3 'The Growler' This kit now includes new undercarriage made of hardened black PX resin Also newly designed instructions are now included in the box This full resin kit contains 56 parts, 10 hardened black resin parts (undercarriage), 9 clear resin parts (canopy and fuselage windows) and a comprehensive decal sheet. Colour schemes included in the kit: Avro Shackleton MR.3, WR975/203-F, No.203 Sq., RAF...
Aircraft kits (resin)
Catalogue
£43.80
Out of Stock
0

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

Czech Master Resin CMR-DS05
1:72
Beaching Trolley for Hansa-Brandenberg W.20 flying boat. (designed to be used with Czech Master Resin kits) (Hansa W.20/2]
Diorama accessories (resin)
Catalogue
£7.25
Out of Stock
0

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

Czech Master Resin CMR72-217
Re-released! de Havilland Vampire FB.31 in RAAF Service. Colour Schemes Included de Havilland Vampire FB.31 - A79-215 No.21 Squadron, RAAF, 1957 de Havilland Vampire FB.31 - A79-111 'Red Devils' Aerobatic Team, No.2 O.T.U., RAAF, Williamtown, N.S.W., circa 1958 de Havilland Vampire FB.31 - A79-915 No.21 Squadron, RAAF, Laverton, Victoria, September 1959 de Havilland Vampire FB.31 - A79-550 '50', RAAF (Squadron, date & p...
Aircraft kits (resin)
Catalogue
£47.99
-
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

Czech Master Resin CMR72-5105
Grumman G-21 Goose flying boat. Grumman G-21 Goose I - 942 ' 'MK-H', No.13 (OT) Squadron, RCAF, Vancouver, B.C., March 1941 Grumman JRF-2 ' U.S. Coast Guard, 1942 Grumman JRF-5 ' U.S. Navy, July 1943
Aircraft kits (resin)
Catalogue
£35.70
-
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

Czech Master Resin CMR144-007
1:144
Douglas DC-2 'European users' with decals. includes more optional parts (two additional tailplanes + propellers with bullet covers, antenas etc) for later DC-2 versions, and decal sheets includes 5 markings - Czechoslovak Airliner CSL, Lufthansa, Swissair, KLM and Finnish Air Lines.
Aircraft kits (resin)
Catalogue
£26.40
-
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

Czech Master Resin CMR144-008
Douglas C-33 Decals U.S. Army
Aircraft kits (resin)
Catalogue
£17.99
Out of Stock
0

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

Czech Master Resin CMR144-009
Douglas C-39 with decals for 2 x USAAC aircraft from 1940 and 1941 Douglas C-39 - 16 USAAC, Middletown Air Depot, PA, Becember 1940 Douglas C-39 - 62TG20 62nd Transport Group, USAAC, 1940
Aircraft kits (resin)
Catalogue
£17.99
-
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

Czech Master Resin CMR144-010
Douglas DC-2 'Civilian Service' In the mid-1930s the Douglas DC-2 opened a new chapter in modern-era air transport. Providing its passengers with a fast and comfortable vehicle, it caused an enormous increase in traffic over the routes not only in the USA, but also in Europe, and enabled easy connections as far afield as south-east Asia and Australia. Colour schemes included in the kit: 1) DC-2 (Nakajima-built...
Aircraft kits (resin)
Catalogue
£26.40
Out of Stock
0

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

Czech Master Resin CMR144-011
Douglas DC-2 Military Service Around the World In the mid-1930s the Douglas DC-2 opened a new chapter in modern-era air transport. Although overshadowed by its more famous successors, the DC-3/Dakota/C-47 Skytrain and C-53 Skytrooper aeroplanes, the DC-2 and its military versions tirelessly operated literally around the globe and flew well up to the 1950s. They were involved both in the Spanish Civil War and Second World War,...
Aircraft kits (resin)
Catalogue
£26.40
-
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

Czech Master Resin CMR72-140
Westland Wyvern TF.1 with Rolls-Royce Eagle 24 cylinder 'H' configured engine with decals Westland Wyvern N.11/44 - TS380 (the fourth Mk.1 Prototype), December, 1948 Westland Wyvern Prototype - TS371 Yeovil Factory Base, December 1948 Westland Wyvern TF.1 - VR131 Westland Aircraft Ltd., Yeovil, Somerset, March 1949
Aircraft kits (resin)
Catalogue
£40.80
-
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

Czech Master Resin CMR72-112
Morane-Saulnier A1 with decals Morane-Saulnier Type A.1 - MS 1591 Escadrille MSP 156, 1918. Flown by: Sgt. Walter J. Shaffer Morane-Saulnier Type A.1 - MS 1724 Escadrille MSP 158, March/April 1918. Flown by: Sgt. Rufus R. Rand Jnr.
Aircraft kits (resin)
Catalogue
£29.20
-
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

Czech Master Resin CMR72-G5021
Slingsby Kirby Cadet Mk.1 with decals (gliders)
Aircraft kits (resin)
Catalogue
£18.50
-
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

Czech Master Resin CMR72-132
1:72
Re-released! Westland Welkin Mk.I re-issue, decals included
Aircraft kits (resin)
Catalogue
£45.30
-
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

Czech Master Resin CMR72-G5016
1:72
Goppingen Go-3/DFS Minimoa with decals (gliders) GĦppingen G 3 'Minimoa' - 'die 100 Minimoa', *Nationalsozialistisches Fliegerkorps (NSFK), Germany, June 1939 GĦppingen G 3 'Minimoa' - S-42 Post-war 'British Air Force of Occupation' gliding club, 1945 GĦppingen G 3 'Minimoa' - 15-790 Contest number '52', D-STADT-NECKARGEMUND GĦppingen G 3 'Minimoa' - D-7-2192 (Czecholsovak designation VT-2), c/n 305, *'strredni let...
Aircraft kits (resin)
Catalogue
£21.80
-
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

Czech Master Resin CMR72-5005
Hansa-Brandenberg W.20/2 flying boat. Now with decals! (Also available to buy separately CMR-DS05 Beaching Trolley for this kits)
Aircraft kits (resin)
Catalogue
£24.75
-
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

Czech Master Resin CMR72-011
1:72
Siemens-Schuckert D.III (this kit does not have any decals included)
Aircraft kits (resin)
Catalogue
£50.60
-
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

Czech Master Resin CMR72-123
1:72
Supermarine Spitfire Prototype with decals Supermarine Type 300, Supermarine Spitfire Prototype - K5054 As she appeared in June 1936
Aircraft kits (resin)
Catalogue
£28.80
-
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

Czech Master Resin CMR72-170
Supermarine Spitfire Type 300 prototype, Eastleigh Airport 1935 'Birth Of A Legend' Supermarine Type 300, Supermarine Spitfire Prototype - K5054 'Maiden Flight', Eastleigh Airport, 5th March 1936
Aircraft kits (resin)
Catalogue
£34.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

Czech Master Resin CMR72-236
Supermarine Spitfire Prototypes. F.21 Victor PP139 & F.23 Valiant LA187. This full resin kit contains 46 parts, two clear parts, vacu canopy (a spare set is included for modeller's convenience), and a coloured photo-etched fret. The decal sheet also includes a set of stencils. Colour schemes included in the kit: 1) Spitfire F Mk.21 'Victor' prototype, PP139, Aeroplane & Armament Experimental Establishment, Boscombe Down, ...
Aircraft kits (resin)
Catalogue
£32.60
-
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

Czech Master Resin CMR72-154
1:72
Supermarine Spitfire Mk.XII
Aircraft kits (resin)
Catalogue
£34.60
-
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

Czech Master Resin CMR72-G109
1:72
Opel-Sander RAK.1 glider with decals Opel-Sander RAK.I Rocket Glider Early 'Blue' trim Opel-Sander RAK.I Rocket Glider Later 'Red' trim (gliders)
Aircraft kits (resin)
Catalogue
£19.80
-
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

Czech Master Resin CMR72-5070
1:72
CAC CA-15 NOW WITH DECALS!
Aircraft kits (resin)
Catalogue
£29.20
Out of Stock
0

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

Czech Master Resin CMR72-239
1:72
Curtiss F9C Sparrowhawk "Airship Fighter"
Aircraft kits (resin)
Catalogue
£21.99
-
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

Czech Master Resin CMR72-009
1:72
Armstrong F.K.10
Aircraft kits (resin)
Catalogue
£50.60
-
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

Czech Master Resin CMR72-G014
1:72
Grunau Baby I (gliders)
Aircraft kits (resin)
Catalogue
£11.30
-
In Stock
1

   1   |   2      »      [2]

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