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=1894 AND ps.id = 954 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 47 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
MiniArt MT37073
1:35
TRANSMISSION SET FOR Soviet T-55/T-55A (designed to be used with MiniArt kits)
Military vehicle detailing sets (injection)
Catalogue
£18.99
-
In Stock
5+
Meng Model MMSPS-004
Military vehicle Nuts and Bolts SET A large
Military vehicle detailing sets (injection)
Catalogue
£4.75
-
In Stock
7
MiniArt MT35321
CONTINENTAL R975 ENGINE Engine Compatible with: M3 LEE, M3A1 LEE, GRANT MK. I, M4 SHERMAN, M4A1 SHERMAN, M4A4T SHERMAN, RAM Mk.I, RAM Mk.II, M7 PRIEST HOWITZER, M18 HELLCAT, GRIZZLY I CRUISER, KANGAROO APC, M12, M40 GUN MOTOR CARRIAGE, SEXTON SPG (designed to be used with Dragon, Hobby Boss, Italeri and Tamiya kits)
Military vehicle detailing sets (injection)
Catalogue
£18.99
-
In Stock
4
MiniArt MT37047
U.S. Machine gun set with etched parts
Military vehicle detailing sets (injection)
Catalogue
£18.99
-
In Stock
4
No Image
Asuka Model AS35L14
1:35
U.S. JERRY CAN SET
Military vehicle detailing sets (injection)
Catalogue
£12.99
Out of Stock
0
No Image
Asuka Model AS35L38
WWII BRITISH ARMY ACCESSORIES
Military vehicle detailing sets (injection)
Catalogue
£15.99
-
In Stock
5
Meng Model MMSPS-028
1:35
Soviet V-84 Engine (For MMTS-014 Soviet T-90A & MMTS-028 Soviet T-72B3) (designed to be used with Meng-Model kits)
Military vehicle detailing sets (injection)
Catalogue
£19.99
-
Out of Stock
0
MiniArt MT35316
GERMAN ROCKETS 28cm WK Spr & 32cm WK FLAMM UNASSEMBLED PLASTIC MODEL KIT BOX CONTAINS MODELS OF GERMAN ROCKETS
Military vehicle detailing sets (injection)
Catalogue
£18.99
-
In Stock
2
No Image
AFV Club AFAG3525
German 15cm Heavy Infantry Gun Ammo Set (brass) Ammo set for 1/35 scale 15cm sIG 33 gun, also fitted to Pz.Kpfw.1B, Pz.Kpfw.II (Sf) etc. 8 brass shells (2 types 4 each), plus photo-etch fired & unfired shell case headstamps.
Military vehicle detailing sets (injection)
Catalogue
£15.99
-
In Stock
20+
No Image
AFV Club AFAG3534
British 95mm Howitzer Ammo Set (brass) Ammo set for 1/35 scale Churchill Mk V & Cromwell Mk IV. 16 brass shells (4 types 4 each), 4 x brass cartridges, photo-etch headstamps & decals.
Military vehicle detailing sets (injection)
Catalogue
£15.99
-
In Stock
20
No Image
AFV Club AFAG3543
German SFH18 Howitzer Ammunition (brass) Ammo set for 1/35 scale WWII German 15cm howitzer. 21 brass shells of type 19, 36 & 38 (3 each).
Military vehicle detailing sets (injection)
Catalogue
£15.99
-
In Stock
19
No Image
AFV Club AFAG3547
NATO M68/L7 105mm Tank Ammunition (brass) Ammo set for 1/35 scale US M68 105mm gun derived from Royal Ordnance L7, Cold War & later. Fitted to British Centurion from Mk 5/2, German Leopard 1, certain variants of US M48/M60. 12 brass shells of type M392, M416, M456A2, M494, M728, M774 plus photo-etch headstamps.
Military vehicle detailing sets (injection)
Catalogue
£15.99
-
In Stock
15
No Image
AFV Club AFAG3551
US 120mm Ammo Set (aluminum) Ammo set for 1/35 scale M256 gun of US M1A1/M1A2 Abrams. 10 aluminium shells of Type M829, M829A3, M830A1, M865, M1002 (2 each) & 2 x M1028 canisters, plus photo-etch headstamps.
Military vehicle detailing sets (injection)
Catalogue
£15.99
-
In Stock
13
No Image
AFV Club AFAG3555
US M1122 & M454 155mm Artillery Shells (aluminium) Ammo used in IDF M109A2 'Rochev' & US M109A6 'Paladin' turreted self-propelled howitzers. 16 aluminium shells of type M454 & M1122 (8 each) plus decals.
Military vehicle detailing sets (injection)
Catalogue
£12.99
-
In Stock
14
MiniArt MT37055
SLA HEAVY APC-54 with interior
Military vehicle detailing sets (injection)
Catalogue
£59.99
-
In Stock
3
No Image
MiniArt MT37066
SOVIET 6 T-54 LATE TRANSMISSION SET KIT CONTAINS MODEL OF TRANSMISSION SET FOR T-54 EARLY TYPE (designed to be used with MiniArt kits) MT37009 T-54A INTERIOR KIT MT37011 T-54B SOVIET MEDIUM TANK. EARLY PRODUCTION. INTERIOR KIT MT37013 TIRAN 4 SHARIR LATE TYPE. INTERIOR KIT MT37029 TIRAN 4 LATE TYPE. INTERIOR KIT
Military vehicle detailing sets (injection)
Catalogue
£18.99
-
In Stock
2
MiniArt MT35220
M3/M4 ROADWHEELS SET. WELDED TYPE AND PRESSED TYPE Kit contains unassembled plastic model of M3/M4 Roadwheels Suitable for VVSS suspension: Medium Tanks M3 Lee Series Medium Tanks M3 Grant Series Medium Tanks M4 Sherman Series All the vehicles with same chassis
Military vehicle detailing sets (injection)
Catalogue
£13.75
Out of Stock
0
MiniArt MT35250
1:35
Re-released! German Machine Gun set. Box contains models of german machineguns (MG 34, MG 42, ZB-53). Kit contains 84 plastic parts and 38 PE parts.
Military vehicle detailing sets (injection)
Catalogue
£10.99
-
In Stock
2
AFV Club AF35196
1:35
Detail Set for Sd.Kfz.164 Nashorn (designed to be used with AFV Club kits)
Military vehicle detailing sets (injection)
Catalogue
£16.99
-
In Stock
4
Trumpeter TU06611
1:35
LAV-III/Stryker Windscreen Units (designed to be used with Trumpeter kits)
Military vehicle detailing sets (injection)
Catalogue
£7.99
-
In Stock
3
Trumpeter TU06613
4WD M151 Protector Remote Weapon Station. The PROTECTOR 4WD M151 Remote Weapon Station is a remotely-controlled weapons station (RWS) that can be mounted to vehicles and stationary platforms.
Military vehicle detailing sets (injection)
Catalogue
£9.99
-
In Stock
4
MiniArt MT35253
1:35
Panzerfaust 30/60 SET
Military vehicle detailing sets (injection)
Catalogue
£16.99
-
In Stock
1
Riich Models RE30015
1:35
Running gear & Tracks set for Universal Carrier
Military vehicle detailing sets (injection)
Catalogue
£12.99
-
In Stock
6
Riich Models RE30014
1:35
German Antenna Set & GG400 Generator (Model kits x2)
Military vehicle detailing sets (injection)
Catalogue
£15.99
-
In Stock
5
AFV Club AFAC35005
German Sd.Kfz.251/1 Ausf.C/Ausf.D Half-track Periscopes (designed to be used with AFV Club kits)
Military vehicle detailing sets (injection)
Catalogue
£7.50
-
In Stock
3

   1   |   2      »      [2]

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