Verbesserungs-/Erweiterungsvorschlag Umsatzstatistik

[mroehrs]

Hallo,

die Umsatzstatistik ist eigentlich sehr informativ - aber auch sehr rudimentär. Gibt es eigentlich ein Modul, dass den Shop-Umsatz für die Buchhaltung zugänglich macht?
Ich würde es z.B. begrüßen, wenn in der Statistik neben den Preisen, auch die netto-Preise, die Versandkosten, Kosten für PayPal/Kreditkarten sowie vielleicht noch eine Aufschlüsselung nach Kundennamen. Und wenn diese dann noch nach Excel exportierbar wäre (als csv) damit man die Daten gesammelt in die Buchhaltung geben kann. Das ganze könnte ja aufgebaut sein wie eine wöchentliche/monatliche Rechnung mit alle Positionen.
Oder habe ich einfach nur die Möglichkeit übersehen, wie das jetzt schon geht.

mit fragenden Grüßen

Michael



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

[DokuMan]

Mit der Anbindung eines Warenwirtschaftsprogramms z.B. "JTL" wärst du hier glaube ich noch wesentlich flexibler. Deshalb ist die Auswertung im Shop auch so rudimentär!

[mroehrs]

hallo dokuMan,

an JTL hatte ich auch schon gedacht. Ich hatte jedoch gehofft, dass die Auswertung schon modified eCommerce Shopsoftware so erweitert werden könnte. Ich finde es ein wenig mit Kanonen auf Spatzen geschossen, wenn ich für eine ausführlichere Auswertung einen WaWi installieren muss.

mfg

Michael

[Clever]

Also wir fanden das auch besser wenn man auf den Ersten Blick ein wenig Übersicht bekommt,
darum haben wir mal ein wenig erweitert...
allerdings muß die Anpassung in der "start.php" vorgenommen werden, da ja jeder "orderstatus" bei jedem andere ziffern hat - zudem hat einer 4 der andere 20 verschiedene...

Aber in der "start.php" kannst Du z.B. so vorgehen:

Code: PHP  [Auswählen]

// 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) and o.orders_status <> 9 and o.orders_status <> 23 and o.orders_status <> 8 and o.orders_status <> 10, 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);
?>
 

Wobei Du die einzlnen StatusID selbst bei Dir nachsehen mußt und entsprechend anpassen solltest.
Leider kommt es bei uns zwischen dem Gesamtumsatz inkl storno und dem Umsatz + storno zu einer differenz von ca. 1.000 euro - warum auch immer... naja haben wir uns dran gewöhnt.

  [ Für Gäste sind keine Dateianhänge sichtbar ]
Der Vollständigkeit halber auch noch die tabelle weiter unten

Code: PHP  [Auswählen]

<!-- body_text //-->
     <td width="100%" valign="top">
           <table border="0" width="100%" cellspacing="0" cellpadding="0">
         <tr>
         <td class="boxCenter" width="100%" valign="top">
     <table border="0" width="100%" cellspacing="0" cellpadding="0">
          <tr>
               <td><h1><?php echo HEADING_TITLE; ?></h1></td>
          </tr>
          <tr>
               <td>
                           <!--  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%">

[Ironbone]

Hi,
habe das bei mir mal ausprobiert. Ein bisschen verändert. Aber irgendwie mach ich was falsch.

Bei dieser Abfrage werden außer bei total völlig falsche Werte ausgegeben:

Code: PHP  [Auswählen]

$turnover_query = xtc_db_query('select
  round(coalesce(sum(if(date(o.date_purchased) = current_date - interval 0 day and o.orders_status <> 14 and o.orders_status <> 5 and o.orders_status <> 3 and o.orders_status <> 2, ot.value, null)), 0), 2) today,
  round(coalesce(sum(if(date(o.date_purchased) = current_date - interval 1 day and o.orders_status <> 14 and o.orders_status <> 5 and o.orders_status <> 3 and o.orders_status <> 2, 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 <> 14 and o.orders_status <> 5 and o.orders_status <> 3 and o.orders_status <> 2 and o.orders_status <> 11 and o.orders_status <> 12 and o.orders_status <> 6 and o.orders_status <> 15 and o.orders_status <> 1, 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 0 month) and o.orders_status <> 14 and o.orders_status <> 5 and o.orders_status <> 3 and o.orders_status <> 2, ot.value, null)), 0), 2) this_month_paid,
  round(coalesce(sum(if(extract(year_month from o.date_purchased) = extract(year_month from current_date - interval 0 month) and o.orders_status <> 17, ot.value, null)), 0), 2) this_month_back,
  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 and o.orders_status <> 7 and o.orders_status <> 13 and o.orders_status <> 4 and o.orders_status <> 16, ot.value, null)), 0), 2) this_month_storno,
  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 <> 14 and o.orders_status <> 5 and o.orders_status <> 3 and o.orders_status <> 2 and o.orders_status <> 11 and o.orders_status <> 12 and o.orders_status <> 6 and o.orders_status <> 15 and o.orders_status <> 1, 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 <> 14 and o.orders_status <> 5 and o.orders_status <> 3 and o.orders_status <> 2, ot.value, null)), 0), 2) last_month_paid,
  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 <> 17, ot.value, null)), 0), 2) last_month_back,  
  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 <> 9 and o.orders_status <> 7 and o.orders_status <> 13 and o.orders_status <> 4 and o.orders_status <> 16, ot.value, null)), 0), 2) last_month_storno,
   round(coalesce(sum(if(extract(year from o.date_purchased) = extract(year from current_date - interval 0 year) and o.orders_status <> 14 and o.orders_status <> 5 and o.orders_status <> 3 and o.orders_status <> 2 and o.orders_status <> 11 and o.orders_status <> 12 and o.orders_status <> 6 and o.orders_status <> 15 and o.orders_status <> 1, 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 <> 14 and o.orders_status <> 5 and o.orders_status <> 3 and o.orders_status <> 2, ot.value, null)), 0), 2) this_year_paid,
   round(coalesce(sum(if(extract(year from o.date_purchased) = extract(year from current_date - interval 0 year) and o.orders_status <> 17, ot.value, null)), 0), 2) this_year_back,
  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 <> 7 and o.orders_status <> 13 and o.orders_status <> 4 and o.orders_status <> 16, ot.value, null)), 0), 2) this_year_storno,
   round(coalesce(sum(if(extract(year from o.date_purchased) = extract(year from current_date - interval 1 year) and o.orders_status <> 14 and o.orders_status <> 5 and o.orders_status <> 3 and o.orders_status <> 2 and o.orders_status <> 11 and o.orders_status <> 12 and o.orders_status <> 6 and o.orders_status <> 15 and o.orders_status <> 1, 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 <> 14 and o.orders_status <> 5 and o.orders_status <> 3 and o.orders_status <> 2, ot.value, null)), 0), 2) last_year_paid,
   round(coalesce(sum(if(extract(year from o.date_purchased) = extract(year from current_date - interval 1 year) and o.orders_status <> 17, ot.value, null)), 0), 2) last_year_back,
   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 <> 7 and o.orders_status <> 13 and o.orders_status <> 4 and o.orders_status <> 16, ot.value, null)), 0), 2) last_year_storno,      
  round(coalesce(sum(if(extract(year from o.date_purchased) = extract(year from current_date) - o.orders_status <> 14 and o.orders_status <> 5 and o.orders_status <> 3 and o.orders_status <> 2 and o.orders_status <> 11 and o.orders_status <> 12 and o.orders_status <> 6 and o.orders_status <> 15 and o.orders_status <> 1, ot.value, null)), 0), 2) total_all,
  round(coalesce(sum(if(extract(year from o.date_purchased) = extract(year from current_date) - o.orders_status <> 14 and o.orders_status <> 5 and o.orders_status <> 3 and o.orders_status <> 2, ot.value, null)), 0), 2) total_paid,
  round(coalesce(sum(if(extract(year from o.date_purchased) = extract(year from current_date) and o.orders_status <> 17, ot.value, null)), 0), 2) total_back,
  round(coalesce(sum(if(extract(year from o.date_purchased) = extract(year from current_date) - o.orders_status <> 9 and o.orders_status <> 7 and o.orders_status <> 13 and o.orders_status <> 4 and o.orders_status <> 16, ot.value, null)), 0), 2) total_storno,
  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 bei dieser Abfrage stimmen nur total, last_month_back, last_month_back, this_year_back, last__year_back. Und bei  total_back nur ein falscher Wert.

Code: PHP  [Auswählen]

$turnover_query = xtc_db_query('select
  round(coalesce(sum(if(date(o.date_purchased) = current_date - interval 0 day and o.orders_status = 14 and o.orders_status = 5 and o.orders_status = 3 and o.orders_status = 2, ot.value, null)), 0), 2) today,
  round(coalesce(sum(if(date(o.date_purchased) = current_date - interval 1 day and o.orders_status = 14 and o.orders_status = 5 and o.orders_status = 3 and o.orders_status = 2, 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 = 14 and o.orders_status = 5 and o.orders_status = 3 and o.orders_status = 2 and o.orders_status = 11 and o.orders_status = 12 and o.orders_status = 6 and o.orders_status = 15 and o.orders_status = 1, 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 0 month) and o.orders_status = 14 and o.orders_status = 5 and o.orders_status = 3 and o.orders_status = 2, ot.value, null)), 0), 2) this_month_paid,
  round(coalesce(sum(if(extract(year_month from o.date_purchased) = extract(year_month from current_date - interval 0 month) and o.orders_status = 17, ot.value, null)), 0), 2) this_month_back,
  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 and o.orders_status = 7 and o.orders_status = 13 and o.orders_status = 4 and o.orders_status = 16, ot.value, null)), 0), 2) this_month_storno,
  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 = 14 and o.orders_status = 5 and o.orders_status = 3 and o.orders_status = 2 and o.orders_status = 11 and o.orders_status = 12 and o.orders_status = 6 and o.orders_status = 15 and o.orders_status = 1, 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 = 14 and o.orders_status = 5 and o.orders_status = 3 and o.orders_status = 2, ot.value, null)), 0), 2) last_month_paid,
  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 = 17, ot.value, null)), 0), 2) last_month_back,  
  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 = 9 and o.orders_status = 7 and o.orders_status = 13 and o.orders_status = 4 and o.orders_status = 16, ot.value, null)), 0), 2) last_month_storno,
   round(coalesce(sum(if(extract(year from o.date_purchased) = extract(year from current_date - interval 0 year) and o.orders_status = 14 and o.orders_status = 5 and o.orders_status = 3 and o.orders_status = 2 and o.orders_status = 11 and o.orders_status = 12 and o.orders_status = 6 and o.orders_status = 15 and o.orders_status = 1, 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 = 14 and o.orders_status = 5 and o.orders_status = 3 and o.orders_status = 2, ot.value, null)), 0), 2) this_year_paid,
   round(coalesce(sum(if(extract(year from o.date_purchased) = extract(year from current_date - interval 0 year) and o.orders_status = 17, ot.value, null)), 0), 2) this_year_back,
  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 = 7 and o.orders_status = 13 and o.orders_status = 4 and o.orders_status = 16, ot.value, null)), 0), 2) this_year_storno,
   round(coalesce(sum(if(extract(year from o.date_purchased) = extract(year from current_date - interval 1 year) and o.orders_status = 14 and o.orders_status = 5 and o.orders_status = 3 and o.orders_status = 2 and o.orders_status = 11 and o.orders_status = 12 and o.orders_status = 6 and o.orders_status = 15 and o.orders_status = 1, 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 = 14 and o.orders_status = 5 and o.orders_status = 3 and o.orders_status = 2, ot.value, null)), 0), 2) last_year_paid,
   round(coalesce(sum(if(extract(year from o.date_purchased) = extract(year from current_date - interval 1 year) and o.orders_status = 17, ot.value, null)), 0), 2) last_year_back,
   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 = 7 and o.orders_status = 13 and o.orders_status = 4 and o.orders_status = 16, ot.value, null)), 0), 2) last_year_storno,      
  round(coalesce(sum(if(extract(year from o.date_purchased) = extract(year from current_date) - o.orders_status = 14 and o.orders_status = 5 and o.orders_status = 3 and o.orders_status = 2 and o.orders_status = 11 and o.orders_status = 12 and o.orders_status = 6 and o.orders_status = 15 and o.orders_status = 1, ot.value, null)), 0), 2) total_all,
  round(coalesce(sum(if(extract(year from o.date_purchased) = extract(year from current_date) - o.orders_status = 14 and o.orders_status = 5 and o.orders_status = 3 and o.orders_status = 2, ot.value, null)), 0), 2) total_paid,
  round(coalesce(sum(if(extract(year from o.date_purchased) = extract(year from current_date) and o.orders_status = 17, ot.value, null)), 0), 2) total_back,
  round(coalesce(sum(if(extract(year from o.date_purchased) = extract(year from current_date) - o.orders_status = 9 and o.orders_status = 7 and o.orders_status = 13 and o.orders_status = 4 and o.orders_status = 16, ot.value, null)), 0), 2) total_storno,
  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);

Das HTML Template ist bei beiden gleich:

Code: PHP  [Auswählen]

 <td width="25%" valign="top">
                      <table width="100%" border="0">
                                        <tr>
                                                 <td style="background:#eee" align="right">&nbsp;</td>
                                                 <td style="background:#eee; color:#000" align="right"><strong><?php echo TURNOVER_TOTAL; ?></td>
                         <td style="background:#eee; color:#008000" align="right"><strong><?php echo TURNOVER_PAID; ?></strong></td>
                                                 <td style="background:#eee; color:#0600ff" align="right"><strong><?php echo TURNOVER_BACK; ?></td>
                                                 <td style="background:#eee; color:#ff0000" align="right"><strong><?php echo TURNOVER_STORNO; ?></td>
                                        </tr>
                    <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>
                         <td  style="background:#eee" align="right">&nbsp;</td>
                         <td  style="background:#eee" align="right">&nbsp;</td>
                         <td  style="background:#eee" align="right">&nbsp;</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>
                         <td  style="background:#fff" align="right">&nbsp;</td>
                         <td  style="background:#fff" align="right">&nbsp;</td>
                         <td  style="background:#fff" align="right">&nbsp;</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>
                                                 <td  style="background:#eee" align="right"><?php echo $currencies->format($turnover['this_month_paid']); ?></td>
                                                 <td  style="background:#eee" align="right"><?php echo $currencies->format($turnover['this_month_back']); ?></td>
                                                 <td  style="background:#eee" align="right"><?php echo $currencies->format($turnover['this_month_storno']); ?></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>
                                                 <td  style="background:#ccc" align="right"><?php echo $currencies->format($turnover['last_month_paid']); ?></td>
                                                 <td  style="background:#ccc" align="right"><?php echo $currencies->format($turnover['last_month_back']); ?></td>
                                                 <td  style="background:#ccc" align="right"><?php echo $currencies->format($turnover['last_month_storno']); ?></td>                        
                                        </tr>
                                        <tr>
                                                 <td style="background:#a7a7a7"><strong><?php echo TURNOVER_THIS_YEAR; ?>:</strong></td>
                                                 <td style="background:#a7a7a7" align="right"><?php echo $currencies->format($turnover['this_year']); ?></td>
                                                 <td  style="background:#a7a7a7" align="right"><?php echo $currencies->format($turnover['this_year_paid']); ?></td>
                                                 <td  style="background:#a7a7a7" align="right"><?php echo $currencies->format($turnover['this_year_back']); ?></td>
                                                 <td  style="background:#a7a7a7" align="right"><?php echo $currencies->format($turnover['this_year_storno']); ?></td>                        
                                        </tr>
                                        <tr>
                                                 <td style="background:#6c6c6c"><strong><?php echo TURNOVER_LAST_YEAR; ?>:</strong></td>
                                                 <td style="background:#6c6c6c" align="right"><?php echo $currencies->format($turnover['last_year']); ?></td>
                                                 <td  style="background:#6c6c6c" align="right"><?php echo $currencies->format($turnover['last_year_paid']); ?></td>
                                                 <td  style="background:#6c6c6c" align="right"><?php echo $currencies->format($turnover['last_year_back']); ?></td>
                                                 <td  style="background:#6c6c6c" align="right"><?php echo $currencies->format($turnover['last_year_storno']); ?></td>
                    </tr>
                                        <tr>
                                                 <td style="background:#343434; color:#FFF"><strong><?php echo TURNOVER_TOTAL; ?>:</strong></td>
                                                 <td style="background:#343434; color:#FFF" align="right"><?php echo $currencies->format($turnover['total']); ?></td>
                                                 <td  style="background:#343434; color:#FFF" align="right"><?php echo $currencies->format($turnover['total_paid']); ?></td>
                                                 <td  style="background:#343434; color:#FFF" align="right"><?php echo $currencies->format($turnover['total_back']); ?></td>
                                                 <td  style="background:#343434; color:#FFF" align="right"><?php echo $currencies->format($turnover['total_storno']); ?></td>
                    </tr>
                           </table>
               </td>

Vielleicht weiß ja jemand was ich falsch mache.

Gruß Olly