dotnetnuke.schema.sqldataprovider

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

create procedure {databaseOwner}{objectQualifier}GetFileById

@FileId   int,
@PortalId int

as

select *
from   {objectQualifier}Files
where  FileId = @FileId
and    ((PortalId = @PortalId) or (@PortalId is null and PortalId is null))







GO

create procedure {databaseOwner}{objectQualifier}GetSystemMessages

as

select MessageName
from   {objectQualifier}SystemMessages
where  PortalID is null








GO

CREATE  procedure {databaseOwner}{objectQualifier}UpdateModuleControl

@ModuleControlId               int,
@ModuleDefID                   int,
@ControlKey                    nvarchar(50),
@ControlTitle                  nvarchar(50),
@ControlSrc                    nvarchar(256),
@IconFile                      nvarchar(100),
@ControlType                   int,
@ViewOrder                     int,
@HelpUrl                       nvarchar(200)

as

update {objectQualifier}ModuleControls
set    ModuleDefId       = @ModuleDefId,
       ControlKey        = @ControlKey,
       ControlTitle      = @ControlTitle,
       ControlSrc        = @ControlSrc,
       IconFile          = @IconFile,
       ControlType       = @ControlType,
       ViewOrder         = ViewOrder,
       HelpUrl           = @HelpUrl
where  ModuleControlId = @ModuleControlId








GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}AddTabPermission
	@TabID int,
	@PermissionID int,
	@RoleID int,
	@AllowAccess bit
AS

INSERT INTO {databaseOwner}{objectQualifier}TabPermission (
	[TabID],
	[PermissionID],
	[RoleID],
	[AllowAccess]
) VALUES (
	@TabID,
	@PermissionID,
	@RoleID,
	@AllowAccess
)

select SCOPE_IDENTITY()








GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetSearchItemWordPosition
	@SearchItemWordPositionID int
	
AS

SELECT
	[SearchItemWordPositionID],
	[SearchItemWordID],
	[ContentPosition]
FROM
	{databaseOwner}{objectQualifier}SearchItemWordPosition
WHERE
	[SearchItemWordPositionID] = @SearchItemWordPositionID







GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}PurgeScheduleHistory
AS
DELETE FROM {databaseOwner}{objectQualifier}ScheduleHistory
FROM {databaseOwner}{objectQualifier}Schedule s
WHERE
    (
    SELECT COUNT(*)
    FROM {databaseOwner}{objectQualifier}ScheduleHistory sh
    WHERE
        sh.ScheduleID = {databaseOwner}{objectQualifier}ScheduleHistory.ScheduleID AND
        sh.ScheduleID = s.ScheduleID AND
        sh.StartDate >= {databaseOwner}{objectQualifier}ScheduleHistory.StartDate
    ) > s.RetainHistoryNum
AND RetainHistoryNum<>-1








GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetPermission
	@PermissionID int
AS

SELECT
	[PermissionID],
	[PermissionCode],
	[ModuleDefID],
	[PermissionKey],
	[PermissionName]
FROM
	{databaseOwner}{objectQualifier}Permission
WHERE
	[PermissionID] = @PermissionID








GO






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}AddSearchItem

	@Title nvarchar(200),
	@Description nvarchar(2000),
	@Author int,
	@PubDate datetime,
	@ModuleId int,
	@SearchKey nvarchar(100),
	@Guid nvarchar(200), 
	@ImageFileId int

as

insert into {objectQualifier}SearchItem (
	Title,
	Description,
	Author,
	PubDate,
	ModuleId,
 	SearchKey,
	Guid,
	HitCount,
	ImageFileId
) 
values (
	@Title,
	@Description,
	@Author,
	@PubDate,
	@ModuleId,
	@SearchKey,
	@Guid,
	0,
	@ImageFileId
)

select SCOPE_IDENTITY()








GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DeleteFolderPermission
	@FolderPermissionID int
AS

DELETE FROM {databaseOwner}{objectQualifier}FolderPermission
WHERE
	[FolderPermissionID] = @FolderPermissionID







GO

create procedure {databaseOwner}{objectQualifier}UpdateSystemMessage

@PortalID     int,
@MessageName  nvarchar(50),
@MessageValue ntext

as

update {objectQualifier}SystemMessages
set    MessageValue = @MessageValue
where  ((PortalID = @PortalID) or (PortalID is null and @PortalID is null))
and    MessageName = @MessageName








GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}GetPermissionsByFolderPath
	@PortalID int,
	@FolderPath varchar(300)
AS

SELECT
	P.[PermissionID],
	P.[PermissionCode],
	P.[PermissionKey],
	P.[PermissionName]
FROM
	{databaseOwner}{objectQualifier}Permission P
WHERE
	P.PermissionCode = 'SYSTEM_FOLDER'








GO
CREATE procedure {databaseOwner}{objectQualifier}AddPortalAlias

@PortalID int,
@HTTPAlias nvarchar(200)

as

INSERT INTO {databaseOwner}{objectQualifier}PortalAlias 
(PortalID, HTTPAlias)
VALUES
(@PortalID, @HTTPAlias)

select SCOPE_IDENTITY()








GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}GetScheduleHistory
@ScheduleID int
AS
SELECT S.ScheduleID, S.TypeFullName, SH.StartDate, SH.EndDate, SH.Succeeded, SH.LogNotes, SH.NextStart, SH.Server
FROM {databaseOwner}{objectQualifier}Schedule S
INNER JOIN {databaseOwner}{objectQualifier}ScheduleHistory SH
ON S.ScheduleID = SH.ScheduleID
WHERE S.ScheduleID = @ScheduleID or @ScheduleID = -1

GO

create procedure {databaseOwner}{objectQualifier}UpdateAffiliate

@AffiliateId int,
@StartDate         datetime,
@EndDate           datetime,
@CPC               float,
@CPA               float

as

update {objectQualifier}Affiliates
set    StartDate   = @StartDate,
       EndDate     = @EndDate,
       CPC         = @CPC,
       CPA         = @CPA
where  AffiliateId = @AffiliateId 








GO

create procedure {databaseOwner}{objectQualifier}GetSiteLog12

@PortalId int,
@PortalAlias nvarchar(50),
@StartDate datetime,
@EndDate datetime

as

select AffiliateId,
 'Requests' = count(*),
 'LastReferral' = max(DateTime)
from {objectQualifier}SiteLog
where {objectQualifier}SiteLog.PortalId = @PortalId
and {objectQualifier}SiteLog.DateTime between @StartDate and @EndDate
and AffiliateId is not null
group by AffiliateId
order by Requests desc








GO

create procedure {databaseOwner}{objectQualifier}GetModuleControls

@ModuleDefId int

as

select *
from   {objectQualifier}ModuleControls
where  (ModuleDefId is null and @ModuleDefId is null) or (ModuleDefId = @ModuleDefId)
order  by ControlKey, ViewOrder








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}UpdateProfile

@UserId        int, 
@PortalId      int,
@ProfileData   ntext

as

update {objectQualifier}Profile
set    ProfileData = @ProfileData,
       CreatedDate = getdate()
where  UserId = @UserId
and    PortalId = @PortalId








GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetScheduleItemSettings 
@ScheduleID int
AS
SELECT *
FROM {databaseOwner}{objectQualifier}ScheduleItemSettings
WHERE ScheduleID = @ScheduleID








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}GetSearchCommonWordByID
	@CommonWordID int
	
AS

SELECT
	[CommonWordID],
	[CommonWord],
	[Locale]
FROM
	{databaseOwner}{objectQualifier}SearchCommonWords
WHERE
	[CommonWordID] = @CommonWordID







GO

CREATE procedure {databaseOwner}{objectQualifier}GetModuleSetting

@ModuleId      int,
@SettingName   nvarchar(50)

as

select 
'SettingValue' = case when {objectQualifier}Files.FileName is null then {objectQualifier}ModuleSettings.SettingValue else {objectQualifier}Files.Folder + {objectQualifier}Files.FileName end

from {objectQualifier}ModuleSettings 
left outer join {objectQualifier}Files on {objectQualifier}ModuleSettings.SettingValue = 'fileid=' +
convert(varchar,{objectQualifier}Files.FileID)
where SettingName = @SettingName and moduleid=@moduleid







GO

create procedure {databaseOwner}{objectQualifier}DeleteAffiliate

@AffiliateId int

as

delete
from   {objectQualifier}Affiliates
where  AffiliateId = @AffiliateId








GO

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


GO

create procedure {databaseOwner}{objectQualifier}GetSearchItem
	@ModuleId int,
	@SearchKey varchar(100) 
AS