📄 03.01.01.sqldataprovider

📁 SharpNuke源代码
💻 SQLDATAPROVIDER
📖 第 1 页 / 共 4 页
字号:
12 3 4 下一页
/************************************************************/
/*****              SqlDataProvider                     *****/
/*****                                                  *****/
/*****                                                  *****/
/***** Note: To manually execute this script you must   *****/
/*****       perform a search and replace operation     *****/
/*****       for {databaseOwner} and {objectQualifier}  *****/
/*****                                                  *****/
/************************************************************/

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

CREATE procedure {databaseOwner}{objectQualifier}DeleteUserPortal
	@UserId   int,
	@PortalId int
AS

	DELETE FROM {objectQualifier}UserPortals
	WHERE Userid = @UserId and PortalId = @PortalId
GO

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetEventLog
	@PortalID int,
	@LogTypeKey nvarchar(35),
	@PageSize int,
	@PageIndex int
AS

	DECLARE @PageLowerBound int
	DECLARE @PageUpperBound int
	-- Set the page bounds
	SET @PageLowerBound = @PageSize * @PageIndex
	SET @PageUpperBound = @PageLowerBound + @PageSize + 1

	CREATE TABLE #PageIndex 
	(
		IndexID		int IDENTITY (1, 1) NOT NULL,
		LogGUID	varchar(36)
	)

	INSERT INTO #PageIndex (LogGUID)
	SELECT {objectQualifier}EventLog.LogGUID
	FROM {objectQualifier}EventLog
	INNER JOIN {objectQualifier}EventLogConfig
		ON {objectQualifier}EventLog.LogConfigID = {objectQualifier}EventLogConfig.ID
	WHERE (LogPortalID = @PortalID or @PortalID IS NULL)
		AND ({objectQualifier}EventLog.LogTypeKey = @LogTypeKey or @LogTypeKey IS NULL)
	ORDER BY LogCreateDate DESC


	SELECT {objectQualifier}EventLog.*
	FROM {objectQualifier}EventLog
	INNER JOIN {objectQualifier}EventLogConfig
		ON {objectQualifier}EventLog.LogConfigID = {objectQualifier}EventLogConfig.ID
	INNER JOIN #PageIndex PageIndex
		ON {objectQualifier}EventLog.LogGUID = PageIndex.LogGUID
	WHERE PageIndex.IndexID			> @PageLowerBound	
		AND	PageIndex.IndexID			< @PageUpperBound	
	ORDER BY
		PageIndex.IndexID	

	SELECT COUNT(*) as TotalRecords
	FROM #PageIndex
GO


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


CREATE PROCEDURE {databaseOwner}{objectQualifier}GetScheduleByTypeFullName
	@TypeFullName varchar(200),
	@Server varchar(150)
AS

	SELECT S.ScheduleID, S.TypeFullName, S.TimeLapse, S.TimeLapseMeasurement,  S.RetryTimeLapse, S.RetryTimeLapseMeasurement, S.ObjectDependencies, S.AttachToEvent, S.RetainHistoryNum, S.CatchUpEnabled, S.Enabled, S.Servers
	FROM {objectQualifier}Schedule S
	WHERE S.TypeFullName = @TypeFullName 
	AND (S.Servers LIKE ',%' + @Server + '%,' or S.Servers 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, S.Servers
GO


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


CREATE PROCEDURE {databaseOwner}{objectQualifier}GetAffiliate
	@AffiliateId int,
	@VendorId int,
	@PortalID int
AS

	SELECT	{objectQualifier}Affiliates.AffiliateId,
		{objectQualifier}Affiliates.VendorId,
		{objectQualifier}Affiliates.StartDate,
		{objectQualifier}Affiliates.EndDate,
		{objectQualifier}Affiliates.CPC,
		{objectQualifier}Affiliates.Clicks,
		{objectQualifier}Affiliates.CPA,
		{objectQualifier}Affiliates.Acquisitions
	FROM	{objectQualifier}Affiliates 
	INNER JOIN {objectQualifier}Vendors ON {objectQualifier}Affiliates.VendorId = {objectQualifier}Vendors.VendorId
	WHERE	{objectQualifier}Affiliates.AffiliateId = @AffiliateId
	AND	{objectQualifier}Affiliates.VendorId = @VendorId
	AND	({objectQualifier}Vendors.PortalId = @PortalID or ({objectQualifier}Vendors.PortalId is null and @portalid is null))
GO


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


CREATE PROCEDURE {databaseOwner}{objectQualifier}FindBanners
	@PortalId     int,
	@BannerTypeId int,
	@GroupName    nvarchar(100)
AS

	SELECT 	BannerId,
		{objectQualifier}Banners.VendorId,
		BannerName,
		URL,
		'ImageFile' = case when {objectQualifier}Files.FileName is null then {objectQualifier}Banners.ImageFile else {objectQualifier}Files.Folder + {objectQualifier}Files.FileName end,
		Impressions,
		CPM,
		{objectQualifier}Banners.Views,
		{objectQualifier}Banners.ClickThroughs,
		StartDate,
		EndDate,
		BannerTypeId,
		Description,
		GroupName,
		Criteria,
		{objectQualifier}Banners.Width,
		{objectQualifier}Banners.Height
	FROM	{objectQualifier}Banners
	INNER JOIN {objectQualifier}Vendors ON {objectQualifier}Banners.VendorId = {objectQualifier}Vendors.VendorId
	LEFT OUTER JOIN {objectQualifier}Files ON {objectQualifier}Banners.ImageFile = 'fileid=' + convert(varchar,{objectQualifier}Files.FileID)
	WHERE	({objectQualifier}Banners.BannerTypeId = @BannerTypeId or @BannerTypeId is null)
	AND	({objectQualifier}Banners.GroupName = @GroupName or @GroupName is null)
	AND	(({objectQualifier}Vendors.PortalId = @PortalId) or (@PortalId is null and {objectQualifier}Vendors.PortalId is null))
	AND	({objectQualifier}Banners.StartDate is null Or getdate() >= {objectQualifier}Banners.StartDate )
	AND	(  ( Criteria = 1 /* OR */
           		and ({objectQualifier}Banners.Impressions >= {objectQualifier}Banners.Views Or {objectQualifier}Banners.Impressions = 0)
           		and (getdate() <= {objectQualifier}Banners.EndDate or {objectQualifier}Banners.EndDate is null) )
       		or ( Criteria = 0 /* AND */
           		and ({objectQualifier}Banners.Impressions >= {objectQualifier}Banners.Views and {objectQualifier}Banners.Impressions <> 0)
           		or  (getdate() <= {objectQualifier}Banners.EndDate and {objectQualifier}Banners.EndDate is not null) )  )
	AND 	{objectQualifier}Vendors.Authorized = 1 
	ORDER BY BannerId
GO


UPDATE 	{databaseOwner}[{objectQualifier}Lists] 
SET 	Value = 'NL', Text = 'Newfoundland and Labrador' 
WHERE	ListName = 'Region'
AND 	ParentID = 36
AND	EntryId = 243
GO


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


CREATE procedure {databaseOwner}{objectQualifier}GetList
	@ListName nvarchar(50),
	@ParentKey nvarchar(150),
	@DefinitionID int
AS

If @ParentKey = '' 
Begin
	Select DISTINCT 	
	E.[ListName],
	E.[Level],	
	E.[DefinitionID],
	E.[ParentID],	
	(SELECT MAX([SortOrder]) FROM {objectQualifier}Lists WHERE [ListName] = E.[ListName]) As [MaxSortOrder],
	(SELECT COUNT(EntryID) FROM {objectQualifier}Lists WHERE [ListName] = E.[ListName] AND ParentID = E.[ParentID]) As EntryCount,
	IsNull((SELECT [ListName] + '.' + [Value] + ':' FROM {objectQualifier}Lists WHERE [EntryID] = E.[ParentID]), '') + E.[ListName] As [Key],	
	IsNull((SELECT [ListName] + '.' + [Text] + ':' FROM {objectQualifier}Lists WHERE [EntryID] = E.[ParentID]), '') + E.[ListName] As [DisplayName],
	IsNull((SELECT [ListName] + '.' + [Value] FROM {objectQualifier}Lists WHERE [EntryID] = E.[ParentID]), '') As [ParentKey],
	IsNull((SELECT [ListName] + '.' + [Text] FROM {objectQualifier}Lists WHERE [EntryID] = E.[ParentID]), '') As [Parent],
	IsNull((SELECT [ListName] FROM {objectQualifier}Lists WHERE [EntryID] = E.[ParentID]),'') As [ParentList]
	From {objectQualifier}Lists E (nolock)
	where  ([ListName] = @ListName or @ListName='')
	and (DefinitionID = @DefinitionID or @DefinitionID = -1)
	Order By E.[Level], E.[DisplayName]
End
Else
Begin

	DECLARE @ParentListName nvarchar(50)
	DECLARE @ParentValue nvarchar(100)
	SET @ParentListName = LEFT(@ParentKey, CHARINDEX( '.', @ParentKey) - 1)
	SET @ParentValue = RIGHT(@ParentKey, LEN(@ParentKey) -  CHARINDEX( '.', @ParentKey))
	Select DISTINCT 	
	E.[ListName],
	E.[Level],	
	E.[DefinitionID],
	E.[ParentID],	
	(SELECT MAX([SortOrder]) FROM {objectQualifier}Lists WHERE [ListName] = E.[ListName]) As [MaxSortOrder],
	(SELECT COUNT(EntryID) FROM {objectQualifier}Lists WHERE [ListName] = E.[ListName] AND ParentID = E.[ParentID]) As EntryCount,
	IsNull((SELECT [ListName] + '.' + [Value] + ':' FROM {objectQualifier}Lists WHERE [EntryID] = E.[ParentID]), '') + E.[ListName] As [Key],	
	IsNull((SELECT [ListName] + '.' + [Text] + ':' FROM {objectQualifier}Lists WHERE [EntryID] = E.[ParentID]), '') + E.[ListName] As [DisplayName],
	IsNull((SELECT [ListName] + '.' + [Value] FROM {objectQualifier}Lists WHERE [EntryID] = E.[ParentID]), '') As [ParentKey],
	IsNull((SELECT [ListName] + '.' + [Text] FROM {objectQualifier}Lists WHERE [EntryID] = E.[ParentID]), '') As [Parent],
	IsNull((SELECT [ListName] FROM {objectQualifier}Lists WHERE [EntryID] = E.[ParentID]),'') As [ParentList]
	
	From {objectQualifier}Lists E (nolock)
	where  [ListName] = @ListName And
	[ParentID] = (SELECT [EntryID] From {objectQualifier}Lists Where [ListName] = @ParentListName And [Value] = @ParentValue)	
	Order By E.[Level], E.[DisplayName]

End
GO


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


CREATE procedure {databaseOwner}{objectQualifier}GetVendors
	@PortalID int,
	@UnAuthorized bit,
	@PageSize int,
	@PageIndex int
AS

	DECLARE @PageLowerBound int
	DECLARE @PageUpperBound int
	-- Set the page bounds
	SET @PageLowerBound = @PageSize * @PageIndex
	SET @PageUpperBound = @PageLowerBound + @PageSize + 1

	CREATE TABLE #PageIndex 
	(
		IndexID		int IDENTITY (1, 1) NOT NULL,
		VendorId	int
	)

	INSERT INTO #PageIndex (VendorId)
	SELECT VendorId
	FROM {objectQualifier}Vendors
	WHERE ( ((Authorized = 0 AND @UnAuthorized = 1) OR @UnAuthorized = 0 ) AND ((PortalId = @PortalId) or (@PortalId is null and PortalId is null)) )
	ORDER BY VendorId DESC


	SELECT COUNT(*) as TotalRecords
	FROM #PageIndex


	SELECT {objectQualifier}Vendors.*,
       		'Banners' = ( select count(*) from {objectQualifier}Banners where {objectQualifier}Banners.VendorId = {objectQualifier}Vendors.VendorId )
	FROM {objectQualifier}Vendors
	INNER JOIN #PageIndex PageIndex
		ON {objectQualifier}Vendors.VendorId = PageIndex.VendorId
	WHERE ( (PageIndex.IndexID > @PageLowerBound) OR @PageLowerBound is null )	
		AND ( (PageIndex.IndexID < @PageUpperBound) OR @PageUpperBound is null )	
	ORDER BY
		PageIndex.IndexID	
GO


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


CREATE procedure {databaseOwner}{objectQualifier}GetVendorsByEmail
	@Filter nvarchar(50),
	@PortalID int,
	@PageSize int,
	@PageIndex int
AS

	DECLARE @PageLowerBound int
	DECLARE @PageUpperBound int
	-- Set the page bounds
	SET @PageLowerBound = @PageSize * @PageIndex
	SET @PageUpperBound = @PageLowerBound + @PageSize + 1

	CREATE TABLE #PageIndex 
	(
		IndexID		int IDENTITY (1, 1) NOT NULL,
		VendorId	int
	)

	INSERT INTO #PageIndex (VendorId)
	SELECT VendorId
	FROM {objectQualifier}Vendors
	WHERE ( (Email = @Filter) AND ((PortalId = @PortalId) or (@PortalId is null and PortalId is null)) )
	ORDER BY VendorId DESC


	SELECT COUNT(*) as TotalRecords
	FROM #PageIndex


	SELECT {objectQualifier}Vendors.*,
       		'Banners' = ( select count(*) from {objectQualifier}Banners where {objectQualifier}Banners.VendorId = {objectQualifier}Vendors.VendorId )
	FROM {objectQualifier}Vendors
	INNER JOIN #PageIndex PageIndex
		ON {objectQualifier}Vendors.VendorId = PageIndex.VendorId
	WHERE ( (PageIndex.IndexID > @PageLowerBound) OR @PageLowerBound is null )	
		AND ( (PageIndex.IndexID < @PageUpperBound) OR @PageUpperBound is null )	
	ORDER BY
		PageIndex.IndexID	
GO


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


CREATE procedure {databaseOwner}{objectQualifier}GetVendorsByName
	@Filter nvarchar(50),
	@PortalID int,
	@PageSize int,
	@PageIndex int
AS

	DECLARE @PageLowerBound int
	DECLARE @PageUpperBound int
	-- Set the page bounds
	SET @PageLowerBound = @PageSize * @PageIndex
	SET @PageUpperBound = @PageLowerBound + @PageSize + 1

	CREATE TABLE #PageIndex 
	(
		IndexID		int IDENTITY (1, 1) NOT NULL,
		VendorId	int
	)

	INSERT INTO #PageIndex (VendorId)
	SELECT VendorId
	FROM {objectQualifier}Vendors
	WHERE ( (VendorName like @Filter + '%') AND ((PortalId = @PortalId) or (@PortalId is null and PortalId is null)) )
	ORDER BY VendorId DESC
12 3 4 下一页
⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -