alter procedure [dbo].[_LiquidacionAdicionalesAnticipado]	
@p_alquiler as INT = 0

as begin

declare @adicional as table(
checked varchar(100)
,id_item int
,item varchar(max)
,codigoinventario varchar(max)  
,valorunitario float
,cantidad float
,id_catalogo int
,cantidadfacturado float
,porcdto float
,porciva float
,totaleje float
,cantidadfac float
,entradas float
,total float
,fechainicio smalldatetime,
fechafin smalldatetime,
anticipado varchar(10)
);

insert into @adicional (
checked 
,id_item 
,item
,codigoinventario
,valorunitario 
,cantidad 
,cantidadfacturado 
,porcdto 
,cantidadfac 
,entradas
,total 
 ,fechainicio
 ,fechafin,
 anticipado)
 

select 'True' as checked,
 itemalq.id as id_item,
 itemalq.itemdocumento  as item,
 cat.codigoInventario as codigoinventario,
 itemalq.valorunitarioPlaneado as valorunitario,
 --doc.prefijo,
 -- revisar que los documentos no esten cancelados 
 isnull((select  sum(pre.cantidadplaneado) from doc_itemDocumento as pre where pre.id_itemDocumento in (itemalq.id) and pre.id_documento in (select id from [doc_documento.cotizacion])),0) as cantidad,
 isnull( (select  sum(sal.cantidadplaneado) from doc_itemDocumento as sal where sal.id_itemDocumento in (itemalq.id) and sal.id_documento in (select id from [doc_documento.salida] where active = 1 ) and sal.id_catalogo in(select id from [cat_catalogo.otro] where periodoAlquiler <1) ) ,0) as cantidadfacturado, 
   itemalq.porcdto,
   0 as cantidadfac, 
  isnull( (select  sum(ent.cantidadplaneado) from doc_itemDocumento as ent where ent.id_itemDocumento in (itemalq.id) and ent.id_documento in (select id from [doc_documento.entrada] where active = 1 ) and ent.id_catalogo in(select id from [cat_catalogo.otro] where periodoAlquiler <1) ),0) as entradas,
 -- 0 as entradas,
  0  as total, 
  '01/01/1900' as fechainicio, 
   '01/01/1900' as fechafin, 
    --itemalq.porcdto,
   'True' as anticipado
       from doc_itemdocumento as itemalq
inner join doc_itemDocumento as itemspre on itemalq.id = itemspre.id_itemDocumento
--inner join doc_itemDocumento as itemssalida on itemalq.id = itemssalida.id_itemDocumento
--inner join doc_itemDocumento as itementrada on itemalq.id = itementrada.id_itemDocumento
inner join doc_documento as doc on doc.id =  itemalq.id_documento
inner join [cat_catalogo.otro] as catotro on itemspre.id_catalogo = catotro.id 
inner join [cat_catalogo] as cat on itemspre.id_catalogo =  cat.id
where doc.id = @p_alquiler 
and catotro.periodoAlquiler = 0 
and itemspre.id_documento in (select id from [doc_documento.cotizacion] where active = 1) 
--and itemssalida.id_documento in (select id from [doc_documento.salida]) --and itemssalida.id_documento in (select id from [doc_documento.entrada])
--and itemssalida.id not in (select id_itemDocumento from alq_detalleLiquidacion where active = 1)
--and itementrada.id_documento in (select id from [doc_documento.entrada])
and itemalq.id_catalogo in (select id from [cat_catalogo.otro] where periodoAlquiler < 1)
group by itemspre.id_catalogo,itemalq.id,itemalq.itemdocumento,itemalq.valorunitarioPlaneado,doc.prefijo,cat.codigoInventario,cat.precioVenta,itemalq.cantidadplaneado,itemalq.porcdto, itemalq.itemDocumento
--having sum (itemssalida.cantidadplaneado) > sum (itemspre.cantidadplaneado) 
update @adicional set cantidadfacturado = (cantidadfacturado + entradas)
update @adicional set cantidadfac  = (  cantidadfacturado   - cantidad)
update @adicional set total = (cantidadfac * valorunitario) - ((cantidadfac * valorunitario) * (porcdto/100))
select * from  @adicional where cantidadfac > 0
end



