Jump to content

Edit History

Wuschel

Wuschel

Oder du nimmst einfach den folgenden Code, um deine Bestellungen zu exportieren: 

SELECT 
    o.`id_order` AS `ID`,
    o.`reference` AS `BestellNr`,
    c.`firstname` AS `Nachname`, 
    c.`lastname` AS `Nachname`,
    ad.`address1` AS ` Strasse`,
    ad.`address2` AS `Adresszusatz`,
    ad.`city` AS `Stadt`,
    st.`name` AS `Bundesland`,
    co.`name` AS `Land`,
    ad.`postcode` AS `PLZ`,
    cou.`iso_code` AS `Kuerzel`,
    c.`email` AS `Email`,
    ad.`phone_mobile` AS `Tel`,
    SUM(d.`product_quantity`) AS `Artikelmenge`,
    REPLACE(ROUND(o.`total_products_wt`, 2), '.', ',') AS `Umsatz`,
    cu.`name` AS `Waehrung`,  
    p.`reference` AS `SKU`,
    d.`product_quantity` AS `Menge`
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`

Die REPLACE-Anweisung, die für das erforderliche Dezimalkomma in Excel sorgt, funktioniert erst ab Prestashop 1.7.

EDIT:

Um den Befehl REPLACE auch in 1.5 und 1.6 verfügbar zu machen, genügt übrigens eine kleine Änderung in der Datei /classes/RequestSql.php (ab Ziele 45):

    public $tested = array(
        'required' => array('SELECT', 'FROM'),
        'option' => array('WHERE', 'ORDER', 'LIMIT', 'HAVING', 'GROUP', 'UNION'),
        'operator' => array(
            'AND', '&&', 'BETWEEN', 'AND', 'BINARY', '&', '~', '|', '^', 'CASE', 'WHEN', 'END', 'DIV', '/', '<=>', '=', '>=',
            '>', 'IS', 'NOT', 'NULL', '<<', '<=', '<', 'LIKE', '-', '%', '!=', '<>', 'REGEXP', '!', '||', 'OR', '+', '>>', 'RLIKE', 'SOUNDS', '*',
            '-', 'XOR', 'IN'
        ),
        'function' => array(
            'AVG', 'SUM', 'COUNT', 'MIN', 'MAX', 'STDDEV', 'STDDEV_SAMP', 'STDDEV_POP', 'VARIANCE', 'VAR_SAMP', 'VAR_POP',
            'GROUP_CONCAT', 'BIT_AND', 'BIT_OR', 'BIT_XOR', 'REPLACE'
        ),
        'unauthorized' => array(
            'DELETE', 'ALTER', 'INSERT', 'REPLACE', 'CREATE', 'TRUNCATE', 'OPTIMIZE', 'GRANT', 'REVOKE', 'SHOW', 'HANDLER', 
            (....)

Wuschel

Wuschel

Oder du nimmst einfach den folgenden Code, um deine Bestellungen zu exportieren: 

SELECT 
    o.`id_order` AS `ID`,
    o.`reference` AS `BestellNr`,
    c.`firstname` AS `Nachname`, 
    c.`lastname` AS `Nachname`,
    ad.`address1` AS ` Strasse`,
    ad.`address2` AS `Adresszusatz`,
    ad.`city` AS `Stadt`,
    st.`name` AS `Bundesland`,
    co.`name` AS `Land`,
    ad.`postcode` AS `PLZ`,
    cou.`iso_code` AS `Kuerzel`,
    c.`email` AS `Email`,
    ad.`phone_mobile` AS `Tel`,
    SUM(d.`product_quantity`) AS `Artikelmenge`,
    REPLACE(ROUND(o.`total_products_wt`, 2), '.', ',') AS `Umsatz`,
    cu.`name` AS `Waehrung`,  
    p.`reference` AS `SKU`,
    d.`product_quantity` AS `Menge`
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`

Die REPLACE-Anweisung, die für das erforderliche Dezimalkomma in Excel sorgt, funktioniert erst ab Prestashop 1.7.

EDIT:

Um den Befehl REPLACE auch in 1.5 und 1.6 verfügbar zu machen, genügt übrigens eine kleine Änderung in der Datei /classes/RequestSql.php:

    public $tested = array(
        'required' => array('SELECT', 'FROM'),
        'option' => array('WHERE', 'ORDER', 'LIMIT', 'HAVING', 'GROUP', 'UNION'),
        'operator' => array(
            'AND', '&&', 'BETWEEN', 'AND', 'BINARY', '&', '~', '|', '^', 'CASE', 'WHEN', 'END', 'DIV', '/', '<=>', '=', '>=',
            '>', 'IS', 'NOT', 'NULL', '<<', '<=', '<', 'LIKE', '-', '%', '!=', '<>', 'REGEXP', '!', '||', 'OR', '+', '>>', 'RLIKE', 'SOUNDS', '*',
            '-', 'XOR', 'IN'
        ),
        'function' => array(
            'AVG', 'SUM', 'COUNT', 'MIN', 'MAX', 'STDDEV', 'STDDEV_SAMP', 'STDDEV_POP', 'VARIANCE', 'VAR_SAMP', 'VAR_POP',
            'GROUP_CONCAT', 'BIT_AND', 'BIT_OR', 'BIT_XOR', 'REPLACE'
        ),
        'unauthorized' => array(
            'DELETE', 'ALTER', 'INSERT', 'REPLACE', 'CREATE', 'TRUNCATE', 'OPTIMIZE', 'GRANT', 'REVOKE', 'SHOW', 'HANDLER', 
            (....)

Wuschel

Wuschel

Oder du nimmst einfach den folgenden Code, um deine Bestellungen zu exportieren: 

SELECT 
    o.`id_order` AS `ID`,
    o.`reference` AS `BestellNr`,
    c.`firstname` AS `Nachname`, 
    c.`lastname` AS `Nachname`,
    ad.`address1` AS ` Strasse`,
    ad.`address2` AS `Adresszusatz`,
    ad.`city` AS `Stadt`,
    st.`name` AS `Bundesland`,
    co.`name` AS `Land`,
    ad.`postcode` AS `PLZ`,
    cou.`iso_code` AS `Kuerzel`,
    c.`email` AS `Email`,
    ad.`phone_mobile` AS `Tel`,
    SUM(d.`product_quantity`) AS `Artikelmenge`,
    REPLACE(ROUND(o.`total_products_wt`, 2), '.', ',') AS `Umsatz`,
    cu.`name` AS `Waehrung`,  
    p.`reference` AS `SKU`,
    d.`product_quantity` AS `Menge`
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`

Die REPLACE-Anweisung, die für das erforderliche Dezimalkomma in Excel sorgt, funktioniert erst ab Prestashop 1.7.
 

Wuschel

Wuschel

Oder du nimmst einfach den folgenden Code, um deine Bestellungen zu exportieren: 

SELECT 
    o.`id_order` AS `ID`,
    o.`reference` AS `BestellNr`,
    c.`firstname` AS `Nachname`, 
    c.`lastname` AS `Nachname`,
    ad.`address1` AS ` Strasse`,
    ad.`address2` AS `Adresszusatz`,
    ad.`city` AS `Stadt`,
    st.`name` AS `Bundesland`,
    co.`name` AS `Land`,
    ad.`postcode` AS `PLZ`,
    cou.`iso_code` AS `Kuerzel`,
    c.`email` AS `Email`,
    ad.`phone_mobile` AS `Tel`,
    SUM(d.`product_quantity`) AS `Artikelmenge`,
    REPLACE(ROUND(o.`total_products_wt`, 2), '.', ',') AS `Umsatz`,
    cu.`name` AS `Waehrung`,  
    p.`reference` AS `SKU`,
    d.`product_quantity` AS `Menge`
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`

Die REPLACE-Anweisung, die für das erforderliche Dezimalkomma in Excel sorgt, funktioniert erst ab Prestashop 1.7.

EDIT:

Um den Befehl REPLACE auch in 1.5 und 1.6 verfügbar zu machen, genügt übrigens eine kleine Änderung in der Datei /classes/RequestSql.php:

    public $tested = array(
        'required' => array('SELECT', 'FROM'),
        'option' => array('WHERE', 'ORDER', 'LIMIT', 'HAVING', 'GROUP', 'UNION'),
        'operator' => array(
            'AND', '&&', 'BETWEEN', 'AND', 'BINARY', '&', '~', '|', '^', 'CASE', 'WHEN', 'END', 'DIV', '/', '<=>', '=', '>=',
            '>', 'IS', 'NOT', 'NULL', '<<', '<=', '<', 'LIKE', '-', '%', '!=', '<>', 'REGEXP', '!', '||', 'OR', '+', '>>', 'RLIKE', 'SOUNDS', '*',
            '-', 'XOR', 'IN'
        ),
        'function' => array(
            'AVG', 'SUM', 'COUNT', 'MIN', 'MAX', 'STDDEV', 'STDDEV_SAMP', 'STDDEV_POP', 'VARIANCE', 'VAR_SAMP', 'VAR_POP',
            'GROUP_CONCAT', 'BIT_AND', 'BIT_OR', 'BIT_XOR', 'REPLACE'
        ),
        'unauthorized' => array(
            'DELETE', 'ALTER', 'INSERT', 'REPLACE', 'CREATE', 'TRUNCATE', 'OPTIMIZE', 'GRANT', 'REVOKE', 'SHOW', 'HANDLER', 
            (....)
 

Wuschel

Wuschel

Oder du nimmst einfach den folgenden Code, um deine Bestellungen zu exportieren: 

SELECT 
    o.`id_order` AS `ID`,
    o.`reference` AS `BestellNr`,
    c.`firstname` AS `Nachname`, 
    c.`lastname` AS `Nachname`,
    ad.`address1` AS ` Strasse`,
    ad.`address2` AS `Adresszusatz`,
    ad.`city` AS `Stadt`,
    st.`name` AS `Bundesland`,
    co.`name` AS `Land`,
    ad.`postcode` AS `PLZ`,
    cou.`iso_code` AS `Kuerzel`,
    c.`email` AS `Email`,
    ad.`phone_mobile` AS `Tel`,
    SUM(d.`product_quantity`) AS `Artikelmenge`,
    REPLACE(ROUND(o.`total_products_wt`, 2), '.', ',') AS `Umsatz`,
    cu.`name` AS `Waehrung`,  
    p.`reference` AS `SKU`,
    d.`product_quantity` AS `Menge`
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`

Die REPLACE-Anweisung, die für das Dezimalkomma in Excel sorgt, funktioniert erst ab Prestashop 1.7.

Wuschel

Wuschel

Oder du nimmst einfach den folgenden Code, um deine Bestellungen zu exportieren: 

SELECT 
    o.`id_order` AS `ID`,
    o.`reference` AS `BestellNr`,
    c.`firstname` AS `Nachname`, 
    c.`lastname` AS `Nachname`,
    ad.`address1` AS ` Strasse`,
    ad.`address2` AS `Adresszusatz`,
    ad.`city` AS `Stadt`,
    st.`name` AS `Bundesland`,
    co.`name` AS `Land`,
    ad.`postcode` AS `PLZ`,
    cou.`iso_code` AS `Kuerzel`,
    c.`email` AS `Email`,
    ad.`phone_mobile` AS `Tel`,
    SUM(d.`product_quantity`) AS `Artikelmenge`,
    REPLACE(ROUND(o.`total_products_wt`, 2), '.', ',') AS `Umsatz`,
    cu.`name` AS `Waehrung`,  
    p.`reference` AS `SKU`,
    d.`product_quantity` AS `Menge`
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`

 

×
×
  • Create New...