Umsätze aus DB auslesen

[mhunziger]

Wir haben aus verschiedenen Gründen zum Jahreswechsel den Shop geschlossen.
Leider habe ich gerade festgestellt, dass mir noch ein paar Umsatzzahlen fehlen. Der Shop ansich lässt sich warum auch immer nicht mehr herstellen.
Kann man aus dem letzten DB Backup die Zahlen auslesen für 2018?

Danke

Linkback: https://www.modified-shop.org/forum/index.php?topic=39923.0

[Modulfux]

Wenn du in phpMyAdmin dieses SELECT abschickst, bekommst du die einige Zahlen.

Code: SQL  [Auswählen]

SELECT round(COALESCE(SUM(IF(DATE(o.date_purchased) = CURRENT_DATE, ot.value/o.currency_value, NULL)), 0), 2) today,
       round(COALESCE(SUM(IF(DATE(o.date_purchased) = CURRENT_DATE - INTERVAL 1 DAY, ot.value/o.currency_value, NULL)), 0), 2) yesterday,
       round(COALESCE(SUM(IF(EXTRACT(year_month FROM o.date_purchased) = EXTRACT(year_month FROM CURRENT_DATE), ot.value/o.currency_value, NULL)), 0), 2) this_month,
       round(COALESCE(SUM(IF(EXTRACT(year_month FROM o.date_purchased) = EXTRACT(year_month FROM CURRENT_DATE - INTERVAL 1 YEAR), ot.value/o.currency_value, NULL)), 0), 2) this_month_last_year,
       round(COALESCE(SUM(IF(EXTRACT(year_month FROM o.date_purchased) = EXTRACT(year_month FROM CURRENT_DATE - INTERVAL 1 year_month), ot.value/o.currency_value, NULL)), 0), 2) last_month,
       round(COALESCE(SUM(IF(EXTRACT(year_month FROM o.date_purchased) = EXTRACT(year_month FROM CURRENT_DATE - INTERVAL 1 year_month - INTERVAL 1 YEAR), ot.value/o.currency_value, NULL)), 0), 2) last_month_last_year,
       round(COALESCE(SUM(IF(EXTRACT(year_month FROM o.date_purchased) = EXTRACT(year_month FROM CURRENT_DATE - INTERVAL 1 year_month) AND o.orders_status <> 1, ot.value/o.currency_value, NULL)), 0), 2) last_month_paid,
       round(COALESCE(SUM(IF(EXTRACT(YEAR FROM o.date_purchased) = EXTRACT(YEAR FROM CURRENT_DATE - INTERVAL 1 YEAR), ot.value/o.currency_value, NULL)), 0), 2) last_year,
       round(COALESCE(SUM(IF(EXTRACT(YEAR FROM o.date_purchased) = EXTRACT(YEAR FROM CURRENT_DATE), ot.value/o.currency_value, NULL)), 0), 2) this_year,
       round(COALESCE(SUM(ot.value/o.currency_value), 0), 2) total  
  FROM orders o
  JOIN orders_total ot
       ON ot.orders_id = o.orders_id
 WHERE ot.class = 'ot_total'
 

[mhunziger]

hat funktioniert