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,
	   @deposito varchar(10) = ''
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)
		)

if(@deposito<>'')
begin

INSERT INTO #MOV_STOCK (ID_PRODUCTO, Descripcion_Producto,STK_INICIAL,COMPRAS,DEVOLUCIONES,VENTAS_DIA,TOTAL_DIA,VENTAS_MES,TOTAL_MES,VARIOS,STK_FINAL) 
       SELECT DISTINCT (p.id_producto),Descripcion_detallada,0,0,0,0,0,0,0,0,0 FROM Productos P,Cbtes C, Cbtes_Items ci 
	   WHERE Activo = 1 
	   AND Maneja_Stock = 1 
	   AND ID_FAMILIA  <> 7
	   AND c.Id_Trans = ci.Id_Trans
	   and ci.Id_Producto = p.Id_Producto
	   --and Id_Tipo_Cbte = 'rem'
	   and c.Id_Dep_Fisico = @deposito
	   ORDER BY  p.id_producto
	   

END

IF(@deposito='')
BEGIN

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 AND ID_FAMILIA  <> 7

END


  DECLARE @id_Producto char(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, PRODUCTOS P
		WHERE I.Id_Trans = C.Id_Trans  AND C.Id_Tipo_Cbte = T.Id_Tipo_Cbte  
		AND C.Id_Estado_Cbte <> 3 
		AND I.Id_Estado_Cbte <> 32 
		AND T.Compromete_Stock in (1,-1) 
		AND P.ID_PRODUCTO = I.ID_PRODUCTO
		AND P.ID_FAMILIA  <> 7
		AND isnull(i.id_producto,'') <> ''  
		AND (C.Fecha <= @FECHA  OR  C.Fecha IS NULL ) 
		AND I.Flag_QA = 'A' 
		GROUP BY I.ID_PRODUCTO  HAVING SUM(I.Cantidad*T.Compromete_Stock) <> 0
		ORDER BY I.ID_PRODUCTO
		
	
	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_STOCK) AS Stock  
		FROM Cbtes_Items I,Cbtes C,Cbtes_Tipos T ,PRODUCTOS P 
		WHERE I.Id_Trans = C.Id_Trans  AND C.Id_Tipo_Cbte = T.Id_Tipo_Cbte  
		AND P.ID_PRODUCTO = I.ID_PRODUCTO
		AND P.ID_FAMILIA  <> 7
		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 isnull(i.id_producto,'') <> ''  
		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
		ORDER BY I.ID_PRODUCTO
	
	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 ,PRODUCTOS P 
		WHERE I.Id_Trans = C.Id_Trans  AND C.Id_Tipo_Cbte = T.Id_Tipo_Cbte
		AND P.ID_PRODUCTO = I.ID_PRODUCTO
		AND P.ID_FAMILIA  <> 7  
		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 isnull(i.id_producto,'') <> ''  
		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
		ORDER BY I.ID_PRODUCTO
	
	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) AS Total 
		 FROM Cbtes_Items I,Cbtes C,Cbtes_Tipos T,PRODUCTOS P  
		WHERE I.Id_Trans = C.Id_Trans  AND C.Id_Tipo_Cbte = T.Id_Tipo_Cbte  
		AND P.ID_PRODUCTO = I.ID_PRODUCTO
		AND P.ID_FAMILIA  <> 7
		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,-1) 
		AND I.Imp_Gravado <> 0
		AND isnull(i.id_producto,'') <> ''  
		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
		ORDER BY I.ID_PRODUCTO
	
	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-DEVOLUCIONES, 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) AS Total 
		 FROM Cbtes_Items I,Cbtes C,Cbtes_Tipos T,PRODUCTOS P  
		WHERE I.Id_Trans = C.Id_Trans  AND C.Id_Tipo_Cbte = T.Id_Tipo_Cbte  
		AND P.ID_PRODUCTO = I.ID_PRODUCTO
		AND P.ID_FAMILIA  <> 7
		AND C.Id_Estado_Cbte <> 3 
		AND I.Id_Estado_Cbte <> 32 
		and i.Imp_Gravado <> 0 
		AND T.Compromete_Stock in (1,-1) 
		AND T.Compromete_factura in (1,-1) 
		AND isnull(i.id_producto,'') <> ''  
		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 
		ORDER BY I.ID_PRODUCTO
	
	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/VARIOS
  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,PRODUCTOS P
		WHERE I.Id_Trans = C.Id_Trans  AND C.Id_Tipo_Cbte = T.Id_Tipo_Cbte  
		AND P.ID_PRODUCTO = I.ID_PRODUCTO
		AND P.ID_FAMILIA  <> 7
		AND C.Id_Estado_Cbte <> 3 
		AND I.Id_Estado_Cbte <> 32 
	    AND T.Compromete_comprAS NOT IN (1,-1) 
		AND T.Compromete_Stock in (1,-1)
		AND ( T.Compromete_FACTURA NOT IN (1,-1) 
 		OR (T.Compromete_FACTURA IN (1,-1) AND I.Imp_Gravado = 0))
		AND isnull(i.id_producto,'') <> ''  
		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
		ORDER BY I.ID_PRODUCTO
	
	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 ,PRODUCTOS P
		WHERE I.Id_Trans = C.Id_Trans  AND C.Id_Tipo_Cbte = T.Id_Tipo_Cbte 
		AND P.ID_PRODUCTO = I.ID_PRODUCTO
		AND P.ID_FAMILIA  <> 7 
		AND C.Id_Estado_Cbte <> 3 
		AND I.Id_Estado_Cbte <> 32 
		AND T.Compromete_Stock in (1,-1) 
		AND isnull(i.id_producto,'') <> ''  
		AND (C.Fecha < @FECHA+1  OR  C.Fecha IS NULL ) 
		AND I.Flag_QA = 'A' 	
		GROUP BY I.ID_PRODUCTO  HAVING SUM(I.Cantidad*T.Compromete_STOCK) <> 0
		ORDER BY I.ID_PRODUCTO
	
	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 '20180612'

