03.00.09.sqldataprovider

SharpNuke源代码

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

drop procedure {databaseOwner}{objectQualifier}GetUserRolesByUsername
GO

CREATE procedure {databaseOwner}{objectQualifier}GetUserRolesByUsername

@PortalId int, 
@Username nvarchar(100), 
@Rolename nvarchar(50)

as

SELECT	R.*,        
        'FullName' = U.FirstName + ' ' + U.LastName,
        UR.UserRoleID,
        UR.UserID,
        UR.ExpiryDate,
        UR.IsTrialUsed
FROM	{objectQualifier}UserRoles UR
INNER JOIN {objectQualifier}Users U ON UR.UserID = U.UserID
INNER JOIN {objectQualifier}Roles R ON R.RoleID = UR.RoleID
WHERE  R.PortalId = @PortalId
AND    (U.Username = @Username or @Username is NULL)
AND    (R.Rolename = @Rolename or @RoleName is NULL)

GO

drop procedure {databaseOwner}{objectQualifier}GetSearchResultModules
GO

CREATE procedure {databaseOwner}{objectQualifier}GetSearchResultModules

@PortalID int

AS

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

drop procedure {databaseOwner}{objectQualifier}GetFolderPermissionsByFolderPath
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetFolderPermissionsByFolderPath
	@PortalID int,
	@FolderPath varchar(300), 
	@PermissionID int
AS

SELECT
	FP.[FolderPermissionID],
	F.[FolderID],
	F.[FolderPath],
	P.[PermissionID],
	FP.[RoleID],
	case FP.RoleID
		when -1 then 'All Users'
		when -2 then 'Superuser'		
		when -3 then 'Unauthenticated Users'
		else 	R.RoleName
	end
	'RoleName',
	FP.[AllowAccess],
	P.[PermissionCode],
	P.[PermissionKey],
	P.[PermissionName]
FROM
	{objectQualifier}FolderPermission FP
LEFT JOIN {objectQualifier}Folders F ON	FP.FolderID = F.FolderID	
LEFT JOIN {objectQualifier}Permission P ON	FP.PermissionID = P.PermissionID
LEFT JOIN {objectQualifier}Roles R ON	FP.RoleID = R.RoleID
WHERE
	( (F.[FolderPath] = @FolderPath and ((F.[PortalID] = @PortalID) or (F.[PortalID] is null and @PortalID is null)))
          or (F.FolderPath is null and P.PermissionCode = 'SYSTEM_FOLDER') )
AND	(P.[PermissionID] = @PermissionID or @PermissionID = -1)

GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetAllTabsModules

@PortalId int,
@AllTabs bit

AS

select 
  {objectQualifier}tabmodules.tabid,
  {objectQualifier}Modules.*,
  {objectQualifier}DesktopModules.*
from   {objectQualifier}Modules
inner join {objectQualifier}ModuleDefinitions on {objectQualifier}Modules.ModuleDefID = {objectQualifier}ModuleDefinitions.ModuleDefID
inner join {objectQualifier}DesktopModules on {objectQualifier}ModuleDefinitions.DesktopModuleID = {objectQualifier}DesktopModules.DesktopModuleID
inner join {objectQualifier}TabModules on {objectQualifier}Modules.ModuleID = {objectQualifier}TabModules.ModuleID
where  {objectQualifier}Modules.PortalId = @PortalId and {objectQualifier}Modules.AllTabs = @AllTabs
  and {objectQualifier}tabmodules.tabmoduleid =(select min(tabmoduleid) 
		from {objectQualifier}tabmodules
		where Moduleid = {objectQualifier}Modules.ModuleID)
order by {objectQualifier}Modules.ModuleId

GO

declare @ModuleDefID int

select @ModuleDefID = ModuleDefID
from   {objectQualifier}ModuleDefinitions
where  FriendlyName = 'Module Definitions'

INSERT INTO {databaseOwner}{objectQualifier}ModuleControls (
	[ModuleDefID], 
	[ControlKey], 
	[ControlTitle], 
	[ControlSrc], 
	[IconFile], 
	[ControlType], 
	[ViewOrder], 
	[HelpUrl]
) 
VALUES (
	@ModuleDefID, 
	N'Package', 
	N'Create Private Assembly', 
	N'Admin/ModuleDefinitions/PrivateAssembly.ascx', 
	N'icon_moduledefinitions_32px.gif', 
	3, 
	NULL, 
	NULL
)

GO

drop procedure {databaseOwner}{objectQualifier}GetBanner

GO

CREATE procedure {databaseOwner}{objectQualifier}GetBanner

@BannerId int,
@VendorId int,
@PortalID int

as

select {objectQualifier}Banners.BannerId,
       {objectQualifier}Banners.VendorId,
       'ImageFile' = case when {objectQualifier}Files.FileName is null then {objectQualifier}Banners.ImageFile else {objectQualifier}Files.Folder + {objectQualifier}Files.FileName end,
       {objectQualifier}Banners.BannerName,
       {objectQualifier}Banners.Impressions,
       {objectQualifier}Banners.CPM,
       {objectQualifier}Banners.Views,
       {objectQualifier}Banners.ClickThroughs,
       {objectQualifier}Banners.StartDate,
       {objectQualifier}Banners.EndDate,
       'CreatedByUser' = {objectQualifier}Users.FirstName + ' ' + {objectQualifier}Users.LastName,
       {objectQualifier}Banners.CreatedDate,
       {objectQualifier}Banners.BannerTypeId,
       {objectQualifier}Banners.Description,
       {objectQualifier}Banners.GroupName,
       {objectQualifier}Banners.Criteria,
       {objectQualifier}Banners.URL        
FROM   {objectQualifier}Banners 
INNER JOIN {objectQualifier}Vendors ON {objectQualifier}Banners.VendorId = {objectQualifier}Vendors.VendorId 
LEFT OUTER JOIN {objectQualifier}Users ON {objectQualifier}Banners.CreatedByUser = {objectQualifier}Users.UserID
left outer join {objectQualifier}Files on {objectQualifier}Banners.ImageFile = 'FileId=' + convert(varchar,{objectQualifier}Files.FileID)
where  {objectQualifier}Banners.BannerId = @BannerId
and   {objectQualifier}Banners.vendorId = @VendorId
AND {objectQualifier}Vendors.PortalId = @PortalID

GO

UPDATE {objectQualifier}Announcements
	SET URL = 'FileID=' + convert(varchar,F.FileID)
FROM 
	{objectQualifier}Announcements AS A
	INNER JOIN {objectQualifier}Files AS F ON A.URL = F.FileName
GO

UPDATE {objectQualifier}Links
	SET URL = 'FileID=' + convert(varchar,F.FileID)
FROM 
	{objectQualifier}Links AS L
	INNER JOIN {objectQualifier}Files AS F ON L.URL = F.FileName
GO

UPDATE {objectQualifier}Documents
	SET URL = 'FileID=' + convert(varchar,F.FileID)
FROM 
	{objectQualifier}Documents AS D
	INNER JOIN {objectQualifier}Files AS F ON D.URL = F.FileName
GO

DROP procedure {databaseOwner}{objectQualifier}GetSearchResults
GO

CREATE procedure {databaseOwner}{objectQualifier}GetSearchResults
	@PortalID int,
	@Word nVarChar(100)
AS
SELECT si.SearchItemID,
	sw.Word,
	siw.Occurrences,
	siw.Occurrences + 1000 as Relevance,
	m.ModuleID,
	tm.TabID,
	si.Title,
	si.Description,
	si.Author,
	si.PubDate,
	si.SearchKey,
	si.Guid,
	si.ImageFileId,
	u.FirstName + ' ' + u.LastName As AuthorName
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   (((m.StartDate Is Null) OR (GetDate() > m.StartDate)) AND ((m.EndDate Is Null) OR (GetDate() < m.EndDate)))
	AND (((t.StartDate Is Null) OR (GetDate() > t.StartDate)) AND ((t.EndDate Is Null) OR (GetDate() < t.EndDate)))
	AND (sw.Word = @Word) 
	AND (t.IsDeleted = 0) 
	AND (m.IsDeleted = 0) 
	AND (t.PortalID = @PortalID)
GO


/************************************************************/
/*****        Fix Missing Object Qualifiers             *****/
/************************************************************/

SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
SET XACT_ABORT ON
GO