IF EXISTS ( SELECT * 
            FROM   sysobjects 
            WHERE  id = object_id(N'[dbo].[CP_MOV_STOCK]') 
                   and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
BEGIN
    DROP PROCEDURE [dbo].[CP_MOV_STOCK] 
END
GO

CREATE PROCEDURE DBO.[CP_MOV_STOCK] 
       @FECHA DATETIME
AS

CREATE TABLE MOV_STOCK  (
			 ID_PRODUCTO varchar(14)
			,Descripcion_Producto VARCHAR(300)
			,STK_INICIAL  DECIMAL(16,2)
			,COMPRAS  DECIMAL(16,2)
			,DEVOLUCIONES  DECIMAL(16,2)
			,VENTAS_DIA  DECIMAL(16,2)
			,TOTAL_DIA  DECIMAL(16,2)
			,VENTAS_MES  DECIMAL(16,2)
			,TOTAL_MES  DECIMAL(16,2)
			,VARIOS  DECIMAL(16,2)
			,STK_FINAL  DECIMAL(16,2)
		)

INSERT INTO MOV_STOCK (ID_PRODUCTO, Descripcion_Producto,STK_INICIAL,COMPRAS,DEVOLUCIONES,VENTAS_DIA,TOTAL_DIA,VENTAS_MES,TOTAL_MES,VARIOS,STK_FINAL) 
       select id_producto,Descripcion_detallada,0,0,0,0,0,0,0,0,0 from Productos where Activo = 1 AND Maneja_Stock = 1


  Declare @id_Producto VARchar(14)
  Declare @Stock Decimal(17,4)
  Declare @Total Decimal(17,2)
  DECLARE @FECHA_DESDE DATETIME

  SET @FECHA_DESDE = convert(char(6), @FECHA, 112) + '01' 
  PRINT @FECHA_DESDE

--Calculo Stk_Fisico
  Declare Cursor_RecStock Cursor for

		SELECT I.ID_PRODUCTO,SUM(I.Cantidad*T.Compromete_Stock) as Stock 
		 FROM Cbtes_Items I,Cbtes C,Cbtes_Tipos T , MOV_STOCK M 
		WHERE I.Id_Trans = C.Id_Trans  AND C.Id_Tipo_Cbte = T.Id_Tipo_Cbte  
		AND I.ID_PRODUCTO = M.ID_PRODUCTO
		AND C.Id_Estado_Cbte <> 3 
		AND I.Id_Estado_Cbte <> 32 
		AND T.Compromete_Stock in (1,-1) 
		AND (I.Fecha_QA < @FECHA OR  I.Fecha_QA IS NULL ) 
		AND I.Flag_QA = 'A' GROUP BY I.ID_PRODUCTO  having SUM(I.Cantidad*T.Compromete_Stock) <> 0
	
	OPEN Cursor_RecStock

	FETCH NEXT FROM Cursor_RecStock
	INTO @Id_Producto,@Stock

	WHILE @@FETCH_STATUS = 0
	BEGIN

		 update MOV_STOCK set STK_INICIAL = @Stock where  Id_producto = @Id_Producto 

	 	FETCH NEXT FROM Cursor_RecStock
		INTO @Id_Producto, @Stock
	END

	CLOSE Cursor_RecStock
	DEALLOCATE Cursor_RecStock


--Calculo compras
  Declare Cursor_compras Cursor for

		SELECT I.ID_PRODUCTO,SUM(I.Cantidad*T.Compromete_COMPRAS) as Stock 
		 FROM Cbtes_Items I,Cbtes C,Cbtes_Tipos T, MOV_STOCK M
		WHERE I.Id_Trans = C.Id_Trans  AND C.Id_Tipo_Cbte = T.Id_Tipo_Cbte  
		AND M.ID_PRODUCTO = I.ID_PRODUCTO
		AND C.Id_Estado_Cbte <> 3 
		AND I.Id_Estado_Cbte <> 32 
		AND T.Compromete_Stock in (1,-1) 
		AND T.Compromete_compras in (1,-1) 
		AND (C.Fecha >= @FECHA  and C.Fecha < @FECHA+1 ) 
		AND I.Flag_QA = 'A' GROUP BY I.ID_PRODUCTO  having SUM(I.Cantidad*T.Compromete_COMPRAS) <> 0
	
	OPEN Cursor_compras

	FETCH NEXT FROM Cursor_compras
	INTO @Id_Producto,@Stock

	WHILE @@FETCH_STATUS = 0
	BEGIN

		 update MOV_STOCK set Compras = @Stock where  Id_producto = @Id_Producto 

	 	FETCH NEXT FROM Cursor_compras
		INTO @Id_Producto, @Stock
	END

	CLOSE Cursor_compras
	DEALLOCATE Cursor_compras	


--Calculo DEVOLUCIONES
  Declare Cursor_Devo Cursor for

		SELECT I.ID_PRODUCTO,SUM(I.Cantidad*T.Compromete_factura) as Stock 
		 FROM Cbtes_Items I,Cbtes C,Cbtes_Tipos T, MOV_STOCK M  
		WHERE I.Id_Trans = C.Id_Trans  AND C.Id_Tipo_Cbte = T.Id_Tipo_Cbte
		AND M.ID_PRODUCTO = I.ID_PRODUCTO  
		AND C.Id_Estado_Cbte <> 3 
		AND I.Id_Estado_Cbte <> 32 
		AND T.Compromete_Stock in (1,-1) 
		AND T.Compromete_factura in (-1) 
		AND (C.Fecha >= @FECHA  and C.Fecha < @FECHA+1 ) 
		AND I.Flag_QA = 'A' GROUP BY I.ID_PRODUCTO  having SUM(I.Cantidad*T.Compromete_factura) <> 0
	
	OPEN Cursor_Devo

	FETCH NEXT FROM Cursor_Devo
	INTO @Id_Producto,@Stock

	WHILE @@FETCH_STATUS = 0
	BEGIN

		 update MOV_STOCK set DEVOLUCIONES = @Stock where  Id_producto = @Id_Producto 

	 	FETCH NEXT FROM Cursor_Devo
		INTO @Id_Producto, @Stock
	END

	CLOSE Cursor_Devo
	DEALLOCATE Cursor_Devo	

--Calculo Ventas
  Declare Cursor_VENTAS Cursor for

		SELECT I.ID_PRODUCTO,SUM(I.Cantidad*T.Compromete_factura) as Stock,SUM(I.Imp_Gravado*T.Compromete_factura*C.COTIZACION*(1-c.Porc_Bonificacion/100)) as Total 
		 FROM Cbtes_Items I,Cbtes C,Cbtes_Tipos T , MOV_STOCK M 
		WHERE I.Id_Trans = C.Id_Trans  AND C.Id_Tipo_Cbte = T.Id_Tipo_Cbte
		AND M.ID_PRODUCTO = I.ID_PRODUCTO		  
		AND C.Id_Estado_Cbte <> 3 
		AND I.Id_Estado_Cbte <> 32 
		AND T.Compromete_Stock in (1,-1) 
		AND T.Compromete_factura in (1) 
		AND (C.Fecha >= @FECHA  and C.Fecha < @FECHA+1 ) 
		AND I.Flag_QA = 'A' GROUP BY I.ID_PRODUCTO  having SUM(I.Cantidad*T.Compromete_factura) <> 0
	
	OPEN Cursor_VENTAS

	FETCH NEXT FROM Cursor_VENTAS
	INTO @Id_Producto,@Stock,@Total

	WHILE @@FETCH_STATUS = 0
	BEGIN

		 update MOV_STOCK set VENTAS_DIA = @Stock, TOTAL_DIA = @Total where  Id_producto = @Id_Producto 

	 	FETCH NEXT FROM Cursor_VENTAS
		INTO @Id_Producto, @Stock,@Total
	END

	CLOSE Cursor_VENTAS
	DEALLOCATE Cursor_VENTAS	


--Calculo Ventas MES

  Declare Cursor_VENTAS_MES Cursor for
		SELECT I.ID_PRODUCTO,SUM(I.Cantidad*T.Compromete_factura) as Stock,SUM(I.Imp_Gravado*T.Compromete_factura*C.COTIZACION*(1-c.Porc_Bonificacion/100)) as Total 
		 FROM Cbtes_Items I,Cbtes C,Cbtes_Tipos T, MOV_STOCK M  
		WHERE I.Id_Trans = C.Id_Trans  AND C.Id_Tipo_Cbte = T.Id_Tipo_Cbte
		AND M.ID_PRODUCTO = I.ID_PRODUCTO		  
		AND C.Id_Estado_Cbte <> 3 
		AND I.Id_Estado_Cbte <> 32 
		AND T.Compromete_Stock in (1,-1) 
		AND T.Compromete_factura in (1) 
		AND (C.Fecha >= @FECHA_DESDE  and C.Fecha < @FECHA+1 ) 
		AND I.Flag_QA = 'A' GROUP BY I.ID_PRODUCTO  having SUM(I.Cantidad*T.Compromete_factura) <> 0 
	
	OPEN Cursor_VENTAS_MES

	FETCH NEXT FROM Cursor_VENTAS_MES
	INTO @Id_Producto,@Stock,@Total

	WHILE @@FETCH_STATUS = 0
	BEGIN

		 update MOV_STOCK set VENTAS_MES = @Stock, TOTAL_MES = @Total where  Id_producto = @Id_Producto 

	 	FETCH NEXT FROM Cursor_VENTAS_MES
		INTO @Id_Producto, @Stock,@Total
	END

	CLOSE Cursor_VENTAS_MES
	DEALLOCATE Cursor_VENTAS_MES	

--Calculo OTROS
  Declare Cursor_OTROS Cursor for

		SELECT I.ID_PRODUCTO,SUM(I.Cantidad*T.Compromete_STOCK) as Stock 
		 FROM Cbtes_Items I,Cbtes C,Cbtes_Tipos T, MOV_STOCK M  
		WHERE I.Id_Trans = C.Id_Trans  AND C.Id_Tipo_Cbte = T.Id_Tipo_Cbte  
		AND M.ID_PRODUCTO = I.ID_PRODUCTO
		AND C.Id_Estado_Cbte <> 3 
		AND I.Id_Estado_Cbte <> 32 
		AND T.Compromete_Stock in (1,-1) 
		AND T.Compromete_compras NOT IN (1,-1) 
		AND T.Compromete_FACTURA NOT IN (1,-1) 
		AND (C.Fecha >= @FECHA  and C.Fecha < @FECHA+1 ) 
		AND I.Flag_QA = 'A' GROUP BY I.ID_PRODUCTO  having SUM(I.Cantidad*T.Compromete_STOCK) <> 0
	
	OPEN Cursor_OTROS

	FETCH NEXT FROM Cursor_OTROS
	INTO @Id_Producto,@Stock

	WHILE @@FETCH_STATUS = 0
	BEGIN

		 update MOV_STOCK set VARIOS = @Stock where  Id_producto = @Id_Producto 

	 	FETCH NEXT FROM Cursor_OTROS
		INTO @Id_Producto, @Stock
	END

	CLOSE Cursor_OTROS
	DEALLOCATE Cursor_OTROS	

--Calculo STK_ACTUAL
  Declare Cursor_STK_ACTUAL Cursor for

		SELECT I.ID_PRODUCTO,SUM(I.Cantidad*T.Compromete_STOCK) as Stock 
		 FROM Cbtes_Items I,Cbtes C,Cbtes_Tipos T, MOV_STOCK M 
		WHERE I.Id_Trans = C.Id_Trans  AND C.Id_Tipo_Cbte = T.Id_Tipo_Cbte  
		AND M.ID_PRODUCTO = I.ID_PRODUCTO
		AND C.Id_Estado_Cbte <> 3 
		AND I.Id_Estado_Cbte <> 32 
		AND T.Compromete_Stock in (1,-1) 
		AND (I.Fecha_QA < @FECHA+1  OR  I.Fecha_QA IS NULL ) 
		AND I.Flag_QA = 'A' GROUP BY I.ID_PRODUCTO  having SUM(I.Cantidad*T.Compromete_STOCK) <> 0
	
	OPEN Cursor_STK_ACTUAL

	FETCH NEXT FROM Cursor_STK_ACTUAL
	INTO @Id_Producto,@Stock

	WHILE @@FETCH_STATUS = 0
	BEGIN

		 update MOV_STOCK set STK_FINAL = @Stock where  Id_producto = @Id_Producto 

	 	FETCH NEXT FROM Cursor_STK_ACTUAL
		INTO @Id_Producto, @Stock
	END

	CLOSE Cursor_STK_ACTUAL
	DEALLOCATE Cursor_STK_ACTUAL	


select * from MOV_STOCK

drop table MOV_STOCK



-- exec CP_MOV_STOCK '20170612'