02.02.00.sqldataprovider

SharpNuke源代码

	case M.RoleID
		when -1 then 'All Users'
		when -2 then 'Superuser'		
		when -3 then 'Unauthenticated Users'
		else 	R.RoleName
	end
	'RoleName',
	M.[AllowAccess],
	P.[PermissionCode],
	P.[ModuleDefID],
	P.[PermissionKey],
	P.[PermissionName]
FROM
	{databaseOwner}{objectQualifier}ModulePermission M
LEFT JOIN
	{databaseOwner}{objectQualifier}Permission P
ON	M.PermissionID = P.PermissionID
LEFT JOIN
	{databaseOwner}{objectQualifier}ModuleDefinitions MD
ON	P.ModuleDefID = MD.ModuleDefID
LEFT JOIN
	{databaseOwner}{objectQualifier}Roles R
ON	M.RoleID = R.RoleID
WHERE
	(M.[ModuleID] = @ModuleID
	OR (M.ModuleID IS NULL and P.PermissionCode = 'SYSTEM_MODULE_DEFINITION'))
AND	(P.[PermissionID] = @PermissionID or @PermissionID = -1)

GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}GetPermissionsByModuleID
	@ModuleID int
AS

SELECT
	P.[PermissionID],
	P.[PermissionCode],
	P.[ModuleDefID],
	P.[PermissionKey],
	P.[PermissionName]
FROM
	{databaseOwner}{objectQualifier}Permission P
WHERE
	P.ModuleDefID = (SELECT ModuleDefID FROM {databaseOwner}{objectQualifier}Modules WHERE ModuleID = @ModuleID)
OR 	P.PermissionCode = 'SYSTEM_MODULE_DEFINITION'

GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetPermissionByCodeAndKey
	@PermissionCode varchar(50),
	@PermissionKey varchar(20)
AS

SELECT
	P.[PermissionID],
	P.[PermissionCode],
	P.[ModuleDefID],
	P.[PermissionKey],
	P.[PermissionName]
FROM
	{databaseOwner}{objectQualifier}Permission P
WHERE
	(P.PermissionCode = @PermissionCode or @PermissionCode IS NULL)
	AND
	(P.PermissionKey = @PermissionKey or @PermissionKey IS NULL)

GO



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

DELETE FROM {databaseOwner}{objectQualifier}TabPermission
WHERE
	[TabID] = @TabID
GO



CREATE PROCEDURE {databaseOwner}{objectQualifier}DeleteTabPermission
	@TabPermissionID int
AS

DELETE FROM {databaseOwner}{objectQualifier}TabPermission
WHERE
	[TabPermissionID] = @TabPermissionID
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetTabPermission
	@TabPermissionID int
AS

SELECT
	[TabPermissionID],
	[TabID],
	[PermissionID],
	[RoleID],
	[AllowAccess]
FROM
	{databaseOwner}{objectQualifier}TabPermission
WHERE
	[TabPermissionID] = @TabPermissionID

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}AddTabPermission
	@TabID int,
	@PermissionID int,
	@RoleID int,
	@AllowAccess bit
AS

INSERT INTO {databaseOwner}{objectQualifier}TabPermission (
	[TabID],
	[PermissionID],
	[RoleID],
	[AllowAccess]
) VALUES (
	@TabID,
	@PermissionID,
	@RoleID,
	@AllowAccess
)

select SCOPE_IDENTITY()

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}UpdateTabPermission
	@TabPermissionID int, 
	@TabID int, 
	@PermissionID int, 
	@RoleID int ,
	@AllowAccess bit
AS

UPDATE {databaseOwner}{objectQualifier}TabPermission SET
	[TabID] = @TabID,
	[PermissionID] = @PermissionID,
	[RoleID] = @RoleID,
	[AllowAccess] = @AllowAccess
WHERE
	[TabPermissionID] = @TabPermissionID

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE {databaseOwner}{objectQualifier}GetTabPermissionsByTabID
	@TabID int, 
	@PermissionID int
AS

SELECT
	M.[TabPermissionID],
	M.[TabID],
	P.[PermissionID],
	M.[RoleID],
	case M.RoleID
		when -1 then 'All Users'
		when -2 then 'Superuser'		
		when -3 then 'Unauthenticated Users'
		else 	R.RoleName
	end
	'RoleName',
	M.[AllowAccess],
	P.[PermissionCode],
	P.[PermissionKey],
	P.[PermissionName]
FROM
	{databaseOwner}{objectQualifier}TabPermission M
LEFT JOIN
	{databaseOwner}{objectQualifier}Permission P
ON	M.PermissionID = P.PermissionID
LEFT JOIN
	{databaseOwner}{objectQualifier}Roles R
ON	M.RoleID = R.RoleID
WHERE
	(M.[TabID] = @TabID
	OR (M.TabID IS NULL and P.PermissionCode = 'SYSTEM_TAB'))
AND	(P.[PermissionID] = @PermissionID or @PermissionID = -1)

GO


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

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

GO

create procedure {databaseOwner}{objectQualifier}GetAllTabs

as

select *,
       'HasChildren' = case when exists (select 1 from {objectQualifier}Tabs T2 where T2.ParentId = {objectQualifier}Tabs.TabId) then 'true' else 'false' end
from   {databaseOwner}{objectQualifier}Tabs
order by TabOrder, TabName


GO

ALTER procedure  {databaseOwner}{objectQualifier}GetPortal

@PortalId  int

as

select P.*,
       'SuperUserId' = ( select UserId from Users where IsSuperUser = 1 ),
       'SuperTabId' = ( select TabId from Tabs where PortalId is null and ParentId is null ),
       'AdministratorRoleName' = ( select RoleName from Roles where RoleId = P.AdministratorRoleID ),
       'RegisteredRoleName' = ( select RoleName from Roles where RoleId = P.RegisteredRoleID )
from  {databaseOwner}{objectQualifier}Portals P
where  PortalId = @PortalId
GO


alter procedure {databaseOwner}{objectQualifier}UpdateRole

@RoleId           int,
@Description      nvarchar(1000),
@ServiceFee       money,
@BillingPeriod    int,
@BillingFrequency char(1),
@TrialFee         money,
@TrialPeriod      int,
@TrialFrequency   char(1),
@IsPublic         bit,
@AutoAssignment   bit

as

update {databaseOwner}{objectQualifier}Roles
set    Description = @Description,
       ServiceFee = @ServiceFee,
       BillingPeriod = @BillingPeriod,
       BillingFrequency = @BillingFrequency,
       TrialFee = @TrialFee,
       TrialPeriod = @TrialPeriod,
       TrialFrequency = @TrialFrequency,
       IsPublic = @IsPublic,
       AutoAssignment = @AutoAssignment
where  RoleId = @RoleId


GO

CREATE procedure {databaseOwner}{objectQualifier}GetSuperUsers

as

select U.*,
	   IsNull(UP.PortalId, -1) PortalId,
       'FullName' = U.FirstName + ' ' + U.LastName
from {databaseOwner}{objectQualifier}Users U
left join {databaseOwner}{objectQualifier}UserPortals UP
on U.UserID = UP.UserID
where  U.IsSuperUser = 1

GO



UPDATE {databaseOwner}{objectQualifier}Tabs
SET AuthorizedRoles='0;'
WHERE TabName='Log Viewer'
AND		ParentID in (SELECT TabID From {databaseOwner}{objectQualifier}Tabs WHERE TabName='Admin')

GO



CREATE PROCEDURE {databaseOwner}{objectQualifier}GetTabPermissionsByPortal
	@PortalID int
AS

SELECT
	R.PortalID,
	TP.[TabPermissionID],
	TP.[TabID],
	P.[PermissionID],
	TP.[RoleID],
	case TP.RoleID
		when -1 then 'All Users'
		when -2 then 'Superuser'
		when -3 then 'Unauthenticated Users'
		else 	R.RoleName
	end
	'RoleName',
	TP.[AllowAccess],
	P.[PermissionCode],
	P.[ModuleDefID],
	P.[PermissionKey],
	P.[PermissionName]
FROM
	{databaseOwner}{objectQualifier}TabPermission TP
INNER JOIN {databaseOwner}{objectQualifier}Tabs T
ON	TP.TabID = T.TabID
LEFT JOIN {databaseOwner}{objectQualifier}Permission P
ON	TP.PermissionID = P.PermissionID
LEFT JOIN {databaseOwner}{objectQualifier}Roles R
ON	TP.RoleID = R.RoleID
WHERE 	T.PortalID = @PortalID

GO



CREATE PROCEDURE {databaseOwner}{objectQualifier}GetModulePermissionsByPortal
	@PortalID int
AS

SELECT
	MP.[ModulePermissionID],
	MP.[ModuleID],
	P.[PermissionID],
	MP.[RoleID],
	case MP.RoleID
		when -1 then 'All Users'
		when -2 then 'Superuser'
		when -3 then 'Unauthenticated Users'
		else 	R.RoleName
	end
	'RoleName',
	MP.[AllowAccess],
	P.[PermissionCode],
	P.[ModuleDefID],
	P.[PermissionKey],
	P.[PermissionName]
FROM
	{databaseOwner}{objectQualifier}ModulePermission MP
INNER JOIN {databaseOwner}{objectQualifier}Modules M
ON	MP.ModuleID = M.ModuleID
INNER JOIN {databaseOwner}{objectQualifier}Tabs T
ON	M.TabID = T.TabID
LEFT JOIN {databaseOwner}{objectQualifier}Permission P
ON	MP.PermissionID = P.PermissionID
LEFT JOIN {databaseOwner}{objectQualifier}ModuleDefinitions MD
ON	P.ModuleDefID = MD.ModuleDefID
LEFT JOIN {databaseOwner}{objectQualifier}Roles R
ON	MP.RoleID = R.RoleID
WHERE	T.PortalID = @PortalID

GO

ALTER TABLE {databaseOwner}{objectQualifier}FAQs
	DROP CONSTRAINT FK_{objectQualifier}FAQs_{objectQualifier}Modules
GO

CREATE TABLE {databaseOwner}{objectQualifier}Tmp_FAQs
	(
	ItemID int NOT NULL IDENTITY (0, 1),
	ModuleID int NOT NULL,
	CreatedByUser nvarchar(100) NULL,
	CreatedDate datetime NULL,
	Question ntext NULL,
	Answer ntext NULL
	)  ON [PRIMARY]
	 TEXTIMAGE_ON [PRIMARY]
GO

SET IDENTITY_INSERT {databaseOwner}{objectQualifier}Tmp_FAQs ON
GO

IF EXISTS(SELECT * FROM {databaseOwner}{objectQualifier}FAQs)
	 EXEC('INSERT INTO {databaseOwner}{objectQualifier}Tmp_FAQs (ItemID, ModuleID, CreatedByUser, CreatedDate, Question, Answer)
		SELECT ItemID, ModuleID, CreatedByUser, CreatedDate, Question, Answer FROM {databaseOwner}{objectQualifier}FAQs TABLOCKX')
GO

SET IDENTITY_INSERT {databaseOwner}{objectQualifier}Tmp_FAQs OFF
GO

DROP TABLE {databaseOwner}{objectQualifier}FAQs
GO

EXECUTE sp_rename N'{databaseOwner}{objectQualifier}Tmp_FAQs', N'{objectQualifier}FAQs', 'OBJECT'
GO

ALTER TABLE {databaseOwner}{objectQualifier}FAQs ADD CONSTRAINT
	PK_{objectQualifier}FAQs PRIMARY KEY CLUSTERED 
	(
	ItemID
	) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX IX_{objectQualifier}FAQs ON {databaseOwner}{objectQualifier}FAQs
	(
	ModuleID
	) ON [PRIMARY]
GO

ALTER TABLE {databaseOwner}{objectQualifier}FAQs WITH NOCHECK ADD CONSTRAINT
	FK_{objectQualifier}FAQs_{objectQualifier}Modules FOREIGN KEY
	(
	ModuleID
	) REFERENCES {databaseOwner}{objectQualifier}Modules
	(
	ModuleID
	) ON DELETE CASCADE
	 NOT FOR REPLICATION

GO

drop procedure {databaseOwner}{objectQualifier}AddFAQ
GO

create procedure {databaseOwner}{objectQualifier}AddFAQ

@ModuleId int,
@UserName nvarchar(100),
@Question ntext,
@Answer   ntext

as

insert into {objectQualifier}FAQs (
  CreatedByUser,
  CreatedDate,
  ModuleId,
  Question,
  Answer
)
values (
  @UserName,
  getdate(),
  @ModuleId,
  @Question,
  @Answer
)

select SCOPE_IDENTITY()

GO

drop procedure {databaseOwner}{objectQualifier}UpdateFAQ
GO

create procedure {databaseOwner}{objectQualifier}UpdateFAQ

@ItemId    int,
@UserName  nvarchar(100),
@Question  ntext,
@Answer    ntext

as

update {objectQualifier}FAQs
set    Question = @Question,
       Answer = @Answer,
       CreatedByUser = @UserName,
       CreatedDate = getdate()
where  ItemId = @ItemId

GO

ALTER TABLE {databaseOwner}{objectQualifier}VendorFeedback
	DROP CONSTRAINT FK_{objectQualifier}VendorFeedback_{objectQualifier}Vendors
GO

DROP TABLE {databaseOwner}{objectQualifier}VendorFeedback
GO

ALTER TABLE {databaseOwner}{objectQualifier}VendorSearch
	DROP CONSTRAINT FK_{objectQualifier}VendorSearch_{objectQualifier}Portals
GO

DROP TABLE {databaseOwner}{objectQualifier}VendorSearch
GO

ALTER TABLE {databaseOwner}{objectQualifier}VendorLog
	DROP CONSTRAINT FK_{objectQualifier}VendorLog_{objectQualifier}Portals