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_manufacturer_id=362691 AND P.price_set = 1 
		 AND P.product_category_id = 105 
		AND pc.show_in_search = 1

	 AND (P.product_category_id = 103 OR P.price_set) GROUP BY P.id ORDER BY DATE_FORMAT(P.arrival_date,"%Y-%m-%d %H")   DESC  ,  P.code
New Arrivals | Hannants
Hannants News Feed Subscribe To This Search | Create Search Alert Create Search Notification

New Arrivals



Found 14 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
Delivered 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
Eduard kits EDK11181
OVERLORD: D-DAY MUSTANGS. This is a new tool Limited Edition, Dual Combo kit featuring the P-51B Mustang in 1/48 scale. kits) The kit will include decals for 10 machines that participated in the 1944 Normandy landings, as well as pre-painted photo-etched parts and masks.
Aircraft kits (injection)
New Arrivals
02/05/2024
£53.50
£48.15
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
Eduard kits EDK7473
Supermarine Spitfire Mk.IXc late 1/72 EDUARD-WEEKEND The Weekend edition kit of British fighter aircraft Spitfire Mk.IXc late in 1/72 scale. - plastic parts: Eduard - marking options: 4 - decals: Eduard - PE parts: no - painting mask: no - resin parts: no kits)
Aircraft kits (injection)
New Arrivals
02/05/2024
£11.99
£10.79
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
Eduard kits EDK84199
Supermarine Spitfire Mk.IXc late The Weekend edition kit of British fighter aircraft Spitfire Mk.IXc late in 1/48 scale. - plastic parts: Eduard - marking options: 4 - decals: Eduard - PE parts: no - painting mask: no - resin parts: no kits)
Aircraft kits (injection)
New Arrivals
02/05/2024
£19.60
£17.64
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
Eduard kits EDR0019
North-American P-51B Mustang EDUARD-ROYAL CLASS The Royal Class edition kit of the famous US fighter aircraft P-51B Mustang in 1/48 scale. The kit offers markings for 14 aircraft; 13 of them of US Air Force and one Mustang Mk.III of the RAF. The kit is prepared as a Dual Combo, it is extended version of kit 11181. Both kits will feature different packaging designs and box art. This kit will only be produced in numbers ac...
Aircraft kits (injection)
New Arrivals
02/05/2024
£79.99
£71.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
Eduard kits EDK2145
Messerschmitt Bf-109G GUSTAV pt. 2 DUAL COMBO The Limited edition of the kit of the famous German WWII fighter aircraft Bf-109G in 1/72 scale. The kit offers aircraft of G-6 late and G-14 versions with Erla haube canopy. - plastic parts: Eduard- marking options: 10- decals: Eduard- PE parts: yes, pre-painted- painting mask:yes- resin parts: no 1 / 10 COL A Messerschmitt Bf-109G-6/R6, WNr. 160303, Hptm. Friedrich Eberle, CO ...
Aircraft kits (injection)
New Arrivals
28/03/2024
£25.50
£22.95
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

Eduard kits EDK7077
RE-RELEASED! Grumman F6F-5 Hellcat 1/72 The ProfiPACK edition kit of US carrier based WWII fighter plane F6F-5 Hellcat in 1/72 scale.- plastic parts: Eduard- marking options: 4- decals: Eduard- PE parts: yes, pre-painted- painting mask: yes- resin parts: no 1 / 4 aâé-~Minsi III, BuNo 70143, Cdr. David McCampbell, USS Essex, October 25th, 1944 2 / 4 White 7 aâé-~Paper Doll, Lt. Carl A. Brown Jr., VF-27, USS Princeton, O...
Aircraft kits (injection)
New Arrivals
28/03/2024
£13.99
£12.59
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
Eduard kits EDK82203
1:48
Grumman F4F-4 Wildcat late 1/48 The ProfiPACK edition kit of the US carrier based WWII fighter plane F4F-4 Wildcat late in 1/48 scale. - plastic parts: Eduard- marking options: 6- decals: Eduard- PE parts: yes, pre-painted- painting mask: yes- resin parts: no 1 / 6 F4F-4 Wildcat late 1/48 - COL A Lt.(Jg) William E. Eder, VGF-29, USS Santee (CVE-29), November 1942 2 / 6 F4F-4 Wildcat late 1/48 - COL B BuNo.11992, Ens. Ler...
Aircraft kits (injection)
New Arrivals
28/03/2024
£28.65
£25.79
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
Eduard kits EDK8419
Fokker E.III EDUARD-WEEKEND The Weekend edition kit of German WWI fighter aircraft Fokker E.III in 1/48 scale. - plastic parts: Eduard - marking options: 4 - decals: Eduard - PE parts: no - painting mask: no - resin parts: no 1 / 4 Fokker E. III 1/48 - COL A E339/16, Lt. Josef Jacobs, Fokkerstaffel West, June 1916 2 / 4 Fokker E. III 1/48 - COL B E105/15, Ernst Udet, KeK Habsheim, 1916 3 / 4 Fokker E. III 1/48 - COL C...
Aircraft kits (injection)
New Arrivals
28/03/2024
£17.20
£15.48
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
Eduard kits EDK11179
Savoia-Marchetti SM.79 bomber in 1/48 scale. This is a short-run kit, originally produced for Classic Airframes in 2001 and released by Eduard in the Flashback series. EDUARD-LIMITED The Heavy Retro Limited edition of the kit of the famous Italian WWII three-engined medium bomber aircraft SM.79 Sparviero in 1/48 scale.The first edition of this kit was released in 2001. Sparviero (Sparrowhawk) served as a classic bomber or as ...
Aircraft kits (injection)
New Arrivals
02/03/2024
£59.99
£53.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

Eduard kits EDK8281
Re-released! Supermarine Spitfire Mk.IXc late version "The ProfiPACK edition kit of British fighter aircraft Spitfire Mk.IXc (late version) in 1/48 scale. REEDITION. - plastic parts: Eduard - No. of decal options: 6 - decals: Eduard - PE parts: yes, color - painting mask: yes - resin parts: no 1 / 6 Spitfire Mk. IXc pozdnƒ verze 1/48 - Spitfire LFMk.IXc, MH712, flown by W/O Henryk Dygala, No. 302 Squadron, Summer / Autumn,...
Aircraft kits (injection)
New Arrivals
02/03/2024
£28.65
£25.79
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
Eduard kits EDK11182
Northrop F-5 FREEDOM TIGER The Limited edition of the kit of the US supersonic light fighter Northrop F-5E in 1/48 scale.- plastic parts: AFV Club- marking options: 8- decals: Eduard- PE parts: yes, pre-painted- painting mask: yes - resin parts: yes, wheels, RWR antennae A 73-00878, 63rd Tactical Wing, RVNAF, Bien Hoa, South Vietnam, 1974 73-00878, Socialist Republic of Vietnam, 2023 renovation at The Aviation Museum Kbel...
Aircraft kits (injection)
New Arrivals
02/03/2024
£53.50
£48.15
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
Eduard kits EDK70156
1:72
Messerschmitt Bf-109G-2 The ProfiPACK edition kit of the German WWII fighter plane Bf-109G-2 in 1/72 scale. - plastic parts: Eduard - marking options: 6 - decals: Eduard - PE parts: yes, pre-painted - painting mask: yes - resin parts: no COL A Bf-109G-2, Maj. Johannes Trautloft, CO of JG 54, the Soviet Union, August 1942 COL B Bf-109G-2/R6, WNr. 13531, Hptm. Hermann Graf, CO of 9./JG 52, Pitomnik, the Soviet Union, Septemb...
Aircraft kits (injection)
New Arrivals
02/03/2024
£13.99
£12.59
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

Eduard kits EDK8078
Re-released! Messerschmitt Bf-108B The ProfiPACK edition kit of German WWII liasion aircraft Messerschmitt Bf-108 in 1/48 scale.- plastic parts: Eduard- marking options: 5- decals: Eduard- PE parts: yes, pre-painted- painting mask: yes- resin parts: no
Aircraft kits (injection)
New Arrivals
02/03/2024
£25.50
£22.95
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
Eduard kits EDK84118
Focke-Wulf Fw-190A-5 light fighter The Weekend edition kit of German WWII fighter aircraft Fw-190A-5 in 1/48 scale. The kit offers Fw-190A-5s armed with two cannons in the wings. - plastic parts: Eduard - marking options: 4 - decals: Eduard - PE parts: no - painting mask: no COL A Fw-190A-5, WNr. 2700, 2./JG 11, Husum, Germany, August 1943 COL B Fw-190A-5, WNr. 0152594, Maj. Hermann Graf, CO of JGr. Ost, Toulouse-Blagnac, ...
Aircraft kits (injection)
New Arrivals
02/03/2024
£19.60
£17.64
Out of Stock
0


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