01.00.00.sqldataprovider

SharpNuke源代码

if @Admin = 1
begin
  select FriendlyName,
         DesktopSrc,
         MobileSrc,
         ModuleDefID
  from   ModuleDefinitions
  order  by FriendlyName
end
else
begin
  select FriendlyName,
         DesktopSrc,
         MobileSrc,
         ModuleDefID
  from   ModuleDefinitions
  where  AdminOrder is null
  and    DesktopSrc is not null
  order  by FriendlyName
end


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO



create procedure GetPortalByAlias

@PortalAlias nvarchar(200)

as

select 'PortalID' = min(PortalID)
from   Portals
where  PortalAlias like '%' + @PortalAlias + '%'


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create procedure GetPortalSpaceUsed

@PortalId int

as

select 'SpaceUsed' = sum(Size)
from   Files
where  PortalId = @PortalId

return 1

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create procedure GetRegionCodes
    
@Country char(2)

as

select *
from   CodeRegion
where  Country = @Country
order by Description


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create procedure GetSingleFAQ

@ItemID   int,
@ModuleId int

as

select ItemID,
       ModuleID,
       Question,
       Answer,
       'CreatedByUser' = Users.FirstName + ' ' + Users.LastName,
       FAQs.CreatedDate
from   FAQs
left outer join Users on FAQs.CreatedByUser = Users.UserID
where  ItemID = @ItemID
and    ModuleId = @ModuleId


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create procedure GetSingleFile

@FileName  nvarchar(100),
@PortalId  int

as

select FileName,
       Extension,
       Size,
       Width,
       Height,
       ContentType
from   Files
where  FileName = @FileName
and    PortalId = @PortalId

return 1

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create procedure GetSingleModuleDefinition

@ModuleDefID int

as

select FriendlyName,
       DesktopSrc,
       MobileSrc,
       AdminOrder,
       EditSrc,
       Secure
from   ModuleDefinitions
where  ModuleDefID = @ModuleDefID


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create procedure GetSingleModuleDefinitionByName

@FriendlyName nvarchar(128)

as

select ModuleDefId,
       FriendlyName,
       DesktopSrc,
       MobileSrc,
       AdminOrder,
       EditSrc,
       Secure
from   ModuleDefinitions
where  FriendlyName = @FriendlyName


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create procedure GetSinglePortal

@PortalID  int

as

select *
from   Portals
where  PortalID = @PortalID


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create procedure GetSingleUserByEmail

@Email nvarchar(100)

as
 
select UserId,
       Email,
       Password,
       'FullName' = FirstName + ' ' + LastName,
       FirstName,
       LastName,
       Unit,
       Street,
       City,
       Region,
       PostalCode,
       Country
from   Users
where  Email  = @Email


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create procedure UpdatePortalInfo

@PortalID           int,
@PortalName         nvarchar(50),
@PortalAlias        nvarchar(200) = null,
@UploadDirectory    nvarchar(100) = null,
@LogoFile           nvarchar(50) = null,
@FooterText         nvarchar(100) = null,
@ExpiryDate         datetime = null,
@UserRegistration   int = null,
@BannerAdvertising  int = null,
@Currency           char(3) = null,
@AdministratorId    int = null,
@HostFee            nvarchar(10) = null,
@HostSpace          int = null,
@PayPalId           nvarchar(50) = null

as

update Portals
set    PortalName = @PortalName,
       PortalAlias = isnull(@PortalAlias,PortalAlias),
       UploadDirectory = isnull(@UploadDirectory,UploadDirectory),
       LogoFile = @LogoFile,
       FooterText = @FooterText,
       ExpiryDate = @ExpiryDate,
       UserRegistration = @UserRegistration,
       BannerAdvertising = @BannerAdvertising,
       Currency = @Currency,
       AdministratorId = @AdministratorId,
       HostFee = @HostFee,
       HostSpace = @HostSpace,
       PayPalId = @PayPalId
where  PortalID = @PortalID


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create procedure AddRole

@PortalID         int,
@RoleName         nvarchar(50),
@Description      nvarchar(1000) = null,
@ServiceFee       decimal = 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
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


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

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

return 1


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
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,
    CreatedDate,
    LastLoginDate
  )
  values (
    @FirstName,
    @LastName,
    @Unit,
    @Street,
    @City,
    @Region,
    @PostalCode,
    @Country,
    @Email,
    @Password,
    getdate(),
    null
  )

  select @UserID = @@IDENTITY
end

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


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create procedure AddVendor

@PortalID 	int,
@VendorName 	nvarchar(50),
@Unit    	nvarchar(50),
@Street 	nvarchar(50),
@City		nvarchar(50),
@Region	        nvarchar(50),
@Country	nvarchar(50),
@PostalCode	nvarchar(50),
@Telephone	nvarchar(50),
@Fax   	        nvarchar(50),
@Email    	nvarchar(50),
@Website	nvarchar(100),
@Contact	nvarchar(50),
@UserName       nvarchar(100),
@LogoFile       nvarchar(100),
@KeyWords       text,
@VendorID	int OUTPUT

as

insert into Vendors (
  VendorName,
  Unit,
  Street,
  City,
  Region,
  Country,
  PostalCode,
  Telephone,
  PortalId,
  Fax,
  Email,
  Website,
  Contact,
  ClickThroughs,
  Views,
  CreatedByUser,
  CreatedDate,
  LogoFile,
  KeyWords
)
values (
  @VendorName,
  @Unit,
  @Street,
  @City,
  @Region,
  @Country,
  @PostalCode,
  @Telephone,
  @PortalID,
  @Fax,
  @Email,
  @Website,
  @Contact,
  0,
  0,
  @UserName,
  getdate(), 
  @LogoFile,
  @KeyWords
)

select @VendorID = @@IDENTITY


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO



create procedure DeleteRole

@RoleID int

as

if @RoleID <> 0 /* Admins Role */
begin
  delete 
  from   Roles
  where  RoleID = @RoleID
end


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create procedure DeleteUser

@PortalId int,
@UserID   int

as

declare @RoleId int

if not exists ( select 1 from Portals where AdministratorId = @UserID )
begin
  delete
  from   UserPortals
  where  PortalId = @PortalId
  and    UserID = @UserID

  select @RoleId = min(RoleId)
  from   Roles
  where  PortalId = @PortalId
  while @RoleId is not null
  begin
    delete
    from   UserRoles
    where  UserId = @UserId
    and    RoleId = @RoleId

    select @RoleId = min(RoleId)
    from   Roles
    where  PortalId = @PortalId
    and    RoleId > @RoleId
  end

  if not exists ( select 1 from UserPortals where UserId = @UserID )
  begin
    delete
    from   Users
    where  UserID = @UserID
  end
end


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create procedure GetBannerLog

@BannerId  int

as

select 'LogDate' = convert(varchar,DateTime,102),
       'Views' = count(*)
from   VendorLog
where  BannerId = @BannerId
group by convert(varchar,DateTime,102)
order by LogDate desc

return 1


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO




create procedure GetPortalRoles

@PortalID     int

as

select Roles.RoleID,
       Roles.RoleName,
       Roles.Description,
       Roles.ServiceFee,
       'BillingFrequency' = case when Roles.ServiceFee is not null then C1.Description else null end,
       Roles.TrialPeriod,
       'TrialFrequency' = case when Roles.TrialPeriod is not null then C2.Description else null end
from   Roles
left outer join CodeFrequency C1 on Roles.BillingFrequency = C1.Code
left outer join CodeFrequency C2 on Roles.TrialFrequency = C2.Code
where  PortalID = @PortalID
or     PortalID is null


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create procedure GetPortals

as

select Portals.*,
       'Users' = ( select count(*) from UserPortals where UserPortals.PortalId = Portals.PortalId )
from   Portals
order by PortalName


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create procedure GetSingleRole

@RoleID   int,
@PortalId int