s
Contact Login Register
h M

Dynamic User Directory returning Region and Country Text Values as Opposed to EntryID

Dynamic User Directory simple altering of SQL proc to work with DNN 7.4 and higher that stores Region and Country profiles as EntryID numbers

Author: David To/Friday, June 10, 2016/Categories: SQL Performance / Code Security

Rate this article:
No rating
--exec {databaseOwner}[{objectQualifier}DataSprings_RetrieveDNNUserProfileData3] 0, '', '', 1, '', ''

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


CREATE Procedure {databaseOwner}[{objectQualifier}DataSprings_RetrieveDNNUserProfileData3]
(@PortalID int,@RoleFilter nvarchar(1000),@LimitResults nvarchar(100),@Authonly int,@Filter nvarchar(4000), @ORDERBY nvarchar(1000))
AS
BEGIN
if exists(select top 1 * from sysobjects where xtype = 'U' and name = '#tempDUDProfile') and (@PortalID = 999)
begin
drop table #tempDUDProfile
end
if exists(select top 1 * from sysobjects where xtype = 'U' and name = 'tempDUDPivot') and (@PortalID = 999)
begin
drop table dbo.tempDUDPivot
end
-- generate tempDUDProfile table
-- create table tempDUDProfile if not exists
If not exists(select top 1 * from sysobjects where xtype = 'U' and name = '#tempDUDProfile') and (@PortalID = 999)
BEGIN
 
CREATE TABLE #tempDUDProfile(
 
[userid] [int] NOT NULL,
[username] [nvarchar](100) NOT NULL,
[firstname] [nvarchar](50) NOT NULL,
[lastname] [nvarchar](50) NOT NULL,
[email] [nvarchar](256) NULL,
[Displayname] [nvarchar](128) NOT NULL,
[propertyvalue] [nvarchar](3750) NULL,
[propertyname] [nvarchar](50) NOT NULL,
[PortalID] [int] NULL
) ON [PRIMARY]


--ALTER TABLE #tempDUDProfile ADD CONSTRAINT [PK_tempDUDProfile] PRIMARY KEY CLUSTERED  ([profileid])

INSERT INTO #tempDUDProfile(userid, username, firstname, lastname, email, Displayname,  propertyvalue, propertyname, portalID)

select U.userid,U.username,U.firstname,U.lastname,U.email,U.Displayname,
case UP.Visibility --when 0 then UP.propertyvalue ELSE 'Not Visible'
when 0 then UP.propertyvalue
when 1 then UP.propertyvalue
---- Uncomment this line if you want the system to use privacy settings. when 2 then 'Not Visible' ELSE 'Not Visible'
ELSE UP.propertyvalue
END AS PropertyValue,P.propertyname, P.PortalID 
from {databaseOwner}[{objectQualifier}Users] U inner join {databaseOwner}[{objectQualifier}userprofile] UP on U.userid = UP.userid
inner join {databaseOwner}[{objectQualifier}profilepropertydefinition] P on UP.propertydefinitionid = P.propertydefinitionid
--where U.userid In (Select userid from {databaseOwner}[{objectQualifier}UserPortals] where PortalID = Convert(varchar(3), @PortalID))
where P.Visible=1
AND U.IsSuperUser=0
AND P.Deleted=0
AND P.PRopertyName <> 'FirstName'
AND P.PropertyName <> 'LastName'
AND P.PRopertyNAme <> 'E-Mail'
AND P.PRopertyNAme <> 'Email'
AND P.PRopertyNAme <> 'Username'
AND P.PRopertyNAme <> 'Displayname'
AND P.PropertyName Not Like '%[.#)(&[@!^!]]%' ESCAPE '!'
-- exec dbo.[DataSprings_RetrieveDNNUserProfileData3a]
--We are now moving this to a single stored procedure. 
Declare @MyCreateTableSQL nvarchar(MAX)

if exists(select top 1 * from sysobjects where xtype = 'U' and name = 'tempDUDPivot') and (@PortalID = 999)
begin
drop table dbo.tempDUDPivot
end

Set @MyCreateTableSQL = 'Create TABLE [dbo].[tempDUDPivot](
[id] [int] IDENTITY(1,1),
[userid] [int] NOT NULL,
[username] [nvarchar](200) NOT NULL,
[firstname] [nvarchar](200) NOT NULL,
[lastname] [nvarchar](200) NOT NULL,
[email] [nvarchar](500) NULL,
[Displayname] [nvarchar](500) NOT NULL,
[PortalID] [int] NULL,'

DECLARE @columnsForCreateTable NVARCHAR(MAX)
SELECT @columnsForCreateTable = COALESCE(@columnsForCreateTable + ',[' + cast(propertyname as varchar) + '] [nvarchar](MAX) NULL',
'[' + cast(propertyname as varchar)+ '] [nvarchar](MAX) NULL')
FROM #tempDUDProfile
GROUP BY propertyname
--select @columnsForCreateTable

set @MyCreateTableSQL = @MyCreateTableSQL + @columnsForCreateTable  + ') ON [PRIMARY]'

--PRINT @MyCreateTableSQL
Execute(@MyCreateTableSQL)

ALTER TABLE [dbo].[tempDUDPivot] ADD CONSTRAINT [PK_tempDUDPivot] PRIMARY KEY CLUSTERED  ([ID])


DECLARE @columns VARCHAR(8000)
SELECT @columns = COALESCE(@columns + ',[' + cast(propertyname as varchar) + ']',
'[' + cast(propertyname as varchar)+ ']')
FROM #tempDUDProfile
GROUP BY propertyname
DECLARE @query nvarchar(MAX)
SET @query = '
INSERT INTO [dbo].[tempDUDPivot](userid, username, firstname, lastname, email, Displayname, portalID, ' + @columns + ' )

SELECT userid, username, firstname, lastname, email, Displayname, portalID, ' + @columns + '
FROM #tempDUDProfile
PIVOT
(
MAX(propertyvalue)
FOR [propertyname]
IN (' + @columns + ')
)
AS p
'
--Select @query

EXECUTE(@query)
---- End of stored procedure to create the tables

END
If (@PortalID <> 999)
BEGIN
Declare @MySQL nvarchar(max)
-- check if DNN version is 5.0 or higher
if exists (select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME like '%userportals%' and COLUMN_NAME = 'IsDeleted')
and exists (select top 1 * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME like '%users' and COLUMN_NAME = 'IsDeleted')
BEGIN
Set @MySQL = 'select ' + @LimitResults + ' A.*, {databaseOwner}{objectQualifier}DataSprings_DUD_GetUserRoles(A.UserID) AS UserRoles,{databaseOwner}{objectQualifier}DataSprings_DUD_GetAllUserData(A.userid) As SearchAllField, D.[CreatedByUserID], D.[CreatedOnDate], D.[LastModifiedByUserID]
, D.[LastModifiedOnDate] 
, LI.Text as CountryText,LI2.Text as RegionText,LI.Value as CountryValue,LI2.Value as RegionValue
from dbo.tempDUDPivot AS A' + '
inner join aspnet_users AS B on B.username = A.username
inner join aspnet_membership C on B.userid = C.userid
inner join {databaseOwner}[{objectQualifier}users] D on D.UserID = A.UserID 
left join {databaseOwner}[{objectQualifier}Lists] LI on A.Country = LI.EntryID
left join {databaseOwner}[{objectQualifier}Lists] LI2 on A.Region = LI2.EntryID
'
+ replace(replace(replace(replace(replace(@Filter,'[CreatedOnDate]','D.CreatedOnDate'), '[CreatedByUserID]', 'D.CreatedByUserID'), '[LastModifiedByUserID]', 'D.LastModifiedByUserID'), '[LastModifiedOnDate]', 'D.LastModifiedOnDate'), '[','A.[')
+ replace(@RoleFilter,'[','A.[')
+ ' AND ((C.IsApproved = 1) OR (' + Cast(@AuthOnly as varchar(2)) + ' = 0))'
+ ' AND (A.PortalID = ' + cast(@PortalID as varchar(3))
+ ' AND A.userid not in (select userid from {databaseOwner}[{objectQualifier}users] where issuperuser = 1 and isdeleted = 1)
AND A.userid not in (select userid from {databaseOwner}[{objectQualifier}userportals] where isdeleted = 1 AND portalID = '
+ + cast(@PortalID as varchar(3)) + ')'
+ ' AND (A.userid not in (select userid from {databaseOwner}[{objectQualifier}userportals] where Authorised = 0 AND portalID = '
+ + cast(@PortalID as varchar(3)) + ') OR ' + Cast(@AuthOnly as varchar(2)) + ' = 0))'
+ @ORDERBY
EXECUTE(@MySQL)
END
else
if exists (select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME like '%userportals%' and COLUMN_NAME = 'IsDeleted')
and not exists (select top 1 * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME like '%users' and COLUMN_NAME = 'IsDeleted')
BEGIN
Set @MySQL = 'select ' + @LimitResults + ' A.*, {databaseOwner}{objectQualifier}DataSprings_DUD_GetUserRoles(A.UserID) AS UserRoles, {databaseOwner}{objectQualifier}DataSprings_DUD_GetAllUserData(A.userid) As SearchAllField 
, LI.Text as CountryText,LI2.Text as RegionText,LI.Value as CountryValue,LI2.Value as RegionValue
from dbo.tempDUDPivot AS A' + '
inner join aspnet_users AS B on B.username = A.username
inner join aspnet_membership C on B.userid = C.userid 
left join {databaseOwner}[{objectQualifier}Lists] LI on A.Country = LI.EntryID
left join {databaseOwner}[{objectQualifier}Lists] LI2 on A.Region = LI2.EntryID
'
+ replace(@Filter,'[','A.[')
+ replace(@RoleFilter,'[','A.[')
+ ' AND ((C.IsApproved = 1) OR (' + Cast(@AuthOnly as varchar(2)) + ' = 0))'
+ ' AND A.PortalID = ' + cast(@PortalID as varchar(3))
+ ' AND A.userid not in (select userid from {databaseOwner}[{objectQualifier}userportals] where isdeleted = 1 AND portalID = '
+ cast(@PortalID as varchar(3)) + ')'
+ ' AND (A.userid not in (select userid from {databaseOwner}[{objectQualifier}userportals] where Authorised = 0 AND portalID = '
+ cast(@PortalID as varchar(3)) + ') OR (' + Cast(@AuthOnly as varchar(2)) + ' = 0))'
+ @ORDERBY
EXECUTE(@MySQL)
END
else
-- if DNN version is lower than 5.0
BEGIN
Set @MySQL = 'select ' + @LimitResults + ' A.*, {databaseOwner}{objectQualifier}DataSprings_DUD_GetUserRoles(A.UserID) AS UserRoles, {databaseOwner}{objectQualifier}DataSprings_DUD_GetAllUserData(A.userid) As SearchAllField 
, LI.Text as CountryText,LI2.Text as RegionText,LI.Value as CountryValue,LI2.Value as RegionValue
from dbo.tempDUDPivot AS A' + '
inner join aspnet_users AS B on B.username = A.username
inner join aspnet_membership C on B.userid = C.userid 
left join {databaseOwner}[{objectQualifier}Lists] LI on A.Country = LI.EntryID
left join {databaseOwner}[{objectQualifier}Lists] LI2 on A.Region = LI2.EntryID
'
+ replace(@Filter,'[','A.[')
+ replace(@RoleFilter,'[','A.[')
+ ' AND ((C.IsApproved = 1) OR (' + Cast(@AuthOnly as varchar(2)) + ' = 0))'
+ ' AND A.PortalID = ' + cast(@PortalID as varchar(3)) +''
+ @ORDERBY
EXECUTE(@MySQL)
END
--PRINT(@MySQL)
END
END

Number of views (1931)/Comments (-)

Tags:
blog comments powered by Disqus

Enter your email below AND grab your spot in our big giveaway!

The winner will receive the entire Data Springs Collection 7.0 - Designed to get your website up and running like a DNN superhero (spandex not included).

  
Subscribe