• Replies 1 reply
  • Subscribers 337 subscribers
  • Views 203 views
  • Users 0 members are here
Related

mark tickets as attended via sql

Kathleen Smith
$organization

Hi everyone,

 I have a few hundred “tickets” from online events that I need to mark as attended in Tessitura using sql.  Am I correct in thinking that I have to:

  •  Insert a line in t_order_seat_hist (with event code 22)
  • Update tx_perf_seat. vailable_ind to 0 for given perf_no and seat_no
  • Insert a line into t_attendance (with event code 22)

 Physical tickets don’t exist (meaning I can’t n-scan them in), the tickets were fake printed already so the auto-attend functionality doesn’t apply, and  I don’t really want anyone to suffer by typing each ticket_no manually using the Season Manager Record Attendance function.  Or is that ultimately the best option?..

 Thanks!

  • $organization


    If you have NScan you can do the following:

    Declare
    @DoUpdate                bit = 0
    ,@userID                varchar(8) = 'Manual'
    ,@PerfNo                int
    ,@ticketNo                int
    ,@ScanOffSet            int = -15
    ,@ScanTime                datetime
    ,@PriceTypeIDStr        varchar(max) = '137,224'
    ,@PerfStartDate            date = '01/20/22'
    ,@PerfEndDate            datetime = '01/20/22 23:59'
    ,@RecCount                int


    Declare @tblTickets table(
    TicketNo            int
    ,PerfNo                int
    ,PerfDate            datetime
    ,PriceTypeID            int
    primary key (ticketno))

    Declare @tblScanning table  (
        [customer_no] [int] NULL,
        [cust_name] [varchar](55) NULL,
        [perf_no] [int] NULL,
        [perf_code] [varchar](10) NULL,
        [perf_date] [varchar](10) NULL,
        [perf_time] [varchar](8) NULL,
        [perf_name] [varchar](43) NULL,
        [section_desc] [varchar](30) NULL,
        [row] [varchar](5) NULL,
        [seat] [varchar](5) NULL,
        [entrance] [varchar](20) NULL,
        [note] [varchar](255) NULL,
        [urg_ind] [varchar](1) NULL,
        [status] [varchar](50) NULL,
        [activity_no] [int] NULL,
        [issue_action] [int] NULL,
        [issue_closed] [varchar](1) NULL,
        [auto_close] [varchar](1) NULL,
        [ticket_no] [int] ,
        [area_prompt_qty] [varchar](1) NULL,
        [scan_count] [int] NUll,
        [memb_no_str] [varchar](255) NULL,
        [memb_org_str] [varchar](255) NULL,
        [memb_org_name_str] [varchar](255) NULL,
        [memb_level_str] [varchar](255) NULL,
        [admission_adult_str] [varchar](255) NULL,
        [admission_child_str] [varchar](255) NULL,
        [admission_other_str] [varchar](255) NULL,
        [perf_str] [varchar](255) NULL,
        [perf_memb_org_str] [varchar](255) NULL,
        [perf_name_str] [varchar](1000) NULL,
        [perf_attendance_str] [varchar](255) NULL,
        [perf_count_str] [varchar](255) NULL,
        [perf_ticket_str] [varchar](255) NULL,
        [control_id] [int] NULL,
        [is_ga_house] [char](1) NULL
        primary key (Ticket_no)
        )


    insert into @tblTickets
    Select  ticket_no , l.perf_no , perf_dt , price_type  from t_sub_lineitem as L
    inner join t_perf as P on l.perf_no = p.perf_no
    where perf_dt between @PerfStartDate and @PerfEndDate and sli_status = 12
    and not exists (Select 1 from T_ATTENDANCE as A where a.ticket_no = l.ticket_no )
    and price_type in (Select element from dbo.FT_SPLIT_LIST(@PriceTypeIDStr,',') where ISNUMERIC (element) = 1)
    ---additional logic can be applied

    if @DoUpdate = 1
        Begin
            Select @ticketNo = min(TicketNo)  from @tblTickets
            While @ticketNo is not null
                Begin
                    Select @PerfNo = PerfNo , @ScanTime  = dateadd(minute,@ScanOffSet , PerfDate  )
                    from @tblTickets where TicketNo = @ticketNo
                    insert into @tblScanning
                    EXEC [NP_SCAN_EVENTS]
                            @scan_str = @ticketno,
                            @selected_perf_str = @perfno,
                            @user_entrance = NULL,
                            @update = N'Y',  
                            @online = N'N',
                            @exit_mode = N'N',
                            @user_id = @userID ,
                            @update_dt = @ScanTime,
                            @device_name = NULL,
                            @control_id = 0,
                            @suppress_log_updates = N'N'

                    Select @ticketNo = min(TicketNo)  from @tblTickets where TicketNo > @ticketNo
                end


        end


    Select t.*, pt.description, a.id , a.attend_dt, S.* from @tblTickets as T
    inner join TR_price_Type as PT on t.priceTypeID = pt.id
    left join T_ATTENDANCE as A on A.ticket_no = t.TicketNo
    left join @tblScanning as s on t.ticketno = s.Ticket_no

    Here is what the output looks like: