-- 
-- Margen
-- 
SELECT
	ShopWAWIProduct.ShopWAWIProduct_ArticleNumber AS `Artikelnummer`,
	ShopWAWIProduct.ShopWAWIProduct_Title AS `Bezeichnung`,
	IFNULL(ShopWAWIProduct.ShopWAWIProduct_EAN, '') AS `EAN`,
	REPLACE(ShopWAWIProduct.ShopWAWIProduct_Einkaufspreis, '.', ',') AS `Einkaufspreis`,
	REPLACE(ShopWAWIProduct.ShopWAWIProduct_Listenpreis, '.', ',') AS `Listenpreis (Netto)`,
	REPLACE(IF (ShopWAWIProduct.ShopPriceType_Code = 'brutto', ShopWAWIProduct.ShopWAWIProduct_Listenpreis / 1.2, ShopWAWIProduct.ShopWAWIProduct_Listenpreis), '.', ',') AS `Listenpreis (Netto)`,
	REPLACE(IF (ShopWAWIProduct.ShopPriceType_Code = 'brutto', ShopWAWIProduct.ShopWAWIProduct_Listenpreis / 1.2, ShopWAWIProduct.ShopWAWIProduct_Listenpreis) - ShopWAWIProduct.ShopWAWIProduct_Einkaufspreis, '.', ',') AS `Marge EUR`,
	REPLACE(ROUND((IF (ShopWAWIProduct.ShopPriceType_Code = 'brutto', ShopWAWIProduct.ShopWAWIProduct_Listenpreis / 1.2, ShopWAWIProduct.ShopWAWIProduct_Listenpreis) - ShopWAWIProduct.ShopWAWIProduct_Einkaufspreis) / ShopWAWIProduct.ShopWAWIProduct_Einkaufspreis * 100, 2), '.', ',') AS `Marge %`,
	GROUP_CONCAT(ArticleEntry_Language.ArticleEntry_Language_Title SEPARATOR ', ') AS Kategorie
FROM ShopWAWIProduct
INNER JOIN ShopArticleItem USING (ShopWAWIProduct_ID)
INNER JOIN ShopArticle_ShopCategory USING (ShopArticle_ID)
INNER JOIN ShopCategory USING (ShopCategory_ID)
INNER JOIN ArticleEntry_Language ON (ArticleEntry_Language.ArticleEntry_ID = ShopCategory.ArticleEntry_ID AND ArticleEntry_Language.Language_Code = 'de')
WHERE ShopWAWIProduct.ShopWAWIProduct_ArticleNumber NOT IN ('TEST00001')
GROUP BY ShopWAWIProduct.ShopWAWIProduct_ID
ORDER BY `Marge %` ASC

-- 
-- Durchschnitts Marge
-- 
SELECT
	REPLACE(ROUND(AVG((IF (ShopWAWIProduct.ShopPriceType_Code = 'brutto', ShopWAWIProduct.ShopWAWIProduct_Listenpreis / 1.2, ShopWAWIProduct.ShopWAWIProduct_Listenpreis) - ShopWAWIProduct.ShopWAWIProduct_Einkaufspreis) / ShopWAWIProduct.ShopWAWIProduct_Einkaufspreis * 100), 2), '.', ',') AS `Durchschn. Marge %`
FROM ShopWAWIProduct
WHERE ShopWAWIProduct.ShopWAWIProduct_ArticleNumber NOT IN ('TEST00001')
ORDER BY `Durchschn. Marge %` ASC

-- 
-- Durchschnitts Marge pro Kategorie
-- 
SELECT
	ArticleEntry_Language.ArticleEntry_Language_Title AS Kategorie,
	COUNT(DISTINCT ShopWAWIProduct.ShopWAWIProduct_ID)  `# Artikel`,
	REPLACE(ROUND(AVG((IF (ShopWAWIProduct.ShopPriceType_Code = 'brutto', ShopWAWIProduct.ShopWAWIProduct_Listenpreis / 1.2, ShopWAWIProduct.ShopWAWIProduct_Listenpreis) - ShopWAWIProduct.ShopWAWIProduct_Einkaufspreis) / ShopWAWIProduct.ShopWAWIProduct_Einkaufspreis * 100), 2), '.', ',') AS `Durchschn. Marge %`,
	REPLACE(ROUND(MIN((IF (ShopWAWIProduct.ShopPriceType_Code = 'brutto', ShopWAWIProduct.ShopWAWIProduct_Listenpreis / 1.2, ShopWAWIProduct.ShopWAWIProduct_Listenpreis) - ShopWAWIProduct.ShopWAWIProduct_Einkaufspreis) / ShopWAWIProduct.ShopWAWIProduct_Einkaufspreis * 100), 2), '.', ',') AS `Min. Marge %`,
	REPLACE(ROUND(MAX((IF (ShopWAWIProduct.ShopPriceType_Code = 'brutto', ShopWAWIProduct.ShopWAWIProduct_Listenpreis / 1.2, ShopWAWIProduct.ShopWAWIProduct_Listenpreis) - ShopWAWIProduct.ShopWAWIProduct_Einkaufspreis) / ShopWAWIProduct.ShopWAWIProduct_Einkaufspreis * 100), 2), '.', ',') AS `Max. Marge %`
FROM ShopWAWIProduct
INNER JOIN ShopArticleItem USING (ShopWAWIProduct_ID)
INNER JOIN ShopArticle_ShopCategory USING (ShopArticle_ID)
INNER JOIN ShopCategory USING (ShopCategory_ID)
INNER JOIN ArticleEntry_Language ON (ArticleEntry_Language.ArticleEntry_ID = ShopCategory.ArticleEntry_ID AND ArticleEntry_Language.Language_Code = 'de')
WHERE ShopWAWIProduct.ShopWAWIProduct_ArticleNumber NOT IN ('TEST00001')
GROUP BY ShopCategory.ShopCategory_ID
ORDER BY `Durchschn. Marge %` ASC






SELECT
	sc.ShopCategory_ID,
	ael.ArticleEntry_Language_Title,
	ael.ArticleEntry_Language_TextCode
FROM ShopCategory AS sc
INNER JOIN ArticleEntry_Language AS ael ON (ael.ArticleEntry_ID = sc.ArticleEntry_ID AND ael.Language_Code = 'de')
INNER JOIN (
	SELECT sci.ShopCategory_ID, sci.ShopCategory_ID_Root, sci. FROM ShopCategory AS sci
	INNER JOIN ArticleEntry_Language AS aeli ON (aeli.ArticleEntry_ID = sci.ArticleEntry_ID AND aeli.Language_Code = 'de')
) AS scp ON (scp.ShopCategory_ID_Root = sc.ShopCategory_ID_Root AND scp.ShopCategory_Left < sc.ShopCategory_Left AND scp.ShopCategory_Right > sc.ShopCategory_Right)
WHERE
	sc.ShopCategory_ID_Root = 1
	AND sc.ShopCategory_ID = 16
GROUP BY
	sc.ShopCategory_ID

