01.00.04.sqldataprovider

SharpNuke源代码

/************************************************************/
/*****              Upgrade Script 1.0.4                *****/
/************************************************************/

ALTER TABLE dbo.VendorClassification
	DROP CONSTRAINT FK_VendorClassification_Vendors
GO

ALTER TABLE dbo.VendorClassification WITH NOCHECK ADD CONSTRAINT
	FK_VendorClassification_Vendors FOREIGN KEY
	(
	VendorId
	) REFERENCES dbo.Vendors
	(
	VendorId
	) ON DELETE CASCADE
	 NOT FOR REPLICATION

GO

ALTER TABLE dbo.VendorClassification
	DROP CONSTRAINT FK_VendorClassification_Classification
GO

ALTER TABLE dbo.VendorClassification WITH NOCHECK ADD CONSTRAINT
	FK_VendorClassification_Classification FOREIGN KEY
	(
	ClassificationId
	) REFERENCES dbo.Classification
	(
	ClassificationId
	) ON DELETE CASCADE
	 NOT FOR REPLICATION

GO

ALTER TABLE dbo.VendorClassification ADD CONSTRAINT
	IX_VendorClassification UNIQUE NONCLUSTERED 
	(
	VendorId,
	ClassificationId
	) ON [PRIMARY]

GO

ALTER TABLE dbo.Tabs ADD
	ParentId int NULL
GO

ALTER TABLE dbo.Tabs WITH NOCHECK ADD CONSTRAINT
	FK_Tabs_Tabs FOREIGN KEY
	(
	ParentId
	) REFERENCES dbo.Tabs
	(
	TabID
	) NOT FOR REPLICATION

GO

update Tabs
set    TabName = 'Super'
where  PortalId is null
GO

declare @TabID int

select @TabID = TabID
from   Tabs
where  PortalID is null

declare @ModuleDefID int

select @ModuleDefID = ModuleDefID
from   ModuleDefinitions
where  FriendlyName = 'Manage Users'

delete
from   Modules
where  TabID = @TabID
and    ModuleDefID = @ModuleDefID
GO

ALTER TABLE dbo.Modules ADD
	AllTabs bit NOT NULL CONSTRAINT DF_Modules_AllTabs DEFAULT 0,
	ShowTitle bit NOT NULL CONSTRAINT DF_Modules_ShowTitle DEFAULT 1,
	Personalize int NOT NULL CONSTRAINT DF_Modules_Personalize DEFAULT 0
GO

drop procedure AddTab
GO

create procedure AddTab

@PortalID        int,
@TabName         nvarchar(50),
@ShowMobile      bit,
@MobileTabName   nvarchar(50),
@AuthorizedRoles nvarchar (256),
@LeftPaneWidth   nvarchar(5),
@RightPaneWidth  nvarchar(5),
@IsVisible       bit,
@ParentId        int,
@TabID           int OUTPUT

as

declare @TabOrder int

select @TabOrder = 0

if @ParentId is null and @IsVisible = 1
begin
  select @TabOrder = TabOrder
  from   Tabs
  where  PortalID = @PortalID
  and    TabName = 'Admin'

  update Tabs
  set    TabOrder = @TabOrder + 2
  where  PortalID = @PortalID
  and    TabName = 'Admin'
end

insert into Tabs (
    PortalID,
    TabName,
    TabOrder,
    ShowMobile,
    MobileTabName,
    AuthorizedRoles,
    LeftPaneWidth,
    RightPaneWidth,
    IsVisible,
    ParentId
)
values (
    @PortalID,
    @TabName,
    @TabOrder,
    @ShowMobile,
    @MobileTabName,
    @AuthorizedRoles,
    @LeftPaneWidth,
    @RightPaneWidth,
    @IsVisible,
    @ParentId
)

select @TabID = @@IDENTITY

GO

drop procedure UpdateTab
GO

create procedure UpdateTab
@TabID           int,
@TabName         nvarchar(50),
@ShowMobile      bit,
@MobileTabName   nvarchar(50),
@AuthorizedRoles nvarchar(256),
@LeftPaneWidth   nvarchar(5),
@RightPaneWidth  nvarchar(5),
@IsVisible       bit,
@ParentId        int

as

update Tabs
set    TabName = @TabName,
       ShowMobile = @ShowMobile,
       MobileTabName = @MobileTabName,
       AuthorizedRoles = @AuthorizedRoles,
       LeftPaneWidth = @LeftPaneWidth,
       RightPaneWidth = @RightPaneWidth,
       IsVisible = @IsVisible,
       ParentId = @ParentId
where  TabID = @TabID

GO

drop procedure DeleteTab
GO

create procedure DeleteTab

@TabID int

as

declare @ParentId int

select @ParentId = null

select @ParentId = ParentId
from   Tabs
where  TabId = @TabId

update Tabs
set    ParentId = @ParentId
where  ParentId = @TabId

delete
from   Tabs
where  TabID = @TabID

GO

create procedure GetTabsByParentId

@ParentId int

as

select TabID,
       TabOrder,
       PortalID,
       TabName,
       MobileTabName,
       AuthorizedRoles,
       ShowMobile,
       LeftPaneWidth,
       RightPaneWidth,
       IsVisible
from   Tabs
where  ParentId = @ParentId

GO

drop procedure GetTabById
GO

create procedure GetTabById

@TabId int

as

select TabID,
       TabOrder,
       PortalID,
       TabName,
       MobileTabName,
       AuthorizedRoles,
       ShowMobile,
       LeftPaneWidth,
       RightPaneWidth,
       IsVisible,
       ParentId
from   Tabs
where  TabId = @TabId

GO

drop procedure UpdateModule
GO

create procedure UpdateModule

@ModuleID       int,
@ModuleTitle    nvarchar(256),
@Alignment      nvarchar(10),
@Color          nvarchar(20),
@Border         nvarchar(1),
@IconFile       nvarchar(100),
@CacheTime      int,
@ViewRoles      nvarchar(256),
@EditRoles      nvarchar(256),
@ShowMobile     bit,
@TabId          int,
@AllTabs        bit,
@ShowTitle      bit,
@Personalize    int

as

declare @OldTabId int
declare @ModuleOrder int

select @OldTabId = TabId
from   Modules
where  ModuleID = @ModuleID

update Modules
set    ModuleTitle = @ModuleTitle,
       CacheTime   = @CacheTime,
       ShowMobile  = @ShowMobile,
       AuthorizedViewRoles = @ViewRoles,
       AuthorizedEditRoles = @EditRoles,
       Alignment = @Alignment,
       Color = @Color,
       Border = @Border,
       IconFile = @IconFile,
       TabId = @TabId,
       AllTabs = @AllTabs,
       ShowTitle = @ShowTitle,
       Personalize = @Personalize
where  ModuleID = @ModuleID

if @OldTabId <> @TabId
begin
  select @ModuleOrder = max(ModuleOrder) + 2
  from   Modules
  where  TabID = @TabId
  and    PaneName = 'ContentPane'

  if @ModuleOrder is null
    select @ModuleOrder = 1

  update Modules
  set    PaneName = 'ContentPane',
         ModuleOrder = @ModuleOrder
  where  ModuleId = @ModuleId
end

if @AllTabs = 1
begin
  update Modules
  set    ModuleOrder = 0
  where  ModuleId = @ModuleId
end

GO

drop procedure UpdateTabModuleOrder
GO

create procedure UpdateTabModuleOrder

@TabID           int

as

declare @PaneName nvarchar(50)
declare @ModuleCounter int
declare @ModuleOrder int

select @PaneName = min(PaneName)
from   Modules
where  TabID = @TabID
while @PaneName is not null 
begin
  select @ModuleCounter = 0

  select @ModuleOrder = min(ModuleOrder)
  from   Modules
  where  TabID = @TabID
  and    PaneName = @PaneName
  and    ModuleOrder <> 0
  while @ModuleOrder is not null
  begin
    select @ModuleCounter = @ModuleCounter + 1        

    update Modules
    set    ModuleOrder = ((@ModuleCounter * 2) - 1) * -1 
    where  TabID = @TabID
    and    PaneName = @PaneName
    and    ModuleOrder = @ModuleOrder  

    select @ModuleOrder = min(ModuleOrder)
    from   Modules
    where  TabID = @TabID
    and    PaneName = @PaneName
    and    ModuleOrder > @ModuleOrder
  end 

  update Modules
  set    ModuleOrder = ModuleOrder * -1 
  where  TabID = @TabID
  and    PaneName = @PaneName

  select @PaneName = min(PaneName)
  from   Modules
  where  TabID = @TabID
  and    PaneName > @PaneName
end

GO

drop procedure UpdateModuleOrder
GO

create procedure UpdateModuleOrder

@ModuleID           int,
@ModuleOrder        int,
@PaneName           nvarchar(50)

as

declare @TabID int
declare @AllTabs bit

select @TabID = TabID,
       @AllTabs = AllTabs
from   Modules
where  ModuleID = @ModuleID

if @ModuleOrder = -1
begin
  select @ModuleOrder = max(ModuleOrder) + 2
  from   Modules
  where  TabID = @TabID
  and    PaneName = @PaneName

  if @ModuleOrder is null
    select @ModuleOrder = 1
end

if @AllTabs = 1
begin
  select @ModuleOrder = 0
end

update Modules
set    ModuleOrder = @ModuleOrder,
       PaneName = @PaneName
where  ModuleID = @ModuleID

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(*),