SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

IF EXISTS (Select * From sys.objects 
   Where object_id = OBJECT_ID(N'WINV_RepExtracto') )
DROP Procedure WINV_RepExtracto
GO

CREATE PROCEDURE WINV_RepExtracto 
					@list_prov varchar(8000), 
					@cod_desde varchar(20), 
					@cod_hasta varchar(20), 
					@fdesde datetime,
					@fhasta datetime, 
					@tipo tinyint
AS

BEGIN
/*
	Date: Apr 18,2016

*/

/* ===================================================
	da un cursor con las compras para el extracto 
	de proveedores
	segun los parametros seleccionados y 
	el tipo de reporte elegido
	se incluyen las reversiones
	todo siempre en moneda base porque es consolidado
	===================================================
*/
  
  DECLARE @Cad varchar(8000), 
          @filtro_arti varchar(200), 
		  @order varchar(500)

/* =========================================
	Region 01
	-- preparar filtro para artículos
   =========================================
*/
	Select @filtro_arti = ''
	If     @cod_desde is not NULL 
	   and @cod_hasta is not NULL

	  Select @filtro_arti = ' AND dbo.FullArtiCode(cl.id_arti) between "' 
	                      + @cod_desde + '" and "' 
						  + @cod_hasta + '" '

	Else
	  Begin 
	     If @cod_desde is not NULL
	        Select @filtro_arti = ' AND dbo.FullArtiCode(cl.id_arti) >= "' 
						        + @cod_desde + '" '
	     Else
	 If @cod_hasta is not NULL
	    Select @filtro_arti = ' AND dbo.FullArtiCode(cl.id_arti) <= "' 
						    + @cod_hasta + '" '
	  End

	-- preparar order by
	Select @order = ' order by ' + 
				Case @tipo
				When 1 Then 'p.siglas, grcod, fmcod '
				When 2 Then 'grcod, fmcod, arcod '
				When 3 Then 'p.siglas, arcod, o.fec_oper '
				When 4 Then 'p.siglas, arcod '
				When 5 Then 'grcod, fmcod, p.siglas '
				When 6 Then 'grcod, fmcod, arcod, p.siglas '
				End

/* =========================================
	Region 02
	-- Compras
   =========================================
*/
	Select @Cad = 
	'Select 
		p.siglas,
		p.nombre_empresa,
		o.fec_oper,
		importe_a_pagarMB = round(cl.importe_a_pagar * cc.tasa,2),
		cl.importe,
		cl.imp_desgaste,
		cl.cantidad,
		cl.cantidad_extra,
		im.importe_imp,
		grcod=g.codigo,
		fmcod=dbo.FullFamiCode(f.id_fami),
		arcod=dbo.FullArtiCode(cl.id_arti),
		grdes=g.descripcion,
		fmdes=f.descripcion,
		ardes=a.descripcion,
		umcod=u.codigo,
		umdes=u.descripcion,
		pmedio=x.costo
		
	From stokoper o 
		inner join stokcmpc cc on cc.id_operacion = o.id_operacion
		inner join stokcmpi ci on cc.id_operacion = ci.id_operacion
		inner join stokcmpl cl on cl.id_cmpi = ci.id_cmpi
		left join (Select id_cmpl,importe_imp=sum(importe_imp) 
					  From stokcmli 
					  group by id_cmpl) im 
			on cl.id_cmpl=im.id_cmpl
		inner join stokprov p on cc.id_prov = p.id_prov 
		inner join stokarti a on a.id_arti = cl.id_arti
		inner join stokfami f on f.id_fami = a.id_fami
		inner join stokgrup g on g.id_grup = f.id_grup
		inner join stokunme u on u.id_unme = a.id_unme_co
		left join stokalex x on a.id_arti=x.id_arti
	where o.estado = 2 and consignacion = 0 
	  and (Datediff(dy, fec_oper,' + 
		Case When @fdesde is null 
			 Then 'fec_oper' 
			 Else '''' + convert(char(10),@fdesde,121)+'''' 
			 End +		
		    ') <= 0) and (Datediff(dy, fec_oper,' + 
		Case When @fhasta is null 
		     Then 'fec_oper' 
			 Else ''''+convert(char(10),@fhasta,121)+'''' 
			 End +') >= 0) '

	Select @Cad = @Cad + 
		Case When @list_prov is null 
			 Then '' 
		     Else ' and cc.id_prov in (' + @list_prov + ')' 
			 End 

	Select @Cad = @Cad + @filtro_arti

/* =========================================
	Region 03
	-- Reversiones
   =========================================
*/
	Select @Cad = @cad + 
		'UNION ALL 
		Select 
		p.siglas,
		p.nombre_empresa,
		o.fec_oper,
		importe_a_pagarMB = round(-cl.importe_a_pagar * cc.tasa,2),
		-cl.importe,
		-cl.imp_desgaste,
		-cl.cantidad,
		-cl.cantidad_extra,
		-im.importe_imp,
		grcod=g.codigo,
		fmcod=dbo.FullFamiCode(f.id_fami),
		arcod=dbo.FullArtiCode(cl.id_arti),
		grdes=g.descripcion,
		fmdes=f.descripcion,
		ardes=a.descripcion,
		umcod=u.codigo,
		umdes=u.descripcion,
		pmedio=x.costo
	From stokoper o 
		inner join stokoper rvtda on o.id_revertido = rvtda.id_operacion
		inner join stokcmpc cc on cc.id_operacion = rvtda.id_operacion
		inner join stokcmpl cl on cl.id_revertidor = o.id_operacion
		inner join stokcmpi ci on cl.id_cmpi = ci.id_cmpi
		left join (Select id_cmpl,importe_imp=sum(importe_imp) 
					  From stokcmli 
					  group by id_cmpl) im 
			on cl.id_cmpl=im.id_cmpl
		inner join stokprov p on cc.id_prov = p.id_prov 
		inner join stokarti a on a.id_arti = cl.id_arti
		inner join stokfami f on f.id_fami = a.id_fami
		inner join stokgrup g on g.id_grup = f.id_grup
		inner join stokunme u on u.id_unme = a.id_unme_co
		left join stokalex x on a.id_arti=x.id_arti
	where o.estado = 2 
	  and consignacion = 0 
	  and (Datediff(dy, o.fec_oper,' + 
			Case When @fdesde is null 
			     Then 'o.fec_oper' 
				 Else ''''+convert(char(10),@fdesde,121)+'''' 
				 End +		
			') <= 0) and (Datediff(dy, o.fec_oper,' + 
			Case When @fhasta is null 
			     Then 'o.fec_oper' 
				 Else ''''+convert(char(10),@fhasta,121)+'''' 
				 End + ') >= 0) '

	Select @Cad = @Cad + 
		Case When @list_prov is null 
		     Then '' 
		     Else ' and cc.id_prov in (' + @list_prov + ')' 
			 End 

	Select @Cad = @Cad + @filtro_arti + @order

	execute (@cad)

End











