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 pt.id=1897 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 135 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
No Image
CMK/Czech Master Kits 129-P35028
1:35
Type 92 Japanese Heavy Machine Gun The Type 92 was a Japanese heavy machine gun was a development of the earlier 6.5mm Type 3 gun and its origins can be traced back to the 7.7mm Hotchkiss. It was introduced to production in 1932 and used by the Japanese Army in the war in China and also later during WW2. Those guns captured by the Chinese were then also used against their former owners. Even later in the post war years, the gu...
Military vehicle detailing sets (resin)
Catalogue
£13.70
£12.33
Out of Stock
0
No Image
CMK/Czech Master Kits 129-P48010
1:48
SS-100 Gigant Front Radiator Mask Highly detailed front radiator grille directly replacing the kit's styrene item, which lacks much of the detail and even the radiator itself. You will find it in this set though. (designed to be used with Tamiya kits)
Military vehicle detailing sets (resin)
Catalogue
£8.10
£7.29
Out of Stock
0
No Image
CMK/Czech Master Kits 129-P35025
Sd.Kfz.251D MG 34 with armor shields and pivot mount 1/35 for Tamiya and Dragon kits The German Sd.Kfz.251 armored personnel carrier was most often equipped with a pair of machine guns, MG 34 or MG 42. The forward firing one had a shield, the other was placed on a hinge in the rear part and was used not only against ground targets but also against planes. Sd.Kfz.251D used a machine gun with a different type of shield than th...
Military vehicle detailing sets (resin)
Catalogue
£11.50
£10.35
Out of Stock
0
No Image
CMK/Czech Master Kits 129-P35026
1:35
Vickers Machine Gun (WWII) The Vickers machine gun was developed on the basis of the earlier Maxim weapon. The design prouded itself with great efficacy as well as almost a hundred percent reliability. These technically perfect Vickers guns were produced in really great numbers both for the army (these were water-cooled) and for the air force, where they were air-cooled. The British army had the Vickers in use well until the e...
Military vehicle detailing sets (resin)
Catalogue
£13.70
£12.33
Out of Stock
0
No Image
CMK/Czech Master Kits 129-P35027
1:35
PPSh-41 Soviet Submachine Gun. The iconic 'Shpagin' PPSh-1 submachine gun is among the most-well known guns of WW2 and now our 3D-Printed set almost oozes with plentiful detail and also offers two styles of the ammo magazines, the rounded and the segment types. This weapon remained in service on the Eastern Front not just by the Red Army, but also by the Wehrmacht which captured several of them. In the post war era, the Shpagi...
Military vehicle detailing sets (resin)
Catalogue
£7.20
£6.48
Out of Stock
0
No Image
CMK/Czech Master Kits 129-P35023
MG 34 Copula Anti Aircraft Machine Gun Ralk 1/35 (2 pcs.) 3D-Printed set of the MG34 machine gun on an anti-aircraft mount as used on the Pz.Kpfw.IV Ausf.H/Ausf.J, Panther and King Tiger tanks. A direct replacement of the styrene machine gun as found in the tank model kits. We offer two machine guns in our set, differing by the barrel, one has its barel perforated, the other one is with plain barrel. During the war both these ...
Military vehicle detailing sets (resin)
Catalogue
£7.99
£7.19
Out of Stock
0
No Image
CMK/Czech Master Kits 129-P35024
Sd.Kfz.251C MG 34 with armor shields and pivot mount 1/35 for Academy and AFV kits The Sd.Kfz.251C armoured personnel carrier was most often equipped with a pair of machine guns, a forward mounted MG34 with a shield and a rear one of the same type on an anti-aircraft mount. The styrene machine guns from the kits lack many details due to the injection technology. Our 3D-Printed replicas offer much better levels of the detail.
Military vehicle detailing sets (resin)
Catalogue
£11.50
£10.35
Out of Stock
0
No Image
IBG Models IBG72U029
Bedford QL Wheels (Pattern 1 Good Year TRACTION ) - 3D-Printed (designed to be used with IBG Models kits)
Military vehicle detailing sets (resin)
Catalogue
£8.99
Out of Stock
0
No Image
IBG Models IBG72U030
Bedford QL Wheels (Pattern 2 AVON) - 3D-Printed (designed to be used with IBG Models kits)
Military vehicle detailing sets (resin)
Catalogue
£8.99
Out of Stock
0
No Image
CMK/Czech Master Kits CMF72400
1:72
Marder Tank Destroyer Commander and Gunner Two crew figures in this set portray a commander and a gunner of a Marder tank destroyer. Made to fit the Special Armour line Marder II kit made by Special Hobby. In fact, these two can be used with any German WW2 tank destroyers or even another type of military vehicles.
Military vehicle detailing sets (resin)
Catalogue
£8.10
-
In Stock
5+
No Image
Art Scale 200-A35001
BMW R75 - Wheels and Cylinders detail set (designed to be used with Great Wall Models kits)
Military vehicle detailing sets (resin)
Catalogue
£17.99
Out of Stock
0
No Image
Art Scale 200-A35002
Zƒ¼ndap KS 750 wheels and cylinders (designed to be used with Great Wall Hobby kits) Included left and right cylinder and all 4 wheels. 3D print
Military vehicle detailing sets (resin)
Catalogue
£17.99
Out of Stock
0
No Image
Art Scale 200-A35003
BMW R75 det. set - engine cylinders recommended for Great Wall Hobby kit Included left and right cylinder
Military vehicle detailing sets (resin)
Catalogue
£8.40
-
In Stock
4
No Image
Art Scale 200-A35004
1:35
Zundap KS 750 - Engine Cylinders detail set (designed to be used with Great Wall Hobby kits) Included left and right cylinder 3D print. [Zƒ¼ndap]
Military vehicle detailing sets (resin)
Catalogue
£8.40
-
In Stock
3
No Image
CMK/Czech Master Kits CMK3148
Fliegerfaust B 'German WWII ground-to-air rocket launcher' The Fliegerfaust B was one of the German wonder weapons to be introduced into the production in the final stages of the war. This anti-tank weapon was capable of firing nine projectiles against a low-flying target. The period photos seem to confirm that the weapon was really used at the very end of the war in Berlin in 1945. Nicely detailed parts come with a photo etch...
Military vehicle detailing sets (resin)
Catalogue
£4.33
-
In Stock
4
No Image
CMK/Czech Master Kits 129-P35020
Hetzer / Sturmgeschutz/StuG.IIIG (Late) Remote Controlled MG 34 Remotely controlled MG34 machine gun mount on the top of the Sturmgeschutz/StuG.III and Hetzer armoured vehicles reduced the numbers of losses amongst the gunners defending their vehicles against attacking enemy troops. Highly detailed, 3D-Printed gun mount with the MG34 machine gun and an ammo magazine for 50 rounds come also with etched parts. This set is design...
Military vehicle detailing sets (resin)
Catalogue
£10.80
-
In Stock
5+
No Image
Eduard Brassin ED635036
WWII German NbKWrf39 smoke launcher 3D-Printed 1/35 kits)
Military vehicle detailing sets (resin)
Catalogue
£5.30
-
In Stock
4
No Image
Eduard Brassin ED635035
Sd.Kfz.251/1 Ausf.C radio equipment dark yellow 3D-Printed (designed to be used with Academy kits)
Military vehicle detailing sets (resin)
Catalogue
£7.99
-
In Stock
3
No Image
Hauler HLX48409
U.S.Howitzer M8- Hedgerow cutter (designed to be used with Tamiya kits)
Military vehicle detailing sets (resin)
Catalogue
£6.30
-
In Stock
5+
No Image
SBS Model SBS35035
1:35
Ford Model T basic upgrade set Resin/PE (designed to be used with ICM kits)
Military vehicle detailing sets (resin)
Catalogue
£13.99
-
In Stock
5
No Image
SBS Model SBS35036
1:35
Ford Model T Ambulance update set (designed to be used with ICM kits)
Military vehicle detailing sets (resin)
Catalogue
£15.99
-
In Stock
4
No Image
Eduard Brassin ED635033
Sd.Kfz.251/1 Ausf.C radio equipment 3D-Printed (designed to be used with Academy kits)
Military vehicle detailing sets (resin)
Catalogue
£7.99
-
In Stock
5
No Image
Eduard Brassin ED635034
WWII German fire extinguishers 3D-Printed 1/35
Military vehicle detailing sets (resin)
Catalogue
£5.30
-
In Stock
5+
No Image
IBG Models IBG35U003
One piece road wheels for Pz.Kpfw.II a1/a2/a3/b kit (3D-Printed ) (designed to be used with IBG kits0
Military vehicle detailing sets (resin)
Catalogue
£12.70
-
In Stock
11
No Image
Eduard Brassin ED635029
WWII German lamp headlights 3D-Printed 1/35
Military vehicle detailing sets (resin)
Catalogue
£5.99
-
In Stock
5+

   1   |   2   |   3   |   4   |   5      »      [6]

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