This is a quickly redacted version of what I have for a working solution for the number dialling sequence above.
First you need to add the odbc connection
; /etc/odbc.ini
[your-custom-db]
Description=MySQL connection for Custom database information
driver=MySQL
server=localhost
database=db_name
Port=3306
Socket=/var/lib/mysql/mysql.sock
option=3
Charset=utf8
Next add that odbc connection to asterisk.
; /etc/asterisk/res_odbc_custom.conf
; Custom Connection for Account Number Database Queries
; https://github.com/asterisk/asterisk/blob/master/configs/samples/res_odbc.conf.sample
[your-custom-db]
;this has to match what is in /etc/odbc.ini
dsn => your-custom-db
; Valid username for the database
username => db_username
; Valid password for the database
password => db_user_password
; The database to use
database=>db_name
; Settings
max_connections=>5
enabled => yes
pre-connect => yes
And then the SQL query to use
; /etc/asterisk/func_odbc.conf
; Return the ID of the accountnumber if it exists
[VALIDATEACCOUNT]
prefix=GET
dsn=your-custom-db
readsql=SELECT uniqueid FROM accounttable WHERE accountnumber = '${ARG1}'
Then add some custom context logic flow to make use of it.
Making use of the trunk-predial-hook.
[macro-dialout-trunk-predial-hook]
exten => s,1,NoOp()
; Skip if this is an emergency call
exten => s,n,GotoIf($["${EMERGENCYROUTE}"=="YES"]?emergencyrouteused)
; Check if the call has already been validated to go out, and skip the rest of the processing
exten => s,n,GotoIf($["${OUTBOUND_ROUTE_NAME}"=="Call_Validated_to_go_Out"]?skipvalidation)
; Depending on the outbound route send the call to be validated
; International calling needs no autcode
exten => s,n,GotoIf($["${OUTBOUND_ROUTE_NAME}"=="International_Allowed"]?skipvalidation)
; NANPA Allowed to dial with no autcode
; Executive Conference room, St Louis extensions, etc.
exten => s,n,GotoIf($["${OUTBOUND_ROUTE_NAME}"=="NANPA_Allowed_No_Code"]?skipvalidation)
; NANPA dialed with a code
exten => s,n,GotoIf($["${OUTBOUND_ROUTE_NAME}"=="NANPA_Dialed_with_Code"]?validatenanpawithautcode,s,1)
; NANPA dialed without a code
exten => s,n,GotoIf($["${OUTBOUND_ROUTE_NAME}"=="NANPA_Dialed_no_Code"]?validatenanpanoautcode,s,1)
;More logic possibly needed. For now, dump channel and terminate call.
exten => s,n,Log(NOTICE, Call from extension ${FROMEXTEN} got someplace it should not be)
exten => s,n,DumpChan
exten => s,n,Hangup()
; The call was an emergency dial, do no processing, but note the log.
exten => s,n(emergencyrouteused),NoOp()
exten => s,n,Log(NOTICE, 911 was dialed by extension ${FROMEXTEN})
; The call was validated, nothing else to do.
exten => s,n(skipvalidation),NoOp()
This bit is unchanged from what it live on the PBX because just too complicated to tweak for your sample.
[validatenanpawithautcode]
exten => s,1,NoOp()
; NANPA format for this PBX means digit zero is (7 or 8 for outbound line)
; and digit one is a 1 while digits two through ten should be a 10 digit number NXXNXXXXXX
; the last five digits are the AUTCODE
exten => s,n,Set(USERDIALED=${OUTNUM:2:10})
exten => s,n,Set(AUTCODE=${OUTNUM:-5})
; Go check for a valid code
exten => s,n,Set(RETURNTOCONTEXT=validatenanpawithautcode)
exten => s,n,GoTo(checkvalidautcode,s,1)
; Return location if AUTCODE is valid
exten => s,n(autcodevalid),NoOp()
; Check if the AUTCODE has a CID associated with it.
exten => s,n,Set(AUTCODECIDNUM=${GET_AUTCODECID(${AUTCODE})})
; If there is no CID jump to autcidno, otherwise autcidyes
exten => s,n,GotoIf($["${AUTCODECIDNUM}" == ""]?autcidno:autcidyes)
exten => s,n,Hangup()
exten => s,n(autcidyes),NoOp()
exten => s,n,Log(NOTICE, The AUTCODE of ${AUTCODE} has an associated CID of ${AUTCODECIDNUM}. Forcing it to apply.)
exten => s,n,Set(TRUNKCIDOVERRIDE=${AUTCODECIDNUM})
exten => s,n(autcidno),NoOp()
exten => s,n,DumpChan
; Go back to outbound route logic with the "secret" prefix of 8675309 (Jenny) that will match the pattern to get to an actual outbound trunk
exten => s,n,Goto(outbound-allroutes,8675309${USERDIALED},1)
exten => s,n,Hangup()
That context calls this one that actually does the SQL lookup.
; This context checks that the AUTCODE value (last five digits) exists in the `autcodes` table
; See func_odbc.conf for the SQL statement.
; This context requires that the variable RETURNTOCONTEXT be set in by the context that calls it
; This context also requres that the calling context have a line labeled autcodevalid to return to
[checkvalidautcode]
exten => s,1,NoOp()
; Start a rety counter.
exten => s,n,Set(RETRYCODE=0)
; If code is invalid, user is prompted to try again and the call is sent back to here
exten => s,n(tryagain),NoOp()
; Check the database for the autcode. SQL query returns the `id` of the row if found
exten => s,n,Set(AUTCODEID=${GET_VALIDATEAUTCODE(${AUTCODE})})
; if no rows are returned jump to badaut, if something is returned jump to goodaut
exten => s,n,GotoIf($[${ODBCROWS} = 0]?badaut,1:goodaut,1)
exten => s,n,Hangup()
exten => badaut,1,NoOp()
exten => badaut,n(notfound),Log(NOTICE, The AUTCODE of ${AUTCODE} was not found, user needs to try again)
; Increment the fail counter
exten => badaut,n,Set(RETRYCODE=$[${RETRYCODE} + 1])
; If the user has failed 3 times (or more somehow) jump to toomanytries
exten => badaut,n,GotoIf($[${RETRYCODE} >= 3]?toomanytries,1)
; Tell them the code was bad and to try again
exten => badaut,n,Playback(you-entered)
exten => badaut,n,Playback(access-code)
exten => badaut,n,SayDigits(${AUTCODE})
exten => badaut,n,Playback(vm-pls-try-again)
exten => badaut,n,Set(TIMEOUT(response)=10)
exten => badaut,n,Read(AUTCODE,access-code,5,,2,5)
; If the entered code was 5 digits jump back up to try again, otherwise jump up to not found
exten => badaut,n,GotoIf($[${LEN(${AUTCODE})}==5]?s,tryagain:notfound)
exten => badaut,n,Hangup()
exten => goodaut,1,NoOp()
exten => goodaut,n,Log(NOTICE, The AUTCODE of ${AUTCODE} was found, let call proceed)
; The code was found, so go back to the context that we came from at the spot marked autcodevalid
exten => goodaut,n,GoTo(${RETURNTOCONTEXT},s,autcodevalid)
exten => goodaut,n,Hangup()
exten => toomanytries,1,NoOp()
exten => toomanytries,n,Log(NOTICE, The user failed to enter a valid AUTCODE too many times)
; The user the dialed the code wrong too many times, hangup the call
exten => toomanytries,n,Playback(sorry)
exten => toomanytries,n,Playback(goodbye)
exten => toomanytries,n,Hangup()