USP_DATALIST_LOCATIONAVAILABILITY
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @LOCATIONSSELECTED | xml | IN | |
| @STARTDATE | date | IN | |
| @ENDDATE | date | IN | |
| @STARTTIME | time | IN | |
| @ENDTIME | time | IN | |
| @AVAIL_UNAVAIL | tinyint | IN |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_LOCATIONAVAILABILITY (
@LOCATIONSSELECTED xml = null,
@STARTDATE date = null,
@ENDDATE date = null,
@STARTTIME time(0) = null,
@ENDTIME time(0) = null,
@AVAIL_UNAVAIL tinyint = 0
)
as
set nocount on;
declare @LOCATIONLIST as table
(
ID uniqueidentifier
);
insert into @LOCATIONLIST
select
T.c.value('(ID)[1]','uniqueidentifier') as 'ID'
from @LOCATIONSSELECTED.nodes('/LOCATIONSSELECTED/ITEM') T(c)
where T.c.value('(SELECTED)[1]','bit')='TRUE';
declare @LOCATIONUSAGE table (
PROGRAM nvarchar(100),
LOCATIONID uniqueidentifier,
LOCATION nvarchar(100),
STARTDATE date,
ENDDATE date,
STARTTIME time,
ENDTIME time,
QUANTITY int,
EVENTCAPACITY int,
LOCATIONCAPACITY int
);
--populate @LOCATIONUSAGE with all booked location information from Sales and Events
with event_cte as (
select
ID,
NAME,
PROGRAMID,
EVENTLOCATIONID,
CAPACITY,
d.[DAY] STARTDATE,
d.[DAY] ENDDATE,
case
when d.[DAY] = STARTDATE then
case STARTTIME
when '' then convert(time(0), '00:00:00')
else convert(time(0), STARTDATETIME)
end
else convert(time(0), '00:00:00')
end as STARTTIME,
case
when d.[DAY] = ENDDATE then
case ENDTIME
when '' then convert(time(0), '23:59:59')
else convert(time(0), ENDDATETIME)
end
else
convert(time(0), '23:59:59')
end as ENDTIME
from dbo.EVENT
inner join
(
select DATEADD(D,NUM, @STARTDATE) as [DAY] from NUMBERS
) as D on D.[DAY] <= ENDDATE and D.[DAY] >= STARTDATE
)
--Sales
insert into @LOCATIONUSAGE (
PROGRAM,
LOCATIONID,
lOCATION,
STARTDATE,
ENDDATE,
STARTTIME,
ENDTIME,
QUANTITY,
EVENTCAPACITY,
LOCATIONCAPACITY
)
select
p.NAME,
el.id,
el.NAME,
e.STARTDATE,
e.ENDDATE,
e.STARTTIME,
e.ENDTIME,
coalesce(sum(ticketSold.QUANTITYSOLD), 0),
max(e.CAPACITY),
max(el.CAPACITY)
from @LOCATIONLIST ll
inner join dbo.EVENTLOCATION el on el.ID = ll.ID
inner join dbo.PROGRAMEVENTLOCATION pel on pel.EVENTLOCATIONID = el.ID
inner join event_cte e on e.ID = pel.EVENTID
inner join dbo.PROGRAM p on p.ID = e.PROGRAMID
left join (
select
e.ID as EVENTID,
sum(si.QUANTITY - coalesce(refunds.QUANTITY, 0)) as QUANTITYSOLD
from dbo.SALESORDERITEM si
inner join dbo.SALESORDERITEMTICKET ticket on si.ID = ticket.ID
inner join dbo.SALESORDER so on si.SALESORDERID = so.ID
inner join dbo.EVENT e on ticket.EVENTID = e.ID
left join (
select
ci.SALESORDERITEMID,
SUM(li.QUANTITY) QUANTITY
from dbo.CREDITITEM_EXT ci
inner join dbo.FINANCIALTRANSACTIONLINEITEM li on li.ID = ci.ID
group by ci.SALESORDERITEMID
) refunds on refunds.SALESORDERITEMID = si.ID
where so.STATUSCODE <> 5
group by e.ID) ticketSold on ticketSold.EVENTID = e.ID
where
(e.STARTDATE >= @STARTDATE or @STARTDATE is null)
and (e.ENDDATE <= @ENDDATE or @ENDDATE is null)
and (e.STARTTIME >= @STARTTIME or @STARTTIME is null)
and (e.ENDTIME <= @ENDTIME or @ENDTIME is null)
group by
p.NAME,
el.id,
el.NAME,
e.STARTDATE,
e.ENDDATE,
e.STARTTIME,
e.ENDTIME
union
--Events
select
e.NAME,
el.ID,
el.NAME,
e.STARTDATE,
e.ENDDATE,
e.STARTTIME,
e.ENDTIME,
count(r.ID) [QUANTITY],
max(e.CAPACITY),
max(el.CAPACITY)
from @LOCATIONLIST ll
inner join dbo.EVENTLOCATION el on el.ID = ll.ID
inner join event_cte e on e.EVENTLOCATIONID = el.ID
left join dbo.REGISTRANT r ON r.EVENTID = e.ID and r.WILLNOTATTEND = 0 and r.ISCANCELLED = 0
where
(e.STARTDATE >= @STARTDATE or @STARTDATE is null)
and (e.ENDDATE <= @ENDDATE or @ENDDATE is null)
and (e.STARTTIME >= @STARTTIME or @STARTTIME is null)
and (e.ENDTIME <= @ENDTIME or @ENDTIME is null)
group by
e.NAME,
el.ID,
el.NAME,
e.STARTDATE,
e.ENDDATE,
e.STARTTIME,
e.ENDTIME;
declare @LOCATIONAVAILABILIY table ( --temp table to hold Available dates and times
[DATE] date,
[LOCATIONID] uniqueidentifier,
[LOCATION] nvarchar(100),
[EVENTCAPACITY] int,
[LOCATIONCAPACITY] int,
[AVAILFROM] time,
[AVAILTO] time
);
declare
@LOCID uniqueidentifier,
@LOC nvarchar(100),--Location
@STARTD date,
@ENDD date,
@STARTT time,
@ENDT time,
@EVENTCAP int, --Capacity
@LOCATIONCAP int,
@AVAILFROM time,
@AVAILTO time,
@NEWDATE bit, --tells if still working on the same date between rows
@LASTENDTIME time, --End time from the previous row
@LASTSTARTDATE date, --Start Date from the previous row
@LASTLOCATIONID uniqueidentifier,--Location from the previous row
@LASTLOCATION nvarchar(100),
@LASTENDDATE date, --End Date from previous row
@LASTEVENTCAP int, --Capacity from previous row
@LASTLOCATIONCAP int,
@NEWLOCATION bit = 1, --Indicates that we have moved to a new location
@WORKINGDATE date --Variable used in While clause;
set @WORKINGDATE = @STARTDATE;
set @NEWDATE = 1;
set @LASTLOCATIONID = null;
set @LASTLOCATION = '';
set @AVAILFROM = '00:00:00';
set @AVAILTO = '23:59:59';
if @AVAIL_UNAVAIL = 0
begin
declare @SELECTEDID uniqueidentifier = null;
--Cursor loops through selected locations
declare crsSELECTED cursor for select ID from @LOCATIONLIST;
open crsSELECTED;
fetch next from crsSELECTED into @SELECTEDID;
while @@FETCH_STATUS = 0
begin
while @WORKINGDATE <= @ENDDATE
begin
if not exists (select top(1) LU.STARTDATE from @LOCATIONUSAGE LU where LU.LOCATIONID = @SELECTEDID and LU.STARTDATE = @WORKINGDATE)
begin
set @AVAILFROM = '00:00:00';
set @AVAILTO = '23:59:59';
insert into @LOCATIONAVAILABILIY (
[DATE],
[LOCATIONID],
[LOCATION],
[EVENTCAPACITY],
[LOCATIONCAPACITY],
[AVAILFROM],
[AVAILTO]
)
select
@WORKINGDATE,
el.ID,
el.NAME,
e.CAPACITY,
el.CAPACITY,
@AVAILFROM,
@AVAILTO
from DBO.EVENTLOCATION el
inner join @LOCATIONLIST ll on el.ID = ll.ID
left join dbo.EVENT e on e.EVENTLOCATIONID = el.ID
end;
else
begin
declare crsLOCAVAIL cursor for
select
LOCATION,
LOCATIONID,
STARTDATE,
ENDDATE,
STARTTIME,
ENDTIME,
EVENTCAPACITY,
LOCATIONCAPACITY
from @LOCATIONUSAGE
where STARTDATE = @WORKINGDATE;
open crsLOCAVAIL;
fetch next from crsLOCAVAIL into @LOC, @LOCID, @STARTD, @ENDD, @STARTT, @ENDT, @EVENTCAP, @LOCATIONCAP;
while @@FETCH_STATUS = 0
begin
--set flag when a new location is encountered
if @LASTLOCATIONID <> @LOCID and @LASTLOCATIONID is not null
begin
set @NEWLOCATION = 1;
end;
else
begin
set @NEWLOCATION = 0;
end;
--set flag for same date different times for the same location
if (@LASTSTARTDATE = @STARTD and @NEWLOCATION = 0) or @LASTSTARTDATE is null
begin
set @NEWDATE = 0;
end;
else
begin
set @NEWDATE = 1;
end;
if (@NEWLOCATION = 1 or @NEWDATE = 1) and @LASTENDDATE is not null
begin
set @LASTLOCATION = (select EL.NAME from DBO.EVENTLOCATION EL where EL.ID = @LASTLOCATIONID);
set @AVAILFROM = @LASTENDTIME;
set @AVAILTO = '23:59:59';
--only insert if location bookings are not back to back
if @AVAILFROM <> @AVAILTO
begin
insert into @LOCATIONAVAILABILIY (
[DATE],
[LOCATIONID],
[LOCATION],
[EVENTCAPACITY],
[LOCATIONCAPACITY],
[AVAILFROM],
[AVAILTO]
)
select
@LASTENDDATE,
@LASTLOCATIONID,
@LASTLOCATION,
@LASTEVENTCAP,
@LASTLOCATIONCAP,
@AVAILFROM,
@AVAILTO;
end;
end;
if @STARTT >= '00:00:00' and @NEWDATE = 1
begin
set @AVAILFROM='00:00:00';
end;
if @LASTLOCATIONID = @LOCID and @STARTD = @LASTSTARTDATE
begin
set @AVAILFROM = @LASTENDTIME;
end;
set @AVAILTO = @STARTT;
set @LASTENDTIME = @ENDT;
set @LASTSTARTDATE = @STARTD;
set @LASTENDDATE = @ENDD;
set @LASTEVENTCAP = @EVENTCAP
set @LASTLOCATIONCAP = @LOCATIONCAP;
--only insert if location bookings are not back to back
if @AVAILFROM <> @AVAILTO
begin
insert into @LOCATIONAVAILABILIY (
[DATE],
[LOCATIONID],
[LOCATION],
[EVENTCAPACITY],
[LOCATIONCAPACITY],
[AVAILFROM],
[AVAILTO]
)
select
@STARTD,
@LOCID,
@LOC,
@EVENTCAP,
@LOCATIONCAP,
@AVAILFROM,
@AVAILTO;
end;
set @LASTLOCATIONID = @LOCID;
fetch next from crsLOCAVAIL into @LOC, @LOCID, @STARTD, @ENDD, @STARTT, @ENDT, @EVENTCAP, @LOCATIONCAP;
if @@FETCH_STATUS = -1
begin
--last row was just processed so need to insert availability from last end time to 11:59
set @LASTLOCATION = (select EL.NAME from DBO.EVENTLOCATION EL where EL.ID = @LASTLOCATIONID);
set @AVAILFROM = @LASTENDTIME;
set @AVAILTO = '23:59:59';
--only insert if location bookings are not back to back
if @AVAILFROM <> @AVAILTO
begin
insert into @LOCATIONAVAILABILIY (
[DATE],
[LOCATIONID],
[LOCATION],
[EVENTCAPACITY],
[LOCATIONCAPACITY],
[AVAILFROM],
[AVAILTO]
)
select
@LASTENDDATE,
@LASTLOCATIONID,
@LASTLOCATION,
@LASTEVENTCAP,
@LASTLOCATIONCAP,
@AVAILFROM,
@AVAILTO;
end;
end;
end;
close crsLOCAVAIL;
deallocate crsLOCAVAIL;
end;
set @WORKINGDATE = DATEADD(day, 1, @WORKINGDATE);
end;
declare @LASTSELECTEDID uniqueidentifier;
set @LASTSELECTEDID = @SELECTEDID;
fetch next from crsSELECTED into @SELECTEDID;
if @LASTSELECTEDID <> @SELECTEDID
begin
set @WORKINGDATE = @STARTDATE;
end;
end;
close crsSELECTED;
deallocate crsSELECTED;
end;
if @AVAIL_UNAVAIL = 0
begin
select
la.LOCATION [LOCATION],
'' [PROGRAMNAME],
la.[DATE] [DATE],
la.AVAILFROM [STARTTIME],
min(la.AVAILTO) [ENDTIME],
0 [USED],
max(la.LOCATIONCAPACITY) [AVAILABLE],
max(la.EVENTCAPACITY) [EVENTCAPACTIY],
max(la.LOCATIONCAPACITY) [LOCATIONCAPACITY]
from @LOCATIONAVAILABILIY la
group by
la.LOCATIONID,
la.LOCATION,
la.[DATE],
la.AVAILFROM
order by
[LOCATION],
[DATE],
[STARTTIME];
end;
else if @AVAIL_UNAVAIL = 1
begin
select
lu.LOCATION [LOCATION],
lu.PROGRAM [PROGRAMNAME],
lu.STARTDATE,
lu.STARTTIME,
lu.ENDTIME,
lu.QUANTITY [USED],
case
when lu.QUANTITY > lu.EVENTCAPACITY then 0
else lu.EVENTCAPACITY - lu.QUANTITY
end [AVAILABLE],
lu.EVENTCAPACITY [EVENTCAPACITY],
lu.LOCATIONCAPACITY [LOCATIONCAPACITY]
from @LOCATIONUSAGE lu
order by
[LOCATION],
[STARTDATE],
[STARTTIME];
end;