01.00.00.sqldataprovider

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

as

select RoleName,
       Description,
       ServiceFee,
       BillingFrequency,
       TrialPeriod,
       TrialFrequency
from   Roles
where  RoleID = @RoleID
and    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 GetSingleUser

@PortalId int,
@UserId int

as

select Users.UserID,
       'FullName' = FirstName + ' ' + LastName,
       FirstName,
       LastName,
       Unit,
       Street,
       City,
       Region,
       PostalCode,
       Country,
       Email,
       Authorized,
       CreatedDate,
       LastLoginDate,
       Password
from   Users
inner join UserPortals on Users.UserId = UserPortals.UserId
where  Users.UserId = @UserId
and    UserPortals.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 GetSingleVendor

@VendorID int

as

select Vendors.VendorName, 
       Vendors.Unit, 
       Vendors.Street, 
       Vendors.City, 
       Vendors.Region, 
       Vendors.Country, 
       Vendors.PostalCode, 
       Vendors.Telephone,
       Vendors.Fax,
       Vendors.Email,
       Vendors.Website,
       Vendors.Contact,
       Vendors.ClickThroughs,
       Vendors.Views,
       'CreatedByUser' = Users.FirstName + ' ' + Users.LastName,
       Vendors.CreatedDate,
       Vendors.LogoFile,
       Vendors.KeyWords
from   Vendors
left outer join Users on Vendors.CreatedByUser = Users.UserID
where  VendorID = @VendorID


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO




create procedure GetSiteLog

@PortalId   int,
@StartDate  datetime,
@EndDate    datetime

as

select 'Pages' = count(*),
       'Visitors' = count(distinct SiteLog.UserHostAddress),
       'Users' = count(distinct SiteLog.UserId)
from SiteLog
left outer join Users on SiteLog.UserId = Users.UserId 
where SiteLog.PortalId = @PortalId
and   SiteLog.DateTime between @StartDate and @EndDate

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 GetSiteLogDetail

@PortalId   int,
@StartDate  datetime,
@EndDate    datetime

as

declare @PortalAlias nvarchar(50)

select @PortalAlias = PortalAlias
from   Portals
where  PortalID = @PortalID

select SiteLog.DateTime,
       'FullName' = 
	  case
            when SiteLog.UserId is null then null
            else Users.FirstName + ' ' + Users.LastName
          end,
       'Referrer' = 
         case 
           when SiteLog.Referrer like '%' + @PortalAlias + '%' then null 
           else SiteLog.Referrer
         end,
       'URL' = substring(SiteLog.URL,len(@PortalAlias) + 8,500),
       'UserAgent' = 
         case 
           when SiteLog.UserAgent like '%MSIE 1%' then 'Internet Explorer 1'
           when SiteLog.UserAgent like '%MSIE 2%' then 'Internet Explorer 2'
           when SiteLog.UserAgent like '%MSIE 3%' then 'Internet Explorer 3'
           when SiteLog.UserAgent like '%MSIE 4%' then 'Internet Explorer 4'
           when SiteLog.UserAgent like '%MSIE 5%' then 'Internet Explorer 5'
           when SiteLog.UserAgent like '%MSIE 6%' then 'Internet Explorer 6'
           when SiteLog.UserAgent like '%MSIE%' then 'Internet Explorer'
           when SiteLog.UserAgent like '%Mozilla/1%' then 'Netscape Navigator 1'
           when SiteLog.UserAgent like '%Mozilla/2%' then 'Netscape Navigator 2'
           when SiteLog.UserAgent like '%Mozilla/3%' then 'Netscape Navigator 3'
           when SiteLog.UserAgent like '%Mozilla/4%' then 'Netscape Navigator 4'
           else 'Unknown UserAgent'
         end,
         SiteLog.UserHostAddress
from SiteLog
left outer join Users on SiteLog.UserId = Users.UserId 
where SiteLog.PortalId = @PortalId
and   SiteLog.DateTime between @StartDate and @EndDate
order by SiteLog.DateTime desc


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



create procedure GetUsers

@PortalId int = null

as

if @PortalId is null
begin
  select UserID,
         Email,
         'FullName' = FirstName + ' ' + LastName,
         FirstName,
         LastName,
         Unit,
         Street,
         City,
         Region,
         Country,
         PostalCode
  from   Users
  order  by 'FullName'
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.Country,
         Users.PostalCode,
         'Authorized' = case when UserPortals.Authorized = 1 then 'Y' else 'N' end
  from   Users
  inner join UserPortals on Users.UserId = UserPortals.UserId
  where  UserPortals.PortalId = @PortalId
  order  by 'FullName'
end



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create procedure GetVendorClickThrough

@VendorId int

as

update Vendors
set    ClickThroughs = ClickThroughs + 1
where  VendorId = @VendorId

select VendorId,
       VendorName,
       Street,
       City,
       Region,
       Country,
       PostalCode,
       Telephone,
       PortalId,
       Fax,
       Email,
       Website,
       Contact
from   Vendors
where  VendorId = @VendorId

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 GetVendorLog

@VendorId  int

as

select Search,
       'Requests' = count(*),
       'LastRequest' = max(DateTime)
from   VendorLog
where  VendorId = @VendorId
and    BannerId is null
group by Search
order by Requests desc

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 dbo.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),
@Email		nvarchar(100),
@Password	nvarchar(20) = null,
@Authorized     bit = null


as

update Users
set    FirstName = @FirstName,
       LastName	 = @LastName,
       Unit	 = @Unit,
       Street	 = @Street,
       City	 = @City,
       Region	 = @Region,
       PostalCode = @PostalCode,
       Country	 = @Country,
       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
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



create procedure UpdateVendor

@VendorID	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

as

update Vendors
set    VendorName    = @VendorName,
       Unit          = @Unit,
       Street        = @Street,
       City          = @City,
       Region        = @Region,
       Country       = @Country,
       PostalCode    = @PostalCode,
       Telephone     = @Telephone,
       Fax           = @Fax,
       Email         = @Email,
       Website       = @Website,
       Contact       = @Contact,
       CreatedByUser = @UserName,
       CreatedDate   = getdate(),
       LogoFile      = @LogoFile,
       KeyWords      = @KeyWords
where  VendorId = @VendorId


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create procedure UserLogin

@Email    nvarchar(100),
@Password nvarchar(20),
@PortalID int,
@SuperUserId int

as

declare @UserId int

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
  end
end

if not @UserId is null
begin
  update Users
  set    LastLoginDate = getdate()
  where  UserId = @UserId

  select 'UserId' = @UserId
end


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create procedure AddBanner
(
    @BannerName    nvarchar(100),
    @VendorId      int,
    @ImageFile     nvarchar(50),
    @URL           nvarchar(100) = null,
    @Impressions   int,
    @CPM           float,
    @StartDate     datetime = null,
    @EndDate       datetime = null,
    @UserName      nvarchar(100),
    @BannerTypeId  int = null
)

as

insert into Banners
(
    VendorId,
    ImageFile,
    BannerName,
    URL,
    Impressions,
    CPM,
    Views,
    ClickThroughs,
    StartDate,
    EndDate,
    CreatedByUser,
    CreatedDate,
    BannerTypeId
)
values
(
    @VendorId,
    @ImageFile,
    @BannerName,
    @URL,
    @Impressions,
    @CPM,
    0,
    0,
    @StartDate,
    @EndDate,
    @UserName,
    getdate(),
    @BannerTypeId
)



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


create procedure AddModule
    
@TabID          int,
@ModuleOrder    int,
@ModuleTitle    nvarchar(256),
@PaneName       nvarchar(50),
@ModuleDefID    int,
@CacheTime      int,
@EditRoles      nvarchar(256),
@ShowMobile     bit

as

insert into Modules (
  TabID,
  ModuleOrder,
  ModuleTitle,
  PaneName,
  ModuleDefID,
  CacheTime,
  AuthorizedEditRoles,
  ShowMobile
) 
values (
  @TabID,
  @ModuleOrder,
  @ModuleTitle,
  @PaneName,
  @ModuleDefID,
  @CacheTime,
  @EditRoles,
  @ShowMobile
)


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


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

as

declare @ModuleDefId int
declare @TabId int
declare @ModuleOrder int
declare @AdministratorRoleId int

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

select @ModuleDefID = @@IDENTITY

/* add to all Admin tabs */
if @AdminOrder is not null and @AdminOrder > 0
begin
  select @TabId = min(TabId)
  from Tabs
  where TabName = 'Admin'
  while @TabId is not null
  begin
    select @ModuleOrder = (max(ModuleOrder) + 2)
    from   Modules
    where  TabId = @TabId

    select @AdministratorRoleId = AdministratorRoleId
    from   Portals
    inner join Tabs on Portals.PortalId = Tabs.PortalId
    where TabId = @TabId

    if not exists ( select 1 from Modules where TabID = @TabId and ModuleDefID = @ModuleDefId )
    begin
      insert into Modules (
        TabID,
        ModuleDefID,
        ModuleOrder,
        PaneName,
        ModuleTitle,
        AuthorizedEditRoles,
        CacheTime,
        ShowMobile,
        AuthorizedViewRoles
      )
      values (
        @TabId,
        @ModuleDefId,
        @ModuleOrder,
        'ContentPane',
        'Sales Summary',
        convert(varchar,@AdministratorRoleId) + ';',
        0,
        0,
        ''
      )
    end

    select @TabId = min(TabId)
    from Tabs
    where TabName = 'Admin'
    and TabId > @TabId
  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 AddPortalInfo

@PortalName         nvarchar(50),
@PortalAlias        nvarchar(200),
@UploadDirectory    nvarchar(100),
@LogoFile           nvarchar(50) = null,
@FooterText         nvarchar(100) = null,
@UserRegistration   int = null,
@BannerAdvertising  int = null,
@Currency           char(3) = null,
@AdministratorId    int = null,
@FirstName          nvarchar(100),
@LastName           nvarchar(100),
@Email              nvarchar(200),
@Password           nvarchar(40),
@ExpiryDate         datetime = null,
@HostFee            nvarchar(10) = null,
@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 @ModuleOrder int
declare @RoleId int
declare @UserId int
declare @AdministratorRoleId int
declare @RegisteredRoleId    int

begin transaction

insert into Portals (
  PortalName,
  PortalAlias,
  UploadDirectory,
  LogoFile,
  FooterText,
  ExpiryDate,
  UserRegistration,
  BannerAdvertising,
  Currency,
  AdministratorId,
  HostFee,
  HostSpace,
  PayPalId,
  AdministratorRoleId,
  RegisteredRoleId
)
values (