Test Environment, fake-paying 5,000 future subscriptions via SQL

Clarke,

This sounds like an interesting predicament to be sure.  I must confess myself curious as to what sort of thing would require fake payment of new orders that cannot also be achieved by using past orders.  Especially if you took past orders and just updated the order dates or something like that to achieve your required situation.

But to your question, unless someone else has already done this before and has specific scripts for you, it sounds like this is going to have to be something you create yourself.  I would suggest opening SQL Profiler, starting a trace, and then paying a couple of orders.  Look at the results of the trace, and see what specific procedures are called and actions taken, and then use that to construct what I can only assume would be a rather large and involved cursor that runs through your series of 5K+ orders.

John

At the Science Museum, we have automated attendance scans to produce an order. I'm sure you could add to it making it a batch insert of sorts. Let me know if you would like more info

I would like to see that! 

cweigle@floridastudiotheatre.org

Clarke, 

Are you creating the orders via rollover, or how are you building them? It would be possible to create orders using the Order Export/Order Import utility, and one of the settings for the Import configuration is whether or not the orders are paid. If they are to be paid, you can specify a single payment method for all of them in the import configuration. Full docs on how to configure an order import can be found at https://www.tessituranetwork.com/Help_System_v151/Content/Order%20Import/Order%20Import%20Utility.htm. 

The orders are created by rollover.

Thanks for that, Troy.  It looks like, in terms of payment, it just marks due_amt & paid_amt as both being 0 in the appropriate tables (as well as setting appeal_no & source_no to be reasonable values in T_ORDER).

Since you asked...

We have a lot of subscribers, with multiple overlapping seasons.  As part of our mission to keep these people happy and loyal, we allow them to exchange dates for free.  Before we tell them what dates they have for upcoming seasons, we do make certain there are no date conflicts.

However, we also have numerous constituents who want to go to shows with particular friends, and a large chunk of subscribers who are only in town for part of the year.  It is a very complicated puzzle, and it is hard on the box office when the tsunami of change requests comes in. 

We are exploring (in Test) how practical it will be to try to respect the “attends with” and the seasonal preferences at the same time we resolve date conflicts.  Tessitura does give us this information without too much fuss, so if we can save later anguish by doing more work before sending dates to our customers, we should do it.  Moreover, if we can encourage people who live here all year to have their subscriptions during the less hectic months, that will also make everyone’s lives better.

The fake payment is just to make the order details in our future subscription season malleable in Test, so we can try the experiment.

Sounds like an interesting time of it for sure, and the idea of being able to unseat-reseat everything makes sense.  And that is certainly a more complicated set up than we have here.  I do love the idea of exploring ways of simplifying exchanges in general, too.  Something like that always seems simpler to the patron than it is to the staff member.

Best of luck!

I know your question says via SQL, but consider that it may ultimately be easier to do this via the API. Pick whatever scripting language you're most comfortable with that knows how to make HTTP requests, figure out the sequence of calls that you need in Postman, and then script out a sequence that loops through those calls for however many orders you need to pay off. If this were me I'd probably reach for Node.js or PowerShell to do the actual scripting.