#!/usr/bin/perl

#################################################################
################## Written by: Matthew Harris ###################
#################################################################
# optincheck                                                    #
# Determine opt-in status                           #
# http://git.toolbox.hostgator.com/optincheck                   #
# Please submit all bug reports at bugs.hostgator.com           #
#                                                               #
# (C) 2011 - HostGator.com, LLC                                 #
#################################################################

use warnings;
use strict;
use DBI;
use File::Find::Rule;
use File::Find;
use Getopt::Long;
use Math::Round;
use lib '/usr/local/cpanel';
use Cpanel::PublicAPI;

_options();

sub _options {
    if ( !defined( $ARGV[0] ) ) { _usage(); }
    my ( $help, $phplist, $arp, $inter, $user, $joomla );
    GetOptions(
        'help|usage|h' => \$help,
        'phplist|p'    => \$phplist,
        'arp3|a'       => \$arp,
        'interspire|i' => \$inter,
        'joomla|j'     => \$joomla,
        'user|u'       => \$user,
    );
    my $db = $ARGV[0];
    if ($help) {
        _usage();
    }
    if ($phplist) {
        _runpl($db);
    }
    if ($arp) {
        _runarp($db);
    }
    if ($inter) {
        _runinter($db);
    }
    if ($joomla) {
        _runjoom($db);
    }
    if ($user) {
        _findsoft($db);
    }
}

sub _runpl {
    my $db = shift;
    if ( !defined($db) ) { _usage(); exit; }
    my ( @confirmed, @bouncebacks, @ips, @summary, @invalid, @randlist );
    my ( $subject, $sendstart, $processed, $bouncecount, $website );
    my $dbh      = _connect_db($db);
    my $ginfo = $dbh->prepare(
"SELECT confirmed from phplist_user_user where disabled=0 and blacklisted=0;"
    );
    $ginfo->execute() or die "Database issue: $!";

    while ( my $data = $ginfo->fetchrow_hashref() ) {
        push( @confirmed, $data->{'confirmed'} );
    }
    $ginfo = $dbh->prepare(
"SELECT bouncecount from phplist_user_user where disabled=0 and blacklisted=0;"
    );
    $ginfo->execute() or die "Database issue: $!";
    while ( my $data = $ginfo->fetchrow_hashref() ) {
        push( @bouncebacks, $data->{'bouncecount'} );
    }
    $ginfo = $dbh->prepare("SELECT ip,summary from phplist_user_user_history;");
    $ginfo->execute() or die "Database issue: $!";
    while ( my $data = $ginfo->fetchrow_hashref() ) {
        push( @ips,     $data->{'ip'} );
        push( @summary, $data->{'summary'} );
    }
    $ginfo = $dbh->prepare(
        "SELECT entry from phplist_eventlog where entry like 'Invalid%';");
    $ginfo->execute() or die "Database issue: $!";
    while ( my $data = $ginfo->fetchrow_hashref() ) {
        push( @invalid, $data->{'entry'} );
    }
    $ginfo = $dbh->prepare(
"SELECT subject,processed,sendstart,bouncecount from phplist_message order by sendstart desc limit 1;"
    );
    $ginfo->execute() or die "Database issue: $!";
    while ( my $data = $ginfo->fetchrow_hashref() ) {
        $subject     = $data->{'subject'};
        $sendstart   = $data->{'sendstart'};
        $processed   = $data->{'processed'};
        $bouncecount = $data->{'bouncecount'};
    }
    $ginfo =
      $dbh->prepare("SELECT value from phplist_config where item = 'website';");
    $ginfo->execute() or die "Database issue: $!";
    while ( my $data = $ginfo->fetchrow_hashref() ) {
        $website = $data->{'value'};
    }
    $ginfo->finish();
    my $docroot;
    if ( $website =~ /\// ) {
        my ( $domain, $dir ) = split( /\//, $website );
        $docroot = _getdoc( $db, $domain, $dir );
    }
    else {
        $docroot = _getdoc( $db, $website );
    }
    my %confset = _getconf($docroot);
    my ( $import, $cbyuser, $unsub, $change, $resub, $subadm, $subd ) =
      _summary( \@summary );
    _display(
        $import,      $cbyuser,    $unsub,        $change,    $resub,
        $subadm,      $subd,       $subject,      $sendstart, $processed,
        $bouncecount, \@confirmed, \@bouncebacks, \@ips,      \@invalid
    );
    _conflogic(%confset);
}

my $count ||= 0;

sub _findsoft {
    my $user = shift;
    if ( !defined($user) ) { _usage(); }
    my $directory = "/home/$user/public_html";
    find( \&wanted, $directory );
    if ( !defined($count) ) { print "[!] No software found.\n"; }
}

sub wanted {
    my ( @phplist, @arp3, @interspire );
    /^lt\.php\z/s          && push( @phplist,    $File::Find::dir );
    /^ut\.php\z/s          && push( @phplist,    $File::Find::dir );
    /^dl\.php\z/s          && push( @phplist,    $File::Find::dir );
    /^arp3-config\.pl\z/s  && push( @arp3,       $File::Find::dir );
    /^arp3-setup\.pl\z/s   && push( @arp3,       $File::Find::dir );
    /^arp3-settings.pl\z/s && push( @arp3,       $File::Find::dir );
    /^addmta\.php\z/s      && push( @interspire, $File::Find::dir );
    /^deletemta\.php\z/s   && push( @interspire, $File::Find::dir );
    /^editmta\.php\z/s     && push( @interspire, $File::Find::dir );
    if ( defined( $phplist[0] ) ) {
        my $dir = $phplist[0];
        foreach my $found (@phplist) {
            if ( $found =~ /$dir/ ) {
                $count++;
            }
        }
        if ( $count == 3 ) {
            _finddb( 'phplist', $dir );
        }
    }
    if ( defined( $arp3[0] ) ) {
        my $dir = $arp3[0];
        foreach my $found (@arp3) {
            if ( $found =~ /$dir/ ) {
                $count++;
            }
        }
        if ( $count == 3 ) {
            _finddb( 'arp3', $dir );
        }
    }
    if ( defined( $interspire[0] ) ) {
        my $dir = $interspire[0];
        foreach my $found (@interspire) {
            if ( $found =~ /$dir/ ) {
                $count++;
            }
        }
        if ( $count == 3 ) {
            _finddb( 'interspire', $dir );
        }
    }
    return $count;
}

sub _finddb {
    my ( $install, $dir ) = @_;
    if ( $install eq 'phplist' ) {
        my @content = _rfile("$dir/config/config.php");
        @content = grep( /^\$database_name/, @content );
        if ( $content[0] =~ /(?<=")(\S+)(?=";)/ ) {
            $content[0] = $1;
        }
        _runpl( $content[0] );
    }
    if ( $install eq 'arp3' ) {
        my @content = _rfile("$dir/arp3-config.pl");
        @content = grep( /^\$db_name/, @content );
        if ( $content[0] =~ /(?<=")(\S+)(?=";)/ ) {
            $content[0] = $1;
        }
        _runarp( $content[0] );
    }
    if ( $install eq 'interspire' ) {
        my @content = _rfile("$dir/includes/config.php");
        @content = grep( /^define\(\'SENDSTUDIO_DATABASE_NAME/, @content );
        if ( $content[0] =~ /(?<=', ')(\S+)(?=')/ ) {
            $content[0] = $1;
        }
        _runinter( $content[0] );
    }
}

sub _rfile {
    my $file = shift;
    open my $fh, '<', $file or die "Could not open $file: $!\n";
    my @content = <$fh>;
    close $fh;
    return @content;
}

sub _getdoc {
    my ( $db, $domain, $dir ) = @_;
    my $accesshash = _getah();
    my $cp =
      Cpanel::PublicAPI->new( 'user' => 'root', 'accesshash' => $accesshash );
    my ( $user, undef ) = split( /_/, $db );
    my $docroot = $cp->cpanel_api2_request(
        'whostmgr',
        {
            'module' => 'DomainLookup',
            'func'   => 'getdocroot',
            'user'   => "$user"
        },
        { 'domain' => "$domain", }
    );
    $docroot = $docroot->{cpanelresult}{data}[0]{docroot};
    if ( defined($dir) ) {
        $docroot = $docroot . '/' . $dir;
    }
    return $docroot;
}

sub _getah {
    open my $hash, "<", "/root/.accesshash";
    my @accesshash = <$hash>;
    close $hash;
    my $accesshash;
    foreach (@accesshash) {
        $accesshash .= $_;
    }
    return $accesshash;
}

sub _usage {
    my $output = <<'EOF';

     Usage: optincheck [OPTION] database_name
            optincheck -u username

     Available options:
       --phplist, -p        Shows PHPlist list info
       --arp3, -a           Shows arp3 list info
       --interspire, -i     Shows InterSpire list info
       --joomla, -j         Shows Joomla AcyMailing list info
       --user, -u           Autofind Database

EOF
    print $output;
    exit;
}

sub _getconf {
    my $docroot = shift;
    my $conf    = $docroot . '/config/config.php';
    my @conf;
    if ( !-f $conf ) {
        @conf = File::Find::Rule->file()->name('config.php')->in($docroot);
    }
    foreach (@conf) {
        if ( $_ =~ /config\/config.php$/ ) {
            $conf = $_;
        }
    }
    my %confset;
    open my $fh, '<', $conf or die "Could not open configuration file\n";
    while (<$fh>) {
        if ( $_ =~ /^define\(\"MAILQUEUE_BATCH_SIZE\",(\d+)\);/ ) {
            $confset{batch_processing} = $1;
        }
        if ( $_ =~ /^define\(\"MAILQUEUE_BATCH_PERIOD\",(\d+)\);/ ) {
            $confset{batch_period} = $1;
        }
        if ( $_ =~ /^define\(\'MAILQUEUE_THROTTLE\',(\d+)\);/ ) {
            $confset{throttle} = $1;
        }
    }
    close $fh;
    return %confset;
}

sub _summary {
    my $summary = shift;
    my ( @import, @cbyuser, @unsub, @change, @resub, @subadm, @subd );
    foreach (@$summary) {
        if ( $_ =~ /Import/ ) {
            push( @import, $_ );
        }
        if ( $_ =~ /Confirmation/ ) {
            push( @cbyuser, $_ );
        }
        if ( $_ =~ /Unsubscription/ ) {
            push( @unsub, $_ );
        }
        if ( $_ =~ /Change/ ) {
            push( @change, $_ );
        }
        if ( $_ =~ /Re-Subscription/ ) {
            push( @resub, $_ );
        }
        if ( $_ =~ /Subscription by/ ) {
            push( @subadm, $_ );
        }
        if ( $_ =~ /^Subscription$/ ) {
            push( @subd, $_ );
        }
    }
    my $import  = @import;
    my $cbyuser = @cbyuser;
    my $unsub   = @unsub;
    my $change  = @change;
    my $resub   = @resub;
    my $subadm  = @subadm;
    my $subd    = @subd;
    return $import, $cbyuser, $unsub, $change, $resub, $subadm, $subd;
}

sub _conflogic {
    my %confset = @_;
    my $perhour;
    if ( !defined( $confset{throttle} ) ) {
        $confset{throttle} = 0;
    }
    if ( $confset{throttle} != 0 ) {
        $perhour = 3600 / $confset{throttle};
    }
    else {
        $perhour = "unset";
    }
    my $seconds = $confset{batch_period};
    my $minutes;
    if ( defined($seconds) ) {
        $minutes = ( $seconds / 60 ) % 60;
    }
    printf "%-30s \n", "Throttle Configuration:";
    if ( !exists( $confset{batch_processing} ) ) {
        $confset{batch_processing} = 0;
    }
    if ( $confset{batch_processing} == 0 ) {
        printf "%-50s %-30s \n", '   Batch Processing', 'Disabled';
        printf "%-50s %-30s \n", '   Throttle Rate',
          "$confset{throttle} seconds";
        printf "%-50s %-30s \n", '   Throttle Per Hour', "$perhour emails";
    }
    else {
        printf "%-50s %-30s \n", '   Batch Processing', 'Enabled';
        printf "%-50s %-30s \n", '   Batch Size',
          "$confset{batch_processing} messages";
        printf "%-50s %-30s \n", '   Batch Period', "$minutes minutes";
    }
    printf "%-80s \n\n",
      "________________________________________________________________";
}

sub _display {
    my (
        $import,      $cbyuser,   $unsub,       $change,    $resub,
        $subadm,      $subd,      $subject,     $sendstart, $processed,
        $bouncecount, $confirmed, $bouncebacks, $ips,       $invalid
    ) = @_;
    my ( $confirm, $total ) = 0;
    foreach (@$confirmed) {
        if ( $_ == 1 ) {
            $confirm++;
        }
        $total++;
    }
    my $unconfirmed = $total - $confirm;
    my $bounces;
    foreach (@$bouncebacks) {
        $bounces += $_;
    }
    my $bounce_percent = $bounces / $total * 100;
    $bounce_percent = round($bounce_percent);
    my $invalid_count   = @$invalid;
    my $invalid_percent = $invalid_count / $total * 100;
    $invalid_percent = round($invalid_percent);
    my %ipseen;
    my @unique = grep { !$ipseen{$_}++ } @$ips;
    my $ipcount = @unique;

    if ( !defined($sendstart) || !defined($subject) || !defined($processed) ) {
        $sendstart = 'NULL';
        $subject   = 'NULL';
        $processed = 'NULL';
    }
    printf "\n %-17s %-50s \n", "", "PHPLIST DETAILED INFORMATION";
    printf "%-80s\n\n",
      "______________________________________________________________";
    printf "%-30s \n",         "List Totals:";
    printf "%-50s %-30s \n",   "   Confirmed Addresses", $confirm;
    printf "%-50s %-30s \n",   "   Unconfirmed Addresses", $unconfirmed;
    printf "%-50s %-30s \n\n", "   Total Addresses", $total;
    printf "%-30s \n",         "Simple Checks:";
    printf "%-50s %-30s \n",   "   Bounceback %", "$bounce_percent%";
    printf "%-50s %-30s \n",   "   Unique IPs", $ipcount;
    printf "%-50s %-30s \n",   "   Invalid Addresses", $invalid_count;
    printf "%-50s %-30s \n\n", "   Invalid %", "$invalid_percent%";
    printf "%-30s \n",         "How was the user added:";
    printf "%-50s %-30s \n",   "   Imported Addresses", $import;
    printf "%-50s %-30s \n",   "   Confirmed Addresses", $cbyuser;
    printf "%-50s %-30s \n",   "   Unsubscribed", $unsub;
    printf "%-50s %-30s \n",   "   Changed Subscription", $change;
    printf "%-50s %-30s \n",   "   Re-Subscription", $resub;
    printf "%-50s %-30s \n",   "   Admin Subscribed", $subadm;
    printf "%-50s %-30s \n\n", "   User Subscribed", $subd;
    printf "%-30s \n",         "Last Run:";
    printf "%-50s %-30s \n",   "   Send Start", $sendstart;
    printf "%-50s %-30s \n",   "   Subject", $subject;
    printf "%-50s %-30s \n",   "   Volume", $processed;
    printf "%-50s %-30s \n\n", "   Bouncecount", defined $bouncecount ? $bouncecount : 'NULL';
}

sub _runarp {
    my $db = shift;
    if ( !defined($db) ) { _usage(); }
    my (
        @confirmed, @status,  @requested, @failures,
        $sendstart, $subject, $processed
    );
    my $dbh      = _connect_db($db);
    my $ginfo = $dbh->prepare(
"SELECT double_status,double_requested,double_confirmed,failures from arp3_cam;"
    );
    $ginfo->execute();

    while ( my $data = $ginfo->fetchrow_hashref() ) {
        push( @status,    $data->{'double_status'} );
        push( @requested, $data->{'double_requested'} );
        push( @confirmed, $data->{'double_confirmed'} );
        push( @failures,  $data->{'failures'} );
    }
    $ginfo = $dbh->prepare(
"SELECT create_date,subject,subscribers from arp3_bat order by create_date desc limit 1;"
    );
    $ginfo->execute();
    while ( my $data = $ginfo->fetchrow_hashref() ) {
        $sendstart = $data->{'create_date'};
        $subject   = $data->{'subject'};
        $processed = $data->{'subscribers'};
    }
    $ginfo->finish();
    $sendstart = localtime($sendstart);
    my $total           = 0;
    my $confirmed_count = 0;
    foreach (@status) {
        if ( $_ == 1 ) {
            $confirmed_count++;
        }
        $total++;
    }
    foreach (@confirmed) {
        if ( $_ != 0 ) {
            $confirmed_count++;
        }
    }
    my $requested_count = 0;
    foreach (@requested) {
        if ( $_ != 0 ) {
            $requested_count++;
        }
    }
    my $fail_count = 0;
    foreach (@failures) {
        $fail_count += $_;
    }
    my $unconfirmed = $total - $confirmed_count;
    printf "\n %-17s %-50s \n", "", "ARP3 DETAILED INFORMATION";
    printf "%-80s\n\n",
      "______________________________________________________________";
    printf "%-30s \n",         "List Totals:";
    printf "%-50s %-30s \n",   "   Confirmed Addresses", $confirmed_count;
    printf "%-50s %-30s \n",   "   Unconfirmed Addresses", $unconfirmed;
    printf "%-50s %-30s \n",   "   Confirmation e-mails sent", $requested_count;
    printf "%-50s %-30s \n",   "   Bouncebacks", $fail_count;
    printf "%-50s %-30s \n\n", "   Total Addresses", $total;
    printf "%-30s \n",         "Last Run:";
    printf "%-50s %-30s \n",   "   Created", $sendstart;
    printf "%-50s %-30s \n",   "   Subject", $subject;
    printf "%-50s %-30s \n",   "   Subscribers", $processed;
    printf "%-80s\n\n",
      "______________________________________________________________";
}

sub _runinter {
    my $db = shift;
    if ( !defined($db) ) { _usage(); }
    my ( @confirmed, @unsubscribed, @failures, @bounceinfo );
    my $dbh      = _connect_db($db);
    my $ginfo = $dbh->prepare(
"SELECT confirmed,unsubscribed,requestdate,subscribedate,confirmdate,bounced from email_list_subscribers;"
    );
    $ginfo->execute();

    while ( my $data = $ginfo->fetchrow_hashref() ) {
        push( @unsubscribed, $data->{'unsubscribed'} );
        push( @confirmed,    $data->{'confirmed'} );
        push( @failures,     $data->{'bounced'} );
    }
    $ginfo =
      $dbh->prepare("SELECT bouncerule from email_list_subscriber_bounces;");
    $ginfo->execute();
    while ( my $data = $ginfo->fetchrow_hashref() ) {
        push( @bounceinfo, $data->{'bouncerule'} );
    }
    $ginfo->finish();
    my ( $confirm_count, $total, $bounce_count, $unsub_count ) = 0;
    foreach (@confirmed) {
        if ( $_ == 1 ) {
            $confirm_count++;
        }
        $total++;
    }
    my $unconfirmed = $total - $confirm_count;
    foreach (@unsubscribed) {
        if ( $_ == 1 ) {
            $unsub_count++;
        }
    }
    foreach (@failures) {
        if ( $_ != 0 ) {
            $bounce_count++;
        }
    }
    $_ = '0'
      for (
        my ( $exist, $relay, $remote, $content, $quota, $config, $inactive ) );
    foreach (@bounceinfo) {
        if ( $_ =~ /emaildoesntexist/ ) {
            $exist++;
        }
        if ( $_ =~ /relayerror/ ) {
            $relay++;
        }
        if ( $_ =~ /remoteconfigerror/ ) {
            $remote++;
        }
        if ( $_ =~ /blockedcontent/ ) {
            $content++;
        }
        if ( $_ =~ /overquota/ ) {
            $quota++;
        }
        if ( $_ =~ /localconfigerror/ ) {
            $config++;
        }
        if ( $_ =~ /inactive/ ) {
            $inactive++;
        }
    }
    printf "\n %-17s %-50s \n", "", "INTERSPIRE DETAILED INFORMATION";
    printf "%-80s\n\n",
      "______________________________________________________________";
    printf "%-30s \n",         "List Totals:";
    printf "%-50s %-30s \n",   "   Confirmed Addresses", $confirm_count;
    printf "%-50s %-30s \n",   "   Unconfirmed Addresses", $unconfirmed;
    printf "%-50s %-30s \n",   "   Bouncebacks", $bounce_count;
    printf "%-50s %-30s \n\n", "   Total Addresses", $total;
    printf "%-30s \n",         "Bounceback Reasons:";
    printf "%-50s %-30s \n",   "   Email Doesnt Exist", $exist;
    printf "%-50s %-30s \n",   "   Relay Error", $relay;
    printf "%-50s %-30s \n",   "   Remote Error", $remote;
    printf "%-50s %-30s \n",   "   Blocked Content", $content;
    printf "%-50s %-30s \n",   "   Quota", $quota;
    printf "%-50s %-30s \n",   "   Config Error", $config;
    printf "%-50s %-30s \n",   "   InActive", $inactive;
    printf "%-80s\n\n",
      "______________________________________________________________";
}

sub _connect_db {
    my $db = shift || die "Database name not provided.";
    return DBI->connect( "DBI:mysql:database=$db;host=localhost;mysql_read_default_file=/root/.my.cnf", undef, undef );
}

sub _runjoom {
    my $db = shift;
    if ( !defined($db) ) { _usage(); }
    my ( @confirmed, @unconfirmed, @failures, @total, @html, @text, @sent, @opened, @bounces, @bc, @subject, @id );
    my $dbh      = _connect_db($db);
    my $ginfo = $dbh->prepare("select count(*) from jos_acymailing_subscriber where enabled = 1;");
    $ginfo->execute();
    while ( my $data = $ginfo->fetchrow_hashref() ) {
        push( @total, $data->{'count(*)'} );
    }
    $ginfo = $dbh->prepare("select count(*) from jos_acymailing_subscriber where confirmed = 1 and enabled = 1;");
    $ginfo->execute();
    while ( my $data = $ginfo->fetchrow_hashref() ) {
        push( @confirmed, $data->{'count(*)'} );
    }
    $ginfo = $dbh->prepare("select count(*) from jos_acymailing_subscriber where confirmed = 0 and enabled = 1;");
    $ginfo->execute();
    while ( my $data = $ginfo->fetchrow_hashref() ) {
        push( @unconfirmed,    $data->{'count(*)'} );
    }
    $ginfo = $dbh->prepare("select mailid,count(senthtml),count(senttext),from_unixtime(senddate),bounceunique,openunique from jos_acymailing_stats order by senddate desc limit 1;");
    $ginfo->execute();
    while ( my $data = $ginfo->fetchrow_hashref() ) {
        push( @id, $data->{'mailid'} );
        push( @html, $data->{'count(senthtml)'} );
        push( @text,    $data->{'count(senttext)'} );
        push( @sent,     $data->{'from_unixtime(senddate)'} );
        push( @failures,     $data->{'bounceunique'} );
        push( @opened,     $data->{'openunique'} );
    }
    my $id = $id[0];
    $ginfo = $dbh->prepare("select subject from jos_acymailing_mail where mailid = $id;");
    $ginfo->execute();
    while ( my $data = $ginfo->fetchrow_hashref() ) {
        push( @subject, $data->{'subject'} );
    }
    $ginfo = $dbh->prepare("select sum(bounceunique) from jos_acymailing_stats;");
    $ginfo->execute();
    while ( my $data = $ginfo->fetchrow_hashref() ) {
        push( @bounces, $data->{'sum(bounceunique)'} );
    }
    $ginfo = $dbh->prepare("select count(bounceunique) from jos_acymailing_stats;");
    $ginfo->execute();
    while ( my $data = $ginfo->fetchrow_hashref() ) {
        push( @bc, $data->{'count(bounceunique)'} );
    }
    printf "\n %-9s %-50s \n", "", "JOOMLA ACYMAILING DETAILED INFORMATION";
    printf "%-80s\n\n",
      "______________________________________________________________";
    printf "%-30s \n",         "List Totals:";
    printf "%-50s %-30s \n",   "   Confirmed Addresses", $confirmed[0];
    printf "%-50s %-30s \n",   "   Unconfirmed Addresses", $unconfirmed[0];
    printf "%-50s %-30s \n",   "   Bouncebacks", $bounces[0];
    printf "%-50s %-30s \n",   "   Bounce %", $bounces[0] / $bc[0];
    printf "%-50s %-30s \n\n", "   Total Addresses", $total[0];
    printf "%-30s \n",         "Last Run:";
    printf "%-50s %-30s \n",   "   Created", $sent[0];
    printf "%-50s %-30s \n",   "   Subject", $subject[0];
    printf "%-50s %-30s \n",   "   Volume", $html[0] + $text[0];
    printf "%-50s %-30s \n",   "   Bouncebacks", $failures[0];
    printf "%-80s\n\n",
      "______________________________________________________________";
}
