03.02.03.sqldataprovider

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

	@BillingFrequency char(1),
	@TrialFee         money,
	@TrialPeriod      int,
	@TrialFrequency   char(1),
	@IsPublic         bit,
	@AutoAssignment   bit,
	@RSVPCode         nvarchar(50),
	@IconFile         nvarchar(100)

AS

UPDATE {databaseOwner}{objectQualifier}Roles
SET    RoleGroupId = @RoleGroupId,
       Description = @Description,
       ServiceFee = @ServiceFee,
       BillingPeriod = @BillingPeriod,
       BillingFrequency = @BillingFrequency,
       TrialFee = @TrialFee,
       TrialPeriod = @TrialPeriod,
       TrialFrequency = @TrialFrequency,
       IsPublic = @IsPublic,
       AutoAssignment = @AutoAssignment,
       RSVPCode = @RSVPCode,
       IconFile = @IconFile
WHERE  RoleId = @RoleId

GO

/* Update Add User Role Stored Procedure */
/*****************************************/

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

CREATE PROCEDURE {databaseOwner}[{objectQualifier}AddUserRole]

	@PortalId		int,
	@UserId			int,
	@RoleId			int,
	@EffectiveDate	datetime = null,
	@ExpiryDate		datetime = null

AS
DECLARE @UserRoleId int

SELECT @UserRoleId = null

SELECT @UserRoleId = UserRoleId
	FROM   {objectQualifier}UserRoles
	WHERE  UserId = @UserId AND RoleId = @RoleId
 
IF @UserRoleId IS NOT NULL
	BEGIN
		UPDATE {objectQualifier}UserRoles
			SET ExpiryDate = @ExpiryDate,
				EffectiveDate = @EffectiveDate	
			WHERE  UserRoleId = @UserRoleId
		SELECT @UserRoleId
	END
ELSE
	BEGIN
		INSERT INTO {objectQualifier}UserRoles (
			UserId,
			RoleId,
			EffectiveDate,
			ExpiryDate
		  )
		VALUES (
			@UserId,
			@RoleId,
			@EffectiveDate,
			@ExpiryDate
		  )

	SELECT SCOPE_IDENTITY()
END

GO

/* Update Get UserRole Stored Procedures */
/*****************************************/

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

CREATE procedure {databaseOwner}[{objectQualifier}GetUserRole]

	@PortalId int, 
	@UserId int, 
	@RoleId int

AS
SELECT	r.*, 
        ur.UserRoleID, 
        ur.UserID, 
        ur.EffectiveDate, 
        ur.ExpiryDate, 
        ur.IsTrialUsed
	FROM	{objectQualifier}UserRoles ur
		INNER JOIN {objectQualifier}UserPortals up on ur.UserId = up.UserId
		INNER JOIN {objectQualifier}Roles r on r.RoleID = ur.RoleID
	WHERE   up.UserId = @UserId
		AND     up.PortalId = @PortalId
		AND     ur.RoleId = @RoleId
GO

/* Update Get UserRoles By Name Stored Procedures */
/**************************************************/

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}GetUserRolesByUsername]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	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.EffectiveDate,
        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

/* Update Update UserRole Stored Procedures */
/********************************************/

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

CREATE PROCEDURE {databaseOwner}[{objectQualifier}UpdateUserRole] 
    @UserRoleId int, 
	@EffectiveDate	datetime = null,
	@ExpiryDate		datetime = null
AS

UPDATE {objectQualifier}UserRoles 
	SET ExpiryDate = @ExpiryDate,
		EffectiveDate = @EffectiveDate	
	WHERE  UserRoleId = @UserRoleId
GO

/* Update Find Banners Stored Procedures */
/*****************************************/
	
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}Vendors.Authorized = 1 
AND     (getdate() <= {objectQualifier}Banners.EndDate or {objectQualifier}Banners.EndDate is null)
ORDER BY BannerId

GO

/* Add Fields to Users Table */
/*****************************/

ALTER TABLE {databaseOwner}{objectQualifier}Users 
	ADD 
		DisplayName nvarchar(128) NOT NULL CONSTRAINT DF_{objectQualifier}Users_DisplayName DEFAULT '',
		UpdatePassword bit NOT NULL CONSTRAINT DF_{objectQualifier}Users_UpdatePassword DEFAULT 0
GO

/* Update the User Portals Table */
/*********************************/

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}UserPortals]') and OBJECTPROPERTY(id, N'IsTable') = 1)
	BEGIN
		ALTER TABLE {objectQualifier}UserPortals 
			ADD Authorised bit NOT NULL CONSTRAINT DF_{objectQualifier}UserPortals_Authorised DEFAULT 1
	END
GO

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

CREATE PROCEDURE {databaseOwner}[{objectQualifier}AddUser]

	@PortalID		int,
	@Username		nvarchar(100),
	@FirstName		nvarchar(50),
	@LastName		nvarchar(50),
	@AffiliateId    int,
	@IsSuperUser    bit,
	@Email          nvarchar(256),
	@DisplayName    nvarchar(100),
	@UpdatePassword	bit,
	@Authorised		bit

AS

DECLARE @UserID int

SELECT @UserID = UserID
	FROM   {objectQualifier}Users
	WHERE  Username = @Username

IF @UserID is null
	BEGIN
		INSERT INTO {objectQualifier}Users (
			Username,
			FirstName, 
			LastName, 
			AffiliateId,
			IsSuperUser,
			Email,
			DisplayName,
			UpdatePassword
		  )
		VALUES (
			@Username,
			@FirstName, 
			@LastName, 
			@AffiliateId,
			@IsSuperUser,
			@Email,
			@DisplayName,
			@UpdatePassword
		)

		SELECT @UserID = SCOPE_IDENTITY()
	END

IF @IsSuperUser = 0
	BEGIN
		IF not exists ( SELECT 1 FROM {objectQualifier}UserPortals WHERE UserID = @UserID AND PortalID = @PortalID )
			BEGIN
				INSERT INTO {objectQualifier}UserPortals (
					UserID,
					PortalID,
					Authorised
				)
				VALUES (
					@UserID,
					@PortalID,
					@Authorised
				)
			END
	END

SELECT @UserID

GO

/* Add Users View */
/******************/

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

CREATE VIEW {databaseOwner}[{objectQualifier}vw_Users]
AS
SELECT 
	U.UserId,
    UP.PortalId,
    U.Username,
    U.FirstName,
    U.LastName,
    U.DisplayName,
	U.IsSuperUser,
	U.Email,
    U.AffiliateId,
    U.UpdatePassword,
    UP.Authorised
FROM {objectQualifier}Users U
	LEFT OUTER JOIN {objectQualifier}UserPortals UP On U.UserId = UP.UserId
GO

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

CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetUser]

	@PortalId int,
	@UserId int

AS
SELECT * FROM {objectQualifier}vw_Users U
WHERE  UserId = @UserId
	AND    (PortalId = @PortalId or IsSuperUser = 1)
GO

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

CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetAllUsers]

    @PortalId		int,
    @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 ( PortalId = @PortalId or @PortalId is null )
		ORDER BY FirstName + ' ' + LastName

    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 FirstName + ' ' + LastName

    SELECT  TotalRecords = COUNT(*)
    FROM    #PageIndexForUsers

END

GO

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

CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetUsersByEmail]
    @PortalId		int,
    @EmailToMatch   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
    IF( @EmailToMatch IS NULL )
        INSERT INTO #PageIndexForUsers (UserId)
            SELECT UserId FROM	{objectQualifier}vw_Users 
            WHERE  Email IS NULL
				AND ( PortalId = @PortalId OR (PortalId Is Null AND @PortalId is null ))
            ORDER BY Email
    ELSE