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

CREATE PROCEDURE DBO.[CP_VTAS_POR_ZONA] 
        @FECHA_DESDE DATETIME
	   ,@FECHA_HASTA DATETIME
	   ,@ID_PROVINCIA CHAR(3)
AS

CREATE TABLE VTAS_ZONA  (
			 ID_ZONA varchar(5)
			,Descripcion VARCHAR(300)
			,SDO_INICIAL  DECIMAL(16,2)
			,FACTURAS  DECIMAL(16,2)
			,DEBITOS  DECIMAL(16,2)
			,CREDITOS  DECIMAL(16,2)
			,COBRANZAS  DECIMAL(16,2)
			,DESCUENTOS  DECIMAL(16,2)
			,OTROS  DECIMAL(16,2)
			,RETENCIONES  DECIMAL(16,2)
			,SDO_FINAL  DECIMAL(16,2)
		)

INSERT INTO VTAS_ZONA (ID_ZONA, Descripcion,SDO_INICIAL,FACTURAS,DEBITOS,CREDITOS,COBRANZAS,DESCUENTOS,OTROS,RETENCIONES,SDO_FINAL) 
       select Id_ZONA,Descripcion,0,0,0,0,0,0,0,0,0 from Tab_Zonas  


  Declare @id_ZONA VARchar(5)
  Declare @Stock Decimal(17,4)
  Declare @Total Decimal(17,2)


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


--Calculo FACTURAS
  Declare Cursor_FACTURAS Cursor for

		SELECT I.ID_ZONA,SUM(C.IMP_TOTAL*C.COTIZACION*T.Compromete_FACTURA) as TOTAL 
		 FROM CLIENTES I,Cbtes C,Cbtes_Tipos T , VTAS_ZONA M 
		WHERE I.Id_CLIENTE = C.Id_CLIENTE  AND C.Id_Tipo_Cbte = T.Id_Tipo_Cbte  
		AND I.ID_ZONA = M.ID_ZONA
		AND C.Id_Estado_Cbte <> 3 
		AND T.Compromete_FACTURA in (1) 
		AND (C.Fecha >= @FECHA_DESDE  and C.Fecha < @FECHA_HASTA +1 ) 
		AND T.Id_tributario in ('01','06','11')
        GROUP BY I.ID_ZONA  having SUM(C.IMP_TOTAL*T.Compromete_FACTURA) <> 0
	
	OPEN Cursor_FACTURAS

	FETCH NEXT FROM Cursor_FACTURAS
	INTO @Id_ZONA,@TOTAL

	WHILE @@FETCH_STATUS = 0
	BEGIN

		 update VTAS_ZONA set FACTURAS = @TOTAL where  ID_ZONA = @Id_ZONA

	 	FETCH NEXT FROM Cursor_FACTURAS
		INTO @Id_ZONA,@TOTAL
	END

	CLOSE Cursor_FACTURAS
	DEALLOCATE Cursor_FACTURAS


--Calculo DEBITOS
  Declare Cursor_DEBITOS Cursor for

		SELECT I.ID_ZONA,SUM(C.IMP_TOTAL*C.COTIZACION*T.Compromete_FACTURA) as TOTAL 
		 FROM CLIENTES I,Cbtes C,Cbtes_Tipos T , VTAS_ZONA M 
		WHERE I.Id_CLIENTE = C.Id_CLIENTE  AND C.Id_Tipo_Cbte = T.Id_Tipo_Cbte  
		AND I.ID_ZONA = M.ID_ZONA
		AND C.Id_Estado_Cbte <> 3 
		AND T.Compromete_FACTURA in (1) 
		AND (C.Fecha >= @FECHA_DESDE  and C.Fecha < @FECHA_HASTA+1 ) 
		AND T.Id_tributario NOT in ('01','06','11')
        GROUP BY I.ID_ZONA  having SUM(C.IMP_TOTAL*T.Compromete_FACTURA) <> 0
	
	OPEN Cursor_DEBITOS

	FETCH NEXT FROM Cursor_DEBITOS
	INTO @Id_ZONA,@TOTAL

	WHILE @@FETCH_STATUS = 0
	BEGIN

		 update VTAS_ZONA set DEBITOS = @TOTAL where  ID_ZONA = @Id_ZONA

	 	FETCH NEXT FROM Cursor_DEBITOS
		INTO @Id_ZONA,@TOTAL
	END

	CLOSE Cursor_DEBITOS
	DEALLOCATE Cursor_DEBITOS


--Calculo CREDITOS
  Declare Cursor_CREDITOS Cursor for

		SELECT I.ID_ZONA,SUM(C.IMP_TOTAL*C.COTIZACION) as TOTAL 
		 FROM CLIENTES I,Cbtes C,Cbtes_Tipos T , VTAS_ZONA M 
		WHERE I.Id_CLIENTE = C.Id_CLIENTE  AND C.Id_Tipo_Cbte = T.Id_Tipo_Cbte  
		AND I.ID_ZONA = M.ID_ZONA
		AND C.Id_Estado_Cbte <> 3 
		AND T.Compromete_FACTURA in (-1) 
		AND (C.Fecha >= @FECHA_DESDE  and C.Fecha < @FECHA_HASTA +1 ) 
        GROUP BY I.ID_ZONA  having SUM(C.IMP_TOTAL) <> 0
	
	OPEN Cursor_CREDITOS

	FETCH NEXT FROM Cursor_CREDITOS
	INTO @Id_ZONA,@TOTAL

	WHILE @@FETCH_STATUS = 0
	BEGIN

		 update VTAS_ZONA set CREDITOS = @TOTAL where  ID_ZONA = @Id_ZONA

	 	FETCH NEXT FROM Cursor_CREDITOS
		INTO @Id_ZONA,@TOTAL
	END

	CLOSE Cursor_CREDITOS
	DEALLOCATE Cursor_CREDITOS

--Calculo Recibos
  Declare Cursor_Recibos Cursor for

		SELECT I.ID_ZONA,SUM(C.IMP_TOTAL*C.COTIZACION) as TOTAL 
		 FROM CLIENTES I,Cbtes C,Cbtes_Tipos T , VTAS_ZONA M 
		WHERE I.Id_CLIENTE = C.Id_CLIENTE  AND C.Id_Tipo_Cbte = T.Id_Tipo_Cbte  
		AND I.ID_ZONA = M.ID_ZONA
		AND C.Id_Estado_Cbte <> 3 
		AND T.Compromete_RECIBOS in (1,-1) 
		AND (C.Fecha >= @FECHA_DESDE  and C.Fecha < @FECHA_HASTA +1 ) 
        GROUP BY I.ID_ZONA  having SUM(C.IMP_TOTAL) <> 0
	
	OPEN Cursor_Recibos

	FETCH NEXT FROM Cursor_Recibos
	INTO @Id_ZONA,@TOTAL

	WHILE @@FETCH_STATUS = 0
	BEGIN

		 update VTAS_ZONA set COBRANZAS = @TOTAL where  ID_ZONA = @Id_ZONA

	 	FETCH NEXT FROM Cursor_Recibos
		INTO @Id_ZONA,@TOTAL
	END

	CLOSE Cursor_Recibos
	DEALLOCATE Cursor_Recibos

--Calculo CREDITOS PARA EL SALDO ANTERIOR
  Declare Cursor_Recibos_SI Cursor for

		SELECT I.ID_ZONA,SUM(C.IMP_TOTAL*C.COTIZACION) as TOTAL 
		 FROM CLIENTES I,Cbtes C,Cbtes_Tipos T , VTAS_ZONA M 
		WHERE I.Id_CLIENTE = C.Id_CLIENTE  AND C.Id_Tipo_Cbte = T.Id_Tipo_Cbte  
		AND I.ID_ZONA = M.ID_ZONA
		AND C.Id_Estado_Cbte <> 3 
		AND (T.Compromete_RECIBOS in (1,-1) OR T.Compromete_FACTURA IN (-1)) 
		AND C.Fecha <  @FECHA_DESDE
        GROUP BY I.ID_ZONA  having SUM(C.IMP_TOTAL) <> 0
	
	OPEN Cursor_Recibos_SI

	FETCH NEXT FROM Cursor_Recibos_SI
	INTO @Id_ZONA,@TOTAL

	WHILE @@FETCH_STATUS = 0
	BEGIN

		 update VTAS_ZONA set SDO_INICIAL = SDO_INICIAL - @TOTAL where  ID_ZONA = @Id_ZONA

	 	FETCH NEXT FROM Cursor_Recibos_SI
		INTO @Id_ZONA,@TOTAL
	END

	CLOSE Cursor_Recibos_SI
	DEALLOCATE Cursor_Recibos_SI

--Calculo DEBITOS PARA EL SALDO ANTERIOR
  Declare Cursor_DEBITOS_SI Cursor for

		SELECT I.ID_ZONA,SUM(C.IMP_TOTAL*C.COTIZACION) as TOTAL 
		 FROM CLIENTES I,Cbtes C,Cbtes_Tipos T , VTAS_ZONA M 
		WHERE I.Id_CLIENTE = C.Id_CLIENTE  AND C.Id_Tipo_Cbte = T.Id_Tipo_Cbte  
		AND I.ID_ZONA = M.ID_ZONA
		AND C.Id_Estado_Cbte <> 3 
		AND  T.Compromete_FACTURA IN (1) 
		AND C.Fecha <  @FECHA_DESDE
        GROUP BY I.ID_ZONA  having SUM(C.IMP_TOTAL) <> 0
	
	OPEN Cursor_DEBITOS_SI

	FETCH NEXT FROM Cursor_DEBITOS_SI
	INTO @Id_ZONA,@TOTAL

	WHILE @@FETCH_STATUS = 0
	BEGIN

		 update VTAS_ZONA set SDO_INICIAL = SDO_INICIAL + @TOTAL where  ID_ZONA = @Id_ZONA

	 	FETCH NEXT FROM Cursor_DEBITOS_SI
		INTO @Id_ZONA,@TOTAL
	END

	CLOSE Cursor_DEBITOS_SI
	DEALLOCATE Cursor_DEBITOS_SI

--Calculo CREDITOS PARA EL SALDO ANTERIOR
  Declare Cursor_Recibos_SA Cursor for

		SELECT I.ID_ZONA,SUM(C.IMP_TOTAL*C.COTIZACION) as TOTAL 
		 FROM CLIENTES I,Cbtes C,Cbtes_Tipos T , VTAS_ZONA M 
		WHERE I.Id_CLIENTE = C.Id_CLIENTE  AND C.Id_Tipo_Cbte = T.Id_Tipo_Cbte  
		AND I.ID_ZONA = M.ID_ZONA
		AND C.Id_Estado_Cbte <> 3 
		AND (T.Compromete_RECIBOS in (1,-1) OR T.Compromete_FACTURA IN (-1)) 
		AND C.Fecha < @FECHA_HASTA+1
        GROUP BY I.ID_ZONA  having SUM(C.IMP_TOTAL) <> 0
	
	OPEN Cursor_Recibos_SA

	FETCH NEXT FROM Cursor_Recibos_SA
	INTO @Id_ZONA,@TOTAL

	WHILE @@FETCH_STATUS = 0
	BEGIN

		 update VTAS_ZONA set SDO_FINAL = SDO_FINAL - @TOTAL where  ID_ZONA = @Id_ZONA

	 	FETCH NEXT FROM Cursor_Recibos_SA
		INTO @Id_ZONA,@TOTAL
	END

	CLOSE Cursor_Recibos_SA
	DEALLOCATE Cursor_Recibos_SA

--Calculo DEBITOS PARA EL SALDO ANTERIOR
  Declare Cursor_DEBITOS_SA Cursor for

		SELECT I.ID_ZONA,SUM(C.IMP_TOTAL*C.COTIZACION) as TOTAL 
		 FROM CLIENTES I,Cbtes C,Cbtes_Tipos T , VTAS_ZONA M 
		WHERE I.Id_CLIENTE = C.Id_CLIENTE  AND C.Id_Tipo_Cbte = T.Id_Tipo_Cbte  
		AND I.ID_ZONA = M.ID_ZONA
		AND C.Id_Estado_Cbte <> 3 
		AND  T.Compromete_FACTURA IN (1) 
		AND C.Fecha < @FECHA_HASTA+1
        GROUP BY I.ID_ZONA  having SUM(C.IMP_TOTAL) <> 0
	
	OPEN Cursor_DEBITOS_SA

	FETCH NEXT FROM Cursor_DEBITOS_SA
	INTO @Id_ZONA,@TOTAL

	WHILE @@FETCH_STATUS = 0
	BEGIN

		 update VTAS_ZONA set SDO_FINAL = SDO_FINAL + @TOTAL where  ID_ZONA = @Id_ZONA

	 	FETCH NEXT FROM Cursor_DEBITOS_SA
		INTO @Id_ZONA,@TOTAL
	END

	CLOSE Cursor_DEBITOS_SA
	DEALLOCATE Cursor_DEBITOS_SA

select * from VTAS_ZONA

drop table VTAS_ZONA



-- exec [CP_VTAS_POR_ZONA] '20170101','20171231','1'

-- SELECT *,SDO_INICIAL+FACTURAS+DEBITOS-CREDITOS-COBRANZAS-SDO_FINAL FROM VTAS_ZONA WHERE SDO_INICIAL+FACTURAS+DEBITOS-CREDITOS-COBRANZAS<>SDO_FINAL


