(SOLUCIONADO) Consulta sql de pedidos y costo de productos en pedido

[Guadalquivir Producciones]

Hola buenas tardes, llevo varios días dando vueltas y realizando consultas que no dan fruto en SQL. 

Necesito una consulta donde pueda obtener el id del pedido, nombre del cliente, producto que compro, cantidad, precio de venta y precio de costo de ese producto, cada campo en una columna para poder tratar los datos. Pero no consigo conjugar bien los campos y las tablas en la consulta. Solo recibo errores.

Por favor agradezco cualquier ayuda u orientación de como puedo obtener esto.

No estoy muy puesto en SQL y al tener tantas tablas en prestashop estoy echo un lío.

 

Gracias

Un saludo

Edited July 8, 2015 by Guadalquivir Producciones (see edit history)

[ventura]

Tienes un estupendo modulo desarrollado por enrique engblom que te genera los datos que necesitas

https://www.prestashop.com/forums/topic/358171-modulo-exportar-pedidos-en-csv-ps16/

[Guadalquivir Producciones]

Hola en primer lugar gracias por la respuesta, he probado el modulo y esta genial, pero no me da parte de los datos que necesito. Necesitaría tener forma de pago, y el costo del producto. En el modulo me da el precio de venta. Si pudiese de alguna forma incorporar esos datos el modulo me vendría de perlas.

Quizás hay alguna configuración del modulo que no he puesto bien?

 

Gracias de nuevo.

[Guadalquivir Producciones]

Hola, alguien puede sugerir como conseguir esto. El modulo que amablemente me recomendó ventura es estupendo pero le faltan datos que necesito.

 

Imagino que con SQL y la consulta adecuada se puede conseguir los datos. El problema es que no me muevo muy bien en SQL y en prestashop son muchas tablas desde donde hacer la consulta.

 

Pues eso a ver si pueden orientarme como construir la consulta.

 

Gracias de nuevo Ventura.

[ventura]

Para una consulta normal puedes usar una query de este tipo y completarla con los campos que necesites, rangos de fechas e id del idioma que necesites, 

SELECT d.id_order, os.name AS state, d.product_name, d.product_reference, d.product_price, d.product_quantity, o.payment, o.date_upd, CONCAT_WS(  ' ', g.firstname, g.lastname ) AS customer, gl.name AS group_name
FROM ps_order_detail d
LEFT JOIN ps_orders o ON ( d.id_order = o.id_order ) 
LEFT JOIN ps_customer g ON ( o.id_customer = g.id_customer ) 
LEFT JOIN ps_group_lang gl ON ( g.id_default_group = gl.id_group ) 
LEFT JOIN ps_order_state_lang os ON ( o.current_state = os.id_order_state ) 
WHERE os.id_lang =1
LIMIT 0 , 30

En el foro encontraras bastantes hilos sobre este tema

[Guadalquivir Producciones]

Muchas gracias por el codigo es practicamente la base de lo que necesito. Una ultima pregunta si no te importa, sabes como añadir el costo del producto, me refiero. Es algo como esto.

 

SELECT d.id_order, os.name AS state, d.product_name, d.product_reference, d.product_price, d.product_quantity, o.payment, o.date_upd, CONCAT_WS(  ' ', g.firstname, g.lastname ) AS customer, gl.name AS group_name

FROM ps_order_detail d

LEFT JOIN ps_orders o ON ( d.id_order = o.id_order ) WHERE o.date_upd

BETWEEN  '2015-03-01'

AND  '2015-05-31'

LEFT JOIN ps_stock k ON (k. price_te=k.price_te)

LEFT JOIN ps_customer g ON ( o.id_customer = g.id_customer ) 

LEFT JOIN ps_group_lang gl ON ( g.id_default_group = gl.id_group ) 

LEFT JOIN ps_order_state_lang os ON ( o.current_state = os.id_order_state ) 

WHERE os.id_lang =1 AND 

LIMIT 0 , 30

 

esta consulta no consigo que funcione

Me dice que 

1. Error "checkedWhere" no definido

Y si quito el WHERE no me da ningún resultado.

Es que no asigno bien la tabla y el concepto. He puesto LEFT JOIN ps_stock k ON (k. price_te=k.price_te) para conseguir el precio de costo del producto.

 

No consigo nada. 

Por favor puedes echar un ojo al código y corregir o decirme donde cometo el error.

 

Muchas gracias

[Guadalquivir Producciones]

Ya he conseguido el costo del producto 

SELECT d.id_order, os.name AS state, d.product_name, d.product_reference, d.product_price,  k.price_te, d.product_quantity, o.payment, o.date_upd, CONCAT_WS(  ' ', g.firstname, g.lastname ) AS customer, gl.name AS group_name

FROM ps_order_detail d

LEFT JOIN ps_orders o ON ( d.id_order = o.id_order ) 

LEFT JOIN ps_stock k ON (k.price_te = k.price_te)

LEFT JOIN ps_customer g ON ( o.id_customer = g.id_customer ) 

LEFT JOIN ps_group_lang gl ON ( g.id_default_group = gl.id_group ) 

LEFT JOIN ps_order_state_lang os ON ( o.current_state = os.id_order_state ) 

WHERE os.id_lang =1 AND 

LIMIT 0 , 30

 

Pero no consigio que lo limite en el tiempo. Entre fechas.

Si le quito el limite de abajo da error.

Y ponga donde ponga el 

WHERE o.date_upd

BETWEEN  '2015-03-01'

AND  '2015-05-31'

Me da error.

 

Sabes que hago mal.

 

Gracias

[Guadalquivir Producciones]

Finalmente y tras varias pruebas he modificado una consulta de pedidos. Obtengo lo que quiero a falta de los precios de costo o precio de mayorista como lo denomina prestashop.

 

Te dejo la consulta por si tú o alguien encuentra donde esta el error ya que no funciona si le pongo las tablas de ps_stock

SELECT SQL_CALC_FOUND_ROWS 			a.`id_order`,`reference`,`total_paid_tax_excl` AS `PV SIN IVA`, k.`price_te`,`payment`,a.date_add as FECHA , a.id_currency, 				
CONCAT(LEFT(c.`firstname`, 1), '. ', c.`lastname`) AS `customer`, 		osl.`name` AS `ESTADO`			
FROM `ps_orders` a 			 			 		
LEFT JOIN `ps_customer` c ON (c.`id_customer` = a.`id_customer`)
INNER JOIN `ps_address` address ON address.id_address = a.id_address_delivery 		
INNER JOIN `ps_country` country ON address.id_country = country.id_country 		
INNER JOIN `ps_country_lang` country_lang ON (country.`id_country` = country_lang.`id_country` AND country_lang.`id_lang` = 1) 
LEFT JOIN `ps_stock` k ON (k.`price_te`= `k.price_te`)		
LEFT JOIN `ps_order_state` os ON (os.`id_order_state` = a.`current_state`) 		
LEFT JOIN `ps_order_state_lang` osl ON (os.`id_order_state` = osl.`id_order_state` AND osl.`id_lang` = 1)  			 			
WHERE 1  AND a.`date_add` >= '2015-05-01 0:0:0' AND a.`date_add` <= '2015-05-16 23:59:59' 			 			 			
ORDER BY a.`id_order` DESC

[Guadalquivir Producciones]

Quizás elijo la tabla errónea, dentro de las tablas de pedidos estará el precio mayorista de los productos dentro de ese pedido?

[ventura]

Para que te incluya el precio de mayorista. el precio del proveedor, añade

d.purchase_supplier_price

en la consulta que te he puesto antes

[Guadalquivir Producciones]

Muchas gracias. Justo eso era. De verdad te lo agrdezco, con tu ayuda he construido la siguiente consulta, pero por algun motivo me repite varias veces los pedidos. Del mismo id me repite hasta tres veces. Es logico que cuando hay varios productos ponga varias lineas con el id en cada uno de los productos, pero los triplica. Te pongo tu consulta con las modificaciones que le he añadido. Primero para que si alguien la necesita ya la tenga más o menos estructurada y dos para que si puedes me indiques por que repite los pedidos.

SELECT  d.id_order, os.name AS ESTADO, d.product_name AS NOMBRE, d.product_reference AS REFERENCIA, d.product_price AS PRECIO , d.purchase_supplier_price AS COSTO, d.product_quantity AS CANTIDAD, o.payment AS PAGADO, o.date_upd AS FECHA, CONCAT_WS(  ' ', g.firstname, g.lastname ) AS CLIENTE, gl.name AS GRUPO
FROM ps_order_detail d
LEFT JOIN ps_orders o ON ( d.id_order = o.id_order ) 
LEFT JOIN ps_customer g ON ( o.id_customer = g.id_customer ) 
LEFT JOIN ps_group_lang gl ON ( g.id_default_group = gl.id_group ) 
LEFT JOIN ps_order_state_lang os ON ( o.current_state = os.id_order_state ) 
WHERE os.id_lang =1 and o.date_upd between '2015-05-15 00:00:00.000' and '2015-05-16 00:00:00.000'

Muchas gracias de nuevo por tu ayuda.

 

Imagino que para no duplicar sera alguna función de agrupar o algo asi no?

Edited June 19, 2015 by Guadalquivir Producciones (see edit history)

[Guadalquivir Producciones]

Finalmente la he dejado así, que es prácticamente es lo que necesito. Lo unico que me haria falta es poder sumar los costos, ya que el costo que me da es de uno solo de los productos que aparecen en el id del pedido y hay ids con más de un producto distinto. Lo ideal sería poder sumar d.purchase_supplier_price y que en la consulta mostrara el costo toal del id del pedido. asi como he puesto total_paid_tax_excl para saber el total que pago el cliente en ese pedido.

SELECT  d.id_order, os.name AS ESTADO, o.total_paid_tax_excl AS VENTA , d.purchase_supplier_price AS COSTO, d.product_quantity AS CANTIDAD, o.payment AS PAGADO, o.date_upd AS FECHA, CONCAT_WS(  ' ', g.firstname, g.lastname ) AS CLIENTE, gl.name AS GRUPO
FROM ps_order_detail d
LEFT JOIN ps_orders o ON ( d.id_order = o.id_order ) 
LEFT JOIN ps_customer g ON ( o.id_customer = g.id_customer ) 
LEFT JOIN ps_group_lang gl ON ( g.id_default_group = gl.id_group ) 
LEFT JOIN ps_order_state_lang os ON ( o.current_state = os.id_order_state ) 
WHERE os.id_lang =1 and o.date_upd between '2015-05-15 00:00:00.000' and '2015-05-16 00:00:00.000'
GROUP BY d.product_reference

Vuelvo a subir la consulta por si ha alguien le sirve o inspira. Aunque esta sin terminar, al menos para lo que yo necesito.

 

Gracias 

[Guadalquivir Producciones]

No abandono, jajaja. Sigo con ello y trato de dar con la forma de hacer la suma del costo de los productos incluidos en un pedido. Creo que no ando mal encaminado pero, podría alguien que sepa de SQL indicarme como se puede realizar esta suma de dichos importes?

 

Imagino que necesitare un condicional para que sume los importes de los id de pedidos que coincidan y después esta suma es la que debe mostrar en la consulta junto al resto de campos.

 

De nuevo gracias de ante mano.

[FERMB]

Para una consulta normal puedes usar una query de este tipo y completarla con los campos que necesites, rangos de fechas e id del idioma que necesites, 

SELECT d.id_order, os.name AS state, d.product_name, d.product_reference, d.product_price, d.product_quantity, o.payment, o.date_upd, CONCAT_WS(  ' ', g.firstname, g.lastname ) AS customer, gl.name AS group_name
FROM ps_order_detail d
LEFT JOIN ps_orders o ON ( d.id_order = o.id_order ) 
LEFT JOIN ps_customer g ON ( o.id_customer = g.id_customer ) 
LEFT JOIN ps_group_lang gl ON ( g.id_default_group = gl.id_group ) 
LEFT JOIN ps_order_state_lang os ON ( o.current_state = os.id_order_state ) 
WHERE os.id_lang =1
LIMIT 0 , 30

En el foro encontraras bastantes hilos sobre este tema

 

Bien Ventura.

gracias por el código, pero no sé porqué me repetia varias veces el listado de productos de un unico pedido, pero a partir de él, pude hacer este codígo con lo que necesitaba, así que gracias por el aporte.

Con este código, con encabezados en español, muestra todos los artículos de cada pedido, unidades compradas, stock restante, fecha, nombre de cliente, direcciones de envio y facturación, mail de cliente, etc... y ordenando los pedidos para ver los últimos en primer lugar.

SELECT d.id_order, os.name AS estado, o.date_upd AS fecha, d.product_name AS producto, d.product_reference AS ref_ReleMat, d.product_supplier_reference AS ref_proveedor, d.product_quantity AS uds,  d.product_price, s.quantity AS en_stock, o.payment,  CONCAT_WS(  ' ', g.firstname, g.lastname ) AS cliente,  CONCAT_WS(' ', ad.address1, ad.address2, ad.postcode, ad.city, ad.other, ad.phone, ad.phone_mobile) AS envio, CONCAT_WS(' ', ai.address1, ai.address2,  ai.postcode, ai.city, ai.other, ai.phone, ai.phone_mobile) AS facturacion, g.email, gl.name AS grupo
FROM ps_order_detail d
LEFT JOIN ps_orders o ON ( d.id_order = o.id_order ) 
LEFT JOIN ps_customer g ON ( o.id_customer = g.id_customer ) 
LEFT JOIN ps_stock_available s ON (d.product_id = s.id_product)
LEFT JOIN ps_address ad ON (o.id_address_delivery = ad.id_address)
LEFT JOIN ps_address ai ON (o.id_address_invoice = ai.id_address)
LEFT JOIN ps_group_lang gl ON ( g.id_default_group = gl.id_group ) 
LEFT JOIN ps_order_state_lang os ON ( o.current_state = os.id_order_state ) 
WHERE os.id_lang =1
GROUP BY d.id_order, d.product_name
ORDER BY d.id_order DESC

Espero que alguien más le de utilidad, para mejorar/completar la exportación de datos de los pedidos que recuerdo a todos se puede hacer también desde el listado de pedidos del backoffice, con el botón superior derecha que exporta a excel (aunque sólo los datos que muestra el listado de pedidos)

Edited June 30, 2015 by FERMB (see edit history)

[Guadalquivir Producciones]

Finalmente realice dos consultas, una para las ventas y otra para conseguir los costos de esas ventas. Dejo aquí ambas por si a alguien les sirve tal cual o les inspira para conseguir otra.

Y por supuesto agradecer la ayuda de Ventura que me oriento de forma adecuada.

SELECT o.`id_order` AS `id PEDIDO`, o.`invoice_number` AS `Nº FRA`, os.`name` AS `ESTADO`,
    CONCAT(LEFT(c.`firstname`, 1), '. ', c.`lastname`) AS `CLIENTE`,
    ca.`name` AS `TRANSPORTISTA`, 
    o.`payment` AS `FORMA PAGO`,
    o.`total_paid_tax_excl` AS `PV. TOTAL`,
    o.`date_add` AS `FECHA`
FROM `ps_orders` o
LEFT JOIN `ps_customer` c ON (o.`id_customer` = c.`id_customer`)
LEFT JOIN `ps_carrier` ca ON (o.id_carrier = ca.id_carrier)
LEFT JOIN `ps_currency` cu ON (o.`id_currency` = cu.`id_currency`)
LEFT JOIN ps_order_state_lang os ON ( o.current_state = os.id_order_state ) 
WHERE  o.`date_add` >= '2015-07-01 0:0:0' AND o.`date_add` <= '2015-07-06 23:59:59'  and os.id_lang =1

COSTOS

SELECT o.`id_order` AS `id`, o.`invoice_number` AS `Nº FRA`, os.`name` AS `ESTADO`,
    CONCAT(LEFT(c.`firstname`, 1), '. ', c.`lastname`) AS `CLIENTE`,
     o.`payment` AS `FORMA PAGO`,
   od.`product_quantity`AS `CANTIDAD`, 
   od.`purchase_supplier_price` AS `COSTO.UND`,
   od.`product_name` AS `PRODUCTO`,
    o.`date_add` AS `FECHA`
FROM `ps_orders` o
LEFT JOIN `ps_order_detail` od ON (od.id_order = o.id_order)
LEFT JOIN `ps_order_carrier` oc ON (oc.id_order = o.id_order)
LEFT JOIN `ps_customer` c ON (o.`id_customer` = c.`id_customer`)
LEFT JOIN `ps_carrier` ca ON (o.id_carrier = ca.id_carrier)
LEFT JOIN `ps_currency` cu ON (o.`id_currency` = cu.`id_currency`)
LEFT JOIN ps_order_state_lang os ON ( o.current_state = os.id_order_state ) 
WHERE o.`date_add` >= '2015-07-01 0:0:0' AND o.`date_add` <= '2015-07-06 23:59:59' and os.id_lang =1

Doy por solucionado el tema.

Gracias a todos.

[bazaralandalus]

Muchisimas gracias, era lo que estaba necesitando.

No entiendo ni papa de SQL y necesitaba sacar esta consulta. Solo me gustaría añadirle el teléfono del cliente. ¿puede ser?

La que me  funciona es la de FERMB. Muchas gracias de verdad

Si podeis ayudarme con lo del teléfono sería genial, para la empresa de transporte.

 

¿Hay algún sitio donde estén recopiladas las consultas SQL, como pasa con los modulos?

[Arturado]

Una pregunta, como hago yo para imprimir el precio de un producto de mi prestashop en una Tabla hecha en un CMS ? 

 

Según el ID del producto 

[ventura]

Una pregunta, como hago yo para imprimir el precio de un producto de mi prestashop en una Tabla hecha en un CMS ? 

 

Según el ID del producto 

 Por favor abra un hilo con su duda ya que la que plantea en este no tiene mucho que ver con el original.

Gracias.

 

Un saludo.

[chenler19]

Buenas, 

 

Os pongo una consulta donde también aparece el precio de venta y el coste, creo que es similar a lo que preguntaís en este post, y aparecen estos parámetros: 

Cliente Producto Cantidad Precio_Venta_Producto Precio_Coste_Producto Descuento Total_pagado Forma_de_pago Fecha_compra id_category

 

SELECT CONCAT_WS(  ' ', g.firstname, g.lastname ) AS Cliente, n.product_name AS Producto, n.product_quantity AS Cantidad,n.total_price_tax_incl AS Precio_Venta_Producto, n.original_wholesale_price AS Precio_Coste_Producto, o.total_discounts AS Descuento , o.total_paid_tax_incl AS Total_pagado,  o.payment AS Forma_de_pago, o.invoice_date AS Fecha_compra, h.id_category

FROM  ps_orders o

LEFT JOIN  ps_customer g ON g.id_customer = o.id_customer 

LEFT JOIN  ps_order_detail n ON n.id_order = o.id_order

LEFT JOIN ps_category_product h ON h.id_product = n.product_id

WHERE o.date_upd

BETWEEN  '2014-01-01' AND  '2017-12-31'

AND h.id_category = 31  (esta línea es opcional, Yo lo tengo filtrado para que solo me salgan los productos de la categoria con ID = 31, que es la que me interesa, si quitáis esto saldrían todos los productos)

ORDER BY o.date_upd DESC

 

Lo tengo también ordenador por fecha. Es para prestashop 1.6, en el resto no se como está la B.D.

 

Un saludo. 

Edited December 18, 2016 by chenler19 (see edit history)

[burbujita]

Guauuu esto da mucho juego!

yo no tengo mucha idea de SQL, pero a esta última consulta, me podéis decir como poner el campo email del cliente?