01.00.10.sqldataprovider

SharpNuke源代码

       TabID,
       TabOrder,
       IsVisible,
       DisableLink,
       'ParentId' = isnull(Tabs.ParentID,-1),
       Tabs.Level,
       Tabs.IconFile,
       'AdminTabIcon' = ( select AdminTabIcon from ModuleDefinitions where ModuleDefinitions.FriendlyName = Tabs.TabName ),
       '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,
       DisableLink,
       'ParentId' = isnull(Tabs.ParentID,-1),
       Tabs.Level,
       Tabs.IconFile,
       'AdminTabIcon' = ( select AdminTabIcon from ModuleDefinitions where ModuleDefinitions.FriendlyName = Tabs.TabName ),
       '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

if exists (select * from [dbo].sysobjects where id = object_id(N'[dbo].[AddUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
  drop procedure [dbo].[AddUser]
GO


CREATE procedure [dbo].AddUser

@PortalId       int,
@FirstName	nvarchar(50),
@LastName	nvarchar(50),
@Unit		nvarchar(50),
@Street		nvarchar(50),
@City		nvarchar(50),
@Region		nvarchar(50),
@PostalCode	nvarchar(50),
@Country	nvarchar(50),
@Telephone      nvarchar(50),
@Email		nvarchar(100),
@Username	nvarchar(100),
@Password	nvarchar(50),
@Authorized     bit,
@UserID	int	OUTPUT

as

declare @RoleID int, @count int

select	@count = count(UserID)
from 	Users
where	Username = @Username 

if @count = 0
begin
  insert into Users (
    FirstName,
    LastName,
    Unit, 
    Street, 
    City,
    Region, 
    PostalCode,
    Country,
    Telephone,
    Email,
    Username,
    Password
  )
  values (
    @FirstName,
    @LastName,
    @Unit,
    @Street,
    @City,
    @Region,
    @PostalCode,
    @Country,
    @Telephone,
    @Email,
    @Username,
    @Password
  )

  select @UserID = @@IDENTITY
end

if @UserId is not null
begin
  if not exists ( select 1 from UserPortals where UserId = @UserId and PortalId = @PortalId ) 
  begin
    insert into UserPortals (
      UserId,
      PortalId,
      Authorized,
      CreatedDate
    )
    values (
      @UserId,
      @PortalId,
      @Authorized,
      getdate()
    )
  end

  select @RoleID = min(RoleID)
  from   Roles
  where  PortalID = @PortalID
  and    AutoAssignment = 1
  while @RoleID is not null
  begin
    if not exists ( select 1 from UserRoles where UserId = @UserId and RoleId = @RoleId )
    begin
      insert into UserRoles (
        UserID,
        RoleID,
        ExpiryDate,
        IsTrialUsed
      )
      values (
        @UserID,
        @RoleID,
        null,
        0 
      )
    end

    select @RoleID = min(RoleID)
    from   Roles
    where  PortalID = @PortalID
    and    AutoAssignment = 1
    and    RoleID > @RoleID
  end
end

GO


/*
** The following procedure will repair the tab level of all tabs in the database
*/

DECLARE FixTabLevelCursor  CURSOR LOCAL SCROLL STATIC READ_ONLY FOR
	SELECT TabId, Level FROM [dbo].Tabs
OPEN FixTabLevelCursor

DECLARE @TabId [int]
DECLARE @ParentId [int]
DECLARE @OldLevel [int]
DECLARE @NewLevel [int]
DECLARE @IndexId [int]

FETCH NEXT FROM FixTabLevelCursor INTO @TabId, @OldLevel
WHILE (@@FETCH_STATUS <> -1)
BEGIN
	IF (@@FETCH_STATUS <> -2)
	BEGIN
		SET @NewLevel = 0
		SET @IndexId = @TabId
		SELECT @ParentId = ParentId FROM [dbo].Tabs WHERE TabId = @IndexId
		WHILE @ParentId IS NOT NULL
		BEGIN
			SET @NewLevel = @NewLevel + 1
			SET @IndexId = @ParentId
			SELECT @ParentId = ParentId FROM [dbo].Tabs WHERE TabId = @IndexId
		END
		IF (@OldLevel <> @NewLevel)
		BEGIN
			UPDATE [dbo].Tabs SET Level = @NewLevel WHERE TabId = @TabId
		END
	END
	FETCH NEXT FROM FixTabLevelCursor INTO @TabId, @OldLevel
END
CLOSE FixTabLevelCursor
DEALLOCATE FixTabLevelCursor

/*
** End of tab level repair
*/


/*****************************************************************************************/
/**********      PLEASE KEEP THIS INDEX SECTION AT THE END OF THE SCRIPT        **********/
/*****************************************************************************************/

if exists (select name from [dbo].sysindexes where name = 'IX_VendorSearch')
   drop index [dbo].VendorSearch.IX_VendorSearch
go

create index [IX_VendorSearch] on [dbo].[VendorSearch] (
	[PortalId]) on [PRIMARY]  
go

if exists (select name from [dbo].sysindexes where name = 'IX_Vendors_1')
   drop index [dbo].Vendors.IX_Vendors_1
go

create index [IX_Vendors_1] on [dbo].[Vendors] (
	[PortalId]) on [PRIMARY]  
go

if exists (select name from [dbo].sysindexes where name = 'IX_VendorLog')
   drop index [dbo].VendorLog.IX_VendorLog
go

create index [IX_VendorLog] on [dbo].[VendorLog] (
	[PortalId]) on [PRIMARY]  
go

if exists (select name from [dbo].sysindexes where name = 'IX_VendorFeedback')
   drop index [dbo].VendorFeedback.IX_VendorFeedback
go

create index [IX_VendorFeedback] on [dbo].[VendorFeedback] (
	[VendorId]) on [PRIMARY]  
go

if exists (select name from [dbo].sysindexes where name = 'IX_VendorClassification_1')
   drop index [dbo].VendorClassification.IX_VendorClassification_1
go

create index [IX_VendorClassification_1] on [dbo].[VendorClassification] (
	[ClassificationId]) on [PRIMARY]  
go

if exists (select name from [dbo].sysindexes where name = 'IX_VendorClassification_2')
   drop index [dbo].VendorClassification.IX_VendorClassification_2
go

create index [IX_VendorClassification_2] on [dbo].[VendorClassification] (
	[VendorId]) on [PRIMARY]  
go

if exists (select name from [dbo].sysindexes where name = 'IX_UserRoles')
   drop index [dbo].UserRoles.IX_UserRoles
go

create index [IX_UserRoles] on [dbo].[UserRoles] (
	[RoleID]) on [PRIMARY]  
go

if exists (select name from [dbo].sysindexes where name = 'IX_UserRoles_1')
   drop index [dbo].UserRoles.IX_UserRoles_1
go

create index [IX_UserRoles_1] on [dbo].[UserRoles] (
	[UserID]) on [PRIMARY]  
go

if exists (select name from [dbo].sysindexes where name = 'IX_UserPortals')
   drop index [dbo].UserPortals.IX_UserPortals
go

create index [IX_UserPortals] on [dbo].[UserPortals] (
	[PortalId]) on [PRIMARY]  
go

if exists (select name from [dbo].sysindexes where name = 'IX_UserPortals_1')
   drop index [dbo].UserPortals.IX_UserPortals_1
go

create index [IX_UserPortals_1] on [dbo].[UserPortals] (
	[UserID]) on [PRIMARY]  
go

if exists (select name from [dbo].sysindexes where name = 'IX_UserDefinedRows')
   drop index [dbo].UserDefinedRows.IX_UserDefinedRows
go

create index [IX_UserDefinedRows] on [dbo].[UserDefinedRows] (
	[ModuleID]) on [PRIMARY]  
go

if exists (select name from [dbo].sysindexes where name = 'IX_UserDefinedFields')
   drop index [dbo].UserDefinedFields.IX_UserDefinedFields
go

create index [IX_UserDefinedFields] on [dbo].[UserDefinedFields] (
	[ModuleID]) on [PRIMARY]  
go

if exists (select name from [dbo].sysindexes where name = 'IX_UserDefinedData')
   drop index [dbo].UserDefinedData.IX_UserDefinedData
go

create index [IX_UserDefinedData] on [dbo].[UserDefinedData] (
	[UserDefinedFieldId]) on [PRIMARY]  
go

if exists (select name from [dbo].sysindexes where name = 'IX_UserDefinedData_1')
   drop index [dbo].UserDefinedData.IX_UserDefinedData_1
go

create index [IX_UserDefinedData_1] on [dbo].[UserDefinedData] (
	[UserDefinedRowId]) on [PRIMARY]  
go

if exists (select name from [dbo].sysindexes where name = 'IX_Tabs_1')
   drop index [dbo].Tabs.IX_Tabs_1
go

create index [IX_Tabs_1] on [dbo].[Tabs] (
	[PortalId]) on [PRIMARY]  
go

if exists (select name from [dbo].sysindexes where name = 'IX_Tabs_2')
   drop index [dbo].Tabs.IX_Tabs_2
go

create index [IX_Tabs_2] on [dbo].[Tabs] (
	[ParentId]) on [PRIMARY]  
go

if exists (select name from [dbo].sysindexes where name = 'IX_SiteLog')
   drop index [dbo].SiteLog.IX_SiteLog
go

create index [IX_SiteLog] on [dbo].[SiteLog] (
	[PortalId]) on [PRIMARY]  
go

if exists (select name from [dbo].sysindexes where name = 'IX_Search')
   drop index [dbo].Search.IX_Search
go

create index [IX_Search] on [dbo].[Search] (
	[ModuleID]) on [PRIMARY]  
go

if exists (select name from [dbo].sysindexes where name = 'IX_Roles')
   drop index [dbo].Roles.IX_Roles
go

create index [IX_Roles] on [dbo].[Roles] (
	[BillingFrequency]) on [PRIMARY]  
go

if exists (select name from [dbo].sysindexes where name = 'IX_Roles_1')
   drop index [dbo].Roles.IX_Roles_1
go

create index [IX_Roles_1] on [dbo].[Roles] (
	[PortalId]) on [PRIMARY]  
go

if exists (select name from [dbo].sysindexes where name = 'IX_PortalModuleDefinitions_1')
   drop index [dbo].PortalModuleDefinitions.IX_PortalModuleDefinitions_1
go

create index [IX_PortalModuleDefinitions_1] on [dbo].[PortalModuleDefinitions] (
	[ModuleDefID]) on [PRIMARY]  
go

if exists (select name from [dbo].sysindexes where name = 'IX_PortalModuleDefinitions_2')
   drop index [dbo].PortalModuleDefinitions.IX_PortalModuleDefinitions_2
go

create index [IX_PortalModuleDefinitions_2] on [dbo].[PortalModuleDefinitions] (
	[PortalId]) on [PRIMARY]  
go

if exists (select name from [dbo].sysindexes where name = 'IX_Modules')
   drop index [dbo].Modules.IX_Modules
go

create index [IX_Modules] on [dbo].[Modules] (
	[ModuleDefID]) on [PRIMARY]  
go

if exists (select name from [dbo].sysindexes where name = 'IX_Modules_1')
   drop index [dbo].Modules.IX_Modules_1
go

create index [IX_Modules_1] on [dbo].[Modules] (
	[TabID]) on [PRIMARY]  
go

if exists (select name from [dbo].sysindexes where name = 'IX_ModuleEvents')
   drop index [dbo].ModuleEvents.IX_ModuleEvents
go

create index [IX_ModuleEvents] on [dbo].[ModuleEvents] (
	[ModuleID]) on [PRIMARY]  
go

if exists (select name from [dbo].sysindexes where name = 'IX_Links')
   drop index [dbo].Links.IX_Links
go

create index [IX_Links] on [dbo].[Links] (
	[ModuleID]) on [PRIMARY]  
go

if exists (select name from [dbo].sysindexes where name = 'IX_Files')
   drop index [dbo].Files.IX_Files
go

create index [IX_Files] on [dbo].[Files] (
	[PortalId]) on [PRIMARY]  
go

if exists (select name from [dbo].sysindexes where name = 'IX_FAQs')
   drop index [dbo].FAQs.IX_FAQs
go

create index [IX_FAQs] on [dbo].[FAQs] (
	[ModuleID]) on [PRIMARY]  
go

if exists (select name from [dbo].sysindexes where name = 'IX_Documents')
   drop index [dbo].Documents.IX_Documents
go

create index [IX_Documents] on [dbo].[Documents] (
	[ModuleID]) on [PRIMARY]  
go

if exists (select name from [dbo].sysindexes where name = 'IX_Discussion')
   drop index [dbo].Discussion.IX_Discussion
go

create index [IX_Discussion] on [dbo].[Discussion] (
	[ModuleID]) on [PRIMARY]  
go

if exists (select name from [dbo].sysindexes where name = 'IX_Contacts')
   drop index [dbo].Contacts.IX_Contacts
go

create index [IX_Contacts] on [dbo].[Contacts] (
	[ModuleID]) on [PRIMARY]  
go

if exists (select name from [dbo].sysindexes where name = 'IX_Classification')
   drop index [dbo].Classification.IX_Classification
go

create index [IX_Classification] on [dbo].[Classification] (
	[ParentId]) on [PRIMARY]  
go

if exists (select name from [dbo].sysindexes where name = 'IX_Banners')
   drop index [dbo].Banners.IX_Banners
go

create index [IX_Banners] on [dbo].[Banners] (
	[BannerTypeId]) on [PRIMARY]  
go

if exists (select name from [dbo].sysindexes where name = 'IX_Banners_1')
   drop index [dbo].Banners.IX_Banners_1
go

create index [IX_Banners_1] on [dbo].[Banners] (
	[VendorId]) on [PRIMARY]  
go

if exists (select name from [dbo].sysindexes where name = 'IX_Announcements')
   drop index [dbo].Announcements.IX_Announcements
go

create index [IX_Announcements] on [dbo].[Announcements] (
	[ModuleID]) on [PRIMARY]  
go

/************************************************************/
/*****              Upgrade Script 1.0.10                *****/
/************************************************************/