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



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)