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=362578 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 18 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

No Image
Takom TAK01020
1:16
Otto Carius WWII German Tank Ace Limited Edition Otto Carius (27/5/192224/1/2015) was a German tank commander who served in World War II. He is considered one of the most successful tank aces of the war, with claims of destroying over 150 opposing tanks. Carius fought on both the Eastern and Western Fronts, and was awarded the Knight's Cross with Oak Leaves for bravery and leadership. Born in Zweibrƒ¼cken, Germany, in 1...
Figures (injection)
New Arrivals
26/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

No Image
Takom TAK02125W
German WWII Jagdpanther G1 Early SdKfz 173 with Zimmerit Limited Edition WWII German Tank Destroyer. Zimmerit for upper hull surfaces. Hatches can be fitted opened or closed. Link & length tracks with track assembly jig. Photo-etch parts. Choice of 6 markings. This version of kit ref 2125 is without interior.
Military vehicle kits (injection)
New Arrivals
26/04/2024
£49.99
£44.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
Takom TAK02198
Pz.Kpfw.VI Tiger I Mid-production with Zimmerit The formidable (but not invincible) German World War II heavy tank renowned for its overwhelming firepower and fortified armour. Its ordnance inventory designation (Kampfmittelbestandsbeschreibung) was SdKfz 181 PzKpfw VI Ausf E. New tooling from Takom announced in November 2023. Hatches can be fitted opened or closed. 2 types of link & length tracks with track assembly jig. M...
Military vehicle kits (injection)
New Arrivals
26/04/2024
£49.99
£44.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
Takom TAK02199
Pz.Kpfw.VI Tiger I Late/Late Command w/ Zimmerit 2 in 1 The formidable (but not invincible) German World War II heavy tank renowned for its overwhelming firepower and fortified armour. Its ordnance inventory designation (Kampfmittelbestandsbeschreibung) was SdKfz 181 PzKpfw VI Ausf E. New tooling from Takom announced in November 2023. Build two ways, Late version or Late Command version. Hatches can be fitted opened or clos...
Military vehicle kits (injection)
New Arrivals
26/04/2024
£49.99
£44.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
Takom TAK02200
Pz.Kpfw.VI Tiger I Mid production with Zimmerit & Otto Carius figure Limited Edition The formidable (but not invincible) German World War II heavy tank renowned for its overwhelming firepower and fortified armour. Its ordnance inventory designation (Kampfmittelbestandsbeschreibung) was SdKfz 181 PzKpfw VI Ausf E. New tooling from Takom announced in November 2023. Otto Carius figure included. Hatches can be fitted opened or ...
Military vehicle kits (injection)
New Arrivals
26/04/2024
£49.99
£44.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
Takom TAK02200W
Pz.Kpfw.VI Tiger I Big Box Limited Edition (3 tanks 2 figures) Takom 10th anniversary 2013-2023 Tiger I special pack. Big Box includes: Pz.Kpfw.VI Tiger I Mid-production with Zimmerit (TAK02198) Pz.Kpfw.VI Tiger I Late production with Zimmerit (TAK02199) Pz.Kpfw.I Tiger I Mid production with Zimmerit & Otto Carius figure (TAK02200) 1:16 Otto Carius figure (TAK01020)
Military vehicle kits (injection)
New Arrivals
26/04/2024
£139.99
£125.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

Takom TAK02049
German WWII Super Heavy Tank Maus V1 Super-heavy German tank developed during World War II. The Maus was massive, weighing around 188 metric tons. Its armour was exceptionally thick, providing significant protection from enemy fire. It was armed with a 128mm main gun and a coaxial 75mm gun, offering substantial firepower. Despite its powerful weaponry and armour, its mobility was limited due to its immense weight and the resu...
Military vehicle kits (injection)
New Arrivals
26/04/2024
£44.99
£40.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

Takom TAK02050
German WWII Super Heavy Tank Maus V2 Super-heavy German tank developed during World War II. The Maus was massive, weighing around 188 metric tons. Its armour was exceptionally thick, providing significant protection from enemy fire. It was armed with a 128mm main gun and a coaxial 75mm gun, offering substantial firepower. Despite its powerful weaponry and armour, its mobility was limited due to its immense weight and the resu...
Military vehicle kits (injection)
New Arrivals
26/04/2024
£44.99
£40.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

Takom TAK02052
South African G6 Rhino Self-propelled Howitzer The G6 Rhino of the South African National Defence Force (SANDF) is a 155mm self-propelled howitzer developed in the late 1970s. It is based on a 6x6 wheeled chassis equipped with a 155mm howitzer capable of firing a variety of ammunition including HE-FRAG, smoke, illumination and incendiary rounds. Maximum range is 30km with standard HE-FRAG projectile and 39km with rocket-assis...
Military vehicle kits (injection)
New Arrivals
26/04/2024
£52.99
£47.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

Takom TAK01008
1:16
Re-issue at a lower price!! Pz.Kpfw.I Ausf.A
Military vehicle kits (injection)
New Arrivals
12/02/2024
£59.99
£53.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
Takom TAK01010
1:16
Re-issue at a lower price!! Pz.Kpfw.I Ausf.B
Military vehicle kits (injection)
New Arrivals
12/02/2024
£59.99
£53.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
Takom TAK02168
US WWII M29C Weasel Light Amphibious Tracked Vehicle Versatile tracked vehicle used by the Allies during World War II. Nicknamed "Water Weasel" the M29C was equipped with a sealed hull, buoyancy chambers at the front and rear of the vehicle and twin rudders for steering in water. Not designed to be a seagoing vessel, the M29C was capable of operating in inland waterways and coastal waters. It was used by the Allies to transpo...
Military vehicle kits (injection)
New Arrivals
12/02/2024
£35.99
£32.39
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
Takom TAK02170X
Jagdpanzer 38(t) Hetzer Early Production Limited Edition The Hetzer was a German light tank destroyer of the Second World War. It was based on the of the Panzer 38(t) light tank chassis and armed with a 75mm PaK 39 L/48 gun. The low-profile, heavily armoured tank destroyer could be used to ambush enemy tanks and is acknowledged as one of the most successful tank destroyers of WWII, destroying hundreds of enemy tanks. Hatches...
Military vehicle kits (injection)
New Arrivals
12/02/2024
£29.99
£26.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
Takom TAK02171X
Jagdpanzer 38(t) Hetzer Mid Production Limited Edition The Hetzer was a German light tank destroyer of the Second World War. It was based on the of the Panzer 38(t) light tank chassis and armed with a 75mm PaK 39 L/48 gun. The low-profile, heavily armoured tank destroyer could be used to ambush enemy tanks and is acknowledged as one of the most successful tank destroyers of WWII, destroying hundreds of enemy tanks. Hatches c...
Military vehicle kits (injection)
New Arrivals
12/02/2024
£29.99
£26.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
Takom TAK02172X
Jagdpanzer 38(t) Hetzer Late Production Limited Edition The Hetzer was a German light tank destroyer of the Second World War. It was based on the of the Panzer 38(t) light tank chassis and armed with a 75mm PaK 39 L/48 gun. The low-profile, heavily armoured tank destroyer could be used to ambush enemy tanks and is acknowledged as one of the most successful tank destroyers of WWII, destroying hundreds of enemy tanks. Hatches ...
Military vehicle kits (injection)
New Arrivals
12/02/2024
£29.99
£26.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
Takom TAK02178
German Heavy Tank Sd.Kfz.182 King Tiger (P) w/ 105mm KwK 46L/68 2 in 1 Tiger II, KĦnigstiger, King Tiger, Royal Tiger - alternative names for the heaviest and most heavily armoured tank fielded by the German army during World War II. First used in combat in 1944, King Tigers saw action on the Western and Eastern Fronts. Formidable and effective in combat, but too heavy and slow, and difficult to produce. KĦnigstiger was not...
Military vehicle kits (injection)
New Arrivals
12/02/2024
£52.99
£47.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
Takom TAK05023
IJN Aircraft Carrier Akagi, Island & Flight Deck, Pearl Harbor Attack 1941 AKAGI was a Japanese aircraft carrier that played a significant role in the Second World War. Commissioned in 1927 it was one of the six Japanese aircraft carriers that participated in the attack on Pearl Harbor on December 7, 1941. AKAGI's island was located on the port side of the ship and was relatively large for the time. It housed the ship's bridg...
Diorama bases (injection)
New Arrivals
12/02/2024
£74.99
£67.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
Takom TAK06006
USS ABSD-1 Large Auxiliary Floating Dry Dock Auxiliary Floating Dock, Big (AFDB), composed of ten Advance Base Sectional Docks (ABSD) each 165' wide, 93' long, 75' deep, with a total lifting capacity of 90,000"100,000 tons when fully assembled. Construction was undertaken in 1942/43 at four US shipyards, Everett-Pacific Shipbuilding & Dry Dock Co, Everett WA; Chicago Bridge & Iron Co at Eureka CA, also Morgan City LA; and...
Diorama accessories (injection)
New Arrivals
12/02/2024
£151.99
£136.79
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