04.03.05.sqldataprovider

完整的商业模板和强大的后台管理功能

IF EXISTS ( SELECT * FROM sysobjects WHERE id = object_id(N'{databaseOwner}{objectQualifier}UpdatePortalInfo') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}{objectQualifier}UpdatePortalInfo
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}UpdatePortalInfo

@PortalId           int,
@PortalName         nvarchar(128),
@LogoFile           nvarchar(50),
@FooterText         nvarchar(100),
@ExpiryDate         datetime,
@UserRegistration   int,
@BannerAdvertising  int,
@Currency           char(3),
@AdministratorId    int,
@HostFee            money,
@HostSpace          int,
@PaymentProcessor   nvarchar(50),
@ProcessorUserId    nvarchar(50),
@ProcessorPassword  nvarchar(50),
@Description        nvarchar(500),
@KeyWords           nvarchar(500),
@BackgroundFile     nvarchar(50),
@SiteLogHistory     int,
@SplashTabId          int,
@HomeTabId          int,
@LoginTabId         int,
@UserTabId          int,
@DefaultLanguage    nvarchar(10),
@TimeZoneOffset	    int,
@HomeDirectory varchar(100)

as

update {databaseOwner}{objectQualifier}Portals
set    PortalName = @PortalName,
       LogoFile = @LogoFile,
       FooterText = @FooterText,
       ExpiryDate = @ExpiryDate,
       UserRegistration = @UserRegistration,
       BannerAdvertising = @BannerAdvertising,
       Currency = @Currency,
       AdministratorId = @AdministratorId,
       HostFee = @HostFee,
       HostSpace = @HostSpace,
       PaymentProcessor = @PaymentProcessor,
       ProcessorUserId = @ProcessorUserId,
       ProcessorPassword = @ProcessorPassword,
       Description = @Description,
       KeyWords = @KeyWords,
       BackgroundFile = @BackgroundFile,
       SiteLogHistory = @SiteLogHistory,
       SplashTabId = @SplashTabId,
       HomeTabId = @HomeTabId,
       LoginTabId = @LoginTabId,
       UserTabId = @UserTabId,
       DefaultLanguage = @DefaultLanguage,
       TimeZoneOffset = @TimeZoneOffset,
       HomeDirectory = @HomeDirectory
where  PortalId = @PortalId

GO

/* Update GetSearchResults */
/***************************/

IF EXISTS ( SELECT * FROM sysobjects WHERE id = object_id(N'{databaseOwner}{objectQualifier}GetSearchResults') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	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,
	m.PortalId
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)
ORDER BY Relevance DESC
GO

/* Update GetSearchResults */
/***************************/

IF EXISTS ( SELECT * FROM sysobjects WHERE id = object_id(N'{databaseOwner}{objectQualifier}GetFoldersByUser') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP procedure {databaseOwner}{objectQualifier}GetFoldersByUser
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetFoldersByUser
	@PortalID int,
	@UserID int,
	@IncludeSecure bit,
	@IncludeDatabase bit,
	@AllowAccess bit,
	@PermissionKeys nvarchar(200)

AS
SELECT DISTINCT
	F.FolderID,
	F.PortalID,
	F.FolderPath,
	F.StorageLocation,
	F.IsProtected,
	F.IsCached,
	F.LastUpdated
FROM {databaseOwner}{objectQualifier}Roles R
	INNER JOIN {databaseOwner}{objectQualifier}UserRoles UR ON R.RoleID = UR.RoleID 
	RIGHT OUTER JOIN {databaseOwner}{objectQualifier}Folders F
		INNER JOIN {databaseOwner}{objectQualifier}FolderPermission FP ON F.FolderID = FP.FolderID 
		INNER JOIN {databaseOwner}{objectQualifier}Permission P ON FP.PermissionID = P.PermissionID 
	ON R.RoleID = FP.RoleID
WHERE (	UR.UserID = @UserID
			OR (FP.RoleID = - 1 AND @UserID Is Not NULL)
			OR (FP.RoleID = - 3)
			)
		AND CHARINDEX(P.PermissionKey, @PermissionKeys) > 0
		AND FP.AllowAccess = @AllowAccess
	AND F.PortalID = @PortalID
	AND (F.StorageLocation = 0 
		OR (F.StorageLocation = 1 AND @IncludeSecure = 1) 
		OR (F.StorageLocation = 2 AND @IncludeDatabase = 1)
	)
GO

/* Add GetFolderByFolderID */
/***************************/

IF EXISTS ( SELECT * FROM sysobjects WHERE id = object_id(N'{databaseOwner}{objectQualifier}GetFolderByFolderID') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP procedure {databaseOwner}{objectQualifier}GetFolderByFolderID
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetFolderByFolderID
	@PortalID int,
	@FolderID int
AS
SELECT *
	FROM {databaseOwner}{objectQualifier}Folders
	WHERE ((PortalID = @PortalID) or (PortalID is null and @PortalID is null))
		AND (FolderID = @FolderID)
GO

/* Add GetFolderByFolderPath */
/*****************************/

IF EXISTS ( SELECT * FROM sysobjects WHERE id = object_id(N'{databaseOwner}{objectQualifier}GetFolderByFolderPath') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP procedure {databaseOwner}{objectQualifier}GetFolderByFolderPath
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetFolderByFolderPath
	@PortalID int,
	@FolderPath nvarchar(300)
AS
SELECT *
	FROM {databaseOwner}{objectQualifier}Folders
	WHERE ((PortalID = @PortalID) or (PortalID is null and @PortalID is null))
		AND (FolderPath = @FolderPath)
	ORDER BY FolderPath
GO

/* Update GetSearchModules */
/***************************/

IF EXISTS ( SELECT * FROM sysobjects WHERE id = object_id(N'{databaseOwner}{objectQualifier}GetSearchModules') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP procedure {databaseOwner}{objectQualifier}GetSearchModules
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetSearchModules

@PortalID 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  M.IsDeleted = 0  
	AND T.IsDeleted = 0  
	AND ControlKey is null 
	AND DM.IsAdmin = 0
	AND (DM.SupportedFeatures & 2 = 2)
	AND (T.EndDate > GETDATE() or T.EndDate IS NULL) 
	AND (T.StartDate <= GETDATE() or T.StartDate IS NULL) 
	AND (M.StartDate <= GETDATE() or M.StartDate IS NULL) 
	AND (M.EndDate > GETDATE() or M.EndDate IS NULL) 
	AND (NOT (DM.BusinessControllerClass IS NULL))
	AND (T.PortalID = @PortalID OR (T.PortalID IS NULL AND @PortalID Is NULL))
ORDER BY TM.ModuleOrder

GO

/* Update UpdateSearchItem */
/***************************/

IF EXISTS ( SELECT * FROM sysobjects WHERE id = object_id(N'{databaseOwner}{objectQualifier}UpdateSearchItem') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP procedure {databaseOwner}{objectQualifier}UpdateSearchItem
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}UpdateSearchItem
	@SearchItemID int, 
	@Title nvarchar(200), 
	@Description nvarchar(2000), 
	@Author int, 
	@PubDate datetime, 
	@ModuleId int, 
	@SearchKey nvarchar(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




/************************************************************/
/*****              SqlDataProvider                     *****/
/************************************************************/