01.00.05.sqldataprovider

SharpNuke源代码

/************************************************************/
/*****              Upgrade Script 1.0.5                *****/
/************************************************************/

delete 
from moduledefinitions
where FriendlyName = 'Sales Summary'
GO

ALTER TABLE Users
	DROP CONSTRAINT DF_Users_IsSuperUser
GO

CREATE TABLE dbo.Tmp_Users
	(
	UserID int NOT NULL IDENTITY (1, 1),
	FirstName nvarchar(50) NOT NULL,
	LastName nvarchar(50) NOT NULL,
	Street nvarchar(20) NULL,
	City nvarchar(20) NULL,
	Region nvarchar(20) NULL,
	PostalCode nvarchar(10) NULL,
	Country nvarchar(20) NULL,
	Password nvarchar(50) NOT NULL,
	Email nvarchar(100) NOT NULL,
	Unit nvarchar(50) NULL,
	IsSuperUser bit NOT NULL,
	Telephone nvarchar(20) NULL
	)  ON [PRIMARY]
GO

ALTER TABLE Tmp_Users ADD CONSTRAINT
	DF_Users_IsSuperUser DEFAULT (0) FOR IsSuperUser
GO

SET IDENTITY_INSERT Tmp_Users ON
GO
  
IF EXISTS(SELECT * FROM Users)
	 EXEC('INSERT INTO Tmp_Users (UserID, FirstName, LastName, Street, City, Region, PostalCode, Country, Password, Email, Unit, IsSuperUser, Telephone) SELECT UserID, FirstName, LastName, Street, City, Region, PostalCode, Country, Password, Email, Unit, IsSuperUser, null FROM Users TABLOCKX')
GO

SET IDENTITY_INSERT Tmp_Users OFF
GO

ALTER TABLE UserRoles
	DROP CONSTRAINT FK_UserRoles_Users
GO

ALTER TABLE UserPortals
	DROP CONSTRAINT FK_UserPortals_Users
GO

DROP TABLE Users
GO

EXECUTE sp_rename N'Tmp_Users', N'Users', 'OBJECT'
GO

ALTER TABLE Users ADD CONSTRAINT
	IX_Users UNIQUE NONCLUSTERED 
	(
	Email
	) ON [PRIMARY]
GO

ALTER TABLE Users ADD CONSTRAINT
	PK_Users PRIMARY KEY NONCLUSTERED 
	(
	UserID
	) ON [PRIMARY]
GO

ALTER TABLE UserPortals WITH NOCHECK ADD CONSTRAINT
	FK_UserPortals_Users FOREIGN KEY
	(
	UserId
	) REFERENCES Users
	(
	UserID
	) NOT FOR REPLICATION
GO

ALTER TABLE UserRoles WITH NOCHECK ADD CONSTRAINT
	FK_UserRoles_Users FOREIGN KEY
	(
	UserID
	) REFERENCES Users
	(
	UserID
	) ON DELETE CASCADE
	 NOT FOR REPLICATION
GO

drop procedure AddUser
GO

create procedure AddUser

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

as

select	@UserID = UserID
from 	Users
where	Email = @Email 
and Password = @Password

if @UserID is null
begin
  insert into Users (
    FirstName,
    LastName,
    Unit, 
    Street, 
    City,
    Region, 
    PostalCode,
    Country,
    Telephone,
    Email,
    Password
  )
  values (
    @FirstName,
    @LastName,
    @Unit,
    @Street,
    @City,
    @Region,
    @PostalCode,
    @Country,
    @Telephone,
    @Email,
    @Password
  )

  select @UserID = @@IDENTITY
end

if @@ERROR = 0
begin
  insert into UserPortals (
    UserId,
    PortalId,
    Authorized,
    CreatedDate
  )
  values (
    @UserId,
    @PortalId,
    @Authorized,
    getdate()
  )
end

GO

drop procedure UpdateUser
GO

create procedure UpdateUser

@PortalId       int,
@UserID         int,
@FirstName	nvarchar(50),
@LastName	nvarchar(50),
@Unit		nvarchar(50),
@Street		nvarchar(20),
@City	        nvarchar(20),
@Region	        nvarchar(20),
@PostalCode	nvarchar(10),
@Country	nvarchar(20),
@Telephone	nvarchar(20),
@Email		nvarchar(100),
@Password	nvarchar(50) = null,
@Authorized     bit = null


as

update Users
set    FirstName = @FirstName,
       LastName	 = @LastName,
       Unit	 = @Unit,
       Street	 = @Street,
       City	 = @City,
       Region	 = @Region,
       PostalCode = @PostalCode,
       Country	 = @Country,
       Telephone = @Telephone,
       Email	 = @Email,
       Password	 = isnull(@Password,Password)
where  UserId = @UserID

if @Authorized is not null
begin
  update UserPortals
  set    Authorized = @Authorized
  where  PortalId = @PortalId
  and    userId = @UserId
end

GO

drop procedure GetUsers
GO

create procedure GetUsers

@PortalId int,
@Filter   nvarchar(1)

as

if @PortalID is null
begin
  select Users.*,
         'FullName' = Users.FirstName + ' ' + Users.LastName
  from   Users
  order by UserID
end
else
begin
  select Users.UserID,
         Users.Email,
         'FullName' = Users.FirstName + ' ' + Users.LastName,
         Users.FirstName,
         Users.LastName,
         Users.Unit,
         Users.Street,
         Users.City,
         Users.Region,
         Users.PostalCode,
         Users.Country,
         'Authorized' = case when UserPortals.Authorized = 1 then 'Y' else 'N' end,
         UserPortals.CreatedDate,
         UserPortals.LastLoginDate
  from   Users
  inner join UserPortals on Users.UserId = UserPortals.UserId
  where  UserPortals.PortalId = @PortalId
  and    Users.FirstName like @Filter + '%'
  order  by 'FullName'
end
GO

drop procedure GetSingleUser
GO

create procedure GetSingleUser

@PortalId int,
@UserId int

as

select Users.UserID,
       Users.Email,
       Users.Password,
       'FullName' = Users.FirstName + ' ' + Users.LastName,
       Users.FirstName,
       Users.LastName,
       Users.Unit,
       Users.Street,
       Users.City,
       Users.Region,
       Users.PostalCode,
       Users.Country,
       Users.Telephone,
       Users.IsSuperUser,
       UserPortals.Authorized,
       UserPortals.CreatedDate,
       UserPortals.LastLoginDate
from   Users
left outer join UserPortals on Users.UserId = UserPortals.UserId
where  Users.UserId = @UserId
and    (UserPortals.PortalId = @PortalId or Users.IsSuperUser = 1)

GO

drop procedure GetSingleUserByEmail
GO


create procedure GetSingleUserByEmail

@PortalID int,
@Email nvarchar(100)

as
 
select Users.UserId,
       Users.Email,
       Users.Password,
       'FullName' = Users.FirstName + ' ' + Users.LastName,
       Users.FirstName,
       Users.LastName,
       Users.Unit,
       Users.Street,
       Users.City,
       Users.Region,
       Users.PostalCode,
       Users.Country,
       Users.Telephone,
       Users.IsSuperUser,
       UserPortals.Authorized,
       UserPortals.CreatedDate,
       UserPortals.LastLoginDate
from   Users
left outer join UserPortals on Users.UserId = UserPortals.UserId
where  Email  = @Email
and    (UserPortals.PortalId = @PortalId or Users.IsSuperUser = 1)

GO

drop procedure UserLogin
GO

create procedure UserLogin

@Email    nvarchar(100),
@Password nvarchar(50),
@PortalID int

as

declare @UserId int
declare @SuperUserId int

select @SuperUserId = UserId
from   Users
where  IsSuperUser = 1

select @UserId = null

/* validate the user */
select @UserId = UserId
from   Users
where  Email = @Email
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    Email = @Email
    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

create procedure UpdateUserLogin

@UserID   int,
@PortalID int

as

declare @Authorized bit
declare @SuperUserId int
declare @UserPortalId int

select @Authorized = 0

select @SuperUserId = UserId
from   Users
where  IsSuperUser = 1

if @UserID <> @SuperUserId
begin
  select @Authorized = Authorized
  from   UserPortals
  where  UserId = @UserId
  and    PortalId = @PortalId

  if @Authorized = 1
  begin
    update UserPortals
    set    LastLoginDate = getdate()
    where  UserId = @UserId
    and    PortalId = @PortalId
  end
end
else
begin
  select @Authorized = 1
end

select 'Authorized' = @Authorized

go

ALTER TABLE CodeCountry ADD CONSTRAINT
	PK_CodeCountry PRIMARY KEY CLUSTERED 
	(
	Code
	) ON [PRIMARY]

GO

ALTER TABLE CodeRegion ADD CONSTRAINT
	PK_CodeRegion PRIMARY KEY CLUSTERED 
	(
	Code,
	Country
	) ON [PRIMARY]

GO

ALTER TABLE ModuleDefinitions ADD
	Description nvarchar(2000) NULL,        
	HostFee money NOT NULL CONSTRAINT DF_ModuleDefinitions_HostFee DEFAULT 0
GO

CREATE TABLE dbo.PortalModuleDefinitions
	(
	PortalModuleDefinitionId int NOT NULL IDENTITY (1, 1),
	PortalId int NOT NULL,
	ModuleDefId int NOT NULL,
	HostFee money NULL CONSTRAINT DF_PortalModuleDefinitions_HostFee DEFAULT 0
	)  ON [PRIMARY]
GO

ALTER TABLE PortalModuleDefinitions ADD CONSTRAINT
	PK_PortalModuleDefinitions PRIMARY KEY CLUSTERED 
	(
	PortalModuleDefinitionId
	) ON [PRIMARY]

GO

ALTER TABLE PortalModuleDefinitions ADD CONSTRAINT
	IX_PortalModuleDefinitions UNIQUE NONCLUSTERED 
	(
	PortalId,
	ModuleDefId
	) ON [PRIMARY]

GO

ALTER TABLE PortalModuleDefinitions ADD CONSTRAINT
	FK_PortalModuleDefinitions_ModuleDefinitions FOREIGN KEY
	(
	ModuleDefId
	) REFERENCES ModuleDefinitions
	(
	ModuleDefID
	) ON DELETE CASCADE
	 NOT FOR REPLICATION

GO

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

GO

drop procedure AddModuleDefinition
GO

create procedure AddModuleDefinition
    
@FriendlyName nvarchar(128),
@DesktopSrc   nvarchar(256),
@MobileSrc    nvarchar(256),
@AdminOrder   int,
@EditSrc      nvarchar(256),
@Secure       bit,
@Description  nvarchar(2000),
@HostFee      money

as

declare @ModuleDefId int
declare @TabId int
declare @AdministratorRoleId int
declare @PortalId int
declare @TabOrder int
declare @ChildTabId int

insert into ModuleDefinitions (
  FriendlyName,
  DesktopSrc,
  MobileSrc,
  AdminOrder,
  EditSrc,
  Secure,
  Description,
  HostFee
)
values (
  @FriendlyName,
  @DesktopSrc,
  @MobileSrc,
  @AdminOrder,
  @EditSrc,
  @Secure,
  @Description,
  @HostFee
)

select @ModuleDefID = @@IDENTITY

GO