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=7973 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 19 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

No Image
CMK/Czech Master Kits 129-P72016
Hawker Typhoon Mk.I Standard Exhausts 3D-printed (designed to be used with Airfix kits) 3D-printed exhausts for 1/72 Typhoon model. Portrays the exhaust type without aerodynamic fairings.
Aircraft exhausts (resin)
New Arrivals
10/04/2024
£3.60
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
CMK/Czech Master Kits 129-P35030
MG 15 Machine Gun with Infantry Modification. The MG 15 was an aircraft machine gun widely used as a hand manipulated weapon in German aircraft in very early stages of WW2. By 1940, it had been already replaced by the MG81 machine gun. The MG15 was then adapted for the infantry needs and used by Luftwaffe and Wehrmacht ground units.
Military vehicle detailing sets (resin)
New Arrivals
09/04/2024
£8.10
£7.29
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
CMK/Czech Master Kits 129-P72012
Hawker Typhoon Mk.I Engine (designed to be used with Airfix kits) As it was the case with Airfixs 1/72 Tempest, we have now also prepared an top half engine printed set for the 1/72 Typhoon from the same producer. The Sabre engine now comes also with the oil tank situated directly behind the power unit. The set is a one part item only and can be easily placed in the model as all is necessary is to cut away the top cowling a...
Aircraft engines and propellers (resin)
New Arrivals
09/04/2024
£14.85
£13.37
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
CMK/Czech Master Kits 129-P72013
Hawker Typhoon Mk.I Gun Sight and Seat Correction (designed to be used with Airfix kits) One of Airfixs kit flaws is also the very narrow pilots seat and incorrectly shaped head armour plate. Our set nicely fixes these issues and brings you a printed seat with much better detail overall and of the correct size. The set also contains the gun sight with its bracket. The sight is made of clear material.
Aircraft detailing sets (resin)
New Arrivals
09/04/2024
£6.30
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
CMK/Czech Master Kits 129-P72014
Hawker Typhoon Mk.I Three-blade Propeller (designed to be used with Airfix kits) The late series of the Hawker Typhoon employed a four-bladed propeller unit that can be found in the Airfix kit. However, the majority of Typhons were still fitted with the earlier style three-bladed prop which comes now in the form of this 3D-printed set. And as the prop hub oozes with detail, we also recommend the set even without the spinner.
Aircraft engines and propellers (resin)
New Arrivals
09/04/2024
£7.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

No Image
CMK/Czech Master Kits CMK4472
Grumman TBF-3/TBM-3 Avenger Paddle Blade Propeller Correction Set (designed to be used with Accurate Miniatures and Academy kits) Among the only very few flaws of the TBF-3 Avenger kit (ex-Accurate Miniatures), unfortunately the quite visible one, is the propeller with narrow blades of the earlier TBF-1 version. And as the Dash Three had propeller blades rather broad, paddle-like, easily fix the issue now using our set th...
Aircraft detailing sets (resin)
New Arrivals
09/04/2024
£6.30
£5.67
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
CMK/Czech Master Kits CMK6009
1:35
Vietnam Era US Helicopter Pilot Helmet (2 pcs.) [Bell AH-1G Cobra Sikorsky CH-54A](designed to be used with ICM kits)
Aircraft detailing sets (resin)
New Arrivals
09/04/2024
£5.60
£5.04
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
CMK/Czech Master Kits CMK8065
Humber LRC Mk.II Resin cast model of a British armoured car used during WW2. The Humber LRCs were mass produced in Mk.2, Mk.3 and Mk.3a versions and saw service in the British Isles, Western and Southern Europe, in Africa and in India, too. The units operating these vehicles belonged to the British Army, RAF and the Indian Army. On top of that the vehicles were also used by Britain-based units with Polish and Czechoslovak pers...
Military vehicle kits (resin)
New Arrivals
09/04/2024
£32.60
£29.34
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
CMK/Czech Master Kits CMK8069
60 cm Flak Scheinwerfer (Flak Sw-36) mit Sd.Ah.51 / Svetlomet 60N s vlekem. The German WW2 60cm-diameter searchlight that was used both by the Luftwaffe and Wehrmacht to illuminate the night skies during air raids and airfield runways to allow for safe landings. This searchlight is already on offer in 1/72 scale (model MV130) and now it has been scaled up to 1/48 and improved with better detail allowed by the larger scale. The...
Military vehicle kits (resin)
New Arrivals
09/04/2024
£34.20
£30.78
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
CMK/Czech Master Kits CMQ72415
Republic F-84F Thunderstreak Wheels (designed to be used with Special Hobby kits) The excellent F-84F Thunderstreak kit can be improved further by this set of resin cast wheels with much better levels of detail than the styrene kit wheels could offer.
Aircraft wheels (resin)
New Arrivals
09/04/2024
£5.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

No Image
CMK/Czech Master Kits 129-P35029
Type 3 Japanese Heavy Machine Gun 1/35 The Type 3 heavy machine gun was developed from the Hotchkiss gun. In early 20th century, the Japanese had bought the licence rights to produce the Hotchkiss machine gun, Japanese engineer Kijiro Nambu improved the original design though to meet better the Japanese Army requirements. At first was produced the Type 38 heavy machine gun, later some more design alterations led to the devel...
Military vehicle detailing sets (resin)
New Arrivals
04/03/2024
£13.70
£12.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
CMK/Czech Master Kits CMF35260
German WWII Hetzer Sitting Infantry man
Figures (resin)
New Arrivals
04/03/2024
£12.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
CMK/Czech Master Kits CMF35261
1:35
WWII German Jagdpanzer 38(t) 'Hetzer' Crewmember 1/35 Highly detailed resin cast figure of German tank crewmember was designed for Jagdpanzer 38(t) 'Hetzer' tank hunter. The crew member sits on the Jagdpanzer 38(t) 'Hetzer''s armoured roof with his legs rested on the sloping armour. The figure is cast in one piece with only separated head and right arm.
Figures (resin)
New Arrivals
04/03/2024
£8.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
CMK/Czech Master Kits CMF35262
German WWII Hetzer Radio Operator / Gunner
Figures (resin)
New Arrivals
04/03/2024
£8.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
CMK/Czech Master Kits CMF35263
German WW II Hetzer Commander 1945
Figures (resin)
New Arrivals
04/03/2024
£8.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
CMK/Czech Master Kits CMK3150
Ford V 3000 S Wheels. Nicely cast, detail wheels with weighted tyres offering life-like tread are an easy replacement for the original styrene wheels that lack many of the detail offered in the kit of the German WW2 trucks (designed to be used with ICM kits)
Military vehicle detailing sets (resin)
New Arrivals
04/03/2024
£14.99
£13.49
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
CMK/Czech Master Kits CMK4469
1:48
Lockheed PV-1 Ventura Nose and Turret Armament 1 (designed to be used with Accurate Miniatures, Academy and Italeri kits) Finely detailed machine guns for the turret and belly gunner station, a nose housing for three machine guns and the fuselage gun barrels, all of which have much better details than the kit parts.
Aircraft detailing sets (resin)
New Arrivals
04/03/2024
£8.10
£7.29
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
CMK/Czech Master Kits CMK6008
1:35
Bell AH-1G Cobra Upgrade Cockpit Set (designed to be used with ICM kits) This multimaterial set consists of resin cast, 3D-printed and photo-etched parts and significantly improves the pilots and gunners offices of the AH-1G Cobra model. And as the interior of the cockpit is very easy to see through the large side window panes, which can even be posed open, this set is truly a aâé-~Must have! one.
Aircraft detailing sets (resin)
New Arrivals
04/03/2024
£12.40
£11.16
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
CMK/Czech Master Kits CMK8068
SS-100 Gigant Wheels 1/48 / (designed to be used with Tamiya kits) Detail, resin cast wheels with weighted tyres and accurately portrayed tread pattern, overall much finer detail than the styrene wheels in the Tamiya kit.
Military vehicle detailing sets (resin)
New Arrivals
04/03/2024
£11.70
£10.53
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