--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