Dynamic call queues in Asterisk

Good afternoon, dear readers of this site.
Today I would like to tell you about the strange way queue management in asterisk.
So.

Given:
1. asterisk1.8.32.3 server
2. sip clients (35 numbers). Two-digit numbering. The first number is 11, the last is 46.
3. Call queues. When you receive a call to a landline number, the menu is pronounced and you are prompted to click on the numbers from 1 to 4. When you click, the call is transferred to the queue 1, 2, 3 or 4, respectively.

What queues.conf looks like now:
[queue-1]
music = default
strategy = linear
timeout = 10
wrapuptime = 0
ringinuse = yes
periodic-announce-frequency = 30
joinempty = no
leavewhenempty = no
announce-position = no
announce-holdtime = no
announce-frequency = 0
member => SIP / 24
member => SIP / 25
member => SIP / 17
member => SIP / 23
member => SIP / 21
member => SIP / 18

[queue-2]
music = default
strategy = linear
timeout = 10
wrapuptime = 0
ringinuse = yes
periodic-announce-frequency = 30
joinempty = no
leavewhenempty = no
announce-position = no
announce-holdtime = no
announce-frequency = 0
member => SIP / 18
member => SIP / 21
member => SIP / 23
')
[queue-3]
music = default
strategy = linear
timeout = 10
wrapuptime = 0
ringinuse = yes
periodic-announce-frequency = 30
joinempty = no
leavewhenempty = no
announce-position = no
announce-holdtime = no
announce-frequency = 0
member => SIP / 37
member => SIP / 35
member => SIP / 31
member => SIP / 33
member => SIP / 34
member => SIP / 32

[queue-4]
music = default
strategy = linear
timeout = 10
wrapuptime = 0
ringinuse = yes
periodic-announce-frequency = 30
joinempty = no
leavewhenempty = no
announce-position = no
announce-holdtime = no
announce-frequency = 0
member => SIP / 24
member => SIP / 25
member => SIP / 37
member => SIP / 18
member => SIP / 32

That is, we see that the queue file is set statically. Numbers are added / removed manually and after each change of the file it is necessary to do queue reload all. That is, you need to go to the asterisk server in the console, say, then asterisk -vvvvvvvvvvvr and type this command.

Task:

Make members add to the queue dynamically. And the composition of the queue would be determined by the contents of the corresponding table in mysql.
I.e. The composition of the queue is determined by mysql.
Accordingly, the data in the mysql table can be entered via the mysql console or through any other muscle access program. For example, I wrote my “program” using LAMP to read and modify the corresponding table with queues.

Decision:

1. Create a table in some kind of mysql database. I will not describe the process of creating a table, just give an example of my worksheet.
mysql> desc queue;
+ ------------ + -------------- + ------ + ----- + -------- - + ---------------- +
| Field | Type | Null | Key | Default | Extra |
+ ------------ + -------------- + ------ + ----- + -------- - + ---------------- +
| id | int (11) | NO | PRI | NULL | auto_increment |
| priznak | int (1) | YES | | NULL | |
| queue | varchar (100) | YES | | NULL | |
| sip | varchar (100) | YES | | NULL | |
| sort_queue | int (2) | YES | | NULL | |
+ ------------ + -------------- + ------ + ----- + -------- - + ---------------- +

and the data of the table itself:
mysql> select * from queue;
+ ---- + --------- + --------- + -------- + ------------ +
| id | priznak | queue | sip | sort_queue |
+ ---- + --------- + --------- + -------- + ------------ +
| 1 | 1 | queue-1 | SIP / 24 | 4 |
| 2 | 1 | queue-1 | SIP / 17 | 2 |
| 3 | 1 | queue-1 | SIP / 23 | 3 |
| 4 | 0 | queue-1 | SIP / 21 | 4 |
| 5 | 1 | queue-1 | SIP / 25 | 1 |
| 6 | 1 | queue-1 | SIP / 18 | 6 |
| 7 | 1 | queue-2 | SIP / 18 | 1 |
| 8 | 0 | queue-2 | SIP / 21 | 2 |
| 9 | 1 | queue-2 | SIP / 23 | 2 |
| 10 | 1 | queue-3 | SIP / 37 | 1 |
| 11 | 1 | queue-3 | SIP / 35 | 2 |
| 12 | 1 | queue-3 | SIP / 31 | 3 |
| 13 | 1 | queue-3 | SIP / 33 | 4 |
| 14 | 1 | queue-3 | SIP / 34 | 5 |

I will explain the table fields.
Id - key.
Priznak - 0/1. Accordingly, if 0, then this number does not participate in the queue
queue - the name of the queue
sip - sip account.
sort_queue is the order in which SIP accounts will participate in dialing. Let's say in the queue queue it is necessary that the SIP / 25 number be the first to call, then SIP / 17, etc.

2. Now we configure asterisk itself to work with queues in this form.
I want to warn you that here I am using a dial plan written in AEL. Mostly because the AEL syntax is close to C ++. And the cycles on AEL are much more convenient to write and they are better read.

Create file extensions.ael
I will not write "specifics" about how an incoming call goes to asterisk and what happens next with it, I will just say that when you click on the 1,2,3 or 4 button, the context described in extensions.ael is called.

Now the contents of the file itself with comments:
push-1 => {
// answer the call
answer ();
// connect to mysql
MYSQL (Connect connid 192.168.1.1 server user passwd);
// in the loop, remove all membes from the queue. Rooms remember from 11 to 46 inclusive.
for (x = 11; $ {x} <= 46; x = $ {x} +1) {
// deletion itself. Team asterisk'a.
RemoveQueueMember (queue-1, SIP / $ {x});
};

// then make a query from the table. We are looking for how many positions will be found in our request. We are looking for how many members have the sign "1", that is, participating in the queue.
MYSQL (Query resultid $ {connid} select count (*) as co from queue where queue = 'queue-1' and priznak = 1 order by sort_queue);
MYSQL (fetch fetchid $ {resultid} co);

// it may happen that there is only one number in the queue at the moment. We cannot know in advance how many numbers will be in the queue and the process of sampling data from the table by an asterisk will be different. That is, if one match is found, then the sample will be described as follows, and if it is somewhat different. Therefore, we check how many matches are found.
// if there are more than one matches, then we make a request (we need a sip field) with the “1” sign and sorting by the “sort_queue” field.
if ($ {co}> 1) {

MYSQL (Query resultid $ {connid} select sip from queue where queue = 'queue-1' and priznak = 1 order by sort_queue);
MYSQL (fetch fetchid $ {resultid} sip);
// add the first found number by the AddQueueMember command.

// for general development. If the values ​​found in the table are more than one, then first we do something with the first (MYSQL (fetch fetchid $ {resultid} sip);), and then all the others in a loop (MYSQL (NextResult resultid $ {connid});
MYSQL (fetch fetchid $ {resultid} sip_next);)

AddQueueMember (queue-1, $ {sip});

// then do a search in the loop for the remaining results and add them to the queue accordingly using the AddQueueMember command
for (x = 1; $ {x} <$ {co}; x = $ {x} +1) {

MYSQL (NextResult resultid $ {connid});
MYSQL (fetch fetchid $ {resultid} sip_next);
AddQueueMember (queue-1, $ {sip_next});
};
}
// if the result is one thing, then we make a request and add the found number to the queue with the command AddQueueMember (queue-1, $ {sip});
else {

MYSQL (Query resultid $ {connid} select sip from queue where queue = 'queue-1' order by sort_queue);
MYSQL (fetch fetchid $ {resultid} sip);
AddQueueMember (queue-1, $ {sip});
};

// then clear the result of the query and disconnect.
MYSQL (Clear $ {resultid});
MYSQL (Disconnect $ {connid});

// actually here we run the queue.
queue (queue-1, Ct ,,, 300);
};

We make changes to the table and the PBX on the next call of the queue considers those numbers that are needed. Let's say that some number would not participate in the telephone call you have to put it a sign "0".

For other queues all the same, only the name of the queue changes.

Look like that's it.

Just want to say that the deletion / addition cycle is completed in 1-2 seconds, which is normal in my case (no delays)

I also tried working with queues using extconfig.conf. This file describes what we can work with through mysql. That is, all the parameters of the queue will not be in the file, but in mysql. For me personally, the file is much easier and easier to read, so I did not use this option.

Now the queues.conf file looks like this:
[queue-1]
music = default
strategy = linear
timeout = 10
wrapuptime = 0
ringinuse = yes
periodic-announce-frequency = 30
joinempty = no
leavewhenempty = no
announce-position = no
announce-holdtime = no
announce-frequency = 0

[queue-2]
music = default
strategy = linear
timeout = 10
wrapuptime = 0
ringinuse = yes
periodic-announce-frequency = 30
joinempty = no
leavewhenempty = no
announce-position = no
announce-holdtime = no
announce-frequency = 0

[queue-3]
music = default
strategy = linear
timeout = 10
wrapuptime = 0
ringinuse = yes
periodic-announce-frequency = 30
joinempty = no
leavewhenempty = no
announce-position = no
announce-holdtime = no
announce-frequency = 0

[queue-4]
music = default
strategy = linear
timeout = 10
wrapuptime = 0
ringinuse = yes
periodic-announce-frequency = 30
joinempty = no
leavewhenempty = no
announce-position = no
announce-holdtime = no
announce-frequency = 0
All settings of the queue itself are stored in a file, and members are added dynamically.

That's all. Thank.