02.01.01.sqldataprovider

SharpNuke源代码

  @Clicks,
  @LastClick,
  @CreatedDate,
  @LogActivity
)

GO

create procedure {databaseOwner}{objectQualifier}UpdateUrlTracking

@PortalID     int,
@Url          nvarchar(255),
@UrlType      char(1),
@Clicks       int,
@LastClick    datetime,
@CreatedDate  datetime,
@LogActivity  bit

as

update {objectQualifier}UrlTracking
set    UrlType = @UrlType,
       Clicks = @Clicks,
       LastClick = @LastClick,
       CreatedDate = @CreatedDate,
       LogActivity = @LogActivity
where  PortalID = @PortalID
and    Url = @Url

GO

create procedure {databaseOwner}{objectQualifier}DeleteUrlTracking

@PortalID     int,
@Url          nvarchar(255)

as

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

GO

create procedure {databaseOwner}{objectQualifier}GetUrlTracking

@PortalID     int,
@Url          nvarchar(255)

as

select *
from   {objectQualifier}UrlTracking
where  PortalID = @PortalID
and    Url = @Url

GO

create procedure {databaseOwner}{objectQualifier}AddUrlLog

@UrlTrackingID int,
@ModuleID      int,
@UserID        int

as

insert into {objectQualifier}UrlLog (
  UrlTrackingID,
  ClickDate,
  UserID,
  ModuleID
)
values (
  @UrlTrackingID,
  getdate(),
  @UserID,
  @ModuleID
)

GO

create procedure {databaseOwner}{objectQualifier}GetUrlLog

@URLTrackingID int,
@ModuleID      int,
@StartDate     datetime,
@EndDate       datetime

as

select {objectQualifier}UrlLog.*,
       'FullName' = {objectQualifier}Users.FirstName + ' ' + {objectQualifier}Users.LastName,
       ModuleTitle
from   {objectQualifier}UrlLog
left outer join {objectQualifier}Users on {objectQualifier}UrlLog.UserId = {objectQualifier}Users.UserId
left outer join {objectQualifier}Modules on {objectQualifier}UrlLog.ModuleId = {objectQualifier}Modules.ModuleId
where  UrlTrackingID = @UrlTrackingID
and    (({objectQualifier}UrlLog.ModuleID = @ModuleID) or @ModuleID is null)
and    ((ClickDate >= @StartDate) or @StartDate is null)
and    ((ClickDate <= @EndDate) or @EndDate is null)
order by ClickDate

GO

DROP TABLE {databaseOwner}{objectQualifier}ClickLog
GO

DROP PROCEDURE {databaseOwner}{objectQualifier}GetClicks
GO

DROP PROCEDURE {databaseOwner}{objectQualifier}UpdateClicks
GO

CREATE TABLE {databaseOwner}{objectQualifier}Schedule (
	[ScheduleID] [int] IDENTITY (1, 1) NOT NULL ,
	[TypeFullName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[TimeLapse] [int] NOT NULL ,
	[TimeLapseMeasurement] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[RetryTimeLapse] [int] NOT NULL ,
	[RetryTimeLapseMeasurement] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[RetainHistoryNum] [int] NOT NULL ,
	[AttachToEvent] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[CatchUpEnabled] [bit] NOT NULL ,
	[Enabled] [bit] NOT NULL ,
	[ObjectDependencies] [varchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE {databaseOwner}{objectQualifier}ScheduleHistory (
	[ScheduleHistoryID] [int] IDENTITY (1, 1) NOT NULL ,
	[ScheduleID] [int] NOT NULL ,
	[StartDate] [datetime] NOT NULL ,
	[EndDate] [datetime] NULL ,
	[Succeeded] [bit] NULL ,
	[LogNotes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[NextStart] [datetime] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

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


CREATE PROCEDURE {databaseOwner}{objectQualifier}AddSchedule
@TypeFullName varchar(200)
,@TimeLapse int
,@TimeLapseMeasurement varchar(2)
,@RetryTimeLapse int
,@RetryTimeLapseMeasurement varchar(2)
,@RetainHistoryNum int
,@AttachToEvent varchar(50)
,@CatchUpEnabled bit
,@Enabled bit
,@ObjectDependencies varchar(300)
AS
INSERT INTO {databaseOwner}{objectQualifier}Schedule
(TypeFullName
,TimeLapse
,TimeLapseMeasurement
,RetryTimeLapse
,RetryTimeLapseMeasurement
,RetainHistoryNum
,AttachToEvent
,CatchUpEnabled
,Enabled
,ObjectDependencies
)
VALUES
(@TypeFullName
,@TimeLapse
,@TimeLapseMeasurement
,@RetryTimeLapse
,@RetryTimeLapseMeasurement
,@RetainHistoryNum
,@AttachToEvent
,@CatchUpEnabled
,@Enabled
,@ObjectDependencies
)
GO

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

select SCOPE_IDENTITY()

GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DeleteSchedule
@ScheduleID int
AS
DELETE FROM {databaseOwner}{objectQualifier}Schedule
WHERE ScheduleID = @ScheduleID
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetSchedule
AS
SELECT S.ScheduleID, S.TypeFullName, S.TimeLapse, S.TimeLapseMeasurement,  S.RetryTimeLapse, S.RetryTimeLapseMeasurement, S.ObjectDependencies, S.AttachToEvent, S.RetainHistoryNum, S.CatchUpEnabled, S.Enabled, ISNULL(SH.NextStart, getDate()) 'NextStart'
FROM {databaseOwner}{objectQualifier}Schedule S
LEFT JOIN {databaseOwner}{objectQualifier}ScheduleHistory SH
ON S.ScheduleID = SH.ScheduleID
WHERE SH.ScheduleHistoryID = (SELECT TOP 1 S1.ScheduleHistoryID FROM {databaseOwner}{objectQualifier}ScheduleHistory S1 WHERE S1.ScheduleID = S.ScheduleID ORDER BY S1.NextStart DESC)
OR  SH.ScheduleHistoryID IS NULL
GROUP BY S.ScheduleID, S.TypeFullName, S.TimeLapse, S.TimeLapseMeasurement,  S.RetryTimeLapse, S.RetryTimeLapseMeasurement, S.ObjectDependencies, S.AttachToEvent, S.RetainHistoryNum, S.CatchUpEnabled, S.Enabled, SH.NextStart
GO

GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetScheduleByEvent
@EventName varchar(50)
AS
SELECT S.ScheduleID, S.TypeFullName, S.TimeLapse, S.TimeLapseMeasurement,  S.RetryTimeLapse, S.RetryTimeLapseMeasurement, S.ObjectDependencies, S.AttachToEvent, S.RetainHistoryNum, S.CatchUpEnabled, S.Enabled
FROM {databaseOwner}{objectQualifier}Schedule S
WHERE S.AttachToEvent = @EventName
GROUP BY S.ScheduleID, S.TypeFullName, S.TimeLapse, S.TimeLapseMeasurement,  S.RetryTimeLapse, S.RetryTimeLapseMeasurement, S.ObjectDependencies, S.AttachToEvent, S.RetainHistoryNum, S.CatchUpEnabled, S.Enabled


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}GetScheduleItemSettings 
@ScheduleID int
AS
SELECT *
FROM {databaseOwner}{objectQualifier}ScheduleItemSettings
WHERE ScheduleID = @ScheduleID

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}UpdateSchedule
@ScheduleID int
,@TypeFullName varchar(200)
,@TimeLapse int
,@TimeLapseMeasurement varchar(2)
,@RetryTimeLapse int
,@RetryTimeLapseMeasurement varchar(2)
,@RetainHistoryNum int
,@AttachToEvent varchar(50)
,@CatchUpEnabled bit
,@Enabled bit
,@ObjectDependencies varchar(300)
AS
UPDATE {databaseOwner}{objectQualifier}Schedule
SET TypeFullName = @TypeFullName
,TimeLapse = @TimeLapse
,TimeLapseMeasurement = @TimeLapseMeasurement
,RetryTimeLapse = @RetryTimeLapse
,RetryTimeLapseMeasurement = @RetryTimeLapseMeasurement
,RetainHistoryNum = @RetainHistoryNum
,AttachToEvent = @AttachToEvent
,CatchUpEnabled = @CatchUpEnabled
,Enabled = @Enabled
,ObjectDependencies = @ObjectDependencies
WHERE ScheduleID = @ScheduleID

GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetScheduleHistory
@ScheduleID int
AS
SELECT S.ScheduleID, S.TypeFullName, SH.StartDate, SH.EndDate, SH.Succeeded, SH.LogNotes, SH.NextStart
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}UpdateScheduleHistory
@ScheduleHistoryID int,
@EndDate datetime,
@Succeeded bit,
@LogNotes ntext,
@NextStart datetime
AS
UPDATE {databaseOwner}{objectQualifier}ScheduleHistory
SET	EndDate = @EndDate,
	Succeeded = @Succeeded,
	LogNotes = @LogNotes,
	NextStart = @NextStart
WHERE ScheduleHistoryID = @ScheduleHistoryID

GO

INSERT INTO {databaseOwner}{objectQualifier}Schedule VALUES ('DotNetNuke.Entities.Users.PurgeUsersOnline, DOTNETNUKE', 1, 'm', 5, 'm', 60, '', 0, 1, 'UsersOnline')
GO
INSERT INTO {databaseOwner}{objectQualifier}Schedule VALUES ('DotNetNuke.Services.Log.SiteLog.PurgeSiteLog, DOTNETNUKE', 1, 'd', 2, 'h', 10, '', 0, 1, 'SiteLog')
GO
INSERT INTO {databaseOwner}{objectQualifier}Schedule VALUES ('DotNetNuke.Services.Scheduling.PurgeScheduleHistory, DOTNETNUKE', 1, 'm', 5, 'm', 60, '', 0, 1, 'ScheduleHistory')
GO
INSERT INTO {databaseOwner}{objectQualifier}Schedule VALUES ('DotNetNuke.Services.Log.EventLog.PurgeLogBuffer, DOTNETNUKE.XMLLOGGINGPROVIDER', 1, 'm', 5, 'm', 60, '', 0, 1, 'XMLLoggingProvider')
GO
INSERT INTO {databaseOwner}{objectQualifier}Schedule VALUES ('DotNetNuke.Services.Log.EventLog.SendLogNotifications, DOTNETNUKE.XMLLOGGINGPROVIDER', 5, 'm', 10, 'm', 60, '', 0, 1, 'XMLLoggingProvider')
GO
DELETE FROM {databaseOwner}{objectQualifier}Modules WHERE ModuleTitle='Exception Viewer'
GO
DELETE FROM {databaseOwner}{objectQualifier}Tabs WHERE TabName='Exception Viewer'
GO
DELETE FROM {databaseOwner}{objectQualifier}DesktopModules WHERE FriendlyName='Exception Viewer'
GO
DELETE FROM {databaseOwner}{objectQualifier}ModuleControls WHERE ControlSrc='Admin/Portal/ExceptionViewer.ascx'
GO
DELETE FROM {databaseOwner}{objectQualifier}ModuleDefinitions WHERE FriendlyName='Exception Viewer'
GO


drop procedure {databaseOwner}{objectQualifier}GetPremiumDesktopModules
GO

create procedure {databaseOwner}{objectQualifier}GetPremiumDesktopModules
    
@PortalId int

as

select distinct({objectQualifier}DesktopModules.DesktopModuleId) as DesktopModuleId,
       PortalModuleDefinitionId,
       {objectQualifier}DesktopModules.FriendlyName,
       {objectQualifier}DesktopModules.Description,
       'HostFee' = case when HostFee is null then 0 else HostFee end
from {objectQualifier}DesktopModules
left outer join {objectQualifier}PortalModuleDefinitions on {objectQualifier}DesktopModules.DesktopModuleId = {objectQualifier}PortalModuleDefinitions.DesktopModuleId AND PortalId = @PortalId
where  {objectQualifier}DesktopModules.IsPremium = 1
order  by FriendlyName
GO


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