Select the search type
  • Site
  • Web
Search
You are here:  Support/Forums
Support

Bring2mind Forums

SQL Queries
Last Post 02/02/2010 10:05 PM by Peter Donker. 1 Replies.
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
mtMike
New Member
New Member
Posts:14


--
01/29/2010 6:39 PM

We constantly have a demand for knowing who is registered to a specific role and know who downloaded what documnet and when.

Below are a couple SQL queries that will help accomplish those tasks.

Users attached to a role:

Output is in the form of:

UserID:RoleName:Username:Email:FirstName:LastName:DisplayName:CompanyName:PhoneNumber

----------Beginning----------

USE [DotNetNuke]
GO

DECLARE @rid AS VARCHAR(255)
SET @rid = 'Role name goes here%'

SELECT u.[UserID],r.[RoleName],u.[Username],u.[Email],u.[FirstName],u.[LastName],u.[DisplayName],
    up.[PropertyValue] 'CompanyName',up1.[PropertyValue] 'PhoneNumber'
FROM [UserRoles] ur INNER JOIN [Roles] r ON ur.[RoleID] = r.[RoleID]
    INNER JOIN [Users] u ON ur.[UserID]=u.[UserID]
    INNER JOIN ([UserProfile] up INNER JOIN [ProfilePropertyDefinition] ppd ON up.[PropertyDefinitionID]=ppd.[PropertyDefinitionID])
        ON up.[UserID]=u.[UserID]
    inner JOIN ([UserProfile] up1 INNER JOIN [ProfilePropertyDefinition] ppd2 ON up1.[PropertyDefinitionID]=ppd2.[PropertyDefinitionID])
        ON up1.[UserID]=u.[UserID]   
WHERE r.[RoleName] LIKE @rid AND ppd.[PropertyName]='CompanyName' AND ppd2.[PropertyName]='Telephone'

----------End----------

 

Downloads by folder (or Role):

Output is in the form of:

path:EntryId:LastModified:OriginalFileName:DownloadDate:UserId:Username:Email:FirstName:LastName:DisplayName:Company

-----beginning------

USE [DotNetNuke]
DECLARE @projectfolder varchar(256)
SET @projectfolder = '/Name of role or folder goes here%'

SELECT dbo.DMXDAV_GetPath(en.CollectionId,en.PortalId,NULL) AS 'path',en.EntryId,en.LastModified,en.OriginalFileName
    ,MIN(lg.Datime) AS 'DownloadDate',
    lg.UserId,u.[Username],u.[Email],u.[FirstName],u.[LastName],u.[DisplayName],up.PropertyValue AS 'Company'
FROM dbo.DMX_Entries en
    INNER JOIN dbo.DMX_Log lg ON en.EntryId = lg.EntId
    INNER JOIN dbo.Users u ON lg.UserId=u.UserID   
    LEFT JOIN (dbo.UserProfile up
        INNER JOIN dbo.ProfilePropertyDefinition pd ON up.PropertyDefinitionID = pd.PropertyDefinitionID)
        ON u.UserID = up.UserID
WHERE en.CollectionId<>en.EntryId AND (dbo.DMXDAV_GetPath(en.CollectionId,en.PortalId,NULL)) like @projectfolder
    AND lg.Action='Download' AND pd.PropertyName='CompanyName'
GROUP BY dbo.DMXDAV_GetPath(en.CollectionId,en.PortalId,NULL),en.EntryId,en.LastModified,en.OriginalFileName,
    lg.UserId,u.[Username],u.[Email],u.[FirstName],u.[LastName],u.[DisplayName],DATEPART(dy,lg.Datime),up.PropertyValue

UNION SELECT dbo.DMXDAV_GetPath(en.CollectionId,en.PortalId,NULL) AS 'path',en.EntryId,en.LastModified,en.OriginalFileName
    ,MIN(lg.Datime) AS 'DownloadDate',
    lg.UserId,u.[Username],u.[Email],u.[FirstName],u.[LastName],u.[DisplayName],'N/A' AS 'Company'
FROM dbo.DMX_Entries en
    INNER JOIN dbo.DMX_Log lg ON en.EntryId = lg.EntId
    INNER JOIN dbo.Users u ON lg.UserId=u.UserID           
WHERE en.CollectionId<>en.EntryId AND (dbo.DMXDAV_GetPath(en.CollectionId,en.PortalId,NULL)) like @projectfolder
    AND lg.Action='Download' AND NOT EXISTS
    (SELECT TOP 1 * FROM dbo.UserProfile INNER JOIN dbo.ProfilePropertyDefinition ON dbo.UserProfile.PropertyDefinitionID = dbo.ProfilePropertyDefinition.PropertyDefinitionID
        WHERE dbo.UserProfile.UserID=u.UserID AND PropertyName='CompanyName')
GROUP BY dbo.DMXDAV_GetPath(en.CollectionId,en.PortalId,NULL),en.EntryId,en.LastModified,en.OriginalFileName,
    lg.UserId,u.[Username],u.[Email],u.[FirstName],u.[LastName],u.[DisplayName],DATEPART(dy,lg.Datime)
ORDER BY u.LastName

-----end-------

**for this one we found a slight flaw in DMXDAV_GetPath that we had to correct. We would get into an infinate loop if the entryID=collectionID which occured on some oprhaned entries. Fix is below.

<!--[if gte mso 9]> Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4 <!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:1; mso-generic-font-family:roman; mso-font-format:other; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-520092929 1073786111 9 0 415 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; font-size:10.0pt; mso-ansi-font-size:10.0pt; mso-bidi-font-size:10.0pt;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> <!--[if gte mso 10]> --SBL  2010-01-27 added exit if entryID=collectionID

 IF EXISTS (SELECT EntryId FROM dbo.DMX_Entries

      WHERE EntryId=@EntryId AND PortalId=@PortalId AND CollectionId<>EntryId)

 

 

 

 

 

Peter Donker
Veteran Member
Veteran Member
Posts:4536


--
02/02/2010 10:05 PM
Thanks for the info Mike. I'll check out the getpath code. It's true I never contemplate entry id being equal to collection id as it would constitute an orphaned entry as you rightly point out.

Peter
You are not authorized to post a reply.