SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

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

CREATE PROCEDURE WINV_ListConsumoAllIncl 
					@Fecha datetime, 
					@DiaMes tinyint, 
					@AcuMes bit, 
					@OrderBy tinyint, 
					@CliexDias bit
AS
-- se consideran consumo:
		-- fijacion de inventario negativa
		-- ajustes negativos de cantidad
		-- rebajas de pv (tanto principales como alternativas) 
		-- traslados a secciones de gasto (es consumo de la seccion destino)
		-- compras de articulos que NCE (es consumo del alma o secc q compra)
		-- traslados a secciones de familias de insumo (es consumo de la seccion destino)
		-- orígenes de despieces
BEGIN


   DECLARE @Cad varchar(8000), 
			@filtro_fecha varchar(500), 
			@order_by varchar(200),
			@CadFields varchar(500), 
			@id_tgrp int,
			@codigo char(10),
			@grpcount int,
			@clies_dias int,
			@clies_mes int

/* ========================================
   Region 01
   -- para un dia
   ========================================
*/
	if @DiaMes = 1	
		begin
			if @AcuMes = 0
				begin
					select @filtro_fecha = 
							' and Datediff(dd, convert(datetime,"'
							+ convert(varchar(20),@Fecha,101)
							+ '",101), smay.fec_oper) = 0'
				end

			else	-- con acumulado del mes
				begin

					select @filtro_fecha = 
						   ' and Datediff(yy, convert(datetime,"'
						   + convert(varchar(20),@Fecha,101)
						   + '",101), smay.fec_oper) = 0'

					select @filtro_fecha = 
							@filtro_fecha 
							+ ' and Datediff(mm, smay.fec_oper, convert(datetime,"'
							+ convert(varchar(20),@Fecha,101)
							+ '",101)) = 0'

					select @filtro_fecha = 
							@filtro_fecha 
							+ ' and Datediff(dd, smay.fec_oper, convert(datetime,"'
							+ convert(varchar(20),@Fecha,101)
							+ '",101)) >= 0'
				end
		end
	else	-- para un mes
		begin

			select @filtro_fecha = 
				' and Datediff(yy, convert(datetime,"'
				+ convert(varchar(20),@Fecha,101)
				+ '",101), smay.fec_oper) = 0'

			select @filtro_fecha = 
					@filtro_fecha 
					+ ' and Datediff(mm, smay.fec_oper, convert(datetime,"'
					+ convert(varchar(20),@Fecha,101)
					+ '",101)) = 0'
	
		end

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

	CREATE TABLE #InvActual 
	      (	id_fami int,
			id_secc int,
			id_arti int, 
			fec_oper datetime,
			cantidad numeric(12,4),
			importe numeric(12,2))

-- arti.ctrl_exist = 0


/* ========================================
   Region 03
   -- Compras
   ========================================
*/
      Select @Cad = 
		'INSERT #invactual 
			SELECT arti.id_fami,
				smay.id_secc, 
				 smay.id_arti,
				smay.fec_oper,
				cantidad = coalesce(cmpl.cantidad_D,0),
				importe = coalesce(cmpl.importe,0)
			FROM stoksmay smay
				INNER JOIN stoktses tses ON smay.id_secc = tses.id_secc
				INNER JOIN stokscmp scmp ON scmp.id_smay=smay.id_smay
				INNER JOIN stokcmpl cmpl ON scmp.id_cmpl=cmpl.id_cmpl
				INNER JOIN stokarti arti ON smay.id_arti = arti.id_arti
				INNER JOIN stoktsef tsef ON arti.id_fami = tsef.id_fami
			WHERE smay.id_dmov = 1 and arti.ctrl_exist = 1 ' 
			+ @filtro_fecha 
			+ ' and smay.id_operacion 
			not in (select id_revertido 
					   from stokoper 
					   where id_revertido is not null and estado=1) '

		EXECUTE (@Cad)

/* ========================================
   Region 04
   --Traslados a secciones de gasto
   ========================================
*/
     Select @Cad = 
		'INSERT #invactual 
			SELECT arti.id_fami,
				smay.id_secc, 
				smay.id_arti,
				smay.fec_oper,
				cantidad = coalesce(tral.cantidad_D,0),
				importe = coalesce(tral.importe + tral.imp_desgaste,0)
			FROM stoksmay smay
				INNER JOIN stoktses tses ON smay.id_secc = tses.id_secc
				INNER JOIN stokstra stra ON stra.id_smay = smay.id_smay
				INNER JOIN stoktral tral ON stra.id_tral = tral.id_tral
				INNER JOIN stokarti arti ON smay.id_arti = arti.id_arti
				INNER JOIN stoktsef tsef ON arti.id_fami = tsef.id_fami
				INNER JOIN stokalfa alfa ON arti.id_fami = alfa.id_fami
				INNER JOIN stokalse alse ON alfa.id_alma = alse.id_alma 
				  and smay.id_secc = alse.id_secc
			WHERE smay.id_dmov = 32 and alse.gasto = 1 ' 
			+ @filtro_fecha 
			+ ' and smay.id_operacion 
			not in (select id_revertido 
					   from stokoper 
					   where id_revertido is not null and estado=1) '

		EXECUTE (@Cad)

/* ========================================
   Region 05
   --Traslados de familias de insumo
   ========================================
*/
      Select @Cad = 
		'INSERT #invactual 
			SELECT arti.id_fami,
				smay.id_secc, 
				smay.id_arti,
				smay.fec_oper,
				cantidad = coalesce(tral.cantidad_D,0),
				importe = coalesce(tral.importe + tral.imp_desgaste,0)
			FROM stoksmay smay
				INNER JOIN stoktses tses ON smay.id_secc = tses.id_secc
				INNER JOIN stokstra stra ON stra.id_smay = smay.id_smay
				INNER JOIN stoktral tral ON stra.id_tral = tral.id_tral
				INNER JOIN stokarti arti ON smay.id_arti = arti.id_arti
				INNER JOIN stoktsef tsef ON arti.id_fami = tsef.id_fami
				INNER JOIN stokfami fami ON arti.id_fami = fami.id_fami
			WHERE smay.id_dmov = 32 and fami.tipo = 2 ' 
			+ @filtro_fecha 
			+ ' and smay.id_operacion 
			not in (select id_revertido 
						from stokoper 
						where id_revertido is not null and estado=1) '

		EXECUTE (@Cad)


/* ========================================
   Region 06
   --MultiTraslados a secciones de gasto
   ========================================
*/
      Select @Cad = 
		'INSERT #invactual 
			SELECT arti.id_fami,
				smay.id_secc, 
				smay.id_arti,
				smay.fec_oper,
				cantidad = coalesce(mall.cantidad_D,0),
				importe = coalesce(mall.importe + mall.imp_desgaste,0)
			FROM stoksmay smay
				INNER JOIN stoktses tses ON smay.id_secc = tses.id_secc
				INNER JOIN stoksmal smal ON smal.id_smay = smay.id_smay
				INNER JOIN stokmall mall ON smal.id_mall = mall.id_mall
				INNER JOIN stokarti arti ON smay.id_arti = arti.id_arti
				INNER JOIN stoktsef tsef ON arti.id_fami = tsef.id_fami
				INNER JOIN stokalfa alfa ON arti.id_fami = alfa.id_fami
				INNER JOIN stokalse alse ON alfa.id_alma = alse.id_alma 
				  and smay.id_secc = alse.id_secc
			WHERE smay.id_dmov = 72 and alse.gasto = 1 ' 
			+ @filtro_fecha 

		EXECUTE (@Cad)

/* ========================================
   Region 07
   --MultiTraslados de familias de insumo
   ========================================
*/
      Select @Cad = 
		'INSERT #invactual 
			SELECT arti.id_fami,
				smay.id_secc, 
				smay.id_arti,
				smay.fec_oper,
				cantidad = coalesce(mall.cantidad_D,0),
				importe = coalesce(mall.importe + mall.imp_desgaste,0)
			FROM stoksmay smay
				INNER JOIN stoktses tses ON smay.id_secc = tses.id_secc
				INNER JOIN stoksmal smal ON smal.id_smay = smay.id_smay
				INNER JOIN stokmall mall ON smal.id_mall = mall.id_mall
				INNER JOIN stokarti arti ON smay.id_arti = arti.id_arti
				INNER JOIN stoktsef tsef ON arti.id_fami = tsef.id_fami
				INNER JOIN stokfami fami ON arti.id_fami = fami.id_fami
			WHERE smay.id_dmov = 72 and fami.tipo = 2 ' 
			+ @filtro_fecha 

		EXECUTE (@Cad)

/* ========================================
   Region 08
   -- Ajustes
   ========================================
*/
     Select @Cad = 
		'INSERT #invactual 
			SELECT arti.id_fami,
				smay.id_secc, 
				smay.id_arti,
				smay.fec_oper,
				cantidad = coalesce(ajul.cantidad,0),
				importe = coalesce(ajul.importe,0)
			FROM stoksmay smay
				INNER JOIN stoktses tses ON smay.id_secc = tses.id_secc
				INNER JOIN stoksaju saju ON saju.id_smay = smay.id_smay
				INNER JOIN stokajul ajul ON saju.id_ajul = ajul.id_ajul
				INNER JOIN stokarti arti ON smay.id_arti = arti.id_arti
				INNER JOIN stoktsef tsef ON arti.id_fami = tsef.id_fami
			WHERE smay.id_dmov = 5 and ajul.cantidad < 0 ' 
			+ @filtro_fecha 
			+ ' and smay.id_operacion 
			not in (select id_revertido 
						from stokoper 
						where id_revertido is not null and estado=1) '

		EXECUTE (@Cad)

/* ========================================
   Region 09
   -- Fijación de inventarios
   ========================================
*/
      Select @Cad = 
		'INSERT #invactual 
			SELECT arti.id_fami,
				smay.id_secc, 
				smay.id_arti,
				smay.fec_oper,
				cantidad = coalesce(invl.existencia-invl.toma,0),
				importe = coalesce(-invl.importe,0)
			FROM stoksmay smay
				INNER JOIN stoktses tses ON smay.id_secc = tses.id_secc
				INNER JOIN stoksinv sinv ON sinv.id_smay = smay.id_smay
				INNER JOIN stokinvl invl ON sinv.id_invl = invl.id_invl
				INNER JOIN stokarti arti ON smay.id_arti = arti.id_arti
				INNER JOIN stoktsef tsef ON arti.id_fami = tsef.id_fami
			WHERE smay.id_dmov = 9 and invl.toma < invl.existencia '
			 + @filtro_fecha 
			 + ' and smay.id_operacion 
			 not in (select id_revertido 
						from stokoper 
						where id_revertido is not null and estado=1) '
    
		EXECUTE (@Cad)

/* ========================================
   Region 10
   -- Rebaja Automatica
   -- principal
   ========================================
*/
      Select @Cad = 
		'INSERT #invactual 
			SELECT arti.id_fami,
				smay.id_secc, 
				smay.id_arti,
				smay.fec_oper,
				cantidad = coalesce(rebp.cantidad,0),
				importe = coalesce(rebp.importe,0)
			FROM stoksmay smay
				INNER JOIN stoktses tses ON smay.id_secc = tses.id_secc
				INNER JOIN stoksrep srep ON srep.id_smay = smay.id_smay
				INNER JOIN stokrebp rebp ON srep.id_rebp = rebp.id_rebp
				INNER JOIN stokarti arti ON smay.id_arti = arti.id_arti
				INNER JOIN stoktsef tsef ON arti.id_fami = tsef.id_fami
			WHERE smay.id_dmov = 121 ' 
			+ @filtro_fecha 
			+ ' and smay.id_operacion 
			not in (select id_revertido 
						from stokoper 
						where id_revertido is not null and estado=1) '
     
		EXECUTE (@Cad)

/* ========================================
   Region 11
   -- Rebaja Automatica
   -- alternativo
   ========================================
*/
      Select @Cad = 
		'INSERT #invactual 
			SELECT arti.id_fami,
				smay.id_secc, 
				smay.id_arti,
				smay.fec_oper,
				cantidad = coalesce(reba.cantidad,0),
				importe = coalesce(reba.importe,0)
			FROM stoksmay smay
				INNER JOIN stoktses tses ON smay.id_secc = tses.id_secc
				INNER JOIN stoksrea srea ON srea.id_smay = smay.id_smay
				INNER JOIN stokreba reba ON srea.id_reba = reba.id_reba
				INNER JOIN stokarti arti ON smay.id_arti = arti.id_arti
				INNER JOIN stoktsef tsef ON arti.id_fami = tsef.id_fami
			WHERE smay.id_dmov = 122 ' 
			+ @filtro_fecha 
			+ ' and smay.id_operacion 
			not in (select id_revertido 
						from stokoper 
						where id_revertido is not null and estado=1) '

		EXECUTE (@Cad)

/* ========================================
   Region 12
	-- Crear tabla temporal
   ========================================
*/
	CREATE TABLE #Resum 
	     (  Codigo varchar(50),
			Id_Tgrp int, 
			Id_Tsub int, 
			Descripcion varchar(250), 
			Cantidad numeric(12,4), 
			Importe numeric(12,2), 
			Cantidad_Mes numeric(12,4), 
			Importe_Mes numeric(12,2) )

/* ========================================
   Region 13
	-- para un dia
   ========================================
*/
	if @DiaMes = 1	
		begin
			if @AcuMes = 0
				begin
					Select @CadFields = 
							' cantidad=SUM(i.cantidad), importe=SUM(i.importe), 
							  cantidad_mes=SUM(0), importe_mes=SUM(0) '
				end
			else	-- con acumulado del mes
				begin

					Select @CadFields = 
							' cantidad=SUM(case when Datediff(dd, convert(datetime,"'
							+ convert(varchar(20),@Fecha,101)
							+ '",101), i.fec_oper) = 0 then i.cantidad else 0 end)
							, importe=SUM(case when Datediff(dd, convert(datetime,"'
							+ convert(varchar(20),@Fecha,101)
							+ '",101), i.fec_oper) = 0 then i.importe else 0 end)
							, cantidad_mes=SUM(i.cantidad), importe_mes=SUM(i.importe) '
				end
		end
	else	-- para un mes
		begin
			Select @CadFields = 
					' cantidad=SUM(i.cantidad), importe=SUM(i.importe), 
					  cantidad_mes=SUM(i.cantidad), importe_mes=SUM(i.importe) '
		end

/* ========================================
   Region 14
	@OrderBy = 1
		Secciones
   ========================================
*/
if @OrderBy = 1
	begin
		Select @Cad = 
			' insert #resum 
			     Select codigo=dbo.GetCodigoSeccion(i.id_secc), 
			            g.id_tgrp, id_tsub=MIN(s.id_tsub), 
						descripcion=dbo.GetDescripcionSeccion(i.id_secc), '

		Select @Cad = @Cad + @CadFields

		Select @Cad = @Cad + 
			' from #invactual i  
			       inner join stoktgrf g on i.id_fami = g.id_fami
			       left join stoktsus s on i.id_secc = s.id_secc
			  group by i.id_secc, g.id_tgrp  '
	end

/* ========================================
   Region 15
	@OrderBy = 2
		Grupo-Familia
   ========================================
*/
if @OrderBy = 2
	begin
		Select @Cad = 
			'insert #resum 
				Select codigo=dbo.FullFamiCode(i.id_fami), g.id_tgrp, 
					   id_tsub=MIN(s.id_tsub), descripcion=dbo.GetFamiDescripcion(i.id_fami), '

		Select @Cad = @Cad + @CadFields

		Select @Cad = @Cad 
				+' from #invactual i  
					    inner join stoktgrs g on i.id_secc = g.id_secc
					    left join stoktsuf s on i.id_fami = s.id_fami
			       group by i.id_fami, g.id_tgrp  '
	end

/* ========================================
   Region 16
	@OrderBy = 3
		Articulos
	 dbo.PADR(dbo.GetArtiDescripcion(i.id_arti),28," ")
   ========================================
*/
if @OrderBy = 3
	begin
		Select @Cad = 
			'insert #resum 
				Select codigo=dbo.FullArtiCode(i.id_arti), g.id_tgrp, 
					   id_tsub=MIN(s.id_tsub), 
					   descripcion=  rtrim(dbo.GetArtiDescripcion(i.id_arti))
					   +" - "+ ltrim(dbo.GetUMCode(dbo.GetArtiUmSe(i.id_arti))), '

		Select @Cad = @Cad + @CadFields

		Select @Cad = @Cad 
			+ ' from #invactual i  
			         inner join stoktgrs g on i.id_secc = g.id_secc
		 			 left join stoktsuf s on i.id_fami = s.id_fami
			     group by i.id_arti, g.id_tgrp  '
	end

	Execute(@Cad)

/* ========================================
   Region 17
	-- Calculo de los clientes 
   ========================================
*/
Select @clies_dias = 0, 
       @clies_mes  = 0


if @CliexDias = 1 
   and exists(select * 
                 from stoktpax 
				 where Datediff(yy,@Fecha,fecha) = 0 
				   and Datediff(mm,@Fecha,fecha) = 0 )
	begin
		CREATE TABLE #clies 
		       (clies_dias int,
				clies_mes int )

/* ========================================
   Region 18
	-- para un dia
   ========================================
*/		
		if @DiaMes = 1	-- para un dia
			begin
				if @AcuMes = 0
					begin
						Select @Cad = 
							'insert  #clies 
								Select clies_dias=COALESCE(p.cantidad,0), 
									  clies_mes=0  from stoktpax p where '

						select @filtro_fecha = 
								' Datediff(dd, convert(datetime,"'
								+ convert(varchar(20),@Fecha,101)
								+ '",101), p.fecha) = 0'
					end

				else	-- con acumulado del mes
					begin

						Select @Cad = 
							'insert  #clies 
								Select clies_dias=
								    case when Datediff(dd, convert(datetime,"'
									+ convert(varchar(20),@Fecha,101)
									+ '",101), p.fecha) = 0 
									then COALESCE(p.cantidad,0) 
									else 0 end, clies_mes=COALESCE(p.cantidad,0)  
									from stoktpax p where '

						select @filtro_fecha = 
								' Datediff(yy, convert(datetime,"'
								+ convert(varchar(20),@Fecha,101)
								+ '",101), p.fecha) = 0'

						select @filtro_fecha = 
								@filtro_fecha 
								+ ' and Datediff(mm, p.fecha, convert(datetime,"'
								+ convert(varchar(20),@Fecha,101)
								+ '",101)) = 0'

						select @filtro_fecha = 
								@filtro_fecha 
								+ ' and Datediff(dd, p.fecha, convert(datetime,"'
								+ convert(varchar(20),@Fecha,101)
								+'",101)) >= 0'
					end
			end

/* ========================================
   Region 18
	-- para un mes
   ========================================
*/		
		else	-- para un mes

			begin

				Select @Cad = 
					'insert  #clies 
						Select clies_dias=COALESCE(p.cantidad,0), 
							  clies_mes=0  from stoktpax p where '

				select @filtro_fecha = 
						' Datediff(yy, convert(datetime,"'
						+ convert(varchar(20),@Fecha,101)
						+'",101), p.fecha) = 0'

				select @filtro_fecha = 
						@filtro_fecha 
						+ ' and Datediff(mm, p.fecha, convert(datetime,"'
						+ convert(varchar(20),@Fecha,101)
						+ '",101)) = 0'
			end
		
		Select @Cad = @Cad + @filtro_fecha
		
		Execute(@Cad)
		
		Select @clies_dias = SUM(clies_dias), 
			   @clies_mes  = SUM(clies_mes) 
		   From #clies
		
		drop table #clies
	end


/* ========================================
   Region 19
	-- Apertura en grupos por columnas 
	y totales adicionales en columnas
   ========================================
*/		
	Select @grpcount=0

	Select @Cad = 
	   'Select r.codigo, descripcion=MIN(r.descripcion), 
			   id_tsub=MIN(r.id_tsub), subtotal = MIN(s.descripcion)'

	Create Table #stoktgrp 
	       (Id_Tgrp int,
		    Codigo nvarchar(20))

    Declare @vCant int
	       
--select * from stoktgrp 
    Insert Into #Stoktgrp
	  select id_tgrp, codigo 
		 from stoktgrp 
		 where tipo = case when @OrderBy = 1 
						   then 2 
						   else 1 
						   end 
		order by id_tgrp

	Set @vCant = @@ROWCOUNT + 1
	 
	While @vCant <= 4
	Begin
	  Insert Into #StokTgrp
	    Values(0,'Grupo_' + rtrim(ltrim(str(@vCant))) )
      Set @vCant = @vCant + 1
	End

	declare curgrupo cursor for
		select id_tgrp, codigo 
		 from #StokTgrp 

	 -- select id_tgrp, codigo 
		-- from stoktgrp 
		-- where tipo = case when @OrderBy = 1 
		--				   then 2 
		--				   else 1 
		--				   end 
		--order by id_tgrp

	open curgrupo fetch next 
		from curgrupo into @id_tgrp,@codigo

	while @@fetch_status = 0
		begin

			Select @grpcount = @grpcount + 1

			Select @Cad = 
			   @Cad + ', grupo'
			        + RTRIM(CONVERT(varchar(14),@grpcount))
					+ '_n="'+ @codigo + '"'

			Select @Cad = 
					@Cad + ', SUM(case when r.id_tgrp = '
					     + CONVERT(varchar(14),@id_tgrp)
						 + ' then r.cantidad else 0 end) as grupo'
						 + RTRIM(CONVERT(varchar(14),@grpcount))
						 + '_c'

			Select @Cad = 
					@Cad 
					+ ', SUM(case when r.id_tgrp = '
					+ CONVERT(varchar(14),@id_tgrp)
					+ ' then r.importe else 0 end) as grupo'
					+ RTRIM(CONVERT(varchar(14),@grpcount))
					+'_i'
			fetch next from curgrupo into @id_tgrp,@codigo
		end

	close curgrupo
	deallocate curgrupo

	Select @Cad = @Cad + ', SUM(r.cantidad) as total_c'

	Select @Cad = @Cad + ', SUM(r.importe) as total_i'

	Select @Cad = @Cad + ', SUM(r.cantidad_mes) as mes_c'

	Select @Cad = @Cad + ', SUM(r.importe_mes) as mes_i'

	Select @Cad = 
			@Cad + ', '
			+ CONVERT(varchar(14),@clies_dias)
			+ ' as clies_dias, '
			+ CONVERT(varchar(14),@clies_mes)
			+ ' as clies_mes'

	Select @Cad = 
			@Cad 
			+ ' from #resum r 
					 left join stoktsub s on r.id_tsub = s.id_tsub 
				where r.cantidad != 0 or r.importe != 0  
				group by r.codigo order by id_tsub, codigo '

	Execute(@Cad)

--print @Cad
--select * from #invactual

	drop table #invactual
	drop table #resum

END


