Bestellungen Exportieren (SQL-Manager) und mit Excel auswerten

[Lausli]

Guten morgen.

 

Ich möchte meine Excel Wirtschaft etwas aufpolieren. Daher frage ich mal euch u.a. um Rat und Tat wie ihr das macht (wenn ihr Excel verwendet) und ob ihr mir einen Tipp geben könnt. Excel ist nicht meine Stärke.

 

Achso: Excel ist hier: Libre Office

 

Ich habe einen Export:

id_order Status Produkt Preis Menge Bezahlung Datum Kunde Strasse PLZ Ort Land Kundengruppe

SELECT d.id_order, os.name AS Status, d.product_name AS Produkt, ROUND(d.product_price,2) AS Preis, d.product_quantity AS Menge, o.payment AS Bezahlung, o.date_upd AS Datum, CONCAT_WS(' ', a.firstname, a.lastname) AS Kunde, a.address1 AS Strasse, a.postcode AS PLZ, a.city AS Ort, cl.name AS Land, gl.name AS Kundengruppe
    FROM ps_order_detail d
    LEFT JOIN ps_orders o ON (d.id_order = o.id_order)
    LEFT JOIN ps_customer g ON (o.id_customer = g.id_customer)
    LEFT JOIN ps_address AS a ON (o.id_customer = a.id_customer)
    LEFT JOIN ps_country_lang AS cl ON (a.id_country = cl.id_country )
    LEFT JOIN ps_group_lang gl ON (g.id_default_group = gl.id_group)
    LEFT JOIN ps_order_state_lang os ON (o.current_state = os.id_order_state)
 GROUP BY d.id_order

Was hier noch fehlt:

• Retouren
• Einkaufspreis

 

Was ich gern mit Excel bzw. Libre Office machen möchte

 

Blatt 1:

2015

Gesamtverkäufe:

Gesamtumsatz:

Mit PayPal Bezahlt:

> PayPal gebühren: 1,9% + 0,35 cent

Mit Überweisung  bezahlt

Über eBay verkauft:

Über Shop verkauft:

 

Das ganze wird dann natürlich für jedes Jahr gemacht.

 

So kann man dann einfach die Verkäufe in Blatt 2 Kopieren und hat eine Statistik mit der man ggf. etwas anfangen kann.

 

Mein Problem ist, dass der Export ja die Werte mit einem . ausgibt. Also 15.24.

Das lässt sich mit:

=SUMME(Bestellungen.D2:D904)

nicht addieren.

 

Außerdem steht ja da der VK für einen Artikel. In der Zeile daneben also Bestellungen.E2:E904 steht die Menge.

Das Heißt, es müsste beim Auswerten (wohl per Matrix) mit berücksichtigt werden.

Oder gleich im Export.

 

Um den Export ggf. leichter zu berechnen fiele mir noch ein statt einem Blatt "Bestellungen" dann halt Blätter so anzulegen: "Bestellungen 2014", "Bestellungen 2015", "Bestellungen 2016"

 

Kennt sich jemand mit Excel entsprechend aus? Oder hat jemand eine andere Idee oder macht es besser?

Möchte das dieses Jahr besser machen wie im letzten

Edited November 7, 2016 by Lausli (see edit history)

[rictools]

Mein Problem ist, dass der Export ja die Werte mit einem . ausgibt. Also 15.24.

Erstelle eine neue Spalte mit =WERT(WECHSELN(A1;".";","))

[eleazar]

Na ja, man kann in Excel auch einfach die Spalte markieren und mit Suchen & Ersetzen arbeiten.

 

Aber es geht auch direkt in der SQL-Abfrage:

SELECT REPLACE (ROUND(d.product_price,2), '.', ',') AS Preis

[Lausli]

Danke, ich bevorzuge das direkte ersetzen, weil am Ende soll es möglich sein, ohne weiteren Aufwand den Export einfach einzufügen und dennoch eine Statistik zu bekommen

Danke bis hier her für die Hilfe

 

Wenn ich mal alles beisammen hab bekommt ihr das Ergebnis

[Lausli]

ist ein nicht autorisiertes Schlüsselwort.

 

 

Ich denke das liegt an "REPLACE"

SELECT d.id_order, 
os.name AS Status, 
d.product_name AS Produkt, 
ROUND(d.product_price,2) AS Preis, 
REPLACE (ROUND(d.product_price,2), '.', ',') AS Preis2,
d.product_quantity AS Menge, 
o.payment AS Bezahlung,
o.date_upd AS Datum, 
CONCAT_WS(' ', a.firstname, a.lastname) AS Kunde, 
a.address1 AS Strasse, 
a.postcode AS PLZ, 
a.city AS Ort, 
cl.name AS Land, 
gl.name AS Kundengruppe

    FROM ps_order_detail d
    LEFT JOIN ps_orders o ON (d.id_order = o.id_order)
    LEFT JOIN ps_customer g ON (o.id_customer = g.id_customer)
    LEFT JOIN ps_address AS a ON (o.id_customer = a.id_customer)
    LEFT JOIN ps_country_lang AS cl ON (a.id_country = cl.id_country )
    LEFT JOIN ps_group_lang gl ON (g.id_default_group = gl.id_group)
    LEFT JOIN ps_order_state_lang os ON (o.current_state = os.id_order_state)
 GROUP BY d.id_order

Das geht so nicht.

[Lausli]

So, ich habe jetzt einen SQL Export für die Bestellungen so fertig, dass man etwas damit anfangen kann.
Langsam steige ich dahinter wie das funktioniert.
 
Bisher kann man folgendes exportieren:

• ID     
• Bestellnummer     
• Gesamtpreis  
• Einkaufspreis  
• Währung     
• Datum     
• Vorname     
• Nachname   
• Empfängeradresse 1   
• Empfängeradresse 2     
• PLZ   
• Stadt     
• Bundesland     
• Land     
• Landesvorwahl     
• Kundenemail     
• Kunde Handynummer     
• Artikel SKU     
• Produkt-Menge     
• Zahlart

Was nicht funktioniert

• Bei mehreren Bestellungen wird nur ein Artikelname ausgegeben
• Bei mehreren  Bestellungen wird nur ein EK ausgegeben
• Das ersetzen von . in ,

Eventuell hat ja jemand noch ne Idee. Ich werde es auch weiter versuchen

SELECT o.`id_order` AS `ID`,
    o.`reference` AS `Bestellnummer`,
    ROUND(o.`total_products_wt`,2) AS Gesamtpreis,
    p.`wholesale_price` AS `Einkaufspreis`,
    cu.`name` AS `Währung`,    

    c.`date_upd` AS `Datum`,

    c.`firstname` AS `Vorname`,
    c.`lastname` AS `Nachname`,
    ad.`address1` AS ` Empfängeradresse 1`,
    ad.`address2` AS ` Empfängeradresse 2`,
    ad.`postcode` AS `PLZ`,
    ad.`city` AS `Stadt`,
    st.`name` AS `Bundesland`,
    co.`name` AS `Land`,
    cou.`iso_code` AS `Landesvorwahl`,
    c.`email` AS `Kundenemail`,
    ad.`phone_mobile` AS `Kunde Handynummer`,

    p.`reference` AS `Artikel SKU`,
    d.`product_quantity` AS `Produkt-Menge`,
    o.`payment` AS `Zahlart`
    
    
FROM ps_orders o
    LEFT JOIN `ps_order_detail` d ON (o.id_order = d.id_order)
    LEFT JOIN `ps_customer` c ON (o.id_customer = c.id_customer)
    LEFT JOIN `ps_address` ad ON(o.`id_customer`=ad.`id_customer`)
    LEFT JOIN `ps_state` st ON(ad.`id_state`=st.`id_state`)
    LEFT JOIN `ps_country_lang` co ON(ad.`id_country`=co.`id_country`)
    LEFT JOIN `ps_country` cou ON(ad.`id_country`=cou.`id_country`)
    LEFT JOIN `ps_product` p ON(d.`product_id`=p.`id_product`)
    LEFT JOIN `ps_currency` cu ON(o.`id_currency`=cu.`id_currency`)
    
WHERE o.`valid` = 1
GROUP BY o.id_order,d.id_order_detail
ORDER BY o.`id_order`

Nachtrag:

Noch den Gesamtpreis auf 2 stellen Gerundet

Edited November 9, 2016 by Lausli (see edit history)

[eleazar]

Sorry, mein Fehler! In PrestaShop dürfen es eben keine einfachen Beistriche sein, das geht nur in PHPMyAdmin! Es muss also heißen:

REPLACE (ROUND(d.product_price,2), ".", ",") AS Preis

Nur in PHPMyAdmin darfst du auch schreiben:

REPLACE (ROUND(d.product_price,2), '.', ',') AS Preis

[Lausli]

Ach Prestashop ist da ja sowieso sehr eigen. Nicht deine Schuld, meine.

Es ist nur verwirrend, das einige statements mit  ' oder ` oder " sind.

 

Allerdings geht auch:

REPLACE (ROUND(o.`total_products_wt`,2), ".", ",") AS Gesamtpreis,

nicht.

 

Prestashop meckert ja wegen eines nicht zulässigen Schlüsselwortes. Finde keine Dokumentation zu erlaubten Schlüsselworten.

[eleazar]

Ach, weißt du was, dann mach es eben auf die brutale Tour.

 

Öffne die Datei /controllers/admin/AdminRequestSqlController.php

und deaktiviere die Zeilen 487-489:

 //case 'testedUnauthorized':
 //    $this->errors[] = sprintf(Tools::displayError('Is an unauthorized keyword.'), $e[$key]);
 //break;

[Lausli]

:lol:  Sehr gut