Think Inside The Box

If you are using Packages and coupons, you probably have found that it can be difficult to see what is inside the package at a glance. This bit of sql will compress a package to a 2D dataset for easy viewing. Note - this will expand a show list to a list of events attached, but it will not do calculations on time-limitations. It only lists time limitations.

**updated 9/14/11 - old code had incorrect link from limitsetslink to packageitems.


declare @packageid int
set @packageid = 4

declare @item_type as table (code int, descr varchar(99))
insert @item_type (code, descr)
select 1,'Event'
union select 3,'Merchandise'
union select 4,'Series'
union select 5,'Memberships'
union select 6,'Events'
union select 7,'Shows'

declare @limittype as table (code int, descr varchar(99), source varchar(99))

insert @limittype (code, descr, source)
select 9,'Pricetype', 'pricetypes'
union select 10,'Subs Price Type', 'subscriptiontype'
union select 2,'Shows', 'shows'
union select 3,'Halls', 'halls'
union select 7,'Merchandise', 'merchandiseitems'
union select 8,'Price Levels', 'tariff'
union select 14,'Time Limitations', 'timelimitations'
union select 4,'Areas', 'areas'
union select 6,'Series', 'serieses'


declare @benefit_type table (code int, descr varchar(99))
insert @benefit_type (code, descr)
select 0,'No Benefit (qualifier)'
union select 1,'Nominal'
union select 2,'Percent'
union select 3,'Free Item'

declare @timelimitcat as table (code int, descr varchar(99))
insert @timelimitcat (code, descr)
select 1, 'Dates+Hours'
union select 2,'Days of Week + Hours'
union select 3,'Hours'

--select * from @item_type
--select * from @limittype
--select * from @benefit_type
--select * from @timelimitcat

SELECT
pgi_code,
pgi_BasketItemType,
isnull(it.descr ,'?') as item_type_descr,
pgi_MinItemsCount, pgi_MaxItemsCount,
pgi_BenefitType,
isnull(bt.descr,'?') as benefit_type_descr,
pgi_BenefitValue,
0 as pgi_PriceTypeForCommission,
'' as pricetype_for_commission,
lsl_LimitType,
isnull(limittype.descr,'?') as limit_type_descr,
lsl_LimitID,
lsl_AvailabilityType,
replicate(' ',50) as limit_descr,
isnull(eveventdate,'1/1/1900') as evdate
into #pkg
FROM
PKG_PackagesItems PKG_PackagesItems left outer JOIN
LimitSetsLink ON pgi_limitset = lsl_LimitSetID
left outer join @limittype as limittype on limittype.code = lsl_LimitType
left outer join @benefit_type as bt on bt.code = pgi_BenefitType
left outer join @item_type as it on it.code = pgi_BasketItemType
left outer join events on evshow = lsl_LimitID and lsl_limittype = 2
WHERE (pgi_Package = @packageid)
order by pgi_Package, pgi_code

update #pkg set limit_descr = 'All' where lsl_limittype in (10,9,2,3,7,8,4,6) and lsl_limitID = 0

update #pkg
set limit_descr = pctdescr
from #pkg
inner join PriceType ON lsl_limitID = pctCode
where lsl_limittype = 9

update #pkg
set limit_descr = sbtdescr
from #pkg
inner join subscriptiontype ON lsl_limitID = sbtCode
where lsl_limittype = 10

update #pkg
set limit_descr = left(shdescr,50)
from #pkg
inner join shows ON lsl_limitID = shcode
where lsl_limittype = 2

update #pkg
set limit_descr = left(haname,50)
from #pkg
inner join halls ON lsl_limitID = hacode
where lsl_limittype = 3

update #pkg
set limit_descr = left(srdescr,50)
from #pkg
inner join serieses ON lsl_limitID = srcode
where lsl_limittype = 6


update #pkg
set limit_descr = left(mitdescr,50)
from #pkg
inner join merchandiseitems ON lsl_limitID = mitcode
where lsl_limittype = 7

update #pkg
set limit_descr = left(tadescr,50)
from #pkg
inner join tariff ON lsl_limitID = tacode
where lsl_limittype = 8

update #pkg
set limit_descr =
case when lsl_availabilitytype = 1 then 'Available for: ' else 'Not Available for: ' end
+ left(ltg_description,50)
from #pkg
inner join LimitTimeGroup ON lsl_limitID = ltg_recordid
where lsl_limittype = 14


select * from #pkg
order by pgi_code, limit_type_descr, evdate, limit_descr

drop table #pkg



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!