Series House List

You want to see the seats that have been sold in a series, with a few details. Maybe even see the seats that have been sold on a linked series (sharing a required event). Well, now you can see the series nest all at once in a list format. You will still no be able to see the universe in its entirety unless you look into the tardis, though.

create procedure moa_series_houselist (@s int, @show_overlap bit)
as

/*
declare @s int, @show_overlap bit
set @s = 775
set @show_overlap = 1
*/

create table #srhl (
chaircode int primary key ,
sec varchar(99),
row varchar(99),
seat varchar(99),
account int,
transact int,
pricetype varchar(99),
pricelevel varchar(99),
cltname varchar(99),
reservation_type varchar(99),
series varchar(99),
lock_info varchar(99)
)

declare @areas table (arcode int)

insert #srhl (chaircode,account ,transact ,reservation_type ,series, pricetype, pricelevel )
select proseatnum, prosubsnum, protransactnum, 'Seated', srdescr, sbtdescr, tadescr
from moa_pe..subsprogram inner join
moa_pe..serieses on srcode = proseriescode
inner join moa_pe..subscriptiontype on sbtcode = prosubstype
inner join moa_pe..tariff on tacode = propricelevel
where procurrentstatus = 10 and proseriescode = @s

if @show_overlap = 1
begin
insert @areas select sraarea from moa_pe..seriesareas where sraseries = @s

insert #srhl (chaircode,account ,transact ,reservation_type ,series, pricetype, pricelevel )
select a2.ahchaircode, prosubsnum, protransactnum, 'Seated - other series.', srdescr, sbtdescr, tadescr
from moa_pe..subsprogram inner join
moa_pe..serieses on srcode = proseriescode
inner join moa_pe..subscriptiontype on sbtcode = prosubstype
inner join moa_pe..tariff on tacode = propricelevel
inner join moa_pe..areachair a1 on proseatnum = ahchaircode
inner join moa_pe..areachair a2 on a1.ahparentseatid = a2.ahparentseatid
inner join @areas ar on ar.arcode = a2.ahareacode
where procurrentstatus = 10
and proseriescode in
(select l1.lseseriescode
from moa_pe..linkseriesevents l1
inner join moa_pe..linkseriesevents l2 on l1.lseeventcode = l2.lseeventcode
and l1.lseIsRequiered = 1 and l2.lseIsRequiered = 1
and l2.lseseriescode = @s
and l1.lseseriescode <> @s
)
and a2.ahchaircode not in (select chaircode from #srhl) --should not happen, but exclude anyway.
end

update #srhl set sec = ahshortarname, row = ahline, seat = ahchair
from #srhl inner join moa_pe..areachair on ahchaircode = chaircode

update #srhl set lock_info = isnull(clrdescr,'')
from #srhl
left outer join moa_pe..serieslockedseats on slsseries = @s and slschair = chaircode
left outer join moa_pe..colors on slsticktype = clrstatus

update #srhl set cltname = isnull(cltclientname,'?')
from #srhl left outer join moa_pe..clients on cltcode = account

select #srhl.*, ss.id as sortseatid, sr.id as sortrowid
from #srhl
left outer join sortseat ss on seatname = seat
left outer join sortrow as sr on rowname = row

drop table #srhl