SELECT
p.products_id AS productid,
d.products_name AS name,
p.products_ean AS ean,
IF(sp.specials_new_products_price < p.products_price&&sp.specials_new_products_price IS NOT NULL, ROUND(sp.specials_new_products_price*(tr.tax_rate/100+1),2), ROUND(p.products_price*(tr.tax_rate/100+1),2)) AS price,
IF((p.products_price*(tr.tax_rate/100+1) >= " 60
"&&sp.specials_new_products_price IS NULL)||sp.specials_new_products_price*(tr.tax_rate/100+1) >= 60.,'0.00','" . $this->_shippingCost->getShippingRate(). "') AS shippingcost,
CONCAT('http://www.planet-sports.de/product', CONVERT(p.products_id, CHAR),'/product_info.html') AS producturl,
CONCAT('http://images.planet-sports.com/is/image/planetsports/',REPLACE(m.manufacturers_name,' ','-'),'-',REPLACE(d.products_name,' ','-'),'-',p.products_id,'_set.jpg?\$l\$&defaultImage=no_pic' ) AS imageurl,
(SELECT CASE WHEN p.stock = 0 THEN 'Nicht auf Lager' WHEN p.stock > 0 THEN 'Auf Lager' END) as availability,
d.products_description AS description,
m.manufacturers_name AS brand,
(SELECT
CONCAT(pcd.categories_name, ' > ',
cd.categories_name) AS categorypath
FROM
categories c
INNER JOIN
categories_description cd
ON c.categories_id = cd.categories_id
INNER JOIN categories_description pcd
ON c.parent_id = pcd.categories_id
INNER JOIN
products_to_categories p2c
ON c.categories_id = p2c.categories_id
where c.categories_status='1'
and cd.language_id = '2' AND pcd.language_id = '2'
and p2c.products_id = p.products_id
AND pcd.categories_name IS NOT NULL
LIMIT 1 ) AS category,
ptc.categories_id AS categoryid,
p.products_ps_tags AS type,
col.color_name AS color,
'Versandfertig in 24 Stunden' AS availabilitydetails,
GROUP_CONCAT(ptc.categories_id) AS categoryids,
'Neu' AS `condition`,
IF(sp.specials_new_products_price IS NOT NULL,ROUND(p.products_price*(tr.tax_rate/100+1),2), 'NULL') AS oldprice,
ROUND(sp.specials_new_products_price*(tr.tax_rate/100+1),2) AS specialprice,
CASE (SELECT
pcd.categories_name
FROM
categories c
INNER JOIN
categories_description cd
ON c.categories_id = cd.categories_id
INNER JOIN categories_description pcd
ON c.parent_id = pcd.categories_id
INNER JOIN
products_to_categories p2c
ON c.categories_id = p2c.categories_id
where c.categories_status='1'
and cd.language_id = '2' AND pcd.language_id = '2'
and p2c.products_id = p.products_id
AND pcd.categories_name IS NOT NULL
LIMIT 1 )
WHEN 'Girls Fashion' THEN 'f'
WHEN 'Girls Footwear' THEN 'f'
WHEN 'Mens Fashion' THEN 'm'
WHEN 'Mens Footwear' THEN 'm'
ELSE 'unisex'
END
as sex,
lg.code AS countrycode,
p.stock AS stock,
GROUP_CONCAT(pov.products_options_values_name,'#') as sizes
FROM products p
LEFT JOIN manufacturers m
ON p.manufacturers_id = m.manufacturers_id
LEFT JOIN products_description d
ON p.products_id = d.products_id
LEFT JOIN specials sp
ON p.products_id = sp.products_id
LEFT JOIN products_to_categories ptc
ON p.products_id = ptc.products_id
LEFT JOIN colors_to_products c2p
ON p.products_id = c2p.products_id
INNER JOIN colors col
ON c2p.color_id = col.coSELECT
p.products_id AS productid,
d.products_name AS name,
p.products_ean AS ean,
IF(sp.specials_new_products_price < p.products_price&&sp.specials_new_products_price IS NOT NULL, ROUND(sp.specials_new_products_price*(tr.tax_rate/100+1),2), ROUND(p.products_price*(tr.tax_rate/100+1),2)) AS price,
IF((p.products_price*(tr.tax_rate/100+1) >= " 60
"&&sp.specials_new_products_price IS NULL)||sp.specials_new_products_price*(tr.tax_rate/100+1) >= 60.,'0.00','" . $this->_shippingCost->getShippingRate(). "') AS shippingcost,
CONCAT('http://www.planet-sports.de/product', CONVERT(p.products_id, CHAR),'/product_info.html') AS producturl,
CONCAT('http://images.planet-sports.com/is/image/planetsports/',REPLACE(m.manufacturers_name,' ','-'),'-',REPLACE(d.products_name,' ','-'),'-',p.products_id,'_set.jpg?\$l\$&defaultImage=no_pic' ) AS imageurl,
(SELECT CASE WHEN p.stock = 0 THEN 'Nicht auf Lager' WHEN p.stock > 0 THEN 'Auf Lager' END) as availability,
d.products_description AS description,
m.manufacturers_name AS brand,
(SELECT
CONCAT(pcd.categories_name, ' > ',
cd.categories_name) AS categorypath
FROM
categories c
INNER JOIN
categories_description cd
ON c.categories_id = cd.categories_id
INNER JOIN categories_description pcd
ON c.parent_id = pcd.categories_id
INNER JOIN
products_to_categories p2c
ON c.categories_id = p2c.categories_id
where c.categories_status='1'
and cd.language_id = '2' AND pcd.language_id = '2'
and p2c.products_id = p.products_id
AND pcd.categories_name IS NOT NULL
LIMIT 1 ) AS category,
ptc.categories_id AS categoryid,
p.products_ps_tags AS type,
col.color_name AS color,
'Versandfertig in 24 Stunden' AS availabilitydetails,
GROUP_CONCAT(ptc.categories_id) AS categoryids,
'Neu' AS `condition`,
IF(sp.specials_new_products_price IS NOT NULL,ROUND(p.products_price*(tr.tax_rate/100+1),2), 'NULL') AS oldprice,
ROUND(sp.specials_new_products_price*(tr.tax_rate/100+1),2) AS specialprice,
CASE (SELECT
pcd.categories_name
FROM
categories c
INNER JOIN
categories_description cd
ON c.categories_id = cd.categories_id
INNER JOIN categories_description pcd
ON c.parent_id = pcd.categories_id
INNER JOIN
products_to_categories p2c
ON c.categories_id = p2c.categories_id
where c.categories_status='1'
and cd.language_id = '2' AND pcd.language_id = '2'
and p2c.products_id = p.products_id
AND pcd.categories_name IS NOT NULL
LIMIT 1 )
WHEN 'Girls Fashion' THEN 'f'
WHEN 'Girls Footwear' THEN 'f'
WHEN 'Mens Fashion' THEN 'm'
WHEN 'Mens Footwear' THEN 'm'
ELSE 'unisex'
END
as sex,
lg.code AS countrycode,
p.stock AS stock,
GROUP_CONCAT(pov.products_options_values_name,'#') as sizes
FROM products p
LEFT JOIN manufacturers m
ON p.manufacturers_id = m.manufacturers_id
LEFT JOIN products_description d
ON p.products_id = d.products_id
LEFT JOIN specials sp
ON p.products_id = sp.products_id
LEFT JOIN products_to_categories ptc
ON p.products_id = ptc.products_id
LEFT JOIN colors_to_products c2p
ON p.products_id = c2p.products_id
INNER JOIN colors col
ON c2p.color_id = col.color_id
INNER JOIN
tax_rates tr
ON p.products_tax_class_id=tr.tax_class_id
INNER JOIN languages lg
ON lg.languages_id = d.language_id
LEFT JOIN
products_attributes pa ON pa.products_id = p.products_id
LEFT JOIN
products_options_values pov ON pa.options_values_id = pov.products_options_values_id
WHERE
p.stock > '0'
AND p.products_status = '1'
GROUP BY p.products_id, d.language_id AND pov.language_id = '2'lor_id
INNER JOIN
tax_rates tr
ON p.products_tax_class_id=tr.tax_class_id
INNER JOIN languages lg
ON lg.languages_id = d.language_id
LEFT JOIN
products_attributes pa ON pa.products_id = p.products_id
LEFT JOIN
products_options_values pov ON pa.options_values_id = pov.products_options_values_id
WHERE
p.stock > '0'
AND p.products_status = '1'
GROUP BY p.products_id, d.language_id AND pov.language_id = '2'