wordpress - para - woocommerce descargar pedidos

Consulta SQL para descargar el informe del pedido en woocommerce

Question

por favor ayuda a completar esto. Necesito descargar el informe de pedidos de woocommerce en formato csv, para eso hice la siguiente consulta:

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);

Y esto es solo un rastro y aquí solo obtengo datos de la tabla de publicaciones.

Pero necesito conectarme a Postmeta y otras tablas, para poder obtener toda la información sobre el pedido.
Al buscar en Internet obtengo el siguiente código, pero no sé cómo integrarlo con mi código.
Consulte la consulta para obtener todos los detalles del pedido:

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

Por favor, ayuda a completar esto, ¿o hay algún buen método en lugar de esto?
No quiero usar complementos.
Actualmente tengo un complemento, pero eso funciona lentamente, por eso estoy haciendo esta página.

Por favor, ayuda a resolver este problema.

Answer 1

Desde mi propia publicación de blog , este es el formato para que el SQL extraiga información de un diseño de tabla de estilo 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);