Soundscan

If any of you send files to nielsen for reporting cd sales, here is the format for the file that is sent. I may be the first to post this anywhere on the internet. how very exciting.


EAN DOWNLOAD DATA FORMAT

This is the format used by vendors who download data to the SoundScan system.

RECORDS/FIELDS CHARACTERS POSITION

STORE RETAIL SALES HEADER RECORD
Record Number (92) 2 1 - 2
Chain Number 5 3 - 7
Individual Store No. 5 8 - 12
Period End Date (YYMMDD) 6 13 - 18
Filler (Blanks) 2 19 - 20

STORE RETAIL SALES RECORD
Record Number (I3) 2 1 - 2
UPC Number of Selection 13 3 - 15
Position 3 = "0" if less the 13 digits
Positions 3-15 = EAN 13
Quantity Sold 5 16 - 20

STORE RETAIL SALES TRAILER RECORD
Record Number (94) 2 1 - 2
Number of Sales Records (I3's) 5 3 - 7
Number of Units Sold 7 8 - 14
Filler (Blanks) 6 15 - 20


20 Byte Records:
1 - 92 = Header Record per store
Multiple - I3 Data sales info records ( one for each EAN sold)
1 - 94 = Trailer Record with sum of items sold



FILE FORMAT - ASCII, DOS CR/LF, OEF AT END OF FILE

920090100010061029
I30000002900049-0001
I3000002900015900001
I3012342900106900001
I3123457994450200001
I3000007994455200001
94 5 4



List series locks in seat ranges

Aha!
I have rigged this one up using a sonic screwdriver, and it just might work.
This is like the single-event-lock-list rpt in a prev post, except for series locks:


CREATE procedure moa_series_lock_count_list (@series int, @season int, @scheme int)
as
declare @l table (event int, chair int, area int, row int, col int, vertical bit, rowname varchar(5), seatname varchar(5), sec varchar(5), ticktype int )

insert into @l (event, chair, area, row, col, vertical, rowname, seatname, sec, ticktype)
select slsseries, slschair, slsarea , ahrow, ahcol, 0, ahline, ahchair, left(ahshortarname,5), slsticktype
from moa_pe..serieslockedseats serieslockedseats with(nolock)
inner join moa_pe..areachair areachair with(nolock) on slschair = ahchaircode
where slsseries > 0 and slsticktype between 2000 and 3000
and slsseries in (select srcode from moa_pe..serieses sr with(nolock)
where (srcode = @series or @series = 0)
and (srseasons = @season or @season = 0)
and (srshowtype = @scheme or @scheme = 0) )
order by slsseries, slschair, slsarea, ahrow, ahcol

update @l set vertical = 1 where area in (select arcode from moa_pe..areas with(nolock) where arname like '%boxes%' )

declare @out table (event int, list varchar(255), row varchar(5), area int, cnt int, ticktype int, sec varchar(10) )

declare @ev int, @oldev int, @ar int, @oldar int, @row int, @oldrow int, @col int, @oldcol int
declare @sec varchar(5), @oldsec varchar(5) , @rowname varchar(5), @oldrowname varchar(5), @seat varchar(5), @oldseat varchar(5)
declare @ticktype int, @oldticktype int
declare @count int, @seatstr varchar(5000)



declare ch cursor fast_forward for
select event, area, row, col, rowname, seatname, sec, ticktype
from @l
where vertical = 0 order by event, area, row, col

open ch
fetch next from ch into @ev, @ar, @row, @col, @rowname, @seat, @sec, @ticktype

set @count = 1
set @seatstr = ''

set @oldev = @ev
set @oldar = @ar
set @oldrow = @row
set @oldcol = @col - 1
set @oldsec = @sec
set @oldrowname = @rowname
set @oldseat = ''
set @oldticktype = @ticktype

while @@fetch_status = 0
begin

if (@ev <> @oldev or @ar <> @oldar or @row <> @oldrow or @ticktype <> @oldticktype or @col <> @oldcol + 1)
begin
insert into @out (event,list,row,area,cnt, ticktype, sec) select @oldev, @seatstr, @oldrowname, @oldar, @count, @oldticktype, @oldsec
set @seatstr = '' set @count = 0
end
set @count = @count + 1 set @seatstr = @seatstr + @seat + ','

set @oldev = @ev
set @oldar = @ar
set @oldrow = @row
set @oldcol = @col
set @oldsec = @sec
set @oldrowname = @rowname
set @oldseat = @seat
set @oldticktype = @ticktype


fetch next from ch into @ev, @ar, @row, @col, @rowname, @seat, @sec, @ticktype
end
close ch
deallocate ch


--last grp:
insert into @out (event,list,row,area,cnt, ticktype, sec) select @oldev, @seatstr, @oldrowname, @oldar, @count, @oldticktype, @oldsec

declare cv cursor fast_forward for
select event, area, row, col, rowname, seatname, sec, ticktype
from @l
where vertical = 1 order by event, area, col, row

open cv
fetch next from cv into @ev, @ar, @row, @col, @rowname, @seat, @sec, @ticktype

set @count = 1
set @seatstr = ''

set @oldev = @ev
set @oldar = @ar
set @oldrow = @row - 1
set @oldcol = @col
set @oldsec = @sec
set @oldrowname = @rowname
set @oldseat = ''
set @oldticktype = @ticktype

while @@fetch_status = 0
begin

if (@ev <> @oldev or @ar <> @oldar or @col <> @oldcol or @ticktype <> @oldticktype or @row <> @oldrow + 1 or @oldrowname <> @rowname)
begin
insert into @out (event,list,row,area,cnt, ticktype, sec) select @oldev, @seatstr, @oldrowname, @oldar, @count, @oldticktype, @oldsec
set @seatstr = '' set @count = 0
end
set @count = @count + 1 set @seatstr = @seatstr + @seat + ','

set @oldev = @ev
set @oldar = @ar
set @oldrow = @row
set @oldcol = @col
set @oldsec = @sec
set @oldrowname = @rowname
set @oldseat = @seat
set @oldticktype = @ticktype


fetch next from cv into @ev, @ar, @row, @col, @rowname, @seat, @sec, @ticktype
end
close cv
deallocate cv

--last grp:
insert into @out (event,list,row,area,cnt, ticktype, sec) select @oldev, @seatstr, @oldrowname, @oldar, @count, @oldticktype, @oldsec



update @out set list = left(list,len(list)-1) where len(list) > 1
update @out set list = right(list,len(list)-1) where list like ',%'
select o.*, clrdescr, srdescr, arname
from @out as o inner join
moa_pe..colors c on clrstatus = o.ticktype inner join
moa_pe..serieses on srcode = o.event inner join
moa_pe..areas on arcode = o.area
order by srdescr, arname, sec, row, list


GO

Yet More useful lock-reporting.

Ah yes. Now what if you have a lock on one series, that shares events with another series, and there is a lock on the other series? How are you to be master of your locks, let alone the universe, if you have a messy lock situation?
Not sure how that happened, but it's as easy as pi to find them:



create procedure moa_series_locks_overlapping
as
SELECT Serieses.srDescr AS series1, Colors.clrDescr AS lock1, Serieses_1.srDescr AS series2, Colors_1.clrDescr AS lock2, AreaChair.ahShortArName AS sec,
AreaChair.ahLine AS row, AreaChair.ahChair AS seat
FROM
moa_pe..LinkSeriesEvents LinkSeriesEvents with(nolock) INNER JOIN
moa_pe..LinkSeriesEvents LinkSeriesEvents_1 with(nolock) INNER JOIN
moa_pe..SeriesLockedSeats SeriesLockedSeats_1 with(nolock) INNER JOIN
moa_pe..SeriesLockedSeats SeriesLockedSeats with(nolock) ON SeriesLockedSeats_1.slsSeries > SeriesLockedSeats.slsSeries AND
SeriesLockedSeats_1.slsChair = SeriesLockedSeats.slsChair ON LinkSeriesEvents_1.lseSeriesCode = SeriesLockedSeats_1.slsSeries ON
LinkSeriesEvents.lseSeriesCode = SeriesLockedSeats.slsSeries AND LinkSeriesEvents.lseEventCode = LinkSeriesEvents_1.lseEventCode INNER JOIN
moa_pe..Serieses Serieses with(nolock) ON SeriesLockedSeats.slsSeries = Serieses.srCode INNER JOIN
moa_pe..Serieses Serieses_1 with(nolock) ON SeriesLockedSeats_1.slsSeries = Serieses_1.srCode INNER JOIN
moa_pe..Colors Colors with(nolock) ON SeriesLockedSeats.slsTickType = Colors.clrStatus INNER JOIN
moa_pe..Colors Colors_1 with(nolock) ON SeriesLockedSeats_1.slsTickType = Colors_1.clrStatus INNER JOIN
moa_pe..AreaChair AreaChair with(nolock) ON SeriesLockedSeats_1.slsChair = AreaChair.ahChairCode
WHERE serieses_1.srdescr not like '%qrt' and serieses.srdescr not like '%qrt'
group by Serieses.srDescr , Colors.clrDescr , Serieses_1.srDescr , Colors_1.clrDescr , AreaChair.ahShortArName ,
AreaChair.ahLine , AreaChair.ahChair

GO

More lock-reporting tidbits

Now its time for some real fun. a report that does something useful for somone i'm sure. This will allow you to find groups of 2 adjacent locks, singles, n adjacent locks, or just list them all.
It takes parameters for event date range and lock type, and lists out the seat locations of these groups of locks. If you are like us, you have some areas in which the rows run vertical. the section of code 'update ... set vertical = 1 where ...' accounts for these.
In our business, single locks are useless for certain lock types, like whell-chair locks, which need a pair.
There is a 65.36 percent chance that you will find this code useful.



CREATE procedure moa_event_lock_count_list (@event int, @evdatefrom datetime, @evdateto datetime, @showtype int, @locktype int, @cntfilter int)
as
declare @l table (event int, chair int, area int, row int, col int, vertical bit, rowname varchar(5), seatname varchar(5), sec varchar(5), ticktype int )

insert into @l (event, chair, area, row, col, vertical, rowname, seatname, sec, ticktype)
select elsevent, elschair, elsarea , ahrow, ahcol, 0, ahline, ahchair, left(ahshortarname,5), elsticktype
from
moa_pe..eventlockedseats eventlockedseats with(nolock)
inner join moa_pe..areachair areachair with(nolock) on elschair = ahchaircode
where
elsticktype between 2000 and 3000
and (elsticktype = @locktype or @locktype = 0)
and elsevent in (select evcode
from moa_pe..events with(nolock) inner join
moa_pe..shows with(nolock) on shcode = evshow
where eveventdate between @evdatefrom and @evdateto
and (shshowtype = @showtype or @showtype = 0) )

order by elsevent, elschair, elsarea, ahrow, ahcol

update @l set vertical = 1 where area in (select arcode from moa_pe..areas with(nolock) where arname like '%boxes%' )

declare @out table (event int, list varchar(255), row varchar(5), area int, cnt int, ticktype int, sec varchar(10) )

declare @ev int, @oldev int, @ar int, @oldar int, @row int, @oldrow int, @col int, @oldcol int
declare @sec varchar(5), @oldsec varchar(5) , @rowname varchar(5), @oldrowname varchar(5), @seat varchar(5), @oldseat varchar(5)
declare @ticktype int, @oldticktype int
declare @count int, @seatstr varchar(5000)



declare ch cursor fast_forward for
select event, area, row, col, rowname, seatname, sec, ticktype
from @l
where vertical = 0 order by event, area, row, col

open ch
fetch next from ch into @ev, @ar, @row, @col, @rowname, @seat, @sec, @ticktype

set @count = 1
set @seatstr = ''

set @oldev = @ev
set @oldar = @ar
set @oldrow = @row
set @oldcol = @col - 1
set @oldsec = @sec
set @oldrowname = @rowname
set @oldseat = ''
set @oldticktype = @ticktype

while @@fetch_status = 0
begin

if (@ev <> @oldev or @ar <> @oldar or @row <> @oldrow or @ticktype <> @oldticktype or @col <> @oldcol + 1)
begin
insert into @out (event,list,row,area,cnt, ticktype, sec) select @oldev, @seatstr, @oldrowname, @oldar, @count, @oldticktype, @oldsec
set @seatstr = '' set @count = 0
end
set @count = @count + 1 set @seatstr = @seatstr + @seat + ','

set @oldev = @ev
set @oldar = @ar
set @oldrow = @row
set @oldcol = @col
set @oldsec = @sec
set @oldrowname = @rowname
set @oldseat = @seat
set @oldticktype = @ticktype


fetch next from ch into @ev, @ar, @row, @col, @rowname, @seat, @sec, @ticktype
end
close ch
deallocate ch


--last grp:
insert into @out (event,list,row,area,cnt, ticktype, sec) select @oldev, @seatstr, @oldrowname, @oldar, @count, @oldticktype, @oldsec

declare cv cursor fast_forward for
select event, area, row, col, rowname, seatname, sec, ticktype
from @l
where vertical = 1 order by event, area, col, row

open cv
fetch next from cv into @ev, @ar, @row, @col, @rowname, @seat, @sec, @ticktype

set @count = 1
set @seatstr = ''

set @oldev = @ev
set @oldar = @ar
set @oldrow = @row - 1
set @oldcol = @col
set @oldsec = @sec
set @oldrowname = @rowname
set @oldseat = ''
set @oldticktype = @ticktype

while @@fetch_status = 0
begin

if (@ev <> @oldev or @ar <> @oldar or @col <> @oldcol or @ticktype <> @oldticktype or @row <> @oldrow + 1 or @oldrowname <> @rowname)
begin
insert into @out (event,list,row,area,cnt, ticktype, sec) select @oldev, @seatstr, @oldrowname, @oldar, @count, @oldticktype, @oldsec
set @seatstr = '' set @count = 0
end
set @count = @count + 1 set @seatstr = @seatstr + @seat + ','

set @oldev = @ev
set @oldar = @ar
set @oldrow = @row
set @oldcol = @col
set @oldsec = @sec
set @oldrowname = @rowname
set @oldseat = @seat
set @oldticktype = @ticktype


fetch next from cv into @ev, @ar, @row, @col, @rowname, @seat, @sec, @ticktype
end
close cv
deallocate cv

--last grp:
insert into @out (event,list,row,area,cnt, ticktype, sec) select @oldev, @seatstr, @oldrowname, @oldar, @count, @oldticktype, @oldsec



update @out set list = left(list,len(list)-1) where len(list) > 1
update @out set list = right(list,len(list)-1) where list like ',%'
select o.*, clrdescr, eveventdate, left(shdescr,15) as showname, arname
from @out as o inner join
moa_pe..colors c with(nolock) on clrstatus = o.ticktype inner join
moa_pe..events with(nolock) on evcode = o.event inner join
moa_pe..areas with(nolock) on arcode = o.area inner join
moa_pe..shows with(nolock) on shcode = evshow
where (cnt = @cntfilter or @cntfilter = 0)
order by eveventdate, arname, sec, row, list


GO



How did k-9 come up with so many significant digits for a probability of random occurences? It might help that he was in a time machine...

Get a handle on your series Locks!

You have serieses to sell, and many of them. you put locks on the series chart to prevent the single events from selling on those seats, so you can sell the subscriptions with all the same seat. You have a bad rep that took the lock on just one of the events in the series, (or you were the victim of the bug (now fixed) in which the series locks did not propogate if seat-info was on the event seat) and now that seat can't be sold for the subscription without allocating one of the seats to a different seat. annoying, right? With a bit of sql you can generate a list of seats that have been 'pirated' in this way:
The following gets a list of locks that should be there, and checks to see if the locks are there, and if not, the seat status. Arr, matey.


--replace moa_pe.. w/ yourdbname..
--the @inc_stage_ext is an option to include or exclude event-lock type pirates

CREATE PROCEDURE moa_series_lock_pirated_seats
(@season int, @inc_stage_ext bit = 0)

AS
/*
--drop table #sl
declare @season int
set @season = 17
declare @inc_stage_ext bit
set @inc_stage_ext = 0
*/
--get all eventt locks that should be there:
create table #sl (s int, e int, c int, ticktype varchar(40), ticktypecode int, lockuser varchar(50), lockdate datetime, evc int)
create index slec on #sl (e,c)

--declare #sl table (s int, e int, c int, ticktype varchar(40), ticktypecode int, lockuser varchar(50), lockdate datetime)
declare @out table (s int, e int, c int, srname varchar(20), evdate datetime, shname varchar(50), lockuser varchar(50),
ticktype varchar(50), pirate varchar(50), acct int, transact int, piratedate datetime, sec varchar(10), row varchar(5), seat varchar(5), reason varchar(50), lockdate datetime )

insert into #sl (s, e, c, ticktype, ticktypecode , evc)
SELECT
SeriesLockedSeats.slsSeries as s,
LinkSeriesEvents.lseEventCode as e,
SeriesLockedSeats.slsChair as c ,
clrdescr,
slsticktype,
ah2.ahchaircode
FROM
moa_pe.dbo.Serieses Serieses with(nolock) INNER JOIN
moa_pe.dbo.SeriesLockedSeats SeriesLockedSeats with(nolock) ON Serieses.srCode = SeriesLockedSeats.slsSeries INNER JOIN
moa_pe.dbo.LinkSeriesEvents LinkSeriesEvents with(nolock) ON SeriesLockedSeats.slsSeries = LinkSeriesEvents.lseSeriesCode inner join
moa_pe..eventsareas with(nolock) on evaevent = lseeventcode inner join
moa_pe..areachair ah1 with(nolock) on ah1.ahchaircode = slschair inner join
moa_pe..areachair ah2 with(nolock) on ah2.ahparentseatid = ah1.ahparentseatid and ah2.ahareacode = evaarea
inner join moa_pe..colors colors with(nolock) on clrstatus = slsticktype inner join
moa_pe..events with(nolock) on evcode = lseeventcode and evhall = srhall
WHERE (Serieses.srSeasons = @season)
--AND (LinkSeriesEvents.lseIsRequiered = 1)
and slsticktype between 2000 and 3000
order by LinkSeriesEvents.lseEventCode , SeriesLockedSeats.slsChair

delete from #sl
from #sl inner join moa_pe..subsprogram with(nolock) on proseatnum = c and proseriescode = s and procurrentstatus = 10 and proseasoncode = @season

update #sl set lockuser = uscode ,lockdate = slhdatelocked
from #sl as sl
inner JOIN
moa_pe..SeatLockHistory SeatLockHistory with(nolock) ON sl.s = SeatLockHistory.slhEventCode AND
sl.c = SeatLockHistory.slhSeatCode AND sl.ticktypecode = SeatLockHistory.slhTickType
and slhlocktype = 3
left outer join
moa_pe..users users with(nolock) on usrecid = slhuserlocked

insert into @out (s,e,c, ticktype, lockuser, piratedate, pirate, acct, transact, reason, lockdate)
select s,e,c, ticktype, lockuser,
tiactualsaleddate,
uscode,
timailinglist,
titransactnum,
'Sold as single ticket',
lockdate
from #sl as sl inner join
moa_pe..tickets t with(nolock) on tievent = sl.e and tichair = sl.evc and tistatus = 1 inner join
moa_pe..shifts with(nolock) on sfcode = tishift and sfactiontype = 0 left outer join
moa_pe..users with(nolock) on usrecid = sfuser

insert into @out (s,e,c, ticktype, lockuser, piratedate, pirate, acct, transact, reason, lockdate)
select s,e,c, ticktype, lockuser,
tisactualsaledate,
uscode,
prosubsnum,
tistransactnum,
'Sold as subscription ticket',
lockdate
from #sl as sl inner join
moa_pe..ticketssubscription t with(nolock) on tisevent = sl.e and tischair = sl.evc and tisstatus = 1 inner join
moa_pe..shifts with(nolock) on sfcode = tisshift and sfactiontype = 0 inner join
moa_pe..subsprogram with(nolock) on case when prolasttransact > 0 then prolasttransact else prorecnum end = tissubsprogram and procurrentstatus = 10 left outer join
moa_pe..users with(nolock) on usrecid = sfuser


insert into @out (s,e,c, ticktype, lockuser, piratedate, pirate, acct, transact, reason, lockdate)
select s,e,c, ticktype, lockuser,
tibtimestamp,
uscode,
0,
0,
'Event Lock-stage ext',
lockdate
from #sl as sl inner join
moa_pe..ticketbase t with(nolock) on tibevent = sl.e and tibchair = sl.evc and tibticktype = 2 left outer join
moa_pe..users with(nolock) on usrecid = tibuser

insert into @out (s,e,c, ticktype, lockuser, piratedate, pirate, acct, transact, reason, lockdate)
select s,e,c, ticktype, lockuser,
tbxactualactiondate,
uscode,
tbxmailcustomer,
tbxtransactnum,
'Sold as Reservation',
lockdate
from #sl as sl inner join
moa_pe..ticketbaseextra t with(nolock) on tbxevent = sl.e and tbxchair = sl.evc and tbxstatus = 1 left outer join
moa_pe..users with(nolock) on usrecid = tbxuser

insert into @out (s,e,c, ticktype, lockuser, piratedate, pirate, acct, transact, reason, lockdate)
select sl.s,sl.e,sl.c, sl.ticktype, sl.lockuser,
'1/1/1900',
isnull(srdescr,''),
0,
0,
'Locked as: ' + clrdescr,
sl.lockdate
from #sl as sl inner join
moa_pe..eventlockedseats els with(nolock) on elsevent = sl.e and elschair = sl.evc and elsticktype <> sl.ticktypecode and elsticktype between 2000 and 3000 left outer join
@out o on sl.c = o.c and sl.e = o.e and sl.s = o.s inner join
moa_pe..colors with(nolock) on clrstatus = elsticktype left outer join
moa_pe..serieses sr with(nolock) on srcode = elsseries
where o.e is null

insert into @out (s,e,c, ticktype, lockuser, piratedate, pirate, acct, transact, reason, lockdate)
select s.s,s.e,s.c, s.ticktype, s.lockuser, '1/1/1900', '', 0, 0, 'Not locked on event', s.lockdate
from #sl s left outer join
@out o on s.c = o.c and s.e = o.e left outer join
moa_pe..eventlockedseats on elsevent = s.e and elschair = s.evc and elsticktype = s.ticktypecode
where elsevent is null and o.e is null

update @out
set
srname = left(srdescr,20),
evdate = eveventdate,
shname = left(shdescr,20),
sec = ahshortarname,
row = ahline,
seat = ahchair
from @out inner join
moa_pe..serieses on srcode = s inner join
moa_pe..areachair on ahchaircode = c inner join
moa_pe..events on evcode = e inner join
moa_pe..shows on shcode = evshow

select * from @out
where (@inc_stage_ext = 1 or reason not like '%stage ext%')
order by srname, sec, row, seat

drop table #sl

GO

Move audience and Access control

So, this will be the last post on move-audience. I am obsessed.
We used move-audience to move tickets between events, and found that the barcodes table is not updated in the move. The barcodes table stores the event code in the brtentity field, which the scanning sp's use to look up the event.
An alternative to this query is to use the company table setting 'Reset print status On Move Audience' which will unprint the moved tickets, so you can re-issue them with valid barcodes.
So, if you use move-audience between events, and also use scanners, use this sql to update the barcodes table :

update barcodes
set brtentitycode = tievent
from
Tickets with(nolock) INNER JOIN
Barcodes ON Tickets.tiCode = Barcodes.brtSourceCode
AND Tickets.tiEvent <> Barcodes.brtEntityCode inner join
events with(nolock) on tievent = evcode
WHERE
(Barcodes.brtSourceTable = 1)
AND (Tickets.tiSaleDate > '1/1/08')
AND (Tickets.tiStatus = 1)
and brtEntityType = 1
and eveventdate > getdate()

Move Audience Audit script

I came across the instance where ticket sales ws confused after a customer called in claiming they had ordered different seats than what was mailed to them. the reason their seats had changed was due to a stage extension on the event, where the audience was moved using the move-audience feature in PE administration. I figured that a good solution to this problem would be to insert a remark for each seat affected on the patron's account, since there is no other way to see move-audience history with PE . The following stored procedure is what I use to do this. I have it set up to stuff remarks every night for seat moves that occured in the past 24 hours:


create procedure moa_create_moveaudience_remarks as

declare @fromdate datetime
set @fromdate = getdate() - 1

select
identity(int) as id,
masl.maslrecordid,
maslupdatedate,
uscode,
slpmachinename,
tix.cltcode,
tix.cltgroupcode,
tix.type,
evfrom.eveventdate as fromevdate,
shfrom.shdescr as fromshow,
arfrom.arname as fromar,
ahfrom.ahshortarname as fromsec,
ahfrom.ahline as fromrow,
ahfrom.ahchair as fromseat,
evto.eveventdate as toevdate,
shto.shdescr as toshow,
arto.arname as toar,
ahto.ahshortarname as tosec,
ahto.ahline as torow,
ahto.ahchair as toseat
into #m
from
MoveAudienceSeatsLog masl with(nolock)
inner join
(select ticode as tcode, timailinglist as cltcode, ticlientgroupcode as cltgroupcode, 'Single' as type from tickets with(nolock)
union
select tiscode, prosubsnum, procustnum, 'Subs' from ticketssubscription with(nolock)
inner join subsprogram with(nolock) on prorecnum = tissubsprogram
union
select tbxcode, tbxmailcustomer, tbxclientgroupcode, 'Reservation' from ticketbaseextra with(nolock) )
as tix on masl.maslticketcode = tix.tcode inner join
areachair ahfrom with(nolock) on masl.masloldseat = ahfrom.ahchaircode inner join
areachair ahto with(nolock) on masl.maslnewseat = ahto.ahchaircode inner join
areas arfrom with(nolock) on arfrom.arcode = masl.masloldareacode inner join
areas arto with(nolock) on arto.arcode = masl.maslnewareacode inner join
events evfrom with(nolock) on masl.masloldeventcode = evfrom.evcode inner join
shows shfrom with(nolock) on shfrom.shcode = evfrom.evshow inner join
events evto with(nolock) on evto.evcode = masl.maslneweventcode inner join
shows shto with(nolock) on shto.shcode = evto.evshow inner join
users with(nolock) on masluser = usrecid inner join
salespoints with(nolock) on salespoints.slpnumber = masl.maslsalepoint
where maslupdatedate > @fromdate
order by maslrecordid

declare @rmkstartnum int, @rmknewnum int
set @rmkstartnum = (SELECT coLastNumber + 1 FROM Counters WHERE (coName = 'TableCustomerRemarks'))
set @rmknewnum = (select count(*) from #m) + @rmkstartnum
update Counters set coLastNumber = @rmknewnum where (coName = 'TableCustomerRemarks')


insert into moa_pe.dbo.customerremarks
select id + @rmkstartnum , cltgroupcode, cltcode, 0, 53, maslupdatedate,
'From ' + convert(varchar(20),fromevdate,101) + ' ' + left(fromshow,10) + ' To: ' + convert(varchar(20),toevdate,101) + ' - ' + left(toshow,10)
+ char(13) + char(10)
+ 'From: ' + ' ' + fromsec + ' ' + fromrow + ' ' + fromseat + ' to' + char(13) + char(10)
+ 'To: ' + ' ' + tosec + ' ' + torow + ' ' + toseat + ' ' + char(13) + char(10)
+ 'By: ' + Uscode + ' ' + slpmachinename
,
1, 0, 0, '1/1/1900', 1
from #m

--select * from #m

drop table #m


GO



Print at Home

I recently had to design our print at home ticket documents. Read this and save time. You're welcome in advance.
Print at home ticket design Basics:

• Use ctrl+x to remove a field
• If you put html in a free-text field, be sure to include the / at the end of image, br, or other tags e.g. <br /> instead of <br >.
• Always use PDF format! When html is emailed to gmail, alo, etc, the ticket gets completely destroyed! Try it, its kind of funny to see.
• always test whenever you make a change, to make sure you don't get the 'print at home error', which really could mean anything, so keep track of your changes too.
• use the export utility to backup functional ticket designs (or use the document copy feature)

Date format:
To format dates in the doc designer:

• Day name: ddd
• day number: dd or d for 03 or 3
• Month name: mmm (like Aug , Sep, Nov)
• Month Number: mm
• Year: yy or yyyy for 09 or 2009
• Hour: h or hh for 7 or 07
• minute: mm
• AM / PM: ampm

So for example you want the format
Sun Aug 3 2008 7:00 PM
use
ddd mmm d yyyy h:mm ampm
and enter this in the 'format' field when you have the event date highlighted.

Layering Images and Text:

I could not get text to appear on top of an image. the designer makes it look like you can control what appears on top of what if you have 2 fields overlapping. It appears to save the info, but when the designer is closed and re-opened, it does not save the change. In addition, i did not see a place in the database to store any ordinal information. I tried to get around this using transparent images - but - when printed from firefox, text underneath a transparent image does not show on the printed page.

Concatenating Fields:

You can concatenate fields by using
concat(@FirstName,' ',@LastName)
or concat(@fieldname1,'anytext',@fieldname2)

Barcodes:

I had the barcode width set to 300, the pah barcode width set to 300 as well, but the image still measured in at 200 pixels. If anyone figures out how to get a bigger barcode, kudos and let me know. for now, 200 pixels seems to work fine and scans fine when printed.

PEO: Recommended Events

This feature can be cumbersome, but here are some ideas to make it easier to read:
1:
When adding anything to this list, ALL shows display in alphabetical order. this is dumb.
to fix:
go to System Setup / Column Rules >> find the entry where name = rcshowcode and table = Recommended
Open the record, and change the "Source" to:

SELECT Shows.shCode, CONVERT(varchar(20), MIN(Events.evDateTime), 101) + ' ' + Shows.shName AS shname
FROM Shows INNER JOIN Events ON Shows.shCode = Events.evShowCode
WHERE Events.evDateTime > GETDATE()
GROUP BY Shows.shCode, Shows.shName
ORDER BY min(evdatetime)


Change the SQL SELECT to:

rcShowCode = ISNULL(( SELECT TOP 1 CONVERT(varchar(20), MIN(Events.evDateTime), 101) + ' ' + Shows.shName
FROM Shows INNER JOIN Events ON Shows.shCode = Events.evShowCode
WHERE shCode = rcShowCode GROUP BY Shows.shCode, Shows.shName), 0)

Ok. Now you can use the feature.
Go back to recommended events, and add a new show to a category. it should work. bada bing, easy to use, its the way the world should be.
There is also a 'Site level' type of entry, which governs which events show up on the default 'all events' page, the page the users see when they first get to the site. We like to have the next 7 non-rental events on this page at all times. To manage this, I use a nightly sql job to populate the Site Level events.
Here it is:


CREATE PROCEDURE Kustom_recommended AS
--Thats Kustom with a kapital K

--for testing...
--delete from recommended where rctype = 1
--get show list
SELECT
TOP 7
min(Recommended.rcCategoryCode) as cat,
Recommended.rcShowCode as show
into #ev
FROM Recommended INNER JOIN
Shows ON Recommended.rcShowCode = Shows.shCode INNER JOIN
Category ON Recommended.rcCategoryCode = Category.cgCode inner join
events on events.evshowcode = shows.shcode
WHERE
events.evendsaledate > GETDATE()
and Events.evDateTime > GETDATE()
AND Recommended.rcType = 2 --category level entries
AND Category.cgName NOT LIKE '%rental%'
group by Recommended.rcShowCode
ORDER BY min(Events.evDateTime)

--insert into recommended
declare @next int, @show int, @cat int

declare r cursor for select cat, show from #ev
open r
fetch next from r into @cat, @show
while @@fetch_status = 0
begin
if not exists (select * from recommended where rctype = 1 and rcshowcode = @show )
begin
set @next = (SELECT cnValue + 1 FROM Counters WHERE (cnName = 'NewRecommend'))
update counters set cnvalue = @next, cnlastupdate = getdate() where cnname = 'NewRecommend'
insert into recommended select @next, 1, @cat, @show, getdate()
end

fetch next from r into @cat, @show
end
close r
deallocate r
drop table #ev

--clean up recommended table:
delete from --select * from
recommended
where rcshowcode in (SELECT shCode FROM Shows WHERE shMaxDate < GETDATE())
GO


This also will delete old entries that are no longer needed.
Disclaimer: use at your own risk. Always test before using. i am not responsible for anything that may happen to you. Don't run with scissors.

Re: PE is So Slow - CRM Series Tab Improvement

The Series tab in CRM is really slow, so i took a crack at re-writing the select statement. The idea is to only get aggregate data that is required by the Tablesrules table. (Tables Definitions in administration)
It works great on my test environment, but please test yourself. i need lab rats!
In my testing, I found that it was able to load the series tab in 1 second, where it used to take 60. most accounts used to load in 3 seconds, and now load in .5 seconds.

To Implement, replace the If @Action = 'Season' ... section in the GetClientLists stored Procedure with the following:

IF @Action = 'Season'
begin
set nocount on

if @clientcode > 0
set @clientgroupcode = (select top 1 cltgroupcode from clients with(nolock) where cltcode = @clientcode)

create table #output (ProgramCode int, LastTransact int, TransactDate datetime, TransactNum int, Client_Code int, Client varchar(255),
FullName varchar(255), SeasonDescr varchar(100), SeriesDescr varchar(255), HallDescr varchar(100), AreaDescr varchar (100),
RowDescr varchar(10), ColDescr varchar(10), Season_Code int, Season varchar(100), Series_Code int, Series varchar(255),
SeriesType int, SubsType varchar(255), Hall varchar(255), Area varchar(255), Sector varchar(255), Row varchar(10), Col varchar(10),
Price money, Subsidy money, ChairCode int, AreaCode int, Status_Code int, Status varchar(50), Renewstatus varchar(50),
NumOfEvents int, NumOfBonus int, NumOfOptional int, TicketsLeft int, BonusLeft int, OptionalLeft int, PrintCount int, SelectionCode int,
SerialNum varchar(50), OriginalProgram int, OpeningDate datetime, FreezedStatus_code int, EndDate datetime, LastPrintDate datetime )

insert into #output (Programcode, LastTransact,
TransactDate,TransactNum ,Client_Code ,Client ,Fullname ,SeasonDescr ,Seriesdescr ,Halldescr ,areadescr ,rowdescr ,coldescr ,
season_code,season ,Series_code ,Series ,SeriesType ,SubsType ,Hall ,Area ,Sector ,Row ,Col ,Price ,Subsidy ,
chaircode ,areacode ,status_code ,status ,renewstatus ,NumOfEvents ,NumOfBonus ,NumOfOptional ,Ticketsleft ,BonusLeft, OptionalLeft ,PrintCount ,
SelectionCode, SerialNum ,OriginalProgram ,Openingdate ,FreezedStatus_code ,EndDate, lastprintdate )

select
sp.prorecnum,
case when sp.prolasttransact = 0 then sp.prorecnum else sp.prolasttransact end,
sp.protransactdate,
sp.protransactnum,
prosubsnum,
'', '',
sn.ssndescr,
sr.srdescr,
ha.haname,
'', '', '',
sn.ssncode,
sn.ssndescr,
sr.srcode,
sr.srdescr,
sp.proseriestype,
sp.prosubstype,
ha.haname,
'', '', '', '',
sp.proprice,
sp.prosubsidy,
sp.proseatnum,
sp.proarea,
sp.procurrentstatus,
'' as status ,
convert(varchar(10),prorenewstatus) as renewstatus,
sp.pronumofevents,
sp.probonus,
sp.prooptionalevents,
0,0,0,
sp.proprintnum,
sp.proselectioncode,
sp.proserialnumber,
case when sp.prolasttransact = 0 then sp.prorecnum else sp.prolasttransact end,
protransactdate,
Case proLastTransact
When 0 Then IsNull((Select Top 1 eesStatus From ExtendedEntityStatuses with(nolock) Where eesEntityCode = proRecNum And eesEntityType = 1 And eesStatus < 8), 0)
Else IsNull((Select Top 1 eesStatus From ExtendedEntityStatuses with(nolock) Where eesEntityCode = proLastTransact And eesEntityType = 1 And eesStatus < 8), 0)
End,
case when sp.proenddate = '1/1/1900' then
case when sr.srenddate = '1/1/1900' then sn.ssntodate else sr.srenddate end
else sp.proenddate end ,
'1/1/1900'
from subsprogram sp with(nolock) inner join
serieses sr with(nolock) on sp.proseriescode = sr.srcode inner join
seasons sn with(nolock) on ssncode = srseasons left outer join
halls ha on ha.hacode = sr.srhall
where
@clientgroupcode = sp.procustnum and
sp.proCurrentStatus IN (10, 21, 22, 23, 27, 28, 29)
and (sp.proseasoncode = @seasoncode or @seasoncode = 0)
and (sp.prosubsnum = @clientcode or @viewallingroup = 1 )
and dbo.IsRestriction(@IsAccessControl,@UserGroup,10, sr.srCode,@LockAction, sr.srOrgUnit) = 0
ORDER BY proRecNum DESC

update #output set status_code = case renewstatus when '32' then 36 when '30' then 30 else 34 end where enddate < getdate() and status_code = 10

update #output set status = stsdescription from statustype with(nolock) inner join #output on stsstatus = status_code

update #output set renewstatus = stsdescription from statustype with(nolock) inner join #output on stsstatus = convert(int,renewstatus)
where isnumeric(renewstatus) = 1


declare @trseason table (trcolumn varchar(100))

insert into @trseason
SELECT trColumn FROM TablesRules WHERE trTable = 'SeasonTabFields' AND trDisplay = 1

if exists (SELECT * FROM @trseason WHERE trColumn = 'Client')
update #output set client = cltclientname, fullname = cltclientname
from #output inner join clients on clients.cltcode = #output.client_code

if exists (SELECT * FROM @trseason WHERE trColumn = 'area')
update #output set Areadescr = arname , area = arname
from areas inner join #output on areacode = arcode

if exists (SELECT * FROM @trseason WHERE trColumn in ('col','row','sector' ) )
update #output set col = ahchair, row = ahline, sector = ahshortarname, rowdescr = ahline, coldescr = ahchair
from #output inner join areachair with(nolock) on ahchaircode = chaircode

if exists (SELECT * FROM @trseason WHERE trColumn = 'ticketsleft')
update #output set ticketsleft = numofevents - tix.tixcount
from #output inner join
(select tissubsprogram, count(*) as tixcount from ticketssubscription with(nolock)
where (tisStatus = 1 OR ( tisStatus = 8 AND tisIsTradeIn = 1 ))
AND tisTickType IN (810, 811, 812, 813, 814)
group by tissubsprogram)
as tix on tix.tissubsprogram = originalprogram

if exists (SELECT * FROM @trseason WHERE trColumn = 'bonusleft')
update #output set bonusleft = numofbonus - tix.tixcount
from #output inner join
(select tissubsprogram, count(*) as tixcount from ticketssubscription with(nolock)
where (tisStatus = 1 OR ( tisStatus = 8 AND tisIsTradeIn = 1 ))
AND tisTickType IN (820,821)
group by tissubsprogram)
as tix on tix.tissubsprogram = originalprogram

if exists (SELECT * FROM @trseason WHERE trColumn = 'optionalleft')
update #output set optionalleft = numofoptional - tix.tixcount
from #output inner join
(select tissubsprogram, count(*) as tixcount from ticketssubscription with(nolock)
where (tisStatus = 1 OR ( tisStatus = 8 AND tisIsTradeIn = 1 ))
AND tisTickType IN (830,831)
group by tissubsprogram)
as tix on tix.tissubsprogram = originalprogram

if exists (SELECT * FROM @trseason WHERE trColumn = 'lastprintdate')
update #output set lastprintdate = pr.lastprintdate
from #output inner join
(
Select max(praUpdated) as lastprintdate, pradoccode From printingaudit(NOLOCK)
Where praSourceTable=4 and praStatus<>3
group by pradoccode
) as pr on pr.pradoccode = programcode

select ProgramCode ,LastTransact , TransactDate , TransactNum , Client_Code , Client ,
FullName , SeasonDescr , SeriesDescr , HallDescr , AreaDescr ,
RowDescr , ColDescr , Season_Code , Season , Series_Code , Series ,
SeriesType , SubsType , Hall , Area , Sector , Row , Col ,
Price , Subsidy , ChairCode , AreaCode , Status_Code , Status , Renewstatus ,
NumOfEvents , NumOfBonus , NumOfOptional , TicketsLeft , BonusLeft , OptionalLeft , PrintCount , SelectionCode ,
SerialNum , OriginalProgram , OpeningDate , FreezedStatus_code , EndDate , LastPrintDate
from #output

drop table #output

end

Lets talk about Scanning

Our setup:
Scanners: Symbol mc50 pocket PC's
2 wireless antennae
1 web server (an old dell running server 2000, has PE installed on it),
some customized asp and sql.

An overview of how it works:
Scanner reads barcode, sends it via web form to web-server. The web server processes the asp page and sends the info to a Stored proc on the ticket server.
The stored proc validates it (using also included stored procedures) and returns a result code and message to the web server. The result is included on the scanner web page. the page plays a background noise depending on the result-code (either pass or fail)

A few problems we ran into from the start:
The scanners send the 'enter' key after a scan, but the web page does not submit the form if there is more than 1 form field. I customized the page so that it only has 1 field (barcode), and a submit button - The selection for check and scan is hardcoded to be only scan. Now it works.

Our logic as to who passes and who is denied entry, when it relates to reprinted tickets, differed from Blackbaud's defaults. For this I made custom validation sp's, so that reprints are handled exactly right. To do this, you need to first have a look at barcodes_checkticket and barcodes_checksubsticket to get an idea of how it works. There is a table named scanningconfig that points pe to the sp to do the actual validation. Note that there is one for single tickets and one for subscription. As of the last version (3.3.10), the default logic between the 2 is consistent.

There is another table called Barcodes_ResultMessages, which stores the text for the reurn message. This is probably an easier customization that most people could do. Just don't change the number field ;-)

Merging Accounts Automatically after RE Integration

So, you ran your integration with RE, you have data flying back and forth between systems, you have people ordering tickets online creating duplicate accounts, and you fins that the integration made a couple thousand duplicates as well. now what? You need to merge lots of accounts, but merging is a bit different post integration. There is a "client merge" utility, but if you are like us, you don't want to merge all johnson's to one super-johnson account. You probably have a hundred or so nit-picky criteria that goes into deciding who to merge, which could never be handled by this utility.
So , here is what you do.
The birthday field is a widely unused field on the back-end of PE, and it turns out, you can auto-merge accounts based on birthdate. Also, the birthday field does not transfer to RE in the integration. The cltbirthdate field in the clients table is what you will use to mark your duplicates. Once duplicates are marked, use the client merge widard to merge the duplicate accounts based on birthdate.
Use something similar to the following code to mark the duplicates:

--safety first
--always use test system for testing
--don't run with scissors
--run at your own risk

declare @mbday datetime, @listsize int
set @mbday = '1/1/2100'
set @listsize = 10

--get mergelist . lowest acct number is always kept
SELECT top 5000
case when ClientCode_1 > ClientCode_2 then ClientCode_2 else ClientCode_1 end as keep,
case when ClientCode_1 < ClientCode_2 then ClientCode_2 else ClientCode_1 end as lose
into #m
FROM MyDuplicateListTable
group by
case when ClientCode_1 > ClientCode_2 then ClientCode_2 else ClientCode_1 end ,
case when ClientCode_1 < ClientCode_2 then ClientCode_2 else ClientCode_1 end


--delete loops
delete from #m
where keep in (select lose from #m)

--already merged recs out
delete from #m where
keep not in (select cltcode from clients with(nolock) )
or lose not in (select cltcode from clients with(nolock) )

--exclude more accounts based on your own criteria ...

--#ml = mergelist
select identity(int) as id, keep, lose into #ml from #m group by keep, lose order by keep
drop table #m
delete from #ml where id > @listsize


--now we go to transfer data that may be lost in the merging process
--copy email address to keep account (lower of the 2 numbers...)
update clients
set clients.cltemail = clt2.cltemail
from clients inner join
#ml on #ml.keep = clients.cltcode inner join
clients clt2 on clt2.cltcode = #ml.lose
where clt2.cltemail like '%_@%_.%__'
and (clients.cltemail like '' or clients.cltupdate < clt2.cltupdate)

--copy peologin to keep account
--existing:
update customerlogin
set customerlogin.cluserlogin = cl2.cluserlogin,
customerlogin.clpassword = cl2.clpassword
from #ml inner join
customerlogin on #ml.keep = customerlogin.clcontact inner join
customerlogin cl2 on cl2.clcontact = #ml.lose
where convert(varchar(30),cl2.clcontact) <> cl2.clpassword -- if pw defaults to acct num, do not copy

--new
insert into customerlogin (clcontact, cluserlogin, clpassword, clnextpwdchange)
select #ml.keep, cl.cluserlogin , cl.clpassword , '1/1/3000'
from #ml inner join
customerlogin cl on #ml.lose = cl.clcontact left outer join
customerlogin cl2 on cl2.clcontact = #ml.keep inner join
(select #ml.keep, max(#ml.lose) as maxloser from #ml inner join customerlogin on clcontact = #ml.lose group by #ml.keep)
as maxlose on maxlose.keep = #ml.keep and maxlose.maxloser = #ml.lose
where cl2.clcontact is null

--restrictions: get union list of 'yes' entries, copy list to both accts
select cltcode, cdprulecode
into #r
from
(
select #ml.keep as cltcode, cdprulecode
FROM ClientDataProtection with (nolock) inner join
#ml on #ml.keep = ClientDataProtection.cdpclientcode
where cdpflag = 1
union
select #ml.keep as cltcode, cdprulecode
FROM ClientDataProtection with (nolock) inner join
#ml on #ml.lose = ClientDataProtection.cdpclientcode
where cdpflag = 1
union
select #ml.lose as cltcode, cdprulecode
FROM ClientDataProtection with (nolock) inner join
#ml on #ml.keep = ClientDataProtection.cdpclientcode
where cdpflag = 1
union
select #ml.lose as cltcode, cdprulecode
FROM ClientDataProtection with (nolock) inner join
#ml on #ml.lose = ClientDataProtection.cdpclientcode
where cdpflag = 1
) as bla
group by cltcode, cdprulecode

--transfer status to existing...
update clientdataprotection set cdpflag = 1, cdpupdate = getdate()
from clientdataprotection inner join #r on #r.cltcode = clientdataprotection.cdpclientcode and #r.cdprulecode = clientdataprotection.cdprulecode

--insert records for recs that do not exist
insert into clientdataprotection
select #r.cdprulecode, #r.cltcode, 1, '1/1/1900', '1/1/3000', 1, getdate()
from #r left outer join
clientdataprotection cdp on cdp.cdpclientcode = #r.cltcode and cdp.cdprulecode = #r.cdprulecode
where cdp.cdpclientcode is null

--end merge data preservation section

--reset button for birthday field:
update clients set cltbirthdate = dateadd(ss,cltcode,'1/1/1900') where cltbirthdate <> dateadd(ss,cltcode,'1/1/1900')

--NOW MARK THE DUPLICATES
--mark cltbirthdate for merging. ac1 and ac1 both get same birthday,
--mark ac1
update clients
set cltbirthdate = dateadd(ss, #ml.keep ,@mbday)
from clients inner join #ml on #ml.keep = cltcode
--mark ac2
update clients
set cltbirthdate = dateadd(ss, #ml.keep ,@mbday)
from clients inner join #ml on #ml.lose = cltcode



Printing Setup

I'm bored. Lets talk about printing.

I will explain my printing setup, and maybe it will help yours.

When PE prints a ticket, here is what is going on:

PE locks the ticket, so that no-one else can print during the process
A record is prepared and inserted into a print-spool table in the database.
The tix_printserver.exe application queries this table every x seconds to look for pending tickets.
When it gets a ticket, the tix_printserver.exe sends the ticket to the printer.
If successful, the tix server will mark the tik as printed, and unlock the ticket for printing by others.
The computer that runs the boca printer driver spools the ticket for printing, and then it prints.

So, I have found that it is best to have the tix_printserver running on the database-server for a couple of reasons: 1; Someone has to be logged in for tix-print to run (it can't run as a service), so leave a login open on the db-server for this kind of process (the pc-charge payment server has the same circumstance. I use the same open-login for print servers andpccharge). 2: since the app is querying the db every 5 seconds, it will ease network traffic to have the app running on the db server.

The Boca printers themselves I have hooked up to HP printer hubs. Network connection on one end, paralell on the other. Use the HP jet-admin utility to configure. You assign a tcp/port number to "create" a tcp printing port on the server.

The PE application assigns printers according to sales point. every sp gets a main and alternate printer. There is a user-option to "print to alternate", which will print to the alternate for that salespoint.

If you run into the problem of a botched batch of tickets, you must unlock the printing transaction so someone else can print the tickets. there is a utility in PE admin for this.

Thats all I have for now.

Don't ask me how to print reciepts that you can't print otherwise, cause I won't tell you till next week.

Till next time, happy PEing!

PEO: Print At Home

Anybody out there using the print-at-home feature with PEO?
Do you have any advice for those (like myself) who are about to start using this feature?

PEO Module List

Don't ask how I got this. I will only say that teeth pulling was involved. This is a list of PEO Modules, along with prices. The document had a datestamp of DEC 07. Enjoy ;-)

Recommended Shows
Recommend shows to online patrons based on shows they are browsing or are in their online basket.
$3000


Ticket Discounts


Promotion Codes
For single OR series, not both
Define promotional codes that will discount and event or group of events
$1800

Quantity Discounts

# of Tickets
Provide a discount on tickets when a minimum number of tickets is reached in the online transaction
$2000

# of Events
Provide a discount on tickets when a minimum number of events is reached in the online transaction
$2000

# of Shows
Provide a discount on tickets when a minimum number of shows is reached in the online transaction
$2000



Combination Discounts

Show combo
Provide a discount when a specific number of tickets are purchased to two shows in combination (i.e. 2 tickets for show 1 and 1 ticket for show two results in a discount on tickets for show 2)
$2000

Event Combo
Provide a discount when a specific number of tickets are purchased to two events in combination (i.e. 2 tickets for event 1 and 1 ticket for event 1 results in a discount on show 2)
$2000

Ticket Restrictions

Event based restrictions
Restrict a designated event or events from purchase unless purchased in combination with another event or events
$2000

Show based restrictions
Restrict a designated show or shows from purchase unless purchased in combination with another event or shows
$2000



Member or Subscriber Benefits

$3000
Member/Subscriber pricing (discounts)
Allow access to special pricing to current members or subscribers

Member/Subscriber restrictions
Allows for special presales or “members only” events




Promotional tools for Memberships and Subscriptions


Promotion Codes
Define promotional codes that will discount specific memberships or series
$1800

Restricted memberships or subscriptions
Restrict a designated from purchase unless purchased in combination with another membership/series
$1800

PE: Move Audience

The Move Audience feature is pretty cool. Butt. It has one potentially dangerouse flaw: it keeps NO record of which seats you moved. It does keep an event-level log, listing how many seats were moved per event.

Another draw-back - if you do accounting on events using transaction-date-range as a criteria, move audience will move money from one event to another, effecting the report for past transaction date ranges. This goes against the principles of any good accounting system. Just do not use this for moving tickets between events and you'll be fine.

Ideally, the ticket records would reflect the movement. (insert a status 9, change the old to status 8, and pull the new status 1 ticket)

PEO BUG: User Can't order tickets in more than 1 area for the same show

Lets say that your venue has 2 "areas" : "Main Floor" and "Balcony"

A user gets tickets to an event on the main floor, and presses add-to-cart. He remembers that he has to get an extra ticket for his mother in law, so he looks in the Balcony. He clicks on "continue shopping" and goes back to the seating chart, selects the balcony area, and selects the cheapest seat he could find. He presses add-to-cart, but in the cart-display section, he only sees the 1 balcony seat, and not the premium Main Floor seats he really wanted.
He goes back to the main floor to get the primo seats, and now the cart only displays the Main Floor seats. He takes this as a sign that he should not go to a show with his mother in law.

Blackbaud has acknowledged this bug ... no news yet on which version it will be fixed in.

PE: Is For Mailing check box on CRM Client editing window checked for more than one address.

This problem could effect your reports, if you are depending on the assumption that only one address on an account will have claisformailing = 1. (many of mine do...)
The causes of this include:

PEO Delivery address (see prev. post)
and
1st address had a type that is restricted to the user group that checked the box on another address. - (there might be more... but thats all I got)

The fix I use to take care of these bad bad bits :


--duplicate claisformailing = 1 on same account. nono. peo bad.
update clientaddresses set claisformailing = 0
where claaddresscode in
(
SELECT MAX(claAddressCode)
FROM ClientAddresses
WHERE (claIsForMailing = 1)
GROUP BY claClientCode
HAVING (COUNT(*) > 1)
)


You will need something more involved if you experienced the bug as a result of reason 2.

PEO BUG: Merchandise with different delivery address - address is not visible through PE

When you sell a merchandise item in PEO, and you have the delivery address enabled in the PEO checkout, PEO will record the delivery address in the orders table. The problem: It is not visible when you are in the PE CRM merchandise tab. Ideally, it would behave like single tickets, where you right-click on the item and boom, show me the address. but no.

The address does get recorded in the client record under the address type that you specify, but if they order yet another item that will get overwritten. There is even a bug associated with this - the is for mailing check-box ends up checked for both the billing and delivery addresses. (see next post)

A workaround I have in place is to run a nightly process to extract these addresses and stuff them into remarks. The following code is what I use as a workaround. Note: this writes data to the pe database. I am not responsible for your eagerness to press the green play button.


--insert remarks for online merchandise sales w/ different delivery addr...
--when merch sold on peo, adds order w/ addr, which can't be seen in pe. doh.
--jrh 12/1/07
SELECT
identity(int) as id,
convert(varchar(20),mcdtransactnum) +' - Internet Merch Order. Shipping Addr:' + char(13) + char(10)
+ ordclientname + char(13) + char(10)
+ ordfield1 + + char(13) + char(10)
+ ordfield2 + ', ' + ordfield4 + ' ' + ordfield3 as rmk,
min(ordopendate) as ordopendate,
mcdmailinglist,
mcdclientgroupcode
into #om
FROM
Orders with(nolock) INNER JOIN
Merchandise with(nolock) ON Orders.ordCode = Merchandise.mcdOrder
WHERE
(Orders.ordOrderTypeTitle = 5)
and mcdstatus = 0
and ordopendate > getdate() - 1
group by mcdmailinglist, mcdtransactnum, ordclientname , ordfield1, ordfield2, ordfield3, ordfield4, mcdclientgroupcode

declare @cn int set @cn = (select colastnumber from counters where coname = 'tablecustomerremarks')
update counters set colastnumber = @cn + (select count(*) from #om) where coname = 'tablecustomerremarks'

insert into customerremarks
select id + @cn, mcdclientgroupcode, mcdmailinglist, 0, 42, ordopendate, rmk, 1, 0, 0, '1/1/1900', 1
from #om
drop table #om


PEO: Live Subscription Seating Online

PEO does not currently have this feature.
Let me describe my dream:
A user goes to your web-site, and is able to buy a subscription ticket package online. The customer selects the seat just like they would a single ticket (with all the fancy peo stuff - area maps, view from seat... Thou shalt not leave isolated seats pop-up window). The user adds a couple single tickets to the basket along with a merchandise item and a donation. The user gives you lots of money without you having to talk to them. Brilliant.

If you like the idea, be sure to let blackbaud know. In fact, I am begging you to let them know. This will increase the chances that it will happen soon.
Thanks you for your support.

PE: Back Button in Series Payments Screen does not work

When purchasing a subscription in PE, and you get to the payments screen, you can't go back. If the amount came out to an amount that you did not expect, you have to start all over. This in combination with the commissions resetting during the subscription-selling process makes this bug especially frustrating. (you know, when you add a commission or discount, and then click the events button, the changes dissappear!)
I have been told that this might be fixed in the next version, but it is not out yet. It may come out soon. It may come out later. Woe. Woe is me. I'll be in the server room crying on a pile of cat 5 network cable. You can cry on my blog if you like. There there. Does that feel better?

Bug PEO Subscription Renewals do not work if new series has a GA event.

It turns out that PEO will not renew a series if the renewing-into series has a GA event, and the series is seated. In addition to all the other criteria that the peo renewing function must meet, it has made it unuseable by us...
"The Other Criteria" being: The old and new series must have the same number of events. The new series must have that number of events as it's "maximum num of events". The renewal must have the same price type from season to season. and Discounts are not supported.

Anyone find a fix or workaround for this one?

Deposit Balance Details View

In PE, when you make a payment to a client account, and just post it to that clients account, it makes a deposit. Here is the data structure for deposits:


CREATE VIEW dbo.moa_deposit_balance
AS
SELECT ReceiptDeposit.rcdActionType AS Type, BalanceCategories.bcCategoryName AS CategoryName, ReceiptDeposit.rcdCategory AS Category,
ReceiptDeposit.rcdCode, Clients.cltClientName AS ClientName, ReceiptDeposit.rcdClientCode, ReceiptDeposit.rcdPayAmount AS [Original Amount],
ISNULL(bdDebit.Debits, 0) AS Movement, ReceiptDeposit.rcdPayAmount + ISNULL(bdDebit.Debits, 0) AS Balance,
ReceiptDeposit.rcdTransactNum AS Transact, ReceiptDeposit.rcdReceiptCode AS Receipt, ReceiptDeposit.rcdTransactType,
ReceiptDeposit.rcdTransactSource, ReceiptDeposit.rcdDueDate AS Due
FROM dbo.ReceiptDeposit ReceiptDeposit LEFT OUTER JOIN
dbo.BalanceCategories BalanceCategories ON ReceiptDeposit.rcdCategory = BalanceCategories.bcCode LEFT OUTER JOIN
dbo.Clients Clients ON ReceiptDeposit.rcdClientCode = Clients.cltCode LEFT OUTER JOIN
(SELECT bdReceiptCreditNum, SUM(bdAmount * - 1) AS Debits
FROM MOA_PE.dbo.BalanceDetails BalanceDetails
GROUP BY bdReceiptCreditNum) bdDebit ON ReceiptDeposit.rcdCode = bdDebit.bdReceiptCreditNum
WHERE (ReceiptDeposit.rcdActionType = 0) AND (ReceiptDeposit.rcdStatus = 1) AND (ReceiptDeposit.rcdPayAmount + ISNULL(bdDebit.Debits, 0) <> 0)




Invoice Balance Details View

This view will display Invoices that have a balance due, along with some other info. Merry Xmas PEons.


CREATE VIEW dbo.moa_invoice_balance_details
AS
SELECT ReceiptDeposit.rcdActionType AS Type, ReceiptDeposit.rcdClientCode, ReceiptDeposit.rcdPayAmount * - 1 + ISNULL(bdCredit.Credits, 0) AS Balance,
ReceiptDeposit.rcdTransactNum AS Transact, MAX(ISNULL(BalanceCategories.bcCategoryName, 'Undefined .')) AS Expr1,
ReceiptDeposit.rcdGroupCode, ReceiptDeposit.rcdCategory, users.usCode, ReceiptDeposit.rcdDueDate,
ReceiptDeposit.rcdTransactDate AS entry_date, ReceiptDeposit.rcdCode, ReceiptDeposit.rcdReceiptCode, ReceiptDeposit.rcdShiftNum AS shift,
ISNULL(BalanceCategories.bcCategoryName, 'Undefined .') AS balance_category
FROM MOA_PE.dbo.ReceiptDeposit AS ReceiptDeposit WITH (nolock) LEFT OUTER JOIN
MOA_PE.dbo.BalanceCategories AS BalanceCategories WITH (nolock) ON ReceiptDeposit.rcdCategory = BalanceCategories.bcCode LEFT OUTER JOIN
(SELECT bdReceiptDebitNum, SUM(bdAmount) AS Credits
FROM MOA_PE.dbo.BalanceDetails AS BalanceDetails WITH (nolock)
GROUP BY bdReceiptDebitNum) AS bdCredit ON ReceiptDeposit.rcdCode = bdCredit.bdReceiptDebitNum INNER JOIN
MOA_PE.dbo.Users AS users WITH (nolock) ON users.usRecId = ReceiptDeposit.rcdUser
WHERE (ReceiptDeposit.rcdStatus = 1) AND (ReceiptDeposit.rcdActionType = 1) AND (ReceiptDeposit.rcdPayAmount * - 1 + ISNULL(bdCredit.Credits, 0) <> 0)
GROUP BY ReceiptDeposit.rcdClientCode, ReceiptDeposit.rcdTransactNum, ReceiptDeposit.rcdActionType, ReceiptDeposit.rcdPayAmount, bdCredit.Credits,
ReceiptDeposit.rcdGroupCode, ReceiptDeposit.rcdCategory, users.usCode, ReceiptDeposit.rcdDueDate, ReceiptDeposit.rcdTransactDate,
ReceiptDeposit.rcdCode, ReceiptDeposit.rcdReceiptCode, ReceiptDeposit.rcdShiftNum, ISNULL(BalanceCategories.bcCategoryName, 'Undefined .')


Calculate the actual price of a subscription ticket

PE does not do a good job at telling you the actual ticket value for subscription tickets... The subscription may have a 20% discount, yet the reports would report full value. Use this function as such: select moa_subtik_actual_price(tiscode) from ticketssubscription .... to get the actual value. Enjoy ;-)


CREATE FUNCTION dbo.moa_seriestik_actual_price (@prorecnum int)
RETURNS money AS
--added 11/15/06 jrh
-----------------------------------------------WILL RETURN NEGATIVE VALUE FOR RETURNS-------
--for reports that need live data, and only process a handful of tix

BEGIN

--declare @prorecnum int
--set @prorecnum = 224458
declare @actualprice money
set @actualprice = (
SELECT top 1 (subsprogram.proprice * procach.m) + isnull(discounts.clcsum,0) AS actualprice

FROM (SELECT cc.clcTicketNum, SUM(cc.clcSum * cach.m) AS clcsum
FROM CalculatedCommission cc WITH (NOLOCK) INNER JOIN
Commission WITH (NOLOCK) ON cc.clcCommission = Commission.cmsCode
inner join (select 1 as c, -1 as m union select 0 , 1) as cach on cc.clccachinout = cach.c
WHERE (Commission.cmsCommissionType = 4)
GROUP BY clcticketnum)
discounts right outer JOIN
subsprogram WITH (NOLOCK) ON discounts.clcTicketNum = subsprogram.prorecnum
inner join
(select 10 as s, 1 as m union
select 17, 1 union
select 28, 1 union
select 18, 1 union
select 29, -1 union
select 27, -1 union
select 21, - 1
) as procach on subsprogram.procurrentstatus = procach.s
WHERE (subsprogram.prorecnum = @prorecnum)
)

return isnull(@actualprice,0)

END


Tracing Merged Records in PE from old (merged) account to new account

--this function takes an int input of the possibly no-longer-existing account, and returns the account number (cltcode) of the current account



CREATE FUNCTION dbo.moa_merge_trace (
@old int
) RETURNS int
as begin
declare @new int, @count int
set @count = 0 -- to prevent infinite loops
--begin recursive trace
if exists (select cltcode from clients with(nolock) where cltcode = @old) return @old

while exists (SELECT FromID FROM PATRONEDGE_MERGELOG with(nolock) where FromID = @old and metaobject_id = 713) and @count < 100
begin
set @new = (SELECT ToID FROM PATRONEDGE_MERGELOG with(nolock) where FromID = @old and metaobject_id = 713)
set @old = @new
set @count = @count + 1
end

return @new
end


Welcome to the Patron's Edge Blog.

I work for an organization that uses Patron's Edge. In an effort to connect with other PE users, and to expand the public knowledge base on this product, I created this blog. Have you discovered a bug? Found a fix or workaround to that bug? Have learned that a bug has been fixed in the latest release? Let us know! Enjoy !