01.00.06.sqldataprovider

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

select @UserId = null

/* validate the user */
select @UserId = UserId
from   Users
where  Username = @Username
and    Password = @Password

if @UserId is not null
begin
  if @UserId <> @SuperUserId
  begin
    select @UserId = null

    /* validate the user belongs to the portal */
    select @UserId = Users.UserId
    from   UserPortals
    inner join Users on UserPortals.UserId = Users.UserId
    where  PortalID = @PortalID
    and    Username = @Username
    and    Password = @Password
    and    Authorized = 1

    if not @UserId is null
    begin
      update UserPortals
      set    LastLoginDate = getdate()
      where  UserId = @UserId
      and    PortalID = @PortalID
    end
  end
end

select 'UserId' = @UserId

GO

declare @SettingValue nvarchar(256)

select @SettingValue = SettingValue
from   HostSettings
where  SettingName = 'LoggingEnabled'

if @SettingValue = 'Y'
  exec updateHostSetting 'SiteLogHistory', '60'
else
  exec updateHostSetting 'SiteLogHistory', ''
GO

delete
from   HostSettings
where  SettingName = 'LoggingEnabled'
GO

delete
from   HostSettings
where  SettingName = 'LastEncryptionKey'
GO

ALTER TABLE Tabs ADD
	AdministratorRoles nvarchar(256) NULL
GO


ALTER TABLE Portals ADD
	PaymentProcessor nvarchar(50) NULL,
	ProcessorUserId nvarchar(50) NULL,
	ProcessorPassword nvarchar(50) NULL,
	SiteLogHistory int NULL
GO

update Portals
set    PaymentProcessor = 'PayPal',
       ProcessorUserId = PayPalId,
       SiteLogHistory = 60
GO

ALTER TABLE Portals
	DROP COLUMN PayPalId
GO

drop procedure GetPortalSettings
GO

create procedure GetPortalSettings

@PortalAlias nvarchar(200),
@TabID       int

as

declare @PortalID int
declare @VerifyTabID int

/* convert PortalAlias to PortalID */

select @PortalID = null

select @PortalID = PortalID
from   Portals
where  PortalAlias = @PortalAlias

if @PortalID is null
begin
  select @PortalID = min(PortalID)
  from   Portals
  where  PortalAlias like '%' + @PortalAlias + '%' /* multiple alias may be specified seperated by commas */
end

select @VerifyTabID = null

/* verify the TabID belongs to the portal */
if @TabID <> 0
begin
  select @VerifyTabID = Tabs.TabID
  from   Tabs
  left outer join Portals on Tabs.PortalID = Portals.PortalID
  where  TabId = @TabId
  and    ( Portals.PortalID = @PortalID or Tabs.PortalId is null )
end
else
begin
  select @VerifyTabID = null
end

/* get the TabID if none provided */
if @VerifyTabID is null
begin
  select @TabID = Tabs.TabID
  from Tabs
  inner join Portals on Tabs.PortalID = Portals.PortalID
  where Portals.PortalID = @PortalID
  and Tabs.TabOrder = 1  
end

/* First, get Out Params */
select Portals.PortalAlias,
       Portals.PortalID,
       Portals.GUID,
       Portals.PortalName,
       Portals.LogoFile,
       Portals.FooterText,
       Portals.ExpiryDate,
       Portals.UserRegistration,
       Portals.BannerAdvertising,
       Portals.Currency,
       Portals.AdministratorId,
       Users.Email,
       Portals.HostFee,
       Portals.HostSpace,
       Portals.AdministratorRoleId,
       Portals.RegisteredRoleId,
       Portals.Description,
       Portals.KeyWords,
       Portals.BackgroundFile,
       Portals.SiteLogHistory,
       'AdminTabId' = ( select TabID from Tabs where PortalId = @PortalId and TabName = 'Admin' ),
       'SuperUserId' = ( select UserID from Users where IsSuperUser = 1 ),
       'SuperTabId' = ( select TabID from Tabs where PortalId is null and ParentId is null ),
       Tabs.TabID,
       Tabs.TabOrder,
       Tabs.TabName,
       Tabs.MobileTabName,
       Tabs.AuthorizedRoles,
       Tabs.AdministratorRoles,
       Tabs.ShowMobile,
       Tabs.LeftPaneWidth,
       Tabs.RightPaneWidth,
       Tabs.IsVisible,
       'ParentId' = isnull(Tabs.ParentID,-1),
       Tabs.Level,
       Tabs.IconFile,
       'HasChildren' = case when exists (select 1 from Tabs T2 where T2.ParentId = Tabs.TabId) then 'true' else 'false' end
from   Tabs
inner join Portals on Portals.PortalID = @PortalID
inner join Users on Portals.AdministratorId = Users.UserId
where  TabID = @TabID

/* Get Tabs list */
select TabName,
       AuthorizedRoles,
       AdministratorRoles,
       TabID,
       TabOrder,
       IsVisible,
       'ParentId' = isnull(Tabs.ParentID,-1),
       Tabs.Level,
       Tabs.IconFile,
       'HasChildren' = case when exists (select 1 from Tabs T2 where T2.ParentId = Tabs.TabId) then 'true' else 'false' end
from   Tabs
where  PortalID = @PortalId
order  by TabOrder, TabName

/* Get Mobile Tabs list */
select MobileTabName,
       AuthorizedRoles,
       AdministratorRoles,
       TabID,
       IsVisible,
       'ParentId' = isnull(Tabs.ParentID,-1),
       Tabs.Level,
       Tabs.IconFile,
       'HasChildren' = case when exists (select 1 from Tabs T2 where T2.ParentId = Tabs.TabId) then 'true' else 'false' end
from   Tabs
where  PortalID = @PortalID
and    ShowMobile = 1
order  by TabOrder, TabName

/* Then, get the DataTable of module info */
select Modules.*, ModuleDefinitions.*
from   Modules
inner join ModuleDefinitions on Modules.ModuleDefID = ModuleDefinitions.ModuleDefID
inner join Tabs on Modules.TabID = Tabs.TabID
where  Modules.TabID = @TabID
or     (Modules.AllTabs = 1 and Tabs.PortalID = @PortalID)
order by ModuleOrder

GO

drop procedure AddPortalInfo
GO

create procedure AddPortalInfo

@PortalName         nvarchar(128),
@PortalAlias        nvarchar(200),
@Currency           char(3) = null,
@FirstName          nvarchar(100),
@LastName           nvarchar(100),
@Username           nvarchar(100),
@Password           nvarchar(50),
@Email              nvarchar(100),
@ExpiryDate         datetime = null,
@HostFee            money = 0,
@HostSpace          int = null,
@SiteLogHistory     int = null,
@PortalID           int OUTPUT

as

declare @AdminOrder int
declare @ModuleDefId int
declare @FriendlyName nvarchar(128)
declare @PaneName nvarchar(50)
declare @TabId int
declare @TabOrder int
declare @ChildTabId 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,
  AdministratorRoleId,
  RegisteredRoleId,
  Description,
  KeyWords,
  BackgroundFile
)
values (
  @PortalName,
  @PortalAlias,
  null,
  null,
  @ExpiryDate,
  0,
  0,
  @Currency,
  null,
  @HostFee,
  @HostSpace,
  null,
  null,
  @PortalName,
  @PortalName,
  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

select @TabOrder = 1

insert into Tabs (
    PortalID,
    TabOrder,
    TabName,
    AuthorizedRoles,
    AdministratorRoles,
    MobileTabName,
    ShowMobile,
    LeftPaneWidth,
    RightPaneWidth,
    IsVisible,
    ParentId,
    IconFile,
    Level
) 
values (
    @PortalID,
    @TabOrder,
    'Home',
    '-1;',
    null,
    'Home',
    1,
    '200',
    '200',
    1,
    null,
    null,
    0
)

select @TabOrder = @TabOrder + 2

insert into Tabs (
    PortalID,
    TabOrder,
    TabName,
    AuthorizedRoles,
    AdministratorRoles,
    MobileTabName,
    ShowMobile,
    LeftPaneWidth,
    RightPaneWidth,
    IsVisible,
    ParentId,
    IconFile,
    Level
) 
values (
    @PortalID,
    @TabOrder,
    'Admin',
    convert(varchar,@AdministratorRoleId) + ';',
    null,
    'Admin',
    0,
    '200',
    '200',
    1,
    null,
    null,
    0
)

select @TabId = @@IDENTITY

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 @TabOrder = @TabOrder + 2

  insert into Tabs (
    TabOrder,
    PortalID,
    TabName,
    MobileTabName,
    AuthorizedRoles,
    AdministratorRoles,
    ShowMobile,
    LeftPaneWidth,
    RightPaneWidth,
    IsVisible,
    ParentId,
    IconFile,
    Level
  )
  values (
    @TabOrder,
    @PortalID,
    @FriendlyName,
    '',
    convert(varchar,@AdministratorRoleId) + ';',
    convert(varchar,@AdministratorRoleId) + ';',
    0,
    '200',
    '200',
    1,
    @TabID,
    null,
    1      
  )

  select @ChildTabId = @@IDENTITY

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

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

select @UserId = null

select @UserId = UserId
from   Users
where  Username = @Username

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

  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