01.00.02.sqldataprovider

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

GO

drop procedure UpdatePortalInfo
GO

create procedure UpdatePortalInfo

@PortalID           int,
@PortalName         nvarchar(50),
@PortalAlias        nvarchar(200) = null,
@LogoFile           nvarchar(50) = null,
@FooterText         nvarchar(100) = null,
@ExpiryDate         datetime = null,
@UserRegistration   int = null,
@BannerAdvertising  int = null,
@Currency           char(3) = null,
@AdministratorId    int = null,
@HostFee            nvarchar(10) = null,
@HostSpace          int = null,
@PayPalId           nvarchar(50) = null,
@Description        nvarchar(500) = null,
@KeyWords           nvarchar(500) = null,
@BackgroundFile     nvarchar(50) = null

as

update Portals
set    PortalName = @PortalName,
       PortalAlias = isnull(@PortalAlias,PortalAlias),
       LogoFile = @LogoFile,
       FooterText = @FooterText,
       ExpiryDate = @ExpiryDate,
       UserRegistration = @UserRegistration,
       BannerAdvertising = @BannerAdvertising,
       Currency = @Currency,
       AdministratorId = @AdministratorId,
       HostFee = @HostFee,
       HostSpace = @HostSpace,
       PayPalId = @PayPalId,
       Description = @Description,
       KeyWords = @KeyWords,
       BackgroundFile = @BackgroundFile
where  PortalID = @PortalID

GO

drop procedure AddPortalInfo
GO

create procedure AddPortalInfo

@PortalName         nvarchar(50),
@PortalAlias        nvarchar(200),
@LogoFile           nvarchar(50) = null,
@FooterText         nvarchar(100) = null,
@UserRegistration   int = null,
@BannerAdvertising  int = null,
@Currency           char(3) = null,
@AdministratorId    int = null,
@FirstName          nvarchar(100),
@LastName           nvarchar(100),
@Email              nvarchar(200),
@Password           nvarchar(40),
@ExpiryDate         datetime = null,
@HostFee            nvarchar(10) = null,
@HostSpace          int = null,
@PayPalId           nvarchar(50) = null,
@PortalID           int OUTPUT

as

declare @AdminOrder int
declare @ModuleDefId int
declare @FriendlyName nvarchar(128)
declare @PaneName nvarchar(50)
declare @TabId int
declare @ModuleOrder int
declare @RoleId int
declare @UserId int
declare @AdministratorRoleId int
declare @RegisteredRoleId    int

begin transaction

insert into Portals (
  PortalName,
  PortalAlias,
  LogoFile,
  FooterText,
  ExpiryDate,
  UserRegistration,
  BannerAdvertising,
  Currency,
  AdministratorId,
  HostFee,
  HostSpace,
  PayPalId,
  AdministratorRoleId,
  RegisteredRoleId
)
values (
  @PortalName,
  @PortalAlias,
  @LogoFile,
  @FooterText,
  @ExpiryDate,
  @UserRegistration,
  @BannerAdvertising,
  @Currency,
  null,
  @HostFee,
  @HostSpace,
  @PayPalId,
  null,
  null
)

select @PortalID = @@IDENTITY

insert into Roles (
  PortalID,
  RoleName,
  Description,
  ServiceFee,
  BillingFrequency,
  TrialPeriod,
  TrialFrequency
)
values (
  @PortalID,
  'Administrators',
  'Portal Administration',
  null,
  4,
  null,
  null
)

select @AdministratorRoleId = @@IDENTITY

insert into Roles (
  PortalID,
  RoleName,
  Description,
  ServiceFee,
  BillingFrequency,
  TrialPeriod,
  TrialFrequency
)
values (
  @PortalID,
  'Registered Users',
  'Registered Users',
  null,
  0,
  null,
  null
)

select @RegisteredRoleId = @@IDENTITY

insert into Tabs (
    PortalID,
    TabOrder,
    TabName,
    AuthorizedRoles,
    MobileTabName,
    ShowMobile,
    LeftPaneWidth,
    RightPaneWidth
) 
values (
    @PortalID,
    1,
    'Home',
    '-1;',
    'Home',
    1,
    '200',
    '200'   
)

insert into Tabs (
    PortalID,
    TabOrder,
    TabName,
    AuthorizedRoles,
    MobileTabName,
    ShowMobile,
    LeftPaneWidth,
    RightPaneWidth
) 
values (
    @PortalID,
    5,
    'Admin',
    convert(varchar,@AdministratorRoleId) + ';',
    'Admin',
    0,
    '200',
    '200'   
)

select @TabId = @@IDENTITY

select @ModuleOrder = 0

select @AdminOrder = min(AdminOrder)
from   ModuleDefinitions
where  AdminOrder is not null
and    AdminOrder > 0
while @AdminOrder is not null
begin
  select @ModuleDefId = ModuleDefId,
         @FriendlyName = FriendlyName
  from   ModuleDefinitions
  where  AdminOrder = @AdminOrder

  select @ModuleOrder = @ModuleOrder + 1

  select @PaneName = 'ContentPane'

  insert Modules ( 
    TabID,
    ModuleDefID,
    ModuleOrder,
    PaneName,
    ModuleTitle,
    AuthorizedEditRoles,
    CacheTime,
    ShowMobile
  )
  values (
    @TabId,
    @ModuleDefId,
    @ModuleOrder,
    @PaneName,
    @FriendlyName,
    convert(varchar,@AdministratorRoleId) + ';',
    0,
    0
  )

  select @AdminOrder = min(AdminOrder)
  from   ModuleDefinitions
  where  AdminOrder is not null
  and    AdminOrder > @AdminOrder
end 

select @UserId = null

if @AdministratorId is null
  select @UserId = UserId
  from   Users
  where  Email = @Email
else
  select @UserId = @AdministratorId

if @UserId is null
begin
  insert into Users (
    FirstName,
    LastName, 
    Email,
    Password
  )
  values (
    @FirstName,
    @LastName,
    @Email,
    @Password
  )

  select @UserId = @@IDENTITY
end

insert into UserPortals (
  UserId,
  PortalId,
  Authorized,
  CreatedDate,
  LastLoginDate
)
values (
  @UserId,
  @PortalID,
  1,
  getdate(),
  getdate()
)

if not exists ( select 1 from UserRoles where UserId = @UserId and RoleID = @AdministratorRoleId )
begin
  insert into UserRoles (
    UserId,
    RoleId,
    ExpiryDate
  )
  values (
    @UserId,
    @AdministratorRoleId, /* Administrators */
    null
  )
end

if not exists ( select 1 from UserRoles where UserId = @UserId and RoleID = @RegisteredRoleId )
begin
  insert into UserRoles (
    UserId,
    RoleId,
    ExpiryDate
  )
  values (
    @UserId,
    @RegisteredRoleId, /* Registered */
    null
  )
end

update Portals
set    AdministratorId = @UserId,
       AdministratorRoleId = @AdministratorRoleId,
       RegisteredRoleId = @RegisteredRoleId
where  PortalID = @PortalID

if @@error <> 0
  rollback transaction
else
  commit transaction

GO

drop procedure GetTabById
GO

create procedure GetTabById

@TabId int

as

select TabID,
       TabOrder,
       PortalID,
       TabName,
       MobileTabName,
       AuthorizedRoles,
       ShowMobile,
       LeftPaneWidth,
       RightPaneWidth,
       IsVisible
from   Tabs
where  TabId = @TabId

GO

create procedure GetPortalByTab

@TabID int,
@PortalAlias nvarchar(200)
 
as

declare @PortalID int

select @PortalID = -1

select @PortalID = PortalID
from   Tabs
where  TabID = @TabID

if @PortalID is null /* SuperTab */
begin
  select 'PortalAlias' = @PortalAlias
end
else
begin
  select PortalAlias
  from   Portals
  inner join Tabs on Portals.PortalID = Tabs.PortalID
  where  TabID = @TabID
  and    PortalAlias like '%' + @PortalAlias + '%'
end

GO

declare @PortalId int
declare @PortalAlias nvarchar(200)

select @PortalId = min(PortalId)
from Portals
while @PortalId is not null
begin
  select @PortalAlias = PortalAlias 
  from   Portals
  where  PortalId = @PortalId

  if charindex('.',@PortalAlias) = 0 and charindex(',',@PortalAlias) = 0 and charindex('localhost',@PortalAlias) = 0 and @PortalAlias <> '_default'
  begin
    update Portals
    set    PortalAlias = 'localhost/' + @PortalAlias
    where  PortalId = @PortalId
  end

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

update Tabs
set    TabName = 'Super Tab'
where PortalId is null
go

drop procedure GetAnnouncements
GO

create procedure GetAnnouncements

@ModuleID int

as

select ItemID,
       CreatedByUser,
       CreatedDate,
       Title,
       URL,
       Syndicate,
       ExpireDate,
       Description
from   Announcements
where  ModuleID = @ModuleID
and    (ExpireDate > GetDate() or ExpireDate is null)

GO

delete
from ModuleDefinitions
where FriendlyName = 'Help'
GO

drop procedure GetUsers
GO

create procedure GetUsers

@PortalId int,
@Filter   nvarchar(1)

as

if @PortalID is null
begin
  select *
  from   Users
  order by UserID
end
else
begin
  select Users.UserID,
         Users.Email,
         'FullName' = Users.FirstName + ' ' + Users.LastName,
         Users.FirstName,
         Users.LastName,
         Users.Unit,
         Users.Street,
         Users.City,
         Users.Region,
         Users.PostalCode,
         Users.Country,
         'Authorized' = case when UserPortals.Authorized = 1 then 'Y' else 'N' end,
         UserPortals.CreatedDate,
         UserPortals.LastLoginDate
  from   Users
  inner join UserPortals on Users.UserId = UserPortals.UserId
  where  UserPortals.PortalId = @PortalId
  and    Users.FirstName like @Filter + '%'
  order  by 'FullName'
end

GO

create procedure GetSiteModule

as

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 = 'Portals'

GO