01.00.05.sqldataprovider

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

@TabID           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)

as

declare @PortalID int

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

/* hierarchical tabs must be visible */
if (exists ( select 1 from Tabs where ParentId = @TabId )) or (@ParentId is not null)
begin
  select @IsVisible = 1
end

update Tabs
set    TabName = @TabName,
       ShowMobile = @ShowMobile,
       MobileTabName = @MobileTabName,
       AuthorizedRoles = @AuthorizedRoles,
       LeftPaneWidth = @LeftPaneWidth,
       RightPaneWidth = @RightPaneWidth,
       IsVisible = @IsVisible,
       ParentId = @ParentId,
       IconFile = @IconFile
where  TabID = @TabID

GO

drop procedure UpdateTabOrder
GO

create procedure UpdateTabOrder

@TabID    int,
@TabOrder int,
@Level    int,
@ParentId int

as

update Tabs
set    TabOrder = @TabOrder,
       Level = @Level,
       ParentId = @ParentId
where  TabID = @TabID

GO

drop procedure DeleteTab
GO

create procedure DeleteTab

@TabID int

as

if not exists ( select 1 from Tabs where ParentId = @TabID )
begin 
  delete
  from   Tabs
  where  TabID = @TabID
end

GO


drop procedure GetTabsByParentId
GO

create procedure GetTabsByParentId

@ParentId int

as

select TabID,
       TabOrder,
       PortalID,
       TabName,
       MobileTabName,
       AuthorizedRoles,
       ShowMobile,
       LeftPaneWidth,
       RightPaneWidth,
       IsVisible,
       IconFile
from   Tabs
where  ParentId = @ParentId
order by TabOrder

GO

/* check if script has already been run */
if not exists ( select 1 from Tabs where TabName = 'Host Settings' )
begin
  declare @PortalID int
  declare @TabID int
  declare @ModuleId int
  declare @TabOrder int
  declare @AuthorizedRoles nvarchar(256)
  declare @ModuleTitle nvarchar(256)
  declare @ChildTabId int

  select @TabID = min(TabID)
  from   Tabs
  where  TabName = 'Admin'
  while @TabID is not null
  begin
    select @PortalId = PortalID,
           @TabOrder = TabOrder,
           @AuthorizedRoles = AuthorizedRoles
    from   Tabs
    where  TabID = @TabID

    select @ModuleId = min(ModuleId)
    from   Modules
    where  TabId = @TabId
    while @ModuleId is not null
    begin
      select @ModuleTitle = ModuleTitle
      from   Modules
      where  ModuleId = @ModuleId

      select @TabOrder = @TabOrder + 2

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

      select @ChildTabID = @@IDENTITY
    
      update Modules
      set    TabID = @ChildTabID
      where  ModuleId = @ModuleId

      select @ModuleId = min(ModuleId)
      from   Modules
      where  TabId = @TabId
      and    ModuleId > @ModuleId
    end

    select @TabID = min(TabID)
    from   Tabs
    where  TabName = 'Admin'
    and TabID > @TabID
  end
end
go

/* check if script has already been run */
if not exists ( select 1 from Tabs where TabName = 'Host Settings' )
begin
  update Tabs
  set    TabName = 'Host'
  where  PortalId is null

  declare @PortalID int
  declare @TabID int
  declare @ModuleOrder int
  declare @ModuleId int
  declare @AuthorizedRoles nvarchar(256)
  declare @ModuleTitle nvarchar(256)
  declare @ChildTabId int

  select @TabId = TabID,
         @PortalId = PortalID,
         @AuthorizedRoles = AuthorizedRoles
  from   Tabs
  where  PortalID is null

  select @ModuleOrder = min(ModuleOrder)
  from   Modules
  where  TabId = @TabId
  while @ModuleOrder is not null
  begin
    select @ModuleId = ModuleId,
           @ModuleTitle = ModuleTitle
    from   Modules
    where  TabId = @TabId
    and    ModuleOrder = @ModuleOrder

    insert into Tabs (
      TabOrder,
      PortalID,
      TabName,
      MobileTabName,
      AuthorizedRoles,
      ShowMobile,
      LeftPaneWidth,
      RightPaneWidth,
      IsVisible,
      ParentId,
      IconFile,
      Level
    )
    values (
      @ModuleOrder,
      @PortalID,
      @ModuleTitle,
      '',
      @AuthorizedRoles,
      0,
      '200',
      '200',
      1,
      @TabID,
      null,
      1      
    )

    select @ChildTabID = @@IDENTITY
    
    update Modules
    set    TabID = @ChildTabID
    where  ModuleId = @ModuleId

    select @ModuleOrder = min(ModuleOrder)
    from   Modules
    where  TabId = @TabId
    and    ModuleOrder > @ModuleOrder
  end
end
go

update Portals
set    HostFee = 0
where  HostFee is null
GO

update Portals
set    HostSpace = 0
where  HostSpace is null
GO

ALTER TABLE Portals
	DROP CONSTRAINT DF_Portals_GUID
GO

CREATE TABLE dbo.Tmp_Portals
	(
	PortalID int NOT NULL IDENTITY (-1, 1),
	PortalAlias nvarchar(200) NOT NULL,
	PortalName nvarchar(128) NOT NULL,
	LogoFile nvarchar(50) NULL,
	FooterText nvarchar(100) NULL,
	ExpiryDate datetime NULL,
	UserRegistration int NOT NULL,
	BannerAdvertising int NOT NULL,
	AdministratorId int NULL,
	PayPalId nvarchar(50) NULL,
	Currency char(3) NULL,
	HostFee money NOT NULL,
	HostSpace int NOT NULL,
	AdministratorRoleId int NULL,
	RegisteredRoleId int NULL,
	Description nvarchar(500) NULL,
	KeyWords nvarchar(500) NULL,
	BackgroundFile nvarchar(50) NULL,
	GUID uniqueidentifier NOT NULL
	)  ON [PRIMARY]
GO

ALTER TABLE Tmp_Portals ADD CONSTRAINT
	DF_Portals_UserRegistration DEFAULT 0 FOR UserRegistration
GO

ALTER TABLE Tmp_Portals ADD CONSTRAINT
	DF_Portals_BannerAdvertising DEFAULT 0 FOR BannerAdvertising
GO

ALTER TABLE Tmp_Portals ADD CONSTRAINT
	DF_Portals_HostFee DEFAULT 0 FOR HostFee
GO

ALTER TABLE Tmp_Portals ADD CONSTRAINT
	DF_Portals_HostSpace DEFAULT 0 FOR HostSpace
GO

ALTER TABLE Tmp_Portals ADD CONSTRAINT
	DF_Portals_GUID DEFAULT (newid()) FOR GUID
GO

SET IDENTITY_INSERT Tmp_Portals ON
GO

IF EXISTS(SELECT * FROM Portals)
	 EXEC('INSERT INTO Tmp_Portals (PortalID, PortalAlias, PortalName, LogoFile, FooterText, ExpiryDate, UserRegistration, BannerAdvertising, AdministratorId, PayPalId, Currency, HostFee, HostSpace, AdministratorRoleId, RegisteredRoleId, Description, KeyWords, BackgroundFile, GUID) SELECT PortalID, PortalAlias, PortalName, LogoFile, FooterText, ExpiryDate, UserRegistration, BannerAdvertising, AdministratorId, PayPalId, Currency, CONVERT(money, HostFee), HostSpace, AdministratorRoleId, RegisteredRoleId, Description, KeyWords, BackgroundFile, GUID FROM Portals TABLOCKX')
GO

SET IDENTITY_INSERT Tmp_Portals OFF
GO

ALTER TABLE Tabs
	DROP CONSTRAINT FK_Tabs_Portals
GO

ALTER TABLE SiteLog
	DROP CONSTRAINT FK_SiteLog_Portals
GO

ALTER TABLE VendorSearch
	DROP CONSTRAINT FK_VendorSearch_Portals
GO

ALTER TABLE UserPortals
	DROP CONSTRAINT FK_UserPortals_Portals
GO

ALTER TABLE Vendors
	DROP CONSTRAINT FK_Vendor_Portals
GO

ALTER TABLE Roles
	DROP CONSTRAINT FK_Roles_Portals
GO

ALTER TABLE VendorLog
	DROP CONSTRAINT FK_VendorLog_Portals
GO

ALTER TABLE PortalModuleDefinitions
	DROP CONSTRAINT FK_PortalModuleDefinitions_Portals
GO

DROP TABLE Portals
GO

EXECUTE sp_rename N'Tmp_Portals', N'Portals', 'OBJECT'
GO

ALTER TABLE Portals ADD CONSTRAINT
	PK_Portals PRIMARY KEY NONCLUSTERED 
	(
	PortalID
	) ON [PRIMARY]
GO

ALTER TABLE PortalModuleDefinitions WITH NOCHECK ADD CONSTRAINT
	FK_PortalModuleDefinitions_Portals FOREIGN KEY
	(
	PortalId
	) REFERENCES Portals
	(
	PortalID
	) ON DELETE CASCADE
	 NOT FOR REPLICATION
GO

ALTER TABLE VendorLog WITH NOCHECK ADD CONSTRAINT
	FK_VendorLog_Portals FOREIGN KEY
	(
	PortalId
	) REFERENCES Portals
	(
	PortalID
	) ON DELETE CASCADE
	 NOT FOR REPLICATION
GO

ALTER TABLE Roles WITH NOCHECK ADD CONSTRAINT
	FK_Roles_Portals FOREIGN KEY
	(
	PortalID
	) REFERENCES Portals
	(
	PortalID
	) ON DELETE CASCADE
	 NOT FOR REPLICATION
GO

ALTER TABLE Vendors WITH NOCHECK ADD CONSTRAINT
	FK_Vendor_Portals FOREIGN KEY
	(
	PortalId
	) REFERENCES Portals
	(
	PortalID
	) ON DELETE CASCADE
	 NOT FOR REPLICATION
GO

ALTER TABLE UserPortals WITH NOCHECK ADD CONSTRAINT
	FK_UserPortals_Portals FOREIGN KEY
	(
	PortalId
	) REFERENCES Portals
	(
	PortalID
	) ON DELETE CASCADE
	 NOT FOR REPLICATION
GO

ALTER TABLE VendorSearch WITH NOCHECK ADD CONSTRAINT
	FK_VendorSearch_Portals FOREIGN KEY
	(
	PortalId
	) REFERENCES Portals
	(
	PortalID
	) ON DELETE CASCADE
	 NOT FOR REPLICATION
GO
 
ALTER TABLE SiteLog WITH NOCHECK ADD CONSTRAINT
	FK_SiteLog_Portals FOREIGN KEY
	(
	PortalId
	) REFERENCES Portals
	(
	PortalID
	) ON DELETE CASCADE
	 NOT FOR REPLICATION
GO

ALTER TABLE Tabs WITH NOCHECK ADD CONSTRAINT
	FK_Tabs_Portals FOREIGN KEY
	(
	PortalID
	) REFERENCES Portals
	(
	PortalID
	) ON DELETE CASCADE
	 NOT FOR REPLICATION
GO

drop procedure AddPortalInfo
GO

create procedure AddPortalInfo

@PortalName         nvarchar(128),
@PortalAlias        nvarchar(200),
@Currency           char(3) = null,
@FirstName          nvarchar(100),
@LastName           nvarchar(100),
@Email              nvarchar(200),
@Password           nvarchar(50),
@ExpiryDate         datetime = null,
@HostFee            money = 0,
@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 @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,
  PayPalId,
  AdministratorRoleId,
  RegisteredRoleId,
  Description,
  KeyWords,
  BackgroundFile
)
values (
  @PortalName,
  @PortalAlias,
  null,
  null,
  @ExpiryDate,
  0,
  0,
  @Currency,
  null,
  @HostFee,
  @HostSpace,
  @PayPalId,
  null,
  null,
  @PortalName,
  @PortalName,
  null
)

select @PortalID = @@IDENTITY

insert into Roles (
  PortalID,
  RoleName,
  Description,
  ServiceFee,
  BillingFrequency,
  TrialPeriod,