Untitled
unknown
plain_text
a year ago
18 kB
4
Indexable
<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;
Editor is loading...
Leave a Comment