01.00.05.sqldataprovider

SharpNuke源代码

  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,
    MobileTabName,
    ShowMobile,
    LeftPaneWidth,
    RightPaneWidth,
    IsVisible,
    ParentId,
    IconFile,
    Level
) 
values (
    @PortalID,
    @TabOrder,
    'Home',
    '-1;',
    'Home',
    1,
    '200',
    '200',
    1,
    null,
    null,
    0
)

select @TabOrder = @TabOrder + 2

insert into Tabs (
    PortalID,
    TabOrder,
    TabName,
    AuthorizedRoles,
    MobileTabName,
    ShowMobile,
    LeftPaneWidth,
    RightPaneWidth,
    IsVisible,
    ParentId,
    IconFile,
    Level
) 
values (
    @PortalID,
    @TabOrder,
    'Admin',
    convert(varchar,@AdministratorRoleId) + ';',
    '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,
    ShowMobile,
    LeftPaneWidth,
    RightPaneWidth,
    IsVisible,
    ParentId,
    IconFile,
    Level
  )
  values (
    @TabOrder,
    @PortalID,
    @FriendlyName,
    '',
    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  Email = @Email

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 UpdatePortalInfo
GO

create procedure UpdatePortalInfo

@PortalID           int,
@PortalName         nvarchar(128),
@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            money = 0,
@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 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.PayPalId,
       Portals.AdministratorRoleId,
       Portals.RegisteredRoleId,
       Portals.Description,
       Portals.KeyWords,
       Portals.BackgroundFile,
       '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.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,
       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,
       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 GetPortalByAlias
GO

create procedure GetPortalByAlias

@PortalAlias nvarchar(200)

as

declare @PortalID int

select @PortalID = null

select @PortalID = min(PortalID)
from   Portals
where  PortalAlias like '%' + @PortalAlias + '%'

if @PortalID is null
begin
  update Portals
  set    PortalAlias = @PortalAlias
  where  PortalAlias = '_default'

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

select 'PortalID' = @PortalID

GO

create procedure UpdatePortalExpiry

@PortalID   int

as

declare @ExpiryDate datetime

select @ExpiryDate = null

select @ExpiryDate = ExpiryDate
from   Portals
where  PortalID = @PortalID

if @ExpiryDate is null or @ExpiryDate < getdate()
  select @ExpiryDate = getdate()

update Portals
set    ExpiryDate = dateadd(Month,1,@ExpiryDate)
where  PortalID = @PortalID

GO

drop procedure GetPortalRoles
GO

create procedure GetPortalRoles

@PortalID     int

as

select Roles.RoleID,
       Roles.RoleName,
       Roles.Description,
       Roles.ServiceFee,
       'BillingFrequency' = case when Roles.ServiceFee is not null then C1.Description else null end,
       Roles.TrialPeriod,
       'TrialFrequency' = case when Roles.TrialPeriod is not null then C2.Description else null end
from   Roles
left outer join CodeFrequency C1 on Roles.BillingFrequency = C1.Code
left outer join CodeFrequency C2 on Roles.TrialFrequency = C2.Code
where  PortalID = @PortalID
or     PortalID is null
order by Roles.RoleName

GO

drop procedure UpdateService
GO

create procedure UpdateService
    
@UserId       int,
@RoleId       int,
@Units        int

as

declare @TrialPeriod int
declare @Frequency char(1)
declare @ExpiryDate datetime
declare @IsTrialUsed bit

select @TrialPeriod = TrialPeriod
from   Roles
where  RoleId = @RoleId

if @TrialPeriod is not null
begin
  select @ExpiryDate = ExpiryDate,
         @IsTrialUsed = IsTrialUsed
  from   UserRoles
  where  UserId = @UserId
  and    RoleId = @RoleId

  if @Units = 0
  begin
    if @IsTrialUsed is null /* trial period not used */
    begin
      select @Frequency = TrialFrequency,
             @Units = TrialPeriod
      from   Roles