How to lock and unlock an extension using a passcode (example of setting/reading/deleting variable in MySQL database)

In the FreePBX forum, user wnpaul (Wolf N. Paul) had a problem - at his school he wanted to be able to lock a particular extension (one that is accessible by the public at times when the school is not in session) so that no outgoing calls could be made, but incoming calls could still be received. He wanted to be able to do this by dialing a passcode to lock the phone, and then the same passcode to unlock the phone. Once the phone was unlocked, he wanted it to stay unlocked until locked again (which is to say, the idea of dialing a PIN or passcode for each outgoing call during times when school was in session was unacceptable).

Another forum participant, fskrotzki, pointed out that it's imperative to include a bypass for 911 or 112 calls (or whatever the local emergency services number may be) so that help can be summoned in an emergency even if no one knows the unlock code.

User GWalmsley then contributed the following code. He wrote:

Here is Graham's original code:

[custom-checklock]
exten => _.,1,NoOp(AMPUSER/${CALLERID(num)}/locked)
exten => _.,n,Gotoif($[${DB_EXISTS(AMPUSER/${CALLERID(num)}/locked)}=0]?carryon)
exten => _.,n,Set(PINCOUNT=0)
exten => _.,n,Set(VALIDPIN=1234)
exten => _.,n(readpin),Read(PIN,vm-password,,,,)
exten => _.,n,Gotoif($[${PIN}=${VALIDPIN}]?carryon)
exten => _.,n,Set(PINCOUNT=$[${PINCOUNT}+1])
exten => _.,n,Playback(vm-incorrect)
exten => _.,n,GotoIf($[${PINCOUNT}>3]?h)
exten => _.,n,Wait(1)
exten => _.,n,Goto(readpin)
exten => _.,n(carryon),NoOp(${DB_DELETE(AMPUSER/${CALLERID(num)}/locked)}) 
exten => _.,n,Goto(from-internal,${EXTEN},1)
exten => 911,1,Goto(from-internal,${EXTEN},1)
exten => h,1,Hangup

[custom-setlock]
exten => s,1,NoOp(In Set Lock)
exten => s,n,Set(DB(AMPUSER/${CALLERID(num)}/locked)=1)
exten => s,n,Playback(vm-goodbye)
exten => s,n,Hangup
exten => h,1,Hangup

Then another forum user, ch.jacobsen, rewrote the above contexts to permit setting the passcode to a different value every time you lock the extension. In addition, VALIDPIN (which is hardcoded into the context) can be used by administrators to unlock the extension:

[custom-checklock]
exten => _.,1,NoOp(AMPUSER/${CALLERID(num)}/locked)
exten => _.,n,Gotoif($[${DB_EXISTS(AMPUSER/${CALLERID(num)}/locked)}=0]?carryon)
exten => _.,n,Set(PINCOUNT=0)
exten => _.,n,Set(VALIDPIN=1234)
exten => _.,n(readpin),Read(PIN,vm-password,,,,)
exten => _.,n,Gotoif($[${PIN}=${VALIDPIN}]?carryon)
exten => _.,n,Gotoif($[${PIN}=${DB(AMPUSER/${CALLERID(num)}/locked)}]?carryon)
exten => _.,n,Set(PINCOUNT=$[${PINCOUNT}+1])
exten => _.,n,Playback(vm-incorrect)
exten => _.,n,GotoIf($[${PINCOUNT}>3]?h)
exten => _.,n,Wait(1)
exten => _.,n,Goto(readpin)
exten => _.,n(carryon),NoOp(${DB_DELETE(AMPUSER/${CALLERID(num)}/locked)})
exten => _.,n,Goto(from-internal,${EXTEN},1)
exten => 911,1,Goto(from-internal,${EXTEN},1)
exten => h,1,Hangup

[custom-setlock]
exten => s,1,NoOp(In Set Lock)
exten => s,n,Read(PIN1,vm-password,,,,)
exten => s,n,Read(PIN2,vm-password,,,,)
exten => s,n,Gotoif($[${PIN1}=${PIN2}]?okey)
exten => s,n,Playback(an-error-has-occured)
exten => s,n,Hangup
exten => s,n(okey),Set(DB(AMPUSER/${CALLERID(num)}/locked)=${PIN1})
exten => s,n,Playback(vm-goodbye)
exten => s,n,Hangup
exten => h,1,Hangup

Both examples above have been slightly edited, for example to add the final "hangup" line in [custom-setlock] to cover the case where someone abandons an attempt to set the lock code. And note that these should be considered untested code. I'd strongly advise temporarily changing the emergency bypass number to a different local extension, entering the code to lock the extension, and then while the extension is locked, try a test call to the emergency number substitute extension. If the other extension rings, be sure to change the number in the code back to 911 or 112 or whatever, after you have finished testing. If the other extension doesn't ring, then your emergency bypass code isn't working and you need to find out why.

What I did NOT edit, but probably should have, are the lines that begin with exten => _.. The _. pattern should never be used unless there is simply no other way to accomplish what you need to do, which is not the case here. I would strongly advise changing all instances of _. to something like _[*#0-9]! (or _[*#0-9]. if using Asterisk 1.2 or earlier).

In case it is not clear from the above, the usage of these contexts is as follows:

Both the [custom-checklock] and the [custom-setlock] contexts must be added to /etc/asterisk/extensions_custom.conf

In [custom-checklock] change the VALIDPIN value to something less obvious, but that administrators can remember.

If necessary, change exten => 911 to exten => 112 (or whatever the local emergency number is). Do NOT omit this line unless you enjoy paying huge sums of money to lawyers, and possibly to the survivors of someone who dies because they couldn't summon emergency help from your locked extension. I'd suggest testing this line to make sure it will work by temporarily changing it to a nearby local extension and testing it as mentioned above, but don't forget to change it back to your local emergency services number when you're finished testing!

Note that you CAN add additional dialable numbers using the same format, in case there are additional numbers you want to make accessible even when the extension is locked (for example, you have a manager's home phone number posted by the phone and you want calls to that number to go through even when the phone is "locked"). But be sure to include a line for each valid way that call can be dialed (think 7/10/11 digit dialing in the NANP region).

Then go to Custom Destinations (under the Tools tab - you may need to install the Custom Destinations module if you have not done so previously) and create a new Custom Destination of custom-setlock,s,1 and give the Custom Destination a descriptive name, such as Lock Extension Using Passcode. Then create a new Misc. Application to specify the Feature Code you wish to use to lock the extension - the destination will be the Custom Destination you just created.

Once you have it working you can change or add prompts as as you like.

The interesting thing about this code is that it shows examples of setting, reading, and deleting a variable in the MySQL database that is keyed to the user of the current extension. This example could be useful in other situations where you may desire to store some bit of information tied to a particular extension, that persists even if Asterisk or the entire server needs to be restarted.

For additional information and discussion see the original thread in the FreePBX forum.