SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIfIER OFF
GO

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

CREATE PROCEDURE WINV_RepFisco
					@list_prov varchar(8000), 
					@fdesde datetime,
					@fhasta datetime, 
					@importe numeric(12,2)
AS

Begin

/*
    Date: Apr 13,2016

*/


  DECLARE @Cad varchar(8000)

/*
	 da un cursor con las compras detalladas segun la seleccion
	 el programa es el que debe dar el resumen o detalle en el reporte 
	 segun el tipo que se seleccione
	 el importe en MB de lo comprado al proveedor se calcula sumando para
	 cada linea de compra el cociente de importe_a_pagar entre tasa

*/
	If @importe is null 
		Begin
     	/* ======================================
		   Region 01
			Selección directa de proveedores
			Compras
		   ======================================	
        */ 
			Select @Cad = 'Select 
				p.siglas,
				p.nombre_empresa,
				o.consecutivo,
				o.referencia,
				o.fec_oper,
				o.observaciones,
				cc.factura,
				cc.id_prov,
				cc.id_mone,
				cod_mone,
				cc.tasa,
				cc.incluido,
				cl.importe_a_pagar,
				cl.importe,
				cl.imp_desgaste,
				cl.impuesto_total,
				im.importe_imp,
				o.id_operacion
			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 siclamon sm on sm.id_mone = cc.id_mone
			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 


     	/* ======================================
		   Region 02
  			Reversiones
		   ======================================	
        */ 
			Select @Cad = @cad + 
				'Union ALL 
				Select 
					p.siglas,
					p.nombre_empresa,
					o.consecutivo,
					o.referencia,
					o.fec_oper,
					o.observaciones,
					cc.factura,
					cc.id_prov,
					cc.id_mone,
					cod_mone,
					cc.tasa,
					cc.incluido,
					-cl.importe_a_pagar,
					-cl.importe,
					-cl.imp_desgaste,
					-cl.impuesto_total,
					-im.importe_imp,
					o.id_operacion
			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 siclamon sm on sm.id_mone = cc.id_mone
				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 +
				'order by p.siglas,  cc.factura,o.id_operacion, o.fec_oper'
		
			Execute  (@cad)

		End
	
	If @importe is not null
		Begin		
		/* ========================================================================
		   Region 03
			 Selección de proveedores por monto de las compras
			 Primero se seleccionan los proveedores cuyo monto de compras
			 en el periodo seleccionado sea mayor que el indicado
			 Despues se seleccionan las compras de ese periodo para esos proveedores
			 En ambos pasos se consideran compras y reversiones
			 En este caso nunca se tiene en cuenta el valor del param list_prov
           ========================================================================
		*/	


     	/* ======================================
		   Region 04
  			Proveedores, total de compras 
		   ======================================	
        */ 
 		Select id_prov = convert(int,id_prov), 
			    total = convert(numeric(12,2),sum(importe_a_pagar/tasa))
			Into #tot
			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
			 Where o.estado = 2 and consignacion = 0 
			   and (DatedIff(dy, fec_oper,coalesce(@fdesde,fec_oper)) <= 0) 
			   and (DatedIff(dy, fec_oper,coalesce(@fhasta,fec_oper)) >= 0) 
				Group by id_prov

     	/* ======================================
		   Region 05
  			Proveedores, total de reversiones de compras 
		   ======================================	
        */ 
			Union all

			Select id_prov = convert(int,id_prov), 
					total = convert(numeric(12,2),sum(-importe_a_pagar/tasa))
				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
				Where o.estado = 2 and consignacion = 0 
				  and (DatedIff(dy, o.fec_oper,coalesce(@fdesde,o.fec_oper)) <= 0) 
				  and (DatedIff(dy, o.fec_oper,coalesce(@fhasta,o.fec_oper)) >= 0) 
				Group by id_prov
	
     	/* ======================================
		   Region 06
  			Total por proveedor
		   ======================================	
        */ 
			Select id_prov, totcmp=sum(coalesce(total,0))
			   Into #prov
			   From #tot
			   Group by id_prov
			   having  sum(coalesce(total,0)) > @importe
	
			drop table #tot

			-- Compras de los proveedores a incluir
			Select 
				p.siglas,
				p.nombre_empresa,
				o.consecutivo,
				o.referencia,
				o.fec_oper,
				o.observaciones,
				cc.factura,
				cc.id_prov,
				cc.id_mone,
				cod_mone,
				cc.tasa,
				cc.incluido,
				cl.importe_a_pagar,
				cl.importe,
				cl.imp_desgaste,
				cl.impuesto_total,
				im.importe_imp,
				o.id_operacion
			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 #prov tp on cc.id_prov = tp.id_prov
				inner join siclamon sm on sm.id_mone = cc.id_mone
			Where o.estado = 2 and consignacion = 0 
			  and (DatedIff(dy, o.fec_oper,coalesce(@fdesde,o.fec_oper)) <= 0) 
			  and (DatedIff(dy, o.fec_oper,coalesce(@fhasta,o.fec_oper)) >= 0) 

     	/* ======================================
		   Region 07
			 Reversiones
		   ======================================	
        */ 
			Union ALL 
				Select 
					p.siglas,
					p.nombre_empresa,
					o.consecutivo,
					o.referencia,
					o.fec_oper,
					o.observaciones,
					cc.factura,
					cc.id_prov,
					cc.id_mone,
					cod_mone,
					cc.tasa,
					cc.incluido,
					-cl.importe_a_pagar,
					-cl.importe,
					-cl.imp_desgaste,
					-cl.impuesto_total,
					-im.importe_imp,
					o.id_operacion
			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 #prov tp on cc.id_prov = tp.id_prov
				inner join siclamon sm on sm.id_mone = cc.id_mone
			Where o.estado = 2 and consignacion = 0 
			  and (DatedIff(dy, o.fec_oper,coalesce(@fdesde,o.fec_oper)) <= 0) 
			  and (DatedIff(dy, o.fec_oper,coalesce(@fhasta,o.fec_oper)) >= 0) 
			order by p.siglas,cc.factura, o.id_operacion,  o.fec_oper

		drop table #prov

	End


End









