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_category_id = 125 
		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
Damaged | Hannants
Hannants News Feed Subscribe To This Search | Create Search Alert Create Search Notification

Damaged



Found 28 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

Category

Scale

Product Type

Manufacturer

Added Within

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

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
Orion Figures 72040ORI
Modern Army Israel (set 2). THESE ARE NEW BUT ARRIVED WITH WATER DAMAGED/STUCK BOXES. NOW FINISHED.
Figures (injection)
Damaged
£8.98
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
Revell RV45D67
Special Forces TIE Fighter Damaged box (same as RV6745) Crushed and torn corner of box NORMALLY é£44.99. SAVE é£10.01!!! Special Forces TIE Fighter
Film, Science Fiction or TV related
Damaged
£34.98
-
In Stock
1

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 1018TAK
Panzerjager IB with 7.5cm StuK 40 L/48 THIS IS NEW BUT HAS SUFFERED DAMAGE TO THE LID!!! SAVE é£15.00. NOW OUT OF PRODUCTION BUT STILL IN OUR STOCK WITH A GOOD BOX!!! Sturmgeschutz/StuG.III gun mounted on Panzer I Ausf.B hull. At least one example is known to have been built for the German Army in WWII. Hatches can be fitted opened or closed, workable tracks & road wheels, choice of 4 markings, metal barrel & figure included
Military vehicle kits (injection)
Damaged
£84.97
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
IsraDecal Studio 32042IC
RAFAEL Python 3 missiles for IAF McDonnell F-4E Phantom. Includes 2 missile with rails, mount adapters and decals (designed to be used with Revell and Tamiya kits) THIS IS NEW BUT ARRIVED IN A WET BOX. NOW DRIED OUT AND CONTENTS ARE FINE! SAVE é£6.00!!!
Aircraft detailing sets (resin)
Damaged
£19.30
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
Italeri 1450IT
Alenia C-27A Spartan This is new but arrived with a slightly marked box. Barely noticeable but we prefer to sell it a bit cheaper... Save é£10.00... SUPER DECALS SHEET FOR 5 VERSIONS - COLORS INSTRUCTIONS SHEET The Aeritalia G-222 twin-engine transport aircraft was designed in the late 1960s by the famous ing. Giuseppe Gabrielli, fighter designer such as G.55 and G. 91 and was born for a specific need of the Italian Air...
Aircraft kits (injection)
Damaged
£49.97
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
Italeri 5610IT
MTB 77 Vosper 72'6" This is new but arrived with a slightly marked box. Barely noticeable but we prefer to sell it a bit cheaper... Save é£10.00...
Ship kits, full hull (injection)
Damaged
£99.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
HPH Models AEROL-39
Aero L-39ZA Albatros This is a new HPH32018R that arrived with a Damaged box. SAVE é£45.00!!!
Aircraft kits (resin)
Damaged
£149.97
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
Squadron Signal BOOK245
No Scale
a slight damaged new copy of SQS34005 USS Lexington CV-2 (now out of print so be quick!)
Ship books
Damaged
£9.98
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
Squadron Signal BOOK246
a slightly damaged new copy of SQS39001 'Ferdinand'/Elefant [Sd.Kfz.184 'Elefant' Jagdpanzer Tiger]
Military vehicle books
Damaged
£9.98
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
Master Box MB3549
U.S. Civil War Series: Yankee Scout and Tracker THIS IS NEW BUT ARRIVED WITH A DAMAGED BOX! SAVE é£3.53!!!
Figures (injection)
Damaged
£8.97
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
I LOVE KIT 63526LK
US C-RAM with HEMTT A3 Damaged in transit. Save é£30.00!!! US Army Counter Rocket, Artillery and Mortar (C-RAM) "Centurion" land-based Phalanx rapid-fire, computer-controlled radar and 20mm gun counter-insurgent system which automatically intercepts rockets, artillery and mortar rounds in the air before impact. C-RAM has defended the Green Zone US base in Baghdad Iraq against more than 100 hostile incoming rockets and morta...
Military vehicle kits (injection)
Damaged
£99.97
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
A-Model AMU220D72
1:72
Antonov An-30 Damaged box on delivery (same as AMU72220) Ripped corner of box SAVE é£7.62!!! Antonov An-30 From Wikipedia... The Antonov An-30 is a derivative of the An-24 fitted with an entirely new fuselage forward of frame 11. The fuselage nose is extensively glazed, reminiscent of the Boeing B-29. Housed within the new nose section is the navigator and precise navigational equipment, including an optical sight for ensur...
Aircraft kits (injection)
Damaged
£49.98
-
In Stock
1

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
Valom VAL151D72
Curtiss C-46D Commando (JASDF) Damaged box on delivery (same as VAL72151) Ripped top of box SAVE é£11.52! Curtiss C-46D Commando (JASDF) with resin parts (engines)
Aircraft kits (injection)
Damaged
£64.98
-
In Stock
2

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
Dragon 92DN69
Sd.Kfz.166 Stu.Pz.IV 'Brummbƒ¤r' Early Production Damaged box on delivery (same as DN6992) Crushed corner of box SAVE é£10.01!!! Sd.Kfz.166 Stu.Pz.IV 'Brummbƒ¤r' Early Production
Military vehicle kits (injection)
Damaged
£64.98
-
In Stock
1

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
Revell RV59D56
Gift Set "Millennium Falcon" RotJ 40th Damaged box on delivery (same as RV5659) crushed corner of box SAVE é£10.01!!! Gift Set "Millennium Falcon" RotJ 40th
Film or TV related
Damaged
£69.98
-
In Stock
1

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

Caesar Miniatures (specials) CMH006
NO BOX!!! WWII Underground Resistance these are new but we didn't receive enough boxes. Now out of production.
Figures (injection)
Damaged
£9.98
-
In Stock
11

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
Meng Model SS-012
1:35
British Mastiff 2 6x6 Wheeled Protected Patrol Vehicle NEW KITS WHICH ARRIVED WITH DAMAGED BOXES! SAVE é£20.00!!!
Military vehicle kits (injection)
Damaged
£59.98
-
In Stock
4

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
Revell RV16D75
Mercedes 1625 TLF 24/50 Ziegler WAS é£78.99. TEMPORARILY SAVE 1/3RD!!! Damaged box (same as RV7516) Crushed corner of box Mercedes 1625 TLF 24/50 Ziegler WAS é£78.99. TEMPORARILY SAVE 1/3RD!!!
Fire engine kits (Injection)
Damaged
£52.66
-
In Stock
1

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
MiniArt MT158D35
BZ-38 Refueller Mod 1939 Damaged box on delivery (same as MT35158) ripped box lid NORMALLY é£54.99. SAVE é£10.01! BZ-38 Refueller Mod 1939
Military vehicle kits (injection)
Damaged
£44.98
-
In Stock
1

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
Revell RV50D36
North-American B-25 Mitchell (Easy-Click) Damaged box on delivery (same as RV3650) Crushed box on delivery WAS é£15.99. SAVE é£4.01!!! North-American B-25 Mitchell (Easy-Click)
Aircraft kits (injection)
Damaged
£11.98
-
In Stock
1

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
Dragon DN08D66
1:35
Pz.Kpfw.VI Tiger I Damaged box on delivery (same as DN6608) Hole in corner of box SAVE é£10.00!!! Pz.Kpfw.VI Tiger I Initial Production
Military vehicle kits (injection)
Damaged
£64.99
-
In Stock
1

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
AMT AMT35D12
American Superliner Semi Tractor Damaged box on delivery (same as AMT1235) crushed box on one side SAVE é£25.01!!! American Superliner Semi Tractor
Truck and trailer kits (injection)
Damaged
£99.98
-
In Stock
1

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
AMT AMT38D12
Ford LTL 9000 Semi Tractor Damaged on delivery (same as AMT1238) Crushed box SAVE é£25.01!!! Ford LTL 9000 Semi Tractor
Truck and trailer kits (injection)
Damaged
£99.98
-
In Stock
1

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
Trumpeter TU10205
1:35
Mil Mi-8MT/Mi-17 Hip-H *Damaged on delivery (same as TU05102) Damaged box SAVE é£18.01!!! Mil Mi-8MT/Mi-17 Hip-H Helicopter
Aircraft kits (injection)
Damaged
£89.98
-
In Stock
1

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
Dragon DN00D66
Pz.Kpfw.VI Ausf.E Tiger 1 Initial Production *damaged on delivery (same as DN6600) Damaged outer box SAVE é£10.01!!! Pz.Kpfw.VI Ausf.E Tiger 1 Initial Production s.Pz.Abt 502 Mishkino 1943
Military vehicle kits (injection)
Damaged
£69.98
-
In Stock
1

   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