03.00.01.sqldataprovider

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

	E.[ListName] + '.' + E.[Value] As [Key],	
	E.[ListName] + '.' + E.[Text] As [DisplayName],
	IsNull((SELECT [ListName] + '.' + [Value] FROM {objectQualifier}Lists WHERE [EntryID] = E.[ParentID]), '') As [ParentKey],
	IsNull((SELECT [ListName] + '.' + [Text] FROM {objectQualifier}Lists WHERE [EntryID] = E.[ParentID]), '') As [Parent],
	IsNull((SELECT [ListName] FROM {objectQualifier}Lists WHERE [EntryID] = E.[ParentID]),'') As [ParentList],		
	(SELECT COUNT(DISTINCT [ParentID]) FROM {objectQualifier}Lists (nolock) WHERE [ParentID] = E.[EntryID]) As HasChildren
	From {objectQualifier}Lists E (nolock)
	Where (E.[ListName] = @ListName or @ListName='')
	and (E.[DefinitionID]=@DefinitionID or @DefinitionID = -1)
	and (E.[EntryID]=@EntryID or @EntryID = -1)
	and (E.[Value]=@Value or @Value = '')
	Order By E.[Level], E.[ListName], E.[SortOrder], E.[Text]
End
Else
Begin

	DECLARE @ParentListName nvarchar(50)
	DECLARE @ParentValue nvarchar(100)
	SET @ParentListName = LEFT(@ParentKey, CHARINDEX( '.', @ParentKey) - 1)
	SET @ParentValue = RIGHT(@ParentKey, LEN(@ParentKey) -  CHARINDEX( '.', @ParentKey))
	select 
	E.[EntryID],
	E.[ListName],
	E.[Value],
	E.[Text],
	E.[Level],
	E.[SortOrder],
	E.[DefinitionID],
	E.[ParentID],
	E.[Description], 	
	E.[ListName] + '.' + E.[Value] As [Key],	
	E.[ListName] + '.' + E.[Text] As [DisplayName],
	IsNull((SELECT [ListName] + '.' + [Value] FROM {objectQualifier}Lists WHERE [EntryID] = E.[ParentID]), '') As [ParentKey],
	IsNull((SELECT [ListName] + '.' + [Text] FROM {objectQualifier}Lists WHERE [EntryID] = E.[ParentID]), '') As [Parent],
	IsNull((SELECT [ListName] FROM {objectQualifier}Lists WHERE [EntryID] = E.[ParentID]),'') As [ParentList],	
	(SELECT COUNT(DISTINCT [ParentID]) FROM {objectQualifier}Lists (nolock) WHERE [ParentID] = E.[EntryID]) As HasChildren
	From {objectQualifier}Lists E (nolock)
	where  [ListName] = @ListName 
	and (E.[DefinitionID]=@DefinitionID or @DefinitionID = -1)
	and (E.[EntryID]=@EntryID or @EntryID = -1)
	and (E.[Value]=@Value or @Value = '')
	and [ParentID] = (SELECT [EntryID] From {objectQualifier}Lists Where [ListName] = @ParentListName And [Value] = @ParentValue)
	Order By E.[Level], E.[ListName], E.[SortOrder], E.[Text]

End
GO

CREATE procedure {databaseOwner}{objectQualifier}GetList

@ListName nvarchar(50),
@ParentKey nvarchar(150),
@DefinitionID int

as
If @ParentKey = '' 
Begin
	Select DISTINCT 	
	E.[ListName],
	E.[Level],	
	E.[DefinitionID],
	E.[ParentID],	
	(SELECT MAX([SortOrder]) FROM {objectQualifier}Lists WHERE [ListName] = E.[ListName]) As [MaxSortOrder],
	(SELECT COUNT(EntryID) FROM {objectQualifier}Lists WHERE [ListName] = E.[ListName] AND ParentID = E.[ParentID]) As EntryCount,
	IsNull((SELECT [ListName] + '.' + [Value] + ':' FROM {objectQualifier}Lists WHERE [EntryID] = E.[ParentID]), '') + E.[ListName] As [Key],	
	IsNull((SELECT [ListName] + '.' + [Text] + ':' FROM {objectQualifier}Lists WHERE [EntryID] = E.[ParentID]), '') + E.[ListName] As [DisplayName],
	IsNull((SELECT [ListName] + '.' + [Value] FROM {objectQualifier}Lists WHERE [EntryID] = E.[ParentID]), '') As [ParentKey],
	IsNull((SELECT [ListName] + '.' + [Text] FROM {objectQualifier}Lists WHERE [EntryID] = E.[ParentID]), '') As [Parent],
	IsNull((SELECT [ListName] FROM {objectQualifier}Lists WHERE [EntryID] = E.[ParentID]),'') As [ParentList]
	From {objectQualifier}Lists E (nolock)
	where  ([ListName] = @ListName or @ListName='')
	and (DefinitionID = @DefinitionID or @DefinitionID = -1)
End
Else
Begin

	DECLARE @ParentListName nvarchar(50)
	DECLARE @ParentValue nvarchar(100)
	SET @ParentListName = LEFT(@ParentKey, CHARINDEX( '.', @ParentKey) - 1)
	SET @ParentValue = RIGHT(@ParentKey, LEN(@ParentKey) -  CHARINDEX( '.', @ParentKey))
	Select DISTINCT 	
	E.[ListName],
	E.[Level],	
	E.[DefinitionID],
	E.[ParentID],	
	(SELECT MAX([SortOrder]) FROM {objectQualifier}Lists WHERE [ListName] = E.[ListName]) As [MaxSortOrder],
	(SELECT COUNT(EntryID) FROM {objectQualifier}Lists WHERE [ListName] = E.[ListName] AND ParentID = E.[ParentID]) As EntryCount,
	IsNull((SELECT [ListName] + '.' + [Value] + ':' FROM {objectQualifier}Lists WHERE [EntryID] = E.[ParentID]), '') + E.[ListName] As [Key],	
	IsNull((SELECT [ListName] + '.' + [Text] + ':' FROM {objectQualifier}Lists WHERE [EntryID] = E.[ParentID]), '') + E.[ListName] As [DisplayName],
	IsNull((SELECT [ListName] + '.' + [Value] FROM {objectQualifier}Lists WHERE [EntryID] = E.[ParentID]), '') As [ParentKey],
	IsNull((SELECT [ListName] + '.' + [Text] FROM {objectQualifier}Lists WHERE [EntryID] = E.[ParentID]), '') As [Parent],
	IsNull((SELECT [ListName] FROM {objectQualifier}Lists WHERE [EntryID] = E.[ParentID]),'') As [ParentList]
	
	From {objectQualifier}Lists E (nolock)
	where  [ListName] = @ListName And
	[ParentID] = (SELECT [EntryID] From {objectQualifier}Lists Where [ListName] = @ParentListName And [Value] = @ParentValue)	

End
GO



CREATE PROCEDURE {databaseOwner}{objectQualifier}UpdateListEntry

@EntryID int, 
@ListName nvarchar(50), 
@Value nvarchar(100), 
@Text nvarchar(150), 
@Description nvarchar(500)

AS

UPDATE {objectQualifier}Lists
SET	
	[ListName] = @ListName,
	[Value] = @Value,
	[Text] = @Text,	
	[Description] = @Description
WHERE 	[EntryID] = @EntryID


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE {databaseOwner}{objectQualifier}UpdateListSortOrder
(
@EntryID	int, 
@MoveUp		bit
)
AS
DECLARE @EntryListName nvarchar(50)
DECLARE @ParentID int
DECLARE @CurrentSortValue int
DECLARE @ReplaceSortValue int
-- Get the current sort order
SELECT @CurrentSortValue = [SortOrder], @EntryListName = [ListName], @ParentID = [ParentID] FROM {objectQualifier}Lists (nolock) WHERE [EntryID] = @EntryID
-- Move the item up or down?
IF (@MoveUp = 1)
  BEGIN
    IF (@CurrentSortValue != 1) -- we rearrange sort order only if list enable sort order - sortorder >= 1
      BEGIN
        SET @ReplaceSortValue = @CurrentSortValue - 1
        UPDATE {objectQualifier}Lists SET [SortOrder] = @CurrentSortValue WHERE [SortOrder] = @ReplaceSortValue And [ListName] = @EntryListName And [ParentID] = @ParentID
        UPDATE {objectQualifier}Lists SET [SortOrder] = @ReplaceSortValue WHERE [EntryID] = @EntryID
      END
  END
ELSE
  BEGIN
    IF (@CurrentSortValue < (SELECT MAX([SortOrder]) FROM {objectQualifier}Lists))
    BEGIN
      SET @ReplaceSortValue = @CurrentSortValue + 1
      UPDATE {objectQualifier}Lists SET [SortOrder] = @CurrentSortValue WHERE SortOrder = @ReplaceSortValue And [ListName] = @EntryListName  And [ParentID] = @ParentID
      UPDATE {objectQualifier}Lists SET [SortOrder] = @ReplaceSortValue WHERE EntryID = @EntryID
    END
  END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO




INSERT INTO {databaseOwner}{objectQualifier}Lists
 ([ListName], [Value], [Text])
SELECT 'Country', Code, [Description]
FROM {databaseOwner}{objectQualifier}CodeCountry
GO


DECLARE @CanadaID int
SELECT @CanadaID = [EntryID] FROM {databaseOwner}{objectQualifier}Lists WHERE [ListName] = 'Country' AND [Value] = 'CA'
INSERT INTO {databaseOwner}{objectQualifier}Lists
([ListName], [Value], [Text], [Level], [ParentID])
SELECT 'Region', Code, [Description], 1, @CanadaID
FROM {databaseOwner}{objectQualifier}CodeRegion
WHERE Country='CA'

GO

DECLARE @UnitedStatesID int
SELECT @UnitedStatesID = [EntryID] FROM {databaseOwner}{objectQualifier}Lists WHERE [ListName] = 'Country' AND [Value] = 'US'
INSERT INTO {databaseOwner}{objectQualifier}Lists 
([ListName], [Value], [Text], [Level], [ParentID])
SELECT 'Region', Code, [Description], 1, @UnitedStatesID
FROM {databaseOwner}{objectQualifier}CodeRegion
WHERE Country='US'
GO

INSERT INTO {databaseOwner}{objectQualifier}Lists ([ListName], [Value], [Text]) 
SELECT 'Currency', Code, [Description]
FROM {databaseOwner}{objectQualifier}CodeCurrency

GO

INSERT INTO {databaseOwner}{objectQualifier}Lists ([ListName], [Value], [Text]) 
SELECT 'Frequency', Code, [Description]
FROM {databaseOwner}{objectQualifier}CodeFrequency

GO

INSERT INTO {databaseOwner}{objectQualifier}Lists ([ListName], [Value], [Text]) 
SELECT 'Processor', Processor, URL
FROM {databaseOwner}{objectQualifier}CodeProcessor

GO

INSERT INTO {databaseOwner}{objectQualifier}Lists ([ListName], [Value], [Text]) 
SELECT 'Site Log Reports', Code, Description
FROM {databaseOwner}{objectQualifier}CodeSiteLogReport

GO

DROP TABLE {databaseOwner}{objectQualifier}CodeProcessor
GO
DROP TABLE {databaseOwner}{objectQualifier}CodeCurrency
GO
DROP TABLE {databaseOwner}{objectQualifier}CodeRegion
GO
DROP TABLE {databaseOwner}{objectQualifier}CodeCountry
GO
DROP TABLE {databaseOwner}{objectQualifier}CodeSiteLogReport
GO

ALTER TABLE {databaseOwner}{objectQualifier}Roles DROP CONSTRAINT FK_{objectQualifier}Roles_{objectQualifier}CodeFrequency
GO

drop table {databaseOwner}{objectQualifier}CodeFrequency

GO

DROP PROCEDURE {databaseOwner}{objectQualifier}GetBillingFrequencyCode

GO

DROP PROCEDURE {databaseOwner}{objectQualifier}GetBillingFrequencyCodes

GO

DROP PROCEDURE {databaseOwner}{objectQualifier}GetCountryCodes

GO

DROP PROCEDURE {databaseOwner}{objectQualifier}GetCountry

GO

DROP PROCEDURE {databaseOwner}{objectQualifier}GetProcessorCodes

GO

DROP PROCEDURE {databaseOwner}{objectQualifier}GetRegion

GO

DROP PROCEDURE {databaseOwner}{objectQualifier}GetRegionCodes

GO

DROP PROCEDURE {databaseOwner}{objectQualifier}GetSiteLogReports

GO

alter procedure {databaseOwner}{objectQualifier}GetServices
    
@PortalId  int,
@UserId    int = null

as

select RoleId,
       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,
       'ExpiryDate' = ( select ExpiryDate from {databaseOwner}{objectQualifier}UserRoles where {databaseOwner}{objectQualifier}UserRoles.RoleId = R.RoleId and {databaseOwner}{objectQualifier}UserRoles.UserId = @UserId ),
       'Subscribed' = ( select UserRoleId from {databaseOwner}{objectQualifier}UserRoles where {databaseOwner}{objectQualifier}UserRoles.RoleId = R.RoleId and {databaseOwner}{objectQualifier}UserRoles.UserId = @UserId )
from {databaseOwner}{objectQualifier}Roles R
inner join {databaseOwner}{objectQualifier}Lists L1 on R.BillingFrequency = L1.Value
left outer join {databaseOwner}{objectQualifier}Lists L2 on R.TrialFrequency = L2.Value
where  R.PortalId = @PortalId
and    R.IsPublic = 1
and L1.ListName='Frequency'
and L2.ListName='Frequency'

GO

alter procedure {databaseOwner}{objectQualifier}GetPortalRoles

@PortalId     int

as

select R.RoleId,
       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
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 {databaseOwner}{objectQualifier}Lists SET [SortOrder] = 0, [DefinitionID] = -1

GO

update {databaseOwner}{objectQualifier}Portals 
set BannerAdvertising = 1
where PortalID = 0
and PortalName = 'DotNetNuke'
GO


ALTER TABLE {databaseOwner}{objectQualifier}Modules
DROP COLUMN AuthorizedEditRoles
GO
ALTER TABLE {databaseOwner}{objectQualifier}Modules
DROP COLUMN AuthorizedViewRoles
GO
ALTER TABLE {databaseOwner}{objectQualifier}Tabs
DROP COLUMN AdministratorRoles
GO
ALTER TABLE {databaseOwner}{objectQualifier}Tabs
DROP COLUMN AuthorizedRoles
GO


ALTER procedure {databaseOwner}{objectQualifier}GetTabs

@PortalId int

as

select TabID, 
       TabOrder, 
       {objectQualifier}Tabs.PortalID, 
       TabName, 
       IsVisible, 
       ParentId, 
       [Level], 
       'IconFile' = case when {objectQualifier}Files_1.FileName is null then {objectQualifier}Tabs.IconFile else {objectQualifier}Files_1.Folder + {objectQualifier}Files_1.FileName end,
       DisableLink, 
       Title, 
       Description, 
       KeyWords, 
       IsDeleted,
       SkinSrc,
       ContainerSrc,
       TabPath,
       StartDate,
       EndDate,
       'URL' = case when {objectQualifier}Files.FileName is null then {objectQualifier}Tabs.URL else {objectQualifier}Files.Folder + {objectQualifier}Files.FileName end,
       'HasChildren' = case when exists (select 1 from {objectQualifier}Tabs T2 where T2.ParentId = {objectQualifier}Tabs.TabId) then 'true' else 'false' end
from   {objectQualifier}Tabs
left outer join {objectQualifier}Files on {objectQualifier}Tabs.URL = 'fileid=' + convert(varchar,{objectQualifier}Files.FileID)
left outer join {objectQualifier}Files {objectQualifier}Files_1 ON {objectQualifier}Tabs.IconFile = 'fileid=' + convert(varchar,{objectQualifier}Files_1.FileID)
where  {objectQualifier}Tabs.PortalId = @PortalId
order by TabOrder, TabName


GO

ALTER procedure {databaseOwner}{objectQualifier}GetTabsByParentId

@ParentId int

as

select TabID, 
       TabOrder, 
       {objectQualifier}Tabs.PortalID, 
       TabName, 
       IsVisible, 
       ParentId, 
       [Level], 
       'IconFile' = case when {objectQualifier}Files_1.FileName is null then {objectQualifier}Tabs.IconFile else {objectQualifier}Files_1.Folder + {objectQualifier}Files_1.FileName end,
       DisableLink, 
       Title, 
       Description, 
       KeyWords, 
       IsDeleted,
       SkinSrc,
       ContainerSrc,
       TabPath,
       StartDate,
       EndDate,
       'URL' = case when {objectQualifier}Files.FileName is null then {objectQualifier}Tabs.URL else {objectQualifier}Files.Folder + {objectQualifier}Files.FileName end
from   {objectQualifier}Tabs
left outer join {objectQualifier}Files on {objectQualifier}Tabs.URL = 'fileid=' + convert(varchar,{objectQualifier}Files.FileID)
left outer join {objectQualifier}Files {objectQualifier}Files_1 ON {objectQualifier}Tabs.IconFile = 'fileid=' + convert(varchar,{objectQualifier}Files_1.FileID)
where  {objectQualifier}Tabs.ParentId = @ParentId


GO


drop procedure {databaseOwner}{objectQualifier}AddUserRole
GO

create procedure {databaseOwner}{objectQualifier}AddUserRole

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

as

declare @UserRoleId int

select @UserRoleId = null

select @UserRoleId = UserRoleId
from {objectQualifier}UserRoles
where  {objectQualifier}UserRoles.UserId = @UserId
and    {objectQualifier}UserRoles.RoleId = @RoleId
 
if @UserRoleId is not null
begin
  update {objectQualifier}UserRoles
  set    ExpiryDate = @ExpiryDate
  where  UserRoleId = @UserRoleId

  select @UserRoleId
end
else
begin
  insert into {objectQualifier}UserRoles (
    UserId,
    RoleId,
    ExpiryDate
  )
  values (
    @UserId,
    @RoleId,
    @ExpiryDate
  )

  select SCOPE_IDENTITY()
end



GO

DROP procedure {databaseOwner}{objectQualifier}GetSuperUsers
GO

CREATE procedure {databaseOwner}{objectQualifier}GetSuperUsers

as

select U.*,
       'PortalId' = -1,
       'FullName' = U.FirstName + ' ' + U.LastName
from   {databaseOwner}{objectQualifier}Users U
where  U.IsSuperUser = 1

GO

DROP PROCEDURE {databaseOwner}{objectQualifier}AddPortalUser
GO
DROP PROCEDURE {databaseOwner}{objectQualifier}DeletePortalUser
GO
DROP PROCEDURE {databaseOwner}{objectQualifier}GetPortalUser
GO
DROP PROCEDURE {databaseOwner}{objectQualifier}GetPortalUsers
GO
DROP PROCEDURE {databaseOwner}{objectQualifier}GetUserPortals
GO
DROP PROCEDURE {databaseOwner}{objectQualifier}UpdatePortalUser
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.ExpiryDate,
        UR.IsTrialUsed
FROM	{databaseOwner}{objectQualifier}UserRoles UR
INNER JOIN {databaseOwner}{objectQualifier}Users U ON UR.UserID = U.UserID
INNER JOIN {databaseOwner}{objectQualifier}UserPortals UP ON U.UserID = UP.UserID
INNER JOIN {databaseOwner}{objectQualifier}Roles R ON R.RoleID = UR.RoleID
WHERE  UP.PortalId = @PortalId
AND    (U.Username = @Username or @Username is NULL)
AND    (R.Rolename = @Rolename or @RoleName is NULL)

GO

/************************************************************/
/*****              SqlDataProvider                     *****/
/************************************************************/