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.