01.00.08.sqldataprovider

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

        AuthorizedEditRoles,
        CacheTime,
        ShowMobile,
        AuthorizedViewRoles
      )
      values (
        @ChildTabId,
        @ModuleDefId,
        1,
        'ContentPane',
        @FriendlyName,
        convert(varchar,@AdministratorRoleId) + ';',
        0,
        0,
        ''
      )
    end
    select @TabId = min(TabId)
    from Tabs
    where TabName = 'Admin'
    and TabId > @TabId
  end
end

GO

create procedure dbo.AddModuleEvent

@ModuleID    int,
@Description nvarchar(2000),
@DateTime    datetime,
@Title       nvarchar(100),
@ExpireDate  datetime = null,
@UserName    nvarchar(200),
@Every       int,
@Period      char(1),
@IconFile    nvarchar(256)

as

insert ModuleEvents ( 
  ModuleID,
  Description,
  DateTime,
  Title,
  ExpireDate,
  CreatedByUser,
  CreatedDate,
  Every,
  Period,
  IconFile
)
values (
  @ModuleID,
  @Description,
  @DateTime,
  @Title,
  @ExpireDate,
  @UserName,
  getdate(),
  @Every,
  @Period,
  @IconFile
)

GO

create procedure dbo.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
  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

create procedure dbo.AddRole

@PortalID         int,
@RoleName         nvarchar(50),
@Description      nvarchar(1000) = null,
@ServiceFee       money = null,
@BillingFrequency char(1),
@TrialPeriod      int = null,
@TrialFrequency   char(1)

as

insert into Roles (
  PortalID,
  RoleName,
  Description,
  ServiceFee,
  BillingFrequency,
  TrialPeriod,
  TrialFrequency
)
values (
  @PortalID,
  @RoleName,
  @Description,
  @ServiceFee,
  @BillingFrequency,
  @TrialPeriod,
  @TrialFrequency
)

GO

create procedure dbo.AddSearch

@ModuleID  int,
@TableName nvarchar(50)

as

if not exists ( select 1 from Search where ModuleId = @ModuleId and TableName = @TableName )
begin
  insert into Search (
    ModuleId,
    TableName
  )
  values (
    @ModuleId,
    @TableName
  )
end

GO

create procedure dbo.AddSiteLog

@PortalId                      int,
@UserId                        int                   = null,
@Referrer                      nvarchar(255)         = null,
@Url                           nvarchar(255)         = null,
@UserAgent                     nvarchar(255)         = null,
@UserHostAddress               nvarchar(255)         = null,
@UserHostName                  nvarchar(255)         = null,
@TabId                         int                   = null,
@AffiliateId                   int                   = null

as
 
declare @SiteLogHistory int

insert SiteLog ( 
  DateTime,
  PortalId,
  UserId,
  Referrer,
  Url,
  UserAgent,
  UserHostAddress,
  UserHostName,
  TabId,
  AffiliateId
)
values (
  getdate(),
  @PortalId,
  @UserId,
  @Referrer,
  @Url,
  @UserAgent,
  @UserHostAddress,
  @UserHostName,
  @TabId,
  @AffiliateId
)

/* purge site log history */
select @SiteLogHistory = SiteLogHistory
from   Portals
where  PortalID = @PortalId

if @SiteLogHistory is not null
begin
  delete
  from   SiteLog
  where  PortalID = @PortalId
  and    datediff(day,DateTime,getdate()) > @SiteLogHistory
  and    AffiliateId is null
end

GO

create procedure dbo.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),
@AdministratorRoles nvarchar (256),
@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,
    AdministratorRoles
)
values (
    @PortalID,
    @TabName,
    @ShowMobile,
    @MobileTabName,
    @AuthorizedRoles,