#!/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!
# * ======================================== */
$PATHconf       = '/etc/astguiclient.conf';
$PATHconfPREFIX = '/etc/vicidial_prefix.conf';
$|              = 1;
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 =~ /^PATHlogs/ ) && ( $CLIlogs < 1 ) ) {
        $PATHlogs = $line;
        $PATHlogs =~ s/.*=//gi;
    }
    if (   ( $line =~ /^VARfastagi_log_min_servers/ )
        && ( $CLIVARfastagi_log_min_servers < 1 ) )
    {
        $VARfastagi_log_min_servers = $line;
        $VARfastagi_log_min_servers =~ s/.*=//gi;
    }
    if (   ( $line =~ /^VARfastagi_log_max_servers/ )
        && ( $CLIVARfastagi_log_max_servers < 1 ) )
    {
        $VARfastagi_log_max_servers = $line;
        $VARfastagi_log_max_servers =~ s/.*=//gi;
    }
    if (   ( $line =~ /^VARfastagi_log_min_spare_servers/ )
        && ( $CLIVARfastagi_log_min_spare_servers < 1 ) )
    {
        $VARfastagi_log_min_spare_servers = $line;
        $VARfastagi_log_min_spare_servers =~ s/.*=//gi;
    }
    if (   ( $line =~ /^VARfastagi_log_max_spare_servers/ )
        && ( $CLIVARfastagi_log_max_spare_servers < 1 ) )
    {
        $VARfastagi_log_max_spare_servers = $line;
        $VARfastagi_log_max_spare_servers =~ s/.*=//gi;
    }
    if (   ( $line =~ /^VARfastagi_log_max_requests/ )
        && ( $CLIVARfastagi_log_max_requests < 1 ) )
    {
        $VARfastagi_log_max_requests = $line;
        $VARfastagi_log_max_requests =~ s/.*=//gi;
    }
    if (   ( $line =~ /^VARfastagi_log_checkfordead/ )
        && ( $CLIVARfastagi_log_checkfordead < 1 ) )
    {
        $VARfastagi_log_checkfordead = $line;
        $VARfastagi_log_checkfordead =~ s/.*=//gi;
    }
    if (   ( $line =~ /^VARfastagi_log_checkforwait/ )
        && ( $CLIVARfastagi_log_checkforwait < 1 ) )
    {
        $VARfastagi_log_checkforwait = $line;
        $VARfastagi_log_checkforwait =~ 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++;
}
open( viciprefix, "$PATHconfPREFIX" ) || die "can't open $PATHconfPREFIX: $!\n";
@viciprefix = <viciprefix>;
close(viciprefix);
$i = 0;
foreach (@viciprefix) {
    $line = $viciprefix[$i];
    $line =~ s/ |>|\n|\r|\t|\#.*|;.*//gi;
    if ( ( $line =~ /^PREFIX_DB_server/ ) && ( $CLIDB_server < 1 ) ) {
        $PREFIX_DB_server = $line;
        $PREFIX_DB_server =~ s/.*=//gi;
    }
    if ( ( $line =~ /^PREFIX_DB_database/ ) && ( $CLIDB_database < 1 ) ) {
        $PREFIX_DB_database = $line;
        $PREFIX_DB_database =~ s/.*=//gi;
    }
    if ( ( $line =~ /^PREFIX_DB_user/ ) && ( $CLIDB_user < 1 ) ) {
        $PREFIX_DB_user = $line;
        $PREFIX_DB_user =~ s/.*=//gi;
    }
    if ( ( $line =~ /^PREFIX_DB_pass/ ) && ( $CLIDB_pass < 1 ) ) {
        $PREFIX_DB_pass = $line;
        $PREFIX_DB_pass =~ s/.*=//gi;
    }
    if ( ( $line =~ /^PREFIX_DB_port/ ) && ( $CLIDB_port < 1 ) ) {
        $PREFIX_DB_port = $line;
        $PREFIX_DB_port =~ s/.*=//gi;
    }
    $i++;
}
$secX              = time();
$MT[0]             = '';
$list_id           = '';
$cellphone_list_id = '';
$landline_list_id  = '';
$invalid_list_id   = '';
$invalid           = 0;
$cellphone         = 0;
$landline          = 0;

if ( length( $ARGV[0] ) > 1 ) {
    $i = 0;
    while ( $#ARGV >= $i ) {
        $args = "$args $ARGV[$i]";
        $i++;
    }
    if ( $args =~ /--help/i ) {
        print "allowed run time options:\n";
        print "  [--test] = test\n";
        print "  [--quiet] = supress all normal output\n";
        print "  [--debug] = verbose debug messages\n";
        print "  [--debugX] = extra verbose debug messages\n";
        print "  [--user=XXX] = system user for lookups\n";
        print "  [--pass=XXX] = system password for lookups\n";
        print
"  [--list-id=XXX] = list(s) that you want to filter, double-dash-delimited if more than one: 111--222\n";
        print
"                    double-dash-delimited if more than one: 111--222\n";
        print
"                    or you can use ---ALL--- for all lists in the system\n";
        print
"  [--cellphone-list-id=XXX] = list that you want to move cellphones into\n";
        print
"  [--landline-list-id=XXX] = OPTIONAL, list that you want to move landline phones into\n";
        print
"  [--invalid-list-id=XXX] = OPTIONAL, list that you want to move invalid prefix phone numbers into\n";
        print
"  [--vl-field-update=XXX] = OPTIONAL, field that you want to update with the phone type information\n";
        print
"  [--exclude-field=XXX] = OPTIONAL, field that you want to check in vicidial_list to exclude from processing\n";
        print
"  [--exclude-value=XXX] = OPTIONAL, value of the above field that you want to check in vicidial_list to exclude from processing\n";
        print
"  [--batch-flag-vl-field] = OPTIONAL, before processing, append F to all vl-field values of S, C, I\n";
        print
"  [--batch-quantity=XXX] = OPTIONAL, process only XXX number of vicidial_list records, must run with --batch-flag-vl-field first time\n";
        print
"                           This option will only process records that have blank, NULL, SF, CF or IF as vl-field values\n";
        print "\n";
        exit;
    }
    else {
        if ( $args =~ /--quiet/i ) {
            $Q = 1;
        }
        if ( $args =~ /--debug/i ) {
            $DB = 1;
        }
        if ( $args =~ /--debugX/i ) {
            $DBX = 1;
        }
        if ( $args =~ /--output-to-db/i ) {
            $output_to_db = 1;
        }
        if ( $args =~ /--db-output-code=/i ) {
            @data_in        = split( /--db-output-code=/, $args );
            $db_output_code = $data_in[1];
            $db_output_code =~ s/ .*//gi;
            if ( $DB > 0 ) {
                print "\n----- DB OUTPUT CODE: $db_output_code -----\n\n";
            }
        }
        if ( $args =~ /--test/i ) {
            $TEST = 1;
        }
        if ( $args =~ /--user=/i ) {
            @data_in = split( /--user=/, $args );
            $user    = $data_in[1];
            $user =~ s/ .*//gi;
            if ( $DB > 0 ) { print "\n----- USER: $user -----\n\n"; }
        }
        if ( $args =~ /--pass=/i ) {
            @data_in = split( /--pass=/, $args );
            $pass    = $data_in[1];
            $pass =~ s/ .*//gi;
            if ( $DB > 0 ) { print "\n----- PASS: XXXX -----\n\n"; }
        }
        if ( $args =~ /--list-id=/i ) {
            @data_in = split( /--list-id=/, $args );
            $list_id = $data_in[1];
            $list_id =~ s/ .*//gi;
            if ( $list_id =~ /---ALL---/ ) {
                $list_idSQL = '';
            }
            else {
                if ( $list_id =~ /--/ ) {
                    $list_idTEMP = $list_id;
                    $list_idTEMP =~ s/--/','/gi;
                    $list_idSQL = "where list_id IN('$list_idTEMP')";
                }
                else {
                    $list_idSQL = "where list_id='$list_id'";
                }
            }
            if ( $DB > 0 ) {
                print "\n----- LIST ID: $list_id ($list_idSQL) -----\n\n";
            }
        }
        if ( $args =~ /--cellphone-list-id=/i ) {
            @data_in           = split( /--cellphone-list-id=/, $args );
            $cellphone_list_id = $data_in[1];
            $cellphone_list_id =~ s/ .*//gi;
            if ( $DB > 0 ) {
                print "\n----- CELLPHONE LIST ID: $cellphone_list_id -----\n\n";
            }
        }
        if ( $args =~ /--landline-list-id=/i ) {
            @data_in          = split( /--landline-list-id=/, $args );
            $landline_list_id = $data_in[1];
            $landline_list_id =~ s/ .*//gi;
            if ( $DB > 0 ) {
                print "\n----- LANDLINE LIST ID: $landline_list_id -----\n\n";
            }
        }
        if ( $args =~ /--invalid-list-id=/i ) {
            @data_in         = split( /--invalid-list-id=/, $args );
            $invalid_list_id = $data_in[1];
            $invalid_list_id =~ s/ .*//gi;
            if ( $DB > 0 ) {
                print "\n----- INVALID LIST ID: $invalid_list_id -----\n\n";
            }
        }
        if ( $args =~ /--vl-field-update=/i ) {
            @data_in         = split( /--vl-field-update=/, $args );
            $vl_field_update = $data_in[1];
            $vl_field_update =~ s/ .*//gi;
            if ( $DB > 0 ) {
                print "\n----- VL FIELD UPDATE: $vl_field_update -----\n\n";
            }
        }
        if ( $args =~ /--exclude-field=/i ) {
            @data_in       = split( /--exclude-field=/, $args );
            $exclude_field = $data_in[1];
            $exclude_field =~ s/ .*//gi;
            if ( $DB > 0 ) {
                print "\n----- EXCLUDE FIELD: $exclude_field -----\n\n";
            }
        }
        if ( $args =~ /--exclude-value=/i ) {
            @data_in       = split( /--exclude-value=/, $args );
            $exclude_value = $data_in[1];
            $exclude_value =~ s/ .*//gi;
            if ( $DB > 0 ) {
                print "\n----- EXCLUDE VALUE: $exclude_value -----\n\n";
            }
        }
        if (   ( $args =~ /--batch-quantity=/i )
            && ( length($vl_field_update) > 2 ) )
        {
            @data_in        = split( /--batch-quantity=/, $args );
            $batch_quantity = $data_in[1];
            $batch_quantity =~ s/ .*//gi;
            if ( $DB > 0 ) {
                print "\n----- BATCH QUANTITY VALUE: $batch_quantity -----\n\n";
            }
        }
        if (   ( $args =~ /--batch-flag-vl-field/i )
            && ( length($vl_field_update) > 2 ) )
        {
            $batch_flag_vl_field = 1;
            if ( $DB > 0 ) {
                print
"\n----- BATCH VL FIELD FLAG VALUE: $batch_flag_vl_field -----\n\n";
            }
        }
    }
}
else {
    print "no command line options set\n";
    exit;
}
if ( length($list_id) < 2 ) {
    print "list-id option must be set for this script to run: |$list_id|\n";
    exit;
}
$server_ip = $VARserver_ip;    # Asterisk server IP
if ( !$VARDB_port ) { $VARDB_port = '3306'; }
use DBI;
$dbh = DBI->connect( "DBI:mysql:$VARDB_database:$VARDB_server:$VARDB_port",
    "$VARDB_user", "$VARDB_pass" )
  or die "Couldn't connect to database: " . DBI->errstr;
$dbhA = DBI->connect( "DBI:mysql:$VARDB_database:$VARDB_server:$VARDB_port",
    "$VARDB_user", "$VARDB_pass" )
  or die "Couldn't connect to database: " . DBI->errstr;
$dbhN = DBI->connect(
    "DBI:mysql:$PREFIX_DB_database:$PREFIX_DB_server:$PREFIX_DB_port",
    "$PREFIX_DB_user", "$PREFIX_DB_pass" )
  or die "Couldn't connect to database: " . DBI->errstr;
if ( !%six_digit_wireless_hash ) { &CompilePrefixHashes; }
( $sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $isdst ) =
  localtime(time);
@lead_ids   = @MT;
@phones     = @MT;
$excludeSQL = '';

if ( ( length($exclude_field) > 1 ) && ( length($exclude_value) > 0 ) ) {
    if ( length($list_idSQL) > 5 ) {
        $excludeSQL = "and $exclude_field!='$exclude_value'";
    }
    else {
        $excludeSQL = "where $exclude_field!='$exclude_value'";
    }
}
$limitSQL = '';
if ( ( length($batch_quantity) > 1 ) && ( length($vl_field_update) > 2 ) ) {
    if ( ( length($list_idSQL) > 5 ) || ( length($excludeSQL) > 5 ) ) {
        $limitSQL =
"and ( ($vl_field_update is NULL) or ($vl_field_update IN('','SF','CF','IF')) ) limit $batch_quantity";
    }
    else {
        $limitSQL =
"where ( ($vl_field_update is NULL) or ($vl_field_update IN('','SF','CF','IF')) ) limit $batch_quantity";
    }
}
if ( $batch_flag_vl_field > 0 ) {
    if ($DB) { print "FLAGGING PREVIOUSLY TAGGED LEADS...\n"; }
    $clausesSQL = "$list_idSQL $excludeSQL";
    $clausesSQL =~ s/^where/and/gi;
    $stmtA =
"UPDATE vicidial_list SET $vl_field_update='SF' where $vl_field_update='S' $clausesSQL;";
    if ($DBX) { print STDERR "\n|$stmtA|\n"; }
    $affected_rows = $dbhA->do($stmtA);
    if ($DB) { print "|$affected_rows records changed from S to SF|\n"; }
    $stmtA =
"UPDATE vicidial_list SET $vl_field_update='CF' where $vl_field_update='C' $clausesSQL;";
    if ($DBX) { print STDERR "\n|$stmtA|\n"; }
    $affected_rows = $dbhA->do($stmtA);
    if ($DB) { print "|$affected_rows records changed from C to CF|\n"; }
    $stmtA =
"UPDATE vicidial_list SET $vl_field_update='IF' where $vl_field_update='I' $clausesSQL;";
    if ($DBX) { print STDERR "\n|$stmtA|\n"; }
    $affected_rows = $dbhA->do($stmtA);
    if ($DB) { print "|$affected_rows records changed from I to IF|\n"; }
}
$stmtA =
"SELECT lead_id,phone_number from vicidial_list $list_idSQL $excludeSQL $limitSQL;";
$sthA = $dbhA->prepare($stmtA) or die "preparing: ",        $dbhA->errstr;
$sthA->execute                 or die "executing: $stmtA ", $dbhA->errstr;
$sthArows  = $sthA->rows;
$rec_count = 0;
if ($DB)  { print "LEADS FOUND IN LIST $list_id: |$sthArows|\n"; }
if ($DBX) { print "|$stmtA|\n"; }

while ( $sthArows > $rec_count ) {
    @aryA                 = $sthA->fetchrow_array;
    $lead_ids[$rec_count] = $aryA[0];
    $phones[$rec_count]   = $aryA[1];
    $rec_count++;
    if ( $DB > 0 ) {
        if ( $rec_count =~ /100$/i ) { print STDERR "  *     $rec_count\r"; }
        if ( $rec_count =~ /200$/i ) { print STDERR "   *    $rec_count\r"; }
        if ( $rec_count =~ /300$/i ) { print STDERR "    *   $rec_count\r"; }
        if ( $rec_count =~ /400$/i ) { print STDERR "     *  $rec_count\r"; }
        if ( $rec_count =~ /500$/i ) { print STDERR "      * $rec_count\r"; }
        if ( $rec_count =~ /600$/i ) { print STDERR "     *  $rec_count\r"; }
        if ( $rec_count =~ /700$/i ) { print STDERR "    *   $rec_count\r"; }
        if ( $rec_count =~ /800$/i ) { print STDERR "   *    $rec_count\r"; }
        if ( $rec_count =~ /900$/i ) { print STDERR "  *     $rec_count\r"; }
        if ( $rec_count =~ /000$/i ) { print STDERR " *      $rec_count\r"; }
    }
}
if ($DB) {
    print "STARTING LOOKUP OF NANPA PHONE TYPE, ($rec_count records)\n";
}
$end_time = time;
if ( !$Q ) {
    print "Pre-scrub Elapsed time: "
      . ( $end_time - $start_time )
      . " sec\n\n\n";
}
$rec_count    = 0;
$batch_count  = 0;
$batch_phones = '';
%type_hash    = ();
while ( $sthArows > $rec_count ) {
    $rec_next = ( $rec_count + 1 );
    if ( ( $batch_count < 1000 ) && ( $rec_next < $sthArows ) ) {
        $batch_count++;
    }
    else {
        $secTEMP     = time();
        $batch_count = 0;
    }
    $type = 'I';
    if (   ( length( $phones[$rec_count] ) > 9 )
        && ( length( $phones[$rec_count] ) < 11 ) )
    {
        $pre4 = substr( $phones[$rec_count], 0, 4 );
        $pre5 = substr( $phones[$rec_count], 0, 5 );
        $pre6 = substr( $phones[$rec_count], 0, 6 );
        $pre7 = substr( $phones[$rec_count], 0, 7 );
        if ( $four_digit_prefix_hash{$pre4} ) {
            if (   ( $four_digit_prefix_hash{$pre4} ne "S" )
                && ( $four_digit_prefix_hash{$pre4} ne "V" ) )
            {
                $type = "C";
                Wireless_to_Wired( $phones[$rec_count] );
            }
            else {
                $type = "S";
                Wired_to_Wireless( $phones[$rec_count] );
            }
        }
        elsif ( $five_digit_prefix_hash{$pre5} ) {
            if (   ( $five_digit_prefix_hash{$pre5} ne "S" )
                && ( $five_digit_prefix_hash{$pre5} ne "V" ) )
            {
                $type = "C";
                Wireless_to_Wired( $phones[$rec_count] );
            }
            else {
                $type = "S";
                Wired_to_Wireless( $phones[$rec_count] );
            }
        }
        elsif ( $six_digit_prefix_hash{$pre6} ) {
            if (   ( $six_digit_prefix_hash{$pre6} ne "S" )
                && ( $six_digit_prefix_hash{$pre6} ne "V" ) )
            {
                $type = "C";
                Wireless_to_Wired( $phones[$rec_count] );
            }
            else {
                $type = "S";
                Wired_to_Wireless( $phones[$rec_count] );
            }
        }
        elsif ( $seven_digit_prefix_hash{$pre7} ) {
            if (   ( $seven_digit_prefix_hash{$pre7} ne "S" )
                && ( $seven_digit_prefix_hash{$pre7} ne "V" ) )
            {
                $type = "C";
                Wireless_to_Wired( $phones[$rec_count] );
            }
            else {
                $type = "S";
                Wired_to_Wireless( $phones[$rec_count] );
            }
        }
    }
    $type_hash{$type} .= $lead_ids[$rec_count] . ",";
    @lead_count = ( $type_hash{$type} =~ /,/g );
    if ( @lead_count == 1000 ) {
        $stmtA = "";
        $type_hash{$type} =~ s/,$//;
        if ( ( $type =~ /I/ ) && ( length($invalid_list_id) > 1 ) ) {
            $stmtA =
"UPDATE vicidial_list SET list_id=$invalid_list_id where lead_id in ($type_hash{$type});";
        }
        if ( ( $type =~ /C/ ) && ( length($cellphone_list_id) > 1 ) ) {
            $stmtA =
"UPDATE vicidial_list SET list_id=$cellphone_list_id where lead_id in ($type_hash{$type});";
        }
        if ( ( $type =~ /S|V/ ) && ( length($landline_list_id) > 1 ) ) {
            $stmtA =
"UPDATE vicidial_list SET list_id=$landline_list_id where lead_id in ($type_hash{$type});";
        }
        if ( length($stmtA) > 0 ) {
            if ($DBX) { print STDERR "\n|$stmtA|$phones[$temp_rec]|\n"; }
            $affected_rows = $dbhA->do($stmtA);
            if ($DBX) { print STDERR "|$affected_rows records changed|\n"; }
        }
        if ( ( length($vl_field_update) > 2 ) && ( $TEST < 1 ) ) {
            $stmtA =
"UPDATE vicidial_list SET $vl_field_update='$type' where lead_id in ($type_hash{$type});";
            if ($DBX) { print STDERR "\n|$stmtA|$phones[$temp_rec]|\n"; }
            $affected_rows = $dbhA->do($stmtA);
            if ($DBX) { print STDERR "|$affected_rows records changed|\n"; }
        }
        $type_hash{$type} = "";
        if ($DB) { print STDERR "$rec_count / $sthArows\r"; }
    }
    if ( $type =~ /S|V/ ) { $landline++; }
    if ( $type =~ /C/ )   { $cellphone++; }
    if ( $type =~ /I/ )   { $invalid++; }
    $rec_count++;
    if ( ( $rec_count =~ /00000$/i ) && ($DB) ) {
        print STDERR
          "$rec_count / $sthArows (S:$landline | C:$cellphone | I:$invalid)\n";
    }
}
foreach $type ( keys %type_hash ) {
    @lead_count = ( $type_hash{$type} =~ /,/g );
    $stmtA      = "";
    if ( @lead_count > 0 ) {
        $type_hash{$type} =~ s/,$//;
        if ( ( $type =~ /I/ ) && ( length($invalid_list_id) > 1 ) ) {
            $stmtA =
"UPDATE vicidial_list SET list_id=$invalid_list_id where lead_id in ($type_hash{$type});";
        }
        if ( ( $type =~ /C/ ) && ( length($cellphone_list_id) > 1 ) ) {
            $stmtA =
"UPDATE vicidial_list SET list_id=$cellphone_list_id where lead_id in ($type_hash{$type});";
        }
        if ( ( $type =~ /S|V/ ) && ( length($landline_list_id) > 1 ) ) {
            $stmtA =
"UPDATE vicidial_list SET list_id=$landline_list_id where lead_id in ($type_hash{$type});";
        }
        if ( length($stmtA) > 0 ) {
            if ($DBX) { print STDERR "\n|$stmtA|$phones[$temp_rec]|\n"; }
            $affected_rows = $dbhA->do($stmtA);
            if ($DBX) { print STDERR "|$affected_rows records changed|\n"; }
        }
        if ( ( length($vl_field_update) > 2 ) && ( $TEST < 1 ) ) {
            $stmtA =
"UPDATE vicidial_list SET $vl_field_update='$type' where lead_id in ($type_hash{$type});";
            if ($DBX) { print STDERR "\n|$stmtA|$phones[$temp_rec]|\n"; }
            $affected_rows = $dbhA->do($stmtA);
            if ($DBX) { print STDERR "|$affected_rows records changed|\n"; }
        }
        $type_hash{$type} = "";
        if ($DB) { print STDERR "$sthArows / $sthArows\r"; }
    }
}
$end_time = time;
$stmtA =
"INSERT INTO vicidial_admin_log set event_date=NOW(), user='VDAD', ip_address='1.1.1.1', event_section='SERVERS', event_type='OTHER', record_id='$server_ip', event_code='NANPA preload filter run', event_sql='', event_notes='$rec_count / $sthArows (S:$landline | C:$cellphone | I:$invalid) TOTAL Elapsed time: "
  . ( $end_time - $start_time )
  . " sec  |$args|';";
$Iaffected_rows = $dbhA->do($stmtA);
if ($DBX) { print "Admin logged: |$Iaffected_rows|$stmtA|\n"; }
if ( !$Q ) {
    print STDERR
      "$rec_count / $sthArows (S:$landline | C:$cellphone | I:$invalid)\n";
}
if ( !$Q ) {
    print "TOTAL Elapsed time: " . ( $end_time - $start_time ) . " sec\n\n";
}

sub Wired_to_Wireless {
    $phone = $_[0];
    $wtw6  = substr( $phone, 0, 6 );
    $wtw7  = substr( $phone, 0, 7 );
    $wtw8  = substr( $phone, 0, 8 );
    $wtw9  = substr( $phone, 0, 9 );
    if ( $six_digit_wireless_hash{$wtw6} ) {
        $type = "C";
    }
    elsif ( $seven_digit_wireless_hash{$wtw7} ) {
        $type = "C";
    }
    elsif ( $eight_digit_wireless_hash{$wtw8} ) {
        $type = "C";
    }
    elsif ( $nine_digit_wireless_hash{$wtw9} ) {
        $type = "C";
    }
    elsif ( $ten_digit_wireless_hash{$phone} ) {
        $type = "C";
    }
}

sub Wireless_to_Wired {
    $phone = $_[0];
    $wtw6  = substr( $phone, 0, 6 );
    $wtw7  = substr( $phone, 0, 7 );
    $wtw8  = substr( $phone, 0, 8 );
    $wtw9  = substr( $phone, 0, 9 );
    if ( $six_digit_wired_hash{$wtw6} ) {
        $type = "S";
    }
    elsif ( $seven_digit_wired_hash{$wtw7} ) {
        $type = "S";
    }
    elsif ( $eight_digit_wired_hash{$wtw8} ) {
        $type = "S";
    }
    elsif ( $nine_digit_wired_hash{$wtw9} ) {
        $type = "S";
    }
    elsif ( $ten_digit_wired_hash{$phone} ) {
        $type = "S";
    }
}

sub CompilePrefixHashes {
    $start_time        = time;
    $exc_clause        = "";
    $exc_clause_prefix = "";
    $ct_stmt =
"select count(*), count(distinct ac_prefix) from nanpa_prefix_exchanges_fast;";
    $ct_rslt = $dbhN->prepare($ct_stmt);
    $ct_rslt->execute();
    @ct_row = $ct_rslt->fetchrow_array;
    if ( $ct_row[0] == $ct_row[1] ) {
        $stmt =
"select substr(ac_prefix,1,4) as prefix, type, count(*) as ct from nanpa_prefix_exchanges_fast group by prefix, type order by ct desc";
        $rslt = $dbhN->prepare($stmt);
        $rslt->execute();
        %four_digit_prefix_hash = ();
        $current_time           = time;
        $exc_clause4            = "substr(ac_prefix,1,4) not in (";
        if ( $DB == 1 ) {
            print "4-digit check complete.  Elapsed time: "
              . ( $current_time - $start_time )
              . " sec\n";
        }
        while ( @row = $rslt->fetchrow_array ) {
            if ( $row[2] == 1000 ) {
                $exc_clause4 .= "$row[0],";
                $four_digit_prefix_hash{ $row[0] } = $row[1];
            }
            else {
                last;
            }
        }
        $exc_clause4 =~ s/,$//gi;
        $exc_clause4 .= ")";
        if ( length($exc_clause4) < 35 ) {
            $exc_clause4 = "";
        }
        else {
            $exc_clause_prefix = " where ";
            $exc_clause        = $exc_clause4;
        }
        if ( $DB == 1 ) {
            print "Prefixes: " . scalar( keys %four_digit_prefix_hash ) . "\n";
        }
        $stmt =
"select substr(ac_prefix,1,5) as prefix, type, count(*) as ct from nanpa_prefix_exchanges_fast "
          . $exc_clause_prefix
          . $exc_clause
          . " group by prefix, type order by ct desc";
        $rslt = $dbhN->prepare($stmt);
        $rslt->execute();
        %five_digit_prefix_hash = ();
        $current_time           = time;
        $exc_clause5            = "substr(ac_prefix,1,5) not in (";
        if ( $DB == 1 ) {
            print "5-digit check complete.  Elapsed time: "
              . ( $current_time - $start_time )
              . " sec\n";
        }
        while ( @row = $rslt->fetchrow_array ) {
            $ch4 = substr( $row[0], 0, 4 );
            if ( $row[2] == 100 && !$four_digit_prefix_hash{$ch4} ) {
                $exc_clause5 .= "$row[0],";
                $five_digit_prefix_hash{ $row[0] } = $row[1];
            }
            else {
                last;
            }
        }
        $exc_clause5 =~ s/,$//gi;
        $exc_clause5 .= ")";
        if ( length($exc_clause5) < 35 ) {
            $exc_clause5 = "";
        }
        else {
            $exc_clause_prefix = " where ";
            $exc_clause .= " and $exc_clause5";
        }
        if ( $DB == 1 ) {
            print "Prefixes: " . scalar( keys %five_digit_prefix_hash ) . "\n";
        }
        $exc_clause =~ s/^\sand\s//gi;
        $stmt =
"select substr(ac_prefix,1,6) as prefix, type, count(*) as ct from nanpa_prefix_exchanges_fast "
          . $exc_clause_prefix
          . $exc_clause
          . " group by prefix, type order by ct desc";
        $rslt = $dbhN->prepare($stmt);
        $rslt->execute();
        %six_digit_prefix_hash = ();
        $current_time          = time;
        $exc_clause6           = "substr(ac_prefix,1,6) not in (";

        if ( $DB == 1 ) {
            print "6-digit check complete.  Elapsed time: "
              . ( $current_time - $start_time )
              . " sec\n";
        }
        while ( @row = $rslt->fetchrow_array ) {
            $ch4 = substr( $row[0], 0, 4 );
            $ch5 = substr( $row[0], 0, 5 );
            if (   $row[2] == 10
                && !$four_digit_prefix_hash{$ch4}
                && !$five_digit_prefix_hash{$ch5} )
            {
                $exc_clause6 .= "$row[0],";
                $six_digit_prefix_hash{ $row[0] } = $row[1];
            }
            else {
                last;
            }
        }
        $exc_clause6 =~ s/,$//gi;
        $exc_clause6 .= ")";
        if ( length($exc_clause6) < 35 ) {
            $exc_clause6 = "";
        }
        else {
            $exc_clause_prefix = " where ";
            $exc_clause .= " and $exc_clause6";
        }
        if ( $DB == 1 ) {
            print "Prefixes: " . scalar( keys %six_digit_prefix_hash ) . "\n";
        }
        $exc_clause =~ s/^\sand\s//gi;
        $stmt =
"select ac_prefix, type, count(*) as ct from nanpa_prefix_exchanges_fast "
          . $exc_clause_prefix
          . $exc_clause
          . " group by ac_prefix, type order by ct desc";
        $rslt = $dbhN->prepare($stmt);
        $rslt->execute();
        %seven_digit_prefix_hash = ();
        $current_time            = time;
        if ( $DB == 1 ) {
            print "7-digit check complete.  Elapsed time: "
              . ( $current_time - $start_time )
              . " sec\n";
        }
        while ( @row = $rslt->fetchrow_array ) {
            $ch4 = substr( $row[0], 0, 4 );
            $ch5 = substr( $row[0], 0, 5 );
            $ch6 = substr( $row[0], 0, 6 );
            if (   !$four_digit_prefix_hash{$ch4}
                && !$five_digit_prefix_hash{$ch5}
                && !$six_digit_prefix_hash{$ch6} )
            {
                $seven_digit_prefix_hash{ $row[0] } = $row[1];
            }
            else {
                last;
            }
        }
        if ( $DB == 1 ) {
            print "Prefixes: " . scalar( keys %seven_digit_prefix_hash ) . "\n";
        }
    }
    $stmt =
"select substr(phone,1,6) as prefix, count(*) as ct from nanpa_wired_to_wireless group by prefix order by ct desc";
    $rslt = $dbhN->prepare($stmt);
    $rslt->execute();
    %six_digit_wireless_hash = ();
    $current_time            = time;
    $exc_clause              = "";
    $exc_clause_prefix       = "";
    $exc_clause6             = "substr(phone,1,6) not in (";

    if ( $DB == 1 ) {
        print "6-digit check complete.  Elapsed time: "
          . ( $current_time - $start_time )
          . " sec\n";
    }
    while ( @row = $rslt->fetchrow_array ) {
        if ( $row[1] == 10000 ) {
            $exc_clause6 .= "$row[0],";
            $six_digit_wireless_hash{ $row[0] }++;
        }
        else {
            last;
        }
    }
    $exc_clause6 =~ s/,$//gi;
    $exc_clause6 .= ")";
    if ( length($exc_clause6) < 30 ) {
        $exc_clause6 = "";
    }
    else {
        $exc_clause_prefix = " where ";
        $exc_clause        = $exc_clause6;
    }
    if ( $DB == 1 ) {
        print "Prefixes: " . scalar( keys %six_digit_wireless_hash ) . "\n";
    }
    $stmt =
"select substr(phone,1,7) as prefix, count(*) as ct from nanpa_wired_to_wireless "
      . $exc_clause_prefix
      . $exc_clause
      . " group by prefix order by ct desc";
    $rslt = $dbhN->prepare($stmt);
    $rslt->execute();
    %seven_digit_wireless_hash = ();
    $current_time              = time;
    $exc_clause7               = "substr(phone,1,7) not in (";
    if ( $DB == 1 ) {
        print "7-digit check complete.  Elapsed time: "
          . ( $current_time - $start_time )
          . " sec\n";
    }
    while ( @row = $rslt->fetchrow_array ) {
        $ch6 = substr( $row[0], 0, 6 );
        if ( $row[1] == 1000 && !$six_digit_wireless_hash{$ch6} ) {
            $exc_clause7 .= "$row[0],";
            $seven_digit_wireless_hash{ $row[0] }++;
        }
        else {
            last;
        }
    }
    $exc_clause7 =~ s/,$//gi;
    $exc_clause7 .= ")";
    if ( length($exc_clause7) < 30 ) {
        $exc_clause7 = "";
    }
    else {
        $exc_clause_prefix = " where ";
        $exc_clause .= " and $exc_clause7";
    }
    if ( $DB == 1 ) {
        print "Prefixes: " . scalar( keys %seven_digit_wireless_hash ) . "\n";
    }
    $exc_clause =~ s/^\sand\s//gi;
    $stmt =
"select substr(phone,1,8) as prefix, count(*) as ct from nanpa_wired_to_wireless "
      . $exc_clause_prefix
      . $exc_clause
      . " group by prefix order by ct desc";
    $rslt = $dbhN->prepare($stmt);
    $rslt->execute();
    %eight_digit_wireless_hash = ();
    $current_time              = time;
    $exc_clause8               = "substr(phone,1,8) not in (";

    if ( $DB == 1 ) {
        print "8-digit check complete.  Elapsed time: "
          . ( $current_time - $start_time )
          . " sec\n";
    }
    while ( @row = $rslt->fetchrow_array ) {
        $ch6 = substr( $row[0], 0, 6 );
        $ch7 = substr( $row[0], 0, 7 );
        if (   $row[1] == 100
            && !$six_digit_wireless_hash{$ch6}
            && !$seven_digit_wireless_hash{$ch7} )
        {
            $exc_clause8 .= "$row[0],";
            $eight_digit_wireless_hash{ $row[0] }++;
        }
        else {
            last;
        }
    }
    $exc_clause8 =~ s/,$//gi;
    $exc_clause8 .= ")";
    if ( length($exc_clause8) < 30 ) {
        $exc_clause8 = "";
    }
    else {
        $exc_clause_prefix = " where ";
        $exc_clause .= " and $exc_clause8";
    }
    if ( $DB == 1 ) {
        print "Prefixes: " . scalar( keys %eight_digit_wireless_hash ) . "\n";
    }
    $exc_clause =~ s/^\sand\s//gi;
    $stmt =
"select substr(phone,1,9) as prefix, count(*) as ct from nanpa_wired_to_wireless "
      . $exc_clause_prefix
      . $exc_clause
      . " group by prefix order by ct desc";
    $rslt = $dbhN->prepare($stmt);
    $rslt->execute();
    %nine_digit_wireless_hash = ();
    $current_time             = time;
    $exc_clause9              = "substr(phone,1,9) not in (";

    if ( $DB == 1 ) {
        print "9-digit check complete.  Elapsed time: "
          . ( $current_time - $start_time )
          . " sec\n";
    }
    while ( @row = $rslt->fetchrow_array ) {
        $ch6 = substr( $row[0], 0, 6 );
        $ch7 = substr( $row[0], 0, 7 );
        $ch8 = substr( $row[0], 0, 8 );
        if (   $row[1] == 10
            && !$six_digit_wireless_hash{$ch6}
            && !$seven_digit_wireless_hash{$ch7}
            && !$eight_digit_wireless_hash{$ch8} )
        {
            $exc_clause9 .= "$row[0],";
            $nine_digit_wireless_hash{ $row[0] }++;
        }
        else {
            last;
        }
    }
    $exc_clause9 =~ s/,$//gi;
    $exc_clause9 .= ")";
    if ( length($exc_clause9) < 30 ) {
        $exc_clause9 = "";
    }
    else {
        $exc_clause_prefix = " where ";
        $exc_clause .= " and $exc_clause9";
    }
    if ( $DB == 1 ) {
        print "Prefixes: " . scalar( keys %nine_digit_wireless_hash ) . "\n";
    }
    $stmt =
        "select phone from nanpa_wired_to_wireless "
      . $exc_clause_prefix
      . $exc_clause;
    $rslt = $dbhN->prepare($stmt);
    $rslt->execute();
    %ten_digit_wireless_hash = ();
    while ( @row = $rslt->fetchrow_array ) {
        $ch6 = substr( $row[0], 0, 6 );
        $ch7 = substr( $row[0], 0, 7 );
        $ch8 = substr( $row[0], 0, 8 );
        $ch9 = substr( $row[0], 0, 9 );
        if (   !$six_digit_wireless_hash{$ch6}
            && !$seven_digit_wireless_hash{$ch7}
            && !$eight_digit_wireless_hash{$ch8}
            && !$nine_digit_wireless_hash{$ch9} )
        {
            $ten_digit_wireless_hash{ $row[0] }++;
        }
    }
    if ( $DB == 1 ) {
        print "10-digit prefixes: "
          . scalar( keys %ten_digit_wireless_hash ) . "\n\n";
    }
    $stmt =
"select substr(phone,1,6) as prefix, count(*) as ct from nanpa_wireless_to_wired group by prefix order by ct desc";
    $rslt = $dbhN->prepare($stmt);
    $rslt->execute();
    %six_digit_wired_hash = ();
    $current_time         = time;
    $exc_clause           = "";
    $exc_clause_prefix    = "";
    $exc_clause6          = "substr(phone,1,6) not in (";

    if ( $DB == 1 ) {
        print "6-digit check complete.  Elapsed time: "
          . ( $current_time - $start_time )
          . " sec\n";
    }
    while ( @row = $rslt->fetchrow_array ) {
        if ( $row[1] == 10000 ) {
            $exc_clause6 .= "$row[0],";
            $six_digit_wired_hash{ $row[0] }++;
        }
        else {
            last;
        }
    }
    $exc_clause6 =~ s/,$//gi;
    $exc_clause6 .= ")";
    if ( length($exc_clause6) < 30 ) {
        $exc_clause6 = "";
    }
    else {
        $exc_clause_prefix = " where ";
        $exc_clause        = $exc_clause6;
    }
    if ( $DB == 1 ) {
        print "Prefixes: " . scalar( keys %six_digit_wired_hash ) . "\n";
    }
    $stmt =
"select substr(phone,1,7) as prefix, count(*) as ct from nanpa_wireless_to_wired "
      . $exc_clause_prefix
      . $exc_clause
      . " group by prefix order by ct desc";
    $rslt = $dbhN->prepare($stmt);
    $rslt->execute();
    %seven_digit_wired_hash = ();
    $current_time           = time;
    $exc_clause7            = "substr(phone,1,7) not in (";
    if ( $DB == 1 ) {
        print "7-digit check complete.  Elapsed time: "
          . ( $current_time - $start_time )
          . " sec\n";
    }
    while ( @row = $rslt->fetchrow_array ) {
        $ch6 = substr( $row[0], 0, 6 );
        if ( $row[1] == 1000 && !$six_digit_wired_hash{$ch6} ) {
            $exc_clause7 .= "$row[0],";
            $seven_digit_wired_hash{ $row[0] }++;
        }
        else {
            last;
        }
    }
    $exc_clause7 =~ s/,$//gi;
    $exc_clause7 .= ")";
    if ( length($exc_clause7) < 30 ) {
        $exc_clause7 = "";
    }
    else {
        $exc_clause_prefix = " where ";
        $exc_clause .= " and $exc_clause7";
    }
    if ( $DB == 1 ) {
        print "Prefixes: " . scalar( keys %seven_digit_wired_hash ) . "\n";
    }
    $exc_clause =~ s/^\sand\s//gi;
    $stmt =
"select substr(phone,1,8) as prefix, count(*) as ct from nanpa_wireless_to_wired "
      . $exc_clause_prefix
      . $exc_clause
      . " group by prefix order by ct desc";
    $rslt = $dbhN->prepare($stmt);
    $rslt->execute();
    %eight_digit_wired_hash = ();
    $current_time           = time;
    $exc_clause8            = "substr(phone,1,8) not in (";

    if ( $DB == 1 ) {
        print "8-digit check complete.  Elapsed time: "
          . ( $current_time - $start_time )
          . " sec\n";
    }
    while ( @row = $rslt->fetchrow_array ) {
        $ch6 = substr( $row[0], 0, 6 );
        $ch7 = substr( $row[0], 0, 7 );
        if (   $row[1] == 100
            && !$six_digit_wired_hash{$ch6}
            && !$seven_digit_wired_hash{$ch7} )
        {
            $exc_clause8 .= "$row[0],";
            $eight_digit_wired_hash{ $row[0] }++;
        }
        else {
            last;
        }
    }
    $exc_clause8 =~ s/,$//gi;
    $exc_clause8 .= ")";
    if ( length($exc_clause8) < 30 ) {
        $exc_clause8 = "";
    }
    else {
        $exc_clause_prefix = " where ";
        $exc_clause .= " and $exc_clause8";
    }
    if ( $DB == 1 ) {
        print "Prefixes: " . scalar( keys %eight_digit_wired_hash ) . "\n";
    }
    $exc_clause =~ s/^\sand\s//gi;
    $stmt =
"select substr(phone,1,9) as prefix, count(*) as ct from nanpa_wireless_to_wired "
      . $exc_clause_prefix
      . $exc_clause
      . " group by prefix order by ct desc";
    $rslt = $dbhN->prepare($stmt);
    $rslt->execute();
    %nine_digit_wired_hash = ();
    $current_time          = time;
    $exc_clause9           = "substr(phone,1,9) not in (";

    if ( $DB == 1 ) {
        print "9-digit check complete.  Elapsed time: "
          . ( $current_time - $start_time )
          . " sec\n";
    }
    while ( @row = $rslt->fetchrow_array ) {
        $ch6 = substr( $row[0], 0, 6 );
        $ch7 = substr( $row[0], 0, 7 );
        $ch8 = substr( $row[0], 0, 8 );
        if (   $row[1] == 10
            && !$six_digit_wired_hash{$ch6}
            && !$seven_digit_wired_hash{$ch7}
            && !$eight_digit_wired_hash{$ch8} )
        {
            $exc_clause9 .= "$row[0],";
            $nine_digit_wired_hash{ $row[0] }++;
        }
        else {
            last;
        }
    }
    $exc_clause9 =~ s/,$//gi;
    $exc_clause9 .= ")";
    if ( length($exc_clause9) < 30 ) {
        $exc_clause9 = "";
    }
    else {
        $exc_clause_prefix = " where ";
        $exc_clause .= " and $exc_clause9";
    }
    if ( $DB == 1 ) {
        print "Prefixes: " . scalar( keys %nine_digit_wired_hash ) . "\n";
    }
    $stmt =
        "select phone from nanpa_wireless_to_wired "
      . $exc_clause_prefix
      . $exc_clause;
    $rslt = $dbhN->prepare($stmt);
    $rslt->execute();
    %ten_digit_wired_hash = ();
    while ( @row = $rslt->fetchrow_array ) {
        $ch6 = substr( $row[0], 0, 6 );
        $ch7 = substr( $row[0], 0, 7 );
        $ch8 = substr( $row[0], 0, 8 );
        $ch9 = substr( $row[0], 0, 9 );
        if (   !$six_digit_wired_hash{$ch6}
            && !$seven_digit_wired_hash{$ch7}
            && !$eight_digit_wired_hash{$ch8}
            && !$nine_digit_wired_hash{$ch9} )
        {
            $ten_digit_wired_hash{ $row[0] }++;
        }
    }
    if ( $DB == 1 ) {
        print "10-digit prefixes: "
          . scalar( keys %ten_digit_wired_hash ) . "\n";
    }
    $end_time = time;
    if ( !$Q ) {
        print "Elapsed time: " . ( $end_time - $start_time ) . " sec\n\n\n";
    }
}