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 ps.id = 956 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 80 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
Metallic Details MDMDR7278
Vickers 40 mm automatic gun
Ship detailing sets (resin)
Catalogue
£7.99
£7.19
Out of Stock
0
No Image
CMK/Czech Master Kits CMKN72044
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
CMK/Czech Master Kits CMKN72043
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
CMK/Czech Master Kits CMKN72032
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
CMK/Czech Master Kits CMKN72042
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
CMK/Czech Master Kits CMKN72031
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
CMK/Czech Master Kits CMKN72041
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
CMK/Czech Master Kits CMKN72030
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
CMK/Czech Master Kits CMKN72040
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+
Great Little Ships GLSA72015
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+
CMK/Czech Master Kits CMKN72019
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+
CMK/Czech Master Kits CMKN72035
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
CMK/Czech Master Kits CMKN72039
PT-588/PT-579 Patrol Torpedo Boat Life Rafts (designed to be used with Revell kits) Resin cast life raft with better details thoughout, surpassing the original Revell kit item
Ship detailing sets (resin)
Catalogue
£6.30
-
In Stock
5
No Image
Great Little Ships GLSA72026
Flower Class Corvette 11" Bilge Keels. The bilge keels on the Revell kit are complete works of fiction and this set of replacement keels will rectify this. The corvettes were initially fitted with these 11" keels and they apply to all the short forecastle vessels, as well as many long forecastle corvettes, as built. (designed to be used with Revell H.M.S. Snowberry (The Flower Class Corvettekits)
Ship detailing sets (resin)
Catalogue
£13.50
-
In Stock
1
No Image
Great Little Ships GLSA72027
1:72
Flower Class Corvette 22" Bilge Keels The bilge keels on the Revell kit are complete works of fiction and these sets of replacement keels will rectify this. The corvettes were initially fitted with 11" keels but it was often said by crews that they would 'roll on wet grass', and in an attempt to limit this tendency many boats were fitted with 22" keels later on in the war. As can be appreciated, these much deeper keels requi...
Ship detailing sets (resin)
Catalogue
£22.20
-
In Stock
5
No Image
Great Little Ships GLSA72028
Flower Class Corvette 2 RN/RCN Reversible Wooden Life Raft.This super-detailed kit in etched brass and cast metal constructs the merchant shipping pattern of rectangular raft carried mostly on Canadian corvettes, often singly. These were fitted with internal tanks, which are included, for supplies of fresh water and condensed milk. Kit is also supplied with paddles and roping (designed to be used with Revell H.M.S. Snowberry (...
Ship detailing sets (resin)
Catalogue
£13.20
-
In Stock
6
No Image
Metallic Details MDMDR7251
Twin 20 mm Oerlikon fixed mount guns
Ship detailing sets (resin)
Catalogue
£7.40
Out of Stock
0
No Image
Metallic Details MDMDR7252
Twin 40 mm Bofors guns
Ship detailing sets (resin)
Catalogue
£22.25
-
In Stock
5+
No Image
Planet Models PLAMV126
1:72
15 cm TbtsK C/36 'WWII German Atlantic Wall Gun'. German naval gun in the standard style gun turret was originally developed as main armament of German destroyers and secondary armament of heavier vessels including the planned battle ships of the Kriegsmarine. Following some modifications, most of the built gun turrets was used in the concrete casemates of the Atlantic Wall. Because of these modifications, the turrets differed...
Ship detailing sets (resin)
Catalogue
£25.99
-
In Stock
3
No Image
Planet Models PLAMV125
15cm TbtsK C/36 'WWII German Destroyer Gun' German naval gun in a standard style gun turret was originally developed as main armament of German destroyers and secondary armament of heavier vessels including the planned battle ships of the Kriegsmarine. Finely detailed resin cast parts come with a fret of etches.
Ship detailing sets (resin)
Catalogue
£25.99
-
In Stock
4
No Image
CMK/Czech Master Kits CMKN72037
German Speed Boat Life Raft / Schnellboot Rettungs Floss. This life raft set consists of resin parts accompanied by a fret of photo etches. The raft itself offers much more detail than those styrene ones from Revell's E-Boot and Schnellboot kits and is a rather straightforward replacement. The German fast boats were equipped with a special style of the life raft. (designed to be used with Revell kits)
Ship detailing sets (resin)
Catalogue
£6.99
-
In Stock
4
No Image
CMK/Czech Master Kits CMKN72038
German Kriegsmarine Life Raft / Rettungs Floss. Set of a life raft, resin parts come along with a fret of etches. The raft was usually used on various Kriegsmarine vessels, from the smallest kutters to the heaviest battleships (designed to be used with Revell kits)
Ship detailing sets (resin)
Catalogue
£6.99
-
In Stock
4
CMK/Czech Master Kits CMKN72013
1:72
Type IXC exterior set part 1 [designed to be used with Revell kits) (U-Boot/U-Boat/U Boat/U Boot/Submarines] In the early years of the war, enemy's submarines would attack and sink solitary ships with their guns while emerged on the surface, thus saving their precious torpedoes. This set brings open deck hatches and open 105mm cannon ammo box. The parts were 3D designed and masters produced on a 3D printer.
Ship detailing sets (resin)
Catalogue
£27.20
-
Out of Stock
0
CMK/Czech Master Kits CMKN72036
PT-109 Patrol Torpedo Boat Deck Set (Smoke Generator, Ventilation Funnels) (designed to be used with Revell kits)
Ship detailing sets (resin)
Catalogue
£6.30
-
In Stock
5+
CMK/Czech Master Kits CMKN72033
PT-109 Patrol Torpedo Boat Life Raft This inflatable transport and life raft comes in the form of a nicely detailed resin cast model tailored for the Revell's PT-109 torpedo boat. The set contains also paddles. (designed to be used with Revell kits)
Ship detailing sets (resin)
Catalogue
£6.30
-
In Stock
5

   1   |   2   |   3   |   4      »      [4]

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