Resolución ejercicio final (25) - Generar modelo 347

RESOLUCIÓN DEL PUNTO 22 DEL ENUNCIADO DEL EJERCICIO FINAL.

En esta entrada resolveremos el punto 22:
Generar listado de Clientes y Proveedores para generar el modelo 347 (3005,06 €).

Se trata de generar un listado en el que se incluyan los clientes y proveedores que sobrepasen la cantidad facturada (IVA incluido) de 3005,06 € (lo que se corresponde, exactamente, con las antiguas 500000 pts).

Se realizará una consulta a siete tablas (Ivas, Articulos, lineas y cabeceras tanto de facturas de venta como de albaranes de compra, clientes y proveedores )

Dada la complejidad del listado, se puede facilitar el proceso dividiéndolo en dos partes. Por una parte se buscarán los datos necesarios para los clientes y en otra para los proveedores, que luego se unirán en un único listado mediante UNION.

Para obtener los clientes, la consulta se hará a cuatro tablas.

Se podrá aprovechar la solución del punto 13 (Ver totales de cliente entre fechas)

Para poder diferenciar entre clientes y proveedores se añadirá un campo literal, que permitirá diferenciar entre unos y otros una vez unidas las consultas.

select clientes.id_cliente as id, nombre, nif,
round(sum(neto * cantidad * ((100.0 + tipo_iva) / 100.0)),2) as total, 

"cliente" as tipo
from clientes left join cab_fv
on clientes.id_cliente = cab_fv.id_cliente
left join lin_fv
on cab_fv.id_fv = lin_fv.id_fv
inner join ivas
on lin_fv.iva = ivas.codigo_iva
where cab_fv.fecha between "2012-01-01" and "2012-12-31"

group by clientes.id_cliente;

Para obtener los proveedores, la consulta es similar a la anterior solo que cambiando los campos por los correspondientes a las tablas de proveedores y albaranes de compra.

select proveedores.id_proveedor as id, nombre, nif,
round(sum(neto * cantidad * ((100.0 + tipo_iva) / 100.0)),2) as total, 
"proveedor" as tipo
from proveedores left join cab_ac
on proveedores.id_proveedor = cab_ac.id_proveedor
left join lin_ac
on cab_ac.id_ac = lin_ac.id_ac
inner join ivas
on lin_ac.iva = ivas.codigo_iva
where cab_ac.fecha between "2012-01-01" and "2012-12-31"
group by proveedores.id_proveedor;

Una vez que se tienen las dos consultas solo es preciso unirlas mediante la cláusula UNION.

select clientes.id_cliente as id, nombre, nif,

round(sum(neto * cantidad * ((100.0 + tipo_iva) / 100.0)),2) as total, 

"cliente" as tipo

from clientes left join cab_fv

on clientes.id_cliente = cab_fv.id_cliente

left join lin_fv

on cab_fv.id_fv = lin_fv.id_fv

inner join ivas

on lin_fv.iva = ivas.codigo_iva

where cab_fv.fecha between "2012-01-01" and "2012-12-31"

group by clientes.id_cliente

UNION

select proveedores.id_proveedor as id, nombre, nif,

round(sum(neto * cantidad * ((100.0 + tipo_iva) / 100.0)),2) as total, 

"proveedor" as tipo

from proveedores left join cab_ac

on proveedores.id_proveedor = cab_ac.id_proveedor

left join lin_ac

on cab_ac.id_ac = lin_ac.id_ac

inner join ivas

on lin_ac.iva = ivas.codigo_iva

where cab_ac.fecha between "2012-01-01" and "2012-12-31"

group by proveedores.id_proveedor;

El resultado es una consulta de 23 líneas.

NOTA: Una posible solución para simplificar esta consulta y facilitar su reutilización es crear una vista, mediante CREATE VIEW, para obtener los clientes, otra para los proveedores y finalmente unir ambas en una sola consulta.

Creación de la vista de clientes:

create view clientes347
(id, nombre, nif, total, tipo)
as
select clientes.id_cliente, nombre, nif,
round(sum(neto * cantidad * ((100.0 + tipo_iva) / 100.0)),2), 
"cliente"
from clientes left join cab_fv
on clientes.id_cliente = cab_fv.id_cliente
left join lin_fv
on cab_fv.id_fv = lin_fv.id_fv
inner join ivas
on lin_fv.iva = ivas.codigo_iva
where cab_fv.fecha between "2012-01-01" and "2012-12-31"
group by clientes.id_cliente;

Si se realiza un select de esta vista se obtiene como resultado el mismo que ejecutando la consulta completa:

select * from clientes347;

Creación de la vista de proveedores:

create view proveedores347
(id, nombre, nif, total, tipo)
as
select proveedores.id_proveedor, nombre, nif,
round(sum(neto * cantidad * ((100.0 + tipo_iva) / 100.0)),2), 
"proveedor"
from proveedores left join cab_ac
on proveedores.id_proveedor = cab_ac.id_proveedor
left join lin_ac
on cab_ac.id_ac = lin_ac.id_ac
inner join ivas
on lin_ac.iva = ivas.codigo_iva
where cab_ac.fecha between "2012-01-01" and "2012-12-31"
group by proveedores.id_proveedor;

Si se realiza un select de esta vista se obtiene como resultado el mismo que ejecutando la consulta completa:

select * from proveedores347;

Y finalmente para obtener el listado completo, solo será preciso realizar la unión entre ambas vistas. Recordemos que SQL trata a las vistas como si fuesen tablas.

select * from clientes347
UNION
select * from proveedores347;