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

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

/*Parametros */
DECLARE @Archivo VARCHAR(1000)

DECLARE @Mensaje			VARCHAR(100)
		,@sql				NVARCHAR(4000)
		,@cant				INT
		,@Fecha				VARCHAR(10)
		,@Fecha_Pub			VARCHAR(10)
		,@Cuit				VARCHAR(19)
		,@Descripcion		VARCHAR(500)
		,@xParametro		TINYINT
		
/*Fin de Parametros*/

SET @Archivo	 = ISNULL(CONVERT(VARCHAR(1000)	, dbo.CF_JSON_VALUE(@pJson,'Archivo')), '')
SET @xParametro	 = (SELECT dbo.CF_xParam('Afip_Fact_Apocri'))

IF (@Archivo = '')
BEGIN	
	SET @cant		= 0;
	SET @Mensaje	= 'Archivo Vaacio';

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

	RETURN;
END 

IF (@xParametro = 0)
BEGIN
	SET @cant = 0;
	SET @Mensaje	= 'Función Deshabilitada por xParametro: Afip_Fact_Apocri';

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

	RETURN;
END


BEGIN TRY
BEGIN TRAN

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

EXEC (@sql)


DELETE AR_AFIP_Fact_Apocrifas


DECLARE curTabCarga CURSOR FOR
SELECT  CAST(SUBSTRING(Linea,0,12)   AS VARCHAR(19)) AS 'Cuit'
					,CAST(SUBSTRING(Linea,13,10)  AS VARCHAR(10)) AS 'Fecha'
					,CAST(SUBSTRING(Linea,24,10)  AS VARCHAR(10)) AS 'Fecha_Pub'
					,CAST(SUBSTRING(Linea,36,500) AS varchar(500)) AS 'Descripcion'
FROM #AR_AFIP_Fact_Apocrifas_Import 
WHERE Linea NOT LIKE '%#%'

OPEN curTabCarga
FETCH NEXT FROM curTabCarga INTO  @Cuit, @Fecha, @Fecha_Pub, @Descripcion
WHILE @@FETCH_STATUS = 0
BEGIN
	

		PRINT 'CUIT: ' + @Cuit

		SET @Fecha =		(SELECT dbo.SF_CortarCadena (@Fecha, '/',3)		+  dbo.SF_CortarCadena (@Fecha, '/',2)		+  dbo.SF_CortarCadena (@Fecha, '/',1))
		PRINT 'FECHA: ' + @Fecha

		SET @Fecha_Pub =	(SELECT dbo.SF_CortarCadena (@Fecha_Pub, '/',3) +  dbo.SF_CortarCadena (@Fecha_Pub, '/',2)	+  dbo.SF_CortarCadena (@Fecha_Pub, '/',1))
		PRINT '@Fecha_Pub: ' + @Fecha_Pub

		INSERT INTO AR_AFIP_Fact_Apocrifas (Cuit,	Fecha,					Fecha_Pub,					Descripcion)
									VALUES  (@Cuit, CONVERT(DATE,@Fecha),	CONVERT(DATE,@Fecha_Pub),	@Descripcion)

	SET @cant += 1


FETCH NEXT FROM curTabCarga INTO @Cuit, @Fecha, @Fecha_Pub, @Descripcion
END
CLOSE curTabCarga
DEALLOCATE curTabCarga;


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


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 DBO.CPJ_AR_Fact_Apocrifas_Import '{"Archivo":"C:\Users\soporte\Downloads\FacturasApocrifas.txt"}'
*/