01.00.04.sqldataprovider

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

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

GO

if not exists ( select 1 from ModuleDefinitions where FriendlyName = 'Feedback' )
begin
  insert into ModuleDefinitions ( FriendlyName, DesktopSrc, MobileSrc, AdminOrder, EditSrc, Secure ) 
  values ( 'Feedback', 'DesktopModules/Feedback/Feedback.ascx', NULL, NULL, NULL, 1 )
end
GO

update ModuleDefinitions
set    FriendlyName = 'Service Directory'
where  FriendlyName = 'Business Directory'
GO

if not exists ( select 1 from ModuleDefinitions where FriendlyName = 'Map Quest' )
begin
  insert into ModuleDefinitions ( FriendlyName, DesktopSrc, MobileSrc, AdminOrder, EditSrc, Secure ) 
  values ( 'Map Quest', 'DesktopModules/Maps/MapQuest.ascx', NULL, NULL, 'DesktopModules/Maps/EditMapQuest.ascx', 1 )
end
GO

if not exists ( select 1 from ModuleDefinitions where FriendlyName = 'Weather Network' )
begin
  insert into ModuleDefinitions ( FriendlyName, DesktopSrc, MobileSrc, AdminOrder, EditSrc, Secure ) 
  values ( 'Weather Network', 'DesktopModules/Weather/WeatherNetwork.ascx', NULL, NULL, 'DesktopModules/Weather/EditWeatherNetwork.ascx', 1 )
end
GO

CREATE TABLE [dbo].[UserDefinedData] (
	[UserDefinedDataId] [int] IDENTITY (1, 1) NOT NULL ,
	[UserDefinedFieldId] [int] NOT NULL ,
	[UserDefinedRowId] [int] NOT NULL ,
	[FieldValue] [nvarchar] (2000) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[UserDefinedFields] (
	[UserDefinedFieldId] [int] IDENTITY (1, 1) NOT NULL ,
	[ModuleId] [int] NOT NULL ,
	[FieldTitle] [varchar] (50) NOT NULL ,
	[Visible] [bit] NOT NULL ,
	[FieldOrder] [int] NOT NULL ,
	[FieldType] [varchar] (20) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[UserDefinedRows] (
	[UserDefinedRowId] [int] IDENTITY (1, 1) NOT NULL ,
	[ModuleId] [int] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[UserDefinedData] WITH NOCHECK ADD 
	CONSTRAINT [PK_UserDefinedData] PRIMARY KEY  CLUSTERED 
	(
		[UserDefinedDataId]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[UserDefinedFields] WITH NOCHECK ADD 
	CONSTRAINT [PK_UserDefinedTable] PRIMARY KEY  CLUSTERED 
	(
		[UserDefinedFieldId]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[UserDefinedRows] WITH NOCHECK ADD 
	CONSTRAINT [PK_UserDefinedRows] PRIMARY KEY  CLUSTERED 
	(
		[UserDefinedRowId]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[UserDefinedFields] WITH NOCHECK ADD 
	CONSTRAINT [DF_UserDefinedFields_FieldOrder] DEFAULT (0) FOR [FieldOrder]
GO

ALTER TABLE [dbo].[UserDefinedData] ADD 
	CONSTRAINT [FK_UserDefinedData_UserDefinedFields] FOREIGN KEY 
	(
		[UserDefinedFieldId]
	) REFERENCES [dbo].[UserDefinedFields] (
		[UserDefinedFieldId]
	) NOT FOR REPLICATION ,
	CONSTRAINT [FK_UserDefinedData_UserDefinedRows] FOREIGN KEY 
	(
		[UserDefinedRowId]
	) REFERENCES [dbo].[UserDefinedRows] (
		[UserDefinedRowId]
	) NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[UserDefinedFields] ADD 
	CONSTRAINT [FK_UserDefinedFields_Modules] FOREIGN KEY 
	(
		[ModuleId]
	) REFERENCES [dbo].[Modules] (
		[ModuleID]
	) ON DELETE CASCADE  NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[UserDefinedRows] ADD 
	CONSTRAINT [FK_UserDefinedRows_Modules] FOREIGN KEY 
	(
		[ModuleId]
	) REFERENCES [dbo].[Modules] (
		[ModuleID]
	) ON DELETE CASCADE  NOT FOR REPLICATION 
GO

/* stored procedures */
if exists (select * from dbo.sysobjects where id = object_id(N'[AddUserDefinedField]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure AddUserDefinedField
GO


create procedure AddUserDefinedField

@ModuleId     int,
@FieldTitle   varchar(50),
@Visible      bit,
@FieldType    varchar(20)

as

declare @FieldOrder int

select @FieldOrder = count(*) + 1
from   UserDefinedFields
where  ModuleId = @ModuleId

insert UserDefinedFields ( 
  ModuleId,
  FieldTitle,
  Visible,
  FieldType
)
values (
  @ModuleId,
  @FieldTitle,
  @Visible,
  @FieldType
)

return 1

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[AddUserDefinedRow]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure AddUserDefinedRow
GO

create procedure AddUserDefinedRow

@ModuleId         int,
@UserDefinedRowId int OUTPUT

as

insert UserDefinedRows ( 
  ModuleId
)
values (
  @ModuleId
)

select @UserDefinedRowId = @@IDENTITY

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[DeleteUserDefinedField]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure DeleteUserDefinedField
GO

create procedure DeleteUserDefinedField

@UserDefinedFieldId    int 

as

delete 
from   UserDefinedData
where  UserDefinedFieldId = @UserDefinedFieldId

delete 
from   UserDefinedFields
where  UserDefinedFieldId = @UserDefinedFieldId

return 1

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[DeleteUserDefinedRow]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure DeleteUserDefinedRow
GO

create procedure DeleteUserDefinedRow

@UserDefinedRowId    int 

as

delete 
from   UserDefinedData
where  UserDefinedRowId = @UserDefinedRowId

delete 
from   UserDefinedRows
where  UserDefinedRowId = @UserDefinedRowId

return 1

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[GetSingleUserDefinedField]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetSingleUserDefinedField
GO

create procedure GetSingleUserDefinedField

@UserDefinedFieldId  int

as

select ModuleId,
       FieldTitle,
       Visible,
       FieldOrder
from   UserDefinedFields
where  UserDefinedFieldId = @UserDefinedFieldId

return 1

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[GetSingleUserDefinedRow]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetSingleUserDefinedRow
GO

create procedure GetSingleUserDefinedRow

@UserDefinedRowID   int,
@ModuleId           int

as

select UserDefinedFields.FieldTitle,
       UserDefinedData.FieldValue
from   UserDefinedData
inner join UserDefinedFields on UserDefinedData.UserDefinedFieldId = UserDefinedFields.UserDefinedFieldId
where  UserDefinedData.UserDefinedRowID = @UserDefinedRowID
and    UserDefinedFields.ModuleId = @ModuleId

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[GetUserDefinedFields]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetUserDefinedFields
GO

create procedure GetUserDefinedFields

@ModuleId  int

as

select UserDefinedFieldId,
       FieldTitle,
       Visible,
       FieldType
from   UserDefinedFields
where  ModuleId = @ModuleId
order by FieldOrder

return 1

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[GetUserDefinedRows]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure GetUserDefinedRows
GO

create procedure GetUserDefinedRows

@ModuleId    int 

as

select UserDefinedRows.UserDefinedRowId,
       UserDefinedFields.FieldTitle,
       UserDefinedData.FieldValue
from   UserDefinedRows
left outer join UserDefinedData on UserDefinedRows.UserDefinedRowId = UserDefinedData.UserDefinedRowId
inner join UserDefinedFields on UserDefinedData.UserDefinedFieldId = UserDefinedFields.UserDefinedFieldId 
where  UserDefinedRows.ModuleId = @ModuleId

return 1

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[UpdateUserDefinedData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure UpdateUserDefinedData
GO

create procedure UpdateUserDefinedData

@UserDefinedRowId    int,
@UserDefinedFieldId  int,
@FieldValue          nvarchar(2000) = null

as

if @FieldValue is null
begin
  if exists ( select 1 from UserDefinedData where UserDefinedFieldId = @UserDefinedFieldId and UserDefinedRowId = @UserDefinedRowId )
  begin
    delete
    from UserDefinedData
    where UserDefinedFieldId = @UserDefinedFieldId
    and UserDefinedRowId = @UserDefinedRowId
  end
end
else
begin
  if not exists ( select 1 from UserDefinedData where UserDefinedFieldId = @UserDefinedFieldId and UserDefinedRowId = @UserDefinedRowId )
  begin
    insert UserDefinedData ( 
      UserDefinedFieldId,
      UserDefinedRowId,
      FieldValue
    )
    values (
      @UserDefinedFieldId,
      @UserDefinedRowId,
      @FieldValue
    )
  end
  else
  begin
    update UserDefinedData
    set    FieldValue = @FieldValue
    where UserDefinedFieldId = @UserDefinedFieldId
    and UserDefinedRowId = @UserDefinedRowId
  end
end

return 1

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[UpdateUserDefinedField]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure UpdateUserDefinedField
GO

create procedure UpdateUserDefinedField

@UserDefinedFieldId   int,
@FieldTitle           varchar(50),
@Visible              bit,
@FieldType            varchar(20)

as

update UserDefinedFields
set    FieldTitle = @FieldTitle,
       Visible = @Visible,
       FieldType = @FieldType
where  UserDefinedFieldId = @UserDefinedFieldId

return 1

GO