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=362554 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 7 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 115
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

Mark I Models MKM14410
1:144
RE-RELEASED! Mikoyan MiG-17PF/PFU Fresco D/E 'All-weather Fighter' E (USSR, Czechoslovakia, Hungary, Egypt) Developed from the successful MiG-15 jet in 1949, the MiG-17 constituted a marked improvement in transonic handling over the previous model. Both the clear weather interceptors (the MiG-17 and MiG-17F) and the all-weather fighters (the MiG-17PF and MiG-17PFU) were evolved in the USSR, with the total number reaching 10,5...
Aircraft kits (injection)
New Arrivals
03/04/2024
£13.99
£12.59
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

Mark I Models MKM14435
1:144
RE-RELEASED! Bristol Beaufighter Mk.IC 'Coastal Patrol' The Bristol Beaufighter was a British heavy fighter, developed from the Beaufort torpedo bomber and its first prototype flew in July 1939. The first Beaufighter variants the Mk.IF and Cs were high performance fighters and long-range fighters, respectively, while the former Mark also became an efficient night-fighter, employing the then novel AI radar. The Beauf...
Aircraft kits (injection)
New Arrivals
03/04/2024
£19.99
£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

Mark I Models MKM14498
Re-released! de Havilland Vampire FB.9 'Tropical Fighter-Bomber' (2in1 = 2 kits in 1 box) (RAF, RhAF) The de Havilland Vampire was a British jet fighter developed during WWII. It was the second jet, after the Gloster Meteor, operated by the RAF. It replaced wartime piston-engine fighters and was in front-line service until 1953. The Vampire FB Mk.9 was a tropicalised fighter-bomber variant based on the Vampire Mk.5, w...
Aircraft kits (injection)
New Arrivals
03/04/2024
£17.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

No Image
Mark I Models MKM144124
de Havilland Mosquito FB.VI 'Amiens Prison Raid' Description Colour schemes included in the kit: 1) de Havilland DH.98 Mosquito FB Mk.VI (Srs.II), HX922, Sky EG-F, No.487 Sq., Operation aâé-~Ramrod 564?, flown by Gp Capt P.C. Pickard & Flt Lt J. Broadley, Royal New Zealand Air Force, Hunsdon airfield, Hertfordshire, 18 February 1944 2) de Havilland DH.98 Mosquito FB Mk.VI (Srs.II), LR333, Sky EG-R, No.487 Sq., Operatio...
Aircraft kits (injection)
New Arrivals
07/03/2024
£23.70
£21.33
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
Mark I Models MKM144149
Re-released! Mil Mi-2 Hoplite 'Warsaw Pact' (2in1 = 2 kits in 1 box) (Soviet AF, Czechoslovak AF, Polish AF, East German AF) Designed by the Soviet Mil Design Bureau in late 1950s and exclusively produced in Poland by the WSK "PZL-ƒ......iwidnik" factory, the Mi-2 was a small transport and utility helicopter. The Mil' s prototype first flew in 1961, while the first ƒ......iwidnik-built example took to the air in November 1965. A n...
Aircraft kits (injection)
New Arrivals
07/03/2024
£17.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

No Image
Mark I Models MKM144177
Hawker Tempest Mk.V 'Fighter Aces' Description The Hawker Tempest Mk.V was the RAFs last piston-engined fighter used in the WWII, attaining great success in destruction of the German V-1 flying bombs. Developed in late 1941 from the troublesome Typhoon interceptor, the prototype flew in September 1942. The first order was for 400 Tempests, produced as the Mk.V Series 1 (100 a/c by Hawker) and Series 2 (300 a/c by Gloste...
Aircraft kits (injection)
New Arrivals
07/03/2024
£17.99
£16.19
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
Mark I Models MKM144178
Piper L-4 'Pleasure Flying' Colour schemes included in the kit: 1) Piper L-4J Grasshopper (J-3C-65), G-BCPJ (s/n 45-4466), various airfields, U.K.. 2002-11 2) Piper L-4J Grasshopper (J-3C-65), N68771 (s/n 45-55206), Chino Airport, California, U.S.A., autumn 1967 3) Piper L-4H Grasshopper (J-3C-65), OK-ZFB (s/n 45-4422), Gliding Centre of the Czechoslovak Aeroclub, Maresyev airfield H?rka, Novƒ½ Ji?ƒn, Czechoslovakia...
Aircraft kits (injection)
New Arrivals
07/03/2024
£18.99
£17.09
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