#!/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!
# * ======================================== */
use Spreadsheet::WriteExcel;
use Time::Local;
use DBI;
$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++;
}
$server_ip = $VARserver_ip;    # Asterisk server IP
if ( !$VARDB_port ) { $VARDB_port = '3306'; }
$dbhA = DBI->connect( "DBI:mysql:$VARDB_database:$VARDB_server:$VARDB_port",
    "$VARDB_user", "$VARDB_pass" )
  or die "Couldn't connect to database: " . DBI->errstr;
$list_ids       = $ARGV[0];
$sales_number   = $ARGV[1];
$timestamp      = $ARGV[2];
$forc           = $ARGV[3];
$now            = $ARGV[4];
$dcampaign      = $ARGV[5];
$list_id_clause = "and v.list_id in (";
@lists          = split( /\,/, $list_ids );

for ( $i = 0 ; $i < scalar(@lists) ; $i++ ) {
    if ( length( $lists[$i] > 0 ) ) { $list_id_clause .= "$lists[$i], "; }
}
$list_id_clause = substr( $list_id_clause, 0, -2 );
$list_id_clause .= ")";
if ( $sales_number == 0 ) { undef $sales_number; }
$xl = Spreadsheet::WriteExcel->new(
    "$PATHweb/vicidial/vicidial_closer_report_$now.xls");
$xlsheet = $xl->add_worksheet();
$xlsheet->set_landscape();
$rptheader = $xl->add_format();    # Add a format
$rptheader->set_bold();
$rptheader->set_size('8');
$rptheader->set_color('10');
$rptheader->set_align('center');
$rptheaderLEFT = $xl->add_format();    # Add a format
$rptheaderLEFT->set_bold();
$rptheaderLEFT->set_size('8');
$rptheaderLEFT->set_color('10');
$rptheaderLEFT->set_align('left');
$normcell = $xl->add_format();         # Add a format
$normcell->set_size('8');
$normcell->set_align('center');
$normcell->set_bg_color('22');
$boldcell = $xl->add_format();         # Add a format
$boldcell->set_bold();
$boldcell->set_size('8');
$boldcell->set_align('center');
$boldcell->set_bg_color('22');
$dollarformat = $xl->add_format();     # Add a format
$dollarformat->set_size('8');
$dollarformat->set_num_format('$0.00');
$intformat = $xl->add_format();        # Add a format
$intformat->set_size('8');
$intformat->set_num_format('0');
$numberformat = $xl->add_format();     # Add a format
$numberformat->set_size('8');
$numberformat->set_num_format('0.00');
$pformat = $xl->add_format();          # Add a format
$pformat->set_size('8');
$pformat->set_num_format('0.00%');
$p2format = $xl->add_format();         # Add a format
$p2format->set_size('8');
$p2format->set_num_format('0%');
$statcell = $xl->add_format( num_format => '@' );    # Add a format
$statcell->set_size('8');
$countcell = $xl->add_format();                      # Add a format
$countcell->set_size('8');
$countcell->set_bg_color('35');
$terminateheader = $xl->add_format();                # Add a format
$terminateheader->set_bold();
$terminateheader->set_size('8');
$terminateheader->set_color('10');
$terminateheader->set_num_format('0.00');
$xlsheet->write( "B1", "Closer stats",         $rptheader );
$xlsheet->write( "D1", "$dcampaign",           $rptheaderLEFT );
$xlsheet->write( "F1", "$list_ids",            $rptheaderLEFT );
$xlsheet->write( "A2", "Closer Name",          $rptheader );
$xlsheet->write( "B2", "# of calls",           $rptheader );
$xlsheet->write( "C2", "# of sales",           $rptheader );
$xlsheet->write( "D2", "Sales percentage",     $rptheader );
$xlsheet->write( "E2", "Calls per hour",       $rptheader );
$xlsheet->write( "F2", "Sales per hour",       $rptheader );
$xlsheet->write( "G2", "Total time in system", $rptheader );
%closers  = ();
%fronters = ();
$sales    = 0;
$stmtA =
"select v.status, u.full_name, u.user from vicidial_users u, vicidial_list v, vicidial_log vl where vl.call_date>='$timestamp' and vl.call_date<='$now' and vl.lead_id=v.lead_id $list_id_clause and vl.user=u.user;";
$sthA = $dbhA->prepare($stmtA) or die "preparing: ",        $dbhA->errstr;
$sthA->execute                 or die "executing: $stmtA ", $dbhA->errstr;
$sthArows  = $sthA->rows;
$rec_count = 0;

while ( $sthArows > $rec_count ) {
    @aryA = $sthA->fetchrow_array;
    if ( length($sales_number) == 0 || $sales < $sales_number ) {
        $closers{"$aryA[1]"}[0]++;
        $fronters{"$aryA[1]"}[0]++;
        if ( $aryA[0] eq "SALE" ) {
            $sales++;
            $closers{"$aryA[1]"}[1]++;
            $fronters{"$aryA[1]"}[1]++;
        }
    }
    $closers{"$aryA[1]"}[2]  = $aryA[2];
    $fronters{"$aryA[1]"}[2] = $aryA[2];
    $rec_count++;
}
$sthA->finish();
if ( $forc eq "F" ) {
    %closers = ();
    $sales   = 0;
    $stmtA =
"select v.status, u.full_name, u.user from vicidial_list v, vicidial_users u, vicidial_xfer_log vl where vl.call_date>='$timestamp' and vl.call_date<='$now' and vl.lead_id=v.lead_id $list_id_clause and vl.closer=u.user;";
    $sthA = $dbhA->prepare($stmtA) or die "preparing: ",        $dbhA->errstr;
    $sthA->execute                 or die "executing: $stmtA ", $dbhA->errstr;
    $sthArows  = $sthA->rows;
    $rec_count = 0;
    while ( $sthArows > $rec_count ) {
        @aryA = $sthA->fetchrow_array;
        if ( !$sales_number || $sales < $sales_number ) {
            $closers{"$aryA[1]"}[0]++;
            if ( $aryA[0] eq "SALE" ) {
                $sales++;
                $closers{"$aryA[1]"}[1]++;
            }
        }
        $closers{"$aryA[1]"}[2] = $aryA[2];
        $rec_count++;
    }
    $sthA->finish();
}
$x                = 3;
$grand_total_time = 0;
foreach $closername ( sort( keys(%closers) ) ) {
    $closers{$closername}[0] += 0;
    $closers{$closername}[1] += 0;
    $stmtA =
"select sum(pause_sec+wait_sec+talk_sec), sec_to_time(sum(pause_sec+wait_sec+talk_sec)) from vicidial_agent_log where user="
      . $closers{$closername}[2]
      . " and event_time>='$timestamp' and event_time<='$now' and pause_sec<28800 and wait_sec<28800 and talk_sec<28800;";
    $sthA = $dbhA->prepare($stmtA) or die "preparing: ",        $dbhA->errstr;
    $sthA->execute                 or die "executing: $stmtA ", $dbhA->errstr;
    $sthArows  = $sthA->rows;
    $rec_count = 0;
    while ( $sthArows > $rec_count ) {
        @aryA  = $sthA->fetchrow_array;
        $hours = ( $aryA[0] / 3600 );
        $grand_total_time += $aryA[0];
        $total_time = $aryA[1];
        $rec_count++;
    }
    $sthA->finish();
    $xlsheet->write( "A$x", "$closername",                 $normcell );
    $xlsheet->write( "B$x", "=$closers{$closername}[0]+0", $intformat );
    $xlsheet->write( "C$x", "=$closers{$closername}[1]+0", $intformat );
    $xlsheet->write( "D$x", "=C$x/B$x",                    $pformat );
    $xlsheet->write( "E$x", "=B$x/$hours",                 $numberformat );
    $xlsheet->write( "F$x", "=C$x/$hours",                 $numberformat );
    $xlsheet->write( "G$x", "$total_time",                 $normcell );
    $x++;
}
$hours = ( $grand_total_time / 3600 );
$stmtA = "select sec_to_time($grand_total_time)";
$sthA  = $dbhA->prepare($stmtA) or die "preparing: ", $dbhA->errstr;
$sthA->execute or die "executing: $stmtA ", $dbhA->errstr;
$sthArows  = $sthA->rows;
$rec_count = 0;
while ( $sthArows > $rec_count ) {
    @aryA             = $sthA->fetchrow_array;
    $grand_total_time = $aryA[0];
    $rec_count++;
}
$sthA->finish();
$y = ( $x + 1 );
$xlsheet->write( "A$y", "Grand Total",       $boldcell );
$xlsheet->write( "B$y", "=SUM(B3:B$x)",      $intformat );
$xlsheet->write( "C$y", "=SUM(C3:C$x)",      $intformat );
$xlsheet->write( "D$y", "=C$y/B$y",          $pformat );
$xlsheet->write( "E$y", "=B$y/$hours",       $numberformat );
$xlsheet->write( "F$y", "=C$y/$hours",       $numberformat );
$xlsheet->write( "G$y", "$grand_total_time", $normcell );
$xl->close();

if ( $forc eq "F" ) {
    $xl = Spreadsheet::WriteExcel->new(
        "$PATHweb/vicidial/vicidial_fronter_report_$now.xls");
    $xlsheet = $xl->add_worksheet();
    $xlsheet->set_landscape();
    $rptheader = $xl->add_format();    # Add a format
    $rptheader->set_bold();
    $rptheader->set_size('8');
    $rptheader->set_color('10');
    $rptheader->set_align('center');
    $normcell = $xl->add_format();     # Add a format
    $normcell->set_size('8');
    $normcell->set_align('center');
    $normcell->set_bg_color('22');
    $boldcell = $xl->add_format();     # Add a format
    $boldcell->set_bold();
    $boldcell->set_size('8');
    $boldcell->set_align('center');
    $boldcell->set_bg_color('22');
    $dollarformat = $xl->add_format();    # Add a format
    $dollarformat->set_size('8');
    $dollarformat->set_num_format('$0.00');
    $intformat = $xl->add_format();       # Add a format
    $intformat->set_size('8');
    $intformat->set_num_format('0');
    $numberformat = $xl->add_format();    # Add a format
    $numberformat->set_size('8');
    $numberformat->set_num_format('0.00');
    $pformat = $xl->add_format();         # Add a format
    $pformat->set_size('8');
    $pformat->set_num_format('0.00%');
    $statcell = $xl->add_format( num_format => '@' );    # Add a format
    $statcell->set_size('8');
    $countcell = $xl->add_format();                      # Add a format
    $countcell->set_size('8');
    $countcell->set_bg_color('35');
    $terminateheader = $xl->add_format();                # Add a format
    $terminateheader->set_bold();
    $terminateheader->set_size('8');
    $terminateheader->set_color('10');
    $terminateheader->set_num_format('0.00');
    $xlsheet->write( "D1", "Fronter stats",        $rptheader );
    $xlsheet->write( "A2", "Fronter Name",         $rptheader );
    $xlsheet->write( "B2", "# of calls",           $rptheader );
    $xlsheet->write( "C2", "# of sales",           $rptheader );
    $xlsheet->write( "D2", "Sales percentage",     $rptheader );
    $xlsheet->write( "E2", "Calls per hour",       $rptheader );
    $xlsheet->write( "F2", "Sales per hour",       $rptheader );
    $xlsheet->write( "G2", "Total time in system", $rptheader );
    $x                = 3;
    $grand_total_time = 0;

    foreach $tsrname ( sort( keys(%fronters) ) ) {
        $fronters{$tsrname}[0] += 0;
        $fronters{$tsrname}[1] += 0;
        $stmtA =
"select sum(pause_sec+wait_sec+talk_sec), sec_to_time(sum(pause_sec+wait_sec+talk_sec)) from vicidial_agent_log where user="
          . $fronters{$tsrname}[2]
          . " and event_time>='$timestamp' and event_time<='$now' and pause_sec<28800 and wait_sec<28800 and talk_sec<28800;";
        $sthA = $dbhA->prepare($stmtA) or die "preparing: ", $dbhA->errstr;
        $sthA->execute or die "executing: $stmtA ",          $dbhA->errstr;
        $sthArows  = $sthA->rows;
        $rec_count = 0;
        while ( $sthArows > $rec_count ) {
            @aryA  = $sthA->fetchrow_array;
            $hours = ( $aryA[0] / 3600 );
            $grand_total_time += $aryA[0];
            $total_time = $aryA[1];
            $rec_count++;
        }
        $sthA->finish();
        $xlsheet->write( "A$x", "$tsrname",                  $normcell );
        $xlsheet->write( "B$x", "=$fronters{$tsrname}[0]+0", $intformat );
        $xlsheet->write( "C$x", "=$fronters{$tsrname}[1]+0", $intformat );
        $xlsheet->write( "D$x", "=C$x/B$x",                  $pformat );
        $xlsheet->write( "E$x", "=B$x/$hours",               $numberformat );
        $xlsheet->write( "F$x", "=C$x/$hours",               $numberformat );
        $xlsheet->write( "G$x", "$total_time",               $normcell );
        $x++;
    }
    $hours = ( $grand_total_time / 3600 );
    $stmtA = "select sec_to_time($grand_total_time)";
    $sthA  = $dbhA->prepare($stmtA) or die "preparing: ", $dbhA->errstr;
    $sthA->execute or die "executing: $stmtA ", $dbhA->errstr;
    $sthArows  = $sthA->rows;
    $rec_count = 0;
    while ( $sthArows > $rec_count ) {
        @aryA             = $sthA->fetchrow_array;
        $grand_total_time = $aryA[0];
        $rec_count++;
    }
    $sthA->finish();
    $y = ( $x + 1 );
    $xlsheet->write( "A$y", "Grand Total",       $boldcell );
    $xlsheet->write( "B$y", "=SUM(B3:B$x)",      $intformat );
    $xlsheet->write( "C$y", "=SUM(C3:C$x)",      $intformat );
    $xlsheet->write( "D$y", "=C$y/B$y",          $pformat );
    $xlsheet->write( "E$y", "=B$y/$hours",       $numberformat );
    $xlsheet->write( "F$y", "=C$y/$hours",       $numberformat );
    $xlsheet->write( "G$y", "$grand_total_time", $normcell );
    $xl->close();
}