#!/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!
# * ======================================== */
( $sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $isdst ) =
  localtime(time);
$year = ( $year + 1900 );
$mon++;
if ( $hour < 10 ) { $hour = "0$hour"; }
if ( $min < 10 )  { $min  = "0$min"; }
if ( $sec < 10 )  { $sec  = "0$sec"; }
if ( $mon < 10 )  { $mon  = "0$mon"; }
if ( $mday < 10 ) { $mday = "0$mday"; }
$FTPdate    = "$year-$mon-$mday";
$timestamp  = "$year$mon$mday$hour$min$sec";
$PATHconf   = '/etc/astguiclient.conf';
$FTPdb      = 0;
$FTPpassive = 0;
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 =~ /^PATHDONEmonitor/ ) && ( $CLIDONEmonitor < 1 ) ) {
        $PATHDONEmonitor = $line;
        $PATHDONEmonitor =~ 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;
    }
    if ( ( $line =~ /^VARFTP_host/ ) && ( $CLIFTP_host < 1 ) ) {
        $VARFTP_host = $line;
        $VARFTP_host =~ s/.*=//gi;
    }
    if ( ( $line =~ /^VARFTP_user/ ) && ( $CLIFTP_user < 1 ) ) {
        $VARFTP_user = $line;
        $VARFTP_user =~ s/.*=//gi;
    }
    if ( ( $line =~ /^VARFTP_pass/ ) && ( $CLIFTP_pass < 1 ) ) {
        $VARFTP_pass = $line;
        $VARFTP_pass =~ s/.*=//gi;
    }
    if ( ( $line =~ /^VARFTP_port/ ) && ( $CLIFTP_port < 1 ) ) {
        $VARFTP_port = $line;
        $VARFTP_port =~ s/.*=//gi;
    }
    if ( ( $line =~ /^VARFTP_dir/ ) && ( $CLIFTP_dir < 1 ) ) {
        $VARFTP_dir = $line;
        $VARFTP_dir =~ s/.*=//gi;
    }
    if ( ( $line =~ /^VARHTTP_path/ ) && ( $CLIHTTP_path < 1 ) ) {
        $VARHTTP_path = $line;
        $VARHTTP_path =~ s/.*=//gi;
    }
    $i++;
}
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 "  [--help] = this screen\n";
        print "  [--debug] = debug\n";
        print "  [--debugX] = super debug\n";
        print "  [--FTPdb] = show FTP debug output\n";
        print
"  [--test] = will execute script and transfer files, but not delete lists from dialer\n";
        print
"  [--container_id=XXX] = System settings container to pull info from\n";
        print "\n";
        exit;
    }
    else {
        if ( $args =~ /--debug/i ) {
            $DB = 1;
            print "\n----- DEBUG -----\n\n";
        }
        if ( $args =~ /--debugX/i ) {
            $DBX = 1;
            print "\n----- SUPER DEBUG -----\n\n";
        }
        if ( $args =~ /--FTPdb/i ) {
            $FTPdb = 1;
            print "\n----- FTP DEBUG -----\n\n";
        }
        if ( $args =~ /--test/i ) {
            $T = 1;
            print "\n----- TEST EXECUTION -----\n\n";
        }
        if ( $args =~ /--container_id/i ) {
            my @data_in = split( /--container_id=/, $args );
            $container_id = $data_in[1];
            $container_id =~ s/ .*//gi;
            if ($DB) {
                print "\n----- SYSTEM CONTAINER: $container_id -----\n\n";
            }
        }
    }
}
if ( !-d "$PATHweb/vicidial/export_bkups" ) {
    `mkdir $PATHweb/vicidial/export_bkups`;
    if ( !-d "$PATHweb/vicidial/export_bkups" ) {
        print
"Directory $PATHweb/vicidial/export_bkups does not exist and can't be created.\n";
        exit;
    }
}
if ( !$container_id ) {
    print "Missing required container_id variable\n";
    exit;
}
$server_ip = $VARserver_ip;    # Asterisk server IP
if ( !$VARDB_port ) { $VARDB_port = '3306'; }
use Time::HiRes ( 'gettimeofday', 'usleep', 'sleep' )
  ;    # necessary to have perl sleep command of less than one second
use DBI;
$dbhA = DBI->connect( "DBI:mysql:$VARDB_database:$VARDB_server:$VARDB_port",
    "$VARDB_user", "$VARDB_pass" )
  or die "Couldn't connect to database: " . DBI->errstr;
$dbhB = DBI->connect( "DBI:mysql:$VARDB_database:$VARDB_server:$VARDB_port",
    "$VARDB_user", "$VARDB_pass" )
  or die "Couldn't connect to database: " . DBI->errstr;
$dbhC = DBI->connect( "DBI:mysql:$VARDB_database:$VARDB_server:$VARDB_port",
    "$VARDB_user", "$VARDB_pass" )
  or die "Couldn't connect to database: " . DBI->errstr;
$dbhD = DBI->connect( "DBI:mysql:$VARDB_database:$VARDB_server:$VARDB_port",
    "$VARDB_user", "$VARDB_pass" )
  or die "Couldn't connect to database: " . DBI->errstr;
$header_stmt = "show columns from vicidial_list";
$header_rslt = $dbhA->prepare($header_stmt);
$header_rslt->execute();
$CSV_header = "";

while ( @header_row = $header_rslt->fetchrow_array ) {
    $CSV_header .= "\"$header_row[0]\",";
}
$stmt =
"select * from vicidial_settings_containers where container_id='$container_id' and container_type='PERL_CLI'";
if ($DBX) { print "\n$stmt\n\n"; }
$rslt = $dbhA->prepare($stmt);
$rslt->execute();
if ( $rslt->rows > 0 ) {
    @row = $rslt->fetchrow_array;
    $row[4] =~ s/\r//g;
    @container_settings = split( /\n/, $row[4] );
    for ( $i = 0 ; $i < scalar(@container_settings) ; $i++ ) {
        $args = $container_settings[$i];
        if ( $args =~ /--campaign_id/i ) {
            my @data_in = split( /--campaign_id=/, $args );
            $campaign_id = $data_in[1];
            $campaign_id =~ s/ .*//gi;
            if ($DB) { print "\n----- CAMPAIGN ID: $campaign_id -----\n\n"; }
        }
        if ( $args =~ /--last_call_date_days\=/i ) {
            my @data_in = split( /--last_call_date_days=/, $args );
            $last_call_date_days = $data_in[1];
            $last_call_date_days =~ s/ .*//gi;
            $last_call_date_days =~ s/[^0-9|(now)]//gi;
            if ($DB) {
                print
"\n----- LAST CALL DATE EXPIRATION DAYS: $last_call_date_days -----\n\n";
            }
        }
        if ( $args =~ /--last_call_date_days_export/i ) {
            my @data_in = split( /--last_call_date_days_export=/, $args );
            $last_call_date_days_export = $data_in[1];
            $last_call_date_days_export =~ s/ .*//gi;
            $last_call_date_days_export =~ s/[^0-9|(now)]//gi;
            if ($DB) {
                print
"\n----- LAST CALL DATE EXPIRATION DAYS, EXPORT ONLY: $last_call_date_days_export -----\n\n";
            }
        }
        if ( $args =~ /--last_call_date_days_delete/i ) {
            my @data_in = split( /--last_call_date_days_delete=/, $args );
            $last_call_date_days_delete = $data_in[1];
            $last_call_date_days_delete =~ s/ .*//gi;
            $last_call_date_days_delete =~ s/[^0-9|(now)]//gi;
            if ($DB) {
                print
"\n----- LAST CALL DATE EXPIRATION DAYS, DELETE ONLY: $last_call_date_days_delete -----\n\n";
            }
        }
        if ( $args =~ /--custom_ftp_host/i ) {
            my @data_in = split( /--custom_ftp_host=/, $args );
            $custom_ftp_host = $data_in[1];
            $custom_ftp_host =~ s/ .*//gi;
            if ($DBX) { print "\n----- FTP HOST: $custom_ftp_host -----\n\n"; }
        }
        if ( $args =~ /--custom_ftp_port/i ) {
            my @data_in = split( /--custom_ftp_port=/, $args );
            $custom_ftp_port = $data_in[1];
            $custom_ftp_port =~ s/ .*//gi;
            $custom_ftp_port =~ s/[^0-9]//gi;
            if ($DBX) { print "\n----- FTP PORT: $custom_ftp_port -----\n\n"; }
        }
        if ( $args =~ /--custom_ftp_user/i ) {
            my @data_in = split( /--custom_ftp_user=/, $args );
            $custom_ftp_user = $data_in[1];
            $custom_ftp_user =~ s/ .*//gi;
            if ($DBX) { print "\n----- FTP USER: $custom_ftp_user -----\n\n"; }
        }
        if ( $args =~ /--ftp_passive/i ) {
            my @data_in = split( /--ftp_passive=/, $args );
            $FTPpassive = $data_in[1];
            $FTPpassive =~ s/ .*//gi;
            $FTPpassive =~ s/[^0-9]//gi;
            if ( $FTPpassive != 0 ) { $FTPpassive = 1; }
            if ($DBX) { print "\n----- FTP PASSIVE: $FTPpassive -----\n\n"; }
        }
        if ( $args =~ /--custom_ftp_pwd/i ) {
            my @data_in = split( /--custom_ftp_pwd=/, $args );
            $custom_ftp_pwd = $data_in[1];
            $custom_ftp_pwd =~ s/ .*//gi;
            if ($DBX) {
                print "\n----- FTP PASSWORD: $custom_ftp_pwd -----\n\n";
            }
        }
        if ( $args =~ /--ftp_persistent/i ) {
            $FTPpersistent = 1;
            if ($DB) { print "\n----- FTP PERSISTENT  -----\n\n"; }
        }
        if ( $args =~ /--destination_folder/i ) {
            my @data_in = split( /--destination_folder=/, $args );
            $destination_folder = $data_in[1];
            $destination_folder =~ s/ .*//gi;
            $destination_folder =~ s/-------/ /gi;
            if ($DB) {
                print
                  "\n----- DESTINATION FOLDER: $destination_folder -----\n\n";
            }
        }
    }
    if ( !$last_call_date_days_export ) {
        $last_call_date_days_export = $last_call_date_days;
    }
    if ( !$last_call_date_days_delete ) {
        $last_call_date_days_delete = $last_call_date_days;
    }
    if ( !$campaign_id ) {
        print "Script failed - no campaign to pull from.\n";
        exit;
    }
    if ( !$last_call_date_days_export ) {
        print "Script failed - no last call date range given.\n";
        exit;
    }
    if ( !$custom_ftp_host || !$custom_ftp_user || !$custom_ftp_pwd ) {
        print "Script failed - missing FTP info\n";
        exit;
    }
    if ( $last_call_date_days_export =~ /now/i ) {
        $last_call_date_days_export = 0;
        if ( $hour > 5 ) {
            print "Script failed - cannot run 'now' date range after 6am.\n";
            exit;
        }
    }
    if ( $last_call_date_days_delete =~ /now/i ) {
        $last_call_date_days_delete = 0;
        if ( $hour > 5 ) {
            print "Script failed - cannot run 'now' date range after 6am.\n";
            exit;
        }
    }
    if ( !$custom_ftp_port ) { $custom_ftp_port = 21; }
    $list_export_stmt =
"select list_id from vicidial_lists where active='N' and campaign_id='$campaign_id' and list_lastcalldate<=now()-INTERVAL $last_call_date_days_export DAY";
    if ($DBX) { print "\n$list_export_stmt\n\n"; }
    $list_rslt = $dbhB->prepare($list_export_stmt);
    $list_rslt->execute();
    $lists_to_export = $list_rslt->rows;
    if ( $lists_to_export > 0 ) {
        use Net::Ping;
        use Net::FTP;
        $p         = Net::Ping->new();
        $ping_good = $p->ping("$VARFTP_host");
        if ( !$ping_good ) {
            $p         = Net::Ping->new("icmp");
            $ping_good = $p->ping("$VARFTP_host");
        }
        if ($ping_good) {
            $ftp = Net::FTP->new(
                "$custom_ftp_host",
                Port    => $custom_ftp_port,
                Passive => $FTPpassive,
                Debug   => $FTPdb
            );
            $ftp->login( "$custom_ftp_user", "$custom_ftp_pwd" );
            if ($destination_folder) { $ftp->cwd("$destination_folder"); }
        }
        else {
            if ($DB) { print "ERROR: Could not ping server $VARFTP_host\n"; }
            exit;
        }
    }
    else {
        if ($DB) { print "No lists to export\n"; }
    }
    $l = 0;
    while ( $l < $lists_to_export ) {
        @list_row        = $list_rslt->fetchrow_array;
        $list_id         = $list_row[0];
        $export_filename = "EXPORT_" . $list_id . "_" . $timestamp . ".csv";
        $custom_tbl_stmt = "show tables like 'custom_" . $list_id . "'";
        if ($DBX) { print "\n$custom_tbl_stmt\n"; }
        $custom_tbl_rslt = $dbhC->prepare($custom_tbl_stmt);
        $custom_tbl_rslt->execute();
        $CSV_custom_header = "";

        if ( $custom_tbl_rslt->rows == 1 ) {
            $pull_custom_info = 1;
            $col_stmt         = "show columns from custom_" . $list_id;
            $col_rslt         = $dbhD->prepare($col_stmt);
            $col_rslt->execute();
            while ( @col_row = $col_rslt->fetchrow_array ) {
                $CSV_custom_header .= "\"$col_row[0]\",";
            }
            $col_rslt->finish;
        }
        $custom_tbl_rslt->finish;
        $CSV_full_header = $CSV_header . $CSV_custom_header;
        $CSV_full_header =~ s/,$//;
        open( EXPORT, "> $PATHweb/vicidial/export_bkups/$export_filename" );
        print EXPORT $CSV_full_header . "\n";
        $exp_stmt =
"select * from vicidial_list where list_id='$list_id' order by lead_id asc";
        if ($DBX) { print "\n$exp_stmt\n"; }
        $exp_rslt = $dbhC->prepare($exp_stmt);
        $exp_rslt->execute();

        while ( @exp_row = $exp_rslt->fetchrow_array ) {
            $CSV_data = "";
            $lead_id  = $exp_row[0];
            for ( $q = 0 ; $q < scalar(@exp_row) ; $q++ ) {
                $CSV_data .= "\"$exp_row[$q]\",";
            }
            if ($pull_custom_info) {
                $cdata_stmt =
                    "select * from custom_"
                  . $list_id
                  . " where lead_id='$lead_id'";
                if ($DBX) { print "\n$cdata_stmt\n"; }
                $cdata_rslt = $dbhD->prepare($cdata_stmt);
                $cdata_rslt->execute();
                if ( $cdata_rslt->rows > 0 ) {
                    @cdata_row = $cdata_rslt->fetchrow_array;
                    for ( $c = 1 ; $c < scalar(@cdata_row) ; $c++ ) {
                        $CSV_data .= "\"$cdata_row[$c]\",";
                    }
                }
                $cdata_rslt->finish;
            }
            $CSV_data =~ s/,$//;
            print EXPORT $CSV_data . "\n";
        }
        $exp_rslt->finish;
        close(EXPORT);
        $ExportFileSize =
          ( -s "$PATHweb/vicidial/export_bkups/$export_filename" );
        $ftp->binary();
        $ftp->put( "$PATHweb/vicidial/export_bkups/$export_filename",
            "$export_filename" );
        $FTPfilesize = $ftp->size("$export_filename");
        if ($DBX) {
            print STDERR
"FTP FILESIZE:   $FTPfilesize/$ExportFileSize | $export_filename\n";
        }
        if ( !$FTPfilesize
            || ( ( $ExportFileSize > 100 ) && ( $FTPfilesize < 100 ) ) )
        {
            print
"ERROR! File did not transfer, exiting:   $FTPfilesize / $ExportFileSize | $export_filename\n";
            exit;
        }
        $l++;
    }
    $list_rslt->finish;
    if ( $last_call_date_days_delete < $last_call_date_days_export ) {
        $last_call_date_days_delete = $last_call_date_days_export;
        print
"WARNING! Current settings delete lists before exporting - delete day range defaulting to export day range of $last_call_date_days_export day(s)\n";
    }
    $list_delete_stmt =
"select list_id from vicidial_lists where active='N' and campaign_id='$campaign_id' and list_lastcalldate<=now()-INTERVAL $last_call_date_days_delete DAY";
    if ($DBX) { print "\n$list_delete_stmt\n\n"; }
    if ( !$T ) {
        $list_rslt = $dbhB->prepare($list_delete_stmt);
        $list_rslt->execute();
        $lists_to_delete = $list_rslt->rows;
        $l               = 0;
        while ( $l < $lists_to_delete ) {
            @list_row = $list_rslt->fetchrow_array;
            $list_id  = $list_row[0];
            $del_stmt = "delete from vicidial_list where list_id='$list_id'";
            if ($DBX) { print "\n\n$del_stmt"; }
            $del_rslt = $dbhC->prepare($del_stmt);
            $del_rslt->execute();
            $del_rslt->finish;
            $del_stmt = "delete from vicidial_lists where list_id='$list_id'";
            if ($DBX) { print "\n$del_stmt"; }
            $del_rslt = $dbhC->prepare($del_stmt);
            $del_rslt->execute();
            $del_rslt->finish;
            $del_stmt = "delete from vicidial_hopper where list_id='$list_id'";
            if ($DBX) { print "\n$del_stmt\n\n"; }
            $del_rslt = $dbhC->prepare($del_stmt);
            $del_rslt->execute();
            $del_rslt->finish;
            $l++;
        }
    }
}
else {
    if ($DB) {
        print
          "No setting container \"$conatiner_id\" of type PERL_CLI found.\n";
    }
    exit;
}
$rslt->finish;