Main
Index 		Search
Posts 		Who's
Online 		Log
In

Home: Products: CMS Builder: Plugins & Add-ons:
Report Builder Question

 
 


8bit Pixel
User

Dec 9, 2011, 7:39 AM

Post #1 of 11 (4743 views)
Shortcut
Report Builder Question Can't Post
Hi Guys!

Can I use the report builder to create reports to show how each user account is setup, etc.

I am looking for a quick way to see at a glance all details without going into each one individually.

Thanks!

Paul


Jason
Staff / Moderator


Dec 9, 2011, 11:13 AM

Post #2 of 11 (4735 views)
Shortcut
Re: [Pixels & Beats] Report Builder Question [In reply to] Can't Post
Hi Paul,

Yes you can. You can take any valid MySQL query and use that in report builder.

Hope this helps
---------------------------------------------------
Jason Sauchuk - Programmer 
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/ 


8bit Pixel
User

Dec 12, 2011, 4:34 AM

Post #3 of 11 (4717 views)
Shortcut
Re: [Jason] Report Builder Question [In reply to] Can't Post
OK, plugin is bought and going really well, except for one last hurdle.

I've learnt some MySQL but I seem unable to get the last and most important information. I need to know what sections each user has been given access too. After digging around I think I need to do a MySQL join, and have tried all manner of combinations this morning, with no luck. Here is the working MySQL query I have at the moment:

Code
SELECT dealer_title                               as 'Sub Domain',   
       fullname                                   as 'Full name',   
       email                                      as 'Email',   
       num                                        as 'User No',  
       disabled                                   as 'Account Live',  
       isAdmin                                    as 'Admin Status',   
       DATE_FORMAT(lastLoginDate, '%D %M %Y %T')  as 'Last Login'  
   
   
FROM `<?php echo $TABLE_PREFIX ?>accounts`   
WHERE true  
GROUP BY dealer_title

Can anyone give me a hint on how to get the _accesslist joined?

I think I need to use the following to get the full picture:
'userNum' in _accesslist joined with 'num' in accounts

then I need to show any records where 'userNum' in _accesslist and 'num' match then show info from:
'accessLevel'
'maxRecords'
in the _accesslist

I think I have chosen a fairly involved bit of MySQL query to start learning with lol.

Thanks

Paul


(This post was edited by Pixels & Beats on Dec 12, 2011, 8:04 AM)


8bit Pixel
User

Dec 13, 2011, 8:33 AM

Post #4 of 11 (4673 views)
Shortcut
Re: [Pixels & Beats] Report Builder Question [In reply to] Can't Post
OK, I've kept going as best as I can and I am so very close. My main problem now is that I need to filter out some of the data! I need to only show what the user has access to, but I am able to see what they do and do not have access too..

Code
SELECT dealer_title                               as 'Sub Domain',  
       fullname                                   as 'Full name',  
       email                                      as 'Email',  
       userNum                                    as 'User No', 
       disabled                                   as 'Account Live', 
       isAdmin                                    as 'Admin Status',  
       DATE_FORMAT(lastLoginDate, '%D %M %Y %T')  as 'Last Login', 
       tableName                                  as 'Access List', 
       accessLevel                                as 'Access Level', 
       maxRecords                                 as 'Max Records' 
         
FROM `<?php echo $TABLE_PREFIX ?>accounts` AS accounts_record 
LEFT JOIN `<?php echo $TABLE_PREFIX ?>_accesslist` AS accesslist_record ON accounts_record.num = accesslist_record.userNum  
UNION 
SELECT dealer_title                               as 'Sub Domain',  
       fullname                                   as 'Full name',  
       email                                      as 'Email',  
       userNum                                    as 'User No', 
       disabled                                   as 'Account Live', 
       isAdmin                                    as 'Admin Status',  
       DATE_FORMAT(lastLoginDate, '%D %M %Y %T')  as 'Last Login', 
       tableName                                  as 'Access List', 
       accessLevel                                as 'Access Level', 
       maxRecords                                 as 'Max Records' 
 
FROM `<?php echo $TABLE_PREFIX ?>accounts` AS accounts_record 
RIGHT JOIN `<?php echo $TABLE_PREFIX ?>_accesslist` AS accesslist_record ON accounts_record.num = accesslist_record.userNum 
 
GROUP BY dealer_title

Any ideas?


(This post was edited by Pixels & Beats on Dec 13, 2011, 8:35 AM)


Jason
Staff / Moderator


Dec 13, 2011, 11:00 AM

Post #5 of 11 (4669 views)
Shortcut
Re: [Pixels & Beats] Report Builder Question [In reply to] Can't Post
Hi,

You can try returning the table name from the accesslist section as a subquery like this:

Code
SELECT user.dealer_title                                 as 'Sub Domain',    
       user.fullname                                     as 'Full name',    
       user.email                                        as 'Email',    
       user.num                                          as 'User No',   
       user.disabled                                     as 'Account Live',   
       user.isAdmin                                      as 'Admin Status',    
       DATE_FORMAT(user.lastLoginDate, '%D %M %Y %T')    as 'Last Login', 
        
       (SELECT GROUP_CONCAT(tableName, ', ') FROM 
         `<?php echo $TABLE_PREFIX;?>_accesslist` 
	  WHERE userNum = user.num 
	        AND accessLevel != 0)                     as 'Access List' 
        
    
FROM `<?php echo $TABLE_PREFIX ?>accounts` user    
WHERE true   
GROUP BY user.dealer_title

Hope this helps
---------------------------------------------------
Jason Sauchuk - Programmer 
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/ 

(This post was edited by Jason on Dec 13, 2011, 11:24 AM)


8bit Pixel
User

Dec 13, 2011, 11:18 AM

Post #6 of 11 (4668 views)
Shortcut
Re: [Jason] Report Builder Question [In reply to] Can't Post
Hi Jason,

Thanks for that, I think we are close but I am getting an error of:
MySQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as 'Access List' FROM `cms_accounts` user WHERE true GROUP' at line 12

Paul


Jason
Staff / Moderator


Dec 13, 2011, 11:25 AM

Post #7 of 11 (4666 views)
Shortcut
Re: [Pixels & Beats] Report Builder Question [In reply to] Can't Post
Hi Paul,

Ooops, I missed a closing bracket in my sub-query. I've made this change in the previous post if you want to try that again.

Thanks
---------------------------------------------------
Jason Sauchuk - Programmer 
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/ 


8bit Pixel
User

Dec 13, 2011, 11:52 AM

Post #8 of 11 (4665 views)
Shortcut
Re: [Jason] Report Builder Question [In reply to] Can't Post
Nice one Jason!! Thanks for the help :)


8bit Pixel
User

Dec 14, 2011, 7:19 AM

Post #9 of 11 (4632 views)
Shortcut
Re: [Pixels & Beats] Report Builder Question [In reply to] Can't Post
One more quick question. I am seeing the table name 'all' being output against all users as something they have access to.

I am wondering why this is? The Admin account shows 'all' as it's only one (which is of course expected).

Just curious, and also cautious in case there is an issue I am unaware of.


Jason
Staff / Moderator


Dec 14, 2011, 11:17 AM

Post #10 of 11 (4615 views)
Shortcut
Re: [Pixels & Beats] Report Builder Question [In reply to] Can't Post
Hi Paul,

A user is given an access Level of 1 to the "all" table when you select the "By Section" option for a user. This won't give them access to anything that you don't specify by section, so there is no security problem. However, you probably don't want it to show up for everyone, so try this change:

Code
SELECT user.dealer_title                                 as 'Sub Domain',     
       user.fullname                                     as 'Full name',     
       user.email                                        as 'Email',     
       user.num                                          as 'User No',    
       user.disabled                                     as 'Account Live',    
       user.isAdmin                                      as 'Admin Status',     
       DATE_FORMAT(user.lastLoginDate, '%D %M %Y %T')    as 'Last Login',  
         
       (SELECT GROUP_CONCAT(tableName, ', ') FROM  
         `<?php echo $TABLE_PREFIX;?>_accesslist`  
	  WHERE userNum = user.num  
	        AND accessLevel != 0 
		AND accessLevel != 1)                     as 'Access List'  
         
     
FROM `<?php echo $TABLE_PREFIX ?>accounts` user     
WHERE true    
GROUP BY user.dealer_title

Hope this helps
---------------------------------------------------
Jason Sauchuk - Programmer 
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/ 


8bit Pixel
User

Dec 15, 2011, 1:51 AM

Post #11 of 11 (4560 views)
Shortcut
Re: [Jason] Report Builder Question [In reply to] Can't Post
Ahhh I get it! Thanks for explaining Jason. Much appreciated.