Untitled

  <pre>{JSON.stringify(posts, null, 2)}</pre>
  
<<OBTENER LOS PEDIDOS SIN REPETIR CLIENTE>>
Para obtener una lista de pedidos con emails únicos (sin repetir correos electrónicos), puedes utilizar una consulta SQL que agrupe los resultados por el correo electrónico y seleccione el primer pedido (o cualquier otro criterio específico) para cada correo electrónico. Aquí te muestro cómo hacerlo:

sql
Copy code
SELECT
    MIN(posts.ID) as order_id,
    pm_billing_email.meta_value as customer_email,
    CONCAT(pm_billing_first_name.meta_value, ' ', pm_billing_last_name.meta_value) as billing_name,
    pm_billing_phone.meta_value as billing_phone
FROM
    conft_posts as posts
JOIN
    conft_postmeta as pm_billing_email ON posts.ID = pm_billing_email.post_id AND pm_billing_email.meta_key = '_billing_email'
LEFT JOIN
    conft_postmeta as pm_billing_first_name ON posts.ID = pm_billing_first_name.post_id AND pm_billing_first_name.meta_key = '_billing_first_name'
LEFT JOIN
    conft_postmeta as pm_billing_last_name ON posts.ID = pm_billing_last_name.post_id AND pm_billing_last_name.meta_key = '_billing_last_name'
LEFT JOIN
    conft_postmeta as pm_billing_phone ON posts.ID = pm_billing_phone.post_id AND pm_billing_phone.meta_key = '_billing_phone'
WHERE
    posts.post_type = 'shop_order'
GROUP BY
    pm_billing_email.meta_value
ORDER BY
    MIN(posts.ID);
En esta consulta:

Se utiliza MIN(posts.ID) para seleccionar el ID del primer pedido realizado por cada correo electrónico. Puedes cambiar MIN a MAX si prefieres obtener el pedido más reciente para cada correo electrónico.
Se agrupa por pm_billing_email.meta_value para asegurar que cada correo electrónico sea único en el conjunto de resultados.
Se incluyen las columnas order_id, customer_email, billing_name, y billing_phone.
Esta consulta te dará una lista de pedidos donde cada correo electrónico aparece una sola vez, junto con los detalles del pedido asociado. Dependiendo de tus requisitos específicos, podrías querer ajustar qué pedido se selecciona para cada correo electrónico (por ejemplo, el más reciente, el más antiguo, etc.).





----------


Sí, puedes incluir la cantidad de cada producto pedido. Para hacerlo, debes acceder al metadato correspondiente a la cantidad del producto en cada pedido. En WooCommerce, esta información suele almacenarse en la tabla conft_woocommerce_order_itemmeta con una clave de metadato como _qty. Aquí está la consulta modificada para incluir la cantidad de cada producto:

sql
Copy code
SELECT
    p.ID as order_id,
    p.post_date as order_date,
    p.post_status as order_status,
    SUM(CAST(oim_qty.meta_value AS UNSIGNED)) as quantity
FROM
    conft_posts as p
JOIN
    conft_woocommerce_order_items as oi ON p.ID = oi.order_id
JOIN
    conft_woocommerce_order_itemmeta as oim ON oi.order_item_id = oim.order_item_id
LEFT JOIN
    conft_woocommerce_order_itemmeta as oim_qty ON oi.order_item_id = oim_qty.order_item_id AND oim_qty.meta_key = '_qty'
WHERE
    p.post_type = 'shop_order'
    AND oim.meta_key = '_product_id'
    AND oim.meta_value = '120711'
GROUP BY
    p.ID
ORDER BY
    p.ID DESC;
En esta consulta:

Se une conft_woocommerce_order_itemmeta (alias oim_qty) para obtener la cantidad (_qty) de cada ítem en el pedido.
Se usa SUM(CAST(oim_qty.meta_value AS UNSIGNED)) para sumar las cantidades, en caso de que un pedido contenga más de una línea del mismo producto. Si cada pedido sólo puede tener una línea del producto especificado, podrías utilizar simplemente oim_qty.meta_value.
Los demás aspectos de la consulta son similares a la versión anterior, filtrando por pedidos que contienen el producto con ID 120711.
Esta consulta te proporcionará una lista de todos los pedidos que contienen el producto con ID 120711, junto con la fecha, el estado del pedido y la cantidad total de ese producto en cada pedido.

-----------


Para obtener una lista de pedidos en WooCommerce con los estados 'processing' y 'procesando-local', 
incluyendo detalles como el ID del pedido, fecha de creación, email del cliente, nombre del cliente, total del pedido, 
método de pago y el estado del pedido, puedes utilizar la siguiente consulta SQL:

SELECT
    posts.ID AS order_id,
    posts.post_date AS order_date,
    posts.post_status AS order_status,
    pm_billing_email.meta_value AS customer_email,
    CONCAT(pm_billing_first_name.meta_value, ' ', pm_billing_last_name.meta_value) AS customer_name,
    pm_order_total.meta_value AS order_total,
    pm_payment_method.meta_value AS payment_method
FROM
    conft_posts AS posts
LEFT JOIN
    conft_postmeta AS pm_billing_email ON posts.ID = pm_billing_email.post_id AND pm_billing_email.meta_key = '_billing_email'
LEFT JOIN
    conft_postmeta AS pm_billing_first_name ON posts.ID = pm_billing_first_name.post_id AND pm_billing_first_name.meta_key = '_billing_first_name'
LEFT JOIN
    conft_postmeta AS pm_billing_last_name ON posts.ID = pm_billing_last_name.post_id AND pm_billing_last_name.meta_key = '_billing_last_name'
LEFT JOIN
    conft_postmeta AS pm_order_total ON posts.ID = pm_order_total.post_id AND pm_order_total.meta_key = '_order_total'
LEFT JOIN
    conft_postmeta AS pm_payment_method ON posts.ID = pm_payment_method.post_id AND pm_payment_method.meta_key = '_payment_method_title'
WHERE
    posts.post_type = 'shop_order'
    AND posts.post_status IN ('wc-processing', 'wc-procesando-local')
ORDER BY
    posts.ID DESC;


-------

ESTA ES COMO LA DE ARRIBA, PERO TRASE SOLO COMPLETED Y ADEMAS SOLO DE DICIEMNRE:
SELECT
    posts.ID AS order_id,
    posts.post_date AS order_date,
    posts.post_status AS order_status,
    pm_billing_email.meta_value AS customer_email,
    CONCAT(pm_billing_first_name.meta_value, ' ', pm_billing_last_name.meta_value) AS customer_name,
    pm_order_total.meta_value AS order_total,
    pm_payment_method.meta_value AS payment_method
FROM
    conft_posts AS posts
LEFT JOIN
    conft_postmeta AS pm_billing_email ON posts.ID = pm_billing_email.post_id AND pm_billing_email.meta_key = '_billing_email'
LEFT JOIN
    conft_postmeta AS pm_billing_first_name ON posts.ID = pm_billing_first_name.post_id AND pm_billing_first_name.meta_key = '_billing_first_name'
LEFT JOIN
    conft_postmeta AS pm_billing_last_name ON posts.ID = pm_billing_last_name.post_id AND pm_billing_last_name.meta_key = '_billing_last_name'
LEFT JOIN
    conft_postmeta AS pm_order_total ON posts.ID = pm_order_total.post_id AND pm_order_total.meta_key = '_order_total'
LEFT JOIN
    conft_postmeta AS pm_payment_method ON posts.ID = pm_payment_method.post_id AND pm_payment_method.meta_key = '_payment_method_title'
WHERE
    posts.post_type = 'shop_order'
    AND posts.post_status = 'wc-completed'
    AND posts.post_date >= '2023-12-01 00:00:00'
    AND posts.post_date <= '2023-12-31 23:59:59'
ORDER BY
    posts.ID DESC;



    -------------------------------------

    Esta consulta SQL recuperará las columnas que mencionaste, así como los metadatos específicos que solicitaste para las 
    variaciones de productos. Asegúrate de reemplazar conft_ con tu prefijo de base de datos real si es diferente.

    SELECT
    v.ID AS ID,
    v.post_date AS post_date,
    v.post_title AS post_title,
    v.post_excerpt AS post_excerpt,
    v.post_status AS post_status,
    v.post_parent AS post_parent,
    m_svc_costo.meta_value AS svc_costo,
    m_svc_ubicacion.meta_value AS svc_ubicacion_bodega,
    m_cantidad_inicial.meta_value AS cantidad_inicial,
    m_sku.meta_value AS sku,
    m_price.meta_value AS price,
    m_sale_price.meta_value AS sale_price,
    m_stock_status.meta_value AS stock_status,
    m_stock.meta_value AS stock
FROM
    conft_posts v
LEFT JOIN
    conft_postmeta m_svc_costo ON v.ID = m_svc_costo.post_id AND m_svc_costo.meta_key = 'svc_costo'
LEFT JOIN
    conft_postmeta m_svc_ubicacion ON v.ID = m_svc_ubicacion.post_id AND m_svc_ubicacion.meta_key = 'svc_ubicacion-bodega'
LEFT JOIN
    conft_postmeta m_cantidad_inicial ON v.ID = m_cantidad_inicial.post_id AND m_cantidad_inicial.meta_key = 'cantidad-inicial'
LEFT JOIN
    conft_postmeta m_sku ON v.ID = m_sku.post_id AND m_sku.meta_key = '_sku'
LEFT JOIN
    conft_postmeta m_price ON v.ID = m_price.post_id AND m_price.meta_key = '_price'
LEFT JOIN
    conft_postmeta m_sale_price ON v.ID = m_sale_price.post_id AND m_sale_price.meta_key = '_sale_price'
LEFT JOIN
    conft_postmeta m_stock_status ON v.ID = m_stock_status.post_id AND m_stock_status.meta_key = '_stock_status'
LEFT JOIN
    conft_postmeta m_stock ON v.ID = m_stock.post_id AND m_stock.meta_key = '_stock'
WHERE
    v.post_type = 'product_variation'
LIMIT
    100;


Se puede modificar el WHERE para filtrar asi:
WHERE
    v.post_type = 'product_variation'
    AND v.post_parent = 1490

---------------

ME MUESTRA LAS UNIDADES DE UNA VARIACION QUE SE ENCUENTRAN EN LOS PEDIDOS DE UN ESTADO EN ESPECIFICO.
ESTO ES BUENO PORQUE PUEDO SABER CUANTAS UNIDADES POR VARIACION SE ENCUENTRAN EN DIFERENTES PEDIDOS.
SELECT 
    posts.ID AS order_number,
    variation_meta.order_item_id,
    variation_meta.meta_value AS variation_id,
    qty_meta.meta_value AS quantity,
    posts.post_status AS order_status
FROM conft_woocommerce_order_itemmeta AS variation_meta
JOIN conft_woocommerce_order_itemmeta AS qty_meta ON variation_meta.order_item_id = qty_meta.order_item_id
JOIN conft_woocommerce_order_items AS items ON variation_meta.order_item_id = items.order_item_id
JOIN conft_posts AS posts ON items.order_id = posts.ID
WHERE variation_meta.meta_key = '_variation_id' AND variation_meta.meta_value = '111125'
AND qty_meta.meta_key = '_qty'
AND posts.post_status = 'wc-processing';

SI LE QUITO ESTA LINEA: AND posts.post_status = 'wc-processing';
ENTONCES ME TRAE TODOS LOS ESTADOS.




-------
Y ESTA MUESTRA EL DATO DE LA VARIACION PERO INCLUYE VARIOS ESTADOS DE PEDIDOS:
SELECT 
    posts.ID AS order_number,
    variation_meta.order_item_id,
    variation_meta.meta_value AS variation_id,
    qty_meta.meta_value AS quantity,
    posts.post_status AS order_status
FROM conft_woocommerce_order_itemmeta AS variation_meta
JOIN conft_woocommerce_order_itemmeta AS qty_meta ON variation_meta.order_item_id = qty_meta.order_item_id
JOIN conft_woocommerce_order_items AS items ON variation_meta.order_item_id = items.order_item_id
JOIN conft_posts AS posts ON items.order_id = posts.ID
WHERE variation_meta.meta_key = '_variation_id' AND variation_meta.meta_value = '111125'
AND qty_meta.meta_key = '_qty'
AND (posts.post_status = 'wc-on-hold' OR posts.post_status = 'wc-processing' OR posts.post_status = 'wc-pending-cod' );


-----
Y ESTA ES MAS SENCILLA, PORQUE TRAE LA VARIACION PERO INCUYE TODOS LOS ESTADO DE PEDIDOS.
ES COMO PARA SABER EN CUANTOS PEDIDOS DIFERENTES SE ENCUENTRA PRESENTE.
PODRIA DESPUES EXCLUIR ALGUN ESTADO AUNQUE SE VOLVERIA LENTA LA CONSULTA.

SELECT
    itemmeta.order_item_id,
    MAX(CASE WHEN itemmeta.meta_key = '_variation_id' THEN itemmeta.meta_value END) AS variation_id,
    MAX(CASE WHEN itemmeta.meta_key = '_qty' THEN itemmeta.meta_value END) AS quantity
FROM conft_woocommerce_order_itemmeta AS itemmeta
JOIN conft_woocommerce_order_items AS items ON itemmeta.order_item_id = items.order_item_id
GROUP BY itemmeta.order_item_id
HAVING variation_id = '111125';



------------
CONSULTA LOS DATOS DE LOS PEDIDOS EN UNA FECHA especificado
SELECT
    posts.ID AS order_id,
    posts.post_date AS order_date,
    posts.post_status AS order_status,
    pm_billing_email.meta_value AS customer_email,
    CONCAT(pm_billing_first_name.meta_value, ' ', pm_billing_last_name.meta_value) AS customer_name,
    pm_order_total.meta_value AS order_total,
    pm_payment_method.meta_value AS payment_method,
    pm_dateInvoiced.meta_value AS svc_dateInvoiced,
    pm_invoiceNumber.meta_value AS svc_invoiceNumber,
    pm_dateShipped.meta_value AS svc_dateShipped,
    pm_trackingId.meta_value AS svc_trackingId,
    pm_shippingProvider.meta_value AS svc_shippingProvider
FROM
    conft_posts AS posts
LEFT JOIN
    conft_postmeta AS pm_billing_email ON posts.ID = pm_billing_email.post_id AND pm_billing_email.meta_key = '_billing_email'
LEFT JOIN
    conft_postmeta AS pm_billing_first_name ON posts.ID = pm_billing_first_name.post_id AND pm_billing_first_name.meta_key = '_billing_first_name'
LEFT JOIN
    conft_postmeta AS pm_billing_last_name ON posts.ID = pm_billing_last_name.post_id AND pm_billing_last_name.meta_key = '_billing_last_name'
LEFT JOIN
    conft_postmeta AS pm_order_total ON posts.ID = pm_order_total.post_id AND pm_order_total.meta_key = '_order_total'
LEFT JOIN
    conft_postmeta AS pm_payment_method ON posts.ID = pm_payment_method.post_id AND pm_payment_method.meta_key = '_payment_method_title'
LEFT JOIN
    conft_postmeta AS pm_dateInvoiced ON posts.ID = pm_dateInvoiced.post_id AND pm_dateInvoiced.meta_key = 'svc_dateInvoiced'
LEFT JOIN
    conft_postmeta AS pm_invoiceNumber ON posts.ID = pm_invoiceNumber.post_id AND pm_invoiceNumber.meta_key = 'svc_invoiceNumber'
LEFT JOIN
    conft_postmeta AS pm_dateShipped ON posts.ID = pm_dateShipped.post_id AND pm_dateShipped.meta_key = 'svc_dateShipped'
LEFT JOIN
    conft_postmeta AS pm_trackingId ON posts.ID = pm_trackingId.post_id AND pm_trackingId.meta_key = 'svc_trackingId'
LEFT JOIN
    conft_postmeta AS pm_shippingProvider ON posts.ID = pm_shippingProvider.post_id AND pm_shippingProvider.meta_key = 'svc_shippingProvider'
WHERE
    posts.post_type = 'shop_order'
    AND posts.post_status = 'wc-completed'
    AND posts.post_date >= '2023-12-01 00:00:00'
    AND posts.post_date <= '2023-12-31 23:59:59'
ORDER BY
    posts.ID DESC;






    ---------
VENTAS POR PRODUCTO EN TOTAL 
    SELECT 
    p.ID AS numero_pedido,
    p.post_status AS estado_pedido,
    oi.order_item_id AS item_id,
    oi.order_item_name AS nombre_item,
    COALESCE(MAX(CASE WHEN oim.meta_key = '_qty' THEN oim.meta_value END), 0) AS cantidad,
    MAX(CASE WHEN oim.meta_key = '_line_total' THEN oim.meta_value END) AS precio_total,
    MAX(CASE WHEN oim.meta_key = '_variation_id' THEN oim.meta_value END) AS id_variacion,
    MAX(CASE WHEN oim2.meta_key = '_product_id' THEN oim2.meta_value END) AS id_producto_padre
FROM 
    conft_posts AS p
JOIN 
    conft_woocommerce_order_items AS oi ON p.ID = oi.order_id
JOIN 
    conft_woocommerce_order_itemmeta AS oim ON oi.order_item_id = oim.order_item_id
LEFT JOIN 
    conft_woocommerce_order_itemmeta AS oim2 ON oim2.order_item_id = oi.order_item_id AND oim2.meta_key = '_product_id'
WHERE 
    p.post_type = 'shop_order'
    AND p.post_date >= '2023-12-01' 
    AND p.post_date <= '2023-12-31'
    AND (oim.meta_key IN ('_qty', '_line_total', '_variation_id') OR oim2.meta_key = '_product_id')
GROUP BY 
    p.ID,
    p.post_status,
    oi.order_item_id,
    oi.order_item_name
LIMIT 100;



-----------------------------------------------------
CONSULTAS PARA ACTUALIZAR PRECIOS MASIVAMENTE:

>>>>>>>>>>>   PASO 1: SE SELECCIONAN LOS PRODUCTOS SEGUN CRITERIO. EN ESTE CASO, POR UNA ETIQUETA.
'promo_pedido_minimo_enero2024_10off'

SELECT 
    p.ID AS id
FROM 
    conft_posts AS p
JOIN 
    conft_term_relationships AS tr ON p.ID = tr.object_id
JOIN 
    conft_term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
JOIN 
    conft_terms AS t ON tt.term_id = t.term_id
WHERE 
    p.post_type = 'product' 
    AND p.post_parent = 0 -- Solo productos padres
    AND t.name = 'promo_pedido_minimo_enero2024_10off' 
    AND tt.taxonomy = 'product_tag'; -- Asegurarse de que es una etiqueta

 >>>>>>>>>>   PASO 2: Se obtienes las variantes ID:

    SELECT 
    v.ID AS id,
    v.post_title AS nombre_de_la_variacion,
    MAX(CASE WHEN pm.meta_key = '_regular_price' THEN pm.meta_value END) AS precio_regular,
    MAX(CASE WHEN pm.meta_key = '_sale_price' THEN pm.meta_value END) AS precio_oferta
FROM 
    conft_posts AS p
JOIN 
    conft_posts AS v ON p.ID = v.post_parent
LEFT JOIN 
    conft_postmeta AS pm ON v.ID = pm.post_id
WHERE 
    p.ID IN (103247, 103248)
    AND v.post_type = 'product_variation'
GROUP BY 
    v.ID,
    v.post_title;

    >>>>>>>>> PASO 3: SE ACTUALIZA EL sale_priceUPDATE conft_postmeta AS pm_sale
JOIN conft_postmeta AS pm_regular ON pm_sale.post_id = pm_regular.post_id
SET pm_sale.meta_value = pm_regular.meta_value * 0.90  -- Descuento del 10%
WHERE pm_sale.meta_key = '_sale_price'
AND pm_regular.meta_key = '_regular_price'
AND pm_regular.post_id IN (103255, 103256, 103257);


------------------------
CONSULTA PEDIDOS PARA REVISAR VENTAS 

SELECT 
    p.ID AS order_id,
    p.post_date AS order_date,
    p.post_status AS order_status,
    MAX(CASE WHEN pm.meta_key = '_billing_email' THEN pm.meta_value END) AS customer_email,
    CONCAT(MAX(CASE WHEN pm.meta_key = '_billing_first_name' THEN pm.meta_value END), ' ', MAX(CASE WHEN pm.meta_key = '_billing_last_name' THEN pm.meta_value END)) AS customer_name,
    MAX(CASE WHEN pm.meta_key = '_order_total' THEN pm.meta_value END) AS order_total,
    MAX(CASE WHEN pm.meta_key = '_payment_method_title' THEN pm.meta_value END) AS payment_method,
    MAX(CASE WHEN pm.meta_key = 'svc_dateInvoiced' THEN pm.meta_value END) AS svc_dateInvoiced,
    MAX(CASE WHEN pm.meta_key = 'svc_invoiceNumber' THEN pm.meta_value END) AS svc_invoiceNumber,
    MAX(CASE WHEN pm.meta_key = 'svc_dateShipped' THEN pm.meta_value END) AS svc_dateShipped,
    MAX(CASE WHEN pm.meta_key = 'svc_trackingId' THEN pm.meta_value END) AS svc_trackingId,
    MAX(CASE WHEN pm.meta_key = 'svc_shippingProvider' THEN pm.meta_value END) AS svc_shippingProvider
FROM 
    conft_posts AS p
LEFT JOIN 
    conft_postmeta AS pm ON p.ID = pm.post_id
WHERE 
    p.post_type = 'shop_order'
    AND p.post_status = 'wc-completed'
    AND p.post_date >= '2024-01-01'
    AND p.post_date < '2024-02-01'
GROUP BY 
    p.ID;