Jumpy seat map scrolling in IE9

I just got the IE9 download, and noticed that the seat selection screen on PEO is jumpy when you are trying to scroll through an area. (not as jumpy as Amy here...) It seems that it is limited to IE9 and also limited to this one screen, and also limited to seating areas that have more than, say, 400 seats.

The reason for the jumpyness - the seats are actually little gif images, which are put through an image filter, which will rotate them to the top, left, right, bottom depending on where you say the stage is. When IE9 does this for 1000 little .gifs, it tends to freeze up a bit. firefox, IE8, and Chrome had no problems. The javascript has the following lines;

var img_filter = 'progid:DXImageTransform.Microsoft.BasicImage(rotation=2)';
...


Taking the filter property out of the image tag in seats_plan.asp line 226 fixed it right up, and actually had the page loading faster in all browsers. You may have to rotate all of the little seat images with an image editor so that they point toward the "stage".

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()

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

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


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?