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=303033 AND P.price_set = 1 
		 AND P.product_category_id = 102 
		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
Catalogue | Hannants
Hannants News Feed Subscribe To This Search | Create Search Alert Create Search Notification

Catalogue



Found 8 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
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
Freightdog FDR72159
Hawker Hunter P.1109A conversion (can be used to convert the aircraft on decal FSD72020) This item is a 1/72 scale resin conversion for use with the Revell Hunter F.6 or FGA.9 base kit (not included). The P.1109A was an early production F.Mk.6 Hunter used as an aerodynamic test best for the AI.20 radar equipped P.1109B, which could carry two Firestreak missiles. Set includes longer nose, straight leading edges, improved nose u...
Aircraft conversions (resin)
Catalogue
£12.00
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
Freightdog FDR72160
Folland Gnat T.1 prototype nose (can be used to convert the aircraft on decal FSD72020)
Aircraft conversions (resin)
Catalogue
£5.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

Freightdog FSD32001
Post-War RAF Thunderbolts (3) Republic P-47D Thunderbolt 'Razorback' HD154/18 73 OTU Egypt 1946 camouflage; 'Bubbletop' KL308 RS-C 30 Sqn India 1945; KL339 FL-O 81 Sq Java 1946
Aircraft decals (military)
Catalogue
£9.00
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

Freightdog FSD72003S
'Weekend Warriors' RAuxAF 1948-53. This sheet covers RAuxAF options between 1948 and 1953, all operated from South of England airfields. Covering four options, with enough roundels to complete all four. Aircraft covered are de Havilland Mosquito T3 RAO-L VT588 of 608 Sqn at Manston, Supermarine Spitfire Mk.XVIe SL727 of 601 Sqn 1949 at North Weald, Harvard T2B FX432 of 500 Sqn 1952 at West Malling and Supermarine Spitfire Mk.2...
Aircraft detailing sets (decal and resin)
Catalogue
£7.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

Freightdog FSD72004S
RAF Post-War (7) - Bristol Beaufighter Mk.X RD805/OB-J 45 Sqn, Malaya 1948, de Havilland Mosquito FB.6 RS679/AU-P 4 Sqn, RAF Celle, West Germany 1949, Hawker Tempest F Mk.6 NX135/V 6 Sqn, Supermarine Spitfire Mk.XVIII TZ203/J 208 sqn, MEAF Egypt 1949, Supermarine Spitfire Mk.XIX PS888 81 Sqn, RAF Selatar 1954 ('The Last!'), Canadair Sabre F.4 XB770/B, 4 Sqn RAF Jever, West Germany 1954 and a Bristol Brigand WB236/K from 84 Sqn...
Aircraft decals (military)
Catalogue
£13.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

Freightdog FSD72008
Post-War Supermarine Spitfires Part 1 (6) Supermarine Spitfire PR.Mk.IX PL915 C Flight Mediterranean and Middle East Communications Sqn RAF Heliopolis 1946; Supermarine Spitfire Mk.IXc PV119 RG-Q natural metal or PV120 RG-W Dk.Green/Ocena Grey/Med Sea Grey, both 208 Sqn RAF Palestine 1946; RK917 DB Douglas Bader Battle of Britain flypast 1945; ML124 GZ-S 32 Sqn P/O Harry Shaw 32 Sqn Palestine 1947; Supermarine Spitfire Mk.XVIe...
Aircraft decals (military)
Catalogue
£11.00
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

Freightdog FSD72017
1:72
RAF SEAC Insignia - A5 sheet covering SEAC dull roundel blue and India white roundels and fin flashes in standard and non-standard sizes, applicable to most RAF types operated in the Far East 1943-46.
Aircraft decals (military)
Catalogue
£7.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

No Image
Freightdog FSD72020
1:72
British Testing colours. High quality screen printed decal in 1/72 scale, print run 250 sheets. Includes decals for six options; Folland Gnat trainer prototype, Gnat T.1, BAC/EE Lightning T.4, Hawker Hunter T.12, Hunter P.1109A and Avro Shackleton MR.3 used in trials and testing work from the early 1960s to mid 1970s. Several of these options can be made with Freightdog conversion sets, available separately. Printed by Fantasy...
Aircraft decals (military)
Catalogue
£18.00
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