03.01.00.sqldataprovider

完整的商业模板和强大的后台管理功能

CREATE PROCEDURE {databaseOwner}{objectQualifier}AddEventLog
	@LogGUID varchar(36),
	@LogTypeKey nvarchar(35),
	@LogUserID int,
	@LogUserName nvarchar(50),
	@LogPortalID int,
	@LogPortalName nvarchar(100),
	@LogCreateDate datetime,
	@LogServerName nvarchar(50),
	@LogProperties ntext,
	@LogConfigID int
AS
INSERT INTO {databaseOwner}{objectQualifier}EventLog
	(LogGUID,
	LogTypeKey,
	LogUserID,
	LogUserName,
	LogPortalID,
	LogPortalName,
	LogCreateDate,
	LogServerName,
	LogProperties,
	LogConfigID)
VALUES
	(@LogGUID,
	@LogTypeKey,
	@LogUserID,
	@LogUserName,
	@LogPortalID,
	@LogPortalName,
	@LogCreateDate,
	@LogServerName,
	@LogProperties,
	@LogConfigID)

DECLARE @NotificationActive bit
DECLARE @NotificationThreshold bit
DECLARE @ThresholdQueue int
DECLARE @NotificationThresholdTime int
DECLARE @NotificationThresholdTimeType int
DECLARE @MinDateTime smalldatetime
DECLARE @CurrentDateTime smalldatetime

SET @CurrentDateTime = getDate()


SELECT TOP 1 @NotificationActive = EmailNotificationIsActive,
	@NotificationThreshold = NotificationThreshold,
	@NotificationThresholdTime = NotificationThresholdTime,
	@NotificationThresholdTimeType = @NotificationThresholdTimeType,
	@MinDateTime = 
		CASE
			 --seconds
			WHEN NotificationThresholdTimeType=1 THEN DateAdd(second, NotificationThresholdTime * -1, @CurrentDateTime)
			--minutes
			WHEN NotificationThresholdTimeType=2  THEN DateAdd(minute, NotificationThresholdTime * -1, @CurrentDateTime)
			--hours
			WHEN NotificationThresholdTimeType=3  THEN DateAdd(Hour, NotificationThresholdTime * -1, @CurrentDateTime)
			--days
			WHEN NotificationThresholdTimeType=4  THEN DateAdd(Day, NotificationThresholdTime * -1, @CurrentDateTime)
		END
FROM {databaseOwner}{objectQualifier}EventLogConfig
WHERE ID = @LogConfigID

IF @NotificationActive=1
BEGIN
	
	SELECT @ThresholdQueue = COUNT(*)
	FROM {databaseOwner}{objectQualifier}EventLog
	INNER JOIN {databaseOwner}{objectQualifier}EventLogConfig
	ON {databaseOwner}{objectQualifier}EventLog.LogConfigID = {databaseOwner}{objectQualifier}EventLogConfig.ID
	WHERE LogCreateDate > @MinDateTime

	PRINT 'MinDateTime=' + convert(varchar(20), @MinDateTime)
	PRINT 'ThresholdQueue=' + convert(varchar(20), @ThresholdQueue)
	PRINT 'NotificationThreshold=' + convert(varchar(20), @NotificationThreshold)

	IF @ThresholdQueue > @NotificationThreshold
	BEGIN
		UPDATE {databaseOwner}{objectQualifier}EventLog
		SET LogNotificationPending = 1 
		WHERE LogConfigID = @LogConfigID
		AND LogNotificationPending IS NULL		
		AND LogCreateDate > @MinDateTime
	END
END
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}AddEventLogConfig
	@LogTypeKey nvarchar(35),
	@LogTypePortalID int,
	@LoggingIsActive bit,
	@KeepMostRecent int,
	@EmailNotificationIsActive bit,
	@NotificationThreshold int,
	@NotificationThresholdTime int,
	@NotificationThresholdTimeType int,
	@MailFromAddress nvarchar(50),
	@MailToAddress nvarchar(50)
AS
INSERT INTO {databaseOwner}{objectQualifier}EventLogConfig
	(LogTypeKey,
	LogTypePortalID,
	LoggingIsActive,
	KeepMostRecent,
	EmailNotificationIsActive,
	NotificationThreshold,
	NotificationThresholdTime,
	NotificationThresholdTimeType,
	MailFromAddress,
	MailToAddress)
VALUES
	(@LogTypeKey,
	@LogTypePortalID,
	@LoggingIsActive,
	@KeepMostRecent,
	@EmailNotificationIsActive,
	@NotificationThreshold,
	@NotificationThresholdTime,
	@NotificationThresholdTimeType,
	@MailFromAddress,
	@MailToAddress)
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DeleteEventLog
	@LogGUID varchar(36)
AS
DELETE FROM {databaseOwner}{objectQualifier}EventLog
WHERE LogGUID = @LogGUID or @LogGUID IS NULL

GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DeleteEventLogConfig
	@ID int
AS
DELETE FROM {databaseOwner}{objectQualifier}EventLogConfig
WHERE ID = @ID

GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetEventLog
	@PortalID int,
	@LogTypeKey nvarchar(35)
AS
SELECT {databaseOwner}{objectQualifier}EventLog.*
FROM {databaseOwner}{objectQualifier}EventLog
INNER JOIN {databaseOwner}{objectQualifier}EventLogConfig
ON {databaseOwner}{objectQualifier}EventLog.LogConfigID = {databaseOwner}{objectQualifier}EventLogConfig.ID
WHERE (LogPortalID = @PortalID or @PortalID IS NULL)
AND ({databaseOwner}{objectQualifier}EventLog.LogTypeKey = @LogTypeKey or @LogTypeKey IS NULL)
ORDER BY LogCreateDate DESC

GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}GetEventLogByLogGUID
	@LogGUID varchar(36)
AS
SELECT *
FROM {databaseOwner}{objectQualifier}EventLog
WHERE (LogGUID = @LogGUID)

GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetEventLogConfig
	@ID int
AS
SELECT *
FROM {databaseOwner}{objectQualifier}EventLogConfig
WHERE (ID = @ID or @ID IS NULL)

GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetEventLogType
AS
SELECT *
FROM {databaseOwner}{objectQualifier}EventLogTypes

GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}UpdateEventLogConfig
	@ID int,
	@LogTypeKey nvarchar(35),
	@LogTypePortalID int,
	@LoggingIsActive bit,
	@KeepMostRecent int,
	@EmailNotificationIsActive bit,
	@NotificationThreshold int,
	@NotificationThresholdTime int,
	@NotificationThresholdTimeType int,
	@MailFromAddress nvarchar(50),
	@MailToAddress nvarchar(50)
AS
UPDATE {databaseOwner}{objectQualifier}EventLogConfig
SET 	LogTypeKey = @LogTypeKey,
	LogTypePortalID = @LogTypePortalID,
	LoggingIsActive = @LoggingIsActive,
	KeepMostRecent = @KeepMostRecent,
	EmailNotificationIsActive = @EmailNotificationIsActive,
	NotificationThreshold = @NotificationThreshold,
	NotificationThresholdTime = @NotificationThresholdTime,
	NotificationThresholdTimeType = @NotificationThresholdTimeType,
	MailFromAddress = @MailFromAddress,
	MailToAddress = @MailToAddress
WHERE	ID = @ID
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}PurgeEventLog
AS
DELETE FROM {databaseOwner}{objectQualifier}EventLog
FROM {databaseOwner}{objectQualifier}EventLogConfig elc
WHERE 
    (
    SELECT COUNT(*)
    FROM {databaseOwner}{objectQualifier}EventLog el
    WHERE el.LogConfigID = elc.ID
	and {databaseOwner}{objectQualifier}EventLog.LogTypeKey = el.LogTypeKey
	and el.LogCreateDate >= {databaseOwner}{objectQualifier}EventLog.LogCreateDate
    ) > elc.KeepMostRecent
AND elc.KeepMostRecent<>-1
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}AddEventLogType
	@LogTypeKey nvarchar(35),
	@LogTypeFriendlyName nvarchar(50),
	@LogTypeDescription nvarchar(128),
	@LogTypeOwner nvarchar(100),
	@LogTypeCSSClass nvarchar(40)
AS
	INSERT INTO {databaseOwner}{objectQualifier}EventLogTypes
	(LogTypeKey,
	LogTypeFriendlyName,
	LogTypeDescription,
	LogTypeOwner,
	LogTypeCSSClass)
VALUES
	(@LogTypeKey,
	@LogTypeFriendlyName,
	@LogTypeDescription,
	@LogTypeOwner,
	@LogTypeCSSClass)	
	
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}UpdateEventLogType
	@LogTypeKey nvarchar(35),
	@LogTypeFriendlyName nvarchar(50),
	@LogTypeDescription nvarchar(128),
	@LogTypeOwner nvarchar(100),
	@LogTypeCSSClass nvarchar(40)
AS
UPDATE {databaseOwner}{objectQualifier}EventLogTypes
	SET LogTypeFriendlyName = @LogTypeFriendlyName,
	LogTypeDescription = @LogTypeDescription,
	LogTypeOwner = @LogTypeOwner,
	LogTypeCSSClass = @LogTypeCSSClass
WHERE	LogTypeKey = @LogTypeKey
	
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DeleteEventLogType
	@LogTypeKey nvarchar(35)
AS
DELETE FROM {databaseOwner}{objectQualifier}EventLogTypes
WHERE	LogTypeKey = @LogTypeKey
	
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetEventLogPendingNotifConfig
AS

SELECT 	COUNT(*) as PendingNotifs,
	elc.ID,
	elc.LogTypeKey, 
	elc.LogTypePortalID, 
	elc.LoggingIsActive,
	elc.KeepMostRecent,
	elc.EmailNotificationIsActive,
	elc.NotificationThreshold,
	elc.NotificationThresholdTime,
	elc.NotificationThresholdTimeType,
	elc.MailToAddress, 
	elc.MailFromAddress
FROM {databaseOwner}{objectQualifier}EventLogConfig elc
INNER JOIN {databaseOwner}{objectQualifier}EventLog
ON {databaseOwner}{objectQualifier}EventLog.LogConfigID = elc.ID
WHERE {databaseOwner}{objectQualifier}EventLog.LogNotificationPending = 1
GROUP BY elc.ID,
	elc.LogTypeKey, 
	elc.LogTypePortalID, 
	elc.LoggingIsActive,
	elc.KeepMostRecent,
	elc.EmailNotificationIsActive,
	elc.NotificationThreshold,
	elc.NotificationThresholdTime,
	elc.NotificationThresholdTimeType,
	elc.MailToAddress, 
	elc.MailFromAddress
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetEventLogPendingNotif
	@LogConfigID int
AS
SELECT *
FROM {databaseOwner}{objectQualifier}EventLog
WHERE LogNotificationPending = 1
AND LogConfigID = @LogConfigID

GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}UpdateEventLogPendingNotif
	@LogConfigID int
AS
UPDATE {databaseOwner}{objectQualifier}EventLog
SET LogNotificationPending = 0
WHERE LogNotificationPending = 1
AND LogConfigID = @LogConfigID

GO

INSERT {databaseOwner}{objectQualifier}HostSettings
	VALUES (N'EnableModuleOnLineHelp',N'Y', 0)
GO

INSERT {databaseOwner}{objectQualifier}HostSettings
	VALUES (N'HelpURL',N'http://www.dotnetnuke.com/default.aspx?tabid=787', 0)
GO

ALTER TABLE {databaseOwner}{objectQualifier}Vendors ADD
	Keywords2 ntext NULL
GO

update {databaseOwner}{objectQualifier}Vendors
set Keywords2 = Keywords
GO

ALTER TABLE {databaseOwner}{objectQualifier}Vendors
	DROP COLUMN Keywords
GO

ALTER TABLE {databaseOwner}{objectQualifier}Vendors ADD
	Keywords ntext NULL
GO

update {databaseOwner}{objectQualifier}Vendors
set Keywords = Keywords2
GO

ALTER TABLE {databaseOwner}{objectQualifier}Vendors
	DROP COLUMN Keywords2
GO


/************************************************************/
/*****              SqlDataProvider                     *****/
/************************************************************/