#returns crosstab of total count of filedownload permissions (role_id = 2) for users that have this role set for each dataset specified


select * from crosstab('select au.useridentifier||'' (''|| au.firstname ||'' ''||  au.lastname|| '' '' || au.email|| '')'' as username, dv2.id as dv2_id, count(*) from authenticateduser au 
inner join roleassignment ra on ''@''||au.useridentifier=ra.assigneeidentifier 
inner join dvobject dv1 on ra.definitionpoint_id = dv1.id 
inner join dvobject dv2 on dv1.owner_id = dv2.id 
where ra.role_id=2 and dv2.dtype=''Dataset'' and dv2.id in (12,35,152)
group by username, dv2_id order by username, dv2_id',
'select id from dvobject
where dtype=''Dataset'' and id in (12,35,152) group by id order by id')
AS (username text, dset_12_filedwnld_role_count int, dset_35_filedwnld_role_count int, dset_152_filedwnld_role_count int);


        username                             | dset_12_filedwnld_role_count | dset_35_filedwnld_role_count  | dset_152_filedwnld_role_count 
---------------------------------------------+------------------------------+-------------------------------+------------------------------
 userid1 (u1name u1lastname u1email) 	     |                              |                           3   |                               
 userid2 (u2name u2lastname u2email)         |                              |                               |                            1
 userid3 (u3name u3lastname u3email)         |                              |                           3   |                               
(3 rows)






#returns crosstab of 1/<nothing> for users with filedownload permission (role_id = 2) for each dataset specified (1 indicates at least 1 filedownload permission (role_id = 2) for the dataset) 


select * from crosstab('select au.useridentifier||'' (''|| au.firstname ||'' ''||  au.lastname|| '' '' || au.email|| '')'' as username, dv2.id as dv2_id, (case when count(*) > 0 then 1 end) from authenticateduser au 
inner join roleassignment ra on ''@''||au.useridentifier=ra.assigneeidentifier 
inner join dvobject dv1 on ra.definitionpoint_id = dv1.id 
inner join dvobject dv2 on dv1.owner_id = dv2.id 
where ra.role_id=2 and dv2.dtype=''Dataset'' and dv2.id in (12,35,152)
group by username, dv2_id order by username, dv2_id',
'select id from dvobject
where dtype=''Dataset'' and id in (12,35,152) group by id order by id')
AS (username text, dset_12_filedwnld_role_perm int, dset_35_filedwnld_role_perm int, dset_152_filedwnld_role_perm int);


    	username                    	    | dset_12_filedwnld_role_perm   | dset_35_filedwnld_role_perm   | dset_152_filedwnld_role_perm
--------------------------------------------+-------------------------------+-------------------------------+--------------------------------
 userid1 (u1name u1lastname u1email) 	    |                               |                             1 |                               
 userid2 (u2name u2lastname u2email)        |                               |                               |                            1
 userid3 (u3name u3lastname u3email)        |                               |                             1 |                               
(3 rows)








#returns crosstab of 'x'/<nothing> for users with filedownload permission (role_id = 2) for each dataset specified ('x' indicates at least 1 filedownload permission (role_id = 2) for the dataset) 


select * from crosstab('select au.useridentifier||'' (''|| au.firstname ||'' ''||  au.lastname|| '' '' || au.email|| '')'' as username, dv2.id as dv2_id, (case when count(*) > 0 then ''x'' end) from authenticateduser au 
inner join roleassignment ra on ''@''||au.useridentifier=ra.assigneeidentifier 
inner join dvobject dv1 on ra.definitionpoint_id = dv1.id 
inner join dvobject dv2 on dv1.owner_id = dv2.id 
where ra.role_id=2 and dv2.dtype=''Dataset'' and dv2.id in (12,35,152)
group by username, dv2_id order by username, dv2_id',
'select id from dvobject
where dtype=''Dataset'' and id in (12,35,152) group by id order by id')
AS (username text, dset_12_filedwnld_role_perm text, dset_35_filedwnld_role_perm text, dset_152_filedwnld_role_perm text);


        username                             | dset_12_filedwnld_role_perm   | dset_35_filedwnld_role_perm   | dset_152_filedwnld_role_perm 
---------------------------------------------+-------------------------------+-------------------------------+--------------------------------
 userid1 (u1name u1lastname u1email) 	     |                               | x                             | 
 userid2 (u2name u2lastname u2email)         |                               |                               | x
 userid3 (u3name u3lastname u3email)         |                               | x                             | 
(3 rows)