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

CREATE PROCEDURE SPARSIV_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 = ''LOGARSIV'')
					  CREATE TABLE '+@DB+'[dbo].[LOGARSIV](
						[TARIH] [smalldatetime] NULL,
						[SAAT] [nvarchar](11) NULL,
						[USER] [nvarchar](50) NULL,
						[ISLEM] [nvarchar](50) NULL,
						[TABLE] [nvarchar](35) NULL,
						[ICERIK1] [ntext] NULL,
						[KAYITNO] [nvarchar](50) NULL,
						[DEGISIKLIK] [nvarchar](100) NULL,
						[LOGID] [int] NOT NULL,
					 CONSTRAINT [PK_LOGARSIV] PRIMARY KEY CLUSTERED 
					(
						[LOGID] ASC
					)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) 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].[LOGARSIV]'') AND name = N''IX_LOGARSIV_ISLEM'')
					  CREATE NONCLUSTERED INDEX [IX_LOGARSIV_ISLEM] ON '+@DB+'[dbo].[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].[LOGARSIV]'') AND name = N''IX_LOGARSIV_TARIH_SAAT'')
					  CREATE NONCLUSTERED INDEX [IX_LOGARSIV_TARIH_SAAT] ON '+@DB+'[dbo].[LOGARSIV]
					(
						[TARIH] ASC,
						[SAAT] 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].[LOGARSIV]'') AND name = N''IX_LOGARSIV_USER'')
					  CREATE NONCLUSTERED INDEX [IX_LOGARSIV_USER] ON '+@DB+'[dbo].[LOGARSIV]
					(
						[USER] 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].[LOGARSIV]'') AND name = N''IX_LOGARSIV_TABLE'')
					  CREATE NONCLUSTERED INDEX [IX_LOGARSIV_TABLE] ON '+@DB+'[dbo].[LOGARSIV]
					(
						[TABLE] 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 LOG WHERE TARIH<DATEADD(WEEK,-2,GETDATE()))
		BEGIN
			SET @SQL =  N'DELETE FROM '+@DB+'DBO.LOGARSIV WHERE LOGID IN (SELECT TOP 3000 LOGID FROM LOG WHERE TARIH<DATEADD(WEEK,-2,GETDATE()) ORDER BY LOGID DESC)'
			EXECUTE sp_executesql @SQL;
			SET @SQL =  N'INSERT INTO '+@DB+'DBO.LOGARSIV (TARIH,SAAT,[USER],ISLEM,[TABLE],ICERIK1,KAYITNO,DEGISIKLIK,LOGID) SELECT TOP 3000 TARIH,SAAT,[USER],ISLEM,[TABLE],ICERIK1,KAYITNO,DEGISIKLIK,LOGID FROM LOG WHERE TARIH<DATEADD(WEEK,-2,GETDATE()) ORDER BY LOGID DESC'
			EXECUTE sp_executesql @SQL;
			DELETE FROM LOG WHERE LOGID IN (SELECT TOP 3000 LOGID FROM LOG WHERE TARIH<DATEADD(WEEK,-2,GETDATE()) ORDER BY LOGID DESC)
			WAITFOR DELAY '00:00:01';
		END
		ELSE
		BEGIN
			SET @SON=1
		END

	END
END
GO
