Lua Group Pickup example

About

We will try in this example to simulate group pickup as in Asterisk.

How it works

The theory is that you put users in the groups table specifying the group_id they belongs. Then somebody can dial *4 and pickup the first call ringing in the group to which the caller belongs

The active_uuid table is a FIFO queue.

First: Create a sqlite3 DB as:

sqlite3 /opt/freeswitch/db/group_pickup.db
sqlite3> create table active_uuid(time_id bigint primary key, group_id varchar(32), uuid varchar(60));
sqlite3> create table groups(user varchar(32) primary key, group_id varchar(32));
sqlite3> .exit

Then add this extension to default.xml before extension global:

<extension name="intercept-group">
    <condition field="destination_number" expression="^\*4$">
        <action application="answer"/>
        <action application="lua" data="pickup_group_uuid.lua ${callerid_number}"/>
        <action application="intercept" data="${g_uuid}"/>
        <action application="sleep" data="2000"/>
    </condition>
</extension>

Inside '<extension name="global" continue="true">' add:

    <condition>
        <anti-action application="lua" data="add_group_uuid.lua ${uuid} ${dialed_ext}"/>
        <anti-action application="set" data="api_hangup_hook=lua del_group_uuid.lua ${uuid}"/>
        <anti-action application="set" data="execute_on_answer=lua del_group_uuid.lua ${uuid}"/>
    </condition>

Then create these scripts a nd put then in scripts folder.

Script: add_group_uuid.lua

--
-- add_group_uuid.lua
-- This script insert uuid into a FIFO queue to be retrieved on pickup
--

require "luasql.sqlite3";

env = assert (luasql.sqlite3());
con = assert (env:connect("/opt/freeswitch/db/group_pickup.db"));

local uuid = argv[1];
local called = argv[2];

-- retrieve called's group
cur = assert (con:execute(string.format("SELECT group_id from groups where user='%s'", called)));
row = cur:fetch({}, "a");

local group_id = row.group_id;

if (group_id ~= nil) then
    freeswitch.consoleLog("info", "\n--- CALLED PARTY => " .. called .. " , GROUP = " .. group_id .. ", UUID = " .. uuid .. " ---\n")

    -- insert data
    res = assert (con:execute(string.format("INSERT INTO active_uuid VALUES(julianday('now'),'%s','%s')", group_id, uuid)));
end

-- close all
cur:close();
con:close();
env:close();

Script: del_group_uuid.lua

-- del_group_uuid.lua
-- Delete the uuid from the FIFO when the call is answered or abandoned
--

require "luasql.sqlite3";

env = assert (luasql.sqlite3());
con = assert (env:connect("/opt/freeswitch/db/group_pickup.db"));

local uuid = argv[1];

-- delete uuid from table
res = assert (con:execute(string.format("DELETE FROM active_uuid where uuid='%s'", uuid)));

freeswitch.consoleLog("info", "\n--- GROUP PIKCUP UUID: " .. uuid .. " DELETED ---\n")

-- close all
con:close();
env:close();

Script: pickup_group_uuid.lua

-- 
-- pickup_group_uuid.lua
--
-- This script get the first call ringing in the queue db for the group
--

require "luasql.sqlite3";

env = assert (luasql.sqlite3());
con = assert (env:connect("/opt/freeswitch/db/group_pickup.db"));

local caller = session:getVariable("effective_caller_id_number")

-- retrieve called's group
cur = assert (con:execute(string.format("SELECT group_id from groups where user='%s'", caller)));
row = cur:fetch({}, "a");

local group_id = row.group_id;
cur:close();

if (group_id ~= nil) then
    freeswitch.consoleLog("info", "\n--- CALLER PARTY => " .. caller .. " , GROUP = " .. group_id .. " ---\n")
    -- retrieve first waiting uuid from active_uuid for this group_id
    cur = assert (con:execute(string.format("SELECT uuid from active_uuid where group_id = '%s' order by time_id desc limit 1", group_id)));
    row = cur:fetch ({}, "a");
    if (row ~= nil) then
        local uuid = row.uuid;
        session:setVariable("g_uuid", uuid);
        res = assert (con:execute(string.format("DELETE FROM active_uuid where uuid = '%s'", uuid)));
    end
end

-- close all
cur:close();
con:close();
env:close();