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=362755 AND ps.id = 956 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

AOA Decals AOA72006
Re-printed! USAF/VNAF Fairchild AC-119G Shadows & AC-119K Stingers in the Vietnam War. This 1/72 decal sheet includes marking options for AC-119G Shadow and AC-119K Stinger gunships in the Vietnam War as operated by the United States Air Force (USAF) and the South Vietnam Air Force (VNAF). Although the AC-119G Shadow variant is not available in kit form, the Shadow can be easily represented by relatively basic modifications to...
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

AOA Decals AOASS01
1:32, 1:48
Re-printed! Devil 501 & Devil 505 - USN Grumman A-6E Intruders on the Big Screen (1/32, 1/48, & 1/72 combo special sheet) In late 1989, A-6E TRAM Intruders from VA-165 Boomers were modified to represent the earlier A-6A from VA-196 Main Battery during a fictitious 1972 USS Independence Vietnam War cruise. This small special sheet includes decals for three scales - 1/32, 1/48, & 1/72 - and includes the exterior markings to ...
Aircraft decals (military)
Catalogue
£14.50
-
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
AOA Decals AOA72002
1:72
Intruder Sandbox - USN/USMC Grumman A-6E Intruders in Libya Strikes & Desert Storm 8 Marking Options Included: VA-34 Blue Blasters (2 options) - USS America, Operations Prairie Fire & El Dorado Canyon (1986) VA-55 Warhorses - USS Coral Sea, Operations Prairie Fire & El Dorado Canyon (1986) VA-85 Black Falcons - USS Saratoga, Operation Prairie Fire (1986) VA-35 Black Panthers (2 options) - USS Saratoga, Operation De...
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
AOA Decals AOA72010
Punchers & Tigers - USN Grumman A-6E Intruders in the Cold War & Desert Storm. This 1/72 decal sheet covers the low visibility marking period of two Navy A-6 Intruder squadrons - VA-65 Tigers and VA-75 Sunday Punchers, with coverage spanning from the 1980s into Operation Desert Storm in 1991. For Fujimi 1/72 A-6E TRAM Intruder 6 Marking Options Included: 1, VA-65 Tigers (4 options) 2, USS Dwight D Eisenhower (1981/82) ...
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

AOA Decals AOA72009
1:72
VIETNAMESE SPADS (2) VNAF Douglas AD-6/A-1H Skyraiders in the Vietnam War [Decals released __ 2018] For Hasegawa 1/72 A-1H/A-1J Skyraider Part 2 of the South Vietnam Air Force (VNAF) Skyraider coverage, this 1/72 decal sheet includes 11 marking options (depicting 10 aircraft) for 1st, 514th, 516th, and 518th Fighter Squadrons. The markings provided span from the early 1960s to 1970, and also includes two Skyraiders tha...
Aircraft decals (military)
Catalogue
£15.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

AOA Decals AOA72008
1:72
VIETNAMESE SPADS (1) VNAF Douglas AD-6/A-1H Skyraiders in the Vietnam War [Decals released __ 2018] For Hasegawa 1/72 A-1H/A-1J Skyraider Part 1 of the South Vietnam Air Force (VNAF) Skyraider coverage, this 1/72 decal sheet includes 10 marking options (depicting 8 aircraft) for 1st, 514th, 518th, 520th, 522nd (83rd SOG), 524th, and 530th Fighter Squadrons. The markings provided span from the early 1960s to the early 19...
Aircraft decals (military)
Catalogue
£15.99
-
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

AOA Decals AOA72004
North-American/Rockwell OV-10A Broncos (USAF Vietnam War) This 1/72 decal sheet includes 16 marking options for OV-10A Broncos used by the USAF in the Forward Air Controller (FAC) role during the Vietnam War. Unfortunately not all schemes included are identified to their applicable assigned Tactical Air Support Squadron (TASS). Although not illustrated, one aircraft (67-14623) can also be modeled as a PAVE NAIL aircraft since ...
Aircraft decals (military)
Catalogue
£19.20
-
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

AOA Decals AOA72001
Grumman Intruders from the Beach - USMC Grumman A-6A Intruders in the Vietnam War This extensive and comprehensive decal sheet covers the three land based USMC A-6 Intruder squadrons during the Vietnam War (VMA(AW)-225, -242, and -533); allowing the option to represent one of many possible Intruders from any timeframe for each squadron. For Fujimi 1/72 A-6A Intruder, but most decals are non-kit specific and can also be u...
Aircraft decals (military)
Catalogue
£18.99
-
In Stock
4


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