mysql - SQL 查询以显示包括产品图像在内的最新订单信息 (Prestashop)

Question

使用来自 Prestashop(1.6 和 1.7)的 sql 查询，我需要输出一个 csv 文件，显示(x 数量)我最新订单的详细信息。这必须包括订购的每件商品的详细信息以及每个订单的每件商品的产品图片的位置。

我“认为”我有 sql 的两个部分，但我不知道如何加入它们。

我(理解并)已经在使用这部分

SELECT o.reference, c.firstname, c.lastname, a.lastname, a.firstname, a.address1, a.address2, a.postcode, a.city, a.phone, a.phone_mobile, o.id_order
FROM ps_orders o
LEFT JOIN ps_customer c on (c.id_customer = o.id_customer)
LEFT JOIN ps_address a on (a.id_address = o.id_address_delivery)
OUTER JOIN  (SELECT  *  from ps_order_detail where id_order = o.id_order limit 1) od1 on (o.id_order = o.id_order)

order by id_order desc limit 5

还有这个——我在 stack exchange 上借来的

select
p.`id_product`
,pl.`name`
,concat('http:/mydomain.com/',cl.`link_rewrite`,'/',pl.`link_rewrite`,'-',p.`id_product`,'.html') as "ProductURL"
,case
    when length(im.`id_image`)=6 then
     concat('http:/mydomain.com/img/p/',insert(insert(insert(insert(insert(im.`id_image`,2,0,'/'),4,0,'/'),6,0,'/'),8,0,'/'),10,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=5 then
     concat('http:/mydomain.com/img/p/',insert(insert(insert(insert(im.`id_image`,2,0,'/'),4,0,'/'),6,0,'/'),8,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=4 then
     concat('http:/mydomain.com/img/p/',insert(insert(insert(im.`id_image`,2,0,'/'),4,0,'/'),6,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=3 then
     concat('http:/mydomain.com/img/p/',insert(insert(im.`id_image`,2,0,'/'),4,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=2 then
     concat('http:/mydomain.com/img/p/',insert(im.`id_image`,2,0,'/'),'/',im.`id_image`,'.jpg')
    when length(im.`id_image`)=1 then
     concat('http:/mydomain.com/img/p/',insert(im.`id_image`,2,0,'/'),im.`id_image`,'.jpg')  

    else ''
    end as "ImgURL_1"
FROM `ps_product` p
join `ps_product_lang` pl on pl.`id_product`= p.`id_product`
join `ps_category_lang` cl on cl.`id_category`= p.`id_category_default`
join `ps_image` im on im.`id_product`= p.`id_product`
where pl.`id_lang`=1 and cl.`id_lang`=1 and im.`cover`=1 and p.`active`=1

基本上，我需要您在普通 Prestashop 送货单上获得的所有信息，即客户名称、地址、产品名称等以及产品图片位置。然后，这将被拉入 Adob​​e Indesign 以制作定制的送货单。谢谢

Answer 1

这适用于 PrestaShop 1.6/1.7，将生成包含您需要的所有内容的 CSV 文件，包括图像:

<?php

include('config/config.inc.php');
include('init.php');

$context = Context::getContext();
$id_lang = (int)Configuration::get('PS_LANG_DEFAULT', (int)$context->shop->id);

$order_details = Db::getInstance()->ExecuteS('
SELECT o.reference, c.firstname, c.lastname, a.lastname, a.firstname, a.address1, a.address2, a.postcode, s.name state, a.city, a.phone, a.phone_mobile, o.id_order,
od.product_id, od.product_name, od.product_quantity, od.product_reference, i.id_image, pl.link_rewrite
FROM '._DB_PREFIX_.'orders o
LEFT JOIN '._DB_PREFIX_.'customer c on (c.id_customer = o.id_customer)
LEFT JOIN '._DB_PREFIX_.'address a on (a.id_address = o.id_address_delivery)
LEFT JOIN '._DB_PREFIX_.'state s on (a.id_state = s.id_state)
LEFT JOIN '._DB_PREFIX_.'order_detail od ON (od.id_order = o.id_order)
LEFT JOIN '._DB_PREFIX_.'image i ON (i.id_product = od.product_id)
LEFT JOIN '._DB_PREFIX_.'product_lang pl ON (pl.id_product = od.product_id)
WHERE o.valid = 1 AND i.cover = 1 AND pl.id_lang = '.(int)$id_lang.' AND pl.id_shop = '.(int)$context->shop->id.'
ORDER BY o.id_order DESC
LIMIT 5');

foreach ($order_details as &$od)
    $od['product_image'] = $context->link->getImageLink($od['link_rewrite'], $od['id_image'], 'home_default');

if (count($order_details))
{
    /* CSV Separator */
    $csv_sep = ',';
    $csv_delim = '"';

    /* CSV Header */
    $csv = '';
    foreach ($order_details[0] as $k => $val)
        $csv .= $k.$csv_sep;
    $csv = rtrim($csv, $csv_sep)."\n";

    /* Ordered products */
    foreach ($order_details as $line)
    {
        foreach ($line as $column)
            $csv .= $csv_delim.$column.$csv_delim.$csv_sep;
        $csv = rtrim($csv, $csv_sep)."\n";
    }

    /* Comment the two lines below if you'd just like to display instead of forcing download */
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="orders-'.date('YmdHis').'.csv"');
    echo $csv;
}

结果:

我做了什么:

• 添加了 _DB_PREFIX_，确保它可以在其他可能具有不同于 ps_ 的数据库表前缀的 PrestaShop 商店上工作
• 添加了 o.valid = 1 以仅包含已支付和有效的订单
• 在image表上添加了一个JOIN来选择产品封面图片
• 添加了州名(例如佛罗里达州、加利福尼亚州等)
• 添加了产品名称、ID、引用资料和订购数量
• 使用内置的 PrestaShop 方法检索产品图片
• 动态生成 CSV 文件

希望对您有所帮助!