Skip campaign Dial Prefix/Manual Dial Prefix on call_log

[basha04]

Hello ,

Is there any option to skip the campaign Dial Prefix/Manual Dial Prefix when inserting number_dialed on call_log table ?

For example , in the following example 7351 is the Campaign Manual Dial Prefix which I would like to not be inserted on number_dialed column .

number_dialed: 7351xxxxxxx

exten => _7351XX!,1,AGI(agi://127.0.0.1:4577/call_log)
exten => _7351XX!,n,Set(tipo_chiam=PreOutboundVicidial)

I want to remove so I can query using number_dialed = 'xxxxxxxx' and not number_dialed like '%xxxxxxxxxx% ', the queries would be faster because of index usage.
Note for different campaigns there are different Dial Prefix/Manual Dial Prefix.

System version :

VERSION: 2.14-854a
BUILD: 220429-1111
© 2022 ViciDial Group

[mflorell]

We do not recommend using the call_log table because of the possibility of duplicates and inaccurate data. Instead use the vicidial_log table for outbound and the vicidial_closer_log table for inbound calls.

[basha04]

Thanks mflorell for the response .

There are some columns in the call_log table which can't be found on the vicidial_log (as my problem are only the outbound calls).
I hoped there were an built in option without having to modify the scripts .

As per the Inbound calls, agi-DID_route.agi executes before agi-VDAD_ALL_inbound.agi and some calls which might hangup before agi-VDAD_ALL_inbound.agi execution the only place found will be call_log.

[mflorell]

For inbound calls you can also check the vicidial_did_log table.

[martinch]

DID log should do it here's my take though...maybe use a lookup table? Not ideal but yeah...it's an idea

Code: Select all

-- Grab cluster distinct prefixes (outbound ones)
SELECT DISTINCT(dial_prefix) FROM vicidial_campaigns;

-- Generate SQL query
SELECT number_dialed FROM call_log WHERE number_dialed IN ('prefix_1+phone_number','prefix_2+phone_number',''prefix_3+phone_number');


That might be garbage but maybe it might help in some small way. Also DID log is rad...the one Matt suggested

[basha04]

Thanks martinch .

That could work.
I can get the campaign_name , dial_prefix and usit on the query .As per vicidial_did_log it is for Inbound calls only, I do not have problems with Inbound calls. I can manipulate the ${EXTEN}, CALLERID(num) etc on the dialplan , or using agi scripts.

Outbound calls have some issues not only this option that I want , but for connecting vicidial_log , call_log and recording_log . The uniqueid of vicidial_log and call_log and vicidial_id of recording_log for the same call differs from each-other.

I will try with the campaign_name for now and will let you know how it will go after a while.

[basha04]

I had another idea if it will help somebody else.

I created two agi scripts out_call_log_insert.agi and out_call_log_update.agi . The first will insert the data into the call_log table and the second will update end_epoch ,end_time. I didn't mind length_in_sec and length_in_min , those could be calculated . I just made some tests and it seems ok.

out_call_log_insert

Code: Select all

#!/usr/bin/perl

#################################################################################################
#                                                                                               #
# Insert into  call_log for  Outbound    calls                                                  #
#                                                                                               #
#                          Ergest Basha 05.08.2023                                              #
#                                                                                               #
#################################################################################################

use strict;
use warnings;
use DBI;
use Config::IniFiles;
use POSIX qw(strftime);
use sigtrap;
use sigtrap qw(handler my_handler normal-signals stack-trace error-signals);

my $datetime  = strftime ("%Y-%m-%d %H:%M:%S", localtime time) ;

###################################################### DB connection ###########################################################
                   my $cfg = Config::IniFiles -> new( -file => '/etc/pronto.ini' );
                   my $dbinst = $cfg -> val('AST', 'DBINST');
                   my $dbhost = $cfg -> val('AST', 'DBHOST');
                   my $dbuser = $cfg -> val('AST', 'DBUSER');
                   my $dbpass = $cfg -> val('AST', 'DBPASS');
                   
   my $dbhA  = DBI->connect( "dbi:mysql:$dbinst:$dbhost",$dbuser,$dbpass,
                               { RaiseError => 0,
                                 PrintError => 1,
                                 AutoCommit => 0,
                               }
                            ) or die $DBI::errstr;   
###################################################### DB connection ###########################################################

############################### Kalohen te dhenat nga kanali (asterisk) te scripti ####################
(my $empty_field,  my $data ) = @ARGV;

 my @data_array = split /--/, $data;
                 
  my $uniqueid       = $data_array[0];
  my $channel        = $data_array[1];
  my $type           = $data_array[2];
  my $extension      = $data_array[3];
  my $number_dialed  = $data_array[4];
  my $caller_code    = $data_array[5];
  my $start_epoch    = $data_array[6];
########################################### Kalohen te dhenat nga kanali (asterisk) te scripti ##########################################


############################### Kur perdorim strict variablat duhet te deklarohen para se te perdoren #####################
  my $channel_group  = 'OUTBOUND';
  my $server_ip      = '192.168.100.14';
  my $start_time     = $datetime;
############################### Kur perdorim strict variablat duhet te deklarohen para se te perdoren #####################

################################## Inseron te dhena te tabela call_log  ##############

        my  $query_insert_data = "INSERT INTO call_log ( uniqueid      ,
                                                         channel       ,
                                                         channel_group ,
                                                         type          ,
                                                         server_ip     ,
                                                         extension     ,
                                                         number_dialed ,
                                                         caller_code   ,
                                                         start_time    ,
                                                         start_epoch )
                                  VALUES(?,?,?,?,?,?,?,?,?,?)";
       my  $statement_data = $dbhA->prepare($query_insert_data);
           $statement_data->execute( $uniqueid      ,
                                     $channel       ,
                                     $channel_group ,
                                     $type          ,
                                     $server_ip     ,
                                     $extension     ,
                                     $number_dialed ,
                                     $caller_code   ,
                                     $start_time    ,
                                     $start_epoch    );         
           $statement_data->finish();
################################## Inseron te dhena te tabela call_log  ##############

################## Close DB connection ##################
    warn $DBI::errstr if $DBI::err;
         $dbhA->commit();
         $dbhA->disconnect();
################## Close DB connection ##################

out_call_log_update

Code: Select all

#!/usr/bin/perl

#################################################################################################
#                                                                                               #
# Update call_log for  Outbound   calls                                                         #
#                                                                                               #
#                          Ergest Basha 05.08.2023                                              #
#                                                                                               #
#################################################################################################

use strict;
use warnings;
use DBI;
use Config::IniFiles;
use POSIX qw(strftime);
use sigtrap;
use sigtrap qw(handler my_handler normal-signals stack-trace error-signals);

my $datetime  = strftime ("%Y-%m-%d %H:%M:%S", localtime time) ;

###################################################### DB connection ###########################################################
                   my $cfg = Config::IniFiles -> new( -file => '/etc/pronto.ini' );
                   my $dbinst = $cfg -> val('AST', 'DBINST');
                   my $dbhost = $cfg -> val('AST', 'DBHOST');
                   my $dbuser = $cfg -> val('AST', 'DBUSER');
                   my $dbpass = $cfg -> val('AST', 'DBPASS');
                   
   my $dbhA  = DBI->connect( "dbi:mysql:$dbinst:$dbhost",$dbuser,$dbpass,
                               { RaiseError => 0,
                                 PrintError => 1,
                                 AutoCommit => 0,
                               }
                            ) or die $DBI::errstr;   
###################################################### DB connection ###########################################################

############################### Kalohen te dhenat nga kanali (asterisk) te scripti ####################
(my $empty_field,  my $data ) = @ARGV;

 my @data_array = split /--/, $data;
                 
  my $uniqueid       = $data_array[0];
  my $number_dialed  = $data_array[1];
  my $end_epoch      = $data_array[2];

########################################### Kalohen te dhenat nga kanali (asterisk) te scripti ##########################################

################################## update-on tabelen call_log  ne mbyllje te telefonates ##############

        my  $query_insert_data = "update call_log set end_epoch = ? , end_time = ? where uniqueid = ? limit 2";
        my  $statement_data = $dbhA->prepare($query_insert_data);
            $statement_data->bind_param(1,$end_epoch) ;
            $statement_data->bind_param(2,$end_time)  ;
            $statement_data->bind_param(3,$uniqueid)  ;
            $statement_data->execute();
            $statement_data->finish();
################################## update-on tabelen call_log  ne mbyllje te telefonates ##############

################## Close DB connection ##################
    warn $DBI::errstr if $DBI::err;
         $dbhA->commit();
         $dbhA->disconnect();
################## Close DB connection ##################

Then in my outbound extension

Code: Select all

exten => _744441100XX!,1,AGI(it_agi/clean_extension.agi,,${EXTEN:6})
exten => _744441100XX!,n,Set(number_dialed=${ClientNumber})
exten => _744441100XX!,n,AGI(it_agi/it-out-number-translation.agi,,${number_dialed})
exten => _744441100XX!,n,Set(Numero=${numero})
exten => _744441100XX!,n,Set(caller_code=${CALLERID(num)})
exten => _744441100XX!,n,AGI(it_agi/out_call_log_insert.agi,,${UNIQUEID}--${CHANNEL}--${CUT(CHANNEL,/,1)}--${EXTEN}--${number_dialed}--${caller_code}--${EPOCH})
exten => _744441100XX!,n,Set(${CALLERID(name)}=${CALLERID(num)})
exten => _744441100XX!,n,Set(filename=${CALLERID(num)}-${number_dialed}-${STRFTIME(${EPOCH},,%Y%m%d-%H%M%S)})
exten => _744441100XX!,n,Monitor(gsm,/var/spool/asterisk/monitorDONE/GSM/${filename},m)
exten => _744441100XX!,n,Set(tipo_chiam=PreOutboundVicidial)
exten => _744441100XX!,n,AGI(it_agi/pre_data_out_vicidial.agi,,${UNIQUEID}--${caller_code}--${tipo_chiam}--${number_dialed})
exten => _744441100XX!,n,GotoIf($["${linea}" = "bt"]?btonly) 
exten => _744441100XX!,n,GotoIf($["${linea}" = "9"]?albanoonly) 
exten => _744441100XX!,n,Set(CALLERID(num)=39${CALLERID(num)})
exten => _744441100XX!,n,Dial(${trunkvici}/90039${numero},,tTo)
exten => _744441100XX!,n,GotoIf($[$["${DIALSTATUS}" != "ANSWER"] & $["${DIALSTATUS}" != "BUSY"]]?bt:ok) 
exten => _744441100XX!,n(ok),Hangup()
exten => _744441100XX!,n(bt),Set(CALLERID(num)=${CALLERID(num):2})
exten => _744441100XX!,n,NoOp(${DIALSTATUS})
exten => _744441100XX!,n,Dial(${trunkvici}/0039${numero},,tTo)
exten => _744441100XX!,n,Hangup()
exten => _744441100XX!,n(btonly),Dial(${trunkvici}/0039${numero},,tTo)
exten => _744441100XX!,n,Hangup()
exten => _744441100XX!,n(albanoonly),Set(CALLERID(num)=39${CALLERID(num)})
exten => _744441100XX!,n,Dial(${trunkvici}/90039${numero},,tTo)
exten => _744441100XX!,n,Hangup()

Hangup extension

Code: Select all

[it_out_mbyllje_gesti]
exten => h,1,Set(Mbyllje=Outbound Vicidial)
exten => h,n,Set(tipo_chiam=OutboundVicidial)
exten => h,n,AGI(it_agi/out_call_log_update.agi,,${UNIQUEID}--${numero}--${EPOCH}--${HANGUPCAUSE}-----${DIALSTATUS}-----${DIALEDTIME}-----${ANSWEREDTIME}-----${HANGUPCAUSE(${HANGUPCAUSE_KEYS()},tech)})
exten => h,n,AGI(it_agi/data_out_vicidial.agi,,${UNIQUEID}--${DIALSTATUS}--${ANSWEREDTIME}--${tipo_chiam}--${number_dialed}--${caller_code})
exten => h,n,Hangup()

If somebody in the future asked why the hangup exten is on a custom context in extension.conf there is a condition

Code: Select all

exten => h,n,GotoIf($[$["${stato}" = "IT"] & $["${tipo_chiam}" = "PreOutboundVicidial"]]?it_out_mbyllje_gesti,${EXTEN},1:)

[martinch]

Oh hey basha, this looks very good and if it works for you then that's all that matters it's a little convoluted for my brain to get my head around at this point but yeah.

If you want a clean and easy way of updating call_log number_dialed, you can edit Inbound and Outbound AGI and vdc_db_query.php for manuals if you are looking for a super small change. Something along the lines of regexing out outbound prefixes.

It's not dialplan level so the number_dialed will be overwritten but yeah I can provide an example of what I mean if you want. Let me know. Cheers!

[basha04]

Hello Martin ,

My custom scripts are working as expected for the call_log table , but the updates on other realted tables such as vicidial_carrier_log aren't occurring.

I expected if I have the following dialplan

Code: Select all

exten => _744441100XX!,1,AGI(it_agi/clean_extension.agi,,${EXTEN:6})
exten => _744441100XX!,n,Set(number_dialed=${ClientNumber})
exten => _744441100XX!,n,AGI(it_agi/it-out-number-translation.agi,,${number_dialed})
exten => _744441100XX!,n,Set(Numero=${numero})
exten => _744441100XX!,n,Set(caller_code=${CALLERID(num)})
exten => _744441100XX!,n,AGI(it_agi/out_call_log_insert.agi,,${UNIQUEID}--${CHANNEL}--${CUT(CHANNEL,/,1)}--${EXTEN}--${number_dialed}--${caller_code}--${EPOCH})
......
.....

And in the h extension

Code: Select all

exten => h,1,Set(tipo_chiam=InboundZoiper)
exten => h,n,AGI(agi://127.0.0.1:4577/call_log--HVcauses--PRI-----NODEBUG-----${HANGUPCAUSE}-----${DIALSTATUS}-----${DIALEDTIME}-----${ANSWEREDTIME}-----${HANGUPCAUSE(${HANGUPCAUSE_KEYS()},tech)})
..........
...........

the call_log script in the hangup would update my custom insert ,but it wont because as I investigated the uniqueid of the channel isn't the same.

Is there any chance of doing what I am trying to accomplish above ?
As far as I know agi://127.0.0.1:4577/call_log ....are the parameters passed to the /usr/share/astguiclient/FastAGI_log.pl script and my only issue is that here isn't the channel uniqueid passed.
My problem are only the outbound calls Manual or automatic (inserted to the list).

As per

If you want a clean and easy way of updating call_log number_dialed, you can edit Inbound and Outbound AGI and vdc_db_query.php for manuals if you are looking for a super small change. Something along the lines of regexing out outbound prefixes.

It's not dialplan level so the number_dialed will be overwritten but yeah I can provide an example of what I mean if you want

yes please, a suggestion would be nice.

Thanks Martin

[martinch]

Oh hey basha, thanks for the post!

So when you're about to enter that hangup handler, you can be cheeky and process further code on the hangup handler...something like this;

Code: Select all

exten => h,1,AGI(agi://127.0.0.1:4577/call_log--HVcauses--PRI-----NODEBUG-----${HANGUPCAUSE}-----${DIALSTATUS}-----${DIALEDTIME}-----${ANSWEREDTIME}-----${HANGUPCAUSE(${HANGUPCAUSE_KEYS()},tech)})

; Bashas Awesome Hangup Handler
exten => h,2,AGI(bashas_awesome_hangup_handler_script_that_scrubs_prefixes_from_places.pl,param1-----param2-----etc)


Every call would pass through your script and you can do the post-processing you would like (scrubbing of prefixes and stuff. It would cover all calls placed through the server. Dump the AGI variables in there just to see what you have, you should have enough to select the right data in the schema to make your edits. I was proposing doing the processing at the start of the call...but it makes more sense doing it your way at the end of the call. either way, I think if you get it working either way is good.

If you need help spinning up the Perl script, just let me know. You can use Matt and Michael's Perl scripts as a guide...for this piece, just initialise the script up to the DB connection should be enough then you can do stuffs

Hope this helps!

[basha04]

In the end I added the following variables into the dialplan and I pass those variables into the agi script. I didn't use my custom agi scripts described above.

Code: Select all

exten => _7421[1-9]XX!,n,Set(number_dialed=${EXTEN:4})
exten => _7421[1-9]XX!,n,Set(number_dialed_call_log=${EXTEN})

Code: Select all

The query I used are as follows,

########################### vicidial_log ###########################################
       my  $sthD = $dbhA->prepare( "select uniqueid      ,
                                                     lead_id       ,
                            campaign_id   ,
                        call_date     ,
                        length_in_sec ,
                        status        ,
                        phone_number  ,
                        user          ,
                        user_group    ,
                        term_reason                        
                           from vicidial_log
                           where phone_number = ?
                           order by insert_record_dt desc limit 1" );
          $sthD->bind_param( 1, $number_dialed );
          $sthD->execute();     
  my @row = $sthD->fetchrow_array();
      
        my $vl_uniqueid         = $row[0];     
        my $vl_lead_id            = $row[1];     
        my $campaign_id         = $row[2];     
        my $vl_call_date         = $row[3];     
        my $vl_length_in_sec  = $row[4];     
        my $status                  = $row[5];     
        my $phone_number     = $row[6];     
        my $user                     = $row[7];     
        my $user_group           = $row[8];     
        my $vl_term_reason    = $row[9];     
     
          $sthD->finish();
        
my $vl_campaign_id      = $campaign_id   ;
my $vl_status                = $status        ;
my $vl_phone_number   = $phone_number  ;
my $vl_user                   = $user          ;
my $vl_user_group         = $user_group    ;
########################### vicidial_log ###########################################

########################### call_log ###############################################
                       my $sthB = $dbhA->prepare( "select uniqueid      ,
                                                                        extension     ,
                                                                        number_dialed ,
                                                    caller_code   ,
                                                    start_time    ,
                                          end_time      ,
                                          length_in_sec ,
                                          start_epoch
                                      from call_log
                                      where number_dialed = ?
                                      order by insert_record_dt desc limit 1" );
                          $sthB->bind_param( 1, $number_dialed_call_log );
                          $sthB->execute();
                    @row = $sthB->fetchrow_array();
               
                     my  $cl_uniqueid                = $row[0];       
                     my  $cl_extension            = $row[1];
                     my  $cl_number_dialed    = $row[2];
                     my  $cl_caller_code             = $row[3];
                     my  $start_time               = $row[4];
                     my  $cl_end_time       = $row[5];
                     my  $length_in_sec              = $row[6];
                     my  $start_epoch            = $row[7];     
                     
                          $sthB->finish();

                  
my $cl_start_time        = $start_time    ; 
my $cl_length_in_sec   = $length_in_sec ;
my $cl_start_epoch      = $start_epoch   ;
########################### call_log ###############################################

########################### get recording   ########################################
                   my $sthC = $dbhA->prepare( "SELECT recording_id  ,
                                                                     start_time    ,
                                       end_time      ,
                                                                     length_in_sec ,
                                                                     filename      ,
                                                                     location      ,
                                       lead_id       ,
                                       user          ,
                                                                           vicidial_id                              
                                  FROM recording_log
                                  WHERE  filename like ?
                                  ORDER BY recording_id desc LIMIT 1" );
                      $sthC->bind_param( 1, "$number_dialed%" );
                      $sthC->execute(); 
               @row = $sthC->fetchrow_array();
                  
                      $recording_id            = $row[0];
                      $rl_start_time            = $row[1];
                      $rl_end_time            = $row[2];
                      $rl_length_in_sec    = $row[3];
                      $filename               = $row[4];
                      $rl_location       = $row[5];
                      $rl_lead_id       = $row[6];
                      $rl_user               = $row[7];
                      $rl_vicidial_id            = $row[8];
                        
                      $sthC->finish();      

my $rl_recording_id = $recording_id;
   $rl_filename         = $filename;
########################### get recording   ########################################

Tables have the following indexes

Code: Select all

vicidial_log     --- KEY `phone_insdt` (`phone_number`,`insert_record_dt`)
call_log                    --- KEY `nr_ins_dt` (`number_dialed`,`insert_record_dt`)
recording_log           --- KEY `filename` (`filename`)

Indexes are used properly from 8 seconds before the query using call_log table has decreased to 0.5.