select prod.prod_codigo AS PROD_CODIGO,

prod.prod_detalle AS PROD_DETALLE,

rubro.rubr_id AS ID_PROD_RUBRO,

rubro.rubr_detalle AS PROD_RUBRO,

(select count(*) from Producto p2 where p2.prod_rubro = prod.prod_rubro) as CANT_PROD_RUBRO,

(case when exists(select * from Item_Factura it2 where it2.item_producto = prod.prod_codigo) 

then (select max(it2.item_precio) from Item_Factura it2 where it2.item_producto = prod.prod_codigo) 

else 0 end) as PRECIO_MAX

from Producto prod join Rubro rubro 

on prod.prod_rubro = rubro.rubr_id

where prod.prod_codigo not in (select distinct it.item_producto from Factura f join Item_Factura it

on f.fact_numero = it.item_numero

and f.fact_sucursal = it.item_sucursal

and f.fact_tipo = it.item_tipo

where year(f.fact_fecha) = 2012)

order by (case when exists(select * from Composicion comp where comp.comp_producto = prod.prod_codigo) then 1 else 0 end) DESC

GO

 --para un cliente dado retorna el total vendido en el mes y año actual para el cliente

CREATE FUNCTION total_vendido_mes(@fact_cliente char(6))

RETURNS DECIMAL(12,2)

AS

	BEGIN



		RETURN (select sum(f.fact_total) from Factura f 

				where f.fact_cliente = @fact_cliente 

				and MONTH(CURRENT_TIMESTAMP) = MONTH(f.fact_fecha) 

				and YEAR(CURRENT_TIMESTAMP) = YEAR(f.fact_fecha))

	END

GO



--se considera que el fact_total una vez insertado no se vuelve a modificar

CREATE TRIGGER tr_check_limite_credito on Factura

instead of INSERT

AS

	BEGIN

		IF(exists(select * from inserted i where month(i.fact_fecha) != month(CURRENT_TIMESTAMP)))

			BEGIN

				RAISERROR (15599,10,1, 'No se puede facturar un mes distinto del actual'); 

			END

		ELSE

			BEGIN

				IF(exists(select * from inserted i join Cliente c 

							on c.clie_codigo = i.fact_cliente			--total_vendido_mes esta definido arriba

							where c.clie_limite_credito < (i.fact_total + total_vendido_mes(i.fact_cliente)) ) )

					BEGIN

						RAISERROR (15600,10,1, 'La venta no puede superar el limite de credito para el cliente'); 

					END

				ELSE

					BEGIN

						INSERT INTO Factura (fact_tipo,fact_sucursal,fact_numero,fact_fecha,fact_vendedor,fact_total,fact_total_impuestos,fact_cliente)

						SELECT fact_tipo,fact_sucursal,fact_numero,fact_fecha,fact_vendedor,fact_total,fact_total_impuestos,fact_cliente from inserted 

					END

			END

	END

GO