04.00.04.sqldataprovider

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

  @ModuleDefId,
  @ModuleTitle,
  @AllTabs,
  @Header,
  @Footer, 
  @StartDate,
  @EndDate,
  @InheritViewPermissions,
  @IsDeleted
)

select SCOPE_IDENTITY()

GO

/* Update UpdateModule Procedure */
/*********************************/

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}UpdateModule]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure {databaseOwner}{objectQualifier}UpdateModule
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}UpdateModule

	@ModuleId               int,
	@ModuleTitle            nvarchar(256),
	@AllTabs                bit, 
	@Header                 ntext,
	@Footer                 ntext,
	@StartDate              datetime,
	@EndDate                datetime,
	@InheritViewPermissions	bit,
	@IsDeleted              bit

AS

UPDATE {objectQualifier}Modules
SET    ModuleTitle = @ModuleTitle,
       AllTabs = @AllTabs,
       Header = @Header,
       Footer = @Footer, 
       StartDate = @StartDate,
       EndDate = @EndDate,
       InheritViewPermissions = @InheritViewPermissions,
       IsDeleted = @IsDeleted
WHERE  ModuleId = @ModuleId

GO

/* Secure File Storage */
/***********************/

If (SELECT {databaseOwner}{objectQualifier}fn_GetVersion(3,2,3)) = 0
	BEGIN
		ALTER TABLE {databaseOwner}{objectQualifier}Files ADD
			FolderID int NOT NULL CONSTRAINT DF_{objectQualifier}Files_FolderID DEFAULT 0,
			Content image NULL
	END
GO

If (SELECT {databaseOwner}{objectQualifier}fn_GetVersion(3,2,3)) = 0
	BEGIN
		ALTER TABLE {databaseOwner}{objectQualifier}Files
			DROP CONSTRAINT DF_{objectQualifier}Files_FolderID
	END
GO

/* make FolderPath values consistent with Files table Folder column */
UPDATE {objectQualifier}Folders
	SET    FolderPath = FolderPath + '/'
WHERE  FolderPath <> '' AND RIGHT(FolderPath, 1) <> '/'

/* Handle Updating Files table with new FolderID column based on Folders table */
UPDATE {objectQualifier}Files
	SET    FolderID = FO.FolderID
FROM   {objectQualifier}Files F, {objectQualifier}Folders FO
WHERE F.Folder = FO.FolderPath
	AND ( ( F.PortalID = FO.PortalID ) OR ( F.PortalID is NULL AND  FO.PortalID is NULL ) )
GO

If (SELECT {databaseOwner}{objectQualifier}fn_GetVersion(3,2,3)) = 0
	BEGIN
		ALTER TABLE {databaseOwner}{objectQualifier}Files ADD CONSTRAINT
			FK_{objectQualifier}Files_{objectQualifier}Folders FOREIGN KEY
			(
			FolderID
			) REFERENCES {databaseOwner}{objectQualifier}Folders
			(
			FolderID
			)
	END
GO

If (SELECT {databaseOwner}{objectQualifier}fn_GetVersion(3,2,3)) = 0
	BEGIN
		ALTER TABLE {databaseOwner}{objectQualifier}Folders ADD
			StorageLocation int NOT NULL CONSTRAINT DF_{objectQualifier}Folders_StorageLocation DEFAULT 0,
			IsProtected bit NOT NULL CONSTRAINT DF_{objectQualifier}Folders_IsProtected DEFAULT 0,
			IsCached bit NOT NULL CONSTRAINT DF_{objectQualifier}Folders_IsCached DEFAULT 0
	END
GO

/* This sets all portal root folders to protected */
update {objectQualifier}Folders
Set     IsProtected = 1
WHERE
	FolderPath = ''
GO

/* This sets all containers root folders to protected */
update {objectQualifier}Folders
Set     IsProtected = 1
WHERE
	FolderPath = 'Containers/'
GO

/* This sets all skins root folders to protected */
update {objectQualifier}Folders
Set     IsProtected = 1
WHERE
	FolderPath = 'Skins/'
GO

/* This sets all cache root folders to protected */
UPDATE {objectQualifier}Folders
	SET     IsProtected = 1
WHERE
	FolderPath = 'Cache/'
GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}AddFile]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}AddFile]
GO

CREATE procedure {databaseOwner}{objectQualifier}AddFile

@PortalId    int,
@FileName    nvarchar(100),
@Extension   nvarchar(100),
@Size        int,
@WIdth       int,
@Height      int,
@ContentType nvarchar(200),
@Folder      nvarchar(200),
@FolderID    int

as

insert into {objectQualifier}Files ( 
  PortalId,
  FileName,
  Extension,
  Size,
  WIdth,
  Height,
  ContentType,
  Folder,
  FolderID
)
values (
  @PortalId,
  @FileName,
  @Extension,
  @Size,
  @WIdth,
  @Height,
  @ContentType,
  @Folder,
  @FolderID
)

select SCOPE_IDENTITY()

GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}GetAllFiles]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}GetAllFiles]
GO

CREATE procedure {databaseOwner}[{objectQualifier}GetAllFiles]

AS

SELECT
	FileId,
             FO.PortalId,
             FileName,
             Extension,
             Size,
             Width,
             Height,
             ContentType,
             F.FolderID,
             'Folder' = FolderPath,
	     StorageLocation,
             IsCached
FROM 
	{objectQualifier}Files F

INNER JOIN 
	{objectQualifier}Folders FO on F.FolderID = FO.FolderID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}GetFile]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}GetFile]
GO

CREATE procedure {databaseOwner}{objectQualifier}GetFile

@FileName  nvarchar(100),
@PortalId  int,
@FolderID  int

as

select FileId,
       {objectQualifier}Folders.PortalId,
       FileName,
       Extension,
       Size,
       Width,
       Height,
       ContentType,
       {objectQualifier}Files.FolderID,
       'Folder' = FolderPath,
       StorageLocation,
       IsCached
from {objectQualifier}Files
inner join {objectQualifier}Folders on {objectQualifier}Files.FolderID = {objectQualifier}Folders.FolderID
where  FileName = @FileName 
and    {objectQualifier}Files.FolderID = @FolderID
and    (({objectQualifier}Folders.PortalId = @PortalId) or (@PortalId is null and {objectQualifier}Folders.PortalId is null))

GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}GetFiles]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}GetFiles]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetFiles]

@PortalId   int,
@FolderID   int

AS

SELECT 
	FileId,
             FO.PortalId,
             FileName,
             Extension,
             Size,
             Width,
             Height,
             ContentType,
             F.FolderID,
	     'Folder' = FolderPath,
             StorageLocation,
             IsCached
FROM 
	{objectQualifier}Files F
INNER JOIN 
	{objectQualifier}Folders FO on F.FolderID = FO.FolderID
WHERE   
	F.FolderID = @FolderID
AND     
	((FO.PortalId = @PortalId) or (@PortalId is NULL AND FO.PortalId is NULL))
ORDER BY FileName
GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}GetFileById]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}GetFileById]
GO

create procedure {databaseOwner}{objectQualifier}GetFileById

@FileId   int,
@PortalId int

as

select FileId,
       {objectQualifier}Folders.PortalId,
       FileName,
       Extension,
       Size,
       Width,
       Height,
       ContentType,
       {objectQualifier}Files.FolderID,
       'Folder' = FolderPath,
       StorageLocation,
       IsCached
from   {objectQualifier}Files
inner join {objectQualifier}Folders on {objectQualifier}Files.FolderID = {objectQualifier}Folders.FolderID
where  FileId = @FileId
and    (({objectQualifier}Folders.PortalId = @PortalId) or (@PortalId is null and {objectQualifier}Folders.PortalId is null))

GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}UpdateFile]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}UpdateFile]
GO

CREATE procedure {databaseOwner}{objectQualifier}UpdateFile

	@FileId      int,
	@FileName    nvarchar(100),
	@Extension   nvarchar(100),
	@Size        int,
	@WIdth       int,
	@Height      int,
	@ContentType nvarchar(200),
	@Folder      nvarchar(200),
	@FolderID    int

as

update {objectQualifier}Files
set    FileName = @FileName,
       Extension = @Extension,
       Size = @Size,
       WIdth = @WIdth,
       Height = @Height,
       ContentType = @ContentType,
       Folder = @Folder,
       FolderID = @FolderID
where  FileId = @FileId

GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}DeleteFile]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}DeleteFile]
GO

create procedure {databaseOwner}{objectQualifier}DeleteFile

@PortalId int,
@FileName nvarchar(100),
@FolderID int

AS

delete 
from   {objectQualifier}Files
where  FileName = @FileName
and    FolderID = @FolderID
and    ((PortalId = @PortalId) or (@PortalId is null and PortalId is null))

GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}UpdateFileContent]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}UpdateFileContent]
GO

CREATE procedure {databaseOwner}{objectQualifier}UpdateFileContent

@FileId      int,
@Content     image

as

update {objectQualifier}Files
set    Content = @Content
where  FileId = @FileId

GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}GetFileContent]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}GetFileContent]
GO

CREATE procedure {databaseOwner}{objectQualifier}GetFileContent

@FileId   int,
@PortalId int

as

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

GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}GetFolders]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}GetFolders]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetFolders]
	@PortalID int,
	@FolderID int,
	@FolderPath nvarchar(300)
AS
SELECT *
	FROM {objectQualifier}Folders
	WHERE ((PortalID = @PortalID) or (PortalID is null and @PortalID is null))
		AND (FolderID = @FolderID or @FolderID = -1)
		AND (FolderPath = @FolderPath or @FolderPath = '')
	ORDER BY FolderPath
GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}AddFolder]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}AddFolder]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}AddFolder]
	@PortalID int,
	@FolderPath varchar(300),
	@StorageLocation int,
	@IsProtected bit,
    @IsCached bit
AS
	IF NOT EXISTS (SELECT 1 FROM {objectQualifier}Folders WHERE PortalID = @PortalID and FolderPath = @FolderPath)
	BEGIN
		INSERT INTO {objectQualifier}Folders
		(PortalID, FolderPath, StorageLocation, IsProtected, IsCached)
		VALUES
		(@Por