Profiles at a glance!

So, you have probably had to dig around in the profile editing program in PE to find who has access to what. If you have more than a couple profiles this can be time consuming.
I put together a bit of sql which helps greatly when testing new versions (and troubleshooting why someone does not have access to something when you do...)

This will sort all profile data by profile-setting-group, profile-setting, and then by profile:



declare @m table (grp varchar(99), node varchar(99), morder int, trail varchar(256), lastparent varchar(99) )

set nocount on
insert into @m
select menus.mnugroup,mnunode,mnuorder,menus.mnugroup , menus.mnugroup
from menus menus with(nolock) where mnunode not in (select mnugroup from menus menus with(nolock)) and mnuactive = 1 order by menus.mnugroup, mnuorder

while exists (select 1 from @m inner join menus menus with(nolock) on mnunode = lastparent and mnunodedepth > 0)
begin
update @m set lastparent = mnugroup, trail = mnugroup + ' > ' + trail
from @m inner join menus menus with(nolock) on mnunode = lastparent and mnunodedepth > 0
end


SELECT
Profiles.proDescr,
isnull(m.trail , case when prmformdescr like '%???%'then prmformname else prmformdescr end) as groupname,
case when prbcontroldescr = '' then prbcontrolname else prbControlDescr end as prbControlDescr ,
convert(int,priAvailable) as priAvailable
FROM
ProfileBase ProfileBase with(nolock) INNER JOIN
ProfileInfo ProfileInfo with(nolock) ON ProfileBase.prbFormName = ProfileInfo.priFormName AND ProfileBase.prbControlName = ProfileInfo.priControlName INNER JOIN
Profiles Profiles with(nolock) ON ProfileInfo.priProfile = Profiles.proCode left outer join
@m m on m.node = prbControlname left outer join
profilemenus profilemenus with(nolock) on prmformname = prbformname
WHERE (ProfileBase.prbAvailable = 1)
order by Profiles.proDescr, isnull(m.trail , case when prmformdescr like '%???%' then prmformname else prmformdescr end),
morder, ProfileBase.prbControlDescr


You will have this access mess sorted out in no time at all! maybe even print this out for PCI ocumentation!

PE version 3.3.4 and PCI compliance

I downloaded the latest PE release recently. After a bit of testing, It seems that blackbaud really has made this pci compliant. What this means is that:

--Any transfer of credit-card data is encrypted
--The credit card data is deleted from the database once the charge completes (except for the last four digits).

We have some business processes that depend on stored credit-card numbers, so we are waiting to upgrade until those are sorted out.

I did find that card numbers are stored in the pc-charge datbase (under heavy encryption). these numbers can only be accessed one at a time using teh trout id. It is a solution for an accountant that needs to refund a charge to an unknown patron, but would not be efficient for reporting purposes. I imagine that similar functions exist in other payment processing programs.

Raisers edge has a functionality for recurring payments in it's latest version. Perhaps a similar feature would benefit us PE users well? Be sure to put in a request if this is something that you can use.

If you decide to store card numbers outside of PE (on paper or otherwise) , your organization must comply with pci standards. Search for PCI "Self-Assesment Questionaire D" or SAQ-D to get the checklist required for such storage.

Happy PEing!

(ps, sorry about the data pollution. My last post about "clean coal" has been moved to another blog)