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=8069 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 13 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

Italeri IT0788
DAF 95 Master Truck The 'Concept 95/380' has been, for some time, the flagship product in the renowned DAF manufacturer's lineup, representing a further development of the Space Cab series. It was recognized by the market as a significant evolution compared to its predecessors. Indeed, numerous technical and aesthetic improvements were developed and adopted, greatly appreciated by users of road industrial vehicles, especially ...
Truck and trailer kits (injection)
New Arrivals
19/04/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

Italeri IT1312
1:72
Focke-Wulf Fw-190D-9 NEW DECALS EDITION: SUPER DECALS SHEET FOR 6 VERSIONS - COLOR INSTRUCTION SHEET FIGURIE INCLUDED - CONTAINS 1 MODEL The Focke-Wulf Fw 190 was one of the best aircraft produced during World War II. Manufactured in more than 20,000 units starting from 1941, it was, alongside the Messerschmitt Bf-109, the most renowned fighter aircraft of the Luftwaffe. In its early versions, such as the Focke-Wulf Fw 1...
Aircraft kits (injection)
New Arrivals
19/04/2024
£14.99
£13.49
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 IT6754
Pz.Kpfw.VI Tiger I Ausf.E late production. LINK-AND-LENGTH TRACKS OR GLUABLE RUBBER TRUCKS - DECALS SHEET FOR 4 VERSIONS - COLOR INSTRUCTION SHEET The Panzerkampfwagen VI Ausf. Tiger I is undoubtedly one of the most famous tanks of World War II. It was developed to provide the armored divisions of the Wehrmacht with a heavily armored and heavily armed tank to complement the more common medium tanks, Panzer III and Panzer IV...
Military vehicle kits (injection)
New Arrivals
19/04/2024
£29.99
£26.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
Italeri IT4715
FIAT 500 F 1968 upgraded edition IMPROVED DOOR HINGES - UPDATED STEERING SYSTEM Upgraded moulds - Opening hood and trunk - Opened or closed top - Opening doors - Steering wheels - Working suspensions - Rubber tires - Photoetched and chromed parts The "Nuova 500" was produced by Fiat from 1957 to 1975. It is a real icon on four wheels and one of the symbols of aâé-l"made in Italyaâé-. Its unique and recognizable design is fam...
Car kits (injection)
New Arrivals
12/04/2024
£125.00
£112.50
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

Italeri IT3645
Back in stock! Mercedes Benz 300SL Gullwing The Mercedes-Benz 300 SL was produced in 1954 by the German car manufacturer and it's a masterpiece on four-wheels for design, elegance and luxury. The 300 SL, where the acronym stands for Sport Leicht, (Sport Light), adopted innovative solutions for the time. The Mercedes 2 door coupe had a tubular frame, a front 2.996 cc longitudinal engine connected to a 4-speed transmission and t...
Car kits (injection)
New Arrivals
18/03/2024
£34.99
-
In Stock
5+

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 IT4713
1:12
Bugatti Roadster/Monte Carlo Steering wheels - Opening cowling - Highly detailed engine - Rubber tires and spare wheel - Chromed and Photoetched parts - Screws, tubes and artificial leather - Color Instruction Sheet The Bugatti Type 35 is one of the most iconic and revered racing cars in motor racing history, both technically and competitively. Ettore Bugatti's masterpiece was a unique mix of engineering skill, design, ele...
Car kits (injection)
New Arrivals
15/03/2024
£174.99
£157.49
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

Italeri IT7022
GMC DUKW 353 Amphibious truck. NEW SUPER DECALS SHEET FOR 4 VERSIONS - COLOR INSTRUCTION SHEET FIGURE NOT INCLUDED To meet the logistical needs of the US Army landing forces in transferring loads quickly and efficiently from ships to beaches, the American General Motor CCKW-353 2é½ t 6x6 amphibious version tactical transport truck was designed and built for this purpose. The basic vehicle components were the same as the ...
Military vehicle kits (injection)
New Arrivals
08/03/2024
£14.99
£13.49
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

Italeri IT6271
GMC 2 1/2 6x6 Truck NEW DECALS SHEET FOR 3 VERSIONS - COLOR INSTRUCTION SHEET FIGURE NOT INCLUDED The GMC 2 é½ ton truck was built in an exceptionally high number of units and can be considered the backbone of the logistical operations of the U.S. Army during World War II. Characterized by a very robust and reliable structure, its strength lay in its excellent off-road performance, thanks to its six-wheel drive and effec...
Military vehicle kits (injection)
New Arrivals
07/03/2024
£24.99
£22.49
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 IT3950
Back in stock! Scania R143 M500 Streamline 4x2
Truck and trailer kits (injection)
New Arrivals
06/03/2024
£59.99
-
In Stock
5+

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

Italeri IT0074
Messerschmitt Me-410 Hornisse. SUPER DECALS SHEET FOR 4 VERSIONS - COLOR INSTRUCTIONS SHEET FIGURE NOT INCLUDED The Messerschmitt Me 410 Hornisse was a low-wing twin-engine heavy fighter developed by the famous German aircraft manufacturer as an evolution of the previous Me-210. The heavy fighter was intended to replace the outdated Bf-110s in the Luftwaffe Squadrons. Powered by two 1,750 HP Daimler-Benz DB603 engines, i...
Aircraft kits (injection)
New Arrivals
12/02/2024
£19.99
£17.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

Italeri IT6364
M8 Greyhound NEW DECALS SHEET FOR 4 VERSIONS - COLOR INSTRUCTION SHEET At the beginning of World War II, the U.S. Army had lightweight and lightly armed vehicles, called "scout cars," for advanced reconnaissance activities. Developed during the war, the M-8 Greyhound armored car, characterized by increased protection and heavier armament, represented a turning point for the American army. The vehicle provided greater operat...
Military vehicle kits (injection)
New Arrivals
12/02/2024
£24.99
£22.49
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 IT2830
Junkers Ju-87G-1 'Stuka' Kanonenvogel SUPER DECALS SHEET FOR 4 VERSIONS - COLOR INSTRUCTION SHEET - PHOTO-ETCHED FRET INCLUDED FIGURE NOT INCLUDED The JU-87 dive bomber is certainly one of the best known aircraft of WW2. The characteristic "gull wingaâé- aircraft gained great notoriety at the beginning of the war, and is considered, together with the Panzer Divisions of the Wehrmacht, as defining symbols of the Blitzkrieg...
Aircraft kits (injection)
New Arrivals
09/02/2024
£37.99
£34.19
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 IT3965
Scania 770S V8 aâé-l"White Cabaâé- Scania S series a key player in the industrial and heavy commercial vehicles market has been designed to meet long-range logistics and transportation needs. The new S series was introduced by the famous Scandinavian manufacturer to the market in 2016, and included new innovative design solutions. Key design features were incorporated to optimize the use of the space inside the cabin to enhance dr...
Truck and trailer kits (injection)
New Arrivals
09/02/2024
£38.88
£34.99
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