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=362427 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 11 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
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

Caracal Models CD144001
RE-PRINTED!!! USAF Boeing KC-135R Stratotanker. The workhorse of the USAF tanker fleet since the 1960s, the KC-135 Stratotanker will be serving the USAF (and its allies) well into the 2010s. Our latest decal sheet is a very comprehensive tribute to the current version of the Stratotanker : the KC-135R. Our first 1/144 scale sheet is a scaled-down version of our best-selling 1/72 KC-135R sheet. This extensive set features mark...
Aircraft decals (military)
New Arrivals
15/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

No Image
Caracal Models CD32025
USAF Lockheed-Martin F-16D Fighting Falcon "Mig Killer" Anniversary desert camouflage markings for F-16D Mig Killer 90-0778 - limited edition of 250 sheets only. Accurate stencilling and national insignia for this specific jet are included. This decal sheet was professionally screen printed by Microscale in the US.
Aircraft decals (military)
New Arrivals
15/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

Caracal Models CD48028
RE-PRINTED!!! Air National Guard Fairchild A-10C Warthogs. Current ANG Fairchild A-10C markings for Arkansas ANG (184FS), Idaho ANG(190FS), Indiana ANG(163FS) and Michigan ANG(107FS). Our first decal sheet for the A-10 features four options for the recently upgraded Fairchild A-10C version of the venerable Warthog. The options on this sheet are: Fairchild A-10C 82-0661, 163 FS, Indiana ANG (commander's aircraft) Fairchild A...
Aircraft decals (military)
New Arrivals
15/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

Caracal Models CD48034
RE-PRINTED!!! Air National Guard Fairchild A-10C Part 2: The options on this sheet are: Fairchild A-10C 80-0188, 188 FW, Arkansas ANG (commander's aircraft) Fairchild A-10C 78-0614, 188 FW, Arkansas ANG Fairchild A-10C 79-0193, 127 FW, Michigan ANG Fairchild A-10C 80-0255, 127 FW, Michigan ANG
Aircraft decals (military)
New Arrivals
15/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

No Image
Caracal Models CD48186
Republic F-105B/F-105D Thunderchief Multiple marking options for USAF single-seat Thuds. The F-105 Thunderchief, affectionately known as the "Thud," held a prominent position in the USAF fleet from the late 1950s through the 1980s. With its robust design and remarkable strike capabilities, the F-105 played a vital role in various combat scenarios and strategic missions, leaving an indelible mark on aviation history. Renowned ...
Aircraft decals (military)
New Arrivals
15/03/2024
£15.99
£14.39
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
Caracal Models CD48198
Fairchild A-10A Thunderbolt II - The Early Years Multiple marking options for prototype and test Fairchild A-10 Warthogs. Our "The Early Years" series continues with a sheet that transports us back to the origins of the 'Warthog' : the iconic A-10 Thunderbolt, a ground-attack aircraft that has seen service across various battlefields and is loved by infantry for its close-air-support capabilities. Before its unparalleled comb...
Aircraft decals (military)
New Arrivals
15/03/2024
£15.99
£14.39
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
Caracal Models CD48223
JANUARY 2024 RELEASE! General-Dynamics F-111D Aardvark Multiple marking options. The F-111D was intended to be a significant advancement of the basic F-111A. This variant of the F-111 family incorporated state-of-the-art avionics which proved to be difficult to maintain during the early years of its service history. Once the initial reliability problems were solved and the systems were refined, the F-111D offered unparallel...
Aircraft decals (military)
New Arrivals
15/03/2024
£15.99
£14.39
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
Caracal Models CD72123
USMC Lockheed C-130J "Fat Albert" This new 1/72 decal sheet features markings for the latest "Fat Albert" : a former-RAF C-130J which took over the job in 2020 after the retirement of the hard-working C-130T which has been in service for almost 20 years. We provide complete and accurate markings for this iconic aircraft that supports the US Navy aerial demonstration team "Blue Angels". The sole option on this sheet is: C-130...
Aircraft decals (military)
New Arrivals
15/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

No Image
Caracal Models CD72144
USAF Republic F-105B/F-105D Thunderchief Multiple marking options for USAF F-105B/D Thunderchiefs. Peg" program. The F-105 Thunderchief, affectionately known as the "Thud," held a prominent position in the USAF fleet from the late 1950s through the 1980s. With its robust design and remarkable strike capabilities, the F-105 played a vital role in various combat scenarios and strategic missions, leaving an indelible mark on avi...
Aircraft decals (military)
New Arrivals
15/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

No Image
Caracal Models CD72146
JANUARY 2024 PRINTED!!! USAF CT ANG Lockheed C-130H Hercules "Black Lightning" The Connecticut Air National Guard (CT ANG) holds a storied heritage. Originating in 1923, the CT ANG has been a part of many critical moments in history, from defending the Eastern Seaboard during World War II to engaging in modern operations overseas. In order to commemorate the 100 years of CT ANG, the 103rd Airlift Wing chose to decorate one of...
Aircraft decals (military)
New Arrivals
15/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

No Image
Caracal Models CD72147
General-Dynamics F-111D Aardvark The F-111D was intended to be a significant advancement of the basic F-111A. This variant of the F-111 family incorporated state-of-the-art avionics which proved to be difficult to maintain during the early years of its service history. Once the initial reliability problems were solved and the systems were refined, the F-111D offered unparalleled navigation and targeting capabilities for its t...
Aircraft decals (military)
New Arrivals
15/03/2024
£14.99
£13.49
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