IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[SPARSIV_EFATURA_LOG]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE SPARSIV_EFATURA_LOG
GO

CREATE PROCEDURE SPARSIV_EFATURA_LOG
AS
BEGIN

	DECLARE @DB VARCHAR(50)
	DECLARE @SQL NVARCHAR(2000)

	SET @DB = ISNULL((SELECT TOP 1 VALUESTR FROM CONFIG WHERE PROGNAME='MEDISOFT' AND SECTIONSTR='Sistem' AND KEYNAME='Arsiv veri tabani'),'')
	IF @DB<>'' SET @DB=@DB+'.'


	SET @SQL =  N'IF NOT EXISTS (SELECT * FROM '+@DB+'[dbo].SYSOBJECTS WHERE SYSOBJECTS.name = ''EFATURA_LOGARSIV'')
					  CREATE TABLE '+@DB+'[dbo].[EFATURA_LOGARSIV](
						[ID] [int] NOT NULL,
						[EFATNO] [nvarchar](50) NULL,
						[UUID] [nvarchar](50) NULL,
						[DURUM] [nvarchar](25) NULL,
						[TARIH] [datetime] NOT NULL,
						[ISLEM] [nvarchar](25) NULL,
						[HATAMESAJI] [nvarchar](255) NULL,
						[ACIKLAMA] [nvarchar](250) NULL,
						[XML] [ntext] NULL,
						[ONIZLE] [ntext] NULL,
						CONSTRAINT [PK_EFATURA_LOGARSIV] PRIMARY KEY CLUSTERED 
						(
							[ID] ASC
						)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
						) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]';

	EXECUTE sp_executesql @SQL; 


	SET @SQL =  N'IF NOT EXISTS (SELECT * FROM '+@DB+'sys.indexes WHERE object_id = OBJECT_ID(N'''+@DB+'[dbo].[EFATURA_LOGARSIV]'') AND name = N''IX_EFATURA_LOGARSIV_EFATNO'')
					  CREATE NONCLUSTERED INDEX [IX_EFATURA_LOGARSIV_EFATNO] ON '+@DB+'[dbo].[EFATURA_LOGARSIV]
					(
						[EFATNO] ASC
					)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]';
	EXECUTE sp_executesql @SQL; 

	SET @SQL =  N'IF NOT EXISTS (SELECT * FROM '+@DB+'sys.indexes WHERE object_id = OBJECT_ID(N'''+@DB+'[dbo].[EFATURA_LOGARSIV]'') AND name = N''IX_EFATURA_LOGARSIV_ISLEM'')
					  CREATE NONCLUSTERED INDEX [IX_EFATURA_LOGARSIV_ISLEM] ON '+@DB+'[dbo].[EFATURA_LOGARSIV]
					(
						[ISLEM] ASC
					)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]';
	EXECUTE sp_executesql @SQL; 

	SET @SQL =  N'IF NOT EXISTS (SELECT * FROM '+@DB+'sys.indexes WHERE object_id = OBJECT_ID(N'''+@DB+'[dbo].[EFATURA_LOGARSIV]'') AND name = N''IX_EFATURA_LOGARSIV_UUID'')
					  CREATE NONCLUSTERED INDEX [IX_EFATURA_LOGARSIV_UUID] ON '+@DB+'[dbo].[EFATURA_LOGARSIV]
					(
						[UUID] ASC
					)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]';
	EXECUTE sp_executesql @SQL;
	

	DECLARE @SON BIT = 0

	WHILE @SON =0
	BEGIN

		IF EXISTS(SELECT TOP 1 * FROM EFATURA_LOG WHERE TARIH<DATEADD(MONTH,-2,GETDATE()))
		BEGIN
			SET @SQL =  N'DELETE FROM '+@DB+'DBO.EFATURA_LOGARSIV WHERE ID IN (SELECT TOP 3000 ID FROM EFATURA_LOG WHERE TARIH<DATEADD(MONTH,-2,GETDATE()) ORDER BY ID DESC)'
			EXECUTE sp_executesql @SQL;
			SET @SQL =  N'INSERT INTO '+@DB+'DBO.EFATURA_LOGARSIV (ID,EFATNO,UUID,DURUM,TARIH,ISLEM,HATAMESAJI,ACIKLAMA,XML,ONIZLE) SELECT TOP 3000 ID,EFATNO,UUID,DURUM,TARIH,ISLEM,HATAMESAJI,ACIKLAMA,XML,ONIZLE FROM EFATURA_LOG WHERE TARIH<DATEADD(MONTH,-2,GETDATE()) ORDER BY ID DESC'
			EXECUTE sp_executesql @SQL;
			DELETE FROM EFATURA_LOG WHERE ID IN (SELECT TOP 3000 ID FROM EFATURA_LOG WHERE TARIH<DATEADD(MONTH,-2,GETDATE()) ORDER BY ID DESC)
			WAITFOR DELAY '00:00:01';
		END
		ELSE
		BEGIN
			SET @SON=1
		END

	END
END
GO
