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=3076 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 104 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
Price ASC DESC
Watch
Stock ASC DESC
Buy
No Image
Carley float for 40 people " Type 2 3D-Printed
Ship detailing sets (resin)
Catalogue
£5.30
-
In Stock
5+
No Image
Carley float for 60 people " Type 1 3D-Printed 1/350
Ship detailing sets (resin)
Catalogue
£5.99
-
In Stock
5
No Image
Vickers 40 mm automatic gun
Ship detailing sets (resin)
Catalogue
£7.99
£7.19
Out of Stock
0
No Image
PT Boat Weapon Set No.6 - Bofors 40mm AA/AS Gun 1/72 / (designed to be used with Revell kits)s. The 40mm Bofors gun was a standard and universal anti-tank and anti-vessel armament widely used on US Navy boats as well as with other allied fleets. Among other boats, the gun was mounted on the stern of the US PT Boats of the later production standards. Our set is employs several production technologies and contails resin cast par...
Ship detailing sets (resin)
Catalogue
£10.80
-
In Stock
5+
No Image
PT-579/588 Engine Compartment Ventilation 1/72 / (designed to be used with Revell kits)s Resin cast set that directly replaces the styrene parts PT-579/588 boat ventilation fan parts and these new parts offer much better levels of detail compared to the kit items.
Ship detailing sets (resin)
Catalogue
£8.55
-
In Stock
5+
No Image
PT-109 Boat Weapon Set No.3 - 37 mm Gun M3 1/72 / (designed to be used with Revell kits) s The main fire armament of the PT-109 boats was the US-made 37mm M3 anti-tank cannon carried on supporting arms and was mounted on the bow behind a shield. The kit's styrene barrel can be replaced with a new, resin made, which also offers much more detail and comes with etches.
Ship detailing sets (resin)
Catalogue
£6.30
-
In Stock
5+
No Image
PT Boat Weapon Set No.5 - 37 mm M4 Autocannon 1/72 / (designed to be used with Revell kits) s The later batches of the PT boats had the 37mm M4 automatic cannon mounted on their bows, which was the very same armament as used on the P-39 fighter aeroplanes. Our resin replica is much better detailed that that of the original kit's styrene item.
Ship detailing sets (resin)
Catalogue
£7.20
-
In Stock
5+
No Image
PT Boat Weapon Set No.2 - Twin 12.7 mm AA Brownings (2 printed pcs) A pair of nicely detailed twin-mounted machine guns to be used on the US PT Boats as made by Revell. Compared to the original styrene parts, our set offers much more details. (designed to be used with Revell kits)[PT-160 PT-588/PT-579 PT-109 Patrol]
Ship detailing sets (resin)
Catalogue
£6.30
-
In Stock
5
No Image
PT-579/588 Boat Radar Mast (designed to be used with Revell kits) Resin cast detailed antenna mast with a radar as fitted to the PT-579/588 boats. The set comes with a fret of etches. The detail overall is much better than on the original kit's item.
Ship detailing sets (resin)
Catalogue
£7.40
-
In Stock
5+
No Image
PT-109 Patrol Torpedo Boat Weapon Set No.1 - Mk.4 20mm Oerlikon cannon (designed to be used with Revell kits)
Ship detailing sets (resin)
Catalogue
£8.60
-
In Stock
5+
No Image
PT-109 Patrol Torpedo Boat Weapon Set No.4 - Mk.50 Rocket Launcher (2 pcs.) (designed to be used with Revell kits)
Ship detailing sets (resin)
Catalogue
£10.99
-
In Stock
5+
No Image
Balsa life raft 3D-Printed 1/350 (February release expected in stock lat e January 2023)
Ship detailing sets (resin)
Catalogue
£5.99
-
In Stock
3
No Image
Carley float for 60 people " Type 2 3D-Printed 1/350
Ship detailing sets (resin)
Catalogue
£5.99
-
In Stock
4
No Image
Carley float for 25 people " Type 1 3D-Printed 1/350 kits)
Ship detailing sets (resin)
Catalogue
£5.30
-
In Stock
5
No Image
Carley float for 25 people " Type 2 3D-Printed 1/350 kits)
Ship detailing sets (resin)
Catalogue
£5.30
-
In Stock
5
No Image
Vought OS2U-3 Kingfisher 3D-Printed x 2
Ship detailing sets (resin)
Catalogue
£7.99
-
In Stock
3
1:72
H.M.S. Snowberry (The Flower Class Corvette) Wooden Reversible Life Raft - this super-detailed kit in etched brass and cast metal constructs the Back in stock! Merchant Shipping pattern of rectangular raft carried mostly on Canadian corvettes [often singly]. These were fitted with internal tanks [included] for supplies of fresh water and condensed milk. Kit is also supplied with paddles and roping (designed to be used with ...
Ship detailing sets (resin)
Catalogue
£8.10
-
In Stock
5+
No Image
USN searchlights 12 inch 3D-Printed 1/350
Ship detailing sets (resin)
Catalogue
£5.30
-
In Stock
3
No Image
5"/25 caliber gun Mk.10 3D-Printed 1/350
Ship detailing sets (resin)
Catalogue
£9.20
-
In Stock
5+
Back in stock! Type IXC torpedo Bow Torpedo with loading Winch and Cart [designed to be used with Revell kits) Just another set in our series of arguably the world's best resin cast sets designed for German submarine models. This set has been designed for the U-Boot Type IX and offers the modeller with the option to show in open position the deck hatch which allowed the submarine's forward torpedoes to be put inside the vessel...
Ship detailing sets (resin)
Catalogue
£27.20
-
In Stock
5+
PT-109 Patrol Torpedo Boat Mk.6 Depth Charges (2pcs) (designed to be used with Revell kits)
Ship detailing sets (resin)
Catalogue
£9.20
-
In Stock
5+
No Image
Carley float for 10 people " Type 1 3D-Printed 1/350 kits)
Ship detailing sets (resin)
Catalogue
£5.30
-
In Stock
3
No Image
1:350
Carley float for 10 people " Type 2 3D-Printed 1/350 kits)
Ship detailing sets (resin)
Catalogue
£5.30
-
In Stock
3
No Image
M2 Browning water cooled 3D-Printed 1/350
Ship detailing sets (resin)
Catalogue
£5.30
-
In Stock
5+
No Image
USN searchlights 24 inch 3D-Printed 1/350
Ship detailing sets (resin)
Catalogue
£5.30
-
In Stock
5+