Naja, es ist eben ein wenig schwer, da die Bestellstati von euch ja immer unterschiedliche IDs haben. Man müsste das, wenn überhaupt, mit einer Einstellung im Adminbereich versehen, oder zumindest den Status "storniert" per default mit einfügen.
Aber auch dann ist es problematisch, denn viele haben mehrere "storniert-Stati", beispielsweise "PayPal storniert", "Sofortüberweisung storniert", etc.
Grüße
Torsten
naja... man kann ja unendliche "and" aneinander ketten...
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 and o
.orders_status
<> 9 and o
.orders_status
<> 10 and o
.orders_status
<> 19 and o
.orders_status
<> 20, ot
.value
, null)), 0), 2) last_month_paid
, natürlich wäre es schöner, wenn man dies im Backend festlegen kann.
bei mir sieht es derzeit so aus:
// turnover
$turnover_query = xtc_db_query('select
round(coalesce(sum(if(date(o.date_purchased) = current_date - interval 0 day and o.orders_status <> 9, ot.value, null)), 0), 2) today,
round(coalesce(sum(if(date(o.date_purchased) = current_date - interval 1 day and o.orders_status <> 9, ot.value, null)), 0), 2) yesterday,
round(coalesce(sum(if(extract(year_month from o.date_purchased) = extract(year_month from current_date - interval 0 month) and o.orders_status <> 9, ot.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_month), ot.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) and o.orders_status <> 1 and o.orders_status <> 2 and o.orders_status <> 4 and o.orders_status <> 5 and o.orders_status <> 6 and o.orders_status <> 7 and o.orders_status <> 8 and o.orders_status <> 10 and o.orders_status <> 11 and o.orders_status <> 12 and o.orders_status <> 16 and o.orders_status <> 19 and o.orders_status <> 20 and o.orders_status <> 22 and o.orders_status <> 23 and o.orders_status <> 9 and o.orders_status <> 17 and o.orders_status <> 18, ot.value, null)), 0), 2) last_month_paid,
round(coalesce(sum(if(extract(year from o.date_purchased) = extract(year from current_date - interval 0 year) and o.orders_status <> 9 and o.orders_status <> 23, ot.value, null)), 0), 2) this_year,
round(coalesce(sum(if(extract(year from o.date_purchased) = extract(year from current_date - interval 0 year) and o.orders_status <> 1 and o.orders_status <> 2 and o.orders_status <> 4 and o.orders_status <> 5 and o.orders_status <> 6 and o.orders_status <> 7 and o.orders_status <> 8 and o.orders_status <> 11 and o.orders_status <> 12 and o.orders_status <> 16 and o.orders_status <> 19 and o.orders_status <> 20 and o.orders_status <> 22 and o.orders_status <> 23 and o.orders_status <> 9 and o.orders_status <> 17 and o.orders_status <> 18, ot.value, null)), 0), 2) this_year_paid,
round(coalesce(sum(if(extract(year from o.date_purchased) = extract(year from current_date - interval 1 year) and o.orders_status <> 9 and o.orders_status <> 23 and o.orders_status <> 11 and o.orders_status <> 1 and o.orders_status <> 2 and o.orders_status <> 7 and o.orders_status <> 8, ot.value, null)), 0), 2) last_year,
round(coalesce(sum(if(extract(year from o.date_purchased) = extract(year from current_date - interval 1 year) and o.orders_status <> 1 and o.orders_status <> 2 and o.orders_status <> 4 and o.orders_status <> 5 and o.orders_status <> 6 and o.orders_status <> 7 and o.orders_status <> 8 and o.orders_status <> 10 and o.orders_status <> 11 and o.orders_status <> 12 and o.orders_status <> 16 and o.orders_status <> 19 and o.orders_status <> 20 and o.orders_status <> 22 and o.orders_status <> 23 and o.orders_status <> 9 and o.orders_status <> 17 and o.orders_status <> 18, ot.value, null)), 0), 2) last_year_paid,
round(coalesce(sum(if(extract(year from o.date_purchased) = extract(year from current_date) - o.orders_status <> 1 and o.orders_status <> 2 and o.orders_status <> 4 and o.orders_status <> 5 and o.orders_status <> 6 and o.orders_status <> 7 and o.orders_status <> 8 and o.orders_status <> 10 and o.orders_status <> 11 and o.orders_status <> 12 and o.orders_status <> 16 and o.orders_status <> 19 and o.orders_status <> 20 and o.orders_status <> 22 and o.orders_status <> 23 and o.orders_status <> 9 and o.orders_status <> 17 and o.orders_status <> 18, ot.value, null)), 0), 2) total_paid,
round(coalesce(sum(if(extract(year from o.date_purchased) = extract(year from current_date) - o.orders_status <> 1 and o.orders_status <> 2 and o.orders_status <> 3 and o.orders_status <> 4 and o.orders_status <> 5 and o.orders_status <> 6 and o.orders_status <> 7 and o.orders_status <> 10 and o.orders_status <> 12 and o.orders_status <> 16 and o.orders_status <> 17 and o.orders_status <> 18 and o.orders_status <> 19 and o.orders_status <> 20 and o.orders_status <> 22, ot.value, null)), 0), 2) total_storno,
round(coalesce(sum(if(extract(year from o.date_purchased) = extract(year from current_date - interval 0 year) and o.orders_status <> 3 and o.orders_status <> 8 and o.orders_status <> 9 and o.orders_status <> 11 and o.orders_status <> 23, ot.value, null)), 0), 2) total_offen,
round(coalesce(sum(ot.value), 0), 2) total
from ' . TABLE_ORDERS . ' o
join ' . TABLE_ORDERS_TOTAL . ' ot on ot.orders_id = o.orders_id
where ot.class = \'ot_total\'');
$turnover = xtc_db_fetch_array($turnover_query);
und entsprechend
<!-- BOF START INFOS STATISTIK -->
<table width="100%" border="0" cellspacing="0">
<tr>
<td width="25%" valign="top"><table width="100%" border="0">
<tr>
<td style="background:#eee"><strong><?php echo TURNOVER_TODAY; ?>:</strong></td>
<td style="background:#eee" align="right"><?php echo $currencies->format($turnover['today']); ?></td>
</tr>
<tr>
<td style="background:#fff"><strong><?php echo TURNOVER_YESTERDAY; ?>:</strong></td>
<td style="background:#fff" align="right"><?php echo $currencies->format($turnover['yesterday']); ?></td>
</tr>
<tr>
<td style="background:#eee"><strong><?php echo TURNOVER_THIS_MONTH; ?>:</strong></td>
<td style="background:#eee" align="right"><?php echo $currencies->format($turnover['this_month']); ?></td>
</tr>
<tr>
<td style="background:#ccc"><strong><?php echo TURNOVER_LAST_MONTH; ?>:</strong></td>
<td style="background:#ccc" align="right"><?php echo $currencies->format($turnover['last_month']); ?></td>
</tr>
<tr>
<td style="background:#ccc"><strong><?php echo TURNOVER_LAST_MONTH_PAID; ?>:</strong></td>
<td style="background:#ccc" align="right"><?php echo $currencies->format($turnover['last_month_paid']); ?></td>
</tr>
<tr>
<td style="background:#888; color:#FFF"><strong><?php echo TURNOVER_THIS_YEAR; ?>:</strong></td>
<td style="background:#888; color:#FFF" align="right"><?php echo $currencies->format($turnover['this_year']); ?></td>
</tr>
<tr>
<td style="background:#CCC; color:#000"><strong><?php echo TURNOVER_THIS_YEAR_PAID; ?>:</strong></td>
<td style="background:#CCC; color:#000" align="right"><?php echo $currencies->format($turnover['this_year_paid']); ?></td>
</tr>
<tr>
<td style="background:#888; color:#FFF"><strong><?php echo TURNOVER_LAST_YEAR; ?>:</strong></td>
<td style="background:#888; color:#FFF" align="right"><?php echo $currencies->format($turnover['last_year']); ?></td>
</tr>
<tr>
<td style="background:#CCC; color:#008000"><strong><?php echo TURNOVER_LAST_YEAR_PAID; ?>:</strong></td>
<td style="background:#CCC; color:#008000" align="right"><?php echo $currencies->format($turnover['last_year_paid']); ?></td>
</tr>
<tr>
<td style="background:#FFF; color:#008000"><strong><?php echo TOTAL_TURNOVER_PAID; ?>:</strong></td>
<td style="background:#FFF; color:#008000" align="right"><?php echo $currencies->format($turnover['total_paid']); ?></td>
</tr>
<tr>
<td style="background:#FF8080; color:#000"><strong><?php echo TURNOVER_STORNO; ?>:</strong></td>
<td style="background:#FF8080; color:#000" align="right"><?php echo $currencies->format($turnover['total_storno']); ?></td>
</tr>
<tr>
<td style="background:#90EE90; color:#000"><strong><?php echo TURNOVER_OFFEN; ?>:</strong></td>
<td style="background:#90EE90; color:#000" align="right"><?php echo $currencies->format($turnover['total_offen']); ?></td>
</tr>
<tr>
<td style="background:#666; color:#FFF"><strong><?php echo TOTAL_TURNOVER; ?>:</strong></td>
<td style="background:#666; color:#FFF" align="right"><?php echo $currencies->format($turnover['total']); ?></td>
</tr>
</table></td>
<td width="25%" valign="top"><table width="100%">
Dabei kommen die Summen nicht ganz hin
und mit Rundungsfehlern sind so hohe Differenzen nicht erklärbar.
Aber im Prinzip schon Hilfreich für uns.
Vielleicht hat ja mal jemand lust da was tolles zu zaubern.