External transfer - flag up calls waiting

[chrissoulier]

Hi folks,

Just to set the scene;
I am running an outbound lead generation campaign. Qualified leads are transferred externally via the 'Transfer Conf' to our client - the transfer number is set in the campaign as a preset.
When we click 'park customer dial' it can sometimes be some time before a transfer agent (at my client's inbound call centre) becomes available.
I have been tasked with writing a query that displays when a live call is being transferred to my client, and currently waiting to be answered by the client. This will underpin a simple html/php page that flashes a horrible shade of red until the call is answered - to be used as a 'there is a call waiting - wallboard' on a computer screen in my client's call centre.

What I have so far is;

Code: Select all

SELECT
cl.caller_code AS `callwaiting`
  FROM asterisk.call_log cl
  INNER JOIN asterisk.recording_log rl   ON (RIGHT(rl.filename,8) = RIGHT(cl.caller_code,8) AND rl.start_time > CURDATE())
     WHERE cl.number_dialed = <<TRANSFER NUMBER>>
     AND cl.end_epoch IS NULL
     AND rl.end_epoch IS NULL

     GROUP BY cl.caller_code
     LIMIT 10

This does most of the job that I want it to, it returns results from the moment the 'Park Customer Dial' button is pressed (e.g. as soon as the dialler initiates the third party call) - and when my agent leaves the 3-way call it returns no results (ending the flashing call waiting sign on the wallboard/display)

I really need to find when the call is answered by my client's transfer agent rather than when we've completed the transfer.

Also this query can take up to three seconds to execute depending on traffic, which is a serious issue as if my client opens more than one instance of the 'wallboard' it completely locks up my slave server and it's goodbye reports until we get them to shut the extra pages!

I need to find a much less resource intensive way to get this information from the dialler. I've combed the asterisk tables for anything that might help, scoured for help topics online and so far come up with nothing so would greatly appreciate any help or guidance!

Many thanks in advance,

Chris

[mflorell]

First suggestion, don't do table joins like that. Grab results from one table and then run another query on another table. Keep the data in whatever program you are using. This is how most of Vicidial works.

Second suggestion, Since you are querying the call_log, you might consider doing nightly archiving of that table. Take a look at the ADMIN_roll_logs script and the --daily flag option.

[chrissoulier]

Thanks for the quick response mflorell.

As I said I'm aware that this is a really poor way of doing it, and I'm reaching out for ideas on how to resolve this with a different approach rather than to optimise the current approach.

I was hoping that there might be a table somewhere that I've missed containing a log of 3way transfers or something along those lines?

[mflorell]

There is a table called vicidial_xfer_log, but not sure it will give you what you are looking for.

How exactly are these transfers performed? (step by step instructions)

[chrissoulier]

The vicidial_xfer_log is only for closer transfers to INGROUPS and not transfers to presets.

Steps are;
1) Agent confirms lead with customer & advises they will transfer
2) Click 'Transfer Conf' to open transfer settings panel
3) Click 'Presets'
4) Select appropriate transfer preset
5) Click 'Park customer dial' (At this stage I want to get a 'CALL WAITING' flag to the wallboard I'm trying to develop)
6) Our call to the preset connects to the destination dialler and places our call in a queue
7) The call is answered by the transfer agent - we do a quick verbal transfer of information to them (It's at this stage I want the 'CALL WAITING' to go to 'CALL ANSWERED')
Click 'grab parked call' to create a 3-way conference
9) Click 'leave 3-way' to leave the client speaking with the transfer agent
10) Select the call outcome/status and resume dialling

The key is to find some sort of call_log style table that populates with information when we make a call to a preset. There is loads of information for calls to 'Leads' but it seems that when we do an external transfer there is next to nothing available, but the data has to be stored somewhere - even if it's in a constantly refreshing table (such as vicidial_live_agents) - I just need to find out where!!!

[mflorell]

You could try matching the vicidial_live_agents table to the vicidial_conferences table. When an agent is in a 3way call, the session_id will match one of the viidial_conferences entries, and when the agent leaves the 3way, that vicidial_conferences entry they were in is changed to have it's leave_3way field set to '1' and the leave_3way_datetime will be set to the current time.