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