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=362588 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 20 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

No Image
Mark I Decals DMF14412
Augusta-Bell AB-206A in Finnish AF (Bell OH-58A KIOWA)
Aircraft decals (military)
New Arrivals
03/04/2024
£3.60
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
Mark I Decals DMF14421
1:144
MD-500D in Finnish AF
Aircraft decals (military)
New Arrivals
03/04/2024
£3.60
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
Mark I Decals DMF14422
1:144
Mil Mi-4 in Finnish AF
Aircraft decals (military)
New Arrivals
03/04/2024
£3.60
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
Mark I Decals DMF7203
de Havilland Mosquito FB.VI 'Amiens Prison Raid'
Aircraft decals (military)
New Arrivals
03/04/2024
£11.20
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
Mark I Decals DMK144110
Baltic states air forces - insignia, 2 sets
Aircraft decals (military)
New Arrivals
03/04/2024
£3.60
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
Mark I Decals DMK144111
CzAF numerals - Black, White outline, Pt.2, 2 sets
Aircraft decals (military)
New Arrivals
03/04/2024
£3.20
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
Mark I Decals DMK7212
1:72
ROCAF/Republic Of China Air Force roundels, 2 sets
Aircraft decals (military)
New Arrivals
03/04/2024
£5.40
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
Mark I Decals DMK7213
Indian AF Insignia, 2 sets
Aircraft decals (military)
New Arrivals
03/04/2024
£7.25
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
Mark I Decals DMK7214
USAF lettering - Blue, 2 sets
Aircraft decals (military)
New Arrivals
03/04/2024
£7.25
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
Mark I Decals DMF14413
RAF Mustang roundels & fin flashes, 2 sets insignia diameters: Type A (40"), A1 (30,35"), B (32,40"), C (32,40"), C1 (36") scale diameters: Type A (7.0 mm), A1 (5.3;6.2 mm), B (5.6;7.0 mm), C (5.6;7.0 mm), C1 (6.4 mm) fin flashes: 24x24", 24x27" scale sizes: 4.2x4.2; 4.2x4.8 mm
Aircraft decals (military)
New Arrivals
07/03/2024
£3.60
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
Mark I Decals DMF14414
Typhoon roundels & fin flashes, 2 sets insignia diameters: Type A (32,42"), A1 (35,42"), B (35,50"), C (32,50"), C1 (36") scale diameters: Type A (5.6;7.4 mm), A1 (6.2;7.4 mm), B (6.2;8.8 mm), C (5.6;8.8 mm), C1 (6.4 mm) fin flash: 24x24" scale size: 4.2x4.2 mm
Aircraft decals (military)
New Arrivals
07/03/2024
£3.60
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
Mark I Decals DMF14415
1:144
Tempest roundels & fin flashes, 2 sets insignia diameters: Type B (50"), C (32,36,50"), C1 (36,50"), D (36,50") scale diameters: Type B (8.8 mm), C (5.6;6.4;8.8 mm), C1 (6.4;8.8 mm), D (6.4;8.8 mm) fin flashes: 24x24" scale size: 4.2x4.2 mm
Aircraft decals (military)
New Arrivals
07/03/2024
£3.60
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
Mark I Decals DMF14416
Mohawk, Tomahawk & Kittyhawk roundels & fin flashes, 2 sets insignia diameters: Type A (38,45"), A1 (35,45"), B (48"), C (32,35"), C1 (36") scale diameters: Type A (6.7;7.9 mm), A1 (6.2;7.9 mm), B (8.5 mm), C (5.6;6.2 mm), C1 (6.4 mm) fin flashes: 24x24", 24x27" scale sizes: 4.2x4.2; 4.2x4.8 mm
Aircraft decals (military)
New Arrivals
07/03/2024
£3.60
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
Mark I Decals DMF14417
1:144
Hurricane roundels & fin flashes (Pt.2), 2 sets Hurricane roundels & fin flashes (Pt. 2), 2 sets insignia diameters: Type B (32,36,49"), B1 (49"), C (32,49"), C1 (18,36"), SEAC (16") scale diameters: Type B (5.6;6.4;8.5 mm), B1 (8.5 mm), C (5.6;8.5 mm), C1 (3.2;6.4 mm), SEAC (2.8 mm) fin flashes: 18x18", 24x24", SEAC 16x24" scale sizes: 3.2x3.2; 4.2x4.2; 2.8x4.2 mm
Aircraft decals (military)
New Arrivals
07/03/2024
£3.60
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
Mark I Decals DMF14418
Fw-190 crosses & swastikas, 2 sets Balkenkreuz sizes: 600,800,900 mm scale sizes: 4.2;5.6;6.3 mm Swastikas: size 420,480,530 mm scale sizes: 2.9;3.3;3.7 mm
Aircraft decals (military)
New Arrivals
07/03/2024
£3.60
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
Mark I Decals DMF14419
Unarmed Hawker Hurricanes Decals for the following aircraft are included: Hurricane Mk.I, G-AFKX (ex-L1606), Hawker Aircraft demonstrator & trials a/c Hurricane Mk.IIC(Met), PZ818, 5O-K, No.521 Sq., Royal Air Force Hurricane Mk.IIC, LF363, Fighter Command Communications Sq., Royal Air Force Hurricane Mk.IIC/Trop, 2-13, Training School, Imperial Iranian Air Force These decals were designed to match any 1/144 Hawker Hurric...
Aircraft decals (military)
New Arrivals
07/03/2024
£5.40
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
Mark I Decals DMF14420
RAF Airacobra roundels & fin flashes, 2 sets insignia diameters: Type A (25.5,30"), A1 (34"), B (48"), C (32"), C1 (36") scale diameters: Type A (4.5;5.3 mm), A1 (6.0 mm), B (8.5 mm), C (5.6 mm), C1 (6.4 mm) fin flashes: 24x22", 24x27", slanted fin flash: 24x27" scale sizes: 4.2x3.9; 4.2x4.8 mm
Aircraft decals (military)
New Arrivals
07/03/2024
£3.60
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
Mark I Decals DMF7202
Unarmed Hawker Hurricanes Mk.I/Mk.IIC Unarmed Hurricanes Decals for the following aircraft are included: Hurricane Mk.I, G-AFKX (ex-L1606), Hawker Aircraft demonstrator & trials a/c Hurricane Mk.IIC(Met), PZ818, 5O-K, No.521 Sq., Royal Air Force Hurricane Mk.IIC, LF363, Fighter Command Communications Sq., Royal Air Force Hurricane Mk.IIC/Trop, 2-13, Training School, Imperial Iranian Air Force These decals were designed to...
Aircraft decals (military)
New Arrivals
07/03/2024
£11.20
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
Mark I Decals DMK144108
Irish Air Corps insignia 1939-54, 2 sets
Aircraft decals (military)
New Arrivals
07/03/2024
£3.60
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
Mark I Decals DMK144109
Irish Air Corps insignia 1954-present, 2 sets
Aircraft decals (military)
New Arrivals
07/03/2024
£3.60
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