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

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


CREATE PROCEDURE [dbo].[CP_Cbtes_Aplic_Automat_Cli] (
			@pId_Tipo_Cbte_NC CHAR(4)
			,@pFecha DATETIME
			,@pId_Tipo_Cbte_A_Cancelar CHAR(4)
			,@pId_Tipo_Cbte_Rec CHAR(4)
			,@pId_Periodo_Caja INT
			,@pLote VARCHAR(20)
			,@pLote_Descrip VARCHAR(50)
			,@pId_Lote_Tipo SMALLINT
			,@pId_Producto_Devolucion VARCHAR(14)
			,@pId_Moneda CHAR(3)
			,@pCotizacion DECIMAL(12,4)
			,@pProvisorio TINYINT
			)
AS

SET NOCOUNT ON

IF ISNULL(@pProvisorio,0) = 0
BEGIN
	IF EXISTS(SELECT 1 FROM Cbtes_Lotes WHERE Lote = @pLote)
	BEGIN
		RETURN 1
	END
	ELSE
	BEGIN
		INSERT INTO Cbtes_Lotes VALUES(@pLote,@pFecha,NULL,NULL,@pLote_Descrip,@pId_Lote_Tipo,0,0,'',0,NULL,NULL,NULL,NULL,NULL)
	END
END

DECLARE @Id_Producto Varchar(14)
		  ,@Id_Proveedor Int
		  ,@Razon_Social VarChar(100)
		  ,@Cuit Varchar(13)
		  ,@Id_Iva Char(3)
		  ,@Id_Alias_Cta_Cont Int
		  ,@Id_Cta_Contable_H Int
		  ,@Porc_Comision_Prov Decimal(7,2) 
		  ,@Importe Decimal(12,2) 
		  ,@IMPORTE_CREDITO Decimal(12,2) 
		  ,@Imp_prod_siva  Decimal(12,2) 
		  ,@ID_TRANS_NC INT
		  ,@ID_TRANS_fac INT
		  ,@Id_Cliente_Externo VARCHAR(14)
		  ,@Id_Cta_Banco INT
		  ,@Id_Cliente INT
		  ,@Plazo    INT
		  ,@PORCENTAJE DECIMAL(12,2)
		  ,@Descripcion_Producto VarChar(200)
		  ,@CANTIDAD INT
		  ,@Precio DECIMAL(12,2)
		  ,@Saldo_NC DECIMAL(12,2)
		  ,@Saldo_Fac DECIMAL(12,2)
		  ,@ID_ESTADO_NC SMALLINT
		  ,@Trans_REC INT
		  ,@ErrorNumber INT
		  ,@Cli_Ref_CtaCte_Agrup INT

SET @Cli_Ref_CtaCte_Agrup = ISNULL(CAST(dbo.CF_xParam('Cli_Ref_CtaCte_Agrup') AS INT),0)

/* Tablas auxiliares -------------------------------------------------------------------------*/ 
 CREATE TABLE #Tab_Result (
		Id_Cliente INT
		,Id_Trans_NC INT
		,Saldo_NC DECIMAL(16,2)
		,Id_Trans_Fac INT
		,Saldo_Fac DECIMAL(16,2)
		,Importe_Aplic DECIMAL(16,2)
		)
CREATE INDEX IX_Tab_Result ON #Tab_Result (Id_Trans_NC,Id_Trans_Fac)

CREATE TABLE #Nc (
	Id_Cliente INT
	,Id_trans INT
	,Saldo DECIMAL(16,2)
	,Id_Cliente_Tit INT
	)

CREATE TABLE #Fac_Cli (
	Id_Cliente INT
	)


CREATE TABLE #Fac (
	Id_Cliente INT
	,Id_trans INT
	,Saldo DECIMAL(16,2)
	,Id_Cliente_Tit INT
	)

/* Cargo las NC -----------------------------------------------------------------------------*/
INSERT INTO #Nc
		SELECT  ID_CLIENTE,ID_TRANS,SALDO,NULL 
			FROM CBTES 
			WHERE ID_TIPO_CBTE = @pID_TIPO_CBTE_NC AND ID_ESTADO_CBTE IN (1,4) 

CREATE INDEX IX_NC ON #NC (Id_Cliente_Tit,Id_Trans)

/* Completo el titular*/

IF (@Cli_Ref_CtaCte_Agrup <> 0)
BEGIN
	UPDATE #Nc SET Id_Cliente_Tit = C.Id_Cliente_Ref
			FROM #NC Nc, Clientes C
			WHERE C.Id_Cliente = NC.Id_Cliente
			AND ISNULL(C.Id_Cliente_Ref,0) <> 0
END
UPDATE #NC SET Id_Cliente_Tit = Id_Cliente WHERE ISNULL(Id_Cliente_Tit,0) = 0


/*Indentifico los clientes para cargar las facturas--------------------------------------*/
INSERT INTO #Fac_Cli
		SELECT DISTINCT(Id_Cliente_Tit) FROM #Nc

IF (@Cli_Ref_CtaCte_Agrup <> 0)              --Agrego a la lista los integrantes del grupo
BEGIN
	INSERT INTO #Fac_Cli
			SELECT DISTINCT(C.Id_Cliente) FROM Clientes C, #Nc Nc 
				WHERE C.Id_Cliente_Ref = Nc.Id_Cliente_Tit
				AND NOT EXISTS (SELECT 1 FROM #Fac_Cli F WHERE C.id_Cliente = F.Id_Cliente)
END

/*--------------------------------------------------------------------------------------------*/
/*
IF ISNULL(@pProvisorio,0) <> 0
BEGIN
	SELECT 'Total de NC '+ CAST( (SELECT COUNT(*) FROM #NC ) AS VARCHAR(10)) 
		, 'Total de NC Tit '+ CAST( (SELECT COUNT(*) FROM #NC WHERE Id_cLiente = Id_Cliente_Tit) AS VARCHAR(10) )
		, 'Total de NC Int '+ CAST( (SELECT COUNT(*) FROM #NC WHERE Id_cLiente <> Id_Cliente_Tit ) AS VARCHAR(10) )
	--SELECT * FROM #NC WHERE Id_cLiente = Id_Cliente_Tit ORDER BY Id_Cliente_Tit,Id_Cliente 
	--SELECT * FROM #NC WHERE Id_cLiente <> Id_Cliente_Tit ORDER BY Id_Cliente_Tit,Id_Cliente 
	--SELECT COUNT(*) AS Cant_NC FROM #NC 
	--SELECT DISTINCT(Id_Cliente_Tit) FROM #Nc
	--SELECT DISTINCT(C.Id_Cliente) FROM Clientes C, #Nc Nc WHERE C.Id_Cliente_Ref = Nc.Id_Cliente_Tit
END
*/
/* Cargo la lista de facturas a cancelar----------------------------------------------------------------------------------*/

INSERT INTO #Fac
SELECT C.Id_Cliente ,C.ID_TRANS, C.SALDO, E.Id_Cliente_Ref
					FROM CBTES C, CBTES_TIPOS T,#Fac_Cli F, Clientes E
					WHERE C.ID_TIPO_CBTE = T.ID_TIPO_CBTE
						AND  T.COMPROMETE_FACTURA = 1 
						AND C.ID_ESTADO_CBTE IN (1,4) 
						AND C.ID_COND_PAGO <> 1 
						AND C.ID_TIPO_CBTE = @pId_Tipo_Cbte_A_Cancelar
						AND C.ID_CLIENTE = F.Id_Cliente
						AND E.Id_Cliente = F.Id_Cliente


IF (@Cli_Ref_CtaCte_Agrup <> 0)    
BEGIN
	UPDATE #Fac SET Id_Cliente_Tit = Id_Cliente WHERE ISNULL(Id_Cliente_Tit,0) = 0  -- agrupa cta.cte completo el titular faltante
END
ELSE
BEGIN
	UPDATE #Fac SET Id_Cliente_Tit = Id_Cliente    --sino no agrupa reescribo el titular
END

UPDATE #Fac SET Id_Cliente_Tit = Id_Cliente WHERE ISNULL(Id_Cliente_Tit,0) = 0

/*--------------------------------------------------------------------------------------------*/
/*						
IF ISNULL(@pProvisorio,0) <> 0
BEGIN
	SELECT 'Total de Fac '+ CAST( (SELECT COUNT(*) FROM #Fac ) AS VARCHAR(10) )
			,'Total de Fac Tit '+ CAST( (SELECT COUNT(*) FROM #Fac WHERE Id_cLiente = Id_Cliente_Tit) AS VARCHAR(10) )
			,'Total de Fac Int '+ CAST( (SELECT COUNT(*) FROM #Fac WHERE Id_cLiente <> Id_Cliente_Tit ) AS VARCHAR(10) )
	--SELECT * FROM #Fac WHERE Id_cLiente = Id_Cliente_Tit ORDER BY Id_Cliente_Tit,Id_Cliente
	--SELECT * FROM #Fac WHERE Id_cLiente <> Id_Cliente_Tit ORDER BY Id_Cliente_Tit,Id_Cliente
	--SELECT COUNT(*) AS Facturas FROM #Fac 
END
*/
/*--------------------------------------------------------------------------------------------*/


DECLARE CUR_GENERAR CURSOR FOR SELECT  ID_CLIENTE_Tit,ID_TRANS,SALDO FROM #NC ORDER BY Id_Cliente_Tit,Id_Trans
        
SET @ErrorNumber = 0

OPEN CUR_GENERAR
FETCH NEXT FROM CUR_GENERAR INTO  @Id_Cliente,@ID_TRANS_NC,@SALDO_NC
WHILE @@FETCH_STATUS = 0 AND @ErrorNumber = 0
BEGIN
      --PRINT 'Cliente: ' + CAST(@Id_Cliente AS VARCHAR(10)) + ' Trans_NC ' + CAST(@ID_TRANS_NC AS VARCHAR(10)) + ' Saldo_NC: ' + CAST(@SALDO_NC AS VARCHAR(10))
      DECLARE CUR_GENERAR_DETALLE CURSOR FOR SELECT ID_TRANS FROM #Fac WHERE Id_Cliente_Tit = @Id_Cliente ORDER BY Id_Trans

	  OPEN CUR_GENERAR_DETALLE
	  FETCH NEXT FROM CUR_GENERAR_DETALLE INTO  @Id_Trans_FAC
	  
	  WHILE @@FETCH_STATUS = 0 AND @ErrorNumber = 0
	  BEGIN
			/* cargo el saldo*/
			IF ISNULL(@pProvisorio,0) = 0
			BEGIN
				SET @Saldo_Fac = ISNULL((SELECT Saldo FROM Cbtes WHERE Id_Trans = @Id_Trans_FAC AND Id_Estado_Cbte IN (1,4)),0)
			END
			ELSE
			BEGIN
				SET @Saldo_Fac = ISNULL((SELECT Saldo FROM #Fac WHERE Id_Trans = @Id_Trans_FAC),0)
			END

			IF @SALDO_NC > 0 AND @Saldo_Fac > 0
			BEGIN
				IF @SALDO_NC > @SALDO_FAC
				BEGIN
					SET @IMPORTE =  @SALDO_FAC
					SET @SALDO_NC = @SALDO_NC - @SALDO_FAC
					INSERT INTO #Tab_Result VALUES (@Id_Cliente,@ID_TRANS_NC,@SALDO_NC + @SALDO_FAC,@Id_Trans_FAC,@Saldo_Fac,@IMPORTE)
				END
				ELSE
				BEGIN
					SET @IMPORTE =  @SALDO_NC
					SET @SALDO_NC = 0
					INSERT INTO #Tab_Result VALUES (@Id_Cliente,@ID_TRANS_NC,@IMPORTE,@Id_Trans_FAC,@Saldo_Fac,@IMPORTE)
				END
				--PRINT' Trans_FC ' + CAST(@Id_Trans_FAC AS VARCHAR(10)) + ' Saldo_Fac: ' + CAST(@Saldo_Fac AS VARCHAR(10))
				

				IF ISNULL(@pProvisorio,0) = 0
				BEGIN

					EXEC @ErrorNumber = CP_Cbtes_Rec_Generar -1,@Id_Cliente,' ',@pId_Periodo_Caja,@pFECHA,0,@pId_Tipo_Cbte_Rec,@pId_Moneda,@pCotizacion,@pLote
					IF ISNULL(@ErrorNumber,0) <> 0
						BREAK

					SET  @Trans_Rec = (SELECT max(ID_Trans) FROM Cbtes WHERE Lote = @pLote)

					EXEC @ErrorNumber = CP_Cbtes_Rec_Generar_Item @Trans_Rec,1,@pId_Producto_Devolucion,1,@Id_Trans_FAC,@IMPORTE
					IF ISNULL(@ErrorNumber,0) <> 0
						BREAK

					SET @IMPORTE = @IMPORTE * -1
					EXEC @ErrorNumber = CP_Cbtes_Rec_Generar_Item @Trans_REC,2,@pId_Producto_Devolucion,1,@ID_TRANS_NC,@IMPORTE 
					IF ISNULL(@ErrorNumber,0) <> 0
						BREAK

					EXEC @ErrorNumber = CP_Cbtes_Rec_Fin @Trans_REC
					IF ISNULL(@ErrorNumber,0) <> 0
						BREAK
				END
				ELSE
				BEGIN 
					/*cuando es provisorio, recalculo el saldo de la tabla temporal de facturas para el informe final */
					UPDATE #Fac SET Saldo = @Saldo_Fac - @IMPORTE WHERE Id_trans = @Id_Trans_FAC
				END
				
        	END 
			FETCH NEXT FROM CUR_GENERAR_DETALLE INTO  @Id_Trans_FAC
	 END  
	 CLOSE CUR_GENERAR_DETALLE
	 DEALLOCATE CUR_GENERAR_DETALLE

	 FETCH NEXT FROM CUR_GENERAR INTO  @Id_Cliente,@ID_TRANS_NC,@SALDO_NC
END
CLOSE CUR_GENERAR
DEALLOCATE CUR_GENERAR 



IF ISNULL(@pProvisorio,0) = 0
BEGIN
		UPDATE Cbtes_Lotes	SET Cantidad = (SELECT COUNT(*) FROM Cbtes WHERE Lote = @pLote),
					Importe = (SELECT SUM(Imp_total) FROM Cbtes WHERE Lote = @pLote )
					WHERE Lote = @pLote
END

SELECT T.*,NC.Fecha AS Fecha_NC , FC.Fecha AS Fecha_Fac  FROM  #Tab_Result T, Cbtes NC, Cbtes FC
					WHERE NC.Id_Trans = T.Id_Trans_NC
					AND FC.Id_Trans = T.Id_Trans_Fac

DROP TABLE #Tab_Result 
DROP TABLE #Nc 
DROP TABLE #Fac_Cli
DROP TABLE #Fac



-- EXEC CP_Generar_Recibo_Aplicacion_Automatica 'NCL','20181228','CUP','REC',7904,'REC-LIB2018-4','Aplicación NC Libertadores 2018-2',6,'DEV','PES',1,1
  






  
    





