#!/usr/bin/perl
#/* ========================================
# * ███╗   ███╗██████╗ ██╗ █████╗ ██╗
# * ████╗ ████║██╔══██╗██║██╔══██╗██║
# * ██╔████╔██║██║  ██║██║███████║██║
# * ██║╚██╔╝██║██║  ██║██║██╔══██║██║
# * ██║ ╚═╝ ██║██████╔╝██║██║  ██║███████╗
# * ╚═╝     ╚═╝╚═════╝ ╚═╝╚═╝  ╚═╝╚══════╝
# * ========================================
# * mDial - Omni-Channel Contact Centre Suite.
# * Initially Written by Martin McCarthy.
# * Contributions welcome.
# * Active: 2020 - 2023.
# *
# * This software is licensed under AGPLv2.
# * You can find more information here;
# * https://www.gnu.org/licenses/agpl-3.0.en.html
# * A copy of the license is also shipped with this build.
# *
# * Important note: this software is provided to you free of charge.
# * If you paid for this software, you were ripped off.
# *
# * This project is a fork of the awesome FOSS project, ViCiDial.
# * ViCiDial is copyrighted by Matt Florell and the ViCiDial Group
# * under the AGPLv2 license.
# *
# * You can find out more about ViCiDial;
# * Web: https://www.vicidial.com/
# * Email: Matt Florell <vicidial@gmail.com>
# * IRC: Libera.Chat - ##vicidial
# *
# * Bug reports, feature requests and patches welcome!
# * ======================================== */
$script = 'agi-VDAD_inbound_calltime_check.agi';
( $sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $isdst ) =
  localtime(time);
$year = ( $year + 1900 );
$mon++;
if ( $mon < 10 )  { $mon  = "0$mon"; }
if ( $mday < 10 ) { $mday = "0$mday"; }
if ( $hour < 10 ) { $hour = "0$hour"; }
if ( $min < 10 )  { $min  = "0$min"; }
if ( $sec < 10 )  { $sec  = "0$sec"; }
$hm             = "$hour$min";
$hm             = ( $hm + 0 );
$now_date_epoch = time();
$now_date       = "$year-$mon-$mday $hour:$min:$sec";
$CLInow_date    = "$year-$mon-$mday\\ $hour:$min:$sec";
$YMD            = "$year-$mon-$mday";
$start_time     = $now_date;
$CIDdate        = "$mon$mday$hour$min$sec";
$tsSQLdate      = "$year$mon$mday$hour$min$sec";
$SQLdate        = "$year-$mon-$mday $hour:$min:$sec";
$SQLdateBEGIN   = $SQLdate;
$BDtarget       = ( $now_date_epoch + 600 );
( $Bsec, $Bmin, $Bhour, $Bmday, $Bmon, $Byear, $Bwday, $Byday, $Bisdst ) =
  localtime($BDtarget);
$Byear = ( $Byear + 1900 );
$Bmon++;
if ( $Bmon < 10 )  { $Bmon  = "0$Bmon"; }
if ( $Bmday < 10 ) { $Bmday = "0$Bmday"; }
if ( $Bhour < 10 ) { $Bhour = "0$Bhour"; }
if ( $Bmin < 10 )  { $Bmin  = "0$Bmin"; }
if ( $Bsec < 10 )  { $Bsec  = "0$Bsec"; }
$last_update_time = "$Byear$Bmon$Bmday$Bhour$Bmin$Bsec";
$PATHconf         = '/etc/astguiclient.conf';
open( conf, "$PATHconf" ) || die "can't open $PATHconf: $!\n";
@conf = <conf>;
close(conf);
$i = 0;

foreach (@conf) {
    $line = $conf[$i];
    $line =~ s/ |>|\n|\r|\t|\#.*|;.*//gi;
    if ( ( $line =~ /^PATHhome/ ) && ( $CLIhome < 1 ) ) {
        $PATHhome = $line;
        $PATHhome =~ s/.*=//gi;
    }
    if ( ( $line =~ /^PATHlogs/ ) && ( $CLIlogs < 1 ) ) {
        $PATHlogs = $line;
        $PATHlogs =~ s/.*=//gi;
    }
    if ( ( $line =~ /^PATHagi/ ) && ( $CLIagi < 1 ) ) {
        $PATHagi = $line;
        $PATHagi =~ s/.*=//gi;
    }
    if ( ( $line =~ /^PATHweb/ ) && ( $CLIweb < 1 ) ) {
        $PATHweb = $line;
        $PATHweb =~ s/.*=//gi;
    }
    if ( ( $line =~ /^PATHsounds/ ) && ( $CLIsounds < 1 ) ) {
        $PATHsounds = $line;
        $PATHsounds =~ s/.*=//gi;
    }
    if ( ( $line =~ /^PATHmonitor/ ) && ( $CLImonitor < 1 ) ) {
        $PATHmonitor = $line;
        $PATHmonitor =~ s/.*=//gi;
    }
    if ( ( $line =~ /^VARserver_ip/ ) && ( $CLIserver_ip < 1 ) ) {
        $VARserver_ip = $line;
        $VARserver_ip =~ s/.*=//gi;
    }
    if ( ( $line =~ /^VARDB_server/ ) && ( $CLIDB_server < 1 ) ) {
        $VARDB_server = $line;
        $VARDB_server =~ s/.*=//gi;
    }
    if ( ( $line =~ /^VARDB_database/ ) && ( $CLIDB_database < 1 ) ) {
        $VARDB_database = $line;
        $VARDB_database =~ s/.*=//gi;
    }
    if ( ( $line =~ /^VARDB_user/ ) && ( $CLIDB_user < 1 ) ) {
        $VARDB_user = $line;
        $VARDB_user =~ s/.*=//gi;
    }
    if ( ( $line =~ /^VARDB_pass/ ) && ( $CLIDB_pass < 1 ) ) {
        $VARDB_pass = $line;
        $VARDB_pass =~ s/.*=//gi;
    }
    if ( ( $line =~ /^VARDB_port/ ) && ( $CLIDB_port < 1 ) ) {
        $VARDB_port = $line;
        $VARDB_port =~ s/.*=//gi;
    }
    $i++;
}
if ( !$VARDB_port )  { $VARDB_port  = '3306'; }
if ( !$AGILOGfile )  { $AGILOGfile  = "$PATHlogs/agiout.$year-$mon-$mday"; }
if ( !$PRSLOGfile )  { $PRSLOGfile  = "$PATHlogs/prsout.$year-$mon-$mday"; }
if ( !$PRSTESTfile ) { $PRSTESTfile = "$PATHlogs/prstest.$year-$mon-$mday"; }
use DBI;
use Time::HiRes ( 'gettimeofday', 'usleep', 'sleep' )
  ;    # necessary to have perl sleep command of less than one second
use Asterisk::AGI;
$AGI  = new Asterisk::AGI;
$dbhA = DBI->connect( "DBI:mysql:$VARDB_database:$VARDB_server:$VARDB_port",
    "$VARDB_user", "$VARDB_pass" )
  or die "Couldn't connect to database: " . DBI->errstr;
$stmtA = "SELECT agi_output FROM servers where server_ip = '$VARserver_ip';";
$sthA  = $dbhA->prepare($stmtA) or die "preparing: ", $dbhA->errstr;
$sthA->execute or die "executing: $stmtA ", $dbhA->errstr;
$sthArows = $sthA->rows;

if ( $sthArows > 0 ) {
    $AGILOG       = '0';
    @aryA         = $sthA->fetchrow_array;
    $DBagi_output = $aryA[0];
    if ( $DBagi_output =~ /STDERR/ ) { $AGILOG = '1'; }
    if ( $DBagi_output =~ /FILE/ )   { $AGILOG = '2'; }
    if ( $DBagi_output =~ /BOTH/ )   { $AGILOG = '3'; }
}
$sthA->finish();
$stmtA =
"SELECT enable_queuemetrics_logging,queuemetrics_server_ip,queuemetrics_dbname,queuemetrics_login,queuemetrics_pass,queuemetrics_log_id,queuemetrics_eq_prepend,call_menu_qualify_enabled FROM system_settings;";
$sthA = $dbhA->prepare($stmtA) or die "preparing: ",        $dbhA->errstr;
$sthA->execute                 or die "executing: $stmtA ", $dbhA->errstr;
$sthArows = $sthA->rows;
if ( $sthArows > 0 ) {
    @aryA                        = $sthA->fetchrow_array;
    $enable_queuemetrics_logging = $aryA[0];
    $queuemetrics_server_ip      = $aryA[1];
    $queuemetrics_dbname         = $aryA[2];
    $queuemetrics_login          = $aryA[3];
    $queuemetrics_pass           = $aryA[4];
    $queuemetrics_log_id         = $aryA[5];
    $queuemetrics_eq_prepend     = $aryA[6];
    $call_menu_qualify_enabled   = $aryA[7];
}
$sthA->finish();
if ( length( $ARGV[0] ) > 1 ) {
    if ($AGILOG) { $agi_string = "Perl Environment Dump:"; &agi_output; }
    $i = 0;
    while ( $#ARGV >= $i ) {
        $args = "$args $ARGV[$i]";
        if ($AGILOG) { $agi_string = "$i|$ARGV[$i]"; &agi_output; }
        $i++;
    }
    @ARGV_vars     = split( /-----/, $ARGV[0] );
    $channel_group = $ARGV_vars[0];
    $log_to_vac    = $ARGV_vars[1];
    $note          = $ARGV_vars[2];
    if ( length($note) < 1 ) { $note = 'NONE'; }
    $call_time_override     = $ARGV_vars[3];
    $route_override         = $ARGV_vars[4];
    $route_value_override   = $ARGV_vars[5];
    $route_context_override = $ARGV_vars[6];
    $query_qualification    = $ARGV_vars[7];
    $sip_silence_playback   = $ARGV_vars[8];
}
$| = 1;
while (<STDIN>) {
    chomp;
    last unless length($_);
    if ($AGILOG) {
        if (/^agi_(\w+)\:\s+(.*)$/) {
            $AGI{$1} = $2;
        }
    }
    if (/^agi_context\:\s+(.*)$/)   { $context   = $1; }
    if (/^agi_priority\:\s+(.*)$/)  { $priority  = $1; }
    if (/^agi_uniqueid\:\s+(.*)$/)  { $unique_id = $1; $uniqueid = $unique_id; }
    if (/^agi_channel\:\s+(.*)$/)   { $channel   = $1; }
    if (/^agi_extension\:\s+(.*)$/) { $extension = $1; }
    if (/^agi_type\:\s+(.*)$/)      { $type      = $1; }
    if (/^agi_callerid\:\s+(.*)$/)  { $callerid  = $1; }
    if (/^agi_calleridname\:\s+(.*)$/) { $calleridname = $1; }
}
$callerid     =~ s/\'|\\\\|\\\|\\|\\;|\\\;|\;|;//gi;
$calleridname =~ s/\'|\\\\|\\\|\\|\\;|\\\;|\;|;//gi;
$extension    =~ s/\'|\"|\\\\|\\\|\\|\\;|\\\;|\;|;//gi;
$outboundIVR = 0;
$ingroupIVR  = 0;
$lead_id     = '';
if ( $calleridname =~
/^V\d\d\d\d\d\d\d\d\d\d\d\d\d\d\d\d\d\d\d|^M\d\d\d\d\d\d\d\d\d\d\d\d\d\d\d\d\d\d\d|^Y\d\d\d\d\d\d\d\d\d\d\d\d\d\d\d\d\d\d\d/
  )
{
    $stmtA =
"SELECT campaign_id,lead_id FROM vicidial_auto_calls where callerid='$calleridname';";
    $sthA = $dbhA->prepare($stmtA) or die "preparing: ",        $dbhA->errstr;
    $sthA->execute                 or die "executing: $stmtA ", $dbhA->errstr;
    $sthArows = $sthA->rows;
    if ( $sthArows > 0 ) {
        @aryA        = $sthA->fetchrow_array;
        $campaign_id = $aryA[0];
        $lead_id     = $aryA[1];
    }
    else {
        $lead_id = substr( $calleridname, 10, 10 );
        $lead_id = ( $lead_id + 0 );
        $callerid =~ s/\D|\'//gi;
        if   ( length($callerid) > 0 ) { $phone_number = $callerid; }
        else                           { $phone_number = ''; }
        $phone_code = '1';
    }
    $sthA->finish();
    if ( $calleridname =~
/^V\d\d\d\d\d\d\d\d\d\d\d\d\d\d\d\d\d\d\d|^M\d\d\d\d\d\d\d\d\d\d\d\d\d\d\d\d\d\d\d/
      )
    {
        $outboundIVR = 1;
    }
    if ( $calleridname =~ /^Y\d\d\d\d\d\d\d\d\d\d\d\d\d\d\d\d\d\d\d/ ) {
        $ingroupIVR = 1;
    }
    if ( length($phone_number) < 6 ) {
        $stmtA =
          "SELECT phone_number FROM vicidial_list where lead_id='$lead_id';";
        $sthA = $dbhA->prepare($stmtA) or die "preparing: ", $dbhA->errstr;
        $sthA->execute or die "executing: $stmtA ",          $dbhA->errstr;
        $sthArows = $sthA->rows;
        if ( $sthArows > 0 ) {
            @aryA         = $sthA->fetchrow_array;
            $phone_number = $aryA[0];
        }
        $sthA->finish();
    }
    $callerid = $calleridname;
}
else {
    if (   ( $callerid =~ /\".*\"/ )
        && ( ( !$calleridname ) or ( $calleridname =~ /unknown/ ) ) )
    {
        $calleridname = $callerid;
        $calleridname =~ s/\<\d\d\d\d\d\d\d\d\d\d\>//gi;
        $calleridname =~ s/\"|\" //gi;
    }
    $callerid     =~ s/\D|\'//gi;
    $calleridname =~ s/unknown|\'//gi;
    if ( ( !$callerid ) or ( $callerid =~ /unknown/ ) ) {
        $callerid = $calleridname;
    }
    if   ( length($callerid) > 0 ) { $phone_number = $callerid; }
    else                           { $phone_number = ''; }
    if   ( length($calleridname) > 0 ) { $VLcomments = $calleridname; }
    else                               { $VLcomments = ''; }
}
if ( ( length($callerid) < 8 ) && ( length($parked_by) > 6 ) ) {
    $callerid = $parked_by;
}
if ( length($pin) > 0 )                   { $callerid             = $pin; }
if ( $sip_silence_playback !~ /YES|NO/i ) { $sip_silence_playback = 'YES'; }
foreach $i ( sort keys %AGI ) {
    if ($AGILOG) { $agi_string = " -- $i = $AGI{$i}"; &agi_output; }
}
if ($AGILOG) {
    $agi_string =
      "AGI Variables: |$unique_id|$channel|$extension|$type|$callerid|";
    &agi_output;
}
if ( length($pin) < 1 ) { $pin = $inbound_number; }
$fronter = $pin;
if ($AGILOG) {
    $agi_string =
"+++++ INBOUND CALL VDCL STARTED : |$channel_group|$callerid-$pin|$now_date";
    &agi_output;
}
$VDADphone      = '';
$VDADphone_code = '';
if ( $channel =~ /Local/i ) {
    if ($AGILOG) {
        $agi_string = "+++++ VDAD START LOCAL CHANNEL: EXITING- $priority";
        &agi_output;
    }
    exit;
}
$cbc = 0;
$stmtA =
"SELECT voicemail_dump_exten,ext_context,answer_transfer_agent,local_gmt,asterisk_version,max_vicidial_trunks,voicemail_dump_exten_no_inst FROM servers where server_ip = '$VARserver_ip';";
$sthA = $dbhA->prepare($stmtA) or die "preparing: ",        $dbhA->errstr;
$sthA->execute                 or die "executing: $stmtA ", $dbhA->errstr;
$sthArows = $sthA->rows;
while ( $sthArows > $cbc ) {
    @aryA                         = $sthA->fetchrow_array;
    $DBvoicemail_dump_exten       = $aryA[0];
    $DBext_context                = $aryA[1];
    $DBanswer_transfer_agent      = $aryA[2];
    $DBSERVER_GMT                 = $aryA[3];
    $DBasterisk_version           = $aryA[4];
    $DBmax_vicidial_trunks        = $aryA[5];
    $voicemail_dump_exten_no_inst = $aryA[6];

    if ($DBvoicemail_dump_exten) {
        $voicemail_dump_exten = $DBvoicemail_dump_exten;
    }
    if ($DBext_context) { $ext_context = $DBext_context; }
    if ($DBanswer_transfer_agent) {
        $answer_transfer_agent = $DBanswer_transfer_agent;
    }
    if ($DBSERVER_GMT)       { $SERVER_GMT = $DBSERVER_GMT; }
    if ($DBasterisk_version) { $AST_ver    = $DBasterisk_version; }
    if ($DBmax_vicidial_trunks) {
        $max_vicidial_trunks = $DBmax_vicidial_trunks;
    }
    $cbc++;
}
$sthA->finish();
if ( length($channel_group) > 1 ) {
    $cbc = 0;
    $stmtA =
"SELECT call_time_id,after_hours_action,after_hours_message_filename,after_hours_exten,after_hours_voicemail,welcome_message_filename,moh_context,onhold_prompt_filename,prompt_interval,agent_alert_exten,agent_alert_delay,drop_call_seconds,drop_action,drop_exten,next_agent_call,voicemail_ext,queue_priority,drop_inbound_group,afterhours_xfer_group FROM vicidial_inbound_groups where group_id = '$channel_group';";
    $sthA = $dbhA->prepare($stmtA) or die "preparing: ",        $dbhA->errstr;
    $sthA->execute                 or die "executing: $stmtA ", $dbhA->errstr;
    $sthArows = $sthA->rows;
    while ( $sthArows > $cbc ) {
        @aryA                         = $sthA->fetchrow_array;
        $call_time_id                 = $aryA[0];
        $after_hours_action           = $aryA[1];
        $after_hours_message_filename = $aryA[2];
        $after_hours_exten            = $aryA[3];
        $after_hours_voicemail        = $aryA[4];
        $welcome_message_filename     = $aryA[5];
        $moh_context                  = $aryA[6];
        $onhold_prompt_filename       = $aryA[7];
        $prompt_interval              = $aryA[8];
        $agent_alert_exten            = $aryA[9];
        $agent_alert_delay            = $aryA[10];
        $drop_call_seconds            = $aryA[11];
        $DROP_TIME                    = $drop_call_seconds;
        $drop_action                  = $aryA[12];
        $drop_exten                   = $aryA[13];
        $CAMP_callorder               = $aryA[14];
        $VDADvoicemail_ext            = $aryA[15];
        $VDADvoicemail_ext =~ s/\D//gi;
        $queue_priority        = $aryA[16];
        $drop_inbound_group    = $aryA[17];
        $afterhours_xfer_group = $aryA[18];
        $cbc++;
    }
    $sthA->finish();
}
if ( length($call_time_override) > 1 ) {
    $call_time_id = $call_time_override;
}
if ( length($route_override) > 1 ) {
    $after_hours_action = $route_override;
}
if ( length($route_value_override) > 1 ) {
    $after_hours_message_filename = $route_value_override;
    $after_hours_exten            = $route_value_override;
    $after_hours_voicemail        = $route_value_override;
}
if ( length($route_context_override) > 1 ) {
    $ext_context = $route_context_override;
}
if ( $wday == 0 ) {
    $daySQL  = ',ct_sunday_start,ct_sunday_stop';
    $yestSQL = ',ct_saturday_start,ct_saturday_stop';
}
if ( $wday == 1 ) {
    $daySQL  = ',ct_monday_start,ct_monday_stop';
    $yestSQL = ',ct_sunday_start,ct_sunday_stop';
}
if ( $wday == 2 ) {
    $daySQL  = ',ct_tuesday_start,ct_tuesday_stop';
    $yestSQL = ',ct_monday_start,ct_monday_stop';
}
if ( $wday == 3 ) {
    $daySQL  = ',ct_wednesday_start,ct_wednesday_stop';
    $yestSQL = ',ct_tuesday_start,ct_tuesday_stop';
}
if ( $wday == 4 ) {
    $daySQL  = ',ct_thursday_start,ct_thursday_stop';
    $yestSQL = ',ct_wednesday_start,ct_wednesday_stop';
}
if ( $wday == 5 ) {
    $daySQL  = ',ct_friday_start,ct_friday_stop';
    $yestSQL = ',ct_thursday_start,ct_thursday_stop';
}
if ( $wday == 6 ) {
    $daySQL  = ',ct_saturday_start,ct_saturday_stop';
    $yestSQL = ',ct_friday_start,ct_friday_stop';
}
$ct_default_start = '0';
$ct_default_stop  = '9999';
$stmtA =
"SELECT ct_default_start,ct_default_stop $daySQL $yestSQL,ct_holidays FROM vicidial_call_times where call_time_id = '$call_time_id';";
$sthA = $dbhA->prepare($stmtA) or die "preparing: ",        $dbhA->errstr;
$sthA->execute                 or die "executing: $stmtA ", $dbhA->errstr;
$sthArows = $sthA->rows;
if ( $sthArows > 0 ) {
    @aryA             = $sthA->fetchrow_array;
    $ct_default_start = $aryA[0];
    $ct_default_stop  = $aryA[1];
    $ct_day_start     = $aryA[2];
    $ct_day_stop      = $aryA[3];
    $ct_yest_start    = $aryA[4];
    $ct_yest_stop     = $aryA[5];
    $ct_holidays      = $aryA[6];
    $sthA->finish();

    if ( ( $ct_yest_start < 1 ) && ( $ct_yest_stop < 1 ) ) {
        $ct_yest_start = $ct_default_start;
        $ct_yest_stop  = $ct_default_stop;
    }
    if ( ( $ct_day_start > 0 ) || ( $ct_day_stop > 0 ) ) {
        $ct_default_start = $ct_day_start;
        $ct_default_stop  = $ct_day_stop;
    }
}
if ( length($ct_holidays) > 2 ) {
    $stmtA =
"SELECT count(*) from vicidial_call_time_holidays where holiday_date='$YMD' and holiday_status='ACTIVE';";
    if ($DBX) { print "   |$stmtA|\n"; }
    $sthA = $dbhA->prepare($stmtA) or die "preparing: ",        $dbhA->errstr;
    $sthA->execute                 or die "executing: $stmtA ", $dbhA->errstr;
    @aryA                = $sthA->fetchrow_array;
    $holiday_today_count = $aryA[0];
    if ($AGILOG) {
        $agi_string =
          "Holiday check: |$holiday_today_count|$stmtA|$ct_holidays|";
        &agi_output;
    }
    $ct_hrs = 0;
    $b      = 0;
    if ( $holiday_today_count > 0 ) {
        @holiday_rules = split( /\|/, $ct_holidays );
        $ct_hrs        = ( $#holiday_rules - 0 );
        while ( $ct_hrs >= $b ) {
            if ( length( $holiday_rules[$b] ) > 1 ) {
                $stmtA =
"SELECT holiday_id,holiday_date,holiday_name,ct_default_start,ct_default_stop,default_afterhours_filename_override from vicidial_call_time_holidays where holiday_id='$holiday_rules[$b]' and holiday_status='ACTIVE' and holiday_date='$YMD';";
                if ($DBX) { print "   |$stmtA|\n"; }
                $sthA = $dbhA->prepare($stmtA)
                  or die "preparing: ", $dbhA->errstr;
                $sthA->execute or die "executing: $stmtA ", $dbhA->errstr;
                $sthArows = $sthA->rows;
                if ( $sthArows > 0 ) {
                    @aryA             = $sthA->fetchrow_array;
                    $holiday_id       = $aryA[0];
                    $holiday_date     = $aryA[1];
                    $holiday_name     = $aryA[2];
                    $ct_default_start = $aryA[3];
                    $ct_default_stop  = $aryA[4];
                    $default_afterhours_filename_override = $aryA[5];
                    if ($AGILOG) {
                        $agi_string =
"Active Holiday found!    |$holiday_id|$holiday_date|$holiday_name|$ct_default_start|$ct_default_stop|$default_afterhours_filename_override|";
                        &agi_output;
                    }
                }
                $sthA->finish();
            }
            $b++;
        }
    }
}
if ( $sip_silence_playback =~ /YES/i ) {
    $AGI->stream_file('sip-silence');
    $AGI->stream_file('sip-silence');
}
$ct_stop_overflow = 0;    # allow for overflow time, past midnight
if ( $ct_yest_stop > 2400 ) { $ct_stop_overflow = ( $ct_yest_stop - 2400 ); }
if (   ( ( $hm < $ct_default_start ) || ( $hm > $ct_default_stop ) )
    && ( $hm >= $ct_stop_overflow ) )
{
    $VHqueryCID = "VA$CIDdate$hour$min$sec$sec";
    if ( $outboundIVR > 0 ) {
        $stmtA =
"DELETE FROM vicidial_auto_calls where callerid='$callerid' and campaign_id='$campaign_id' and server_ip='$VARserver_ip' order by call_time desc limit 1;";
        $affected_rows = $dbhA->do($stmtA);
        if ($AGILOG) {
            $agi_string =
"--    AH VDAC vac record deleted: |$affected_rows|$campaign_id|$callerid|$call_time_id|";
            &agi_output;
        }
        $stmtA =
"INSERT INTO vicidial_outbound_ivr_log (uniqueid,caller_code,event_date,campaign_id,lead_id,menu_id,menu_action) values('$uniqueid','$callerid','$SQLdate','$campaign_id','$lead_id','$context','AFTERHOURSDROP')";
        if ($AGILOG) { $agi_string = "|$stmtA|"; &agi_output; }
        $affected_rowsL = $dbhA->do($stmtA);
        if ($AGILOG) {
            $agi_string =
"--    VOIL insert: |$affected_rowsL|$uniqueid|$callerid|$campaign_id|$lead_id|$context|";
            &agi_output;
        }
    }
    else {
        $stmtA =
"DELETE FROM vicidial_auto_calls where callerid='$callerid' and uniqueid='$uniqueid' and server_ip='$VARserver_ip' order by call_time desc limit 1;";
        $affected_rows = $dbhA->do($stmtA);
        if ($AGILOG) {
            $agi_string =
"--    AH VDAC vac record deleted: |$affected_rows|$uniqueid|$callerid|$call_time_id|";
            &agi_output;
        }
        $stmtA =
"INSERT INTO live_inbound_log (uniqueid,channel,server_ip,caller_id,extension,phone_ext,start_time,comment_a,comment_b,comment_c,comment_d,comment_e) values('$uniqueid','$channel','$VARserver_ip','$callerid','$extension','$phone_number','$SQLdate','$channel_group','$note','AFTERHOURSDROP','$context','$priority')";
        if ($AGILOG) { $agi_string = "|$stmtA|"; &agi_output; }
        $affected_rowsL = $dbhA->do($stmtA);
        if ($AGILOG) {
            $agi_string =
"--    AH LIL insert: |$affected_rowsL|$uniqueid|$callerid|$extension|";
            &agi_output;
        }
    }
    if ( $enable_queuemetrics_logging > 0 ) {
        $dbhB = DBI->connect(
            "DBI:mysql:$queuemetrics_dbname:$queuemetrics_server_ip:3306",
            "$queuemetrics_login", "$queuemetrics_pass" )
          or die "Couldn't connect to database: " . DBI->errstr;
        if ($DBX) {
            print
"CONNECTED TO QueueMetrics DATABASE:  $queuemetrics_server_ip|$queuemetrics_dbname\n";
        }
        $stmtB =
"INSERT INTO queue_log SET `partition`='P01',time_id='$now_date_epoch',call_id='$uniqueid',queue='NONE',agent='NONE',verb='INFO',data1='IVR',data2='$context',serverid='$queuemetrics_log_id';";
        if ($AGILOG) { $agi_string = "|$stmtB|"; &agi_output; }
        $Baffected_rows = $dbhB->do($stmtB);
        $dbhB->disconnect();
    }
    if ( $after_hours_action =~
        /EXTENSION|VOICEMAIL|VMAIL_NO_INST|IN_GROUP|CALLMENU/ )
    {
        if ( $after_hours_action =~ /CALLMENU/ ) {
            $DROPexten   = 's';
            $ext_context = "$after_hours_exten";
        }
        if ( $after_hours_action =~ /EXTENSION/ ) {
            $DROPexten = "$after_hours_exten";
        }
        if ( $after_hours_action =~ /VOICEMAIL|VMAIL_NO_INST/ ) {
            if ( $after_hours_action =~ /VMAIL_NO_INST/ ) {
                $voicemail_dump_exten = $voicemail_dump_exten_no_inst;
            }
            $DROPexten = "$voicemail_dump_exten$after_hours_voicemail";
        }
        if ( $after_hours_action =~ /IN_GROUP/ )
        {    # 90009*CL_uk3survy_*8301*10000123*universal*7275551212*1234*"
            $S = '*';
            $DROPexten =
"90009*$afterhours_xfer_group$S$S$insert_lead_id$S$S$phone_number$S$fronter$S";
        }
        if ( length($DROPexten) > 0 )
        { ### if DROP extension is defined then send the dropped call there instead of hangup
            if ( $sip_silence_playback =~ /YES/i ) {
                $AGI->stream_file('sip-silence');   # stop music-on-hold process
                $AGI->stream_file('sip-silence');   # stop music-on-hold process
            }
            sleep(1);
            if ($AGILOG) {
                $agi_string =
"exiting the VDAD app after hours, transferring call to $DROPexten   |$hm|$ct_default_start|$ct_default_stop|$ct_stop_overflow|";
                &agi_output;
            }
            print "SET CONTEXT $ext_context\n";
            $result = <STDIN>;
            checkresult($result);
            print "SET EXTENSION $DROPexten\n";
            $result = <STDIN>;
            checkresult($result);
            print "SET PRIORITY 1\n";
            $result = <STDIN>;
            checkresult($result);
        }
    }
    if ( $after_hours_action =~ /MESSAGE|HANGUP/ ) {
        if ( $sip_silence_playback =~ /YES/i ) {
            $AGI->stream_file('sip-silence');
            $AGI->stream_file('sip-silence');
        }
        $AGI->stream_file("$after_hours_message_filename");
        sleep(1);
        $stmtA =
"INSERT INTO vicidial_manager values('','','$SQLdate','NEW','N','$VARserver_ip','','Hangup','$VHqueryCID','Channel: $channel','','','','','','','','','')";
        $affected_rows = $dbhA->do($stmtA);
        if ($AGILOG) {
            $agi_string =
"--    VDCL call_hungup after hours: |$VHqueryCID|$VDADconf_exten|$channel|insert to vicidial_manager";
            &agi_output;
        }
    }
    $dbhA->disconnect();
    exit;
}
if (   ( $call_menu_qualify_enabled > 0 )
    && ( $query_qualification =~ /YES/ )
    && ( ( $outboundIVR > 0 ) || ( $ingroupIVR > 0 ) ) )
{
    $qualify_count = 0;
    $stmtA =
      "SELECT qualify_sql FROM vicidial_call_menu where menu_id='$context';";
    $sthA = $dbhA->prepare($stmtA) or die "preparing: ",        $dbhA->errstr;
    $sthA->execute                 or die "executing: $stmtA ", $dbhA->errstr;
    $sthArows = $sthA->rows;
    if ( $sthArows > 0 ) {
        @aryA        = $sthA->fetchrow_array;
        $qualify_sql = $aryA[0];
    }
    $sthA->finish();
    if ( length($qualify_sql) > 5 ) {
        $lead_id = substr( $calleridname, 10, 10 );
        $lead_id = ( $lead_id + 0 );
        $stmtA =
"SELECT count(*) FROM vicidial_list where lead_id='$lead_id' and $qualify_sql;";
        $sthA = $dbhA->prepare($stmtA) or die "preparing: ", $dbhA->errstr;
        $sthA->execute or die "executing: $stmtA ",          $dbhA->errstr;
        $sthArows = $sthA->rows;
        if ( $sthArows > 0 ) {
            @aryA          = $sthA->fetchrow_array;
            $qualify_count = $aryA[0];
        }
        $sthA->finish();
        if ($AGILOG) {
            $agi_string =
"Exiting the TimeCheck App Qualify SQL   |$qualify_count|$lead_id|$context|$calleridname|$stmtA|";
            &agi_output;
        }
        if ( $qualify_count < 1 ) {
            if ( $sip_silence_playback =~ /YES/i ) {
                $AGI->stream_file('sip-silence');   # stop music-on-hold process
                $AGI->stream_file('sip-silence');   # stop music-on-hold process
            }
            usleep( 1 * 100 * 1000 );
            if ($AGILOG) {
                $agi_string =
"      TimeCheck App Qualify SQL FAIL, transferring call to D   |$qualify_count|$lead_id|$context|$calleridname|";
                &agi_output;
            }
            print "SET CONTEXT $context\n";
            $result = <STDIN>;
            checkresult($result);
            print "SET EXTENSION D\n";
            $result = <STDIN>;
            checkresult($result);
            print "SET PRIORITY 1\n";
            $result = <STDIN>;
            checkresult($result);
            exit;
        }
    }
}
if ( $outboundIVR > 0 ) {
    $stmtA =
"INSERT INTO vicidial_outbound_ivr_log (uniqueid,caller_code,event_date,campaign_id,lead_id,menu_id,menu_action) values('$uniqueid','$callerid','$SQLdate','$campaign_id','$lead_id','$context','')";
    if ($AGILOG) { $agi_string = "|$stmtA|"; &agi_output; }
    $affected_rowsL = $dbhA->do($stmtA);
    if ($AGILOG) {
        $agi_string =
"--    VOIL insert: |$affected_rowsL|$uniqueid|$callerid|$campaign_id|$lead_id|$context|";
        &agi_output;
    }
    $stmtA =
"UPDATE vicidial_auto_calls SET last_update_time='$last_update_time',status='IVR' where callerid='$callerid';";
    if ($AGILOG) { $agi_string = "|$stmtA|"; &agi_output; }
    $affected_rowsV = $dbhA->do($stmtA);
    if ($AGILOG) {
        $agi_string =
"--    VAC update: |$affected_rowsV|$uniqueid|$callerid|$last_update_time|";
        &agi_output;
    }
}
else {
    if ( $log_to_vac =~ /YES/ ) {
        if ( $ingroupIVR > 0 ) {
            $stmtA =
"UPDATE vicidial_auto_calls SET last_update_time='$last_update_time',status='IVR' where callerid='$callerid';";
            if ($AGILOG) { $agi_string = "|$stmtA|"; &agi_output; }
            $affected_rowsV = $dbhA->do($stmtA);
            if ($AGILOG) {
                $agi_string =
"--    VAC update: |$affected_rowsV|$uniqueid|$callerid|$last_update_time|";
                &agi_output;
            }
            if ( $affected_rowsV < 1 ) {
                $stmtA =
"INSERT INTO vicidial_auto_calls (server_ip,campaign_id,lead_id,status,uniqueid,callerid,channel,phone_code,phone_number,call_time,call_type,stage,queue_priority,last_update_time) values('$VARserver_ip','$channel_group','$lead_id','IVR','$uniqueid','$callerid','$channel','$phone_code','$phone_number','$SQLdate','IN','IVR-0','0','$last_update_time')";
                if ($AGILOG) { $agi_string = "|$stmtA|"; &agi_output; }
                $affected_rowsV = $dbhA->do($stmtA);
                if ($AGILOG) {
                    $agi_string =
"--    VAC insert: |$affected_rowsV|$uniqueid|$callerid|$extension|$lead_id|";
                    &agi_output;
                }
            }
        }
        else {
            $stmtA =
"DELETE FROM vicidial_auto_calls where callerid='$callerid' and uniqueid='$uniqueid' and server_ip='$VARserver_ip';";
            $affected_rows = $dbhA->do($stmtA);
            if ($AGILOG) {
                $agi_string =
"--    VDAC vac record deleted: |$affected_rows| $uniqueid|$callerid|";
                &agi_output;
            }
            $stmtA =
"INSERT INTO vicidial_auto_calls (server_ip,campaign_id,lead_id,status,uniqueid,callerid,channel,phone_code,phone_number,call_time,call_type,stage,queue_priority,last_update_time) values('$VARserver_ip','$channel_group','0','IVR','$uniqueid','$callerid','$channel','$phone_code','$phone_number','$SQLdate','IN','IVR-0','$queue_priority','$last_update_time')";
            if ($AGILOG) { $agi_string = "|$stmtA|"; &agi_output; }
            $affected_rowsV = $dbhA->do($stmtA);
            if ($AGILOG) {
                $agi_string =
"--    VAC insert: |$affected_rowsV|$uniqueid|$callerid|$extension|";
                &agi_output;
            }
        }
    }
    $stmtA =
"INSERT INTO live_inbound_log (uniqueid,channel,server_ip,caller_id,extension,phone_ext,start_time,comment_a,comment_b,comment_d,comment_e) values('$uniqueid','$channel','$VARserver_ip','$callerid','$extension','$phone_number','$SQLdate','$channel_group','$note','$context','$priority')";
    if ($AGILOG) { $agi_string = "|$stmtA|"; &agi_output; }
    $affected_rowsL = $dbhA->do($stmtA);
    if ($AGILOG) {
        $agi_string =
          "--    LIL insert: |$affected_rowsL|$uniqueid|$callerid|$extension|";
        &agi_output;
    }
}
if ( $enable_queuemetrics_logging > 0 ) {
    $dbhB = DBI->connect(
        "DBI:mysql:$queuemetrics_dbname:$queuemetrics_server_ip:3306",
        "$queuemetrics_login", "$queuemetrics_pass" )
      or die "Couldn't connect to database: " . DBI->errstr;
    if ($DBX) {
        print
"CONNECTED TO QueueMetrics DATABASE:  $queuemetrics_server_ip|$queuemetrics_dbname\n";
    }
    if ( $outboundIVR > 0 ) { $uniqueid = $caller_id; }
    $stmtB =
"INSERT INTO queue_log SET `partition`='P01',time_id='$now_date_epoch',call_id='$uniqueid',queue='NONE',agent='NONE',verb='INFO',data1='IVR',data2='$context',serverid='$queuemetrics_log_id';";
    if ($AGILOG) { $agi_string = "|$stmtB|"; &agi_output; }
    $Baffected_rows = $dbhB->do($stmtB);
    $dbhB->disconnect();
}
$sthA->finish();
$dbhA->disconnect();
exit;

sub checkresult {
    my ($res) = @_;
    my $retval;
    $tests++;
    chomp $res;
    if ( $res =~ /^200/ ) {
        $res =~ /result=(-?\d+)/;
        if ( !length($1) ) {
            $fail++;
        }
        else {
            $pass++;
        }
    }
    else {
        $fail++;
    }
}

sub agi_output {
    if ( $AGILOG >= 2 ) {
        open( Lout, ">>$AGILOGfile" )
          || die "Can't open $AGILOGfile: $!\n";
        print Lout "$now_date|$script|$agi_string\n";
        close(Lout);
    }
    if ( ( $AGILOG == '1' ) || ( $AGILOG == '3' ) ) {
        print STDERR "$now_date|$script|$agi_string\n";
    }
    $agi_string = '';
}