Vista de compras y articulos

[dhamaso]

Hola alguien me puede decir si es posible hacer una vista con las siguientes tablas


Tablas con registros de prueba :
Compras


Compras articulos


Compras pagos


Para que me quede de esta manera:
--------------------------------------------------------------------------
id_compra | monto_total | monto_pagado | monto_restante
--------------------------------------------------------------------------

Lo que intento hacer es relacionar la tabla de compras con la tabla de compras_articulos para obtener el monto total de la compra, ya que el monto total se obtiene, sumando los montos de los articulos por la cantidad de cada
uno de estos.

Tambien quiero relacionar la tabla de compras con la de compras_pagos para obtener la suma de pagos hechos a cada compra y cuanto es el monto restante.


Lo intente hacer asi:

Código SQL:

CREATE VIEW vta_compras AS SELECT 
`compras`.`id_compra`, 
SUM(`compras_articulos`.`precio_unitario` * `compras_articulos`.`cantidad`) AS `monto_total`,
SUM(`compras_pagos`.`monto`) AS `monto_pagado`,
(SUM(`compras_articulos`.`precio_unitario` * `compras_articulos`.`cantidad`) - SUM(`compras_pagos`.`monto`)) AS `monto_pendiente`
FROM `compras`
LEFT JOIN `compras_articulos` ON (`compras`.`id_compra` = `compras_articulos`.`id_compra`)
LEFT JOIN `compras_pagos` ON (`compras`.`id_compra` = `compras_pagos`.`id_compra`)
GROUP BY `compras`.`id_compra`
 

Pero al ejecutarlo me suma los montos.


Cuando deberia de quedar asi



¿Alguna idea, sugerencia, comentario sobre que podria hacer?

[Gedeon]

En mi opinión tienes un planteamiento de tables equivocado. Quizá no interpreté bien, pero si es lo que pienso y las tablas las has pensado mal ya partes de errores gordos.

En principio tu quieres obtener información de ventas de articulos y proveedores. Eso muestra tres tablas de tres conceptos distintos que tienes que organizar. Proveedores, articulos y facturas.

En cada factura debe figurar el artículo y cada artículo corresponde a un proveedor.

Así que la primera relación que me sale es una relación de uno a muchos proveedor-articulo y una relación facturas-articulo de uno a muchos.

Por tanto en proveedores necesitas los datos que describan al proveedor y una clave única primaria a la que llamo id. Para la relación con articulos en cada articulo tendrás los datos que describen al articulo y entre ellos una clave que identifica al proveedor que le corresponde yl e llamo id_proveedor.

En las facturas tienes un problema. hay dos tipos de datos que forman parte de una factura. Unos son datos globales de la factura en si misma y otros son datos concretos de la relación de articulos que se ha vendido. Hay varias maneras de hacer esto pero quizá la mas clara sea una tabla de facturas donde almacenas fecha, numero de factura, importe e impuestos y otra tabla detalle-factura donde almacenas la relación de articulos con sus precios correspondientes. La relación es de nuevo uno a muchos entre facturas y facturas-detalle. La otra forma es mas sencilla de entender pero mas deficil de explicar aqui. Consiste en tener además de una clave una subclave y tener dos tipos de registro, unos para el detalle y otro (solo uno) para los datos generales de la factura.

Osea que empieza por diseñar las tablas de proveedores con su clave primaria, la de articulos con la suya y luego piensa como hacer las facturas. Bien con una tabla y dos tipos de registro o bien con dos tablas una general y otra detalle. La tabla de facturas tendrás que relacionarla con articulos y los articulos con proveedores.

[dhamaso]

Creo que mal interpretaste mi planteamiento, lo que quiero obtener es :
1.- El monto total de cada una de las compras y esto se haría a través del precio * cantidad de cada uno de los artículos de la compra; osea la relación es: una compra tiene muchos artículos (una relación de uno a muchos) y de los artículos es de donde obtengo el monto total de la compra.

2.- El monto total de los pagos realizados a cada una de las compras, (son pagos parciales) y esto se haría sumando todos los montos pagados a una compra, osea cada compra puede tener muchos pagos (una relación de uno a muchos).

3.- Obtener la diferencia o monto restante por pagar de cada una de las compras, y para esto tendría que obtener el monto total de la compra - la sumatoria de los pagos hechos a la compra.

4.- Lo de las facturas no importan mucho aqui, ya que son datos opcionales y solo se apuntaría el numero de la factura.

5.- Lo de los proveedores, aquí solo estoy guardando el id del proveedor, los datos como el nombre del proveedor, rfc etc, están en un catalogo de proveedores, pero al igual que la factura este campo no me interesa.

[Gedeon]

vale, ya veo que por donde yo iba no estaba bien, pero aún así tengo algunas cosas que objetar.

El punto 1 dice esto

1.- El monto total de cada una de las compras y esto se haría a través del precio * cantidad de cada uno de los artículos de la compra; osea la relación es: una compra tiene muchos artículos (una relación de uno a muchos) y de los artículos es de donde obtengo el monto total de la comp

El monto total de la compra efectivamente es la suma del monto de compra de cada uno de los artículos, pero el error es que esa cantidad es un dato de la factura. Comprendo que no te interesa una gestión de facturas propiamente dicha, pero la tabla donde almacenas eso es una tabla de facturas en la que además de campos que no te interesan como cliente, fecha y número de factura debería estar el monto total de la factura y la forma de pago.

Y si consideramos que es primera tabla es de facturas, la segunda es el detalle de facturas y aquí lo que tienes es una relación de los artículos que están incluidos en esa factura con sus precios correspondientes y cualquier otro dato que necesites.

Si además quieres que se hagan varios pagos por factura (por ejemplo mensualmente) entonces necesitas también una tabla de pagos. Mas o menos sería esto

tabla facturas
id (key principal), ImporteTotal, FormaDePago,ImportePagado, ImportePendiente... [etc]
donde etc son campos que en un caso real deberían estar como numero de factura, fecha, id_cliente, etc pero que ahora no te interesan.

Tabla detalle
id (key principal), idArticulo,cantidad,precio,Importe,IdFactura
El idfactura sería la FK del id de facturas y lo utilizarías para relacionar facturas con detalle uno a muchos. Importe es cantidad * precio

tabla pagos
id (key principal), fecha, importe,IdFactura
El IdFActura sería la FK del id de facturas y lo utilizarías para relacionar facturas con pagos uno a muchos.

De esta forma en facturas, Importe es la suma de los (muchos) importes de detalle y pagado es la suma de los (muchos) importes de pagos. Lo que le queda por pagar sería ImporteTotal-ImportePagado.

Creo que esto se aproxima mas a lo que buscas.

La forma de calcular el ImporteTotal, lo pagado y lo pendiente sería

Código:

update facturas F 
   set Importetotal=(   select (sum(Importe) as I from Detalle 
                                where IdFactura=F.id),
   set ImportePagado=(   select (sum(Importe) as J from Pagos 
                                where IdFactura=F.id),
   set ImportePendiente=ImporteTotal-ImportePagado
   where factura='xxx'

donde xxx sería la factura que quieres calcular su importe y lo pagado.

Para sacar la factura con sus importes y articulos
[CODE]select F.x,D.* from factura F
inner join Detalle D on D.IdFactura=F.Id
where Factura='xxx'{/CODE]

Y para sacar los pagos de la factura sería así
[CODE]select F.*,P.* from factura F
inner join Pagos P on P.IdFactura=F.Id
where Factura='xxx'{/CODE]