Sql查询在woocommerce中下载订单报告
Sql查询在woocommerce中下载订单报告
提问于 2016-04-10 05:40:18
请帮忙完成这件事。我需要以csv格式下载woocommerce的订单报告，为此我进行了以下查询：
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=data.csv');
// create a file pointer connected to the output stream
$output = fopen('php://output', 'w');
fputcsv($output, array('ID', 'Date', 'Status','Name'));
$rows = mysql_query('SELECT ID,post_date,post_status,post_name FROM wp_posts WHERE post_date LIKE "%2016-03-30%"');
while ($row = mysql_fetch_assoc($rows)) fputcsv($output, $row);这只是一条小径，这里我只从post表中获取数据。
但是我需要连接到postmeta和其他表，这样我就可以获得关于订单的所有信息。
通过在互联网上搜索，我得到了以下代码，但我不知道如何与我的代码进行集成。
请参见获取所有订单详细信息的查询：
select
p.ID as order_id,
p.post_date,
max( CASE WHEN pm.meta_key = '_billing_email' and p.ID = pm.post_id THEN pm.meta_value END ) as billing_email,
max( CASE WHEN pm.meta_key = '_billing_first_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_first_name,
max( CASE WHEN pm.meta_key = '_billing_last_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_last_name,
max( CASE WHEN pm.meta_key = '_billing_address_1' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_address_1,
max( CASE WHEN pm.meta_key = '_billing_address_2' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_address_2,
max( CASE WHEN pm.meta_key = '_billing_city' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_city,
max( CASE WHEN pm.meta_key = '_billing_state' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_state,
max( CASE WHEN pm.meta_key = '_billing_postcode' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_postcode,
max( CASE WHEN pm.meta_key = '_shipping_first_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_first_name,
max( CASE WHEN pm.meta_key = '_shipping_last_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_last_name,
max( CASE WHEN pm.meta_key = '_shipping_address_1' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_address_1,
max( CASE WHEN pm.meta_key = '_shipping_address_2' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_address_2,
max( CASE WHEN pm.meta_key = '_shipping_city' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_city,
max( CASE WHEN pm.meta_key = '_shipping_state' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_state,
max( CASE WHEN pm.meta_key = '_shipping_postcode' and p.ID = pm.post_id THEN pm.meta_value END ) as _shipping_postcode,
max( CASE WHEN pm.meta_key = '_order_total' and p.ID = pm.post_id THEN pm.meta_value END ) as order_total,
max( CASE WHEN pm.meta_key = '_order_tax' and p.ID = pm.post_id THEN pm.meta_value END ) as order_tax,
max( CASE WHEN pm.meta_key = '_paid_date' and p.ID = pm.post_id THEN pm.meta_value END ) as paid_date,
( select group_concat( order_item_name separator '|' ) from wp_woocommerce_order_items where order_id = p.ID ) as order_items
from
wp_posts as p,
wp_postmeta as pm
where
post_type = 'shop_order' and
p.ID = pm.post_id and
post_date BETWEEN '2015-01-01' AND '2015-07-08'
and post_status = 'wc-completed'
group by
p.ID请帮助完成这一点，或有什么好的方法，而不是这个？
我不想使用插件。
目前我有一个插件，但这是缓慢的工作，这就是为什么我做这个网页。
请帮助解决这个问题。
回答 1
Stack Overflow用户
回答已采纳
发布于 2016-08-01 11:23:33
从我自己的博客帖子中，这是SQL从EAV样式表布局中提取信息的格式。
$reportQuery = "
SELECT
A.ID as order_id
, B.meta_value as b_first_name
, C.meta_value as b_last_name
, D.meta_value as b_address_1
, E.meta_value as b_address_2
, F.meta_value as b_country
, G.meta_value as b_state
, H.meta_value as b_city
, I.meta_value as b_postcode
, J.meta_value as b_user_id
, K.user_email as b_email
FROM wp_posts as A
LEFT JOIN wp_postmeta B
ON A.id = B.post_id AND B.meta_key = '_billing_first_name'
LEFT JOIN wp_postmeta C
ON A.id = C.post_id AND C.meta_key = '_billing_last_name'
LEFT JOIN wp_postmeta D
ON A.id = D.post_id AND D.meta_key = '_billing_address_1'
LEFT JOIN wp_postmeta E
ON A.id = E.post_id AND E.meta_key = '_billing_address_2'
LEFT JOIN wp_postmeta F
ON A.id = F.post_id AND F.meta_key = '_billing_country'
LEFT JOIN wp_postmeta G
ON A.id = G.post_id AND G.meta_key = '_billing_state'
LEFT JOIN wp_postmeta H
ON A.id = H.post_id AND H.meta_key = '_billing_city'
LEFT JOIN wp_postmeta I
ON A.id = I.post_id AND I.meta_key = '_billing_postcode'
LEFT JOIN wp_postmeta J
ON A.id = J.post_id AND J.meta_key = '_customer_user'
LEFT JOIN wp_users K
ON J.meta_value = K.ID
WHERE A.post_type = 'shop_order'
AND A.post_status = 'wc-completed';
AND A.post_date_gmt >= DATE_SUB(NOW(), INTERVAL 1 DAY)
";
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=woocommerce-'.date('Y-m-d').'.csv');
// create a file pointer connected to the output stream
$output = fopen('php://output', 'w');
$rows = mysql_query($reportQuery);
while ($row = mysql_fetch_assoc($rows)) fputcsv($output, $row);
fclose($output);页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/36526268
复制相关文章
相似问题
腾讯云开发者
