03.00.01.sqldataprovider

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

@PortalId int

as

select {objectQualifier}Modules.ModuleId
from   {objectQualifier}Modules
inner join {objectQualifier}ModuleDefinitions on {objectQualifier}Modules.ModuleDefId = {objectQualifier}ModuleDefinitions.ModuleDefId
where  {objectQualifier}Modules.PortalId = @PortalId
and    {objectQualifier}ModuleDefinitions.FriendlyName = @FriendlyName

GO

drop procedure {databaseOwner}{objectQualifier}UpdateModuleOrder
GO

create procedure {databaseOwner}{objectQualifier}UpdateModuleOrder

@TabId              int,
@ModuleId           int,
@ModuleOrder        int,
@PaneName           nvarchar(50)

as

update {objectQualifier}TabModules
set    ModuleOrder = @ModuleOrder,
       PaneName = @PaneName
where  TabId = @TabId
and    ModuleId = @ModuleId

GO

drop procedure {databaseOwner}{objectQualifier}UpdateTabModuleOrder
GO

drop procedure {databaseOwner}{objectQualifier}GetModulePermissionsByPortal
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetModulePermissionsByPortal
	
@PortalID int

AS

select MP.[ModulePermissionID],
       MP.[ModuleID],
       P.[PermissionID],
       MP.[RoleID],
       case MP.RoleID
         when -1 then 'All Users'
         when -2 then 'Superuser'
         when -3 then 'Unauthenticated Users'
         else R.RoleName
       end
       'RoleName',
       MP.[AllowAccess],
       P.[PermissionCode],
       P.[ModuleDefID],
       P.[PermissionKey],
       P.[PermissionName]
from   {databaseOwner}{objectQualifier}ModulePermission MP
inner join {databaseOwner}{objectQualifier}Modules M on MP.ModuleID = M.ModuleID
left join {databaseOwner}{objectQualifier}Permission P on MP.PermissionID = P.PermissionID
left join {databaseOwner}{objectQualifier}ModuleDefinitions MD on P.ModuleDefID = MD.ModuleDefID
left join {databaseOwner}{objectQualifier}Roles R on MP.RoleID = R.RoleID
where  M.PortalID = @PortalID

GO

drop procedure {databaseOwner}{objectQualifier}GetSearchResults
GO

CREATE  PROCEDURE {databaseOwner}{objectQualifier}GetSearchResults
	@Word nVarChar(100)
AS

	/*****************************************************************************
	 *  This procedure applies filters to the SearchItems to make sure that only
	 *  active Search content is returned.  All security checks will be done in 
	 *  the Business Logic.
	 *****************************************************************************/
	DECLARE @TempList table
	(
		Word nvarchar(100),
		Occurences int,
		Relevance int,
		ModuleID int,
		TabID int,
		ModStartDate datetime,
		ModEndDate datetime,
		TabStartDate datetime,
		TabEndDate datetime,
		Title varchar(200),
		Link varchar(256),
		Description varchar(500),
		Author int,
		PubDate datetime,
		Content text,
		SearchKey varchar(100)
	)

	INSERT @TempList
	SELECT    sw.Word, siw.Occurrences, siw.Occurrences + 1000 as Relevance, m.ModuleID, tm.TabID,
						ISNULL(m.StartDate, GETDATE() - 1) AS ModStartDate, ISNULL(m.EndDate, GETDATE() + 1) AS ModEndDate, 
						ISNULL(t.StartDate, GETDATE() - 1) AS TabStartDate, ISNULL(t.EndDate, GETDATE() + 1) AS TabEndDate, 
						si.Title, si.Link, si.Description, si.Author, si.PubDate, si.Content, si.SearchKey
	FROM      {databaseOwner}{objectQualifier}SearchWord sw INNER JOIN
	          {databaseOwner}{objectQualifier}SearchItemWord siw ON sw.SearchWordsID = siw.SearchWordsID INNER JOIN
	          {databaseOwner}{objectQualifier}SearchItem si ON siw.SearchItemID = si.SearchItemID INNER JOIN
	          {databaseOwner}{objectQualifier}Modules m ON si.ModuleId = m.ModuleID LEFT OUTER JOIN
	          {databaseOwner}{objectQualifier}TabModules tm ON si.ModuleId = tm.ModuleID INNER JOIN
	          {databaseOwner}{objectQualifier}Tabs t ON tm.TabID = t.TabID
	WHERE     (sw.Word = @Word) AND (t.IsDeleted = 0) AND (m.IsDeleted = 0)

	SELECT Word, Occurences, Relevance, ModuleID, TabID, Title, Link, Description, Pubdate, SearchKey FROM @TempList 
	WHERE GetDate() between ModStartDate and ModEndDate
	AND GetDate() between TabStartDate and TabEndDate

GO

drop procedure {databaseOwner}{objectQualifier}GetDefaultLanguageByModule
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetDefaultLanguageByModule
(
	@ModuleList varchar(1000)
)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @TempList table
	(
		ModuleID int
	)

	DECLARE @ModuleID varchar(10), @Pos int

	SET @ModuleList = LTRIM(RTRIM(@ModuleList))+ ','
	SET @Pos = CHARINDEX(',', @ModuleList, 1)

	IF REPLACE(@ModuleList, ',', '') <> ''
	BEGIN
		WHILE @Pos > 0
		BEGIN
			SET @ModuleID = LTRIM(RTRIM(LEFT(@ModuleList, @Pos - 1)))
			IF @ModuleID <> ''
			BEGIN
				INSERT INTO @TempList (ModuleID) VALUES (CAST(@ModuleID AS int)) 
			END
			SET @ModuleList = RIGHT(@ModuleList, LEN(@ModuleList) - @Pos)
			SET @Pos = CHARINDEX(',', @ModuleList, 1)

		END
	END	

SELECT DISTINCT m.ModuleID, p.DefaultLanguage
FROM            {objectQualifier}Modules  m
INNER JOIN      {objectQualifier}Portals p ON p.PortalID = m.PortalID
WHERE		m.ModuleID in (SELECT ModuleID FROM @TempList)
ORDER BY        m.ModuleID	
		
END
GO

drop procedure {databaseOwner}{objectQualifier}GetSearchResultModules
GO

drop procedure {databaseOwner}{objectQualifier}GetSiteModule
GO

create procedure {databaseOwner}{objectQualifier}GetModuleByDefinition

@PortalId int,
@FriendlyName nvarchar(128)

as

select {objectQualifier}Modules.*,
       {objectQualifier}TabModules.*,
       {objectQualifier}DesktopModules.*
from   {objectQualifier}Modules
inner join {objectQualifier}ModuleDefinitions on {objectQualifier}Modules.ModuleDefId = {objectQualifier}ModuleDefinitions.ModuleDefId
inner join {objectQualifier}DesktopModules on {objectQualifier}ModuleDefinitions.DesktopModuleId = {objectQualifier}DesktopModules.DesktopModuleId
left outer join {objectQualifier}TabModules on {objectQualifier}Modules.ModuleID = {objectQualifier}TabModules.ModuleID
where  (({objectQualifier}Modules.PortalId = @PortalId) or ({objectQualifier}Modules.PortalId is null and @PortalID is null))
and    {objectQualifier}ModuleDefinitions.FriendlyName = @FriendlyName

GO

CREATE TABLE {databaseOwner}{objectQualifier}TabModuleSettings
	(
	TabModuleID int NOT NULL,
	SettingName nvarchar(50) NOT NULL,
	SettingValue nvarchar(2000) NOT NULL
	)  ON [PRIMARY]
GO

ALTER TABLE {databaseOwner}{objectQualifier}TabModuleSettings ADD CONSTRAINT
	PK_TabModuleSettings PRIMARY KEY CLUSTERED 
	(
	TabModuleID,
	SettingName
	) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX IX_TabModuleSettings ON {databaseOwner}{objectQualifier}TabModuleSettings
	(
	TabModuleID,
	SettingName
	) ON [PRIMARY]
GO

ALTER TABLE {databaseOwner}{objectQualifier}TabModuleSettings WITH NOCHECK ADD CONSTRAINT
	FK_TabModuleSettings_TabModules FOREIGN KEY
	(
        TabModuleID
	) REFERENCES {databaseOwner}{objectQualifier}TabModules
	(
        TabModuleID
	) ON DELETE CASCADE
	 NOT FOR REPLICATION

GO

create procedure {databaseOwner}{objectQualifier}GetTabModuleSettings

@TabModuleId int

as

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

GO

create procedure {databaseOwner}{objectQualifier}GetTabModuleSetting

@TabModuleId int,
@SettingName nvarchar(50)

as

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

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

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

as

update {objectQualifier}TabModuleSettings
set    SettingValue = @SettingValue
where  TabModuleId = @TabModuleId
and    SettingName = @SettingName

GO

update	{objectQualifier}ModuleControls
set		ControlKey = 'Settings', 
		ControlTitle = 'Links Settings', 
		ControlSrc = 'DesktopModules/Links/Settings.ascx'
where	ControlKey = 'ViewOptions'
and		ControlSrc = 'DesktopModules/Links/EditLinksViewOptions.ascx'

GO

update	{objectQualifier}ModuleControls
set		ControlKey = 'Settings', 
		ControlTitle = 'Events Settings', 
		ControlSrc = 'DesktopModules/Events/Settings.ascx'
where	ControlKey = 'ViewOptions'
and		ControlSrc = 'DesktopModules/Events/EditEventsViewOptions.ascx'

GO


CREATE TABLE {databaseOwner}{objectQualifier}Lists (
	[EntryID] [int] IDENTITY (1, 1) NOT NULL ,
	[ListName] [nvarchar] (50)  NOT NULL ,
	[Value] [nvarchar] (100)  NOT NULL ,
	[Text] [nvarchar] (150)  NOT NULL ,
	[ParentID] [int] NOT NULL ,	
	[Level] [int] NOT NULL ,
	[SortOrder] [int] NOT NULL ,
	[DefinitionID] [int] NOT NULL ,	
	[Description] [nvarchar] (500)  NULL 
) ON [PRIMARY]
GO

ALTER TABLE {databaseOwner}{objectQualifier}Lists ADD 
	CONSTRAINT [DF_{objectQualifier}Lists_Level] DEFAULT (0) FOR [Level],
	CONSTRAINT [DF_{objectQualifier}Lists_SortOrder] DEFAULT (0) FOR [SortOrder],
	CONSTRAINT [DF_{objectQualifier}Lists_DefinitionID] DEFAULT (0) FOR [DefinitionID],
	CONSTRAINT [DF_{objectQualifier}Lists_ParentID] DEFAULT (0) FOR [ParentID],
	CONSTRAINT [PK_{objectQualifier}Lists] PRIMARY KEY  CLUSTERED 
	(		
		[ListName],
		[Value],
		[Text],
		[ParentID]
	)  ON [PRIMARY] 
GO

CREATE procedure {databaseOwner}{objectQualifier}AddListEntry

@ListName nvarchar(50), 
@Value nvarchar(100), 
@Text nvarchar(150), 
@ParentKey nvarchar(150), 
@EnableSortOrder bit,
@DefinitionID int, 
@Description nvarchar(500)

as

DECLARE @ParentID int
DECLARE @Level int
DECLARE @SortOrder int

IF @EnableSortOrder = 1
BEGIN
	SET @SortOrder = IsNull((SELECT MAX ([SortOrder]) From {objectQualifier}Lists Where [ListName] = @ListName), 0) + 1
END
ELSE
BEGIN
	SET @SortOrder = 0
END


If @ParentKey <> ''
BEGIN
	DECLARE @ParentListName nvarchar(50)
	DECLARE @ParentValue nvarchar(100)
	SET @ParentListName = LEFT(@ParentKey, CHARINDEX( '.', @ParentKey) - 1)
	SET @ParentValue = RIGHT(@ParentKey, LEN(@ParentKey) -  CHARINDEX( '.', @ParentKey))
	SELECT @ParentID = [EntryID], @Level = ([Level] + 1) From {objectQualifier}Lists Where [ListName] = @ParentListName And [Value] = @ParentValue

	Print 'ParentListName: ' + @ParentListName
	Print 'ParentValue: ' + @ParentValue
	--Print @ParentID
END
ELSE
BEGIN
	SET @ParentID = 0
	SET @Level = 0
END

-- Check if this entry exists
If EXISTS (SELECT [EntryID] From {objectQualifier}Lists WHERE [ListName] = @ListName And [Value] = @Value And [Text] = @Text And [ParentID] = @ParentID)
BEGIN
select -1
Return 
END

insert into {objectQualifier}Lists 
	(
  	[ListName],
	[Value],
	[Text],
	[Level],
	[SortOrder],
	[DefinitionID],
	[ParentID],
	[Description]
	)
values (
	@ListName,
	@Value,
	@Text,
	@Level,
	@SortOrder,
	@DefinitionID,
	@ParentID,
	@Description  	
	)

select SCOPE_IDENTITY()
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE procedure {databaseOwner}{objectQualifier}DeleteListEntryByID

@EntryId   int,
@DeleteChild bit

as

Delete
From {objectQualifier}Lists
Where  [EntryID] = @EntryID

If @DeleteChild = 1
Begin
	Delete 
	From {objectQualifier}Lists
	Where [ParentID] = @EntryID
End


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE procedure {databaseOwner}{objectQualifier}DeleteList

@ListName nvarchar(50),
@ParentKey nvarchar(150)

as

DECLARE @EntryID int

If @ParentKey = '' 
Begin
	-- need to store entries which to be deleted to clean up their sub entries
	DECLARE allentry_cursor CURSOR FOR
	SELECT [EntryID] FROM {objectQualifier}Lists Where  [ListName] = @ListName	
	-- then delete their sub entires
	OPEN allentry_cursor
	FETCH NEXT FROM allentry_cursor INTO @EntryID
	While @@FETCH_STATUS = 0
	Begin	
		Delete {objectQualifier}Lists Where [ParentID] = @EntryID
   		FETCH NEXT FROM allentry_cursor INTO @EntryID
	End
	-- Delete entries belong to this list
	Delete {objectQualifier}Lists
	Where  [ListName] = @ListName
End
Else
Begin

	DECLARE @ParentListName nvarchar(50)
	DECLARE @ParentValue nvarchar(100)
	SET @ParentListName = LEFT(@ParentKey, CHARINDEX( '.', @ParentKey) - 1)
	SET @ParentValue = RIGHT(@ParentKey, LEN(@ParentKey) -  CHARINDEX( '.', @ParentKey))

	-- need to store entries which to be deleted to clean up their sub entries
	DECLARE selentry_cursor CURSOR FOR
	SELECT [EntryID] FROM {objectQualifier}Lists Where  [ListName] = @ListName And
	[ParentID] = (SELECT [EntryID] From {objectQualifier}Lists Where [ListName] = @ParentListName And [Value] = @ParentValue)
	-- delete their sub entires
	OPEN selentry_cursor
	FETCH NEXT FROM selentry_cursor INTO @EntryID
	While @@FETCH_STATUS = 0
	Begin	
		Delete {objectQualifier}Lists Where [ParentID] = @EntryID
   		FETCH NEXT FROM selentry_cursor INTO @EntryID
	End
	-- delete entry list
	Delete {objectQualifier}Lists 
	where  [ListName] = @ListName And
	[ParentID] = (SELECT [EntryID] From {objectQualifier}Lists Where [ListName] = @ParentListName And [Value] = @ParentValue)	
End


GO

CREATE procedure {databaseOwner}{objectQualifier}GetListEntries

@ListName nvarchar(50),
@ParentKey nvarchar(150),
@EntryID int,
@DefinitionID int,
@Value nvarchar(200)

as
IF @ParentKey=''
Begin
	select 
	E.[EntryID],
	E.[ListName],
	E.[Value],
	E.[Text],
	E.[Level],
	E.[SortOrder],
	E.[DefinitionID],
	E.[ParentID],
	E.[Description],