Anonymous

CREATE PROCEDURE dbo.stats(@DomeinID int) AS-- ID Int-- Bestand varchar(255)-- RequestCount int-- DomeinID int-- BytesSent int-- BytesRecieved int-- create table imghost (-- ClientHost varchar(255), username varchar(255),-- LogTime datetime, service varchar( 255), machine varchar( 255),-- serverip varchar( 50), processingtime int, bytesrecvd int,-- bytessent int, servicestatus int, win32status int,-- operation varchar( 255), target varchar(255), parameters varchar(255) )Declare @RequestCount int, @BytesSent int, @BytesRecieved int, @ID intDeclare @IP varchar(15), @IPVisits bigint, @IPTS bigint, @IPTR bigintDeclare @TrafficRequests bigint, @TotalRequests bigint, @TotalSent bigint, @TotalRecieved bigint, @Vandaag Datetime, @TrafficSent bigint, @TrafficRecieved bigintDeclare @Bestand varchar(255), @LogID int, @BytesSentLOG int, @BytesRecievedLOG int, @get_log CURSORSet @Vandaag = CONVERT(CHAR(11),GETDATE(),120)if (@DomeinID = 1) 	SET @get_log = CURSOR FOR SELECT id, clienthost, target, bytesrecvd, bytessent FROM imghostIf (@DomeinID = 2) 	SET @get_log = CURSOR FOR SELECT id, clienthost, target, bytesrecvd, bytessent FROM forumif (@DomeinID = 3) 	SET @get_log = CURSOR FOR SELECT id, clienthost, target, bytesrecvd, bytessent FROM wwwIf (@DomeinID = 4) 	SET @get_log = CURSOR FOR SELECT id, clienthost, target, bytesrecvd, bytessent FROM raymonIf (@DomeinID = 5) 	SET @get_log = CURSOR FOR SELECT id, clienthost, target, bytesrecvd, bytessent FROM overigOPEN @get_log	FETCH NEXT FROM @get_log INTO @LogID, @IP, @Bestand, @BytesRecievedLOG, @BytesSentLOGSET @TotalRequests = 0SET @TotalSent = 0SET @TotalRecieved = 0WHILE (@@FETCH_STATUS = 0)	BEGIN	-- stukje voor RequestsByIP	SET @IPVisits = 0	SELECT @IPVisits=RequestCount, @IPTS=TotalSent, @IPTR=TotalRecieved FROM RequestsByIP Where IP = @IP	If (@IPVisits = 0)		BEGIN			INSERT INTO RequestsByIP (IP, RequestCount, TotalSent, TotalRecieved, DomeinID, FirstVisitDate, LastVisitDate) values (@IP, 1, @BytesSentLOG, @BytesRecievedLOG, @DomeinID, @Vandaag, @Vandaag)		END	Else		BEGIN			UPDATE RequestsByIP SET RequestCount = (@IPVisits + 1), TotalSent = (@IPTS + @BytesSentLOG), TotalRecieved = (@IPTR + @BytesRecievedLOG), LastVisitDate = @Vandaag Where IP = @IP AND DomeinID = @DomeinID		END	-- Einde stukje voor RequestsByIP	-- Begin Stukje voor Requests	SET @ID = ''	SELECT @ID=ID, @RequestCount=RequestCount, @BytesSent=BytesSent, @BytesRecieved=BytesRecieved FROM Requests Where Bestand = @Bestand AND DomeinID = @DomeinID	SET @TotalRequests = @TotalRequests+ 1	if (@ID = '')		BEGIN			INSERT INTO Requests (Bestand, RequestCount, DomeinID, BytesSent, BytesRecieved) Values (@Bestand, 1, @DomeinID, @BytesSentLOG, @BytesRecievedLOG)		END	Else		BEGIN			SET @TotalSent = @TotalSent + @BytesSent			SET @TotalRecieved = @TotalRecieved + @BytesRecieved			UPDATE Requests SET RequestCount = (@RequestCount + 1), BytesSent = (@BytesSent + @BytesSentLOG), BytesRecieved = (@BytesRecieved + @BytesRecievedLOG) Where ID = @ID AND DomeinID = @DomeinID		End 	-- Einde stukje voor Requests	if (@DomeinID = 1) 		Delete From imghost where ID = @LOGID	If (@DomeinID = 2) 		Delete From forum where ID = @LOGID	if (@DomeinID = 3) 		Delete From www where ID = @LOGID	If (@DomeinID = 4) 		Delete From raymon where ID = @LOGID	If (@DomeinID = 5) 		Delete From overig where ID = @LOGID	OPEN @get_log	FETCH NEXT FROM @get_log INTO @LogID, @IP, @Bestand, @BytesRecievedLOG, @BytesSentLOGENDCLOSE @get_logDEALLOCATE @get_log-- Totalen weg schrijven in een Table :pSET @TrafficSent = ''SET @TrafficRecieved = ''SELECT @TrafficSent=TotalSent, @TrafficRecieved=TotalRecieved, @TrafficRequests=TotalRequests FROM Traffic Where Datum = @Vandaag AND DomeinID = @DomeinIDIf  (@TrafficSent = '') And (@TrafficRecieved = '')	BEGIN		INSERT INTO Traffic (TotalSent, TotalRecieved, TotalRequests, DomeinID, Datum) Values (@TotalSent, @TotalRecieved, @TotalRequests, @DomeinID, @Vandaag)		Print 'Traffic totals Insert (Geen eerdere requests gevonden)'	ENDElse	BEGIN		UPDATE Traffic SET TotalSent = (@TrafficSent + @TotalSent), TotalRecieved = (@TrafficRecieved + @TotalRecieved), TotalRequests = (@TrafficRequests + @TotalRequests) Where Datum = @vandaag And DomeinID = @DomeinID		Print 'Traffic totals Update (Eerdere requests aanwezig)'	End-- Delete From Requests where DomeinID = @DomeinID-- Delete From Traffic where DomeinID = @DomeinID-- Delete From RequestsByIP where DomeinID = @DomeinIDGO