• Replies 4 replies
  • Subscribers 414 subscribers
  • Views 37 views
  • Users 0 members are here
Related

User define voucher expiry date

Ranie Daw (Past Staff Member)
$organization

Normal 0 false false false EN-AU X-NONE X-NONE MicrosoftInternetExplorer4 We are about to sell drink vouchers on Tess (on line and through our counter) as an add on with  a ticket.

We want to see if we can create a voucher expiry date from the date of the show that is in the cart (i.e show date 3/5 so expiry date for drink voucher 3/8th – 3 months after the show date) BUT if there are more than 1 show date in the cart, (i.e the constituent has purchase 2 x shows) we want it to pull from the eldest show date – the date furthest in the future.

It would obviously require a user defined element to be created but do you think we could create the logic to do that – masking would be needed I assume.

Anyone?

 

 

  • $organization

    Hi Ranie

    You'll want to get one of your SQL bods to update the LP_TICKET_ELEMENTS stored procedure, with something matching the following;

    select @ude_value = dateadd(month,3, max(c.perf_dt))  from t_order a
        join t_sub_lineitem b
            on a.order_no = b.order_no
        join t_perf c
            on b.perf_no = c.perf_no
        where a.order_no = @order_no
            and    a.customer_no = @customer_no

    In the example above me are adding +3 months to the max performance date in the sub lineitem table.

    You can then draw these vaules out on the tickets with some user defined elephants.

    Thanks, Ben

  • Former Member
    Former Member $organization

     

    I’m always forgetting that there can be other sub-lineitems (returns, exchanges, etc) in the order that I should ignore when doing things like this.

     

     -steve

    Information Technology Manager

    The Granada

    (805) 899-3000 x 111 (phone)

    (805) 899-3081 (fax)

     

     

    From: Tessitura Ticketing Forum [mailto:forums-ticketing@tessituranetwork.com] On Behalf Of Ben Magson
    Sent: Thursday, April 15, 2010 8:20 AM
    To: Steve Carlock
    Subject: Re: [Tessitura Ticketing Forum] User define voucher expiry date

     

    Hi Ranie

    You'll want to get one of your SQL bods to update the LP_TICKET_ELEMENTS stored procedure, with something matching the following;

    select @ude_value = dateadd(month,3, max(c.perf_dt))  from t_order a
        join t_sub_lineitem b
            on a.order_no = b.order_no
        join t_perf c
            on b.perf_no = c.perf_no
        where a.order_no = @order_no
            and    a.customer_no = @customer_no

    In the example above me are adding +3 months to the max performance date in the sub lineitem table.

    You can then draw these vaules out on the tickets with some user defined elephants.

    Thanks, Ben

  • Former Member
    Former Member $organization in reply to Former Member

    Hmmm - something happened to my last post.  It should have read:

    Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4

    Friendly suggestion – include a constraint on the sli_status in the select so you don’t get influenced by other things that have been in the order.  Something like:

     

                    where a.order_no = @order_no

                                    and a.customer_no = @customer_no

                                    and b.sli_status in (3, 6, 12) -- seated paid, unseated paid, ticketed paid

     

    I’m always forgetting that there can be other sub-lineitems (returns, exchanges, etc) in the order that I should ignore when doing things like this.

     

    Sorry about that.

     

  • $organization in reply to Ben Magson

    I never knew elephants could be user defined - which zoo has these??

    Cheers - will get the SQL masters to have a look at this

    Thanks