TRY THIS Limit inbound call duration via an IVR linked to mySQL Table

GerryGerry

Member
Joined
Dec 26, 2015
Messages
57
Reaction score
9
Here is my first asterisk 'project' I'm posting it here as I'd like to shre what I've done in the hope that it will help others (if this is the wrong forum let me know). I'd also appreciate it if and 'gurus' would look at what I've done and comment on how they might have achived the same objective. That way everyone learns.


First you need to create and populate a table in a mySQL database, I did this on the test database

here is the code to create the SQL table:
CREATE TABLE IF NOT EXISTS `calltime` (
`id` char(1) NOT NULL,
`seconds` int(4) NOT NULL DEFAULT '0',
`selected` tinyint(4) NOT NULL DEFAULT '0',
UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `calltime`
--

INSERT INTO `calltime` (`id`, `seconds`, `selected`) VALUES
('0', 7200, 0),
('1', 60, 0),
('2', 120, 0),
('3', 180, 0),
('4', 240, 0),
('5', 300, 0),
('6', 360, 1),
('7', 720, 0),
('8', 780, 0),
('9', 840, 0),;
next I added the following functions to the /ect/asterisk/func_odbc.conf file
[LIMITCALLTIME]
dsn=MySQL-test
read=SELECT `seconds` from `calltime` WHERE `selected`=1
write=UPDATE `calltime` SET `selected`=1 WHERE id='${SQL_ESC(${ARG1})}'

[RESETCALLTIME]
dsn=MySQL-test
write=UPDATE `calltime` SET `selected`=0

I then added the following custom context to /ect/asterisk/extensions_custom.conf
[limit-calls]
exten => _X.,1,GotoIf($[${CALLERID(number)}=ZZZZZZZZZZ]?change)
exten => _X.,n,noop(number is ${CALLERID(number)} name is ${CALLERID(name)})
exten => _X.,n,noop(Limiting maximum call duration)
exten => _X.,n,Set(TIMELIMIT=${ODBC_LIMITCALLTIME()})
exten => _X.,n,noop(time retived was ${TIMELIMIT})
exten => _X.,n,Dial(SIP/101,120,S(${TIMELIMIT}))
exten => _X.,n,Hangup()
exten => _X.,n(change),Wait(1)
exten => _X.,n,Authenticate(1111)
exten => _X.,n,Read(newwait,please-enter-the&number, 1)
exten => _X.,n,Set(ODBC_RESETCALLTIME()=1)
exten => _X.,n,Set(ODBC_LIMITCALLTIME(${newwait})=1)
exten => _X.,n,SayDigits(${newwait})
exten => _X.,n,Hangup()

Finally I added a custom destination and called it limit-calls as follows:-
limit-calls,${EXTEN},1
and then created an inbound route and pointed it to this custom destination.

To use,
Just call in from the administrators phone (replace the ZZZZZZZZ in first line of the limit calls context with this telephone number) and enter 1111 when prompted for the password finally dial the number on mins to limit the call or 0 to cancel.

 

wardmundy

Nerd Uno
Joined
Oct 12, 2007
Messages
19,168
Reaction score
5,199
Great idea, @GerryGerry ! Just a few suggestions. There are some apps that are perfectly suited for SQLite3. This is one of them. You can learn all about SQLite3 here.

The advantage is that the database is completely portable (it's just a file) and can be used on any platform, and it's just as easy to set it up with ODBC. I've attached a sample calltime database. Just untar it into /var/lib/asterisk/agi-bin which is always accessible to your Asterisk dialplan. Be sure to make asterisk the owner of the file!

You can add entries into the table like this:

sqlite3 /var/lib/asterisk/agi-bin/calltime.db

Code:
insert into calltime values(1,180,1);
.quit

Finally, make these entries in the following files:

/etc/odbc.ini:

Code:
[SQLITE3-calltime]
Description=SQLite3 calltime database
Driver=SQLITE3
Database=/var/lib/asterisk/agi-bin/calltime.db

/etc/asterisk/res_odbc.conf:

Code:
[SQLITE3-calltime]
enabled => yes
dsn => SQLITE3-calltime
preconnect => yes

/etc/asterisk/func_odbc.conf:

Code:
[LIMITCALLTIME]
dsn=SQLITE3-calltime
read=SELECT seconds FROM calltime WHERE selected='1'
write=UPDATE calltime SET selected=1 WHERE id='${SQL_ESC(${ARG1})}'

Code:
[RESETCALLTIME]
dsn=SQLite3-calltime
write=UPDATE calltime SET selected=0 WHERE id='1'
 

Attachments

  • calltime.tar.gz
    364 bytes · Views: 4

dicko

Still learning but earning
Joined
Oct 30, 2015
Messages
1,607
Reaction score
826
Building on Ward's stuff about sqlite3, you might want to use the built in sqlite3 database (/var/lib/asterisk/astdb.sqlite3) and the inbuilt dialplan functions

http://astbook.asteriskdocs.org/en/2nd_Edition/asterisk-book-html-chunk/asterisk-CHP-6-SECT-6.html

so to set the variable where ${newwait} is the seconds you "read"

exten => _*,n,Set(DB(limitcalltime/${CallerID(num)})=${newwait})

to get it

exten => _*,n,Set(TIMELIMIT=${DB(limitcalltime/${CallerIID(num))})

That way you don't need ODBC or an additional SQL table of any variety.

(I couldn't work out if you wanted to be able to set the limit by CID or just generally, I assumed by CID as there are far easier ways to do it generally)
 
Last edited:

Members online

Forum statistics

Threads
25,782
Messages
167,509
Members
19,202
Latest member
pbxnewguy
Get 3CX - Absolutely Free!

Link up your team and customers Phone System Live Chat Video Conferencing

Hosted or Self-managed. Up to 10 users free forever. No credit card. Try risk free.

3CX
A 3CX Account with that email already exists. You will be redirected to the Customer Portal to sign in or reset your password if you've forgotten it.
Top