03.00.04.sqldataprovider

SharpNuke源代码

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

ALTER TABLE {databaseOwner}{objectQualifier}Vendors
	ADD Cell VARCHAR(50) NULL
GO

ALTER TABLE {databaseOwner}{objectQualifier}SearchItem 
	ALTER COLUMN Guid varchar(200)
GO

ALTER TABLE {databaseOwner}{objectQualifier}SearchItem 
	ADD ImageFileId int
GO

drop procedure {databaseOwner}{objectQualifier}GetSearchResults
GO

/*****************************************************************************
 *  This procedure applies filters to the SearchItems to make sure that only
 *  active Search content is returned.  All security checks will be done in 
 *  the Business Logic.
 *****************************************************************************/
CREATE procedure {databaseOwner}{objectQualifier}GetSearchResults
	@PortalID	int,
	@Word 	nVarChar(100)
AS
	DECLARE @TempList table
	(
		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
	)

	INSERT @TempList
	SELECT  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
	FROM   {databaseOwner}{objectQualifier}SearchWord sw INNER JOIN
	         	{databaseOwner}{objectQualifier}SearchItemWord siw ON sw.SearchWordsID = siw.SearchWordsID INNER JOIN
	          	{databaseOwner}{objectQualifier}SearchItem si ON siw.SearchItemID = si.SearchItemID INNER JOIN
	         	{databaseOwner}{objectQualifier}Modules m ON si.ModuleId = m.ModuleID LEFT OUTER JOIN
	          	{databaseOwner}{objectQualifier}TabModules tm ON si.ModuleId = tm.ModuleID INNER JOIN
	          	{databaseOwner}{objectQualifier}Tabs t ON tm.TabID = t.TabID
	WHERE     (sw.Word = @Word) 
		AND (t.IsDeleted = 0) 
		AND (m.IsDeleted = 0)
		AND (t.PortalID = @PortalID)

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

create procedure {databaseOwner}{objectQualifier}GetSearchSettings

	@ModuleID	int

AS

select     	tm.ModuleID, 
			settings.SettingName, 
			settings.SettingValue
from	{objectQualifier}Tabs searchTabs INNER JOIN
		{objectQualifier}TabModules searchTabModules ON searchTabs.TabID = searchTabModules.TabID INNER JOIN
        {objectQualifier}Portals p ON searchTabs.PortalID = p.PortalID INNER JOIN
        {objectQualifier}Tabs t ON p.PortalID = t.PortalID INNER JOIN
        {objectQualifier}TabModules tm ON t.TabID = tm.TabID INNER JOIN
        {objectQualifier}ModuleSettings settings ON searchTabModules.ModuleID = settings.ModuleID
where   searchTabs.TabName = N'Search Admin'
and		tm.ModuleID = @ModuleID

GO

drop procedure {databaseOwner}{objectQualifier}UserLogin
GO

drop procedure {databaseOwner}{objectQualifier}GetSiteLog3
GO

create procedure {databaseOwner}{objectQualifier}GetSiteLog3

	@PortalId int,
	@PortalAlias nvarchar(50),
	@StartDate datetime,
	@EndDate datetime

as

select 'Name' = {objectQualifier}Users.FirstName + ' ' + {objectQualifier}Users.LastName,
	'Requests' = count(*),
	'LastRequest' = max(DateTime)
from {objectQualifier}SiteLog
inner join {objectQualifier}Users on {objectQualifier}SiteLog.UserId = {objectQualifier}Users.UserId
where {objectQualifier}SiteLog.PortalId = @PortalId
and {objectQualifier}SiteLog.DateTime between @StartDate and @EndDate
and {objectQualifier}SiteLog.UserId is not null
group by {objectQualifier}Users.FirstName + ' ' + {objectQualifier}Users.LastName
order by Requests desc

GO

drop procedure {databaseOwner}{objectQualifier}AddSearchItem
GO

create procedure {databaseOwner}{objectQualifier}AddSearchItem

	@Title nvarchar(200),
	@Description nvarchar(2000),
	@Author int,
	@PubDate datetime,
	@ModuleId int,
	@SearchKey nvarchar(100),
	@Guid nvarchar(200), 
	@ImageFileId int

as

insert into {objectQualifier}SearchItem (
	Title,
	Description,
	Author,
	PubDate,
	ModuleId,
 	SearchKey,
	Guid,
	HitCount,
	ImageFileId
) 
values (
	@Title,
	@Description,
	@Author,
	@PubDate,
	@ModuleId,
	@SearchKey,
	@Guid,
	0,
	@ImageFileId
)

select SCOPE_IDENTITY()

GO

create procedure {databaseOwner}{objectQualifier}DeleteSearchItemWords
	@SearchItemID int
AS

delete from {databaseOwner}{objectQualifier}SearchItemWord
where
	[SearchItemID] = @SearchItemID
GO

drop procedure {databaseOwner}{objectQualifier}GetSearchItem
GO

create procedure {databaseOwner}{objectQualifier}GetSearchItem
	@ModuleId int,
	@SearchKey varchar(100) 
AS

select
	[SearchItemID],
	[Title],
	[Description],
	[Author],
	[PubDate],
	[ModuleId],
	[SearchKey],
	[Guid],
	[HitCount],
	ImageFileId
from
	{objectQualifier}SearchItem
where
	[ModuleID] = @ModuleID AND
	[SearchKey] = @SearchKey

GO

drop procedure {databaseOwner}{objectQualifier}ListSearchItem
GO

create procedure {databaseOwner}{objectQualifier}ListSearchItem

AS

select
	[SearchItemID],
	[Title],
	[Description],
	[Author],
	[PubDate],
	[ModuleId],
	[SearchKey],
	[Guid],
	[HitCount],
	ImageFileId
from
	{objectQualifier}SearchItem

GO

drop procedure {databaseOwner}{objectQualifier}UpdateSearchItem
GO

create procedure {databaseOwner}{objectQualifier}UpdateSearchItem
	@SearchItemID int, 
	@Title varchar(200), 
	@Description varchar(500), 
	@Author int, 
	@PubDate datetime, 
	@ModuleId int, 
	@SearchKey varchar(100), 
	@Guid nvarchar(200), 
	@HitCount int, 
	@ImageFileId int
AS

UPDATE {objectQualifier}SearchItem SET
	[Title] = @Title,
	[Description] = @Description,
	[Author] = @Author,
	[PubDate] = @PubDate,
	[ModuleId] = @ModuleId,
	[SearchKey] = @SearchKey,
	[Guid] = @Guid,
	[HitCount] = @HitCount,
	ImageFileId = 	@ImageFileId
WHERE
	[SearchItemID] = @SearchItemID

GO

drop procedure {databaseOwner}{objectQualifier}AddVendor
GO

CREATE procedure {databaseOwner}{objectQualifier}AddVendor

@PortalId 	int,
@VendorName nvarchar(50),
@Unit    	nvarchar(50),
@Street 	nvarchar(50),
@City		nvarchar(50),
@Region	    nvarchar(50),
@Country	nvarchar(50),
@PostalCode	nvarchar(50),
@Telephone	nvarchar(50),
@Fax   	    nvarchar(50),
@Cell   	nvarchar(50),
@Email    	nvarchar(50),
@Website	nvarchar(100),
@FirstName	nvarchar(50),
@LastName	nvarchar(50),
@UserName   nvarchar(100),
@LogoFile   nvarchar(100),
@KeyWords   text,
@Authorized bit

as

insert into {objectQualifier}Vendors (
  VendorName,
  Unit,
  Street,
  City,
  Region,
  Country,
  PostalCode,
  Telephone,
  PortalId,
  Fax,
  Cell,
  Email,
  Website,
  FirstName,
  Lastname,
  ClickThroughs,
  Views,
  CreatedByUser,
  CreatedDate,
  LogoFile,
  KeyWords,
  Authorized
)
values (
  @VendorName,
  @Unit,
  @Street,
  @City,
  @Region,
  @Country,
  @PostalCode,
  @Telephone,
  @PortalId,
  @Fax,
  @Cell,
  @Email,
  @Website,
  @FirstName,
  @LastName,
  0,
  0,
  @UserName,
  getdate(), 
  @LogoFile,
  @KeyWords,
  @Authorized
)

select SCOPE_IDENTITY()
GO

drop procedure {databaseOwner}{objectQualifier}UpdateVendor
GO

CREATE procedure {databaseOwner}{objectQualifier}UpdateVendor

@VendorId	int,
@VendorName nvarchar(50),
@Unit	 	nvarchar(50),
@Street 	nvarchar(50),
@City		nvarchar(50),
@Region	    nvarchar(50),
@Country	nvarchar(50),
@PostalCode	nvarchar(50),
@Telephone	nvarchar(50),
@Fax		nvarchar(50),
@Cell		nvarchar(50),
@Email		nvarchar(50),
@Website	nvarchar(100),
@FirstName	nvarchar(50),
@LastName	nvarchar(50),
@UserName   nvarchar(100),
@LogoFile   nvarchar(100),
@KeyWords   text,
@Authorized bit

as

update {objectQualifier}Vendors
set    VendorName    = @VendorName,
       Unit          = @Unit,
       Street        = @Street,
       City          = @City,
       Region        = @Region,
       Country       = @Country,
       PostalCode    = @PostalCode,
       Telephone     = @Telephone,
       Fax           = @Fax,
       Cell          = @Cell,
       Email         = @Email,
       Website       = @Website,
       FirstName     = @FirstName,