01.00.05.sqldataprovider

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

      where  RoleId = @RoleId
      if @Units is null /* no trial period for role */
      begin
        select @Frequency = '0'
      end
      else
      begin
        if @ExpiryDate is null or @ExpiryDate < getdate()
          select @ExpiryDate = getdate()
      end
    end
    else
    begin
      select @Frequency = '0'
    end
  end
  else  
  begin
    select @Frequency = BillingFrequency
    from   Roles
    where  RoleId = @RoleId
    if @ExpiryDate is null or @ExpiryDate < getdate()
      select @ExpiryDate = getdate()
  end
  select @ExpiryDate =
    case
      when @Frequency = '0' then @ExpiryDate
      when @Frequency = '1' then convert(datetime,'12/31/9999')
      when @Frequency = '2' then dateadd(Day,@Units,@ExpiryDate)
      when @Frequency = '3' then dateadd(Week,@Units,@ExpiryDate)
      when @Frequency = '4' then dateadd(Month,@Units,@ExpiryDate)
      when @Frequency = '5' then dateadd(Year,@Units,@ExpiryDate)
    end
  if exists ( select 1 from UserRoles where UserId = @UserId and RoleId = @RoleId )
  begin
    update UserRoles
    set    ExpiryDate = @ExpiryDate,
           IsTrialUsed = 1
    where  UserId = @UserId
    and    RoleId = @RoleId
  end
  else
  begin
    insert UserRoles (
      UserId,
      RoleId,
      ExpiryDate,
      IsTrialUsed
    )
    values (
      @UserId,
      @RoleId,
      @ExpiryDate,
      1
    )
  end
end

GO

drop procedure GetSingleRole
GO

create procedure GetSingleRole

@RoleID   int

as

select RoleId,
       PortalId,
       RoleName,
       Description,
       ServiceFee,
       BillingFrequency,
       TrialPeriod,
       TrialFrequency
from   Roles
where  RoleID = @RoleID

GO

CREATE TABLE dbo.HostSettings (
	SettingName nvarchar(50) NOT NULL,
	SettingValue nvarchar(256) NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE dbo.HostSettings ADD CONSTRAINT
	IX_HostSettings UNIQUE NONCLUSTERED 
	(
	SettingName
	) ON [PRIMARY]

GO

create procedure GetHostSettings

as

select SettingName,
       SettingValue
from   HostSettings

GO

create procedure UpdateHostSetting

@SettingName   nvarchar(50),
@SettingValue  nvarchar(256)

as

if not exists ( select 1 from HostSettings where SettingName = @SettingName ) 
begin
  insert into HostSettings (
    SettingName,
    SettingValue
  ) 
  values (
    @SettingName,
    @SettingValue
  )
end
else
begin
  update HostSettings
  set    SettingValue = @SettingValue
  where  SettingName = @SettingName
end

GO

exec UpdateHostSetting 'HostTitle', 'DotNetNuke'
GO
exec UpdateHostSetting 'HostURL', 'www.dotnetnuke.com'
GO
exec UpdateHostSetting 'HostEmail', '[e-mail]'
GO
exec UpdateHostSetting 'PayPalId', ''
GO
exec UpdateHostSetting 'HostFee', ''
GO
exec UpdateHostSetting 'HostCurrency', 'USD'
GO
exec UpdateHostSetting 'HostSpace', ''
GO
exec UpdateHostSetting 'DemoPeriod', ''
GO
exec UpdateHostSetting 'DemoSignup', 'N'
GO
exec UpdateHostSetting 'EncryptionKey', ''
GO
exec UpdateHostSetting 'ProxyServer', ''
GO
exec UpdateHostSetting 'ProxyPort', ''
GO
exec UpdateHostSetting 'SMTPServer', ''
GO
exec UpdateHostSetting 'LoggingEnabled', 'Y'
GO
exec UpdateHostSetting 'PortalModule', 'Y'
GO

create procedure GetHostSetting

@SettingName nvarchar(50)

as

select SettingValue
from   HostSettings
where  SettingName = @SettingName

GO

CREATE TABLE dbo.Referrer
	(
	ReferrerId int NOT NULL IDENTITY (1, 1),
	Referrer nvarchar(500) NOT NULL,
	Description nvarchar(500) NOT NULL,
	CreatedDate datetime NOT NULL,
	LastModifiedDate datetime NOT NULL
	)  ON [PRIMARY]
GO

ALTER TABLE Referrer ADD CONSTRAINT
	PK_Referrer PRIMARY KEY CLUSTERED 
	(
	ReferrerId
	) ON [PRIMARY]

GO

create procedure UpdateReferrer

@Referrer nvarchar(500),
@Description nvarchar(500)

as

declare @ReferrerId int

select @ReferrerId = null

select @ReferrerId = ReferrerId
from   Referrer
where  Referrer = @Referrer

if @ReferrerId is null
begin
  insert into Referrer (
    Referrer,
    Description,
    CreatedDate,
    LastModifiedDate
  )
  values (
    @Referrer,
    @Description,
    getdate(),
    getdate()
  )
end
else
begin
  update Referrer
  set    Description = @Description,
         LastModifiedDate = getdate()
  where  ReferrerId = @ReferrerId
end

go

ALTER TABLE SiteLog ADD
	AffiliateId int NULL
GO

drop procedure AddSiteLog
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,
@TabId                         int                   = null,
@AffiliateId                   int                   = null

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

return 1

GO

drop procedure GetSiteLog
GO


create procedure GetSiteLog

@PortalId   int,
@PortalAlias nvarchar(50),
@ReportType int = null,
@StartDate  datetime = null,
@EndDate    datetime = null

as

if @ReportType is null
  select @ReportType = 1
if @StartDate is null
  select @StartDate = min(DateTime) from SiteLog where PortalId = @PortalId

if @EndDate is null
  select @EndDate = max(DateTime) from SiteLog where PortalId = @PortalId

if @ReportType = 1 /* page views per day */
begin
  select 'Date' = convert(varchar,DateTime,102),
         'Views' = count(*),
         'Visitors' = count(distinct SiteLog.UserHostAddress),
         'Users' = count(distinct SiteLog.UserId)
  from   SiteLog
  where  PortalId = @PortalId
  and   SiteLog.DateTime between @StartDate and @EndDate
  group by convert(varchar,DateTime,102)
  order by Date desc
end
else
begin
  if @ReportType = 2 /* detailed site log */
  begin
    select SiteLog.DateTime,
           'Name' = 
	      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,
           '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'
               when SiteLog.UserAgent like '%Mozilla/5%' then 'Netscape Navigator 6+'
               else SiteLog.UserAgent
             end,
             SiteLog.UserHostAddress,
             Tabs.TabName
    from SiteLog
    left outer join Users on SiteLog.UserId = Users.UserId 
    left outer join Tabs on SiteLog.TabId = Tabs.TabId 
    where SiteLog.PortalId = @PortalId
    and   SiteLog.DateTime between @StartDate and @EndDate
    order by SiteLog.DateTime desc
  end
  else
  begin
    if @ReportType = 3 /* user frequency */
    begin
      select 'Name' = Users.FirstName + ' ' + Users.LastName,
             'Requests' = count(*),
             'LastRequest' = max(DateTime)
      from   SiteLog
      inner join Users on SiteLog.UserId = Users.UserId
      where  PortalID = @PortalId
      and   SiteLog.DateTime between @StartDate and @EndDate
      and    SiteLog.UserId is not null
      group by Users.FirstName + ' ' + Users.LastName
      order by Requests desc
    end
    else
    begin
      if @ReportType = 4 /* site referrals */
      begin
        select Referrer,
               'Requests' = count(*),
               'LastRequest' = max(DateTime)
        from   SiteLog
        where  SiteLog.PortalID = @PortalId
        and   SiteLog.DateTime between @StartDate and @EndDate
        and    Referrer is not null
        and    Referrer not like '%' + @PortalAlias + '%'
        group by Referrer
        order by Requests desc
      end
      else
      begin
        if @ReportType = 5 /* user agents */
        begin
          select'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'
                     when SiteLog.UserAgent like '%Mozilla/5%' then 'Netscape Navigator 6+'
                     else SiteLog.UserAgent
                   end,
                 'Requests' = count(*),
                 'LastRequest' = max(DateTime)
          from   SiteLog
          where  PortalID = @PortalId
          and   SiteLog.DateTime between @StartDate and @EndDate
          group by 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'
                     when SiteLog.UserAgent like '%Mozilla/5%' then 'Netscape Navigator 6+'
                     else SiteLog.UserAgent
                   end
          order by Requests desc
        end
        else
        begin
          if @ReportType = 6 /* page views by hour */
          begin
            select 'Hour' = datepart(hour,DateTime),
                   'Views' = count(*),
                   'Visitors' = count(distinct SiteLog.UserHostAddress),
                   'Users' = count(distinct SiteLog.UserId)
            from   SiteLog
            where  PortalId = @PortalId
            and   SiteLog.DateTime between @StartDate and @EndDate
            group by datepart(hour,DateTime)
            order by Hour
          end
          else
          begin
            if @ReportType = 7 /* page views by week day */
            begin
              select 'WeekDay' = datepart(weekday,DateTime),
                     'Views' = count(*),
                     'Visitors' = count(distinct SiteLog.UserHostAddress),
                     'Users' = count(distinct SiteLog.UserId)
              from   SiteLog
              where  PortalId = @PortalId
              and   SiteLog.DateTime between @StartDate and @EndDate
              group by datepart(weekday,DateTime)
              order by WeekDay
            end
            else
            begin
              if @ReportType = 8 /* page views by month */
              begin
                select 'Month' = datepart(month,DateTime),
                       'Views' = count(*),
                       'Visitors' = count(distinct SiteLog.UserHostAddress),
                       'Users' = count(distinct SiteLog.UserId)
                from   SiteLog
                where  PortalId = @PortalId
                and   SiteLog.DateTime between @StartDate and @EndDate
                group by datepart(month,DateTime)
                order by Month
              end              else
              begin
                if @ReportType = 9 /* page popularity */
                begin
                  select 'Page' = Tabs.TabName,
                         'Requests' = count(*),
                         'LastRequest' = max(DateTime)
                  from   SiteLog
                  inner join Tabs on SiteLog.TabID = Tabs.TabID
                  where  SiteLog.PortalId = @PortalId
                  and   SiteLog.DateTime between @StartDate and @EndDate
                  and    SiteLog.TabId is not null
                  group by Tabs.TabName
                  order by Requests desc
                end
                else
                begin
                  if @ReportType = 10 /* user registrations by date */
                  begin
                    select 'Date' = convert(varchar,CreatedDate,102),
                           'Users' = count(*)
                    from   UserPortals
                    where  PortalId = @PortalId
                    and   CreatedDate between @StartDate and @EndDate
                    group by convert(varchar,CreatedDate,102)
                    order by Date desc
                  end
                  else
                  begin
                    if @ReportType = 11 /* user registrations by country */
                    begin
                      select Country,
                             'Users' = count(*)
                      from   UserPortals
                      inner join Users on UserPortals.UserID = Users.UserID
                      where  PortalId = @PortalId
                      and   CreatedDate between @StartDate and @EndDate
                      group by Country
                      order by 'Users' desc
                    end
                    else
                    begin
                      if @ReportType = 12 /* affiliate referrals */
                      begin
                        select AffiliateId,
                               'Requests' = count(*),
                               'LastReferral' = max(DateTime)
                        from   SiteLog
                        where  SiteLog.PortalID = @PortalId
                        and   SiteLog.DateTime between @StartDate and @EndDate
                        and    AffiliateId is not null
                        group by AffiliateId
                        order by Requests desc
                      end
                    end
                  end
                end
              end
            end
          end
        end
      end
    end
  end
end

GO

sp_changeobjectowne