03.02.03.sqldataprovider

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

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

/* Add RoleGroups Table */
/************************/

IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}RoleGroups]') and OBJECTPROPERTY(id, N'IsTable') = 1)
	BEGIN
		CREATE TABLE {databaseOwner}{objectQualifier}RoleGroups
		(
			[RoleGroupID] int IDENTITY(0,1) NOT NULL,
			[PortalID] int NOT NULL,
			[RoleGroupName] nvarchar(50) NOT NULL,
			[Description] nvarchar(1000) NULL
		)

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

		ALTER TABLE {databaseOwner}{objectQualifier}RoleGroups
			ADD CONSTRAINT [IX_{objectQualifier}RoleGroupName] UNIQUE NONCLUSTERED ([PortalID] ASC, [RoleGroupName] ASC)

		ALTER TABLE {databaseOwner}{objectQualifier}RoleGroups  WITH NOCHECK 
			ADD CONSTRAINT [FK_{objectQualifier}RoleGroups_{objectQualifier}Portals] FOREIGN KEY([PortalID]) REFERENCES {databaseOwner}[{objectQualifier}Portals] ([PortalID]) ON DELETE CASCADE

		ALTER TABLE {databaseOwner}{objectQualifier}Roles 
			ADD RoleGroupID int NULL

		ALTER TABLE {databaseOwner}{objectQualifier}Roles 
			ADD CONSTRAINT FK_{objectQualifier}Roles_{objectQualifier}RoleGroups FOREIGN KEY (RoleGroupID) REFERENCES {databaseOwner}{objectQualifier}RoleGroups (RoleGroupID)
	END
GO

/* Update Role Table */
/*********************/

ALTER TABLE {databaseOwner}{objectQualifier}Roles 
	ADD RSVPCode nvarchar(50) NULL, IconFile nvarchar(100) NULL


/* Add Role Group Stored Procedures */
/************************************/

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

CREATE PROCEDURE {databaseOwner}[{objectQualifier}AddRoleGroup]

	@PortalId         int,
	@RoleGroupName    nvarchar(50),
	@Description      nvarchar(1000)

AS

INSERT INTO {databaseOwner}{objectQualifier}RoleGroups (
  PortalId,
  RoleGroupName,
  Description
)
VALUES (
  @PortalId,
  @RoleGroupName,
  @Description
)

SELECT SCOPE_IDENTITY()

GO

/* Add Delete Role Group Stored Procedures */
/*******************************************/

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

CREATE PROCEDURE {databaseOwner}[{objectQualifier}DeleteRoleGroup]

	@RoleGroupId      int
	
AS

DELETE  
FROM {databaseOwner}{objectQualifier}RoleGroups
WHERE  RoleGroupId = @RoleGroupId

GO

/* Add Get Role Group Stored Procedures */
/****************************************/

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

CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetRoleGroup]

	@PortalId		int,
	@RoleGroupId    int
	
AS

SELECT
	RoleGroupId,
	PortalId,
	RoleGroupName,
	Description
FROM {databaseOwner}{objectQualifier}RoleGroups
WHERE  (RoleGroupId = @RoleGroupId OR RoleGroupId IS NULL AND @RoleGroupId IS NULL)
	AND    PortalId = @PortalId

GO

/* Add Get Role Groups Stored Procedures */
/*****************************************/

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

CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetRoleGroups]

	@PortalId		int
	
AS

SELECT
	RoleGroupId,
	PortalId,
	RoleGroupName,
	Description
FROM {databaseOwner}{objectQualifier}RoleGroups
WHERE  PortalId = @PortalId

GO

/* Add Update Role Group Stored Procedures */
/*******************************************/

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

CREATE PROCEDURE {databaseOwner}[{objectQualifier}UpdateRoleGroup]

	@RoleGroupId      int,
	@RoleGroupName	  nvarchar(50),
	@Description      nvarchar(1000)

AS

UPDATE {databaseOwner}{objectQualifier}RoleGroups
SET    RoleGroupName = @RoleGroupName,
	   Description = @Description
WHERE  RoleGroupId = @RoleGroupId

GO


/* Update Add Role Stored Procedures */
/*************************************/

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

CREATE PROCEDURE {databaseOwner}[{objectQualifier}AddRole]

	@PortalId         int,
	@RoleGroupId      int,
	@RoleName         nvarchar(50),
	@Description      nvarchar(1000),
	@ServiceFee       money,
	@BillingPeriod    int,
	@BillingFrequency char(1),
	@TrialFee         money,
	@TrialPeriod      int,
	@TrialFrequency   char(1),
	@IsPublic         bit,
	@AutoAssignment   bit,
	@RSVPCode         nvarchar(50),
	@IconFile         nvarchar(100)

AS

INSERT INTO {databaseOwner}{objectQualifier}Roles (
  PortalId,
  RoleGroupId,
  RoleName,
  Description,
  ServiceFee,
  BillingPeriod,
  BillingFrequency,
  TrialFee,
  TrialPeriod,
  TrialFrequency,
  IsPublic,
  AutoAssignment,
  RSVPCode,
  IconFile
)
VALUES (
  @PortalId,
  @RoleGroupId,
  @RoleName,
  @Description,
  @ServiceFee,
  @BillingPeriod,
  @BillingFrequency,
  @TrialFee,
  @TrialPeriod,
  @TrialFrequency,
  @IsPublic,
  @AutoAssignment,
  @RSVPCode,
  @IconFile
)

SELECT SCOPE_IDENTITY()

GO

/* Update Get Portal Roles Stored Procedures */
/*********************************************/

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

CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetPortalRoles]

	@PortalId     int

AS

SELECT R.RoleId,
       R.PortalId,
       R.RoleGroupId,
       R.RoleName,
       R.Description,
       'ServiceFee' = case when convert(int,R.ServiceFee) <> 0 then R.ServiceFee else null end,
       'BillingPeriod' = case when convert(int,R.ServiceFee) <> 0 then R.BillingPeriod else null end,
       'BillingFrequency' = case when convert(int,R.ServiceFee) <> 0 then L1.Text else '' end,
       'TrialFee' = case when R.TrialFrequency <> 'N' then R.TrialFee else null end,
       'TrialPeriod' = case when R.TrialFrequency <> 'N' then R.TrialPeriod else null end,
       'TrialFrequency' = case when R.TrialFrequency <> 'N' then L2.Text else '' end,
       'IsPublic' = case when R.IsPublic = 1 then 'True' else 'False' end,
       'AutoAssignment' = case when R.AutoAssignment = 1 then 'True' else 'False' end,
       RSVPCode,
       IconFile
FROM {databaseOwner}{objectQualifier}Roles R
	LEFT OUTER JOIN {databaseOwner}{objectQualifier}Lists L1 on R.BillingFrequency = L1.Value
	LEFT OUTER JOIN {databaseOwner}{objectQualifier}Lists L2 on R.TrialFrequency = L2.Value
WHERE  PortalId = @PortalId
	OR     PortalId is null
ORDER BY R.RoleName

GO

/* Update Get Role Stored Procedures */
/*************************************/

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

CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetRole]

	@RoleId   int,
	@PortalId int

AS

SELECT RoleId,
       PortalId,
       RoleGroupId,
       RoleName,
       Description,
       ServiceFee,
       BillingPeriod,
       BillingFrequency,
       TrialFee,
       TrialPeriod,
       TrialFrequency,
       IsPublic,
       AutoAssignment,
       RSVPCode,
       IconFile
FROM   {objectQualifier}Roles
WHERE  RoleId = @RoleId
	AND    PortalId = @PortalId

GO

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

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

CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetRoleByName]

	@PortalId int,
	@RoleName nvarchar(50)

AS

SELECT RoleId,
       PortalId,
       RoleGroupId,
       RoleName,
       Description,
       ServiceFee,
       BillingPeriod,
       BillingFrequency,
       TrialFee,
       TrialPeriod,
       TrialFrequency,
       IsPublic,
       AutoAssignment,
       RSVPCode,
       IconFile
FROM   {objectQualifier}Roles
WHERE  PortalId = @PortalId 
	AND RoleName = @RoleName

GO

/* Add Get Roles By Group Stored Procedures */
/********************************************/

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

CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetRolesByGroup]

	@RoleGroupId     int,
	@PortalId		 int

AS

SELECT R.RoleId,
       R.PortalId,
       R.RoleGroupId,
       R.RoleName,
       R.Description,
       'ServiceFee' = case when convert(int,R.ServiceFee) <> 0 then R.ServiceFee else null end,
       'BillingPeriod' = case when convert(int,R.ServiceFee) <> 0 then R.BillingPeriod else null end,
       'BillingFrequency' = case when convert(int,R.ServiceFee) <> 0 then L1.Text else '' end,
       'TrialFee' = case when R.TrialFrequency <> 'N' then R.TrialFee else null end,
       'TrialPeriod' = case when R.TrialFrequency <> 'N' then R.TrialPeriod else null end,
       'TrialFrequency' = case when R.TrialFrequency <> 'N' then L2.Text else '' end,
       'IsPublic' = case when R.IsPublic = 1 then 'True' else 'False' end,
       'AutoAssignment' = case when R.AutoAssignment = 1 then 'True' else 'False' end,
       R.RSVPCode,
       R.IconFile
FROM {databaseOwner}{objectQualifier}Roles R
	LEFT OUTER JOIN {databaseOwner}{objectQualifier}Lists L1 on R.BillingFrequency = L1.Value
	LEFT OUTER JOIN {databaseOwner}{objectQualifier}Lists L2 on R.TrialFrequency = L2.Value
WHERE  (RoleGroupId = @RoleGroupId OR (RoleGroupId IS NULL AND @RoleGroupId IS NULL))
	AND PortalId = @PortalId
ORDER BY R.RoleName

GO

/* Update UserRole Table */
/*************************/

ALTER TABLE {databaseOwner}{objectQualifier}UserRoles 
	ADD EffectiveDate datetime NULL

GO

/* Update Get Roles By User Stored Procedures */
/**********************************************/

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

CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetRolesByUser]
    
	@UserId        int,
	@PortalId      int

AS

SELECT {objectQualifier}Roles.RoleName,
       {objectQualifier}Roles.RoleId
	FROM {objectQualifier}UserRoles
		INNER JOIN {objectQualifier}Users on {objectQualifier}UserRoles.UserId = {objectQualifier}Users.UserId
		INNER JOIN {objectQualifier}Roles on {objectQualifier}UserRoles.RoleId = {objectQualifier}Roles.RoleId
	WHERE  {objectQualifier}Users.UserId = @UserId
		AND    {objectQualifier}Roles.PortalId = @PortalId
		AND    (EffectiveDate <= getdate() or EffectiveDate is null)
		AND    (ExpiryDate >= getdate() or ExpiryDate is null)
GO

/* Update Update Role Stored Procedures */
/****************************************/

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

CREATE PROCEDURE {databaseOwner}[{objectQualifier}UpdateRole]

	@RoleId           int,
	@RoleGroupId      int,
	@Description      nvarchar(1000),
	@ServiceFee       money,
	@BillingPeriod    int,