02.02.00.sqldataprovider

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

GO

DROP TABLE {databaseOwner}{objectQualifier}VendorLog
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DeleteFolderPermissionsByFolderPath
	@PortalID int,
	@FolderPath varchar(300)
AS
DECLARE @FolderID int
SELECT @FolderID = FolderID FROM {databaseOwner}{objectQualifier}Folders
WHERE FolderPath = @FolderPath
AND ((PortalID = @PortalID) or (PortalID is null and @PortalID is null))

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


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

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


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

SELECT
	FP.[FolderPermissionID],
	F.[FolderPath],
	P.[PermissionID],
	FP.[RoleID],
	case FP.RoleID
		when -1 then 'All Users'
		when -2 then 'Superuser'
		when -3 then 'Unauthenticated Users'
		else 	R.RoleName
	end
	'RoleName',
	FP.[AllowAccess],
	P.[PermissionCode],
	P.[PermissionKey],
	P.[PermissionName]
FROM
	{databaseOwner}{objectQualifier}FolderPermission FP
LEFT JOIN
	{databaseOwner}{objectQualifier}Folders F
ON	FP.FolderID = F.FolderID
LEFT JOIN
	{databaseOwner}{objectQualifier}Permission P
ON	FP.PermissionID = P.PermissionID
LEFT JOIN
	{databaseOwner}{objectQualifier}Roles R
ON	FP.RoleID = R.RoleID
WHERE
	FP.FolderPermissionID = @FolderPermissionID
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}AddFolderPermission
	@FolderID int,
	@PermissionID int,
	@RoleID int,
	@AllowAccess bit
AS

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

select SCOPE_IDENTITY()

GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}UpdateFolderPermission
	@FolderPermissionID int, 
	@FolderID int, 
	@PermissionID int, 
	@RoleID int ,
	@AllowAccess bit
AS

UPDATE {databaseOwner}{objectQualifier}FolderPermission SET
	[FolderID] = @FolderID,
	[PermissionID] = @PermissionID,
	[RoleID] = @RoleID,
	[AllowAccess] = @AllowAccess
WHERE
	[FolderPermissionID] = @FolderPermissionID

GO

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

SELECT
	FP.[FolderPermissionID],
	F.[FolderPath],
	P.[PermissionID],
	FP.[RoleID],
	case FP.RoleID
		when -1 then 'All Users'
		when -2 then 'Superuser'		
		when -3 then 'Unauthenticated Users'
		else 	R.RoleName
	end
	'RoleName',
	FP.[AllowAccess],
	P.[PermissionCode],
	P.[PermissionKey],
	P.[PermissionName]
FROM
	{databaseOwner}{objectQualifier}FolderPermission FP
LEFT JOIN
	{databaseOwner}{objectQualifier}Folders F
ON	FP.FolderID = F.FolderID	
LEFT JOIN
	{databaseOwner}{objectQualifier}Permission P
ON	FP.PermissionID = P.PermissionID
LEFT JOIN
	{databaseOwner}{objectQualifier}Roles R
ON	FP.RoleID = R.RoleID
WHERE
	( (F.[FolderPath] = @FolderPath and ((F.[PortalID] = @PortalID) or (F.[PortalID] is null and @PortalID is null)))
          or (F.FolderPath is null and P.PermissionCode = 'SYSTEM_FOLDER') )
AND	(P.[PermissionID] = @PermissionID or @PermissionID = -1)

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}GetFolders
	@PortalID int,
	@FolderID int,
	@FolderPath varchar(300)
AS
	SELECT *
	FROM {databaseOwner}{objectQualifier}Folders
	WHERE ((PortalID = @PortalID) or (PortalID is null and @PortalID is null))
	AND (FolderID = @FolderID or @FolderID = -1)
	AND (FolderPath = @FolderPath or @FolderPath = '')
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}AddFolder
	@PortalID int,
	@FolderPath varchar(300)
AS
	IF NOT EXISTS (SELECT 1 FROM {databaseOwner}{objectQualifier}Folders WHERE PortalID = @PortalID and FolderPath = @FolderPath)
	BEGIN
		INSERT INTO {databaseOwner}{objectQualifier}Folders
		(PortalID, FolderPath)
		VALUES
		(@PortalID, @FolderPath)
		
		SELECT SCOPE_IDENTITY()
	END
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}DeleteFolder
	@PortalID int,
	@FolderID int,
	@FolderPath varchar(300)
AS
	DELETE FROM {databaseOwner}{objectQualifier}Folders
	WHERE ((PortalID = @PortalID) or (PortalID is null and @PortalID is null))
	AND FolderPath = @FolderPath

GO

UPDATE {databaseOwner}{objectQualifier}Schedule
SET TypeFullName = 'DotNetNuke.Entities.Users.PurgeUsersOnline, DotNetNuke'
WHERE lower(TypeFullName) = 'dotnetnuke.purgeusersonline, dotnetnuke'

GO

UPDATE {databaseOwner}{objectQualifier}Schedule
SET TypeFullName = 'DotNetNuke.Services.Log.SiteLog.PurgeSiteLog, DOTNETNUKE'
WHERE lower(TypeFullName) = 'dotnetnuke.purgesitelog, dotnetnuke'

GO

UPDATE {databaseOwner}{objectQualifier}Schedule
SET TypeFullName = 'DotNetNuke.Services.Scheduling.PurgeScheduleHistory, DOTNETNUKE'
WHERE lower(TypeFullName) = 'dotnetnuke.scheduling.purgeschedulehistory, dotnetnuke'

GO

UPDATE {databaseOwner}{objectQualifier}Schedule
SET TypeFullName = 'DotNetNuke.Entities.Users.PurgeUsersOnline, DOTNETNUKE'
WHERE lower(TypeFullName) = 'dotnetnuke.purgeusersonline, dotnetnuke'

GO

UPDATE {databaseOwner}{objectQualifier}Schedule
SET TypeFullName = 'DotNetNuke.Services.Log.EventLog.PurgeLogBuffer, DOTNETNUKE.XMLLOGGINGPROVIDER'
WHERE lower(TypeFullName) = 'dotnetnuke.logging.purgelogbuffer, dotnetnuke.xmlloggingprovider'

GO

UPDATE {databaseOwner}{objectQualifier}Schedule
SET TypeFullName = 'DotNetNuke.Services.Log.EventLog.SendLogNotifications, DOTNETNUKE.XMLLOGGINGPROVIDER'
WHERE lower(TypeFullName) = 'dotnetnuke.logging.sendlognotifications, dotnetnuke.xmlloggingprovider'

GO

ALTER TABLE {databaseOwner}{objectQualifier}ModuleEvents
	DROP CONSTRAINT FK_{objectQualifier}Events_{objectQualifier}Modules
GO

CREATE TABLE {databaseOwner}{objectQualifier}Events
	(
	ItemID int NOT NULL IDENTITY (0, 1),
	ModuleID int NOT NULL,
	Description nvarchar(2000) NOT NULL,
	DateTime datetime NOT NULL,
	Title nvarchar(100) NOT NULL,
	ExpireDate datetime NULL,
	CreatedByUser nvarchar(200) NOT NULL,
	CreatedDate datetime NOT NULL,
	Every int NULL,
	Period char(1) NULL,
	IconFile nvarchar(256) NULL,
	AltText nvarchar(50) NULL
	)  ON [PRIMARY]
GO

SET IDENTITY_INSERT {databaseOwner}{objectQualifier}Events ON
GO

IF EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}ModuleEvents)
	 EXEC('INSERT INTO {databaseOwner}{objectQualifier}Events (ItemID, ModuleID, Description, DateTime, Title, ExpireDate, CreatedByUser, CreatedDate, Every, Period, IconFile, AltText)
		SELECT ItemID, ModuleID, Description, DateTime, Title, ExpireDate, CreatedByUser, CreatedDate, Every, Period, IconFile, AltText FROM {databaseOwner}{objectQualifier}ModuleEvents TABLOCKX')
GO

SET IDENTITY_INSERT {databaseOwner}{objectQualifier}Events OFF
GO

DROP TABLE {databaseOwner}{objectQualifier}ModuleEvents
GO

ALTER TABLE {databaseOwner}{objectQualifier}Events ADD CONSTRAINT
	PK_{objectQualifier}Events PRIMARY KEY NONCLUSTERED 
	(
	ItemID
	) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX IX_{objectQualifier}Events ON {databaseOwner}{objectQualifier}Events
	(
	ModuleID
	) ON [PRIMARY]
GO

ALTER TABLE {databaseOwner}{objectQualifier}Events WITH NOCHECK ADD CONSTRAINT
	FK_{objectQualifier}Events_{objectQualifier}Modules FOREIGN KEY
	(
	ModuleID
	) REFERENCES {databaseOwner}{objectQualifier}Modules
	(
	ModuleID
	) ON DELETE CASCADE
	 NOT FOR REPLICATION

GO

drop procedure {databaseOwner}{objectQualifier}AddModuleEvent
GO

create procedure {databaseOwner}{objectQualifier}AddEvent

@ModuleID    int,
@Description nvarchar(2000),
@DateTime    datetime,
@Title       nvarchar(100),
@ExpireDate  datetime = null,
@UserName    nvarchar(200),
@Every       int,
@Period      char(1),
@IconFile    nvarchar(256),
@AltText     nvarchar(50)

as

insert into {objectQualifier}Events ( 
  ModuleID,
  Description,
  DateTime,
  Title,
  ExpireDate,
  CreatedByUser,
  CreatedDate,
  Every,
  Period,
  IconFile,
  AltText
)
values (
  @ModuleID,
  @Description,
  @DateTime,
  @Title,
  @ExpireDate,
  @UserName,
  getdate(),
  @Every,
  @Period,
  @IconFile,
  @AltText
)

select SCOPE_IDENTITY()

GO

drop procedure {databaseOwner}{objectQualifier}DeleteModuleEvent
GO

create procedure {databaseOwner}{objectQualifier}DeleteEvent

@ItemId int

as

delete
from   {objectQualifier}Events
where  ItemId = @ItemId

GO

drop procedure {databaseOwner}{objectQualifier}GetModuleEvent
GO

create procedure {databaseOwner}{objectQualifier}GetEvent

@ItemId   int,
@ModuleId int

as

select ItemId,
       Description,
       DateTime,
       Title,
       ExpireDate,
       'CreatedByUser' = FirstName + ' ' + LastName,
       {objectQualifier}Events.CreatedDate,
       Every,
       Period,
       IconFile,
       AltText
from   {objectQualifier}Events
left outer join {objectQualifier}Users on {objectQualifier}Events.CreatedByUser = {objectQualifier}Users.UserId
where  ItemId = @ItemId
and    ModuleId = @ModuleId

GO

drop procedure {databaseOwner}{objectQualifier}GetModuleEvents
GO

create procedure {databaseOwner}{objectQualifier}GetEvents

@ModuleId int

as

select ItemId,
       Description,
       DateTime,
       Title,
       ExpireDate,
       CreatedByUser,
       CreatedDate,
       IconFile,
       AltText,
       'MaxWIdth' = (select max(WIdth) from {objectQualifier}Events left outer join {objectQualifier}Files on {objectQualifier}Events.IconFile = {objectQualifier}Files.FileName where ModuleId = @ModuleId and (ExpireDate > getdate() or ExpireDate is null))
from   {objectQualifier}Events
where  ModuleId = @ModuleId
and    (ExpireDate > getdate() or ExpireDate is null)
order by DateTime


GO

drop procedure {databaseOwner}{objectQualifier}GetModuleEventsByDate
GO

create procedure {databaseOwner}{objectQualifier}GetEventsByDate

@ModuleId int,
@StartDate datetime,
@EndDate datetime

as

select ItemId,
       Description,
       DateTime,
       Title,
       ExpireDate,
       CreatedByUser,
       CreatedDate,
       Every,
       Period,
       IconFile,
       AltText
from   {objectQualifier}Events
where  ModuleId = @ModuleId
and    ( (Period is null and (DateTime >= @StartDate and DateTime <= @EndDate)) or Period is not null )
order by DateTime


GO

drop procedure {databaseOwner}{objectQualifier}UpdateModuleEvent
GO

create procedure {databaseOwner}{objectQualifier}UpdateEvent

@ItemId      int,
@Description nvarchar(2000),
@DateTime    datetime,
@Title       nvarchar(100),
@ExpireDate  datetime = null,
@UserName    nvarchar(200),
@Every       int,
@Period      char(1),
@IconFile    nvarchar(256),
@AltText     nvarchar(50)

as

update {objectQualifier}Events
set    Description = @Description,
       DateTime = @DateTime,
       Title = @Title,
       ExpireDate = @ExpireDate,
       CreatedByUser = @UserName,
       CreatedDate = getdate(),
       Every = @Every,
       Period = @Period,
       IconFile = @IconFile,
       AltText = @AltText
where  ItemId = @ItemId

GO


alter procedure {databaseOwner}{objectQualifier}GetUsers

@PortalId int

as

select {objectQualifier}Users.*,
       {objectQualifier}Users.FirstName + ' ' + {objectQualifier}Users.LastName as FullName,
       {objectQualifier}UserPortals.Authorized,
       {objectQualifier}UserPortals.CreatedDate,
       {objectQualifier}UserPortals.LastLoginDate
from {objectQualifier}Users 
left join {objectQualifier}UserPortals on {objectQualifier}Users.UserId = {objectQualifier}UserPortals.UserId 
where ( {objectQualifier}UserPortals.PortalId = @PortalId or @PortalId is null )
order by {objectQualifier}Users.FirstName + ' ' + {objectQualifier}Users.LastName   

GO
/************************************************************/
/*****              SqlDataProvider                     *****/
/************************************************************/