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

CREATE PROCEDURE SPARSIV_MDLXMLLOG
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 = ''MDLXMLLOGARSIV'')
					CREATE TABLE '+@DB+'[dbo].[MDLXMLLOGARSIV](
						[MDLXMLLOGID] [int] NOT NULL,
						[HDOSYAID] [int] NULL,
						[TARIH] [datetime] NULL,
						[KAYDEDEN] [nvarchar](15) NULL,
						[XMLDOSYAADI] [nvarchar](60) NULL,
						[XMLDATA] [nvarchar](max) NULL,
						[MDLHBSVFATURALAMAID] [int] NULL,
					 CONSTRAINT [PK_MDLXMLLOGARSIV] PRIMARY KEY CLUSTERED 
					(
						[MDLXMLLOGID] ASC
					)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 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].[MDLXMLLOGARSIV]'') AND name = N''IX_MDLXMLLOGARSIV_HDOSYAID'')
					  CREATE NONCLUSTERED INDEX [IX_MDLXMLLOGARSIV_HDOSYAID] ON '+@DB+'[dbo].[MDLXMLLOGARSIV]
					(
						[HDOSYAID] 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 MDLXMLLOG WHERE TARIH<DATEADD(WEEK,-2,GETDATE()))
		BEGIN
			SET @SQL =  N'DELETE FROM '+@DB+'DBO.MDLXMLLOGARSIV WHERE MDLXMLLOGID IN (SELECT TOP 3000 MDLXMLLOGID FROM MDLXMLLOG WHERE TARIH<DATEADD(WEEK,-2,GETDATE()) ORDER BY MDLXMLLOGID DESC)'
			EXECUTE sp_executesql @SQL;
			SET @SQL =  N'INSERT INTO '+@DB+'DBO.MDLXMLLOGARSIV (MDLXMLLOGID,HDOSYAID,TARIH,KAYDEDEN,XMLDOSYAADI,XMLDATA,MDLHBSVFATURALAMAID) SELECT TOP 3000 MDLXMLLOGID,HDOSYAID,TARIH,KAYDEDEN,XMLDOSYAADI,XMLDATA,MDLHBSVFATURALAMAID FROM MDLXMLLOG WHERE TARIH<DATEADD(WEEK,-2,GETDATE()) ORDER BY MDLXMLLOGID DESC'
			EXECUTE sp_executesql @SQL;
			DELETE FROM MDLXMLLOG WHERE MDLXMLLOGID IN (SELECT TOP 3000 MDLXMLLOGID FROM MDLXMLLOG WHERE TARIH<DATEADD(WEEK,-2,GETDATE()) ORDER BY MDLXMLLOGID DESC)
			WAITFOR DELAY '00:00:01';
		END
		ELSE
		BEGIN
			SET @SON=1
		END

	END
END
GO
