Synopsis:

This is an IVR (Interactive Voice Response) scheme that shows how a FacetPhone caller can query a database to check shipping status of an order and the amount of any freight charges using the telephone.  This example uses stock data from the Northwind Trader sample database that comes with Microsoft Access 2000.  The database is stored and maintained on a Windows PC.  A DOS Batch file is run periodically by the Windows Task Scheduler to pull an up-to-date list of order information from the Access database and automatically export it to a text file made available to the phone server.  The IVR script can query the file for order information using an order number provided by the caller and reply with order date, ship date, and amount of any freight charges.

If you would like to provide telephone access to your data, please contact FacetCorp Support to discuss your needs.

Querying Access Database:

I found the information I wanted to provide callers in the Orders table of the sample Northwind Traders database that came with Microsoft Access 2000: Order ID, Order Date, Shipped Date, and Freight.  To pull only the needed pieces of data, I defined a new Query (Objects/Queries/New) and using the Simple Query Wizard, selected Table:Orders and fields: OrderID, OrderDate, ShippedDate, and Freight. I clicked Next to pick Detail query, and then Next to provide the query title: OrderShipInfo.

With the query selected, I defined a new Export Specification (File/Export) to save the text file "NWOrders.txt" on a network drive mapped across the network to a file share on the phone server.  I used FacetWin to map the network drive but any method you choose to make the export file available to the phone server should work fine.  I clicked Save, to bring up the Export Text Wizard where I took the Advanced route to specify Delimited file format with a comma Field Delimiter, MDY Date Order with a slash Date Delimiter, Four Digit Years, and dot for a Decimal Symbol.  Then I clicked Save As to provide the Export Specification Name: OrderShipInfo; then OK and Finish.

I made a new Macro (Objects/Macros/New) to automate exporting data with just two Actions: TransferText, Quit.

TransferText Arguments:  TransferType: Export Delimited; Specification Name: OrderShipInfo; Table Name: Orders; File Name: NWOrders.txt; Has Field Names: No; Code Page: 1252 Western European.

Quit Arguments:  Options: Exit.

I saved the new Macro (File/Save) with Macro Name: OrderShipInfo.

To make it easy to run via the Windows Task Scheduler I made this simple one-liner DOS Batch file, GetShip.bat, which doesn't do much more than run the export macro on the Northwind Access database:

Every time the Windows Task Scheduler runs the DOS batch file, the phone server is provided with up-to-date information about: Order ID, Order Date, Shipped Date, and Freight, in this simple text-file:

"/usr/facetphone/DB/NWOrders.txt": ------------------------------------------------ 10248,7/4/1996 0:00:00,7/16/1996 0:00:00,$32.38 10249,7/5/1996 0:00:00,7/10/1996 0:00:00,$11.61 10250,7/8/1996 0:00:00,7/12/1996 0:00:00,$65.83 10251,7/8/1996 0:00:00,7/15/1996 0:00:00,$41.34 10252,7/9/1996 0:00:00,7/11/1996 0:00:00,$51.30 ------------------------------------------------

IVR Script to Query Data File (overview):

This IVR script includes a support subroutine, play_money(), which takes a money amount like $143.15 and plays it to caller as, "one-hundred, forty-three dollars, fifteen cents".  And a support subroutine, play_date(), which takes a date in the form mm/dd/yyyy like 3/2/1836 and plays it back as, "March two, eighteen thirty-six".

The main subroutine, check_order_menu(), has a pretty straight-forward flow:

        (O)
         |
      L1 |-- Prompt Caller for 5-digit Order Number
         |
         |-- Collect 5 Digits
         |
         |-- Display Order Number on Desktops
         |
         |-- Search Data-file for Order Number
         |
         +-- If Not Found, Tell Caller and Repeat from L1
         |
         |-- Play Order Number
         |
         +-- If No Shipped Date, Tell Caller Not Shipped and Repeat from L1
         |
         |-- Play Shipped Date
         |
         |-- Play Freight Charges
         |
         +-- Repeat from L1 (10 times)

Download Example Script and Associated Files:

     1505 Oct 19 16:14  GetShip.bat            - Export Data Batch File
    40220 Oct 19 15:20  NWOrders.txt           - Comma-delimited Data 
     7575 Oct 19 14:13  check_order_menu.ivr   - IVR Script Source 
    91136 Oct 23 21:09  prompts.cpio           - Prompts used by Script

IVR Script to Query Data File (annotated):

/* * check_order_menu.ivr * * IVR routine to query export of order shipping information from an * MS Access database using the GETSHIP.BAT batch file against the * NorthWind sample database that comes with MS Access 2000. Getting the data out of the MS Access database is just being used as an example.  This script could easily be modified to query any data format or run command-line database queries. * * The GETSHIP.BAT file can be run regularly by the Windows Scheduler * to create the comma-delimited text file queried by this IVR script.  * The text file is expected to be in the FacetPhone "DB" directory * as "NWOrders.txt", for example: * This file must be in place and readable so the IVR process can query it. * /usr/facetphone/DB/NWOrders.txt * * Expected text file format: * * ORDER#,ORDER_DATE,SHIP_DATE,FREIGHT_CHARGE * * For example: * Comma-delimited files are easy to parse!  (e.g. "grep file | cut -d, -f#" ) * 10248,7/4/1996 0:00:00,7/16/1996 0:00:00,$32.38 * 10249,7/5/1996 0:00:00,7/10/1996 0:00:00,$11.61 * 10250,7/8/1996 0:00:00,7/12/1996 0:00:00,$65.83 * * * Requires pre-recorded WAV files: * These prompts are all used by the script.  You must have them in place before the IVR script will work.  Recommended location: /usr/facetphone/ivr/localsystem/vmprompts * check_order_menu.wav : 'Enter your 5-digit order # after * : the beep' * : * order.wav : 'order' * not_found.wav : 'not_found' * placed.wav : 'placed' * shipped_on.wav : 'shipped on' * has_not_been_shipped.wav : 'has not been shipped' * freight_charges_are.wav : 'freight charges are' * dollars.wav : 'dollars' * cents.wav : 'cents' * * * Include in "localsys.ivr": * To be incorporated into the overall IVR system, each IVR script must be included in either "sys.ivr" or "localsys.ivr".  The "sys.ivr" file should be reserved for default set of supplied scripts.  Include custom new scripts in "localsys.ivr" under: /usr/facetphone/ivr/localsystem/ivrscripts * #include <check_order_menu.ivr> * * * Example of calling: * This script could be a menu item in another script (like "company_open_menu") by calling it like this -- in this example, when the caller presses '7'. * int ret; * * if ( ret == E_TT_7 ) * { * ret = check_order_menu(); * continue; * } * * * NOTES: * * See "http://www.facetcorp.com/tnotes" for "Query Order Ship Info" * tech note and "NWOrders.txt" to test with. * * * 10/18/03 Eric Yundt, FacetCorp Tech Support, support@facetcorp.com * ************************************************************************ */ The play_date  subroutine takes a passed in date string, parses out the month, day of month and year, then announces the date.  It shows several examples of running the UNIX "expr" command with date string plus regular expression, then reading back the parsed output. /************************************************************ * * play_date( string date ) * * Expects date string in {M|MM}/{D|DD}/YYYY format. * ************************************************************ */ subroutine play_date( date ) { int ret; int y, y1, y2; string cmd; string out; string month, day, year; out = ""; Command string like this is built:     expr  mm/dd/yyyy  :  "\(.*\)/.*/.*" cmd = "expr " + date + " : \"\(.*\)/.*/.*\""; dbgprt( cmd ); These three builtin functions, Popen, Pread, and Pclose, combine to effectively form what is commonly referred to as a "system" call.  See the "popen" manpage for the general idea:  create a pipe (Popen)  between the IVR process and the command then execute the command; read back (Pread) its output; close down (Pclose)  the command pipe. Popen( cmd ); Pread( out ); Pclose(); Ouput from above "expr" command is 1 or 2-digit month, look up its name. if ( out == "01" || out == "1" ) { month = "january"; } else if ( out == "02" || out == "2" ) { month = "february"; } else if ( out == "03" || out == "3" ) { month = "march"; } else if ( out == "04" || out == "4" ) { month = "april"; } else if ( out == "05" || out == "5" ) { month = "may"; } else if ( out == "06" || out == "6" ) { month = "june"; } else if ( out == "07" || out == "7" ) { month = "july"; } else if ( out == "08" || out == "8" ) { month = "august"; } else if ( out == "09" || out == "9" ) { month = "september"; } else if ( out == "10" ) { month = "october"; } else if ( out == "11" ) { month = "november"; } else if ( out == "12" ) { month = "december"; } Parse out day-of-month with:     expr  mm/dd/yyyy  :  ".*/\(.*\)/.*" cmd = "expr " + date + " : \".*/\(.*\)/.*\""; dbgprt( cmd ); day = ""; Popen( cmd ); Pread( day ); Pclose(); Parse out year with:     expr  mm/dd/yyyy  :  ".*/.*/\(.*\)" cmd = "expr " + date + " : \".*/\(.*\)\""; dbgprt( cmd ); year = ""; Popen( cmd ); Pread( year ); Pclose(); Builtin function, Log_string, puts a message in "facetphone.log". Log_string( month + " " + day + " " + year ); Builtin function, Play_prompt, plays prompt by name and returns any key-press or if entire prompt played without interruption, E_DONE. ret = Play_prompt( month ); if ( ret == E_DONE ) { Last prompt finished playing without interruption, announce "day". Play_string_as_number( day ); } /* PLAY PRETTY YEAR */ Convert "year" string to a number to do arithmetic.  If year is 2000+, play number naturally, for example:  two-thousand three.  If year is before 2000, play century followed by 2-digit remainder, for example:  nineteen ninety-three. dbgprt( year ); y = atoi( year ); dbgprt( y ); if ( y < 2000 && ret == E_DONE ) { y1 = y / 100; dbgprt( y1 ); ret = Play_number( y1 ); if ( ret == E_DONE ) { y2 = y % 100; dbgprt( y2 ); ret = Play_number( y2 ); } } else if ( ret == E_DONE ) { Play_number( y ); } return ( ret ); } The play_money  subroutine takes a passed in money string, parses out the dollars and cents, then announces the amount.  It shows a couple examples of running the UNIX "expr" command with money string plus regular expression, then reading back the parsed output. /************************************************************ * * play_money( string money ) * * Expects money string in "[$]ddd.cc" format. * ************************************************************ */ subroutine play_money( money ) { int ret; string cmd; string dollars, cents; /* GET DOLLARS */ Parse out dollars with:     expr  '$ddd.cc'  :  "\$*\(.*\)\..." cmd = "expr '" + money + "' : \"\$*\(.*\)\...\""; Popen( cmd ); Pread( dollars ); Pclose(); /* GET CENTS */ Parse out cents with:     expr  '$ddd.cc'  :  ".*\.\(..\)" cmd = "expr '" + money + "' : \".*\.\(..\)\""; Popen( cmd ); Pread( cents ); Pclose(); ret = Play_string_as_number( dollars ); if ( ret == E_DONE ) { ret = Play_prompt( "dollars" ); } ret = Play_string_as_number( cents ); if ( ret == E_DONE ) { ret = Play_prompt( "cents" ); } return ( ret ); } The check_order_menu  subroutine is the main entry point into this script.  It could be called directly as an auto-attendant script (i.e. listed in "ivr.local.cfg") or used as a menu item in another script.  In it are examples of collecting digits from caller, grep'ing a text file, and changing the "Callee" field on the desktop interface.  The "Callee" field normally shows the callee's information or the name of a running IVR script -- here we display the Order Number being researched. /******************************************************************** * * check_order_menu() * * Provides telephone access to an exported data file. Callers * are prompted for an order number, then the data is searched * for information about that order. If found, shipping info * (shipped date and freight charges) is announced to caller. * ******************************************************************** */ subroutine check_order_menu() { int ret; int i; int order_id_digits; int loops; string cmd, script_out; string ivr_name; string order_id; string order_date, ship_date; string freight; string datafile; Logging when a subroutine starts makes it easy to locate in the log. Log_string( "Starting script: check_order_menu" ); /* ASSUMES DATAFILE IS HERE */ datafile = "/usr/facetphone/DB/NWOrders.txt"; dbgprt ( datafile ); order_id_digits = 5; loops = 10; Builtin function, Set_ivr_name, sets the "Callee" field on the desktop interface.  In this subroutine we update it with the Order Number caller is researching. order_id = ""; ivr_name = "Checking Order: " + order_id; Set_ivr_name( ivr_name ); Looping a fixed number of times prevents a call from going on forever. for ( i = 0; i < loops; i++ ) { ret = Play_prompt( "check_order_menu" ); If caller hangs up an E_STOP is returned -- exit on hang-up or error. if ( ret == E_STOP || ret == E_ERROR ) { exit ( 0 ); } if ( ret != E_DONE ) { continue; } Builtin function, Collect_digits_fixed_len, is used to accept a fixed number of digits from the caller.  Digits collected are loaded in the global DATA1  variable. ret = Collect_digits_fixed_len( order_id_digits ); if ( ret != 0 ) { return ( ret ); } ret = Play_prompt( "beep" ); if ( ret == E_DONE ) { Builtin function, Wait, can be used as a pause function or to make the IVR process wait a specified number of seconds for an event (like a key-press) to happen.  If the wait timer expires without an event happening it returns E_TIMEOUT. ret = Wait( 8 ); } if ( ret == E_TIMEOUT ) { Builtin function, Stop_collect, is used to force an end to digit collection, in this case because we timed out with no input from caller. Stop_collect(); if ( i == 0 ) { continue; } else { exit ( 0 ); } } The initial wait timer expired.  If caller is entering keys for multi-digit collection then give them more time while Wait  returns E_COLLECTING. while ( ret == E_COLLECTING ) { ret = Wait( 30 ); } Multi-digit collection did not end naturally with E_DIGITS_COLLECTED  by caller entering keys, so force an end to the multi-digit collection operation. if ( ret != E_DIGITS_COLLECTED ) { Stop_collect(); } Collect_digits_fixed_len  put what it collected in DATA1,  grab it and store it. order_id = DATA1; Builtin function, dbgprt, logs a variable value in "facetphone.log". dbgprt( order_id ); /* CONFIRM ORDER # */ /*************************************************** ret = Play_prompt( "you_entered" ); if ( ret == E_DONE ) { ret = Play_number_string( order_id ); } ***************************************************/ /* DISPLAY ORDER # ON DESKTOP INTERFACE */ ivr_name = "Checking Order: " + order_id; Set_ivr_name( ivr_name ); Build up command-line to grep datafile, then run it with Popen.  We search for the Order and parse out Order Date with: grep \^ORDER datafile | cut -d, -f2 | cut -d' ' -f1 /* FIND ORDER_DATE */ cmd = "grep \^" + order_id + " " + datafile; cmd += " | cut -d, -f2"; cmd += " | cut -d' ' -f1"; order_date = ""; Popen( cmd ); Pread( order_date ); Pclose(); /* ORDER ... */ Repeat back Order number ... ret = Play_prompt( "order" ); if ( ret == E_DONE ) { ret = Play_number_string( order_id ); } /* ... NOT FOUND! */ ... if it wasn't found in data file, say so, and loop back to start a new search, if ( ret == E_DONE && order_date == "" ) { ret = Play_prompt( "not_found" ); continue; } /* ... PLACED, ORDER_DATE ... */ else if Order was found, announce the Order Date. if ( ret == E_DONE ) { ret = Play_prompt( "placed" ); } if ( ret == E_DONE ) { ret = play_date( order_date ); } /* FIND SHIP_DATE */ Build up command-line to grep datafile, then run it with Popen.  We search for the Order and parse out Shipped Date with: grep \^ORDER datafile | cut -d, -f3 | cut -d' ' -f1 cmd = "grep \^" + order_id + " " + datafile; cmd += " | cut -d, -f3"; cmd += " | cut -d' ' -f1"; ship_date = ""; Popen( cmd ); Pread( ship_date ); Pclose(); /* ... WAS NOT SHIPPED! */ If no Shipped Date then it hasn't yet, say so, and loop back to start a new search. if ( ret == E_DONE && ship_date == "" ) { ret = Play_prompt( "has_not_been_shipped" ); continue; } /* ... SHIPPED, SHIP_DATE ... */ If we got a Shipped Date then announce it. if ( ret == E_DONE ) { ret = Play_prompt( "shipped_on" ); } if ( ret == E_DONE ) { ret = play_date( ship_date ); } /* FIND FREIGHT CHARGE */ Build up command-line to grep datafile, then run it with Popen.  We search for the Order and parse out Freight Charges with: grep \^ORDER datafile | cut -d, -f4 cmd = "grep \^" + order_id + " " + datafile; cmd += " | cut -d, -f4"; /* cmd += " | tr -d \$"; */ freight = ""; Popen( cmd ); Pread( freight ); Pclose(); Announce Freight Charges with play_money  subroutine. if ( ret == E_DONE ) { ret = Play_prompt( "freight_charges_are" ); } if ( ret == E_DONE ) { ret = play_money( freight ); } } If here because of hang-up or error, quit IVR process, otherwise return to calling script to continue or script processor to hang-up. if ( ret == E_STOP || ret == E_ERROR ) { exit ( 0 ); } Log_string( "Leaving script: check_order_menu" ); return ( ret ); } /***** END OF SCRIPT *****/