03.02.03.sqldataprovider

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

        INSERT INTO #PageIndexForUsers (UserId)
            SELECT UserId FROM	{objectQualifier}vw_Users 
            WHERE  LOWER(Email) LIKE LOWER(@EmailToMatch)
				AND ( PortalId = @PortalId OR (PortalId Is Null AND @PortalId is null ))
            ORDER BY Email

    SELECT  *
    FROM	{objectQualifier}vw_Users u, 
			#PageIndexForUsers p
    WHERE  u.UserId = p.UserId
			AND ( PortalId = @PortalId OR (PortalId Is Null AND @PortalId is null ))
			AND p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
    ORDER BY LOWER(u.Email)

    SELECT  TotalRecords = COUNT(*)
    FROM    #PageIndexForUsers

END


GO

/* Add Get Users By Profile Property Stored Procedures */
/*******************************************************/
	
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}GetUsersByProfileProperty]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure {databaseOwner}[{objectQualifier}GetUsersByProfileProperty]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetUsersByProfileProperty]
    @PortalId		int,
    @PropertyName   nvarchar(256),
    @PropertyValue  nvarchar(256),
    @PageIndex      int,
    @PageSize       int
AS
BEGIN
    -- Set the page bounds
    DECLARE @PageLowerBound INT
    DECLARE @PageUpperBound INT
    SET @PageLowerBound = @PageSize * @PageIndex
    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound

    -- Create a temp table TO store the select results
    CREATE TABLE #PageIndexForUsers
    (
        IndexId int IDENTITY (0, 1) NOT NULL,
        UserId int
    )

    -- Insert into our temp table
    INSERT INTO #PageIndexForUsers (UserId)
        SELECT U.UserId 
		FROM   {objectQualifier}ProfilePropertyDefinition P
			INNER JOIN {objectQualifier}UserProfile UP ON P.PropertyDefinitionID = UP.PropertyDefinitionID 
			INNER JOIN {objectQualifier}vw_Users U ON UP.UserID = U.UserID
		WHERE (PropertyName = @PropertyName) AND (PropertyValue LIKE @PropertyValue OR PropertyText LIKE @PropertyValue )
			AND U.Portalid = @PortalId OR (U.PortalId Is Null AND @PortalId is null )
		ORDER BY U.DisplayName

    SELECT  *
    FROM	{objectQualifier}vw_Users u, 
			#PageIndexForUsers p
    WHERE  u.UserId = p.UserId
			AND ( PortalId = @PortalId OR (PortalId Is Null AND @PortalId is null ))
			AND p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
		ORDER BY U.DisplayName

    SELECT  TotalRecords = COUNT(*)
    FROM    #PageIndexForUsers
    
END

GO

/* Add Get User By RoleName Stored Procedures */
/**********************************************/
	
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}GetUsersByRolename]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure {databaseOwner}[{objectQualifier}GetUsersByRolename]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetUsersByRolename]

	@PortalId	int,
	@Rolename	nvarchar(50)

AS
SELECT     
		U.UserID, 
		UP.PortalId, 
		U.Username, 
		U.FirstName, 
		U.LastName, 
		U.DisplayName, 
		U.IsSuperUser, 
		U.Email, 
		U.AffiliateId, 
		U.UpdatePassword
	FROM {objectQualifier}UserPortals AS UP 
			RIGHT OUTER JOIN {objectQualifier}UserRoles  UR 
			INNER JOIN {objectQualifier}Roles R ON UR.RoleID = R.RoleID 
			RIGHT OUTER JOIN {objectQualifier}Users AS U ON UR.UserID = U.UserID 
		ON UP.UserId = U.UserID	
	WHERE ( UP.PortalId = @PortalId OR @PortalId IS null )
		AND (R.RoleName = @Rolename)
	ORDER BY U.FirstName + ' ' + U.LastName
GO

/* Add Get Users By User Name Stored Procedures */
/************************************************/
	
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}GetUsersByUserName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure {databaseOwner}[{objectQualifier}GetUsersByUserName]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetUsersByUserName]
    @PortalId			int,
    @UserNameToMatch	nvarchar(256),
    @PageIndex			int,
    @PageSize			int
AS
BEGIN
    -- Set the page bounds
    DECLARE @PageLowerBound INT
    DECLARE @PageUpperBound INT
    SET @PageLowerBound = @PageSize * @PageIndex
    SET @PageUpperBound = @PageSize - 1 + @PageLowerBound

    -- Create a temp table TO store the select results
    CREATE TABLE #PageIndexForUsers
    (
        IndexId int IDENTITY (0, 1) NOT NULL,
        UserId int
    )

    -- Insert into our temp table
    INSERT INTO #PageIndexForUsers (UserId)
        SELECT UserId FROM	{objectQualifier}vw_Users 
        WHERE  Username LIKE @UserNameToMatch
			AND ( PortalId = @PortalId OR (PortalId Is Null AND @PortalId is null ))
	    ORDER BY UserName

    SELECT  *
    FROM	{objectQualifier}vw_Users u, 
			#PageIndexForUsers p
    WHERE  u.UserId = p.UserId
			AND ( PortalId = @PortalId OR (PortalId Is Null AND @PortalId is null ))
			AND p.IndexId >= @PageLowerBound AND p.IndexId <= @PageUpperBound
    ORDER BY u.UserName

    SELECT  TotalRecords = COUNT(*)
    FROM    #PageIndexForUsers
END

GO

/* Update Get User By Name Stored Procedures */
/*********************************************/
	
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}GetUserByUsername]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure {databaseOwner}[{objectQualifier}GetUserByUsername]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetUserByUsername]

	@PortalId int,
	@Username nvarchar(100)

AS
SELECT * FROM {objectQualifier}vw_Users
WHERE  Username = @Username
	AND    (PortalId = @PortalId OR IsSuperUser = 1 OR @PortalId is null)
GO

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

CREATE PROCEDURE {databaseOwner}[{objectQualifier}UpdateUser]

	@UserId         int,
	@PortalId		int,
	@FirstName		nvarchar(50),
	@LastName		nvarchar(50),
	@Email          nvarchar(256),
	@DisplayName    nvarchar(100),
	@UpdatePassword	bit,
	@Authorised		bit

AS
UPDATE {objectQualifier}Users
SET
	FirstName = @FirstName,
    LastName = @LastName,
    Email = @Email,
	DisplayName = @DisplayName,
	UpdatePassword = @UpdatePassword
WHERE  UserId = @UserId

UPDATE {objectQualifier}UserPortals
SET
	Authorised = @Authorised
WHERE  UserId = @UserId
	AND PortalId = @PortalId

GO


/* Add Profile Property Definition Table */
/*****************************************/

IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}ProfilePropertyDefinition]') and OBJECTPROPERTY(id, N'IsTable') = 1)
	BEGIN
		CREATE TABLE {databaseOwner}{objectQualifier}ProfilePropertyDefinition
			(
				PropertyDefinitionID int IDENTITY(1,1) NOT NULL,
				PortalID int NOT NULL,
				ModuleDefID int NULL,
				Deleted bit NOT NULL,
				DataType int NOT NULL,
				DefaultValue nvarchar(50) NULL,
				PropertyCategory nvarchar(50) NOT NULL,
				PropertyName nvarchar(50) NOT NULL,
				Length int NOT NULL CONSTRAINT DF_{objectQualifier}ProfilePropertyDefinition_Length DEFAULT 0,
				Required bit NOT NULL,
				ValidationExpression nvarchar(100) NULL,
				ViewOrder int NOT NULL,
				Visible bit NOT NULL
			)

		ALTER TABLE {databaseOwner}{objectQualifier}ProfilePropertyDefinition 
			ADD CONSTRAINT PK_{objectQualifier}ProfilePropertyDefinition PRIMARY KEY CLUSTERED (PropertyDefinitionID)

		CREATE UNIQUE INDEX IX_{objectQualifier}ProfilePropertyDefinition ON {objectQualifier}ProfilePropertyDefinition(PortalID ASC, ModuleDefID ASC, PropertyName ASC) 
		CREATE INDEX IX_{objectQualifier}ProfilePropertyDefinition_PropertyName ON {objectQualifier}ProfilePropertyDefinition(PropertyName ASC) 

	END	
GO

/* Add Add Property Definition Stored Procedures */
/*************************************************/

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

CREATE PROCEDURE {databaseOwner}[{objectQualifier}AddPropertyDefinition]
	@PortalId int,
	@ModuleDefId int,
	@DataType int,
	@DefaultValue nvarchar(50),
	@PropertyCategory nvarchar(50),
	@PropertyName nvarchar(50),
	@Required bit,
	@ValidationExpression nvarchar(100),
	@ViewOrder int,
	@Visible bit,
    @Length int

as

INSERT {databaseOwner}{objectQualifier}ProfilePropertyDefinition	(
		PortalId,
		ModuleDefId,
		Deleted,
		DataType,
		DefaultValue,
		PropertyCategory,
		PropertyName,
		Required,
		ValidationExpression,
		ViewOrder,
		Visible,
        Length
	)
	VALUES	(
		@PortalId,
		@ModuleDefId,
		0,
		@DataType,
		@DefaultValue,
		@PropertyCategory,
		@PropertyName,
		@Required,
		@ValidationExpression,
		@ViewOrder,
		@Visible,
        @Length
	) 
	
SELECT SCOPE_IDENTITY()

GO

/* Add Delete Property Definition Stored Procedures */
/****************************************************/

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

CREATE PROCEDURE {databaseOwner}[{objectQualifier}DeletePropertyDefinition]

	@PropertyDefinitionId int

AS

UPDATE {databaseOwner}{objectQualifier}ProfilePropertyDefinition 
	SET Deleted = 1
	WHERE  PropertyDefinitionId = @PropertyDefinitionId
GO

/* Add Get Property Definition Stored Procedures */
/*************************************************/

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

CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetPropertyDefinition]

	@PropertyDefinitionID	int

AS
SELECT	{databaseOwner}{objectQualifier}ProfilePropertyDefinition.*
FROM	{databaseOwner}{objectQualifier}ProfilePropertyDefinition
WHERE PropertyDefinitionID = @PropertyDefinitionID
	AND Deleted = 0
GO

/* Add Get Property Definitions By Portal Stored Procedures */
/************************************************************/

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

CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetPropertyDefinitionsByPortal]

	@PortalID	int

AS

SELECT	{databaseOwner}{objectQualifier}ProfilePropertyDefinition.*
FROM	{databaseOwner}{objectQualifier}ProfilePropertyDefinition
WHERE PortalID = @PortalID
	AND Deleted = 0
ORDER BY ViewOrder

GO

/* Add Update Property Definition Stored Procedures */
/****************************************************/

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

CREATE PROCEDURE {databaseOwner}[{objectQualifier}UpdatePropertyDefinition]
	@PropertyDefinitionId int,
	@DataType int,
	@DefaultValue nvarchar(50),
	@PropertyCategory nvarchar(50),
	@PropertyName nvarchar(50),
	@Required bit,
	@ValidationExpression nvarchar(100),
	@ViewOrder int,
	@Visible bit,
    @Length int

as

UPDATE {databaseOwner}{objectQualifier}ProfilePropertyDefinition 
	SET DataType = @DataType,
		DefaultValue = @DefaultValue,
		PropertyCategory = @PropertyCategory,
		PropertyName = @PropertyName,
		Required = @Required,
		ValidationExpression = @ValidationExpression,
		ViewOrder = @ViewOrder,
		Visible = @Visible,
        Length = @Length
	WHERE PropertyDefinitionId = @PropertyDefinitionId	

GO

/* Add Add Default Property Definitions Stored Procedure */
/**********************************************************/

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

CREATE PROCEDURE {databaseOwner}[{objectQualifier}AddDefaultPropertyDefinitions]
	@PortalId int

AS
	DECLARE @TextDataType as int
	SELECT @TextDataType = (SELECT EntryID FROM {objectQualifier}Lists WHERE ListName = 'DataType' AND Value = 'Text')
	DECLARE @CountryDataType as int
	SELECT @CountryDataType = (SELECT EntryID FROM {objectQualifier}Lists WHERE ListName = 'DataType' AND Value = 'Country')
	DECLARE @RegionDataType as int
	SELECT @RegionDataType = (SELECT EntryID FROM {objectQualifier}Lists WHERE ListName = 'DataType' AND Value = 'Region')
	DECLARE @TimeZoneDataType as int
	SELECT @TimeZoneDataType = (SELECT EntryID FROM {objectQualifier}Lists WHERE ListName = 'DataType' AND Value = 'TimeZone')
	DECLARE @LocaleDataType as int
	SELECT @LocaleDataType = (SELECT EntryID FROM {objectQualifier}Lists WHERE ListName = 'DataType' AND Value = 'Locale')
	DECLARE @RichTextDataType as int
	SELECT @RichTextDataType = (SELECT EntryID FROM {objectQualifier}Lists WHERE ListName = 'DataType' AND Value = 'RichText')
	
	DECLARE @RC int

	--Add Name Properties
	EXECUTE @RC = {databaseOwner}[{objectQualifier}AddPropertyDefinition] @PortalId, -1, @TextDataType, '', 'Name','Prefix', 0, '', 1, 1, 50
	EXECUTE @RC = {databaseOwner}[{objectQualifier}AddPropertyDefinition] @PortalId, -1, @TextDataType, '', 'Name','FirstName' ,0, '', 3, 1, 50
	EXECUTE @RC = {databaseOwner}[{objectQualifier}AddPropertyDefinition] @PortalId, -1, @TextDataType, '', 'Name','MiddleName' ,0, '', 5, 1, 50
	EXECUTE @RC = {databaseOwner}[{objectQualifier}AddPropertyDefinition] @PortalId, -1, @TextDataType, '', 'Name','LastName' ,0, '', 7, 1, 50
	EXECUTE @RC = {databaseOwner}[{objectQualifier}AddPropertyDefinition] @PortalId, -1, @TextDataType, '', 'Name','Suffix' ,0, '', 9, 1, 50