SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIfIER OFF
GO

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

CREATE PROCEDURE WINV_ResuXProveedor
					@list_artis varchar(8000), 
					@list_alma varchar(8000), 
					@list_secc varchar(8000), 
					@list_prov varchar(8000), 
					@fdesde datetime,
					@fhasta datetime,
					@contabilidad bit
AS

BEGIN
/*
	Date: Apr 15,2016


*/

   DECLARE @Cad varchar(8000), 
			@Filtro_fecha varchar(200), 
			@Filtro_destino varchar(8000), 
			@Filtro_arti varchar(8000),
			@Filtro_prov varchar(8000)


/* =================================================
   Region 01
   -- Preparo los parámetros
   =================================================
*/

Select @Filtro_arti=''

If @list_artis is not NULL
	Select @Filtro_arti =' AND ' 
	                    + @list_artis

   Select @Filtro_destino = ''

   If     @list_alma is not NULL 
      and @list_secc is not NULL
      Select @Filtro_destino = ' AND ( (cmpi.id_alma is not null and cmpi.id_alma in (' 
	                         + @list_alma 
							 + ')) or (cmpi.id_secc is not null and cmpi.id_secc in (' 
							 + @list_secc + ')) ) '

   Else
      Begin 
         If @list_alma is not NULL
            Select @Filtro_destino = ' AND (cmpi.id_alma is not null and cmpi.id_alma in (' 
			                       + @list_alma + ') )'
         Else

	If @list_secc is not NULL

	   Select @Filtro_destino = ' AND (cmpi.id_secc is not null and cmpi.id_secc in (' 
							  + @list_secc + ') )'

      End

Select @Filtro_prov=''

If @list_prov is not NULL
	Select @Filtro_prov = ' AND cmpc.id_prov in (' 
					    + @list_prov + ') '

Set @Filtro_fecha=''

If @fdesde is not null
	Select @Filtro_fecha = ' and DatedIff(dd, convert(datetime,"'
						 + convert(varchar(20),@fdesde,101)
						 + '",101), oper.fec_oper) >= 0'

If @fhasta is not null
	Select @Filtro_fecha = @Filtro_fecha 
	                     + ' and DatedIff(dd, oper.fec_oper, convert(datetime,"'
						 + convert(varchar(20),@fhasta,101)
						 + '",101)) >= 0'


/* =================================================
   Region 02
	-- Crear tabla temporal
   =================================================
*/

	CREATE TABLE #compras (
			cod_dest char(4),
			cod_fami char(16),
			id_dest int,
			isalma char(1),
			id_alma int, 
			id_secc int,
			id_arti int, 
			cantidad numeric(12,4),
			importe numeric(12,2),
			importe_a_pagar numeric(12,2),
			reversion bit,
			fec_oper datetime,
			id_prov int,
			id_espe int )


/* =================================================
   Region 04
	-- Compras
   =================================================
*/
    Select @Cad = 
	   'INSERT #compras
			Select cod_dest = Case When cmpi.id_alma is not null 
								   Then "A"+ dbo.GetCodigoAlmacen(cmpi.id_alma) 
								   Else "S" + dbo.GetCodigoSeccion(cmpi.id_secc) 
								   End, 
					cod_fami=dbo.FullFamiCode(arti.id_fami),
					id_dest = Case When cmpi.id_alma is not null 
					               Then cmpi.id_alma 
								   Else cmpi.id_secc 
								   End, 
					isalma = Case When cmpi.id_alma is not null 
								  Then "A" 
								  Else "S" 
								  End,
					cmpi.id_alma, cmpi.id_secc, cmpl.id_arti,
					cantidad = coalesce(cmpl.cantidad_D,0),
					importe = coalesce(cmpl.importe,0),
					importe_a_pagar = coalesce(cmpl.importe_a_pagar,0),
					reversion=convert(bit,0),
					oper.fec_oper, cmpc.id_prov,fami.id_espe
			From stokoper oper
				INNER JOIN stokcmpc cmpc ON oper.id_operacion=cmpc.id_operacion
				INNER JOIN stokcmpi cmpi ON cmpc.id_operacion=cmpi.id_operacion
				INNER JOIN stokcmpl cmpl ON cmpi.id_cmpi=cmpl.id_cmpi
				INNER JOIN stokarti arti ON cmpl.id_arti = arti.id_arti
				INNER JOIN stokfami fami ON arti.id_fami = fami.id_fami
				INNER JOIN stoktmov tmov ON oper.id_tmov = tmov.id_tmov
			Where tmov.id_cmov = 1 
			  and oper.estado = 2 
			  and cmpc.consignacion = 0 ' 
			  + @Filtro_fecha 
			  + @Filtro_arti 
			  + @Filtro_destino 
			  + @Filtro_prov

		EXECUTE (@CAD)

/* =================================================
   Region 05
	-- Reversiones de Compras
   =================================================
*/
    Select @Cad = 
	   'INSERT #compras
			Select cod_dest = Case When cmpi.id_alma is not null 
								   Then "A"+ dbo.GetCodigoAlmacen(cmpi.id_alma) 
								   Else "S" + dbo.GetCodigoSeccion(cmpi.id_secc) 
								   End, 
					cod_fami=dbo.FullFamiCode(arti.id_fami),
					id_dest = Case When cmpi.id_alma is not null 
							       Then cmpi.id_alma 
								   Else cmpi.id_secc 
								   End, 
					isalma = Case When cmpi.id_alma is not null 
								  Then "A" 
								  Else "S" 
								  End,
					cmpi.id_alma, cmpi.id_secc, cmpl.id_arti,
					cantidad = coalesce(-cmpl.cantidad_D,0),
					importe = coalesce(-cmpl.importe,0),
					importe_a_pagar = coalesce(-cmpl.importe_a_pagar,0),
					reversion=convert(bit,1),
					oper.fec_oper, cmpc.id_prov,fami.id_espe
			From stokoper oper
				INNER JOIN stokcmpc cmpc ON oper.id_revertido=cmpc.id_operacion
				INNER JOIN stokcmpi cmpi ON cmpc.id_operacion=cmpi.id_operacion
				INNER JOIN stokcmpl cmpl ON cmpi.id_cmpi = cmpl.id_cmpi 
				INNER JOIN stokarti arti ON cmpl.id_arti = arti.id_arti
				INNER JOIN stokfami fami ON arti.id_fami = fami.id_fami
				INNER JOIN stoktmov tmov ON oper.id_tmov = tmov.id_tmov
			Where tmov.id_cmov = 2 
			  and oper.estado = 2 
			  and cmpc.consignacion = 0  
			  and cmpl.id_revertidor is not null 
			  and cmpl.id_revertidor = oper.id_operacion ' 
			  + @Filtro_fecha 
			  + @Filtro_arti 
			  + @Filtro_destino 
			  + @Filtro_prov

		EXECUTE (@CAD)

/* =================================================
   Region 06
	-- Crear tabla temporal
   =================================================
*/

	If @contabilidad = 0
		CREATE TABLE #resum (
				id_prov int,
				cod_dest char(4),
				cod_fami char(16),
				id_arti int, 
				id_dest int,
				isalma char(1),
				id_espe int,
				cantidad numeric(12,4),
				importe numeric(12,2) )
	Else
		CREATE TABLE #resum_conta (
				id_cta int,
				id_cto int,
				cantidad numeric(12,4),
				importe numeric(12,2) )

 
	If @contabilidad = 0
		Select @Cad = 
		   'insert #resum 
			   Select id_prov, cod_dest, cod_fami, 
					  id_arti, id_dest=MIN(id_dest), 
					  isalma=MIN(isalma), id_espe=MIN(COALESCE(id_espe,-1)),
					  cantidad=SUM(cantidad), importe=SUM(importe_a_pagar) 
			  From #compras
			  group by id_prov, cod_dest, 
					   cod_fami, id_arti '
	Else
		Select @Cad = 
		   'insert #resum_conta 
			   Select id_cta, id_cto, 
					  cantidad=SUM(cantidad), 
					  importe=SUM(importe_a_pagar) 
				  From #compras c 
				  left join stokenpr e on c.id_prov = e.id_prov 
				  group by id_cta, id_cto '

	  Execute(@Cad)

If @contabilidad = 0
	   Select @Cad = 
		  'Select res.*, 
					cod_arti=dbo.FullArtiCode(arti.id_arti), desc_arti=arti.descripcion,
					cod_seccion = Case When res.isalma="A" 
									   Then alma.codigo 
									   Else secc.codigo 
									   End, 
					desc_seccion = Case When res.isalma="A" 
										Then alma.descripcion 
										Else secc.descripcion 
										End ,
					cod_unme = Case When res.isalma="A" 
									Then dbo.GetUMCode(arti.id_unme_al) 
									Else dbo.GetUMCode(arti.id_unme_se) 
									End,
					cant_unme = Case When res.isalma="A" 
									 Then cant_unme_al 
									 Else cant_unme_se 
									 End,
					cod_grupo=grup.codigo, 
					desc_grupo=grup.descripcion, desc_fami=fami.descripcion,
					especialidad=espe.descripcion,
					cod_prov=prov.siglas, desc_prov=prov.nombre_empresa
					
	        From #resum res
				Left Join stokalma alma On res.id_dest = alma.id_alma
				Left Join stoksecc secc On res.id_dest = secc.id_secc
				inner join stokarti arti on res.id_arti = arti.id_arti
				inner join stokfami fami on arti.id_fami = fami.id_fami
				inner join stokgrup grup on fami.id_grup = grup.id_grup
				inner join stokprov prov on res.id_prov = prov.id_prov
				LEFT JOIN stokespe espe ON fami.id_espe = espe.id_espe
		order by  cod_prov, cod_dest, 
		cod_fami, cod_arti '
Else

	   Select @Cad = 
	      'Select res.*, 
		          c.id_cuenta, c.cuenta, 
				  cto_gto=COALESCE(c.cto_gto,""), c.tit_cta
				
	          From #resum_conta res
			       left join stokctacto c on res.id_cta = c.id_cta 
				   and (res.id_cto is null 
				   or (res.id_cto is not null and res.id_cto = c.id_cto ))
		      order by  cuenta, cto_gto '


  Execute(@Cad)


   Drop table #compras

  If @contabilidad = 0
     Drop table #resum
  Else
     Drop table #resum_conta

End


