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=8189 AND P.price_set = 1 
		 AND P.product_category_id = 105 
		AND pc.show_in_search = 1

	 AND (P.product_category_id = 103 OR P.price_set) GROUP BY P.id ORDER BY DATE_FORMAT(P.arrival_date,"%Y-%m-%d %H")   DESC  ,  P.code
New Arrivals | Hannants
Hannants News Feed Subscribe To This Search | Create Search Alert Create Search Notification

New Arrivals



Found 15 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
Delivered 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

RS Models RSMI92200
1:72
RE-RELEASED!!! Kawasaki Ki-61-I Ko 3 decal variants: 1. Ki-61Ko, Akeno Fighter School, Japan 1943 2. Ki-61Ko, 2nd Chutai, 68th Sentai, New Guinea 1944 3. Ki-61Ko, Gunso Matsumi Nakano, 5th Shinten Seiku-tai, 244th Sentai, Chofu, February 1945 The first Ki-61 prototype took off in December 1941, and tests of other prototypes took place during 1942. At the end of 1942, these aircraft began to be mass-produced, and t...
Aircraft kits (injection)
New Arrivals
03/04/2024
£19.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

RS Models RSMI94003
1:72
RE-RELEASED!!! via Ba.422 LIMITED!!! resin fuselage with injection moulded wings etc The Avia Ba.422 aerobatic special was modified according to Novƒik's comments for the aerobatic competition held in Saint-Germain-en-Laye near Paris.It was built at the beginning of 1938 in two pieces 422.1 OK-AVB which was a backup and Novƒik's OK-AVC 422.2, powered by Avia Rk-17 engine with 350 hp. 1. Avia Ba.422.2 OK-AVC, Franti...iek ...
Aircraft kits (injection)
New Arrivals
03/04/2024
£22.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

RS Models RSMI92219
RE-RELEASED!!! North-American P-51H Mustang 1. P-51H, 412th, March Field, CA, August 1945 2. P-51H-1,prototype, Mines Field, Los Angeles, February 1945 3. P-51H, KN987, RAF, Boscombe Down 1945 4. P-51H from the 62th FS, 56th FG, Alaska Air Command, 1948 The P-51H was the final production Mustang and used the new V-1650-9 engine, a version of the Merlin that included Simmons automatic supercharger boost control with ...
Aircraft kits (injection)
New Arrivals
03/04/2024
£19.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

No Image
RS Models RSMI48008
Kawasaki Ki-61-II Kai 3 decal variants: 1. Ki-61-II, 56th Sentai, Itami AB, Japan 1945 2. Ki-61-II, 55th Sentai, Sano AB, Japan 1945 (What if) 3. Ki-61-II, Japan 1945 In the fall of the same year, the armament was strengthened and during the war there were further improvements and production of modernized modifications. The Ki-61 earned the respect of the Allies in the battles over the Pacific. They formed part of T...
Aircraft kits (injection)
New Arrivals
03/04/2024
£39.99
£35.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

RS Models RSMI92062
Heinkel He-112 Decals Hungary x 4, Czech, Great Britain, Japan
Aircraft kits (injection)
New Arrivals
03/04/2024
£19.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

RS Models RSMI92136
1:72
Bell P-39Q-25 Airacobra 6 decal variants 1. P-39 Q-25, of 213 GIAP, Poland, September 1944, Lt. Nikolaj Strojkov 2. P-39 Q-25, 2e Escadrille, GC 2/9 Auvergne, Le Vallon, 1945 3. P-39 Q-20, p. aircraft of the PAF Comm. Gen. Polynin, late 1945 4. P-39 Q-5, 4th Reconnaissance Sq,. 1943 5. P-39 Q-15, Zebra training Airacraft, 1944 6. P-39Q Q-25, 213, GIAP, 22 GIAD, USSR 1944 The Bell P-39 Airacobra was one...
Aircraft kits (injection)
New Arrivals
03/04/2024
£19.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

RS Models RSMI92146
Curtiss F-11C-2 Hawk universal box !!! On 16 April 1932, the U.S. Navy ordered two prototypes of a new shipboard fighter under the designations XF11C-1 and XF11C-2, with a 700hp Wright R-1820-78 radial. The R-1820-78 Cyclone and mixed structure of the XF11C-2 found favour with the US Navy, and, on 18 October 1932, a production order was placed for 28 F11C-2s, deliveries of which began in February 1933 and were completed in ...
Aircraft kits (injection)
New Arrivals
03/04/2024
£19.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

RS Models RSMI92166
Yakovlev Yak-11 / C-11 "Moose" 4 decal variants for CSSR, Hungarian, Poland, Mali 4 decal variants 1. C-11, No. 171707, 47. prƒ...é¯z. let. pluk, 2. let., Mlada, 1956 2. C-11, Hungarian Air Force 3. C-11, Gdynia - Oksywie, Babie Doly, Poland, 1960 4. C-11, Mali Air-force, 1961 The Yakovlev design bureau began work on an advanced trainer based on the successful Yak-3 fighter in mid 1944, although the trainer wa...
Aircraft kits (injection)
New Arrivals
03/04/2024
£19.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

No Image
RS Models RSMI94017
Bucker Bu-131A/D 2 decal v. for RomaniaThe Bƒ¼cker Bƒ¼ 131 Jungmann was a German two-seat biplane trainer used during World War II. The aircraft was designed by Swedish-born designer Ing. Anders J. Anderson, who led all design work at Bƒ¼cker until 1939. 1. Bƒ¼cker 131A, Aeroclub Prahova 1939 2. Bƒ¼cker 131D, Romanian AF, 1941 3. Bƒ¼cker 131D, Romanian AF, 1942 The first flight of the prototype Bƒ¼ 131 (D-3150) was...
Aircraft kits (injection)
New Arrivals
03/04/2024
£22.99
£20.69
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

No Image
RS Models RSMI94018
Avia Ba.122 1 decal v. for Czechoslovakia. In the spring of 1934, the army leadership decided on the participation of Czechoslovak pilots in the international aerobatic competition Coupe Mondial, which was to be held in June in Paris. Under the leadership of ing. Novotny and with the help of acrobats rtm. Novƒik and Sgt. Ambruz, a new aerobatic aircraft B-222 was created in six weeks. 1. Avia Ba.122.17, 4th Internationa...
Aircraft kits (injection)
New Arrivals
03/04/2024
£22.99
£20.69
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

No Image
RS Models RSMI94019
Avia Ba.322.1 1 decal v. for Czechoslovakia 1. Avia B...i.322.1, III. Nƒirodnƒ let RAl'S a II. Nƒirodnƒ soutAº...¾ v leteckƒ© akrobacii. Kbely 1937 The engine was a Castor II-A. During July 1937, B...i-122 machines were assigned to the training squadron of Air Regiment 4 and to the VLU. Modified B...i-322 and B-322 aircraft were built from two B...i-122s. The B...i-322 type was powered by an Rk-17 engine and had hooded wheels. He fl...
Aircraft kits (injection)
New Arrivals
03/04/2024
£22.99
£20.69
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

No Image
RS Models RSMI48009
Messerschmitt P.1101 The Messerschmitt P.1101 was commissioned by OKL in the summer of 1944 and was the first aircraft in the world to have variable wing geometry. It could only be built on the ground at a yaw angle of 35é° to 45é°. 3 decal variants: 1. Messerschmitt P.1101, 1./JG51, 1946 /alternate marking/ 2. Messerschmitt P.1101, 9./JG52, 1946 /alternate marking/ 3. Messerschmitt P.1101, Czechoslovakia, 1947 /alternat...
Aircraft kits (injection)
New Arrivals
13/02/2024
£42.50
£38.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

No Image
RS Models RSMI48010
Messerschmitt P.1101 Nightfighter The Messerschmitt P.1101 was commissioned by OKL in the summer of 1944 and was the first aircraft in the world to have variable wing geometry. It could only be built on the ground at a yaw angle of 35é° to 45é° 3 decal variants: 1. Messerschmitt P.1101, NJG1, Luftwaffe, 1946 /alternate marking/ 2. Messerschmitt P.1101, NJG3, Luftwaffe, 1946 /alternate marking/ 3. Messerschmitt P.1101, t...
Aircraft kits (injection)
New Arrivals
13/02/2024
£42.50
£38.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

No Image
RS Models RSMI48011
1:48
Messerschmitt Me P.1101 aâé-~Limited Edition The Messerschmitt P.1101 was commissioned by OKL in the summer of 1944 and was the first aircraft in the world to have variable wing geometry. It could only be built on the ground at a yaw angle of 35é° to 45é° 3 decal variants: 1. Messerschmitt P.1101, 9./JG54, Luftwaffe, 1946 /alternate marking/ 2. Messerschmitt P.1101, 4./JG51, Luftwaffe, 1946 /alternate marking/ 3. Mess...
Aircraft kits (injection)
New Arrivals
13/02/2024
£42.50
£38.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

RS Models RSMI92213
Re-released! Curtiss BFC-2 Goshawk. 1. BFC-2 Goshawk, USS Saratoga, VB-3B, 1937 2. BFC-2 Goshawk, USS Enterprise, VB-6, 1938 3. BFC-2 Goshawk, USS Saratoga, VB-2B, 1937 On 16 April 1932, the U.S. Navy ordered two prototypes of a new shipboard fighter under the designations XF11C-1 and XF11C-2, with a 700hp Wright R-1820-78 radial. The R-1820-78 Cyclone and mixed structure of the XF11C-2 found favour with the US Navy, an...
Aircraft kits (injection)
New Arrivals
13/02/2024
£19.99
£17.99
Out of Stock
0


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