01.00.05.sqldataprovider

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

drop procedure UpdateModuleDefinition
GO

create procedure UpdateModuleDefinition

@ModuleDefID   int,
@FriendlyName  nvarchar(128),
@DesktopSrc    nvarchar(256),
@MobileSrc     nvarchar(256),
@AdminOrder    int,
@EditSrc       nvarchar(256),
@Secure        bit,
@Description   nvarchar(2000),
@HostFee       money

as

declare @TabId int
declare @ModuleOrder int
declare @AdministratorRoleId int
declare @PortalId int
declare @TabOrder int
declare @ChildTabId int

update ModuleDefinitions
set    FriendlyName = @FriendlyName,
       DesktopSrc   = @DesktopSrc,
       MobileSrc    = @MobileSrc,
       AdminOrder   = @AdminOrder,
       EditSrc      = @EditSrc,
       Secure       = @Secure,
       Description  = @Description,
       HostFee      = @HostFee
where  ModuleDefID = @ModuleDefID

GO

drop procedure GetSingleModuleDefinition
GO

create procedure GetSingleModuleDefinition

@ModuleDefID int

as

select FriendlyName,
       DesktopSrc,
       MobileSrc,
       AdminOrder,
       EditSrc,
       Secure,
       Description,
       HostFee
from   ModuleDefinitions
where  ModuleDefID = @ModuleDefID

GO

drop procedure GetModuleDefinitions
GO

create procedure GetModuleDefinitions

@PortalID int,
@Admin    bit = 1

as

if @Admin = 1
begin
  select ModuleDefID,
         FriendlyName,
         Description,
         HostFee
  from   ModuleDefinitions
  where  AdminOrder is null
  and    DesktopSrc is not null
  order  by FriendlyName
end
else
begin
  select distinct(ModuleDefinitions.ModuleDefID),
         ModuleDefinitions.FriendlyName,
         ModuleDefinitions.Description
  from   ModuleDefinitions
  left outer join PortalModuleDefinitions on ModuleDefinitions.ModuleDefID = PortalModuleDefinitions.ModuleDefID
  left outer join Portals on PortalModuleDefinitions.PortalID = @PortalID
  where  AdminOrder is null
  and    DesktopSrc is not null
  and    ( ModuleDefinitions.HostFee = 0 or PortalModuleDefinitions.PortalModuleDefinitionId is not null)
  order  by FriendlyName
end

GO

create procedure GetPortalModuleDefinitions

@PortalID int

as

select distinct(ModuleDefinitions.ModuleDefID),
       'Subscribed' = case when PortalModuleDefinitions.PortalModuleDefinitionId is not null then 1 else 0 end,
       ModuleDefinitions.FriendlyName,
       ModuleDefinitions.Description,
       'HostFee' = case when PortalModuleDefinitions.HostFee is not null then PortalModuleDefinitions.HostFee else ModuleDefinitions.HostFee end
from   ModuleDefinitions
left outer join PortalModuleDefinitions on ModuleDefinitions.ModuleDefID = PortalModuleDefinitions.ModuleDefID
left outer join Portals on PortalModuleDefinitions.PortalID = @PortalID
where  ModuleDefinitions.HostFee <> 0
order  by FriendlyName

GO

create procedure UpdatePortalModuleDefinition

@PortalID int,
@ModuleDefID int,
@Subscribed  bit,
@HostFee money

as

if exists ( select 1 from PortalModuleDefinitions where PortalID = @PortalID and ModuleDefID = @ModuleDefID )
begin
  if @Subscribed = 1
  begin
    update PortalModuleDefinitions
    set    HostFee = @HostFee
    where  PortalID = @PortalID
    and    ModuleDefID = @ModuleDefID
  end
  else
  begin
    if not exists ( select 1 from Modules inner join Tabs on Modules.TabId = Tabs.TabId where Modules.ModuleDefId = @ModuleDefId and Tabs.PortalID = @PortalID )
    begin
      delete
      from   PortalModuleDefinitions
      where  PortalID = @PortalID
      and    ModuleDefID = @ModuleDefID
    end 
  end  
end
else
begin
  if @Subscribed = 1
  begin
    insert into PortalModuleDefinitions ( 
      PortalID,
      ModuleDefID,
      HostFee
    )
    values (
      @PortalID,
      @ModuleDefID,
      @HostFee
    )
  end
end

GO

create procedure GetPortalModuleDefinitionFee

@PortalID int

as

select 'HostFee' = sum(HostFee)
from   PortalModuleDefinitions
where  PortalID = @PortalID

go

drop procedure GetSiteModule
GO

create procedure GetSiteModule

@FriendlyName nvarchar(128),
@PortalID int = null

as

if @PortalId is null
begin
  select Modules.ModuleId
  from   Modules
  inner join Tabs on Modules.TabId = Tabs.TabId
  inner join ModuleDefinitions on Modules.ModuleDefId = ModuleDefinitions.ModuleDefId
  where  Tabs.PortalID is null
  and    ModuleDefinitions.FriendlyName = @FriendlyName
end
else
begin
  select Modules.ModuleId
  from   Modules
  inner join Tabs on Modules.TabId = Tabs.TabId
  inner join ModuleDefinitions on Modules.ModuleDefId = ModuleDefinitions.ModuleDefId
  where  Tabs.PortalID = @PortalId
  and    ModuleDefinitions.FriendlyName = @FriendlyName
end

GO

/* check if script has already been run */
if not exists ( select 1 from Tabs where TabName = 'Host Settings' )
begin
  declare @ModuleDefID int

  insert into ModuleDefinitions ( FriendlyName, DesktopSrc, MobileSrc, AdminOrder, EditSrc, Secure, Description, HostFee ) 
  values ( 'Host Settings', 'admin/Portal/HostSettings.ascx', NULL, -1, NULL, 1, '', 0 )
 
  select @ModuleDefID = @@IDENTITY

  declare @TabID int

  select @TabID = TabID
  from   Tabs
  where  PortalID is null

  insert Modules ( 
    TabID,
    ModuleDefID,
    ModuleOrder,
    PaneName,
    ModuleTitle,
    AuthorizedEditRoles,
    CacheTime,
    ShowMobile,
    AuthorizedViewRoles,
    Alignment,
    Color,
    Border,
    IconFile
  )
  values (
    @TabID,
    @ModuleDefID,
    1,
    'ContentPane',
    'Host Settings',
    '-2;',
    0,
    0,
    '',
    '',
    '',
    '',
    null 
  )

  update Modules
  set    ModuleOrder = 3
  where  TabID = @TabID
  and    ModuleTitle = 'Portals'

  update Modules
  set    ModuleOrder = 5
  where  TabID = @TabID
  and    ModuleTitle = 'Module Definitions'

  update Modules
  set    ModuleOrder = 7
  where  TabID = @TabID
  and    ModuleTitle = 'File Manager'

  update Modules
  set    ModuleOrder = 9
  where  TabID = @TabID
  and    ModuleTitle = 'Vendors'

  update Modules
  set    ModuleOrder = 11
  where  TabID = @TabID
  and    ModuleTitle = 'SQL'

end
GO

update ModuleDefinitions
set    Description = 'This module renders a list of announcements. Each announcement includes title, text and a "read more" link, and can be set to automatically expire after a particular date.'
where  FriendlyName = 'Announcements'
go

update ModuleDefinitions
set    Description = 'Banner advertising is managed through the Vendors module in the Admin tab. You can select the number of banners to display as well as the banner type.'
where  FriendlyName = 'Banners'
go

update ModuleDefinitions
set    Description = 'Administrators can send bulk email to all users belonging to a particular Role.'
where  FriendlyName = 'Bulk Email'
go

update ModuleDefinitions
set    Description = 'This module renders contact information for a group of people, for example a project team. The Mobile version of this module also provides a Call link to phone a contact when the module is browsed from a wireless telephone. Contacts includes an edit page, which allows authorized users to edit the Contacts data stored in the SQL database.'
where  FriendlyName = 'Contacts'
go

update ModuleDefinitions
set    Description = 'This module renders a group of message threads on a specific topic. Discussion includes a Read/Reply Message page, which allows authorized users to reply to exising messages or add a new message thread. The data for Discussion is stored in the SQL database. '
where  FriendlyName = 'Discussions'
go

update ModuleDefinitions
set    Description = 'This module renders a list of documents, including links to browse or download the document. Documents includes an edit page, which allows authorized users to edit the information about the Documents (for example, a friendly title) stored in the SQL database.'
where  FriendlyName = 'Documents'
go

update ModuleDefinitions
set    Description = 'This module renders a list of upcoming events, including time and location. Individual events can be set to automatically expire from the list after a particular date. Events includes an edit page, which allows authorized users to edit the Events data stored in the SQL database. '
where  FriendlyName = 'Events'
go

update ModuleDefinitions
set    Description = 'FAQs allow you to manage a list of Frequently Asked Questions and their corresponding Answers.'
where  FriendlyName = 'FAQs'
go

update ModuleDefinitions
set    Description = 'Feedback allows visitors to send messages to the Administrator of the portal.'
where  FriendlyName = 'Feedback'
go

update ModuleDefinitions
set    Description = 'Administrators can manage the files stored in their upload directory. This module allows you to upload new files, download files, delete files, and synchronize your upload directory. It also provides information on the amount of disk space used and available.'
where  FriendlyName = 'File Manager'
go

update ModuleDefinitions
set    Description = 'The Super User can manage the configuration settings which apply to the entire site.'
where  FriendlyName = 'Host Settings'
go

update ModuleDefinitions
set    Description = 'This module renders a snippet of HTML or text. The Html/Text module includes an edit page, which allows authorized users to the HTML or text snippets directly. The snippets are stored in the SQL database. '
where  FriendlyName = 'HTML'
go

if not exists ( select 1 from ModuleDefinitions where FriendlyName = 'IFrame' )
begin
  INSERT INTO [dbo].[ModuleDefinitions] ([FriendlyName], [DesktopSrc], [MobileSrc], [AdminOrder], [EditSrc], [Secure], [Description], [HostFee]) VALUES ('IFrame', 'DesktopModules/IFrame/IFrame.ascx', '', NULL, 'DesktopModules/IFrame/EditIFrame.ascx', 1, NULL, 0)
end
go

update ModuleDefinitions
set    Description = 'IFrame is an Internet Explorer browser feature which allows you to display content from another website within a frame on your site.'
where  FriendlyName = 'IFrame'
go

update ModuleDefinitions
set    Description = 'This module renders an image using an HTML IMG tag. The module simply sets the IMG tags src attribute to a relative or absolute URL, so the image file does not need to reside within the portal. The module also exposes height and width attributes, which permits you to scale the image. Image includes an edit page, which persists these settings to the portals configuration file. '
where  FriendlyName = 'Image'
go

update ModuleDefinitions
set    Description = 'This module renders a list of hyperlinks. Links includes an edit page, which allows authorized users to edit the Links data stored in the SQL database.'
where  FriendlyName = 'Links'
go

update ModuleDefinitions
set    Description = 'Administrators can manage their registered users. This module allows you to add new users, modify existing users, delete users, and manage the security roles for users.'
where  FriendlyName = 'Manage Users'
go

update ModuleDefinitions
set    Description = 'Map Quest allows you to display a functional map image by leveraging the MapQuest GIS service.'
where  FriendlyName = 'Map Quest'
go

update ModuleDefinitions
set    Description = 'News Feed allows you to consume syndicated news feeds in Rich Site Summary (RSS) format.'
where  FriendlyName = 'News Feeds (RSS)'
go

update ModuleDefinitions
set    Description = 'The Super User can manage the various parent and child Portals within the site. This module allows you to add a new portal, modify an existing portal, and delete a portal.'
where  FriendlyName = 'Portals'
go

update ModuleDefinitions
set    Description = 'Administrators can manage the security roles defined for their portal. The module allows you to add new security roles, modify existing security roles, delete security roles, and manage the users assigned to security roles.'
where  FriendlyName = 'Security Roles'
go

update ModuleDefinitions
set    Description = 'The Service Directory exposes the list of Vendors maintained in the Admin tab. This module allows you to search for vendors based on key word criteria.'
where  FriendlyName = 'Service Directory'
go

update ModuleDefinitions
set    Description = 'Administrators can view the details of visitors using their portal. There are a variety of reports available to display information regarding site usage, membership, and volumes.'
where  FriendlyName = 'Site Log'
go

update ModuleDefinitions
set    Description = 'The Site Settings module represents the local options for your portal. Local settings allow you to customize your portal to meet your business requirements.'
where  FriendlyName = 'Site Settings'
go

update ModuleDefinitions
set    Description = 'The Super User can execute SQL statements against the database.'
where  FriendlyName = 'SQL'
go

update ModuleDefinitions
set    Description = 'Administrators can manage the Tabs within the portal. This module allows you to create a new tab, modify an existing tab, delete tabs, change the tab order, and change the hierarchical tab level.'
where  FriendlyName = 'Tabs'
go

update ModuleDefinitions
set    Description = 'User Defined Table allows you to create a custom data table for managing tabular information.'
where  FriendlyName = 'User Defined Table'
go

update ModuleDefinitions
set    Description = 'Administrators can manage the Vendors and Banners associated to the portal. This module allows you to add a new vendor, modify an existing vendor, and delete a vendor.'
where  FriendlyName = 'Vendors'
go

update ModuleDefinitions
set    Description = 'The Weather Network allows you to display local weather information on your site.'
where  FriendlyName = 'Weather Network'
go

update ModuleDefinitions
set    Description = 'This module renders the result of an XML/XSL transform. The XML and XSL files are identified by their UNC paths in the xmlsrc and xslsrc properties of the module. The Xml/Xsl module includes an edit page, which persists these settings to the SQL database.'
where  FriendlyName = 'XML/XSL'
go

ALTER TABLE Tabs ADD
	[Level] int NOT NULL CONSTRAINT DF_Tabs_Level DEFAULT 0,
	IconFile nvarchar(100) NULL
GO

ALTER TABLE Tabs ADD CONSTRAINT
	DF_Tabs_TabOrder DEFAULT 0 FOR TabOrder
GO

if not exists ( select 1 from Tabs where TabName = 'Host Settings' )
begin
  update Tabs
  set    ParentId = null

  update Tabs
  set    TabOrder = 9999
  where  IsVisible = 0

  declare @PortalID int
  declare @TabId int
  declare @TabCounter int

  select @PortalID = min(PortalId)
  from   Portals
  while @PortalID is not null
  begin
    select @TabCounter = 0

    select @TabId = min(TabId)
    from   Tabs
    where  PortalID = @PortalID
    and    IsVisible = 0
    while @TabId is not null
    begin
      select @TabCounter = @TabCounter + 1        

      update Tabs
      set    TabOrder = (@TabCounter * 2) - 1
      where  TabId = @TabId

      select @TabId = min(TabId)
      from   Tabs
      where  PortalID = @PortalID
      and    IsVisible = 0
      and    TabId > @TabId
    end 

    select @PortalID = min(PortalId)
    from   Portals
    where  PortalID > @PortalId
  end
end
go

drop procedure AddTab
GO

create procedure AddTab

@PortalID        int,
@TabName         nvarchar(50),
@ShowMobile      bit,
@MobileTabName   nvarchar(50),
@AuthorizedRoles nvarchar (256),
@LeftPaneWidth   nvarchar(5),
@RightPaneWidth  nvarchar(5),
@IsVisible       bit,
@ParentId        int,
@IconFile        nvarchar(100),
@TabID           int OUTPUT

as

if @ParentId is not null
begin
  select @IsVisible = 1
end

insert into Tabs (
    PortalID,
    TabName,
    ShowMobile,
    MobileTabName,
    AuthorizedRoles,
    LeftPaneWidth,
    RightPaneWidth,
    IsVisible,
    ParentId,
    IconFile
)
values (
    @PortalID,
    @TabName,
    @ShowMobile,
    @MobileTabName,
    @AuthorizedRoles,
    @LeftPaneWidth,
    @RightPaneWidth,
    @IsVisible,
    @ParentId,
    @IconFile
)

select @TabID = @@IDENTITY

GO

drop procedure UpdateTab
GO

create procedure UpdateTab