Report Builder Question

11 posts by 2 authors in: Forums > CMS Builder: Plugins & Add-ons
Last Post: December 15, 2011 (RSS)

Archived
Reply
Reply

By gadefgaertgqe - December 9, 2011

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

Re: [Pixels & Beats] Report Builder Question

Archived
Reply
Reply

By Jason - December 9, 2011

Hi Paul,

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

Hope this helps

---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

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

Re: [Jason] Report Builder Question

Archived
Reply
Reply

By gadefgaertgqe - December 12, 2011 - edited: December 12, 2011

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:

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

Re: [Pixels & Beats] Report Builder Question

Archived
Reply
Reply

By gadefgaertgqe - December 13, 2011 - edited: December 13, 2011

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

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?

Re: [Pixels & Beats] Report Builder Question

Archived
Reply
Reply

By Jason - December 13, 2011 - edited: December 13, 2011

Hi,

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

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

Re: [Jason] Report Builder Question

Archived
Reply
Reply

By gadefgaertgqe - December 13, 2011

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

Re: [Pixels & Beats] Report Builder Question

Archived
Reply
Reply

By Jason - December 13, 2011

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

Re: [Jason] Report Builder Question

Archived
Reply
Reply

By gadefgaertgqe - December 13, 2011

Nice one Jason!! Thanks for the help :)

Re: [Pixels & Beats] Report Builder Question

Archived
Reply
Reply

By gadefgaertgqe - December 14, 2011

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.

Re: [Pixels & Beats] Report Builder Question

Archived
Reply
Reply

By Jason - December 14, 2011

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:

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