FINALE VERSION - MIT LAGERREICHWEITEN VORHERSAGE (ca.) in TAGEN UND MONATEN & EK BESTELLWERT pro PROUKT ...
1. für andere Intervalle unten die: interval 365 day" auf Tage ändern.
2. für andere Warengruppen die cl.id_category (aktuell 3) auf eure Kategorie ändern : JOIN `ps_category_lang` cl ON (sa.`id_category_default` = cl.`id_category` AND b.`id_lang` = cl.`id_lang` AND cl.id_shop = a.id_shop_default and cl.id_category = 3)
3. wenn Ihr keine Kategorien braucht sondern alles sehen wollt dann löscht folgendes : AND cl.id_shop = a.id_shop_default and cl.id_category = 3
_________________________________________________________________________________________________________________________________
SELECT
a.id_product AS 'ID',
a.`reference`AS 'ART.NO',
sa.`active`AS `AKTIV`,
b.`name` AS 'PRODUKT',
sav.`quantity` AS 'AUF LAGER',
sum(d.product_quantity) AS 'VERKAUFT 12M',
(sum(d.product_quantity) / 365 ) AS 'VERKAUF PRO TAG',
round ((sav.quantity / (sum(d.product_quantity) /365)),1) AS 'REICHWEITE TAGE',
round (((sav.quantity / (sum(d.product_quantity) /365)) /30),1) AS 'REICHWEITE MONAT',
max(x.date_add) AS 'LETZER VERKAUF',
'BESTELLEN' AS 'BESTELLEN FÜR ',
round((sum(d.product_quantity) * 1.10 ) - sav.quantity) AS '12 MONATE',
' € ' AS '. ',
round (a.wholesale_price,2) AS 'STK -EK ',
' € ' AS ' ',
round (((((sum(d.product_quantity) * 1.10 ) - sav.quantity)) * a.wholesale_price),2) AS 'BESTELLWERT'
FROM ps_order_detail d
JOIN ps_orders x ON ( d.id_order = x.id_order AND (x.`date_add` BETWEEN date_sub(now(), interval 365 day) AND now() ) )
JOIN `ps_product` a ON (d.product_id = a.id_product)
JOIN `ps_product_lang` b ON (b.`id_product` = a.`id_product` AND b.`id_lang` = 1 AND b.`id_shop` = 1)
JOIN `ps_product_shop` sa ON (a.`id_product` = sa.`id_product` AND sa.id_shop = a.id_shop_default)
JOIN `ps_category_lang` cl ON (sa.`id_category_default` = cl.`id_category` AND b.`id_lang` = cl.`id_lang` AND cl.id_shop = a.id_shop_default and cl.id_category = 3)
JOIN `ps_stock_available` sav ON (sav.`id_product` = a.`id_product`)
GROUP BY a.id_product
ORDER BY sav.`quantity` ASC
____________________________________________________________________________________________________________
mich würde mal interessieren wer sowas ausser mir noch gebrauchen kann ...
LG Marco