01.00.06.sqldataprovider

SharpNuke源代码

  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
                      else /* default = all */
                      begin
                        select *
                        from   SiteLog
                        where  SiteLog.PortalID = @PortalId
                        and   SiteLog.DateTime between @StartDate and @EndDate
                        order by SiteLog.DateTime
                      end
                    end
                  end
                end
              end
            end
          end
        end
      end
    end
  end
end

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
 
declare @SiteLogHistory int

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

/* purge site log history */
select @SiteLogHistory = SiteLogHistory
from   Portals
where  PortalID = @PortalId

if @SiteLogHistory is not null
begin
  delete
  from   SiteLog
  where  PortalID = @PortalId
  and    datediff(day,DateTime,getdate()) > @SiteLogHistory
  and    AffiliateId is null
end

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
declare @SiteLogHistory int

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

/* purge vendor log */
select @SiteLogHistory = SiteLogHistory
from   Portals
where  PortalID = @DisplayPortalId

if @SiteLogHistory is not null
begin
  delete
  from   VendorLog
  where  PortalID = @DisplayPortalId
  and    datediff(day,DateTime,getdate()) > @SiteLogHistory
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

drop procedure GetRoleMembership
GO

create procedure GetRoleMembership
    
@PortalId int,
@RoleId   int = null,
@UserId   int = null

as

if @RoleId is null
begin
  select UserRoles.UserRoleID,
         UserRoles.UserId,
         'FullName' = Users.FirstName + ' ' + Users.LastName,
         Users.Email,
         UserRoles.RoleId,
         Roles.RoleName,
         UserRoles.ExpiryDate
  from   UserRoles
  inner join Users On Users.UserId = UserRoles.UserId
  inner join Roles On Roles.RoleId = UserRoles.RoleId
  inner join UserPortals On Users.UserId = UserPortals.UserId and UserPortals.PortalID = @PortalID
  where  Roles.PortalId = @PortalId
  and    UserRoles.UserId = @UserId
  and    UserPortals.Authorized = 1
end
else
begin
  select UserRoles.UserRoleID,
         UserRoles.UserId,
         'FullName' = Users.FirstName + ' ' + Users.LastName,
         Users.Email,
         UserRoles.RoleId,
         Roles.RoleName,
         UserRoles.ExpiryDate
  from   UserRoles
  inner join Users On Users.UserId = UserRoles.UserId
  inner join Roles On Roles.RoleId = UserRoles.RoleId
  inner join UserPortals On Users.UserId = UserPortals.UserId and UserPortals.PortalID = @PortalID
  where  Roles.PortalId = @PortalId
  and    UserRoles.RoleId = @RoleId
  and    UserPortals.Authorized = 1
end

GO


/************************************************************/
/*****              Upgrade Script 1.0.6                *****/
/************************************************************/