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=1899 AND P.product_manufacturer_id=8069 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 111 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
Italeri IT6577
Panzerjager I tank destroyer NEW GLUEABLE RUBBER TRACKS - DECALS FOR 4 VERSIONS - COLOR INSTRUCTIONS SHEET - PHOTO-ETCHED FRET CONTAINS 1 FIGURE The self-propelled Panzerjager I tank destroyer was built on the hull of the Panzer I light tank and armed with an effective weapon of the period, the 47mm Czechoslovakian anti-tank gun. The Panzer I tank had in fact a protection and armament capability no longer in keep...
Military vehicle kits (injection)
Catalogue
£29.99
-
In Stock
14
No Image
Italeri IT0241
Bedford QL Medium Truck The Bedford QL line of military trucks was established in the late 1930s for the British Army to have a more modern and flexible transport vehicle. Following the conclusion of testing in 1940 and in early 1941, the Bedford QLs were distributed to Army Corps, Regiments and other Units. For the duration of the war it was deployed on various fronts, proving to be a highly effective support vehicle for meet...
Military vehicle kits (injection)
Catalogue
£35.99
£32.39
Out of Stock
0
Italeri IT0228
1:35
Dodge WC-56/57 Command Car NEW DECALS FOR 3 VERSIONS - COLORS INSTRUCTIONS SHEET FIGURE NOT INCLUDED The Dodge WC range of military transport vehicles was mainly produced by American industry during the Second World War. The development of the WC 51-52 series aimed to create a light, four-wheel drive "utility vehicle", characterized by great reliability and flexibility in any environment and further enhanced by a good p...
Military vehicle kits (injection)
Catalogue
£19.99
£17.99
Out of Stock
0
Italeri IT6392
GMC DUKW 353 Amphibious truck To meet the logistical needs of the US Army landing forces in transferring loads quickly and efficiently from ships to beaches, the American General Motor CCKW-353 2é½ t 6x6 amphibious version tactical transport truck was designed and built for this purpose. The basic vehicle components were the same as the GMC road truck with its 4,400 cm3 6-cylinder engine, three differentials and six driving wh...
Military vehicle kits (injection)
Catalogue
£31.50
£28.35
Out of Stock
0
Italeri IT6477
1:35
Semovente L40 da 47/32
Military vehicle kits (injection)
Catalogue
£29.99
-
In Stock
5+
Italeri IT7508
Re-released! Autocannone RO3 with 90/53 AA Gun
Military vehicle kits (injection)
Catalogue
£16.50
-
In Stock
1
Italeri IT7518
Re-released! M4A3 Sherman 75mm x 2 Fast Assembly Kits
Military vehicle kits (injection)
Catalogue
£16.50
-
In Stock
5
No Image
Italeri IT6598
HMMWV M966 TOW Carrier NEW RUBBER TYRES - COLOR INSTRUCTION SHEET The military vehicle developed for the US Army with the acronym HMMWV ( High Mobility Multipurpose Wheeled Vehicle ), universally known as the "Humvee", is representative of the technological evolution of US "know-how" applied to the creation of extremely flexible, 'all-condition' vehicles of which even the "Willys", is still an icon today. The "Humvee" went ...
Military vehicle kits (injection)
Catalogue
£24.99
-
In Stock
6
Italeri IT7034
1:72
German Sd.Kfz.250/3
Military vehicle kits (injection)
Catalogue
£15.99
-
In Stock
9
Italeri IT0270
Pz.Kpfw.V Ausf.A Panther The Panzerkampfwagen V "Panther" is widely recognised as one of the best tanks that was produced during World War 11. It was deployed as a direct response to the Soviet T-34 and KV-1 tanks on the Eastern Front, which were superior to both the Panzer III and Panzer IV in terms of performance and armaments. Powered by a 700 horsepower V-12 Maybach engine, it was armed with the 75 mm KwK 42 high speed gun...
Military vehicle kits (injection)
Catalogue
£36.99
-
In Stock
19
No Image
Italeri IT6596
M1A1 Abrams DECALS FOR 4 VERSIONS - NEW GLUABLE TRACKS OR LINK AND LENGHT TRACKS - COLORS INSTRUCTION SHEET The 3rd generation American main battle tank the M1 Abrams, is the backbone of the United States Army and United States Marine Corps armored divisions. The Abrams is characterized by its heavy armor (it is one of the heaviest combat vehicles in the world), and also its powerful multifuel turbine engine. The tank first...
Military vehicle kits (injection)
Catalogue
£35.99
-
In Stock
8
No Image
Italeri IT6597
1:35
Kfz. 12 Horch 901 typ 40 frƒ¼hen Ausf RUBBER TYRES - DECALS FOR 4 VERSIONS - COLORS INSTRUCTION SHEET Beginning in the mid '30s Germany sought to improve the mobility and mechanization of its Army. One of the more important requirements of its ambitious development program was the adoption of a standard military chassis which would optimize cross country mobility. Among the different versions that were proposed, the medium ...
Military vehicle kits (injection)
Catalogue
£24.99
-
In Stock
6
No Image
Italeri IT6599
Carro Armato P40 with Metal Gun Barrel and Photo Etch. PHOTOETCHED - METAL GUN BARREL - LINK-AND-LENGTH TRACKS - DECALS SHEET FOR 2 VERSIONS - COLOR INSTRUCTION SHEET The early years of WW2 highlighted the inadequacies of Italian medium tanks in modern warfare, which was now characterized by clashes between heavily armed and heavily armored tanks, and also by the use of more effective and lethal anti-tank weapons. Following...
Military vehicle kits (injection)
Catalogue
£35.99
-
In Stock
16
Italeri IT6481
1:35
Leopard MBT 1A5
Military vehicle kits (injection)
Catalogue
£34.99
-
In Stock
15
Italeri IT7063
M3A1 White Scout Car
Military vehicle kits (injection)
Catalogue
£12.50
-
In Stock
5
Italeri IT7003
M4A1 Sherman
Military vehicle kits (injection)
Catalogue
£12.99
-
In Stock
5+
No Image
Italeri IT6595
1:35
Kfz.10 Demag D7 with 7.5cm Le Ig18 + Crew
Military vehicle kits (injection)
Catalogue
£34.99
-
In Stock
7
No Image
Italeri IT6591
AB 41 with Bersaglieri Italian Infantry. CONTAINS 6 FIGURES - RUBBER TYRES - DECALS FOR 3 VERSIONS - COLOR INSTRUCTION SHEET The AB series of armored cars were developed by Fiat-Ansaldo and used by the reconnaissance units of the Italian "Regio Esercito". Following the introduction of the first version, the AB-40, the AB-41 was built which featured a more powerful engine and was armed with the 20 mm Breda 20/65 gun. Additio...
Military vehicle kits (injection)
Catalogue
£35.99
-
In Stock
11
No Image
Italeri IT6594
FLAKPANZER IV OSTWIND LINK-AND-LENGTH TRACKS OR GLUABLE RUBBER TRACKS - DECALS FOR 2 VERSIONS - COLORS INSTRUCTION SHEET During the war, the need to produce self-propelled anti-aircraft guns capable of protecting troops against low-flying air attacks became marked. To this end, the hull of the Panzer IV medium tank, the Flakpanzer IV Wirbelwind, was used as a platform to add on a 20 mm Flak 38 quadruple gun system. By the e...
Military vehicle kits (injection)
Catalogue
£39.99
-
In Stock
8
No Image
Italeri IT7079
Sd.Kfz.251/10 The Sd.Kfz.251 half-track was an armored fighting vehicle and was used by the Wehrmacht on all fronts, during the Second World War. Powered by a Maybach 6-cylinder petrol engine was able to achieve, even in off-road terrain, the speed of 55 km / h. Produced in huge numbers, it was mainly used to transport the "Panzergrenadiers" but, during the war, several versions has been developed to meet various operational n...
Military vehicle kits (injection)
Catalogue
£17.99
-
In Stock
5+
Italeri IT7511
M4A2 Sherman III includes 2 snap together vehicles
Military vehicle kits (injection)
Catalogue
£16.50
-
In Stock
5+
No Image
Italeri IT6584
Semovente M42 da 75/34 (NEW Tooling)
Military vehicle kits (injection)
Catalogue
£34.99
-
In Stock
20+
No Image
Italeri IT6570
Soviet T-34/76 Model 43 (NEW Tooling in 2022)
Military vehicle kits (injection)
Catalogue
£46.99
-
In Stock
12
No Image
Italeri IT6593
Pz.Kpfw.IV Ausf.F1/Ausf.F2/Ausf.G with Afrika Korps Infantry
Military vehicle kits (injection)
Catalogue
£39.99
-
In Stock
11
No Image
Italeri IT6592
Crusader Mk.III & British Tank Crew El Alamein 80th Anniversary
Military vehicle kits (injection)
Catalogue
£39.99
-
In Stock
2

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

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