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


CREATE  PROCEDURE [dbo].[CPJ_AR_Padron_IIBB_Import] @pJson VARCHAR(8000)
AS


DECLARE  @Id_Tipo_Reten VARCHAR(10)
		,@Archivo VARCHAR(1000)
		,@Tipo CHAR(1)
		,@sql NVARCHAR(4000)
		,@cant INT

SET @Id_Tipo_Reten	= ISNULL(CONVERT(VARCHAR(10)	, dbo.CF_JSON_VALUE(@pJson,'Id_Tipo_Reten')),'')
SET @Archivo		= ISNULL(CONVERT(VARCHAR(1000)	, dbo.CF_JSON_VALUE(@pJson,'Archivo')), '')
SET @Tipo			= ISNULL(CONVERT(CHAR(1)		, dbo.CF_JSON_VALUE(@pJson,'Tipo')), '')

IF @Id_Tipo_Reten = ''  RETURN
IF @Archivo = ''		RETURN
IF @Tipo = ''			RETURN

DECLARE @Mensaje			VARCHAR(100)
BEGIN TRY
BEGIN TRAN
 

CREATE TABLE #AR_Padron_IIBB_Temp (Linea VARCHAR(500) )
SET @sql = 'BULK INSERT #AR_Padron_IIBB_Temp
		FROM '''+ @Archivo + '''
		WITH ( DATAFILETYPE =''char'' ,FIELDTERMINATOR= ''|'', ROWTERMINATOR = ''0x0a'' );'

EXEC (@sql)

DELETE AR_Padron_IIBB WHERE Id_Tipo_Reten = @Id_Tipo_Reten
INSERT INTO AR_Padron_IIBB
	SELECT CAST(SUBSTRING(Linea,30,11) AS VARCHAR(19))
			,@Id_Tipo_Reten,GETDATE()
			,0
			,CAST(SUBSTRING(Linea,48,1) +'.' + SUBSTRING(Linea,50,2) AS DECIMAL(7,2))
			FROM #AR_Padron_IIBB_Temp


IF UPPER(@Tipo) = 'P'
BEGIN
	DELETE FROM Clientes_Reten WHERE Id_Tipo_Reten = @Id_Tipo_Reten
	INSERT INTO Clientes_Reten
		SELECT B.Id_Cliente , @Id_Tipo_Reten,A.Porc,A.Coef 
				FROM [dbo].[AR_Padron_IIBB] A, Clientes B 
				WHERE A.Cuit = B.Cuit AND A.Id_Tipo_Reten = @Id_Tipo_Reten
   SELECT @cant = COUNT(*) FROM Clientes_Reten WHERE Id_Tipo_Reten = @Id_Tipo_Reten

END 

IF UPPER(@Tipo) = 'R'
BEGIN
	DELETE FROM Proveedores_Reten where Id_Tipo_Reten = @Id_Tipo_Reten
	INSERT INTO Proveedores_Reten
		SELECT B.Id_Proveedor , @Id_Tipo_Reten,A.Porc,A.Coef 
				FROM [dbo].[AR_Padron_IIBB] A, Proveedores B 
				WHERE A.Cuit = B.Cuit AND A.Id_Tipo_Reten = @Id_Tipo_Reten
	SELECT @cant = COUNT(*) FROM Proveedores_Reten WHERE Id_Tipo_Reten = @Id_Tipo_Reten

END 

SET @Mensaje = CONVERT(VARCHAR(10),@cant) + ' Registros Importados'
DROP TABLE #AR_Padron_IIBB_Temp

COMMIT
SELECT	 0					AS ErrorNumber 
		,0					AS ErrorSeverity  
		,0					AS ErrorState 
		,0					AS ErrorProcedure  
		,@cant 				AS ErrorLine		
		,@Mensaje			AS ErrorMessage

END TRY  


BEGIN CATCH  
    EXECUTE usp_GetErrorInfo;  
	ROLLBACK
END CATCH;   

/*

EXEC CPJ_AR_Padron_IIBB_Import  '{
												"Id_Tipo_Reten":"",
												"Archivo":"0",
												"ModoDebug":"0",
												"Tipo":"R"
											  }'


*/