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=7939 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 117 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
Begemot BT48057
1:48
Sukhoi Su-30SM Flankers from Irkutsk. Decal pack with options to make all known Su-30SMs - prototypes and all Su-30SMs from the Russian Air Force, Armenian AF, Belarussian AF, Kazakh AF, Su-30MKI form Indian AF, Su-30MKA from Algeria AF, Su-30MKM from Malazian AF and Su-30SME form Myanmar AF. Including six big size decals sheet, 13-pages big size instruction.
Aircraft decals (military)
Catalogue
£23.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
Begemot BT72066
Petlyakov Pe-2... with opportunity make 108 marking variations of Pe-2 family aircraft's all types, from next AF's: VVS RKKA, Finland, Poland, Yugoslavia, Czechoslovakia, Bulgaria and Hungary. Including two big size decals, one small size decal and 23-pages booklet A4-size instruction.
Aircraft decals (military)
Catalogue
£19.40
-
In Stock
15

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
Begemot BT72069
Kamov Ka-52 - with the opportunity make 54 marking variations of Ka-52 Alligator family helicopter all types, from next AF's: Russia and Egypt. Including one big size decal, one small size decal and 11-pages instruction A3-size instruction http://www.begemotdecals.ru/doc/72-069%20Ka-52%20ins1.pdf http://www.begemotdecals.ru/doc/72-069%20Ka-52%20ins2.pdf
Aircraft decals (military)
Catalogue
£16.99
-
In Stock
9

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
Begemot BT72074
Yakovlev Yak-9 with the opportunity make 67 marking variations of Yak-9 family aircraft's all types, from next AF's: USSR. Including one big size decal, one small size decal and 11-pages instruction A3-size instruction. http://www.begemotdecals.ru/doc/72-074%20Yak-9%20ins%201.pdf http://www.begemotdecals.ru/doc/72-074%20Yak-9%20ins%202.pdf
Military vehicle decals
Catalogue
£16.99
-
In Stock
14

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
Begemot BT48032
1:48
Grumman F9F Panther Decal with opportunity make 12 marking variations of F9F Panther from US NAVY and MARINES aviation and Argentinian naval aviation. In decal consist full stencil set for one aircraft. Including two big size decals sheet and 4-pages big size instruction. [F9F-2 F9F-2B F9F-3 F9F-5 F9F-5KD]
Aircraft decals (military)
Catalogue
£19.40
-
In Stock
14

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
Begemot BT35-003
Russian car registration plates, including military
Military vehicle decals
Catalogue
£8.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
Begemot BT35-004
1:35
St George ribbon on the Russian tanks
Military vehicle decals
Catalogue
£11.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

Begemot BT72027
1:72
Sukhoi Su-27 Flankers (20) Includes `Soviet Knights' five versions and various Squadrons, most in blue, blue grey camouflage schemes
Aircraft decals (military)
Catalogue
£10.70
-
In Stock
4

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
Begemot BT72054
Grumman F9F Panther 12 marking variations of F9F Panther from US NAVY and MARINES aviation and Argentinian naval aviation. In decal consist full stencil set for one aircraft. Including one big size decals sheet and 4-pages big size instruction. [F9F-2 F9F-2B F9F-3 F9F-5 F9F-5KD]
Aircraft decals (military)
Catalogue
£16.99
-
In Stock
15

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
Begemot BT72086
1:72
St George ribbon on the Russian tanks. St. George ribbons on armored vehicles and vehicles of the Armed Forces of Russia, a set of two different widths of two shades and stars of various designs and sizes.Including one big size decal, one standard size decal sheet.
Military vehicle decals
Catalogue
£8.99
-
In Stock
7

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

Begemot BT48025
Re-printed! Yakovlev Yak-3 family. Decal set consist from 4 big decal sheets andchoice of 68 versions this best Soviet fighter of the WWII in Red Army AF, Soviet AF, Yugoslavia, France and Poland color schemes.
Aircraft decals (military)
Catalogue
£16.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

Begemot BT48041
Re-printed! Petlyakov Pe-2 family. 108 markings for different modification Pe-2 family aircrafts from VVS RKKA, Finland, Yugoslavian, Poland, Bulgarian and Hungary Air Forces.
Aircraft decals (military)
Catalogue
£21.40
-
In Stock
3

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

Begemot BT72047
Re-printed! Yakovlev Yak-3 family. Decal set consist from 2 big decal sheets and 8 format A3 instruction pages. Fantastic choice from 68 versions this best Soviet WW II fighter. Stencils for 3 model kits.
Aircraft decals (military)
Catalogue
£14.99
-
In Stock
4

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

Begemot BT48005
Re-printed! Sukhoi Su-27 Flanker family Part 2 (24) Variety of camouflage schemes operated by Russia; Vietnam; Belarus; Ukrania; Indonesia; Kazakstan; Ethiopia; Single and Two Seaters, Aerobatic Team etc. Inc large paint mask
Aircraft decals (military)
Catalogue
£10.70
-
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
Begemot BT72068
1:72
Mil Mi-24P Decal with opportunity make 51 marking variations Mi-24P helicopter. Full stencil data for one helicopter and variouse external armament. Including two big decal sheet, one small size decal sheet and four-pages A3 instruction.
Aircraft decals (military)
Catalogue
£14.99
-
In Stock
1

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
Begemot BT48056
Registration numbers, all this type 2018. Including six big size decals and 1-pages A4-size instruction.
Aircraft decals (military)
Catalogue
£19.40
-
In Stock
15

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
Begemot BT48055
Lavochkin La-9/La-11 Decal with opportunity make 29 marking variations of La-9 and La-11 family aircraft's all types, from next AF's: USSR, China, North Korea, Romania and Indonesia. 4 sets of full stencil for La-9(11) aircraft included. Including two big size decals and 5-pages instruction A3-size instruction.
Aircraft decals (military)
Catalogue
£19.40
-
In Stock
7

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

Begemot BT350-001
Re-printed! Italian Navy Flags and Markings
Ship decals
Catalogue
£2.99
-
In Stock
13

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

Begemot BT350-003
Re-printed! Japan Navy Navy Flags and Markings.
Ship decals
Catalogue
£2.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

Begemot BT350-004
1:350
Re-printed! German Kriegsmarine flags and markings
Ship decals
Catalogue
£2.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

Begemot BT350-005
Re-printed! Royal Navy/RN flags and markings
Ship decals
Catalogue
£3.99
-
In Stock
4

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

Begemot BT350-006
Re-printed! U.S. Navy Navy Flags and Markings.
Ship decals
Catalogue
£3.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

Begemot BT48008
Re-printed! Sukhoi Su-27 Family 'Demo-Flankers'. Includes 2 big decal sheet and 1 small decal sheet for 45 aircraft and 20 page A4 instruction booklet
Aircraft decals (military)
Catalogue
£14.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
Begemot BT72078
Curtiss P-40 Warhawk CCCP. Decal with opportunity make 41 marking variations of Curtiss é -40 aircraft of all modication from VVS RKKA. Including one big size decals and 9-pages big size instruction. http://www.begemotdecals.ru/doc/72-078%20P-40%20in%20USSR%20ins1.pdf http://www.begemotdecals.ru/doc/72-078%20P-40%20in%20USSR%20ins2.pdf [Tomahawk Mk.IIA Mk.IIB P-40C P-40E P-40K-1 P-40N]
Aircraft decals (military)
Catalogue
£16.99
-
In Stock
2

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

Begemot BT48039
Re-printed! Sukhoi Su-35S new generation of the Flankeréé½
Aircraft decals (military)
Catalogue
£19.40
-
In Stock
2

   1   |   2   |   3   |   4   |   5      »      [5]

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