01.00.02.sqldataprovider

SharpNuke源代码

       UserPortals.LastLoginDate
from   Users
inner join UserPortals on Users.UserId = UserPortals.UserId
where  UserPortals.PortalId = @PortalId
and    Users.FirstName like @Filter + '%'
order  by 'FullName'

GO

ALTER TABLE dbo.SiteLog ADD
	TabId int NULL
GO

declare @SiteLogId int
declare @URL nvarchar(255)
declare @StartPos int
declare @Length int

select @SiteLogId = min(SiteLogId)
from SiteLog
while @SiteLogId is not null
begin
  select @URL = URL
  from SiteLog
  where SiteLogId = @SiteLogId

  select @StartPos = charindex('tabid=',@URL)

  if @StartPos <> 0
  begin
    if charindex('&',@URL,@StartPos) <> 0
      select @Length = (charindex('&',@URL,@StartPos) - @StartPos) - 6
    else
      select @Length = (len(@URL) - @StartPos) - 5

    update SiteLog
    set    TabId = convert(int,substring(@URL,@StartPos + 6,@Length))
    where  SiteLogId = @SiteLogId
  end

  select @SiteLogId = min(SiteLogId)
  from SiteLog
  where SiteLogId > @SiteLogId
end

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

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

return 1

GO

CREATE TABLE dbo.CodeSiteLogReport
	(
	Code int NOT NULL,
	Description nvarchar(50) NOT NULL
	)  ON [PRIMARY]
GO

ALTER TABLE CodeSiteLogReport ADD CONSTRAINT
	PK_CodeSiteLogReport PRIMARY KEY CLUSTERED 
	(
	Code
	) ON [PRIMARY]

GO

create procedure GetSiteLogReports

as

select *
from   CodeSiteLogReport
order by Description

go

insert into CodeSiteLogReport ( code, description ) values ( 1, 'Page Views By Day' )
go
insert into CodeSiteLogReport ( code, description ) values ( 2, 'Detailed Site Log' )
go
insert into CodeSiteLogReport ( code, description ) values ( 3, 'User Frequency' )
go
insert into CodeSiteLogReport ( code, description ) values ( 4, 'Site Referrals' )
go
insert into CodeSiteLogReport ( code, description ) values ( 5, 'User Agents' )
go
insert into CodeSiteLogReport ( code, description ) values ( 6, 'Page Views By Hour' )
go
insert into CodeSiteLogReport ( code, description ) values ( 7, 'Page Views By Day Of Week' )
go
insert into CodeSiteLogReport ( code, description ) values ( 8, 'Page Views By Month' )
go
insert into CodeSiteLogReport ( code, description ) values ( 9, 'Page Popularity' )
go
insert into CodeSiteLogReport ( code, description ) values ( 10, 'User Registrations By Date' )
go
insert into CodeSiteLogReport ( code, description ) values ( 11, 'User Registrations By Country' )
go

drop procedure GetSiteLogDetail
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'
               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'
                     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'
                     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
                  end
                end
              end
            end
          end
        end
      end
    end
  end
end

go

ALTER TABLE dbo.Portals ADD
	Description nvarchar(500) NULL,
	KeyWords nvarchar(500) NULL,
	BackgroundFile nvarchar(50) NULL
GO

ALTER TABLE dbo.Portals
	DROP COLUMN UploadDirectory
GO

drop procedure GetPortalSettings
GO

create procedure GetPortalSettings

@PortalAlias nvarchar(200),
@TabID       int

as

declare @PortalID int
declare @VerifyTabID int

/* convert PortalAlias to PortalID */

select @PortalID = null

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

if @PortalID is null
begin
  select @PortalID = min(PortalID)
  from   Portals
  where  PortalAlias like '%' + @PortalAlias + '%' /* multiple alias may be specified seperated by commas */
end

select @VerifyTabID = null

/* verify the TabID belongs to the portal */
if @TabID <> 0
begin
  select @VerifyTabID = Tabs.TabID
  from   Tabs
  left outer join Portals on Tabs.PortalID = Portals.PortalID
  where  TabId = @TabId
  and    ( Portals.PortalID = @PortalID or Tabs.PortalId is null )
end
else
begin
  select @VerifyTabID = null
end

/* get the TabID if none provided */
if @VerifyTabID is null
begin
  select @TabID = min(Tabs.TabID)
  from Tabs
  inner join Portals on Tabs.PortalID = Portals.PortalID
  where Portals.PortalID = @PortalID
end

/* First, get Out Params */
select Portals.PortalAlias,
       Portals.PortalID,
       Portals.GUID,
       Portals.PortalName,
       Portals.LogoFile,
       Portals.FooterText,
       Portals.ExpiryDate,
       Portals.UserRegistration,
       Portals.BannerAdvertising,
       Portals.Currency,
       Portals.AdministratorId,
       Users.Email,
       Portals.HostFee,
       Portals.HostSpace,
       Portals.PayPalId,
       Portals.AdministratorRoleId,
       Portals.RegisteredRoleId,
       Portals.Description,
       Portals.KeyWords,
       Portals.BackgroundFile,
       'SuperUserId' = ( select UserID from Users where IsSuperUser = 1 ),
       'SuperTabId' = ( select TabID from Tabs where PortalId is null ),
       Tabs.TabID,
       Tabs.TabOrder,
       Tabs.TabName,
       Tabs.MobileTabName,
       Tabs.AuthorizedRoles,
       Tabs.ShowMobile,
       Tabs.LeftPaneWidth,
       Tabs.RightPaneWidth,
       Tabs.IsVisible
from   Tabs
inner join Portals on Portals.PortalID = @PortalID
inner join Users on Portals.AdministratorId = Users.UserId
where  TabID = @TabID

/* Get Tabs list */
select TabName,
       AuthorizedRoles,
       TabID,
       TabOrder,
       IsVisible
from   Tabs
where  PortalID = @PortalID
order  by TabOrder

/* Get Mobile Tabs list */
select MobileTabName,
       AuthorizedRoles,
       TabID,
       IsVisible,
       ShowMobile
from   Tabs
where  PortalID = @PortalID
and    ShowMobile = 1
order  by TabOrder

/* Then, get the DataTable of module info */
select *
from   Modules
inner join ModuleDefinitions on Modules.ModuleDefID = ModuleDefinitions.ModuleDefID
where  TabID = @TabID
order by ModuleOrder