01.00.02.sqldataprovider

SharpNuke源代码

/************************************************************/
/*****              Upgrade Script 1.0.2                *****/
/************************************************************/


declare @TabID int

select @TabID = TabID
from   Tabs
where  PortalID is null

update Modules
set    ModuleOrder = 3
where  TabID = @TabID
and    ModuleTitle = 'Module Definitions'

declare @ModuleDefID int

select @ModuleDefID = ModuleDefID
from   ModuleDefinitions
where  FriendlyName = 'Manage Users'

insert Modules ( 
  TabID,
  ModuleDefID,
  ModuleOrder,
  PaneName,
  ModuleTitle,
  AuthorizedEditRoles,
  CacheTime,
  ShowMobile,
  AuthorizedViewRoles,
  Alignment,
  Color,
  Border,
  IconFile
)
values (
  @TabID,
  @ModuleDefID,
  5,
  'ContentPane',
  'Manage Users',
  '-2;',
  0,
  0,
  '',
  '',
  '',
  '',
  null
)

update Modules
set    ModuleOrder = 7
where  TabID = @TabID
and    ModuleTitle = 'File Manager'

update Modules
set    ModuleOrder = 9
where  TabID = @TabID
and    ModuleTitle = 'Vendors'

update Modules
set    ModuleOrder = 11
where  TabID = @TabID
and    ModuleTitle = 'SQL'
go

drop procedure FindBanners
GO

create procedure FindBanners

@DisplayPortalId int,
@BannerTypeId int = null,
@SelectPortalId int = null,
@Banners  int = 1

as

declare @RecordCounter int
declare @RandomRecord int
declare @BannerId int
declare @StartDate smalldatetime
declare @EndDate smalldatetime
declare @Views int
declare @Impressions int
declare @VendorId int

if @BannerTypeId is null
begin
  select @BannerTypeId = BannerTypeId
  from   BannerTypes
  where  BannerTypeName = 'Banner'
end

/* find number of banners */
select @RecordCounter = count(*)
from   Banners
inner join Vendors on Banners.VendorId = Vendors.VendorId
where  Banners.BannerTypeId = @BannerTypeId
and    ((Vendors.PortalId = @SelectPortalId) or (@SelectPortalId is null and Vendors.PortalId is null))
and    (Banners.Impressions > Banners.Views Or Banners.Impressions = 0)
and    (Banners.StartDate is null Or getdate() >= Banners.StartDate )
and    (Banners.EndDate is null Or getdate() <= Banners.EndDate )

if @Banners > @RecordCounter
begin
  select @Banners = @RecordCounter
end

/* generate random number */
select @RandomRecord = Round(RAND() * (@RecordCounter - @Banners + 1),0)
if @RandomRecord = 0
begin
  select @RandomRecord = 1
end

/* move record pointer to random record */
select @RecordCounter = 1

select @BannerId = min(Banners.BannerId)
from   Banners
inner join Vendors on Banners.VendorId = Vendors.VendorId
where  Banners.BannerTypeId = @BannerTypeId
and    ((Vendors.PortalId = @SelectPortalId) or (@SelectPortalId is null and Vendors.PortalId is null))
and    (Banners.Impressions > Banners.Views Or Banners.Impressions = 0)
and    (Banners.StartDate is null Or getdate() >= Banners.StartDate )
and    (Banners.EndDate is null Or getdate() <= Banners.EndDate )

while @BannerId is not null and @RecordCounter <> @RandomRecord
begin
  select @BannerId = min(Banners.BannerId)
  from   Banners
  inner join Vendors on Banners.VendorId = Vendors.VendorId
  where  Banners.BannerTypeId = @BannerTypeId
  and    ((Vendors.PortalId = @SelectPortalId) or (@SelectPortalId is null and Vendors.PortalId is null))
  and    (Banners.Impressions > Banners.Views Or Banners.Impressions = 0)
  and    (Banners.StartDate is null Or getdate() >= Banners.StartDate )
  and    (Banners.EndDate is null Or getdate() <= Banners.EndDate )
  and    Banners.BannerId > @BannerId

  select @RecordCounter = @RecordCounter + 1
end

/* return matching banners */
set rowcount @Banners

if @SelectPortalId is null
begin
  select BannerId,
         BannerName,
         ImageFile
  from   Banners
  inner join Vendors on Banners.VendorId = Vendors.VendorId
  where  Banners.BannerTypeId = @BannerTypeId
  and    ((Vendors.PortalId = @SelectPortalId) or (@SelectPortalId is null and Vendors.PortalId is null))
  and    (Banners.Impressions > Banners.Views Or Banners.Impressions = 0)
  and    (Banners.StartDate is null Or getdate() >= Banners.StartDate )
  and    (Banners.EndDate is null Or getdate() <= Banners.EndDate )
  and    BannerId >= @BannerId
end
else
begin
  select BannerId,
         BannerName,
         ImageFile
  from   Banners
  inner join Vendors on Banners.VendorId = Vendors.VendorId
  where  Banners.BannerTypeId = @BannerTypeId
  and    ((Vendors.PortalId = @SelectPortalId) or (@SelectPortalId is null and Vendors.PortalId is null))
  and    (Banners.Impressions > Banners.Views Or Banners.Impressions = 0)
  and    (Banners.StartDate is null Or getdate() >= Banners.StartDate )
  and    (Banners.EndDate is null Or getdate() <= Banners.EndDate )
  and    BannerId >= @BannerId
end

set rowcount 0

/* update banners */
select @RecordCounter = 0

while @RecordCounter < @Banners
begin
  update Banners
  set    Views = Views + 1
  where  BannerId = @BannerId

  select @vendorId = VendorId
  from   Banners
  where  BannerId = @BannerId

  insert VendorLog (
    DateTime,
    PortalId,
    VendorId,
    BannerId,
    Search
  )
  values (
    getdate(),    @DisplayPortalId,
    @VendorId,
    @BannerId,
    null
  ) 

  select @StartDate = StartDate,
         @EndDate = EndDate,
         @Views = Views,
         @Impressions = Impressions
  from   Banners
  where  BannerId = @BannerId

  if @StartDate is null
    select @StartDate = getdate()
  if @Views = @Impressions
    select @EndDate = getdate()

  update Banners
  set    StartDate = @StartDate,
         EndDate = @EndDate
  where  BannerId = @BannerId

  select @BannerId = min(Banners.BannerId)
  from   Banners
  inner join Vendors on Banners.VendorId = Vendors.VendorId
  where  Banners.BannerTypeId = @BannerTypeId
  and    ((Vendors.PortalId = @SelectPortalId) or (@SelectPortalId is null and Vendors.PortalId is null))
  and    (Banners.Impressions > Banners.Views Or Banners.Impressions = 0)
  and    (Banners.StartDate is null Or getdate() >= Banners.StartDate )
  and    (Banners.EndDate is null Or getdate() <= Banners.EndDate )
  and    Banners.BannerId > @BannerId

  select @RecordCounter = @RecordCounter + 1
end

return 1

GO

ALTER TABLE dbo.Users ADD
	IsSuperUser bit NOT NULL CONSTRAINT DF_Users_IsSuperUser DEFAULT (0)
GO

if not exists ( select 1 from Users where IsSuperUser = 1 )
begin
  update Users
  set    IsSuperUser = 1
  where  Email = 'host'
end
go

ALTER TABLE dbo.UserPortals ADD
	CreatedDate datetime NULL,
	LastLoginDate datetime NULL
GO

declare @UserID int
declare @CreatedDate datetime
declare @LastLoginDate datetime

select @UserID = min(UserID)
from   Users
while @UserID is not null
begin
  select @CreatedDate = CreatedDate,
         @LastLoginDate = LastLoginDate
  from   Users
  where  UserID = @UserID

  update UserPortals
  set    CreatedDate = @CreatedDate,
         LastLoginDate = isnull(@LastLoginDate,@CreatedDate)
  where  UserID = @UserID
       
  select @UserID = min(UserID)
  from   Users
  where  UserID > @UserID
end

ALTER TABLE dbo.Users
	DROP COLUMN CreatedDate, LastLoginDate
GO

drop procedure UserLogin
GO

create procedure UserLogin

@Email    nvarchar(100),
@Password nvarchar(20),
@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

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.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.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 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),
@Email		nvarchar(100),
@Password	nvarchar(20),
@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,
    Email,
    Password
  )
  values (
    @FirstName,
    @LastName,
    @Unit,
    @Street,
    @City,
    @Region,
    @PostalCode,
    @Country,
    @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 GetUsers
GO


create procedure GetUsers

@PortalId int,
@Filter   nvarchar(1)

as

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,