SQLServer - Account Balance Enquiry System

Any person who has an account in a bank would like to know his/her account balance status at any time. The customers have got only three options - walk down to the bank to get these details or contact the bank staff through telephone or go for the Internet banking. The first option is not feasible in the present scenario. The second option is not possible all the time as it may increase the workload of the staff and for the third option using Internet Banking facility will require a system with Internet facility which may not be possessed by many of the customers. To simplify, Xtend IVR brings out a faster approach in dealing with the accounts in banks.

Download the evaluation version of Xtend IVR and install the telephony application in your system. Run the sample script from the Script Editor. Click here to refer the code.

The automated attendant will work as given below:

• IVR waits for the call
• Caller dials the number, IVR accepts the call and plays the Welcome message
• IVR asks the caller to enter the account number and PIN number
• Validates the entries in the database
• If the account number is valid, the IVR informs the account balance status to the caller
• If the account number is invalid, IVR plays the invalid message and disconnects the call

Download the source file for the Account Balance Enquiry System using SQLServer

**************************************************************************
* Script: Bank.dt
* Notes : welcomes the user
*         accepts the account number
*         accepts a security number
*         query a MS-SQLSERVER database and retrive the account balance
**************************************************************************

**** Prerequisites ****
// please run the set of queries below to create the table 'Bank'
// You can use the Xtend IVR editor itself to run these queries one by one
;create table Bank (AccNo character(10),Pin character(10),Balance Integer,Name character(50))
;insert into Bank (AccNo,Pin,Balance,Name) values ('1111','1234',25000,'Charly Brown')
;insert into Bank (AccNo,Pin,Balance,Name) values ('2222','2345',32000,'David Thomas')
;insert into Bank (AccNo,Pin,Balance,Name) values ('3333','3456',10000,'Peter Williams')
;insert into Bank (AccNo,Pin,Balance,Name) values ('4444','4567',12000,'James')
;insert into Bank (AccNo,Pin,Balance,Name) values ('5555','5678',32200,'Thomas')
;insert into Bank (AccNo,Pin,Balance,Name) values ('6666','6789',23456,'Alexander')

// ADO Connect string for database
$db = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;
				Initial Catalog=master; Data Source=mssqlserver"

// All inputs are configured for not more than two retry attempts
$retrycount = 2
MAIN:
	display "Waiting for call . . ."
	
	// Pickup the call after the first ring
	answer 1
	
	* Welcome user
	play "Welcome.wav"
	play "silence.wav"
	
ACCEPT_ACCNO:
	; The sample data is given in readme.htm
	display "Accept account number"
	play "silence.wav"
	$retry = $retrycount
	while $retry > 0
		* Ask for account code
		play "PunchAccount.wav"
		$Acno = Input(4,5)
		if $Acno = ""
			* Advice the caller to enable TONE mode
			* in his telephone (Do it only once)
			if $retry = $retrycount
				play "advice.wav"
			endif
		else
			break
		endif
		$retry -= 1
	endwhile
	if $Acno = "" then goto GOODBYE
	
ACCEPT_PIN:
	display "Accept PIN number"
	play "silence.wav"
	$retry = $retrycount
	while $retry > 0
		* Accept PIN
		play "GetPin.wav"
		$Pin = Input(4,5)
		if $Pin <> ""
			break
		endif
		$retry -= 1
	endwhile
	if $Pin == "" then goto GOODBYE	
	
	display "Validating User"
	
	* Validate Account Code
	$sql = format("SELECT * FROM BANK WHERE AccNo = '%s'",$Acno)
	$dbalias = db.RunSQL($db, $sql)
	if $dbalias.eof
		play "InvalidAcno.wav"
		goto GOODBYE
	endif
	
	* Validate PIN
	if compare($dbalias.PIN,$Pin) <> 0
		play "InvalidPin.wav"
		goto GOODBYE
	endif
	
	play "silence.wav"
	play "AccBal.wav"
	
	* Speak out the results to the caller
	$wave = Money2Wav($dbalias.Balance)
	play $wave
	

GOODBYE:
	* Thank you message
	display "Terminating Call"
	play "silence.wav"
	play "goodbye.wav"
	hangup
	goto MAIN
	
* User has hung up
ONHANGUP:
	hangup
	goto MAIN

* An error has occurred
ONSYSTEMERROR:
	log $error
	display $error
	hangup
	goto MAIN

* End of Script