📄 03.02.03.sqldataprovider

📁 完整的商业模板和强大的后台管理功能
💻 SQLDATAPROVIDER
📖 第 1 页 / 共 5 页
字号:
	
	--Add Address Properties
	EXECUTE @RC = {databaseOwner}[{objectQualifier}AddPropertyDefinition] @PortalId, -1, @TextDataType, '', 'Address','Unit' ,0, '', 11, 1, 50
	EXECUTE @RC = {databaseOwner}[{objectQualifier}AddPropertyDefinition] @PortalId, -1, @TextDataType, '', 'Address','Street' ,0, '', 13, 1, 50
	EXECUTE @RC = {databaseOwner}[{objectQualifier}AddPropertyDefinition] @PortalId, -1, @TextDataType, '', 'Address','City' ,0, '', 15, 1, 50
	EXECUTE @RC = {databaseOwner}[{objectQualifier}AddPropertyDefinition] @PortalId, -1, @RegionDataType, '', 'Address','Region' ,0, '', 17, 1, 0
	EXECUTE @RC = {databaseOwner}[{objectQualifier}AddPropertyDefinition] @PortalId, -1, @CountryDataType, '', 'Address','Country' ,0, '', 19, 1, 0
	EXECUTE @RC = {databaseOwner}[{objectQualifier}AddPropertyDefinition] @PortalId, -1, @TextDataType, '', 'Address','PostalCode' ,0, '', 21, 1, 50

	--Add Contact Info Properties
	EXECUTE @RC = {databaseOwner}[{objectQualifier}AddPropertyDefinition] @PortalId, -1, @TextDataType, '', 'Contact Info','Telephone' ,0, '', 23, 1, 50
	EXECUTE @RC = {databaseOwner}[{objectQualifier}AddPropertyDefinition] @PortalId, -1, @TextDataType, '', 'Contact Info','Cell' ,0, '', 25, 1, 50
	EXECUTE @RC = {databaseOwner}[{objectQualifier}AddPropertyDefinition] @PortalId, -1, @TextDataType, '', 'Contact Info','Fax' ,0, '', 27, 1, 50
	EXECUTE @RC = {databaseOwner}[{objectQualifier}AddPropertyDefinition] @PortalId, -1, @TextDataType, '', 'Contact Info','Website' ,0, '', 29, 1, 50
	EXECUTE @RC = {databaseOwner}[{objectQualifier}AddPropertyDefinition] @PortalId, -1, @TextDataType, '', 'Contact Info','IM' ,0, '', 31, 1, 50

	--Add Preferences Properties
	EXECUTE @RC = {databaseOwner}[{objectQualifier}AddPropertyDefinition] @PortalId, -1, @RichTextDataType, '', 'Preferences','Biography' ,0, '', 33, 1, 0
	EXECUTE @RC = {databaseOwner}[{objectQualifier}AddPropertyDefinition] @PortalId, -1, @TimeZoneDataType, '', 'Preferences','TimeZone' ,0, '', 35, 1, 0
	EXECUTE @RC = {databaseOwner}[{objectQualifier}AddPropertyDefinition] @PortalId, -1, @LocaleDataType, '', 'Preferences','PreferredLocale' ,0, '', 37, 1, 0


GO

Grant EXECUTE on {databaseOwner}[{objectQualifier}AddDefaultPropertyDefinitions] to public
GO

/* Update Get Permissions By Tab Stored Procedures */
/***************************************************/

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetPermissionsByTabID
	@TabID int
AS

SELECT
	P.[PermissionID],
	P.[PermissionCode],
	P.[PermissionKey],
	P.[ModuleDefID],
	P.[PermissionName]
FROM
	{databaseOwner}{objectQualifier}Permission P
WHERE
	P.PermissionCode = 'SYSTEM_TAB'

GO

/* Add Get Online User Stored Procedures */
/*****************************************/

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetOnlineUser	@UserID int

AS

SELECT
		{objectQualifier}UsersOnline.UserID,
		{objectQualifier}Users.UserName
	FROM   {objectQualifier}UsersOnline
	INNER JOIN {objectQualifier}Users ON {objectQualifier}UsersOnline.UserID = {objectQualifier}Users.UserID
	WHERE  {objectQualifier}UsersOnline.UserID = @UserID

GO

/* Add Get Online Users Stored Procedures */
/******************************************/

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetOnlineUsers	@PortalID int

AS

SELECT 
	UO.UserID, 
	U.Username
	FROM {objectQualifier}UsersOnline UO
		INNER JOIN {objectQualifier}Users U ON UO.UserID = U.UserID 
		INNER JOIN {objectQualifier}UserPortals UP ON U.UserID = UP.UserId
	WHERE  UP.PortalID = @PortalID
	
GO

/* Add UserProfile Table */
/*************************/

IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}UserProfile]') and OBJECTPROPERTY(id, N'IsTable') = 1)
	BEGIN
		CREATE TABLE {databaseOwner}{objectQualifier}UserProfile
		(
			[ProfileID] int IDENTITY(1,1) NOT NULL,
			[UserID] int NOT NULL,
			[PropertyDefinitionID] int NOT NULL,
			[PropertyValue] nvarchar(3750) NULL,
			[PropertyText] ntext NULL,
			[Visibility] int NOT NULL DEFAULT 0,
			[LastUpdatedDate] datetime NOT NULL
		)

		ALTER TABLE {databaseOwner}{objectQualifier}UserProfile
			ADD CONSTRAINT [PK_{objectQualifier}UserProfile] PRIMARY KEY NONCLUSTERED ([ProfileID])

		ALTER TABLE {databaseOwner}{objectQualifier}UserProfile  WITH NOCHECK 
			ADD CONSTRAINT [FK_{objectQualifier}UserProfile_{objectQualifier}Users] FOREIGN KEY([UserID]) REFERENCES {databaseOwner}[{objectQualifier}Users] ([UserID]) ON DELETE CASCADE

		ALTER TABLE {databaseOwner}{objectQualifier}UserProfile  WITH NOCHECK 
			ADD CONSTRAINT [FK_{objectQualifier}UserProfile_{objectQualifier}ProfilePropertyDefinition] FOREIGN KEY([PropertyDefinitionID]) REFERENCES {databaseOwner}[{objectQualifier}ProfilePropertyDefinition] ([PropertyDefinitionID]) ON DELETE CASCADE

	END
GO

/* Add GetProfilePropertyDefinitionID Function */
/***********************************************/

CREATE FUNCTION {databaseOwner}[{objectQualifier}GetProfilePropertyDefinitionID]
(
	@PortalID				int,
	@PropertyName			nvarchar(50)
)
RETURNS int

AS
BEGIN
	DECLARE @DefinitionID int
	SELECT @DefinitionID = -1

	IF  @PropertyName IS NULL
		OR LEN(@PropertyName) = 0
		RETURN -1

	IF @PortalID IS NULL
		SET @POrtalID = -1

	SET @DefinitionID = (SELECT PropertyDefinitionID 
							FROM {objectQualifier}ProfilePropertyDefinition
							WHERE PortalID = @PortalID
								AND PropertyName = @PropertyName
						)
	
	RETURN @DefinitionID
END

GO

Grant EXECUTE on {databaseOwner}[{objectQualifier}GetProfilePropertyDefinitionID] to public
GO

/* Add GetProfileElement Function */
/**********************************/

CREATE FUNCTION {databaseOwner}[{objectQualifier}GetProfileElement]
(
	@fieldName AS NVARCHAR(100),
	@fields AS NVARCHAR(4000),
	@values AS NVARCHAR(4000)
)

RETURNS NVARCHAR(4000)

AS

BEGIN

	-- If input is invalid, return null.
	IF  @fieldName IS NULL
		OR LEN(@fieldName) = 0
		OR @fields IS NULL
		OR LEN(@fields) = 0
		OR @values IS NULL
		OR LEN(@values) = 0
		RETURN NULL

	-- locate FieldName in Fields
	DECLARE @fieldNameToken AS NVARCHAR(20)
	DECLARE @fieldNameStart AS INTEGER, @valueStart AS INTEGER, @valueLength AS INTEGER

	-- Only handle string type fields (:S:)
	SET @fieldNameStart = CHARINDEX(@fieldName + ':S',@Fields,0)

	-- If field is not found, return null
	IF @fieldNameStart = 0 RETURN NULL
	SET @fieldNameStart = @fieldNameStart + LEN(@fieldName) + 3

	-- Get the field token which I've defined as the start of the field offset to the end of the length
	SET @fieldNameToken =
	SUBSTRING(@Fields,@fieldNameStart,LEN(@Fields)-@fieldNameStart)

	-- Get the values for the offset and length
	SET @valueStart = {databaseOwner}{objectQualifier}getelement(1,@fieldNameToken,':')
	SET @valueLength = {databaseOwner}{objectQualifier}getelement(2,@fieldNameToken,':')

	-- Check for sane values, 0 length means the profile item was stored, just no data
	IF @valueLength = 0 RETURN ''

	-- Return the string
	RETURN SUBSTRING(@values, @valueStart+1, @valueLength)
END

GO

Grant EXECUTE on {databaseOwner}[{objectQualifier}GetProfileElement] to public
GO

/* Add GetElement Function */
/***************************/

CREATE FUNCTION {databaseOwner}[{objectQualifier}GetElement]
(
	@ord AS INT,
	@str AS VARCHAR(8000),
	@delim AS VARCHAR(1) 
)
RETURNS INT

AS

BEGIN
	-- If input is invalid, return null.
	IF  @str IS NULL
		OR LEN(@str) = 0
		OR @ord IS NULL
		OR @ord < 1
		-- @ord > [is the] expression that calculates the number of elements.
		OR @ord > LEN(@str) - LEN(REPLACE(@str, @delim, '')) + 1
		RETURN NULL
 
	DECLARE @pos AS INT, @curord AS INT
	SELECT @pos = 1, @curord = 1
	-- Find next element's start position and increment index.
	WHILE @curord < @ord
		SELECT
			@pos = CHARINDEX(@delim, @str, @pos) + 1,
			@curord = @curord + 1
	RETURN    CAST(SUBSTRING(@str, @pos, CHARINDEX(@delim, @str + @delim, @pos) - @pos) AS INT)
END

GO

Grant EXECUTE on {databaseOwner}[{objectQualifier}GetElement] to public
GO

/* Add GetUserProfile Procedure */
/********************************/

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

CREATE procedure {databaseOwner}{objectQualifier}GetUserProfile

	@UserId int

AS
SELECT
	ProfileID,
	UserID,
	PropertyDefinitionID,
	'PropertyValue' = case when (PropertyValue Is Null) then PropertyText else PropertyValue end,
	Visibility,
	LastUpdatedDate
	FROM	{objectQualifier}UserProfile
	WHERE   UserId = @UserId
GO

/* Add UpdateUserProfileProperty Procedure */
/*******************************************/

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

CREATE PROCEDURE {databaseOwner}[{objectQualifier}UpdateUserProfileProperty]

	@ProfileID				int,
	@UserID					int,
	@PropertyDefinitionID	int,
	@PropertyValue			ntext,
	@Visibility				int,
	@LastUpdatedDate		datetime

AS
IF @ProfileID IS NULL OR @ProfileID = -1
	-- Try the UserID/PropertyDefinitionID to see if the Profile property exists
	SELECT @ProfileID = ProfileID
		FROM   {objectQualifier}UserProfile
		WHERE  UserID = @UserID AND PropertyDefinitionID = @PropertyDefinitionID
 
IF @ProfileID IS NOT NULL
	-- Update Property
	BEGIN
		UPDATE {objectQualifier}UserProfile
			SET PropertyValue = case when (DATALENGTH(@PropertyValue) > 7500) then NULL else @PropertyValue end,
				PropertyText = case when (DATALENGTH(@PropertyValue) > 7500) then @PropertyValue else NULL end,
				Visibility = @Visibility,
				LastUpdatedDate = @LastUpdatedDate
			WHERE  ProfileID = @ProfileID
		SELECT @ProfileID
	END
ELSE
	-- Insert New Property
	BEGIN
		INSERT INTO {objectQualifier}UserProfile (
			UserID,
			PropertyDefinitionID,
			PropertyValue,
			PropertyText,
			Visibility,
			LastUpdatedDate
		  )
		VALUES (
			@UserID,
			@PropertyDefinitionID,
			case when (DATALENGTH(@PropertyValue) > 7500) then NULL else @PropertyValue end,
			case when (DATALENGTH(@PropertyValue) > 7500) then @PropertyValue else NULL end,
			@Visibility,
			@LastUpdatedDate
		  )

	SELECT SCOPE_IDENTITY()
END
GO

/* Update AddModule Procedure */
/******************************/

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}AddModule
    
@PortalId                      int,
@ModuleDefId                   int,
@ModuleTitle                   nvarchar(256),
@AllTabs                       bit,
@Header                        ntext,
@Footer                        ntext,
@StartDate                     datetime,
@EndDate                       datetime,
@InheritViewPermissions        bit,
@IsDeleted                     bit

AS

INSERT INTO {objectQualifier}Modules ( 
  PortalId,
  ModuleDefId,
  ModuleTitle,
  AllTabs,
  Header,
  Footer, 
  StartDate,
  EndDate,
  InheritViewPermissions,
  IsDeleted
)
values (
  @PortalId,
  @ModuleDefId,
  @ModuleTitle,
  @AllTabs,
  @Header,
  @Footer, 
  @StartDate,
  @EndDate,
  @InheritViewPermissions,
  @IsDeleted
)

select SCOPE_IDENTITY()

GO

/* Update UpdateModule Procedure */
/*********************************/

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

CREATE PROCEDURE {databaseOwner}{objectQualifier}UpdateModule

@ModuleId               int,
@ModuleTitle            nvarchar(256),
@AllTabs                bit, 
@Header                 ntext,
@Footer                 ntext,
@StartDate              datetime,
@EndDate                datetime,
@InheritViewPermissions	bit,
@IsDeleted              bit

AS

UPDATE {objectQualifier}Modules
SET    ModuleTitle = @ModuleTitle,
       AllTabs = @AllTabs,
       Header = @Header,
       Footer = @Footer, 
       StartDate = @StartDate,
       EndDate = @EndDate,
       InheritViewPermissions = @InheritViewPermissions,
       IsDeleted = @IsDeleted
WHERE  ModuleId = @ModuleId

GO


/* Secure File Storage */
/***********************/

ALTER TABLE {databaseOwner}{objectQualifier}Files ADD
	FolderID int NOT NULL CONSTRAINT DF_{objectQualifier}Files_FolderID DEFAULT 0,
⌨️ 快捷键说明

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