SELECT
o.id AS pedido_id,
DATE(o.date_created_gmt) AS fecha_pedido,
o.status,
a.first_name AS nombre_cliente,
a.last_name AS apellido_cliente,
o.total_amount AS total,
(
SELECT JSON_UNQUOTE(JSON_EXTRACT(transbank_response, '$.authorizationCode'))
FROM xxt_webpay_rest_transactions
WHERE order_id = o.id
LIMIT 1
) AS authorizationCode,
(
SELECT GROUP_CONCAT(items.order_item_name SEPARATOR ', ')
FROM xxt_woocommerce_order_items AS items
WHERE items.order_id = o.id AND items.order_item_type = 'line_item'
) AS productos
FROM xxt_wc_orders o
LEFT JOIN xxt_wc_order_addresses a ON o.id = a.order_id AND a.address_type = 'billing'
ORDER BY o.date_created_gmt DESC;Notas:
- Trae todo los pedidos (completados, cancelados, fallados)
- Por alguna razón, algunos códigos viene en 00000, pero en WooC se puede ir ver el código en cada uno de los Pedidos. No sé si es problema con la query o bug de WooC. Son un 10% inicialmente.
- Se puede mejorar con fechas o especificando estado. Está pendiente un plugin a partir de esto. Intenté modificar un informe de WooC pero no resulta, quizás sea mejor intentar un informe absolutamente separado.
- Ojo con la tabla donde almacena pedidos WooC. Dependiendo de la versión y opciones de tecnología, varía, por lo que se hace algo difícil obtener resultados al principio, hay que aclara eso.
- El dato que queremos está dentro de un array en el campo transbank_response de una tabla de propia de Webpay.
Versión 2 (trae el mes actual y el anterior, y solo los pedidos con estado wc-completed)
SELECT
o.id AS pedido_id,
DATE(o.date_created_gmt) AS fecha_pedido,
o.status,
a.first_name AS nombre_cliente,
a.last_name AS apellido_cliente,
o.total_amount AS total,
(
SELECT JSON_UNQUOTE(JSON_EXTRACT(transbank_response, '$.authorizationCode'))
FROM xxt_webpay_rest_transactions
WHERE order_id = o.id
LIMIT 1
) AS authorizationCode,
(
SELECT GROUP_CONCAT(items.order_item_name SEPARATOR ', ')
FROM xxt_woocommerce_order_items AS items
WHERE items.order_id = o.id AND items.order_item_type = 'line_item'
) AS productos,
CASE
WHEN o.date_created_gmt >= DATE_FORMAT(NOW(), '%Y-%m-01') THEN 'Mes Actual'
ELSE 'Mes Anterior'
END AS periodo
FROM xxt_wc_orders o
LEFT JOIN xxt_wc_order_addresses a ON o.id = a.order_id AND a.address_type = 'billing'
WHERE o.status = 'wc-completed'
AND o.date_created_gmt >= DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL 1 MONTH)
AND o.date_created_gmt < DATE_ADD(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL 1 MONTH)
ORDER BY o.date_created_gmt DESC;
Código para obtener resumen de eventos y tipos de tickets
SELECT
COALESCE(parent_event.post_title, ticket_variation.post_title) AS event_name,
ticket_type_meta.meta_value AS ticket_type,
SUM(p.product_qty) AS total_quantity
FROM xxt_wc_order_product_lookup AS p
JOIN xxt_wc_orders AS o ON p.order_id = o.id
LEFT JOIN xxt_posts AS ticket_variation ON ticket_variation.ID = p.product_id
LEFT JOIN xxt_posts AS parent_event ON ticket_variation.post_parent = parent_event.ID
LEFT JOIN xxt_woocommerce_order_itemmeta AS ticket_type_meta
ON ticket_type_meta.order_item_id = p.order_item_id
AND ticket_type_meta.meta_key = 'pa_tipo-de-ticket'
-- Join para categorías
JOIN xxt_term_relationships AS tr ON tr.object_id = COALESCE(parent_event.ID, ticket_variation.ID)
JOIN xxt_term_taxonomy AS tt ON tt.term_taxonomy_id = tr.term_taxonomy_id AND tt.taxonomy = 'product_cat'
JOIN xxt_terms AS t ON t.term_id = tt.term_id
WHERE o.status = 'wc-completed'
AND t.name = 'Eventos'
GROUP BY event_name, ticket_type
ORDER BY event_name ASC, ticket_type ASC;
SELECT
COALESCE(parent_event.post_title, ticket_variation.post_title) AS event_name,
ticket_type_meta.meta_value AS ticket_type,
SUM(p.product_qty) AS total_quantity,
SUM(CAST(line_subtotal.meta_value AS DECIMAL(10,2))) AS total_sales
FROM xxt_wc_order_product_lookup AS p
JOIN xxt_wc_orders AS o ON p.order_id = o.id
LEFT JOIN xxt_posts AS ticket_variation ON ticket_variation.ID = p.product_id
LEFT JOIN xxt_posts AS parent_event ON ticket_variation.post_parent = parent_event.ID
LEFT JOIN xxt_woocommerce_order_itemmeta AS ticket_type_meta
ON ticket_type_meta.order_item_id = p.order_item_id
AND ticket_type_meta.meta_key = ‘pa_tipo-de-ticket’
LEFT JOIN xxt_woocommerce_order_itemmeta AS line_subtotal
ON line_subtotal.order_item_id = p.order_item_id
AND line_subtotal.meta_key = ‘_line_subtotal’
— Join para categorías
JOIN xxt_term_relationships AS tr ON tr.object_id = COALESCE(parent_event.ID, ticket_variation.ID)
JOIN xxt_term_taxonomy AS tt ON tt.term_taxonomy_id = tr.term_taxonomy_id AND tt.taxonomy = ‘product_cat’
JOIN xxt_terms AS t ON t.term_id = tt.term_id
WHERE o.status = ‘wc-completed’
AND t.name = ‘Eventos’
GROUP BY event_name, ticket_type
ORDER BY event_name ASC, ticket_type ASC;