-- --------------------------------------------------------------------
-- Find Fake-Ratings
-- --------------------------------------------------------------------
SELECT
	DATE(r.Table_Create),
	SUM(IF(r.Rating_Value = 1, 1, 0)) AS Value1,
	TRIM(REPLACE(GROUP_CONCAT(IF(r.Rating_Value = 1, sa.ShopArticle_Articlenumber, '') SEPARATOR ' '), '  ', ' ')) AS Value1,
	SUM(IF(r.Rating_Value = 2, 1, 0)) AS Value2,
	SUM(IF(r.Rating_Value = 3, 1, 0)) AS Value3,
	SUM(IF(r.Rating_Value = 4, 1, 0)) AS Value4,
	SUM(IF(r.Rating_Value = 5, 1, 0)) AS Value5,
	COUNT(*) AS Total
FROM Rating AS r
INNER JOIN ShopArticle AS sa ON (sa.ArticleEntry_ID = r.ArticleEntry_ID)
GROUP BY DATE(r.Table_Create)
ORDER BY r.Table_Create;

-- --------------------------------------------------------------------
-- Remove Fake-Ratings by value and date
-- --------------------------------------------------------------------
-- DELETE FROM Rating WHERE Rating_Value = 1 AND DATE(Table_Create) = '2013-03-17'

-- --------------------------------------------------------------------
-- Fix cached rating count and values in ArticleEntry
-- --------------------------------------------------------------------
UPDATE ArticleEntry AS ae
SET
	ae.Table_Edit = NOW(),
	ae.ArticleEntry_Rating = (SELECT SUM(IFNULL(r.Rating_Value, 0)) FROM Rating AS r WHERE r.ArticleEntry_ID = ae.ArticleEntry_ID),
	ae.ArticleEntry_Rating_Count = (SELECT COUNT(r.Rating_ID) FROM Rating AS r WHERE r.ArticleEntry_ID = ae.ArticleEntry_ID)
WHERE
	ae.ArticleEntry_Rating_Count != (SELECT COUNT(r.Rating_ID) FROM Rating AS r WHERE r.ArticleEntry_ID = ae.ArticleEntry_ID);

