03.00.08.sqldataprovider

SharpNuke源代码

/************************************************************/
/*****              SqlDataProvider                     *****/
/*****                                                  *****/
/*****                                                  *****/
/***** Note: To manually execute this script you must   *****/
/*****       perform a search and replace operation     *****/
/*****       for {databaseOwner} and {objectQualifier}  *****/
/*****                                                  *****/
/************************************************************/

INSERT INTO {databaseOwner}{objectQualifier}ModuleControls ([ModuleDefID], [ControlKey], [ControlTitle], [ControlSrc], [IconFile], [ControlType], [ViewOrder], [HelpUrl]) VALUES (NULL, N'TREEVIEW', NULL, N'Admin/Skins/TreeViewMenu.ascx', NULL, -2, NULL, NULL)
GO

drop procedure {databaseOwner}{objectQualifier}GetSearchItems
GO

create procedure {databaseOwner}{objectQualifier}GetSearchItems

@PortalId int,
@TabId int,
@ModuleId int

as

select si.*,
       'AuthorName' = u.FirstName + ' ' + u.LastName,
       t.TabId
from   {objectQualifier}SearchItem si
left outer join {objectQualifier}Users u ON si.Author = u.UserID
inner join {objectQualifier}Modules m ON si.ModuleId = m.ModuleID 
inner join {objectQualifier}TabModules tm ON m.ModuleId = tm.ModuleID 
inner join {objectQualifier}Tabs t ON tm.TabID = t.TabID
inner join {objectQualifier}Portals p ON t.PortalID = p.PortalID
where (p.PortalId = @PortalId or @PortalId is null)
and   (t.TabId = @TabId or @TabId is null)
and   (m.ModuleId = @ModuleId or @ModuleId is null)

GO

drop procedure {databaseOwner}{objectQualifier}GetSearchResults
GO

CREATE procedure {databaseOwner}{objectQualifier}GetSearchResults
	@PortalID	int,
	@Word 	nVarChar(100)
AS
	DECLARE @TempList table
	(
		SearchItemID int,
		Word nvarchar(100),
		Occurences int,
		Relevance int,
		ModuleID int,
		TabID int,
		ModStartDate datetime,
		ModEndDate datetime,
		TabStartDate datetime,
		TabEndDate datetime,
		Title varchar(200),
		Description varchar(500),
		Author int,
		PubDate datetime,
		SearchKey varchar(100),
		Guid varchar(200),
		ImageFileId int,
        AuthorName nvarchar(200)
	)

	INSERT @TempList
	SELECT  si.SearchItemID,
		sw.Word, 
		siw.Occurrences, 
		siw.Occurrences + 1000 as Relevance, 
		m.ModuleID, 
		tm.TabID,
		ISNULL(m.StartDate, GETDATE() - 1) AS ModStartDate, 
		ISNULL(m.EndDate, GETDATE() + 1) AS ModEndDate, 
		ISNULL(t.StartDate, GETDATE() - 1) AS TabStartDate, 
		ISNULL(t.EndDate, GETDATE() + 1) AS TabEndDate, 
		si.Title, 
		si.Description, 
		si.Author, 
		si.PubDate, 
		si.SearchKey,
		si.Guid,
		si.ImageFileId,
                u.FirstName + ' ' + u.LastName
	FROM	{objectQualifier}SearchWord sw 
        INNER JOIN {objectQualifier}SearchItemWord siw ON sw.SearchWordsID = siw.SearchWordsID 
        INNER JOIN {objectQualifier}SearchItem si ON siw.SearchItemID = si.SearchItemID 
        INNER JOIN {objectQualifier}Modules m ON si.ModuleId = m.ModuleID 
        LEFT OUTER JOIN {objectQualifier}TabModules tm ON si.ModuleId = tm.ModuleID 
        INNER JOIN {objectQualifier}Tabs t ON tm.TabID = t.TabID
        LEFT OUTER JOIN {objectQualifier}Users u ON si.Author = u.UserID
	WHERE (sw.Word = @Word) AND (t.IsDeleted = 0) AND (m.IsDeleted = 0) AND (t.PortalID = @PortalID)

	SELECT	SearchItemID,
		 Word, 
		Occurences, 
		Relevance, 
		ModuleID, 
		TabID, 
		Title, 
		Description,
		Author,
		Pubdate, 
		SearchKey,
		Guid,
		ImageFileId,
                AuthorName
	FROM @TempList 
	WHERE GetDate() between ModStartDate and ModEndDate
	AND GetDate() between TabStartDate and TabEndDate
GO

DROP PROCEDURE {databaseOwner}{objectQualifier}GetSchedule

GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetSchedule
AS
SELECT S.ScheduleID, S.TypeFullName, S.TimeLapse, S.TimeLapseMeasurement,  S.RetryTimeLapse, S.RetryTimeLapseMeasurement, S.ObjectDependencies, S.AttachToEvent, S.RetainHistoryNum, S.CatchUpEnabled, S.Enabled, SH.NextStart
FROM {objectQualifier}Schedule S
LEFT JOIN {databaseOwner}{objectQualifier}ScheduleHistory SH
ON S.ScheduleID = SH.ScheduleID
WHERE SH.ScheduleHistoryID = (SELECT TOP 1 S1.ScheduleHistoryID FROM {objectQualifier}ScheduleHistory S1 WHERE S1.ScheduleID = S.ScheduleID ORDER BY S1.NextStart DESC)
OR  SH.ScheduleHistoryID IS NULL
GROUP BY S.ScheduleID, S.TypeFullName, S.TimeLapse, S.TimeLapseMeasurement,  S.RetryTimeLapse, S.RetryTimeLapseMeasurement, S.ObjectDependencies, S.AttachToEvent, S.RetainHistoryNum, S.CatchUpEnabled, S.Enabled, SH.NextStart

GO

DROP PROCEDURE {databaseOwner}{objectQualifier}GetSearchResultModules

GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetSearchResultModules
(
	@PortalID int
)
AS

SELECT     
		{objectQualifier}TabModules.TabID, 
		{objectQualifier}Tabs.TabName  AS SearchTabName
FROM	{objectQualifier}Modules INNER JOIN
		{objectQualifier}ModuleDefinitions ON {objectQualifier}Modules.ModuleDefID = {objectQualifier}ModuleDefinitions.ModuleDefID INNER JOIN
		{objectQualifier}TabModules ON {objectQualifier}Modules.ModuleID = {objectQualifier}TabModules.ModuleID INNER JOIN
        {objectQualifier}Tabs.TabID ON {objectQualifier}TabModules.TabID = Tabs.TabID
WHERE   {objectQualifier}ModuleDefinitions.FriendlyName = N'Search Results'
AND {objectQualifier}Tabs.PortalID = @PortalID
AND {objectQualifier}Tabs.IsDeleted = 0

GO

ALTER TABLE {databaseOwner}{objectQualifier}TabModules ADD
	DisplayTitle bit NOT NULL CONSTRAINT DF_{objectQualifier}TabModules_DisplayTitle DEFAULT (1),
	DisplayPrint bit NOT NULL CONSTRAINT DF_{objectQualifier}TabModules_DisplayPrint DEFAULT (1),
	DisplaySyndicate bit NOT NULL CONSTRAINT DF_{objectQualifier}TabModules_DisplaySyndicate DEFAULT (1)
GO

drop procedure {databaseOwner}{objectQualifier}AddTabModule
GO

create procedure {databaseOwner}{objectQualifier}AddTabModule
    
@TabId                         int,
@ModuleId                      int,
@ModuleOrder                   int,
@PaneName                      nvarchar(50),
@CacheTime                     int,
@Alignment                     nvarchar(10),
@Color                         nvarchar(20),
@Border                        nvarchar(1),
@IconFile                      nvarchar(100),
@Visibility                    int,
@ContainerSrc                  nvarchar(200),
@DisplayTitle                  bit,
@DisplayPrint                  bit,
@DisplaySyndicate              bit

as

insert into {objectQualifier}TabModules ( 
  TabId,
  ModuleId,
  ModuleOrder,
  PaneName,
  CacheTime,
  Alignment,
  Color,
  Border,
  IconFile,
  Visibility,
  ContainerSrc,
  DisplayTitle,
  DisplayPrint,
  DisplaySyndicate
)
values (
  @TabId,
  @ModuleId,
  @ModuleOrder,
  @PaneName,
  @CacheTime,
  @Alignment,
  @Color,
  @Border,
  @IconFile,
  @Visibility,
  @ContainerSrc,
  @DisplayTitle,
  @DisplayPrint,
  @DisplaySyndicate
)

GO

drop procedure {databaseOwner}{objectQualifier}GetModule
GO

create procedure {databaseOwner}{objectQualifier}GetModule

@ModuleId int,
@TabId    int

as

select M.ModuleID,
       M.ModuleDefID,
       M.ModuleTitle,
       M.AllTabs,
       M.IsDeleted,
       M.InheritViewPermissions,
       M.Header,
       M.Footer,
       M.StartDate,
       M.EndDate,
       M.PortalID,
       TM.TabModuleId,
       TM.TabId,
       TM.PaneName,
       TM.ModuleOrder,
       TM.CacheTime,
       TM.Alignment,
       TM.Color,
       TM.Border,
       TM.Visibility,
       TM.ContainerSrc,
       TM.DisplayTitle,
       TM.DisplayPrint,
       TM.DisplaySyndicate,
       'IconFile' = case when F.FileName is null then TM.IconFile else F.Folder + F.FileName end,
       DM.*
from {objectQualifier}Modules M
inner join {objectQualifier}ModuleDefinitions MD on M.ModuleDefId = MD.ModuleDefId
inner join {objectQualifier}DesktopModules DM on MD.DesktopModuleId = DM.DesktopModuleId
left outer join {objectQualifier}TabModules TM on M.ModuleId = TM.ModuleId
left outer join {objectQualifier}Files F on TM.IconFile = 'fileid=' + convert(varchar,F.FileID)
where   M.ModuleId = @ModuleId
and     (TM.TabId = @TabId or @TabId is null)

GO

drop procedure {databaseOwner}{objectQualifier}GetModuleByDefinition
GO

create procedure {databaseOwner}{objectQualifier}GetModuleByDefinition

@PortalId int,
@FriendlyName nvarchar(128)

as

select M.ModuleID,
       M.ModuleDefID,
       M.ModuleTitle,
       M.AllTabs,
       M.IsDeleted,
       M.InheritViewPermissions,
       M.Header,
       M.Footer,
       M.StartDate,
       M.EndDate,
       M.PortalID,
       TM.TabModuleId,
       TM.TabId,
       TM.PaneName,
       TM.ModuleOrder,
       TM.CacheTime,
       TM.Alignment,
       TM.Color,
       TM.Border,
       TM.Visibility,
       TM.ContainerSrc,
       TM.DisplayTitle,
       TM.DisplayPrint,
       TM.DisplaySyndicate,
       'IconFile' = case when F.FileName is null then TM.IconFile else F.Folder + F.FileName end,
       DM.*
from {objectQualifier}Modules M
inner join {objectQualifier}ModuleDefinitions MD on M.ModuleDefId = MD.ModuleDefId
inner join {objectQualifier}DesktopModules DM on MD.DesktopModuleId = DM.DesktopModuleId
left outer join {objectQualifier}TabModules TM on M.ModuleId = TM.ModuleId
left outer join {objectQualifier}Files F on TM.IconFile = 'fileid=' + convert(varchar,F.FileID)
where  ((M.PortalId = @PortalId) or (M.PortalId is null and @PortalID is null))
and    MD.FriendlyName = @FriendlyName

GO

drop procedure {databaseOwner}{objectQualifier}GetPortalTabModules
GO

create procedure {databaseOwner}{objectQualifier}GetPortalTabModules

@PortalId int, 
@TabId int

as

select M.ModuleID,
       M.ModuleDefID,
       M.ModuleTitle,
       M.AllTabs,
       M.IsDeleted,
       M.InheritViewPermissions,
       M.Header,
       M.Footer,
       M.StartDate,
       M.EndDate,
       M.PortalID,
       TM.TabModuleId,
       TM.TabId,
       TM.PaneName,
       TM.ModuleOrder,
       TM.CacheTime,
       TM.Alignment,
       TM.Color,
       TM.Border,
       TM.Visibility,
       TM.ContainerSrc,
       TM.DisplayTitle,
       TM.DisplayPrint,
       TM.DisplaySyndicate,
       'IconFile' = case when F.FileName is null then TM.IconFile else F.Folder + F.FileName end,
       DM.*,
       MC.ModuleControlId,
       MC.ControlSrc,
       MC.ControlType,
       MC.ControlTitle,
       MC.HelpURL
from {objectQualifier}Modules M
inner join {objectQualifier}TabModules TM on M.ModuleId = TM.ModuleId
inner join {objectQualifier}Tabs T on TM.TabId = T.TabId
inner join {objectQualifier}ModuleDefinitions MD on M.ModuleDefId = MD.ModuleDefId
inner join {objectQualifier}DesktopModules DM on MD.DesktopModuleId = DM.DesktopModuleId
inner join {objectQualifier}ModuleControls MC on MD.ModuleDefId = MC.ModuleDefId
left outer join {objectQualifier}Files F on TM.IconFile = 'fileid=' + convert(varchar,F.FileID)
where  TM.TabId = @TabId
and    ControlKey is null
order by TM.ModuleOrder

GO

drop procedure {databaseOwner}{objectQualifier}UpdateTabModule
GO

create procedure {databaseOwner}{objectQualifier}UpdateTabModule

@TabId               int,
@ModuleId            int,
@ModuleOrder         int,
@PaneName            nvarchar(50),
@CacheTime           int,
@Alignment           nvarchar(10),
@Color               nvarchar(20),
@Border              nvarchar(1),
@IconFile            nvarchar(100),
@Visibility          int,
@ContainerSrc        nvarchar(200),
@DisplayTitle        bit,
@DisplayPrint        bit,
@DisplaySyndicate    bit

as

update {objectQualifier}TabModules
set    ModuleOrder = @ModuleOrder,
       PaneName = @PaneName,
       CacheTime = @CacheTime,
       Alignment = @Alignment,
       Color = @Color,
       Border = @Border,
       IconFile = @IconFile,
       Visibility = @Visibility,
       ContainerSrc = @ContainerSrc,
       DisplayTitle = @DisplayTitle,
       DisplayPrint = @DisplayPrint,
       DisplaySyndicate = @DisplaySyndicate
where  TabId = @TabId
and    ModuleId = @ModuleId

GO

ALTER TABLE {databaseOwner}{objectQualifier}Banners ADD
	URL2 nvarchar(255) NULL
GO

UPDATE {databaseOwner}{objectQualifier}Banners
SET    URL2 = URL
GO

ALTER TABLE {databaseOwner}{objectQualifier}Banners
	DROP COLUMN URL
GO

ALTER TABLE {databaseOwner}{objectQualifier}Banners ADD
	URL nvarchar(255) NULL
GO

UPDATE {databaseOwner}{objectQualifier}Banners
SET    URL = URL2
GO

ALTER TABLE {databaseOwner}{objectQualifier}Banners
	DROP COLUMN URL2
GO

drop procedure {databaseOwner}{objectQualifier}AddBanner
GO

create procedure {databaseOwner}{objectQualifier}AddBanner

@BannerName    nvarchar(100),
@VendorId      int,
@ImageFile     nvarchar(50),
@URL           nvarchar(255),
@Impressions   int,
@CPM           float,
@StartDate     datetime,
@EndDate       datetime,
@UserName      nvarchar(100),
@BannerTypeId  int,
@Description   nvarchar(2000),
@GroupName     nvarchar(100),
@Criteria      bit

as

insert into {objectQualifier}Banners (
    VendorId,
    ImageFile,
    BannerName,
    URL,
    Impressions,
    CPM,
    Views,
    ClickThroughs,
    StartDate,
    EndDate,
    CreatedByUser,
    CreatedDate,
    BannerTypeId,
    Description,
    GroupName,
    Criteria
)