dotnetnuke.schema.sqldataprovider

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

 case 
 when {objectQualifier}SiteLog.UserAgent like '%MSIE 1%' then 'Internet Explorer 1'
 when {objectQualifier}SiteLog.UserAgent like '%MSIE 2%' then 'Internet Explorer 2'
 when {objectQualifier}SiteLog.UserAgent like '%MSIE 3%' then 'Internet Explorer 3'
 when {objectQualifier}SiteLog.UserAgent like '%MSIE 4%' then 'Internet Explorer 4'
 when {objectQualifier}SiteLog.UserAgent like '%MSIE 5%' then 'Internet Explorer 5'
 when {objectQualifier}SiteLog.UserAgent like '%MSIE 6%' then 'Internet Explorer 6'
 when {objectQualifier}SiteLog.UserAgent like '%MSIE%' then 'Internet Explorer'
 when {objectQualifier}SiteLog.UserAgent like '%Mozilla/1%' then 'Netscape Navigator 1'
 when {objectQualifier}SiteLog.UserAgent like '%Mozilla/2%' then 'Netscape Navigator 2'
 when {objectQualifier}SiteLog.UserAgent like '%Mozilla/3%' then 'Netscape Navigator 3'
 when {objectQualifier}SiteLog.UserAgent like '%Mozilla/4%' then 'Netscape Navigator 4'
 when {objectQualifier}SiteLog.UserAgent like '%Mozilla/5%' then 'Netscape Navigator 6+'
 else {objectQualifier}SiteLog.UserAgent
 end,
 'Requests' = count(*),
 'LastRequest' = max(DateTime)
from {objectQualifier}SiteLog
where PortalId = @PortalId
and {objectQualifier}SiteLog.DateTime between @StartDate and @EndDate
group by case 
 when {objectQualifier}SiteLog.UserAgent like '%MSIE 1%' then 'Internet Explorer 1'
 when {objectQualifier}SiteLog.UserAgent like '%MSIE 2%' then 'Internet Explorer 2'
 when {objectQualifier}SiteLog.UserAgent like '%MSIE 3%' then 'Internet Explorer 3'
 when {objectQualifier}SiteLog.UserAgent like '%MSIE 4%' then 'Internet Explorer 4'
 when {objectQualifier}SiteLog.UserAgent like '%MSIE 5%' then 'Internet Explorer 5'
 when {objectQualifier}SiteLog.UserAgent like '%MSIE 6%' then 'Internet Explorer 6'
 when {objectQualifier}SiteLog.UserAgent like '%MSIE%' then 'Internet Explorer'
 when {objectQualifier}SiteLog.UserAgent like '%Mozilla/1%' then 'Netscape Navigator 1'
 when {objectQualifier}SiteLog.UserAgent like '%Mozilla/2%' then 'Netscape Navigator 2'
 when {objectQualifier}SiteLog.UserAgent like '%Mozilla/3%' then 'Netscape Navigator 3'
 when {objectQualifier}SiteLog.UserAgent like '%Mozilla/4%' then 'Netscape Navigator 4'
 when {objectQualifier}SiteLog.UserAgent like '%Mozilla/5%' then 'Netscape Navigator 6+'
 else {objectQualifier}SiteLog.UserAgent
 end
order by Requests desc








GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}UpdateSearchItemWordPosition
	@SearchItemWordPositionID int, 
	@SearchItemWordID int, 
	@ContentPosition int 
AS

UPDATE {databaseOwner}{objectQualifier}SearchItemWordPosition SET
	[SearchItemWordID] = @SearchItemWordID,
	[ContentPosition] = @ContentPosition
WHERE
	[SearchItemWordPositionID] = @SearchItemWordPositionID







GO

create procedure {databaseOwner}{objectQualifier}AddTabModuleSetting

@TabModuleId   int,
@SettingName   nvarchar(50),
@SettingValue  nvarchar(2000)

as

insert into {objectQualifier}TabModuleSettings ( 
  TabModuleId,
  SettingName, 
  SettingValue 
) 
values ( 
  @TabModuleId,
  @SettingName, 
  @SettingValue 
)








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

@TabModuleId int

as

select SettingName,
       SettingValue
from   {objectQualifier}TabModuleSettings 
where  TabModuleId = @TabModuleId








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

@Major  int,
@Minor  int,
@Build  int

as

select 1
from   {objectQualifier}Version
where  Major = @Major
and    Minor = @Minor
and    Build = @Build








GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DeleteSearchItemWord
	@SearchItemWordID int
AS

DELETE FROM {databaseOwner}{objectQualifier}SearchItemWord
WHERE
	[SearchItemWordID] = @SearchItemWordID







GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetSearchItemWordBySearchItem
	@SearchItemID int
AS

SELECT
	[SearchItemWordID],
	[SearchItemID],
	[SearchWordsID],
	[Occurrences]
FROM
	{databaseOwner}{objectQualifier}SearchItemWord
WHERE
	[SearchItemID]=@SearchItemID







GO
CREATE TABLE {databaseOwner}[{objectQualifier}Urls]
(
[UrlID] [int] NOT NULL IDENTITY(1, 1),
[PortalID] [int] NULL,
[Url] [nvarchar] (255) NOT NULL
)

GO
ALTER TABLE {databaseOwner}[{objectQualifier}Urls] ADD CONSTRAINT [PK_{objectQualifier}Urls] PRIMARY KEY CLUSTERED  ([UrlID])
GO

create procedure {databaseOwner}{objectQualifier}DeleteUrl

@PortalID     int,
@Url          nvarchar(255)

as

delete
from   {objectQualifier}Urls
where  PortalID = @PortalID
and    Url = @Url








GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetScheduleByScheduleID
@ScheduleID int
AS
SELECT S.*
FROM {databaseOwner}{objectQualifier}Schedule S
WHERE S.ScheduleID = @ScheduleID








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}UpdateSearchItemWord
	@SearchItemWordID int, 
	@SearchItemID int, 
	@SearchWordsID int, 
	@Occurrences int 
AS

UPDATE {databaseOwner}{objectQualifier}SearchItemWord SET
	[SearchItemID] = @SearchItemID,
	[SearchWordsID] = @SearchWordsID,
	[Occurrences] = @Occurrences
WHERE
	[SearchItemWordID] = @SearchItemWordID







GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DeleteSearchItem
	@SearchItemID int
AS

DELETE FROM {databaseOwner}{objectQualifier}SearchItem
WHERE
	[SearchItemID] = @SearchItemID







GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetSearchWords
AS

SELECT
	[SearchWordsID],
	[Word],
	[HitCount]
FROM
	{databaseOwner}{objectQualifier}SearchWord







GO

create procedure {databaseOwner}{objectQualifier}GetSiteLog8

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

as

select 'Month' = datepart(month,DateTime),
 'Views' = count(*),
 'Visitors' = count(distinct {objectQualifier}SiteLog.UserHostAddress),
 'Users' = count(distinct {objectQualifier}SiteLog.UserId)
from {objectQualifier}SiteLog
where PortalId = @PortalId
and {objectQualifier}SiteLog.DateTime between @StartDate and @EndDate
group by datepart(month,DateTime)
order by Month








GO

create procedure {databaseOwner}{objectQualifier}AddAffiliate

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

as

insert into {objectQualifier}Affiliates (
    VendorId,
    StartDate,
    EndDate,
    CPC,
    Clicks,
    CPA,
    Acquisitions
)
values (
    @VendorId,
    @StartDate,
    @EndDate,
    @CPC,
    0,
    @CPA,
    0
)

select SCOPE_IDENTITY()








GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}UpdateFolder
	@PortalID int,
	@FolderID int,
	@FolderPath varchar(300)
AS
	UPDATE {databaseOwner}{objectQualifier}Folders
	SET FolderPath = @FolderPath
	WHERE ((PortalID = @PortalID) or (PortalID is null and @PortalID is null))
	AND FolderID = @FolderID








GO

create procedure {databaseOwner}{objectQualifier}GetAllProfiles
AS
SELECT * FROM {objectQualifier}Profile



GO


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


GO
CREATE TABLE {databaseOwner}[{objectQualifier}ScheduleItemSettings]
(
[ScheduleID] [int] NOT NULL,
[SettingName] [nvarchar] (50) NOT NULL,
[SettingValue] [nvarchar] (256) NOT NULL
)

GO
ALTER TABLE {databaseOwner}[{objectQualifier}ScheduleItemSettings] ADD CONSTRAINT [PK_{objectQualifier}ScheduleItemSettings] PRIMARY KEY CLUSTERED  ([ScheduleID], [SettingName])
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}UpdateSearchWord
	@SearchWordsID int, 
	@Word nvarchar(100), 
	@IsCommon bit, 
	@HitCount int 
AS

UPDATE {databaseOwner}{objectQualifier}SearchWord SET
	[Word] = @Word,
	[IsCommon] = @IsCommon,
	[HitCount] = @HitCount
WHERE
	[SearchWordsID] = @SearchWordsID







GO



CREATE procedure {databaseOwner}{objectQualifier}GetFiles

@PortalId   int,
@Folder nvarchar(200)
as

select FileId,
       PortalId,
       FileName,
       Extension,
       Size,
       WIdth,
       Height,
       ContentType
from {objectQualifier}Files
where  ((PortalId = @PortalId AND Folder=@Folder) or (@PortalId is null and PortalId is null AND Folder=@Folder))
order by FileName







GO

create procedure {databaseOwner}{objectQualifier}GetSiteLog4

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

as

select Referrer,
 'Requests' = count(*),
 'LastRequest' = max(DateTime)
from {objectQualifier}SiteLog
where {objectQualifier}SiteLog.PortalId = @PortalId
and {objectQualifier}SiteLog.DateTime between @StartDate and @EndDate
and Referrer is not null
and Referrer not like '%' + @PortalAlias + '%'
group by Referrer
order by Requests desc








GO

create procedure {databaseOwner}{objectQualifier}DeleteModuleControl

@ModuleControlId int

as

delete
from   {objectQualifier}ModuleControls
where  ModuleControlId = @ModuleControlId








GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetSearchItemWordPositionBySearchItemWord
	@SearchItemWordID int
AS

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







GO

create procedure {databaseOwner}{objectQualifier}DeleteSkin

@SkinRoot               nvarchar(50),
@PortalID		int,
@SkinType               int

as

delete
from   {objectQualifier}Skins
where   SkinRoot = @SkinRoot
and     SkinType = @SkinType
and    ((PortalID is null and @PortalID is null) or (PortalID = @PortalID))








GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DeleteSearchWord
	@SearchWordsID int
AS

DELETE FROM {databaseOwner}{objectQualifier}SearchWord
WHERE
	[SearchWordsID] = @SearchWordsID







GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}AddScheduleHistory
@ScheduleID int,
@StartDate datetime,
@Server varchar(150)
AS
INSERT INTO {databaseOwner}{objectQualifier}ScheduleHistory
(ScheduleID,
StartDate,
Server)
VALUES
(@ScheduleID,
@StartDate,
@Server)

select SCOPE_IDENTITY()

GO