One of our clients has a IVR based bid system which allows callers to place bids for services over the phone. The IVR application validates the identity of the caller, allows them to place a bid, then writes the bid info to a SQL database; however, there’s a restriction that callers can bid once every six hours. If the caller has placed a bid in the previous six hours, their bid is rejected.
So what we need to do is capture the time of the current bid, query SQL for an existing bid in the previous six hours and reject the current bid if a previous bid exists.
I’m not a programmer, so I’ve struggled a little with finding a way in UCCX to subtract six hours from the current time and querying SQL for records after that time. Here’s what I came up with:
- Capture the current date and time in a Date variable named currentBidTime.
- Convert the variable currentBidTime to milliseconds and store it in a Long variable named currentBidTimeMS.
- Subtract 21600000 milliseconds (6 hours) from currentBidTimeMS, store the result in a Long variable named latestAllowedBidTimeMS.
- Convert the variable latestAllowedBidTimeMS to a Date and store in a new Date variable named lastAllowedBidTime.
- Convert the Date variable to a Simple Date Format date, then to a String and store in a String variable named sLatestAllowedBidTime
- Query SQL for records newer than the sLatestAllowedBidTime using a query containing the clause ‘WHERE LAST_BID >= sLatestAllowedBidTime’.
Is this the easiest way to do it? I don’t know, but it seems to work.