Seat Locations in Wordfly Emails

USE [impresario]
GO

/****** Object:  View [dbo].[LV_SDC_ORDER_SUMMARY]    Script Date: 22/05/2021 10:40:02 AM ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO






/*************************************************************************************************

Modified HDW 14/05/2021
This view is to give order details, including seats and part of the house, to WordFly so that 
seating info can be sent out in bulk to patrons as a confirmation.

This is needed as we moved from GA performance to a seated performance after COVID

*************************************************************************************************/


CREATE View [dbo].[LV_SDC_ORDER_SUMMARY]
AS


SELECT 
		distinct e.customer_no,
		d.perf_no,
		f.prod_season_desc as perf_nm,
		f.perf_dt as date,
		c.description as level, 
		seats = STUFF(( SELECT ', ' + seat_row + seat_num
										FROM	[dbo].T_SEAT  B2
										Join [dbo].T_SUB_LINEITEM B3 on B2.seat_no = B3.seat_no
										WHERE B3.li_seq_no = a.li_seq_no
										ORDER BY B2.seat_row,B2.seat_no
										FOR XML	PATH('')
										), 1, 1, ''),
		COUNT(b.seat_num) as num_seat
FROM	[dbo].T_SUB_LINEITEM AS a
		JOIN [dbo].T_SEAT b  ON a.seat_no = b.seat_no
		JOIN [dbo].TX_SMAP_SCREEN c  ON b.smap_no = c.smap_no and b.screen_no = c.screen_no
		JOIN [dbo].T_LINEITEM d ON a.li_seq_no = d.li_seq_no
		JOIN [dbo].T_ORDER e on d.order_no = e.order_no
		JOIN [dbo].VS_ELEMENTS_PERF f on a.perf_no = f.perf_no
Where	
		a.sli_status IN (3,12)
		and ISNULL(a.seat_no, 0) > 0 
		--and d.perf_no = 1151
Group by 
		e.customer_no,
		a.li_seq_no,
		d.perf_no,
		f.prod_season_desc,
		f.perf_dt,
		c.description

--Order by e.customer_no

GO