Get sitemap and list of people with permissions on them

If you have questions about using mojoPortal, you can post them here.

You may want to first review our site administration documentation to see if your question is answered there.

This thread is closed to new posts. You must sign in to post in the forums.
6/7/2012 12:21:41 AM
Gravatar
Total Posts 68

Get sitemap and list of people with permissions on them

I am trying to create a new admin page to be included in the Administration panel which will allow me to view the same structure as the sitemap but with columns for who the Content Managers are,  when the page was Last Updated  and if the page is Visible to All.

Do you have any suggestion of the database query I could run to get all that info in a structured way?

I dont know which tables or views to be looking at.

Any help would be greatly appreciated.

 

7/17/2012 12:58:41 AM
Gravatar
Total Posts 68

Re: Get sitemap and list of people with permissions on them

Hi, anyone have any suggestions for this?

7/17/2012 10:43:02 AM
Gravatar
Total Posts 1204
Proud member of the mojoPortal team

Help support mojoPortal!
Add-on modules

Re: Get sitemap and list of people with permissions on them

Here's something that should get you started. Disclaimer: I don't use related sites mode, and am not very familiar with how it works, so if you're using that, it might affect the SQL you'll use, as related to the site ID joins below.

This will be a multi-step process. First, you can get the page and role information from mp_pages:

SELECT P.SiteID, P.PageID, P.IncludeInMenu, P.IncludeInSiteMap, P.IncludeInChildSiteMap, P.ParentID, P.PageName, P.PageTitle, P.Url, P.AuthorizedRoles, P.EditRoles, P.CreateChildPageRoles, P.LastModifiedUTC
FROM mp_pages P
WHERE P.SiteID = [site ID]

You'll have to use the pageID/ParentID relationship to build the site page hierarchy.

To get individuals in authorized/edit/create child page roles for page:

Break a comma separated list of role names from the page select into a list of string

For each rolename string, you can get information about the users in that role like this:


SELECT R.RoleName, R.DisplayName, U.UserID, U.LoginName, U.email, U.FirstName, U.LastName
FROM mp_roles R, mp_userroles UR, mp_users U
WHERE R.RoleName = [role string]
  AND R.SiteID = [site ID]
  AND UR.RoleID = R.RoleID
  AND U.UserID = UR.UserID

Jamie

7/23/2012 11:38:38 PM
Gravatar
Total Posts 68

Re: Get sitemap and list of people with permissions on them

Thanks very much for the help Jamie.

The first part works fine.

I am not sure how to go about doing the second part though. "Break a comma separated list of role names from the page select into a list of string"

Can you elaborate more please? Maybe let me have the whole script I can put into a view and just call that view in a ASP.Net page?

7/24/2012 10:45:04 AM
Gravatar
Total Posts 1204
Proud member of the mojoPortal team

Help support mojoPortal!
Add-on modules

Re: Get sitemap and list of people with permissions on them

If you look at the contents of any of the *Roles fields on the mp_pages table, you'll see that they contain a semicolon-separated list of role names. I'm not sure why the roles are stored this way instead of as parent/child database relationship tables, but because of this I don't know of any way to flatten out the data you want into a single view or select. Instead, you'll need to do or contract for some programming to gather the role and user information. What I meant by breaking out the role names was something like this:

List<string> roles = authorizedRoles.Replace(";", ",").SplitOnChar(',');

Sorry, but it's just not as easy to gather this data as you had hoped.

Jamie

7/24/2012 10:56:06 AM
Gravatar
Total Posts 1204
Proud member of the mojoPortal team

Help support mojoPortal!
Add-on modules

Re: Get sitemap and list of people with permissions on them

I should mention too that SplitOnChar is a function in mojoPortal.Web.Framework\StringHelper.cs. It uses the Split function to build all non-blank segments into a list of string.

Jamie

7/26/2012 3:40:16 PM
Gravatar
Total Posts 533
feet planted firmly on the ground

Re: Get sitemap and list of people with permissions on them

Hi Dominique/Jamie, you could achieve the whole thing in T-SQL but you'd need to write a function or two, for example to turn the separated list into a table see this thread http://stackoverflow.com/questions/738133/comma-separated-values-in-a-database-field

 

 

You must sign in to post in the forums. This thread is closed to new posts.