You have serieses to sell, and many of them. you put locks on the series chart to prevent the single events from selling on those seats, so you can sell the subscriptions with all the same seat. You have a bad rep that took the lock on just one of the events in the series, (or you were the victim of the bug (now fixed) in which the series locks did not propogate if seat-info was on the event seat) and now that seat can't be sold for the subscription without allocating one of the seats to a different seat. annoying, right? With a bit of sql you can generate a list of seats that have been 'pirated' in this way:
The following gets a list of locks that should be there, and checks to see if the locks are there, and if not, the seat status. Arr, matey.
--replace moa_pe.. w/ yourdbname..
--the @inc_stage_ext is an option to include or exclude event-lock type pirates
CREATE PROCEDURE moa_series_lock_pirated_seats
(@season int, @inc_stage_ext bit = 0)
AS
/*
--drop table #sl
declare @season int
set @season = 17
declare @inc_stage_ext bit
set @inc_stage_ext = 0
*/
--get all eventt locks that should be there:
create table #sl (s int, e int, c int, ticktype varchar(40), ticktypecode int, lockuser varchar(50), lockdate datetime, evc int)
create index slec on #sl (e,c)
--declare #sl table (s int, e int, c int, ticktype varchar(40), ticktypecode int, lockuser varchar(50), lockdate datetime)
declare @out table (s int, e int, c int, srname varchar(20), evdate datetime, shname varchar(50), lockuser varchar(50),
ticktype varchar(50), pirate varchar(50), acct int, transact int, piratedate datetime, sec varchar(10), row varchar(5), seat varchar(5), reason varchar(50), lockdate datetime )
insert into #sl (s, e, c, ticktype, ticktypecode , evc)
SELECT
SeriesLockedSeats.slsSeries as s,
LinkSeriesEvents.lseEventCode as e,
SeriesLockedSeats.slsChair as c ,
clrdescr,
slsticktype,
ah2.ahchaircode
FROM
moa_pe.dbo.Serieses Serieses with(nolock) INNER JOIN
moa_pe.dbo.SeriesLockedSeats SeriesLockedSeats with(nolock) ON Serieses.srCode = SeriesLockedSeats.slsSeries INNER JOIN
moa_pe.dbo.LinkSeriesEvents LinkSeriesEvents with(nolock) ON SeriesLockedSeats.slsSeries = LinkSeriesEvents.lseSeriesCode inner join
moa_pe..eventsareas with(nolock) on evaevent = lseeventcode inner join
moa_pe..areachair ah1 with(nolock) on ah1.ahchaircode = slschair inner join
moa_pe..areachair ah2 with(nolock) on ah2.ahparentseatid = ah1.ahparentseatid and ah2.ahareacode = evaarea
inner join moa_pe..colors colors with(nolock) on clrstatus = slsticktype inner join
moa_pe..events with(nolock) on evcode = lseeventcode and evhall = srhall
WHERE (Serieses.srSeasons = @season)
--AND (LinkSeriesEvents.lseIsRequiered = 1)
and slsticktype between 2000 and 3000
order by LinkSeriesEvents.lseEventCode , SeriesLockedSeats.slsChair
delete from #sl
from #sl inner join moa_pe..subsprogram with(nolock) on proseatnum = c and proseriescode = s and procurrentstatus = 10 and proseasoncode = @season
update #sl set lockuser = uscode ,lockdate = slhdatelocked
from #sl as sl
inner JOIN
moa_pe..SeatLockHistory SeatLockHistory with(nolock) ON sl.s = SeatLockHistory.slhEventCode AND
sl.c = SeatLockHistory.slhSeatCode AND sl.ticktypecode = SeatLockHistory.slhTickType
and slhlocktype = 3
left outer join
moa_pe..users users with(nolock) on usrecid = slhuserlocked
insert into @out (s,e,c, ticktype, lockuser, piratedate, pirate, acct, transact, reason, lockdate)
select s,e,c, ticktype, lockuser,
tiactualsaleddate,
uscode,
timailinglist,
titransactnum,
'Sold as single ticket',
lockdate
from #sl as sl inner join
moa_pe..tickets t with(nolock) on tievent = sl.e and tichair = sl.evc and tistatus = 1 inner join
moa_pe..shifts with(nolock) on sfcode = tishift and sfactiontype = 0 left outer join
moa_pe..users with(nolock) on usrecid = sfuser
insert into @out (s,e,c, ticktype, lockuser, piratedate, pirate, acct, transact, reason, lockdate)
select s,e,c, ticktype, lockuser,
tisactualsaledate,
uscode,
prosubsnum,
tistransactnum,
'Sold as subscription ticket',
lockdate
from #sl as sl inner join
moa_pe..ticketssubscription t with(nolock) on tisevent = sl.e and tischair = sl.evc and tisstatus = 1 inner join
moa_pe..shifts with(nolock) on sfcode = tisshift and sfactiontype = 0 inner join
moa_pe..subsprogram with(nolock) on case when prolasttransact > 0 then prolasttransact else prorecnum end = tissubsprogram and procurrentstatus = 10 left outer join
moa_pe..users with(nolock) on usrecid = sfuser
insert into @out (s,e,c, ticktype, lockuser, piratedate, pirate, acct, transact, reason, lockdate)
select s,e,c, ticktype, lockuser,
tibtimestamp,
uscode,
0,
0,
'Event Lock-stage ext',
lockdate
from #sl as sl inner join
moa_pe..ticketbase t with(nolock) on tibevent = sl.e and tibchair = sl.evc and tibticktype = 2 left outer join
moa_pe..users with(nolock) on usrecid = tibuser
insert into @out (s,e,c, ticktype, lockuser, piratedate, pirate, acct, transact, reason, lockdate)
select s,e,c, ticktype, lockuser,
tbxactualactiondate,
uscode,
tbxmailcustomer,
tbxtransactnum,
'Sold as Reservation',
lockdate
from #sl as sl inner join
moa_pe..ticketbaseextra t with(nolock) on tbxevent = sl.e and tbxchair = sl.evc and tbxstatus = 1 left outer join
moa_pe..users with(nolock) on usrecid = tbxuser
insert into @out (s,e,c, ticktype, lockuser, piratedate, pirate, acct, transact, reason, lockdate)
select sl.s,sl.e,sl.c, sl.ticktype, sl.lockuser,
'1/1/1900',
isnull(srdescr,''),
0,
0,
'Locked as: ' + clrdescr,
sl.lockdate
from #sl as sl inner join
moa_pe..eventlockedseats els with(nolock) on elsevent = sl.e and elschair = sl.evc and elsticktype <> sl.ticktypecode and elsticktype between 2000 and 3000 left outer join
@out o on sl.c = o.c and sl.e = o.e and sl.s = o.s inner join
moa_pe..colors with(nolock) on clrstatus = elsticktype left outer join
moa_pe..serieses sr with(nolock) on srcode = elsseries
where o.e is null
insert into @out (s,e,c, ticktype, lockuser, piratedate, pirate, acct, transact, reason, lockdate)
select s.s,s.e,s.c, s.ticktype, s.lockuser, '1/1/1900', '', 0, 0, 'Not locked on event', s.lockdate
from #sl s left outer join
@out o on s.c = o.c and s.e = o.e left outer join
moa_pe..eventlockedseats on elsevent = s.e and elschair = s.evc and elsticktype = s.ticktypecode
where elsevent is null and o.e is null
update @out
set
srname = left(srdescr,20),
evdate = eveventdate,
shname = left(shdescr,20),
sec = ahshortarname,
row = ahline,
seat = ahchair
from @out inner join
moa_pe..serieses on srcode = s inner join
moa_pe..areachair on ahchaircode = c inner join
moa_pe..events on evcode = e inner join
moa_pe..shows on shcode = evshow
select * from @out
where (@inc_stage_ext = 1 or reason not like '%stage ext%')
order by srname, sec, row, seat
drop table #sl
GO
No comments:
Post a Comment